package com.ruoyi.tms.service.impl;
|
|
import java.util.List;
|
|
import com.baomidou.mybatisplus.core.conditions.update.LambdaUpdateWrapper;
|
import com.ruoyi.common.core.domain.AjaxResult;
|
import com.ruoyi.common.core.redis.RedisCache;
|
import com.ruoyi.common.utils.DateUtils;
|
import com.ruoyi.common.utils.StringUtils;
|
import com.ruoyi.common.utils.DictUtils;
|
import com.ruoyi.common.config.RuoYiConfig;
|
import com.ruoyi.system.service.ISysConfigService;
|
import com.ruoyi.tms.domain.*;
|
import com.ruoyi.tms.mapper.*;
|
import org.apache.poi.ss.usermodel.*;
|
import org.apache.poi.ss.util.CellRangeAddress;
|
import org.apache.poi.xssf.streaming.SXSSFSheet;
|
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
|
import org.springframework.beans.factory.annotation.Autowired;
|
import javax.annotation.Resource;
|
import java.io.File;
|
import java.io.FileOutputStream;
|
import java.math.BigDecimal;
|
import java.math.RoundingMode;
|
import java.util.*;
|
|
import org.springframework.transaction.annotation.Transactional;
|
import org.springframework.stereotype.Service;
|
import org.springframework.scheduling.annotation.Async;
|
import org.slf4j.Logger;
|
import org.slf4j.LoggerFactory;
|
import com.ruoyi.common.utils.PageUtils;
|
import com.ruoyi.common.constant.Constants;
|
import com.ruoyi.common.annotation.DataSource;
|
import com.ruoyi.common.enums.DataSourceType;
|
import com.ruoyi.common.core.service.BaseService;
|
|
import com.ruoyi.tms.service.ITmsArBillService;
|
import com.ruoyi.common.core.text.Convert;
|
import org.springframework.beans.factory.annotation.Value;
|
import org.springframework.core.task.AsyncTaskExecutor;
|
import org.springframework.core.task.SimpleAsyncTaskExecutor;
|
import org.springframework.http.HttpEntity;
|
import org.springframework.http.HttpHeaders;
|
import org.springframework.http.HttpMethod;
|
import org.springframework.http.MediaType;
|
import org.springframework.http.ResponseEntity;
|
import org.springframework.web.client.RestTemplate;
|
import com.alibaba.fastjson2.JSON;
|
|
/**
|
* 应收账单Service业务层处理
|
*
|
* @author ruoyi
|
* @date 2025-12-13
|
*/
|
@Service
|
@Transactional(rollbackFor = Exception.class)
|
public class TmsArBillServiceImpl extends BaseService<TmsArBillMapper, TmsArBill> implements ITmsArBillService
|
{
|
protected final Logger logger = LoggerFactory.getLogger(getClass());
|
@Resource
|
private TmsArBillMapper tmsArBillMapper;
|
|
@Resource
|
private TmsArBillItemMapper tmsArBillItemMapper;
|
@Resource
|
private TmsReceivableFeeMapper tmsReceivableFeeMapper;
|
@Resource
|
private TmsReceivableFeeItemMapper tmsReceivableFeeItemMapper;
|
@Resource
|
private TmsTripMapper tmsTripMapper;
|
|
@Autowired
|
private ISysConfigService sysConfigService;
|
|
@Autowired
|
private RedisCache redisCache;
|
|
@Resource
|
private TmsDispatchOrderMapper tmsDispatchOrderMapper;
|
|
@Autowired
|
private RestTemplate restTemplate;
|
|
@Value("${custom.cwxtApi.url}")
|
private String url;
|
|
/**
|
* 查询应收账单
|
*
|
* @param id 应收账单ID
|
* @return 应收账单
|
*/
|
@DataSource(DataSourceType.SLAVE)
|
@Override
|
public TmsArBill selectTmsArBillById(Integer id)
|
{
|
TmsArBill tmsArBill = tmsArBillMapper.selectTmsArBillById(id);
|
tmsArBill.setItems(tmsArBillItemMapper.selectTmsArBillItemList(new TmsArBillItem(){{setBillId( id);setStatus(0);}}));
|
return tmsArBill;
|
}
|
|
/**
|
* 查询应收账单 记录数
|
*
|
* @param tmsArBill 应收账单
|
* @return 应收账单集合
|
*/
|
@DataSource(DataSourceType.SLAVE)
|
@Override
|
public int selectTmsArBillCount(TmsArBill tmsArBill)
|
{
|
return tmsArBillMapper.selectTmsArBillCount(tmsArBill);
|
}
|
|
/**
|
* 查询应收账单列表
|
*
|
* @param tmsArBill 应收账单
|
* @return 应收账单
|
*/
|
@DataSource(DataSourceType.SLAVE)
|
@Override
|
public List<TmsArBill> selectTmsArBillList(TmsArBill tmsArBill)
|
{
|
return tmsArBillMapper.selectTmsArBillList(tmsArBill);
|
}
|
|
/**
|
* 查询应收账单列表 异步 导出
|
*
|
* @param tmsArBill 应收账单
|
* @param exportKey 导出功能的唯一标识
|
* @return 应收账单集合
|
*/
|
@DataSource(DataSourceType.SLAVE)
|
@Async
|
@Override
|
public void export(TmsArBill tmsArBill,String exportKey) {
|
|
super.export(TmsArBill.class,exportKey,"tmsArBillData",(pageNum)->{
|
PageUtils.startPage(pageNum, Constants.EXPORT_PATE_SIZE);
|
return selectTmsArBillList(tmsArBill);
|
});
|
}
|
|
|
/**
|
* 新增应收账单
|
*
|
* @param tmsArBill 应收账单
|
* @return 结果
|
*/
|
@Override
|
public int insertTmsArBill(TmsArBill tmsArBill)
|
{
|
tmsArBill.setCreateTime(DateUtils.getNowDate());
|
return tmsArBillMapper.insertTmsArBill(tmsArBill);
|
}
|
|
@Override
|
public AjaxResult cancelArBill(Integer id) {
|
|
TmsArBillItem billItem = tmsArBillItemMapper.selectTmsArBillItemById(id);
|
if(billItem == null){
|
return AjaxResult.warn("数据不存在");
|
}
|
if(billItem.getStatus() == 1){
|
return AjaxResult.warn("该数据已作废");
|
}
|
billItem.setStatus(1);
|
tmsArBillItemMapper.updateTmsArBillItem(billItem);
|
tmsReceivableFeeMapper.update(new LambdaUpdateWrapper<TmsReceivableFee>()
|
.set(TmsReceivableFee::getStatus,0)
|
.in(TmsReceivableFee::getId, billItem.getArFeeId())
|
);
|
|
tmsArBillMapper.update(new LambdaUpdateWrapper<TmsArBill>()
|
.setSql("settle_amount = settle_amount - " + billItem.getEstimateAmount())
|
.setSql("actual_settlement_amount = actual_settlement_amount - " + billItem.getEstimateAmount())
|
.eq(TmsArBill::getId, billItem.getBillId())
|
);
|
return AjaxResult.success();
|
}
|
|
/**
|
* 新增应收账单[批量]
|
*
|
* @param tmsArBills 应收账单
|
* @return 结果
|
*/
|
@Override
|
public int insertTmsArBillBatch(List<TmsArBill> tmsArBills)
|
{
|
int rows = tmsArBillMapper.insertTmsArBillBatch(tmsArBills);
|
return rows;
|
}
|
|
/**
|
* 修改应收账单
|
*
|
* @param tmsArBill 应收账单
|
* @return 结果
|
*/
|
@Override
|
public int updateTmsArBill(TmsArBill tmsArBill)
|
{
|
tmsArBill.setUpdateTime(DateUtils.getNowDate());
|
return tmsArBillMapper.updateTmsArBill(tmsArBill);
|
}
|
|
/**
|
* 修改应收账单[批量]
|
*
|
* @param tmsArBills 应收账单
|
* @return 结果
|
*/
|
@Override
|
public int updateTmsArBillBatch(List<TmsArBill> tmsArBills){
|
return tmsArBillMapper.updateTmsArBillBatch(tmsArBills);
|
}
|
|
/**
|
* 删除应收账单对象
|
*
|
* @param ids 需要删除的数据ID
|
* @return 结果
|
*/
|
@Override
|
public int deleteTmsArBillByIds(String ids)
|
{
|
return deleteTmsArBillByIds(Convert.toIntArray(ids));
|
}
|
|
/**
|
* 删除应收账单对象
|
*
|
*
|
* @param ids 需要删除的数据ID
|
* @return 结果
|
*/
|
@Override
|
public int deleteTmsArBillByIds(Integer[] ids)
|
{
|
return tmsArBillMapper.deleteTmsArBillByIds(ids);
|
}
|
|
/**
|
* 删除应收账单信息
|
*
|
* @param id 应收账单ID
|
* @return 结果
|
*/
|
@Override
|
public int deleteTmsArBillById(Integer id)
|
{
|
return tmsArBillMapper.deleteTmsArBillById(id);
|
}
|
|
/**
|
* 手动推送应收账单到外部系统
|
*
|
* @param id 应收账单ID
|
* @return 结果
|
*/
|
@Override
|
public void manualPushToExternalSystem(Integer id) {
|
TmsArBill tmsArBill = tmsArBillMapper.selectTmsArBillById(id);
|
if (tmsArBill == null) {
|
throw new RuntimeException("应收账单不存在");
|
}
|
|
// 查询关联的应收费用列表
|
List<TmsReceivableFee> tmsReceivableFees = tmsReceivableFeeMapper.selectList(new com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper<TmsReceivableFee>()
|
.eq(TmsReceivableFee::getBillRelationId, id)
|
);
|
|
// 为每个应收费用加载明细
|
for (TmsReceivableFee fee : tmsReceivableFees) {
|
List<TmsReceivableFeeItem> items = tmsReceivableFeeItemMapper.selectTmsReceivableFeeItemList(new TmsReceivableFeeItem() {
|
{
|
setHeadId(fee.getId());
|
}
|
});
|
fee.setItems(items);
|
}
|
|
// 异步推送
|
AsyncTaskExecutor executor = new SimpleAsyncTaskExecutor();
|
executor.execute(() -> pushToExternalSystem(tmsArBill, tmsReceivableFees));
|
}
|
|
/**
|
* 更新推送状态
|
*
|
* @param id 应收账单ID
|
* @param pushStatus 推送状态
|
* @return 结果
|
*/
|
@Override
|
public int updatePushStatus(Integer id, Integer pushStatus) {
|
TmsArBill tmsArBill = new TmsArBill();
|
tmsArBill.setId(id);
|
tmsArBill.setPushStatus(pushStatus);
|
tmsArBill.setPushTime(DateUtils.getNowDate());
|
return tmsArBillMapper.updateTmsArBill(tmsArBill);
|
}
|
|
/**
|
* 向外部系统推送数据
|
* @param tmsArBill 应收账单
|
* @param tmsReceivableFees 应收费用列表
|
*/
|
@Async
|
protected void pushToExternalSystem(TmsArBill tmsArBill, List<TmsReceivableFee> tmsReceivableFees) {
|
java.util.Map<String, Object> requestBody = new java.util.HashMap<>();
|
try {
|
// 更新推送状态为推送中
|
tmsArBill.setPushStatus(1);
|
tmsArBill.setPushTime(DateUtils.getNowDate());
|
tmsArBillMapper.updateTmsArBill(tmsArBill);
|
|
// 构建请求体
|
String apiUrl = url+"/addBill";
|
|
// 构建bill部分
|
java.util.Map<String, Object> billMap = new java.util.HashMap<>();
|
billMap.put("billName", tmsArBill.getBillName());
|
billMap.put("customerName", tmsArBill.getCustomerName());
|
billMap.put("payee", "");
|
billMap.put("responsiblePerson", "");
|
billMap.put("responsibleLeader", "");
|
billMap.put("settlementMethod", "");
|
billMap.put("businessType", "");
|
billMap.put("promotionRequirement", "");
|
billMap.put("isInternalSettlement", "0");
|
billMap.put("internalSettlementUnit", "");
|
billMap.put("documentCount", tmsReceivableFees.size());
|
billMap.put("totalAmount", tmsArBill.getSettleAmount());
|
billMap.put("currency", "RMB");
|
billMap.put("discountAmount", 0.00);
|
billMap.put("receivedAmount", 0.00);
|
billMap.put("pendingAmount", tmsArBill.getSettleAmount());
|
billMap.put("exchangeRate", tmsArBill.getSettleRate());
|
billMap.put("cnyAmount", tmsArBill.getSettleAmount());
|
billMap.put("periodType", "");
|
billMap.put("businessStartDate", "");
|
billMap.put("businessEndDate", "");
|
billMap.put("billingStartDate", "");
|
billMap.put("billingEndDate", "");
|
billMap.put("billGenerateDate", "");
|
billMap.put("billSendDate", "");
|
billMap.put("billDueDate", "");
|
billMap.put("settlementCategory", "");
|
billMap.put("settlementPeriod", "");
|
billMap.put("status", "0");
|
billMap.put("remark", "");
|
billMap.put("sourceSystemId", tmsArBill.getId());
|
|
// 构建fees部分
|
List<java.util.Map<String, Object>> feesList = new java.util.ArrayList<>();
|
for (int i = 0; i < tmsReceivableFees.size(); i++) {
|
TmsReceivableFee fee = tmsReceivableFees.get(i);
|
java.util.Map<String, Object> feeMap = new java.util.HashMap<>();
|
feeMap.put("serialNumber", String.format("%03d", i + 1));
|
feeMap.put("relatedBillNo", "");
|
feeMap.put("sourceSystem", "TMS");
|
feeMap.put("businessSector", "0");
|
feeMap.put("documentType", "0");
|
feeMap.put("documentNo", fee.getDispatchNo() != null ? fee.getDispatchNo() : "");
|
feeMap.put("isInternalSettlement", "0");
|
feeMap.put("internalSettlementUnit", "");
|
feeMap.put("customerName", tmsArBill.getCustomerName());
|
feeMap.put("projectName", fee.getProjectName() != null ? fee.getProjectName() : "");
|
feeMap.put("businessTime", fee.getDispatchConfirmTime());
|
feeMap.put("receivableConfirmTime", fee.getDispatchConfirmTime());
|
feeMap.put("receivableAmount", fee.getReceivableRMBAmount().add(fee.getReceivableHKBAmount()));
|
// 构建receivableAmountStr
|
BigDecimal rmbAmount = fee.getReceivableRMBAmount();
|
BigDecimal hkbAmount = fee.getReceivableHKBAmount();
|
StringBuilder amountStr = new StringBuilder();
|
if (rmbAmount.compareTo(BigDecimal.ZERO) > 0) {
|
amountStr.append(rmbAmount).append("人民币");
|
}
|
if (hkbAmount.compareTo(BigDecimal.ZERO) > 0) {
|
if (amountStr.length() > 0) {
|
amountStr.append(" ");
|
}
|
amountStr.append(hkbAmount).append("港币");
|
}
|
feeMap.put("receivableAmountStr", amountStr.toString());
|
feeMap.put("status", "1");
|
feeMap.put("remark", "");
|
|
// 构建feeDetails部分
|
List<java.util.Map<String, Object>> feeDetailsList = new java.util.ArrayList<>();
|
List<TmsReceivableFeeItem> items = fee.getItems();
|
for (int j = 0; j < items.size(); j++) {
|
TmsReceivableFeeItem item = items.get(j);
|
java.util.Map<String, Object> feeDetailMap = new java.util.HashMap<>();
|
feeDetailMap.put("serialNumber", String.format("%03d", j + 1));
|
feeDetailMap.put("feeType", item.getFeeType());
|
feeDetailMap.put("feeName", item.getFeeName());
|
feeDetailMap.put("billingUnit", "次");
|
feeDetailMap.put("unitPrice", item.getRegisterAmount());
|
feeDetailMap.put("billingQuantity", item.getRegisterAmount());
|
feeDetailMap.put("billingAmount", item.getRegisterAmount());
|
feeDetailMap.put("actualAmount", item.getRegisterAmount());
|
feeDetailMap.put("currency", item.getCurrency());
|
feeDetailMap.put("feeRegTime", item.getRegisterTime());
|
feeDetailMap.put("remark", "");
|
feeDetailsList.add(feeDetailMap);
|
}
|
|
feeMap.put("feeDetails", feeDetailsList);
|
feesList.add(feeMap);
|
}
|
|
// 构建完整请求体
|
requestBody.put("bill", billMap);
|
requestBody.put("fees", feesList);
|
|
// 设置HTTP头
|
HttpHeaders headers = new HttpHeaders();
|
headers.setContentType(MediaType.APPLICATION_JSON);
|
HttpEntity<String> entity = new HttpEntity<>(JSON.toJSONString(requestBody), headers);
|
|
// 发送API请求
|
ResponseEntity<String> response = restTemplate.exchange(apiUrl, HttpMethod.POST, entity, String.class);
|
logger.info("推送数据到外部系统成功，响应: {}", response.getBody());
|
|
// 更新推送状态为成功
|
tmsArBill.setPushStatus(2);
|
tmsArBill.setPushTime(DateUtils.getNowDate());
|
tmsArBillMapper.updateTmsArBill(tmsArBill);
|
} catch (Exception e) {
|
logger.error("推送数据到外部系统失败，账单ID: {}, 客户: {}",
|
tmsArBill.getId(), tmsArBill.getCustomerName(), e);
|
logger.debug("推送失败的请求数据: {}", JSON.toJSONString(requestBody));
|
|
// 更新推送状态为失败
|
tmsArBill.setPushStatus(3);
|
tmsArBill.setPushTime(DateUtils.getNowDate());
|
tmsArBillMapper.updateTmsArBill(tmsArBill);
|
}
|
}
|
|
/**
|
* 导出对账单一式多联格式
|
*
|
* @param tmsArBill 应收账单
|
* @param exportKey 导出功能的唯一标识
|
*/
|
@DataSource(DataSourceType.SLAVE)
|
@Async
|
@Override
|
public void exportArBillFormat(TmsArBill tmsArBill, String exportKey) {
|
// 设置当前任务为“下载中”状态
|
com.ruoyi.common.utils.file.DownloadExportUtil.deleteDownloadFile(redisCache, exportKey, "0");
|
|
try {
|
// 生成文件名
|
String fileName = com.ruoyi.common.utils.poi.ExcelUtil.encodeFileName("arBillFormatData");
|
// 执行导出
|
fileName = exportArBillFormatData(fileName, tmsArBill);
|
// 设置下载完成状态
|
com.ruoyi.common.utils.file.DownloadExportUtil.setDownloadFile(redisCache, exportKey, fileName);
|
logger.info("Export completed for key: {}, file: {}", exportKey, fileName);
|
} catch (Exception e) {
|
logger.error("Export failed for key: {}, error: {}", exportKey, e.getMessage(), e);
|
com.ruoyi.common.utils.file.DownloadExportUtil.deleteDownloadFile(redisCache, exportKey, "1"); // 设置失败状态
|
throw e;
|
}
|
}
|
|
/**
|
* 导出对账单一式多联数据
|
*
|
* @param fileName 文件名
|
* @param tmsArBill 应收账单
|
* @return 导出后的文件名
|
*/
|
protected String exportArBillFormatData(String fileName, TmsArBill tmsArBill) {
|
try (SXSSFWorkbook workbook = new SXSSFWorkbook(1000)) {
|
// 创建工作表
|
SXSSFSheet sheet = workbook.createSheet("对账单");
|
|
// 创建样式
|
Map<String, CellStyle> styles = createArBillStyles(workbook);
|
|
// 查询数据
|
TmsArBill bill = selectTmsArBillById(tmsArBill.getId());
|
|
|
// 获取港币兑人民币汇率
|
String rateStr = sysConfigService.selectConfigByKey("sys.hk.rmb.rate");
|
BigDecimal exchangeRate = new BigDecimal(rateStr);
|
|
// 收集所有费用名称，用于动态生成列
|
Set<String> feeNames = new HashSet<>();
|
|
for (TmsArBillItem item : bill.getItems()) {
|
// 应收费用ID
|
Integer arFeeId = item.getArFeeId();
|
// 应收费用明细
|
List<TmsReceivableFeeItem> tmsReceivableFeeItems = tmsReceivableFeeItemMapper.selectTmsReceivableFeeItemList(new TmsReceivableFeeItem() {{
|
setHeadId(arFeeId);
|
}});
|
// 从应收费用明细中收集费用名称
|
for (TmsReceivableFeeItem feeItem : tmsReceivableFeeItems) {
|
feeNames.add(feeItem.getFeeName());
|
}
|
}
|
|
|
// 将费用名称转换为列表，保持顺序
|
List<String> feeNameList = new ArrayList<>(feeNames);
|
|
// 基础列数（序号、装货日期、装货点、卸货点、车牌、型号）
|
int baseColumns = 6;
|
// 备注列位置
|
int remarkColumn = baseColumns + feeNameList.size();
|
|
// 设置列宽
|
sheet.setColumnWidth(0, 5000); // 序号
|
sheet.setColumnWidth(1, 5000); // 装货日期
|
sheet.setColumnWidth(2, 8000); // 装货点
|
sheet.setColumnWidth(3, 8000); // 卸货点
|
sheet.setColumnWidth(4, 5000); // 车牌
|
sheet.setColumnWidth(5, 5000); // 型号
|
|
// 设置费用列宽
|
for (int i = 0; i < feeNameList.size(); i++) {
|
sheet.setColumnWidth(baseColumns + i, 6000);
|
}
|
|
sheet.setColumnWidth(remarkColumn, 10000); // 备注
|
|
// 标题区域
|
createTitleArea(sheet, styles,bill);
|
|
// 表头
|
createDynamicTableHeader(sheet, styles, feeNameList, baseColumns, remarkColumn);
|
|
// 数据区域
|
int startRow = 5;
|
// 费用合计映射
|
Map<String, BigDecimal> feeTotals = new HashMap<>();
|
for (String feeName : feeNameList) {
|
feeTotals.put(feeName, BigDecimal.ZERO);
|
}
|
|
int rowIndex = 0;
|
|
for (TmsArBillItem item : bill.getItems()) {
|
// 查询对应的应收费用
|
TmsReceivableFee fee = tmsReceivableFeeMapper.selectTmsReceivableFeeById(item.getArFeeId());
|
if (fee != null) {
|
Row row = sheet.createRow(startRow + rowIndex);
|
|
// 序号
|
Cell cell0 = row.createCell(0);
|
cell0.setCellValue(rowIndex + 1);
|
cell0.setCellStyle(styles.get("data"));
|
// 查询调度单信息
|
TmsDispatchOrder dispatchOrder = null;
|
if (fee.getDispatchId() != null) {
|
dispatchOrder = tmsDispatchOrderMapper.selectTmsDispatchOrderById(fee.getDispatchId());
|
}
|
// 装货日期
|
Cell cell1 = row.createCell(1);
|
if (dispatchOrder != null && dispatchOrder.getOrderTime() !=null) {
|
cell1.setCellValue(DateUtils.parseDateToStr("yyyy-MM-dd", dispatchOrder.getOrderTime()));
|
}
|
cell1.setCellStyle(styles.get("data"));
|
|
// 装货点
|
Cell cell2 = row.createCell(2);
|
if (dispatchOrder != null && dispatchOrder.getShipperRegionLabel() != null) {
|
cell2.setCellValue(dispatchOrder.getShipperRegionLabel());
|
}
|
cell2.setCellStyle(styles.get("data"));
|
|
// 卸货点
|
Cell cell3 = row.createCell(3);
|
if (dispatchOrder != null && dispatchOrder.getReceiverRegionLabel() != null) {
|
cell3.setCellValue(dispatchOrder.getReceiverRegionLabel());
|
}
|
cell3.setCellStyle(styles.get("data"));
|
|
// 车牌
|
Cell cell4 = row.createCell(4);
|
if (dispatchOrder != null && dispatchOrder.getLicensePlate() != null) {
|
cell4.setCellValue(dispatchOrder.getLicensePlate());
|
}
|
cell4.setCellStyle(styles.get("data"));
|
|
// 型号
|
Cell cell5 = row.createCell(5);
|
if (dispatchOrder != null && dispatchOrder.getActualVehicleType() != null) {
|
// 使用字典转换车辆型号
|
String vehicleType = dispatchOrder.getActualVehicleType();
|
String vehicleTypeLabel = DictUtils.getDictLabel("vehicle_type", vehicleType);
|
cell5.setCellValue(StringUtils.isNotEmpty(vehicleTypeLabel) ? vehicleTypeLabel : vehicleType);
|
}
|
cell5.setCellStyle(styles.get("data"));
|
|
// 构建费用名称到金额的映射（港币转人民币）
|
Map<String, BigDecimal> feeMap = new HashMap<>();
|
// 查询应收费用明细
|
List<TmsReceivableFeeItem> tmsReceivableFeeItems = tmsReceivableFeeItemMapper.selectTmsReceivableFeeItemList(new TmsReceivableFeeItem() {{
|
setHeadId(fee.getId());
|
}});
|
// 处理应收费用明细
|
for (TmsReceivableFeeItem feeItem : tmsReceivableFeeItems) {
|
BigDecimal amount = feeItem.getRegisterAmount();
|
// 如果是港币，转换为人民币
|
if ("HKD".equals(feeItem.getCurrency()) || "港币".equals(feeItem.getCurrency())) {
|
amount = amount.multiply(exchangeRate).setScale(2, RoundingMode.HALF_UP);
|
}
|
feeMap.put(feeItem.getFeeName(), amount);
|
}
|
|
// 填充费用列
|
for (int j = 0; j < feeNameList.size(); j++) {
|
String feeName = feeNameList.get(j);
|
Cell cell = row.createCell(baseColumns + j);
|
BigDecimal feeAmount = feeMap.getOrDefault(feeName, BigDecimal.ZERO);
|
cell.setCellValue(feeAmount.doubleValue());
|
cell.setCellStyle(styles.get("data"));
|
|
// 累计合计
|
feeTotals.put(feeName, feeTotals.get(feeName).add(feeAmount));
|
}
|
|
// 备注
|
Cell remarkCell = row.createCell(remarkColumn);
|
remarkCell.setCellValue("" + (fee.getDispatchNo() != null ? fee.getDispatchNo() : ""));
|
remarkCell.setCellStyle(styles.get("data"));
|
|
rowIndex++;
|
}
|
}
|
|
|
// 小计行
|
int subTotalRow = startRow + rowIndex;
|
Row subTotal = sheet.createRow(subTotalRow);
|
// 为所有列创建单元格
|
for (int i = 0; i <= remarkColumn; i++) {
|
Cell cell = subTotal.createCell(i);
|
if (i == 0) {
|
cell.setCellValue("小计");
|
}
|
cell.setCellStyle(styles.get("total"));
|
}
|
sheet.addMergedRegion(new CellRangeAddress(subTotalRow, subTotalRow, 0, 5));
|
|
// 填充费用小计
|
for (int j = 0; j < feeNameList.size(); j++) {
|
String feeName = feeNameList.get(j);
|
Cell cell = subTotal.getCell(baseColumns + j);
|
cell.setCellValue(feeTotals.get(feeName).doubleValue());
|
}
|
|
// 合计行
|
int totalRow = subTotalRow + 1;
|
Row total = sheet.createRow(totalRow);
|
// 为所有列创建单元格
|
for (int i = 0; i <= remarkColumn; i++) {
|
Cell cell = total.createCell(i);
|
if (i == 0) {
|
cell.setCellValue("合计(RMB)");
|
}
|
cell.setCellStyle(styles.get("total"));
|
}
|
sheet.addMergedRegion(new CellRangeAddress(totalRow, totalRow, 0, 5));
|
|
// 计算所有费用的合计
|
BigDecimal grandTotal = BigDecimal.ZERO;
|
for (BigDecimal amount : feeTotals.values()) {
|
grandTotal = grandTotal.add(amount);
|
}
|
|
// 填充费用合计（所有费用的总和）
|
for (int j = 0; j < feeNameList.size(); j++) {
|
Cell cell = total.getCell(baseColumns + j);
|
if (j == 0) {
|
cell.setCellValue(grandTotal.doubleValue());
|
}
|
}
|
// 合并费用列单元格
|
if (feeNameList.size() > 1) {
|
sheet.addMergedRegion(new CellRangeAddress(totalRow, totalRow, baseColumns, baseColumns + feeNameList.size() - 1));
|
}
|
|
// 备注说明区域
|
createNotesArea(sheet, styles, totalRow + 1, grandTotal);
|
|
// 账户信息和签字盖章区域（合并成一个大格）
|
createAccountAndSignatureArea(sheet, styles, totalRow + 4, bill);
|
|
// 保存文件
|
String path = RuoYiConfig.getDownloadPath() + fileName;
|
File file = new File(path);
|
File parentFile = file.getParentFile();
|
if (!parentFile.exists()) {
|
parentFile.mkdirs();
|
}
|
|
try (FileOutputStream fos = new FileOutputStream(file)) {
|
workbook.write(fos);
|
}
|
|
return fileName;
|
} catch (Exception e) {
|
logger.error("导出对账单一式多联格式失败", e);
|
throw new RuntimeException("导出失败，请检查数据", e);
|
}
|
|
}
|
|
/**
|
* 创建对账单一式多联样式
|
*
|
* @param workbook 工作簿
|
* @return 样式映射
|
*/
|
private Map<String, CellStyle> createArBillStyles(SXSSFWorkbook workbook) {
|
Map<String, CellStyle> styles = new HashMap<>();
|
|
// 标题样式
|
CellStyle titleStyle = workbook.createCellStyle();
|
titleStyle.setAlignment(HorizontalAlignment.CENTER);
|
titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
|
titleStyle.setBorderTop(BorderStyle.THIN);
|
titleStyle.setBorderBottom(BorderStyle.THIN);
|
titleStyle.setBorderLeft(BorderStyle.THIN);
|
titleStyle.setBorderRight(BorderStyle.THIN);
|
Font titleFont = workbook.createFont();
|
titleFont.setFontName("微软雅黑");
|
titleFont.setFontHeightInPoints((short) 16);
|
titleFont.setBold(true);
|
titleStyle.setFont(titleFont);
|
styles.put("title", titleStyle);
|
|
// 表头样式
|
CellStyle headerStyle = workbook.createCellStyle();
|
headerStyle.setAlignment(HorizontalAlignment.CENTER);
|
headerStyle.setVerticalAlignment(VerticalAlignment.CENTER);
|
headerStyle.setBorderTop(BorderStyle.THIN);
|
headerStyle.setBorderBottom(BorderStyle.THIN);
|
headerStyle.setBorderLeft(BorderStyle.THIN);
|
headerStyle.setBorderRight(BorderStyle.THIN);
|
Font headerFont = workbook.createFont();
|
headerFont.setFontName("微软雅黑");
|
headerFont.setFontHeightInPoints((short) 11);
|
headerFont.setBold(true);
|
headerStyle.setFont(headerFont);
|
styles.put("header", headerStyle);
|
|
// 数据样式
|
CellStyle dataStyle = workbook.createCellStyle();
|
dataStyle.setAlignment(HorizontalAlignment.CENTER);
|
dataStyle.setVerticalAlignment(VerticalAlignment.CENTER);
|
dataStyle.setBorderTop(BorderStyle.THIN);
|
dataStyle.setBorderBottom(BorderStyle.THIN);
|
dataStyle.setBorderLeft(BorderStyle.THIN);
|
dataStyle.setBorderRight(BorderStyle.THIN);
|
Font dataFont = workbook.createFont();
|
dataFont.setFontName("微软雅黑");
|
dataFont.setFontHeightInPoints((short) 11);
|
dataStyle.setFont(dataFont);
|
styles.put("data", dataStyle);
|
|
// 合计样式
|
CellStyle totalStyle = workbook.createCellStyle();
|
totalStyle.setAlignment(HorizontalAlignment.CENTER);
|
totalStyle.setVerticalAlignment(VerticalAlignment.CENTER);
|
totalStyle.setBorderTop(BorderStyle.THIN);
|
totalStyle.setBorderBottom(BorderStyle.THIN);
|
totalStyle.setBorderLeft(BorderStyle.THIN);
|
totalStyle.setBorderRight(BorderStyle.THIN);
|
Font totalFont = workbook.createFont();
|
totalFont.setFontName("微软雅黑");
|
totalFont.setFontHeightInPoints((short) 11);
|
totalFont.setBold(true);
|
totalStyle.setFont(totalFont);
|
styles.put("total", totalStyle);
|
|
// 文本样式
|
CellStyle textStyle = workbook.createCellStyle();
|
textStyle.setAlignment(HorizontalAlignment.LEFT);
|
textStyle.setVerticalAlignment(VerticalAlignment.TOP);
|
textStyle.setBorderTop(BorderStyle.THIN);
|
textStyle.setBorderBottom(BorderStyle.THIN);
|
textStyle.setBorderLeft(BorderStyle.THIN);
|
textStyle.setBorderRight(BorderStyle.THIN);
|
Font textFont = workbook.createFont();
|
textFont.setFontName("微软雅黑");
|
textFont.setFontHeightInPoints((short) 11);
|
textStyle.setFont(textFont);
|
styles.put("text", textStyle);
|
|
// 文本样式
|
CellStyle textStyle2 = workbook.createCellStyle();
|
textStyle2.setAlignment(HorizontalAlignment.LEFT);
|
textStyle2.setVerticalAlignment(VerticalAlignment.TOP);
|
textStyle2.setBorderTop(BorderStyle.THIN);
|
textStyle2.setBorderBottom(BorderStyle.THIN);
|
textStyle2.setBorderLeft(BorderStyle.THIN);
|
|
Font textFont2 = workbook.createFont();
|
textFont2.setFontName("微软雅黑");
|
textFont2.setFontHeightInPoints((short) 11);
|
textStyle2.setFont(textFont2);
|
styles.put("text2", textStyle2);
|
|
return styles;
|
}
|
|
/**
|
* 创建标题区域
|
*
|
* @param sheet 工作表
|
* @param styles 样式映射
|
* @param tmsReceivableFee 应收费用查询条件
|
*/
|
private void createTitleArea(SXSSFSheet sheet, Map<String, CellStyle> styles, TmsArBill tmsArBill) {
|
// 标题行（无边框，居中）
|
Row titleRow = sheet.createRow(0);
|
titleRow.setHeightInPoints(30);
|
for (int i = 0; i <= 9; i++) {
|
Cell cell = titleRow.createCell(i);
|
if (i == 0) {
|
cell.setCellValue(tmsArBill.getCustomerName());
|
// 居中显示
|
CellStyle centerStyle = sheet.getWorkbook().createCellStyle();
|
centerStyle.setAlignment(HorizontalAlignment.CENTER);
|
centerStyle.setVerticalAlignment(VerticalAlignment.CENTER);
|
cell.setCellStyle(centerStyle);
|
}
|
// 不设置边框样式
|
}
|
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 9));
|
|
// 对账单行 - 居中显示（无边框）
|
Row billRow = sheet.createRow(1);
|
billRow.setHeightInPoints(20);
|
for (int i = 0; i <= 9; i++) {
|
Cell cell = billRow.createCell(i);
|
if (i == 0) {
|
cell.setCellValue(tmsArBill.getBillName());
|
// 居中显示
|
CellStyle centerStyle = sheet.getWorkbook().createCellStyle();
|
centerStyle.setAlignment(HorizontalAlignment.CENTER);
|
centerStyle.setVerticalAlignment(VerticalAlignment.CENTER);
|
cell.setCellStyle(centerStyle);
|
}
|
// 不设置边框样式
|
}
|
sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 9));
|
|
// FROM行（在TO上面，无边框）
|
Row fromRow = sheet.createRow(2);
|
fromRow.setHeightInPoints(20);
|
for (int i = 0; i <= 9; i++) {
|
Cell cell = fromRow.createCell(i);
|
if (i == 0) {
|
cell.setCellValue("FROM:珠海市汇畅交通投资有限公司");
|
}
|
// 不设置边框样式
|
}
|
|
// TO行（无边框）
|
Row toRow = sheet.createRow(3);
|
toRow.setHeightInPoints(20);
|
for (int i = 0; i <= 9; i++) {
|
Cell cell = toRow.createCell(i);
|
if (i == 0) {
|
cell.setCellValue("TO: "+tmsArBill.getCustomerName());
|
}
|
// 不设置边框样式
|
}
|
}
|
|
/**
|
* 创建动态表格表头
|
*
|
* @param sheet 工作表
|
* @param styles 样式映射
|
* @param feeNameList 费用名称列表
|
* @param baseColumns 基础列数
|
* @param remarkColumn 备注列位置
|
*/
|
private void createDynamicTableHeader(SXSSFSheet sheet, Map<String, CellStyle> styles, List<String> feeNameList, int baseColumns, int remarkColumn) {
|
Row headerRow = sheet.createRow(4);
|
headerRow.setHeightInPoints(25);
|
|
// 基础列
|
String[] baseHeaders = {"序号", "装货日期", "装货点", "卸货点", "车牌", "型号"};
|
for (int i = 0; i < baseHeaders.length; i++) {
|
Cell cell = headerRow.createCell(i);
|
cell.setCellValue(baseHeaders[i]);
|
cell.setCellStyle(styles.get("header"));
|
}
|
|
// 费用列
|
for (int i = 0; i < feeNameList.size(); i++) {
|
Cell cell = headerRow.createCell(baseColumns + i);
|
cell.setCellValue(feeNameList.get(i) + "(人民币)");
|
cell.setCellStyle(styles.get("header"));
|
}
|
|
// 备注列
|
Cell remarkCell = headerRow.createCell(remarkColumn);
|
remarkCell.setCellValue("备注");
|
remarkCell.setCellStyle(styles.get("header"));
|
}
|
|
/**
|
* 将金额转换为中文大写
|
*
|
* @param amount 金额
|
* @return 中文大写金额
|
*/
|
private String convertToChineseUppercase(BigDecimal amount) {
|
if (amount == null) {
|
return "零元整";
|
}
|
|
String[] digits = {"零", "壹", "贰", "叁", "肆", "伍", "陆", "柒", "捌", "玖"};
|
String[] units = {"", "拾", "佰", "仟"};
|
String[] bigUnits = {"", "万", "亿"};
|
|
String amountStr = amount.setScale(2, RoundingMode.HALF_UP).toString();
|
String integerPart = amountStr.split("\\.")[0];
|
String decimalPart = amountStr.split("\\.")[1];
|
|
StringBuilder result = new StringBuilder();
|
|
// 处理整数部分
|
int length = integerPart.length();
|
for (int i = 0; i < length; i++) {
|
int digit = Integer.parseInt(String.valueOf(integerPart.charAt(i)));
|
int unitIndex = (length - i - 1) % 4;
|
int bigUnitIndex = (length - i - 1) / 4;
|
|
if (digit != 0) {
|
result.append(digits[digit]).append(units[unitIndex]).append(bigUnits[bigUnitIndex]);
|
} else {
|
// 避免连续的零
|
if (i > 0 && Integer.parseInt(String.valueOf(integerPart.charAt(i - 1))) != 0) {
|
result.append(digits[digit]);
|
}
|
// 只在非末尾位置添加大单位
|
if (unitIndex == 0 && bigUnitIndex > 0 && i < length - 1) {
|
result.append(bigUnits[bigUnitIndex]);
|
}
|
}
|
}
|
|
result.append("元");
|
|
// 处理小数部分
|
int jiao = Integer.parseInt(String.valueOf(decimalPart.charAt(0)));
|
int fen = Integer.parseInt(String.valueOf(decimalPart.charAt(1)));
|
|
if (jiao == 0 && fen == 0) {
|
result.append("整");
|
} else if (jiao == 0) {
|
result.append("零").append(digits[fen]).append("分");
|
} else if (fen == 0) {
|
result.append(digits[jiao]).append("角整");
|
} else {
|
result.append(digits[jiao]).append("角").append(digits[fen]).append("分");
|
}
|
|
return result.toString();
|
}
|
|
/**
|
* 创建备注说明区域
|
*
|
* @param sheet 工作表
|
* @param styles 样式映射
|
* @param startRow 起始行
|
* @param grandTotal 总金额
|
*/
|
private void createNotesArea(SXSSFSheet sheet, Map<String, CellStyle> styles, int startRow, BigDecimal grandTotal) {
|
// 创建无边界样式
|
CellStyle noBorderStyle = sheet.getWorkbook().createCellStyle();
|
|
// 创建两行
|
for (int rowIdx = 0; rowIdx < 2; rowIdx++) {
|
Row row = sheet.createRow(startRow + rowIdx);
|
for (int i = 0; i <= 9; i++) {
|
Cell cell = row.createCell(i);
|
if (i == 0) {
|
// 第一列设置带边框的样式
|
cell.setCellStyle(styles.get("text2"));
|
} else {
|
// 其他列设置无边界样式
|
cell.setCellStyle(noBorderStyle);
|
}
|
}
|
}
|
|
// 第一行备注
|
Row firstRow = sheet.getRow(startRow);
|
Cell cell1 = firstRow.getCell(0);
|
String chineseAmount = convertToChineseUppercase(grandTotal);
|
cell1.setCellValue("1.依据合同相关规定，贵司需向我司支付￥" + grandTotal.setScale(2, RoundingMode.HALF_UP) + "（大写金额：" + chineseAmount + "）的费用，请贵司及时安排支付。");
|
|
// 第二行备注
|
Row secondRow = sheet.getRow(startRow + 1);
|
Cell cell2 = secondRow.getCell(0);
|
cell2.setCellValue("2.传真件与原件起同等法律效力。");
|
|
|
}
|
|
|
|
/**
|
* 创建账户信息和签字盖章区域
|
*
|
* @param sheet 工作表
|
* @param styles 样式映射
|
* @param startRow 起始行
|
* @param bill
|
*/
|
private void createAccountAndSignatureArea(SXSSFSheet sheet, Map<String, CellStyle> styles, int startRow, TmsArBill bill) {
|
// 计算结束行（账户信息4行 + 签字盖章6行）
|
int endRow = startRow + 9;
|
|
// 创建所有行，设置行高
|
for (int rowIdx = 0; rowIdx <= 9; rowIdx++) {
|
Row row = sheet.createRow(startRow + rowIdx);
|
row.setHeightInPoints(20);
|
for (int i = 0; i <= 9; i++) {
|
Cell cell = row.createCell(i);
|
// 不设置边框样式，只显示数据
|
}
|
}
|
|
// 账户信息区域（左上角）
|
Row infoRow1 = sheet.getRow(startRow);
|
Cell infoCell1 = infoRow1.getCell(0);
|
infoCell1.setCellValue("烦请核对确认并转至我司如下帐号：");
|
|
Row infoRow2 = sheet.getRow(startRow + 1);
|
Cell infoCell2 = infoRow2.getCell(0);
|
infoCell2.setCellValue("开户银行:中国农业发展银行珠海市分行");
|
|
Row infoRow3 = sheet.getRow(startRow + 2);
|
Cell infoCell3 = infoRow3.getCell(0);
|
infoCell3.setCellValue("账号:20344990100000422001");
|
|
Row infoRow4 = sheet.getRow(startRow + 3);
|
Cell infoCell4 = infoRow4.getCell(0);
|
infoCell4.setCellValue("户名:珠海市汇畅交通投资有限公司");
|
|
// 付款单位（左下角）
|
Row payerRow = sheet.getRow(startRow + 4);
|
Cell payerCell = payerRow.getCell(0);
|
payerCell.setCellValue("付款单位（甲方）：" + bill.getCustomerName());
|
|
// 收款单位（右下角）
|
Row payeeRow = sheet.getRow(startRow + 4);
|
Cell payeeCell = payeeRow.getCell(6);
|
payeeCell.setCellValue("收款单位（乙方）：珠海市汇畅交通投资有限公司");
|
|
// 制表人员
|
Row creatorRow = sheet.getRow(startRow + 5);
|
Cell creatorCell1 = creatorRow.getCell(0);
|
creatorCell1.setCellValue("制表人员：");
|
Cell creatorCell2 = creatorRow.getCell(6);
|
creatorCell2.setCellValue("制表人员：");
|
|
// 审核人员
|
Row checkerRow = sheet.getRow(startRow + 6);
|
Cell checkerCell1 = checkerRow.getCell(0);
|
checkerCell1.setCellValue("审核人员：");
|
Cell checkerCell2 = checkerRow.getCell(6);
|
checkerCell2.setCellValue("审核人员：");
|
|
// 复核人员
|
Row reviewerRow = sheet.getRow(startRow + 7);
|
Cell reviewerCell1 = reviewerRow.getCell(0);
|
reviewerCell1.setCellValue("复核人员：");
|
Cell reviewerCell2 = reviewerRow.getCell(6);
|
reviewerCell2.setCellValue("复核人员：");
|
|
// 盖章
|
Row stampRow = sheet.getRow(startRow + 8);
|
Cell stampCell1 = stampRow.getCell(0);
|
stampCell1.setCellValue("盖章：");
|
Cell stampCell2 = stampRow.getCell(6);
|
stampCell2.setCellValue("盖章：");
|
|
// 日期
|
Row dateRow = sheet.getRow(startRow + 9);
|
Cell dateCell1 = dateRow.getCell(0);
|
dateCell1.setCellValue("日期：2026年 月 日");
|
Cell dateCell2 = dateRow.getCell(6);
|
dateCell2.setCellValue("日期：2026年 月 日");
|
|
|
|
}
|
|
/**
|
* 创建签字盖章区域
|
*
|
* @param sheet 工作表
|
* @param styles 样式映射
|
* @param startRow 起始行
|
* @param bill
|
*/
|
private void createSignatureArea(SXSSFSheet sheet, Map<String, CellStyle> styles, int startRow, TmsArBill bill) {
|
// 付款单位（甲方）和收款单位（乙方）行
|
Row payerRow = sheet.createRow(startRow);
|
for (int i = 0; i <= 9; i++) {
|
Cell cell = payerRow.createCell(i);
|
if (i == 0) {
|
cell.setCellValue("付款单位（甲方）："+bill.getCustomerName());
|
} else if (i == 6) {
|
cell.setCellValue("收款单位（乙方）：珠海市汇畅交通投资有限公司");
|
}
|
cell.setCellStyle(styles.get("text"));
|
}
|
|
// 制表人员行
|
Row creatorRow = sheet.createRow(startRow + 1);
|
for (int i = 0; i <= 9; i++) {
|
Cell cell = creatorRow.createCell(i);
|
if (i == 0) {
|
cell.setCellValue("制表人员：");
|
} else if (i == 6) {
|
cell.setCellValue("制表人员：");
|
}
|
cell.setCellStyle(styles.get("text"));
|
}
|
|
// 审核人员行
|
Row checkerRow = sheet.createRow(startRow + 2);
|
for (int i = 0; i <= 9; i++) {
|
Cell cell = checkerRow.createCell(i);
|
if (i == 0) {
|
cell.setCellValue("审核人员：");
|
} else if (i == 6) {
|
cell.setCellValue("审核人员：");
|
}
|
cell.setCellStyle(styles.get("text"));
|
}
|
|
// 复核人员行
|
Row reviewerRow = sheet.createRow(startRow + 3);
|
for (int i = 0; i <= 9; i++) {
|
Cell cell = reviewerRow.createCell(i);
|
if (i == 0) {
|
cell.setCellValue("复核人员：");
|
} else if (i == 6) {
|
cell.setCellValue("复核人员：");
|
}
|
cell.setCellStyle(styles.get("text"));
|
}
|
|
// 盖章行
|
Row stampRow = sheet.createRow(startRow + 4);
|
for (int i = 0; i <= 9; i++) {
|
Cell cell = stampRow.createCell(i);
|
if (i == 0) {
|
cell.setCellValue("盖章：");
|
} else if (i == 6) {
|
cell.setCellValue("盖章：");
|
}
|
cell.setCellStyle(styles.get("text"));
|
}
|
|
// 日期行
|
Row dateRow = sheet.createRow(startRow + 5);
|
for (int i = 0; i <= 9; i++) {
|
Cell cell = dateRow.createCell(i);
|
if (i == 0) {
|
cell.setCellValue("日期：2026年 月 日");
|
} else if (i == 6) {
|
cell.setCellValue("日期：2026年 月 日");
|
}
|
cell.setCellStyle(styles.get("text"));
|
}
|
}
|
}
|
