AiDOPWarehouse/server/Admin.NET.Web.Entry/UpdateScripts/1.0.158.sql

-- S4-MDP-PROCUREMENT-EXECUTION-1
-- S4 采购执行专属贴源/标准/DWD 与 mdp_entity 登记（幂等）
-- 采购订单主链仍由 S3 维护；S4 仅补 IQC/发货执行/退货/欠料执行事实。

SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS mdp_stg_s4_iqc (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT NOT NULL DEFAULT 0,
    source_system VARCHAR(50) NOT NULL DEFAULT 'AIDOP',
    source_table VARCHAR(100) NOT NULL,
    source_row_id VARCHAR(100) NOT NULL,
    source_biz_key VARCHAR(200) NULL,
    sync_batch_id VARCHAR(100) NOT NULL,
    sync_time DATETIME NOT NULL,
    process_status VARCHAR(20) NOT NULL DEFAULT 'PENDING',
    raw_data JSON NOT NULL,
    update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_mdp_stg_s4_iqc (tenant_id, source_table, source_row_id),
    KEY idx_mdp_stg_s4_iqc_batch (sync_batch_id),
    KEY idx_mdp_stg_s4_iqc_biz (source_biz_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='S4 IQC/收货贴源';

CREATE TABLE IF NOT EXISTS mdp_stg_s4_shipment (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT NOT NULL DEFAULT 0,
    source_system VARCHAR(50) NOT NULL DEFAULT 'AIDOP',
    source_table VARCHAR(100) NOT NULL,
    source_row_id VARCHAR(100) NOT NULL,
    source_biz_key VARCHAR(200) NULL,
    sync_batch_id VARCHAR(100) NOT NULL,
    sync_time DATETIME NOT NULL,
    process_status VARCHAR(20) NOT NULL DEFAULT 'PENDING',
    raw_data JSON NOT NULL,
    update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_mdp_stg_s4_shipment (tenant_id, source_table, source_row_id),
    KEY idx_mdp_stg_s4_shipment_batch (sync_batch_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='S4 发货执行贴源';

CREATE TABLE IF NOT EXISTS mdp_stg_s4_return (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT NOT NULL DEFAULT 0,
    source_system VARCHAR(50) NOT NULL DEFAULT 'AIDOP',
    source_table VARCHAR(100) NOT NULL,
    source_row_id VARCHAR(100) NOT NULL,
    source_biz_key VARCHAR(200) NULL,
    sync_batch_id VARCHAR(100) NOT NULL,
    sync_time DATETIME NOT NULL,
    process_status VARCHAR(20) NOT NULL DEFAULT 'PENDING',
    raw_data JSON NOT NULL,
    update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_mdp_stg_s4_return (tenant_id, source_table, source_row_id),
    KEY idx_mdp_stg_s4_return_batch (sync_batch_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='S4 采购退货贴源';

CREATE TABLE IF NOT EXISTS mdp_stg_s4_shortage (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT NOT NULL DEFAULT 0,
    source_system VARCHAR(50) NOT NULL DEFAULT 'AIDOP',
    source_table VARCHAR(100) NOT NULL,
    source_row_id VARCHAR(100) NOT NULL,
    source_biz_key VARCHAR(200) NULL,
    sync_batch_id VARCHAR(100) NOT NULL,
    sync_time DATETIME NOT NULL,
    process_status VARCHAR(20) NOT NULL DEFAULT 'PENDING',
    raw_data JSON NOT NULL,
    update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_mdp_stg_s4_shortage (tenant_id, source_table, source_row_id),
    KEY idx_mdp_stg_s4_shortage_batch (sync_batch_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='S4 欠料执行贴源';

CREATE TABLE IF NOT EXISTS mdp_std_s4_iqc (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT NOT NULL DEFAULT 0,
    factory_id BIGINT NULL DEFAULT 1,
    source_system VARCHAR(50) NOT NULL DEFAULT 'AIDOP',
    po_no VARCHAR(50) NULL,
    po_line VARCHAR(50) NULL,
    supplier_code VARCHAR(50) NULL,
    item_code VARCHAR(50) NULL,
    receipt_qty DECIMAL(18,6) NULL DEFAULT 0,
    sample_qty DECIMAL(18,6) NULL DEFAULT 0,
    defect_qty DECIMAL(18,6) NULL DEFAULT 0,
    qc_result VARCHAR(20) NULL,
    receipt_date DATETIME NULL,
    source_biz_key VARCHAR(200) NULL,
    sync_batch_id VARCHAR(100) NOT NULL,
    sync_time DATETIME NOT NULL,
    update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_std_s4_iqc (tenant_id, source_biz_key),
    KEY idx_std_s4_iqc_po (tenant_id, po_no, po_line),
    KEY idx_std_s4_iqc_supplier (tenant_id, supplier_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='S4 IQC标准层';

CREATE TABLE IF NOT EXISTS mdp_std_s4_shipment (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT NOT NULL DEFAULT 0,
    factory_id BIGINT NULL DEFAULT 1,
    source_system VARCHAR(50) NOT NULL DEFAULT 'AIDOP',
    shipment_no VARCHAR(50) NULL,
    po_no VARCHAR(50) NULL,
    po_line VARCHAR(50) NULL,
    supplier_code VARCHAR(50) NULL,
    item_code VARCHAR(50) NULL,
    ship_qty DECIMAL(18,6) NULL DEFAULT 0,
    ship_date DATETIME NULL,
    source_biz_key VARCHAR(200) NULL,
    sync_batch_id VARCHAR(100) NOT NULL,
    sync_time DATETIME NOT NULL,
    update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_std_s4_shipment (tenant_id, source_biz_key),
    KEY idx_std_s4_shipment_po (tenant_id, po_no),
    KEY idx_std_s4_shipment_supplier (tenant_id, supplier_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='S4 发货执行标准层';

CREATE TABLE IF NOT EXISTS mdp_std_s4_return (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT NOT NULL DEFAULT 0,
    factory_id BIGINT NULL DEFAULT 1,
    source_system VARCHAR(50) NOT NULL DEFAULT 'AIDOP',
    po_no VARCHAR(50) NULL,
    po_line VARCHAR(50) NULL,
    supplier_code VARCHAR(50) NULL,
    item_code VARCHAR(50) NULL,
    return_qty DECIMAL(18,6) NULL DEFAULT 0,
    return_reason VARCHAR(200) NULL,
    return_status VARCHAR(50) NULL,
    source_biz_key VARCHAR(200) NULL,
    sync_batch_id VARCHAR(100) NOT NULL,
    sync_time DATETIME NOT NULL,
    update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_std_s4_return (tenant_id, source_biz_key),
    KEY idx_std_s4_return_po (tenant_id, po_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='S4 采购退货标准层';

CREATE TABLE IF NOT EXISTS mdp_std_s4_shortage (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT NOT NULL DEFAULT 0,
    factory_id BIGINT NULL DEFAULT 1,
    source_system VARCHAR(50) NOT NULL DEFAULT 'AIDOP',
    work_order VARCHAR(100) NULL,
    supplier_code VARCHAR(50) NULL,
    item_code VARCHAR(50) NULL,
    shortage_qty DECIMAL(18,6) NULL DEFAULT 0,
    risk_level VARCHAR(20) NULL,
    need_date DATETIME NULL,
    source_biz_key VARCHAR(200) NULL,
    sync_batch_id VARCHAR(100) NOT NULL,
    sync_time DATETIME NOT NULL,
    update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_std_s4_shortage (tenant_id, source_biz_key),
    KEY idx_std_s4_shortage_supplier (tenant_id, supplier_code),
    KEY idx_std_s4_shortage_item (tenant_id, item_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='S4 欠料执行标准层';

CREATE TABLE IF NOT EXISTS dwd_s4_purchase_execution (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT NOT NULL,
    factory_id BIGINT NOT NULL DEFAULT 1,
    stat_date DATE NOT NULL,
    po_no VARCHAR(50) NULL,
    po_line VARCHAR(50) NULL,
    supplier_code VARCHAR(50) NULL,
    item_code VARCHAR(50) NULL,
    order_qty DECIMAL(12,3) NULL DEFAULT 0,
    delivery_qty DECIMAL(12,3) NULL DEFAULT 0,
    received_qty DECIMAL(12,3) NULL DEFAULT 0,
    returned_qty DECIMAL(12,3) NULL DEFAULT 0,
    shortage_qty DECIMAL(12,3) NULL DEFAULT 0,
    due_date DATE NULL,
    actual_arrival_date DATE NULL,
    risk_level VARCHAR(20) NULL,
    source_system VARCHAR(20) NULL DEFAULT 'AIDOP',
    sync_batch_id VARCHAR(100) NULL,
    sync_time DATETIME NULL,
    update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_dwd_s4_pe (tenant_id, stat_date, po_no, po_line, item_code),
    KEY idx_dwd_s4_pe_date (tenant_id, stat_date),
    KEY idx_dwd_s4_pe_supplier (tenant_id, supplier_code),
    KEY idx_dwd_s4_pe_po (tenant_id, po_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='S4采购执行宽表';

-- 扩展 dwd_po_trans（幂等加列）
SET @db := DATABASE();

SET @sql := IF(
    EXISTS(SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='dwd_po_trans' AND COLUMN_NAME='factory_id'),
    'SELECT 1', 'ALTER TABLE dwd_po_trans ADD COLUMN factory_id BIGINT NULL DEFAULT 1 AFTER tenant_id');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF(
    EXISTS(SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='dwd_po_trans' AND COLUMN_NAME='po_line'),
    'SELECT 1', 'ALTER TABLE dwd_po_trans ADD COLUMN po_line VARCHAR(50) NULL AFTER po_no');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF(
    EXISTS(SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='dwd_po_trans' AND COLUMN_NAME='returned_qty'),
    'SELECT 1', 'ALTER TABLE dwd_po_trans ADD COLUMN returned_qty DECIMAL(12,3) NULL DEFAULT 0 AFTER received_qty');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF(
    EXISTS(SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='dwd_po_trans' AND COLUMN_NAME='shortage_qty'),
    'SELECT 1', 'ALTER TABLE dwd_po_trans ADD COLUMN shortage_qty DECIMAL(12,3) NULL DEFAULT 0 AFTER returned_qty');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF(
    EXISTS(SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='dwd_po_trans' AND COLUMN_NAME='due_date'),
    'SELECT 1', 'ALTER TABLE dwd_po_trans ADD COLUMN due_date DATE NULL AFTER shortage_qty');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF(
    EXISTS(SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='dwd_po_trans' AND COLUMN_NAME='actual_arrival_date'),
    'SELECT 1', 'ALTER TABLE dwd_po_trans ADD COLUMN actual_arrival_date DATE NULL AFTER due_date');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF(
    EXISTS(SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='dwd_po_trans' AND COLUMN_NAME='risk_level'),
    'SELECT 1', 'ALTER TABLE dwd_po_trans ADD COLUMN risk_level VARCHAR(20) NULL AFTER actual_arrival_date');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF(
    EXISTS(SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='dwd_po_trans' AND COLUMN_NAME='sync_batch_id'),
    'SELECT 1', 'ALTER TABLE dwd_po_trans ADD COLUMN sync_batch_id VARCHAR(100) NULL AFTER source_system');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

INSERT INTO mdp_entity
(tenant_id, source_id, entity_code, entity_name, entity_type, source_table_name, target_table_name, sync_mode, batch_size, status, remark)
SELECT 0, s.id, v.entity_code, v.entity_name, 'TABLE', v.source_table_name, v.target_table_name, 'FULL', 5000, 1, v.remark
FROM mdp_source s
JOIN (
    SELECT 'S4_IQC_RECEIPT' AS entity_code, 'S4 IQC收货明细' AS entity_name, 'PurOrdRctDetail' AS source_table_name, 'mdp_stg_s4_iqc' AS target_table_name, 'S4 采购执行 IQC/收货贴源，不重复 S3 采购订单主链' AS remark
    UNION ALL SELECT 'S4_SHIPMENT_EXEC', 'S4 发货执行明细', 'scm_shdzb', 'mdp_stg_s4_shipment', 'S4 供应商发货单执行贴源'
    UNION ALL SELECT 'S4_RETURN_EXEC', 'S4 采购退货行', 'srm_polist_ds', 'mdp_stg_s4_return', 'S4 交货计划退货数量贴源'
    UNION ALL SELECT 'S4_SHORTAGE_EXEC', 'S4 欠料执行', 'dwd_material_shortage', 'mdp_stg_s4_shortage', 'S4 消费 S3 缺料 DWD 快照，不新增第三套工单贴源'
) v ON 1=1
WHERE s.tenant_id = 0 AND s.source_code = 'AIDOPDEV_MYSQL'
ON DUPLICATE KEY UPDATE
    source_id = VALUES(source_id),
    entity_name = VALUES(entity_name),
    source_table_name = VALUES(source_table_name),
    target_table_name = VALUES(target_table_name),
    remark = VALUES(remark),
    update_time = CURRENT_TIMESTAMP;

-- ----------------------------------------------------------------------
-- SYS-TENANT-CONNECTION-LENGTH-1
-- 放大 SysTenant.Connection，避免 ID 隔离租户写入主库连接串时超过 varchar(256)。

SET NAMES utf8mb4;
SET @db := DATABASE();

SET @sql := IF(
    EXISTS(
        SELECT 1
        FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = @db
          AND LOWER(TABLE_NAME) = LOWER('SysTenant')
          AND COLUMN_NAME = 'Connection'
          AND DATA_TYPE <> 'text'
    ),
    'ALTER TABLE `SysTenant` MODIFY COLUMN `Connection` TEXT NULL COMMENT ''数据库连接''',
    'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;