Delimiter $$
/**
 * Calculate DSO ROLLBACK for each ACCOUNTS
 * Updates the FI_ANA_DSO Field on table FI_ANA
 * le DSO est basé sut FI_ANA_HISTO
 * @ticket #3378
 * @author Armen
 */ 
Drop Procedure If Exists BUSINESS_DSO_ROLLBACK $$
Create Procedure BUSINESS_DSO_ROLLBACK (
        In idaccounts Int unsigned
        , In identities Int unsigned
        , In toHisto Int
        , In iDSO_maxDso int unsigned
    )
    bdrlb_Main: Begin
        Declare dtDSO_refdate, dtDSO_caEdate, dtDSO_caBdate Date;
        Declare iDSO_dsoPerMonth Int;
--         Declare  iDSO_toHisto Int;

        Set @drb_max_months = 36;

        Set iDSO_dsoPerMonth = 30;
--         Set iDSO_maxDso = 365;
--         Set iDSO_toHisto = Coalesce(toHisto, 0);

        -- Set @bdrlb_debugMode = Coalesce(@debugMode, @bdrlb_debugMode, 0);
        Set @bdrlb_debugMode = Cast(Coalesce(@debugMode, @bdrlb_debugMode, 0) as Decimal(10, 0));

        if idaccounts Is null Then
            Set @bdrlb_accMinVal = 0;
            Set @bdrlb_accMaxVal = 4e7;
        Else
            Set @bdrlb_accMinVal = idaccounts;
            Set @bdrlb_accMaxVal = idaccounts;
        End if;

        if identities Is null Then
            Set @bdrlb_enttMinVal = 0;
            Set @bdrlb_enttMaxVal = 4e7;
        Else
            Set @bdrlb_enttMinVal = identities;
            Set @bdrlb_enttMaxVal = identities;
        End if;
        
--     oustanding: Begin
        -- determination de la periode pour la chiffre d'affaire
--         
-- 	dans fiana on ne stock que les que le denier jours des mois clos
--	et la veille du mois en cours
--	on dois donc tester si la date de référnce est un mois clos ou pas
--	cela detemine la date de fin de periode pour le filtre sur la table fianahisto

		Set dtDSO_refdate = AMO_BAL_REFDATE(); -- jour du mois de référence
        Set @dtDSO_refdate = dtDSO_refdate;

        Set @iDSO_dsoPerMonth = iDSO_dsoPerMonth;
        Set @iDSO_maxDso = LEAST(GREATEST(Coalesce(iDSO_maxDso, 365), 365), 15*365);
--         Select  @iDSO_maxDso;
--         Set @iDSO_toHisto = iDSO_toHisto;
        Set @iDSO_toHisto = Coalesce(toHisto, 0);

		if Date_Format(@dtDSO_refdate, '%Y%m') = Date_Format(CURDATE(), '%Y%m')  then
-- 			-- on est dans le mois courrant
-- 			-- les données historisé sont j-1
			Set dtDSO_caEdate = dtDSO_refdate; 
		Else
			-- retro calcul
			Set dtDSO_caEdate = LAST_DAY(dtDSO_refdate); -- dernier jour du mois de référence
            Set @iDSO_toHisto = 1;
		End if ;
	
        Set @dtDSO_caEdate = dtDSO_caEdate;
        Set dtDSO_caBdate = DATE_SUB(@dtDSO_caEdate, INTERVAL DAYOFMONTH(@dtDSO_caEdate)-1 DAY); -- 1er jour du mois de référence
        Set @dtDSO_caBdate = dtDSO_caBdate;
        Set @dtDSO_oldestdate = Date_sub(@dtDSO_caEdate, interval @drb_max_months + 1 month) ;
