Delimiter //
/**
**/
Drop Procedure If Exists LOADER_SUMMARIES_NORMALIZE_MAP_CREATE_OR_UPDATE//
Create Procedure LOADER_SUMMARIES_NORMALIZE_MAP_CREATE_OR_UPDATE(
    In batchnum Int
)
Begin
	-- recheche des historique à mettre à jour
	-- => historique avec le mode code déjà présent pour le couple compte/ecriture
	-- => flag historique LOADER_SUMMARIES_UPDATE_ALLOWED = 1
	-- => le flg historique vaut 1 si
	-- => - directement mappé avec 1
	-- => - le code catégorie de l'historique fait parti de ceux autorisant la MAJ
	-- comment il peut y avoir plusieurs historiques avec le même codes
	-- on ne met à jour que le dernier
	ldSmrsMpUpdt_initTable: Begin
		DROP TEMPORARY TABLE IF EXISTS TMP_SUMMARIES_4_UPDATE;
		CREATE TEMPORARY TABLE TMP_SUMMARIES_4_UPDATE (
			SUMMARIES_ID_SUMMARIES Int Unsigned Null 
			, LOADER_SUMMARIES_ID_LOADER_SUMMARIES Int Unsigned Not Null
			, index (LOADER_SUMMARIES_ID_LOADER_SUMMARIES)
			, index (SUMMARIES_ID_SUMMARIES)
		);
		
	End ;
	ldSmrsMpUpdt_accSumaries: Begin
		-- historiques lié aux Comptes
		-- on ne dois pas joindre à la fois sur les écritures
		-- et les comptes pour des raison de performances
		-- on utilise un table temporaire intermédiaire pour des raisons de performances

		Drop Temporary Table If Exists TMP_ACCOUNTS_SUMMARIES;
# 		Create Temporary Table TMP_ACCOUNTS_SUMMARIES As
		Create Temporary Table TMP_ACCOUNTS_SUMMARIES (
                    ACCOUNTS_ID_ACCOUNTS Int Unsigned
                    , DF_SUMMARIES_ID_DF_SUMMR Int Unsigned
                    , LOADER_SUMMARIES_ID_LOADER_SUMMARIES Int Unsigned

                    , Index IX_TMP_ACCOUNTS_SUMMARIES_01 (ACCOUNTS_ID_ACCOUNTS, DF_SUMMARIES_ID_DF_SUMMR)
                    , Index IX_TMP_ACCOUNTS_SUMMARIES_02 (LOADER_SUMMARIES_ID_LOADER_SUMMARIES)
                    , Index IX_TMP_ACCOUNTS_SUMMARIES_03 (DF_SUMMARIES_ID_DF_SUMMR)
		        )
		    ;
		Insert Into TMP_ACCOUNTS_SUMMARIES (
		        ACCOUNTS_ID_ACCOUNTS, DF_SUMMARIES_ID_DF_SUMMR
		        , LOADER_SUMMARIES_ID_LOADER_SUMMARIES
		    )
		Select
			LDS.ACCOUNTS_ID_ACCOUNTS, LDS.DF_SUMMARIES_ID_DF_SUMMR
			, LDS.ID_LOADER_SUMMARIES LOADER_SUMMARIES_ID_LOADER_SUMMARIES
		From
			LOADER_SUMMARIES LDS
			Inner Join LOADER_STATUS LST
				On LDS.LOADER_STATUS_ID_LOADER_STATUS = LST.ID_LOADER_STATUS
		Where 1 = 1 
			And LST.LOADER_STATUS_BATCH_NUM = batchnum
			And LST.LOADER_STATUS_ERRCOUNT = 0
			And LDS.LOADER_SUMMARIES_UPDATE_ALLOWED = 1
			And LDS.ENTRIES_ID_ENTRIES Is Null
			And LDS.ACCOUNTS_ID_ACCOUNTS Is Not Null
			And LDS.DF_SUMMARIES_ID_DF_SUMMR Is Not Null
		;
# 		Alter Table TMP_ACCOUNTS_SUMMARIES
# 				Add Index IX_TMP_ACCOUNTS_SUMMARIES_01 (ACCOUNTS_ID_ACCOUNTS, DF_SUMMARIES_ID_DF_SUMMR)
# 				, Add Index IX_TMP_ACCOUNTS_SUMMARIES_02 (LOADER_SUMMARIES_ID_LOADER_SUMMARIES)
# 			;

		Insert Into TMP_SUMMARIES_4_UPDATE (
			SUMMARIES_ID_SUMMARIES, LOADER_SUMMARIES_ID_LOADER_SUMMARIES
		)
		Select
		    (
	            Select SMS.ID_SUMMARIES
	            From SUMMARIES SMS
                Where LDS.ACCOUNTS_ID_ACCOUNTS = SMS.ACCOUNTS_ID_ACCOUNTS
				    And LDS.DF_SUMMARIES_ID_DF_SUMMR = SMS.DF_SUMMARIES_ID_DF_SUMMR
	            Order By 1 Desc
	            Limit 1
		    ) MX_ID_SUMMARIES
			, LDS.LOADER_SUMMARIES_ID_LOADER_SUMMARIES
		From
			TMP_ACCOUNTS_SUMMARIES LDS
		;
