跳转到主要内容

发货-发货统计

发货统计模块功能解析文档

1. 系统架构

1.1 整体架构

发货统计模块采用前后端分离架构，主要包含以下组件：

  • 前端组件：Vue 3 + Element Plus构建的单页应用
  • 后端服务：Spring Boot微服务，提供RESTful API
  • 数据库：MySQL数据库存储发货相关数据
  • 外部依赖：亚马逊API用于获取和同步FBA货件信息

1.2 模块依赖关系

前端组件 (ship_summary/index.vue)
    ↓
API接口层 (reportApi.js / reportV2Api.js)
    ↓
控制器层 (ShipInboundReportController / ShipInboundReportV2Controller)
    ↓
服务层 (IShipInboundItemService)
    ↓
数据访问层 (ShipInboundItemMapper / ShipInboundItemV2Mapper)
    ↓
MySQL数据库

1.3 文件结构

wimoor-ui/src/views/amazon/report/
├── ship/
│   └── ship_summary/
│       ├── index.vue              # V1版本前端组件
│       └── component/
│           └── piechart.vue       # 饼图组件
└── shipv2/
    └── ship_summary/
        ├── index.vue              # V2版本前端组件
        └── component/
            └── piechart.vue       # 饼图组件

wimoor-amazon/amazon-boot/src/main/java/
└── com/wimoor/amazon/
    ├── inbound/
    │   ├── controller/
    │   │   └── ShipInboundReportController.java    # V1控制器
    │   ├── service/
    │   │   ├── IShipInboundItemService.java        # 服务接口
    │   │   └── impl/ShipInboundItemServiceImpl.java # 服务实现
    │   └── mapper/
    │       └── ShipInboundItemMapper.xml           # V1 Mapper XML
    └── inboundV2/
        ├── controller/
        │   └── ShipInboundReportV2Controller.java  # V2控制器
        ├── service/
        │   ├── IShipInboundItemService.java        # V2服务接口
        │   └── impl/ShipInboundItemServiceImpl.java # V2服务实现
        └── mapper/
            └── ShipInboundItemV2Mapper.xml         # V2 Mapper XML

2. 前端实现

2.1 核心组件分析

2.1.1 主页面组件（index.vue）

文件路径：wimoor-ui/src/views/amazon/report/ship/ship_summary/index.vue

主要功能：

  • 提供多维度数据分组功能
  • 支持灵活的筛选条件
  • 展示饼图可视化
  • 显示详细的统计数据表格
  • 提供数据导出功能

核心代码结构：

<template>
  <div class="main-sty">
    <!-- 分组条件区 -->
    <el-row>
      <el-checkbox-group v-model="queryParam.groupby" @change="handleQuery">
        <el-checkbox label="channeldetailid">物流承运商(汇总)</el-checkbox>
        <el-checkbox label="warehouse">FBA仓库(汇总)</el-checkbox>
        <el-checkbox label="warehouseid">本地仓库(汇总)</el-checkbox>
        <el-checkbox label="groupid">店铺(汇总)</el-checkbox>
        <el-checkbox label="marketplaceid">站点(汇总)</el-checkbox>
        <el-checkbox label="sku">SKU(汇总)</el-checkbox>
        <el-checkbox label="shipmentid">货件(汇总)</el-checkbox>
      </el-checkbox-group>
    </el-row>

    <!-- 筛选条件区 -->
    <el-row>
      <Group @change="getData" />
      <Warehouse @changeware="getWarehouse" />
      <el-select v-model="queryParam.datetype">
        <el-option value="createdate" label="创建日期"></el-option>
        <el-option value="deliverydate" label="发货日期"></el-option>
      </el-select>
      <Datepicker @changedate="changedate" />
      <el-select v-model="queryParam.companyid" @change="companyChange">
        <el-option v-for="item in companylist" :value="item.id" :label="item.name"></el-option>
      </el-select>
      <el-select v-model="queryParam.channelid">
        <el-option v-for="item in channellist" :value="item.id" :label="item.channame"></el-option>
      </el-select>
      <el-input v-model="queryParam.search" placeholder="请输入SKU" />
    </el-row>

    <!-- 饼图展示区 -->
    <el-row class="gary-bg pie-chart">
      <el-select v-model="fieldkey.value">
        <el-option v-for="item in fieldoptions" :value="item.key" :label="item.name"></el-option>
      </el-select>
      <div v-for="(value,key) in chartdata.value">
        <PieChart :name="key" :data="value" :keyvalue="fieldkey" :chartdata="chartdata" />
      </div>
    </el-row>

    <!-- 数据表格区 -->
    <GlobalTable :tableData="tableData" @loadTable="loadTableData" show-summary :summary-method="getSummaries">
      <template #field>
        <el-table-column v-if="queryParam.groupby.indexOf('groupid')>=0" label="店铺" />
        <el-table-column v-if="queryParam.groupby.indexOf('marketplaceid')>=0" label="站点" />
        <el-table-column v-if="queryParam.groupby.indexOf('warehouseid')>=0" label="本地仓" />
        <el-table-column v-if="queryParam.groupby.indexOf('channeldetailid')>=0" label="物流承运商" />
        <el-table-column v-if="queryParam.groupby.indexOf('channeldetailid')>=0" label="物流渠道" />
        <el-table-column v-if="queryParam.groupby.indexOf('warehouse')>=0" label="FBA仓库" />
        <el-table-column v-if="queryParam.groupby.indexOf('sku')>=0" label="SKU" />
        <el-table-column v-if="queryParam.groupby.indexOf('shipmentid')>=0" label="货件" />
        <!-- 发货信息列组 -->
        <el-table-column label="发货信息">
          <el-table-column prop="totalqty" label="计划发货" />
          <el-table-column prop="totalout" label="实际发货" />
          <el-table-column prop="totalrec" label="实际接收" />
          <el-table-column prop="lessrec" label="接收差值" />
          <el-table-column prop="needout" label="待发货" />
          <el-table-column prop="needrec" label="待接收" />
          <el-table-column prop="worth" label="实际发货货值" />
        </el-table-column>
        <!-- 运输信息列组 -->
        <el-table-column label="运输信息">
          <el-table-column prop="readweight" label="预估运输重量(KG)" />
          <el-table-column prop="transweight_kg" label="发货运输重量(KG)" />
          <el-table-column prop="transweight_cbm" label="发货运输重量(CBM)" />
          <el-table-column prop="totalbox" label="货件箱数" />
          <el-table-column prop="shipfee" label="运输费用" />
          <el-table-column prop="totalotherfee" label="关税/其他费用" />
          <el-table-column prop="avgtime" label="平均物流时效(天)" />
        </el-table-column>
        <!-- 货件信息列组 -->
        <el-table-column label="货件信息">
          <el-table-column prop="shipmentnum" label="货件票数" />
          <el-table-column prop="problem" label="异常货件票数" />
        </el-table-column>
      </template>
    </GlobalTable>
  </div>
