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

/*

Run this script on:

        (local)\SQLEXPRESS.AMS_UAT_GD3    -  This database will be modified

to synchronize it with:

        192.168.1.230,5036.gAMSPro_BVB_v3_UAT_NB

You are recommended to back up your database before running this script

Script created by SQL Compare version 13.1.6.5463 from Red Gate Software Ltd at 2/23/2022 2:33:07 PM

*/

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

BEGIN TRANSACTION

GO

IF @@ERROR <> 0 SET NOEXEC ON

GO

PRINT N'Dropping extended properties'

GO

PRINT N'Dropping constraints from [dbo].[CON_REQUEST_DOC]'

GO

ALTER TABLE [dbo].[CON_REQUEST_DOC] DROP CONSTRAINT [PK__CON_REQU__06143B5B9A835A09]

GO

IF @@ERROR <> 0 SET NOEXEC ON

GO

PRINT N'Altering [dbo].[CON_MASTER]'

GO

IF @@ERROR <> 0 SET NOEXEC ON

GO

ALTER TABLE [dbo].[CON_MASTER] ADD

[TOTAL_AREA_USE] [decimal] (18, 2) NULL,

[TOTAL_COST] [decimal] (18, 2) NULL,

[UNIT_PRICE] [decimal] (18, 2) NULL

GO

IF @@ERROR <> 0 SET NOEXEC ON

GO

PRINT N'Rebuilding [dbo].[CON_REQUEST_DOC]'

GO

CREATE TABLE [dbo].[RG_Recovery_1_CON_REQUEST_DOC]