# 		Select
# 			Max(SMS.ID_SUMMARIES)
# 			, LDS.LOADER_SUMMARIES_ID_LOADER_SUMMARIES
# 		From
# 			TMP_ACCOUNTS_SUMMARIES LDS
# 			Inner Join SUMMARIES SMS
# 				On LDS.ACCOUNTS_ID_ACCOUNTS = SMS.ACCOUNTS_ID_ACCOUNTS
# 				And LDS.DF_SUMMARIES_ID_DF_SUMMR = SMS.DF_SUMMARIES_ID_DF_SUMMR
# 		Group By LDS.LOADER_SUMMARIES_ID_LOADER_SUMMARIES
# 		;
		Drop Temporary Table If Exists TMP_ACCOUNTS_SUMMARIES;

	End ;
	
	ldSmrsMpUpdt_entSumaries: Begin
		Drop Temporary Table If Exists TMP_ENTRIES_SUMMARIES;
# 		Create Temporary Table TMP_ENTRIES_SUMMARIES As
		Create Temporary Table TMP_ENTRIES_SUMMARIES (
                    ENTRIES_ID_ENTRIES Int Unsigned
                    , DF_SUMMARIES_ID_DF_SUMMR Int Unsigned
                    , LOADER_SUMMARIES_ID_LOADER_SUMMARIES Int Unsigned

                    , Index IX_TMP_ACCOUNTS_SUMMARIES_01 (ENTRIES_ID_ENTRIES, DF_SUMMARIES_ID_DF_SUMMR)
                    , Index IX_TMP_ACCOUNTS_SUMMARIES_02 (LOADER_SUMMARIES_ID_LOADER_SUMMARIES)
                    , Index IX_TMP_ACCOUNTS_SUMMARIES_03 (DF_SUMMARIES_ID_DF_SUMMR)
		        )
            ;
        Insert Into TMP_ENTRIES_SUMMARIES (
               ENTRIES_ID_ENTRIES, DF_SUMMARIES_ID_DF_SUMMR
               , LOADER_SUMMARIES_ID_LOADER_SUMMARIES
           )
		Select
			LDS.ENTRIES_ID_ENTRIES, LDS.DF_SUMMARIES_ID_DF_SUMMR
			, LDS.ID_LOADER_SUMMARIES LOADER_SUMMARIES_ID_LOADER_SUMMARIES
		From
			LOADER_SUMMARIES LDS
			Inner Join LOADER_STATUS LST
				On LDS.LOADER_STATUS_ID_LOADER_STATUS = LST.ID_LOADER_STATUS
		Where 1 = 1 
			And LST.LOADER_STATUS_BATCH_NUM = batchnum
			And LST.LOADER_STATUS_ERRCOUNT = 0
			And LDS.LOADER_SUMMARIES_UPDATE_ALLOWED = 1
# 			And LDS.ACCOUNTS_ID_ACCOUNTS Is Null
			And LDS.ENTRIES_ID_ENTRIES Is Not Null
			And LDS.DF_SUMMARIES_ID_DF_SUMMR Is Not Null
		;
# 		Alter Table TMP_ENTRIES_SUMMARIES
# 				Add Index IX_TMP_ENTRIES_SUMMARIES_01 (ENTRIES_ID_ENTRIES, DF_SUMMARIES_ID_DF_SUMMR)
# 				, Add Index IX_TMP_ENTRIES_SUMMARIES_02 (LOADER_SUMMARIES_ID_LOADER_SUMMARIES)
# 			;

		Insert Into TMP_SUMMARIES_4_UPDATE (
			SUMMARIES_ID_SUMMARIES, LOADER_SUMMARIES_ID_LOADER_SUMMARIES
		)
		Select
			(
			    Select SMS.ID_SUMMARIES
	            From SUMMARIES SMS
                Where LDS.ENTRIES_ID_ENTRIES = SMS.ENTRIES_ID_ENTRIES
                    And LDS.DF_SUMMARIES_ID_DF_SUMMR = SMS.DF_SUMMARIES_ID_DF_SUMMR
	            Order By 1 Desc
	            Limit 1
			)
			, LDS.LOADER_SUMMARIES_ID_LOADER_SUMMARIES
		From
			TMP_ENTRIES_SUMMARIES LDS
		;

# 		Select
# 			Max(SMS.ID_SUMMARIES)
# 			, LDS.LOADER_SUMMARIES_ID_LOADER_SUMMARIES
# 		From
# 			TMP_ENTRIES_SUMMARIES LDS
# 			Inner Join SUMMARIES SMS
# 				On LDS.ENTRIES_ID_ENTRIES = SMS.ENTRIES_ID_ENTRIES
# 				And LDS.DF_SUMMARIES_ID_DF_SUMMR = SMS.DF_SUMMARIES_ID_DF_SUMMR
# 		Group By LDS.LOADER_SUMMARIES_ID_LOADER_SUMMARIES
# 		;
		Drop Temporary Table If Exists TMP_ENTRIES_SUMMARIES;
	End ;

	ldSmrsMpUpdt_updateLdSumaries: Begin
		-- -- Update new entries with their new id
		UPDATE 
			LOADER_SUMMARIES LDS
			Inner Join TMP_SUMMARIES_4_UPDATE TS4U
				On LDS.ID_LOADER_SUMMARIES  = TS4U.LOADER_SUMMARIES_ID_LOADER_SUMMARIES
		SET
			LDS.ID_SUMMARIES = TS4U.SUMMARIES_ID_SUMMARIES
		Where
			TS4U.SUMMARIES_ID_SUMMARIES Is Not Null
		;
	End ;

	ldSmrsMpUpdt_clear: Begin
		DROP TEMPORARY TABLE IF EXISTS TMP_SUMMARIES_4_UPDATE;
	End ;
End//
Delimiter ;
