 SET NAMES utf8;

DELIMITER $$

DROP PROCEDURE IF EXISTS `send_mail`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `send_mail`(
    IN _MODE VARCHAR(150),
    IN _IN_MODE VARCHAR(150),
    IN _Key VARCHAR(255),    
    IN _Key2 VARCHAR(255),    
    IN _Param_A VARCHAR(255),
    IN _Param_B VARCHAR(255),
    IN _Param_C VARCHAR(255),
    IN _Params MEDIUMTEXT
) 
S_send_mail: BEGIN 
                    IF _MODE IN ('FetchParams') THEN
                        SET @_Params = _Params;
                        SET 
                        /*  w _PARAM_A może być Sender_UserGUID */
                            @.SM.Sender_Name = NULLIF(_Params2('_FetchParams', '', @_Params, 'Sender_Name', ''), ''),
                            @.SM.Sender_Mail = NULLIF(_Params2('_FetchParams', '', @_Params, 'Sender_Mail', ''), ''),
                            @.SM.Portal_Name = NULLIF(_Params2('_FetchParams', '', @_Params, 'Portal_Name', ''), ''),
                            @.SM.Portal_Url = NULLIF(_Params2('_FetchParams', '', @_Params, 'Portal_Url', ''), ''),
                            @.SM.Portal_Descr = NULLIF(_Params2('_FetchParams', '', @_Params, 'Portal_Descr', ''), ''),
                            @.SM.Param_Welcome = IFNULL(NULLIF(_Params2('_FetchParams', '', @_Params, 'Param_Welcome', ''), ''), 'Witamy'),
                            @.SM.Param_DontAnswer = IFNULL(NULLIF(_Params2('_FetchParams', '', @_Params, 'Param_DontAnswer', ''), ''), 'Wiadomość ta wygenerowana została automatycznie z systemu <I>Motława</I>. Prosimy na nią nie odpowiadać.'),
                            @.SM.Param1 = NULLIF(_Params2('_FetchParams', '', @_Params, 'Param1', ''), ''),
                            @.SM.Param2 = NULLIF(_Params2('_FetchParams', '', @_Params, 'Param2', ''), ''),
                            @.SM.Param3 = NULLIF(_Params2('_FetchParams', '', @_Params, 'Param3', ''), ''),
                            @.SM.Param4 = NULLIF(_Params2('_FetchParams', '', @_Params, 'Param4', ''), ''),
                            
                            @.SM.Msg_Title = NULLIF(_Params2('_FetchParams', '', @_Params, 'Msg_Title', ''), ''),
                        --    Gdy @.SM.Msg_Body_IREG <> '' to @.SM.Msg_Body nie jest brane pod uwage
                        --    Uwaga dla niektórych _IN_MODE @.SM.Msg_Body jest budowane odgórnie z IRega
                            -- @.SM.Msg_Body_IREG = NULLIF(_Params2('_FetchParams', '', @_Params, 'Msg_Body_IREG', ''), ''),
                            @.SM.Ctx_GUID = NULLIF(_Params2('_FetchParams', '', @_Params, 'Ctx_GUID', ''), ''),
                        /*
                            Mamy 3 możliwości
                            -     w Msg_Body mamy cale body - wprost podane lub z Irega dla odp. _IN_MODE 
                            -    w Msg_Body_Template mamy szablon - wprost lub jeśli Msg_Body_Template = 'DEFAULT' to domyslny z IRega i 
                                wtedy param. Msg_Body jest ignorowany i jest 
                                budowany = Msg_Body_Template + wewnątrz usupełniany jest Msg_Body_Content który musi byc podany
                            Szablon Msg_Body_Template ma postać 
                                "<>.....#Content#.../>"  i pochodzi z parametru lub 
                                "DEFAULT" i wtedy jest ładowany z Irega
                            Msg_Body_Content zastępuje frazę #Content# w Msg_Body_Template a finalnie Msg_Body
                        */
                            @.SM.Body_Template = NULLIF(_Params2('_FetchParams', '', @_Params, 'Body_Template', ''), ''),
                            @.SM.Body_Content = NULLIF(_Params2('_FetchParams', '', @_Params, 'Body_Content', ''), ''),
                            @.SM.Body_Link = NULLIF(_Params2('_FetchParams', '', @_Params, 'Body_Link', ''), ''),
							@.SM.Url_Action = NULLIF(_Params2('_FetchParams', '', @_Params, 'Url_Action', ''), ''),
							@.SM.Url_ver = NULLIF(_Params2('_FetchParams', '', @_Params, 'Url_ver', ''), ''),
                            -- @.SM.Msg_Body = NULLIF(_Params2('_FetchParams', '', @_Params, 'Msg_Body', ''), ''),
                        --    ewent. UserGUID (domyślnie zalogowany)    
                        /*  w _PARAM_A może być Sender_UserGUID */
                            @.SM.Sender_UserGUID = IFNULL(NULLIF(_Params2('_FetchParams', '', @_Params, 'Sender_UserGUID', ''), ''), _PARAM_A),
                        --    ewent. wpisany tu np. Jan Prztycki lub pusto wtedy z Sender_UserGUID
                            @.SM.Sender_UserName = NULLIF(_Params2('_FetchParams', '', @_Params, 'Sender_UserName', ''), ''),
                            @.SM.Send_Status = NULLIF(_Params2('_FetchParams', '', @_Params, 'Send_Status', ''), '')
                            
                        ;
                    END IF;
                    
    IF _MODE IN ('Init', 'Init') THEN
    /* *************************************************************************************************************************************************************
        Inicjalizacja
        CALL send_mail('Init', 'User.Powiadomienie', 'MAIL_FROM_Kadry', 'M.WEB.PortaFide.www', '', '', '', @_Params);
        ...
        _Params     Filtr dodatkowy doklejany do sql-ki 
    **************************************************************************************************************************************************************/
        SET
            @.SM.Template.Default = 
