 /*!40101 SET NAMES utf8 */;
DELIMITER $$
CREATE OR REPLACE DEFINER=`root`@`localhost` VIEW `v_wf_def` AS
	(
		SELECT
			cr.Def_confirm_statuses, GROUP_CONCAT(cr.Nazwa) AS Nazwa, GROUP_CONCAT(CONCAT('(',cr.Podrodzaj, ')')) AS Podrodzaje
		FROM `crm_rodzaje` cr
		WHERE
			cr.Rodzaj = 16 AND cr.Deleted = 0
		GROUP BY cr.Def_confirm_statuses
	)
$$

CREATE OR REPLACE DEFINER=`root`@`localhost` VIEW `v_crm_workflow_def_active` AS
	(
	SELECT
		s.*,
		cr.Nazwa,
		cr.Podrodzaje,
		/*
		-- cr.*,
		s.Def,
		s.Status, s.Descr_status, 
		s.Buttons,
		-- s.Is_ending,
		-- s.crm_status + ABS(s.Poziom)* 1000 AS LP -- s.Poziom AS LP
		s.Poziom,
		100 + ABS(s.Poziom)* 10 + s.Is_ending AS LP, 
		*/
		100 + ABS(s.Poziom)* 10 + s.Is_ending AS LP, 
		IF(s.Is_ending = 2, 1000, 100) + ABS(s.Poziom)* 10 AS LP_2 -- do list fltrów
	FROM `crm_workflow_def` s, `v_wf_def` cr
		/*
		(
		SELECT
			cr.Def_confirm_statuses, GROUP_CONCAT(cr.Nazwa) AS Nazwa
		FROM `crm_rodzaje` cr
		WHERE
			cr.Rodzaj = 16
		GROUP BY cr.Def_confirm_statuses
		) cr
		*/
	WHERE
		TRUE
		AND s.Def = cr.Def_confirm_statuses	-- 'web_wnioski_2_level_zast_szef'
		AND s.Deleted = 0
	ORDER BY s.Def, LP	
	)
$$

CREATE OR REPLACE DEFINER=`root`@`localhost` VIEW `v_crm_workflow_20` AS
	(
	SELECT
		cw.*,
		(SELECT culowner.Osoba FROM `crm_usr_links` culowner WHERE culowner.commisionGUID = cw.commisionGUID AND culowner.Poziom = 2) AS Owner_GUID,
		(SELECT culowner.Osoba FROM `crm_usr_links` culowner WHERE culowner.commisionGUID = cw.commisionGUID AND culowner.Poziom = 0) AS Creator_GUID
	FROM `crm_workflow` cw
	)
$$
CREATE OR REPLACE DEFINER=`root`@`localhost` VIEW `v_crm_workflow_def_links` AS
	(
	SELECT 
	-- wds.Poziom,
	-- wds.Descr_status,
		cwdl.*, 
		-- CONCAT_WS(' - ', IF(cwde.Is_Ending, 'Koniec', NULL), cwdl.Descr_Item_Past, cwde.Descr_status) AS Decr_Current,
		CONCAT_WS(' - ', IF(cwde.Is_Ending, 'Koniec', NULL), cwdl.Descr_Item_Past, IF(cwde.Descr_status = cwdl.Descr_Item_Past, NULL, cwde.Descr_status)) AS Status_Current_Str
	FROM `crm_workflow_def_links` cwdl 
	INNER JOIN `crm_workflow_def` cwds ON cwds.Status = cwdl.Status_Start AND cwds.def = cwdl.def AND NOT cwds.Deleted
	INNER JOIN `crm_workflow_def` cwde ON cwde.Status = cwdl.Status_End AND cwde.def = cwdl.def AND NOT cwde.Deleted
	-- WHERE 
	-- 	TRUE
		-- AND cwdl.def = 'web_delegacja'
	-- ORDER BY cwds.Poziom
	)
$$

