平台商核算单价导入导入sql

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