/**
 * compute BUSINESS_LATE_INTEREST_PREPARE_DATA
 * @author Armen
 * ne pas changer le delimiter
 */ 
Delimiter !
Drop Procedure if Exists BUSINESS_LATE_INTEREST_CALCULATION !
Create Procedure BUSINESS_LATE_INTEREST_CALCULATION()
Begin
    main: begin
        prepareRates: Begin
            
            -- table temporaire pour les taux
            orderRates: begin
                -- on classe les taux pour faciliter les requete
                -- on a besoin de l'autoincrement
                Drop temporary table if exists TMP_LATE_INTEREST_RATES_ORDER;
                Create temporary table TMP_LATE_INTEREST_RATES_ORDER (
                    TMP_LATE_INTEREST_RATES_ORDER__ID Int Unsigned Not Null Primary Key AUTO_INCREMENT
                    , TMP_LATE_INTEREST_RATES_ORDER__LATE_INTEREST_NAMEID Int Unsigned Not Null 
                    , TMP_LATE_INTEREST_RATES_ORDER__DATE DATE
                    , TMP_LATE_INTEREST_RATES_ORDER__RATE DECIMAL(5,5)
                    , Unique Key (TMP_LATE_INTEREST_RATES_ORDER__LATE_INTEREST_NAMEID
                                    , TMP_LATE_INTEREST_RATES_ORDER__DATE
                                ) 
                );
                Insert Into TMP_LATE_INTEREST_RATES_ORDER (
                    TMP_LATE_INTEREST_RATES_ORDER__LATE_INTEREST_NAMEID
                    , TMP_LATE_INTEREST_RATES_ORDER__DATE
                    , TMP_LATE_INTEREST_RATES_ORDER__RATE
                )
                -- explain
                Select
                    TMP_LATE_INTEREST_RATES__LATE_INTEREST_NAMEID
                    , TMP_LATE_INTEREST_RATES__DATE
                    , TMP_LATE_INTEREST_RATES__RATE 
                From
                   TMP_LATE_INTEREST_RATES
                Order By
                    TMP_LATE_INTEREST_RATES__LATE_INTEREST_NAMEID
                    , TMP_LATE_INTEREST_RATES__DATE
                ;

                Delete TMP_LATE_INTEREST_RATES From TMP_LATE_INTEREST_RATES;
--                 Truncate Table TMP_LATE_INTEREST_RATES; -- !! commit implicite
                Insert Into TMP_LATE_INTEREST_RATES (
                    TMP_LATE_INTEREST_RATES__ID
                    , TMP_LATE_INTEREST_RATES__LATE_INTEREST_NAMEID
                    , TMP_LATE_INTEREST_RATES__DATE
                    , TMP_LATE_INTEREST_RATES__RATE 
                )
                Select 
                    TMP_LATE_INTEREST_RATES_ORDER__ID 
                    , TMP_LATE_INTEREST_RATES_ORDER__LATE_INTEREST_NAMEID 
                    , TMP_LATE_INTEREST_RATES_ORDER__DATE 
                    , TMP_LATE_INTEREST_RATES_ORDER__RATE 
                From TMP_LATE_INTEREST_RATES_ORDER
                ;
            end;

            -- table temporaire avec les dates de début et de fin
            --
            Drop temporary table if exists TMP_LATE_INTEREST_RATES_DATES;
            Create temporary table TMP_LATE_INTEREST_RATES_DATES (
                TMP_LATE_INTEREST_RATES_DATES__LATE_INTEREST_NAMEID Int unsigned not null
                , TMP_LATE_INTEREST_RATES_DATES__BDATE DATE
                , TMP_LATE_INTEREST_RATES_DATES__EDATE DATE
                , TMP_LATE_INTEREST_RATES_DATES__RATE DECIMAL(5,5)
                , index (TMP_LATE_INTEREST_RATES_DATES__LATE_INTEREST_NAMEID, TMP_LATE_INTEREST_RATES_DATES__BDATE)
            );
            Insert Into TMP_LATE_INTEREST_RATES_DATES (
                TMP_LATE_INTEREST_RATES_DATES__LATE_INTEREST_NAMEID
                , TMP_LATE_INTEREST_RATES_DATES__BDATE
                , TMP_LATE_INTEREST_RATES_DATES__EDATE
                , TMP_LATE_INTEREST_RATES_DATES__RATE
            )
            -- explain
            Select
                TRB.TMP_LATE_INTEREST_RATES__LATE_INTEREST_NAMEID
                , TRB.TMP_LATE_INTEREST_RATES__DATE bd
                , Coalesce(adddate(TRE.TMP_LATE_INTEREST_RATES__DATE, -1), current_date) ed
                , TRB.TMP_LATE_INTEREST_RATES__RATE
            From
                TMP_LATE_INTEREST_RATES TRB
                LEFT Join TMP_LATE_INTEREST_RATES TRE
                    On TRB.TMP_LATE_INTEREST_RATES__LATE_INTEREST_NAMEID = TRE.TMP_LATE_INTEREST_RATES__LATE_INTEREST_NAMEID
                    And  TRB.TMP_LATE_INTEREST_RATES__ID+1 = TRE.TMP_LATE_INTEREST_RATES__ID
            ;

        end;
        
        prepareData: begin
            orderData: begin
