Delimiter $$
/**
 * compute BUSINESS_COMPUTE_PENALTIES_ACCOUNTS_PENALITY_RULES
 * détermine l'id de la regle des pénalités à appliquer pa compte
 * la table temporraire TMP_COMPUTES_PENALTIES_ACCOUNTS doit à avoit été populé
 * elle contient la liste des compte pour lequel la pénlité est calulé
 *  elle doit posséder un champ ACCOUNTS_ID_ACCOUNTS indexé
 * Cette procédure rempli la table temporaire TMP_PENALTIES_ACCOUNTS_WITH_RULES
 * 
 */ 
Drop Procedure if Exists BUSINESS_COMPUTE_PENALTIES_ACCOUNTS_PENALITY_RULES $$
Create Procedure BUSINESS_COMPUTE_PENALTIES_ACCOUNTS_PENALITY_RULES(
			In penalityRuleCode varchar(45)
			, In cialDeptTypeId Int Unsigned
			, In useBal Int Unsigned
		)
Begin

    cpt_pntl_acc_pntl_rles_init: begin
-- 		Set @cmptaccpntlrles_cialDeptTypeId = cialDeptTypeId;
		Set @cmptaccpntlrles_cialDeptTypeId = cialDeptTypeId;
		Set @cmptaccpntlrles_penalityRuleCode = penalityRuleCode;
		Set @cmptaccpntlrles_useBal = Coalesce(useBal, 0);

		Set @cmptaccpntlrles_cptDtKey = (
											select Concat('DFPRT_', ID_DF_PENALTIES_RULES_TYPES) 
											from DF_PENALTIES_RULES_TYPES 
											where DF_PENALTIES_RULES_TYPES_CODE = @cmptaccpntlrles_penalityRuleCode
									)
				;


		Delete ACD 
		From ACCOUNTS_COMPUTED_DATA ACD 
--  commentaire à ne pas supprimer pour le moment
--  decomenter pour filter les suppression
--  on devra alors faire la purges des pénalités en amon

			Inner Join TMP_COMPUTES_PENALTIES_ACCOUNTS TACC
				on ACD.ACCOUNTS_ID_ACCOUNTS = TACC.ACCOUNTS_ID_ACCOUNTS
		Where ACD.ACCOUNTS_COMPUTED_DATA_KEY = @cmptaccpntlrles_cptDtKey
		;

		Delete ECD 
		From ENTRIES_COMPUTED_DATA ECD 
--  commentaire à ne pas supprimer pour le moment
--  decomenter pour filter les suppression
--  on devra alors faire la purges des panélité en amon
			Inner Join TMP_COMPUTES_PENALTIES_ACC_ENTRIES TENTR
				on ECD.ENTRIES_ID_ENTRIES = TENTR.ENTRIES_ID_ENTRIES
		Where ECD.ENTRIES_COMPUTED_DATA_KEY = @cmptaccpntlrles_cptDtKey
		;

    end;
    
	cpt_pntl_acc_pntl_rles_main: begin
        set @cpt_pntl_acc_pntl_rles_curdate = curdate();

		call BUSINESS_COMPUTE_PENALTIES_PREP_ACCOUNTS_RULES_TABLE(penalityRuleCode, @cptpntl_cialDeptTypeId);

		Insert Into ENTRIES_COMPUTED_DATA (
			ENTRIES_ID_ENTRIES, ENTRIES_COMPUTED_DATA_KEY, ENTRIES_COMPUTED_DATA_DECIMAL
            ,  ENTRIES_COMPUTED_DATA_DATE
		)
