259 lines
12 KiB
SQL
259 lines
12 KiB
SQL
-- =====================================================
|
||
-- 生产计划优化 v1.6.037
|
||
-- 作者: 周启威
|
||
-- 日期: 2026-02-25
|
||
-- 说明: 优化生产计划创建流程，支持多订单批量处理
|
||
-- =====================================================
|
||
|
||
-- -----------------------------------------------------
|
||
-- 1. 生产计划表扩展字段（如果不存在）
|
||
-- -----------------------------------------------------
|
||
|
||
-- 添加计划周期字段
|
||
SET @col_exists = 0;
|
||
SELECT COUNT(*) INTO @col_exists FROM information_schema.columns
|
||
WHERE table_schema = DATABASE() AND table_name = 'pro_plan' AND column_name = 'schedule_cycle';
|
||
SET @sql = IF(@col_exists = 0,
|
||
'ALTER TABLE `pro_plan` ADD COLUMN `schedule_cycle` varchar(20) DEFAULT NULL COMMENT ''计划周期：year=年计划,month=月计划,week=周计划'' AFTER `plan_type`',
|
||
'SELECT ''Column schedule_cycle already exists'' AS message');
|
||
PREPARE stmt FROM @sql;
|
||
EXECUTE stmt;
|
||
DEALLOCATE PREPARE stmt;
|
||
|
||
-- 添加计划年份字段
|
||
SET @col_exists = 0;
|
||
SELECT COUNT(*) INTO @col_exists FROM information_schema.columns
|
||
WHERE table_schema = DATABASE() AND table_name = 'pro_plan' AND column_name = 'plan_year';
|
||
SET @sql = IF(@col_exists = 0,
|
||
'ALTER TABLE `pro_plan` ADD COLUMN `plan_year` int DEFAULT NULL COMMENT ''计划年份'' AFTER `schedule_cycle`',
|
||
'SELECT ''Column plan_year already exists'' AS message');
|
||
PREPARE stmt FROM @sql;
|
||
EXECUTE stmt;
|
||
DEALLOCATE PREPARE stmt;
|
||
|
||
-- 添加月份字段
|
||
SET @col_exists = 0;
|
||
SELECT COUNT(*) INTO @col_exists FROM information_schema.columns
|
||
WHERE table_schema = DATABASE() AND table_name = 'pro_plan' AND column_name = 'month_number';
|
||
SET @sql = IF(@col_exists = 0,
|
||
'ALTER TABLE `pro_plan` ADD COLUMN `month_number` int DEFAULT NULL COMMENT ''月份（1-12）'' AFTER `plan_year`',
|
||
'SELECT ''Column month_number already exists'' AS message');
|
||
PREPARE stmt FROM @sql;
|
||
EXECUTE stmt;
|
||
DEALLOCATE PREPARE stmt;
|
||
|
||
-- 添加周数字段
|
||
SET @col_exists = 0;
|
||
SELECT COUNT(*) INTO @col_exists FROM information_schema.columns
|
||
WHERE table_schema = DATABASE() AND table_name = 'pro_plan' AND column_name = 'week_number';
|
||
SET @sql = IF(@col_exists = 0,
|
||
'ALTER TABLE `pro_plan` ADD COLUMN `week_number` int DEFAULT NULL COMMENT ''周数（1-53）'' AFTER `month_number`',
|
||
'SELECT ''Column week_number already exists'' AS message');
|
||
PREPARE stmt FROM @sql;
|
||
EXECUTE stmt;
|
||
DEALLOCATE PREPARE stmt;
|
||
|
||
-- 添加开始时间字段（如果不存在）
|
||
SET @col_exists = 0;
|
||
SELECT COUNT(*) INTO @col_exists FROM information_schema.columns
|
||
WHERE table_schema = DATABASE() AND table_name = 'pro_plan' AND column_name = 'start_time';
|
||
SET @sql = IF(@col_exists = 0,
|
||
'ALTER TABLE `pro_plan` ADD COLUMN `start_time` datetime DEFAULT NULL COMMENT ''开始时间'' AFTER `week_number`',
|
||
'SELECT ''Column start_time already exists'' AS message');
|
||
PREPARE stmt FROM @sql;
|
||
EXECUTE stmt;
|
||
DEALLOCATE PREPARE stmt;
|
||
|
||
-- 添加结束时间字段（如果不存在）
|
||
SET @col_exists = 0;
|
||
SELECT COUNT(*) INTO @col_exists FROM information_schema.columns
|
||
WHERE table_schema = DATABASE() AND table_name = 'pro_plan' AND column_name = 'end_time';
|
||
SET @sql = IF(@col_exists = 0,
|
||
'ALTER TABLE `pro_plan` ADD COLUMN `end_time` datetime DEFAULT NULL COMMENT ''结束时间'' AFTER `start_time`',
|
||
'SELECT ''Column end_time already exists'' AS message');
|
||
PREPARE stmt FROM @sql;
|
||
EXECUTE stmt;
|
||
DEALLOCATE PREPARE stmt;
|
||
|
||
-- 添加关联订单ID列表字段
|
||
SET @col_exists = 0;
|
||
SELECT COUNT(*) INTO @col_exists FROM information_schema.columns
|
||
WHERE table_schema = DATABASE() AND table_name = 'pro_plan' AND column_name = 'related_order_ids';
|
||
SET @sql = IF(@col_exists = 0,
|
||
'ALTER TABLE `pro_plan` ADD COLUMN `related_order_ids` text DEFAULT NULL COMMENT ''关联订单ID列表（JSON格式）'' AFTER `plan_analysis`',
|
||
'SELECT ''Column related_order_ids already exists'' AS message');
|
||
PREPARE stmt FROM @sql;
|
||
EXECUTE stmt;
|
||
DEALLOCATE PREPARE stmt;
|
||
|
||
-- -----------------------------------------------------
|
||
-- 2. 添加索引优化查询性能
|
||
-- -----------------------------------------------------
|
||
|
||
-- 计划周期索引
|
||
SET @index_exists = 0;
|
||
SELECT COUNT(*) INTO @index_exists FROM information_schema.statistics
|
||
WHERE table_schema = DATABASE() AND table_name = 'pro_plan' AND index_name = 'idx_schedule_cycle';
|
||
SET @sql = IF(@index_exists = 0,
|
||
'ALTER TABLE `pro_plan` ADD INDEX `idx_schedule_cycle` (`schedule_cycle`)',
|
||
'SELECT ''Index idx_schedule_cycle already exists'' AS message');
|
||
PREPARE stmt FROM @sql;
|
||
EXECUTE stmt;
|
||
DEALLOCATE PREPARE stmt;
|
||
|
||
-- 年份+周数索引
|
||
SET @index_exists = 0;
|
||
SELECT COUNT(*) INTO @index_exists FROM information_schema.statistics
|
||
WHERE table_schema = DATABASE() AND table_name = 'pro_plan' AND index_name = 'idx_year_week';
|
||
SET @sql = IF(@index_exists = 0,
|
||
'ALTER TABLE `pro_plan` ADD INDEX `idx_year_week` (`plan_year`, `week_number`)',
|
||
'SELECT ''Index idx_year_week already exists'' AS message');
|
||
PREPARE stmt FROM @sql;
|
||
EXECUTE stmt;
|
||
DEALLOCATE PREPARE stmt;
|
||
|
||
-- 年份+月份索引
|
||
SET @index_exists = 0;
|
||
SELECT COUNT(*) INTO @index_exists FROM information_schema.statistics
|
||
WHERE table_schema = DATABASE() AND table_name = 'pro_plan' AND index_name = 'idx_year_month';
|
||
SET @sql = IF(@index_exists = 0,
|
||
'ALTER TABLE `pro_plan` ADD INDEX `idx_year_month` (`plan_year`, `month_number`)',
|
||
'SELECT ''Index idx_year_month already exists'' AS message');
|
||
PREPARE stmt FROM @sql;
|
||
EXECUTE stmt;
|
||
DEALLOCATE PREPARE stmt;
|
||
|
||
-- 工序路线索引（如果不存在）
|
||
SET @index_exists = 0;
|
||
SELECT COUNT(*) INTO @index_exists FROM information_schema.statistics
|
||
WHERE table_schema = DATABASE() AND table_name = 'pro_plan' AND index_name = 'idx_route_id';
|
||
SET @sql = IF(@index_exists = 0,
|
||
'ALTER TABLE `pro_plan` ADD INDEX `idx_route_id` (`route_id`)',
|
||
'SELECT ''Index idx_route_id already exists'' AS message');
|
||
PREPARE stmt FROM @sql;
|
||
EXECUTE stmt;
|
||
DEALLOCATE PREPARE stmt;
|
||
|
||
-- -----------------------------------------------------
|
||
-- 3. 产品表验证和索引优化
|
||
-- -----------------------------------------------------
|
||
|
||
-- 验证产品表中是否存在route_id字段（默认工序路线ID）
|
||
-- 该字段已存在于md_material表中，用于存储产品的默认工序路线
|
||
-- 如果不存在，则添加该字段
|
||
SET @col_exists = 0;
|
||
SELECT COUNT(*) INTO @col_exists FROM information_schema.columns
|
||
WHERE table_schema = DATABASE() AND table_name = 'md_material' AND column_name = 'route_id';
|
||
SET @sql = IF(@col_exists = 0,
|
||
'ALTER TABLE `md_material` ADD COLUMN `route_id` bigint DEFAULT NULL COMMENT ''默认工序路线ID'' AFTER `update_time`',
|
||
'SELECT ''Column route_id already exists in md_material'' AS message');
|
||
PREPARE stmt FROM @sql;
|
||
EXECUTE stmt;
|
||
DEALLOCATE PREPARE stmt;
|
||
|
||
-- 为产品表的route_id字段创建索引，优化工序路线查询性能
|
||
SET @index_exists = 0;
|
||
SELECT COUNT(*) INTO @index_exists FROM information_schema.statistics
|
||
WHERE table_schema = DATABASE() AND table_name = 'md_material' AND index_name = 'idx_material_route_id';
|
||
SET @sql = IF(@index_exists = 0,
|
||
'ALTER TABLE `md_material` ADD INDEX `idx_material_route_id` (`route_id`)',
|
||
'SELECT ''Index idx_material_route_id already exists'' AS message');
|
||
PREPARE stmt FROM @sql;
|
||
EXECUTE stmt;
|
||
DEALLOCATE PREPARE stmt;
|
||
|
||
-- -----------------------------------------------------
|
||
-- 4. 工单表验证和索引优化
|
||
-- -----------------------------------------------------
|
||
|
||
-- 验证工单表中是否存在source_type字段（来源类型）
|
||
-- 该字段已存在于pro_workorder表中，用于标识工单的来源（如"plan"表示来自计划）
|
||
SET @col_exists = 0;
|
||
SELECT COUNT(*) INTO @col_exists FROM information_schema.columns
|
||
WHERE table_schema = DATABASE() AND table_name = 'pro_workorder' AND column_name = 'source_type';
|
||
SELECT IF(@col_exists > 0,
|
||
'Column source_type exists in pro_workorder',
|
||
'WARNING: Column source_type does NOT exist in pro_workorder') AS verification_result;
|
||
|
||
-- 验证工单表中是否存在source_info字段（来源信息）
|
||
-- 该字段已存在于pro_workorder表中，用于存储计划ID和订单信息（JSON格式）
|
||
SET @col_exists = 0;
|
||
SELECT COUNT(*) INTO @col_exists FROM information_schema.columns
|
||
WHERE table_schema = DATABASE() AND table_name = 'pro_workorder' AND column_name = 'source_info';
|
||
SELECT IF(@col_exists > 0,
|
||
'Column source_info exists in pro_workorder',
|
||
'WARNING: Column source_info does NOT exist in pro_workorder') AS verification_result;
|
||
|
||
-- 为source_type字段创建索引，优化按来源类型查询工单的性能
|
||
SET @index_exists = 0;
|
||
SELECT COUNT(*) INTO @index_exists FROM information_schema.statistics
|
||
WHERE table_schema = DATABASE() AND table_name = 'pro_workorder' AND index_name = 'idx_source_type';
|
||
SET @sql = IF(@index_exists = 0,
|
||
'ALTER TABLE `pro_workorder` ADD INDEX `idx_source_type` (`source_type`)',
|
||
'SELECT ''Index idx_source_type already exists'' AS message');
|
||
PREPARE stmt FROM @sql;
|
||
EXECUTE stmt;
|
||
DEALLOCATE PREPARE stmt;
|
||
|
||
-- 为material_id字段创建索引（如果不存在），优化按产品查询工单的性能
|
||
-- 注意：该索引可能已存在，脚本会自动检查
|
||
SET @index_exists = 0;
|
||
SELECT COUNT(*) INTO @index_exists FROM information_schema.statistics
|
||
WHERE table_schema = DATABASE() AND table_name = 'pro_workorder' AND index_name = 'idx_workorder_material';
|
||
SET @sql = IF(@index_exists = 0,
|
||
'ALTER TABLE `pro_workorder` ADD INDEX `idx_workorder_material` (`material_id`)',
|
||
'SELECT ''Index idx_workorder_material already exists'' AS message');
|
||
PREPARE stmt FROM @sql;
|
||
EXECUTE stmt;
|
||
DEALLOCATE PREPARE stmt;
|
||
|
||
-- -----------------------------------------------------
|
||
-- 5. 更新计划周期字典数据（如果不存在）
|
||
-- -----------------------------------------------------
|
||
|
||
-- 检查并插入年计划字典
|
||
INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
|
||
SELECT 0, '年计划', 'year', 'pro_schedule_cycle', '', 'danger', 'N', '0', 'admin', NOW(), '按年制定的生产计划'
|
||
WHERE NOT EXISTS (
|
||
SELECT 1 FROM sys_dict_data WHERE dict_type = 'pro_schedule_cycle' AND dict_value = 'year'
|
||
);
|
||
|
||
-- 检查并插入周计划字典
|
||
INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
|
||
SELECT 1, '周计划', 'week', 'pro_schedule_cycle', '', 'primary', 'N', '0', 'admin', NOW(), '按周制定的生产计划'
|
||
WHERE NOT EXISTS (
|
||
SELECT 1 FROM sys_dict_data WHERE dict_type = 'pro_schedule_cycle' AND dict_value = 'week'
|
||
);
|
||
|
||
-- 检查并插入月计划字典
|
||
INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
|
||
SELECT 2, '月计划', 'month', 'pro_schedule_cycle', '', 'success', 'N', '0', 'admin', NOW(), '按月制定的生产计划'
|
||
WHERE NOT EXISTS (
|
||
SELECT 1 FROM sys_dict_data WHERE dict_type = 'pro_schedule_cycle' AND dict_value = 'month'
|
||
);
|
||
|
||
-- -----------------------------------------------------
|
||
-- 6. 说明
|
||
-- -----------------------------------------------------
|
||
-- 本次优化不新增表，使用现有表结构：
|
||
-- - pro_plan: 生产计划表（扩展字段）
|
||
-- - pro_workorder: 工单主表（已验证source_type和source_info字段存在，用于关联计划）
|
||
-- - pro_workorder_entry: 工单子表（工序明细）
|
||
-- - pro_report: 报工单表（报工记录）
|
||
--
|
||
-- 字段验证结果：
|
||
-- - pro_workorder.source_type: 已存在，用于标识工单来源（如"plan"）
|
||
-- - pro_workorder.source_info: 已存在，用于存储计划ID和订单信息（JSON格式）
|
||
-- - md_material.route_id: 已验证/添加，用于存储产品的默认工序路线ID
|
||
--
|
||
-- 多订单处理方式：
|
||
-- - plan_analysis字段存储JSON格式的多订单分析数据
|
||
-- - related_order_ids字段存储关联的订单ID列表
|
||
-- - 每个订单生成一个工单，通过source_type="plan"和source_info关联到计划
|
||
-- - 工单子表记录每个工序的明细
|
||
-- - 报工单记录每个工序的报工情况
|
||
--
|
||
-- 索引优化：
|
||
-- - pro_plan: 添加schedule_cycle、year_week、year_month、route_id索引
|
||
-- - md_material: 添加route_id索引
|
||
-- - pro_workorder: 添加source_type索引，优化按来源类型查询性能 |