Delimiter $$
/**
 * compute BUSINESS_COMPUTE_PENALTIES_PREP_ENTRIES
 * liste les ecritures pour lequel le calcul des pénalités est applicable
 * #6675, #6090
 */ 
Drop Procedure if Exists BUSINESS_COMPUTE_PENALTIES_PREP_ENTRIES $$
Create Procedure BUSINESS_COMPUTE_PENALTIES_PREP_ENTRIES(In penalityRuleCode varchar(45))
Begin
	Set @penalityRuleCode = penalityRuleCode;

-- 	Set @invoiceEntType = (Select ID_DF_ENTRIES_TYPE from DF_ENTRIES_TYPE where DF_ENTRIES_TYPE_CODE = 'INV');
    -- table avec la liste des écritures sur lesquelles les calculs vont s'effectuer
	Drop Temporary Table if Exists TMP_COMPUTES_PENALTIES_ACC_ENTRIES;
	Create Temporary Table TMP_COMPUTES_PENALTIES_ACC_ENTRIES (
                ACCOUNTS_ID_ACCOUNTS Int unsigned Not Null
                , ENTRIES_ID_ENTRIES Int unsigned Not Null
                , Index IX_TMP_COMPUTES_PENALTIES_ACC_ENTRIES_01 (ACCOUNTS_ID_ACCOUNTS)
                , Index IX_TMP_COMPUTES_PENALTIES_ACC_ENTRIES_02 (ENTRIES_ID_ENTRIES)
            )
        ;
-- 
--     -- table avec la liste de types d'ecriture 
-- 	Drop Temporary Table if Exists TMP_COMPUTES_PENALTIES_TYPE_ENTRIES;
-- 	Create Temporary Table TMP_COMPUTES_PENALTIES_ACC_ENTRIES (
--                 DF_ENTRIES_TYPE_ID_DF_ENTRIES_TYPE Int unsigned Not Null primary key
--             )
--         ;

	-- limitation mysql
	-- on ne peut pas construire une requete xpath dynamique en concaténant une donnée
	-- ex
	-- Concat('//penalties/filters/filter[@name="DF_ENTR_TYPE_ID_DF_ENTR_TYPE"]/value[text()="', ENT.DF_ENTR_TYPE_ID_DF_ENTR_TYPE  , '"]')
	-- pour contourner on utilise un curseur 
	pnlt_prep_entr_all: Begin
		Declare entriesType Int Unsigned;
		Declare entrTypesCursor Cursor For
				Select ID_DF_ENTRIES_TYPE
				From DF_ENTRIES_TYPE
				Where
					DF_ENTRIES_TYPE_ACCOUNTANT = 1
					And DF_ENTRIES_TYPE_IS_DEBIT = 1
			;
		Open entrTypesCursor;
		pnlt_prep_entr_blk: Begin
			Declare Exit Handler for NOT FOUND 
                Begin
                    Close entrTypesCursor;
                End ;
			pnlt_prep_entr_loop: Loop
				fetch entrTypesCursor Into entriesType;
				set @entriesType = entriesType;

                

				Insert Into TMP_COMPUTES_PENALTIES_ACC_ENTRIES(ACCOUNTS_ID_ACCOUNTS, ENTRIES_ID_ENTRIES)
                -- @todo attention performance !!!!
                -- il est préférable de faire une boucle 
                -- qui ne fait que remplire une table tmp avec les type d'écriture (champ indexé)
                -- et faire une seul requete d'insertion avec jointure

				select 
					TACC.ACCOUNTS_ID_ACCOUNTS
					, EHA.ENTRIES_ID_ENTRIES
					-- ci dessous colonne pour debug
