se/SE/se-lib/Route/FixCrmProcesInitIdx/update_proces_init_idx_rec.sql

CREATE DEFINER=`root`@`localhost` PROCEDURE `update_proces_init_idx_rec`()
BEGIN
	SET @enabled = FALSE;
	SET @conf_last_exec_lock_key = 'tbl_indexer_CRM_PROCES_last_exec_lock_date';
	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';
	-- if already runnig mysql will throw 'Commands out of sync; you cant run this command now'
	IF (select count(1) from `CRM_CONFIG` where `conf_key`=@conf_last_exec_lock_key and `conf_val`!='')>0 THEN
		UPDATE `P5-MSG:Route_FixCrmProcesInitIdx:ERROR: Already executed` SET x=1;
	ELSE BEGIN

		-- CONF.1--set-start-time-in-config
		replace into `CRM_CONFIG` (`conf_key`, `conf_val`) values (@conf_last_exec_lock_key, NOW()), (@conf_last_exec_start_key, NOW());

		-- I.1--truncate-table
		truncate table `CRM_PROCES_idx_TEMP`;

		-- I.2--fill-CRM_PROCES_idx_TEMP
		insert into `CRM_PROCES_idx_TEMP` ( `ID_PROCES`, `TYPE`, `PARENT_ID`, `idx_PROCES_INIT_ID`, `idx_MAIN_PROCES_INIT_ID` )
			select p.`ID` as `ID_PROCES`
				, p.`TYPE` as `TYPE`
				, p.`PARENT_ID` as `PARENT_ID`
				, IF( p.`TYPE` = 'PROCES_INIT', p.`ID`, 0 ) as `idx_PROCES_INIT_ID`
				, IF( p.`TYPE` = 'PROCES_INIT', p.`ID`, 0 ) as `idx_MAIN_PROCES_INIT_ID`
			from `CRM_PROCES` p
			where p.`A_STATUS` in ( 'NORMAL', 'WAITING' )
		;

		-- G.1--before-while-2-set-initial-proc_with_groups_id
		update `CRM_PROCES_idx_TEMP` 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
		;

		-- G.2--while-2-loop-update-proc_with_groups_id-from-parent
		SET @i = 0;
		SET @loopLomit = 100;
		SET @groupCnt = 1;
		WHILE @i < @loopLomit and @groupCnt > 0 DO
			update `CRM_PROCES_idx_TEMP` as p
				join `CRM_PROCES_idx_TEMP` 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;

		-- P_INIT_ID.1--fill_idx_INIT
		SET @i = 0;
		SET @loopLomit = 100;
		SET @pinitCnt = 1;
		WHILE @i < @loopLomit and @pinitCnt > 0 DO
			update `CRM_PROCES_idx_TEMP` p
				join `CRM_PROCES_idx_TEMP` pp on ( pp.`ID_PROCES` = p.`PARENT_ID` )
				set p.`idx_PROCES_INIT_ID` = IF( p.`idx_PROCES_INIT_ID` > 0, p.`idx_PROCES_INIT_ID`, pp.`idx_PROCES_INIT_ID` )
					, p.`idx_MAIN_PROCES_INIT_ID` = pp.`idx_MAIN_PROCES_INIT_ID`
				-- // TODO:?: update here `idx_MAIN_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;

		-- GOTO_AND_RET.1--from-7-fill-goto_and_return
		insert ignore into `CRM_PROCES_idx_TEMP` ( `ID_PROCES`, `TYPE`, `PARENT_ID`, `idx_PROCES_INIT_ID`, `idx_MAIN_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`
				, i.`idx_MAIN_PROCES_INIT_ID` as `idx_MAIN_PROCES_INIT_ID`
				, IF( i.`idx_PROCES_WITH_GROUPS_ID` is null, gi.`idx_PROCES_WITH_GROUPS_ID`, i.`idx_PROCES_WITH_GROUPS_ID` ) as `idx_PROCES_WITH_GROUPS_ID`
			from `CRM_PROCES` p
				join `CRM_PROCES_idx_TEMP` i on ( i.`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
					)
				join `CRM_PROCES_idx_TEMP` gi on ( gi.`ID_PROCES` = g.`ID` )
			where p.`A_STATUS` in ( 'NORMAL', 'WAITING' )
		;

		-- GOTO_AND_RET.2--fill-childrens-of-goto_and_return
		insert ignore into `CRM_PROCES_idx_TEMP` ( `ID_PROCES`, `TYPE`, `PARENT_ID`, `idx_PROCES_INIT_ID`, `idx_MAIN_PROCES_INIT_ID`, `idx_PROCES_WITH_GROUPS_ID` )
			select ic.`ID_PROCES`
				, 'GOTO_AND_RETURN_CHILD' as `TYPE`
				, ic.`PARENT_ID` as `PARENT_ID`
				, ic.`idx_PROCES_INIT_ID` as `idx_PROCES_INIT_ID`
				, i.`idx_MAIN_PROCES_INIT_ID` as `idx_MAIN_PROCES_INIT_ID`
				, IF( ic.`idx_PROCES_WITH_GROUPS_ID` is null, i.`idx_PROCES_WITH_GROUPS_ID`, ic.`idx_PROCES_WITH_GROUPS_ID` ) as `idx_PROCES_WITH_GROUPS_ID`
			from `CRM_PROCES_idx_TEMP` i
				join `CRM_PROCES_idx_TEMP` ic on (
					ic.`idx_PROCES_INIT_ID` = i.`ID_PROCES`
					and ic.`ID_PROCES` != i.`ID_PROCES`
				)
			where i.`TYPE` = 'GOTO_AND_RETURN'
		;

		-- GOTO_AND_RET.3--fill-goto_and_return_lvl2
		-- gc  p(5105) is GOTO_AND_RETURN_CHILD pID(5061) pI(5039) pIM(5040) pG(5040)
		-- gc  p(5105) is GOTO_AND_RETURN_CHILD pID(5061) pI(5039) pIM(5100) pG(5100)
		-- gg  p(5103) is GOTO_AND_RETURN       pID(5105) pI(5103) pIM(5039) pG(NULL)
		-- +   p(5103) is GOTO_AND_RETURN_LVL2  pID(5105) pI(5103) pIM(5040) pG(5040)
		-- +   p(5103) is GOTO_AND_RETURN_LVL2  pID(5105) pI(5103) pIM(5100) pG(5100)
		insert ignore into `CRM_PROCES_idx_TEMP`  ( `ID_PROCES`, `TYPE`, `PARENT_ID`, `idx_PROCES_INIT_ID`, `idx_MAIN_PROCES_INIT_ID`, `idx_PROCES_WITH_GROUPS_ID` )
			select gg.`ID_PROCES` as `ID_PROCES`
				, 'GOTO_AND_RETURN_LVL2' as `TYPE`
				, gg.`PARENT_ID` as `PARENT_ID`
				, gg.`idx_PROCES_INIT_ID` as `idx_PROCES_INIT_ID`
				, gc.`idx_MAIN_PROCES_INIT_ID` as `idx_MAIN_PROCES_INIT_ID`
				, IF( gg.`idx_PROCES_WITH_GROUPS_ID` is null, gc.`idx_PROCES_WITH_GROUPS_ID`, gg.`idx_PROCES_WITH_GROUPS_ID` ) as `idx_PROCES_WITH_GROUPS_ID`
		--		, 'gc', gc.*, 'gg', gg.*
			from `CRM_PROCES_idx_TEMP` gg
				join `CRM_PROCES_idx_TEMP` gc on ( gc.`ID_PROCES` = gg.`PARENT_ID` )
			where gg.`TYPE` = 'GOTO_AND_RETURN'
				and gc.`TYPE` = 'GOTO_AND_RETURN_CHILD'
		;

		-- GOTO_AND_RET.3--fill-childrens-of-goto_and_return_lvl2
		-- i   p(5103) is GOTO_AND_RETURN_LVL2       pID(5105) pI(5103) pIM(5040) pG(5040)
		-- i   p(5103) is GOTO_AND_RETURN_LVL2       pID(5105) pI(5103) pIM(5100) pG(5100)
		-- ic  p(5104) is GOTO_AND_RETURN_CHILD      pID(5103) pI(5103) pIM(5039) pG(NULL)
		-- ic  p(5104) is GOTO_AND_RETURN_CHILD      pID(5103) pI(5103) pIM(5039) pG(NULL)
		-- +   p(5104) is GOTO_AND_RETURN_CHILD_LVL2 pID(5103) pI(5103) pIM(5100) pG(5100)
		-- +   p(5104) is GOTO_AND_RETURN_CHILD_LVL2 pID(5103) pI(5103) pIM(5040) pG(5040)
		insert ignore into `CRM_PROCES_idx_TEMP` ( `ID_PROCES`, `TYPE`, `PARENT_ID`, `idx_PROCES_INIT_ID`, `idx_MAIN_PROCES_INIT_ID`, `idx_PROCES_WITH_GROUPS_ID` )
			select ic.`ID_PROCES`
				, 'GOTO_AND_RETURN_CHILD_LVL2' as `TYPE`
				, ic.`PARENT_ID` as `PARENT_ID`
				, ic.`idx_PROCES_INIT_ID` as `idx_PROCES_INIT_ID`
				, i.`idx_MAIN_PROCES_INIT_ID` as `idx_MAIN_PROCES_INIT_ID`
				, IF( ic.`idx_PROCES_WITH_GROUPS_ID` is null, i.`idx_PROCES_WITH_GROUPS_ID`, ic.`idx_PROCES_WITH_GROUPS_ID` ) as `idx_PROCES_WITH_GROUPS_ID`
		--		, 'i', i.*, 'ic', ic.*
			from `CRM_PROCES_idx_TEMP` i
				join `CRM_PROCES_idx_TEMP` ic on (
					ic.`idx_PROCES_INIT_ID` = i.`ID_PROCES`
					and ic.`ID_PROCES` != i.`ID_PROCES`
					and ic.`TYPE` = 'GOTO_AND_RETURN_CHILD'
				)
			where i.`TYPE` = 'GOTO_AND_RETURN_LVL2'
		;

		-- IDX.1--clear-CRM_PROCES_idx
		truncate table `CRM_PROCES_idx`;

		-- IDX.2--update-CRM_PROCES_idx-from-temp
		insert into `CRM_PROCES_idx` ( `ID_PROCES`, `PARENT_ID`, `TYPE`, `idx_PROCES_INIT_ID`, `idx_MAIN_PROCES_INIT_ID`, `idx_PROCES_WITH_GROUPS_ID` )
			select i.`ID_PROCES`, i.`PARENT_ID`, i.`TYPE`, i.`idx_PROCES_INIT_ID`, i.`idx_MAIN_PROCES_INIT_ID`, i.`idx_PROCES_WITH_GROUPS_ID`
			from `CRM_PROCES_idx_TEMP` i
			where i.`idx_PROCES_INIT_ID` > 0 and i.`idx_PROCES_WITH_GROUPS_ID` is not null
		;


		-- TODO OFF: time ~0.1 P.1--update-CRM_PROCES-idx_PROCES_INIT_ID
		-- 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)
		-- ;

		-- GP.1--clear-CRM_PROCES_idx_GROUP_to_PROCES
		truncate table `CRM_PROCES_idx_GROUP_to_PROCES`;

		-- GP.2--fill-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')
		;

		-- GP.2--clear-CRM_PROCES_idx_GROUP_to_PROCES_PERM
		truncate table `CRM_PROCES_idx_GROUP_to_PROCES_PERM`;

		-- GP.3--fill-CRM_PROCES_idx_GROUP_to_PROCES_PERM
		insert into `CRM_PROCES_idx_GROUP_to_PROCES_PERM` ( `ID_PROCES`, `ID_GROUP`, `HAS_PERM_R`, `HAS_PERM_X` )
			select i.`ID_PROCES` as `_ID_PROCES`
				, z.`ID` as `_ID_GROUP`
				, IF( przyp.`FORM_TREAT` & 2, 1, 0 ) as `_HAS_PERM_R`
				, IF( przyp.`FORM_TREAT` & 8, 1, 0 ) as `_HAS_PERM_X`
			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` )
		--	where i.`TYPE` in('PROCES_INIT', 'GOTO_AND_RETURN')
			ON DUPLICATE KEY UPDATE `HAS_PERM_R` = GREATEST( `HAS_PERM_R`, IF( przyp.`FORM_TREAT` & 2, 1, 0 ) )
				, `HAS_PERM_X` = GREATEST( `HAS_PERM_X`, IF( przyp.`FORM_TREAT` & 8, 1, 0 ) )
		;

		-- CONF.2--set-end-time-in-config
		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
		CALL CRM_UI_MSGS__markTableEveryoneAsExecuted( 'update_proces_init_idx_rec', 'FixCrmProcesInitIdx', 'Update perms', 'CRM_PROCES' );

	END ; END IF ;
END ;