| | |
|---|
| | | "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报错） |
|---|
| | | "SUM(IFNULL(slp.quantity, 0)) AS product_quantity, " + // 产品销售数量 |
|---|
| | | // 发货率 = 有发货记录的订单数 / 总订单数 * 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 " + |
|---|
| | | "COUNT(DISTINCT CASE WHEN si.id IS NOT NULL THEN sl.id 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 " + |
|---|
| | | "LEFT JOIN shipping_info si ON sl.id = si.sales_ledger_id " + |
|---|
| | | "WHERE sl.entry_date >= DATE_FORMAT(#{statisticsTableDto.entryDateStart}, '%Y-%m-01') " + |
|---|
| | | "AND sl.entry_date <= LAST_DAY(#{statisticsTableDto.entryDateEnd}) " + |
|---|
| | | // 产品大类筛选 |
|---|
