soc/project_data/sql/0.0.5/soc/soc.sql

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 = 'sys_org' AND COLUMN_NAME = 'breviary' ) THEN
ALTER TABLE `sys_org` ADD COLUMN `breviary` VARCHAR ( 255 ) NULL COMMENT '机构缩略词';

END IF;

	IF
        NOT EXISTS ( SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema = DATABASE () AND TABLE_NAME = 'core_safety_task' AND COLUMN_NAME = 'solid_id' ) THEN
        ALTER TABLE  `core_safety_task`
					ADD COLUMN `solid_id` bigint NULL COMMENT '固定的id,用于识别多个副本是否属于同一个计划' AFTER `plan_id`;
    END IF;
	IF
       NOT EXISTS ( SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema = DATABASE () AND TABLE_NAME = 'core_safecheck_plan' AND COLUMN_NAME = 'publish_time' ) THEN
        	ALTER TABLE `core_safecheck_plan`
				MODIFY COLUMN `parent_id` bigint NULL DEFAULT -1 COMMENT '下发父计划id' AFTER `id`,
				ADD COLUMN `solid_id` bigint NULL COMMENT '固定的id,用于识别多个副本是否属于同一个计划' AFTER `parent_id`,
				ADD COLUMN `parent_solid_id` bigint NULL COMMENT '固定的parentid,用于识别多个副本是否属于同一个计划' AFTER `solid_id`,
				ADD COLUMN `task_has_completed` int DEFAULT NULL COMMENT '1：表示有已完成的任务',
				ADD COLUMN `publish_time` datetime NULL COMMENT '最后一次下发时间' AFTER `task_has_completed`,
				ADD COLUMN `withdraw_time` datetime NULL COMMENT '最后一次撤回时间' AFTER `publish_time`,
				ADD COLUMN `effective_time` datetime NULL COMMENT '计划生效时间' AFTER `withdraw_time`;
				update core_safecheck_plan set publish_time =create_time WHERE publish_time is null and plan_status=1;
				update core_safecheck_plan set withdraw_time =create_time WHERE withdraw_time is null and plan_status=2;
				update core_safecheck_plan set effective_time =create_time WHERE effective_time is null and (plan_status=1 or plan_status=2);
    END IF;


--             -- 预案演练表增加字段:最后一次下发时间/最后一次撤回时间/计划生效日期
            IF NOT EXISTS(SELECT *
                              FROM information_schema.columns
                              WHERE table_schema = DATABASE()
                                AND table_name = 'core_drill_plan'
                                AND column_name = 'publish_time') THEN
                    ALTER TABLE core_drill_plan
                        ADD COLUMN `publish_time` datetime NULL COMMENT '最后一次下发时间' AFTER `parent_id`,
                        ADD COLUMN `withdraw_time` datetime NULL COMMENT '最后一次撤回时间' AFTER `publish_time`,
                        ADD COLUMN `effective_time` datetime NULL COMMENT '计划生效日期' AFTER `withdraw_time`;
                    update core_drill_plan set publish_time =update_time WHERE publish_time is null and plan_status=1;
                    update core_drill_plan set withdraw_time =update_time WHERE withdraw_time is null and plan_status=2;
                    update core_drill_plan set effective_time =update_time WHERE effective_time is null and (plan_status=1 or plan_status=2);
            END IF;
-- 
--                 -- 预案演练表增加字段:最后一次下发时间/最后一次撤回时间/计划生效日期
            IF NOT EXISTS(SELECT *
                              FROM information_schema.columns
                              WHERE table_schema = DATABASE()
                                AND table_name = 'core_edu_training_plan'
                                AND column_name = 'publish_time') THEN
                    ALTER TABLE core_edu_training_plan
                        ADD COLUMN `publish_time` datetime NULL COMMENT '最后一次下发时间' AFTER `parent_id`,
                        ADD COLUMN `withdraw_time` datetime NULL COMMENT '最后一次撤回时间' AFTER `publish_time`,
                        ADD COLUMN `effective_time` datetime NULL COMMENT '计划生效日期' AFTER `withdraw_time`;
                    update core_edu_training_plan set publish_time =update_time WHERE publish_time is null and plan_status=1;
                    update core_edu_training_plan set withdraw_time =update_time WHERE withdraw_time is null and plan_status=2;
                    update core_edu_training_plan set effective_time =update_time WHERE effective_time is null and (plan_status=1 or plan_status=2);
            END IF;


