FT542-WMS/sql/bas_stock_mapping_fixed.sql

/*
 * 脚本说明：
 * 1. 创建新表 bas_stock_product_inventory (产品维度汇总) 和 bas_stock_inventory_detail (明细维度，解析 JSON 或映射原表)。
 * 2. 创建视图 v_bas_stock_mapping，将 bas_stock 原表字段映射到新业务语义。
 * 3. 提供 ETL 存储过程，用于从 bas_stock 同步数据到新表（示例逻辑）。
 * 
 * 修改说明：
 * - 修正 model 映射为 product_model (原逻辑误映射为 unit_name)
 * - 修正 unit_name 映射为 bas_product.unit (原逻辑误映射为 model)
 */

-- =============================================
-- 1. 创建新表：库存明细表 (对应接口 rows / cklist 解析后的结构)
-- =============================================
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[bas_stock_inventory_detail]') AND type in (N'U'))
    DROP TABLE [dbo].[bas_stock_inventory_detail];
GO

CREATE TABLE [dbo].[bas_stock_inventory_detail](
    [id] [bigint] IDENTITY(1,1) NOT NULL,
    [stock_id] [varchar](32) NULL,          -- 关联原表 bas_stock.stock_id
    [product_code] [varchar](255) NULL,     -- 对应 bas_stock.product_id / 接口 order1
    [product_name] [varchar](255) NULL,     -- 对应 bas_stock.product_name / 接口 title
    [product_model] [varchar](255) NULL,    -- 对应 bas_stock.model / 接口 type1
    [warehouse_name] [varchar](200) NULL,   -- 需关联 bas_warehouse 或从 JSON 解析
    [location_name] [varchar](200) NULL,    -- 需关联 bas_cargo_space 或从 JSON 解析
    [unit_name] [varchar](50) NULL,         -- 接口 unitname
    [quantity] [numeric](18, 3) DEFAULT 0,  -- 对应 bas_stock.quantity
    [ratio] [numeric](18, 6) DEFAULT 1.0,   -- 换算比例
    [detail_json] [nvarchar](max) NULL,     -- 原始 JSON 数据 (cklist)
    [sync_time] [datetime2](7) DEFAULT SYSUTCDATETIME(),
    CONSTRAINT [PK_bas_stock_inventory_detail] PRIMARY KEY CLUSTERED ([id] ASC)
);
GO

-- =============================================
-- 2. 创建新表：产品库存汇总表 (对应接口外层列表)
-- =============================================
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[bas_stock_product_inventory]') AND type in (N'U'))
    DROP TABLE [dbo].[bas_stock_product_inventory];
GO

CREATE TABLE [dbo].[bas_stock_product_inventory](
    [id] [bigint] IDENTITY(1,1) NOT NULL,
    [product_ord] [int] NULL,               -- 接口 ord
    [product_code] [varchar](255) NULL,     -- product_id
    [product_name] [varchar](255) NULL,     -- product_name
    [product_model] [varchar](255) NULL,    -- model
    [total_quantity] [numeric](18, 3) DEFAULT 0,
    [unit_name] [varchar](50) NULL,
    [warehouse_stock_json] [nvarchar](max) NULL, -- 存储 cklist JSON 字符串
    [updated_time] [datetime2](7) DEFAULT SYSUTCDATETIME(),
    CONSTRAINT [PK_bas_stock_product_inventory] PRIMARY KEY CLUSTERED ([id] ASC)
);
GO

-- =============================================
-- 3. 创建视图：映射 bas_stock 到新语义 (含关联)
-- =============================================
IF OBJECT_ID('dbo.v_bas_stock_mapping', 'V') IS NOT NULL
    DROP VIEW dbo.v_bas_stock_mapping;
GO

CREATE VIEW [dbo].[v_bas_stock_mapping] AS
SELECT
    s.stock_id,
    s.product_id AS product_code,
    s.product_name,
    -- 修正：unit_name 取自 bas_product.unit
    ISNULL(p.unit, '个') AS unit_name,
    -- 修正：model 映射为 product_model
    ISNULL(s.model, '') AS product_model, 
    s.quantity,
    s.warehouse_id,
    ISNULL(w.warehouse_name, '未知仓库') AS warehouse_name,
    s.cargo_space_id,
    ISNULL(c.cargo_space_name, '未知库位') AS location_name,
    s.lot_no,
    s.in_date
FROM dbo.bas_stock s
LEFT JOIN dbo.bas_warehouse w ON s.warehouse_id = w.warehouse_id
LEFT JOIN dbo.bas_cargo_space c ON s.cargo_space_id = c.cargo_space_id
LEFT JOIN dbo.bas_product p ON s.product_id = p.product_id;
GO

-- =============================================
-- 4. ETL 脚本：从 bas_stock 全量刷新到明细表
-- =============================================
-- 清空明细表
TRUNCATE TABLE dbo.bas_stock_inventory_detail;

-- 插入数据
INSERT INTO dbo.bas_stock_inventory_detail
(stock_id, product_code, product_name, product_model, unit_name, quantity, warehouse_name, location_name)
SELECT
    stock_id,
    product_code,
    product_name,
    product_model,
    unit_name,
    quantity,
    warehouse_name,
    location_name
FROM dbo.v_bas_stock_mapping;

-- =============================================
-- 5. ETL 脚本：生成汇总表数据 (简单聚合示例)
-- =============================================
TRUNCATE TABLE dbo.bas_stock_product_inventory;

INSERT INTO dbo.bas_stock_product_inventory
(product_code, product_name, product_model, unit_name, total_quantity)
SELECT 
    product_code,
    MAX(product_name),
    MAX(product_model),
    MAX(unit_name),
    SUM(quantity)
FROM dbo.bas_stock_inventory_detail
GROUP BY product_code;