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` bigint 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_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_approve_log'
                    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;


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);