355 lines
17 KiB
Transact-SQL
355 lines
17 KiB
Transact-SQL
USE [ERPTOOL_JY]
|
|
GO
|
|
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
-- 补齐正式库截图中的工价计算字段；修改人:Ld 修改时间:2026-08-04 09:49:39;
|
|
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:49:39;
|
|
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
|
|
|
|
-- 用完整字段公式触发器替代之前仅计算总时间的触发器；修改人:Ld 修改时间:2026-08-04 09:49:39;
|
|
IF OBJECT_ID(N'[dbo].[TR_车间生产管理工艺_工序工价_总时间自动计算]', N'TR') IS NOT NULL
|
|
DROP TRIGGER [dbo].[TR_车间生产管理工艺_工序工价_总时间自动计算];
|
|
GO
|
|
|
|
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:49:39; 前端编辑过程仍通过旧字段提交，优先按日产能反推工作时间。
|
|
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:49:39; 前端编辑过程仍通过旧字段提交，优先按工价定额和标准产能反推日工资。
|
|
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:49:39; 旧字段继续同步，兼容现有页面和历史接口。
|
|
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:49:39;
|
|
IF OBJECT_ID(N'[dbo].[车间生产管理工艺_工序工价]', N'U') IS NOT NULL
|
|
BEGIN
|
|
UPDATE [dbo].[车间生产管理工艺_工序工价]
|
|
SET [机动时间s] = [机动时间s];
|
|
END
|
|
GO
|
|
|
|
-- 工序工价分页查询增加截图字段，并保留旧字段兼容前端；修改人:Ld 修改时间:2026-08-04 09:49:39;
|
|
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:49:39;
|
|
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:49:39;
|
|
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:49:39;
|
|
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
|