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();
		// echo '<a href="index.php?_route=FixCrmProcesInitIdx&_task=run">aktualizuj uprawnienia</a>';
		?>
<div class="jumbotron">
  <div class="container">
		<form class="form-inline" method="POST">
			<input type="hidden" name="_route" value="FixCrmProcesInitIdx" />
			<input type="hidden" name="_task" value="run" />
			<button type="submit" id="fldExecuteBtn" class="btn btn-primary" autocomplete="off">
				Zaktualizuj uprawnienia
			</button>
		</form>
	</div>
</div>
<script type="text/javascript">
jQuery(document).ready(function () {
  jQuery('#fldExecuteBtn').on('click', function () {
    jQuery(this).text(jQuery(this).text() + '...').attr('disabled', 'disabled');
		jQuery(this).parent().submit();
  })
});
</script>
		<?php
		SE_Layout::dol();
	}

	public function mapAction() {
		SE_Layout::gora();
		// echo '<a href="index.php?_route=FixCrmProcesInitIdx&_task=run">aktualizuj uprawnienia</a>';
		$idProcesInit = V::get('id_proces_init', '', $_REQUEST, 'int');
		?>
<div class="jumbotron">
  <div class="container">
		<form class="form-inline" method="POST">
			<input type="hidden" name="_route" value="FixCrmProcesInitIdx" />
			<input type="hidden" name="_task" value="map" />
			<input type="text" class="form-control" name="id_proces_init" value="<?php echo $idProcesInit; ?>" />
			<button type="submit" id="fldExecuteBtn" class="btn btn-primary" autocomplete="off">
				Pokaż mapę
			</button>
		</form>
	</div>
</div>
<script type="text/javascript">
jQuery(document).ready(function () {
  jQuery('#fldExecuteBtn').on('click', function () {
    jQuery(this).text(jQuery(this).text() + '...').attr('disabled', 'disabled');
		jQuery(this).parent().submit();
  })
});
</script>
		<?php
		if ($idProcesInit) {
			$this->printProcesInitMap($idProcesInit);
		}
		SE_Layout::dol();
	}

	public function printProcesInitMap($idProcesInit) {
		Lib::loadClass('CrmProcesMap');
		$map = new CrmProcesMap($idProcesInit);
	}

	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['RemoveTableGroupToInit'] = "DROP TABLE IF EXISTS `CRM_PROCES_idx_GROUP_to_PROCES`";
		$sqlList['InstallTableGroupToInit'] = <<<SQL
			CREATE TABLE IF NOT EXISTS `CRM_PROCES_idx_GROUP_to_PROCES` (
				`ID_PROCES` int(11) NOT NULL
				, `ID_GROUP` int(11) NOT NULL
				, KEY `ID_PROCES` (`ID_PROCES`)
				, KEY `ID_GROUP` (`ID_GROUP`)
				, UNIQUE KEY `uniq__GROUP_to_PROCES` (`ID_PROCES`,`ID_GROUP`)
			) ENGINE=MyISAM DEFAULT CHARSET=latin2;
SQL;
		$sqlList['UpdateTableGroupToInit'] = <<<SQL
			insert ignore into `CRM_PROCES_idx_GROUP_to_PROCES` (`ID_PROCES`,`ID_GROUP`)
				select i.`ID_PROCES` as ID_PROCES, z.`ID` as ID_GROUP
				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-fill_idx_INIT-rm'] = "DROP PROCEDURE if exists `update_proces_init_idx__loop__fill_idx_INIT`";
		$sqlList['0-prepare-fill_idx_INIT-create'] = <<<SQL
