From 00fe3336b89c3c24ffe2826f45419d8e7dbb17fc Mon Sep 17 00:00:00 2001
From: 龚焕茏 <2842157468@qq.com>
Date: 星期四, 23 五月 2024 15:46:57 +0800
Subject: [PATCH] feat:统计新需求

---
 src/main/java/com/mindskip/xzs/repository/ExamPaperMapper.java                 |    4 +
 src/main/java/com/mindskip/xzs/repository/ExamPaperAnswerMapper.java           |   11 +++
 src/main/java/com/mindskip/xzs/service/impl/ExamPaperAnswerServiceImpl.java    |   17 +++++
 src/main/resources/mapper/ExamPaperMapper.xml                                  |    8 ++
 src/main/java/com/mindskip/xzs/controller/admin/ExamPaperController.java       |    5 +
 src/main/java/com/mindskip/xzs/service/ExamPaperAnswerService.java             |    3 +
 src/main/java/com/mindskip/xzs/domain/vo/ExamPaperDataVO.java                  |   19 ++++++
 src/main/java/com/mindskip/xzs/service/impl/ExamPaperServiceImpl.java          |    8 ++
 src/main/java/com/mindskip/xzs/controller/admin/ExamPaperAnswerController.java |    6 ++
 src/main/resources/mapper/ExamPaperAnswerMapper.xml                            |   92 +++++++++++++++++++++++++-----
 src/main/java/com/mindskip/xzs/service/ExamPaperService.java                   |    6 ++
 11 files changed, 162 insertions(+), 17 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 6d2281e..d70659e 100644
--- a/src/main/java/com/mindskip/xzs/controller/admin/ExamPaperAnswerController.java
+++ b/src/main/java/com/mindskip/xzs/controller/admin/ExamPaperAnswerController.java
@@ -8,6 +8,7 @@
 import com.mindskip.xzs.domain.ExamPaperSubject;
 import com.mindskip.xzs.domain.Subject;
 import com.mindskip.xzs.domain.exam.ExamPaperAnswerObject;
+import com.mindskip.xzs.domain.vo.ExamPaperDataVO;
 import com.mindskip.xzs.domain.vo.ExamPaperStatisticVO;
 import com.mindskip.xzs.service.ExamPaperAnswerService;
 import com.mindskip.xzs.service.ExamPaperSubjectService;
@@ -126,4 +127,9 @@
         examPaperStatisticVO.setDepartmentId(ObjectUtils.isNotEmpty(examPaperStatisticVO.getDepartmentId()) ? examPaperStatisticVO.getDepartmentId() : getAdminDeptIds());
         return RestResponse.ok(examPaperAnswerService.statistic(examPaperStatisticVO));
     }
+
+    @RequestMapping(value = "/data", method = RequestMethod.POST)
+    public RestResponse<Map<String, Object>> data(@RequestBody ExamPaperDataVO examPaperDataVO) {
+        return RestResponse.ok(examPaperAnswerService.data(examPaperDataVO));
+    }
 }
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 af682fd..1986f67 100644
--- a/src/main/java/com/mindskip/xzs/controller/admin/ExamPaperController.java
+++ b/src/main/java/com/mindskip/xzs/controller/admin/ExamPaperController.java
@@ -154,4 +154,9 @@
         List<Integer> adminDeptIds = getAdminDeptIds();
         return RestResponse.ok(examPaperService.selectStudent(userVO, adminDeptIds));
     }
+
+    @RequestMapping(value = "/queryCondition", method = RequestMethod.GET)
+    public RestResponse<List<ExamPaper>> queryCondition() {
+        return RestResponse.ok(examPaperService.queryCondition());
+    }
 }
diff --git a/src/main/java/com/mindskip/xzs/domain/vo/ExamPaperDataVO.java b/src/main/java/com/mindskip/xzs/domain/vo/ExamPaperDataVO.java
new file mode 100644
index 0000000..1dc8c50
--- /dev/null
+++ b/src/main/java/com/mindskip/xzs/domain/vo/ExamPaperDataVO.java
@@ -0,0 +1,19 @@
+package com.mindskip.xzs.domain.vo;
+
+import lombok.Data;
+import lombok.experimental.Accessors;
+
+/**
+ * @author gonghl
+ * @since 2024/5/23
+ */
+
+@Data
+@Accessors(chain = true)
+public class ExamPaperDataVO {
+
+    private Integer id;
+
+    private Integer type;
+
+}
diff --git a/src/main/java/com/mindskip/xzs/repository/ExamPaperAnswerMapper.java b/src/main/java/com/mindskip/xzs/repository/ExamPaperAnswerMapper.java
index 06e76c1..36252a9 100644
--- a/src/main/java/com/mindskip/xzs/repository/ExamPaperAnswerMapper.java
+++ b/src/main/java/com/mindskip/xzs/repository/ExamPaperAnswerMapper.java
@@ -4,6 +4,7 @@
 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.ExamPaperDataVO;
 import com.mindskip.xzs.domain.vo.ExamPaperStatisticVO;
 import com.mindskip.xzs.domain.vo.ExamTemplatesVO;
 import com.mindskip.xzs.domain.vo.TeamplatesUserExcelVO;
