download_service/src/main/java/com/ywt/mg/services/MealOrderService.java

package com.ywt.mg.services;

import com.ywt.mg.configs.Constants;
import com.ywt.mg.core.SqlHelper;
import com.ywt.mg.core.utils.*;
import com.ywt.mg.domain.entities.OrderPayment;
import com.ywt.mg.domain.entities.RefundLog;
import com.ywt.mg.domain.mealEntities.Shop;

import com.ywt.mg.domain.mealEntities.UserMealOrderView;
import com.ywt.mg.domain.models.ConstantDef;
import com.ywt.mg.domain.models.ExcelDataMap;
import com.ywt.mg.domain.models.enums.*;
import com.ywt.mg.domain.models.nutrimeal.MealOrderInfo;
import com.ywt.mg.domain.models.pojo.CustomExcelItem;
import com.ywt.mg.domain.models.pojo.ExcelCollectPojo;
import com.ywt.mg.params.mealOrder.MealOrderListRequest;
import com.ywt.mg.web.common.ExcelDownloadSrv;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import java.text.SimpleDateFormat;
import java.util.*;
import java.util.stream.Collectors;

@Service
public class MealOrderService {

    private Logger logger = LoggerFactory.getLogger(MealOrderService.class);

    @Autowired
    private SqlHelper sqlHelper;

    @Autowired
    private DownloadRecordService downloadRecordService;

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Autowired
    private ShopService shopService;

    @Autowired
    private OrderDishService orderDishService;

    @Autowired
    private RefundLogService refundLogService;


    //客户端类型：商家 PC 端
    public static final int CLIENT_PC_MERCHANT = 1;
    //客户端类型：管理后台
    public static final int CLIENT_WEB_MG = 2;

    public static final String DATE_FORMAT1 = "yyyy-MM-dd";

    @Autowired
    private OrderPaymentService orderPaymentService;


    public List<UserMealOrderView> queryDownLoadMealOrderList(MealOrderListRequest request) {

        Map<String, Object> map = queryMealOrderListCommon(request);
        List<Object> paramList = (List<Object>) map.get(Constants.PARAM_LIST);
        String whereSql = (String) map.get(Constants.WHERE_SQL);
        // 分页查找
        StringBuilder sqlBuilder = new StringBuilder();
        // 拼凑sql语句
        sqlBuilder.append("select * from user_meal_order_view where ")
                .append(whereSql)
                .append("order by create_time desc");
        return jdbcTemplate.query(sqlBuilder.toString(), paramList.toArray(), new BeanPropertyRowMapper<>(UserMealOrderView.class));

    }