'<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
    <meta http-equiv="content-type" content="text/html; charset=UTF-8"/>
    <title></title>
    <style TYPE="text/css">
        .welcome{margin:20px 10px 20px 10px;color:#0079c1;font-size: 2em;font-family: Helvetica, Arial, sans-serif;clear: both;}
        .text-info{line-height:1.5em;margin: 20px 10px 20px 10px;color:#000;font-size:1.2em;font-family: Helvetica, Arial, sans-serif;text-align: left;}
        .text-small-info{line-height:1.0em;margin: 10px 10px 10px 10px;color:#000;font-size:1em;font-family: Helvetica, Arial, sans-serif;text-align: left;}
        .activate-info{line-height:.9em;margin:40px 10px 40px 10px;color:#717074;font-size:1.1em;font-family: Helvetica, Arial, sans-serif;text-align: left;font-weight: 900;}
        .button{margin: 20px 10px 20px 10px; padding:10px 30px 10px 30px;background-color:blue;color:yellow; border-radius: 5px;text-decoration-line: none;}
        a:focus, a:hover { background-color:#DDD;color: #0079c1;}
        .p-button {margin: 40px 10px 40px 10px;}
        a {font-family: Helvetica, Arial, sans-serif;}        
    </style>
</head>
<body lang="pl-PL">
#Content#
<p class="text-small-info"><BR>#DontAnswer#</strong></p>
</body>
</html>'
        ;
        DROP TEMPORARY TABLE IF EXISTS `t_send_mail_inf`;
        CREATE TEMPORARY TABLE `t_send_mail_inf` (
            `IN_MODE` VARCHAR(250),            -- _IN_MODE
            `Owner_Name` VARCHAR(250),    -- Przedsiebiorstwo (z Owner lub z Ireg-a)
            `Owner_Adr` VARCHAR(250),    -- Przedsiebiorstwo (z Owner lub z Ireg-a)
            `Sender_Name` VARCHAR(250),    -- np dział kadr
            `Sender_Mail` VARCHAR(250),    -- np. mail działu kadr
            `Sender_UserGUID` VARCHAR(38),    -- np. zalogowany
            `Sender_UserName` VARCHAR(250),    -- Imie nazwisko
        --    Pozostale parametry    
            `Modul_ID` VARCHAR(38),             -- ID modułu
            `Portal_Name` VARCHAR(250),             -- opcj. dotyczy portalu  #WWW#
            `Portal_Url` VARCHAR(250),             -- ---,,---  #WWW_URL#
            `Portal_Descr` MEDIUMTEXT,             -- ---,,---  #WWW_DESCR#
            -- `Token` VARCHAR(250),                 -- #TOKEN#
            `Param_Welcome` VARCHAR(250) DEFAULT NULL,        -- #Welcome#
            `Param_DontAnswer` VARCHAR(250) DEFAULT NULL,    -- #DontAnswer#      Wiadomość ta wygenerowana została automatycznie z systemu <I>Motława</I>. Prosimy na nią nie odpowiadać.
            `Param1` VARCHAR(250) DEFAULT NULL,                -- #Param1#
            `Param2` VARCHAR(250) DEFAULT NULL,                -- #Param2#
            `Param3` VARCHAR(250) DEFAULT NULL,                -- #Param3#
            `Param4` VARCHAR(250) DEFAULT NULL,                -- #Param4#
            `Body_Default_Template` LONGTEXT
            -- PRIMARY KEY (`OsobaGUID`)
        ) -- ENGINE=MYISAM DEFAULT CHARSET=UTF8
        ;
        /*  w _PARAM_A może być Sender_UserGUID */
        CALL send_mail('FetchParams', _IN_MODE, _Key, _Key2, _PARAM_A, _PARAM_B, _PARAM_C, _Params);
        CALL _Owner_KontrGUID_Tools('GET.Data.To.Var', '', '', '', '');
        CALL com_mailer('SET.Sender.MAIL', '', _Key, '', '', '');    -- ustawia zmienną @.Sender.MAIL jako np. Firmowy/MAIL_FROM_faktury/MAIL_FROM_zamowienia/MAIL_FROM_Place/MAIL_FROM_rozrach/ 
        SET @_Modul_ID = _Key2;
        TRUNCATE `t_send_mail_inf`;
        INSERT IGNORE INTO `t_send_mail_inf`
        SET
            IN_MODE = _IN_MODE,            -- _IN_MODE
            Owner_Name =
                REPLACE(REPLACE(@session.globals.Owner.Name, 
                    'ograniczoną odpowiedzialnością', 'o.o.'), 
                    'OGRANICZONĄ ODPOWIEDZIALNOŚCIĄ', 'O.O.'),
            Owner_Adr = @session.globals.Owner.Addr,    -- Przedsiebiorstwo adres  (z Owner lub z Ireg-a)
            Sender_Name = @.SM.Sender_Name,
            Sender_Mail = IFNULL(@.SM.Sender_Mail, @.Sender.MAIL),
            Sender_UserGUID = IFNULL(@.SM.Sender_UserGUID, ''),
            Sender_UserName = IFNULL(@.SM.Sender_UserName, ''),    -- Imie nazwisko
            Modul_ID = @_Modul_ID,                            -- ID modułu    
            -- Portal_Name = IFNULL(c.Caption, '?'),         -- opcj. dotyczy portalu  #WWW#
            -- Portal_Url = IFNULL(c.Prog_HREF, ''),         -- ---,,---  #WWW_URL#
            -- Portal_Descr = IFNULL(c.Descr, ''),         -- ---,,---  #WWW_DESCR#
            Param_Welcome = @.SM.Param_Welcome,            -- #Welcome#
            Param_DontAnswer = @.SM.Param_DontAnswer,    -- #DontAnswer#      Wiadomość ta wygenerowana została automatycznie z systemu <I>Motława</I>. Prosimy na nią nie odpowiadać.
            Param1 = @.SM.Param1,                        -- #Param1#
            Param2 = @.SM.Param2,                        -- #Param2#
            Param3 = @.SM.Param3,                        -- #Param3#
            Param4 = @.SM.Param4,                        -- #Param4#
            Body_Default_Template = @.SM.Template.Default
        ;
		/*	było ale zostawiam tylko cz. dot. Ownera a cz. dot. portalów.apikacji przenoszę do Prepare
        UPDATE `t_send_mail_inf` ti
		LEFT JOIN `owner_info` oi ON TRUE
		LEFT JOIN `_sys_app_reg` a ON a.ID = ti.Modul_ID -- AND a.Prog_HREF
		LEFT JOIN `sys_core` sc ON sc.GUID = ti.Modul_ID -- AND a.Prog_HREF
        SET
            ti.Owner_Name = IFNULL(oi.oName, ti.Owner_Name),
            ti.Owner_Adr = IFNULL(oi.oAddr, ti.Owner_Adr),
            ti.Portal_Name = IFNULL(a.Caption, IFNULL(sc.Caption, '')), 		-- opcj. dotyczy portalu  #WWW#
            ti.Portal_Url = IFNULL(a.Prog_HREF, IFNULL(sc.Prog_HREF, '')), 	-- ---,,---  #WWW_URL#
            ti.Portal_Descr = IFNULL(a.Descr, IFNULL(sc.Descr, '')) 			-- ---,,---  #WWW_DESCR#
        ;
    --    Gdy nazwa i opis portalu jest taki sam to usuń    
        UPDATE `t_send_mail_inf` ti        
        SET
            ti.Portal_Descr = ''
        WHERE
            ti.Portal_Descr <=> ti.Portal_Name
        ;
		*/
        UPDATE `t_send_mail_inf` ti
		LEFT JOIN `owner_info` oi ON TRUE
        SET
            ti.Owner_Name = IFNULL(oi.oName, ti.Owner_Name),
            ti.Owner_Adr = IFNULL(oi.oAddr, ti.Owner_Adr)
        ;
        DROP TEMPORARY TABLE IF EXISTS `t_send_mail_templates`;
        CREATE TEMPORARY TABLE `t_send_mail_templates` (
            `IN_MODE` VARCHAR(250),            -- _IN_MODE
            `Body_Template` LONGTEXT,
			PRIMARY KEY IN_MODE(IN_MODE)
        ) ENGINE=MYISAM DEFAULT CHARSET=UTF8
        ;
        SET @.Create = ROW_COUNT(); -- SELECT @.Create;
	--	1.	najpierw z `_txt_templ`
        INSERT IGNORE INTO `t_send_mail_templates`
        (IN_MODE, Body_Template)
        SELECT
            IF(tt.ID = 'ua.Mail.aktywujacy', 'Mail_aktywujacy', IF(tt.ID = 'ua.Mail.zapomnialem.hasla', 'Mail_zapomnialem_hasla', '')) AS IN_MODE,
            tt.Txt AS Body_Template
		FROM `_txt_templ` tt
        WHERE 
            tt.ID IN ('ua.Mail.aktywujacy', 'ua.Mail.zapomnialem.hasla')
        ;
	--	2.	teraz po staremu z IReg-a
        INSERT IGNORE INTO `t_send_mail_templates`
        (IN_MODE, Body_Template)
        SELECT
            IF(IdKey = 'Mail aktywujacy', 'Mail_aktywujacy', IF(IdKey = 'Mail zapomnialem hasla', 'Mail_zapomnialem_hasla', '')) AS IN_MODE,
            i.ValueX AS Body_Template
        FROM `IReg` i 
        WHERE 
            IdKey IN ('Mail aktywujacy', 'Mail zapomnialem hasla')
        ;
        
        -- SELECT * FROM `t_send_mail_inf` ti;
        DROP TEMPORARY TABLE IF EXISTS `t_send_mail`;
        CREATE TEMPORARY TABLE `t_send_mail` (
            `SendGUID` VARCHAR(38) DEFAULT NULL,                -- GUID w tabeli `com_msg_ng`
            `Send_Mode` VARCHAR(250),            -- _IN_MODE - okresla typ kolejnej pozycji (gdy rekordów > 1)
            `Recip_GUID` VARCHAR(38),            -- User/Osoba w danym kontekscie
            `Recip_NazwiskoImie` VARCHAR(250),            -- Nazwisko imie usera #N#
            `Recip_ImieNazwisko` VARCHAR(250),            -- Nazwisko imie usera #ImNa#
            `Recip_Login` VARCHAR(200) DEFAULT NULL,    -- #LOGIN#
            `Recip_Ad_mail` VARCHAR(200) DEFAULT NULL,    -- domyslnie to bedzie adres mailowy do wysylki

            `Ctx_GUID` VARCHAR(38),                -- User/Osoba/Klient w danym kontekscie np. osoba ktora nie ma ust. szefa (nie recipient!!)
            `Ctx_Name` VARCHAR(250),            -- Nazwisko imie usera #CtxN#
            `Ctx_Name_Short` VARCHAR(250),        -- Nazwisko imie usera #CtxNS#

            
            `Msg_Title` VARCHAR(250),
            `Msg_Body` LONGTEXT,
            `Msg_Body_Link` VARCHAR(250),
            `Msg_Body_Template` LONGTEXT,                -- 
            `Msg_Body_Content` LONGTEXT,                 -- #Content#
            `Token` VARCHAR(250), 				-- #TOKEN#
			`Url_Action` VARCHAR(150),				-- #ACT=#
			`Url_ver` VARCHAR(50), 				-- #URL.ver#
        --    Pozostale parametry    
            `Param_Welcome` VARCHAR(250) DEFAULT NULL,        -- #Welcome#
            `Param_DontAnswer` VARCHAR(250) DEFAULT NULL,    -- #DontAnswer#      Wiadomość ta wygenerowana została automatycznie z systemu <I>Motława</I>. Prosimy na nią nie odpowiadać.
            `Param1` VARCHAR(250) DEFAULT NULL,                -- #Param1#
            `Param2` VARCHAR(250) DEFAULT NULL,                -- #Param2#
            `Param3` VARCHAR(250) DEFAULT NULL,                -- #Param3#
            `Param4` VARCHAR(250) DEFAULT NULL,                -- #Param4#
            `Send_Status` VARCHAR(150) DEFAULT NULL,        -- Status umieszczany w polu Status tabeli `com_msg_ng`
            PRIMARY KEY `Send_Mode`(`Send_Mode`, `Recip_GUID`),
            UNIQUE KEY `SendGUID`(`SendGUID`)
        ) DEFAULT CHARSET=UTF8
        ;    
        DROP TEMPORARY TABLE IF EXISTS `t_send_mail_Recip`;
        CREATE TEMPORARY TABLE `t_send_mail_Recip` (
            `SendGUID` VARCHAR(38) DEFAULT NULL,                -- GUID w tabeli `com_msg_ng`
            `Recipient` VARCHAR(250) DEFAULT NULL,                -- GUID w tabeli `com_msg_ng`
            `RecipientRole` enum('To','Cc','Bcc') DEFAULT 'To',    -- typ adresu
            PRIMARY KEY (SendGUID, Recipient)
        ) DEFAULT CHARSET=UTF8
        ;    
        DROP TEMPORARY TABLE IF EXISTS `t_send_mail_Recipient`;
        CREATE TEMPORARY TABLE `t_send_mail_Recipient` (
            `Recipient` VARCHAR(250) DEFAULT NULL,                -- GUID w tabeli `com_msg_ng`
            `RecipientRole` enum('To','Cc','Bcc') DEFAULT 'To'    -- typ adresu
        ) DEFAULT CHARSET=UTF8
        ;    
    END IF;

    IF _MODE LIKE 'Add%' THEN
    /* *************************************************************************************************************************************************************
        Inicjalizacja
        CALL send_mail('Add', 'User.Powiadomienie', 'User', 'M.WEB.PortaFide.www', '', '', '', @_Params);
        _Key    Recip_GUID
        _Params     
    **************************************************************************************************************************************************************/
        CALL send_mail('FetchParams', _IN_MODE, _Key, _Key2, _PARAM_A, _PARAM_B, _PARAM_C, _Params);
        INSERT IGNORE INTO `t_send_mail`
        SET
            SendGUID = _Get_ID_xxxxx('SHORT', 38, 'send_mail.', '', '', NULL, NULL, NULL),
            Send_Mode = _IN_MODE,            -- Send_Mode
            Recip_GUID = _Key,
            Ctx_GUID = @.SM.Ctx_GUID,
            Msg_Title = @.SM.Msg_Title,
            Msg_Body_Template = @.SM.Body_Template,
            Msg_Body_Content = @.SM.Body_Content,
            Msg_Body_Link = @.SM.Body_Link,
			Url_Action = @.SM.Url_Action,
            Url_ver = @.SM.Url_ver,
            Param_Welcome = @.SM.Param_Welcome,            -- #Welcome#
            Param_DontAnswer = @.SM.Param_DontAnswer,    -- #DontAnswer#      Wiadomość ta wygenerowana została automatycznie z systemu <I>Motława</I>. Prosimy na nią nie odpowiadać.
            Param1 = @.SM.Param1,                        -- #Param1#
            Param2 = @.SM.Param2,                        -- #Param2#
            Param3 = @.SM.Param3,                        -- #Param3#
            Param4 = @.SM.Param4,                        -- #Param4#
            Send_Status = @.SM.Send_Status
        ;
    /*
        Szablon Msg_Body_Template ma postać 
            "<>.....#Content#.../>"  i pochodzi z parametru lub 
            "DEFAULT" i wtedy jest ładowany z Irega
        Msg_Body_Content zastępuje frazę #Content# w Msg_Body_Template a finalnie Msg_Body
        
        W zmiennych mamy dla roznych modow domyslne szablony
            @.SM.Template.Mail_aktywujacy
            @.SM.Template.Mail_zapomnialem_hasla
            @.SM.Template.Default
            ....
    */
    
    END IF;
    
    IF _MODE LIKE 'Recipient.Add%' THEN
    /* *************************************************************************************************************************************************************
        Dodawanie np BCC, CC etc
        CALL send_mail('Recipient.Add', '', 'jmam@tralala.pl', 'BCC', '', '', '', @_Params);
    **************************************************************************************************************************************************************/
        INSERT IGNORE INTO `t_send_mail_Recipient`
        SET 
            Recipient = _Key, 
            RecipientRole = _Key2
        ;
        
    END IF;
    
    IF _MODE LIKE 'Prepare%' THEN
    /* *************************************************************************************************************************************************************
        Inicjalizacja
        CALL send_mail('Prepare', '', '', '', '', '', '', @_Params);
    **************************************************************************************************************************************************************/
	--	dane aplikacji
	--	było w Init ale zostawiłem tylko cz. dot. Ownera a cz. dot. portalów.apikacji jest tutaj...
        UPDATE `t_send_mail_inf` ti
		LEFT JOIN `_sys_app_reg` a ON a.ID = ti.Modul_ID -- AND a.Prog_HREF
		LEFT JOIN `sys_core` sc ON sc.GUID = ti.Modul_ID -- AND a.Prog_HREF
        SET
            ti.Portal_Name = IFNULL(a.Caption, IFNULL(sc.Caption, '')), 		-- opcj. dotyczy portalu  #WWW#
            ti.Portal_Url = IFNULL(a.Prog_HREF, IFNULL(sc.Prog_HREF, '')), 	-- ---,,---  #WWW_URL#
            ti.Portal_Descr = IFNULL(a.Descr, IFNULL(sc.Descr, '')) 			-- ---,,---  #WWW_DESCR#
        ;
    --    Gdy nazwa i opis portalu jest taki sam to usuń    
        UPDATE `t_send_mail_inf` ti        
        SET
            ti.Portal_Descr = ''
        WHERE
            ti.Portal_Descr <=> ti.Portal_Name
        ;
    --	Usupelnij adresy z konta
        /*
        było
        UPDATE `t_send_mail` tm, `osoby` o, `_user_accounts` ua
        SET
            tm.Recip_ImieNazwisko = CONCAT_WS(' ', o.Imie1, o.Nazwisko),
            tm.Recip_NazwiskoImie = CONCAT_WS(' ', o.Nazwisko, o.Imiona),
            tm.Recip_Login = ua.Login,
            tm.Recip_Ad_mail = IFNULL(NULLIF(tm.Recip_Ad_mail, ''), ua.Ad_mail),
            
        WHERE
            o.GUID = tm.Recip_GUID
            AND ua.OsobaGUID = o.GUID
        ;
        jest ...
        */
        UPDATE `t_send_mail` tm
        INNER JOIN `osoby` o ON o.GUID = tm.Recip_GUID
        LEFT JOIN `_user_accounts` ua ON ua.OsobaGUID = o.GUID
        SET
            tm.Recip_ImieNazwisko = CONCAT_WS(' ', o.Imie1, o.Nazwisko),
            tm.Recip_NazwiskoImie = CONCAT_WS(' ', o.Nazwisko, o.Imiona),
            tm.Recip_Login = ua.Login,
        --    email - jeśli jest uzytkownikiem to z pola Ad_mail
            tm.Recip_Ad_mail = IFNULL(NULLIF(tm.Recip_Ad_mail, ''), ua.Ad_mail),
        --    Send_Status jeśli jest uzytkownikiem to spr. czy nie ma wł. aktywacji bo jeśli nie wtedy gdy w Send_Status = 'HOLD_FOR_ACTIVATION' daj 'NEW'
            tm.Send_Status = 
                IF(IFNULL(tm.Send_Status, '') = 'HOLD_FOR_ACTIVATION',
                    IF(IFNULL(ua.ExtFlags, '') LIKE '%<SetUp.New>%',
                        tm.Send_Status,
                        'NEW'
                    ),
                    IFNULL(tm.Send_Status, 'NEW')
                )
        WHERE
            TRUE
        ;
	/*	rozwojowo	
	--	ewent. prepare Msg_Body_Link
        UPDATE `t_send_mail` tm
		SET
			tm.Msg_Body_Link = 
				CONCAT_WS('&', 
					tm.Msg_Body_Link,
					IF(tm.Msg_Body_Link LIKE '%&klient_id=%', NULL, CONCAT('klient_id=', @session.globals.Owner)),
					IF(tm.Msg_Body_Link LIKE '%&db_name=%', NULL, CONCAT('db_name=', SCHEMA())),
					IF(tm.Msg_Body_Link LIKE '%&ver=%', NULL, 'ver=2.0')
				)
        WHERE
            TRUE
        ;
        */
    --    Usupelnij adresy gdy ich brak    
        UPDATE `t_send_mail` tm 
        SET
            tm.Recip_Ad_mail = 
                    IFNULL(
                        (
                        SELECT 
                            k.Adres 
                        FROM `kontakty` k 
                        WHERE 
                            k.osobaGUID = tm.Recip_GUID
                            AND k.typ_medium = 3 
                            AND IFNULL(k.KontrGUID, '') != ''                 --    KontrGUID !!!
                        ORDER BY k.newsletter DESC, k.priorytet DESC 
                        LIMIT 1
                        ),
                        IFNULL(
                            (
                            SELECT
                                k.Adres
                            FROM `kontakty` k 
                            WHERE
                                k.osobaGUID = tm.Recip_GUID
                                AND k.typ_medium = 3 
                            ORDER BY k.newsletter DESC, k.priorytet DESC 
                            LIMIT 1
                            ),
                            ''
                        )
                    )
        WHERE
            IFNULL(tm.Recip_Ad_mail, '')  = ''
        ;
    --    Dla grup dodaj maile do `t_send_mail_Recip` które potem powedruja do `com_msg_po`
		INSERT IGNORE INTO `t_send_mail_Recip`
		(SendGUID, Recipient, RecipientRole)
		SELECT
			-- r.*
			r.SendGUID, r.Recipient, 'To' AS RecipientRole
		FROM (
			SELECT 
				tm.SendGUID,
				CONCAT_WS(' ', o.Nazwisko, o.Imie1),
				ua.Ad_mail AS Recipient
				-- ,tm.* 
			FROM `t_send_mail` tm
			INNER JOIN `osoby` g USE KEY(GUID) ON g.GUID = tm.Recip_GUID AND g.Rodzaj = 0
			INNER JOIN `grupy_osob` go USE KEY(osoba_grupa) ON go.GrupaGUID = g.GUID AND go.GrupaGUID != go.OsobaGUID
			INNER JOIN `osoby` o ON o.GUID = go.OsobaGUID
			LEFT JOIN `_user_accounts` ua ON ua.OsobaGUID = o.GUID
			WHERE
				IFNULL(tm.Recip_Ad_mail, '')  = ''
		) r
		WHERE
			TRUE
			AND IFNULL(r.Recipient, '') <> ''
        ;
    --    Uzupełnij dane firmy gdy konieczne
        UPDATE `t_send_mail` tm 
        LEFT JOIN `osoby` o ON o.GUID = tm.Ctx_GUID
        LEFT JOIN `kontrah` k ON k.GUID = tm.Ctx_GUID
        SET
            tm.Ctx_Name = IFNULL(NULLIF(tm.Ctx_Name, ''), IFNULL(NULLIF(CONCAT_WS(' ', o.Imie1, o.Nazwisko), ''), k.Nazwa)),
            tm.Ctx_Name_Short = IFNULL(NULLIF(tm.Ctx_Name_Short, ''), IFNULL(NULLIF(o.IdnUser, ''), k.Symbol))
        ;
    --    Uzupełnij dane wysyłającego (imie i nazwisko)
        UPDATE `t_send_mail_inf` tmi
        INNER JOIN `osoby` o ON o.GUID = IFNULL(NULLIF(tmi.Sender_UserGUID, ''), @SYSTEM.Client.Session.GUID)
        -- LEFT JOIN `_user_accounts` ua ON k.GUID = tm.Ctx_GUID
        SET
            tmi.Sender_UserName = TRIM(CONCAT_WS(' ', NULLIF(o.Imie1, ''), NULLIF(o.Nazwisko, '')))
        WHERE
            IFNULL(tmi.Sender_UserName, '') = ''
        ;
        UPDATE `t_send_mail` tm
        INNER JOIN `t_send_mail_inf` ti
		LEFT JOIN `_txt_templ` tt ON tt.ID = ti.IN_MODE
        LEFT JOIN `t_send_mail_templates` templ ON templ.IN_MODE = ti.IN_MODE
        SET
            tm.SendGUID = IFNULL(NULLIF(tm.SendGUID, ''), _Get_ID_xxxxx('', 38, 'send:', tm.SEND_MODE, Recip_GUID, NOW(), NULL, NULL)),
            tm.Msg_Body_Template = 
				IFNULL(tt.txt,
					IFNULL(templ.Body_Template,
						IFNULL(
							IF(tm.Msg_Body_Template = 'DEFAULT', 
								ti.Body_Default_Template, -- @.SM.Template.Default,
								tm.Msg_Body_Template
							),
							'#Content#'
						)
					)
                )
        ;                
        
        
        SET @.Body.Template.XXX = REPLACE(REPLACE(REPLACE(@.Body.Pre := (SELECT Msg_Body_Template FROM `t_send_mail` LIMIT 1), '\n', ''), '\r', ''), CHAR(9), '');
        UPDATE `t_send_mail` tm
        SET
            tm.Msg_Body =
                REPLACE(tm.Msg_Body_Template, '#Content#', IFNULL(tm.Msg_Body_Content, ''))
        ;
        UPDATE `t_send_mail` tm
        INNER JOIN `t_send_mail_inf` ti
        SET
			tm.Msg_Body = 
				REPLACE(tm.Msg_Body, 
					'<p class="p-button"> <a class="button" href="#WWW_URL#" target="_blank">#WWW#</a> </p></body></html>', '')
		WHERE
			ti.Modul_ID = 'M.SYS.MOTLAWA'
		;
		
        UPDATE `t_send_mail` tm, `t_send_mail_inf` ti, `owner_info` oi
        SET
            tm.Msg_Title =
            REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(tm.Msg_Title, 
                '#ImNa#', IFNULL(tm.Recip_ImieNazwisko, '')), 
                '#N#', IFNULL(tm.Recip_NazwiskoImie, '')), 
                '#CtxN#', IFNULL(tm.Ctx_Name, '')), 
                '#CtxNS#', IFNULL(tm.Ctx_Name_Short, '')), 
                '#URL:LINK#', IFNULL(tm.Msg_Body_Link, '')), 
                '#TOKEN#', IFNULL(tm.Token, '')),
                '#LOGIN#', IFNULL(tm.Recip_Login, '')),
                '#WWW#', IFNULL(ti.Portal_Name, '')),
                '#WWW_URL#', IFNULL(ti.Portal_Url, '')),
                '#WWW_DESCR#', IFNULL(ti.Portal_Descr, '')),
                '#OwnerID#', IFNULL(@session.globals.Owner, '')),
                '#OWNER_ADR#', IFNULL(ti.Owner_Adr, '')),
                '#OWNER#', IFNULL(ti.Owner_Name, '')),
                '#SENDER#', IFNULL(ti.Sender_Name, '')),
                '#SENDER_Name#', IFNULL(ti.Sender_UserName, '')),
                '#Welcome#', IFNULL(IFNULL(tm.Param_Welcome, ti.Param_Welcome), '')),
                '#DontAnswer#', IFNULL(IFNULL(tm.Param_DontAnswer, ti.Param_DontAnswer), '')),
                '#ACT=#', IFNULL(tm.Url_Action, '')),
                '#URL.ver#', IFNULL(tm.Url_ver, '')),
                '#URL.db#', SCHEMA()),
                '#Param1#', IFNULL(IFNULL(tm.Param1, ti.Param1), '')),
                '#Param2#', IFNULL(IFNULL(tm.Param2, ti.Param2), '')),
                '#Param3#', IFNULL(IFNULL(tm.Param3, ti.Param3), '')),
                '#Param4#', IFNULL(IFNULL(tm.Param4, ti.Param4), ''))
            ,    
            tm.Msg_Body =
			REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(tm.Msg_Body, 
                '#ImNa#', IFNULL(tm.Recip_ImieNazwisko, '')), 
                '#N#', IFNULL(tm.Recip_NazwiskoImie, '')), 
                '#CtxN#', IFNULL(tm.Ctx_Name, '')), 
                '#CtxNS#', IFNULL(tm.Ctx_Name_Short, '')), 
                '#URL:LINK#', IFNULL(tm.Msg_Body_Link, '')), 
                '#TOKEN#', IFNULL(tm.Token, '')),
                '#LOGIN#', IFNULL(tm.Recip_Login, '')),
                '#WWW#', IFNULL(ti.Portal_Name, '')),
                '#WWW_URL#', IFNULL(ti.Portal_Url, '')),
                '#WWW_DESCR#', IFNULL(ti.Portal_Descr, '')),
                '#OwnerID#', IFNULL(@session.globals.Owner, '')),
                '#OWNER_ADR#', IFNULL(ti.Owner_Adr, '')),
                '#OWNER#', IFNULL(ti.Owner_Name, '')),
                '#SENDER#', IFNULL(ti.Sender_Name, '')),
                '#SENDER_Name#', IFNULL(ti.Sender_UserName, '')),
                '#Welcome#', IFNULL(IFNULL(tm.Param_Welcome, ti.Param_Welcome), '')),
                '#DontAnswer#', IFNULL(IFNULL(tm.Param_DontAnswer, ti.Param_DontAnswer), '')),
                '#ACT=#', IFNULL(tm.Url_Action, '')),
                '#URL.ver#', IFNULL(tm.Url_ver, '')),
                '#URL.db#', SCHEMA()),
                '#Param1#', IFNULL(IFNULL(tm.Param1, ti.Param1), '')),
                '#Param2#', IFNULL(IFNULL(tm.Param2, ti.Param2), '')),
                '#Param3#', IFNULL(IFNULL(tm.Param3, ti.Param3), '')),
                '#Param4#', IFNULL(IFNULL(tm.Param4, ti.Param4), ''))
                        
        ;
        IF NOT IFNULL(@session.Delfinek, FALSE) THEN
            DROP TEMPORARY TABLE IF EXISTS `t_send_mail_templates`;
        END IF;
    END IF;
        
    IF _MODE LIKE 'Send%' THEN
    /* *************************************************************************************************************************************************************
        Wysyłka
        CALL send_mail('Send', '', '', '', '', '', '', @_Params);
    **************************************************************************************************************************************************************/
        CALL send_mail('Prepare', '', '', '', '', '', '', '');
        INSERT IGNORE INTO `com_msg_ng`
        (GUID, CreateAT, TYPE, OWNER, Msg_Title, Msg_Body, SrcGUID, SrcTable, STATUS, Msg_Mode, Notification_Start, Notification_Until, Sender)
        SELECT
            tm.SendGUID AS GUID,
            NOW() AS CreateAT,
            'OUT' AS `Type`,
            tm.Recip_GUID AS `Owner`,
            tm.Msg_Title AS Msg_Title, 
            REPLACE(REPLACE(REPLACE(tm.Msg_Body, '\n', ''), '\r', ''), '\t', '') AS Msg_Body,
            tm.Recip_GUID AS SrcGUID, 
            '_user_accounts' AS SrcTable, 
            -- 'NEW' AS Status, 
            IFNULL(tm.Send_Status, 'NEW') AS Status,  -- zazwyczaj NULL albo np. 'HOLD_FOR_ACTIVATION'  
            ti.IN_MODE AS Msg_Mode,
            NOW() AS Notification_Start,
            DATE_ADD(NOW(), INTERVAL 1 DAY) AS Notification_Until,
            -- było @.mail_from AS Sender
            ti.Sender_Mail AS Sender
        FROM `t_send_mail` tm
        INNER JOIN `t_send_mail_inf` ti ON TRUE
        LEFT JOIN `_user_accounts` ua ON ua.OsobaGUID = tm.Recip_GUID
        WHERE
            TRUE
        ;        
