WarehouseManagementLc/ruoyi-warehouse/src/main/java/com/ruoyi/warehouseBusiness/service/impl/TWarehousebillsfeesServiceImpl.java

package com.ruoyi.warehouseBusiness.service.impl;


import com.ruoyi.basicData.mapper.TFeesMapper;
import com.ruoyi.common.config.RuoYiConfig;
import com.ruoyi.common.core.domain.AjaxResult;
import com.ruoyi.common.utils.DateUtils;
import com.ruoyi.common.utils.StringUtils;
import com.ruoyi.finance.excel.FleetExcel;
import com.ruoyi.warehouseBusiness.domain.TWarehousebillsfees;
import com.ruoyi.warehouseBusiness.excel.BillDetailsSubItem;
import com.ruoyi.warehouseBusiness.excel.InStock;
import com.ruoyi.warehouseBusiness.excel.ListMapToBeanUtils;
import com.ruoyi.warehouseBusiness.excel.QueryMenu;
import com.ruoyi.warehouseBusiness.mapper.TWarehousebillsfeesMapper;
import com.ruoyi.warehouseBusiness.service.ITWarehousebillsfeesService;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.io.*;
import java.math.BigDecimal;
import java.util.*;

/**
 * 仓库费用明细Service业务层处理
 *
 * @author ruoyi
 * @date 2020-12-11
 */
@Service
public class TWarehousebillsfeesServiceImpl implements ITWarehousebillsfeesService {
    @Autowired
    private TWarehousebillsfeesMapper tWarehousebillsfeesMapper;

    @Autowired
    private TFeesMapper tFeesMapper;

    /**
     * 查询仓库费用明细
     *
     * @param fId 仓库费用明细ID
     * @return 仓库费用明细
     */
    @Override
    public TWarehousebillsfees selectTWarehousebillsfeesById(Long fId) {
        return tWarehousebillsfeesMapper.selectTWarehousebillsfeesById(fId);
    }

    /**
     * 查询仓库费用明细列表
     *
     * @param tWarehousebillsfees 仓库费用明细
     * @return 仓库费用明细
     */
    @Override
    public List<TWarehousebillsfees> selectTWarehousebillsfeesList(TWarehousebillsfees tWarehousebillsfees) {
        return tWarehousebillsfeesMapper.selectTWarehousebillsfeesList(tWarehousebillsfees);
    }

    /**
     * 新增仓库费用明细
     *
     * @param tWarehousebillsfees 仓库费用明细
     * @return 结果
     */
    @Override
    public int insertTWarehousebillsfees(TWarehousebillsfees tWarehousebillsfees) {
        tWarehousebillsfees.setCreateTime(DateUtils.getNowDate());
        return tWarehousebillsfeesMapper.insertTWarehousebillsfees(tWarehousebillsfees);
    }

    /**
     * 修改仓库费用明细
     *
     * @param tWarehousebillsfees 仓库费用明细
     * @return 结果
     */
    @Override
    public int updateTWarehousebillsfees(TWarehousebillsfees tWarehousebillsfees) {
        tWarehousebillsfees.setUpdateTime(DateUtils.getNowDate());
        return tWarehousebillsfeesMapper.updateTWarehousebillsfees(tWarehousebillsfees);
    }

    /**
     * 批量删除仓库费用明细
     *
     * @param fIds 需要删除的仓库费用明细ID
     * @return 结果
     */
    @Override
    public int deleteTWarehousebillsfeesByIds(Long[] fIds) {
        return tWarehousebillsfeesMapper.deleteTWarehousebillsfeesByIds(fIds);
    }

    /**
     * 删除仓库费用明细信息
     *
     * @param fId 仓库费用明细ID
     * @return 结果
     */
    @Override
    public int deleteTWarehousebillsfeesById(Long fId) {
        return tWarehousebillsfeesMapper.deleteTWarehousebillsfeesById(fId);
    }

