USE [YL_MESDB];
GO

IF COL_LENGTH(N'[dbo].[MES_设计报工_任务]', N'类别') IS NULL
BEGIN
    ALTER TABLE [dbo].[MES_设计报工_任务] ADD [类别] nvarchar(100) NULL;
END
GO

CREATE OR ALTER PROCEDURE [dbo].[设计报工_任务_查询]
    @DID int = NULL,
    @项目号 nvarchar(100) = N'',
    @物料编码 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.[研发目的],
        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 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].[设计报工_任务_新增]
    @项目号 nvarchar(100) = N'',
    @物料编码 nvarchar(100) = N'',
    @物料名称 nvarchar(200) = N'',
    @图号 nvarchar(100) = N'',
    @类别 nvarchar(100) = N'',
    @任务描述 nvarchar(500) = N'',
    @关联父件物料编码 nvarchar(100) = N'',
    @关联父件物料名称 nvarchar(200) = N'',
    @设计要求 nvarchar(max) = N'',
    @研发立项号 nvarchar(100) = N'',
    @研发目的 nvarchar(500) = N'',
    @指派对象ID nvarchar(1000) = N'',
    @指派对象 nvarchar(1000) = N'',
    @预计开始时间 nvarchar(30) = N'',
    @预计结束时间 nvarchar(30) = N'',
    @创建人 nvarchar(100) = N'',
    @备注 nvarchar(500) = N''
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO [dbo].[MES_设计报工_任务]
    (
        [项目号], [物料编码], [物料名称], [图号], [类别], [任务描述],
        [关联父件物料编码], [关联父件物料名称], [设计要求],
        [研发立项号], [研发目的], [指派对象ID], [指派对象],
        [预计开始时间], [预计结束时间], [单据状态],
        [创建人], [最后编辑人], [最后编辑时间], [备注]
    )
    VALUES
    (
        NULLIF(@项目号, N''), NULLIF(@物料编码, N''), NULLIF(@物料名称, N''), NULLIF(@图号, N''), NULLIF(@类别, N''), NULLIF(@任务描述, N''),
        NULLIF(@关联父件物料编码, N''), NULLIF(@关联父件物料名称, N''), NULLIF(@设计要求, N''),
        NULLIF(@研发立项号, N''), NULLIF(@研发目的, N''), NULLIF(@指派对象ID, N''), NULLIF(@指派对象, N''),
        TRY_CONVERT(datetime, NULLIF(@预计开始时间, N'')), TRY_CONVERT(datetime, NULLIF(@预计结束时间, N'')), N'待开始',
        NULLIF(@创建人, N''), NULLIF(@创建人, N''), GETDATE(), NULLIF(@备注, N'')
    );

    SELECT CAST(1 AS int) AS [result], N'新增成功' AS [message], SCOPE_IDENTITY() AS [DID];
END
GO

CREATE OR ALTER PROCEDURE [dbo].[设计报工_任务_编辑]
    @DID int,
    @项目号 nvarchar(100) = N'',
    @物料编码 nvarchar(100) = N'',
    @物料名称 nvarchar(200) = N'',
    @图号 nvarchar(100) = N'',
    @类别 nvarchar(100) = N'',
    @任务描述 nvarchar(500) = N'',
    @关联父件物料编码 nvarchar(100) = N'',
    @关联父件物料名称 nvarchar(200) = N'',
    @设计要求 nvarchar(max) = N'',
    @研发立项号 nvarchar(100) = N'',
    @研发目的 nvarchar(500) = N'',
    @指派对象ID nvarchar(1000) = N'',
    @指派对象 nvarchar(1000) = N'',
    @预计开始时间 nvarchar(30) = N'',
    @预计结束时间 nvarchar(30) = N'',
    @最后编辑人 nvarchar(100) = N'',
    @备注 nvarchar(500) = N''
AS
BEGIN
    SET NOCOUNT ON;

    IF NOT EXISTS (SELECT 1 FROM [dbo].[MES_设计报工_任务] WHERE [DID] = @DID)
    BEGIN
        SELECT CAST(0 AS int) AS [result], N'任务不存在' AS [message];
        RETURN;
    END

    UPDATE [dbo].[MES_设计报工_任务]
    SET
        [项目号] = NULLIF(@项目号, N''),
        [物料编码] = NULLIF(@物料编码, N''),
        [物料名称] = NULLIF(@物料名称, N''),
        [图号] = NULLIF(@图号, N''),
        [类别] = NULLIF(@类别, N''),
        [任务描述] = NULLIF(@任务描述, N''),
        [关联父件物料编码] = NULLIF(@关联父件物料编码, N''),
        [关联父件物料名称] = NULLIF(@关联父件物料名称, N''),
        [设计要求] = NULLIF(@设计要求, N''),
        [研发立项号] = NULLIF(@研发立项号, N''),
        [研发目的] = NULLIF(@研发目的, N''),
        [指派对象ID] = NULLIF(@指派对象ID, N''),
        [指派对象] = NULLIF(@指派对象, N''),
        [预计开始时间] = TRY_CONVERT(datetime, NULLIF(@预计开始时间, N'')),
        [预计结束时间] = TRY_CONVERT(datetime, NULLIF(@预计结束时间, N'')),
        [最后编辑人] = NULLIF(@最后编辑人, N''),
        [最后编辑时间] = GETDATE(),
        [备注] = NULLIF(@备注, N'')
    WHERE [DID] = @DID;

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

