Delimiter //
/**
 * Main contact is not provided
 * - S'il n'y a pas déjà un contact "main contact" dans le compte : initialiser le contact à intégrer avec "main contact=1"
 * - S'il y a déjà un "main contact" dans le compte et que sa référence externe de contact est différente : initialiser le contact à intégrer avec main contact = 0
 *
 * A executer une fois à la fin des cycles de normalisation
 * Contexte import COMPTE uniquement
 * 
 * @author Benoit
 * @ticket #2113
 */
Drop Procedure If Exists LOADER_CONTACTS_NORMALIZE_MAP_MAIN_FROMACCOUNT_NOTPROVIDED//
Create Procedure LOADER_CONTACTS_NORMALIZE_MAP_MAIN_FROMACCOUNT_NOTPROVIDED(
    In batchnum Int
)
Begin

        Set @lcnmmfn_batchnum = batchnum;
--         Set @lcnmmfn_batchnum = 1; -- pour debug

        Drop Temporary table if exists TMP_LOADER_CONTACTS_ACCOUNTS;
        Create Temporary Table TMP_LOADER_CONTACTS_ACCOUNTS
        AS 
        Select
            Min(LC.LOADER_STATUS_ID_LOADER_STATUS) LOADER_STATUS_ID_LOADER_STATUS
            , Min(LAR.LOADER_ACCOUNTS_ID_LOADER_ACCOUNTS) LOADER_ACCOUNTS_ID_LOADER_ACCOUNTS
            , A.ID_ACCOUNTS
        From
            LOADER_STATUS LS
            Inner Join LOADER_CONTACTS LC
                On LS.ID_LOADER_STATUS = LC.LOADER_STATUS_ID_LOADER_STATUS
            Inner Join LOADER_ACCOUNTS_REPLICATION LAR                          -- contexte d'import de compte
                On LAR.LOADER_STATUS_ID_LOADER_STATUS = LC.LOADER_STATUS_ID_LOADER_STATUS
                And LAR.ID_ACCOUNTS Is Not Null
            Inner Join ACCOUNTS A                                               -- compte existant
                On A.ID_ACCOUNTS = LAR.ID_ACCOUNTS     
                And A.CONTACTS_ID_CONTACTS Is Null                              -- le compte existant n'a pas de contact principal
        Where
            
            LC.IS_MAIN_CONTACT = 0                                              -- le contact n'est pas fourni comme contact principal
            And LS.LOADER_STATUS_BATCH_NUM = @lcnmmfn_batchnum
            And A.ID_ACCOUNTS Not In (                                          -- aucun contact principal n'a été fourni dans l'import en cours
                Select LARX.ID_ACCOUNTS
                From LOADER_CONTACTS LCX
                    Inner Join LOADER_ACCOUNTS_REPLICATION LARX
                    On LARX.LOADER_STATUS_ID_LOADER_STATUS = LCX.LOADER_STATUS_ID_LOADER_STATUS
                Where 
                    LCX.IS_MAIN_CONTACT = 1
                    And LARX.ID_ACCOUNTS = A.ID_ACCOUNTS                        -- optim
            )
           And A.ID_ACCOUNTS Not In (                                           -- le contact principal du compte n'a pas déjà été défini dans l'import en cours
                Select LCAX.ACCOUNTS_ID_ACCOUNTS
                From LOADER_CONTACTS_ACCOUNTS LCAX
                Where 
                    LCAX.ACCOUNTS_ID_ACCOUNTS = A.ID_ACCOUNTS
                    And LCAX.ACCOUNTS_ID_ACCOUNTS_NEW Is Null
           )
        Group By A.ID_ACCOUNTS
        Order By LOADER_STATUS_ID_LOADER_STATUS
    ;
    Insert Into LOADER_CONTACTS_ACCOUNTS (
            LOADER_STATUS_ID_LOADER_STATUS
            , ACCOUNTS_ID_ACCOUNTS
        )
    Select 
            LOADER_STATUS_ID_LOADER_STATUS
            , ID_ACCOUNTS
    From TMP_LOADER_CONTACTS_ACCOUNTS;

    Drop Temporary table if exists TMP_LOADER_CONTACTS_ACCOUNTS;
        Create Temporary Table TMP_LOADER_CONTACTS_ACCOUNTS
        AS 
        Select
            Min(LC.LOADER_STATUS_ID_LOADER_STATUS) LOADER_STATUS_ID_LOADER_STATUS
            , Min(LAR.LOADER_ACCOUNTS_ID_LOADER_ACCOUNTS) LOADER_ACCOUNTS_ID_LOADER_ACCOUNTS
            , LAR.ID_ACCOUNTS_NEW
        From
            LOADER_STATUS LS
            Inner Join LOADER_CONTACTS LC
                On LS.ID_LOADER_STATUS = LC.LOADER_STATUS_ID_LOADER_STATUS
            Inner Join LOADER_ACCOUNTS_REPLICATION LAR                          -- contexte d'import de compte
                On LAR.LOADER_STATUS_ID_LOADER_STATUS = LC.LOADER_STATUS_ID_LOADER_STATUS
--                 And LAR.ID_ACCOUNTS_NEW Is Not Null
        Where
            
            LC.IS_MAIN_CONTACT = 0                                              -- le contact n'est pas fourni comme contact principal
            And LS.LOADER_STATUS_BATCH_NUM = @lcnmmfn_batchnum
            And LAR.ID_ACCOUNTS_NEW Is Not Null
            And LAR.ID_ACCOUNTS_NEW Not In (                                    -- aucun contact principal n'a été fourni pour le compte dans l'import en cours
                Select LARX.ID_ACCOUNTS_NEW
                From LOADER_CONTACTS LCX
                    Inner Join LOADER_ACCOUNTS_REPLICATION LARX
                        On LARX.LOADER_STATUS_ID_LOADER_STATUS = LCX.LOADER_STATUS_ID_LOADER_STATUS
                Where 
                    LCX.IS_MAIN_CONTACT = 1
                    And LARX.ID_ACCOUNTS_NEW = LAR.ID_ACCOUNTS_NEW              -- optim
            )
            And LAR.ID_ACCOUNTS_NEW Not In (                                    -- le contact principal du compte n'a pas déjà été défini dans l'import en cours
                Select LCAX.ACCOUNTS_ID_ACCOUNTS_NEW
                From LOADER_CONTACTS_ACCOUNTS LCAX
                Where 
                    LCAX.ACCOUNTS_ID_ACCOUNTS Is Null
                    And LCAX.ACCOUNTS_ID_ACCOUNTS_NEW = LAR.ID_ACCOUNTS_NEW
            )
        Group By LAR.ID_ACCOUNTS_NEW
        Order By LOADER_STATUS_ID_LOADER_STATUS
    ;

    Insert Into LOADER_CONTACTS_ACCOUNTS (
            LOADER_STATUS_ID_LOADER_STATUS
            , ACCOUNTS_ID_ACCOUNTS_NEW
            , LOADER_ACCOUNTS_ID_LOADER_ACCOUNTS
        )
    Select 
            LOADER_STATUS_ID_LOADER_STATUS
            , ID_ACCOUNTS_NEW
            , LOADER_ACCOUNTS_ID_LOADER_ACCOUNTS
    From TMP_LOADER_CONTACTS_ACCOUNTS;

    Drop Temporary table if exists TMP_LOADER_CONTACTS_ACCOUNTS;


End//
Delimiter ;