考核结果导出优化2

fuliqi

2024-09-06 045023133d111be97ad52f097b4b99a2cf5a8311

考核结果导出优化2

 ycl-pojo/src/main/java/com/ycl/platform/domain/entity/CheckIndexCar.java

@@ -93,7 +93,7 @@
    //转换成字符串用于excel导出
    public static CheckIndexCarVO getExcelVo(@NonNull CheckIndexCar checkIndexCar) {
        CheckIndexCarVO checkIndexCarVo = new CheckIndexCarVO();
        checkIndexCarVo.setScore(checkIndexCar.getScore());
        checkIndexCarVo.setScore(checkIndexCar.getScore().setScale(2,RoundingMode.HALF_UP));
        checkIndexCarVo.setDeptName(checkIndexCar.getDeptName());
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
        //避免合计行报错(合计数据 缺少createTimeStr字段 直接将createTimeStr设置为"合计")
@@ -148,7 +148,7 @@
        checkIndexCar.setCreateTimeStr("合计");
        checkIndexCar.setDeptName(checkIndexCars.get(0).getDeptName());
        checkIndexCar.setExamineTag(checkIndexCars.get(0).getExamineTag());
        checkIndexCar.setScore(scoreSum);
        checkIndexCar.setScore(scoreSum.divide(new BigDecimal(checkIndexCars.size()), 2, RoundingMode.HALF_UP));
        checkIndexCar.setViewConnectStability(viewConnectStabilitySum.divide(new BigDecimal(checkIndexCars.size()), 2, RoundingMode.HALF_UP));
        checkIndexCar.setSiteOnline(siteOnlineSum.divide(new BigDecimal(checkIndexCars.size()), 2, RoundingMode.HALF_UP));
        checkIndexCar.setDeviceDirectoryConsistent(deviceDirectoryConsistentSum.divide(new BigDecimal(checkIndexCars.size()), 2, RoundingMode.HALF_UP));

 ycl-pojo/src/main/java/com/ycl/platform/domain/entity/CheckIndexFace.java

@@ -83,7 +83,7 @@
    //转换成字符串用于excel导出
    public static CheckIndexFaceVO getExcelVo(@NonNull CheckIndexFace checkIndexFace) {
        CheckIndexFaceVO checkIndexFaceVo = new CheckIndexFaceVO();
        checkIndexFaceVo.setScore(checkIndexFace.getScore());
        checkIndexFaceVo.setScore(checkIndexFace.getScore().setScale(2,RoundingMode.HALF_UP));
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
        //避免合计行报错(合计数据 缺少createTimeStr字段 直接将createTimeStr设置为"合计")
        if (StringUtils.isEmpty(checkIndexFace.getCreateTimeStr())) {
@@ -133,7 +133,7 @@
        result.setCreateTimeStr("合计");
        result.setDeptName(checkIndexFaces.get(0).getDeptName());
        result.setExamineTag(checkIndexFaces.get(0).getExamineTag());
        result.setScore(scoreSum);
        result.setScore(scoreSum.divide(BigDecimal.valueOf(count), 2, RoundingMode.HALF_UP));
        result.setViewConnectStability(totalViewConnectStability.divide(BigDecimal.valueOf(count), 2, RoundingMode.HALF_UP));
        result.setSiteOnline(totalSiteOnline.divide(BigDecimal.valueOf(count), 2, RoundingMode.HALF_UP));
        result.setDeviceDirectoryConsistent(totalDeviceDirectoryConsistent.divide(BigDecimal.valueOf(count), 2, RoundingMode.HALF_UP));

 ycl-pojo/src/main/java/com/ycl/platform/domain/entity/CheckIndexVideo.java

@@ -121,7 +121,7 @@
    //转换成字符串用于excel导出
    public static CheckIndexVideoVO getExcelVo(@NonNull CheckIndexVideo checkIndexVideo) {
        CheckIndexVideoVO checkIndexVideoVO = new CheckIndexVideoVO();
        checkIndexVideoVO.setScore(checkIndexVideo.getScore());
        checkIndexVideoVO.setScore(checkIndexVideo.getScore().setScale(2,RoundingMode.HALF_UP));
        checkIndexVideoVO.setDeptName(checkIndexVideo.getDeptName());
        //避免合计行报错(合计数据 缺少createTimeStr字段 直接将createTimeStr设置为"合计")
        if (StringUtils.isEmpty(checkIndexVideo.getCreateTimeStr())) {
@@ -193,7 +193,7 @@
        averageVideo.setCreateTimeStr("合计");
        averageVideo.setDeptName(videos.get(0).getDeptName());
        averageVideo.setExamineTag(videos.get(0).getExamineTag());
        averageVideo.setScore(scoreSum);
        averageVideo.setScore(scoreSum.divide(BigDecimal.valueOf(count), 2, RoundingMode.HALF_UP));
        averageVideo.setPlatformOnline(totalPlatformOnline.divide(BigDecimal.valueOf(count), 2, RoundingMode.HALF_UP));
        averageVideo.setMonitorQualification(totalMonitorQualification.divide(BigDecimal.valueOf(count), 2, RoundingMode.HALF_UP));
        averageVideo.setMonitorRegistration(totalMonitorRegistration.divide(BigDecimal.valueOf(count), 2, RoundingMode.HALF_UP));

 ycl-pojo/src/main/java/com/ycl/platform/domain/entity/CheckScore.java

@@ -45,8 +45,8 @@
    @Excel(name = "考核标签(省厅/市局)")
    private Integer examineTag;

    /** 考核类型(车辆/人脸/视频) */
    @Excel(name = "考核类型(车辆/人脸/视频)")
    /** 考核类型(视频/车辆/人脸) */
    @Excel(name = "考核类型(视频/车辆/人脸)")
    private Short examineCategory;
    /** 修改时间 */
    @Excel(name = "修改时间")

 ycl-server/src/main/java/com/ycl/platform/service/impl/CheckScoreServiceImpl.java

@@ -317,19 +317,30 @@
        roleControl(checkIndexCar);
        List<CheckIndexCar> checkIndexCars = indexCarService.selectCheckIndexCarList(checkIndexCar);
        //计算平均值放在excel最后
        if (exportDTO.getAverage() && !CollectionUtils.isEmpty(checkIndexCars)) {
            //先区分各个区县，再区分考核标签
            Map<Long, List<CheckIndexCar>> carMap = checkIndexCars.stream().collect(Collectors.groupingBy(CheckIndexCar::getDeptId));
            carMap.forEach((deptId,list)->{
                List<CheckIndexCar> provinceList = list.stream().filter(car -> CheckConstants.Examine_Tag_Province.equals(car.getExamineTag())).collect(Collectors.toList());
                if (!CollectionUtils.isEmpty(provinceList)) {
                    checkIndexCars.add(CheckIndexCar.calculateAverage(provinceList));
                }
                List<CheckIndexCar> countyList = list.stream().filter(car -> CheckConstants.Examine_Tag_County.equals(car.getExamineTag())).collect(Collectors.toList());
                if (!CollectionUtils.isEmpty(countyList)) {
                    checkIndexCars.add(CheckIndexCar.calculateAverage(countyList));
                }
            });
        if (exportDTO.getAverage() != null && exportDTO.getAverage() && !CollectionUtils.isEmpty(checkIndexCars)) {
            //先区分考核标签，再区分区县
            List<CheckIndexCar> countyList = checkIndexCars.stream().filter(car -> CheckConstants.Examine_Tag_County.equals(car.getExamineTag())).collect(Collectors.toList());
            if (!CollectionUtils.isEmpty(countyList)) {
                Map<Long, List<CheckIndexCar>> carMap = countyList.stream().collect(Collectors.groupingBy(CheckIndexCar::getDeptId));
                List<CheckIndexCar> cars = new ArrayList<>();
                carMap.forEach((deptId, list) -> {
                    CheckIndexCar car = CheckIndexCar.calculateAverage(list);
                    cars.add(car);
                });
                //根据积分降序
                checkIndexCars.addAll(cars.stream().sorted(Comparator.comparing(CheckIndexCar::getScore).reversed()).collect(Collectors.toList()));
            }
            List<CheckIndexCar> provinceList = checkIndexCars.stream().filter(car -> CheckConstants.Examine_Tag_Province.equals(car.getExamineTag())).collect(Collectors.toList());
            if (!CollectionUtils.isEmpty(provinceList)) {
                Map<Long, List<CheckIndexCar>> carMap = provinceList.stream().collect(Collectors.groupingBy(CheckIndexCar::getDeptId));
                List<CheckIndexCar> cars = new ArrayList<>();
                carMap.forEach((deptId, list) -> {
                    CheckIndexCar car = CheckIndexCar.calculateAverage(list);
                    cars.add(car);
                });
                //根据积分降序
                checkIndexCars.addAll(cars.stream().sorted(Comparator.comparing(CheckIndexCar::getScore).reversed()).collect(Collectors.toList()));
            }
        }
        List<CheckIndexCarVO> checkIndexCarVOS = new ArrayList<>();
        for (CheckIndexCar indexCar : checkIndexCars) {
@@ -348,19 +359,30 @@
        roleControl(checkIndexFace);
        List<CheckIndexFace> checkIndexFaces = indexFaceService.selectCheckIndexFaceList(checkIndexFace);
        //计算平均值放在excel最后
        if (exportDTO.getAverage() && !CollectionUtils.isEmpty(checkIndexFaces)) {
            //先区分各个区县，再区分考核标签
            Map<Long, List<CheckIndexFace>> faceMap = checkIndexFaces.stream().collect(Collectors.groupingBy(CheckIndexFace::getDeptId));
            faceMap.forEach((deptId,list)->{
                List<CheckIndexFace> provinceList = list.stream().filter(face -> CheckConstants.Examine_Tag_Province.equals(face.getExamineTag())).collect(Collectors.toList());
                if (!CollectionUtils.isEmpty(provinceList)) {
                    checkIndexFaces.add(CheckIndexFace.calculateAverage(provinceList));
                }
                List<CheckIndexFace> countyList = list.stream().filter(face -> CheckConstants.Examine_Tag_County.equals(face.getExamineTag())).collect(Collectors.toList());
                if (!CollectionUtils.isEmpty(countyList)) {
                    checkIndexFaces.add(CheckIndexFace.calculateAverage(countyList));
                }
            });
        if (exportDTO.getAverage() != null && exportDTO.getAverage() && !CollectionUtils.isEmpty(checkIndexFaces)) {
            //先区分考核标签，再区分区县
            List<CheckIndexFace> countyList = checkIndexFaces.stream().filter(face -> CheckConstants.Examine_Tag_County.equals(face.getExamineTag())).collect(Collectors.toList());
            if (!CollectionUtils.isEmpty(countyList)) {
                Map<Long, List<CheckIndexFace>> faceMap = countyList.stream().collect(Collectors.groupingBy(CheckIndexFace::getDeptId));
                List<CheckIndexFace> faces = new ArrayList<>();
                faceMap.forEach((deptId, list) -> {
                    CheckIndexFace face = CheckIndexFace.calculateAverage(list);
                    faces.add(face);
                });
                //根据积分降序
                checkIndexFaces.addAll(faces.stream().sorted(Comparator.comparing(CheckIndexFace::getScore).reversed()).collect(Collectors.toList()));
            }
            List<CheckIndexFace> provinceList = checkIndexFaces.stream().filter(face -> CheckConstants.Examine_Tag_Province.equals(face.getExamineTag())).collect(Collectors.toList());
            if (!CollectionUtils.isEmpty(provinceList)) {
                Map<Long, List<CheckIndexFace>> faceMap = provinceList.stream().collect(Collectors.groupingBy(CheckIndexFace::getDeptId));
                List<CheckIndexFace> faces = new ArrayList<>();
                faceMap.forEach((deptId, list) -> {
                    CheckIndexFace face = CheckIndexFace.calculateAverage(list);
                    faces.add(face);
                });
                //根据积分降序
                checkIndexFaces.addAll(faces.stream().sorted(Comparator.comparing(CheckIndexFace::getScore).reversed()).collect(Collectors.toList()));
            }
        }
        List<CheckIndexFaceVO> checkIndexFaceVOS = new ArrayList<>();
        for (CheckIndexFace indexFace : checkIndexFaces) {
@@ -379,19 +401,30 @@
        roleControl(checkIndexVideo);
        List<CheckIndexVideo> checkIndexVideos = indexVideoService.selectCheckIndexVideoList(checkIndexVideo);
        //计算平均值放在excel最后
        if (exportDTO.getAverage() && !CollectionUtils.isEmpty(checkIndexVideos)) {
            //先区分各个区县，再区分考核标签
            Map<Long, List<CheckIndexVideo>> videoMap = checkIndexVideos.stream().collect(Collectors.groupingBy(CheckIndexVideo::getDeptId));
            videoMap.forEach((deptId,list)->{
                List<CheckIndexVideo> provinceList = list.stream().filter(video -> CheckConstants.Examine_Tag_Province.equals(video.getExamineTag())).collect(Collectors.toList());
                if (!CollectionUtils.isEmpty(provinceList)) {
                    checkIndexVideos.add(CheckIndexVideo.calculateAverage(provinceList));
                }
                List<CheckIndexVideo> countyList = list.stream().filter(video -> CheckConstants.Examine_Tag_County.equals(video.getExamineTag())).collect(Collectors.toList());
                if (!CollectionUtils.isEmpty(countyList)) {
                    checkIndexVideos.add(CheckIndexVideo.calculateAverage(countyList));
                }
            });
        if (exportDTO.getAverage() != null && exportDTO.getAverage() && !CollectionUtils.isEmpty(checkIndexVideos)) {
            //先区分考核标签，再区分区县
            List<CheckIndexVideo> countyList = checkIndexVideos.stream().filter(video -> CheckConstants.Examine_Tag_County.equals(video.getExamineTag())).collect(Collectors.toList());
            if (!CollectionUtils.isEmpty(countyList)) {
                Map<Long, List<CheckIndexVideo>> videoMap = countyList.stream().collect(Collectors.groupingBy(CheckIndexVideo::getDeptId));
                List<CheckIndexVideo> videos = new ArrayList<>();
                videoMap.forEach((deptId, list) -> {
                    CheckIndexVideo video = CheckIndexVideo.calculateAverage(list);
                    videos.add(video);
                });
                //根据积分降序
                checkIndexVideos.addAll(videos.stream().sorted(Comparator.comparing(CheckIndexVideo::getScore).reversed()).collect(Collectors.toList()));
            }
            List<CheckIndexVideo> provinceList = checkIndexVideos.stream().filter(video -> CheckConstants.Examine_Tag_Province.equals(video.getExamineTag())).collect(Collectors.toList());
            if (!CollectionUtils.isEmpty(provinceList)) {
                Map<Long, List<CheckIndexVideo>> videoMap = provinceList.stream().collect(Collectors.groupingBy(CheckIndexVideo::getDeptId));
                List<CheckIndexVideo> videos = new ArrayList<>();
                videoMap.forEach((deptId, list) -> {
                    CheckIndexVideo video = CheckIndexVideo.calculateAverage(list);
                    videos.add(video);
                });
                //根据积分降序
                checkIndexVideos.addAll(videos.stream().sorted(Comparator.comparing(CheckIndexVideo::getScore).reversed()).collect(Collectors.toList()));
            }
        }
        List<CheckIndexVideoVO> checkIndexVideoVOS = new ArrayList<>();
        for (CheckIndexVideo indexVideo : checkIndexVideos) {

 ycl-server/src/main/resources/mapper/zgyw/CheckIndexCarMapper.xml

@@ -31,11 +31,12 @@
        from t_check_index_car tcic left join sys_dept sd on tcic.dept_id = sd.dept_id
        left join t_check_score tcs on tcic.id = tcs.index_id
        <where>
            tcs.examine_category = 2
            <if test="deptId != null "> and tcic.dept_id = #{deptId}</if>
            <if test="examineTag != null "> and examine_tag = #{examineTag}</if>
            <if test="examineTag != null "> and tcic.examine_tag = #{examineTag}</if>
            <if test="date != null "> and  date_format(tcic.create_time,'%Y-%m') = #{date}</if>
            <if test="day != null "> and  date(tcic.create_time) = #{day}</if>
            <if test="params.publish != null  and params.publish != ''">and publish = #{params.publish}</if>
            <if test="params.publish != null  and params.publish != ''">and tcic.publish = #{params.publish}</if>
            <if test="deptIds != null and deptIds.size()>0">and tcic.dept_id in
                <foreach collection="deptIds" separator="," open="(" close=")" item="deptId">
                    #{deptId}
@@ -47,6 +48,7 @@
                </foreach>
            </if>
        </where>
        order by tcic.examine_tag desc,tcic.create_time,tcs.score desc
    </select>

    <select id="selectCheckIndexCarById" resultMap="CheckIndexCarResult">

 ycl-server/src/main/resources/mapper/zgyw/CheckIndexFaceMapper.xml

@@ -28,11 +28,12 @@
        from t_check_index_face tcif left join sys_dept sd on tcif.dept_id = sd.dept_id
        left join t_check_score tcs on tcif.id = tcs.index_id
        <where>
            tcs.examine_category = 3
            <if test="deptId != null "> and tcif.dept_id = #{deptId}</if>
            <if test="examineTag != null "> and examine_tag = #{examineTag}</if>
            <if test="examineTag != null "> and tcif.examine_tag = #{examineTag}</if>
            <if test="date != null "> and  date_format(tcif.create_time,'%Y-%m') = #{date}</if>
            <if test="day != null "> and  date(tcif.create_time) = #{day}</if>
            <if test="params.publish != null  and params.publish != ''">and publish = #{params.publish}</if>
            <if test="params.publish != null  and params.publish != ''">and tcif.publish = #{params.publish}</if>
            <if test="deptIds != null ">and tcif.dept_id in
                <foreach collection="deptIds" separator="," open="(" close=")" item="deptId">
                    #{deptId}
@@ -44,6 +45,7 @@
                </foreach>
            </if>
        </where>
        order by tcif.examine_tag desc,tcif.create_time,tcs.score desc
    </select>

    <select id="selectCheckIndexFaceById"  resultMap="CheckIndexFaceResult">

 ycl-server/src/main/resources/mapper/zgyw/CheckIndexVideoMapper.xml

@@ -34,12 +34,13 @@
        from t_check_index_video tciv left join sys_dept sd on tciv.dept_id = sd.dept_id
        left join t_check_score tcs on tciv.id = tcs.index_id
        <where>
            tcs.examine_category = 1
            <if test="deptId != null "> and tciv.dept_id = #{deptId}</if>
            <if test="examineTag != null "> and examine_tag = #{examineTag}</if>
            <if test="examineTag != null "> and tciv.examine_tag = #{examineTag}</if>
            <if test="date != null "> and  date_format(tciv.create_time,'%Y-%m') = #{date}</if>
            <if test="day != null "> and  date(tciv.create_time) = #{day}</if>
            <if test="createTime != null "> and  date(tciv.create_time) = #{createTime}</if>
            <if test="params.publish != null  and params.publish != ''">and publish = #{params.publish}</if>
            <if test="params.publish != null  and params.publish != ''">and tciv.publish = #{params.publish}</if>
            <if test="deptIds != null ">and tciv.dept_id in
            <foreach collection="deptIds" separator="," open="(" close=")" item="deptId">
                #{deptId}
@@ -51,6 +52,7 @@
                </foreach>
            </if>
        </where>
        order by tciv.examine_tag desc,tciv.create_time,tcs.score desc
    </select>

    <select id="selectCheckIndexVideoById"  resultMap="CheckIndexVideoResult">

 ycl-server/src/main/resources/mapper/zgyw/CheckScoreMapper.xml

@@ -161,7 +161,7 @@
            <if test="params.publish != null  and params.publish != ''">and publish = #{params.publish}</if>
            ${params.dataScope}
        </where>
        -- GROUP BY tcs.dept_id, tcs.examine_category, tcs.examine_tag
        GROUP BY tcs.dept_id, tcs.examine_category, tcs.examine_tag
    </select>

    <select id="getLast" resultType="com.ycl.platform.domain.entity.CheckScore">