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

<?php

Lib::loadClass('RouteBase');

class Route_FixProjectPath extends RouteBase {

	public function defaultAction() {
		echo 'TODO: F.' . __FUNCTION__;
		//$sqlList['Check'] = "SHOW PROCEDURE STATUS where `Name`='update_project_path_idx_rec'";
	}

	public function runAction() {
		$sql = "call `update_project_path_idx_rec`();";
		// TODO: update fields:
		//   `IN7_MK_BAZA_DYSTRYBUCJI`.`path`
		//   `IN7_DZIENNIK_KORESP`.`path`
		//   `PROBLEMS`.`ID_PROJECT_path`
		$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()));
		}
		die('OK');
	}

	public function reinstallAction() {
		// TODO: reinstall triggers for after insert/update `IN7_MK_BAZA_DYSTRYBUCJI`?
		// TODO: reinstall triggers for after insert/update `IN7_DZIENNIK_KORESP` and `PROBLEMS`?
		$sqlList = array();
		$sqlList['RemoveTable'] = "DROP TABLE IF EXISTS `_project_path_idx`";
		$sqlList['InstallTable'] = "
			CREATE TABLE IF NOT EXISTS `_project_path_idx` (
				`ID` int(11) NOT NULL
				, `P_ID` int(11) NOT NULL DEFAULT '0'
				, `idx_PATH` varchar(255) NOT NULL DEFAULT ''
				, KEY `ID` (`ID`)
				, KEY `P_ID` (`P_ID`)
			) ENGINE=MyISAM DEFAULT CHARSET=latin2
		";
		$sqlList['Remove'] = "DROP PROCEDURE if exists `update_project_path_idx_rec`";
		$sqlList['Create'] = "
			CREATE PROCEDURE `update_project_path_idx_rec`()
			BEGIN
				SET @conf_last_exec_key = 'tbl_indexer_project_last_exec';
				replace into `CRM_CONFIG` (`conf_key`, `conf_val`) values (@conf_last_exec_key, NOW());

				truncate table `_project_path_idx`;
				-- delete from `_project_path_idx`;

				insert into `_project_path_idx` (`ID`,`P_ID`)
					select p.`ID`, p.`P_ID`
					from `IN7_MK_BAZA_DYSTRYBUCJI` p
					where 1=1
				;

				update `_project_path_idx` as p set p.`idx_PATH`=concat('0-', p.`ID`) where p.`P_ID` is null or p.`P_ID`=0;
				SET @i = 0;
				SET @loopLomit = 100;
				SET @pinitCnt = 1;
				WHILE @i < @loopLomit and @pinitCnt > 0 DO
					update `_project_path_idx` p join `_project_path_idx` pp on(pp.`ID`=p.`P_ID`)
						set p.`idx_PATH`=concat(pp.`idx_PATH`, '-', p.`ID`)
						where p.`idx_PATH`='' and pp.`idx_PATH`!='';
					SET @pinitCnt = ROW_COUNT();
					SET @i = @i + 1;
				END WHILE;
			END ;
		";
		$db = DB::getDB();
		if ($db->has_errors()) {
			throw new Exception("DB Errors: " . implode("\n<br>", $db->get_errors()));
		}
		foreach ($sqlList as $sql) {
			$res = $db->query($sql);
			if ($db->has_errors()) {
				throw new Exception("DB Errors: " . implode("\n<br>", $db->get_errors()));
			}
		}
		die('OK');
	}

}