package com.caimei.modules.order.web;

import com.caimei.modules.common.utils.ExcelUtil;
import com.caimei.modules.order.dao.CmDiscernReceiptDao;
import com.caimei.modules.order.dao.NewOrderDao;
import com.caimei.modules.order.entity.*;
import com.caimei.modules.order.service.*;
import com.caimei.modules.order.utils.OrderUtil;
import com.caimei.modules.user.dao.CmUserDao;
import com.caimei.modules.user.entity.CmUser;
import com.caimei.modules.user.entity.CmUserBalanceRecord;
import com.caimei.modules.user.entity.CmUserOrganize;
import com.caimei.modules.user.service.CmUserBalanceRecordService;
import com.caimei.modules.user.service.CmUserOrganizeService;
import com.caimei.utils.DateUtils;
import com.thinkgem.jeesite.common.config.Global;
import com.thinkgem.jeesite.common.persistence.Page;
import com.thinkgem.jeesite.common.utils.Encodes;
import com.thinkgem.jeesite.common.utils.StringUtils;
import com.thinkgem.jeesite.common.web.BaseController;
import org.apache.commons.collections.CollectionUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.shiro.authz.annotation.RequiresPermissions;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.ModelAttribute;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.servlet.mvc.support.RedirectAttributes;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayOutputStream;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 识别款项表Controller
 *
 * @author lwt
 * @version 2019-06-19
 */
@Controller
@RequestMapping(value = "${adminPath}/order/cmDiscernReceipt")
public class CmDiscernReceiptController extends BaseController {

    @Autowired
    private CmDiscernReceiptService cmDiscernReceiptService;
    @Resource
    private NewOrderDao newOrderDao;
    @Autowired
    private NewOrderProductService newOrderProductService;
    @Autowired
    private NewShopOrderService newShopOrderService;
    @Autowired
    private CmReceiptOrderRelationService cmReceiptOrderRelationService;
    @Autowired
    private NewOrderService newOrderService;
    @Resource
    private CmUserDao cmUserDao;
    @Resource
    private CmDiscernReceiptDao cmDiscernReceiptDao;
    @Autowired
    private CmPayShopRecordService cmPayShopRecordService;
    @Autowired
    private CmUserBalanceRecordService cmUserBalanceRecordService;
    @Autowired
    private CmUserOrganizeService cmUserOrganizeService;

    @ModelAttribute
    public CmDiscernReceipt get(@RequestParam(required = false) String id) {
        CmDiscernReceipt entity = null;
        if (StringUtils.isNotBlank(id)) {
            entity = cmDiscernReceiptService.get(id);
        }
        if (entity == null) {
            entity = new CmDiscernReceipt();
        }
        return entity;
    }

    @RequiresPermissions("order:cmDiscernReceipt:view")
    @RequestMapping(value = {"list", ""})
    public String list(CmDiscernReceipt cmDiscernReceipt, HttpServletRequest request, HttpServletResponse response, Model model) {
        if (null != cmDiscernReceipt.getStartTime() && !"".equals(cmDiscernReceipt.getStartTime()) && !cmDiscernReceipt.getStartTime().endsWith("00:00:00")) {
            model.addAttribute("startTime", cmDiscernReceipt.getStartTime());
            cmDiscernReceipt.setStartTime(cmDiscernReceipt.getStartTime().trim() + " 00:00:00");
        }
        if (null != cmDiscernReceipt.getEndTime() && !"".equals(cmDiscernReceipt.getEndTime()) && !cmDiscernReceipt.getEndTime().endsWith("23:59:59")) {
            model.addAttribute("endTime", cmDiscernReceipt.getEndTime());
            cmDiscernReceipt.setEndTime(cmDiscernReceipt.getEndTime().trim() + " 23:59:59");
        }
        Page<CmDiscernReceipt> page = cmDiscernReceiptService.findPage(new Page<CmDiscernReceipt>(request, response), cmDiscernReceipt);
        //获取组织列表
        List<CmUserOrganize> cmUserOrganizeList = cmUserOrganizeService.findOrganize();
        model.addAttribute("cmUserOrganizeList", cmUserOrganizeList);
        //
        model.addAttribute("page", page);
        return "modules/order/cmDiscernReceiptList";
    }

    @RequiresPermissions("order:cmDiscernReceipt:view")
    @RequestMapping(value = "form")
    public String form(CmDiscernReceipt cmDiscernReceipt, Model model) {
        model.addAttribute("cmDiscernReceipt", cmDiscernReceipt);
        return "modules/order/cmDiscernReceiptForm";
    }

    @RequiresPermissions("order:cmDiscernReceipt:edit")
    @RequestMapping(value = "save")
    public String save(CmDiscernReceipt cmDiscernReceipt, Model model, RedirectAttributes redirectAttributes) {
        if (!beanValidator(model, cmDiscernReceipt)) {
            return form(cmDiscernReceipt, model);
        }
        cmDiscernReceiptService.save(cmDiscernReceipt);
        addMessage(redirectAttributes, "保存识别款项表成功");
        return "redirect:" + Global.getAdminPath() + "/order/cmDiscernReceipt/?repage";
    }

    @RequiresPermissions("order:cmDiscernReceipt:delete")
    @RequestMapping(value = "delete")
    public String delete(CmDiscernReceipt cmDiscernReceipt, RedirectAttributes redirectAttributes) {
        cmDiscernReceiptService.delete(cmDiscernReceipt);
        addMessage(redirectAttributes, "删除识别款项表成功");
        return "redirect:" + Global.getAdminPath() + "/order/cmDiscernReceipt/?repage";
    }

    //------------------------------------------------------------
    //
    @RequiresPermissions("order:cmDiscernReceipt:view")
    @RequestMapping(value = "detail")
    public String detail(CmDiscernReceipt cmDiscernReceipt, Model model) {
        ReceiptDetailVo vo = cmDiscernReceiptService.detail(cmDiscernReceipt);
        model.addAttribute("vo", vo);
        return "modules/order/cmDiscernReceiptForm";
    }

    /**
     * 撤销收款
     *
     * @param cmDiscernReceipt
     * @param model
     * @return
     */
    @RequiresPermissions("order:cmDiscernReceipt:edit")
    @RequestMapping(value = "cancel")
    public String cancel(CmDiscernReceipt cmDiscernReceipt, Model model, String note, RedirectAttributes redirectAttributes) {
        try {
            cmDiscernReceiptService.cancel(cmDiscernReceipt, note);

            //撤销收款后如果存在多收款退款余额，则此处需要重新减少用户余额，以及撤销余额收支记录
            String orderID = cmDiscernReceipt.getOrderID();
            NewOrder newOrder = newOrderDao.get(orderID);
            String confirmType = cmDiscernReceipt.getConfirmType();
            if (StringUtils.isNotEmpty(confirmType) && StringUtils.equals("3", confirmType)) {
                //撤销余额收支记录
                CmUserBalanceRecord cmUserBalanceRecord = new CmUserBalanceRecord();
                cmUserBalanceRecord.setOrderId(newOrder.getOrderID());
                cmUserBalanceRecord.setReceiptId(Integer.parseInt(cmDiscernReceipt.getId()));
                cmUserBalanceRecord.setUserId(newOrder.getUserID());
                cmUserBalanceRecord.setRemark("撤销收款，余额记录无效-后台");
                cmUserBalanceRecord.setDelFlag("1");
                cmUserBalanceRecordService.updateBalanceRecordBuyReceipt(cmUserBalanceRecord);

                //回收用户退款余额
                Integer userID = newOrder.getUserID();
                CmUser cmUser = cmUserDao.get(String.valueOf(userID));
                String receiptAmount = cmDiscernReceipt.getReceiptAmount();//收款金额
                Double payableAmount = newOrder.getPayableAmount();//订单真实需支付金额
                Double receiptAmountDb = 0d;
                if (StringUtils.isNotEmpty(receiptAmount)) {
                    receiptAmountDb = Double.parseDouble(receiptAmount);
                }
                //多收退款余额金额
                Double blanceAmount = 0d;
                if (receiptAmountDb > payableAmount) {
                    blanceAmount = receiptAmountDb - payableAmount;
                }
                cmUser.setAbleUserMoney(cmUser.getAbleUserMoney() - blanceAmount);
                cmUser.setUserMoney(cmUser.getUserMoney() - blanceAmount);
                cmUserDao.updateMoney(cmUser);
            }

        } catch (Exception e) {
            addMessage(redirectAttributes, "数据异常,撤销收款失败");
            logger.info(e.getMessage());
            e.printStackTrace();
        }
        return "redirect:" + Global.getAdminPath() + "/order/cmDiscernReceipt/detail?repage&id=" + cmDiscernReceipt.getId();
    }
    //--------------------------------------------------------------