CREATE OR REPLACE DEFINER=`root`@`localhost` VIEW `v_crm_workflow_def_links2` AS
	(
	SELECT 
		cwdl.*,
		cwde.Is_Ending,
		-- CONCAT_WS(' - ', IF(cwde.Is_Ending, 'Koniec', NULL), cwdl.Descr_Item_Past, cwde.Descr_status) AS Decr_Current,
		CONCAT_WS(' - ', 
			IF(cwde.Is_Ending, 'Koniec', NULL), 
			cwdl.Descr_Item_Past, 
			IF(cwde.Descr_status = cwdl.Descr_Item_Past, NULL, cwde.Descr_status)
		) AS Status_Current_Str
	FROM `crm_workflow_def_links` cwdl 
	-- INNER JOIN `crm_workflow_def` cwds ON cwds.Status = cwdl.Status_Start AND cwds.def = cwdl.def AND NOT cwds.Deleted
	INNER JOIN `crm_workflow_def` cwde ON cwde.Status = cwdl.Status_End AND cwde.def = cwdl.def AND NOT cwde.Deleted
	)
$$

CREATE OR REPLACE DEFINER=`root`@`localhost` VIEW `v_crm_workflow` AS
	(
	SELECT
		cw.*,
		IFNULL(cwdl.Status_Current_Str,				-- aktualny decyzja podjęta
			IFNULL(cwdl_prev.Status_Current_Str,	-- poprzedni decyzja podjęta
				cwd.Descr_status
			)
		) AS Status_Current_Str,
		GROUP_CONCAT(ouser.IdnUser) AS IdnUser,
		GROUP_CONCAT(o.IdnUser) AS Decision_IdnUser,
		GROUP_CONCAT(CONCAT(ouser.Imie1, ' ', ouser.Nazwisko)) AS User_Nazwiska,
		GROUP_CONCAT(CONCAT(o.Imie1, ' ', o.Nazwisko)) AS Decision_User_Nazwiska,
		IFNULL(cwdl_prev.Descr_Item_Past, cwd_before.Descr_Status) AS Status_Before,
		/* */
		CONCAT_WS('\n',
			IFNULL(cwdl.Descr_Item_Past, 
				IF(NOT cwd.crm_Status IN (3, 4, 8, 11, 13, 14) AND NOT cwd.Is_Ending, CONCAT(_VarStr('brak decyzji - ('), cwd.Descr_Status, ')'), cwd.Descr_Status)
			),
			_ConcatPre('Z adnotacją: ',
				NULLIF(GROUP_CONCAT(_Brackets('"', NULLIF(cwc.Comment, _VarStr('(wysłany zaraz po utworzeniu)')), '"') ORDER BY cwc.ModifyAt SEPARATOR ' + '), '')
				
			)
		) AS Status_Hist,
		/* */
		IFNULL(cwdl.Descr_Item_Past, 
			IF(NOT cwd.crm_Status IN (3, 4, 8, 11, 13, 14) AND NOT cwd.Is_Ending, 
				-- CONCAT(_VarStr('brak decyzji - ('), cwd.Descr_Status, ')'), 
				CONCAT(cwd.Descr_Status, _VarStr(' - oczekiw. na decyzję')), 
				cwd.Descr_Status
			)
		) AS Status_Current,
		cwd_before.Poziom,
		cwd.Status AS Curr_Status,
		cwd.Is_Ending AS Curr_Is_Ending,
		cwd.Poziom AS Curr_Poziom,
		GROUP_CONCAT(
			CONCAT(
				cwc.ModifyAt, ' - ', o.IdnUser, ' - > ', cwc_def.Descr_Status, 
				-- _VarStr(IF(IFNULL(cwc.Comment, '') != '', _VarStr(CONCAT('.\nZ adnotacją: "', _VarStr(cwc.Comment), '"')), ''))
				IFNULL(_Brackets(_VarStr('.\nZ adnotacją: "'), _VarStr(cwc.Comment), '"'), '')
			) 
			ORDER BY cwc.ModifyAt SEPARATOR '\n'
		) AS Confirm_log,
		GROUP_CONCAT(
			CONCAT(
				cwc_def.Descr_Status, 
				-- IF(IFNULL(cwc.Comment, '') != '', _VarStr(CONCAT('.\nZ adnotacją: "', _VarStr(cwc.Comment), '"')), '') 
				IFNULL(_Brackets(_VarStr('.\nZ adnotacją: "'), _VarStr(cwc.Comment), '"'), '')
			)
			ORDER BY cwc.ModifyAt SEPARATOR '\n'
		) AS Status_log,
		cw_next.IDN IS NOT NULL AS Next_Exists,
		cw_next.IDN AS Next_Idn_WorkFlow,
		-- było cw_next.ResponseCount IS NOT NULL AS Next_ResponseCount,
		IFNULL(cw_next.ResponseCount, 0) AS Next_ResponseCount,
		cw_next.Response AS Next_Response,
		(cw.Response = 'RESPONSE_100') AS Has_Decision,
		(cw_next.Response = 'RESPONSE_100') AS Next_Has_Decision,
		((IFNULL(cw_next.ResponseCount, 0) > 0) OR cw_next.Confirm_Status IS NOT NULL) AS IS_Disabled,
		cwdl.ExtNotify,
		(SELECT culowner.Osoba FROM `crm_usr_links` culowner WHERE culowner.commisionGUID = cw.commisionGUID AND culowner.Poziom = 2 LIMIT 1) AS Owner_GUID,
		(SELECT culcreate.Osoba FROM `crm_usr_links` culcreate WHERE culcreate.commisionGUID = cw.commisionGUID AND culcreate.Poziom = 0 LIMIT 1) AS Creator_GUID
	FROM `crm_workflow` cw
	LEFT JOIN `crm_workflow_def` cwd_before ON cwd_before.Def = cw.Def AND cwd_before.Status = cw.Start_Status
	LEFT JOIN `crm_workflow_def` cwd ON cwd.Def = cw.Def AND cwd.Status = cw.Confirm_Status
	-- LEFT JOIN `crm_workflow_def_links` cwdl ON cwdl.Def = cw.Def AND cwdl.Status_Start = cw.Start_Status AND cwdl.Status_End = cw.Confirm_Status
	LEFT JOIN `v_crm_workflow_def_links` cwdl ON cwdl.Def = cw.Def AND cwdl.Status_Start = cw.Start_Status AND cwdl.Status_End = cw.Confirm_Status
	
	LEFT JOIN `crm_workflow` cw_prev ON cw_prev.Idn = cw.Prev_Idn_WorkFlow	-- node poprzednie
	LEFT JOIN `crm_workflow` cw_next ON cw_next.Prev_Idn_WorkFlow = cw.Idn	-- node nastepne
	-- LEFT JOIN `crm_workflow_def_links` cwdl_prev ON cwdl_prev.Def = cw_prev.Def AND cwdl_prev.Status_Start = cw_prev.Start_Status AND cwdl_prev.Status_End = cw_prev.Confirm_Status
	LEFT JOIN `v_crm_workflow_def_links` cwdl_prev ON cwdl_prev.Def = cw_prev.Def AND cwdl_prev.Status_Start = cw_prev.Start_Status AND cwdl_prev.Status_End = cw_prev.Confirm_Status
	
	LEFT JOIN `crm_workflow_confirm` cwc ON cwc.IDN_workflow = cw.IDN
	LEFT JOIN `osoby` o ON o.GUID = cwc.Decision_UserGUID
	LEFT JOIN `crm_workflow_def` cwc_def ON cwc_def.Def = cw.Def AND cwc_def.Status = cwc.Confirm_Status
	
	LEFT JOIN `osoby` ouser ON ouser.GUID = cwc.UserGUID
	GROUP BY cw.IDN
	)