CREATE PROCEDURE `update_proces_init_idx__loop__fill_idx_INIT`()
BEGIN

		SET @i = 0;
		SET @loopLomit = 100;
		SET @pinitCnt = 1;
		WHILE @i < @loopLomit and @pinitCnt > 0 DO
			update `CRM_PROCES_idx_TEMP` p
				join `CRM_PROCES_idx_TEMP` pp on(pp.`ID_PROCES`=p.`PARENT_ID`)
				set p.`idx_PROCES_INIT_ID`=IF(p.`idx_PROCES_INIT_ID`>0, p.`idx_PROCES_INIT_ID`, pp.`idx_PROCES_INIT_ID`)
					, p.`idx_MAIN_PROCES_INIT_ID`=pp.`idx_MAIN_PROCES_INIT_ID`
				-- // TODO:?: update here `idx_MAIN_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-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_TEMP` 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_TEMP` p
				join `CRM_PROCES_idx_TEMP` pp on(pp.`ID_PROCES`=p.`PARENT_ID`)
				set p.`idx_MAIN_PROCES_INIT_ID`=pp.`idx_MAIN_PROCES_INIT_ID`
				where p.`idx_MAIN_PROCES_INIT_ID`=0 and pp.`idx_MAIN_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_TEMP` as p
				join `CRM_PROCES_idx_TEMP` 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__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_TEMP` as p
				join `CRM_PROCES_idx_TEMP` 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['CONF.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['I.1--truncate-table'] = <<<SQL
		-- // TODO: mv to the procedure end, and add insert into `CRM_PROCES_idx` select * from `CRM_PROCES_idx_TEMP`;
		-- truncate table `CRM_PROCES_idx`;
		DROP TABLE IF EXISTS `CRM_PROCES_idx_TEMP`;
SQL;
		$sqlList['I.2--fill-CRM_PROCES_idx_TEMP'] = <<<SQL
		-- Mysql BUG: "Can't reopen table" -- CREATE TEMPORARY TABLE `CRM_PROCES_idx_TEMP` (
		-- You cannot refer to a TEMPORARY table more than once in the same query.
			CREATE TABLE `CRM_PROCES_idx_TEMP` (
				`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_MAIN_PROCES_INIT_ID` int(11) 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_MAIN_PROCES_INIT_ID` (`idx_MAIN_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`)
			)

			select p.`ID` as `ID_PROCES`
				, p.`TYPE` as `TYPE`
				, p.`PARENT_ID` as `PARENT_ID`
				, IF(p.`TYPE`='PROCES_INIT', p.`ID`, 0) as `idx_PROCES_INIT_ID`
				, IF(p.`TYPE`='PROCES_INIT', p.`ID`, 0) as `idx_MAIN_PROCES_INIT_ID`
			from `CRM_PROCES` p
			where p.`A_STATUS` in('NORMAL','WAITING')
		;
SQL;
		$sqlList['G.1--before-while-2-set-initial-proc_with_groups_id'] = <<<SQL
		update `CRM_PROCES_idx_TEMP` 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`
		-- TODO: main procedure (this rows was removed step before):
			where i.`idx_PROCES_INIT_ID`>0
		;
SQL;
		$sqlList['G.2--while-2-loop-update-proc_with_groups_id-from-parent'] = <<<SQL
		call `update_proces_init_idx_rec__while2`();
SQL;
		$sqlList['P_INIT_ID.1--fill_idx_INIT'] = <<<SQL
		call `update_proces_init_idx__loop__fill_idx_INIT`();
SQL;
		$sqlList['GOTO_AND_RET.1--from-7-fill-goto_and_return'] = <<<SQL
		insert ignore into `CRM_PROCES_idx_TEMP` (`ID_PROCES`,`TYPE`,`PARENT_ID`,`idx_PROCES_INIT_ID`,`idx_MAIN_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`
				, i.`idx_MAIN_PROCES_INIT_ID` as `idx_MAIN_PROCES_INIT_ID`
				, IF(i.`idx_PROCES_WITH_GROUPS_ID` is null, gi.`idx_PROCES_WITH_GROUPS_ID`, i.`idx_PROCES_WITH_GROUPS_ID`) as `idx_PROCES_WITH_GROUPS_ID`
			from `CRM_PROCES` p
				join `CRM_PROCES_idx_TEMP` i on(i.`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
					)
				join `CRM_PROCES_idx_TEMP` gi on(gi.`ID_PROCES`=g.`ID`)
			where p.`A_STATUS` in('NORMAL','WAITING')
		;

SQL;
		$sqlList['GOTO_AND_RET.2--fill-childrens-of-goto_and_return'] = <<<SQL
		insert ignore into `CRM_PROCES_idx_TEMP` (`ID_PROCES`,`TYPE`,`PARENT_ID`,`idx_PROCES_INIT_ID`,`idx_MAIN_PROCES_INIT_ID`,`idx_PROCES_WITH_GROUPS_ID`)
			select ic.`ID_PROCES`
				, 'GOTO_AND_RETURN_CHILD' as `TYPE`
				, ic.`PARENT_ID` as `PARENT_ID`
				, ic.`idx_PROCES_INIT_ID` as `idx_PROCES_INIT_ID`
				, i.`idx_MAIN_PROCES_INIT_ID` as `idx_MAIN_PROCES_INIT_ID`
				, IF(ic.`idx_PROCES_WITH_GROUPS_ID` is null, i.`idx_PROCES_WITH_GROUPS_ID`, ic.`idx_PROCES_WITH_GROUPS_ID`) as `idx_PROCES_WITH_GROUPS_ID`
			from `CRM_PROCES_idx_TEMP` i
				join `CRM_PROCES_idx_TEMP` ic on(
					ic.`idx_PROCES_INIT_ID`=i.`ID_PROCES`
					and ic.`ID_PROCES`!=i.`ID_PROCES`
				)
			where i.`TYPE`='GOTO_AND_RETURN'
		;

SQL;
		$sqlList__TODO_IS_NEEDED__['4.2-NEW-while-1'] = <<<SQL
		call `update_proces_init_idx_rec__while1`();
