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

<?php

Lib::loadClass('RouteBase');

class Route_FixZasobPath extends RouteBase {

	public function defaultAction() {
		//$sqlList['Check'] = "SHOW PROCEDURE STATUS where `Name`='update_zasob_path_idx_rec'";
		SE_Layout::gora();
		//echo '<a href="/index.php?_route=FixZasobPath&_task=run">Zaktualizuj ścieżki zasobów</a>';
		?>
<div class="jumbotron">
  <div class="container">
		<form class="form-inline" method="POST">
			<input type="hidden" name="_route" value="FixZasobPath" />
			<input type="hidden" name="_task" value="run" />
			<button type="submit" id="fldExecuteBtn" class="btn btn-primary" autocomplete="off">
				Zaktualizuj ścieżki zasobów
			</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 checkPathsAction() {
		$sql = "
			select z.`ID`
				, z.`path` as zasob_path
				, wsk.`path` as wsk_path
				, i.`idx_PATH` as idx_path
			from `CRM_LISTA_ZASOBOW` z
				join `_zasob_path_idx` i on(i.`ID`=z.`ID`)
				left join `CRM_WSKAZNIK` wks on(wsk.`ID_ZASOB`=z.`ID`)
			where (z.`path`!=i.`idx_PATH` or wsk.`path`!=i.`idx_PATH`)
		";
		$rows = array();
		$db = DB::getDB();
		if ($db->has_errors()) {
			throw new Exception("DB Errors: " . implode("\n<br>", $db->get_errors()));
		}
		$res = $db->query($sql);
		while ($r = $db->fetch($res)) {
			$rows[] = $r;
		}
		echo $sql;
		echo'<pre>';print_r($rows);echo'</pre>';
		die('OK');
	}

	public function runAction() {
		$sql = "call `update_zasob_path_idx_rec`();";
		/* update fields:
		 *	`CRM_LISTA_ZASOBOW`.`path`
		 *	`CRM_WSKAZNIK`.`path_CRM_LISTA_ZASOBOW`
		 */
		$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()));
		}

		SE_Layout::gora();
		SE_Layout::menu();
		?>
		<div class="container">
			<div class="alert alert-success">
				Zaktualizowano ścieżki zasobów
			</div>
		</div>
		<?php
		SE_Layout::dol();
	}

	public function cleanAllPathsAction() {
		$sqlList = array();
		$sqlList['CleanPath_Zasob'] = "update `CRM_LISTA_ZASOBOW` p set p.`path`='' ";
		$sqlList['CleanPath_Wskaznik'] = "update `CRM_WSKAZNIK` k set k.`path_CRM_LISTA_ZASOBOW`='' ";
		$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()));
			}
		}
		die('OK');
	}

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

	public function reinstall() {
		$sqlList = array();
		$sqlList['RemoveTable'] = "DROP TABLE IF EXISTS `_zasob_path_idx`";
		$sqlList['InstallTable'] = "
			CREATE TABLE IF NOT EXISTS `_zasob_path_idx` (
				`ID` int(11) NOT NULL
				, `P_ID` int(11) NOT NULL DEFAULT '0'
				, `DESC` varchar(255) NOT NULL DEFAULT '0'
				, `idx_PATH` varchar(255) NOT NULL DEFAULT ''
				, `idx_PATH_DESC` varchar(255) NOT NULL DEFAULT ''
				, KEY `ID` (`ID`)
				, KEY `P_ID` (`P_ID`)
			) ENGINE=MyISAM DEFAULT CHARSET=latin2
		";
		$sqlList['RemoveProcedure'] = "DROP PROCEDURE if exists `update_zasob_path_idx_rec`";
		$sqlList['CreateProcedure'] = "
			CREATE PROCEDURE `update_zasob_path_idx_rec`()
			BEGIN
				SET @conf_last_exec_key = 'tbl_indexer_zasob_last_exec';
				replace into `CRM_CONFIG` (`conf_key`, `conf_val`) values (@conf_last_exec_key, NOW());

				truncate table `_zasob_path_idx`;
				-- delete from `_zasob_path_idx`;

				insert into `_zasob_path_idx` (`ID`,`P_ID`,`DESC`)
					select z.`ID`, coalesce(z.`PARENT_ID`, 0), z.`DESC`
					from `CRM_LISTA_ZASOBOW` z
					where 1=1
				;

				update `_zasob_path_idx` as p set p.`idx_PATH`=concat('0-', p.`ID`), p.`idx_PATH_DESC`=concat('', p.`DESC`) where p.`P_ID`=0;
				update `_zasob_path_idx` as p set p.`idx_PATH`=concat('-1-', p.`ID`), p.`idx_PATH_DESC`=concat('-1/', p.`DESC`) where p.`P_ID`<0;
				SET @i = 0;
				SET @loopLomit = 100;
				SET @pinitCnt = 1;
				WHILE @i < @loopLomit and @pinitCnt > 0 DO
					update `_zasob_path_idx` p join `_zasob_path_idx` pp on(pp.`ID`=p.`P_ID`)
						set p.`idx_PATH`=concat(pp.`idx_PATH`, '-', p.`ID`)
							, p.`idx_PATH_DESC`=concat(pp.`idx_PATH_DESC`, '/', p.`DESC`)
						where p.`idx_PATH`='' and pp.`idx_PATH`!='';
					SET @pinitCnt = ROW_COUNT();
					SET @i = @i + 1;
				END WHILE;

				update `CRM_LISTA_ZASOBOW` z join `_zasob_path_idx` i on(i.`ID`=z.`ID`)
					set z.`path`=i.`idx_PATH_DESC`;
				update `CRM_WSKAZNIK` wsk left join `_zasob_path_idx` i on(i.`ID`=wsk.`ID_ZASOB`)
					set wsk.`path_CRM_LISTA_ZASOBOW`=coalesce(i.`idx_PATH_DESC`, '?');
			END
		";
		$sqlList['RemoveTrigger_BeforeInsertZasob'] = "DROP TRIGGER IF EXISTS `_CRM_LISTA_ZASOBOW_tree_INSERT`";
		$sqlList['CreateTrigger_BeforeInsertZasob'] = "
			CREATE TRIGGER `_CRM_LISTA_ZASOBOW_tree_INSERT` BEFORE INSERT ON `CRM_LISTA_ZASOBOW`
				FOR EACH ROW BEGIN
					-- SET NEW.path = CRM_LISTA_ZASOBOW_hierarchy_sys_connect_by_path('/', NEW.PARENT_ID);
					SET NEW.path = (select CONCAT(
							(select IF (NEW.`PARENT_ID`>0
								, coalesce(
									(select p.`path` from `CRM_LISTA_ZASOBOW` p where p.`ID`=NEW.`PARENT_ID` limit 1)
									, '?')
								, ''
							))
							, '/'
							, NEW.`DESC`
						));
				END
		";
		$sqlList['RemoveTrigger_AfterInsertZasob'] = "DROP TRIGGER IF EXISTS `_CRM_LISTA_ZASOBOW_tree_AFTER_INSERT`";
		$sqlList['CreateTrigger_AfterInsertZasob'] = "
			CREATE TRIGGER `_CRM_LISTA_ZASOBOW_tree_AFTER_INSERT` AFTER INSERT ON `CRM_LISTA_ZASOBOW`
				FOR EACH ROW BEGIN
					insert into `_zasob_path_idx` (`ID`, `P_ID`, `DESC`, `idx_PATH`, `idx_PATH_DESC`)
						values(
							NEW.`ID`
							, NEW.`PARENT_ID`
							, NEW.`DESC`
							, (select CONCAT(
									(select IF (NEW.`PARENT_ID`>0,
										coalesce(
											(select p.`idx_PATH` from `_zasob_path_idx` p where p.`ID`=NEW.`PARENT_ID` limit 1)
											, '?')
										, NEW.`PARENT_ID`
									))
									, '-'
									, NEW.ID
								))
							, (select CONCAT(
									(select IF (NEW.`PARENT_ID`>0
										, coalesce(
											(select p.`idx_PATH_DESC` from `_zasob_path_idx` p where p.`ID`=NEW.`PARENT_ID` limit 1)
											, '?')
										, ''
									))
									, '/'
									, NEW.`DESC`
								))
						);
				END
		";
		$sqlList['RemoveTrigger_BeforeUpdateZasob'] = "DROP TRIGGER IF EXISTS `_CRM_LISTA_ZASOBOW_tree_UPDATE`";
		// throws errors:
		//  #1146 - Table '{DATABASE_NAME}.ERROR: Loop detected ID=PARENT_ID' doesn't exist
		//  #1146 - Table '{DATABASE_NAME}.ERROR: Parent item not exists' doesn't exist
		//  #1146 - Table '{DATABASE_NAME}.ERROR: Loop detected in path' doesn't exist
		$sqlList['CreateTrigger_BeforeUpdateZasob'] = "
			CREATE TRIGGER `_CRM_LISTA_ZASOBOW_tree_UPDATE` BEFORE UPDATE ON `CRM_LISTA_ZASOBOW`
				FOR EACH ROW BEGIN
					-- IF NEW.PARENT_ID<>OLD.PARENT_ID or NEW.`DESC`<>OLD.`DESC` THEN
					-- SET NEW.path = CRM_LISTA_ZASOBOW_hierarchy_sys_connect_by_path('/', NEW.PARENT_ID);
					-- update CRM_WSKAZNIK ik set ik.path_CRM_LISTA_ZASOBOW=CRM_LISTA_ZASOBOW_hierarchy_sys_connect_by_path('/', NEW.ID) where path_CRM_LISTA_ZASOBOW like concat('%',NEW.ID,'%');
					-- -- update CRM_LISTA_ZASOBOW ik set ik.path=CRM_LISTA_ZASOBOW_hierarchy_sys_connect_by_path('/', ik.ID) where path like concat('%',OLD.`DESC`,'%') and ik.ID<>OLD.ID;
					IF NEW.`PARENT_ID`!=OLD.`PARENT_ID` or NEW.`DESC`!=OLD.`DESC` THEN
						IF OLD.`ID` = NEW.`PARENT_ID` THEN
							UPDATE `P5-MSG:Route_FixZasobPath:ERROR: Loop detected ID=P_ID` SET x=1;
							-- #1146 - Table 'biall.ERROR: Loop detected ID=PARENT_ID' doesn't exist
						END IF;
						IF NEW.`PARENT_ID` > 0 THEN
							-- check if zasob exists
							IF (select count(1) from `CRM_LISTA_ZASOBOW` p where p.`ID`=NEW.`PARENT_ID`) = 0 THEN
								UPDATE `P5-MSG:Route_FixZasobPath:ERROR: Parent item not exists` SET x=1;
								-- #1146 - Table 'biall.ERROR: Parent item not exists' doesn't exist
							END IF;
							-- check loop error
							-- state: 1276.path='0-868-1218-1276', 1218.path='0-868-1218'
							-- update P_ID=1276 where ID=1218 - should throw error
							IF (select IF(
									p.`idx_PATH` like concat('%-',OLD.ID,'-%')
									or p.`idx_PATH` like concat(OLD.ID,'-%')
									, 1
									, 0) from `_zasob_path_idx` p where p.`ID`=NEW.`PARENT_ID`) > 0 THEN
								UPDATE `P5-MSG:Route_FixZasobPath:ERROR: Loop detected in path` SET x=1;
								-- #1146 - Table 'biall.ERROR: Loop detected in path' doesn't exist
							END IF;
						END IF;

						SET NEW.`path` = (select CONCAT(
								(select IF (NEW.`PARENT_ID`>0
									, coalesce(
										(select p.`path` from `CRM_LISTA_ZASOBOW` p where p.`ID`=NEW.`PARENT_ID` limit 1)
										, '?')
									, IF (NEW.`PARENT_ID`<0
										, '-1'
										, ''
									)
								))
								, '/'
								, NEW.`DESC`
							));

						-- DONT: throw warning to update all pathes in `CRM_LISTA_ZASOBOW` with path under current?
						-- NOTE: throw error like that prevent update fields
						-- IF (select count(1) from `CRM_LISTA_ZASOBOW` p where p.`P_ID`=NEW.ID) > 0 THEN
						-- 	UPDATE `P5-MSG:Route_FixZasobPath:WARNING: Update all paths` SET x=1;
						-- END IF;

						-- DONT: update `CRM_WSKAZNIK`.`path_CRM_LISTA_ZASOBOW` - rows under NEW.ID has wrong path
					END IF;
				END
		";
		$sqlList['RemoveTrigger_AfterUpdateZasob'] = "DROP TRIGGER IF EXISTS `_CRM_LISTA_ZASOBOW_tree_AFTER_UPDATE`";
		// throws errors:
		//  #1146 - Table '{DATABASE_NAME}.WARNING: Update all paths' doesn't exist
		$sqlList['CreateTrigger_AfterUpdateZasob'] = "
			CREATE TRIGGER `_CRM_LISTA_ZASOBOW_tree_AFTER_UPDATE` AFTER UPDATE ON `CRM_LISTA_ZASOBOW`
				FOR EACH ROW BEGIN
					IF NEW.`PARENT_ID`!=OLD.`PARENT_ID` or NEW.`DESC`!=OLD.`DESC` THEN
						-- throw warning to update all pathes in `CRM_LISTA_ZASOBOW` with path under current?
						IF (select count(1) from `CRM_LISTA_ZASOBOW` p where p.`PARENT_ID`=NEW.ID) > 0 THEN
							UPDATE `P5-MSG:Route_FixZasobPath:WARNING: Update all paths` SET x=1;
						END IF;
					END IF;
				END
		";
		$sqlList['RemoveTrigger_BeforeInsertWskaznik'] = "DROP TRIGGER IF EXISTS `_CRM_WSKAZNIK_tree_INSERT`";
		$sqlList['CreateTrigger_BeforeInsertWskaznik'] = "
			CREATE TRIGGER `_CRM_WSKAZNIK_tree_INSERT` BEFORE INSERT ON `CRM_WSKAZNIK`
				FOR EACH ROW BEGIN
					IF NEW.ID_ZASOB IS NOT NULL and NEW.ID_ZASOB>0 THEN
						-- SET NEW.path_CRM_LISTA_ZASOBOW = CRM_LISTA_ZASOBOW_hierarchy_sys_connect_by_path('/', NEW.ID_ZASOB);
						SET NEW.`path_CRM_LISTA_ZASOBOW` = (select coalesce(
										(select p.`path` from `CRM_LISTA_ZASOBOW` p where p.`ID`=NEW.`ID_ZASOB` limit 1)
										, '?'));
					END IF;
				END
		";
		$sqlList['RemoveTrigger_BeforeUpdateWskaznik'] = "DROP TRIGGER IF EXISTS `_CRM_WSKAZNIK_tree_UPDATE`";
		$sqlList['CreateTrigger_BeforeUpdateWskaznik'] = "
			CREATE TRIGGER `_CRM_WSKAZNIK_tree_UPDATE` BEFORE UPDATE ON `CRM_WSKAZNIK`
				FOR EACH ROW BEGIN
					IF NEW.ID_ZASOB IS NULL THEN
						SET NEW.path_CRM_LISTA_ZASOBOW = '';
					ELSEIF OLD.ID_ZASOB IS NULL or NEW.ID_ZASOB<>OLD.ID_ZASOB THEN
						-- SET NEW.path_CRM_LISTA_ZASOBOW = CRM_LISTA_ZASOBOW_hierarchy_sys_connect_by_path('/', NEW.ID_ZASOB);
						IF NEW.ID_ZASOB>0 THEN
							SET NEW.`path_CRM_LISTA_ZASOBOW` = (select coalesce(
											(select p.`path` from `CRM_LISTA_ZASOBOW` p where p.`ID`=NEW.`ID_ZASOB` limit 1)
											, '?'));
						ELSE
							SET NEW.`path_CRM_LISTA_ZASOBOW` = '';
						END IF;
					END IF;
				END
		";
		$sqlList['RemoveEvent_everyDay'] = "DROP EVENT IF EXISTS `_CRM_LISTA_ZASOBOW__CRM_WSKAZNIK_tree_event`";
		$sqlList['CreateEvent_everyDay'] = "
			CREATE EVENT `_CRM_LISTA_ZASOBOW__CRM_WSKAZNIK_tree_event` ON SCHEDULE EVERY 1 DAY STARTS '2015-05-15 05:00:00' ON COMPLETION NOT PRESERVE ENABLE DO BEGIN
				call `update_zasob_path_idx_rec`();
			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()));
			}
		}
	}

	/*
		Table 'DB.P5-MSG:Route_FixZasobPath:WARNING: Update all paths' doesn't exist
		Table 'DB.P5-MSG:Route_FixZasobPath:ERROR: Loop detected ID=P_ID' doesn't exist
		Table 'DB.P5-MSG:Route_FixZasobPath:ERROR: Parent item not exists' doesn't exist
		Table 'DB.P5-MSG:Route_FixZasobPath:ERROR: Loop detected in path' doesn't exist
	 */
	public function parseMessageFromStorage($msg) {
		switch ($msg) {
			case 'WARNING: Update all paths': {
				$msg = "Zaktualizuj ścieżki zasobów!";
				break;
			}
			case 'ERROR: Loop detected ID=P_ID': {
				$msg = "Nr rekordu nadrzędnego musi różnić się od nr rekordu";
				break;
			}
			case 'ERROR: Parent item not exists': {
				$msg = "Nie istnieje rekord o numerze podanym jako nr nadrzędny";
				break;
			}
			case 'ERROR: Loop detected in path': {
				$msg = "Nieprawidłowy nr nadrzędny";
				break;
			}
		}
		return $msg;
	}

}

