Delimiter //
/**
 * DEPRECATED
 * Caculate the matching code state of the entries :
 * ce batch est à utiliser lorsque le lettrage entre des écritures d'un même compte
 * Si le lettrage se fait avec des écritures appartenant à des compte différents, 
 * Il faut utiliser le batch BUSINESS_CALCULATE_ENTITIES_MATCHING_CODE_STATE
 * Pour un même code lettrage d’un compte
 * - Si Somme(ENTRIES_ENT_DEBIT - ENTRIES_ENT_CREDIT) = 0
 *     => CLOSED ENTRY (3)
 * - Si Somme(ENTRIES_ENT_DEBIT - ENTRIES_ENT_CREDIT) <> 0
 *     => PARTIAL MATCHING CODE (2)
 * - Les autres écritures
 *     => OPEN ENTRY (1)
 * 
 * @author Benoit
 * @ticket #3778
 */ 
Drop Procedure if Exists BUSINESS_CALCULATE_ENTRIES_MATCHING_CODE_STATE//
 
Create Procedure BUSINESS_CALCULATE_ENTRIES_MATCHING_CODE_STATE(In idaccounts Int unsigned, In fastStatusMode Int, In maxDelay Smallint Unsigned)
Begin
	bcetrmcs_Main: Begin
		bcetrmcs_initvar: Begin
			-- Get matching codes state ids
			Set @OpenEntry = (Select ID_DF_MATCHINGCODE_STATE From DF_MATCHINGCODE_STATE Where DF_MATCHINGCODE_STATE_CODE = 'OPEN ENTRY');
			Set @Partial = (Select ID_DF_MATCHINGCODE_STATE From DF_MATCHINGCODE_STATE Where DF_MATCHINGCODE_STATE_CODE = 'PARTIAL MATCHING CODE');
			Set @ClosedEntry = (Select ID_DF_MATCHINGCODE_STATE From DF_MATCHINGCODE_STATE Where DF_MATCHINGCODE_STATE_CODE = 'CLOSED ENTRY');

			-- 	en historisation on ne peux pas utiliser le mode faste
			set @fastStatusMode = (Case When AMO_BAL_REFDATE() < curdate() then 0 else Coalesce(fastStatusMode, 0) End);
            -- set @fastStatusMode = 1
-- 			set @mtchRefDate = (Case When @fastStatusMode = 0 then '1970-01-01' else date_add(AMO_BAL_REFDATE(), Interval -30 day) End );
			set @mtchRefDate = AMO_BAL_REFDATE();

			set @CONST_FULL_MODE = 0;
			set @CONST_FAST_MODE = 1;
			set @CONST_AVG_MODE = 2;

            if idaccounts Is null Then
                Set @bcemcs_accMinVal = 0;
                Set @bcemcs_accMaxVal = 4e7;
            Else
                Set @bcemcs_accMinVal = idaccounts;
                Set @bcemcs_accMaxVal = idaccounts;
            End if;

-- 			set @accFilter = ifnull(idaccounts, '%');
			set @idacounts = ifnull(idaccounts, 0);

            -- optimisation pour les filtres de prise en compte du mode FAST et des status lettre
            -- comme un in (val1, val2, ...valn) sur un champ indéx est plus rapide qu'un <>
            -- on utilise le mode pour un filtre sur les status clos vrais ou pseudo
            Set @ClosedEntry_FastModeFilterOptim = Case When @fastStatusMode = @CONST_FAST_MODE Then  0 Else @ClosedEntry End;

		End ;
				-- @debug
			--     Select @OpenEntry, @Partial, @ClosedEntry,  @ClosedEntry_FastModeFilterOptim, @fastStatusMode, @maxUpdateDate, @mtchRefDate ;
		bcetrmcs__computebal: Begin
				-- Temporary table for storing combinations of 
			Drop Temporary Table If Exists temp_BALANCES;
			Create Temporary Table temp_BALANCES (
-- 				ACCOUNTS_ID_ACCOUNTS Int Unsigned Not Null
				ACCOUNTS_ID_ACCOUNTS Int Unsigned Null
				, MATCHINGCODES_ID_MATCHINGCODES Int Unsigned Not Null Primary Key
				, MATCHINGCODES_BALANCE Decimal(19,4) Not Null
-- 				, Index idx_BALANCES_0 (ACCOUNTS_ID_ACCOUNTS, MATCHINGCODES_ID_MATCHINGCODES)
-- 				, Index idx_BALANCES_1 (MATCHINGCODES_ID_MATCHINGCODES)
			);

			-- Inserts in the temporary table
			If @fastStatusMode = @CONST_FAST_MODE Then
                -- mode rapide : filtrage strict sur les écritures closes non modifié récemment
                -- 7j délai par défaut pour retro compatibilité
			    -- 7J * 24h * 60 mn = 10080 mn
			    set @maxDelayMinutes = Coalesce(maxDelay, 10080);
			    -- 1J * 24h * 60 mn = 1440 mn
			    if  @maxDelayMinutes > 1440 Then
