package com.ywt.service;

import com.ywt.core.DataResponse;
import com.ywt.core.PagedList;
import com.ywt.core.SqlHelper;
import com.ywt.core.utils.Checker;
import com.ywt.core.utils.DateUtil;
import com.ywt.core.utils.ExcelHelper;
import com.ywt.core.utils.FormatUtil;
import com.ywt.core.utils.serializers.JsonSerializer;
import com.ywt.domain.entity.glasses.GoodsOrders;
import com.ywt.domain.entity.glasses.GoodsOrdersRepository;

import com.ywt.domain.models.enums.GetChannelEnum;
import com.ywt.domain.models.enums.GoodsOrdersStatusEnum;
import com.ywt.domain.models.enums.PaymentChannelEnum;
import com.ywt.domain.models.enums.PaymentStatusEnum;
import com.ywt.params.mg.order.*;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import org.apache.dubbo.config.annotation.DubboService;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.stream.Collectors;

@DubboService
@Service
public class GoodsOrderService {

    private static final Logger logger = LoggerFactory.getLogger(GoodsService.class);

    @Autowired
    @Qualifier("sqlHelperGlasses")
    private SqlHelper sqlHelper;

    @Autowired
    private UserService userService;

    @Autowired
    private GoodsOrdersRepository goodsOrdersRepository;


