阅卷系统业务全景图
阅卷系统业务全景图.png
Omr分数更改业务流程
Omr分数更改业务流程.png
EMS
- TB_COURSE
ALTER TABLE TB_COURSE ADD DIS_ORDER INT DEFAULT 0
SELECT * FROM EXAM.VW_EXAM_PROGRESS
SELECT * FROM EMS.VW_EXAM_COURSE
EXAM
- TB_STUDENT
- ABSENT 标记 CHAR(1)
‘Y ’缺考 （计0分），N’未缺考（正常） ，'Z' 作弊（计1分），默认为 N；
缺考的不计入平均分计算，作弊的需要计入；
- STUTYPE 文理科 NUMBER(8)
-1 不区分 1 文科 2 理科 - PINYIN 拼音 VARCHAR2(50) ：用于学生按姓名首字母查询
- IS_THREE INT 是否三校生、借读生： 1 是 0 否 ，默认为0，主要用于学生成绩的过滤；
- IS_SPECIAL INT 是否特殊学生 ：1 是 0 否，默认为0
ALTER TABLE TB_STUDENT ADD PINYIN VARCHAR2(50)
ALTER TABLE TB_STUDENT ADD IS_THREE INT DEFAULT 0
ALTER TABLE TB_STUDENT ADD IS_SPECIAL INT DEFAULT 0
ALTER TABLE BR_STUDENTINFO ADD PINYIN VARCHAR2(50)
ALTER TABLE BR_STUDENTINFO ADD IS_THREE INT DEFAULT 0
ALTER TABLE BR_STUDENTINFO ADD IS_SPECIAL INT DEFAULT 0
ALTER TABLE TT_STUDENT ADD PINYIN VARCHAR2(50)
ALTER TABLE TT_STUDENT ADD IS_THREE INT DEFAULT 0
ALTER TABLE TT_STUDENT ADD IS_SPECIAL INT DEFAULT 0
SELECT * FROM VW_STUDENT
UPDATE TB_STUDENT SET PINYIN = F_PINYIN(NAME)
权限管理
- Sys_Role
RoleId RoleName Text
1 Administrator 管理员
2 Leader 阅卷组长
3 Reader 阅卷员
4 SuperLeader 超级组长
5 SuperReader 超级阅卷员
- Roles:
Administrator
Administrator
Administrator;SuperLeader
Administrator
Administrator
Administrator;Leader;Reader;SuperLeader;SuperReader
Administrator;Leader;SuperLeader
Administrator;Leader;SuperLeader
Administrator
Administrator
Administrator
Administrator
Administrator
Administrator
Administrator
Administrator
Administrator
- Sys_Privilege
PrivilegeId PrivilegeName Text
1 BrowseExam 浏览考试
2 CreateExam 创建考试
3 DeleteExam 删除考试
4 UpdateExam 更新考试
5 BrowseStudent 浏览学生
6 CreateStudent 添加学生
7 DeleteStudent 删除学生
8 UpdateStudent 编辑学生
9 BrowseTemplate 浏览模版
10 CreateTemplate 创建模版
11 DeleteTemplate 删除模版
12 UpdateTemplate 编辑模版
13 UserManager 用户管理
14 RoleManager 权限管理
15 ReadN 评阅普通卷
16 ReadP 评阅问题卷
17 ReadJ 评阅仲裁卷
18 BrowseProgress 进度监控
19 BrowseScore 抽查试卷
20 UpdateScore 修改分数
21 AutoItem 自动分配题目
22 SystemTools 系统工具
Analysis Model
- EXAM.Area
| AreaId | Title | AreaType | RegType |
|---|---|---|---|
| 1 | 考号 | 4 | 4 条形码 |
| 2 | 选择题 | 5 | 1 OMR |
| 3 | 选择题 | 5 | 1 OMR |
| 4 | 选择题 | 5 | 1 OMR |
| 5 | 定位器 | 1 | 6 直线 |
| 6 | 定位器 | 1 | 6 直线 |
| 7 | 切割区 | 6 | 0 |
| 8 | 保密区 | 10 | 0 |
| 9 | 切割区 | 6 | 0 |
| 10 | 切割区 | 6 | 0 |
| 11 | 切割区 | 6 | 0 |
| 12 | 题号区 | 8 | 1 OMR |
- Model
SELECT * FROM TB_EXAM WHERE EXAMGUID = '5F9766432291437C8031E9F2EF774532' AND EXAMNUM = 1108;
SELECT * FROM TB_EXAM_COURSE WHERE EXAMGUID = '5F9766432291437C8031E9F2EF774532' AND EXAMID IN (11080001,11080005);
SELECT * FROM TB_STUDENT WHERE EXAMID IN (11080001,11080005);
SELECT * FROM TB_AREA WHERE EXAMID IN (11080001,11080005);
SELECT * FROM TB_ANSWER WHERE EXAMID IN (11080001,11080005);
SELECT * FROM TB_FRAME WHERE EXAMID IN (11080001,11080005);
SELECT * FROM TB_ITEM WHERE EXAMID IN (11080001,11080005);
SELECT * FROM TB_SUBITEM WHERE EXAMID IN (11080001,11080005);
SELECT * FROM TB_SCAN_PAPER WHERE EXAMID IN (11080001,11080005);
SELECT * FROM TB_SCAN_GROUP WHERE EXAMID IN (11080001,11080005);
SELECT * FROM TB_SCAN_PAGE WHERE EXAMID IN (11080001,11080005);
SELECT * FROM TB_SCAN_OMR1 WHERE EXAMID IN (11080001,11080005);
SELECT * FROM TB_IMGPATH WHERE EXAMID IN (11080001,11080005);
SELECT * FROM TB_LIB1 WHERE EXAMID IN (11080001,11080005);
SELECT * FROM TB_LIB_DEMO WHERE EXAMID IN (11080001,11080005);
SELECT * FROM TB_LIB3 WHERE EXAMID IN (11080001,11080005);
SELECT * FROM TB_LIB4 WHERE EXAMID IN (11080001,11080005);
SELECT * FROM TB_IDX1 WHERE EXAMID IN (11080001,11080005);
SELECT * FROM TB_IDX2 WHERE EXAMID IN (11080001,11080005);
SELECT * FROM TB_IDX3 WHERE EXAMID IN (11080001,11080005);
SELECT * FROM TB_IDX4 WHERE EXAMID IN (11080001,11080005);
SELECT * FROM TB_IDXR WHERE EXAMID IN (11080001,11080005);
SELECT * FROM TB_ITEM_ROLE WHERE EXAMID IN (11080001,11080005)
SELECT * FROM TB_BUFFER WHERE EXAMID IN (11080001,11080005);
SELECT * FROM TB_FINAL WHERE EXAMID IN (11080001,11080005);
SELECT * FROM TB_ANNOTATION WHERE EXAMID IN (11080001,11080005);
SELECT * FROM TB_PROBLEM_MEMO WHERE EXAMID IN (11080001,11080005);
SELECT * FROM TB_LOG WHERE EXAMID IN (11080001,11080005);
SELECT * FROM TB_LOG_CHECK WHERE EXAMID IN (11080001,11080005);
SELECT * FROM TB_LOG_CONTROL WHERE EXAMID IN (11080001,11080005);
SELECT * FROM TB_LOG_OWNER WHERE EXAMID IN (11080001,11080005);
SELECT * FROM TB_LOG_TRAIN WHERE EXAMID IN (11080001,11080005);
SELECT * FROM TB_STAT WHERE EXAMID IN (11080001,11080005);
SELECT * FROM TB_STAT_READS WHERE EXAMID IN (11080001,11080005);
SELECT * FROM TB_STAT_FINAL WHERE EXAMID IN (11080001,11080005);
SELECT * FROM TB_STAT_SCORE WHERE EXAMID IN (11080001,11080005);
SELECT * FROM TB_STAT_CHECK WHERE EXAMID IN (11080001,11080005);
SELECT * FROM TB_STAT_COUNT WHERE EXAMID IN (11080001,11080005);
SELECT * FROM TB_STAT_DIFF WHERE EXAMID IN (11080001,11080005);
SELECT * FROM TB_STAT_MATCH WHERE EXAMID IN (11080001,11080005);
数据字典说明
TB_ANSWER.MARKTYPE：是否多选 单选为0；
TB_ITEM.OPTIONID：是否可选题 0 正常 1 可选题；
TB_ITEM.SCORERULE：出分规则 0 平均 1 最高 2 最低
TB_ITEM.MAXPERSON：评次 单评 1 双评 3 三评 4
TB_ITEM.MATCHMODE：匹配方式 0 默认 1 全匹配
TB_ITEM.SUBDIFF：子题匹配 0 不匹配 1 匹配
TB_ITEM.DIFF_N 误差
TB_ITEM.DIFF_O 自评误差
TB_ITEM.DIFF_M 受控卷误差
TB_ITEM.TOTAL 总任务量
TB_ITEM_ROLE.STAGE：评卷状态 N 正评 D 练习
解决分数溢出
-- 解决分数溢出 500错误
-- 名称:PR_REFRESH_SCORE
CREATE OR REPLACE PROCEDURE PR_REFRESH_SCORE
IS
CNT INTEGER;
BEGIN
SELECT COUNT(*)
INTO CNT
FROM TB_BUFFER T1
LEFT JOIN TB_ITEM T2
ON T1.EXAMID = T2.EXAMID
AND T1.ITEMID = T2.ITEMID
WHERE T1.SCORE > T2.FULLSCORE;
IF (CNT = 0) THEN
dbms_output.put_line('5');
RETURN;
ELSE
UPDATE TB_BUFFER T1
SET (SCORE, SUBSCORE) =
(SELECT T2.FULLSCORE, T2.FULLSCORE
FROM TB_ITEM T2
WHERE T2.EXAMID = T1.EXAMID
AND T2.ITEMID = T1.ITEMID)
WHERE EXISTS (SELECT 1
FROM TB_ITEM T2
WHERE T1.EXAMID = T2.EXAMID
AND T1.ITEMID = T2.ITEMID
AND T1.SCORE > T2.FULLSCORE);
END IF;
COMMIT;
END;
Oracle 里面定时执行任务，比如存储过程内容
DECLARE
job_no_ NUMBER;
BEGIN
DBMS_JOB.SUBMIT(job_no_,
'PR_REFRESH_SCORE;',
sysdate,
'sysdate+1/1440');
COMMIT;
END;
-- 成功创建的JOB
SELECT * FROM DBA_JOBS T WHERE T.BROKEN='N' AND T.SCHEMA_USER='EXAM'
-- 查询正在执行的JOB
SELECT * FROM DBA_JOBS_RUNNING;
