USE [YL_MESDB];
GO

SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO

IF COL_LENGTH(N'dbo.MES_设计报工_记录', N'进度说明') IS NULL
BEGIN
    ALTER TABLE [dbo].[MES_设计报工_记录] ADD [进度说明] nvarchar(500) NULL;
END
GO

CREATE OR ALTER PROCEDURE [dbo].[设计报工_任务_结束]
    @DID int,
    @操作人ID nvarchar(100) = N'',
    @操作人 nvarchar(100) = N'',
    @进度说明 nvarchar(500) = 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'已结束',
            [进度说明] = 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,
            [最后编辑人] = @当前操作人,
            [最后编辑时间] = 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 = 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.[进度说明],
        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].[设计报工_工时报表_作业者工时_查询]
    @开始日期 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.[进度说明],
            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 [进度说明],
            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 [进度说明],
            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 [进度说明],
            ISNULL([备注], N'') AS [备注]
        FROM base
    )
    SELECT *
    FROM result
    ORDER BY [操作人], [工作日期], [Level], [RID];
END
GO
