Delimiter //
/**
 * Author:  armen
 * Created: 20 juil. 2020
 */
Drop Procedure If Exists LOADER_CREDITPROVIDERS_NORMALIZE_MAP_ACCOUNTS//
Create Procedure LOADER_CREDITPROVIDERS_NORMALIZE_MAP_ACCOUNTS(
    In batchnum Int
)
Begin
    lcpnmp_main: Begin
    
        Set @lcpnma_batchnum = batchnum;

        -- pas d'info pour le batch 0, on utilise un greatest avec 1
        Set @lcpnma_idEntities = GET_LOADER_INFO('ID_ENTITIES', Greatest(@lcpnma_batchnum, 1));

        if NullIf(@lcpnma_idEntities, 0) Is Null Then
            leave lcpnmp_main;
        End if;

        lcpnma_findAccounts: Begin
            -- on doit obligatoirement appeler cette PS dans TOUS les lots DONT le 0, pour init la table est les données
            call LOADER_FIND_ACCOUNTS_BY_SIREN (
                        @lcpnma_batchnum -- In batchnum Int
                        , 'LOADER_CREDITPROVIDERS' -- , In tableSrc Varchar(50)
                        , 'ID_LOADER_CREDITPROVIDERS' -- , In fieldIdTableSrc Varchar(50)
                        , 'CONTACTS_BUSINESS_SIRET' -- , In fieldSiren Varchar(50)
                        , 'LOADER_STATUS_ID_LOADER_STATUS' -- , In fieldIdLoaderStatus Varchar(50)
                        , 'ACCOUNTS_REF' -- , In fieldAccountRef Varchar(50)
                        , 'LOADER_CREDITPROVIDERS_MAIN_CONTACT_ONLY' -- champ flag : tous les contacts/ main contacts only
                        , 'TMP_LCPNMA_ACCOUNTS_FOUND_BY_SIREN' -- , In tableAccountFound Varchar(50)
                        , 1 -- , In clearOutputTable SmallInt
                    )
                ;
        End ;


        lcpnma_cpFoundAccounts: Begin
            Insert Into LOADER_CREDITPROVIDERS_ACCOUNTS (
                    LDR_CRDTPRVDRS_ID_LDR_CRDTPRVDRS
                    , LOADER_STATUS_ID_LOADER_STATUS, ACCOUNTS_ID_ACCOUNTS
                )
            Select
                LOADER_BASE_TABLE_ID, LOADER_STATUS_ID_LOADER_STATUS
                , ACCOUNTS_ID_ACCOUNTS
            From TMP_LCPNMA_ACCOUNTS_FOUND_BY_SIREN
            ;
        End;

        if @lcpnma_batchnum = 0 Then
            leave lcpnmp_main;
        End if;

        Set @lcpnma_srenNfErrMsgBase = 'Siren/Siret non trouvé :';

        Set @lcpnma_idTemplateLoader = GET_LOADER_INFO('ID_TEMPLATE_LOADER', @lcpnma_batchnum);
        Set @lcpnma_crdPrdAcNFMdErrCode = 'CRPR-ACCNF-ERR'; -- Credit Providers Account Not Found : Error Mode
        Set @lcpnma_crdPrdAcNFMdWarnCode = 'CRPR-ACCNF-WARN'; -- Credit Providers Account Not Found : Warning mode
        Set @lcpnma_crdPrdAcNFMdSkipCode = 'CRPR-ACCNF-SKIP'; -- Credit Providers Account Not Found : SKip mode
        Set @lcpnma_accNfErrMd = Coalesce(
                        (
                            Select
                                TMPLOPT_FSLT.TEMPLATE_OPTIONS_CODE
                            From
                                TEMPLATE_LOADER TMPLDR_FSLT
                                Inner Join TEMPL_LOADER_has_TEMPL_OPT TMPLDR_FSLTHASOPT
                                    On TMPLDR_FSLT.ID_TEMPLATE_LOADER = TMPLDR_FSLTHASOPT.TEMPL_LOADER_ID_TEMPL_LOADER
                                Inner Join TEMPLATE_OPTIONS TMPLOPT_FSLT
                                    On TMPLDR_FSLTHASOPT.TEMPL_OPT_ID_TEMPL_OPT = TMPLOPT_FSLT.ID_TEMPLATE_OPTIONS
                            where 1 = 1
                                And TMPLDR_FSLT.ID_TEMPLATE_LOADER = @lcpnma_idTemplateLoader
                                And TMPLOPT_FSLT.TEMPLATE_OPTIONS_CODE In (
                                        @lcpnma_crdPrdAcNFMdErrCode, @lcpnma_crdPrdAcNFMdWarnCode
                                        , @lcpnma_crdPrdAcNFMdSkipCode
                                    )
                        )
                    , @lcpnma_crdPrdAcNFMdErrCode
                )
            ;

        lcpnma_updterrrefacc: Begin
            -- mise à jour des erreurs pour les comptes non trouvés par la référence de compte
            -- n'est pas impacté par l'option @lcpnma_accNfErrMd 
            Update
                LOADER_STATUS STS
                Inner Join LOADER_CREDITPROVIDERS LDR
                    On LDR.LOADER_STATUS_ID_LOADER_STATUS = STS.ID_LOADER_STATUS
            Set
                STS.LOADER_STATUS_ERRCOUNT = STS.LOADER_STATUS_ERRCOUNT + 1
                , STS.LOADER_STATUS_ERRMSGS = LOADER_CONCAT_ERR(STS.LOADER_STATUS_ERRMSGS
                                                            , Concat('Référence de compte inexistante :', LDR.ACCOUNTS_REF ) 
                                                        )
                , LDR.LOADER_CREDITPROVIDERS_ROW_REJECTED = 1
            Where 
                STS.LOADER_STATUS_BATCH_NUM = @lcpnma_batchnum
                And STS.LOADER_STATUS_ERRCOUNT = 0 -- les lignes erronnées n'on pas été traité ici
                And Trim(Coalesce(LDR.ACCOUNTS_REF, '')) <> ''
                And Not Exists (
                    Select FOUNDACC.LDR_CRDTPRVDRS_ID_LDR_CRDTPRVDRS
                    From LOADER_CREDITPROVIDERS_ACCOUNTS FOUNDACC
                    Where LDR.ID_LOADER_CREDITPROVIDERS =  FOUNDACC.LDR_CRDTPRVDRS_ID_LDR_CRDTPRVDRS
                    Limit 1
                )
            ;

        End;

        lcpnma_updterr: Begin

            if @lcpnma_accNfErrMd <> @lcpnma_crdPrdAcNFMdErrCode Then
                leave lcpnma_updterr;
            End if;

            -- Maj des erreurs des comptes non trouvés par le siren
            Update
                LOADER_STATUS STS
                Inner Join LOADER_CREDITPROVIDERS LDR
                    On LDR.LOADER_STATUS_ID_LOADER_STATUS = STS.ID_LOADER_STATUS
            Set
                STS.LOADER_STATUS_ERRCOUNT = STS.LOADER_STATUS_ERRCOUNT + 1
                , STS.LOADER_STATUS_ERRMSGS = LOADER_CONCAT_ERR(STS.LOADER_STATUS_ERRMSGS
                                                            , Concat(@lcpnma_srenNfErrMsgBase, LDR.CONTACTS_BUSINESS_SIRET ) 
                                                        ) 
                , LDR.LOADER_CREDITPROVIDERS_ROW_REJECTED = 1
            Where 
                STS.LOADER_STATUS_BATCH_NUM = @lcpnma_batchnum
                And STS.LOADER_STATUS_ERRCOUNT = 0 -- les lignes erronnées n'on pas été traité ici
                And Trim(Coalesce(LDR.ACCOUNTS_REF, '')) = ''
                And Not Exists (
                    Select FOUNDACC.LDR_CRDTPRVDRS_ID_LDR_CRDTPRVDRS
                    From LOADER_CREDITPROVIDERS_ACCOUNTS FOUNDACC
                    Where LDR.ID_LOADER_CREDITPROVIDERS =  FOUNDACC.LDR_CRDTPRVDRS_ID_LDR_CRDTPRVDRS
                    Limit 1
                )
            ;
        End;

        lcpnma_updtwarn: Begin

            if @lcpnma_accNfErrMd <> @lcpnma_crdPrdAcNFMdWarnCode Then
                leave lcpnma_updtwarn;
            End if;

            -- Maj des alertes comptes non trouvés par le siren

            Update
                LOADER_STATUS STS
                Inner Join LOADER_CREDITPROVIDERS LDR
                    On LDR.LOADER_STATUS_ID_LOADER_STATUS = STS.ID_LOADER_STATUS
            Set
                STS.LOADER_STATUS_WARNCOUNT = STS.LOADER_STATUS_WARNCOUNT + 1
                , STS.LOADER_STATUS_WARNMSGS = LOADER_CONCAT_ERR(STS.LOADER_STATUS_WARNMSGS
                                                            , Concat(@lcpnma_srenNfErrMsgBase, LDR.CONTACTS_BUSINESS_SIRET ) 
                                                        ) 
                , LDR.LOADER_CREDITPROVIDERS_ROW_REJECTED = 1
            Where 
                STS.LOADER_STATUS_BATCH_NUM = @lcpnma_batchnum
                And STS.LOADER_STATUS_ERRCOUNT = 0 -- les lignes erronnées n'on pas été traité ici
                And Trim(Coalesce(LDR.ACCOUNTS_REF, '')) = ''
                And Not Exists (
                    Select FOUNDACC.LDR_CRDTPRVDRS_ID_LDR_CRDTPRVDRS
                    From LOADER_CREDITPROVIDERS_ACCOUNTS FOUNDACC
                    Where LDR.ID_LOADER_CREDITPROVIDERS =  FOUNDACC.LDR_CRDTPRVDRS_ID_LDR_CRDTPRVDRS
                    Limit 1
                )
            ;
        End;
        lcpnma_updtskip: Begin

            if @lcpnma_accNfErrMd <> @lcpnma_crdPrdAcNFMdSkipCode Then
                leave lcpnma_updtskip;
            End if;

            -- Maj flag "rejet" pour les comptes non trouvés par le siren

            Update
                LOADER_STATUS STS
                Inner Join LOADER_CREDITPROVIDERS LDR
                    On LDR.LOADER_STATUS_ID_LOADER_STATUS = STS.ID_LOADER_STATUS
            Set
                LDR.LOADER_CREDITPROVIDERS_ROW_REJECTED = 1
            Where 
                STS.LOADER_STATUS_BATCH_NUM = @lcpnma_batchnum
                And STS.LOADER_STATUS_ERRCOUNT = 0 -- les lignes erronnées n'on pas été traité ici
                And Trim(Coalesce(LDR.ACCOUNTS_REF, '')) = ''
                And Not Exists (
                    Select FOUNDACC.LDR_CRDTPRVDRS_ID_LDR_CRDTPRVDRS
                    From LOADER_CREDITPROVIDERS_ACCOUNTS FOUNDACC
                    Where LDR.ID_LOADER_CREDITPROVIDERS =  FOUNDACC.LDR_CRDTPRVDRS_ID_LDR_CRDTPRVDRS
                    Limit 1
                )
            ;
        End;

    End;

End//
Delimiter ;