$$

CREATE OR REPLACE DEFINER=`root`@`localhost` VIEW `v_crm_workflow_Old` AS
	(
	SELECT
		cw.*,
		GROUP_CONCAT(ouser.IdnUser) AS IdnUser,
		GROUP_CONCAT(o.IdnUser) AS Decision_IdnUser,
		GROUP_CONCAT(CONCAT(ouser.Imie1, ' ', ouser.Nazwisko)) AS User_Nazwiska,
		GROUP_CONCAT(CONCAT(o.Imie1, ' ', o.Nazwisko)) AS Decision_User_Nazwiska,
		IFNULL(cwdl_prev.Descr_Item_Past, cwd_before.Descr_Status) AS Status_Before,
		IFNULL(cwdl.Descr_Item_Past, IF(NOT cwd.crm_Status IN (3, 4, 8, 11, 13, 14) AND NOT cwd.Is_Ending, CONCAT('brak decyzji - (', cwd.Descr_Status, ')'), cwd.Descr_Status)) AS Status_Current,
		cwd_before.Poziom,
		GROUP_CONCAT(CONCAT(cwc.ModifyAt, ' - ', o.IdnUser, ' - > ', cwc_def.Descr_Status, _VarStr(IF(IFNULL(cwc.Comment, '') != '', _VarStr(CONCAT('.\nZ adnotacją: "', _VarStr(cwc.Comment), '"')), ''))) ORDER BY cwc.ModifyAt SEPARATOR '\n') AS Confirm_log,
		GROUP_CONCAT(CONCAT(cwc_def.Descr_Status, IF(IFNULL(cwc.Comment, '') != '', _VarStr(CONCAT('.\nZ adnotacją: "', _VarStr(cwc.Comment), '"')), '')) ORDER BY cwc.ModifyAt SEPARATOR '\n') AS Status_log
	FROM `crm_workflow` cw
	LEFT JOIN `crm_workflow_def` cwd_before ON cwd_before.Def = cw.Def AND cwd_before.Status = cw.Start_Status
	LEFT JOIN `crm_workflow_def` cwd ON cwd.Def = cw.Def AND cwd.Status = cw.Confirm_Status
	LEFT JOIN `crm_workflow_def_links` cwdl ON cwdl.Def = cw.Def AND cwdl.Status_Start = cw.Start_Status AND cwdl.Status_End = cw.Confirm_Status
	
	LEFT JOIN `crm_workflow` cw_prev ON cw_prev.Idn = cw.Prev_Idn_WorkFlow
	LEFT JOIN `crm_workflow_def_links` cwdl_prev ON cwdl_prev.Def = cw_prev.Def AND cwdl_prev.Status_Start = cw_prev.Start_Status AND cwdl_prev.Status_End = cw_prev.Confirm_Status
	
	LEFT JOIN `crm_workflow_confirm` cwc ON cwc.IDN_workflow = cw.IDN
	LEFT JOIN `osoby` o ON o.GUID = cwc.Decision_UserGUID
	LEFT JOIN `crm_workflow_def` cwc_def ON cwc_def.Def = cw.Def AND cwc_def.Status = cwc.Confirm_Status
	
	INNER JOIN `osoby` ouser ON ouser.GUID = cwc.UserGUID
	
	GROUP BY cw.IDN
	)
