mybatis-plus查询时间段内的报表(月报等)

1.报表频度

/**
 * 报表频率
 *
 * @author wanghuidong
 * @date 2022/2/24 16:39
 */
public enum ReportFrequencyEnum {

    YEAR("year", "年"),

    MONTH("month", "月"),

    HALF_YEAR("half_year", "半年"),

    SEASON("season", "季");

    ReportFrequencyEnum(String code, String name) {
        this.code = code;
        this.name = name;
    }

    private String code;
    private String name;

    public String getCode() {
        return code;
    }

    public void setCode(String code) {
        this.code = code;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    /**
     * 获取指定code的报表频率
     *
     * @param code 报表频率code
     * @return
     */
    public static ReportFrequencyEnum codeOf(String code) {
        for (ReportFrequencyEnum reportFrequencyEnum : ReportFrequencyEnum.values()) {
            if (reportFrequencyEnum.getCode().equals(code)) {
                return reportFrequencyEnum;
            }
        }
        return null;
    }
}

 

2.报表时间

@Data
@ApiModel("报表时间")
public class ReportTime {

    @NotNull
    @ApiModelProperty("年")
    private Integer year;

    @ApiModelProperty("月(1-12)")
    private Integer month;

    @ApiModelProperty("季(1-4)")
    private Integer season;

    @ApiModelProperty("半年(上/下)")
    private String halfYear;
}

 

3.设置mybatis-plus的queryWrapper

/**
     * 设置queryWrapper的报表时间范围
     *
     * <p>
     * 算法逻辑示例：
     * 不跨年月报: 2022/2 - 2022/3
     * and year=2022 and month between 2 and 3
     * <p>
     * 跨年月报：2021/9 - 2023/4
     * and(
     * (year = 2021 and month >=9)
     * or
     * (year = 2022)
     * or
     * (year = 2023 and month <=4)
     * )
     *
     * @param queryWrapper  查询条件包装对象
     * @param frequencyEnum 报表频度
     * @param beginTime     开始时间
     * @param endTime       结束时间
     */
    public static void setQueryWrapperTimePeriod(QueryWrapper<?> queryWrapper, ReportFrequencyEnum frequencyEnum, ReportTime beginTime, ReportTime endTime) {
        Integer startYear = beginTime.getYear();
        Integer endYear = endTime.getYear();

        // 开始时间大于结束时间，则直接让其查询不出来即可
        if (startYear > endYear) {
            queryWrapper.between("year", startYear, endYear);
            return;
        }
        //判断是否跨年
        boolean acrossYear = startYear.intValue() != endYear.intValue();
        //年之间间隔的年份
        List<Integer> centerYears = new ArrayList<>();
        for (int i = startYear + 1; i < endYear; i++) {
            centerYears.add(i);
        }
        switch (frequencyEnum) {
            case YEAR:
                queryWrapper.between("year", startYear, endYear);
                break;
            case MONTH:
                Integer startMonth = beginTime.getMonth();
                Integer endMonth = endTime.getMonth();
                if (acrossYear) {
                    queryWrapper.and(x ->
                            x.nested(x1 -> x1.eq("year", startYear).ge("month", startMonth))
                                    .or(CollUtil.isNotEmpty(centerYears), x2 -> x2.in("year", centerYears))
                                    .or(x3 -> x3.eq("year", endYear).le("month", endMonth))
                    );
                } else {
                    queryWrapper.eq("year", startYear).between("month", startMonth, endMonth);
                }
                break;
            case SEASON:
                Integer startSeason = beginTime.getSeason();
                Integer endSeason = endTime.getSeason();
                if (acrossYear) {
                    queryWrapper.and(x ->
                            x.nested(x1 -> x1.eq("year", startYear).ge("season", startSeason))
                                    .or(CollUtil.isNotEmpty(centerYears), x2 -> x2.in("year", centerYears))
                                    .or(x3 -> x3.eq("year", endYear).le("season", endSeason))
                    );
                } else {
                    queryWrapper.eq("year", startYear).between("season", startSeason, endSeason);
                }
                break;
            case HALF_YEAR:
                String startHalfYear = beginTime.getHalfYear();
                String endHalfYear = endTime.getHalfYear();
                List<String> startHalfYears = new ArrayList<>();
                startHalfYears.add(startHalfYear);
                if ("上".equals(startHalfYear)) {
                    startHalfYears.add("下");
                }
                List<String> endHalfYears = new ArrayList<>();
                endHalfYears.add(endHalfYear);
                if ("下".equals(endHalfYear)) {
                    endHalfYears.add("上");
                }
                if (acrossYear) {
                    queryWrapper.and(x ->
                            x.nested(x1 -> x1.eq("year", startYear).in("half_year", startHalfYears))
                                    .or(CollUtil.isNotEmpty(centerYears), x2 -> x2.in("year", centerYears))
                                    .or(x3 -> x3.eq("year", endYear).in("half_year", endHalfYears))
                    );
                } else {
                    List<String> halfYears = Arrays.asList(startHalfYear, endHalfYear);
                    halfYears = halfYears.stream().distinct().collect(Collectors.toList());
                    queryWrapper.eq("year", startYear).in("half_year", halfYears);
                }
                break;
        }
    }

 

 

4.查询数据

        QueryWrapper<WarnTargetData> queryWrapper = new QueryWrapper<>();
        //select指定字段，查询指定指标
        queryWrapper.select(this.getSelectField(queryParams.getTargetIds()));
        //查询指定业务模块
        queryWrapper.eq("business", businessEnum.getCode());
        queryWrapper.eq("frequency", queryParams.getFrequency());
        if (CollUtil.isNotEmpty(queryParams.getOrgIds())) {
            queryWrapper.in("org_id", queryParams.getOrgIds());
        }

        //指定查询时间
        ReportUtil.setQueryWrapperTimePeriod(queryWrapper, frequencyEnum, queryParams.getBeginTime(), queryParams.getEndTime());
        IPage<WarnTargetData> page = warnTargetDataService.page(new Page<>(queryParams.getCurrent(), queryParams.getSize()), queryWrapper);