351 lines
16 KiB
Transact-SQL
351 lines
16 KiB
Transact-SQL
USE [ERPTOOL_JY_20250826Back]
|
||
GO
|
||
|
||
SET ANSI_NULLS ON
|
||
GO
|
||
|
||
SET QUOTED_IDENTIFIER ON
|
||
GO
|
||
|
||
-- 补齐截图中的工价计算字段；修改人:Ld 修改时间:2026-08-04 09:27:01;
|
||
IF OBJECT_ID(N'[dbo].[车间生产管理工艺_工序工价]', N'U') IS NOT NULL AND COL_LENGTH(N'[dbo].[车间生产管理工艺_工序工价]', N'工作时间(时)') IS NULL
|
||
ALTER TABLE [dbo].[车间生产管理工艺_工序工价] ADD [工作时间(时)] DECIMAL(18, 4) NOT NULL CONSTRAINT [DF_车间生产管理工艺_工序工价_工作时间时] DEFAULT ((0));
|
||
GO
|
||
|
||
IF OBJECT_ID(N'[dbo].[车间生产管理工艺_工序工价]', N'U') IS NOT NULL AND COL_LENGTH(N'[dbo].[车间生产管理工艺_工序工价]', N'宽放时间(时)') IS NULL
|
||
ALTER TABLE [dbo].[车间生产管理工艺_工序工价] ADD [宽放时间(时)] DECIMAL(18, 4) NOT NULL CONSTRAINT [DF_车间生产管理工艺_工序工价_宽放时间时] DEFAULT ((0));
|
||
GO
|
||
|
||
IF OBJECT_ID(N'[dbo].[车间生产管理工艺_工序工价]', N'U') IS NOT NULL AND COL_LENGTH(N'[dbo].[车间生产管理工艺_工序工价]', N'日工资(元)') IS NULL
|
||
ALTER TABLE [dbo].[车间生产管理工艺_工序工价] ADD [日工资(元)] DECIMAL(18, 4) NOT NULL CONSTRAINT [DF_车间生产管理工艺_工序工价_日工资元] DEFAULT ((0));
|
||
GO
|
||
|
||
IF OBJECT_ID(N'[dbo].[车间生产管理工艺_工序工价]', N'U') IS NOT NULL AND COL_LENGTH(N'[dbo].[车间生产管理工艺_工序工价]', N'标准产能(件/天)') IS NULL
|
||
ALTER TABLE [dbo].[车间生产管理工艺_工序工价] ADD [标准产能(件/天)] DECIMAL(18, 4) NOT NULL CONSTRAINT [DF_车间生产管理工艺_工序工价_标准产能件天] DEFAULT ((0));
|
||
GO
|
||
|
||
IF OBJECT_ID(N'[dbo].[车间生产管理工艺_工序工价]', N'U') IS NOT NULL AND COL_LENGTH(N'[dbo].[车间生产管理工艺_工序工价]', N'工价定额') IS NULL
|
||
ALTER TABLE [dbo].[车间生产管理工艺_工序工价] ADD [工价定额] DECIMAL(18, 4) NOT NULL CONSTRAINT [DF_车间生产管理工艺_工序工价_工价定额] DEFAULT ((0));
|
||
GO
|
||
|
||
-- 保护旧数据：按已有日产能和最终单价反推新公式输入字段；修改人:Ld 修改时间:2026-08-04 09:27:01;
|
||
IF OBJECT_ID(N'[dbo].[车间生产管理工艺_工序工价]', N'U') IS NOT NULL
|
||
BEGIN
|
||
UPDATE price
|
||
SET [总时间s] = ISNULL([机动时间s], 0) + ISNULL([辅助时间s], 0),
|
||
[标准产能(件/天)] = ISNULL([日产能(件)], 0),
|
||
[工价定额] = ISNULL([最终单价(元/件)], 0),
|
||
[工作时间(时)] = CASE
|
||
WHEN ISNULL([工作时间(时)], 0) <> 0 THEN [工作时间(时)]
|
||
WHEN ISNULL([日产能(件)], 0) <> 0 AND ISNULL([机动时间s], 0) + ISNULL([辅助时间s], 0) <> 0
|
||
THEN ISNULL([日产能(件)], 0) * (ISNULL([机动时间s], 0) + ISNULL([辅助时间s], 0)) / 3600
|
||
ELSE 0
|
||
END,
|
||
[日工资(元)] = CASE
|
||
WHEN ISNULL([日工资(元)], 0) <> 0 THEN [日工资(元)]
|
||
WHEN ISNULL([最终单价(元/件)], 0) <> 0 AND ISNULL([日产能(件)], 0) <> 0
|
||
THEN ISNULL([最终单价(元/件)], 0) * ISNULL([日产能(件)], 0)
|
||
ELSE 0
|
||
END
|
||
FROM [dbo].[车间生产管理工艺_工序工价] price;
|
||
END
|
||
GO
|
||
|
||
-- 工序工价字段公式触发器：E=C+D，I=F*3600/E，J=H/I；修改人:Ld 修改时间:2026-08-04 09:27:01;
|
||
IF OBJECT_ID(N'[dbo].[TR_车间生产管理工艺_工序工价_字段公式计算]', N'TR') IS NOT NULL
|
||
DROP TRIGGER [dbo].[TR_车间生产管理工艺_工序工价_字段公式计算];
|
||
GO
|
||
|
||
CREATE TRIGGER [dbo].[TR_车间生产管理工艺_工序工价_字段公式计算]
|
||
ON [dbo].[车间生产管理工艺_工序工价]
|
||
AFTER INSERT, UPDATE
|
||
AS
|
||
BEGIN
|
||
SET NOCOUNT ON;
|
||
|
||
IF TRIGGER_NESTLEVEL() > 1
|
||
RETURN;
|
||
|
||
;WITH FormulaSource AS (
|
||
SELECT
|
||
price.工价流水号,
|
||
ISNULL(price.[机动时间s], 0) AS 机动时间s,
|
||
ISNULL(price.[辅助时间s], 0) AS 辅助时间s,
|
||
ISNULL(price.[机动时间s], 0) + ISNULL(price.[辅助时间s], 0) AS 总时间s,
|
||
-- 修改人:Ld 修改时间:2026-08-04 09:39:18; 前端编辑过程仍通过旧字段提交，优先按日产能反推工作时间。
|
||
CASE
|
||
WHEN ISNULL(price.[日产能(件)], 0) <> 0 AND ISNULL(price.[机动时间s], 0) + ISNULL(price.[辅助时间s], 0) <> 0
|
||
THEN ISNULL(price.[日产能(件)], 0) * (ISNULL(price.[机动时间s], 0) + ISNULL(price.[辅助时间s], 0)) / 3600
|
||
ELSE 0
|
||
END AS 工作时间时,
|
||
ISNULL(price.[宽放时间(时)], 0) AS 宽放时间时,
|
||
-- 修改人:Ld 修改时间:2026-08-04 09:39:18; 前端编辑过程仍通过旧字段提交，优先按工价定额和标准产能反推日工资。
|
||
CASE
|
||
WHEN ISNULL(price.[最终单价(元/件)], 0) <> 0 AND ISNULL(price.[日产能(件)], 0) <> 0
|
||
THEN ISNULL(price.[最终单价(元/件)], 0) * ISNULL(price.[日产能(件)], 0)
|
||
ELSE 0
|
||
END AS 日工资元
|
||
FROM [dbo].[车间生产管理工艺_工序工价] price
|
||
INNER JOIN inserted i ON price.工价流水号 = i.工价流水号
|
||
),
|
||
FormulaResult AS (
|
||
SELECT
|
||
工价流水号,
|
||
总时间s,
|
||
工作时间时,
|
||
宽放时间时,
|
||
日工资元,
|
||
CASE WHEN 总时间s <> 0 THEN 工作时间时 * 3600 / 总时间s ELSE 0 END AS 标准产能件天
|
||
FROM FormulaSource
|
||
)
|
||
UPDATE price
|
||
SET price.[总时间s] = calc.总时间s,
|
||
price.[工作时间(时)] = calc.工作时间时,
|
||
price.[宽放时间(时)] = calc.宽放时间时,
|
||
price.[日工资(元)] = calc.日工资元,
|
||
price.[标准产能(件/天)] = calc.标准产能件天,
|
||
price.[工价定额] = CASE WHEN calc.标准产能件天 <> 0 THEN calc.日工资元 / calc.标准产能件天 ELSE 0 END,
|
||
-- 修改人:Ld 修改时间:2026-08-04 09:27:01; 旧字段继续同步，兼容现有页面和历史接口。
|
||
price.[日产能(件)] = calc.标准产能件天,
|
||
price.[最终单价(元/件)] = CASE WHEN calc.标准产能件天 <> 0 THEN calc.日工资元 / calc.标准产能件天 ELSE 0 END,
|
||
price.单件工价 = CASE WHEN calc.标准产能件天 <> 0 THEN calc.日工资元 / calc.标准产能件天 ELSE 0 END
|
||
FROM [dbo].[车间生产管理工艺_工序工价] price
|
||
INNER JOIN FormulaResult calc ON price.工价流水号 = calc.工价流水号;
|
||
END
|
||
GO
|
||
|
||
-- 触发一次计算，确保测试库现有数据按公式同步；修改人:Ld 修改时间:2026-08-04 09:27:01;
|
||
IF OBJECT_ID(N'[dbo].[车间生产管理工艺_工序工价]', N'U') IS NOT NULL
|
||
BEGIN
|
||
UPDATE [dbo].[车间生产管理工艺_工序工价]
|
||
SET [机动时间s] = [机动时间s];
|
||
END
|
||
GO
|
||
|
||
-- 工序工价分页查询增加截图字段，并保留旧字段兼容前端；修改人:Ld 修改时间:2026-08-04 09:27:01;
|
||
ALTER PROCEDURE [dbo].[车间生产管理工艺_工序工价_查询数据]
|
||
@订单流水号_check INT = 0,
|
||
@订单流水号 INT = NULL,
|
||
@组件流水号_check INT = 0,
|
||
@组件流水号 INT = NULL,
|
||
@工艺计划流水号_check INT = 0,
|
||
@工艺计划流水号 INT = NULL,
|
||
@工艺名称_check INT = 0,
|
||
@工艺名称 NVARCHAR(100) = N'',
|
||
@工序名称_check INT = 0,
|
||
@工序名称 NVARCHAR(100) = N'',
|
||
@零件名称_check INT = 0,
|
||
@零件名称 NVARCHAR(100) = N'',
|
||
@图号_check INT = 0,
|
||
@图号 NVARCHAR(100) = N'',
|
||
@是否启用_check INT = 0,
|
||
@是否启用 NVARCHAR(20) = N'',
|
||
@PageCurrent INT = 1,
|
||
@PageSize INT = 1000
|
||
AS
|
||
BEGIN
|
||
SET NOCOUNT ON;
|
||
|
||
IF @PageCurrent IS NULL OR @PageCurrent < 1 SET @PageCurrent = 1;
|
||
IF @PageSize IS NULL OR @PageSize < 1 SET @PageSize = 1000;
|
||
|
||
;WITH PlanData AS (
|
||
-- 与制定工艺组件订单、产品、零件选择保持同一工艺计划来源口径；修改人:Ld 修改时间:2026-08-04 09:27:01;
|
||
SELECT gp.*
|
||
FROM [dbo].[车间生产管理_零件工艺计划_视图] gp
|
||
WHERE gp.考核状态 >= 1
|
||
AND (@订单流水号_check = 0 OR gp.订单流水号 = @订单流水号)
|
||
AND (@组件流水号_check = 0 OR gp.组件流水号 = @组件流水号)
|
||
AND (@工艺计划流水号_check = 0 OR gp.工艺计划流水号 = @工艺计划流水号)
|
||
AND (@零件名称_check = 0 OR gp.零件名称 + gp.零件图号 LIKE N'%' + @零件名称 + N'%')
|
||
AND (@图号_check = 0 OR gp.零件图号 LIKE N'%' + @图号 + N'%')
|
||
),
|
||
ProcessData AS (
|
||
SELECT
|
||
ROW_NUMBER() OVER (
|
||
ORDER BY gp.工艺更新时间 DESC, gp.工艺计划流水号 DESC, gx.工序顺序 ASC, gx.工序流水号 ASC
|
||
) AS RowNum,
|
||
price.工价流水号,
|
||
gx.工序流水号,
|
||
gx.工艺计划流水号,
|
||
gp.订单号,
|
||
gp.产品名称,
|
||
base.工艺名称 AS 工艺名称,
|
||
base.工艺名称 AS 工序名称,
|
||
gp.零件名称,
|
||
gp.零件图号 AS 图号,
|
||
gx.工序顺序,
|
||
gx.定额工时,
|
||
gx.准结工时,
|
||
ISNULL(price.[单装数量(件)], 0) AS [单装数量(件)],
|
||
ISNULL(price.[操作台数], 0) AS [操作台数],
|
||
ISNULL(price.[机动时间s], 0) AS [机动时间s],
|
||
ISNULL(price.[辅助时间s], 0) AS [辅助时间s],
|
||
ISNULL(price.[机动时间s], 0) + ISNULL(price.[辅助时间s], 0) AS [总时间s],
|
||
ISNULL(price.[工作时间(时)], 0) AS [工作时间(时)],
|
||
ISNULL(price.[宽放时间(时)], 0) AS [宽放时间(时)],
|
||
ISNULL(price.[日工资(元)], 0) AS [日工资(元)],
|
||
ISNULL(price.[标准产能(件/天)], 0) AS [标准产能(件/天)],
|
||
ISNULL(price.[工价定额], 0) AS 工价定额,
|
||
ISNULL(price.[标准产能(件/天)], ISNULL(price.[日产能(件)], 0)) AS [日产能(件)],
|
||
ISNULL(price.[工价定额], ISNULL(price.[最终单价(元/件)], 0)) AS [最终单价(元/件)],
|
||
CONVERT(VARCHAR(10), price.生效日期, 23) AS 生效日期,
|
||
ISNULL(price.变更次数, 0) AS 变更次数,
|
||
ISNULL(price.是否启用, 1) AS 是否启用,
|
||
price.备注,
|
||
price.创建人,
|
||
CONVERT(VARCHAR(19), price.创建时间, 120) AS 创建时间,
|
||
price.修改人,
|
||
CONVERT(VARCHAR(19), price.修改时间, 120) AS 修改时间
|
||
FROM PlanData gp
|
||
INNER JOIN [dbo].[车间生产管理工艺_零件工序] gx ON gp.工艺计划流水号 = gx.工艺计划流水号
|
||
LEFT JOIN [dbo].[车间生产管理工艺_基础表_工艺名称] base ON gx.工艺编号 = base.工艺编号
|
||
LEFT JOIN [dbo].[车间生产管理工艺_工序工价] price ON gx.工序流水号 = price.工序流水号
|
||
WHERE NULLIF(LTRIM(RTRIM(base.工艺名称)), N'') IS NOT NULL
|
||
AND (@工艺名称_check = 0 OR base.工艺名称 LIKE N'%' + @工艺名称 + N'%')
|
||
AND (@工序名称_check = 0 OR base.工艺名称 LIKE N'%' + @工序名称 + N'%')
|
||
AND (@是否启用_check = 0 OR CAST(ISNULL(price.是否启用, 1) AS NVARCHAR(20)) = @是否启用)
|
||
)
|
||
SELECT
|
||
工价流水号,
|
||
工序流水号,
|
||
工艺计划流水号,
|
||
订单号,
|
||
产品名称,
|
||
工艺名称,
|
||
工序名称,
|
||
零件名称,
|
||
图号,
|
||
工序顺序,
|
||
定额工时,
|
||
准结工时,
|
||
[单装数量(件)],
|
||
操作台数,
|
||
[机动时间s],
|
||
[辅助时间s],
|
||
[总时间s],
|
||
[工作时间(时)],
|
||
[宽放时间(时)],
|
||
[日工资(元)],
|
||
[标准产能(件/天)],
|
||
工价定额,
|
||
[日产能(件)],
|
||
[最终单价(元/件)],
|
||
生效日期,
|
||
变更次数,
|
||
是否启用,
|
||
备注,
|
||
创建人,
|
||
创建时间,
|
||
修改人,
|
||
修改时间
|
||
FROM ProcessData
|
||
WHERE RowNum BETWEEN ((@PageCurrent - 1) * @PageSize + 1) AND (@PageCurrent * @PageSize)
|
||
ORDER BY RowNum;
|
||
|
||
SELECT COUNT(1) AS total
|
||
FROM [dbo].[车间生产管理_零件工艺计划_视图] gp
|
||
INNER JOIN [dbo].[车间生产管理工艺_零件工序] gx ON gp.工艺计划流水号 = gx.工艺计划流水号
|
||
LEFT JOIN [dbo].[车间生产管理工艺_基础表_工艺名称] base ON gx.工艺编号 = base.工艺编号
|
||
LEFT JOIN [dbo].[车间生产管理工艺_工序工价] price ON gx.工序流水号 = price.工序流水号
|
||
WHERE gp.考核状态 >= 1
|
||
AND (@订单流水号_check = 0 OR gp.订单流水号 = @订单流水号)
|
||
AND (@组件流水号_check = 0 OR gp.组件流水号 = @组件流水号)
|
||
AND (@工艺计划流水号_check = 0 OR gp.工艺计划流水号 = @工艺计划流水号)
|
||
AND NULLIF(LTRIM(RTRIM(base.工艺名称)), N'') IS NOT NULL
|
||
AND (@工艺名称_check = 0 OR base.工艺名称 LIKE N'%' + @工艺名称 + N'%')
|
||
AND (@工序名称_check = 0 OR base.工艺名称 LIKE N'%' + @工序名称 + N'%')
|
||
AND (@零件名称_check = 0 OR gp.零件名称 + gp.零件图号 LIKE N'%' + @零件名称 + N'%')
|
||
AND (@图号_check = 0 OR gp.零件图号 LIKE N'%' + @图号 + N'%')
|
||
AND (@是否启用_check = 0 OR CAST(ISNULL(price.是否启用, 1) AS NVARCHAR(20)) = @是否启用);
|
||
END
|
||
GO
|
||
|
||
-- 工序工价导出增加截图字段；修改人:Ld 修改时间:2026-08-04 09:27:01;
|
||
ALTER PROCEDURE [dbo].[车间生产管理工艺_工序工价_导出表格_查询]
|
||
@订单流水号_check INT = 0,
|
||
@订单流水号 INT = NULL,
|
||
@组件流水号_check INT = 0,
|
||
@组件流水号 INT = NULL,
|
||
@工艺计划流水号_check INT = 0,
|
||
@工艺计划流水号 INT = NULL,
|
||
@工艺名称_check INT = 0,
|
||
@工艺名称 NVARCHAR(100) = N'',
|
||
@工序名称_check INT = 0,
|
||
@工序名称 NVARCHAR(100) = N'',
|
||
@零件名称_check INT = 0,
|
||
@零件名称 NVARCHAR(100) = N'',
|
||
@图号_check INT = 0,
|
||
@图号 NVARCHAR(100) = N'',
|
||
@是否启用_check INT = 0,
|
||
@是否启用 NVARCHAR(20) = N''
|
||
AS
|
||
BEGIN
|
||
SET NOCOUNT ON;
|
||
|
||
SELECT N'工序工价_' + REPLACE(REPLACE(REPLACE(CONVERT(NVARCHAR(19), GETDATE(), 21), ':', ''), '-', ''), ' ', '-') AS tablename, '15' AS tabletype;
|
||
|
||
SELECT
|
||
256 * 20 AS 工价名称,
|
||
256 * 28 AS 工序内容,
|
||
256 * 16 AS [单装数量(件)],
|
||
256 * 18 AS [操作台数(台/人)],
|
||
256 * 16 AS [机动时间(秒)],
|
||
256 * 16 AS [辅助时间(秒)],
|
||
256 * 14 AS [总时间(秒)],
|
||
256 * 16 AS [工作时间(时)],
|
||
256 * 16 AS [宽放时间(时)],
|
||
256 * 14 AS [日工资(元)],
|
||
256 * 20 AS [标准产能(件/天)],
|
||
256 * 14 AS 工价定额;
|
||
|
||
SELECT
|
||
0 AS 工价名称,
|
||
0 AS 工序内容,
|
||
1 AS [单装数量(件)],
|
||
1 AS [操作台数(台/人)],
|
||
1 AS [机动时间(秒)],
|
||
1 AS [辅助时间(秒)],
|
||
1 AS [总时间(秒)],
|
||
1 AS [工作时间(时)],
|
||
1 AS [宽放时间(时)],
|
||
1 AS [日工资(元)],
|
||
1 AS [标准产能(件/天)],
|
||
1 AS 工价定额;
|
||
|
||
;WITH PlanData AS (
|
||
-- 页面查询同源工艺计划数据源，确保导出与列表一致；修改人:Ld 修改时间:2026-08-04 09:27:01;
|
||
SELECT gp.*
|
||
FROM [dbo].[车间生产管理_零件工艺计划_视图] gp
|
||
WHERE gp.考核状态 >= 1
|
||
AND (@订单流水号_check = 0 OR gp.订单流水号 = @订单流水号)
|
||
AND (@组件流水号_check = 0 OR gp.组件流水号 = @组件流水号)
|
||
AND (@工艺计划流水号_check = 0 OR gp.工艺计划流水号 = @工艺计划流水号)
|
||
AND (@零件名称_check = 0 OR gp.零件名称 + gp.零件图号 LIKE N'%' + @零件名称 + N'%')
|
||
AND (@图号_check = 0 OR gp.零件图号 LIKE N'%' + @图号 + N'%')
|
||
)
|
||
SELECT
|
||
base.工艺名称 AS 工价名称,
|
||
gp.零件名称 + N' ' + gp.零件图号 AS 工序内容,
|
||
ISNULL(price.[单装数量(件)], 0) AS [单装数量(件)],
|
||
ISNULL(price.[操作台数], 0) AS [操作台数(台/人)],
|
||
ISNULL(price.[机动时间s], 0) AS [机动时间(秒)],
|
||
ISNULL(price.[辅助时间s], 0) AS [辅助时间(秒)],
|
||
ISNULL(price.[机动时间s], 0) + ISNULL(price.[辅助时间s], 0) AS [总时间(秒)],
|
||
ISNULL(price.[工作时间(时)], 0) AS [工作时间(时)],
|
||
ISNULL(price.[宽放时间(时)], 0) AS [宽放时间(时)],
|
||
ISNULL(price.[日工资(元)], 0) AS [日工资(元)],
|
||
ISNULL(price.[标准产能(件/天)], 0) AS [标准产能(件/天)],
|
||
ISNULL(price.[工价定额], 0) AS 工价定额
|
||
FROM PlanData gp
|
||
INNER JOIN [dbo].[车间生产管理工艺_零件工序] gx ON gp.工艺计划流水号 = gx.工艺计划流水号
|
||
LEFT JOIN [dbo].[车间生产管理工艺_基础表_工艺名称] base ON gx.工艺编号 = base.工艺编号
|
||
LEFT JOIN [dbo].[车间生产管理工艺_工序工价] price ON gx.工序流水号 = price.工序流水号
|
||
WHERE NULLIF(LTRIM(RTRIM(base.工艺名称)), N'') IS NOT NULL
|
||
AND (@工艺名称_check = 0 OR base.工艺名称 LIKE N'%' + @工艺名称 + N'%')
|
||
AND (@工序名称_check = 0 OR base.工艺名称 LIKE N'%' + @工序名称 + N'%')
|
||
AND (@是否启用_check = 0 OR CAST(ISNULL(price.是否启用, 1) AS NVARCHAR(20)) = @是否启用)
|
||
ORDER BY gp.工艺更新时间 DESC, gp.工艺计划流水号 DESC, gx.工序顺序 ASC, gx.工序流水号 ASC;
|
||
END
|
||
GO
|