From 5a8a90c095280fbd2106869ecd2bad10e01a57a6 Mon Sep 17 00:00:00 2001
From: 101captain <237651143@qq.com>
Date: 星期四, 23 十二月 2021 18:10:22 +0800
Subject: [PATCH] 12/23 大屏修改
---
springcloud_k8s_panzhihuazhihuishequ/service_community/src/main/java/com/panzhihua/service_community/dao/ComShopOrderDAO.java | 376 ++++++++++++++++++++---------------------------------
1 files changed, 144 insertions(+), 232 deletions(-)
diff --git a/springcloud_k8s_panzhihuazhihuishequ/service_community/src/main/java/com/panzhihua/service_community/dao/ComShopOrderDAO.java b/springcloud_k8s_panzhihuazhihuishequ/service_community/src/main/java/com/panzhihua/service_community/dao/ComShopOrderDAO.java
index 7c3583f..85333ab 100644
--- a/springcloud_k8s_panzhihuazhihuishequ/service_community/src/main/java/com/panzhihua/service_community/dao/ComShopOrderDAO.java
+++ b/springcloud_k8s_panzhihuazhihuishequ/service_community/src/main/java/com/panzhihua/service_community/dao/ComShopOrderDAO.java
@@ -1,19 +1,15 @@
package com.panzhihua.service_community.dao;
-import com.baomidou.mybatisplus.core.mapper.BaseMapper;
-import com.baomidou.mybatisplus.core.metadata.IPage;
-import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
-import com.panzhihua.common.model.dtos.shop.PageComOrderListDTO;
-import com.panzhihua.common.model.vos.shop.*;
-import com.panzhihua.common.model.dtos.shop.*;
-import com.panzhihua.common.model.vos.shop.*;
-import com.baomidou.mybatisplus.core.metadata.IPage;
-import com.panzhihua.common.model.dtos.PageDTO;
-import com.panzhihua.common.model.vos.shop.ComShopOrderVO;
-import com.panzhihua.service_community.model.dos.ComShopOrderDO;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
+
+import com.baomidou.mybatisplus.core.mapper.BaseMapper;
+import com.baomidou.mybatisplus.core.metadata.IPage;
+import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
+import com.panzhihua.common.model.dtos.shop.*;
+import com.panzhihua.common.model.vos.shop.*;
+import com.panzhihua.service_community.model.dos.ComShopOrderDO;
/**
* @auther cedoo
@@ -23,233 +19,149 @@
@Mapper
public interface ComShopOrderDAO extends BaseMapper<ComShopOrderDO> {
- @Select("<script> " +
- "select order_no,store_id,`status`,pay_status,receiver_id,total_amount as orderTotal,pay_amount,delivery_type,remark,create_at from com_shop_order as cso" +
- " where 1=1 and cso.delete_status = 1 and cso.user_id = #{comOrderListDTO.userId}" +
- "<if test='comOrderListDTO.status != null'>" +
- " AND cso.status = #{comOrderListDTO.status} " +
- " </if> " +
- " order by create_at desc " +
- " </script>")
- IPage<ComShopOrderPageVO> pageOrderList(Page page,@Param("comOrderListDTO") PageComOrderListDTO comOrderListDTO);
+ @Select("<script> "
+ + "select id as orderId,order_no,store_id,`status`,pay_status,receiver_id,total_amount as orderTotal,pay_amount,delivery_type,remark,create_at from com_shop_order as cso"
+ + " where 1=1 and cso.delete_status = 1 and cso.user_id = #{comOrderListDTO.userId}"
+ + "<if test='comOrderListDTO.status != null'>" + " AND cso.status = #{comOrderListDTO.status} " + " </if> "
+ + " order by create_at desc " + " </script>")
+ IPage<ComShopOrderPageVO> pageOrderList(Page page, @Param("comOrderListDTO") PageComOrderListDTO comOrderListDTO);
- @Select("<script> " +
- "SELECT distinct sr.id, sr.store_id, sr.user_id, su.phone AS userName , sr.order_no, sr.pay_no, \n" +
- " sr.wx_tarde_no, sr.status, sr.pay_status, sr.receiver_id, \n" +
- " sr.total_amount, sr.discount_amount, sr.pay_amount, sr.pay_type, \n" +
- " sr.pay_time, sr.delivery_type, sr.delivery_status, sr.logistics_company, \n" +
- " sr.logistics_no, sr.create_at, uad.name as receiver_name, uad.phone as receiver_phone, group_concat(og.goods_name) as goodsName \n" +
- " FROM com_shop_order sr LEFT JOIN com_shop_order_goods og ON sr.id = og.order_id \n" +
- " LEFT JOIN com_shop_goods_attr ga ON og.goods_attr_id = ga.id \n" +
- " LEFT JOIN sys_user su ON sr.user_id = su.user_id \n" +
- " LEFT JOIN com_shop_user_address uad ON sr.receiver_id = uad.id \n" +
- " LEFT JOIN com_shop_store ss ON sr.store_id = ss.id \n" +
- " WHERE sr.delete_status = 1" +
- "<if test='pageComShopOrderSearchDTO.userId != null'>" +
- " AND sr.user_id = #{pageComShopOrderSearchDTO.userId} " +
- " </if> " +
- "<if test='pageComShopOrderSearchDTO.createAtStart != null'>" +
- " AND #{pageComShopOrderSearchDTO.createAtStart} <![CDATA[<=]]> sr.create_at " +
- " </if> " +
- "<if test='pageComShopOrderSearchDTO.createAtEnd != null'>" +
- " AND sr.create_at <![CDATA[<=]]> #{pageComShopOrderSearchDTO.createAtEnd} " +
- " </if> " +
- "<if test='pageComShopOrderSearchDTO.orderNo != null '>" +
- " AND sr.order_no = #{pageComShopOrderSearchDTO.orderNo} " +
- " </if> " +
- "<if test='pageComShopOrderSearchDTO.userAccount != null '>" +
- " AND su.phone = #{pageComShopOrderSearchDTO.userAccount} " +
- " </if> " +
- "<if test='pageComShopOrderSearchDTO.receiverAccount != null '>" +
- " AND uad.name = #{pageComShopOrderSearchDTO.receiverAccount} " +
- " </if> " +
- "<if test='pageComShopOrderSearchDTO.receiverPhone != null '>" +
- " AND uad.phone = #{pageComShopOrderSearchDTO.receiverPhone} " +
- " </if> " +
- "<if test='pageComShopOrderSearchDTO.goodsName != null'>" +
- " AND og.goods_name like concat('%', #{pageComShopOrderSearchDTO.goodsName}, '%') " +
- " </if> " +
- "<if test='pageComShopOrderSearchDTO.status != null '>" +
- " AND sr.status = #{pageComShopOrderSearchDTO.status} " +
- " </if> " +
- "<if test='pageComShopOrderSearchDTO.storeName != null '>" +
- " AND ss.name like concat('%', #{pageComShopOrderSearchDTO.storeName}, '%') " +
- " </if> " +
- "<if test='pageComShopOrderSearchDTO.storeId != null '>" +
- " AND ss.id = #{pageComShopOrderSearchDTO.storeId} " +
- " </if> " +
- "<if test='pageComShopOrderSearchDTO.storeUserId != null '>" +
- " AND ss.sys_user_id = #{pageComShopOrderSearchDTO.storeUserId} " +
- " </if> " +
- " GROUP BY sr.id " +
- " ORDER BY sr.create_at "+
- "</script>")
- IPage<ComShopOrderSearchVO> pageOrderBy(Page page, @Param("pageComShopOrderSearchDTO") PageComShopOrderSearchDTO pageComShopOrderSearchDTO);
+ @Select("<script> "
+ + "SELECT distinct sr.id, sr.store_id, sr.user_id, su.phone AS userName , sr.order_no, sr.pay_no, \n"
+ + " sr.wx_tarde_no, sr.status, sr.pay_status, sr.receiver_id, \n"
+ + " sr.total_amount, sr.discount_amount, sr.pay_amount, sr.pay_type, \n"
+ + " sr.pay_time, sr.delivery_type, sr.delivery_status, sr.logistics_company, \n"
+ + " sr.logistics_no, sr.create_at, uad.name as receiver_name, uad.phone as receiver_phone, group_concat(og.goods_name) as goodsName, ss.name as store_name \n"
+ + " FROM com_shop_order sr LEFT JOIN com_shop_order_goods og ON sr.id = og.order_id \n"
+ + " LEFT JOIN com_shop_goods_attr ga ON og.goods_attr_id = ga.id \n"
+ + " LEFT JOIN sys_user su ON sr.user_id = su.user_id \n"
+ + " LEFT JOIN com_shop_user_address uad ON sr.receiver_id = uad.id \n"
+ + " LEFT JOIN com_shop_store ss ON sr.store_id = ss.id \n" + " WHERE sr.delete_status = 1"
+ + "<if test='pageComShopOrderSearchDTO.userId != null'>"
+ + " AND sr.user_id = #{pageComShopOrderSearchDTO.userId} " + " </if> "
+ + "<if test='pageComShopOrderSearchDTO.createAtStart != null'>"
+ + " AND #{pageComShopOrderSearchDTO.createAtStart} <![CDATA[<=]]> sr.create_at " + " </if> "
+ + "<if test='pageComShopOrderSearchDTO.createAtEnd != null'>"
+ + " AND sr.create_at <![CDATA[<=]]> #{pageComShopOrderSearchDTO.createAtEnd} " + " </if> "
+ + "<if test='pageComShopOrderSearchDTO.orderNo != null '>"
+ + " AND sr.order_no like concat('%', #{pageComShopOrderSearchDTO.orderNo}, '%') " + " </if> "
+ + "<if test='pageComShopOrderSearchDTO.userAccount != null '>"
+ + " AND su.phone like concat('%', #{pageComShopOrderSearchDTO.userAccount}, '%') " + " </if> "
+ + "<if test='pageComShopOrderSearchDTO.receiverAccount != null '>"
+ + " AND uad.name like concat('%', #{pageComShopOrderSearchDTO.receiverAccount}, '%') " + " </if> "
+ + "<if test='pageComShopOrderSearchDTO.receiverPhone != null '>"
+ + " AND uad.phone like concat('%',#{pageComShopOrderSearchDTO.receiverPhone}, '%') " + " </if> "
+ + "<if test='pageComShopOrderSearchDTO.goodsName != null'>"
+ + " AND og.goods_name like concat('%', #{pageComShopOrderSearchDTO.goodsName}, '%') " + " </if> "
+ + "<if test='pageComShopOrderSearchDTO.status != null '>"
+ + " AND sr.status = #{pageComShopOrderSearchDTO.status} " + " </if> "
+ + "<if test='pageComShopOrderSearchDTO.storeName != null '>"
+ + " AND ss.name like concat('%', #{pageComShopOrderSearchDTO.storeName}, '%') " + " </if> "
+ + "<if test='pageComShopOrderSearchDTO.storeId != null '>"
+ + " AND ss.id = #{pageComShopOrderSearchDTO.storeId} " + " </if> "
+ + "<if test='pageComShopOrderSearchDTO.storeUserId != null '>"
+ + " AND ss.sys_user_id = #{pageComShopOrderSearchDTO.storeUserId} " + " </if> " + " GROUP BY sr.id "
+ + " ORDER BY sr.create_at DESC " + "</script>")
+ IPage<ComShopOrderSearchVO> pageOrderBy(Page page,
+ @Param("pageComShopOrderSearchDTO") PageComShopOrderSearchDTO pageComShopOrderSearchDTO);
- @Select("<script> " +
- "SELECT sr.total_amount AS goods_total, GROUP_CONCAT(og.goods_name) AS goods_name , count(og.id) AS goods_count, " +
- " su.phone AS account, uad.name AS receiver, uad.phone AS receiverPhone, \n" +
- " concat(uad.province_name, uad.city_name, uad.district_name, uad.detail_address) AS receiver_address, ss.name AS storeName, sr.remark," +
- " CASE\n" +
- " WHEN sr.`status` = 1 THEN '待付款' \n" +
- " WHEN sr.`status` = 2 THEN '待发货' \n" +
- " WHEN sr.`status` = 3 THEN '待收货' \n" +
- " WHEN sr.`status` = 4 THEN '待评价' \n" +
- " WHEN sr.`status` = 5 THEN '已完成' \n" +
- " WHEN sr.`status` = 5 THEN '已取消' \n" +
- " ELSE '---' \n" +
- " END AS 'status' , " +
- " sr.create_at, " +
- " CASE WHEN sr.pay_time IS NOT NULL THEN sr.pay_time ELSE '---' END AS pay_time " +
- " FROM com_shop_order sr LEFT JOIN com_shop_order_goods og ON sr.id = og.order_id \n" +
- " LEFT JOIN com_shop_goods_attr ga ON og.goods_attr_id = ga.id \n" +
- " LEFT JOIN sys_user su ON sr.user_id = su.user_id \n" +
- " LEFT JOIN com_shop_user_address uad ON sr.receiver_id = uad.id \n" +
- " LEFT JOIN com_shop_store ss ON sr.store_id = ss.id \n" +
- " WHERE sr.delete_status = 1" +
- "<if test='comShopOrderExportDTO.userId != null'>" +
- " AND sr.user_id = #{comShopOrderExportDTO.userId} " +
- " </if> " +
- "<if test='comShopOrderExportDTO.storeUserId != null '>" +
- " AND ss.sys_user_id = #{comShopOrderExportDTO.storeUserId} " +
- " </if> " +
- "<if test='comShopOrderExportDTO.orderIds != null and comShopOrderExportDTO.orderIds.length>0 '>" +
- " AND sr.id in " +
- "<foreach collection='comShopOrderExportDTO.orderIds' item='id' index='index' open='(' close=')' separator=',' >\n" +
- " #{id}" +
- "</foreach>" +
- " </if> " +
+ @Select("<script> "
+ + "SELECT sr.total_amount AS goods_total, GROUP_CONCAT(og.goods_name separator '\n') AS goods_name , sum(og.amount) AS goods_count, "
+ + " su.phone AS account, uad.name AS receiver, uad.phone AS receiverPhone, \n"
+ + " concat(uad.province_name, uad.city_name, uad.district_name, uad.detail_address) AS receiver_address, ss.name AS storeName, sr.remark,"
+ + " CASE\n" + " WHEN sr.`status` = 1 THEN '待付款' \n" + " WHEN sr.`status` = 2 THEN '待发货' \n"
+ + " WHEN sr.`status` = 3 THEN '待收货' \n" + " WHEN sr.`status` = 4 THEN '待评价' \n"
+ + " WHEN sr.`status` = 5 THEN '已完成' \n" + " WHEN sr.`status` = 5 THEN '已取消' \n" + " ELSE '---' \n"
+ + " END AS 'status' , " + " sr.create_at, "
+ + " CASE WHEN sr.pay_time IS NOT NULL THEN sr.pay_time ELSE '---' END AS pay_time "
+ + " FROM com_shop_order sr LEFT JOIN com_shop_order_goods og ON sr.id = og.order_id \n"
+ + " LEFT JOIN com_shop_goods_attr ga ON og.goods_attr_id = ga.id \n"
+ + " LEFT JOIN sys_user su ON sr.user_id = su.user_id \n"
+ + " LEFT JOIN com_shop_user_address uad ON sr.receiver_id = uad.id \n"
+ + " LEFT JOIN com_shop_store ss ON sr.store_id = ss.id \n" + " WHERE sr.delete_status = 1"
+ + "<if test='comShopOrderExportDTO.userId != null'>" + " AND sr.user_id = #{comShopOrderExportDTO.userId} "
+ + " </if> " + "<if test='comShopOrderExportDTO.storeUserId != null '>"
+ + " AND ss.sys_user_id = #{comShopOrderExportDTO.storeUserId} " + " </if> "
+ + "<if test='comShopOrderExportDTO.orderIds != null and comShopOrderExportDTO.orderIds.length>0 '>"
+ + " AND sr.id in "
+ + "<foreach collection='comShopOrderExportDTO.orderIds' item='id' index='index' open='(' close=')' separator=',' >\n"
+ + " #{id}" + "</foreach>" + " </if> " +
- "<if test='comShopOrderExportDTO.orderIds == null or comShopOrderExportDTO.orderIds.length==0 '>" +
- "<if test='comShopOrderExportDTO.createAtStart != null '>" +
- " AND #{comShopOrderExportDTO.createAtStart} <![CDATA[<=]]> sr.create_at " +
- " </if> " +
- "<if test='comShopOrderExportDTO.createAtEnd != null'>" +
- " AND sr.create_at <![CDATA[<=]]> #{comShopOrderExportDTO.createAtEnd} " +
- " </if> " +
- "<if test='comShopOrderExportDTO.orderNo != null '>" +
- " AND sr.order_no = #{comShopOrderExportDTO.orderNo} " +
- " </if> " +
- "<if test='comShopOrderExportDTO.userAccount != null '>" +
- " AND su.phone = #{comShopOrderExportDTO.userAccount} " +
- " </if> " +
- "<if test='comShopOrderExportDTO.receiverAccount != null'>" +
- " AND uad.name = #{comShopOrderExportDTO.receiverAccount} " +
- " </if> " +
- "<if test='comShopOrderExportDTO.receiverPhone != null '>" +
- " AND uad.phone = #{comShopOrderExportDTO.receiverPhone} " +
- " </if> " +
- "<if test='comShopOrderExportDTO.goodsName != null '>" +
- " AND og.name like concat('%', #{comShopOrderExportDTO.goodsName}, '%') " +
- " </if> " +
- "<if test='comShopOrderExportDTO.status != null '>" +
- " AND sr.status = #{comShopOrderExportDTO.status} " +
- " </if> " +
- " </if> " +
- " GROUP BY sr.id ORDER BY sr.create_at "+
- "</script>")
- IPage<ExcelShopOrderDTO> selectOrderExport(Page page, @Param("comShopOrderExportDTO") ComShopOrderExportDTO comShopOrderExportDTO);
+ "<if test='comShopOrderExportDTO.orderIds == null or comShopOrderExportDTO.orderIds.length==0 '>"
+ + "<if test='comShopOrderExportDTO.createAtStart != null '>"
+ + " AND #{comShopOrderExportDTO.createAtStart} <![CDATA[<=]]> sr.create_at " + " </if> "
+ + "<if test='comShopOrderExportDTO.createAtEnd != null'>"
+ + " AND sr.create_at <![CDATA[<=]]> #{comShopOrderExportDTO.createAtEnd} " + " </if> "
+ + "<if test='comShopOrderExportDTO.orderNo != null '>" + " AND sr.order_no = #{comShopOrderExportDTO.orderNo} "
+ + " </if> " + "<if test='comShopOrderExportDTO.userAccount != null '>"
+ + " AND su.phone = #{comShopOrderExportDTO.userAccount} " + " </if> "
+ + "<if test='comShopOrderExportDTO.receiverAccount != null'>"
+ + " AND uad.name = #{comShopOrderExportDTO.receiverAccount} " + " </if> "
+ + "<if test='comShopOrderExportDTO.receiverPhone != null '>"
+ + " AND uad.phone = #{comShopOrderExportDTO.receiverPhone} " + " </if> "
+ + "<if test='comShopOrderExportDTO.goodsName != null '>"
+ + " AND og.name like concat('%', #{comShopOrderExportDTO.goodsName}, '%') " + " </if> "
+ + "<if test='comShopOrderExportDTO.status != null '>" + " AND sr.status = #{comShopOrderExportDTO.status} "
+ + " </if> " + " </if> " + " GROUP BY sr.id ORDER BY sr.create_at " + "</script>")
+ IPage<ExcelShopOrderDTO> selectOrderExport(Page page,
+ @Param("comShopOrderExportDTO") ComShopOrderExportDTO comShopOrderExportDTO);
- @Select("<script> " +
- "SELECT sr.total_amount, sr.pay_time, sr.order_no, \n" +
- " CASE WHEN sr.pay_type = 1 THEN '微信支付' ELSE '' END AS payType, \n" +
- " '收入' AS 'type' " +
- "FROM com_shop_order sr LEFT JOIN com_shop_store st ON sr.store_id = st.id \n" +
- " WHERE sr.pay_status = 2 AND sr.delete_status=1 " +
- "<if test='comShopFundsExportDTO.userId != null'>" +
- " AND st.sys_user_id = #{comShopFundsExportDTO.userId} " +
- " </if> " +
- "<if test='comShopFundsExportDTO.orderIds != null and comShopFundsExportDTO.orderIds.length>0 '>" +
- " AND sr.id in " +
- "<foreach collection='comShopFundsExportDTO.orderIds' item='id' index='index' open='(' close=')' separator=',' >\n" +
- " #{id}" +
- "</foreach>" +
- " </if> " +
- "<if test='comShopFundsExportDTO.orderIds == null or comShopFundsExportDTO.orderIds.length==0 '>" +
- "<if test='comShopFundsExportDTO.payTimeStart != null'>" +
- " AND #{comShopFundsExportDTO.payTimeStart} <![CDATA[<=]]> sr.pay_time " +
- " </if> " +
- "<if test='comShopFundsExportDTO.payTimeEnd != null'>" +
- " AND sr.pay_time <![CDATA[<=]]> #{comShopFundsExportDTO.payTimeEnd} " +
- " </if> " +
- "<if test='comShopFundsExportDTO.orderNo != null '>" +
- " AND sr.order_no = #{comShopFundsExportDTO.orderNo} " +
- " </if> " +
- " </if> " +
- " GROUP BY sr.id ORDER BY sr.create_at "+
- "</script>")
- IPage<ExcelShopFundsDTO> selectFundsExport(Page page, @Param("comShopFundsExportDTO") ComShopFundsExportDTO comShopFundsExportDTO);
+ @Select("<script> " + "SELECT sr.total_amount, sr.pay_time, sr.order_no, \n"
+ + " CASE WHEN sr.pay_type = 1 THEN '微信支付' ELSE '' END AS payType, \n" + " '收入' AS 'type' "
+ + "FROM com_shop_order sr LEFT JOIN com_shop_store st ON sr.store_id = st.id \n"
+ + " WHERE sr.pay_status = 2 AND sr.delete_status=1 " + "<if test='comShopFundsExportDTO.userId != null'>"
+ + " AND st.sys_user_id = #{comShopFundsExportDTO.userId} " + " </if> "
+ + "<if test='comShopFundsExportDTO.orderIds != null and comShopFundsExportDTO.orderIds.length>0 '>"
+ + " AND sr.id in "
+ + "<foreach collection='comShopFundsExportDTO.orderIds' item='id' index='index' open='(' close=')' separator=',' >\n"
+ + " #{id}" + "</foreach>" + " </if> "
+ + "<if test='comShopFundsExportDTO.orderIds == null or comShopFundsExportDTO.orderIds.length==0 '>"
+ + "<if test='comShopFundsExportDTO.payTimeStart != null'>"
+ + " AND #{comShopFundsExportDTO.payTimeStart} <![CDATA[<=]]> sr.pay_time " + " </if> "
+ + "<if test='comShopFundsExportDTO.payTimeEnd != null'>"
+ + " AND sr.pay_time <![CDATA[<=]]> #{comShopFundsExportDTO.payTimeEnd} " + " </if> "
+ + "<if test='comShopFundsExportDTO.orderNo != null '>" + " AND sr.order_no = #{comShopFundsExportDTO.orderNo} "
+ + " </if> " + " </if> " + " GROUP BY sr.id ORDER BY sr.create_at DESC " + "</script>")
+ IPage<ExcelShopFundsDTO> selectFundsExport(Page page,
+ @Param("comShopFundsExportDTO") ComShopFundsExportDTO comShopFundsExportDTO);
- @Select("<script> " +
- " SELECT sys_user_id,\n" +
- " COALESCE(SUM(CASE WHEN abt.days = DATE_FORMAT(NOW(),'%Y%m%d') THEN abt.t END), 0) AS todayTotal,\n" +
- " COALESCE(SUM(CASE WHEN abt.days = DATE_FORMAT(NOW(),'%Y%u') THEN abt.t END), 0) AS weekTotal,\n" +
- " COALESCE(SUM(CASE WHEN abt.days = DATE_FORMAT(NOW(),'%Y%m') THEN abt.t END), 0) AS monthTotal\n" +
- "FROM (\n" +
- "SELECT ss.sys_user_id, sum(sr.pay_amount) AS t , DATE_FORMAT(sr.pay_time,'%Y%m%d') days\n" +
- "FROM com_shop_order sr LEFT JOIN com_shop_store ss ON sr.store_id = ss.id\n" +
- "WHERE sr.pay_status = 2 AND DATE_FORMAT(sr.pay_time,'%Y%m%d') = DATE_FORMAT(NOW(),'%Y%m%d')\n" +
- "<if test='userId!=null'>" +
- " and ss.sys_user_id = #{userId}\n" +
- "</if>" +
- "GROUP BY days\n" +
- "UNION \n" +
- "SELECT ss.sys_user_id, sum(sr.pay_amount) AS t, DATE_FORMAT(sr.pay_time,'%Y%u') weeks\n" +
- "FROM com_shop_order sr LEFT JOIN com_shop_store ss ON sr.store_id = ss.id\n" +
- "WHERE sr.pay_status = 2 AND DATE_FORMAT(sr.pay_time,'%Y%u') = DATE_FORMAT(NOW(),'%Y%u')\n" +
- "<if test='userId!=null'>" +
- " and ss.sys_user_id = #{userId}\n" +
- "</if>" +
- "GROUP BY weeks\n" +
- "UNION \n" +
- "SELECT ss.sys_user_id, sum(sr.pay_amount) AS t, DATE_FORMAT(sr.pay_time,'%Y%m') months\n" +
- "FROM com_shop_order sr LEFT JOIN com_shop_store ss ON sr.store_id = ss.id\n" +
- "WHERE sr.pay_status = 2 AND DATE_FORMAT(sr.pay_time,'%Y%m') = DATE_FORMAT(NOW(),'%Y%m')\n" +
- "<if test='userId!=null'>" +
- " and ss.sys_user_id = #{userId}\n" +
- "</if>" +
- "GROUP BY months\n" +
- ") abt\n" +
- "GROUP BY abt.sys_user_id "+
- "</script>")
+ @Select("<script> " + " SELECT sys_user_id,\n"
+ + " COALESCE(SUM(CASE WHEN abt.days = DATE_FORMAT(NOW(),'%Y%m%d') THEN abt.t END), 0) AS todayTotal,\n"
+ + " COALESCE(SUM(CASE WHEN abt.days = DATE_FORMAT(NOW(),'%Y%u') THEN abt.t END), 0) AS weekTotal,\n"
+ + " COALESCE(SUM(CASE WHEN abt.days = DATE_FORMAT(NOW(),'%Y%m') THEN abt.t END), 0) AS monthTotal\n"
+ + "FROM (\n" + "SELECT ss.sys_user_id, sum(sr.pay_amount) AS t , DATE_FORMAT(sr.pay_time,'%Y%m%d') days\n"
+ + "FROM com_shop_order sr LEFT JOIN com_shop_store ss ON sr.store_id = ss.id\n"
+ + "WHERE sr.pay_status = 2 AND DATE_FORMAT(sr.pay_time,'%Y%m%d') = DATE_FORMAT(NOW(),'%Y%m%d')\n"
+ + "<if test='userId!=null and userId!=0L'>" + " and ss.sys_user_id = #{userId}\n" + "</if>" + "GROUP BY days\n"
+ + "UNION \n" + "SELECT ss.sys_user_id, sum(sr.pay_amount) AS t, DATE_FORMAT(sr.pay_time,'%Y%u') weeks\n"
+ + "FROM com_shop_order sr LEFT JOIN com_shop_store ss ON sr.store_id = ss.id\n"
+ + "WHERE sr.pay_status = 2 AND DATE_FORMAT(sr.pay_time,'%Y%u') = DATE_FORMAT(NOW(),'%Y%u')\n"
+ + "<if test='userId!=null and userId!=0L'>" + " and ss.sys_user_id = #{userId}\n" + "</if>" + "GROUP BY weeks\n"
+ + "UNION \n" + "SELECT ss.sys_user_id, sum(sr.pay_amount) AS t, DATE_FORMAT(sr.pay_time,'%Y%m') months\n"
+ + "FROM com_shop_order sr LEFT JOIN com_shop_store ss ON sr.store_id = ss.id\n"
+ + "WHERE sr.pay_status = 2 AND DATE_FORMAT(sr.pay_time,'%Y%m') = DATE_FORMAT(NOW(),'%Y%m')\n"
+ + "<if test='userId!=null and userId!=0L'>" + " and ss.sys_user_id = #{userId}\n" + "</if>"
+ + "GROUP BY months\n" + ") abt\n" + "GROUP BY abt.sys_user_id " + "</script>")
ComShopFundsVO queryStatis(@Param("userId") Long userId);
- @Select("<script> " +
- "SELECT sr.id, sr.total_amount, sr.pay_time, sr.order_no, \n" +
- " CASE WHEN sr.pay_type = 1 THEN '微信支付' ELSE '' END AS payType, \n" +
- " '收入' AS 'type' " +
- "FROM com_shop_order sr LEFT JOIN com_shop_store st ON sr.store_id = st.id \n" +
- " WHERE sr.pay_status = 2 AND sr.delete_status=1 " +
- "<if test='pageComShopFundsSearchDTO.userId != null'>" +
- " AND st.sys_user_id = #{pageComShopFundsSearchDTO.userId} " +
- " </if> " +
- "<if test='pageComShopFundsSearchDTO.payTimeStart != null'>" +
- " AND #{pageComShopFundsSearchDTO.payTimeStart} <![CDATA[<=]]> sr.pay_time " +
- " </if> " +
- "<if test='pageComShopFundsSearchDTO.payTimeEnd != null'>" +
- " AND sr.pay_time <![CDATA[<=]]> #{pageComShopFundsSearchDTO.payTimeEnd} " +
- " </if> " +
- "<if test='pageComShopFundsSearchDTO.orderNo != null '>" +
- " AND sr.order_no = #{pageComShopFundsSearchDTO.orderNo} " +
- " </if> " +
- " GROUP BY sr.id ORDER BY sr.create_at "+
- "</script>")
- IPage<ComShopFundsOrderVO> pageShopFunds(Page page, @Param("pageComShopFundsSearchDTO") PageComShopFundsSearchDTO pageComShopFundsSearchDTO);
+ @Select("<script> " + "SELECT sr.id, sr.total_amount, sr.pay_time, sr.order_no, \n"
+ + " CASE WHEN sr.pay_type = 1 THEN '微信支付' ELSE '' END AS payType, \n" + " '收入' AS 'type' "
+ + "FROM com_shop_order sr LEFT JOIN com_shop_store st ON sr.store_id = st.id \n"
+ + " WHERE sr.pay_status = 2 AND sr.delete_status=1 " + "<if test='pageComShopFundsSearchDTO.userId != null'>"
+ + " AND st.sys_user_id = #{pageComShopFundsSearchDTO.userId} " + " </if> "
+ + "<if test='pageComShopFundsSearchDTO.payTimeStart != null'>"
+ + " AND #{pageComShopFundsSearchDTO.payTimeStart} <![CDATA[<=]]> sr.pay_time " + " </if> "
+ + "<if test='pageComShopFundsSearchDTO.payTimeEnd != null'>"
+ + " AND sr.pay_time <![CDATA[<=]]> #{pageComShopFundsSearchDTO.payTimeEnd} " + " </if> "
+ + "<if test='pageComShopFundsSearchDTO.orderNo != null '>"
+ + " AND sr.order_no = #{pageComShopFundsSearchDTO.orderNo} " + " </if> "
+ + " GROUP BY sr.id ORDER BY sr.create_at DESC " + "</script>")
+ IPage<ComShopFundsOrderVO> pageShopFunds(Page page,
+ @Param("pageComShopFundsSearchDTO") PageComShopFundsSearchDTO pageComShopFundsSearchDTO);
- @Select("<script> " +
- "SELECT\n" +
- "css.store_account,\n" +
- "css.`contacts`,\n" +
- "css.`name` as storeName,\n" +
- "cso.total_amount,\n" +
- "cso.pay_type,\n" +
- "cso.pay_time, \n" +
- "cso.create_at, \n" +
- "cso.order_no\n" +
- "FROM\n" +
- "com_shop_order cso\n" +
- "left JOIN com_shop_store css on cso.store_id=css.id \n" +
- "where cso.id =#{id} "+
- "</script>")
- CapitalDetailVO capitalDetailByStore(@Param("id")Long id);
+ @Select("<script> " + "SELECT\n" + "css.store_account,\n" + "css.`contacts`,\n" + "css.`name` as storeName,\n"
+ + "cso.total_amount,\n" + "cso.pay_type,\n" + "cso.pay_time, \n" + "cso.create_at, \n" + "cso.order_no\n"
+ + "FROM\n" + "com_shop_order cso\n" + "left JOIN com_shop_store css on cso.store_id=css.id \n"
+ + "where cso.id =#{id} " + "</script>")
+ CapitalDetailVO capitalDetailByStore(@Param("id") Long id);
}
--
Gitblit v1.7.1