    private Map queryMealOrderListCommon(MealOrderListRequest request) {
        List<Object> paramList = new ArrayList<>();
        String whereSql = " ( 1=1 )";

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

        if (!Checker.isNone(request.getName())) {
            String name = request.getName();
            whereSql += " and ( contact_name like ?)";
            paramList.add("%" + name + "%");
        }

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

        if (!Checker.isNone(request.getPickupCode())) {
            String pickupCode = request.getPickupCode();
            whereSql += " and ( pickup_code like ?)";
            paramList.add("%" + pickupCode + "%");
        }

        if (!Checker.isNone(request.getType())) {
            String type = request.getType();
            boolean dineInStatusCompat = Checker.getBooleanValue(request.isDineInStatusCompat());
            if (dineInStatusCompat && (type.equals(String.valueOf(NutrimealOrderTypeEnum.DOGGY_BAG.getValue())) || type.equals(String.valueOf(NutrimealOrderTypeEnum.DINE_IN_SHOP.getValue())))) {
                whereSql += " and ( type = 2 or type = 3)";
            } else {
                whereSql += " and ( type = ?)";
                paramList.add(Integer.parseInt(type));
            }
        }

        if (!Checker.isNone(request.getPayType())) {
            String payType = request.getPayType();
            whereSql += " and ( pay_type = ?)";
            paramList.add(Integer.parseInt(payType));
        }

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

        int hospitalId = Checker.getIntegerValue(request.getHospitalId());
        if (hospitalId > 0) {
            whereSql += " and ( hospital_id = ?)";
            paramList.add(hospitalId);
        }
        /**
         * 根据职工管理版本将shopId, 转为shopIds, 即是shopIds 拥有多个shopId
         *
         */
        String shopId = Checker.getStringValue(request.getShopId());
        String shopIds = Checker.getStringValue(request.getShopIds());
        if (!Checker.isNone(shopId)) {
            int shopIdInt = Integer.parseInt(shopId);
            if (shopIdInt > 0) {
                whereSql += " and ( shop_id = ?)";
                paramList.add(shopIdInt);
            } else if (!Checker.isNone(shopIds) && !shopIds.equals("0") && !shopIds.startsWith(",") && !shopIds.endsWith(",")) {
                whereSql += " and ( shop_id in (" + shopIds + "))";
            }
        }
        try {
            int clientTypeInt = 2;
            if (!Checker.isNone(request.getClientType())) {
                clientTypeInt = Integer.parseInt(request.getClientType());
            }

            if (clientTypeInt == CLIENT_PC_MERCHANT) {
                // PC 商家端过滤未支付订单，只显示支付成功或失败的订单
                whereSql += " and ( status <> 0 and payment_status > 1 )";
            }
        } catch (NumberFormatException e) {
            logger.warn("MealOrderService#queryMealOrderListCommon(): Cannot format clientType {} as number!", shopIds);
        }

        if (request.getbIsStaff() != null) {
            boolean isStaff = request.getbIsStaff();
            whereSql += isStaff ? " and staff_id is not null " : " and staff_id is null ";
        }

        /**
         * status参考下面枚举
         * @see  com.ywt.mg.domain.models.enums.MealOrderStatusEnum
         */
        if (!Checker.isNone(request.getStatus())) {
            int statusInt = Integer.parseInt(request.getStatus());
            whereSql += " and ( (status & ?) = ? and status < ?) ";
            paramList.add(statusInt);
            paramList.add(statusInt);
            paramList.add(2 * statusInt);
        }

        if (!Checker.isNone(request.getStatus())) {
            try {
                int payStatusInt = Integer.parseInt(request.getStatus());
                if (payStatusInt == 0) {
                    // payStatus 传 0，可以查到未支付的订单
                    whereSql += " and ( payment_status is null or payment_status = 0 ) ";
                } else {
                    whereSql += " and ( payment_status = ? ) ";
                    paramList.add(payStatusInt);
                }
            } catch (NumberFormatException e) {
                logger.error("MealOrderService#queryMealOrderListCommon(orderNo={} , name={} , mobile={} , type={} , " +
                                "status={} , payStatus={} , payTimeStart={} , payTimeEnd={} , shopId={} , clientType={} , payType={} , " +
                                "paymentNo={} , refundTimeStart={} , refundTimeEnd={} , dineInStatusCompat={} , hospitalId={} , pickupCode={} ):\npayStatus 参数解析失败{}",
                        request.getOrderNo(), request.getName(), request.getMobile(), request.getType(), request.getStatus(), request.getPayStatus(), request.getPayTimeStart(), request.getPayTimeEnd(), shopIds, request.getClientType(),
                        request.getPayType(), request.getPaymentNo(), request.getRefundTimeStart(), request.getRefundTimeEnd(), request.isDineInStatusCompat(), hospitalId, request.getPickupCode(),
                        e.getMessage(), e);
            }
        }

        String Format_Date = DATE_FORMAT1;
        SimpleDateFormat format = new SimpleDateFormat(Format_Date);

        try {
            // 支付时间
            if (!Checker.isNone(request.getPayTimeEnd())) {
                String payTimeEnd = request.getPayTimeEnd();
                whereSql += " and ( payment_time < ?)";
                Date date = format.parse(payTimeEnd);
                //把日期往后增加一天.整数往后推,负数往前移动
                Calendar calendar = new GregorianCalendar();
                calendar.setTime(date);
                calendar.add(Calendar.DATE, 1);
                //这个时间就是日期往后推一天的结果
                date = calendar.getTime();
                paramList.add(date);
            }
            if (!Checker.isNone(request.getPayTimeStart())) {
                String payTimeStart = request.getPayTimeStart();
                whereSql += " and ( payment_time >= ?)";
                Date date = format.parse(payTimeStart);
                paramList.add(date);
            }

            //退款时间
            if (!Checker.isNone(request.getRefundTimeEnd())) {
                String refundTimeEnd = request.getRefundTimeEnd();
                whereSql += " and ( refund_time < ?)";
                Date date = format.parse(refundTimeEnd);
                //把日期往后增加一天.整数往后推,负数往前移动
                Calendar calendar = new GregorianCalendar();
                calendar.setTime(date);
                calendar.add(Calendar.DATE, 1);
                //这个时间就是日期往后推一天的结果
                date = calendar.getTime();
                paramList.add(date);
            }
            if (!Checker.isNone(request.getRefundTimeStart())) {
                String refundTimeStart = request.getRefundTimeStart();
                whereSql += " and ( refund_time >= ?)";
                Date date = format.parse(refundTimeStart);
                paramList.add(date);
            }
            // 对账时间
            // billStartTime, billEndTime
            if (!Checker.isNone(request.getBillEndTime())) {
                String billEndTime = request.getBillEndTime();
                whereSql += " and ( payment_time < ? or refund_time < ? )";
                Date date = format.parse(billEndTime);
                //把日期往后增加一天.整数往后推,负数往前移动
                Calendar calendar = new GregorianCalendar();
                calendar.setTime(date);
                calendar.add(Calendar.DATE, 1);
                //这个时间就是日期往后推一天的结果
                date = calendar.getTime();
                paramList.add(date);
                paramList.add(date);
            }
            if (!Checker.isNone(request.getBillStartTime())) {
                String billStartTime = request.getBillStartTime();
                whereSql += " and ( payment_time >= ? or refund_time >= ? )";
                Date date = format.parse(billStartTime);
                paramList.add(date);
                paramList.add(date);
            }
        } catch (Exception e) {
            logger.error("MealOrderService#queryMealOrderListCommon(orderNo={} , name={} , mobile={} , type={} , status={} , " +
                            "payStatus={} , payTimeStart={} , payTimeEnd={} ,billStartTime={} , billEndTime={} , shopId={} ):\n {}",
                    request.getOrderNo(), request.getName(), request.getMobile(), request.getType(), request.getStatus(), request.getPayStatus(), request.getPayTimeStart(), request.getPayTimeEnd(), request.getBillStartTime(), request.getBillEndTime(), shopIds, e.getMessage(), e);
        }
        try {
            // 处理交易流水号，考虑到性能，直接采用全匹配到方式
            if (!Checker.isNull(request.getTransactionId())) {
                String transactionId = request.getTransactionId();
                OrderPayment orderPayment = orderPaymentService.getOrderPaymentByTransactionId(transactionId);
                if (!Checker.isNone(orderPayment)) {
                    whereSql += " and ( order_no = ? )";
                    paramList.add(orderPayment.getOrderNo());
                } else {
                    whereSql += " and ( order_no = '-1' )";
                }
            }
        } catch (Exception e) {
            logger.error("MealOrderService#queryMealOrderListCommon(transactionId={} ):\n {} ", request.getTransactionId(), e.getMessage(), e);
        }
        whereSql += " and (deleted = 0)";
        Map<String, Object> map = new HashMap<>();
        map.put(Constants.WHERE_SQL, whereSql);
        map.put(Constants.PARAM_LIST, paramList);
        return map;
    }


