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