Delimiter $$
/**
 * Author:  armen
 * Created: 18 sept. 2017
 */
Drop Procedure if Exists BUSINESS_COMPUTE_ENTRIES_DATA_ENTITIES_MATCHINGCODES_LAST $$
Create Procedure BUSINESS_COMPUTE_ENTRIES_DATA_ENTITIES_MATCHINGCODES_LAST(In identities Int unsigned, In idaccounts Int unsigned, In fastStatusMode Int)

Begin
	Set @targetIdEntities = identities;
-- 	set @accFilter = ifnull(idaccounts, '%');
    if idaccounts Is null Then
        Set @bcedeml_accMinVal = 0;
        Set @bcedeml_accMaxVal = 4e7;
    Else
        Set @bcedeml_accMinVal = idaccounts;
        Set @bcedeml_accMaxVal = idaccounts;
    End if;

    if identities Is null Then
        Set @bcedeml_enttMinVal = 0;
        Set @bcedeml_enttMaxVal = 1000;
    Else
        Set @bcedeml_enttMinVal = identities;
        Set @bcedeml_enttMaxVal = identities;
    End if;


	Set @ClosedEntry = (Select ID_DF_MATCHINGCODE_STATE From DF_MATCHINGCODE_STATE Where DF_MATCHINGCODE_STATE_CODE = 'CLOSED ENTRY');

	set @fastStatusModeCode = 'MTCHNG-FAST-STT' ;
	set @avgStatusModeCode = 'MTCHNG-AVG-STT' ;

	set @mtchRefDate = AMO_BAL_REFDATE();
	Set @fastStatusMode = (
						Select
						-- pas de mode fast en historsation
						Case When @mtchRefDate < curdate() then 0 
						else Coalesce(fastStatusMode
								-- determination du mode en fonction du param de l'entité
									, (
										Select
-- 											Case When Coalesce(TMPLOPT_FSLT.TEMPLATE_OPTIONS_CODE, 'FULL') = @fastStatusModeCode Then 1 Else 0 End
											Case Coalesce(TMPLOPT_FSLT.TEMPLATE_OPTIONS_CODE, 'FULL') 
												When @fastStatusModeCode Then 1 
												When @avgStatusModeCode Then 2 
												Else 0 
											End
										From
											ENTITIES ENTT

											Left Join (
													TEMPLATE_LOADER TMPLDR_FSLT
													Inner Join TEMPL_LOADER_has_TEMPL_OPT TMPLDR_FSLTHASOPT
														On TMPLDR_FSLT.ID_TEMPLATE_LOADER = TMPLDR_FSLTHASOPT.TEMPL_LOADER_ID_TEMPL_LOADER
													Inner Join TEMPLATE_OPTIONS TMPLOPT_FSLT
														On TMPLDR_FSLTHASOPT.TEMPL_OPT_ID_TEMPL_OPT = TMPLOPT_FSLT.ID_TEMPLATE_OPTIONS
-- 														And TMPLOPT_FSLT.TEMPLATE_OPTIONS_CODE = @fastStatusModeCode
														And TMPLOPT_FSLT.TEMPLATE_OPTIONS_CODE in(@fastStatusModeCode, @avgStatusModeCode)
												) On ENTT.TEMPL_LOADER_ID_TEMPL_LOADER = TMPLDR_FSLT.ID_TEMPLATE_LOADER
										where 1 = 1
# 											And ENTT.ID_ENTITIES = @targetIdEntities
											And ENTT.ID_ENTITIES between @bcedeml_enttMinVal And @bcedeml_enttMaxVal
									)
									-- par defaut mode classic
									, 0
								) 
						End
					)
        ;
    Set @closedMatchStatOnFastMode = Case When @fastStatusMode = 0 
            Then 
                -- mon complet -> on inclu les lettrées
                @ClosedEntry
            Else    
                -- mode fast -> on ignlore les lettrés
                0
            End
        ;
    entrcptdtlmt_clearold: Begin
					-- @debug