IF
        NOT EXISTS ( SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema = DATABASE () AND TABLE_NAME = 'core_resumption_plan' AND COLUMN_NAME = 'solid_id' ) THEN
        ALTER TABLE  `core_resumption_plan` 
			MODIFY COLUMN `parent_id` bigint NULL DEFAULT -1 COMMENT '下发父计划id' AFTER `id`,
			ADD COLUMN `solid_id` bigint NULL COMMENT '固定的id,用于识别多个副本是否属于同一个计划' AFTER `parent_id`,
			ADD COLUMN `parent_solid_id` bigint NULL COMMENT '固定的parentid,用于识别多个副本是否属于同一个计划' AFTER `solid_id`;
    END IF;
	IF
        NOT EXISTS ( SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema = DATABASE () AND TABLE_NAME = 'core_resumption' AND COLUMN_NAME = 'solid_id' ) THEN
        ALTER TABLE  `core_resumption` 
			ADD COLUMN `solid_id` bigint NULL COMMENT '固定的id,用于识别多个副本是否属于同一个计划' AFTER `plan_id`,
			ADD COLUMN `parent_solid_id` bigint NULL COMMENT '固定的parentid,用于识别多个副本是否属于同一个计划' AFTER `solid_id`;
    END IF;
	IF
        EXISTS ( SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema = DATABASE () AND TABLE_NAME = 'core_resumption_plan_to_point' AND COLUMN_NAME = 'effective_date' ) THEN
        ALTER TABLE `core_resumption_plan_to_point` 
			DROP COLUMN `effective_date`;
    END IF;
	
	IF
       NOT EXISTS ( SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema = DATABASE () AND TABLE_NAME = 'core_resumption_plan' AND COLUMN_NAME = 'publish_time' ) THEN
        	ALTER TABLE `core_resumption_plan` 
				ADD COLUMN `publish_time` datetime NULL COMMENT '最后一次下发时间' AFTER `task_has_completed`,
				ADD COLUMN `withdraw_time` datetime NULL COMMENT '最后一次撤回时间' AFTER `publish_time`,
				ADD COLUMN `effective_time` datetime NULL COMMENT '计划生效时间' AFTER `withdraw_time`;
				update core_resumption_plan set publish_time =update_time WHERE publish_time is null and plan_status=1;
				update core_resumption_plan set withdraw_time =update_time WHERE withdraw_time is null and plan_status=2;
				update core_resumption_plan set effective_time =update_time WHERE effective_time is null and plan_status=1;
    END IF;

END ??
DELIMITER;
CALL schema_change ();

