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.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;

/**
 * 应收账单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;

    /**
     * 查询应收账单
     *
     * @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 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 = 4;
            // 费用合计映射
            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) {
                          TmsTrip tmsTrip = tmsTripMapper.selectTmsTripByTripType(3,dispatchOrder.getId() );
                          if (tmsTrip!=null) {
                              cell1.setCellValue(DateUtils.parseDateToStr("yyyy-MM-dd", tmsTrip.getTripTime()));
                              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) {
                            cell5.setCellValue(dispatchOrder.getActualVehicleType());
                            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);
            Cell subTotalCell = subTotal.createCell(2);
            subTotalCell.setCellValue("小计");
            subTotalCell.setCellStyle(styles.get("total"));
            sheet.addMergedRegion(new CellRangeAddress(subTotalRow, subTotalRow, 2, 5));
            
            // 填充费用小计
            for (int j = 0; j < feeNameList.size(); j++) {
                String feeName = feeNameList.get(j);
                Cell cell = subTotal.createCell(baseColumns + j);
                cell.setCellValue(feeTotals.get(feeName).doubleValue());
                cell.setCellStyle(styles.get("total"));
            }

            // 合计行
            int totalRow = subTotalRow + 1;
            Row total = sheet.createRow(totalRow);
            Cell totalCell = total.createCell(2);
            totalCell.setCellValue("合计(CNB)");
            totalCell.setCellStyle(styles.get("total"));
            sheet.addMergedRegion(new CellRangeAddress(totalRow, totalRow, 2, 5));

            // 填充费用合计
            for (int j = 0; j < feeNameList.size(); j++) {
                String feeName = feeNameList.get(j);
                Cell cell = total.createCell(baseColumns + j);
                cell.setCellValue(feeTotals.get(feeName).doubleValue());
                cell.setCellStyle(styles.get("total"));
            }
            
            // 备注说明区域
            createNotesArea(sheet, styles, totalRow + 1);
            
            // 账户信息区域
            createAccountInfo(sheet, styles, totalRow + 4);
            
            // 签字盖章区域
            createSignatureArea(sheet, styles, totalRow + 8,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);
        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);
        headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        headerStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        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);
        totalStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        totalStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        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);
        Font textFont = workbook.createFont();
        textFont.setFontName("微软雅黑");
        textFont.setFontHeightInPoints((short) 11);
        textStyle.setFont(textFont);
        styles.put("text", textStyle);
        
        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);
        Cell titleCell = titleRow.createCell(0);
        titleCell.setCellValue(tmsArBill.getCustomerName());
        titleCell.setCellStyle(styles.get("title"));
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 9));

        // 对账单行
        Row billRow = sheet.createRow(1);
        billRow.setHeightInPoints(20);
        Cell toCell = billRow.createCell(0);
        toCell.setCellValue("TO: "+tmsArBill.getCustomerName());
        toCell.setCellStyle(styles.get("text"));

        Cell billTitleCell = billRow.createCell(3);
        billTitleCell.setCellValue(tmsArBill.getBillName());
        billTitleCell.setCellStyle(styles.get("text"));
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 3, 6));

        // 公司信息行
        Row companyRow = sheet.createRow(2);
        companyRow.setHeightInPoints(20);
        Cell fromCell = companyRow.createCell(0);
        fromCell.setCellValue("FROM:珠海市汇畅交通投资有限公司");
        fromCell.setCellStyle(styles.get("text"));
    }

    /**
     * 创建动态表格表头
     *
     * @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(3);
        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 sheet 工作表
     * @param styles 样式映射
     * @param startRow 起始行
     */
    private void createNotesArea(SXSSFSheet sheet, Map<String, CellStyle> styles, int startRow) {
        Row note1Row = sheet.createRow(startRow);
        Cell note1Cell = note1Row.createCell(0);
        note1Cell.setCellValue("1.依据合同相关规定，贵司需向我司支付￥（大写金额：）的费用，请贵司及时安排支付。");
        note1Cell.setCellStyle(styles.get("text"));
        sheet.addMergedRegion(new CellRangeAddress(startRow, startRow, 0, 9));

        Row note2Row = sheet.createRow(startRow + 1);
        Cell note2Cell = note2Row.createCell(0);
        note2Cell.setCellValue("2.传真件与原件起同等法律效力。");
        note2Cell.setCellStyle(styles.get("text"));
        sheet.addMergedRegion(new CellRangeAddress(startRow + 1, startRow + 1, 0, 9));
    }

    /**
     * 创建账户信息区域
     *
     * @param sheet 工作表
     * @param styles 样式映射
     * @param startRow 起始行
     */
    private void createAccountInfo(SXSSFSheet sheet, Map<String, CellStyle> styles, int startRow) {
        Row accountTitleRow = sheet.createRow(startRow);
        Cell accountTitleCell = accountTitleRow.createCell(0);
        accountTitleCell.setCellValue("烦请核对确认并转至我司如下帐号：");
        accountTitleCell.setCellStyle(styles.get("text"));
        sheet.addMergedRegion(new CellRangeAddress(startRow, startRow, 0, 9));

        Row bankRow = sheet.createRow(startRow + 1);
        Cell bankCell = bankRow.createCell(0);
        bankCell.setCellValue("开户银行:中国农业发展银行珠海市分行");
        bankCell.setCellStyle(styles.get("text"));
        sheet.addMergedRegion(new CellRangeAddress(startRow + 1, startRow + 1, 0, 9));

        Row accountRow = sheet.createRow(startRow + 2);
        Cell accountCell = accountRow.createCell(0);
        accountCell.setCellValue("账号:20344990100000422001");
        accountCell.setCellStyle(styles.get("text"));
        sheet.addMergedRegion(new CellRangeAddress(startRow + 2, startRow + 2, 0, 9));

        Row nameRow = sheet.createRow(startRow + 3);
        Cell nameCell = nameRow.createCell(0);
        nameCell.setCellValue("户名:珠海市汇畅交通投资有限公司");
        nameCell.setCellStyle(styles.get("text"));
        sheet.addMergedRegion(new CellRangeAddress(startRow + 3, startRow + 3, 0, 9));
    }

    /**
     * 创建签字盖章区域
     *
     * @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);
        Cell payerCell = payerRow.createCell(0);
        payerCell.setCellValue("付款单位（甲方）："+bill.getCustomerName());
        payerCell.setCellStyle(styles.get("text"));

        Cell payeeCell = payerRow.createCell(6);
        payeeCell.setCellValue("收款单位（乙方）：珠海市汇畅交通投资有限公司");
        payeeCell.setCellStyle(styles.get("text"));

        Row creatorRow = sheet.createRow(startRow + 1);
        Cell creatorCell = creatorRow.createCell(0);
        creatorCell.setCellValue("制表人员：");
        creatorCell.setCellStyle(styles.get("text"));

        Cell creatorCell2 = creatorRow.createCell(6);
        creatorCell2.setCellValue("制表人员：");
        creatorCell2.setCellStyle(styles.get("text"));

        Row checkerRow = sheet.createRow(startRow + 2);
        Cell checkerCell = checkerRow.createCell(0);
        checkerCell.setCellValue("审核人员：");
        checkerCell.setCellStyle(styles.get("text"));

        Cell checkerCell2 = checkerRow.createCell(6);
        checkerCell2.setCellValue("审核人员：");
        checkerCell2.setCellStyle(styles.get("text"));

        Row reviewerRow = sheet.createRow(startRow + 3);
        Cell reviewerCell = reviewerRow.createCell(0);
        reviewerCell.setCellValue("复核人员：");
        reviewerCell.setCellStyle(styles.get("text"));

        Cell reviewerCell2 = reviewerRow.createCell(6);
        reviewerCell2.setCellValue("复核人员：");
        reviewerCell2.setCellStyle(styles.get("text"));

        Row stampRow = sheet.createRow(startRow + 4);
        Cell stampCell = stampRow.createCell(0);
        stampCell.setCellValue("盖章：");
        stampCell.setCellStyle(styles.get("text"));

        Cell stampCell2 = stampRow.createCell(6);
        stampCell2.setCellValue("盖章：");
        stampCell2.setCellStyle(styles.get("text"));

        Row dateRow = sheet.createRow(startRow + 5);
        Cell dateCell = dateRow.createCell(0);
        dateCell.setCellValue("日期：2026年  月  日");
        dateCell.setCellStyle(styles.get("text"));

        Cell dateCell2 = dateRow.createCell(6);
        dateCell2.setCellValue("日期：2026年  月  日");
        dateCell2.setCellStyle(styles.get("text"));
    }
}