Delimiter //
/**
 * compute BUSINESS_LATE_INTEREST
 * @author Armen
 */ 
Drop Procedure if Exists BUSINESS_INITIALIZE_FI_ANA_HISTO//
 
Create Procedure BUSINESS_INITIALIZE_FI_ANA_HISTO(
	In forceErase int
	, In maxMonthCount Int
	, In optionsmask Int
	, In idaccounts Int unsigned
	, in refDate Date
)
Begin
# 	Declare FIANAHISTO_OPT_EXCLUDE_DSORB Int;
# 	Declare FIANAHISTO_OPT_MAX_DSO_MASQ Int;
	
	Set @bifh_FIANAHISTO_OPT_EXCLUDE_DSORB =  Cast(X'8000' As Unsigned);
	Set @bifh_FIANAHISTO_OPT_MAX_DSO_MASQ = Cast(X'7FFF' As Unsigned);
    -- 7300 = 20  ans
    -- 365<= @FIANAHISTO_OPT_MAX_DSO_VALUE <= 7300
    
    bifh_main: begin
        
#         Declare oldestDuDate, previusMonth, oldestCalculatedMonth DATE;
# 		Declare iFH_check int;
# 		Declare iuFH_openEntryStatId int unsigned;

        Set  @bifah_idaccounts = idaccounts ;
#         Set  @bifah_idaccounts = null;
        if idaccounts Is null Then
            Set @bifah_accMinVal = 0;
            Set @bifah_accMaxVal = 4e7;
        Else
            Set @bifah_accMinVal = idaccounts;
            Set @bifah_accMaxVal = idaccounts;
        End if;

		if maxMonthCount Is null Then
			Set @bifh_maxMonthCount = 35;
		else 
			set @bifh_maxMonthCount = maxMonthCount -1;
		End If;

#         set @bifah_optionsmask = 0;
        set @bifah_optionsmask = Coalesce(optionsmask, 0);

        set @FIANAHISTO_OPT_MAX_DSO_VALUE = Least( 7300 
                , Greatest(
                        Cast((@bifh_FIANAHISTO_OPT_MAX_DSO_MASQ & @bifah_optionsmask) As unsigned)
                        , 365
                   )
            )
        ;


-- 		if optionsmask Is null Then
-- 			Set optionsmask = 0;
-- 		End If;
-- modification flag force erase
-- on efface si forceerase = 1, sinon on laisse
--         chkErase: Begin
--             Start transaction;
--             -- check if FI_ANA_HISTO is empty
--             Set @bifh_iFH_check = (select count(*) From FI_ANA_HISTO Limit 1);
--             if (@bifh_iFH_check > 0) AND (Coalesce(forceErase, 0) = 0) And idaccounts 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_HISTO is not empty and erase flag not set'
--                 ;
-- 
--             End if;
--             commit; -- ending transaction for furter truncate
--         End ;
        -- controle plus strict, pour ne pas effacer pas megarde
        -- pour la valeur -1 (simplement le mois en cours)
        if (Coalesce(forceErase, 0) = 1) then
#         if (Coalesce(forceErase, 0) <> 0) then

			-- afin de permetre le bon calcul de du status de lettrage
			-- on doit forcer le status de lettre de toutes les ecriture impacté à ouvert
			-- on doit mettre à jour le champ MATCHINGCODES_UPDATE pour forcer lre recalcul du du status
			set @bifh_opentEntriesStatus = (Select ID_DF_MATCHINGCODE_STATE From DF_MATCHINGCODE_STATE Where DF_MATCHINGCODE_STATE_CODE = 'OPEN ENTRY');

			if @bifah_idaccounts is null Then
-- 				call CASH_PRINT('truncate all fiana tables', 'fiAnaHis');
				-- table initialization
				Truncate Table FI_ANA_MEMBERS_HISTO; -- cause commit
				Truncate Table FI_ANA_HISTO; -- cause commit
				Truncate Table FI_ANA_MEMBERS; -- cause commit
				Truncate Table FI_ANA; -- cause commit
				-- mise à jour du status de lettrage des écritures
				Update ENTRIES Set DF_MATCH_STAT_ID_DF_MATCH_STAT = @bifh_opentEntriesStatus;
				-- mise à jour du champ MATCHINGCODES_UPDATE;
				Update MATCHINGCODES tar
				Set tar.MATCHINGCODES_UPDATE = now()
				Where
                    tar.ID_MATCHINGCODES In (
# 					Exists(
						Select 
							src.ENTRIES_COMPUTED_DATA_EXTID
						From
							ENTRIES_COMPUTED_DATA src
						Where
							src.ENTRIES_COMPUTED_DATA_EXTID = tar.ID_MATCHINGCODES
							And src.ENTRIES_COMPUTED_DATA_KEY = 'LASTMATCH'
# 						Limit 1
					)
				;
			else
