Delimiter //
/**
 * Author:  armen
 * Created: 30 janv. 2018
 */
Drop Procedure if exists LOADER_MEMBERSACCRED_NORMALIZE_MAP_MEMBERS_REFDATA_PROVIDED//

Create Procedure LOADER_MEMBERSACCRED_NORMALIZE_MAP_MEMBERS_REFDATA_PROVIDED (
    In batchnum int
    , In refSequence smallint
    , In refDataType varchar(25)
)
Begin

    -- purge de la table des refdata
    call LOADER_CREATE_TABLE_MEMBERSACCRED_REFDATA(); -- attention auto-comit, voir si génant

--     Set @id_admin_params = GET_LOADER_INFO('ID_ADMIN_PARAMS', batchnum);
    Set @refDataType = Coalesce(refDataType, 'ACCRED_ACCOUNTS');
    Set @refSequence = Coalesce(refSequence, 0);
    Set @batchnum = Coalesce(batchnum, 0);
        

    lmcnmrp_main: Begin
        if refSequence Not Between 1 and 5 Then
            Update LOADER_STATUS sts
            Set sts.LOADER_STATUS_ERRMSGS = LOADER_CONCAT_ERR(sts.LOADER_STATUS_ERRMSGS, Concat('Numéro de référence non géré : ', @refSequence) )
                , sts.LOADER_STATUS_ERRCOUNT = sts.LOADER_STATUS_ERRCOUNT+1
            Where LOADER_STATUS_BATCH_NUM = @batchnum;
            leave lmcnmrp_main;
        End if;
        
        call LOADER_MEMBERSACCRED_NORMALIZE_MAP_ID_ENT_REF(@batchnum, @refSequence, @refDataType);
        lmcnmrp_copytorefdatable: Begin
            Set @query = Concat(
                        'Insert Into LOADER_MEMBERSACCRED_REFDATA ( '
                        ,         'LOADER_STATUS_ID_LOADER_STATUS, LOADER_MEMBERSACCRED_ID_LOADER_MEMBERSACCRED '
                        ,         ', ENTITIES_REF_SEQUENCE, ENTITIES_REF_DATA_CODE, LOADER_REFDATA_TYPE '
                        ,         ', ENTITIES_REF_ID_ENTITIES_REF '
                        ,     ') '
                        , 'Select sts.ID_LOADER_STATUS, src.ID_LOADER_MEMBERSACCRED '
                        ,     ', ?, ACCRED_CODE_', @refSequence, ', ? '
                        ,     ', src.ID_ENTITIES_REF_', @refSequence, ' '
                        , 'From '
                        ,     'LOADER_STATUS sts '
                        ,     'Inner Join LOADER_MEMBERSACCRED src '
                        ,         'On sts.ID_LOADER_STATUS = src.LOADER_STATUS_ID_LOADER_STATUS ' 
                        , 'Where '
                        ,     'sts.LOADER_STATUS_ERRCOUNT = 0 '
                        ,     'And sts.LOADER_STATUS_BATCH_NUM = ? '
                        ,     'And Trim(coalesce(src.ACCRED_CODE_', @refSequence, ', '''')) <> '''' '
                    )
                ;

            Prepare stmt From @query;
            execute stmt using @refSequence, @refDataType, @batchnum ;	
            Deallocate prepare stmt;
        End; 

        lmcnmrp_updaterefdatable: Begin
            call LOADER_MEMBERSACCRED_NORMALIZE_MAP_ID_ENT_REF_DATAS(batchnum); 
        End;

        lmcnmrp_copytoloadertable: Begin
            Set @query = Concat(
                        'Update '
                        ,     'LOADER_STATUS sts '
                        ,     'Inner Join LOADER_MEMBERSACCRED src '
                        ,         'On sts.ID_LOADER_STATUS = src.LOADER_STATUS_ID_LOADER_STATUS '
                        ,     'Inner Join LOADER_MEMBERSACCRED_REFDATA accred '
                        ,         'On src.ID_LOADER_MEMBERSACCRED = accred.LOADER_MEMBERSACCRED_ID_LOADER_MEMBERSACCRED '
                        ,         'And src.ACCRED_CODE_', @refSequence, ' = accred.ENTITIES_REF_DATA_CODE '        
                        , 'Set '
                        ,     'src.ID_ENTITIES_REF_DATAS_', @refSequence, ' = accred.ID_ENTITIES_REF_DATAS '
                        ,     ', src.ID_ENTITIES_REF_DATAS_', @refSequence, '_NEW = accred.NEW_ID_ENTITIES_REF_DATAS '
                        , 'Where '
                        ,     'LOADER_STATUS_ERRCOUNT = 0 '
                        ,     'And sts.LOADER_STATUS_BATCH_NUM = ? '
                        ,     'And accred.ENTITIES_REF_SEQUENCE = ? '
                    )
                ;
            Prepare stmt From @query;
            execute stmt using @batchnum, @refSequence ;	
            Deallocate prepare stmt;
        End; 

        lmcnmrp_replacenullval: Begin
            -- pour simplifier la recherche de profile existante
            -- on remplace les valeur null par des 0 => simplifie les jointure
            -- le prendre en compte lors de la copie
            Set @query = Concat(
                        'Update '
                        ,     'LOADER_STATUS sts '
                        ,     'Inner Join LOADER_MEMBERSACCRED src '
                        ,         'On sts.ID_LOADER_STATUS = src.LOADER_STATUS_ID_LOADER_STATUS '
                        , 'Set '
                        ,     'src.ID_ENTITIES_REF_DATAS_', @refSequence, '  = 0 '
--                         ,     'src.ID_ENTITIES_REF_DATAS_', @refSequence, ' = accred.ID_ENTITIES_REF_DATAS '
--                         ,     ', src.ID_ENTITIES_REF_DATAS_', @refSequence, '_NEW = accred.NEW_ID_ENTITIES_REF_DATAS '
                        , 'Where '
                        ,     'LOADER_STATUS_ERRCOUNT = 0 '
                        ,     'And sts.LOADER_STATUS_BATCH_NUM = ? '
                        ,     'And src.ID_ENTITIES_REF_DATAS_', @refSequence, ' Is Null '
                        ,     'And src.ID_ENTITIES_REF_DATAS_', @refSequence, '_NEW Is Null '
                        
                    )
                ;
            Prepare stmt From @query;
            execute stmt using @batchnum;	
            Deallocate prepare stmt;
        End; 

    end ;

End //
Delimiter ;