--         Set @dtDSO_oldestdate = Date_sub(CURDATE(), interval @drb_max_months + 1 month) ;
        /*
            Select @dtDSO_refdate, @iDSO_dsoPerMonth, @iDSO_maxDso, @iDSO_toHisto
                    , @dtDSO_caEdate, @dtDSO_caBdate, @dtDSO_oldestdate
                ;
        */


        bdrlb_currentOUtstanding: Begin
            -- table temporaire avec l'encour par compte pour la date de référence
            Drop Temporary Table If Exists TMP_ACC_OUTSTANDING;
            Create Temporary Table TMP_ACC_OUTSTANDING (
                ACCOUNTS_ID_ACCOUNTS Int Unsigned Not Null Primary Key
                , FI_ANA_OUTSTANDING Decimal(19, 4) Not Null default 0
            )
            ;
            Insert Into TMP_ACC_OUTSTANDING (ACCOUNTS_ID_ACCOUNTS, FI_ANA_OUTSTANDING )
            Select
                acc.ID_ACCOUNTS, greatest(Coalesce(fah.FI_ANA_OUTSTANDING, FI_ANA.FI_ANA_OUTSTANDING, 0), 0)
            From ACCOUNTS acc
                Left Join FI_ANA_HISTO fah
                    On acc.ID_ACCOUNTS = fah.ACCOUNTS_ID_ACCOUNTS
--                     And fah.FI_ANA_HISTO_DATE = @dtDSO_refdate
                    And fah.FI_ANA_HISTO_DATE = @dtDSO_caEdate
                Left Join FI_ANA 
                    on acc.ID_ACCOUNTS = FI_ANA.ACCOUNTS_ID_ACCOUNTS
            Where
                acc.ID_ACCOUNTS between @bdrlb_accMinVal And @bdrlb_accMaxVal
                And acc.ENTITIES_ID_ENTITIES between @bdrlb_enttMinVal And @bdrlb_enttMaxVal
            ;
        End ;
            -- ici la table TMP_ACC_OUTSTANDING contient les encours par comptes à la date de référence
            -- c'est ces valeurs qu'il faudra épuiser
            -- SELECT * FROM TMP_ACC_OUTSTANDING  Where ACCOUNTS_ID_ACCOUNTS In (629, 6795, 556)
            -- SELECT ACCOUNTS_ID_ACCOUNTS, FI_ANA_OUTSTANDING, FI_ANA_SALES, FI_ANA_HISTO_DATE FROM FI_ANA_HISTO Where ACCOUNTS_ID_ACCOUNTS In (629, 6795, 556) order By ACCOUNTS_ID_ACCOUNTS, FI_ANA_HISTO_DATE Desc

        bdrlb_month_sales_sum : Begin
            -- creation de la table des ventes cumulées par mois jusqu'a la date de référence
            -- le champ SALES_PER_MONTH n'est pas le montant des ventes du mois considéré
            -- le champ SALES_PER_MONTH est la somme de toutes les ventes jusqu'au mois considéré
            -- quand on a déterminié le mois ou l'encore est epuisé
            -- on afine la valeur du dso en regardant le montant non épuisé du mois suivant
            -- 
            Drop Temporary Table If Exists TMP_ACC_MONTH_SALES_SUM ;
#             Create Temporary Table TMP_ACC_MONTH_SALES_SUM AS

            Create Temporary Table If Not Exists TMP_ACC_MONTH_SALES_SUM (
                        ACCOUNTS_ID_ACCOUNTS Int UNSIGNED Not Null
                        , FI_ANA_HISTO_DATE DATE
                        , FI_ANA_OUTSTANDING DECIMAL(19, 4)
                        , SALES_PER_MONTH DECIMAL(19, 4)

                        , Index(ACCOUNTS_ID_ACCOUNTS)
                        , Index(FI_ANA_HISTO_DATE, ACCOUNTS_ID_ACCOUNTS)
                    )
                ;
            Insert Into TMP_ACC_MONTH_SALES_SUM (
                    ACCOUNTS_ID_ACCOUNTS, FI_ANA_HISTO_DATE
                    , FI_ANA_OUTSTANDING, SALES_PER_MONTH
                )

            Select
                fah1.ACCOUNTS_ID_ACCOUNTS, fah1.FI_ANA_HISTO_DATE
                , tao.FI_ANA_OUTSTANDING
                , Coalesce(
                    (
                        Select sum(fah2.FI_ANA_SALES)
                        From FI_ANA_HISTO fah2
                        Where fah1.ACCOUNTS_ID_ACCOUNTS = fah2.ACCOUNTS_ID_ACCOUNTS
                            and fah1.FI_ANA_HISTO_DATE <= fah2.FI_ANA_HISTO_DATE
--                             And fah2.FI_ANA_HISTO_DATE between @dtDSO_oldestdate and @dtDSO_refdate
                            And fah2.FI_ANA_HISTO_DATE between @dtDSO_oldestdate and @dtDSO_caEdate
                    )
                    , 0
                )
                    SALES_PER_MONTH
            From
                FI_ANA_HISTO fah1
                Inner Join TMP_ACC_OUTSTANDING tao
                    On fah1.ACCOUNTS_ID_ACCOUNTS = tao.ACCOUNTS_ID_ACCOUNTS
            Where