</template>

状态管理：

let state = reactive({
  downLoading: false,
  queryParam: {
    search: "",
    marketplaceid: "",
    searchtype: "company",
    datetype: "createdate",
    type: "logitics",
    groupby: ["channeldetailid"],
    companyid: "",
    channelid: ""
  },
  isload: true,
  tableData: { records: [], total: 0 },
  snapshotDate: '',
  summary: {},
  fieldkey: { value: "transweight_kg" },
  chartdata: { value: [] },
  companylist: [],
  channellist: [],
  fieldoptions: [
    { name: "预估运输重量", key: 'readweight' },
    { name: "计划发货", key: 'totalqty' },
    { name: "实际发货", key: 'totalout' },
    { name: "实际接收", key: 'totalrec' },
    { name: "接收差值", key: 'lessrec' },
    { name: "实际发货货值", key: 'worth' },
    { name: "待接收", key: 'needrec' },
    { name: "待发货", key: 'needout' },
    { name: "发货运输重量(KG)", key: 'transweight_kg' },
    { name: "发货运输重量(CBM)", key: 'transweight_cbm' },
    { name: "运输费用", key: 'shipfee' },
    { name: "货件箱数", key: 'totalbox' },
    { name: "关税/其他费用", key: 'totalotherfee' },
    { name: "货件票数", key: 'shipmentnum' },
    { name: "平均物流时效(天)", key: 'avgtime' }
  ]
});

核心方法：

  1. handleQuery() - 处理查询请求
function handleQuery() {
  if (state.queryParam.groupby && state.queryParam.groupby.length > 0) {
    globalTable.value.loadTable(state.queryParam);
  }
}
  1. loadTableData() - 加载统计数据
function loadTableData(params) {
  reportApi.getShipmentReportByLoistics(params).then((res) => {
    state.isload = false;
    state.tableData.records = res.data.records;
    state.tableData.total = res.data.total;
    if (params.currentpage == 1 && res.data.total > 0) {
      state.summary = res.data.records[0].summary;
    }
  });
  reportApi.getShipmentReportByWarehouseLoistics(params).then((res) => {
    state.chartdata.value = res.data;
  });
}
  1. downloadList() - 导出数据
function downloadList() {
  state.downLoading = true;
  reportApi.downExcelShipmentReportByLoistics(state.queryParam, () => {
    state.downLoading = false;
  });
}
  1. getSummaries() - 计算合计行
function getSummaries({ columns, data }) {
  var arr = ["合计"];
  columns.forEach((item, index) => {
    if (index >= 2) {
      arr[index] = state.summary[item.property];
    }
  });
  return arr;
}
  1. companyChange() - 承运商变更处理