--                 drop temporary table if exists tmp_late_interest_order;
--                 Create temporary table tmp_late_interest_order as
--                 select * from TMP_LATE_INTEREST_SRC_DATA;
--                 truncate table TMP_LATE_INTEREST_SRC_DATA;
--                 insert into TMP_LATE_INTEREST_SRC_DATA (
--                     TMP_LATE_INTEREST_SRC_DATA__DATAID, TMP_LATE_INTEREST_SRC_DATA__TMP_LATE_INTEREST_TRG_DATAID
--                     , TMP_LATE_INTEREST_SRC_DATA__DATE, TMP_LATE_INTEREST_SRC_DATA__AMOUNT
-- 
--                 )
--                 Select 
--                     TMP_LATE_INTEREST_SRC_DATA__DATAID, TMP_LATE_INTEREST_SRC_DATA__TMP_LATE_INTEREST_TRG_DATAID
--                     , TMP_LATE_INTEREST_SRC_DATA__DATE, TMP_LATE_INTEREST_SRC_DATA__AMOUNT
--                 From tmp_late_interest_order 
--                 Order by TMP_LATE_INTEREST_SRC_DATA__TMP_LATE_INTEREST_TRG_DATAID, TMP_LATE_INTEREST_SRC_DATA__DATE
--                 ;
--                 drop temporary table if exists tmp_late_interest_order;
                drop temporary table if exists TMP_LATE_INTEREST_SRC_DATA_ORDERED;
                Create temporary table TMP_LATE_INTEREST_SRC_DATA_ORDERED  (
                    -- structure  iso TMP_LATE_INTEREST_SRC_DATA sauf index
                            TMP_LATE_INTEREST_SRC_DATA__ID Int Unsigned Not Null Primary Key AUTO_INCREMENT
                            , TMP_LATE_INTEREST_SRC_DATA__DATAID Int Unsigned Not Null -- id de mouvement
                            , TMP_LATE_INTEREST_SRC_DATA__TMP_LATE_INTEREST_TRG_DATAID Int Unsigned Not Null
                            , TMP_LATE_INTEREST_SRC_DATA__DATE DATE
                            , TMP_LATE_INTEREST_SRC_DATA__AMOUNT DECIMAL(30,5)
                            , TMP_LATE_INTEREST_SRC_DATA__ALLOW_NEGATIVE_PENALITY Smallint Not Null Default 0
                        )
                    ;
                insert into TMP_LATE_INTEREST_SRC_DATA_ORDERED (
                    TMP_LATE_INTEREST_SRC_DATA__DATAID, TMP_LATE_INTEREST_SRC_DATA__TMP_LATE_INTEREST_TRG_DATAID
                    , TMP_LATE_INTEREST_SRC_DATA__DATE, TMP_LATE_INTEREST_SRC_DATA__AMOUNT

                )
                Select 
                    TMP_LATE_INTEREST_SRC_DATA__DATAID, TMP_LATE_INTEREST_SRC_DATA__TMP_LATE_INTEREST_TRG_DATAID
                    , TMP_LATE_INTEREST_SRC_DATA__DATE, TMP_LATE_INTEREST_SRC_DATA__AMOUNT
                From TMP_LATE_INTEREST_SRC_DATA 
                Order by TMP_LATE_INTEREST_SRC_DATA__TMP_LATE_INTEREST_TRG_DATAID, TMP_LATE_INTEREST_SRC_DATA__DATE
                ;
                
                Delete TMP_LATE_INTEREST_SRC_DATA From TMP_LATE_INTEREST_SRC_DATA;

                Insert Into TMP_LATE_INTEREST_SRC_DATA (
                    TMP_LATE_INTEREST_SRC_DATA__ID, TMP_LATE_INTEREST_SRC_DATA__DATAID
                    , TMP_LATE_INTEREST_SRC_DATA__TMP_LATE_INTEREST_TRG_DATAID, TMP_LATE_INTEREST_SRC_DATA__DATE
                    , TMP_LATE_INTEREST_SRC_DATA__AMOUNT, TMP_LATE_INTEREST_SRC_DATA__ALLOW_NEGATIVE_PENALITY
                )
                Select 
                    TMP_LATE_INTEREST_SRC_DATA__ID, TMP_LATE_INTEREST_SRC_DATA__DATAID
                    , TMP_LATE_INTEREST_SRC_DATA__TMP_LATE_INTEREST_TRG_DATAID, TMP_LATE_INTEREST_SRC_DATA__DATE
                    , TMP_LATE_INTEREST_SRC_DATA__AMOUNT, TMP_LATE_INTEREST_SRC_DATA__ALLOW_NEGATIVE_PENALITY
                From TMP_LATE_INTEREST_SRC_DATA_ORDERED
                ;
                drop temporary table if exists TMP_LATE_INTEREST_SRC_DATA_ORDERED;
            end;

            -- les mouvement par périodes
            drop temporary table if exists tmp_interest_amount_sum;
            create temporary table tmp_interest_amount_sum (
                SRC_DATA__ID Int unsigned not null primary key
--                 , amount_sum DECIMAL(30,5) Not Null Default 0 -- pourquoi ????
                , amount_sum DECIMAL(19, 4) Not Null Default 0
            )
            ;
            insert into tmp_interest_amount_sum (SRC_DATA__ID, amount_sum)
            -- explain
            select 
                src1.TMP_LATE_INTEREST_SRC_DATA__ID sid
