统计

zxl

2025-10-16 79b026c753c25f998776d8a6210fc6afab97e647

统计

 framework/src/main/java/cn/lili/modules/lmk/mapper/LmkOrderSelectMapper.java

@@ -12,13 +12,13 @@
@Mapper
public interface LmkOrderSelectMapper extends BaseMapper<Order> {

    List<Map<String, Object>> selectOrderCountByDay(Date startTime, Date endTime);
    List<Map<String, Object>> selectOrderCountByDay(Date startTime, Date endTime,String storeId);

    List<Map<String, Object>>  selectOrderTimePeriod(Date startTime, Date endTime);
    List<Map<String, Object>>  selectOrderTimePeriod(Date startTime, Date endTime,String storeId);

    List<Map<String,Object>> selectViewDataCount(Date startTime, Date endTime);
    List<Map<String,Object>> selectViewDataCount(Date startTime, Date endTime,String storeId, List<String> goodsIds);

    List<Map<String,Object>> selectProductRepurchase(Date startTime, Date endTime,Integer currentLimit);
    List<Map<String,Object>> selectProductRepurchase(Date startTime, Date endTime,Integer currentLimit,String storeId);


    List<Map<String,Object>> selectPvUv(Date startTime, Date endTime,String storeId,List<String> goodsIds);
}

 framework/src/main/java/cn/lili/modules/member/mapper/FootprintMapper.java

@@ -39,7 +39,8 @@
    void deleteLastFootPrint(String memberId);


    @Select("SELECT " +
    @Select("<script>" +
            "SELECT " +
            "lfp.ref_id AS GoodsId, " +
            "lg.goods_name AS GoodsName, "+
            "COUNT(*) AS view_count " +
@@ -48,14 +49,19 @@
            "WHERE lfp.delete_flag = 0 " +
            "AND lfp.create_time BETWEEN #{startTime} AND #{endTime} " +
            "AND lfp.view_type = 'goods' " +
            "AND lg.goods_name is not null " +
            "GROUP BY lfp.ref_id " +  // 按商品ID分组
            "AND lg.goods_name IS NOT NULL " +
            "<if test='storeId != null and storeId != \"\"'>" +
            "AND lfp.store_id = #{storeId} " +
            "</if>" +
            "GROUP BY lfp.ref_id " +
            "ORDER BY view_count DESC " +
            "LIMIT #{currentLimit}"
            "LIMIT #{currentLimit}" +
            "</script>"
            )
    List<Map<String,Object>> selectViewAndCompletionRateCountByDay(Date startTime,
                                                                   Date endTime,
                                                                   Integer currentLimit);
                                                                   Integer currentLimit,
    String storeId);

    /**
     * 按视频维度统计：每个视频的总浏览数和完播率
@@ -63,8 +69,8 @@
     * @param endTime 结束时间
     * @return 包含视频ID、名称、总浏览数、完播率的列表
     */
    @Select({
            "SELECT",
    @Select("<script>" +
            "SELECT" +
            "  lfp.ref_id AS video_id, " +
            "  lmk.title, " +
            "  COUNT(*) AS total_views, " +
@@ -84,19 +90,24 @@
            "FROM li_foot_print lfp" +
            "  LEFT JOIN lmk_video lmk ON lfp.ref_id = lmk.id " +  // 按实际关联字段调整
            "WHERE " +
            "  lfp.delete_flag = 0",
            "  AND lfp.view_type = 'video' " +// 只统计视频类型
            "  lfp.delete_flag = 0" +
            "  AND lfp.view_type = 'video' " +
            "  AND lfp.create_time BETWEEN #{startTime} AND #{endTime} " +
            "  AND lmk.video_duration > 0 " +
            "  AND ref_id IS NOT NULL AND lmk.title IS NOT NULL "+
            "<if test='storeId != null and storeId != \"\"'>" +
            " AND lfp.store_id = #{storeId} " +
            "</if>" +
            "GROUP BY lfp.ref_id "+
            "ORDER BY total_views DESC " +
            "LIMIT #{currentLimit}"
    })
            "LIMIT #{currentLimit}"+
            "</script>"
    )
    List<Map<String, Object>> selectEachVideoStats(
             Date startTime,
            Date endTime,
            Integer currentLimit
            Integer currentLimit,
             String storeId
    );

}

 framework/src/main/java/cn/lili/modules/member/serviceimpl/FootprintServiceImpl.java

