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

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

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

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`
	WHERE p.`ID_TASK` = t.`ID`
;

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`
;

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;

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;

SELECT @c:=COUNT(*) FROM `_SYNC_TASK_PERCENT` WHERE `A_SYNCHRONIZED` = 0;
SET @i = 0;
WHILE @i < @c DO
	SELECT `ID_TASK` into @id FROM `_SYNC_TASK_PERCENT` WHERE `A_SYNCHRONIZED` = 0 ORDER BY `SYNC_SORT_PRIO` DESC LIMIT 0,1;
	SET @i = @i + 1;
	SELECT @childs:=COUNT(*) FROM `_SYNC_TASK_PERCENT` p1
		LEFT JOIN `PROBLEMS` t1 ON p1.`ID_TASK` = t1.`PARENT_ID`
		WHERE p1.`A_SYNCHRONIZED` = 0 AND t1.`PARENT_ID` = @id
	;
	IF @childs = 0 THEN
		UPDATE `_SYNC_TASK_PERCENT` SET A_SYNCHRONIZED = 1, A_LAST_SYNC = NOW(), `CHILDS_COUNT` = @childs WHERE `ID_TASK` = @id;
	ELSE
		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
		;
		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;
		SET @avg = ROUND(@avg/10)*10;
		UPDATE `PROBLEMS` SET `A_PROBLEM_COMPLETE_PERCENT` = CONCAT(@avg, "%"),L_APPOITMENT_PERIOD = @amount, A_RECORD_UPDATE_DATE = NOW() WHERE `ID` = @id;
	END IF;
	SELECT "TEST";
	SELECT @childsdone:=COUNT(*) 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