--                 TMP_LATE_INTEREST_SRC_DATA__TMP_LATE_INTEREST_TRG_DATAID tid
                ,(  
                    Select sum(src2.TMP_LATE_INTEREST_SRC_DATA__AMOUNT)
                    From TMP_LATE_INTEREST_SRC_DATA src2
                    Where src2.TMP_LATE_INTEREST_SRC_DATA__TMP_LATE_INTEREST_TRG_DATAID = src1.TMP_LATE_INTEREST_SRC_DATA__TMP_LATE_INTEREST_TRG_DATAID
                        and src2.TMP_LATE_INTEREST_SRC_DATA__ID <= src1.TMP_LATE_INTEREST_SRC_DATA__ID
                    Group by src2.TMP_LATE_INTEREST_SRC_DATA__TMP_LATE_INTEREST_TRG_DATAID
                ) totamount
            from 
                TMP_LATE_INTEREST_SRC_DATA src1
            ;

        -- ----------------------------------------------------------------------
        -- ----------------------------------------------------------------------
        -- remplissage tables des taux personnalisé    
        -- ----------------------------------------------------------------------
        -- ----------------------------------------------------------------------
            dataRates: begin
                -- table temporaire des taux spécifique a chaque elements
                -- pour le remplir a cause de limitationm mysql
                -- on va utiliser 2 tables tmp suplémetaire casi identique
                -- idem TMP_LATE_INTEREST_RATES_DATA + DATEID
                Drop temporary table if exists TMP_LATE_INTEREST_RATES_DATA;
                Create temporary table TMP_LATE_INTEREST_RATES_DATA (
                    TMP_LATE_INTEREST_RATES_DATA__ID Int unsigned not null Primary Key auto_increment
                    , TMP_LATE_INTEREST_RATES_DATA__LATE_INTEREST_NAMEID Int unsigned not null
                    , TMP_LATE_INTEREST_RATES_DATA__LATE_INTEREST_DATAID Int unsigned not null
                    , TMP_LATE_INTEREST_RATES_DATA__LATE_INTEREST_TRG_DATAID Int unsigned not null
                    , TMP_LATE_INTEREST_RATES_DATA__BDATE DATE
                    , TMP_LATE_INTEREST_RATES_DATA__EDATE DATE
                    , TMP_LATE_INTEREST_RATES_DATA__DATA_DATE DATE
                    , TMP_LATE_INTEREST_RATES_DATA__DATA_NEXTDATE DATE
                    , TMP_LATE_INTEREST_RATES_DATA__RATE DECIMAL(5,5)
                    , index (TMP_LATE_INTEREST_RATES_DATA__LATE_INTEREST_NAMEID
                            , TMP_LATE_INTEREST_RATES_DATA__BDATE
                            , TMP_LATE_INTEREST_RATES_DATA__EDATE
                        )
                    , index(TMP_LATE_INTEREST_RATES_DATA__DATA_DATE)
                    , index(TMP_LATE_INTEREST_RATES_DATA__LATE_INTEREST_DATAID)
                    , index(TMP_LATE_INTEREST_RATES_DATA__LATE_INTEREST_TRG_DATAID)
                );



                Insert Into TMP_LATE_INTEREST_RATES_DATA (
                    TMP_LATE_INTEREST_RATES_DATA__LATE_INTEREST_NAMEID 
                    , TMP_LATE_INTEREST_RATES_DATA__LATE_INTEREST_DATAID 
                    , TMP_LATE_INTEREST_RATES_DATA__LATE_INTEREST_TRG_DATAID 
                    , TMP_LATE_INTEREST_RATES_DATA__BDATE 
                    , TMP_LATE_INTEREST_RATES_DATA__EDATE 
                    , TMP_LATE_INTEREST_RATES_DATA__DATA_DATE 
                    , TMP_LATE_INTEREST_RATES_DATA__RATE 
                )


                select 
                    trgt.TMP_LATE_INTEREST_TRG_DATA__LATE_INTEREST_NAMEID
                    , src.TMP_LATE_INTEREST_SRC_DATA__DATAID
                    , trgt.TMP_LATE_INTEREST_TRG_DATA__DATAID
                    , Coalesce(greatest(TMP_LATE_INTEREST_RATES_DATES__BDATE, src.TMP_LATE_INTEREST_SRC_DATA__DATE), src.TMP_LATE_INTEREST_SRC_DATA__DATE) bd1       
                    , Coalesce(least(src_next.TMP_LATE_INTEREST_SRC_DATA__DATE, rtdt.TMP_LATE_INTEREST_RATES_DATES__EDATE ), rtdt.TMP_LATE_INTEREST_RATES_DATES__EDATE) ed1
					, Coalesce(greatest(TMP_LATE_INTEREST_RATES_DATES__BDATE, src.TMP_LATE_INTEREST_SRC_DATA__DATE), src.TMP_LATE_INTEREST_SRC_DATA__DATE) bd2       
