aktualizuj uprawnienia activate(); $dbgExecTime->log('start'); try { $this->_callProcedure(); } catch (Exception $e) { ?>
getMessage(); ?>
log('procedure'); $procExecTime = $dbgExecTime->getLastExecTime(); //$totalExecTime = $dbgExecTime->getTotalExecTime(); ?>
Zaktualizowano uprawnienia
( s)
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
", $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 = <<has_errors()) { throw new Exception("DB Errors: " . implode("\n
", $db->get_errors())); } $res = $db->query($sql); if ($db->has_errors()) { throw new Exception("DB Errors: " . implode("\n
", $db->get_errors())); } while ($r = $db->fetch($res)) { echo'
';print_r($r);echo'
'; } } 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'] = <<has_errors()) { throw new Exception("DB Errors: " . implode("\n
", $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
", $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'] = << 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'] = << 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'] = << 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'] = <<0; SQL; $sqlList['6-while-2'] = <<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'] = <<0 and i.`TYPE`!='GOTO_AND_RETURN' ; SQL; $sqlList['10-delete-where-!idx_PROCES_WITH_GROUPS_ID'] = <<has_errors()) { throw new Exception("DB Errors: " . implode("\n
", $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
", $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'] = <<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_GROUP_to_PROCES`...'; truncate table `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'); 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
", $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
", $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; } } } }