 SET NAMES utf8;
DELIMITER $$

/* ************************************************************************************************************************************************************************
	BI
************************************************************************************************************************************************************************ */	
DROP TRIGGER IF EXISTS `crm_workflow_n_bi`$$
CREATE DEFINER=`root`@`localhost` TRIGGER `crm_workflow_n_bi` BEFORE INSERT ON `crm_workflow_n` FOR EACH ROW 
A: BEGIN
	IF _TriggerDisabled() THEN LEAVE A; END IF;

	IF @session.globals.WORK.FLOW.Ver IS NULL THEN CALL _Globals_Get_2('WORK.FLOW.Ver', @session.globals.WORK.FLOW.Ver, '2015', '?'); END IF;
	SET @.cwn.NEW.UpdateStatus = NEW.UpdateStatus;
	SET 
		NEW.UpdateStatus = '',
		-- NEW.wfVer = IFNULL(NULLIF(NEW.wfVer, ''), @session.globals.WORK.FLOW.Ver),
		NEW.wfVer = IFNULL(@session.globals.WORK.FLOW.Ver, NEW.wfVer),
		NEW.uCreator = IFNULL(NULLIF(NEW.uCreator, ''), @SYSTEM.Client.Session.log_USER_GUID),
		NEW.dWF = 1 + TIMESTAMPDIFF(DAY, DATE(NEW.D1), DATE(NEW.D2))
	;
	/*	daj to na zewnatrz
	IF NEW.uOwner IS NOT NULL THEN
		SET
			NEW.dAbs = 
				IF(NEW.Alg_Typ = 'W.ABSENCJA',
					(
					SELECT
						SUM(kp.godziny > 0)	-- zliczamy dni z godz. pracy (pracujące)
					FROM `Pracownicy` p, `kalendpracy` k, `kalendpracy_po` kp
					WHERE
						p.GUID = NEW.uOwner
						AND k.Symbol = p.SymbolKalendarza
						AND k.rok = YEAR(NEW.D1)
						AND kp.GUIDNg = k.GUID
						AND kp.Data >= DATE(NEW.D1)
						AND kp.Data <= DATE(NEW.D2)
					),
					NULL
				)
		;
	END IF;
	*/
	IF 
		@.cwn.NEW.UpdateStatus = 'UPD.Temat.Opis' AND
		NEW.uOwner IS NOT NULL
	THEN
		SET
			NEW.Temat = 
				IF(NEW.Temat LIKE '->%',
					CONCAT_WS(' ',
						-- IFNULL(cr.Nazwa, ct.Temat),
						SUBSTR(NEW.Temat, 3, 255),
						'prac.', 
						(SELECT CONCAT_WS(' ', o.Imie1, o.Nazwisko) FROM `osoby` o WHERE o.GUID = NEW.uOwner),
						-- jmam IF(@.crm.Wniosek_Typ = 'W.ABSENCJA', 
						IF(NEW.Alg_Typ = 'W.ABSENCJA', 
							IF(NEW.dAbs IS NOT NULL AND NEW.dAbs <> NEW.dWF,
								CONCAT(NEW.dAbs, ' z'),
								NULL
							),	
							NULL
						),
						-- jmam IF(@.crm.Wniosek_Typ = 'W.ABSENCJA', 
						IF(NEW.Alg_Typ = 'W.ABSENCJA', 
							_Format_DT_Range_Mask(NEW.D1, NEW.D2, '%D %mmm %YH;%D %mmm %YH;',  '[#dni#] [#hour#] ([od][#from#][do][#to#])'),
							-- NULL
							_Format_DT_Range_Mask(NEW.D1, NEW.D2, '%D %mmm %YH;%D %mmm %YH;',  '[#dni#] ([od][#from#][do][#to#])')
						),
						IF(@session.globals.Owner IN ('PLISZKA', 'SEVENET'),	-- na razie po zmianie na view wywalimy to
							CONCAT('- ', NULLIF(NEW.ExtDescr, '')), -- jako opis dodatkowy dołączny do pola opis który jest tu wypełniany
							NULL
						),
						NULL
					),
				NEW.Temat
			),
			NEW.Opis = 
				IF(NEW.Opis LIKE '->%',
					CONCAT_WS(' ',
						SUBSTR(NEW.Opis, 3, 255),
						-- jmam IF(@.crm.Wniosek_Typ = 'W.ABSENCJA', 
						IF(NEW.Alg_Typ = 'W.ABSENCJA', 
							IF(NEW.dAbs IS NOT NULL AND NEW.dAbs <> NEW.dWF,
								CONCAT(NEW.dAbs, ' z'),
								NULL
							),	
							NULL
						),
						-- jmam IF(@.crm.Wniosek_Typ = 'W.ABSENCJA', 
						IF(NEW.Alg_Typ = 'W.ABSENCJA', 
							_Format_DT_Range_Mask(NEW.D1, NEW.D2, '%D %mmm %YH;%D %mmm %YH;',  '[#dni#] [#hour#] ([od][#from#][do][#to#])'),
							-- NULL
							_Format_DT_Range_Mask(NEW.D1, NEW.D2, '%D %mmm %YH;%D %mmm %YH;',  '[#dni#] ([od][#from#][do][#to#])')
						),
						CONCAT('- ', NULLIF(NEW.ExtDescr, '')), -- jako opis dodatkowy dołączny do pola opis który jest tu wypełniany
						NULL
					),
					NEW.Opis
				)
		;	
	END IF;
	
	/*
	IF NEW.Start_Status != '' AND NEW.cwStatus = '' THEN
		SET NEW.cwStatus = NEW.Start_Status;
	ELSEIF NEW.Start_Status = '' AND NEW.cwStatus != '' THEN
		SET NEW.cwStatus = '';
	END IF;

	IF IFNULL((SELECT wd.Is_Ending FROM `crm_workflow_n_def` wd WHERE wd.Def = NEW.Def AND wd.Status = NEW.Start_Status), FALSE) THEN
		SET NEW.Response = 'RESPONSE_100';
	END IF;
	*/
END;
$$

/* ************************************************************************************************************************************************************************
	AI
************************************************************************************************************************************************************************ */	
DROP TRIGGER IF EXISTS `crm_workflow_n_ai`$$
CREATE DEFINER=`root`@`localhost` TRIGGER `crm_workflow_n_ai` AFTER INSERT ON `crm_workflow_n` FOR EACH ROW 
A: BEGIN
	IF _TriggerDisabled() THEN LEAVE A; END IF;
--	INIT Lock trigger zmienna @session.trigger.xxx
	--	IF IFNULL(@session.trigger.crm_workflow_n, FALSE) THEN LEAVE A; END IF;
	--	SET @session.trigger.crm_workflow_n = TRUE;

		
				SET @session.globals.crm_workflow_n.Ver = IFNULL(@session.globals.crm_workflow_n.Ver, '1.0');
	
	SET @.cwn.NEW.UpdateStatus = '';
    /*  XI 2024 Dla starszych serwerów to nie zadziała zatem select into...
		INSERT IGNORE INTO `crm_workflow_confirm`
		(wGUID, IDN_workflow, UserGUID, uGUID, uType, Poziom)
		SELECT
			s.wGUID, s.IDN_workflow, s.UserGUID, s.uGUID, s.uType, s.Poziom
		FROM (
			SELECT
				NEW.GUID AS wGUID, 
				0 AS IDN_workflow, 
				ut.uType AS UserGUID,
				IF(ut.uType = 'uCreator', NEW.uCreator, NEW.uOwner) AS uGUID,
				ut.uType, ut.Poziom
			FROM 
			(SELECT 'uCreator' AS uType, 0 AS Poziom UNION SELECT 'uOwner' AS uType, 2 AS Poziom) ut
			WHERE
				TRUE
		) s
		;
       */
		INSERT IGNORE INTO `crm_workflow_confirm`
		(wGUID, IDN_workflow, UserGUID, uGUID, uType, Poziom)
			SELECT
				NEW.GUID AS wGUID, 
				0 AS IDN_workflow, 
				ut.uType AS UserGUID,
				IF(ut.uType = 'uCreator', NEW.uCreator, NEW.uOwner) AS uGUID,
				ut.uType, ut.Poziom
			FROM 
			(SELECT 'uCreator' AS uType, 0 AS Poziom UNION SELECT 'uOwner' AS uType, 2 AS Poziom) ut
			WHERE
				TRUE
        ;
--	FINAL UnLock trigger zmienna @session.trigger.xxx
	--	SET @session.trigger.crm_workflow_n = FALSE;
END;
$$

/* ************************************************************************************************************************************************************************
	BU
************************************************************************************************************************************************************************ */	
DROP TRIGGER IF EXISTS `crm_workflow_n_bu`$$
CREATE DEFINER=`root`@`localhost` TRIGGER `crm_workflow_n_bu` BEFORE UPDATE ON `crm_workflow_n` FOR EACH ROW 
A: BEGIN
	IF _TriggerDisabled() THEN LEAVE A; END IF;

	/*
	IF IFNULL((SELECT wd.Is_Ending FROM `crm_workflow_n_def` wd WHERE wd.Def = NEW.Def AND wd.Status = NEW.Start_Status), FALSE) THEN
		SET NEW.Response = 'RESPONSE_100';
	END IF;
	*/
	SET @.cwn.NEW.UpdateStatus = NEW.UpdateStatus;
	SET 
		NEW.UpdateStatus = '',
		NEW.uCreator = IFNULL(NULLIF(NEW.uCreator, ''), @SYSTEM.Client.Session.log_USER_GUID)
	;
	SET @.NEW.uOwner.ch := !(NEW.uOwner <=> OLD.uOwner);
	IF (@.NEW.D1D2.ch := (!(NEW.D1 <=> OLD.D1) OR !(NEW.D2 <=> OLD.D2))) OR NEW.dWF <= 0 THEN
		SET NEW.dWF = 1 + TIMESTAMPDIFF(DAY, DATE(NEW.D1), DATE(NEW.D2));
	END IF;
	
	/*	daj to na zewnatrz
	IF @.NEW.D1D2.ch OR @.NEW.uOwner.ch THEN
		SET
			NEW.dAbs = 
				IF(NEW.Alg_Typ = 'W.ABSENCJA',
					(
					SELECT
						SUM(kp.godziny > 0)	-- zliczamy dni z godz. pracy (pracujące)
					FROM `Pracownicy` p, `kalendpracy` k, `kalendpracy_po` kp
					WHERE
						p.GUID = NEW.uOwner
						AND k.Symbol = p.SymbolKalendarza
						AND k.rok = YEAR(NEW.D1)
						AND kp.GUIDNg = k.GUID
						AND kp.Data >= DATE(NEW.D1)
						AND kp.Data <= DATE(NEW.D2)
					),
					NULL
				)
		;
	END IF;
	*/
	SET @.NEW.dAbs.ch := !(NEW.dAbs <=> OLD.dAbs);
	IF 
		-- @.cwn.NEW.UpdateStatus = 'UPD.Temat.Opis' AND
		(
		@.cwn.NEW.UpdateStatus = 'UPD.Temat.Opis' OR
		@.cwn.NEW.UpdateStatus = 'UPD.Osoby' OR
		@.NEW.D1D2.ch OR
		@.NEW.dAbs.ch OR
		@.NEW.uOwner.ch
		)
	THEN
		SET
			@.NEW.Temat.Opis = TRIM(
				IF(NEW.Temat LIKE '->%',
					SUBSTR(NEW.Temat, 3, 255),
					SUBSTR(NEW.Temat, 1, LOCATE('prac.', NEW.Temat) - 1)
				))
		;
		SET
			NEW.Temat = 
				CONCAT_WS(' ',
					-- IFNULL(cr.Nazwa, ct.Temat),
					@.NEW.Temat.Opis,
					'prac.', 
					(SELECT CONCAT_WS(' ', o.Imie1, o.Nazwisko) FROM `osoby` o WHERE o.GUID = NEW.uOwner),
					-- jmam IF(@.crm.Wniosek_Typ = 'W.ABSENCJA', 
					IF(NEW.Alg_Typ = 'W.ABSENCJA', 
						IF(NEW.dAbs IS NOT NULL AND NEW.dAbs <> NEW.dWF,
							CONCAT(NEW.dAbs, ' z'),
							NULL
						),	
						NULL
					),
					-- jmam IF(@.crm.Wniosek_Typ = 'W.ABSENCJA', 
					IF(NEW.Alg_Typ = 'W.ABSENCJA', 
						_Format_DT_Range_Mask(NEW.D1, NEW.D2, '%D %mmm %YH;%D %mmm %YH;',  '[#dni#] [#hour#] ([od][#from#][do][#to#])'),
						-- NULL
						_Format_DT_Range_Mask(NEW.D1, NEW.D2, '%D %mmm %YH;%D %mmm %YH;',  '[#dni#] ([od][#from#][do][#to#])')
					),
					IF(@session.globals.Owner IN ('PLISZKA', 'SEVENET'),	-- na razie po zmianie na view wywalimy to
						CONCAT('- ', NULLIF(NEW.ExtDescr, '')), -- jako opis dodatkowy dołączny do pola opis który jest tu wypełniany
						NULL
					),
					NULL
				),
			NEW.Opis = 
				CONCAT_WS(' ',
					@.NEW.Temat.Opis,
					-- jmam IF(@.crm.Wniosek_Typ = 'W.ABSENCJA', 
					IF(NEW.Alg_Typ = 'W.ABSENCJA', 
						IF(NEW.dAbs IS NOT NULL AND NEW.dAbs <> NEW.dWF,
							CONCAT(NEW.dAbs, ' z'),
							NULL
						),	
						NULL
					),
					-- jmam IF(@.crm.Wniosek_Typ = 'W.ABSENCJA', 
					IF(NEW.Alg_Typ = 'W.ABSENCJA', 
						_Format_DT_Range_Mask(NEW.D1, NEW.D2, '%D %mmm %YH;%D %mmm %YH;',  '[#dni#] [#hour#] ([od][#from#][do][#to#])'),
						-- NULL
						_Format_DT_Range_Mask(NEW.D1, NEW.D2, '%D %mmm %YH;%D %mmm %YH;',  '[#dni#] ([od][#from#][do][#to#])')
					),
					CONCAT('- ', NULLIF(NEW.ExtDescr, '')), -- jako opis dodatkowy dołączny do pola opis który jest tu wypełniany
					NULL
				)
		;	
			
	END IF;
	/*
	SET NEW.cwStatus = IF(NEW.cwStatus IN ('', 'BACK'), NEW.Start_Status, NEW.cwStatus);
	
	
	IF !(NEW.cwStatus <=> OLD.cwStatus) THEN
		SET NEW.DecisionAt = NOW();	-- 	od teraz nie wolno usunac tego rekordu z powodu anulowania decyzji punktu = Prev_IDN_workflow ()
	END IF;
	
	*/
	SET NEW.UpdateStatus = '';	--	zawsze
	
END;
$$

/* ************************************************************************************************************************************************************************
	AU
************************************************************************************************************************************************************************ */	
DROP TRIGGER IF EXISTS `crm_workflow_n_au`$$
CREATE DEFINER=`root`@`localhost` TRIGGER `crm_workflow_n_au` AFTER UPDATE ON `crm_workflow_n` FOR EACH ROW 
A: BEGIN
	IF _TriggerDisabled() THEN LEAVE A; END IF;
--	INIT Lock trigger zmienna @session.trigger.xxx
	--	IF IFNULL(@session.trigger.crm_workflow_n, FALSE) THEN LEAVE A; END IF;
	--	SET @session.trigger.crm_workflow_n = TRUE;


	SET @.cwn.NEW.UpdateStatus = '';
	IF !(NEW.uOwner <=> OLD.uOwner) THEN
		INSERT IGNORE INTO `crm_workflow_confirm`
		(wGUID, IDN_workflow, UserGUID, uGUID, uType, Poziom)
		SELECT
			wGUID, IDN_workflow, UserGUID, uGUID, uType, Poziom
		FROM (
			SELECT
				NEW.GUID AS wGUID, 
				0 AS IDN_workflow, 
				ut.uType AS UserGUID,
				IF(ut.uType = 'uCreator', NEW.uCreator, NEW.uOwner) AS uGUID,
				ut.uType, ut.Poziom
			FROM 
			(SELECT 'uCreator' AS uType, 0 AS Poziom UNION SELECT 'uOwner' AS uType, 2 AS Poziom) ut
			WHERE
				TRUE
		) s
		ON DUPLICATE KEY UPDATE
			uGUID = s.uGUID,
			uType = s.uType,
			Poziom = s.Poziom
		;
	END IF;
--	FINAL UnLock trigger zmienna @session.trigger.xxx
	--	SET @session.trigger.crm_workflow_n = FALSE;
END;
$$

/* ************************************************************************************************************************************************************************
	BD
************************************************************************************************************************************************************************ */	

/* ************************************************************************************************************************************************************************
	AD
************************************************************************************************************************************************************************ */	
DROP TRIGGER IF EXISTS `crm_workflow_n_ad`$$
CREATE DEFINER = `root`@`localhost` TRIGGER `crm_workflow_n_ad` AFTER DELETE ON `crm_workflow_n` FOR EACH ROW 
A: BEGIN
	IF _TriggerDisabled() THEN LEAVE A; END IF;
--	INIT Lock trigger zmienna @session.trigger.xxx
	--	IF IFNULL(@session.trigger.crm_workflow_n, FALSE) THEN LEAVE A; END IF;
	--	SET @session.trigger.crm_workflow_n = TRUE;
	DELETE FROM `crm_workflow_confirm` WHERE wGUID = OLD.GUID AND IDN_workflow = 0;
	DELETE FROM `crm_workflow` WHERE `commisionGUID` = OLD.GUID;

--	FINAL UnLock trigger zmienna @session.trigger.xxx
	--	SET @session.trigger.crm_workflow_n = FALSE;
END;
$$

/*
$Log: crm_workflow_n.sql,v $
Revision 1.3  2024/05/13 18:01:54  jmam
SQL zmiana V 2024 $SEND$

Revision 1.2  2024/04/27 09:13:04  jmam
Inicjacja WORK.FLOW.Ver = 2024.04 $SEND$

*/
