需要用到的时间工具
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>5.8.10</version>
<scope>compile</scope>
</dependency>
controller
/**
* 临停收费统计
*/
@GetMapping("/parkTempChargeStat")
public List<ParkTempChargeStatVo> parkTempChargeStat(@Validated ChargeStatForm form) {
return pmaStatService.parkTempChargeStat(form);
}
service
@Override
public List<ParkTempChargeStatVo> parkTempChargeStat(ChargeStatForm form) {
// 查询项目vpnIp
Map<String, Object> pmap = punitMapper.findInfoByUnitKey(form.getUnitKey());
if (pmap == null) {
throw new ResponseException("未找到项目信息");
}
long punitId = Const.getLong(pmap.get("id"));
Date beginDate;
Date endDate;
List<Date> dates;
// 按月
form.setTypeStr("'%Y-%m-%d'");
if (Const.getInteger(form.getStype()) == 1) {
form.setTypeStr("'%Y-%m'");
beginDate = DateUtil.parse(form.getBeginTime(), "yyyy-MM");
endDate = DateUtil.parse(form.getEndTime(), "yyyy-MM");
dates = findDates(beginDate, endDate, Calendar.MONTH);
} else { // 按天
beginDate = DateUtil.parse(form.getBeginTime(), "yyyy-MM-dd");
endDate = DateUtil.parse(form.getEndTime(), "yyyy-MM-dd");
dates = findDates(beginDate, endDate, Calendar.DAY_OF_MONTH);
}
List<ParkTempChargeStatVo> vos = parkCardChargeLogMapper.statByChargeTime(punitId, form);
for (Date date : dates) {
String dateStr = DateUtil.format(date, (Const.getInteger(form.getStype()) == 1) ? "yyyy-MM" : "yyyy-MM-dd");
if (vos.stream().noneMatch(item -> dateStr.equals(item.getStatDate()))) {
ParkTempChargeStatVo vo = new ParkTempChargeStatVo();
vo.setStatDate(dateStr);
vos.add(vo);
}
}
return vos.stream().sorted(Comparator.comparing(ParkTempChargeStatVo::getStatDate).reversed()).collect(Collectors.toList());
}
mapper
@DSource
@Select("select t.* from ( " +
" select from_unixtime(chargeTime, ${form.typeStr} ) statDate, count(*) chargeNum, sum(money) money, sum(chargeMoney) chargeMoney, sum(saleMoney) saleMoney, sum(prepaidMoney) payOfMoney " +
" from @{db}.parkcardchargeLog :where group by from_unixtime(chargeTime, ${form.typeStr} ) ) t order by t.statDate desc ")
@Condition("punitId = #{punitId}")
@Condition("from_unixtime(chargeTime, ${form.typeStr} ) >= :form.beginTime")
@Condition("from_unixtime(chargeTime, ${form.typeStr} ) <= :form.endTime")
List<ParkTempChargeStatVo> statByChargeTime(@Param("punitId") long punitId, @Param("form") ChargeStatForm from);
util
/**
* 获取指定时间区间的所有数据(包含日期和月份)
*
* @param beginDate 开始时间
* @param endDate 结束时间
* @param rule 日历规则 如:Calendar.DAY_OF_MONTH、Calendar.MONTH
*/
private List<Date> findDates(Date beginDate, Date endDate, int rule) {
List<Date> dates = new ArrayList<>();
if (endDate.before(beginDate)) {
return dates;
}
dates.add(beginDate);
Calendar calBegin = Calendar.getInstance();
calBegin.setTime(beginDate);
Calendar calEnd = Calendar.getInstance();
calEnd.setTime(endDate);
while (endDate.after(calBegin.getTime())) {
calBegin.add(rule, 1);
dates.add(calBegin.getTime());
}
return dates;
}