se/SE/se-lib/Route/FixCrmProcesInitIdx.php

<?php

Lib::loadClass('RouteBase');
Lib::loadClass('DebugExecutionTime');

class Route_FixCrmProcesInitIdx extends RouteBase {

	public function handleAuth() {
		if (!User::logged()) {
			throw new HttpException('Unauthorized', 401);
		}
	}

	public function defaultAction() {
		SE_Layout::gora();
		?>
		<a href="index.php?_route=FixCrmProcesInitIdx&_task=run">aktualizuj uprawnienia</a>
		<?php
	}

	public function runAction() {
		SE_Layout::gora();
		SE_Layout::menu();

		$DBG_TIME = true;//('1' == V::get('DBG_TIME', '', $_GET));
		$dbgExecTime = new DebugExecutionTime();
		if ($DBG_TIME) $dbgExecTime->activate();
		$dbgExecTime->log('start');

		try {
			$this->_callProcedure();
		} catch (Exception $e) {
			?>
			<div class="container">
				<div class="alert alert-warning">
					<?php echo $e->getMessage(); ?>
				</div>
			</div>
			<?php
			SE_Layout::dol();
			exit;
		}

		$dbgExecTime->log('procedure');
		$procExecTime = $dbgExecTime->getLastExecTime();
		//$totalExecTime = $dbgExecTime->getTotalExecTime();

		?>
		<div class="container">
			<div class="alert alert-success">
				Zaktualizowano uprawnienia
				<br><span style="font-style:italic; opacity:0.4;">(<?php echo number_format($procExecTime, 6); ?> s)</span>
			</div>
		</div>
		<?php
		if($DBG_TIME){
			$dbgExecTime->log('end');
			//$dbgExecTime->printDebug();
		}
		SE_Layout::dol();
	}

	public function runApiAction() {
		$this->_callProcedure();
		die('Zaktualizowano uprawnienia');
	}

	private function _callProcedure() {
		$sql = "call `update_proces_init_idx_rec`();";
		$db = DB::getDB();
		if ($db->has_errors()) {
			throw new Exception("DB Errors: " . implode("\n<br>", $db->get_errors()));
		}
		$res = $db->query($sql);
		if ($db->has_errors()) {
			$dbErrors = array();
			if ($db->has_errors()) {
				$dbErrorsSql = $db->get_errors();
				foreach ($dbErrorsSql as $vErr) {
					if ('SQL QUERY FAILED: ' == substr($vErr, 0, 18)) {
						$vErr = substr($vErr, 18);
					}
					//$dbErrors[] = StorageException::parseMessage($vErr);
					$dbErrors[] = $vErr;
				}
			}
			throw new StorageException($dbErrors);
		}
	}

	public function test1Action() {
		$sql = <<<SQL
	select count(*) as cnt, p.`ID`, group_concat(i.`idx_PROCES_INIT_ID`), group_concat(i.`idx_PROCES_WITH_GROUPS_ID`)
	from `CRM_PROCES` p
		left join `CRM_PROCES_idx` i  on(i.`ID_PROCES`=p.`ID`)
	where i.`ID_PROCES`=p.`ID`
	group by p.`ID`
	order by cnt desc
SQL;
		$db = DB::getDB();
		if ($db->has_errors()) {
			throw new Exception("DB Errors: " . implode("\n<br>", $db->get_errors()));
		}
		$res = $db->query($sql);
		if ($db->has_errors()) {
			throw new Exception("DB Errors: " . implode("\n<br>", $db->get_errors()));
		}
		while ($r = $db->fetch($res)) {
			echo'<pre>';print_r($r);echo'</pre>';
		}
	}

	public function test2Action() {
		$sqlList = array();
		$sqlList['RemoveTableGroupToInit'] = "DROP TABLE IF EXISTS `CRM_PROCES_idx_GROUPS_to_INIT`";
		$sqlList['InstallTableGroupToInit'] = <<<SQL
			CREATE TABLE IF NOT EXISTS `CRM_PROCES_idx_GROUPS_to_INIT` (
				`PROCES_INIT_ID` int(11) NOT NULL
				, `GROUP_ID` int(11) NOT NULL
				, KEY `PROCES_INIT_ID` (`PROCES_INIT_ID`)
				, KEY `GROUP_ID` (`GROUP_ID`)
				, UNIQUE KEY `uniq__GROUP_to_INIT` (`PROCES_INIT_ID`,`GROUP_ID`)
			) ENGINE=MyISAM DEFAULT CHARSET=latin2;
SQL;
		$sqlList['UpdateTableGroupToInit'] = <<<SQL
			insert ignore into `CRM_PROCES_idx_GROUPS_to_INIT` (`PROCES_INIT_ID`,`GROUP_ID`)
				select i.`ID_PROCES` as PROCES_INIT_ID, z.`ID` as GROUP_ID
				from `CRM_PROCES_idx` i
					join `CRM_WSKAZNIK` wsk on(wsk.`ID_PROCES`=i.`idx_PROCES_WITH_GROUPS_ID`
							and wsk.`A_STATUS` in('NORMAL', 'WAITING')
						)
					join `CRM_LISTA_ZASOBOW` as z on(z.`ID`=wsk.`ID_ZASOB`
							and z.`TYPE` in('STANOWISKO','PODMIOT','DZIAL')
							and z.`A_STATUS` in('NORMAL', 'WAITING')
						)
					join `CRM_PRZYPADEK` przyp on(przyp.`ID`=wsk.`ID_PRZYPADEK`
							and przyp.`FORM_TREAT` & 8 -- has perm X
						)
				where i.`TYPE` in('PROCES_INIT', 'GOTO_AND_RETURN')
SQL;
		$db = DB::getDB();
		if ($db->has_errors()) {
			throw new Exception("DB Errors: " . implode("\n<br>", $db->get_errors()));
		}
		$DBG_TIME = true;//('1' == V::get('DBG_TIME', '', $_GET));
		$dbgExecTime = new DebugExecutionTime();
		if ($DBG_TIME) $dbgExecTime->activate();
		$dbgExecTime->log('start');
		foreach ($sqlList as $sqlName => $sql) {
			$res = $db->query($sql);
			if ($db->has_errors()) {
				throw new Exception("DB Errors at sql '{$sqlName}': " . implode("\n<br>", $db->get_errors()));
			}
			if($DBG_TIME) $dbgExecTime->log("end-{$sqlName}");
		}
		if($DBG_TIME) $dbgExecTime->printDebug();
	}

