| java实现日报表、月报表统计，没数据补0 | 您所在的位置：网站首页 › 合计报表怎么做 › java实现日报表、月报表统计，没数据补0 |
java实现日报表、月报表统计，没数据补0
|
产品需求：
1、日报表
2、月报表 需求点： 前端传日期或月份区间，当数据库中指定的日期或月份没数据时也需要界面显示，但领取人数和使用人数需要自动补齐0； 举例：2020-11-28、2020-11-29，数据库中没数据，但也需要在界面显示，领取人数和使用人数为0；
1、首先根据前端传来的时间区间，查询出区间中所有的日期或月份； 2、然后循环日期或月份和数据库查询返回的List的日期或月份进行匹配； 2.1、不存在则在List中新增一条实体对象数据； 3、最后统一把处理后的List返回前端； sql语句（后面Mapper.xml中全部详细列出）注：这里是Mysql数据库，以日报表为例，** 月报表则是将 DATE_FORMAT格式修改为 %Y-%m 即可 ** SELECT t.days, sum( t.receiveQty ) AS receiveQty, sum( t.userQty ) AS userQty FROM ( SELECT DATE_FORMAT( r.created_time, '%Y-%m-%d' ) AS days, count( r.id ) AS receiveQty, 0 AS userQty FROM dg_experience_receive r LEFT JOIN dg_experience_order o ON r.user_id = o.user_id WHERE DATE_FORMAT( r.created_time, '%Y-%m-%d' ) >= '2020-11-01' AND DATE_FORMAT( r.created_time, '%Y-%m-%d' ) = '2020-11-01' AND DATE_FORMAT( r.created_time, '%Y-%m-%d' ) /** * 日期 */ private String days; /** * 领取人数 */ private Long receiveQty; /** * 使用人数 */ private Long userQty; } Controller /** * 体验金日报表--查询 */ @PostMapping("/experienceDay/list") public ResponseData listExperienceDayByReq(@Valid @RequestBody DepositExperienceDayReq experienceDayReq){ return ResponseData.success(experienceOrderService.listExperienceDayByReq(experienceDayReq)); } /** * 体验金月报表--查询 */ @PostMapping("/experienceMonth/list") public ResponseData listExperienceMonthByReq(@Valid @RequestBody DepositExperienceDayReq experienceDayReq){ return ResponseData.success(experienceOrderService.listExperienceMonthByReq(experienceDayReq)); } Service /** * 运营报表--体验金日报表--查询 * @param experienceDayReq * @return */ List listExperienceDayByReq(DepositExperienceDayReq experienceDayReq); /** * 运营报表--体验金月报表--查询 * @param experienceDayReq * @return */ List listExperienceMonthByReq(DepositExperienceDayReq experienceDayReq); ServiceImpl(这里是重点，需要看一下)注： DateUtil使用的是 hutool-core-5.0.6.jar /** * 运营报表--体验金日报表--查询 * @param experienceDayReq * @return */ @Override public List listExperienceDayByReq(DepositExperienceDayReq experienceDayReq) { // 首先根据前端传来的时间区间，查询出区间中所有的日期； List lDate = findDates(DateUtil.parseDate(experienceDayReq.getStartDate()), DateUtil.parseDate(experienceDayReq.getEndDate()), Calendar.DAY_OF_MONTH); if (CollectionUtils.isEmpty(lDate)){ return null; } List experienceDayResList = baseMapper.listExperienceDayByReq(experienceDayReq); for (Date date : lDate){ // 当前日期不存在则新增 String dateStr = DateUtil.format(date, "yyyy-MM-dd"); // 进行匹配 if(!experienceDayResList.stream().filter(item -> dateStr.equals(item.getDays())).findAny().isPresent()){ DepositExperienceDayRes experienceDayRes = DepositExperienceDayRes.builder() .days(dateStr) .receiveQty(0L) .userQty(0L) .build(); experienceDayResList.add(experienceDayRes); } } List collect = experienceDayResList.stream().sorted(Comparator.comparing(DepositExperienceDayRes::getDays).reversed()).collect(Collectors.toList()); return collect; } /** * 运营报表--体验金月报表--查询 * @param experienceDayReq * @return */ @Override public List listExperienceMonthByReq(DepositExperienceDayReq experienceDayReq) { // 首先根据前端传来的时间区间，查询出区间中所有的月份； DateTime startDate = DateUtil.parse(experienceDayReq.getStartDate(), "yyyy-MM"); DateTime endDate = DateUtil.parse(experienceDayReq.getEndDate(), "yyyy-MM"); List lDate = findDates(startDate, endDate, Calendar.MONTH); if (CollectionUtils.isEmpty(lDate)){ return null; } List experienceDayResList = baseMapper.listExperienceMonthByReq(experienceDayReq); for (Date date : lDate){ // 当前日期不存在则新增 String dateStr = DateUtil.format(date, "yyyy-MM"); // 进行匹配 if(!experienceDayResList.stream().filter(item -> dateStr.equals(item.getDays())).findAny().isPresent()){ DepositExperienceDayRes experienceDayRes = DepositExperienceDayRes.builder() .days(dateStr) .receiveQty(0L) .userQty(0L) .build(); experienceDayResList.add(experienceDayRes); } } List collect = experienceDayResList.stream().sorted(Comparator.comparing(DepositExperienceDayRes::getDays).reversed()).collect(Collectors.toList()); return collect; } /** * 获取指定时间区间的所有数据（包含日期和月份） * @param dBegin * @param dEnd * @param rule 日历规则 如：Calendar.DAY_OF_MONTH * @return */ public static List findDates(Date dBegin, Date dEnd, int rule) { List lDate = new ArrayList(); if (dEnd.before(dBegin)){ return lDate; } lDate.add(dBegin); Calendar calBegin = Calendar.getInstance(); // 使用给定的 Date 设置此 Calendar 的时间 calBegin.setTime(dBegin); Calendar calEnd = Calendar.getInstance(); // 使用给定的 Date 设置此 Calendar 的时间 calEnd.setTime(dEnd); // 测试此日期是否在指定日期之后 while (dEnd.after(calBegin.getTime())) { // 根据日历的规则，为给定的日历字段添加或减去指定的时间量 calBegin.add(rule, 1); lDate.add(calBegin.getTime()); } return lDate; }Mapper.xml SELECT t.days, sum(t.receiveQty) as receiveQty, sum(t.userQty) as userQty FROM ( SELECT DATE_FORMAT(r.created_time , '%Y-%m-%d' ) AS days, count(r.id) as receiveQty, 0 as userQty FROM dg_experience_receive r LEFT JOIN dg_experience_order o on r.user_id = o.user_id = #{experienceDayReq.startDate} ]]> GROUP BY days UNION ALL SELECT DATE_FORMAT(r.created_time , '%Y-%m-%d' ) AS days, 0 as receiveQty, count(r.id) as userQty FROM dg_experience_receive r JOIN dg_experience_order o on r.user_id = o.user_id AND r.is_used = 1 = #{experienceDayReq.startDate} ]]> GROUP BY days ) t GROUP BY days order by days desc SELECT t.days, sum(t.receiveQty) as receiveQty, sum(t.userQty) as userQty FROM ( SELECT DATE_FORMAT(r.created_time , '%Y-%m' ) AS days, count(r.id) as receiveQty, 0 as userQty FROM dg_experience_receive r LEFT JOIN dg_experience_order o on r.user_id = o.user_id = #{experienceDayReq.startDate} ]]> GROUP BY days UNION ALL SELECT DATE_FORMAT(r.created_time , '%Y-%m' ) AS days, 0 as receiveQty, count(r.id) as userQty FROM dg_experience_receive r JOIN dg_experience_order o on r.user_id = o.user_id AND r.is_used = 1 = #{experienceDayReq.startDate} ]]> GROUP BY days ) t GROUP BY days order by days desc PostMan结果展示：
下一篇将会记录使用：Elasticsearch(简称ES)实现日报表、月报表、年报表统计，没数据补0功能 https://blog.csdn.net/JRocks/article/details/113842092 |
之前是将该文章放在简书中，后面发现简书的页面编辑功能没有这里多，今天就把它移过来了。【本文地址】
公司简介
联系我们