src/main/java/org/springblade/modules/exam/controller/ExamScoreController.java
@@ -311,7 +311,7 @@ examScoreExcel.setExamName("2021年10月**日保安员证培训考试"); examScoreExcel.setName("张三"); examScoreExcel.setIdCardNo("360XXX19XXXXXX****"); examScoreExcel.setCandidateNo("2110**m0001"); // examScoreExcel.setCandidateNo("2110**m0001"); examScoreExcel.setLearnGrade(80); list.add(examScoreExcel); @@ -319,7 +319,7 @@ examScoreExcel1.setExamName("2021年10月**日保安员证培训考试"); examScoreExcel1.setName("李四"); examScoreExcel1.setIdCardNo("360XXX19XXXXXX****"); examScoreExcel1.setCandidateNo("2110**m0002"); // examScoreExcel1.setCandidateNo("2110**m0002"); examScoreExcel1.setLearnGrade(88); list.add(examScoreExcel1); // ExcelUtil.export(response, "实操成绩数据模板", "实操成绩数据表", list, ExamScoreExcel.class); @@ -421,7 +421,7 @@ //按条件查询成绩数据 List<ExportExamScoreExcel> examScoreExcels = examScoreService.exportExamScoreList(examScoreVO); //导出成绩集合数据 ExcelUtil.export(response, "成绩数据", "成绩数据表", examScoreExcels, ExportExamScoreExcel.class); ExcelUtil.export(response, "成绩数据"+DateUtil.time(), "成绩数据表", examScoreExcels, ExportExamScoreExcel.class); } /** src/main/java/org/springblade/modules/exam/excel/ExamScoreExcel.java
@@ -52,9 +52,9 @@ private String idCardNo; @ColumnWidth(20) @ExcelProperty("准考证号*") private String candidateNo; // @ColumnWidth(20) // @ExcelProperty("准考证号*") // private String candidateNo; // @ColumnWidth(15) // @ExcelProperty("理论得分*") src/main/java/org/springblade/modules/exam/mapper/ExamPaperMapper.java
@@ -90,7 +90,7 @@ * @param examName 考试名称 * @return */ ExamPaper getExamInfoByExamName(@Param("examName") String examName); List<ExamPaper> getExamInfoByExamName(@Param("examName") String examName); /** * 查询每个考试的报名人员数量 src/main/java/org/springblade/modules/exam/mapper/ExamScoreMapper.java
@@ -49,7 +49,7 @@ * @param idCardNo 身份证号 * @return */ ExamScore getExamScoreInfoByIdCardNo(@Param("idCardNo")String idCardNo,@Param("examId")Long examId); List<ExamScore> getExamScoreInfoByIdCardNo(@Param("idCardNo")String idCardNo,@Param("startTime")String startTime); List<Map<String,Object>> scoreStatistics(String deptid,String jurisdiction); List<Map<String,Object>> scoreStatisticssc(String deptid,String jurisdiction); src/main/java/org/springblade/modules/exam/mapper/ExamScoreMapper.xml
@@ -120,14 +120,20 @@ <if test="examScore.examType!=null and examScore.examType!=''"> and ke.exam_type = #{examScore.examType} </if> <if test="examScore.isAvatar==1"> and bu.avatar is not null and bu.avatar!="" </if> <if test="examScore.isAvatar==2"> and (bu.avatar is null or bu.avatar="") </if> <if test="examScore.candidateNo!=null and examScore.candidateNo!=''"> and es.candidate_no = #{examScore.candidateNo} </if> <if test="examScore.qualified!=null"> and es.qualified = #{examScore.qualified} </if> <if test="examScore.examDate!=null"> and date_format(es.exam_time,'%Y-%m-%d') = #{examScore.examDate} <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 </select> @@ -250,9 +256,13 @@ blade_user bu on bu.id = es.user_id left join ksxt_exam ke on ke.id = es.exam_id where bu.cardid = #{idCardNo} and es.exam_id = #{examId} and DATE_FORMAT(ke.start_time,'%Y-%m-%d') = #{startTime} </select> <!--修改成绩信息--> @@ -348,7 +358,7 @@ str.id = es.apply_id where 1=1 and (str.is_exam = 2 or str.is_exam = 3) and (str.is_exam = 2 or str.is_exam = 3 or str.is_exam = 4) <if test="examScore.examName!=null and examScore.examName!=''"> and ke.exam_name like concat('%', #{examScore.examName},'%') </if> @@ -373,6 +383,12 @@ <if test="examScore.isExam!=null and examScore.isExam!=''"> and str.is_exam = #{examScore.isExam} </if> <if test="examScore.isAvatar==1"> and bu.avatar is not null and bu.avatar!="" </if> <if test="examScore.isAvatar==2"> and (bu.avatar is null or bu.avatar="") </if> <if test="examScore.examType!=null and examScore.examType!=''"> and ke.exam_type = #{examScore.examType} </if> @@ -383,8 +399,9 @@ and es.qualified = #{examScore.qualified} </if> <if test="examScore.examDate!=null and examScore.examDate!=''"> and date_format(es.exam_time,'%Y-%m-%d') = #{examScore.examDate} and date_format(ke.start_time,'%Y-%m-%d') = #{examScore.examDate} </if> order by es.id desc </select>
@@ -97,7 +97,7 @@ * @param examName 考试名称 * @return */ ExamPaper getExamInfoByExamName(String examName); List<ExamPaper> getExamInfoByExamName(String examName); /** * 根据用户id查询报名信息 src/main/java/org/springblade/modules/exam/service/impl/ExamPaperServiceImpl.java
@@ -182,7 +182,7 @@ * @return */ @Override public ExamPaper getExamInfoByExamName(String examName) { public List<ExamPaper> getExamInfoByExamName(String examName) { return baseMapper.getExamInfoByExamName(examName); }
@@ -249,99 +249,108 @@ examScoreExcelList.forEach(examScoreExcel -> { if (null!=examScoreExcel.getLearnGrade() && null!=examScoreExcel.getIdCardNo() && examScoreExcel.getIdCardNo()!=""){ //使用考试名称匹配考试信息 // ExamPaper examPaper = examPaperService.getExamInfoByExamName(examScoreExcel.getExamName()); List<ExamPaper> examPaperList = examPaperService.getExamInfoByExamName(examScoreExcel.getExamName()); ExamPaper examPaper = examPaperList.get(0); //查询出成绩数据 // ExamScore examScore = baseMapper.getExamScoreInfoByIdCardNo(examScoreExcel.getIdCardNo(),examPaper.getId()); //根据身份证号查询用户 User user0 = userService.getUserInfoByIdCardNo(examScoreExcel.getIdCardNo()); if (null==user0){ throw new ServiceException("用户:["+examScoreExcel.getIdCardNo()+"]不存在"); } ExamScore examScore1 = new ExamScore(); examScore1.setCandidateNo(examScoreExcel.getCandidateNo()); examScore1.setUserId(user0.getId().toString()); //根据准考证号和用户id查询考试成绩 ExamScore examScore = this.getOne(Condition.getQueryWrapper(examScore1)); if (null!=examScore) { //查询当前保安信息 UserVO user = userService.getUserAgeById(Long.parseLong(examScore.getUserId())); examScore.setLearnGrade(examScoreExcel.getLearnGrade()); if (examScore.getTheoryGrade() >= 60 && examScore.getLearnGrade() >= 60) { //合格 examScore.setQualified(0); //修改制证状态为未制证 user.setUserType(7); //正式考试通过生成保安证编号 if (examPaperList.size()>0){ //取第一个 List<ExamScore> examScoreList = baseMapper.getExamScoreInfoByIdCardNo(examScoreExcel.getIdCardNo(),new SimpleDateFormat("yyyy-MM-dd").format(examPaper.getStartTime())); if (examScoreList.size()==0){ //根据身份证号查询用户 User user0 = userService.getUserInfoByIdCardNo(examScoreExcel.getIdCardNo()); if (null==user0){ throw new ServiceException("没有找到用户:["+examScoreExcel.getIdCardNo()+"]相关考试成绩信息,请检查身份证是否有误!"); // throw new ServiceException("用户:["+examScoreExcel.getIdCardNo()+"]不存在"); } } //遍历集合 examScoreList.forEach(examScore -> { // ExamScore examScore1 = new ExamScore(); // examScore1.setCandidateNo(examScoreExcel.getCandidateNo()); // examScore1.setUserId(user0.getId().toString()); // //根据准考证号和用户id查询考试成绩 // ExamScore examScore = this.getOne(Condition.getQueryWrapper(examScore1)); if (null!=examScore) { //查询当前保安信息 UserVO user = userService.getUserAgeById(Long.parseLong(examScore.getUserId())); examScore.setLearnGrade(examScoreExcel.getLearnGrade()); if (examScore.getTheoryGrade() >= 60 && examScore.getLearnGrade() >= 60) { //合格 examScore.setQualified(0); //修改制证状态为未制证 user.setUserType(7); //正式考试通过生成保安证编号 // if (examPaper.getExamType()==1) { //如果已有保安证编号，不更新用户信息 if (null==user.getSecuritynumber() || user.getSecuritynumber().equals("")) { //去生成保安证编号 String pre = SecurityPaperUtil.getSecurityPaper(); //查询当前年份已有的保安证编号 int count = userService.getSecurityPaperCount(pre); String result = null; if (count == 0) { result = pre + "00001"; } else { //格式化 DecimalFormat decimalFormat = new DecimalFormat("00000"); count++; result = pre + (decimalFormat.format(count)); } user.setSecuritynumber(result); //发证日期 //如果已有保安证编号，不更新用户信息 if (null==user.getSecuritynumber() || user.getSecuritynumber().equals("")) { //去生成保安证编号 String pre = SecurityPaperUtil.getSecurityPaper(); //查询当前年份已有的保安证编号 int count = userService.getSecurityPaperCount(pre); String result = null; if (count == 0) { result = pre + "00001"; } else { //格式化 DecimalFormat decimalFormat = new DecimalFormat("00000"); count++; result = pre + (decimalFormat.format(count)); } user.setSecuritynumber(result); //发证日期 // user.setPaperTime(new Date()); //修改为持证保安 user.setHold("1"); //更新保安数据 userService.updateById(user); //修改为持证保安 user.setHold("1"); user.setUpdateTime(new Date()); //更新保安数据 userService.updateById(user); String s1 = "update blade_user set hold = " + "'" + user.getHold() + "'" + ",securitynumber = " + "'" + user.getSecuritynumber() + "'" + ",update_time = " + "'" + new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(user.getUpdateTime()) + "'" + ",user_type = " + "'" + user.getUserType() + "'" + " " + "where id = " + "'" + user.getId() + "'"; FtpUtil.sqlFileUpload(s1); } } else { //不合格 examScore.setQualified(1); } //总成绩 if (user.getAge() <= 50) { if (null!=examScore.getTheoryGrade()) { examScore.setAllGrade(Math.round((examScore.getTheoryGrade() + examScoreExcel.getLearnGrade()) / 2)); }else { examScore.setAllGrade(Math.round(examScoreExcel.getLearnGrade() / 2)); } } if (user.getAge() > 50) { if (null!=examScore.getTheoryGrade()) { examScore.setAllGrade(Integer.parseInt(String.valueOf(Math.round(examScore.getTheoryGrade() * 0.5))) + Integer.parseInt(String.valueOf(Math.round(examScoreExcel.getLearnGrade() * 0.5)))); }else { examScore.setAllGrade(Math.round(examScoreExcel.getLearnGrade() / 2)); } } //内网同步 String s1 = "update blade_user set hold = " + "'" + user.getHold() + "'" + ",securitynumber = " + "'" + user.getSecuritynumber() + "'" + ",user_type = " + "'" + user.getUserType() + "'" + " " + "where id = " + "'" + user.getId() + "'"; "update exam_score set learn_grade = " + "'" + examScore.getLearnGrade() + "'" + ",all_grade = " + "'" + examScore.getAllGrade() + "'" + ",qualified = " + "'" + examScore.getQualified() + "'" + " " + "where id = " + "'" + examScore.getId() + "'"; FtpUtil.sqlFileUpload(s1); } //分配保安角色 // Role role = new Role(); // role.setRoleAlias("保安"); // Role oneRole = roleService.getOne(Condition.getQueryWrapper(role)); // user.setRoleId(oneRole.getId().toString()); // } } else { //不合格 examScore.setQualified(1); } //总成绩 if (user.getAge() <= 50) { if (null!=examScore.getTheoryGrade()) { examScore.setAllGrade(Math.round((examScore.getTheoryGrade() + examScoreExcel.getLearnGrade()) / 2)); //更新成绩数据 baseMapper.updateById(examScore); }else { examScore.setAllGrade(Math.round(examScoreExcel.getLearnGrade() / 2)); errorList.add(examScoreExcel.getIdCardNo()); status.set(false); } } if (user.getAge() > 50) { if (null!=examScore.getTheoryGrade()) { examScore.setAllGrade(Integer.parseInt(String.valueOf(Math.round(examScore.getTheoryGrade() * 0.5))) + Integer.parseInt(String.valueOf(Math.round(examScoreExcel.getLearnGrade() * 0.5)))); }else { examScore.setAllGrade(Math.round(examScoreExcel.getLearnGrade() / 2)); } } }); //内网同步 String s1 = "update exam_score set learn_grade = " + "'" + examScore.getLearnGrade() + "'" + ",all_grade = " + "'" + examScore.getAllGrade() + "'" + ",qualified = " + "'" + examScore.getQualified() + "'" + " " + "where id = " + "'" + examScore.getId() + "'"; FtpUtil.sqlFileUpload(s1); //更新成绩数据 baseMapper.updateById(examScore); }else { errorList.add(examScoreExcel.getIdCardNo()); status.set(false); throw new ServiceException("导入失败!考试名称:["+examScoreExcel.getExamName()+"]不存在!请核对!"); } } }); src/main/java/org/springblade/modules/exam/vo/ExamScoreVO.java
@@ -86,4 +86,9 @@ */ private String avatar; /** * 是否有头像 1:有 2：没有上传 */ private Integer isAvatar; } src/main/java/org/springblade/modules/system/controller/UserController.java
@@ -993,7 +993,7 @@ FtpUtil.sqlFileUpload(s1); //人员离职后修改派遣记录，修改从业记录 updateUserDispatcherExp(user); updateUserDispatcherExp(user2); }else { throw new ServiceException(StringUtil.format("当前用户 [{}] 已存在!", user.getAccount())); } src/main/java/org/springblade/modules/system/entity/User.java
@@ -145,7 +145,7 @@ @ApiModelProperty(value = "联系地址") private String address; /** * 户口所在地 * 身份证住址 */ @ApiModelProperty(value = "户口所在地") private String registered ; src/main/java/org/springblade/modules/system/service/impl/UserServiceImpl.java
@@ -835,6 +835,10 @@ if (null==user.getCardid() || user.getCardid().equals("")){ throw new ServiceException("导入失败!身份证号码不能为空!"); } //身份证住址校验 if (null==user.getRegistered() || user.getRegistered().equals("")){ throw new ServiceException("导入失败!身份证住址不能为空!"); } if (null!=user.getCardid() && !user.getCardid().equals("")){ //去除所有空格 String cardid = user.getCardid().replaceAll(" ", ""); @@ -1066,7 +1070,7 @@ @Override public void importSecurityYy(List<SecurityYyExcel> data, Boolean isCovered, String deptId) { //将不能导入的保安员账号存起来 //将不能导入的保安员账号存起来 List<String> errorList = new ArrayList<>(); //将需要新增的保安员信息存入集合 List<String> ageErrorList = new ArrayList<>(); @@ -1091,10 +1095,14 @@ throw new ServiceException("导入失败!公司名:["+user.getDeptId()+"]不存在!"); } //身份证校验 //身份证号码校验 if (null==user.getCardid() || user.getCardid().equals("")){ throw new ServiceException("导入失败!身份证号码不能为空!"); } //身份证住址校验 if (null==user.getRegistered() || user.getRegistered().equals("")){ throw new ServiceException("导入失败!身份证住址不能为空!"); } if (null!=user.getCardid() && !user.getCardid().equals("")){ //去除所有空格 String cardid = user.getCardid().replaceAll(" ", "");
