Delimiter //
/**
    In batchnum Int : num du lot, si 0 ou null traitement de l'ensembles des lignes
*/
Drop Procedure if exists LOADER_MATCHINGCODES_ENTRIES_INSERT//
Create Procedure LOADER_MATCHINGCODES_ENTRIES_INSERT (
				In batchnum int
				, In lastId Int
				, In fastStatusMode Int
				
	)
Begin

		Set @lmei_lastid = lastId;
		Set @lmei_batchnum = batchnum;
		-- vérifier si nécéssaire de prendr ene compte l'option
-- 		Set @lmei_stdLetteringMode = Coalesce(stdLetteringMode, 0); 
		lmei_ENTRIES_MATCHCOD: Begin
			-- mode non "comptable" pas de pessitance des codes lettrages
			--  supression creation des lettrage
			Insert Into ENTRIES_MATCHCOD (
				ENTRIES_ID_ENTRIES, MATCHCOD_ID_MATCHCOD,
				ENTRIES_MATCHCOD_ORDER, ENTRIES_MATCHCOD_CURRENT
			)
			Select 
				src.ID_ENTRIES, 
				Coalesce(src.MATCHINGCODES_ID_MATCHINGCODES, TBL_2.ID_LOADER_MATCHINGCODES_MATCHINGCODES + @lmei_lastid) , 
				-- ci dessous pour debug
-- 				src.MATCHINGCODES_ID_MATCHINGCODES, TBL_2.ID_LOADER_MATCHINGCODES_MATCHINGCODES, @lmei_lastid , 
				src.ENTRIES_MATCHCOD_ORDER, src.ENTRIES_MATCHCOD_CURRENT
			From 
				LOADER_MATCHINGCODES src
				Inner Join LOADER_STATUS lst
					On src.LOADER_STATUS_ID_LOADER_STATUS = lst.ID_LOADER_STATUS
				Inner Join LOADER_MATCHINGCODES_ENTRIES TBL_1
					On lst.ID_LOADER_STATUS = TBL_1.LOADER_STATUS_ID_LOADER_STATUS
				Inner Join LOADER_MATCHINGCODES_MATCHINGCODES TBL_2
					On TBL_2.ID_LOADER_MATCHINGCODES_MATCHINGCODES = TBL_1.LOADER_MATCHCOD_MATCHCOD_ID_LOADER_MATCHCOD_MATCHCOD
			Where 1 = 1  
				And lst.LOADER_STATUS_ERRCOUNT = 0
				And TBL_2.ID_LOADER_MATCHINGCODES_MATCHINGCODES Is Not Null
