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

-- 未映射库存流水隔离表。trans_type 留空的行登记在此，指标不计入。
CREATE TABLE IF NOT EXISTS mdp_std_inv_trans_unmapped (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT NOT NULL DEFAULT 0,
    source_system VARCHAR(50) NOT NULL,
    src_rec_id VARCHAR(100) NOT NULL,
    src_trans_type_raw VARCHAR(64) NOT NULL,
    row_count BIGINT NOT NULL DEFAULT 1,
    first_seen DATETIME NOT NULL,
    last_seen DATETIME NOT NULL,
    UNIQUE KEY uk_inv_unmapped (tenant_id, source_system, src_rec_id),
    KEY idx_inv_unmapped_code (tenant_id, source_system, src_trans_type_raw)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='未映射到中立阶段码的库存流水';

INSERT INTO mdp_std_inv_trans_unmapped
  (tenant_id, source_system, src_rec_id, src_trans_type_raw, row_count, first_seen, last_seen)
SELECT tenant_id, source_system, src_rec_id, src_trans_type_raw, 1, NOW(), NOW()
FROM mdp_std_inv_trans
WHERE trans_type IS NULL AND src_trans_type_raw IS NOT NULL
ON DUPLICATE KEY UPDATE last_seen=NOW();

-- 排程宽表补 doc_type，唯一键纳入单据类型，生产任务与销售订单重号不再互相覆盖
SET @sql := IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='dwd_order_schedule_trans' AND COLUMN_NAME='doc_type')=0, 'ALTER TABLE `dwd_order_schedule_trans` ADD COLUMN `doc_type` VARCHAR(32) NOT NULL DEFAULT ''PROD_TASK''', 'DO 0');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql := IF((SELECT COUNT(*) FROM information_schema.STATISTICS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='dwd_order_schedule_trans' AND INDEX_NAME='uk_dwd_order_schedule_trans' AND COLUMN_NAME='doc_type')=0 AND (SELECT COUNT(*) FROM information_schema.STATISTICS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='dwd_order_schedule_trans' AND INDEX_NAME='uk_dwd_order_schedule_trans')>0, 'ALTER TABLE `dwd_order_schedule_trans` DROP INDEX `uk_dwd_order_schedule_trans`, ADD UNIQUE KEY `uk_dwd_order_schedule_trans` (`tenant_id`,`doc_type`,`work_order`,`calc_batch_id`)', 'DO 0');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- FQC 任务唯一键纳入来源，避免不同来源的同号 source_row_id 互相覆盖
SET @sql := IF((SELECT COUNT(*) FROM information_schema.STATISTICS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='mdp_std_fqc_task' AND INDEX_NAME='uk_mdp_std_fqc_task' AND COLUMN_NAME='source_system')=0 AND (SELECT COUNT(*) FROM information_schema.STATISTICS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='mdp_std_fqc_task' AND INDEX_NAME='uk_mdp_std_fqc_task')>0, 'ALTER TABLE `mdp_std_fqc_task` DROP INDEX `uk_mdp_std_fqc_task`, ADD UNIQUE KEY `uk_mdp_std_fqc_task` (`tenant_id`,`source_system`,`source_row_id`)', 'DO 0');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 入站授权可按对接方收紧契约版本。默认 v1，未通知对接方前不拒收旧报文。
SET @sql := IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='mdp_inbound_grant' AND COLUMN_NAME='min_contract_version')=0 AND (SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='mdp_inbound_grant')>0, 'ALTER TABLE `mdp_inbound_grant` ADD COLUMN `min_contract_version` VARCHAR(8) NOT NULL DEFAULT ''v1''', 'DO 0');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;