Acasă Explorează Ajutor
Autentificare
art-enrollment
/
server
2
0
Bifurcare 0
Fisiere Probleme 0 Trageți solicitările 0 Wiki
Ramură: master
Ramuri Etichete
server
/
sql
/
art.sql

art.sql 7.6 KB
Permalink Istoric Crud

1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556 
  1. INSERT INTO `frame_report`(`REPORT_NAME`, `REPORT_ALIAS`, `RES_ID`, `REPORT_SQL`, `REPORT_PARAM`, `REPORT_PARAM_FLAG`, `REPORT_SUFFIX`, `INDEX_FLAG`, `REPORT_TYPE`, `WITH_THREAD`) VALUES ('ExamRoomTimeReport', '考场考试时间', NULL, 'SELECT\n	group_name,\n	room_addr,\n	min min_start_time,\n	max max_end_time,\n	GROUP_CONCAT( ss.JUDGE_NAME ) judge_name\nFROM\n	(\n	SELECT\n		s.GROUP_NAME,\n		r.LY_ROOM_ID,\n		r.ROOM_ADDR,\n		r.EXAM_DATE,\n		min( REAL_INROOM_TIME ) min,\n		max( REAL_INROOM_TIME ) max \n	FROM\n		ly_std_subject s,\n		ly_room_interview r \n	WHERE\n		r.LY_ROOM_ID = s.REAL_ROOM_ID \n		AND s.real_ROOM_ID IN ( SELECT i.ly_room_id FROM ly_group g, ly_room_interview i WHERE optr_dept= ? AND g.LY_GROUP_Id = i.LY_GROUP_ID ) \n	GROUP BY\n		s.GROUP_NAME,\n		r.LY_ROOM_ID,\n		r.ROOM_ADDR,\n		r.EXAM_DATE \n	) x,\n	ex_judge_room ss \nWHERE\n	ss.ROOM_ID = x.LY_ROOM_ID \nGROUP BY\n	GROUP_NAME,\n	LY_ROOM_ID,\n	ROOM_ADDR,\n	min,\n	max', 'optr_dept', NULL, 'LocalDateTime', 'Active', 'XLXS', NULL);
    2. 

  2. INSERT INTO `frame_report_detail`(`REPORT_NAME`, `FIELD_ORDER`, `FIELD_NAME`, `FIELD_LENGTH`, `FIELD_TEXT`, `FIELD_DATE_TYPE`, `FIELD_STATUS`, `DICT_NAME`) VALUES ('ExamRoomTimeReport', 1, 'group_name', 10, '考试内容', 'java.lang.String', 'Active', NULL);
    3. 

  3. INSERT INTO `frame_report_detail`(`REPORT_NAME`, `FIELD_ORDER`, `FIELD_NAME`, `FIELD_LENGTH`, `FIELD_TEXT`, `FIELD_DATE_TYPE`, `FIELD_STATUS`, `DICT_NAME`) VALUES ('ExamRoomTimeReport', 2, 'room_addr', 10, '考场', 'java.lang.String', 'Active', NULL);
    4. 

  4. INSERT INTO `frame_report_detail`(`REPORT_NAME`, `FIELD_ORDER`, `FIELD_NAME`, `FIELD_LENGTH`, `FIELD_TEXT`, `FIELD_DATE_TYPE`, `FIELD_STATUS`, `DICT_NAME`) VALUES ('ExamRoomTimeReport', 3, 'min_start_time', 10, '开始', 'java.lang.String', 'Active', NULL);
    5. 

  5. INSERT INTO `frame_report_detail`(`REPORT_NAME`, `FIELD_ORDER`, `FIELD_NAME`, `FIELD_LENGTH`, `FIELD_TEXT`, `FIELD_DATE_TYPE`, `FIELD_STATUS`, `DICT_NAME`) VALUES ('ExamRoomTimeReport', 4, 'max_end_time', 10, '结束', 'java.lang.String', 'Active', NULL);
    6. 

  6. INSERT INTO `frame_report_detail`(`REPORT_NAME`, `FIELD_ORDER`, `FIELD_NAME`, `FIELD_LENGTH`, `FIELD_TEXT`, `FIELD_DATE_TYPE`, `FIELD_STATUS`, `DICT_NAME`) VALUES ('ExamRoomTimeReport', 5, 'judge_name', 10, '评委', 'java.lang.String', 'Active', NULL);
    7. 

  7. 

  8. #评委结果导出
    9. 

  9. delete from frame_report where report_name='ExamJudgeExtract';
    10. 

  10. INSERT INTO `frame_report`(`REPORT_NAME`, `REPORT_ALIAS`, `RES_ID`, `REPORT_SQL`, `REPORT_PARAM`, `REPORT_PARAM_FLAG`, `REPORT_SUFFIX`, `INDEX_FLAG`, `REPORT_TYPE`, `WITH_THREAD`) VALUES ('ExamJudgeExtract', '评委抽签结果表', NULL, 'SELECT group_name,room_seq,room_addr,judge_seq,judge_name,judge_mobile,judge_title,judge_school,judge_tel,(CASE WHEN room_chief=\'Active\' THEN \'主评委\' ELSE NULL END) room_chief,j.judge_breeds,j.judge_desc FROM ex_judge j,ex_judge_extract_detail d,ly_room_interview r,ly_group g WHERE g.ly_group_id=r.ly_group_id AND j.judge_id=d.judge_id AND d.ly_room_id=r.ly_room_id AND r.ly_agent_id=? AND r.exam_date=? ORDER BY r.ly_group_id,r.room_seq,judge_seq', 'ly_agent_id\r\nexam_date:LocalDate', NULL, 'LocalDate', NULL, 'XLXS', NULL);
    11. 

  11. INSERT INTO `frame_report_detail`(`REPORT_NAME`, `FIELD_ORDER`, `FIELD_NAME`, `FIELD_LENGTH`, `FIELD_TEXT`, `FIELD_DATE_TYPE`, `FIELD_STATUS`, `DICT_NAME`) VALUES ('ExamJudgeExtract', 10, 'judge_breeds', 20, '品类', 'java.lang.String', 'Active', NULL);
    12. 

  12. INSERT INTO `frame_report_detail`(`REPORT_NAME`, `FIELD_ORDER`, `FIELD_NAME`, `FIELD_LENGTH`, `FIELD_TEXT`, `FIELD_DATE_TYPE`, `FIELD_STATUS`, `DICT_NAME`) VALUES ('ExamJudgeExtract', 11, 'judge_desc', 20, '备注', 'java.lang.String', 'Active', NULL);
    13. 

  13. 

  14. #复试是否能够修改曲目
    15. 

  15. INSERT INTO `cf_enrol_param`(`PARAM_TYPE`, `PARAM_NAME`, `PARAM_VALUE`, `PARAM_NOTE`) VALUES ('Enrol', 'EnrolModifyTracksTwo', 'Active', '复试是否可以修改曲目');
    16. 

  16. #三试是否能够修改曲目
    17. 

  17. INSERT INTO `cf_enrol_param`(`PARAM_TYPE`, `PARAM_NAME`, `PARAM_VALUE`, `PARAM_NOTE`) VALUES ('Enrol', 'EnrolModifyTracksThree', 'Active', '允许修改三试曲目');
    18. 

  18. INSERT INTO `cf_enrol_param`(`PARAM_TYPE`, `PARAM_NAME`, `PARAM_VALUE`, `PARAM_NOTE`) VALUES ('Enrol', 'TicketRemark', '暂无可用准考证，准考证开放时间请关注官方公告！', '当准考证没有开放的时候，在微信端显示的提示信息');
    19. 

  19. #冲突查找
    20. 

  20. ALTER TABLE `ly_group` ADD COLUMN `conflict_type` int(4) NULL COMMENT '冲突类型1:强冲突；2：弱冲突' AFTER `aspect_id`;
    21. 

  21. ALTER TABLE `ly_group` MODIFY COLUMN `conflict_type` int(0) NULL DEFAULT 1 COMMENT '冲突类型1:强冲突；2：弱冲突' AFTER `aspect_id`;
    22. 

  22. INSERT INTO `frame_dict`(`DICT_NAME`, `DICT_VALUE`, `DICT_TEXT`, `DICT_ORDER`) VALUES ('ConflictType', '1', '强冲突', 1);
    23. 

  23. INSERT INTO `frame_dict`(`DICT_NAME`, `DICT_VALUE`, `DICT_TEXT`, `DICT_ORDER`) VALUES ('ConflictType', '2', '弱冲突', 2);
    24. 

  24. 

  25. #编排结果冲突
    26. 

  26. CREATE TABLE `ly_std_subject_conflict`  (
    27. 

  27.   `id` int(0) NOT NULL AUTO_INCREMENT,
    28. 

  28.   `std_id` int(0) NULL DEFAULT NULL COMMENT '考生ID',
    29. 

  29.   `group_id` int(0) NULL DEFAULT NULL COMMENT '源分组ID',
    30. 

  30.   `conflict_group_id` int(0) NULL DEFAULT NULL COMMENT '冲突分组ID',
    31. 

  31.   `ly_agent_id` int(0) NULL DEFAULT NULL COMMENT '考点ID',
    32. 

  32.   `batch_id` int(0) NULL DEFAULT NULL COMMENT '批次',
    33. 

  33.   PRIMARY KEY (`id`) USING BTREE
    34. 

  34. ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '编排结果冲突表' ROW_FORMAT = Dynamic;
    35. 

  35. 

  36. 

  37. ALTER TABLE `cf_aspect` 
    38. 

  38. ADD COLUMN `score_mode` varchar(8) NULL DEFAULT 'batch' COMMENT '合分模式：aspect-按照专业计入总分 batch:按考试批次' AFTER `ONLINE_FLAG`;
    39. 

  39. INSERT INTO `frame_dict`(`DICT_NAME`, `DICT_VALUE`, `DICT_TEXT`, `DICT_ORDER`) VALUES ('ScoreMode', 'aspect', '按专业合分', 1);
    40. 

  40. INSERT INTO `frame_dict`(`DICT_NAME`, `DICT_VALUE`, `DICT_TEXT`, `DICT_ORDER`) VALUES ('ScoreMode', 'batch', '按考试批次合分', 2);
    41. 

  41. 

  42. #视频汇总-最新菜单
    43. 

  43. update frame_res set res_url = '/art/exam/videonew/total' where res_url = '/art/exam/video/total';
    44. 

  44. 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 (608000, '一录查询', '一录查询', '/art/score/oneinput', 600000, 2, 'Page', 'fas fa-bookmark text-info	', 'Active', 2, NULL);
    45. 

  45. 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 (503090, '未完成考生', '未完成考生', '/art/exam/interview/nocomplete', 503000, 3, 'Page', 'icon-eye', 'Active', 503090, NULL);
    46. 

  46. 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 (303040, '编排冲突', '编排冲突', '/art/layout/conflict/layout', 303000, 4, 'Page', 'fas  fa-chalkboard-teacher  text-dark', 'Active', 303040, NULL);
    47. 

  47. 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 (304050, '编排结果冲突', '', '/art/layout/conflict/result', 304000, 4, 'Page', 'fas fa-cart-plus text-primary', 'Active', 304050, NULL);
    48. 

  48. 

  49. #北外评委-添加闹钟相关字段
    50. 

  50. ALTER TABLE `ly_group_param` 
    51. 

  51. ADD COLUMN `CLOCK_NUM` int(0) NULL DEFAULT 1 COMMENT '闹钟个数,最大个数3个' AFTER `MIN_PLAY_TIME`,
    52. 

  52. ADD COLUMN `CLOCK_1_SECONDS` int(0) NULL DEFAULT 300 COMMENT '闹钟1默认倒计时' AFTER `CLOCK_NUM`,
    53. 

  53. ADD COLUMN `CLOCK_2_SECONDS` int(0) NULL DEFAULT 500 COMMENT '闹钟2默认倒计时' AFTER `CLOCK_1_SECONDS`,
    54. 

  54. ADD COLUMN `CLOCK_3_SECONDS` int(0) NULL DEFAULT 600 COMMENT '闹钟3默认倒计时' AFTER `CLOCK_2_SECONDS`;
    55. 

  55. 

  56. ALTER TABLE `ly_std_subject` ADD COLUMN `exam_no` varchar(255) NULL comment '考题号';
    57.