 SET NAMES UTF8;

DELIMITER $$

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

/*
CALL mail_alerty('_Kontrlist','','','IZO%','hourly','','');
CALL mail_alerty('CRM_LOG_Prepare','','','IZO%','hourly','',''); -- daily, weekly
CALL mail_alerty('CRM_LOG_Prepare_Projekty','','','','hourly','',''); 
CALL mail_alerty('CRM_LOG_SetSent','{11748169-D6D6-4ADE-B2F6-EC62C4CE9A12}','','','','','');
CALL mail_alerty('Lista','','','','','','');
CALL mail_alerty('HeadMail','{11748169-D6D6-4ADE-B2F6-EC62C4CE9A12}','','','','','');
CALL mail_alerty('BodyMail','{11748169-D6D6-4ADE-B2F6-EC62C4CE9A12}','','','','','');
CALL mail_alerty('CRM_LOG_Prepare_CUBE','','','IZO%','','',''); 
CALL mail_alerty('CRM_LOG_WebCRM_CUBE','','','IZOHAN_ZOO','','','');
*/


SET @debug=_Param_D like '%DEBUG%';

IF _MODE = '_KontrList' THEN
    -- mod wewnętrzny - tabela kontrahentów z parametrami wysylki logów
    SET @mailer_hourly = IF(_Param_B like '%hourly%',1,-1);
    SET @mailer_daily = IF(_Param_B like '%daily%',1,-1);
    SET @mailer_weekly = IF(_Param_B like '%weekly%',1,-1);
    SET @mailer_cube = IF(_Param_B like '%cube%',1,-1);
    DROP TABLE IF EXISTS t_mail_alerty_kontrah;
    CREATE TABLE t_mail_alerty_kontrah
    SELECT k.guid as kontrguid,k.symbol,
    sum(kd.symbol LIKE 'WebCRM_mail_szczegolowy') as szczegolowy,
    sum(kd.symbol LIKE 'WebCRM_mail_hourly') as hourly,
    sum(kd.symbol LIKE 'WebCRM_mail_daily') as daily,
    sum(kd.symbol LIKE 'WebCRM_mail_weekly') as weekly,
    sum(kd.symbol LIKE 'WebCRM_mail_cube') as addcube,
    IFNULL((SELECT group_concat(kn.adres separator ',') 
        FROM kontakty kn WHERE kn.kontrguid=k.guid AND kn.opis='WebCRM_DW'),
    'int@int.com.pl') as mail_cc
    FROM kategorie_def kd 
    INNER JOIN kategorie_links kl ON kl.guid_kat=kd.guid 
    INNER JOIN kontrah k ON k.guid=kl.guid_link
    WHERE kd.symbol like 'WebCRM%' AND (_Param_A='' OR k.symbol like _Param_A) AND (_GUID='' OR k.guid=_GUID)
    GROUP BY kontrguid
    HAVING hourly=@mailer_hourly OR daily=@mailer_daily OR weekly=@mailer_weekly OR addcube=@mailer_cube
    ;
END IF;
/*
 tabela tymczasowa zawiera guidy zdarzen oznaczonych jako log publiczny zaleznie od ustawienia w karcie kontrahenta - albo szczegolowy bez wzgledu na status (WebCRM_mail_szczegolowy
*/
IF _MODE = 'CRM_LOG_Prepare' THEN
    CALL mail_alerty('_Kontrlist','','',_Param_A,_Param_B,_Param_C,_Param_D);
    DROP TABLE IF EXISTS t_mail_alerty_zdarzenia;
    CREATE TABLE t_mail_alerty_zdarzenia
    SELECT
    k.symbol as symbol_klienta,k.mail_cc,c.GUID AS commissionGUID,c.idn,c.rodzaj,c.status, 
    c.temat,'' as nrzamw,cl.idn AS idnlog,LEFT(cl.modify_date,16) AS kiedy,  
    _DisplayDurationFormat(time_sheet) AS czas,_VarStr(cl.jest) AS komentarz,
    IF((c.temat like '%raporty nieprzydzielone%') or (c.temat like '%bie__ca%obs_uga%') -- TODO inaczej oznaczac
    ,'W','Z') AS ZadanieWorek,
    concat(o.imie1,' ',o.nazwisko) as osoba
    FROM crm_log cl
    INNER JOIN crm_commisions c  ON c.guid=cl.commissionguid 
    INNER JOIN crm_usr_links cul ON cul.commisionGUID = c.guid AND cul.poziom=4
    INNER JOIN osoby o ON cl.osobaGUID = o.guid
    INNER JOIN t_mail_alerty_kontrah k ON k.kontrguid=cul.kontrguid
    WHERE cl.zmiana_dotyczy IN ('TIME_SHEET', 'ADD_COMMENT','CHANGE_STATUS') 
    AND cl.publiczny=1 AND cl.info_mail IS NULL -- nie wyslany - 
    AND DATE(cl.modify_date) >= CURRENT_DATE - INTERVAL 7 DAY 
    AND ((DATE(cl.modify_date) <= CURRENT_DATE - INTERVAL 1 DAY) OR k.hourly)
    AND (k.szczegolowy OR (c.status=13 AND c.rodzaj=8))
    AND (c.nrwewn NOT LIKE '%$%') -- AS
    ORDER BY kiedy;
    ALTER TABLE t_mail_alerty_zdarzenia ADD INDEX commissionGUID(commissionGUID),ADD INDEX idnlog(idnlog);
END IF;
        
IF _MODE = 'CRM_LOG_Prepare_Projekty' THEN
    DROP TABLE IF EXISTS t_mail_alerty_zdarzenia;
    CREATE TABLE t_mail_alerty_zdarzenia
    SELECT DISTINCT
    -- IFNULL(k.symbol,"BRAK KLIENTA") as symbol_klienta,
    IFNULL(k.nazwa,"BRAK KLIENTA") as symbol_klienta,
    "" as mail_cc,c.GUID AS commissionGUID,c.idn,c.rodzaj,c.status, 
    c.temat,IFNULL((SELECT n.nrzamw from zmng n WHERE n.guid IN (c.parentguid,c.guid)),'') as nrzamw,
    cl.idn AS idnlog,LEFT(cl.modify_date,16) AS kiedy,  
    _DisplayDurationFormat(time_sheet) AS czas,_VarStr(cl.jest) AS komentarz,
    'Z' AS ZadanieWorek,
    concat(o.imie1,' ',o.nazwisko) as osoba
    FROM crm_log cl
    INNER JOIN crm_commisions c  ON c.guid=cl.commissionguid 
    INNER JOIN crm_usr_links cul ON cul.commisionGUID = c.guid AND cul.poziom in (1,2)
    LEFT  JOIN crm_usr_links culk ON culk.commisionGUID = c.guid AND culk.poziom in (4)
    LEFT JOIN osoby o ON cl.osobaGUID = o.guid
--      LEFT  JOIN zmng z ON c.GUID = z.guid
    LEFT  JOIN kontrah k ON k.guid=culk.kontrguid
    WHERE --- cl.zmiana_dotyczy IN ('TIME_SHEET', 'ADD_COMMENT','CHANGE_STATUS','CHANGE_PROGRESS','CHANGE_DESCR','CHANGE_USERS') AND
    cl.publiczny in (0,1) AND
    (@debug OR cl.info_mail IS NULL) AND -- nie wyslany - 
    DATE(cl.modify_date) >= CURRENT_DATE - INTERVAL 7 DAY AND 
--        ((DATE(cl.modify_date) <= CURRENT_DATE - INTERVAL 1 DAY) OR k.hourly)
    (c.rodzaj in (0,8)) -- AND 
    -- (c.nrwewn NOT LIKE '%XX%')
    
    ORDER BY kiedy;
    ALTER TABLE t_mail_alerty_zdarzenia ADD INDEX commissionGUID(commissionGUID),ADD INDEX idnlog(idnlog);
    IF @debug THEN
        select * from t_mail_alerty_zdarzenia;
    END IF;
END IF;
        
IF _MODE = 'CRM_LOG_GetList' THEN
    SELECT DISTINCT commissionGUID FROM t_mail_alerty_zdarzenia;
END IF;    

IF _MODE IN('CRM_LOG_MAILTO','CRM_LOG_MAILTO_Projekty') THEN
    SELECT GROUP_CONCAT(DISTINCT CONCAT(o.imie1,' ',o.nazwisko,'<',
        IFNULL(NULLIF(u.ad_mail,''),
        IFNULL(k.adres,'int@int.com.pl')),'>'))
    INTO @mail_to
    FROM crm_commisions c
    INNER JOIN crm_usr_links cul ON cul.commisionGUID = c.GUID AND IF(_MODE='CRM_LOG_MAILTO',cul.poziom=4,cul.poziom in (1,2))
    INNER JOIN osoby o ON o.guid=cul.osoba AND o.rodzaj=1 -- tylko osoby, nie grupy
    LEFT JOIN _user_accounts u ON u.osobaguid=o.guid
    LEFT JOIN kontakty k ON k.osobaGUID=cul.osoba AND k.typ_medium=3 AND k.kontrGUID<>''
    WHERE c.GUID = _GUID;
    SELECT IFNULL(@mail_to,'BRAK ZGŁASZAJĄCEGO <int@int.com.pl>')  as adresy;