SQL;
		// TODO: mv here loop insert goto_and_return
		// TODO: add idx_MAIN_PROCES_INIT_ID - which proces has goto_end_return for this step
		$sqlList__TODO_SEE_IDX_2__['CLEANUP.1--remove-not-needed-rows'] = <<<SQL
		-- // TODO: add to main procedure
		delete from `CRM_PROCES_idx_TEMP` where `idx_PROCES_INIT_ID`=0;
SQL;
		$sqlList__TODO_SEE_IDX_2__['CLEANUP.2--update-idx_PROCES_WITH_GROUPS_ID-zero-where-null'] = <<<SQL
		update `CRM_PROCES_idx_TEMP`
			set `idx_PROCES_WITH_GROUPS_ID`=0
			where `idx_PROCES_WITH_GROUPS_ID` is null
				and `TYPE`='PROCES_INIT'
		;

SQL;
		$sqlList__TODO_IS_NEEDED__['9-fix-goto_and_return'] = <<<SQL
		insert ignore into `CRM_PROCES_idx_TEMP` (`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_TEMP` i
				join `CRM_PROCES_idx_TEMP` 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__TODO_IS_NEEDED__['10-delete-where-!idx_PROCES_WITH_GROUPS_ID'] = <<<SQL
		delete from `CRM_PROCES_idx_TEMP` where `idx_PROCES_WITH_GROUPS_ID` is null or `idx_PROCES_WITH_GROUPS_ID`=0;
SQL;
		$sqlList['IDX.1--clear-CRM_PROCES_idx'] = <<<SQL
		truncate table `CRM_PROCES_idx`;
SQL;
		$sqlList['IDX.2--update-CRM_PROCES_idx-from-temp'] = <<<SQL
		insert into `CRM_PROCES_idx`
			select i.* from `CRM_PROCES_idx_TEMP` i
			where i.`idx_PROCES_INIT_ID`>0 and i.`idx_PROCES_WITH_GROUPS_ID` is not null
		;
SQL;
		$sqlList__TODO_OFF_time_0_1__['P.1--update-CRM_PROCES-idx_PROCES_INIT_ID'] = <<<SQL
		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['GP.1--clear-CRM_PROCES_idx_GROUP_to_PROCES'] = <<<SQL
		truncate table `CRM_PROCES_idx_GROUP_to_PROCES`;
SQL;
		$sqlList['GP.2--fill-CRM_PROCES_idx_GROUP_to_PROCES'] = <<<SQL
		insert ignore into `CRM_PROCES_idx_GROUP_to_PROCES` (`ID_PROCES`,`ID_GROUP`)
			select i.`ID_PROCES` as ID_PROCES, z.`ID` as ID_GROUP
			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['CONF.2--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');
		$sqlExecuteLimit = V::get('DBG_LIMIT', 0, $_GET);
		$sqlExecuteCounter = 0;
		foreach ($sqlList as $sqlName => $sql) {
			$sqlExecuteCounter++;
			echo'<p>['.$sqlExecuteCounter.'] Executing sql "'.$sqlName.'" ... </p>';
			$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}");
			echo'<p>['.$sqlExecuteCounter.'] DONE sql "'.$sqlName.'"</p>';
			if ($sqlExecuteLimit > 0 && $sqlExecuteCounter >= $sqlExecuteLimit) break;
		}
		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_MAIN_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_MAIN_PROCES_INIT_ID` (`idx_MAIN_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['RemoveOldTableGroupToInit'] = "DROP TABLE IF EXISTS `CRM_PROCES_idx_GROUPS_to_INIT`";
		$sqlList['RemoveTableGroupToInit'] = "DROP TABLE IF EXISTS `CRM_PROCES_idx_GROUP_to_PROCES`";
		$sqlList['InstallTableGroupToInit'] = <<<SQL
			CREATE TABLE IF NOT EXISTS `CRM_PROCES_idx_GROUP_to_PROCES` (
				`ID_PROCES` int(11) NOT NULL
				, `ID_GROUP` int(11) NOT NULL
				, KEY `ID_PROCES` (`ID_PROCES`)
				, KEY `ID_GROUP` (`ID_GROUP`)
				, UNIQUE KEY `uniq__GROUP_to_PROCES` (`ID_PROCES`,`ID_GROUP`)
			) ENGINE=MyISAM DEFAULT CHARSET=latin2;
SQL;
		$sqlList['RemoveViewUserToProces'] = "DROP VIEW IF EXISTS `CRM_PROCES_idx_USER_to_PROCES_VIEW`";
		$sqlList['InstallViewUserToProces'] = <<<SQL
			CREATE VIEW `CRM_PROCES_idx_USER_to_PROCES_VIEW` AS
			select concat(u.`ID`, gi.`ID_PROCES`) as ID
				, gi.`ID_PROCES` as `ID_PROCES`
				, u.`ADM_ACCOUNT` as `ADM_ACCOUNT`
				, u.`EMPLOYEE_TYPE` as `EMPLOYEE_TYPE`
				, u.`A_STATUS` as `A_STATUS`
			from `ADMIN_USERS` u
				join `CRM_AUTH_PROFILE` au on(au.`REMOTE_ID`=u.`ID` and au.`REMOTE_TABLE`='ADMIN_USERS' and au.`A_STATUS` in('NORMAL','WAITING'))
				join `CRM_PROCES_idx_GROUP_to_PROCES` gi on(gi.`ID_GROUP`=au.`ID_ZASOB`)