/**
 * Old triggers before 2015-06-16:

CREATE TRIGGER `_CRM_LISTA_ZASOBOW_tree_INSERT` BEFORE INSERT ON `CRM_LISTA_ZASOBOW`
	FOR EACH ROW BEGIN
		SET NEW.path = CRM_LISTA_ZASOBOW_hierarchy_sys_connect_by_path('/', NEW.PARENT_ID);
	END

CREATE TRIGGER `_CRM_LISTA_ZASOBOW_tree_UPDATE` BEFORE UPDATE ON `CRM_LISTA_ZASOBOW`
	FOR EACH ROW BEGIN
		IF NEW.PARENT_ID<>OLD.PARENT_ID or NEW.`DESC`<>OLD.`DESC` THEN
			SET NEW.path = CRM_LISTA_ZASOBOW_hierarchy_sys_connect_by_path('/', NEW.PARENT_ID);
			update CRM_WSKAZNIK ik set ik.path_CRM_LISTA_ZASOBOW=CRM_LISTA_ZASOBOW_hierarchy_sys_connect_by_path('/', NEW.ID) where path_CRM_LISTA_ZASOBOW like concat('%',NEW.ID,'%');
			-- update CRM_LISTA_ZASOBOW ik set ik.path=CRM_LISTA_ZASOBOW_hierarchy_sys_connect_by_path('/', ik.ID) where path like concat('%',OLD.`DESC`,'%') and ik.ID<>OLD.ID;
		END IF;
	END

CREATE TRIGGER `_CRM_WSKAZNIK_tree_INSERT` BEFORE INSERT ON `CRM_WSKAZNIK`
	FOR EACH ROW BEGIN
		SET NEW.path_CRM_LISTA_ZASOBOW = CRM_LISTA_ZASOBOW_hierarchy_sys_connect_by_path('/', NEW.ID_ZASOB);
	END

CREATE TRIGGER `_CRM_WSKAZNIK_tree_UPDATE` BEFORE UPDATE ON `CRM_WSKAZNIK`
	FOR EACH ROW BEGIN
		IF NEW.ID_ZASOB<>OLD.ID_ZASOB THEN
			SET NEW.path_CRM_LISTA_ZASOBOW = CRM_LISTA_ZASOBOW_hierarchy_sys_connect_by_path('/', NEW.ID_ZASOB);
		END IF;
	END

CREATE EVENT `_CRM_LISTA_ZASOBOW__CRM_WSKAZNIK_tree_event` ON SCHEDULE EVERY 1 DAY STARTS '2015-05-15 05:00:00' ON COMPLETION NOT PRESERVE ENABLE DO BEGIN
	update  CRM_LISTA_ZASOBOW set path=CRM_LISTA_ZASOBOW_hierarchy_sys_connect_by_path('/',ID);
	update  CRM_WSKAZNIK set path_CRM_LISTA_ZASOBOW=CRM_LISTA_ZASOBOW_hierarchy_sys_connect_by_path('/',ID_ZASOB);
END

CREATE DEFINER=`root`@`localhost` FUNCTION `CRM_LISTA_ZASOBOW_hierarchy_sys_connect_by_path`(`delimiter` TEXT, `node` INT) RETURNS text CHARSET latin2
	READS SQL DATA
	BEGIN
		DECLARE _path TEXT;
		DECLARE _type CHAR(255);
		DECLARE _lvl INT;
		DECLARE _cpath TEXT;
		DECLARE _id INT;
		DECLARE _id_cur INT;
		DECLARE EXIT HANDLER FOR NOT FOUND RETURN _path;
		SET _id = COALESCE(node, @id);
		SET _path = '';
		SET _lvl=1;
		the_loop: LOOP
			SET _lvl =_lvl+1;
			IF _lvl>100 THEN
				RETURN concat('ERROR',_path);
				LEAVE the_loop;
			END IF;
			SELECT  PARENT_ID,`DESC`,ID
				INTO    _id,_type,_id_cur
				FROM    CRM_LISTA_ZASOBOW
				WHERE   id = _id AND COALESCE(id <> @start_with, TRUE);
					SET _path = CONCAT(_type, delimiter, _path);
		END LOOP the_loop;
	END
 */