From b19d7869a20efec118e6e2b3384b43b53e3ffb27 Mon Sep 17 00:00:00 2001
From: 龚焕茏 <2842157468@qq.com>
Date: 星期二, 14 五月 2024 17:28:11 +0800
Subject: [PATCH] feat：答卷统计新需求
---
src/main/java/com/mindskip/xzs/repository/ExamPaperMapper.java | 1
src/main/java/com/mindskip/xzs/repository/ExamPaperAnswerMapper.java | 7 +-
src/main/java/com/mindskip/xzs/service/impl/ExamPaperAnswerServiceImpl.java | 15 +++-
src/main/resources/mapper/ExamPaperMapper.xml | 4 +
src/main/java/com/mindskip/xzs/controller/admin/ExamPaperController.java | 5 +
src/main/java/com/mindskip/xzs/service/ExamPaperAnswerService.java | 4 +
src/main/java/com/mindskip/xzs/service/impl/ExamPaperServiceImpl.java | 6 +
src/main/java/com/mindskip/xzs/controller/admin/ExamPaperAnswerController.java | 18 ++++--
src/main/resources/mapper/ExamPaperAnswerMapper.xml | 85 +++++++++++++++++++++++----
src/main/java/com/mindskip/xzs/service/ExamPaperService.java | 5 +
10 files changed, 119 insertions(+), 31 deletions(-)
diff --git a/src/main/java/com/mindskip/xzs/controller/admin/ExamPaperAnswerController.java b/src/main/java/com/mindskip/xzs/controller/admin/ExamPaperAnswerController.java
index 75beda4..2a86dae 100644
--- a/src/main/java/com/mindskip/xzs/controller/admin/ExamPaperAnswerController.java
+++ b/src/main/java/com/mindskip/xzs/controller/admin/ExamPaperAnswerController.java
@@ -1,28 +1,29 @@
package com.mindskip.xzs.controller.admin;
-import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
-import com.google.gson.JsonObject;
+import com.github.pagehelper.PageInfo;
import com.mindskip.xzs.base.BaseApiController;
import com.mindskip.xzs.base.RestResponse;
import com.mindskip.xzs.domain.ExamPaperAnswer;
import com.mindskip.xzs.domain.ExamPaperSubject;
import com.mindskip.xzs.domain.Subject;
-import com.mindskip.xzs.domain.User;
import com.mindskip.xzs.domain.exam.ExamPaperAnswerObject;
-import com.mindskip.xzs.service.*;
+import com.mindskip.xzs.service.ExamPaperAnswerService;
+import com.mindskip.xzs.service.ExamPaperSubjectService;
+import com.mindskip.xzs.service.SubjectService;
+import com.mindskip.xzs.service.UserService;
import com.mindskip.xzs.utility.DateTimeUtil;
import com.mindskip.xzs.utility.ExamUtil;
import com.mindskip.xzs.utility.PageInfoHelper;
-import com.mindskip.xzs.viewmodel.student.exampaper.ExamPaperAnswerPageResponseVM;
import com.mindskip.xzs.viewmodel.admin.paper.ExamPaperAnswerPageRequestVM;
-import com.github.pagehelper.PageInfo;
+import com.mindskip.xzs.viewmodel.student.exampaper.ExamPaperAnswerPageResponseVM;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import javax.validation.Valid;
import java.math.BigDecimal;
import java.util.List;
+import java.util.Map;
import java.util.stream.Collectors;
@RestController("AdminExamPaperAnswerController")
@@ -114,4 +115,9 @@
}
return RestResponse.ok(object);
}
+
+ @RequestMapping(value = "/statistic", method = RequestMethod.GET)
+ public RestResponse<Map<String, Object>> statistic(String examPaperId, String departmentId) {
+ return RestResponse.ok(examPaperAnswerService.statistic(examPaperId, departmentId));
+ }
}
diff --git a/src/main/java/com/mindskip/xzs/controller/admin/ExamPaperController.java b/src/main/java/com/mindskip/xzs/controller/admin/ExamPaperController.java
index 662afdb..9facf4a 100644
--- a/src/main/java/com/mindskip/xzs/controller/admin/ExamPaperController.java
+++ b/src/main/java/com/mindskip/xzs/controller/admin/ExamPaperController.java
@@ -123,4 +123,9 @@
List<PaperExcelVO> list = examPaperService.getPaperExcelById(id);
ExcelUtils.export(response,"涓汉缁冧範",list,PaperExcelVO.class);
}
+
+ @RequestMapping(value = "/list", method = RequestMethod.GET)
+ public RestResponse<List<ExamPaper>> list() {
+ return RestResponse.ok(examPaperService.list());
+ }
}
diff --git a/src/main/java/com/mindskip/xzs/repository/ExamPaperAnswerMapper.java b/src/main/java/com/mindskip/xzs/repository/ExamPaperAnswerMapper.java
index a1ff9fa..93d769f 100644
--- a/src/main/java/com/mindskip/xzs/repository/ExamPaperAnswerMapper.java
+++ b/src/main/java/com/mindskip/xzs/repository/ExamPaperAnswerMapper.java
@@ -1,14 +1,11 @@
package com.mindskip.xzs.repository;
-import com.mindskip.xzs.domain.ExamPaper;
import com.mindskip.xzs.domain.ExamPaperAnswer;
import com.mindskip.xzs.domain.ExamTemplatesUserCount;
import com.mindskip.xzs.domain.ScoreTemplatesUserCount;
import com.mindskip.xzs.domain.other.KeyValue;
-import com.mindskip.xzs.domain.vo.ScoreTemplatesCountVO;
import com.mindskip.xzs.domain.vo.TeamplatesUserExcelVO;
import com.mindskip.xzs.viewmodel.admin.exam.ExamPaperEditRequestVM;
-import com.mindskip.xzs.viewmodel.admin.exam.ExamPaperEditRequestVO;
import com.mindskip.xzs.viewmodel.admin.paper.ExamPaperGradePageRequestVM;
import com.mindskip.xzs.viewmodel.admin.paper.ExamPaperGradeQuery;
import com.mindskip.xzs.viewmodel.student.exampaper.ExamPaperAnswerPageVM;
@@ -17,6 +14,7 @@
import java.util.Date;
import java.util.List;
+import java.util.Map;
@Mapper
public interface ExamPaperAnswerMapper extends BaseMapper<ExamPaperAnswer> {
@@ -53,4 +51,7 @@
void insertDefault(ExamPaperEditRequestVM model);
+ Map<String, Object> histogram(@Param("examPaperId") String examPaperId, @Param("departmentId") String departmentId);
+
+ Map<String, Object> pieChart(@Param("examPaperId") String examPaperId, @Param("departmentId") String departmentId);
}
diff --git a/src/main/java/com/mindskip/xzs/repository/ExamPaperMapper.java b/src/main/java/com/mindskip/xzs/repository/ExamPaperMapper.java
index 7997772..d756404 100644
--- a/src/main/java/com/mindskip/xzs/repository/ExamPaperMapper.java
+++ b/src/main/java/com/mindskip/xzs/repository/ExamPaperMapper.java
@@ -38,4 +38,5 @@
List<PaperExcelVO> getPaperExcelById(@Param("id") Integer id);
+ List<ExamPaper> list();
}
diff --git a/src/main/java/com/mindskip/xzs/service/ExamPaperAnswerService.java b/src/main/java/com/mindskip/xzs/service/ExamPaperAnswerService.java
index a2fe2cb..5f24cad 100644
--- a/src/main/java/com/mindskip/xzs/service/ExamPaperAnswerService.java
+++ b/src/main/java/com/mindskip/xzs/service/ExamPaperAnswerService.java
@@ -2,13 +2,13 @@
import com.github.pagehelper.PageInfo;
import com.mindskip.xzs.domain.*;
-import com.mindskip.xzs.domain.vo.ScoreTemplatesCountVO;
import com.mindskip.xzs.viewmodel.admin.paper.ExamPaperGradePageRequestVM;
import com.mindskip.xzs.viewmodel.admin.paper.ExamPaperGradeQuery;
import com.mindskip.xzs.viewmodel.student.exam.ExamPaperSubmitVM;
import com.mindskip.xzs.viewmodel.student.exampaper.ExamPaperAnswerPageVM;
import java.util.List;
+import java.util.Map;
public interface ExamPaperAnswerService extends BaseService<ExamPaperAnswer> {
@@ -63,4 +63,6 @@
List<ExamPaperAnswer> getByScorePaperIdAndUserId(List<ScoreTemplatesUserCount> scoreTemplatesUserCounts);
PageInfo<ExamPaperAnswer> getByCreatUser(ExamPaperGradeQuery query);
+
+ Map<String, Object> statistic(String examPaperId, String departmentId);
}
diff --git a/src/main/java/com/mindskip/xzs/service/ExamPaperService.java b/src/main/java/com/mindskip/xzs/service/ExamPaperService.java
index ba50cd5..94896b7 100644
--- a/src/main/java/com/mindskip/xzs/service/ExamPaperService.java
+++ b/src/main/java/com/mindskip/xzs/service/ExamPaperService.java
@@ -1,5 +1,6 @@
package com.mindskip.xzs.service;
+import com.github.pagehelper.PageInfo;
import com.mindskip.xzs.domain.ExamPaper;
import com.mindskip.xzs.domain.User;
import com.mindskip.xzs.domain.vo.PaperExcelVO;
@@ -9,8 +10,6 @@
import com.mindskip.xzs.viewmodel.student.dashboard.PaperFilter;
import com.mindskip.xzs.viewmodel.student.dashboard.PaperInfo;
import com.mindskip.xzs.viewmodel.student.exam.ExamPaperPageVM;
-import com.github.pagehelper.PageInfo;
-import org.apache.ibatis.annotations.Param;
import java.util.List;
@@ -39,4 +38,6 @@
List<PaperExcelVO> getPaperExcelById(Integer id);
void missExam(ExamPaperEditRequestVM model);
+
+ List<ExamPaper> list();
}
diff --git a/src/main/java/com/mindskip/xzs/service/impl/ExamPaperAnswerServiceImpl.java b/src/main/java/com/mindskip/xzs/service/impl/ExamPaperAnswerServiceImpl.java
index eedd73f..5319359 100644
--- a/src/main/java/com/mindskip/xzs/service/impl/ExamPaperAnswerServiceImpl.java
+++ b/src/main/java/com/mindskip/xzs/service/impl/ExamPaperAnswerServiceImpl.java
@@ -10,7 +10,6 @@
import com.mindskip.xzs.domain.other.ExamPaperAnswerUpdate;
import com.mindskip.xzs.domain.other.KeyValue;
import com.mindskip.xzs.domain.task.TaskItemAnswerObject;
-import com.mindskip.xzs.domain.vo.ScoreTemplatesCountVO;
import com.mindskip.xzs.repository.ExamPaperAnswerMapper;
import com.mindskip.xzs.repository.ExamPaperMapper;
import com.mindskip.xzs.repository.QuestionMapper;
@@ -30,9 +29,7 @@
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
-import java.util.ArrayList;
-import java.util.Date;
-import java.util.List;
+import java.util.*;
import java.util.stream.Collectors;
@Service
@@ -313,4 +310,14 @@
return PageHelper.startPage(query.getPageIndex(), query.getPageSize(), "id desc").doSelectPageInfo(() ->
examPaperAnswerMapper.getByCreatUser(query));
}
+
+ @Override
+ public Map<String, Object> statistic(String examPaperId, String departmentId) {
+ HashMap<String, Object> map = new HashMap<>();
+ Map<String, Object> histogram = examPaperAnswerMapper.histogram(examPaperId, departmentId);
+ Map<String, Object> pieChart = examPaperAnswerMapper.pieChart(examPaperId, departmentId);
+ map.put("histogram", histogram);
+ map.put("pieChart", pieChart);
+ return map;
+ }
}
diff --git a/src/main/java/com/mindskip/xzs/service/impl/ExamPaperServiceImpl.java b/src/main/java/com/mindskip/xzs/service/impl/ExamPaperServiceImpl.java
index 67543e5..2c8e75d 100644
--- a/src/main/java/com/mindskip/xzs/service/impl/ExamPaperServiceImpl.java
+++ b/src/main/java/com/mindskip/xzs/service/impl/ExamPaperServiceImpl.java
@@ -32,7 +32,6 @@
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
-import org.springframework.util.CollectionUtils;
import java.util.*;
import java.util.concurrent.atomic.AtomicInteger;
@@ -621,4 +620,9 @@
// 琛ヨ�冩椂灏嗘病鏈夐�夋嫨鐨勫苟涓旀病鏈夊弬鍔犺繃鑰冭瘯鐨勮�冪敓澧炲姞涓�鏉￠浂鍒嗚�冭瘯璁板綍锛屽惁鍒欐病鏈夊弬鍔犺繃鑰冭瘯鐨勫嵆浣夸笉琚�夋嫨涔熷彲浠ュ弬鍔犺ˉ鑰�
examPaperAnswerMapper.insertDefault(model);
}
+
+ @Override
+ public List<ExamPaper> list() {
+ return examPaperMapper.list();
+ }
}
diff --git a/src/main/resources/mapper/ExamPaperAnswerMapper.xml b/src/main/resources/mapper/ExamPaperAnswerMapper.xml
index 9f4e0bf..d312ee9 100644
--- a/src/main/resources/mapper/ExamPaperAnswerMapper.xml
+++ b/src/main/resources/mapper/ExamPaperAnswerMapper.xml
@@ -395,20 +395,77 @@
where a.create_time
</select>
- <insert id="insertDefault">
- 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 id="histogram" resultType="java.util.Map">
+ <![CDATA[
SELECT
- a.id, a.name, a.paper_type, 0, 0, a.score, 0, a.question_count, 0, 2, b.user_id, NOW(), 0
- 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
- WHERE a.id = #{examPaperId}
- and c.id is null
- and b.user_id not in
- <foreach collection="userIds" item="item" open="(" close=")" separator=",">
- #{item}
- </foreach>
- </insert>
+ COUNT(CASE WHEN a.user_score < 60 THEN 1 END) AS scoreLeq60,
+ COUNT(CASE WHEN a.user_score >= 60 AND a.user_score < 80 THEN 1 END) AS score60To79,
+ COUNT(CASE WHEN a.user_score >= 80 AND a.user_score <= 100 THEN 1 END) AS score80To100,
+ 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,
+ COUNT(CASE WHEN TIMESTAMPDIFF(YEAR, c.birth_day, CURDATE()) BETWEEN 40 AND 49 THEN 1 END) AS age40To49,
+ COUNT(CASE WHEN TIMESTAMPDIFF(YEAR, c.birth_day, CURDATE()) >= 50 THEN 1 END) AS ageOver50
+ ]]>
+ FROM
+ t_exam_paper_answer a
+ INNER JOIN (SELECT * FROM t_user_department
+ <if test="departmentId != null and departmentId != ''">
+ WHERE department_id = #{departmentId}
+ </if>
+ GROUP BY user_id) b ON a.create_user = b.user_id
+ LEFT JOIN t_user c ON a.create_user = c.id
+ WHERE
+ (a.invalid IS NULL OR a.invalid = 0)
+ <if test="examPaperId != null and examPaperId != ''">
+ AND a.exam_paper_id = #{examPaperId}
+ </if>
+ </select>
+
+ <select id="pieChart" resultType="java.util.Map">
+ SELECT
+ <!-- 搴旇鍙傚姞鑰冭瘯鐨勪汉鏁� -->
+ COUNT(DISTINCT a.user_id) AS totalExpected,
+ <!-- 鍙傚姞鑰冭瘯鐨勪汉鏁� -->
+ COUNT(DISTINCT c.create_user) AS totalAttended,
+ <!-- 鍙傚姞鑰冭瘯鐨勭櫨鍒嗘瘮锛屽苟鍥涜垗浜斿叆淇濈暀涓や綅灏忔暟 -->
+ ROUND((COUNT(DISTINCT c.create_user) * 100.0 / NULLIF(COUNT(DISTINCT a.user_id), 0)), 2) AS percentageAttended,
+ <!-- 鍙傚姞鑰冭瘯鐨勫皬鏁� -->
+ COUNT(DISTINCT c.create_user) / NULLIF(COUNT(DISTINCT a.user_id), 0) as attended,
+ <!-- 缂鸿�冪殑浜烘暟 -->
+ COUNT(DISTINCT a.user_id) - COUNT(DISTINCT c.create_user) AS totalAbsent,
+ <!-- 缂鸿�冪殑鐧惧垎姣旓紝骞跺洓鑸嶄簲鍏ヤ繚鐣欎袱浣嶅皬鏁� -->
+ ROUND(((COUNT(DISTINCT a.user_id) - COUNT(DISTINCT c.create_user)) * 100.0 / COUNT(DISTINCT a.user_id)), 2) AS percentageAbsent,
+ <!-- 缂鸿�冪殑灏忔暟 -->
+ ((COUNT(DISTINCT a.user_id) - COUNT(DISTINCT c.create_user)) / COUNT(DISTINCT a.user_id)) as absent
+ FROM
+ t_exam_paper_user a
+ INNER JOIN t_exam_paper b ON a.exam_paper_id = b.id
+ <if test="examPaperId != null and examPaperId != ''">
+ AND b.id = #{examPaperId}
+ </if>
+ LEFT JOIN t_exam_paper_answer c ON a.exam_paper_id = c.exam_paper_id AND a.user_id = c.create_user AND (c.invalid IS NULL OR c.invalid = 0)
+ INNER JOIN (SELECT * FROM t_user_department
+ <if test="departmentId != null and departmentId != ''">
+ WHERE department_id = #{departmentId}
+ </if>
+ GROUP BY user_id) d ON a.user_id = d.user_id
+ </select>
+
+<insert id="insertDefault">
+ 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
+ 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
+ WHERE a.id = #{examPaperId}
+ and c.id is null
+ and b.user_id not in
+ <foreach collection="userIds" item="item" open="(" close=")" separator=",">
+ #{item}
+ </foreach>
+</insert>
</mapper>
diff --git a/src/main/resources/mapper/ExamPaperMapper.xml b/src/main/resources/mapper/ExamPaperMapper.xml
index 9a4c638..4708fcc 100644
--- a/src/main/resources/mapper/ExamPaperMapper.xml
+++ b/src/main/resources/mapper/ExamPaperMapper.xml
@@ -423,4 +423,8 @@
where a.exam_paper_id = #{id}
</select>
+ <select id="list" resultType="com.mindskip.xzs.domain.ExamPaper">
+ select id, name from t_exam_paper order by create_time desc
+ </select>
+
</mapper>
--
Gitblit v1.8.0