-- update `t_send_mail` set Recip_Ad_mail = 'jmam68@gmail.com';        
        INSERT IGNORE INTO `com_msg_po`
        (GuidNg, TYPE, Medium_Type, Recipient, STATUS, Dispatch_ID)
        SELECT
            tm.SendGUID AS GuidNg,
            'OUT' AS `Type`,
            'mail' AS Medium_Type,
            tm.Recip_Ad_mail AS Recipient,
            'NEW' AS `STATUS`,
            _Get_ID_xxxxx('', 38, 'Disp:', tm.Send_Mode, ':', _VarStr(NOW()), tm.Recip_GUID, NULL) AS Dispatch_ID
        FROM `t_send_mail` tm -- , `t_send_mail_inf` ti
        WHERE
            IFNULL(tm.Recip_Ad_mail, '') <> ''
        ;        
        
    --  wstaw z `t_send_mail_Recip`
        INSERT IGNORE INTO `com_msg_po`
        (GuidNg, TYPE, Medium_Type, Recipient, RecipientRole, STATUS, Dispatch_ID)
        SELECT
            tm.SendGUID AS GuidNg,
            'OUT' AS `Type`,
            'mail' AS Medium_Type,
            tmr.Recipient,
            tmr.RecipientRole,
            'NEW' AS `STATUS`,
            _Get_ID_xxxxx('', 38, 'Disp:', tm.Send_Mode, ':', _VarStr(NOW()), tmr.Recipient, NULL) AS Dispatch_ID    -- TUTAJ mail zamiast GUIDa osoby
        FROM `t_send_mail` tm, `t_send_mail_Recip` tmr
        WHERE
            tm.SendGUID = tmr.SendGUID
            AND IFNULL(tmr.Recipient, '') <> ''
        ;        
    --  wstaw z `t_send_mail_Recipient`  = takie sobie i STARE!!!!!!!!!!!!
        INSERT IGNORE INTO `com_msg_po`
        (GuidNg, TYPE, Medium_Type, Recipient, RecipientRole, STATUS, Dispatch_ID)
        SELECT
            tm.SendGUID AS GuidNg,
            'OUT' AS `Type`,
            'mail' AS Medium_Type,
            tmr.Recipient,
            tmr.RecipientRole,
            'NEW' AS `STATUS`,
            _Get_ID_xxxxx('', 38, 'Disp:', tm.Send_Mode, ':', _VarStr(NOW()), tmr.Recipient, NULL) AS Dispatch_ID    -- TUTAJ mail zamiast GUIDa osoby
        FROM `t_send_mail` tm, `t_send_mail_Recipient` tmr
        WHERE
            IFNULL(tmr.Recipient, '') <> ''
        ;        
    END IF;
    
    IF (_MODE = 'SEND_WERSJA') THEN
  
    SELECT valuex INTO @htmlbody FROM IReg WHERE idkey='Domyslna_tresc_maila_wersja';
    
    IF _Param_A='GASDROID' THEN
  
        INSERT IGNORE INTO com_msg_ng (GUID,Type,OWNER,Msg_Title,Status,Msg_Body,SrcTable,Sender)
        SELECT 
        IF(@session.globals.Owner='CDG',
            MD5(CONCAT(_Param_A,IF(LEFT(tel_nrinwent,1)<=8,8,9),CURRENT_DATE() )),
            MD5(CONCAT(_Param_A,CURRENT_DATE()))),'OUT','system',CONCAT('Aktualizacja aplikacji ',_Param_A),'WAIT',
        REPLACE(REPLACE(
            @htmlbody,'#app_name#',_Param_A),
            '#app_link#',IF(@session.globals.Owner='CDG',IF(LEFT(tel_nrinwent,1)<=8,
            'http://update.int.com.pl:56093/motlawa/CDG/GasDroid/Android%208.0/GasDroidCDG_a8.apk',
            'http://update.int.com.pl:56093/motlawa/CDG/GasDroid/Android%209.0/GasDroidCDG_a9.apk'),
            'http://update.int.com.pl:56093/motlawa/MPGK/GasDroidMPGK.apk')),_Param_A,
            'no-reply@motlawa.greengas.pl' FROM droid_users d 
        WHERE 
        IFNULL(d.tel_nrinwent,'')<>''  GROUP BY IF(LEFT(d.tel_nrinwent,1)<=8,8,9);

        INSERT IGNORE INTO com_msg_po (GUIDNg,Type,Medium_Type,Recipient,Status,RecipientRole,Dispatch_ID)
        SELECT MD5(CONCAT(_Param_A,IF(LEFT(tel_nrinwent,1)<=8,8,9),CURRENT_DATE() )),'OUT','mail',gmail,'','To',UUID_SHORT() FROM droid_users WHERE IFNULL(tel_nrinwent,'')<>'';

        -- Na razie piotr , trzeba zmieni na oddzialy gdzie oni trzymaja maile???

        INSERT INTO com_msg_po (GUIDNg,Type,Medium_Type,Recipient,Status,RecipientRole,Dispatch_ID)
        SELECT MD5(CONCAT(_Param_A,IF(LEFT(tel_nrinwent,1)<=8,8,9),CURRENT_DATE() )),'OUT','mail',
           'piotr.adamowicz@cdg.net.pl','','Cc',UUID_SHORT() FROM droid_users d 
        WHERE IFNULL(tel_nrinwent,'')<>''
        GROUP BY IF(LEFT(tel_nrinwent,1)<=8,8,9);
        
        -- param D  mail na sztywno,np.moj
        /* nie ma _Param_D....
        IF _Param_D <>'' THEN
            INSERT INTO com_msg_po (GUIDNg,Type,Medium_Type,Recipient,Status,RecipientRole,Dispatch_ID)
            SELECT MD5(CONCAT(_Param_A,IF(LEFT(tel_nrinwent,1)<=8,8,9),CURRENT_DATE() )),'OUT','mail',_PARAM_D,'','Cc',UUID_SHORT() FROM droid_users d 
            WHERE IFNULL(tel_nrinwent,'')<>''
            GROUP BY IF(LEFT(tel_nrinwent,1)<=8,8,9);
        END IF;
        */
    END IF;
    
    IF _Param_A='HT' THEN
    SELECT 'Jak bedzie wolna chwili to polaczyc z callem z zest_alert' as info;
    END IF;
   
  END IF; 
END $$

DELIMITER ;

/*
$Log: send_mail.sql,v $
Revision 1.5  2025/03/18 08:13:43  waliant
$SEND$ Warningi
Revision 1.4  2025/01/22 17:51:57  jmam
$send$ Aktywacja konta

Revision 1.3  2024/10/15 16:26:12  jmam
$SEND$ Wysyłka aktywacyjna

Revision 1.2  2024/01/30 11:37:01  jmam
Synch.

Revision 1.1  2023/08/02 07:18:19  apro
refactor

*/