SQL;
		$sqlList['RemoveViewUserToInit'] = "DROP VIEW IF EXISTS `CRM_PROCES_idx_USER_to_INIT_VIEW`";
		$sqlList['InstallViewUserToInit'] = <<<SQL
			CREATE VIEW `CRM_PROCES_idx_USER_to_INIT_VIEW` AS
			select concat(u.`ID`, p.`ID`) as ID
				, p.`ID` as `ID_PROCES_INIT`
				, p.`DESC` as `DESC`
				, u.`ADM_ACCOUNT` as `ADM_ACCOUNT`
				, u.`EMPLOYEE_TYPE` as `EMPLOYEE_TYPE`
				, u.`A_STATUS` as `A_STATUS`
			from `ADMIN_USERS` u
				join `CRM_AUTH_PROFILE` au on(au.`REMOTE_ID`=u.`ID` and au.`REMOTE_TABLE`='ADMIN_USERS' and au.`A_STATUS` in('NORMAL','WAITING'))
				join `CRM_PROCES_idx_GROUP_to_PROCES` gi on(gi.`ID_GROUP`=au.`ID_ZASOB`)
				join `CRM_PROCES_idx` i on(i.`ID_PROCES`=gi.`ID_PROCES`)
				join `CRM_PROCES` p on(p.`ID`=i.`idx_PROCES_INIT_ID`)
			where p.`TYPE`='PROCES_INIT'
			-- group by u.`ID`, p.`ID`
SQL;
		/* Usage - find proces init for user by $userLogin:
				SELECT *
				FROM  `CRM_PROCES_idx_USER_to_INIT_VIEW`
				WHERE  `ADM_ACCOUNT` LIKE  '{$userLogin}'
				GROUP BY ID_PROCES_INIT
		*/
		$sqlList['RemoveViewTablesInfo'] = "DROP VIEW IF EXISTS `CRM_PROCES_idx_TABLES_INFO_VIEW`";
		$sqlList['InstallViewTablesInfo'] = <<<SQL
			CREATE VIEW `CRM_PROCES_idx_TABLES_INFO_VIEW` AS
			select z.`ID` as `ID_CELL`
					, z.`DESC` as `CELL_NAME`
					, z.`DESC_PL` as `CELL_LABEL`
					, z.`OPIS` as `CELL_DESCRIPTION`
					, z.`SORT_PRIO` as `CELL_SORT_PRIO`
					, zp.`ID` as `ID_TABLE`
					, zp.`DESC` as `TABLE_NAME`
					, zp.`DESC_PL` as `TABLE_LABEL`
					, zp.`OPIS` as `TABLE_DESCRIPTION`
					, zpp.`ID` as `ID_DATABASE`
				from `CRM_LISTA_ZASOBOW` z
					join `CRM_LISTA_ZASOBOW` zp on(zp.`ID`=z.`PARENT_ID` and zp.`TYPE`='TABELA' and zp.`A_STATUS` in('WAITING','NORMAL'))
					join `CRM_LISTA_ZASOBOW` zpp on(zpp.`ID`=zp.`PARENT_ID`
						-- TODO: and zpp.`TYPE`='BAZA_DANYCH...'
						and zpp.`A_STATUS` in('WAITING','NORMAL')
					)
				where z.`TYPE`='KOMORKA'
					and z.`A_STATUS` in('WAITING','NORMAL')
SQL;
		$sqlList['RemoveViewTableToProces'] = "DROP VIEW IF EXISTS `CRM_PROCES_idx_TABLE_TO_PROCES_VIEW`";
		$sqlList['InstallViewTableToProces'] = <<<SQL
			CREATE VIEW `CRM_PROCES_idx_TABLE_TO_PROCES_VIEW` AS
			select tv.`ID_CELL` as `ID_CELL`
					, tv.`CELL_NAME` as `CELL_NAME`
					, tv.`ID_TABLE` as `ID_TABLE`
					, tv.`TABLE_NAME` as `TABLE_NAME`
					, tv.`TABLE_LABEL` as `TABLE_LABEL`
					, tv.`TABLE_DESCRIPTION` as `TABLE_DESCRIPTION`
					, tv.`ID_DATABASE` as `ID_DATABASE`
					, wsk.`ID_PROCES` as `ID_PROCES`
				from `CRM_PROCES_idx_TABLES_INFO_VIEW` tv
					join `CRM_WSKAZNIK` wsk on(wsk.`ID_ZASOB`=tv.`ID_CELL` and wsk.`A_STATUS` in('WAITING','NORMAL'))
					join `CRM_PROCES` p on(p.`ID`=wsk.`ID_PROCES` and p.`A_STATUS` in('WAITING','NORMAL'))
