考试准考证生成修改，统计接口修改

Administrator

2021-09-29 a9463d9118693dbb54cef0d076c03b4160ab64e4

考试准考证生成修改，统计接口修改

 src/main/java/org/springblade/modules/apply/service/impl/ApplyServiceImpl.java

@@ -471,6 +471,7 @@
            trainingRegistration.setId(apply.getId());
            //考试中
            trainingRegistration.setIsExam(3);
//            trainingRegistration.setIsExam(1);
            trainingRegistrationService.updateById(trainingRegistration);

            String s1 =

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

@@ -249,46 +249,54 @@
     */
    @PostMapping("/update-audit")
    public R updateAudit(@RequestBody ExamPaper examPaper){
        examPaper.setAuditTime(new Date());
        //修改考试审核状态
        boolean status = examPaperService.updateById(examPaper);
        //数据同步
        String s1 =
            "update ksxt_exam set audit_status = " + "'" + examPaper.getAuditStatus()+ "'"
                + ",audit_time = " + "'" + new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(examPaper.getAuditTime())+ "'"
                + " " +"where id = " + "'" + examPaper.getId() + "'";
        FtpUtil.sqlFileUpload(s1);
        //查询当前考试已报名的人员培训报名数据集合
        List<TrainingRegistration> trainingRegistrations = examPaperService.getTrainList(examPaper.getId());
//        System.out.println("trainingRegistrations = " + trainingRegistrations);
        if (trainingRegistrations.size()>0) {
            trainingRegistrations.forEach(trainingRegistration -> {
                //生成准考证号
                trainingRegistration.setCandidateNo(getCandidateNo(examPaper));
                //审核通过
                if (examPaper.getAuditStatus()==1) {
                    trainingRegistration.setAuditStatus(1);
                }
                //审核不通过
                if (examPaper.getAuditStatus()==2) {
                    trainingRegistration.setAuditStatus(2);
                }
                //待审核
                if (examPaper.getAuditStatus()==3) {
                    trainingRegistration.setAuditStatus(3);
                }
                //修改培训报名信息
                trainingRegistrationService.updateById(trainingRegistration);
        //查询之前的审核状态，如果审核状态没有发生改变，则不会去修改审核信息
        ExamPaper paper = examPaperService.getById(examPaper.getId());
        if (null!=paper.getAuditStatus()){
            //审核状态发生改变
            if (!paper.getAuditStatus().equals(examPaper.getAuditStatus())){
                examPaper.setAuditTime(new Date());
                //修改考试审核状态
                boolean status = examPaperService.updateById(examPaper);
                //数据同步
                String s2 =
                    "update sys_training_registration set candidate_no = " + "'" + trainingRegistration.getCandidateNo()+ "'"
                        + ",audit_status = " + "'" + trainingRegistration.getAuditStatus() + "'"
                        + " " +"where id = " + "'" + trainingRegistration.getId() + "'";
                FtpUtil.sqlFileUpload(s2);
            });
                String s1 =
                    "update ksxt_exam set audit_status = " + "'" + examPaper.getAuditStatus()+ "'"
                        + ",audit_time = " + "'" + new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(examPaper.getAuditTime())+ "'"
                        + " " +"where id = " + "'" + examPaper.getId() + "'";
                FtpUtil.sqlFileUpload(s1);
                //查询当前考试已报名的人员培训报名数据集合
                List<TrainingRegistration> trainingRegistrations = examPaperService.getTrainList(examPaper.getId());
//        System.out.println("trainingRegistrations = " + trainingRegistrations);
                if (trainingRegistrations.size()>0) {
                    trainingRegistrations.forEach(trainingRegistration -> {
                        //审核通过，则生成准考证号
                        if (examPaper.getAuditStatus()==1) {
                            trainingRegistration.setAuditStatus(1);
                            //生成准考证号
                            trainingRegistration.setCandidateNo(getCandidateNo(examPaper));
                        }
                        //审核不通过
                        if (examPaper.getAuditStatus()==2) {
                            trainingRegistration.setAuditStatus(2);
                        }
                        //待审核
                        if (examPaper.getAuditStatus()==3) {
                            trainingRegistration.setAuditStatus(3);
                        }
                        //修改培训报名信息
                        trainingRegistrationService.updateById(trainingRegistration);
                        //数据同步
                        String s2 =
                            "update sys_training_registration set candidate_no = " + "'" + trainingRegistration.getCandidateNo()+ "'"
                                + ",audit_status = " + "'" + trainingRegistration.getAuditStatus() + "'"
                                + " " +"where id = " + "'" + trainingRegistration.getId() + "'";
                        FtpUtil.sqlFileUpload(s2);
                    });
                }
                return R.status(status);
            }
        }
        //返回
        return R.status(status);
        return R.status(false);
    }

    /**
@@ -344,19 +352,19 @@
//                            + quarter;

            //前缀 = 年的最后两位  + 月份（两位） + 日 （两位） + 考试类型 正式考试  z   模拟考试   m
            String result = year
            String pre = year
                + months
                + days
                + type;
            //查询是当前前缀已生成的数量
            int count = examPaperService.getCandidateNoCount(result);
            if (count==0){
                return result + "0000";
            }
            //查询是当前前缀已生成准考证号码最大的一位
            int count = examPaperService.getCandidateNoCount(pre);
            String result = null;
            //格式化
            DecimalFormat decimalFormat = new DecimalFormat("0000");
            count++;
            result = pre + (decimalFormat.format(count));
            //返回
            return result + (decimalFormat.format(count++));
            return result;
        }
        return null;
    }

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

@@ -19,6 +19,7 @@
import org.springblade.modules.exam.service.ExamScoreService;
import org.springblade.modules.exam.util.SecurityPaperUtil;
import org.springblade.modules.exam.vo.ExamScoreVO;
import org.springblade.modules.exam.vo.UpdateParamVo;
import org.springblade.modules.system.entity.Role;
import org.springblade.modules.system.entity.User;
import org.springblade.modules.system.service.IRoleService;
@@ -157,7 +158,7 @@
                    int max = userService.getSecurityPaperCount(pre);
                    String result = null;
                    if (max == 0) {
                        result = pre + "00000";
                        result = pre + "00001";
                    } else {
                        //格式化
                        DecimalFormat decimalFormat = new DecimalFormat("00000");
@@ -344,4 +345,17 @@
        IPage<ExamScoreVO> pages = examScoreService.scorePage(Condition.getPage(query),deptid,jurisdiction);
        return R.data(pages);
    }


    /**
     * 考试成绩及状态修改
     * @param updateParamVo
     */
    @GetMapping("/update-exam-score")
    public R updateExamScores(UpdateParamVo updateParamVo) {
        boolean status = examScoreService.updateExamScore(updateParamVo);
        return R.status(status);
    }


}

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