#                     Set @maxUpdateDate = date_add(@mtchRefDate, interval -7 DAY);
                    Set @maxUpdateDate = date_sub(@mtchRefDate, interval @maxDelayMinutes MINUTE);
			    Else
                    Set @maxUpdateDate = date_sub(now(), Interval 60 MINUTE );
                End if;

				Insert Into 
					temp_BALANCES (
						ACCOUNTS_ID_ACCOUNTS
						, MATCHINGCODES_ID_MATCHINGCODES
						, MATCHINGCODES_BALANCE
					)
                -- explain
                    Select 
						Null ID_ACCOUNTS
						, EMC.MATCHCOD_ID_MATCHCOD
						, SUM(Coalesce(ENT.ENTRIES_ENT_BALANCE, Coalesce(ENT.ENTRIES_ENT_DEBIT, 0) - Coalesce(ENT.ENTRIES_ENT_CREDIT, 0))) AS MATCHINGCODES_BALANCE
					From
						ENTRIES ENT
                        Inner Join ENTRIES_MATCHCOD EMC
                            On ENT.ID_ENTRIES  = EMC.ENTRIES_ID_ENTRIES
                            And  EMC.ENTRIES_MATCHCOD_CURRENT =1
                    Where ENT.ID_ENTRIES IN (
                            Select 
                                EMC_MTCH.ENTRIES_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_SEL
                                    On EHA.ENTRIES_ID_ENTRIES = ENT_SEL.ID_ENTRIES
                                Inner Join ENTRIES_MATCHCOD EMC_SEL
                                    On ENT_SEL.ID_ENTRIES = EMC_SEL.ENTRIES_ID_ENTRIES
                                    And EMC_SEL.ENTRIES_MATCHCOD_CURRENT = 1
                                Inner Join MATCHINGCODES MC
                                    On EMC_SEL.MATCHCOD_ID_MATCHCOD = MC.ID_MATCHINGCODES
                                Inner Join ENTRIES_MATCHCOD EMC_MTCH
                                    On MC.ID_MATCHINGCODES = EMC_MTCH.MATCHCOD_ID_MATCHCOD
                                    And EMC_MTCH.ENTRIES_MATCHCOD_CURRENT = 1
                            Where
                                EMC_MTCH.ENTRIES_ID_ENTRIES = ENT.ID_ENTRIES
                                And ACC.ID_ACCOUNTS Between @bcemcs_accMinVal And @bcemcs_accMaxVal
                                And MC.MATCHINGCODES_DATE <= @mtchRefDate
                                And (
        --                             on ne prend en compte que les écritures ouvertes ou modifiées récemment
                                    -- les valeurs de @ClosedEntry_FastModeFilterOptim sont :
                                    --  - pour le mode fast : vaut 0 => exclusion des lettrés sauf si modif récent de l'ecriture
                                    --  - mode complet : vaut @ClosedEntry => inclusion systématique des lettrés
                                    -- utilisation de l'astuce d'optim optim : in (x, y) plus rapide que <> z
                                    ENT_SEL.DF_MATCH_STAT_ID_DF_MATCH_STAT In (@OpenEntry, @Partial, @ClosedEntry_FastModeFilterOptim) 
                                    Or ENT_SEL.ENTRIES_LAST_UPDATE >= @maxUpdateDate
                                )
                        )
                    Group By EMC.MATCHCOD_ID_MATCHCOD
                    ;

					
			End if;

			If @fastStatusMode = @CONST_AVG_MODE Then
			    -- deprected, pas utilisé, ne couvre pas tout
                -- à supprimer
				-- mode intermediaire : filtrage des lettrages ayant toute les ecritures closes
				Insert Into 
					temp_BALANCES (
						ACCOUNTS_ID_ACCOUNTS
						, MATCHINGCODES_ID_MATCHINGCODES
						, MATCHINGCODES_BALANCE
					)
					-- explain
					Select 
