Delimiter //
/**
 * compute aging balance
 * cette PS semble dépréciée et inutilisée
 * il y a des commit implicite de transaction et une implémentation incomplète du BLOCKED et DUE
 * (manque notion de promesse non échu et status écriture bloquant)
 * @author Armen
 */ 
Drop Procedure if Exists BUSINESS_AGING_BALANCE//
 
Create Procedure BUSINESS_AGING_BALANCE()
Begin
    main: begin
        set @dec=0;
        datesTables: Begin
            drop temporary table if exists TMP_AGING_BALANCE_DATES;
            create temporary table TMP_AGING_BALANCE_DATES As 
                Select ENTITIES.ADMIN_ID_ADMIN, ENTITIES.ID_ENTITIES ENTITIES_ID_ENTITIES
                    , concat ('p8: +',ADMIN_AGING_PORT_BAL_PERIOD7)  periode_label
                    , ADMIN_AGING_PORT_BAL_PERIOD7 +1 bal_period
                    , date_sub(curdate(), interval 100 year) bdate
                    , date_sub(curdate(), interval ADMIN_AGING_PORT_BAL_PERIOD7 day) edate
                From ADMIN_PARAMS
                    Inner Join ENTITIES
                        On ADMIN_PARAMS.ID_ADMIN_PARAMS = ENTITIES.ADMIN_ID_ADMIN
                Union All
                Select ENTITIES.ADMIN_ID_ADMIN, ENTITIES.ID_ENTITIES ENTITIES_ID_ENTITIES
                    , concat ('p7: ',ADMIN_AGING_PORT_BAL_PERIOD6, ' - ', ADMIN_AGING_PORT_BAL_PERIOD7)  periode_label
                    , ADMIN_AGING_PORT_BAL_PERIOD7 bal_period
                    , date_sub(curdate(), interval ADMIN_AGING_PORT_BAL_PERIOD7 - @dec day) bdate
                    , date_sub(curdate(), interval ADMIN_AGING_PORT_BAL_PERIOD6 day) edate
                From ADMIN_PARAMS
                    Inner Join ENTITIES
                        On ADMIN_PARAMS.ID_ADMIN_PARAMS = ENTITIES.ADMIN_ID_ADMIN
                Union All
                Select ENTITIES.ADMIN_ID_ADMIN, ENTITIES.ID_ENTITIES ENTITIES_ID_ENTITIES
                    , concat ('p6: ',ADMIN_AGING_PORT_BAL_PERIOD5, ' - ', ADMIN_AGING_PORT_BAL_PERIOD6)  periode_label
                    , ADMIN_AGING_PORT_BAL_PERIOD6 bal_period
                    , date_sub(curdate(), interval ADMIN_AGING_PORT_BAL_PERIOD6 - @dec day) bdate
                    , date_sub(curdate(), interval ADMIN_AGING_PORT_BAL_PERIOD5  day) edate
                From ADMIN_PARAMS
                    Inner Join ENTITIES
                        On ADMIN_PARAMS.ID_ADMIN_PARAMS = ENTITIES.ADMIN_ID_ADMIN
                Union All
                Select ENTITIES.ADMIN_ID_ADMIN, ENTITIES.ID_ENTITIES ENTITIES_ID_ENTITIES
                    , concat ('p5: ',ADMIN_AGING_PORT_BAL_PERIOD4, ' - ', ADMIN_AGING_PORT_BAL_PERIOD5)  periode_label
                    , ADMIN_AGING_PORT_BAL_PERIOD5 bal_period
                    , date_sub(curdate(), interval ADMIN_AGING_PORT_BAL_PERIOD5 - @dec day) bdate
                    , date_sub(curdate(), interval ADMIN_AGING_PORT_BAL_PERIOD4  day) edate
                From ADMIN_PARAMS
                    Inner Join ENTITIES
                        On ADMIN_PARAMS.ID_ADMIN_PARAMS = ENTITIES.ADMIN_ID_ADMIN
                Union All
                Select ENTITIES.ADMIN_ID_ADMIN, ENTITIES.ID_ENTITIES ENTITIES_ID_ENTITIES
                    , concat ('p4: ',ADMIN_AGING_PORT_BAL_PERIOD3, ' - ', ADMIN_AGING_PORT_BAL_PERIOD4)  periode_label
                    , ADMIN_AGING_PORT_BAL_PERIOD4 bal_period
                    , date_sub(curdate(), interval ADMIN_AGING_PORT_BAL_PERIOD4 - @dec day) bdate
                    , date_sub(curdate(), interval ADMIN_AGING_PORT_BAL_PERIOD3  day) edate
                From ADMIN_PARAMS
                    Inner Join ENTITIES
                        On ADMIN_PARAMS.ID_ADMIN_PARAMS = ENTITIES.ADMIN_ID_ADMIN
                Union All
                Select ENTITIES.ADMIN_ID_ADMIN, ENTITIES.ID_ENTITIES ENTITIES_ID_ENTITIES
                    , concat ('p3: ',ADMIN_AGING_PORT_BAL_PERIOD2, ' - ', ADMIN_AGING_PORT_BAL_PERIOD3)  periode_label
                    , ADMIN_AGING_PORT_BAL_PERIOD3 bal_period
                    , date_sub(curdate(), interval ADMIN_AGING_PORT_BAL_PERIOD3 - @dec day) bdate
                    , date_sub(curdate(), interval ADMIN_AGING_PORT_BAL_PERIOD2  day) edate
                From ADMIN_PARAMS
                    Inner Join ENTITIES
                        On ADMIN_PARAMS.ID_ADMIN_PARAMS = ENTITIES.ADMIN_ID_ADMIN
                Union All
                Select ENTITIES.ADMIN_ID_ADMIN, ENTITIES.ID_ENTITIES ENTITIES_ID_ENTITIES
                    , concat ('p2: ',ADMIN_AGING_PORT_BAL_PERIOD1, ' - ', ADMIN_AGING_PORT_BAL_PERIOD2)  periode_label
                    , ADMIN_AGING_PORT_BAL_PERIOD2 bal_period
                    , date_sub(curdate(), interval ADMIN_AGING_PORT_BAL_PERIOD2 - @dec day) bdate
                    , date_sub(curdate(), interval ADMIN_AGING_PORT_BAL_PERIOD1  day) edate
                From ADMIN_PARAMS
                    Inner Join ENTITIES
                        On ADMIN_PARAMS.ID_ADMIN_PARAMS = ENTITIES.ADMIN_ID_ADMIN
                Union All
                Select ENTITIES.ADMIN_ID_ADMIN, ENTITIES.ID_ENTITIES ENTITIES_ID_ENTITIES
                    , concat ('p1: 0', ' - ', ADMIN_AGING_PORT_BAL_PERIOD1)  periode_label
                    , ADMIN_AGING_PORT_BAL_PERIOD1 bal_period
                    , date_sub(curdate(), interval ADMIN_AGING_PORT_BAL_PERIOD1 - @dec day) bdate
                    , date_sub(curdate(), interval 0  day) edate
                From ADMIN_PARAMS
                    Inner Join ENTITIES
                        On ADMIN_PARAMS.ID_ADMIN_PARAMS = ENTITIES.ADMIN_ID_ADMIN
                Union All
                Select ENTITIES.ADMIN_ID_ADMIN, ENTITIES.ID_ENTITIES ENTITIES_ID_ENTITIES
                    ,'not overdue'  periode_label
                    , null bal_period
                    , date_add(curdate(), interval 1 day) bdate
                    , date_add(curdate(), interval 100 year) edate
                From ADMIN_PARAMS
                    Inner Join ENTITIES
                        On ADMIN_PARAMS.ID_ADMIN_PARAMS = ENTITIES.ADMIN_ID_ADMIN
            ;
            Alter Table TMP_AGING_BALANCE_DATES add ID_TMP_AGING_BALANCE_DATES int unsigned not null auto_increment primary key;