--                     , src.TMP_LATE_INTEREST_SRC_DATA__DATE
                    , rtdt.TMP_LATE_INTEREST_RATES_DATES__RATE 

                from
                    TMP_LATE_INTEREST_TRG_DATA trgt
                    Inner Join TMP_LATE_INTEREST_SRC_DATA src
                        On src.TMP_LATE_INTEREST_SRC_DATA__TMP_LATE_INTEREST_TRG_DATAID = trgt.TMP_LATE_INTEREST_TRG_DATA__ID
					Left Join TMP_LATE_INTEREST_SRC_DATA src_next
						On src.TMP_LATE_INTEREST_SRC_DATA__TMP_LATE_INTEREST_TRG_DATAID = src_next.TMP_LATE_INTEREST_SRC_DATA__TMP_LATE_INTEREST_TRG_DATAID
						And src.TMP_LATE_INTEREST_SRC_DATA__ID = src_next.TMP_LATE_INTEREST_SRC_DATA__ID -1
                    Left Join TMP_LATE_INTEREST_RATES_DATES rtdt
                        On trgt.TMP_LATE_INTEREST_TRG_DATA__LATE_INTEREST_NAMEID = rtdt.TMP_LATE_INTEREST_RATES_DATES__LATE_INTEREST_NAMEID
						And src.TMP_LATE_INTEREST_SRC_DATA__DATE  <= rtdt.TMP_LATE_INTEREST_RATES_DATES__EDATE 
						And Coalesce(src_next.TMP_LATE_INTEREST_SRC_DATA__DATE, rtdt.TMP_LATE_INTEREST_RATES_DATES__EDATE )  > rtdt.TMP_LATE_INTEREST_RATES_DATES__BDATE 
                Order by src.TMP_LATE_INTEREST_SRC_DATA__TMP_LATE_INTEREST_TRG_DATAID, src.TMP_LATE_INTEREST_SRC_DATA__ID, bd1
                ;


            end;

            -- 
            -- on doit classer les evenements
            --  => ecriture / modif taux en fonction de leur date d'apparition
            -- puis on doit determiner la date de fin
            -- ce qui correspond à la date de début de l'evenement suivant
            Drop temporary table if exists tmp_date_sorter1;