END IF; 

IF _MODE = 'CRM_LOG_MAILCC' THEN
    SELECT GROUP_CONCAT(DISTINCT CONCAT(o.imie1,' ',o.nazwisko,'<',
        IFNULL(NULLIF(u.ad_mail,''),
        IFNULL(k.adres,'int@int.com.pl')),'>'))
    INTO @mail_cc
    FROM crm_commisions c
    INNER JOIN crm_usr_links cul ON cul.commisionGUID = c.GUID AND cul.poziom=3
    INNER JOIN osoby o ON o.guid=cul.osoba AND o.rodzaj=1 -- tylko osoby, nie grupy 
    LEFT JOIN _user_accounts u ON u.osobaguid=o.guid
    LEFT JOIN kontakty k ON k.osobaGUID=cul.osoba AND k.typ_medium=3 AND k.kontrGUID<>''
    WHERE c.GUID = _GUID 
    ;
    IF (select Database()='int') THEN
        SELECT CONCAT('int@int.com.pl,zarzad@int.com.pl,',
        ifnull(CONCAT(@mail_cc,','),''),
        (SELECT t.mail_cc FROM t_mail_alerty_zdarzenia t WHERE t.commissionGUID = _GUID LIMIT 1)) as adresy;
    ELSE
        SELECT IFNULL(@mail_cc,'') as adresy;
    END IF; 
END IF; 
    
IF (_MODE = 'CRM_LOG_Subject') THEN
    SELECT CONCAT(symbol_klienta, ' (',_VarStr(idn),'): ',IF(ZadanieWorek='W','Zadanie zbiorcze',temat)) AS temat
    FROM t_mail_alerty_zdarzenia
    WHERE commissionGUID = _GUID
    ORDER BY kiedy LIMIT 1;
END IF;

IF (_MODE = 'CRM_LOG_Subject_Projekty') THEN
    
    SELECT CONCAT(nrzamw,' ',symbol_klienta, ' (',_VarStr(idn),'): ',temat) AS temat
    FROM t_mail_alerty_zdarzenia
    WHERE commissionGUID = _GUID
    ORDER BY kiedy LIMIT 1;
END IF;

IF (_MODE LIKE 'CRM_LOG_Header%') THEN
    SET @html_header=CONCAT('<HTML><HEAD><META HTTP-EQUIV=\"Content-Type\" CONTENT=\"text/html; charset=UTF-8\">',
        '<style>table,th,td{border:1px solid black; border-spacing:0; border-collapse:collapse; font-family: Arial; font-size: 9pt;} body {font-family: Arial; font-size: 9pt;}</style></HEAD><BODY>',
        "<img src='http://motlawa.int.com.pl/img/crmlogo120.jpg' align=left width=120><span style='font-size: 12pt; font-family: Arial'>");
END IF;

IF (_MODE = 'CRM_LOG_Header') THEN
    SELECT DISTINCT CONCAT(@html_header,'Lista czynności dla zadania:<br> <b>(',_VARSTR(idn),') ',temat,'</b> </span><br><br>') AS header
    FROM t_mail_alerty_zdarzenia
    WHERE commissionGUID = _GUID
    ORDER BY kiedy;
END IF;

IF (_MODE = 'CRM_LOG_Body') THEN
    SELECT DISTINCT 
        idnlog, osoba, kiedy, czas AS czas_pracy, komentarz
    FROM t_mail_alerty_zdarzenia
    WHERE commissionGUID = _GUID
    ORDER BY kiedy;
END IF;

IF (_MODE LIKE 'CRM_LOG_Footer%') THEN
    SET @tekst1=_UTF8'<br>Wiadomość automatyczna: prosimy na nią nie odpowiadać.<br>';
END IF;

IF (_MODE LIKE 'CRM_LOG_Footer_Projekty') THEN
    SELECT @tekst1;
END IF;

IF (_MODE = 'CRM_LOG_Footer') THEN
    SELECT c.idn
    INTO @IdnOjca
    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;
    SELECT DISTINCT CONCAT(@tekst1,
        IF(ZadanieWorek<>'W',CONCAT('<br>Osoba zglaszajaca ze strony klienta: <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 cul.poziom=4 WHERE cul.commisionGUID= _GUID ),
        '</b><br>'),''),
        '<a href="https://motlawa.int.com.pl/DevelopStuff/index.php?crmidn=',
        _VarStr(IF(@IDNOjca>10,@IDNOjca,idn)),'#/crm_edit\">Link do zdarzenia w WebCRM</a></BODY></HTML>') AS footer
    FROM t_mail_alerty_zdarzenia
    WHERE commissionGUID = _GUID;
END IF;

-- @@@@@@@@@@@@@@@@@@@@@@@@@@ CUBE

IF _MODE = 'CRM_LOG_MAILTO_CUBE' THEN
    SELECT GROUP_CONCAT(DISTINCT CONCAT(IFNULL(CONCAT(o.imie1,' ',o.nazwisko),''),
        IFNULL(CONCAT('<',k.adres,'>'),' BRAK ADRESU <int@int.com.pl>')))
    INTO @mail_to
    FROM kontakty k
    LEFT JOIN osoby o ON o.guid=k.osobaguid AND o.rodzaj=1 
    WHERE k.typ_medium=3 AND k.opis='WebCRM_DW' AND k.kontrGUID = _GUID;
    SELECT IFNULL(@mail_to,'BRAK ZGŁASZAJĄCEGO <int@int.com.pl>')  as adresy;
END IF; 

IF _MODE = 'CRM_LOG_GetList_CUBE' THEN
    SELECT DISTINCT kontrGUID FROM t_mail_alerty_zdarzenia_cube;
END IF;    

IF _MODE = 'CRM_LOG_Prepare_CUBE' THEN
    
    SET @months=IF(_PARAM_D='',2,_VarInt(_PARAM_D));
    
    CALL mail_alerty('_Kontrlist',_GUID,_Where_SQL,_Param_A,'cube',_Param_C,_Param_D);
    DROP TABLE IF EXISTS t_mail_alerty_zdarzenia_cube;
    CREATE TABLE t_mail_alerty_zdarzenia_cube
    SELECT
    year(L.modify_date) as rok,
    lpad(month(L.modify_date),2,'0') as mc,
    count(distinct C.idn) as ileCrm,
    sum(L.time_sheet) as czas,
    if(C.nrwewn like "%.wiz%" or C.nrwewn like "%.spot%","WIZYTY",
        if(C.nrwewn like "%reklam%" OR L.keywords like "%[R]%","REKLAMACJE",
        if(C.nrwewn like "%podsta%","PODSTAWOWE",
        if(C.nrwewn like "%dodatkow%.$%","DODATKOWE_$",
        if(C.nrwewn like "%dodatkow%","DODATKOWE","?"))))) as crodzaj,
    -- ifnull(nullif(L.kontrGUID,''),cul.kontrguid) as kontrguid,
    concat(/*group_concat(L.modify_date),*/'Czas:',round(sum(L.time_sheet),2),' ZG:',IFNULL((Select p.idn from crm_commisions p where p.guid=C.ParentGUID),'brak zg'),':',C.idn,':',C.nrwewn,' ',C.temat) as crmidn,
    -- group_concat(distinct concat(C.idn,':',C.nrwewn) separator "#BR#") as crmidn,
    (SELECT cul.kontrguid FROM crm_usr_links cul WHERE cul.commisionGUID = C.guid AND cul.poziom=4 LIMIT 1) as kontrguid
--        ,k.symbol as symbol_klienta
    FROM crm_log L
    INNER JOIN crm_commisions C ON L.commissionguid=C.GUID
--      INNER JOIN crm_usr_links cul ON cul.commisionGUID = C.guid AND cul.poziom=4
--      INNER JOIN t_mail_alerty_kontrah k ON k.kontrguid=cul.kontrguid AND k.addcube
    WHERE  C.template_osoba =''  AND C.Rodzaj in (8,5) and date(L.modify_date)>last_day(current_date-interval @months month) AND L.time_sheet>0
    AND L.commissionguid IN 
    (SELECT DISTINCT cul.commisionGUID 
        FROM crm_usr_links cul, t_mail_alerty_kontrah k WHERE k.kontrguid=cul.kontrguid AND k.addcube AND cul.poziom=4)
    GROUP BY  kontrguid,rok,mc,crodzaj,C.idn;
    ALTER TABLE t_mail_alerty_zdarzenia_cube ADD INDEX kontrGUID(kontrGUID);