@@ -263,7 +263,7 @@
        }

        if ("goods".equals(queryParam.getCurrentType())){
            List<Map<String, Object>> maps = baseMapper.selectViewAndCompletionRateCountByDay(startTime, endTime,queryParam.getCurrentLimit());
            List<Map<String, Object>> maps = baseMapper.selectViewAndCompletionRateCountByDay(startTime, endTime,queryParam.getCurrentLimit(),queryParam.getStoreId());
            Map<String,Long> viewPrintMap = maps.stream()
                    .collect(Collectors.toMap(
                            map -> {
@@ -287,7 +287,7 @@
            data.put("xData", xData);
            return Result.ok().data(data);
        }else{
            List<Map<String, Object>> maps = baseMapper.selectEachVideoStats(startTime, endTime,queryParam.getCurrentLimit());
            List<Map<String, Object>> maps = baseMapper.selectEachVideoStats(startTime, endTime,queryParam.getCurrentLimit(),queryParam.getStoreId());
            Map<String, VideoViewCompletionRateVO> viewPrintMap = maps.stream()
                    .collect(Collectors.toMap(
                            map -> {

 framework/src/main/java/cn/lili/modules/order/order/service/OrderService.java

@@ -363,5 +363,7 @@

    Result getOrderTimePeriod(StatisticsQueryParam queryParam);

    Result getPvUv(StatisticsQueryParam queryParam);

    Result gerProductRepurchase(StatisticsQueryParam queryParam);
}

 framework/src/main/java/cn/lili/modules/order/order/serviceimpl/OrderServiceImpl.java

@@ -17,27 +17,23 @@
import cn.lili.common.event.TransactionCommitSendMQEvent;
import cn.lili.common.exception.ServiceException;
import cn.lili.common.properties.RocketmqCustomProperties;
import cn.lili.common.security.AuthUser;
import cn.lili.common.security.OperationalJudgment;
import cn.lili.common.security.context.UserContext;
import cn.lili.common.security.enums.UserEnums;
import cn.lili.common.utils.CurrencyUtil;
import cn.lili.common.utils.SnowFlake;
import cn.lili.common.utils.StringUtils;
import cn.lili.common.vo.ResultMessage;
import cn.lili.modules.goods.entity.dos.Goods;
import cn.lili.modules.goods.entity.dos.GoodsSku;
import cn.lili.modules.goods.entity.dto.GoodsCompleteMessage;
import cn.lili.modules.goods.service.GoodsService;
import cn.lili.modules.goods.service.GoodsSkuService;
import cn.lili.modules.lmk.domain.entity.CouponVirtual;
import cn.lili.modules.lmk.domain.vo.OrderCountByDayAndHourVO;
import cn.lili.modules.lmk.domain.vo.OrderCountVO;
import cn.lili.modules.lmk.enums.general.AdminRoleEnum;
import cn.lili.modules.lmk.enums.general.VirtualGoodsTypeEnum;
import cn.lili.modules.lmk.mapper.LmkOrderSelectMapper;
import cn.lili.modules.lmk.service.CouponVirtualService;
import cn.lili.modules.member.entity.dos.Member;
import cn.lili.modules.member.entity.dto.MemberAddressDTO;
import cn.lili.modules.member.mapper.MemberMapper;
import cn.lili.modules.order.cart.entity.dto.TradeDTO;
@@ -52,9 +48,7 @@
import cn.lili.modules.order.trade.entity.dos.OrderLog;
import cn.lili.modules.order.trade.service.OrderLogService;
import cn.lili.modules.payment.entity.enums.PaymentMethodEnum;
import cn.lili.modules.permission.entity.dos.AdminUser;
import cn.lili.modules.permission.service.AdminUserService;
import cn.lili.modules.permission.service.RoleService;
import cn.lili.modules.promotion.entity.dos.Coupon;
import cn.lili.modules.promotion.entity.dos.Pintuan;
import cn.lili.modules.promotion.service.CouponService;
@@ -92,7 +86,6 @@
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.baomidou.mybatisplus.extension.conditions.query.LambdaQueryChainWrapper;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import io.reactivex.rxjava3.core.Maybe;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
@@ -117,13 +110,10 @@
import java.math.RoundingMode;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.LocalTime;
import java.util.*;
import java.util.concurrent.TimeUnit;
import java.util.stream.Collectors;
import cn.lili.modules.permission.entity.dos.Role;

/**
 * 子订单业务层实现
 *
@@ -1409,7 +1399,7 @@
            }
        }

        List<Map<String, Object>> maps = lmkOrderSelectMapper.selectOrderCountByDay(startTime,endTime);
        List<Map<String, Object>> maps = lmkOrderSelectMapper.selectOrderCountByDay(startTime,endTime,queryParam.getStoreId());

        Map<String, Long> dayCountMap = maps.stream()
                .collect(Collectors.toMap(
@@ -1504,32 +1494,184 @@



        List<Map<String, Object>> maps = lmkOrderSelectMapper.selectOrderTimePeriod(startTime,endTime);
        List<Map<String,Object>> countMap = lmkOrderSelectMapper.selectViewDataCount(startTime,endTime);
        List<Map<String, Object>> maps = lmkOrderSelectMapper.selectOrderTimePeriod(startTime,endTime,queryParam.getStoreId());
        List<String> goodsIds = new ArrayList<>();
        //如果查询条件带了店铺 查询店铺的商品
        if (StringUtils.isNotBlank(queryParam.getStoreId())){
            List<Goods> list = new LambdaQueryChainWrapper<>(goodsService.getBaseMapper())
                    .eq(Goods::getDeleteFlag, Boolean.FALSE)
                    .eq(Goods::getStoreId, queryParam.getStoreId())
                    .list();
            goodsIds = list.stream().map(Goods::getId).collect(Collectors.toList());
        }


        List<Map<String,Object>> countMap = lmkOrderSelectMapper.selectViewDataCount(startTime,endTime,queryParam.getStoreId(),goodsIds);


        Map<String, Long> countByDayHour = maps.stream()
                .collect(Collectors.toMap(
                        map -> map.get("hour").toString(),
                        map -> Long.valueOf(map.get("count").toString()),
                        Long::sum  // 如果有重复，累加count
                ));
        System.out.println("打印记录---------------------------------------");
        System.out.println(countMap);
        System.out.println("---------------------------------------");
        Map<String, Long> viewDataCountByDayHour = countMap.stream()
                        .collect(Collectors.toMap(
                                map -> map.get("hour").toString(),
                                map -> Long.valueOf(map.get("count").toString()),
                                Long::sum
                        ));
        // 遍历查看结果
        List<Long> countList = new ArrayList<>();
        List<Long> viewData = new ArrayList<>();
        for (int i = 0; i < 24; i++) {
            String hourKey = String.valueOf(i);
            Long viewDataCount = viewDataCountByDayHour.getOrDefault(hourKey,0L);
            Long count = countByDayHour.getOrDefault(hourKey, 0L);
            countList.add(count);
            viewData.add(viewDataCount);
        }

        Map<String,Object> map = new HashMap<>();
        map.put("dateData",dateList);
        map.put("countData",countList);
        //TODO 浏览数据未加入
//        map.put();
        map.put("viewData",viewData);

        return Result.ok().data(map);
    }
    @Override
    public Result getPvUv(StatisticsQueryParam queryParam) {
        Date startTime = null;
        Date endTime = new Date(); // 结束时间默认是当前时间
        List<String> dateList = new ArrayList<>();
        Calendar calendar = Calendar.getInstance(); // 用于日期计算的日历实例
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        int days = 0;
        if (queryParam.getYear() != null && queryParam.getMonth() != null) {
            Date[] dates =CommonUtil.getMonthStartAndEnd(queryParam.getYear(),queryParam.getMonth());
            startTime = dates[0];
            endTime = dates[1];
            Calendar tempCalendar = Calendar.getInstance();
            tempCalendar.setTime(startTime);
            days = tempCalendar.getActualMaximum(Calendar.DAY_OF_MONTH);
            for (int i = 0; i < days; i++) {
                dateList.add(sdf.format(tempCalendar.getTime()));
                tempCalendar.add(Calendar.DATE, 1); // 每天累加1天
            }
        }else{
            switch (queryParam.getSearchType()) {
                case "TODAY":
                    // 今天：从今天0点到现在
                    calendar.setTime(new Date()); // 重置为当前时间
                    calendar.set(Calendar.HOUR_OF_DAY, 0); // 小时设为0（24小时制）
                    calendar.set(Calendar.MINUTE, 0);      // 分钟设为0
                    calendar.set(Calendar.SECOND, 0);      // 秒设为0
                    calendar.set(Calendar.MILLISECOND, 0); // 毫秒设为0
                    startTime = calendar.getTime();        // 得到今天0点的Date对象
                    dateList.add(sdf.format(startTime));
                    break;
                case "YESTERDAY":
                    // 昨天：从昨天0点到昨天23:59:59.999
                    calendar.setTime(new Date());
                    calendar.add(Calendar.DATE, -1); // 日期减1天（变为昨天）

                    // 设置昨天0点
                    calendar.set(Calendar.HOUR_OF_DAY, 0);
                    calendar.set(Calendar.MINUTE, 0);
                    calendar.set(Calendar.SECOND, 0);
                    calendar.set(Calendar.MILLISECOND, 0);
                    startTime = calendar.getTime();

                    // 设置昨天23:59:59.999
                    calendar.set(Calendar.HOUR_OF_DAY, 23);
                    calendar.set(Calendar.MINUTE, 59);
                    calendar.set(Calendar.SECOND, 59);
                    calendar.set(Calendar.MILLISECOND, 999);
                    endTime = calendar.getTime();
                    dateList.add(sdf.format(startTime));
                    break;
                case "LAST_SEVEN":
                    // 过去七天：从7天前0点到现在（含今天共7天）
                    calendar.setTime(new Date());
                    calendar.add(Calendar.DATE, -6); // 日期减6天（7天前的今天）

                    // 设置7天前0点
                    calendar.set(Calendar.HOUR_OF_DAY, 0);
                    calendar.set(Calendar.MINUTE, 0);
                    calendar.set(Calendar.SECOND, 0);
                    calendar.set(Calendar.MILLISECOND, 0);
                    startTime = calendar.getTime();
                    days = 7;
                    // 循环生成7天的日期字符串
                    Calendar tempCalendar = Calendar.getInstance();
                    tempCalendar.setTime(startTime);
                    for (int i = 0; i < days; i++) {
                        dateList.add(sdf.format(tempCalendar.getTime()));
                        tempCalendar.add(Calendar.DATE, 1); // 每天累加1天
                    }
                    break;
                case "LAST_THIRTY":
                    // 过去30天：从30天前0点到现在（含今天共30天）
                    calendar.setTime(new Date());
                    calendar.add(Calendar.DATE, -29); // 日期减29天（30天前的今天）

                    // 设置30天前0点
                    calendar.set(Calendar.HOUR_OF_DAY, 0);
                    calendar.set(Calendar.MINUTE, 0);
                    calendar.set(Calendar.SECOND, 0);
                    calendar.set(Calendar.MILLISECOND, 0);
                    startTime = calendar.getTime();
                    days = 30;
                    // 循环生成30天的日期字符串
                    Calendar tempCalendar30 = Calendar.getInstance();
                    tempCalendar30.setTime(startTime);
                    for (int i = 0; i < days; i++) {
                        dateList.add(sdf.format(tempCalendar30.getTime()));
                        tempCalendar30.add(Calendar.DATE, 1); // 每天累加1天
                    }
                    break;
                default:
                    return Result.error("不支持的时间范围类型");
            }
        }
        List<String> goodsIds = new ArrayList<>();
        //如果查询条件带了店铺 查询店铺的商品
        if (StringUtils.isNotBlank(queryParam.getStoreId())){
            List<Goods> list = new LambdaQueryChainWrapper<>(goodsService.getBaseMapper())
                    .eq(Goods::getDeleteFlag, Boolean.FALSE)
                    .eq(Goods::getStoreId, queryParam.getStoreId())
                    .list();
            goodsIds = list.stream().map(Goods::getId).collect(Collectors.toList());
        }
        List<Map<String,Object>> maps = lmkOrderSelectMapper.selectPvUv(startTime,endTime,queryParam.getStoreId(),goodsIds);
        Map<String, Long> pvDataMap = maps.stream()
                .collect(Collectors.toMap(
                        map -> map.get("day").toString(),
                        map -> Long.valueOf(map.get("pvData").toString()),
                        Long::sum  // 如果有重复，累加count
                ));
        Map<String, Long> uvDataMap = maps.stream()
                .collect(Collectors.toMap(
                        map -> map.get("day").toString(),
                        map -> Long.valueOf(map.get("uvData").toString()),
                        Long::sum  // 如果有重复，累加count
                ));
        List<Long> pvData = new ArrayList<>();
        List<Long> uvData = new ArrayList<>();
        for (String date: dateList){
            String dayKey = String.valueOf(date);
            Long pv = pvDataMap.getOrDefault(dayKey,0L);
            Long uv = uvDataMap.getOrDefault(dayKey, 0L);
            pvData.add(pv);
            uvData.add(uv);
        }

        Map<String,Object> map = new HashMap<>();
        map.put("dateData",dateList);
        map.put("pvData",pvData);
        map.put("uvData",uvData);
        return Result.ok().data(map);

    }

    @Override
@@ -1603,7 +1745,7 @@
                    return Result.error("不支持的时间范围类型");
            }
        }
        List<Map<String, Object>> maps = lmkOrderSelectMapper.selectProductRepurchase(startTime,endTime,queryParam.getCurrentLimit());
        List<Map<String, Object>> maps = lmkOrderSelectMapper.selectProductRepurchase(startTime,endTime,queryParam.getCurrentLimit(),queryParam.getStoreId());
        List<String> goodsData = new ArrayList<>();
        List<BigDecimal> rateData = new ArrayList<>();


 framework/src/main/resources/mapper/lmk/orderMapper.xml

@@ -9,6 +9,7 @@
    <resultMap id="orderTimePeriod" type="java.util.Map">
        <result column="day" property="day" />
        <result column="hour" property="hour"/>
        <result column="count" property="count"/>
    </resultMap>

    <resultMap id="productRepurchase" type="java.util.Map">
@@ -21,7 +22,11 @@
        <result column="hour" property="hour"/>
        <result column="count" property="count"/>
    </resultMap>

    <resultMap id="pvUvCount" type="java.util.Map">
        <result column="day" property="day"/>
        <result column="pvData" property="pvData"/>
        <result column="uvData" property="uvData"/>
    </resultMap>
    <select id="selectOrderCountByDay"  resultMap="OrderCountByDayMap">
        SELECT
            DATE(create_time) AS day,
@@ -30,6 +35,9 @@
            li_order o
            WHERE
            o.create_time BETWEEN #{startTime} AND #{endTime}
            <if test="storeId != null and storeId != ''">
                AND o.store_id = #{storeId}
            </if>
            GROUP BY
            day
            ORDER BY
@@ -45,12 +53,15 @@
            li_order o
            WHERE
            o.create_time BETWEEN #{startTime} AND #{endTime}
            <if test="storeId != null and storeId != ''">
                AND o.store_id = #{storeId}
            </if>
            GROUP BY
            day
            ORDER BY
            day , hour ASC
    </select>
    <select id="selectViewDataCount" resultMap="">
    <select id="selectViewDataCount" resultMap="viewDataCount">
        SELECT
            DATE(LAR.create_time) AS day,
            HOUR(LAR.create_time) AS hour,
@@ -58,6 +69,14 @@
        FROM lmk_action_record LAR
        where LAR.delete_flag = 0
          AND LAR.page_status = 'JOIN'
            AND LAR.create_time BETWEEN #{startTime} AND #{endTime}
            <if test="storeId != null and storeId !=''">
                AND page_code = 'GOODS_DETAILS'
                AND JSON_EXTRACT(LAR.page_params, '$.goodsId') IN
                <foreach collection="goodsIds" item="id" open="(" separator="," close=")">
                    #{id}
                </foreach>
            </if>
        GROUP BY day, hour
        ORDER BY day, hour;
    </select>
@@ -94,6 +113,9 @@
                            AND o.member_id IS NOT NULL
                            AND o.create_time >= #{startTime}
                            AND o.create_time &lt;= #{endTime}
                            <if test="storeId != null and storeId != ''">
                                AND o.store_id = #{storeId}
                            </if>
                          GROUP BY i.goods_id, i.goods_name, o.member_id
                      ) AS user_buy_counts
                 GROUP BY goods_id, goods_name
@@ -102,4 +124,27 @@
        LIMIT #{currentLimit};
    </select>

    <select id="selectPvUv" resultMap="pvUvCount">
        SELECT
            DATE(LAR.create_time) AS day,
            COUNT(*) AS pvData,
            COUNT(DISTINCT LAR.user_id) AS uvData
        FROM
            lmk_action_record LAR
        WHERE
            LAR.delete_flag = 0
            AND LAR.page_status = 'JOIN'
            AND LAR.create_time BETWEEN #{startTime} AND #{endTime}
        <if test="storeId != null and storeId !=''">
            AND page_code = 'GOODS_DETAILS'
            AND JSON_EXTRACT(LAR.page_params, '$.goodsId') IN
            <foreach collection="goodsIds" item="id" open="(" separator="," close=")">
                #{id}
            </foreach>
        </if>
        GROUP BY
            DATE(LAR.create_time);
    </select>


</mapper>

 manager-api/src/main/java/cn/lili/controller/lmk/StatisticsController.java

@@ -39,44 +39,8 @@
    @ApiOperation(value = "获取pv、uv流量数据 表单获取")
    @GetMapping("/pvUv")
    public Result getPVUVData(StatisticsQueryParam queryParam) {
        //TODO 获得 pv/uv数据
        PvUvVO pvUvVO = new PvUvVO();
        List<Long> pvData = new ArrayList<>();
        List<Long> uvData = new ArrayList<>();
        switch (queryParam.getSearchType()) {
            case "TODAY":
                // 今天的数据：添加1
                pvData.add(1L);
                uvData.add(9L);
                break;

            case "YESTERDAY":
                // 明天的数据：添加1
                pvData.add(1L);
                uvData.add(10L);
                break;

            case "LAST_SEVEN":
                // 过去七天的数据：添加1, 2, 3
                for(int i =1;i<=7;i++){
                    pvData.add((long) i);
                    uvData.add((long) i+7);
                }

                break;

            case "LAST_THIRTY":
                // 过去一个月的数据：可以根据需要自定义，这里示例添加1到5
                for(int i =1;i<=30;i++){
                    pvData.add((long) i);
                    uvData.add((long) i+7);
                }
                break;

        }
        pvUvVO.setPvData(pvData);
        pvUvVO.setUvData(uvData);
        return Result.ok().data(pvUvVO);
        return orderService.getPvUv(queryParam);
    }

    @GetMapping("/orderCount")