 Delimiter //
/**
 * If language code is provided, and is in application, set language id
 * 
 * @param int batchnum
 * @author Benoit
 * @ticket #2125
 */ 
 Drop Procedure if Exists LOADER_CONTACTS_NORMALIZE_MAP_LANGUAGE_PROVIDED//
 
Create Procedure LOADER_CONTACTS_NORMALIZE_MAP_LANGUAGE_PROVIDED (
    In batchnum int
 )
 Begin

    Set @idAdminParams = GET_LOADER_INFO('ID_ADMIN_PARAMS', batchnum);

    Set @idDefaultLanguage = (
                            Select A.DF_LANGUAGES_ID_DEF_LANGUAGE 
                            From ADMIN_PARAMS A 
                            Where A.ID_ADMIN_PARAMS = @idAdminParams
                        )
                    ;
 

    Set @sDefLangCode = (
                        select DF_LANGUAGES_CODE
                        from DF_LANGUAGES 
                        where ID_DF_LANGUAGES = @idDefaultLanguage
                        limit 1 
                    )
                ;

    -- pour retro compatibilité
    -- gestion du code langue incorrect pour ANGLAIS (en_EN au lieu de en_GB)
    Set @iEnGbLang = (
                        select ID_DF_LANGUAGES 
                        from DF_LANGUAGES 
                        where DF_LANGUAGES_CODE like 'en%' 
                        ORDER by ID_DF_LANGUAGES limit 1 
                    )
                ;
    Set @sWrongEnLn = 'en_EN';

    UPDATE
        LOADER_CONTACTS C
        INNER JOIN LOADER_STATUS S
            ON S.ID_LOADER_STATUS = C.LOADER_STATUS_ID_LOADER_STATUS
        LEFT JOIN DF_LANGUAGES L
            ON L.DF_LANGUAGES_CODE = C.DF_LANGUAGES_CODE
    SET
--         C.DF_LANGUAGES_ID_DF_LANGUAGES = 
--             (CASE WHEN L.ID_DF_LANGUAGES IS NOT NULL
--                 THEN L.ID_DF_LANGUAGES
--                 ELSE @idDefaultLanguage
--             END)
        C.DF_LANGUAGES_ID_DF_LANGUAGES = (
                CASE 
                    WHEN L.ID_DF_LANGUAGES IS NOT NULL THEN L.ID_DF_LANGUAGES
                    When C.DF_LANGUAGES_CODE = @sWrongEnLn Then @iEnGbLang
                    ELSE @idDefaultLanguage
                END
            )
        , S.LOADER_STATUS_WARNCOUNT = 
            (CASE WHEN L.ID_DF_LANGUAGES IS NULL
                THEN S.LOADER_STATUS_WARNCOUNT + 1
                ELSE S.LOADER_STATUS_WARNCOUNT
            END)
--         , S.LOADER_STATUS_WARNMSGS = 
--             (CASE WHEN L.ID_DF_LANGUAGES IS NULL
--                 THEN LOADER_CONCAT_ERR(S.LOADER_STATUS_WARNMSGS, Concat('Code langue inexistant : ', C.DF_LANGUAGES_CODE) )
--                 ELSE S.LOADER_STATUS_WARNMSGS
--             END)
        , S.LOADER_STATUS_WARNMSGS = (
                CASE 
                    WHEN ( L.ID_DF_LANGUAGES IS NULL ) And (C.DF_LANGUAGES_CODE = @sWrongEnLn) THEN 
                        LOADER_CONCAT_ERR(S.LOADER_STATUS_WARNMSGS, Concat('unknown language code : ', C.DF_LANGUAGES_CODE, ', replaced by en_GB') )
                    WHEN L.ID_DF_LANGUAGES IS NULL THEN
                         LOADER_CONCAT_ERR(S.LOADER_STATUS_WARNMSGS, Concat('unknown language code : ', C.DF_LANGUAGES_CODE, ', replaced by the default code ', @sDefLangCode) )
--                          LOADER_CONCAT_ERR(S.LOADER_STATUS_WARNMSGS, Concat('unknown language code : ', C.DF_LANGUAGES_CODE, ', replaced by the default code ') )
                    ELSE S.LOADER_STATUS_WARNMSGS
                END
            )
    WHERE 
        S.LOADER_STATUS_BATCH_NUM = batchnum
        AND Coalesce(C.DF_LANGUAGES_CODE, '') != ''
    ;
    
 End //
 Delimiter ;