新增实操成绩导入逻辑修改，ftp 处理文件逻辑修改

Administrator

2022-06-13 f70d9a7b19b8451532de026bf1e6b2a5770c16bd

新增实操成绩导入逻辑修改，ftp 处理文件逻辑修改

 src/main/java/org/springblade/common/utils/SecurityPaperUtil.java

New file
@@ -0,0 +1,24 @@
package org.springblade.common.utils;

import java.util.Calendar;
import java.util.Date;

/**
 * 保安证编号生成
 * @author zhongrj
 * @since 2021-08-11
 */
public class SecurityPaperUtil {

    private final static String pre = "赣洪";

    public static String getSecurityPaper(){
        //获取当前时间
        Date date = new Date();
        Calendar instance = Calendar.getInstance();
        instance.setTime(date);
        //获取年份
        int year = instance.get(Calendar.YEAR);
        return pre+year;
    }
}

 src/main/java/org/springblade/modules/FTP/DataHanlder.java

@@ -2,9 +2,15 @@

import com.alibaba.fastjson.JSON;
import org.springblade.common.utils.DesensitizedUtil;
import org.springblade.common.utils.SecurityPaperUtil;
import org.springblade.core.log.exception.ServiceException;
import org.springblade.core.mp.support.Condition;
import org.springblade.core.tool.utils.BeanUtil;
import org.springblade.modules.dispatcher.entity.Dispatcher;
import org.springblade.modules.dispatcher.service.IDispatcherService;
import org.springblade.modules.exam.entity.ExamPaper;
import org.springblade.modules.exam.entity.ExamScore;
import org.springblade.modules.exam.service.ExamScoreService;
import org.springblade.modules.experience.entity.Experience;
import org.springblade.modules.experience.service.IExperienceService;
import org.springblade.modules.securitypaper.entity.SecurityPaper;
@@ -13,9 +19,11 @@
import org.springblade.modules.system.entity.User;
import org.springblade.modules.system.service.IDeptService;
import org.springblade.modules.system.service.IUserService;
import org.springblade.modules.system.vo.UserVO;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import javax.annotation.PostConstruct;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;

@@ -42,6 +50,9 @@

    @Autowired
    private IDeptService iDeptService;

    @Autowired
    private ExamScoreService examScoreService;



