AMS - VB

BEGIN TRANSACTION

--DECLARE @FULLNAME NVARCHAR(MAX) = N''

--DECLARE @EMP_CODE VARCHAR(MAX) = N''

--DECLARE @EMAIL NVARCHAR(MAX) = N''

--DECLARE @BRANCH_CODE VARCHAR(MAX) = N''

--DECLARE @DEP_ID_IMP VARCHAR(MAX) = N''

--DECLARE @ROLE VARCHAR(MAX) =  N''

DECLARE @FULLNAME NVARCHAR(MAX) = N'Huỳnh Kim Thuỷ,Trần Thị Thể,Phạm Di Luân,Lâm Bích Hà, Võ thị Tuyết Nhi,Nguyễn Văn Thuyên,Phan Nguyễn Hữu Nhàn'

DECLARE @EMP_CODE VARCHAR(MAX) = N'2019-05017,2018-05003,2019-10041,2019-06039,2020-09017,2019-04076,1101-14001'

DECLARE @EMAIL NVARCHAR(MAX) = N'huynhkimthuy@vietbank.com.vn,tranthithe@vietbank.com.vn,phamdiluan@vietbank.com.vn,lambichha@vietbank.com.vn,vothituyetnhi@vietbank.com.vn,nguyenvanthuyen@vietbank.com.vn,phannguyenhuunhan@vietbank.com.vn'

DECLARE @BRANCH_CODE VARCHAR(MAX) = N'1401,1401,1401,1602,1602,1602,1600'

DECLARE @DEP_ID_IMP VARCHAR(MAX) = N',,,,,,'

DECLARE @ROLE VARCHAR(MAX) =  N'NVTT,NVTT,GDDV,NVTT,NVTT,GDDV,DVCM'

DECLARE @USER_NAME VARCHAR(MAX) = 'thuyhk,thett,luanpd1401,hald,nhivtt1602,thuyennv,nhanpnh'

--DELETE IMPORT_USER_QLTS

--INSERT INTO IMPORT_USER_QLTS (TLNAME, FULLNAME, EMAIL, EMP_CODE, BRANCH_CODE, DEP_CODE, ROLE_NAME)

SELECT * FROM TL_USER CE WHERE CE.TLNANME IN (SELECT * FROM STRING_SPLIT(@USER_NAME,','))

SELECT C.TLNAME,A.FULLNAME,D.EMAIL,B.EMP_CODE,E.BRANCH_CODE,F.DEP_CODE,G.ROLE_NAME

FROM (

SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS ID, VALUE AS FULLNAME

FROM STRING_SPLIT(@FULLNAME,','))A

LEFT JOIN (

    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS ID, VALUE AS EMP_CODE

    FROM STRING_SPLIT(@EMP_CODE,','))B ON A.ID = B.ID

LEFT JOIN (

    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS ID, VALUE AS TLNAME

    FROM STRING_SPLIT(@USER_NAME,',')) C ON A.ID = C.ID

LEFT JOIN (

    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS ID, VALUE AS EMAIL

    FROM STRING_SPLIT(@EMAIL,','))D ON A.ID = D.ID

LEFT JOIN (

    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS ID, VALUE AS BRANCH_CODE

    FROM STRING_SPLIT(@BRANCH_CODE,','))E ON A.ID = E.ID

LEFT JOIN (

    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS ID, VALUE AS DEP_CODE

    FROM STRING_SPLIT(@DEP_ID_IMP,','))F ON A.ID = F.ID

LEFT JOIN (

    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS ID, VALUE AS ROLE_NAME

    FROM STRING_SPLIT(@ROLE,','))G ON A.ID = G.ID

DECLARE @INS_MESSAGE NVARCHAR(1000)         

DECLARE @INS_FULLNAME NVARCHAR(1000)

DECLARE @INS_EMP_CODE VARCHAR(1000)        

DECLARE @INS_USER_NAME VARCHAR(1000) 