-- 				call CASH_PRINT('del account fiana tables', 'fiAnaHis');
				delete FI_ANA_MEMBERS_HISTO From FI_ANA_MEMBERS_HISTO;
				delete FI_ANA_MEMBERS From FI_ANA_MEMBERS;
				delete FI_ANA_HISTO From FI_ANA_HISTO Where FI_ANA_HISTO.ACCOUNTS_ID_ACCOUNTS between @bifah_accMinVal And @bifah_accMaxVal ;
-- 				delete FI_ANA_HISTO From FI_ANA_HISTO Where FI_ANA_HISTO.ACCOUNTS_ID_ACCOUNTS like @accFilter;
				delete FI_ANA From FI_ANA Where FI_ANA.ACCOUNTS_ID_ACCOUNTS  between @bifah_accMinVal And @bifah_accMaxVal ;
-- 				delete FI_ANA From FI_ANA Where FI_ANA.ACCOUNTS_ID_ACCOUNTS like @accFilter;

				-- mise à jour du status de lettrage des écritures du compte
				Update 
					ENTRIES tar 
					Inner Join ENTRIES_has_ACCOUNTS src
						On tar.ID_ENTRIES = src.ENTRIES_ID_ENTRIES
				Set tar.DF_MATCH_STAT_ID_DF_MATCH_STAT = @bifh_opentEntriesStatus
				Where
					src.ACCOUNTS_ID_ACCOUNTS  between @bifah_accMinVal And @bifah_accMaxVal 
-- 					src.ACCOUNTS_ID_ACCOUNTS like @accFilter
					And src.ENTRIES_PERMANENT_ATTACHEMENT = 1
				;
				-- mise à jour du champ MATCHINGCODES_UPDATE;
				Update MATCHINGCODES tar
				Set tar.MATCHINGCODES_UPDATE = now()
				Where
					Exists(
						Select 
							src.ENTRIES_COMPUTED_DATA_EXTID
						From
							ENTRIES_COMPUTED_DATA src
							Inner Join ENTRIES_has_ACCOUNTS eha
								On src.ENTRIES_ID_ENTRIES = eha.ENTRIES_ID_ENTRIES
								And src.ENTRIES_COMPUTED_DATA_KEY = 'LASTMATCH'
						Where
							src.ENTRIES_COMPUTED_DATA_EXTID = tar.ID_MATCHINGCODES
							And eha.ENTRIES_PERMANENT_ATTACHEMENT = 1
							And eha.ACCOUNTS_ID_ACCOUNTS  between @bifah_accMinVal And @bifah_accMaxVal 
-- 							And eha.ACCOUNTS_ID_ACCOUNTS like @accFilter
						Limit 1
					)
				;

			end if;
		end if;
leave bifh_main;
        -- initialisation of matching code status
        initMatchStat: begin
-- 			call CASH_PRINT('updat match stat', 'fiAnaHis');
            -- getting open matching code status
            Set @iuFH_openEntryStatId = (Select ID_DF_MATCHINGCODE_STATE From DF_MATCHINGCODE_STATE Where DF_MATCHINGCODE_STATE_CODE = 'OPEN ENTRY');
            
            Update ENTRIES E 
			Set E.DF_MATCH_STAT_ID_DF_MATCH_STAT = @iuFH_openEntryStatId
			Where E.ID_ENTRIES In (
                        Select  EHA.ENTRIES_ID_ENTRIES
                        From ENTRIES_has_ACCOUNTS EHA
                        Where
                            EHA.ACCOUNTS_ID_ACCOUNTS  between @bifah_accMinVal And @bifah_accMaxVal
                            And E.ID_ENTRIES = EHA.ENTRIES_ID_ENTRIES
                    )
                ;
        end;


		call BUSINESS_FI_ANA_INSERT_NEW_ACCOUNTS(@bifah_idaccounts, null);
		call BUSINESS_FI_ANA_INSERT_NEW_MEMBERS(Null);

        process: begin
# 			Declare dIFH_month, dIFH_today Date;
--             Start transaction;

            -- get last date for previous month of current date
--             Set @bifh_dIFH_today = CURDATE(); 
            Set @bifh_dIFH_today = Coalesce(refDate, CURDATE()); 
-- 			if @bifh_dIFH_today > CURDATE() then
-- 				-- garde fou, on ne peu calculuer pour le futur
-- 				Set @bifh_dIFH_today = CURDATE(); 
-- 			end if;
--             Set @bifh_previusMonth = LAST_DAY(DATE_ADD(@bifh_dIFH_today, INTERVAL -DAY(@bifh_dIFH_today) day));
            Set @bifh_previusMonth = DATE_ADD(@bifh_dIFH_today, INTERVAL -DAY(@bifh_dIFH_today) day);
            
            Set @bifh_oldestCalculatedMonth = DATE_ADD(@bifh_previusMonth, INTERVAL -@bifh_maxMonthCount MONTH);
            Set @bifh_oldestCalculatedMonth = LAST_DAY(@bifh_oldestCalculatedMonth);

			-- looking for oldest due date
			-- !!!! les champs ENTRIES_DUE_DATE, ENTRIES_DATE sont de type date non nullable
			-- cependant en raison de la permissivité de MYSQL on peut malgré ça avoir des valeurs null
			-- ou date equivalant à null (0000-00-00)
			-- les dates null font disfonctionner l'historisation
			-- si dessous controunement
            Set @bifh_oldestDuDate = Coalesce(
								(
									Select min(
											Coalesce(
												NullIf(ENTRIES_DUE_DATE, '0000-00-00')
												, NullIf(ENTRIES_DATE, '0000-00-00')
											)
										) oldest 
									FROM ENTRIES
								)
								, @bifh_oldestCalculatedMonth
							)
						;
            -- set date at end of the month
            Set @bifh_oldestDuDate = LAST_DAY(@bifh_oldestDuDate);
