ruoyi-api/ruoyi-api-system/src/main/java/com/ruoyi/system/api/domain/dto/MgtBasePlatformDto.java
@@ -47,4 +47,7 @@ @ApiModelProperty(value="活动id") private String activityId; @ApiModelProperty(value = "年龄层 1 <20,2 21-30,3 31-40,4 41-50,5 >50") private Integer ageType; } ruoyi-api/ruoyi-api-system/src/main/java/com/ruoyi/system/api/domain/dto/MgtBaseShopDto.java
@@ -16,4 +16,6 @@ @ApiModelProperty(value = "商户id") private Long shopId; } ruoyi-modules/ruoyi-member/src/main/java/com/ruoyi/member/service/impl/member/MemberServiceImpl.java
@@ -1255,7 +1255,7 @@ menPercent = new BigDecimal("100.00"); womenPercent = new BigDecimal("0.00"); }else{ menPercent = manTotalBig.divide(personTotalBig,2,BigDecimal.ROUND_HALF_UP); menPercent = manTotalBig.divide(personTotalBig,2,BigDecimal.ROUND_HALF_UP).multiply(new BigDecimal("100")); womenPercent = bigTen.subtract(menPercent); } mgtTotalMemberTotalVo.setManPercent(menPercent); @@ -1398,7 +1398,7 @@ menPercent = new BigDecimal("100.00"); womenPercent = new BigDecimal("0.00"); }else{ menPercent = manTotalBig.divide(personTotalBig,2,BigDecimal.ROUND_HALF_UP); menPercent = manTotalBig.divide(personTotalBig,2,BigDecimal.ROUND_HALF_UP).multiply(new BigDecimal("100")); womenPercent = bigTen.subtract(menPercent); } plTotalMemberTotalVo.setManTotal(manTotal); ruoyi-modules/ruoyi-member/src/main/resources/mapper/member/MemberMapper.xml
@@ -404,8 +404,8 @@ IFNULL(SUM(CASE WHEN gender = 0 THEN 1 ELSE 0 END),0) manTotal, IFNULL(SUM(CASE WHEN gender = 1 THEN 1 ELSE 0 END),0) womenTotal, IFNULL(SUM(CASE WHEN TIMESTAMPDIFF(YEAR, birthday, CURDATE()) BETWEEN 1 AND 20 THEN 1 ELSE 0 END),0) AS tenAge, IFNULL(SUM(CASE WHEN TIMESTAMPDIFF(YEAR, birthday, CURDATE()) BETWEEN 21 AND 30 THEN 1 ELSE 0 END),0) AS twentyAge, IFNULL(SUM(CASE WHEN TIMESTAMPDIFF(YEAR, birthday, CURDATE()) BETWEEN 31 AND 40 THEN 1 ELSE 0 END),0) AS ThirtyAge, IFNULL(SUM(CASE WHEN TIMESTAMPDIFF(YEAR, birthday, CURDATE()) BETWEEN 21 AND 31 THEN 1 ELSE 0 END),0) AS twentyAge, IFNULL(SUM(CASE WHEN TIMESTAMPDIFF(YEAR, birthday, CURDATE()) BETWEEN 31 AND 41 THEN 1 ELSE 0 END),0) AS ThirtyAge, IFNULL(SUM(CASE WHEN TIMESTAMPDIFF(YEAR, birthday, CURDATE()) BETWEEN 41 AND 50 THEN 1 ELSE 0 END),0) AS fortyAge, IFNULL(SUM(CASE WHEN TIMESTAMPDIFF(YEAR, birthday, CURDATE()) >= 51 THEN 1 ELSE 0 END),0) AS fiftyAge FROM t_member WHERE del_flag = 0 AND binding_flag = 1 @@ -538,10 +538,10 @@ AND TIMESTAMPDIFF(YEAR, birthday, CURDATE()) BETWEEN 1 AND 20 </if> <if test="param.ageType != null and param.ageType ==2"> AND TIMESTAMPDIFF(YEAR, birthday, CURDATE()) BETWEEN 21 AND 30 AND TIMESTAMPDIFF(YEAR, birthday, CURDATE()) BETWEEN 21 AND 31 </if> <if test="param.ageType != null and param.ageType ==3"> AND TIMESTAMPDIFF(YEAR, birthday, CURDATE()) BETWEEN 31 AND 40 AND TIMESTAMPDIFF(YEAR, birthday, CURDATE()) BETWEEN 31 AND 41 </if> <if test="param.ageType != null and param.ageType ==4"> AND TIMESTAMPDIFF(YEAR, birthday, CURDATE()) BETWEEN 41 AND 50 @@ -570,8 +570,8 @@ IFNULL(SUM(CASE WHEN gender = 0 THEN 1 ELSE 0 END),0) manTotal, IFNULL(SUM(CASE WHEN gender = 1 THEN 1 ELSE 0 END),0) womenTotal, IFNULL(SUM(CASE WHEN TIMESTAMPDIFF(YEAR, birthday, CURDATE()) BETWEEN 1 AND 20 THEN 1 ELSE 0 END),0) AS tenAge, IFNULL(SUM(CASE WHEN TIMESTAMPDIFF(YEAR, birthday, CURDATE()) BETWEEN 21 AND 30 THEN 1 ELSE 0 END),0) AS twentyAge, IFNULL(SUM(CASE WHEN TIMESTAMPDIFF(YEAR, birthday, CURDATE()) BETWEEN 31 AND 40 THEN 1 ELSE 0 END),0) AS ThirtyAge, IFNULL(SUM(CASE WHEN TIMESTAMPDIFF(YEAR, birthday, CURDATE()) BETWEEN 21 AND 31 THEN 1 ELSE 0 END),0) AS twentyAge, IFNULL(SUM(CASE WHEN TIMESTAMPDIFF(YEAR, birthday, CURDATE()) BETWEEN 31 AND 41 THEN 1 ELSE 0 END),0) AS ThirtyAge, IFNULL(SUM(CASE WHEN TIMESTAMPDIFF(YEAR, birthday, CURDATE()) BETWEEN 41 AND 50 THEN 1 ELSE 0 END),0) AS fortyAge, IFNULL(SUM(CASE WHEN TIMESTAMPDIFF(YEAR, birthday, CURDATE()) >= 51 THEN 1 ELSE 0 END),0) AS fiftyAge FROM t_member WHERE del_flag = 0 AND binding_flag = 1 @@ -783,8 +783,8 @@ IFNULL(SUM(CASE WHEN gender = 0 THEN 1 ELSE 0 END),0) manTotal, IFNULL(SUM(CASE WHEN gender = 1 THEN 1 ELSE 0 END),0) womenTotal, IFNULL(SUM(CASE WHEN TIMESTAMPDIFF(YEAR, birthday, CURDATE()) <= 20 THEN 1 ELSE 0 END),0) AS tenAge, IFNULL(SUM(CASE WHEN TIMESTAMPDIFF(YEAR, birthday, CURDATE()) BETWEEN 21 AND 30 THEN 1 ELSE 0 END),0) AS twentyAge, IFNULL(SUM(CASE WHEN TIMESTAMPDIFF(YEAR, birthday, CURDATE()) BETWEEN 31 AND 40 THEN 1 ELSE 0 END),0) AS ThirtyAge, IFNULL(SUM(CASE WHEN TIMESTAMPDIFF(YEAR, birthday, CURDATE()) BETWEEN 21 AND 31 THEN 1 ELSE 0 END),0) AS twentyAge, IFNULL(SUM(CASE WHEN TIMESTAMPDIFF(YEAR, birthday, CURDATE()) BETWEEN 31 AND 41 THEN 1 ELSE 0 END),0) AS ThirtyAge, IFNULL(SUM(CASE WHEN TIMESTAMPDIFF(YEAR, birthday, CURDATE()) BETWEEN 41 AND 50 THEN 1 ELSE 0 END),0) AS fortyAge, IFNULL(SUM(CASE WHEN TIMESTAMPDIFF(YEAR, birthday, CURDATE()) >= 51 THEN 1 ELSE 0 END),0) AS fiftyAge FROM t_member WHERE del_flag = 0 AND binding_flag = 1 @@ -994,10 +994,10 @@ AND TIMESTAMPDIFF(YEAR, birthday, CURDATE()) BETWEEN 1 AND 20 </if> <if test="param.ageType !=null and param.ageType == 2"> AND TIMESTAMPDIFF(YEAR, birthday, CURDATE()) BETWEEN 21 AND 30 AND TIMESTAMPDIFF(YEAR, birthday, CURDATE()) BETWEEN 21 AND 31 </if> <if test="param.ageType !=null and param.ageType == 3"> AND TIMESTAMPDIFF(YEAR, birthday, CURDATE()) BETWEEN 31 AND 40 AND TIMESTAMPDIFF(YEAR, birthday, CURDATE()) BETWEEN 31 AND 41 </if> <if test="param.ageType !=null and param.ageType == 4"> AND TIMESTAMPDIFF(YEAR, birthday, CURDATE()) BETWEEN 41 AND 50 ruoyi-modules/ruoyi-order/src/main/java/com/ruoyi/order/domain/vo/MgtPlTotalActivityTotalVo.java
@@ -48,16 +48,19 @@ @JsonSerialize(using = ToStringSerializer.class) private BigDecimal serviceMoney; @ApiModelProperty(value = "总人数") @ApiModelProperty(value = "总参与人数") private Integer orderJoinPerson; @ApiModelProperty(value = "总获客人数") private Integer orderPerson; @ApiModelProperty(value = "周期人数") @ApiModelProperty(value = "周期获客人数") private Integer cyclePerson; @ApiModelProperty(value = "体验人数") @ApiModelProperty(value = "体验获客人数") private Integer experiencePerson; @ApiModelProperty(value = "服务人数") @ApiModelProperty(value = "服务获客人数") private Integer servicePerson; @ApiModelProperty(value = "订单数量key") @@ -91,4 +94,16 @@ @ApiModelProperty(value = "活动年龄分布value") private Integer[] activityAgeValue; @ApiModelProperty(value = "参与人数key") private String[] joinMemberTotalKey; @ApiModelProperty(value = "参与人数key") private Integer[] joinMemberTotalValue; @ApiModelProperty(value = "获客人数key") private String[] getMemberTotalKey; @ApiModelProperty(value = "获客人数key") private Integer[] getMemberTotalValue; } ruoyi-modules/ruoyi-order/src/main/java/com/ruoyi/order/domain/vo/MgtTotalActivityTotalVo.java
@@ -48,16 +48,19 @@ @JsonSerialize(using = ToStringSerializer.class) private BigDecimal serviceMoney; @ApiModelProperty(value = "总人数") @ApiModelProperty(value = "总参与人数") private Integer orderJoinPerson; @ApiModelProperty(value = "总获客人数") private Integer orderPerson; @ApiModelProperty(value = "周期人数") @ApiModelProperty(value = "周期获客人数") private Integer cyclePerson; @ApiModelProperty(value = "体验人数") @ApiModelProperty(value = "体验获客人数") private Integer experiencePerson; @ApiModelProperty(value = "服务人数") @ApiModelProperty(value = "服务获客人数") private Integer servicePerson; @ApiModelProperty(value = "订单数量key") @@ -81,4 +84,10 @@ @ApiModelProperty(value = "活动年龄分布value") private Integer[] activityAgeValue; @ApiModelProperty(value = "参与人数key") private String[] joinMemberTotalKey; @ApiModelProperty(value = "参与人数key") private Integer[] joinMemberTotalValue; } ruoyi-modules/ruoyi-order/src/main/java/com/ruoyi/order/mapper/order/OrderMapper.java
@@ -374,6 +374,24 @@ */ MgtPlTotalActivityTotalVo getPlTotalActivityTotal(@Param("param") MgtBasePlatformDto mgtBasePlatformDto); /** * @description 活动获客人数 * @author jqs * @date 2023/8/26 16:07 * @param mgtBasePlatformDto * @return MgtPlTotalActivityTotalVo */ MgtPlTotalActivityTotalVo getPlTotalActivityMemberTotal(@Param("param") MgtBasePlatformDto mgtBasePlatformDto); /** * @description 活动获客人数 * @author jqs * @date 2023/8/26 16:07 * @param mgtBaseShopDto * @return getTotalActivityMemberTotal */ MgtTotalActivityTotalVo getTotalActivityMemberTotal(@Param("param") MgtBaseShopDto mgtBaseShopDto); /** * @description listTotalActivityTotalGoodsType @@ -622,6 +640,9 @@ */ List<MgtMapIntTotalVo> getStaffActivityGetMemberTotal(@Param("param")StaffTotalDto staffTotalDto); List<MgtMapIntTotalVo> getMgtActivityGetMemberTotal(@Param("param")MgtBasePlatformDto mgtBasePlatformDto); /** * @description 员工端获取活动获客人数 * @author jqs @@ -631,6 +652,9 @@ */ List<MgtMapIntTotalVo> getStaffActivityMemberTotal(@Param("param")StaffTotalDto staffTotalDto); List<MgtMapIntTotalVo> getMgtActivityMemberTotal(@Param("param")MgtBasePlatformDto mgtBasePlatformDto); /** * @description 获取用户商品购买数量 * @author jqs ruoyi-modules/ruoyi-order/src/main/java/com/ruoyi/order/service/impl/order/OrderServiceImpl.java
@@ -2836,6 +2836,12 @@ public MgtTotalActivityTotalVo getTotalActivityTotal(MgtBaseShopDto mgtBaseShopDto) { //获取基础统计 MgtTotalActivityTotalVo totalActivityTotalVo = orderMapper.getTotalActivityTotal(mgtBaseShopDto); //获客人数 MgtTotalActivityTotalVo totalActivityMemberTotalVo = orderMapper.getTotalActivityMemberTotal(mgtBaseShopDto); totalActivityTotalVo.setOrderPerson(totalActivityMemberTotalVo.getOrderPerson()); totalActivityTotalVo.setCyclePerson(totalActivityMemberTotalVo.getCyclePerson()); totalActivityTotalVo.setServicePerson(totalActivityMemberTotalVo.getServicePerson()); totalActivityTotalVo.setExperiencePerson(totalActivityMemberTotalVo.getExperiencePerson()); //获取商品分类销售数据 List<MgtMapTotalPlusVo> MgtMapIntTotalVoGoodsType = orderMapper.listTotalActivityTotalGoodsType(mgtBaseShopDto); if (MgtMapIntTotalVoGoodsType != null && MgtMapIntTotalVoGoodsType.size() > 0) { @@ -2896,6 +2902,8 @@ totalActivityTotalVo.setOrderTotalValue(orderTotalValue); totalActivityTotalVo.setOrderMoneyValue(orderMoneyValue); } //参与人数 return totalActivityTotalVo; } @@ -3152,6 +3160,12 @@ mgtBasePlatformDto.setShopIdList(shopIdList); //获取基础统计 MgtPlTotalActivityTotalVo totalActivityTotalVo = orderMapper.getPlTotalActivityTotal(mgtBasePlatformDto); //获客人数 MgtPlTotalActivityTotalVo totalActivityMemberTotalVo = orderMapper.getPlTotalActivityMemberTotal(mgtBasePlatformDto); totalActivityTotalVo.setOrderPerson(totalActivityMemberTotalVo.getOrderPerson()); totalActivityTotalVo.setCyclePerson(totalActivityMemberTotalVo.getCyclePerson()); totalActivityTotalVo.setServicePerson(totalActivityMemberTotalVo.getServicePerson()); totalActivityTotalVo.setExperiencePerson(totalActivityMemberTotalVo.getExperiencePerson()); //获取商品分类销售数据 List<MgtMapTotalPlusVo> MgtMapIntTotalVoGoodsType = orderMapper.listPlTotalActivityTotalGoodsType(mgtBasePlatformDto); List<MgtMapIntTotalVo> goodsTypeTotalList = new ArrayList<>(); @@ -3241,6 +3255,57 @@ activitySalesRank = bigListRemoveNull(activitySalesRank); totalActivityTotalVo.setActivitySalesRankList(activitySalesRank); } MerTotalDto merTotalDto = new MerTotalDto(); merTotalDto.setAgeType(mgtBasePlatformDto.getAgeType()); if(mgtBasePlatformDto.getAgeType()!=null){ List<Long> userIdList = remoteMemberService.listUserIdByAgeType(mgtBasePlatformDto.getAgeType()).getData(); mgtBasePlatformDto.setUserIdList(userIdList); } //参与人数 List<MgtMapIntTotalVo> joinList = orderMapper.getMgtActivityMemberTotal(mgtBasePlatformDto); // 将查询结果转为Map Map<String, Integer> mgtMapIntTotalVoMap = new HashMap<>(); if (joinList != null && !joinList.isEmpty()) { mgtMapIntTotalVoMap = joinList.stream() .collect(Collectors.toMap(MgtMapIntTotalVo::getMapKey, MgtMapIntTotalVo::getMapValue)); } // 获取日期范围 List<String> dateList = getDateRange(mgtBasePlatformDto.getStartDate(), mgtBasePlatformDto.getEndDate()); int size = dateList.size(); // 创建日期和成员总数的数组 String[] joinMemberTotalKey = new String[size]; Integer[] joinMemberTotalValue = new Integer[size]; // 遍历日期列表，设置日期和成员总数的数组 String str; Integer value; for (int i = 0; i < size; i++) { str = dateList.get(i); joinMemberTotalKey[i] = str; value = mgtMapIntTotalVoMap.get(str); joinMemberTotalValue[i] = (value != null) ? value : 0; } totalActivityTotalVo.setJoinMemberTotalKey(joinMemberTotalKey); totalActivityTotalVo.setJoinMemberTotalValue(joinMemberTotalValue); //获客人数 List<MgtMapIntTotalVo> getList = orderMapper.getMgtActivityGetMemberTotal(mgtBasePlatformDto); // 将查询结果转为Map Map<String, Integer> getMemberMap = new HashMap<>(); if (getList != null && !getList.isEmpty()) { getMemberMap = getList.stream() .collect(Collectors.toMap(MgtMapIntTotalVo::getMapKey, MgtMapIntTotalVo::getMapValue)); } // 创建日期和成员总数的数组 String[] getMemberTotalKey = new String[size]; Integer[] getMemberTotalValue = new Integer[size]; // 遍历日期列表，设置日期和成员总数的数组 for (int i = 0; i < size; i++) { str = dateList.get(i); joinMemberTotalKey[i] = str; value = getMemberMap.get(str); joinMemberTotalValue[i] = (value != null) ? value : 0; } totalActivityTotalVo.setGetMemberTotalKey(getMemberTotalKey); totalActivityTotalVo.setGetMemberTotalValue(getMemberTotalValue); return totalActivityTotalVo; }
@@ -480,7 +480,6 @@ */ StaffActivityDateMemberTotalVo getStaffActivityMemberTotal(StaffTotalDto staffTotalDto); /** * @description 检查订单状态定时任务 * @author jqs ruoyi-modules/ruoyi-order/src/main/resources/mapper/order/OrderMapper.xml
@@ -893,10 +893,10 @@ <select id="getTotalActivityTotal" resultType="com.ruoyi.order.domain.vo.MgtTotalActivityTotalVo"> SELECT COUNT(order_id) orderTotal, IFNULL(SUM(order_money),0) orderMoney, COUNT(DISTINCT user_id) orderPerson FROM t_order WHERE del_flag = 0 AND order_from = 2 COUNT(CASE WHEN order_status = 3 THEN order_id ELSE NULL END) orderTotal, IFNULL(SUM(CASE WHEN order_status = 3 AND pay_type = 1 THEN change_receivable_money WHEN order_status = 3 AND pay_type = 2 THEN change_receivable_money + online_pay_money ELSE 0 END),0) orderMoney, COUNT(DISTINCT user_id) orderJoinPerson FROM t_order WHERE del_flag = 0 AND order_from = 2 AND order_status IN (2,3) <if test="param.shopId != null and param.shopId != ''"> AND shop_id = #{param.shopId} </if> @@ -913,9 +913,8 @@ tog.goods_type mapKey, COUNT(DISTINCT toc.order_id) AS mapValueFirst, IFNULL(SUM(tog.goods_total_money),0) AS mapValueSecond, COUNT(DISTINCT toc.user_id) AS mapValueThird, FROM t_order toc COUNT(DISTINCT toc.user_id) AS mapValueThird FROM t_order toc INNER JOIN t_order_goods tog ON tog.order_id = toc.order_id WHERE toc.del_flag = 0 AND order_from = 2 <if test="param.shopId != null and param.shopId != ''"> @@ -1200,7 +1199,7 @@ SELECT tog.goods_type mapKey, COUNT(DISTINCT toc.order_id) AS mapValueFirst, IFNULL(SUM(tog.goods_total_money),0) AS mapValueSecond IFNULL(SUM(tog.goods_receivable_money),0) AS mapValueSecond FROM t_order toc INNER JOIN t_order_goods tog ON tog.order_id = toc.order_id @@ -1217,7 +1216,8 @@ <select id="listPlTotalOrderTotalGoodsRank" resultType="com.ruoyi.system.api.domain.vo.MgtMapBigTotalVo"> SELECT tog.goods_name mapKey, IFNULL(SUM(tog.goods_total_money),0) mapValue IFNULL(SUM(tog.goods_receivable_money),0) mapValue, COUNT(DISTINCT toc.order_id) mapTotalValue FROM t_order toc INNER JOIN t_order_goods tog ON tog.order_id = toc.order_id WHERE toc.del_flag = 0 @@ -1265,10 +1265,10 @@ <select id="getPlTotalActivityTotal" resultType="com.ruoyi.order.domain.vo.MgtPlTotalActivityTotalVo"> SELECT COUNT(order_id) orderTotal, IFNULL(SUM(order_money),0) orderMoney, COUNT(DISTINCT user_id) orderPerson FROM t_order WHERE del_flag = 0 AND order_from = 2 COUNT(CASE WHEN order_status = 3 THEN order_id ELSE NULL END) orderTotal, IFNULL(SUM(CASE WHEN order_status = 3 AND pay_type = 1 THEN change_receivable_money WHEN order_status = 3 AND pay_type = 2 THEN change_receivable_money + online_pay_money ELSE 0 END),0) orderMoney, COUNT(DISTINCT user_id) orderJoinPerson FROM t_order WHERE del_flag = 0 AND order_from = 2 AND order_status IN (2,3) <if test="param.shopIdList != null and param.shopIdList.size() > 0"> AND shop_id IN <foreach collection="param.shopIdList" item="item" open="(" separator="," close=")"> @@ -1283,15 +1283,61 @@ </if> </select> <select id="getPlTotalActivityMemberTotal" resultType="com.ruoyi.order.domain.vo.MgtPlTotalActivityTotalVo"> SELECT COUNT(DISTINCT toc.user_id) orderPerson, COUNT(DISTINCT CASE WHEN tog.goods_type = 1 THEN toc.user_id ELSE NULL END) cyclePerson, COUNT(DISTINCT CASE WHEN tog.goods_type = 2 THEN toc.user_id ELSE NULL END) experiencePerson, COUNT(DISTINCT CASE WHEN tog.goods_type = 3 THEN toc.user_id ELSE NULL END) servicePerson FROM t_order toc INNER JOIN t_order_goods tog ON tog.order_id = toc.order_id WHERE toc.del_flag = 0 AND toc.order_from = 2 AND toc.order_status = 3 AND toc.new_member_flag = 1 <if test="param.shopIdList != null and param.shopIdList.size() > 0"> AND toc.shop_id IN <foreach collection="param.shopIdList" item="item" open="(" separator="," close=")"> #{item} </foreach> </if> <if test="param.startDate!=null and param.startDate!=''"> AND Date(toc.create_time) >= #{param.startDate} </if> <if test="param.endDate!=null and param.endDate!=''"> AND Date(toc.create_time) <= #{param.endDate} </if> </select> <select id="getTotalActivityMemberTotal" resultType="com.ruoyi.order.domain.vo.MgtTotalActivityTotalVo"> SELECT COUNT(DISTINCT toc.user_id) orderPerson, COUNT(DISTINCT CASE WHEN tog.goods_type = 1 THEN toc.user_id ELSE NULL END) cyclePerson, COUNT(DISTINCT CASE WHEN tog.goods_type = 2 THEN toc.user_id ELSE NULL END) experiencePerson, COUNT(DISTINCT CASE WHEN tog.goods_type = 3 THEN toc.user_id ELSE NULL END) servicePerson FROM t_order toc INNER JOIN t_order_goods tog ON tog.order_id = toc.order_id WHERE toc.del_flag = 0 AND toc.order_from = 2 AND toc.order_status = 3 AND toc.new_member_flag = 1 <if test="param.shopIdList != null and param.shopIdList.size() > 0"> AND toc.shop_id IN <foreach collection="param.shopIdList" item="item" open="(" separator="," close=")"> #{item} </foreach> </if> <if test="param.startDate!=null and param.startDate!=''"> AND Date(toc.create_time) >= #{param.startDate} </if> <if test="param.endDate!=null and param.endDate!=''"> AND Date(toc.create_time) <= #{param.endDate} </if> </select> <select id="listPlTotalActivityTotalGoodsType" resultType="com.ruoyi.order.domain.vo.MgtMapTotalPlusVo"> SELECT tog.goods_type mapKey, COUNT(DISTINCT toc.order_id) AS mapValueFirst, IFNULL(SUM(tog.goods_total_money),0) AS mapValueSecond, IFNULL(SUM(tog.goods_receivable_money),0) AS mapValueSecond, COUNT(DISTINCT toc.user_id) AS mapValueThird FROM t_order toc INNER JOIN t_order_goods tog ON tog.order_id = toc.order_id WHERE toc.del_flag = 0 AND toc.order_from = 2 WHERE toc.del_flag = 0 AND toc.order_from = 2 AND order_status = 3 <if test="param.shopIdList != null and param.shopIdList.size() > 0"> AND toc.shop_id IN <foreach collection="param.shopIdList" item="item" open="(" separator="," close=")"> @@ -1311,9 +1357,9 @@ SELECT DATE_FORMAT(create_time, '%Y-%m-%d') AS mapKey, COUNT(DISTINCT order_id) AS mapValueFirst, IFNULL(SUM(order_money),0) AS mapValueSecond IFNULL(SUM(CASE WHEN pay_type = 1 THEN change_receivable_money WHEN pay_type = 2 THEN change_receivable_money + online_pay_money ELSE 0 END),0) AS mapValueSecond FROM t_order WHERE del_flag = 0 AND order_from = 2 WHERE del_flag = 0 AND order_from = 2 AND order_status = 3 AND Date(create_time) >= DATE_SUB(CURDATE(), INTERVAL 6 DAY) <if test="param.shopIdList != null and param.shopIdList.size() > 0"> AND shop_id IN @@ -1371,12 +1417,18 @@ shop_id AS mapKey, COUNT(order_id) AS mapValue FROM t_order WHERE del_flag = 0 AND order_from = 2 WHERE del_flag = 0 AND order_from = 2 AND order_status IN (2,3) <if test="param.shopIdList != null and param.shopIdList.size() > 0"> AND shop_id IN <foreach collection="param.shopIdList" item="item" open="(" separator="," close=")"> #{item} </foreach> </if> <if test="param.startDate!=null and param.startDate!=''"> AND Date(create_time) >= #{param.startDate} </if> <if test="param.endDate!=null and param.endDate!=''"> AND Date(create_time) <= #{param.endDate} </if> GROUP BY shop_id ORDER BY COUNT(order_id) DESC LIMIT 15 @@ -1409,6 +1461,12 @@ <foreach collection="param.shopIdList" item="item" open="(" separator="," close=")"> #{item} </foreach> </if> <if test="param.startDate!=null and param.startDate!=''"> AND Date(create_time) >= #{param.startDate} </if> <if test="param.endDate!=null and param.endDate!=''"> AND Date(create_time) <= #{param.endDate} </if> GROUP BY activity_name ORDER BY SUM(order_money) DESC LIMIT 15 @@ -1555,7 +1613,7 @@ <if test="param.endDate!=null and param.endDate!=''"> AND Date(toc.create_time) <= #{param.endDate} </if> GROUP BY mapValue GROUP BY mapKey ORDER BY mapValue DESC LIMIT 15 </select> @@ -1794,13 +1852,41 @@ GROUP BY mapKey </select> <select id="getMgtActivityGetMemberTotal" resultType="com.ruoyi.system.api.domain.vo.MgtMapIntTotalVo"> SELECT DATE(toc.create_time) AS mapKey, COUNT(DISTINCT toc.user_id) AS mapValue FROM t_order toc INNER JOIN t_order_goods tog ON tog.order_id = toc.order_id WHERE toc.order_from = 2 AND toc.new_member_flag = 1 AND toc.order_status IN (2,3) <if test="param.shopIdList != null and param.shopIdList.size() > 0"> AND toc.shop_id IN <foreach collection="param.shopIdList" item="item" open="(" separator="," close=")"> #{item} </foreach> </if> <if test="param.userIdList !=null and param.userIdList.size()>0"> AND toc.user_id IN <foreach collection="param.userIdList" item="item" open="(" separator="," close=")"> #{item} </foreach> </if> <if test="param.startDate!=null and param.startDate!=''"> AND Date(toc.create_time) >= #{param.startDate} </if> <if test="param.endDate!=null and param.endDate!=''"> AND Date(toc.create_time) <= #{param.endDate} </if> GROUP BY mapKey </select> <select id="getStaffActivityMemberTotal" resultType="com.ruoyi.system.api.domain.vo.MgtMapIntTotalVo"> SELECT DATE(toc.create_time) AS mapKey, COUNT(DISTINCT toc.user_id) AS mapValue FROM t_order toc INNER JOIN t_order_goods tog ON tog.order_id = toc.order_id WHERE toc.del_flag = 0 AND toc.shop_id = #{param.shopId} AND toc.order_from = 2 WHERE toc.del_flag = 0 AND toc.shop_id = #{param.shopId} AND toc.order_from = 2 AND toc.order_staus IN (2,3) <if test="param.activityId !=null and param.activityId != ''"> AND toc.activity_id = #{param.activityId} </if> @@ -1822,6 +1908,35 @@ GROUP BY mapKey </select> <select id="getMgtActivityMemberTotal" resultType="com.ruoyi.system.api.domain.vo.MgtMapIntTotalVo"> SELECT DATE(toc.create_time) AS mapKey, COUNT(DISTINCT toc.user_id) AS mapValue FROM t_order toc INNER JOIN t_order_goods tog ON tog.order_id = toc.order_id WHERE toc.del_flag = 0 AND toc.order_from = 2 AND toc.order_staus IN (2,3) <if test="param.shopIdList != null and param.shopIdList.size() > 0"> AND toc.shop_id IN <foreach collection="param.shopIdList" item="item" open="(" separator="," close=")"> #{item} </foreach> </if> <if test="param.userIdList !=null and param.userIdList.size()>0"> AND toc.user_id IN <foreach collection="param.userIdList" item="item" open="(" separator="," close=")"> #{item} </foreach> </if> <if test="param.startDate!=null and param.startDate!=''"> AND Date(toc.create_time) >= #{param.startDate} </if> <if test="param.endDate!=null and param.endDate!=''"> AND Date(toc.create_time) <= #{param.endDate} </if> GROUP BY mapKey </select> <select id="countUserBuyGoodsNum" resultType="java.lang.Integer"> SELECT SUM(tog.buy_num) FROM t_order toc