$$

CREATE OR REPLACE DEFINER=`root`@`localhost` VIEW `v_crm_workflow_confirm` AS
	(
	SELECT
		cwc.*,
		o.IdnUser,
		CONCAT_WS(' ', o.Imie1, o.Nazwisko) AS Imiona_Nazwisko,
		CONCAT_WS(' ', odec.Imie1, odec.Nazwisko) AS Decision_Imiona_Nazwisko,
		-- cw.IDN,
		cw.Prev_IDN_workflow,
		cw.commisionGUID,
		cw.Def,
		--	cw.Start_Status,	
		cw.DecisionAt,
		cw.Status_Before,
		-- cw.Status_Current,
		cw.Status_Current_Str AS Status_Current,
		cw.Status_Current_Str,
		cw.Response,
		cw.Status_log,
		cw.Next_Exists,
		cw.Next_Idn_WorkFlow,
		cw.Next_ResponseCount,
		cw.Next_Response,
		cw.IS_Disabled,
		(NOT cw.IS_Disabled) AND (IFNULL(cw.Next_ResponseCount, 0) = 0) AND (cw.confirm_status IN ('', cw.Start_Status)) AS Decision_Enabled,
		-- cw.concession
		cw.Has_Decision,
		cw.Next_Has_Decision

		
	FROM `v_crm_workflow` cw
	INNER JOIN `crm_workflow_confirm` cwc ON cwc.IDN_workflow = cw.IDN
	INNER JOIN `osoby` o ON o.GUID = cwc.UserGUID
	LEFT JOIN `osoby` odec ON odec.GUID = cwc.Decision_UserGUID
	)
$$

