BEGIN TRANSACTION
DECLARE @FULLNAME NVARCHAR(MAX) = N'Nguyễn Đức Anh,Đỗ Nguyên Khang,Đỗ Nguyên Khang ,Trần Thị Tuyết Hoa ,Nguyễn Văn Điền ,Nguyễn Thanh Phúc ,Nguyễn Thiện Tùng ,Vũ Công Huân ,Bùi Văn Dũng ,Nguyễn Duy Trí ,Võ Tấn Phương ,Phan Anh Tiến ,Mai Văn Quân,Nguyễn Đức Anh ,Nguyễn Thị Thu Ngân ,Đặng Giang Sang ,Nguyễn Văn Thịnh ,Trương Hồ Hoàng Chương ,Huỳnh Thị Thanh Thảo'
DECLARE @EMP_CODE VARCHAR(MAX) = N'2022-05039,2021-06046,2021-06046,2020-07085,2017-04015,2018-06032,2018-05001,2019-04102,2017-03059,2019-08005,0000-10759,2022-06032,2017-07006,2022-05039,2021-07004,2020-05049,2020-06018,2021-06068,2018-10045'
DECLARE @EMAIL NVARCHAR(MAX) = N'nguyenducanh@vietbank.com.vn,donguyenkhang@vietbank.com.vn,donguyenkhang@vietbank.com.vn,tranthituyethoa1@vietbank.com.vn,nguyenvandien@vietbank.com.vn,nguyenthanhphuc@vietbank.com.vn,nguyenthientung@vietbank.com.vn,vuconghuan@vietbank.com.vn,buivandung@vietbank.com.vn,nguyenduytri@vietbank.com.vn,votanphuong@vietbank.com.vn,phananhtien@vietbank.com.vn,maivanquan@vietbank.com.vn,nguyenducanh@vietbank.com.vn,nguyenthithungan@vietbank.com.vn,danggiangsang@vietbank.com.vn,nguyenvanthinh1@gmail.com.vn,truonghohoangchuong@vietbank.com.vn,huynhthithanhthao@vietbank.com.vn'
DECLARE @BRANCH_CODE VARCHAR(MAX) = N'0500,0500,0500,0500,0500,0500,0500,0500,0500,0500,0500,0500,0500,0500,0500,0500,0500,0500,0500'
DECLARE @DEP_ID_IMP VARCHAR(MAX) = N'05N22,05N22,05N22,05N22,05N22,05N22,05N22,05N22,05N22,05N22,05N22,05N22,05N22,05N22,05N22,05N22,05N22,05N22,05N22'
DECLARE @ROLE VARCHAR(MAX) =  N'TBP_PTML,TBP_XDCB,XDCB,XDCB,XDCB,XDCB,XDCB,XDCB,XDCB,XDCB,XDCB,XDCB,XDCB,PTML,PTML,PTML,PTML,PTML,PTML'
DECLARE @USER_NAME VARCHAR(MAX) = 'anhnd0500,khangdn0500,khangdn0500,hoattt0500,diennv,phucnt2,tungnt1,huanvc,dungbv,trind,phuongvt,tienpa0500,quanmv,anhnd0500,nganntt10500,sangdg,thinhnv0500,chuongthh0500,thaohtt1'
--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
      
      IF(NOT EXISTS(SELECT 1 FROM TL_USER WHERE TLNANME = @INS_USER_NAME))
      BEGIN     
    			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());
     END

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