se/SE/se-lib/Route/UrlAction/TaskManager.php.problems_sync_percent.sql

CREATE DEFINER=`root`@`localhost` PROCEDURE `PROBLEMS__SYNC_PERCENT`()
`proc_label`:BEGIN

SET @updatedCnt = 1;

INSERT IGNORE INTO _SYNC_TASK_PERCENT (`ID_TASK`, `ID_PARENT_TASK`) SELECT `ID`, `PARENT_ID` FROM PROBLEMS;

-- update `_SYNC_TASK_PERCENT` set `A_LAST_SYNC` = NOW(); -- TODO test recurse up - RMME
-- update `_SYNC_TASK_PERCENT` set `A_SYNCHRONIZED` = 1; -- TODO test recurse up - RMME
-- update `_SYNC_TASK_PERCENT` set `A_SYNCHRONIZED` = 0 WHERE `ID_TASK`=1050; -- TODO test recurse up - RMME

UPDATE `_SYNC_TASK_PERCENT` p, `PROBLEMS` t
	SET p.`A_PROBLEM_COMPLETE_PERCENT` = REPLACE(t.`A_PROBLEM_COMPLETE_PERCENT`, "%", "")
		, p.`L_APPOITMENT_PERIOD` = t.`L_APPOITMENT_PERIOD`
		, p.`ID_PARENT_TASK` = t.`PARENT_ID`
	WHERE p.`ID_TASK` = t.`ID`
;

SET @updatedCnt = ROW_COUNT(); SELECT CONCAT("UPDATE 1 : updated ", @updatedCnt); -- DBG

UPDATE `_SYNC_TASK_PERCENT` t
	LEFT JOIN `PROBLEMS` p ON t.`ID_TASK` = p.`ID`
	SET t.A_SYNCHRONIZED = 0
	WHERE t.`A_LAST_SYNC` < p.`A_RECORD_CREATE_DATE` OR t.`A_LAST_SYNC` < p.`A_RECORD_UPDATE_DATE`
;

SET @updatedCnt = ROW_COUNT(); SELECT CONCAT("UPDATE 2 : updated ", @updatedCnt); -- DBG

-- SELECT @c:=COUNT(*) FROM `_SYNC_TASK_PERCENT` WHERE `A_SYNCHRONIZED` = 0;
-- SET @i = 0;
-- WHILE @i < @c DO
-- 	PREPARE stmt FROM "SELECT `ID_TASK` into @parent_id FROM `_SYNC_TASK_PERCENT` WHERE `A_SYNCHRONIZED` = 0 LIMIT ?,1";
-- 	EXECUTE stmt USING @i;
-- 	DEALLOCATE PREPARE stmt;
-- 	select @parent_id;
-- 	WHILE @parent_id <> 0 DO
-- 		SELECT @parent_id := `PARENT_ID` FROM `PROBLEMS` WHERE `ID` = @parent_id;
-- 		UPDATE `_SYNC_TASK_PERCENT` SET `A_SYNCHRONIZED` = 0 WHERE `ID_TASK` = @parent_id;
-- 	END WHILE;
-- 	SET @i = @i +1;
-- END WHILE;

-- set A_SYNCHRONIZED = 0 going up by recurse
SET @i = 0;
SET @loopLomit = 100;
SET @updatedCnt = 1;
WHILE @i < @loopLomit and @updatedCnt > 0 DO
	update `_SYNC_TASK_PERCENT` p
		join `_SYNC_TASK_PERCENT` pp on(pp.`ID_TASK`=p.`ID_PARENT_TASK`)
		set p.`A_SYNCHRONIZED`=0
		where pp.`A_SYNCHRONIZED`!=0
	;
	SET @updatedCnt = ROW_COUNT();
	SELECT CONCAT("LOOP 2 (", @i, "): updated ", @updatedCnt); -- DBG
	SET @i = @i + 1;
END WHILE;

