Delimiter //
/**
 * Author:  armen
 * Created: 27 juil. 2017
 */

Drop Procedure if Exists LOADER_EDMINDEX_NORMALIZE_CHECK_KEYWORD_EXISTS//
Create Procedure LOADER_EDMINDEX_NORMALIZE_CHECK_KEYWORD_EXISTS (
    In batchnum int
	, In KeywordNo Int 
)
Begin
		set @lencke_batchnum = batchnum;
		set @lencke_KeywordNo = KeywordNo;
		Set @lencke_idAdminParams = GET_LOADER_INFO('ID_ADMIN_PARAMS', batchnum);
		Set @lencke_Query = Concat(
				'Insert Into LOADER_EDMINDEX_DOCUMENTS_HAS_KEYWORDS( '
				, '		LOADER_STATUS_ID_LOADER_STATUS '
				, '		, LOADER_EDMINDEX_ID_LOADER_EDMINDEX '
				, '		, DF_KEYWORDS_CODE '
				, '	) '
				, '	Select '
				, '		LST.ID_LOADER_STATUS, LEDM.ID_LOADER_EDMINDEX '
				, '		, LEDM.DOCUMENTS_KEYWORD', @lencke_KeywordNo, ' '
				, '	From '
				, '		LOADER_EDMINDEX LEDM '
				, '        Inner Join LOADER_STATUS LST '
				, '            On LST.ID_LOADER_STATUS = LEDM.LOADER_STATUS_ID_LOADER_STATUS '
				, '	Where  '
				, '        LST.LOADER_STATUS_BATCH_NUM = ? '
				, '		And Coalesce(LEDM.DOCUMENTS_KEYWORD', @lencke_KeywordNo, ', '''') <> '''' '
			)
		;

		Prepare lencke_stmt From @lencke_Query;
		execute lencke_stmt using @lencke_batchnum	;	
		Deallocate prepare lencke_stmt;

		
		Update
			LOADER_EDMINDEX_DOCUMENTS_HAS_KEYWORDS EDMKW
			Inner Join LOADER_EDMINDEX LED
                On EDMKW.LOADER_EDMINDEX_ID_LOADER_EDMINDEX = LED.ID_LOADER_EDMINDEX
			Inner Join LOADER_STATUS LST
                On LST.ID_LOADER_STATUS = LED.LOADER_STATUS_ID_LOADER_STATUS
		Set
			EDMKW.ID_DF_KEYWORDS = (
                Select DFK.ID_DF_KEYWORDS
                From DF_KEYWORDS DFK
                Where
                    EDMKW.DF_KEYWORDS_CODE = DFK.DF_KEYWORDS_CODE
                    And DFK.ADMIN_PARAMS_ID_ADMIN_PARAMS = @lencke_idAdminParams
                Limit 1
            )
			, LOADER_EDMINDEX_KEYWORDS_TREATED = 1 
		Where
			LOADER_EDMINDEX_KEYWORDS_TREATED = 0
            And LST.LOADER_STATUS_BATCH_NUM = @lencke_batchnum
            And Exists (
                Select DFK1.ID_DF_KEYWORDS
                From DF_KEYWORDS DFK1
                Where
                    EDMKW.DF_KEYWORDS_CODE = DFK1.DF_KEYWORDS_CODE
                    And DFK1.ADMIN_PARAMS_ID_ADMIN_PARAMS = @lencke_idAdminParams
                Limit 1
            )
		;

        -- maj des erreur
        Update
            LOADER_EDMINDEX_DOCUMENTS_HAS_KEYWORDS EDMKW
			Inner Join LOADER_EDMINDEX LED
                On EDMKW.LOADER_EDMINDEX_ID_LOADER_EDMINDEX = LED.ID_LOADER_EDMINDEX
			Inner Join LOADER_STATUS LST
                On LST.ID_LOADER_STATUS = LED.LOADER_STATUS_ID_LOADER_STATUS
        Set 
            LST.LOADER_STATUS_WARNCOUNT = LST.LOADER_STATUS_WARNCOUNT + 1
            , LST.LOADER_STATUS_WARNMSGS = LOADER_CONCAT_ERR(LST.LOADER_STATUS_WARNMSGS, Concat('Mot clé inexistant :', EDMKW.DF_KEYWORDS_CODE )) 
        Where
            LOADER_EDMINDEX_KEYWORDS_TREATED = 1
			And EDMKW.ID_DF_KEYWORDS Is Null
            And LST.LOADER_STATUS_BATCH_NUM = @lencke_batchnum
        ;



-- maj des id
-- 		Update
-- 			LOADER_EDMINDEX_DOCUMENTS_HAS_KEYWORDS EDMKW
-- 			Inner Join DF_KEYWORDS DFK
-- 				On EDMKW.DF_KEYWORDS_CODE = DFK.DF_KEYWORDS_CODE
-- 			Inner Join LOADER_STATUS LST
--                 On LST.ID_LOADER_STATUS = EDMKW.LOADER_STATUS_ID_LOADER_STATUS
-- 		Set
-- 			EDMKW.ID_DF_KEYWORDS = DFK.ID_DF_KEYWORDS
-- 			, LOADER_EDMINDEX_KEYWORDS_TREATED = 1 
-- 		Where
-- 			LOADER_EDMINDEX_KEYWORDS_TREATED = 0
-- 			And DFK.ADMIN_PARAMS_ID_ADMIN_PARAMS = @lencke_idAdminParams
-- 		;
-- 		-- maj des erreur
--         Update
--             LOADER_EDMINDEX_DOCUMENTS_HAS_KEYWORDS EDMKW
-- 			Inner Join LOADER_STATUS LST
--                 On LST.ID_LOADER_STATUS = EDMKW.LOADER_STATUS_ID_LOADER_STATUS
--         Set 
--             LST.LOADER_STATUS_WARNCOUNT = LST.LOADER_STATUS_WARNCOUNT + 1
--             , LST.LOADER_STATUS_WARNMSGS = LOADER_CONCAT_ERR(LST.LOADER_STATUS_WARNMSGS, Concat('Mot clé inexistant :', EDMKW.DF_KEYWORDS_CODE )) 
--             , LOADER_EDMINDEX_KEYWORDS_TREATED = 1 
--         Where
--             LOADER_EDMINDEX_KEYWORDS_TREATED = 0
-- 			And EDMKW.ID_DF_KEYWORDS Is Null
--         ;
		
End //
Delimiter ;