Delimiter //
/**
    purge des compte liés en mode purge et remplace
*/
Drop Procedure if exists LOADER_LINKEDACCOUNTS_CLEAR_NOT_IMPORTED//
Create Procedure LOADER_LINKEDACCOUNTS_CLEAR_NOT_IMPORTED (
)
Begin
    llaca_main: BEGIN
        llaca_chkmode: begin
            set @codeModeImportPurge = 'M-LNKACC-PURGE';
            set @codeModeImportAdd = 'M-LNKACC-ADD';
            Set @loaderTemplateId = GET_LOADER_INFO('ID_TEMPLATE_LOADER', 1);
            Set @modeImportLnkAcc = 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 @modeImportLnkAcc <> @codeModeImportPurge Then
                -- mode ajout, on ne dois rien purger
                -- -> on quitte la ps
                leave llaca_main;
            end if ;
        end ;

        llaca_notemptyfile: begin
            Set @emptyFileChk = (
                        Select
                            src.ID_LOADER_LINKEDACCOUNTS
                        From LOADER_LINKEDACCOUNTS src
                            Inner Join LOADER_STATUS ls
                                ON src.LOADER_STATUS_ID_LOADER_STATUS = ls.ID_LOADER_STATUS
                        Where
                              ls.LOADER_STATUS_ERRCOUNT = 0
                        Limit 1
                    )
                ;
            if COALESCE( @emptyFileChk, 0) = 0 Then
                Insert Into LOADER_LOGS (
                        LOADER_STATUS_ID_LOADER_STATUS
                        , LOADER_LOGS_SRC_TABLE_ID
                        , LOADER_LOGS_BATCH_NUMM
                        , LOADER_LOGS_ERRCOUNT
                        , LOADER_LOGS_ERRMSGS
                        , LOADER_LOGS_WARNCOUNT
                        , LOADER_LOGS_WARNMSGS
                        , LOADER_LOGS_TYPE
                    )
                 Values (
                        Null
                        , Null
                        , Null
                        , 1
                        , 'Empty file detected. Aborting linked accounts purge'
                        , 0
                        , Null
                        , 'LINKEDACCOUNTS'
                    )
                ;
                leave llaca_main;
            end if ;
        end ;

        llaca_errordetected: begin
            Set @errorChk = (
                        Select
                            src.ID_LOADER_LINKEDACCOUNTS
                        From LOADER_LINKEDACCOUNTS src
                            Inner Join LOADER_STATUS ls
                                ON src.LOADER_STATUS_ID_LOADER_STATUS = ls.ID_LOADER_STATUS
                        Where
                              ls.LOADER_STATUS_ERRCOUNT = 1
                        Limit 1
                    )
                ;
            if COALESCE( @errorChk, 0) > 0 Then
                -- todo creer un warning
                 Insert Into LOADER_LOGS (
                        LOADER_STATUS_ID_LOADER_STATUS
                        , LOADER_LOGS_SRC_TABLE_ID
                        , LOADER_LOGS_BATCH_NUMM
                        , LOADER_LOGS_ERRCOUNT
                        , LOADER_LOGS_ERRMSGS
                        , LOADER_LOGS_WARNCOUNT
                        , LOADER_LOGS_WARNMSGS
                        , LOADER_LOGS_TYPE
                    )
                 Values (
                        Null
                        , Null
                        , Null
                        , 0
                        , Null
                        , 1
                        , 'Errors detected in mode purge and replaces for a linked accounts filetype'
                        , 'LINKEDACCOUNTS'
                    )
                ;
#                 leave llaca_main;
            end if ;
        end ;

        llaca_clear_aha: begin
            delete aha
            from ACCOUNTS_has_ACCOUNTS aha
                Left join (
                    LOADER_LINKEDACCOUNTS src
                    Inner Join LOADER_STATUS ls
                        ON src.LOADER_STATUS_ID_LOADER_STATUS = ls.ID_LOADER_STATUS
                        and ls.LOADER_STATUS_ERRCOUNT = 0
                ) On aha.ACCOUNTS_ID_ACCOUNTS = src.ACCOUNTS_ID_ACCOUNTS
                    and aha.ACCOUNTS_ID_ACCOUNTS_LINK =src.ACCOUNTS_ID_ACCOUNTS_LINKED
            Where
                src.LOADER_STATUS_ID_LOADER_STATUS is null
            ;
        end ;

#         llaca_clear_eha: begin
#             delete eha
#             From
#                 ENTRIES_has_ACCOUNTS eha
#                 Left Join (
#                     LOADER_LINKEDACCOUNTS src
#                     Inner Join LOADER_STATUS ls
#                         ON src.LOADER_STATUS_ID_LOADER_STATUS = ls.ID_LOADER_STATUS
#                         and ls.LOADER_STATUS_ERRCOUNT = 0
#                 ) On
#             where eha.ENTRIES_PERMANENT_ATTACHEMENT = 0;
#         end ;

    end ;

End //

Delimiter ;


