考试成绩导入接口修改，查询接口修改

Administrator

2021-07-29 92b71ac07ad4e0b7282135cad0919a4be4ec1efb

考试成绩导入接口修改，查询接口修改

 src/main/java/org/springblade/modules/exam/controller/ExamScoreController.java

@@ -4,14 +4,21 @@
import io.swagger.annotations.ApiOperation;
import io.swagger.annotations.ApiParam;
import lombok.AllArgsConstructor;
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.Func;
import org.springblade.modules.exam.entity.ExamScore;
import org.springblade.modules.exam.excel.ExamScoreExcel;
import org.springblade.modules.exam.excel.ExamScoreImporter;
import org.springblade.modules.exam.service.ExamScoreService;
import org.springblade.modules.exam.vo.ExamScoreVO;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.util.ArrayList;
import java.util.List;

/**
 * @author zhongrj
@@ -89,8 +96,8 @@
                //不合格
                examScore.setQualified(1);
            }
            int totalSocre = examScore.getLearnGrade()+examScore.getTheoryGrade();
            examScore.setAllGrade(Math.round(totalSocre/2));
//            int totalSocre = examScore.getLearnGrade()+examScore.getTheoryGrade();
//            examScore.setAllGrade(Math.round(totalSocre/2));
        }
        return R.status(examScoreService.updateById(examScore));
    }
@@ -125,4 +132,27 @@
        //返回
        return R.data(detail);
    }

    /**
     * 导入实操成绩
     * @param isCovered 1 覆盖  0不覆盖
     * @return
     */
    @PostMapping("import-examScore")
    @ApiOperation(value = "导入实操成绩", notes = "传入excel")
    public R importExamScore(MultipartFile file, Integer isCovered) {
        ExamScoreImporter examScoreImporter = new ExamScoreImporter(examScoreService, false);
        ExcelUtil.save(file, examScoreImporter, ExamScoreExcel.class);
        return R.success("操作成功");
    }

    /**
     * 导出模板
     */
    @GetMapping("export-template")
    @ApiOperation(value = "导出模板")
    public void exportUser(HttpServletResponse response) {
        List<ExamScoreExcel> list = new ArrayList<>();
        ExcelUtil.export(response, "实操成绩数据模板", "实操成绩数据表", list, ExamScoreExcel.class);
    }
}

 src/main/java/org/springblade/modules/exam/entity/ExamScore.java

@@ -55,13 +55,6 @@


    /**
     * 考试类型 0：正式考试  1： 模拟考试  2：在线联系  3：调查问卷
     */
//    @TableField("exam_type")
//    private Integer examType;


    /**
     * 考试人员所属公司名称id
     */
    private String company;

 src/main/java/org/springblade/modules/exam/excel/ExamScoreExcel.java

New file
@@ -0,0 +1,55 @@
/*
 *      Copyright (c) 2018-2028, Chill Zhuang All rights reserved.
 *
 *  Redistribution and use in source and binary forms, with or without
 *  modification, are permitted provided that the following conditions are met:
 *
 *  Redistributions of source code must retain the above copyright notice,
 *  this list of conditions and the following disclaimer.
 *  Redistributions in binary form must reproduce the above copyright
 *  notice, this list of conditions and the following disclaimer in the
 *  documentation and/or other materials provided with the distribution.
 *  Neither the name of the dreamlu.net developer nor the names of its
 *  contributors may be used to endorse or promote products derived from
 *  this software without specific prior written permission.
 *  Author: Chill 庄骞 (smallchill@163.com)
 */
package org.springblade.modules.exam.excel;

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentRowHeight;
import com.alibaba.excel.annotation.write.style.HeadRowHeight;
import lombok.Data;

import java.io.Serializable;

/**
 * ExamPaymentExcel
 * @author zhongrj
 * @since 2021-07-29
 */
@Data
@ColumnWidth(25)
@HeadRowHeight(20)
@ContentRowHeight(18)
public class ExamScoreExcel implements Serializable {
    private static final long serialVersionUID = 1L;


