Delimiter //
/**
 * Author:  armen
 * Created: 21 juil. 2020
 * 
 * donnée exipré si ou CREDITS_PROVIDERS_VAL_DATE <> null et < curdate()
 * donnée non exipré si ou CREDITS_PROVIDERS_VAL_DATE null ou > curdate()
 * 
 * regle d'import
 * - si donnée expiré recherche d'une donnée de même ref et même val_date : ajout/ maj
 * - si donnée non expiré
 *   -> si vide recherche d'une donnée de même ref et val_date vide et même score: ajout/ maj
 *   -> si non vide recherche d'une donnée de même ref et même val_date : ajout/ maj
 * si ajout d'une donnée non expiré, rendre expiré les données existante non expirés. Ceci sera fait dans 
 * lors de la copie des données à l'aide de la P.S Business dédié : BUSINESS_CREDITSPROVIDERS_KEEP_1_UNEXPIRED_BY_REF
 * 
 */
Drop Procedure If Exists LOADER_CREDITPROVIDERS_NORMALIZE_MAP_CREATE_OR_UPDATE//
Create Procedure LOADER_CREDITPROVIDERS_NORMALIZE_MAP_CREATE_OR_UPDATE(
    In batchnum Int
)
Begin
    lcpnmcu_main: Begin
        lcpnmcu_init: Begin
            Set @lcpnmcou_batchnum = batchnum;
            Set @lcpnmcou_curdate = curdate();

            -- pour dev / debogade, laisser en commentaire
            -- Set @lcpnmcou_batchnum = 1;


        End;
        -- il y a 2 cas de figures à traiter : 
        --  - echu/non date val null (REMARQUE parfois présence de date type '0000-00-00')
        --  - non echu date val not null: 
        -- on maj les ids dans la table LOADER_CREDITPROVIDERS_ACCOUNTS des cas ci dessus trouvés sur la table de prod
        -- on ajute des nouveaus id pour les nouvelle données (non trouvées en prod)

        lcpnmcu_find_valdte_notnull: Begin
            -- recherche des credits providers echu/non echu exisants avec la même date val (date val non null): 
            -- - même compte
            -- - même ref
            -- - même date val
            Update
                LOADER_CREDITPROVIDERS LDR
                Inner Join LOADER_STATUS LS
                    On LS.ID_LOADER_STATUS = LDR.LOADER_STATUS_ID_LOADER_STATUS
                Inner Join LOADER_CREDITPROVIDERS_ACCOUNTS LDA
                    On LDR.ID_LOADER_CREDITPROVIDERS = LDA.LDR_CRDTPRVDRS_ID_LDR_CRDTPRVDRS
            Set
                LDA.ID_CREDITS_PROVIDERS = (
                    Select
                        CP.ID_CREDITS_PROVIDERS
                    From CREDITS_PROVIDERS CP
                    Where CP.ACCOUNTS_ID_ACCOUNTS = LDA.ACCOUNTS_ID_ACCOUNTS
                        And LDR.CREDITS_PROVIDERS_REF = CP.CREDITS_PROVIDERS_REF
                        And LDR.CREDITS_PROVIDERS_VAL_DATE = CP.CREDITS_PROVIDERS_VAL_DATE
                        -- le plus récent limit de credit non echu
                    Order By CP.CREDITS_PROVIDERS_UPDATE_DATE Desc, CP.ID_CREDITS_PROVIDERS Desc
                    Limit 1
                )
            Where
                LS.LOADER_STATUS_BATCH_NUM =  @lcpnmcou_batchnum
                And LS.LOADER_STATUS_ERRCOUNT = 0
                And LDA.ID_CREDITS_PROVIDERS Is Null
                And LDR.CREDITS_PROVIDERS_VAL_DATE Is Not Null -- implicite
            ;

        End;


        lcpnmcu_find_valdte_null: Begin

            -- recherche des credit providers non echu exisant avec date val null : 
            -- - même compte
            -- - même ref
            -- - même score
            -- - date null
            Update
                LOADER_CREDITPROVIDERS LDR
                Inner Join LOADER_STATUS LS
                    On LS.ID_LOADER_STATUS = LDR.LOADER_STATUS_ID_LOADER_STATUS
                Inner Join LOADER_CREDITPROVIDERS_ACCOUNTS LDA
                    On LDR.ID_LOADER_CREDITPROVIDERS = LDA.LDR_CRDTPRVDRS_ID_LDR_CRDTPRVDRS
            Set
                LDA.ID_CREDITS_PROVIDERS = (
                    Select
                        CP.ID_CREDITS_PROVIDERS
                    From CREDITS_PROVIDERS CP
                    Where CP.ACCOUNTS_ID_ACCOUNTS = LDA.ACCOUNTS_ID_ACCOUNTS
                        And LDR.CREDITS_PROVIDERS_REF = CP.CREDITS_PROVIDERS_REF
                        And LDR.CREDITS_PROVIDERS_SCORE = CP.CREDITS_PROVIDERS_SCORE
                        And (
                            CP.CREDITS_PROVIDERS_VAL_DATE Is Null
                            Or CP.CREDITS_PROVIDERS_VAL_DATE = '0000-00-00'
                        )
                        -- le plus récent limit de credit non echu
                    Order By CP.CREDITS_PROVIDERS_UPDATE_DATE Desc, CP.ID_CREDITS_PROVIDERS Desc
                    Limit 1
                )
            Where
                LS.LOADER_STATUS_BATCH_NUM =  @lcpnmcou_batchnum
                And LS.LOADER_STATUS_ERRCOUNT = 0
                And LDA.ID_CREDITS_PROVIDERS Is Null
                And (
                    LDR.CREDITS_PROVIDERS_VAL_DATE Is Null 
                    Or LDR.CREDITS_PROVIDERS_VAL_DATE  = '0000-00-00'
                )
            ;

        End;

        lcpnmcu_find_newids: Begin

            Drop Temporary Table If Exists TMP_CREDITPROVIDERS_NEWIDS;
            Create Temporary Table TMP_CREDITPROVIDERS_NEWIDS (
                ID_CREDITS_PROVIDERS Int Unsigned Not Null Auto_increment
                , LOADER_STATUS_ID_LOADER_STATUS Int Unsigned Not Null
                , LDR_CRDTPRVDRS_ID_LDR_CRDTPRVDRS Int Unsigned Not Null
                , LDR_CRDTPRVDRS_ID_LDR_CRDTPRVDRS_ACC Int Unsigned Not Null

                , Primary Key (ID_CREDITS_PROVIDERS)
                , index (LOADER_STATUS_ID_LOADER_STATUS)
                , index (LDR_CRDTPRVDRS_ID_LDR_CRDTPRVDRS)
                , index (LDR_CRDTPRVDRS_ID_LDR_CRDTPRVDRS_ACC)
            );

            Insert Into TMP_CREDITPROVIDERS_NEWIDS (
                    LOADER_STATUS_ID_LOADER_STATUS
                    , LDR_CRDTPRVDRS_ID_LDR_CRDTPRVDRS
                    , LDR_CRDTPRVDRS_ID_LDR_CRDTPRVDRS_ACC
                )
            Select 
                LS.ID_LOADER_STATUS
                , LDR.ID_LOADER_CREDITPROVIDERS
                , LCA.ID_LOADER_CREDITPROVIDERS_ACCOUNTS
            From 
                LOADER_STATUS LS
                Inner Join LOADER_CREDITPROVIDERS LDR
                    On LS.ID_LOADER_STATUS = LDR.LOADER_STATUS_ID_LOADER_STATUS
                Inner Join LOADER_CREDITPROVIDERS_ACCOUNTS LCA
                    On LDR.ID_LOADER_CREDITPROVIDERS = LCA.LDR_CRDTPRVDRS_ID_LDR_CRDTPRVDRS
            Where
                LS.LOADER_STATUS_BATCH_NUM = @lcpnmcou_batchnum
                And LS.LOADER_STATUS_ERRCOUNT = 0
                And LCA.ID_CREDITS_PROVIDERS is null
                And LCA.ID_CREDITS_PROVIDERS_NEW is null
            ;

            Update 
                LOADER_CREDITPROVIDERS_ACCOUNTS LDA
                Inner Join TMP_CREDITPROVIDERS_NEWIDS TMP
                    ON TMP.LDR_CRDTPRVDRS_ID_LDR_CRDTPRVDRS_ACC = LDA.ID_LOADER_CREDITPROVIDERS_ACCOUNTS
            Set
                LDA.ID_CREDITS_PROVIDERS_NEW = TMP.ID_CREDITS_PROVIDERS
            Where
                LDA.ID_CREDITS_PROVIDERS_NEW Is Null
                And LDA.ID_CREDITS_PROVIDERS Is Null
            ;

        End;

    End;

End//
Delimiter ;

