package com.yh.saas.plugin.yiliangyiyun.service.impl;

import com.baomidou.mybatisplus.mapper.EntityWrapper;
import com.winsea.svc.base.base.util.DateUtils;
import com.yh.saas.common.support.util.IdGenerator;
import com.yh.saas.plugin.yiliangyiyun.entity.*;
import com.baomidou.mybatisplus.plugins.Page;
import com.google.common.collect.Lists;
import com.yh.saas.plugin.yiliangyiyun.mapper.StorageFeeRecordMapper;
import com.yh.saas.plugin.yiliangyiyun.service.IContractManagementInfoService;
import com.yh.saas.plugin.yiliangyiyun.service.IStockSaleReceiptReportService;
import com.yh.saas.plugin.yiliangyiyun.service.IStorageFeeRecordService;
import com.baomidou.mybatisplus.service.impl.ServiceImpl;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.httpclient.util.DateUtil;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.beans.factory.annotation.Autowired;
import org.apache.commons.collections.CollectionUtils;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.IOException;
import java.net.URLDecoder;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * <p>
 * 记录代储费信息 服务实现类
 * </p>
 *
 * @author Gdc
 * @since 2022-01-18
 */
@Service
@Slf4j
public class StorageFeeRecordServiceImpl extends ServiceImpl<StorageFeeRecordMapper, StorageFeeRecord> implements IStorageFeeRecordService {


    @Autowired
    private IContractManagementInfoService contractManagementInfoService;
    @Autowired
    private IStockSaleReceiptReportService stockSaleReceiptReportService;
    @Value("${file-root-path}")
    private String localPath;