END IF;

IF (_MODE = 'CRM_LOG_Subject_CUBE') THEN
    SELECT CONCAT(k.symbol, ' - zestawienie zbiorcze ',_varstr(now())) AS temat
    FROM t_mail_alerty_zdarzenia_cube t, kontrah k 
    WHERE k.guid=t.kontrguid AND t.kontrGUID = _GUID LIMIT 1;
END IF;

IF (_MODE = 'CRM_LOG_Header_CUBE') THEN
    SELECT DISTINCT CONCAT(@html_header,'Zestawienie zbiocze czynności serwisowych na dzień:<br> ',current_date,' dla:',k.symbol,',</span><br><br>') AS header
    FROM t_mail_alerty_zdarzenia_cube t, kontrah k 
    WHERE k.guid=t.kontrguid AND t.kontrGUID = _GUID LIMIT 1;
END IF;

IF (_MODE = 'CRM_LOG_BODY_CUBE') THEN
    SELECT ifnull(rok,'RAZEM') as rok,ifnull(mc,'RAZEM') as mc,ifnull(crodzaj,'RAZEM') as crodzaj,sum(ilecrm) as ile,round(sum(czas)) as czas
--        ,group_concat(distinct crmidn  separator "#BR#") as idny 
    FROM t_mail_alerty_zdarzenia_cube  t
    WHERE t.kontrguid=_GUID
    GROUP BY rok,mc,crodzaj WITH ROLLUP;
    SELECT "<BR>" as `<BR>`;
    SELECT mc,crodzaj as rodzaj,sum(ilecrm) as ile,round(sum(czas)) as czas
    ,group_concat(distinct crmidn order by crmidn separator "<BR>") as idny 
    FROM t_mail_alerty_zdarzenia_cube  t
    WHERE t.kontrguid=_GUID and rok*12+mc>=year(current_date - interval 7 day)*12+month(current_date - interval 7 day)
    GROUP BY rok,mc,rodzaj;
END IF; 

IF (_MODE = 'CRM_LOG_Footer_CUBE') THEN
        SELECT @tekst1;
END IF; 

IF (_MODE = 'CRM_LOG_WebCRM_CUBE') THEN
    CALL mail_alerty('CRM_LOG_Prepare_CUBE',_GUID,_WHERE_SQL,_Param_A,_Param_B,_Param_C,'6');
    SELECT concat(rok,'-',mc) as rokmc,crodzaj as rodzaj,sum(ilecrm) as ile,round(sum(czas)) as czas
    FROM t_mail_alerty_zdarzenia_cube  t
    WHERE t.kontrguid=_GUID OR _GUID=''
    GROUP BY rokmc,crodzaj;
END IF; 

IF _MODE = 'CRM_LOG_SetSent' THEN
    UPDATE crm_log l, t_mail_alerty_zdarzenia t SET info_mail=NOW()
    WHERE t.commissionguid = _GUID AND t.idnlog=l.idn;
END IF;

IF _MODE = 'CRM_LOG_Clean' THEN
    DROP TABLE IF EXISTS t_mail_alerty_zdarzenia;
    DROP TABLE IF EXISTS t_mail_alerty_kontrah;
    DROP TABLE IF EXISTS t_mail_alerty_zdarzenia_cube;
END IF;    


-- --------------------------------------------------------------------------------------------------------

IF (_MODE = 'HeadMail') THEN
    SELECT IF(ZadanieWorek='W','Zadanie zbiorcze',temat) AS temat
    FROM t_mail_alerty_zdarzenia
    WHERE commissionGUID = _GUID
    ORDER BY kiedy LIMIT 1;
END IF;


IF _MODE = 'MAILER_LIST' THEN
    DROP table if exists xcrm_maillist;
    CREATE TABLE xcrm_maillist
    SELECT DISTINCT c.GUID  
    FROM crm_log cl
    JOIN crm_commisions c ON c.GUID=cl.commissionguid
    LEFT JOIN `crm_usr_links` cul ON cul.`commisionGUID` = c.GUID
    LEFT JOIN kontakty k ON k.osobaGUID=cul.osoba 
    WHERE
        cl.publiczny=1 AND c.status=13 AND c.rodzaj=8 AND -- na razie tylko zgloszenia
        IFNULL(cl.info_mail,'')=''
        AND cl.zmiana_dotyczy in ('TIME_SHEET','ADD_COMMENT','CHANGE_STATUS')
    ORDER BY cl.modify_date DESC 
    ;
    SELECT guid FROM xcrm_maillist;
END IF;

-- wszystkie komentarze publiczne do zadan o statusie do sprawdzenia:
IF _MODE = 'MAILER_LIST_ASTE' THEN
    DROP table if exists xcrm_maillist;
    CREATE TABLE xcrm_maillist
    SELECT 
        DISTINCT c.GUID  
    FROM
    crm_log cl
    JOIN crm_commisions c ON c.GUID=cl.commissionguid
    LEFT JOIN `crm_usr_links` cul ON cul.`commisionGUID` = c.GUID
    LEFT JOIN kontakty k ON k.osobaGUID=cul.osoba 
    WHERE
        cl.publiczny=1 AND c.status=13 AND -- sprawdzenie przez klienta
        IFNULL(cl.info_mail,'')=''
        AND (k.kontrGUID in (select guid from xcrm_maillist_kontrah) OR
        cul.kontrGUID in (select guid from xcrm_maillist_kontrah)) 
        AND cl.zmiana_dotyczy in ('TIME_SHEET','ADD_COMMENT','CHANGE_STATUS')
        
    ORDER BY cl.modify_date DESC 
    ;
    SELECT * FROM xcrm_maillist;
END IF;

IF _MODE = 'MAILER_CHECKIT' THEN
    UPDATE crm_log SET info_mail=NOW()
    WHERE commissionguid IN    (SELECT GUID FROM xcrm_maillist);
    DROP TABLE IF EXISTS xcrm_maillist
    ;
END IF;

        
IF _MODE = 'MAILER_PREPARE' THEN
        SELECT concat(Imiona,' ',Nazwisko) as kto, cl.modify_date as kiedy, _VarStr(cl.jest) as komentarz 
        FROM crm_log cl
        INNER JOIN crm_commisions c ON c.GUID=cl.commissionguid
        LEFT JOIN osoby o on o.GUID=cl.osobaguid
        WHERE c.GUID = _GUID AND cl.publiczny=1 AND 
            c.rodzaj=8 AND -- na razie tylko zadania
            IFNULL(cl.info_mail,'')='' AND cl.zmiana_dotyczy in ('TIME_SHEET','ADD_COMMENT')
        ORDER BY cl.modify_date DESC;
END IF;

IF _MODE = 'MAILER_ADRESY' THEN
    SELECT group_concat(distinct IFNULL(k.adres,'<BRAK ADRESU> int@int.com.pl')) as adresy
    FROM crm_commisions c
    INNER JOIN crm_usr_links cul ON cul.commisionGUID = c.GUID
    INNER JOIN kontakty k ON k.osobaGUID=cul.osoba 
    WHERE c.GUID = _GUID AND k.typ_medium=3 AND IFNULL(k.adres,'') like '%@%' 
            AND cul.poziom>-1 AND k.kontrGUID<>'';
END IF; 

IF _MODE = 'MAILER_ADRESY_ASTE' THEN
    DROP TEMPORARY TABLE IF EXISTS xcrm_firmy ;
    
    CREATE TEMPORARY TABLE xcrm_firmy 
        SELECT DISTINCT o.kontrGUID,cul.poziom
        FROM crm_commisions c
        JOIN crm_usr_links cul ON cul.commisionGUID = c.GUID
        JOIN osoby o ON o.GUID=cul.osoba
        WHERE c.GUID = _GUID AND cul.poziom=4
    ;

    SELECT group_concat(distinct IFNULL(k.adres,'<BRAK ADRESU> int@int.com.pl')) as adresy
    FROM crm_commisions c
    INNER JOIN crm_usr_links cul ON cul.commisionGUID = c.GUID
    INNER JOIN kontakty k ON k.osobaGUID=cul.osoba 
    WHERE c.GUID = _GUID AND k.typ_medium=3 AND IFNULL(k.adres,'') like '%@%' AND
            cul.poziom>-1 AND k.kontrGUID IN (SELECT kontrGUID FROM xcrm_firmy) 
    ;