--                 fah1.FI_ANA_HISTO_DATE between @dtDSO_oldestdate and @dtDSO_refdate
                fah1.FI_ANA_HISTO_DATE between @dtDSO_oldestdate and @dtDSO_caEdate
                -- implicite par jointure
            ;
#             Alter Table TMP_ACC_MONTH_SALES_SUM
#                 Add Index IX_TMP_ACC_MONTH_SALES_SUM_01(ACCOUNTS_ID_ACCOUNTS, FI_ANA_HISTO_DATE )
#             ;
        End ;
            -- select * from TMP_ACC_MONTH_SALES_SUM  Where ACCOUNTS_ID_ACCOUNTS In (629, 6795, 556) order By ACCOUNTS_ID_ACCOUNTS, FI_ANA_HISTO_DATE desc;

        bdrlb_runoutmonth: Begin
            -- calcul des mois ou l'encours est consomé
            Drop Temporary Table If Exists TMP_RUNOUT_MONTH;
#             Create Temporary Table TMP_RUNOUT_MONTH AS
            Create Temporary Table TMP_RUNOUT_MONTH (
                        ACCOUNTS_ID_ACCOUNTS INT UNSIGNED NOT NULL
                        , CONSUMED_MONTH_BEGIN_DATE  Date
                        , CONSUMED_MONTH_END_DATE Date
                        , NEXT_MONTH_BEGIN_DATE Date
                        , NEXT_MONTH_END_DATE Date
                        , Index (ACCOUNTS_ID_ACCOUNTS, NEXT_MONTH_BEGIN_DATE, NEXT_MONTH_END_DATE )
                        , Index(ACCOUNTS_ID_ACCOUNTS, CONSUMED_MONTH_BEGIN_DATE, CONSUMED_MONTH_END_DATE )
                    )
                ;
            Insert Into TMP_RUNOUT_MONTH (
                      ACCOUNTS_ID_ACCOUNTS, CONSUMED_MONTH_END_DATE
                  )
            Select ACCOUNTS_ID_ACCOUNTS,  max(FI_ANA_HISTO_DATE) CONSUMED_MONTH_END_DATE
    --             , least(Date_add(max(FI_ANA_HISTO_DATE), Interval 1 month) ,curdate()) AFTER_COSUMED_MONTH
                -- le dernier jours du mois précédant
            From TMP_ACC_MONTH_SALES_SUM
            Where FI_ANA_OUTSTANDING <= SALES_PER_MONTH 
            Group By ACCOUNTS_ID_ACCOUNTS 
            ;

