From d526b031d167f76ae22ad53d3221a59d38abc388 Mon Sep 17 00:00:00 2001
From: 龚焕茏 <2842157468@qq.com>
Date: 星期五, 24 五月 2024 18:08:23 +0800
Subject: [PATCH] feat:人数分布统计支持随机试卷

---
 src/main/java/com/mindskip/xzs/repository/ExamPaperMapper.java              |    2 
 src/main/java/com/mindskip/xzs/repository/ExamPaperAnswerMapper.java        |    4 +
 src/main/java/com/mindskip/xzs/service/impl/ExamPaperAnswerServiceImpl.java |   22 +++++++
 src/main/resources/mapper/ExamPaperMapper.xml                               |   15 ++++
 src/main/java/com/mindskip/xzs/domain/vo/ExamPaperStatisticVO.java          |    2 
 src/main/java/com/mindskip/xzs/domain/vo/ExamPaperDataVO.java               |    4 +
 src/main/java/com/mindskip/xzs/service/impl/ExamPaperServiceImpl.java       |    5 +
 pom.xml                                                                     |    6 ++
 src/main/resources/mapper/ExamPaperAnswerMapper.xml                         |  107 +++++++++++++++++++++++++++++++++++
 9 files changed, 161 insertions(+), 6 deletions(-)

diff --git a/pom.xml b/pom.xml
index 3ad2269..9c8aa35 100644
--- a/pom.xml
+++ b/pom.xml
@@ -178,6 +178,12 @@
         </dependency>
 
         <dependency>
+            <groupId>commons-beanutils</groupId>
+            <artifactId>commons-beanutils</artifactId>
+            <version>1.9.3</version>
+        </dependency>
+
+        <dependency>
             <groupId>com.qiniu</groupId>
             <artifactId>qiniu-java-sdk</artifactId>
             <version>7.2.25</version>
diff --git a/src/main/java/com/mindskip/xzs/domain/vo/ExamPaperDataVO.java b/src/main/java/com/mindskip/xzs/domain/vo/ExamPaperDataVO.java
index 1dc8c50..d37f33c 100644
--- a/src/main/java/com/mindskip/xzs/domain/vo/ExamPaperDataVO.java
+++ b/src/main/java/com/mindskip/xzs/domain/vo/ExamPaperDataVO.java
@@ -3,6 +3,8 @@
 import lombok.Data;
 import lombok.experimental.Accessors;
 
