AMS - VB

BEGIN TRANSACTION

DECLARE @FULLNAME NVARCHAR(MAX) = N'Nguyễn Thu Trang ,NGUYỄN THỊ HIỀN ,Trần Thị Diệu Hiền ,Vũ Thị Thanh Huyền ,Huỳnh Nguyên Bình ,Vũ Thị Tin ,Lý Thị Thu Minh ,Nguyễn Hải Yến ,Diệp Bảo Lân ,Hoàng Anh Tuấn ,Lê Khánh Linh'

DECLARE @EMP_CODE VARCHAR(MAX) = N'040-09-060,040-09-012,2019-01003,04.0-17010,2018-07017,0401-10050,040-09-052,040-09-021,2022-11063,2022-11065,0000-10077'

DECLARE @EMAIL NVARCHAR(MAX) = N'nguyenthutrang@vietbank.com.vn,nguyenthihien2@vietbank.com.vn,tranthidieuhien@vietbank.com.vn,vuthithanhhuyen@vietbank.com.vn,huynhnguyenbinh@vietbank.com.vn,vuthitin@vietbank.com.vn,lythithuminh@vietbank.com.vn,nguyenhaiyen@vietbank.com.vn,diepbaolan@vietbank.com.vn,hoanganhtuan@vietbank.com.vn,lekhanhlinh@vietbank.com.vn'

DECLARE @BRANCH_CODE VARCHAR(MAX) = N'0800,0811,0500,0500,0500,0801,0801,0801,0500,0500,0702'

DECLARE @DEP_ID_IMP VARCHAR(MAX) = N',,05T00,05T00,05T00,,,,05J00,05J01,'

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

DECLARE @USER_NAME VARCHAR(MAX) = 'TRANGNT09060,HIENNT09012,hienttd,huyenvtt,binhhn,tinvt,minhltt,yennh,landb,tuanha,linhlk0702'

--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,','))

DECLARE @TBL_IMPORT TABLE(TLNAME VARCHAR(MAX),FULLNAME NVARCHAR(MAX),EMAIL NVARCHAR(MAX),EMP_CODE NVARCHAR(MAX),BRANCH_CODE NVARCHAR(MAX),DEP_CODE NVARCHAR(MAX),ROLE_NAME NVARCHAR(MAX))

INSERT INTO @TBL_IMPORT

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

SELECT A.TLNAME

      ,A.FULLNAME

      ,A.EMAIL

      ,A.EMP_CODE

      ,B.BRANCH_ID

      ,C.DEP_ID

      ,A.ROLE_NAME

      ,D.POS_NAME

      ,D.POS_CODE

FROM @TBL_IMPORT A

LEFT JOIN CM_BRANCH B ON A.BRANCH_CODE = B.BRANCH_CODE

LEFT JOIN CM_DEPARTMENT C ON A.DEP_CODE = C.DEP_CODE

LEFT JOIN CM_EMPLOYEE D ON A.EMP_CODE = D.EMP_CODE

--LEFT JOIN CM_BRANCH C ON A.BRANCH_CODE = C.BRANCH_CODE

--LEFT JOIN TL_USER B ON A.TLNAME = B.TLNANME

--LEFT JOIN CM_BRANCH D ON B.TLSUBBRID = D.BRANCH_ID

--WHERE A.ROLE_NAME <> B.RoleName OR A.BRANCH_CODE <> D.BRANCH_CODE

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 @INS_POSNAME NVARCHAR(200)

DECLARE @INS_POSCODE NVARCHAR(200)

DECLARE cur CURSOR FAST_FORWARD READ_ONLY LOCAL FOR

SELECT A.*,D.POS_NAME,D.POS_CODE

FROM @TBL_IMPORT A

LEFT JOIN CM_EMPLOYEE D ON A.EMP_CODE = D.EMP_CODE

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,@INS_POSNAME,@INS_POSCODE

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, @INS_POSCODE, @INS_POSNAME, 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

  SET @INS_POSNAME = NULL

  SET @INS_POSCODE = NULL

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

END

CLOSE cur

DEALLOCATE cur

COMMIT TRANSACTION