--             Create temporary table tmp_date_sorter1 As 
--             select * from TMP_LATE_INTEREST_RATES_DATA order by  TMP_LATE_INTEREST_RATES_DATA__ID
--             ;
-- 
--             Alter table tmp_date_sorter1 add TMP_LATE_INTEREST_RATES__TMPID Int Not Null Primary Key Auto_increment;
-- --             Create index ix_tmp_date_sorter1_0 on tmp_date_sorter1(TMP_LATE_INTEREST_RATES_DATA__LATE_INTEREST_DATAID);
--             Create index ix_tmp_date_sorter1_0 on tmp_date_sorter1(TMP_LATE_INTEREST_RATES_DATA__LATE_INTEREST_TRG_DATAID);
--             Create unique index ix_tmp_date_sorter1_1 on tmp_date_sorter1(TMP_LATE_INTEREST_RATES_DATA__ID);

            Create temporary table tmp_date_sorter1 (
                        -- colonne iso TMP_LATE_INTEREST_RATES_DATA - index + new PK auto-inc
                        TMP_LATE_INTEREST_RATES__TMPID Int unsigned not null Primary Key auto_increment
                        , TMP_LATE_INTEREST_RATES_DATA__ID Int unsigned not null 
                        , TMP_LATE_INTEREST_RATES_DATA__LATE_INTEREST_NAMEID Int unsigned not null
                        , TMP_LATE_INTEREST_RATES_DATA__LATE_INTEREST_DATAID Int unsigned not null
                        , TMP_LATE_INTEREST_RATES_DATA__LATE_INTEREST_TRG_DATAID Int unsigned not null
                        , TMP_LATE_INTEREST_RATES_DATA__BDATE DATE
                        , TMP_LATE_INTEREST_RATES_DATA__EDATE DATE
                        , TMP_LATE_INTEREST_RATES_DATA__DATA_DATE DATE
                        , TMP_LATE_INTEREST_RATES_DATA__DATA_NEXTDATE DATE
                        , TMP_LATE_INTEREST_RATES_DATA__RATE DECIMAL(5,5)
                        , index(TMP_LATE_INTEREST_RATES_DATA__LATE_INTEREST_TRG_DATAID)
                        , index(TMP_LATE_INTEREST_RATES_DATA__LATE_INTEREST_DATAID)
                        , Unique Key (TMP_LATE_INTEREST_RATES_DATA__ID)   
                    )
                ;
            Insert Into tmp_date_sorter1 (
                     TMP_LATE_INTEREST_RATES_DATA__ID , TMP_LATE_INTEREST_RATES_DATA__LATE_INTEREST_NAMEID 
                    , TMP_LATE_INTEREST_RATES_DATA__LATE_INTEREST_DATAID , TMP_LATE_INTEREST_RATES_DATA__LATE_INTEREST_TRG_DATAID 
                    , TMP_LATE_INTEREST_RATES_DATA__BDATE , TMP_LATE_INTEREST_RATES_DATA__EDATE 
                    , TMP_LATE_INTEREST_RATES_DATA__DATA_DATE , TMP_LATE_INTEREST_RATES_DATA__DATA_NEXTDATE 
                    , TMP_LATE_INTEREST_RATES_DATA__RATE 
                )
            Select
                    TMP_LATE_INTEREST_RATES_DATA__ID , TMP_LATE_INTEREST_RATES_DATA__LATE_INTEREST_NAMEID 
                    , TMP_LATE_INTEREST_RATES_DATA__LATE_INTEREST_DATAID , TMP_LATE_INTEREST_RATES_DATA__LATE_INTEREST_TRG_DATAID 
                    , TMP_LATE_INTEREST_RATES_DATA__BDATE , TMP_LATE_INTEREST_RATES_DATA__EDATE 
                    , TMP_LATE_INTEREST_RATES_DATA__DATA_DATE , TMP_LATE_INTEREST_RATES_DATA__DATA_NEXTDATE 
                    , TMP_LATE_INTEREST_RATES_DATA__RATE 

            From TMP_LATE_INTEREST_RATES_DATA
            order by  TMP_LATE_INTEREST_RATES_DATA__ID
            ;



            Drop temporary table if exists tmp_date_sorter2;