-- 						ACC.ID_ACCOUNTS
						null ID_ACCOUNTS
						, MC.ID_MATCHINGCODES
						, SUM(Coalesce(ENT.ENTRIES_ENT_BALANCE, Coalesce(ENT.ENTRIES_ENT_DEBIT, 0) - Coalesce(ENT.ENTRIES_ENT_CREDIT, 0))) AS MATCHINGCODES_BALANCE
-- 						, Count(NullIf(ENT.DF_MATCH_STAT_ID_DF_MATCH_STAT, @ClosedEntry))  -- debug
					From
						ACCOUNTS ACC
						Inner Join (
							ENTRIES ENT
							Inner Join  ENTRIES_has_ACCOUNTS EHA
								On EHA.ENTRIES_ID_ENTRIES = ENT.ID_ENTRIES
								And EHA.ENTRIES_PERMANENT_ATTACHEMENT = 1
						) On ACC.ID_ACCOUNTS = EHA.ACCOUNTS_ID_ACCOUNTS

						Inner Join (
							MATCHINGCODES MC
							Inner Join ENTRIES_MATCHCOD EMC
								On EMC.MATCHCOD_ID_MATCHCOD = MC.ID_MATCHINGCODES
								-- vue avec olvier, pas de gestion d'historique de lettrage
								-- on ne joint plus sur la table ENTRIES_COMPUTED_DATA avec la clé LASTMATCH
								-- on utilise simplement ENTRIES_MATCHCOD_CURRENT
								And EMC.ENTRIES_MATCHCOD_CURRENT =1
			-- 				Inner Join ENTRIES_COMPUTED_DATA LASTMATCH
			-- 					On EMC.ENTRIES_ID_ENTRIES = LASTMATCH.ENTRIES_ID_ENTRIES
			-- 					And EMC.MATCHCOD_ID_MATCHCOD = LASTMATCH.ENTRIES_COMPUTED_DATA_EXTID
						) On ENT.ID_ENTRIES  = EMC.ENTRIES_ID_ENTRIES 

					Where 
                        ACC.ID_ACCOUNTS  Between @bcemcs_accMinVal And @bcemcs_accMaxVal
--                         ACC.ID_ACCOUNTS like @accFilter
						And MC.MATCHINGCODES_DATE <= @mtchRefDate
--                         And Not Exists (
--                             Select
--                                 entchk.ID_ENTRIES
--                             from ENTRIES entchk
--                                 Inner Join ENTRIES_MATCHCOD emvchk
--                                     On entchk.ID_ENTRIES  = emvchk.ENTRIES_ID_ENTRIES 
--                             Where
--                                 entchk.DF_MATCH_STAT_ID_DF_MATCH_STAT <> @ClosedEntry
--                                 And emvchk.MATCHCOD_ID_MATCHCOD = MC.ID_MATCHINGCODES
--                             Limit 1
--                         )
					Group By 
-- 						ACC.ID_ACCOUNTS, MC.ID_MATCHINGCODES
						MC.ID_MATCHINGCODES
                    Having 
						-- on dois exclure les lettrages dont l'ensemble des ecritures sont fermées
						Count(NullIf(ENT.DF_MATCH_STAT_ID_DF_MATCH_STAT, @ClosedEntry)) > 0
				;
			End if;


