|
1
|
BEGIN TRANSACTION
|
|
2
|
DECLARE @FULLNAME NVARCHAR(MAX) = N'Đỗ 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,Cao Tiến Diện,Nguyễn Hoàng Vũ, Huỳnh Nhật Hoà,Phạm Hoài An,Đoàn Thị Khánh Linh ,Nguyễn Thị Kim Oanh ,Nguyễn Mạnh Tường'
|
|
3
|
DECLARE @EMP_CODE VARCHAR(MAX) = N'2021-06046,2020-07085,2017-04015,2018-06032,2018-05001,2019-04102,2017-03059,2019-08005,0000-10759,2022-06032,2017-07006,HNI-08-007,2021-10053,2021-12042,0000-10233,0000-10025,2018-05021,0000-16233'
|
|
4
|
DECLARE @USER_NAME VARCHAR(MAX) = 'khangdn0500,hoattt0500,diennv,phucnt2,tungnt1,huanvc,dungbv,trind,phuongvt,tienpa0500,quanmv,dienct,vunh30500,hoahn0500,anph,linhdtk1705,oanhntk1705,tuongnm'
|
|
5
|
DECLARE @EMAIL NVARCHAR(MAX) = N'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,caotiendien@vietbank.com.vn,nguyenhoangvu@vietbank.com.vn,huynhnhathoa@vietbank.com.vn,phamhoaian@vietbank.com.vn,doanthikhanhlinh@vietbank.com.vn,nguyenthikimoanh2@vietbank.com.vn,nguyenmanhtuong@vietbank.com.vn'
|
|
6
|
DECLARE @BRANCH_CODE VARCHAR(MAX) = '0500,0500,0500,0500,0500,0500,0500,0500,0500,0500,0500,0804,0500,0500,0500,1705,1705,1705'
|
|
7
|
DECLARE @DEP_ID_IMP VARCHAR(MAX) = '05N22,05N22,05N22,05N22,05N22,05N22,05N22,05N22,05N22,05N22,05N22,,05N21,05N21,05J03,,,'
|
|
8
|
DECLARE @ROLE VARCHAR(MAX) = 'DVCM,DVCM,DVCM,DVCM,DVCM,DVCM,DVCM,DVCM,DVCM,DVCM,DVCM,NVTT,GDDV,DVCM,DVCM,NVTT,NVTT,GDDV'
|
|
9
|
--DELETE IMPORT_USER_QLTS
|
|
10
|
--INSERT INTO IMPORT_USER_QLTS (TLNAME, FULLNAME, EMAIL, EMP_CODE, BRANCH_CODE, DEP_CODE, ROLE_NAME)
|
|
11
|
--SELECT C.TLNAME,A.FULLNAME,D.EMAIL,B.EMP_CODE,E.BRANCH_CODE,F.DEP_CODE,G.ROLE_NAME
|
|
12
|
--FROM (
|
|
13
|
--SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS ID, VALUE AS FULLNAME
|
|
14
|
--FROM STRING_SPLIT(@FULLNAME,','))A
|
|
15
|
--LEFT JOIN (
|
|
16
|
-- SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS ID, VALUE AS EMP_CODE
|
|
17
|
-- FROM STRING_SPLIT(@EMP_CODE,','))B ON A.ID = B.ID
|
|
18
|
--LEFT JOIN (
|
|
19
|
-- SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS ID, VALUE AS TLNAME
|
|
20
|
-- FROM STRING_SPLIT(@USER_NAME,',')) C ON A.ID = C.ID
|
|
21
|
--LEFT JOIN (
|
|
22
|
-- SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS ID, VALUE AS EMAIL
|
|
23
|
-- FROM STRING_SPLIT(@EMAIL,','))D ON A.ID = D.ID
|
|
24
|
--LEFT JOIN (
|
|
25
|
-- SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS ID, VALUE AS BRANCH_CODE
|
|
26
|
-- FROM STRING_SPLIT(@BRANCH_CODE,','))E ON A.ID = E.ID
|
|
27
|
--LEFT JOIN (
|
|
28
|
-- SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS ID, VALUE AS DEP_CODE
|
|
29
|
-- FROM STRING_SPLIT(@DEP_ID_IMP,','))F ON A.ID = F.ID
|
|
30
|
--LEFT JOIN (
|
|
31
|
-- SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS ID, VALUE AS ROLE_NAME
|
|
32
|
-- FROM STRING_SPLIT(@ROLE,','))G ON A.ID = G.ID
|
|
33
|
DECLARE @INS_MESSAGE NVARCHAR(1000)
|
|
34
|
DECLARE @INS_FULLNAME NVARCHAR(1000)
|
|
35
|
DECLARE @INS_EMP_CODE VARCHAR(1000)
|
|
36
|
DECLARE @INS_USER_NAME VARCHAR(1000)
|
|
37
|
DECLARE @INS_EMAIL NVARCHAR(1000)
|
|
38
|
DECLARE @INS_BRANCH_CODE VARCHAR(1000)
|
|
39
|
DECLARE @INS_DEP_CODE VARCHAR(1000)
|
|
40
|
DECLARE @INS_ROLE VARCHAR(1000)
|
|
41
|
DECLARE cur CURSOR FAST_FORWARD READ_ONLY LOCAL FOR
|
|
42
|
--SELECT TLNAME
|
|
43
|
-- ,FULLNAME
|
|
44
|
-- ,EMAIL
|
|
45
|
-- ,EMP_CODE
|
|
46
|
-- ,BRANCH_CODE
|
|
47
|
-- ,DEP_CODE
|
|
48
|
-- ,ROLE_NAME FROM IMPORT_USER_QLTS
|
|
49
|
-- where TLNAME = 'khoidt'
|
|
50
|
SELECT C.TLNAME,A.FULLNAME,D.EMAIL,B.EMP_CODE,E.BRANCH_CODE,F.DEP_CODE,G.ROLE_NAME
|
|
51
|
FROM (
|
|
52
|
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS ID, VALUE AS FULLNAME
|
|
53
|
FROM STRING_SPLIT(@FULLNAME,','))A
|
|
54
|
LEFT JOIN (
|
|
55
|
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS ID, VALUE AS EMP_CODE
|
|
56
|
FROM STRING_SPLIT(@EMP_CODE,','))B ON A.ID = B.ID
|
|
57
|
LEFT JOIN (
|
|
58
|
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS ID, VALUE AS TLNAME
|
|
59
|
FROM STRING_SPLIT(@USER_NAME,',')) C ON A.ID = C.ID
|
|
60
|
LEFT JOIN (
|
|
61
|
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS ID, VALUE AS EMAIL
|
|
62
|
FROM STRING_SPLIT(@EMAIL,','))D ON A.ID = D.ID
|
|
63
|
LEFT JOIN (
|
|
64
|
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS ID, VALUE AS BRANCH_CODE
|
|
65
|
FROM STRING_SPLIT(@BRANCH_CODE,','))E ON A.ID = E.ID
|
|
66
|
LEFT JOIN (
|
|
67
|
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS ID, VALUE AS DEP_CODE
|
|
68
|
FROM STRING_SPLIT(@DEP_ID_IMP,','))F ON A.ID = F.ID
|
|
69
|
LEFT JOIN (
|
|
70
|
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS ID, VALUE AS ROLE_NAME
|
|
71
|
FROM STRING_SPLIT(@ROLE,','))G ON A.ID = G.ID
|
|
72
|
OPEN cur
|
|
73
|
FETCH NEXT FROM cur INTO @INS_USER_NAME,@INS_FULLNAME,@INS_EMAIL,@INS_EMP_CODE,@INS_BRANCH_CODE,@INS_DEP_CODE,@INS_ROLE
|
|
74
|
WHILE @@FETCH_STATUS = 0 BEGIN
|
|
75
|
DECLARE @MESSAGE NVARCHAR(MAX)
|
|
76
|
IF(EXISTS(SELECT 1 FROM TL_USER tu WHERE tu.TLNANME = @INS_USER_NAME))
|
|
77
|
BEGIN
|
|
78
|
SET @MESSAGE = ISNULL(@INS_USER_NAME,'') + N' Tên đăng nhập đã tồn tại'
|
|
79
|
END
|
|
80
|
|
|
81
|
IF(NOT EXISTS(SELECT 1 FROM AbpRoles WHERE DisplayName = @INS_ROLE))
|
|
82
|
BEGIN
|
|
83
|
SET @MESSAGE = ISNULL(@INS_ROLE,'') + N' Quyền không tồn tại trong hệ thống'
|
|
84
|
END
|
|
85
|
|
|
86
|
IF(NOT EXISTS(SELECT 1 FROM CM_BRANCH cb WHERE cb.BRANCH_CODE = @INS_BRANCH_CODE))
|
|
87
|
BEGIN
|
|
88
|
SET @MESSAGE = ISNULL(@INS_BRANCH_CODE,'') + N' ĐƠN VỊ không tồn tại trong hệ thống'
|
|
89
|
END
|
|
90
|
|
|
91
|
IF(NOT EXISTS(SELECT 1 FROM CM_DEPARTMENT cd WHERE cd.DEP_CODE = @INS_DEP_CODE) AND @INS_DEP_CODE IS NOT NULL AND @INS_DEP_CODE <> '')
|
|
92
|
BEGIN
|
|
93
|
SET @MESSAGE = ISNULL(@INS_DEP_CODE,'') + N' PHÒNG BAN không tồn tại trong hệ thống'
|
|
94
|
END
|
|
95
|
|
|
96
|
|
|
97
|
IF(@MESSAGE IS NOT NULL AND @MESSAGE <> '')
|
|
98
|
BEGIN
|
|
99
|
--DEALLOCATE cur
|
|
100
|
--ROLLBACK TRANSACTION
|
|
101
|
SELECT @MESSAGE as Result
|
|
102
|
--RETURN '-1'
|
|
103
|
END
|
|
104
|
IF(NOT EXISTS(SELECT 1 FROM TL_USER WHERE TLNANME = @INS_USER_NAME))
|
|
105
|
BEGIN
|
|
106
|
DECLARE @BRANCH_ID VARCHAR(15), @DEP_ID VARCHAR(15), @DEP_CODE VARCHAR(100)
|
|
107
|
|
|
108
|
SELECT @BRANCH_ID = cb.BRANCH_ID FROM CM_BRANCH cb WHERE cb.BRANCH_CODE = @INS_BRANCH_CODE
|
|
109
|
SELECT @DEP_ID = cd.DEP_ID FROM CM_DEPARTMENT cd WHERE cd.DEP_CODE = @INS_DEP_CODE
|
|
110
|
SET @DEP_CODE = @INS_DEP_CODE
|
|
111
|
|
|
112
|
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
|
|
113
|
(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)
|
|
114
|
|
|
115
|
INSERT INTO CM_EMPLOYEE_LOG (EMP_CODE, EMP_NAME, BRANCH_CODE, DEP_CODE, USER_DOMAIN, POS_CODE, POS_NAME, CREATE_DT)
|
|
116
|
VALUES (@INS_EMP_CODE, @INS_FULLNAME, @INS_BRANCH_CODE, @INS_DEP_CODE, @INS_USER_NAME, '', N'', GETDATE());
|
|
117
|
|
|
118
|
INSERT INTO AbpUserRoles (CreationTime, CreatorUserId, RoleId, TenantId, UserId)
|
|
119
|
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));
|
|
120
|
END
|
|
121
|
|
|
122
|
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)))
|
|
123
|
BEGIN
|
|
124
|
INSERT INTO AbpUserRoles (CreationTime, CreatorUserId, RoleId, TenantId, UserId)
|
|
125
|
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));
|
|
126
|
END
|
|
127
|
|
|
128
|
FETCH NEXT FROM cur INTO @INS_USER_NAME,@INS_FULLNAME,@INS_EMAIL,@INS_EMP_CODE,@INS_BRANCH_CODE,@INS_DEP_CODE,@INS_ROLE
|
|
129
|
END
|
|
130
|
CLOSE cur
|
|
131
|
DEALLOCATE cur
|
|
132
|
COMMIT TRANSACTION
|