CREATE OR REPLACE DEFINER=`root`@`localhost` VIEW `v_crm_workflow_coms2` AS
		SELECT
			c.IDN,
			c.GUID,
			c.Rodzaj, c.Podrodzaj,
			c.typ,
			c.Data,
			c.Temat,
			c.Opis,
			c.StartTime, c.EndTime, c.Duration, 
			c.status,
			c.status2,
			c.confirm_status,
			wd.Is_Ending AS confirm_status_Is_Ending,
			wd.Is_Ending = 2 AS confirm_status_Is_Cancel,
			(wd.Is_Ending != 2 AND c.status > 1) AS Enable_ExtRec,
			wd.param1 AS confirm_status_Param1,
			(
				(c.STATUS IN (3, 4, 8, 11, 13, 14)) 
				AND (NOW() > c.Data)
			) AS Status_IsArchiwum,
			wd.Descr_status,

			st.statusName AS Status_Info,
			st.Terminated AS Status_Is_Ending,
			culc.Osoba AS Creator_GUID,
			CONCAT_WS(' ', ocr.Imie1, ocr.Nazwisko) AS Creator_Kto,
			culo.Osoba AS Owner_GUID,
			CONCAT_WS(' ', oow.Imie1, oow.Nazwisko) AS Owner_Kto,
			cr.nazwa AS typ_nazwa,
			c.AreaGUID,
			c.NrWewn,
			CONCAT(d.Symbol, ' ', d.Nazwa) AS Oddzial_Str,
			ad.Idn AS Absencja_Rodzaj,
			IFNULL(ad.Idn, 0) > 0 AS IS_Absencja,
			ad.Kod_ABS,
			/*
			IF(IFNULL(ad.Idn, 0) > 0,
				'W.ABSENCJA',
					CASE 
						WHEN c.PodRodzaj = 1 THEN 'W.ZALICZKA'
						WHEN c.PodRodzaj = 2 THEN 'W.ZAPOMOGA'
						WHEN c.PodRodzaj = 3 THEN 'W.POZYCZKA'
						WHEN c.PodRodzaj = 5 THEN 'W.SZKOLENIE'
						WHEN c.PodRodzaj = 6 THEN 'W.DELEGACJA'
						ELSE 
							'???'
					END
			) AS Wniosek_Typ,
			*/
			cr.Alg_Typ AS Wniosek_Typ,
			IFNULL(ad.okres_typ, 'D') AS okres_typ, 	-- gdy nie absencja to na razie uznaj że "D" czyli dniowa
		--	do 19 XII było ...	
		--	cr.Def_confirm_statuses,
		--	od 19 XII jest ...	
			IFNULL(c.Def_confirm_statuses, cr.Def_confirm_statuses) AS Def_confirm_statuses,
			cr.Undo_After_End_Active_By,
			c.DeadLine,
			c.www_tag_text,
			c.Template_Osoba,
            DATE(CURRENT_DATE() - INTERVAL ad.LimitBackDaysWniosek DAY) AS LimitBackDate,
            ad.LimitBackDaysWniosek
		FROM `crm_commisions` c
		LEFT JOIN `crm_rodzaje` cr ON cr.rodzaj = c.rodzaj AND cr.podrodzaj = c.podrodzaj
		LEFT JOIN `status` st ON st.statusID = c.status
	--	do 19 XII było ...	
	--	LEFT JOIN `crm_workflow_def` wd ON wd.Def = cr.Def_confirm_statuses AND wd.Status = c.confirm_status
	--	od 19 XII jest ...	
		LEFT JOIN `crm_workflow_def` wd ON wd.Def = IFNULL(c.Def_confirm_statuses, cr.Def_confirm_statuses) AND wd.Status = c.confirm_status
		LEFT JOIN `erp_division_schema` d ON d.GUID = c.AreaGUID
		LEFT JOIN `absencje_dict` ad ON ad.Wniosek_rodzaj = c.Rodzaj AND ad.Wniosek_podrodzaj = c.Podrodzaj
		
		LEFT JOIN `crm_usr_links` culc ON culc.commisionGUID = c.GUID AND culc.Poziom = 0
		LEFT JOIN `osoby` ocr ON ocr.GUID = culc.Osoba
		LEFT JOIN `crm_usr_links` culo ON  culo.commisionGUID = c.GUID AND culo.Poziom = 2
		LEFT JOIN `osoby` oow ON oow.GUID = culc.Osoba
		-- inner join c.confirm_status
		WHERE
			c.Rodzaj = 16
$$

