package cn.iocoder.yudao.module.erp.dal.mysql.statistics;

import cn.iocoder.yudao.module.erp.dal.dataobject.sale.ErpSaleCustomerReceiptStatisticsDO;
import cn.iocoder.yudao.module.erp.dal.dataobject.sale.ErpSaleCustomerStatisticsDO;
import cn.iocoder.yudao.module.erp.dal.dataobject.sale.ErpSaleProductStatisticsDO;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

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

/**
 * ERP 销售统计 Mapper
 *
 * @author 超级管理员
 */
@Mapper
public interface ErpSaleStatisticsMapper {

    BigDecimal getSalePrice(@Param("beginTime") LocalDateTime beginTime,
                            @Param("endTime") LocalDateTime endTime);

    /**
     * 按客户统计销售订单（仅统计已审核订单）
     *
     * 需求 6.7① 销售款统计、③ 客户销售统计
     */
    @Select("<script>"
            + "SELECT customer_id AS customerId, COUNT(1) AS orderCount, "
            + "IFNULL(SUM(total_price), 0) AS saleAmount, MAX(order_time) AS lastOrderTime "
            + "FROM erp_sale_order WHERE deleted = 0 AND status = 20 AND customer_id IS NOT NULL "
            + "<if test='customerId != null'>AND customer_id = #{customerId} </if>"
            + "<if test='beginTime != null'>AND order_time &gt;= #{beginTime} </if>"
            + "<if test='endTime != null'>AND order_time &lt;= #{endTime} </if>"
            + "GROUP BY customer_id ORDER BY saleAmount DESC"
            + "</script>")
    List<ErpSaleCustomerStatisticsDO> selectCustomerStatistics(@Param("customerId") Long customerId,
                                                               @Param("beginTime") LocalDateTime beginTime,
                                                               @Param("endTime") LocalDateTime endTime);

    /**
     * 按客户统计已审核收款单的收款金额
     *
     * 需求 6.7① 销售款统计：已收款 / 未收款
     */
    @Select("<script>"
            + "SELECT customer_id AS customerId, IFNULL(SUM(receipt_price), 0) AS receiptAmount "
            + "FROM erp_finance_receipt WHERE deleted = 0 AND status = 20 AND customer_id IS NOT NULL "
            + "<if test='customerId != null'>AND customer_id = #{customerId} </if>"
            + "<if test='beginTime != null'>AND receipt_time &gt;= #{beginTime} </if>"
            + "<if test='endTime != null'>AND receipt_time &lt;= #{endTime} </if>"
            + "GROUP BY customer_id"
            + "</script>")
    List<ErpSaleCustomerReceiptStatisticsDO> selectCustomerReceiptStatistics(@Param("customerId") Long customerId,
                                                                           @Param("beginTime") LocalDateTime beginTime,
                                                                           @Param("endTime") LocalDateTime endTime);

    /**
     * 按产品统计销售明细（仅统计已审核订单）
     *
     * 需求 6.7② 产品销售统计
     */
    @Select("<script>"
            + "SELECT i.product_id AS productId, COUNT(DISTINCT i.order_id) AS orderCount, "
            + "IFNULL(SUM(i.count), 0) AS totalCount, IFNULL(SUM(i.total_price), 0) AS saleAmount "
            + "FROM erp_sale_order_item i INNER JOIN erp_sale_order o ON o.id = i.order_id AND o.deleted = 0 "
            + "WHERE i.deleted = 0 AND o.status = 20 AND i.product_id IS NOT NULL "
            + "<if test='productId != null'>AND i.product_id = #{productId} </if>"
            + "<if test='beginTime != null'>AND o.order_time &gt;= #{beginTime} </if>"
            + "<if test='endTime != null'>AND o.order_time &lt;= #{endTime} </if>"
            + "GROUP BY i.product_id ORDER BY saleAmount DESC"
            + "</script>")
    List<ErpSaleProductStatisticsDO> selectProductStatistics(@Param("productId") Long productId,
                                                             @Param("beginTime") LocalDateTime beginTime,
                                                             @Param("endTime") LocalDateTime endTime);

}