-- 				And src.MATCHINGCODES_ID_MATCHINGCODES Is Null
				And lst.LOADER_STATUS_BATCH_NUM =  @lmei_batchnum
			;
			-- mode "comptable" pessitance des codes lettrages
			-- ajout des ecriture non présants dans lettrages
			Insert Into ENTRIES_MATCHCOD (
				ENTRIES_ID_ENTRIES, MATCHCOD_ID_MATCHCOD,
				ENTRIES_MATCHCOD_ORDER, ENTRIES_MATCHCOD_CURRENT
			)
			Select 
				src.ID_ENTRIES, 
				src.MATCHINGCODES_ID_MATCHINGCODES , 
				src.ENTRIES_MATCHCOD_ORDER, src.ENTRIES_MATCHCOD_CURRENT
			From 
				LOADER_MATCHINGCODES src
				Inner Join LOADER_STATUS lst
					On src.LOADER_STATUS_ID_LOADER_STATUS = lst.ID_LOADER_STATUS
				Inner Join LOADER_MATCHINGCODES_ENTRIES TBL_1
					On lst.ID_LOADER_STATUS = TBL_1.LOADER_STATUS_ID_LOADER_STATUS
			Where 1 = 1  
				And lst.LOADER_STATUS_ERRCOUNT = 0
				And src.MATCHINGCODES_ID_MATCHINGCODES Is Not Null
				And lst.LOADER_STATUS_BATCH_NUM =  @lmei_batchnum
			;
		End ;
			-- ticket #6291 mise à jour du status de lettrage par ecritures

			-- optimisation (posterieur au ticket)
			-- le batch de calcul du status de lettrage n'actualise desomais que 
			-- les écritres ouvertes ( non totalement lettrés) en fonction de l'option d'import
			-- si le code status n'est pas fournis, on met un status de lettrage partiel
			-- pour "forcer" le actualisation du lettrage

			Set @lmei_optimizedMatchStat = Coalesce(fastStatusMode, 0);

            

            Set @lmei_openEntryCode = 'OPEN ENTRY';
            Set @lmei_partialEntryCode = 'PARTIAL MATCHING CODE';
            Set @lmei_closedEntryCode = 'CLOSED ENTRY';

            Set @lmei_openEntry = (Select ID_DF_MATCHINGCODE_STATE From DF_MATCHINGCODE_STATE Where DF_MATCHINGCODE_STATE_CODE = @lmei_openEntryCode Limit 1);
            Set @lmei_partialEntry = (Select ID_DF_MATCHINGCODE_STATE From DF_MATCHINGCODE_STATE Where DF_MATCHINGCODE_STATE_CODE = @lmei_partialEntryCode Limit 1);
            Set @lmei_closedEntry = (Select ID_DF_MATCHINGCODE_STATE From DF_MATCHINGCODE_STATE Where DF_MATCHINGCODE_STATE_CODE = @lmei_closedEntryCode Limit 1);

			If @lmei_optimizedMatchStat  = 0 Then
				-- mode classic
				Update 
					ENTRIES ENT
					Inner Join LOADER_MATCHINGCODES LM
						On ENT.ID_ENTRIES = LM.ID_ENTRIES
					Inner Join LOADER_STATUS LST
						On LM.LOADER_STATUS_ID_LOADER_STATUS = LST.ID_LOADER_STATUS
				Set 
                    -- le champ issu de la normalisation "DF_MATCHINGCODE_STATE_ID_DF_MATCHINGCODE_STATE" peut désormais 
                    -- avoir la valeur "0" indiquant qu'il faut supprimer le statue de lettrage forcé
                    -- Dans ce cas on affecte la valeur provisoire "2" (lettrage partiel) pour forcé  le recaclul
                    -- du status de lettrage par la balance
					ENT.DF_MATCH_STAT_ID_DF_MATCH_STAT = Case
                                                            When LM.DF_MATCHINGCODE_STATE_ID_DF_MATCHINGCODE_STATE Is Null Then @lmei_partialEntry
                                                            When LM.DF_MATCHINGCODE_STATE_ID_DF_MATCHINGCODE_STATE In (
                                                                    @lmei_openEntry, @lmei_partialEntry, @lmei_closedEntry
                                                                ) Then LM.DF_MATCHINGCODE_STATE_ID_DF_MATCHINGCODE_STATE 
                                                            Else @lmei_partialEntry -- partiel pour les autre cas donc le "0" (mode auto, suprresion du delettrage forcé)
                                                        End
                                                                
				Where
					LST.LOADER_STATUS_ERRCOUNT = 0
					And LST.LOADER_STATUS_BATCH_NUM = @lmei_batchnum
					And LM.DF_MATCHINGCODE_STATE_ID_DF_MATCHINGCODE_STATE Is Not Null
				;
			Else

				Update 
					ENTRIES ENT
					Inner Join LOADER_MATCHINGCODES LM
						On ENT.ID_ENTRIES = LM.ID_ENTRIES
					Inner Join LOADER_STATUS LST
						On LM.LOADER_STATUS_ID_LOADER_STATUS = LST.ID_LOADER_STATUS
				Set 