-- 					,  ENT.DF_ENTR_TYPE_ID_DF_ENTR_TYPE
-- 					, ExtractValue(Coalesce(ACCPRL.DF_PENALTIES_RULES_DATA, ETTPRL.DF_PENALTIES_RULES_DATA)
-- 			-- 					, Concat('//penalties/filters/filter[@name="DF_ENTR_TYPE_ID_DF_ENTR_TYPE"]/value[text()="', ENT.DF_ENTR_TYPE_ID_DF_ENTR_TYPE  , '"]')
-- 								, Concat('Count(//penalties/filters/filter[@name="DF_ENTR_TYPE_ID_DF_ENTR_TYPE"]/value[text()="', @invoiceEntType  , '"])')
-- 							)
				From 
					TMP_COMPUTES_PENALTIES_ACCOUNTS TACC
					Inner Join ENTRIES_has_ACCOUNTS EHA
						On TACC.ACCOUNTS_ID_ACCOUNTS = EHA.ACCOUNTS_ID_ACCOUNTS
						And EHA.ENTRIES_PERMANENT_ATTACHEMENT = 1
					Inner Join ENTRIES ENT
						On EHA.ENTRIES_ID_ENTRIES = ENT.ID_ENTRIES
					Inner Join ACCOUNTS ACC
						On EHA.ACCOUNTS_ID_ACCOUNTS = ACC.ID_ACCOUNTS
					Left Join (
						ACCOUNTS_has_DF_PENALTIES_RULES ACCHASPNTRLS 
						Inner Join DF_PENALTIES_RULES ACCPRL
							On ACCHASPNTRLS.DF_PENALTIES_RULES_ID_DF_PENALTIES_RULES = ACCPRL.ID_DF_PENALTIES_RULES
						Inner Join DF_PENALTIES_RULES_TYPES ACCPRT
							On ACCPRL.DF_PENALTIES_RULES_TYPES_ID_DF_PENALTIES_RULES_TYPES = ACCPRT.ID_DF_PENALTIES_RULES_TYPES
							And ACCPRT.DF_PENALTIES_RULES_TYPES_CODE = @penalityRuleCode
					) On ACC.ID_ACCOUNTS = ACCHASPNTRLS.ACCOUNTS_ID_ACCOUNTS
					Left Join (
						ENTITIES_has_DF_PENALTIES_RULES ETTHASPNTRLS 
						Inner Join DF_PENALTIES_RULES ETTPRL
							On ETTHASPNTRLS.DF_PENALTIES_RULES_ID_DF_PENALTIES_RULES = ETTPRL.ID_DF_PENALTIES_RULES
						Inner Join DF_PENALTIES_RULES_TYPES ETTPRT
							On ETTPRL.DF_PENALTIES_RULES_TYPES_ID_DF_PENALTIES_RULES_TYPES = ETTPRT.ID_DF_PENALTIES_RULES_TYPES
							And ETTPRT.DF_PENALTIES_RULES_TYPES_CODE = @penalityRuleCode
					) On ACC.ENTITIES_ID_ENTITIES = ETTHASPNTRLS.ENTITIES_ID_ENTITIES

				Where ENT.ENTRIES_DUE_DATE <= AMO_BAL_REFDATE()
					ANd ENT.DF_ENTR_TYPE_ID_DF_ENTR_TYPE = @entriesType
					And ExtractValue(Coalesce(ACCPRL.DF_PENALTIES_RULES_DATA, ETTPRL.DF_PENALTIES_RULES_DATA)
					-- ci dessous ne fonctionne pas en mysql 5.5
			-- -- 					, Concat('//penalties/filters/filter[@name="DF_ENTR_TYPE_ID_DF_ENTR_TYPE"]/value[text()="', ENT.DF_ENTR_TYPE_ID_DF_ENTR_TYPE  , '"]')
								, Concat('Count(//penalties/filters/filter[@name="DF_ENTR_TYPE_ID_DF_ENTR_TYPE"]/value[text()="', @entriesType  , '"])')
							) > 0

				;
-- structre xml dans data : 
-- 	'<penalties>'
-- 	   , '<filters>'
-- 		   , '<filter name="DF_ENTR_TYPE_ID_DF_ENTR_TYPE">'
-- 			   , '<value>', @invoiceEntType , '</value>'
-- 		   , '</filter>'
-- 	   , '</filters>'
--    ,'</penalties>'


			End Loop pnlt_prep_entr_loop;
		End;
	end;

-- 		Insert Into TMP_COMPUTES_PENALTIES_ACC_ENTRIES(ACCOUNTS_ID_ACCOUNTS, ENTRIES_ID_ENTRIES)
-- 
-- 		select 
-- 			TACC.ACCOUNTS_ID_ACCOUNTS
-- 			, EHA.ENTRIES_ID_ENTRIES
-- 		From 
-- 			TMP_COMPUTES_PENALTIES_ACCOUNTS TACC
-- 			Inner Join ENTRIES_has_ACCOUNTS EHA
-- 				On TACC.ACCOUNTS_ID_ACCOUNTS = EHA.ACCOUNTS_ID_ACCOUNTS
-- 				And EHA.ENTRIES_PERMANENT_ATTACHEMENT = 1
-- 			Inner Join ENTRIES ENT
-- 				On EHA.ENTRIES_ID_ENTRIES = ENT.ID_ENTRIES
-- 
-- 		Where
-- 			ENT.DF_ENTR_TYPE_ID_DF_ENTR_TYPE = @invoiceEntType
-- 			And ENT.ENTRIES_DUE_DATE <= AMO_BAL_REFDATE()
-- 		;
end $$
Delimiter ;