@@ -57,9 +58,15 @@
 
     void insertDefault(ExamPaperEditRequestVM model);
 
-    void insertDefaultByTemplate(ExamTemplatesVO model);
-
     Map<String, Object> histogram(ExamPaperStatisticVO examPaperStatisticVO);
 
     Map<String, Object> pieChart(ExamPaperStatisticVO examPaperStatisticVO);
+
+    List<Map<String, Object>> dataByTemplate(ExamPaperDataVO examPaperDataVO);
+
+    Map<String, Object> totalByTemplate(ExamPaperDataVO examPaperDataVO);
+
+    List<Map<String, Object>> dataByPaper(ExamPaperDataVO examPaperDataVO);
+
+    Map<String, Object> totalByPaper(ExamPaperDataVO examPaperDataVO);
 }
diff --git a/src/main/java/com/mindskip/xzs/repository/ExamPaperMapper.java b/src/main/java/com/mindskip/xzs/repository/ExamPaperMapper.java
index 2191fbf..1e7ffb7 100644
--- a/src/main/java/com/mindskip/xzs/repository/ExamPaperMapper.java
+++ b/src/main/java/com/mindskip/xzs/repository/ExamPaperMapper.java
@@ -45,4 +45,8 @@
     List<UserVO> selectStudent(UserVO userVO);
 
     List<Integer> getExamPaperByTemplateId(ExamTemplatesVO model);
+
+    List<ExamPaper> getExamPaper();
+
+    List<ExamPaper> getTemplate();
 }
diff --git a/src/main/java/com/mindskip/xzs/service/ExamPaperAnswerService.java b/src/main/java/com/mindskip/xzs/service/ExamPaperAnswerService.java
index af21dd9..db11300 100644
--- a/src/main/java/com/mindskip/xzs/service/ExamPaperAnswerService.java
+++ b/src/main/java/com/mindskip/xzs/service/ExamPaperAnswerService.java
@@ -2,6 +2,7 @@
 
 import com.github.pagehelper.PageInfo;
 import com.mindskip.xzs.domain.*;
+import com.mindskip.xzs.domain.vo.ExamPaperDataVO;
 import com.mindskip.xzs.domain.vo.ExamPaperStatisticVO;
 import com.mindskip.xzs.viewmodel.admin.paper.ExamPaperGradePageRequestVM;
 import com.mindskip.xzs.viewmodel.admin.paper.ExamPaperGradeQuery;
@@ -66,4 +67,6 @@
     PageInfo<ExamPaperAnswer> getByCreatUser(ExamPaperGradeQuery query);
 
     Map<String, Object> statistic(ExamPaperStatisticVO examPaperStatisticVO);
+
+    Map<String, Object> data(ExamPaperDataVO examPaperDataVO);
 }
diff --git a/src/main/java/com/mindskip/xzs/service/ExamPaperService.java b/src/main/java/com/mindskip/xzs/service/ExamPaperService.java
index 56dc960..11b0a05 100644
--- a/src/main/java/com/mindskip/xzs/service/ExamPaperService.java
+++ b/src/main/java/com/mindskip/xzs/service/ExamPaperService.java
@@ -53,4 +53,10 @@
     PageInfo<UserVO> selectStudent(UserVO userVO, List<Integer> adminDeptIds);
 
     void missExamByTemplateId(ExamTemplatesVO model);
+
+    /**
+     * 鑰冭瘯缁熻鐨勬煡璇㈡潯浠�
+     * @return 璇曞嵎id銆佸悕绉般�佺被鍨�
+     */
+    List<ExamPaper> queryCondition();
 }
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 6b0d9f2..8270867 100644
--- a/src/main/java/com/mindskip/xzs/service/impl/ExamPaperAnswerServiceImpl.java
+++ b/src/main/java/com/mindskip/xzs/service/impl/ExamPaperAnswerServiceImpl.java
@@ -10,6 +10,7 @@
 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.ExamPaperDataVO;
 import com.mindskip.xzs.domain.vo.ExamPaperStatisticVO;
 import com.mindskip.xzs.repository.ExamPaperAnswerMapper;
 import com.mindskip.xzs.repository.ExamPaperMapper;
@@ -349,4 +350,20 @@
         map.put("examPeopleNum", examPeopleNum);
         return map;
     }
