feat：答卷统计新需求

龚焕茏

2024-05-14 b19d7869a20efec118e6e2b3384b43b53e3ffb27

feat：答卷统计新需求

 src/main/java/com/mindskip/xzs/controller/admin/ExamPaperAnswerController.java

@@ -1,28 +1,29 @@
package com.mindskip.xzs.controller.admin;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.google.gson.JsonObject;
import com.github.pagehelper.PageInfo;
import com.mindskip.xzs.base.BaseApiController;
import com.mindskip.xzs.base.RestResponse;
import com.mindskip.xzs.domain.ExamPaperAnswer;
import com.mindskip.xzs.domain.ExamPaperSubject;
import com.mindskip.xzs.domain.Subject;
import com.mindskip.xzs.domain.User;
import com.mindskip.xzs.domain.exam.ExamPaperAnswerObject;
import com.mindskip.xzs.service.*;
import com.mindskip.xzs.service.ExamPaperAnswerService;
import com.mindskip.xzs.service.ExamPaperSubjectService;
import com.mindskip.xzs.service.SubjectService;
import com.mindskip.xzs.service.UserService;
import com.mindskip.xzs.utility.DateTimeUtil;
import com.mindskip.xzs.utility.ExamUtil;
import com.mindskip.xzs.utility.PageInfoHelper;
import com.mindskip.xzs.viewmodel.student.exampaper.ExamPaperAnswerPageResponseVM;
import com.mindskip.xzs.viewmodel.admin.paper.ExamPaperAnswerPageRequestVM;
import com.github.pagehelper.PageInfo;
import com.mindskip.xzs.viewmodel.student.exampaper.ExamPaperAnswerPageResponseVM;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import javax.validation.Valid;
import java.math.BigDecimal;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

@RestController("AdminExamPaperAnswerController")
@@ -114,4 +115,9 @@
        }
        return RestResponse.ok(object);
    }

    @RequestMapping(value = "/statistic", method = RequestMethod.GET)
    public RestResponse<Map<String, Object>> statistic(String examPaperId, String departmentId) {
        return RestResponse.ok(examPaperAnswerService.statistic(examPaperId, departmentId));
    }
}

 src/main/java/com/mindskip/xzs/controller/admin/ExamPaperController.java

@@ -123,4 +123,9 @@
        List<PaperExcelVO> list = examPaperService.getPaperExcelById(id);
        ExcelUtils.export(response,"个人练习",list,PaperExcelVO.class);
    }

    @RequestMapping(value = "/list", method = RequestMethod.GET)
    public RestResponse<List<ExamPaper>> list() {
        return RestResponse.ok(examPaperService.list());
    }
}

 src/main/java/com/mindskip/xzs/repository/ExamPaperAnswerMapper.java

@@ -1,14 +1,11 @@
package com.mindskip.xzs.repository;

import com.mindskip.xzs.domain.ExamPaper;
import com.mindskip.xzs.domain.ExamPaperAnswer;
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.ScoreTemplatesCountVO;
import com.mindskip.xzs.domain.vo.TeamplatesUserExcelVO;
import com.mindskip.xzs.viewmodel.admin.exam.ExamPaperEditRequestVM;
import com.mindskip.xzs.viewmodel.admin.exam.ExamPaperEditRequestVO;
import com.mindskip.xzs.viewmodel.admin.paper.ExamPaperGradePageRequestVM;
import com.mindskip.xzs.viewmodel.admin.paper.ExamPaperGradeQuery;
import com.mindskip.xzs.viewmodel.student.exampaper.ExamPaperAnswerPageVM;
@@ -17,6 +14,7 @@

import java.util.Date;
import java.util.List;
import java.util.Map;

@Mapper
public interface ExamPaperAnswerMapper extends BaseMapper<ExamPaperAnswer> {
@@ -53,4 +51,7 @@

    void insertDefault(ExamPaperEditRequestVM model);

    Map<String, Object> histogram(@Param("examPaperId") String examPaperId, @Param("departmentId") String departmentId);

    Map<String, Object> pieChart(@Param("examPaperId") String examPaperId, @Param("departmentId") String departmentId);
}

 src/main/java/com/mindskip/xzs/repository/ExamPaperMapper.java

