Delimiter //
/**
 * Author:  armen
 * Created: 20 sept. 2017
 * doit être lance apres LOADER_MATCHINGCODE_NORMALIZE_MAP_ENTRY
 */
Drop Procedure if Exists LOADER_MATCHINGCODE_NORMALIZE_CHECK_MATCHING_CODE_EXISTS//
Create Procedure LOADER_MATCHINGCODE_NORMALIZE_CHECK_MATCHING_CODE_EXISTS (
    In batchnum int
	, out stdLetteringMode Int 
)
Begin
	lmcncmce_Main: Begin
		lmcncmce_initVars: Begin
			Set @idEntities = GET_LOADER_INFO('ID_ENTITIES', batchnum);
			Set @idTemplateLoader = GET_LOADER_INFO('ID_TEMPLATE_LOADER', batchnum);

			-- obtention du mode fast du schema utilisé
			-- , si non paramtré utilisation du mode fast du schema par défaut de l'entitié
			-- sinon pas d'utilisation du mode fast par défaut
			set @stdLetteringModeCode = 'MTCHNG-CD-UK-ST' ; -- mode lettrage stanrd : conservation du lettrage
			set @perFileLetteringModeCode = 'MTCHNG-CD-UK-FL' ; -- mode lettrage stc : re-lettrage
			set @stdLetteringMode = Coalesce(
						(
							Select
								Case When Coalesce(TMPLOPT_FSLT.TEMPLATE_OPTIONS_CODE, @perFileLetteringModeCode) = @stdLetteringModeCode Then 1 Else 0 End
							From

								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 = @stdLetteringModeCode
							where 1 = 1
								And TMPLDR_FSLT.ID_TEMPLATE_LOADER = @idTemplateLoader
						)
						, (
							Select
								Case When Coalesce(TMPLOPT_FSLT.TEMPLATE_OPTIONS_CODE, @perFileLetteringModeCode) = @stdLetteringModeCode Then 1 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 = @stdLetteringModeCode
									) On ENTT.TEMPL_LOADER_ID_TEMPL_LOADER = TMPLDR_FSLT.ID_TEMPLATE_LOADER
							where 1 = 1
								And ENTT.ID_ENTITIES = @idEntities 
						)
						, 0
					)
				;
			Set stdLetteringMode = @stdLetteringMode;
			if @stdLetteringMode <> 1  Then
				-- re-lettrage si re-présentation d'un même code lettrage
				leave lmcncmce_Main;
			End if;
		end ;

		lmcncmce_update_mcde: Begin
			-- on  met a null les codes lettrage vide, pour eviter des lettrages indésirés avec des valeur null
			-- optimisation, une pierre 2 coup si MATCHINGCODES_CODE mappé alors MATCHINGCODES_ACCOUNTS_CODE forcé à null
			-- pour compatibilité ascendante
			Update 
				LOADER_MATCHINGCODES LM
				Inner Join LOADER_STATUS S
					On S.ID_LOADER_STATUS = LM.LOADER_STATUS_ID_LOADER_STATUS
			Set MATCHINGCODES_ACCOUNTS_CODE =  Case When Trim(Coalesce(MATCHINGCODES_CODE, '')) <> '' Then Null Else NullIf(Trim(MATCHINGCODES_ACCOUNTS_CODE), '') end 
				, MATCHINGCODES_CODE =  NullIf(Trim(MATCHINGCODES_CODE), '')
			Where 
				S.LOADER_STATUS_BATCH_NUM = batchnum
			;
		End ;

		lmcncmce_update_entmod: Begin
			-- lettrage inter-compte
			-- on a besoin de vérifier l'unicité du lettrage par rapport à l'entité 
			UPDATE
				LOADER_MATCHINGCODES LM
				Inner Join LOADER_STATUS LS
					On LM.LOADER_STATUS_ID_LOADER_STATUS = LS.ID_LOADER_STATUS
			Set
				LM.MATCHINGCODES_ID_MATCHINGCODES = (
					-- filtre sur l'entité
					Select 
-- 						Max(MC.ID_MATCHINGCODES)
-- 						Min(MC.ID_MATCHINGCODES)
                        MC.ID_MATCHINGCODES
					From
						MATCHINGCODES MC
						Inner Join ENTRIES_MATCHCOD EM
							On MC.ID_MATCHINGCODES = EM.MATCHCOD_ID_MATCHCOD
                            And EM.ENTRIES_MATCHCOD_CURRENT = 1
						Inner Join ENTRIES_has_ACCOUNTS EHA
							On EM.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
					Where
						ACC.ENTITIES_ID_ENTITIES = @idEntities
						And LM.MATCHINGCODES_CODE = MC.MATCHINGCODES_CODE
--                     Order By MC.ID_MATCHINGCODES
                    Limit 1
				)
			Where
				LS.LOADER_STATUS_ERRCOUNT = 0
				And LM.MATCHINGCODES_UNMATCH_FORCED = 0
				And LM.MATCHINGCODES_CODE Is Not Null
				And LM.MATCHINGCODES_CODE <> ''
				And LS.LOADER_STATUS_BATCH_NUM = batchnum
			;
		End ;
		lmcncmce_update_accmod: Begin
			-- lettrage intra compte
			-- on a besoin de vérifier l'unicité du lettrage dans le compte
			UPDATE
				LOADER_MATCHINGCODES LM
				Inner Join LOADER_STATUS LS
					On LM.LOADER_STATUS_ID_LOADER_STATUS = LS.ID_LOADER_STATUS
			Set
				LM.MATCHINGCODES_ID_MATCHINGCODES = (
					-- filtre sur l'entité
					Select 
-- 						Max(MC.ID_MATCHINGCODES)
-- 						Min(MC.ID_MATCHINGCODES)
                        MC.ID_MATCHINGCODES
					From
						MATCHINGCODES MC
						Inner Join ENTRIES_MATCHCOD EM
							On MC.ID_MATCHINGCODES = EM.MATCHCOD_ID_MATCHCOD
                            And EM.ENTRIES_MATCHCOD_CURRENT = 1
						Inner Join ENTRIES_has_ACCOUNTS EHA
							On EM.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
					Where
						LM.ACCOUNTS_ID_ACCOUNTS = ACC.ID_ACCOUNTS
						And LM.MATCHINGCODES_ACCOUNTS_CODE = MC.MATCHINGCODES_CODE
--                     Order By MC.ID_MATCHINGCODES
                    Limit 1
				)
			Where
				LS.LOADER_STATUS_ERRCOUNT = 0
				And LM.MATCHINGCODES_UNMATCH_FORCED = 0
				And LM.MATCHINGCODES_ACCOUNTS_CODE Is Not Null
				And LM.MATCHINGCODES_ACCOUNTS_CODE <> ''
				And LS.LOADER_STATUS_BATCH_NUM = batchnum
			;
		End ;

	End ;

End //
Delimiter ;
