Delimiter //
/**
 * compute BUSINESS_LATE_INTEREST
 * @author Armen
 */ 
Drop Procedure if Exists BUSINESS_LATE_INTEREST//
 
Create Procedure BUSINESS_LATE_INTEREST(In idaccounts Int unsigned, In identities Int unsigned)
Begin
    main: begin
        
        Set @bli_idaccounts = idaccounts;
        if idaccounts Is null Then
            Set @bli_accMinVal = 0;
            Set @bli_accMaxVal = 4e7;
        Else
            Set @bli_accMinVal = idaccounts;
            Set @bli_accMaxVal = idaccounts;
        End if;
        
        Set @bli_identities = identities;
        if identities Is null Then
            Set @bli_enttMinVal = 0;
            Set @bli_enttMaxVal = 1000;
        Else
            Set @bli_enttMinVal = identities;
            Set @bli_enttMaxVal = identities;
        End if;


		-- on utilise la table ACCOUNTS computed data pour consolider les intérets de retard
        -- explain
		Delete ACD
		From ACCOUNTS_COMPUTED_DATA ACD
		    Inner Join ACCOUNTS acc
		        On ACD.ACCOUNTS_ID_ACCOUNTS = acc.ID_ACCOUNTS
		Where ACD.ACCOUNTS_COMPUTED_DATA_KEY = 'LATE INTEREST'
			And ACD.ACCOUNTS_ID_ACCOUNTS Between @bli_accMinVal And @bli_accMaxVal
			And acc.ENTITIES_ID_ENTITIES Between @bli_enttMinVal And @bli_enttMaxVal
		;


        -- preparation des données
        call BUSINESS_LATE_INTEREST_PREPARE_DATA(@bli_idaccounts, @bli_identities);
        -- calcul des interets
        call BUSINESS_LATE_INTEREST_CALCULATION();

--         Select * from TMP_LATE_INTEREST_TRG_DATA;
        -- mise à jour des données de production
        -- explain
        Update
        -- explain select * from 
            ENTRIES ent
            Inner join TMP_LATE_INTEREST_TRG_DATA trgdt
                On ent.ID_ENTRIES = trgdt.TMP_LATE_INTEREST_TRG_DATA__DATAID
        Set
            ent.ENTRIES_INTEREST = trgdt.TMP_LATE_INTEREST_TRG_DATA__PENALITY_AMOUNT
            , ent.ENTRIES_INTEREST_LST_UPDAT = current_date
        ;

        -- purge des interets des ecritures bloquées
        -- explain
        Update
        -- explain Select * from
            ENTRIES ent
            -- TMP_ENTRIES_DUE_4_INTEREST_DATA crée dans la PS BUSINESS_LATE_INTEREST_PREPARE_DATA
            Left Join TMP_ENTRIES_DUE_4_INTEREST_DATA ved 
                On ent.ID_ENTRIES = ved.ID_ENTRIES
        Set
            -- on met à jour la date si nécéssaire
--             ent.ENTRIES_INTEREST_LST_UPDAT = Case When ent.ENTRIES_INTEREST Is Null Then current_date else Null End
            ent.ENTRIES_INTEREST_LST_UPDAT = current_date 
--             on force le null en raison du left join
            , ent.ENTRIES_INTEREST = null
        Where
            ved.ID_ENTRIES Is Null -- non exigible
            and ent.ENTRIES_INTEREST Is Not null
        ;

		-- cumul dans la table computed
		-- vue avec Olivier
		-- Pour le calcul des totaux par compte des frais, on doit filter
		--  - les ecritures totalement léttrées
		--  - les écritures bloqués
		--  - les écritures non échus
		--  suite modif regle exigible
		--  - les écritures avec promesses non échues

		Insert Into ACCOUNTS_COMPUTED_DATA (
			ACCOUNTS_ID_ACCOUNTS, ACCOUNTS_COMPUTED_DATA_KEY, ACCOUNTS_COMPUTED_DATA_DECIMAL 
		)
        -- explain
		Select 
# 			EHA.ACCOUNTS_ID_ACCOUNTS
            acc.ID_ACCOUNTS
		     , 'LATE INTEREST', Sum(ENT.ENTRIES_INTEREST)
		from ENTRIES ENT
			Inner Join ENTRIES_has_ACCOUNTS EHA
				On ENT.ID_ENTRIES = EHA.ENTRIES_ID_ENTRIES
            Inner Join ACCOUNTS acc
                       On EHA.ACCOUNTS_ID_ACCOUNTS = acc.ID_ACCOUNTS
            -- TMP_ENTRIES_DUE_4_INTEREST_DATA crée dans la PS BUSINESS_LATE_INTEREST_PREPARE_DATA
            Inner Join TMP_ENTRIES_DUE_4_INTEREST_DATA VED
                On ENT.ID_ENTRIES = VED.ID_ENTRIES
-- la jointure sur la vue pause de gros problème de perfs,
-- qui n'apparaisses pas en sous requetes
-- 			Inner Join V_ENTRIES_DUE VED
-- 				On ENT.ID_ENTRIES = VED.ID_ENTRIES
-- 				And VED.ID_DF_LANGUAGES = 1 -- simple filtre de dédoublonage des langues
-- 			Left Join DF_MATCHINGCODE_STATE DMS
-- 				On ENT.DF_MATCH_STAT_ID_DF_MATCH_STAT = DMS.ID_DF_MATCHINGCODE_STATE
		Where
            EHA.ACCOUNTS_ID_ACCOUNTS  Between @bli_accMinVal And @bli_accMaxVal
            And acc.ENTITIES_ID_ENTITIES Between @bli_enttMinVal And @bli_enttMaxVal
		    And EHA.ENTRIES_PERMANENT_ATTACHEMENT = 1
			-- ci dessous inutil est desormais fait la vue
			And ENT.ENTRIES_DUE_DATE < AMO_BAL_REFDATE()
# 		Group By EHA.ACCOUNTS_ID_ACCOUNTS
		Group By acc.ID_ACCOUNTS
		;
    End;

    if Coalesce(@late_interest_debug, 0) <> 1 Then
        Drop Temporary Table If exists TMP_ENTRIES_DUE_4_INTEREST_DATA;
    end if;


End //