    /**
     * 生成代储费记录
     *
     * @param contractNo
     * @return
     */
    @Override
    public String addStorageFeeRecord(String contractNo) {

        // 查询合同信息
        ContractManagementInfo contractManagementInfo = contractManagementInfoService.selectOne(new EntityWrapper<ContractManagementInfo>().eq("contract_no", contractNo));
        if (contractManagementInfo != null) {
            // 判断代储时间和现时间(返回值1 开始大于结束   2 开始小于结束 3 开始等于结束)
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            try {
                String dateFlag1 = comparetoTime(sdf.format(new Date()), sdf.format(contractManagementInfo.getStorageFeeStartdate()));
                // 开始时间大于当天时间 可继续执行
                if (!"2".equals(dateFlag1)) {
                    String dateFlag = comparetoTime(sdf.format(new Date()), sdf.format(contractManagementInfo.getStorageFeeEnddate()));
                    // 结束时间 在当天时间 之前
                    if (!"2".equals(dateFlag)) {
                        EntityWrapper<StockSaleReceiptReport> salesDeliveryReportEntityWrapper = new EntityWrapper<StockSaleReceiptReport>();
                        salesDeliveryReportEntityWrapper.eq("contract_no", contractNo);
                        salesDeliveryReportEntityWrapper.and("DATE_FORMAT(retrieval_date,\"%Y%m%d\") &lt;=\n" +
                                "DATE_FORMAT('" + contractManagementInfo.getStorageFeeEnddate() + "',\"%Y%m%d\")");
                        salesDeliveryReportEntityWrapper.groupBy("retrieval_date");
                        List<StockSaleReceiptReport> salesDeliveryReports = stockSaleReceiptReportService.selectList(salesDeliveryReportEntityWrapper);
                        if (!CollectionUtils.isEmpty(salesDeliveryReports)) {
                            for (int i = 0; i < salesDeliveryReports.size(); i++) {
                                // 查询是否已经生成记录
                                EntityWrapper<StorageFeeRecord> storageFeeRecordEntityWrapper = new EntityWrapper<StorageFeeRecord>();
                                storageFeeRecordEntityWrapper.eq("contract_no", contractNo);
                                storageFeeRecordEntityWrapper.and("DATE_FORMAT(storage_fee_date,\"%Y%m%d\") =\n" +
                                        "DATE_FORMAT('" + salesDeliveryReports.get(i).getDeliveryDate() + "',\"%Y%m%d\")");
                                StorageFeeRecord storageFeeRecord1 = this.selectOne(storageFeeRecordEntityWrapper);
                                if (storageFeeRecord1 == null) {
                                    // 初始化
                                    StorageFeeRecord storageFeeRecord = new StorageFeeRecord();
                                    storageFeeRecord.setId(IdGenerator.generateUUID());
                                    storageFeeRecord.setGoodsNameKey(contractManagementInfo.getGoodsNameKey());
                                    storageFeeRecord.setGoodsName(contractManagementInfo.getGoodsName());
                                    storageFeeRecord.setStorageFeeDate(salesDeliveryReports.get(i).getDeliveryDate());
                                    storageFeeRecord.setStorageVolume(0f);
                                    // 查询当日出库量
                                    EntityWrapper<StockSaleReceiptReport> salesDeliveryReportEntityWrapper1 = new EntityWrapper<StockSaleReceiptReport>();
                                    salesDeliveryReportEntityWrapper1.eq("contract_no", contractNo);
                                    salesDeliveryReportEntityWrapper1.and("DATE_FORMAT(retrieval_date,\"%Y%m%d\") =\n" +
                                            "DATE_FORMAT('" + contractManagementInfo.getStorageFeeEnddate() + "',\"%Y%m%d\")");
                                    List<StockSaleReceiptReport> salesDeliveryReports1 = stockSaleReceiptReportService.selectList(salesDeliveryReportEntityWrapper1);
                                    Float ton = 0f;
                                    if (!CollectionUtils.isEmpty(salesDeliveryReports1)) {
                                        for (int j = 0; j < salesDeliveryReports1.size(); j++) {
                                            ton = ton + Float.valueOf(String.valueOf(salesDeliveryReports1.get(i).getNetWeight()));

                                        }

                                    }
                                    storageFeeRecord.setDeliveryVolume(ton);
                                    // 计算当日计费储量
                                    String dateAfter = getSpecifiedDayBefore(sdf.format(salesDeliveryReports.get(i).getDeliveryDate()));
                                    EntityWrapper<StorageFeeRecord> storageFeeRecordEntityWrapper2 = new EntityWrapper<StorageFeeRecord>();
                                    storageFeeRecordEntityWrapper2.eq("contract_no", contractNo);
                                    storageFeeRecordEntityWrapper2.and("DATE_FORMAT(storage_fee_date,\"%Y%m%d\") =\n" +
                                            "DATE_FORMAT('" + dateAfter + "',\"%Y%m%d\")");
                                    StorageFeeRecord storageFeeRecord2 = this.selectOne(storageFeeRecordEntityWrapper);
                                    if (storageFeeRecord2 != null) {
                                        storageFeeRecord.setDailyBillingReserves(storageFeeRecord2.getDailyBillingReserves() - storageFeeRecord.getDeliveryVolume() + storageFeeRecord.getStorageVolume());
                                    } else {
                                        storageFeeRecord.setDailyBillingReserves(contractManagementInfo.getStorageFeeWeight() - storageFeeRecord.getDeliveryVolume() + storageFeeRecord.getStorageVolume());
                                    }
                                    storageFeeRecord.setStorageFee(1.0);
                                    storageFeeRecord.setReceivable(storageFeeRecord.getStorageFee()*storageFeeRecord.getDailyBillingReserves());

                                }
                                // 已经生成过记录修改当日出库量
                                else {
                                    // 查询当日出库量
                                    EntityWrapper<StockSaleReceiptReport> salesDeliveryReportEntityWrapper1 = new EntityWrapper<StockSaleReceiptReport>();
                                    salesDeliveryReportEntityWrapper1.eq("contract_no", contractNo);
                                    salesDeliveryReportEntityWrapper1.and("DATE_FORMAT(retrieval_date,\"%Y%m%d\") =\n" +
                                            "DATE_FORMAT('" + contractManagementInfo.getStorageFeeEnddate() + "',\"%Y%m%d\")");
                                    List<StockSaleReceiptReport> salesDeliveryReports1 = stockSaleReceiptReportService.selectList(salesDeliveryReportEntityWrapper1);
                                    Float ton = 0f;
                                    if (!CollectionUtils.isEmpty(salesDeliveryReports1)) {
                                        for (int j = 0; j < salesDeliveryReports1.size(); j++) {
                                            ton = ton + Float.valueOf(String.valueOf(salesDeliveryReports1.get(i).getNetWeight()));
                                        }

                                    }
                                    storageFeeRecord1.setDeliveryVolume(ton);
                                    // 计算当日计费储量
                                    String dateAfter = getSpecifiedDayBefore(sdf.format(salesDeliveryReports.get(i).getDeliveryDate()));
                                    EntityWrapper<StorageFeeRecord> storageFeeRecordEntityWrapper2 = new EntityWrapper<StorageFeeRecord>();
                                    storageFeeRecordEntityWrapper2.eq("contract_no", contractNo);
                                    storageFeeRecordEntityWrapper2.and("DATE_FORMAT(storage_fee_date,\"%Y%m%d\") =\n" +
                                            "DATE_FORMAT('" + dateAfter + "',\"%Y%m%d\")");
                                    StorageFeeRecord storageFeeRecord2 = this.selectOne(storageFeeRecordEntityWrapper);
                                    if (storageFeeRecord2 != null) {
                                        storageFeeRecord1.setDailyBillingReserves(storageFeeRecord2.getDailyBillingReserves() - storageFeeRecord1.getDeliveryVolume() + storageFeeRecord1.getStorageVolume());
                                    } else {
                                        storageFeeRecord1.setDailyBillingReserves(contractManagementInfo.getStorageFeeWeight() - storageFeeRecord1.getDeliveryVolume() + storageFeeRecord1.getStorageVolume());
                                    }
                                    storageFeeRecord1.setReceivable(storageFeeRecord1.getStorageFee()*storageFeeRecord1.getDailyBillingReserves());
                                    this.updateById(storageFeeRecord1);

                                }

                            }

                        }


                    } else {



                        EntityWrapper<StockSaleReceiptReport> salesDeliveryReportEntityWrapper = new EntityWrapper<StockSaleReceiptReport>();
                        salesDeliveryReportEntityWrapper.eq("contract_no", contractNo);
                        salesDeliveryReportEntityWrapper.and("DATE_FORMAT(retrieval_date,\"%Y%m%d\") &lt;=\n" +
                                "DATE_FORMAT('" + new Date() + "',\"%Y%m%d\")");
                        salesDeliveryReportEntityWrapper.groupBy("retrieval_date");
                        List<StockSaleReceiptReport> salesDeliveryReports = stockSaleReceiptReportService.selectList(salesDeliveryReportEntityWrapper);
                        if (!CollectionUtils.isEmpty(salesDeliveryReports)) {
                            for (int i = 0; i < salesDeliveryReports.size(); i++) {
                                // 查询是否已经生成记录
                                EntityWrapper<StorageFeeRecord> storageFeeRecordEntityWrapper = new EntityWrapper<StorageFeeRecord>();
                                storageFeeRecordEntityWrapper.eq("contract_no", contractNo);
                                storageFeeRecordEntityWrapper.and("DATE_FORMAT(storage_fee_date,\"%Y%m%d\") =\n" +
                                        "DATE_FORMAT('" + salesDeliveryReports.get(i).getDeliveryDate() + "',\"%Y%m%d\")");
                                StorageFeeRecord storageFeeRecord1 = this.selectOne(storageFeeRecordEntityWrapper);
                                if (storageFeeRecord1 == null) {
                                    // 初始化
                                    StorageFeeRecord storageFeeRecord = new StorageFeeRecord();
                                    storageFeeRecord.setId(IdGenerator.generateUUID());
                                    storageFeeRecord.setGoodsNameKey(contractManagementInfo.getGoodsNameKey());
                                    storageFeeRecord.setGoodsName(contractManagementInfo.getGoodsName());
                                    storageFeeRecord.setStorageFeeDate(salesDeliveryReports.get(i).getDeliveryDate());
                                    storageFeeRecord.setStorageVolume(0f);
                                    // 查询当日出库量
                                    EntityWrapper<StockSaleReceiptReport> salesDeliveryReportEntityWrapper1 = new EntityWrapper<StockSaleReceiptReport>();
                                    salesDeliveryReportEntityWrapper1.eq("contract_no", contractNo);
                                    salesDeliveryReportEntityWrapper1.and("DATE_FORMAT(retrieval_date,\"%Y%m%d\") =\n" +
                                            "DATE_FORMAT('" + contractManagementInfo.getStorageFeeEnddate() + "',\"%Y%m%d\")");
                                    List<StockSaleReceiptReport> salesDeliveryReports1 = stockSaleReceiptReportService.selectList(salesDeliveryReportEntityWrapper1);
                                    Float ton = 0f;
                                    if (!CollectionUtils.isEmpty(salesDeliveryReports1)) {
                                        for (int j = 0; j < salesDeliveryReports1.size(); j++) {
                                            ton = ton + Float.valueOf(String.valueOf(salesDeliveryReports1.get(i).getNetWeight()));
                                        }

                                    }
                                    storageFeeRecord.setDeliveryVolume(ton);
                                    // 计算当日计费储量
                                    String dateAfter = getSpecifiedDayBefore(sdf.format(salesDeliveryReports.get(i).getDeliveryDate()));
                                    EntityWrapper<StorageFeeRecord> storageFeeRecordEntityWrapper2 = new EntityWrapper<StorageFeeRecord>();
                                    storageFeeRecordEntityWrapper2.eq("contract_no", contractNo);
                                    storageFeeRecordEntityWrapper2.and("DATE_FORMAT(storage_fee_date,\"%Y%m%d\") =\n" +
                                            "DATE_FORMAT('" + dateAfter + "',\"%Y%m%d\")");
                                    StorageFeeRecord storageFeeRecord2 = this.selectOne(storageFeeRecordEntityWrapper);
                                    if (storageFeeRecord2 != null) {
                                        storageFeeRecord.setDailyBillingReserves(storageFeeRecord2.getDailyBillingReserves() - storageFeeRecord.getDeliveryVolume() + storageFeeRecord.getStorageVolume());
                                    } else {
                                        storageFeeRecord.setDailyBillingReserves(contractManagementInfo.getStorageFeeWeight() - storageFeeRecord.getDeliveryVolume() + storageFeeRecord.getStorageVolume());
                                    }
                                    storageFeeRecord.setStorageFee(1.0);
                                    storageFeeRecord.setReceivable(storageFeeRecord.getStorageFee()*storageFeeRecord.getDailyBillingReserves());

                                }
                                // 已经生成过记录修改当日出库量
                                else {
                                    // 查询当日出库量
                                    EntityWrapper<StockSaleReceiptReport> salesDeliveryReportEntityWrapper1 = new EntityWrapper<StockSaleReceiptReport>();
                                    salesDeliveryReportEntityWrapper1.eq("contract_no", contractNo);
                                    salesDeliveryReportEntityWrapper1.and("DATE_FORMAT(retrieval_date,\"%Y%m%d\") =\n" +
                                            "DATE_FORMAT('" + contractManagementInfo.getStorageFeeEnddate() + "',\"%Y%m%d\")");
                                    List<StockSaleReceiptReport> salesDeliveryReports1 = stockSaleReceiptReportService.selectList(salesDeliveryReportEntityWrapper1);
                                    Float ton = 0f;
                                    if (!CollectionUtils.isEmpty(salesDeliveryReports1)) {
                                        for (int j = 0; j < salesDeliveryReports1.size(); j++) {
                                            ton = ton + Float.valueOf(String.valueOf(salesDeliveryReports1.get(i).getNetWeight()));
                                        }

                                    }
                                    storageFeeRecord1.setDeliveryVolume(ton);
                                    // 计算当日计费储量
                                    String dateAfter = getSpecifiedDayBefore(sdf.format(salesDeliveryReports.get(i).getDeliveryDate()));
                                    EntityWrapper<StorageFeeRecord> storageFeeRecordEntityWrapper2 = new EntityWrapper<StorageFeeRecord>();
                                    storageFeeRecordEntityWrapper2.eq("contract_no", contractNo);
                                    storageFeeRecordEntityWrapper2.and("DATE_FORMAT(storage_fee_date,\"%Y%m%d\") =\n" +
                                            "DATE_FORMAT('" + dateAfter + "',\"%Y%m%d\")");
                                    StorageFeeRecord storageFeeRecord2 = this.selectOne(storageFeeRecordEntityWrapper);
                                    if (storageFeeRecord2 != null) {
                                        storageFeeRecord1.setDailyBillingReserves(storageFeeRecord2.getDailyBillingReserves() - storageFeeRecord1.getDeliveryVolume() + storageFeeRecord1.getStorageVolume());
                                    } else {
                                        storageFeeRecord1.setDailyBillingReserves(contractManagementInfo.getStorageFeeWeight() - storageFeeRecord1.getDeliveryVolume() + storageFeeRecord1.getStorageVolume());
                                    }
                                    storageFeeRecord1.setReceivable(storageFeeRecord1.getStorageFee()*storageFeeRecord1.getDailyBillingReserves());
                                    this.updateById(storageFeeRecord1);

                                }

                            }

                        }



                    }
                }
            } catch (Exception e) {

            }
        }
        return "ok";
    }


