-- 仓储库存状态初始化（无租户字段）
|
SET @stock_state_exists = (
|
SELECT COUNT(*)
|
FROM information_schema.columns
|
WHERE table_schema = DATABASE()
|
AND table_name = 'mes_wm_material_stock'
|
AND column_name = 'stock_state'
|
);
|
SET @stock_state_sql = IF(
|
@stock_state_exists = 0,
|
'ALTER TABLE mes_wm_material_stock ADD COLUMN stock_state tinyint NOT NULL DEFAULT 20 COMMENT ''库存状态：10暂存库，20合格库，30已出库'' AFTER in_transit_quantity',
|
'SELECT 1'
|
);
|
PREPARE stock_state_stmt FROM @stock_state_sql;
|
EXECUTE stock_state_stmt;
|
DEALLOCATE PREPARE stock_state_stmt;
|
|
SET @stock_state_index_exists = (
|
SELECT COUNT(*)
|
FROM information_schema.statistics
|
WHERE table_schema = DATABASE()
|
AND table_name = 'mes_wm_material_stock'
|
AND index_name = 'idx_stock_state_query'
|
);
|
SET @stock_state_index_sql = IF(
|
@stock_state_index_exists = 0,
|
'CREATE INDEX idx_stock_state_query ON mes_wm_material_stock (stock_state, item_id, batch_id, warehouse_id, receipt_time)',
|
'SELECT 1'
|
);
|
PREPARE stock_state_index_stmt FROM @stock_state_index_sql;
|
EXECUTE stock_state_index_stmt;
|
DEALLOCATE PREPARE stock_state_index_stmt;
|