DECLARE @INS_EMAIL NVARCHAR(1000)

DECLARE @INS_BRANCH_CODE VARCHAR(1000) 

DECLARE @INS_DEP_CODE VARCHAR(1000)   

DECLARE @INS_ROLE VARCHAR(1000)

DECLARE cur CURSOR FAST_FORWARD READ_ONLY LOCAL FOR

--SELECT TLNAME

--      ,FULLNAME

--      ,EMAIL

--      ,EMP_CODE

--      ,BRANCH_CODE

--      ,DEP_CODE

--      ,ROLE_NAME FROM IMPORT_USER_QLTS 

--	  where TLNAME = 'khoidt'

SELECT C.TLNAME,A.FULLNAME,D.EMAIL,B.EMP_CODE,E.BRANCH_CODE,F.DEP_CODE,G.ROLE_NAME

FROM (

SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS ID, VALUE AS FULLNAME

FROM STRING_SPLIT(@FULLNAME,','))A

LEFT JOIN (

    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS ID, VALUE AS EMP_CODE

    FROM STRING_SPLIT(@EMP_CODE,','))B ON A.ID = B.ID

LEFT JOIN (

    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS ID, VALUE AS TLNAME

    FROM STRING_SPLIT(@USER_NAME,',')) C ON A.ID = C.ID

LEFT JOIN (

    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS ID, VALUE AS EMAIL

    FROM STRING_SPLIT(@EMAIL,','))D ON A.ID = D.ID

LEFT JOIN (

    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS ID, VALUE AS BRANCH_CODE

    FROM STRING_SPLIT(@BRANCH_CODE,','))E ON A.ID = E.ID

LEFT JOIN (

    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS ID, VALUE AS DEP_CODE

    FROM STRING_SPLIT(@DEP_ID_IMP,','))F ON A.ID = F.ID

LEFT JOIN (

    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS ID, VALUE AS ROLE_NAME

    FROM STRING_SPLIT(@ROLE,','))G ON A.ID = G.ID

OPEN cur

FETCH NEXT FROM cur INTO @INS_USER_NAME,@INS_FULLNAME,@INS_EMAIL,@INS_EMP_CODE,@INS_BRANCH_CODE,@INS_DEP_CODE,@INS_ROLE

