苍穹外卖学习笔记(十一)

数据统计-图形报表

Apache ECharts

介绍

Apache ECarts 是一款基于 JavaScript 的数据可视化图标库，提供直观，生动，可交互，可个性化定制的数据可视化图表。

官网地址: https://echarts.apache.org/zh/index.html

20241020173638

入门案例

echarts.js

html 文件

营业额统计

ReportController

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
package com.sky.controller.admin;

import com.sky.result.Result;
import com.sky.service.ReportService;
import com.sky.vo.TurnoverReportVO;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.format.annotation.DateTimeFormat;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.time.LocalDate;

/**
 * @author Jie.
 * @description: TODO
 * @date 2024/10/20
 * @version: 1.0
 */

/**
 * 数据统计
 */
@RestController
@RequestMapping("/admin/report")
@Api(tags = "数据统计")
@Slf4j
public class ReportController {

    @Autowired
    private ReportService reportService;

    /**
     * 营业额统计接口
     */
    @GetMapping("/turnoverStatistics")
    @ApiOperation("营业额统计接口")
    public Result<TurnoverReportVO> turnoverStatistics(
            @DateTimeFormat(pattern = "yyyy-MM-dd") LocalDate begin,
            @DateTimeFormat(pattern = "yyyy-MM-dd") LocalDate end) {
        log.info("营业额数据统计：begin:{}, end:{}", begin, end);
        TurnoverReportVO turnoverStatistics = reportService.getTurnoverStatistics(begin, end);
        return Result.success(turnoverStatistics);
    }
}

ReportService

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
package com.sky.service;

import com.sky.vo.TurnoverReportVO;

import java.time.LocalDate;

/**
 * @author Jie.
 * @description: TODO
 * @date 2024/10/20
 * @version: 1.0
 */
public interface ReportService {

    /**
     * 营业额统计
     * @param begin 开始时间
     * @param end 结束时间
     * @return 营业额统计结果
     */
    TurnoverReportVO getTurnoverStatistics(LocalDate begin, LocalDate end);
}

ReportServiceImpl

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
package com.sky.service.impl;

import com.sky.entity.Orders;
import com.sky.mapper.OrderMapper;
import com.sky.service.OrderService;
import com.sky.service.ReportService;
import com.sky.vo.TurnoverReportVO;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.LocalTime;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author Jie.
 * @description: TODO
 * @date 2024/10/20
 * @version: 1.0
 */
@Service
@Slf4j
public class ReportServiceImpl implements ReportService {

    @Autowired
    private OrderMapper orderMapper;

    /**
     * 营业额统计
     * @param begin 开始时间
     * @param end 结束时间
     * @return 营业额统计结果
     */
    @Override
    public TurnoverReportVO getTurnoverStatistics(LocalDate begin, LocalDate end) {
        // 当前集合用于存放从begin到end的日期
        List<LocalDate> dateList = new ArrayList<>();
        dateList.add(begin);
        while (begin.isBefore(end)) {
            // 日期加1
            begin = begin.plusDays(1);
            dateList.add(begin);
        }
        String dateJoin = StringUtils.join(dateList, ",");

        // 当前集合用于存放每天的营业额
        List<Double> turnoverList = new ArrayList<>();
        for (LocalDate date : dateList) {
            log.info("date:{}", date);
            LocalDateTime beginTime = LocalDateTime.of(date, LocalTime.MIN);
            LocalDateTime endTime = LocalDateTime.of(date, LocalTime.MAX);
            //查询date日期的营业额数据，营业额是指状态为已完成的订单的总金额
            Map map = new HashMap();
            map.put("begin", beginTime);
            map.put("end", endTime);
            map.put("status", Orders.COMPLETED);
            Double turnover = orderMapper.sumByNao(map);
            turnover = turnover == null ? 0.0 : turnover;
            turnoverList.add(turnover);
        }
        String turnoverJoin = StringUtils.join(turnoverList, ",");

        return TurnoverReportVO.builder()
                .dateList(dateJoin)
                .turnoverList(turnoverJoin)
                .build();
    }
}

OrderMapper.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
package com.sky.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.sky.entity.Orders;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.time.LocalDateTime;
import java.util.List;
import java.util.Map;

/**
 * @author Jie.
 * @description: TODO
 * @date 2024/10/10
 * @version: 1.0
 */
@Mapper
public interface OrderMapper extends BaseMapper<Orders> {

    /**
     * 统计订单状态
     */
    @Select("select count(id) from orders where status = #{status}")
    Integer countStatus(Integer toBeConfirmed);

    /**
     * 根据状态和订单时间查询订单
     */
    @Select("select * from orders where status = #{status} and order_time < #{orderTime}")
    List<Orders> getByStatusAndOrderTimeLT(Integer status, LocalDateTime orderTime);

    /**
     * 营业额统计数据
     */
    Double sumByNao(Map map);
}

