Delimiter //
/**
 * Initializes the FI_ANA_MEMBERS_HISTO table
*
 * Attention, au préalable appeler la procédure :
 *	- BUSINESS_KEEP_ONE_ACCOUNT_FIANA_PER_MONTH
 *
 * @author Benoit
 */ 
Drop Procedure If Exists BUSINESS_INITIALIZE_FI_ANA_MEMBERS_HISTO//
 
Create Procedure BUSINESS_INITIALIZE_FI_ANA_MEMBERS_HISTO(
	In forceErase Int
	, In maxMonthCount Int
	, In idmembers Int unsigned
)
Begin

    main: Begin

        Declare memberFilter Varchar(10) Default Ifnull(idmembers, '%');

        Declare oldestDate, previousMonthDate, oldestCalculableDate Date;
        Declare histoCount Int;

        If maxMonthCount Is null Then
            Set maxMonthCount = 37;
        End If;

        chkErase: Begin
            Start Transaction;
            -- Check if FI_ANA_MEMBERS_HISTO is empty
            Set histoCount = (Select Count(*) From FI_ANA_MEMBERS_HISTO Limit 1);
            If (histoCount > 0) And (Coalesce(forceErase, 0) = 0) And idmembers is Not Null Then
                -- fiana is not empty and erase flag is net set 
                -- trowing exception
                Rollback;
                SIGNAL SQLSTATE '99001'
                    SET MESSAGE_TEXT = 'FI_ANA_MEMBERS_HISTO is not empty but erase flag is not setted'
                ;

            End if;
            Commit; -- ending transaction for furter truncate
        End;
        
        If idmembers Is Null Then
            -- table initialization
            Truncate Table FI_ANA_MEMBERS_HISTO; -- cause commit
            Truncate Table FI_ANA_MEMBERS; -- cause commit
        Else
            Delete FI_ANA_MEMBERS_HISTO From FI_ANA_MEMBERS_HISTO Where FI_ANA_MEMBERS_HISTO.ACCOUNTS_ID_ACCOUNTS Like memberFilter;
            Delete FI_ANA_MEMBERS From FI_ANA_MEMBERS Where FI_ANA_MEMBERS.ACCOUNTS_ID_ACCOUNTS Like memberFilter;
        End If;

        process: begin

            Declare loopMonthDate, todayDate Date;

            -- looking for the oldest FiANA date
            Set oldestDate = (Select Min(FI_ANA_HISTO_DATE) oldest From FI_ANA_HISTO);
            -- set date at end of the month
            Set oldestDate = LAST_DAY(oldestDate);
            
            -- get last date for previous month of current date
            Set todayDate = CURDATE(); 
            Set previousMonthDate = DATE_ADD(todayDate, Interval -Day(todayDate) Day);
            
            -- get oldest calculable date
            Set oldestCalculableDate = DATE_ADD(previousMonthDate, Interval -maxMonthCount Month);
            -- set date at end of the month
            Set oldestCalculableDate = LAST_DAY(oldestCalculableDate);

            If oldestCalculableDate > oldestDate Then
                -- date max de calcul plafonner à 36 mois par defaut (maxMonthCount)
                Set oldestDate = oldestCalculableDate;
            End If;
			
            Call BUSINESS_FI_ANA_INSERT_NEW_MEMBERS(idmembers);

            -- process loop
            Set loopMonthDate = oldestDate;
            While loopMonthDate <= previousMonthDate Do
            
                -- AMO_BAL_REFDATE is optionnal for this procedure
                Call BUSINESS_BACKUP_FIANA_MEMBERS_FROM_HISTO(loopMonthDate, idmembers, 1);

                -- set month on the last day of next month
                Set loopMonthDate = LAST_DAY(DATE_ADD(loopMonthDate, Interval 1 Day));
            End While;

            -- ici le calcul à été effectuer jusqu'au dernier mois clos
            -- on calcul pour le jour

            Set @AMO_BAL_DATE_DEFAULT = Null;

            Start transaction;
            Call BUSINESS_UPDATE_FIANA_MEMBERS(idmembers);
            Call BUSINESS_BACKUP_FIANA_MEMBERS(idmembers, 0);
            Commit;

        End;
        
    End;
End //
