

DELIMITER $$

DROP PROCEDURE IF EXISTS `crm_porta_fide_tools`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `crm_porta_fide_tools`(
	IN _MODE VARCHAR(250),	 --	_handle_oper INSERT, EDIT, DELETE,
	IN _Key VARCHAR(38),
	IN _Key2 VARCHAR(150),
	IN _Key3 VARCHAR(150),	--	najczescie _Cul_Idn a przy nowym to GUID Szablonu
	IN _Param_A VARCHAR(150),	--	tutaj np. Temat
	IN _Param_B TEXT,	
	IN _Param_C VARCHAR(150),	
	IN _Param_D VARCHAR(150),	
	IN _Param_E VARCHAR(150)	
)
/*
*/	
S_crm_porta_fide_tools: BEGIN
	
	DECLARE _OsobaGUID, _crmGUID VARCHAR(38);
	DECLARE _confirm_status VARCHAR(150);
	DECLARE _Archiwum, _Komentarz VARCHAR(255);
	DECLARE _IDN_workflow, _Rodzaj, _PodRodzaj INTEGER;
	
	IF _MODE = 'GET.DATE.RANGE.Result' THEN
	/************************************************************************************************************************************************
		Tworzy zdarzenie _Key przez osobe _Key2 z szablonu _Key3.
		CALL crm_web_pracownicy_tools('GET.DATE.RANGE.Result', 'crmGUID', 'Prac70', '', 'DataOD', 'DataDO', '', '', '');
		_Key		_crmGUID
		_Key2		_OsobaGUID
		_Param_A	Data OD
		_Param_B	Data Do
	************************************************************************************************************************************************/
		SET _crmGUID = IFNULL(_Key, '');
		SET _OsobaGUID = IF(IFNULL(_Key2, '') = '', @session.CurrOsobaGUID, _Key2);
		CALL crm_INSERT('z.szablonu', _crmGUID, _Key3, 16, 0, _Param_A, _Param_B, 0.5, '', NULL, NULL, _OsobaGUID, '', '', '', 0, 4, 'new', 'new');
		SELECT 
			a.AbsencjaOd, a.AbsencjaDo, IFNULL(ad.definicja, '?') AS Opis
		FROM `absencje` a
		LEFT JOIN `absencje_dict` ad ON ad.Idn = ABS(a.Rodzaj)
		WHERE
			a.GUIDpr = _OsobaGUID
			AND
			_TimeRange('conflict', DATE(_Param_A), DATE(_Param_B), a.AbsencjaOd, a.AbsencjaDo, '', '')
			AND a.GUID != _crmGUID
		;		
	END IF;
	
	IF _MODE = 'crm.NEW.one' THEN
	/************************************************************************************************************************************************
		Tworzy zdarzenie _Key przez osobe _Key2 z szablonu _Key3.
		CALL crm_porta_fide_tools('crm.NEW.one', 'NewcrmGUID', 'OsobaGUID', 'SzablonGUID', '', '', '', '', '');
		CALL crm_porta_fide_tools('crm.NEW.one', 'webdoc.kuku', 'Prac70', 'webdoc.16.0', 'Temat', 'Opis szerszy', '2', '', '');
		_Key		_crmGUID
		_Key2		_OsobaGUID
		_Key3		Szablon
		_Param_A	- Temat
		_Param_B	Uwagi/Opis
		_Param_C	Typ (z `absencje_dict`)
		-- _Param_D	Inicjalny status
	************************************************************************************************************************************************/
		CALL _Session_Refresh_Variables('', '', '');
		SET _crmGUID = IFNULL(_Key, '');
		SET _OsobaGUID = IF(IFNULL(_Key2, '') = '', @session.CurrOsobaGUID, _Key2);
		CALL crm_INSERT('z.szablonu', _crmGUID, _Key3, 16, 0, _Param_A, _Param_B, 0.5, '', NULL, NULL, _OsobaGUID, '', '', '', 0, 4, 'new', 'new');
		
		SELECT 
			c.Rodzaj, c.PodRodzaj, cr.Def_confirm_statuses 
		FROM `crm_commisions` c 
		INNER JOIN `crm_rodzaje` cr ON cr.Rodzaj = c.Rodzaj AND cr.PodRodzaj = c.PodRodzaj
		WHERE 
			c.GUID = _crmGUID
		INTO 
			@.c.Rodzaj, @.c.PodRodzaj, @.cr.Def_confirm_statuses
		;
		
		IF @.c.Rodzaj = 16 AND @.c.PodRodzaj = 0 THEN
			CALL crm_usr_links_INSERT('z.hr_self_links', _crmGUID, '', _OsobaGUID, 1, 0, NULL, NULL, 'sent', '');
		ELSEIF @.c.Rodzaj = 16 AND @.c.PodRodzaj = 1 THEN
			CALL crm_usr_links_INSERT('z.hr_self_links', _crmGUID, '', _OsobaGUID, 3, 0, NULL, NULL, '', '');
		END IF;
		
	-- NOWY WorkFLOW
		CALL crm_workflow_TOOLS('Insert.New', _crmGUID, 'new', '', '', '', '');
		
		UPDATE `crm_commisions` c
		SET
			c.Typ = _Param_C,
			c.confirm_status = 'new', -- tutaj zawsze new,
			c.www_tag_text = 'abcdefgh'
		WHERE
			c.GUID = _crmGUID
		;
		
		SET @_IDN_workflow = NULL;
		SELECT 
			IDN 
		FROM `crm_workflow` 
		WHERE 
			commisionGUID = _crmGUID
		LIMIT 1 
		INTO
			@_IDN_workflow
		;
		
		SELECT @_IDN_workflow AS idn;	--	zwracam IDN z `crm_workflow` aby mozna teraz zrobić 'crm.MODIFY.one'
	END IF;

	IF _MODE = 'crm.MODIFY.one' THEN
	/************************************************************************************************************************************************
		Modyfikujemy zdarzenie _Key przez osobe _Key2
		CALL crm_porta_fide_tools('crm.MODIFY.one', 'NewcrmGUID', 'OsobaGUID', '233333', '', '', '', '', '');
		CALL crm_porta_fide_tools('crm.MODIFY.one', 'webdoc.kuku', 'Prac70', '233333', 'Temat', 'Uwagi', 'sent', 'DataOD', 'DataDO');
		_Key		_crmGUID
		_Key2		_OsobaGUID
		_Key3		to _IDN_workflow czyli IDN w `crm_workflow`
		_Param_A	- Temat
		_Param_B	Uwagi/Opis
		_Param_C	Inicjalny status
		_Param_D	Data OD
		_Param_E	Data Do
	************************************************************************************************************************************************/
		SET _crmGUID = IFNULL(_Key, '');
		SET _OsobaGUID = IF(IFNULL(_Key2, '') = '', @session.CurrOsobaGUID, _Key2);
		SET _IDN_workflow = IF(IFNULL(_Key3, '') = '', 0, _Key3);
		
		UPDATE `crm_commisions` c
		SET	
			c.Temat = IF(_Param_A = '', c.Temat, _Param_A),
			c.Opis = IF(_Param_B = '', c.Opis, _Param_B),
			c.StartTime = IF(_Param_D = '', c.StartTime, _Param_D),
			c.EndTime = IF(_Param_E = '', c.EndTime, _Param_E)
		WHERE
			c.GUID = _crmGUID
		;
		
		IF _IDN_workflow = 0 THEN
			SET _IDN_workflow = NULL;
			SELECT 
				IDN 
			FROM `crm_workflow` 
			WHERE 
				commisionGUID = _crmGUID
				AND Start_Status = 'new'
			LIMIT 1 
			INTO
				_IDN_workflow
			;
		END IF;
		IF _Param_C = 'sent' AND IFNULL(_IDN_workflow, 0) > 0 THEN -- ?? poco o to pytac _confirm_status = 'new' THEN
		--	 czasem te 'crm.MODIFY.one' jest wolany po NEW i przekazywany jest SENT aby od razu przesłać 
			SET _confirm_status = _Param_C;
			CALL crm_workflow_TOOLS('confirm.Response', _IDN_workflow, _OsobaGUID, _confirm_status, '', '', '(wysłany zaraz po utworzeniu)');
		END IF;

		CALL crm_porta_fide_tools('Update.Wniosek.After.Confirm.After.Edit', _crmGUID, '', '', '', '', '', '', '');
		-- select @XXX;
	END IF;

	
	IF _MODE = 'Update.Wniosek.After.Confirm.After.Edit' THEN
	/************************************************************************************************************************************************
		Update crm. frekw, absenci etc. wniosku _Key po edycji przez osobe _Key2 w Idn crm_usr_links = _Key3
		CALL crm_porta_fide_tools('Update.Wniosek.After.Confirm.After.Edit', 'crmGUID', 'Prac50', '279854', '', '', '', '', '');
		_Key		_crmGUID
		-- _Key2		Osoba
		-- _Key3		to _Cul_Idn czyli IDN w `crm_usr_links`
	************************************************************************************************************************************************/
		SET _crmGUID = IFNULL(_Key, '');
		-- SET _OsobaGUID = IF(IFNULL(_Key2, '') = '', @session.CurrOsobaGUID, _Key2);
		/*
		SET _Cul_Idn = _Key3;
		
		IF _crmGUID = '' THEN
			SELECT cul.commisionGUID FROM `crm_usr_links` cul WHERE cul.Idn = _Cul_Idn INTO _crmGUID;
			SET _crmGUID = IFNULL(_crmGUID, '');
		END IF;
		*/

		IF _crmGUID = '' THEN LEAVE S_crm_porta_fide_tools; END IF;

		SELECT 
			c.Rodzaj, c.PodRodzaj, cr.Def_confirm_statuses 
		FROM `crm_commisions` c 
		INNER JOIN `crm_rodzaje` cr ON cr.Rodzaj = c.Rodzaj AND cr.PodRodzaj = c.PodRodzaj
		WHERE 
			c.GUID = _crmGUID
		INTO 
			@.c.Rodzaj, @.c.PodRodzaj, @.cr.Def_confirm_statuses
		;
		
		IF @.c.Rodzaj = 16 THEN
		
			SET @_crmGUID = IFNULL(_crmGUID, '');
		--	Teraz jeszcze nalicz Absencje
			-- dawniej CALL _crm_commisons_ai_au_ad('AU', _crmGUID, 16, 1, '', '');
			CALL crm_porta_fide_tools('COMS.Absencje.AU', _crmGUID, 16, 1, '', '', '', '', '');
		--	Teraz jeszcze nalicz frekwencje o ile jest
			CALL _absencje_Update_frekw_from_absencje('update.for.one.rec.`absencje`', MD5(_crmGUID), '1', '', '', '');	-- nie musisz zmieniac juz absencji bo zrobil to juz CALL _crm_commisons_ai_au_ad('AU', _crmGUID, 16, 1, '', '');
		END IF;
		
	END IF;
	
					IF _MODE = 'COMS.Absencje.AU' AND (_Key2 = 16) THEN
					--	Tylko Wnioski urlopowo zwolnieniowe
						SET _crmGUID = IFNULL(_Key, '');
						SET _OsobaGUID = IF(IFNULL(_Key2, '') = '', @session.CurrOsobaGUID, _Key2);
						/************************************************************************************************************************************************
							sterowanie rekordem absencji (rodzaj na +/- w zalezn. od statusu wniosku) - dawne CALL _crm_commisons_ai_au_ad('AU', _crmGUID, 16, 1, '', '');
							CALL crm_porta_fide_tools('COMS.Absencje.AU', _crmGUID, 16, 1, '', '', '', '', '');
							_Key	wniosek/zdarz 
							_Key2	- Rodzaj
							_Key3	- PodRodzaj
						************************************************************************************************************************************************/
	SET @COMS.Absencje.AU = '1';
	SET @COMS.Absencje.AU = '2';
						DROP TEMPORARY TABLE IF EXISTS `t_trigger_crm_commisions`;
						CREATE TEMPORARY TABLE `t_trigger_crm_commisions` 
							SELECT 
								c.*,
								IFNULL(
									(SELECT cul.Osoba FROM `crm_usr_links` cul WHERE cul.commisionGUID  = c.GUID AND cul.Poziom = 2 LIMIT 1)
								, '') AS OsobaWorker
							FROM `crm_commisions` c 
							WHERE 
								c.GUID = _crmGUID
								-- AND
								-- c.Status > 1	--	tylko dla przeslanych wnioskow
						;
	SET @COMS.Absencje.AU = '3';
					--	1. Wstawiamy Wnioski ale tylko przeslane wnioski
						INSERT IGNORE INTO `absencje`
							(GUID, GUIDpr, AbsencjaOd, AbsencjaDo, PelnaAbsencjaOd, PelnaAbsencjaDo, Rodzaj, Opis) -- , miesiac, FrekwGUID)
							SELECT
								MD5(c.GUID) AS GUID,
								@PracGUID := OsobaWorker AS GUIDpr,
								c.StartTime AS AbsencjaOd, 
								c.EndTime AS AbsencjaDo, 
								c.StartTime AS PelnaAbsencjaOd, 
								c.EndTime AS PelnaAbsencjaDo, 
								-- IF(c.Status = 3, c.Typ, -ABS(c.Typ)) AS Rodzaj, 
								IF(c.Status = 3, c.Typ, -ABS(2)) AS Rodzaj, 
								-- MONTH(c.StartTime) AS miesiac,
								c.Opis
							FROM `t_trigger_crm_commisions` c
							WHERE
								TRUE -- c.guid = '240c4cc4b3ed9ad21515c3d6abc559f9'
								AND
								c.Status > 1	--	tylko dla przeslanych wnioskow
							HAVING GUIDpr != ''
							ON DUPLICATE KEY UPDATE
								`absencje`.AbsencjaOd = c.StartTime, 
								`absencje`.AbsencjaDo = c.EndTime, 
								`absencje`.PelnaAbsencjaOd = c.StartTime, 
								`absencje`.PelnaAbsencjaDo = c.EndTime, 
								`absencje`.Rodzaj = IF(c.Status = 3, c.Typ, -ABS(c.Typ)), 
								-- `absencje`.miesiac = MONTH(c.StartTime),
								`absencje`.Opis = c.Opis
						;
	SET @COMS.Absencje.AU = '4';
					--	2. Anulowane usuwamy
						DELETE a FROM `absencje` a, `t_trigger_crm_commisions` c
						WHERE
							a.GUID = MD5(c.GUID)
							AND
							c.Status IN (0, 8, 14)	--	tylko dla nowych i anulowanych
							AND
							IFNULL(a.FrekwGUID, '') = '' 
							-- ????? AND	a.Rodzaj < 0
						;
	SET @COMS.Absencje.AU = '5';
				--		DROP TEMPORARY TABLE IF EXISTS `t_trigger_crm_commisions`;
					END IF;
	
	
	IF _MODE = 'remove.app' THEN
	/************************************************************************************************************************************************
		Usuwa nie wysłany wniosek
		CALL crm_porta_fide_tools('remove.app', 'GUID', '', '', '', '', '', '', '');
		_Key		to GUID crm
		_Key2 to guid osoby
	************************************************************************************************************************************************/
		SET _crmGUID = IFNULL(_Key, '');
		SET _OsobaGUID = IF(IFNULL(_Key2, '') = '', @session.CurrOsobaGUID, _Key2);
		
		SET @_isOwner = (SELECT cul.poziom FROM `crm_usr_links` cul WHERE cul.`commisionGUID`= _crmGUID AND osoba = _OsobaGUID AND cul.poziom = 0);		
		IF (@_isOwner IS NOT NULL) THEN
			DELETE c FROM `crm_commisions` c WHERE c.GUID = _crmGUID
			;
			-- DELETE FROM `crm_workflow` WHERE `commisionGUID` = _crmGUID;
		END IF;
		
		
	END IF;
	
	
	IF _MODE LIKE 'select.%' THEN
		SET _OsobaGUID = IF(IFNULL(_Key2, '') = '', @session.CurrOsobaGUID, _Key2);
		/************************************************************************************************************************************************
			
			CALL crm_porta_fide_tools('Select.wnioski.do.decyzji', '', 'Prac50', '', '', '', '', '', '');
			CALL crm_porta_fide_tools('Select.wnioski.do.decyzji', '', 'Prac50', '1', '', '', '', '', '');	--	archiwum
			_Key2	- Osoba spr.
			_Key3	gdy != '' to archiwum
			
			CALL crm_porta_fide_tools('Select.wnioski.moje', '', 'Prac50', '', '', '', '', '', '');	
			CALL crm_porta_fide_tools('Select.wnioski.moje', '', 'Prac50', '1', '', '', '', '', '');	--	archiwum
			_Key2	- Osoba spr.
			_Key3	gdy != '' to archiwum
			
			Zwraca historie wniosku
			CALL crm_porta_fide_tools('select.hist', 'abc', '', '', '', '', '', '', '');
			_Key		to GUID crm
			
			Select do maila
			CALL crm_porta_fide_tools('Select.Get.email.select', 'GUID', '', '', '', '', '', '', '');
			_Key		to GUID crm
			
			Zdarzenia moje
			CALL crm_porta_fide_tools('Select.Zdarzenia', '', '2', '1', '', '', '', '', '');
			Zdarzenia wszystkie zwiazane ze mna
			CALL crm_porta_fide_tools('Select.Zdarzenia', '', '', '', '1', '', '', '', '');
			_Key2		gdy = 2 to moje a gdy != 2 np "" to wszystkie zwiazane ze mna
			_Key3		to Status czyli 1, 2, lub 3
		************************************************************************************************************************************************/
		
		IF _MODE = 'Select.wnioski.do.decyzji' THEN
			SET _Archiwum = _Key3;
			CALL crm_workflow_TOOLS('Select.all.nodes.do.decyzji', 'web_crm', _OsobaGUID, '16', '0', _Archiwum, '');
			LEAVE S_crm_porta_fide_tools;
		END IF;
		
		IF _MODE = 'Select.wnioski.moje' THEN
			SET _Archiwum = _Key3;
			CALL crm_workflow_TOOLS('Select.all.nodes.moje', 'web_crm', _OsobaGUID, '16', '0', _Archiwum, '');
			LEAVE S_crm_porta_fide_tools;
		END IF;

		IF _MODE = 'Select.hist' THEN
		SET @X = _MODE;
			SET _crmGUID = IFNULL(_Key, '');
			
			CALL crm_workflow_TOOLS('Select.workflow.history', _crmGUID, '', '', '', '', '');
			-- CALL crm_workflow_TOOLS('Select.workflow.history', '2afca98874a4ddf07b8410bce9ffbe54', '', '', '', '', '');
			
			/*
			bylo
			SELECT 
				CONCAT(o.Imie1, ' ', o.Nazwisko) AS OsobaStr,
				clog.time_sheet_when AS WhenStr,
				clog.jest AS HistStr
			FROM `crm_log` clog 
			LEFT JOIN `osoby` o ON o.GUID = clog.osobaGUID
			WHERE 
				clog.commissionguid = _crmGUID 
				AND clog.zmiana_dotyczy = 'VALIDATE'
			ORDER BY clog.Idn
			;
			*/
			
			
			
		END IF;

		IF _MODE = 'Select.Get.email.select' THEN
			SET _crmGUID = IFNULL(_Key, '');
			-- SET _OsobaGUID = IF(IFNULL(_Key2, '') = '', @session.CurrOsobaGUID, _Key2);
			DROP TEMPORARY TABLE IF EXISTS `t_prepare_mail_by_workflow_confirm_1`;
			CREATE TEMPORARY TABLE `t_prepare_mail_by_workflow_confirm_1` (
			  `Send_Mode` VARCHAR(38) DEFAULT NULL,
			  `crm_GUID` VARCHAR(38) DEFAULT NULL,
			  `Idn_workflow` INT(11) NOT NULL DEFAULT '0',
			  `OsobaGUID_recipient` VARCHAR(38),
			  `OsobaGUID_owner` VARCHAR(38),
			  `adres` VARCHAR(255),
			PRIMARY KEY OsobaGUID_recipient(OsobaGUID_recipient)
			)
			;
			SET
				@.OsobaGUID_skladajaca = IFNULL((SELECT cul.Osoba FROM `crm_usr_links` cul WHERE cul.commisionGUID = _crmGUID AND cul.Poziom = 2), '')
			;
			SET
				@.Osoba_skladajaca = IFNULL((SELECT CONCAT(o.Imie1, ' ', o.Nazwisko) FROM `osoby` o WHERE o.GUID = @.OsobaGUID_skladajaca), '')
			;
			SET
				@workflow.mail.MSG.Ja.Ja = 'Twój wniosek ma teraz status "#STATUS_BEFORE#" i czeka na Twoją decyzję #STATUS_?#',
				@workflow.mail.MSG.Ja.Ktos = 'Twój wniosek ma teraz status "#STATUS_BEFORE#" i czeka na decyzję osoby(grupy): #GRUPA#',
				@workflow.mail.MSG.Ktos = 'Wniosek osoby #OSOBA# ma teraz status "#STATUS_BEFORE#" i czeka na Twoją decyzję #STATUS_?#',
				@workflow.mail.MSG.Ja.END = 'Twój wniosek zmienił status na "#STATUS_BEFORE#" (tym samym zakończono rozpatrywanie wniosku)'
			;
			TRUNCATE `t_prepare_mail_by_workflow_confirm_1`;
			INSERT IGNORE INTO `t_prepare_mail_by_workflow_confirm_1`
			(Send_Mode, crm_GUID, Idn_workflow, OsobaGUID_recipient, OsobaGUID_owner)
			SELECT
				'KTOS' AS Send_Mode,
				vw.commisionGUID AS crm_GUID, 
				vw.Idn_workflow, 
				o.GUID AS OsobaGUID_recipient,
				@.OsobaGUID_skladajaca
			FROM `crm_commisions` c 
			INNER JOIN `crm_rodzaje` cr ON cr.Rodzaj = c.Rodzaj AND cr.PodRodzaj = c.PodRodzaj
			INNER JOIN `v_crm_workflow_confirm` vw ON vw.commisionGUID = c.GUID AND vw.Def = cr.Def_confirm_statuses AND vw.Response != 'RESPONSE_100'
			INNER JOIN `grupy_osob` go ON go.grupaGUID = vw.UserGUID
			INNER JOIN `osoby` o ON o.GUID = go.osobaGUID AND o.Rodzaj != 0
			-- UsewrGUID
			WHERE 
				c.GUID = _crmGUID 
			GROUP BY OsobaGUID_recipient -- , Idn_workflow
			;
			UPDATE `t_prepare_mail_by_workflow_confirm_1` t
			SET
				t.Send_Mode =  
					IF(t.OsobaGUID_owner = t.OsobaGUID_recipient,
						'JA.JA',
						t.Send_Mode
					)
			;
			INSERT IGNORE INTO `t_prepare_mail_by_workflow_confirm_1`
			(Send_Mode, crm_GUID, Idn_workflow, OsobaGUID_recipient, OsobaGUID_owner)
			SELECT
				'JA.KTOS' AS Send_Mode,
				c.GUID AS crm_GUID, 
				IFNULL(
					(SELECT vw.Idn_workflow FROM `v_crm_workflow_confirm` vw WHERE vw.commisionGUID = c.GUID AND vw.Def = cr.Def_confirm_statuses ORDER BY vw.Idn_workflow DESC LIMIT 1)
				, 0)
				AS Idn_workflow, 
				o.GUID AS OsobaGUID_recipient,
				@.OsobaGUID_skladajaca
			FROM `crm_commisions` c 
			INNER JOIN `crm_rodzaje` cr ON cr.Rodzaj = c.Rodzaj AND cr.PodRodzaj = c.PodRodzaj
			INNER JOIN `osoby` o ON o.GUID = @.OsobaGUID_skladajaca
			-- UsewrGUID
			WHERE 
				c.GUID = _crmGUID
			GROUP BY OsobaGUID_recipient -- , Idn_workflow
			;
			
			UPDATE `t_prepare_mail_by_workflow_confirm_1` t, `v_crm_workflow_confirm` vw 
			SET
				t.Send_Mode =  
					IF(vw.Response = 'RESPONSE_100',
						'JA.END',
						t.Send_Mode
					)
			WHERE
				vw.Idn_workflow = t.Idn_workflow
			;
			DROP TEMPORARY TABLE IF EXISTS `t_prepare_mail_by_workflow_confirm_2`;
			CREATE TEMPORARY TABLE `t_prepare_mail_by_workflow_confirm_2` 
			SELECT
				CASE 
				WHEN t.Send_Mode = 'JA.JA' THEN @workflow.mail.MSG.Ja.Ja
				WHEN t.Send_Mode = 'JA.KTOS' THEN @workflow.mail.MSG.Ja.Ktos
				WHEN t.Send_Mode = 'KTOS' THEN @workflow.mail.MSG.Ktos
				WHEN t.Send_Mode = 'JA.END' THEN @workflow.mail.MSG.Ja.END
				END AS Msg_init,
				
				GROUP_CONCAT(vw.Imiona_Nazwisko) AS Grupa_Nazwy,
				CONCAT(o.Imie1, ' ', o.Nazwisko) AS Imiona_Nazwisko,
				t.*
			FROM `t_prepare_mail_by_workflow_confirm_1` t
			INNER JOIN `v_crm_workflow_confirm` vw  ON vw.Idn_workflow = t.Idn_workflow
			INNER JOIN `osoby` grupa ON grupa.GUID = vw.UserGUID
			INNER JOIN `osoby` o ON o.GUID = t.OsobaGUID_recipient
			GROUP BY t.OsobaGUID_recipient
			;
			UPDATE `t_prepare_mail_by_workflow_confirm_2` t, `v_crm_workflow_confirm` vw  
			SET
				t.Msg_init = 
					REPLACE(
						REPLACE(
							REPLACE(
								REPLACE(
									t.Msg_init, '#STATUS_?#', ''
								),
								'#GRUPA#', t.Grupa_Nazwy
							),
							'#OSOBA#', @.Osoba_skladajaca
						),
						'#STATUS_BEFORE#', vw.Status_Before
						
					)
			WHERE
				vw.Idn_workflow = t.Idn_workflow

			;
			
			
		--	Adresy Mailowe
		--	1.	Najpierw sluzbowe dla poziomu <> 2 
			UPDATE `t_prepare_mail_by_workflow_confirm_2` t
			SET
				t.adres = (
					SELECT
						k.Adres
					FROM `kontakty` k 
					WHERE
						k.osobaGUID = t.OsobaGUID_recipient
						AND k.typ_medium = 3 
						AND IFNULL(k.KontrGUID, '') != '' 				--	KontrGUID !!!
					ORDER BY k.newsletter DESC, k.priorytet DESC 
					LIMIT 1
					)
			WHERE
				t.OsobaGUID_owner != t.OsobaGUID_recipient
			;
		--	2.	Teraz dla wszystkich jesli w p.1 nie wstawil sluzbowego to tutaj wstawi niesluzbowy
			UPDATE `t_prepare_mail_by_workflow_confirm_2` t
			SET
				t.adres = (
					SELECT
						k.Adres
					FROM `kontakty` k 
					WHERE
						k.osobaGUID = t.OsobaGUID_recipient
						AND k.typ_medium = 3 
					ORDER BY k.newsletter DESC, k.priorytet DESC 
					LIMIT 1
					)
			WHERE
				IFNULL(t.adres, '') = ''
			;

			-- select @.Osoba_skladajaca;
			
			SELECT 
				t.Msg_init AS Msg, 
				GROUP_CONCAT(DISTINCT t.adres SEPARATOR ',') AS adres, 
				c.Temat 
			FROM `t_prepare_mail_by_workflow_confirm_2` t 
			INNER JOIN `crm_commisions` c ON c.GUID = t.crm_GUID
			GROUP BY Msg
			;
			
			-- SELECT * FROM `t_prepare_mail_by_workflow_confirm_2` t;
			
		END IF;

		IF _MODE = 'Select.absencje.prac' THEN
			DROP TEMPORARY TABLE IF EXISTS `t_absencje_day`;
			CREATE TEMPORARY TABLE `t_absencje_day` (
			  -- `idn_absencji` int(11) NOT NULL AUTO_INCREMENT,
			  `Data` DATE DEFAULT NULL,
			  `GUIDpr` VARCHAR(38) NOT NULL DEFAULT '',
			  `GUID_absencji` VARCHAR(38) NOT NULL DEFAULT '',
			  PRIMARY KEY ID(`GUIDpr`, `Data`),
			  KEY `GUIDpr` (`GUIDpr`),
			  KEY `GUID_absencji` (`GUID_absencji`),
			  KEY `Data` (`Data`)
			);

			SET
				@.Calendar.DataOd = CURRENT_DATE() - INTERVAL 7 DAY,
				@.Calendar.DataDo = CURRENT_DATE() + INTERVAL 2 MONTH 
			;
			SET
				@.Calendar.DataOd = _Param_A - INTERVAL 7 DAY,
				@.Calendar.DataDo = _Param_A + INTERVAL 1 MONTH + INTERVAL 6 DAY
			;
			DROP TEMPORARY TABLE IF EXISTS `t_days`;
			CREATE TEMPORARY TABLE `t_days` 
			SELECT 
				kp.* 
			FROM `kalendpracy_po` kp
			WHERE
				kp.data BETWEEN @.Calendar.DataOd AND @.Calendar.DataDo
			GROUP BY kp.data
			ORDER BY kp.data
			;
			
			DROP TEMPORARY TABLE IF EXISTS `t_absencje_day_abs`;
			CREATE TEMPORARY TABLE `t_absencje_day_abs` (
			  `GUID` VARCHAR(38) NOT NULL DEFAULT '',
			  `GUIDpr` VARCHAR(38) DEFAULT NULL,
			  `AbsencjaOd` DATE DEFAULT NULL,
			  `AbsencjaDo` DATE DEFAULT NULL,
			  PRIMARY KEY GUID(GUID),
			  KEY `GUIDpr` (`GUIDpr`),
			  KEY `AbsencjaOd` (`AbsencjaOd`),
			  KEY `AbsencjaDo` (`AbsencjaDo`)
			)
			;
			INSERT IGNORE INTO `t_absencje_day_abs` 
			SELECT
				a.GUID, a.GUIDPr, a.AbsencjaOd, a.AbsencjaDo
			FROM `t_days` d  
			INNER JOIN `absencje` a ON d.Data BETWEEN a.AbsencjaOd AND a.AbsencjaDo
			GROUP BY a.GUID
			;
			
			DROP TEMPORARY TABLE IF EXISTS `t_abs_pracownicy_szefa`;
			IF _Key3 = '' THEN
				CREATE TEMPORARY TABLE IF NOT EXISTS `t_abs_pracownicy_szefa` AS (
					SELECT 
						o.GUID AS GUIDpr
						-- hr.OsobaGuid_A AS GUIDpr
					FROM `Pracownicy` o, `hr_self_links` hr 
					-- INNER JOIN `hr_self_links` hr ON hr.OsobaGuid_A = o.GUID
					WHERE 
						(o.ToPracownik OR (o.aktywny AND (o.firma_wspolprac OR o.ToZleceniobiorca)))
						AND
						NOT (o.GUID IN ('{7184A9AC-9B77-4BB3-B86C-43EE92592E58}', 'Prac5'))
						AND
						hr.OsobaGuid_A = o.GUID
						AND
						hr.OsobaGuid_B = _OsobaGUID
						AND
						hr.Rel = 'szef'
				)
				;	
			ELSEIF _Key3 = '1' THEN
				CREATE TEMPORARY TABLE IF NOT EXISTS `t_abs_pracownicy_szefa` AS (
					SELECT 
						o.GUID AS GUIDpr
					FROM `Pracownicy` o 
					WHERE 
						(o.ToPracownik OR (o.aktywny AND (o.firma_wspolprac OR o.ToZleceniobiorca)))
						AND
						NOT (o.GUID IN ('{7184A9AC-9B77-4BB3-B86C-43EE92592E58}', 'Prac5'))
						AND
						EXISTS(SELECT a.GUIDpr FROM `t_absencje_day_abs` a WHERE a.GUIDpr = o.GUID)
				)
				;	
			ELSE
				CREATE TEMPORARY TABLE IF NOT EXISTS `t_abs_pracownicy_szefa` AS (
					SELECT 
						o.GUID AS GUIDpr
					FROM `Pracownicy` o
					WHERE 
						(o.ToPracownik OR (o.aktywny AND (o.firma_wspolprac OR o.ToZleceniobiorca)))
						AND
						NOT (o.GUID IN ('{7184A9AC-9B77-4BB3-B86C-43EE92592E58}', 'Prac5'))
				)
				;	
			END IF;
			
			INSERT INTO `t_absencje_day` 
			(`GUIDpr`, `Data`)
			SELECT
				ts.GUIDpr, td.Data
			FROM `t_days` td, `t_abs_pracownicy_szefa` ts
			;
			UPDATE `t_absencje_day` ad
			SET
				ad.GUID_absencji = (SELECT GUID FROM `t_absencje_day_abs` a WHERE a.GUIDPr = ad.GUIDpr AND ad.Data BETWEEN a.AbsencjaOd AND a.AbsencjaDo LIMIT 1)
			;
			
			
			/*
			UPDATE `t_absencje_day` ad
			SET
				ad.GUID_absencji = (SELECT GUID FROM `absencje` a WHERE a.GUIDPr = ad.GUIDpr AND ad.Data BETWEEN a.AbsencjaOd AND a.AbsencjaDo LIMIT 1)
			;
			*/
			/*
			IF _Key3 = '1' THEN
				DELETE FROM `t_absencje_day` WHERE IFNULL(GUID_absencji, '') = '';
			END IF;
			*/
			
			SELECT 
				ad.GUIDPr AS OsobaGUID,
				ad.*,
				IFNULL(kl.swieto, FALSE) AS IS_Swieto,
				WEEKDAY(ad.data) = 5 AS IS_So,
				WEEKDAY(ad.data) = 6 AS IS_Nd,
				CONCAT(o.nazwisko, ' ', o.Imie1, ' ', IFNULL((SELECT adres FROM kontakty WHERE osobaGUID = o.GUID AND rodzaj = 0 AND typ_medium = 2 LIMIT 1), '')) AS OsobaStr,
				IFNULL(a.Rodzaj < 0, FALSE) AS As_Plan,
				IFNULL(d.Symbol, '') AS Symbol_Absencji,
				IFNULL(d.definicja, '') AS HINT,
				a.*
			FROM `t_absencje_day` ad
			LEFT JOIN osoby o ON o.GUID = ad.GUIDpr
			LEFT JOIN `absencje` a ON a.GUID = ad.GUID_absencji
			LEFT JOIN `absencje_dict` d ON d.Idn = ABS(a.Rodzaj)
			LEFT JOIN `kalendarz` kl ON kl.data = ad.Data
			ORDER BY ad.GUIDpr, ad.Data
			;
						
			LEAVE S_crm_porta_fide_tools;
		END IF;

		IF _MODE = 'Select.Zdarzenia' THEN
			SELECT
				c.Temat, c.Opis, c.Data, c.Status,
				(SELECT COUNT(*) FROM `crm_log` cl WHERE cl.commissionguid = c.GUID) AS IleKomentarzy,
				(SELECT COUNT(*) FROM `dokumenty` d WHERE d.GUID = c.GUID) AS IleDok
			FROM `crm_commisions` c
			INNER JOIN `grupy_osob` go ON go.OsobaGUID = @session.CurrOsobaGUID 
			INNER JOIN `crm_usr_links` cul ON cul.commisionGUID = c.GUID AND cul.Osoba = go.grupaGUID AND IF(_Key2 = 2, cul.Poziom = 2, cul.Poziom != 2)
			WHERE
				c.Rodzaj IN (6, 2) 
				AND
				c.Status = _Key3
			GROUP BY c.GUID
			;			
		END IF;
		
	END IF;
	
	IF _MODE LIKE 'Zdarzenia.%' THEN
	/************************************************************************************************************************************************
		Dla zdarzen rejestrowanych przez tablet
		Najpierw samo zdarzenie ...
		CALL crm_porta_fide_tools('Zdarzenia.NEW', 'GUIDCrm', 'OsobaZalogowanaZglaszajaca', '6', '0', 'Temat', '', '', '');
		_Key		to GUID crm
		_Key2 		to guid osoby
		_Key3		Rodzaj
		_Param_A	PodRodzaj
		_Param_B	tekst zdarzenia
		
		... potem aby dodać osoby n x wolamy
		CALL crm_porta_fide_tools('Zdarzenia.NEW.Osoby', 'GUIDCrm', 'OsobaZeZdarzenia', '', '', '', '', '', '');
		_Key		to GUID crm
		_Key2 		to guid osoby
		
		... potem aby dodać zdjęcia n x wolamy
		CALL crm_porta_fide_tools('Zdarzenia.NEW.Photo', 'GUIDCrm', '', '', '', 'binary???', '', '', '');
	************************************************************************************************************************************************/
		SET _crmGUID = IFNULL(_Key, '');
		SET _OsobaGUID = IF(IFNULL(_Key2, '') = '', @session.CurrOsobaGUID, _Key2);

		IF _MODE = 'Zdarzenia.NEW' THEN
			SET
				_Rodzaj = _Key3, _PodRodzaj = _Param_A
			;
			CALL crm_INSERT('', _crmGUID, '', _Rodzaj, _PodRodzaj, 'Notatka', _Param_B, 0.5, 'NrWewn', NULL, NULL, _OsobaGUID, '', '', '', 0, 0, '', '');
			-- CALL crm_usr_links_INSERT('insert.bez.duplikatu.poziomu', _crmGUID, '', _OsobaGUID, 2, 0, NULL, NULL, '', '');
			CALL crm_usr_links_INSERT('delete', _crmGUID, '', _OsobaGUID, 2, 0, NULL, NULL, '', '');
			CALL crm_usr_links_INSERT('z.grupy_osob', _crmGUID, '', 'KADRY_Decyzje', 1, 0, NULL, NULL, '', '');
		END IF;

		IF _MODE = 'Zdarzenia.NEW.Osoby' THEN
			-- CALL crm_usr_links_INSERT('insert.bez.duplikatu.poziomu', _crmGUID, '', _OsobaGUID, 2, 0, NULL, NULL, '', '');
			CALL crm_usr_links_INSERT('', _crmGUID, '', _OsobaGUID, 2, 0, NULL, NULL, '', '');
			CALL crm_usr_links_INSERT('z.hr_self_links.szef', _crmGUID, '', _OsobaGUID, 1, 0, NULL, NULL, 'new', '');
		END IF;
		
		IF _MODE = 'Zdarzenia.NEW.Photo' THEN
			SET @X = 'obsluzone w function saveForm()';
		END IF;
		
		
	END IF;
	