SQL;
		$sqlList['RemoveViewUrlToProces'] = "DROP VIEW IF EXISTS `CRM_PROCES_idx_URL_TO_PROCES_VIEW`";
		$sqlList['InstallViewUrlToProces'] = <<<SQL
			CREATE VIEW `CRM_PROCES_idx_URL_TO_PROCES_VIEW` AS
			select z.`ID` as `ID_URL`
					, z.`DESC` as `URL_LINK`
					, z.`DESC_PL` as `URL_LABEL`
					, z.`OPIS` as `URL_DESC`
					, wsk.`ID_PROCES` as `ID_PROCES`
				from `CRM_LISTA_ZASOBOW` z
					join `CRM_WSKAZNIK` wsk on(wsk.`ID_ZASOB`=z.`ID` and wsk.`A_STATUS` in('WAITING','NORMAL'))
					join `CRM_PROCES` p on(p.`ID`=wsk.`ID_PROCES` and p.`A_STATUS` in('WAITING','NORMAL'))
				where z.`TYPE`='URL'
					and z.`A_STATUS` in('WAITING','NORMAL')
SQL;
		$sqlList['RemoveViewTableCellToProcesWithPerms'] = "DROP VIEW IF EXISTS `CRM_PROCES_idx_TABLE_TO_PROCES_PERMS_VIEW`";
		$sqlList['InstallViewTableCellToProcesWithPerms'] = <<<SQL
			CREATE VIEW `CRM_PROCES_idx_TABLE_TO_PROCES_PERMS_VIEW` AS
			select tv.`ID_CELL` as `ID_CELL`
					, tv.`CELL_NAME` as `CELL_NAME`
					, tv.`ID_TABLE` as `ID_TABLE`
					, tv.`TABLE_NAME` as `TABLE_NAME`
					, tv.`TABLE_LABEL` as `TABLE_LABEL`
					, tv.`TABLE_DESCRIPTION` as `TABLE_DESCRIPTION`
					, tv.`ID_DATABASE` as `ID_DATABASE`
					, wsk.`ID_PROCES` as `ID_PROCES`
					, replace(przyp.`FORM_TREAT`, ',', '') as FORM_TREAT
					, IF(przyp.`FORM_TREAT` & 2,   1, 0) as PERM_R
					, IF(przyp.`FORM_TREAT` & 4,   1, 0) as PERM_W
					, IF(przyp.`FORM_TREAT` & 8,   1, 0) as PERM_X
					, IF(przyp.`FORM_TREAT` & 16,  1, 0) as PERM_C
					, IF(przyp.`FORM_TREAT` & 32,  1, 0) as PERM_S
					, IF(przyp.`FORM_TREAT` & 64,  1, 0) as PERM_O
					, IF(przyp.`FORM_TREAT` & 128, 1, 0) as PERM_V
					, IF(przyp.`FORM_TREAT` & 256, 1, 0) as PERM_E
				from `CRM_PROCES_idx_TABLES_INFO_VIEW` tv
					join `CRM_WSKAZNIK` wsk on(wsk.`ID_ZASOB`=tv.`ID_CELL` and wsk.`A_STATUS` in('WAITING','NORMAL'))
					join `CRM_PROCES` p on(p.`ID`=wsk.`ID_PROCES` and p.`A_STATUS` in('WAITING','NORMAL'))
					join `CRM_PRZYPADEK` as przyp on (przyp.`ID`=wsk.`ID_PRZYPADEK`)
SQL;
		$sqlExample_TableCellToProcesForInitTable = <<<SQL
			select tv.`ID_CELL` as `ID_CELL`
					, tv.`CELL_NAME` as `CELL_NAME`
					, tv.`CELL_LABEL` as `CELL_LABEL`
					, tv.`CELL_DESC` as `CELL_DESC`
					, tv.`CELL_SORT_PRIO` as `SORT_PRIO`
					, tv.`ID_TABLE` as `ID_TABLE`
					, tv.`TABLE_NAME` as `TABLE_NAME`
					, wsk.`ID_PROCES` as `ID_PROCES`
					, sum(IF(przyp.`FORM_TREAT` & 2,   1, 0)) as PERM_R
					, sum(IF(przyp.`FORM_TREAT` & 4,   1, 0)) as PERM_W
					, sum(IF(przyp.`FORM_TREAT` & 8,   1, 0)) as PERM_X
					, sum(IF(przyp.`FORM_TREAT` & 16,  1, 0)) as PERM_C
					, sum(IF(przyp.`FORM_TREAT` & 32,  1, 0)) as PERM_S
					, sum(IF(przyp.`FORM_TREAT` & 64,  1, 0)) as PERM_O
					, sum(IF(przyp.`FORM_TREAT` & 128, 1, 0)) as PERM_V
					, sum(IF(przyp.`FORM_TREAT` & 256, 1, 0)) as PERM_E
				from `CRM_PROCES_idx_TABLES_INFO_VIEW` tv
					join `CRM_WSKAZNIK` wsk on(wsk.`ID_ZASOB`=tv.`ID_CELL` and wsk.`A_STATUS` in('WAITING','NORMAL'))
					join `CRM_PROCES` p on(p.`ID`=wsk.`ID_PROCES` and p.`A_STATUS` in('WAITING','NORMAL'))
					join `CRM_PRZYPADEK` as przyp on (przyp.`ID`=wsk.`ID_PRZYPADEK`)
				where tv.`ID_TABLE`='{$idTable}'
					and wsk.`ID_PROCES` in({$sqlIdProcesList})
				group by tv.`ID_CELL`, wsk.`ID_PROCES`
				order by tv.`CELL_SORT_PRIO`
