Delimiter //
/**
    In batchnum Int : num du lot, si 0 ou null traitement de l'ensembles des lignes
*/
Drop Procedure if exists LOADER_CONTACTS_INSERT//
Create Procedure LOADER_CONTACTS_INSERT (
    In batchnum int, 
    In lastId Int
)
Begin
        set @lci_batchnum = batchnum;
        set @lci_lastId = lastId;
--         set @lci_batchnum = 1; -- pour debug
--         set @lci_lastId = 0; -- pour debug
        Insert Into CONTACTS (
            ID_CONTACTS, 
            ACCOUNTS_ID_ACCOUNTS, DF_LANGUAGES_ID_DF_LANGUAGE, CONTACTS_REF, 
            DF_SAND_STATE_ID_DF_SAND_STATE, CONTACTS_TRASHED, CONTACTS_LAST_UPDATE,
            CONTACTS_NOTE
        )  
        Select  
            src.ID_CONTACTS_NEW + @lci_lastId,
            src.ACCOUNTS_ID_ACCOUNTS ,  
            src.DF_LANGUAGES_ID_DF_LANGUAGES,
            src.CONTACTS_REF, 
            src.DF_SAND_STATE_ID_DF_SAND_STATE, src.CONTACTS_TRASHED, Coalesce(src.CONTACTS_LAST_UPDATE, now()),
			src.CONTACTS_NOTE

        From  
            LOADER_CONTACTS src  
            Inner Join LOADER_STATUS lst  
                On src.LOADER_STATUS_ID_LOADER_STATUS = lst.ID_LOADER_STATUS  

        Where 1 = 1  
            And lst.LOADER_STATUS_ERRCOUNT = 0  
            And lst.LOADER_STATUS_BATCH_NUM =  @lci_batchnum
            And Coalesce(src.ID_CONTACTS_NEW, 0) <> 0
        ;

        -- mise à jour de l'id compte avec les compte en cour d'importation
        Update 
            CONTACTS CTC
            Inner Join LOADER_CONTACTS LDCTC
                On CTC.ID_CONTACTS = LDCTC.ID_CONTACTS_NEW + @lci_lastId
            Inner Join LOADER_STATUS LS  
                On LDCTC.LOADER_STATUS_ID_LOADER_STATUS = LS.ID_LOADER_STATUS   
            Inner Join LOADER_ACCOUNTS_REPLICATION LDCTREP
                On LS.ID_LOADER_STATUS = LDCTREP.LOADER_STATUS_ID_LOADER_STATUS
        Set
            CTC.ACCOUNTS_ID_ACCOUNTS = LDCTREP.ID_ACCOUNTS
        Where
            LS.LOADER_STATUS_BATCH_NUM = @lci_batchnum
            And CTC.ACCOUNTS_ID_ACCOUNTS Is Null
            And LDCTREP.ID_ACCOUNTS Is Not Null
            And LS.LOADER_STATUS_ERRCOUNT = 0   
        ;


        -- maj des contact principaux
        update 
            LOADER_CONTACTS src 
            Inner Join LOADER_STATUS lst 
                On lst.ID_LOADER_STATUS = src.LOADER_STATUS_ID_LOADER_STATUS
            Inner Join LOADER_CONTACTS_ACCOUNTS TBL_1
                On TBL_1.LOADER_STATUS_ID_LOADER_STATUS = lst.ID_LOADER_STATUS
            Inner Join ACCOUNTS trgt
                On trgt.ID_ACCOUNTS = TBL_1.ACCOUNTS_ID_ACCOUNTS 
        Set  
            trgt.CONTACTS_ID_CONTACTS = src.ID_CONTACTS_NEW + @lci_lastId
        Where 1 = 1 
            And lst.LOADER_STATUS_ERRCOUNT = 0 
            And lst.LOADER_STATUS_BATCH_NUM =  @lci_batchnum
--             And src.IS_MAIN_CONTACT = 1 -- implicite
            And Coalesce(src.ID_CONTACTS_NEW, 0) <> 0 
        ;
End //

Delimiter ;





