DELIMITER $$

DROP PROCEDURE IF EXISTS `crist_zadania`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `crist_zadania`(
	IN _MODE VARCHAR(38), 
	IN _GUID VARCHAR(38),
	IN _WHERE_SQL TEXT, 
	IN _Param_A VARCHAR(50),
	IN _Param_B VARCHAR(50))
SPROC: BEGIN 

/*
CALL crist_zadania('Prepare','','','','');
CALL crist_zadania('Lista','','','','');
CALL crist_zadania('HeadMail','{11748169-D6D6-4ADE-B2F6-EC62C4CE9A12}','','','');
CALL crist_zadania('BodyMail','{11748169-D6D6-4ADE-B2F6-EC62C4CE9A12}','','','');

*/

	IF (_MODE = 'Prepare') THEN

		DROP TABLE IF EXISTS t_crist_zadania;
		
		CREATE TABLE t_crist_zadania
			SELECT 
			  c.GUID as zadanieGUID,
			  c.idn as idnzadania, 
			  c.temat as tematzadania,
			  cl.idn as idnlog,
			  LEFT(cl.modify_date,16) AS kiedy,  
			  _DisplayDurationFormat(time_sheet) as czas,
			  cl.jest AS komentarz,
			  IF(cl.commissionguid = '{11748169-D6D6-4ADE-B2F6-EC62C4CE9A12}','W','Z') as ZadanieWorek
			FROM 
			crm_log cl
			JOIN crm_commisions c ON c.guid=cl.commissionguid 
			JOIN crm_usr_links cul ON cul.commisionGUID = c.guid 
			WHERE 
			cl.zmiana_dotyczy IN ('TIME_SHEET', 'ADD_COMMENT') 
			AND cl.publiczny=1
			AND cl.modify_date LIKE CONCAT(ADDDATE(CURRENT_DATE(), INTERVAL -2 DAY),'%')
			AND cul.kontrguid IN (SELECT GUID FROM kontrah WHERE nazwa LIKE '%CRIST%')
			ORDER BY kiedy
		;
	
	END IF;
	
	IF (_MODE = 'Clean') THEN

		DROP TABLE IF EXISTS t_crist_zadania;
		
	END IF;	

	IF (_MODE = 'Lista') THEN

		SELECT distinct zadanieGUID FROM t_crist_zadania;
		
	END IF;	
	
	IF (_MODE = 'SubjectMail') THEN
	
		SELECT 
		  CONCAT('CRIST (',idnzadania,'): ',IF(ZadanieWorek='W','Worek zbiorczy',tematzadania)) as temat
		FROM 
		  t_crist_zadania
		WHERE
		  zadanieGUID = _GUID
		ORDER BY 
		  kiedy
		LIMIT 1
		;
		
	END IF;
	IF (_MODE = 'HeadMail') THEN
	
		SELECT 
		  IF(ZadanieWorek='W','Worek zbiorczy',tematzadania) as temat
		FROM 
		  t_crist_zadania
		WHERE
		  zadanieGUID = _GUID
		ORDER BY 
		  kiedy
		LIMIT 1
		;
		
	END IF;

	IF (_MODE = 'BodyMail0') THEN
	
		SELECT distinct 
		  CONCAT("<HTML><HEAD> <META HTTP-EQUIV=\"Content-Type\" CONTENT=\"text/html; charset=Windows-1250\"> </HEAD><BODY>","<span style='font-size: 12pt; font-family: Arial'>Lista wykonanych prac dla zadania:<br> <b>(",idnzadania,") \"",tematzadania,"\"</b> </span><br><br>") as wynik
		FROM 
		  t_crist_zadania
		WHERE
		  zadanieGUID = _GUID
		ORDER BY 
		  kiedy
		;
		
	END IF;

	IF (_MODE = 'BodyMailEnd') THEN
	
		SELECT c.idn FROM  crm_commision_link ccl LEFT JOIN crm_commisions c ON c.GUID=ccl.link_to WHERE ccl.link_from=_GUID AND ccl.link_typ=1 INTO @GUIDOjca;
	
		SELECT distinct 
		  CONCAT("<br>Wiadomosc zostala wygenerowana autormatycznie i prosimy na nia nie odpowiadac.<br>",
		  IF(ZadanieWorek<>'W',CONCAT("<br>Osoba zglaszajaca z CRIST: <b>",(SELECT 
GROUP_CONCAT(CONCAT(o.Imiona,' ',o.Nazwisko)) AS osoby
FROM  crm_usr_links cul
JOIN osoby o ON o.guid=cul.osoba AND poziom=4
WHERE commisionGUID= _GUID ),"</b><br>"),''),"Link do zdarzenia w WebCRM: <a href=\"http://motlawa.int.com.pl/crm/crmrec.php?idn=",IF(@GUIDOjca>10,@GUIDOjca,idnzadania),"\">http://motlawa.int.com.pl/crm/crmrec.php?idn=",IF(@GUIDOjca>10,@GUIDOjca,idnzadania),"</a> </BODY></HTML>") as wynik
		FROM 
		  t_crist_zadania
		WHERE
		  zadanieGUID = _GUID
		ORDER BY 
		  kiedy
		;
		
	END IF;

	IF (_MODE = 'BodyMail') THEN
	
		SELECT distinct 
		  idnlog, kiedy as od_kiedy, czas as czas_wykonania, komentarz
		FROM 
		  t_crist_zadania
		WHERE
		  zadanieGUID = _GUID
		ORDER BY 
		  kiedy
		;
		
	END IF;

END$$

DELIMITER ;