    /**
     * 导出订单信息
     *
     * @param cmDiscernReceipt
     * @return
     */
    @RequiresPermissions("order:cmDiscernReceipt:edit")
    @RequestMapping(value = "export")
    public void export(CmDiscernReceipt cmDiscernReceipt, RedirectAttributes redirectAttributes, HttpServletRequest request, HttpServletResponse response) {
        //获取订单数据
        String fileName = "收款列表.xls";
        String template = Global.getConfig("export.template");
        String templateFileName = template + "/discern_receipt.xls";
        try {
            Map data = new HashMap<String, Object>();
            List<CmDiscernReceipt> list = cmDiscernReceiptService.export(new Page<CmDiscernReceipt>(request, response), cmDiscernReceipt);
            data.put("list", list);
            data.put("orderUtil", new OrderUtil());
            new ExcelUtil().createExcel(templateFileName, data, fileName, response);
        } catch (Exception e) {
            addMessage(redirectAttributes, "收款列表导出失败！" + e.getMessage());
            logger.info(e.getMessage());
            e.printStackTrace();
        }
    }

    @RequestMapping(value = "exportTest")
    public void exportTest(String startTime, String endTime, CmDiscernReceipt cmDiscernReceipt, RedirectAttributes redirectAttributes, HttpServletRequest request, HttpServletResponse response) {
        //获取订单数据
        String fileName = "收款账单.xls";
        String template = Global.getConfig("export.template");
        String templateFileName = template + "/discern_receipt_export.xls";
        List<String> listSheetNames = new ArrayList<String>();
        ArrayList<List> objects = new ArrayList<List>();
        //收款时间筛查
        if (startTime == null) {
            cmDiscernReceipt.setStartTime("2019-10-01 00:00:00");
        } else {
            cmDiscernReceipt.setStartTime(startTime);
        }
        if (endTime == null) {
            cmDiscernReceipt.setEndTime("2019-10-31 23:59:59");
        } else {
            cmDiscernReceipt.setEndTime(endTime);
        }
        try {

            List<CmMainReceiptExport> list = cmDiscernReceiptService.exportTest(cmDiscernReceipt);
            for (CmMainReceiptExport bean : list) {
                String productNames = bean.getProductNames();
                String[] productNameArr = productNames.split(",");
                for (int i = 0; i < productNameArr.length; i++) {
                    productNameArr[i] = (i + 1) + "." + productNameArr[i] + "\r\n";
                    productNames = i==0?productNameArr[i]:productNames + productNameArr[i];
                }
                bean.setProductNames(productNames);
                bean.setPayType(OrderUtil.getPayTypeStr(bean.getPayType()));
            }
            //返佣数据
            List<CmMainReceiptExport> list1 = cmDiscernReceiptService.exportRebate(cmDiscernReceipt);
            for (CmMainReceiptExport export : list1) {
                export.setPayType(OrderUtil.getPayTypeStr(export.getPayType()));
                export.setOrderID(export.getOrderID() + ("5".equals(export.getReceiptType())?"(供应商退款订单)":"(返佣订单)"));
                list.add(export);
            }
            //非订单款
            List<CmMainReceiptExport> list2 = cmDiscernReceiptService.theOrder(cmDiscernReceipt);
            for (CmMainReceiptExport export : list2) {
                export.setOrderID("非订单");
                export.setUserName("");
                export.setPayType(OrderUtil.getPayTypeStr(export.getPayType()));
                list.add(export);
            }

            //退款记录表
            List<CmReturnedPurchase> purchases = cmDiscernReceiptService.exportRefund(cmDiscernReceipt);
            for (CmReturnedPurchase purchase : purchases) {
                String refundWay = "";
                if (purchase.getReturnedWay().equals("1")) {
                    if (purchase.getRefundBalanceFee() != null && purchase.getRefundBalanceFee() != 0) {
                        refundWay += "账户余额";
                    }
                    if (purchase.getRefundOnlineFee() != null && purchase.getRefundOnlineFee() != 0) {
                        refundWay += ";线上退回";
                    }
                    if (purchase.getRefundOfflineFee() != null && purchase.getRefundOfflineFee() != 0) {
                        refundWay += ";线下转账";
                    }
                }
                purchase.setReturnedWay(refundWay);
                String payType = OrderUtil.getPayType(purchase.getPayType());
                purchase.setPayType(payType);
            }

            //	收款进度  1待收  2部分收  3全收
            cmDiscernReceipt.setReceiptProgress("2");
            List<CmReceiptExportData> ListExportData1 = getListExportData(cmDiscernReceipt);
            cmDiscernReceipt.setReceiptProgress("3");
            List<CmReceiptExportData> ListExportData2 = getListExportData(cmDiscernReceipt);

            objects.add(list);
            /*objects.add(ListExportData1);
            objects.add(ListExportData2);
            objects.add(getListOrderPayment(cmDiscernReceipt));
            objects.add(purchases);*/
            listSheetNames.add("收款表");
            /*listSheetNames.add("部分收款");
            listSheetNames.add("已收款");
            listSheetNames.add("返佣订单");
            listSheetNames.add("退款记录表");*/

            new ExcelUtil().createMultipleSheetsExcel(templateFileName, fileName, listSheetNames, objects, response);
        } catch (Exception e) {
            addMessage(redirectAttributes, "收款列表导出失败！" + e.getMessage());
            logger.info(e.getMessage());
            e.printStackTrace();
        }
    }

    @RequestMapping(value = "exportTest1")
    public void exportTest1(String startTime, String endTime, CmDiscernReceipt cmDiscernReceipt, RedirectAttributes redirectAttributes, HttpServletRequest request, HttpServletResponse response) {
        //获取订单数据
        String fileName = "付款账单.xls";
        String template = Global.getConfig("export.template");
        String templateFileName = template + "/discern_receipt_export1.xls";
        List<String> listSheetNames = new ArrayList<String>();
        ArrayList<List> objects = new ArrayList<List>();
        if (startTime == null) {
            cmDiscernReceipt.setStartTime("2019-10-01 00:00:00");
        } else {
            cmDiscernReceipt.setStartTime(startTime);
        }
        if (endTime == null) {
            cmDiscernReceipt.setEndTime("2019-10-31 23:59:59");
        } else {
            cmDiscernReceipt.setEndTime(endTime);
        }
        try {

            List<CmMainReceiptExport> list = cmDiscernReceiptService.exportPay(cmDiscernReceipt);
            for (CmMainReceiptExport bean : list) {
                bean.setPayType(OrderUtil.getPayType(bean.getPayType()));
            }
            cmDiscernReceipt.setReceiptProgress("2");
            List<CmReceiptExportData> ListExportData1 = getListExportPayData(cmDiscernReceipt);
            cmDiscernReceipt.setReceiptProgress("3");
            List<CmReceiptExportData> ListExportData2 = getListExportPayData(cmDiscernReceipt);
            objects.add(list);
            objects.add(ListExportData1);
            objects.add(ListExportData2);
            listSheetNames.add("付款表");
            listSheetNames.add("部分付款");
            listSheetNames.add("已付款");

            new ExcelUtil().createMultipleSheetsExcel(templateFileName, fileName, listSheetNames, objects, response);
        } catch (Exception e) {
            addMessage(redirectAttributes, "付款列表导出失败！" + e.getMessage());
            logger.info(e.getMessage());
            e.printStackTrace();
        }
    }