#             Alter Table TMP_RUNOUT_MONTH
#                 Add Column CONSUMED_MONTH_BEGIN_DATE  Date
#                 , Add Column NEXT_MONTH_BEGIN_DATE  Date
#                 , Add Column NEXT_MONTH_END_DATE  Date
# --                 , Add Index IX_TMP_RUNOUT_MONTH_01(ACCOUNTS_ID_ACCOUNTS)
#                 , Add Index IX_TMP_RUNOUT_MONTH_02(ACCOUNTS_ID_ACCOUNTS, NEXT_MONTH_BEGIN_DATE, NEXT_MONTH_END_DATE )
#                 , Add Index IX_TMP_RUNOUT_MONTH_03(ACCOUNTS_ID_ACCOUNTS, CONSUMED_MONTH_BEGIN_DATE, CONSUMED_MONTH_END_DATE )
#             ;

            Update TMP_RUNOUT_MONTH 
            Set 
                CONSUMED_MONTH_BEGIN_DATE = (
                    @bdrlb_csm_mth_bd_dt := Date_Sub(CONSUMED_MONTH_END_DATE, Interval DAYOFMONTH(CONSUMED_MONTH_END_DATE)-1 DAY)
                )
                , NEXT_MONTH_END_DATE = (
                    @bdrlb_nxt_mth_ed_dt_app := 
                        Case
                            When (
                                @bdrlb_nxt_mth_ed_dt_calc := (
                                        Date_sub(
                                            Date_add(@bdrlb_csm_mth_bd_dt , Interval 2 Month)
                                            , interval 1 day
                                        )
                                    )
                                ) > @dtDSO_refdate
                            Then
                                @dtDSO_refdate
                            Else
                                @bdrlb_nxt_mth_ed_dt_calc
                        End
                    
--                         @bdrlb_nxt_mth_ed_dt :=  Least( @dtDSO_refdate
--                                 , Date_sub(
--                                         Date_add(@bdrlb_csm_mth_bd_dt , Interval 2 Month)
--                                         , interval 1 day
--                                     )
--                         )
                    )
                , NEXT_MONTH_BEGIN_DATE  = Date_Sub(@bdrlb_nxt_mth_ed_dt_app, Interval DAYOFMONTH(@bdrlb_nxt_mth_ed_dt_app)-1 DAY)
            ;
--             Update TMP_RUNOUT_MONTH Set CONSUMED_MONTH_BEGIN_DATE = Date_Sub(CONSUMED_MONTH_END_DATE, Interval DAYOFMONTH(CONSUMED_MONTH_END_DATE)-1 DAY);
-- 
--             Update TMP_RUNOUT_MONTH 
--             Set 
-- --                 NEXT_MONTH_BEGIN_DATE = Date_add(CONSUMED_MONTH_BEGIN_DATE , Interval 1 Month)
--                 NEXT_MONTH_BEGIN_DATE = Date_add(CONSUMED_MONTH_BEGIN_DATE , Interval 1 Month)
--                 , NEXT_MONTH_END_DATE = Least(curdate()
--                                             , Date_sub(
--                                                     Date_add(CONSUMED_MONTH_BEGIN_DATE , Interval 2 Month)
--                                                     , interval 1 day
--                                                 )
--                                     )
--             ;
        End ;
            /*
            select * from TMP_RUNOUT_MONTH  Where ACCOUNTS_ID_ACCOUNTS In (629, 6795, 556) ;
            select * from TMP_ACC_MONTH_SALES_SUM  Where ACCOUNTS_ID_ACCOUNTS In (629, 6795, 556) Order By ACCOUNTS_ID_ACCOUNTS, FI_ANA_HISTO_DATE desc ;
            */
        bdrlb_torunoff: Begin
            -- on calcul le reste à epuiser pour pour le mois suivant le mois d'epuisement
            Drop Temporary Table If Exists TMP_OUTSTANDING_TO_RUNOF;
#             Create Temporary Table TMP_OUTSTANDING_TO_RUNOF AS
            Create Temporary Table TMP_OUTSTANDING_TO_RUNOF (
                        ACCOUNTS_ID_ACCOUNTS Int UNSIGNED Not Null
                        , FI_ANA_HISTO_DATE DATE
                        , FI_ANA_OUTSTANDING DECIMAL(19, 4)
                        , SALES_PER_MONTH DECIMAL(19, 4)
                        , OUTSTANDING_LEFT DECIMAL(19, 4)
                        , Index(ACCOUNTS_ID_ACCOUNTS, FI_ANA_HISTO_DATE)
                    )
                ;
            Insert Into TMP_OUTSTANDING_TO_RUNOF(
                     ACCOUNTS_ID_ACCOUNTS
                    , FI_ANA_HISTO_DATE
                    , FI_ANA_OUTSTANDING
                    , SALES_PER_MONTH
                    , OUTSTANDING_LEFT
                )
            Select
                TMP_ACC_MONTH_SALES_SUM.ACCOUNTS_ID_ACCOUNTS
                , TMP_ACC_MONTH_SALES_SUM.FI_ANA_HISTO_DATE
                , TMP_ACC_MONTH_SALES_SUM.FI_ANA_OUTSTANDING
                , TMP_ACC_MONTH_SALES_SUM.SALES_PER_MONTH
                , TMP_ACC_MONTH_SALES_SUM.FI_ANA_OUTSTANDING - TMP_ACC_MONTH_SALES_SUM.SALES_PER_MONTH OUTSTANDING_LEFT
