102 lines
3.5 KiB
Transact-SQL
102 lines
3.5 KiB
Transact-SQL
-- 修复员工工时统计导出 null：统一导出元数据列名，并修正导出文件名前缀；修改人:Ld 修改时间:2026-07-17 07:53:49;
|
||
ALTER PROCEDURE [dbo].[报表_精工车间_员工工时统计_查询数据]
|
||
@开始时间 DATETIME = NULL,
|
||
@结束时间 DATETIME = NULL,
|
||
@排序名称 INT = NULL,
|
||
@排序方式 INT = NULL
|
||
AS
|
||
BEGIN
|
||
SET NOCOUNT ON;
|
||
|
||
-- 后端导出处理器未支持 tabletype=18，使用通用导出类型 15 防止返回 NULL；修改人:Ld 修改时间:2026-07-17 07:58:30;
|
||
SELECT N'员工工时统计_' + REPLACE(REPLACE(REPLACE(CONVERT(NVARCHAR(19), GETDATE(), 21), ':', ''), '-', ''), ' ', '-') AS tablename, '15' AS tabletype;
|
||
|
||
SELECT
|
||
256 * 15 AS 工序,
|
||
256 * 10 AS 工序顺序,
|
||
256 * 30 AS 零件名称,
|
||
256 * 30 AS 零件图号,
|
||
256 * 20 AS 物料编码,
|
||
256 * 10 AS 加工数,
|
||
256 * 12 AS 计价方式,
|
||
256 * 10 AS 单价,
|
||
256 * 12 AS 总价,
|
||
256 * 15 AS 加工工时M,
|
||
256 * 10 AS 暂停时长,
|
||
256 * 30 AS 开始加工时间,
|
||
256 * 30 AS 完成时间,
|
||
256 * 10 AS 操作者,
|
||
256 * 25 AS 设备,
|
||
246 * 25 AS 订单编号,
|
||
246 * 25 AS 产品名称;
|
||
|
||
SELECT
|
||
0 AS 工序,
|
||
1 AS 工序顺序,
|
||
0 AS 零件名称,
|
||
0 AS 零件图号,
|
||
0 AS 物料编码,
|
||
1 AS 加工数,
|
||
0 AS 计价方式,
|
||
1 AS 单价,
|
||
1 AS 总价,
|
||
1 AS 加工工时M,
|
||
1 AS 暂停时长,
|
||
0 AS 开始加工时间,
|
||
0 AS 完成时间,
|
||
0 AS 操作者,
|
||
0 AS 设备,
|
||
0 AS 订单编号,
|
||
0 AS 产品名称;
|
||
|
||
;WITH PriceData AS (
|
||
SELECT
|
||
data.*,
|
||
CASE WHEN data.计价方式 = N'计时' THEN ISNULL(data.单时工价, 0) ELSE CASE WHEN ISNULL(data.单件工价, 0) <> 0 THEN data.单件工价 ELSE ISNULL(priceData.单件工价, 0) END END AS 单价,
|
||
CASE
|
||
WHEN data.计价方式 = N'计时' THEN ROUND(ISNULL(data.加工时长, 0) / 60.0 * ISNULL(data.单时工价, 0), 4)
|
||
ELSE ROUND(ISNULL(data.完成数, 0) * CASE WHEN ISNULL(data.单件工价, 0) <> 0 THEN data.单件工价 ELSE ISNULL(priceData.单件工价, 0) END, 4)
|
||
END AS 总价
|
||
FROM dbo.车间生产管理_零件工序加工时长_视图 data
|
||
OUTER APPLY (
|
||
SELECT TOP 1
|
||
CASE
|
||
WHEN ISNULL(price.[单件工价], 0) <> 0 THEN price.[单件工价]
|
||
ELSE ISNULL(price.[最终单价(元/件)], 0)
|
||
END AS 单件工价
|
||
FROM dbo.车间生产管理工艺_工序工价 price
|
||
WHERE price.零件名称 = data.零件名称
|
||
AND price.图号 = data.图号
|
||
AND price.工序名称 = data.工序
|
||
AND ISNULL(price.是否启用, 1) = 1
|
||
ORDER BY ISNULL(price.生效日期, CONVERT(DATE, '1900-01-01')) DESC, price.修改时间 DESC, price.工价流水号 DESC
|
||
) priceData
|
||
WHERE data.结束加工 >= @开始时间
|
||
AND data.结束加工 <= @结束时间
|
||
)
|
||
SELECT
|
||
工序,
|
||
工序顺序,
|
||
零件名称,
|
||
图号 AS 零件图号,
|
||
物料编码,
|
||
完成数 AS 加工数,
|
||
计价方式,
|
||
单价,
|
||
总价,
|
||
加工时长 AS 加工工时M,
|
||
暂停时间 AS 暂停时长,
|
||
开始加工 AS 开始加工时间,
|
||
结束加工 AS 完成时间,
|
||
操作者,
|
||
工位号 AS 设备,
|
||
订单编号,
|
||
产品名称
|
||
FROM PriceData
|
||
ORDER BY
|
||
CASE WHEN @排序名称 = 1 AND @排序方式 = 1 THEN 结束加工 END DESC,
|
||
CASE WHEN @排序名称 = 1 AND @排序方式 <> 1 THEN 结束加工 END ASC,
|
||
CASE WHEN ISNULL(@排序名称, 0) <> 1 THEN 结束加工 END DESC;
|
||
END
|
||
GO
|