update sys_menu set component='safetycheck/plan/index' WHERE menu_name='检查任务下发' and menu_type='C' and platform_type='1';
delete from sys_menu where id=1734903034555719682;
INSERT INTO sys_menu (id, menu_name, parent_id, order_num, path, component, query, is_frame, is_cache, menu_type, visible, status, perms, platform_type, icon, image_path, create_by, create_time, update_by, update_time, remark)
VALUES (1734903034555719682, '导出安全保卫工作情况统计', 103, 8, '', null, null, 1, 0, 'F', '0', '0', 'system:dept:exportConstructionDetails', '1', null, null, null, now(), '', null, '');
delete from sys_role_menu where menu_id =1734903034555719682;
INSERT INTO `sys_role_menu` (`role_id`, `menu_id`) VALUES (114, 1734903034555719682);
INSERT INTO `sys_role_menu` (`role_id`, `menu_id`) VALUES (115, 1734903034555719682);
INSERT INTO `sys_role_menu` (`role_id`, `menu_id`) VALUES (116, 1734903034555719682);
INSERT INTO `sys_role_menu` (`role_id`, `menu_id`) VALUES (117, 1734903034555719682);
INSERT INTO `sys_role_menu` (`role_id`, `menu_id`) VALUES (118, 1734903034555719682);
INSERT INTO `sys_role_menu` (`role_id`, `menu_id`) VALUES (119, 1734903034555719682);
INSERT INTO `sys_role_menu` (`role_id`, `menu_id`) VALUES (120, 1734903034555719682);
delete  from sys_role_menu where menu_id in (select id from  sys_menu where perms='core:eduTask:report' and menu_name='报表查看');
delete  from sys_menu where perms='core:eduTask:report' and menu_name='报表查看';
delete  from sys_dict_type where  dict_type='monitor_org';
INSERT INTO sys_dict_type (dict_name, dict_type, status, create_by, create_time, update_by, update_time, remark)
VALUES ('调阅机构类型', 'monitor_org', '0', null, now(), null, now(), null);
delete  from sys_dict_data where dict_type='monitor_org';
INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, update_by, update_time, remark)
VALUES (3, '行社', '3', 'monitor_org', null, 'default', 'N', '0',  null, now(), null, now(), null);
INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, update_by, update_time, remark)
VALUES (2, '营业网点', '4', 'monitor_org', null, 'default', 'N', '0',  null, now(), null, now(), null);
INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, update_by, update_time, remark)
VALUES (5, '离行式自助银行', '5', 'monitor_org', null, 'default', 'N', '0',  null, now(), null, now(), null);
INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, update_by, update_time, remark)
VALUES (6, '中心业务库', '6', 'monitor_org', null, 'default', 'N', '0',  null, now(), null, now(), null);
INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, update_by, update_time, remark)
VALUES (10, '监控中心', '10', 'monitor_org', null, 'default', 'N', '0',  null, now(), null, now(), null);
delete  from `sys_menu` where  id=01719892877586239490;
INSERT INTO `sys_menu` (`id`, `menu_name`, `parent_id`, `order_num`, `path`, `component`, `query`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `platform_type`, `icon`, `image_path`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`)
VALUES (01719892877586239490, '外包履职评价统计表', 1719287834364436482, 2, 'evaluateTable', 'evaluateTable/index', NULL, 1, 1, 'C', '0', '0', 'core:evaluateTask:statistics', '1', 'chart', NULL, '超级管理员', '2023-09-21 10:37:52', '超级管理员', '2023-09-22 09:42:43', '');


UPDATE sys_menu set menu_type='C',perms='appquestion:list' WHERE platform_type=0 and menu_name='隐患问题清单';
delete from sys_dict_data where dict_type='register_book_type' and dict_value='28';
INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, update_by, update_time, remark)
 VALUES (28, '监控调阅登记簿', '28', 'register_book_type', null, 'default', 'N', '0', null, now(), null, now(), null);

DELETE from `sys_dict_type`  WHERE `dict_type`='app_question_status';
DELETE from `sys_dict_data`  WHERE `dict_type`='app_question_status';

INSERT INTO `sys_dict_type` ( `dict_name`, `dict_type`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES ( 'APP隐患问题状态', 'app_question_status', '0', '超级管理员', '2023-12-19 15:40:55', '', NULL, NULL);
INSERT INTO `sys_dict_data` ( `dict_sort`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES ( 0, '待确认', '0', 'app_question_status', NULL, 'default', 'N', '0', '超级管理员', '2023-12-19 15:42:47', '', NULL, NULL);
INSERT INTO `sys_dict_data` ( `dict_sort`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES ( 1, '提出异议', '1', 'app_question_status', NULL, 'default', 'N', '0', '超级管理员', '2023-12-19 15:42:59', '', NULL, NULL);
INSERT INTO `sys_dict_data` ( `dict_sort`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES ( 3, '已关闭', '3', 'app_question_status', NULL, 'default', 'N', '0', '超级管理员', '2023-12-19 15:43:23', '超级管理员', '2023-12-19 15:43:30', NULL);
INSERT INTO `sys_dict_data` ( `dict_sort`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES ( 10, '待整改', '10', 'app_question_status', NULL, 'default', 'N', '0', '超级管理员', '2023-12-19 15:43:55', '', NULL, NULL);
INSERT INTO `sys_dict_data` ( `dict_sort`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES ( 11, '已整改', '11', 'app_question_status', NULL, 'default', 'N', '0', '超级管理员', '2023-12-19 15:44:07', '超级管理员', '2023-12-19 15:44:31', NULL);
INSERT INTO `sys_dict_data` ( `dict_sort`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES ( 12, '已逾期', '12', 'app_question_status', NULL, 'default', 'N', '0', '超级管理员', '2023-12-19 15:44:18', '', NULL, NULL);

-- 新增安全责任书签署层级字典
DELETE from sys_dict_data where dict_type='sign_level' and dict_label='其他' limit 1;
INSERT INTO  `sys_dict_data`( `dict_sort`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (0, '其他', '3', 'sign_level', NULL, 'default', 'N', '0', null, NOW(), null
                                                                                                                                                                                                                , NOW(), NULL);
-- 增加演练范本详情菜单权限
set @id =  ( SELECT id FROM `sys_menu` where menu_name='演练范本详情' and perms='core:drillDictionary:query');
DELETE from sys_role_menu where menu_id=@id;
insert into sys_role_menu select DISTINCT id,@id from sys_role;