    /**
     * 查询报表
     *
     * @param tWarehousebillsfees
     * @return
     */
    @Override
    public List<FleetExcel> selectFleetExcelList(TWarehousebillsfees tWarehousebillsfees) {
        List<FleetExcel> fleetExcelList = new ArrayList<>();
        // 开始月份
        int startMonth = 1;
        // 结束月份
        int endMonth = 1;
        if (StringUtils.isNotEmpty(tWarehousebillsfees.getfYears())) {
            // 年份
            List<String> month = tWarehousebillsfees.getfMonth();
            if (StringUtils.isNotEmpty(month.get(0))) {
                startMonth = Integer.parseInt(month.get(0));
            }
            if (StringUtils.isNotEmpty(month.get(1))) {
                endMonth = Integer.parseInt(month.get(1));
            }
            if (endMonth < startMonth) {
                return null;
            }
            for (int i = startMonth; i <= endMonth; i++) {
                String firstDay = DateUtils.getFirstDay(Integer.parseInt(tWarehousebillsfees.getfYears()), i);
                String lastDay = DateUtils.getLastDay(Integer.parseInt(tWarehousebillsfees.getfYears()), i);
                month.set(0, firstDay);
                month.set(1, lastDay);
                tWarehousebillsfees.setfMonth(month);
                List<FleetExcel> fleetExcels = tWarehousebillsfeesMapper.selectFleetExcelList(tWarehousebillsfees);
                if (StringUtils.isNotEmpty(fleetExcels)) {
                    for (FleetExcel e : fleetExcels) {
                        fleetExcelList.add(e);
                    }
                    fleetExcelList.add(tWarehousebillsfeesMapper.selectFleetExcelMonthList(tWarehousebillsfees));
                }
            }
        }
        return fleetExcelList;
    }

    /**
     * 查询报表合计
     *
     * @param fleetExcelsList
     * @return
     */
    @Override
    public List<String> selectFleetExcel(List<FleetExcel> fleetExcelsList) {
        List<String> stringList = new ArrayList<>();
        if (StringUtils.isEmpty(fleetExcelsList)) {
            stringList.add("0");
            stringList.add("0");
            stringList.add("0");
            return stringList;
        }
        BigDecimal ccf = new BigDecimal(0);
        BigDecimal fAmount = new BigDecimal(0);
        BigDecimal totalAmount = new BigDecimal(0);
        for (FleetExcel f : fleetExcelsList) {
            if (StringUtils.isEmpty(f.getUserName())) {
                if (StringUtils.isNotEmpty(f.getCcf())) {
                    ccf = ccf.add(new BigDecimal(f.getCcf()));
                } else {
                    ccf = ccf.add(new BigDecimal(0));
                }
                if (StringUtils.isNotEmpty(f.getfAmount())) {
                    fAmount = fAmount.add(new BigDecimal(f.getfAmount()));
                } else {
                    fAmount = fAmount.add(new BigDecimal(0));
                }
                if (StringUtils.isNotEmpty(f.getTotalAmount())) {
                    totalAmount = totalAmount.add(new BigDecimal(f.getTotalAmount()));
                } else {
                    totalAmount = totalAmount.add(new BigDecimal(0));
                }
            }
        }
        stringList.add(ccf + "");
        stringList.add(fAmount + "");
        stringList.add(totalAmount + "");
        return stringList;
    }

    @Override
    public List<Map<String, Object>> accountsDue() {
        return tWarehousebillsfeesMapper.accountsDue();
    }

    @Override
    public List<QueryMenu> selectQueryMenuList(TWarehousebillsfees tWarehousebillsfees) {
        return tWarehousebillsfeesMapper.selectQueryMenuList(tWarehousebillsfees);
    }