    @RequestMapping(value = "exportTest2")
    public void exportTest2(String startTime, String endTime, CmDiscernReceipt cmDiscernReceipt, RedirectAttributes redirectAttributes, HttpServletResponse response) {
        //获取订单数据
        String fileName = "订单列表账单.xls";
        String template = Global.getConfig("export.template");
        String templateFileName = template + "/discern_receipt_export2.xls";
        List<String> listSheetNames = new ArrayList<String>();
        ArrayList<List> objects = new ArrayList<List>();
        if (startTime == null) {
            cmDiscernReceipt.setStartTime("2019-10-01 00:00:00");
        } else {
            cmDiscernReceipt.setStartTime(startTime);
        }
        if (endTime == null) {
            cmDiscernReceipt.setEndTime("2019-10-31 23:59:59");
        } else {
            cmDiscernReceipt.setEndTime(endTime);
        }
        try {
            List<CmReceiptExportData> listOrderDate = getListOrderDate(cmDiscernReceipt);
            objects.add(listOrderDate);
            objects.add(getListOrder(cmDiscernReceipt));
            listSheetNames.add("普通订单");
            listSheetNames.add("返佣订单");
            new ExcelUtil().createMultipleSheetsExcel(templateFileName, fileName, listSheetNames, objects, response);
        } catch (Exception e) {
            addMessage(redirectAttributes, "付款列表导出失败！" + e.getMessage());
            logger.info(e.getMessage());
            e.printStackTrace();
        }
    }

    @RequestMapping(value = "exportTest3")
    public void exportTest3(String startTime, String endTime, CmDiscernReceipt cmDiscernReceipt, RedirectAttributes redirectAttributes, HttpServletResponse response) {
        if (startTime == null) {
            cmDiscernReceipt.setStartTime("2019-10-01 00:00:00");
        } else {
            cmDiscernReceipt.setStartTime(startTime);
        }
        if (endTime == null) {
            cmDiscernReceipt.setEndTime("2019-10-31 23:59:59");
        } else {
            cmDiscernReceipt.setEndTime(endTime);
        }
        try {
            List<CmReceiptExportData> ListExportData = getListExportData2(cmDiscernReceipt);
            //非订单款
            List<CmMainReceiptExport> list = cmDiscernReceiptService.theOrder(cmDiscernReceipt);
            if (list != null && list.size() > 0) {
                CmReceiptExportData receiptExportData = new CmReceiptExportData();
                List<CmReceiptExport> exportDataList = new ArrayList<>();
                for (CmMainReceiptExport export : list) {
                    CmReceiptExport receiptExport = new CmReceiptExport();
                    receiptExport.setOrderID("非订单");
                    receiptExport.setReceiptDate(export.getReceiptDate());
                    receiptExport.setReceiptedRecord(OrderUtil.getPayTypeStr(export.getPayType()));
                    receiptExport.setReceiptedAmount(export.getAssociateAmount().toString());
                    receiptExport.setReceiptedAmountRemark("非订单款");
                    receiptExport.setCommission(export.getAssociateAmount());
                    exportDataList.add(receiptExport);
                }
                receiptExportData.setList(exportDataList);
                ListExportData.add(receiptExportData);
            }
            excel(response, ListExportData);
        } catch (Exception e) {
            addMessage(redirectAttributes, "收款账单导出失败！" + e.getMessage());
            logger.info(e.getMessage());
            e.printStackTrace();
        }
    }

    /**
     * 返佣订单记录,按收款时间
     *
     * @param cmDiscernReceipt
     * @return
     */
    private List<CmReceiptExportData> getListOrderPayment(CmDiscernReceipt cmDiscernReceipt) {
        List<CmReceiptExportData> data = new ArrayList<CmReceiptExportData>();
        List<CmMainReceiptExport> listReceipted = cmDiscernReceiptService.exportPayment(cmDiscernReceipt);
        data = format(data, listReceipted);
        return data;
    }

    /**
     * 返佣订单记录,按订单时间
     *
     * @param cmDiscernReceipt
     * @return
     */
    private List<CmReceiptExportData> getListOrder(CmDiscernReceipt cmDiscernReceipt) {
        List<CmReceiptExportData> data = new ArrayList<CmReceiptExportData>();
        List<CmMainReceiptExport> listReceipted = cmDiscernReceiptService.getListOrder(cmDiscernReceipt);
        data = format(data, listReceipted);
        return data;
    }

    /**
     * 获取普通订单记录
     *
     * @param cmDiscernReceipt
     * @return
     */
    private List<CmReceiptExportData> getListOrderDate(CmDiscernReceipt cmDiscernReceipt) {
        List<CmReceiptExportData> data = new ArrayList<CmReceiptExportData>();
        List<CmMainReceiptExport> listReceipted = cmDiscernReceiptService.exportOrderDate(cmDiscernReceipt);
        data = format(data, listReceipted);
        return data;
    }

    /**
     * @Author ye.qin
     * @Description //TODO 获取付款记录
     * @Date 2019\8\8 0008 11:26
     * @Param
     */
    private List<CmReceiptExportData> getListExportPayData(CmDiscernReceipt cmDiscernReceipt) {
        List<CmReceiptExportData> data = new ArrayList<CmReceiptExportData>();
        List<CmMainReceiptExport> listReceipted = cmDiscernReceiptService.exportPayData(cmDiscernReceipt);
        data = formatData(data, listReceipted);
        return data;
    }

    /**
     * @Author ye.qin
     * @Description //TODO 获取收款记录
     * @Date 2019\8\8 0008 11:26
     * @Param
     */
    private List<CmReceiptExportData> getListExportData(CmDiscernReceipt cmDiscernReceipt) {
        List<CmReceiptExportData> data = new ArrayList<CmReceiptExportData>();
        List<CmMainReceiptExport> listReceipted = cmDiscernReceiptService.exportReceiptPay(cmDiscernReceipt);
        if (cmDiscernReceipt.getReceiptProgress().equals("2")) {
            //只使用余额支付的记录
            List<CmMainReceiptExport> list1 = cmDiscernReceiptService.exportOnlyBalancePay(cmDiscernReceipt);
            if (list1 != null) listReceipted.addAll(list1);
        }
        data = formatData(data, listReceipted);

        return data;
    }

    /**
     * 获取收款记录2
     *
     * @param cmDiscernReceipt
     * @return
     */
    private List<CmReceiptExportData> getListExportData2(CmDiscernReceipt cmDiscernReceipt) {
        List<CmReceiptExportData> data = new ArrayList<CmReceiptExportData>();
        List<CmMainReceiptExport> listReceipted = cmDiscernReceiptService.exportReceiptPay(cmDiscernReceipt);
        cmDiscernReceipt.setReceiptProgress("2");
        //只使用余额支付的记录
        List<CmMainReceiptExport> list1 = cmDiscernReceiptService.exportOnlyBalancePay(cmDiscernReceipt);
        //返佣记录
        List<CmMainReceiptExport> list2 = cmDiscernReceiptService.exportPayment(cmDiscernReceipt);
        if (list1 != null) listReceipted.addAll(list1);
        if (list2 != null) listReceipted.addAll(list2);
        data = format2(data, listReceipted);
        return data;
    }

