Delimiter //
/**
 * 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_FULL//
 
Create Procedure BUSINESS_CALCULATE_ENTRIES_MATCHING_CODE_STATE_FULL(
    In idaccounts Int unsigned
    , In identities Int unsigned
    , In maxDelay Smallint Unsigned
)
Begin
	bcmcsfl_Main: Begin
		bcmcsfl_initvars: Begin
			-- Get matching codes state ids
			Set @bcmcsfl_OpenEntry = (Select ID_DF_MATCHINGCODE_STATE From DF_MATCHINGCODE_STATE Where DF_MATCHINGCODE_STATE_CODE = 'OPEN ENTRY');
			Set @bcmcsfl_Partial = (Select ID_DF_MATCHINGCODE_STATE From DF_MATCHINGCODE_STATE Where DF_MATCHINGCODE_STATE_CODE = 'PARTIAL MATCHING CODE');
			Set @bcmcsfl_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 @bcmcsfl_mtchRefDate = AMO_BAL_REFDATE();

			set @bcmcsfl_CONST_FULL_MODE = 0;
			set @bcmcsfl_CONST_FAST_MODE = 1;
			set @bcmcsfl_CONST_AVG_MODE = 2;

            if idaccounts Is null Then
                Set @bcmcsfl_accMinVal = 0;
                Set @bcmcsfl_accMaxVal = 4e7;

                Set @bcmcsfl_enttMinVal = Coalesce(identities, 0);
                Set @bcmcsfl_enttMaxVal = Coalesce(identities, 20000);

            Else
                Set @bcmcsfl_accMinVal = idaccounts;
                Set @bcmcsfl_accMaxVal = idaccounts;

                Set @bcmcsfl_enttMinVal = Coalesce(
                            (
                                Select ENTITIES_ID_ENTITIES
                                From ACCOUNTS
                                Where ID_ACCOUNTS = @bcmcsfl_accMinVal
                                Limit 1
                            )
                            , 0
                        )
                    ;
                Set @bcmcsfl_enttMaxVal = @bcmcsfl_enttMinVal;

            End if;


            -- 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 @bcmcsfl_ClsdEntr_FstMdeFltrOptim = @bcmcsfl_ClosedEntry ;


            -- option pour savoir s'il faut tenir compte ou non des date de lettrage (dans le futur)
            --  - utilisé uniquement en mode fase (calcul à date du jour)
            --  - non applicable en retro caclul
            Set @bcmcsfl_miscParamTextVal = Coalesce(
                        (
                            Select MISCELLANEOUS_PARAMS_TEXT_VAL1
                            from MISCELLANEOUS_PARAMS
                            where MISCELLANEOUS_PARAMS_KEY = 'MATCHING_CODE_STATE'
                            Limit 1
                        )
                        , ''
                    )
                ;

            Set @bcmcsfl_testMatchingDate = extractValue(@bcmcsfl_miscParamTextVal,  '/MiscParamsRoot/rules/testMatchingDate') ;
            Set @bcmcsfl_testMatchingDate = Case When Coalesce(CASH_ISINTEGER(@bcmcsfl_testMatchingDate), 0)=0 then 1 else Cast(@bcmcsfl_testMatchingDate as SIGNED) End;

            If  @bcmcsfl_testMatchingDate < 1 And @bcmcsfl_mtchRefDate < curdate() Then
                Set @bcmcsfl_mtchRefDate = Date_Add(Curdate(), Interval 50 Year);
            End if;


		End ;

		bcmcsfl__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 Null
				, MATCHINGCODES_ID_MATCHINGCODES Int Unsigned Not Null Primary Key
				, MATCHINGCODES_BALANCE Decimal(19,4) Null Default 0
-- 				, Index idx_BALANCES_0 (ACCOUNTS_ID_ACCOUNTS, MATCHINGCODES_ID_MATCHINGCODES)
-- 				, Index idx_BALANCES_1 (MATCHINGCODES_ID_MATCHINGCODES)
			);

            -- mode complet : traitement de toutes les données
            Insert Into temp_BALANCES (
                    ACCOUNTS_ID_ACCOUNTS
                    , MATCHINGCODES_ID_MATCHINGCODES
                    , MATCHINGCODES_BALANCE
                )
            -- la double jointure sur ENTRIES_MATCHCOD  fait effondrer les performances sur certaines instances
            -- pour contourner, on procède en en 2 étapes pour éviter cette double jointure
            -- 1 : on rempli la tabletemp_BALANCES  avec les MATCHINGCODES_ID_MATCHINGCODES correspondant aux 
            -- écritures/comptes séléctionnées. Lees autres champs dont MATCHINGCODES_BALANCE sont null
            -- 2 : mise a jour du MATCHINGCODES_BALANCE par sous-requête
            -- explain
            Select 
                Null ID_ACCOUNTS
                , MC.ID_MATCHINGCODES
                , Null MATCHINGCODES_BALANCE
            -- debug
            -- explain Select    Count(*)
            From ACCOUNTS ACC_SEL
                Inner join ENTRIES_has_ACCOUNTS EHA_SEL
                    On ACC_SEL.ID_ACCOUNTS = EHA_SEL.ACCOUNTS_ID_ACCOUNTS 
                Inner Join ENTRIES ENT_SEL
                    On EHA_SEL.ENTRIES_ID_ENTRIES = ENT_SEL.ID_ENTRIES 
                Inner Join ENTRIES_MATCHCOD EMC_SEL
                    On ENT_SEL.ID_ENTRIES = EMC_SEL.ENTRIES_ID_ENTRIES
                Inner Join MATCHINGCODES MC
                    On EMC_SEL.MATCHCOD_ID_MATCHCOD = MC.ID_MATCHINGCODES

            Where 1 = 1
                And ACC_SEL.ID_ACCOUNTS Between @bcmcsfl_accMinVal And @bcmcsfl_accMaxVal
                And ACC_SEL.ENTITIES_ID_ENTITIES Between @bcmcsfl_enttMinVal And @bcmcsfl_enttMaxVal
                And EHA_SEL.ENTRIES_PERMANENT_ATTACHEMENT = 1
                And EMC_SEL.ENTRIES_MATCHCOD_CURRENT = 1
                And MC.MATCHINGCODES_DATE <= @bcmcsfl_mtchRefDate

            Group By MC.ID_MATCHINGCODES
            ;

            -- mise à jour du champ MATCHINGCODES_BALANCE
            
            Update temp_BALANCES tar
            Set tar.MATCHINGCODES_BALANCE = Coalesce(
                    (
                        -- ici on est dans le mode Complet
                        -- on doit untiliser uniquement le le debit - credit
                        Select SUM(ENT_MTCH.ENTRIES_ENT_DEBIT - ENT_MTCH.ENTRIES_ENT_CREDIT) 
                        From ENTRIES_MATCHCOD EMC_MTCH 
                            Inner Join ENTRIES ENT_MTCH
                                On EMC_MTCH.ENTRIES_ID_ENTRIES = ENT_MTCH.ID_ENTRIES
                        Where tar.MATCHINGCODES_ID_MATCHINGCODES = EMC_MTCH.MATCHCOD_ID_MATCHCOD
                            And EMC_MTCH.ENTRIES_MATCHCOD_CURRENT = 1
                        Group By EMC_MTCH.MATCHCOD_ID_MATCHCOD
                    ) 
                    , 0
                )
            ;
        -- select tar.*, (Select SUM(ENT_MTCH.ENTRIES_ENT_BALANCE) From ENTRIES_MATCHCOD EMC_MTCH Inner Join ENTRIES ENT_MTCH On EMC_MTCH.ENTRIES_ID_ENTRIES = ENT_MTCH.ID_ENTRIES Where tar.MATCHINGCODES_ID_MATCHINGCODES = EMC_MTCH.MATCHCOD_ID_MATCHCOD And EMC_MTCH.ENTRIES_MATCHCOD_CURRENT = 1 Group By EMC_MTCH.MATCHCOD_ID_MATCHCOD)  from temp_BALANCES tar

		End ;

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

            Update
                ENTRIES ENT
                Inner Join ENTRIES_has_ACCOUNTS EHA
                    On EHA.ENTRIES_ID_ENTRIES = ENT.ID_ENTRIES
                    And EHA.ENTRIES_PERMANENT_ATTACHEMENT = 1
                Inner Join ACCOUNTS ACC
                    On EHA.ACCOUNTS_ID_ACCOUNTS = ACC.ID_ACCOUNTS
                Left Join ENTRIES_MATCHCOD EMC
                    On ENT.ID_ENTRIES  = EMC.ENTRIES_ID_ENTRIES 
                    And EMC.ENTRIES_MATCHCOD_CURRENT =1
                Left Join MATCHINGCODES MC
                    On EMC.MATCHCOD_ID_MATCHCOD = MC.ID_MATCHINGCODES
                Left Join temp_BALANCES TMP
                    On TMP.MATCHINGCODES_ID_MATCHINGCODES = MC.ID_MATCHINGCODES
                Left 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 = (
                        Case
                            When EMC.MATCHCOD_ID_MATCHCOD Is Null Then @bcmcsfl_OpenEntry
                            When TMP.MATCHINGCODES_BALANCE Is Null Then @bcmcsfl_OpenEntry
                            When TMP.MATCHINGCODES_BALANCE = 0 Then @bcmcsfl_ClosedEntry
                            Else @bcmcsfl_Partial

                        End 

                    )
            Where
                EHA.ACCOUNTS_ID_ACCOUNTS  Between @bcmcsfl_accMinVal And @bcmcsfl_accMaxVal
                And ACC.ENTITIES_ID_ENTITIES Between @bcmcsfl_enttMinVal And @bcmcsfl_enttMaxVal
                -- exclusion des lettrage forcé
                And ECD.ENTRIES_ID_ENTRIES Is Null
            ;

		End ;

		bcmcsfl_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
				ACC.ID_ACCOUNTS  Between @bcmcsfl_accMinVal And @bcmcsfl_accMaxVal
                And ACC.ENTITIES_ID_ENTITIES Between @bcmcsfl_enttMinVal And @bcmcsfl_enttMaxVal
				And ENT.DF_MATCH_STAT_ID_DF_MATCH_STAT <> ECD.ENTRIES_COMPUTED_DATA_EXTID
			;
		End ;

	End ;
End //
Delimiter ;