END IF;     


    
/*
Tabela tygodniowa z zestawieniem czasu pracownikow dla TL
CALL mail_alerty('WEEKTAB','','','','','','');
*/    
IF _MODE = 'WEEKTAB' THEN
    DROP TEMPORARY TABLE IF EXISTS t_weektab;
    
    CREATE TEMPORARY TABLE t_weektab (
    idnuser varchar(38),godz_w_tyg int not null,
    pn double(16,4) not null,wt double(16,4) not null,sr double(16,4) not null,cz double(16,4) not null,pt double(16,4) not null,so double(16,4) not null,nd double(16,4) not null,
    UNIQUE INDEX idnuser(idnuser));

    CALL mail_alerty('WEEKTAB_ADD','slon','','','','','');
    CALL mail_alerty('WEEKTAB_ADD','waliant','','','','','');
    CALL mail_alerty('WEEKTAB_ADD','es','','','','','');
    CALL mail_alerty('WEEKTAB_ADD','apro','','','','','');
    CALL mail_alerty('WEEKTAB_ADD','jmam','','','','','');
    CALL mail_alerty('WEEKTAB_ADD','aga','','','','','60');
    CALL mail_alerty('WEEKTAB_ADD','monikap','','','','','');
    CALL mail_alerty('WEEKTAB_ADD','krasnal','','','','','');
    CALL mail_alerty('WEEKTAB_ADD','jmam','','','','','');
    CALL mail_alerty('WEEKTAB_ADD','andy5','','','','','');
    CALL mail_alerty('WEEKTAB_ADD','zim_tomek','','','','','');
    CALL mail_alerty('WEEKTAB_ADD','krasnal','','','','','');
    CALL mail_alerty('WEEKTAB_ADD','darekb','','','','','');
    CALL mail_alerty('WEEKTAB_ADD','mcz','','','','','50');
    CALL mail_alerty('WEEKTAB_ADD','tiwek','','','','','20');
    CALL mail_alerty('WEEKTAB_ADD','robert','','','','','');
    CALL mail_alerty('WEEKTAB_ADD','michals','','','','','');

    SELECT 
    idnuser,  
    CONCAT(RPAD(_DisplayDurationFormat(Pn),10,' '),'(', LPAD(ROUND(Pn/8*100),3,' '),'%)') AS Pn, 
    CONCAT(RPAD(_DisplayDurationFormat(Wt),10,' '),'(', LPAD(ROUND(Wt/8*100),3,' '),'%)') AS Wt, 
    CONCAT(RPAD(_DisplayDurationFormat(Sr),10,' '),'(', LPAD(ROUND(Sr/8*100),3,' '),'%)') AS Sr, 
    CONCAT(RPAD(_DisplayDurationFormat(Cz),10,' '),'(', LPAD(ROUND(Cz/8*100),3,' '),'%)') AS Cz, 
    CONCAT(RPAD(_DisplayDurationFormat(Pt),10,' '),'(', LPAD(ROUND(Pt/8*100),3,' '),'%)') AS Pt,
    CONCAT(RPAD(_DisplayDurationFormat(So),10,' '),'(', LPAD(ROUND(So/8*100),3,' '),'%)') AS So,
    CONCAT(RPAD(_DisplayDurationFormat(Nd),10,' '),'(', LPAD(ROUND(Nd/8*100),3,' '),'%)') AS Nd,
    CONCAT(RPAD(_DisplayDurationFormat(ROUND(Pn+Wt+Sr+Cz+Pt+So+Nd,2)),10,' '),' ', 
        LPAD(ROUND(((Pn+Wt+Sr+Cz+Pt+So+Nd)/(godz_w_tyg))*100),3,' '),'% (',LPAD(godz_w_tyg,2,' '),' g/t)') AS Suma
    FROM t_weektab WHERE IFNULL(idnuser,'')<>'' ORDER BY idnuser
    ;

END IF;
  
/*
  CALL mail_alerty('WEEKTAB_ADD',osobaguid,'','','','','etat');
  dodaniepozycji dla konkretnego pracownika - etat = procent, jesli pusty, to 100
*/
IF _MODE = 'WEEKTAB_ADD' THEN
    SET @godz_w_tyg=round(IF(_Param_D='',100,_varINT(_Param_D))*8*5/100);
    INSERT IGNORE INTO t_weektab 
    SELECT 
        CONCAT(idnuser,' - ',Nazwisko,' ',Imiona) AS idnuser,@godz_w_tyg,
        SUM(IF(DAYofWEEK(modify_date) = 2, IFNULL(time_sheet,0), 0)) AS Pn,
        SUM(IF(DAYofWEEK(modify_date) = 3, IFNULL(time_sheet,0), 0)) AS Wt,
        SUM(IF(DAYofWEEK(modify_date) = 4, IFNULL(time_sheet,0), 0)) AS Sr,
        SUM(IF(DAYofWEEK(modify_date) = 5, IFNULL(time_sheet,0), 0)) AS Cz,
        SUM(IF(DAYofWEEK(modify_date) = 6, IFNULL(time_sheet,0), 0)) AS Pt,
        SUM(IF(DAYofWEEK(modify_date) = 7, IFNULL(time_sheet,0), 0)) AS So,
        SUM(IF(DAYofWEEK(modify_date) = 1, IFNULL(time_sheet,0), 0)) AS Nd
    FROM crm_log cl
    LEFT JOIN osoby o ON o.guid=cl.osobaguid
--    WHERE LEFT(modify_date,10) >= LEFT(ADDDATE(NOW(), INTERVAL -6 DAY),10)  
    WHERE DATE(modify_date) BETWEEN CURRENT_DATE - INTERVAL 7 DAY AND CURRENT_DATE - INTERVAL 1 DAY    
    AND o.idnuser= _GUID
    ORDER BY modify_date,osobaguid DESC
    ;
    
    INSERT IGNORE INTO t_weektab 
    SELECT CONCAT(idnuser,' - ',Nazwisko,' ',Imiona) AS idnuser,@godz_w_tyg,0,0,0,0,0,0,0
    FROM osoby
    WHERE idnuser= _GUID
    ;
END IF;    
  
-- KONIEC - Tabela tygodniowa z zestawieniem czasu dla TL

/*
CALL mail_alerty('CRM.getIncorrentKeywords','','','','','','');
*/
IF _MODE = 'CRM.getIncorrentKeywords' THEN
  
    DROP TEMPORARY TABLE IF EXISTS t_kluczyki;
    
    CREATE TEMPORARY TABLE t_kluczyki
    SELECT 1 as typ_danych,
         cd.idn, cd.keywords, co.idn AS idn_ojca, co.keywords AS keywords_ojca
    FROM crm_commisions cd
    LEFT JOIN crm_commision_link ccl ON ccl.link_from = cd.GUID
    LEFT JOIN crm_commisions co ON co.GUID = ccl.link_to
    WHERE co.keywords<>cd.keywords and co.rodzaj<>5
    ;
    
    SET @ilepoz = 0;
    SELECT count(*)
    INTO @ilepoz
    FROM crm_commisions cd
    LEFT JOIN crm_commision_link ccl ON ccl.link_from = cd.GUID
    LEFT JOIN crm_commisions co ON co.GUID = ccl.link_to
    WHERE co.keywords<>cd.keywords and co.rodzaj<>5 
    ;
    
    IF @ilepoz > 0 THEN
        INSERT INTO t_kluczyki
        SELECT 2 as typ_danych, 0 as idn, 'RAZEM: ' as keywords, count(*) AS idn_ojca, '' AS keywords_ojca
        FROM crm_commisions cd
        LEFT JOIN crm_commision_link ccl ON ccl.link_from = cd.GUID
        LEFT JOIN crm_commisions co ON co.GUID = ccl.link_to
        WHERE co.keywords<>cd.keywords AND co.rodzaj<>5    
        ;
    END IF;

    SELECT idn, keywords, idn_ojca, keywords_ojca 
    FROM t_kluczyki
    ORDER BY typ_danych DESC, idn_ojca;
    
END IF;

/*
   Tabela z podsumowaniem czasow wysylana do ludzi
   CALL mail_alerty('CRMSUMTIMETASK',_GUIDosoby,'','','','','');
   
   'CRMSUMTIMETASK.GetOsoby' - lista GUID osob do parametrow alerta 
   'CRMSUMTIMETASK.GetMail' - adres maila dla danej osoby
   'CRMSUMTIMETASK.MainTable' - tabela wlasciwa do umieszczenia w BODY dla wlasciwego usera
   'CRMSUMTIMETASK.MailHeader' - naglowek wiadomosci /wzglednie temat maila
*/

IF _MODE = 'CRMSUMTIMETASK.GetOsoby' THEN
    SELECT GUID 
    FROM osoby 
    WHERE idnuser IN ('slon','waliant','zim_tomek','andy5','jmam','es','aga','monikap','apro','krasnal','ania','mcz'/*,'tiwek'*/,'robert','michals');
END IF;