-- 					ENT.DF_MATCH_STAT_ID_DF_MATCH_STAT = Coalesce(LM.DF_MATCHINGCODE_STATE_ID_DF_MATCHINGCODE_STATE, @lmei_partialEntry)
					ENT.DF_MATCH_STAT_ID_DF_MATCH_STAT = Case
                                                            When LM.DF_MATCHINGCODE_STATE_ID_DF_MATCHINGCODE_STATE Is Null Then @lmei_partialEntry
                                                            When LM.DF_MATCHINGCODE_STATE_ID_DF_MATCHINGCODE_STATE In (
                                                                    @lmei_openEntry, @lmei_partialEntry, @lmei_closedEntry
                                                                ) Then LM.DF_MATCHINGCODE_STATE_ID_DF_MATCHINGCODE_STATE 
                                                            Else @lmei_partialEntry -- partiel pour les autre cas donc le "0" (mode auto, suprresion du delettrage forcé)
                                                        End
				Where
					LST.LOADER_STATUS_ERRCOUNT = 0
					And LST.LOADER_STATUS_BATCH_NUM = @lmei_batchnum
				;
			End if;

			-- afin de pouvoir exclure ces ecriture de la mise à jour du status de lettrage, on utilise la table ENTRIES_COMPUTED_DATA
			-- la purge de la table ENTRIES_COMPUTED_DATA  est faite dans LOADER_MATCHINGCODES_ENTRIES_DELETE
            set @lmei_curDate = curdate();
			-- insertion des ecritures avec un status de lettrage forcé
			Insert Into ENTRIES_COMPUTED_DATA (
				ENTRIES_ID_ENTRIES, ENTRIES_COMPUTED_DATA_KEY, ENTRIES_COMPUTED_DATA_EXTID
                , ENTRIES_COMPUTED_DATA_DATE
			)
			Select 
-- 				distinct 
-- 				LM.ID_ENTRIES, 'FORCEDMATCHSTS', LM.DF_MATCHINGCODE_STATE_ID_DF_MATCHINGCODE_STATE
				LM.ID_ENTRIES, 'FORCEDMATCHSTS', Min(LM.DF_MATCHINGCODE_STATE_ID_DF_MATCHINGCODE_STATE)
                --  en raison du distinct on utilise la date du jour et non une donnée mappé
                -- pour éviter le risque d'un doublons
                , @lmei_curDate
			From
				LOADER_MATCHINGCODES LM
				Inner Join LOADER_STATUS LST
					On LM.LOADER_STATUS_ID_LOADER_STATUS = LST.ID_LOADER_STATUS
			Where
				LST.LOADER_STATUS_ERRCOUNT = 0
				And LST.LOADER_STATUS_BATCH_NUM = @lmei_batchnum
				And LM.DF_MATCHINGCODE_STATE_ID_DF_MATCHINGCODE_STATE Is Not Null
				And LM.DF_MATCHINGCODE_STATE_ID_DF_MATCHINGCODE_STATE In (
                                                                    @lmei_openEntry, @lmei_partialEntry, @lmei_closedEntry
                                                                )
                -- si MATCHINGCODES_STATE_CODE =0 ou LM.MATCHINGCODE_STATE = 0 Alors la normalisation
                -- à affecté la valeur 0 au champ LM.DF_MATCHINGCODE_STATE_ID_DF_MATCHINGCODE_STATE 
                
--                 And (
--                         Coalesce(LM.MATCHINGCODES_STATE_CODE, '') <> ''
--                         Or Coalesce(LM.MATCHINGCODE_STATE, 0) <> 0
--                     )
                -- ci dessus du fait du test sur LM.DF_MATCHINGCODE_STATE_ID_DF_MATCHINGCODE_STATE
--                 And (
--                         Coalesce(LM.MATCHINGCODES_STATE_CODE, '') In (  
--                                 @lmei_openEntryCode, @lmei_partialEntryCode, @lmei_closedEntryCode
--                             )
--                         Or Coalesce(LM.MATCHINGCODE_STATE, 0) In (
--                                 @lmei_openEntry, @lmei_partialEntry, @lmei_closedEntry
--                             )
--                     )
             Group By LM.ID_ENTRIES
                ;

End //

Delimiter ;


