package com.trafficaudit.reportexport.service;
|
|
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
|
import com.trafficaudit.common.util.RegionUtil;
|
import com.trafficaudit.dataimport.entity.FreightTurnoverImport;
|
import com.trafficaudit.dataimport.entity.H2032EnterpriseMonthly;
|
import com.trafficaudit.dataimport.entity.ScaleSplitTransport;
|
import com.trafficaudit.dataimport.mapper.FreightTurnoverImportMapper;
|
import com.trafficaudit.dataimport.mapper.H2032EnterpriseMonthlyMapper;
|
import com.trafficaudit.dataimport.mapper.ScaleSplitTransportMapper;
|
import lombok.extern.slf4j.Slf4j;
|
import org.apache.poi.ss.usermodel.Cell;
|
import org.apache.poi.ss.usermodel.CellStyle;
|
import org.apache.poi.ss.usermodel.Row;
|
import org.apache.poi.ss.usermodel.Sheet;
|
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
|
import org.springframework.stereotype.Service;
|
|
import javax.annotation.Resource;
|
import java.io.ByteArrayOutputStream;
|
import java.util.HashMap;
|
import java.util.List;
|
import java.util.Map;
|
|
/**
|
* 报表生成服务：3 类输出报表
|
* 货运量口径：合计=模板_货运量周转量（左半今年1-N月，右半去年1-12月）；规上=H203-2按17市州汇总；规下=合计-规上
|
* 周转量口径：全部来自规上规下拆分表（用户手动上传，含忠实导入的全省行）
|
*/
|
@Slf4j
|
@Service
|
public class ReportExportService {
|
|
@Resource
|
private H2032EnterpriseMonthlyMapper h2032Mapper;
|
@Resource
|
private ScaleSplitTransportMapper scaleSplitMapper;
|
@Resource
|
private FreightTurnoverImportMapper freightTurnoverMapper;
|
|
// ==================== 1. 生成_货运量分市州明细.xlsx ====================
|
|
public byte[] exportCityDetail(String period) throws Exception {
|
int currentMonth = parseMonth(period);
|
String year = period.substring(0, 4);
|
Map<String, Map<Integer, ScaleSplitTransport>> monthData = loadMonthData(period);
|
Map<Integer, ScaleSplitTransport> provinceMonthMap = loadProvinceMonthMap(period);
|
ScaleSplitTransport provinceCum = getProvinceCumulative(period);
|
Map<String, ScaleSplitTransport> cumMap = loadCumulativeMap(period);
|
Map<String, Map<Integer, Double>> h2032Freight = loadH2032FreightByMonth(period);
|
Map<String, Double> h2032FreightCum = loadH2032FreightCumulative(period);
|
Map<String, FreightTurnoverImport> ftMap = loadFreightTurnover(period);
|
|
XSSFWorkbook wb = new XSSFWorkbook();
|
Sheet sheet = wb.createSheet("货运量分市州明细");
|
|
Row r1 = sheet.createRow(0);
|
r1.createCell(0).setCellValue("报表期：" + year + "年" + currentMonth + "月");
|
sheet.createRow(1);
|
Row header = sheet.createRow(2);
|
header.createCell(0).setCellValue("地区 名称");
|
header.createCell(1).setCellValue("指标");
|
CellStyle dateStyle = wb.createCellStyle();
|
dateStyle.setDataFormat(wb.getCreationHelper().createDataFormat().getFormat("yyyy-mm-dd"));
|
for (int m = 1; m <= currentMonth; m++) {
|
Cell c = header.createCell(2 + (m - 1) * 2);
|
c.setCellValue(java.sql.Date.valueOf(year + "-" + pad(m) + "-01"));
|
c.setCellStyle(dateStyle);
|
header.createCell(3 + (m - 1) * 2).setCellValue(m + "月与去年同比");
|
}
|
int cumCol = 2 + currentMonth * 2;
|
header.createCell(cumCol).setCellValue("累计周转量");
|
header.createCell(cumCol + 1).setCellValue("同比");
|
sheet.createRow(3);
|
|
int rowIdx = 4;
|
String[][] provinceMetrics = {
|
{"货运量 （万吨）", "freight", "total"},
|
{"货物周转量 （万吨公里）", "turnover", "total"},
|
{"其中规上货运量 (万吨)", "freight", "above"},
|
{"其中规上货物周转量 (万吨公里)", "turnover", "above"},
|
{"其中规下货运量 (万吨)", "freight", "below"},
|
{"其中规下货物周转量 (万吨公里)", "turnover", "below"}
|
};
|
FreightTurnoverImport ftProvince = ftMap.get("湖北省");
|
for (String[] metric : provinceMetrics) {
|
Row row = sheet.createRow(rowIdx++);
|
row.createCell(0).setCellValue("全省");
|
row.createCell(1).setCellValue(metric[0]);
|
for (int m = 1; m <= currentMonth; m++) {
|
setNumeric(row, 2 + (m - 1) * 2, getProvinceMonthValue(provinceMonthMap, ftProvince, m, metric[1], metric[2], h2032Freight));
|
setNumeric(row, 3 + (m - 1) * 2, getProvinceMonthYoy(provinceMonthMap, ftProvince, m, metric[1], metric[2]));
|
}
|
setNumeric(row, cumCol, getProvinceCumValue(provinceCum, ftProvince, metric[1], metric[2], h2032FreightCum, currentMonth));
|
setNumeric(row, cumCol + 1, getProvinceCumYoy(provinceCum, ftProvince, metric[1], metric[2], currentMonth));
|
}
|
|
for (String city : RegionUtil.cityList()) {
|
String[][] metrics = {
|
{"规上+规下货运量(万吨)", "freight", "total"},
|
{"规上货运量 (万吨)", "freight", "above"},
|
{"规下货运量 (万吨)", "freight", "below"},
|
{"规上+规下周转量 (万吨公里)", "turnover", "total"},
|
{"规上货物周转量 (万吨公里)", "turnover", "above"},
|
{"规下货物周转量 (万吨公里)", "turnover", "below"}
|
};
|
FreightTurnoverImport ftCity = ftMap.get(city);
|
ScaleSplitTransport cum = cumMap.get(city);
|
for (String[] metric : metrics) {
|
Row row = sheet.createRow(rowIdx++);
|
row.createCell(0).setCellValue(RegionUtil.shortName(city));
|
row.createCell(1).setCellValue(metric[0]);
|
for (int m = 1; m <= currentMonth; m++) {
|
setNumeric(row, 2 + (m - 1) * 2, getCityMonthValue(monthData, ftCity, city, m, metric[1], metric[2], h2032Freight));
|
setNumeric(row, 3 + (m - 1) * 2, getCityMonthYoy(monthData, ftCity, city, m, metric[1], metric[2]));
|
}
|
setNumeric(row, cumCol, getCityCumValue(cum, ftCity, metric[1], metric[2], h2032FreightCum, city, currentMonth));
|
setNumeric(row, cumCol + 1, getCityCumYoy(cum, ftCity, metric[1], metric[2], currentMonth));
|
}
|
}
|
|
autoWidth(sheet, cumCol + 2);
|
return toBytes(wb);
|
}
|
// ==================== 2. 生成_货运量排名.xlsx ====================
|
|
public byte[] exportFreightRank(String period) throws Exception {
|
String year = period.substring(0, 4);
|
int month = parseMonth(period);
|
Map<String, FreightTurnoverImport> ftMap = loadFreightTurnover(period);
|
Map<String, Double> h2032FreightCum = loadH2032FreightCumulative(period);
|
Map<String, Double> h2032FreightYoy = computeH2032Yoy(period);
|
|
Map<String, Double> above = new HashMap<>();
|
Map<String, Double> below = new HashMap<>();
|
Map<String, Double> total = new HashMap<>();
|
Map<String, Double> aboveYoy = new HashMap<>();
|
Map<String, Double> belowYoy = new HashMap<>();
|
Map<String, Double> totalYoy = new HashMap<>();
|
|
double provAbove = 0.0;
|
for (Double v : h2032FreightCum.values()) {
|
if (v != null) provAbove += v;
|
}
|
Double provAboveWan = round(provAbove / 10000.0, 4);
|
Double provTotal = freightCum(ftMap.get("湖北省"), month);
|
Double provBelow = provTotal == null ? null : round(provTotal - provAboveWan, 4);
|
Double provTotalYoy = freightCumYoy(ftMap.get("湖北省"), month);
|
|
for (String city : RegionUtil.cityList()) {
|
Double a = h2032FreightCum.get(city);
|
double aWan = a == null ? 0.0 : round(a / 10000.0, 4);
|
Double t = freightCum(ftMap.get(city), month);
|
above.put(city, aWan);
|
total.put(city, t);
|
below.put(city, t == null ? null : round(t - aWan, 4));
|
aboveYoy.put(city, h2032FreightYoy.get(city));
|
belowYoy.put(city, null);
|
totalYoy.put(city, freightCumYoy(ftMap.get(city), month));
|
}
|
|
XSSFWorkbook wb = new XSSFWorkbook();
|
Sheet sheet = wb.createSheet("货运量排名");
|
|
Row title = sheet.createRow(0);
|
title.createCell(0).setCellValue(year + "年1-" + month + "月全省分市州累计完成公路货运量情况");
|
|
int rowIdx = 1;
|
rowIdx = writeRankHead(sheet, rowIdx, "货运量", "吨");
|
rowIdx = writeFreightRankRows(sheet, rowIdx, null, above, below, total, aboveYoy, belowYoy, totalYoy,
|
provAboveWan, provBelow, provTotal, null, null, provTotalYoy, true);
|
for (String city : RegionUtil.cityList()) {
|
rowIdx = writeFreightRankRows(sheet, rowIdx, city, above, below, total, aboveYoy, belowYoy, totalYoy,
|
provAboveWan, provBelow, provTotal, null, null, provTotalYoy, false);
|
}
|
|
// 本月区块（当月标题 + 累计数据，与样例结构一致）
|
sheet.createRow(rowIdx++);
|
Row title2 = sheet.createRow(rowIdx++);
|
title2.createCell(0).setCellValue(year + "年" + month + "月全省分市州累计完成" + "公路货运量情况");
|
rowIdx = writeRankHead(sheet, rowIdx, "货运量", "吨");
|
rowIdx = writeFreightRankRows(sheet, rowIdx, null, above, below, total, aboveYoy, belowYoy, totalYoy,
|
provAboveWan, provBelow, provTotal, null, null, provTotalYoy, true);
|
for (String city : RegionUtil.cityList()) {
|
rowIdx = writeFreightRankRows(sheet, rowIdx, city, above, below, total, aboveYoy, belowYoy, totalYoy,
|
provAboveWan, provBelow, provTotal, null, null, provTotalYoy, false);
|
}
|
|
// 末尾空行（与样例结构对齐）
|
for (int i = 0; i < 4; i++) sheet.createRow(rowIdx++);
|
|
autoWidth(sheet, 18);
|
return toBytes(wb);
|
}
|
|
// ==================== 3. 生成_周转量排名.xlsx ====================
|
|
public byte[] exportTurnoverRank(String period) throws Exception {
|
String year = period.substring(0, 4);
|
int month = parseMonth(period);
|
Map<String, ScaleSplitTransport> cumMap = loadCumulativeMap(period);
|
ScaleSplitTransport province = getProvinceCumulative(period);
|
|
XSSFWorkbook wb = new XSSFWorkbook();
|
Sheet sheet = wb.createSheet("周转量排名");
|
|
Row title = sheet.createRow(0);
|
title.createCell(0).setCellValue(year + "年1-" + month + "月全省分市州累计完成公路货物运输周转量情况");
|
|
int rowIdx = 1;
|
rowIdx = writeRankHead(sheet, rowIdx, "周转量", "吨公里");
|
rowIdx = writeTurnoverRankRows(sheet, rowIdx, null, cumMap, province, true);
|
for (String city : RegionUtil.cityList()) {
|
rowIdx = writeTurnoverRankRows(sheet, rowIdx, city, cumMap, province, false);
|
}
|
|
// 本月区块（当月标题 + 累计数据，与样例结构一致）
|
sheet.createRow(rowIdx++);
|
Row title2 = sheet.createRow(rowIdx++);
|
title2.createCell(0).setCellValue(year + "年" + month + "月全省分市州累计完成" + "公路货物运输周转量情况");
|
rowIdx = writeRankHead(sheet, rowIdx, "周转量", "吨公里");
|
rowIdx = writeTurnoverRankRows(sheet, rowIdx, null, cumMap, province, true);
|
for (String city : RegionUtil.cityList()) {
|
rowIdx = writeTurnoverRankRows(sheet, rowIdx, city, cumMap, province, false);
|
}
|
|
// 末尾空行（与样例结构对齐）
|
for (int i = 0; i < 4; i++) sheet.createRow(rowIdx++);
|
|
autoWidth(sheet, 18);
|
return toBytes(wb);
|
}
|
// ==================== 数据加载 ====================
|
|
/** 当月数据: city -> month -> record（不含全省） */
|
private Map<String, Map<Integer, ScaleSplitTransport>> loadMonthData(String period) {
|
Map<String, Map<Integer, ScaleSplitTransport>> result = new HashMap<>();
|
List<ScaleSplitTransport> list = scaleSplitMapper.selectList(
|
new LambdaQueryWrapper<ScaleSplitTransport>()
|
.eq(ScaleSplitTransport::getPeriodType, "MONTH"));
|
for (ScaleSplitTransport record : list) {
|
if (record.getReportPeriod() == null) continue;
|
if (record.getReportPeriod().compareTo(period) > 0) continue;
|
String city = RegionUtil.normalizeCityName(record.getRegionName());
|
if (city == null) continue;
|
if ("湖北省".equals(city)) continue;
|
int month = parseMonth(record.getReportPeriod());
|
result.computeIfAbsent(city, k -> new HashMap<>()).put(month, record);
|
}
|
return result;
|
}
|
|
/** 累计数据: city -> 目标期及之前最近一期（不含全省） */
|
private Map<String, ScaleSplitTransport> loadCumulativeMap(String period) {
|
Map<String, ScaleSplitTransport> result = new HashMap<>();
|
List<ScaleSplitTransport> list = scaleSplitMapper.selectList(
|
new LambdaQueryWrapper<ScaleSplitTransport>()
|
.eq(ScaleSplitTransport::getPeriodType, "CUMULATIVE"));
|
for (ScaleSplitTransport record : list) {
|
if (record.getReportPeriod() == null) continue;
|
if (record.getReportPeriod().compareTo(period) > 0) continue;
|
String city = RegionUtil.normalizeCityName(record.getRegionName());
|
if (city == null) continue;
|
if ("湖北省".equals(city)) continue;
|
ScaleSplitTransport existing = result.get(city);
|
if (existing == null || record.getReportPeriod().compareTo(existing.getReportPeriod()) > 0) {
|
result.put(city, record);
|
}
|
}
|
return result;
|
}
|
|
/** 全省当月: month -> record（忠实读取导入的全省行） */
|
private Map<Integer, ScaleSplitTransport> loadProvinceMonthMap(String period) {
|
Map<Integer, ScaleSplitTransport> result = new HashMap<>();
|
List<ScaleSplitTransport> list = scaleSplitMapper.selectList(
|
new LambdaQueryWrapper<ScaleSplitTransport>()
|
.eq(ScaleSplitTransport::getPeriodType, "MONTH"));
|
for (ScaleSplitTransport record : list) {
|
if (record.getReportPeriod() == null) continue;
|
if (record.getReportPeriod().compareTo(period) > 0) continue;
|
String name = RegionUtil.normalizeCityName(record.getRegionName());
|
if (!"湖北省".equals(name)) continue;
|
int month = parseMonth(record.getReportPeriod());
|
result.put(month, record);
|
}
|
return result;
|
}
|
|
/** 全省累计（忠实读取导入的全省行，不求和） */
|
private ScaleSplitTransport getProvinceCumulative(String period) {
|
List<ScaleSplitTransport> list = scaleSplitMapper.selectList(
|
new LambdaQueryWrapper<ScaleSplitTransport>()
|
.eq(ScaleSplitTransport::getPeriodType, "CUMULATIVE"));
|
ScaleSplitTransport best = null;
|
for (ScaleSplitTransport record : list) {
|
if (record.getReportPeriod() == null) continue;
|
if (record.getReportPeriod().compareTo(period) > 0) continue;
|
String name = RegionUtil.normalizeCityName(record.getRegionName());
|
if (!"湖北省".equals(name)) continue;
|
if (best == null || record.getReportPeriod().compareTo(best.getReportPeriod()) > 0) best = record;
|
}
|
return best;
|
}
|
|
/** H2032规上: city -> month -> 货运量(吨) */
|
private Map<String, Map<Integer, Double>> loadH2032FreightByMonth(String period) {
|
Map<String, Map<Integer, Double>> result = new HashMap<>();
|
List<H2032EnterpriseMonthly> list = h2032Mapper.selectList(null);
|
for (H2032EnterpriseMonthly record : list) {
|
if (record.getReportPeriod() == null || record.getReportPeriod().compareTo(period) > 0) continue;
|
String city = RegionUtil.cityByCode(record.getRegionCode());
|
if (city == null) continue;
|
int month = parseMonth(record.getReportPeriod());
|
double freight = record.getFreightTotal() == null ? 0.0 : record.getFreightTotal();
|
result.computeIfAbsent(city, k -> new HashMap<>()).merge(month, freight, Double::sum);
|
}
|
return result;
|
}
|
|
/** H2032规上累计: city -> 货运量(吨) */
|
private Map<String, Double> loadH2032FreightCumulative(String period) {
|
Map<String, Double> result = new HashMap<>();
|
List<H2032EnterpriseMonthly> list = h2032Mapper.selectList(null);
|
for (H2032EnterpriseMonthly record : list) {
|
if (record.getReportPeriod() == null || record.getReportPeriod().compareTo(period) > 0) continue;
|
String city = RegionUtil.cityByCode(record.getRegionCode());
|
if (city == null) continue;
|
double freight = record.getFreightTotal() == null ? 0.0 : record.getFreightTotal();
|
result.merge(city, freight, Double::sum);
|
}
|
return result;
|
}
|
|
/** H2032规上同比（需去年同期的H2032数据，暂无则为空） */
|
private Map<String, Double> computeH2032Yoy(String period) {
|
Map<String, Double> result = new HashMap<>();
|
String lastYear = (Integer.parseInt(period.substring(0, 4)) - 1) + period.substring(4);
|
Map<String, Double> current = loadH2032FreightCumulative(period);
|
Map<String, Double> last = loadH2032FreightCumulative(lastYear);
|
for (Map.Entry<String, Double> entry : current.entrySet()) {
|
double lastVal = last.getOrDefault(entry.getKey(), 0.0);
|
if (lastVal != 0.0) {
|
result.put(entry.getKey(), (entry.getValue() - lastVal) / lastVal);
|
}
|
}
|
return result;
|
}
|
|
/** 模板_货运量周转量: city -> 行数据（含湖北省） */
|
private Map<String, FreightTurnoverImport> loadFreightTurnover(String period) {
|
Map<String, FreightTurnoverImport> result = new HashMap<>();
|
List<FreightTurnoverImport> list = freightTurnoverMapper.selectList(
|
new LambdaQueryWrapper<FreightTurnoverImport>()
|
.eq(FreightTurnoverImport::getReportPeriod, period));
|
for (FreightTurnoverImport e : list) {
|
String city = RegionUtil.normalizeCityName(e.getRegionName());
|
if (city != null) result.put(city, e);
|
}
|
return result;
|
}
|
// ==================== 排名行写入 ====================
|
|
/** 排名表双行表头 */
|
private int writeRankHead(Sheet sheet, int rowIdx, String metric, String unit) {
|
Row h1 = sheet.createRow(rowIdx++);
|
h1.createCell(0).setCellValue("市州");
|
h1.createCell(1).setCellValue("规上" + metric);
|
h1.createCell(4).setCellValue("规上增速");
|
h1.createCell(6).setCellValue("规下" + metric);
|
h1.createCell(9).setCellValue("规下增速");
|
h1.createCell(11).setCellValue("合计" + metric);
|
h1.createCell(14).setCellValue("合计增速");
|
h1.createCell(16).setCellValue("分市州规上规下占比");
|
|
Row h2 = sheet.createRow(rowIdx++);
|
h2.createCell(1).setCellValue("累计完成 (万" + unit + "）");
|
h2.createCell(2).setCellValue("排名");
|
h2.createCell(3).setCellValue("占全省比重");
|
h2.createCell(4).setCellValue("同比");
|
h2.createCell(5).setCellValue("增速 排名");
|
h2.createCell(6).setCellValue("累计完成 (万" + unit + "）");
|
h2.createCell(7).setCellValue("排名");
|
h2.createCell(8).setCellValue("占全省比重");
|
h2.createCell(9).setCellValue("同比");
|
h2.createCell(10).setCellValue("增速排名");
|
h2.createCell(11).setCellValue("累计完成 (万" + unit + "）");
|
h2.createCell(12).setCellValue("排名");
|
h2.createCell(13).setCellValue("占全省比重");
|
h2.createCell(14).setCellValue("同比");
|
h2.createCell(15).setCellValue("增速排名");
|
return rowIdx;
|
}
|
|
/** 货运量排名行：规上=H2032累计、合计=模板累计、规下=合计-规上（单位：万吨） */
|
private int writeFreightRankRows(Sheet sheet, int rowIdx, String city,
|
Map<String, Double> above, Map<String, Double> below, Map<String, Double> total,
|
Map<String, Double> aboveYoy, Map<String, Double> belowYoy, Map<String, Double> totalYoy,
|
Double provAbove, Double provBelow, Double provTotal,
|
Double provAboveYoy, Double provBelowYoy, Double provTotalYoy,
|
boolean isProvince) {
|
Row row = sheet.createRow(rowIdx);
|
row.createCell(0).setCellValue(isProvince ? "全省" : RegionUtil.shortName(city));
|
|
Double a = isProvince ? provAbove : above.get(city);
|
Double b = isProvince ? provBelow : below.get(city);
|
Double t = isProvince ? provTotal : total.get(city);
|
Double ay = isProvince ? provAboveYoy : aboveYoy.get(city);
|
Double by = isProvince ? provBelowYoy : belowYoy.get(city);
|
Double ty = isProvince ? provTotalYoy : totalYoy.get(city);
|
|
setNumeric(row, 1, a);
|
setNumeric(row, 2, isProvince ? null : rankOfMap(above, city));
|
setNumeric(row, 3, isProvince ? null : ratioOf(a, provAbove));
|
setNumeric(row, 4, ay);
|
setNumeric(row, 5, isProvince ? null : yoyRankOfMap(aboveYoy, city));
|
|
setNumeric(row, 6, b);
|
setNumeric(row, 7, isProvince ? null : rankOfMap(below, city));
|
setNumeric(row, 8, isProvince ? null : ratioOf(b, provBelow));
|
setNumeric(row, 9, by);
|
setNumeric(row, 10, isProvince ? null : yoyRankOfMap(belowYoy, city));
|
|
setNumeric(row, 11, t);
|
setNumeric(row, 12, isProvince ? null : rankOfMap(total, city));
|
setNumeric(row, 13, isProvince ? null : ratioOf(t, provTotal));
|
setNumeric(row, 14, ty);
|
setNumeric(row, 15, isProvince ? null : yoyRankOfMap(totalYoy, city));
|
|
if (t != null && t > 0 && a != null) {
|
double share = round(a * 10.0 / t, 1);
|
setNumeric(row, 16, share);
|
setNumeric(row, 17, round(10.0 - share, 1));
|
}
|
return rowIdx + 1;
|
}
|
|
private int writeTurnoverRankRows(Sheet sheet, int rowIdx, String city,
|
Map<String, ScaleSplitTransport> cumMap,
|
ScaleSplitTransport province,
|
boolean isProvince) {
|
if (province == null) province = new ScaleSplitTransport();
|
Row row = sheet.createRow(rowIdx);
|
row.createCell(0).setCellValue(isProvince ? "全省" : RegionUtil.shortName(city));
|
|
ScaleSplitTransport cum = isProvince ? null : cumMap.get(city);
|
Double above = isProvince ? province.getAboveScaleTurnover() : (cum == null ? null : cum.getAboveScaleTurnover());
|
Double below = isProvince ? province.getBelowScaleTurnover() : (cum == null ? null : cum.getBelowScaleTurnover());
|
Double total = isProvince ? province.getTotalTurnover() : (cum == null ? null : cum.getTotalTurnover());
|
|
Double provinceAbove = province.getAboveScaleTurnover();
|
Double provinceBelow = province.getBelowScaleTurnover();
|
Double provinceTotal = province.getTotalTurnover();
|
|
setNumeric(row, 1, above);
|
setNumeric(row, 2, isProvince || cum == null ? null : rankOfTurnover(cumMap, city, 0));
|
setNumeric(row, 3, isProvince ? null : ratioOf(above, provinceAbove));
|
setNumeric(row, 4, isProvince ? province.getAboveScaleYoy() : (cum == null ? null : cum.getAboveScaleYoy()));
|
setNumeric(row, 5, isProvince || cum == null ? null : yoyRankOfTurnover(cumMap, city, 0));
|
|
setNumeric(row, 6, below);
|
setNumeric(row, 7, isProvince || cum == null ? null : rankOfTurnover(cumMap, city, 1));
|
setNumeric(row, 8, isProvince ? null : ratioOf(below, provinceBelow));
|
setNumeric(row, 9, isProvince ? province.getBelowScaleYoy() : (cum == null ? null : cum.getBelowScaleYoy()));
|
setNumeric(row, 10, isProvince || cum == null ? null : yoyRankOfTurnover(cumMap, city, 1));
|
|
setNumeric(row, 11, total);
|
setNumeric(row, 12, isProvince || cum == null ? null : rankOfTurnover(cumMap, city, 2));
|
setNumeric(row, 13, isProvince ? null : ratioOf(total, provinceTotal));
|
setNumeric(row, 14, isProvince ? province.getTotalYoy() : (cum == null ? null : cum.getTotalYoy()));
|
setNumeric(row, 15, isProvince || cum == null ? null : yoyRankOfTurnover(cumMap, city, 2));
|
|
if (total != null && total > 0 && above != null) {
|
double share = round(above * 10.0 / total, 1);
|
setNumeric(row, 16, share);
|
setNumeric(row, 17, round(10.0 - share, 1));
|
}
|
return rowIdx + 1;
|
}
|
// ==================== 明细取值辅助 ====================
|
|
/** 全省当月值：周转量取拆分表，货运量取模板（合计）/H2032（规上）/差值（规下） */
|
private Double getProvinceMonthValue(Map<Integer, ScaleSplitTransport> provinceMonthMap,
|
FreightTurnoverImport ft, int month, String metric, String scale,
|
Map<String, Map<Integer, Double>> h2032Freight) {
|
if ("turnover".equals(metric)) {
|
return getMetric(provinceMonthMap.get(month), metric, scale);
|
}
|
if ("total".equals(scale)) return freightMonth(ft, month);
|
if ("above".equals(scale)) {
|
double sum = 0.0;
|
for (Map<Integer, Double> byMonth : h2032Freight.values()) {
|
Double v = byMonth == null ? null : byMonth.get(month);
|
if (v != null) sum += v;
|
}
|
return round(sum / 10000.0, 4);
|
}
|
Double total = freightMonth(ft, month);
|
Double above = getProvinceMonthValue(provinceMonthMap, ft, month, "freight", "above", h2032Freight);
|
if (total == null) return null;
|
return total - above;
|
}
|
|
/** 全省当月同比：周转量取拆分表，货运量合计取模板（左半vs右半） */
|
private Double getProvinceMonthYoy(Map<Integer, ScaleSplitTransport> provinceMonthMap,
|
FreightTurnoverImport ft, int month, String metric, String scale) {
|
if ("turnover".equals(metric)) {
|
return getYoyMetric(provinceMonthMap.get(month), metric, scale);
|
}
|
if ("total".equals(scale)) return freightYoy(ft, month);
|
return null;
|
}
|
|
/** 全省累计：周转量取拆分表累计，货运量合计=模板1-N月之和，规上=H2032累计，规下=差值 */
|
private Double getProvinceCumValue(ScaleSplitTransport provinceCum, FreightTurnoverImport ft,
|
String metric, String scale, Map<String, Double> h2032FreightCum,
|
int monthCount) {
|
if ("turnover".equals(metric)) {
|
return getMetric(provinceCum, metric, scale);
|
}
|
if ("total".equals(scale)) return freightCum(ft, monthCount);
|
if ("above".equals(scale)) {
|
double sum = 0.0;
|
for (Double v : h2032FreightCum.values()) {
|
if (v != null) sum += v;
|
}
|
return round(sum / 10000.0, 4);
|
}
|
Double total = freightCum(ft, monthCount);
|
Double above = getProvinceCumValue(provinceCum, ft, "freight", "above", h2032FreightCum, monthCount);
|
if (total == null) return null;
|
return total - above;
|
}
|
|
/** 全省累计同比：周转量取拆分表，货运量合计=模板累计同比 */
|
private Double getProvinceCumYoy(ScaleSplitTransport provinceCum, FreightTurnoverImport ft,
|
String metric, String scale, int monthCount) {
|
if ("turnover".equals(metric)) {
|
return getYoyMetric(provinceCum, metric, scale);
|
}
|
if ("total".equals(scale)) return freightCumYoy(ft, monthCount);
|
return null;
|
}
|
|
/** 市州当月值 */
|
private Double getCityMonthValue(Map<String, Map<Integer, ScaleSplitTransport>> monthData,
|
FreightTurnoverImport ft, String city, int month,
|
String metric, String scale,
|
Map<String, Map<Integer, Double>> h2032Freight) {
|
if ("turnover".equals(metric)) {
|
return getMetric(getRecord(monthData, city, month), metric, scale);
|
}
|
if ("total".equals(scale)) return freightMonth(ft, month);
|
if ("above".equals(scale)) {
|
Map<Integer, Double> byMonth = h2032Freight.get(city);
|
Double v = byMonth == null ? null : byMonth.get(month);
|
return v == null ? 0.0 : round(v / 10000.0, 4);
|
}
|
Double total = freightMonth(ft, month);
|
Double above = getCityMonthValue(monthData, ft, city, month, "freight", "above", h2032Freight);
|
if (total == null) return null;
|
return total - above;
|
}
|
|
/** 市州当月同比 */
|
private Double getCityMonthYoy(Map<String, Map<Integer, ScaleSplitTransport>> monthData,
|
FreightTurnoverImport ft, String city, int month,
|
String metric, String scale) {
|
if ("turnover".equals(metric)) {
|
return getYoyMetric(getRecord(monthData, city, month), metric, scale);
|
}
|
if ("total".equals(scale)) return freightYoy(ft, month);
|
return null;
|
}
|
|
/** 市州累计 */
|
private Double getCityCumValue(ScaleSplitTransport cum, FreightTurnoverImport ft,
|
String metric, String scale, Map<String, Double> h2032FreightCum,
|
String city, int monthCount) {
|
if ("turnover".equals(metric)) {
|
return cum == null ? null : getMetric(cum, metric, scale);
|
}
|
if ("total".equals(scale)) return freightCum(ft, monthCount);
|
if ("above".equals(scale)) {
|
Double v = h2032FreightCum.get(city);
|
return v == null ? 0.0 : round(v / 10000.0, 4);
|
}
|
Double total = freightCum(ft, monthCount);
|
Double above = getCityCumValue(cum, ft, "freight", "above", h2032FreightCum, city, monthCount);
|
if (total == null) return null;
|
return total - above;
|
}
|
|
/** 市州累计同比 */
|
private Double getCityCumYoy(ScaleSplitTransport cum, FreightTurnoverImport ft,
|
String metric, String scale, int monthCount) {
|
if ("turnover".equals(metric)) {
|
return cum == null ? null : getYoyMetric(cum, metric, scale);
|
}
|
if ("total".equals(scale)) return freightCumYoy(ft, monthCount);
|
return null;
|
}
|
// ==================== 模板_货运量周转量取值 ====================
|
|
private Double freightMonth(FreightTurnoverImport e, int m) {
|
if (e == null) return null;
|
switch (m) {
|
case 1: return e.getFreightM01();
|
case 2: return e.getFreightM02();
|
case 3: return e.getFreightM03();
|
case 4: return e.getFreightM04();
|
case 5: return e.getFreightM05();
|
case 6: return e.getFreightM06();
|
case 7: return e.getFreightM07();
|
case 8: return e.getFreightM08();
|
case 9: return e.getFreightM09();
|
case 10: return e.getFreightM10();
|
case 11: return e.getFreightM11();
|
case 12: return e.getFreightM12();
|
default: return null;
|
}
|
}
|
|
private Double lastFreightMonth(FreightTurnoverImport e, int m) {
|
if (e == null) return null;
|
switch (m) {
|
case 1: return e.getLastFreightM01();
|
case 2: return e.getLastFreightM02();
|
case 3: return e.getLastFreightM03();
|
case 4: return e.getLastFreightM04();
|
case 5: return e.getLastFreightM05();
|
case 6: return e.getLastFreightM06();
|
case 7: return e.getLastFreightM07();
|
case 8: return e.getLastFreightM08();
|
case 9: return e.getLastFreightM09();
|
case 10: return e.getLastFreightM10();
|
case 11: return e.getLastFreightM11();
|
case 12: return e.getLastFreightM12();
|
default: return null;
|
}
|
}
|
|
/** 模板货运量当月同比 = (今年m - 去年m) / 去年m */
|
private Double freightYoy(FreightTurnoverImport e, int m) {
|
Double cur = freightMonth(e, m);
|
Double last = lastFreightMonth(e, m);
|
if (cur == null || last == null || last == 0.0) return null;
|
return (cur - last) / last;
|
}
|
|
/** 模板货运量累计 = 左半 1..monthCount 之和 */
|
private Double freightCum(FreightTurnoverImport e, int monthCount) {
|
if (e == null) return null;
|
double sum = 0.0;
|
boolean any = false;
|
for (int m = 1; m <= monthCount; m++) {
|
Double v = freightMonth(e, m);
|
if (v != null) {
|
sum += v;
|
any = true;
|
}
|
}
|
return any ? sum : null;
|
}
|
|
/** 模板去年货运量累计 = 右半 1..monthCount 之和（去年1-12月固定，取前N个月） */
|
private Double lastFreightCum(FreightTurnoverImport e, int monthCount) {
|
if (e == null) return null;
|
double sum = 0.0;
|
boolean any = false;
|
for (int m = 1; m <= monthCount; m++) {
|
Double v = lastFreightMonth(e, m);
|
if (v != null) {
|
sum += v;
|
any = true;
|
}
|
}
|
return any ? sum : null;
|
}
|
|
/** 模板货运量累计同比 = (今年1-N累计 - 去年1-N累计) / 去年1-N累计 */
|
private Double freightCumYoy(FreightTurnoverImport e, int monthCount) {
|
Double cur = freightCum(e, monthCount);
|
Double last = lastFreightCum(e, monthCount);
|
if (cur == null || last == null || last == 0.0) return null;
|
return (cur - last) / last;
|
}
|
// ==================== 拆分表取值辅助 ====================
|
|
private Double getMetric(ScaleSplitTransport record, String metric, String scale) {
|
if (record == null) return null;
|
if ("freight".equals(metric)) {
|
if ("above".equals(scale)) return record.getAboveScaleFreight();
|
if ("below".equals(scale)) return record.getBelowScaleFreight();
|
return record.getTotalFreight();
|
} else {
|
if ("above".equals(scale)) return record.getAboveScaleTurnover();
|
if ("below".equals(scale)) return record.getBelowScaleTurnover();
|
return record.getTotalTurnover();
|
}
|
}
|
|
private Double getYoyMetric(ScaleSplitTransport record, String metric, String scale) {
|
if (record == null) return null;
|
if ("above".equals(scale)) return record.getAboveScaleYoy();
|
if ("below".equals(scale)) return record.getBelowScaleYoy();
|
return record.getTotalYoy();
|
}
|
|
private ScaleSplitTransport getRecord(Map<String, Map<Integer, ScaleSplitTransport>> monthData,
|
String city, int month) {
|
Map<Integer, ScaleSplitTransport> map = monthData.get(city);
|
return map == null ? null : map.get(month);
|
}
|
|
private Integer rankOfMap(Map<String, Double> map, String city) {
|
Double self = map.get(city);
|
if (self == null) return null;
|
int rank = 1;
|
for (Double v : map.values()) {
|
if (v != null && v > self) rank++;
|
}
|
return rank;
|
}
|
|
private Integer yoyRankOfMap(Map<String, Double> map, String city) {
|
Double self = map.get(city);
|
if (self == null) return null;
|
int rank = 1;
|
for (Double v : map.values()) {
|
if (v != null && v > self) rank++;
|
}
|
return rank;
|
}
|
|
private Integer rankOfTurnover(Map<String, ScaleSplitTransport> cumMap, String city, int which) {
|
Double self = null;
|
for (Map.Entry<String, ScaleSplitTransport> e : cumMap.entrySet()) {
|
ScaleSplitTransport r = e.getValue();
|
Double v = which == 0 ? r.getAboveScaleTurnover()
|
: which == 1 ? r.getBelowScaleTurnover() : r.getTotalTurnover();
|
if (v == null) continue;
|
if (RegionUtil.normalizeCityName(e.getKey()).equals(RegionUtil.normalizeCityName(city))) self = v;
|
}
|
if (self == null) return null;
|
int rank = 1;
|
for (ScaleSplitTransport r : cumMap.values()) {
|
Double v = which == 0 ? r.getAboveScaleTurnover()
|
: which == 1 ? r.getBelowScaleTurnover() : r.getTotalTurnover();
|
if (v != null && v > self) rank++;
|
}
|
return rank;
|
}
|
|
private Integer yoyRankOfTurnover(Map<String, ScaleSplitTransport> cumMap, String city, int which) {
|
Double self = null;
|
for (Map.Entry<String, ScaleSplitTransport> e : cumMap.entrySet()) {
|
ScaleSplitTransport r = e.getValue();
|
Double v = which == 0 ? r.getAboveScaleYoy()
|
: which == 1 ? r.getBelowScaleYoy() : r.getTotalYoy();
|
if (v == null) continue;
|
if (RegionUtil.normalizeCityName(e.getKey()).equals(RegionUtil.normalizeCityName(city))) self = v;
|
}
|
if (self == null) return null;
|
int rank = 1;
|
for (ScaleSplitTransport r : cumMap.values()) {
|
Double v = which == 0 ? r.getAboveScaleYoy()
|
: which == 1 ? r.getBelowScaleYoy() : r.getTotalYoy();
|
if (v != null && v > self) rank++;
|
}
|
return rank;
|
}
|
|
// ==================== 通用辅助 ====================
|
|
private int parseMonth(String period) {
|
return Integer.parseInt(period.split("-")[1]);
|
}
|
|
private String pad(int m) {
|
return m < 10 ? "0" + m : String.valueOf(m);
|
}
|
|
private double round(double v, int scale) {
|
double factor = Math.pow(10, scale);
|
return Math.round(v * factor) / factor;
|
}
|
|
private Double ratioOf(Double part, Double total) {
|
if (part == null || total == null || total == 0.0) return null;
|
return part / total;
|
}
|
|
private void setNumeric(Row row, int idx, Double value) {
|
Cell cell = row.createCell(idx);
|
if (value != null) cell.setCellValue(value);
|
}
|
|
private void setNumeric(Row row, int idx, Integer value) {
|
if (value != null) setNumeric(row, idx, value.doubleValue());
|
}
|
|
private void autoWidth(Sheet sheet, int colCount) {
|
for (int i = 0; i < colCount; i++) {
|
sheet.setColumnWidth(i, 12 * 256);
|
}
|
sheet.setColumnWidth(0, 16 * 256);
|
sheet.setColumnWidth(1, 22 * 256);
|
}
|
|
private byte[] toBytes(XSSFWorkbook wb) throws Exception {
|
try (ByteArrayOutputStream out = new ByteArrayOutputStream()) {
|
wb.write(out);
|
wb.close();
|
return out.toByteArray();
|
}
|
}
|
}
|
