server/sql/fz_update.sql

-- 央美附中更新脚本
INSERT INTO `frame_dict`(`DICT_NAME`, `DICT_VALUE`, `DICT_TEXT`, `DICT_ORDER`) VALUES ('PoliticalType', 'masses', '群众', 1);
INSERT INTO `frame_dict`(`DICT_NAME`, `DICT_VALUE`, `DICT_TEXT`, `DICT_ORDER`) VALUES ('PoliticalType', 'league', '共青团员', 2);
INSERT INTO `frame_dict`(`DICT_NAME`, `DICT_VALUE`, `DICT_TEXT`, `DICT_ORDER`) VALUES ('PoliticalType', 'preparty', '预备党员', 3);
INSERT INTO `frame_dict`(`DICT_NAME`, `DICT_VALUE`, `DICT_TEXT`, `DICT_ORDER`) VALUES ('PoliticalType', 'party', '共产党员', 4);

INSERT INTO `frame_dict`(`DICT_NAME`, `DICT_VALUE`, `DICT_TEXT`, `DICT_ORDER`) VALUES ('StdCategory', 'Composite', '综合改革', 1);
INSERT INTO `frame_dict`(`DICT_NAME`, `DICT_VALUE`, `DICT_TEXT`, `DICT_ORDER`) VALUES ('StdCategory', 'History', '历史类', 2);
INSERT INTO `frame_dict`(`DICT_NAME`, `DICT_VALUE`, `DICT_TEXT`, `DICT_ORDER`) VALUES ('StdCategory', 'Physics', '物理类', 3);
INSERT INTO `frame_dict`(`DICT_NAME`, `DICT_VALUE`, `DICT_TEXT`, `DICT_ORDER`) VALUES ('StdCategory', 'Liberal', '文史', 4);
INSERT INTO `frame_dict`(`DICT_NAME`, `DICT_VALUE`, `DICT_TEXT`, `DICT_ORDER`) VALUES ('StdCategory', 'Science', '理工', 5);
INSERT INTO `frame_dict`(`DICT_NAME`, `DICT_VALUE`, `DICT_TEXT`, `DICT_ORDER`) VALUES ('StdCategory', 'ArtLiberal', '文科', 6);
INSERT INTO `frame_dict`(`DICT_NAME`, `DICT_VALUE`, `DICT_TEXT`, `DICT_ORDER`) VALUES ('StdCategory', 'ArtScience', '理科', 7);

INSERT INTO `frame_dict`(`DICT_NAME`, `DICT_VALUE`, `DICT_TEXT`, `DICT_ORDER`) VALUES ('GraduateType', 'HighSchool', '普通高中', 1);
INSERT INTO `frame_dict`(`DICT_NAME`, `DICT_VALUE`, `DICT_TEXT`, `DICT_ORDER`) VALUES ('GraduateType', 'OtherSchool', '其他中等专业学校毕业', 2);
INSERT INTO `frame_dict`(`DICT_NAME`, `DICT_VALUE`, `DICT_TEXT`, `DICT_ORDER`) VALUES ('GraduateType', 'ProfessSchool', '职业高中毕业', 3);
INSERT INTO `frame_dict`(`DICT_NAME`, `DICT_VALUE`, `DICT_TEXT`, `DICT_ORDER`) VALUES ('GraduateType', 'SkillSchool', '技工学校毕业', 4);
INSERT INTO `frame_dict`(`DICT_NAME`, `DICT_VALUE`, `DICT_TEXT`, `DICT_ORDER`) VALUES ('GraduateType', 'LevelSchool', '具有高级中等教育以上同等学力', 5);

INSERT INTO `frame_dict`(`DICT_NAME`, `DICT_VALUE`, `DICT_TEXT`, `DICT_ORDER`) VALUES ('StdType', 'TownNew', '城镇应届', 1);
INSERT INTO `frame_dict`(`DICT_NAME`, `DICT_VALUE`, `DICT_TEXT`, `DICT_ORDER`) VALUES ('StdType', 'SideNew', '农村应届', 2);
INSERT INTO `frame_dict`(`DICT_NAME`, `DICT_VALUE`, `DICT_TEXT`, `DICT_ORDER`) VALUES ('StdType', 'TownOld', '城镇往届', 3);
INSERT INTO `frame_dict`(`DICT_NAME`, `DICT_VALUE`, `DICT_TEXT`, `DICT_ORDER`) VALUES ('StdType', 'SideOld', '农村往届', 4);


-- 2024-10-28
ALTER TABLE `cf_enrol_province`
    ADD COLUMN `ART_SCIENCE_STATUS` varchar(255) NULL COMMENT '新高考、旧高考、不分文理区分' AFTER `REG_FETCH_PARAM`;