--                 , TMP_RUNOUT_MONTH.*
            From TMP_ACC_MONTH_SALES_SUM
                Inner Join TMP_RUNOUT_MONTH
                    On TMP_ACC_MONTH_SALES_SUM.ACCOUNTS_ID_ACCOUNTS = TMP_RUNOUT_MONTH.ACCOUNTS_ID_ACCOUNTS
                    And TMP_ACC_MONTH_SALES_SUM.FI_ANA_HISTO_DATE Between TMP_RUNOUT_MONTH.NEXT_MONTH_BEGIN_DATE And TMP_RUNOUT_MONTH.NEXT_MONTH_END_DATE
            ;

#             Alter Table TMP_OUTSTANDING_TO_RUNOF
#                 Add Index IX_TMP_OUTSTANDING_TO_RUNOF_01(ACCOUNTS_ID_ACCOUNTS, FI_ANA_HISTO_DATE)
#             ;
            -- select * from TMP_OUTSTANDING_TO_RUNOF  Where ACCOUNTS_ID_ACCOUNTS In (629, 6795, 556)
            
        end ;
        -- calcul des ventes du derniers mois à épuiser
        bdrlb_last_sales_per_days: Begin
            Drop Temporary Table If Exists TMP_LAST_MONTH_SALES_PER_DAY1;
#             Create Temporary Table TMP_LAST_MONTH_SALES_PER_DAY1 AS
            Create Temporary Table TMP_LAST_MONTH_SALES_PER_DAY1 (
                        ACCOUNTS_ID_ACCOUNTS Int UNSIGNED Not Null
                        , ENTRIES_DATE DATE
                        , SALES DECIMAL(19, 4)

                        , Index (ACCOUNTS_ID_ACCOUNTS, ENTRIES_DATE)
                    )
                ;

            Insert Into TMP_LAST_MONTH_SALES_PER_DAY1 (
                    ACCOUNTS_ID_ACCOUNTS, ENTRIES_DATE
                    , SALES
                )
            -- explain
            Select
                eha.ACCOUNTS_ID_ACCOUNTS, ent.ENTRIES_DATE
                , sum(ent.ENTRIES_ENT_DEBIT - ent.ENTRIES_ENT_CREDIT) SALES
            From
                ENTRIES ent
                Inner Join DF_ENTRIES_TYPE det
                    On ent.DF_ENTR_TYPE_ID_DF_ENTR_TYPE = det.ID_DF_ENTRIES_TYPE
                Inner Join ENTRIES_has_ACCOUNTS eha
                    On ent.ID_ENTRIES = eha.ENTRIES_ID_ENTRIES
--                     And eha.ENTRIES_PERMANENT_ATTACHEMENT = 1
                Inner Join TMP_RUNOUT_MONTH
                    On eha.ACCOUNTS_ID_ACCOUNTS = TMP_RUNOUT_MONTH.ACCOUNTS_ID_ACCOUNTS 
                    And ent.ENTRIES_DATE Between TMP_RUNOUT_MONTH.CONSUMED_MONTH_BEGIN_DATE And TMP_RUNOUT_MONTH.CONSUMED_MONTH_END_DATE
            Where
                det.DF_ENTRIES_TYPE_CODE in ('INV', 'CRN')
                -- implicite par jointure
            Group By eha.ACCOUNTS_ID_ACCOUNTS, ent.ENTRIES_DATE
            ;