SQL;
		$sqlExample_UrlToUserProces = <<<SQL
			select zpv.*
				from `CRM_PROCES_idx_URL_TO_PROCES_VIEW` zpv
				where zpv.`ID_PROCES` in({$sqlIdProcesList})
SQL;
		$sqlExample_sqlIdProcesList = <<<SQL
			select gi.`ID_PROCES`
				from `CRM_PROCES_idx_GROUP_to_PROCES` gi
				where gi.`ID_GROUP` in({$idUserGroupList})
SQL;
		$sqlList_BUG = <<<SQL
			-- BUG  #1349 - View's SELECT contains a subquery in the FROM clause
			CREATE VIEW `CRM_PROCES_idx_TABLE_TO_USER_VIEW` AS
			select tpvg.`ID_PROCES`, tpvg.`TABLE_NAME`
				, upv.`ADM_ACCOUNT`
				, upv.`EMPLOYEE_TYPE`
				, upv.`A_STATUS`
			from
				(select tpv.`ID_PROCES`, tpv.`TABLE_NAME`
					from `CRM_PROCES_idx_TABLE_TO_PROCES_VIEW` tpv
					group by tpv.`TABLE_NAME`, tpv.`ID_PROCES`) as tpvg
				join `CRM_PROCES_idx_USER_to_PROCES_VIEW` upv on(upv.`ID_PROCES`=tpvg.`ID_PROCES`)
SQL;
		$sqlList['RemoveViewTableToProcesGrouped'] = "DROP VIEW IF EXISTS `CRM_PROCES_idx_TABLE_TO_PROCES_GROUPED_VIEW`";
		$sqlList['InstallViewTableToProcesGrouped'] = <<<SQL
			CREATE VIEW `CRM_PROCES_idx_TABLE_TO_PROCES_GROUPED_VIEW` AS
			select tpv.`ID_TABLE`
				, tpv.`TABLE_NAME`
				, tpv.`TABLE_LABEL`
				, tpv.`TABLE_DESCRIPTION`
				, tpv.`ID_DATABASE`
				, tpv.`ID_PROCES`
			from `CRM_PROCES_idx_TABLE_TO_PROCES_VIEW` tpv
			group by tpv.`ID_TABLE`, tpv.`ID_PROCES`