+
+    @Override
+    public Map<String, Object> data(ExamPaperDataVO examPaperDataVO) {
+        Map<String, Object> map = new HashMap<>();
+        // 闅忔満璇曞嵎
+        if (Objects.equals(examPaperDataVO.getType(), 7)) {
+            map.put("data", examPaperAnswerMapper.dataByTemplate(examPaperDataVO));
+            map.put("total", examPaperAnswerMapper.totalByTemplate(examPaperDataVO));
+        }
+        // 鏅�氳瘯鍗�
+        else {
+            map.put("data", examPaperAnswerMapper.dataByPaper(examPaperDataVO));
+            map.put("total", examPaperAnswerMapper.totalByPaper(examPaperDataVO));
+        }
+        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 e8584a5..b33c030 100644
--- a/src/main/java/com/mindskip/xzs/service/impl/ExamPaperServiceImpl.java
+++ b/src/main/java/com/mindskip/xzs/service/impl/ExamPaperServiceImpl.java
@@ -646,4 +646,12 @@
         return PageHelper.startPage(userVO.getPageIndex(), userVO.getPageSize()).doSelectPageInfo(() ->
                 examPaperMapper.selectStudent(userVO));
     }
+
+    @Override
+    public List<ExamPaper> queryCondition() {
+        List<ExamPaper> examPaperList = examPaperMapper.getExamPaper();
+        List<ExamPaper> templateList = examPaperMapper.getTemplate();
+        examPaperList.addAll(templateList);
+        return examPaperList.stream().sorted(Comparator.comparing(ExamPaper::getCreateTime).reversed()).collect(Collectors.toList());
+    }
 }
diff --git a/src/main/resources/mapper/ExamPaperAnswerMapper.xml b/src/main/resources/mapper/ExamPaperAnswerMapper.xml
index 7b059bf..75b6094 100644
--- a/src/main/resources/mapper/ExamPaperAnswerMapper.xml
+++ b/src/main/resources/mapper/ExamPaperAnswerMapper.xml
@@ -475,20 +475,82 @@
     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>
+    <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>
+
+  <select id="dataByTemplate" resultType="java.util.Map" parameterType="com.mindskip.xzs.domain.vo.ExamPaperDataVO">
+    SELECT
+      a.id,
+      a.name,
+      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 -- 鍙傝�冪櫨鍒嗘瘮
+    FROM
+      t_department a
+        LEFT JOIN t_user_department b ON a.id = b.department_id
+        LEFT JOIN t_exam_templates_user d ON d.user_id = b.user_id AND d.templates_id = #{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)
+    GROUP BY
+      a.id, a.name
+  </select>
+
+  <select id="totalByTemplate" resultType="java.util.Map" parameterType="com.mindskip.xzs.domain.vo.ExamPaperDataVO">
+    SELECT
+           (SELECT (SELECT count(*) FROM t_exam_paper) + (SELECT count(*) FROM t_exam_templates)) 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 -- 鍙傝�冪櫨鍒嗘瘮
+    FROM
+      t_exam_templates a
+        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)
+    WHERE a.id = #{id}
+    GROUP BY
+      a.id
+  </select>
+
+  <select id="dataByPaper" resultType="java.util.Map" parameterType="com.mindskip.xzs.domain.vo.ExamPaperDataVO">
+    SELECT
+      a.id,
+      a.name,
+      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 -- 鍙傝�冪櫨鍒嗘瘮
+    FROM
+      t_department a
+        LEFT JOIN t_user_department b ON a.id = b.department_id
+        LEFT JOIN t_exam_paper_answer c ON b.user_id = c.create_user AND (c.invalid = 0 OR c.invalid IS NULL) AND c.exam_paper_id = #{id}
+        LEFT JOIN t_exam_paper_user d ON c.exam_paper_id = d.exam_paper_id and b.user_id = d.user_id
+    GROUP BY
+      a.id, a.name
+  </select>
+
+  <select id="totalByPaper" resultType="java.util.Map" parameterType="com.mindskip.xzs.domain.vo.ExamPaperDataVO">
+    SELECT
+        (SELECT (SELECT count(*) FROM t_exam_paper) + (SELECT count(*) FROM t_exam_templates)) 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
+    WHERE a.id = #{id}
+    GROUP BY a.id
+  </select>
 
 </mapper>
diff --git a/src/main/resources/mapper/ExamPaperMapper.xml b/src/main/resources/mapper/ExamPaperMapper.xml
index e69fd96..1c3206f 100644
--- a/src/main/resources/mapper/ExamPaperMapper.xml
+++ b/src/main/resources/mapper/ExamPaperMapper.xml
@@ -476,4 +476,12 @@
       select exam_paper_id from t_exam_templates_user_count where exam_templates_id = #{id}
     </select>
 
+  <select id="getExamPaper" resultType="com.mindskip.xzs.domain.ExamPaper">
+    select id, name, paper_type, create_time from t_exam_paper order by create_time desc
+  </select>
+
+  <select id="getTemplate" resultType="com.mindskip.xzs.domain.ExamPaper">
+    select id, name, paper_type, ctime as create_time from t_exam_templates order by ctime desc
+  </select>
+
 </mapper>

--
Gitblit v1.8.0