USE [YL_MESDB];
GO

CREATE OR ALTER PROCEDURE [dbo].[设计报工_任务_开始]
    @DID int,
    @操作人ID nvarchar(100) = N'',
    @操作人 nvarchar(100) = N''
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT OFF;

    DECLARE @当前操作人ID nvarchar(100) = NULLIF(@操作人ID, N'');
    DECLARE @当前操作人 nvarchar(100) = NULLIF(@操作人, N'');
    DECLARE @RID int;
    DECLARE @初始事务数 int = @@TRANCOUNT;
    DECLARE @保存点名称 sysname = N'DRT_Task_Start';

    IF @当前操作人ID IS NULL AND @当前操作人 IS NULL
    BEGIN
        SELECT CAST(0 AS int) AS [result], N'未获取到当前操作人，不能开始' AS [message];
        RETURN;
    END

    BEGIN TRY
        IF @初始事务数 = 0 BEGIN TRANSACTION;
        ELSE SAVE TRANSACTION @保存点名称;

        IF NOT EXISTS (SELECT 1 FROM [dbo].[MES_设计报工_任务] WITH (UPDLOCK, HOLDLOCK) WHERE [DID] = @DID)
        BEGIN
            IF @初始事务数 = 0 ROLLBACK TRANSACTION;
            ELSE ROLLBACK TRANSACTION @保存点名称;
            SELECT CAST(0 AS int) AS [result], N'任务不存在' AS [message];
            RETURN;
        END

        IF EXISTS (SELECT 1 FROM [dbo].[MES_设计报工_任务] WHERE [DID] = @DID AND [单据状态] = N'已完成')
        BEGIN
            IF @初始事务数 = 0 ROLLBACK TRANSACTION;
            ELSE ROLLBACK TRANSACTION @保存点名称;
            SELECT CAST(0 AS int) AS [result], N'任务已完成，不能开始' AS [message];
            RETURN;
        END

        IF EXISTS
        (
            SELECT 1
            FROM [dbo].[MES_设计报工_记录] WITH (UPDLOCK, HOLDLOCK)
            WHERE [DID] = @DID
                AND [结束时间] IS NULL
                AND (
                    (@当前操作人ID IS NOT NULL AND ISNULL([操作人ID], N'') = @当前操作人ID)
                    OR (@当前操作人ID IS NULL AND @当前操作人 IS NOT NULL AND ISNULL([操作人], N'') = @当前操作人)
                )
        )
        BEGIN
            IF @初始事务数 = 0 ROLLBACK TRANSACTION;
            ELSE ROLLBACK TRANSACTION @保存点名称;
            SELECT CAST(0 AS int) AS [result], N'当前人员已存在未结束的报工记录' AS [message];
            RETURN;
        END

        INSERT INTO [dbo].[MES_设计报工_记录]([DID], [操作人ID], [操作人], [开始时间], [报工状态])
        VALUES(@DID, @当前操作人ID, @当前操作人, GETDATE(), N'执行中');

        SET @RID = CONVERT(int, SCOPE_IDENTITY());

        UPDATE [dbo].[MES_设计报工_任务]
        SET [单据状态] = N'执行中', [最后编辑人] = @当前操作人, [最后编辑时间] = GETDATE()
        WHERE [DID] = @DID;

        IF @初始事务数 = 0 COMMIT TRANSACTION;
        SELECT CAST(1 AS int) AS [result], N'开始成功' AS [message], @RID AS [RID];
    END TRY
    BEGIN CATCH
        IF XACT_STATE() <> 0
        BEGIN
            IF @初始事务数 = 0 ROLLBACK TRANSACTION;
            ELSE IF XACT_STATE() = 1 ROLLBACK TRANSACTION @保存点名称;
        END
        SELECT CAST(0 AS int) AS [result], ERROR_MESSAGE() AS [message];
    END CATCH
END
GO