#             Alter Table TMP_LAST_MONTH_SALES_PER_DAY1
#                 Add Index IX_TMP_LAST_MONTH_SALES_PER_DAY1(ACCOUNTS_ID_ACCOUNTS, ENTRIES_DATE)
#             ;

            -- duplication de la table TMP_LAST_MONTH_SALES_PER_DAY1 en raison des limitations 
            -- mysql pour les tables temporaires
            Drop Temporary Table If Exists TMP_LAST_MONTH_SALES_PER_DAY2;
            Create Temporary Table TMP_LAST_MONTH_SALES_PER_DAY2 (
                        ACCOUNTS_ID_ACCOUNTS Int UNSIGNED Not Null
                        , ENTRIES_DATE DATE
                        , SALES DECIMAL(19, 4)

                        , Index (ACCOUNTS_ID_ACCOUNTS, ENTRIES_DATE)
                    )
                ;
            Insert Into TMP_LAST_MONTH_SALES_PER_DAY2 (
                    ACCOUNTS_ID_ACCOUNTS, ENTRIES_DATE
                    , SALES
                )
            SELECT
                ACCOUNTS_ID_ACCOUNTS, ENTRIES_DATE
                , SALES
            From TMP_LAST_MONTH_SALES_PER_DAY1
            ;
#             Create Temporary Table TMP_LAST_MONTH_SALES_PER_DAY2 AS
#             Select * from TMP_LAST_MONTH_SALES_PER_DAY1;
#             Alter Table TMP_LAST_MONTH_SALES_PER_DAY2
#                 Add Index IX_TMP_LAST_MONTH_SALES_PER_DAY2(ACCOUNTS_ID_ACCOUNTS, ENTRIES_DATE)
#             ;
        End ;
        -- select * From TMP_LAST_MONTH_SALES_PER_DAY1 Where ACCOUNTS_ID_ACCOUNTS in (629, 6795, 556) Order By ACCOUNTS_ID_ACCOUNTS, ENTRIES_DATE Desc
        bdrlb_last_month_sales_sum: Begin
            -- calcul des cumules des ventes pour le mois d'epuisement
            Drop Temporary Table If Exists TMP_LAST_MONTH_SALES_SUM;
            Create Temporary Table TMP_LAST_MONTH_SALES_SUM (
                        ACCOUNTS_ID_ACCOUNTS Int UNSIGNED Not Null
                        , ENTRIES_DATE DATE
                        , SALES DECIMAL(19, 4)
                        , NEXT_MONTH_SALES DECIMAL(19, 4)
                        , OUTSTANDING_LEFT DECIMAL(19, 4)
                        , TOT_SALES_AT_DAY_FOR_MONTH DECIMAL(19, 4)

                        , Index (ACCOUNTS_ID_ACCOUNTS, ENTRIES_DATE)
                    )
                ;
            Insert Into TMP_LAST_MONTH_SALES_SUM (
                    ACCOUNTS_ID_ACCOUNTS
                    , ENTRIES_DATE
                    , SALES
                    , NEXT_MONTH_SALES
                    , OUTSTANDING_LEFT
                    , TOT_SALES_AT_DAY_FOR_MONTH
                )
            Select
#                 TMP_LAST_MONTH_SALES_PER_DAY1.*
                TMP_LAST_MONTH_SALES_PER_DAY1.ACCOUNTS_ID_ACCOUNTS
                , TMP_LAST_MONTH_SALES_PER_DAY1.ENTRIES_DATE
                , TMP_LAST_MONTH_SALES_PER_DAY1.SALES
                , TMP_OUTSTANDING_TO_RUNOF.SALES_PER_MONTH NEXT_MONTH_SALES
                , TMP_OUTSTANDING_TO_RUNOF.OUTSTANDING_LEFT
                , (
                    Select Sum(TMP_LAST_MONTH_SALES_PER_DAY2.SALES) 
                    From TMP_LAST_MONTH_SALES_PER_DAY2
                    Where TMP_LAST_MONTH_SALES_PER_DAY1.ACCOUNTS_ID_ACCOUNTS = TMP_LAST_MONTH_SALES_PER_DAY2.ACCOUNTS_ID_ACCOUNTS
                        And TMP_LAST_MONTH_SALES_PER_DAY1.ENTRIES_DATE <= TMP_LAST_MONTH_SALES_PER_DAY2.ENTRIES_DATE
                    Group By TMP_LAST_MONTH_SALES_PER_DAY2.ACCOUNTS_ID_ACCOUNTS
                ) TOT_SALES_AT_DAY_FOR_MONTH
            From TMP_LAST_MONTH_SALES_PER_DAY1
                Inner Join TMP_OUTSTANDING_TO_RUNOF
                    On TMP_LAST_MONTH_SALES_PER_DAY1.ACCOUNTS_ID_ACCOUNTS = TMP_OUTSTANDING_TO_RUNOF.ACCOUNTS_ID_ACCOUNTS 
            ;
