-- 2024-10-24
INSERT INTO `cf_enrol_param`(`PARAM_TYPE`, `PARAM_NAME`, `PARAM_VALUE`, `PARAM_NOTE`) VALUES ('Enrol', 'TencentLiveInfoType', '0', '活体识别获取结果类型');
INSERT INTO `cf_enrol_param`(`PARAM_TYPE`, `PARAM_NAME`, `PARAM_VALUE`, `PARAM_NOTE`) VALUES ('Enrol', 'TencentLiveRedirectUrl', 'https://10047.qmth.com.cn/bk/wechat/wechat.jsp', '跳转地址');
INSERT INTO `cf_enrol_param`(`PARAM_TYPE`, `PARAM_NAME`, `PARAM_VALUE`, `PARAM_NOTE`) VALUES ('Enrol', 'TencentLiveRuleId', '1', '活体识别业务唯一标识');
INSERT INTO `cf_enrol_param`(`PARAM_TYPE`, `PARAM_NAME`, `PARAM_VALUE`, `PARAM_NOTE`) VALUES ('Enrol', 'TencentLiveSecretId', 'AKIDEJCwMdidMr8C7pYFpDTbcgYSSXP8fyAB', '活体识别ID');
INSERT INTO `cf_enrol_param`(`PARAM_TYPE`, `PARAM_NAME`, `PARAM_VALUE`, `PARAM_NOTE`) VALUES ('Enrol', 'TencentLiveSecretKey', 'ymTyLWcXEhzBD6rfbSDoIZhjkgeV9f4J', '活体识别秘钥');


CREATE TABLE `tx_liveness_detail`
(
    `id`               int(0)                                                  NOT NULL AUTO_INCREMENT COMMENT '主键',
    `biz_token`        varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'token',
    `req_time`         bigint(0)                                               NULL DEFAULT NULL COMMENT '请求时间',
    `seq`              varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '请求的唯一标记',
    `id_card`          varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci  NULL DEFAULT NULL COMMENT '身份证号',
    `name`             varchar(80) CHARACTER SET utf8 COLLATE utf8_general_ci  NULL DEFAULT NULL COMMENT '姓名',
    `sim`              varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci  NULL DEFAULT NULL COMMENT '相似度',
    `need_charge`      bit(1)                                                  NULL DEFAULT NULL COMMENT '是否需要收费0：不收费，1：收费',
    `error_code`       int(0)                                                  NULL DEFAULT NULL COMMENT '本次活体一比一最终结果',
    `error_msg`        varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '本次活体一比一最终结果描述',
    `live_status`      int(0)                                                  NULL DEFAULT NULL COMMENT '本次活体结果',
    `live_msg`         varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '本次活体结果描述',
    `compare_status`   int(0)                                                  NULL DEFAULT NULL COMMENT '本次一比一结果',
    `compare_msg`      varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci  NULL DEFAULT NULL COMMENT '本次一比一结果描述',
    `compare_lib_type` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci  NULL DEFAULT NULL COMMENT '比对库源类型',
    `liveness_mode`    int(0)                                                  NULL DEFAULT NULL COMMENT '活体检测类型',
    PRIMARY KEY (`id`) USING BTREE
) COMMENT = '活体识别对账流水记录表';

CREATE TABLE `tx_std_live_log`
(
    `id`          int(0)                                                  NOT NULL AUTO_INCREMENT COMMENT '主键',
    `std_id`      int(0)                                                  NULL DEFAULT NULL COMMENT '考生ID',
    `biz_token`   varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '腾讯活体识别token',
    `create_time` datetime(0)                                             NULL DEFAULT NULL COMMENT '创建时间',
    `pull_flag`   varchar(4) CHARACTER SET utf8 COLLATE utf8_general_ci   NULL DEFAULT NULL COMMENT '是否拉取标志：0:未拉取，1：已成功拉取',
    PRIMARY KEY (`id`) USING BTREE
) COMMENT = '考生请求token记录表';

