From 603853a1290e7d71297c9ce5b64181a090adab87 Mon Sep 17 00:00:00 2001
From: zhizhijie <zhizhijie@users.noreply.gitee.com>
Date: 星期二, 08 九月 2026 17:44:50 +0800
Subject: [PATCH] docs: 09-08 工作日结补记下班前状态（最新代码已重启就绪，明日待办8月填值/9月整链/临时文件清理）
---
traffic-audit-server/src/main/java/com/trafficaudit/reportexport/calc/WycSplitCalc.java | 266 +++++++++++++++++++++++++++++++++++++++++++++++++++++
1 files changed, 266 insertions(+), 0 deletions(-)
diff --git a/traffic-audit-server/src/main/java/com/trafficaudit/reportexport/calc/WycSplitCalc.java b/traffic-audit-server/src/main/java/com/trafficaudit/reportexport/calc/WycSplitCalc.java
new file mode 100644
index 0000000..baf3d26
--- /dev/null
+++ b/traffic-audit-server/src/main/java/com/trafficaudit/reportexport/calc/WycSplitCalc.java
@@ -0,0 +1,266 @@
+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.mapper.CityTaxiMonthlyMapper;
+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;
+ @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);
+ }
+
+ /** 宸℃父鍑虹鏈堟姤锛圖B锛夋寜鏈堝姞杞斤細甯傚窞 -> [瀹㈣繍閲�,鍛ㄨ浆閲�,鍩庡競鍐呭杩愰噺,鍩庡競鍐呭懆杞噺,骞冲潎杩愯窛,鍩庡競鍐呭钩鍧囪繍璺漖 */
+ 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] = round(a[1] / a[0], 2);
+ if (a[5] <= 0 && a[2] > 0) a[5] = round(a[3] / a[2], 2);
+ }
+ return map;
+ }
+
+ /** 璇诲彇 docs/鍩庡競瀹㈣繍/銆婄綉绾﹁溅璁㈠崟鍙婂叏鐪佹�婚噺.xlsx銆嬶細鎸� period(yyyy-MM) 琛屽彇 pin(B-E) 涓� 17 甯傚窞璁㈠崟(G-W) */
+ private Map<String, Object> loadMonthlyInput(String period) throws Exception {
+ 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;
+ }
+ }
+
+ 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; }
+
+ private double round(double v, int scale) {
+ double factor = Math.pow(10, scale);
+ return Math.round(v * factor) / factor;
+ }
+}
--
Gitblit v1.9.1