    public void downloadMealOrderListNew(int downloadRecordId, String fileName, MealOrderListRequest request) {
        try {

            String col2 = "订单号";
            String col3 = "支付流水号";
            String col3_1 = "交易流水号";
            String col3_2 = "店铺名";
            String col4 = "客户姓名";
            String col5 = "客户手机号";
            String col6 = "取餐方式";
            String col7 = "状态";
            String col8 = "支付状态";
            String col9 = "支付方式";
            String col10 = "总价";
            String col11 = "支付时间";
            String col12 = "退款时间";
            String col13 = "取餐时间";
            String col14 = "备注";
            String col15 = "微信支付";
            String col16 = "金币支付";
            String col17 = "现场支付";
            String col18 = "医院";
//         String filename = "订餐订单列表";
            String col19 = "取餐码";
            String col20 = "职工";
            String col21 = "科室";
            String[] columns;

            String clientType = "2";
            if (!Checker.isNone(request.getClientType())) {
                clientType = request.getClientType();
            }
            int clientTypeInt = Integer.parseInt(clientType);
            if (clientTypeInt == CLIENT_WEB_MG) {
                // 管理后台显示支付流水号
                columns = new String[]{
                        col2, col3, col3_1, col3_2, col18, col4, col5, col20, col21, col6, col19, col7, col8, col9,
                        col10, col15, col16, col17, col11, col12, col13, col14
                };
            } else {
                columns = new String[]{
                        col2, col4, col5, col20, col6, col19, col7, col8, col9,
                        col10, col15, col16, col17, col11, col12, col13, col14
                };
            }
            List<Shop> shopList = shopService.getAllShops();
            ExcelDataMap map = new ExcelDataMap(columns);
            List<UserMealOrderView> userMealOrderViews = queryDownLoadMealOrderList(request);
            List<OrderPayment> orderPaymentList = orderPaymentService.getOrderPaymentListByMealOrderList(userMealOrderViews);
            userMealOrderViews.forEach(userMealOrderView -> {
//                map.getStringListSafely(col1).add(userMealOrderView.getId() + "");
                map.getStringListSafely(col2).add(Checker.getStringValue(userMealOrderView.getOrderNo()));
                map.getStringListSafely(col4).add(Checker.getStringValue(userMealOrderView.getContactName()));
                map.getStringListSafely(col5).add(Checker.getStringValue(userMealOrderView.getMobile()));
                String typeStr = NutrimealOrderTypeEnum.getDisplayName(Checker.getIntegerValue(userMealOrderView.getType()));
                map.getStringListSafely(col6).add(typeStr);
                String statusStr = NutrimealOrderStatusEnum.getDisplayName(Checker.getIntegerValue(userMealOrderView.getStatus()));
                map.getStringListSafely(col7).add(statusStr);
                String totalPriceStr = FormatUtil.intShrink100ToStr(userMealOrderView.getOrderAmount());
                map.getStringListSafely(col10).add(totalPriceStr);
                String payTime = FormatUtil.createTimeFormatList(userMealOrderView.getPaymentTime());
                map.getStringListSafely(col11).add(payTime);
                String deliveryTime = FormatUtil.createTimeFormatList(userMealOrderView.getDeliveryTime());
                map.getStringListSafely(col13).add(deliveryTime);
                map.getStringListSafely(col9).add(OrderPayTypeEnum.valueOf(Checker.getIntegerValue(userMealOrderView.getPayType())).getDisplayName());
                //支付状态
                Integer paymentStatus = userMealOrderView.getPaymentStatus();
                map.getStringListSafely(col8).add(paymentStatus == null ? NutrimealPaymentStatusEnum.Pending.getDisplayName() :
                        NutrimealPaymentStatusEnum.valueOf(paymentStatus).getDisplayName());
                //退款时间
                map.getStringListSafely(col12).add(FormatUtil.createTimeFormatList(userMealOrderView.getRefundTime()));
                //备注
                String extData = Checker.getStringValue(userMealOrderView.getExtData());
                Map<String, Object> m = FormatUtil.stringToMapT(extData);
                map.getStringListSafely(col14).add(m == null ? "" : (String) m.getOrDefault("refuseOrderRemark", ""));
                if (clientTypeInt == CLIENT_WEB_MG) {
                    // 管理后台显示支付流水号
                    map.getStringListSafely(col3).add(Checker.getStringValue(userMealOrderView.getPaymentNo()));

                    String transactionIdStr = orderPaymentService.getTransactionIdByOrderNo(orderPaymentList, Checker.getStringValue(userMealOrderView.getOrderNo()));
                    map.getStringListSafely(col3_1).add(transactionIdStr);

                    String shopName = getShopName(shopList, Checker.getIntegerValue(userMealOrderView.getShopId()));
                    map.getStringListSafely(col3_2).add(shopName);
                    map.getStringListSafely(col18).add(BizUtil.getHospitalNameByHospitalId(Checker.getIntegerValue(userMealOrderView.getHospitalId())));
                    map.getStringListSafely(col21).add(Checker.getStringValue(userMealOrderView.getDeptName()));

                }
                String wechatPayAmount = FormatUtil.intShrink100ToStr(userMealOrderView.getWechatPayAmount());
                map.getStringListSafely(col15).add(wechatPayAmount);
                String coinPayAmount = FormatUtil.intShrink100ToStr(userMealOrderView.getCoinPayAmount());
                map.getStringListSafely(col16).add(coinPayAmount);
                String f2fPayAmount = FormatUtil.intShrink100ToStr(userMealOrderView.getF2fPayAmount());
                map.getStringListSafely(col17).add(f2fPayAmount);
                map.getStringListSafely(col19).add(Checker.getStringValue(userMealOrderView.getPickupCode()));
                map.getStringListSafely(col20).add(userMealOrderView.getStaffId() == null ? "否" : "是");
            });
            boolean needStat = clientTypeInt == CLIENT_WEB_MG && !Checker.isNone(userMealOrderViews);
            int size = userMealOrderViews.size();
//         List<ExcelCollectPojo> itemList = getStatisticsData(size, payTotal);
//         downloadRecordService.createFileAndUploadOssAndSaveToDataBase(fileName, downloadRecordId, map, itemList);
            List<CustomExcelItem> itemList = getStatisticsData(userMealOrderViews, shopList);
            downloadRecordService.createMealFileAndUploadOssAndSaveToDataBase(fileName, downloadRecordId, map,
                    needStat ? itemList : null);

        } catch (Exception e) {
            logger.error("/mealOrder/downloadMealOrderList(): {}", e.getMessage(), e);
            e.printStackTrace();
        }

    }

