USE [SOI_ADD]
GO
/****** Object: StoredProcedure [dbo].[SP_BI_WSDealerPriceImport] Script Date: 2025/12/18 11:14:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[SP_BI_WSDealerPriceImport]
(
@userid int,
@TempTable NVARCHAR(100)
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @rows INT, @temp NVARCHAR(4000), @rows1 INT;
DECLARE @t TABLE (x INT PRIMARY KEY, y NVARCHAR(1000) COLLATE Chinese_PRC_CI_AS);
DECLARE @totalnum INT;
DECLARE @count INT;
CREATE TABLE #DataTemp
(
[PKID] int IDENTITY(1,1) NOT NULL,
[省份] nvarchar(255) COLLATE Chinese_PRC_CI_AS NULL,
[产品编码] nvarchar(255) COLLATE Chinese_PRC_CI_AS NULL,
[产品名称] nvarchar(255) COLLATE Chinese_PRC_CI_AS NULL,
[单价] nvarchar(255) COLLATE Chinese_PRC_CI_AS NULL,
[生效日期] nvarchar(255) COLLATE Chinese_PRC_CI_AS NULL,
[截止日期] nvarchar(255) COLLATE Chinese_PRC_CI_AS NULL,
[FK_Province] int,
[FK_Product] int
);
DECLARE @sql nvarchar(4000);
SET @sql = 'insert #DataTemp select ltrim(rtrim([省份])),ltrim(rtrim([产品编码])),ltrim(rtrim([产品名称])),[单价],[生效日期],[截止日期],0,0 from ' + @TempTable;
EXEC(@sql);
SET @temp = '';
-- 基本校验
SELECT @rows = COUNT(*) FROM #DataTemp;
IF (@rows = 0)
BEGIN
INSERT INTO @t VALUES (1, N'临时表中没有数据,请确认Excel文档格式正确!');
SELECT x,y FROM @t;
RETURN;
END
SELECT @rows = COUNT(*) FROM #DataTemp WHERE [省份] IS NULL OR [省份] = '';
IF (@rows > 0)
BEGIN
INSERT INTO @t VALUES (1, N'省份不能为空');
SELECT x,y FROM @t;
RETURN;
END
SELECT @rows = COUNT(*) FROM #DataTemp WHERE [产品编码] IS NULL OR [产品编码] = '';
IF (@rows > 0)
BEGIN
INSERT INTO @t VALUES (1, N'产品编码不能为空');
SELECT x,y FROM @t;
RETURN;
END
SELECT @rows = COUNT(*) FROM #DataTemp WHERE [单价] IS NULL OR [单价] = '';
IF (@rows > 0)
BEGIN
INSERT INTO @t VALUES (1, N'单价不能为空');
SELECT x,y FROM @t;
RETURN;
END
SELECT @rows = COUNT(*) FROM #DataTemp WHERE [生效日期] IS NULL OR [生效日期] = '';
IF (@rows > 0)
BEGIN
INSERT INTO @t VALUES (1, N'生效日期不能为空');
SELECT x,y FROM @t;
RETURN;
END
SELECT @rows = COUNT(*) FROM #DataTemp WHERE [截止日期] IS NULL OR [截止日期] = '';
IF (@rows > 0)
BEGIN
INSERT INTO @t VALUES (1, N'截止日期不能为空');
SELECT x,y FROM @t;
RETURN;
END
-- 产品映射
UPDATE #DataTemp SET FK_Product = (
SELECT TOP 1 PKID FROM UT_Product WHERE JDECode = #DataTemp.[产品编码] AND IsValid = 1 AND FK_Origin = 3
);
DECLARE @problemProductNum INT;
SELECT @problemProductNum = COUNT(*) FROM #DataTemp WHERE FK_Product IS NULL OR FK_Product = 0;
IF (@problemProductNum > 0)
BEGIN
INSERT INTO @t VALUES (1, N'产品在系统中不存在');
SELECT x,y FROM @t;
RETURN;
END
-- 单价 numeric 校验（保留原有 ISNUMERIC 校验）
DECLARE @problemPriceNum INT;
SELECT @problemPriceNum = COUNT(*) FROM #DataTemp WHERE ISNUMERIC([单价]) = 0;
IF (@problemPriceNum > 0)
BEGIN
INSERT INTO @t VALUES (1, N'单价必须为数字');
SELECT x,y FROM @t;
RETURN;
END
-- 日期格式校验 (使用 IsDate)
DECLARE @problemStartNum INT;
SELECT @problemStartNum = COUNT(*) FROM #DataTemp WHERE IsDate([生效日期]) = 0;
IF (@problemStartNum > 0)
BEGIN
INSERT INTO @t VALUES (1, N'生效日期不正确');
SELECT x,y FROM @t;
RETURN;
END
DECLARE @problemEndNum INT;
SELECT @problemEndNum = COUNT(*) FROM #DataTemp WHERE IsDate([截止日期]) = 0;
IF (@problemEndNum > 0)
BEGIN
INSERT INTO @t VALUES (1, N'截止日期不正确');
SELECT x,y FROM @t;
RETURN;
END
DECLARE @problemEffectNum INT;
SELECT @problemEffectNum = COUNT(*) FROM #DataTemp WHERE CAST([截止日期] AS date) <= CAST([生效日期] AS date);
IF (@problemEffectNum > 0)
BEGIN
INSERT INTO @t VALUES (1, N'生效日期必须小于截止日期');
SELECT x,y FROM @t;
RETURN;
END
-- 省份映射
UPDATE #DataTemp SET FK_Province = (
SELECT TOP(1) PKID FROM SOI_SmallRegion WHERE SmallRegionName = #DataTemp.[省份] AND IsValid = 1
);
-- 返回具体省份名
DECLARE @problemDealerNum INT;
SELECT @problemDealerNum = COUNT(*) FROM #DataTemp WHERE (FK_Province IS NULL OR FK_Province = 0);
IF (@problemDealerNum > 0)
BEGIN
DECLARE @badProvinces NVARCHAR(MAX);
SELECT @badProvinces = STUFF(
(
SELECT DISTINCT '，' + ISNULL([省份], N'（空）')
FROM #DataTemp
WHERE FK_Province IS NULL OR FK_Province = 0
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'');
INSERT INTO @t VALUES (1, N'以下省份在系统中不存在：' + @badProvinces);
SELECT x,y FROM @t;
RETURN;
END
-- 关键校验：若存在任意一条导入记录，其时间区间完全包含了系统中某条历史记录，则拒绝整个导入
IF EXISTS (
SELECT 1
FROM #DataTemp dt
JOIN BI_WSDealerPrice b ON b.IsValid = 1 AND b.[ProvinceName] = dt.省份 AND b.FK_Product = dt.FK_Product
WHERE b.StartTime >= CAST(dt.[生效日期] AS date)
AND b.EndTime <= CAST(dt.[截止日期] AS date)
AND NOT (b.StartTime = CAST(dt.[生效日期] AS date) AND b.EndTime = CAST(dt.[截止日期] AS date))
)
BEGIN
INSERT INTO @t VALUES (1, N'导入失败：存在历史记录完全包含在某条导入记录的时间区间内，系统不允许导入。请检查导入数据或历史数据。');
SELECT x,y FROM @t;
RETURN;
END
-- 开始事务并调整交叉的历史记录，然后 MERGE 导入
BEGIN TRY
BEGIN TRANSACTION;
-- 左侧相交：历史 b.Start < S.Start and b.End between S.Start and S.End => b.End = S.Start -1
UPDATE b
SET b.EndTime = DATEADD(day, -1, CAST(dt.[生效日期] AS date))
FROM BI_WSDealerPrice b
JOIN #DataTemp dt ON b.[ProvinceName] = dt.省份 AND b.FK_Product = dt.FK_Product
WHERE b.IsValid = 1
AND b.StartTime < CAST(dt.[生效日期] AS date)
AND b.EndTime >= CAST(dt.[生效日期] AS date)
AND b.EndTime <= CAST(dt.[截止日期] AS date);
-- 右侧相交：b.Start between S.Start and S.End and b.End > S.End => b.Start = S.End + 1
UPDATE b
SET b.StartTime = DATEADD(day, 1, CAST(dt.[截止日期] AS date))
FROM BI_WSDealerPrice b
JOIN #DataTemp dt ON b.[ProvinceName] = dt.省份 AND b.FK_Product = dt.FK_Product
WHERE b.IsValid = 1
AND b.StartTime >= CAST(dt.[生效日期] AS date)
AND b.StartTime <= CAST(dt.[截止日期] AS date)
AND b.EndTime > CAST(dt.[截止日期] AS date);
-- 跨越导入区间（拆分右段）
INSERT INTO BI_WSDealerPrice (
[WS_DealerType],[WS_DealerCode],[WS_DealerName],[FK_BPCSDealer],
[FK_SDDealer],[ProductCode],[ProductName],[FK_Product],
[Price],[StartTime],[EndTime],[CreateTime],[FK_CreateUser],[ModifyTime],
[FK_ModifyUser],[IsValid],[Memo],[ProvinceName]
)
SELECT
b.WS_DealerType, b.WS_DealerCode, b.WS_DealerName, b.FK_BPCSDealer,
b.FK_SDDealer, b.ProductCode, b.ProductName, b.FK_Product,
b.Price, DATEADD(day, 1, CAST(dt.[截止日期] AS date)) AS StartTime, b.EndTime,
GETDATE(), b.FK_CreateUser, GETDATE(), @userid, 1, b.Memo,b.[ProvinceName]
FROM BI_WSDealerPrice b
JOIN #DataTemp dt ON b.[ProvinceName] = dt.省份 AND b.FK_Product = dt.FK_Product
WHERE b.IsValid = 1
AND b.StartTime < CAST(dt.[生效日期] AS date)
AND b.EndTime > CAST(dt.[截止日期] AS date);
-- 左段更新 EndTime = S.Start -1
UPDATE b
SET b.EndTime = DATEADD(day, -1, CAST(dt.[生效日期] AS date))
FROM BI_WSDealerPrice b
JOIN #DataTemp dt ON b.[ProvinceName] = dt.省份 AND b.FK_Product = dt.FK_Product
WHERE b.IsValid = 1
AND b.StartTime < CAST(dt.[生效日期] AS date)
AND b.EndTime > CAST(dt.[截止日期] AS date);
-- MERGE 导入（明确列出 SOURCE 字段并使用 CAST 进行转换）
MERGE INTO BI_WSDealerPrice AS TARGET
USING (
SELECT
省份,
FK_Product,
[产品编码] AS ProductCode,
[产品名称] AS ProductName,
CAST([单价] AS decimal(18,4)) AS Price,
CAST([生效日期] AS date) AS StartTime,
CAST([截止日期] AS date) AS EndTime
FROM #DataTemp
) AS SOURCE
ON (TARGET.ProvinceName = SOURCE.省份
AND TARGET.FK_Product = SOURCE.FK_Product
AND TARGET.StartTime = SOURCE.StartTime
AND TARGET.EndTime = SOURCE.EndTime)
WHEN MATCHED THEN
UPDATE SET
TARGET.Price = SOURCE.Price,
TARGET.CreateTime = GETDATE(),
TARGET.FK_CreateUser = @userid,
TARGET.ModifyTime = GETDATE(),
TARGET.FK_ModifyUser = @userid,
TARGET.IsValid = 1,
TARGET.Memo = '导入'
WHEN NOT MATCHED BY TARGET THEN
INSERT (
[WS_DealerType],[WS_DealerCode],[WS_DealerName],[FK_BPCSDealer],
[FK_SDDealer],[ProductCode],[ProductName],[FK_Product],
[Price],[StartTime],[EndTime],[CreateTime],[FK_CreateUser],[ModifyTime],
[FK_ModifyUser],[IsValid],[Memo],[ProvinceName]
)
VALUES (
NULL, NULL, NULL, NULL,
NULL, SOURCE.ProductCode, SOURCE.ProductName, SOURCE.FK_Product,
SOURCE.Price, SOURCE.StartTime, SOURCE.EndTime, GETDATE(), @userid, GETDATE(), @userid, 1, '导入', SOURCE.省份
);
COMMIT TRANSACTION;
INSERT INTO @t VALUES (0, N'导入成功!');
SELECT x,y FROM @t;
RETURN;
END TRY
BEGIN CATCH
DECLARE @ErrNum INT = ERROR_NUMBER();
DECLARE @ErrSeverity INT = ERROR_SEVERITY();
DECLARE @ErrState INT = ERROR_STATE();
DECLARE @ErrProc NVARCHAR(128) = ISNULL(ERROR_PROCEDURE(), N'(NULL)');
DECLARE @ErrLine INT = ERROR_LINE();
DECLARE @ErrMsg NVARCHAR(4000) = ERROR_MESSAGE();
DECLARE @FullMsg NVARCHAR(1000) = N'导入失败，发生异常: 错误号=' + CAST(@ErrNum AS NVARCHAR(20))
+ N', 严重性=' + CAST(@ErrSeverity AS NVARCHAR(10))
+ N', 状态=' + CAST(@ErrState AS NVARCHAR(10))
+ N', 存储过程=' + @ErrProc
+ N', 行=' + CAST(@ErrLine AS NVARCHAR(10))
+ N', 消息=' + LEFT(@ErrMsg, 900); -- 截断以适应 NVARCHAR(1000)
IF XACT_STATE() <> 0
BEGIN
ROLLBACK TRANSACTION;
END
INSERT INTO @t VALUES (1, @FullMsg);
SELECT x,y FROM @t;
RETURN;
END CATCH
END