CREATE OR REPLACE DEFINER=`root`@`localhost` VIEW `v_crm_workflow_coms` AS
		SELECT
			c.GUID,
			c.Rodzaj, c.Podrodzaj,
			c.typ,
			c.Data,
			c.Temat,
			c.Opis,
			c.StartTime, c.EndTime, c.Duration, 
			c.status,
			c.status2,
			c.confirm_status,
			wd.Is_Ending AS confirm_status_Is_Ending,
			wd.Is_Ending = 2 AS confirm_status_Is_Cancel,
			wd.param1 AS confirm_status_Param1,
			(
				(c.STATUS IN (3, 4, 8, 11, 13, 14)) 
				AND (NOW() > c.Data)
			) AS Status_IsArchiwum,
			wd.Descr_status,

			st.statusName AS Status_Info,
			st.Terminated AS Status_Is_Ending,
			(SELECT culc.Osoba FROM `v_crm_usr_links_inf` culc WHERE culc.commisionGUID = c.GUID AND culc.Poziom = 0 LIMIT 1) AS Creator_GUID,
			(SELECT culc.Imiona_Nazwisko FROM `v_crm_usr_links_inf` culc WHERE culc.commisionGUID = c.GUID AND culc.Poziom = 0 LIMIT 1) AS Creator_Kto,
			(SELECT culo.Osoba FROM `v_crm_usr_links_inf` culo WHERE culo.commisionGUID = c.GUID AND culo.Poziom = 2 LIMIT 1) AS Owner_GUID,
			(SELECT culo.Imiona_Nazwisko FROM `v_crm_usr_links_inf` culo WHERE culo.commisionGUID = c.GUID AND culo.Poziom = 2 LIMIT 1) AS Owner_Kto,
			cr.nazwa AS typ_nazwa,
			c.AreaGUID,
			c.NrWewn,
			CONCAT(d.Symbol, ' ', d.Nazwa) AS Oddzial_Str,
			ad.Idn AS Absencja_Rodzaj,
			IFNULL(ad.Idn, 0) > 0 AS IS_Absencja,
			/*
			IF(IFNULL(ad.Idn, 0) > 0,
				'W.ABSENCJA',
					CASE 
						WHEN c.PodRodzaj = 1 THEN 'W.ZALICZKA'
						WHEN c.PodRodzaj = 2 THEN 'W.ZAPOMOGA'
						WHEN c.PodRodzaj = 3 THEN 'W.POZYCZKA'
						WHEN c.PodRodzaj = 5 THEN 'W.SZKOLENIE'
						WHEN c.PodRodzaj = 6 THEN 'W.DELEGACJA'
						ELSE 
							'???'
					END
			) AS Wniosek_Typ,
			*/
			cr.Alg_Typ AS Wniosek_Typ,
			IFNULL(ad.okres_typ, 'D') AS okres_typ, 	-- gdy nie absencja to na razie uznaj że "D" czyli dniowa
		--	do 19 XII było ...	
		--	cr.Def_confirm_statuses,
		--	od 19 XII jest ...	
			IFNULL(c.Def_confirm_statuses, cr.Def_confirm_statuses) AS Def_confirm_statuses,
			c.DeadLine
		FROM `crm_commisions` c
		LEFT JOIN `crm_rodzaje` cr ON cr.rodzaj = c.rodzaj AND cr.podrodzaj = c.podrodzaj
		LEFT JOIN `status` st ON st.statusID = c.status
	--	do 19 XII było ...	
	--	LEFT JOIN `crm_workflow_def` wd ON wd.Def = cr.Def_confirm_statuses AND wd.Status = c.confirm_status
	--	od 19 XII jest ...	
		LEFT JOIN `crm_workflow_def` wd ON wd.Def = IFNULL(c.Def_confirm_statuses, cr.Def_confirm_statuses) AND wd.Status = c.confirm_status
		LEFT JOIN `erp_division_schema` d ON d.GUID = c.AreaGUID
		LEFT JOIN `absencje_dict` ad ON ad.Wniosek_rodzaj = c.Rodzaj AND ad.Wniosek_podrodzaj = c.Podrodzaj
		-- inner join c.confirm_status
		WHERE
			c.Rodzaj = 16
$$