    /**
     * 获得指定日期的前一天
     *
     * @param specifiedDay
     * @return
     * @throws Exception
     */
    public static String getSpecifiedDayBefore(String specifiedDay) {
//SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
        Calendar c = Calendar.getInstance();
        Date date = null;
        try {
            date = new SimpleDateFormat("yy-MM-dd").parse(specifiedDay);
        } catch (ParseException e) {
            e.printStackTrace();
        }
        c.setTime(date);
        int day = c.get(Calendar.DATE);
        c.set(Calendar.DATE, day - 1);

        String dayBefore = new SimpleDateFormat("yyyy-MM-dd").format(c.getTime());
        return dayBefore;
    }

    public String comparetoTime(String beginTime, String endTime) throws ParseException {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        //String beginTime = "2018-06-01";
        //String endTime = "2018-05-31";
        Date bt = sdf.parse(beginTime);
        Date et = sdf.parse(endTime);
        if (bt.before(et)) {
            //表示bt小于et
            return "1";
        } else {
            //--反之
            if (beginTime.equals(endTime)) {
                return "3";
            } else {
                return "2";
            }

        }

    }
    @Override
    public Page<StorageFeeRecord> selectInfo(StorageFeeRecord storageFeeRecord) {
        Map<String, Object> pageView = new HashMap<>();
        pageView.put("startRecord", (storageFeeRecord.getCurrentPage() - 1)
                * storageFeeRecord.getPageSize());
        //公司id
        pageView.put("compId", storageFeeRecord.getCompId());
        pageView.put("pageSize", storageFeeRecord.getPageSize());
        pageView.put("currentPage", storageFeeRecord.getCurrentPage());
        pageView.put("startDate", storageFeeRecord.getStartDate());
        pageView.put("contractNo", storageFeeRecord.getContractNo());
        pageView.put("endDate", storageFeeRecord.getEndDate());
        Integer dataCount = baseMapper.getCountByCondition(pageView);
        List<StorageFeeRecord> dataList = baseMapper.getListByCondition(pageView);
        Page<StorageFeeRecord> page = new Page<>();
        page.setRecords(dataList == null ? Lists.newArrayList() : dataList);
        page.setTotal(dataCount == null ? 0 : dataCount);
        page.setCurrent(storageFeeRecord.getCurrentPage());
        page.setSize(storageFeeRecord.getPageSize());
        return page;
    }

