src/main/java/org/springblade/modules/exam/controller/ExamPaperController.java
@@ -16,39 +16,50 @@ */ package org.springblade.modules.exam.controller; import com.alibaba.excel.EasyExcel; import com.alibaba.excel.ExcelWriter; import com.alibaba.excel.write.metadata.WriteSheet; import com.alibaba.excel.write.metadata.fill.FillConfig; import com.alibaba.excel.write.metadata.fill.FillWrapper; import com.baomidou.mybatisplus.core.metadata.IPage; import com.github.xiaoymin.knife4j.annotations.ApiOperationSupport; import io.swagger.annotations.ApiOperation; import io.swagger.annotations.ApiParam; import lombok.AllArgsConstructor; import org.apache.commons.codec.Charsets; import org.springblade.core.boot.ctrl.BladeController; import org.springblade.core.excel.util.ExcelUtil; import org.springblade.core.mp.support.Condition; import org.springblade.core.mp.support.Query; import org.springblade.core.tool.api.R; import org.springblade.core.tool.utils.DateUtil; import org.springblade.core.tool.utils.Func; import org.springblade.modules.FTP.FtpUtil; import org.springblade.modules.apply.entity.Apply; import org.springblade.modules.exam.entity.ExamPaper; import org.springblade.modules.exam.entity.ExamSubjectChoices; import org.springblade.modules.exam.excel.ExportExamScoreExcel; import org.springblade.modules.exam.service.ExamPaperService; import org.springblade.modules.exam.vo.ExamPaperSubjectVO; import org.springblade.modules.exam.vo.ExamPaperVO; import org.springblade.modules.exam.vo.ExamStatisticsVO; import org.springblade.modules.exam.vo.ExamSubjectChoicesVO; import org.springblade.modules.exam.vo.*; import org.springblade.modules.exam.wrapper.ExamPaperWrapper; import org.springblade.modules.system.entity.User; import org.springblade.modules.system.service.IUserService; import org.springblade.modules.training.entity.TrainExam; import org.springblade.modules.training.entity.TrainingRegistration; import org.springblade.modules.training.service.TrainingRegistrationService; import org.springframework.core.io.ClassPathResource; import org.springframework.web.bind.annotation.*; import springfox.documentation.annotations.ApiIgnore; import javax.servlet.http.HttpServletResponse; import java.io.File; import java.io.IOException; import java.io.InputStream; import java.net.URLEncoder; import java.text.DecimalFormat; import java.text.SimpleDateFormat; import java.util.Date; import java.util.List; import java.util.Map; import java.util.*; import java.util.stream.Collectors; /** * 控制器 @@ -444,6 +455,72 @@ return R.data(pages); } /** * 导出考试统计情况信息 * @param examScoreVO 需要传三个参数 1：培训学校名称，2:培训学校id 3:日期 4：考试类型，培训考试 */ @GetMapping("export-examStatistics") public void exportExamStatistics(HttpServletResponse response, ExamScoreVO examScoreVO) throws Exception { try{ //获取考试情况统计数据 List<ExamStatisticsVO> list1 = examPaperService.getExamInfoByExamIdList(examScoreVO); //获取考试成绩明细 List<ExamScoreVO> list2 = examPaperService.getExamScoreList(examScoreVO); //模板路径 String templateFileName = "xls" + File.separator + "examStatistics.xlsx"; //读取模板输入流 InputStream resourceAsStream = this.getClass().getClassLoader().getResourceAsStream(templateFileName); //设置文件名称 String fileName = URLEncoder.encode(examScoreVO.getCompanyName()+examScoreVO.getExamDate()+"考试情况数据"+ DateUtil.time(), Charsets.UTF_8.name()); //设置文件类型 response.setContentType("application/vnd.ms-excel"); //设置编码格式 response.setCharacterEncoding("utf-8"); response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx"); //创建excel ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).withTemplate(resourceAsStream).build(); //创建sheet WriteSheet writeSheet = EasyExcel.writerSheet().build(); FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build(); // 如果有多个list 模板上必须有{前缀.} 这里的前缀就是 a，然后多个list必须用 FillWrapper包裹 excelWriter.fill(new FillWrapper("a", list1), fillConfig, writeSheet); excelWriter.fill(new FillWrapper("b", list2), fillConfig, writeSheet); //其他数据写入 map Map map = new HashMap<>(); //计算总的报名人数，异常人数，缺考人数，合格人数， int znum = list1.stream().mapToInt(ExamStatisticsVO::getZnum).sum(); int ycnum = list1.stream().mapToInt(ExamStatisticsVO::getYcnum).sum(); int qknum = list1.stream().mapToInt(ExamStatisticsVO::getQknum).sum(); int yknum = list1.stream().mapToInt(ExamStatisticsVO::getYknum).sum(); map.put("znumTotal",znum); map.put("ycnumTotal",ycnum); map.put("qknumTotal",qknum); map.put("yknumTotal",yknum); //计算占比，格式化小数 DecimalFormat df = new DecimalFormat("0.00"); //返回的是String类型,缺考人数占比 map.put("absentProportionTotal",df.format((float)qknum/znum*100)); //合格人数占比 DecimalFormat df1 = new DecimalFormat("0.00"); map.put("qualifiedProportionTotal",df1.format((float)yknum/znum*100)); excelWriter.fill(map, writeSheet); //关闭流 excelWriter.finish(); }catch (Exception e){ e.printStackTrace(); } } } src/main/java/org/springblade/modules/exam/mapper/ExamPaperMapper.java
@@ -23,10 +23,7 @@ import org.springblade.modules.exam.entity.ExamExaminationSubject; import org.springblade.modules.exam.entity.ExamPaper; import org.springblade.modules.exam.entity.ExamSubjectChoices; import org.springblade.modules.exam.vo.ExamPaperSubjectVO; import org.springblade.modules.exam.vo.ExamPaperVO; import org.springblade.modules.exam.vo.ExamStatisticsVO; import org.springblade.modules.exam.vo.ExamSubjectChoicesVO; import org.springblade.modules.exam.vo.*; import org.springblade.modules.training.entity.TrainingRegistration; import java.util.List; @@ -148,4 +145,18 @@ * @return */ List<ExamStatisticsVO> getExamInfoByExamId(@Param("page") IPage<ExamStatisticsVO> page,@Param("examPaper") ExamStatisticsVO examPaperVO); /** * 根据考试id 统计查询考试信息 * @param examScoreVO * @return */ List<ExamStatisticsVO> getExamInfoByExamIdList(@Param("examPaper") ExamScoreVO examScoreVO); /** * 查询考试成绩明细 * @param examScoreVO * @return */ List<ExamScoreVO> getExamScoreList(@Param("examScore") ExamScoreVO examScoreVO); } src/main/java/org/springblade/modules/exam/mapper/ExamPaperMapper.xml
@@ -465,7 +465,73 @@ left join blade_dept bd on a.dept_id = bd.id left join (select bu.dept_id,count(*) c from sys_training_registration str left join blade_user bu on bu.id = str.user_id left join ksxt_exam ke on ke.id = str.train_exam_id where str.training_unit_id = #{examPaper.trainUnitId} and date_format(ke.start_time,'%Y-%m-%d') = #{examPaper.startTime} and cancel =1 and is_exam = 1 and bu.examination_type = 1 GROUP BY bu.dept_id) d on d.dept_id = a.dept_id left join (select bu.dept_id,count(*) c from sys_training_registration str left join blade_user bu on bu.id = str.user_id left join ksxt_exam ke on ke.id = str.train_exam_id where str.training_unit_id = #{examPaper.trainUnitId} and date_format(ke.start_time,'%Y-%m-%d') = #{examPaper.startTime} and is_exam = 4 GROUP BY bu.dept_id) b on b.dept_id = a.dept_id left join (select bu.dept_id,count(*) c from exam_score es left join blade_user bu on bu.id = es.user_id left join ksxt_exam ke on ke.id = es.exam_id left join sys_training_registration str on str.id = es.apply_id where str.training_unit_id = #{examPaper.trainUnitId} and date_format(ke.start_time,'%Y-%m-%d') = #{examPaper.startTime} and es.qualified = 0 GROUP BY bu.dept_id) c on c.dept_id = a.dept_id left join (select bu.dept_id,count(*) c from exam_score es left join blade_user bu on bu.id = es.user_id left join ksxt_exam ke on ke.id = es.exam_id left join sys_training_registration str on str.id = es.apply_id where str.training_unit_id = #{examPaper.trainUnitId} and date_format(ke.start_time,'%Y-%m-%d') = #{examPaper.startTime} and es.theory_grade >= 60 GROUP BY bu.dept_id) c on c.dept_id = a.dept_id </select> <!--根据考试id 统计查询考试信息--> <select id="getExamInfoByExamIdList" resultType="org.springblade.modules.exam.vo.ExamStatisticsVO"> select (@i:=@i+1) no,aa.* from ( select bd.dept_name deptName, ifnull(a.c,0) znum, ifnull(b.c,0) qknum, ifnull(d.c,0) ycnum, ifnull(c.c,0) yknum from (select bu.dept_id,count(*) c from sys_training_registration str left join blade_user bu on bu.id = str.user_id left join ksxt_exam ke on ke.id = str.train_exam_id where str.training_unit_id = #{examPaper.trainUnitId} and date_format(ke.start_time,'%Y-%m-%d') = #{examPaper.examDate} GROUP BY bu.dept_id) a left join blade_dept bd on a.dept_id = bd.id left join (select bu.dept_id,count(*) c from sys_training_registration str left join blade_user bu on bu.id = str.user_id left join ksxt_exam ke on ke.id = str.train_exam_id where str.training_unit_id = #{examPaper.trainUnitId} and date_format(ke.start_time,'%Y-%m-%d') = #{examPaper.examDate} and cancel =1 and is_exam = 1 and bu.examination_type = 1 GROUP BY bu.dept_id) d on d.dept_id = a.dept_id left join (select bu.dept_id,count(*) c from sys_training_registration str left join blade_user bu on bu.id = str.user_id left join ksxt_exam ke on ke.id = str.train_exam_id where str.training_unit_id = #{examPaper.trainUnitId} and date_format(ke.start_time,'%Y-%m-%d') = #{examPaper.examDate} and is_exam = 4 GROUP BY bu.dept_id) b on b.dept_id = a.dept_id left join (select bu.dept_id,count(*) c from exam_score es left join blade_user bu on bu.id = es.user_id left join ksxt_exam ke on ke.id = es.exam_id left join sys_training_registration str on str.id = es.apply_id where str.training_unit_id = #{examPaper.trainUnitId} and date_format(ke.start_time,'%Y-%m-%d') = #{examPaper.examDate} and es.theory_grade >= 60 GROUP BY bu.dept_id) c on c.dept_id = a.dept_id ) aa,(select @i:=0) bb </select> <!--导出-按条件查询成绩数据-模拟考试--> <select id="getExamScoreList" resultType="org.springblade.modules.exam.vo.ExamScoreVO"> select (@i:=@i+1) no,aa.* from ( SELECT bu.real_name securityName, bd.dept_name companyName, es.theory_grade theoryGrade,es.learn_grade learnGrade,es.all_grade allGrade, case when str.is_exam=2 then "发布成绩" when str.is_exam=3 then "考试中" when str.is_exam=4 then "缺考" when str.is_exam=5 then "纸质考试" else "" end as sortName from exam_score es left join ksxt_exam ke on ke.id = es.exam_id left join blade_user bu on es.user_id = bu.id left join blade_dept bd on bd.id = bu.dept_id left join sys_training_registration str on str.id = es.apply_id where 1=1 and (str.is_exam = 2 or str.is_exam = 3 or str.is_exam = 4 or str.is_exam = 5) <if test="examScore.trainUnitId!=null"> and str.training_unit_id = #{examScore.trainUnitId} </if> <if test="examScore.isExam!=null and examScore.isExam!=''"> and str.is_exam = #{examScore.isExam} </if> <if test="examScore.examType!=null and examScore.examType!=''"> and ke.exam_type = #{examScore.examType} </if> <if test="examScore.examDate!=null and examScore.examDate!=''"> and date_format(ke.start_time,'%Y-%m-%d') = #{examScore.examDate} </if> order by es.id desc ) aa,(select @i:=0) bb </select> </mapper> src/main/java/org/springblade/modules/exam/mapper/ExamScoreMapper.xml
@@ -381,7 +381,7 @@ str.id = es.apply_id where 1=1 and (str.is_exam = 2 or str.is_exam = 3 or str.is_exam = 4) and (str.is_exam = 2 or str.is_exam = 3 or str.is_exam = 4 or str.is_exam = 5) <if test="examScore.examName!=null and examScore.examName!='' and examScore.examName!='undefined'"> and ke.exam_name like concat('%', #{examScore.examName},'%') </if> src/main/java/org/springblade/modules/exam/service/ExamPaperService.java
@@ -123,4 +123,18 @@ * @return */ IPage<ExamStatisticsVO> getExamInfoByExamId(IPage<ExamStatisticsVO> page, ExamStatisticsVO examPaperVO); /** * 根据考试id 统计查询考试信息 * @param examScoreVO * @return */ List<ExamStatisticsVO> getExamInfoByExamIdList(ExamScoreVO examScoreVO); /** * 查询考试成绩明细 * @param examScoreVO * @return */ List<ExamScoreVO> getExamScoreList(ExamScoreVO examScoreVO); } src/main/java/org/springblade/modules/exam/service/impl/ExamPaperServiceImpl.java
@@ -278,7 +278,6 @@ @Override public IPage<ExamStatisticsVO> getExamInfoByExamId(IPage<ExamStatisticsVO> page, ExamStatisticsVO examPaperVO) { List<ExamStatisticsVO> statisticsVOS = baseMapper.getExamInfoByExamId(page, examPaperVO); System.out.println("statisticsVOS = " + statisticsVOS); //遍历，计算占比 if (statisticsVOS.size()>0){ statisticsVOS.forEach(examStatisticsVO -> { @@ -292,4 +291,36 @@ } return page.setRecords(statisticsVOS); } /** * 根据考试id 统计查询考试信息 * @param examScoreVO * @return */ @Override public List<ExamStatisticsVO> getExamInfoByExamIdList(ExamScoreVO examScoreVO) { List<ExamStatisticsVO> statisticsVOS = baseMapper.getExamInfoByExamIdList(examScoreVO); //遍历，计算占比 if (statisticsVOS.size()>0){ statisticsVOS.forEach(examStatisticsVO -> { //格式化小数 DecimalFormat df = new DecimalFormat("0.00"); //返回的是String类型 examStatisticsVO.setAbsentProportion(df.format((float)examStatisticsVO.getQknum()/examStatisticsVO.getZnum()*100)); DecimalFormat df1 = new DecimalFormat("0.00"); examStatisticsVO.setQualifiedProportion(df1.format((float)examStatisticsVO.getYknum()/examStatisticsVO.getZnum()*100)); }); } return statisticsVOS; } /** * 查询考试成绩明细 * @param examScoreVO * @return */ @Override public List<ExamScoreVO> getExamScoreList(ExamScoreVO examScoreVO) { return baseMapper.getExamScoreList(examScoreVO); } } src/main/java/org/springblade/modules/exam/vo/ExamScoreVO.java
@@ -45,6 +45,11 @@ private Integer isExam; /** * 下标 */ private Integer no; /** * 用户名 */ private String account; src/main/java/org/springblade/modules/exam/vo/ExamStatisticsVO.java
@@ -62,6 +62,11 @@ private Integer qxnum; /** * 下标 */ private Integer no; /** * 企业名称 */ private String deptName; src/main/resources/xls/examStatistics.xlsxBinary files differ