-- UPDATE `_SYNC_TASK_PERCENT` SET `SYNC_SORT_PRIO` = 0;
-- SELECT @c:=COUNT(*) FROM `_SYNC_TASK_PERCENT` p1
-- 	LEFT JOIN `PROBLEMS` t1 ON p1.`ID_TASK` = t1.`PARENT_ID`
-- 	WHERE p1.`A_SYNCHRONIZED` = 0 AND  t1.`ID` IS NULL
-- ;
-- SET @i = 0;
-- WHILE @i < @c DO
-- 	SET @sort_prio = 1000;
-- 	SELECT @i;
-- 	PREPARE stmt FROM "SELECT p1.`ID_TASK` into @parent_id FROM `_SYNC_TASK_PERCENT` p1
-- 		LEFT JOIN `PROBLEMS` t1 ON p1.`ID_TASK` = t1.`PARENT_ID`
-- 		WHERE p1.`A_SYNCHRONIZED` = 0 AND  t1.`ID` IS NULL LIMIT ?, 1"
-- 	;
-- 	EXECUTE stmt USING @i;
-- 	DEALLOCATE PREPARE stmt;
-- 	select @parent_id;
-- 	UPDATE `_SYNC_TASK_PERCENT` SET `SYNC_SORT_PRIO` = @sort_prio WHERE `ID_TASK` = @parent_id;
-- 	WHILE @parent_id <> 0 DO
-- 		SET @sort_prio = @sort_prio - 1;
-- 		SELECT @parent_id := `PARENT_ID` FROM `PROBLEMS` WHERE `ID` = @parent_id;
-- 		UPDATE `_SYNC_TASK_PERCENT` SET `SYNC_SORT_PRIO` = @sort_prio WHERE `ID_TASK` = @parent_id;
-- 	END WHILE;
-- 	SET @i = @i + 1;
-- END WHILE;

-- set SYNC_SORT_PRIO - start from value 1000 for leaf and go up recurse and lover value
-- select p.`ID_TASK`, p.`ID_PARENT_TASK`
-- 		, IF((select count(1) as cnt from `_SYNC_TASK_PERCENT` sp where sp.`ID_PARENT_TASK` = p.`ID_TASK`) > 0, 0, 1000) as sort_prio
-- 	from `_SYNC_TASK_PERCENT` p
-- ;
-- select p.`ID_TASK`, p.`ID_PARENT_TASK`
-- 		, IF((select count(1) as cnt from `PROBLEMS` sp where sp.`PARENT_ID` = p.`ID_TASK`) > 0, 0, 1000) as sort_prio
-- 	from `_SYNC_TASK_PERCENT` p
-- ;
-- set 1000 when is leaf - has no children
SET @i = 0;
SET @loopLomit = 100;
SET @updatedCnt = 1;
SET @sortPrio = 100;
UPDATE `_SYNC_TASK_PERCENT` p
	SET p.`SYNC_SORT_PRIO` = IF((select count(1) as cnt from `PROBLEMS` sp where sp.`PARENT_ID` = p.`ID_TASK`) > 0, 0, @sortPrio)
;
WHILE @i < @loopLomit and @updatedCnt > 0 DO
	update `_SYNC_TASK_PERCENT` p
		join `_SYNC_TASK_PERCENT` pp on(pp.`ID_TASK`=p.`ID_PARENT_TASK`)
		set pp.`SYNC_SORT_PRIO` = IF(@sortPrio > pp.`SYNC_SORT_PRIO` and pp.`SYNC_SORT_PRIO` > 0, pp.`SYNC_SORT_PRIO` - 1, @sortPrio - 1)
		where p.`SYNC_SORT_PRIO` = @sortPrio
	;
	SET @updatedCnt = ROW_COUNT();
	SET @i = @i + 1;
	SET @sortPrio = @sortPrio - 1;
	SELECT CONCAT("LOOP 2 (", @i, "): updated ", @updatedCnt); -- DBG
END WHILE;