CREATE TABLE `tx_std_material_log`
(
    `id`          int(0)                                          NOT NULL AUTO_INCREMENT,
    `std_id`      int(0)                                          NULL DEFAULT NULL COMMENT '考生ID',
    `std_photo`   text CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT '考生base64图片',
    `create_time` datetime(0)                                     NULL DEFAULT NULL COMMENT '创建时间',
    PRIMARY KEY (`id`) USING BTREE
) COMMENT = '考生失败材料记录表';


-- 2024-10-25
update cf_enrol_param set param_value='InActive' where param_name='EnrolVerifyNoCrownWithPayed';


-- 2024-11-01
CREATE TABLE `ly_std_ticket_result`
(
    `LY_TICKET_ID`     int(0)                                                        NOT NULL AUTO_INCREMENT COMMENT '内部流水号',
    `LY_AGENT_ID`      int(0)                                                        NULL DEFAULT NULL COMMENT '考点编号',
    `STD_ID`           int(0)                                                        NULL DEFAULT NULL COMMENT '考生编号',
    `CATEGORY_ID`      int(0)                                                        NULL DEFAULT NULL COMMENT '大类：本系统根据大类来生成准考证，如果无大类，就为空或者0',
    `TICKET_BATCH`     int(0)                                                        NULL DEFAULT NULL COMMENT '考试批次，某些学校复试需要生成新的准考证',
    `TICKET_SEQ`       int(0)                                                        NULL DEFAULT NULL COMMENT '准考证排序',
    `TICKET_NO`        varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci  NULL DEFAULT NULL COMMENT '准考证号',
    `TICKET_FILE_NAME` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '准考证文件路径',
    PRIMARY KEY (`LY_TICKET_ID`) USING BTREE,
    INDEX `PK_LY_STD_TICKET` (`LY_AGENT_ID`, `STD_ID`, `CATEGORY_ID`) USING BTREE,
    UNIQUE INDEX `IDX_01` (`STD_ID`, `TICKET_BATCH`, `TICKET_NO`) USING BTREE
) ENGINE = InnoDB
  CHARACTER SET = utf8mb4
  COLLATE = utf8mb4_general_ci COMMENT = '考生准考证'
  ROW_FORMAT = Dynamic;

INSERT INTO `frame_res`(`RES_ID`, `RES_NAME`, `RES_ALIAS`, `RES_URL`, `RES_PID`, `RES_LEVEL`, `RES_TYPE`, `RES_CSS`, `RES_STATUS`, `RES_ORDER`, `RES_DESC`) VALUES (304060, '准考证查询', '准考证查询', '/art/layout/material/ticketResult', 304000, 4, 'Page', 'fas fa-cart-plus text-danger', 'Active', 304060, NULL);


