AMS - VB

DELETE CM_EMPLOYEE_LOG WHERE USER_DOMAIN = 'hannd'

DELETE AbpUserRoles WHERE UserId = (SELECT TU.ID FROM TL_USER TU WHERE TU.TLNANME = 'hannd')

DELETE TL_USER WHERE TLNANME = 'hannd'

BEGIN TRANSACTION

DECLARE @FULLNAME NVARCHAR(MAX) =N'Phan Phước Trung,Bùi Thị Hồng Mơ,Cao Minh Tuấn,Nguyễn Diệp Hân'

DECLARE @EMP_CODE VARCHAR(MAX) = N'05.0-16002,0000-15094,0000-16313,2019-07074'

DECLARE @USER_NAME VARCHAR(MAX) =N'trungpp,mobth,tuancm,hannd'

DECLARE @EMAIL NVARCHAR(MAX) = N'phanphuoctrung@vietbank.com.vn,buithihongmo@vietbank.com.vn,caominhtuan@vietbank.com.vn,nguyendiephan@vietbank.com.vn'

DECLARE @BRANCH_CODE VARCHAR(MAX) = N'0500,0500,0500,1303'

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

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

--DELETE IMPORT_USER_QLTS

--INSERT INTO IMPORT_USER_QLTS (TLNAME, FULLNAME, EMAIL, EMP_CODE, BRANCH_CODE, DEP_CODE, ROLE_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

			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));

      if(@INS_USER_NAME <> 'hannd')

      BEGIN

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

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

      END

  	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