Delimiter //
/**
 * Load the associated matching code
 * 
 * We never update a matching code, but we remove old matching code, and add the new given
 * 
 * @lmcnmmuoc_param int batchnum
 * @lmcnmmuoc_author Jean-François
 * @lmcnmmuoc_ticket #2153, #2155, #4206, #4223, #4224
 */ 
Drop Procedure if Exists LOADER_MATCHINGCODE_NORMALIZE_MAP_MATCHINGCODE_UPDATE_OR_CREATE//
Create Procedure LOADER_MATCHINGCODE_NORMALIZE_MAP_MATCHINGCODE_UPDATE_OR_CREATE (
    In batchnum int
	, In stdLetteringMode Int
)
Begin

	Set @lmcnmmuoc_stdLetteringMode = Coalesce(stdLetteringMode, 0); 
	Set @lmcnmmuoc_batchnum = batchnum; 
-- 	Set @lmcnmmuoc_perFiledLetteringMode = if(@lmcnmmuoc_stdLetteringMode = 1, 0, 1); 
	
	call LOADER_MATCHINGCODE_NORMALIZE_MAP_MATCHING_CODE_TO_REMOVE(@lmcnmmuoc_batchnum, @lmcnmmuoc_stdLetteringMode);



    -- Identifier les relations écriture/code lettrage à supprimer, càd :
    --      - celles dont le code lettrage est en cours de mise à jour
    --      - même pour les écritures non présentes dans le lettrage en cours
	call LOADER_MATCHINGCODE_NORMALIZE_MAP_ENTRIES_TO_REMOVE(@lmcnmmuoc_batchnum, @lmcnmmuoc_stdLetteringMode);

    
--     select @lmcnmmuoc_stdLetteringMode, @lmcnmmuoc_batchnum;
    -- =========== AJOUTS =============


--     -- Calculer un ID_NEW pour les codes lettrage (avec dédoublonnage)
--     -- grâce à une table temporaire

	Drop Temporary Table If Exists tmp_matching_codes;
    -- Ajouter les nouveaux codes lettrage 
	-- on passe par une table tmp pour l'aggregation par compte en fonction type mappage
	Create Temporary Table tmp_matching_codes (
		MATCHINGCODES_CODE varchar(20)
		, MATCHINGCODES_DATE date
		, ACCOUNTS_ID_ACCOUNTS int unsigned 
		, index(ACCOUNTS_ID_ACCOUNTS )
		, index(MATCHINGCODES_CODE )
	);

	if  @lmcnmmuoc_stdLetteringMode =  0 Then
		-- pas de vérification de l'existance d'un code lettrage 
		-- (Lorsque des lots distincts d'écritures ont le même code lettrage
		-- , ce code se voit dupliqué dans la table de production)

		Insert Into tmp_matching_codes(
                MATCHINGCODES_CODE
				, MATCHINGCODES_DATE
				, ACCOUNTS_ID_ACCOUNTS
		)
		Select
			Coalesce(LM.MATCHINGCODES_CODE, MATCHINGCODES_ACCOUNTS_CODE) MATCHINGCODES_CODE
			, LM.MATCHINGCODES_DATE
			, Case When MATCHINGCODES_ACCOUNTS_CODE Is Null Then Null Else ACCOUNTS_ID_ACCOUNTS End ACCOUNTS_ID_ACCOUNTS
		From LOADER_MATCHINGCODES LM
			Inner Join LOADER_STATUS S
				On S.ID_LOADER_STATUS = LM.LOADER_STATUS_ID_LOADER_STATUS
		Where 
			S.LOADER_STATUS_BATCH_NUM = @lmcnmmuoc_batchnum
			And S.LOADER_STATUS_ERRCOUNT = 0
-- 			And LM.MATCHINGCODES_UNMATCH_FORCED <> 1 
			And LM.MATCHINGCODES_UNMATCH_FORCED = 0 
			And Coalesce(LM.MATCHINGCODES_CODE, MATCHINGCODES_ACCOUNTS_CODE) Is Not Null
		;
	Else
		-- vérifier les code de lettrage existant
		Insert Into tmp_matching_codes(MATCHINGCODES_CODE
				, MATCHINGCODES_DATE
				, ACCOUNTS_ID_ACCOUNTS
		)
		Select
			Coalesce(LM.MATCHINGCODES_CODE, MATCHINGCODES_ACCOUNTS_CODE) MATCHINGCODES_CODE
			, LM.MATCHINGCODES_DATE
			, Case When MATCHINGCODES_ACCOUNTS_CODE Is Null Then Null Else ACCOUNTS_ID_ACCOUNTS End ACCOUNTS_ID_ACCOUNTS
		From LOADER_MATCHINGCODES LM
			Inner Join LOADER_STATUS S
				On S.ID_LOADER_STATUS = LM.LOADER_STATUS_ID_LOADER_STATUS
		Where 
			S.LOADER_STATUS_BATCH_NUM = @lmcnmmuoc_batchnum
			And S.LOADER_STATUS_ERRCOUNT = 0