    /**
     * 获取店铺名称
     *
     * @param shopList 店铺list
     * @param shopId   店铺ID
     * @return 店铺名称
     */
    public String getShopName(List<Shop> shopList, int shopId) {
        String backValue = "";
        Shop shop = shopList.stream().filter(p -> p.getId() == shopId).findFirst().orElse(null);
        if (shop != null) {
            backValue = shop.getName();
        }
        return backValue;
    }

    private List<CustomExcelItem> getStatisticsData(List<UserMealOrderView> userMealOrderViews, List<Shop> shopList) {
        int startRows = userMealOrderViews.size() + 3;
        Map<Integer, List<UserMealOrderView>> shopDataMap = userMealOrderViews.stream()
                // 过滤未支付的订单
                .filter(v -> Checker.getIntegerValue(v.getPaymentStatus()) > 1)
                .collect(Collectors.groupingBy(UserMealOrderView::getShopId));
        int startColumn = 3;
        List<CustomExcelItem> customExcelItemList = new ArrayList<>();
        // 按店铺统计
        for (Integer sId : shopDataMap.keySet()) {
            List<UserMealOrderView> orders = shopDataMap.get(sId);
            String shopName = getShopName(shopList, sId);
            customExcelItemList.add(new CustomExcelItem(startColumn, startRows, shopName, null));
            /**
             4、总成本=微信点单成本+金币点单成本+现场点单成本(也等于各个菜品成本价乘以份数的相加的总额) ;
             5、微信点单成本:支付方式是”微信支付“的订单，其各个菜品成本价乘以份数的相加的总额(不包含退款订单) ;
             金币点单成本:支付方式是”微信+金币“的订单，其各个菜品成本价乘以份数的相加的总额(不包含退款订单) ;
             现场点单成本:支付方式是”现场支付“的订单，其各个菜品成本价乘以份数的相加的总额(不包含退款订单) ;*/

            List<UserMealOrderView> wechatSinglePointCostList = orders.stream()
                    .filter(p -> p.getPayType() == OrderPayTypeEnum.WECHAT.getValue()
                            && RefundStatusEnum.SUCCESS.getValue() != Checker.getIntegerValue(p.getRefundStatus()))
                    .collect(Collectors.toList());
            int wechatSinglePointCostInt = orderDishService.getAllDishCostPriceSumByUserMealOrderViewList(wechatSinglePointCostList);
            List<UserMealOrderView> coinSinglePointCostList = orders.stream()
                    .filter(p -> p.getPayType() == OrderPayTypeEnum.COIN.getValue()
                            && RefundStatusEnum.SUCCESS.getValue() != Checker.getIntegerValue(p.getRefundStatus()))
                    .collect(Collectors.toList());
            int coinSinglePointCostInt = orderDishService.getAllDishCostPriceSumByUserMealOrderViewList(coinSinglePointCostList);
            List<UserMealOrderView> p2fSinglePointCostList = orders.stream()
                    .filter(p -> p.getPayType() == OrderPayTypeEnum.F2F.getValue()
                            && RefundStatusEnum.SUCCESS.getValue() != Checker.getIntegerValue(p.getRefundStatus()))
                    .collect(Collectors.toList());
            int p2fSinglePointCostInt = orderDishService.getAllDishCostPriceSumByUserMealOrderViewList(p2fSinglePointCostList);
            String wechatSinglePointCost = FormatUtil.intShrink100ToStr(wechatSinglePointCostInt);
            String coinSinglePointCost = FormatUtil.intShrink100ToStr(coinSinglePointCostInt);
            String p2fSinglePointCost = FormatUtil.intShrink100ToStr(p2fSinglePointCostInt);
            String allCost = FormatUtil.intShrink100ToStr(wechatSinglePointCostInt + coinSinglePointCostInt + p2fSinglePointCostInt);

            // 总计
            customExcelItemList.add(new CustomExcelItem(startColumn + 1, startRows, "支付总额", null));
            customExcelItemList.add(new CustomExcelItem(startColumn + 2, startRows,
                    FormatUtil.intShrink100ToStr(orders.stream()
                            .mapToInt(v -> Checker.getIntegerValue(v.getOrderAmount()))
                            .sum()), null));
            customExcelItemList.add(new CustomExcelItem(startColumn + 3, startRows, "退款总额", null));
            customExcelItemList.add(new CustomExcelItem(startColumn + 4, startRows,
                    FormatUtil.intShrink100ToStr(orders.stream()
                            .filter(v -> RefundStatusEnum.SUCCESS.getValue() == Checker.getIntegerValue(v.getRefundStatus()))
                            .mapToInt(v -> Checker.getIntegerValue(v.getOrderAmount()))
                            .sum()), null));
            customExcelItemList.add(new CustomExcelItem(startColumn + 5, startRows, "实际支付总额", null));
            customExcelItemList.add(new CustomExcelItem(startColumn + 6, startRows,
                    FormatUtil.intShrink100ToStr(orders.stream()
                            .filter(v -> RefundStatusEnum.SUCCESS.getValue() != Checker.getIntegerValue(v.getRefundStatus()))
                            .mapToInt(v -> Checker.getIntegerValue(v.getOrderAmount()))
                            .sum()), null));
            customExcelItemList.add(new CustomExcelItem(startColumn + 7, startRows, "总成本", null));
            customExcelItemList.add(new CustomExcelItem(startColumn + 8, startRows, allCost, null));
            startRows += 1;
            // 微信支付
            customExcelItemList.add(new CustomExcelItem(startColumn + 1, startRows, "微信支付总额", null));
            customExcelItemList.add(new CustomExcelItem(startColumn + 2, startRows,
                    FormatUtil.intShrink100ToStr(orders.stream()
                            .mapToInt(v -> Checker.getIntegerValue(v.getWechatPayAmount()))
                            .sum()), null));
            customExcelItemList.add(new CustomExcelItem(startColumn + 3, startRows, "微信退款总额", null));
            customExcelItemList.add(new CustomExcelItem(startColumn + 4, startRows,
                    FormatUtil.intShrink100ToStr(orders.stream()
                            .filter(v -> RefundStatusEnum.SUCCESS.getValue() == Checker.getIntegerValue(v.getRefundStatus()))
                            .mapToInt(v -> Checker.getIntegerValue(v.getWechatPayAmount()))
                            .sum()), null));
            customExcelItemList.add(new CustomExcelItem(startColumn + 5, startRows, "微信实际支付总额", null));
            customExcelItemList.add(new CustomExcelItem(startColumn + 6, startRows,
                    FormatUtil.intShrink100ToStr(orders.stream()
                            .filter(v -> RefundStatusEnum.SUCCESS.getValue() != Checker.getIntegerValue(v.getRefundStatus()))
                            .mapToInt(v -> Checker.getIntegerValue(v.getWechatPayAmount()))
                            .sum()), null));
            customExcelItemList.add(new CustomExcelItem(startColumn + 7, startRows, "微信点单成本", null));
            customExcelItemList.add(new CustomExcelItem(startColumn + 8, startRows, wechatSinglePointCost, null));
            startRows += 1;
            // 金币支付
            customExcelItemList.add(new CustomExcelItem(startColumn + 1, startRows, "金币支付总额", null));
            customExcelItemList.add(new CustomExcelItem(startColumn + 2, startRows,
                    FormatUtil.intShrink100ToStr(orders.stream()
                            .mapToInt(v -> Checker.getIntegerValue(v.getCoinPayAmount()))
                            .sum()), null));
            customExcelItemList.add(new CustomExcelItem(startColumn + 3, startRows, "金币退款总额", null));
            customExcelItemList.add(new CustomExcelItem(startColumn + 4, startRows,
                    FormatUtil.intShrink100ToStr(orders.stream()
                            .filter(v -> RefundStatusEnum.SUCCESS.getValue() == Checker.getIntegerValue(v.getRefundStatus()))
                            .mapToInt(v -> Checker.getIntegerValue(v.getCoinPayAmount()))
                            .sum()), null));
            customExcelItemList.add(new CustomExcelItem(startColumn + 5, startRows, "金币实际支付总额", null));
            customExcelItemList.add(new CustomExcelItem(startColumn + 6, startRows,
                    FormatUtil.intShrink100ToStr(orders.stream()
                            .filter(v -> RefundStatusEnum.SUCCESS.getValue() != Checker.getIntegerValue(v.getRefundStatus()))
                            .mapToInt(v -> Checker.getIntegerValue(v.getCoinPayAmount()))
                            .sum()), null));
            customExcelItemList.add(new CustomExcelItem(startColumn + 7, startRows, "金币点单成本", null));
            customExcelItemList.add(new CustomExcelItem(startColumn + 8, startRows, coinSinglePointCost, null));
            startRows += 1;
            // 现场支付
            customExcelItemList.add(new CustomExcelItem(startColumn + 1, startRows, "现场支付总额", null));
            customExcelItemList.add(new CustomExcelItem(startColumn + 2, startRows,
                    FormatUtil.intShrink100ToStr(orders.stream()
                            .mapToInt(v -> Checker.getIntegerValue(v.getF2fPayAmount()))
                            .sum()), null));
            customExcelItemList.add(new CustomExcelItem(startColumn + 3, startRows, "现场退款总额", null));
            customExcelItemList.add(new CustomExcelItem(startColumn + 4, startRows,
                    FormatUtil.intShrink100ToStr(orders.stream()
                            .filter(v -> RefundStatusEnum.SUCCESS.getValue() == Checker.getIntegerValue(v.getRefundStatus()))
                            .mapToInt(v -> Checker.getIntegerValue(v.getF2fPayAmount()))
                            .sum()), null));
            customExcelItemList.add(new CustomExcelItem(startColumn + 5, startRows, "现场实际支付总额", null));
            customExcelItemList.add(new CustomExcelItem(startColumn + 6, startRows,
                    FormatUtil.intShrink100ToStr(orders.stream()
                            .filter(v -> RefundStatusEnum.SUCCESS.getValue() != Checker.getIntegerValue(v.getRefundStatus()))
                            .mapToInt(v -> Checker.getIntegerValue(v.getF2fPayAmount()))
                            .sum()), null));
            customExcelItemList.add(new CustomExcelItem(startColumn + 7, startRows, "现场点单成本", null));
            customExcelItemList.add(new CustomExcelItem(startColumn + 8, startRows, p2fSinglePointCost, null));
            startRows += 1;
            // 洗碗费
            // 目前只计算有金币支付的订单，不包括纯微信支付及现场支付订单。计算的规则是符合条件的订单的微信支付金额总和。
            // 等同这个 SQL 语句：
            // select sum(wechat_pay_amount) from nutrimeal.meal_order where deleted = 0 and pay_type = 4 and (refund_status is null or refund_status != 1) and wechat_pay_amount is not null ;
            customExcelItemList.add(new CustomExcelItem(startColumn + 1, startRows, "2元洗碗费总额", null));
            customExcelItemList.add(new CustomExcelItem(startColumn + 2, startRows,
                    FormatUtil.intShrink100ToStr(orders.stream()
                            .filter(v -> OrderPayTypeEnum.COIN.getValue() == Checker.getIntegerValue(v.getPayType()) &&
                                    RefundStatusEnum.SUCCESS.getValue() != Checker.getIntegerValue(v.getRefundStatus()))
                            .mapToInt(v -> Checker.getIntegerValue(v.getWechatPayAmount()))
                            .sum()), null));
            startRows += 2;
            // v1.3.1_nfyybyfy 新增职工统计
            // 职工支付
            customExcelItemList.add(new CustomExcelItem(startColumn + 1, startRows, "职工支付总额", null));
            customExcelItemList.add(new CustomExcelItem(startColumn + 2, startRows,
                    FormatUtil.intShrink100ToStr(orders.stream()
                            .filter(v -> Checker.getIntegerValue(v.getStaffId()) > 0)
                            .mapToInt(v -> Checker.getIntegerValue(v.getOrderAmount()))
                            .sum()), null));
            customExcelItemList.add(new CustomExcelItem(startColumn + 3, startRows, "退款总额", null));
            customExcelItemList.add(new CustomExcelItem(startColumn + 4, startRows,
                    FormatUtil.intShrink100ToStr(orders.stream()
                            .filter(v -> Checker.getIntegerValue(v.getStaffId()) > 0)
                            .filter(v -> RefundStatusEnum.SUCCESS.getValue() == Checker.getIntegerValue(v.getRefundStatus()))
                            .mapToInt(v -> Checker.getIntegerValue(v.getOrderAmount()))
                            .sum()), null));
            customExcelItemList.add(new CustomExcelItem(startColumn + 5, startRows, "实际支付总额", null));
            customExcelItemList.add(new CustomExcelItem(startColumn + 6, startRows,
                    FormatUtil.intShrink100ToStr(orders.stream()
                            .filter(v -> Checker.getIntegerValue(v.getStaffId()) > 0)
                            .filter(v -> RefundStatusEnum.SUCCESS.getValue() != Checker.getIntegerValue(v.getRefundStatus()))
                            .mapToInt(v -> Checker.getIntegerValue(v.getOrderAmount()))
                            .sum()), null));
            startRows += 1;
            // 非职工支付
            customExcelItemList.add(new CustomExcelItem(startColumn + 1, startRows, "非职工支付总额", null));
            customExcelItemList.add(new CustomExcelItem(startColumn + 2, startRows,
                    FormatUtil.intShrink100ToStr(orders.stream()
                            .filter(v -> Checker.getIntegerValue(v.getStaffId()) <= 0)
                            .mapToInt(v -> Checker.getIntegerValue(v.getOrderAmount()))
                            .sum()), null));
            customExcelItemList.add(new CustomExcelItem(startColumn + 3, startRows, "退款总额", null));
            customExcelItemList.add(new CustomExcelItem(startColumn + 4, startRows,
                    FormatUtil.intShrink100ToStr(orders.stream()
                            .filter(v -> Checker.getIntegerValue(v.getStaffId()) <= 0)
                            .filter(v -> RefundStatusEnum.SUCCESS.getValue() == Checker.getIntegerValue(v.getRefundStatus()))
                            .mapToInt(v -> Checker.getIntegerValue(v.getOrderAmount()))
                            .sum()), null));
            customExcelItemList.add(new CustomExcelItem(startColumn + 5, startRows, "实际支付总额", null));
            customExcelItemList.add(new CustomExcelItem(startColumn + 6, startRows,
                    FormatUtil.intShrink100ToStr(orders.stream()
                            .filter(v -> Checker.getIntegerValue(v.getStaffId()) <= 0)
                            .filter(v -> RefundStatusEnum.SUCCESS.getValue() != Checker.getIntegerValue(v.getRefundStatus()))
                            .mapToInt(v -> Checker.getIntegerValue(v.getOrderAmount()))
                            .sum()), null));
            startRows += 2;
        }
        if (customExcelItemList.size() > 0) {
            customExcelItemList.add(new CustomExcelItem(startColumn + 1, startRows,
                    "备注（单位：元）：", ExcelDownloadSrv.getCellFormat4StatRemarkWithNoSetBorder()));
            customExcelItemList.add(new CustomExcelItem(startColumn + 1, startRows + 1,
                    "1、支付总额=微信支付总额+金币支付总额+现场支付总额；", ExcelDownloadSrv.getCellFormat4StatRemarkWithNoSetBorder()));
            customExcelItemList.add(new CustomExcelItem(startColumn + 1, startRows + 2,
                    "2、退款总额=微信退款总额+金币退款总额+现场退款总额；", ExcelDownloadSrv.getCellFormat4StatRemarkWithNoSetBorder()));
            customExcelItemList.add(new CustomExcelItem(startColumn + 1, startRows + 3,
                    "3、实际支付总额=微信实际支付总额+金币实际支付总额+现场实际支付总额=支付总额-退款总额；", ExcelDownloadSrv.getCellFormat4StatRemarkWithNoSetBorder()));
            customExcelItemList.add(new CustomExcelItem(startColumn + 1, startRows + 4,
                    "4、总成本=微信点单成本+金币点单成本+现场点单成本（也等于各个菜品成本价乘以份数的相加的总额）；", ExcelDownloadSrv.getCellFormat4StatRemarkWithNoSetBorder()));
            customExcelItemList.add(new CustomExcelItem(startColumn + 1, startRows + 5,
                    "5、微信点单成本：支付方式是“微信支付”的订单，其各个菜品成本价乘以份数的相加的总额（不包含退款订单）；", ExcelDownloadSrv.getCellFormat4StatRemarkWithNoSetBorder()));
            customExcelItemList.add(new CustomExcelItem(startColumn + 1, startRows + 6,
                    "   金币点单成本：支付方式是“微信+金币”的订单，其各个菜品成本价乘以份数的相加的总额（不包含退款订单）；", ExcelDownloadSrv.getCellFormat4StatRemarkWithNoSetBorder()));
            customExcelItemList.add(new CustomExcelItem(startColumn + 1, startRows + 7,
                    "   现场点单成本：支付方式是“现场支付”的订单，其各个菜品成本价乘以份数的相加的总额（不包含退款订单）；", ExcelDownloadSrv.getCellFormat4StatRemarkWithNoSetBorder()));
            customExcelItemList.add(new CustomExcelItem(startColumn + 1, startRows + 8,
                    "6、2元洗碗费总额：支付方式是“微信+金币”的订单，其微信支付部分的总额（不包含退款订单）。", ExcelDownloadSrv.getCellFormat4StatRemarkWithNoSetBorder()));
        }
        return customExcelItemList;
    }


