USE [YL_MESDB];
GO

SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO

CREATE OR ALTER PROCEDURE [dbo].[设计报工_工时校验_保存]
    @RID int,
    @开始时间 nvarchar(30),
    @结束时间 nvarchar(30),
    @校验人ID nvarchar(100) = N'',
    @校验人 nvarchar(100) = N'',
    @校验备注 nvarchar(500) = N''
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @开始 datetime = TRY_CONVERT(datetime, NULLIF(@开始时间, N''));
    DECLARE @结束 datetime = TRY_CONVERT(datetime, NULLIF(@结束时间, N''));
    DECLARE @DID int;
    DECLARE @旧开始 datetime;
    DECLARE @旧结束 datetime;

    SELECT @DID = [DID], @旧开始 = [开始时间], @旧结束 = [结束时间]
    FROM [dbo].[MES_设计报工_记录]
    WHERE [RID] = @RID;

    IF @DID IS NULL
    BEGIN
        SELECT CAST(0 AS int) AS [result], N'报工记录不存在' AS [message];
        RETURN;
    END

    IF @开始 IS NULL OR @结束 IS NULL OR @结束 <= @开始
    BEGIN
        SELECT CAST(0 AS int) AS [result], N'开始时间和结束时间不合法' AS [message];
        RETURN;
    END

    INSERT INTO [dbo].[MES_设计报工_校验记录]
    (
        [RID], [DID], [校验前开始时间], [校验前结束时间],
        [校验后开始时间], [校验后结束时间], [校验后工时秒数],
        [校验人ID], [校验人], [校验时间], [校验备注]
    )
    VALUES
    (
        @RID, @DID, @旧开始, @旧结束,
        @开始, @结束, DATEDIFF(second, @开始, @结束),
        NULLIF(@校验人ID, N''), NULLIF(@校验人, N''), GETDATE(), NULLIF(@校验备注, N'')
    );

    UPDATE [dbo].[MES_设计报工_记录]
    SET
        [开始时间] = @开始,
        [结束时间] = @结束,
        [工时秒数] = DATEDIFF(second, @开始, @结束),
        [报工状态] = N'已结束',
        [已校验] = 1,
        [校验人ID] = NULLIF(@校验人ID, N''),
        [校验人] = NULLIF(@校验人, N''),
        [校验时间] = GETDATE(),
        [备注] = NULLIF(@校验备注, 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,
        [最后编辑人] = NULLIF(@校验人, N''),
        [最后编辑时间] = GETDATE()
    WHERE [DID] = @DID;

    SELECT
        CAST(1 AS int) AS [result],
        N'校验成功' AS [message],
        (SELECT [单据状态] FROM [dbo].[MES_设计报工_任务] WHERE [DID] = @DID) AS [任务状态];
END
GO

CREATE OR ALTER PROCEDURE [dbo].[设计报工_工时校验_批量保存]
    @RIDs nvarchar(max),
    @校验人ID nvarchar(100) = N'',
    @校验人 nvarchar(100) = N''
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @RID表 table([RID] int PRIMARY KEY);
    DECLARE @DID表 table([DID] int PRIMARY KEY);
    DECLARE @xml xml;
    DECLARE @更新记录数 int;

    SET @xml = TRY_CONVERT(xml, N'<r><i>' + REPLACE(ISNULL(@RIDs, N''), N',', N'</i><i>') + N'</i></r>');

    INSERT INTO @RID表([RID])
    SELECT DISTINCT TRY_CONVERT(int, T.c.value(N'.', N'nvarchar(30)'))
    FROM @xml.nodes(N'/r/i') AS T(c)
    WHERE TRY_CONVERT(int, T.c.value(N'.', N'nvarchar(30)')) IS NOT NULL;

    IF NOT EXISTS (SELECT 1 FROM @RID表)
    BEGIN
        SELECT CAST(0 AS int) AS [result], N'没有可校验记录' AS [message];
        RETURN;
    END

    INSERT INTO @DID表([DID])
    SELECT DISTINCT r.[DID]
    FROM [dbo].[MES_设计报工_记录] AS r
    INNER JOIN @RID表 AS ids ON r.[RID] = ids.[RID]
    WHERE r.[开始时间] IS NOT NULL AND r.[结束时间] IS NOT NULL;

    INSERT INTO [dbo].[MES_设计报工_校验记录]
    (
        [RID], [DID], [校验前开始时间], [校验前结束时间],
        [校验后开始时间], [校验后结束时间], [校验后工时秒数],
        [校验人ID], [校验人], [校验时间], [校验备注]
    )
    SELECT
        r.[RID], r.[DID], r.[开始时间], r.[结束时间],
        r.[开始时间], r.[结束时间], r.[工时秒数],
        NULLIF(@校验人ID, N''), NULLIF(@校验人, N''), GETDATE(), N'批量校验'
    FROM [dbo].[MES_设计报工_记录] AS r
    INNER JOIN @RID表 AS ids ON r.[RID] = ids.[RID]
    WHERE r.[开始时间] IS NOT NULL AND r.[结束时间] IS NOT NULL;

    UPDATE r
    SET
        [已校验] = 1,
        [校验人ID] = NULLIF(@校验人ID, N''),
        [校验人] = NULLIF(@校验人, N''),
        [校验时间] = GETDATE()
    FROM [dbo].[MES_设计报工_记录] AS r
    INNER JOIN @RID表 AS ids ON r.[RID] = ids.[RID]
    WHERE r.[开始时间] IS NOT NULL AND r.[结束时间] IS NOT NULL;

    SET @更新记录数 = @@ROWCOUNT;

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

    SELECT CAST(1 AS int) AS [result], N'批量校验成功' AS [message], @更新记录数 AS [count];
END
GO