	public function runDebugAction() {
		$sqlList = array();
//	SET @conf_last_exec_start_key = 'tbl_indexer_CRM_PROCES_last_exec_start';
//	SET @conf_last_exec_end_key   = 'tbl_indexer_CRM_PROCES_last_exec_end';
		$sqlList['0-prepare-while-1-rm'] = "DROP PROCEDURE if exists `update_proces_init_idx_rec__while1`";
		$sqlList['0-prepare-while-1-create'] = <<<SQL
CREATE PROCEDURE `update_proces_init_idx_rec__while1`()
BEGIN

		update `CRM_PROCES_idx` as i set i.`idx_PROCES_INIT_ID`=IF(i.`TYPE`='PROCES_INIT', i.`ID_PROCES`, 0);
		SET @i = 0;
		SET @loopLomit = 100;
		SET @pinitCnt = 1;
		WHILE @i < @loopLomit and @pinitCnt > 0 DO
			update `CRM_PROCES_idx` as p join `CRM_PROCES_idx` as pp on(pp.`ID_PROCES`=p.`PARENT_ID`)
				set p.`idx_PROCES_INIT_ID`=pp.`idx_PROCES_INIT_ID`
				where p.`idx_PROCES_INIT_ID`=0 and pp.`idx_PROCES_INIT_ID`>0;
			SET @pinitCnt = ROW_COUNT();
			SET @i = @i + 1;
		END WHILE;
END
SQL;
		$sqlList['0-prepare-while-2-rm'] = "DROP PROCEDURE if exists `update_proces_init_idx_rec__while2`";
		$sqlList['0-prepare-while-2-create'] = <<<SQL
CREATE PROCEDURE `update_proces_init_idx_rec__while2`()
BEGIN

		SET @i = 0;
		SET @loopLomit = 100;
		SET @groupCnt = 1;
		WHILE @i < @loopLomit and @groupCnt > 0 DO
			update `CRM_PROCES_idx` as p join `CRM_PROCES_idx` as pp on(pp.`ID_PROCES`=p.`PARENT_ID`)
				set p.`idx_PROCES_WITH_GROUPS_ID`=pp.`idx_PROCES_WITH_GROUPS_ID`
				where p.`idx_PROCES_WITH_GROUPS_ID` is null
					and pp.`idx_PROCES_WITH_GROUPS_ID` is not null
					and pp.`idx_PROCES_WITH_GROUPS_ID`>0;
			SET @groupCnt = ROW_COUNT();
			SET @i = @i + 1;
		END WHILE;

END
SQL;
		$sqlList__TEST_PERF_NO_DIFF['0-prepare-while-2-create'] = <<<SQL
CREATE PROCEDURE `update_proces_init_idx_rec__while2`()
BEGIN

		SET @i = 0;
		SET @loopLomit = 100;
		SET @groupCnt = 1;
		WHILE @i < @loopLomit and @groupCnt > 0 DO
			update `CRM_PROCES_idx` as p
				join `CRM_PROCES_idx` as pp on(pp.`ID_PROCES`=p.`PARENT_ID`
					and pp.`idx_PROCES_WITH_GROUPS_ID` is not null
					and pp.`idx_PROCES_WITH_GROUPS_ID`>0
				)
				set p.`idx_PROCES_WITH_GROUPS_ID`=pp.`idx_PROCES_WITH_GROUPS_ID`
				where p.`idx_PROCES_WITH_GROUPS_ID` is null
			;
			SET @groupCnt = ROW_COUNT();
			SET @i = @i + 1;
		END WHILE;

END
SQL;
		$sqlList['1-set-start-time-in-config'] = <<<SQL
		replace into `CRM_CONFIG` (`conf_key`, `conf_val`) values ('tbl_indexer_CRM_PROCES_last_exec_start', NOW());
SQL;
		$sqlList['2-truncate-table'] = <<<SQL
		truncate table `CRM_PROCES_idx`;
		-- delete from `CRM_PROCES_idx`;
SQL;
		$sqlList['3-fill-CRM_PROCES_idx'] = <<<SQL
-- select 'insert into `CRM_PROCES_idx`...';
		insert into `CRM_PROCES_idx` (`ID_PROCES`,`TYPE`,`PARENT_ID`,`idx_PROCES_INIT_ID`)
			select p.`ID`, p.`TYPE`, p.`PARENT_ID`, IF(p.`TYPE`='PROCES_INIT', p.`ID`, 0) as idx_PROCES_INIT_ID
			from `CRM_PROCES` p
			where p.`A_STATUS` in('NORMAL','WAITING')
		;
SQL;
		$sqlList['4-while-1'] = <<<SQL
		call `update_proces_init_idx_rec__while1`();
SQL;
		$sqlList['5-before-while-2'] = <<<SQL
		update `CRM_PROCES_idx` i
				join `CRM_WSKAZNIK` as wsk on(wsk.`ID_PROCES`=i.`ID_PROCES`
						and wsk.`A_STATUS` in('NORMAL', 'WAITING')
					)
				join `CRM_LISTA_ZASOBOW` as z on(z.`ID`=wsk.`ID_ZASOB`
						and z.`TYPE` in('STANOWISKO','PODMIOT','DZIAL')
						and z.`A_STATUS` in('NORMAL', 'WAITING')
					)
				join `CRM_PRZYPADEK` przyp on(przyp.`ID`=wsk.`ID_PRZYPADEK`
						and przyp.`FORM_TREAT` & 8 -- has perm X
					)
			set
				i.`idx_PROCES_WITH_GROUPS_ID`=i.`ID_PROCES`
			where i.`idx_PROCES_INIT_ID`>0;
SQL;
		$sqlList['6-while-2'] = <<<SQL
		call `update_proces_init_idx_rec__while2`();
SQL;
		$sqlList['7-goto_and_return'] = <<<SQL
		insert ignore into `CRM_PROCES_idx` (`ID_PROCES`,`TYPE`,`PARENT_ID`,`idx_PROCES_INIT_ID`,`idx_PROCES_WITH_GROUPS_ID`)
			select g.`ID`, 'GOTO_AND_RETURN' as `TYPE`, p.`ID` as PARENT_ID, g.`ID` as idx_PROCES_INIT_ID
				, pi.`idx_PROCES_WITH_GROUPS_ID`
			from `CRM_PROCES` p
				join `CRM_PROCES_idx` pi on(pi.`ID_PROCES`=p.`ID`)
				join `CRM_PROCES` g on(g.`ID`=p.`IF_TRUE_GOTO`
						and g.`TYPE`='PROCES_INIT'
						and g.`A_STATUS` in('NORMAL','WAITING')
						and p.`IF_TRUE_GOTO_FLAG`='GOTO_AND_RETURN'
						and p.`IF_TRUE_GOTO`>0
					)
			where p.`A_STATUS` in('NORMAL','WAITING')
				and 0=(select IF(i.`idx_PROCES_WITH_GROUPS_ID` is null, 0, -1) from `CRM_PROCES_idx` i where i.`ID_PROCES`=g.`ID`)
		;
--		SET @groupCnt = ROW_COUNT();

SQL;
		$sqlList['8-update-idx_PROCES_WITH_GROUPS_ID-zero-where-null'] = <<<SQL
		update `CRM_PROCES_idx`
			set `idx_PROCES_WITH_GROUPS_ID`=0
			where `idx_PROCES_WITH_GROUPS_ID` is null
				and `TYPE`='PROCES_INIT'
		;

SQL;
		$sqlList['9-fix-goto_and_return'] = <<<SQL
		insert ignore into `CRM_PROCES_idx` (`ID_PROCES`,`TYPE`,`PARENT_ID`,`idx_PROCES_INIT_ID`,`idx_PROCES_WITH_GROUPS_ID`)
			select i.`ID_PROCES`, 'GOTO_AND_RETURN' as `TYPE`, i.`PARENT_ID`, i.`idx_PROCES_INIT_ID`, pi.`idx_PROCES_WITH_GROUPS_ID`
			from `CRM_PROCES_idx` i
				join `CRM_PROCES_idx` pi on(pi.`idx_PROCES_INIT_ID`=i.`idx_PROCES_INIT_ID`)
			where i.`idx_PROCES_WITH_GROUPS_ID` is null
				and pi.`idx_PROCES_WITH_GROUPS_ID` is not null
				and pi.`idx_PROCES_WITH_GROUPS_ID`>0
				and i.`TYPE`!='GOTO_AND_RETURN'
		;

SQL;
		$sqlList['10-delete-where-!idx_PROCES_WITH_GROUPS_ID'] = <<<SQL
-- select 'delete idx_PROCES_WITH_GROUPS_ID...';
		delete from `CRM_PROCES_idx` where `idx_PROCES_WITH_GROUPS_ID` is null or `idx_PROCES_WITH_GROUPS_ID`=0;
SQL;
		$sqlList['11-update-CRM_PROCES-idx_PROCES_INIT_ID'] = <<<SQL
-- select 'update `CRM_PROCES`...';
		update `CRM_PROCES` p
			set p.`idx_PROCES_INIT_ID`=coalesce((select i.`idx_PROCES_INIT_ID` from `CRM_PROCES_idx` i where i.`ID_PROCES`=p.`ID` limit 1), 0)
		;

SQL;
		$sqlList['11-clear-CRM_PROCES_idx_GROUPS_to_INIT'] = <<<SQL
-- select 'update `CRM_PROCES_idx_GROUPS_to_INIT`...';
		truncate table `CRM_PROCES_idx_GROUPS_to_INIT`;
SQL;
		$sqlList['11-fill-CRM_PROCES_idx_GROUPS_to_INIT'] = <<<SQL
		insert ignore into `CRM_PROCES_idx_GROUPS_to_INIT` (`PROCES_INIT_ID`,`GROUP_ID`)
			select i.`ID_PROCES` as PROCES_INIT_ID, z.`ID` as GROUP_ID
			from `CRM_PROCES_idx` i
				join `CRM_WSKAZNIK` wsk on(wsk.`ID_PROCES`=i.`idx_PROCES_WITH_GROUPS_ID`
						and wsk.`A_STATUS` in('NORMAL', 'WAITING')
					)
				join `CRM_LISTA_ZASOBOW` as z on(z.`ID`=wsk.`ID_ZASOB`
						and z.`TYPE` in('STANOWISKO','PODMIOT','DZIAL')
						and z.`A_STATUS` in('NORMAL', 'WAITING')
					)
				join `CRM_PRZYPADEK` przyp on(przyp.`ID`=wsk.`ID_PRZYPADEK`
						and przyp.`FORM_TREAT` & 8 -- has perm X
					)
			where i.`TYPE` in('PROCES_INIT', 'GOTO_AND_RETURN');

SQL;
		$sqlList['12-set-end-time-in-config'] = <<<SQL
		replace into `CRM_CONFIG` (`conf_key`, `conf_val`) values ('tbl_indexer_CRM_PROCES_last_exec_end', NOW());
SQL;
		$db = DB::getDB();
		if ($db->has_errors()) {
			throw new Exception("DB Errors: " . implode("\n<br>", $db->get_errors()));
		}
		$DBG_TIME = true;//('1' == V::get('DBG_TIME', '', $_GET));
		$dbgExecTime = new DebugExecutionTime();
		if ($DBG_TIME) $dbgExecTime->activate();
		$dbgExecTime->log('start');
		foreach ($sqlList as $sqlName => $sql) {
			$res = $db->query($sql);
			if ($db->has_errors()) {
				throw new Exception("DB Errors at sql '{$sqlName}': " . implode("\n<br>", $db->get_errors()));
			}
			if($DBG_TIME) $dbgExecTime->log("execute sql {$sqlName}");
		}
		if($DBG_TIME) $dbgExecTime->printDebug();
	}

