From 0e5a8aec6cdd998e1db7f5aa8e1b020f1c97573a Mon Sep 17 00:00:00 2001
From: xiangpei <xiangpei@timesnew.cn>
Date: 星期四, 30 五月 2024 11:20:47 +0800
Subject: [PATCH] Merge remote-tracking branch 'origin/master'
---
src/main/resources/mapper/ExamPaperAnswerMapper.xml | 38 +++++++++++++++++++-------------------
src/main/resources/mapper/UserMapper.xml | 19 +++++++------------
2 files changed, 26 insertions(+), 31 deletions(-)
diff --git a/src/main/resources/mapper/ExamPaperAnswerMapper.xml b/src/main/resources/mapper/ExamPaperAnswerMapper.xml
index dd8c0c1..bb5749d 100644
--- a/src/main/resources/mapper/ExamPaperAnswerMapper.xml
+++ b/src/main/resources/mapper/ExamPaperAnswerMapper.xml
@@ -233,7 +233,7 @@
<update id="setMissExam">
update t_exam_paper_answer
- set invalid = 2
+ set invalid = 1
where exam_paper_id = #{examPaperId}
and create_user in (
<foreach collection="userIds" item="item" index="index" separator=",">
@@ -244,7 +244,7 @@
<update id="setMissExamByTemplate">
update t_exam_paper_answer
- set invalid = 2
+ set invalid = 1
where exam_paper_id
in
<foreach collection="paperIds" item="item" separator="," open="(" close=")">
@@ -423,11 +423,11 @@
<select id="histogram" resultType="java.util.TreeMap">
<![CDATA[
SELECT
- COUNT(CASE WHEN a.user_score < 60 THEN 1 END) AS score0To59,
- COUNT(CASE WHEN a.user_score >= 60 AND a.user_score < 70 THEN 1 END) AS score60To69,
- COUNT(CASE WHEN a.user_score >= 70 AND a.user_score < 80 THEN 1 END) AS score70To79,
- COUNT(CASE WHEN a.user_score >= 80 AND a.user_score < 90 THEN 1 END) AS score80To89,
- COUNT(CASE WHEN a.user_score >= 90 AND a.user_score <= 100 THEN 1 END) AS score90To100,
+ COUNT(CASE WHEN a.user_score / 10 < 60 THEN 1 END) AS score0To59,
+ COUNT(CASE WHEN a.user_score / 10 >= 60 AND a.user_score / 10 < 70 THEN 1 END) AS score60To69,
+ COUNT(CASE WHEN a.user_score / 10 >= 70 AND a.user_score / 10 < 80 THEN 1 END) AS score70To79,
+ COUNT(CASE WHEN a.user_score / 10 >= 80 AND a.user_score / 10 < 90 THEN 1 END) AS score80To89,
+ COUNT(CASE WHEN a.user_score / 10 >= 90 AND a.user_score / 10 <= 100 THEN 1 END) AS score90To100,
COUNT(CASE WHEN TIMESTAMPDIFF(YEAR, c.birth_day, CURDATE()) BETWEEN 0 AND 19 THEN 1 END) AS age0To19,
COUNT(CASE WHEN TIMESTAMPDIFF(YEAR, c.birth_day, CURDATE()) BETWEEN 20 AND 29 THEN 1 END) AS age20To29,
COUNT(CASE WHEN TIMESTAMPDIFF(YEAR, c.birth_day, CURDATE()) BETWEEN 30 AND 39 THEN 1 END) AS age30To39,
@@ -455,11 +455,11 @@
<select id="histogramByTemplate" resultType="java.util.TreeMap">
SELECT
<![CDATA[
- COUNT(CASE WHEN c.user_score < 60 THEN 1 END) AS score0To59,
- COUNT(CASE WHEN c.user_score >= 60 AND c.user_score < 70 THEN 1 END) AS score60To69,
- COUNT(CASE WHEN c.user_score >= 70 AND c.user_score < 80 THEN 1 END) AS score70To79,
- COUNT(CASE WHEN c.user_score >= 80 AND c.user_score < 90 THEN 1 END) AS score80To89,
- COUNT(CASE WHEN c.user_score >= 90 AND c.user_score <= 100 THEN 1 END) AS score90To100,
+ COUNT(CASE WHEN c.user_score / 10 < 60 THEN 1 END) AS score0To59,
+ COUNT(CASE WHEN c.user_score / 10 >= 60 AND c.user_score / 10 < 70 THEN 1 END) AS score60To69,
+ COUNT(CASE WHEN c.user_score / 10 >= 70 AND c.user_score / 10 < 80 THEN 1 END) AS score70To79,
+ COUNT(CASE WHEN c.user_score / 10 >= 80 AND c.user_score / 10 < 90 THEN 1 END) AS score80To89,
+ COUNT(CASE WHEN c.user_score / 10 >= 90 AND c.user_score / 10 <= 100 THEN 1 END) AS score90To100,
]]>
COUNT(CASE WHEN TIMESTAMPDIFF(YEAR, d.birth_day, CURDATE()) BETWEEN 0 AND 19 THEN 1 END) AS age0To19,
COUNT(CASE WHEN TIMESTAMPDIFF(YEAR, d.birth_day, CURDATE()) BETWEEN 20 AND 29 THEN 1 END) AS age20To29,
@@ -508,7 +508,7 @@
INSERT INTO t_exam_paper_answer(exam_paper_id, paper_name, paper_type, system_score, user_score, paper_score, question_correct, question_count, do_time, status,
create_user, create_time, invalid)
SELECT
- a.id, a.name, a.paper_type, 0, 0, a.score, 0, a.question_count, 0, 2, b.user_id, NOW(), 0
+ a.id, a.name, a.paper_type, 0, 0, a.score, 0, a.question_count, 0, 2, b.user_id, NOW(), 2
FROM t_exam_paper a
left join t_exam_paper_user b on a.id = b.exam_paper_id and b.deleted = 0
left join t_exam_paper_answer c on a.id = c.exam_paper_id and c.create_user = b.user_id
@@ -525,7 +525,7 @@
a.id,
a.name,
COUNT(DISTINCT c.create_user) as factPeopleTotal, -- 鍙傝�冧汉鏁�
- IFNULL(ROUND(AVG(c.user_score), 2), 0) as averageScore, -- 骞冲潎鎴愮哗
+ IFNULL(ROUND(AVG(c.user_score / 10), 2), 0) as averageScore, -- 骞冲潎鎴愮哗
IFNULL(ROUND((COUNT(DISTINCT c.create_user) * 100.0 / COUNT(DISTINCT d.user_id)), 2), 0) AS referencePercentage -- 鍙傝�冪櫨鍒嗘瘮
FROM
t_department a
@@ -559,7 +559,7 @@
</if>
)) as examTotal, -- 鎬昏�冭瘯娆℃暟
COUNT(DISTINCT c.create_user) as factPeopleTotal, -- 鍙傝�冧汉鏁�
- IFNULL(ROUND(AVG(c.user_score), 2), 0) as averageScore, -- 骞冲潎鎴愮哗
+ IFNULL(ROUND(AVG(c.user_score / 10), 2), 0) as averageScore, -- 骞冲潎鎴愮哗
IFNULL(ROUND((COUNT(DISTINCT c.create_user) * 100.0 / COUNT(DISTINCT d.user_id)), 2), 0) AS referencePercentage -- 鍙傝�冪櫨鍒嗘瘮
FROM
t_exam_templates a
@@ -585,7 +585,7 @@
a.id,
a.name,
COUNT(DISTINCT c.create_user) as factPeopleTotal, -- 鍙傝�冧汉鏁�
- IFNULL(ROUND(AVG(c.user_score), 2), 0) as averageScore, -- 骞冲潎鎴愮哗
+ IFNULL(ROUND(AVG(c.user_score / 10), 2), 0) as averageScore, -- 骞冲潎鎴愮哗
IFNULL(ROUND((COUNT(DISTINCT c.create_user) * 100.0 / COUNT(DISTINCT d.user_id)), 2), 0) AS referencePercentage -- 鍙傝�冪櫨鍒嗘瘮
FROM
t_department a
@@ -600,7 +600,7 @@
<select id="totalByPaper" resultType="com.mindskip.xzs.domain.vo.ExamPaperDataExportVO" parameterType="com.mindskip.xzs.domain.vo.ExamPaperDataVO">
SELECT
(SELECT
- (SELECT count(*) FROM t_exam_paper
+ (SELECT COUNT(*) FROM t_exam_paper
<if test="deptIds != null and deptIds.size() > 0">
WHERE dept_id IN
<foreach collection="deptIds" item="item" open="(" separator="," close=")">
@@ -617,8 +617,8 @@
</foreach>
</if>
)) as examTotal, -- 鎬昏�冭瘯娆℃暟
- count(DISTINCT b.create_user) AS factPeopleTotal, -- 鍙傝�冩暟
- IFNULL(ROUND(avg(user_score), 2), 0) as averageScore, -- 骞冲潎鍒�
+ COUNT(DISTINCT b.create_user) AS factPeopleTotal, -- 鍙傝�冩暟
+ IFNULL(ROUND(AVG(user_score / 10), 2), 0) as averageScore, -- 骞冲潎鍒�
IFNULL(ROUND((COUNT(DISTINCT b.create_user) * 100.0 / COUNT(DISTINCT c.user_id)), 2), 0) AS referencePercentage -- 鍙傝�冪櫨鍒嗘瘮
FROM t_exam_paper a
LEFT JOIN t_exam_paper_user c ON a.id = c.exam_paper_id
diff --git a/src/main/resources/mapper/UserMapper.xml b/src/main/resources/mapper/UserMapper.xml
index 1c55b8d..0d3b2e1 100644
--- a/src/main/resources/mapper/UserMapper.xml
+++ b/src/main/resources/mapper/UserMapper.xml
@@ -551,18 +551,13 @@
</select>
<select id="getUserByExamByTemplateId" resultType="com.mindskip.xzs.domain.User" parameterType="com.mindskip.xzs.domain.ExamPaper">
- <![CDATA[
- SELECT DISTINCT
- d.*
- FROM
- t_exam_templates a
- INNER JOIN t_exam_templates_user_count b ON a.id = b.exam_templates_id and a.id = ${id}
- LEFT JOIN t_exam_paper_answer c ON b.exam_paper_id = c.exam_paper_id AND c.create_user = b.user_id and invalid = 0
- LEFT JOIN t_user d ON b.user_id = d.id
- WHERE
- ( c.id IS NULL OR ( user_score / paper_score ) < 0.6 )
- AND b.user_id = #{createUser}
- ]]>
+ select *
+ from t_user
+ where id in
+ (select user_id from t_exam_templates_user WHERE templates_id = #{id} and user_id not in
+ (SELECT create_user from t_exam_paper_answer where (user_score / paper_score) > 0.6 and (invalid = 0 or invalid is null) and exam_paper_id in
+ (select exam_paper_id from t_exam_templates_user_count WHERE exam_templates_id = #{id})))
+ and id = #{createUser}
</select>
<select id="getDeptIds" resultType="integer">
--
Gitblit v1.8.0