package com.talentcard.server.service;
|
|
import java.io.ByteArrayOutputStream;
|
import java.io.IOException;
|
import java.math.BigDecimal;
|
import java.math.RoundingMode;
|
import java.time.DayOfWeek;
|
import java.time.LocalDate;
|
import java.time.LocalDateTime;
|
import java.time.YearMonth;
|
import java.util.ArrayList;
|
import java.util.LinkedHashMap;
|
import java.util.List;
|
import java.util.Locale;
|
import java.util.Map;
|
|
import com.talentcard.server.business.PageResult;
|
import com.talentcard.server.business.merchant.MerchantTypeCatalog;
|
import com.talentcard.server.common.BusinessException;
|
import com.talentcard.server.common.ErrorCode;
|
import com.talentcard.server.mapper.MerchantSettlementStatisticsMapper;
|
import org.apache.poi.ss.usermodel.CellStyle;
|
import org.apache.poi.ss.usermodel.FillPatternType;
|
import org.apache.poi.ss.usermodel.IndexedColors;
|
import org.apache.poi.ss.usermodel.Row;
|
import org.apache.poi.ss.usermodel.Sheet;
|
import org.apache.poi.ss.usermodel.Workbook;
|
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
|
import org.springframework.stereotype.Service;
|
import org.springframework.transaction.annotation.Transactional;
|
|
@Service
|
public class MerchantSettlementStatisticsService {
|
private static final int EXPORT_LIMIT = 200000;
|
|
private final MerchantSettlementStatisticsMapper mapper;
|
|
public MerchantSettlementStatisticsService(MerchantSettlementStatisticsMapper mapper) {
|
this.mapper = mapper;
|
}
|
|
@Transactional(readOnly = true)
|
public PageResult<Map<String, Object>> summaries(
|
Long merchantId,
|
String merchantType,
|
String periodType,
|
String startDate,
|
String endDate,
|
int page,
|
int size
|
) {
|
requireMerchantIdWhenSpecified(merchantId);
|
String normalizedPeriod = normalizePeriod(periodType);
|
DateRange range = range(normalizedPeriod, startDate, endDate);
|
int safePage = Math.max(page, 1);
|
int safeSize = Math.min(Math.max(size, 1), 100);
|
List<Map<String, Object>> records = mapper.selectPeriodSummaries(
|
merchantId,
|
merchantTypeCode(merchantType),
|
normalizedPeriod,
|
range.startTime(),
|
range.endTime(),
|
safeSize,
|
(safePage - 1) * safeSize
|
).stream()
|
.map(row -> view(row, normalizedPeriod))
|
.toList();
|
Long total = mapper.countPeriodSummaries(
|
merchantId,
|
merchantTypeCode(merchantType),
|
normalizedPeriod,
|
range.startTime(),
|
range.endTime()
|
);
|
return new PageResult<>(records, safePage, safeSize, total == null ? 0 : total);
|
}
|
|
@Transactional(readOnly = true)
|
public Map<String, Object> overview(Long merchantId, String merchantType, String startDate, String endDate) {
|
requireMerchantIdWhenSpecified(merchantId);
|
DateRange range = range("DAY", startDate, endDate);
|
List<Map<String, Object>> rows = mapper.selectMerchantOverviews(merchantId, merchantTypeCode(merchantType), range.startTime(), range.endTime());
|
Map<String, Object> result = new LinkedHashMap<>();
|
result.put("startDate", range.startDate() == null ? "" : range.startDate().toString());
|
result.put("endDate", range.endDateInclusive() == null ? "" : range.endDateInclusive().toString());
|
result.put("records", rows.stream().map(row -> view(row, "TOTAL")).toList());
|
result.put("total", total(rows));
|
return result;
|
}
|
|
public Map<String, Object> currentMonthOverview(Long merchantId) {
|
YearMonth currentMonth = YearMonth.now();
|
return overview(merchantId, null, currentMonth.atDay(1).toString(), currentMonth.atEndOfMonth().toString());
|
}
|
|
private String merchantTypeCode(String value) {
|
String cleaned = clean(value);
|
return cleaned.isBlank() ? "" : MerchantTypeCatalog.canonicalCode(cleaned);
|
}
|
|
@Transactional(readOnly = true)
|
public Map<String, Object> dashboardCards(Long merchantId) {
|
requireMerchantId(merchantId);
|
LocalDate today = LocalDate.now();
|
LocalDate weekStartDate = today.with(DayOfWeek.MONDAY);
|
Map<String, Object> row = mapper.selectDashboardCards(
|
merchantId,
|
today.atStartOfDay(),
|
today.plusDays(1).atStartOfDay(),
|
weekStartDate.atStartOfDay(),
|
today.plusDays(1).atStartOfDay()
|
);
|
long transactionAmount = longValue(row, "transactionAmountCents");
|
long todayVerificationCount = longValue(row, "todayVerificationCount");
|
long weekVerificationCount = longValue(row, "weekVerificationCount");
|
long subsidyAmount = longValue(row, "subsidyAmountCents");
|
|
Map<String, Object> result = new LinkedHashMap<>();
|
result.put("merchantId", merchantId);
|
result.put("statisticsDate", today.toString());
|
result.put("weekStartDate", weekStartDate.toString());
|
result.put("transactionTotal", amountCard("交易总额", transactionAmount));
|
result.put("todayVerification", countCard("今日核销", todayVerificationCount));
|
result.put("weekVerification", countCard("本周核销", weekVerificationCount));
|
result.put("subsidyTotal", amountCard("补贴总额", subsidyAmount));
|
result.put("transactionAmountCents", transactionAmount);
|
result.put("transactionAmount", yuan(transactionAmount));
|
result.put("todayVerificationCount", todayVerificationCount);
|
result.put("weekVerificationCount", weekVerificationCount);
|
result.put("subsidyAmountCents", subsidyAmount);
|
result.put("subsidyAmount", yuan(subsidyAmount));
|
result.put("hasData", transactionAmount > 0 || todayVerificationCount > 0 || weekVerificationCount > 0 || subsidyAmount > 0);
|
result.put("statisticsBasis", "交易总额统计已支付且已核销订单的实付金额；补贴总额统计政府承担的订单补贴、政府补贴券及酒店免费入住线下补贴，商家自行让利不进入政府补贴结算");
|
return result;
|
}
|
|
@Transactional(readOnly = true)
|
public byte[] exportSettlementDetailsExcel(Long merchantId, String status, String startDate, String endDate) {
|
requireMerchantId(merchantId);
|
DateRange range = range("DAY", startDate, endDate);
|
StatusFilter statusFilter = normalizeExportStatus(status);
|
List<Map<String, Object>> records = mapper.selectSettlementExportDetails(
|
merchantId,
|
statusFilter.orderStatus(),
|
statusFilter.verifyStatus(),
|
range.startTime(),
|
range.endTime(),
|
EXPORT_LIMIT
|
);
|
String[] headers = {
|
"序号", "订单号", "订单类型", "商户名称", "门店名称", "持卡人", "手机号", "人才卡号", "人才卡等级",
|
"商品/服务", "交易金额(元)", "政府补贴(元)", "应结算金额(元)", "优惠券金额(元)", "订单状态", "核销状态", "下单时间", "支付时间", "核销时间", "核销人员"
|
};
|
try (Workbook workbook = new XSSFWorkbook(); ByteArrayOutputStream output = new ByteArrayOutputStream()) {
|
Sheet sheet = workbook.createSheet("结算明细");
|
sheet.createFreezePane(0, 1);
|
CellStyle headerStyle = headerStyle(workbook);
|
Row headerRow = sheet.createRow(0);
|
for (int index = 0; index < headers.length; index++) {
|
headerRow.createCell(index).setCellValue(headers[index]);
|
headerRow.getCell(index).setCellStyle(headerStyle);
|
}
|
int rowIndex = 1;
|
for (Map<String, Object> record : records) {
|
Row row = sheet.createRow(rowIndex);
|
List<String> values = exportValues(rowIndex, record);
|
for (int index = 0; index < values.size(); index++) {
|
row.createCell(index).setCellValue(values.get(index));
|
}
|
rowIndex++;
|
}
|
int[] widths = {8, 26, 18, 28, 24, 16, 18, 24, 16, 28, 16, 16, 18, 16, 14, 14, 24, 24, 24, 16};
|
for (int index = 0; index < widths.length; index++) {
|
sheet.setColumnWidth(index, widths[index] * 256);
|
}
|
workbook.write(output);
|
return output.toByteArray();
|
} catch (IOException exception) {
|
throw new BusinessException(ErrorCode.INTERNAL_ERROR, "结算明细导出失败");
|
}
|
}
|
|
private List<String> exportValues(int index, Map<String, Object> row) {
|
List<String> values = new ArrayList<>();
|
values.add(String.valueOf(index));
|
values.add(text(row, "orderNo", "order_no"));
|
values.add(orderTypeName(text(row, "orderType", "order_type")));
|
values.add(text(row, "merchantName", "merchant_name"));
|
values.add(text(row, "storeName", "store_name"));
|
values.add(text(row, "talentName", "talent_name"));
|
values.add(text(row, "talentMobile", "talent_mobile"));
|
values.add(text(row, "cardNo", "card_no"));
|
values.add(text(row, "cardLevel", "card_level"));
|
values.add(text(row, "productName", "product_name"));
|
values.add(string(yuan(longValue(row, "transactionAmountCents"))));
|
values.add(string(yuan(longValue(row, "subsidyAmountCents"))));
|
values.add(string(yuan(longValue(row, "settlementAmountCents"))));
|
values.add(string(yuan(longValue(row, "couponAmountCents"))));
|
values.add(orderStatusName(intValue(row, "orderStatus")));
|
values.add(verifyStatusName(intValue(row, "verifyStatus")));
|
values.add(string(value(row, "createdAt", "created_at")));
|
values.add(string(value(row, "paidAt", "paid_at")));
|
values.add(string(value(row, "verifyTime", "verify_time")));
|
values.add(text(row, "verifyOperator", "verify_operator"));
|
return values;
|
}
|
|
private Map<String, Object> amountCard(String title, long amountCents) {
|
Map<String, Object> card = new LinkedHashMap<>();
|
card.put("title", title);
|
card.put("amountCents", amountCents);
|
card.put("amount", yuan(amountCents));
|
card.put("unit", "元");
|
return card;
|
}
|
|
private Map<String, Object> countCard(String title, long count) {
|
Map<String, Object> card = new LinkedHashMap<>();
|
card.put("title", title);
|
card.put("count", count);
|
card.put("unit", "笔");
|
return card;
|
}
|
|
private Map<String, Object> total(List<Map<String, Object>> rows) {
|
long orderCount = 0;
|
long transactionAmount = 0;
|
long subsidyAmount = 0;
|
long settlementAmount = 0;
|
long originalAmount = 0;
|
long couponAmount = 0;
|
long subsidyOrderCount = 0;
|
for (Map<String, Object> row : rows) {
|
orderCount += longValue(row, "orderCount");
|
transactionAmount += longValue(row, "transactionAmountCents");
|
subsidyAmount += longValue(row, "subsidyAmountCents");
|
settlementAmount += longValue(row, "settlementAmountCents");
|
originalAmount += longValue(row, "originalAmountCents");
|
couponAmount += longValue(row, "couponAmountCents");
|
subsidyOrderCount += longValue(row, "governmentSubsidyOrderCount");
|
}
|
Map<String, Object> total = new LinkedHashMap<>();
|
total.put("orderCount", orderCount);
|
total.put("transactionAmountCents", transactionAmount);
|
total.put("transactionAmount", yuan(transactionAmount));
|
total.put("subsidyAmountCents", subsidyAmount);
|
total.put("subsidyAmount", yuan(subsidyAmount));
|
total.put("settlementAmountCents", settlementAmount);
|
total.put("settlementAmount", yuan(settlementAmount));
|
total.put("originalAmountCents", originalAmount);
|
total.put("originalAmount", yuan(originalAmount));
|
total.put("couponAmountCents", couponAmount);
|
total.put("couponAmount", yuan(couponAmount));
|
total.put("governmentSubsidyOrderCount", subsidyOrderCount);
|
return total;
|
}
|
|
private Map<String, Object> view(Map<String, Object> row, String periodType) {
|
Map<String, Object> result = new LinkedHashMap<>();
|
if (row.containsKey("periodKey")) {
|
result.put("periodKey", text(row, "periodKey"));
|
result.put("periodType", periodType);
|
}
|
result.put("merchantId", value(row, "merchantId"));
|
result.put("merchantName", text(row, "merchantName"));
|
result.put("merchantType", text(row, "merchantType"));
|
result.put("orderCount", longValue(row, "orderCount"));
|
result.put("transactionAmountCents", longValue(row, "transactionAmountCents"));
|
result.put("transactionAmount", yuan(longValue(row, "transactionAmountCents")));
|
result.put("subsidyAmountCents", longValue(row, "subsidyAmountCents"));
|
result.put("subsidyAmount", yuan(longValue(row, "subsidyAmountCents")));
|
result.put("settlementAmountCents", longValue(row, "settlementAmountCents"));
|
result.put("settlementAmount", yuan(longValue(row, "settlementAmountCents")));
|
result.put("originalAmountCents", longValue(row, "originalAmountCents"));
|
result.put("originalAmount", yuan(longValue(row, "originalAmountCents")));
|
result.put("couponAmountCents", longValue(row, "couponAmountCents"));
|
result.put("couponAmount", yuan(longValue(row, "couponAmountCents")));
|
result.put("governmentSubsidyOrderCount", longValue(row, "governmentSubsidyOrderCount"));
|
result.put("periodStartTime", value(row, "periodStartTime", "startTime"));
|
result.put("periodEndTime", value(row, "periodEndTime", "endTime"));
|
result.put("statisticsBasis", "已支付且已核销订单及酒店免费入住核销记录；补贴额统计政府承担的订单补贴、政府补贴券及酒店免费入住线下补贴，商家自行让利不进入政府补贴结算");
|
return result;
|
}
|
|
private DateRange range(String periodType, String startDate, String endDate) {
|
LocalDate start = parseDate(startDate, "开始日期");
|
LocalDate end = parseDate(endDate, "结束日期");
|
if (start != null && end != null && start.isAfter(end)) {
|
throw new BusinessException(ErrorCode.BAD_REQUEST, "开始日期不能晚于结束日期");
|
}
|
if ("MONTH".equals(periodType)) {
|
LocalDate normalizedStart = start == null ? null : start.withDayOfMonth(1);
|
LocalDate normalizedEnd = end == null ? null : YearMonth.from(end).atEndOfMonth();
|
return new DateRange(
|
normalizedStart,
|
normalizedEnd,
|
normalizedStart == null ? null : normalizedStart.atStartOfDay(),
|
normalizedEnd == null ? null : normalizedEnd.plusDays(1).atStartOfDay()
|
);
|
}
|
return new DateRange(
|
start,
|
end,
|
start == null ? null : start.atStartOfDay(),
|
end == null ? null : end.plusDays(1).atStartOfDay()
|
);
|
}
|
|
private LocalDate parseDate(String value, String field) {
|
String text = clean(value);
|
if (text.isBlank()) {
|
return null;
|
}
|
try {
|
if (text.matches("^\\d{4}-\\d{2}$")) {
|
return YearMonth.parse(text).atDay(1);
|
}
|
return LocalDate.parse(text);
|
} catch (Exception exception) {
|
throw new BusinessException(ErrorCode.BAD_REQUEST, field + "格式不正确，应为yyyy-MM-dd或yyyy-MM");
|
}
|
}
|
|
private StatusFilter normalizeExportStatus(String raw) {
|
String status = clean(raw).toUpperCase(Locale.ROOT).replace("-", "_");
|
if (status.isBlank() || "ALL".equals(status) || "全部".equals(status)) {
|
return new StatusFilter(null, null);
|
}
|
return switch (status) {
|
case "PAID", "已支付" -> new StatusFilter(1, null);
|
case "VERIFIED", "COMPLETED", "已核销", "已完成" -> new StatusFilter(1, 1);
|
case "UNVERIFIED", "PENDING_VERIFICATION", "待核销", "未核销" -> new StatusFilter(1, 0);
|
case "PENDING_PAYMENT", "UNPAID", "待支付", "未支付", "0" -> new StatusFilter(0, null);
|
case "CANCELLED", "REFUNDED", "已取消", "已退款", "2" -> new StatusFilter(2, null);
|
case "1" -> new StatusFilter(1, null);
|
default -> throw new BusinessException(ErrorCode.BAD_REQUEST, "状态参数不正确");
|
};
|
}
|
|
private String normalizePeriod(String value) {
|
String period = clean(value).toUpperCase(Locale.ROOT);
|
if (period.isBlank() || "DAY".equals(period) || "DAILY".equals(period) || "日".equals(period)) {
|
return "DAY";
|
}
|
if ("MONTH".equals(period) || "MONTHLY".equals(period) || "月".equals(period)) {
|
return "MONTH";
|
}
|
throw new BusinessException(ErrorCode.BAD_REQUEST, "统计周期只能为DAY日汇总或MONTH月汇总");
|
}
|
|
private void requireMerchantIdWhenSpecified(Long merchantId) {
|
if (merchantId != null && merchantId <= 0) {
|
throw new BusinessException(ErrorCode.BAD_REQUEST, "商户ID不正确");
|
}
|
}
|
|
private void requireMerchantId(Long merchantId) {
|
if (merchantId == null || merchantId <= 0) {
|
throw new BusinessException(ErrorCode.BAD_REQUEST, "商户ID不正确");
|
}
|
}
|
|
private CellStyle headerStyle(Workbook workbook) {
|
CellStyle style = workbook.createCellStyle();
|
style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
|
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
|
return style;
|
}
|
|
private Object value(Map<String, Object> row, String... keys) {
|
if (row == null) {
|
return null;
|
}
|
for (String key : keys) {
|
if (row.containsKey(key)) {
|
return row.get(key);
|
}
|
}
|
return null;
|
}
|
|
private String text(Map<String, Object> row, String... keys) {
|
Object value = value(row, keys);
|
return value == null ? "" : String.valueOf(value).trim();
|
}
|
|
private long longValue(Map<String, Object> row, String key) {
|
Object value = value(row, key);
|
return value instanceof Number number ? number.longValue() : 0L;
|
}
|
|
private Integer intValue(Map<String, Object> row, String key) {
|
Object value = value(row, key);
|
return value instanceof Number number ? number.intValue() : null;
|
}
|
|
private BigDecimal yuan(long cents) {
|
return BigDecimal.valueOf(cents, 2).setScale(2, RoundingMode.HALF_UP);
|
}
|
|
private String orderStatusName(Integer status) {
|
if (status == null) {
|
return "未知";
|
}
|
return switch (status) {
|
case 0 -> "待支付";
|
case 1 -> "已支付";
|
case 2 -> "已取消/已退款";
|
default -> "未知";
|
};
|
}
|
|
private String verifyStatusName(Integer status) {
|
if (status == null) {
|
return "未知";
|
}
|
return status == 1 ? "已核销" : "未核销";
|
}
|
|
private String orderTypeName(String orderType) {
|
return switch (clean(orderType).toUpperCase(Locale.ROOT)) {
|
case "STORE_CONSUME" -> "到店消费";
|
case "GROUP_BUY" -> "团购订单";
|
case "HOTEL_RESERVATION" -> "酒店预约";
|
case "HOTEL_FREE" -> "酒店免费入住";
|
default -> clean(orderType);
|
};
|
}
|
|
private String string(Object value) {
|
return value == null ? "" : String.valueOf(value);
|
}
|
|
private String clean(String value) {
|
return value == null ? "" : value.trim();
|
}
|
|
private record DateRange(LocalDate startDate, LocalDate endDateInclusive, LocalDateTime startTime, LocalDateTime endTime) {
|
}
|
|
private record StatusFilter(Integer orderStatus, Integer verifyStatus) {
|
}
|
}
|