OrderMapper.xml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.sky.mapper.OrderMapper">
    <select id="sumByNao" resultType="Double">
        select sum(amount) from 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>
</mapper>

用户统计

ReportController

1
2
3
4
5
6
7
8
9
10
11
12
/**
 * 用户数据统计接口
 */
@GetMapping("/userStatistics")
@ApiOperation("用户数据统计接口")
public Result<UserReportVO> userStatistics(
        @DateTimeFormat(pattern = "yyyy-MM-dd") LocalDate begin,
        @DateTimeFormat(pattern = "yyyy-MM-dd") LocalDate end) {
    log.info("用户数据统计：begin:{}, end:{}", begin, end);
    UserReportVO userStatistics = reportService.getUserStatistics(begin, end);
    return Result.success(userStatistics);
}

ReportService

1
2
3
4
5
6
7
/**
 * 用户数据统计
 * @param begin 开始时间
 * @param end 结束时间
 * @return 用户数据统计结果
 */
UserReportVO getUserStatistics(LocalDate begin, LocalDate end);

ReportServiceImpl

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
/**
 * 用户数据统计
 * @param begin 开始时间
 * @param end 结束时间
 * @return 用户数据统计结果
 */
@Override
public UserReportVO getUserStatistics(LocalDate begin, LocalDate end) {
    // 当前集合用于存放从begin到end的日期
    List<LocalDate> dateList = new ArrayList<>();
    dateList.add(begin);
    while (begin.isBefore(end)) {{
        // 日期加1
        begin = begin.plusDays(1);
        dateList.add(begin);
    }}
    String dateJoin = StringUtils.join(dateList, ",");

    // 当前集合用于存放新增用户数
    List<Integer> newUserList = new ArrayList<>();
    // 当前集合用于存放总用户数
    List<Integer> totalUserList = new ArrayList<>();

    for (LocalDate date : dateList) {
        log.info("date:{}", date);
        LocalDateTime beginTime = LocalDateTime.of(date, LocalTime.MIN);
        LocalDateTime endTime = LocalDateTime.of(date, LocalTime.MAX);
        Map map = new HashMap();
        //查询date日期的总用户数
        map.put("end", endTime);
        Integer totalUser = userMapper.countByMap(map);
        totalUser = totalUser == null ? 0 : totalUser;
        totalUserList.add(totalUser);
        //查询date日期的新增用户数
        map.put("begin", beginTime);
        Integer newUser = userMapper.countByMap(map);
        newUser = newUser == null ? 0 : newUser;
        newUserList.add(newUser);
    }
    // 将集合转换为字符串
    String newUserJoin = StringUtils.join(newUserList, ",");
    String totalUserJoin = StringUtils.join(totalUserList, ",");

    return UserReportVO.builder()
            .dateList(dateJoin)
            .newUserList(newUserJoin)
            .totalUserList(totalUserJoin)
            .build();
}

userMapper

1
2
3
4
5
6
@Mapper
public interface UserMapper extends BaseMapper<User> {

    // 根据map动态条件查询数量
    Integer countByMap(Map map);
}

userMapper.xml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.sky.mapper.UserMapper">

    <select id="countByMap" resultType="java.lang.Integer">
        select count(id) from user
        <where>
            <if test="begin != null">
                and create_time > #{begin}
            </if>
            <if test="end != null">
                and create_time < #{end}
            </if>
        </where>
    </select>
</mapper>

订单统计

ReportController

1
2
3
4
5
6
7
8
9
10
11
12
/**
 * 订单数据统计接口
 */
@GetMapping("/ordersStatistics")
@ApiOperation("订单数据统计接口")
public Result<OrderReportVO> ordersStatistics(
        @DateTimeFormat(pattern = "yyyy-MM-dd") LocalDate begin,
        @DateTimeFormat(pattern = "yyyy-MM-dd") LocalDate end) {
    log.info("订单数据统计：begin:{}, end:{}", begin, end);
    OrderReportVO orderStatistics = reportService.getOrderStatistics(begin, end);
    return Result.success(orderStatistics);
}

ReportService

1
2
3
4
5
6
7
/**
     * 订单数据统计
     * @param begin 开始时间
     * @param end 结束时间
     * @return 订单数据统计结果
     */
    OrderReportVO getOrderStatistics(LocalDate begin, LocalDate end);