    private List<CmReceiptExportData> formatData(List<CmReceiptExportData> data, List<CmMainReceiptExport> listReceipted) {
        for (CmMainReceiptExport bean : listReceipted) {
            CmReceiptExportData currentData = new CmReceiptExportData();
            NewOrder order = newOrderService.get(bean.getOrderID());
            List<CmReceiptExport> listShopOrder = cmDiscernReceiptService.findShopOrder(bean.getOrderID());
            Double payProductFeeTotal = 0d;//用于第三行商品金额汇总统计（总）
            Double payTaxFeeTotal = 0d;//应付总税费（总）
            Double payFreightTotal = 0d;//应付总运费（总）
            Double payOtherTotal = 0d;//应付第三方（总）
            Double payAmountTotal = 0d;//应付总金额（总）
            Double payedAmountTotal = 0d;//已付总金额（总）
            Double commissionTotal = 0d;//采美佣金(总)

            String userName = "";//用户名称
            StringBuilder shopInfos = new StringBuilder();//供应商名称(汇总并添加应付金额)
            for (CmReceiptExport bean1 : listShopOrder) {
                userName = bean1.getUserName();
                //汇总商品供应商名称以及应付信息
                String supplierInfo = bean1.getSupplierName() + "(" + bean1.getPayAmount() + ")";
                shopInfos.append(supplierInfo);
                shopInfos.append("\r\n");

                List<NewOrderProduct> listProduct = newOrderProductService.findListByShopOrderID(Integer.parseInt(bean1.getShopOrderID()));
                Double payProductFee = 0d;
                Double productFee = 0d;
                StringBuilder productMsg = new StringBuilder();
                Integer flag = 1;
                for (NewOrderProduct bean2 : listProduct) {
                    if (999 != bean2.getProductID()) {
                        payProductFee += bean2.getNewCostPrice() * (bean2.getNum() + bean2.getPresentNum());
                        productMsg.append(flag + "." + bean2.getName() + " 数量(赠品)：" + bean2.getNum() + "(" + bean2.getPresentNum() + ")" + ",售价：" + bean2.getDiscountPrice() + ",成本：" + bean2.getNewCostPrice() + ",应收税费：" + bean2.getAddedValueTax() + ",应付税费：" + bean2.getShouldPayTotalTax() / bean2.getNum() + ";");
                        flag++;
                        productFee += bean2.getDiscountPrice() * bean2.getNum();
                    }
                }
                bean1.setProductMsg(productMsg.toString());
                bean1.setPayProductFee(payProductFee);    //设置应付商品费
                payProductFeeTotal = payProductFeeTotal + payProductFee;//设置应付总商品费
                payTaxFeeTotal = payTaxFeeTotal + bean1.getPayTaxFee();
                payFreightTotal = payFreightTotal + bean1.getPayFreight();
                payOtherTotal = payOtherTotal + bean1.getPayOther();

                bean1.setGroup("订单");
                bean1.setProductFee(productFee);
                if (bean1.getShopID().equals("998")) {
                    bean1.setCommission(0d);
                    bean1.setPayedAmount(0d);
                    if (bean1.getPayStatus().equals("3")) {
                        bean1.setPayedAmount(bean1.getPayAmount());
                    }
                    bean1.setReceiptedAmount("0");
                    bean1.setPayProductFee(bean1.getProductFee());
                    bean1.setPayAmount(bean1.getPayProductFee());
                } else {
                    bean1.setCommission(bean1.getReceiptAmount() - bean1.getPayOther() - bean1.getPayAmount());
                    bean1.setReceiptedAmount("0");
                    HashMap data1 = new HashMap();
                    Double payedAmount = 0d;//统计子订单所有付款金额
                    //获取子订单一下的，按照每个月每个账号的一个收款金额显示
                    List<CmPayShopRecord> listPayRecord = newShopOrderService.getPayedRecordsGroupBy(bean1.getShopOrderID());
                    StringBuilder payRecord = new StringBuilder();
                    for (CmPayShopRecord bean3 : listPayRecord) {
                        payRecord.append(bean3.getPayTime() + "付:" + OrderUtil.getPayType(bean3.getPayType()) + "(￥" + bean3.getPayAmount() + ")");
                        payRecord.append("\r\n");
                        payedAmount += bean3.getPayAmount();
                    }
                    bean1.setPayRecord(payRecord.toString());
                    bean1.setPayedAmount(payedAmount);
                }
                bean1.setArrearage(bean1.getPayAmount() - bean1.getPayedAmount());//增加未付款
                DecimalFormat df = new DecimalFormat("#.00");//保留两位小数
                bean1.setCommission(Double.valueOf(df.format(bean1.getCommission())));
                payAmountTotal = payAmountTotal + bean1.getPayAmount();
                payedAmountTotal = payedAmountTotal + bean1.getPayedAmount();
                commissionTotal = commissionTotal + bean1.getCommission();
            }
            //判断是否存在经理折扣，存在就给集合手动添加数据
            if (order.getDiscountFee() != null && order.getDiscountFee() > 0) {
                CmReceiptExport discountBean = new CmReceiptExport();
                discountBean.setOrderID(order.getOrderID().toString());
                discountBean.setGroup("经理折扣");
                discountBean.setProductMsg("经理折扣：" + order.getDiscountFee());
                discountBean.setReceiptAmount(-order.getDiscountFee());
                discountBean.setReceiptedAmount("-" + order.getDiscountFee().toString());
                discountBean.setCommission(-order.getDiscountFee());
                listShopOrder.add(discountBean);
            }

            /**汇总数据--start*/
            //集合手动添加汇总数据

            //2、汇总信息的详细收款等
            String recepitRecord = "";
            List<CmReceiptOrderRelation> listRelationGroupBy = cmReceiptOrderRelationService.findByOrderIDGroupBy(order.getOrderID().toString());
            CmReceiptExport totalBean = new CmReceiptExport();
            //1、待合并的汇总信息
            totalBean.setGroup("汇总");
            //判断是否是返佣订单
            List<Integer> l = new ArrayList<>();
            l.add(order.getOrderID());
            List<Integer> orderIds = cmDiscernReceiptDao.findRebateOrderID(l);
            if (null != orderIds && orderIds.contains(order.getOrderID()))
                totalBean.setOrderID(order.getOrderID().toString() + "(返佣订单)");
            else
                totalBean.setOrderID(order.getOrderID().toString());

            //totalBean.setProductMsg("汇总");
            //1.1应收款
            totalBean.setReceiptAmount(order.getPayTotalFee() + order.getDiscountFee());

            List<CmReceiptOrderRelation> listRelation = cmReceiptOrderRelationService.findByOrderID(order.getOrderID().toString());
            Double totalAmount = 0d;
            String confirmType = "";
            Boolean isFanyong = false;
            for (CmReceiptOrderRelation bean3 : listRelation) {
                bean3.setReceiptDate(DateUtils.format(DateUtils.parse(bean3.getReceiptDate()), "yyyy-MM"));
                totalAmount += Double.parseDouble(bean3.getAssociateAmount());
                if (bean3.getConfirmType().equals("1")) {
                    confirmType = "小额抹平确认";
                } else if (bean3.getConfirmType().equals("2")) {
                    confirmType = "大额抹平确认";
                } else if (bean3.getConfirmType().equals("3")) {
                    confirmType = "大额退款余额";
                }
                if (bean3.getRelationType().equals("1")) {
                    isFanyong = true;
                }
            }
            StringBuilder msg = new StringBuilder();
            if (confirmType != "") {
                msg.append("(" + confirmType + ")");
            }
            if (order.getBalancePayFee() != null && order.getBalancePayFee() > 0) {
                msg.append("（余额抵扣：￥" + order.getBalancePayFee() + ")");
            }
            //1.2已收款
            totalBean.setReceiptedAmount(totalAmount + msg.toString());
            //1.3客户名称
            totalBean.setUserName(userName);
            //1.4供应商
            totalBean.setSupplierName(shopInfos.toString());
            //1.5应付商品费
            totalBean.setPayProductFee(payProductFeeTotal);
            //1.6应付税费
            totalBean.setPayTaxFee(payTaxFeeTotal);
            //1.7应付运费
            totalBean.setPayFreight(payFreightTotal);
            //1.8应付第三方
            totalBean.setPayOther(payOtherTotal);
            //1.9应付款
            totalBean.setPayAmount(payAmountTotal);
            //1.10已付款
            totalBean.setPayedAmount(payedAmountTotal);
            //1.11采美佣金
            totalBean.setCommission(commissionTotal);

            totalBean.setArrearage(payAmountTotal - payedAmountTotal);//增加未付款

            //查询主订单下面的所有子订单ID串
            NewOrder newOrder = newOrderDao.get(order.getOrderID().toString());
            String shopOrderIDs = newOrder.getShopOrderIDs();
            String[] split = null;
            StringBuilder payRecord = new StringBuilder();
            if (null != shopOrderIDs && shopOrderIDs.contains(",")) {
                split = shopOrderIDs.split(",");
            } else {
                split = new String[1];
                split[0] = shopOrderIDs;
            }

            List<CmPayShopRecord> byOrderIDsGroupBy = cmPayShopRecordService.findByOrderIDsGroupBy(split);
            for (CmPayShopRecord cps : byOrderIDsGroupBy) {
                payRecord.append(OrderUtil.getPayType(cps.getPayType()) + "(￥" + cps.getPayAmount() + ")");
                payRecord.append("\r\n");
            }
            //1.12付款账号
            totalBean.setPayRecord(payRecord.toString());
            for (CmReceiptOrderRelation cror : listRelationGroupBy) {
                recepitRecord = cror.getReceiptDate() + "收:" + cror.getPayType() + "(￥" + cror.getAssociateAmount() + ")";
            }
            //2.1设置收款账号
            totalBean.setReceiptedRecord(recepitRecord);
            recepitRecord = "";
            listShopOrder.add(totalBean);

            //if(isFanyong)totalBean.setSupplierName("返佣订单");
            /**汇总数据--end*/
            currentData.setList(listShopOrder);
            data.add(currentData);
        }
        return data;
    }