@@ -38,4 +38,5 @@

    List<PaperExcelVO> getPaperExcelById(@Param("id") Integer id);

    List<ExamPaper> list();
}

 src/main/java/com/mindskip/xzs/service/ExamPaperAnswerService.java

@@ -2,13 +2,13 @@

import com.github.pagehelper.PageInfo;
import com.mindskip.xzs.domain.*;
import com.mindskip.xzs.domain.vo.ScoreTemplatesCountVO;
import com.mindskip.xzs.viewmodel.admin.paper.ExamPaperGradePageRequestVM;
import com.mindskip.xzs.viewmodel.admin.paper.ExamPaperGradeQuery;
import com.mindskip.xzs.viewmodel.student.exam.ExamPaperSubmitVM;
import com.mindskip.xzs.viewmodel.student.exampaper.ExamPaperAnswerPageVM;

import java.util.List;
import java.util.Map;

public interface ExamPaperAnswerService extends BaseService<ExamPaperAnswer> {

@@ -63,4 +63,6 @@
    List<ExamPaperAnswer> getByScorePaperIdAndUserId(List<ScoreTemplatesUserCount> scoreTemplatesUserCounts);

    PageInfo<ExamPaperAnswer> getByCreatUser(ExamPaperGradeQuery query);

    Map<String, Object> statistic(String examPaperId, String departmentId);
}

 src/main/java/com/mindskip/xzs/service/ExamPaperService.java

@@ -1,5 +1,6 @@
package com.mindskip.xzs.service;

import com.github.pagehelper.PageInfo;
import com.mindskip.xzs.domain.ExamPaper;
import com.mindskip.xzs.domain.User;
import com.mindskip.xzs.domain.vo.PaperExcelVO;
@@ -9,8 +10,6 @@
import com.mindskip.xzs.viewmodel.student.dashboard.PaperFilter;
import com.mindskip.xzs.viewmodel.student.dashboard.PaperInfo;
import com.mindskip.xzs.viewmodel.student.exam.ExamPaperPageVM;
import com.github.pagehelper.PageInfo;
import org.apache.ibatis.annotations.Param;

import java.util.List;

@@ -39,4 +38,6 @@
    List<PaperExcelVO> getPaperExcelById(Integer id);

    void missExam(ExamPaperEditRequestVM model);

    List<ExamPaper> list();
}

 src/main/java/com/mindskip/xzs/service/impl/ExamPaperAnswerServiceImpl.java

@@ -10,7 +10,6 @@
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.ScoreTemplatesCountVO;
import com.mindskip.xzs.repository.ExamPaperAnswerMapper;
import com.mindskip.xzs.repository.ExamPaperMapper;
import com.mindskip.xzs.repository.QuestionMapper;
@@ -30,9 +29,7 @@
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.*;
import java.util.stream.Collectors;

@Service
@@ -313,4 +310,14 @@
        return PageHelper.startPage(query.getPageIndex(), query.getPageSize(), "id desc").doSelectPageInfo(() ->
                examPaperAnswerMapper.getByCreatUser(query));
    }

    @Override
    public Map<String, Object> statistic(String examPaperId, String departmentId) {
        HashMap<String, Object> map = new HashMap<>();
        Map<String, Object> histogram = examPaperAnswerMapper.histogram(examPaperId, departmentId);
        Map<String, Object> pieChart = examPaperAnswerMapper.pieChart(examPaperId, departmentId);
        map.put("histogram", histogram);
        map.put("pieChart", pieChart);
        return map;
    }
}

 src/main/java/com/mindskip/xzs/service/impl/ExamPaperServiceImpl.java

@@ -32,7 +32,6 @@
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.CollectionUtils;

import java.util.*;
import java.util.concurrent.atomic.AtomicInteger;
@@ -621,4 +620,9 @@
        // 补考时将没有选择的并且没有参加过考试的考生增加一条零分考试记录，否则没有参加过考试的即使不被选择也可以参加补考
        examPaperAnswerMapper.insertDefault(model);
    }

    @Override
    public List<ExamPaper> list() {
        return examPaperMapper.list();
    }
}

 src/main/resources/mapper/ExamPaperAnswerMapper.xml

