DELIMITER ??
DROP PROCEDURE IF EXISTS schema_change ??
CREATE PROCEDURE schema_change()
BEGIN


-- 培训任务表增加主持人名称字段
    IF
    NOT EXISTS(SELECT *
                      FROM information_schema.columns
                      WHERE table_schema = DATABASE()
                        AND table_name = 'core_monitoring_task_registration'
                        AND column_name = 'task_end_time') THEN
    alter table core_monitoring_task_registration
        add task_end_time datetime DEFAULT NULL COMMENT '调阅任务填报结束时间' after end_picture;
    END IF;

    IF
    NOT EXISTS(SELECT *
                      FROM information_schema.columns
                      WHERE table_schema = DATABASE()
                        AND table_name = 'core_monitoring_retrieval_task'
                        AND column_name = 'duration_total') THEN
    alter table core_monitoring_retrieval_task
        add duration_total double(15,4) DEFAULT NULL COMMENT '调阅总时长' after sign_img;
    END IF;

    IF
    NOT EXISTS(SELECT *
                      FROM information_schema.columns
                      WHERE table_schema = DATABASE()
                        AND table_name = 'sys_org_extend'
                        AND column_name = 'alone_dept') THEN
    alter table sys_org_extend
        add alone_dept varchar(1) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '是否独立机构' after remote_control_count;
    END IF;

    -- 修改介绍信附件字段长度
    alter table core_introduce_letter
        modify letter_file text null comment '介绍信文件';

    alter table core_introduce_letter_out_in_request
        modify letter_file text null comment '介绍信附件';

    alter table core_introduce_letter_out_in_request_user
        modify img_file varchar(500) null;

    alter table core_introduce_letter_user
        modify img_file varchar(500) null;




END ??
DELIMITER ;
CALL schema_change();


UPDATE `quartz`.`sys_job` SET  `status` = '1' where  `job_name` = '回归未完成的调阅任务';
DELETE FROM quartz.sys_job where job_name = '每日定时清理未完成的调阅任务记录';
INSERT INTO `quartz`.`sys_job`( `job_name`, `job_group`, `invoke_target`, `cron_expression`, `misfire_policy`, `concurrent`, `status`, `create_by`, `create_time`, `update_by`, `update_time`) VALUES ('每日定时清理未完成的调阅任务记录', 'DEFAULT', 'CoreMessageSendTask.dealTaskRegistration', '0 0 0 * * ? *', '1', '1', '0', '超级管理员', '2024-07-25 13:45:18', '', NULL);

-- 修改盗情为防盗
UPDATE iot_alarm_system_field SET source_type_des = '防盗' , `name` = '防盗告警' WHERE `source_type`='41881';
UPDATE sys_dict_data SET dict_label = '防盗告警'  WHERE dict_type = 'sms_notify_type' AND dict_value = '41881';
UPDATE sys_dict_data SET dict_label = '防盗'  WHERE dict_type = 'app_sensor_device_type' AND dict_value = '41881';

UPDATE iot_alarm_data SET source_type_des = REPLACE ( source_type_des, '盗情', '防盗' ),content = REPLACE ( content, '盗情', '防盗' ) WHERE source_type = '41881';
update iot_sensor_log set infos = REPLACE ( infos, '被盗', '防盗' ) WHERE device_type = '41881';
-- 处理已失效的介绍信无法登记离开
update core_out_in_record set status=1 where arrival_time is not null  and departure_time is null  and status=3;
-- 处理通知公告排序
update core_announcement_notification set update_time=create_time,update_by=create_by where update_time is null;