一、介绍
二、营业额统计
需求分析和设计：
Controller:
Service:
/*** 营业额统计* @param begindate* @param enddate* @return* */@Overridepublic TurnoverReportVO turnoverStatistics(LocalDate begindate, LocalDate enddate) {//创建时间集合List<LocalDate> datelist=new ArrayList<>();//加入起始时间datelist.add(begindate);//循环，直到将起始时间->结束时间期间的每一天都加入到集合中while(!begindate.equals(enddate)){begindate = begindate.plusDays(1);datelist.add(begindate);}//集合元素按照 a，b格式转化成字符串String dateList = StringUtils.join(datelist, ",");List<Double> turnoverList =new ArrayList<>();for (LocalDate localDate : datelist) {//根据日期查询营业额 （状态为已完成的订单的综合）LocalDateTime begin = LocalDateTime.of(localDate, LocalTime.MIN);//LocalTime.MIN 0点 0分 0秒LocalDateTime end = LocalDateTime.of(localDate, LocalTime.MAX);//LocalTime.MAX 59:59:5999Map map=new HashMap();map.put("begin",begin);map.put("end",end);map.put("status", Orders.COMPLETED);Double turnover =orderMapper.sumByMap(map);//当天没有营业额则置零turnover = turnover==null?0.0:turnover;turnoverList.add(turnover);}//集合元素按照 a，b格式转化成字符串String turnoverlist = StringUtils.join(turnoverList, ",");return TurnoverReportVO.builder().turnoverList(turnoverlist).dateList(dateList).build();}
Mapper:
<select id="sumByMap" resultType="java.lang.Double">select sum(amount) from sky_take_out.orders<where><if test=" begin != null">and order_time > #{begin}</if><if test=" end != null">and order_time < #{end}</if><if test="status != null">and status = #{status}</if></where></select>
三、用户统计
需求分析和设计：
Controller:
/*** 用户统计* @param begin* @param end* @return* */@ApiOperation("用户统计")@GetMapping("/userStatistics")public Result<UserReportVO> getUserStatistics(@DateTimeFormat(pattern = "yyyy-MM-dd") LocalDate begin,@DateTimeFormat(pattern = "yyyy-MM-dd") LocalDate end){log.info("用户统计:{},{}",begin,end);return Result.success(reportService.userStatistics(begin,end));}
Service:
/*** 用户统计* @param begin* @param end* @return* */@Overridepublic UserReportVO userStatistics(LocalDate begin, LocalDate end) {List<LocalDate> datelist=new ArrayList<>();datelist.add(begin);while(!begin.equals(end)){//日期计算，循环到enddate为止begin = begin.plusDays(1);datelist.add(begin);}//集合元素按照 a，b格式转化成字符串String dateList = StringUtils.join(datelist, ",");List<Double> newuserlist=new ArrayList<>();for (LocalDate localDate : datelist) {//根据日期查询新增用户 （状态为已完成的订单的综合）LocalDateTime begindate = LocalDateTime.of(localDate, LocalTime.MIN);//LocalTime.MIN 0点 0分 0秒LocalDateTime enddate = LocalDateTime.of(localDate, LocalTime.MAX);//LocalTime.MAX 59:59:5999Map map=new HashMap();map.put("begin",begindate);map.put("end",enddate);Double usercount= orderMapper.sumuserByMap(map);usercount= usercount==null?0:usercount;newuserlist.add(usercount);}String newuser = StringUtils.join(newuserlist, ",");List<Double> totalueserlist=new ArrayList<>();for (LocalDate localDate : datelist) {//根据日期查询总用户LocalDateTime enddate = LocalDateTime.of(localDate, LocalTime.MAX);//LocalTime.MAX 59:59:5999Map map=new HashMap();map.put("end",enddate);Double totalusercount= orderMapper.sumuserByMap(map);totalusercount= totalusercount==null?0:totalusercount;totalueserlist.add(totalusercount);}String totaluser = StringUtils.join(totalueserlist, ",");return UserReportVO.builder().newUserList(newuser).totalUserList(totaluser).dateList(dateList).build();}
Mapper:
<select id="sumuserByMap" resultType="java.lang.Double">select count(id) from sky_take_out.user<where><if test=" begin != null">and create_time > #{begin}</if><if test=" end != null">and create_time < #{end}</if></where></select>
四、订单统计
需求分析和设计：
Controller：
/*** 订单统计* @param begin* @param end* @return* */@ApiOperation("订单统计")@GetMapping("/ordersStatistics")public Result<OrderReportVO> ordersStatistics(@DateTimeFormat(pattern = "yyyy-MM-dd") LocalDate begin,@DateTimeFormat(pattern = "yyyy-MM-dd") LocalDate end){log.info("订单统计:{},{}",begin,end);return Result.success(reportService.ordersStatistics(begin,end));}
Service：
/*** 订单统计* @param begin* @param end* @return* */@Overridepublic OrderReportVO ordersStatistics(LocalDate begin, LocalDate end) {List<LocalDate> datelist=new ArrayList<>();datelist.add(begin);while(!begin.equals(end)){//日期计算，循环到enddate为止begin = begin.plusDays(1);datelist.add(begin);}//集合元素按照 a，b格式转化成字符串String dateList = StringUtils.join(datelist, ",");//每日订单数List<Double> orderslist =new ArrayList<>();for (LocalDate localDate : datelist) {LocalDateTime begindate = LocalDateTime.of(localDate, LocalTime.MIN);LocalDateTime enddate = LocalDateTime.of(localDate, LocalTime.MAX);Map map=new HashMap();map.put("begin",begindate);map.put("end",enddate);//每日订单数量Double ordercount = orderMapper.sumorderByMap(map);orderslist.add(ordercount);}//集合元素按照 a，b格式转化成字符串String orderCountList = StringUtils.join(orderslist, ",");//每日有效订单数List<Double> validorderslist =new ArrayList<>();for (LocalDate localDate : datelist) {LocalDateTime begindate = LocalDateTime.of(localDate, LocalTime.MIN);LocalDateTime enddate = LocalDateTime.of(localDate, LocalTime.MAX);Map map=new HashMap();map.put("begin",begindate);map.put("end",enddate);map.put("status",Orders.COMPLETED);//每日有效订单数量Double validordercount = orderMapper.sumorderByMap(map);validorderslist.add(validordercount);}//集合元素按照 a，b格式转化成字符串String validorderCountList = StringUtils.join(validorderslist, ",");//完成率Map map=new HashMap();map.put("end",LocalDateTime.now());Double total=orderMapper.sumorderByMap(map);int totalorder =total.intValue();Double valid=orderMapper.sumorderByMap(map);int validordercount=valid.intValue();Double orderCompletion=valid/total;return OrderReportVO.builder().orderCompletionRate(orderCompletion).orderCountList(orderCountList).validOrderCountList(validorderCountList).totalOrderCount(totalorder).validOrderCount(validordercount).dateList(dateList).build();}
Mapper：
<select id="sumorderByMap" resultType="java.lang.Double">select count(id) from sky_take_out.orders<where><if test=" begin != null">and order_time > #{begin}</if><if test=" end != null">and order_time < #{end}</if><if test=" status != null">and status = #{status}</if></where></select>
五、销量排名
需求分析和设计：
Controller:
Service:
/*** 查询销量top10* @param begin* @param end* @return* */@Overridepublic SalesTop10ReportVO getTop10(LocalDate begin, LocalDate end) {LocalDateTime begindate = LocalDateTime.of(begin, LocalTime.MIN);LocalDateTime enddate = LocalDateTime.of(end, LocalTime.MAX);//创建集合，将查询到的数据封装入对象存入集合List<GoodsSalesDTO> top10 = orderMapper.getTop10(begindate, enddate);//创建两个集合，将所需数据分别提取List<String> namelist=new ArrayList<>();List<Integer> numberList=new ArrayList<>();for (GoodsSalesDTO goodsSalesDTO : top10) {namelist.add(goodsSalesDTO.getName());numberList.add(goodsSalesDTO.getNumber());}String name = StringUtils.join(namelist, ",");String number = StringUtils.join(numberList, ",");return SalesTop10ReportVO.builder().nameList(name).numberList(number).build();}
Mapper：
<select id="getTop10" resultType="com.sky.dto.GoodsSalesDTO">select od.name name,sum(od.number) numberfrom sky_take_out.order_detail od , sky_take_out.orders owhere o.id=od.order_id and o.status=5<if test=" begin != null">and o.order_time > #{begin}</if><if test=" end != null">and o.order_time < #{end}</if>group by od.nameorder by number desclimit 0,10</select>