-- 		
		Select
			ENTRIES_ID_ENTRIES, @cmptaccpntlrles_cptDtKey
			,  Least(Greatest( 
						case when @cmptaccpntlrles_useBal = 0 Then
							balPenality2
						Else
							balPenality1
						End 
					, Coalesce(PENALTIES_MIN_AMOUNT, 0)
				), PENALTIES_MAX_AMOUNT)
            , @cpt_pntl_acc_pntl_rles_curdate
		from (
				select 
					TENTR.ACCOUNTS_ID_ACCOUNTS, TENTR.ENTRIES_ID_ENTRIES
					, Case When Coalesce(DFPR.DF_PENALTIES_RULES_RATE, 0) > 0 Then
						(DFPR.DF_PENALTIES_RULES_RATE/100) * ENT.ENTRIES_ENT_BALANCE
					Else
						Coalesce(DFPR.DF_PENALTIES_RULES_AMOUNT, 0)
					End balPenality1
				, Case When Coalesce(DFPR.DF_PENALTIES_RULES_RATE, 0) > 0 Then
						(DFPR.DF_PENALTIES_RULES_RATE/100) * (Coalesce(ENT.ENTRIES_ENT_Debit, 0) -  Coalesce(ENT.ENTRIES_ENT_CREDIT, 0))
					Else
						Coalesce(DFPR.DF_PENALTIES_RULES_AMOUNT, 0)
					End balPenality2
				, Coalesce(DF_PENALTIES_RULES_MIN_AMOUNT, 0) PENALTIES_MIN_AMOUNT
				, Coalesce(DFPR.DF_PENALTIES_RULES_MAX_AMOUNT, 1e7) PENALTIES_MAX_AMOUNT
				From 
					TMP_PENALTIES_ACCOUNTS_WITH_RULES TACCRLES
					Inner Join DF_PENALTIES_RULES DFPR
						On TACCRLES.DF_PENALTIES_RULES_ID_DF_PENALTIES_RULES = DFPR.ID_DF_PENALTIES_RULES
					Inner Join TMP_COMPUTES_PENALTIES_ACC_ENTRIES TENTR
						On TACCRLES.ACCOUNTS_ID_ACCOUNTS = TENTR.ACCOUNTS_ID_ACCOUNTS
					Inner Join ENTRIES ENT
						On TENTR.ENTRIES_ID_ENTRIES = ENT.ID_ENTRIES
			) tmpEntPenal1
		;


		-- 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 modifications regle calcul exigible, on exclu égelement les écritures ayant
		-- une promesse non echus
		-- l'ensembles des critere est présent désromais dans la vue V_ENTRIES_DUES
		Insert Into ACCOUNTS_COMPUTED_DATA (
			ACCOUNTS_ID_ACCOUNTS, ACCOUNTS_COMPUTED_DATA_KEY, ACCOUNTS_COMPUTED_DATA_DECIMAL 
            , ACCOUNTS_COMPUTED_DATA_DATE
		)
		Select 
			TENTR.ACCOUNTS_ID_ACCOUNTS, @cmptaccpntlrles_cptDtKey, Sum(ENTRIES_COMPUTED_DATA_DECIMAL)
            , @cpt_pntl_acc_pntl_rles_curdate
            -- select TENTR.ACCOUNTS_ID_ACCOUNTS, ENTRIES_COMPUTED_DATA_DECIMAL, VED.ID_ENTRIES, ECD.ENTRIES_ID_ENTRIES
            -- select *
		from ENTRIES_COMPUTED_DATA ECD
			Inner Join TMP_COMPUTES_PENALTIES_ACC_ENTRIES TENTR
				On ECD.ENTRIES_ID_ENTRIES = TENTR.ENTRIES_ID_ENTRIES
				And ECD.ENTRIES_COMPUTED_DATA_KEY = @cmptaccpntlrles_cptDtKey

			Inner Join V_ENTRIES_DUE VED
				On TENTR.ENTRIES_ID_ENTRIES = VED.ID_ENTRIES
                And TENTR.ACCOUNTS_ID_ACCOUNTS = VED.ID_ACCOUNTS
-- 				And VED.ID_DF_LANGUAGES = 1 -- simple filtre de dédoublonage des langues
-- 			Left Join DF_MATCHINGCODE_STATE DMS
-- 				On VED.DF_MATCH_STAT_ID_DF_MATCH_STAT = DMS.ID_DF_MATCHINGCODE_STATE
-- 		Where VED.ENTRIES_DUE_DATE < AMO_BAL_REFDATE()
-- 			and Coalesce(DMS.DF_MATCHINGCODE_STATE_CLOSED, 0) = 0
        Where VED.ID_DF_LANGUAGES = 1 -- simple filtre de dédoublonage des langues

		Group By TENTR.ACCOUNTS_ID_ACCOUNTS
		;
	end;

end $$
Delimiter ;