CREATE OR REPLACE DEFINER=`root`@`localhost` VIEW `v_wfc_osoby_cwc` AS
				SELECT
					cw.commisionGUID, 
					go.OsobaGUID,
					cwc.IDN_workflow,
					cw.Def,
					cw.Start_Status, 
					cw.Confirm_Status,
					cw_next.IDN AS Next_Idn_WorkFlow,
					(cw.Response = 'RESPONSE_100') AS Has_Decision,
					(cw_next.Response = 'RESPONSE_100') AS Next_Has_Decision,
					cwd.Is_Ending > 0 AS Is_Ending,
					cwd.Is_Ending = 2 AS Is_Cancel,
					cwd.param1 AS confirm_status_Param1,
					(cwd.Is_Ending > 0) AS Is_Archiwum,
					(IFNULL(cw_next.ResponseCount, 0) = 0) AND (cw.confirm_status IN ('', cw.Start_Status)) AS Decision_Enabled,
					(cwc.Decision_UserGUID = '') AS Do_decyzji,
					(
						NOT (cwc.confirm_status IN ('', cw.Start_Status)) 
						-- AND cwc.Decision_UserGUID = #UserGUID#
						-- AND cwc.Decision_UserGUID = @_USER
						AND IFNULL(cw_next.ResponseCount, 0) = 0
						AND cwc.ModifyAT > CURRENT_DATE() - INTERVAL 42 DAY
					) AS Undo_Enabled_For_Decision_UserGUID,
					cwc.Decision_UserGUID
			--	decyzje		
				FROM `crm_workflow_confirm` cwc
			--	grupa/osoba decyzyjna
				INNER JOIN `grupy_osob` go ON go.grupaGUID = cwc.UserGUID -- AND go.OsobaGUID  = @_USER 
			--	decyzja dotyczy: node workflow	
				INNER JOIN `crm_workflow` cw ON cw.IDN = cwc.IDN_workflow
			--	nast. node workflow	
				LEFT JOIN `crm_workflow` cw_next ON cw_next.Prev_Idn_WorkFlow = cw.Idn	-- node nastepne
	--	aktualny status noda workflow	
		LEFT JOIN `crm_workflow_def` cwd ON cwd.Def = cw.Def AND cwd.Status = cw.confirm_status
	--	status dot. decyzji jako para status start -> status end
		LEFT JOIN `crm_workflow_def_links` cwdl ON cwdl.Def = cw.Def AND cwdl.Status_Start = cw.Start_Status AND cwdl.Status_End = cw.confirm_status 
	--	zapasowy status dot. decyzji jesli ten wyżej link zwróci null - wtedy decyzja jako stan wybranej decyzji
		LEFT JOIN `crm_workflow_def` cwddec ON cwddec.Def = cw.Def AND cwddec.Status = cwc.confirm_status
				WHERE
					TRUE
					-- AND go.grupaGUID = cwc.UserGUID -- AND go.OsobaGUID  = @_USER 
					-- AND cw.Idn = cwc.IDN_workflow
					AND cw.CreateAt > NOW() - INTERVAL 2 YEAR
$$

--	szybka choc nie najszybsza ale zawiera ost. idny workflow i workflow_confirm
CREATE OR REPLACE DEFINER=`root`@`localhost` VIEW `v_wf_osoby_cwc` AS
				SELECT
					cw.commisionGUID, go.OsobaGUID -- AS UserGUID
					,cw.Def
					,EXISTS(SELECT cul.Idn FROM `crm_usr_links` cul WHERE cul.commisionGUID = cw.commisionGUID AND cul.Osoba = go.OsobaGUID AND cul.Poziom = 2) AS UserIsOwner
					, MAX(cwc.IDN) AS UserLast_cwcIDN
					, MAX(cw.IDN) AS UserLast_cwIDN
					, MAX(IF(cwc.Decision_UserGUID = go.OsobaGUID, cwc.IDN, 0)) AS UserLast_cwcIDN_Decyzja
					, MAX(IF(cwc.Decision_UserGUID = go.OsobaGUID, cw.IDN, 0)) AS UserLast_cwIDN_Decyzja
					-- , MAX(cwc.IDN) AS OsobaLastConfIdn
				FROM `crm_workflow_confirm` cwc, `grupy_osob` go, `crm_workflow` cw
				, `_user_accounts` ua 
				--	co to jest {81DD4230-B484-44D5-BDBF-560387E0EE0F}
				WHERE
					TRUE
					AND go.grupaGUID = cwc.UserGUID -- AND go.OsobaGUID  = @_USER 
					AND cw.Idn = cwc.IDN_workflow
					AND cw.CreateAt > NOW() - INTERVAL 2 YEAR
					AND ua.osobaGUID = go.osobaGUID
				GROUP BY commisionGUID, OsobaGUID
	