--             call CASH_PRINT(@bifh_oldestDuDate, '@bifh_oldestDuDate');

            -- select @bifh_oldestCalculatedMonth , @bifh_oldestDuDate
            if @bifh_oldestCalculatedMonth > @bifh_oldestDuDate Then
                -- date max de calcul plafonner à 36 mois par defaut (@bifh_maxMonthCount)
                set @bifh_oldestDuDate = @bifh_oldestCalculatedMonth;
            End If;
			
-- 			call BUSINESS_FI_ANA_INSERT_NEW_ACCOUNTS(idaccounts);
--             call BUSINESS_FI_ANA_INSERT_NEW_MEMBERS(Null);

            Set @bifh_dIFH_month = @bifh_oldestDuDate;
-- 			call CASH_PRINT(@bifh_dIFH_month, '@bifh_dIFH_month');
-- 			call CASH_PRINT(@bifh_previusMonth, '@bifh_previusMonth');

            -- process loop
            While @bifh_dIFH_month <= @bifh_previusMonth Do
            -- select @bifh_dIFH_month, @bifh_previusMonth;
                -- set var @AMO_BAL_DATE_DEFAULT because it vas use inside function AMO_BAL_REFDATE()
                -- AMO_BAL_REFDATE was use bas all procedure and vu for calculating balance
                Set @AMO_BAL_DATE_DEFAULT = @bifh_dIFH_month; 
-- 				call CASH_PRINT(@bifh_dIFH_month, 'Month_loop');
-- 				select @AMO_BAL_DATE_DEFAULT, @bifh_dIFH_month, AMO_BAL_REFDATE();
                Start transaction;
                --
                call BUSINESS_CALCULATE_MATCHING_CODE_STATE(@bifah_idaccounts , null, null);

				call BUSINESS_UPDATE_FIANA(@bifah_idaccounts , null);
                call BUSINESS_UPDATE_FIANA_MEMBERS(Null);
				-- le dso RB est bassé les valeurs de FI_ANA_HISTO
				-- on dois populer FI_ANA_HISTO avec le DSO RB
				call BUSINESS_BACKUP_FIANA(@bifah_idaccounts , 0);
                call BUSINESS_BACKUP_FIANA_MEMBERS(Null, 0);

				if (@bifh_FIANAHISTO_OPT_EXCLUDE_DSORB & @bifah_optionsmask) <> @bifh_FIANAHISTO_OPT_EXCLUDE_DSORB Then
					-- on ne dois pas exclure le calcul du DSO RB
					Call BUSINESS_DSO_ROLLBACK(@bifah_idaccounts , null, 1, @FIANAHISTO_OPT_MAX_DSO_VALUE);
					
				End if;

                commit;
                -- set month on the last day of next month
                Set @bifh_dIFH_month = LAST_DAY(DATE_ADD(@bifh_dIFH_month, INTERVAL 1 DAY));
            End While;
			
			if refDate = CURDATE() Then
				-- 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_CALCULATE_ENTRIES_MATCHING_CODE_STATE(idaccounts);
				call BUSINESS_CALCULATE_MATCHING_CODE_STATE(@bifah_idaccounts , null, null);

-- 				call BUSINESS_BACKUP_FIANA(idaccounts, 0);
-- 				call BUSINESS_BACKUP_FIANA_MEMBERS(Null, 0);
--                 -- excution volotaire de fiana APRES BACKUP
                call BUSINESS_UPDATE_FIANA(@bifah_idaccounts , null);
				call BUSINESS_UPDATE_FIANA_MEMBERS(Null);
				call BUSINESS_BACKUP_FIANA(@bifah_idaccounts , 0);
				call BUSINESS_BACKUP_FIANA_MEMBERS(Null, 0);

				if (@bifh_FIANAHISTO_OPT_EXCLUDE_DSORB & @bifah_optionsmask) <> @bifh_FIANAHISTO_OPT_EXCLUDE_DSORB Then
					-- on ne dois pas exclure le calcul du DSO RB
					Call BUSINESS_DSO_ROLLBACK(@bifah_idaccounts , null, 1, @FIANAHISTO_OPT_MAX_DSO_VALUE);
				End if;
			End if;
            commit;

        end;
        
    End;
End //