    @Override
    public AjaxResult exportBillSubItemDetails(TWarehousebillsfees tWarehousebillsfees,String excelName) {
        // 创建 workbook，对应一个 excel 文件
        HSSFWorkbook wb = new HSSFWorkbook();
        // 在 workbook 中添加一个sheet，对应 excel 文档中的 sheet
        HSSFSheet sheet = wb.createSheet(excelName);;
        // 获取 cell 样式
        HSSFCellStyle cellTitleStyle = getCellStyle(wb, "center", true);
        HSSFCellStyle cellContentStyle = getCellStyle(wb, "center", true);
        // 创建标题，合并标题单元格
        // 行号
        int rowNum = 0;
        // 第一行
        HSSFRow first = sheet.createRow(rowNum++);
        String[] left = {"序号", "业务类型", "作业类型", "客户", "提单号", "货名", "件数", "重量", "体积", "入库时间", "出库时间", "计算天数"};
        List<String> middle = tFeesMapper.selectTFeesNameList();
        String[] right = {"金额", "计划员", "计费时间", "结费时间", "仓库", "备注"};

        List<String> all = new ArrayList<>();
        Collections.addAll(all, left);
        all.addAll(middle);
        Collections.addAll(all, right);

        int size = all.size();
        for (int i = 0; i < size; i++) {
            HSSFCell tempCell = first.createCell(i);
            if (i > 11 && i < size - 6) {
                tempCell.setCellValue(all.get(i));
            } else {
                tempCell.setCellValue(all.get(i));
            }
            tempCell.setCellStyle(cellTitleStyle);
        }
        // 根据客户名称，发货方查询清单数据
        List<Map<String, Object>> list = tWarehousebillsfeesMapper.selectBillDetailsSubItemNew(tWarehousebillsfees);
        if (list.size() < 1) {
            return AjaxResult.error("无数据，导出Excel失败");
        }

        int index = 1;
        for (Map<String, Object> map : list) {
            // 根据 提单号+客户名称，获取费用数据
            String fMblno = StringUtils.objToStr(map.get("fMblno"));
            String fName = StringUtils.objToStr(map.get("tcfName"));
            List<Map<String, Object>> fees;
            if (StringUtils.isNotEmpty(fMblno)) {
                fees = tWarehousebillsfeesMapper.selectFeesBymblnoAndFname(fMblno, fName);
            } else {
                fees = new ArrayList<>();
            }
            // 初始化金额
            BigDecimal allAmount = BigDecimal.ZERO;
            Map<Integer, String> feeMap = new HashMap<>();
            for (Map<String, Object> fee : fees) {
                // 获取费用名称
                String feeName = StringUtils.objToStr(fee.get("feeName"));
                // 三元表达式校验金额是否为空，为空则为字符串 0，不为空则返回单价字符串
                String fAmount = fee.get("fAmount") == null ? "0" : String.valueOf(fee.get("fAmount"));
                // 金额
                allAmount = allAmount.add(new BigDecimal(fee.get("fAmount") == null ? "0" : String.valueOf(fee.get("fAmount"))));
                // 根据费用名称中间部分的数组下标
                int j = middle.indexOf(feeName);
                if (j >= 0) { // 根据下标，对费用名称下的数据进行传值
                    feeMap.put(12 + j, fAmount);
                }
            }
            HSSFRow tempRow = sheet.createRow(rowNum++);
            // 循环单元格填入数据
            for (int i = 0; i < size; i++) {
                HSSFCell temCell = tempRow.createCell(i);
                String cellValue = "";
                if (i == 0) {
                    // 序号
                    cellValue = StringUtils.objToStr(index++);
                } else if (i == 1) {
                    // 业务类型
                    cellValue = StringUtils.objToStr(map.get("fBilltype"));
                } else if (i == 2) {
                    // 作业类型
                    cellValue = StringUtils.objToStr(map.get("fBusinesstype"));
                } else if (i == 3) {
                    // 客户
                    cellValue = StringUtils.objToStr(map.get("tcfName"));
                } else if (i == 4) {
                    // 提单号
                    cellValue = StringUtils.objToStr(map.get("fMblno"));
                } else if (i == 5) {
                    // 货名
                    cellValue = StringUtils.objToStr(map.get("tgfName"));
                } else if (i == 6) {
                    // 件数
                    cellValue = StringUtils.objToStr(map.get("fQty"));
                } else if (i == 7) {
                    // 重量
                    cellValue = StringUtils.objToStr(map.get("fNetweight"));
                } else if (i == 8) {
                    // 体积
                    cellValue = StringUtils.objToStr(map.get("fVolumn"));
                } else if (i == 9) {
                    // 入库时间
                    cellValue = StringUtils.objToStr(map.get("fOriginalbilldate"));
                } else if (i == 10) {
                    // 出库时间
                    cellValue = StringUtils.objToStr(map.get("fBsdate"));
                } else if (i == 11) {
                    // 计费天数
                    cellValue = StringUtils.objToStr(map.get("fBillingdays"));
                } else if (i == size - 6) {
                    // 金额
                    cellValue = allAmount.toString();
                } else if (i == size - 5) {
                    // 计划员
                    cellValue = StringUtils.objToStr(map.get("createBy"));
                } else if (i == size - 4) {
                    // 计费时间
                    cellValue = StringUtils.objToStr(map.get("fChargedate"));
                } else if (i == size - 3) {
                    // 结费时间
                    cellValue = StringUtils.objToStr(map.get("fStlamountdate"));
                } else if (i == size - 2) {
                    // 仓库
                    cellValue = StringUtils.objToStr(map.get("fhName"));
                } else if (i == size - 1) {
                    // 备注
                    cellValue = StringUtils.objToStr(map.get("remark"));
                }
                if (i <= 5 || i >= 9 && i <= 11 || i >= -5 && i <= -1) {
                    temCell.setCellStyle(cellContentStyle);
                    temCell.setCellValue(cellValue);
                } else {
                    // 将 excel 中的数字 String 转换成 原本类型
                    switchExcelStringToOther(wb,cellValue,cellContentStyle,temCell);
                }
            }

            feeMap.forEach((kev, value) -> {
                //tempRow.getCell(kev).setCellValue(value);
                // 对金额进行字符串转数字
                switchExcelStringToOther(wb,value,cellContentStyle,tempRow.getCell(kev));
            });
        }

        for (int i = 0; i < size; i++) {
            sheet.autoSizeColumn(i);
            sheet.setColumnWidth(i, sheet.getColumnWidth(i) * 17 / 10);
        }
        // 导出 excel
        String fileName = excelName + ".xls";
        // 创建导出流
        try {
            OutputStream out = new FileOutputStream(getAbsoluteFile(fileName));
            wb.write(out);
            wb.close();
            out.close();
            return AjaxResult.success(fileName);
        } catch (Exception e) {
            e.printStackTrace();
            return AjaxResult.error("导出Excel失败");
        }
    }

