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');

		$this->_callProcedure();

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

		?>
		<div class="container">
			<div class="alert alert-success">
				Zaktualizowano uprawnienia
				<br><span style="opacity:0.6;">(<?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()) {
			throw new Exception("DB Errors: " . implode("\n<br>", $db->get_errors()));
		}
	}

	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` 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` (`ID`)
				, 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`,`idx_PROCES_WITH_GROUPS_ID`)
			) ENGINE=MyISAM DEFAULT CHARSET=latin2;
SQL;
		$sqlList['RemoveProcedure'] = "DROP PROCEDURE if exists `update_proces_init_idx_rec`";
		$sqlList['CreateProcedure'] = <<<SQL
CREATE PROCEDURE `update_proces_init_idx_rec`()
BEGIN
	SET @enabled = FALSE;
	SET @conf_last_exec_key = 'tbl_indexer_CRM_PROCES_last_exec';
	-- call debug_msg(@enabled, 'init procedure...');
	-- if already runnig mysql will throw "Commands out of sync; you can't run this command now"
	-- IF '1'=(select IF(`conf_val` < DATE_SUB(NOW(), INTERVAL 1 MINUTE), '1', '0') from `CRM_CONFIG` where `conf_key`=@conf_last_exec_key limit 1) THEN BEGIN
		replace into `CRM_CONFIG` (`conf_key`, `conf_val`) values (@conf_last_exec_key, NOW());

		-- lock tables `CRM_PROCES_idx` WRITE, `CRM_PROCES` WRITE, `CRM_CONFIG` READ, `CRM_WSKAZNIK` READ, `CRM_LISTA_ZASOBOW` READ;
		-- lock table `CRM_PROCES_idx` WRITE;
-- select sleep(2);
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`,`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`, 0);
		--	call debug_msg(@enabled, (select concat_ws(' ', "all idx_PROCES_INIT_ID cleared", ROW_COUNT())));
		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`=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...';
		update `CRM_PROCES_idx` i
				join `CRM_WSKAZNIK` as wsk on(wsk.`ID_PROCES`=i.`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')
					)
			set
				i.`idx_PROCES_WITH_GROUPS_ID`=i.`ID`
			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`=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 into `CRM_PROCES_idx` (`ID`,`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`=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`=g.`ID`)
		;
		SET @groupCnt = ROW_COUNT();

		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`,`TYPE`,`PARENT_ID`,`idx_PROCES_INIT_ID`,`idx_PROCES_WITH_GROUPS_ID`)
			select i.`ID`, '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;

-- TODO: multiple rows for proces id - goto_and_return
select 'update `CRM_PROCES`...';
		update `CRM_PROCES` p
			set p.`idx_PROCES_INIT_ID`=(select i.`idx_PROCES_INIT_ID` from `CRM_PROCES_idx` i where i.`ID`=p.`ID` limit 1)
--		update `CRM_PROCES` p
--			join `CRM_PROCES_idx` i on(i.`ID`=p.`ID`)
--			set p.`idx_PROCES_INIT_ID`=i.`idx_PROCES_INIT_ID`
		--			, p.`idx_PROCES_WITH_GROUPS_ID`=i.`idx_PROCES_WITH_GROUPS_ID`
		;

		-- unlock tables;

	-- END; END IF;
END ;

SQL;
		$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()));
			}
		}
	}

}