    @ColumnWidth(20)
    @ExcelProperty("姓名")
    private String name;


    @ColumnWidth(25)
    @ExcelProperty("身份证号码")
    private String idCardNo;


    @ColumnWidth(15)
    @ExcelProperty("实操得分")
    private Integer learnGrade;


}

 src/main/java/org/springblade/modules/exam/excel/ExamScoreImporter.java

New file
@@ -0,0 +1,41 @@
/*
 *      Copyright (c) 2018-2028, Chill Zhuang All rights reserved.
 *
 *  Redistribution and use in source and binary forms, with or without
 *  modification, are permitted provided that the following conditions are met:
 *
 *  Redistributions of source code must retain the above copyright notice,
 *  this list of conditions and the following disclaimer.
 *  Redistributions in binary form must reproduce the above copyright
 *  notice, this list of conditions and the following disclaimer in the
 *  documentation and/or other materials provided with the distribution.
 *  Neither the name of the dreamlu.net developer nor the names of its
 *  contributors may be used to endorse or promote products derived from
 *  this software without specific prior written permission.
 *  Author: Chill 庄骞 (smallchill@163.com)
 */
package org.springblade.modules.exam.excel;

import lombok.RequiredArgsConstructor;
import org.springblade.core.excel.support.ExcelImporter;
import org.springblade.modules.exam.service.ExamScoreService;

import java.util.List;

/**
 * 实操成绩导入类
 *
 * @author zhongrj
 * @since 2021-07-29
 */
@RequiredArgsConstructor
public class ExamScoreImporter implements ExcelImporter<ExamScoreExcel> {

    private final ExamScoreService service;
    private final Boolean isCovered;

    @Override
    public void save(List<ExamScoreExcel> data) {
        service.importExamScore(data, isCovered);
    }
}

 src/main/java/org/springblade/modules/exam/mapper/ExamScoreMapper.java

@@ -31,4 +31,10 @@
     */
    ExamScoreVO selectExamScoreInfo(@Param("examScore") ExamScore examScore);

    /**
     * 根据身份证号导入实操成绩
     * @param idCardNo 身份证号
     * @return
     */
    ExamScore getExamScoreInfoByIdCardNo(@Param("idCardNo")String idCardNo);
}

 src/main/java/org/springblade/modules/exam/mapper/ExamScoreMapper.xml

