soc/project_data/sql/0.0.7/soc/soc.sql

-- 新增 监控调阅计划与执行机构关系表
DROP TABLE IF EXISTS `core_monitoring_retrieval_plan_to_exec_org`;
CREATE TABLE `core_monitoring_retrieval_plan_to_exec_org`  (
                                                               `plan_id` bigint NOT NULL COMMENT '计划id',
                                                               `org_id` bigint NOT NULL COMMENT '机构id'
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '监控调阅计划与执行机构关系表' ROW_FORMAT = Dynamic;

DROP TABLE IF EXISTS `core_introduce_letter_out_in_request_user`;
CREATE TABLE `core_introduce_letter_out_in_request_user` (
                                                      `id` bigint NOT NULL,
                                                      `user_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
                                                      `company_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
                                                      `id_type` int DEFAULT NULL COMMENT '证件类型',
                                                      `id_card` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
                                                      `img_file` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
                                                      `out_in_request_id` bigint DEFAULT NULL,
                                                      `create_by` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
                                                      `create_time` datetime DEFAULT NULL,
                                                      PRIMARY KEY (`id`),
                                                      KEY `index_out_in_request_id` (`out_in_request_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='出入申请人员表';


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_extend'
                    AND column_name = 'hd_camera_count') THEN
        ALTER TABLE `sys_org_extend`
            ADD COLUMN `hd_camera_count` int NULL COMMENT '高清摄像头路数' AFTER `outside_count`,
            ADD COLUMN `total_camera_count` int NULL COMMENT '摄像头总路数' AFTER `hd_camera_count`;
    END IF;

	IF NOT EXISTS(SELECT *
                  FROM information_schema.columns
                  WHERE table_schema = DATABASE()
                    AND table_name = 'core_out_in_record'
                    AND column_name = 'status') THEN
        ALTER TABLE `core_out_in_record`
            DROP COLUMN `letter_id`,
            DROP COLUMN `letter_user_id`,
            ADD COLUMN `status` int NULL COMMENT '出入状态 ：0待审批 1 同意 2 不同意 3 已失效' AFTER `check_image`,
            ADD COLUMN `out_in_request_effective_start_time` datetime NULL COMMENT '出入申请生效开始时间' AFTER `pdf_url`,
            ADD COLUMN `out_in_request_effective_end_time` datetime NULL COMMENT '出入申请生效结束时间' AFTER `out_in_request_effective_start_time`,
            ADD COLUMN `out_in_request_id` bigint NULL COMMENT '出入申请Id' AFTER `out_in_request_effective_end_time`,
            ADD COLUMN `out_in_request_user_id` bigint NULL COMMENT '出入人员在出入申请中的人员ID' AFTER `out_in_request_id`,
            ADD COLUMN `submit_sign` varchar(255) NULL COMMENT '登记人签名' AFTER `out_in_request_user_id`,
            ADD COLUMN `update_by` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '修改人' AFTER `submit_sign`,
            ADD COLUMN `update_time` datetime(0) NULL DEFAULT NULL COMMENT '修改时间' AFTER `update_by`;
    END IF;

    IF EXISTS(SELECT *
              FROM information_schema.columns
              WHERE table_schema = DATABASE()
                AND table_name = 'core_out_in_record'
                AND column_name = 'submit_sign') THEN
        ALTER TABLE `core_out_in_record`
            MODIFY COLUMN `submit_sign` varchar(255) NULL DEFAULT NULL COMMENT '登记人签名' AFTER `out_in_request_user_id`;
    END IF;

    IF EXISTS(SELECT *
                  FROM information_schema.columns
                  WHERE table_schema = DATABASE()
                    AND table_name = 'core_introduce_letter_approve_log'
                    ) THEN

            ALTER TABLE core_introduce_letter_approve_log RENAME TO core_introduce_letter_out_in_request;
    END IF;

    IF NOT EXISTS(SELECT *
                  FROM information_schema.columns
                  WHERE table_schema = DATABASE()
                    AND table_name = 'core_introduce_letter_out_in_request'
                    AND column_name = 'letter_name') THEN
            ALTER TABLE `core_introduce_letter_out_in_request`
                ADD COLUMN `approve_sign_img` varchar(255) NULL COMMENT '审批人签名' AFTER `approve_remark`,
                ADD COLUMN `letter_name` varchar(255) NULL COMMENT '介绍信名称' AFTER `org_id`,
                ADD COLUMN `letter_file` varchar(500) NULL COMMENT '介绍信附件' AFTER `letter_name`,
                ADD COLUMN `letter_no` varchar(20) NULL COMMENT '介绍信编号' AFTER `letter_file`,
                ADD COLUMN `letter_description` varchar(500) NULL COMMENT '介绍信备注' AFTER `letter_no`,
                ADD COLUMN `letter_reasons` varchar(100) NULL COMMENT '出入事由' AFTER `letter_description`,
                ADD COLUMN `letter_type` int NULL COMMENT '类型：1 电子, 2 纸质，3 紧急' AFTER `letter_reasons`,
                ADD COLUMN `deleted` int NULL DEFAULT 0 COMMENT '0正常1删除' AFTER `letter_type`,
                ADD COLUMN `start_time` datetime(0) NULL DEFAULT NULL COMMENT '有效开始时间，开具时间' AFTER `deleted`,
                ADD COLUMN `end_time` datetime(0) NULL DEFAULT NULL COMMENT '有效结束时间' AFTER `start_time`,
                ADD COLUMN `effective_days` int NULL DEFAULT NULL COMMENT '有效天数' AFTER `end_time`,
                ADD COLUMN `create_by` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '创建人' AFTER `end_time`,
                ADD COLUMN `create_time` datetime(0) NULL DEFAULT NULL COMMENT '创建时间' AFTER `create_by`,
                ADD COLUMN `update_by` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '修改人' AFTER `create_time`,
                ADD COLUMN `update_time` datetime(0) NULL DEFAULT NULL COMMENT '修改时间' AFTER `update_by`,
                COMMENT = '出入申请表';
    END IF;

	-- 安全检查增加签名图片字段
    IF NOT EXISTS (
                SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema = DATABASE () AND TABLE_NAME = 'core_safety_task' AND COLUMN_NAME = 'sign_img' ) THEN
        alter table core_safety_task add sign_img varchar(125) null comment '签名图片';
    END IF;



    -- 监控调阅任务详情表有调阅设备更改为调阅区域，增加区域字段
    IF NOT EXISTS (
            SELECT * FROM INFORMATION_SCHEMA.COLUMNS
                     WHERE table_schema = DATABASE ()
                       AND TABLE_NAME = 'core_monitoring_task_registration_monitor'
                       AND COLUMN_NAME = 'area_code' )
        THEN
                alter table core_monitoring_task_registration_monitor
                    add area_code varchar(36) null comment '调阅区域,字典取值';

                alter table core_monitoring_task_registration_monitor
                    add area_name varchar(64) null comment '调阅区域名称';
    END IF;


    -- 监控调阅任务表增加pdfUrl字段
    IF NOT EXISTS (
            SELECT * FROM INFORMATION_SCHEMA.COLUMNS
            WHERE table_schema = DATABASE ()
              AND TABLE_NAME = 'core_monitoring_retrieval_task'
              AND COLUMN_NAME = 'pdf_url' )
    THEN
        alter table core_monitoring_retrieval_task
            add pdf_url varchar(125) null comment '登记簿url';
    END IF;

    -- 监控调阅任务表增加签名图片字段
    IF NOT EXISTS (
            SELECT * FROM INFORMATION_SCHEMA.COLUMNS
            WHERE table_schema = DATABASE ()
              AND TABLE_NAME = 'core_monitoring_retrieval_task'
              AND COLUMN_NAME = 'sign_img' )
    THEN
        alter table core_monitoring_retrieval_task
            add sign_img varchar(125) null comment '签名图片地址';
    END IF;




END ??
DELIMITER ;
CALL schema_change ();

-- 清空 来访申请和 来访记录数据
delete from core_introduce_letter_out_in_request;
delete from core_out_in_record;

delete from sys_dict_type where dict_type = 'video_retrieval_area';
delete from sys_dict_data where dict_type = 'video_retrieval_area';
INSERT INTO sys_dict_type (dict_name, dict_type, status, create_by, create_time, update_by, update_time, remark) VALUES ('监控调阅区域', 'video_retrieval_area', '0', '超级管理员', '2024-03-20 18:18:26', '', 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', 'video_retrieval_area', null, 'default', 'N', '0', '超级管理员', '2024-03-20 18:20:59', '超级管理员', '2024-03-20 18:22:46', 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', 'video_retrieval_area', null, 'default', 'N', '0', '超级管理员', '2024-03-20 18:21:17', '超级管理员', '2024-03-20 18:22:55', 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, '非现金业务区', '2', 'video_retrieval_area', null, 'default', 'N', '0', '超级管理员', '2024-03-20 18:23:26', '', 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', 'video_retrieval_area', null, 'default', 'N', '0', '超级管理员', '2024-03-20 18:23:39', '', 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 (4, '办公区', '4', 'video_retrieval_area', null, 'default', 'N', '0', '超级管理员', '2024-03-20 18:23:54', '', 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 (5, '设备间', '5', 'video_retrieval_area', null, 'default', 'N', '0', '超级管理员', '2024-03-20 18:24:03', '', 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 (6, '自助银行', '6', 'video_retrieval_area', null, 'default', 'N', '0', '超级管理员', '2024-03-20 18:24:22', '', 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 (7, '加钞间', '7', 'video_retrieval_area', null, 'default', 'N', '0', '超级管理员', '2024-03-20 18:24:39', '', 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 (8, '业务库', '8', 'video_retrieval_area', null, 'default', 'N', '0', '超级管理员', '2024-03-20 18:24:56', '', 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 (9, '保管箱库', '9', 'video_retrieval_area', null, 'default', 'N', '0', '超级管理员', '2024-03-20 18:25:08', '', null, null);

DELETE from `sys_menu` where id in(1771078052690612225);
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 (1771078052690612225, '空调管理', 1749608698379771905, 17, '/iot/airconditioner', NULL, NULL, 1, 0, 'C', '0', '0', 'appiot:airconditioner', '0', 'data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAEAAAABACAYAAACqaXHeAAAFJ0lEQVR4nO2baWhdRRiGn6SJSdpGU2NItS5BcRetBtwQRdywdQEriv4RFH+IuyJorVpxRcwfFatFRPRHsdhFhYIoWLGKFHFBpbQqKopatJTUtLa2916Z8t5ycpg559wzc+5C7guXhDlzZuZ77zfffMtc2mijjSmNjrjwC+79y5ePC4C7gHlAp9o2A0uAR5uB7BVjQ/v+70zsWRtOBN4EPgAui409DCwGXitGpPwIQYAR7jngW+CalL43ADcWKVCt8CGgF3gA+B64vYb3ngH6woqRH3kJuAnYBDwJ9Dv6lIDnpRlRDAJz6yhjImolYD6wHngFOCyh3zLgOOAO4EXL80MCrd8bXRkHOFxWfF5Kvw+BR4CPI20VS79pdZIvFVkIOAVYCwwk9NkkK7/M8mw/S1s50Pq9kUbAQcBHwAGO51uAx3UKhBTqaOBqbbOyNOttYE/AOfYijYCFDuGNWo/JCG4NvKZF0qboNrkV+Bl4AXgZmAg1WZoRvNTSZtT8WOC+goR/zGEjRoBndew+BRwVYsIkAgZk/KJYB1yvRYTGkRI+DbOB+4GNwFLgpCIJiBuwXwoQvIpaPUSjJTcD3wCrgYvzTJpEwB45M1H05pkkI+LOkTF+VwBXAV+kDHEl8J4Mdpo7PgkhgyFfxD1KY1/eBVYBoyLi/ZQ5zlVA9llWb7OZCIgfo+UYKauk5ucAy1PGOgP4Ejg1bdJmIiDuMXbY8hXAJ8C1wMnyP7YnjPlW2qS+BFwOfCq2P9ffBz3HzApj/O4EjpEz9qflPXOyXJQ0ni8BtwBnab+N6m+9CKjid+Ah+QVLLM/PT3rZl4BxS9tmzzHzYgfwsOXdOUnj+RIwy9I221uU/Ni/1jezhsMuLNU+65PPYIzWGwUKOEsB2m6t/Y+YEey2vJMYpPkSsFLHU09kov88x3RhVM7OoJw04wn+BpwpO5ALIY5Bc3ztlOBFCW9wmoRHX1yHwuXjfQZtJj8gDbscz71yBK1EgEu7dvgM6msDZgCXKEgqazuYpOlPnuPasEZZ5hEJbQzvd3K+csOXgKeB22JtG4ATgog8GduUZQ4K3y1wqKXtwNCLLBK+BNhS3k2T8c0CXwJsbq+XUao3fG3AQhmhqAe2tiAZpgP3xFztH2QY45mrzPAlYKvc4XpgviNpuk6heC60kh8w3dE+w2fQViKgx9HuVWdsJQK+Av6NtZnS3I8+g/ragAu1L/vlkxtCX1cFJzTWqyI1LKPXqXLZlkYScLfC0SgWF0SAwa/6BIPvFthmafs75AKLRhEpseHmE9MN3y3wqgqofREXeGVRi1VCZFihcbdqlQ0Nh5fr0xWJC3J7ZSmYqzuIg5qrQ0bwbOUGcyHUMVgtpBYlvMHpkZRYtWI0MpVSYjsd7VMmJbbb0e6ViPW1AfXEGhVD50gbenU77eupQsC4iqFB0UpboBC0CWiCNTQUSQSMW5KerqMoCbYS+j+WtriVLztijSTYfu7iqijtRZIRPNhCkHFEjqhBc0qqHsdh8noz5c5WJGz8NwRdugOYdAUmiormqsSu1vSrbN5tC51tvxkynV9SrD8Ufx6JxbOg5CB5Qj58lYCS7iVGk6uViAba7grZCMByt2iXNKlbofSiFWND71Qf2hZnYvnrEiaqJQXl0rCZ+iTBdUnKBVffnsgXOaBL1/v62r7J82qYtOVhI6Beae5GYVJGyaaiY7LSC6Q6JUcJLA+qaj2hE6X6BVRkrHoCR5TTNM922QCTQH2ikey30UYzAfgfc8rrKphAjyUAAAAASUVORK5CYII=', NULL, '超级管理员', '2024-03-22 15:34:47', '', NULL, '');

DELETE from sys_role_menu WHERE menu_id in (1771078052690612225);
INSERT into sys_role_menu SELECT id ,1771078052690612225 from sys_role WHERE id in(116,118,120,121,122,128,143,144,145,146,151);
-- 修正字典数据
delete from sys_dict_data where dict_type='org_extend_is';
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', 'org_extend_is', null, 'default', 'N', '0', '超级管理员', '2023-09-15 09:48:58', '省联社账号', '2023-10-16 14:24:52', 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, '区域外', '0', 'org_extend_is', null, 'default', 'N', '0', '超级管理员', '2023-09-15 09:49:08', '省联社账号', '2023-10-16 14:24:59', null);