From 0b9e60f0bada4cc39187a9dd3c36bf37452f63de Mon Sep 17 00:00:00 2001
From: puzhibing <393733352@qq.com>
Date: 星期四, 31 七月 2025 18:58:10 +0800
Subject: [PATCH] 修改bug
---
ruoyi-api/ruoyi-api-system/src/main/java/com/ruoyi/system/api/service/RemoteOrderService.java | 9
ruoyi-modules/ruoyi-goods/src/main/java/com/ruoyi/goods/service/impl/lottery/LotteryEventServiceImpl.java | 2
ruoyi-modules/ruoyi-order/src/main/resources/mapper/order/OrderMapper.xml | 576 +++++++++++++++++++++++++++++++---------------------
ruoyi-modules/ruoyi-member/src/main/java/com/ruoyi/member/domain/dto/MerMemberPageDto.java | 6
ruoyi-api/ruoyi-api-system/src/main/java/com/ruoyi/system/api/factory/RemoteOrderFallbackFactory.java | 6
ruoyi-modules/ruoyi-member/src/main/java/com/ruoyi/member/service/impl/member/MemberServiceImpl.java | 24 ++
ruoyi-modules/ruoyi-order/src/main/java/com/ruoyi/order/service/impl/order/ConsumerGoodsServiceImpl.java | 15 +
ruoyi-modules/ruoyi-order/src/main/java/com/ruoyi/order/controller/concole/OrderController.java | 18 +
ruoyi-modules/ruoyi-order/src/main/resources/mapper/order/UserServiceRecordMapper.xml | 4
9 files changed, 424 insertions(+), 236 deletions(-)
diff --git a/ruoyi-api/ruoyi-api-system/src/main/java/com/ruoyi/system/api/factory/RemoteOrderFallbackFactory.java b/ruoyi-api/ruoyi-api-system/src/main/java/com/ruoyi/system/api/factory/RemoteOrderFallbackFactory.java
index c97a5db..21cc85e 100644
--- a/ruoyi-api/ruoyi-api-system/src/main/java/com/ruoyi/system/api/factory/RemoteOrderFallbackFactory.java
+++ b/ruoyi-api/ruoyi-api-system/src/main/java/com/ruoyi/system/api/factory/RemoteOrderFallbackFactory.java
@@ -15,6 +15,7 @@
import java.math.BigDecimal;
import java.util.List;
+import java.util.Set;
/**
* 订单服务
@@ -108,6 +109,11 @@
public R addOrder(AddOrderVo orderVo) {
return R.fail("抽奖活动添加订单失败：" + throwable.getMessage());
}
+
+ @Override
+ public R<Set<Long>> getConsumerGoodsResidueUserId(Long shopId, Integer goodsType) {
+ return R.fail("获取剩余服务类型对应的用户id失败：" + throwable.getMessage());
+ }
};
}
}
diff --git a/ruoyi-api/ruoyi-api-system/src/main/java/com/ruoyi/system/api/service/RemoteOrderService.java b/ruoyi-api/ruoyi-api-system/src/main/java/com/ruoyi/system/api/service/RemoteOrderService.java
index fa287b2..d632569 100644
--- a/ruoyi-api/ruoyi-api-system/src/main/java/com/ruoyi/system/api/service/RemoteOrderService.java
+++ b/ruoyi-api/ruoyi-api-system/src/main/java/com/ruoyi/system/api/service/RemoteOrderService.java
@@ -14,6 +14,7 @@
import java.math.BigDecimal;
import java.util.List;
+import java.util.Set;
@FeignClient(contextId = "remoteOrderService", value = ServiceNameConstants.ORDER_SERVICE, fallbackFactory = RemoteOrderFallbackFactory.class)
public interface RemoteOrderService {
@@ -144,4 +145,12 @@
@PostMapping(value = "/order/addOrder")
R addOrder(@RequestBody AddOrderVo orderVo);
+
+ /**
+ * 获取剩余服务类型对应的用户id
+ * @param shopId
+ * @return
+ */
+ @PostMapping("/order/getConsumerGoodsResidueUserId")
+ R<Set<Long>> getConsumerGoodsResidueUserId(@RequestParam("shopId") Long shopId, @RequestParam("goodsType") Integer goodsType);
}
diff --git a/ruoyi-modules/ruoyi-goods/src/main/java/com/ruoyi/goods/service/impl/lottery/LotteryEventServiceImpl.java b/ruoyi-modules/ruoyi-goods/src/main/java/com/ruoyi/goods/service/impl/lottery/LotteryEventServiceImpl.java
index 1967a52..c85351c 100644
--- a/ruoyi-modules/ruoyi-goods/src/main/java/com/ruoyi/goods/service/impl/lottery/LotteryEventServiceImpl.java
+++ b/ruoyi-modules/ruoyi-goods/src/main/java/com/ruoyi/goods/service/impl/lottery/LotteryEventServiceImpl.java
@@ -90,7 +90,7 @@
QueryWrapper<TUserLotteryEvent> wrapper = new QueryWrapper<TUserLotteryEvent>().eq("lottery_event_id", id).eq("user_id", userId);
//下单抽奖
if(2 == lotteryEvent.getActivityType()){
- wrapper.eq("orderId", orderId);
+ wrapper.eq("order_id", orderId);
}
List<TUserLotteryEvent> userLotteryEvents = userLotteryEventService.list(wrapper);
vo.setLaveTimes(lotteryEvent.getTimes() - userLotteryEvents.size());
diff --git a/ruoyi-modules/ruoyi-member/src/main/java/com/ruoyi/member/domain/dto/MerMemberPageDto.java b/ruoyi-modules/ruoyi-member/src/main/java/com/ruoyi/member/domain/dto/MerMemberPageDto.java
index 911ae89..646edc4 100644
--- a/ruoyi-modules/ruoyi-member/src/main/java/com/ruoyi/member/domain/dto/MerMemberPageDto.java
+++ b/ruoyi-modules/ruoyi-member/src/main/java/com/ruoyi/member/domain/dto/MerMemberPageDto.java
@@ -72,6 +72,12 @@
@ApiModelProperty(value = "是否有未跟进任务（0=否，1=是）")
private Integer isTask;
+
+ @ApiModelProperty(value = "剩余周期人数（0=否，1=是）")
+ private Integer residuePeriod;
+
+ @ApiModelProperty(value = "剩余体验人数（0=否，1=是）")
+ private Integer residueExperience;
private List<Integer> userIds;
diff --git a/ruoyi-modules/ruoyi-member/src/main/java/com/ruoyi/member/service/impl/member/MemberServiceImpl.java b/ruoyi-modules/ruoyi-member/src/main/java/com/ruoyi/member/service/impl/member/MemberServiceImpl.java
index ecff163..3a2ff61 100644
--- a/ruoyi-modules/ruoyi-member/src/main/java/com/ruoyi/member/service/impl/member/MemberServiceImpl.java
+++ b/ruoyi-modules/ruoyi-member/src/main/java/com/ruoyi/member/service/impl/member/MemberServiceImpl.java
@@ -435,13 +435,37 @@
//今日到店用户
if(null != merMemberPageDto.getIsTodayStore() && 1 == merMemberPageDto.getIsTodayStore()){
List<Integer> data = remoteOrderService.countShopServicePerson(merMemberPageDto.getShopId()).getData();
+ if(data.isEmpty()){
+ data.add(-1);
+ }
merMemberPageDto.setUserIds(data);
}
//有未跟进的任务用户
if(null != merMemberPageDto.getIsTask() && 1 == merMemberPageDto.getIsTask()){
List<Integer> data = remoteShopService.getMemberIngTotal(merMemberPageDto.getShopId()).getData();
+ if(data.isEmpty()){
+ data.add(-1);
+ }
merMemberPageDto.setUserIds(data);
}
+ //剩余周期人数
+ if(null != merMemberPageDto.getResiduePeriod() && 1 == merMemberPageDto.getResiduePeriod()){
+ Set<Long> data = remoteOrderService.getConsumerGoodsResidueUserId(merMemberPageDto.getShopId(), 1).getData();
+ List<Integer> collect = data.stream().map(s -> Integer.valueOf(s.intValue())).collect(Collectors.toList());
+ if(collect.isEmpty()){
+ collect.add(-1);
+ }
+ merMemberPageDto.setUserIds(collect);
+ }
+ //剩余体验人数
+ if(null != merMemberPageDto.getResidueExperience() && 1 == merMemberPageDto.getResidueExperience()){
+ Set<Long> data = remoteOrderService.getConsumerGoodsResidueUserId(merMemberPageDto.getShopId(), 3).getData();
+ List<Integer> collect = data.stream().map(s -> Integer.valueOf(s.intValue())).collect(Collectors.toList());
+ if(collect.isEmpty()){
+ collect.add(-1);
+ }
+ merMemberPageDto.setUserIds(collect);
+ }
List<MerMemberPageVo> merMemberPageVoList = memberMapper.pageMerMember(page, merMemberPageDto);
//处理活跃度返回
if (merMemberPageVoList != null && !merMemberPageVoList.isEmpty()) {
diff --git a/ruoyi-modules/ruoyi-order/src/main/java/com/ruoyi/order/controller/concole/OrderController.java b/ruoyi-modules/ruoyi-order/src/main/java/com/ruoyi/order/controller/concole/OrderController.java
index 801b6af..4b2ae44 100644
--- a/ruoyi-modules/ruoyi-order/src/main/java/com/ruoyi/order/controller/concole/OrderController.java
+++ b/ruoyi-modules/ruoyi-order/src/main/java/com/ruoyi/order/controller/concole/OrderController.java
@@ -1,8 +1,10 @@
package com.ruoyi.order.controller.concole;
+import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.github.binarywang.wxpay.bean.ecommerce.ProfitSharingResult;
import com.github.binarywang.wxpay.exception.WxPayException;
import com.ruoyi.common.core.domain.R;
+import com.ruoyi.order.domain.pojo.order.ConsumerGoods;
import com.ruoyi.order.service.order.ConsumerGoodsService;
import com.ruoyi.order.service.order.OrderService;
import com.ruoyi.order.service.order.UserServiceRecordService;
@@ -19,6 +21,8 @@
import javax.annotation.Resource;
import java.math.BigDecimal;
import java.util.List;
+import java.util.Set;
+import java.util.stream.Collectors;
/**
* @author jqs34
@@ -238,4 +242,18 @@
orderService.addOrder(orderVo.getShopId(), orderVo.getUserId(), goods, orderVo.getGoodsName(), orderVo.getGoodsNum(), orderVo.getId(), 2);
return R.ok();
}
+
+
+ /**
+ * 获取剩余服务类型对应的用户id
+ * @param shopId
+ * @return
+ */
+ @PostMapping("/getConsumerGoodsResidueUserId")
+ public R<Set<Long>> getConsumerGoodsResidueUserId(@RequestParam("shopId") Long shopId, @RequestParam("goodsType") Integer goodsType){
+ List<ConsumerGoods> list = consumerGoodsService.list(new QueryWrapper<ConsumerGoods>().eq("del_flag", 0)
+ .eq("service_status", 1).eq("shop_id", shopId).eq("cycle_num_flag", 1).eq("goods_type", goodsType));
+ Set<Long> collect = list.stream().map(ConsumerGoods::getUserId).collect(Collectors.toSet());
+ return R.ok(collect);
+ }
}
diff --git a/ruoyi-modules/ruoyi-order/src/main/java/com/ruoyi/order/service/impl/order/ConsumerGoodsServiceImpl.java b/ruoyi-modules/ruoyi-order/src/main/java/com/ruoyi/order/service/impl/order/ConsumerGoodsServiceImpl.java
index 806d05e..e8e8086 100644
--- a/ruoyi-modules/ruoyi-order/src/main/java/com/ruoyi/order/service/impl/order/ConsumerGoodsServiceImpl.java
+++ b/ruoyi-modules/ruoyi-order/src/main/java/com/ruoyi/order/service/impl/order/ConsumerGoodsServiceImpl.java
@@ -103,7 +103,7 @@
merConsumerGoodsVo.setNoSureList(noSureList);
}
List<MerConsumerGoodsListVo> sureList = consumerGoodsMapper.listMerConsumerGoods(merConsumerGoodsDto);
- Member member = remoteMemberService.getMember(merConsumerGoodsDto.getUserId()).getData();
+ Member member = remoteMemberService.getMember(merConsumerGoodsDto.getMemberUserId()).getData();
String lastServiceId = member.getLastServiceId();
if (StringUtils.isNotEmpty(lastServiceId)) {
List<String> list = JSON.parseArray(lastServiceId, String.class);
@@ -126,6 +126,8 @@
if(oldConsumerGoods.getSureNum()==null||oldConsumerGoods.getSureNum()<1){
throw new ServiceException(AppErrorConstant.GOODS_SURE_FAILED);
}
+ Member member = remoteMemberService.getMember(merSureConsumerGoodsDto.getMemberUserId()).getData();
+ Shop shop = remoteShopService.getShop(merSureConsumerGoodsDto.getShopId()).getData();
List<MerSureConsumerGoodsListDto> merSureConsumerGoodsListDtoList = merSureConsumerGoodsDto.getMerSureConsumerGoodsListDtoList();
if(merSureConsumerGoodsListDtoList!=null && !merSureConsumerGoodsListDtoList.isEmpty()){
ConsumerGoods consumerGoods;
@@ -182,6 +184,15 @@
sureNum = sureNum + 1;
}
}
+
+ String msg = "{\"name\":\"" + (StringUtils.isEmpty(member.getRealName()) ? member.getNickName() : member.getRealName()) + "\"," +
+ "\"store\":\"" + shop.getShopName() + "\",\"product\":\"" + goods.getGoodsName() + "\"}";
+ try {
+ MsgUtils.sendMsg(member.getMobile(), "SMS_491320362", msg);
+ } catch (Exception e) {
+ e.printStackTrace();
+ }
+
}
//判断商品确认次数
if (sureNum > oldConsumerGoods.getSureNum()) {
@@ -321,7 +332,7 @@
//保存本次确认的服务商品id，用于下次进入页面后默认勾选上
List<String> collect = goodsList.stream().map(MerSureConsumerGoodsListDto::getConsumerGoodsId).collect(Collectors.toList());
- Member member = remoteMemberService.getMember(merSureConsumerGoodsDto.getUserId()).getData();
+ Member member = remoteMemberService.getMember(merSureConsumerGoodsDto.getMemberUserId()).getData();
member.setLastServiceId(JSON.toJSONString(collect));
remoteMemberService.editMember(member);
diff --git a/ruoyi-modules/ruoyi-order/src/main/resources/mapper/order/OrderMapper.xml b/ruoyi-modules/ruoyi-order/src/main/resources/mapper/order/OrderMapper.xml
index 64a43e8..f855e5b 100644
--- a/ruoyi-modules/ruoyi-order/src/main/resources/mapper/order/OrderMapper.xml
+++ b/ruoyi-modules/ruoyi-order/src/main/resources/mapper/order/OrderMapper.xml
@@ -3,28 +3,28 @@
<mapper namespace="com.ruoyi.order.mapper.order.OrderMapper">
<resultMap type="com.ruoyi.order.domain.pojo.order.Order" id="OrderResult">
- <result property="orderId" column="order_id" />
- <result property="delFlag" column="del_flag" />
- <result property="orderStatus" column="order_status" />
- <result property="orderNo" column="order_no" />
- <result property="orderFrom" column="order_from" />
- <result property="shopId" column="shop_id" />
- <result property="userId" column="user_id" />
- <result property="orderMoney" column="order_money" />
- <result property="couponMoney" column="coupon_money" />
- <result property="discountMoney" column="discount_money" />
- <result property="receivableMoney" column="receivable_money" />
- <result property="payType" column="pay_type" />
- <result property="payMoney" column="pay_money" />
- <result property="onlinePayMoney" column="online_pay_money" />
- <result property="offlinePayMoney" column="offline_pay_money" />
- <result property="orderRemark" column="order_remark" />
- <result property="goodsInfo" column="goods_info" />
- <result property="createTime" column="create_time" />
- <result property="payTime" column="pay_time" />
- <result property="useTime" column="use_time" />
- <result property="cancelTime" column="cancel_time" />
- <result property="useUserId" column="use_user_id" />
+ <result property="orderId" column="order_id"/>
+ <result property="delFlag" column="del_flag"/>
+ <result property="orderStatus" column="order_status"/>
+ <result property="orderNo" column="order_no"/>
+ <result property="orderFrom" column="order_from"/>
+ <result property="shopId" column="shop_id"/>
+ <result property="userId" column="user_id"/>
+ <result property="orderMoney" column="order_money"/>
+ <result property="couponMoney" column="coupon_money"/>
+ <result property="discountMoney" column="discount_money"/>
+ <result property="receivableMoney" column="receivable_money"/>
+ <result property="payType" column="pay_type"/>
+ <result property="payMoney" column="pay_money"/>
+ <result property="onlinePayMoney" column="online_pay_money"/>
+ <result property="offlinePayMoney" column="offline_pay_money"/>
+ <result property="orderRemark" column="order_remark"/>
+ <result property="goodsInfo" column="goods_info"/>
+ <result property="createTime" column="create_time"/>
+ <result property="payTime" column="pay_time"/>
+ <result property="useTime" column="use_time"/>
+ <result property="cancelTime" column="cancel_time"/>
+ <result property="useUserId" column="use_user_id"/>
</resultMap>
<resultMap id="orderResultMap" type="com.ruoyi.order.domain.vo.AppUserOrderPageVo">
@@ -33,10 +33,10 @@
<result column="orderNo" property="orderNo"/>
<result column="receivableMoney" property="receivableMoney"/>
<result column="goodsNum" property="goodsNum"/>
- <result column="shopId" property="shopId" />
- <result column="orderFrom" property="orderFrom" />
- <result column="createTime" property="createTime" />
- <result column="cancelType" property="cancelType" />
+ <result column="shopId" property="shopId"/>
+ <result column="orderFrom" property="orderFrom"/>
+ <result column="createTime" property="createTime"/>
+ <result column="cancelType" property="cancelType"/>
<collection property="appUserOrderGoodsPageVoList"
ofType="com.ruoyi.order.domain.vo.AppUserOrderGoodsPageVo"
select="listOrderGoodsByOrderId"
@@ -49,9 +49,9 @@
<result column="goodsType" property="goodsType"/>
<result column="buyNum" property="buyNum"/>
<result column="goodsPrice" property="goodsPrice"/>
- <result column="goodsRealPrice" property="goodsRealPrice" />
- <result column="cycleNumFlag" property="cycleNumFlag" />
- <result column="serviceNum" property="serviceNum" />
+ <result column="goodsRealPrice" property="goodsRealPrice"/>
+ <result column="cycleNumFlag" property="cycleNumFlag"/>
+ <result column="serviceNum" property="serviceNum"/>
</collection>
</resultMap>
@@ -70,7 +70,6 @@
</resultMap>-->
-
<resultMap id="merOrderResultMap" type="com.ruoyi.order.domain.vo.MerOrderPageVo">
<result column="userId" property="userId"/>
<result column="orderId" property="orderId"/>
@@ -85,10 +84,10 @@
<result column="payMoney" property="payMoney"/>
<result column="receiveMoney" property="receiveMoney"/>
<result column="unPaidMoney" property="unPaidMoney"/>
- <result column="orderRemark" property="orderRemark" />
- <result column="orderFrom" property="orderFrom" />
- <result column="createTime" property="createTime" />
- <result column="tripartiteOrderId" property="tripartiteOrderId" />
+ <result column="orderRemark" property="orderRemark"/>
+ <result column="orderFrom" property="orderFrom"/>
+ <result column="createTime" property="createTime"/>
+ <result column="tripartiteOrderId" property="tripartiteOrderId"/>
<collection property="merOrderGoodsVoList"
ofType="com.ruoyi.order.domain.vo.MerOrderGoodsPageVo"
select="listSimpleOrderGoodsByOrderId"
@@ -109,9 +108,9 @@
<result column="receivableMoney" property="receivableMoney"/>
<result column="payMoney" property="payMoney"/>
<result column="receiveMoney" property="receiveMoney"/>
- <result column="orderFrom" property="orderFrom" />
- <result column="orderFromDesc" property="orderFromDesc" />
- <result column="createTime" property="createTime" />
+ <result column="orderFrom" property="orderFrom"/>
+ <result column="orderFromDesc" property="orderFromDesc"/>
+ <result column="createTime" property="createTime"/>
<collection property="mgtOrderGoodsVoList"
ofType="com.ruoyi.order.domain.vo.MgtOrderGoodsPageVo"
select="listMgtSimpleOrderGoodsByOrderId"
@@ -170,25 +169,25 @@
<select id="selectOrderList" parameterType="com.ruoyi.order.domain.pojo.order.Order" resultMap="OrderResult">
<include refid="selectOrderVo"/>
<where>
- <if test="orderStatus != null "> and order_status = #{orderStatus}</if>
- <if test="orderNo != null and orderNo != ''"> and order_no = #{orderNo}</if>
- <if test="orderFrom != null "> and order_from = #{orderFrom}</if>
- <if test="shopId != null "> and shop_id = #{shopId}</if>
- <if test="userId != null "> and user_id = #{userId}</if>
- <if test="orderMoney != null "> and order_money = #{orderMoney}</if>
- <if test="couponMoney != null "> and coupon_money = #{couponMoney}</if>
- <if test="discountMoney != null "> and discount_money = #{discountMoney}</if>
- <if test="receivableMoney != null "> and receivable_money = #{receivableMoney}</if>
- <if test="payType != null "> and pay_type = #{payType}</if>
- <if test="payMoney != null "> and pay_money = #{payMoney}</if>
- <if test="onlinePayMoney != null "> and online_pay_money = #{onlinePayMoney}</if>
- <if test="offlinePayMoney != null "> and offline_pay_money = #{offlinePayMoney}</if>
- <if test="orderRemark != null and orderRemark != ''"> and order_remark = #{orderRemark}</if>
- <if test="goodsInfo != null and goodsInfo != ''"> and goods_info = #{goodsInfo}</if>
- <if test="payTime != null "> and pay_time = #{payTime}</if>
- <if test="useTime != null "> and use_time = #{useTime}</if>
- <if test="cancelTime != null "> and cancel_time = #{cancelTime}</if>
- <if test="useUserId != null "> and use_user_id = #{useUserId}</if>
+ <if test="orderStatus != null ">and order_status = #{orderStatus}</if>
+ <if test="orderNo != null and orderNo != ''">and order_no = #{orderNo}</if>
+ <if test="orderFrom != null ">and order_from = #{orderFrom}</if>
+ <if test="shopId != null ">and shop_id = #{shopId}</if>
+ <if test="userId != null ">and user_id = #{userId}</if>
+ <if test="orderMoney != null ">and order_money = #{orderMoney}</if>
+ <if test="couponMoney != null ">and coupon_money = #{couponMoney}</if>
+ <if test="discountMoney != null ">and discount_money = #{discountMoney}</if>
+ <if test="receivableMoney != null ">and receivable_money = #{receivableMoney}</if>
+ <if test="payType != null ">and pay_type = #{payType}</if>
+ <if test="payMoney != null ">and pay_money = #{payMoney}</if>
+ <if test="onlinePayMoney != null ">and online_pay_money = #{onlinePayMoney}</if>
+ <if test="offlinePayMoney != null ">and offline_pay_money = #{offlinePayMoney}</if>
+ <if test="orderRemark != null and orderRemark != ''">and order_remark = #{orderRemark}</if>
+ <if test="goodsInfo != null and goodsInfo != ''">and goods_info = #{goodsInfo}</if>
+ <if test="payTime != null ">and pay_time = #{payTime}</if>
+ <if test="useTime != null ">and use_time = #{useTime}</if>
+ <if test="cancelTime != null ">and cancel_time = #{cancelTime}</if>
+ <if test="useUserId != null ">and use_user_id = #{useUserId}</if>
</where>
</select>
@@ -310,24 +309,25 @@
</if>
<if test="param.keyword != null and param.keyword != ''">
AND (toc.order_no LIKE CONCAT('%',#{param.keyword},'%') OR
- EXISTS( SELECT 1 FROM t_order_goods tog WHERE tog.order_id = toc.order_id AND tog.goods_name LIKE CONCAT('%',#{param.keyword},'%')) )
+ EXISTS( SELECT 1 FROM t_order_goods tog WHERE tog.order_id = toc.order_id AND tog.goods_name LIKE
+ CONCAT('%',#{param.keyword},'%')) )
</if>
ORDER BY toc.create_time DESC
</select>
<select id="listOrderGoodsByOrderId" resultType="com.ruoyi.order.domain.vo.AppUserOrderGoodsPageVo">
SELECT
- tog.goods_id goodsId,
- tog.goods_name goodsName,
- tog.goods_introduction goodsIntroduction,
- tog.goods_picture goodsPicture,
- tog.order_goods_id orderGoodsId,
- tog.goods_type goodsType,
- tog.buy_num buyNum,
- tog.goods_price goodsPrice,
- tog.goods_total_money goodsRealPrice,
- tog.cycle_num_flag cycleNumFlag,
- tog.service_num serviceNum
+ tog.goods_id goodsId,
+ tog.goods_name goodsName,
+ tog.goods_introduction goodsIntroduction,
+ tog.goods_picture goodsPicture,
+ tog.order_goods_id orderGoodsId,
+ tog.goods_type goodsType,
+ tog.buy_num buyNum,
+ tog.goods_price goodsPrice,
+ tog.goods_total_money goodsRealPrice,
+ tog.cycle_num_flag cycleNumFlag,
+ tog.service_num serviceNum
FROM t_order_goods tog
WHERE tog.order_id = #{orderId}
<if test="keyword != null and keyword != ''">
@@ -343,12 +343,16 @@
toc.order_money orderGoodsMoney,
toc.coupon_money couponDiscount,
toc.pay_type payType,
- CASE toc.pay_type WHEN 1 THEN toc.order_money-toc.coupon_money WHEN 2 THEN toc.order_money-toc.coupon_money-toc.online_pay_money END receivableMoney,
+ CASE toc.pay_type WHEN 1 THEN toc.order_money-toc.coupon_money WHEN 2 THEN
+ toc.order_money-toc.coupon_money-toc.online_pay_money END receivableMoney,
toc.receivable_deposit receivableDeposit,
toc.pay_money payMoney,
- (CASE WHEN null = toc.change_receivable_money or toc.change_receivable_money = 0 THEN toc.receivable_money ELSE toc.change_receivable_money END) relReceiveMoney,
+ (CASE WHEN null = toc.change_receivable_money or toc.change_receivable_money = 0 THEN toc.receivable_money ELSE
+ toc.change_receivable_money END) relReceiveMoney,
CASE toc.pay_type WHEN 1 THEN toc.pay_money WHEN 2 THEN IFNULL(toc.offline_pay_money,0) END receiveMoney,
- (CASE WHEN null = toc.change_receivable_money or toc.change_receivable_money = 0 THEN toc.receivable_money ELSE toc.change_receivable_money END) - (CASE toc.pay_type WHEN 1 THEN toc.pay_money WHEN 2 THEN IFNULL(toc.offline_pay_money,0) END) unPaidMoney,
+ (CASE WHEN null = toc.change_receivable_money or toc.change_receivable_money = 0 THEN toc.receivable_money ELSE
+ toc.change_receivable_money END) - (CASE toc.pay_type WHEN 1 THEN toc.pay_money WHEN 2 THEN
+ IFNULL(toc.offline_pay_money,0) END) unPaidMoney,
toc.create_time createTime,
toc.close_flag closeFlag
FROM t_order toc
@@ -412,7 +416,8 @@
AND Date(toc.create_time) <= #{param.endOrderDate}
</if>
<if test="param.keyword != null and param.keyword != ''">
- AND (toc.order_no LIKE CONCAT('%',#{param.keyword},'%') OR EXISTS( SELECT 1 FROM t_order_goods tog WHERE tog.order_id = toc.order_id AND tog.goods_name LIKE CONCAT('%',#{param.keyword},'%'))
+ AND (toc.order_no LIKE CONCAT('%',#{param.keyword},'%') OR EXISTS( SELECT 1 FROM t_order_goods tog WHERE
+ tog.order_id = toc.order_id AND tog.goods_name LIKE CONCAT('%',#{param.keyword},'%'))
<if test="param.memberUserIdList != null and param.memberUserIdList.size()>0">
OR toc.user_id IN
<foreach collection="param.memberUserIdList" item="item" open="(" separator="," close=")">
@@ -440,7 +445,8 @@
IFNULL(SUM(IFNULL(toc.receivable_money,0)),0) totalReceivableMoney,
IFNULL(SUM(IFNULL(toc.discount_money,0)),0) totalDiscountMoney,
IFNULL(SUM(IFNULL(toc.pay_money,0)),0) totalPayMoney,
- IFNULL(SUM((CASE WHEN null = toc.change_receivable_money or toc.change_receivable_money = 0 THEN toc.receivable_money ELSE toc.change_receivable_money END)),0) totalReceiveMoney
+ IFNULL(SUM((CASE WHEN null = toc.change_receivable_money or toc.change_receivable_money = 0 THEN
+ toc.receivable_money ELSE toc.change_receivable_money END)),0) totalReceiveMoney
FROM t_order toc
WHERE toc.del_flag = 0 AND toc.user_id = #{param.memberUserId}
<if test="param.orderStatus != null">
@@ -468,12 +474,14 @@
WHEN 2 THEN "待核销"
WHEN 3 THEN "已完成"
END orderStatus,
- CASE WHEN toc.unbinding_flag = 0 THEN toc.shop_id WHEN toc.unbinding_flag = 1 AND toc.order_from = 1 THEN toc.shop_id WHEN toc.order_status = 3 THEN toc.shop_id ELSE NULL END shopId,
+ CASE WHEN toc.unbinding_flag = 0 THEN toc.shop_id WHEN toc.unbinding_flag = 1 AND toc.order_from = 1 THEN
+ toc.shop_id WHEN toc.order_status = 3 THEN toc.shop_id ELSE NULL END shopId,
toc.order_money orderGoodsMoney,
toc.discount_money discountMoney,
toc.receivable_money receivableMoney,
toc.pay_money payMoney,
- (CASE WHEN null = toc.change_receivable_money or toc.change_receivable_money = 0 THEN toc.receivable_money ELSE toc.change_receivable_money END) receiveMoney,
+ (CASE WHEN null = toc.change_receivable_money or toc.change_receivable_money = 0 THEN toc.receivable_money ELSE
+ toc.change_receivable_money END) receiveMoney,
toc.create_time createTime,
toc.order_from orderFrom,
CASE toc.order_from
@@ -488,7 +496,8 @@
AND toc.order_status = #{param.orderStatus}
</if>
<if test="param.shopId != null and param.shopId != ''">
- AND toc.shop_id = #{param.shopId} AND (toc.unbinding_flag = 0 OR (toc.unbinding_flag = 1 AND toc.order_from = 1) OR (toc.unbinding_flag = 1 AND toc.order_from = 2 AND toc.order_status = 3))
+ AND toc.shop_id = #{param.shopId} AND (toc.unbinding_flag = 0 OR (toc.unbinding_flag = 1 AND toc.order_from
+ = 1) OR (toc.unbinding_flag = 1 AND toc.order_from = 2 AND toc.order_status = 3))
</if>
<if test="param.orderFrom != null and param.orderFrom != ''">
AND toc.order_from = #{param.orderFrom}
@@ -500,7 +509,8 @@
AND Date(toc.create_time) <= #{param.endOrderDate}
</if>
<if test="param.keyword != null and param.keyword != ''">
- AND (toc.order_no LIKE CONCAT('%',#{param.keyword},'%') EXISTS( SELECT 1 FROM t_order_goods tog WHERE tog.order_id = toc.order_id AND tog.goods_name LIKE CONCAT('%',#{param.keyword},'%')))
+ AND (toc.order_no LIKE CONCAT('%',#{param.keyword},'%') EXISTS( SELECT 1 FROM t_order_goods tog WHERE
+ tog.order_id = toc.order_id AND tog.goods_name LIKE CONCAT('%',#{param.keyword},'%')))
</if>
ORDER BY toc.create_time DESC
</select>
@@ -526,21 +536,21 @@
<select id="pageMgtActivityOrder" resultType="com.ruoyi.order.domain.vo.MgtActivityOrderPageVo">
SELECT
- toc.order_id orderId,
- toc.order_no orderNo,
- toc.out_trade_no as outTradeNo,
- toc.user_id userId,
- toc.activity_name activityName,
- tog.goods_name goodsName,
- tog.buy_num buyNum,
- toc.order_money orderMoney,
- toc.discount_money discountMoney,
- toc.receivable_money receivableMoney,
- toc.create_time createTime,
- tog.goods_price activityPrice,
- toc.pay_money payMoney,
- CASE WHEN toc.order_status = 3 THEN toc.shop_id ELSE NULL END shopId,
- toc.use_time verifyTime
+ toc.order_id orderId,
+ toc.order_no orderNo,
+ toc.out_trade_no as outTradeNo,
+ toc.user_id userId,
+ toc.activity_name activityName,
+ tog.goods_name goodsName,
+ tog.buy_num buyNum,
+ toc.order_money orderMoney,
+ toc.discount_money discountMoney,
+ toc.receivable_money receivableMoney,
+ toc.create_time createTime,
+ tog.goods_price activityPrice,
+ toc.pay_money payMoney,
+ CASE WHEN toc.order_status = 3 THEN toc.shop_id ELSE NULL END shopId,
+ toc.use_time verifyTime
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
@@ -551,13 +561,15 @@
AND tog.goods_type = #{param.goodsType}
</if>
<if test="param.shopId != null and param.shopId != ''">
- AND toc.shop_id = #{param.shopId} AND (toc.unbinding_flag = 0 OR (toc.unbinding_flag = 1 AND toc.order_from = 1) OR (toc.unbinding_flag = 1 AND toc.order_from = 2 AND toc.order_status = 3))
+ AND toc.shop_id = #{param.shopId} AND (toc.unbinding_flag = 0 OR (toc.unbinding_flag = 1 AND toc.order_from
+ = 1) OR (toc.unbinding_flag = 1 AND toc.order_from = 2 AND toc.order_status = 3))
</if>
<if test="param.shopIds != null and param.shopIds != ''">
AND FIND_IN_SET(toc.shop_id, #{param.shopIds})
</if>
<if test="param.keyword != null and param.keyword != ''">
- AND (toc.order_no LIKE CONCAT('%',#{param.keyword},'%') OR toc.activity_name LIKE CONCAT('%',#{param.keyword},'%') OR FIND_IN_SET(toc.user_id, #{param.userIds}) > 0)
+ AND (toc.order_no LIKE CONCAT('%',#{param.keyword},'%') OR toc.activity_name LIKE
+ CONCAT('%',#{param.keyword},'%') OR FIND_IN_SET(toc.user_id, #{param.userIds}) > 0)
</if>
<if test="param.verifyStartTime != null and param.verifyStartTime != ''">
AND Date(toc.use_time) >= #{param.verifyStartTime}
@@ -567,11 +579,12 @@
</if>
ORDER BY toc.create_time DESC
</select>
-<!-- todo 统计-->
+ <!-- todo 统计-->
<select id="totalOrderFirst" resultType="com.ruoyi.order.domain.vo.MgtOrderTotal">
SELECT
COUNT(temp.order_id) orderTotal,
- IFNULL(SUM(CASE WHEN null = temp.change_receivable_money or temp.change_receivable_money = 0 THEN temp.receivable_money ELSE temp.change_receivable_money END),0) orderMoneyTotal
+ IFNULL(SUM(CASE WHEN null = temp.change_receivable_money or temp.change_receivable_money = 0 THEN
+ temp.receivable_money ELSE temp.change_receivable_money END),0) orderMoneyTotal
FROM
(SELECT toc.order_id,toc.change_receivable_money,toc.receivable_money,toc.pay_money
FROM t_order toc
@@ -584,16 +597,18 @@
AND tog.goods_type = #{param.goodsType}
</if>
<!--<if test="param.shopId != null and param.shopId != ''">-->
- <!--AND toc.shop_id = #{param.shopId}-->
+ <!--AND toc.shop_id = #{param.shopId}-->
<!--</if>-->
<if test="param.shopId != null and param.shopId != ''">
- AND toc.shop_id = #{param.shopId} AND (toc.unbinding_flag = 0 OR (toc.unbinding_flag = 1 AND toc.order_from = 1) OR (toc.unbinding_flag = 1 AND toc.order_from = 2 AND toc.order_status = 3))
+ AND toc.shop_id = #{param.shopId} AND (toc.unbinding_flag = 0 OR (toc.unbinding_flag = 1 AND toc.order_from
+ = 1) OR (toc.unbinding_flag = 1 AND toc.order_from = 2 AND toc.order_status = 3))
</if>
<if test="param.shopIds != null and param.shopIds != ''">
AND FIND_IN_SET(toc.shop_id, #{param.shopIds}) > 0
</if>
<if test="param.keyword != null and param.keyword != ''">
- AND (toc.order_no LIKE CONCAT('%',#{param.keyword},'%') OR toc.activity_name LIKE CONCAT('%',#{param.keyword},'%') OR FIND_IN_SET(toc.user_id, #{param.userIds}) > 0)
+ AND (toc.order_no LIKE CONCAT('%',#{param.keyword},'%') OR toc.activity_name LIKE
+ CONCAT('%',#{param.keyword},'%') OR FIND_IN_SET(toc.user_id, #{param.userIds}) > 0)
</if>
<if test="param.verifyStartTime != null and param.verifyStartTime != ''">
AND Date(toc.use_time) >= #{param.verifyStartTime}
@@ -607,9 +622,13 @@
<select id="totalOrderSecond" resultType="com.ruoyi.order.domain.vo.MgtOrderTotal">
SELECT
COUNT(temp.order_id) shopOrderTotal,
- IFNULL(SUM(CASE WHEN null = temp.change_receivable_money or temp.change_receivable_money = 0 THEN temp.receivable_money ELSE temp.change_receivable_money END),0) shopOrderMoneyTotal,
+ IFNULL(SUM(CASE WHEN null = temp.change_receivable_money or temp.change_receivable_money = 0 THEN
+ temp.receivable_money ELSE temp.change_receivable_money END),0) shopOrderMoneyTotal,
IFNULL(SUM(temp.pay_money),0) shopPayMoneyTotal,
- IFNULL(SUM(CASE WHEN (CASE WHEN null = temp.change_receivable_money or temp.change_receivable_money = 0 THEN temp.receivable_money ELSE temp.change_receivable_money END) > temp.pay_money THEN (CASE WHEN null = temp.change_receivable_money or temp.change_receivable_money = 0 THEN temp.receivable_money ELSE temp.change_receivable_money END) - temp.pay_money ELSE 0 END),0) shopUnPayMoneyTotal
+ IFNULL(SUM(CASE WHEN (CASE WHEN null = temp.change_receivable_money or temp.change_receivable_money = 0 THEN
+ temp.receivable_money ELSE temp.change_receivable_money END) > temp.pay_money THEN (CASE WHEN null =
+ temp.change_receivable_money or temp.change_receivable_money = 0 THEN temp.receivable_money ELSE
+ temp.change_receivable_money END) - temp.pay_money ELSE 0 END),0) shopUnPayMoneyTotal
FROM
(SELECT toc.order_id,toc.change_receivable_money,toc.receivable_money,toc.pay_money
FROM t_order toc
@@ -622,16 +641,18 @@
AND tog.goods_type = #{param.goodsType}
</if>
<!--<if test="param.shopId != null and param.shopId != ''">-->
- <!--AND toc.shop_id = #{param.shopId}-->
+ <!--AND toc.shop_id = #{param.shopId}-->
<!--</if>-->
<if test="param.shopId != null and param.shopId != ''">
- AND toc.shop_id = #{param.shopId} AND (toc.unbinding_flag = 0 OR (toc.unbinding_flag = 1 AND toc.order_from = 1) OR (toc.unbinding_flag = 1 AND toc.order_from = 2 AND toc.order_status = 3))
+ AND toc.shop_id = #{param.shopId} AND (toc.unbinding_flag = 0 OR (toc.unbinding_flag = 1 AND toc.order_from
+ = 1) OR (toc.unbinding_flag = 1 AND toc.order_from = 2 AND toc.order_status = 3))
</if>
<if test="param.shopIds != null and param.shopIds != ''">
AND FIND_IN_SET(toc.shop_id, #{param.shopIds}) > 0
</if>
<if test="param.keyword != null and param.keyword != ''">
- AND (toc.order_no LIKE CONCAT('%',#{param.keyword},'%') OR toc.activity_name LIKE CONCAT('%',#{param.keyword},'%') OR FIND_IN_SET(toc.user_id, #{param.userIds}) > 0)
+ AND (toc.order_no LIKE CONCAT('%',#{param.keyword},'%') OR toc.activity_name LIKE
+ CONCAT('%',#{param.keyword},'%') OR FIND_IN_SET(toc.user_id, #{param.userIds}) > 0)
</if>
<if test="param.verifyStartTime != null and param.verifyStartTime != ''">
AND Date(toc.use_time) >= #{param.verifyStartTime}
@@ -645,9 +666,13 @@
<select id="totalOrderThird" resultType="com.ruoyi.order.domain.vo.MgtOrderTotal">
SELECT
COUNT(temp.order_id) activityOrderTotal,
- IFNULL(SUM(CASE WHEN null = temp.change_receivable_money or temp.change_receivable_money = 0 THEN temp.receivable_money ELSE temp.change_receivable_money END),0) activityOrderMoneyTotal,
+ IFNULL(SUM(CASE WHEN null = temp.change_receivable_money or temp.change_receivable_money = 0 THEN
+ temp.receivable_money ELSE temp.change_receivable_money END),0) activityOrderMoneyTotal,
IFNULL(SUM(temp.pay_money),0) activityPayMoneyTotal,
- IFNULL(SUM(CASE WHEN (CASE WHEN null = temp.change_receivable_money or temp.change_receivable_money = 0 THEN temp.receivable_money ELSE temp.change_receivable_money END) > temp.pay_money THEN (CASE WHEN null = temp.change_receivable_money or temp.change_receivable_money = 0 THEN temp.receivable_money ELSE temp.change_receivable_money END) - temp.pay_money ELSE 0 END),0)activityUnPayMoneyTotal
+ IFNULL(SUM(CASE WHEN (CASE WHEN null = temp.change_receivable_money or temp.change_receivable_money = 0 THEN
+ temp.receivable_money ELSE temp.change_receivable_money END) > temp.pay_money THEN (CASE WHEN null =
+ temp.change_receivable_money or temp.change_receivable_money = 0 THEN temp.receivable_money ELSE
+ temp.change_receivable_money END) - temp.pay_money ELSE 0 END),0)activityUnPayMoneyTotal
FROM
(SELECT toc.order_id,toc.change_receivable_money,toc.receivable_money,toc.pay_money
FROM t_order toc
@@ -660,16 +685,18 @@
AND tog.goods_type = #{param.goodsType}
</if>
<!--<if test="param.shopId != null and param.shopId != ''">-->
- <!--AND toc.shop_id = #{param.shopId}-->
+ <!--AND toc.shop_id = #{param.shopId}-->
<!--</if>-->
<if test="param.shopId != null and param.shopId != ''">
- AND toc.shop_id = #{param.shopId} AND (toc.unbinding_flag = 0 OR (toc.unbinding_flag = 1 AND toc.order_from = 1) OR (toc.unbinding_flag = 1 AND toc.order_from = 2 AND toc.order_status = 3))
+ AND toc.shop_id = #{param.shopId} AND (toc.unbinding_flag = 0 OR (toc.unbinding_flag = 1 AND toc.order_from
+ = 1) OR (toc.unbinding_flag = 1 AND toc.order_from = 2 AND toc.order_status = 3))
</if>
<if test="param.shopIds != null and param.shopIds != ''">
and toc.order_status = 3 AND FIND_IN_SET(toc.shop_id, #{param.shopIds}) > 0
</if>
<if test="param.keyword != null and param.keyword != ''">
- AND (toc.order_no LIKE CONCAT('%',#{param.keyword},'%') OR toc.activity_name LIKE CONCAT('%',#{param.keyword},'%') OR FIND_IN_SET(toc.user_id, #{param.userIds}) > 0)
+ AND (toc.order_no LIKE CONCAT('%',#{param.keyword},'%') OR toc.activity_name LIKE
+ CONCAT('%',#{param.keyword},'%') OR FIND_IN_SET(toc.user_id, #{param.userIds}) > 0)
</if>
<if test="param.verifyStartTime != null and param.verifyStartTime != ''">
AND Date(toc.use_time) >= #{param.verifyStartTime}
@@ -708,7 +735,8 @@
AND FIND_IN_SET(toc.shop_id, #{param.shopIds}) > 0
</if>
<if test="param.keyword != null and param.keyword != ''">
- AND (toc.order_no LIKE CONCAT('%',#{param.keyword},'%') OR toc.activity_name LIKE CONCAT('%',#{param.keyword},'%') OR FIND_IN_SET(toc.user_id, #{param.userIds}) > 0)
+ AND (toc.order_no LIKE CONCAT('%',#{param.keyword},'%') OR toc.activity_name LIKE
+ CONCAT('%',#{param.keyword},'%') OR FIND_IN_SET(toc.user_id, #{param.userIds}) > 0)
</if>
<if test="param.verifyStartTime != null and param.verifyStartTime != ''">
AND Date(toc.use_time) >= #{param.verifyStartTime}
@@ -730,21 +758,22 @@
toc.receivable_money receivableMoney,
toc.create_time createTime,
toc.pay_money payMoney,
--- CASE WHEN toc.unbinding_flag = 0 THEN toc.shop_id WHEN toc.unbinding_flag = 1 AND toc.order_from = 1 THEN toc.shop_id WHEN toc.order_status = 3 THEN toc.shop_id ELSE NULL END shopId,
+ -- CASE WHEN toc.unbinding_flag = 0 THEN toc.shop_id WHEN toc.unbinding_flag = 1 AND toc.order_from = 1 THEN
+ toc.shop_id WHEN toc.order_status = 3 THEN toc.shop_id ELSE NULL END shopId,
toc.shop_id shopId,
toc.use_time verifyTime,
CASE toc.order_from
- WHEN 1 THEN '小程序'
- WHEN 2 THEN '秒杀活动'
- WHEN 3 THEN '线下创建'
- WHEN 4 THEN '抽奖'
- WHEN 5 THEN '美团'
- WHEN 6 THEN '抖音'
- WHEN 7 THEN '快手'
- END orderFrom,
+ WHEN 1 THEN '小程序'
+ WHEN 2 THEN '秒杀活动'
+ WHEN 3 THEN '线下创建'
+ WHEN 4 THEN '抽奖'
+ WHEN 5 THEN '美团'
+ WHEN 6 THEN '抖音'
+ WHEN 7 THEN '快手'
+ END orderFrom,
#{param.keyword} AS keyword
FROM t_order toc
- WHERE toc.del_flag = 0 AND toc.order_from != 2 -- 不查找秒杀活动的订单
+ WHERE toc.del_flag = 0 AND toc.order_from != 2 -- 不查找秒杀活动的订单
<if test="param.orderStatus != null">
AND toc.order_status = #{param.orderStatus}
</if>
@@ -755,13 +784,15 @@
AND tog.goods_type = #{param.goodsType}
</if>
<if test="param.shopId != null and param.shopId != ''">
- AND toc.shop_id = #{param.shopId} AND (toc.unbinding_flag = 0 OR (toc.unbinding_flag = 1 AND toc.order_from = 1) OR (toc.unbinding_flag = 1 AND toc.order_from = 2 AND toc.order_status = 3))
+ AND toc.shop_id = #{param.shopId} AND (toc.unbinding_flag = 0 OR (toc.unbinding_flag = 1 AND toc.order_from
+ = 1) OR (toc.unbinding_flag = 1 AND toc.order_from = 2 AND toc.order_status = 3))
</if>
<if test="param.shopIds != null and param.shopIds != ''">
AND FIND_IN_SET(toc.shop_id, #{param.shopIds}) > 0
</if>
<if test="param.keyword != null and param.keyword != ''">
- AND (toc.order_no LIKE CONCAT('%',#{param.keyword},'%') OR toc.activity_name LIKE CONCAT('%',#{param.keyword},'%') OR FIND_IN_SET(toc.user_id, #{param.userIds}) > 0)
+ AND (toc.order_no LIKE CONCAT('%',#{param.keyword},'%') OR toc.activity_name LIKE
+ CONCAT('%',#{param.keyword},'%') OR FIND_IN_SET(toc.user_id, #{param.userIds}) > 0)
</if>
<if test="param.verifyStartTime != null and param.verifyStartTime != ''">
AND Date(toc.use_time) >= #{param.verifyStartTime}
@@ -775,9 +806,10 @@
<select id="boardOrderTotal" resultType="com.ruoyi.system.api.domain.vo.MgtBulletinBoardVo">
SELECT
- IFNULL(SUM(CASE WHEN order_status = 3 THEN (CASE WHEN null = change_receivable_money or 0 = change_receivable_money THEN receivable_money ELSE change_receivable_money END) ELSE 0 END),0) salesTotal,
- IFNULL(SUM(CASE WHEN order_status = 2 THEN 1 ELSE 0 END),0) UnUseOrderTotal,
- IFNULL(SUM(CASE WHEN order_from = 2 THEN 1 ELSE 0 END),0) activityOrderTotal
+ IFNULL(SUM(CASE WHEN order_status = 3 THEN (CASE WHEN null = change_receivable_money or 0 =
+ change_receivable_money THEN receivable_money ELSE change_receivable_money END) ELSE 0 END),0) salesTotal,
+ IFNULL(SUM(CASE WHEN order_status = 2 THEN 1 ELSE 0 END),0) UnUseOrderTotal,
+ IFNULL(SUM(CASE WHEN order_from = 2 THEN 1 ELSE 0 END),0) activityOrderTotal
FROM t_order
WHERE del_flag = 0 AND order_status IN (2,3)
<if test="null != shopIds and shopIds.size() > 0">
@@ -797,7 +829,7 @@
<select id="activityUserTotal" resultType="java.lang.Integer">
SELECT
- COUNT(DISTINCT user_id)
+ COUNT(DISTINCT user_id)
FROM t_order
WHERE del_flag = 0 AND order_from = 2 AND order_status IN (2,3)
<if test="null != shopIds and shopIds.size() > 0">
@@ -814,10 +846,14 @@
IFNULL(SUM(CASE WHEN order_from = 1 THEN 1 ELSE 0 END),0) onlineTotal,
IFNULL(SUM(CASE WHEN order_from = 3 THEN 1 ELSE 0 END),0) offlineTotal,
IFNULL(SUM(CASE WHEN order_from = 2 THEN 1 ELSE 0 END),0) activityTotal,
- IFNULL(SUM(CASE WHEN null = change_receivable_money or change_receivable_money = 0 THEN receivable_money ELSE change_receivable_money END),0) orderMoney,
- IFNULL(SUM(CASE WHEN order_from = 1 THEN (CASE WHEN null = change_receivable_money or change_receivable_money = 0 THEN receivable_money ELSE change_receivable_money END) ELSE 0 END),0) onlineMoney,
- IFNULL(SUM(CASE WHEN order_from = 3 THEN (CASE WHEN null = change_receivable_money or change_receivable_money = 0 THEN receivable_money ELSE change_receivable_money END) ELSE 0 END),0) offlineMoney,
- IFNULL(SUM(CASE WHEN order_from = 2 THEN (CASE WHEN null = change_receivable_money or change_receivable_money = 0 THEN receivable_money ELSE change_receivable_money END) ELSE 0 END),0) activityMoney
+ IFNULL(SUM(CASE WHEN null = change_receivable_money or change_receivable_money = 0 THEN receivable_money ELSE
+ change_receivable_money END),0) orderMoney,
+ IFNULL(SUM(CASE WHEN order_from = 1 THEN (CASE WHEN null = change_receivable_money or change_receivable_money =
+ 0 THEN receivable_money ELSE change_receivable_money END) ELSE 0 END),0) onlineMoney,
+ IFNULL(SUM(CASE WHEN order_from = 3 THEN (CASE WHEN null = change_receivable_money or change_receivable_money =
+ 0 THEN receivable_money ELSE change_receivable_money END) ELSE 0 END),0) offlineMoney,
+ IFNULL(SUM(CASE WHEN order_from = 2 THEN (CASE WHEN null = change_receivable_money or change_receivable_money =
+ 0 THEN receivable_money ELSE change_receivable_money END) ELSE 0 END),0) activityMoney
FROM t_order WHERE del_flag = 0 AND order_status = 3
<if test="param.shopId != null and param.shopId != ''">
AND shop_id = #{param.shopId}
@@ -882,9 +918,12 @@
<select id="listTotalOrderTotal" resultType="com.ruoyi.order.domain.vo.MgtMapTotalPlusVo">
SELECT
- DATE_FORMAT(create_time, '%Y-%m-%d') AS mapKey,
- COUNT(order_id) AS mapValueFirst,
- IFNULL(SUM(CASE WHEN pay_type = 1 THEN (CASE WHEN null = change_receivable_money or change_receivable_money = 0 THEN receivable_money ELSE change_receivable_money END) WHEN pay_type = 2 THEN (CASE WHEN null = change_receivable_money or change_receivable_money = 0 THEN receivable_money ELSE change_receivable_money END) + online_pay_money ELSE 0 END),0) AS mapValueSecond
+ DATE_FORMAT(create_time, '%Y-%m-%d') AS mapKey,
+ COUNT(order_id) AS mapValueFirst,
+ IFNULL(SUM(CASE WHEN pay_type = 1 THEN (CASE WHEN null = change_receivable_money or change_receivable_money = 0
+ THEN receivable_money ELSE change_receivable_money END) WHEN pay_type = 2 THEN (CASE WHEN null =
+ change_receivable_money or change_receivable_money = 0 THEN receivable_money ELSE change_receivable_money END) +
+ online_pay_money ELSE 0 END),0) AS mapValueSecond
FROM t_order
WHERE del_flag = 0 AND order_status = 3
<if test="param.shopId != null and param.shopId != ''">
@@ -947,7 +986,10 @@
WHEN 2 THEN "秒杀订单"
WHEN 3 THEN "线下订单"
END mapKey,
- IFNULL(SUM(CASE WHEN toc.pay_type = 1 THEN (CASE WHEN null = toc.change_receivable_money or toc.change_receivable_money = 0 THEN toc.receivable_money ELSE toc.change_receivable_money END) WHEN toc.pay_type = 2 THEN (CASE WHEN null = toc.change_receivable_money or toc.change_receivable_money = 0 THEN toc.receivable_money ELSE toc.change_receivable_money END) + toc.online_pay_money ELSE 0 END),0) mapValue
+ IFNULL(SUM(CASE WHEN toc.pay_type = 1 THEN (CASE WHEN null = toc.change_receivable_money or
+ toc.change_receivable_money = 0 THEN toc.receivable_money ELSE toc.change_receivable_money END) WHEN
+ toc.pay_type = 2 THEN (CASE WHEN null = toc.change_receivable_money or toc.change_receivable_money = 0 THEN
+ toc.receivable_money ELSE toc.change_receivable_money END) + toc.online_pay_money ELSE 0 END),0) mapValue
FROM t_order toc
WHERE toc.del_flag = 0 AND toc.order_status = 3
<if test="param.shopId != null and param.shopId != ''">
@@ -966,7 +1008,10 @@
<select id="getTotalActivityTotal" resultType="com.ruoyi.order.domain.vo.MgtTotalActivityTotalVo">
SELECT
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 (CASE WHEN null = change_receivable_money or change_receivable_money = 0 THEN receivable_money ELSE change_receivable_money END) WHEN order_status = 3 AND pay_type = 2 THEN (CASE WHEN null = change_receivable_money or change_receivable_money = 0 THEN receivable_money ELSE change_receivable_money END) + online_pay_money ELSE 0 END),0) orderMoney,
+ IFNULL(SUM(CASE WHEN order_status = 3 AND pay_type = 1 THEN (CASE WHEN null = change_receivable_money or
+ change_receivable_money = 0 THEN receivable_money ELSE change_receivable_money END) WHEN order_status = 3 AND
+ pay_type = 2 THEN (CASE WHEN null = change_receivable_money or change_receivable_money = 0 THEN receivable_money
+ ELSE change_receivable_money END) + 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 =3 AND shop_id = #{param.shopId}
<if test="param.startDate!=null and param.startDate!=''">
@@ -999,7 +1044,10 @@
SELECT
DATE_FORMAT(create_time, '%Y-%m-%d') AS mapKey,
COUNT(DISTINCT order_id) AS mapValueFirst,
- IFNULL(SUM(CASE WHEN pay_type = 1 THEN (CASE WHEN null = change_receivable_money or change_receivable_money = 0 THEN receivable_money ELSE change_receivable_money END) WHEN pay_type = 2 THEN (CASE WHEN null = change_receivable_money or change_receivable_money = 0 THEN receivable_money ELSE change_receivable_money END) + online_pay_money ELSE 0 END),0) AS mapValueSecond
+ IFNULL(SUM(CASE WHEN pay_type = 1 THEN (CASE WHEN null = change_receivable_money or change_receivable_money = 0
+ THEN receivable_money ELSE change_receivable_money END) WHEN pay_type = 2 THEN (CASE WHEN null =
+ change_receivable_money or change_receivable_money = 0 THEN receivable_money ELSE change_receivable_money END) +
+ online_pay_money ELSE 0 END),0) AS mapValueSecond
FROM t_order
WHERE del_flag = 0 AND order_from = 2 AND order_status = 3 AND shop_id = #{param.shopId}
<if test="param.startDate!=null and param.startDate!=''">
@@ -1071,35 +1119,42 @@
<select id="getTotalDataMemberTotal" resultType="com.ruoyi.order.domain.vo.MgtTotalDataTotalVo">
SELECT
- COUNT(DISTINCT CASE WHEN goods_type_1_count > 0 AND goods_type_3_count = 0 THEN user_id ELSE NULL END) AS onlyCyclePerson,
- COUNT(DISTINCT CASE WHEN goods_type_1_count = 0 AND goods_type_3_count > 0 THEN user_id ELSE NULL END) AS onlyExperiencePerson,
- COUNT(DISTINCT CASE WHEN goods_type_1_count > 0 AND goods_type_3_count > 0 THEN user_id ELSE NULL END) AS experienceCyclePerson,
- COUNT(DISTINCT CASE WHEN goods_type_1_count > 0 OR goods_type_2_count > 0 OR goods_type_3_count > 0 OR goods_type_4_count > 0 THEN user_id ELSE NULL END) AS orderPerson
- FROM (
- SELECT
- o.user_id,
- COUNT(DISTINCT CASE WHEN og.goods_type = 1 THEN og.goods_id ELSE NULL END) AS goods_type_1_count,
- COUNT(DISTINCT CASE WHEN og.goods_type = 2 THEN og.goods_id ELSE NULL END) AS goods_type_2_count,
- COUNT(DISTINCT CASE WHEN og.goods_type = 3 THEN og.goods_id ELSE NULL END) AS goods_type_3_count,
- COUNT(DISTINCT CASE WHEN og.goods_type = 4 THEN og.goods_id ELSE NULL END) AS goods_type_4_count
- FROM t_order o
- JOIN t_order_goods og ON o.order_id = og.order_id
- WHERE o.del_flag = 0 AND og.del_flag = 0 AND o.order_status = 3
- <if test="param.shopId != null and param.shopId != ''">
- AND o.shop_id = #{param.shopId}
- </if>
- <if test="param.startDate!=null and param.startDate!=''">
- AND Date(o.create_time) >= #{param.startDate}
- </if>
- <if test="param.endDate!=null and param.endDate!=''">
- AND Date(o.create_time) <= #{param.endDate}
- </if>
- GROUP BY o.user_id
- ) AS user_goods_type_counts
+ COUNT(DISTINCT CASE WHEN goods_type_1_count > 0 AND goods_type_3_count = 0 THEN user_id ELSE NULL END) AS
+ onlyCyclePerson,
+ COUNT(DISTINCT CASE WHEN goods_type_1_count = 0 AND goods_type_3_count > 0 THEN user_id ELSE NULL END) AS
+ onlyExperiencePerson,
+ COUNT(DISTINCT CASE WHEN goods_type_1_count > 0 AND goods_type_3_count > 0 THEN user_id ELSE NULL END) AS
+ experienceCyclePerson,
+ COUNT(DISTINCT CASE WHEN goods_type_1_count > 0 OR goods_type_2_count > 0 OR goods_type_3_count > 0 OR
+ goods_type_4_count > 0 THEN user_id ELSE NULL END) AS orderPerson
+ FROM (
+ SELECT
+ o.user_id,
+ COUNT(DISTINCT CASE WHEN og.goods_type = 1 THEN og.goods_id ELSE NULL END) AS goods_type_1_count,
+ COUNT(DISTINCT CASE WHEN og.goods_type = 2 THEN og.goods_id ELSE NULL END) AS goods_type_2_count,
+ COUNT(DISTINCT CASE WHEN og.goods_type = 3 THEN og.goods_id ELSE NULL END) AS goods_type_3_count,
+ COUNT(DISTINCT CASE WHEN og.goods_type = 4 THEN og.goods_id ELSE NULL END) AS goods_type_4_count
+ FROM t_order o
+ JOIN t_order_goods og ON o.order_id = og.order_id
+ WHERE o.del_flag = 0 AND og.del_flag = 0 AND o.order_status = 3
+ <if test="param.shopId != null and param.shopId != ''">
+ AND o.shop_id = #{param.shopId}
+ </if>
+ <if test="param.startDate!=null and param.startDate!=''">
+ AND Date(o.create_time) >= #{param.startDate}
+ </if>
+ <if test="param.endDate!=null and param.endDate!=''">
+ AND Date(o.create_time) <= #{param.endDate}
+ </if>
+ GROUP BY o.user_id
+ ) AS user_goods_type_counts
</select>
<select id="shopSalesTotal" resultType="java.math.BigDecimal">
- SELECT IFNULL(SUM(CASE pay_type WHEN 1 THEN (CASE WHEN null = change_receivable_money or change_receivable_money = 0 THEN receivable_money ELSE change_receivable_money END) WHEN 2 THEN (CASE WHEN null = change_receivable_money or change_receivable_money = 0 THEN receivable_money ELSE change_receivable_money END)+online_pay_money ELSE 0 END),0)
+ SELECT IFNULL(SUM(CASE pay_type WHEN 1 THEN (CASE WHEN null = change_receivable_money or change_receivable_money
+ = 0 THEN receivable_money ELSE change_receivable_money END) WHEN 2 THEN (CASE WHEN null =
+ change_receivable_money or change_receivable_money = 0 THEN receivable_money ELSE change_receivable_money
+ END)+online_pay_money ELSE 0 END),0)
FROM t_order
WHERE del_flag = 0 AND order_status = 3
<if test="param.shopId != null and param.shopId != ''">
@@ -1114,23 +1169,23 @@
</select>
<select id="getReorderPerson" resultType="java.lang.Integer">
- SELECT COUNT(temp.userId) FROM
- (SELECT toc.user_id userId,
- COUNT(DISTINCT toc.order_id) orderCount
- FROM t_order toc
- INNER JOIN t_order_goods tog ON toc.order_id = tog.order_id
- WHERE toc.del_flag = 0 AND tog.goods_type = 1 AND toc.order_status = 3
- <if test="param.shopId != null and param.shopId != ''">
- AND toc.shop_id = #{param.shopId}
- </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 toc.user_id) AS temp
- WHERE temp.orderCount > 1
+ SELECT COUNT(temp.userId) FROM
+ (SELECT toc.user_id userId,
+ COUNT(DISTINCT toc.order_id) orderCount
+ FROM t_order toc
+ INNER JOIN t_order_goods tog ON toc.order_id = tog.order_id
+ WHERE toc.del_flag = 0 AND tog.goods_type = 1 AND toc.order_status = 3
+ <if test="param.shopId != null and param.shopId != ''">
+ AND toc.shop_id = #{param.shopId}
+ </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 toc.user_id) AS temp
+ WHERE temp.orderCount > 1
<!--SELECT COUNT(DISTINCT user_id)
FROM (SELECT t1.user_id, t1.pay_time, t2.pay_time as last_pay_time
FROM t_order t1
@@ -1207,10 +1262,14 @@
IFNULL(SUM(CASE WHEN order_from = 6 THEN receivable_money ELSE 0 END),0) douyinAmount,
IFNULL(SUM(CASE WHEN order_from = 7 THEN 1 ELSE 0 END),0) kuaishou,
IFNULL(SUM(CASE WHEN order_from = 7 THEN receivable_money ELSE 0 END),0) kuaishouAmount,
- IFNULL(SUM(CASE WHEN null = change_receivable_money or change_receivable_money = 0 THEN receivable_money ELSE change_receivable_money END),0) orderMoney,
- IFNULL(SUM(CASE WHEN order_from = 1 THEN (CASE WHEN null = change_receivable_money or change_receivable_money = 0 THEN receivable_money ELSE change_receivable_money END) ELSE 0 END),0) onlineMoney,
- IFNULL(SUM(CASE WHEN order_from = 3 THEN (CASE WHEN null = change_receivable_money or change_receivable_money = 0 THEN receivable_money ELSE change_receivable_money END) ELSE 0 END),0) offlineMoney,
- IFNULL(SUM(CASE WHEN order_from = 2 THEN (CASE WHEN null = change_receivable_money or change_receivable_money = 0 THEN receivable_money ELSE change_receivable_money END) ELSE 0 END),0) activityMoney
+ IFNULL(SUM(CASE WHEN null = change_receivable_money or change_receivable_money = 0 THEN receivable_money ELSE
+ change_receivable_money END),0) orderMoney,
+ IFNULL(SUM(CASE WHEN order_from = 1 THEN (CASE WHEN null = change_receivable_money or change_receivable_money =
+ 0 THEN receivable_money ELSE change_receivable_money END) ELSE 0 END),0) onlineMoney,
+ IFNULL(SUM(CASE WHEN order_from = 3 THEN (CASE WHEN null = change_receivable_money or change_receivable_money =
+ 0 THEN receivable_money ELSE change_receivable_money END) ELSE 0 END),0) offlineMoney,
+ IFNULL(SUM(CASE WHEN order_from = 2 THEN (CASE WHEN null = change_receivable_money or change_receivable_money =
+ 0 THEN receivable_money ELSE change_receivable_money END) ELSE 0 END),0) activityMoney
FROM t_order WHERE del_flag = 0 AND order_status = 3
<if test="param.shopIdList != null and param.shopIdList.size() > 0">
AND shop_id IN
@@ -1255,7 +1314,10 @@
SELECT
DATE_FORMAT(create_time, '%Y-%m-%d') AS mapKey,
COUNT(order_id) AS mapValueFirst,
- IFNULL(SUM(CASE WHEN pay_type = 1 THEN (CASE WHEN null = change_receivable_money or change_receivable_money = 0 THEN receivable_money ELSE change_receivable_money END) WHEN pay_type = 2 THEN (CASE WHEN null = change_receivable_money or change_receivable_money = 0 THEN receivable_money ELSE change_receivable_money END) + online_pay_money ELSE 0 END),0) AS mapValueSecond
+ IFNULL(SUM(CASE WHEN pay_type = 1 THEN (CASE WHEN null = change_receivable_money or change_receivable_money = 0
+ THEN receivable_money ELSE change_receivable_money END) WHEN pay_type = 2 THEN (CASE WHEN null =
+ change_receivable_money or change_receivable_money = 0 THEN receivable_money ELSE change_receivable_money END) +
+ online_pay_money ELSE 0 END),0) AS mapValueSecond
FROM
t_order
WHERE del_flag = 0 AND order_status = 3
@@ -1307,7 +1369,7 @@
WHEN toc.order_from IN (5, 6, 7) THEN toc.receivable_money
ELSE tog.goods_receivable_money
END
- ), 0) AS mapValue
+ ), 0) 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 order_status = 3
@@ -1335,7 +1397,10 @@
WHEN 3 THEN "线下订单"
WHEN 4 then "抽奖订单"
END mapKey,
- IFNULL(SUM(CASE WHEN pay_type = 1 THEN (CASE WHEN null = change_receivable_money or change_receivable_money = 0 THEN receivable_money ELSE change_receivable_money END) WHEN pay_type = 2 THEN (CASE WHEN null = change_receivable_money or change_receivable_money = 0 THEN receivable_money ELSE change_receivable_money END) + online_pay_money ELSE 0 END),0) mapValue
+ IFNULL(SUM(CASE WHEN pay_type = 1 THEN (CASE WHEN null = change_receivable_money or change_receivable_money = 0
+ THEN receivable_money ELSE change_receivable_money END) WHEN pay_type = 2 THEN (CASE WHEN null =
+ change_receivable_money or change_receivable_money = 0 THEN receivable_money ELSE change_receivable_money END) +
+ online_pay_money ELSE 0 END),0) mapValue
FROM t_order toc
WHERE toc.del_flag = 0 AND order_status = 3
<if test="param.shopIdList != null and param.shopIdList.size() > 0">
@@ -1357,7 +1422,10 @@
<select id="getPlTotalActivityTotal" resultType="com.ruoyi.order.domain.vo.MgtPlTotalActivityTotalVo">
SELECT
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 (CASE WHEN null = change_receivable_money or change_receivable_money = 0 THEN receivable_money ELSE change_receivable_money END) WHEN order_status = 3 AND pay_type = 2 THEN (CASE WHEN null = change_receivable_money or change_receivable_money = 0 THEN receivable_money ELSE change_receivable_money END) + online_pay_money ELSE 0 END),0) orderMoney,
+ IFNULL(SUM(CASE WHEN order_status = 3 AND pay_type = 1 THEN (CASE WHEN null = change_receivable_money or
+ change_receivable_money = 0 THEN receivable_money ELSE change_receivable_money END) WHEN order_status = 3 AND
+ pay_type = 2 THEN (CASE WHEN null = change_receivable_money or change_receivable_money = 0 THEN receivable_money
+ ELSE change_receivable_money END) + online_pay_money ELSE 0 END),0) orderMoney,
COUNT(DISTINCT user_id) orderJoinPerson
FROM t_order WHERE del_flag = 0 AND order_from = 2
<if test="param.shopId != null and param.shopId != ''">
@@ -1417,7 +1485,8 @@
COUNT(DISTINCT CASE WHEN tog.goods_type = 2 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 AND toc.shop_id = #{param.shopId}
+ WHERE toc.del_flag = 0 AND toc.order_from = 2 AND toc.order_status = 3 AND toc.new_member_flag = 1 AND
+ toc.shop_id = #{param.shopId}
<if test="param.startDate!=null and param.startDate!=''">
AND Date(toc.create_time) >= #{param.startDate}
</if>
@@ -1460,7 +1529,10 @@
SELECT
DATE_FORMAT(create_time, '%Y-%m-%d') AS mapKey,
COUNT(DISTINCT order_id) AS mapValueFirst,
- IFNULL(SUM(CASE WHEN pay_type = 1 THEN (CASE WHEN null = change_receivable_money or change_receivable_money = 0 THEN receivable_money ELSE change_receivable_money END) WHEN pay_type = 2 THEN (CASE WHEN null = change_receivable_money or change_receivable_money = 0 THEN receivable_money ELSE change_receivable_money END) + online_pay_money ELSE 0 END),0) AS mapValueSecond
+ IFNULL(SUM(CASE WHEN pay_type = 1 THEN (CASE WHEN null = change_receivable_money or change_receivable_money = 0
+ THEN receivable_money ELSE change_receivable_money END) WHEN pay_type = 2 THEN (CASE WHEN null =
+ change_receivable_money or change_receivable_money = 0 THEN receivable_money ELSE change_receivable_money END) +
+ online_pay_money ELSE 0 END),0) AS mapValueSecond
FROM t_order
WHERE del_flag = 0 AND order_from = 2 AND order_status = 3
<if test="param.activityId!=null and param.activityId!=''">
@@ -1526,8 +1598,8 @@
<select id="listPlTotalShopActivityRank" resultType="com.ruoyi.system.api.domain.vo.MgtMapIntTotalVo">
SELECT
- shop_id AS mapKey,
- COUNT(DISTINCT activity_id) AS mapValue
+ shop_id AS mapKey,
+ COUNT(DISTINCT activity_id) AS mapValue
FROM t_order
WHERE del_flag = 0 AND order_from = 2 AND order_status = 3
<if test="param.activityId!=null and param.activityId!=''">
@@ -1552,7 +1624,10 @@
<select id="listPlTotalShopActivitySalesRank" resultType="com.ruoyi.system.api.domain.vo.MgtMapBigTotalVo">
SELECT
shop_id AS mapKey,
- IFNULL(SUM(CASE WHEN pay_type = 1 THEN (CASE WHEN null = change_receivable_money or change_receivable_money = 0 THEN receivable_money ELSE change_receivable_money END) WHEN pay_type = 2 THEN (CASE WHEN null = change_receivable_money or change_receivable_money = 0 THEN receivable_money ELSE change_receivable_money END)+online_pay_money ELSE 0 END),0) AS mapValue
+ IFNULL(SUM(CASE WHEN pay_type = 1 THEN (CASE WHEN null = change_receivable_money or change_receivable_money = 0
+ THEN receivable_money ELSE change_receivable_money END) WHEN pay_type = 2 THEN (CASE WHEN null =
+ change_receivable_money or change_receivable_money = 0 THEN receivable_money ELSE change_receivable_money
+ END)+online_pay_money ELSE 0 END),0) AS mapValue
FROM t_order
WHERE del_flag = 0 AND order_from = 2 AND order_status = 3
<if test="param.activityId!=null and param.activityId!=''">
@@ -1580,7 +1655,10 @@
<select id="listPlTotalActivitySalesRank" resultType="com.ruoyi.system.api.domain.vo.MgtMapBigTotalVo">
SELECT
activity_name AS mapKey,
- IFNULL(SUM(CASE WHEN pay_type = 1 THEN (CASE WHEN null = change_receivable_money or change_receivable_money = 0 THEN receivable_money ELSE change_receivable_money END) WHEN pay_type = 2 THEN (CASE WHEN null = change_receivable_money or change_receivable_money = 0 THEN receivable_money ELSE change_receivable_money END)+online_pay_money ELSE 0 END),0) AS mapValue
+ IFNULL(SUM(CASE WHEN pay_type = 1 THEN (CASE WHEN null = change_receivable_money or change_receivable_money = 0
+ THEN receivable_money ELSE change_receivable_money END) WHEN pay_type = 2 THEN (CASE WHEN null =
+ change_receivable_money or change_receivable_money = 0 THEN receivable_money ELSE change_receivable_money
+ END)+online_pay_money ELSE 0 END),0) AS mapValue
FROM t_order
WHERE del_flag = 0 AND order_from = 2 AND order_status = 3
<if test="param.activityId!=null and param.activityId!=''">
@@ -1606,11 +1684,20 @@
</select>
<select id="getMerHomeShopTotalVo" resultType="com.ruoyi.system.api.domain.vo.MerHomeShopTotalVo">
+
+ SELECT sum(aa.unHandleOrder) as unHandleOrder,sum(aa.shopTurnover) as shopTurnover FROM (
SELECT
- IFNULL(SUM(CASE WHEN order_status = 2 AND order_from = 1 THEN 1 ELSE 0 END),0) unHandleOrder,
- IFNULL(SUM(CASE WHEN order_status = 3 AND pay_type = 1 THEN (CASE WHEN null = change_receivable_money or change_receivable_money = 0 THEN receivable_money ELSE change_receivable_money END) WHEN order_status = 3 AND pay_type = 2 THEN (CASE WHEN null = change_receivable_money or change_receivable_money = 0 THEN receivable_money ELSE change_receivable_money END)+online_pay_money ELSE 0 END),0) shopTurnover
+ IFNULL(SUM(CASE WHEN order_status = 2 THEN 1 ELSE 0 END),0) unHandleOrder,
+ 0 AS shopTurnover
FROM t_order
WHERE del_flag = 0 AND shop_id = #{shopId} AND order_status IN (2,3)
+ UNION ALL
+ SELECT
+ 0,
+ IFNULL(SUM(CASE WHEN order_status = 3 AND pay_type = 1 THEN (CASE WHEN null = change_receivable_money or change_receivable_money = 0 THEN receivable_money ELSE change_receivable_money END) WHEN order_status = 3 AND pay_type = 2 THEN (CASE WHEN null = change_receivable_money or change_receivable_money = 0 THEN receivable_money ELSE change_receivable_money END)+online_pay_money ELSE 0 END),0) shopTurnover
+ FROM t_order
+ WHERE del_flag = 0 AND shop_id = #{shopId} AND order_status IN (2,3) and DATE_FORMAT(create_time, '%Y-%m') = DATE_FORMAT(now(), '%Y-%m')
+ ) AS aa
</select>
<select id="getMerHomeShopServiceTotalVo" resultType="com.ruoyi.system.api.domain.vo.MerHomeShopTotalVo">
@@ -1648,10 +1735,14 @@
IFNULL(SUM(CASE WHEN order_from in (1,4,5,6,7) THEN 1 ELSE 0 END),0) onlineTotal,
IFNULL(SUM(CASE WHEN order_from = 3 THEN 1 ELSE 0 END),0) offlineTotal,
IFNULL(SUM(CASE WHEN order_from = 2 THEN 1 ELSE 0 END),0) activityTotal,
- IFNULL(SUM(CASE WHEN null = change_receivable_money or change_receivable_money = 0 THEN receivable_money ELSE change_receivable_money END),0) orderMoney,
- IFNULL(SUM(CASE WHEN order_from in (1,4,5,6,7) THEN (CASE WHEN null = change_receivable_money or change_receivable_money = 0 THEN receivable_money ELSE change_receivable_money END) ELSE 0 END),0) onlineMoney,
- IFNULL(SUM(CASE WHEN order_from = 3 THEN (CASE WHEN null = change_receivable_money or change_receivable_money = 0 THEN receivable_money ELSE change_receivable_money END) ELSE 0 END),0) offlineMoney,
- IFNULL(SUM(CASE WHEN order_from = 2 THEN (CASE WHEN null = change_receivable_money or change_receivable_money = 0 THEN receivable_money ELSE change_receivable_money END) ELSE 0 END),0) activityMoney
+ IFNULL(SUM(CASE WHEN null = change_receivable_money or change_receivable_money = 0 THEN receivable_money ELSE
+ change_receivable_money END),0) orderMoney,
+ IFNULL(SUM(CASE WHEN order_from in (1,4,5,6,7) THEN (CASE WHEN null = change_receivable_money or
+ change_receivable_money = 0 THEN receivable_money ELSE change_receivable_money END) ELSE 0 END),0) onlineMoney,
+ IFNULL(SUM(CASE WHEN order_from = 3 THEN (CASE WHEN null = change_receivable_money or change_receivable_money =
+ 0 THEN receivable_money ELSE change_receivable_money END) ELSE 0 END),0) offlineMoney,
+ IFNULL(SUM(CASE WHEN order_from = 2 THEN (CASE WHEN null = change_receivable_money or change_receivable_money =
+ 0 THEN receivable_money ELSE change_receivable_money END) ELSE 0 END),0) activityMoney
FROM t_order WHERE del_flag = 0 AND order_status = 3
<if test="param.shopId != null and param.shopId != ''">
AND shop_id = #{param.shopId}
@@ -1673,9 +1764,9 @@
<select id="listMerOrderDistributionTotal" resultType="com.ruoyi.order.domain.vo.MgtMapTotalPlusVo">
SELECT
- DATE(toc.create_time) AS mapKey,
- COUNT(DISTINCT tog.order_id) AS mapValueFirst,
- SUM(tog.goods_receivable_money) AS mapValueSecond
+ DATE(toc.create_time) AS mapKey,
+ COUNT(DISTINCT tog.order_id) AS mapValueFirst,
+ SUM(tog.goods_receivable_money) AS mapValueSecond
FROM t_order toc
INNER JOIN t_order_goods tog ON toc.order_id = tog.order_id
WHERE toc.del_flag = 0 AND toc.order_status = 3 AND tog.del_flag = 0
@@ -1790,13 +1881,20 @@
<select id="totalMerOrder" resultType="com.ruoyi.order.domain.vo.MerTotalOrderVo">
SELECT
COUNT(toc.order_id) orderTotal,
- IFNULL(SUM(CASE toc.pay_type WHEN 1 THEN toc.receivable_money WHEN 2 THEN order_money-coupon_money-online_pay_money END),0) receivableMoney,
- IFNULL(SUM(CASE WHEN null = toc.change_receivable_money or toc.change_receivable_money = 0 THEN toc.receivable_money ELSE toc.change_receivable_money END),0) relReceiveMoney,
- IFNULL(SUM(IF(toc.order_from = 3, toc.offline_pay_money, IF(toc.order_from != 3 AND toc.pay_type = 1, toc.online_pay_money, IFNULL(toc.offline_pay_money,0)))),0) relPayMoney,
- IFNULL(SUM((CASE WHEN null = toc.change_receivable_money or toc.change_receivable_money = 0 THEN toc.receivable_money ELSE toc.change_receivable_money END)-IFNULL(IF(toc.order_from = 3, toc.offline_pay_money, IF(toc.order_from != 3 AND toc.pay_type = 1, toc.online_pay_money, IFNULL(toc.offline_pay_money,0))),0)),0) unpaidMoney,
+ IFNULL(SUM(CASE toc.pay_type WHEN 1 THEN toc.receivable_money WHEN 2 THEN
+ order_money-coupon_money-online_pay_money END),0) receivableMoney,
+ IFNULL(SUM(CASE WHEN null = toc.change_receivable_money or toc.change_receivable_money = 0 THEN
+ toc.receivable_money ELSE toc.change_receivable_money END),0) relReceiveMoney,
+ IFNULL(SUM(IF(toc.order_from = 3, toc.offline_pay_money, IF(toc.order_from != 3 AND toc.pay_type = 1,
+ toc.online_pay_money, IFNULL(toc.offline_pay_money,0)))),0) relPayMoney,
+ IFNULL(SUM((CASE WHEN null = toc.change_receivable_money or toc.change_receivable_money = 0 THEN
+ toc.receivable_money ELSE toc.change_receivable_money END)-IFNULL(IF(toc.order_from = 3, toc.offline_pay_money,
+ IF(toc.order_from != 3 AND toc.pay_type = 1, toc.online_pay_money, IFNULL(toc.offline_pay_money,0))),0)),0)
+ unpaidMoney,
IFNULL(SUM(CASE toc.pay_type WHEN 2 THEN toc.online_pay_money ELSE 0 END),0) receivableDeposit
FROM t_order toc
- WHERE toc.del_flag = 0 AND toc.shop_id = #{param.shopId} and if(toc.order_from = 1, 1 = 1, toc.order_status = 3)<!--临时增加过滤-->
+ WHERE toc.del_flag = 0 AND toc.shop_id = #{param.shopId} and if(toc.order_from = 1, 1 = 1, toc.order_status =
+ 3)<!--临时增加过滤-->
<if test="param.memberUserId != null and param.memberUserId != ''">
AND toc.user_id = #{param.memberUserId}
</if>
@@ -1820,7 +1918,8 @@
</if>
<if test="param.keyword != null and param.keyword != ''">
AND (toc.order_no LIKE CONCAT('%',#{param.keyword},'%') OR
- toc.order_id IN (SELECT DISTINCT tog.order_id FROM t_order_goods tog WHERE tog.goods_name LIKE CONCAT('%',#{param.keyword},'%'))
+ toc.order_id IN (SELECT DISTINCT tog.order_id FROM t_order_goods tog WHERE tog.goods_name LIKE
+ CONCAT('%',#{param.keyword},'%'))
<if test="param.memberUserIdList != null and param.memberUserIdList.size()>0">
OR toc.user_id IN
<foreach collection="param.memberUserIdList" item="item" open="(" separator="," close=")">
@@ -1839,9 +1938,12 @@
toc.user_id userId,
toc.order_money orderMoney,
toc.discount_money discountMoney,
- CASE toc.pay_type WHEN 1 THEN toc.order_money-toc.coupon_money WHEN 2 THEN toc.order_money-toc.coupon_money-toc.online_pay_money END receivableMoney,
- (CASE WHEN null = toc.change_receivable_money or toc.change_receivable_money = 0 THEN toc.receivable_money ELSE toc.change_receivable_money END) payMoney,
- CASE toc.order_from WHEN 1 THEN '小程序' WHEN 2 THEN CONCAT('秒杀活动(',toc.activity_name,')') WHEN 3 THEN '线下创建' END orderFrom,
+ CASE toc.pay_type WHEN 1 THEN toc.order_money-toc.coupon_money WHEN 2 THEN
+ toc.order_money-toc.coupon_money-toc.online_pay_money END receivableMoney,
+ (CASE WHEN null = toc.change_receivable_money or toc.change_receivable_money = 0 THEN toc.receivable_money ELSE
+ toc.change_receivable_money END) payMoney,
+ CASE toc.order_from WHEN 1 THEN '小程序' WHEN 2 THEN CONCAT('秒杀活动(',toc.activity_name,')') WHEN 3 THEN '线下创建' END
+ orderFrom,
CASE toc.order_status
WHEN 0 THEN "已取消"
WHEN 1 THEN "待支付"
@@ -1880,9 +1982,12 @@
<select id="getStaffActivityOrderTotal" resultType="com.ruoyi.order.domain.vo.StaffActivityOrderTotalVo">
SELECT
- COUNT(DISTINCT toc.order_id) orderTotal,
- IFNULL(SUM(CASE WHEN toc.pay_type = 1 THEN (CASE WHEN null = toc.change_receivable_money or toc.change_receivable_money = 0 THEN toc.receivable_money ELSE toc.change_receivable_money END) WHEN toc.pay_type = 2 THEN (CASE WHEN null = toc.change_receivable_money or toc.change_receivable_money = 0 THEN toc.receivable_money ELSE toc.change_receivable_money END) + toc.online_pay_money ELSE 0 END),0) orderMoney,
- COUNT(DISTINCT toc.user_id) memberTotal
+ COUNT(DISTINCT toc.order_id) orderTotal,
+ IFNULL(SUM(CASE WHEN toc.pay_type = 1 THEN (CASE WHEN null = toc.change_receivable_money or
+ toc.change_receivable_money = 0 THEN toc.receivable_money ELSE toc.change_receivable_money END) WHEN
+ toc.pay_type = 2 THEN (CASE WHEN null = toc.change_receivable_money or toc.change_receivable_money = 0 THEN
+ toc.receivable_money ELSE toc.change_receivable_money END) + toc.online_pay_money ELSE 0 END),0) orderMoney,
+ COUNT(DISTINCT toc.user_id) memberTotal
FROM t_order toc
WHERE toc.del_flag = 0 AND toc.shop_id = #{param.shopId} AND toc.order_from = 2 AND toc.order_status = 3
<if test="param.activityId !=null and param.activityId != ''">
@@ -1969,8 +2074,8 @@
<select id="getStaffActivityGetMemberTotal" resultType="com.ruoyi.system.api.domain.vo.MgtMapIntTotalVo">
SELECT
- DATE(toc.create_time) AS mapKey,
- COUNT(DISTINCT toc.user_id) AS mapValue
+ 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 = 3 AND toc.shop_id = #{param.shopId}
@@ -2055,7 +2160,7 @@
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_status IN (2,3)
+ WHERE toc.del_flag = 0 AND toc.order_from = 2 AND toc.order_status IN (2,3)
<if test="param.activityId!=null and param.activityId!=''">
AND toc.activity_id = #{param.activityId}
</if>
@@ -2089,7 +2194,7 @@
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_status IN (2,3)
+ WHERE toc.del_flag = 0 AND toc.order_from = 2 AND toc.order_status IN (2,3)
<if test="param.shopId != null and param.shopId != ''">
AND toc.shop_id = #{param.shopId}
</if>
@@ -2119,7 +2224,8 @@
SELECT
COUNT(DISTINCT toc.order_id) orderTotal,
IFNULL(SUM(toc.order_money),0) totalOrderMoney,
- IFNULL(SUM(CASE WHEN null = toc.change_receivable_money or toc.change_receivable_money = 0 THEN toc.receivable_money ELSE toc.change_receivable_money END),0) totalReceiveMoney,
+ IFNULL(SUM(CASE WHEN null = toc.change_receivable_money or toc.change_receivable_money = 0 THEN
+ toc.receivable_money ELSE toc.change_receivable_money END),0) totalReceiveMoney,
IFNULL(SUM(CASE WHEN toc.pay_type = 2 THEN toc.online_pay_money ELSE 0 END),0) totalDepositMoney
FROM t_order toc
WHERE toc.del_flag = 0
@@ -2195,7 +2301,8 @@
<select id="shopSalesRank" resultType="com.ruoyi.system.api.domain.vo.MgtMapBigTotalVo">
SELECT
shop_id mapKey,
- SUM(CASE WHEN null = change_receivable_money or change_receivable_money = 0 THEN receivable_money ELSE change_receivable_money END) mapValue
+ SUM(CASE WHEN null = change_receivable_money or change_receivable_money = 0 THEN receivable_money ELSE
+ change_receivable_money END) mapValue
FROM t_order
WHERE del_flag = 0 AND order_status = 3 AND shop_id IN
<foreach collection="shopIdList" item="item" open="(" separator="," close=")">
@@ -2207,7 +2314,10 @@
<select id="getAgencyTotalVo" resultType="com.ruoyi.system.api.domain.vo.AgencyTotalVo">
SELECT
- IFNULL(SUM(CASE pay_type WHEN 1 THEN (CASE WHEN null = change_receivable_money or change_receivable_money = 0 THEN receivable_money ELSE change_receivable_money END) WHEN 2 THEN (CASE WHEN null = change_receivable_money or change_receivable_money = 0 THEN receivable_money ELSE change_receivable_money END)+online_pay_money ELSE 0 END),0) areaTurnover
+ IFNULL(SUM(CASE pay_type WHEN 1 THEN (CASE WHEN null = change_receivable_money or change_receivable_money = 0
+ THEN receivable_money ELSE change_receivable_money END) WHEN 2 THEN (CASE WHEN null = change_receivable_money or
+ change_receivable_money = 0 THEN receivable_money ELSE change_receivable_money END)+online_pay_money ELSE 0
+ END),0) areaTurnover
FROM t_order
WHERE del_flag = 0 AND order_status = 3 AND shop_id IN
<foreach collection="shopIdList" item="item" open="(" separator="," close=")">
@@ -2243,9 +2353,9 @@
<if test="param.orderFrom == null or param.orderFrom == '' ">
and toc.order_from in (5,6,7)
</if>
- <if test="null != param.orderFrom and param.orderFrom!='' ">
- and toc.order_from = #{param.orderFrom}
- </if>
+ <if test="null != param.orderFrom and param.orderFrom!='' ">
+ and toc.order_from = #{param.orderFrom}
+ </if>
<if test="param.shopId != null and param.shopId != ''">
AND toc.shop_id = #{param.shopId}
</if>
@@ -2253,7 +2363,8 @@
AND FIND_IN_SET(toc.shop_id, #{param.shopIds})
</if>
<if test="param.keyword != null and param.keyword != ''">
- AND (toc.order_no LIKE CONCAT('%',#{param.keyword},'%') OR toc.activity_name LIKE CONCAT('%',#{param.keyword},'%') OR FIND_IN_SET(toc.user_id, #{param.userIds}) > 0)
+ AND (toc.order_no LIKE CONCAT('%',#{param.keyword},'%') OR toc.activity_name LIKE
+ CONCAT('%',#{param.keyword},'%') OR FIND_IN_SET(toc.user_id, #{param.userIds}) > 0)
</if>
<if test="param.createTimeBegin != null and param.createTimeBegin != ''">
AND toc.create_time >= #{param.createTimeBegin}
@@ -2279,7 +2390,7 @@
IFNULL(SUM(CASE WHEN order_from = 7 THEN 1 ELSE 0 END), 0) AS kuaishou,
IFNULL(SUM(CASE WHEN order_from = 8 THEN 1 ELSE 0 END), 0) AS xiaoeyun,
-- 订单金额统计（取order_money）
- IFNULL(SUM(receivable_money),0) AS orderTotalMoney,
+ IFNULL(SUM(receivable_money),0) AS orderTotalMoney,
IFNULL(SUM(CASE WHEN order_from = 5 THEN receivable_money ELSE 0 END),0) meituanMoney,
IFNULL(SUM(CASE WHEN order_from = 6 THEN receivable_money ELSE 0 END),0) douyinMoney,
IFNULL(SUM(CASE WHEN order_from = 7 THEN receivable_money ELSE 0 END),0) kuaishouMoney,
@@ -2287,8 +2398,8 @@
FROM
t_order
WHERE
- del_flag = 0 -- 未删除
- AND order_from IN (5, 6, 7, 8) -- 仅统计三方平台订单
+ del_flag = 0 -- 未删除
+ AND order_from IN (5, 6, 7, 8) -- 仅统计三方平台订单
-- 可选条件：按商户筛选
<if test="param.shopIdList != null and param.shopIdList.size() > 0">
AND shop_id IN
@@ -2305,7 +2416,8 @@
COUNT(temp.order_id) thirdOrderTotal,
IFNULL(SUM( temp.receivable_money ),0) thirdOrderMoneyTotal,
IFNULL(SUM(temp.pay_money),0) thirdPayMoneyTotal,
- IFNULL(SUM(CASE WHEN (temp.receivable_money ) > temp.pay_money THEN ( temp.receivable_money ) - temp.pay_money ELSE 0 END),0)thirdUnPayMoneyTotal
+ IFNULL(SUM(CASE WHEN (temp.receivable_money ) > temp.pay_money THEN ( temp.receivable_money ) -
+ temp.pay_money ELSE 0 END),0)thirdUnPayMoneyTotal
FROM
(SELECT toc.order_id,toc.receivable_money,toc.pay_money
FROM t_order toc
@@ -2321,13 +2433,15 @@
<!--AND toc.shop_id = #{param.shopId}-->
<!--</if>-->
<if test="param.shopId != null and param.shopId != ''">
- AND toc.shop_id = #{param.shopId} AND (toc.unbinding_flag = 0 OR (toc.unbinding_flag = 1 AND toc.order_from in (5,6,7)) OR (toc.unbinding_flag = 1 AND toc.order_from in (5,6,7) AND toc.order_status = 3))
+ AND toc.shop_id = #{param.shopId} AND (toc.unbinding_flag = 0 OR (toc.unbinding_flag = 1 AND toc.order_from
+ in (5,6,7)) OR (toc.unbinding_flag = 1 AND toc.order_from in (5,6,7) AND toc.order_status = 3))
</if>
<if test="param.shopIds != null and param.shopIds != ''">
and toc.order_status = 3 AND FIND_IN_SET(toc.shop_id, #{param.shopIds}) > 0
</if>
<if test="param.keyword != null and param.keyword != ''">
- AND (toc.order_no LIKE CONCAT('%',#{param.keyword},'%') OR toc.activity_name LIKE CONCAT('%',#{param.keyword},'%') OR FIND_IN_SET(toc.user_id, #{param.userIds}) > 0)
+ AND (toc.order_no LIKE CONCAT('%',#{param.keyword},'%') OR toc.activity_name LIKE
+ CONCAT('%',#{param.keyword},'%') OR FIND_IN_SET(toc.user_id, #{param.userIds}) > 0)
</if>
<if test="param.verifyStartTime != null and param.verifyStartTime != ''">
AND Date(toc.use_time) >= #{param.verifyStartTime}
diff --git a/ruoyi-modules/ruoyi-order/src/main/resources/mapper/order/UserServiceRecordMapper.xml b/ruoyi-modules/ruoyi-order/src/main/resources/mapper/order/UserServiceRecordMapper.xml
index 65370d3..e5b88a9 100644
--- a/ruoyi-modules/ruoyi-order/src/main/resources/mapper/order/UserServiceRecordMapper.xml
+++ b/ruoyi-modules/ruoyi-order/src/main/resources/mapper/order/UserServiceRecordMapper.xml
@@ -132,10 +132,10 @@
AND user_id = #{userId}
</select>
- <select id="countShopServicePerson" resultType="list">
+ <select id="countShopServicePerson" resultType="int">
SELECT DISTINCT user_id
FROM t_user_service_record
- WHERE shop_id = #{shopId} AND DATE (create_time) = CURDATE()
+ WHERE shop_id = #{shopId} AND DATE(create_time) = CURDATE()
</select>
</mapper>
--
Gitblit v1.7.1