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

Create Procedure LOADER_CONTACTS_PHONES_UPDATE (
    In batchnum int
)
Begin

--     
    set @codeModeImportPurge= 'M-PHONES-PURGE';
    set @codeModeImportAdd= 'M-PHONES-ADD';
    Set @loaderTemplateId = GET_LOADER_INFO('ID_TEMPLATE_LOADER', batchnum);
    Set @modeImportTel = 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  @modeImportTel = @codeModeImportAdd then

        -- MODE AJOUT / Mise à jour
        -- Dans la procédure LOADER_CONTACTS_NORMALIZE_MAP_PHONE_PROVIDED
        -- on a suprimé de la table LOADER_CONTACTS_PHONES les tél non modifié 
        -- (meme contact, meme num, même type)
        -- pour maj, on fait un raprochement avec l'id du contact
        -- et le tél en format numérique seul
        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_PHONES 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_PHONES tar
                On TBL_1.ID_CONTACTS = tar.CONTACTS_ID_CONTACTS
                And src.CONTACTS_PHONE_NUMERIC_ONLY = tar.CONTACTS_PHONE_NUMERIC_ONLY
        Set
            tar.DF_PHONE_TYP_ID_DF_PHONE_TYP = Coalesce(src.DF_PHONE_TYP_ID_DF_PHONE_TYP, tar.DF_PHONE_TYP_ID_DF_PHONE_TYP)
            , 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_PHONES_ORIGIN = src.CONTACTS_PHONES_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 téléphones
        -- dont l'origne est import et non favoris
        -- des contacts présents dans l'import ayant au moins un tél (sans erreur)
        Delete 
            tar.*
        From CONTACTS_PHONES tar
        -- Where Exists(
        Where tar.CONTACTS_ID_CONTACTS in (
                Select
                    TBL_1.ID_CONTACTS
                From
                    LOADER_CONTACTS_PHONES 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_PHONES_ORIGIN In (2)
--             And tar.CONTACTS_PHONE_PREFERENCE = 0 -- regle supprimée à la demande d'Olivier
        ;
        -- supression dans la table de prod
        -- des téléphones en court d'import 
--         Delete 
--             tar.*
--         From CONTACTS_PHONES tar
-- --         Where Exists(
--         Where tar.CONTACTS_PHONE_NUMERIC_ONLY In (
--                 Select
--                     -- TBL_1.ID_CONTACTS
--                     tar.CONTACTS_PHONE_NUMERIC_ONLY
--                 From
--                     LOADER_CONTACTS_PHONES 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_PHONE_NUMERIC_ONLY = tar.CONTACTS_PHONE_NUMERIC_ONLY
--                     And lst.LOADER_STATUS_ERRCOUNT = 0  
--                     And lst.LOADER_STATUS_BATCH_NUM =  batchnum
-- --                 Limit 1
--             )
-- --             And tar.CONTACTS_PHONE_PREFERENCE = 0 -- regle supprimée à la demande d'Olivier
--         ;
            -- Optimisation suite lenteur preprod mysql 56
            -- requiere index sur CONTACTS_PHONES.CONTACTS_PHONE_NUMERIC_ONLY
            -- Alter Table CONTACTS_PHONES
            --    add index ix_CONTACTS_PHONES_01 (CONTACTS_PHONE_NUMERIC_ONLY, CONTACTS_ID_CONTACTS)
            Delete tar
            From 
                LOADER_CONTACTS_PHONES 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 
                Inner Join CONTACTS_PHONES tar
    --                 force index for join (ix_CONTACTS_PHONES_01 )
                    On TBL_1.ID_CONTACTS = tar.CONTACTS_ID_CONTACTS
                    and src.CONTACTS_PHONE_NUMERIC_ONLY = tar.CONTACTS_PHONE_NUMERIC_ONLY
            Where 
                lst.LOADER_STATUS_ERRCOUNT = 0  
                And lst.LOADER_STATUS_BATCH_NUM =  batchnum
            ;
-- 
        -- insertion des nouveau tél des contacts existants
        Insert Into CONTACTS_PHONES (
            CONTACTS_ID_CONTACTS, CONTACTS_PHONES_LAST_UPDATE, CONTACTS_PHONES_TRASHED, CONTACTS_PHONE_NUMBER, 
            CONTACTS_PHONE_NUMERIC_ONLY, CONTACTS_PHONE_PREFERENCE, DF_CHECK_STAT_ID_DF_CHECK_STAT, DF_PHONE_TYP_ID_DF_PHONE_TYP
            , CONTACTS_PHONES_ORIGIN
        )  
        Select  
            TBL_1.ID_CONTACTS , src.CONTACTS_PHONES_LAST_UPDATE , src.CONTACTS_PHONES_TRASHED , src.CONTACTS_PHONE_NUMBER , 
            src.CONTACTS_PHONE_NUMERIC_ONLY , Coalesce(src.CONTACTS_PHONE_PREFERENCE, 0) , src.DF_CHECK_STAT_ID_DF_CHECK_STAT , src.DF_PHONE_TYP_ID_DF_PHONE_TYP
            , src.CONTACTS_PHONES_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_PHONES 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_PHONE_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 tel 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_PHONES 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_PHONES tar
--                 On TBL_1.ID_CONTACTS = tar.CONTACTS_ID_CONTACTS
--                 And src.CONTACTS_PHONE_NUMERIC_ONLY = tar.CONTACTS_PHONE_NUMERIC_ONLY
--         Set
--             tar.DF_PHONE_TYP_ID_DF_PHONE_TYP = Coalesce(src.DF_PHONE_TYP_ID_DF_PHONE_TYP, tar.DF_PHONE_TYP_ID_DF_PHONE_TYP)
--             , 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_PHONES_ORIGIN = src.CONTACTS_PHONES_ORIGIN
--         Where
--             lst.LOADER_STATUS_ERRCOUNT = 0  
--             And tar.CONTACTS_PHONE_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;

        
End //