    /**
     * 修改
     * @param storageFeeRecord
     * @return
     */
    @Override
    public String editInfo(StorageFeeRecord storageFeeRecord) {
        //修改代储费
        if ("1".equals(storageFeeRecord.getFlag())) {
            StorageFeeRecord storageFeeRecord1 = this.selectById(storageFeeRecord.getId());
            List<StorageFeeRecord> storageFeeRecordList = this.selectList(new EntityWrapper<StorageFeeRecord>()
                    .eq("comp_id", storageFeeRecord1.getCompId())
                    .eq("contract_no", storageFeeRecord1.getContractNo())
                    .gt("storage_fee_date", storageFeeRecord1.getStorageFeeDate()));
            if (CollectionUtils.isNotEmpty(storageFeeRecordList)) {
                for (StorageFeeRecord storageFeeRecord2 : storageFeeRecordList) {
                    storageFeeRecord2.setStorageFee(storageFeeRecord.getStorageFee());
                    this.updateById(storageFeeRecord2);
                }
            }
        }
        //终止
        else if ("2".equals(storageFeeRecord.getFlag())) {
            StorageFeeRecord storageFeeRecord1 = this.selectById(storageFeeRecord.getId());
            //时间大于当条的删除
            List<StorageFeeRecord> storageFeeRecordList = this.selectList(new EntityWrapper<StorageFeeRecord>()
                    .eq("comp_id", storageFeeRecord1.getCompId())
                    .eq("contract_no", storageFeeRecord1.getContractNo())
                    .gt("storage_fee_date", storageFeeRecord1.getStorageFeeDate()));
            if (CollectionUtils.isNotEmpty(storageFeeRecordList)) {
                for (StorageFeeRecord storageFeeRecord2 : storageFeeRecordList) {
                    this.deleteById(storageFeeRecord2.getId());
                }
            }
            //时间小于当条的状态打成已终止
            List<StorageFeeRecord> storageFeeRecordList1 = this.selectList(new EntityWrapper<StorageFeeRecord>()
                    .eq("comp_id", storageFeeRecord1.getCompId())
                    .eq("contract_no", storageFeeRecord1.getContractNo())
                    .le("storage_fee_date", storageFeeRecord1.getStorageFeeDate()));
            if (CollectionUtils.isNotEmpty(storageFeeRecordList1)) {
                for (StorageFeeRecord storageFeeRecord2 : storageFeeRecordList1) {
                    storageFeeRecord2.setEndFlag("1");
                    this.updateById(storageFeeRecord2);
                }
            }
        }
        this.updateById(storageFeeRecord);
        return "ok";
    }

