-- 销售订单模块新增字段（2026-05-11）
|
-- 说明：
|
-- 1) 脚本按“存在则跳过，不存在则新增”执行，可重复执行。
|
-- 2) 物料号统一字段为 material_no；若历史存在 material，会回填到 material_no。
|
|
SET @db_name = DATABASE();
|
|
-- ----------------------------
|
-- sales_ledger 新增字段
|
-- ----------------------------
|
SELECT COUNT(*) INTO @cnt
|
FROM information_schema.COLUMNS
|
WHERE TABLE_SCHEMA = @db_name
|
AND TABLE_NAME = 'sales_ledger'
|
AND COLUMN_NAME = 'order_type';
|
SET @sql = IF(@cnt = 0,
|
'ALTER TABLE sales_ledger ADD COLUMN order_type VARCHAR(64) NULL COMMENT ''订单类型''',
|
'SELECT ''skip: sales_ledger.order_type exists''');
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
SELECT COUNT(*) INTO @cnt
|
FROM information_schema.COLUMNS
|
WHERE TABLE_SCHEMA = @db_name
|
AND TABLE_NAME = 'sales_ledger'
|
AND COLUMN_NAME = 'order_line';
|
SET @sql = IF(@cnt = 0,
|
'ALTER TABLE sales_ledger ADD COLUMN order_line VARCHAR(64) NULL COMMENT ''订单行''',
|
'SELECT ''skip: sales_ledger.order_line exists''');
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
SELECT COUNT(*) INTO @cnt
|
FROM information_schema.COLUMNS
|
WHERE TABLE_SCHEMA = @db_name
|
AND TABLE_NAME = 'sales_ledger'
|
AND COLUMN_NAME = 'demand_date';
|
SET @sql = IF(@cnt = 0,
|
'ALTER TABLE sales_ledger ADD COLUMN demand_date DATE NULL COMMENT ''需求日期''',
|
'SELECT ''skip: sales_ledger.demand_date exists''');
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
-- ----------------------------
|
-- sales_ledger_product 新增字段
|
-- ----------------------------
|
SELECT COUNT(*) INTO @cnt
|
FROM information_schema.COLUMNS
|
WHERE TABLE_SCHEMA = @db_name
|
AND TABLE_NAME = 'sales_ledger_product'
|
AND COLUMN_NAME = 'delivery_quantity';
|
SET @sql = IF(@cnt = 0,
|
'ALTER TABLE sales_ledger_product ADD COLUMN delivery_quantity DECIMAL(18,6) NULL COMMENT ''交货数量''',
|
'SELECT ''skip: sales_ledger_product.delivery_quantity exists''');
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
SELECT COUNT(*) INTO @cnt
|
FROM information_schema.COLUMNS
|
WHERE TABLE_SCHEMA = @db_name
|
AND TABLE_NAME = 'sales_ledger_product'
|
AND COLUMN_NAME = 'remaining_quantity';
|
SET @sql = IF(@cnt = 0,
|
'ALTER TABLE sales_ledger_product ADD COLUMN remaining_quantity DECIMAL(18,6) NULL COMMENT ''剩余数量''',
|
'SELECT ''skip: sales_ledger_product.remaining_quantity exists''');
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
SELECT COUNT(*) INTO @cnt
|
FROM information_schema.COLUMNS
|
WHERE TABLE_SCHEMA = @db_name
|
AND TABLE_NAME = 'sales_ledger_product'
|
AND COLUMN_NAME = 'sub_inventory';
|
SET @sql = IF(@cnt = 0,
|
'ALTER TABLE sales_ledger_product ADD COLUMN sub_inventory VARCHAR(128) NULL COMMENT ''子库存''',
|
'SELECT ''skip: sales_ledger_product.sub_inventory exists''');
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
SELECT COUNT(*) INTO @cnt
|
FROM information_schema.COLUMNS
|
WHERE TABLE_SCHEMA = @db_name
|
AND TABLE_NAME = 'sales_ledger_product'
|
AND COLUMN_NAME = 'location';
|
SET @sql = IF(@cnt = 0,
|
'ALTER TABLE sales_ledger_product ADD COLUMN location VARCHAR(128) NULL COMMENT ''货位''',
|
'SELECT ''skip: sales_ledger_product.location exists''');
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
SELECT COUNT(*) INTO @cnt
|
FROM information_schema.COLUMNS
|
WHERE TABLE_SCHEMA = @db_name
|
AND TABLE_NAME = 'sales_ledger_product'
|
AND COLUMN_NAME = 'is_spray';
|
SET @sql = IF(@cnt = 0,
|
'ALTER TABLE sales_ledger_product ADD COLUMN is_spray TINYINT(1) NULL COMMENT ''是否喷砂''',
|
'SELECT ''skip: sales_ledger_product.is_spray exists''');
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
SELECT COUNT(*) INTO @cnt
|
FROM information_schema.COLUMNS
|
WHERE TABLE_SCHEMA = @db_name
|
AND TABLE_NAME = 'sales_ledger_product'
|
AND COLUMN_NAME = 'material_no';
|
SET @sql = IF(@cnt = 0,
|
'ALTER TABLE sales_ledger_product ADD COLUMN material_no VARCHAR(128) NULL COMMENT ''物料号''',
|
'SELECT ''skip: sales_ledger_product.material_no exists''');
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
-- ----------------------------
|
-- 历史数据兼容：material -> material_no
|
-- ----------------------------
|
SELECT COUNT(*) INTO @has_old_material
|
FROM information_schema.COLUMNS
|
WHERE TABLE_SCHEMA = @db_name
|
AND TABLE_NAME = 'sales_ledger_product'
|
AND COLUMN_NAME = 'material';
|
|
SELECT COUNT(*) INTO @has_new_material_no
|
FROM information_schema.COLUMNS
|
WHERE TABLE_SCHEMA = @db_name
|
AND TABLE_NAME = 'sales_ledger_product'
|
AND COLUMN_NAME = 'material_no';
|
|
SET @sql = IF(@has_old_material = 1 AND @has_new_material_no = 1,
|
'UPDATE sales_ledger_product
|
SET material_no = material
|
WHERE (material_no IS NULL OR material_no = '''')
|
AND material IS NOT NULL',
|
'SELECT ''skip: material -> material_no backfill''');
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
-- 可选：确认前端/后端都已切换后，再评估是否删除旧列 material
|
-- ALTER TABLE sales_ledger_product DROP COLUMN material;
|
