Delimiter //
/**
 * Author:  armen
 * Created: 04 janv. 2019
 */
Drop Procedure if exists LOADER_ATTACHEDMEMBERS_NORMALIZE_MAP_MEMBERS_REFDATA_PROVIDED//

Create Procedure LOADER_ATTACHEDMEMBERS_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_ATTACHEDMEMBERS_REFDATA(); -- attention auto-comit, voir si génant

--     Set @id_admin_params = GET_LOADER_INFO('ID_ADMIN_PARAMS', batchnum);
    Set @lmcnmrp_refDataType = Coalesce(refDataType, 'ACCRED_ACCOUNTS');
    Set @lmcnmrp_refSequence = Coalesce(refSequence, 0);
    Set @lmcnmrp_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é : ', @lmcnmrp_refSequence) )
                , sts.LOADER_STATUS_ERRCOUNT = sts.LOADER_STATUS_ERRCOUNT+1
            Where LOADER_STATUS_BATCH_NUM = @lmcnmrp_batchnum;
            leave lmcnmrp_main;
        End if;
        
        call LOADER_ATTACHEDMEMBERS_NORMALIZE_MAP_ID_ENT_REF(@lmcnmrp_batchnum, @lmcnmrp_refSequence, @lmcnmrp_refDataType);
        lmcnmrp_copytorefdatable: Begin
            Set @lmcnmrp_copytorefdatable_query = Concat(
                        'Insert Into LOADER_ATTACHEDMEMBERS_REFDATA ( '
                        ,         'LOADER_STATUS_ID_LOADER_STATUS, LOADER_ATTACHEDMEMBERS_ID_LOADER_ATTACHEDMEMBERS '
                        ,         ', ENTITIES_REF_SEQUENCE, ENTITIES_REF_DATA_CODE, LOADER_REFDATA_TYPE '
                        ,         ', ENTITIES_REF_ID_ENTITIES_REF '
                        ,     ') '
                        , 'Select sts.ID_LOADER_STATUS, src.ID_LOADER_ATTACHEDMEMBERS '
                        ,     ', ?, ACCRED_CODE_', @lmcnmrp_refSequence, ', ? '
                        ,     ', src.ID_ENTITIES_REF_', @lmcnmrp_refSequence, ' '
                        , 'From '
                        ,     'LOADER_STATUS sts '
                        ,     'Inner Join LOADER_ATTACHEDMEMBERS 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_', @lmcnmrp_refSequence, ', '''')) <> '''' '
                    )
                ;

            Prepare lmcnmrp_copytorefdatable_stmt From @lmcnmrp_copytorefdatable_query;
            execute lmcnmrp_copytorefdatable_stmt using @lmcnmrp_refSequence, @lmcnmrp_refDataType, @lmcnmrp_batchnum ;	
            Deallocate prepare lmcnmrp_copytorefdatable_stmt;
        End; 

        lmcnmrp_updaterefdatable: Begin
            call LOADER_ATTACHEDMEMBERS_NORMALIZE_MAP_ID_ENT_REF_DATAS(batchnum); 
        End;

        lmcnmrp_copytoloadertable: Begin
            Set @lmcnmrp_copytoloadertable_query = Concat(
                        'Update '
                        ,     'LOADER_STATUS sts '
                        ,     'Inner Join LOADER_ATTACHEDMEMBERS src '
                        ,         'On sts.ID_LOADER_STATUS = src.LOADER_STATUS_ID_LOADER_STATUS '
                        ,     'Inner Join LOADER_ATTACHEDMEMBERS_REFDATA accred '
                        ,         'On src.ID_LOADER_ATTACHEDMEMBERS = accred.LOADER_ATTACHEDMEMBERS_ID_LOADER_ATTACHEDMEMBERS '
                        ,         'And src.ACCRED_CODE_', @lmcnmrp_refSequence, ' = accred.ENTITIES_REF_DATA_CODE '        
                        , 'Set '
                        ,     'src.ID_ENTITIES_REF_DATAS_', @lmcnmrp_refSequence, ' = accred.ID_ENTITIES_REF_DATAS '
--                         ,     ', src.ID_ENTITIES_REF_DATAS_', @lmcnmrp_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 lmcnmrp_copytoloadertable_stmt From @lmcnmrp_copytoloadertable_query;
            execute lmcnmrp_copytoloadertable_stmt using @lmcnmrp_batchnum, @lmcnmrp_refSequence ;	
            Deallocate prepare lmcnmrp_copytoloadertable_stmt;
        End; 


        -- provisoire
        -- on indique en erreur les références non trouvées 
        -- car si non trouvé => null => en raison du mode permissif , implique le droit sur le compte
        lmcnmrp_tmpseterr: Begin
            Set @lmcnmrp_tmpseterr_query = Concat(
                        'Update '
                        ,     'LOADER_STATUS sts '
                        ,     'Inner Join LOADER_ATTACHEDMEMBERS src '
                        ,         'On sts.ID_LOADER_STATUS = src.LOADER_STATUS_ID_LOADER_STATUS '
                        , 'Set '
                        ,   ' sts.LOADER_STATUS_ERRCOUNT = Coalesce(sts.LOADER_STATUS_ERRCOUNT, 0) + 1 '
                        ,   ', sts.LOADER_STATUS_ERRMSGS = LOADER_CONCAT_ERR(sts.LOADER_STATUS_ERRMSGS, Concat(''Référence d''''accreditation '', ?, '' non trouvé :'', src.ACCRED_CODE_', @lmcnmrp_refSequence, ' ) ) '
                        , 'Where '
                        ,     'LOADER_STATUS_ERRCOUNT = 0 '
                        ,     'And sts.LOADER_STATUS_BATCH_NUM = ? '
                        ,     'And Coalesce(src.ACCRED_CODE_', @lmcnmrp_refSequence, ', '''') <> '''' '
                        ,     'And src.ID_ENTITIES_REF_DATAS_', @lmcnmrp_refSequence, ' Is Null '
                    )
                ;

--             Select @lmcnmrp_batchnum, @lmcnmrp_refSequence, @lmcnmrp_tmpseterr_query;
            Prepare lmcnmrp_tmpseterr_stmt From @lmcnmrp_tmpseterr_query;
            execute lmcnmrp_tmpseterr_stmt using @lmcnmrp_refSequence, @lmcnmrp_batchnum ;	
            Deallocate prepare lmcnmrp_tmpseterr_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_ATTACHEDMEMBERS src '
--                         ,         'On sts.ID_LOADER_STATUS = src.LOADER_STATUS_ID_LOADER_STATUS '
--                         , 'Set '
--                         ,     'src.ID_ENTITIES_REF_DATAS_', @lmcnmrp_refSequence, '  = 0 '
-- --                         ,     'src.ID_ENTITIES_REF_DATAS_', @lmcnmrp_refSequence, ' = accred.ID_ENTITIES_REF_DATAS '
-- --                         ,     ', src.ID_ENTITIES_REF_DATAS_', @lmcnmrp_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_', @lmcnmrp_refSequence, ' Is Null '
-- --                         ,     'And src.ID_ENTITIES_REF_DATAS_', @lmcnmrp_refSequence, '_NEW Is Null '
--                         
--                     )
--                 ;
--             Prepare stmt From @query;
--             execute stmt using @lmcnmrp_batchnum;	
--             Deallocate prepare stmt;
--         End; 

    end ;

End //
Delimiter ;