From 7aa3389ed873054a07a963ac010190d12e7ce89b Mon Sep 17 00:00:00 2001
From: zxl <763096477@qq.com>
Date: 星期三, 15 十月 2025 14:25:25 +0800
Subject: [PATCH] 统计
---
framework/src/main/resources/mapper/lmk/orderMapper.xml | 105 +++++++++++++++++++++
framework/src/main/java/cn/lili/modules/lmk/mapper/LmkOrderSelectMapper.java | 24 ++++
framework/src/main/java/cn/lili/modules/order/order/service/OrderService.java | 2
framework/src/main/java/cn/lili/modules/order/order/serviceimpl/OrderServiceImpl.java | 114 ++++++++++++++++++++++
manager-api/src/main/java/cn/lili/controller/lmk/StatisticsController.java | 10 ++
framework/src/main/java/cn/lili/modules/order/order/mapper/OrderMapper.java | 28 -----
6 files changed, 251 insertions(+), 32 deletions(-)
diff --git a/framework/src/main/java/cn/lili/modules/lmk/mapper/LmkOrderSelectMapper.java b/framework/src/main/java/cn/lili/modules/lmk/mapper/LmkOrderSelectMapper.java
new file mode 100644
index 0000000..4ea2434
--- /dev/null
+++ b/framework/src/main/java/cn/lili/modules/lmk/mapper/LmkOrderSelectMapper.java
@@ -0,0 +1,24 @@
+package cn.lili.modules.lmk.mapper;
+
+import cn.lili.modules.order.order.entity.dos.Order;
+import com.baomidou.mybatisplus.core.mapper.BaseMapper;
+import org.apache.ibatis.annotations.Mapper;
+import org.apache.ibatis.annotations.Select;
+
+import java.util.Date;
+import java.util.List;
+import java.util.Map;
+
+@Mapper
+public interface LmkOrderSelectMapper extends BaseMapper<Order> {
+
+ List<Map<String, Object>> selectOrderCountByDay(Date startTime, Date endTime);
+
+ List<Map<String, Object>> selectOrderTimePeriod(Date startTime, Date endTime);
+
+ List<Map<String,Object>> selectViewDataCount(Date startTime, Date endTime);
+
+ List<Map<String,Object>> selectProductRepurchase(Date startTime, Date endTime,Integer currentLimit);
+
+
+}
diff --git a/framework/src/main/java/cn/lili/modules/order/order/mapper/OrderMapper.java b/framework/src/main/java/cn/lili/modules/order/order/mapper/OrderMapper.java
index 238bbc7..4a4c11c 100644
--- a/framework/src/main/java/cn/lili/modules/order/order/mapper/OrderMapper.java
+++ b/framework/src/main/java/cn/lili/modules/order/order/mapper/OrderMapper.java
@@ -193,32 +193,4 @@
@Select("select o.* " +
" FROM li_order o INNER JOIN li_order_item AS oi on o.sn = oi.order_sn ${ew.customSqlSegment} ")
List<Order> queryListByParams(@Param(Constants.WRAPPER) Wrapper<Order> queryWrapper);
-
-
- @Select("SELECT " +
- " DATE(create_time) AS day, " +
- " COUNT(*) AS count " +
- " FROM" +
- " li_order o" +
- " WHERE " +
- " o.create_time BETWEEN #{startTime} AND #{endTime} " +
- " GROUP BY" +
- " day " +
- " ORDER BY " +
- " day ASC")
- List<Map<String, Object>> selectOrderCountByDay(Date startTime, Date endTime);
-
- @Select("SELECT " +
- " DATE(create_time) AS day, " +
- " HOUR(create_time) AS hour," +
- " COUNT(*) AS count " +
- " FROM" +
- " li_order o" +
- " WHERE " +
- " o.create_time BETWEEN #{startTime} AND #{endTime} " +
- " GROUP BY" +
- " day " +
- " ORDER BY " +
- " day , hour ASC")
- List<Map<String, Object>> selectOrderTimePeriod(Date startTime, Date endTime);
}
\ No newline at end of file
diff --git a/framework/src/main/java/cn/lili/modules/order/order/service/OrderService.java b/framework/src/main/java/cn/lili/modules/order/order/service/OrderService.java
index 7b9fb68..e6eb89e 100644
--- a/framework/src/main/java/cn/lili/modules/order/order/service/OrderService.java
+++ b/framework/src/main/java/cn/lili/modules/order/order/service/OrderService.java
@@ -362,4 +362,6 @@
Result getOrderCount(StatisticsQueryParam queryParam);
Result getOrderTimePeriod(StatisticsQueryParam queryParam);
+
+ Result gerProductRepurchase(StatisticsQueryParam queryParam);
}
\ No newline at end of file
diff --git a/framework/src/main/java/cn/lili/modules/order/order/serviceimpl/OrderServiceImpl.java b/framework/src/main/java/cn/lili/modules/order/order/serviceimpl/OrderServiceImpl.java
index c3003e8..3dfac11 100644
--- a/framework/src/main/java/cn/lili/modules/order/order/serviceimpl/OrderServiceImpl.java
+++ b/framework/src/main/java/cn/lili/modules/order/order/serviceimpl/OrderServiceImpl.java
@@ -35,6 +35,7 @@
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;
@@ -113,6 +114,7 @@
import javax.servlet.http.HttpServletResponse;
import java.io.InputStream;
import java.math.BigDecimal;
+import java.math.RoundingMode;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
@@ -227,6 +229,11 @@
@Autowired
private GoodsSkuService goodsSkuService;
+
+ @Autowired
+ private LmkOrderSelectMapper lmkOrderSelectMapper;
+
+
private final static String LOCK_ORDER_NO_MQ="lock_order_no_mq:";
private final static String LOCK_EDIT_ORDER_ADDRESS="lock_edit_order_address:";
private final static String LOCK_COUPON_CARD="lock_coupon_card:";
@@ -1399,7 +1406,7 @@
}
}
- List<Map<String, Object>> maps = baseMapper.selectOrderCountByDay(startTime,endTime);
+ List<Map<String, Object>> maps = lmkOrderSelectMapper.selectOrderCountByDay(startTime,endTime);
Map<String, Long> dayCountMap = maps.stream()
.collect(Collectors.toMap(
@@ -1494,15 +1501,17 @@
- List<Map<String, Object>> maps = baseMapper.selectOrderTimePeriod(startTime,endTime);
-
+ List<Map<String, Object>> maps = lmkOrderSelectMapper.selectOrderTimePeriod(startTime,endTime);
+ List<Map<String,Object>> countMap = lmkOrderSelectMapper.selectViewDataCount(startTime,endTime);
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("---------------------------------------");
// 閬嶅巻鏌ョ湅缁撴灉
List<Long> countList = new ArrayList<>();
for (int i = 0; i < 24; i++) {
@@ -1520,6 +1529,103 @@
return Result.ok().data(map);
}
+ @Override
+ public Result gerProductRepurchase(StatisticsQueryParam queryParam) {
+ Date startTime = null;
+ Date endTime = new Date(); // 缁撴潫鏃堕棿榛樿鏄綋鍓嶆椂闂�
+
+ Calendar calendar = Calendar.getInstance(); // 鐢ㄤ簬鏃ユ湡璁＄畻鐨勬棩鍘嗗疄渚�
+
+ if (queryParam.getYear() != null && queryParam.getMonth() != null) {
+ Date[] dates =CommonUtil.getMonthStartAndEnd(queryParam.getYear(),queryParam.getMonth());
+ startTime = dates[0];
+ endTime = dates[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瀵硅薄
+ 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();
+ 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();
+
+ 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();
+
+ break;
+ default:
+ return Result.error("涓嶆敮鎸佺殑鏃堕棿鑼冨洿绫诲瀷");
+ }
+ }
+ List<Map<String, Object>> maps = lmkOrderSelectMapper.selectProductRepurchase(startTime,endTime,queryParam.getCurrentLimit());
+ List<String> goodsData = new ArrayList<>();
+ List<BigDecimal> rateData = new ArrayList<>();
+
+ // 閬嶅巻缁撴灉闆嗭紝鎻愬彇鏁版嵁鍒伴泦鍚�
+ for (Map<String, Object> map : maps) {
+ // 鎻愬彇鍟嗗搧鍚嶏紙娉ㄦ剰锛氶敭鏄痳esultMap涓畾涔夌殑property鍊�"goodsName"锛�
+ if (Objects.nonNull(map.get("goodsName"))) {
+ String goodsName = map.get("goodsName").toString();
+ goodsData.add(goodsName);
+ }
+
+ // 鎻愬彇澶嶈喘鐜囷紙澶嶈喘鐜囬�氬父鏄暟瀛楃被鍨嬶紝杩欓噷鐢˙igDecimal鎺ユ敹锛�
+ if (Objects.nonNull(map.get("repurchaseRate"))) {
+ // 杞崲涓築igDecimal锛堟牴鎹疄闄呯被鍨嬭皟鏁达紝涔熷彲鑳芥槸Double绛夛級
+ BigDecimal rate = new BigDecimal(map.get("repurchaseRate").toString()).setScale(2, RoundingMode.HALF_UP);
+ rateData.add(rate);
+ }
+ }
+ Map<String,Object> map = new HashMap<>();
+ map.put("goodsData",goodsData);
+ map.put("rateData",rateData);
+
+ return Result.ok().data(map);
+ }
+
/**
* 铏氭嫙鎴愬洟
*
diff --git a/framework/src/main/resources/mapper/lmk/orderMapper.xml b/framework/src/main/resources/mapper/lmk/orderMapper.xml
new file mode 100644
index 0000000..690d8fe
--- /dev/null
+++ b/framework/src/main/resources/mapper/lmk/orderMapper.xml
@@ -0,0 +1,105 @@
+<?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="cn.lili.modules.lmk.mapper.LmkOrderSelectMapper">
+ <resultMap id="OrderCountByDayMap" type="java.util.Map">
+ <result column="day" property="day" />
+ <result column="count" property="count"/>
+ </resultMap>
+
+ <resultMap id="orderTimePeriod" type="java.util.Map">
+ <result column="day" property="day" />
+ <result column="hour" property="hour"/>
+ </resultMap>
+
+ <resultMap id="productRepurchase" type="java.util.Map">
+ <result column="goods_name" property="goodsName"/>
+ <result column="repurchase_rate_percent" property="repurchaseRate"/>
+ </resultMap>
+
+ <resultMap id="viewDataCount" type="java.util.Map">
+ <result column="day" property="day" />
+ <result column="hour" property="hour"/>
+ <result column="count" property="count"/>
+ </resultMap>
+
+ <select id="selectOrderCountByDay" resultMap="OrderCountByDayMap">
+ SELECT
+ DATE(create_time) AS day,
+ COUNT(*) AS count
+ FROM
+ li_order o
+ WHERE
+ o.create_time BETWEEN #{startTime} AND #{endTime}
+ GROUP BY
+ day
+ ORDER BY
+ day ASC
+ </select>
+
+ <select id="selectOrderTimePeriod" resultMap="orderTimePeriod">
+ SELECT
+ DATE(create_time) AS day,
+ HOUR(create_time) AS hour,
+ COUNT(*) AS count
+ FROM
+ li_order o
+ WHERE
+ o.create_time BETWEEN #{startTime} AND #{endTime}
+ GROUP BY
+ day
+ ORDER BY
+ day , hour ASC
+ </select>
+ <select id="selectViewDataCount" resultMap="">
+ SELECT
+ DATE(LAR.create_time) AS day,
+ HOUR(LAR.create_time) AS hour,
+ COUNT(LAR.id) AS count
+ FROM lmk_action_record LAR
+ where LAR.delete_flag = 0
+ AND LAR.page_status = 'JOIN'
+ GROUP BY day, hour
+ ORDER BY day, hour;
+ </select>
+<!--buyer_count璐拱浜烘暟 buy_times鎬昏喘涔版鏁� repurchase_buyer_count 澶嶈喘浜烘暟-->
+ <select id="selectProductRepurchase" resultMap="productRepurchase">
+ SELECT
+ t.goods_name,
+ t.total_users AS buyer_count,
+ t.total_buy_times AS buy_times,
+ t.repurchase_users AS repurchase_buyer_count,
+ ROUND(
+ IFNULL(t.repurchase_users / t.total_users, 0) * 100,
+ 2
+ ) AS repurchase_rate_percent
+ FROM (
+ SELECT
+ goods_id,
+ goods_name,
+ COUNT(DISTINCT member_id) AS total_users,
+ SUM(user_buy_counts.buy_count) AS total_buy_times,
+ SUM(CASE WHEN buy_count >= 2 THEN 1 ELSE 0 END) AS repurchase_users
+ FROM (
+ SELECT
+ i.goods_id,
+ i.goods_name,
+ o.member_id,
+ COUNT(DISTINCT o.sn) AS buy_count
+ FROM li_order_item i
+ INNER JOIN li_order o ON i.order_sn = o.sn
+ WHERE
+ o.order_status = 'COMPLETED'
+ AND o.delete_flag = 0
+ AND i.delete_flag = 0
+ AND o.member_id IS NOT NULL
+ AND o.create_time >= #{startTime}
+ AND o.create_time <= #{endTime}
+ GROUP BY i.goods_id, i.goods_name, o.member_id
+ ) AS user_buy_counts
+ GROUP BY goods_id, goods_name
+ ) AS t
+ ORDER BY repurchase_rate_percent DESC
+ LIMIT #{currentLimit};
+ </select>
+
+</mapper>
diff --git a/manager-api/src/main/java/cn/lili/controller/lmk/StatisticsController.java b/manager-api/src/main/java/cn/lili/controller/lmk/StatisticsController.java
index 0de0d3e..60e84be 100644
--- a/manager-api/src/main/java/cn/lili/controller/lmk/StatisticsController.java
+++ b/manager-api/src/main/java/cn/lili/controller/lmk/StatisticsController.java
@@ -106,4 +106,14 @@
return orderService.getOrderTimePeriod(queryParam);
}
+
+ /**
+ * 鍟嗗搧澶嶈喘鐜�
+ * @param queryParam
+ * @return
+ */
+ @GetMapping("/productRepurchase")
+ public Result gerProductRepurchase(StatisticsQueryParam queryParam){
+ return orderService.gerProductRepurchase(queryParam);
+ }
}
--
Gitblit v1.8.0