src/main/java/org/springblade/modules/exam/controller/ExamPaperController.java
@@ -278,12 +278,13 @@ public R updateAudit(@RequestBody ExamPaper examPaper){ //查询之前的审核状态，如果审核状态没有发生改变，则不会去修改审核信息 ExamPaper paper = examPaperService.getById(examPaper.getId()); boolean status = false; if (null!=paper.getAuditStatus()){ //审核状态发生改变 if (!paper.getAuditStatus().equals(examPaper.getAuditStatus())){ // if (!paper.getAuditStatus().equals(examPaper.getAuditStatus())){ examPaper.setAuditTime(new Date()); //修改考试审核状态 boolean status = examPaperService.updateById(examPaper); status = examPaperService.updateById(examPaper); //数据同步 String s1 = "update ksxt_exam set audit_status = " + "'" + examPaper.getAuditStatus()+ "'" @@ -308,8 +309,10 @@ } } } //生成准考证号 trainingRegistration.setCandidateNo(getCandidateNo(examPaper)); if(null==trainingRegistration.getCandidateNo() || trainingRegistration.getCandidateNo().equals("")){ //生成准考证号 trainingRegistration.setCandidateNo(getCandidateNo(examPaper)); } } //审核不通过 if (examPaper.getAuditStatus()==2) { @@ -330,10 +333,10 @@ }); } return R.status(status); } // } } //返回 return R.status(false); return R.status(status); } /** @@ -430,6 +433,17 @@ return R.data(pages); } /** * 根据考试id 统计查询考试信息 * @param examPaperVO * @return */ @GetMapping("/getExamInfoByExamId") public R getExamInfoByExamId(ExamStatisticsVO examPaperVO, Query query){ IPage<ExamStatisticsVO> pages = examPaperService.getExamInfoByExamId(Condition.getPage(query), examPaperVO); return R.data(pages); } } src/main/java/org/springblade/modules/exam/mapper/ExamPaperMapper.java
@@ -141,4 +141,11 @@ * @return */ List<ExamStatisticsVO> getExamStatistics(@Param("page") IPage<ExamStatisticsVO> page,@Param("examPaper") ExamPaperVO examPaper); /** * 根据考试id 统计查询考试信息 * @param examPaperVO * @return */ List<ExamStatisticsVO> getExamInfoByExamId(@Param("page") IPage<ExamStatisticsVO> page,@Param("examPaper") ExamStatisticsVO examPaperVO); } src/main/java/org/springblade/modules/exam/mapper/ExamPaperMapper.xml
@@ -262,7 +262,7 @@ <!--查询当前考试已报名的人员培训报名数据集合--> <select id="getTrainList" resultType="org.springblade.modules.training.entity.TrainingRegistration"> SELECT str.id,str.user_id userId str.id,str.user_id userId,str.candidate_no candidateNo FROM sys_training_registration str left join @@ -305,7 +305,7 @@ on bu.id = str.user_id where bu.is_deleted = 0 and bu.status = 1 1=1 and train_exam_id = #{examPaper.id} </select> @@ -378,38 +378,94 @@ on bu.id = str.user_id where bu.is_deleted = 0 and bu.status = 1 1=1 and train_exam_id = #{examPaper.id} and (is_exam = 2 or is_exam = 3) and cancel = 1 </select> <!--考试查询统计--> <!-- <select id="getExamStatistics" resultType="org.springblade.modules.exam.vo.ExamStatisticsVO">--> <!-- SELECt--> <!-- ke.id,ke.start_time startTime,ke.end_time endTime,bd.dept_name schoolName,--> <!-- ifnull(a.c,0) znum,--> <!-- ifnull(b.c,0) yknum,--> <!-- ifnull(c.c,0) qknum,--> <!-- ifnull(d.c,0) ycnum,--> <!-- ifnull(e.c,0) qxnum--> <!-- from ksxt_exam ke--> <!-- left join (select count(*) c,train_exam_id from sys_training_registration GROUP BY train_exam_id) a on a.train_exam_id = ke.id--> <!-- left join (select count(*) c,train_exam_id from sys_training_registration where 1=1 and (is_exam = 2 or is_exam = 3) GROUP BY train_exam_id) b on b.train_exam_id = ke.id--> <!-- left join (select count(*) c,train_exam_id from sys_training_registration str left join blade_user bu on bu.id = str.user_id where 1=1 and is_exam = 4 and cancel =2 or (is_exam=1 and cancel =1) and bu.examination_type!=1 GROUP BY train_exam_id) c on c.train_exam_id = ke.id--> <!-- left join (select count(*) c,train_exam_id from sys_training_registration str left join blade_user bu on bu.id = str.user_id where 1=1 and cancel =1 and is_exam = 1 and bu.examination_type = 1 GROUP BY train_exam_id) d on d.train_exam_id = ke.id--> <!-- left join (select count(*) c,train_exam_id from sys_training_registration where 1=1 and is_exam = 1 and cancel =2 GROUP BY train_exam_id) e on e.train_exam_id = ke.id--> <!-- left join blade_user bu2 on bu2.id = ke.creator--> <!-- left join blade_dept bd on bd.id = bu2.dept_id--> <!-- where 1=1--> <!-- and ke.audit_status = 1--> <!-- <if test="examPaper.schoolName!=null and examPaper.schoolName!=''">--> <!-- and bd.dept_name like concat('%',#{examPaper.schoolName},'%')--> <!-- </if>--> <!-- <if test="examPaper.examTime!=null and examPaper.examTime!=''">--> <!-- and date_format(ke.start_time,'%Y-%m-%d') = #{examPaper.examTime}--> <!-- </if>--> <!-- order by ke.id desc--> <!-- </select> --> <!--考试查询统计,按天，培训学校分组--> <select id="getExamStatistics" resultType="org.springblade.modules.exam.vo.ExamStatisticsVO"> SELECt ke.id,ke.start_time startTime,ke.end_time endTime,bd.dept_name schoolName, si.enterpriseName schoolName, a.time startTime, a.training_unit_id trainUnitId, ifnull(a.c,0) znum, ifnull(b.c,0) yknum, ifnull(f.c,0) zznum, ifnull(c.c,0) qknum, ifnull(d.c,0) ycnum, ifnull(e.c,0) qxnum from ksxt_exam ke left join (select count(*) c,train_exam_id from sys_training_registration GROUP BY train_exam_id) a on a.train_exam_id = ke.id left join (select count(*) c,train_exam_id from sys_training_registration where 1=1 and (is_exam = 2 or is_exam = 3) GROUP BY train_exam_id) b on b.train_exam_id = ke.id left join (select count(*) c,train_exam_id from sys_training_registration str left join blade_user bu on bu.id = str.user_id where 1=1 and is_exam = 4 and cancel =2 or (is_exam=1 and cancel =1) and bu.examination_type!=1 GROUP BY train_exam_id) c on c.train_exam_id = ke.id left join (select count(*) c,train_exam_id from sys_training_registration str left join blade_user bu on bu.id = str.user_id where 1=1 and cancel =1 and is_exam = 1 and bu.examination_type = 1 GROUP BY train_exam_id) d on d.train_exam_id = ke.id left join (select count(*) c,train_exam_id from sys_training_registration where 1=1 and is_exam = 1 and cancel =2 GROUP BY train_exam_id) e on e.train_exam_id = ke.id left join blade_user bu2 on bu2.id = ke.creator left join blade_dept bd on bd.id = bu2.dept_id from (select date_format(ke.start_time,'%Y-%m-%d') time,str.training_unit_id,count(*) c from sys_training_registration str left join ksxt_exam ke on ke.id = str.train_exam_id where 1=1 and ke.audit_status =1 GROUP BY date_format(ke.start_time,'%Y-%m-%d'),str.training_unit_id ) a left join (select count(*) c,date_format(ke.start_time,'%Y-%m-%d') time,str.training_unit_id from sys_training_registration str left join ksxt_exam ke on ke.id = str.train_exam_id where 1=1 and ke.audit_status =1 and (is_exam = 2 or is_exam = 3) GROUP BY date_format(ke.start_time,'%Y-%m-%d'),str.training_unit_id ) b on a.time = b.time and a.training_unit_id = b.training_unit_id left join (select count(*) c,date_format(ke.start_time,'%Y-%m-%d') time,str.training_unit_id from sys_training_registration str left join ksxt_exam ke on ke.id = str.train_exam_id where 1=1 and ke.audit_status =1 and is_exam = 5 GROUP BY date_format(ke.start_time,'%Y-%m-%d'),str.training_unit_id ) f on a.time = f.time and a.training_unit_id = f.training_unit_id left join (select count(*) c,date_format(ke.start_time,'%Y-%m-%d') time,str.training_unit_id from sys_training_registration str left join ksxt_exam ke on ke.id = str.train_exam_id left join blade_user bu on bu.id = str.user_id where 1=1 and is_exam = 4 GROUP BY date_format(ke.start_time,'%Y-%m-%d'),str.training_unit_id ) c on a.time = c.time and a.training_unit_id = c.training_unit_id left join (select count(*) c,date_format(ke.start_time,'%Y-%m-%d') time,str.training_unit_id from sys_training_registration str left join ksxt_exam ke on ke.id = str.train_exam_id left join blade_user bu on bu.id = str.user_id where 1=1 and cancel =1 and is_exam = 1 and bu.examination_type = 1 GROUP BY date_format(ke.start_time,'%Y-%m-%d'),str.training_unit_id ) d on a.time = d.time and a.training_unit_id = d.training_unit_id left join (select count(*) c,date_format(ke.start_time,'%Y-%m-%d') time,str.training_unit_id from sys_training_registration str left join ksxt_exam ke on ke.id = str.train_exam_id where 1=1 and is_exam = 1 and cancel =2 GROUP BY date_format(ke.start_time,'%Y-%m-%d'),str.training_unit_id ) e on a.time = e.time and a.training_unit_id = e.training_unit_id left join sys_information si on si.departmentid = a.training_unit_id where 1=1 and ke.audit_status = 1 <if test="examPaper.schoolName!=null and examPaper.schoolName!=''"> and bd.dept_name like concat('%',#{examPaper.schoolName},'%') and si.enterpriseName like concat('%',#{examPaper.schoolName},'%') </if> <if test="examPaper.examTime!=null and examPaper.examTime!=''"> and date_format(ke.start_time,'%Y-%m-%d') = #{examPaper.examTime} and a.time = #{examPaper.examTime} </if> order by ke.id desc order by a.time desc,si.enterpriseName desc </select> <!--根据考试id 统计查询考试信息--> <select id="getExamInfoByExamId" resultType="org.springblade.modules.exam.vo.ExamStatisticsVO"> select a.dept_id id, 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.startTime} 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.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 </select> </mapper> src/main/java/org/springblade/modules/exam/mapper/ExamScoreMapper.xml
@@ -93,7 +93,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 ke.exam_name like concat('%', #{examScore.examName},'%') </if> src/main/java/org/springblade/modules/exam/service/ExamPaperService.java
@@ -116,4 +116,11 @@ * @return */ IPage<ExamStatisticsVO> getExamStatistics(IPage<ExamStatisticsVO> page, ExamPaperVO examPaperVO); /** * 根据考试id 统计查询考试信息 * @param examPaperVO * @return */ IPage<ExamStatisticsVO> getExamInfoByExamId(IPage<ExamStatisticsVO> page, ExamStatisticsVO examPaperVO); } src/main/java/org/springblade/modules/exam/service/impl/ExamPaperServiceImpl.java
@@ -30,6 +30,7 @@ import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service; import java.text.DecimalFormat; import java.text.SimpleDateFormat; import java.util.*; @@ -268,4 +269,27 @@ public IPage<ExamStatisticsVO> getExamStatistics(IPage<ExamStatisticsVO> page, ExamPaperVO examPaperVO) { return page.setRecords(baseMapper.getExamStatistics(page,examPaperVO)); } /** * 根据考试id 统计查询考试信息 * @param examPaperVO * @return */ @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 -> { //格式化小数 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 page.setRecords(statisticsVOS); } } src/main/java/org/springblade/modules/exam/service/impl/ScoreAuditRecordsServiceImpl.java
@@ -92,8 +92,15 @@ ExamScore examScore = examScoreService.getById(auditRecords.getExamScoreId()); //1.修改培训报名考试状态 TrainingRegistration trainingRegistration = trainingRegistrationService.getById(examScore.getApplyId()); //已考试 trainingRegistration.setIsExam(2); //考试状态修改 if(trainingRegistration.getIsExam()==4) { //考试状态修改为纸质考试 trainingRegistration.setIsExam(5); trainingRegistration.setCancel(1); }else { //考试结束 trainingRegistration.setIsExam(2); } //修改 trainingRegistrationService.updateById(trainingRegistration); @@ -135,6 +142,7 @@ ",qualified = " + "'" + examScore.getQualified() + "'" + " " + "where id = " + "'" + examScore.getId() + "';"+ "update sys_training_registration set is_exam = " + "'" + trainingRegistration.getIsExam() + "'" + ",cancel = " + "'" + trainingRegistration.getCancel() + "'" + " " + "where id = " + "'" + trainingRegistration.getId() + "';"+ "update blade_user set is_train = " + "'" + user.getIsTrain() + "'" + " " + "where id = " + "'" + user.getId() + "'"; @@ -223,6 +231,7 @@ ",qualified = " + "'" + examScore.getQualified() + "'" + " " + "where id = " + "'" + examScore.getId() + "';"+ "update sys_training_registration set is_exam = " + "'" + trainingRegistration.getIsExam() + "'" + ",cancel = " + "'" + trainingRegistration.getCancel() + "'" + " " + "where id = " + "'" + trainingRegistration.getId() + "';"+ "update blade_user set hold = " + "'" + user.getHold() + "'" + ",securitynumber = " + "'" + user.getSecuritynumber() + "'" + src/main/java/org/springblade/modules/exam/vo/ExamStatisticsVO.java
@@ -12,6 +12,11 @@ private Long id; /** * 培训学校id */ private String trainUnitId; /** * 培训学校名称 */ private String schoolName; @@ -37,6 +42,11 @@ private Integer yknum; /** * 纸质考试人数 */ private Integer zznum; /** * 缺考/未考试/笔试人数 */ private Integer qknum; @@ -51,5 +61,20 @@ */ private Integer qxnum; /** * 企业名称 */ private String deptName; /** * 缺考占比 */ private String absentProportion; /** * 合格占比 */ private String qualifiedProportion; } src/main/java/org/springblade/modules/system/mapper/UserMapper.xml
@@ -985,7 +985,12 @@ <if test="user.cardid!=null and user.cardid != ''"> and bu.cardid like concat('%', #{user.cardid},'%') </if> ORDER BY bu.id desc <if test="user.sortName!=null and user.sortName!=''"> ORDER BY bu.${user.sortName} ${user.sort},bu.id desc </if> <if test="user.sortName==null or user.sortName==''"> ORDER BY bu.id desc </if> </select> <!--查询押运人员编码不为null的保安信息--> src/main/java/org/springblade/modules/system/service/impl/UserServiceImpl.java
@@ -20,6 +20,7 @@ import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper; import com.baomidou.mybatisplus.core.metadata.IPage; import com.baomidou.mybatisplus.core.toolkit.Wrappers; import com.google.zxing.WriterException; import lombok.AllArgsConstructor; import org.apache.commons.lang3.StringUtils; import org.springblade.common.cache.SysCache; @@ -64,6 +65,8 @@ import java.io.FileNotFoundException; import java.io.FileOutputStream; import java.io.IOException; import java.io.UnsupportedEncodingException; import java.net.URLEncoder; import java.text.SimpleDateFormat; import java.util.*; import java.util.concurrent.atomic.AtomicBoolean; @@ -1584,49 +1587,53 @@ */ @Override public void importQrCode(List<QrCodeExcel> data, Boolean isCovered, String deptId) { // String url = "http://223.82.109.183:2080/securityInfo.html"; // data.forEach(qrCodeExcel -> { // if (null!=qrCodeExcel.getSecuritynumber()) { // String encoded = null; // try { // //中文字符串编码 // encoded = URLEncoder.encode(qrCodeExcel.getSecuritynumber(),"UTF-8"); // } catch (UnsupportedEncodingException e) { // e.printStackTrace(); // } // //url 拼接 // String content = url + "?securityNumber=" + encoded; // byte[] qrCodeImage = new byte[0]; // try { // //生成二维码字节流 // qrCodeImage = QRCodeUtil.getQRCodeImage(content, 350, 350); // } catch (WriterException e) { // e.printStackTrace(); // } catch (IOException e) { // e.printStackTrace(); // } // String path = "D:\\QrCode\\"; // FileOutputStream fileOutputStream = null; // try { // fileOutputStream = new FileOutputStream(path+qrCodeExcel.getSecuritynumber()+".png"); // } catch (FileNotFoundException e) { // e.printStackTrace(); // } // try { // //图片下载到本地 // fileOutputStream.write(qrCodeImage,0,qrCodeImage.length); // fileOutputStream.flush(); // fileOutputStream.close(); // } catch (IOException e) { // e.printStackTrace(); // } // } // }); String url = "http://223.82.109.183:2080/securityInfo.html"; //二维码生成 data.forEach(qrCodeExcel -> { if (null!=qrCodeExcel.getSecuritynumber()) { String encoded = null; try { //中文字符串编码 encoded = URLEncoder.encode(qrCodeExcel.getSecuritynumber(),"UTF-8"); } catch (UnsupportedEncodingException e) { e.printStackTrace(); } //url 拼接 String content = url + "?securityNumber=" + encoded; byte[] qrCodeImage = new byte[0]; try { //生成二维码字节流 qrCodeImage = QRCodeUtil.getQRCodeImage(content, 350, 350); } catch (WriterException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } String path = "D:\\QrCode\\"; FileOutputStream fileOutputStream = null; try { fileOutputStream = new FileOutputStream(path+qrCodeExcel.getSecuritynumber()+".png"); } catch (FileNotFoundException e) { e.printStackTrace(); } try { //图片下载到本地 fileOutputStream.write(qrCodeImage,0,qrCodeImage.length); fileOutputStream.flush(); fileOutputStream.close(); } catch (IOException e) { e.printStackTrace(); } } }); //头像生成 data.forEach(qrCodeExcel -> { if (null!=qrCodeExcel.getSecuritynumber()) { //查询用户头像url User user = new User(); user.setRealName(qrCodeExcel.getRealName()); user.setStatus(1); user.setIsDeleted(0); user.setSecuritynumber(qrCodeExcel.getSecuritynumber()); User user1 = this.getOne(Condition.getQueryWrapper(user)); //url 拼接 src/main/java/org/springblade/modules/training/mapper/TrainingRegistrationMapper.xml
@@ -32,8 +32,6 @@ ke.id = sr.train_exam_id WHERE 1=1 and bu.status = 1 and bu.is_deleted = 0 <if test="trainingRegistration.isExam!=null and trainingRegistration.isExam!=0"> and is_exam = #{trainingRegistration.isExam} </if> @@ -130,8 +128,6 @@ ke.id = sr.train_exam_id WHERE 1=1 and bu.status = 1 and bu.is_deleted = 0 <if test="trainingRegistration.isExam!=null and trainingRegistration.isExam!=0"> and is_exam = #{trainingRegistration.isExam} </if> @@ -271,8 +267,6 @@ ke.id = sr.train_exam_id WHERE 1=1 and bu.status = 1 and bu.is_deleted = 0 <if test="trainingRegistration.isExam!=null"> and is_exam = #{trainingRegistration.isExam} </if> src/main/java/org/springblade/modules/training/service/impl/TrainingRegistrationServiceImpl.java
@@ -346,13 +346,14 @@ //考试截止时间小于当前时间，说明已过考试时间 if (exanEndTime.before(now)) { //修改报名状态 trainingRegistration1.setCancel(2); trainingRegistration1.setCancel(1); //缺考标记 trainingRegistration1.setIsExam(4); this.updateById(trainingRegistration1); //修改保安报名状态 User user = userService.getById(trainingRegistration1.getUserId()); user.setIsTrain(2); //考试结束 user.setIsTrain(3); userService.updateById(user); //生成缺考记录 AbsentRecords absentRecords = new AbsentRecords();