@@ -130,6 +141,133 @@
    }

    /**
     * 考试实操成绩处理
     * @param json
     */
    public static void handlerUserExamScore(String json) {
        Map map = JSON.parseObject(json, Map.class);
        //取出 key(uuid)
        String key = map.keySet().toArray()[0].toString();
        Object value = map.get(key);
        //得到考试成绩
        ExamScore examScore = JSON.parseObject(value.toString(), ExamScore.class);
        //处理实操考试成绩
        examScoreHandler(examScore);
    }

    /**
     * 实操成绩处理
     * @param examScore0
     */
    private static void examScoreHandler(ExamScore examScore0) {
        //取第一个
        List<ExamScore> examScoreList = hanlder.examScoreService.getExamScoreInfoByIdCardNo(examScore0.getCandidateNo(), new SimpleDateFormat("yyyy-MM-dd").format(examScore0.getExamTime()));
        if (examScoreList.size()>0) {
            //遍历集合
            examScoreList.forEach(examScore -> {
                if (null != examScore) {
                    //查询当前保安信息
                    UserVO user = hanlder.userService.getUserAgeById(Long.parseLong(examScore.getUserId()));
                    //设置实操成绩
                    examScore.setLearnGrade(examScore0.getLearnGrade());
                    if (null != examScore.getTheoryGrade()) {
                        if (examScore.getTheoryGrade() >= 60 && examScore.getLearnGrade() >= 60) {
                            //合格
                            examScore.setQualified(0);
                            //修改制证状态为未制证
                            user.setUserType(7);
                            //如果已有保安证编号，不更新用户信息
                            if (null == user.getSecuritynumber() || user.getSecuritynumber().equals("")) {
                                //去生成保安证编号
                                String pre = SecurityPaperUtil.getSecurityPaper();
                                //查询当前年份已有的保安证编号
                                int count = hanlder.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(DesensitizedUtil.desensitizedSecurityNumber(result));
                                //修改为持证保安
                                user.setHold("1");
                                user.setUpdateTime(new Date());
                                //更新保安数据
                                hanlder.userService.updateById(user);

                                //生成保安证的同时向保安证管理表中插入一条数据
                                SecurityPaper securityPaper = new SecurityPaper();
                                securityPaper.setUserId(user.getId());
                                securityPaper.setNumber(result);
                                securityPaper.setCreateTime(new Date());
                                securityPaper.setIdCardNo(user.getCardid());
                                securityPaper.setPeopleName(user.getRealName());
                                securityPaper.setExamId(Long.parseLong(examScore.getExamId()));
                                securityPaper.setApplyId(examScore.getApplyId());
                                securityPaper.setSource(1);
                                hanlder.securityPaperService.save(securityPaper);

                                //内网同步
                                String s1 = "update blade_user set hold = " + "'" + user.getHold() + "'" +
                                    ",securitynumber = " + "'" + DesensitizedUtil.desensitizedSecurityNumber(result) + "'" +
                                    ",update_time = " + "'" + new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(user.getUpdateTime()) + "'" +
                                    ",user_type = " + "'" + user.getUserType() + "'" +
                                    " " + "where id = " + "'" + user.getId() + "'";
                                hanlder.myAsyncService.FTP(s1);
                            }
                        }
                    } else {
                        //不合格
                        examScore.setQualified(1);
                    }
                    //总成绩
                    if (user.getAge() <= 50) {
                        if (null != examScore.getTheoryGrade()) {
                            examScore.setAllGrade(Math.round((examScore.getTheoryGrade() + examScore0.getLearnGrade()) / 2));
                        } else {
                            examScore.setAllGrade(Math.round(examScore0.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(examScore0.getLearnGrade() * 0.5))));
                        } else {
                            examScore.setAllGrade(Math.round(examScore0.getLearnGrade() / 2));
                        }
                    }

                    //外网同步
                    String s1 =
                        "update exam_score set learn_grade = " + "'" + examScore.getLearnGrade() + "'" +
                            ",all_grade = " + "'" + examScore.getAllGrade() + "'" +
                            ",qualified = " + "'" + examScore.getQualified() + "'" +
                            " " + "where id = " + "'" + examScore.getId() + "'";
                    hanlder.myAsyncService.FTP(s1);
                    //更新成绩数据
                    hanlder.examScoreService.updateById(examScore);
                }
            });
        }else {
            //根据身份证号查询用户
            User user0 = hanlder.userService.getUserInfoByIdCardNo(examScore0.getCandidateNo());
            if (null == user0) {
                String msg = "没有找到用户:[" + examScore0.getCandidateNo() + "]相关考试成绩信息,请检查身份证是否有误!";
                //向外网推送
                Result result = new Result(200,null,msg);
                Map<String, Object> map = new HashMap<>(1);
                map.put("key", result);
                //向外网发送数据
                FtpUtil.objectFileUploadExamScore(map);
            }
        }
    }

    /**
     * 数据处理(集合)
     * @param json
     */

 src/main/java/org/springblade/modules/FTP/FtpUtil.java

