Tạo và cập nhật quy trình duyệt chứng từ

	DECLARE @_BranchCode VARCHAR(24) = 'A03', @_nUserId INT, @_LangId INT
	DECLARE @_DocCode VARCHAR(24) = 'PX', @_DefaultDocStatus TINYINT = 1 -- Trạng thái chứng từ mặc định;
	DECLARE @_RowId_DocProcess VARCHAR(24), @_DocProcessId VARCHAR(24)
	DECLARE @_Stt VARCHAR(24), @_DocDate DATE
 
	IF OBJECT_ID(N'tempdb..#IdList') IS NOT NULL DROP TABLE #IdList
	SELECT AccDoc.Stt, AccDoc.DocDate, AccDoc.DocNo, AccDoc.DocStatus, AccDoc.CreatedBy, AccDoc.CreatedAt
	INTO #IdList
	FROM dbo.B30AccDocItem AccDoc
	WHERE AccDoc.BranchCode = @_BranchCode AND AccDoc.DocCode = @_DocCode AND AccDoc.DocStatus = 1
 
	WHILE EXISTS(SELECT 1 FROM #IdList)
	BEGIN
		SELECT TOP(1) @_Stt = Stt, @_DocDate = DocDate, @_nUserId = CreatedBy FROM #IdList ORDER BY Stt ASC
 
		DELETE dbo.B30AccDocProcess WHERE Stt = @_Stt
		DELETE dbo.B30AccDocProcessUser WHERE Stt = @_Stt
		DELETE dbo.B30AccDocProcessResult WHERE Stt = @_Stt
 
		EXECUTE dbo.usp_B20DocProcess_GetData
			@_Id = NULL,
			@_DocDate = @_DocDate,
			@_BranchCode = @_BranchCode,
			@_DocCode = @_DocCode,
			@_LangId = @_LangId,
			@_IsRunFromApp = 0,
			@_IsSelectResult = 0,
			@_RowId_DocProcess = @_RowId_DocProcess OUTPUT,
			@_DocProcessId = @_DocProcessId OUTPUT
 
		DECLARE @ci varbinary(128)
		SELECT @ci = ISNULL(CONTEXT_INFO(), 0)
		SET CONTEXT_INFO 0x55555;
	
			UPDATE dbo.B30AccDocItem
			SET DocStatus = @_DefaultDocStatus,
				RowId_DocProcess = @_RowId_DocProcess,
				DocProcessId = @_DocProcessId
			WHERE BranchCode = @_BranchCode
				AND DocCode = @_DocCode
				AND Stt = @_Stt
	
		SET CONTEXT_INFO @ci;
 
		EXECUTE dbo.usp_B20DocProcess_CreateProcess_WithoutNotification
			@_Stt = @_Stt,
			@_RowId_DocProcess = @_RowId_DocProcess,
			@_BranchCode = @_BranchCode,
			@_DocCode = @_DocCode,
			@_DefaultDocStatus = @_DefaultDocStatus,
			@_nUserId = @_nUserId,
			@_LangId = @_LangId
 
		DELETE #IdList WHERE Stt = @_Stt
	END
 
	DROP TABLE #IdList

/*
* Tạo các bước duyệt chi tiết cho chứng từ dựa theo Quy trình (xác định bởi hàm usp_B20DocProcess_GetData) và dữ liệu trên phiếu
* + Insert các bước duyệt (ví dụ: 3 bước, 2 bước,...) chưa kiểm tra đến user nào, điều kiện nào
* + Trong từng bước duyệt, kiểm tra điều kiện giá trị dựa theo bảng TableHeader trong B00DmCt để xác định bước đó sẽ có user nào duyệt
-- 07/10/2020 Bổ sung điều kiện thực hiện cho các bước
-- 22/06/2021 Chamvt bổ sung thêm các điều kiện để gửi thông báo khi có chứng từ cần duyệt trên PC
-- 08/09/2022 PhongNVT sửa gửi thông báo qua Mobile, PC, Email theo khai báo từng quy trình duyệt
-- 08/10/2022 THANGNH kiểm tra nếu chưa được duyệt lần nào thì tạo lại quy trình mới
*/
ALTER PROCEDURE dbo.usp_B20DocProcess_CreateProcess_WithoutNotification
	@_Stt UniqueKeyType = '',
	@_RowId_DocProcess UniqueKeyType = '',
	@_BranchCode varchar(3) = '',
	@_DocCode VARCHAR(24) = '',
	@_DefaultDocStatus INT = 0,
	@_nUserId INT = 0,
	@_LangId INT = 0
 
AS
BEGIN
	SET NOCOUNT ON;
 
	DECLARE @_ExecString NVARCHAR(MAX), @_RowId_Detail VARCHAR(16), @_Id_Condition INT, @_ConditionString NVARCHAR(MAX), @_Count INT = 0,
			@_Result BIT, @_Id_Sequen INT, @_ErrorMessage NVARCHAR(128), @_TableHeader VARCHAR(254), @_ProcessKeyColumnName VARCHAR(16),
			@_ProcessTableName VARCHAR(32), @_ProcessUserTableName VARCHAR(64), @_IsCreateInitStep BIT, @_CheckUserList BIT,
			@_IdList_Check VARCHAR(MAX), @_IdList_Original VARCHAR(MAX), @_ConditionDetail NVARCHAR(MAX) = '',
			@ci varbinary(128), @_ProcessList_Original VARCHAR(MAX), @_ProcessList_Check VARCHAR(MAX) = '', -- 03/06/2021 MinhPT: Thêm biến @_ProcessList_Original, @_ProcessList_Check
			@_MobilePush INT, @_PCPush INT, @_SourceTableId INT,  -- 22/06/2021 chamvt thêm
			@_EmailPush INT, @_ExplorerCommandkey VARCHAR(24)  -- 08/09/2022 PhongNVT thêm
 
	SELECT @_TableHeader = TableHeader, @_ProcessTableName = ProcessTableName, 
			@_ProcessKeyColumnName = KeyColumnName, @_ProcessUserTableName = ProcessTableName + 'User',
			@_ExplorerCommandkey = ExplorerCommandkey  -- 08/09/2022 PhongNVT thêm
		FROM dbo.B00DmCt
		WHERE Ma_Ct = @_DocCode
 
	SELECT @_IsCreateInitStep = IsCreateInitStep, 
		@_PCPush = PcPushNoti, @_MobilePush = MobilePushNoti, @_EmailPush = EmailPushNoti -- 08/09/2022 PhongNVT thêm
		FROM dbo.B20DocProcess
		WHERE RowId = @_RowId_DocProcess
 
	-- 08/10/2022 THANGNH kiểm tra chứng từ trước đó có bước 0 hay không
	DECLARE @_ExistsInitStep INT = 0
	SET @_ExecString = 
	N'IF EXISTS (SELECT 1 FROM dbo.' + @_ProcessTableName + ' WHERE ' + @_ProcessKeyColumnName + ' = @_Stt AND RowId_Detail = ''0000'') 
		SET @_ExistsInitStep = 1'
	EXECUTE sp_executesql @_ExecString, N'@_Stt VARCHAR(16), @_ExistsInitStep INT OUTPUT', @_Stt = @_Stt, @_ExistsInitStep = @_ExistsInitStep OUTPUT;
 
	IF (ISNULL(@_RowId_DocProcess, '') <> '')
	BEGIN
		-- 03/06/2021 MinhPT: Lấy giá trị @_ProcessList_Original là các bước duyệt hiện tại
		SELECT @_ExecString = N'
		IF EXISTS(SELECT 1 FROM dbo.' + @_ProcessTableName + ' WHERE ' + @_ProcessKeyColumnName + ' = @_Stt)
		BEGIN
			SELECT @_Count = 1
 
			SELECT @_IdList_Original = COALESCE(ISNULL(@_IdList_Original, '''') + '','', '''') + CAST(Id_Condition AS VARCHAR(MAX))
				FROM dbo.' + @_ProcessUserTableName 
			+ ' WHERE ' + @_ProcessKeyColumnName + ' = @_Stt --AND ISNULL(Id_Condition, 0) <> -99
				GROUP BY Id_Condition
				ORDER BY MAX(Id)
			
			SELECT @_ProcessList_Original = COALESCE(ISNULL(@_ProcessList_Original, '''') + '','', '''') + CAST(RowId_Detail AS VARCHAR(MAX))
				FROM dbo.' + @_ProcessTableName 
			+ ' WHERE ' + @_ProcessKeyColumnName + ' = @_Stt
				GROUP BY RowId_Detail
				ORDER BY MAX(BuiltinOrder)
		END ELSE
			SELECT @_Count = 0, @_IdList_Original = '''', @_ProcessList_Original = '''''
 
		EXECUTE sp_executesql @_ExecString, N'@_Count INT OUTPUT, @_Stt VARCHAR(16), @_IdList_Original VARCHAR(MAX) OUTPUT, @_ProcessList_Original VARCHAR(MAX) OUTPUT', 
									@_Count = @_Count OUTPUT, @_Stt = @_Stt, @_IdList_Original = @_IdList_Original OUTPUT, @_ProcessList_Original = @_ProcessList_Original OUTPUT
 
		IF @_Count > 0
		BEGIN
			IF NOT EXISTS(SELECT 1 FROM dbo.B20DocProcess WHERE RowId = @_RowId_DocProcess)
			BEGIN
				SELECT @_ErrorMessage = N'Quy trình gốc đã bị xóa.'
 
				RAISERROR(@_ErrorMessage, 16, 1);
				GOTO END_PROCESS;
			END
			
			IF OBJECT_ID('TempDb..#TblProcessDetail') IS NOT NULL DROP TABLE #TblProcessDetail;
 
			CREATE TABLE #TblProcessDetail (Id INT, ProcessKeyColumn VARCHAR(16), BuiltinOrder INT, RowId VARCHAR(16), Condition NVARCHAR(MAX))
 
			IF @_IsCreateInitStep = 1
			BEGIN
				INSERT INTO #TblProcessDetail(Id, ProcessKeyColumn, BuiltinOrder, RowId, Condition)
				SELECT CAST(-99 AS INT), @_Stt, CAST(0 AS INT), '0000', ''
			END
 
			INSERT INTO #TblProcessDetail(Id, ProcessKeyColumn, BuiltinOrder, RowId, Condition)
			SELECT Id, @_Stt, BuiltinOrder, RowId, Condition
			FROM dbo.B20DocProcessDetail
			WHERE RowId_DocProcess = @_RowId_DocProcess
 
			WHILE EXISTS(SELECT 1 FROM #TblProcessDetail)
			BEGIN
				SELECT TOP(1) @_RowId_Detail = RowId, @_ConditionDetail = ISNULL(Condition, '')
					FROM #TblProcessDetail
					ORDER BY BuiltinOrder
						
				-- Nếu các bước có điều kiện, và chứng từ bị thay đổi dữ liệu so với điều kiện lúc tạo phiếu thì STOP
				SELECT @_ExecString = N'IF EXISTS (SELECT 1 FROM ' + @_TableHeader 
													+ ' WHERE ' + @_ProcessKeyColumnName + ' = ''' + @_Stt + '''' 
													+ IIF(@_ConditionDetail = '', '', ' AND (' + @_ConditionDetail + ')') 
												+ ') 
											SET @_Result = 1 ELSE SET @_Result = 0'
 
				EXECUTE sp_executesql @_ExecString, N'@_Result INT OUTPUT', @_Result = @_Result OUTPUT;
 
				DECLARE @TblCondition TABLE (Id INT, RowId_Detail VARCHAR(16), Condition NVARCHAR(512), BuiltinOrder INT)
 
				IF @_Result = 1
				BEGIN
					SELECT @_ProcessList_Check = ISNULL(@_ProcessList_Check, '') + ',' + ISNULL(@_RowId_Detail, '')
 
					INSERT INTO @TblCondition (Id, RowId_Detail, Condition, BuiltinOrder)
					SELECT Id, RowId_Detail, ISNULL(Condition, ''), BuiltinOrder
						FROM dbo.B20DocProcessCondition
						WHERE RowId_Detail = @_RowId_Detail
				END
 
				IF @_IsCreateInitStep = 1
				BEGIN
					INSERT INTO @TblCondition (Id, RowId_Detail, Condition, BuiltinOrder)
					SELECT -99, @_RowId_Detail, ISNULL(Condition, ''), BuiltinOrder
						FROM #TblProcessDetail
						WHERE RowId = '0000'
				END
 
				DELETE #TblProcessDetail WHERE RowId = @_RowId_Detail
 
				WHILE EXISTS(SELECT 1 FROM @TblCondition WHERE RowId_Detail = @_RowId_Detail)
				BEGIN
					SELECT TOP(1) @_Id_Condition = Id, @_ConditionString = ISNULL(Condition, '')
						FROM @TblCondition
						WHERE RowId_Detail = @_RowId_Detail
						ORDER BY BuiltinOrder
 
					DELETE FROM @TblCondition
						WHERE Id = @_Id_Condition
 
					-- 03/06/2021 MinhPT: Kiểm tra danh sách các bước duyệt của chứng từ. Do thêm điều kiện rẽ nhánh ở các bước duyệt
					SELECT @_ExecString = N'IF EXISTS(
												SELECT 1 FROM ' + @_TableHeader 
													+ ' WHERE ' + @_ProcessKeyColumnName + ' = ''' + @_Stt + '''' 
													+ IIF(@_ConditionString = '', '', ' AND (' + @_ConditionString + ')') 
												+ ') 
											SELECT @_Result = 1 ELSE SELECT @_Result = 0'
					BEGIN TRY
						EXECUTE sp_executesql @_ExecString, N'@_Result BIT OUTPUT', @_Result = @_Result OUTPUT;
 
						IF (@_Result = 1)
						BEGIN
							SELECT @_IdList_Check = ISNULL(@_IdList_Check, '') + ',' + FORMAT(ISNULL(@_Id_Condition, ''), '#')
 
							DELETE FROM @TblCondition
								WHERE RowId_Detail = @_RowId_Detail
						END
					END TRY
					BEGIN CATCH
						PRINT ERROR_MESSAGE()
						DELETE FROM @TblCondition
							WHERE Id = @_Id_Condition
					END CATCH
				END
			END
 
			-- 03/06/2021 MinhPT: So sánh quy trình cũ mới
			-- 08/10/2022 THANGNH kiểm tra nếu chưa được duyệt lần nào thì tạo lại quy trình mới
			DECLARE @_ExistsResult INT = 0;
 
			SET @_ExecString = N'IF EXISTS(SELECT 1 FROM dbo.' + @_ProcessTableName + 'Result WHERE ' + @_ProcessKeyColumnName + ' = @_Stt) SET @_ExistsResult = 1 ELSE SET @_ExistsResult = 0;'
			EXECUTE sp_executesql @_ExecString, N'@_Stt VARCHAR(16), @_ExistsResult INT OUTPUT', @_Stt = @_Stt, @_ExistsResult = @_ExistsResult OUTPUT;
 
			IF @_ExistsResult = 0
			BEGIN
				SET @_ExecString = N'DELETE dbo.' + @_ProcessTableName + ' WHERE ' + @_ProcessKeyColumnName + ' = @_Stt;' + CHAR(13) + 
					'DELETE dbo.' + @_ProcessUserTableName + ' WHERE ' + @_ProcessKeyColumnName + ' = @_Stt;'
				EXECUTE sp_executesql @_ExecString, N'@_Stt VARCHAR(16)', @_Stt = @_Stt;
			END
			ELSE
			BEGIN
				IF (ISNULL(@_ProcessList_Check, '') <> ISNULL(@_ProcessList_Original, '')) OR (ISNULL(@_IdList_Check, '') <> ISNULL(@_IdList_Original, ''))
				BEGIN			
					SELECT @_ErrorMessage = dbo.ufn_sys_MessageText('NotChangeProcess', @_LangId)
					RAISERROR(@_ErrorMessage, 16, 1);
					GOTO END_PROCESS;
				END
				ELSE
					GOTO END_PROCESS
			END
 
			IF OBJECT_ID('TempDb..#TblProcessDetail') IS NOT NULL DROP TABLE #TblProcessDetail;
 
			--IF (ISNULL(@_IdList_Check, '') <> ISNULL(@_IdList_Original, '')) AND @_ExistsResult = 1
			--BEGIN
			--	IF @_ExistsResult = 1
			--	BEGIN
			--		SELECT @_ErrorMessage = dbo.ufn_sys_MessageText('NotChangeProcess', @_LangId)
 
			--		RAISERROR(@_ErrorMessage, 16, 1);
			--		GOTO END_PROCESS;	
			--	END
			--	ELSE
			--	BEGIN
			--		SET @_ExecString = N'DELETE dbo.' + @_ProcessTableName + ' WHERE ' + @_ProcessKeyColumnName + ' = @_Stt;'
			--		EXECUTE sp_executesql @_ExecString, N'@_Stt VARCHAR(16)', @_Stt = @_Stt;
			--	END
			--END 
			--ELSE
			--	RETURN
		END
	END
 
	IF OBJECT_ID('TempDb..#DocProcessDetail') IS NOT NULL DROP TABLE #DocProcessDetail;
 
	DECLARE @ProcessDetailTmp TABLE (Id INT, BuiltinOrder INT, RowId VARCHAR(16), ApprovalDocStatus INT, 
										RefuseDocStatus INT, RowId_DocProcess VARCHAR(16), IsInitStep BIT, Condition NVARCHAR(MAX),
										IsESign BIT, ESignType VARCHAR(24), ESignDocType VARCHAR(24) --29/05/2023 PhongNVT thêm xử lý ESign
										)
 
	INSERT INTO @ProcessDetailTmp(Id, BuiltinOrder, RowId, ApprovalDocStatus, RefuseDocStatus, RowId_DocProcess, Condition, IsInitStep,
									IsESign, ESignType, ESignDocType --29/05/2023 PhongNVT thêm xử lý ESign
									)
	SELECT d.Id, d.BuiltinOrder, d.RowId, d.ApprovalDocStatus, d.RefuseDocStatus, d.RowId_DocProcess, d.Condition, 
		IIF(@_IsCreateInitStep = 1, 0, IIF(d.BuiltinOrder = 1, 1, 0)) AS IsInitStep,
		d.IsESign, d.ESignType, dp.ESignDocType --29/05/2023 PhongNVT thêm xử lý ESign
	FROM dbo.B20DocProcessDetail AS d
		LEFT JOIN dbo.B20DocProcess dp ON dp.RowId = d.RowId_DocProcess
	WHERE d.RowId_DocProcess = @_RowId_DocProcess;
 
 
	-- 26/09/2019: ThụĐT: Sửa trường hợp tạo bước đệm sẽ để RowID = 0000
	IF @_IsCreateInitStep = 1
	BEGIN
		INSERT INTO @ProcessDetailTmp(Id, BuiltinOrder, RowId, ApprovalDocStatus, RefuseDocStatus, RowId_DocProcess, IsInitStep,
										IsESign, ESignType, ESignDocType --29/05/2023 PhongNVT thêm xử lý ESign
										)
		SELECT TOP (1) CAST(-99 AS INT), CAST(0 AS INT), CAST('0000' AS VARCHAR(16)), @_DefaultDocStatus,
				@_DefaultDocStatus, d.RowId_DocProcess, CAST(1 AS BIT),
				dp.IsESignStep0 AS IsESign, dp.ESignType, dp.ESignDocType --29/05/2023 PhongNVT thêm xử lý ESign
			FROM dbo.B20DocProcessDetail AS d
				LEFT JOIN dbo.B20DocProcess dp ON dp.RowId = d.RowId_DocProcess
			WHERE d.RowId_DocProcess = @_RowId_DocProcess
	END
 
	-- Lấy các bước duyệt dựa theo mã quy trình (RowId_DocProcess)	
	SELECT Id, BuiltinOrder, RowId, ApprovalDocStatus, RefuseDocStatus, Condition, 
			LAG(RowId) OVER (ORDER BY BuiltinOrder) AS PreviousProcess, 
			LEAD(RowId) OVER (ORDER BY BuiltinOrder) AS NextProcess,
			IsInitStep,
			ISNULL(IsESign, 0) AS IsESign, ISNULL(ESignType, '') AS ESignType, ISNULL(ESignDocType, '') AS ESignDocType --29/05/2023 PhongNVT thêm xử lý ESign
		INTO #DocProcessDetail
		FROM @ProcessDetailTmp
		WHERE RowId_DocProcess = @_RowId_DocProcess
 
	-- Lấy các điều kiện duyệt của từng bước duyệt
	IF OBJECT_ID('TempDb..#DocProcessCondition') IS NOT NULL DROP TABLE #DocProcessCondition;
	SELECT Id, BuiltinOrder, RowId_Detail, Condition
		INTO #DocProcessCondition
		FROM dbo.B20DocProcessCondition
		WHERE RowId_Detail IN (SELECT RowId FROM #DocProcessDetail)
 
	IF @_IsCreateInitStep = 1
	BEGIN
		SET IDENTITY_INSERT #DocProcessCondition ON
 
		--26/09/2019: ThụĐT: Sửa trường hợp tạo bước đệm sẽ để RowID= 0000
		INSERT INTO #DocProcessCondition(Id, BuiltinOrder, RowId_Detail, Condition)
		SELECT TOP(1) CAST(-99 AS INT), CAST(0 AS INT), 
				CAST('0000' AS VARCHAR(16)), CAST('' AS VARCHAR(16))
			FROM #DocProcessDetail
 
		SET IDENTITY_INSERT #DocProcessCondition OFF
	END
 
	IF OBJECT_ID('TempDb..#DocProcessUser') IS NOT NULL DROP TABLE #DocProcessUser;
 
	-- Lấy các user duyệt theo các điều kiện
	SELECT Id, ParentId, BuiltinOrder, RowId_Detail, UserId, DocProcessRoleId
		INTO #DocProcessUser
		FROM dbo.B20DocProcessUser
		WHERE ParentId IN (SELECT Id FROM #DocProcessCondition)
	
	IF @_IsCreateInitStep = 1
	BEGIN
		SET IDENTITY_INSERT #DocProcessUser ON
 
		INSERT INTO #DocProcessUser(Id, ParentId, BuiltinOrder, RowId_Detail, UserId)
		SELECT TOP(1) CAST(-99 AS INT), CAST(-99 AS INT), 
				CAST(0 AS INT), CAST('0000' AS VARCHAR(16)), @_nUserId
			FROM #DocProcessCondition
 
		SET IDENTITY_INSERT #DocProcessUser OFF
	END
 
	DECLARE @_DocProcessId VARCHAR(16) = '';
 
	-- 22/10/2020 Tiền xử lý: Loại bỏ các bước không thỏa mãn điều kiện và cập nhật lại PreviousProcess, NextProcess
	-- Không xử lý chung ở vòng WHILE bên dưới được do các dòng không có điều kiện đã được insert vào bảng gốc
	DECLARE @DocProcessDetail0 TABLE (RowId VARCHAR(16), BuiltinOrder INT, Condition NVARCHAR(MAX))
 
	INSERT INTO @DocProcessDetail0 (RowId, BuiltinOrder, Condition)
	SELECT RowId, BuiltinOrder, Condition
	FROM #DocProcessDetail
 
	IF EXISTS (SELECT 1 FROM @DocProcessDetail0 WHERE ISNULL(Condition, '') <> '') 
		OR @_ExistsInitStep <> @_IsCreateInitStep	-- 08/10/2022 THANGNH kiểm tra nếu chưa được duyệt lần nào thì tạo lại quy trình mới (Mà có thay đổi bước 0 - Tự duyệt)
	BEGIN
		WHILE EXISTS(SELECT 1 FROM @DocProcessDetail0)
		BEGIN
			SELECT TOP (1) @_RowId_Detail = RowId, @_ConditionDetail = Condition
				FROM @DocProcessDetail0
				ORDER BY BuiltinOrder
 
			IF ISNULL(@_ConditionDetail, '') <> ''
			BEGIN
				SET @_Result = 0
 
				SELECT @_ExecString = N'
				IF NOT EXISTS (SELECT 1 FROM ' + @_TableHeader + ' WHERE ' + @_ProcessKeyColumnName + ' = ''' + @_Stt + '''' + ' AND (' + @_ConditionDetail + ')) 
					SET @_Result = 1'
 
				--EXECUTE sp_executesql @_ExecString, N'@_Result BIT OUTPUT', @_Result = @_Result OUTPUT;
 
				BEGIN TRY
					EXECUTE sp_executesql @_ExecString, N'@_Result BIT OUTPUT', @_Result = @_Result OUTPUT;
				END TRY
				BEGIN CATCH
					SET @_ErrorMessage = dbo.ufn_sys_MessageText('BizProcess_NoProcess', @_LangId) + CHAR(13) + CHAR(13)
						+ N'Error: ' + ERROR_MESSAGE() + CHAR(13)
 
					RAISERROR(@_ErrorMessage, 16, 1);
 
					GOTO END_PROCESS;
				END CATCH
 
				IF @_Result = 1
				BEGIN
					DELETE #DocProcessDetail WHERE RowId = @_RowId_Detail
					DELETE #DocProcessCondition WHERE RowId_Detail = @_RowId_Detail
					DELETE #DocProcessUser WHERE RowId_Detail = @_RowId_Detail
 
					UPDATE #DocProcessDetail SET PreviousProcess = b.PreviousProcess, NextProcess = b.NextProcess
					FROM #DocProcessDetail a 
						LEFT OUTER JOIN
						(SELECT Id, BuiltinOrder, RowId, 
								LAG(RowId) OVER (ORDER BY BuiltinOrder) AS PreviousProcess, 
								LEAD(RowId) OVER (ORDER BY BuiltinOrder) AS NextProcess
							FROM #DocProcessDetail
						) b ON a.RowId = b.RowId
				END
			END
 
			DELETE @DocProcessDetail0 WHERE RowId = @_RowId_Detail
		END
 
		-- 22/10/2020: Nếu không có bước nào thỏa mãn thì không hợp lệ và không cho lưu. Cập nhật lại DocProcessId
		IF NOT EXISTS (SELECT 1 FROM #DocProcessDetail)
		BEGIN
			SET @ci = ISNULL(CONTEXT_INFO(), 0)
			SET CONTEXT_INFO 0x55555
			
			SET @_ExecString = N'UPDATE dbo.' + @_TableHeader + ' SET RowId_DocProcess = '''' WHERE ' + @_ProcessKeyColumnName + ' = ''' + @_Stt + '''' 
			EXECUTE(@_ExecString)
			
			SET CONTEXT_INFO @ci
 
			SELECT @_ErrorMessage = dbo.ufn_sys_MessageText('BizProcess_NoProcess', @_LangId)
 
			RAISERROR(@_ErrorMessage, 16, 1);
			GOTO END_PROCESS;
		END
 
		-- Xác định lại bước đầu tiên
		UPDATE TOP(1) #DocProcessDetail SET IsInitStep = 1
		WHERE BuiltinOrder = (SELECT MIN(BuiltinOrder) FROM #DocProcessDetail)
 
		-- Cập nhật lại DocProcessId do có thể có sự thay đổi (Bước đầu tiên không thỏa mãn)
		SET @ci = ISNULL(CONTEXT_INFO(), 0)
		SET CONTEXT_INFO 0x55555
 
		SELECT TOP 1 @_DocProcessId = RowId FROM #DocProcessDetail ORDER BY BuiltinOrder ASC
 
		SET @_ExecString = N'UPDATE dbo.' + @_TableHeader + ' SET DocProcessId = ''' + @_DocProcessId + ''' WHERE ' + @_ProcessKeyColumnName + ' = ''' + @_Stt + '''' 
		EXECUTE(@_ExecString)
 
		SET CONTEXT_INFO @ci
	END
 
	-- 03/06/2021 MinhPT: Nếu quy trình tạo ra chỉ có 1 bước số 0 thì coi như quy trình duyệt của phiếu này không có ý nghĩa => báo lỗi
	IF EXISTS(SELECT 1 FROM #DocProcessDetail HAVING MAX(BuiltinOrder) = MIN(BuiltinOrder) AND MIN(BuiltinOrder) = 0)
	BEGIN
		SET @_ErrorMessage = dbo.ufn_sys_MessageText('BizProcess_NoProcess', @_LangId)
 
		RAISERROR(@_ErrorMessage, 16, 1);
 
		GOTO END_PROCESS;
	END
 
	IF OBJECT_ID('TempDb..#ProcessSequen') IS NOT NULL DROP TABLE #ProcessSequen;
	CREATE TABLE #ProcessSequen (Id_Sequen INT)
 
	SELECT TOP 1 @_DocProcessId = RowId FROM #DocProcessDetail ORDER BY BuiltinOrder ASC
 
	-- Xử lý các bước thỏa mãn điều kiện thực hiện
 	WHILE EXISTS(SELECT 1 FROM #DocProcessDetail)
	BEGIN
		SELECT TOP(1) @_RowId_Detail = RowId, @_ConditionDetail = Condition
			FROM #DocProcessDetail
			ORDER BY BuiltinOrder
 
		IF @_ProcessTableName IN ('B30BizDocProcess', 'B30StatsDocProcess') --29/05/2023 PhongNVT thêm xử lý ESign: hiện tại chỉ hỗ trợ cho 1 số chứng từ BizDoc, StatsDoc
		BEGIN
		    SELECT @_ExecString =
				N'INSERT INTO dbo.' + @_ProcessTableName + 
				'(' + @_ProcessKeyColumnName + ', RowId_Detail, BuiltinOrder, ApprovalDocStatus, 
					RefuseDocStatus, PreviousProcess, NextProcess, ApprovedResult, IsESign, ESignType, ESignDocType)' + CHAR(13) +
				N'	OUTPUT Inserted.Id INTO #ProcessSequen(Id_Sequen)' + CHAR(13) +
				N'	SELECT TOP(1) ''' + @_Stt + ''', RowId, BuiltinOrder, ApprovalDocStatus, 
							RefuseDocStatus, ISNULL(PreviousProcess, ''''), ISNULL(NextProcess, ''''),
							IIF(IsInitStep = 1, -1, NULL), IsESign, ESignType, ESignDocType' + CHAR(13) +
				N'		FROM #DocProcessDetail' + CHAR(13) +
				N'		WHERE RowId = ''' + @_RowId_Detail + '''' + CHAR(13) +
				N'		ORDER BY BuiltinOrder'
		END
		ELSE
		BEGIN
		    SELECT @_ExecString =
				N'INSERT INTO dbo.' + @_ProcessTableName + 
				'(' + @_ProcessKeyColumnName + ', RowId_Detail, BuiltinOrder, ApprovalDocStatus, 
					RefuseDocStatus, PreviousProcess, NextProcess, ApprovedResult)' + CHAR(13) +
				N'	OUTPUT Inserted.Id INTO #ProcessSequen(Id_Sequen)' + CHAR(13) +
				N'	SELECT TOP(1) ''' + @_Stt + ''', RowId, BuiltinOrder, ApprovalDocStatus, 
							RefuseDocStatus, ISNULL(PreviousProcess, ''''), ISNULL(NextProcess, ''''),
							IIF(IsInitStep = 1, -1, NULL)' + CHAR(13) +
				N'		FROM #DocProcessDetail' + CHAR(13) +
				N'		WHERE RowId = ''' + @_RowId_Detail + '''' + CHAR(13) +
				N'		ORDER BY BuiltinOrder'
		END
		
	
		EXECUTE sp_executesql @_ExecString
 
		SELECT @_Id_Sequen = Id_Sequen
			FROM #ProcessSequen;
 
		DELETE FROM #ProcessSequen
			WHERE Id_Sequen = @_Id_Sequen;
 
		DELETE FROM #DocProcessDetail
			WHERE RowId = @_RowId_Detail;
 
		WHILE EXISTS(SELECT 1 FROM #DocProcessCondition WHERE RowId_Detail = @_RowId_Detail)
		BEGIN
			SELECT TOP(1) @_Id_Condition = Id, @_ConditionString = ISNULL(Condition, '')
				FROM #DocProcessCondition
				WHERE RowId_Detail = @_RowId_Detail
				ORDER BY BuiltinOrder
 
			DELETE FROM #DocProcessCondition
				WHERE Id = @_Id_Condition
 
			SELECT @_ExecString = N'IF EXISTS(
										SELECT 1 FROM ' + @_TableHeader 
											+ ' WHERE ' + @_ProcessKeyColumnName + ' = ''' + @_Stt + '''' 
											+ IIF(@_ConditionString = '', '', ' AND (' + @_ConditionString + ')')
										+ ') 
									SELECT @_Result = 1 ELSE SELECT @_Result = 0'
			BEGIN TRY
				EXECUTE sp_executesql @_ExecString, N'@_Result BIT OUTPUT', @_Result = @_Result OUTPUT;
 
				IF (@_Result = 1)
				BEGIN
					SELECT @_ExecString =
						N'INSERT INTO dbo.' + @_ProcessUserTableName + 
							'(ParentId, ' + @_ProcessKeyColumnName + ', RowId_Detail, BuiltinOrder, UserId, DocProcessRoleId, Id_Condition)' + CHAR(13) +
						N'SELECT @_Id_Sequen, @_Stt, @_RowId_Detail, BuiltinOrder, UserId, DocProcessRoleId, @_Id_Condition' + CHAR(13) +
						N'	FROM #DocProcessUser' + CHAR(13) +
						N'	WHERE ParentId = @_Id_Condition'
				
					EXECUTE sp_executesql @_ExecString, N'@_Id_Sequen INT, @_Stt VARCHAR(16), @_RowId_Detail VARCHAR(16), @_Id_Condition INT',
											@_Id_Sequen = @_Id_Sequen, @_Stt = @_Stt, @_RowId_Detail = @_RowId_Detail, @_Id_Condition = @_Id_Condition;
 
					DELETE FROM #DocProcessCondition
						WHERE RowId_Detail = @_RowId_Detail
				END
			END TRY
			BEGIN CATCH
				PRINT ERROR_MESSAGE()
				DELETE FROM #DocProcessCondition
					WHERE Id = @_Id_Condition
 
			END CATCH
		END
	
		SELECT @_ExecString = N'IF NOT EXISTS(SELECT 1 FROM dbo.' + @_ProcessUserTableName + ' WHERE ParentId = @_Id_Sequen) 
								SELECT @_CheckUserList = 1 ELSE SELECT @_CheckUserList = 0'
 
		EXECUTE sp_executesql @_ExecString, N'@_Id_Sequen INT, @_CheckUserList BIT OUTPUT',
									@_Id_Sequen = @_Id_Sequen, @_CheckUserList = @_CheckUserList OUTPUT
		
		IF @_CheckUserList = 1
		BEGIN
			SELECT @_ErrorMessage = dbo.ufn_sys_MessageText('BizProcess_NoProcessUser', @_LangId)
 
			RAISERROR(@_ErrorMessage, 16, 1);
			GOTO END_PROCESS;
		END
	END
 
	-- 08/09/2022 PhongNVT sửa gửi thông báo qua Mobile, PC, Email theo khai báo từng quy trình duyệt
	-- 08/09/2022 PhongNVT Rào lại xử lý lấy @_MobilePush, @_PCPush từ B00Config
	--SET @_MobilePush = (SELECT ISNULL(dbo.ufn_vB00Config_GetValue('M_MobilePushNotification', @_BranchCode), 0))
	--SET @_PCPush = (SELECT ISNULL(dbo.ufn_vB00Config_GetValue('M_PCPushNotification', @_BranchCode), 0))
 
	---- Đẩy cảnh báo lên app
	----IF ISNULL(dbo.ufn_vB00Config_GetValue('M_MobilePushNotification', @_BranchCode), 0) = 1 AND ISNULL(@_DocProcessId, '') <> ''
	--IF(@_MobilePush = 1 OR @_PCPush = 1 OR @_EmailPush = 1) AND ISNULL(@_DocProcessId, '') <> ''
	--BEGIN
	--	DECLARE @_UserName NVARCHAR(MAX) = '', @_Body NVARCHAR(256) = '', @_DocNoColumnName VARCHAR(32) = 'DocNo',
	--		@_UserIdList NVARCHAR(MAX) = '', @_Id0 INT, 
	--		@_DocNo NVARCHAR(256) = '', -- 22/06 chamvt thêm; 
	--		@_Title NVARCHAR(256) = '', -- 19/07/22 chamvt sửa đổi
	--		@_PushMobDescription NVARCHAR(500) = ''
 
	--	IF @_TableHeader LIKE 'B30CrmTrans%' SET @_DocNoColumnName = 'TransNo'
 
	--	SELECT @_ExecString = N'SELECT TOP 1 @_DocNo = ' + @_DocNoColumnName + ', @_SourceTableId = Id FROM ' + @_TableHeader + ' WHERE '+ @_ProcessKeyColumnName + ' = ''' + @_Stt + ''''
 
	--	EXECUTE sp_executesql @_ExecString, N'@_TableHeader NVARCHAR(524), @_ProcessKeyColumnName NVARCHAR(16), @_Stt NVARCHAR(16), @_DocNo NVARCHAR(32) OUTPUT, @_SourceTableId INT OUTPUT',
	--								@_TableHeader = @_TableHeader, @_ProcessKeyColumnName = @_ProcessKeyColumnName, @_Stt = @_Stt, @_DocNo = @_DocNo OUTPUT, @_SourceTableId = @_SourceTableId OUTPUT
 
	--	SELECT @_Stt AS Stt, @_DocCode AS DocCode, @_DocNo AS DocNo, @_RowId_DocProcess AS RowId_DocProcess, @_DocProcessId AS DocProcessId,
	--		NULL AS DocProcessIdOrder, 
	--		@_nUserId AS  CreatedBy, @_ExplorerCommandkey AS ExplorerCommandkey, @_SourceTableId AS Id,  -- 08/08/22 chamvt thêm 
	--		@_PCPush AS PCPushNoti, @_MobilePush AS MobilePushNoti, @_EmailPush AS EmailPushNoti, -- 08/09/2022 PhongNVT thêm
	--		2 AS ApprovalStatus, --0: ko duyệt, 1: trả lại, 2: duyệt
	--		@_TableHeader AS SourceTable, '' AS Description
	--	INTO #VoucherListPushNotifi
 
	--	EXEC dbo.usp_sys_PushNotification_Approval 
	--			@_TableTmp = '#VoucherListPushNotifi',                 
	--			@_LangId = @_LangId,                      
	--			@_nUserId = @_nUserId,                     
	--			@_BranchCode = @_BranchCode,                 
	--			@_IdListPush = NULL
 
	--	DROP TABLE IF EXISTS #VoucherListPushNotifi
	--END
	
	
	END_PROCESS:
	IF OBJECT_ID('TempDb..#ProcessSequen') IS NOT NULL DROP TABLE #ProcessSequen;
	IF OBJECT_ID('TempDb..#DocProcessDetail') IS NOT NULL DROP TABLE #DocProcessDetail;
	IF OBJECT_ID('TempDb..#DocProcessCondition') IS NOT NULL DROP TABLE #DocProcessCondition;
	IF OBJECT_ID('TempDb..#DocProcessUser') IS NOT NULL DROP TABLE #DocProcessUser;
	IF OBJECT_ID('TempDb..#TblProcessDetail') IS NOT NULL DROP TABLE #TblProcessDetail;
 
	RETURN;
 
END
 
GO