$$

--	naszybsza linkownia osob wok flow - wychodzimy od cwc czyli od uczestników decyzji `crm_workflow_confirm`
CREATE OR REPLACE DEFINER=`root`@`localhost` VIEW `v_wf_osoby_cwc_1` AS
				SELECT
					cw.commisionGUID, go.OsobaGUID 
				FROM `crm_workflow_confirm` cwc, `grupy_osob` go, `crm_workflow` cw
				WHERE
					TRUE
					AND go.grupaGUID = cwc.UserGUID -- AND go.OsobaGUID  = @_USER 
					AND cw.Idn = cwc.IDN_workflow
					--	jmam VII 2023	AND cw.CreateAt > NOW() - INTERVAL 2 YEAR
				GROUP BY commisionGUID, OsobaGUID
$$

--	rozbudowana cwc - patrz wyżej - dodano AsOwner i OwnerLastConfIdn
CREATE OR REPLACE DEFINER=`root`@`localhost` VIEW `v_wf_osoby_cwc_2` AS
				SELECT
					cw.commisionGUID, go.OsobaGUID 
					,EXISTS(SELECT cul.Idn FROM `crm_usr_links` cul WHERE cul.commisionGUID = cw.commisionGUID AND cul.Osoba = go.OsobaGUID AND cul.Poziom = 2) AS OsobaAsOwner
					, MAX(cwc.IDN) AS OsobaLastConfIdn
				FROM `crm_workflow_confirm` cwc, `grupy_osob` go, `crm_workflow` cw
				WHERE
					TRUE
					AND go.grupaGUID = cwc.UserGUID -- AND go.OsobaGUID  = @_USER 
					AND cw.Idn = cwc.IDN_workflow
					--	jmam VII 2023	AND cw.CreateAt > NOW() - INTERVAL 2 YEAR
				GROUP BY commisionGUID, OsobaGUID
	
$$

--	naszybsza linkownia osob wok flow - wychodzimy od cwc
CREATE OR REPLACE DEFINER=`root`@`localhost` VIEW `v_wf_osoby_cul_1` AS
				SELECT
					cul.commisionGUID, go.OsobaGUID 
				FROM `crm_workflow` cw, `crm_usr_links` cul, `grupy_osob` go
				WHERE
					TRUE
					--	jmam VII 2023	AND cw.CreateAt > NOW() - INTERVAL 2 YEAR
					AND cul.commisionGUID = cw.commisionGUID
					AND go.grupaGUID = cul.Osoba -- AND go.OsobaGUID  = @_USER 
				GROUP BY commisionGUID, OsobaGUID
$$

CREATE OR REPLACE DEFINER=`root`@`localhost` VIEW `v_wf_osoby_cul_2` AS
				SELECT
					cul.commisionGUID, go.OsobaGUID, MAX(cul.Poziom = 2) AS AsOwner 
					, (SELECT MAX(cwc.IDN) FROM `crm_workflow_confirm` cwc WHERE cwc.Idn_WorkFlow = cw.IDN AND cwc.UserGUID = go.OsobaGUID) AS OwnerLastConfIdn
				FROM `crm_workflow` cw, `crm_usr_links` cul, `grupy_osob` go
				-- LEFT JOIN `crm_usr_links` cul ON cul.commisionGUID = cw.commisionGUID AND cul.Osoba = go.OsobaGUID AND cul.Poziom = 2
				WHERE
					TRUE
					--	jmam VII 2023	AND cw.CreateAt > NOW() - INTERVAL 1 YEAR
					AND cw.commisionGUID = cul.commisionGUID
					AND go.grupaGUID = cul.Osoba -- AND go.OsobaGUID  = @_USER 
				GROUP BY cul.commisionGUID, OsobaGUID
$$

/*

*/
DELIMITER ;

/*
$Log$
*/