-- 					Select @accFilter, @ClosedEntry, @fastStatusMode, @entitiesFilter, @targetIdEntities, schema(), @fastStatusModeCode;
-- 					leave entrcptdtlmt_clearold;

					-- purge des données
					-- il peux subsitet des relicats de deletrage non totalement purgés
					Delete ENTRIES_COMPUTED_DATA 
					-- select *
					From ENTRIES_COMPUTED_DATA 
						Left Join MATCHINGCODES MC
							On ENTRIES_COMPUTED_DATA.ENTRIES_COMPUTED_DATA_EXTID  = MC.ID_MATCHINGCODES
							And  ENTRIES_COMPUTED_DATA.ENTRIES_COMPUTED_DATA_KEY = 'LASTMATCH'
					Where ENTRIES_COMPUTED_DATA_KEY = 'LASTMATCH'
						And (
								(
									ENTRIES_COMPUTED_DATA.ENTRIES_ID_ENTRIES In (
										Select   ENT.ID_ENTRIES
										From ACCOUNTS ACC
											Inner Join ENTRIES_has_ACCOUNTS EHA
												On ACC.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 
# 											ACC.ENTITIES_ID_ENTITIES = @targetIdEntities
											ACC.ENTITIES_ID_ENTITIES between @bcedeml_enttMinVal And @bcedeml_enttMaxVal
											And ACC.ID_ACCOUNTS Between @bcedeml_accMinVal And @bcedeml_accMaxVal
											And ENTRIES_COMPUTED_DATA.ENTRIES_ID_ENTRIES = ENT.ID_ENTRIES
											And (
												-- eclusion des lettrage totaux en mode rapide
-- 												ENT.DF_MATCH_STAT_ID_DF_MATCH_STAT <> @ClosedEntry
-- 												Or @fastStatusMode = 0 
												ENT.DF_MATCH_STAT_ID_DF_MATCH_STAT In (1, 2, @closedMatchStatOnFastMode )
											)
									)
								)
								Or MC.ID_MATCHINGCODES Is Null
							)
					;
			End ;
			entrcptdtlmt_insertlast: Begin
				-- mise à jour des données		
				Insert Into ENTRIES_COMPUTED_DATA (
					ENTRIES_ID_ENTRIES, ENTRIES_COMPUTED_DATA_KEY, ENTRIES_COMPUTED_DATA_EXTID
				)
				-- vue avec olvier, pas de gestion d'historique de lettrage
				-- on rempli la table ENTRIES_COMPUTED_DATA avec la clé LASTMATCH pour compatibité
				-- on utilise requee simple avec simplement les lettrage qui ne sont pas déja présent
-- 							And EMC.ENTRIES_MATCHCOD_CURRENT =1
-- 						Select 
-- 							ID_ENTRIES, 'LASTMATCH', Max(ID_MATCHINGCODES)
-- 						From
-- 							V_ENTRIES_MATCHINCODE_MAX_DATE_ID
-- 						Where V_ENTRIES_MATCHINCODE_MAX_DATE_ID.ID_ENTRIES In (
-- 								Select  EHA.ENTRIES_ID_ENTRIES
-- 								From ENTRIES_has_ACCOUNTS EHA
-- 								Where EHA.ACCOUNTS_ID_ACCOUNTS like @accFilter
-- 									And V_ENTRIES_MATCHINCODE_MAX_DATE_ID.ID_ENTRIES = EHA.ENTRIES_ID_ENTRIES
-- 							)
-- 						Group By 
-- 							ID_ENTRIES
-- 						;
				Select 
					EMC.ENTRIES_ID_ENTRIES, 'LASTMATCH', EMC.MATCHCOD_ID_MATCHCOD
				from ENTRIES_MATCHCOD EMC
					Inner Join ENTRIES_has_ACCOUNTS EHA
						On EMC.ENTRIES_ID_ENTRIES = EHA.ENTRIES_ID_ENTRIES
						And EHA.ENTRIES_PERMANENT_ATTACHEMENT = 1
					Inner Join ACCOUNTS ACC
						On EHA.ACCOUNTS_ID_ACCOUNTS = ACC.ID_ACCOUNTS
					Left Join ENTRIES_COMPUTED_DATA ECD
						On EMC.ENTRIES_ID_ENTRIES = ECD.ENTRIES_ID_ENTRIES
						And ECD.ENTRIES_COMPUTED_DATA_KEY = 'LASTMATCH'
				where ENTRIES_MATCHCOD_CURRENT =1
					And ECD.ENTRIES_ID_ENTRIES Is Null
# 					And ACC.ENTITIES_ID_ENTITIES = @targetIdEntities
					And ACC.ENTITIES_ID_ENTITIES between @bcedeml_enttMinVal And @bcedeml_enttMaxVal
					And ACC.ID_ACCOUNTS  Between @bcedeml_accMinVal And @bcedeml_accMaxVal
-- 					And ACC.ID_ACCOUNTS like @accFilter
				;

    End ;



end $$
Delimiter ;