ALTER TABLE `std_reg`
    ADD COLUMN `end_date` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '毕业时间，yyyy年MM月' AFTER `UPDATE_TIME`,
    ADD COLUMN `std_political` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '政治面貌' AFTER `end_date`,
    ADD COLUMN `email` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '电子邮箱' AFTER `std_political`;

-- 缴费后是否执行实名认证
update cf_enrol_param set param_value='InActive' where param_name='EnrolVerifyNoCrownWithPayed';


-- 2024-10-29
DELETE FROM frame_report WHERE report_name='ScoreOriginalSubject';
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 ('ScoreOriginalSubject', '原始分汇总表', NULL, 'SELECT r.std_name, t.ticket_no, t.subject_name, t.real_exam_seq, t.max_score, t.min_score, t.total_score, t.subject_score, t.jn01, t.jn02, t.jn03, t.jn04, t.jn05, t.jn06, t.jn07, t.js01, t.js02, t.js03, t.js04, t.js05, t.js06, t.js07 FROM sc_std_subject t, std_reg r WHERE t.std_id = r.std_id AND (t.subject_id = ? OR 1 = ?) ORDER BY t.std_id, t.subject_id, t.room_id', 'subject_id?', NULL, 'LocalDate', 'Active', NULL, NULL, NULL);

DROP TABLE IF EXISTS `fz_score`;
CREATE TABLE `fz_score`
(
    `id`          int(0)                                                       NOT NULL AUTO_INCREMENT COMMENT '主键',
    `std_name`    varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
    `cert_id`     varchar(18) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
    `ticket_no`   varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
    `aspect_name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
    `std_id`      int(0)                                                       NOT NULL,
    `subj_zxjc`   varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '造型基础',
    `subj_mtcz`   varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '命题创作',
    `subj_zhsy`   varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '综合素养考核',
    `face_score`  varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '现场考核',
    `total_score` varchar(12) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '总分',
    `qualified`   varchar(8) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci  NULL DEFAULT NULL COMMENT '是否合格',
    PRIMARY KEY (`id`) USING BTREE,
    UNIQUE INDEX `IDX_01` (`aspect_name`, `std_id`) USING BTREE
) ENGINE = InnoDB
  CHARACTER SET = utf8mb4
  COLLATE = utf8mb4_general_ci
  ROW_FORMAT = Dynamic;

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 ('YmfzScoreExport', '附中成绩导出', NULL, 'select * from fz_score order by ticket_no', NULL, 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 ('YmfzScoreExport', 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 ('YmfzScoreExport', 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 ('YmfzScoreExport', 3, 'ticket_no', 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 ('YmfzScoreExport', 4, '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 ('YmfzScoreExport', 5, 'subj_zxjc', 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 ('YmfzScoreExport', 6, 'subj_mtcz', 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 ('YmfzScoreExport', 7, 'subj_zhsy', 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 ('YmfzScoreExport', 8, 'face_score', 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 ('YmfzScoreExport', 9, 'total_score', 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 ('YmfzScoreExport', 10, 'qualified', 20, '是否合格', 'java.lang.String', 'Active', NULL);


-- 2024-10-30
update cf_enrol_param set param_value='10047FZ' where param_name='SchoolCode';
INSERT INTO `cf_enrol_param`(`PARAM_TYPE`, `PARAM_NAME`, `PARAM_VALUE`, `PARAM_NOTE`) VALUES ('Enrol', 'MaterialShowDate', '2025年1月2日 09:00-15:00', '布展日期');
INSERT INTO `cf_enrol_param`(`PARAM_TYPE`, `PARAM_NAME`, `PARAM_VALUE`, `PARAM_NOTE`) VALUES ('Enrol', 'TicketShowDate', '2025年1月2日', '准考证布展日期');
INSERT INTO `cf_enrol_param`(`PARAM_TYPE`, `PARAM_NAME`, `PARAM_VALUE`, `PARAM_NOTE`) VALUES ('Enrol', 'TicketAgentName', '中央美术学院燕郊校区', '准考证考点名称');

-- 2024-10-31
-- 考生报考详情报表
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);


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


-- 2024-11-04
CREATE TABLE `ly_room_model_photo`
(
    `id`          int(0)                                                  NOT NULL AUTO_INCREMENT 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 活体识别相关表
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/fzbk/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 = '考生失败材料记录表';

-- 附中成绩查询
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 (605001, '成绩查询', '附中成绩查询', '/art/score/ymfz', 600000, 2, 'Page', 'icon-directions	', 'Active', 605001, NULL);


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


-- 2024-11-19
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';

ALTER TABLE `tx_std_live_log`
    ADD INDEX `IDX_01`(`biz_token`),
    ADD INDEX `IDX_02`(`std_id`);



-- 2024-11-21
ALTER TABLE  `std_reg`
    ADD COLUMN `biz_token` varchar(100) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL COMMENT '腾讯活体识别token';