--             Create temporary table tmp_date_sorter2 As 
--             select * from tmp_date_sorter1 order by TMP_LATE_INTEREST_RATES_DATA__ID;
-- --             Create index ix_tmp_date_sorter2_0 on tmp_date_sorter2(TMP_LATE_INTEREST_RATES_DATA__LATE_INTEREST_DATAID);
--             Create index ix_tmp_date_sorter2_0 on tmp_date_sorter2(TMP_LATE_INTEREST_RATES_DATA__LATE_INTEREST_TRG_DATAID);
--             Create index ix_tmp_date_sorter2_1 on tmp_date_sorter2(TMP_LATE_INTEREST_RATES__TMPID);

            Create temporary table tmp_date_sorter2 (
                        TMP_LATE_INTEREST_RATES__TMPID Int unsigned not null 
                        , TMP_LATE_INTEREST_RATES_DATA__ID Int unsigned not null 
                        , TMP_LATE_INTEREST_RATES_DATA__LATE_INTEREST_NAMEID Int unsigned not null
                        , TMP_LATE_INTEREST_RATES_DATA__LATE_INTEREST_DATAID Int unsigned not null
                        , TMP_LATE_INTEREST_RATES_DATA__LATE_INTEREST_TRG_DATAID Int unsigned not null
                        , TMP_LATE_INTEREST_RATES_DATA__BDATE DATE
                        , TMP_LATE_INTEREST_RATES_DATA__EDATE DATE
                        , TMP_LATE_INTEREST_RATES_DATA__DATA_DATE DATE
                        , TMP_LATE_INTEREST_RATES_DATA__DATA_NEXTDATE DATE
                        , TMP_LATE_INTEREST_RATES_DATA__RATE DECIMAL(5,5)

                        , index(TMP_LATE_INTEREST_RATES_DATA__LATE_INTEREST_TRG_DATAID)
                        , index(TMP_LATE_INTEREST_RATES__TMPID)
                    )
                ;

            Insert Into tmp_date_sorter2 (
                        TMP_LATE_INTEREST_RATES__TMPID , TMP_LATE_INTEREST_RATES_DATA__ID 
                        , TMP_LATE_INTEREST_RATES_DATA__LATE_INTEREST_NAMEID , TMP_LATE_INTEREST_RATES_DATA__LATE_INTEREST_DATAID 
                        , TMP_LATE_INTEREST_RATES_DATA__LATE_INTEREST_TRG_DATAID , TMP_LATE_INTEREST_RATES_DATA__BDATE 
                        , TMP_LATE_INTEREST_RATES_DATA__EDATE , TMP_LATE_INTEREST_RATES_DATA__DATA_DATE 
                        , TMP_LATE_INTEREST_RATES_DATA__DATA_NEXTDATE , TMP_LATE_INTEREST_RATES_DATA__RATE 
                    )
            Select
                TMP_LATE_INTEREST_RATES__TMPID , TMP_LATE_INTEREST_RATES_DATA__ID 
                , TMP_LATE_INTEREST_RATES_DATA__LATE_INTEREST_NAMEID , TMP_LATE_INTEREST_RATES_DATA__LATE_INTEREST_DATAID 
                , TMP_LATE_INTEREST_RATES_DATA__LATE_INTEREST_TRG_DATAID , TMP_LATE_INTEREST_RATES_DATA__BDATE 
                , TMP_LATE_INTEREST_RATES_DATA__EDATE , TMP_LATE_INTEREST_RATES_DATA__DATA_DATE 
                , TMP_LATE_INTEREST_RATES_DATA__DATA_NEXTDATE , TMP_LATE_INTEREST_RATES_DATA__RATE 
            From tmp_date_sorter1 
            Order By TMP_LATE_INTEREST_RATES_DATA__ID
            ;


            Update 
