数据统计模块是管理端的核心功能之一主要包含五个接口营业额统计、用户统计、订单统计、销量排名TOP10和导出Excel报表。这些接口为管理端提供数据可视化支持和报表导出功能。Controller层实现数据统计接口均采用GET请求方式接收begin和end两个日期参数格式为yyyy-MM-dd。接口返回统一的Result封装包含业务数据和状态信息。javaSlf4j RestController RequestMapping(/admin/report) public class ReportController { Autowired private ReportService reportService; GetMapping(/turnoverStatistics) ResultReportAmountVO selectAmount( RequestParam DateTimeFormat(pattern yyyy-MM-dd) LocalDate begin, RequestParam DateTimeFormat(pattern yyyy-MM-dd) LocalDate end ) { return Result.success(reportService.selectAmount(begin, end)); } GetMapping(/userStatistics) ResultReportUserVO selectUserCount( RequestParam DateTimeFormat(pattern yyyy-MM-dd) LocalDate begin, RequestParam DateTimeFormat(pattern yyyy-MM-dd) LocalDate end ) { return Result.success(reportService.selectUserCount(begin, end)); } GetMapping(/top10) ResultReportTop10VO selectSale( RequestParam DateTimeFormat(pattern yyyy-MM-dd) LocalDate begin, RequestParam DateTimeFormat(pattern yyyy-MM-dd) LocalDate end ) { return Result.success(reportService.selectSaleCount(begin, end)); } GetMapping(/ordersStatistics) ResultReportOrderVO selectOrder( RequestParam DateTimeFormat(pattern yyyy-MM-dd) LocalDate begin, RequestParam DateTimeFormat(pattern yyyy-MM-dd) LocalDate end ) { return Result.success(reportService.selectOrder(begin, end)); } GetMapping(/export) public void exportExcel(HttpServletResponse response) throws Exception { reportService.exportExcel(response); } }Service层实现Service层负责业务逻辑处理和数据组装调用Mapper层获取数据并进行处理。营业额统计的Service实现中首先遍历日期范围内的每一天将LocalDate转换为LocalDateTime设置当天的起始和结束时间。然后调用Mapper层查询当天已完成订单的金额总和。使用StringBuilder拼接日期和金额列表最后移除末尾的逗号封装到ReportAmountVO中返回。javaOverride public ReportAmountVO selectAmount(LocalDate begin, LocalDate end) { ReportAmountVO vo new ReportAmountVO(); StringBuilder dateList new StringBuilder(); StringBuilder amountList new StringBuilder(); for (LocalDate date begin; !date.isAfter(end); date date.plusDays(1)) { LocalDateTime beginTime LocalDateTime.of(date, LocalTime.of(0, 0, 0)); LocalDateTime endTime LocalDateTime.of(date, LocalTime.of(23, 59, 59)); Double amount reportMapper.selectAmount(beginTime, endTime); amount amount null ? 0.0 : amount; amountList.append(amount).append(,); dateList.append(date).append(,); } vo.setTurnoverList(amountList.substring(0, amountList.length() - 1)); vo.setDateList(dateList.substring(0, dateList.length() - 1)); return vo; }用户统计接口需要统计每天的新增用户数和累计用户数。新增用户通过create_time判断累计用户则统计截止到当天的所有用户数量。两个数据列表分别拼接后封装到ReportUserVO中返回。javaOverride public ReportUserVO selectUserCount(LocalDate begin, LocalDate end) { StringBuilder dateList new StringBuilder(); StringBuilder newUserList new StringBuilder(); StringBuilder totalUserList new StringBuilder(); for (LocalDate date begin; !date.isAfter(end); date date.plusDays(1)) { LocalDateTime beginTime LocalDateTime.of(date, LocalTime.of(0, 0, 0)); LocalDateTime endTime LocalDateTime.of(date, LocalTime.of(23, 59, 59)); Integer newUser reportMapper.selectNewCustomerCount(beginTime, endTime); Integer totalUser reportMapper.selectCustormerCount(beginTime, endTime); newUser newUser null ? 0 : newUser; dateList.append(date).append(,); newUserList.append(newUser).append(,); totalUserList.append(totalUser).append(,); } ReportUserVO vo new ReportUserVO(); vo.setDateList(dateList.substring(0, dateList.length() - 1)); vo.setNewUserList(newUserList.substring(0, newUserList.length() - 1)); vo.setTotalUserList(totalUserList.substring(0, totalUserList.length() - 1)); return vo; }订单统计接口查询每天的订单总数和有效订单数。同时还会调用selectOrderCount方法获取总体订单完成率将这些数据整合后返回给前端。javaOverride public ReportOrderVO selectOrder(LocalDate begin, LocalDate end) { StringBuilder dateList new StringBuilder(); StringBuilder orderCountList new StringBuilder(); StringBuilder validOrderList new StringBuilder(); for (LocalDate date begin; !date.isAfter(end); date date.plusDays(1)) { LocalDateTime beginTime LocalDateTime.of(date, LocalTime.of(0, 0, 0)); LocalDateTime endTime LocalDateTime.of(date, LocalTime.of(23, 59, 59)); String orderCount reportMapper.selectOrderList(beginTime, endTime); String validCount reportMapper.selectRealOrderList(beginTime, endTime); dateList.append(date).append(,); orderCountList.append(orderCount).append(,); validOrderList.append(validCount).append(,); } ReportOrderVO vo reportMapper.selectOrderCount(); vo.setDateList(dateList.substring(0, dateList.length() - 1)); vo.setOrderCountList(orderCountList.substring(0, orderCountList.length() - 1)); vo.setValidOrderCountList(validOrderList.substring(0, validOrderList.length() - 1)); return vo; }销量排名TOP10接口通过关联order_detail、dish和orders三张表按商品名称分组并按销量降序排列取前10条数据。商品名称和销量分别查询后使用String.join方法拼接成字符串。javaOverride public ReportTop10VO selectSaleCount(LocalDate begin, LocalDate end) { ListString saleTop reportMapper.selectTopName( LocalDateTime.of(begin, LocalTime.of(0, 0, 0)), LocalDateTime.of(end, LocalTime.of(23, 59, 59)) ); ListString saleCount reportMapper.selectSaleCount( LocalDateTime.of(begin, LocalTime.of(0, 0, 0)), LocalDateTime.of(end, LocalTime.of(23, 59, 59)) ); ReportTop10VO vo new ReportTop10VO(); vo.setNameList(String.join(,, saleTop)); vo.setNumberList(String.join(,, saleCount)); return vo; }导出Excel报表接口是对前四个接口的综合应用。首先获取最近30天的统计数据然后使用Apache POI创建Excel工作簿。创建表头样式和数据样式后分别生成营业额、用户、订单和销量TOP10四个工作表最后设置响应头将文件输出到浏览器。javaOverride public void exportExcel(HttpServletResponse response) throws Exception { LocalDate today LocalDate.now(); LocalDate begin today.minusDays(30); ReportAmountVO amountVO selectAmount(begin, today); ReportUserVO userVO selectUserCount(begin, today); ReportOrderVO orderVO selectOrder(begin, today); ReportTop10VO top10VO selectSaleCount(begin, today); Workbook workbook new XSSFWorkbook(); CellStyle headerStyle createHeaderStyle(workbook); CellStyle dataStyle createDataStyle(workbook); createTurnoverSheet(workbook, amountVO, headerStyle, dataStyle); createUserSheet(workbook, userVO, headerStyle, dataStyle); createOrderSheet(workbook, orderVO, headerStyle, dataStyle); createTop10Sheet(workbook, top10VO, headerStyle, dataStyle); response.setContentType(application/vnd.openxmlformats-officedocument.spreadsheetml.sheet); response.setHeader(Content-Disposition, attachment; filename运营数据报表.xlsx); workbook.write(response.getOutputStream()); workbook.close(); }Mapper层实现Mapper层负责执行SQL查询使用MyBatis的动态SQL实现条件查询。营业额统计的SQL查询中使用sum函数汇总已完成订单的金额通过status5过滤已完成订单使用checkout_time字段进行时间范围查询。xmlselect idselectAmount resultTypejava.lang.Double select sum(amount) from orders where status 5 if testbeginTime!nulland checkout_time #{beginTime}/if if testendTime!nulland checkout_time #{endTime}/if /select用户统计的Mapper实现包含两个查询一个查询新增用户数通过create_time字段判断用户创建时间另一个查询累计用户数统计截止到指定时间的所有用户数量。xmlselect idselectNewCustomerCount resultTypejava.lang.Integer select count(*) from user where if testbeginTime!nulland create_time #{beginTime}/if if testendTime!nulland create_time #{endTime}/if /select select idselectCustormerCount resultTypejava.lang.Integer select count(*) from user where if testendTime!nulland create_time #{endTime}/if /select订单统计的Mapper实现包含三个查询第一个查询使用case when语句计算有效订单数、总订单数和订单完成率第二个查询指定时间范围内的订单总数第三个查询指定时间范围内的有效订单数。xmlselect idselectOrderCount resultTypecom.sky.vo.ReportOrderVO select sum(case when status 5 then 1 else 0 end) as validOrderCount, count(*) as totalOrderCount, sum(case when status 5 then 1 else 0 end) * 1.0 / count(*) as orderCompletionRate from orders /select select idselectOrderList resultTypejava.lang.String select count(*) from orders where if testbeginTime!nulland checkout_time #{beginTime}/if if testendTime!nulland checkout_time #{endTime}/if /select select idselectRealOrderList resultTypejava.lang.String select count(*) from orders where status 5 if testbeginTime!nulland checkout_time #{beginTime}/if if testendTime!nulland checkout_time #{endTime}/if /select销量TOP10的Mapper实现需要关联三张表通过order_detail表关联dish表获取商品信息关联orders表过滤已完成订单。使用group by按商品名称分组使用sum函数计算总销量按销量降序排列使用limit限制返回10条数据。xmlselect idselectTopName resultTypejava.lang.String select od.name from order_detail od join dish d on od.dish_idd.id join orders o on od.order_ido.id where o.status5 if testbeginTime!nulland o.checkout_time #{beginTime}/if if testendTime!nulland o.checkout_time #{endTime}/if group by od.name order by sum(od.number) desc limit 10 /select select idselectSaleCount resultTypejava.lang.String select sum(od.number) from order_detail od join orders o on od.order_id o.id join dish d on od.dish_id d.id where o.status5 if testbeginTime!nulland o.checkout_time #{beginTime}/if if testendTime!nulland o.checkout_time #{endTime}/if group by od.name order by sum(od.number) desc /select实现要点分析数据统计模块采用分层架构设计Controller层负责参数校验和格式转换Service层处理业务逻辑和数据组装Mapper层执行SQL查询。这种分层设计使得各层职责清晰便于维护和扩展。
