Delimiter //
/**
 * cette procédure doit être lancé apres les procédures 
 * - LOADER_ENTRIES_NORMALIZE_MAP_ENTRIES_TO_RECONCIL;
 * - call LOADER_ENTRIES_NORMALIZE_MAP_RECONCILIATIONS_ENTRIES;
 * elle se base sur les tables temporaires 
 * - TMP_LOADER_ENTRIES_TO_RECONCIL
 * - TMP_LOADER_RECONCILIATIONS_ENTRIES
 * Cette procédure ajoute dans les tables 
 * - LOADER_MATCHINGCODES, 
 * - LOADER_ENTRIES
 * - LOADER_STATUS
 * les données nécéssaire à la création des ecritures et des lettrage de reconciliations
 * on réalise ces traitements dans 2 lots supplémentaires
 */  
Drop Procedure If Exists LOADER_ENTRIES_NORMALIZE_MAP_RECONCILIATION_PREPDATA//
Create Procedure LOADER_ENTRIES_NORMALIZE_MAP_RECONCILIATION_PREPDATA(
	In batchnum Int
)
Begin


	set @lenmrp_batchnum = batchnum;
-- 	TMP_LOADER_ENTRIES_TO_RECONCIL
	call LOADER_ENTRIES_NORMALIZE_MAP_RECONCILIATION_PREP_LOADER_TABLES;
	
	-- obtention du statu d'ecriture ouverte
	Set @lenmrp_openStatId = ( 
			Select min(ID_DF_MATCHINGCODE_STATE) 
			From DF_MATCHINGCODE_STATE 
			Where DF_MATCHINGCODE_STATE_CODE = 'OPEN ENTRY' 
				And DF_MATCHINGCODE_STATE_OPEN = 1 
				And DF_MATCHINGCODE_STATE_PARTIAL = 0
				And DF_MATCHINGCODE_STATE_CLOSED = 0
		)
	;
	Set @lenmrp_closedStatId = ( 
			Select min(ID_DF_MATCHINGCODE_STATE) 
			From DF_MATCHINGCODE_STATE 
			Where DF_MATCHINGCODE_STATE_CODE = 'CLOSED ENTRY' 
				And DF_MATCHINGCODE_STATE_OPEN = 0 
				And DF_MATCHINGCODE_STATE_PARTIAL = 0
				And DF_MATCHINGCODE_STATE_CLOSED = 1
		)
	;

	-- obtention du dernier id de la table loader_status
	-- afin d'identifier les nouvelles lignes
	Set @lenmrp_loaderStatusIdMaxBeforEntries = Coalesce((Select max(ID_LOADER_STATUS) From LOADER_STATUS), 0);
	-- obtention du nouvelle new_id pour les nouvelles ecritures
	Set @lenmrp_entriesIdNewMax = Coalesce((Select max(ID_ENTRIES_NEW) From LOADER_ENTRIES), 0);
	
	-- obtention du dernier BATCH issu du fichier
	
	-- ajout des la liste des nouvelles ecritures dans la tables loader status 
	-- avec le batch  =maxbatch +1
	Insert Into LOADER_STATUS (
		ID_LOADER_STATUS, SRC_TABLE_ID, LOADER_STATUS_BATCH_NUM
	)
	Select 
		@lenmrp_loaderStatusIdMaxBeforEntries + ENTRIES_ID_ENTRIES_NEW
		, null, @lenmrp_batchnum + 1
		
	from TMP_LOADER_RECONCILIATIONS_ENTRIES
	;

	-- un suffix pour les nouveau référence d'ecriture et code lettrage
	-- le suffix est une sorte de saller qui va servir dans ue fonction de hashage
	-- àa permet d'eviter les collisions
	Set @lenmrp_suffixRefEcr = date_format(now(), '%Y%m%d%H%i');
	Set @lenmrp_suffixlettr = date_format(now(), '%y%m%d');

	-- on ajoute la liste des nouvelles ecritures
	Insert Into LOADER_ENTRIES (
		LOADER_STATUS_ID_LOADER_STATUS
		, ENTRIES_ACC_EXT_REF, ENTRIES_REF
		, ACCOUNTS_ID_ACCOUNTS, ACCOUNTS_REF
-- 		, ID_ENTRIES_NEW
		, DF_ENTR_TYPE_ID_DF_ENTR_TYPE
-- 		, ENTRIES_ENT_DEBIT, ENTRIES_ENT_CREDIT, ENTRIES_ENT_BALANCE
		, ENT_DEBIT, ENT_CREDIT, ENT_BALANCE
-- 		, DF_MATCH_STAT_ID_DF_MATCH_STAT
		, ENTRIES_LABEL
	)
	Select 
		@lenmrp_loaderStatusIdMaxBeforEntries + ENTRIES_ID_ENTRIES_NEW
		-- reference construite
		, Concat('RECONS.',  @lenmrp_suffixRefEcr, '.', encrypt(concat(ACC.ACCOUNTS_REF, '.', @lenmrp_suffixRefEcr), 'RC'))
		, Concat('RECONS.',  @lenmrp_suffixRefEcr, '.', encrypt(concat(ACC.ACCOUNTS_REF, '.', @lenmrp_suffixRefEcr), 'RC'))
		, ACCOUNTS_ID_ACCOUNTS, ACC.ACCOUNTS_REF