	public function reinstallAction() {
		$this->reinstall();
		die('OK');
	}

	public function reinstall() {
		$sqlList = array();
		$sqlList['RemoveTable'] = "DROP TABLE IF EXISTS `CRM_PROCES_idx`";
		$sqlList['InstallTable'] = <<<SQL
			CREATE TABLE IF NOT EXISTS `CRM_PROCES_idx` (
				`ID_PROCES` int(11) NOT NULL
				, `PARENT_ID` int(11) NOT NULL DEFAULT '0'
				, `TYPE` varchar(32) NOT NULL DEFAULT ''
				, `idx_PROCES_INIT_ID` int(11) NOT NULL DEFAULT '0'
				, `idx_PROCES_WITH_GROUPS_ID` int(11) NULL DEFAULT NULL
			--	, `idx_PATH` text NOT NULL
				, KEY `ID_PROCES` (`ID_PROCES`)
				, KEY `PARENT_ID` (`PARENT_ID`)
				, KEY `idx_PROCES_INIT_ID` (`idx_PROCES_INIT_ID`)
				, KEY `idx_PROCES_WITH_GROUPS_ID` (`idx_PROCES_WITH_GROUPS_ID`)
				, UNIQUE KEY `uniq__idx_PROCES_WITH_GROUPS_ID` (`ID_PROCES`,`idx_PROCES_WITH_GROUPS_ID`)
			) ENGINE=MyISAM DEFAULT CHARSET=latin2;
SQL;
		$sqlList['RemoveTableGroupToInit'] = "DROP TABLE IF EXISTS `CRM_PROCES_idx_GROUPS_to_INIT`";
		$sqlList['InstallTableGroupToInit'] = <<<SQL
			CREATE TABLE IF NOT EXISTS `CRM_PROCES_idx_GROUPS_to_INIT` (
				`PROCES_INIT_ID` int(11) NOT NULL
				, `GROUP_ID` int(11) NOT NULL
				, KEY `PROCES_INIT_ID` (`PROCES_INIT_ID`)
				, KEY `GROUP_ID` (`GROUP_ID`)
				, UNIQUE KEY `uniq__GROUP_to_INIT` (`PROCES_INIT_ID`,`GROUP_ID`)
			) ENGINE=MyISAM DEFAULT CHARSET=latin2;
SQL;
		$sqlList['RemoveProcedure'] = "DROP PROCEDURE if exists `update_proces_init_idx_rec`";
		// throws errors:
		//  #1146 - Table '{DATABASE_NAME}.P5-MSG:Route_FixCrmProcesInitIdx:ERROR: Already executed' doesn't exist
		$sqlList['CreateProcedure'] = <<<SQL
CREATE PROCEDURE `update_proces_init_idx_rec`()
BEGIN
	SET @enabled = FALSE;
	SET @conf_last_exec_lock_key = 'tbl_indexer_CRM_PROCES_last_exec_lock_date';
	SET @conf_last_exec_start_key = 'tbl_indexer_CRM_PROCES_last_exec_start';
	SET @conf_last_exec_end_key   = 'tbl_indexer_CRM_PROCES_last_exec_end';
	-- if already runnig mysql will throw "Commands out of sync; you can't run this command now"
	IF (select count(1) from `CRM_CONFIG` where `conf_key`=@conf_last_exec_lock_key and `conf_val`!='')>0 THEN
		UPDATE `P5-MSG:Route_FixCrmProcesInitIdx:ERROR: Already executed` SET x=1;
	ELSE BEGIN

		replace into `CRM_CONFIG` (`conf_key`, `conf_val`) values (@conf_last_exec_lock_key, NOW()), (@conf_last_exec_start_key, NOW());

-- select sleep(2); -- // DBG

-- select 'truncate->delete...';
		truncate table `CRM_PROCES_idx`;
		-- delete from `CRM_PROCES_idx`;

-- select 'insert into `CRM_PROCES_idx`...';
		insert into `CRM_PROCES_idx` (`ID_PROCES`,`TYPE`,`PARENT_ID`,`idx_PROCES_INIT_ID`)
			select p.`ID`, p.`TYPE`, p.`PARENT_ID`, IF(p.`TYPE`='PROCES_INIT', p.`ID`, 0) as idx_PROCES_INIT_ID
			from `CRM_PROCES` p
			where p.`A_STATUS` in('NORMAL','WAITING')
		;

-- select 'while 1...';
		update `CRM_PROCES_idx` as p set p.`idx_PROCES_INIT_ID`=IF(p.`TYPE`='PROCES_INIT', p.`ID_PROCES`, 0);
		SET @i = 0;
		SET @loopLomit = 100;
		SET @pinitCnt = 1;
		WHILE @i < @loopLomit and @pinitCnt > 0 DO
			update `CRM_PROCES_idx` as p join `CRM_PROCES_idx` as pp on(pp.`ID_PROCES`=p.`PARENT_ID`)
				set p.`idx_PROCES_INIT_ID`=pp.`idx_PROCES_INIT_ID`
				where p.`idx_PROCES_INIT_ID`=0 and pp.`idx_PROCES_INIT_ID`>0;
			SET @pinitCnt = ROW_COUNT();
			SET @i = @i + 1;
		END WHILE;

-- select 'while 2... search stanowiska';
		update `CRM_PROCES_idx` i
				join `CRM_WSKAZNIK` as wsk on(wsk.`ID_PROCES`=i.`ID_PROCES`
						and wsk.`A_STATUS` in('NORMAL', 'WAITING')
					)
				join `CRM_LISTA_ZASOBOW` as z on(z.`ID`=wsk.`ID_ZASOB`
						and z.`TYPE` in('STANOWISKO','PODMIOT','DZIAL')
						and z.`A_STATUS` in('NORMAL', 'WAITING')
					)
				join `CRM_PRZYPADEK` przyp on(przyp.`ID`=wsk.`ID_PRZYPADEK`
						and przyp.`FORM_TREAT` & 8 -- has perm X
					)
			set
				i.`idx_PROCES_WITH_GROUPS_ID`=i.`ID_PROCES`
			where i.`idx_PROCES_INIT_ID`>0;
		SET @i = 0;
		SET @loopLomit = 100;
		SET @groupCnt = 1;
		WHILE @i < @loopLomit and @groupCnt > 0 DO
			update `CRM_PROCES_idx` as p join `CRM_PROCES_idx` as pp on(pp.`ID_PROCES`=p.`PARENT_ID`)
				set p.`idx_PROCES_WITH_GROUPS_ID`=pp.`idx_PROCES_WITH_GROUPS_ID`
				where p.`idx_PROCES_WITH_GROUPS_ID` is null
					and pp.`idx_PROCES_WITH_GROUPS_ID` is not null
					and pp.`idx_PROCES_WITH_GROUPS_ID`>0;
			SET @groupCnt = ROW_COUNT();
			SET @i = @i + 1;
		END WHILE;

-- select 'GOTO_AND_RETURN...';
		insert ignore into `CRM_PROCES_idx` (`ID_PROCES`,`TYPE`,`PARENT_ID`,`idx_PROCES_INIT_ID`,`idx_PROCES_WITH_GROUPS_ID`)
			select g.`ID`, 'GOTO_AND_RETURN' as `TYPE`, p.`ID` as PARENT_ID, g.`ID` as idx_PROCES_INIT_ID
				, pi.`idx_PROCES_WITH_GROUPS_ID`
			from `CRM_PROCES` p
				join `CRM_PROCES_idx` pi on(pi.`ID_PROCES`=p.`ID`)
				join `CRM_PROCES` g on(g.`ID`=p.`IF_TRUE_GOTO`
						and g.`TYPE`='PROCES_INIT'
						and g.`A_STATUS` in('NORMAL','WAITING')
						and p.`IF_TRUE_GOTO_FLAG`='GOTO_AND_RETURN'
						and p.`IF_TRUE_GOTO`>0
					)
			where p.`A_STATUS` in('NORMAL','WAITING')
				and 0=(select IF(i.`idx_PROCES_WITH_GROUPS_ID` is null, 0, -1) from `CRM_PROCES_idx` i where i.`ID_PROCES`=g.`ID`)
		;

		update `CRM_PROCES_idx`
			set `idx_PROCES_WITH_GROUPS_ID`=0
			where `idx_PROCES_WITH_GROUPS_ID` is null
				and `TYPE`='PROCES_INIT'
		;

		insert ignore into `CRM_PROCES_idx` (`ID_PROCES`,`TYPE`,`PARENT_ID`,`idx_PROCES_INIT_ID`,`idx_PROCES_WITH_GROUPS_ID`)
			select i.`ID_PROCES`, 'GOTO_AND_RETURN' as `TYPE`, i.`PARENT_ID`, i.`idx_PROCES_INIT_ID`, pi.`idx_PROCES_WITH_GROUPS_ID`
			from `CRM_PROCES_idx` i
				join `CRM_PROCES_idx` pi on(pi.`idx_PROCES_INIT_ID`=i.`idx_PROCES_INIT_ID`)
			where i.`idx_PROCES_WITH_GROUPS_ID` is null
				and pi.`idx_PROCES_WITH_GROUPS_ID` is not null
				and pi.`idx_PROCES_WITH_GROUPS_ID`>0
				and i.`TYPE`!='GOTO_AND_RETURN'
		;

-- select 'delete idx_PROCES_WITH_GROUPS_ID...';
		delete from `CRM_PROCES_idx` where `idx_PROCES_WITH_GROUPS_ID` is null or `idx_PROCES_WITH_GROUPS_ID`=0;

-- select 'update `CRM_PROCES`...';
		update `CRM_PROCES` p
			set p.`idx_PROCES_INIT_ID`=coalesce((select i.`idx_PROCES_INIT_ID` from `CRM_PROCES_idx` i where i.`ID_PROCES`=p.`ID` limit 1), 0)
		;

-- select 'update `CRM_PROCES_idx_GROUPS_to_INIT`...';
		truncate table `CRM_PROCES_idx_GROUPS_to_INIT`;
		insert ignore into `CRM_PROCES_idx_GROUPS_to_INIT` (`PROCES_INIT_ID`,`GROUP_ID`)
			select i.`ID_PROCES` as PROCES_INIT_ID, z.`ID` as GROUP_ID
			from `CRM_PROCES_idx` i
				join `CRM_WSKAZNIK` wsk on(wsk.`ID_PROCES`=i.`idx_PROCES_WITH_GROUPS_ID`
						and wsk.`A_STATUS` in('NORMAL', 'WAITING')
					)
				join `CRM_LISTA_ZASOBOW` as z on(z.`ID`=wsk.`ID_ZASOB`
						and z.`TYPE` in('STANOWISKO','PODMIOT','DZIAL')
						and z.`A_STATUS` in('NORMAL', 'WAITING')
					)
				join `CRM_PRZYPADEK` przyp on(przyp.`ID`=wsk.`ID_PRZYPADEK`
						and przyp.`FORM_TREAT` & 8 -- has perm X
					)
			where i.`TYPE` in('PROCES_INIT', 'GOTO_AND_RETURN');

		replace into `CRM_CONFIG` (`conf_key`, `conf_val`) values (@conf_last_exec_lock_key, ''), (@conf_last_exec_end_key, NOW());

		-- clear msg 'Update perms' from Msgs system
		update `CRM_UI_MSGS`
			set `A_STATUS`='OFF_HARD'
				, `actionExecutedTime`=NOW()
				, `A_RECORD_UPDATE_DATE`=NOW()
				, `A_RECORD_UPDATE_AUTHOR`='update_proces_init_idx_rec'
			where `A_STATUS`='WAITING'
				and `app_className`='FixCrmProcesInitIdx'
				and `msg`='Update perms'
				and `uiTargetType`='default_db_table'
				and `uiTargetName`='CRM_PROCES';

	END ; END IF ;
END ;

SQL;
		$sqlList['RemoveTrigger_AfterInsertProces'] = "DROP TRIGGER IF EXISTS `FixCrmProcesInitIdx_CRM_PROCES_AFTER_INSERT`";
		$sqlList['CreateTrigger_AfterInsertProces'] = "
			CREATE TRIGGER `FixCrmProcesInitIdx_CRM_PROCES_AFTER_INSERT` AFTER INSERT ON `CRM_PROCES`
				FOR EACH ROW BEGIN
					-- only when PARENT_ID changes and PARENT_ID exists in `CRM_PROCES_idx`
					IF NEW.`PARENT_ID` IS NOT NULL AND NEW.`PARENT_ID`>0 AND (select count(*) from `CRM_PROCES_idx` where `ID_PROCES`=NEW.`PARENT_ID` limit 1)>0 THEN
						-- throw warning to update perms
						IF (select count(1) from `CRM_UI_MSGS` where `app_className`='FixCrmProcesInitIdx'
									and `msg`='Update perms'
									and `uiTargetType`='default_db_table'
									and `uiTargetName`='CRM_PROCES'
									and `A_STATUS`='WAITING'
								) = 0 THEN
							INSERT INTO `CRM_UI_MSGS` (`ID`
									, `app_className`, `msg`, `msgType`
									, `uiTargetType`, `uiTargetName`
									, `userTargetType`
									, `A_RECORD_CREATE_DATE`, `A_RECORD_CREATE_AUTHOR`)
								VALUES (NULL
									, 'FixCrmProcesInitIdx', 'Update perms', 'danger'
									, 'default_db_table', 'CRM_PROCES'
									, 'everyone'
									, NOW(), coalesce(NEW.A_RECORD_UPDATE_AUTHOR,'trigger:FixCrmProcesInitIdx_CRM_PROCES_AFTER_INSERT')
								);
						END IF;
						UPDATE `P5-MSG:Route_FixCrmProcesInitIdx:ERROR: Update perms` SET x=1;
					END IF;
				END
		";
		$sqlList['RemoveTrigger_AfterUpdateProces'] = "DROP TRIGGER IF EXISTS `FixCrmProcesInitIdx_CRM_PROCES_AFTER_UPDATE`";
		$sqlList['CreateTrigger_AfterUpdateProces'] = "
			CREATE TRIGGER `FixCrmProcesInitIdx_CRM_PROCES_AFTER_UPDATE` AFTER UPDATE ON `CRM_PROCES`
				FOR EACH ROW BEGIN
					-- only when PARENT_ID changes and ID exists in `CRM_PROCES_idx`
					IF NEW.`PARENT_ID`!=OLD.`PARENT_ID` AND (select count(*) from `CRM_PROCES_idx` where `ID_PROCES` in(NEW.`ID`, NEW.`PARENT_ID`, OLD.`PARENT_ID`) limit 1)>0 THEN
						-- throw warning to update perms
						IF (select count(1) from `CRM_UI_MSGS` where `app_className`='FixCrmProcesInitIdx'
									and `msg`='Update perms'
									and `uiTargetType`='default_db_table'
									and `uiTargetName`='CRM_PROCES'
									and `A_STATUS`='WAITING'
								) = 0 THEN
							INSERT INTO `CRM_UI_MSGS` (`ID`
									, `app_className`, `msg`, `msgType`
									, `uiTargetType`, `uiTargetName`
									, `userTargetType`
									, `A_RECORD_CREATE_DATE`, `A_RECORD_CREATE_AUTHOR`)
								VALUES (NULL
									, 'FixCrmProcesInitIdx', 'Update perms', 'danger'
									, 'default_db_table', 'CRM_PROCES'
									, 'everyone'
									, NOW(), coalesce(NEW.A_RECORD_UPDATE_AUTHOR,'trigger:FixCrmProcesInitIdx_CRM_PROCES_AFTER_UPDATE')
								);
						END IF;
						UPDATE `P5-MSG:Route_FixCrmProcesInitIdx:ERROR: Update perms` SET x=1;
					END IF;
				END
		";
		$sqlList['RemoveTrigger_AfterInsertWskaznik'] = "DROP TRIGGER IF EXISTS `FixCrmProcesInitIdx_CRM_WSKAZNIK_AFTER_INSERT`";
		$sqlList['CreateTrigger_AfterInsertWskaznik'] = "
			CREATE TRIGGER `FixCrmProcesInitIdx_CRM_WSKAZNIK_AFTER_INSERT` AFTER INSERT ON `CRM_WSKAZNIK`
				FOR EACH ROW BEGIN
					-- only when ID_PROCES exists in `CRM_PROCES_idx`
					-- only when ID_ZASOB is group
					-- only when ID_PRZYPADEK  has perm 'X'
					IF NEW.`ID_PROCES`>0
							AND NEW.`ID_ZASOB`>0
							AND NEW.`ID_PRZYPADEK`>0
							AND (select count(*) from `CRM_PROCES_idx` where `ID_PROCES`=NEW.`ID_PROCES` limit 1)>0
							AND (select count(*) from `CRM_LISTA_ZASOBOW` z where z.`ID`=NEW.`ID_ZASOB` and z.`TYPE` in('STANOWISKO','DZIAL','PODMIOT') limit 1)>0
							AND (select count(*) from `CRM_PRZYPADEK` przyp where przyp.`ID`=NEW.`ID_PRZYPADEK` and przyp.`FORM_TREAT` & 8 limit 1)>0
						THEN
						-- throw warning to update perms
						IF (select count(1) from `CRM_UI_MSGS` where `app_className`='FixCrmProcesInitIdx'
									and `msg`='Update perms'
									and `uiTargetType`='default_db_table'
									and `uiTargetName`='CRM_PROCES'
									and `A_STATUS`='WAITING'
								) = 0 THEN
							INSERT INTO `CRM_UI_MSGS` (`ID`
									, `app_className`, `msg`, `msgType`
									, `uiTargetType`, `uiTargetName`
									, `userTargetType`
									, `A_RECORD_CREATE_DATE`, `A_RECORD_CREATE_AUTHOR`)
								VALUES (NULL
									, 'FixCrmProcesInitIdx', 'Update perms', 'danger'
									, 'default_db_table', 'CRM_PROCES'
									, 'everyone'
									, NOW(), coalesce(NEW.A_RECORD_UPDATE_AUTHOR,'trigger:FixCrmProcesInitIdx_CRM_WSKAZNIK_AFTER_INSERT')
								);
						END IF;
						UPDATE `P5-MSG:Route_FixCrmProcesInitIdx:ERROR: Update perms` SET x=1;
					END IF;
				END
		";
		$sqlList['RemoveTrigger_AfterUpdateWskaznik'] = "DROP TRIGGER IF EXISTS `FixCrmProcesInitIdx_CRM_WSKAZNIK_AFTER_UPDATE`";
		$sqlList['CreateTrigger_AfterUpdateWskaznik'] = "
			CREATE TRIGGER `FixCrmProcesInitIdx_CRM_WSKAZNIK_AFTER_UPDATE` AFTER UPDATE ON `CRM_WSKAZNIK`
				FOR EACH ROW BEGIN
					-- only when old or new ID_PROCES exists in `CRM_PROCES_idx`
					-- only when old or new ID_ZASOB is group
					-- only when old or new ID_PRZYPADEK  has perm 'X'
					IF (NEW.`ID_PROCES`!=OLD.`ID_PROCES` or NEW.`ID_ZASOB`!=OLD.`ID_ZASOB` or NEW.`ID_PRZYPADEK`!=OLD.`ID_PRZYPADEK`)
							AND (NEW.`ID_PROCES`>0 or OLD.`ID_PROCES`>0)
							AND (NEW.`ID_ZASOB`>0 or OLD.`ID_ZASOB`>0)
							AND (NEW.`ID_PRZYPADEK`>0 or OLD.`ID_PRZYPADEK`>0)
					--		AND (select count(*) from `CRM_PROCES_idx` where `ID_PROCES` in(NEW.`ID_PROCES`,OLD.`ID_PROCES`) limit 1)>0
							AND (select count(*) from `CRM_LISTA_ZASOBOW` z where z.`ID` in(NEW.`ID_ZASOB`,OLD.`ID_ZASOB`) and z.`TYPE` in('STANOWISKO','DZIAL','PODMIOT') limit 1)>0
							AND (select count(*) from `CRM_PRZYPADEK` przyp where przyp.`ID` in(NEW.`ID_PRZYPADEK`,OLD.`ID_PRZYPADEK`) and przyp.`FORM_TREAT` & 8 limit 1)>0
						THEN
						-- throw warning to update perms
						IF (select count(1) from `CRM_UI_MSGS` where `app_className`='FixCrmProcesInitIdx'
									and `msg`='Update perms'
									and `uiTargetType`='default_db_table'
									and `uiTargetName`='CRM_PROCES'
									and `A_STATUS`='WAITING'
								) = 0 THEN
							INSERT INTO `CRM_UI_MSGS` (`ID`
									, `app_className`, `msg`, `msgType`
									, `uiTargetType`, `uiTargetName`
									, `userTargetType`
									, `A_RECORD_CREATE_DATE`, `A_RECORD_CREATE_AUTHOR`)
								VALUES (NULL
									, 'FixCrmProcesInitIdx', 'Update perms', 'danger'
									, 'default_db_table', 'CRM_PROCES'
									, 'everyone'
									, NOW(), coalesce(NEW.A_RECORD_UPDATE_AUTHOR,'trigger:FixCrmProcesInitIdx_CRM_WSKAZNIK_AFTER_UPDATE')
								);
						END IF;
						UPDATE `P5-MSG:Route_FixCrmProcesInitIdx:ERROR: Update perms` SET x=1;
					END IF;
				END
		";
		$db = DB::getDB();
		if ($db->has_errors()) {
			throw new Exception("DB Errors: " . implode("\n<br>", $db->get_errors()));
		}
		foreach ($sqlList as $sqlName => $sql) {
			$res = $db->query($sql);
			if ($db->has_errors()) {
				throw new Exception("DB Errors at sql '{$sqlName}': " . implode("\n<br>", $db->get_errors()));
			}
		}
	}

	/*
		#1146 - Table '{DATABASE_NAME}.P5-MSG:Route_FixCrmProcesInitIdx:ERROR: Already executed' doesn't exist
	 */
	public function parseMessageFromStorage($msg) {
		switch ($msg) {
			case 'ERROR: Already executed': {
				$msg = "Aktualizacja uprawnień została już uruchomiona przez inną osobę";
				break;
			}
			case 'ERROR: Update perms': {
				$msg = "Wymagana aktualizacja uprawnień";
				break;
			}
		}
		return $msg;
	}

	public function parseMessageFromMsgsSystem($msg) {
		switch ($msg) {
			case 'Update perms': {
				$msg = "Wymagana aktualizacja uprawnień";
				break;
			}
		}
		return $msg;
	}

	public function runByMessageFromMsgsSystem($msg, &$execNotes) {
		switch ($msg) {
			case 'Update perms': {
				$execNotes .= 'Wymagana aktualizacja uprawnień';
				$this->_callProcedure();
				$execNotes .= ' done';
				break;
			}
		}
	}

}