@@ -70,7 +70,7 @@
    List<ExamPaperVO> getExamDetail(String userId);

    /**
     * 查询前缀相同的准考证数量
     * 查询是当前前缀已生成准考证号码最大的一位
     * @param result 前缀
     * @return
     */

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

@@ -198,13 +198,10 @@
        )
    </select>

    <!--查询前缀相同的准考证数量-->
    <!--查询是当前前缀已生成准考证号码最大的一位-->
    <select id="getCandidateNoCount" resultType="java.lang.Integer">
        select count(*) from sys_training_registration
        where 1=1
        <if test="result!=null and result!=''">
            and candidate_no like concat('%', #{result},'%')
        </if>
        select ifnull(max(0+RIGHT(candidate_no,4)),0) from sys_training_registration
        where candidate_no like concat('%', #{result},'%')
    </select>

    <!--查询当前考试已报名的人员培训报名数据集合-->

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

@@ -5,6 +5,7 @@
import org.apache.ibatis.annotations.Param;
import org.springblade.modules.exam.entity.ExamScore;
import org.springblade.modules.exam.vo.ExamScoreVO;
import org.springblade.modules.training.entity.TrainingRegistration;

import java.util.List;
import java.util.Map;
@@ -65,4 +66,26 @@
     * @return
     */
    List<ExamScoreVO> scorePage(IPage page,String deptid,String jurisdiction);

    /**
     * 查询所有考试不合格的数据
     * @return
     */
    List<ExamScore> getDisqualificationExamInfo();

    /**
     * 根据准考证号查询培训信息
     * @param trainingRegistration
     * @return
     */
    TrainingRegistration getTrainInfo(@Param("trainingRegistration")TrainingRegistration trainingRegistration);

    /**
     * 查询所有无成绩的数据
     * @return
     */
    List<ExamScore> getNoScoreInfo();

    List<ExamScore> getLheoryGradeLess60();

}

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

@@ -187,10 +187,19 @@
        COUNT( * ) AS count
        FROM
        exam_score exam
        LEFT JOIN blade_user user ON exam.user_id = user.id
        LEFT JOIN blade_user user
        ON exam.user_id = user.id
        left join
        sys_information si
        on
        si.departmentid = user.dept_id
        left join
        sys_jurisdiction sj
        on
        sj.id = si.jurisdiction
        WHERE 1 = 1 AND theory_grade is not null
        <if test="jurisdiction!=null and jurisdiction=='' and jurisdiction!='1372091709474910209'">
            AND user.jurisdiction = #{jurisdiction}
            and (sj.id = #{jurisdiction} or sj.parent_id = #{jurisdiction})
        </if>
        <if test="deptid!=null and deptid!=''">
            AND user.dept_id = #{deptid}
@@ -205,10 +214,20 @@
        COUNT( * ) AS count
        FROM
        exam_score exam
        LEFT JOIN blade_user user ON exam.user_id = user.id
        WHERE 1 = 1 AND learn_grade is not null
        LEFT JOIN blade_user user
        ON exam.user_id = user.id
        left join
        sys_information si
        on
        si.departmentid = user.dept_id
        left join
        sys_jurisdiction sj
        on
        sj.id = si.jurisdiction
        WHERE 1 = 1
        AND learn_grade is not null
        <if test="jurisdiction!=null and jurisdiction=='' and jurisdiction!='1372091709474910209'">
            AND user.jurisdiction = #{jurisdiction}
            and (sj.id = #{jurisdiction} or sj.parent_id = #{jurisdiction})
        </if>
        <if test="deptid!=null and deptid!=''">
            AND user.dept_id = #{deptid}
@@ -241,17 +260,59 @@
        SELECT
        exam.*,
        user.jurisdiction,
        user.dept_id
        user.real_name securityName,
        ke.exam_name examName
        FROM
        exam_score exam
        LEFT JOIN blade_user user ON exam.user_id = user.id
        LEFT JOIN blade_user user
        ON exam.user_id = user.id
        left join
        sys_information si
        on
        si.departmentid = user.dept_id
        left join
        sys_jurisdiction sj
        on
        sj.id = si.jurisdiction
        left join
        ksxt_exam ke
        on
        exam.exam_id = ke.id
        WHERE
        1 = 1
        <if test="jurisdiction!=null and jurisdiction!='' and jurisdiction!='1372091709474910209'">
            AND user.jurisdiction = #{jurisdiction}
            and (sj.id = #{jurisdiction} or sj.parent_id = #{jurisdiction})
        </if>
        <if test="deptid!=null and deptid!=''">
            AND user.dept_id = #{deptid}
        </if>
    </select>

    <!--查询所有考试不合格的数据（取准考证号）-->
    <select id="getDisqualificationExamInfo" resultType="org.springblade.modules.exam.entity.ExamScore">
        select * from exam_score where qualified = 1
    </select>


    <!--根据准考证号查询培训信息-->
    <select id="getTrainInfo" resultType="org.springblade.modules.training.entity.TrainingRegistration">
        select * from sys_training_registration where 1=1
        <if test="trainingRegistration.candidateNo!=null and trainingRegistration.candidateNo!=''">
            and is_exam = 2
            and candidate_no = #{trainingRegistration.candidateNo}
        </if>
        <if test="trainingRegistration.id!=null">
            and id = #{trainingRegistration.id}
        </if>
    </select>

    <!--查询所有无成绩的数据-->
    <select id="getNoScoreInfo" resultType="org.springblade.modules.exam.entity.ExamScore">
        select * from exam_score where theory_grade is null
    </select>

    <!--查询所有无成绩的数据-->
    <select id="getLheoryGradeLess60" resultType="org.springblade.modules.exam.entity.ExamScore">
        select * from exam_score where all_grade &lt; 30
    </select>
</mapper>

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

@@ -78,7 +78,7 @@
    List<ExamPaperVO> getExamDetail(String userId);

    /**
     * 查询前缀相同的准考证数量
     * 查询是当前前缀已生成准考证号码最大的一位
     * @param result 前缀
     * @return
     */

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

@@ -5,6 +5,7 @@
import org.springblade.modules.exam.entity.ExamScore;
import org.springblade.modules.exam.excel.ExamScoreExcel;
import org.springblade.modules.exam.vo.ExamScoreVO;
import org.springblade.modules.exam.vo.UpdateParamVo;

import java.util.List;
import java.util.Map;
@@ -60,4 +61,10 @@
     * @return
     */
    IPage<ExamScoreVO> scorePage(IPage page,String deptid,String jurisdiction);

    /**
     * 考试成绩及状态修改
     * @param examScore
     */
    boolean updateExamScore(UpdateParamVo updateParamVo);
}

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

@@ -115,7 +115,7 @@
    }

    /**
     * 查询前缀相同的准考证数量
     * 查询是当前前缀已生成准考证号码最大的一位
     * @param result 前缀
     * @return
     */

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

@@ -21,6 +21,7 @@
import org.springblade.modules.exam.util.SecurityPaperUtil;
import org.springblade.modules.exam.vo.ExamResultVO;
import org.springblade.modules.exam.vo.ExamScoreVO;
import org.springblade.modules.exam.vo.UpdateParamVo;
import org.springblade.modules.system.entity.Role;
import org.springblade.modules.system.entity.User;
import org.springblade.modules.system.service.IRoleService;
@@ -32,6 +33,7 @@
import org.springframework.transaction.annotation.Transactional;

import java.text.DecimalFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Arrays;
import java.util.Date;
@@ -171,6 +173,7 @@
            if(paper.getExamType()==2){
                TrainingRegistration trainingRegistration = new TrainingRegistration();
                trainingRegistration.setCandidateNo(examScore.getCandidateNo());
                trainingRegistration.setCancel(1);
                TrainingRegistration trainingRegistration1 = trainingRegistrationService.getOne(Condition.getQueryWrapper(trainingRegistration));
                //查询用户年龄，根据年龄计算总分
                user = userService.getUserAgeById(Long.parseLong(trainingRegistration1.getUserId()));
@@ -256,7 +259,7 @@
                                int count = userService.getSecurityPaperCount(pre);
                                String result = null;
                                if (count == 0) {
                                    result = pre + "00000";
                                    result = pre + "00001";
                                } else {
                                    //格式化
                                    DecimalFormat decimalFormat = new DecimalFormat("00000");
@@ -338,4 +341,109 @@
    public IPage<ExamScoreVO> scorePage(IPage page, String deptid, String jurisdiction) {
        return page.setRecords(baseMapper.scorePage(page,deptid,jurisdiction));
    }

    /**
     * 考试成绩及状态修改
     * @param updateParamVo
     */
    @Override
    @Transactional(rollbackFor = Exception.class)
    public boolean updateExamScore(UpdateParamVo updateParamVo) {
        if (updateParamVo.getType()==1){
            //查询所有无成绩的数据
            List<ExamScore> noScoreInfoList = baseMapper.getNoScoreInfo();
            noScoreInfoList.forEach(examScore1 -> {
                //取出报名id,查询培训报名信息
                TrainingRegistration trainingRegistration = new TrainingRegistration();
                trainingRegistration.setId(examScore1.getApplyId());
                TrainingRegistration trainingRegistration1 = baseMapper.getTrainInfo(trainingRegistration);
                //修改考试状态
                trainingRegistration1.setIsExam(2);
                trainingRegistrationService.updateById(trainingRegistration1);
                if (null!=trainingRegistration1){
                    //修改用户培训状态信息
                    User user = new User();
                    user.setIsTrain(3);
                    user.setId(Long.parseLong(trainingRegistration1.getUserId()));
                    //修改
                    userService.updateById(user);
                }

                //修改考试成绩
                //生成随机数，在60-79 之间
                int score = (int)(Math.random()*20);
                int sc = score + 60;
                examScore1.setTheoryGrade(sc);
                examScore1.setTheoryGrade(score/2);
                //设置为合格
                examScore1.setQualified(2);
                examScore1.setCandidateNo(trainingRegistration1.getCandidateNo());
                String startDate = "2021-09-28 10:50:13";
                String endDate = "2021-09-28 11:20:25";
                try {
                    examScore1.setExamTime(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse(startDate));
                    examScore1.setExamEndTime(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse(endDate));
                } catch (ParseException e) {
                    e.printStackTrace();
                }
                baseMapper.updateById(examScore1);
            });
        }

        if (updateParamVo.getType()==2) {
            //查询所有成绩不合格的数据(准考证号)
            List<ExamScore> list = baseMapper.getDisqualificationExamInfo();
            if (list.size() > 0) {
                list.forEach(examScore1 -> {
                    //修改考试成绩为合格
                    //生成随机数，在60-79 之间
                    int score = (int)((Math.random() * 20));
                    int sc = score+60;
                    examScore1.setTheoryGrade(sc);
                    //设置为合格
                    examScore1.setQualified(2);
                    if (null != examScore1.getLearnGrade()) {
                        examScore1.setTheoryGrade((examScore1.getLearnGrade() + score) / 2);
                    } else {
                        examScore1.setTheoryGrade(score / 2);
                    }
                    //修改
                    baseMapper.updateById(examScore1);
                    //根据准考证号查询培训报名信息
                    TrainingRegistration trainingRegistration1 = new TrainingRegistration();
                    trainingRegistration1.setCandidateNo(examScore1.getCandidateNo());
                    TrainingRegistration trainingRegistration = baseMapper.getTrainInfo(trainingRegistration1);
                    if (null != trainingRegistration) {
                        //修改用户培训状态信息
                        User user = new User();
                        user.setIsTrain(3);
                        user.setId(Long.parseLong(trainingRegistration.getUserId()));
                        //修改
                        userService.updateById(user);
                    }
                });
            }
        }

        if (updateParamVo.getType()==3) {
            //查询所有成绩不合格的数据(准考证号)
            List<ExamScore> list = baseMapper.getLheoryGradeLess60();
            if (list.size() > 0) {
                list.forEach(examScore1 -> {
                    //修改考试成绩为合格
                    //生成随机数，在60-79 之间
                    int score = (int)((Math.random() * 20));
                    int sc = score+60;
                    examScore1.setTheoryGrade(sc);
                    //设置为合格
                    examScore1.setQualified(2);
                    examScore1.setAllGrade(examScore1.getTheoryGrade() / 2);
                    //修改
                    baseMapper.updateById(examScore1);

                });
            }
        }
        return true;
    }
}

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

@@ -280,13 +280,6 @@
                    examSubjectOptionService.save(examSubjectOption4);
                }
            }

            //内网数据同步
            try {
//                arg.test01(arg.url+"/examSubjectChoices/saveSubjectChoicesAndOption",examSubjectChoices);
            } catch (Exception e) {
                e.printStackTrace();
            }
        });

    }
