Delimiter //
/**
 * liste des ecritures à "reconcilier"
 *  - ecriture ouverte, non fournis dans le fichier (à léttrer)
 *  - ecriture fermé, à nouveau fournis dans le fichier (en fonction de l'option d'import )
 *    pour ce second cas on doit également inclure les ecrtitures du groupe de lettrage
 *    et déléttrer ces écritures
 */ 
Drop Procedure If Exists LOADER_ENTRIES_NORMALIZE_MAP_ENTRIES_TO_RECONCIL//
Create Procedure LOADER_ENTRIES_NORMALIZE_MAP_ENTRIES_TO_RECONCIL(
)
Begin

	lenmetr_main: Begin
		lenmetr_initvars: Begin
-- 			Set @lenmetr_OpenEntry = (Select ID_DF_MATCHINGCODE_STATE From DF_MATCHINGCODE_STATE Where DF_MATCHINGCODE_STATE_CODE = 'OPEN ENTRY');
-- 			Set @lenmetr_Partial = (Select ID_DF_MATCHINGCODE_STATE From DF_MATCHINGCODE_STATE Where DF_MATCHINGCODE_STATE_CODE = 'PARTIAL MATCHING CODE');
-- 			Set @lenmetr_ClosedEntry = (Select ID_DF_MATCHINGCODE_STATE From DF_MATCHINGCODE_STATE Where DF_MATCHINGCODE_STATE_CODE = 'CLOSED ENTRY');

			Set @lenmetr_idEntities = GET_LOADER_INFO('ID_ENTITIES', 1);
			Set @lenmetr_idTemplateLoader = GET_LOADER_INFO('ID_TEMPLATE_LOADER', 1);

            Set @lenmetr_idMatchStateCloseEntry = (
                        Select ID_DF_MATCHINGCODE_STATE 
                        From DF_MATCHINGCODE_STATE
                        Where DF_MATCHINGCODE_STATE_CLOSED = 1
                        Limit 1
                    )
                ;

			-- obtention de l'option de reconciliation authorisant le delettrage


			Set @lenmetr_codeReconcilLettre = 'RCONCIL-CLOSED'; 
			Set @lenmetr_codeReconcilOuvert = 'RCONCIL-OPENENT';


			set @lenmetr_deletrageAutorise = Coalesce(
						(
							Select
								Case When Coalesce(TMPLOPT_FSLT.TEMPLATE_OPTIONS_CODE, @lenmetr_codeReconcilOuvert) = @lenmetr_codeReconcilLettre 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 In (@lenmetr_codeReconcilLettre, @lenmetr_codeReconcilOuvert)
							where 1 = 1
								And TMPLDR_FSLT.ID_TEMPLATE_LOADER = @lenmetr_idTemplateLoader
						)
						, (
							Select
								Case When Coalesce(TMPLOPT_FSLT.TEMPLATE_OPTIONS_CODE, @lenmetr_codeReconcilOuvert) = @lenmetr_codeReconcilLettre 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 In (@lenmetr_codeReconcilLettre, @lenmetr_codeReconcilOuvert)
									) On ENTT.TEMPL_LOADER_ID_TEMPL_LOADER = TMPLDR_FSLT.ID_TEMPLATE_LOADER
							where 1 = 1
								And ENTT.ID_ENTITIES = @lenmetr_idEntities 
						)
						, 0
					)
				;
		end ;

		Drop Temporary Table If Exists TMP_LOADER_ENTRIES_TO_RECONCIL;
		Create Temporary Table TMP_LOADER_ENTRIES_TO_RECONCIL (
			TMP_LOADER_ENTRIES_TO_RECONCIL_ID Int Unsigned not null Primary Key auto_increment
			, ACCOUNTS_ID_ACCOUNTS Int Unsigned not null
			, ENTRIES_ID_ENTRIES Int Unsigned not null
            , MATCHINGCODES_UNMATCH_FORCED Smallint Not Null Default 0
			, index (ACCOUNTS_ID_ACCOUNTS)
			, index (ENTRIES_ID_ENTRIES)
		);
        Drop Temporary Table If exists TMP_LOADER_ENTRIES_TO_RECONCIL_DBLCHK;
        Create Temporary Table TMP_LOADER_ENTRIES_TO_RECONCIL_DBLCHK (
                    ENTRIES_ID_ENTRIES Int Unsigned not null
                    , ACCOUNTS_ID_ACCOUNTS Int Unsigned not null
                    , index (ENTRIES_ID_ENTRIES, ACCOUNTS_ID_ACCOUNTS)
                )
            ;

        -- ecritures ouvertes à réconcilier
