java/zhihuishenqu.git

			@@ -3,14 +3,11 @@
			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.ComShopOrderPageVO;
			import com.panzhihua.common.model.vos.shop.ComShopOrderVO;
			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.dtos.shop.PageComShopOrderSearchDTO;
			import com.panzhihua.common.model.vos.shop.ComShopOrderVO;
			import com.panzhihua.common.model.vos.shop.PageShopStoreVO;
			import com.panzhihua.service_community.model.dos.ComShopOrderDO;
			import org.apache.ibatis.annotations.Mapper;
			import org.apache.ibatis.annotations.Param;
			@@ -36,14 +33,180 @@
			IPage<ComShopOrderPageVO> pageOrderList(Page page,@Param("comOrderListDTO") PageComOrderListDTO comOrderListDTO);

			@Select("<script> " +
			"SELECT ordr.id, ordr.store_id, ordr. user_id, ordr.order_no, ordr.pay_no, \n" +
			" ordr.wx_tarde_no, ordr.status, ordr.pay_status, ordr.receiver_id, \n" +
			" ordr.total_amount, ordr.discount_amount, ordr.pay_amount, ordr.pay_type, \n" +
			" ordr.pay_time, ordr.delivery_type, ordr.delivery_status, ordr.logistics_company, \n" +
			" ordr.logistics_no, ordr.create_at \n" +
			" FROM com_shop_order ordr LEFT JOIN com_shop_order_goods goods ON ordr.id = goods.order_id \n" +
			" order by create_at desc"+
			"SELECT sr.id, sr.store_id, sr.user_id, 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 \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.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> " +
			" ORDER BY sr.create_at "+
			"</script>")
			IPage<ComShopOrderVO> pageOrderBy(PageDTO page,@Param("pageComShopOrderSearchDTO") PageComShopOrderSearchDTO pageComShopOrderSearchDTO);
			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 com_shop_store.user_id = #{comShopOrderExportDTO.userId} " +
			" </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);

			@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 sys_user_id,\n" +
			" sum(case when abt.days = DATE_FORMAT(NOW(),'%Y%m%d') then abt.t END) AS todayTotal,\n" +
			" sum(case when abt.days = DATE_FORMAT(NOW(),'%Y%u') then abt.t END) AS weekTotal,\n" +
			" sum(case when abt.days = DATE_FORMAT(NOW(),'%Y%m') then abt.t END) AS monthTotal\n" +
			"FROM (\n" +
			"SELECT ss.sys_user_id, (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" +
			"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" +
			"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" +
			"GROUP BY months\n" +
			") abt\n" +
			"GROUP BY abt.sys_user_id "+
			"</script>")
			ComShopFundsVO queryStatis(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);
			}