WHILE @@FETCH_STATUS = 0 BEGIN

	IF(NOT EXISTS(SELECT 1 FROM TL_USER WHERE TLNANME = @INS_USER_NAME))

    BEGIN

			DECLARE @BRANCH_ID VARCHAR(15), @DEP_ID VARCHAR(15), @DEP_CODE VARCHAR(100)

			SELECT @BRANCH_ID = cb.BRANCH_ID FROM CM_BRANCH cb WHERE cb.BRANCH_CODE = @INS_BRANCH_CODE

			SELECT @DEP_ID = cd.DEP_ID FROM CM_DEPARTMENT cd WHERE cd.DEP_CODE = @INS_DEP_CODE

			SET @DEP_CODE = @INS_DEP_CODE

      if(@INS_ROLE = 'DVCM')

      BEGIN

          SET @BRANCH_ID = 'DV0001'

          SET @DEP_ID = 'DEP000000000027'

          SET @DEP_CODE = '05J03'

      END

			INSERT INTO TL_USER ( TLID, TLNANME, Password, TLFullName, TLSUBBRID,  BRANCH_TYPE,  EMAIL, ADDRESS, PHONE, AUTH_STATUS, MARKER_ID, AUTH_ID, APPROVE_DT, ISAPPROVE, Birthday, ISFIRSTTIME, SECUR_CODE, AccessFailedCount, AuthenticationSource, ConcurrencyStamp, CreatorUserId, DeleterUserId, EmailAddress, EmailConfirmationCode, IsActive, IsDeleted, IsEmailConfirmed, IsLockoutEnabled, IsPhoneNumberConfirmed, IsTwoFactorEnabled, LastModifierUserId, LockoutEndDateUtc, Name, NormalizedEmailAddress, NormalizedUserName, PasswordResetCode, PhoneNumber, ProfilePictureId, SecurityStamp, ShouldChangePasswordOnNextLogin, Surname, TenantId, SignInToken, SignInTokenExpireTimeUtc, GoogleAuthenticatorKey, SendActivationEmail, DEP_ID, CreationTime, UamFullName, UamEmployeeId, UamCompanyCode, UamWfDataId, UamCompanyName, UamJobTitle, UserCurrentLanguage, EMAILTEMP) VALUES

			(NULL, @INS_USER_NAME, N'AQAAAAEAACcQAAAAEMF7sJx/2L/X7bkO6YmSRfr8d7Na/RURfT4tDZYFIDMaik/cy+y7PSfq48Btaka28A==', @INS_FULLNAME, @BRANCH_ID, '',  @INS_EMAIL, N'', '', 'A', 'bichnn', NULL, GETDATE(), '1', GETDATE(), '1', @DEP_ID, 0, NULL, N'fd2b0a93-3081-460d-b969-b5a75f96c0de', NULL, NULL, @INS_EMAIL, NULL, CONVERT(bit, 'True'), CONVERT(bit, 'False'), CONVERT(bit, 'True'), CONVERT(bit, 'False'), CONVERT(bit, 'True'), CONVERT(bit, 'False'), 259, NULL, NULL, @INS_EMAIL, @INS_USER_NAME, NULL, NULL, NULL, N'QSTE4B7VTYWRQON4JZTJST6QN4PEVVLB', CONVERT(bit, 'False'), NULL, 1, NULL, NULL, NULL, CONVERT(bit, 'False'), @DEP_ID, GETDATE(), NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)

			INSERT INTO CM_EMPLOYEE_LOG (EMP_CODE, EMP_NAME, BRANCH_CODE, DEP_CODE, USER_DOMAIN, POS_CODE, POS_NAME, CREATE_DT)

			VALUES (@INS_EMP_CODE, @INS_FULLNAME, @INS_BRANCH_CODE, @INS_DEP_CODE, @INS_USER_NAME, '', N'', GETDATE());

			INSERT INTO AbpUserRoles (CreationTime, CreatorUserId, RoleId, TenantId, UserId)

			VALUES (SYSDATETIME(), 0, (SELECT TOP 1 ar.Id FROM AbpRoles ar WHERE ar.DisplayName = @INS_ROLE), 1, (SELECT MAX(ID) FROM TL_USER tu WHERE tu.TLNANME = @INS_USER_NAME));

  	END

    IF(NOT EXISTS(SELECT 1 FROM AbpUserRoles AUR WHERE AUR.RoleId = (SELECT TOP 1 ar.Id FROM AbpRoles ar WHERE ar.DisplayName = @INS_ROLE) AND AUR.UserId = (SELECT MAX(ID) FROM TL_USER tu WHERE tu.TLNANME = @INS_USER_NAME)))

    BEGIN

      	INSERT INTO AbpUserRoles (CreationTime, CreatorUserId, RoleId, TenantId, UserId)

			  VALUES (SYSDATETIME(), 0, (SELECT TOP 1 ar.Id FROM AbpRoles ar WHERE ar.DisplayName = @INS_ROLE), 1, (SELECT MAX(ID) FROM TL_USER tu WHERE tu.TLNANME = @INS_USER_NAME));

    END

  SET @BRANCH_ID = NULL

  SET @DEP_ID = NULL

  SET @DEP_CODE = NULL

	FETCH NEXT FROM cur INTO @INS_USER_NAME,@INS_FULLNAME,@INS_EMAIL,@INS_EMP_CODE,@INS_BRANCH_CODE,@INS_DEP_CODE,@INS_ROLE

END

CLOSE cur

DEALLOCATE cur

COMMIT TRANSACTION