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

-- ============================================================================
-- 1.0.338 订单评审三表占用追踪
--   1) 新建 b_stock_occupy（库存占用）
--   2) ALTER b_purchase_occupy（采购订单占用 — 已有表加字段）
--   3) ALTER b_mo_occupy（计划工单占用 — 已有表加字段）
-- ============================================================================

-- 1. 新建 b_stock_occupy
CREATE TABLE IF NOT EXISTS `b_stock_occupy` (
  `Id` BIGINT NOT NULL,
  `bill_no` VARCHAR(60) NOT NULL DEFAULT '' COMMENT '订单号',
  `entry_id` BIGINT NOT NULL DEFAULT 0 COMMENT '订单明细ID',
  `lot_serial` VARCHAR(30) NOT NULL DEFAULT '' COMMENT '库存批次(InvMaster.LotSerial)',
  `item_number` VARCHAR(50) NOT NULL DEFAULT '' COMMENT '物料号',
  `qty` DECIMAL(18,5) NOT NULL DEFAULT 0 COMMENT '占用数量',
  `examine_id` BIGINT NULL COMMENT '资源检查编号(b_examine_result.Id)',
  `tenant_id` BIGINT NOT NULL DEFAULT 0,
  `status` INT NOT NULL DEFAULT 0 COMMENT '0=预占,1=已占用',
  `create_time` DATETIME NOT NULL,
  `update_time` DATETIME NOT NULL,
  PRIMARY KEY (`Id`),
  KEY `idx_bso_tenant_item_status` (`tenant_id`, `item_number`, `status`),
  KEY `idx_bso_entry` (`tenant_id`, `entry_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单评审库存占用表';

-- 2. ALTER b_purchase_occupy（已有表加字段）
-- 幂等：先检查列是否存在
SET @has_col := (SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'b_purchase_occupy' AND COLUMN_NAME = 'bill_no');
SET @sql := IF(@has_col = 0,
  'ALTER TABLE `b_purchase_occupy`
    ADD COLUMN `bill_no` VARCHAR(60) NOT NULL DEFAULT '''' COMMENT ''订单号'' AFTER `Id`,
    ADD COLUMN `entry_id` BIGINT NOT NULL DEFAULT 0 COMMENT ''订单明细ID'' AFTER `bill_no`,
    ADD COLUMN `item_number` VARCHAR(50) NOT NULL DEFAULT '''' COMMENT ''物料号'' AFTER `entry_id`,
    ADD COLUMN `examine_id` BIGINT NULL COMMENT ''资源检查编号'' AFTER `item_number`,
    ADD COLUMN `status` INT NOT NULL DEFAULT 0 COMMENT ''0=预占,1=已占用'' AFTER `examine_id`',
  'DO 0');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

SET @has_idx := (SELECT COUNT(DISTINCT INDEX_NAME) FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'b_purchase_occupy'
    AND INDEX_NAME = 'idx_bpo_tenant_item_status');
SET @sql := IF(@has_idx = 0,
  'ALTER TABLE `b_purchase_occupy`
    ADD KEY `idx_bpo_tenant_item_status` (`tenant_id`, `item_number`, `status`),
    ADD KEY `idx_bpo_entry` (`tenant_id`, `entry_id`)',
  'DO 0');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- 3. ALTER b_mo_occupy（已有表加字段）
SET @has_col := (SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'b_mo_occupy' AND COLUMN_NAME = 'bill_no');
SET @sql := IF(@has_col = 0,
  'ALTER TABLE `b_mo_occupy`
    ADD COLUMN `bill_no` VARCHAR(60) NOT NULL DEFAULT '''' COMMENT ''订单号'' AFTER `Id`,
    ADD COLUMN `entry_id` BIGINT NOT NULL DEFAULT 0 COMMENT ''订单明细ID'' AFTER `bill_no`,
    ADD COLUMN `item_number` VARCHAR(50) NOT NULL DEFAULT '''' COMMENT ''物料号'' AFTER `entry_id`,
    ADD COLUMN `examine_id` BIGINT NULL COMMENT ''资源检查编号'' AFTER `item_number`,
    ADD COLUMN `status` INT NOT NULL DEFAULT 0 COMMENT ''0=预占,1=已占用'' AFTER `examine_id`',
  'DO 0');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

SET @has_idx := (SELECT COUNT(DISTINCT INDEX_NAME) FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'b_mo_occupy'
    AND INDEX_NAME = 'idx_bmo_tenant_item_status');
SET @sql := IF(@has_idx = 0,
  'ALTER TABLE `b_mo_occupy`
    ADD KEY `idx_bmo_tenant_item_status` (`tenant_id`, `item_number`, `status`),
    ADD KEY `idx_bmo_entry` (`tenant_id`, `entry_id`)',
  'DO 0');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;