Delimiter //
/**
    In batchnum Int : num du lot, si 0 ou null traitement de l'ensembles des lignes
    
*/

Drop Procedure if exists LOADER_CONTACTS_EMAILS_UPDATE//

Create Procedure LOADER_CONTACTS_EMAILS_UPDATE (
    In batchnum int
)
Begin


    set @codeModeImportPurge= 'M-EMAILS-PURGE';
    set @codeModeImportAdd= 'M-EMAILS-ADD';
    Set @loaderTemplateId = GET_LOADER_INFO('ID_TEMPLATE_LOADER', batchnum);
    Set @modeImportEmail = Coalesce(
                (
                    select
                        TEMPLATE_OPTIONS_CODE
                    From
                        TEMPL_LOADER_has_TEMPL_OPT tmplhasopt
                        Inner Join TEMPLATE_OPTIONS templo
                            On tmplhasopt.TEMPL_OPT_ID_TEMPL_OPT = templo.ID_TEMPLATE_OPTIONS
                    where
                        tmplhasopt.TEMPL_LOADER_ID_TEMPL_LOADER = @loaderTemplateId
                        And templo.TEMPLATE_OPTIONS_CODE In (@codeModeImportPurge, @codeModeImportAdd)
                    Limit 1
                )
                , @codeModeImportAdd
            )
        ;
    if  @modeImportEmail = @codeModeImportAdd then
        -- MODE AJOUT / Mise à jour
        -- Dans la procédure LOADER_CONTACTS_NORMALIZE_MAP_EMAIL_PROVIDED
        -- on a suprimé de la table LOADER_CONTACTS_EMAILS les emails non modifié 
        -- (meme contact, meme email)
        -- pour maj, on fait un raprochement avec l'id du contact
        -- et l'email
        Update
            LOADER_STATUS lst  
            Inner Join LOADER_CONTACTS TBL_1 
                On TBL_1.LOADER_STATUS_ID_LOADER_STATUS = lst.ID_LOADER_STATUS 
            Inner Join LOADER_CONTACTS_EMAILS src  
