SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL Serializable
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
BEGIN TRANSACTION
GO
DECLARE @USER_DOMAIN VARCHAR(300), @BRANCH_CODE VARCHAR(50), @ROLE VARCHAR(100), @DEP_ID VARCHAR(50), @FULLNAME NVARCHAR(500)
DECLARE @Role1 INT, @Role2 INT, @BRANCH_ID VARCHAR(50)
DECLARE CURR_USER CURSOR LOCAL FOR  
SELECT TOP(300) LOWER(TLNANME),B.BRANCH_CODE,RoleName,SECUR_CODE,TLFullName FROM dbo.TL_USER_V2 tuv LEFT JOIN dbo.CM_BRANCH B ON tuv.TLSUBBRID = B.BRANCH_ID WHERE tuv.TLNANME NOT IN (SELECT TLNANME FROM dbo.TL_USER)
OPEN CURR_USER               
FETCH NEXT FROM CURR_USER INTO @USER_DOMAIN,@BRANCH_CODE,@ROLE,@DEP_ID, @FULLNAME
WHILE @@FETCH_STATUS = 0        
BEGIN
SET  @Role1 = (SELECT TOP(1) Id FROM AbpRoles WHERE DisplayName = @ROLE)
SET  @BRANCH_ID = (SELECT TOP(1) BRANCH_ID FROM CM_BRANCH WHERE BRANCH_CODE =@BRANCH_CODE)
IF(@BRANCH_ID IS NULL OR @BRANCH_ID ='')
BEGIN
	--ROLLBACK TRANSACTION
	PRINT N'Mã đơn vị: ' +@BRANCH_ID +N' không tồn tại trong hệ thống'
END	
IF(@Role1 IS NULl)
BEGIN
	--ROLLBACK TRANSACTION
	--PRINT N'Mã ROLE: '+ @ROLE +N' không tồn tại trong hệ thống'
	SET @Role1 =(SELECT TOP 1 ID FROM AbpRoles WHERE DisplayName='NVMS')
END	
IF(NOT EXISTS (SELECT * FROM TL_USER WHERE TLNANME = @USER_DOMAIN))
BEGIN
	--PRINT @USER_DOMAIN
	INSERT INTO [TL_USER] ([TLID],[TLNANME],[Password],[TLFullName],[TLSUBBRID],[BRANCH_NAME],[BRANCH_TYPE],[RoleName],[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])
	SELECT NULL,@USER_DOMAIN,N'd41d8cd98f00b204e9800998ecf8427e',@FULLNAME,BRANCH_ID,BRANCH_NAME,BRANCH_TYPE,@ROLE,@USER_DOMAIN+'@vietcapitalbank.com.vn',N'',N'',N'A',N'tscd_hoiso',NULL,N'Jan  1 1900 12:00AM',N'1',N'Jan  1 1900 12:00AM',N'1',NULL,0,NULL,N'1360eb6c-4581-4870-aaf7-a2986428b91d',NULL,NULL,@USER_DOMAIN+'@vietcapitalbank.com.vn',NULL,1,0,1,0,1,0,810,NULL,NULL,@USER_DOMAIN+'@VIETCAPITALBANK.COM.VN',N'ABC',NULL,NULL,NULL,N'QSTE4B7VTYWRQON4JZTJST6QN4PEVVLB',1,NULL,1,NULL,NULL,NULL,0,NULL,N'Jul  4 2021 11:22AM',NULL,NULL,NULL,NULL,NULL,NULL
	FROM CM_BRANCH
	WHERE BRANCH_CODE = @BRANCH_CODE
	INSERT INTO [AbpUserRoles] ([CreationTime],[CreatorUserId],[RoleId],[TenantId],[UserId])
	SELECT N'2021-10-06 11:22:39.4900000' AS CreationTime, NULL AS CreatorUserId, @Role1 AS RoleId, 1 AS TenantId, ID AS UserId
	FROM TL_USER
	WHERE TLNANME = @USER_DOMAIN
	UPDATE TL_USER SET SECUR_CODE =@DEP_ID, DEP_ID =@DEP_ID WHERE TLNANME =@USER_DOMAIN
END
ELSE
BEGIN
	PRINT 'DA TON TAI'
	--UPDATE TL_USER SET TLSUBBRID = @BRANCH_ID, DEP_ID =@DEP_ID WHERE TLNANME = @USER_DOMAIN
	--UPDATE AbpUserRoles SET RoleId = @Role1 WHERE ID IN (SELECT TOP(1) A.ID FROM AbpUserRoles A LEFT JOIN TL_USER B ON A.UserId = B.ID WHERE B.TLNANME = @USER_DOMAIN)
END
UPDATE TL_USER SET  NormalizedUserName = UPPER(TLNANME)
FETCH NEXT FROM CURR_USER INTO @USER_DOMAIN,@BRANCH_CODE,@ROLE,@DEP_ID, @FULLNAME
END
CLOSE CURR_USER              -- Đóng Cursor
DEALLOCATE CURR_USER         -- Giải phóng tài nguyên
IF @@ERROR <> 0 SET NOEXEC ON
GO
COMMIT TRANSACTION
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
-- This statement writes to the SQL Server Log so SQL Monitor can show this deployment.
IF HAS_PERMS_BY_NAME(N'sys.xp_logevent', N'OBJECT', N'EXECUTE') = 1
BEGIN
    DECLARE @databaseName AS nvarchar(2048), @eventMessage AS nvarchar(2048)
    SET @databaseName = REPLACE(REPLACE(DB_NAME(), N'\', N'\\'), N'"', N'\"')
    SET @eventMessage = N'Redgate SQL Compare: { "deployment": { "description": "Redgate SQL Compare deployed to ' + @databaseName + N'", "database": "' + @databaseName + N'" }}'
    EXECUTE sys.xp_logevent 55000, @eventMessage
END
GO
DECLARE @Success AS BIT
SET @Success = 1
SET NOEXEC OFF
IF (@Success = 1) PRINT 'The database update succeeded'
ELSE BEGIN
	IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION
	PRINT 'The database update failed'
END
GO