@@ -47,6 +47,10 @@
        FTPClient ftpClient = null;
        try {
            ftpClient = new FTPClient();
            //设置超时时间
            ftpClient.setDataTimeout(10000);
            //连接超时为60秒
            ftpClient.setConnectTimeout(10000);
            ftpClient.connect(ftpHost, ftpPort);// 连接FTP服务器
            ftpClient.login(ftpUserNameOut, ftpPasswordOut);// 登陆FTP服务器
            if (!FTPReply.isPositiveCompletion(ftpClient.getReplyCode())) {
@@ -418,6 +422,24 @@
    }

    /**
     * 执行 对象数据（实操成绩导入反馈） 上传
     * @param obj 对象信息
     */
    public static void objectFileUploadExamScore(Object obj){
        String json1 = JSON.toJSONString(obj);
        String response1 = String.valueOf((new Date()).getTime());
        OutJson.createJsonFile(json1, localPath, "ne"+response1);
        FileInputStream in1 = null;
        try {
            in1 = new FileInputStream(new File(localPath + "ne"+response1+".json"));
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }
        FtpUtil.uploadFile(ftpHost, ftpPort, ftpUserNameOut, ftpPasswordOut, ftpPath, "/",  "ne"+response1+".json", in1);
        MysqlCenlint.deletess("ne"+response1+".json");
    }

    /**
     * 返回报名信息结果
     * @param obj
     */

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

@@ -38,4 +38,11 @@
     * @return
     */
    List<ExportExamScoreExcel> exportExamScoreList(@Param("examScore")ExamScoreVO examScoreVO);

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

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

@@ -141,4 +141,23 @@
        </if>
        order by es.id desc
    </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
        left join
            ksxt_exam ke
        on
            ke.id = es.exam_id
        where
            bu.cardid = #{idCardNo}
            and DATE_FORMAT(ke.start_time,'%Y-%m-%d') = #{startTime}
    </select>
</mapper>

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

@@ -42,4 +42,12 @@
     * @return
     */
    List<ExportExamScoreExcel> exportExamScoreList(ExamScoreVO examScoreVO);

    /**
     * 根据时间查看考试成绩
     * @param candidateNo
     * @param time
     * @return
     */
    List<ExamScore> getExamScoreInfoByIdCardNo(String candidateNo, String time);
}

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

@@ -133,4 +133,15 @@
    public List<ExportExamScoreExcel> exportExamScoreList(ExamScoreVO examScoreVO) {
        return baseMapper.exportExamScoreList(examScoreVO);
    }

    /**
     * 根据时间查看考试成绩
     * @param candidateNo
     * @param time
     * @return
     */
    @Override
    public List<ExamScore> getExamScoreInfoByIdCardNo(String candidateNo, String time) {
        return baseMapper.getExamScoreInfoByIdCardNo(candidateNo,time);
    }
}

 src/main/java/org/springblade/modules/quartz/task/Task.java

