USE [YL_MESDB];
GO

CREATE OR ALTER PROCEDURE [dbo].[设计报工_任务_查询]
    @DID int = NULL,
    @项目号 nvarchar(100) = N'',
    @物料编码 nvarchar(100) = N'',
    @图号 nvarchar(100) = N'',
    @关联父件物料编码 nvarchar(100) = N'',
    @预计开始时间_Start nvarchar(30) = N'',
    @预计开始时间_End nvarchar(30) = N'',
    @预计结束时间_Start nvarchar(30) = N'',
    @预计结束时间_End nvarchar(30) = N'',
    @单据状态 nvarchar(50) = N'',
    @当前操作人ID nvarchar(100) = N'',
    @当前操作人 nvarchar(100) = N''
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @预计开始日期1 datetime = TRY_CONVERT(datetime, NULLIF(@预计开始时间_Start, N''));
    DECLARE @预计开始日期2 datetime = TRY_CONVERT(datetime, NULLIF(@预计开始时间_End, N''));
    DECLARE @预计结束日期1 datetime = TRY_CONVERT(datetime, NULLIF(@预计结束时间_Start, N''));
    DECLARE @预计结束日期2 datetime = TRY_CONVERT(datetime, NULLIF(@预计结束时间_End, N''));

    SELECT TOP 1000
        t.[DID],
        t.[项目号],
        t.[物料编码],
        t.[物料名称],
        t.[图号],
        t.[任务描述],
        t.[关联父件物料编码],
        t.[关联父件物料名称],
        t.[设计要求],
        t.[研发立项号],
        t.[研发目的],
        t.[指派对象ID],
        t.[指派对象],
        CONVERT(varchar(19), t.[预计开始时间], 120) AS [预计开始时间],
        CONVERT(varchar(19), t.[预计结束时间], 120) AS [预计结束时间],
        t.[单据状态],
        CONVERT(varchar(19), t.[创建日期], 120) AS [创建日期],
        t.[创建人],
        t.[最后编辑人],
        CONVERT(varchar(19), t.[最后编辑时间], 120) AS [最后编辑时间],
        t.[备注],
        ISNULL(r.[报工次数], 0) AS [报工次数],
        ISNULL(r.[未结束报工数], 0) AS [未结束报工数],
        ISNULL(r.[当前用户未结束报工数], 0) AS [当前用户未结束报工数],
        ISNULL(p.[未结束报工人员], N'') AS [未结束报工人员],
        CONVERT(varchar(19), r.[最后开始时间], 120) AS [最后开始时间],
        CONVERT(varchar(19), r.[最后结束时间], 120) AS [最后结束时间]
    FROM [dbo].[MES_设计报工_任务] AS t
    OUTER APPLY
    (
        SELECT
            COUNT(1) AS [报工次数],
            SUM(CASE WHEN rr.[结束时间] IS NULL THEN 1 ELSE 0 END) AS [未结束报工数],
            SUM(CASE
                WHEN rr.[结束时间] IS NULL
                    AND (
                        (NULLIF(@当前操作人ID, N'') IS NOT NULL AND ISNULL(rr.[操作人ID], N'') = @当前操作人ID)
                        OR (NULLIF(@当前操作人ID, N'') IS NULL AND NULLIF(@当前操作人, N'') IS NOT NULL AND ISNULL(rr.[操作人], N'') = @当前操作人)
                    )
                THEN 1 ELSE 0 END) AS [当前用户未结束报工数],
            MAX(rr.[开始时间]) AS [最后开始时间],
            MAX(rr.[结束时间]) AS [最后结束时间]
        FROM [dbo].[MES_设计报工_记录] AS rr
        WHERE rr.[DID] = t.[DID]
    ) AS r
    OUTER APPLY
    (
        SELECT STRING_AGG(CONVERT(nvarchar(max), x.[操作人标识]), N'、') AS [未结束报工人员]
        FROM
        (
            SELECT DISTINCT COALESCE(NULLIF(rr2.[操作人], N''), NULLIF(rr2.[操作人ID], N''), N'未填写操作人') AS [操作人标识]
            FROM [dbo].[MES_设计报工_记录] AS rr2
            WHERE rr2.[DID] = t.[DID]
                AND rr2.[结束时间] IS NULL
        ) AS x
    ) AS p
    WHERE
        (@DID IS NULL OR @DID = 0 OR t.[DID] = @DID)
        AND (ISNULL(@项目号, N'') = N'' OR t.[项目号] LIKE N'%' + @项目号 + N'%')
        AND (ISNULL(@物料编码, N'') = N'' OR t.[物料编码] LIKE N'%' + @物料编码 + N'%')
        AND (ISNULL(@图号, N'') = N'' OR t.[图号] LIKE N'%' + @图号 + N'%')
        AND (ISNULL(@关联父件物料编码, N'') = N'' OR t.[关联父件物料编码] LIKE N'%' + @关联父件物料编码 + N'%')
        AND (ISNULL(@单据状态, N'') = N'' OR @单据状态 = N'全部' OR t.[单据状态] = @单据状态)
        AND (@预计开始日期1 IS NULL OR t.[预计开始时间] >= @预计开始日期1)
        AND (@预计开始日期2 IS NULL OR t.[预计开始时间] < DATEADD(day, 1, @预计开始日期2))
        AND (@预计结束日期1 IS NULL OR t.[预计结束时间] >= @预计结束日期1)
        AND (@预计结束日期2 IS NULL OR t.[预计结束时间] < DATEADD(day, 1, @预计结束日期2))
    ORDER BY t.[DID] DESC;
