TEST1

USE [app]
GO
/****** Object:  StoredProcedure [dbo].[usp_Gfm_CreateGForm]    Script Date: 2023/11/15 15:27:56 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
-- =============================================
-- Author:		<Kitty Zuo>
-- Create date: <2023-11-14>
-- Description:	<根据JSON传参，将已产生的Group Form写入GFM_FORM_R和GFM_FORM_APPROVER_R>
-- =============================================

--本SP需增加try、catch和trans机制，当更新db有异常时需rollback已写入的数据，只记录该requestLog，并把Catch到的异常信息写入到LogMsg栏位！！！

ALTER PROCEDURE [dbo].[usp_Gfm_CreateGForm] @json NVARCHAR(MAX) AS BEGIN SET NOCOUNT ON;

-- 解析 JSON 参数
DECLARE @company NVARCHAR(50), @formKind NVARCHAR(50), @formNo NVARCHAR(50);
DECLARE @approveContents NVARCHAR(MAX),@gfmWorkFlowId NVARCHAR(50),@gfmFormNo NVARCHAR(50),@requestId NVARCHAR(50);
DECLARE @phaseId NVARCHAR(50), @roleName NVARCHAR(255), @approveId NVARCHAR(50), @approverNo NVARCHAR(50);
DECLARE @gfmEmpId NVARCHAR(50),@logMsg NVARCHAR(MAX);
SELECT @company = [value] FROM OPENJSON(@json) WHERE [key] = 'company';
SELECT @formKind = [value] FROM OPENJSON(@json) WHERE [key] = 'formKind';
SELECT @formNo = [value] FROM OPENJSON(@json) WHERE [key] = 'formNo';
SELECT @approveContents = [value] FROM OPENJSON(@json) WHERE [key] = 'approveContents';
SELECT @gfmWorkFlowId = [value] FROM OPENJSON(@json) WHERE [key] = 'gfmWorkFlowId';
SELECT @gfmFormNo = [value] FROM OPENJSON(@json) WHERE [key] = 'gfmFormNo'; 
SELECT @requestId = [value] FROM OPENJSON(@json) WHERE [key] = 'requestId'; 
DECLARE @approversJSON NVARCHAR(MAX) = (SELECT [value] FROM OPENJSON(@json) WHERE [key] = 'approversInfo');
DECLARE @approversCursor INT;
DECLARE @approversCount INT;
DECLARE @currentApprover NVARCHAR(MAX);
DECLARE @approversEmpNo NVARCHAR(255);
DECLARE @approversApproveId nvarchar(50);
DECLARE @approversGfmEmpId nvarchar(50);
set @approversGfmEmpId = '';
	-- 写入主表数据 GFM_FORM_R

	SET NOCOUNT ON;
	 BEGIN TRY
	 BEGIN TRAN

	BEGIN    
		INSERT INTO GFM_FORM_R (FormKind,FormNo,Company,FormBeginDate,ApproveContents,FormStatus,ActionStatus,GfmWorkFlowId,GfmFormNo,CreateBy,CreateDate,UpdateBy,UpdateDate,Active)
		VALUES(@formKind, @formNo, @company,GETDATE(),@approveContents,'UA','CreateForm_Success',@gfmWorkFlowId,@gfmFormNo,'CreateForm',GETDATE(),'CreateForm',GETDATE(),'Y');
	END

	--将签核人员数据写入GFM_FORM_APPROVER_R（后台传参时要处理为需要写入的人员才传入）
	BEGIN
		-- 计算审批人个数
		
		SELECT @approversCount = COUNT(*) FROM OPENJSON(@approversJSON);
		SELECT @approversCursor = 0, @approversCount = COUNT(*) FROM OPENJSON(@approversJSON);

		-- 循环处理每个审批人
		WHILE @approversCursor < @approversCount
		BEGIN
			-- 获取当前审批人数据
			SET @currentApprover = (SELECT [value] FROM OPENJSON(@approversJSON) WHERE [key] = CAST(@approversCursor AS NVARCHAR(10)));

			-- 解析当前审批人数据
			SELECT @phaseId = [value] FROM OPENJSON(@currentApprover) WHERE [key] = 'phaseId';
			SELECT @roleName = [value] FROM OPENJSON(@currentApprover) WHERE [key] = 'roleName';
			SELECT @approveId = [value] FROM OPENJSON(@currentApprover) WHERE [key] = 'approveId';
			SELECT @approverNo = [value] FROM OPENJSON(@currentApprover) WHERE [key] = 'approverNo';
			SELECT @gfmEmpId = [value] FROM OPENJSON(@currentApprover) WHERE [key] = 'gfmEmpId';
			
			-- 写入数据到表 GFM_FORM_APPROVER_R
			INSERT INTO GFM_FORM_APPROVER_R (formKind, formNo, phaseId, roleName, approveId, ApproverEmpNo,Company,GfmFormNo,gfmEmpId,CreateBy,CreateDate,UpdateBy,UpdateDate,Active)
			VALUES (@formKind, @formNo, @phaseId, @roleName, @approveId, @approverNo,@company,@gfmFormNo,@gfmEmpId,'CreateForm',GETDATE(),'CreateForm',GETDATE(),'Y');

			---- 计数器自增
			 SET @approversCursor = @approversCursor + 1;     
			   IF @approversCursor=1
					SET @approversEmpNo = @approverNo;
					SET @approversApproveId=@approveId;
					SET @approversGfmEmpId=@gfmEmpId;
			   IF @approversCursor>1 
					SET @approversEmpNo =@approversEmpNo+','+ @approverNo;
					SET @approversApproveId=@approversApproveId+','+@approveId;
					SET @approversGfmEmpId=@approversGfmEmpId+','+@gfmEmpId;
		PRINT @approversGfmEmpId
		END;
	END;
	PRINT @approversGfmEmpId
	COMMIT TRAN
	 END TRY
	 BEGIN CATCH------------有异常被捕获
        IF @@TRANCOUNT > 0---------------判断有没有事务
        BEGIN
            ROLLBACK TRAN----------回滚事务
        END 
			-----------记录存储过程执行时的错误信息，自定义
    END CATCH--------结束异常处理
	
	-- 记录Log [GFM_REQUEST_LOG]
	--BEGIN    
	--	INSERT INTO [GFM_REQUEST_LOG] (B2BRequestId,FormKind,FormNo,Company,ApproveId,Approvers,RequestType,RequestStatus,GfmFormNo,LogMsg,GfmEmpId,Entrance,CreateBy,CreateDate)
	--	VALUES(@requestId,@formKind, @formNo, @company,@approveId,@approversEmpNo,'CreateForm','SUCCESS',@gfmFormNo,N'需增加try、catch和trans机制，当有更新db有异常时需rollback已写入的数据，只记录该requestLog，并把Catch到的异常信息@logMsg写入到LogMsg栏位',@gfmEmpId,'PC','CreateForm',GETDATE());
	--END

END