Delimiter //
/**
 * Find the id of the given sequence
 * muste be executed AFTER LOADER_REFDATALABEL_NORMALIZE_MAP_ID_ENT_REF_DATAS
 */
Drop Procedure If Exists LOADER_REFDATALABEL_NORMALIZE_MAP_ID_ENT_REF_DATAS_LNG//
Create Procedure LOADER_REFDATALABEL_NORMALIZE_MAP_ID_ENT_REF_DATAS_LNG(
    In batchnum Int
)
Begin
    

    Set @idEntities = GET_LOADER_INFO('ID_ENTITIES', batchnum);

    Update
        LOADER_STATUS LST
        Inner Join LOADER_REFDATALABEL ARD
            On ARD.LOADER_STATUS_ID_LOADER_STATUS = LST.ID_LOADER_STATUS
        Inner Join ENTITIES_REF_DATAS_LNG ERD
            On ARD.ID_ENTITIES_REF_DATAS = ERD.ENTT_RF_DATAS_ID_ENT_RF_DATAS
            And ARD.DF_LANGUAGES_ID_DF_LANGUAGES = ERD.DF_LANGUAGES_ID_DF_LANG
    Set
        ARD.ID_ENTITIES_REF_DATAS_LNG = ERD.ID_ENTITIES_REF_DATAS_LNG
    Where
        LST.LOADER_STATUS_BATCH_NUM = batchnum
    ;

    --
    -- table temporaire pour les libelle de références en création
    -- permet d'obtenir un auto increment
    DROP TEMPORARY TABLE IF EXISTS temp_ENTREFDATA_LABEL;
    CREATE TEMPORARY TABLE temp_ENTREFDATA_LABEL (
        ID_ENTITIES_REF_DATAS_LNG Int Unsigned Not Null Auto_increment
        , ID_ENTITIES_REF_DATAS Int Unsigned Null
        , NEW_ID_ENTITIES_REF_DATAS Int Unsigned Null
        , DF_LANGUAGES_ID_DF_LANGUAGES Int Unsigned Not Null
        , Primary Key (ID_ENTITIES_REF_DATAS_LNG)
        , index (ID_ENTITIES_REF_DATAS)
        , index (NEW_ID_ENTITIES_REF_DATAS)
    );

    -- insert des données
    Insert Into temp_ENTREFDATA_LABEL (ID_ENTITIES_REF_DATAS, NEW_ID_ENTITIES_REF_DATAS, DF_LANGUAGES_ID_DF_LANGUAGES)
    Select Distinct
		ID_ENTITIES_REF_DATAS, NEW_ID_ENTITIES_REF_DATAS, DF_LANGUAGES_ID_DF_LANGUAGES
    From    
        LOADER_STATUS LST
        Inner Join LOADER_REFDATALABEL ARD
            On ARD.LOADER_STATUS_ID_LOADER_STATUS = LST.ID_LOADER_STATUS
    Where 
        LST.LOADER_STATUS_ERRCOUNT = 0
		And ARD.ID_ENTITIES_REF_DATAS_LNG Is Null
        And LST.LOADER_STATUS_BATCH_NUM = batchnum
        
    ;

    -- mise à jour du champ NEW_ID_ENTITIES_REF_DATAS_LNG 
	-- pour les reférences existantes mais  sans libellé dans la langue

    Update
        LOADER_REFDATALABEL ARD
        Inner Join LOADER_STATUS LST
            On ARD.LOADER_STATUS_ID_LOADER_STATUS = LST.ID_LOADER_STATUS
        Inner Join temp_ENTREFDATA_LABEL TMPERD
            On ARD.ID_ENTITIES_REF_DATAS = TMPERD.ID_ENTITIES_REF_DATAS
            And ARD.DF_LANGUAGES_ID_DF_LANGUAGES = TMPERD.DF_LANGUAGES_ID_DF_LANGUAGES

    Set
        ARD.NEW_ID_ENTITIES_REF_DATAS_LNG = TMPERD.ID_ENTITIES_REF_DATAS_LNG
    Where 
        LST.LOADER_STATUS_ERRCOUNT = 0
		And LST.LOADER_STATUS_BATCH_NUM = batchnum

    ;

    -- mise à jour du champ NEW_ID_ENTITIES_REF_DATAS_LNG 
	-- pour les reférences en création

    Update
        LOADER_REFDATALABEL ARD
        Inner Join LOADER_STATUS LST
            On ARD.LOADER_STATUS_ID_LOADER_STATUS = LST.ID_LOADER_STATUS
        Inner Join temp_ENTREFDATA_LABEL TMPERD
            On ARD.NEW_ID_ENTITIES_REF_DATAS = TMPERD.NEW_ID_ENTITIES_REF_DATAS
            And ARD.DF_LANGUAGES_ID_DF_LANGUAGES = TMPERD.DF_LANGUAGES_ID_DF_LANGUAGES

    Set
        ARD.NEW_ID_ENTITIES_REF_DATAS_LNG = TMPERD.ID_ENTITIES_REF_DATAS_LNG
    Where 
        LST.LOADER_STATUS_ERRCOUNT = 0
		And LST.LOADER_STATUS_BATCH_NUM = batchnum

    ;

    

End//
Delimiter ;