CREATE TABLE `ly_room_model_photo`
(
    `id`          int(0)                                                  NOT NULL COMMENT '主键',
    `room_id`     int(0)                                                  NOT NULL COMMENT '考场ID',
    `seq`         int(0)                                                  NULL DEFAULT NULL COMMENT '序号',
    `photo_path`  varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '图片路径',
    `create_time` datetime(0)                                             NULL DEFAULT NULL COMMENT '创建时间',
    PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB
  CHARACTER SET = utf8
  COLLATE = utf8_general_ci COMMENT = '模特拍照表'
  ROW_FORMAT = Dynamic;


-- 2024-11-08
ALTER TABLE `tx_std_live_log`
    ADD INDEX `IDX_01`(`biz_token`),
    ADD INDEX `IDX_02`(`std_id`);

-- 考生报考详情报表
INSERT INTO `frame_report`(`REPORT_NAME`, `REPORT_ALIAS`, `RES_ID`, `REPORT_SQL`, `REPORT_PARAM`, `REPORT_PARAM_FLAG`, `REPORT_SUFFIX`, `INDEX_FLAG`, `UPDATE_TIME`, `MAX_SIZE`, `CURRENT_SIZE`) VALUES ('StdRegEnrolReport', '考生报考详情导出', NULL, 'SELECT\r\n	r.std_name,\r\n	r.cert_id,\r\n	r.std_sex,\r\n	r.exam_id,\r\n	r.std_mobile,\r\n	case when e.pay_status = \'Active\' then \'已缴费\' else \'未缴费\' end pay_status,\r\n	e.aspect_name,\r\n	ep.province_name \r\nFROM\r\n	std_enrol e,\r\n	std_reg r\r\n	LEFT JOIN cf_enrol_province ep ON ep.province_id = r.std_province \r\nWHERE\r\n	e.std_id = r.std_id \r\n	AND ( e.aspect_name =?  OR 1 =?) \r\n	AND ( e.pay_status =?  OR 1 =?) \r\n	AND ( r.std_sex =?  OR 1 =?) \r\n	AND ( r.province_flag =?  OR 1 =?)', 'aspect_name?\r\npay_status?\r\nstd_sex?\r\nprovince_flag?', NULL, 'LocalDateTime', 'Active', NULL, NULL, NULL);
INSERT INTO `frame_report_detail`(`REPORT_NAME`, `FIELD_ORDER`, `FIELD_NAME`, `FIELD_LENGTH`, `FIELD_TEXT`, `FIELD_DATE_TYPE`, `FIELD_STATUS`, `DICT_NAME`) VALUES ('StdRegEnrolReport', 1, 'std_name', 20, '考生姓名', 'java.lang.String', 'Active', NULL);
INSERT INTO `frame_report_detail`(`REPORT_NAME`, `FIELD_ORDER`, `FIELD_NAME`, `FIELD_LENGTH`, `FIELD_TEXT`, `FIELD_DATE_TYPE`, `FIELD_STATUS`, `DICT_NAME`) VALUES ('StdRegEnrolReport', 2, 'cert_id', 20, '证件号码', 'java.lang.String', 'Active', NULL);
INSERT INTO `frame_report_detail`(`REPORT_NAME`, `FIELD_ORDER`, `FIELD_NAME`, `FIELD_LENGTH`, `FIELD_TEXT`, `FIELD_DATE_TYPE`, `FIELD_STATUS`, `DICT_NAME`) VALUES ('StdRegEnrolReport', 3, 'exam_id', 20, '高考报名号', 'java.lang.String', 'Active', NULL);
INSERT INTO `frame_report_detail`(`REPORT_NAME`, `FIELD_ORDER`, `FIELD_NAME`, `FIELD_LENGTH`, `FIELD_TEXT`, `FIELD_DATE_TYPE`, `FIELD_STATUS`, `DICT_NAME`) VALUES ('StdRegEnrolReport', 4, 'std_sex', 10, '性别', 'java.lang.String', 'Active', NULL);
INSERT INTO `frame_report_detail`(`REPORT_NAME`, `FIELD_ORDER`, `FIELD_NAME`, `FIELD_LENGTH`, `FIELD_TEXT`, `FIELD_DATE_TYPE`, `FIELD_STATUS`, `DICT_NAME`) VALUES ('StdRegEnrolReport', 5, 'std_mobile', 20, '联系电话', 'java.lang.String', 'Active', NULL);
INSERT INTO `frame_report_detail`(`REPORT_NAME`, `FIELD_ORDER`, `FIELD_NAME`, `FIELD_LENGTH`, `FIELD_TEXT`, `FIELD_DATE_TYPE`, `FIELD_STATUS`, `DICT_NAME`) VALUES ('StdRegEnrolReport', 6, 'province_name', 20, '高考所在省份', 'java.lang.String', 'Active', NULL);
INSERT INTO `frame_report_detail`(`REPORT_NAME`, `FIELD_ORDER`, `FIELD_NAME`, `FIELD_LENGTH`, `FIELD_TEXT`, `FIELD_DATE_TYPE`, `FIELD_STATUS`, `DICT_NAME`) VALUES ('StdRegEnrolReport', 7, 'aspect_name', 20, '报考专业', 'java.lang.String', 'Active', NULL);
INSERT INTO `frame_report_detail`(`REPORT_NAME`, `FIELD_ORDER`, `FIELD_NAME`, `FIELD_LENGTH`, `FIELD_TEXT`, `FIELD_DATE_TYPE`, `FIELD_STATUS`, `DICT_NAME`) VALUES ('StdRegEnrolReport', 8, 'pay_status', 10, '是否缴费', 'java.lang.String', 'Active', NULL);

-- 准考证下载考生报表
INSERT INTO `frame_report`(`REPORT_NAME`, `REPORT_ALIAS`, `RES_ID`, `REPORT_SQL`, `REPORT_PARAM`, `REPORT_PARAM_FLAG`, `REPORT_SUFFIX`, `INDEX_FLAG`, `UPDATE_TIME`, `MAX_SIZE`, `CURRENT_SIZE`) VALUES ('TicketStdDownloadReport', '准考证下载考生导出', NULL, 'select r.std_name,r.cert_id,r.exam_id,r.std_sex,r.std_mobile from std_reg r,std_enrol e\r\n where r.std_id=e.std_id and e.pay_status=\'Active\'\r\n  and (r.province_flag = ? or 1=?)\r\n	and r.std_id in (select distinct std_id from std_log where log_type=\'DownloadTicket\')', 'province_flag?', NULL, 'LocalDateTime', 'Active', NULL, NULL, NULL);
INSERT INTO `frame_report_detail`(`REPORT_NAME`, `FIELD_ORDER`, `FIELD_NAME`, `FIELD_LENGTH`, `FIELD_TEXT`, `FIELD_DATE_TYPE`, `FIELD_STATUS`, `DICT_NAME`) VALUES ('TicketStdDownloadReport', 1, 'std_name', 20, '考生姓名', 'java.lang.String', 'Active', NULL);
INSERT INTO `frame_report_detail`(`REPORT_NAME`, `FIELD_ORDER`, `FIELD_NAME`, `FIELD_LENGTH`, `FIELD_TEXT`, `FIELD_DATE_TYPE`, `FIELD_STATUS`, `DICT_NAME`) VALUES ('TicketStdDownloadReport', 2, 'cert_id', 20, '证件号码', 'java.lang.String', 'Active', NULL);
INSERT INTO `frame_report_detail`(`REPORT_NAME`, `FIELD_ORDER`, `FIELD_NAME`, `FIELD_LENGTH`, `FIELD_TEXT`, `FIELD_DATE_TYPE`, `FIELD_STATUS`, `DICT_NAME`) VALUES ('TicketStdDownloadReport', 3, 'exam_id', 20, '高考报名号', 'java.lang.String', 'Active', NULL);
INSERT INTO `frame_report_detail`(`REPORT_NAME`, `FIELD_ORDER`, `FIELD_NAME`, `FIELD_LENGTH`, `FIELD_TEXT`, `FIELD_DATE_TYPE`, `FIELD_STATUS`, `DICT_NAME`) VALUES ('TicketStdDownloadReport', 4, 'std_sex', 10, '性别', 'java.lang.String', 'Active', NULL);
INSERT INTO `frame_report_detail`(`REPORT_NAME`, `FIELD_ORDER`, `FIELD_NAME`, `FIELD_LENGTH`, `FIELD_TEXT`, `FIELD_DATE_TYPE`, `FIELD_STATUS`, `DICT_NAME`) VALUES ('TicketStdDownloadReport', 5, 'std_mobile', 20, '联系电话', 'java.lang.String', 'Active', NULL);

-- 准考证未下载考生报表
INSERT INTO `frame_report`(`REPORT_NAME`, `REPORT_ALIAS`, `RES_ID`, `REPORT_SQL`, `REPORT_PARAM`, `REPORT_PARAM_FLAG`, `REPORT_SUFFIX`, `INDEX_FLAG`, `UPDATE_TIME`, `MAX_SIZE`, `CURRENT_SIZE`) VALUES ('TicketStdNotDownloadReport', '准考证未下载考生导出', NULL, 'select r.std_name,r.cert_id,r.exam_id,r.std_sex,r.std_mobile from std_reg r,std_enrol e\r\n where r.std_id=e.std_id and e.pay_status=\'Active\'\r\n  and (r.province_flag = ? or 1=?)\r\n	and r.std_id not in (select distinct std_id from std_log where log_type=\'DownloadTicket\')', 'province_flag?', NULL, 'LocalDateTime', 'Active', NULL, NULL, NULL);
INSERT INTO `frame_report_detail`(`REPORT_NAME`, `FIELD_ORDER`, `FIELD_NAME`, `FIELD_LENGTH`, `FIELD_TEXT`, `FIELD_DATE_TYPE`, `FIELD_STATUS`, `DICT_NAME`) VALUES ('TicketStdNotDownloadReport', 1, 'std_name', 20, '考生姓名', 'java.lang.String', 'Active', NULL);
INSERT INTO `frame_report_detail`(`REPORT_NAME`, `FIELD_ORDER`, `FIELD_NAME`, `FIELD_LENGTH`, `FIELD_TEXT`, `FIELD_DATE_TYPE`, `FIELD_STATUS`, `DICT_NAME`) VALUES ('TicketStdNotDownloadReport', 2, 'cert_id', 20, '证件号码', 'java.lang.String', 'Active', NULL);
INSERT INTO `frame_report_detail`(`REPORT_NAME`, `FIELD_ORDER`, `FIELD_NAME`, `FIELD_LENGTH`, `FIELD_TEXT`, `FIELD_DATE_TYPE`, `FIELD_STATUS`, `DICT_NAME`) VALUES ('TicketStdNotDownloadReport', 3, 'exam_id', 20, '高考报名号', 'java.lang.String', 'Active', NULL);
INSERT INTO `frame_report_detail`(`REPORT_NAME`, `FIELD_ORDER`, `FIELD_NAME`, `FIELD_LENGTH`, `FIELD_TEXT`, `FIELD_DATE_TYPE`, `FIELD_STATUS`, `DICT_NAME`) VALUES ('TicketStdNotDownloadReport', 4, 'std_sex', 10, '性别', 'java.lang.String', 'Active', NULL);
INSERT INTO `frame_report_detail`(`REPORT_NAME`, `FIELD_ORDER`, `FIELD_NAME`, `FIELD_LENGTH`, `FIELD_TEXT`, `FIELD_DATE_TYPE`, `FIELD_STATUS`, `DICT_NAME`) VALUES ('TicketStdNotDownloadReport', 5, 'std_mobile', 20, '联系电话', 'java.lang.String', 'Active', NULL);


UPDATE `frame_report` SET  `REPORT_SQL` = 'select distinct r.std_name,r.cert_id,r.exam_id,r.std_sex,r.std_mobile from std_reg r,std_enrol e,ly_std_ticket t\r\n where r.std_id=e.std_id and e.pay_status=\'Active\' and r.std_id=t.std_id\r\n and (r.province_flag = ? or 1=?)\r\n	and r.std_id not in (select distinct std_id from std_log where log_type=\'DownloadTicket\');'
WHERE `REPORT_NAME` = 'TicketStdNotDownloadReport';


-- 2024-11-11
INSERT INTO `cf_enrol_param`(`PARAM_TYPE`, `PARAM_NAME`, `PARAM_VALUE`, `PARAM_NOTE`) VALUES ('Enrol', 'YmScoreFinalEndTime', '2022-03-28 09:00:00', '复试成绩查询结束时间');
INSERT INTO `cf_enrol_param`(`PARAM_TYPE`, `PARAM_NAME`, `PARAM_VALUE`, `PARAM_NOTE`) VALUES ('Enrol', 'YmScoreFinalStartTime', '2022-03-28 09:00:00', '复试成绩查询开始时间');
INSERT INTO `cf_enrol_param`(`PARAM_TYPE`, `PARAM_NAME`, `PARAM_VALUE`, `PARAM_NOTE`) VALUES ('Enrol', 'YmScoreFirstEndTime', '2022-03-28 09:00:00', '初试成绩查询结束时间');
INSERT INTO `cf_enrol_param`(`PARAM_TYPE`, `PARAM_NAME`, `PARAM_VALUE`, `PARAM_NOTE`) VALUES ('Enrol', 'YmScoreFirstStartTime', '2022-03-28 09:00:00', '初试成绩查询开始时间');


-- 2024-11-13
INSERT INTO `cf_enrol_param`(`PARAM_TYPE`, `PARAM_NAME`, `PARAM_VALUE`, `PARAM_NOTE`) VALUES ('Enrol', 'ModifyStuDeadLine', '2025-03-01 09:00:00', '个人信息修改截止时间');

-- 2024-11-15
delete from frame_dict where dict_name='YmStdType' and dict_text='新疆少数民族协作计划';