57 lines
2.8 KiB
SQL
57 lines
2.8 KiB
SQL
-- =============================================
|
||
-- 保养计划优化 - 频率改为计划执行时间
|
||
-- 作者: Cascade
|
||
-- 日期: 2026-01-20
|
||
-- 描述:
|
||
-- 1. 添加计划执行时间字段(planned_time)替代频率
|
||
-- 2. 修改状态字段含义：0待执行/1已执行/2已取消
|
||
-- 3. 执行一次计划就结束，不再循环
|
||
-- =============================================
|
||
|
||
-- =============================================
|
||
-- 一、扩展表结构
|
||
-- =============================================
|
||
|
||
SET @dbname = DATABASE();
|
||
SET @tablename = 'dm_inspection_plan';
|
||
|
||
-- 1.1 添加 planned_time 字段（计划执行时间）
|
||
SET @columnname = 'planned_time';
|
||
SET @preparedStatement = (SELECT IF(
|
||
(SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = @columnname) > 0,
|
||
'SELECT 1',
|
||
CONCAT('ALTER TABLE ', @tablename, ' ADD COLUMN ', @columnname, ' DATETIME DEFAULT NULL COMMENT ''计划执行时间'' AFTER `end_date`')
|
||
));
|
||
PREPARE alterIfNotExists FROM @preparedStatement;
|
||
EXECUTE alterIfNotExists;
|
||
DEALLOCATE PREPARE alterIfNotExists;
|
||
|
||
-- =============================================
|
||
-- 二、更新字典数据
|
||
-- =============================================
|
||
|
||
-- 2.1 新增保养计划状态字典类型
|
||
INSERT IGNORE INTO `sys_dict_type` (`dict_name`, `dict_type`, `status`, `create_by`, `create_time`, `remark`)
|
||
VALUES ('保养计划状态', 'maintenance_plan_status', '0', 'admin', NOW(), '保养计划状态：待执行/已执行/已取消');
|
||
|
||
-- 2.2 保养计划状态字典数据
|
||
INSERT IGNORE INTO `sys_dict_data` (`dict_sort`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `status`, `create_by`, `create_time`, `remark`)
|
||
VALUES (0, '待执行', '0', 'maintenance_plan_status', NULL, 'warning', 'Y', '0', 'admin', NOW(), '待执行');
|
||
INSERT IGNORE INTO `sys_dict_data` (`dict_sort`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `status`, `create_by`, `create_time`, `remark`)
|
||
VALUES (1, '已执行', '1', 'maintenance_plan_status', NULL, 'success', 'N', '0', 'admin', NOW(), '已执行');
|
||
INSERT IGNORE INTO `sys_dict_data` (`dict_sort`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `status`, `create_by`, `create_time`, `remark`)
|
||
VALUES (2, '已取消', '2', 'maintenance_plan_status', NULL, 'info', 'N', '0', 'admin', NOW(), '已取消');
|
||
|
||
-- =============================================
|
||
-- 三、回退SQL
|
||
-- =============================================
|
||
|
||
/*
|
||
-- 删除字典数据
|
||
DELETE FROM `sys_dict_data` WHERE `dict_type` = 'maintenance_plan_status';
|
||
DELETE FROM `sys_dict_type` WHERE `dict_type` = 'maintenance_plan_status';
|
||
|
||
-- 删除扩展字段
|
||
ALTER TABLE `dm_inspection_plan` DROP COLUMN `planned_time`;
|
||
*/
|