function companyChange(val) {
  getchannelList(val);
  handleQuery();
}

function getchannelList(val) {
  var companyid = val;
  state.queryParam.channelid = "";
  if (val != "") {
    transportationApi.getChannel({ "company": companyid, "marketplaceid": "", "transtype": "" }).then((res) => {
      res.data.push({ "id": "", "channame": "全部" });
      state.channellist = res.data;
    });
  } else {
    state.channellist = [];
  }
}

2.2 API接口层

文件路径：wimoor-ui/src/api/amazon/inbound/reportApi.js

核心接口：

// 获取货件报表（按物流）
function getShipmentReportByLoistics(data) {
  return request.post('/amazon/api/v1/ship/report/getShipmentReportByLoistics', data);
}

// 获取仓库物流报表
function getShipmentReportByWarehouseLoistics(data) {
  return request.post('/amazon/api/v1/ship/report/getShipmentReportByWarehouseLoistics', data);
}

// 导出物流报表Excel
function downExcelShipmentReportByLoistics(data, callback) {
  return request({
    url: "/amazon/api/v1/ship/report/downExcelShipmentReportByLoistics",
    responseType: "blob",
    data: data,
    method: 'post'
  }).then(res => {
    downloadhandler.downloadSuccess(res, "shipmentLogisticsReport.xlsx");
    if (callback) {
      callback();
    }
  }).catch(e => {
    downloadhandler.downloadFail(e);
    if (callback) {
      callback();
    }
  });
}

V2版本API：wimoor-ui/src/api/amazon/inbound/reportV2Api.js

// V2版本使用相同的接口路径（/api/v2/）
function getShipmentReportByLoistics(data) {
  return request.post('/amazon/api/v2/ship/report/getShipmentReportByLoistics', data);
}

function getShipmentReportByWarehouseLoistics(data) {
  return request.post('/amazon/api/v2/ship/report/getShipmentReportByWarehouseLoistics', data);
}

function downExcelShipmentReportByLoistics(data, callback) {
  return request({
    url: "/amazon/api/v2/ship/report/downExcelShipmentReportByLoistics",
    responseType: "blob",
    data: data,
    method: 'post'
  }).then(res => {
    downloadhandler.downloadSuccess(res, "shipmentLogisticsReport.xlsx");
    // ...
  });
}

3. 后端实现

3.1 控制器层

3.1.1 ShipInboundReportController

文件路径：wimoor-amazon/amazon-boot/src/main/java/com/wimoor/amazon/inbound/controller/ShipInboundReportController.java

主要功能：提供发货统计相关的RESTful API接口

核心接口：

  1. getShipmentReportByLoistics() - 获取货件统计报表
@PostMapping(value = "/getShipmentReportByLoistics")
public Result<IPage<Map<String, Object>>> getShipmentReportByLoistics(@RequestBody ShipmentReportByLogisticsDTO dto) {
  Map<String, Object> param = new HashMap<String, Object>();
  UserInfo user = UserInfoContext.get();
  String shopid = user.getCompanyid();
  param.put("shopid", shopid);
  
  // 处理承运商
  String companyid = dto.getCompanyid();
  if (StrUtil.isEmpty(companyid)) {
    companyid = null;
  }
  param.put("companyid", companyid);
  
  // 处理物流渠道
  String channelid = dto.getChannelid();
  if (StrUtil.isEmpty(channelid)) {
    channelid = null;
  }
  param.put("channelid", channelid);
  
  // 处理仓库
  String warehouseid = dto.getWarehouseid();
  if (StrUtil.isEmpty(warehouseid)) {
    warehouseid = null;
  }
  param.put("warehouseid", warehouseid);
  
  // 处理类型
  String type = dto.getType();
  param.put("type", type);
  
  // 处理搜索条件
  String search = dto.getSearch();
  if (StrUtil.isNotEmpty(search)) {
    param.put("search", search.trim() + "%");
  }
  
  // 处理日期类型
  String datetype = dto.getDatetype();
  param.put("datetype", datetype);
  
  // 处理分组
  String ftype = "nosku";
  Set<String> keySet = new TreeSet<String>();
  keySet.add("channeldetailid");
  keySet.add("warehouse");
  keySet.add("warehouseid");
  keySet.add("groupid");
  keySet.add("sku");
  keySet.add("marketplaceid");
  keySet.add("shipmentid");
  String groupby = null;
  for (String field : dto.getGroupby()) {
    if (keySet.contains(field)) {
      if (field.equals("sku")) {
        ftype = "sku";
      }
      if (groupby == null) {
        groupby = field;
      } else {
        groupby = groupby + "," + field;
      }
    }
  }
  param.put("groupby", groupby);
  param.put("type", ftype);
  
  // 处理日期范围
  SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
  String fromDate = dto.getFromDate();
  if (StrUtil.isNotEmpty(fromDate)) {
    param.put("fromDate", fromDate.trim());
  } else {
    Calendar cal = Calendar.getInstance();
    cal.add(Calendar.MONTH, -1);
    fromDate = GeneralUtil.formatDate(cal.getTime(), sdf);
    param.put("fromDate", fromDate);
  }
  
  String toDate = dto.getToDate();
  if (StrUtil.isNotEmpty(toDate)) {
    param.put("endDate", toDate.trim().substring(0, 10) + " 23:59:59");
  } else {
    toDate = GeneralUtil.formatDate(new Date(), sdf);
    param.put("endDate", toDate + " 23:59:59");
  }
  
  // 处理店铺和市场
  param.put("groupid", StrUtil.isBlank(dto.getGroupid()) ? null : dto.getGroupid());
  param.put("marketplaceid", StrUtil.isBlank(dto.getMarketplaceid()) ? null : dto.getMarketplaceid());
  
  // 调用服务层获取数据
  IPage<Map<String, Object>> list = iShipInboundItemService.shipmentReportByLoistics(dto.getPage(), param);
  
  // 添加汇总数据
  if (list != null && list.getRecords().size() > 0 && dto.getCurrentpage() == 1) {
    Map<String, Object> map = iShipInboundItemService.shipmentReportByLoisticsTotal(param);
    if (map != null) {
      list.getRecords().get(0).put("summary", map);
    }
  }
  
  return Result.success(list);
}
  1. getShipmentReportByWarehouseLoistics() - 获取仓库物流统计