    @Override
    public void export(StorageFeeRecord storageFeeRecord, HttpServletResponse response) throws Exception {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

        // 1.Excel的头部信息
        String headerTitle = "";

        // 2.创建一个webbook 对应一个Excel文件
        HSSFWorkbook wb = new HSSFWorkbook();

        // 3.在webbook中添加一个sheet，对应Excel文件中的sheet
        HSSFSheet sheet;

        sheet = wb.createSheet("代储费记录信息");
        headerTitle = "代储费记录信息";

        // 4.设置打印参数
        sheet.setMargin(HSSFSheet.TopMargin, (short) 1);// 页边距（上）
        sheet.setMargin(HSSFSheet.BottomMargin, (short) 1);// 页边距（下）
        sheet.setMargin(HSSFSheet.LeftMargin, (short) 1);// 页边距（左）
        sheet.setMargin(HSSFSheet.RightMargin, (short) 1);// 页边距（右）

        // 不显示网格
        sheet.setDisplayGridlines(false);
        // 设置水平居中
        sheet.setHorizontallyCenter(true);

        /**************** 数据行样式 start ****************/
        HSSFFont fontTitle = wb.createFont();
        fontTitle.setFontName("宋体");
        fontTitle.setFontHeightInPoints((short) 14);
        // 设置字体加粗
        fontTitle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

        HSSFFont fontComp = wb.createFont();
        fontComp.setFontName("宋体");
        fontComp.setFontHeightInPoints((short) 16);
        // 设置字体加粗
        fontComp.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

        HSSFFont fontDetail = wb.createFont();
        fontDetail.setFontName("宋体");
        fontDetail.setFontHeightInPoints((short) 12);

        // title
        HSSFCellStyle styleTitle = wb.createCellStyle();
        // 指定单元格居中对齐
        styleTitle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // 指定单元格垂直居中对齐
        styleTitle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        // 指定单元格自动换行
        styleTitle.setWrapText(true);
        // 设置单元格字体
        styleTitle.setFont(fontTitle);
        // 右边框
        styleTitle.setBorderRight(HSSFCellStyle.BORDER_NONE);
        // 左边框
        styleTitle.setBorderLeft(HSSFCellStyle.BORDER_NONE);
        // 上边框
        styleTitle.setBorderTop(HSSFCellStyle.BORDER_NONE);
        // 下边框
        styleTitle.setBorderBottom(HSSFCellStyle.BORDER_NONE);

        HSSFCellStyle contextstyle = wb.createCellStyle();
        // 指定单元格居中对齐
        contextstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // 指定单元格垂直居中对齐
        contextstyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        // 指定单元格自动换行
        contextstyle.setWrapText(true);
        // 设置单元格字体
        contextstyle.setFont(fontDetail);
        // 右边框
        contextstyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        // 左边框
        contextstyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        // 上边框
        contextstyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        // 下styleDetailTitle
        contextstyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);