    private String getAbsoluteFile(String fileName) {
        String downloadPath = RuoYiConfig.getDownloadPath() + fileName;
        File desc = new File(downloadPath);
        if (!desc.getParentFile().exists()) {
            desc.getParentFile().mkdirs();
        }
        return downloadPath;
    }

    /**
     * 将 excel 表格中的金额型数字型字符串转换成原本类型
     * @param wb 工作簿
     * @param cellValue 值
     * @param cellContentStyle 样式
     * @param temCell cell
     */
    public void switchExcelStringToOther(HSSFWorkbook wb,String cellValue,HSSFCellStyle cellContentStyle,HSSFCell temCell){
        // 数据是否是数值型
        boolean isNum = false;
        // 数据是否是整数
        boolean isInteger = false;
        // 是否为百分数
        boolean isPercent = false;
        if (cellValue != null) {
            // 判断结果为数值型
            isNum = cellValue.matches("^(-?\\d+)(\\.\\d+)?$");
            // 判断结果是否为正数（小数部分是否为0）
            isInteger = cellValue.matches("^[-\\+]?[\\d]*$");
            // 判断结果是否为百分数
            isPercent = cellValue.contains("%");
        }
        if (isNum && !isPercent) {
            // 设置数据格式
            HSSFDataFormat df = wb.createDataFormat();
            if (isInteger) {
                // 只显示整数
                cellContentStyle.setDataFormat(df.getFormat("#0"));
            } else {
                // 小数
                cellContentStyle.setDataFormat(df.getFormat("##0.00"));
            }
            // 单元格格式
            temCell.setCellStyle(cellContentStyle);
            // 设置单元格内容为 double
            temCell.setCellValue(Double.parseDouble(cellValue));
        } else {
            temCell.setCellStyle(cellContentStyle);
            temCell.setCellValue(cellValue);
        }
    }