-- 		, ENTRIES_ID_ENTRIES_NEW + @lenmrp_entriesIdNewMax
		, DF_ENTR_TYPE_ID_DF_ENTR_TYPE
-- 		, ENTRIES_ENT_DEBIT, ENTRIES_ENT_CREDIT, ENTRIES_ENT_BALANCE
		, ENT_DEBIT, ENT_CREDIT, ENT_BALANCE
-- 		, @lenmrp_openStatId
-- 		, @lenmrp_closedStatId
		, ENTRIES_LABEL
	from TMP_LOADER_RECONCILIATIONS_ENTRIES
		Inner Join ACCOUNTS ACC
			On TMP_LOADER_RECONCILIATIONS_ENTRIES.ACCOUNTS_ID_ACCOUNTS = ACC.ID_ACCOUNTS
	;
	
	-- ici les nouvelle ecritures sont pretes à etre intéger
	
	-- ---------------------------------------------------------------------------------
	-- ---------------------------------------------------------------------------------
	--				gestion des lettrage
	--
	-- on ajoute la liste des nouveaux lettrages
	-- elle comprennes les nouvelle ecritures ainsi que 
	-- les ecritures à reconcilier
    -- les ecriture à delettre (flag MATCHINGCODES_UNMATCH_FORCED = 1)
	-- on utilise le batch maxbatch +2
	-- ---------------------------------------------------------------------------------
	-- ---------------------------------------------------------------------------------
	Set @lenmrp_loaderStatusIdMaxBeforMatchCdeNewEntr = Coalesce((Select max(ID_LOADER_STATUS) From LOADER_STATUS), 0);

	Insert Into LOADER_STATUS (
		ID_LOADER_STATUS, SRC_TABLE_ID, LOADER_STATUS_BATCH_NUM
	)
	Select 
		@lenmrp_loaderStatusIdMaxBeforMatchCdeNewEntr + ENTRIES_ID_ENTRIES_NEW
		, null, @lenmrp_batchnum + 2
		
	from TMP_LOADER_RECONCILIATIONS_ENTRIES
	;
	

	-- en mode faste, on ne dois pas forcer le status de lettrage pour permetre le recalcul correct
	-- des status de lettrage sur les autres écritures du lot de lettrage
	Set @lenmrp_idEntities = GET_LOADER_INFO('ID_ENTITIES', @lenmrp_batchnum);
	Set @lenmrp_idTemplateLoader = GET_LOADER_INFO('ID_TEMPLATE_LOADER', @lenmrp_batchnum);

		-- obtention du mode fast du schema utilisé
	    -- , si non paramtré utilisation du mode fast du schema par défaut de l'entitié
		-- sinon pas d'utilisation du mode fast par défaut
		set @lenmrp_fastStatusModeCode = 'MTCHNG-FAST-STT' ;
		set @lenmrp_avgStatusModeCode = 'MTCHNG-AVG-STT' ;

		set @lenmrp_fastStatusMode = Coalesce(
					(
						Select
							Case Coalesce(TMPLOPT_FSLT.TEMPLATE_OPTIONS_CODE, 'FULL') 
								When @lenmrp_fastStatusModeCode Then 1 
								When @lenmrp_avgStatusModeCode Then 2 
								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 (@lenmrp_fastStatusModeCode, @lenmrp_avgStatusModeCode)
						where 1 = 1
							And TMPLDR_FSLT.ID_TEMPLATE_LOADER = @lenmrp_idTemplateLoader
					)
					, (
						Select
							Case Coalesce(TMPLOPT_FSLT.TEMPLATE_OPTIONS_CODE, 'FULL') 
								When @lenmrp_fastStatusModeCode Then 1 
								When @lenmrp_avgStatusModeCode Then 2 
								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 (@lenmrp_fastStatusModeCode, @lenmrp_avgStatusModeCode)
								) On ENTT.TEMPL_LOADER_ID_TEMPL_LOADER = TMPLDR_FSLT.ID_TEMPLATE_LOADER
						where 1 = 1
							And ENTT.ID_ENTITIES = @lenmrp_idEntities 
					)
					, 0
				)
			;

	Set @lenmrp_forcedMatchingCodeState = (Case When @lenmrp_fastStatusMode = 1 Then Null Else 'CLOSED ENTRY' End);


	Insert into LOADER_MATCHINGCODES (
		LOADER_STATUS_ID_LOADER_STATUS
-- 		, ID_ENTRIES
-- 		, MATCHINGCODES_CODE
		, MATCHINGCODES_ACCOUNTS_CODE -- pour forcer une réconciliation par compte
		, ACCOUNTS_REF, ENTRIES_ACC_EXT_REF
		, MATCHINGCODES_DATE, MATCHINGCODES_BATCH_NUM
		, MATCHINGCODES_STATE_CODE
	)
	Select
		distinct
		@lenmrp_loaderStatusIdMaxBeforMatchCdeNewEntr + ENTRIES_ID_ENTRIES_NEW -- TMP