@@ -395,6 +395,63 @@
    where a.create_time
  </select>

  <select id="histogram" resultType="java.util.Map">
    <![CDATA[
    SELECT
    COUNT(CASE WHEN a.user_score < 60 THEN 1 END) AS scoreLeq60,
    COUNT(CASE WHEN a.user_score >= 60 AND a.user_score < 80 THEN 1 END) AS score60To79,
    COUNT(CASE WHEN a.user_score >= 80 AND a.user_score <= 100 THEN 1 END) AS score80To100,
    COUNT(CASE WHEN TIMESTAMPDIFF(YEAR, c.birth_day, CURDATE()) BETWEEN 0 AND 19 THEN 1 END) AS age0To19,
    COUNT(CASE WHEN TIMESTAMPDIFF(YEAR, c.birth_day, CURDATE()) BETWEEN 20 AND 29 THEN 1 END) AS age20To29,
    COUNT(CASE WHEN TIMESTAMPDIFF(YEAR, c.birth_day, CURDATE()) BETWEEN 30 AND 39 THEN 1 END) AS age30To39,
    COUNT(CASE WHEN TIMESTAMPDIFF(YEAR, c.birth_day, CURDATE()) BETWEEN 40 AND 49 THEN 1 END) AS age40To49,
    COUNT(CASE WHEN TIMESTAMPDIFF(YEAR, c.birth_day, CURDATE()) >= 50 THEN 1 END) AS ageOver50
    ]]>
    FROM
    t_exam_paper_answer a
    INNER JOIN (SELECT * FROM t_user_department
    <if test="departmentId != null and departmentId != ''">
      WHERE department_id = #{departmentId}
    </if>
    GROUP BY user_id) b ON a.create_user = b.user_id
    LEFT JOIN t_user c ON a.create_user = c.id
    WHERE
    (a.invalid IS NULL OR a.invalid = 0)
    <if test="examPaperId != null and examPaperId != ''">
      AND a.exam_paper_id = #{examPaperId}
    </if>
  </select>

  <select id="pieChart" resultType="java.util.Map">
    SELECT
    <!-- 应该参加考试的人数 -->
    COUNT(DISTINCT a.user_id) AS totalExpected,
    <!-- 参加考试的人数 -->
    COUNT(DISTINCT c.create_user) AS totalAttended,
    <!-- 参加考试的百分比，并四舍五入保留两位小数 -->
    ROUND((COUNT(DISTINCT c.create_user) * 100.0 / NULLIF(COUNT(DISTINCT a.user_id), 0)), 2) AS percentageAttended,
    <!-- 参加考试的小数 -->
    COUNT(DISTINCT c.create_user) / NULLIF(COUNT(DISTINCT a.user_id), 0) as attended,
    <!-- 缺考的人数 -->
    COUNT(DISTINCT a.user_id) - COUNT(DISTINCT c.create_user) AS totalAbsent,
    <!-- 缺考的百分比，并四舍五入保留两位小数 -->
    ROUND(((COUNT(DISTINCT a.user_id) - COUNT(DISTINCT c.create_user)) * 100.0 / COUNT(DISTINCT a.user_id)), 2) AS percentageAbsent,
    <!-- 缺考的小数 -->
    ((COUNT(DISTINCT a.user_id) - COUNT(DISTINCT c.create_user)) / COUNT(DISTINCT a.user_id)) as absent
    FROM
    t_exam_paper_user a
    INNER JOIN t_exam_paper b ON a.exam_paper_id = b.id
    <if test="examPaperId != null and examPaperId != ''">
      AND b.id = #{examPaperId}
    </if>
    LEFT JOIN t_exam_paper_answer c ON a.exam_paper_id = c.exam_paper_id AND a.user_id = c.create_user AND (c.invalid IS NULL OR c.invalid = 0)
    INNER JOIN (SELECT * FROM t_user_department
    <if test="departmentId != null and departmentId != ''">
      WHERE department_id = #{departmentId}
    </if>
    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)

 src/main/resources/mapper/ExamPaperMapper.xml

@@ -423,4 +423,8 @@
    where a.exam_paper_id = #{id}
  </select>

    <select id="list" resultType="com.mindskip.xzs.domain.ExamPaper">
    select id, name from t_exam_paper order by create_time desc
    </select>

</mapper>