src/main/java/com/mzl/flower/dto/request/report/QueryPartnerOrderDTO.java
对比新文件 @@ -0,0 +1,30 @@ package com.mzl.flower.dto.request.report; import com.fasterxml.jackson.annotation.JsonFormat; import io.swagger.annotations.ApiModelProperty; import lombok.Data; import org.springframework.format.annotation.DateTimeFormat; import javax.validation.constraints.NotNull; import java.time.LocalDate; import java.time.LocalDateTime; @Data public class QueryPartnerOrderDTO { @ApiModelProperty(value = "合伙人id") private Long partnerId; @ApiModelProperty("订单的开始日期") @JsonFormat(pattern="yyyy-MM-dd" ,timezone="GMT+8") @DateTimeFormat(pattern="yyyy-MM-dd") @NotNull(message = "开始日期不能为空") private LocalDate paymentDateStart; @ApiModelProperty("订单的结束日期") @JsonFormat(pattern="yyyy-MM-dd" ,timezone="GMT+8") @DateTimeFormat(pattern="yyyy-MM-dd") @NotNull(message = "结束日期不能为空") private LocalDate paymentDateEnd; } src/main/java/com/mzl/flower/dto/response/report/OrderPartnerReportResultVO.java
@@ -6,15 +6,14 @@ import org.springframework.format.annotation.DateTimeFormat; import java.math.BigDecimal; import java.time.LocalDate; import java.time.LocalDateTime; @Data public class OrderPartnerReportResultVO { @ApiModelProperty("下单日期") @JsonFormat(pattern="yyyy-MM-dd" ,timezone="GMT+8") @DateTimeFormat(pattern="yyyy-MM-dd") private LocalDateTime orderDate; private String orderDate; @ApiModelProperty("销售额") private BigDecimal orderTotal; @@ -29,20 +28,20 @@ private BigDecimal orderPartnerPriceAmount; @ApiModelProperty("平台区间加价") private BigDecimal orderMarkupOneAmount; // @ApiModelProperty("平台区间加价") // private BigDecimal orderMarkupOneAmount; @ApiModelProperty("平台加价") private BigDecimal orderMarkupTwoAmount; @ApiModelProperty("平台区域加价--暂无") private BigDecimal platformAreaFeeAmount; // @ApiModelProperty("平台加价") // private BigDecimal orderMarkupTwoAmount; // // @ApiModelProperty("平台区域加价--暂无") // private BigDecimal platformAreaFeeAmount; @ApiModelProperty("合伙人加价") private BigDecimal orderMarkupPartnerAmount; @ApiModelProperty("合伙人区间加价--暂无") private BigDecimal partnerSectionFeeAmount; // @ApiModelProperty("合伙人区间加价--暂无") // private BigDecimal partnerSectionFeeAmount; @ApiModelProperty("会员折扣") private BigDecimal orderPriceDiscountAmount; src/main/java/com/mzl/flower/dto/response/report/OrderReportCalendarBO.java
@@ -3,6 +3,7 @@ import io.swagger.annotations.ApiModelProperty; import lombok.Data; import java.time.LocalDate; import java.time.LocalDateTime; @Data @@ -24,4 +25,11 @@ private Integer partnerFlag; @ApiModelProperty("订单的开始日期") private LocalDate paymentDateStart; @ApiModelProperty("订单的结束日期") private LocalDate paymentDateEnd; } src/main/java/com/mzl/flower/mapper/report/OrderReportMapper.java
@@ -2,6 +2,7 @@ import com.baomidou.mybatisplus.extension.plugins.pagination.Page; import com.mzl.flower.dto.response.report.*; import com.mzl.flower.dto.request.report.QueryPartnerOrderDTO; import com.mzl.flower.dto.response.report.OrderDetailReportResultVO; import com.mzl.flower.dto.response.report.OrderPartnerReportResultVO; import com.mzl.flower.dto.response.report.OrderReportCalendarBO; @@ -15,7 +16,11 @@ List<OrderDetailReportResultVO> getOrderDetailReport(@Param("dto") OrderReportCalendarBO calendarBO, Page page); OrderPartnerReportResultVO getPartnerOrderDateReport(@Param("dto") OrderReportCalendarBO calendarBO); OrderPartnerReportResultVO getPartnerOrderDateReportStatis(@Param("dto") QueryPartnerOrderDTO bo); Page<OrderPartnerReportResultVO> getPartnerOrderDateReportPage(Page page, @Param("dto") QueryPartnerOrderDTO dto); List<OrderPartnerReportResultVO> getPartnerOrderDateReportList(@Param("dto") QueryPartnerOrderDTO dto); OrderPartnerReportResultVO getPartnerOrderDateReportStatis(@Param("dto") OrderReportCalendarBO bo);
@@ -2,6 +2,7 @@ import com.baomidou.mybatisplus.extension.plugins.pagination.Page; import com.mzl.flower.dto.request.report.QueryOrderDTO; import com.mzl.flower.dto.request.report.QueryPartnerOrderDTO; import com.mzl.flower.dto.response.report.OrderReportCalendarBO; import com.mzl.flower.entity.CalendarDO; import com.baomidou.mybatisplus.extension.service.IService; src/main/java/com/mzl/flower/service/impl/CalendarServiceImpl.java
@@ -5,6 +5,7 @@ import com.baomidou.mybatisplus.extension.plugins.pagination.Page; import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl; import com.mzl.flower.dto.request.report.QueryOrderDTO; import com.mzl.flower.dto.request.report.QueryPartnerOrderDTO; import com.mzl.flower.dto.response.report.OrderReportCalendarBO; import com.mzl.flower.entity.CalendarDO; import com.mzl.flower.mapper.calendar.CalendarMapper; src/main/java/com/mzl/flower/service/impl/report/OrderReportServiceImpl.java
@@ -5,24 +5,29 @@ import com.mzl.flower.dto.request.report.QueryOrderDTO; import com.mzl.flower.dto.request.report.QuerySupplierDTO; import com.mzl.flower.dto.response.report.*; import com.mzl.flower.dto.request.report.QueryPartnerOrderDTO; import com.mzl.flower.dto.response.report.OrderDetailReportResultVO; import com.mzl.flower.dto.response.report.OrderPartnerReportResultVO; import com.mzl.flower.dto.response.report.OrderReportCalendarBO; import com.mzl.flower.dto.response.report.OrderReportResultVO; import com.mzl.flower.mapper.report.OrderReportMapper; import com.mzl.flower.service.BaseService; import com.mzl.flower.service.calendar.CalendarService; import com.mzl.flower.service.report.OrderReportService; import com.mzl.flower.utils.ExcelExportUtil; import lombok.extern.slf4j.Slf4j; import org.apache.commons.collections4.CollectionUtils; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service; import org.springframework.util.StringUtils; import javax.servlet.http.HttpServletResponse; import java.net.URLEncoder; import java.time.LocalDate; import java.time.LocalDateTime; import java.time.LocalTime; import java.time.format.DateTimeFormatter; import java.util.ArrayList; import java.util.List; import java.util.Objects; import java.util.stream.Collectors; @Slf4j @@ -201,51 +206,21 @@ } @Override public Page<OrderPartnerReportResultVO> getPartnerSalePage(Page page, QueryOrderDTO dto) { List<OrderReportCalendarBO> calendarBOList= calendarService.getOrderDatePage(page,dto); final List<OrderPartnerReportResultVO> list = calendarBOList.stream().map(calendarBO -> { if(null!=dto.getPartnerId()) calendarBO.setPartnerId(dto.getPartnerId()); // if(null!=dto.getPartnerFlag()) calendarBO.setPartnerFlag(dto.getPartnerFlag()); // 查询合伙人 calendarBO.setPartnerFlag(1); OrderPartnerReportResultVO vo2=orderReportMapper.getPartnerOrderDateReport(calendarBO); if(null==vo2) vo2=new OrderPartnerReportResultVO(); vo2.setOrderDate(calendarBO.getCalDate()); return vo2; }).collect(Collectors.toList()); page.setRecords(list); return page; public Page<OrderPartnerReportResultVO> getPartnerSalePage(Page page, QueryPartnerOrderDTO dto) { Page<OrderPartnerReportResultVO> result=orderReportMapper.getPartnerOrderDateReportPage(page,dto); return result; } @Override public OrderPartnerReportResultVO getPartnerSaleStatis(QueryOrderDTO dto) { OrderReportCalendarBO bo=new OrderReportCalendarBO(); if(null!=dto.getPartnerId()) bo.setPartnerId(dto.getPartnerId()); bo.setStartDate(calculateStartTime(dto.getStartDate())); bo.setEndDate(calculateEndTime(dto.getEndDate())); OrderPartnerReportResultVO vo2=orderReportMapper.getPartnerOrderDateReportStatis(bo); public OrderPartnerReportResultVO getPartnerSaleStatis(QueryPartnerOrderDTO dto) { OrderPartnerReportResultVO vo2=orderReportMapper.getPartnerOrderDateReportStatis(dto); return vo2; } @Override public void exportPartnerSalesList(HttpServletResponse response, QueryOrderDTO dto) { List<OrderReportCalendarBO> calendarBOList= calendarService.getOrderDateList(dto); final List<OrderPartnerReportResultVO> list = calendarBOList.stream().map(calendarBO -> { if(null!=dto.getPartnerId()) calendarBO.setPartnerId(dto.getPartnerId()); // if(null!=dto.getPartnerFlag()) calendarBO.setPartnerFlag(dto.getPartnerFlag()); // 查询合伙人 calendarBO.setPartnerFlag(1); OrderPartnerReportResultVO vo2=orderReportMapper.getPartnerOrderDateReport(calendarBO); if(null==vo2) vo2=new OrderPartnerReportResultVO(); vo2.setOrderDate(calendarBO.getCalDate()); public void exportPartnerSalesList(HttpServletResponse response, QueryPartnerOrderDTO dto) { List<OrderPartnerReportResultVO> list = orderReportMapper.getPartnerOrderDateReportList(dto); return vo2; }).collect(Collectors.toList()); String[] rowsName = new String[]{"序号","下单日期", "合伙人ID", "合伙人","底价" , "合伙人加价", "会员折扣","优惠券","售后扣合伙人款","总包干费","实际销售扎数"}; @@ -255,7 +230,7 @@ Object[] objs = new Object[rowsName.length]; int a = 0; objs[a++] = sn; // 序号 objs[a++] = format(o.getOrderDate(), "yyyy-MM-dd"); // 下单日期 objs[a++] = o.getOrderDate(); // 下单日期 objs[a++] = o.getPartnerId(); // 合伙人ID objs[a++] = o.getPartnerName(); // 合伙人 objs[a++] = o.getOrderPartnerPriceAmount(); // 合伙人底价 @@ -269,9 +244,9 @@ sn++; } ExcelExportUtil excelExportUtil = new ExcelExportUtil("财务报表", rowsName, dataList, response); ExcelExportUtil excelExportUtil = new ExcelExportUtil("合伙人财务报表", rowsName, dataList, response); try { response.addHeader("filename", URLEncoder.encode("财务报表.xls", "UTF-8")); response.addHeader("filename", URLEncoder.encode("合伙人财务报表.xls", "UTF-8")); response.addHeader("Access-Control-Expose-Headers", "filename"); excelExportUtil.export(); } catch (Exception e) { src/main/java/com/mzl/flower/service/report/OrderReportService.java
@@ -3,6 +3,7 @@ import com.baomidou.mybatisplus.extension.plugins.pagination.Page; import com.mzl.flower.dto.request.report.QueryOrderDTO; import com.mzl.flower.dto.request.report.QuerySupplierDTO; import com.mzl.flower.dto.request.report.QueryPartnerOrderDTO; import com.mzl.flower.dto.response.report.OrderDetailReportResultVO; import com.mzl.flower.dto.response.report.OrderPartnerReportResultVO; import com.mzl.flower.dto.response.report.OrderReportResultVO; @@ -28,7 +29,7 @@ * @param dto * @return */ Page<OrderPartnerReportResultVO> getPartnerSalePage(Page page, QueryOrderDTO dto); Page<OrderPartnerReportResultVO> getPartnerSalePage(Page page, QueryPartnerOrderDTO dto); /** * 合伙人结算列表统计 @@ -36,8 +37,9 @@ * @param dto * @return */ OrderPartnerReportResultVO getPartnerSaleStatis(QueryOrderDTO dto); OrderPartnerReportResultVO getPartnerSaleStatis(QueryPartnerOrderDTO dto); void exportPartnerSalesList(HttpServletResponse response, QueryPartnerOrderDTO dto); void exportPartnerSalesList(HttpServletResponse response, QueryOrderDTO dto); Page<OrderSupplierReportResultVO> getOrderSupplierPage(Page page, QuerySupplierDTO dto); src/main/java/com/mzl/flower/web/v2/report/OrderReportController.java
@@ -6,6 +6,7 @@ import com.mzl.flower.base.R; import com.mzl.flower.base.ReturnDataDTO; import com.mzl.flower.dto.request.report.QueryOrderDTO; import com.mzl.flower.dto.request.report.QueryPartnerOrderDTO; import com.mzl.flower.dto.request.report.QuerySupplierDTO; import com.mzl.flower.dto.response.report.OrderDetailReportResultVO; import com.mzl.flower.dto.response.report.OrderReportResultVO; @@ -74,21 +75,21 @@ @GetMapping("/partner/sale/page") @ApiOperation(value = "合伙人-查询-分页", notes = "合伙人-查询-分页") public ResponseEntity<ReturnDataDTO<Page<OrderReportResultVO>>> getPartnerPage( Page page,@Validated QueryOrderDTO dto Page page,@Validated QueryPartnerOrderDTO dto ) { return returnData(R.SUCCESS.getCode(), ConverterUtil.transPage(orderReportService.getPartnerSalePage(page,dto), OrderReportResultVO.class)); return returnData(R.SUCCESS.getCode(), orderReportService.getPartnerSalePage(page,dto)); } @GetMapping("/partner/sale/statis") @ApiOperation(value = "合伙人-查询-分页", notes = "合伙人-查询-分页") public ResponseEntity<ReturnDataDTO<Page<OrderReportResultVO>>> getPartnerPageStatis(@Validated QueryOrderDTO dto @ApiOperation(value = "合伙人-查询-统计", notes = "合伙人-查询-统计") public ResponseEntity<ReturnDataDTO<Page<OrderReportResultVO>>> getPartnerPageStatis(@Validated QueryPartnerOrderDTO dto ) { return returnData(R.SUCCESS.getCode(), ConverterUtil.transObject(orderReportService.getPartnerSaleStatis(dto), OrderReportResultVO.class)); return returnData(R.SUCCESS.getCode(), orderReportService.getPartnerSaleStatis(dto)); } @GetMapping({"/partner/sale/export"}) @ApiOperation(value = "合伙人报告列表导出") public void partnerExportSales(HttpServletResponse response,@Validated QueryOrderDTO dto){ public void partnerExportSales(HttpServletResponse response,@Validated QueryPartnerOrderDTO dto){ orderReportService.exportPartnerSalesList(response, dto); }
@@ -160,9 +160,7 @@ ) </if> </select> <select id="getPartnerOrderDateReport" resultType="com.mzl.flower.dto.response.report.OrderPartnerReportResultVO"> <include refid="partnerBaseSql"></include> </select> <select id="getPartnerOrderDateReportStatis" resultType="com.mzl.flower.dto.response.report.OrderPartnerReportResultVO"> select @@ -190,65 +188,78 @@ sum(order_reduce_num) as order_reduce_num, sum(order_lack_num) as order_lack_num, sum(real_sale_num) as real_sale_num from <include refid="partnerBaseSql"></include> from ( <include refid="partnerBaseSqlWithDate"></include> ) t </select> <sql id="partnerBaseSql"> <select id="getPartnerOrderDateReportPage" resultType="com.mzl.flower.dto.response.report.OrderPartnerReportResultVO"> <include refid="partnerBaseSqlWithDate"></include> </select> <select id="getPartnerOrderDateReportList" resultType="com.mzl.flower.dto.response.report.OrderPartnerReportResultVO"> <include refid="partnerBaseSqlWithDate"></include> </select> <sql id="partnerBaseSqlWithDate"> select sum(order_total) as order_total, sum(order_supplier_price_amount)+sum(order_markup_one_amount)+sum(order_markup_two_amount) as order_partner_price_amount, sum(order_supplier_price_amount) as order_supplier_price_amount, sum(order_markup_one_amount) as order_markup_one_amount, sum(order_markup_two_amount) as order_markup_two_amount, sum(order_markup_partner_amount) as order_markup_partner_amount, sum(order_price_discount_amount) as order_price_discount_amount, sum(order_coupon_amount_total) as order_coupon_amount_total, sum(order_check_fee) as order_check_fee, sum(order_lack_fee_supplier) as order_lack_fee_supplier, sum(order_replace_fee) as order_replace_fee, sum(order_fee_supplier) as order_fee_supplier, sum(order_fee_partner) as order_fee_partner, sum(order_fee_platform) as order_fee_platform, sum(order_fee_platform_pack) as order_fee_platform_pack, sum(order_fee_platform_check) as order_fee_platform_check, sum(order_fee_platform_transport) as order_fee_platform_transport, sum(order_total_fee) as order_total_fee, sum(order_fee_packing_transport) as order_fee_packing_transport, sum(order_num) as order_num, sum(order_replace_num) as order_replace_num, sum(order_reduce_num) as order_reduce_num, sum(order_lack_num) as order_lack_num, sum(order_num)-sum(order_lack_num) as real_sale_num, partner_id, partner_name t3.payment_date as orderDate ,t3.partner_id,t3.partner_name, IFNULL(sum(order_total),0) as order_total, IFNULL(sum(order_supplier_price_amount)+sum(order_markup_one_amount)+sum(order_markup_two_amount),0) as order_partner_price_amount, IFNULL(sum(order_supplier_price_amount),0) as order_supplier_price_amount, IFNULL(sum(order_markup_one_amount),0) as order_markup_one_amount, IFNULL(sum(order_markup_two_amount),0) as order_markup_two_amount, IFNULL(sum(order_markup_partner_amount),0) as order_markup_partner_amount, IFNULL(sum(order_price_discount_amount),0) as order_price_discount_amount, IFNULL(sum(order_coupon_amount_total),0) as order_coupon_amount_total, IFNULL(sum(order_check_fee),0) as order_check_fee, IFNULL(sum(order_lack_fee_supplier),0) as order_lack_fee_supplier, IFNULL(sum(order_replace_fee),0) as order_replace_fee, IFNULL(sum(order_fee_supplier),0) as order_fee_supplier, IFNULL(sum(order_fee_partner),0) as order_fee_partner, IFNULL(sum(order_fee_platform),0) as order_fee_platform, IFNULL(sum(order_fee_platform_pack),0) as order_fee_platform_pack, IFNULL(sum(order_fee_platform_check),0) as order_fee_platform_check, IFNULL(sum(order_fee_platform_transport),0) as order_fee_platform_transport, IFNULL(sum(order_total_fee),0) as order_total_fee, IFNULL(sum(order_fee_packing_transport),0) as order_fee_packing_transport, IFNULL(sum(order_num),0) as order_num, IFNULL(sum(order_replace_num),0) as order_replace_num, IFNULL(sum(order_reduce_num),0) as order_reduce_num, IFNULL(sum(order_lack_num),0) as order_lack_num, IFNULL(sum(order_num)-sum(order_lack_num),0) as real_sale_num from ( select o.order_no,o.payment_time,o.status_backend,vor.*,ci.partner_id,p.name as partner_name from t_order o left join ( <include refid="baseSql"></include> ) vor on o.id =vor.order_id select t2.payment_date,t2.id,t2.order_no,t2.create_by,ci.partner_id,p.name as partner_name,r2.* from ( SELECT t.*, CASE WHEN t.payment_time > DATE(t.payment_time) + INTERVAL 17 HOUR THEN DATE(t.payment_time) + INTERVAL 1 DAY ELSE DATE(t.payment_time) END AS payment_date, t.payment_time as payment_time2 FROM t_order t WHERE t.deleted = 0 and t.status_backend not in ('PENDING','CANCEL','REFUND') )t2 left join ( <include refid="baseSqlWithDate"></include> ) r2 on t2.id = r2.order_id left join t_customer_info ci on o.create_by=ci.user_id on t2.create_by=ci.user_id left join t_partner_info p on ci.partner_id=p.id where o.deleted=0 and o.status_backend not in ('PENDING','CANCEL','REFUND') and ci.partner_id is not null <if test="dto.startDate != null"> and o.payment_time > #{dto.startDate} </if> <if test="dto.endDate != null"> and o.payment_time <= #{dto.endDate} </if> <if test="dto.partnerId != null"> and ci.partner_id = #{dto.partnerId} </if> ) t group by partner_id,partner_name )t3 where t3.partner_id is not null <if test="dto.paymentDateStart != null"> and t3.payment_date >= #{dto.paymentDateStart} </if> <if test="dto.paymentDateEnd != null"> and t3.payment_date <= #{dto.paymentDateEnd} </if> <if test="dto.partnerId != null"> and t3.partner_id = #{dto.partnerId} </if> group by t3.payment_date,t3.partner_id,t3.partner_name order by t3.payment_date desc,t3.partner_id </sql> <sql id="baseSql"> select t2.*, @@ -337,6 +348,98 @@ </sql> <sql id="baseSqlWithDate"> select t2.*, IFNULL(t3.fee_supplier,0) as order_fee_supplier, IFNULL(t3.fee_partner,0) as order_fee_partner, IFNULL(t3.fee_platform,0) as order_fee_platform, IFNULL(t3.fee_platform_pack,0) as order_fee_platform_pack, IFNULL(t3.fee_platform_check,0) as order_fee_platform_check, IFNULL(t3.fee_platform_transport,0) as order_fee_platform_transport, IFNULL(t3.total_fee,0) as order_total_fee, IFNULL(t3.fee_packing_transport,0) as order_fee_packing_transport from ( select t1.order_id,sum(t1.num) as order_num , sum(t1.total) as order_total, sum(t1.supplier_price_amount) as order_supplier_price_amount, sum(t1.markup_one_amount) as order_markup_one_amount,sum(t1.markup_two_amount) as order_markup_two_amount, sum(t1.markup_partner_amount) as order_markup_partner_amount,sum(t1.price_amount) as order_price_amount,sum(t1.original_price_amount) as order_original_price_amount,sum(t1.price_discount_amount) as order_price_discount_amount,sum(t1.coupon_amount_total) as order_coupon_amount_total,sum(t1.check_fee) as order_check_fee,sum(t1.lack_fee_supplier) as order_lack_fee_supplier,sum(t1.replace_fee) as order_replace_fee, sum(replace_num) as order_replace_num,sum(reduce_num) as order_reduce_num,sum(lack_num) as order_lack_num from ( SELECT CASE WHEN o.payment_time > DATE(o.payment_time) + INTERVAL 17 HOUR THEN DATE(o.payment_time) + INTERVAL 1 DAY ELSE DATE(o.payment_time) END AS payment_date, oi.order_id, oi.id, IFNULL(oi.num,0) as num, IFNULL(oi.total,0) as total, IFNULL(oi.supplier_price,0) as supplier_price, IFNULL(oi.supplier_price,0) * IFNULL(oi.num,0) as supplier_price_amount, IFNULL(oi.markup_one,0) as markup_one, IFNULL(oi.markup_one,0)* IFNULL(oi.num,0) as markup_one_amount, IFNULL(oi.markup_two,0) as markup_two, IFNULL(oi.markup_two,0)* IFNULL(oi.num,0) as markup_two_amount, IFNULL(oi.markup_partner,0) as markup_partner, IFNULL(oi.markup_partner,0)* IFNULL(oi.num,0) as markup_partner_amount, IFNULL(oi.price,0) as price, IFNULL(oi.price,0)* IFNULL(oi.num,0) as price_amount, IFNULL(oi.original_price,0) as original_price, IFNULL(oi.original_price,0)*IFNULL(oi.num,0) as original_price_amount, IFNULL(oi.original_price,0)*IFNULL(oi.num,0) - IFNULL(oi.price,0)* IFNULL(oi.num,0) as price_discount_amount, IFNULL(oi.real_price,0) as real_price, IFNULL(oi.real_total,0) as real_total, IFNULL(oi.coupon_amount_total,0) as coupon_amount_total, IFNULL(ois.check_fee,0) as check_fee, IFNULL(ois.lack_fee_supplier,0) as lack_fee_supplier, IFNULL(ois.replace_fee,0) as replace_fee ,IFNULL( (select oic.num from t_order_item_check oic where oic.order_item_id = oi.id and oic.type = 'replace'),0) as replace_num ,IFNULL( (select oic.num from t_order_item_check oic where oic.order_item_id = oi.id and oic.type = 'reduce'),0) as reduce_num ,IFNULL( (select oic.num from t_order_item_check oic where oic.order_item_id = oi.id and oic.type = 'lack'),0) as lack_num FROM t_order o left join t_order_item oi on oi.order_id=o.id left join t_station s on s.id = oi.station_id left join t_supplier_info si on si.id = oi.supplier_id left join t_order_item_settlement ois on ois.order_item_id = oi.id WHERE oi.deleted = 0 )t1 where 1=1 <if test="dto.paymentDateStart != null"> and t1.payment_date >= #{dto.paymentDateStart} </if> <if test="dto.paymentDateEnd != null"> and t1.payment_date <= #{dto.paymentDateEnd} </if> group by order_id ) t2 left join ( select ois.order_id, IFNULL(sum(ois.fee_supplier),0) as fee_supplier, IFNULL(sum(ois.fee_partner),0) as fee_partner, IFNULL(sum(ois.fee_platform),0) as fee_platform, IFNULL(sum(ois.fee_platform_pack),0) as fee_platform_pack, IFNULL(sum(ois.fee_platform_check),0) as fee_platform_check, IFNULL(sum(ois.fee_platform_transport),0) as fee_platform_transport, IFNULL(sum(ois.total_fee),0) as total_fee, IFNULL(sum(ois.fee_packing_transport),0) as fee_packing_transport from t_order_item_sales ois group by order_id )t3 on t2.order_id =t3.order_id </sql> <sql id="baseSupplierSql"> SELECT t2.*,