-- 		, ENTRIES_ID_ENTRIES_NEW
		, Concat( @lenmrp_suffixlettr, '.', encrypt(concat(ACC.ACCOUNTS_REF, '.', @lenmrp_suffixRefEcr), 'RC'))
		, ACC.ACCOUNTS_REF, Concat('RECONS.',  @lenmrp_suffixRefEcr, '.', encrypt(concat(ACC.ACCOUNTS_REF, '.', @lenmrp_suffixRefEcr), 'RC'))
		, curdate(), @lenmrp_batchnum + 2

		, @forcedMatchingCodeState
-- 		, 'CLOSED ENTRY' 

	from TMP_LOADER_RECONCILIATIONS_ENTRIES
		Inner Join ACCOUNTS ACC
			On TMP_LOADER_RECONCILIATIONS_ENTRIES.ACCOUNTS_ID_ACCOUNTS = ACC.ID_ACCOUNTS
	;


	-- ajout des ecritures a (re)letter
	-- on dois les ajouter dans la table loader status
	Set @lenmrp_loaderStatusIdMaxBeforMatchCdeOldEntr = Coalesce((Select max(ID_LOADER_STATUS) From LOADER_STATUS), 1);
	Insert Into LOADER_STATUS (
		ID_LOADER_STATUS, SRC_TABLE_ID, LOADER_STATUS_BATCH_NUM
	)
	Select 
	distinct
		@lenmrp_loaderStatusIdMaxBeforMatchCdeOldEntr + TMP_LOADER_ENTRIES_TO_RECONCIL_ID
		, null, @lenmrp_batchnum + 2
		
	from TMP_LOADER_ENTRIES_TO_RECONCIL
	;


	-- puis on les ajoute les ecritures
	Insert into LOADER_MATCHINGCODES (
		LOADER_STATUS_ID_LOADER_STATUS, ID_ENTRIES
-- 		, MATCHINGCODES_CODE
		, MATCHINGCODES_ACCOUNTS_CODE
		, ACCOUNTS_REF, ENTRIES_ACC_EXT_REF
		, MATCHINGCODES_DATE, MATCHINGCODES_BATCH_NUM
		, MATCHINGCODES_STATE_CODE
        , MATCHINGCODES_UNMATCH_FORCED
	)
	Select 
	distinct
		@lenmrp_loaderStatusIdMaxBeforMatchCdeOldEntr + TMP_LOADER_ENTRIES_TO_RECONCIL_ID -- TMP
		, TLER.ENTRIES_ID_ENTRIES
		, Concat( @lenmrp_suffixlettr, '.', encrypt(concat(ACC.ACCOUNTS_REF, '.', @lenmrp_suffixRefEcr), 'RC'))
		, ACC.ACCOUNTS_REF, EHA.ENTRIES_ACC_EXT_REF
		, curdate(), @lenmrp_batchnum + 2

-- 		, @lenmrp_forcedMatchingCodeState
        , Case TLER.MATCHINGCODES_UNMATCH_FORCED When 1 Then Null Else @lenmrp_forcedMatchingCodeState End
        , TLER.MATCHINGCODES_UNMATCH_FORCED
    -- select *
	from TMP_LOADER_ENTRIES_TO_RECONCIL TLER
		Inner Join ACCOUNTS ACC
			On TLER.ACCOUNTS_ID_ACCOUNTS = ACC.ID_ACCOUNTS
		Inner Join ENTRIES_has_ACCOUNTS EHA
			On TLER.ACCOUNTS_ID_ACCOUNTS = EHA.ACCOUNTS_ID_ACCOUNTS
			AND TLER.ENTRIES_ID_ENTRIES = EHA.ENTRIES_ID_ENTRIES
			AND EHA.ENTRIES_PERMANENT_ATTACHEMENT = 1 
		Left Join LOADER_MATCHINGCODES LM
			On ACC.ACCOUNTS_REF = LM.ACCOUNTS_REF
			And ACC.ENTITIES_ID_ENTITIES = @lenmrp_idEntities
			And EHA.ENTRIES_ACC_EXT_REF = LM.ENTRIES_ACC_EXT_REF
	Where
		-- non encore présent dans la table
		LM.LOADER_STATUS_ID_LOADER_STATUS Is Null
	;

End//
Delimiter ;