CREATE OR ALTER PROCEDURE [dbo].[设计报工_任务_结束]
    @DID int,
    @操作人ID nvarchar(100) = N'',
    @操作人 nvarchar(100) = N''
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT OFF;

    DECLARE @当前操作人ID nvarchar(100) = NULLIF(@操作人ID, N'');
    DECLARE @当前操作人 nvarchar(100) = NULLIF(@操作人, N'');
    DECLARE @RID int;
    DECLARE @初始事务数 int = @@TRANCOUNT;
    DECLARE @保存点名称 sysname = N'DRT_Task_End';

    IF @当前操作人ID IS NULL AND @当前操作人 IS NULL
    BEGIN
        SELECT CAST(0 AS int) AS [result], N'未获取到当前操作人，不能结束' AS [message];
        RETURN;
    END

    BEGIN TRY
        IF @初始事务数 = 0 BEGIN TRANSACTION;
        ELSE SAVE TRANSACTION @保存点名称;

        IF NOT EXISTS (SELECT 1 FROM [dbo].[MES_设计报工_任务] WITH (UPDLOCK, HOLDLOCK) WHERE [DID] = @DID)
        BEGIN
            IF @初始事务数 = 0 ROLLBACK TRANSACTION;
            ELSE ROLLBACK TRANSACTION @保存点名称;
            SELECT CAST(0 AS int) AS [result], N'任务不存在' AS [message];
            RETURN;
        END

        SELECT TOP 1 @RID = [RID]
        FROM [dbo].[MES_设计报工_记录] WITH (UPDLOCK, HOLDLOCK)
        WHERE [DID] = @DID
            AND [结束时间] IS NULL
            AND (
                (@当前操作人ID IS NOT NULL AND ISNULL([操作人ID], N'') = @当前操作人ID)
                OR (@当前操作人ID IS NULL AND @当前操作人 IS NOT NULL AND ISNULL([操作人], N'') = @当前操作人)
            )
        ORDER BY [开始时间] DESC, [RID] DESC;

        IF @RID IS NULL
        BEGIN
            IF @初始事务数 = 0 ROLLBACK TRANSACTION;
            ELSE ROLLBACK TRANSACTION @保存点名称;
            SELECT CAST(0 AS int) AS [result], N'当前人员没有未结束的报工记录' AS [message];
            RETURN;
        END

        UPDATE [dbo].[MES_设计报工_记录]
        SET
            [结束时间] = GETDATE(),
            [工时秒数] = DATEDIFF(second, [开始时间], GETDATE()),
            [报工状态] = N'已结束'
        WHERE [RID] = @RID;

        UPDATE [dbo].[MES_设计报工_任务]
        SET
            [单据状态] = CASE
                WHEN [单据状态] = N'已完成' THEN N'已完成'
                WHEN EXISTS (SELECT 1 FROM [dbo].[MES_设计报工_记录] WHERE [DID] = @DID AND [结束时间] IS NULL) THEN N'执行中'
                ELSE N'已结束'
            END,
            [最后编辑人] = @当前操作人,
            [最后编辑时间] = GETDATE()
        WHERE [DID] = @DID;

        IF @初始事务数 = 0 COMMIT TRANSACTION;
        SELECT CAST(1 AS int) AS [result], N'结束成功' AS [message], @RID AS [RID];
    END TRY
    BEGIN CATCH
        IF XACT_STATE() <> 0
        BEGIN
            IF @初始事务数 = 0 ROLLBACK TRANSACTION;
            ELSE IF XACT_STATE() = 1 ROLLBACK TRANSACTION @保存点名称;
        END
        SELECT CAST(0 AS int) AS [result], ERROR_MESSAGE() AS [message];
    END CATCH
END
GO

CREATE OR ALTER PROCEDURE [dbo].[设计报工_任务_完成]
    @DID int,
    @操作人 nvarchar(100) = N''
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT OFF;

    DECLARE @未结束人员 nvarchar(max);
    DECLARE @初始事务数 int = @@TRANCOUNT;
    DECLARE @保存点名称 sysname = N'DRT_Task_Complete';

    BEGIN TRY
        IF @初始事务数 = 0 BEGIN TRANSACTION;
        ELSE SAVE TRANSACTION @保存点名称;

        IF NOT EXISTS (SELECT 1 FROM [dbo].[MES_设计报工_任务] WITH (UPDLOCK, HOLDLOCK) WHERE [DID] = @DID)
        BEGIN
            IF @初始事务数 = 0 ROLLBACK TRANSACTION;
            ELSE ROLLBACK TRANSACTION @保存点名称;
            SELECT CAST(0 AS int) AS [result], N'任务不存在' AS [message];
            RETURN;
        END

        SELECT @未结束人员 = STRING_AGG(CONVERT(nvarchar(max), x.[操作人标识]), N'、')
        FROM
        (
            SELECT DISTINCT COALESCE(NULLIF([操作人], N''), NULLIF([操作人ID], N''), N'未填写操作人') AS [操作人标识]
            FROM [dbo].[MES_设计报工_记录] WITH (UPDLOCK, HOLDLOCK)
            WHERE [DID] = @DID
                AND [结束时间] IS NULL
        ) AS x;

        IF NULLIF(@未结束人员, N'') IS NOT NULL
        BEGIN
            IF @初始事务数 = 0 ROLLBACK TRANSACTION;
            ELSE ROLLBACK TRANSACTION @保存点名称;
            SELECT CAST(0 AS int) AS [result], N'以下人员还未点击结束：' + @未结束人员 AS [message], @未结束人员 AS [未结束人员];
            RETURN;
        END

        IF EXISTS (SELECT 1 FROM [dbo].[MES_设计报工_任务] WHERE [DID] = @DID AND [单据状态] = N'已完成')
        BEGIN
            IF @初始事务数 = 0 ROLLBACK TRANSACTION;
            ELSE ROLLBACK TRANSACTION @保存点名称;
            SELECT CAST(0 AS int) AS [result], N'任务已完成' AS [message];
            RETURN;
        END

        IF NOT EXISTS (SELECT 1 FROM [dbo].[MES_设计报工_记录] WITH (HOLDLOCK) WHERE [DID] = @DID AND [结束时间] IS NOT NULL)
        BEGIN
            IF @初始事务数 = 0 ROLLBACK TRANSACTION;
            ELSE ROLLBACK TRANSACTION @保存点名称;
            SELECT CAST(0 AS int) AS [result], N'任务未产生已结束报工，不能完成' AS [message];
            RETURN;
        END

        UPDATE [dbo].[MES_设计报工_任务]
        SET [单据状态] = N'已完成', [最后编辑人] = NULLIF(@操作人, N''), [最后编辑时间] = GETDATE()
        WHERE [DID] = @DID;

        IF @初始事务数 = 0 COMMIT TRANSACTION;
        SELECT CAST(1 AS int) AS [result], N'完成成功' AS [message];
    END TRY
    BEGIN CATCH
        IF XACT_STATE() <> 0
        BEGIN
            IF @初始事务数 = 0 ROLLBACK TRANSACTION;
            ELSE IF XACT_STATE() = 1 ROLLBACK TRANSACTION @保存点名称;
        END
        SELECT CAST(0 AS int) AS [result], ERROR_MESSAGE() AS [message];
    END CATCH
END
GO