@PostMapping(value = "/getShipmentReportByWarehouseLoistics")
public Result<Map<String, List<Map<String, Object>>>> shipmentReportByWarhouseCHType(@RequestBody ShipmentReportByLogisticsDTO dto) {
  Map<String, Object> param = new HashMap<String, Object>();
  UserInfo user = UserInfoContext.get();
  String shopid = user.getCompanyid();
  param.put("shopid", shopid);
  
  // 处理各种筛选条件...
  
  param.put("groupid", StrUtil.isBlank(dto.getGroupid()) ? null : dto.getGroupid());
  param.put("marketplaceid", StrUtil.isBlank(dto.getMarketplaceid()) ? null : dto.getMarketplaceid());
  
  Map<String, List<Map<String, Object>>> result = iShipInboundItemService.shipmentReportByWarhouseCHType(param);
  return Result.success(result);
}
  1. downExcelShipmentReportByLoisticsAction() - 导出Excel
@PostMapping(value = "/downExcelShipmentReportByLoistics")
public void downExcelShipmentReportByLoisticsAction(@RequestBody ShipmentReportByLogisticsDTO dto, HttpServletResponse response) {
  SXSSFWorkbook workbook = new SXSSFWorkbook();
  Map<String, Object> param = new HashMap<String, Object>();
  UserInfo user = UserInfoContext.get();
  String shopid = user.getCompanyid();
  param.put("shopid", shopid);
  
  // 处理各种参数...
  
  iShipInboundItemService.setShipmentReportByLoisticsExcelBook(workbook, param, dto.getGroupby());
  
  // 输出Excel文件
  response.setContentType("application/force-download");
  response.addHeader("Content-Disposition", "attachment;fileName=ShipmentReportByLoistics" + System.currentTimeMillis() + ".xlsx");
  ServletOutputStream fOut = response.getOutputStream();
  workbook.write(fOut);
  workbook.close();
  fOut.flush();
  fOut.close();
}

3.2 服务层

3.2.1 IShipInboundItemService

文件路径：wimoor-amazon/amazon-boot/src/main/java/com/wimoor/amazon/inbound/service/IShipInboundItemService.java

核心方法：

public interface IShipInboundItemService extends IService<ShipInboundItem> {
  // 其他方法...
  
  /**
   * 获取货件统计报表（按物流）
   */
  IPage<Map<String, Object>> shipmentReportByLoistics(Page<?> page, Map<String, Object> param);
  
  /**
   * 获取货件统计汇总
   */
  Map<String, Object> shipmentReportByLoisticsTotal(Map<String, Object> param);
  
  /**
   * 获取仓库物流统计
   */
  Map<String, List<Map<String, Object>>> shipmentReportByWarhouseCHType(Map<String, Object> param);
  
  /**
   * 生成货件统计报表Excel
   */
  void setShipmentReportByLoisticsExcelBook(SXSSFWorkbook workbook, Map<String, Object> param, List<String> groupby);
}

3.2.2 ShipInboundItemServiceImpl

核心实现逻辑：

@Override
public IPage<Map<String, Object>> shipmentReportByLoistics(Page<?> page, Map<String, Object> param) {
  return this.baseMapper.shipmentReportByLoistics(page, param);
}

@Override
public Map<String, Object> shipmentReportByLoisticsTotal(Map<String, Object> param) {
  return this.baseMapper.shipmentReportByLoisticsTotal(param);
}

