/*
Run this script on:

        DESKTOP-Q8R1K35.VCCB_AMS_LIVE_STABLE    -  This database will be modified

to synchronize it with:

        118.69.72.241,5036.gAMSPro_BVB_v3_FINAL

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/22/2023 10:11:46 AM

*/
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'Creating [dbo].[CM_REQUEST_TEMPLATE_APPROVES_ById]'
GO

CREATE PROCEDURE [dbo].[CM_REQUEST_TEMPLATE_APPROVES_ById]
@p_REQ_ID	varchar(100)
AS
SELECT 	 
STUFF( (select ';' + APPROVE_USERNAME from CM_APPROVE_GROUP WHERE REQ_ID=A.REQ_ID AND STEP_LEVEL =A.STEP_LEVEL AND AUTH_STATUS='U'  FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '') AS SHARE_USER
FROM CM_APPROVE_GROUP A WHERE REQ_ID= @p_REQ_ID AND AUTH_STATUS='U'
GROUP BY STEP_LEVEL,REQ_ID,AUTH_STATUS
ORDER BY STEP_LEVEL ASC
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[CM_WORKFLOW_APPROVE_DATA]'
GO

CREATE PROCEDURE [dbo].[CM_WORKFLOW_APPROVE_DATA] 
	@p_REQ_ID varchar(20)=null,
	@p_DONE BIT = NULL,
	@p_IsNote BIT = NULL
AS
BEGIN 
	--Table 1
	SELECT 
	A.APPROVE_GROUP_ID,
    A.APPROVE_USERNAME,
    A.AUTHORITY_NAME,
    A.STEP_LEVEL,
    A.PROCESS_STATUS,
    A.DONE,
    A.NOTES,
    A.RECORD_STATUS,
    A.AUTH_STATUS,
    A.MAKER_ID,
    A.CREATE_DT,
    A.CHECKER_ID,
	FORMAT(A.APPROVE_DT, 'dd/MM/yyyy hh:mm:ss') AS 'APPROVE_DT',
    A.REQ_ID,
    A.IS_REJECT,
    A.NEED_SEND_EMAIL,
    A.[TYPE],
    A.APPROVE_ROOT_ID,
    A.ORDER_STEP,
	CASE
		WHEN GR.APPROVE_USERNAME IS NOT NULL THEN 'TUQ.' + ER.POS_NAME
		ELSE E.POS_NAME 
	END AS 'POS_NAME',
	A.APPROVE_ROOT_ID,
	UA.TLFullName AS 'APPROVE_FULL_NAME'
	FROM CM_APPROVE_GROUP A
	LEFT JOIN TL_USER UA ON UA.TLNANME = A.APPROVE_USERNAME
	LEFT JOIN CM_EMPLOYEE_LOG E ON E.USER_DOMAIN = UA.TLNANME
	LEFT JOIN dbo.CM_APPROVE_GROUP GR ON A.APPROVE_ROOT_ID = GR.APPROVE_GROUP_ID
	LEFT JOIN CM_EMPLOYEE_LOG ER ON GR.APPROVE_USERNAME = ER.USER_DOMAIN
	WHERE 1=1
	AND A.REQ_ID = @p_REQ_ID
	AND A.DONE = 1
	ORDER BY STEP_LEVEL ASC, ORDER_STEP ASC, CREATE_DT ASC

	--Table 2
	SELECT 
		N'Yêu cầu đã được ' +
		CASE 
			WHEN A.[ACTION] = 'AUT' THEN N'uỷ quyền '
			WHEN A.[ACTION] = 'ADD' THEN N'tạo '
			WHEN A.[ACTION] = 'UPD' THEN N'cập nhật '
			WHEN A.[ACTION] = 'APP' THEN N'duyệt '
			WHEN A.[ACTION] = 'REJ' THEN N'từ chối '
			WHEN A.[ACTION] = 'HAN' THEN N'bàn giao '
			ELSE ''
		END + 
		N'bởi ' + UA.TLFullName + 
		CASE 
			WHEN A.[ACTION] = 'AUT' THEN N' cho ' + UA2.TLFullName
			ELSE ''
		END + 
		N' vào lúc: ' + FORMAT(A.APPROVE_DT, 'dd/MM/yyyy HH:mm:ss') + '.' AS 'LOG'
	FROM CM_TEMPLATE_LOG A
	LEFT JOIN TL_USER UA ON UA.TLNANME = A.CHECKER_ID
	LEFT JOIN CM_APPROVE_GROUP G ON G.APPROVE_GROUP_ID = A.APPROVE_GROUP_ID
	LEFT JOIN TL_USER UA2 ON UA2.TLNANME = G.AUTHORITY_NAME
	WHERE 1=1
	AND A.REQ_ID = @p_REQ_ID

	--Table 3
	IF(@p_IsNote = 1)
	BEGIN
		SELECT 
			UA.TLFullName + FORMAT(A.CREATE_DT, ' (dd/MM/yyyy HH:mm:ss): ') + A.CONTENT + '.' AS 'NOTE'
		FROM CM_TEMPLATE_NOTE A
		LEFT JOIN TL_USER UA ON A.TEMPLATE_NOTE_USERNAME = UA.TLNANME 
		WHERE 1=1
		AND A.REQ_ID = @p_REQ_ID
	END
	ELSE
	BEGIN
		SELECT ' ' AS 'NOTE'
	END
END

GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[CM_REQUEST_TEMPLATE_Close_Template]'
GO
CREATE PROCEDURE [dbo].[CM_REQUEST_TEMPLATE_Close_Template] 
	@p_REQUEST_TEMPLATE_ID nvarchar(200)  = NULL,
	@p_REQUEST_TEMPLATE_NAME	nvarchar(200)  = NULL,
	@p_REQUEST_TEMPLATE_CODE	nvarchar(100)  = NULL,
	@p_CREATE_LOCATION NVARCHAR(100)=NULL,
	@p_HEADER NVARCHAR(max)=NULL,
	@p_CONTENT NVARCHAR(max)=NULL,
	@p_BRANCH_ID NVARCHAR(50)=NULL,
	@p_REPORT_NO NVARCHAR(50)=NULL,
	@p_TITLE NVARCHAR(50)=NULL,
	@p_REPORT_DT	VARCHAR(20) = NULL,
	@p_SCHEME_IN BIT,
	@p_TYPE_TEMPLATE_ID NVARCHAR(20)=NULL,
	@p_NOTES	nvarchar(max)  = NULL,
	@p_RECORD_STATUS	varchar(1)  = NULL,
	@p_MAKER_ID	varchar(12)  = NULL,
	@p_CREATE_DT	VARCHAR(20) = NULL,
	@p_AUTH_STATUS	varchar(50)  = NULL,
	@p_CHECKER_ID	varchar(12)  = NULL,
	@p_APPROVE_DT	VARCHAR(20) = NULL,
	@p_REQUEST_TEMPLATE_XML nvarchar(MAX) = NULL
AS
BEGIN
	--Validation is here
	DECLARE @ERRORSYS NVARCHAR(15) = '',
		@l_REQUEST_TEMPLATE_DETAIL_ID varchar(15),
		@l_REQUEST_TEMPLATE_DETAIL_CONTENT nvarchar(max),
		@l_REQUEST_TEMPLATE_DETAIL_CODE varchar(15)
	IF @ERRORSYS <> '' 
	BEGIN
		SELECT ErrorCode Result, ''  CAR_ID, ErrorDesc ErrorDesc FROM SYS_ERROR WHERE ErrorCode = @ERRORSYS
		RETURN '0'
	END

	BEGIN TRANSACTION  

	UPDATE [dbo].[CM_REQUEST_TEMPLATE]
	SET RECORD_STATUS=0
	  WHERE [REQUEST_TEMPLATE_ID] = @p_REQUEST_TEMPLATE_ID
		IF @@Error <> 0 GOTO ABORT

COMMIT TRANSACTION

SELECT '0' as Result, @p_REQUEST_TEMPLATE_ID  ID, '' ErrorDesc
RETURN '0'
ABORT:
BEGIN
		
		ROLLBACK TRANSACTION
		SELECT '-1' as Result, '' ID, '' ErrorDesc
		RETURN '-1'
END
END
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[CM_REQUEST_TEMPLATE_Open_Template]'
GO
CREATE PROCEDURE [dbo].[CM_REQUEST_TEMPLATE_Open_Template] 
	@p_REQUEST_TEMPLATE_ID nvarchar(200)  = NULL,
	@p_REQUEST_TEMPLATE_NAME	nvarchar(200)  = NULL,
	@p_REQUEST_TEMPLATE_CODE	nvarchar(100)  = NULL,
	@p_CREATE_LOCATION NVARCHAR(100)=NULL,
	@p_HEADER NVARCHAR(max)=NULL,
	@p_CONTENT NVARCHAR(max)=NULL,
	@p_BRANCH_ID NVARCHAR(50)=NULL,
	@p_REPORT_NO NVARCHAR(50)=NULL,
	@p_TITLE NVARCHAR(50)=NULL,
	@p_REPORT_DT	VARCHAR(20) = NULL,
	@p_SCHEME_IN BIT,
	@p_TYPE_TEMPLATE_ID NVARCHAR(20)=NULL,
	@p_NOTES	nvarchar(max)  = NULL,
	@p_RECORD_STATUS	varchar(1)  = NULL,
	@p_MAKER_ID	varchar(12)  = NULL,
	@p_CREATE_DT	VARCHAR(20) = NULL,
	@p_AUTH_STATUS	varchar(50)  = NULL,
	@p_CHECKER_ID	varchar(12)  = NULL,
	@p_APPROVE_DT	VARCHAR(20) = NULL,
	@p_REQUEST_TEMPLATE_XML nvarchar(MAX) = NULL
AS
BEGIN
	--Validation is here
	DECLARE @ERRORSYS NVARCHAR(15) = '',
		@l_REQUEST_TEMPLATE_DETAIL_ID varchar(15),
		@l_REQUEST_TEMPLATE_DETAIL_CONTENT nvarchar(max),
		@l_REQUEST_TEMPLATE_DETAIL_CODE varchar(15)
	IF @ERRORSYS <> '' 
	BEGIN
		SELECT ErrorCode Result, ''  ID, ErrorDesc ErrorDesc FROM SYS_ERROR WHERE ErrorCode = @ERRORSYS
		RETURN '0'
	END
	IF(EXISTS(SELECT * 
				FROM CM_REQUEST_TEMPLATE 
				WHERE REPORT_NO = @p_REPORT_NO
				AND REQUEST_TEMPLATE_ID <> @p_REQUEST_TEMPLATE_ID 
				AND RECORD_STATUS = 1))
	BEGIN
		SELECT '-1' as Result, '' ID, N'Số tờ trình đã được sử dụng ở tờ trình khác. Vui lòng kiểm tra lại' ErrorDesc
		RETURN '-1'
	END
	
	BEGIN TRANSACTION  
	
	UPDATE [dbo].[CM_REQUEST_TEMPLATE]
	SET RECORD_STATUS=1
	  WHERE [REQUEST_TEMPLATE_ID] = @p_REQUEST_TEMPLATE_ID
		IF @@Error <> 0 GOTO ABORT

COMMIT TRANSACTION

SELECT '0' as Result, @p_REQUEST_TEMPLATE_ID  ID, '' ErrorDesc
RETURN '0'
ABORT:
BEGIN
		
		ROLLBACK TRANSACTION
		SELECT '-1' as Result, '' ID, '' ErrorDesc
		RETURN '-1'
END
END
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[TR_REQ_PAY_AUTO_RECURRING_ById]'
GO
CREATE   PROC [dbo].[TR_REQ_PAY_AUTO_RECURRING_ById]
@p_REQ_PAY_AUTO_ID varchar(15)
AS
BEGIN
	SELECT A.*
	FROM TR_REQ_PAY_AUTO_RECURRING A 
	 WHERE A.REQ_PAY_AUTO_ID =@p_REQ_PAY_AUTO_ID
END
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[TR_REQ_PAY_AUTO_BUDGET_ALLOCATION_ById]'
GO
CREATE   PROC [dbo].[TR_REQ_PAY_AUTO_BUDGET_ALLOCATION_ById]
@p_REQ_PAY_AUTO_ID varchar(15)
AS
BEGIN
	SELECT A.*, B.BRANCH_CODE, C.DEP_CODE, B.BRANCH_CODE + ' - ' + B.BRANCH_NAME AS BRANCH_FULL_NAME, C.DEP_CODE + ' - ' + C.DEP_NAME AS DEP_FULL_NAME
	FROM TR_REQ_PAY_AUTO_BUDGET_ALLOCATION A 
	LEFT JOIN  CM_BRANCH B ON A.BRANCH_ID = B.BRANCH_ID
	LEFT JOIN CM_DEPARTMENT C ON A.DEP_ID = C.DEP_ID
	WHERE A.REQ_PAY_AUTO_ID =@p_REQ_PAY_AUTO_ID
END
GO
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