@@ -5,31 +5,40 @@
    <!--考试成绩分页信息-->
    <select id="selectExamScorePage" resultType="org.springblade.modules.exam.vo.ExamScoreVO">
        SELECT
         id,candidate_no candidateNo,theory_grade theoryGrade,ifnull(learn_grade,-1) learnGrade,all_grade,
         exam_name examName,security_name securityName,exam_type examType,company,exam_time examTime,
         all_score allScore,exam_end_time examEndTime,qualified
         es.id,es.theory_grade theoryGrade,ifnull(learn_grade,-1) learnGrade,
         exam_name examName,bu.real_name securityName,company,exam_time examTime,
         all_score allScore,exam_end_time examEndTime,qualified,
         bd.dept_name companyName
        FROM
            exam_score
            exam_score es
        left join
            blade_user bu
        on
            es.user_id = bu.id
        left join
            blade_dept bd
        on
            bd.id = es.company
        WHERE
            1=1
        <if test="examScore.examName!=null and  examScore.examName!=''">
            and exam_name like concat('%', #{examScore.examName},'%')
            and es.exam_name like concat('%', #{examScore.examName},'%')
        </if>
        <if test="examScore.company!=null and  examScore.company!=''">
            and company like concat('%', #{examScore.company},'%')
        <if test="examScore.companyName!=null and  examScore.companyName!=''">
            and bd.dept_name like concat('%', #{examScore.companyName},'%')
        </if>
        <if test="examScore.securityName!=null and  examScore.securityName!=''">
            and security_name like concat('%', #{examScore.securityName},'%')
            and bu.real_name like concat('%', #{examScore.securityName},'%')
        </if>
        <if test="examScore.userId!=null and  examScore.userId!=''">
            and user_id = #{examScore.userId}
            and es.user_id = #{examScore.userId}
        </if>
        <if test="examScore.candidateNo!=null and  examScore.candidateNo!=''">
            and candidate_no = #{examScore.candidateNo}
            and es.candidate_no = #{examScore.candidateNo}
        </if>
<!--        <if test="examScore.examType!=null">-->
<!--            and exam_type = #{examScore.examType}-->
<!--        </if>-->
        <if test="examScore.qualified!=null">
            and es.qualified = #{examScore.qualified}
        </if>
    </select>

    <!--考试成绩详情信息-->
@@ -46,4 +55,18 @@
            and id = #{examScore.id}
        </if>
    </select>

    <!---->
    <select id="getExamScoreInfoByIdCardNo" resultType="org.springblade.modules.exam.entity.ExamScore">
        select
            es.*
        from
            exam_score es
        left join
            blade_user bu
        on
            bu.id = es.user_id
        where
            bu.cardid = #{idCardNo}
    </select>
</mapper>

 src/main/java/org/springblade/modules/exam/service/ExamScoreService.java

@@ -3,7 +3,10 @@
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.service.IService;
import org.springblade.modules.exam.entity.ExamScore;
import org.springblade.modules.exam.excel.ExamScoreExcel;
import org.springblade.modules.exam.vo.ExamScoreVO;

import java.util.List;

/**
 * 考试成绩服务类
@@ -32,4 +35,11 @@
     * @return
     */
    Boolean saveExamScore(ExamScoreVO examScore);

    /**
     * 导入实操成绩
     * @param examScoreExcelList
     * @param isCovered 是否覆盖
     */
    void importExamScore(List<ExamScoreExcel> examScoreExcelList, Boolean isCovered);
}

 src/main/java/org/springblade/modules/exam/service/impl/ExamScoreServiceImpl.java

@@ -7,6 +7,7 @@
import org.springblade.modules.exam.entity.ExamExaminationSubject;
import org.springblade.modules.exam.entity.ExamPaper;
import org.springblade.modules.exam.entity.ExamScore;
import org.springblade.modules.exam.excel.ExamScoreExcel;
import org.springblade.modules.exam.mapper.ExamScoreMapper;
import org.springblade.modules.exam.service.ExamPaperService;
import org.springblade.modules.exam.service.ExamScoreService;
@@ -17,6 +18,8 @@

import java.util.Arrays;
import java.util.List;

import static com.bstek.ureport.expression.model.condition.Join.and;

/**
 * 考试成绩服务实现类
@@ -120,4 +123,32 @@
        //返回结果
        return false;
    }

    /**
     * 导入实操成绩
     * @param examScoreExcelList
     * @param isCovered 是否覆盖
     */
    @Override
    public void importExamScore(List<ExamScoreExcel> examScoreExcelList, Boolean isCovered) {
        if (examScoreExcelList.size()>0){
            examScoreExcelList.forEach(examScoreExcel -> {
                if (null!=examScoreExcel.getLearnGrade() && null!=examScoreExcel.getIdCardNo() && examScoreExcel.getIdCardNo()!=""){
                    ExamScore examScore = baseMapper.getExamScoreInfoByIdCardNo(examScoreExcel.getIdCardNo());
                    if (null!=examScore) {
                        examScore.setLearnGrade(examScoreExcel.getLearnGrade());
                        if (examScore.getTheoryGrade() >= 60 && examScore.getLearnGrade() >= 60) {
                            //合格
                            examScore.setQualified(0);
                        } else {
                            //不合格
                            examScore.setQualified(1);
                        }
                        //更新成绩数据
                        baseMapper.updateById(examScore);
                    }
                }
            });
        }
    }
}

 src/main/java/org/springblade/modules/exam/vo/ExamScoreVO.java

@@ -16,6 +16,11 @@
    private static final long serialVersionUID = 1L;

    /**
     * 公司名称
     */
    private String companyName;

    /**
     * 试卷id
     */
    private Long papersId;