1.报表框架

陶杰

2024-09-27 43d728030dca41209677b0a7e314de2ce0a21aa9

1.报表框架

 src/main/java/com/mzl/flower/dto/response/report/OrderReportCalendarBO.java

@@ -17,4 +17,8 @@
    @ApiModelProperty("订单的结束时间")
    private LocalDateTime endDate;

    @ApiModelProperty(value = "合伙人id")
    private Long partnerId;


}

 src/main/java/com/mzl/flower/dto/response/report/OrderReportResultVO.java

@@ -1,71 +1,76 @@
package com.mzl.flower.dto.response.report;

import com.fasterxml.jackson.annotation.JsonFormat;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import org.springframework.format.annotation.DateTimeFormat;

import java.math.BigDecimal;
import java.time.LocalDateTime;

@Data
public class OrderReportResultVO {

    @ApiModelProperty("下单日期")
    private String orderDate;
    @JsonFormat(pattern="yyyy-MM-dd" ,timezone="GMT+8")
    @DateTimeFormat
    private LocalDateTime orderDate;

    @ApiModelProperty("销售额")
    private BigDecimal saleFeeAmount;
    private BigDecimal orderTotal;

    @ApiModelProperty("花农底价")
    private BigDecimal supplierFeeAmount;
    private BigDecimal orderSupplierPriceAmount;

    @ApiModelProperty("平台区间加价")
    private BigDecimal platformSectionFeeAmount;
    private BigDecimal orderMarkupOneAmount;

    @ApiModelProperty("平台加价")
    private BigDecimal platformFeeAmount;
    private BigDecimal orderMarkupTwoAmount;

    @ApiModelProperty("平台区域加价")
    @ApiModelProperty("平台区域加价--暂无")
    private BigDecimal platformAreaFeeAmount;

    @ApiModelProperty("合伙人加价")
    private BigDecimal partnerFeeAmount;
    private BigDecimal orderMarkupPartnerAmount;

    @ApiModelProperty("合伙人区间加价")
    @ApiModelProperty("合伙人区间加价--暂无")
    private BigDecimal partnerSectionFeeAmount;

    @ApiModelProperty("会员折扣")
    private BigDecimal memberDiscountFeeAmount;
    private BigDecimal orderPriceDiscountAmount;

    @ApiModelProperty("优惠券")
    private BigDecimal couponFeeAmount;
    private BigDecimal orderCouponAmountTotal;

    @ApiModelProperty("降级扣款")
    private BigDecimal demotionFeeAmount;
    private BigDecimal orderCheckFee;

    @ApiModelProperty("缺货扣款")
    private BigDecimal stockoutFeeAmount;
    private BigDecimal orderLackFeeSupplier;

    @ApiModelProperty("补货扣款")
    private BigDecimal replenishmentFeeAmount;
    private BigDecimal orderReplaceFee;

    @ApiModelProperty("售后扣合伙人款")
    private BigDecimal afterSalePartnerFeeAmount;
    private BigDecimal orderFeePartner;

    @ApiModelProperty("售后扣花农款")
    private BigDecimal afterSaleSupplierFeeAmount;
    private BigDecimal orderFeeSupplier;

    @ApiModelProperty("售后扣平台款")
    private BigDecimal afterSalePlatformFeeAmount;
    private BigDecimal orderFeePlatform;

    @ApiModelProperty("总包干费")
    @ApiModelProperty("总包干费--暂无")
    private BigDecimal partnerTotalFeeAmount;

    @ApiModelProperty("销售扎数")
    private Integer saleAmount;
    @ApiModelProperty("实际销售扎数")
    private Integer realSaleNum;

    @ApiModelProperty("利润")
    @ApiModelProperty("利润--总包干费没有无法计算")
    private BigDecimal profitFeeAmount;

    @ApiModelProperty("结算状态")
    @ApiModelProperty("结算状态--暂无")
    private String settleStatus;

}

 src/main/java/com/mzl/flower/mapper/report/OrderReportMapper.java

对比新文件
@@ -0,0 +1,9 @@
package com.mzl.flower.mapper.report;

import com.mzl.flower.dto.response.report.OrderReportCalendarBO;
import com.mzl.flower.dto.response.report.OrderReportResultVO;
import org.apache.ibatis.annotations.Param;

public interface OrderReportMapper {
    OrderReportResultVO getOrderDateReport(@Param("dto") OrderReportCalendarBO calendarBO);
}

 src/main/java/com/mzl/flower/service/impl/report/OrderReportServiceImpl.java