UPDATE `_SYNC_TASK_PERCENT` p
	SET p.`CHILDS_COUNT` = (select count(1) as cnt from `PROBLEMS` sp where sp.`PARENT_ID` = p.`ID_TASK`)
;

SELECT @c:=COUNT(*) FROM `_SYNC_TASK_PERCENT` WHERE `A_SYNCHRONIZED` = 0;
SET @i = 0;
SET @id = 0;
SET @childs = 0;
SELECT CONCAT("LOOP 3 @i(", @i, ") @c(", @c, ")"); -- DBG
WHILE @i < @c DO
	SET @i = @i + 1;
	SELECT CONCAT("LOOP 3 ..."); -- DBG
	SELECT `ID_TASK` into @id FROM `_SYNC_TASK_PERCENT` WHERE `A_SYNCHRONIZED` = 0 ORDER BY `SYNC_SORT_PRIO` DESC, `ID_TASK` DESC LIMIT 0,1;
	SELECT CONCAT("LOOP 3 (", @i, "): id ", @id); -- DBG
	SELECT p.`CHILDS_COUNT` into @childs FROM `_SYNC_TASK_PERCENT` p WHERE p.`ID_TASK` = @id;
	SELECT CONCAT("LOOP 3 (", @i, "): id (",@id,") childs(",@childs,")"); -- DBG
	IF @childs = 0 THEN
		SELECT CONCAT("LOOP 3 (", @i, "): LIF0"); -- DBG
		UPDATE `_SYNC_TASK_PERCENT` SET `A_SYNCHRONIZED` = 1, `A_LAST_SYNC` = NOW(), `CHILDS_COUNT` = @childs WHERE `ID_TASK` = @id;
		SELECT CONCAT("LOOP 3 (", @i, "): LIF1"); -- DBG
	ELSE
		SELECT CONCAT("LOOP 3 (", @i, "): LE0"); -- DBG

		SELECT @avg:=AVG(p2.`A_PROBLEM_COMPLETE_PERCENT`), @amount:=SUM(p2.`L_APPOITMENT_PERIOD`) FROM `_SYNC_TASK_PERCENT` p1
			LEFT JOIN `PROBLEMS` t1 ON p1.`ID_TASK` = t1.`PARENT_ID`
			LEFT JOIN `_SYNC_TASK_PERCENT` p2 ON p2.`ID_TASK` = t1.`ID`
			WHERE p1.`A_SYNCHRONIZED` = 0 AND t1.`PARENT_ID` = @id
		;

		SELECT CONCAT("LOOP 3 (", @i, "): LE1"); -- DBG

		SET @avg = ROUND(@avg);
		UPDATE `_SYNC_TASK_PERCENT`
			SET `A_SYNCHRONIZED` = 1
				, `A_PROBLEM_COMPLETE_PERCENT` = @avg
				, `L_APPOITMENT_PERIOD` = @amount
				, `A_LAST_SYNC` = NOW()
				, `CHILDS_COUNT` = @childs
			WHERE `ID_TASK` = @id
		;

		SELECT CONCAT("LOOP 3 (", @i, "): LE2"); -- DBG

		SET @avg = ROUND(@avg/10)*10;
		-- TODO UPDATE `PROBLEMS` SET `A_PROBLEM_COMPLETE_PERCENT` = CONCAT(@avg, "%"), `L_APPOITMENT_PERIOD` = @amount, `A_RECORD_UPDATE_DATE` = NOW() WHERE `ID` = @id;
	END IF;

	SELECT COUNT(*) into @childsdone FROM `_SYNC_TASK_PERCENT` p
		LEFT JOIN `PROBLEMS` t ON t.`ID` = p.`ID_TASK`
		WHERE p.`A_PROBLEM_COMPLETE_PERCENT` = 100 AND t.`PARENT_ID` = @id
	;
	UPDATE `_SYNC_TASK_PERCENT` SET `CHILDS_DONE_COUNT` = @childsdone WHERE `ID_TASK` = @id;
END WHILE;

END