Delimiter //
/**
 * Backup the FI_ANA table to FI_ANA_HISTO
 * la varibale usetransaction à 2 roles
 *  => elle determine s'il faut utilisé une transaction en interne ou externe
 *  => dans le cas d'un transaction externe, on considere que c'est le processus appelant qui est chagrgé
 *     de purger fi ana histo afin de ne garder qu'une valeur par compte et par mois
 * @author Benoit
 * @ticket #4025
 * @see BUSINESS_UPDATE_FIANA
 * @see BUSINESS_BACKUP_FIANA_MEMBERS
 * @see BUSINESS_BACKUP_FIANA_MEMBERS_FROM_HISTO
 * @see LOADER_ACCOUNTS_COPY_FIANA_BACKUP
 */ 
Drop Procedure if Exists BUSINESS_BACKUP_FIANA//
 
Create Procedure BUSINESS_BACKUP_FIANA(In idaccounts Int unsigned, In usetransaction Int)
Begin
    main: begin
-- 		declare iBCF_month, iBCF_year int;
        Declare transactionStarted int default 0 ;
        DECLARE Exit HANDLER FOR SQLEXCEPTION
        Begin
                if transactionStarted  <> 0 then
                        rollback;
                        set transactionStarted  = 0;
                End if;
                resignal;
        end ;

        if idaccounts Is null Then
            Set @bbf_accMinVal = 0;
            Set @bbf_accMaxVal = 40000000;
        Else
            Set @bbf_accMinVal = idaccounts;
            Set @bbf_accMaxVal = idaccounts;
        End if;


		if Coalesce(usetransaction, 0) <> 0 then
			start transaction;
			set transactionStarted  = 1;
		End if;

        -- supression des historique afin de ne garder que le dernier du mois par compte
        Set @bbf_refDate =  AMO_BAL_REFDATE();
        Set @bbf_refDateMonth= extract(MONTH From  @bbf_refDate);
        Set @bbf_refDateYear = extract(YEAR From  @bbf_refDate);
        
        Set @bbf_dateMin = Cast(Concat(@bbf_refDateYear, '-',  @bbf_refDateMonth, '-01') As Date);
        Set @bbf_dateMax = last_day(@bbf_dateMin);
        
        -- select @bbf_dateMin, @bbf_dateMax, @bbf_accMinVal, @bbf_accMaxVal;