IF _MODE = 'CRMSUMTIMETASK.GetMail' THEN
    SELECT adres 
    FROM kontakty 
    WHERE typ_medium=3 AND osobaguid= _GUID
    ORDER BY kontrGUID DESC
    LIMIT 1;
END IF;

IF _MODE = 'CRMSUMTIMETASK.MailHeader' THEN
    SELECT CONCAT('Zestawienie czasow CRM dla ',imiona,' ',nazwisko) AS tresc FROM osoby WHERE GUID = _GUID;
END IF;
  
IF _MODE = 'CRMSUMTIMETASK.MainTable' THEN
    SELECT MAX(DATE(modify_date)) INTO @MaxData FROM crm_log cl WHERE osobaguid=_GUID AND DATE(modify_date)<CURRENT_DATE;
    SET lc_time_names = 'pl_PL';

    DROP TEMPORARY TABLE IF EXISTS t_listaX;
    CREATE TEMPORARY TABLE t_listaX (
        SELECT 0 AS lp, 
        LEFT(modify_date,16) AS dzien, 
        DATE_FORMAT(LEFT(modify_date,10), '%W') AS dzien_tyg,
        _DisplayDurationFormat(time_sheet) AS godz, 
        CONCAT(ROUND(((time_sheet)/8)*100,2),'%') AS proc,
        c.idn AS crm_id,
        c.nrwewn as nrwewn,
        if(cl.keywords like '%[R]%', 'R','') as rekl,
        CONCAT(IFNULL(k.symbol,'?'),': ',_VarStr(cl.jest)) AS opis 
        FROM crm_log cl
        LEFT JOIN crm_commisions c ON c.GUID=cl.commissionguid
        LEFT JOIN crm_usr_links cul ON cul.commisionGUID = c.GUID AND poziom=4
        LEFT JOIN osoby o ON o.GUID = cul.osoba
        LEFT JOIN kontrah k ON k.GUID=o.kontrGUID
        WHERE 
        osobaguid=_GUID AND 
        zmiana_dotyczy='TIME_SHEET' AND 
        LEFT(modify_date,10) = @MaxData
        GROUP BY modify_date
        ORDER BY dzien DESC
    )
    UNION
    (
        SELECT 1 AS lp, '--- 'AS dzien, '---' AS dzien_tyg, '--' AS godz, '--' AS proc, '----' AS crm_id, '--' as nrwewn, '--' as rekl, '--- PODSUMOWANIE ---' AS opis
    )
    UNION
    (
        SELECT 
        2 AS lp,
        DATE(modify_date) AS dzien, 
        DATE_FORMAT(DATE(modify_date), '%W') AS dzien_tyg,
        _DisplayDurationFormat(SUM(time_sheet)) AS godz,
        -- group_concat(distinct c.idn) AS crm_id,
        CONCAT(ROUND((SUM(time_sheet)/8)*100,2),'%') AS proc,
        ". " AS crm_id,
        ". " AS nrwewn,
        ". " AS rekl,
        ". " AS opis 
        FROM crm_log cl
        JOIN crm_commisions c ON c.GUID=cl.commissionguid
        WHERE 
        osobaguid=_GUID AND time_sheet>0 AND DATE(modify_date)<@MaxData
        GROUP BY dzien 
        ORDER BY dzien DESC
        LIMIT 7
    )
    ;

    SELECT dzien, dzien_tyg, godz, proc, crm_id, nrwewn, rekl, opis FROM t_listaX ORDER BY lp, dzien
    ;
    
END IF;  
-- KONIEC CRMSUMTIMETASK

/*
CALL mail_alerty('MAILER.GetSubjectCRM',_CRMguid,'','','','','');
*/

IF _MODE = 'MAILER.GetSubjectCRM' THEN
    SELECT DISTINCT CONCAT("Wiadomość dot. zad. ",c.idn," - ",c.Temat,"") AS wynik
    FROM crm_commisions c
    WHERE c.GUID = _GUID
    ;
END IF;  

/*
CALL mail_alerty('MAILER.FooterCRM',_CRMguid,'','','','','');
*/


IF _MODE = 'MAILER.FooterCRM' THEN
    SELECT c.idn INTO @IDNOjca 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;
    SELECT DISTINCT 
      CONCAT("<br>Wiadomosc zostala wygenerowana autormatycznie i prosimy na nia nie odpowiadac.<br>",
      CONCAT("<br>Osoba zglaszajaca: <b>",
        (SELECT GROUP_CONCAT(CONCAT(o.Imiona,' ',o.Nazwisko)) AS osoby 
        FROM  crm_usr_links cul, osoby o WHERE o.guid=cul.osoba AND poziom=4 AND cul.commisionGUID= _GUID ),
        "</b><br>"),
        'Link do zdarzenia w WebCRM: <a href=\"http://motlawa.int.com.pl/crm/crmrec.php?idn=',IF(@IDNOjca>10,@IDNOjca,c.idn),'\">',
        'http://motlawa.int.com.pl/crm/crmrec.php?idn=',IF(@IDNOjca>10,@IDNOjca,c.idn),'</a> </BODY></HTML>') AS wynik
    FROM crm_commisions c
    WHERE c.GUID = _GUID
    ;
END IF;   
  
/*
CALL mail_alerty('MAILER.HeaderCRM',_CRMguid,'','','','','');
*/

IF _MODE = 'MAILER.HeaderCRM' THEN
    SELECT c.idn, c.Temat
    INTO @IDNOjca, @TematOjca
    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
    ;

    SET @Dopis = IF(@IDNOjca>10,CONCAT("Dotyczy zgłoszenia:<br> <b>(",@IDNOjca,") \"",@TematOjca,"\"</b>"),'');

    SELECT DISTINCT  
        CONCAT("<HTML><HEAD><META HTTP-EQUIV=\"Content-Type\" CONTENT=\"text/html; charset=UTF-8\">",
        "<style>table,th, td {border: 1px solid black; border-spacing:0; border-collapse:collapse;",
        " font-family: Arial; font-size: 9pt;} body {font-family: Arial; font-size: 9pt;}</style> </HEAD><BODY>",
        "<img src='http://motlawa.int.com.pl/crm/img/crmlogo120.jpg' align=left width=120>","<span style='font-size: 12pt; font-family: Arial'>",
        IF(LENGTH(@Dopis)>1,@Dopis,CONCAT("Dotyczy zadania:<br> <b>(",c.idn,") \"",c.Temat,"\"</b>")),"</span>","<br><br>") AS wynik
    FROM crm_commisions c
    WHERE c.GUID = _GUID
    ;
END IF; 


-- mody dla Polaszka przeniesione do mail_alerty_polaszek
-- mody dla Bysewa (ale chyba nieużywane) przeniesione do mail_alerty_bysewo
/*
alert raportujący o zbliżających się terminach upływu wazności uprawnień i badań
SET @grupa_do="KADRY_ALERT",@param2='4';
_PARAM_A - idn klasy cech
_PARAM_B - guid osoby lub grupy
_PARAM_C - ALL lub guid osoby lub grupy z _PARAM_B (bez sensu)
_PARAM_D - mail_to, mailcc, mailbcc, mail_sub, mail_body, mail_foot, mail_param, mail_param2
 #to
CALL mail_alerty("ALERT_CECHY", "", "", "", @grupa_do, "ALL", "mail_to");
CALL mail_alerty("ALERT_CECHY", "", "", @param2, @grupa_do,"ALL" , "mail_sub");
CALL mail_alerty("ALERT_CECHY", "", "", @param2, @grupa_do, "ALL", "mail_body");
CALL mail_alerty("ALERT_CECHY", "", "", "", @grupa_do, "ALL", "mail_foot");
# param1:
'SELECT "ALL"',
# param2:
CALL mail_alerty("ALERT_CECHY", "", "", "", @grupa_do, "ALL", "mail_param2");
*/