@@ -69,14 +69,10 @@
            } else {
                for (FTPFile file : files) {
                    fileName = file.getName();
                    InputStream is = ftp.retrieveFileStream(new String(fileName.getBytes("GBK"), FTP.DEFAULT_CONTROL_ENCODING));
                    if (null == is) {
                        ftp.disconnect();
                    } else {
                        String substring1 = fileName.substring(0, 1);
                    if (substring1.equals("w")) {
                        //把文件下载到本地
                        FtpUtil.downloadFtpFile(ftpHost, ftpUserName, ftpPassword, ftpPort, ftpPath, localPath, fileName);
                        if (substring1.equals("w")) {
                            String s = OutJson.TestJson(fileName);
                            //sql语句
                            String sql = OutJson.stringReplace(s);
@@ -103,7 +99,10 @@
                            //删除 ftp 文件
//                            ftp.dele(fileName);
                            FtpUtil.deleteFile(ftpHost, ftpPort, ftpUserName, ftpPassword, "anbao/", fileName);
                        }else if(substring1.equals("o")){
                    }
                    if(substring1.equals("o")){
                        //把文件下载到本地
                        FtpUtil.downloadFtpFile(ftpHost, ftpUserName, ftpPassword, ftpPort, ftpPath, localPath, fileName);
                            //获取对象字符串
                            String json = OutJson.TestJson(fileName);
                            //数据处理
@@ -113,7 +112,10 @@
                            //删除 ftp 文件
//                            ftp.dele(fileName);
                            FtpUtil.deleteFile(ftpHost, ftpPort, ftpUserName, ftpPassword, "anbao/", fileName);
                        }else if(substring1.equals("l")){
                    }
                    if(substring1.equals("l")){
                        //把文件下载到本地
                        FtpUtil.downloadFtpFile(ftpHost, ftpUserName, ftpPassword, ftpPort, ftpPath, localPath, fileName);
                            //用户集合数据,批量导入用户
                            //获取对象字符串
                            String json = OutJson.TestJson(fileName);
@@ -124,7 +126,10 @@
                            //删除 ftp 文件
//                            ftp.dele(fileName);
                            FtpUtil.deleteFile(ftpHost, ftpPort, ftpUserName, ftpPassword, "anbao/", fileName);
                        }else if(substring1.equals("t")){
                    }
                    if(substring1.equals("t")){
                        //把文件下载到本地
                        FtpUtil.downloadFtpFile(ftpHost, ftpUserName, ftpPassword, ftpPort, ftpPath, localPath, fileName);
                            //培训报名数据
                            //获取对象字符串
                            String json = OutJson.TestJson(fileName);
@@ -135,7 +140,10 @@
                            //删除 ftp 文件
//                            ftp.dele(fileName);
                            FtpUtil.deleteFile(ftpHost, ftpPort, ftpUserName, ftpPassword, "anbao/", fileName);
                        }else if(substring1.equals("s")){
                    }
                    if(substring1.equals("s")){
                        //把文件下载到本地
                        FtpUtil.downloadFtpFile(ftpHost, ftpUserName, ftpPassword, ftpPort, ftpPath, localPath, fileName);
                            //获取保安员证编号数据
                            //获取对象字符串
                            String json = OutJson.TestJson(fileName);
@@ -146,7 +154,10 @@
                            //删除 ftp 文件
//                            ftp.dele(fileName);
                            FtpUtil.deleteFile(ftpHost, ftpPort, ftpUserName, ftpPassword, "anbao/", fileName);
                        }else if(substring1.equals("u")){
                    }
                    if(substring1.equals("u")){
                        //把文件下载到本地
                        FtpUtil.downloadFtpFile(ftpHost, ftpUserName, ftpPassword, ftpPort, ftpPath, localPath, fileName);
                            //保安员审查
                            //获取对象字符串
                            String json = OutJson.TestJson(fileName);
@@ -155,25 +166,29 @@
                            //删除本地文件
                            MysqlCenlint.deletess(fileName);
                            //删除 ftp 文件
//                            ftp.dele(fileName);
                            FtpUtil.deleteFile(ftpHost, ftpPort, ftpUserName, ftpPassword, "anbao/", fileName);
                        }
                        else if(substring1.equals("f")){
                    //考试实操成绩导入
                    if(substring1.equals("e")){
                        //把文件下载到本地
                        FtpUtil.downloadFtpFile(ftpHost, ftpUserName, ftpPassword, ftpPort, ftpPath, localPath, fileName);
                        //获取对象字符串
                        String json = OutJson.TestJson(fileName);
                        //考试实操成绩数据处理
                        DataHanlder.handlerUserExamScore(json);
                        //删除本地文件
                        MysqlCenlint.deletess(fileName);
                        //删除 ftp 文件
                        FtpUtil.deleteFile(ftpHost, ftpPort, ftpUserName, ftpPassword, "anbao/", fileName);
                    }
                    if(substring1.equals("f")){
                            //把文件下载到本地(图片文件类),测试内外网使用同一台服务器
                            FtpUtil.downloadFtpFile(ftpHost, ftpUserName, ftpPassword, ftpPort, ftpPath, minioPath, fileName);
                            FtpUtil.deleteFile(ftpHost, ftpPort, ftpUserName, ftpPassword, "anbao/", fileName);
//                            ftp.dele(fileName);
                        }
//                        else {
//                            //把文件下载到本地(图片文件类),ftp 内外网服务器分开时使用
//                            FtpUtil.downloadFtpFile(ftpHost, ftpUserName, ftpPassword, ftpPort, ftpPath, minioPath, fileName);
//                            FtpUtil.deleteFile(ftpHost, ftpPort, ftpUserName, ftpPassword, "anbao/", fileName);
//                        }
                        //删除本地文件,ftp 文件没有对应读取的不删除
                        MysqlCenlint.deletess(fileName);
                        is.close();
                        ftp.completePendingCommand();
                    }
//                    ftp.completePendingCommand();
                }
                return true;
            }
@@ -182,6 +197,11 @@
//            FtpUtil.deleteFile(ftpHost, ftpPort, ftpUserName, ftpPassword, "anbao/", fileName);
            e.printStackTrace();
        } finally {
            try {
                ftp.logout();
            } catch (IOException e) {
                e.printStackTrace();
            }
            //如果ftp 没有关闭,则关闭ftp
            if (ftp.isConnected()) {
                try {

 src/main/java/org/springblade/modules/system/mapper/UserMapper.java

@@ -143,4 +143,13 @@
     * @return
     */
    UserDTO getSecurityInfoByIdCardNo(@Param("user")User user);

    /**
     * 通过 身份证号查询用户信息
     * @param idCardNo 身份证号
     * @return
     */
    User getUserInfoByIdCardNo(String idCardNo);

    UserVO getUserAgeById(@Param("userId") long id);
}

 src/main/java/org/springblade/modules/system/mapper/UserMapper.xml

@@ -572,4 +572,24 @@
        and bu.status = 1
        and bu.cardid = #{user.cardid}
    </select>


    <!--通过 身份证号查询用户信息-->
    <select id="getUserInfoByIdCardNo" resultType="org.springblade.modules.system.entity.User">
        SELECT id, name, real_name RealName,securitynumber
        FROM blade_user
        where 1=1
        and is_deleted = 0
        and status = 1
        and cardid = #{param1}
    </select>

    <!--计算保安人员年龄-->
    <select id="getUserAgeById" resultType="org.springblade.modules.system.vo.UserVO">
        select id,real_name realName,ifnull(TIMESTAMPDIFF(YEAR, SUBSTRING(cardid, 7, 8), CURDATE()),0) AS age,securitynumber,cardid
        from
        blade_user
        where
        id = #{userId}
    </select>
</mapper>

 src/main/java/org/springblade/modules/system/service/IUserService.java

@@ -275,4 +275,18 @@
     * @return
     */
    UserDTO getSecurityInfoByIdCardNo(User user);

    /**
     * 根据身份证号查询用户
     * @param candidateNo
     * @return
     */
    User getUserInfoByIdCardNo(String candidateNo);

    /**
     * 查询当前保安信息
     * @param id
     * @return
     */
    UserVO getUserAgeById(long id);
}

 src/main/java/org/springblade/modules/system/service/impl/UserServiceImpl.java

@@ -541,4 +541,25 @@
    public UserDTO getSecurityInfoByIdCardNo(User user) {
        return baseMapper.getSecurityInfoByIdCardNo(user);
    }

    /**
     * 根据身份证号查询用户
     * @param candidateNo
     * @return
     */
    @Override
    public User getUserInfoByIdCardNo(String candidateNo) {
        return baseMapper.getUserInfoByIdCardNo(candidateNo);
    }

    /**
     * 计算保安人员年龄
     *
     * @param id
     * @return
     */
    @Override
    public UserVO getUserAgeById(long id) {
        return baseMapper.getUserAgeById(id);
    }
}

 src/main/resources/application-test.yml

@@ -16,13 +16,13 @@

  datasource:
    # MySql
    url: jdbc:mysql://61.131.136.25:2083/zhba-test?useSSL=false&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&transformedBitIsBoolean=true&serverTimezone=GMT%2B8&nullCatalogMeansCurrent=true&allowPublicKeyRetrieval=true
    url: jdbc:mysql://61.131.136.25:2083/zhba?useSSL=false&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&transformedBitIsBoolean=true&serverTimezone=GMT%2B8&nullCatalogMeansCurrent=true&allowPublicKeyRetrieval=true
    username: root
    password: ZHba@0112

#ftp 设置
ftp:
  sqlConnect: jdbc:mysql://61.131.136.25:2083/zhba-test?useSSL=false&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&transformedBitIsBoolean=true&serverTimezone=GMT%2B8&nullCatalogMeansCurrent=true&allowPublicKeyRetrieval=true
  sqlConnect: jdbc:mysql://61.131.136.25:2083/zhba?useSSL=false&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&transformedBitIsBoolean=true&serverTimezone=GMT%2B8&nullCatalogMeansCurrent=true&allowPublicKeyRetrieval=true
  ftpHost: 192.168.0.198
  ftpPort: 21
  ftpUserName: arsn