CREATE OR ALTER PROCEDURE [dbo].[设计报工_记录_查询]
    @DID int = NULL,
    @项目号 nvarchar(100) = N'',
    @物料编码 nvarchar(100) = N'',
    @类别 nvarchar(100) = N'',
    @操作人 nvarchar(100) = N'',
    @开始日期 nvarchar(30) = N'',
    @结束日期 nvarchar(30) = N'',
    @校验状态 nvarchar(50) = N''
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @开始 datetime = TRY_CONVERT(datetime, NULLIF(@开始日期, N''));
    DECLARE @结束 datetime = TRY_CONVERT(datetime, NULLIF(@结束日期, N''));

    SELECT TOP 1000
        r.[RID],
        r.[DID],
        t.[项目号],
        t.[物料编码],
        t.[物料名称],
        t.[图号],
        t.[类别],
        t.[任务描述],
        t.[指派对象],
        r.[操作人ID],
        r.[操作人],
        CONVERT(varchar(19), r.[开始时间], 120) AS [开始时间],
        CONVERT(varchar(19), r.[结束时间], 120) AS [结束时间],
        r.[工时秒数],
        CAST(CAST(ISNULL(r.[工时秒数], 0) / 3600.0 AS decimal(18, 2)) AS varchar(30)) AS [工时小时],
        r.[报工状态],
        r.[已校验],
        CASE WHEN r.[已校验] = 1 THEN N'已校验' ELSE N'未校验' END AS [校验状态],
        r.[校验人],
        CONVERT(varchar(19), r.[校验时间], 120) AS [校验时间],
        r.[备注],
        t.[单据状态]
    FROM [dbo].[MES_设计报工_记录] AS r
    INNER JOIN [dbo].[MES_设计报工_任务] AS t ON r.[DID] = t.[DID]
    WHERE
        (@DID IS NULL OR @DID = 0 OR r.[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 r.[操作人] LIKE N'%' + @操作人 + N'%')
        AND (@开始 IS NULL OR r.[开始时间] >= @开始)
        AND (@结束 IS NULL OR r.[开始时间] < DATEADD(day, 1, @结束))
        AND (
            ISNULL(@校验状态, N'') = N''
            OR @校验状态 = N'全部'
            OR (@校验状态 = N'已校验' AND r.[已校验] = 1)
            OR (@校验状态 = N'未校验' AND r.[已校验] = 0)
        )
    ORDER BY r.[RID] DESC;
END
GO

CREATE OR ALTER PROCEDURE [dbo].[设计报工_工时校验_查询]
    @DID int = NULL,
    @项目号 nvarchar(100) = N'',
    @物料编码 nvarchar(100) = N'',
    @类别 nvarchar(100) = N'',
    @操作人 nvarchar(100) = N'',
    @开始日期 nvarchar(30) = N'',
    @结束日期 nvarchar(30) = N'',
    @校验状态 nvarchar(50) = N'未校验'
AS
BEGIN
    SET NOCOUNT ON;

    EXEC [dbo].[设计报工_记录_查询]
        @DID = @DID,
        @项目号 = @项目号,
        @物料编码 = @物料编码,
        @类别 = @类别,
        @操作人 = @操作人,
        @开始日期 = @开始日期,
        @结束日期 = @结束日期,
        @校验状态 = @校验状态;
END
GO

CREATE OR ALTER PROCEDURE [dbo].[设计报工_工时报表_作业者工时_查询]
    @开始日期 nvarchar(30) = N'',
    @结束日期 nvarchar(30) = N'',
    @操作人 nvarchar(100) = N'',
    @项目号 nvarchar(100) = N'',
    @物料编码 nvarchar(100) = N'',
    @类别 nvarchar(100) = N'',
    @DID int = NULL
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @开始 datetime = TRY_CONVERT(datetime, NULLIF(@开始日期, N''));
    DECLARE @结束 datetime = TRY_CONVERT(datetime, NULLIF(@结束日期, N''));

    ;WITH base AS
    (
        SELECT
            r.[RID],
            r.[DID],
            ISNULL(r.[操作人], N'') AS [操作人],
            CONVERT(varchar(10), r.[开始时间], 120) AS [工作日期],
            t.[项目号],
            t.[物料编码],
            t.[物料名称],
            t.[图号],
            t.[类别],
            t.[任务描述],
            r.[开始时间],
            r.[结束时间],
            ISNULL(r.[工时秒数], 0) AS [工时秒数],
            r.[已校验],
            r.[备注]
        FROM [dbo].[MES_设计报工_记录] AS r
        INNER JOIN [dbo].[MES_设计报工_任务] AS t ON r.[DID] = t.[DID]
        WHERE
            r.[开始时间] IS NOT NULL
            AND r.[结束时间] IS NOT NULL
            AND (@开始 IS NULL OR r.[开始时间] >= @开始)
            AND (@结束 IS NULL OR r.[开始时间] < DATEADD(day, 1, @结束))
            AND (ISNULL(@操作人, N'') = N'' OR r.[操作人] 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 (@DID IS NULL OR @DID = 0 OR r.[DID] = @DID)
    ),
    result AS
    (
        SELECT
            1 AS [Level],
            N'p-' + [操作人] AS [id],
            CAST(N'' AS nvarchar(100)) AS [parentId],
            [操作人],
            CAST(N'' AS nvarchar(20)) AS [工作日期],
            CAST(NULL AS int) AS [RID],
            CAST(NULL AS int) AS [DID],
            CAST(N'' AS nvarchar(100)) AS [项目号],
            CAST(N'' AS nvarchar(100)) AS [物料编码],
            CAST(N'' AS nvarchar(200)) AS [物料名称],
            CAST(N'' AS nvarchar(100)) AS [图号],
            CAST(N'' AS nvarchar(100)) AS [类别],
            CAST(N'' AS nvarchar(500)) AS [任务描述],
            CAST(N'' AS varchar(19)) AS [开始时间],
            CAST(N'' AS varchar(19)) AS [结束时间],
            COUNT(1) AS [报工次数],
            CAST(SUM([工时秒数]) / 3600.0 AS decimal(18, 2)) AS [工时小时],
            CAST(N'' AS nvarchar(20)) AS [校验状态],
            CAST(N'' AS nvarchar(500)) AS [备注]
        FROM base
        GROUP BY [操作人]

        UNION ALL

        SELECT
            2 AS [Level],
            N'd-' + [操作人] + N'-' + [工作日期] AS [id],
            N'p-' + [操作人] AS [parentId],
            [操作人],
            [工作日期],
            CAST(NULL AS int) AS [RID],
            CAST(NULL AS int) AS [DID],
            CAST(N'' AS nvarchar(100)) AS [项目号],
            CAST(N'' AS nvarchar(100)) AS [物料编码],
            CAST(N'' AS nvarchar(200)) AS [物料名称],
            CAST(N'' AS nvarchar(100)) AS [图号],
            CAST(N'' AS nvarchar(100)) AS [类别],
            CAST(N'' AS nvarchar(500)) AS [任务描述],
            CAST(N'' AS varchar(19)) AS [开始时间],
            CAST(N'' AS varchar(19)) AS [结束时间],
            COUNT(1) AS [报工次数],
            CAST(SUM([工时秒数]) / 3600.0 AS decimal(18, 2)) AS [工时小时],
            CAST(N'' AS nvarchar(20)) AS [校验状态],
            CAST(N'' AS nvarchar(500)) AS [备注]
        FROM base
        GROUP BY [操作人], [工作日期]

        UNION ALL

        SELECT
            3 AS [Level],
            N'r-' + CAST([RID] AS nvarchar(20)) AS [id],
            N'd-' + [操作人] + N'-' + [工作日期] AS [parentId],
            [操作人],
            [工作日期],
            [RID],
            [DID],
            ISNULL([项目号], N'') AS [项目号],
            ISNULL([物料编码], N'') AS [物料编码],
            ISNULL([物料名称], N'') AS [物料名称],
            ISNULL([图号], N'') AS [图号],
            ISNULL([类别], N'') AS [类别],
            ISNULL([任务描述], N'') AS [任务描述],
            CONVERT(varchar(19), [开始时间], 120) AS [开始时间],
            CONVERT(varchar(19), [结束时间], 120) AS [结束时间],
            1 AS [报工次数],
            CAST([工时秒数] / 3600.0 AS decimal(18, 2)) AS [工时小时],
            CASE WHEN [已校验] = 1 THEN N'已校验' ELSE N'未校验' END AS [校验状态],
            ISNULL([备注], N'') AS [备注]
        FROM base
    )
    SELECT *
    FROM result
    ORDER BY [操作人], [工作日期], [Level], [RID];
END
GO
