DỰ ÁN PHẦN MỀM QUẢN LÝ TÀI SẢN - NH BẢN VIỆT - GD3

----B1

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(100), @BRANCH_CODE VARCHAR(50), @ROLE VARCHAR(100), @DEP_ID VARCHAR(15), @FULLNAME NVARCHAR(500)

DECLARE @Role1 INT, @Role2 INT, @BRANCH_ID VARCHAR(15)

DECLARE CURR_USER CURSOR LOCAL FOR  

SELECT TOP 50 LOWER(TLNANME),BRANCH_CODE,RoleName,SECUR_CODE,TLFullName FROM dbo.TL_USER_V2 tuv  WHERE TLNANME NOT IN (SELECT TLNANME FROM 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

--B2

UPDATE TL_SYS_ROLE_MAPPING SET IS_MAIN = 1

-- B3

SELECT COUNT(*) FROM TL_SYS_ROLE_MAPPING --93

SELECT COUNT(*) FROM AbpUserRoles aur --3829

INSERT INTO TL_SYS_ROLE_MAPPING (ROLE_OLD, ROLE_NEW, TLNAME, DEP_ID, BRANCH_ID, EFF_DATE, EXP_DATE, NOTES, AUTH_STATUS, RECORD_STATUS, MAKER_ID, CHECKER_ID, IS_MAIN)

SELECT tu.RoleName, ar.DisplayName, tu.TLNANME,tu.DEP_ID,tu.TLSUBBRID,NULL,NULL,NULL,'A',1,'Admin','Admin',0

FROM TL_USER tu

JOIN AbpUserRoles aur ON tu.ID = aur.UserId

JOIN AbpRoles ar ON aur.RoleId = ar.Id

SELECT COUNT(*) FROM TL_SYS_ROLE_MAPPING

INSERT INTO AbpUserRoles (CreationTime, CreatorUserId, RoleId, TenantId, UserId)

SELECT DISTINCT SYSDATETIME(),678 AS CreatorUserId,ar.ID AS RoleId,0,B.ID AS UserId

FROM TL_SYS_ROLE_MAPPING A

JOIN TL_USER B ON B.TLNANME =A.TLNAME

LEFT JOIN AbpUserRoles aur  ON B.ID = aur.UserId

LEFT JOIN AbpRoles ar ON ar.DisplayName = A.ROLE_NEW

WHERE A.IS_MAIN = 1 AND ar.Id <> aur.RoleId

SELECT UserId,RoleId 

FROM AbpUserRoles 

WHERE UserId

IN(SELECT UserId FROM AbpUserRoles GROUP BY UserId

HAVING COUNT(RoleId)>2)