|
1
|
SET NUMERIC_ROUNDABORT OFF
|
|
2
|
GO
|
|
3
|
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
|
|
4
|
GO
|
|
5
|
SET XACT_ABORT ON
|
|
6
|
GO
|
|
7
|
SET TRANSACTION ISOLATION LEVEL Serializable
|
|
8
|
GO
|
|
9
|
IF @@ERROR <> 0 SET NOEXEC ON
|
|
10
|
GO
|
|
11
|
BEGIN TRANSACTION
|
|
12
|
GO
|
|
13
|
DECLARE @USER_DOMAIN VARCHAR(300), @BRANCH_CODE VARCHAR(50), @ROLE VARCHAR(100), @DEP_ID VARCHAR(50), @FULLNAME NVARCHAR(500)
|
|
14
|
DECLARE @Role1 INT, @Role2 INT, @BRANCH_ID VARCHAR(50)
|
|
15
|
DECLARE CURR_USER CURSOR LOCAL FOR
|
|
16
|
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)
|
|
17
|
OPEN CURR_USER
|
|
18
|
FETCH NEXT FROM CURR_USER INTO @USER_DOMAIN,@BRANCH_CODE,@ROLE,@DEP_ID, @FULLNAME
|
|
19
|
WHILE @@FETCH_STATUS = 0
|
|
20
|
BEGIN
|
|
21
|
SET @Role1 = (SELECT TOP(1) Id FROM AbpRoles WHERE DisplayName = @ROLE)
|
|
22
|
SET @BRANCH_ID = (SELECT TOP(1) BRANCH_ID FROM CM_BRANCH WHERE BRANCH_CODE =@BRANCH_CODE)
|
|
23
|
IF(@BRANCH_ID IS NULL OR @BRANCH_ID ='')
|
|
24
|
BEGIN
|
|
25
|
--ROLLBACK TRANSACTION
|
|
26
|
PRINT N'Mã đơn vị: ' +@BRANCH_ID +N' không tồn tại trong hệ thống'
|
|
27
|
END
|
|
28
|
IF(@Role1 IS NULl)
|
|
29
|
BEGIN
|
|
30
|
--ROLLBACK TRANSACTION
|
|
31
|
--PRINT N'Mã ROLE: '+ @ROLE +N' không tồn tại trong hệ thống'
|
|
32
|
SET @Role1 =(SELECT TOP 1 ID FROM AbpRoles WHERE DisplayName='NVMS')
|
|
33
|
END
|
|
34
|
IF(NOT EXISTS (SELECT * FROM TL_USER WHERE TLNANME = @USER_DOMAIN))
|
|
35
|
BEGIN
|
|
36
|
--PRINT @USER_DOMAIN
|
|
37
|
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])
|
|
38
|
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
|
|
39
|
FROM CM_BRANCH
|
|
40
|
WHERE BRANCH_CODE = @BRANCH_CODE
|
|
41
|
INSERT INTO [AbpUserRoles] ([CreationTime],[CreatorUserId],[RoleId],[TenantId],[UserId])
|
|
42
|
SELECT N'2021-10-06 11:22:39.4900000' AS CreationTime, NULL AS CreatorUserId, @Role1 AS RoleId, 1 AS TenantId, ID AS UserId
|
|
43
|
FROM TL_USER
|
|
44
|
WHERE TLNANME = @USER_DOMAIN
|
|
45
|
UPDATE TL_USER SET SECUR_CODE =@DEP_ID, DEP_ID =@DEP_ID WHERE TLNANME =@USER_DOMAIN
|
|
46
|
END
|
|
47
|
ELSE
|
|
48
|
BEGIN
|
|
49
|
PRINT 'DA TON TAI'
|
|
50
|
--UPDATE TL_USER SET TLSUBBRID = @BRANCH_ID, DEP_ID =@DEP_ID WHERE TLNANME = @USER_DOMAIN
|
|
51
|
--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)
|
|
52
|
END
|
|
53
|
UPDATE TL_USER SET NormalizedUserName = UPPER(TLNANME)
|
|
54
|
FETCH NEXT FROM CURR_USER INTO @USER_DOMAIN,@BRANCH_CODE,@ROLE,@DEP_ID, @FULLNAME
|
|
55
|
END
|
|
56
|
CLOSE CURR_USER -- Đóng Cursor
|
|
57
|
DEALLOCATE CURR_USER -- Giải phóng tài nguyên
|
|
58
|
IF @@ERROR <> 0 SET NOEXEC ON
|
|
59
|
GO
|
|
60
|
COMMIT TRANSACTION
|
|
61
|
GO
|
|
62
|
IF @@ERROR <> 0 SET NOEXEC ON
|
|
63
|
GO
|
|
64
|
-- This statement writes to the SQL Server Log so SQL Monitor can show this deployment.
|
|
65
|
IF HAS_PERMS_BY_NAME(N'sys.xp_logevent', N'OBJECT', N'EXECUTE') = 1
|
|
66
|
BEGIN
|
|
67
|
DECLARE @databaseName AS nvarchar(2048), @eventMessage AS nvarchar(2048)
|
|
68
|
SET @databaseName = REPLACE(REPLACE(DB_NAME(), N'\', N'\\'), N'"', N'\"')
|
|
69
|
SET @eventMessage = N'Redgate SQL Compare: { "deployment": { "description": "Redgate SQL Compare deployed to ' + @databaseName + N'", "database": "' + @databaseName + N'" }}'
|
|
70
|
EXECUTE sys.xp_logevent 55000, @eventMessage
|
|
71
|
END
|
|
72
|
GO
|
|
73
|
DECLARE @Success AS BIT
|
|
74
|
SET @Success = 1
|
|
75
|
SET NOEXEC OFF
|
|
76
|
IF (@Success = 1) PRINT 'The database update succeeded'
|
|
77
|
ELSE BEGIN
|
|
78
|
IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION
|
|
79
|
PRINT 'The database update failed'
|
|
80
|
END
|
|
81
|
GO
|