 /*!40101 SET NAMES utf8 */;
DELIMITER $$

CREATE OR REPLACE DEFINER=`root`@`localhost` VIEW `v_crm_workflow` 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,
		/* */
		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, ')'), 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), '"')), ''))
				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
	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_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(o.Imie1, ' ', o.Nazwisko) AS Imiona_Nazwisko,
		CONCAT(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.Response,
		cw.Status_log
	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
	)
$$


DELIMITER ;
