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