AMS - VB

BEGIN TRANSACTION

DECLARE @FULLNAME NVARCHAR(MAX) = N'Nguyễn Thị Củng Lợi,Lê Thị Ngọc'

DECLARE @EMP_CODE VARCHAR(MAX) = N'2022-03047,2022-11015'

DECLARE @EMAIL NVARCHAR(MAX) = N'nguyenthicungloi@vietbank.com.vn,lethingoc@vietbank.com.vn'

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

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

DECLARE @ROLE VARCHAR(MAX) =  N'DVDM,GDV'

DECLARE @USER_NAME VARCHAR(MAX) = 'lointc0500,ngoclt0500'

DELETE AbpUserRoles WHERE UserId IN (SELECT TU.ID FROM TL_USER TU WHERE TU.TLNANME IN (SELECT * FROM STRING_SPLIT(@USER_NAME,',')))

DELETE CM_EMPLOYEE_LOG WHERE USER_DOMAIN IN (SELECT * FROM STRING_SPLIT(@USER_NAME,','))

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

--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 LTRIM(RTRIM(A.TLNAME)) AS TLNAME

      ,LTRIM(RTRIM(A.FULLNAME)) AS FULLNAME

      ,LTRIM(RTRIM(A.EMAIL)) AS EMAIL

      ,LTRIM(RTRIM(A.EMP_CODE)) AS EMP_CODE

      ,LTRIM(RTRIM(B.BRANCH_ID)) AS BRANCH_ID

      ,LTRIM(RTRIM(C.DEP_ID)) AS DEP_ID

      ,LTRIM(RTRIM(A.ROLE_NAME)) AS ROLE_NAME

      ,LTRIM(RTRIM(D.POS_NAME)) AS POS_NAME

      ,LTRIM(RTRIM(D.POS_CODE)) AS 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

			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 = 'lointc0500'));

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

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

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

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

COMMIT TRANSACTION