    private List<CmReceiptExportData> format(List<CmReceiptExportData> data, List<CmMainReceiptExport> listReceipted) {
        for (CmMainReceiptExport bean : listReceipted) {
            CmReceiptExportData currentData = new CmReceiptExportData();
            NewOrder order = newOrderService.get(bean.getOrderID());
            List<CmReceiptExport> listShopOrder = cmDiscernReceiptService.findShopOrder(bean.getOrderID());
            Double payProductFeeTotal = 0d;//用于第三行商品金额汇总统计（总）
            Double payTaxFeeTotal = 0d;//应付总税费（总）
            Double payFreightTotal = 0d;//应付总运费（总）
            Double payOtherTotal = 0d;//应付第三方（总）
            Double payAmountTotal = 0d;//应付总金额（总）
            Double payedAmountTotal = 0d;//已付总金额（总）
            Double commissionTotal = 0d;//采美佣金(总)

            String userName = "";//用户名称
            StringBuilder shopInfos = new StringBuilder();//供应商名称(汇总并添加应付金额)
            for (CmReceiptExport bean1 : listShopOrder) {
                userName = bean1.getUserName();
                //汇总商品供应商名称以及应付信息
                String supplierInfo = bean1.getSupplierName() + "(" + bean1.getPayAmount() + ")";
                shopInfos.append(supplierInfo);
                shopInfos.append("\r\n");

                List<NewOrderProduct> listProduct = newOrderProductService.findListByShopOrderID(Integer.parseInt(bean1.getShopOrderID()));
                Double payProductFee = 0d;
                Double productFee = 0d;
                StringBuilder productMsg = new StringBuilder();
                Integer flag = 1;
                for (NewOrderProduct bean2 : listProduct) {
                    if (999 != bean2.getProductID()) {
                        payProductFee += bean2.getNewCostPrice() * (bean2.getNum() + bean2.getPresentNum());
                        productMsg.append(flag + "." + bean2.getName() + " 数量(赠品)：" + bean2.getNum() + "(" + bean2.getPresentNum() + ")" + ",售价：" + bean2.getDiscountPrice() + ",成本：" + bean2.getNewCostPrice() + ",应收税费：" + bean2.getAddedValueTax() + ",应付税费：" + bean2.getShouldPayTotalTax() / bean2.getNum() + ";");
                        flag++;
                        productFee += bean2.getDiscountPrice() * bean2.getNum();
                    }
                }
                bean1.setProductMsg(productMsg.toString());
                bean1.setPayProductFee(payProductFee);    //设置应付商品费
                payProductFeeTotal = payProductFeeTotal + payProductFee;//设置应付总商品费
                payTaxFeeTotal = payTaxFeeTotal + bean1.getPayTaxFee();
                payFreightTotal = payFreightTotal + bean1.getPayFreight();
                payOtherTotal = payOtherTotal + bean1.getPayOther();

                bean1.setGroup("订单");
                bean1.setProductFee(productFee);
                if (bean1.getShopID().equals("998")) {
                    bean1.setCommission(0d);
                    bean1.setPayedAmount(0d);
                    if (bean1.getPayStatus().equals("3")) {
                        bean1.setPayedAmount(bean1.getPayAmount());
                    }
                    bean1.setReceiptedAmount("0");
                    bean1.setPayProductFee(bean1.getProductFee());
                    bean1.setPayAmount(bean1.getPayProductFee());
                } else {
                    bean1.setCommission(bean1.getReceiptAmount() - bean1.getPayOther() - bean1.getPayAmount());
                    bean1.setReceiptedAmount("0");
                    HashMap data1 = new HashMap();
                    Double payedAmount = 0d;//统计子订单所有付款金额
                    //获取子订单一下的，按照每个月每个账号的一个收款金额显示
                    List<CmPayShopRecord> listPayRecord = newShopOrderService.getPayedRecordsGroupBy(bean1.getShopOrderID());
                    StringBuilder payRecord = new StringBuilder();
                    for (CmPayShopRecord bean3 : listPayRecord) {
                        payRecord.append(bean3.getPayTime() + "付:" + OrderUtil.getPayType(bean3.getPayType()) + "(￥" + bean3.getPayAmount() + ")");
                        payRecord.append("\r\n");
                        payedAmount += bean3.getPayAmount();
                    }
                    bean1.setPayRecord(payRecord.toString());
                    bean1.setPayedAmount(payedAmount);
                }
                bean1.setArrearage(bean1.getPayAmount() - bean1.getPayedAmount());//增加未付款
                DecimalFormat df = new DecimalFormat("#.00");//保留两位小数
                bean1.setCommission(Double.valueOf(df.format(bean1.getCommission())));
                if (bean.getRelationType() != null) {//判断是否是返佣订单
                    bean1.setPayAmount(bean1.getPayProductFee() + bean1.getPayTaxFee() + bean1.getPayFreight() + bean1.getPayOther());
                    bean1.setPayedAmount(bean1.getPayAmount());//已付款
                    bean1.setCommission(Double.valueOf(bean.getReceiptAmount()));//采美佣金
                    bean1.setArrearage(bean1.getPayedAmount() - bean1.getPayAmount());//未付款
                }
                payAmountTotal = payAmountTotal + bean1.getPayAmount();
                payedAmountTotal = payedAmountTotal + bean1.getPayedAmount();
                commissionTotal = commissionTotal + bean1.getCommission();
            }
            //判断是否存在经理折扣，存在就给集合手动添加数据
            if (order.getDiscountFee() != null && order.getDiscountFee() > 0) {
                CmReceiptExport discountBean = new CmReceiptExport();
                discountBean.setOrderID(order.getOrderID().toString());
                discountBean.setGroup("经理折扣");
                discountBean.setProductMsg("经理折扣：" + order.getDiscountFee());
                discountBean.setReceiptAmount(-order.getDiscountFee());
                discountBean.setReceiptedAmount("-" + order.getDiscountFee().toString());
                discountBean.setCommission(-order.getDiscountFee());
                listShopOrder.add(discountBean);
            }

            /**汇总数据--start*/
            //集合手动添加汇总数据

            //2、汇总信息的详细收款等
            String recepitRecord = "";
            CmReceiptExport totalBean = new CmReceiptExport();
            //1、待合并的汇总信息
            totalBean.setGroup("汇总");
            //判断是否是返佣订单
            List<Integer> l = new ArrayList<>();
            l.add(order.getOrderID());
            List<Integer> orderIds = cmDiscernReceiptDao.findRebateOrderID(l);
            if (null != orderIds && orderIds.contains(order.getOrderID()))
                totalBean.setOrderID(order.getOrderID().toString() + "(返佣订单)");
            else
                totalBean.setOrderID(order.getOrderID().toString());

            //totalBean.setProductMsg("汇总");
            //1.1应收款
            totalBean.setReceiptAmount(order.getPayTotalFee() + order.getDiscountFee());

            List<CmReceiptOrderRelation> listRelation = cmReceiptOrderRelationService.findByOrderID(order.getOrderID().toString());
            Double totalAmount = 0d;
            String confirmType = "";
            Boolean isFanyong = false;
            for (CmReceiptOrderRelation bean3 : listRelation) {
                bean3.setReceiptDate(DateUtils.format(DateUtils.parse(bean3.getReceiptDate()), "yyyy-MM"));
                totalAmount += Double.parseDouble(bean3.getAssociateAmount());
                if (bean3.getConfirmType().equals("1")) {
                    confirmType = "小额抹平确认";
                } else if (bean3.getConfirmType().equals("2")) {
                    confirmType = "大额抹平确认";
                } else if (bean3.getConfirmType().equals("3")) {
                    confirmType = "大额退款余额";
                }
                if (bean3.getRelationType().equals("1")) {
                    isFanyong = true;
                }
            }
            StringBuilder msg = new StringBuilder();
            if (confirmType != "") {
                msg.append("(" + confirmType + ")");
            }
            if (order.getBalancePayFee() != null && order.getBalancePayFee() > 0) {
                msg.append("（余额抵扣：￥" + order.getBalancePayFee() + ")");
            }
            //1.2已收款
            totalBean.setReceiptedAmount(totalAmount + msg.toString());
            //1.3客户名称
            totalBean.setUserName(userName);
            //1.4供应商
            totalBean.setSupplierName(shopInfos.toString());
            //1.5应付商品费
            totalBean.setPayProductFee(payProductFeeTotal);
            //1.6应付税费
            totalBean.setPayTaxFee(payTaxFeeTotal);
            //1.7应付运费
            totalBean.setPayFreight(payFreightTotal);
            //1.8应付第三方
            totalBean.setPayOther(payOtherTotal);
            //1.9应付款
            totalBean.setPayAmount(payAmountTotal);
            //1.10已付款
            totalBean.setPayedAmount(payedAmountTotal);
            //1.11采美佣金
            totalBean.setCommission(commissionTotal);

            totalBean.setStatus(OrderUtil.getStatus(bean.getStatus()));

            totalBean.setArrearage(payAmountTotal - payedAmountTotal);//增加未付款

            if (bean.getRelationType() != null) {//判断是否是返佣订单
                totalBean.setReceiptedAmount(bean.getReceiptAmount() + "(返佣款)");
                totalBean.setPayedAmount(totalBean.getPayAmount());//已付款
                totalBean.setCommission(Double.valueOf(bean.getReceiptAmount()));//采美佣金
                totalBean.setArrearage(payAmountTotal - totalBean.getPayAmount());//未付款
            }

            //查询主订单下面的所有子订单ID串
            NewOrder newOrder = newOrderDao.get(order.getOrderID().toString());
            String shopOrderIDs = newOrder.getShopOrderIDs();
            String[] split = null;
            StringBuilder payRecord = new StringBuilder();
            if (null != shopOrderIDs && shopOrderIDs.contains(",")) {
                split = shopOrderIDs.split(",");
            } else {
                split = new String[1];
                split[0] = shopOrderIDs;
            }

            List<CmPayShopRecord> byOrderIDsGroupBy = cmPayShopRecordService.findByOrderIDsGroupBy(split);
            for (CmPayShopRecord cps : byOrderIDsGroupBy) {
                payRecord.append(OrderUtil.getPayType(cps.getPayType()) + "(￥" + cps.getPayAmount() + ")");
                payRecord.append("\r\n");
            }
            //1.12付款账号
            totalBean.setPayRecord(payRecord.toString());

            List<CmReceiptOrderRelation> listRelationGroupBy = cmReceiptOrderRelationService.findByOrderIDGroupBy(order.getOrderID().toString());
            for (CmReceiptOrderRelation cror : listRelationGroupBy) {
                recepitRecord = cror.getReceiptDate() + "收:" + cror.getPayType() + "(￥" + cror.getAssociateAmount() + ")";
            }
            //2.1设置收款账号
            totalBean.setReceiptedRecord(recepitRecord);

            if (bean.getRelationType() != null) {//判断是否是返佣订单
                List<CmReceiptOrderRelation> listRelationGroup = cmReceiptOrderRelationService.findByOrderIDGroup(order.getOrderID().toString());
                for (CmReceiptOrderRelation relation : listRelationGroup) {
                    recepitRecord = relation.getReceiptDate() + "收:" + relation.getPayType() + "(￥" + relation.getAssociateAmount() + ")";
                }
                //2.1设置收款账号
                totalBean.setReceiptedRecord(recepitRecord);
            }
            recepitRecord = "";
            listShopOrder.add(totalBean);

            //if(isFanyong)totalBean.setSupplierName("返佣订单");
            /**汇总数据--end*/
            currentData.setList(listShopOrder);
            data.add(currentData);
        }
        return data;
    }