IF _MODE = 'ALERT_CECHY' THEN
    IF _Param_D='mail_to' THEN  -- na razie tylko do grupy
        IF _Param_C='ALL' THEN
            SELECT GROUP_CONCAT(DISTINCT '<',adres,'>') as mail_to 
            FROM kontakty k, grupy_osob g 
            WHERE k.osobaguid=g.osobaguid AND k.typ_medium=3 AND g.grupaguid in (SELECT grupa FROM temp_cechy_grupy WHERE idn_cechy=_VarINT(_Param_A) OR _Param_A = '' );
            -- (_Param_A='' OR OC.klasa_cech=_VarInt(_Param_A))) ;
        ELSE    
            SELECT GROUP_CONCAT(DISTINCT '<',adres,'>')  as mail_to FROM kontakty k WHERE  k.typ_medium=3 AND k.osobaguid=_Param_B ; 
        END IF;    
    END IF;    
    
    IF _Param_D='mailcc' THEN
        SELECT '' ;      
    --    SELECT GROUP_CONCAT('<',adres,'>') FROM kontakty k, grupy_osob g WHERE k.osobaguid=g.osobaguid AND g.grupaguid='ALL' ;
    END IF;    
    
    IF _Param_D='mailbcc' THEN
        SELECT '' ;
    END IF;    
    
    IF _Param_D='mail_sub' THEN
        IF _Param_C='ALL'  AND _Param_A=''  THEN
            SELECT CONCAT('Przypomnienie o mijającym okresie ważności cech na dzień ',CURRENT_DATE, ' (baza: ',DATABASE(),')' )  ;
        END IF;
        
        IF _Param_A<>'' THEN
            SELECT CONCAT('Lista osób ze zbliżającym się terminem ważności: ',nazwa_klasy ) FROM osoby_klasy_cech WHERE idn=_VarINT(_Param_A);
        END IF;
        -- TODO dla pojedynczej osoby
    END IF;    
    
    IF _Param_D='mail_body' THEN
        DROP TEMPORARY TABLE IF EXISTS temp_rpt;
        CREATE TEMPORARY TABLE temp_rpt (
        osobaGUID VARCHAR(38) DEFAULT NULL,
        osoba VARCHAR(70) DEFAULT NULL,
        cecha TEXT DEFAULT NULL,
        dni_przed VARCHAR(30) DEFAULT NULL,
        wazne_do DATE DEFAULT NULL,
        dni_po_terminie INT DEFAULT NULL,
        KEY osobaGUID(osobaGUID)
        ) DEFAULT CHARSET=cp1250 COLLATE=cp1250_general_ci
        ;
        insert into temp_rpt
        SELECT
            O.guid as osobaGUID,
            CONCAT(O.Imie1, ' ',O.Nazwisko) AS Osoba,
            GROUP_CONCAT(OC.opis) AS Cecha,MAX(OC.param1) AS dni_przed,
            MAX(U.datawaznosci) AS wazne_do,
            GREATEST(DATEDIFF(CURRENT_DATE,MAX(U.DataWaznosci)),0) AS dni_po_terminie
        FROM Pracownicy P
        INNER JOIN osoby O ON P.GUID = O.GUID
        INNER JOIN umiejetnosci U ON U.GUID_Pracownika = P.GUID
        INNER JOIN osoby_cechy OC ON U.IdnDetalu = OC.idn AND OC.param1<>'0' 
        WHERE P.CzyObecniePracuje=1 AND
             (_Param_C='ALL' OR P.guid=_Param_C) AND -- czy wszystkie osoby czy wskazane
             (_Param_A='' OR OC.klasa_cech=_VarInt(_Param_A))
        GROUP BY P.guid
        HAVING wazne_do <= CURRENT_DATE + INTERVAL dni_przed DAY -- ile dni przed
        ;

        IF  @session.globals.Owner = 'CRIST' THEN
            CALL fr_pl_zest('UMZLEC_DL_SQD','','','','','',''); -- tworzy tabele tt_um

            insert into temp_rpt
            SELECT
                O.guid as osobaGUID,
                CONCAT(O.Imie1, ' ',O.Nazwisko) AS Osoba,
                GROUP_CONCAT(OC.opis) AS Cecha,MAX(OC.param1) AS dni_przed,
                MAX(U.datawaznosci) AS wazne_do,
                GREATEST(DATEDIFF(CURRENT_DATE,MAX(U.DataWaznosci)),0) AS dni_po_terminie
            FROM tt_um t
            INNER JOIN osoby O ON t.GUID_pracownika = O.GUID
            INNER JOIN umiejetnosci U ON U.GUID_Pracownika = O.GUID
            INNER JOIN osoby_cechy OC ON U.IdnDetalu = OC.idn AND OC.param1<>'0'
            WHERE -- t.GUID_pracownika NOT IN (select osobaGUID from temp_rpt) AND
                (_Param_C='ALL' OR P.guid=_Param_C) AND -- czy wszystkie osoby czy wskazane
                (_Param_A='' OR OC.klasa_cech=_VarInt(_Param_A))
                and t.DataUmowy <= current_date() and ifnull(DataDo,'2100-12-31') >= current_date()
            GROUP BY t.GUID_pracownika
            HAVING wazne_do <= CURRENT_DATE + INTERVAL dni_przed DAY -- ile dni przed
            ;


        END IF;


        IF (select count(*) from temp_rpt) > 0 THEN
            SELECT Osoba, wazne_do AS `Ważna do`, Cecha AS `Cecha`, dni_po_terminie AS  `O ile przeterminowane` FROM temp_rpt;
        ELSE
            SELECT 'Nie ma pracowników, którym w najbliższym czasie kończą się terminy badań.' as Komunikat;
        END IF;
        -- DROP TEMPORARY TABLE IF EXISTS temp_rpt;
    END IF;    

    IF _Param_D='mail_param2' THEN
        
        DROP TABLE IF EXISTS temp_cechy_grupy;
        CREATE TABLE temp_cechy_grupy
        SELECT idn as idn_cechy, "KADRY_ALERT" as grupa FROM  osoby_klasy_cech WHERE  param1 LIKE 'ALERT%';
        insert into temp_cechy_grupy (idn_cechy,grupa) values (3,'BHP_ALERT');

        SELECT idn FROM  osoby_klasy_cech WHERE param1 LIKE 'ALERT%';
    END IF;    
    
    IF _Param_D='mail_param' THEN
        SELECT 'ALL';   -- na razie wszystkie jak ktoś będzie chciał doda się guid osoby
    END IF;          

    IF _Param_D='mail_foot' THEN
        SELECT CONCAT('Wiadomość została wygenerowana automatycznie, prosimy na nią nie odpowiadać. ' )  ;
    END IF;    
     
END IF;  

IF _MODE = 'ALERT_UMOWY' THEN
    IF _Param_D='mail_to' THEN  -- na razie tylko do grupy
        IF _Param_C='ALL' THEN
            SELECT GROUP_CONCAT('<',adres,'>') FROM kontakty k, grupy_osob g WHERE k.osobaguid=g.osobaguid AND k.typ_medium=3 AND g.grupaguid=_Param_B ;
        ELSE    
            SELECT GROUP_CONCAT('<',adres,'>') FROM kontakty  WHERE  k.typ_medium=3 AND osobaguid=_Param_B ; 
        END IF;    
    END IF;    
    
    IF _Param_D='mailcc' THEN
        SELECT '' ;      
    END IF;    
    
    IF _Param_D='mailbcc' THEN
        SELECT '' ;
    END IF;    
    
    IF _Param_D='mail_sub' THEN
        SELECT CONCAT('Przypomnienie o mijającym okresie ważności umów na dzień ',CURRENT_DATE )  ;
    END IF;    
    
    IF _Param_D='mail_body' THEN
        SELECT
            O.Nazwisko, O.Imie1 AS `Imię`,
            P.DataRozpoczAktUmowy AS `Data rozpoczęcia aktualnej umowy`,
            T.TypOkresu AS `Typ umowy`,
            CAST(CASE WHEN P.TypOkresuUmowy=1 THEN P.UmowaDo WHEN P.TypOkresuUmowy=2 THEN 
            "---" WHEN P.TypOkresuUmowy=3 THEN P.OkrProbnyDo WHEN P.TypOkresuUmowy=4 THEN 
            P.ZastepstwoDo ELSE '???' END AS CHAR) AS `Umowa do`
        FROM Pracownicy P 
        INNER JOIN osoby O ON P.GUID = O.GUID 
        LEFT JOIN okresy_umow T ON P.TypOkresuUmowy = T.idn 
        WHERE
            P.CzyObecniePracuje=1 AND P.TypOkresuUmowy<>2 
        GROUP BY P.GUID  
        HAVING `Umowa do` <= CURRENT_DATE + INTERVAL 25 DAY
        ORDER BY nazwisko
        ;
    END IF;    

    IF _Param_D='mail_param2' THEN
        SELECT 1;
    END IF;    
    IF _Param_D='mail_param' THEN
        SELECT 'ALL';   -- na razie wszystkie jak ktoś będzie chciał doda się guid osoby
    END IF;          
    IF _Param_D='mail_foot' THEN
        SELECT CONCAT('Wiadomość została wygenerowana automatycznie, prosimy na nią nie odpowiadać. ' )  ;
    END IF;    

END IF; 