@Override
public Map<String, List<Map<String, Object>>> shipmentReportByWarhouseCHType(Map<String, Object> param) {
  List<Map<String, Object>> list = this.baseMapper.shipmentReportByWarhouseCHType(param);
  Map<String, List<Map<String, Object>>> result = new LinkedHashMap<String, List<Map<String, Object>>>();
  for (Map<String, Object> item : list) {
    String key = item.get("warehouseid") != null ? item.get("warehouseid").toString() : "";
    if (!result.containsKey(key)) {
      result.put(key, new ArrayList<Map<String, Object>>());
    }
    result.get(key).add(item);
  }
  return result;
}

@Override
public void setShipmentReportByLoisticsExcelBook(SXSSFWorkbook workbook, Map<String, Object> param, List<String> groupby) {
  Map<String, Object> titlemap = new LinkedHashMap<String, Object>();
  
  // 根据分组条件动态添加标题列
  if (groupby.contains("groupid")) {
    titlemap.put("gname", "店铺");
  }
  if (groupby.contains("marketplaceid")) {
    titlemap.put("market", "站点");
  }
  if (groupby.contains("warehouseid")) {
    titlemap.put("warehousename", "本地仓");
  }
  if (groupby.contains("channeldetailid")) {
    titlemap.put("logitics", "物流承运商");
    titlemap.put("channame", "物流渠道");
  }
  if (groupby.contains("warehouse")) {
    titlemap.put("warehouse", "FBA仓库");
  }
  if (groupby.contains("sku")) {
    titlemap.put("sku", "SKU");
  }
  if (groupby.contains("shipmentid")) {
    titlemap.put("shipmentid", "货件");
  }
  
  // 添加数据列
  titlemap.put("totalqty", "计划发货");
  titlemap.put("totalout", "实际发货");
  titlemap.put("totalrec", "实际接收");
  titlemap.put("lessrec", "接收差值");
  titlemap.put("needout", "待发货");
  titlemap.put("needrec", "待接收");
  titlemap.put("worth", "实际发货货值");
  titlemap.put("readweight", "预估运输重量(KG)");
  titlemap.put("transweight_kg", "发货运输重量(KG)");
  titlemap.put("transweight_cbm", "发货运输重量(CBM)");
  titlemap.put("totalbox", "货件箱数");
  titlemap.put("shipfee", "运输费用");
  titlemap.put("totalotherfee", "关税/其他费用");
  titlemap.put("avgtime", "平均物流时效(天)");
  titlemap.put("shipmentnum", "货件票数");
  titlemap.put("problem", "异常货件票数");
  
  List<Map<String, Object>> list = this.baseMapper.shipmentReportByLoistics(param);
  Sheet sheet = workbook.createSheet("sheet1");
  
  // 创建标题行
  Row trow = sheet.createRow(0);
  Object[] titlearray = titlemap.keySet().toArray();
  for (int i = 0; i < titlearray.length; i++) {
    Cell cell = trow.createCell(i);
    Object value = titlemap.get(titlearray[i].toString());
    cell.setCellValue(value.toString());
  }
  
  // 填充数据行
  for (int i = 0; i < list.size(); i++) {
    Row row = sheet.createRow(i + 1);
    Map<String, Object> map = list.get(i);
    for (int j = 0; j < titlearray.length; j++) {
      Cell cell = row.createCell(j);
      Object value = map.get(titlearray[j].toString());
      if (value != null) {
        cell.setCellValue(value.toString());
      }
    }
  }
}

3.3 数据访问层

3.3.1 ShipInboundItemMapper

文件路径：wimoor-amazon/amazon-boot/src/main/java/com/wimoor/amazon/inbound/mapper/ShipInboundItemMapper.java

核心方法：

public interface ShipInboundItemMapper extends BaseMapper<ShipInboundItem> {
  // 其他方法...
  
  IPage<Map<String, Object>> shipmentReportByLoistics(Page<?> page, @Param("param") Map<String, Object> param);
  
  Map<String, Object> shipmentReportByLoisticsTotal(@Param("param") Map<String, Object> param);
  
  List<Map<String, Object>> shipmentReportByWarhouseCHType(@Param("param") Map<String, Object> param);
}

3.3.2 ShipInboundItemMapper.xml

文件路径：wimoor-amazon/amazon-boot/src/main/resources/mapper/inbound/ShipInboundItemMapper.xml

核心SQL查询：