    private List<CmReceiptExportData> format2(List<CmReceiptExportData> data, List<CmMainReceiptExport> listReceipted) {
        for (CmMainReceiptExport bean : listReceipted) {
            CmReceiptExportData currentData = new CmReceiptExportData();
            NewOrder order = newOrderService.get(bean.getOrderID());
            List<CmReceiptExport> listShopOrderDate = cmDiscernReceiptService.findShopOrder(bean.getOrderID());
            List<CmReceiptExport> listShopOrder = new ArrayList<>();
            Double payProductFeeTotal = 0d;//用于第三行商品金额汇总统计（总）
            Double payTaxFeeTotal = 0d;//应付总税费（总）
            Double payFreightTotal = 0d;//应付总运费（总）
            Double payOtherTotal = 0d;//应付第三方（总）
            Double payAmountTotal = 0d;//应付总金额（总）
            Double payedAmountTotal = 0d;//已付总金额（总）
            Double commissionTotal = 0d;//采美佣金(总)
            Double productFeeTotal = 0d;//应收商品费(总)
            Double taxFeeTotal = 0d;//应收税费(总)
            Double freightTotal = 0d;//应收运费(总)
            String payTime = ""; //付款时间

            String userName = "";//用户名称
            StringBuilder shopInfos = new StringBuilder();//供应商名称(汇总并添加应付金额)
            for (CmReceiptExport bean1 : listShopOrderDate) {
                userName = bean1.getUserName();
                //汇总商品供应商名称以及应付信息
                String supplierInfo = bean1.getSupplierName() + "";
                shopInfos.append(supplierInfo);
                shopInfos.append("\r\n");

                List<NewOrderProduct> listProduct = newOrderProductService.findListByShopOrderID(Integer.parseInt(bean1.getShopOrderID()));
                Double payProductFee = 0d;
                Double productFee = 0d;
                StringBuilder productMsg = new StringBuilder();
                Integer flag = 1;
                for (NewOrderProduct bean2 : listProduct) {
                    if (999 != bean2.getProductID()) {
                        payProductFee += bean2.getNewCostPrice() * (bean2.getNum() + bean2.getPresentNum());
                        productMsg.append(flag + "." + bean2.getName() + " 数量(赠品)：" + bean2.getNum() + "(" + bean2.getPresentNum() + ")" + ",售价：" + bean2.getDiscountPrice() + ",成本：" + bean2.getNewCostPrice() + ",应收税费：" + bean2.getAddedValueTax() + ",应付税费：" + bean2.getShouldPayTotalTax() / bean2.getNum() + ";");
                        flag++;
                        productFee += bean2.getDiscountPrice() * bean2.getNum();
                    }
                }
                bean1.setProductMsg(productMsg.toString());
                bean1.setPayProductFee(payProductFee);    //设置应付商品费
                payProductFeeTotal = payProductFeeTotal + payProductFee;//设置应付总商品费
                payTaxFeeTotal = payTaxFeeTotal + bean1.getPayTaxFee();
                payFreightTotal = payFreightTotal + bean1.getPayFreight();
                payOtherTotal = payOtherTotal + bean1.getPayOther();

                bean1.setGroup("订单");
                bean1.setProductFee(productFee);
                if (bean1.getShopID().equals("998")) {
                    bean1.setCommission(0d);
                    bean1.setPayedAmount(0d);
                    if (bean1.getPayStatus().equals("3")) {
                        bean1.setPayedAmount(bean1.getPayAmount());
                    }
                    bean1.setReceiptedAmount("0");
                    bean1.setPayProductFee(bean1.getProductFee());
                    bean1.setPayAmount(bean1.getPayProductFee());
                    freightTotal += bean1.getProductAmount();
                } else {
                    bean1.setCommission(bean1.getReceiptAmount() - bean1.getPayOther() - bean1.getPayAmount());
                    bean1.setReceiptedAmount("0");
                    HashMap data1 = new HashMap();
                    Double payedAmount = 0d;//统计子订单所有付款金额
                    //获取子订单一下的，按照每个月每个账号的一个收款金额显示
                    List<CmPayShopRecord> listPayRecord = newShopOrderService.getPayedRecordsGroupBy(bean1.getShopOrderID());
                    StringBuilder payRecord = new StringBuilder();
                    for (CmPayShopRecord bean3 : listPayRecord) {
                        payRecord.append(bean3.getPayTime() + "付:" + OrderUtil.getPayType(bean3.getPayType()) + "(￥" + bean3.getPayAmount() + ")");
                        payRecord.append("\r\n");
                        payedAmount += bean3.getPayAmount();
                        payTime = bean3.getPayTime();
                    }
                    bean1.setPayRecord(payRecord.toString());
                    bean1.setPayedAmount(payedAmount);
                }
                bean1.setArrearage(bean1.getPayAmount() - bean1.getPayedAmount());//增加未付款
                DecimalFormat df = new DecimalFormat("#.00");//保留两位小数
                bean1.setCommission(Double.valueOf(df.format(bean1.getCommission())));
                productFeeTotal += bean1.getProductFee();//计算总的应收商品费
                taxFeeTotal += bean1.getTaxFee();//计算总的应收税费
                payAmountTotal = payAmountTotal + bean1.getPayAmount();
                payedAmountTotal = payedAmountTotal + bean1.getPayedAmount();
                commissionTotal = commissionTotal + bean1.getCommission();
            }

            /**汇总数据--start*/
            //集合手动添加汇总数据

            List<CmMainReceiptExport> gathering = cmDiscernReceiptService.gathering(order.getOrderID());
            if (gathering == null || gathering.size() == 0) {
                CmMainReceiptExport cmMainReceiptExport = new CmMainReceiptExport();
                cmMainReceiptExport.setPayType("");
                cmMainReceiptExport.setReceiptDate("无");
                gathering.add(cmMainReceiptExport);
            }
            for (CmMainReceiptExport receiptExport : gathering) {
                //2、汇总信息的详细收款等
                String recepitRecord = "";
                List<CmReceiptOrderRelation> listRelationGroupBy = cmReceiptOrderRelationService.findByOrderIDGroupBy(order.getOrderID().toString());
                CmReceiptExport totalBean = new CmReceiptExport();
                //1、待合并的汇总信息
                totalBean.setGroup("汇总");
                //判断是否是返佣订单
                List<Integer> l = new ArrayList<>();
                l.add(order.getOrderID());
                List<Integer> orderIds = cmDiscernReceiptDao.findRebateOrderID(l);
                if (null != orderIds && orderIds.contains(order.getOrderID()))
                    totalBean.setOrderID(order.getOrderID().toString() + "(返佣订单)");
                else
                    totalBean.setOrderID(order.getOrderID().toString());

                //1.1应收款
                totalBean.setReceiptAmount(order.getPayTotalFee() + order.getDiscountFee());

                List<CmReceiptOrderRelation> listRelation = cmReceiptOrderRelationService.findByOrderID(order.getOrderID().toString());
                Double totalAmount = 0d;
                String confirmType = "";
                Boolean isFanyong = false;
                for (CmReceiptOrderRelation bean3 : listRelation) {
                    bean3.setReceiptDate(DateUtils.format(DateUtils.parse(bean3.getReceiptDate()), "yyyy-MM"));
                    totalAmount += Double.parseDouble(bean3.getAssociateAmount());
                    if (bean3.getConfirmType().equals("1")) {
                        confirmType = "小额抹平确认";
                    } else if (bean3.getConfirmType().equals("2")) {
                        confirmType = "大额抹平确认";
                    } else if (bean3.getConfirmType().equals("3")) {
                        confirmType = "大额退款余额";
                    }
                    if (bean3.getRelationType().equals("1")) {
                        isFanyong = true;
                    }
                }
                StringBuilder msg = new StringBuilder();
                if (confirmType != "") {
                    msg.append("(" + confirmType + ")");
                }
                if (order.getBalancePayFee() != null && order.getBalancePayFee() > 0) {
                    msg.append("（余额抵扣：￥" + order.getBalancePayFee() + ")");
                }
                //1.2已收款
                totalBean.setReceiptedAmount(totalAmount.toString());
                //已收款备注
                if (null != orderIds && orderIds.contains(order.getOrderID())) {
                    totalBean.setReceiptedAmountRemark("返佣款");
                } else {
                    totalBean.setReceiptedAmountRemark(msg.toString());
                }
                //1.3客户名称
                totalBean.setUserName(userName);
                //1.4供应商
                totalBean.setSupplierName(shopInfos.toString());
                //1.5应付商品费
                totalBean.setPayProductFee(payProductFeeTotal);
                //1.6应付税费
                totalBean.setPayTaxFee(payTaxFeeTotal);
                //1.7应付运费
                totalBean.setPayFreight(payFreightTotal);
                //1.8应付第三方
                totalBean.setPayOther(payOtherTotal);
                //1.9应付款
                totalBean.setPayAmount(payAmountTotal);
                //1.10已付款
                totalBean.setPayedAmount(payedAmountTotal);
                //1.11采美佣金
                totalBean.setCommission(commissionTotal);

                totalBean.setArrearage(payAmountTotal - payedAmountTotal);//增加未付款

                totalBean.setProductFee(productFeeTotal);//应收商品费(总)

                totalBean.setTaxFee(taxFeeTotal);//应收税费(总)

                totalBean.setProductAmount(freightTotal);//应收运费(总)

                totalBean.setPayTime(payTime);//付款时间

                totalBean.setReceiptDate(receiptExport.getReceiptDate());//收款时间

                totalBean.setReceiptedRecord(OrderUtil.getPayTypeStr(receiptExport.getPayType()));//收款账号


                //查询主订单下面的所有子订单ID串
                NewOrder newOrder = newOrderDao.get(order.getOrderID().toString());
                String shopOrderIDs = newOrder.getShopOrderIDs();
                String[] split = null;
                StringBuilder payRecord = new StringBuilder();
                if (null != shopOrderIDs && shopOrderIDs.contains(",")) {
                    split = shopOrderIDs.split(",");
                } else {
                    split = new String[1];
                    split[0] = shopOrderIDs;
                }

                List<CmPayShopRecord> byOrderIDsGroupBy = cmPayShopRecordService.findByOrderIDsGroupBy(split);
                for (CmPayShopRecord cps : byOrderIDsGroupBy) {
                    payRecord.append(OrderUtil.getPayType(cps.getPayType()) + "(￥" + cps.getPayAmount() + ")");
                    payRecord.append("\r\n");
                }
                //1.12付款账号
                totalBean.setPayRecord(payRecord.toString());
                listShopOrder.add(totalBean);
            }

            /**汇总数据--end*/
            currentData.setList(listShopOrder);
            data.add(currentData);
        }
        return data;
    }