END
GO

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

    DECLARE @当前操作人ID nvarchar(100) = NULLIF(@操作人ID, N'');
    DECLARE @当前操作人 nvarchar(100) = NULLIF(@操作人, N'');
    DECLARE @RID int;

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

    IF NOT EXISTS (SELECT 1 FROM [dbo].[MES_设计报工_任务] WHERE [DID] = @DID)
    BEGIN
        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
        SELECT CAST(0 AS int) AS [result], N'任务已完成，不能开始' AS [message];
        RETURN;
    END

    IF EXISTS
    (
        SELECT 1
        FROM [dbo].[MES_设计报工_记录]
        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
        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;

    SELECT CAST(1 AS int) AS [result], N'开始成功' AS [message], @RID AS [RID];
END
GO

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

    DECLARE @当前操作人ID nvarchar(100) = NULLIF(@操作人ID, N'');
    DECLARE @当前操作人 nvarchar(100) = NULLIF(@操作人, N'');
    DECLARE @RID int;

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

    IF NOT EXISTS (SELECT 1 FROM [dbo].[MES_设计报工_任务] WHERE [DID] = @DID)
    BEGIN
        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
        SELECT CAST(0 AS int) AS [result], N'任务已完成，不能结束' AS [message];
        RETURN;
    END

    SELECT TOP 1 @RID = [RID]
    FROM [dbo].[MES_设计报工_记录]
    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
        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 EXISTS (SELECT 1 FROM [dbo].[MES_设计报工_记录] WHERE [DID] = @DID AND [结束时间] IS NULL)
            THEN N'执行中'
            ELSE N'已结束'
        END,
        [最后编辑人] = @当前操作人,
        [最后编辑时间] = GETDATE()
    WHERE [DID] = @DID AND [单据状态] <> N'已完成';

    SELECT CAST(1 AS int) AS [result], N'结束成功' AS [message], @RID AS [RID];
END
GO

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

    DECLARE @未结束人员 nvarchar(max);

    IF NOT EXISTS (SELECT 1 FROM [dbo].[MES_设计报工_任务] WHERE [DID] = @DID)
    BEGIN
        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_设计报工_记录]
        WHERE [DID] = @DID
            AND [结束时间] IS NULL
    ) AS x;

    IF NULLIF(@未结束人员, N'') IS NOT NULL
    BEGIN
        SELECT CAST(0 AS int) AS [result], N'以下人员还未点击结束：' + @未结束人员 AS [message], @未结束人员 AS [未结束人员];
        RETURN;
    END

    IF NOT EXISTS (SELECT 1 FROM [dbo].[MES_设计报工_记录] WHERE [DID] = @DID AND [结束时间] IS NOT NULL)
    BEGIN
        SELECT CAST(0 AS int) AS [result], N'任务未产生已结束报工，不能完成' AS [message];
        RETURN;
    END

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

    SELECT CAST(1 AS int) AS [result], N'完成成功' AS [message];
END
GO
