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