feat：人数分布统计支持随机试卷

龚焕茏

2024-05-24 d526b031d167f76ae22ad53d3221a59d38abc388

feat：人数分布统计支持随机试卷

 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>

 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;

}

 src/main/java/com/mindskip/xzs/domain/vo/ExamPaperStatisticVO.java

@@ -16,4 +16,6 @@

    private List<Integer> departmentId;

    private Integer examPaperType;

}

 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);
}

 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);

 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;
    }


 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

 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>

 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