--             Create index IX_TMP_AGING_BALANCE_DATES_bdate on TMP_AGING_BALANCE_DATES(ENTITIES_ID_ENTITIES, bdate);
--             Create index IX_TMP_AGING_BALANCE_DATES_edate on TMP_AGING_BALANCE_DATES(ENTITIES_ID_ENTITIES, edate);
            Create index IX_TMP_AGING_BALANCE_DATES_1 on TMP_AGING_BALANCE_DATES(ENTITIES_ID_ENTITIES, bdate, edate);
        End ;
        
        balanceTable: begin
            Drop Temporary table if exists TMP_BALANCES;
            create temporary table TMP_BALANCES As 
            Select
                ID_TMP_AGING_BALANCE_DATES
                , Count(et.ID_ENTRIES) entriescount
                , Sum(et.ENTRIES_ENT_DEBIT - et.ENTRIES_ENT_CREDIT) pbalance
                , Sum(
                        Case When r.RECLAIMS_NB_BLOCKED = 1 And r.RECLAIMS_NB_CLOSED = 0
                            Then et.ENTRIES_ENT_DEBIT - et.ENTRIES_ENT_CREDIT
                            Else 0
                        End
                    ) As BLOCKED
                , Sum(
                    Case When dms.DF_MATCHINGCODE_STATE_CLOSED = 0
                        Then et.ENTRIES_ENT_DEBIT - et.ENTRIES_ENT_CREDIT
                        Else 0
                    End
                ) - Sum(
                    Case When r.RECLAIMS_NB_BLOCKED = 1 And r.RECLAIMS_NB_CLOSED = 0 
                        Then et.ENTRIES_ENT_DEBIT - et.ENTRIES_ENT_CREDIT
                        Else 0
                    End
                ) As DUE

    
            From
                ACCOUNTS ac
                Inner Join ENTRIES_has_ACCOUNTS eha
                    On ac.ID_ACCOUNTS = eha.ACCOUNTS_ID_ACCOUNTS
                Inner Join ENTRIES et
                    On eha.ENTRIES_ID_ENTRIES = et.ID_ENTRIES
                Inner Join DF_ENTRIES_TYPE det
                    On et.DF_ENTR_TYPE_ID_DF_ENTR_TYPE = det.ID_DF_ENTRIES_TYPE
                    AND det.DF_ENTRIES_TYPE_ACCOUNTANT = 1
                Inner Join TMP_AGING_BALANCE_DATES tdates
                    On ac.ENTITIES_ID_ENTITIES = tdates.ENTITIES_ID_ENTITIES