--             -- -- pour debug
--             Select
--                  t1.TMP_LATE_INTEREST_RATES_DATA__LATE_INTEREST_NAMEID 
--                 , t1.TMP_LATE_INTEREST_RATES_DATA__LATE_INTEREST_DATAID
--                 , t2.TMP_LATE_INTEREST_RATES_DATA__LATE_INTEREST_DATAID
--                 , t1.TMP_LATE_INTEREST_RATES_DATA__BDATE 
--                 , t2.TMP_LATE_INTEREST_RATES_DATA__BDATE
--                 , least(t1.TMP_LATE_INTEREST_RATES_DATA__EDATE , Coalesce(t2.TMP_LATE_INTEREST_RATES_DATA__BDATE, t1.TMP_LATE_INTEREST_RATES_DATA__EDATE )) dddd
--                 , t1.TMP_LATE_INTEREST_RATES_DATA__EDATE 
--                 , t1.TMP_LATE_INTEREST_RATES_DATA__DATA_DATE 
--                 , t1.TMP_LATE_INTEREST_RATES_DATA__RATE 
-- --                 , t2.TMP_LATE_INTEREST_RATES_DATA__BDATE 
-- --                 , t2.TMP_LATE_INTEREST_RATES_DATA__EDATE 
--             From
                TMP_LATE_INTEREST_RATES_DATA trg
                Inner Join tmp_date_sorter1 t1
                    On trg.TMP_LATE_INTEREST_RATES_DATA__ID = t1.TMP_LATE_INTEREST_RATES_DATA__ID
                Left Join tmp_date_sorter2 t2
                        On t1.TMP_LATE_INTEREST_RATES_DATA__LATE_INTEREST_TRG_DATAID = t2.TMP_LATE_INTEREST_RATES_DATA__LATE_INTEREST_TRG_DATAID
                        And t1.TMP_LATE_INTEREST_RATES__TMPID = t2.TMP_LATE_INTEREST_RATES__TMPID-1
            Set trg.TMP_LATE_INTEREST_RATES_DATA__DATA_NEXTDATE = least(t1.TMP_LATE_INTEREST_RATES_DATA__EDATE , Coalesce(t2.TMP_LATE_INTEREST_RATES_DATA__BDATE, t1.TMP_LATE_INTEREST_RATES_DATA__EDATE ))
            ;
            
        end;
        
        -- ----------------------------------------------------------------------
        -- ----------------------------------------------------------------------
        -- filtrer les date de taux par mouvements
        -- ----------------------------------------------------------------------
        -- ----------------------------------------------------------------------

        calculteInterest: begin
            
			-- contrainte de performance
			-- mysql ne peux plus utiliser les indexes apre une aggregation
			-- => on utilise une table temporaire avec les valeurs calculées et une colonne indéxée pour
			--	  améliorer les perfs de mise à jour des données


			Drop Temporary Table if exists TMP_TMP_LATE_INTEREST_RESULTS;