-- 			And LM.MATCHINGCODES_UNMATCH_FORCED <> 1 
			And LM.MATCHINGCODES_UNMATCH_FORCED = 0 
			And Coalesce(LM.MATCHINGCODES_CODE, MATCHINGCODES_ACCOUNTS_CODE) Is Not Null
			And LM.MATCHINGCODES_ID_MATCHINGCODES Is Null
		;

	
	End If;

	-- si lettrage inter-compte, l'id compte est toujours null, on a in regroupement 
	-- sur le code lettrage uniquement
	
	Insert Into LOADER_MATCHINGCODES_MATCHINGCODES (
			MATCHINGCODES_CODE
			, MATCHINGCODES_DATE
			, ACCOUNTS_ID_ACCOUNTS --  sera null en lettrage inter-compte
	)
	Select 
			MATCHINGCODES_CODE
			, Min(MATCHINGCODES_DATE)
			, ACCOUNTS_ID_ACCOUNTS
	From tmp_matching_codes
	Group By ACCOUNTS_ID_ACCOUNTS, MATCHINGCODES_CODE ;

	if Coalesce(@amo_loader_debug, 0) <> 1 Then
        Drop Temporary Table tmp_matching_codes; 
    End if;
	
	lmcmmuor_link_to_entries: Begin
		-- ******************************************************
		-- relations écriture/code lettrage
		-- ******************************************************

		lmcmmuor_entities_entries: Begin
			-- -------------
			-- lettrage inter compte
			-- -------------
			if  @lmcnmmuoc_stdLetteringMode =  0 Then
				-- regle de lettage "non comptable" : supression et re-creation du  lettrage
				Insert Into LOADER_MATCHINGCODES_ENTRIES (
						LOADER_STATUS_ID_LOADER_STATUS
						, LOADER_MATCHCOD_MATCHCOD_ID_LOADER_MATCHCOD_MATCHCOD
					)
				Select
						LM.LOADER_STATUS_ID_LOADER_STATUS
						, LMCMC.ID_LOADER_MATCHINGCODES_MATCHINGCODES
					From LOADER_MATCHINGCODES LM
						Inner Join LOADER_STATUS LS
							On LS.ID_LOADER_STATUS = LM.LOADER_STATUS_ID_LOADER_STATUS
						Inner Join LOADER_MATCHINGCODES_MATCHINGCODES LMCMC
							On LM.MATCHINGCODES_CODE = LMCMC.MATCHINGCODES_CODE 
					Where
-- 						LM.MATCHINGCODES_UNMATCH_FORCED <> 1 
						LM.MATCHINGCODES_UNMATCH_FORCED = 0 
						And LS.LOADER_STATUS_ERRCOUNT = 0
						And LM.MATCHINGCODES_CODE Is Not Null
						And LS.LOADER_STATUS_BATCH_NUM = @lmcnmmuoc_batchnum
				;
			Else
				-- mode "comptable"
				-- ecriture non encore lettre (à lettrer)
				Insert Into LOADER_MATCHINGCODES_ENTRIES (
						LOADER_STATUS_ID_LOADER_STATUS
						, LOADER_MATCHCOD_MATCHCOD_ID_LOADER_MATCHCOD_MATCHCOD
					)
				Select
						LM.LOADER_STATUS_ID_LOADER_STATUS
						, LMCMC.ID_LOADER_MATCHINGCODES_MATCHINGCODES
					From LOADER_MATCHINGCODES LM
						Inner Join LOADER_STATUS LS
							On LS.ID_LOADER_STATUS = LM.LOADER_STATUS_ID_LOADER_STATUS
						Inner Join LOADER_MATCHINGCODES_MATCHINGCODES LMCMC
							On LM.MATCHINGCODES_CODE = LMCMC.MATCHINGCODES_CODE 
					Where