-- 			-- pour le multicible et pour eviter doublons on doit utiliser ID_LOADER_CONTACTS
                On src.LOADER_CONTACTS_ID_LOADER_CONTACTS =  TBL_1.ID_LOADER_CONTACTS
            Inner Join CONTACTS_EMAILS tar
                On TBL_1.ID_CONTACTS = tar.CONTACTS_ID_CONTACTS
                And src.CONTACTS_EMAILS_EMAIL = tar.CONTACTS_EMAILS_EMAIL
        Set
            tar.CONTACTS_EMAILS_EMAIL = Coalesce(src.CONTACTS_EMAILS_EMAIL, tar.CONTACTS_EMAILS_EMAIL)
            , tar.DF_CHECK_STAT_ID_DF_CHECK_STAT = Coalesce(src.DF_CHECK_STAT_ID_DF_CHECK_STAT, tar.DF_CHECK_STAT_ID_DF_CHECK_STAT)
            , tar.CONTACTS_EMAILS_ORIGIN = src.CONTACTS_EMAILS_ORIGIN
        Where
            lst.LOADER_STATUS_ERRCOUNT = 0  
            And lst.LOADER_STATUS_BATCH_NUM =  batchnum
        ;
    else
        -- MODE en purge et remplace
        --
        -- PURGE :
        -- supression dans la table de prod, des emails
        -- dont l'origne est import 
        -- des contacts présents dans l'import ayant au moins un emails (sans erreur)
        Delete 
            tar.*
        From CONTACTS_EMAILS tar
        -- Where Exists(
        Where  tar.CONTACTS_ID_CONTACTS in (
                Select
                    TBL_1.ID_CONTACTS
                From
                    LOADER_CONTACTS_EMAILS src  
                    Inner Join LOADER_CONTACTS TBL_1 
                        On src.LOADER_CONTACTS_ID_LOADER_CONTACTS =  TBL_1.ID_LOADER_CONTACTS
                    Inner Join LOADER_STATUS lst  
                        On TBL_1.LOADER_STATUS_ID_LOADER_STATUS = lst.ID_LOADER_STATUS 
                Where TBL_1.ID_CONTACTS = tar.CONTACTS_ID_CONTACTS
                    And lst.LOADER_STATUS_ERRCOUNT = 0  
                    And lst.LOADER_STATUS_BATCH_NUM =  batchnum
               --  Limit 1
            )
            And tar.CONTACTS_EMAILS_ORIGIN In (2)
--             And tar.CONTACTS_EMAILS_PREFERENCE = 0 -- regle supprimée à la demande d'Olivier
        ;
        -- supression dans la table de prod
        -- des emails en court d'import quelque soit leur origine
        Delete 
            tar.*
        From CONTACTS_EMAILS tar
--         Where Exists(
        Where tar.CONTACTS_ID_CONTACTS in (
                Select
                    TBL_1.ID_CONTACTS
                From
                    LOADER_CONTACTS_EMAILS src  
                    Inner Join LOADER_CONTACTS TBL_1 
                        On src.LOADER_CONTACTS_ID_LOADER_CONTACTS =  TBL_1.ID_LOADER_CONTACTS
                    Inner Join LOADER_STATUS lst  
                        On TBL_1.LOADER_STATUS_ID_LOADER_STATUS = lst.ID_LOADER_STATUS 
                Where TBL_1.ID_CONTACTS = tar.CONTACTS_ID_CONTACTS
                    and src.CONTACTS_EMAILS_EMAIL = tar.CONTACTS_EMAILS_EMAIL
                    And lst.LOADER_STATUS_ERRCOUNT = 0  
                    And lst.LOADER_STATUS_BATCH_NUM =  batchnum
--                 Limit 1
            )
--             And tar.CONTACTS_EMAILS_PREFERENCE = 0 -- regle supprimée à la demande d'Olivier
        ;
        -- insertion des nouveau emails des contacts existants

        Insert Into CONTACTS_EMAILS (
            CONTACTS_EMAILS_EMAIL, CONTACTS_EMAILS_LAST_UPDATE, CONTACTS_EMAILS_PREFERENCE
            , CONTACTS_EMAILS_TRASHED, CONTACTS_ID_CONTACTS, DF_CHECK_STAT_ID_DF_CHECK_STAT
            , CONTACTS_EMAILS_ORIGIN
        )  
        Select 
            src.CONTACTS_EMAILS_EMAIL , src.CONTACTS_EMAILS_LAST_UPDATE , src.CONTACTS_EMAILS_PREFERENCE 
            , src.CONTACTS_EMAILS_TRASHED , TBL_1.ID_CONTACTS , src.DF_CHECK_STAT_ID_DF_CHECK_STAT  
            , src.CONTACTS_EMAILS_ORIGIN
        From  
            LOADER_STATUS lst  
            Inner Join LOADER_CONTACTS TBL_1 
                On lst.ID_LOADER_STATUS =  TBL_1.LOADER_STATUS_ID_LOADER_STATUS 
            Inner Join LOADER_CONTACTS_EMAILS src  
                On src.LOADER_CONTACTS_ID_LOADER_CONTACTS =  TBL_1.ID_LOADER_CONTACTS
            
        Where 1 = 1  
            And lst.LOADER_STATUS_ERRCOUNT = 0  
--             And (src.CONTACTS_EMAILS_PREFERENCE <> 1 ) -- regle supprimée à la demande d'Olivier
            And lst.LOADER_STATUS_BATCH_NUM =  batchnum
            And Coalesce(TBL_1.ID_CONTACTS, 0) <> 0
        ;

--         -- mise à jour des email preferé  -- regle supprimée à la demande d'Olivier
--         Update
--             LOADER_STATUS lst  
--             Inner Join LOADER_CONTACTS TBL_1 
--                 On TBL_1.LOADER_STATUS_ID_LOADER_STATUS = lst.ID_LOADER_STATUS 
--             Inner Join LOADER_CONTACTS_EMAILS src  
-- 			-- pour le multicible et pour eviter doublons on doit utiliser ID_LOADER_CONTACTS
--                 On src.LOADER_CONTACTS_ID_LOADER_CONTACTS =  TBL_1.ID_LOADER_CONTACTS
--             Inner Join CONTACTS_EMAILS tar
--                 On TBL_1.ID_CONTACTS = tar.CONTACTS_ID_CONTACTS
--                 And src.CONTACTS_EMAILS_EMAIL = tar.CONTACTS_EMAILS_EMAIL
--         Set
--             tar.DF_CHECK_STAT_ID_DF_CHECK_STAT = Coalesce(src.DF_CHECK_STAT_ID_DF_CHECK_STAT, tar.DF_CHECK_STAT_ID_DF_CHECK_STAT)
--             , tar.CONTACTS_EMAILS_ORIGIN = src.CONTACTS_EMAILS_ORIGIN
--         Where
--             lst.LOADER_STATUS_ERRCOUNT = 0  
--             And tar.CONTACTS_EMAILS_PREFERENCE= 1
--             And lst.LOADER_STATUS_BATCH_NUM =  batchnum
--         ;
        

        -- on est dans la procédure de mise à jour
        -- de facto il n'y a pas de mise à jour de tél pour les NOUVEAU contacts
    End if;

--         -- avant l'insertion est en purge et remplace
--         -- suite modification regle import des téléphone
--         -- afin d'avoir une regle qui s'en reporche
--         -- la nouvelle regle est la suivante
--         -- afin de préserver les modifications réalisées dans l'interface 
--         -- (satut validation, status supression)
--         -- les emails ne doivent plus être purgés
--         -- on laisse laisse en l'etat, on ne doit importer que les nouveaux emails
--         -- pour cela on suprimes dans la LOADER_CONTACTS_EMAILS les email existant
--         -- pour un meme contact
--         Delete 
--             src.*
--         From
--             LOADER_CONTACTS_EMAILS src 
-- 			-- pour le multicible et pour eviter doublons on doit utiliser ID_LOADER_CONTACTS
-- 			Inner Join LOADER_CONTACTS TBL_1 
--                 On src.LOADER_CONTACTS_ID_LOADER_CONTACTS =  TBL_1.ID_LOADER_CONTACTS
--             Inner Join LOADER_STATUS lst  
--                 On TBL_1.LOADER_STATUS_ID_LOADER_STATUS = lst.ID_LOADER_STATUS  
--             Inner Join CONTACTS_EMAILS tar
--                 on TBL_1.ID_CONTACTS = tar.CONTACTS_ID_CONTACTS
--                 And src.CONTACTS_EMAILS_EMAIL = tar.CONTACTS_EMAILS_EMAIL
--         Where 1 = 1
--             -- on ne dois pas tester les erreur ici car on supprimer pour non doublonnage
--             -- des emails des contacts existant
-- --             And lst.LOADER_STATUS_ERRCOUNT = 0  
--             And lst.LOADER_STATUS_BATCH_NUM =  batchnum
-- --             And Coalesce(TBL_1.ID_CONTACTS, 0) <> 0 -- implicite
--         ;
-- --         Delete 
-- --             tar.*
-- --         From
-- --             LOADER_CONTACTS_EMAILS src 
-- -- 			-- pour le multicible et pour eviter doublons on doit utiliser ID_LOADER_CONTACTS
-- -- --             Inner Join LOADER_STATUS lst  
-- -- --                 On src.LOADER_STATUS_ID_LOADER_STATUS = lst.ID_LOADER_STATUS  
-- -- --             Inner Join LOADER_CONTACTS TBL_1 
-- -- --                 On lst.ID_LOADER_STATUS =  TBL_1.LOADER_STATUS_ID_LOADER_STATUS
-- -- 			Inner Join LOADER_CONTACTS TBL_1 
-- --                 On src.LOADER_CONTACTS_ID_LOADER_CONTACTS =  TBL_1.ID_LOADER_CONTACTS
-- --             Inner Join LOADER_STATUS lst  
-- --                 On TBL_1.LOADER_STATUS_ID_LOADER_STATUS = lst.ID_LOADER_STATUS  
-- -- 
-- --             Inner Join CONTACTS_EMAILS tar
-- --                 on TBL_1.ID_CONTACTS = tar.CONTACTS_ID_CONTACTS
-- --         Where 1 = 1
-- --             And lst.LOADER_STATUS_ERRCOUNT = 0  
-- --             And lst.LOADER_STATUS_BATCH_NUM =  batchnum
-- -- --             And Coalesce(TBL_1.ID_CONTACTS, 0) <> 0 -- implicite
-- --         ;
-- 
--         -- insetion des nouveau email pour les contact existants
--     
--         Insert Into CONTACTS_EMAILS (
--             CONTACTS_EMAILS_EMAIL, CONTACTS_EMAILS_LAST_UPDATE, CONTACTS_EMAILS_PREFERENCE, CONTACTS_EMAILS_TRASHED, 
--             CONTACTS_ID_CONTACTS, DF_CHECK_STAT_ID_DF_CHECK_STAT
--         )  
--         Select 
--             src.CONTACTS_EMAILS_EMAIL , src.CONTACTS_EMAILS_LAST_UPDATE , src.CONTACTS_EMAILS_PREFERENCE , src.CONTACTS_EMAILS_TRASHED ,  
--             TBL_1.ID_CONTACTS , 
--             src.DF_CHECK_STAT_ID_DF_CHECK_STAT  
--         From  
--             LOADER_CONTACTS_EMAILS src  
--             Inner Join LOADER_STATUS lst  
--                 On src.LOADER_STATUS_ID_LOADER_STATUS = lst.ID_LOADER_STATUS  
-- --          pour le multicible et pour eviter doublons on doit utiliser ID_LOADER_CONTACTS
-- --             Inner Join LOADER_CONTACTS TBL_1 
-- --                 On lst.ID_LOADER_STATUS =  TBL_1.LOADER_STATUS_ID_LOADER_STATUS
--             Inner Join LOADER_CONTACTS TBL_1 
--                 On src.LOADER_CONTACTS_ID_LOADER_CONTACTS =  TBL_1.ID_LOADER_CONTACTS
--         Where 1 = 1  
--             And lst.LOADER_STATUS_ERRCOUNT = 0  
--             And lst.LOADER_STATUS_BATCH_NUM =  batchnum
--             And Coalesce(TBL_1.ID_CONTACTS, 0) <> 0
--         ;
End //

Delimiter ;