-- 			Create Temporary Table TMP_TMP_LATE_INTEREST_RESULTS AS
			Create Temporary Table TMP_TMP_LATE_INTEREST_RESULTS (  
                        NAMEID Int Unsigned
                        , TRG_DATAID Int Unsigned 
                        , mp Decimal(19,4)
                        , nbperiode int

                        , Index(TRG_DATAID)
                        , Index(NAMEID)
                    )
            ;

            Insert Into TMP_TMP_LATE_INTEREST_RESULTS (
                        NAMEID, TRG_DATAID , mp , nbperiode
                )
			select 
				trgt.TMP_LATE_INTEREST_TRG_DATA__LATE_INTEREST_NAMEID NAMEID
				, src.TMP_LATE_INTEREST_SRC_DATA__TMP_LATE_INTEREST_TRG_DATAID TRG_DATAID
				, Round(
                    sum(
                        Case When srcamounts.amount_sum < 0 And src.TMP_LATE_INTEREST_SRC_DATA__ALLOW_NEGATIVE_PENALITY = 0 Then
                            -- pénalités négatives (avoir) non authorisées
                            0
                        Else
                                srcamounts.amount_sum 
                                * DateDiff(rtdt.TMP_LATE_INTEREST_RATES_DATA__DATA_NEXTDATE, rtdt.TMP_LATE_INTEREST_RATES_DATA__DATA_DATE) 
                                / 365 
                                * rtdt.TMP_LATE_INTEREST_RATES_DATA__RATE 
                        End
                    ) 
                    , 4
				) mp
				, count(rtdt.TMP_LATE_INTEREST_RATES_DATA__LATE_INTEREST_NAMEID) nbperiode
			from
				TMP_LATE_INTEREST_SRC_DATA src
				Inner Join tmp_interest_amount_sum srcamounts
					On src.TMP_LATE_INTEREST_SRC_DATA__ID = srcamounts.SRC_DATA__ID -- OK
				Inner Join TMP_LATE_INTEREST_TRG_DATA trgt
					On src.TMP_LATE_INTEREST_SRC_DATA__TMP_LATE_INTEREST_TRG_DATAID = trgt.TMP_LATE_INTEREST_TRG_DATA__ID
				Inner Join TMP_LATE_INTEREST_RATES_DATA rtdt
					On trgt.TMP_LATE_INTEREST_TRG_DATA__LATE_INTEREST_NAMEID = rtdt.TMP_LATE_INTEREST_RATES_DATA__LATE_INTEREST_NAMEID
					And src.TMP_LATE_INTEREST_SRC_DATA__DATAID = rtdt.TMP_LATE_INTEREST_RATES_DATA__LATE_INTEREST_DATAID 
			Group by trgt.TMP_LATE_INTEREST_TRG_DATA__LATE_INTEREST_NAMEID
					, src.TMP_LATE_INTEREST_SRC_DATA__TMP_LATE_INTEREST_TRG_DATAID 
			;
			-- @todo vérfier si besoin index double champ
-- 			Create Index IX_TMP_TMP_LATE_INTEREST_RESULTS_1 On TMP_TMP_LATE_INTEREST_RESULTS(TRG_DATAID);
-- 			Create Index IX_TMP_TMP_LATE_INTEREST_RESULTS_2 On TMP_TMP_LATE_INTEREST_RESULTS(NAMEID);



            Update 
                TMP_LATE_INTEREST_TRG_DATA TRG_DATA
                Inner Join TMP_TMP_LATE_INTEREST_RESULTS tmp_amout_cal
                    On TRG_DATA.TMP_LATE_INTEREST_TRG_DATA__ID = tmp_amout_cal.TRG_DATAID
                    And TRG_DATA.TMP_LATE_INTEREST_TRG_DATA__LATE_INTEREST_NAMEID = NAMEID
            set 
                TRG_DATA.TMP_LATE_INTEREST_TRG_DATA__PENALITY_AMOUNT = Coalesce(tmp_amout_cal.mp, 0)
            ;


        end;
        cleantmpdata: begin
			if Coalesce(@late_interest_debug, 0) <> 1 Then
				Drop temporary table if exists TMP_TMP_LATE_INTEREST_RESULTS;
				Drop temporary table if exists TMP_LATE_INTEREST_RATES_ORDER;
				Drop temporary table if exists TMP_LATE_INTEREST_RATES_DATES;
				drop temporary table if exists tmp_interest_amount_sum;
				Drop temporary table if exists tmp_date_sorter1;
				Drop temporary table if exists tmp_date_sorter2;
			End if ;
        end ;
    end;
end !
Delimiter ;