@@ -4,11 +4,19 @@
import com.mzl.flower.dto.request.report.QueryOrderDTO;
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.calendar.CalendarService;
import com.mzl.flower.service.report.OrderReportService;
import com.mzl.flower.utils.ExcelExportUtil;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletResponse;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.LocalTime;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;
import java.util.stream.Collectors;
@@ -19,18 +27,57 @@
    @Autowired
    private CalendarService calendarService;

    @Autowired
    private OrderReportMapper orderReportMapper;



    @Override
    public Page<OrderReportResultVO> getSalePage(Page page, QueryOrderDTO dto) {

        List<OrderReportCalendarBO> calendarBOList= calendarService.getOrderDatePage(page,dto);

        final List<OrderReportResultVO> list = calendarBOList.stream().map(calendarBO -> {
            OrderReportResultVO vo=new OrderReportResultVO();
            BeanUtils.copyProperties(dto,calendarBO);
            OrderReportResultVO vo2=orderReportMapper.getOrderDateReport(calendarBO);
            vo2.setOrderDate(calendarBO.getCalDate());

            return vo;
            return vo2;
        }).collect(Collectors.toList());

        page.setRecords(list);
        return page;
    }

    @Override
    public OrderReportResultVO getSaleStatis(QueryOrderDTO dto) {
        // 计算开始时间和结束时间

        OrderReportCalendarBO bo=new OrderReportCalendarBO();
        BeanUtils.copyProperties(dto,bo);
        bo.setStartDate(calculateStartTime(dto.getStartDate()));
        bo.setEndDate(calculateEndTime(dto.getEndDate()));
        OrderReportResultVO vo2=orderReportMapper.getOrderDateReport(bo);

        return vo2;
    }

    @Override
    public void exportSalesList(HttpServletResponse response, QueryOrderDTO dto) {

    }


    // 计算 startDate 的前一天 17:00:00
    public static LocalDateTime calculateStartTime(LocalDate startDate) {
        LocalDate previousDay = startDate.minusDays(1);
        LocalTime fivePM = LocalTime.of(17, 0, 0); // 下午 17:00:00
        return LocalDateTime.of(previousDay, fivePM);
    }

    // 计算 endDate 当天的 17:00:00
    public static LocalDateTime calculateEndTime(LocalDate endDate) {
        LocalTime fivePM = LocalTime.of(17, 0, 0); // 下午 17:00:00
        return LocalDateTime.of(endDate, fivePM);
    }
}

 src/main/java/com/mzl/flower/service/report/OrderReportService.java

@@ -4,6 +4,12 @@
import com.mzl.flower.dto.request.report.QueryOrderDTO;
import com.mzl.flower.dto.response.report.OrderReportResultVO;

import javax.servlet.http.HttpServletResponse;

public interface OrderReportService {
    Page<OrderReportResultVO> getSalePage(Page page, QueryOrderDTO dto);

    OrderReportResultVO getSaleStatis(QueryOrderDTO dto);

    void exportSalesList(HttpServletResponse response, QueryOrderDTO dto);
}

 src/main/java/com/mzl/flower/web/v2/report/OrderReportController.java

@@ -8,6 +8,7 @@
import com.mzl.flower.config.exception.ValidationException;
import com.mzl.flower.dto.request.coupon.CreateCouponRecordDTO;
import com.mzl.flower.dto.request.coupon.QueryCouponRecordDTO;
import com.mzl.flower.dto.request.payment.OrderItemSalesQueryDTO;
import com.mzl.flower.dto.request.report.QueryOrderDTO;
import com.mzl.flower.dto.response.coupon.CouponRecordResultVO;
import com.mzl.flower.dto.response.coupon.CouponRecordVO;
@@ -27,6 +28,8 @@
import org.springframework.validation.annotation.Validated;
import org.springframework.web.bind.annotation.*;

import javax.servlet.http.HttpServletResponse;


/**
* @author @TaoJie
@@ -44,10 +47,24 @@

    @GetMapping("/sale/page")
    @ApiOperation(value = "查询-分页", notes = "查询-分页")
    public ResponseEntity<ReturnDataDTO<Page<CouponRecordVO>>> page(
    public ResponseEntity<ReturnDataDTO<Page<OrderReportResultVO>>> page(
            Page page, QueryOrderDTO dto
    ) {
        return returnData(R.SUCCESS.getCode(), ConverterUtil.transPage(orderReportService.getSalePage(page,dto), OrderReportResultVO.class));
    }

    @GetMapping("/sale/statis")
    @ApiOperation(value = "统计", notes = "统计")
    public ResponseEntity<ReturnDataDTO<OrderReportResultVO>> statis(QueryOrderDTO dto
    ) {
        return returnData(R.SUCCESS.getCode(), ConverterUtil.transObject(orderReportService.getSaleStatis(dto), OrderReportResultVO.class));
    }


    @GetMapping({"/sale/export"})
    @ApiOperation(value = "报告列表导出")
    public void exportSales(HttpServletResponse response, QueryOrderDTO dto){
        orderReportService.exportSalesList(response, dto);
    }

}

 src/main/resources/mapper/report/OrderReportMapper.xml

对比新文件
@@ -0,0 +1,52 @@
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.mzl.flower.mapper.report.OrderReportMapper">

    <select id="getOrderDateReport" resultType="com.mzl.flower.dto.response.report.OrderReportResultVO">
        select
            sum(order_total)  as order_total, -- 销售额
            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 -- 销售扎数
        from (
                 select
                     o.order_no,o.payment_time,o.status_backend,vor.*
                 from t_order o
                 left join v_order_report vor
                 on o.id =vor.order_id
                 where o.deleted=0
                 and o.status_backend not in ('PENDING','CANCEL','REFUND')
                <if test="dto.startDate != null">
                    and o.payment_time &gt; #{dto.startDate}
                </if>
                <if test="dto.endDate != null">
                    and o.payment_time &lt;= #{dto.endDate}
                </if>
                <if test="dto.partnerId != null">
                    o.create_by in (
                        select user_id from t_customer_info where partner_id=#{dto.partnerId}
                    )
                </if>
        ) t

    </select>
</mapper>