#             Alter Table TMP_LAST_MONTH_SALES_SUM
#                 Add Index IX_TMP_LAST_MONTH_SALES_SUM_01(ACCOUNTS_ID_ACCOUNTS, ENTRIES_DATE)
#             ;
        End ;
            -- select * from TMP_LAST_MONTH_SALES_SUM Where ACCOUNTS_ID_ACCOUNTS in (629, 6795, 556);

            -- TMP_LAST_MONTH_SALES_SUM contient les cumul par jour et par client des ventes pour 
            -- le derier mois à épuiser

        bdrlb_runoffday: Begin
            -- recherche du jour d'épuisement de l'encour
            Drop Temporary Table If Exists TMP_RUNOUT_DATES;
#             Create Temporary Table TMP_RUNOUT_DATES AS
            Create Temporary Table TMP_RUNOUT_DATES (
                        ACCOUNTS_ID_ACCOUNTS Int UNSIGNED Not Null
                        , RUN_OUT_DAY DATE
                        , Index (ACCOUNTS_ID_ACCOUNTS)
                    )
                ;
            Insert Into TMP_RUNOUT_DATES (
                ACCOUNTS_ID_ACCOUNTS, RUN_OUT_DAY
            )
            Select
                ACCOUNTS_ID_ACCOUNTS, Max(ENTRIES_DATE) RUN_OUT_DAY
            From TMP_LAST_MONTH_SALES_SUM
            Where
                TOT_SALES_AT_DAY_FOR_MONTH >= OUTSTANDING_LEFT
            Group By ACCOUNTS_ID_ACCOUNTS
            ;
#             Alter Table TMP_RUNOUT_DATES
#                 Add Index IX_TMP_RUNOUT_DATES_01(ACCOUNTS_ID_ACCOUNTS)
#             ;
        End ;
        bdrlb_updatingfiana: Begin
            Update FI_ANA
                Left Join TMP_ACC_OUTSTANDING
                    On FI_ANA.ACCOUNTS_ID_ACCOUNTS = TMP_ACC_OUTSTANDING.ACCOUNTS_ID_ACCOUNTS 
                Left Join TMP_RUNOUT_DATES
                    On TMP_ACC_OUTSTANDING.ACCOUNTS_ID_ACCOUNTS = TMP_RUNOUT_DATES.ACCOUNTS_ID_ACCOUNTS 
            Set 
                    FI_ANA.FI_ANA_DSO = Cast(Case
                        When TMP_ACC_OUTSTANDING.FI_ANA_OUTSTANDING = 0 Then
                            0
                        Else
                            Least(Case When TMP_RUNOUT_DATES.RUN_OUT_DAY Is Null then @iDSO_maxDso Else Datediff(@dtDSO_refdate, TMP_RUNOUT_DATES.RUN_OUT_DAY)  End, @iDSO_maxDso)
                    End AS Signed)
                    , FI_ANA.FI_ANA_DSO_LAST_UPDAT = @dtDSO_refdate
            -- implicite par jointure
                ;