<select id="shipmentReportByLoistics" parameterType="java.util.Map" resultType="java.util.Map">
  SELECT * FROM (
    SELECT
      plan.marketplaceid warehouse,
      de.transtype,
      max(mkp.name) marketname,
      max(tt.name) name,
      sum(ifnull(dd.weight, 0) * item.Quantity) readweight,
      SUM(item.Quantity) totalqty,
      sum(CASE WHEN shipment.`status` = 5 OR shipment.`status` = 6 THEN item.QuantityShipped ELSE 0 END) totalout,
      sum(CASE WHEN shipment.`status` = 5 OR shipment.`status` = 6 THEN item.QuantityReceived ELSE 0 END) totalrec,
      sum(CASE WHEN shipment.`status` = 5 OR shipment.`status` = 6 THEN item.QuantityReceived - item.QuantityShipped ELSE 0 END) lessrec,
      sum(CASE WHEN shipment.`status` = 5 OR shipment.`status` = 6 THEN item.QuantityShipped * m.price ELSE 0 END) worth,
      sum(CASE WHEN shipment.`status` >= 2 AND shipment.`status` <= 5 THEN item.QuantityShipped ELSE 0 END) needrec,
      sum(CASE WHEN shipment.`status` >= 2 AND shipment.`status` <= 5 THEN item.Quantity - item.QuantityReceived ELSE 0 END) needout
    FROM t_erp_ship_inbounditem item
    LEFT JOIN t_erp_ship_inboundplan plan ON plan.id = item.inboundplanid
    LEFT JOIN t_marketplace mkp ON mkp.marketplaceId = plan.marketplaceid
    LEFT JOIN t_erp_warehouse w ON plan.warehouseid = w.id
    LEFT JOIN t_erp_material m ON m.sku = item.SellerSKU AND plan.shopid = m.shopid AND m.isDelete = 0
    LEFT JOIN t_dimensions dd ON dd.id = m.pkgDimensions
    LEFT JOIN t_erp_ship_inboundshipment shipment ON shipment.ShipmentId = item.ShipmentId
    LEFT JOIN t_erp_ship_inboundtrans trans ON trans.shipmentid = item.ShipmentId
    LEFT JOIN t_erp_ship_transdetail de ON de.id = trans.channel
    LEFT JOIN t_erp_ship_transchannel ch ON ch.id = de.channel
    LEFT JOIN t_erp_transtype tt ON tt.id = de.transtype
    LEFT JOIN t_erp_ship_transcompany com ON com.id = de.company
    WHERE plan.shopid = #{param.shopid, jdbcType = CHAR}
      AND shipment.status >= 5
      AND com.`name` IS NOT NULL
    <if test="param.datetype == 'createdate'">
      AND plan.createdate >= #{param.fromDate, jdbcType = DATE}
      AND plan.createdate <= #{param.endDate, jdbcType = DATE}
    </if>
    <if test="param.datetype == 'deliverydate'">
      AND shipment.shiped_date >= #{param.fromDate, jdbcType = DATE}
      AND shipment.shiped_date <= #{param.endDate, jdbcType = DATE}
    </if>
    <if test="param.warehouseid != null">
      AND w.id = #{param.warehouseid, jdbcType = CHAR}
    </if>
    <if test="param.companyid != null">
      AND de.company = #{param.companyid, jdbcType = CHAR}
    </if>
    <if test="param.channelid != null">
      AND trans.channel = #{param.channelid, jdbcType = CHAR}
    </if>
    <if test="param.search != null">
      AND item.SellerSKU LIKE #{param.search, jdbcType = CHAR}
    </if>
    <if test="param.groupid != null">
      AND plan.amazongroupid = #{param.groupid, jdbcType = CHAR}
    </if>
    <if test="param.marketplaceid != null">
      AND plan.marketplaceid = #{param.marketplaceid, jdbcType = CHAR}
    </if>
    GROUP BY plan.marketplaceid, de.transtype
  ) v
  LEFT JOIN (
    SELECT
      plan.marketplaceid warehouse,
      de.transtype,
      SUM(CASE WHEN trans.wunit = 'kg' THEN trans.transweight ELSE 0 END) transweight_kg,
      SUM(CASE WHEN trans.wunit = 'cbm' THEN trans.transweight ELSE 0 END) transweight_cbm,
      SUM(IFNULL(trans.singleprice, 0) * IFNULL(trans.transweight, 0) + IFNULL(trans.otherfee, 0)) shipfee,
      SUM(trans.otherfee) totalotherfee,
      COUNT(shipment.shipmentid) shipmentnum,
      SUM(shipment.boxnum) totalbox,
      ROUND(AVG(IFNULL(DATEDIFF(shipment.start_receive_date, shipment.shiped_date), 0)), 2) avgtime,
      COUNT(IF(shipment.status = '-1', TRUE, NULL)) problem
    FROM t_erp_ship_inboundshipment shipment
    LEFT JOIN t_erp_ship_inboundplan plan ON plan.id = shipment.inboundplanid
    LEFT JOIN t_marketplace mkp ON mkp.marketplaceid = plan.marketplaceid
    LEFT JOIN t_erp_warehouse w ON plan.warehouseid = w.id
    LEFT JOIN t_erp_ship_inboundtrans trans ON trans.shipmentid = shipment.ShipmentId
    LEFT JOIN t_erp_ship_transdetail de ON de.id = trans.channel
    LEFT JOIN t_erp_ship_transcompany com ON com.id = de.company
    WHERE plan.shopid = #{param.shopid, jdbcType = CHAR}
      AND (shipment.status >= 5 OR shipment.start_receive_date IS NOT NULL)
      AND com.`name` IS NOT NULL
    -- 相同的筛选条件...
    GROUP BY plan.marketplaceid, de.transtype
  ) w ON v.warehouse = w.warehouse AND v.transtype = w.transtype
