跨境代购转运系统运单统计看板开发：多维度物流线路数据加权分析实践

// src/modules/statistics/service/trans-stat.service.ts
import { Injectable } from '@nestjs/common';
import { InjectRepository } from '@nestjs/typeorm';
import { Repository, Between } from 'typeorm';
import { TransOrder } from 'src/modules/trans-order/entities/trans-order.entity';

@Injectable()
export class TransStatService {
  constructor(@InjectRepository(TransOrder) private transRepo: Repository<TransOrder>) {}

  // 获取指定日期区间运单全维度汇总数据
  async getTransTotalStat(startDate: Date, endDate: Date) {
    const stat = await this.transRepo
      .createQueryBuilder('trans')
      .select('SUM(trans.payTotal)', 'payTotal')
      .addSelect('SUM(trans.baseLogisticsFee)', 'totalLogistics')
      .addSelect('SUM(trans.addServiceFee)', 'addServiceFee')
      .addSelect('SUM(trans.warehouseFee)', 'warehouseFee')
      .addSelect('SUM(trans.couponDeduct)', 'couponDeduct')
      .addSelect('SUM(trans.pointDeduct)', 'pointDeduct')
      .addSelect("SUM(CASE trans.status WHEN 'unpay' THEN 1 ELSE 0 END)", 'unpayNum')
      .addSelect("SUM(CASE trans.status WHEN 'paid' THEN 1 ELSE 0 END)", 'paidNum')
      .addSelect("SUM(CASE trans.status WHEN 'packing' THEN 1 ELSE 0 END)", 'packingNum')
      .addSelect("SUM(CASE trans.status WHEN 'waitSend' THEN 1 ELSE 0 END)", 'waitSendNum')
      .addSelect("SUM(CASE trans.status WHEN 'shipped' THEN 1 ELSE 0 END)", 'shippedNum')
      .where('trans.createTime BETWEEN :start AND :end', { start: startDate, end: endDate })
      .getRawOne();
    // 格式化浮点数，统一保留两位小数适配美元计价
    return {
      payTotal: Number(stat.payTotal || 0).toFixed(2),
      totalLogistics: Number(stat.totalLogistics || 0).toFixed(2),
      addServiceFee: Number(stat.addServiceFee || 0).toFixed(2),
      warehouseFee: Number(stat.warehouseFee || 0).toFixed(2),
      couponDeduct: Number(stat.couponDeduct || 0).toFixed(2),
      pointDeduct: Number(stat.pointDeduct || 0).toFixed(2),
      unpayNum: Number(stat.unpayNum || 0),
      paidNum: Number(stat.paidNum || 0),
      packingNum: Number(stat.packingNum || 0),
      waitSendNum: Number(stat.waitSendNum || 0),
      shippedNum: Number(stat.shippedNum || 0)
    }
  }

  // 获取热门线路TOP排行，支持切换排序字段
  async getRouteTopList(startDate: Date, endDate: Date, sortField: string = 'payTotal') {
    const validSort = ['userNum', 'orderNum', 'payTotal'];
    const sortKey = validSort.includes(sortField) ? sortField : 'payTotal';
    return this.transRepo
      .createQueryBuilder('trans')
      .select('trans.routeName', 'routeName')
      .addSelect('COUNT(DISTINCT trans.userId)', 'userNum')
      .addSelect('COUNT(trans.id)', 'orderNum')
      .addSelect('SUM(trans.payTotal)', 'payTotal')
      .where('trans.createTime BETWEEN :start AND :end', { start: startDate, end: endDate })
      .groupBy('trans.routeName')
      .orderBy(sortKey, 'DESC')
      .limit(10)
      .getRawMany();
  }
}

<template>
  <div class="route-top-card">
    <h3>热门线路</h3>
    <el-table :data="routeList" border stripe>
      <el-table-column label="排名" type="index" width="60" />
      <el-table-column label="路线名称" prop="routeName" />
      <el-table-column label="下单人数" prop="userNum" sortable />
      <el-table-column label="下单数量" prop="orderNum" sortable />
      <el-table-column label="下单总金额($)" prop="payTotal" sortable />
    </el-table>
  </div>
</template>