soc/project_data/sql/0.0.6/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 = 'affiliated_area' )
	THEN
    ALTER TABLE `sys_org`
	ADD COLUMN `affiliated_area` varchar(100) NULL COMMENT '机构所属地区' AFTER `tree_parent_code`,
	ADD COLUMN `affiliated_bank` varchar(100) NULL COMMENT '机构所属行社' AFTER `affiliated_area`;

	END IF;

	 IF NOT EXISTS ( SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema = DATABASE ()
		AND TABLE_NAME = 'sys_org' AND COLUMN_NAME = 'affiliated_area' )
	THEN
		ALTER TABLE `sync_fjnx_org`
		ADD COLUMN `affiliated_area` varchar(100) NULL COMMENT '机构所属地区',
		ADD COLUMN `affiliated_bank` varchar(100) NULL COMMENT '机构所属行社' AFTER `affiliated_area`;

	END IF;

END ??
DELIMITER ;
CALL schema_change ();



drop table if exists mediator_video_days_check;
CREATE TABLE `mediator_video_days_check` (
     `id` bigint NOT NULL,
     `create_by` varchar(125) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '创建人',
     `create_time` datetime DEFAULT NULL COMMENT '创建人名称',
     `update_by` varchar(125) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin DEFAULT NULL COMMENT '修改人名称',
     `update_time` datetime DEFAULT NULL COMMENT '修改时间',
     `org_id` bigint DEFAULT '0' COMMENT '机构',
     `org_name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '机构名称',
     `org_path` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '0' COMMENT '机构',
     `channel_code` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '通道序号',
     `channel_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '通道的名称',
     `check_date` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT '计划存储天数起止日期',
     `equipment_code` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '录像机唯一标识',
     `lose_date` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT '计划存储天数起止日期',
     `plan_days` int DEFAULT '0' COMMENT '计划存储总天数',
     `real_days` int DEFAULT '0' COMMENT '计划日期内实际存储总天数',
     `equipment_name` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
     `status` int DEFAULT NULL COMMENT '是否报警 0:未报警,1:报警',
     `earliest_video_time` datetime DEFAULT NULL COMMENT '最早录像日期',
     PRIMARY KEY (`id`) USING BTREE,
     KEY `idx_user_orgId` (`org_id`) USING BTREE,
     KEY `idx_user_orgPath` (`org_path`) USING BTREE,
     KEY `idx_code` (`equipment_code`(191),`channel_code`(191))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='录像机天数检查';

drop table if exists mediator_video_days_check_log;
CREATE TABLE `mediator_video_days_check_log` (
         `id` bigint NOT NULL,
         `create_by` varchar(125) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '创建人',
         `create_time` datetime DEFAULT NULL COMMENT '创建人名称',
         `update_by` varchar(125) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin DEFAULT NULL COMMENT '修改人名称',
         `update_time` datetime DEFAULT NULL COMMENT '修改时间',
         `org_id` bigint DEFAULT '0' COMMENT '机构',
         `org_name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '机构名称',
         `org_path` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '0' COMMENT '机构',
         `channel_code` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '通道序号',
         `channel_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '通道的名称',
         `check_date` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT '计划存储天数起止日期',
         `equipment_code` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '录像机唯一标识',
         `lose_date` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT '计划存储天数起止日期',
         `plan_days` int DEFAULT '0' COMMENT '计划存储总天数',
         `real_days` int DEFAULT '0' COMMENT '计划日期内实际存储总天数',
         `video_days_check_id` bigint DEFAULT NULL COMMENT '录像机天数检查ID',
         `status` int DEFAULT NULL,
         `earliest_video_time` datetime DEFAULT NULL COMMENT '最早录像日期',
         `equipment_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
         PRIMARY KEY (`id`) USING BTREE,
         KEY `idx_user_orgId` (`org_id`) USING BTREE,
         KEY `idx_user_orgPath` (`org_path`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='录像机天数检查日志';

drop table if exists mediator_video_integrity_check;
CREATE TABLE `mediator_video_integrity_check` (
          `id` bigint NOT NULL,
          `create_by` varchar(125) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '创建人',
          `create_time` datetime DEFAULT NULL COMMENT '创建人名称',
          `update_by` varchar(125) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin DEFAULT NULL COMMENT '修改人名称',
          `update_time` datetime DEFAULT NULL COMMENT '修改时间',
          `org_id` bigint DEFAULT '0' COMMENT '机构',
          `org_name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '机构名称',
          `org_path` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '0' COMMENT '机构',
          `channel_code` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '通道序号',
          `channel_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '通道的名称',
          `check_span` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT '检查日期录像计划规则集合',
          `equipment_code` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '录像机唯一标识',
          `lose_span` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT '检查日期录像丢失情况集合',
          `record_date` date DEFAULT NULL COMMENT '录像情况检查日期',
          `device_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '设备名称',
          `status` int DEFAULT NULL COMMENT '0:正常 1:部分丢失 2:全部丢失',
          `equipment_name` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
          `plan_days` int DEFAULT NULL COMMENT '录像计划存储多少天',
          `lose_span_old` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT '录像机唯一标识',
          PRIMARY KEY (`id`) USING BTREE,
          KEY `idx_user_orgId` (`org_id`) USING BTREE,
          KEY `idx_user_orgPath` (`org_path`) USING BTREE,
          KEY `idx_code` (`channel_code`(191),`equipment_code`(191)),
          KEY `idx_recordDate_orgPath_status` (`record_date`,`org_path`,`status`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='录像机完整性检查表';


drop table if exists mediator_video_diagnosis;
CREATE TABLE `mediator_video_diagnosis` (
    `id` bigint NOT NULL,
    `create_by` varchar(125) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '创建人',
    `create_time` datetime DEFAULT NULL COMMENT '创建人名称',
    `update_by` varchar(125) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin DEFAULT NULL COMMENT '修改人名称',
    `update_time` datetime DEFAULT NULL COMMENT '修改时间',
    `org_id` bigint DEFAULT '0' COMMENT '机构',
    `org_name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '机构名称',
    `org_path` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '0' COMMENT '机构',
    `alarm_time` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '报警/恢复时间',
    `channel_code` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '通道序号',
    `channel_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '通道的名称',
    `detail_info` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT '质量当前状态详情',
    `equipment_code` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '录像机唯一标识',
    `is_alarm` int DEFAULT NULL COMMENT '报警状态标识',
    `equipment_name` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
    PRIMARY KEY (`id`) USING BTREE,
    KEY `idx_user_orgId` (`org_id`) USING BTREE,
    KEY `idx_user_orgPath` (`org_path`) USING BTREE,
    KEY `idx_code` (`channel_code`(191),`equipment_code`(191))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='视频质量诊断结果';

drop table if exists mediator_video_diagnosis_log;
CREATE TABLE `mediator_video_diagnosis_log` (
        `id` bigint NOT NULL,
        `create_by` varchar(125) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '创建人',
        `create_time` datetime DEFAULT NULL COMMENT '创建人名称',
        `update_by` varchar(125) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin DEFAULT NULL COMMENT '修改人名称',
        `update_time` datetime DEFAULT NULL COMMENT '修改时间',
        `org_id` bigint DEFAULT '0' COMMENT '机构',
        `org_name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '机构名称',
        `org_path` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '0' COMMENT '机构',
        `alarm_time` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '报警/恢复时间',
        `channel_code` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '通道序号',
        `channel_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '通道的名称',
        `detail_info` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT '质量当前状态详情',
        `equipment_code` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '录像机唯一标识',
        `is_alarm` int DEFAULT NULL COMMENT '报警状态标识',
        `video_diagnosis_id` bigint DEFAULT NULL COMMENT '视频质量诊断结果ID',
        `equipment_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
        PRIMARY KEY (`id`) USING BTREE,
        KEY `idx_user_orgId` (`org_id`) USING BTREE,
        KEY `idx_user_orgPath` (`org_path`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='视频质量诊断结果';

drop table if exists mediator_video_diagnosis_record;
CREATE TABLE `mediator_video_diagnosis_record` (
           `id` bigint NOT NULL,
           `org_id` bigint NOT NULL COMMENT '机构id',
           `org_name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '机构名称',
           `org_path` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '0' COMMENT '机构',
           `video_diagnosis_id` bigint DEFAULT NULL COMMENT '视频质量诊断id',
           `create_by` varchar(125) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '创建人',
           `create_time` datetime DEFAULT NULL COMMENT '创建人名称',
           `update_by` varchar(125) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin DEFAULT NULL COMMENT '修改人名称',
           `update_time` datetime DEFAULT NULL COMMENT '修改时间',
           `equipment_code` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '设备id',
           `channel_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '通道名称',
           `channel_code` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '通道序号',
           `device_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '设备名称',
           `device_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '设备id',
           `signal_lost` int NOT NULL DEFAULT '0' COMMENT '信号丢失  0: 正常 | 1：异常',
           `occlude` int NOT NULL DEFAULT '0' COMMENT '遮挡  0: 正常 | 1：异常',
           `brightness` int NOT NULL DEFAULT '0' COMMENT '亮度  0: 正常 | 1：异常',
           `color_cast` int NOT NULL DEFAULT '0' COMMENT '偏色  0: 正常 | 1：异常',
           `snowflake` int NOT NULL DEFAULT '0' COMMENT '雪花  0: 正常 | 1：异常',
           `stripe` int NOT NULL DEFAULT '0' COMMENT '条纹  0: 正常 | 1：异常',
           `contrast` int NOT NULL DEFAULT '0' COMMENT '对比度  0: 正常 | 1：异常',
           `blurry` int NOT NULL DEFAULT '0' COMMENT '模糊  0: 正常 | 1：异常',
           `equipment_name` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
           `alarm_time` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '报警/恢复时间',
           `is_alarm` int DEFAULT NULL COMMENT '报警状态标识',
           `detail_info` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT '质量当前状态详情',
           PRIMARY KEY (`id`),
           KEY `idx_videodiagnosisrecord_updatetime` (`update_time`),
           KEY `idx_code` (`equipment_code`(191),`channel_code`(191))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;