-- 			If @fastStatusMode = @CONST_FULL_MODE Then
			If @fastStatusMode Not In (@CONST_FAST_MODE, @CONST_AVG_MODE) Then
				-- mode complet : traitement de toutes les données
				Insert Into 
					temp_BALANCES (
						ACCOUNTS_ID_ACCOUNTS
						, MATCHINGCODES_ID_MATCHINGCODES
						, MATCHINGCODES_BALANCE
					)
					-- explain
					Select 
-- 						ACC.ID_ACCOUNTS
						Null ID_ACCOUNTS
						, MC.ID_MATCHINGCODES
						, SUM(Coalesce(ENT.ENTRIES_ENT_BALANCE, Coalesce(ENT.ENTRIES_ENT_DEBIT, 0) - Coalesce(ENT.ENTRIES_ENT_CREDIT, 0))) AS MATCHINGCODES_BALANCE
					From
						ACCOUNTS ACC
						Inner Join (
							ENTRIES ENT
							Inner Join  ENTRIES_has_ACCOUNTS EHA
								On EHA.ENTRIES_ID_ENTRIES = ENT.ID_ENTRIES
								And EHA.ENTRIES_PERMANENT_ATTACHEMENT = 1
						) On ACC.ID_ACCOUNTS = EHA.ACCOUNTS_ID_ACCOUNTS

						Inner Join (
							MATCHINGCODES MC
							Inner Join ENTRIES_MATCHCOD EMC
								On EMC.MATCHCOD_ID_MATCHCOD = MC.ID_MATCHINGCODES
								-- vue avec olvier, pas de gestion d'historique de lettrage
								-- on ne joint plus sur la table ENTRIES_COMPUTED_DATA avec la clé LASTMATCH
								-- on utilise simplement ENTRIES_MATCHCOD_CURRENT
								And EMC.ENTRIES_MATCHCOD_CURRENT =1
			-- 				Inner Join ENTRIES_COMPUTED_DATA LASTMATCH
			-- 					On EMC.ENTRIES_ID_ENTRIES = LASTMATCH.ENTRIES_ID_ENTRIES
			-- 					And EMC.MATCHCOD_ID_MATCHCOD = LASTMATCH.ENTRIES_COMPUTED_DATA_EXTID
						) On ENT.ID_ENTRIES  = EMC.ENTRIES_ID_ENTRIES 

					Where 
                        ACC.ID_ACCOUNTS  Between @bcemcs_accMinVal And @bcemcs_accMaxVal
--                         ACC.ID_ACCOUNTS like @accFilter
						And MC.MATCHINGCODES_DATE <= @mtchRefDate
					Group By 
-- 						ACC.ID_ACCOUNTS, MC.ID_MATCHINGCODES
						MC.ID_MATCHINGCODES
				;
			End if;

		End ;

		bcetrmcs_updmtchstat: Begin
			-- Update of the matching code state of the entries WITHOUT matching code
			-- or with a matching code WITHOUT balance
			Update

-- 				ACCOUNTS ACC
-- 				Inner Join (
-- 					ENTRIES ENT
-- 					Inner Join  ENTRIES_has_ACCOUNTS EHA
-- 						On EHA.ENTRIES_ID_ENTRIES = ENT.ID_ENTRIES
-- 						And EHA.ENTRIES_PERMANENT_ATTACHEMENT = 1
-- 				) On ACC.ID_ACCOUNTS = EHA.ACCOUNTS_ID_ACCOUNTS
				ENTRIES ENT
				Inner Join  ENTRIES_has_ACCOUNTS EHA
					On EHA.ENTRIES_ID_ENTRIES = ENT.ID_ENTRIES
					And EHA.ENTRIES_PERMANENT_ATTACHEMENT = 1
				Left Join (
					MATCHINGCODES MC
					Inner Join ENTRIES_MATCHCOD EMC
						On EMC.MATCHCOD_ID_MATCHCOD = MC.ID_MATCHINGCODES
						-- vue avec olvier, pas de gestion d'historique de lettrage
						-- on ne joint plus sur la table ENTRIES_COMPUTED_DATA avec la clé LASTMATCH
						-- on utilise simplement ENTRIES_MATCHCOD_CURRENT
						And EMC.ENTRIES_MATCHCOD_CURRENT =1
		-- 			Inner Join ENTRIES_COMPUTED_DATA LASTMATCH
		-- 				On EMC.ENTRIES_ID_ENTRIES = LASTMATCH.ENTRIES_ID_ENTRIES
		-- 				And LASTMATCH.ENTRIES_COMPUTED_DATA_KEY = 'LASTMATCH'
		-- 				And EMC.MATCHCOD_ID_MATCHCOD = LASTMATCH.ENTRIES_COMPUTED_DATA_EXTID
				) On ENT.ID_ENTRIES  = EMC.ENTRIES_ID_ENTRIES 
				Left Join temp_BALANCES TMP