IF _MODE = 'ALERT_MINUSY_NA_DZIEN' THEN
    CALL raport_minusy_na_dzien('PREPARE','0','','','');
    IF _Param_A='NAG' THEN
        CALL _IReg_Get('OPNGINFOPATH', 'Akceptuj niedobor', 0, 'BOOLEAN', @session.HT.IR.Akceptuj_niedobor, FALSE);
        CALL _IReg_Get('OPNGINFOPATH', 'KontrolaStanowNaDzien', 0, 'INTEGER', @session.HT.IR.KontrolaStanowNaDzien, 0);

        SELECT CONCAT( 'Akceptuj niedobór na stanie: ',IF (@session.HT.IR.Akceptuj_niedobor=0,'Niedobory są nieakceptowane','Niedobory są akceptowane')) AS `Stan blokad / Błędy`
        UNION
        SELECT CONCAT( 'Kontrola stanów na dzień : ',IF (@session.HT.IR.KontrolaStanowNaDzien>0,'Włączona','Wyłączona'))
        UNION
        SELECT CONCAT('Liczba błędów do poprawienia: ',COUNT(*))  AS Stan
        FROM temp_stannadzien2 s  
        LEFT JOIN Magazyny m ON s.magazyn=m.idnMagazynu
        INNER JOIN TowrNg t ON t.guid=s.idntowr
        WHERE s.minus=1 AND t.usluga=0 AND pozabilans=0   ;    
    ELSE
        SELECT t.symbol,t.nazwa,s.magazyn,s.datawplywu,s.stan
        FROM temp_stannadzien2 s  
        LEFT JOIN Magazyny m ON s.magazyn=m.idnMagazynu
        INNER JOIN TowrNg t ON t.guid=s.idntowr
        WHERE s.minus=1 AND t.usluga=0 AND pozabilans=0 ORDER BY symbol,magazyn,datawplywu  ;
    END IF;
END IF;  


IF _MODE LIKE 'ALERT_WERSJE%' THEN
     
    SET @session.globals.HT2003.ver= (SELECT Prog_Version FROM `_sys_app_reg` WHERE `ID` LIKE CONCAT('%',_GUID,'%'));
    
    IF _MODE LIKE '%.prepare' THEN 
        DROP TABLE IF EXISTS t_wersje_nieaktualne;
        CALL fr_ht_zest('check.wersje',_GUID,'','',_WHERE_SQL,'','','','','','',@session.globals.HT2003.ver);
    END IF;
         
    IF _MODE LIKE '%.mail_to' THEN 
        SELECT '';--     bez pomyslu kiedys jak alerty przejda do tabeli bedziemy tam pobierac
    END IF;    
    
    IF _MODE LIKE '%.mail_cc' THEN
        SELECT 'int@int.com.pl' ;
    END IF;    
    
    IF _MODE LIKE '%.mail_bcc' THEN
        SELECT '' ;
    END IF;    
    
    IF _MODE LIKE '%.mail_sub' THEN
        SELECT CONCAT('[Owner:',(SELECT `value` FROM _db_globals WHERE `key` LIKE 'Owner'),'|baza:',DATABASE(), '|APP:',
        _GUID,'|ERR_LOG:',COUNT(*), ']',' Logowania nieaktualną wersją aplikacji ',_GUID,' z dnia: ' ,_WHERE_SQL ) FROM  t_wersje_nieaktualne ;
    END IF;    
    
    IF _MODE LIKE '%.mail_head' THEN
        SELECT CONCAT('Aktualna wersja: ',@session.globals.HT2003.ver) AS `Aktualna wersja`;
    END IF;    
    
    IF _MODE LIKE '%.mail_body' THEN
        IF @session.globals.HT2003.ver IS NULL THEN
            SELECT CONCAT('Brak ustalonej wymaganej wersji dla ',_GUID,'. Uzupełnij _sys_app_reg.') AS `Status`;
        ELSEIF (SELECT COUNT(*) FROM t_wersje_nieaktualne) >0 THEN 
            SELECT * FROM t_wersje_nieaktualne;
        ELSE 
            SELECT     CONCAT('Wszystkie logowania z dnia ',_WHERE_SQL,' do aplikacji ',_GUID,' aktualnymi wersjami.') AS `Status`;
        END IF;    
    END IF;
    
    IF _MODE LIKE '%.mail_foot' THEN    
        SELECT CONCAT('Wiadomość została wygenerowana automatycznie, prosimy na nią nie odpowiadać. ' )  ;
    END IF;    
    
    IF _MODE LIKE '%.end' THEN    
        DROP TABLE IF EXISTS t_wersje_nieaktualne;  
    END IF;        
END IF; 

/* Przypisywanie odbiorcow do wczesniej przygotowanych alertow w zmng / opng
CALL  mail_alerty('ALERT_ASSIGN.alert_list','_GUID','',SrcTable(zmng/opng),'','','') - jako  _GUID   , guid rekordu na którym stoję
*/

IF _MODE LIKE  'ALERT_ASSIGN%' THEN
    IF _MODE LIKE  '%alert_list%' THEN
        IF (SELECT COUNT(n.guid) FROM `com_msg_ng` n WHERE `type`='SCHEMA' AND SrcTable=_Param_A) = 0 THEN 
            SELECT 'Brak przygotowanych alertow dla tej kartoteki' AS Msg;
            LEAVE SPROC;
        ELSE 
            DROP TEMPORARY TABLE IF EXISTS `t_mail_alerty`;
            CREATE TEMPORARY TABLE `t_mail_alerty` (
                `GUID` VARCHAR(38) NOT NULL, 
                `Msg_Title` VARCHAR(150) DEFAULT NULL,
                `Msg_Mode` VARCHAR(150) DEFAULT NULL,
                `Msg_Submode` VARCHAR(150) DEFAULT NULL,
                `uzywany` INT(11) DEFAULT 0);

            IF _MODE LIKE '%alert_list_rodzaj%' THEN -- operdoc
                DROP TEMPORARY TABLE IF EXISTS `t_mail_rodzaje`;
                CREATE TEMPORARY TABLE t_mail_rodzaje
                SELECT CONCAT('rodzaj',p.rodzaj,'typ',p.typ) AS GUID,p.rodzaj,p.typ,p.nazwa,p.opis
                 FROM operdoc_typy p ,operdoc r WHERE p.rodzaj=r.rodzaj AND r.valid=1 AND
                IF(_Param_A='zmng',r.grupa IN (7),r.grupa IN (1,2,5));
            END IF;

        

            INSERT IGNORE INTO t_mail_alerty 
            SELECT n.guid,n.Msg_Title,n.Msg_Mode,n.Msg_Submode,0
            FROM `com_msg_ng` n WHERE n.type='SCHEMA' AND n.SrcTable=_Param_A;

        END IF;    
    END IF;    
    IF _MODE LIKE  '%alert_osoby_list' THEN
        SET @SchemaGUID=_PARAM_B;

        SELECT n.guid INTO @ext_guid FROM com_msg_ng n WHERE n.type='ALERT' AND n.SrcGUID=_GUID AND n.Owner=@SchemaGUID;

        IF @ext_guid IS NULL THEN 
            SET @ext_guid=MD5(CONCAT(@SchemaGUID,_GUID));
        END IF;
       
        DROP TEMPORARY TABLE IF EXISTS `t_mail_alerty_osoby`;
        CREATE TEMPORARY TABLE `t_mail_alerty_osoby` (
            `SchemaGUID` VARCHAR(38) NOT NULL, 
            `GUID` VARCHAR(38) NOT NULL, 
            `OsobaGUID` VARCHAR(38) NOT NULL,
            `Osoba` VARCHAR(150) DEFAULT NULL,
            `Adres` VARCHAR(150) DEFAULT NULL,
            `Rola` ENUM('To','Cc','Bcc'),
            `Checked` INT(11) DEFAULT 1,
            PRIMARY KEY (SchemaGUID,Adres)
            );
        -- na poczatku zawezona lista osob - doda sie jakies grupy itd.
        INSERT IGNORE INTO t_mail_alerty_osoby
        SELECT @SchemaGUID,p.GUIDNg,p.OsobaGUID,IFNULL(CONCAT(o.Nazwisko,' ',o.Imie1),'Nie powiązany'),p.Recipient,RecipientRole,1 
        FROM `com_msg_po` p 
        LEFT JOIN com_msg_ng n ON n.guid=p.GUIDNg
        LEFT JOIN osoby o ON o.guid=p.OsobaGUID
        WHERE p.GUIDNg=@ext_guid AND n.SrcGUID=_GUID;

        INSERT IGNORE INTO t_mail_alerty_osoby
        SELECT @SchemaGUID,p.adres,'','',p.adres,'To',0 
        FROM `ta_mailepowiadomien` p ;
        
    END IF;    
    
    IF _MODE LIKE  '%get_osoby' THEN
        DROP TEMPORARY TABLE IF EXISTS `t_mail_alerty_osoby_all`;
        CREATE TEMPORARY TABLE `t_mail_alerty_osoby_all` (
            `SchemaGUID` VARCHAR(38) NOT NULL, 
            `OsobaGUID` VARCHAR(38) NOT NULL, 
            `nazwisko` VARCHAR(38) NOT NULL,
            `imie1` VARCHAR(150) DEFAULT NULL,
            `Adres` VARCHAR(150) DEFAULT NULL,
            `Rola` ENUM('To','Cc','Bcc'),
            `ugrpguid` VARCHAR(38) NOT NULL, 
            `Checked` INT(11) DEFAULT 1,
            PRIMARY KEY (SchemaGUID,Adres)
            );
        
        INSERT IGNORE INTO t_mail_alerty_osoby_all    
        SELECT @SchemaGUID AS SchemaGUID, o.guid AS OsobaGUID,o.nazwisko,o.imie1,ko.adres,'To' AS Rola,o.ugrpguid,0 AS checked
        FROM osoby o 
        INNER JOIN Pracownicy pr ON pr.GUID=o.guid AND pr.CzyObecniePracuje=1
        INNER JOIN kontakty ko ON ko.osobaguid=o.guid AND typ_medium=3 
        LEFT JOIN t_mail_alerty_osoby t ON t.OsobaGUid<>o.guid GROUP BY o.guid,ko.adres;
        INSERT IGNORE INTO t_mail_alerty_osoby_all
        select @schemaGUID as SchemaGUID, '','','',ta.adres,'To' AS Rola,'',0 as checked from ta_mailepowiadomien ta
        WHERE NOT EXISTS (SELECT 1 FROM t_mail_alerty_osoby_all t WHERE t.Adres=ta.adres AND t.SchemaGUID=@SchemaGUID);

    END IF;    
    
    IF _MODE LIKE  '%get_osoby.apply' THEN
        INSERT IGNORE INTO t_mail_alerty_osoby (SchemaGUID,GUID,OsobaGUID,Osoba,Adres,Rola,Checked)
        SELECT t.SchemaGUID,@ext_guid,
        t.OsobaGUID,CONCAT(t.Nazwisko,' ',t.Imie1),t.adres,t.Rola,1 FROM  t_mail_alerty_osoby_all t
        WHERE t.checked=1;
    END IF;    
    
    IF _MODE LIKE  '%add_mail' THEN
        SET @Recipient=    (SELECT _FetchParams_Quick('',_WHERE_SQL,'Recipient',''));
        SET @RecipientRole= (SELECT _FetchParams_Quick('',_WHERE_SQL,'RecipientRole',''));
        SET @SchemaGUID='ALERT_REALIZACJA';
        
        INSERT  INTO t_mail_alerty_osoby (SchemaGUID,GUID,OsobaGUID,Osoba,Adres,Rola,Checked)
        SELECT @SchemaGUID,@ext_guid,'Nie powiazany','Adres nie powiązany',@Recipient,@RecipientRole,1 ;
    END IF;    

    IF _MODE =  'ALERT_ASSIGN.apply' THEN
        INSERT IGNORE INTO com_msg_ng (GUID,`Type`,`Owner`,Msg_Title,SrcGUID,SrcTable,`Status`,Sender)
        SELECT @ext_guid,'ALERT',t.SchemaGUID,n.Msg_Title,_GUID,_Param_A,'Alert',n.Sender 
        FROM t_mail_alerty_osoby t , com_msg_ng n 
        WHERE n.guid=t.SchemaGUID AND  t.checked=1 
        LIMIT 1;

        INSERT IGNORE INTO com_msg_po (GUIDNg,TYPE,Medium_Type,Recipient,RecipientRole,Dispatch_ID,OsobaGUID)
        SELECT @ext_guid,'OUT','mail',t.adres,t.Rola,UUID(),t.OsobaGUID FROM t_mail_alerty_osoby t WHERE checked=1;
        
        DELETE p.* FROM com_msg_po p,t_mail_alerty_osoby t 
        WHERE p.OsobaGUID=t.OsobaGUID AND p.GUIDNg=t.GUID AND t.checked=0;

        CALL  mail_alerty('clear.ALERT_ASSIGN','','','','','','');
    END IF;    