    //展示订单列表
    public PagedList<GoodsOrders> queryOrderList(QueryOrdersListRequest request) {
        List<Object> paramList = new ArrayList<>();
        String whereSql = " ( 1=1 )";

        whereSql += "and deleted = 0";

        String orderNo = request.getOrderNo();
        if (!Checker.isNull(orderNo)) {
            whereSql += " and (order_no like ?)";
            paramList.add("%" + orderNo.trim() + "%");
        }

        String paymentNo = request.getPaymentNo();
        if (!Checker.isNull(paymentNo)) {
            whereSql += " and (payment_no like ?)";
            paramList.add("%" + paymentNo.trim() + "%");
        }

        String patientName = request.getPatientName();
        if (!Checker.isNull(patientName)) {
            whereSql += " and (patient_name like ?)";
            paramList.add("%" + patientName.trim() + "%");
        }

        String mobile = request.getMobile();
        if (!Checker.isNull(mobile)) {
            whereSql += " and (mobile like ?)";
            paramList.add("%" + mobile.trim() + "%");
        }

        String getChannel = request.getGetChannel();
        if (!Checker.isNull(getChannel)) {
            int Channel = Integer.parseInt(getChannel);
            whereSql += " and (get_channel = ?)";
            paramList.add(Channel);
        }

        String status = request.getStatus();
        if (!Checker.isNull(status)) {
            int status2 = Integer.parseInt(status);
            whereSql += " and (status = ?)";
            paramList.add(status2);
        }

        String paymentStatus = request.getPaymentStatus();
        if (!Checker.isNull(paymentStatus)) {
            int paymentStatus2 = Integer.parseInt(paymentStatus);
            whereSql += " and (payment_status = ?)";
            paramList.add(paymentStatus2);
        }

        String paymentChannel = request.getPaymentChannel();
        if (!Checker.isNull(paymentChannel)) {
            int paymentChannel2 = Integer.parseInt(paymentChannel);
            whereSql += " and (payment_channel = ?)";
            paramList.add(paymentChannel2);
        }

        int shopId = userService.getShopId();
        whereSql += " and (shop_id =?)";
        paramList.add(shopId);

        String formatDate = "yyyy-MM-dd";
        SimpleDateFormat format = new SimpleDateFormat(formatDate);
        try {

            String createTime = request.getCreateTime();
            if (!Checker.isNull(createTime)) {
                whereSql += " and (create_time >= ?)";
                Date date = format.parse(createTime);
                paramList.add(date);
            }

            String createEndTime = request.getCreateEndTime();
            if (!Checker.isNull(createEndTime)) {

                whereSql += " and ( create_time < ? )";
                Date date = format.parse(createEndTime);

                //把日期往后增加一天.整数往后推,负数往前移动
                Calendar calendar = new GregorianCalendar();
                calendar.setTime(date);
                calendar.add(Calendar.DATE, 1);
                //这个时间就是日期往后推一天的结果
                date = calendar.getTime();
                paramList.add(date);
            }

            String paymentTime = request.getPaymentTime();
            if (!Checker.isNull(paymentTime)) {
                whereSql += " and (payment_time >= ?)";
                Date payDate = format.parse(paymentTime);
                paramList.add(payDate);
            }


            String paymenEndTime = request.getPaymentEndTime();
            if (!Checker.isNull(paymenEndTime)) {
                whereSql += " and (payment_time < ? )";
                Date payDate = format.parse(paymenEndTime);

                //把日期往后增加一天.整数往后推,负数往前移动
                Calendar calendar = new GregorianCalendar();
                calendar.setTime(payDate);
                calendar.add(Calendar.DATE, 1);
                //这个时间就是日期往后推一天的结果
                payDate = calendar.getTime();
                paramList.add(payDate);
            }


            String refundTime = request.getRefundTime();
            if (!Checker.isNull(refundTime)) {
                whereSql += " and (refund_time >= ?)";
                Date refundDate = format.parse(refundTime);
                paramList.add(refundDate);
            }

            String refundEndTime = request.getRefundEndTime();
            if (!Checker.isNull(refundEndTime)) {
                whereSql += " and (refund_time < ? )";
                Date refundDate = format.parse(refundEndTime);

                //把日期往后增加一天.整数往后推,负数往前移动
                Calendar calendar = new GregorianCalendar();
                calendar.setTime(refundDate);
                calendar.add(Calendar.DATE, 1);
                //这个时间就是日期往后推一天的结果
                refundDate = calendar.getTime();
                paramList.add(refundDate);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        return sqlHelper.getPagedList(request.getPageIndex(), request.getPageSize(), "*", whereSql, "create_time desc", GoodsOrders.class, paramList.toArray());
    }


    public List<GoodsOrders> showOrdersList(QueryOrdersListRequest request) throws Exception {
        List<Object> paramList = new ArrayList<>();
        String whereSql = " ( 1=1 )";

        whereSql += "and deleted = 0";

        String orderNo = request.getOrderNo();
        if (!Checker.isNull(orderNo)) {
            whereSql += " and (order_no like ?)";
            paramList.add("%" + orderNo.trim() + "%");
        }

        String paymentNo = request.getPaymentNo();
        if (!Checker.isNull(paymentNo)) {
            whereSql += " and (payment_no like ?)";
            paramList.add("%" + paymentNo.trim() + "%");
        }

        String patientName = request.getPatientName();
        if (!Checker.isNull(patientName)) {
            whereSql += " and (patient_name like ?)";
            paramList.add("%" + patientName.trim() + "%");
        }

        String mobile = request.getMobile();
        if (!Checker.isNull(mobile)) {
            whereSql += " and (mobile like ?)";
            paramList.add("%" + mobile.trim() + "%");
        }

        String getChannel = request.getGetChannel();
        if (!Checker.isNull(getChannel)) {
            int Channel = Integer.parseInt(getChannel);
            whereSql += " and (get_channel = ?)";
            paramList.add(Channel);
        }

        String status = request.getStatus();
        if (!Checker.isNull(status)) {
            int status2 = Integer.parseInt(status);
            whereSql += " and (status = ?)";
            paramList.add(status2);
        }

        String paymentStatus = request.getPaymentStatus();
        if (!Checker.isNull(paymentStatus)) {
            int paymentStatus2 = Integer.parseInt(paymentStatus);
            whereSql += " and (payment_status = ?)";
            paramList.add(paymentStatus2);
        }

        String paymentChannel = request.getPaymentChannel();
        if (!Checker.isNull(paymentChannel)) {
            int paymentChannel2 = Integer.parseInt(paymentChannel);
            whereSql += " and (payment_channel = ?)";
            paramList.add(paymentChannel2);
        }

        if (request.getShopId() > 0) {
            whereSql += " and (shop_id =?)";
            paramList.add(request.getShopId());
        } else {
            int shopId = userService.getShopId();
            whereSql += " and (shop_id =?)";
            paramList.add(shopId);
        }

        String formatDate = "yyyy-MM-dd";
        SimpleDateFormat format = new SimpleDateFormat(formatDate);
        try {

            String createTime = request.getCreateTime();
            if (!Checker.isNull(createTime)) {
                whereSql += " and (create_time >= ?)";
                Date date = format.parse(createTime);
                paramList.add(date);

            }
            String createEndTime = request.getCreateEndTime();
            if (!Checker.isNull(createEndTime)) {
                whereSql += " and ( create_time < ? )";
                Date date = format.parse(createEndTime);

                //把日期往后增加一天.整数往后推,负数往前移动
                Calendar calendar = new GregorianCalendar();
                calendar.setTime(date);
                calendar.add(Calendar.DATE, 1);
                //这个时间就是日期往后推一天的结果
                date = calendar.getTime();
                paramList.add(date);
            }

            String paymentTime = request.getPaymentTime();
            if (!Checker.isNull(paymentTime)) {
                whereSql += " and (payment_time >= ?)";
                Date date = format.parse(paymentTime);
                paramList.add(date);
            }
            String paymenEndTime = request.getPaymentEndTime();
            if (!Checker.isNull(paymenEndTime)) {
                whereSql += " and (payment_time < ? )";
                Date payDate = format.parse(paymenEndTime);

                //把日期往后增加一天.整数往后推,负数往前移动
                Calendar calendar = new GregorianCalendar();
                calendar.setTime(payDate);
                calendar.add(Calendar.DATE, 1);
                //这个时间就是日期往后推一天的结果
                payDate = calendar.getTime();
                paramList.add(payDate);
            }

            String refundTime = request.getRefundTime();
            if (!Checker.isNull(refundTime)) {
                whereSql += " and (refund_time >= ?)";
                Date date = format.parse(refundTime);
                paramList.add(date);
            }
            String refundEndTime = request.getRefundEndTime();
            if (!Checker.isNull(refundEndTime)) {
                whereSql += " and (refund_time < ? )";
                Date refundDate = format.parse(refundEndTime);

                //把日期往后增加一天.整数往后推,负数往前移动
                Calendar calendar = new GregorianCalendar();
                calendar.setTime(refundDate);
                calendar.add(Calendar.DATE, 1);
                //这个时间就是日期往后推一天的结果
                refundDate = calendar.getTime();
                paramList.add(refundDate);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }

        return sqlHelper.getDownLoadList("*", whereSql, "create_time desc", GoodsOrders.class, paramList.toArray());
    }


    //下载订单
    private void detailExcel(QueryOrdersListRequest request, int n, WritableCellFormat cellFormat2, WritableSheet sheet) throws Exception {
        List<GoodsOrders> goodsList = showOrdersList(request);
        for (GoodsOrders p : goodsList) {
            if (!Checker.isNone(p)) {
                String orderNoValue = p.getOrderNo();
                String paymentNoValue = Checker.getStringValue(p.getPaymentNo());
                String patientNameValue = p.getPatientName();
                String ageValue = p.getAge().toString();
                String sexValue = p.getSex() == 1 ? "男" : "女";
                String mobileValue = p.getMobile();
                String getChannelValue = (GetChannelEnum.valueOf(p.getGetChannel())).getDisplayName();
                String payAmountValue = FormatUtil.intShrink100ToStr(p.getPayAmount());
                String statusValue = (GoodsOrdersStatusEnum.valueOf(p.getStatus())).getDisplayName();
                String paymentStatus = (PaymentStatusEnum.getPaymentStatus(p.getPaymentStatus())).getDisplayName();
                String paymentChannelValue = "";
                if (!Checker.isNone(p.getPaymentChannel())) {
                    paymentChannelValue = PaymentChannelEnum.WeChat == PaymentChannelEnum.valueOf(p.getPaymentChannel()) ? "微信支付" : "其他支付方式";
                }
                String createTimeValue = DateUtil.convertToString(p.getCreateTime());
                String paymentTimeValue = "";
                String paymentTime = DateUtil.convertToString(p.getPaymentTime());
                if (!Checker.isNull(paymentTime)) {
                    paymentTimeValue = paymentTime;
                }
                String refundTimeValue = "";
                String refundTime = DateUtil.convertToString(p.getRefundTime());
                if (!Checker.isNull(refundTime)) {
                    refundTimeValue = refundTime;
                }
                String remarkValue = "";

                if (!Checker.isNull(p.getRefundRemark())) {
                    remarkValue = p.getRefundRemark();
                }
                String[] bodyStr = new String[]{orderNoValue, paymentNoValue, patientNameValue, ageValue, sexValue, mobileValue,
                        getChannelValue, payAmountValue, statusValue, paymentStatus, paymentChannelValue, createTimeValue, paymentTimeValue, refundTimeValue, remarkValue};
                ExcelHelper.settingMainBodyLabelCell(sheet, cellFormat2, n, bodyStr);
                n++;
            }
        }
    }


    public void downloadGoods(QueryOrdersListRequest request, HttpServletResponse httpServletResponse) {
        String filename = "眼镜订单.xls";
        OutputStream out = null;
        WritableWorkbook workbook = null;
        try {

            ExcelHelper.setHttpServletResponse(httpServletResponse, filename);
            out = httpServletResponse.getOutputStream();
            // 创建写工作簿对象
            workbook = Workbook.createWorkbook(out);
            // 工作表（设置第一个sheet）
            WritableSheet sheet = workbook.createSheet(filename, 0);
            // 设置第一行格式
            WritableCellFormat cellFormat = ExcelHelper.getWritableCellFormat();
            // 设置列的不同宽度;
            Map<Integer, Integer> widthSet = new HashMap<>(16);
            widthSet.put(0, 35);
            widthSet.put(1, 35);
            widthSet.put(7, 30);
            ExcelHelper.settingColumnWidth(sheet, widthSet);
            String[] title = new String[]{"订单号", "支付流水号", "患者姓名", "年龄", "性别", "患者手机号码", "取货方式", "金额", "状态", "支付状态",
                    "支付方式", "创建时间", "支付时间", "退费时间", "备注"};

            // 第二行格式
            ExcelHelper.settingTitleLabelCell(sheet, cellFormat, title);
            int n = 1;
            // 第二行格式
            WritableCellFormat cellFormat2 = ExcelHelper.getWritableCellFormat();

            detailExcel(request, n, cellFormat2, sheet);

            workbook.write();


        } catch (Exception e) {
            if (out != null)
                e.printStackTrace();
            logger.error("/goods/downloadGoodsList(): {}", e.getMessage(), e);
        } finally {
            if (workbook != null) {
                try {
                    workbook.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
            if (out != null) {
                try {
                    out.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }

    }

    //查询当天付款成功和退款请求的订单
    public List<GoodsOrders> amountVoListTwo(String createTime, String createEndTime) {
        List<Object> paramList = new ArrayList<>();
        String from = "goods_orders";
        String whereSql = " ( 1=1 )";
        whereSql += " and payment_channel is not null";
        whereSql += " and deleted = 0";
        whereSql += " and status > 0";

        int shopId = userService.getShopId();
        whereSql += " and (shop_id =?)";
        paramList.add(shopId);

        String formatDate = "yyyy-MM-dd";
        SimpleDateFormat format = new SimpleDateFormat(formatDate);

        try {

            if (!Checker.isNull(createTime)) {
                whereSql += " and (payment_time >= ?  )";
                Date date = format.parse(createTime);
                paramList.add(date);
            }

            if (!Checker.isNull(createEndTime)) {
                whereSql += " and ( payment_time < ?)";
                Date date = format.parse(createEndTime);

                //把日期往后增加一天.整数往后推,负数往前移动
                Calendar calendar = new GregorianCalendar();
                calendar.setTime(date);
                calendar.add(Calendar.DATE, 1);
                //这个时间就是日期往后推一天的结果
                date = calendar.getTime();
                paramList.add(date);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        return sqlHelper.getDownLoadList("*", whereSql, " create_time desc", GoodsOrders.class, paramList.toArray());
    }


    public List<GoodsOrders> amountVoListTh(String createTime, String createEndTime) {
        List<Object> paramList = new ArrayList<>();
        String from = "goods_orders";
        String whereSql = " ( 1=1 )";
        whereSql += " and payment_channel is not null";
        whereSql += " and deleted = 0";
        whereSql += " and status = 1";

        int shopId = userService.getShopId();
        whereSql += " and (shop_id =?)";
        paramList.add(shopId);

        String formatDate = "yyyy-MM-dd";
        SimpleDateFormat format = new SimpleDateFormat(formatDate);

        try {

            if (!Checker.isNull(createTime)) {
                whereSql += " and (payment_time >= ?  )";
                Date date = format.parse(createTime);
                paramList.add(date);
            }

            if (!Checker.isNull(createEndTime)) {
                whereSql += " and ( payment_time < ?)";
                Date date = format.parse(createEndTime);

                //把日期往后增加一天.整数往后推,负数往前移动
                Calendar calendar = new GregorianCalendar();
                calendar.setTime(date);
                calendar.add(Calendar.DATE, 1);
                //这个时间就是日期往后推一天的结果
                date = calendar.getTime();
                paramList.add(date);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        return sqlHelper.getDownLoadList("*", whereSql, " create_time desc", GoodsOrders.class, paramList.toArray());
    }

    public List<GoodsOrders> amountVoListRe(String createTime, String createEndTime) {
        List<Object> paramList = new ArrayList<>();
        String from = "goods_orders";
        String whereSql = " ( 1=1 )";
        whereSql += " and payment_channel is not null";
        whereSql += " and deleted = 0";
        whereSql += " and status = 2";

        int shopId = userService.getShopId();
        whereSql += " and (shop_id =?)";
        paramList.add(shopId);

        String formatDate = "yyyy-MM-dd";
        SimpleDateFormat format = new SimpleDateFormat(formatDate);

        try {

            if (!Checker.isNull(createTime)) {
                whereSql += " and ( refund_time >= ? )";
                Date date = format.parse(createTime);
                paramList.add(date);
            }

            if (!Checker.isNull(createEndTime)) {
                whereSql += " and (refund_time < ? )";
                Date date = format.parse(createEndTime);

                //把日期往后增加一天.整数往后推,负数往前移动
                Calendar calendar = new GregorianCalendar();
                calendar.setTime(date);
                calendar.add(Calendar.DATE, 1);
                //这个时间就是日期往后推一天的结果
                date = calendar.getTime();
                paramList.add(date);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        return sqlHelper.getDownLoadList("*", whereSql, " create_time desc", GoodsOrders.class, paramList.toArray());
    }


    //根据支付方式分组
    public List<Map<String, Object>> amountListPay(String createTime, String createEndTime) {

        List<Map<String, Object>> datas = new LinkedList<>();
        Map<String, Object> items = new HashMap<>();
        //  int count = 0;
        //int amount = 0;
        items.put("label", "收银统计");
        items.put("table", new ArrayList<>());
        List<Map<String, Object>> datad = new LinkedList<>();
        Map<String, Object> itemm = new HashMap<>();
        itemm.put("dataSource", new ArrayList<>());
        itemm.put("columns", new ArrayList<>());
        Map<String, Object> itemPay = new HashMap<>();
        Map<String, Object> itemCount = new HashMap<>();
        Map<String, Object> itemAmount = new HashMap<>();
        List<Map<String, Object>> dataa = new LinkedList<>();

        itemPay.put("title", "支付方式");
        itemPay.put("dataIndex", "paymentChannel");
        itemPay.put("key", "paymentChannel");
        itemPay.put("width", "500px");

        itemAmount.put("title", "金额");
        itemAmount.put("dataIndex", "orderAmount");
        itemAmount.put("key", "orderAmount");
        itemAmount.put("width", "500px");

        itemCount.put("title", "单数");
        itemCount.put("dataIndex", "orderCount");
        itemCount.put("key", "orderCount");
        itemCount.put("width", "500px");

        dataa.add(itemPay);
        dataa.add(itemAmount);
        dataa.add(itemCount);

        // List<Integer> list = goodsOrdersRepository.findPaymentChannel();


        List<Map<String, Object>> datass = new LinkedList<>();

        Map<String, Object> item = new HashMap<>();
        Map<String, Object> itemOther = new HashMap<>();
        Map<String, Object> itemTotal = new HashMap<>();
        Map<String, Object> itemWeRe = new HashMap<>();
        Map<String, Object> itemOtherRe = new HashMap<>();
        Map<String, Object> itemTotalRe = new HashMap<>();
        //微信支付的账单
        item.put("key", "wechat");
        item.put("paymentChannel", "微信");
        item.put("orderCount", 0);
        item.put("orderAmount", "0.00");

        itemWeRe.put("key", "wechatRe");
        itemWeRe.put("paymentChannel", "微信退款");
        itemWeRe.put("orderCount", 0);
        itemWeRe.put("orderAmount", "0.00");

        //其他方式账单
        itemOther.put("key", "other");
        itemOther.put("paymentChannel", "其他付款方式");
        itemOther.put("orderCount", 0);
        itemOther.put("orderAmount", "0.00");

        itemOtherRe.put("key", "otherRe");
        itemOtherRe.put("paymentChannel", "其他方式退款");
        itemOtherRe.put("orderCount", 0);
        itemOtherRe.put("orderAmount", "0.00");

        //总账单
        itemTotal.put("key", "total");
        itemTotal.put("paymentChannel", "合计");
        itemTotal.put("orderCount", 0);
        itemTotal.put("orderAmount", "0.00");

        List<GoodsOrders> amountList = amountVoListTwo(createTime, createEndTime);
        List<GoodsOrders> amountReList = amountVoListRe(createTime, createEndTime);

        int amount = 0;
        int count = 0;
        int amountWeRe = 0;
        int countWeRe = 0;
        int amountOther = 0;
        int countOther = 0;
        int amountORe = 0;
        int countORe = 0;
        int amountRe = 0;
        int countRe = 0;
        int totalAmount = 0;
        int totalCount = 0;
        //  amountList.stream().filter(p ->Checker.getStringValue(p.getPaymentTime().toString()) == createTime || (p.getPaymentTime()).toString() == createEndTime);

        if (!Checker.isNone(amountList)) {
            for (GoodsOrders amountLs : amountList) {
                totalAmount += Checker.getIntegerValue(amountLs.getPayAmount());
                //itemTotal.put("orderAmount", FormatUtil.intShrink100ToStr(totalAmount));
                totalCount += 1;
                // itemTotal.put("orderCount", Checker.getIntegerValue(totalCount));

                //判断是否是微信支付方式
                if (amountLs.getPaymentChannel() == PaymentChannelEnum.WeChat.getValue()) {
                    amount += Checker.getIntegerValue(amountLs.getPayAmount());

                    item.put("orderAmount", FormatUtil.intShrink100ToStr(amount));
                    count += 1;
                    item.put("orderCount", count);

                } else if (amountLs.getPaymentChannel() != 1) {
                    // List<GoodsOrders> amountList = amountVoListTwo(createTime, createEndTime);
                    amountOther += Checker.getIntegerValue(amountLs.getPayAmount());
                    itemOther.put("orderAmount", FormatUtil.intShrink100ToStr(amountOther));
                    countOther += 1;
                    itemOther.put("orderCount", Checker.getIntegerValue(countOther));
                }
            }
        }

        if (!Checker.isNone(amountReList)) {
            for (GoodsOrders reLs : amountReList) {
                amountRe += Checker.getIntegerValue(reLs.getPayAmount());
                countRe += 1;
                if (reLs.getPaymentChannel() == PaymentChannelEnum.WeChat.getValue()) {
                    amountWeRe += Checker.getIntegerValue(reLs.getPayAmount());
                    itemWeRe.put("orderAmount", FormatUtil.intShrink100ToStr(amountWeRe));
                    countWeRe += 1;
                    itemWeRe.put("orderCount", Checker.getIntegerValue(countWeRe));
                } else {
                    amountORe += Checker.getIntegerValue(reLs.getPayAmount());
                    itemOtherRe.put("orderAmount", FormatUtil.intShrink100ToStr(amountORe));
                    countORe += 1;
                    itemOtherRe.put("orderCount", Checker.getIntegerValue(countORe));
                }
            }
        }

        totalAmount = totalAmount - amountRe;
        itemTotal.put("orderAmount", FormatUtil.intShrink100ToStr(totalAmount));
        totalCount = totalCount - countRe;
        itemTotal.put("orderCount", Checker.getIntegerValue(totalCount));

        datass.add(item);
        datass.add(itemWeRe);
        datass.add(itemOther);
        datass.add(itemOtherRe);
        datass.add(itemTotal);

        itemm.put("dataSource", datass);
        itemm.put("columns", dataa);

        datad.add(itemm);
        items.put("table", datad);
        datas.add(items);
        return datas;
    }

    //根据取货方式展示账单
    public List<Map<String, Object>> amountListPost(String createTime, String createEndTime) {

        List<Map<String, Object>> datases = new LinkedList<>();
        Map<String, Object> items = new HashMap<>();
        items.put("label", "营业统计");
        items.put("table", new ArrayList<>());
        List<Map<String, Object>> datad = new LinkedList<>();
        Map<String, Object> itemm = new HashMap<>();
        Map<String, Object> itemmHand = new HashMap<>();
        Map<String, Object> itemmPost = new HashMap<>();
        Map<String, Object> itemmRe = new HashMap<>();
        itemm.put("dataSource", new ArrayList<>());
        itemm.put("columns", new ArrayList<>());
        itemmHand.put("dataSource", new ArrayList<>());
        itemmHand.put("columns", new ArrayList<>());
        itemmPost.put("dataSource", new ArrayList<>());
        itemmPost.put("columns", new ArrayList<>());
        itemmRe.put("dataSource", new ArrayList<>());
        itemmRe.put("columns", new ArrayList<>());

        Map<String, Object> itemAverage = new HashMap<>();
        Map<String, Object> itemCount = new HashMap<>();
        Map<String, Object> itemAmount = new HashMap<>();
        Map<String, Object> itemAverageHand = new HashMap<>();
        Map<String, Object> itemCountHand = new HashMap<>();
        Map<String, Object> itemAmountHand = new HashMap<>();
        Map<String, Object> itemAveragePost = new HashMap<>();
        Map<String, Object> itemCountPost = new HashMap<>();
        Map<String, Object> itemAmountPost = new HashMap<>();
        Map<String, Object> itemAverageRe = new HashMap<>();
        Map<String, Object> itemCountRe = new HashMap<>();
        Map<String, Object> itemAmountRe = new HashMap<>();
        List<Map<String, Object>> dataa = new LinkedList<>();
        List<Map<String, Object>> dataaHand = new LinkedList<>();
        List<Map<String, Object>> dataaPost = new LinkedList<>();
        List<Map<String, Object>> dataaRe = new LinkedList<>();

        itemCount.put("title", "总单数");
        itemCount.put("dataIndex", "totalOrderCount");
        itemCount.put("key", "totalOrderCount");
        itemCount.put("width", "500px");

        itemAmount.put("title", "订单总额");
        itemAmount.put("dataIndex", "orderAmount");
        itemAmount.put("key", "orderAmount");
        itemAmount.put("width", "500px");

        itemAverage.put("title", "均单消费");
        itemAverage.put("dataIndex", "amountPerOrder");
        itemAverage.put("key", "amountPerOrder");
        itemAverage.put("width", "500px");

        //自取
        itemCountHand.put("title", "自取单数");
        itemCountHand.put("dataIndex", "totalOrderCount");
        itemCountHand.put("key", "totalOrderCount");
        itemCountHand.put("width", "500px");

        itemAmountHand.put("title", "订单总额");
        itemAmountHand.put("dataIndex", "orderAmount");
        itemAmountHand.put("key", "orderAmount");
        itemAmountHand.put("width", "500px");

        itemAverageHand.put("title", "均单消费");
        itemAverageHand.put("dataIndex", "amountPerOrder");
        itemAverageHand.put("key", "amountPerOrder");
        itemAverageHand.put("width", "500px");

        //邮寄
        itemCountPost.put("title", "邮寄单数");
        itemCountPost.put("dataIndex", "totalOrderCount");
        itemCountPost.put("key", "totalOrderCount");
        itemCountPost.put("width", "500px");

        itemAmountPost.put("title", "订单总额");
        itemAmountPost.put("dataIndex", "orderAmount");
        itemAmountPost.put("key", "orderAmount");
        itemAmountPost.put("width", "500px");

        itemAveragePost.put("title", "均单消费");
        itemAveragePost.put("dataIndex", "amountPerOrder");
        itemAveragePost.put("key", "amountPerOrder");
        itemAveragePost.put("width", "500px");

        //退款
        itemCountRe.put("title", "退款单数");
        itemCountRe.put("dataIndex", "totalOrderCount");
        itemCountRe.put("key", "totalOrderCount");
        itemCountRe.put("width", "500px");

        itemAmountRe.put("title", "退款总额");
        itemAmountRe.put("dataIndex", "orderAmount");
        itemAmountRe.put("key", "orderAmount");
        itemAmountRe.put("width", "500px");

        itemAverageRe.put("title", "均单退款");
        itemAverageRe.put("dataIndex", "amountPerOrder");
        itemAverageRe.put("key", "amountPerOrder");
        itemAverageRe.put("width", "500px");

        dataa.add(itemCount);
        dataa.add(itemAmount);
        dataa.add(itemAverage);


        dataaHand.add(itemCountHand);
        dataaHand.add(itemAmountHand);
        dataaHand.add(itemAverageHand);

        dataaPost.add(itemCountPost);
        dataaPost.add(itemAmountPost);
        dataaPost.add(itemAveragePost);

        dataaRe.add(itemCountRe);
        dataaRe.add(itemAmountRe);
        dataaRe.add(itemAverageRe);

        List<Map<String, Object>> datass = new LinkedList<>();
        List<Map<String, Object>> datassHand = new LinkedList<>();
        List<Map<String, Object>> datassPost = new LinkedList<>();
        List<Map<String, Object>> datassRe = new LinkedList<>();
        Map<String, Object> itemPost = new HashMap<>();
        Map<String, Object> itemHand = new HashMap<>();
        Map<String, Object> itemTotal = new HashMap<>();
        Map<String, Object> itemRe = new HashMap<>();
        //邮寄方式
        itemPost.put("key", "post");
        itemPost.put("totalOrderCount", 0);
        itemPost.put("orderAmount", "0.00");
        itemPost.put("amountPerOrder", "0.00");

        //自取方式
        itemHand.put("key", "hand");
        itemHand.put("totalOrderCount", 0);
        itemHand.put("orderAmount", "0.00");
        itemHand.put("amountPerOrder", "0.00");

        //总账单
        itemTotal.put("key", "total");
        itemTotal.put("totalOrderCount", 0);
        itemTotal.put("orderAmount", "0.00");
        itemTotal.put("amountPerOrder", "0.00");

        //退款
        itemRe.put("key", "Refund");
        itemRe.put("totalOrderCount", 0);
        itemRe.put("orderAmount", "0.00");
        itemRe.put("amountPerOrder", "0.00");

        List<GoodsOrders> amountList = amountVoListTh(createTime, createEndTime);
        List<GoodsOrders> amountReList = amountVoListRe(createTime, createEndTime);
        int amount = 0;
        int count = 0;
        int amountTotal = 0;
        int countTotal = 0;
        int amountHand = 0;
        int countHand = 0;
        int amountRe = 0;
        int countRe = 0;

        if (!Checker.isNone(amountList)) {
            for (GoodsOrders lss : amountList) {
                amountTotal += Checker.getIntegerValue(lss.getPayAmount());
                itemTotal.put("orderAmount", FormatUtil.intShrink100ToStr(amountTotal));
                countTotal += 1;
                itemTotal.put("totalOrderCount", Checker.getIntegerValue(countTotal));

                if (amountTotal == 0 || countTotal == 0) {
                    itemTotal.put("amountPerOrder", "0.00");
                } else {
                    DecimalFormat df = new DecimalFormat("0.00");
                    String b = df.format(new BigDecimal(amountTotal).divide(new BigDecimal("100"), 8, BigDecimal.ROUND_HALF_UP).divide(new BigDecimal(countTotal), 8, BigDecimal.ROUND_HALF_UP).doubleValue());
                    itemTotal.put("amountPerOrder", b);
                }
                //判断是否为邮寄
                if (lss.getGetChannel() == GetChannelEnum.MAIL.getValue()) {
                    amount += Checker.getIntegerValue(lss.getPayAmount());
                    itemPost.put("orderAmount", FormatUtil.intShrink100ToStr(amount));
                    count += 1;
                    itemPost.put("totalOrderCount", Checker.getIntegerValue(count));
                    if (amount == 0 || count == 0) {
                        itemPost.put("amountPerOrder", "0.00");
                    } else {
                        //计算单均消费，总金额除以单数
                        DecimalFormat df = new DecimalFormat("0.00");
                        String a = df.format(new BigDecimal(amount).divide(new BigDecimal("100"), 8, BigDecimal.ROUND_HALF_UP).divide(new BigDecimal(count), 8, BigDecimal.ROUND_HALF_UP).doubleValue());
                        itemPost.put("amountPerOrder", a);
                    }
                } else if (lss.getGetChannel() == GetChannelEnum.INVITE.getValue()) {
                    amountHand += Checker.getIntegerValue(lss.getPayAmount());
                    itemHand.put("orderAmount", FormatUtil.intShrink100ToStr(amountHand));
                    countHand += 1;
                    itemHand.put("totalOrderCount", Checker.getIntegerValue(countHand));
                    if (amountHand == 0 || countHand == 0) {
                        itemHand.put("amountPerOrder", "0.00");
                    } else {
                        DecimalFormat df = new DecimalFormat("0.00");
                        String a = df.format(new BigDecimal(amountHand).divide(new BigDecimal("100"), 8, BigDecimal.ROUND_HALF_UP).divide(new BigDecimal(countHand), 8, BigDecimal.ROUND_HALF_UP).doubleValue());
                        itemHand.put("amountPerOrder", a);
                    }
                }
            }
        }
        if (!Checker.isNone(amountReList)) {
            for (GoodsOrders reList : amountReList) {
                amountRe += Checker.getIntegerValue(reList.getPayAmount());
                itemRe.put("orderAmount", FormatUtil.intShrink100ToStr(amountRe));
                countRe += 1;
                itemRe.put("totalOrderCount", Checker.getIntegerValue(countRe));
                if (amountRe == 0 || countRe == 0) {
                    itemRe.put("amountPerOrder", "0.00");
                } else {
                    DecimalFormat df = new DecimalFormat("0.00");
                    String re = df.format(new BigDecimal(amountRe).divide(new BigDecimal("100"), 8, BigDecimal.ROUND_HALF_UP).divide(new BigDecimal(countRe), 8, BigDecimal.ROUND_HALF_UP).doubleValue());
                    itemRe.put("amountPerOrder", re);
                }
            }
        }


        datass.add(itemTotal);
        datassHand.add(itemHand);
        datassPost.add(itemPost);
        datassRe.add(itemRe);


        itemm.put("dataSource", datass);
        itemm.put("columns", dataa);
        itemmHand.put("dataSource", datassHand);
        itemmHand.put("columns", dataaHand);
        itemmPost.put("dataSource", datassPost);
        itemmPost.put("columns", dataaPost);
        itemmRe.put("dataSource", datassRe);
        itemmRe.put("columns", dataaRe);

        datad.add(itemm);
        datad.add(itemmHand);
        datad.add(itemmPost);
        datad.add(itemmRe);
        items.put("table", datad);
        datases.add(items);
        return datases;
    }


    private String valueField() {
        return "payment_channel, " +
                "count(order_no) , " +
                "SUM(pay_amount) ";
    }

    public List<PaymentChannelVo> amountVoLists(String createTime, String createEndTime) {
        List<Object> paramList = new ArrayList<>();
        String from = "goods_orders";
        String whereSql = " ( 1=1 )";
        whereSql += " and payment_channel is not null";
        whereSql += " and deleted = 0";
        whereSql += " and status = 1";
        whereSql += " GROUP BY payment_channel";

        int shopId = userService.getShopId();
        whereSql += " and (shop_id =?)";
        paramList.add(shopId);
        String fields = valueField();

        String formatDate = "yyyy-MM-dd";
        SimpleDateFormat format = new SimpleDateFormat(formatDate);

        try {

            if (!Checker.isNull(createTime)) {
                whereSql += " and (create_time >= ?)";
                Date date = format.parse(createTime);
                paramList.add(date);
            }

            if (!Checker.isNull(createEndTime)) {
                whereSql += " and ( create_time < ? )";
                Date date = format.parse(createEndTime);

                //把日期往后增加一天.整数往后推,负数往前移动
                Calendar calendar = new GregorianCalendar();
                calendar.setTime(date);
                calendar.add(Calendar.DATE, 1);
                //这个时间就是日期往后推一天的结果
                date = calendar.getTime();
                paramList.add(date);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        return sqlHelper.getPagedList3(fields, from, whereSql, " SUM(pay_amount) desc", PaymentChannelVo.class, paramList.toArray());
    }

    public DataResponse<List<PaymentChannelVo>> getOrderPayList(AmoutListRequest request) {
        DataResponse<List<PaymentChannelVo>> response = new DataResponse<>();
        List<PaymentChannelVo> list = amountVoLists(request.getCreateTime(), request.getCreateEndTime());
        if (!Checker.isNone(list)) {
            response.setData(list);
        }
        return response;
    }


    public List<PaymentChannelVo> getOrderPayListTwo(AmoutListRequest request) {
        DataResponse<List<PaymentChannelVo>> response = new DataResponse<>();
        List<PaymentChannelVo> list = new ArrayList<>();
        list = amountVoLists(request.getCreateTime(), request.getCreateEndTime());
        List<PaymentChannelVo> list2 = new ArrayList<>();
        PaymentChannelVo p = new PaymentChannelVo();
        p.setPaymentChannel(1);
        p.setCount(0);
        p.setSum(0);
        PaymentChannelVo p2 = new PaymentChannelVo();
        p2.setPaymentChannel(3);
        p2.setCount(0);
        p2.setSum(0);
        list2.add(p);
        list2.add(p2);

        if (!Checker.isNone(list)) {
            if (list.size() == 2) {
                return list;
            } else {
                for (PaymentChannelVo lss : list) {
                    List<PaymentChannelVo> list3 = new ArrayList<>();
                    if (lss.getPaymentChannel() == 1) {
                        p.setPaymentChannel(1);
                        p.setSum(lss.getSum());
                        p.setCount(lss.getCount());
                        list3.add(p);
                        list3.add(p2);
                        return list3;
                    } else {
                        p2.setPaymentChannel(lss.getPaymentChannel());
                        p2.setSum(lss.getSum());
                        p2.setCount(lss.getCount());
                        list3.add(p);
                        list3.add(p2);
                        return list3;
                    }
                }
            }
        }
        return list2;
    }

    private void detailExcelA(AmoutListRequest request, int n, WritableCellFormat cellFormat3, WritableSheet sheet) throws Exception {
        List<PaymentChannelVo> payAmountList = getOrderPayListTwo(request);
        int payAmount = 0;
        int totalCount = 0;
        for (PaymentChannelVo p : payAmountList) {
            if (!Checker.isNone(p)) {
                String payChannel = p.getPaymentChannel() == 1 ? "微信支付" : "其他支付方式";
                String sum = FormatUtil.intShrink100ToStr(p.getSum());
                String count = (p.getCount()).toString();
                String[] bodyStr = new String[]{payChannel, count, sum};
                ExcelHelper.settingMainBodyLabelCell(sheet, cellFormat3, n, bodyStr);
                n++;

                payAmount += Checker.getIntegerValue(p.getSum());
                totalCount += Checker.getIntegerValue(p.getCount());
            }
        }
        // n = n + 2;
        Label valuel3 = new Label(0, n, "合计", cellFormat3);
        Label valuel4 = new Label(1, n, String.valueOf(totalCount), cellFormat3);
        Label valuel5 = new Label(2, n, FormatUtil.intShrink100ToStr(payAmount), cellFormat3);
        sheet.addCell(valuel3);
        sheet.addCell(valuel4);
        sheet.addCell(valuel5);

    }

    public void downloadAmountList(AmoutListRequest request, HttpServletResponse httpServletResponse) {
        String filename = "报表.xls";
        OutputStream out = null;
        WritableWorkbook workbook = null;
        try {
            ExcelHelper.setHttpServletResponse(httpServletResponse, filename);
            out = httpServletResponse.getOutputStream();
            // 创建写工作簿对象
            workbook = Workbook.createWorkbook(out);
            // 工作表（设置第一个sheet）
            WritableSheet sheet = workbook.createSheet(filename, 0);
            // 设置第一行格式
            WritableCellFormat cellFormat1 = ExcelHelper.getWritableCellFormat();
            // 设置列的不同宽度;
            Map<Integer, Integer> widthSet1 = new HashMap<>(16);
            widthSet1.put(0, 35);
            widthSet1.put(1, 35);
            widthSet1.put(7, 30);
            ExcelHelper.settingColumnWidth(sheet, widthSet1);
            // 设置第一行所在的标题
            String[] title1 = new String[]{"收银统计"};
            ExcelHelper.settingTitleLabelCell(sheet, cellFormat1, title1);

            int n = 1;
            //  n =2;
            // WritableSheet sheet = workbook.createSheet(filename, 0);
            // 设置第一行格式
            WritableCellFormat cellFormat = ExcelHelper.getWritableCellFormat();
            // 设置列的不同宽度;
            Map<Integer, Integer> widthSet = new HashMap<>(16);
            widthSet.put(0, 35);
            widthSet.put(1, 35);
            widthSet.put(7, 30);
            ExcelHelper.settingColumnWidth(sheet, widthSet);
            // 设置第一行所在的标题
            String[] title = new String[]{"支付方式", "单数", "金额"};
            ExcelHelper.settingTitleLabelCell(sheet, cellFormat, n, title);

            //上面是产生表头

            // 第3行格式
            WritableCellFormat cellFormat3 = ExcelHelper.getWritableCellFormat();
            // 处理excel表格
            n = 2;
            detailExcelA(request, n, cellFormat3, sheet);

            //开始执行写入操作
            workbook.write();

        } catch (Exception e) {
            if (out != null)
                e.printStackTrace();
            logger.error("/mg/: {}", e.getMessage(), e);
        } finally {
            if (workbook != null) {
                try {
                    workbook.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
            if (out != null) {
                try {
                    out.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
    }


    private String getFieldString() {
        return "d.id as id, " +
                "o.order_no as order_no, " +
                "d.goods_orders_id as goods_orders_id, " +
                "o.get_channel as get_channel,  " +
                "d.goods_name as goods_name, " +
                "d.goods_id as goods_id, " +
                "d.type_name as type_name, " +
                "g.spec as spec, " +
                "d.goods_price as goods_price, " +
                "d.goods_number as goods_number, " +
                "d.pay_amount as pay_amount, " +
                "o.create_time as create_time, " +
                "o.payment_time as payment_time, " +
                "o.refund_time as refund_time ";
    }

    //对账汇总查询
    public List<PaymentOrderVo> amountVoLs(AmoutListRequest request) {
        List<PaymentOrderVo> a = new ArrayList<>();
        List<Object> paramList = new ArrayList<>();
        //  String from = "goods_orders";
        String fields = getFieldString();
        String from = "glasses.goods_orders_detail d left join glasses.goods_orders o on d.goods_orders_id = o.id " +
                "join glasses.goods g on d.goods_id = g.id";

        String whereSql = " ( 1=1 )";
        whereSql += " and d.deleted = 0";
        whereSql += " and o.status = 1";


        if (request.getShopId() > 0) {
            whereSql += " and (o.shop_id =?)";
            paramList.add(request.getShopId());
        } else {
            int shopId = userService.getShopId();
            whereSql += " and (o.shop_id =?)";
            paramList.add(shopId);
        }


        String formatDate = "yyyy-MM-dd";
        SimpleDateFormat format = new SimpleDateFormat(formatDate);

        try {

            if (!Checker.isNull(request.getCreateTime())) {
                whereSql += " and (o.payment_time >= ?)";
                Date date = format.parse(request.getCreateTime());
                paramList.add(date);
            }

            if (!Checker.isNull(request.getCreateEndTime())) {
                whereSql += " and ( o.payment_time < ? )";
                Date date = format.parse(request.getCreateEndTime());

                //把日期往后增加一天.整数往后推,负数往前移动
                Calendar calendar = new GregorianCalendar();
                calendar.setTime(date);
                calendar.add(Calendar.DATE, 1);
                //这个时间就是日期往后推一天的结果
                date = calendar.getTime();
                paramList.add(date);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        return sqlHelper.getPagedList3(fields, from, whereSql, " o.create_time desc", PaymentOrderVo.class, paramList.toArray());

    }

    //对账下载表头数据查询
    public List<PaymentOrderVo> amountVoLss(AmoutListRequest request) {
        List<PaymentOrderVo> a = new ArrayList<>();
        List<Object> paramList = new ArrayList<>();
        //  String from = "goods_orders";
        String fields = getFieldString();
        String from = "glasses.goods_orders_detail d left join glasses.goods_orders o on d.goods_orders_id = o.id " +
                "join glasses.goods g on d.goods_id = g.id";

        String whereSql = " ( 1=1 )";
        whereSql += " and d.deleted = 0";
        whereSql += " and o.status > 0";


        if (request.getShopId() > 0) {
            whereSql += " and (o.shop_id =?)";
            paramList.add(request.getShopId());
        } else {
            int shopId = userService.getShopId();
            whereSql += " and (o.shop_id =?)";
            paramList.add(shopId);
        }


        String formatDate = "yyyy-MM-dd";
        SimpleDateFormat format = new SimpleDateFormat(formatDate);

        try {

            if (!Checker.isNull(request.getCreateTime())) {
                whereSql += " and (o.payment_time >= ? or o.refund_time >= ? )";
                Date date = format.parse(request.getCreateTime());
                paramList.add(date);
                paramList.add(date);
            }

            if (!Checker.isNull(request.getCreateEndTime())) {
                whereSql += " and ( o.payment_time < ?  or o.refund_time < ? )";
                Date date = format.parse(request.getCreateEndTime());

                //把日期往后增加一天.整数往后推,负数往前移动
                Calendar calendar = new GregorianCalendar();
                calendar.setTime(date);
                calendar.add(Calendar.DATE, 1);
                //这个时间就是日期往后推一天的结果
                date = calendar.getTime();
                paramList.add(date);
                paramList.add(date);
            }


        } catch (Exception e) {
            e.printStackTrace();
        }
        return sqlHelper.getPagedList3(fields, from, whereSql, " o.create_time desc", PaymentOrderVo.class, paramList.toArray());

    }


    public int count(AmoutListRequest request) {
      logger.info("count：{}", JsonSerializer.toJson(request));
        List<Object> paramList = new ArrayList<>();
        String from = "glasses.goods_orders_detail d left join glasses.goods_orders o on d.goods_orders_id = o.id ";
        String whereSql = " ( 1=1 )";
        whereSql += " and o.status > 0";
        whereSql += " and d.deleted = 0";
        String fields = "count(d.id) ";


        if (request.getShopId() > 0) {
            whereSql += " and (o.shop_id =?)";
            paramList.add(request.getShopId());
        } else {
            int shopId = userService.getShopId();
            whereSql += " and (o.shop_id =?)";
            paramList.add(shopId);
        }


        String formatDate = "yyyy-MM-dd";
        SimpleDateFormat format = new SimpleDateFormat(formatDate);
        try {

            if (!Checker.isNull(request.getCreateTime())) {
                whereSql += " and (o.payment_time >= ? or o.refund_time >= ? )";
                Date date = format.parse(request.getCreateTime());
                paramList.add(date);
                paramList.add(date);
            }

            if (!Checker.isNull(request.getCreateEndTime())) {
                whereSql += " and ( o.payment_time < ?  or o.refund_time < ? )";
                Date date = format.parse(request.getCreateEndTime());

                //把日期往后增加一天.整数往后推,负数往前移动
                Calendar calendar = new GregorianCalendar();
                calendar.setTime(date);
                calendar.add(Calendar.DATE, 1);
                //这个时间就是日期往后推一天的结果
                date = calendar.getTime();
                paramList.add(date);
                paramList.add(date);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        return sqlHelper.getSearchTotal(from, whereSql, paramList.toArray());
    }

    public DataResponse amountOrderLs(AmoutListRequest request) {
        DataResponse<List<PaymentOrderVo>> response = new DataResponse<>();
        List<PaymentOrderVo> list = amountVoLs(request);
        List<PaymentOrderVo> ls = new ArrayList<>();
        for (PaymentOrderVo lss : list) {
            PaymentOrderVo p = new PaymentOrderVo();
            p.setId(lss.getId());
            p.setOrderNo(lss.getOrderNo());
            p.setGoodsName(lss.getGoodsName());
            p.setTypeName(lss.getTypeName());
            p.setGetChannel(lss.getGetChannel());
            p.setGoodsId(lss.getGoodsId());
            p.setSpec(lss.getSpec());
            p.setGoodsPrice(lss.getGoodsPrice());
            p.setGoodsNumber(lss.getGoodsNumber());
            p.setPayAmount(lss.getPayAmount());
            p.setCreateTime(lss.getCreateTime());
            p.setPaymentTime(lss.getPaymentTime());
            ls.add(p);
        }
        response.setData(ls);
        return response;
    }

    private void detailExcelAA(AmoutListRequest request, int n, WritableCellFormat cellFormat, WritableSheet sheet) throws Exception {
        logger.info("detailExcelAA：{}", JsonSerializer.toJson(request));
        List<PaymentOrderVo> list = amountVoLss(request);
        List<PaymentOrderVo> ls = new ArrayList<>();
        for (PaymentOrderVo lss : list) {
            int id = lss.getId();
            String orderNo = lss.getOrderNo();
            String goodsName = lss.getGoodsName();
            String typeName = lss.getTypeName();
            String getChannel = (GetChannelEnum.valueOf(lss.getGetChannel())).getDisplayName();
            String spec = lss.getSpec();
            String goodPrice = FormatUtil.intShrink100ToStr(Checker.getIntegerValue(lss.getGoodsPrice()));
            String goodNumber = Checker.getIntegerValue(lss.getGoodsNumber()) + "";
            String payAmount = FormatUtil.intShrink100ToStr(Checker.getIntegerValue(lss.getPayAmount()));
            String createTime = DateUtil.convertToString(lss.getCreateTime());
            String paymentTime = DateUtil.convertToString(lss.getPaymentTime());
            String refundTime = DateUtil.convertToString(lss.getRefundTime());
            String[] bodyStr = new String[]{orderNo, goodsName, typeName, getChannel, spec, goodPrice, goodNumber, payAmount, createTime, paymentTime, refundTime};
            ExcelHelper.settingMainBodyLabelCell(sheet, cellFormat, n, bodyStr);
            n++;
        }

    }

    private void detailExcelB(AmoutListRequest request, int n, WritableCellFormat cellFormat, WritableSheet sheet) throws Exception {
        logger.info("detailExcelBB：{}", JsonSerializer.toJson(request));
        List<PaymentOrderVo> list = amountVoLs(request);
        List<PaymentOrderVo> ls = new ArrayList<>();

        for (PaymentOrderVo p : list) {
            p.setGroupBy((p.getGoodsId()).toString() + p.getGoodsPrice());
            ls.add(p);
        }
        Map<String, List<PaymentOrderVo>> orderList = ls.stream().collect(Collectors.groupingBy(PaymentOrderVo::getGroupBy));
        int totalAmount = 0;
        int totalNumber = 0;
        for (String key : orderList.keySet()) {
            List<PaymentOrderVo> lss = orderList.get(key);
            int toAmount = 0;
            int toCount = 0;
            if (!Checker.isNone(lss)) {
                List<TotalVo> lists = new ArrayList<>();
                TotalVo t = new TotalVo();
                t.setName(lss.get(0).getGoodsName());
                t.setSpec(lss.get(0).getSpec());
                t.setPrice(lss.get(0).getGoodsPrice());
                for (PaymentOrderVo o : lss) {
                    // t.setName(o.getGoodsName());
                    //t.setSpec(o.getSpec());
                    t.setPrice(o.getGoodsPrice());
                    toAmount += o.getPayAmount();
                    t.setTotalAmount(toAmount);
                    //String price = FormatUtil.intShrink100ToStr(o.getGoodsPrice());
                    // toPrice += price;
                    int count = o.getGoodsNumber();
                    toCount += count;
                    t.setCount(toCount);
                }
                String name = t.getName();
                String spec = t.getSpec();
                String price = FormatUtil.intShrink100ToStr(t.getPrice());
                int amountP = t.getTotalAmount();
                String amount = FormatUtil.intShrink100ToStr(amountP);
                int gNumber = toCount;
                String number = t.getCount().toString();
                String[] secondBodyStr = new String[]{name, spec, price, number, amount};
                ExcelHelper.settingMainBodyLabelCell(sheet, cellFormat, n, secondBodyStr);
                n = n + 1;


                totalAmount += amountP;

                totalNumber += gNumber;
            }

        }
        Label valuelTh = new Label(0, n, "合计", cellFormat);
        Label valuelF = new Label(1, n, "", cellFormat);
        Label valuelFi = new Label(2, n, "", cellFormat);
        Label valuelSi = new Label(3, n, String.valueOf(totalNumber), cellFormat);
        Label valuelSe = new Label(4, n, FormatUtil.intShrink100ToStr(totalAmount), cellFormat);
        sheet.addCell(valuelTh);
        sheet.addCell(valuelF);
        sheet.addCell(valuelFi);
        sheet.mergeCells(0, n, 2, n);
        sheet.addCell(valuelSi);
        sheet.addCell(valuelSe);
        n = n + 1;
    }


    public void downloadAmountListTwo(AmoutListRequest request, HttpServletResponse httpServletResponse) {
        String filename = "报表数据";
        OutputStream out = null;
        WritableWorkbook workbook = null;
        try {

            String createTime = request.getCreateTime();
            String createEndTime = request.getCreateEndTime();
            if (!Checker.isNone(createTime) && !Checker.isNone(createEndTime)) {
                filename += createTime.replace("-", "") + "~" + createEndTime.replace("-", "");
            }

            ExcelHelper.setHttpServletResponse(httpServletResponse, filename + ".xls");
            out = httpServletResponse.getOutputStream();
            // 创建写工作簿对象
            workbook = Workbook.createWorkbook(out);
            // 工作表（设置第一个sheet）
            WritableSheet sheet = workbook.createSheet(filename, 0);
            // 设置第一行格式
            WritableCellFormat cellFormat = ExcelHelper.getWritableCellFormat();
            // 设置列的不同宽度;
            Map<Integer, Integer> widthSetOne = new HashMap<>(16);
            widthSetOne.put(0, 35);
            widthSetOne.put(1, 35);
            widthSetOne.put(7, 30);
            ExcelHelper.settingColumnWidth(sheet, widthSetOne);
            // 设置第一行所在的标题
            String[] title = new String[]{"订单号", "取货方式", "名称", "类型", "规格", "单价", "数量", "合计", "创建时间", "支付时间", "退费时间"};
            //上面是产生表头
            ExcelHelper.settingTitleLabelCell(sheet, cellFormat, title);
            int n = 1;
            // 第3行格式
            WritableCellFormat cellFormatTwo = ExcelHelper.getWritableCellFormat();
            // 处理excel表格

            detailExcelAA(request, n, cellFormatTwo, sheet);


            if (Checker.isNone(count(request))) {
                n = n + 2;
            } else {
                int count = count(request);
                n = n + count + 2;
            }


            WritableCellFormat cellFormatThree = ExcelHelper.getWritableCellFormat();
            Map<Integer, Integer> widthSetTwo = new HashMap<>(16);
            widthSetTwo.put(0, 35);
            widthSetTwo.put(1, 35);
            widthSetTwo.put(7, 30);
            ExcelHelper.settingColumnWidth(sheet, widthSetTwo);
            String[] title2 = new String[]{"汇总"};
            ExcelHelper.settingTitleLabelCell(sheet, cellFormatThree, n, title2);

            n = n + 1;
            WritableCellFormat cellFormatF = ExcelHelper.getWritableCellFormat();
            Map<Integer, Integer> widthSetTh = new HashMap<>(16);
            widthSetTh.put(0, 35);
            widthSetTh.put(1, 35);
            widthSetTh.put(7, 30);
            ExcelHelper.settingColumnWidth(sheet, widthSetTh);
            String[] title3 = new String[]{"名称", "规格", "单价", "数量", "总价"};
            ExcelHelper.settingTitleLabelCell(sheet, cellFormatF, n, title3);

            n = n + 1;
            WritableCellFormat cellFormatFi = ExcelHelper.getWritableCellFormat();
            detailExcelB(request, n, cellFormatFi, sheet);

            workbook.write();

        } catch (Exception e) {
            if (out != null)
                e.printStackTrace();
            logger.error("/mg/: {}", e.getMessage(), e);
        } finally {
            if (workbook != null) {
                try {
                    workbook.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
            if (out != null) {
                try {
                    out.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
    }

}