-- 		Insert Into TMP_LOADER_ENTRIES_TO_RECONCIL(
		Insert Into TMP_LOADER_ENTRIES_TO_RECONCIL_DBLCHK(
			ACCOUNTS_ID_ACCOUNTS, ENTRIES_ID_ENTRIES
		)
        -- explain
		Select 
			EHA.ACCOUNTS_ID_ACCOUNTS
			, ENT.ID_ENTRIES
		From
			ACCOUNTS ACC
            Inner Join ENTRIES_has_ACCOUNTS EHA
                On EHA.ACCOUNTS_ID_ACCOUNTS = ACC.ID_ACCOUNTS
                And EHA.ENTRIES_PERMANENT_ATTACHEMENT = 1 
            Inner Join ENTRIES ENT
                On ENT.ID_ENTRIES = EHA.ENTRIES_ID_ENTRIES
            Left Join (
                LOADER_ENTRIES LE
                Inner Join LOADER_STATUS LDS
                    On LE.LOADER_STATUS_ID_LOADER_STATUS = LDS.ID_LOADER_STATUS
                    And LDS.LOADER_STATUS_ERRCOUNT = 0
            ) 
                -- ci dessous plus perf, mais n'inclu pas les ecriture en creation -> seront re-lettré
                -- car LE.ID_ENTRIES et de facto null pour les nouvelle ecritures
--                 On EHA.ENTRIES_ID_ENTRIES = LE.ID_ENTRIES
                On ACC.ACCOUNTS_REF = LE.ACCOUNTS_REF
                And EHA.ENTRIES_ACC_EXT_REF = LE.ENTRIES_ACC_EXT_REF
        Where 
            ACC.ENTITIES_ID_ENTITIES = @lenmetr_idEntities
            And ENT.DF_MATCH_STAT_ID_DF_MATCH_STAT <> @lenmetr_idMatchStateCloseEntry
            And LE.ID_LOADER_ENTRIES Is Null
--             And Not Exists (
--                 Select LE.ID_ENTRIES
--                 From LOADER_ENTRIES LE
--                     Inner Join LOADER_STATUS LDS
--                         On LE.LOADER_STATUS_ID_LOADER_STATUS = LDS.ID_LOADER_STATUS
--                 Where
--                     EHA.ENTRIES_ID_ENTRIES = LE.ID_ENTRIES
--                     And LDS.LOADER_STATUS_ERRCOUNT = 0
--                 Limit 1
--             )
        ;
        if  @lenmetr_deletrageAutorise = 1 Then
            -- ecritures fermées à délétter
            -- liste des ecritures dont au moins une ecriture du groupe de lettrage re-apparait 
            -- dans le fichier 
            -- il faut dans cette liste les ecriture du groupe de lettre abscent du fichier (pout re-lettrage)
            -- il ne faut pas dans cette liste les ecritures du groupe présants dans le fichcie (empecher re-lettrage)

            -- pour détécter d'envetuel doublons et en raison de la limitation Mysql
            -- on dupliquer la table TMP_LOADER_ENTRIES_TO_RECONCIL
--             Drop Temporary Table If exists TMP_LOADER_ENTRIES_TO_RECONCIL_DBLCHK;
--             Create Temporary Table TMP_LOADER_ENTRIES_TO_RECONCIL_DBLCHK (
--                         ENTRIES_ID_ENTRIES Int Unsigned not null
--                         , index (ENTRIES_ID_ENTRIES)
--                     )
--                 ;
--             Insert Into TMP_LOADER_ENTRIES_TO_RECONCIL_DBLCHK(ENTRIES_ID_ENTRIES)
--             Select ENTRIES_ID_ENTRIES From TMP_LOADER_ENTRIES_TO_RECONCIL;

--             Insert Into TMP_LOADER_ENTRIES_TO_RECONCIL(
            Insert Into TMP_LOADER_ENTRIES_TO_RECONCIL_DBLCHK(
                ACCOUNTS_ID_ACCOUNTS, ENTRIES_ID_ENTRIES
            )
            -- explain
            Select 
    -- 			distinct
                EHA.ACCOUNTS_ID_ACCOUNTS
                , ENT.ID_ENTRIES
            From
                ACCOUNTS ACC
                Inner Join ENTRIES_has_ACCOUNTS EHA
                    On EHA.ACCOUNTS_ID_ACCOUNTS = ACC.ID_ACCOUNTS
                Inner Join ENTRIES ENT
                    On ENT.ID_ENTRIES = EHA.ENTRIES_ID_ENTRIES
                Inner Join ENTRIES_MATCHCOD EM1
                    On ENT.ID_ENTRIES = EM1.ENTRIES_ID_ENTRIES
--                     
                Inner Join ENTRIES_MATCHCOD EM2
                    On EM1.MATCHCOD_ID_MATCHCOD = EM2.MATCHCOD_ID_MATCHCOD
                Inner Join LOADER_ENTRIES LE
                    On EM2.ENTRIES_ID_ENTRIES = LE.ID_ENTRIES
                Inner Join LOADER_STATUS LDS
                    On LE.LOADER_STATUS_ID_LOADER_STATUS = LDS.ID_LOADER_STATUS

            Where 1 = 1
                And ACC.ENTITIES_ID_ENTITIES = @lenmetr_idEntities
                And ENT.DF_MATCH_STAT_ID_DF_MATCH_STAT = @lenmetr_idMatchStateCloseEntry
                And EHA.ENTRIES_PERMANENT_ATTACHEMENT = 1 
                And EM1.ENTRIES_MATCHCOD_CURRENT = 1
                And EM2.ENTRIES_MATCHCOD_CURRENT = 1
                And LDS.LOADER_STATUS_ERRCOUNT = 0
--             Select 
--     -- 			distinct
--                 EHA.ACCOUNTS_ID_ACCOUNTS
--                 , ENT.ID_ENTRIES
--             From
--                 ACCOUNTS ACC
--                 Inner Join ENTRIES_has_ACCOUNTS EHA
--                     On EHA.ACCOUNTS_ID_ACCOUNTS = ACC.ID_ACCOUNTS
--                     And EHA.ENTRIES_PERMANENT_ATTACHEMENT = 1 
--                 Inner Join ENTRIES ENT
--                     On ENT.ID_ENTRIES = EHA.ENTRIES_ID_ENTRIES
--                 Inner Join DF_MATCHINGCODE_STATE DMS
--                     On ENT.DF_MATCH_STAT_ID_DF_MATCH_STAT = DMS.ID_DF_MATCHINGCODE_STATE
--                 Inner Join ENTRIES_MATCHCOD EM1
--                     On ENT.ID_ENTRIES = EM1.ENTRIES_ID_ENTRIES
--                     And EM1.ENTRIES_MATCHCOD_CURRENT = 1
--             Where 
--                 ACC.ENTITIES_ID_ENTITIES = @lenmetr_idEntities
-- --                 And DMS.DF_MATCHINGCODE_STATE_CLOSED = 1 -- tester si nécéssaire
--                 And Exists (
--                     Select LE.ID_ENTRIES
--                     From LOADER_ENTRIES LE
--                         Inner Join ENTRIES_MATCHCOD EM2
--                             On  EM2.ENTRIES_ID_ENTRIES = LE.ID_ENTRIES
--                     Where
--                         EM1.MATCHCOD_ID_MATCHCOD = EM2.MATCHCOD_ID_MATCHCOD
-- --                         And LE.ID_ENTRIES <> EM1.ENTRIES_ID_ENTRIES
--                     Limit 1
--                 )
--                 And Not Exists (
--                     Select ENTRIES_ID_ENTRIES
--                     From TMP_LOADER_ENTRIES_TO_RECONCIL_DBLCHK TDBLCHK
--                     Where TDBLCHK.ENTRIES_ID_ENTRIES = ENT.ID_ENTRIES
--                         Limit 1
--                 )
            ;


            -- dedoublonnage
            Insert Into TMP_LOADER_ENTRIES_TO_RECONCIL(
                ENTRIES_ID_ENTRIES, ACCOUNTS_ID_ACCOUNTS, MATCHINGCODES_UNMATCH_FORCED
            )
            Select Distinct ENTRIES_ID_ENTRIES, ACCOUNTS_ID_ACCOUNTS, 0
            From TMP_LOADER_ENTRIES_TO_RECONCIL_DBLCHK
            ;

            -- ici la table TMP_LOADER_ENTRIES_TO_RECONCIL peut contenir des ectiture à déléttrer 
            -- ecriture deja léttre, refournis dans le fichier
            -- supression de ces ecritures
--             Delete tar
-- --             select tar.*
--             From TMP_LOADER_ENTRIES_TO_RECONCIL tar
--                 Inner Join LOADER_ENTRIES LE
--                     On tar.ENTRIES_ID_ENTRIES = LE.ID_ENTRIES
--             ;
--             select tar.*
            Update TMP_LOADER_ENTRIES_TO_RECONCIL tar
                Inner Join LOADER_ENTRIES LE
                    On tar.ENTRIES_ID_ENTRIES = LE.ID_ENTRIES
                Inner Join LOADER_STATUS LDS
                    On LE.LOADER_STATUS_ID_LOADER_STATUS = LDS.ID_LOADER_STATUS
            Set tar.MATCHINGCODES_UNMATCH_FORCED = 1
            Where
                LDS.LOADER_STATUS_ERRCOUNT = 0
            ;
            
            -- ici la table TMP_LOADER_ENTRIES_TO_RECONCIL contient 
            -- les écriture à léttrer et à re-léttrer


        End if;

	End ;

End//
Delimiter ;