--         -- explain
--         Delete FI_ANA_HISTO
--         From FI_ANA 
--             Inner Join FI_ANA_HISTO
--                 On FI_ANA.ACCOUNTS_ID_ACCOUNTS = FI_ANA_HISTO.ACCOUNTS_ID_ACCOUNTS
--             Inner Join DF_DATES
--                 On FI_ANA_HISTO.FI_ANA_HISTO_DATE = DF_DATES.DF_DATES_FULL
--         Where
--             (DF_DATES.DF_DATES_YEAR = @bbf_refDateYear And DF_DATES.DF_DATES_MONTH = @bbf_refDateMonth)
--             And FI_ANA_HISTO.ACCOUNTS_ID_ACCOUNTS between @bbf_accMinVal And @bbf_accMaxVal
--         ;
        -- explain
        Delete FI_ANA_HISTO
        From FI_ANA_HISTO 
        Where
            FI_ANA_HISTO.FI_ANA_HISTO_DATE Between @bbf_dateMin And @bbf_dateMax
            And FI_ANA_HISTO.ACCOUNTS_ID_ACCOUNTS between @bbf_accMinVal And @bbf_accMaxVal
        ;

        -- Insert rows
        Insert Into 
            FI_ANA_HISTO (
                ACCOUNTS_ID_ACCOUNTS
                , ADMIN_PARAMS_ID_ADMIN_PARAMS
                , FI_ANA_HISTO_DATE
                    , FI_ANA_OUTSTANDING
                    , FI_ANA_OVERDUE
                    , FI_ANA_NOT_OVERDUE
                    , FI_ANA_DUE
                    , FI_ANA_FROZEN_AMOUNT
                    , FI_ANA_BLOCKED_ALL
                    , FI_ANA_BLOCKED_NOTOVERDUE
                    , FI_ANA_AGE_PORTFOLIO_PERIOD1
                    , FI_ANA_AGE_PORTFOLIO_PERIOD2
                    , FI_ANA_AGE_PORTFOLIO_PERIOD3
                    , FI_ANA_AGE_PORTFOLIO_PERIOD4
                    , FI_ANA_AGE_PORTFOLIO_PERIOD5
                    , FI_ANA_AGE_PORTFOLIO_PERIOD6
                    , FI_ANA_AGE_PORTFOLIO_PERIOD7
                    , FI_ANA_AGE_PORTFOLIO_PERIOD8
                    , FI_ANA_AGE_ACCOUNT_PERIOD1
                    , FI_ANA_AGE_ACCOUNT_PERIOD2
                    , FI_ANA_AGE_ACCOUNT_PERIOD3
                    , FI_ANA_AGE_ACCOUNT_PERIOD4
                    , FI_ANA_CURRENT_ORDER_AMOUNT
                    , FI_ANA_BLOCKED_ORDER_AMOUNT
                    , FI_ANA_DELIVERY_TOBEINVOICED
                    , FI_ANA_BLOCKED_DELIVERY_TOBEINVOICED
                    , FI_ANA_BANK_DRAFT
                    , FI_ANA_CASH_ASSESSMENT
                    , FI_ANA_INTERNAL_SCORE_RATING
                    , FI_ANA_EXTERNAL_SCORE_RATING
                    , FI_ANA_BPDSO
                    , FI_ANA_ACD
                    , FI_ANA_ACD_EVOLUTION
                    , FI_ANA_DSO
                    , FI_ANA_ADD
                    , FI_ANA_ADD_EVOLUTION
                    , FI_ANA_SALES
                    , FI_ANA_PAYMENT
                    , FI_ANA_UNBOUND_OUTSTANDING
                    , FI_ANA_UNBOUND_OVERDUE
                    , FI_ANA_UNBOUND_NOT_OVERDUE
                    , FI_ANA_UNBOUND_DUE
                    , FI_ANA_UNBOUND_FROZEN_AMOUNT
                    , FI_ANA_UNBOUND_BLOCKED_ALL
                    , FI_ANA_UNBOUND_BLOCKED_NOTOVERDUE
                    , FI_ANA_UNBOUND_CURRENT_ORDER_AMOUNT
                    , FI_ANA_UNBOUND_BLOCKED_ORDER_AMOUNT
                    , FI_ANA_UNBOUND_DELIVERY_TOBEINVOICED
                    , FI_ANA_UNBOUND_BLOCKED_DELIVERY_TOBEINVOICED
                    , FI_ANA_UNBOUND_BANK_DRAFT
                    , FI_ANA_UNBOUND_SALES
                    , FI_ANA_UNBOUND_PAYMENT
                    , FI_ANA_AGE_CUSTMMDE_PERIOD1
                    , FI_ANA_AGE_CUSTMMDE_PERIOD2
                    , FI_ANA_AGE_CUSTMMDE_PERIOD3
                    , FI_ANA_AGE_CUSTMMDE_PERIOD4
                    , FI_ANA_AGE_CUSTMMDE_DEBIT
                    , FI_ANA_AGE_CUSTMMDE_CREDIT

            ) 
            Select
                ACCOUNTS_ID_ACCOUNTS
                , ADMIN_PARAMS_ID_ADMIN_PARAMS
                , @bbf_refDate

                , FI_ANA_OUTSTANDING
                , FI_ANA_OVERDUE
                , FI_ANA_NOT_OVERDUE
                , FI_ANA_DUE
                , FI_ANA_FROZEN_AMOUNT
                , FI_ANA_BLOCKED_ALL
                , FI_ANA_BLOCKED_NOTOVERDUE
                , FI_ANA_AGE_PORTFOLIO_PERIOD1
                , FI_ANA_AGE_PORTFOLIO_PERIOD2
                , FI_ANA_AGE_PORTFOLIO_PERIOD3
                , FI_ANA_AGE_PORTFOLIO_PERIOD4
                , FI_ANA_AGE_PORTFOLIO_PERIOD5
                , FI_ANA_AGE_PORTFOLIO_PERIOD6
                , FI_ANA_AGE_PORTFOLIO_PERIOD7
                , FI_ANA_AGE_PORTFOLIO_PERIOD8
                , FI_ANA_AGE_ACCOUNT_PERIOD1
                , FI_ANA_AGE_ACCOUNT_PERIOD2
                , FI_ANA_AGE_ACCOUNT_PERIOD3
                , FI_ANA_AGE_ACCOUNT_PERIOD4
                , FI_ANA_CURRENT_ORDER_AMOUNT
                , FI_ANA_BLOCKED_ORDER_AMOUNT
                , FI_ANA_DELIVERY_TOBEINVOICED
                , FI_ANA_BLOCKED_DELIVERY_TOBEINVOICED
                , FI_ANA_BANK_DRAFT
                , FI_ANA_CASH_ASSESSMENT
                , FI_ANA_INTERNAL_SCORE_RATING
                , FI_ANA_EXTERNAL_SCORE_RATING
                , FI_ANA_BPDSO
                , FI_ANA_ACD
                , FI_ANA_ACD_EVOLUTION
                , FI_ANA_DSO
                , FI_ANA_ADD
                , FI_ANA_ADD_EVOLUTION
                , FI_ANA_SALES
                , FI_ANA_PAYMENT
                , FI_ANA_UNBOUND_OUTSTANDING
                , FI_ANA_UNBOUND_OVERDUE
                , FI_ANA_UNBOUND_NOT_OVERDUE
                , FI_ANA_UNBOUND_DUE
                , FI_ANA_UNBOUND_FROZEN_AMOUNT
                , FI_ANA_UNBOUND_BLOCKED_ALL
                , FI_ANA_UNBOUND_BLOCKED_NOTOVERDUE
                , FI_ANA_UNBOUND_CURRENT_ORDER_AMOUNT
                , FI_ANA_UNBOUND_BLOCKED_ORDER_AMOUNT
                , FI_ANA_UNBOUND_DELIVERY_TOBEINVOICED
                , FI_ANA_UNBOUND_BLOCKED_DELIVERY_TOBEINVOICED
                , FI_ANA_UNBOUND_BANK_DRAFT
                , FI_ANA_UNBOUND_SALES
                , FI_ANA_UNBOUND_PAYMENT
                , FI_ANA_AGE_CUSTMMDE_PERIOD1
                , FI_ANA_AGE_CUSTMMDE_PERIOD2
                , FI_ANA_AGE_CUSTMMDE_PERIOD3
                , FI_ANA_AGE_CUSTMMDE_PERIOD4
                , FI_ANA_AGE_CUSTMMDE_DEBIT
                , FI_ANA_AGE_CUSTMMDE_CREDIT

            From 
                FI_ANA
			Where 
				FI_ANA.ACCOUNTS_ID_ACCOUNTS between @bbf_accMinVal And @bbf_accMaxVal
        ;

		if transactionStarted = 1 then
			commit ;
			set transactionStarted  = 0;
		End if;

    end ;
        

End //
Delimiter ;