ReportServiceImpl

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
/**
    * 订单数据统计
    * @param begin 开始时间
    * @param end 结束时间
    * @return 订单数据统计结果
    */
   @Override
   public OrderReportVO getOrderStatistics(LocalDate begin, LocalDate end) {
       // 当前集合用于存放从begin到end的日期
       List<LocalDate> dateList = new ArrayList<>();
       dateList.add(begin);
       while (begin.isBefore(end)) {
           // 日期加1
           begin = begin.plusDays(1);
           dateList.add(begin);
       }
       String dateJoin = StringUtils.join(dateList, ",");

       // 当前集合用于存放每天的订单数
       List<Integer> orderCountList = new ArrayList<>();
       // 当前集合用于存放每天的有效订单数
       List<Integer> validOrderCountList = new ArrayList<>();

       // 遍历日期集合，查询每天的订单数和有效订单数
       for (LocalDate date : dateList) {
           log.info("date:{}", date);
           LocalDateTime beginTime = LocalDateTime.of(date, LocalTime.MIN);
           LocalDateTime endTime = LocalDateTime.of(date, LocalTime.MAX);
           // 查询date日期的订单数
           LambdaQueryWrapper<Orders> queryWrapper = new LambdaQueryWrapper<>();
           queryWrapper.between(Orders::getOrderTime, beginTime, endTime);
           Long orderCount = orderMapper.selectCount(queryWrapper);
           // 查询date日期的有效订单数
           queryWrapper.eq(Orders::getStatus, Orders.COMPLETED);
           Long validOrder = orderMapper.selectCount(queryWrapper);
           log.info("orderCount:{}, validOrder:{}", orderCount, validOrder);
           orderCountList.add(orderCount.intValue());
           validOrderCountList.add(validOrder.intValue());
       }
       // 将集合转换为字符串
       String orderCountJoin = StringUtils.join(orderCountList, ",");
       String validOrderCountJoin = StringUtils.join(validOrderCountList, ",");

       // 计算时间区间内的订单总金额
       Integer totalOrderCount = orderCountList.stream().reduce(Integer::sum).get();

       //计算时间区间内的有效订单总金额
       Integer totalValidOrderCount = validOrderCountList.stream().reduce(Integer::sum).get();

       // 计算订单完成率
       Double orderCompletionRate = 0.0;
       if (totalOrderCount != 0) {
           orderCompletionRate = totalValidOrderCount.doubleValue() / totalOrderCount.doubleValue();
       }

       return OrderReportVO.builder()
               .dateList(dateJoin)
               .orderCountList(orderCountJoin)
               .validOrderCountList(validOrderCountJoin)
               .totalOrderCount(totalOrderCount)
               .validOrderCount(totalValidOrderCount)
               .orderCompletionRate(orderCompletionRate)
               .build();
   }

销量排名 Top10

ReportController

1
2
3
4
5
6
7
8
9
10
11
12
/**
 * 销售额Top10
 */
@GetMapping("/top10")
@ApiOperation("销售额Top10")
public Result<SalesTop10ReportVO> top10(
        @DateTimeFormat(pattern = "yyyy-MM-dd") LocalDate begin,
        @DateTimeFormat(pattern = "yyyy-MM-dd") LocalDate end) {
    log.info("销售额Top10：begin:{}, end:{}", begin, end);
    SalesTop10ReportVO top10 = reportService.getSalesTop10(begin, end);
    return Result.success(top10);
}

ReportService

1
2
3
4
5
6
7
/**
 * 销售额Top10统计
 * @param begin 开始时间
 * @param end 结束时间
 * @return 销售额Top10统计结果
 */
SalesTop10ReportVO getSalesTop10(LocalDate begin, LocalDate end);

ReportServiceImpl

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
/**
 * 销售额Top10统计
 * @param begin 开始时间
 * @param end 结束时间
 * @return 销售额Top10统计结果
 */
@Override
public SalesTop10ReportVO getSalesTop10(LocalDate begin, LocalDate end) {
    LocalDateTime beginTime = LocalDateTime.of(begin, LocalTime.MIN);
    LocalDateTime endTime = LocalDateTime.of(end, LocalTime.MAX);

    // 当前集合用于存放商品名称
    List<String> nameList = new ArrayList<>();
    // 当前集合用于存放销量
    List<Integer> numberList = new ArrayList<>();

    List<GoodsSalesDTO> salesTop = orderMapper.getSalesTop(beginTime, endTime);
    for (GoodsSalesDTO goodsSalesDTO : salesTop) {
        log.info("goodsSalesDTO:{}", goodsSalesDTO);
        nameList.add(goodsSalesDTO.getName());
        numberList.add(goodsSalesDTO.getNumber());
    }
    // 将集合转换为字符串
    String nameJoin = StringUtils.join(nameList, ",");
    String numberJoin = StringUtils.join(numberList, ",");
    return SalesTop10ReportVO.builder()
            .nameList(nameJoin)
            .numberList(numberJoin)
            .build();
}

OrderMapper

1
2
3
4
/**
     * 获取指定时间内Top10
     */
    List<GoodsSalesDTO> getSalesTop(LocalDateTime begin, LocalDateTime end);

OrderMapper.xml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
<select id="getSalesTop" resultType="com.sky.dto.GoodsSalesDTO">
        select od.name, sum(od.number) number
        from order_detail od,orders o
        where od.order_id = o.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.name
        order by number desc
        limit 0,10
    </select>

结果

20241021111638