Delimiter //
/**
 * Backup the FI_ANA_MEMBERS table to FI_ANA_MEMBERS_HISTO
 *
 * La variabale 'usetransaction' a 2 roles
 *  => elle détermine s'il faut utiliser une transaction interne ou externe
 *  => dans le cas d'une transaction externe, on considère que c'est le processus appelant qui est chargé
 *     de purger FI_ANA_MEMBERS_HISTO afin de ne garder qu'une valeur par compte et par mois
 *
 * @author Benoit
 * @see BUSINESS_UPDATE_FIANA
 * @see BUSINESS_FI_ANA_INSERT_NEW_MEMBERS
 * @see BUSINESS_UPDATE_FIANA_MEMBERS
 * @see BUSINESS_BACKUP_FIANA
 * @see BUSINESS_BACKUP_FIANA_MEMBERS_FROM_HISTO
 */ 
Drop Procedure if Exists BUSINESS_BACKUP_FIANA_MEMBERS//
 
Create Procedure BUSINESS_BACKUP_FIANA_MEMBERS(In idmembers Int unsigned, In usetransaction Int)
Begin
    main: Begin

--         Declare memberFilter Varchar(10) Default Ifnull(idmembers, '%');
        Declare transactionStarted Int Default 0 ;

        Declare Exit Handler For Sqlexception
        Begin
            If transactionStarted  <> 0 Then
                Rollback;
                Set transactionStarted  = 0;
            End If;
            Resignal;
        End ;

        Set @bbfam_idmembers = idmembers;
        Set @bbfam_mbrMinVal = Coalesce(@bbfam_idmembers, 0);
        Set @bbfam_mbrMaxVal = Coalesce(@bbfam_idmembers, 1000000);



		If Coalesce(usetransaction, 0) <> 0 Then
            Start Transaction;
            Set transactionStarted  = 1;
		End If;

        Set @bbfm_refDate =  AMO_BAL_REFDATE();
        Set @bbfm_refDateMonth= extract(MONTH From  @bbfm_refDate);
        Set @bbfm_refDateYear = extract(YEAR From  @bbfm_refDate);
        
        Set @bbfm_dateMin = Cast(Concat(@bbfm_refDateYear, '-',  @bbfm_refDateMonth, '-01') As Date);
        Set @bbfm_dateMax = last_day(@bbfm_dateMin);

        -- select @bbfm_dateMin, @bbfm_dateMax, @bbfam_mbrMinVal, @bbfam_mbrMaxVal;

-- 		If Coalesce(usetransaction, 0) <> 0 Then
            -- nettoyage historique du mois
            -- explain
--             Delete HISTO
--             From 
--                 FI_ANA_MEMBERS FI
--                 Inner Join FI_ANA_MEMBERS_HISTO HISTO
--                     On FI.MEMBERS_ID_MEMBERS = HISTO.MEMBERS_ID_MEMBERS
--                 Inner Join DF_DATES
--                     On HISTO.FI_ANA_HISTO_DATE = DF_DATES.DF_DATES_FULL
--             Where
--                 (
--                     DF_DATES.DF_DATES_YEAR = Extract(Year From AMO_BAL_REFDATE()) 
--                     And DF_DATES.DF_DATES_MONTH = Extract(Month From AMO_BAL_REFDATE())
--                 )
--                 And HISTO.MEMBERS_ID_MEMBERS Between @bbfam_mbrMinVal And @bbfam_mbrMaxVal
-- --                 And HISTO.MEMBERS_ID_MEMBERS Like memberFilter
--             ;
            -- explain
            Delete HISTO
            From 
                FI_ANA_MEMBERS_HISTO HISTO
            Where
                HISTO.MEMBERS_ID_MEMBERS Between @bbfam_mbrMinVal And @bbfam_mbrMaxVal
                And FI_ANA_HISTO_DATE Between @bbfm_dateMin And @bbfm_dateMax
            ;
-- 		End If;

        -- Insert rows
        Insert Into 
            FI_ANA_MEMBERS_HISTO (
                MEMBERS_ID_MEMBERS
                , ADMIN_PARAMS_ID_ADMIN_PARAMS
                , FI_ANA_ACCOUNTS_COUNT
                , FI_ANA_ACCOUNTS_HASH
                , 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_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
                MEMBERS_ID_MEMBERS
                , ADMIN_PARAMS_ID_ADMIN_PARAMS
                , FI_ANA_ACCOUNTS_COUNT
                , FI_ANA_ACCOUNTS_HASH
                , AMO_BAL_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_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_MEMBERS FIMBR
            Where 
                FIMBR.MEMBERS_ID_MEMBERS  Between @bbfam_mbrMinVal And @bbfam_mbrMaxVal
        ;

        If transactionStarted = 1 Then
            Commit;
            Set transactionStarted  = 0;
        End If;

    End;

End //
Delimiter ;