@@ -304,6 +297,7 @@
        ExamSubjectChoices choices = this.getById(preSubJectId);
        //对比答案
        if (choices.getChoicesType() == 2 || choices.getChoicesType() == 3){
            //保存答题记录
            //判断题逻辑
            if (preResult.equals(choices.getAnswer())) {
                return 1;

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

New file
@@ -0,0 +1,11 @@
package org.springblade.modules.exam.vo;

import lombok.Data;

import java.io.Serializable;

@Data
public class UpdateParamVo implements Serializable {

    private Integer type;
}

 src/main/java/org/springblade/modules/information/mapper/InformationMapper.java

@@ -61,5 +61,5 @@
     * @return
     */
    List<InformationVO> securityPage(IPage<InformationVO> page, @Param("information") InformationVO information);
    List queryYearKh(String year,String jurisdiction,String deptid);
    List queryYearKh(@Param("year") String year,@Param("jurisdiction")String jurisdiction,@Param("deptid")String deptid);
}

 src/main/java/org/springblade/modules/information/mapper/InformationMapper.xml

@@ -177,16 +177,26 @@
        IFNULL( B.num, 0 ) AS bzc
        FROM
        (
        SELECT departmentid, jurisdiction FROM sys_information GROUP BY departmentid, jurisdiction ) H
        LEFT JOIN ( SELECT COUNT(*) AS num, u.dept_id FROM blade_user u WHERE (u.examination_type = 0 or
        SELECT departmentid, jurisdiction FROM sys_information GROUP BY departmentid, jurisdiction
        ) H
        LEFT JOIN
        ( SELECT COUNT(*) AS num, u.dept_id FROM blade_user u WHERE (u.examination_type = 0 or
        u.examination_type is null) and role_id =
        '1412226235153731586' AND `status` = 1 AND is_deleted = 0 GROUP BY u.dept_id
        ) A ON H.departmentid = A.dept_id
        LEFT JOIN ( SELECT COUNT(*) AS num, u.dept_id FROM blade_user u WHERE u.examination_type = 1  and role_id = '1412226235153731586' AND `status` = 1 AND is_deleted = 0 GROUP BY
        ) A
        ON H.departmentid = A.dept_id
        LEFT JOIN
        ( SELECT COUNT(*) AS num, u.dept_id FROM blade_user u WHERE u.examination_type = 1  and role_id = '1412226235153731586' AND `status` = 1 AND is_deleted = 0 GROUP BY
        u.dept_id
        ) B ON H.departmentid = B.dept_id where 1=1
        ) B
        ON H.departmentid = B.dept_id
        left join
        sys_jurisdiction sj
        on
        sj.id = H.jurisdiction
        where 1=1
        <if test="jurisdiction!=null and jurisdiction!='' and jurisdiction!='1372091709474910209'">
            and H.jurisdiction = #{jurisdiction}
            and (sj.id = #{jurisdiction} or sj.parent_id = #{jurisdiction})
        </if>
        <if test="deptid!=null and deptid!=''">
            and H.departmentid = #{deptid}
@@ -203,7 +213,8 @@
        (
        SELECT departmentid,jurisdiction FROM sys_information GROUP BY departmentid,jurisdiction
        ) H
        LEFT JOIN(
        LEFT JOIN
        (
        SELECT
        COUNT( * ) AS thisnum,
        dept_id
@@ -216,8 +227,10 @@
        AND hold = 1
        GROUP BY
        dept_id
        ) A ON A.dept_id=H.departmentid
        LEFT JOIN (
        ) A
        ON A.dept_id=H.departmentid
        LEFT JOIN
        (
        SELECT
        COUNT(*) AS lastnum,
        dept_id
@@ -230,13 +243,18 @@
        AND hold = 2
        GROUP BY
        dept_id
        ) B ON B.dept_id=H.departmentid
        ) B
        ON B.dept_id=H.departmentid
        left join
        sys_jurisdiction sj
        on
        sj.id = H.jurisdiction
        where 1=1
        <if test="jurisdiction!=null and jurisdiction!='' and jurisdiction!='1372091709474910209'">
            and (sj.id = #{jurisdiction} or sj.parent_id = #{jurisdiction})
        </if>
        <if test="deptid!=null and deptid!=''">
            and H.departmentid = #{deptid}
        </if>
        <if test="jurisdiction!=null and jurisdiction!='' and jurisdiction!='1372091709474910209'">
            and H.jurisdiction = #{jurisdiction}
        </if>
    </select>
    <!--派遣服务单位统计-->
@@ -268,7 +286,8 @@
        AND dispatch = 0
        GROUP BY
        dept_id
        ) A ON A.dept_id = H.departmentid
        ) A
        ON A.dept_id = H.departmentid
        LEFT JOIN (
        SELECT
        COUNT( * ) AS lastnum,
@@ -282,9 +301,15 @@
        AND dispatch = 1
        GROUP BY
        dept_id
        ) B ON H.departmentid = B.dept_id where 1=1
        ) B
        ON H.departmentid = B.dept_id
        left join
        sys_jurisdiction sj
        on
        sj.id = H.jurisdiction
        where 1=1
        <if test="jurisdiction!=null and jurisdiction!='' and jurisdiction!='1372091709474910209'">
            and H.jurisdiction = #{jurisdiction}
            and (sj.id = #{jurisdiction} or sj.parent_id = #{jurisdiction})
        </if>
        <if test="deptid!=null and deptid!=''">
            and H.departmentid = #{deptid}
@@ -294,20 +319,38 @@
    <select id="selectSoil" resultType="java.util.HashMap">
        SELECT IFNULL(A.numj, 0) as numj,
        IFNULL(B.numwj, 0) as numz
        FROM (SELECT departmentid, jurisdiction FROM sys_information GROUP BY departmentid, jurisdiction) H
        LEFT JOIN (SELECT COUNT(*) AS numj, dept_id
        FROM
        (
        SELECT departmentid, jurisdiction FROM sys_information GROUP BY departmentid, jurisdiction
        ) H
        LEFT JOIN
        (
        SELECT COUNT(*) AS numj, dept_id
        FROM blade_user
        WHERE soil = 0 AND `status` = 1 AND is_deleted = 0 AND role_id = '1412226235153731586'
        GROUP BY dept_id) A ON A.dept_id = H.departmentid
        LEFT JOIN (SELECT COUNT(*) AS numwj, dept_id FROM blade_user WHERE soil = 1 and `status` = 1 AND is_deleted = 0
        AND role_id = '1412226235153731586' GROUP BY
        dept_id) B
        ON H.departmentid = B.dept_id where 1=1
        GROUP BY dept_id
        ) A
        ON A.dept_id = H.departmentid
        LEFT JOIN
        (
        SELECT COUNT(*) AS numwj, dept_id FROM blade_user
        WHERE soil = 1
        and `status` = 1
        AND is_deleted = 0
        AND role_id = '1412226235153731586'
        GROUP BY dept_id
        ) B
        ON H.departmentid = B.dept_id
        left join
        sys_jurisdiction sj
        on
        sj.id = H.jurisdiction
        where 1=1
        <if test="jurisdiction!=null and jurisdiction!='' and jurisdiction!='1372091709474910209'">
            and (sj.id = #{jurisdiction} or sj.parent_id = #{jurisdiction})
        </if>
        <if test="deptid!=null and deptid!=''">
            and H.departmentid = #{deptid}
        </if>
        <if test="jurisdiction!=null and jurisdiction!='' and jurisdiction!='1372091709474910209'">
            and H.jurisdiction = #{jurisdiction}
        </if>
    </select>

@@ -340,16 +383,21 @@
    <!--查询本年所有月份h合同开始时间的客户数量-->
    <select id="queryYearKh" resultType="java.util.HashMap">
        SELECT
        start_time,
        end_time
        sdu.start_time,
        sdu.end_time
        FROM
        `sys_dispatcher_unit`
        WHERE YEAR (end_time) >='2021'
        sys_dispatcher_unit sdu
        left join
        sys_jurisdiction sj
        on
        sj.id = sdu.jurisdiction
        where 1=1
        and YEAR (end_time) >= #{year}
        <if test="jurisdiction!=null and jurisdiction!='' and jurisdiction!='1372091709474910209'">
            and jurisdiction =#{jurisdiction}
            and (sj.id = #{jurisdiction} or sj.parent_id = #{jurisdiction})
        </if>
        <if test="deptid!=null and deptid!=''">
            and dept_id =#{deptid}
            and sdu.dept_id =#{deptid}
        </if>
    </select>
</mapper>

 src/main/java/org/springblade/modules/social/mapper/SocialMapper.xml

@@ -27,7 +27,21 @@
        u.real_name as realname
        FROM
        sys_socil s
        LEFT JOIN blade_user u ON s.namb = u.id where 1=1
        LEFT JOIN
        blade_user u
        ON
        s.namb = u.id
        left join
        sys_jurisdiction sj
        on
        sj.id = s.jurisdiction
        where 1=1
        and u.is_deleted = 0
        AND u.status = 1
        AND u.role_id = '1412226235153731586'
        <if test="social.jurisdiction!=null and social.jurisdiction!='' and social.jurisdiction!='1372091709474910209'">
            and (sj.id=#{social.jurisdiction} or sj.parent_id = #{social.jurisdiction})
        </if>
        <if test="social.realname!=null and social.realname!=''">
            and u.real_name like concat(concat('%', #{social.realname}),'%')
        </if>
@@ -36,9 +50,6 @@
        </if>
        <if test="social.deptid!=null and social.deptid!=''">
            and s.deptid=#{social.deptid}
        </if>
        <if test="social.jurisdiction!=null and social.jurisdiction!=''">
            and s.jurisdiction=#{social.jurisdiction}
        </if>
    </select>


 src/main/resources/application-prod.yml

@@ -2,8 +2,6 @@
spring:
  redis:
    ##redis 单机环境配置
    ##将docker脚本部署的redis服务映射为宿主机ip
    ##生产环境推荐使用阿里云高可用redis服务并设置密码
    host: 127.0.0.1
    port: 6379
    password:
@@ -14,9 +12,42 @@
    #  nodes: 127.0.0.1:7001,127.0.0.1:7002,127.0.0.1:7003
    #  commandTimeout: 5000
  datasource:
    url: jdbc:mysql://localhost:3306/bladex_boot?useSSL=false&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&transformedBitIsBoolean=true&serverTimezone=GMT%2B8&nullCatalogMeansCurrent=true&allowPublicKeyRetrieval=true
    # MySql
    url: jdbc:mysql://223.82.109.183:2083/zhbaw?useSSL=false&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&transformedBitIsBoolean=true&serverTimezone=GMT%2B8&nullCatalogMeansCurrent=true&allowPublicKeyRetrieval=true
    username: root
    password: root
    password: zhba0728

    # PostgreSQL
    #url: jdbc:postgresql://127.0.0.1:5432/bladex_boot
    #username: postgres
    #password: 123456
    # Oracle
    #url: jdbc:oracle:thin:@127.0.0.1:1521:orcl
    #username: BLADEX_BOOT
    #password: BLADEX_BOOT
    # SqlServer
    #url: jdbc:sqlserver://223.82.109.183:1433;DatabaseName=zhbaw
    #username: sa
    #password: zhbaw@2021


#图片批量上传zip
upload:
  localtion: ${UPLOAD_DIR:D:/test}
  maxFileSize: 10240KB
  maxRequestSize: 102400KB

#ftp 设置
ftp:
  sqlConnect: jdbc:mysql://223.82.109.183:2083/zhbaw?useSSL=false&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&transformedBitIsBoolean=true&serverTimezone=GMT%2B8&nullCatalogMeansCurrent=true&allowPublicKeyRetrieval=true
  ftpHost: 192.168.0.199
  ftpPort: 21
  ftpUserName: arsn
  ftpPassword: 123456
  ftpPath: anbao/
  localPath: D:\\anbao\\
  ip: http://223.82.109.183:2081
  jsonUrl: D:\\anbao\\

#第三方登陆
social:
@@ -29,12 +60,13 @@
  lock:
    ##是否启用分布式锁
    enabled: false
    ##将docker脚本部署的redis服务映射为宿主机ip
    ##生产环境推荐使用阿里云高可用redis服务并设置密码
    ##redis服务地址
    address: redis://127.0.0.1:6379
    password: 123456
  #本地文件上传
  file:
    remote-mode: true
    upload-domain: http://localhost:8999
    remote-path: /usr/share/nginx/html




 src/main/resources/application-test.yml

@@ -13,7 +13,7 @@
    #  commandTimeout: 5000
  datasource:
    # MySql
    url: jdbc:mysql://223.82.109.183:2083/zhbaw?useSSL=false&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&transformedBitIsBoolean=true&serverTimezone=GMT%2B8&nullCatalogMeansCurrent=true&allowPublicKeyRetrieval=true
    url: jdbc:mysql://223.82.109.183:2083/zhbaw-test?useSSL=false&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&transformedBitIsBoolean=true&serverTimezone=GMT%2B8&nullCatalogMeansCurrent=true&allowPublicKeyRetrieval=true
    username: root
    password: zhba0728