--             Select FI_ANA_OUTSTANDING, FI_ANA_DSO, FI_ANA_DSO_LAST_UPDAT from  FI_ANA  Where ACCOUNTS_ID_ACCOUNTS In (629, 6795, 556);

            /*
            -- resultat pour debug
            Select
                TMP_ACC_OUTSTANDING.*
                , TMP_RUNOUT_DATES.*
                , Cast( Case When TMP_ACC_OUTSTANDING.FI_ANA_OUTSTANDING = 0 Then
                        0
                    Else 
                        Least(Case When TMP_RUNOUT_DATES.RUN_OUT_DAY Is Null then @iDSO_maxDso Else Datediff(@dtDSO_refdate, TMP_RUNOUT_DATES.RUN_OUT_DAY)  End, @iDSO_maxDso)
                    End
                    
                  as SIGNED) DSORB
            From TMP_ACC_OUTSTANDING
                Left Join TMP_RUNOUT_DATES
                    On TMP_ACC_OUTSTANDING.ACCOUNTS_ID_ACCOUNTS = TMP_RUNOUT_DATES.ACCOUNTS_ID_ACCOUNTS 
            Where
                TMP_ACC_OUTSTANDING.FI_ANA_OUTSTANDING <> 0
                And TMP_ACC_OUTSTANDING.ACCOUNTS_ID_ACCOUNTS In (629, 6795, 556)
            ;


            */
            if @iDSO_toHisto = 1 then
--                 select 'update dso', @dtDSO_refdate;
                UPDATE 
                    FI_ANA_HISTO
                    Inner Join FI_ANA
                        On FI_ANA_HISTO.ACCOUNTS_ID_ACCOUNTS = FI_ANA.ACCOUNTS_ID_ACCOUNTS
                        And FI_ANA_HISTO.FI_ANA_HISTO_DATE = FI_ANA.FI_ANA_DSO_LAST_UPDAT
                Set 
                    FI_ANA_HISTO.FI_ANA_DSO = FI_ANA.FI_ANA_DSO 

                Where
                    FI_ANA_HISTO.FI_ANA_HISTO_DATE = @dtDSO_refdate
                    -- impliite par jointure
                ;
--              Select @dtDSO_refdate, ACCOUNTS_ID_ACCOUNTS, FI_ANA_HISTO_DATE, FI_ANA_OUTSTANDING, FI_ANA_DSO from  FI_ANA_HISTO  Where ACCOUNTS_ID_ACCOUNTS In (629, 6795, 556) Order By ACCOUNTS_ID_ACCOUNTS, FI_ANA_HISTO_DATE Desc ;

                /*
                Select
                    FI_ANA_HISTO.ACCOUNTS_ID_ACCOUNTS
                    , FI_ANA_HISTO.FI_ANA_HISTO_DATE, FI_ANA.FI_ANA_DSO_LAST_UPDAT
                    , FI_ANA_HISTO.FI_ANA_OUTSTANDING, FI_ANA.FI_ANA_OUTSTANDING
                    , FI_ANA_HISTO.FI_ANA_DSO, FI_ANA.FI_ANA_DSO
                From
                    FI_ANA_HISTO
                    Inner Join FI_ANA
                        On FI_ANA_HISTO.ACCOUNTS_ID_ACCOUNTS = FI_ANA.ACCOUNTS_ID_ACCOUNTS
--                         And FI_ANA_HISTO.FI_ANA_HISTO_DATE = FI_ANA.FI_ANA_DSO_LAST_UPDAT
                Where 1 = 1
                    And FI_ANA_HISTO.ACCOUNTS_ID_ACCOUNTS In (629, 6795, 556)
                    And FI_ANA_HISTO.FI_ANA_HISTO_DATE = @dtDSO_refdate
                ;

                    */
--             else 
--                 select 'not update histo';
            End if;

        End ;

        bdrlb_clear: Begin
            if @bdrlb_debugMode <> 1 Then
--                 select 'drop table', @bdrlb_debugMode;
                Drop Temporary Table If Exists TMP_ACC_OUTSTANDING;
                Drop Temporary Table If Exists TMP_ACC_MONTH_SALES_SUM ;
                Drop Temporary Table If Exists TMP_RUNOUT_MONTH;
                Drop Temporary Table If Exists TMP_OUTSTANDING_TO_RUNOF;
                Drop Temporary Table If Exists TMP_LAST_MONTH_SALES_PER_DAY1;
                Drop Temporary Table If Exists TMP_LAST_MONTH_SALES_PER_DAY2;
                Drop Temporary Table If Exists TMP_LAST_MONTH_SALES_SUM;
                Drop Temporary Table If Exists TMP_RUNOUT_DATES;

            End if;
        End ;

End $$

Delimiter ;