    /**
     * 封装大标题实现复用
     *
     * @param title         标题
     * @param row           所在行
     * @param sheet         sheet
     * @param cellStyle     样式
     * @param cellAddresses 合并行参数
     */
    public void setBigTitle(String title, int row, HSSFSheet sheet, HSSFCellStyle cellStyle, CellRangeAddress cellAddresses) {
        // 合并参数
        sheet.addMergedRegion(cellAddresses);
        // 大标题部分
        HSSFRow bigTitleRow = sheet.createRow(row);
        HSSFCell cell = bigTitleRow.createCell(0);
        cell.setCellValue(title);
        cell.setCellStyle(cellStyle);
    }

    /**
     * 设置 excel 内内容字体
     *
     * @param style    传入的已设置的 excel 样式
     * @param workbook 工作簿
     * @param fontName 字体名称，例如： "宋体"
     * @param fontSize 字体大小
     * @param isBold   是否加粗，true：加粗，false：不加粗
     */
    public void setFont(HSSFCellStyle style, HSSFWorkbook workbook, String fontName, int fontSize, boolean isBold) {
        HSSFFont font = workbook.createFont();
        // 设置字体
        font.setFontName(fontName);
        // 是否加粗
        font.setBold(isBold);
        // 字体大小
        font.setFontHeightInPoints((short) fontSize);
        // 需要设置在谁上
        style.setFont(font);
    }

    /**
     * 获取并设置工作表 excel 样式
     *
     * @param workbook  工作簿
     * @param position  位置参数：left-居左，right-居右，center-居中
     * @param showFrame 是否显示边框，true：显示，false：不显示
     * @return 已设置的 excel 样式
     */
    public HSSFCellStyle getCellStyle(HSSFWorkbook workbook, String position, boolean showFrame) {
        // 创建标题的显示样式
        HSSFCellStyle headerStyle = workbook.createCellStyle();
        // 设置水平垂直居中+上下左右边框+边框是否显示
        setHeaderStyle(headerStyle, position, showFrame);
        // 返回
        return headerStyle;
    }

    /**
     * 封装边框以及水平+垂直居中+边框是否显示
     *
     * @param headerStyle 样式
     * @param position    位置参数：left-居左，right-居右，center-居中
     * @param showFrame   是否显示边框，true：显示，false：不显示
     */
    public void setHeaderStyle(HSSFCellStyle headerStyle, String position, boolean showFrame) {
        // 水平位置摆放
        switch (position) {
            case "left": // 居左
                headerStyle.setAlignment(HorizontalAlignment.LEFT);
                break;
            case "right": // 居右
                headerStyle.setAlignment(HorizontalAlignment.RIGHT);
                break;
            case "center": // 居中
                headerStyle.setAlignment(HorizontalAlignment.CENTER);
                break;
        }
        // 垂直居中
        headerStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        if (showFrame) {
            // 上边框
            headerStyle.setBorderTop(BorderStyle.THIN);
            // 下边框
            headerStyle.setBorderBottom(BorderStyle.THIN);
            // 左边框
            headerStyle.setBorderLeft(BorderStyle.THIN);
            // 右边框
            headerStyle.setBorderRight(BorderStyle.THIN);
        }
    }
}