        // title
        HSSFCellStyle styleComp = wb.createCellStyle();
        // 指定单元格居中对齐
        styleComp.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // 指定单元格垂直居中对齐
        styleComp.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        // 指定单元格自动换行
        styleComp.setWrapText(true);
        // 设置单元格字体
        styleComp.setFont(fontComp);
        // 右边框
        styleComp.setBorderRight(HSSFCellStyle.BORDER_NONE);
        // 左边框
        styleComp.setBorderLeft(HSSFCellStyle.BORDER_NONE);
        // 上边框
        styleComp.setBorderTop(HSSFCellStyle.BORDER_NONE);
        // 下边框
        styleComp.setBorderBottom(HSSFCellStyle.BORDER_NONE);

        HSSFCellStyle styleInfo = wb.createCellStyle();
        // 指定单元格居中对齐
        styleInfo.setAlignment(HSSFCellStyle.ALIGN_LEFT);
        // 指定单元格垂直居中对齐
        styleInfo.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        // 指定单元格自动换行
        styleInfo.setWrapText(true);
        // 设置单元格字体
        styleInfo.setFont(fontDetail);
        // 右边框
        styleInfo.setBorderRight(HSSFCellStyle.BORDER_NONE);
        // 左边框
        styleInfo.setBorderLeft(HSSFCellStyle.BORDER_NONE);
        // 上边框
        styleInfo.setBorderTop(HSSFCellStyle.BORDER_NONE);
        // 下边框
        styleInfo.setBorderBottom(HSSFCellStyle.BORDER_NONE);

        HSSFCellStyle styleDetail = wb.createCellStyle();
        // 指定单元格居中对齐
        styleDetail.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // 指定单元格垂直居中对齐
        styleDetail.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        // 指定单元格自动换行
        styleDetail.setWrapText(true);
        // 设置单元格字体
        styleDetail.setFont(fontDetail);
        // 右边框
        styleDetail.setBorderRight(HSSFCellStyle.BORDER_THIN);
        // 左边框
        styleDetail.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        // 上边框
        styleDetail.setBorderTop(HSSFCellStyle.BORDER_THIN);
        // 下styleDetailTitle
        styleDetail.setBorderBottom(HSSFCellStyle.BORDER_THIN);