+import java.util.List;
+
 /**
  * @author gonghl
  * @since 2024/5/23
@@ -16,4 +18,6 @@
 
     private Integer type;
 
+    private List<Integer> deptIds;
+
 }
diff --git a/src/main/java/com/mindskip/xzs/domain/vo/ExamPaperStatisticVO.java b/src/main/java/com/mindskip/xzs/domain/vo/ExamPaperStatisticVO.java
index b099bdd..1242f37 100644
--- a/src/main/java/com/mindskip/xzs/domain/vo/ExamPaperStatisticVO.java
+++ b/src/main/java/com/mindskip/xzs/domain/vo/ExamPaperStatisticVO.java
@@ -16,4 +16,6 @@
 
     private List<Integer> departmentId;
 
+    private Integer examPaperType;
+
 }
diff --git a/src/main/java/com/mindskip/xzs/repository/ExamPaperAnswerMapper.java b/src/main/java/com/mindskip/xzs/repository/ExamPaperAnswerMapper.java
index efbe31b..8fe8d1a 100644
--- a/src/main/java/com/mindskip/xzs/repository/ExamPaperAnswerMapper.java
+++ b/src/main/java/com/mindskip/xzs/repository/ExamPaperAnswerMapper.java
@@ -57,6 +57,8 @@
 
     Map<String, Object> histogram(ExamPaperStatisticVO examPaperStatisticVO);
 
+    Map<String, Object> histogramByTemplate(ExamPaperStatisticVO examPaperStatisticVO);
+
     Map<String, Object> pieChart(ExamPaperStatisticVO examPaperStatisticVO);
 
     List<ExamPaperDataExportVO> dataByTemplate(ExamPaperDataVO examPaperDataVO);
@@ -66,4 +68,6 @@
     List<ExamPaperDataExportVO> dataByPaper(ExamPaperDataVO examPaperDataVO);
 
     ExamPaperDataExportVO totalByPaper(ExamPaperDataVO examPaperDataVO);
+
+    Integer getExamTotal(ExamPaperStatisticVO examPaperStatisticVO);
 }
diff --git a/src/main/java/com/mindskip/xzs/repository/ExamPaperMapper.java b/src/main/java/com/mindskip/xzs/repository/ExamPaperMapper.java
index 1e7ffb7..36aabe1 100644
--- a/src/main/java/com/mindskip/xzs/repository/ExamPaperMapper.java
+++ b/src/main/java/com/mindskip/xzs/repository/ExamPaperMapper.java
@@ -42,6 +42,8 @@
 
     List<ExamPaper> list(List<Integer> deptIds);
 
+    List<ExamPaper> template(List<Integer> deptIds);
+
     List<UserVO> selectStudent(UserVO userVO);
 
     List<Integer> getExamPaperByTemplateId(ExamTemplatesVO model);
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 cc3f478..3f362f2 100644
--- a/src/main/java/com/mindskip/xzs/service/impl/ExamPaperAnswerServiceImpl.java
+++ b/src/main/java/com/mindskip/xzs/service/impl/ExamPaperAnswerServiceImpl.java
@@ -32,6 +32,7 @@
 import org.springframework.stereotype.Service;
 import org.springframework.transaction.annotation.Transactional;
 
+import java.math.BigDecimal;
 import java.util.*;
 import java.util.stream.Collectors;
 
@@ -317,7 +318,25 @@
     @Override
     public Map<String, Object> statistic(ExamPaperStatisticVO examPaperStatisticVO) {
         // 鑾峰彇鍘熷鏁版嵁
-        Map<String, Object> histogram = examPaperAnswerMapper.histogram(examPaperStatisticVO);
+        Map<String, Object> histogram;
+        ExamPaperDataExportVO examPaperDataExportVO;
+        if (Objects.equals(examPaperStatisticVO.getExamPaperType(), 7)) {
+            histogram = examPaperAnswerMapper.histogramByTemplate(examPaperStatisticVO);
+            examPaperDataExportVO = examPaperAnswerMapper.totalByTemplate(new ExamPaperDataVO().setId(examPaperStatisticVO.getExamPaperId()).setDeptIds(examPaperStatisticVO.getDepartmentId()));
+        } else {
+            histogram = examPaperAnswerMapper.histogram(examPaperStatisticVO);
+            examPaperDataExportVO = examPaperAnswerMapper.totalByPaper(new ExamPaperDataVO().setId(examPaperStatisticVO.getExamPaperId()).setDeptIds(examPaperStatisticVO.getDepartmentId()));
+        }
+
+        // 涓嶅瓨鍦ㄦ椂濉厖0
+        if (Objects.isNull(examPaperDataExportVO)) {
+            examPaperDataExportVO = new ExamPaperDataExportVO()
+                    .setExamTotal(examPaperAnswerMapper.getExamTotal(examPaperStatisticVO))
+                    .setFactPeopleTotal(0)
+                    .setAverageScore(BigDecimal.ZERO)
+                    .setReferencePercentage(BigDecimal.ZERO);
+        }
+
         Map<String, Object> pieChart = examPaperAnswerMapper.pieChart(examPaperStatisticVO);
         // 鍒濆鍖栫粨鏋滃鍣�
         HashMap<String, Object> map = new HashMap<>();
@@ -349,6 +368,7 @@
         map.put("score", score);
         map.put("age", age);
         map.put("examPeopleNum", examPeopleNum);
+        map.put("total", examPaperDataExportVO);
         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 b33c030..8eb7a39 100644
--- a/src/main/java/com/mindskip/xzs/service/impl/ExamPaperServiceImpl.java
+++ b/src/main/java/com/mindskip/xzs/service/impl/ExamPaperServiceImpl.java
@@ -637,7 +637,10 @@
 
     @Override
     public List<ExamPaper> list(List<Integer> deptIds) {
-        return examPaperMapper.list(deptIds);
+        List<ExamPaper> list = examPaperMapper.list(deptIds);
+        List<ExamPaper> template = examPaperMapper.template(deptIds);
+        list.addAll(template);
+        return list.stream().sorted(Comparator.comparing(ExamPaper::getCreateTime).reversed()).collect(Collectors.toList());
     }
 
     @Override
diff --git a/src/main/resources/mapper/ExamPaperAnswerMapper.xml b/src/main/resources/mapper/ExamPaperAnswerMapper.xml
index 7d74b41..857b230 100644
--- a/src/main/resources/mapper/ExamPaperAnswerMapper.xml
+++ b/src/main/resources/mapper/ExamPaperAnswerMapper.xml
@@ -452,6 +452,35 @@
     </if>
   </select>
 
+  <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 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,
+    COUNT(CASE WHEN TIMESTAMPDIFF(YEAR, d.birth_day, CURDATE()) BETWEEN 30 AND 39 THEN 1 END) AS age30To39,
+    COUNT(CASE WHEN TIMESTAMPDIFF(YEAR, d.birth_day, CURDATE()) BETWEEN 40 AND 49 THEN 1 END) AS age40To49,
+    COUNT(CASE WHEN TIMESTAMPDIFF(YEAR, d.birth_day, CURDATE()) >= 50 THEN 1 END) AS ageOver50
+    FROM
+    t_exam_templates a
+    LEFT JOIN (SELECT exam_templates_id, GROUP_CONCAT(exam_paper_id) AS exam_paper_id FROM t_exam_templates_user_count GROUP BY exam_templates_id) b ON a.id = b.exam_templates_id
+    LEFT JOIN t_exam_paper_answer c ON FIND_IN_SET(c.exam_paper_id, b.exam_paper_id) AND (c.invalid = 0 OR c.invalid IS NULL)
+    LEFT JOIN t_user d ON c.create_user = d.id AND d.deleted = 0
+    LEFT JOIN (SELECT * FROM t_user_department GROUP BY user_id) e ON d.id = e.user_id
+    WHERE a.id = #{examPaperId}
+    <if test="departmentId != null and departmentId.size() > 0">
+      AND e.department_id IN
+      <foreach collection="departmentId" item="item" open="(" separator="," close=")">
+        #{item}
+      </foreach>
+    </if>
+  </select>
+
   <select id="pieChart" resultType="java.util.TreeMap">
     SELECT
     <!-- 鍙傚姞鑰冭瘯鐨勪汉鏁� -->
@@ -510,7 +539,24 @@
 
   <select id="totalByTemplate" 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_templates)) as examTotal, -- 鎬昏�冭瘯娆℃暟
+            (SELECT
+            (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=")">
+                #{item}
+              </foreach>
+            </if>
+            )
+            +
+            (SELECT count(*) FROM t_exam_templates
+            <if test="deptIds != null and deptIds.size() > 0">
+              WHERE dept_id IN
+              <foreach collection="deptIds" item="item" open="(" separator="," close=")">
+                #{item}
+              </foreach>
+            </if>
+            )) as examTotal, -- 鎬昏�冭瘯娆℃暟
            COUNT(DISTINCT c.create_user) as factPeopleTotal, -- 鍙傝�冧汉鏁�
            IFNULL(ROUND(AVG(c.user_score), 2), 0) as averageScore, -- 骞冲潎鎴愮哗
            IFNULL(ROUND((COUNT(DISTINCT c.create_user) * 100.0 / COUNT(DISTINCT d.user_id)), 2), 0) AS referencePercentage -- 鍙傝�冪櫨鍒嗘瘮
@@ -519,7 +565,16 @@
         LEFT JOIN t_exam_templates_user d ON d.templates_id = a.id
         LEFT JOIN t_exam_templates_user_count e ON d.templates_id = e.exam_templates_id
         LEFT JOIN t_exam_paper_answer c ON d.user_id = c.create_user AND e.exam_paper_id = c.exam_paper_id AND (c.invalid = 0 OR c.invalid IS NULL)
+        <if test="deptIds != null and deptIds.size() > 0">
+            LEFT JOIN t_user_department f ON d.user_id = f.user_id
+        </if>
     WHERE a.id = #{id}
+    <if test="deptIds != null and deptIds.size() > 0">
+      AND f.department_id IN
+      <foreach collection="deptIds" item="item" open="(" separator="," close=")">
+        #{item}
+      </foreach>
+    </if>
     GROUP BY
       a.id
   </select>
@@ -542,15 +597,63 @@
 
   <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_templates)) as examTotal, -- 鎬昏�冭瘯娆℃暟
+        (SELECT
+             (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=")">
+                  #{item}
+                </foreach>
+              </if>
+             )
+             +
+             (SELECT count(*) FROM t_exam_templates
+            <if test="deptIds != null and deptIds.size() > 0">
+              WHERE dept_id IN
+              <foreach collection="deptIds" item="item" open="(" separator="," close=")">
+                #{item}
+              </foreach>
+            </if>
+              )) as examTotal, -- 鎬昏�冭瘯娆℃暟
         count(DISTINCT b.create_user) AS factPeopleTotal, -- 鍙傝�冩暟
         IFNULL(ROUND(avg(user_score), 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_answer b ON a.id = b.exam_paper_id AND (b.invalid = 0 OR b.invalid IS NULL)
            LEFT JOIN t_exam_paper_user c ON a.id = c.exam_paper_id
+           <if test="deptIds != null and deptIds.size() > 0">
+            LEFT JOIN t_user_department d ON c.user_id = d.user_id
+           </if>
     WHERE a.id = #{id}
+    <if test="deptIds != null and deptIds.size() > 0">
+      AND d.department_id IN
+      <foreach collection="deptIds" item="item" open="(" separator="," close=")">
+        #{item}
+      </foreach>
+    </if>
     GROUP BY a.id
   </select>
 
+  <select id="getExamTotal" resultType="java.lang.Integer">
+    SELECT
+    (SELECT count(*) FROM t_exam_paper
+    <if test="departmentId != null and departmentId.size() > 0">
+      WHERE dept_id IN
+      <foreach collection="departmentId" item="item" open="(" separator="," close=")">
+        #{item}
+      </foreach>
+    </if>
+    )
+    +
+    (SELECT count(*) FROM t_exam_templates
+    <if test="departmentId != null and departmentId.size() > 0">
+      WHERE dept_id IN
+      <foreach collection="departmentId" item="item" open="(" separator="," close=")">
+        #{item}
+      </foreach>
+    </if>
+    )
+  </select>
+
+
 </mapper>
diff --git a/src/main/resources/mapper/ExamPaperMapper.xml b/src/main/resources/mapper/ExamPaperMapper.xml
index 7338f3a..90f6c53 100644
--- a/src/main/resources/mapper/ExamPaperMapper.xml
+++ b/src/main/resources/mapper/ExamPaperMapper.xml
@@ -439,9 +439,9 @@
   </select>
 
     <select id="list" resultType="com.mindskip.xzs.domain.ExamPaper">
-    select id, name from t_exam_paper
-    <if test="deptIds != null and deptIds.size() > 0">
+    select id, name, paper_type, create_time from t_exam_paper
     where paper_type != 7
+    <if test="deptIds != null and deptIds.size() > 0">
     and dept_id in
     <foreach collection="deptIds" item="item" open="(" separator="," close=")">
         #{item}
@@ -450,6 +450,17 @@
     order by create_time desc
     </select>
 
+  <select id="template" resultType="com.mindskip.xzs.domain.ExamPaper">
+    select id, name, paper_type, ctime as create_time from t_exam_templates
+    <if test="deptIds != null and deptIds.size() > 0">
+    where dept_id in
+    <foreach collection="deptIds" item="item" open="(" separator="," close=")">
+      #{item}
+    </foreach>
+    </if>
+    order by ctime desc
+  </select>
+
     <select id="selectStudent" resultType="com.mindskip.xzs.domain.vo.UserVO" parameterType="com.mindskip.xzs.domain.vo.UserVO">
       SELECT
         a.id, a.real_name

--
Gitblit v1.8.0