END IF;   -- ALERT_ASSIGN.%

IF _MODE =  'clear.ALERT_ASSIGN' THEN
    SET @SchemaGUID    = NULL;
    SET @ext_guid = NULL;
    DROP TEMPORARY TABLE IF EXISTS `t_mail_alerty`;
    DROP TEMPORARY TABLE IF EXISTS `t_mail_rodzaje`;
    DROP TEMPORARY TABLE IF EXISTS `t_mail_alerty_osoby`;
    DROP TEMPORARY TABLE IF EXISTS `t_mail_alerty_osoby_all`;
END IF;

IF (_MODE = 'ALERT_GET_TO') THEN
    SELECT IFNULL(GROUP_CONCAT(Recipient SEPARATOR ','),'') AS mailTo FROM com_msg_po p ,com_msg_ng n
    WHERE p.medium_type='mail' AND p.guidng=n.guid AND n.srcguid=_GUID AND n.Owner=_Param_A AND n.Type='Alert' AND p.recipientRole='To';
END IF;

IF (_MODE = 'ALERT_GET_CC') THEN
    SELECT IFNULL(GROUP_CONCAT(Recipient SEPARATOR ','),'') AS mailTo FROM com_msg_po p ,com_msg_ng n
    WHERE p.medium_type='mail' AND p.guidng=n.guid AND n.srcguid=_GUID AND n.Owner=_Param_A AND n.Type='Alert' AND p.recipientRole='Cc';
END IF;

IF (_MODE = 'ALERT_GET_BCC') THEN
        SELECT IFNULL(GROUP_CONCAT(Recipient SEPARATOR ','),'') AS mailTo FROM com_msg_po p ,com_msg_ng n
    WHERE p.medium_type='mail' AND p.guidng=n.guid AND n.srcguid=_GUID AND n.Owner=_Param_A AND n.Type='Alert' AND p.recipientRole='Bcc';
END IF;
 

END$$

DELIMITER ;


/* REWIZJE
$Log: mail_alerty.sql,v $
Revision 1.51  2026/01/13 11:52:42  waliant
$SEND$ alert cech rozbudowane dla crist

Revision 1.50  2025/12/01 12:52:27  waliant
$SEND$ poprawka mail_body

Revision 1.49  2025/09/05 13:00:57  apro
WEEKTAB

Revision 1.48  2025/07/25 11:32:05  robert
Zmiany po zmianie execa

Revision 1.47  2025/06/05 08:17:57  robert
Zmiana listy adresat�w dla Crist (lista z importu)

Revision 1.46  2025/03/10 09:38:30  waliant
$SEND$ Warningi

Revision 1.45  2025/01/17 13:19:45  apro
c.d.

Revision 1.44  2025/01/16 10:06:24  apro
michals

Revision 1.43  2024/09/04 08:27:01  robert
Zmiany w "ALERT_CECHY".
Wysylka roznych cech do roznych adresatow - Pliszka i AS

Revision 1.42  2024/08/06 12:06:13  robert
Merge po konflikcie (uporzadkowanie)

Revision 1.41  2024/07/12 09:44:00  apro
reformat; generalne porządki i przegląd
modyfikacji i scalenie proc z Pliszką

Revision 1.40  2024/06/26 12:19:41  waliant
Rozbduowa dziennego zestawienia czasow $SEND$

Revision 1.39  2024/03/15 11:22:36  apro
LF2

Revision 1.38  2024/03/15 11:16:48  apro
LF

Revision 1.37  2024/03/15 11:05:36  apro
cr

Revision 1.36  2024/02/13 06:43:04  apro
błędnie interpretowany klucz w ireg

Revision 1.35  2023/10/13 10:20:23  rafalg
zmiany dla crist

Revision 1.34  2023/10/11 12:57:33  rafalg
alert

Revision 1.33  2023/10/04 14:03:30  rafalg
zmiany

Revision 1.32  2023/10/03 15:16:05  rafalg
mail_alerty dla crist

Revision 1.31  2023/06/27 05:42:26  apro
maile crm int

Revision 1.30  2023/03/01 08:11:52  apro
..

Revision 1.29  2023/03/01 08:07:18  apro
week tab

Revision 1.28  2022/09/14 08:13:13  rafalg
check_wersje
$SEND$

Revision 1.27  2021/12/13 08:07:26  apro
*** empty log message ***

Revision 1.26  2021/11/19 15:10:04  rafalg
alert stany ujemne na dzien

Revision 1.25  2021/11/09 10:40:31  rafalg
alert cechy + alret umowy

Revision 1.24  2021/06/22 05:40:43  apro
mody dla maili z projektów (Pliszka)

Revision 1.23  2019/11/25 07:51:49  apro
powiadomienia bez zdarzeń $

Revision 1.22  2019/11/06 09:20:49  apro
zakres czasu

Revision 1.21  2019/11/06 08:38:39  apro
poprawka cc

Revision 1.20  2019/01/16 12:27:12  apro
Mariola

Revision 1.19  2018/04/17 10:16:13  apro
generalne porządki

*/