--                     And et.ENTRIES_DUE_DATE between tdates.bdate and tdates.edate
                    And et.ENTRIES_DUE_DATE >= tdates.bdate 
						and et.ENTRIES_DUE_DATE <= tdates.edate
                Left Join V_ENTRIES_NB_RECLAIMS_VIEW r
                    ON et.ID_ENTRIES = r.ENTRIES_ID_ENTRIES
                Left Join DF_MATCHINGCODE_STATE dms
                    ON et.DF_MATCH_STAT_ID_DF_MATCH_STAT = dms.ID_DF_MATCHINGCODE_STATE
            where 
                et.DF_SAND_STATE_ID_DF_SAND_STATE <> 3
                And ac.ENTITIES_ID_ENTITIES = 1
                And dms.DF_MATCHINGCODE_STATE_CLOSED = 0
            group by ID_TMP_AGING_BALANCE_DATES
            ;
        end ;

        -- add filers
        -- result
        Select 
            * 
        From 
            TMP_BALANCES 
            Inner Join TMP_AGING_BALANCE_DATES 
                On TMP_BALANCES.ID_TMP_AGING_BALANCE_DATES = TMP_AGING_BALANCE_DATES.ID_TMP_AGING_BALANCE_DATES
        ;

    end ;
End //
Delimiter ;