SQL;
		$sqlList['RemoveViewTableToUser'] = "DROP VIEW IF EXISTS `CRM_PROCES_idx_TABLE_TO_USER_VIEW`";
		$sqlList['InstallViewTableToUser'] = <<<SQL
			CREATE VIEW `CRM_PROCES_idx_TABLE_TO_USER_VIEW` AS
			select tpgv.`ID_TABLE`, tpgv.`TABLE_NAME`, tpgv.`ID_PROCES`
				, upv.`ADM_ACCOUNT`
				, upv.`EMPLOYEE_TYPE`
				, upv.`A_STATUS`
			from `CRM_PROCES_idx_TABLE_TO_PROCES_GROUPED_VIEW` as tpgv
				join `CRM_PROCES_idx_USER_to_PROCES_VIEW` upv on(upv.`ID_PROCES`=tpgv.`ID_PROCES`)
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

		-- CONF.1--set-start-time-in-config
		replace into `CRM_CONFIG` (`conf_key`, `conf_val`) values (@conf_last_exec_lock_key, NOW()), (@conf_last_exec_start_key, NOW());

		-- I.1--truncate-table
		truncate table `CRM_PROCES_idx_TEMP`;

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

		-- G.1--before-while-2-set-initial-proc_with_groups_id
		update `CRM_PROCES_idx_TEMP` 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
		;

		-- G.2--while-2-loop-update-proc_with_groups_id-from-parent
		SET @i = 0;
		SET @loopLomit = 100;
		SET @groupCnt = 1;
		WHILE @i < @loopLomit and @groupCnt > 0 DO
			update `CRM_PROCES_idx_TEMP` as p
				join `CRM_PROCES_idx_TEMP` 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;

		-- 'P_INIT_ID.1--fill_idx_INIT
		SET @i = 0;
		SET @loopLomit = 100;
		SET @pinitCnt = 1;
		WHILE @i < @loopLomit and @pinitCnt > 0 DO
			update `CRM_PROCES_idx_TEMP` p
				join `CRM_PROCES_idx_TEMP` pp on(pp.`ID_PROCES`=p.`PARENT_ID`)
				set p.`idx_PROCES_INIT_ID`=IF(p.`idx_PROCES_INIT_ID`>0, p.`idx_PROCES_INIT_ID`, pp.`idx_PROCES_INIT_ID`)
					, p.`idx_MAIN_PROCES_INIT_ID`=pp.`idx_MAIN_PROCES_INIT_ID`
				-- // TODO:?: update here `idx_MAIN_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;

		-- GOTO_AND_RET.1--from-7-fill-goto_and_return
		insert ignore into `CRM_PROCES_idx_TEMP` (`ID_PROCES`,`TYPE`,`PARENT_ID`,`idx_PROCES_INIT_ID`,`idx_MAIN_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`
				, i.`idx_MAIN_PROCES_INIT_ID` as `idx_MAIN_PROCES_INIT_ID`
				, IF(i.`idx_PROCES_WITH_GROUPS_ID` is null, gi.`idx_PROCES_WITH_GROUPS_ID`, i.`idx_PROCES_WITH_GROUPS_ID`) as `idx_PROCES_WITH_GROUPS_ID`
			from `CRM_PROCES` p
				join `CRM_PROCES_idx_TEMP` i on(i.`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
					)
				join `CRM_PROCES_idx_TEMP` gi on(gi.`ID_PROCES`=g.`ID`)
			where p.`A_STATUS` in('NORMAL','WAITING')
		;

		-- GOTO_AND_RET.2--fill-childrens-of-goto_and_return
		insert ignore into `CRM_PROCES_idx_TEMP` (`ID_PROCES`,`TYPE`,`PARENT_ID`,`idx_PROCES_INIT_ID`,`idx_MAIN_PROCES_INIT_ID`,`idx_PROCES_WITH_GROUPS_ID`)
			select ic.`ID_PROCES`
				, 'GOTO_AND_RETURN_CHILD' as `TYPE`
				, ic.`PARENT_ID` as `PARENT_ID`
				, ic.`idx_PROCES_INIT_ID` as `idx_PROCES_INIT_ID`
				, i.`idx_MAIN_PROCES_INIT_ID` as `idx_MAIN_PROCES_INIT_ID`
				, IF(ic.`idx_PROCES_WITH_GROUPS_ID` is null, i.`idx_PROCES_WITH_GROUPS_ID`, ic.`idx_PROCES_WITH_GROUPS_ID`) as `idx_PROCES_WITH_GROUPS_ID`
			from `CRM_PROCES_idx_TEMP` i
				join `CRM_PROCES_idx_TEMP` ic on(
					ic.`idx_PROCES_INIT_ID`=i.`ID_PROCES`
					and ic.`ID_PROCES`!=i.`ID_PROCES`
				)
			where i.`TYPE`='GOTO_AND_RETURN'
		;

		-- // TODO: fill with goto_and_ret level 2
		-- gc  p(5105) is GOTO_AND_RETURN_CHILD pID(5061) pI(5039) pIM(5040) pG(5040)
		-- gc  p(5105) is GOTO_AND_RETURN_CHILD pID(5061) pI(5039) pIM(5100) pG(5100)
		-- gg  p(5103) is GOTO_AND_RETURN       pID(5105) pI(5103) pIM(5039) pG(NULL)
		-- +   p(5103) is GOTO_AND_RETURN_LVL2  pID(5105) pI(5103) pIM(5040) pG(5040)
		-- +   p(5103) is GOTO_AND_RETURN_LVL2  pID(5105) pI(5103) pIM(5100) pG(5100)
		insert ignore into `CRM_PROCES_idx_TEMP`  (`ID_PROCES`,`TYPE`,`PARENT_ID`,`idx_PROCES_INIT_ID`,`idx_MAIN_PROCES_INIT_ID`,`idx_PROCES_WITH_GROUPS_ID`)
			select gg.`ID_PROCES` as `ID_PROCES`
				, 'GOTO_AND_RETURN_LVL2' as `TYPE`
				, gg.`PARENT_ID` as `PARENT_ID`
				, gg.`idx_PROCES_INIT_ID` as `idx_PROCES_INIT_ID`
				, gc.`idx_MAIN_PROCES_INIT_ID` as `idx_MAIN_PROCES_INIT_ID`
				, IF(gg.`idx_PROCES_WITH_GROUPS_ID` is null, gc.`idx_PROCES_WITH_GROUPS_ID`, gg.`idx_PROCES_WITH_GROUPS_ID`) as `idx_PROCES_WITH_GROUPS_ID`
		--		, 'gc', gc.*, 'gg', gg.*
			from `CRM_PROCES_idx_TEMP` gg
				join `CRM_PROCES_idx_TEMP` gc on(gc.`ID_PROCES`=gg.`PARENT_ID`)
			where gg.`TYPE`='GOTO_AND_RETURN'
				and gc.`TYPE`='GOTO_AND_RETURN_CHILD'
		;

		-- // TODO: fill childrens for goto_and_ret level 2
		-- i   p(5103) is GOTO_AND_RETURN_LVL2       pID(5105) pI(5103) pIM(5040) pG(5040)
		-- i   p(5103) is GOTO_AND_RETURN_LVL2       pID(5105) pI(5103) pIM(5100) pG(5100)
		-- ic  p(5104) is GOTO_AND_RETURN_CHILD      pID(5103) pI(5103) pIM(5039) pG(NULL)
		-- ic  p(5104) is GOTO_AND_RETURN_CHILD      pID(5103) pI(5103) pIM(5039) pG(NULL)
		-- +   p(5104) is GOTO_AND_RETURN_CHILD_LVL2 pID(5103) pI(5103) pIM(5100) pG(5100)
		-- +   p(5104) is GOTO_AND_RETURN_CHILD_LVL2 pID(5103) pI(5103) pIM(5040) pG(5040)
		insert ignore into `CRM_PROCES_idx_TEMP` (`ID_PROCES`,`TYPE`,`PARENT_ID`,`idx_PROCES_INIT_ID`,`idx_MAIN_PROCES_INIT_ID`,`idx_PROCES_WITH_GROUPS_ID`)
			select ic.`ID_PROCES`
				, 'GOTO_AND_RETURN_CHILD_LVL2' as `TYPE`
				, ic.`PARENT_ID` as `PARENT_ID`
				, ic.`idx_PROCES_INIT_ID` as `idx_PROCES_INIT_ID`
				, i.`idx_MAIN_PROCES_INIT_ID` as `idx_MAIN_PROCES_INIT_ID`
				, IF(ic.`idx_PROCES_WITH_GROUPS_ID` is null, i.`idx_PROCES_WITH_GROUPS_ID`, ic.`idx_PROCES_WITH_GROUPS_ID`) as `idx_PROCES_WITH_GROUPS_ID`
		--		, 'i', i.*, 'ic', ic.*
			from `CRM_PROCES_idx_TEMP` i
				join `CRM_PROCES_idx_TEMP` ic on(
					ic.`idx_PROCES_INIT_ID`=i.`ID_PROCES`
					and ic.`ID_PROCES`!=i.`ID_PROCES`
					and ic.`TYPE`='GOTO_AND_RETURN_CHILD'
				)
			where i.`TYPE`='GOTO_AND_RETURN_LVL2'
		;

		-- IDX.1--clear-CRM_PROCES_idx
		truncate table `CRM_PROCES_idx`;

		-- IDX.2--update-CRM_PROCES_idx-from-temp
		insert into `CRM_PROCES_idx` (`ID_PROCES`,`PARENT_ID`,`TYPE`,`idx_PROCES_INIT_ID`,`idx_MAIN_PROCES_INIT_ID`,`idx_PROCES_WITH_GROUPS_ID`)
			select i.`ID_PROCES`,i.`PARENT_ID`,i.`TYPE`,i.`idx_PROCES_INIT_ID`,i.`idx_MAIN_PROCES_INIT_ID`,i.`idx_PROCES_WITH_GROUPS_ID`
			from `CRM_PROCES_idx_TEMP` i
			where i.`idx_PROCES_INIT_ID`>0 and i.`idx_PROCES_WITH_GROUPS_ID` is not null
		;


		-- TODO OFF: time ~0.1 P.1--update-CRM_PROCES-idx_PROCES_INIT_ID
		-- 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)
		-- ;

		-- GP.1--clear-CRM_PROCES_idx_GROUP_to_PROCES
		truncate table `CRM_PROCES_idx_GROUP_to_PROCES`;

		-- GP.2--fill-CRM_PROCES_idx_GROUP_to_PROCES
		insert ignore into `CRM_PROCES_idx_GROUP_to_PROCES` (`ID_PROCES`,`ID_GROUP`)
			select i.`ID_PROCES` as ID_PROCES, z.`ID` as ID_GROUP
			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')
		;

		-- CONF.2--set-end-time-in-config
		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 DEFINER=`root`@`localhost` 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 DEFINER=`root`@`localhost` 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 DEFINER=`root`@`localhost` 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 DEFINER=`root`@`localhost` 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`
								or (NEW.`A_STATUS`!=OLD.`A_STATUS`
									and ((NEW.`A_STATUS` in('WAITING','NORMAL') and OLD.`A_STATUS` not in('WAITING','NORMAL'))
										or (NEW.`A_STATUS` not in('WAITING','NORMAL') and OLD.`A_STATUS` in('WAITING','NORMAL'))
									)
								)
							)
							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;
			}
		}
	}

}