(

[CON_REQUEST_DOC_ID] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[REQ_CODE] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[REQ_NAME] [nvarchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[REQ_DT] [datetime] NULL,

[REQ_CONTENT] [nvarchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[CONSTRUCT_ID] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[TOTAL_AMT] [decimal] (18, 0) NULL,

[NOTES] [nvarchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[RECORD_STATUS] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[MAKER_ID] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[CREATE_DT] [datetime] NULL,

[AUTH_STATUS] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[CHECKER_ID] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[APPROVE_DT] [datetime] NULL,

[BRANCH_ID] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[PLAN_TYPE] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[IS_PARENT] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[REQUEST_PARENT] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[BUDGET] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[YEAR_BUDGET] [int] NULL,

[IS_ARISE] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[REQ_ID] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

)

GO

IF @@ERROR <> 0 SET NOEXEC ON

GO

INSERT INTO [dbo].[RG_Recovery_1_CON_REQUEST_DOC]([REQ_CODE], [REQ_NAME], [REQ_DT], [REQ_CONTENT], [CONSTRUCT_ID], [TOTAL_AMT], [NOTES], [RECORD_STATUS], [MAKER_ID], [CREATE_DT], [AUTH_STATUS], [CHECKER_ID], [APPROVE_DT], [BRANCH_ID], [PLAN_TYPE], [IS_PARENT], [REQUEST_PARENT], [BUDGET], [YEAR_BUDGET], [IS_ARISE], [REQ_ID]) SELECT [REQ_CODE], [REQ_NAME], [REQ_DT], [REQ_CONTENT], [CONSTRUCT_ID], [TOTAL_AMT], [NOTES], [RECORD_STATUS], [MAKER_ID], [CREATE_DT], [AUTH_STATUS], [CHECKER_ID], [APPROVE_DT], [BRANCH_ID], [PLAN_TYPE], [IS_PARENT], [REQUEST_PARENT], [BUDGET], [YEAR_BUDGET], [IS_ARISE], [REQ_ID] FROM [dbo].[CON_REQUEST_DOC]

GO

IF @@ERROR <> 0 SET NOEXEC ON

GO

DROP TABLE [dbo].[CON_REQUEST_DOC]

GO

IF @@ERROR <> 0 SET NOEXEC ON

GO

EXEC sp_rename N'[dbo].[RG_Recovery_1_CON_REQUEST_DOC]', N'CON_REQUEST_DOC', N'OBJECT'

GO

IF @@ERROR <> 0 SET NOEXEC ON

GO

PRINT N'Creating primary key [PK__CON_REQU__06143B5B9A835A09] on [dbo].[CON_REQUEST_DOC]'

GO

ALTER TABLE [dbo].[CON_REQUEST_DOC] ADD CONSTRAINT [PK__CON_REQU__06143B5B9A835A09] PRIMARY KEY CLUSTERED  ([CON_REQUEST_DOC_ID])

GO

IF @@ERROR <> 0 SET NOEXEC ON

GO

PRINT N'Altering [dbo].[TR_REQ_ADVANCE_PAYMENT]'

GO

IF @@ERROR <> 0 SET NOEXEC ON

GO

ALTER TABLE [dbo].[TR_REQ_ADVANCE_PAYMENT] ADD

[TK_TGD_XL_TLNAME] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[TK_TGD_XL_DT] [datetime] NULL,

[TK_TGD_APPR_TLNAME] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[TK_TGD_APPR_DT] [datetime] NULL,

[TK_HDQT_XL_TLNAME] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[TK_HDQT_XL_DT] [datetime] NULL,

[TK_HDQT_APPR_TLNAME] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[TK_HDQT_APPR_DT] [datetime] NULL,

[TK_OTHER_XL_TLNAME] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[TK_OTHER_XL_DT] [datetime] NULL,

[TK_OTHER_APPR_TLNAME] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[TK_OTHER_APPR_DT] [datetime] NULL

GO

IF @@ERROR <> 0 SET NOEXEC ON

GO

ALTER TABLE [dbo].[TR_REQ_ADVANCE_PAYMENT] ALTER COLUMN [NOTES] [nvarchar] (4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

GO

IF @@ERROR <> 0 SET NOEXEC ON

GO

PRINT N'Altering [dbo].[CM_DEPARTMENT]'

GO

IF @@ERROR <> 0 SET NOEXEC ON

GO

ALTER TABLE [dbo].[CM_DEPARTMENT] ADD

[KHOI_ID] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

GO

IF @@ERROR <> 0 SET NOEXEC ON

GO

PRINT N'Altering [dbo].[TR_REQ_PAYMENT]'

GO

IF @@ERROR <> 0 SET NOEXEC ON

GO

ALTER TABLE [dbo].[TR_REQ_PAYMENT] ADD

[AMT_PAY] [decimal] (18, 0) NULL

GO

IF @@ERROR <> 0 SET NOEXEC ON

GO

PRINT N'Altering [dbo].[BUD_UTIL_LOG]'

GO

IF @@ERROR <> 0 SET NOEXEC ON

GO

ALTER TABLE [dbo].[BUD_UTIL_LOG] ADD

[TOTAL_AMT_INIT] [decimal] (18, 0) NULL,

[VAT_INIT] [numeric] (18, 0) NULL,

[IS_FIRST_TIME] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[CUSTOMER_ID] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

GO

IF @@ERROR <> 0 SET NOEXEC ON

GO

PRINT N'Altering [dbo].[BUD_MASTER]'

GO

IF @@ERROR <> 0 SET NOEXEC ON

GO

ALTER TABLE [dbo].[BUD_MASTER] ADD

[CONTRACT_ID] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[IS_SEND_APPR] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[SEND_APPR_DT] [datetime] NULL,

[SIGN_USER] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[SIGN_DT] [datetime] NULL

GO

IF @@ERROR <> 0 SET NOEXEC ON

GO

PRINT N'Altering [dbo].[BUD_CONTRACT_MASTER]'

GO

IF @@ERROR <> 0 SET NOEXEC ON

GO

ALTER TABLE [dbo].[BUD_CONTRACT_MASTER] ADD

[IS_SEND_APPR] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[SEND_APPR_DT] [datetime] NULL,

[SIGN_USER] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[SIGN_DT] [datetime] NULL

GO

IF @@ERROR <> 0 SET NOEXEC ON

GO

PRINT N'Altering [dbo].[BUD_CONTRACT_DT]'

GO

IF @@ERROR <> 0 SET NOEXEC ON

GO

ALTER TABLE [dbo].[BUD_CONTRACT_DT] ADD

[DIEN_TICH_TANG] [decimal] (18, 0) NULL,

[DIEN_TICH_SD_NOI_BO] [decimal] (18, 0) NULL,

[DIEN_TICH_DA_CHO_THUE] [decimal] (18, 0) NULL,

[DIEN_TICH_CON_LAI] [decimal] (18, 0) NULL,

[DIEN_TICH_CAN_SD] [decimal] (18, 0) NULL,

[REASON] [nvarchar] (max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[PRICE] [decimal] (18, 0) NULL,

[TOTAL_AMT] [decimal] (18, 0) NULL,

[DIEN_TICH_CON_LAI_FINAL] [decimal] (18, 0) NULL,

[BRANCH_ID] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[DEP_ID] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

GO

IF @@ERROR <> 0 SET NOEXEC ON

GO

PRINT N'Altering [dbo].[TR_REQ_PAY_ATTACH]'

GO

IF @@ERROR <> 0 SET NOEXEC ON

GO

ALTER TABLE [dbo].[TR_REQ_PAY_ATTACH] ADD

[LICENSE_DT] [datetime] NULL

GO

IF @@ERROR <> 0 SET NOEXEC ON

GO

PRINT N'Altering [dbo].[RET_MASTER]'

GO

IF @@ERROR <> 0 SET NOEXEC ON

GO

ALTER TABLE [dbo].[RET_MASTER] ADD

[FLUCTUATING_DT] [datetime] NULL,

[REASON_FLUCTUATING] [nvarchar] (max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

GO

IF @@ERROR <> 0 SET NOEXEC ON

GO

PRINT N'Altering [dbo].[REAL_ESTATE_R_H]'

GO

IF @@ERROR <> 0 SET NOEXEC ON

GO

ALTER TABLE [dbo].[REAL_ESTATE_R_H] ADD

[TR_CONTRACT_ID] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[EXTEND_DT] [datetime] NULL,

[STRUCTURE] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

GO

IF @@ERROR <> 0 SET NOEXEC ON

GO

PRINT N'Creating trigger [dbo].[triger_TL_AbpUserRoles_ROLENAME] on [dbo].[AbpUserRoles]'

GO

CREATE TRIGGER [dbo].[triger_TL_AbpUserRoles_ROLENAME]

ON [dbo].[AbpUserRoles]

AFTER  INSERT,UPDATE

AS

BEGIN

DECLARE @ROLE_NAME VARCHAR(20);

SET @ROLE_NAME = (SELECT TOP 1 R.DisplayName FROM  Inserted LEFT JOIN dbo.AbpRoles R ON R.Id=Inserted.RoleId)

UPDATE dbo.TL_USER SET TL_USER.RoleName=@ROLE_NAME WHERE TL_USER.ID IN (SELECT Inserted.UserId FROM Inserted)

END

GO

IF @@ERROR <> 0 SET NOEXEC ON

GO

PRINT N'Creating trigger [dbo].[triger_TL_USER_SECUR_CODE] on [dbo].[TL_USER]'

GO

CREATE TRIGGER [dbo].[triger_TL_USER_SECUR_CODE]

ON [dbo].[TL_USER]

AFTER  INSERT,UPDATE

AS

BEGIN

UPDATE dbo.TL_USER SET TL_USER.SECUR_CODE=TL_USER.DEP_ID WHERE TL_USER.ID IN (SELECT Inserted.ID FROM Inserted)

END

GO

IF @@ERROR <> 0 SET NOEXEC ON

GO

PRINT N'Creating primary key [PK_ASS_CONFIRM_COLLECT_MULTI_DT] on [dbo].[ASS_COLLECT_CONFIRM_DT]'

GO

ALTER TABLE [dbo].[ASS_COLLECT_CONFIRM_DT] ADD CONSTRAINT [PK_ASS_CONFIRM_COLLECT_MULTI_DT] PRIMARY KEY CLUSTERED  ([COLLECT_MULTI_CONFIRM_ID])

GO

IF @@ERROR <> 0 SET NOEXEC ON

GO

PRINT N'Creating primary key [PK_ASS_COLLECT_MULTI_CONFIRM_MASTER] on [dbo].[ASS_COLLECT_CONFIRM_MASTER]'

GO

ALTER TABLE [dbo].[ASS_COLLECT_CONFIRM_MASTER] ADD CONSTRAINT [PK_ASS_COLLECT_MULTI_CONFIRM_MASTER] PRIMARY KEY CLUSTERED  ([COL_MULTI_MASTER_CONFIRM_ID])

GO

IF @@ERROR <> 0 SET NOEXEC ON

GO

PRINT N'Creating primary key [PK_ASS_CONFIRM_TRANSFER_MULTI_DT] on [dbo].[ASS_TRANSFER_CONFIRM_DT]'

GO

ALTER TABLE [dbo].[ASS_TRANSFER_CONFIRM_DT] ADD CONSTRAINT [PK_ASS_CONFIRM_TRANSFER_MULTI_DT] PRIMARY KEY CLUSTERED  ([TRANSFER_MULTI_CONFIRM_ID])

GO

IF @@ERROR <> 0 SET NOEXEC ON

GO

PRINT N'Creating primary key [PK_ASS_CONFIRM_TRANSFER_MULTI_MASTER] on [dbo].[ASS_TRANSFER_CONFIRM_MASTER]'

GO

ALTER TABLE [dbo].[ASS_TRANSFER_CONFIRM_MASTER] ADD CONSTRAINT [PK_ASS_CONFIRM_TRANSFER_MULTI_MASTER] PRIMARY KEY CLUSTERED  ([TRANS_MULTI_MASTER_CONFIRM_ID])

GO

IF @@ERROR <> 0 SET NOEXEC ON

GO

PRINT N'Creating primary key [PK_USER_CONFIRM_MASTER_ID] on [dbo].[ASS_USE_CONFIRM_MASTER]'

GO

ALTER TABLE [dbo].[ASS_USE_CONFIRM_MASTER] ADD CONSTRAINT [PK_USER_CONFIRM_MASTER_ID] PRIMARY KEY CLUSTERED  ([USER_CONFIRM_MASTER_ID])

GO

IF @@ERROR <> 0 SET NOEXEC ON

GO

PRINT N'Creating primary key [PK_USE_MULTI_ID] on [dbo].[ASS_USE_MULTI_DT]'

GO

ALTER TABLE [dbo].[ASS_USE_MULTI_DT] ADD CONSTRAINT [PK_USE_MULTI_ID] PRIMARY KEY CLUSTERED  ([USE_MULTI_ID])

GO

IF @@ERROR <> 0 SET NOEXEC ON

GO

PRINT N'Creating extended properties'

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