feat：统计新需求

龚焕茏

2024-05-23 00fe3336b89c3c24ffe2826f45419d8e7dbb17fc

feat：统计新需求

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

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

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

New file
@@ -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;

}

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

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

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

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

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

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

 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>

 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>