        HSSFCellStyle styleDetailYellow = wb.createCellStyle();
        // 指定单元格居中对齐
        styleDetailYellow.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // 指定单元格垂直居中对齐
        styleDetailYellow.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        // 指定单元格自动换行
        styleDetailYellow.setWrapText(true);
        // 设置单元格字体
        styleDetailYellow.setFont(fontDetail);
        // 右边框
        styleDetailYellow.setBorderRight(HSSFCellStyle.BORDER_THIN);
        // 左边框
        styleDetailYellow.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        // 上边框
        styleDetailYellow.setBorderTop(HSSFCellStyle.BORDER_THIN);
        // 下styleDetailTitle
        styleDetailYellow.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        styleDetailYellow.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
        styleDetailYellow.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);


        HSSFCellStyle styleDetailLeft = wb.createCellStyle();
        // 指定单元格居中对齐
        styleDetailLeft.setAlignment(HSSFCellStyle.ALIGN_LEFT);
        // 指定单元格垂直居中对齐
        styleDetailLeft.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        // 指定单元格自动换行
        styleDetailLeft.setWrapText(true);
        // 设置单元格字体
        styleDetailLeft.setFont(fontDetail);
        // 右边框
        styleDetailLeft.setBorderRight(HSSFCellStyle.BORDER_THIN);
        // 左边框
        styleDetailLeft.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        // 上边框
        styleDetailLeft.setBorderTop(HSSFCellStyle.BORDER_THIN);
        // 下styleDetailTitle
        styleDetailLeft.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        /**************** 数据行样式 end ****************/

        // 设置列宽
        sheet.setColumnWidth(0, 1 * 256 + 184);
        sheet.setColumnWidth(1, 12 * 256 + 184);
        sheet.setColumnWidth(2, 12 * 256 + 184);
        sheet.setColumnWidth(3, 12 * 256 + 184);
        sheet.setColumnWidth(4, 12 * 256 + 184);
        sheet.setColumnWidth(5, 12 * 256 + 184);
        sheet.setColumnWidth(6, 12 * 256 + 184);
        sheet.setColumnWidth(7, 12 * 256 + 184);
        sheet.setColumnWidth(8, 1 * 256 + 184);

        // 创建单元格对象
        HSSFCell cell = null;

        // 创建打印设置对象
        HSSFPrintSetup ps = sheet.getPrintSetup();
        // 打印方向，true：横向，false：纵向(默认)
        ps.setLandscape(false);
        // // 打印质量
        // ps.setVResolution((short) 700)
        // 设置缩放比例
        ps.setScale((short) 80);
        // 纸张类型
        ps.setPaperSize(HSSFPrintSetup.A4_PAPERSIZE);


        int rownum = 1;
        HSSFRow row0 = sheet.createRow((int) rownum);
        row0.setHeightInPoints(25);
        cell = row0.createCell(1);
        cell.setCellStyle(styleTitle);
        cell.setCellValue("代储费记录信息");
        sheet.addMergedRegion(new CellRangeAddress(rownum, rownum, (short) 1, (short) 22));
        cell = row0.createCell(2);
        cell.setCellStyle(styleTitle);
        cell = row0.createCell(3);
        cell.setCellStyle(styleTitle);
        cell = row0.createCell(4);
        cell.setCellStyle(styleTitle);
        cell = row0.createCell(5);
        cell.setCellStyle(styleTitle);
        cell = row0.createCell(6);
        cell.setCellStyle(styleTitle);
        cell = row0.createCell(7);
        cell.setCellStyle(styleTitle);
        rownum++;

        HSSFRow row1 = sheet.createRow((int) rownum);
        row1.setHeightInPoints(30);
        cell = row1.createCell(1);
        cell.setCellValue("货名");
        cell.setCellStyle(styleDetail);
        cell = row1.createCell(2);
        cell.setCellValue("日期");
        cell.setCellStyle(styleDetail);
        cell = row1.createCell(3);
        cell.setCellValue("当日入库量(吨)");
        cell.setCellStyle(styleDetail);
        cell = row1.createCell(4);
        cell.setCellValue("当日出库量(吨)");
        cell.setCellStyle(styleDetail);
        cell = row1.createCell(5);
        cell.setCellValue("当日计费储量(吨)");
        cell.setCellStyle(styleDetail);
        cell = row1.createCell(6);
        cell.setCellValue("代储费(元/吨·天)");
        cell.setCellStyle(styleDetail);
        cell = row1.createCell(7);
        cell.setCellValue("应收(元)");
        cell.setCellStyle(styleDetail);


        rownum++;
        List<StorageFeeRecord> storageFeeRecordList=this.selectList(new EntityWrapper<StorageFeeRecord>()
            .eq("comp_id",storageFeeRecord.getCompId())
            .eq("contract_no",storageFeeRecord.getContractNo()));
        // 详情信息
        if (CollectionUtils.isNotEmpty(storageFeeRecordList)) {
            for (int i = 0; i < storageFeeRecordList.size(); i++) {
                StorageFeeRecord storageFeeRecord1=storageFeeRecordList.get(i);
                HSSFRow rowx = sheet.createRow((int) rownum);
                rowx.setHeightInPoints(20);
                cell = rowx.createCell(1);
                cell.setCellValue(storageFeeRecord1.getGoodsName());
                cell.setCellStyle(styleDetail);
                cell = rowx.createCell(2);
                String date = addDateOneDay(storageFeeRecord1.getStorageFeeDate()!=null?storageFeeRecord1.getStorageFeeDate():new Date());
                cell.setCellValue(date);
                cell.setCellStyle(styleDetail);
                cell = rowx.createCell(3);
                cell.setCellValue(editFloat(storageFeeRecord1.getStorageVolume()));
                cell.setCellStyle(contextstyle);
                HSSFDataFormat df = wb.createDataFormat(); // 此处设置数据格式
                contextstyle.setDataFormat(df.getBuiltinFormat("#,##0.00"));//保留两位小数点
                cell = rowx.createCell(4);
                cell.setCellValue(editFloat(storageFeeRecord1.getDeliveryVolume()));
                cell.setCellStyle(contextstyle);
                cell = rowx.createCell(5);
                cell.setCellValue(editFloat(storageFeeRecord1.getDailyBillingReserves()));
                cell.setCellStyle(contextstyle);
                cell = rowx.createCell(6);
                cell.setCellValue(editDouble(storageFeeRecord1.getStorageFee()));
                cell.setCellStyle(contextstyle);
                cell = rowx.createCell(7);
                cell.setCellValue(editDouble(storageFeeRecord1.getReceivable()));
                cell.setCellStyle(contextstyle);
                rownum++;
            }
        }



        String path = null;
        String tempPath = null;
        String excelPath = null;

        try {

            // 取得绝对路径
            tempPath = URLDecoder.decode((localPath), "utf-8");

            // EXCEL路径
            excelPath = "/web/temp/pay/" + DateUtil.formatDate(new Date(), DateUtils.DATE_FMT_YYYYMMDD_NS) + '/'
                    + UUID.randomUUID().toString().replaceAll("-", "");

            path = tempPath + excelPath + '/' + "合同信息"
                    + ".xls";

        } catch (Exception e) {
            log.debug(e.getMessage());
        }
        // 下载
        download(path, response, wb);

    }
    private String addDateOneDay(Date date) {
        SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
        sdf.setTimeZone(TimeZone.getTimeZone("Asia/Shanghai"));
        return sdf.format(date);
    }

    /**
     * 判断浮点数
     *
     * @param example
     * @return
     */
    private Float editFloat(Float example) {
        Float exampleOne = example != null ? example : 0f;
        return exampleOne;
    }
    /**
     * 判断浮点数
     *
     * @param example
     * @return
     */
    private Double editDouble(Double example) {
        Double exampleOne = example != null ? example : 0d;
        return exampleOne;
    }

    /**
     * 判断字符串
     *
     * @param example
     * @return
     */
    private String editString(String example) {
        String exampleOne = example != null ? example : "";
        return exampleOne;
    }

    /**
     * 下载
     *
     * @param path
     * @param response
     */
    private void download(String path, HttpServletResponse response, Workbook wb) {

        try {

            // path是指欲下载的文件的路径。
            File file = new File(path);
            // 取得文件名。
            String fileName = file.getName();
            // 给文件名编码
            fileName = new String(fileName.getBytes("GBK"), "ISO-8859-1");

            response.setContentType("application/vnd.ms-excel");
            response.addHeader("Content-Disposition", "attachment;filename=" + fileName);

            // 定义byte输出流
            ByteArrayOutputStream out = new ByteArrayOutputStream();

            // 写导出文件
            wb.write(out);

            // 取得模板文件中的数据
            byte[] result = out.toByteArray();

            // 设置输出数据类型
            response.setContentType("application/vnd.ms-excel");
            // 设置输出数据长度
            response.setContentLength(result.length);

            // 设置文件名称
            response.setHeader("Content-Disposition", "attachment; filename=\"" + fileName + "\"");
            // 将文件流输出到画面
            response.getOutputStream().write(result);

            out.flush();
            out.close();

        } catch (IOException ex) {
            log.debug(ex.getMessage());
        }
    }
}