</select>

4. 数据库设计

4.1 核心表结构

4.1.1 t_erp_ship_inbounditem（货件明细表）

字段名 类型 说明
id VARCHAR 主键ID
ShipmentId VARCHAR 亚马逊货件ID
SellerSKU VARCHAR 产品SKU
inboundplanid VARCHAR 发货计划ID
Quantity INT 计划数量
QuantityShipped INT 发货数量
QuantityReceived INT 接收数量

4.1.2 t_erp_ship_inboundplan（发货计划表）

字段名 类型 说明
id VARCHAR 主键ID
amazongroupid VARCHAR 店铺ID
marketplaceid VARCHAR 市场ID
warehouseid VARCHAR 仓库ID
shopid VARCHAR 公司ID
auditstatus INT 审核状态
createdate DATETIME 创建日期

4.1.3 t_erp_ship_inboundshipment（货件表）

字段名 类型 说明
ShipmentId VARCHAR 货件ID（主键）
inboundplanid VARCHAR 发货计划ID
status INT 状态码
shiped_date DATETIME 发货日期
start_receive_date DATETIME 开始接收日期
boxnum INT 箱数

4.1.4 t_erp_ship_inboundtrans（货件运输表）

字段名 类型 说明
shipmentid VARCHAR 货件ID
channel VARCHAR 渠道ID
transweight DECIMAL 运输重量
wunit VARCHAR 重量单位
singleprice DECIMAL 单价
otherfee DECIMAL 其他费用

4.1.5 t_erp_ship_transdetail（运输详情表）

字段名 类型 说明
id VARCHAR 主键ID
company VARCHAR 承运商ID
channel VARCHAR 渠道ID
channame VARCHAR 渠道名称
transtype VARCHAR 运输方式

4.1.6 t_erp_ship_transcompany（承运商表）

字段名 类型 说明
id VARCHAR 承运商ID（主键）
name VARCHAR 承运商名称

4.2 数据关系图

t_erp_ship_inboundplan (发货计划)
    ├── t_erp_ship_inbounditem (货件明细)
    │       └── t_erp_material (产品信息)
    └── t_erp_ship_inboundshipment (货件)
            ├── t_erp_ship_inboundtrans (运输信息)
            │       └── t_erp_ship_transdetail (渠道详情)
            │               └── t_erp_ship_transcompany (承运商)
            └── t_erp_warehouse (仓库)

5. API接口文档

5.1 获取货件统计报表

接口地址：POST /api/v1/ship/report/getShipmentReportByLoistics

请求参数：

{
  "currentpage": 1,
  "pagesize": 20,
  "groupby": ["channeldetailid", "warehouse", "warehouseid", "groupid", "marketplaceid", "sku", "shipmentid"],
  "companyid": "承运商ID",
  "channelid": "渠道ID",
  "warehouseid": "仓库ID",
  "search": "SKU搜索关键词",
  "datetype": "createdate",
  "fromDate": "2026-01-01",
  "toDate": "2026-01-31",
  "type": "logitics",
  "groupid": "店铺ID",
  "marketplaceid": "市场ID"
}

响应参数：

{
  "code": 200,
  "msg": "success",
  "data": {
    "records": [
      {
        "market": "US",
        "warehousename": "深圳仓",
        "logitics": "DHL",
        "channame": "DHL快递",
        "subarea": "北美",
        "channelname": "快递",
        "transtype": "AIR",
        "totalqty": 1000,
        "totalout": 800,
        "totalrec": 750,
        "lessrec": -50,
        "needout": 200,
        "needrec": 250,
        "worth": 50000.00,
        "readweight": 150.5,
        "transweight_kg": 145.2,
        "transweight_cbm": 0.8,
        "totalbox": 20,
        "shipfee": 1500.00,
        "totalotherfee": 200.00,
        "avgtime": 5.5,
        "shipmentnum": 5,
        "problem": 0,
        "summary": {
          "totalqty": 5000,
          "totalout": 4000,
          "totalrec": 3800,
          "lessrec": -200,
          "worth": 250000.00,
          "shipfee": 7500.00
        }
      }
    ],
    "total": 100,
    "size": 20,
    "current": 1,
    "pages": 5
  }
}

