USE [irepodb]
GO
/****** Object: StoredProcedure [dbo].[P_12#_BOLT_AB3] Script Date: 2025/8/29 17:08:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[P_12#_BOLT_AB3]
@def_top_org VARCHAR(10) -- 参数化默认值
AS
BEGIN
SET NOCOUNT ON;
SET XACT_ABORT ON; -- 错误时自动回滚事务
SELECT @def_top_org= DEF_TOP_ORG
FROM [irepodb].[dbo].[T_BOLT_LIMIT]
WHERE LINE ='12' and DEF_TOP_ORG IN ( '16992','27942','31581') group by DEF_TOP_ORG
BEGIN TRY
-- 1. 备份并清理旧数据
BEGIN TRANSACTION;
INSERT INTO T_BOLT_TREND_B
SELECT *
FROM T_BOLT_TREND
WHERE def_top_id = @def_top_org;
DELETE FROM T_BOLT_TREND
WHERE def_top_id = @def_top_org;
-- 2. 使用CTE准备数据
;WITH BoltData AS (
SELECT
b.displayValue,
b.celladdress,
b.rep_top_id,
b.def_top_id,
b.sheetNo,
b.def_top_org,
-- 解析行列号
col_num = dbo.getTransCOL(REPLACE(SUBSTRING(b.celladdress, 2, 2), '$', '')),
row_num = CONVERT(INT, REPLACE(RIGHT(RTRIM(b.celladdress), 2), '$', ''))
FROM dbo.T_BOLT b
WHERE b.def_top_org = @def_top_org
AND b.type IN ('InputNumeric', 'KeyboardText')
AND CONVERT(INT, REPLACE(RIGHT(RTRIM(b.celladdress), 2), '$', '')) >= 6
AND b.displayValue IS NOT NULL
AND (dbo.getTransCOL(REPLACE(SUBSTRING(b.celladdress, 2, 2), '$', '')) - 71) % 3 != 0
),
MetaData AS (
SELECT
rep_top_id,
sheetNo,
MAX(CASE WHEN celladdress = '$H$2:$I$2' THEN displayValue END) AS [Date],
MAX(CASE WHEN celladdress = '$H$3:$I$3' THEN displayValue END) AS Workpiece
FROM dbo.T_BOLT
WHERE def_top_org = @def_top_org
GROUP BY rep_top_id, sheetNo
)
-- 3. 批量插入趋势数据（含时间列修复）
INSERT INTO T_BOLT_TREND (
ID, Date, TIME, Workpiece, Differ, DisplayValue, Orders,
COL, Specs, updateTime, rep_top_id, def_top_id, sheetNo, Remarks
)
SELECT
NEWID(),
md.[Date],
COALESCE(
-- 精确时间定位
(
SELECT TOP 1 t.displayValue
FROM dbo.T_BOLT t
WHERE t.rep_top_id = bd.rep_top_id
AND t.sheetNo = bd.sheetNo
AND t.type = 'Time'
AND dbo.getTransCOL(REPLACE(SUBSTRING(t.celladdress, 2, 2), '$', '')) =
CASE
WHEN (bd.col_num - 71) % 3 = 1 THEN bd.col_num + 1
WHEN (bd.col_num - 71) % 3 = 2 THEN bd.col_num
END
AND CONVERT(INT, REPLACE(RIGHT(RTRIM(t.celladdress), 2), '$', '')) = 4
),
'' -- 最终默认值
) AS TIME,
md.Workpiece,
CASE
WHEN (bd.col_num - 71) % 3 = 1 THEN '最大值'
WHEN (bd.col_num - 71) % 3 = 2 THEN '最小值'
END AS Differ,
bd.displayValue,
CASE
WHEN (bd.col_num - 71) % 3 = 1 THEN (bd.col_num - 69) / 3
ELSE (bd.col_num - 70) / 3
END AS Orders,
REPLACE(bd.celladdress, ' ', ''),
COALESCE(
(
SELECT TOP 1 displayValue
FROM dbo.T_BOLT spec
WHERE spec.rep_top_id = bd.rep_top_id
AND spec.sheetNo = bd.sheetNo
AND spec.def_top_org = bd.def_top_org
AND dbo.getTransCOL(REPLACE(SUBSTRING(spec.celladdress, 2, 2), '$', '')) =
CASE
WHEN (bd.col_num - 71) % 3 = 1 THEN bd.col_num + 2
ELSE bd.col_num + 1
END
),
'N/A' -- 规格默认值
) AS Specs,
GETDATE(),
bd.rep_top_id,
bd.def_top_org,
bd.sheetNo,
'12'
FROM BoltData bd
JOIN MetaData md
ON bd.rep_top_id = md.rep_top_id
AND bd.sheetNo = md.sheetNo;
-- 4. 插入看板数据
INSERT INTO T_BOLT_KANBAN
SELECT
A.Specs, A.DATE, A.TIME, A.Workpiece, A.Differ, A.COL,
CONVERT(DECIMAL(12, 2), A.DisplayValue) AS DisplayValue,
A.Orders, A.rep_top_id, A.def_top_id, A.sheetNo,
B.PROJECTID, B.PROJECT, B.ROW, B.BOLT_NUM, B.LINE, B.TEAM,
B.TYPE, B.UP_LIMIT, B.DOWN_LIMIT, B.UP_ACTION, B.DOWN_ACTION,
GETDATE()
FROM dbo.T_BOLT_TREND A
LEFT JOIN dbo.T_BOLT_LIMIT B
ON A.def_top_id = B.DEF_TOP_ORG
AND B.ROW = REPLACE(RIGHT(RTRIM(A.COL), 2), '$', '')
WHERE A.def_top_id = @def_top_org;
COMMIT TRANSACTION;
-- 5. 数据质量验证
DECLARE @EmptyTimeCount INT;
SELECT @EmptyTimeCount = COUNT(*)
FROM T_BOLT_KANBAN
WHERE def_top_id = @def_top_org
AND TIME = '';
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
RAISERROR('存储过程执行错误.', 16, 1);
END CATCH
END
优化以上SQL，提升处理性能
最新发布
扫一扫
1137
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
