package com.trafficaudit.reportexport.calc;
|
|
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
|
import com.trafficaudit.common.util.RegionUtil;
|
import com.trafficaudit.dataimport.entity.CityTaxiMonthly;
|
import com.trafficaudit.dataimport.entity.WycOrderMonthly;
|
import com.trafficaudit.dataimport.entity.WycTotalMonthly;
|
import com.trafficaudit.dataimport.mapper.CityTaxiMonthlyMapper;
|
import com.trafficaudit.dataimport.mapper.WycOrderMonthlyMapper;
|
import com.trafficaudit.dataimport.mapper.WycTotalMonthlyMapper;
|
import org.apache.poi.ss.usermodel.Cell;
|
import org.apache.poi.ss.usermodel.CellType;
|
import org.apache.poi.ss.usermodel.DataFormatter;
|
import org.apache.poi.ss.usermodel.Row;
|
import org.apache.poi.ss.usermodel.Workbook;
|
import org.apache.poi.ss.usermodel.WorkbookFactory;
|
import org.springframework.beans.factory.annotation.Value;
|
import org.springframework.stereotype.Service;
|
|
import javax.annotation.Resource;
|
import java.io.File;
|
import java.io.FileInputStream;
|
import java.io.InputStream;
|
import java.util.ArrayList;
|
import java.util.LinkedHashMap;
|
import java.util.List;
|
import java.util.Map;
|
|
/**
|
* 网约车拆分口径计算服务（汇总大表数据驱动改造 M1-网约车，2026-09-08）。
|
* 算法与 exportWycSplit / 《网约车拆分.xlsx》月页一致（B5：订单+全省 pin+巡游出租拆分）：
|
* 客运量=订单占比×全省总量；城市内=巡游出租城市内占比代理（武汉保持自身、其余按剩余占比分摊到全省城市内 pin）；
|
* 周转量=客运量×巡游出租平均运距占比分摊全省周转量 pin；城市内周转量同法（用城市内平均运距）。
|
* 2026-07 与 _备份…_清理前_20260904.xlsx 网约车台账页 O 列回归 0 差异（17 市州×客运量/周转量/城市内客运量/城市内周转量）。
|
*/
|
@Service
|
public class WycSplitCalc {
|
|
/** 单市州拆分结果（含城际城乡口径，供不同台账行使用） */
|
public static class WycMetrics {
|
private final double totalPax; // 客运量总量（万人）
|
private final double cityPax; // 城市内客运量（万人）
|
private final double suburbanPax; // 城际城乡客运量 = totalPax - cityPax
|
private final double totalTurnover; // 旅客周转量总量（万人公里）
|
private final double cityTurnover; // 城市内旅客周转量（万人公里）
|
private final double suburbanTurnover;
|
|
public WycMetrics(double totalPax, double cityPax, double totalTurnover, double cityTurnover) {
|
this.totalPax = totalPax;
|
this.cityPax = cityPax;
|
this.suburbanPax = totalPax - cityPax;
|
this.totalTurnover = totalTurnover;
|
this.cityTurnover = cityTurnover;
|
this.suburbanTurnover = totalTurnover - cityTurnover;
|
}
|
|
public double getTotalPax() { return totalPax; }
|
public double getCityPax() { return cityPax; }
|
public double getSuburbanPax() { return suburbanPax; }
|
public double getTotalTurnover() { return totalTurnover; }
|
public double getCityTurnover() { return cityTurnover; }
|
public double getSuburbanTurnover() { return suburbanTurnover; }
|
}
|
|
public static class WycResult {
|
private final String period;
|
private final Map<String, WycMetrics> byCity; // key=RegionUtil 规范市州名
|
private final WycMetrics province; // 全省 = pin 输入
|
|
public WycResult(String period, Map<String, WycMetrics> byCity, WycMetrics province) {
|
this.period = period;
|
this.byCity = byCity;
|
this.province = province;
|
}
|
|
public String getPeriod() { return period; }
|
public Map<String, WycMetrics> getByCity() { return byCity; }
|
public WycMetrics getProvince() { return province; }
|
}
|
|
@Resource
|
private CityTaxiMonthlyMapper cityTaxiMapper;
|
@Resource
|
private WycOrderMonthlyMapper wycOrderMapper;
|
@Resource
|
private WycTotalMonthlyMapper wycTotalMapper;
|
@Value("${city-passenger.template-dir:docs/城市客运}")
|
private String templateDir;
|
|
private static final String[] INPUT_CANDIDATES = {"网约车订单及全省总量.xlsx", "模板_网约车订单及全省总量.xlsx"};
|
|
/** 计算 period（如 2026-07）网约车拆分结果 */
|
public WycResult calc(String period) throws Exception {
|
Map<String, Object> input = loadMonthlyInput(period);
|
if (input == null) {
|
throw new RuntimeException("《网约车订单及全省总量.xlsx》缺少报表期 " + period + " 的数据行，请先维护当月订单与全省 pin");
|
}
|
double pinK = (Double) input.get("pinK");
|
double pinCK = (Double) input.get("pinCK");
|
double pinZ = (Double) input.get("pinZ");
|
double pinCZ = (Double) input.get("pinCZ");
|
double orderSum = (Double) input.get("orderSum");
|
double[] orderArr = (double[]) input.get("orders");
|
if (pinK <= 0 || pinCK <= 0 || pinZ <= 0 || pinCZ <= 0 || orderSum <= 0) {
|
throw new RuntimeException("网约车输入 " + period + " 行不完整：全省 pin / 订单缺失，请核对《网约车订单及全省总量.xlsx》");
|
}
|
List<String> cities = RegionUtil.cityList();
|
int n = cities.size();
|
if (orderArr == null || orderArr.length < n) {
|
throw new RuntimeException("网约车订单列数不足（应为 17 市州订单列）");
|
}
|
Map<String, double[]> taxi = taxiMonthlyOf(period);
|
List<String> missingTaxi = new ArrayList<>();
|
double[] Dtax = new double[n];
|
double[] Etax = new double[n];
|
double[] avgD = new double[n];
|
double[] avgDc = new double[n];
|
for (int i = 0; i < n; i++) {
|
double[] a = taxi.get(cities.get(i));
|
if (a == null || a[0] <= 0) {
|
missingTaxi.add(cities.get(i));
|
continue;
|
}
|
Dtax[i] = a[0];
|
Etax[i] = a[2];
|
avgD[i] = a[4];
|
avgDc[i] = a[5];
|
}
|
if (!missingTaxi.isEmpty()) {
|
throw new RuntimeException("巡游出租月报缺少以下市州数据：" + String.join("、", missingTaxi));
|
}
|
double[] C = new double[n];
|
double[] H = new double[n];
|
double[] Gl = new double[n];
|
double[] Hl = new double[n];
|
double sumG = 0.0;
|
double sumDl = 0.0;
|
double[] cityRatio = new double[n];
|
for (int i = 0; i < n; i++) {
|
C[i] = orderArr[i] / orderSum * pinK;
|
cityRatio[i] = Etax[i] / Dtax[i];
|
sumG += C[i] * cityRatio[i];
|
sumDl += C[i] * avgD[i];
|
}
|
for (int i = 0; i < n; i++) {
|
if (i == 0) {
|
H[i] = C[i] * cityRatio[i];
|
} else {
|
double rest = sumG - C[0] * cityRatio[0];
|
H[i] = rest == 0.0 ? 0.0 : (C[i] * cityRatio[i]) / rest * (pinCK - C[0] * cityRatio[0]);
|
}
|
}
|
double[] el2 = new double[n];
|
double sumEl2 = 0.0;
|
for (int i = 0; i < n; i++) {
|
el2[i] = H[i] * avgDc[i];
|
sumEl2 += el2[i];
|
Gl[i] = sumDl == 0.0 ? 0.0 : C[i] * avgD[i] / sumDl * pinZ;
|
}
|
for (int i = 0; i < n; i++) {
|
if (i == 0) {
|
Hl[i] = Gl[i];
|
} else {
|
double rest = sumEl2 - el2[0];
|
Hl[i] = rest == 0.0 ? 0.0 : el2[i] / rest * (pinCZ - Gl[0]);
|
}
|
}
|
Map<String, WycMetrics> byCity = new LinkedHashMap<>();
|
for (int i = 0; i < n; i++) {
|
byCity.put(cities.get(i), new WycMetrics(C[i], H[i], Gl[i], Hl[i]));
|
}
|
WycMetrics province = new WycMetrics(pinK, pinCK, pinZ, pinCZ);
|
return new WycResult(period, byCity, province);
|
}
|
|
/** 巡游出租月报（DB）按月加载：市州 -> [客运量,周转量,城市内客运量,城市内周转量,平均运距,城市内平均运距] */
|
private Map<String, double[]> taxiMonthlyOf(String period) {
|
Map<String, double[]> map = new LinkedHashMap<>();
|
List<CityTaxiMonthly> rows = cityTaxiMapper.selectList(
|
new LambdaQueryWrapper<CityTaxiMonthly>().eq(CityTaxiMonthly::getReportPeriod, period));
|
for (CityTaxiMonthly r : rows) {
|
String city = RegionUtil.normalizeCityName(r.getCity());
|
if (city == null || city.isEmpty()) city = "未知";
|
double[] a = map.computeIfAbsent(city, k -> new double[6]);
|
a[0] += nz(r.getPassengerVolume());
|
a[1] += nz(r.getTurnover());
|
a[2] += nz(r.getPassengerCity());
|
a[3] += nz(r.getTurnoverCity());
|
if (r.getAvgDistance() != null && r.getAvgDistance() > 0) a[4] = r.getAvgDistance();
|
if (r.getAvgDistanceCity() != null && r.getAvgDistanceCity() > 0) a[5] = r.getAvgDistanceCity();
|
}
|
for (double[] a : map.values()) {
|
// 平均运距兜底：全精度保留，展示位数交给单元格格式
|
if (a[4] <= 0 && a[0] > 0) a[4] = a[1] / a[0];
|
if (a[5] <= 0 && a[2] > 0) a[5] = a[3] / a[2];
|
}
|
return map;
|
}
|
|
/** 读取 docs/城市客运/《网约车订单及全省总量.xlsx》：按 period(yyyy-MM) 行取 pin(B-E) 与 17 市州订单(G-W) */
|
private Map<String, Object> loadMonthlyInput(String period) throws Exception {
|
Map<String, Object> db = loadMonthlyInputFromDb(period);
|
if (db != null) return db;
|
File f = findInput();
|
try (InputStream in = new FileInputStream(f);
|
Workbook wb = WorkbookFactory.create(in)) {
|
org.apache.poi.ss.usermodel.Sheet sh = wb.getSheetAt(0);
|
DataFormatter df = new DataFormatter();
|
List<String> cities = RegionUtil.cityList();
|
int n = cities.size();
|
for (int i = 1; i <= sh.getLastRowNum(); i++) {
|
Row row = sh.getRow(i);
|
if (row == null) continue;
|
Cell c0 = row.getCell(0);
|
if (c0 == null) continue;
|
String m = df.formatCellValue(c0).trim();
|
if (m.isEmpty()) continue;
|
if (m.length() > 7) m = m.substring(0, 7);
|
if (!period.equals(m)) continue;
|
Map<String, Object> map = new LinkedHashMap<>();
|
map.put("pinK", cellNum(row, 1, df, 0.0));
|
map.put("pinCK", cellNum(row, 2, df, 0.0));
|
map.put("pinZ", cellNum(row, 3, df, 0.0));
|
map.put("pinCZ", cellNum(row, 4, df, 0.0));
|
double[] orders = new double[n];
|
double orderSum = 0.0;
|
for (int k = 0; k < n; k++) {
|
orders[k] = cellNum(row, 6 + k, df, 0.0);
|
orderSum += orders[k];
|
}
|
if (orderSum <= 0) orderSum = cellNum(row, 5, df, 0.0);
|
map.put("orders", orders);
|
map.put("orderSum", orderSum);
|
return map;
|
}
|
return null;
|
}
|
}
|
|
/** 优先从库取数（wyc_order_monthly）；库里无该期或订单全空时返回 null，回退读母版文件 */
|
private Map<String, Object> loadMonthlyInputFromDb(String period) {
|
WycOrderMonthly row = wycOrderMapper.selectOne(new LambdaQueryWrapper<WycOrderMonthly>()
|
.eq(WycOrderMonthly::getReportPeriod, period).last("LIMIT 1"));
|
if (row == null) return null;
|
List<String> cities = RegionUtil.cityList();
|
Double[] vals = {
|
row.getOrderWuhan(), row.getOrderHuangshi(), row.getOrderShiyan(), row.getOrderYichang(),
|
row.getOrderXiangyang(), row.getOrderEzhou(), row.getOrderJingmen(), row.getOrderXiaogan(),
|
row.getOrderJingzhou(), row.getOrderHuanggang(), row.getOrderXianning(), row.getOrderSuizhou(),
|
row.getOrderEnshi(), row.getOrderXiantao(), row.getOrderQianjiang(), row.getOrderTianmen(),
|
row.getOrderShennong()
|
};
|
double[] orders = new double[cities.size()];
|
double sum = 0.0;
|
for (int i = 0; i < orders.length && i < vals.length; i++) {
|
orders[i] = vals[i] == null ? 0.0 : vals[i];
|
sum += orders[i];
|
}
|
if (sum <= 0) return null;
|
double orderSum = sum;
|
if (row.getOrderSum() != null && row.getOrderSum() > 0) orderSum = row.getOrderSum();
|
double pinK = row.getPinK() == null ? 0.0 : row.getPinK();
|
double pinCK = row.getPinCK() == null ? 0.0 : row.getPinCK();
|
double pinZ = row.getPinZ() == null ? 0.0 : row.getPinZ();
|
double pinCZ = row.getPinCZ() == null ? 0.0 : row.getPinCZ();
|
boolean pinFix = false;
|
// pin 兜底：订单表 pin 列为空/为 0 时回落 wyc_total_monthly（pin 专表）
|
if (pinK <= 0 || pinCK <= 0 || pinZ <= 0 || pinCZ <= 0) {
|
WycTotalMonthly tot = wycTotalMapper.selectOne(new LambdaQueryWrapper<WycTotalMonthly>()
|
.eq(WycTotalMonthly::getReportPeriod, period).last("LIMIT 1"));
|
if (tot != null) {
|
if (pinK <= 0 && tot.getPinK() != null && tot.getPinK() > 0) { pinK = tot.getPinK(); pinFix = true; }
|
if (pinCK <= 0 && tot.getPinCK() != null && tot.getPinCK() > 0) { pinCK = tot.getPinCK(); pinFix = true; }
|
if (pinZ <= 0 && tot.getPinZ() != null && tot.getPinZ() > 0) { pinZ = tot.getPinZ(); pinFix = true; }
|
if (pinCZ <= 0 && tot.getPinCZ() != null && tot.getPinCZ() > 0) { pinCZ = tot.getPinCZ(); pinFix = true; }
|
}
|
}
|
Map<String, Object> map = new LinkedHashMap<>();
|
map.put("pinK", pinK);
|
map.put("pinCK", pinCK);
|
map.put("pinZ", pinZ);
|
map.put("pinCZ", pinCZ);
|
map.put("orders", orders);
|
map.put("orderSum", orderSum);
|
map.put("source", pinFix ? "db+wyc_total" : "db");
|
return map;
|
}
|
private File findInput() throws Exception {
|
String rel = templateDir;
|
while (rel.startsWith("./")) rel = rel.substring(2);
|
String[] roots = {
|
templateDir,
|
System.getProperty("user.dir") + "/" + rel,
|
System.getProperty("user.dir") + "/../" + rel
|
};
|
for (String root : roots) {
|
if (root == null || root.trim().isEmpty()) continue;
|
for (String name : INPUT_CANDIDATES) {
|
File f = new File(root, name);
|
if (f.exists() && f.isFile()) return f;
|
}
|
}
|
throw new RuntimeException("未找到《网约车订单及全省总量.xlsx》（含 模板_ 前缀命名），请放到 docs/城市客运 目录");
|
}
|
|
private double cellNum(Row row, int idx, DataFormatter df, double def) {
|
Cell c = row.getCell(idx);
|
if (c == null) return def;
|
if (c.getCellType() == CellType.NUMERIC) return c.getNumericCellValue();
|
String s = df.formatCellValue(c).replace(",", "").trim();
|
if (s.isEmpty()) return def;
|
try {
|
return Double.parseDouble(s);
|
} catch (NumberFormatException e) {
|
return def;
|
}
|
}
|
|
private double nz(Double v) { return v == null ? 0.0 : v; }
|
|
}
|