5.2 获取仓库物流统计

接口地址：POST /api/v1/ship/report/getShipmentReportByWarehouseLoistics

请求参数：

{
  "groupby": ["warehouseid"],
  "companyid": "承运商ID",
  "warehouseid": "仓库ID",
  "datetype": "createdate",
  "fromDate": "2026-01-01",
  "toDate": "2026-01-31"
}

响应参数：

{
  "code": 200,
  "msg": "success",
  "data": {
    "warehouse1": [
      {
        "warehouseid": "WH001",
        "warehouse": "深圳仓",
        "transweight_kg": 500.5,
        "shipfee": 3000.00
      }
    ],
    "warehouse2": [
      {
        "warehouseid": "WH002",
        "warehouse": "广州仓",
        "transweight_kg": 300.2,
        "shipfee": 1800.00
      }
    ]
  }
}

5.3 导出统计报表

接口地址：POST /api/v1/ship/report/downExcelShipmentReportByLoistics

请求参数：同5.1

响应：Excel文件流

6. 业务流程

6.1 数据查询流程

用户操作
    ↓
前端构建查询参数（分组、筛选条件）
    ↓
调用 getShipmentReportByLoistics API
    ↓
控制器处理参数，构建查询条件
    ↓
服务层调用 Mapper
    ↓
Mapper 执行 SQL 查询
    ↓
数据库返回查询结果
    ↓
服务层处理结果，添加汇总数据
    ↓
控制器返回 JSON 响应
    ↓
前端渲染表格和图表

6.2 数据计算逻辑

发货指标计算：

  • 计划发货（totalqty）：SUM(产品数量)
  • 实际发货（totalout）：SUM(状态为已发货的货件的发货数量)
  • 实际接收（totalrec）：SUM(状态为已发货的货件的接收数量)
  • 接收差值（lessrec）：SUM(实际发货 - 实际接收)
  • 待发货（needout）：SUM(计划发货 - 实际发货)
  • 待接收（needrec）：SUM(实际发货 - 实际接收)

运输指标计算：

  • 预估运输重量（readweight）：SUM(产品重量 × 发货数量)
  • 发货运输重量（transweight_kg）：SUM(计费重量，单位kg)
  • 发货运输体积（transweight_cbm）：SUM(计费体积，单位cbm)
  • 运输费用（shipfee）：SUM(单价 × 重量 + 其他费用)
  • 平均时效（avgtime）：AVG(接收日期 - 发货日期)

7. 技术亮点

7.1 前端技术亮点

  1. 动态分组：通过el-checkbox-group动态控制分组维度，表格列根据分组条件动态显示
  2. 饼图可视化：使用PieChart组件展示数据分布，支持切换不同的汇总指标
  3. 合计行：使用el-table的show-summary和summary-method实现自动计算合计行
  4. 条件渲染：使用v-if指令根据分组条件动态渲染表格列
  5. 响应式设计：使用el-space布局组件，支持不同屏幕尺寸

7.2 后端技术亮点

  1. 动态SQL：使用MyBatis的动态SQL，根据分组条件动态构建查询语句
  2. 多表关联：使用LEFT JOIN关联多个表，实现复杂的数据统计
  3. 聚合计算：使用SUM、AVG等聚合函数进行数据统计
  4. SXSSFWorkbook：使用Apache POI的SXSSFWorkbook流式处理大数据量Excel导出
  5. 参数校验：对输入参数进行非空检查和格式转换

7.3 性能优化

  1. 索引优化：在关键字段上建立索引，提高查询速度
  2. 分页查询：使用分页功能避免一次性加载大量数据
  3. 缓存机制：缓存常用的查询结果
  4. 懒加载：饼图数据使用单独的请求加载

8. 扩展功能

8.1 分组维度扩展

当前支持7种分组维度，可扩展支持更多维度：

  • 按月/周分组
  • 按产品类别分组
  • 按运输方式分组

8.2 统计指标扩展

当前支持15种统计指标，可扩展支持更多指标：

  • 利润率
  • 退货率
  • 库存周转率

8.3 图表类型扩展

当前使用饼图展示数据分布，可扩展支持：

  • 折线图（趋势分析）
  • 柱状图（对比分析）
  • 地图（地理分布）

9. 注意事项

9.1 数据同步

  • 货件数据通过亚马逊API同步，可能存在延迟
  • 建议在发货后24小时再查看统计数据
  • 接收数据可能需要更长时间才能同步完成

9.2 权限控制

  • 用户只能查看有权限的店铺和仓库数据
  • 权限由系统管理员设置

9.3 性能考虑

  • 避免选择过大的日期范围
  • SKU分组会显著增加数据量
  • 大量数据导出可能需要较长时间

文档版本：v1.0
最后更新：2026-01-26
适用系统：Wimoor FBA发货管理系统

回到顶部