END$$


DELIMITER ;

/*
====================================================================================================================================================================================
	NOTATNIK
====================================================================================================================================================================================



ALTER TABLE `crm_usr_links`   
  ADD COLUMN `confirm_status_init` VARCHAR(100) DEFAULT ''  NOT NULL  COMMENT 'Inicjalny Status zatwierdzenia z `crm_confirm_statuses`' AFTER `wydajnosc`, 
  ADD COLUMN `confirm_status` VARCHAR(100) DEFAULT ''  NOT NULL  COMMENT 'Aktualny Status zatwierdzenia z `crm_confirm_statuses`' AFTER `confirm_status_init`, 
  ADD  INDEX `confirm_status` (`confirm_status`),
  ADD  INDEX `confirm_status_init` (`confirm_status_init`) 
;
ALTER TABLE `crm_commisions`   
  ADD COLUMN `Update_status` VARCHAR(200) DEFAULT '' NOT NULL COMMENT 'gdy <> "" to zainicjowuje specjalne czynnosci w triggerze' AFTER `status2`,
  ADD COLUMN `confirm_status` VARCHAR(100) DEFAULT ''  NOT NULL  COMMENT 'Aktualny Status zatwierdzenia z `crm_confirm_statuses`' AFTER `Update_status`, 
  ADD  INDEX `confirm_status` (`confirm_status`)
;

SHOW CREATE TABLE `crm_confirm_statuses`;
CREATE TABLE `crm_confirm_statuses` (
  `Def` VARCHAR(100) NOT NULL DEFAULT '' COMMENT 'Definicja zatwierdzenia (wspolklucz)',
  `Status` VARCHAR(100) NOT NULL DEFAULT '' COMMENT 'Status zatwierdzenia',
  `Descr_status` VARCHAR(250) NOT NULL DEFAULT '' COMMENT 'Opis samego statusu',
  `Level` INT(11) NOT NULL DEFAULT '0' COMMENT 'Poziom od 0...n - w agregacji bierzemy MIN(Level)',
  `crm_Status` VARCHAR(100) NOT NULL DEFAULT '' COMMENT 'Odp. Status crm',
  `Comment` VARCHAR(250) NOT NULL DEFAULT '' COMMENT 'komentarz',
  PRIMARY KEY (`Status`,`Def`),
  KEY `Status` (`Status`)
) DEFAULT CHARSET=cp1250 COMMENT='Statusy potwierdzen w zdarzeniach crm'
;
SHOW CREATE TABLE `crm_confirm_statuses_links`;
CREATE TABLE `crm_confirm_statuses_links` (
  `Def` VARCHAR(100) NOT NULL DEFAULT '' COMMENT 'Definicja zatwierdzenia (wspolklucz)',
  `Status_1` VARCHAR(100) NOT NULL DEFAULT '' COMMENT 'Poprz. Status zatwierdzenia',
  `Status_2` VARCHAR(100) NOT NULL DEFAULT '' COMMENT 'Nast. Status zatwierdzenia',
  `Desr_Item` VARCHAR(200) NOT NULL DEFAULT '' COMMENT 'Opis wybranej opcji',
  PRIMARY KEY (`Def`,`Status_1`,`Status_2`),
  KEY `Status_1` (`Status_1`),
  KEY `Status_2` (`Status_2`)
) DEFAULT CHARSET=cp1250 COMMENT='Statusy potwierdzen w zdarzeniach crm'
;




------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
*/