    public void excel(HttpServletResponse response, List<CmReceiptExportData> excelList) {
        //创建一个webbook，对应一个Excel文件
        HSSFWorkbook wb = new HSSFWorkbook();
        //在webbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = wb.createSheet("收款详情订单");

        //创建单元格，并设置值表头 设置表头居中
        HSSFCellStyle style = wb.createCellStyle();
        style.setVerticalAlignment(VerticalAlignment.CENTER);//垂直
        style.setAlignment(HorizontalAlignment.CENTER);//水平
        style.setFillForegroundColor(HSSFColor.ORANGE.index);

        HSSFCellStyle textStyle = wb.createCellStyle();
        //设置列宽（第几列，宽度）receivedTotal
        sheet.setColumnWidth(0, 1500);//序号
        sheet.setColumnWidth(1, 3200);//母订单id
        sheet.setColumnWidth(2, 4800);//收款时间
        sheet.setColumnWidth(3, 4800);//收款账号
        sheet.setColumnWidth(4, 5000);//客户
        sheet.setColumnWidth(5, 5000);//应收商品费
        sheet.setColumnWidth(6, 3200);//应收税费
        sheet.setColumnWidth(7, 3200);//应收运费
        sheet.setColumnWidth(8, 3200);//应收款
        sheet.setColumnWidth(9, 3200);//已收款
        sheet.setColumnWidth(10, 5000);//已收款备注
        sheet.setColumnWidth(11, 5000);//付款时间
        sheet.setColumnWidth(12, 3200);//付款账号
        sheet.setColumnWidth(13, 5000);//供应商
        sheet.setColumnWidth(14, 6000);//应付商品费
        sheet.setColumnWidth(15, 3200);//应付税费
        sheet.setColumnWidth(16, 3200);//应付运费
        sheet.setColumnWidth(17, 3200);//应付第三方
        sheet.setColumnWidth(18, 3200);//应付款
        sheet.setColumnWidth(19, 3200);//已付款
        sheet.setColumnWidth(20, 3200);//未付款
        sheet.setColumnWidth(21, 3200);//采美佣金
        //第一行(表头)
        String[] head = new String[]{"序号", "母订单id", "收款时间", "收款账号", "客户", "应收商品费", "应收税费", "应收运费", "应收款", "已收款", "已收款备注", "付款时间", "付款账号", "供应商", "应付商品费", "应付税费", "应付运费", "应付第三方", "应付款", "已付款", "未付款", "采美佣金"};//在excel中的第4行每列的参数
        HSSFCell cell;
        HSSFRow row = sheet.createRow(0);
        for (int i = 0; i < head.length; i++) {
            cell = row.createCell(i);
            row.setHeight((short) 0x270);
            cell.setCellValue(head[i]);
            cell.setCellStyle(style);
        }

        Integer currentUseRow = 1;//当前已使用行号(表头存在一行从1开始)
        Integer currentMergeStartRow = 1;//开始合并行号
        int count = 0;
        //循环遍历所有集合数据
        if (CollectionUtils.isNotEmpty(excelList)) {
            for (CmReceiptExportData exportData : excelList) {
                count++;
                int size = exportData.getList().size();
                if (CollectionUtils.isNotEmpty(exportData.getList())) {
                    List<CmReceiptExport> list = exportData.getList();
                    for (int i = 0; i < list.size(); i++) {
                        HSSFRow row2 = sheet.createRow(currentUseRow);
                        row2.setHeight((short) 0x270);

                        //创建序号列
                        cell = row2.createCell(0);
                        cell.setCellValue(count);
                        cell.setCellStyle(textStyle);
                        //母订单id
                        cell = row2.createCell(1);
                        cell.setCellValue(list.get(i).getOrderID());
                        cell.setCellStyle(textStyle);
                        //收款时间
                        cell = row2.createCell(2);
                        cell.setCellValue(list.get(i).getReceiptDate());
                        cell.setCellStyle(textStyle);
                        //收款账号
                        cell = row2.createCell(3);
                        cell.setCellValue(list.get(i).getReceiptedRecord());
                        cell.setCellStyle(textStyle);
                        //客户
                        cell = row2.createCell(4);
                        cell.setCellValue(list.get(i).getUserName());
                        cell.setCellStyle(textStyle);
                        //应收商品费
                        cell = row2.createCell(5);
                        cell.setCellValue(formatting(list.get(i).getProductFee()));
                        cell.setCellStyle(textStyle);
                        //应收税费
                        cell = row2.createCell(6);
                        cell.setCellValue(formatting(list.get(i).getTaxFee()));
                        cell.setCellStyle(textStyle);
                        //应收运费
                        cell = row2.createCell(7);
                        cell.setCellValue(formatting(list.get(i).getProductAmount()));
                        cell.setCellStyle(textStyle);
                        //应收款
                        cell = row2.createCell(8);
                        cell.setCellValue(formatting(list.get(i).getReceiptAmount()));
                        cell.setCellStyle(textStyle);
                        //已收款
                        cell = row2.createCell(9);
                        cell.setCellValue(formatting(Double.valueOf(list.get(i).getReceiptedAmount())));
                        cell.setCellStyle(textStyle);
                        //已收款备注
                        cell = row2.createCell(10);
                        cell.setCellValue(list.get(i).getReceiptedAmountRemark());
                        cell.setCellStyle(textStyle);
                        //付款时间
                        cell = row2.createCell(11);
                        cell.setCellValue(list.get(i).getPayTime());
                        cell.setCellStyle(textStyle);
                        //付款账号
                        cell = row2.createCell(12);
                        cell.setCellValue(list.get(i).getPayRecord());
                        cell.setCellStyle(textStyle);
                        //供应商
                        cell = row2.createCell(13);
                        cell.setCellValue(list.get(i).getSupplierName());
                        cell.setCellStyle(textStyle);
                        //应付商品费
                        cell = row2.createCell(14);
                        cell.setCellValue(formatting(list.get(i).getPayProductFee()));
                        cell.setCellStyle(textStyle);
                        //应付税费
                        cell = row2.createCell(15);
                        cell.setCellValue(formatting(list.get(i).getPayTaxFee()));
                        cell.setCellStyle(textStyle);
                        //应付运费
                        cell = row2.createCell(16);
                        cell.setCellValue(formatting(list.get(i).getPayFreight()));
                        cell.setCellStyle(textStyle);
                        //应付第三方
                        cell = row2.createCell(17);
                        cell.setCellValue(formatting(list.get(i).getPayOther()));
                        cell.setCellStyle(textStyle);
                        //应付款
                        cell = row2.createCell(18);
                        cell.setCellValue(formatting(list.get(i).getPayAmount()));
                        cell.setCellStyle(textStyle);
                        //已付款
                        cell = row2.createCell(19);
                        cell.setCellValue(formatting(list.get(i).getPayedAmount()));
                        cell.setCellStyle(textStyle);
                        //未付款
                        cell = row2.createCell(20);
                        cell.setCellValue(formatting(list.get(i).getArrearage()));
                        cell.setCellStyle(textStyle);
                        //采美佣金
                        cell = row2.createCell(21);
                        cell.setCellValue(formatting(list.get(i).getCommission()));
                        cell.setCellStyle(textStyle);

                        currentUseRow += 1;//供应商大小*2决定的存在的行数(累计统计)
                    }
                    //合并订单数据
                    for (int j = currentMergeStartRow; j < currentUseRow; j++) {
                        sheet.addMergedRegion(new CellRangeAddress(j, currentUseRow - 1, 0, 0));//起始行号，终止行号， 起始列号，终止列号
                        sheet.addMergedRegion(new CellRangeAddress(j, currentUseRow - 1, 1, 1));//起始行号，终止行号， 起始列号，终止列号
                        sheet.addMergedRegion(new CellRangeAddress(j, currentUseRow - 1, 4, 4));//起始行号，终止行号， 起始列号，终止列号
                        sheet.addMergedRegion(new CellRangeAddress(j, currentUseRow - 1, 5, 5));//起始行号，终止行号， 起始列号，终止列号
                        sheet.addMergedRegion(new CellRangeAddress(j, currentUseRow - 1, 6, 6));//起始行号，终止行号， 起始列号，终止列号
                        sheet.addMergedRegion(new CellRangeAddress(j, currentUseRow - 1, 7, 7));//起始行号，终止行号， 起始列号，终止列号
                        sheet.addMergedRegion(new CellRangeAddress(j, currentUseRow - 1, 8, 8));//起始行号，终止行号， 起始列号，终止列号
                        sheet.addMergedRegion(new CellRangeAddress(j, currentUseRow - 1, 9, 9));//起始行号，终止行号， 起始列号，终止列号
                        sheet.addMergedRegion(new CellRangeAddress(j, currentUseRow - 1, 10, 10));//起始行号，终止行号， 起始列号，终止列号
                        sheet.addMergedRegion(new CellRangeAddress(j, currentUseRow - 1, 11, 11));//起始行号，终止行号， 起始列号，终止列号
                        sheet.addMergedRegion(new CellRangeAddress(j, currentUseRow - 1, 12, 12));//起始行号，终止行号， 起始列号，终止列号
                        sheet.addMergedRegion(new CellRangeAddress(j, currentUseRow - 1, 13, 13));//起始行号，终止行号， 起始列号，终止列号
                        sheet.addMergedRegion(new CellRangeAddress(j, currentUseRow - 1, 14, 14));//起始行号，终止行号， 起始列号，终止列号
                        sheet.addMergedRegion(new CellRangeAddress(j, currentUseRow - 1, 15, 15));//起始行号，终止行号， 起始列号，终止列号
                        sheet.addMergedRegion(new CellRangeAddress(j, currentUseRow - 1, 16, 16));//起始行号，终止行号， 起始列号，终止列号
                        sheet.addMergedRegion(new CellRangeAddress(j, currentUseRow - 1, 17, 17));//起始行号，终止行号， 起始列号，终止列号
                        sheet.addMergedRegion(new CellRangeAddress(j, currentUseRow - 1, 18, 18));//起始行号，终止行号， 起始列号，终止列号
                        sheet.addMergedRegion(new CellRangeAddress(j, currentUseRow - 1, 19, 19));//起始行号，终止行号， 起始列号，终止列号
                        sheet.addMergedRegion(new CellRangeAddress(j, currentUseRow - 1, 20, 20));//起始行号，终止行号， 起始列号，终止列号
                    }
                    currentMergeStartRow += size;//提供下一个订单出账合并使用
                }
            }
        }
        try {
            ByteArrayOutputStream os = new ByteArrayOutputStream();
            wb.write(os);
            //调用浏览器自带的下载器，要先将数据转成byte流
            String fileName = "收款详情列表.xls";
            byte[] content = os.toByteArray();
            response.setContentType("application/x-download");
            response.setHeader("Content-disposition", "attachment; filename=" + Encodes.urlEncode(fileName));
            response.getOutputStream().write(content);
            response.getOutputStream().flush();
            response.getOutputStream().close();
            os.flush();
            os.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public Double formatting(Double num) {
        String data = "0";
        if (num != null) {
            data = String.format("%.2f", num);
        }
        return Double.parseDouble(data);
    }
}