    public void downloadMealOrderBillList(int downloadRecordId, String fileName, MealOrderListRequest request) {
        try {


            String col0 = "交易类型*";
            String col1 = "系统参考号*";
            String col2 = "交易金额*";
            String col3 = "交易日期*";
            String col4 = "订单号";
            String col5 = "类型";
            String[] columns = new String[]{col0, col1, col2, col3, col4, col5};
            List<Shop> shopList = shopService.getAllShops();
            ExcelDataMap map = new ExcelDataMap(columns);
            List<UserMealOrderView> userMealOrderViews = queryDownLoadMealOrderList(request);
//        List<OrderPayment> orderPaymentList = orderPaymentService.getOrderPaymentListByMealOrderList(userMealOrderViews);
            List<MealOrderInfo> list = new ArrayList<>();
            if (!Checker.isNone(userMealOrderViews)) {
                for (UserMealOrderView p : userMealOrderViews) {
                    MealOrderInfo info = new MealOrderInfo();
                    info.setId(p.getId());

                    // 订单号
                    info.setOrderNo(p.getOrderNo());

//                // 店铺名称
//                String shopName = mealOrderService.getShopName(shopList, Checker.getIntegerValue(p.getShopId()));
//                item.put("shopName", shopName);
                    // 客户姓名
                    info.setContactName(p.getContactName());
                    info.setMobile(p.getMobile());

                    int typeInt = Checker.getIntegerValue(p.getType());
                    String typeStr = MealOrderTypeEnum.getDisplayName(typeInt);
                    info.setType(typeStr);
//                item.put("type", typeStr);
//                item.put("typeInt", typeInt);
                    // 状态
                    int statusInt = Checker.getIntegerValue(p.getStatus());
                    String statusStr = MealOrderStatusEnum.getDisplayName(statusInt);
                    info.setStatus(statusStr);
                    info.setStatusInt(statusInt);
//                item.put("statusInt", statusInt);
//                item.put("status", statusStr);
                    // 总价
                    info.setTotalStr(FormatUtil.intShrink100ToStr(p.getOrderAmount()));
                    info.setWechatInt(Checker.getIntegerValue(p.getWechatPayAmount()));

//                item.put("totalStr", FormatUtil.intShrink100ToStr(p.getOrderAmount()));
//                item.put("wechatStr", FormatUtil.intShrink100ToStr(p.getWechatPayAmount()));
//                item.put("wechatInt", Checker.getIntegerValue(p.getWechatPayAmount()));
//                item.put("coinStr", FormatUtil.intShrink100ToStr(p.getCoinPayAmount()));
//                item.put("cashStr", FormatUtil.intShrink100ToStr(p.getF2fPayAmount()));
                    // 支付时间
                    info.setPaymentTime(FormatUtil.createTimeFormatList(p.getPaymentTime()));
//                item.put("paymentTime", FormatUtil.createTimeFormatList(p.getPaymentTime()));
                    // 取餐时间
                    info.setCreateTime(FormatUtil.createTimeFormatList(p.getDeliveryTime()));
//                item.put("createTime", FormatUtil.createTimeFormatList(p.getDeliveryTime()));
                    //支付方式
//                item.put("payType", OrderPayTypeEnum.valueOf(Checker.getIntegerValue(p.getPayType())).getDisplayName());
                    //支付流水号
                    info.setPaymentNo(Checker.getStringValue(p.getPaymentNo()));
                    info.setRefundTime(FormatUtil.createTimeFormatList(p.getRefundTime()));
//                item.put("paymentNo", Checker.getStringValue(p.getPaymentNo()));
//                item.put("refundTime", FormatUtil.createTimeFormatList(p.getRefundTime()));
                    Integer paymentStatus = p.getPaymentStatus();
                    info.setPayStatus(paymentStatus == null ? PaymentStatusEnum.Pending.getDisplayName() :
                            MealPaymentStatusEnum.valueOf(paymentStatus).getDisplayName());
//                item.put("payStatus", paymentStatus == null ? PaymentStatusEnum.Pending.getDisplayName() :
//                        PaymentStatusEnum.valueOf(paymentStatus).getDisplayName());
                    String extData = Checker.getStringValue(p.getExtData());
                    Map<String, Object> m = FormatUtil.stringToMapT(extData);
                    info.setRemark(m == null ? "" : (String) m.getOrDefault("refuseOrderRemark", ""));
//                item.put("remark", m == null ? "" : (String) m.getOrDefault("refuseOrderRemark", ""));
                    int hid = Checker.getIntegerValue(p.getHospitalId());
                    list.add(info);
//                item.put("hospitalId", hid);
//                item.put("hospitalName", BizUtil.getHospitalNameByHospitalId(hid));
//                item.put("pickupCode", Checker.getStringValue(p.getPickupCode()));
//                item.put("staffId", p.getStaffId());
//                item.put("deptName", Checker.getStringValue(p.getDeptName()));
//                item.put("extStatus", Checker.getIntegerValue(p.getExtStatus()));

//                String transactionIdStr = orderPaymentService.getTransactionIdByOrderNo(orderPaymentList, Checker.getStringValue(p.getOrderNo()));
//                item.put("transactionId", transactionIdStr);
                }
                if (!Checker.isNone(list)) {
                    List<OrderPayment> orderPaymentList = orderPaymentService.getOrderPaymentListByMealOrderListB(list);
                    // 得到退款的记录日志（需要从 refund_log 获取 refund_id 字段）
                    List<RefundLog> refundLogList = refundLogService.getRefundLogListByMealOrderList(list);
                    List<MealOrderInfo> refundList = new ArrayList<>();
                    Date billStartTime = DateUtil.stringToDate(request.getBillStartTime() + ConstantDef.BILL_TIME_START_FORMAT);
                    Date billEndTime = DateUtil.stringToDate(request.getBillEndTime() + ConstantDef.BILL_TIME_END_FORMAT);
                    for (MealOrderInfo p : list) {
                        if (PaymentStatusEnum.Success.getDisplayName().equals(p.getPayStatus())
                                && Checker.getIntegerValue(p.getWechatInt()) > 0
                                && !Checker.isNone(p.getPaymentTime())) {
                            // "交易类型*", "系统参考号*", "交易金额*", "交易日期*", "订单号", "类型"
                            Date paymentTime = DateUtil.stringToDate(p.getPaymentTime() + ConstantDef.BILL_TIME_LIST_FORMAT);
                            if (!Checker.isNone(paymentTime) && paymentTime.after(billStartTime) && paymentTime.before(billEndTime)) {
                                // 交易类型: 已支付，已退款，先处理已支付的，再处理已退款的
                                String type = ConstantDef.BILL_PAYMENT_STR;
                                // 交易流水号
                                String transactionId = "";
                                if (!Checker.isNone(orderPaymentList)) {
                                    OrderPayment orderPayment = orderPaymentList.stream().filter(o -> p.getOrderNo().equals(Checker.getStringValue(o.getOrderNo()))).findFirst().orElse(null);
                                    if (orderPayment != null && !Checker.isNone(orderPayment.getTransactionId())) {
                                        transactionId = Checker.getStringValue(orderPayment.getTransactionId());
                                    }
                                }
                                // 交易金额
                                String totalStr = FormatUtil.intShrink100ToStr(p.getWechatInt());
                                // 交易时间
                                String paymentTimeStr = FormatUtil.createTimeFormatDetail(p.getPaymentTime());
                                // 订单号
                                String orderNo = Checker.getStringValue(p.getOrderNo());
                                // 类型
                                String typeName = "营养餐";

                                String[] bodyStr = {type, transactionId, totalStr, paymentTimeStr, orderNo, typeName};
                                map.getStringListSafely(col0).add(type);
                                map.getStringListSafely(col1).add(transactionId);
                                map.getStringListSafely(col2).add(totalStr);
                                map.getStringListSafely(col3).add(paymentTimeStr);
                                map.getStringListSafely(col4).add(orderNo);
                                map.getStringListSafely(col5).add(typeName);
//                            ExcelHelper.settingMainBodyLabelCell(sheet, cellFormat2, n, bodyStr);
//                            n++;
                                refundList.addAll(getRefundMealOrderList(list));
                            }
                        }
                    }
                    for (MealOrderInfo p : refundList) {
                        if (!Checker.isNone(p) && !Checker.isNone(p.getRefundTime()) && Checker.getIntegerValue(p.getWechatInt()) > 0) {
                            // "交易类型*", "系统参考号*", "交易金额*", "交易日期*", "订单号", "类型"
                            Date refundTime = DateUtil.stringToDate(p.getRefundTime() + ConstantDef.BILL_TIME_LIST_FORMAT);
                            if (!Checker.isNone(refundTime) && refundTime.after(billStartTime) && refundTime.before(billEndTime)) {
                                String type = ConstantDef.BILL_REFUND_STR;
                                // 交易流水号
                                String transactionId = "";
                                RefundLog refundLog = refundLogList.stream().filter(o -> p.getPaymentNo().equals(o.getOutOrderNo())).findFirst().orElse(null);
                                if (refundLog != null && !Checker.isNone(refundLog.getRefundId())) {
                                    transactionId = refundLog.getRefundId();
                                }
                                // 交易金额
                                String totalStr = FormatUtil.intShrink100ToStr(p.getWechatInt());
                                // 交易时间
                                String refundTimeStr = FormatUtil.createTimeFormatDetail(p.getRefundTime());
                                // 订单号
                                String orderNo = Checker.getStringValue(p.getOrderNo());
                                // 类型
                                String typeName = "营养餐";

                                String[] bodyStr = {type, transactionId, totalStr, refundTimeStr, orderNo, typeName};
//                            ExcelHelper.settingMainBodyLabelCell(sheet, cellFormat2, n, bodyStr);
//                            n++;
                                map.getStringListSafely(col0).add(type);
                                map.getStringListSafely(col1).add(transactionId);
                                map.getStringListSafely(col2).add(totalStr);
                                map.getStringListSafely(col3).add(refundTimeStr);
                                map.getStringListSafely(col4).add(orderNo);
                                map.getStringListSafely(col5).add(typeName);
                            }
                        }
                    }

                }
            }
            int size = list.size();
            List<ExcelCollectPojo> itemList = new ArrayList<>();
            downloadRecordService.createFileAndUploadOssAndSaveToDataBase(fileName, downloadRecordId, map, itemList);

        } catch (Exception e) {
            logger.error("/mealOrder/downloadMealOrderBillsList(): {}", e.getMessage(), e);
            e.printStackTrace();
        }

    }

    private List<MealOrderInfo> getRefundMealOrderList(List<MealOrderInfo> list) {
        if (!Checker.isNone(list)) {
            return list.stream().filter(p -> !Checker.isNone(p.getRefundTime())).collect(Collectors.toList());
        }
        return new ArrayList<>();
    }
}