package com.ruoyi.sales.mapper;
|
|
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
|
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
|
import com.baomidou.mybatisplus.core.metadata.IPage;
|
import com.baomidou.mybatisplus.core.toolkit.support.SFunction;
|
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
|
import com.ruoyi.sales.dto.SalesLedgerDto;
|
import com.ruoyi.sales.dto.SalesTrendDto;
|
import com.ruoyi.sales.dto.StatisticsTableDto;
|
import com.ruoyi.sales.pojo.SalesLedger;
|
import org.apache.ibatis.annotations.Param;
|
import org.apache.ibatis.annotations.Select;
|
|
import java.math.BigDecimal;
|
import java.util.List;
|
|
|
/**
|
* 销售台账Mapper接口
|
*
|
* @author ruoyi
|
* @date 2025-05-08
|
*/
|
public interface SalesLedgerMapper extends BaseMapper<SalesLedger> {
|
/**
|
* 查询指定日期的所有合同序列号
|
* @param datePart 日期部分（格式：yyyyMMdd）
|
* @return 序列号列表
|
*/
|
List<Integer> selectSequencesByDate(@Param("datePart") String datePart);
|
|
List getSalesNo();
|
|
<T> BigDecimal selectSum(LambdaQueryWrapper<T> wrapper, SFunction<T, BigDecimal> column);
|
|
/**
|
*
|
* @param salesLedgerDto
|
* @return
|
*/
|
List<SalesLedger> selectSalesLedgerList(@Param("salesLedgerDto") SalesLedgerDto salesLedgerDto);
|
|
/**
|
* 分页查询
|
* @param page
|
* @param salesLedgerDto
|
* @return
|
*/
|
IPage<SalesLedger> selectSalesLedgerListPage(Page page, @Param("salesLedgerDto") SalesLedgerDto salesLedgerDto);
|
|
/**
|
* 按月份统计订单数、销售额（支持产品大类、客户名称筛选）
|
* @param statisticsTableDto 统计查询参数DTO
|
* @return 销售趋势统计结果
|
*/
|
@Select("<script>" +
|
"SELECT " +
|
"DATE_FORMAT(sl.entry_date, '%Y-%m') AS month, " +
|
"COUNT(DISTINCT sl.id) AS order_count, " + // 总订单数
|
"SUM(slp.tax_inclusive_total_price) AS sales_amount, " + // 销售额
|
// 发货率 = 发货订单数 * 100 / 总订单数（保留2位小数，避免除0报错）
|
"ROUND(IF(COUNT(DISTINCT sl.id) = 0, 0, " +
|
"SUM(CASE WHEN slp.approve_status = 2 THEN 1 ELSE 0 END) / COUNT(DISTINCT sl.id) * 100), 2) AS ship_rate " +
|
"FROM sales_ledger sl " +
|
"LEFT JOIN sales_ledger_product slp ON sl.id = slp.sales_ledger_id " +
|
"WHERE sl.entry_date BETWEEN #{statisticsTableDto.entryDateStart} AND #{statisticsTableDto.entryDateEnd} " +
|
// 产品大类筛选
|
"<if test='statisticsTableDto.productCategory != null and statisticsTableDto.productCategory != \"\"'>" +
|
"AND slp.product_category = #{statisticsTableDto.productCategory} " +
|
"</if>" +
|
// 客户名称筛选
|
"<if test='statisticsTableDto.customerName != null and statisticsTableDto.customerName != \"\"'>" +
|
"AND sl.customer_name = #{statisticsTableDto.customerName} " +
|
"</if>" +
|
"GROUP BY DATE_FORMAT(sl.entry_date, '%Y-%m') " +
|
"ORDER BY month" +
|
"</script>")
|
List<SalesTrendDto> statisticsTable(@Param("statisticsTableDto")StatisticsTableDto statisticsTableDto);
|
}
|