-- 						LM.MATCHINGCODES_UNMATCH_FORCED <> 1 
						LM.MATCHINGCODES_UNMATCH_FORCED = 0
						And LS.LOADER_STATUS_ERRCOUNT = 0
						And LM.MATCHINGCODES_CODE Is Not Null
						And LM.MATCHINGCODES_ID_MATCHINGCODES Is Null
						And LS.LOADER_STATUS_BATCH_NUM = @lmcnmmuoc_batchnum
				;
			End If ;
		end;

		lmcmmuor_accounts_entries: begin
			-- -------------
			-- lettrage intra compte
			-- -------------
			if  @lmcnmmuoc_stdLetteringMode =  0 Then
				-- regle de lettage "non comptable" : supression et re-creation du  lettrage
				Insert Into LOADER_MATCHINGCODES_ENTRIES (
						LOADER_STATUS_ID_LOADER_STATUS
						, LOADER_MATCHCOD_MATCHCOD_ID_LOADER_MATCHCOD_MATCHCOD
					)
				Select
						LM.LOADER_STATUS_ID_LOADER_STATUS
						, LMCMC.ID_LOADER_MATCHINGCODES_MATCHINGCODES
					From LOADER_MATCHINGCODES LM
						Inner Join LOADER_STATUS LS
							On LS.ID_LOADER_STATUS = LM.LOADER_STATUS_ID_LOADER_STATUS
						Inner Join LOADER_MATCHINGCODES_MATCHINGCODES LMCMC
							On LM.MATCHINGCODES_ACCOUNTS_CODE = LMCMC.MATCHINGCODES_CODE
							And LM.ACCOUNTS_ID_ACCOUNTS = LMCMC.ACCOUNTS_ID_ACCOUNTS 
					Where
-- 						LM.MATCHINGCODES_UNMATCH_FORCED <> 1 
						LM.MATCHINGCODES_UNMATCH_FORCED = 0
						And LS.LOADER_STATUS_ERRCOUNT = 0
						And LM.MATCHINGCODES_ACCOUNTS_CODE Is Not Null
						And LS.LOADER_STATUS_BATCH_NUM = @lmcnmmuoc_batchnum
				;
			Else
				-- mode "comptable"
				-- ecriture non encore lettre (à lettrer)
				Insert Into LOADER_MATCHINGCODES_ENTRIES (
						LOADER_STATUS_ID_LOADER_STATUS
						, LOADER_MATCHCOD_MATCHCOD_ID_LOADER_MATCHCOD_MATCHCOD
					)
				Select
						LM.LOADER_STATUS_ID_LOADER_STATUS
						, LMCMC.ID_LOADER_MATCHINGCODES_MATCHINGCODES
					From LOADER_MATCHINGCODES LM
						Inner Join LOADER_STATUS LS
							On LS.ID_LOADER_STATUS = LM.LOADER_STATUS_ID_LOADER_STATUS
						Inner Join LOADER_MATCHINGCODES_MATCHINGCODES LMCMC
							On LM.MATCHINGCODES_ACCOUNTS_CODE = LMCMC.MATCHINGCODES_CODE
							And LM.ACCOUNTS_ID_ACCOUNTS = LMCMC.ACCOUNTS_ID_ACCOUNTS 
					Where
-- 						LM.MATCHINGCODES_UNMATCH_FORCED <> 1 
						LM.MATCHINGCODES_UNMATCH_FORCED = 0
						And LS.LOADER_STATUS_ERRCOUNT = 0
						And LM.MATCHINGCODES_ACCOUNTS_CODE Is Not Null
						And LM.MATCHINGCODES_ID_MATCHINGCODES Is Null
						And LS.LOADER_STATUS_BATCH_NUM = @lmcnmmuoc_batchnum
				;

			End If ;
		end;

		if  @lmcnmmuoc_stdLetteringMode = 1 Then
			-- ecriture non présente dans le nouveau lettrage (mode comptable)
			-- Les écriture dont le letrage est modifié sont inceré dans la table ENTRIES_TO_REMOVE
			Insert Into LOADER_MATCHINGCODES_ENTRIES (
					LOADER_STATUS_ID_LOADER_STATUS
					, LOADER_MATCHINGCODES_ENTRIES_ID_MATCHINGCODES
				)
			Select
					LM.LOADER_STATUS_ID_LOADER_STATUS
					, LM.MATCHINGCODES_ID_MATCHINGCODES
				From LOADER_MATCHINGCODES LM
					Inner Join LOADER_STATUS LS
						On LS.ID_LOADER_STATUS = LM.LOADER_STATUS_ID_LOADER_STATUS
				Where
-- 					LM.MATCHINGCODES_UNMATCH_FORCED <> 1 
					LM.MATCHINGCODES_UNMATCH_FORCED =0 
					And LS.LOADER_STATUS_ERRCOUNT = 0
					And LM.MATCHINGCODES_ID_MATCHINGCODES Is Not Null
					And Not Exists (
						Select EM.MATCHCOD_ID_MATCHCOD
						from ENTRIES_MATCHCOD EM
						Where 
							EM.MATCHCOD_ID_MATCHCOD = LM.MATCHINGCODES_ID_MATCHINGCODES
							And EM.ENTRIES_ID_ENTRIES = LM.ID_ENTRIES
                            And EM.ENTRIES_MATCHCOD_CURRENT = 1
					)
					And LS.LOADER_STATUS_BATCH_NUM = @lmcnmmuoc_batchnum
			;
		End If ;

	End;

End //
Delimiter ;