Delimiter //
/**
 * Author:  armen
 * Created: 12 oct. 2016
 */
Drop Procedure if exists LOADER_MEMBERS_INSERT//
Create Procedure LOADER_MEMBERS_INSERT (
    In batchnum int, 
    In lastId Int
)
Begin
		Set @idAdminParam = GET_LOADER_INFO('ID_ADMIN_PARAMS', batchnum);
		set @UniverId = (select UNIVERS_ID_UNIVERS from ADMIN_PARAMS where ID_ADMIN_PARAMS = @idAdminParam);

        Insert Into MEMBERS (
            ID_MEMBERS, UNIVERS_ID_UNIVERS
			, CONTACT_INDIV_ID_CONTACT_INDIV
			, MEMBERS_ADMIN_RIGHTS, MEMBERS_TYPE
			, MEMBERS_LOGIN
			, MEMBERS_AUTH_SOURCE_ADAPTER
        )  
        Select  
            src.ID_MEMBERS_NEW + lastId, @UniverId
            , src.CONTACT_INDIV_ID_CONTACT_INDIV  
-- 			, 0 ADMIN_RIGHT, @deniedConnection as MEMBERS_TYPE
			, src.MEMBERS_ADMIN_RIGHTS, src.MEMBERS_TYPE
            , src.MEMBERS_LOGIN
            , src.MEMBERS_AUTH_SOURCE_ADAPTER
        From  
            LOADER_MEMBERS src  
            Inner Join LOADER_STATUS lst  
                On src.LOADER_STATUS_ID_LOADER_STATUS = lst.ID_LOADER_STATUS  

        Where 1 = 1  
            And lst.LOADER_STATUS_ERRCOUNT = 0  
            And lst.LOADER_STATUS_BATCH_NUM =  batchnum
            And Coalesce(src.ID_MEMBERS_NEW, 0) <> 0
        ;

--         insert Into ADMIN_PARAMS_has_MEMBERS (ADMIN_PARAMS_ID_ADMIN_PARAMS, MEMBERS_ID_MEMBERS)
--         Select @idAdminParam, mbr.ID_MEMBERS
--         From MEMBERS mbr
--             Left Join ADMIN_PARAMS_has_MEMBERS aphm
--                 On mbr.ID_MEMBERS = aphm.MEMBERS_ID_MEMBERS
--         where
--             aphm.ID_ADMIN_PARAMS_has_MEMBERS is null 
--         ;
        -- requete multi-entité
        insert Into ADMIN_PARAMS_has_MEMBERS (MEMBERS_ID_MEMBERS, ADMIN_PARAMS_ID_ADMIN_PARAMS)
        Select mbr.ID_MEMBERS
            , (
                Select adp.ID_ADMIN_PARAMS
                From ADMIN_PARAMS adp
                Where mbr.UNIVERS_ID_UNIVERS = adp.UNIVERS_ID_UNIVERS
                Limit 1
            ) ADMIN_PARAMS_ID_ADMIN_PARAMS
        From MEMBERS mbr
            Left Join ADMIN_PARAMS_has_MEMBERS aphm
                On mbr.ID_MEMBERS = aphm.MEMBERS_ID_MEMBERS
        where
            aphm.ID_ADMIN_PARAMS_has_MEMBERS is null 
        ;


End //

Delimiter ;