-- 					On TMP.ACCOUNTS_ID_ACCOUNTS = EHA.ACCOUNTS_ID_ACCOUNTS
-- 					And TMP.MATCHINGCODES_ID_MATCHINGCODES = MC.ID_MATCHINGCODES
					On TMP.MATCHINGCODES_ID_MATCHINGCODES = MC.ID_MATCHINGCODES
			Set 
				ENT.DF_MATCH_STAT_ID_DF_MATCH_STAT = (
							Case When 
									(	EMC.MATCHCOD_ID_MATCHCOD Is Null 
										Or TMP.MATCHINGCODES_BALANCE Is Null
									) 
							Then 
								@OpenEntry
							Else
								Case when (TMP.MATCHINGCODES_BALANCE = 0)
								Then 
									@ClosedEntry
								Else
									@Partial
								End
							End 
						)
			Where
				EHA.ACCOUNTS_ID_ACCOUNTS  Between @bcemcs_accMinVal And @bcemcs_accMaxVal
-- 				EHA.ACCOUNTS_ID_ACCOUNTS like @accFilter
				And (
						-- exclusion des lettrage totaux en mode rapide
-- 						ENT.DF_MATCH_STAT_ID_DF_MATCH_STAT <> @ClosedEntry
-- 						Or @fastStatusMode = @CONST_FULL_MODE
                        -- les valeurs de @ClosedEntry_FastModeFilterOptim sont :
                        --  - pour le mode fast : vaut 0 => exclusion des lettrés sauf si modif récent de l'ecriture
                        --  - mode complet : vaut @ClosedEntry => inclusion systématique des lettrés
                        -- utilisation de l'astuce d'optim optim : in (x, y) plus rapide que <> z
						ENT.DF_MATCH_STAT_ID_DF_MATCH_STAT In (@OpenEntry, @Partial, @ClosedEntry_FastModeFilterOptim) 
				        Or ENT.ENTRIES_LAST_UPDATE >= @maxUpdateDate
					)
			;
		End ;

		bcetrmcs_updtfrcdstat: Begin
			Update 
				ACCOUNTS ACC
				Inner Join (
					ENTRIES ENT
					Inner Join  ENTRIES_has_ACCOUNTS EHA
						On EHA.ENTRIES_ID_ENTRIES = ENT.ID_ENTRIES
						And EHA.ENTRIES_PERMANENT_ATTACHEMENT = 1
				) On ACC.ID_ACCOUNTS = EHA.ACCOUNTS_ID_ACCOUNTS
				Inner Join ENTRIES_COMPUTED_DATA ECD
					On ENT.ID_ENTRIES = ECD.ENTRIES_ID_ENTRIES
					And ECD.ENTRIES_COMPUTED_DATA_KEY = 'FORCEDMATCHSTS'
			Set
				ENT.DF_MATCH_STAT_ID_DF_MATCH_STAT = ECD.ENTRIES_COMPUTED_DATA_EXTID
			Where
				EHA.ACCOUNTS_ID_ACCOUNTS  Between @bcemcs_accMinVal And @bcemcs_accMaxVal
-- 				EHA.ACCOUNTS_ID_ACCOUNTS like @accFilter
				And ENT.DF_MATCH_STAT_ID_DF_MATCH_STAT <> ECD.ENTRIES_COMPUTED_DATA_EXTID
			;
		End ;

	End ;
End //
Delimiter ;