package com.ywt.mg.services;

import com.ywt.mg.core.utils.Checker;
import com.ywt.mg.core.utils.DateUtil;
import com.ywt.mg.core.utils.FormatUtil;
import com.ywt.mg.domain.entities.NatOrder;
import com.ywt.mg.domain.entities.OrderPayment;
import com.ywt.mg.domain.entities.RefundLog;
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.pojo.ExcelCollectPojo;
import com.ywt.mg.domain.ywtDrugEntities.Pharmacy;
import com.ywt.mg.domain.ywtDrugEntities.PrescriptionDrugView;
import com.ywt.mg.domain.ywtDrugEntities.PrescriptionInfo;
import com.ywt.mg.params.prescription.QueryPrescriptionListRequest;
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 PrescriptionServices {

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


    @Autowired
    private JdbcTemplate ywtDrugJdbcTemplate;

    @Autowired
    private OrderPaymentService orderPaymentService;

    @Autowired
    private PharmacyCacheService pharmacyCacheService;

    @Autowired
    private CommonServices commonServices;

    @Autowired
    private DownloadRecordService downloadRecordService;

    @Autowired
    private RefundLogService refundLogService;

    @Autowired
    private AuthService authService;

    /**
     * 根据条件查询用药记录
     *
     * @param request
     * @return
     */
    public List<PrescriptionInfo> downloadPrescriptionlistNew(QueryPrescriptionListRequest request) {
        Map<String, Object> map = getQueryMapCommonNew(request);
        List<Object> paramList = (List<Object>) map.get("paramList");
        String whereSql = (String) map.get("whereSql");

        // 分页查找
        StringBuilder sqlBuilder = new StringBuilder();
        // 拼凑sql语句
        sqlBuilder.append("select * from ywt_drug.prescription_info where ")
                .append(whereSql)
                .append("order by create_on desc");
        List<PrescriptionInfo> list = ywtDrugJdbcTemplate.query(sqlBuilder.toString(), paramList.toArray(), new BeanPropertyRowMapper<>(PrescriptionInfo.class));
        return list;
    }


    /**
     * 抽取公共的sql查找语句
     */
    public Map<String, Object> getQueryMapCommonNew(QueryPrescriptionListRequest request) {
        List<Object> paramList = new ArrayList<>();
        String whereSql = " ( 1=1 )";

        String doctorname = request.getDoctorname();
        if (!Checker.isNull(doctorname)) {
            whereSql += " and ( doctor_name like ?)";
            paramList.add("%" + doctorname + "%");
        }

        String patientname = request.getPatientname();
        if (!Checker.isNull(patientname)) {
            whereSql += " and ( patient_name like ?)";
            paramList.add("%" + patientname + "%");
        }
        String deptname = request.getDeptname();
        if (!Checker.isNull(deptname)) {
            whereSql += " and ( dept like ?)";
            paramList.add("%" + deptname + "%");
        }
        String source = request.getSource();
        if (!Checker.isNull(source)) {
            whereSql += " and ( source = ?)";
            paramList.add(Integer.parseInt(source));
        }
        String pharmacyId = request.getPharmacyId();
        if (!Checker.isNull(pharmacyId)) {
            whereSql += " and ( pharmacy_id = ?)";
            paramList.add(Integer.parseInt(pharmacyId));
        }

        String deliveryMethod = request.getDeliveryMethod();
        if (!Checker.isNull(deliveryMethod)) {
            int deliveryInt = Integer.parseInt(deliveryMethod);
            if (deliveryInt == DeliveryMethodEnum.SelfPickUp.getValue()) {
                whereSql += " and ( (delivery_method = ? ) or (delivery_method is null and source in (1,4,5) ))";
            } else {
                whereSql += " and ( delivery_method = ?)";
            }

            paramList.add(Integer.parseInt(deliveryMethod));
        }

        // 1待支付，2-配送中/待取药，3、已完成(不包括已评价的),4、已退费，5、不通过 （存在ext_status字段），6、已评价， 7-已过期，8-待评价, 10-退费中
        String status = request.getStatus();
        if (!Checker.isNone(status)) {
            int statusInt = Integer.parseInt(status);
            if (statusInt == 1) {
                // 只查找待支付的，不查找已过期的，需要计算时间
                //把日期往后前推3天.整数往后推,负数往前移动
                Date date = DateUtil.getRelativeDate(new Date(), ConstantDef.OVERDUE_DAY_NUM);
                whereSql += " and ( status = ? and create_on > ? ) ";
                paramList.add(statusInt);
                paramList.add(date);
            } else if (statusInt == 2) {
                // 查找只是配送中/待取药 （不包含不通过的）
                whereSql += " and (refund_status is null or refund_status = 2) and ( ((ext_status & ?) != ? or ext_status is null) && status = ?  ) ";
                paramList.add(PrescriptionInfoExtStatusEnum.PresNoPass.getValue());
                paramList.add(PrescriptionInfoExtStatusEnum.PresNoPass.getValue());
                paramList.add(PrescriptionInfoStatusEnum.Dispatching.getValue());
            } else if (statusInt == 3) {
                // 查找只是已完成的，只有广三的有已完成的状态
                whereSql += " and ( status = ? ) and (refund_status is null or refund_status = 2) and pres_type = 1 ";
                paramList.add(PrescriptionInfoStatusEnum.Finished.getValue());

            } else if (statusInt == 5) {
                // 查找只是不通过的，退费的不找出来，已评价的也不找出来
                whereSql += " and ( (ext_status & ?) = ? and ext_status < ? and status != ? ) ";
                paramList.add(PrescriptionInfoExtStatusEnum.PresNoPass.getValue());
                paramList.add(PrescriptionInfoExtStatusEnum.PresNoPass.getValue());
                paramList.add(2 * PrescriptionInfoExtStatusEnum.PresNoPass.getValue());
                paramList.add(PrescriptionInfoStatusEnum.HaveARefund.getValue());
            } else if (statusInt == 6) {
                // 查找已评价的
                whereSql += " and ( (ext_status & ?) = ? and status != ? and pharmacy_id = ? ) ";
                paramList.add(PrescriptionInfoExtStatusEnum.Evaluate.getValue());
                paramList.add(PrescriptionInfoExtStatusEnum.Evaluate.getValue());
                paramList.add(PrescriptionInfoStatusEnum.HaveARefund.getValue());
                paramList.add(ConstantDef.TAIHE_PHARMACY_ID);
            } else if (statusInt == 7) {
                // 根据原型图，订单3天未支付，状态变为已过期
                //把日期往后前推3天.整数往后推,负数往前移动
                Date date = DateUtil.getRelativeDate(new Date(), ConstantDef.OVERDUE_DAY_NUM);
                whereSql += " and ( status = ? or (status = ? and create_on < ?)) ";
                paramList.add(PrescriptionInfoStatusEnum.EXPIRED.getValue());
                paramList.add(PrescriptionInfoStatusEnum.WaitForPayment.getValue());
                paramList.add(date);
            } else if (statusInt == 8) {
                // 查找只是待评价的, 顺丰返回完成状态，需要排除不通过的
                whereSql += " and ( (ext_status & ?) != ? and status = ?  and (ext_status & ?) != ? and pharmacy_id = ?  ) ";
                paramList.add(PrescriptionInfoExtStatusEnum.Evaluate.getValue());
                paramList.add(PrescriptionInfoExtStatusEnum.Evaluate.getValue());
                paramList.add(PrescriptionInfoStatusEnum.Finished.getValue());
                paramList.add(PrescriptionInfoExtStatusEnum.PresNoPass.getValue());
                paramList.add(PrescriptionInfoExtStatusEnum.PresNoPass.getValue());
                paramList.add(ConstantDef.TAIHE_PHARMACY_ID);
            } else if (statusInt == 10) {
                whereSql += " and ( refund_status = ? ) ";
                paramList.add(RefundStatusEnum.PENDING.getValue());
            } else {
                whereSql += " and ( status = ? ) and ( refund_status != ?  or refund_status is null )   ";
                paramList.add(statusInt);
                paramList.add(RefundStatusEnum.PENDING.getValue());
            }
        }

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

        String preBizNo = request.getPreBizNo();
        if (!Checker.isNone(preBizNo)) {
            whereSql += " and ( pre_biz_no like ? ) ";
            paramList.add("%" + preBizNo + "%");
        }
        String bizNo3th = request.getBizNo3th();
        if (!Checker.isNone(bizNo3th)) {
            whereSql += " and ( biz_no_3th like ? ) ";
            paramList.add("%" + bizNo3th + "%");
        }
        String sendNo = request.getSendNo();
        if (!Checker.isNone(sendNo)) {
            whereSql += " and ( send_no like ? ) ";
            paramList.add("%" + sendNo + "%");
        }
        String paymentChannel = request.getPaymentChannel();
        if (!Checker.isNone(paymentChannel)) {
            whereSql += " and ( payment_channel = ? ) ";
            paramList.add(Integer.parseInt(paymentChannel));
        }

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

        String cancelStatus = request.getCancelStatus();
        if (!Checker.isNone(cancelStatus)) {
            whereSql += " and ( cancel_status = ? ) ";
            paramList.add(Integer.parseInt(cancelStatus));
        }

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

        String type = Checker.getStringValue(request.getType());
        if (!Checker.isNone(type)) {
            int typeInt = Integer.parseInt(type);
            whereSql += " and ( pres_type = ? )";
            paramList.add(typeInt);

        }

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

        String createTimeEnd = Checker.getStringValue(request.getCreateTimeEnd());
        String createTimeStart = Checker.getStringValue(request.getCreateTimeStart());
        String payTimeEnd = Checker.getStringValue(request.getPayTimeEnd());
        String payTimeStart = Checker.getStringValue(request.getPayTimeStart());
        String refundTimeEnd = Checker.getStringValue(request.getRefundTimeEnd());
        String refundTimeStart = Checker.getStringValue(request.getRefundTimeStart());
        String billEndTime = Checker.getStringValue(request.getBillEndTime());
        String billStartTime = Checker.getStringValue(request.getBillStartTime());
        try {
            // 创建时间
            if (!Checker.isNull(createTimeEnd)) {
                whereSql += " and ( create_on < ?)";
                Date date = format.parse(createTimeEnd);
                //把日期往后增加一天.整数往后推,负数往前移动
                Calendar calendar = new GregorianCalendar();
                calendar.setTime(date);
                calendar.add(calendar.DATE, 1);
                date = calendar.getTime();   //这个时间就是日期往后推一天的结果

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

            // 支付时间
            if (!Checker.isNull(payTimeEnd)) {
                whereSql += " and ( pay_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.isNull(payTimeStart)) {
                whereSql += " and ( pay_time >= ?)";
                Date date = format.parse(payTimeStart);
                paramList.add(date);
            }

            // 退费时间搜索
            if (!Checker.isNull(refundTimeEnd)) {
                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.isNull(refundTimeStart)) {
                whereSql += " and ( refund_time >= ?)";
                Date date = format.parse(refundTimeStart);
                paramList.add(date);
            }
            // 对账时间搜索
            if (!Checker.isNull(billEndTime)) {
                whereSql += " and ( pay_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.isNull(billStartTime)) {
                whereSql += " and ( pay_time >= ? or refund_time >= ?)";
                Date date = format.parse(billStartTime);
                paramList.add(date);
                paramList.add(date);
            }

        } catch (Exception e) {
            e.printStackTrace();
            logger.error("getQueryMapCommonNew():createTimeEnd:{}\t,createTimeStart:{}\t,payTimeEnd:{}\t,payTimeStart:{}\t," +
                            "refundTimeEnd:{}\t,refundTimeStart:{}\t,billEndTime:{}\t,billStartTime:{}\t", createTimeEnd, createTimeStart, payTimeEnd,
                    payTimeStart, refundTimeEnd, refundTimeStart, billEndTime, billStartTime);
        }

        String transactionId = request.getTransactionId();
        try {
            // 处理交易流水号，考虑到性能，直接采用全匹配到方式
            if (!Checker.isNull(transactionId)) {
                OrderPayment orderPayment = orderPaymentService.getOrderPaymentByTransactionId(transactionId);
                if (!Checker.isNone(orderPayment)) {
                    whereSql += " and ( order_id = ? )";
                    paramList.add(orderPayment.getOrderId());
                } else {
                    whereSql += " and ( order_id = -1 )";
                }
            }
        } catch (Exception e) {
            logger.error("PrescriptionServices#getQueryMapCommonNew(transactionId={} ):\n {} ", transactionId, e.getMessage(), e);
        }
        Map map = new HashMap();
        map.put("whereSql", whereSql);
        map.put("paramList", paramList);
        return map;
    }


    public void downloadPrescriptionlist(int downloadRecordId, String fileName, QueryPrescriptionListRequest request) {
        try {
            String col0 = "订单号";
            String col1 = "支付流水号";
            String col20 = "交易流水号";
            String col2 = "取药方式";
            String col3 = "医生姓名";
            String col4 = "患者姓名";
            String col5 = "来源";
            String col6 = "药房";
            String col7 = "状态";
            String col8 = "支付状态";
            String col9 = "支付渠道";
            String col10 = "总金额";
            String col19 = "处方类型";
            String col11 = "创建时间";
            String col12 = "支付时间";
            String col13 = "退款时间";
            String col14 = "退款状态";
            String col15 = "退费流水号";
            String col16 = "备注";
            String col17 = "作废状态";
            String col18 = "作废备注";

            String[] columns = new String[]{col0, col1, col20, col2, col3, col4, col5, col6, col7, col8, col9, col10, col19,
                    col11, col12, col13, col14, col15, col16, col17, col18};
            int payTotal = 0, refundTotal = 0;
            Map<String, Integer> statisticalDataMap = new HashMap<>();
            List<PrescriptionInfo> prescriptionInfoList = downloadPrescriptionlistNew(request);
            ExcelDataMap map = new ExcelDataMap(columns);
            if (!Checker.isNone(prescriptionInfoList)) {
                List<OrderPayment> orderPaymentList = orderPaymentService.getOrderPaymentListByPrescriptionInfoList(prescriptionInfoList);
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm");

                Map<Integer, Pharmacy> pharmacyMap = pharmacyCacheService.getPharmacyMap();
                for (PrescriptionInfo p : prescriptionInfoList) {

                    map.getStringListSafely(col0).add(p.getOrderNo());
                    map.getStringListSafely(col1).add(p.getPaymentNo());
                    int deliveryMethodInt = Checker.getIntegerValue(p.getDeliveryMethod());
                    String transactionId = orderPaymentService.getTransactionIdByOrderId(orderPaymentList, Checker.getIntegerValue(p.getOrderId()));
                    map.getStringListSafely(col20).add(transactionId);
                    String deliveryMethodStr =  getAdverDeliveryMethodStr(deliveryMethodInt);
                    map.getStringListSafely(col2).add(deliveryMethodStr);
                    map.getStringListSafely(col3).add(p.getDoctorName());
                    map.getStringListSafely(col4).add(p.getPatientName());
                    String sourceStr = PrescriptionInfoSourceEnum.getSource(p.getSource()).getDisplayName();
                    map.getStringListSafely(col5).add(sourceStr);
                    Pharmacy pharmacy = pharmacyMap.get(Checker.getIntegerValue(p.getPharmacyId()));
                    String pharmacyName = !Checker.isNone(pharmacy) ? pharmacy.getName() : "";
                    String statusStr = getPrescriptionInfoStatusStr(p);
                    map.getStringListSafely(col6).add(pharmacyName);
                    map.getStringListSafely(col7).add(statusStr);
                    map.getStringListSafely(col8).add(PaymentStatusEnum.getPaymentStatus(p.getPaymentStatus()).getDisplayName());
                    map.getStringListSafely(col9).add(PresPaymentChannelEnum.valueOf(Checker.getIntegerValue(p.getPaymentChannel())).getDisplayName());
                    String total = String.format("%.2f元", (p.getTotalPrice() / 100d));
                    if(p.getStatus() == 6){
//                        total = "0.00元";
                    }
                    map.getStringListSafely(col10).add(total);
                    map.getStringListSafely(col19).add(Checker.getIntegerValue(p.getPresType()) == 1 ? "处方外转" : "互联网医院");
                    map.getStringListSafely(col11).add(sdf.format(p.getCreateOn()));
                    String payTime = !Checker.isNone(p.getPayTime()) ? sdf.format(p.getPayTime()) : "";
                    map.getStringListSafely(col12).add(payTime);
                    String refundTime = !Checker.isNone(p.getRefundTime()) ? sdf.format(p.getRefundTime()) : "";
                    String refundStatus = Checker.isNone(p.getRefundStatus()) ? "" : RefundStatusEnum.getDisplayName(p.getRefundStatus());
                    map.getStringListSafely(col13).add(refundTime);
                    map.getStringListSafely(col14).add(refundStatus);
                    map.getStringListSafely(col15).add(Checker.getStringValue(p.getRefundNo()));
                    map.getStringListSafely(col16).add(Checker.getStringValue(p.getRemarks()));
                    int cancelStatusInt = Checker.getIntegerValue(p.getCancelStatus());
                    String cancelStatusStr = getPrescriptionInfoCancelStatusStr(cancelStatusInt);
                    map.getStringListSafely(col17).add(cancelStatusStr);
                    map.getStringListSafely(col18).add(Checker.getStringValue(p.getCancelRemark()));

                    //统计支付总额和退费总额
                    int paymentStatusInt = Checker.getIntegerValue(p.getPaymentStatus());
                    int presStatus = Checker.getIntegerValue(p.getStatus());
                    int payChanelInt = Checker.getIntegerValue(p.getPaymentChannel());

                    String payKey = ConstantDef.PAY_KEY_PREFIX + payChanelInt;
                    String refundKey = ConstantDef.REFUND_KEY_PREFIX + payChanelInt;
                    int payValue = statisticalDataMap.getOrDefault(payKey, 0);
                    int refundValue = statisticalDataMap.getOrDefault(refundKey, 0);
                    int amountInt = Checker.getIntegerValue(p.getTotalPrice());
                    if (Checker.getIntegerValue(p.getPaymentStatus()) == PaymentStatusEnum.Success.getValue()) {
                        payTotal += amountInt;
                        payValue += amountInt;
                        statisticalDataMap.put(payKey, payValue);
                    }
                    if (Checker.getIntegerValue(p.getRefundStatus()) == RefundStatusEnum.SUCCESS.getValue()) {
                        refundTotal += amountInt;
                        refundValue += amountInt;
                        statisticalDataMap.put(refundKey, refundValue);
                    }

                }

            }
            int size = prescriptionInfoList.size();
            int bill = 0;
            List<ExcelCollectPojo> itemList = getStatisticsData(size, payTotal, refundTotal, statisticalDataMap);
            downloadRecordService.createFileAndUploadOssAndSaveToDataBase(fileName, downloadRecordId, map, itemList, size, bill);


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

    /**
     * 得到处方的配送方法
     *
     * @param value 1-快递，2-自取
     * @return 中文状态
     */
    private String getDeliveryMethodStr(int value) {
        if (value == 1) {
            return "快递";
        }
        if (value == 2) {
            return "自取";
        }
        return "";
    }


    /*
     * v4.7.0 所有历史外转处方为邮寄
     **
     */
    private String getAdverDeliveryMethodStr(int value) {
        if (value == 1) {
            return "快递";
        }
        if (value == 2 || value == 0) {
            return "自取";
        }
        return "";
    }

    /**
     * 公司后台：得到处方状态
     * <p>
     * pharmacyId ：1 - 医院生殖中心1楼药房（广三），2-南方医院太和分院药房（太和）
     *
     * @param p {@link PrescriptionInfo}
     * @return 处方状态
     */
    public String getPrescriptionInfoStatusStr(PrescriptionInfo p) {
        return commonServices.getCompanyPrescriptionStatusStr(p);
    }

    /**
     * 0-初始状态，1-作废申请中，2-已作废，3-作废失败
     *
     * @param cancelStatusInt 作废状态（数字）
     * @return 作废状态
     */
    public String getPrescriptionInfoCancelStatusStr(int cancelStatusInt) {
        switch (cancelStatusInt) {
            case 1:
                return "作废申请中";
            case 2:
                return "已作废";
            case 3:
                return "作废失败";
            default:
                return "";
        }
    }

    private List<ExcelCollectPojo> getStatisticsData(int size, int payTotal, int refundTotal, Map<String, Integer> statisticalDataMap) {
        int startRows = size + 3;
        int startColumn = 0;

        List<ExcelCollectPojo> customExcelItemList = new ArrayList<>();
        customExcelItemList.add(new ExcelCollectPojo(startColumn + 1, startRows + 1, new String[]{"支付总额", FormatUtil.intShrink100ToStr(payTotal)}));
        customExcelItemList.add(new ExcelCollectPojo(startColumn + 3, startRows + 1, new String[]{"退款总额", FormatUtil.intShrink100ToStr(refundTotal)}));
        customExcelItemList.add(new ExcelCollectPojo(startColumn + 5, startRows + 1, new String[]{"实际支付总额", FormatUtil.intShrink100ToStr(payTotal - refundTotal)}));
        PaymentChannelEnum[] paymentChannelList = PaymentChannelEnum.values();
        startRows++;
        if (!Checker.isNone(paymentChannelList)) {
            for (int i = 0, j = paymentChannelList.length; i < j; i++) {
                int paymentChannel = paymentChannelList[i].getValue();
                if (paymentChannel <= 0) continue;
                String paymentChannelName = paymentChannelList[i].getDisplayName();
                String payKey = ConstantDef.PAY_KEY_PREFIX + paymentChannel;
                String refundKey = ConstantDef.REFUND_KEY_PREFIX + paymentChannel;
                // 按支付渠道统计
                int payValue = statisticalDataMap.getOrDefault(payKey, 0);
                int refundValue = statisticalDataMap.getOrDefault(refundKey, 0);
                if (payValue > 0 || refundValue > 0) {
                    startRows += 1;
                    customExcelItemList.add(new ExcelCollectPojo(startColumn + 1, startRows, new String[]{paymentChannelName + "支付总额", FormatUtil.intShrink100ToStr(payValue)}));
                    customExcelItemList.add(new ExcelCollectPojo(startColumn + 3, startRows, new String[]{paymentChannelName + "退款总额", FormatUtil.intShrink100ToStr(refundValue)}));
                    customExcelItemList.add(new ExcelCollectPojo(startColumn + 5, startRows, new String[]{paymentChannelName + "实际支付总额", FormatUtil.intShrink100ToStr(payValue - refundValue)}));
                }
            }
        }

        return customExcelItemList;
    }


    public List<PrescriptionDrugView> getPrescriptionDrugView(QueryPrescriptionListRequest request) {
        Map<String, Object> map = getQueryMapCommon(request);
        List<Object> paramList = (List<Object>) map.get("paramList");
        String whereSql = (String) map.get("whereSql");

        StringBuilder sqlBuilder = new StringBuilder();
        // 拼凑sql语句
        sqlBuilder.append("select * from ywt_drug.prescription_drug_view where ")
                .append(whereSql)
                .append("order by id desc");
        List<PrescriptionDrugView> list = ywtDrugJdbcTemplate.query(sqlBuilder.toString(), paramList.toArray(), new BeanPropertyRowMapper<>(PrescriptionDrugView.class));
        return list;
    }

    public Map<String, Object> getQueryMapCommon(QueryPrescriptionListRequest request) {
        List<Object> paramList = new ArrayList<>();
        String whereSql = " ( 1=1 )";

        String doctorname = request.getDoctorname();
        if (!Checker.isNull(doctorname)) {
            whereSql += " and ( doctor_name like ?)";
            paramList.add("%" + doctorname + "%");
        }


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

        String deptname = request.getDeptname();
        if (!Checker.isNull(deptname)) {
            whereSql += " and ( dept like ?)";
            paramList.add("%" + deptname + "%");
        }

        String source = request.getSource();
        if (!Checker.isNull(source)) {
            whereSql += " and ( source = ?)";
            paramList.add(Integer.parseInt(source));
        }
        String pharmacyId = request.getPharmacyId();
        if (!Checker.isNull(pharmacyId)) {
            whereSql += " and ( pharmacy_id = ?)";
            paramList.add(Integer.parseInt(pharmacyId));
        }

        String deliveryMethod = request.getDeliveryMethod();
        if (!Checker.isNull(deliveryMethod)) {
            whereSql += " and ( delivery_method = ?)";
            paramList.add(Integer.parseInt(deliveryMethod));
        }

        // 1待支付，2-配送中/待取药，3、已完成(不包括已评价的),4、已退费，5、不通过 （存在ext_status字段），6、已评价， 7-已过期，8-待评价, 10-退费中
        String status = request.getStatus();
        if (!Checker.isNone(status)) {
            int statusInt = Integer.parseInt(status);
            if (statusInt == 1) {
                // 只查找待支付的，不查找已过期的，需要计算时间
                //把日期往后前推3天.整数往后推,负数往前移动
                Date date = DateUtil.getRelativeDate(new Date(), ConstantDef.OVERDUE_DAY_NUM);
                whereSql += " and ( status = ? and create_on > ? ) ";
                paramList.add(statusInt);
                paramList.add(date);
            } else if (statusInt == 2) {
                // 查找只是配送中/待取药 （不包含不通过的）
                whereSql += " and (refund_status is null or refund_status = 2) and ( ((ext_status & ?) != ? or ext_status is null) && status = ?  ) ";
                paramList.add(PrescriptionInfoExtStatusEnum.PresNoPass.getValue());
                paramList.add(PrescriptionInfoExtStatusEnum.PresNoPass.getValue());
                paramList.add(PrescriptionInfoStatusEnum.Dispatching.getValue());
            } else if (statusInt == 3) {
                // 查找只是已完成的，只有广三的有已完成的状态
                whereSql += " and ( status = ? ) and (refund_status is null or refund_status = 2) and pres_type = 1 ";
                paramList.add(PrescriptionInfoStatusEnum.Finished.getValue());

            } else if (statusInt == 5) {
                // 查找只是不通过的，退费的不找出来，已评价的也不找出来
                whereSql += " and ( (ext_status & ?) = ? and ext_status < ? and status != ? ) ";
                paramList.add(PrescriptionInfoExtStatusEnum.PresNoPass.getValue());
                paramList.add(PrescriptionInfoExtStatusEnum.PresNoPass.getValue());
                paramList.add(2 * PrescriptionInfoExtStatusEnum.PresNoPass.getValue());
                paramList.add(PrescriptionInfoStatusEnum.HaveARefund.getValue());
            } else if (statusInt == 6) {
                // 查找已评价的
                whereSql += " and ( (ext_status & ?) = ? and status != ? and pharmacy_id = ? ) ";
                paramList.add(PrescriptionInfoExtStatusEnum.Evaluate.getValue());
                paramList.add(PrescriptionInfoExtStatusEnum.Evaluate.getValue());
                paramList.add(PrescriptionInfoStatusEnum.HaveARefund.getValue());
                paramList.add(ConstantDef.TAIHE_PHARMACY_ID);
            } else if (statusInt == 7) {
                // 根据原型图，订单3天未支付，状态变为已过期
                //把日期往后前推3天.整数往后推,负数往前移动
                Date date = DateUtil.getRelativeDate(new Date(), ConstantDef.OVERDUE_DAY_NUM);
                whereSql += " and ( status = ? or (status = ? and create_on < ?)) ";
                paramList.add(PrescriptionInfoStatusEnum.EXPIRED.getValue());
                paramList.add(PrescriptionInfoStatusEnum.WaitForPayment.getValue());
                paramList.add(date);
            } else if (statusInt == 8) {
                // 查找只是待评价的, 顺丰返回完成状态，需要排除不通过的
                whereSql += " and ( (ext_status & ?) != ? and status = ?  and (ext_status & ?) != ? and pharmacy_id = ?  ) ";
                paramList.add(PrescriptionInfoExtStatusEnum.Evaluate.getValue());
                paramList.add(PrescriptionInfoExtStatusEnum.Evaluate.getValue());
                paramList.add(PrescriptionInfoStatusEnum.Finished.getValue());
                paramList.add(PrescriptionInfoExtStatusEnum.PresNoPass.getValue());
                paramList.add(PrescriptionInfoExtStatusEnum.PresNoPass.getValue());
                paramList.add(ConstantDef.TAIHE_PHARMACY_ID);
            } else {
                if (statusInt != 10) {
                    whereSql += " and ( status = ? ) ";
                    paramList.add(statusInt);
                }
            }

            // 退费中
            if (statusInt == 10) {
                whereSql += " and ( refund_status = ?  ) ";
                paramList.add(RefundStatusEnum.PENDING.getValue());
            } else {
                whereSql += " and ( refund_status != ?  ) ";
                paramList.add(RefundStatusEnum.PENDING.getValue());
            }

        }

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

        String preBizNo = request.getPreBizNo();
        if (!Checker.isNone(preBizNo)) {
            whereSql += " and ( pre_biz_no like ? ) ";
            paramList.add("%" + preBizNo + "%");
        }
        String bizNo3th = request.getBizNo3th();
        if (!Checker.isNone(bizNo3th)) {
            whereSql += " and ( biz_no_3th like ? ) ";
            paramList.add("%" + bizNo3th + "%");
        }
        String sendNo = request.getSendNo();
        if (!Checker.isNone(sendNo)) {
            whereSql += " and ( send_no like ? ) ";
            paramList.add("%" + sendNo + "%");
        }
        String paymentChannel = request.getPaymentChannel();
        if (!Checker.isNone(paymentChannel)) {
            whereSql += " and ( payment_channel = ? ) ";
            paramList.add(Integer.parseInt(paymentChannel));
        }

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

        String cancelStatus = request.getCancelStatus();
        if (!Checker.isNone(cancelStatus)) {
            whereSql += " and ( cancel_status = ? ) ";
            paramList.add(Integer.parseInt(cancelStatus));
        }

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

        String type = Checker.getStringValue(request.getType());
        if (!Checker.isNone(type)) {
            int typeInt = Integer.parseInt(type);
            whereSql += " and ( pres_type = ? )";
            paramList.add(typeInt);

        }

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

        String createTimeEnd = request.getCreateTimeEnd();
        String createTimeStart = request.getCreateTimeStart();
        String payTimeEnd = request.getPayTimeEnd();
        String payTimeStart = request.getPayTimeStart();
        String refundTimeEnd = request.getRefundTimeEnd();
        String refundTimeStart = request.getRefundTimeStart();

        try {
            // 创建时间
            if (!Checker.isNull(createTimeEnd)) {
                whereSql += " and ( create_on < ?)";
                Date date = format.parse(createTimeEnd);
                //把日期往后增加一天.整数往后推,负数往前移动
                Calendar calendar = new GregorianCalendar();
                calendar.setTime(date);
                calendar.add(calendar.DATE, 1);
                date = calendar.getTime();   //这个时间就是日期往后推一天的结果

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

            // 支付时间
            if (!Checker.isNull(payTimeEnd)) {
                whereSql += " and ( pay_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.isNull(payTimeStart)) {
                whereSql += " and ( pay_time >= ?)";
                Date date = format.parse(payTimeStart);
                paramList.add(date);
            }

            // 退费时间搜索
            if (!Checker.isNull(refundTimeEnd)) {
                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.isNull(refundTimeStart)) {
                whereSql += " and ( refund_time >= ?)";
                Date date = format.parse(refundTimeStart);
                paramList.add(date);
            }
            // 对账时间搜索


        } catch (Exception e) {
            e.printStackTrace();
        }
        Map map = new HashMap();
        map.put("whereSql", whereSql);
        map.put("paramList", paramList);
        return map;
    }

    public void downloadPrescriptionDrugList(int downloadRecordId, String fileName, QueryPrescriptionListRequest request) {
        try {
            String col0 = "订单号";
            String col1 = "药品名";
            String col2 = "规格";
            String col3 = "数量";
            String col4 = "单价";
            String col5 = "总价";
            String col6 = "支付时间";
            String col7 = "退费时间";
            String col8 = "药房";
            String[] columns = new String[]{col0, col1, col2, col3, col4, col5, col6, col7, col8};
            List<PrescriptionDrugView> list = getPrescriptionDrugView(request);
            ExcelDataMap map = new ExcelDataMap(columns);
            if (!Checker.isNone(list)) {
                Map<Integer, Pharmacy> ma = pharmacyCacheService.getPharmacyMap();
                for (PrescriptionDrugView d : list) {
                    String orderNo = Checker.getStringValue(d.getOrderNo());
                    String drugName = Checker.getStringValue(d.getDrugName());
                    String spec = Checker.getStringValue(d.getDrugSpec());
                    String quan = Checker.getStringValue(d.getQuantity() + "");
                    String price = FormatUtil.intShrink100ToStr(d.getPrice());
                    String total = FormatUtil.intShrink100ToStr(d.getTotal());
                    String payTime = FormatUtil.createTimeFormatDetail(d.getPayTime());
                    String refundTime = FormatUtil.createTimeFormatDetail(d.getRefundTime());
                    //药房
                    Pharmacy pharmacy = ma.getOrDefault(Checker.getIntegerValue(d.getPharmacyId()), null);
                    String pharmacyName = "";
                    if (!Checker.isNone(pharmacy)) {
                        pharmacyName = Checker.getStringValue(pharmacy.getName());
                    }
                    map.getStringListSafely(col0).add(orderNo);
                    map.getStringListSafely(col1).add(drugName);
                    map.getStringListSafely(col2).add(spec);
                    map.getStringListSafely(col3).add(quan);
                    map.getStringListSafely(col4).add(price);
                    map.getStringListSafely(col5).add(total);
                    map.getStringListSafely(col6).add(payTime);
                    map.getStringListSafely(col7).add(refundTime);
                    map.getStringListSafely(col8).add(pharmacyName);


                }

            }
            int size = list.size();
            List<ExcelCollectPojo> itemList = new ArrayList<>();
            int bill = 0;
            downloadRecordService.createFileAndUploadOssAndSaveToDataBase(fileName, downloadRecordId, map, itemList, size, bill);
        } catch (Exception e) {
            logger.error("/prescription/downloadPrescriptionDrugList(): {}", e.getMessage(), e);
            e.printStackTrace();
        }
    }

    public void downloadPrescriptionBillList(int downloadRecordId, String fileName, QueryPrescriptionListRequest request) {
        try {
            String col0 = "交易类型*";
            String col1 = "系统参考号*";
            String col2 = "交易金额*";
            String col3 = "交易日期*";
            String col4 = "订单号";
            String col5 = "类型";
            String col6 = "支付渠道";
            String[] columns = new String[]{col0, col1, col2, col3, col4, col5, col6};
            request.setPaymentStatus(PaymentStatusEnum.Success.getValue() + "");
            request.setPaymentChannel(PaymentChannelEnum.WeChat.getValue() + "");
            List<PrescriptionInfo> prescriptionInfoList = downloadPrescriptionlistNew(request);
            ExcelDataMap map = new ExcelDataMap(columns);
            Map<String, Integer> statisticalDataMap = new HashMap<>();
            int payTotal = 0, refundTotal = 0, rowSize = 0;
            if (!Checker.isNone(prescriptionInfoList)) {

                // 得到支付的记录日志（需要从 order_payment 获取 transaction_id 字段）
                List<OrderPayment> orderPaymentList = orderPaymentService.getOrderPaymentListByPrescriptionInfoList(prescriptionInfoList);
                // 得到退款的记录日志（需要从 refund_log 获取 refund_id 字段）
                Date billStartTime = DateUtil.stringToDate(request.getBillStartTime() + ConstantDef.BILL_TIME_START_FORMAT);
                Date billEndTime = DateUtil.stringToDate(request.getBillEndTime() + ConstantDef.BILL_TIME_END_FORMAT);
                for (PrescriptionInfo p : prescriptionInfoList) {
                    if (!Checker.isNone(p) && Checker.getIntegerValue(p.getPaymentStatus()) == PaymentStatusEnum.Success.getValue()
                            && Checker.getIntegerValue(p.getPaymentChannel()) == PaymentChannelEnum.WeChat.getValue()
                            && !Checker.isNone(p.getPayTime())) {
                        // "交易类型", "交易流水号", "交易金额", "交易时间", "订单号", "类型"
                        Date paymentTime = Checker.getDateValue(p.getPayTime());
                        if (!Checker.isNone(paymentTime) && paymentTime.after(billStartTime) && paymentTime.before(billEndTime)) {
                            String type = ConstantDef.BILL_PAYMENT_STR;
                            // 交易流水号
                            String transactionId = orderPaymentService.getTransactionIdByOrderId(orderPaymentList, Checker.getIntegerValue(p.getOrderId()));
                            // 交易金额
                            int total = Checker.getIntegerValue(p.getTotalPrice()) + Checker.getIntegerValue(p.getFreight());
                            String totalStr = FormatUtil.intShrink100ToStr(total);
                            // 交易时间
                            String paymentTimeStr = FormatUtil.createTimeFormatDetail(p.getPayTime());
                            // 订单号
                            String orderNo = Checker.getStringValue(p.getOrderNo());

                            int paymentChannelInt = Checker.getIntegerValue(p.getPaymentChannel());
                            String paymentChannel = PaymentChannelEnum.getDisplayName(paymentChannelInt);
                            // 类型
                            String typeStr = "处方";

                            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(typeStr);
                            map.getStringListSafely(col6).add(paymentChannel);
                            rowSize++;

                            String payKey = ConstantDef.PAY_KEY_PREFIX + paymentChannelInt;
                            int payValue = statisticalDataMap.getOrDefault(payKey, 0);
                            int amountInt = Checker.getIntegerValue(p.getTotalPrice());
                            if (Checker.getIntegerValue(p.getPaymentStatus()) == PaymentStatusEnum.Success.getValue()) {
                                payTotal += amountInt;
                                payValue += amountInt;
                                statisticalDataMap.put(payKey, payValue);
                            }

                        }
                    }
                }
                List<RefundLog> refundLogList = refundLogService.getRefundLogListByPrescriptionInfoList(prescriptionInfoList);
                // 得到支付的excel
                // 得到退款的处方订单
                List<PrescriptionInfo> refundPrescriptionInfoList = getRefundPrescriptionInfoList(prescriptionInfoList);
                // 最后处理退款的部分
                for (PrescriptionInfo p : refundPrescriptionInfoList) {
                    if (!Checker.isNone(p) && Checker.getIntegerValue(p.getRefundStatus()) == RefundStatusEnum.SUCCESS.getValue()) {
                        // "交易类型", "交易流水号", "交易金额", "交易时间", "订单号", "类型"
                        Date refundTime = Checker.getDateValue(p.getRefundTime());
                        if (!Checker.isNone(refundTime) && refundTime.after(billStartTime) && refundTime.before(billEndTime)) {
                            String type = ConstantDef.BILL_REFUND_STR;
                            String transactionId = refundLogService.getRefundIdByRefundNo(refundLogList, p.getRefundNo());
                            // 交易金额
                            int total = Checker.getIntegerValue(p.getTotalPrice()) + Checker.getIntegerValue(p.getFreight());
                            String totalStr = FormatUtil.intShrink100ToStr(total);
                            // 交易时间
                            String refundTimeStr = FormatUtil.createTimeFormatDetail(p.getRefundTime());
                            // 订单号
                            String orderNo = Checker.getStringValue(p.getOrderNo());

                            int paymentChannelInt = Checker.getIntegerValue(p.getPaymentChannel());
                            String paymentChannel = PaymentChannelEnum.getDisplayName(paymentChannelInt);
                            // 类型
                            String typeName = "处方";


                            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);
                            map.getStringListSafely(col6).add(paymentChannel);
                            rowSize++;

                            String refundKey = ConstantDef.REFUND_KEY_PREFIX + paymentChannelInt;
                            int refundValue = statisticalDataMap.getOrDefault(refundKey, 0);
                            int amountInt = Checker.getIntegerValue(p.getTotalPrice());
                            if (Checker.getIntegerValue(p.getRefundStatus()) == RefundStatusEnum.SUCCESS.getValue()) {
                                refundTotal += amountInt;
                                refundValue += amountInt;
                                statisticalDataMap.put(refundKey, refundValue);
                            }
                        }
                    }


                }
            }
            int size = prescriptionInfoList.size();
            List<ExcelCollectPojo> itemList = getStatisticsData(rowSize, payTotal, refundTotal, statisticalDataMap);
            int bill = 1;
            downloadRecordService.createFileAndUploadOssAndSaveToDataBase(fileName, downloadRecordId, map, itemList, size, bill);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private List<PrescriptionInfo> getRefundPrescriptionInfoList(List<PrescriptionInfo> items) {
        if (!Checker.isNone(items)) {
            return items.stream().filter(p -> Checker.getIntegerValue(p.getRefundStatus()) == RefundStatusEnum.SUCCESS.getValue()).collect(Collectors.toList());
        }
        return new ArrayList<>();
    }

    public void downloadAdverPrescriptionlist(int downloadRecordId, String fileName, QueryPrescriptionListRequest request) {
        try {
            String col0 = "订单号";
            String col1 = "支付流水号";
            String col20 = "交易流水号";
            String col2 = "取药方式";
            String col3 = "医生姓名";
            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[] columns = new String[]{col0, col1, col20,  col2, col3, col4, col6, col7, col8, col9, col10,
                    col11, col12, col13, col14, col15, col16, col17, col18};
            int payTotal = 0, refundTotal = 0;
            List<PrescriptionInfo> prescriptionInfoList = queryAbverPrescriptionListNew(request);
            Map<String, Integer> statisticalDataMap = new HashMap<>();
            ExcelDataMap map = new ExcelDataMap(columns);
            if (!Checker.isNone(prescriptionInfoList)) {
                List<OrderPayment> orderPaymentList = orderPaymentService.getOrderPaymentListByPrescriptionInfoList(prescriptionInfoList);
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm");

                Map<Integer, Pharmacy> pharmacyMap = pharmacyCacheService.getPharmacyMap();
                for (PrescriptionInfo p : prescriptionInfoList) {

                    map.getStringListSafely(col0).add(p.getOrderNo());
                    map.getStringListSafely(col1).add(p.getPaymentNo());
                    int deliveryMethodInt = Checker.getIntegerValue(p.getDeliveryMethod());
                    String deliveryMethodStr =  getAdverDeliveryMethodStr(deliveryMethodInt);
                    String transactionId = orderPaymentService.getTransactionIdByOrderId(orderPaymentList, Checker.getIntegerValue(p.getOrderId()));
                    map.getStringListSafely(col20).add(transactionId);
                    map.getStringListSafely(col2).add(deliveryMethodStr);
                    map.getStringListSafely(col3).add(p.getDoctorName());
                    map.getStringListSafely(col4).add(p.getPatientName());
                    String sourceStr = PrescriptionInfoSourceEnum.getSource(p.getSource()).getDisplayName();
//                    map.getStringListSafely(col5).add(sourceStr);
                    Pharmacy pharmacy = pharmacyMap.get(Checker.getIntegerValue(p.getPharmacyId()));
                    String pharmacyName = !Checker.isNone(pharmacy) ? pharmacy.getName() : "";
                    String statusStr = getPrescriptionInfoStatusStr(p);
                    map.getStringListSafely(col6).add(pharmacyName);
                    map.getStringListSafely(col7).add(statusStr);
                    map.getStringListSafely(col8).add(PaymentStatusEnum.getPaymentStatus(p.getPaymentStatus()).getDisplayName());
                    map.getStringListSafely(col9).add(PresPaymentChannelEnum.valueOf(Checker.getIntegerValue(p.getPaymentChannel())).getDisplayName());
                    String total = String.format("%.2f元", (p.getTotalPrice() / 100d));
                    if(p.getStatus() == 6){
//                        total = "0.00元";
                    }

                    map.getStringListSafely(col10).add(total);
                    map.getStringListSafely(col11).add(sdf.format(p.getCreateOn()));
                    String payTime = !Checker.isNone(p.getPayTime()) ? sdf.format(p.getPayTime()) : "";
                    map.getStringListSafely(col12).add(payTime);
                    String refundTime = !Checker.isNone(p.getRefundTime()) ? sdf.format(p.getRefundTime()) : "";
                    String refundStatus = Checker.isNone(p.getRefundStatus()) ? "" : RefundStatusEnum.getDisplayName(p.getRefundStatus());
                    map.getStringListSafely(col13).add(refundTime);
                    map.getStringListSafely(col14).add(refundStatus);
                    map.getStringListSafely(col15).add(Checker.getStringValue(p.getRefundNo()));
                    map.getStringListSafely(col16).add(Checker.getStringValue(p.getRemarks()));
                    int cancelStatusInt = Checker.getIntegerValue(p.getCancelStatus());
                    String cancelStatusStr = getPrescriptionInfoCancelStatusStr(cancelStatusInt);
                    map.getStringListSafely(col17).add(cancelStatusStr);
                    map.getStringListSafely(col18).add(Checker.getStringValue(p.getCancelRemark()));

                    //统计支付总额和退费总额
                    int paymentStatusInt = Checker.getIntegerValue(p.getPaymentStatus());
                    int presStatus = Checker.getIntegerValue(p.getStatus());
                    int payChanelInt = Checker.getIntegerValue(p.getPaymentChannel());

                    String payKey = ConstantDef.PAY_KEY_PREFIX + payChanelInt;
                    String refundKey = ConstantDef.REFUND_KEY_PREFIX + payChanelInt;
                    int payValue = statisticalDataMap.getOrDefault(payKey, 0);
                    int refundValue = statisticalDataMap.getOrDefault(refundKey, 0);
                    int amountInt = Checker.getIntegerValue(p.getTotalPrice());
                    if (Checker.getIntegerValue(p.getPaymentStatus()) == PaymentStatusEnum.Success.getValue()) {
                        payTotal += amountInt;
                        payValue += amountInt;
                        statisticalDataMap.put(payKey, payValue);
                    }
                    if (Checker.getIntegerValue(p.getRefundStatus()) == RefundStatusEnum.SUCCESS.getValue()) {
                        refundTotal += amountInt;
                        refundValue += amountInt;
                        statisticalDataMap.put(refundKey, refundValue);
                    }

                }

            }
            int size = prescriptionInfoList.size();
            List<ExcelCollectPojo> itemList = getStatisticsData(size, payTotal, refundTotal, statisticalDataMap);
            int bill = 0;
            downloadRecordService.createFileAndUploadOssAndSaveToDataBase(fileName, downloadRecordId, map, itemList, size, bill);


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


    public List<PrescriptionInfo> queryAbverPrescriptionListNew(QueryPrescriptionListRequest request) {
        Map<String, Object> map = getAbverQueryMapCommonNew(request);
        List<Object> paramList = (List<Object>) map.get("paramList");
        String whereSql = (String) map.get("whereSql");

        // 分页查找
        StringBuilder sqlBuilder = new StringBuilder();
        // 拼凑sql语句
        sqlBuilder.append("select * from ywt_drug.prescription_info where ")
                .append(whereSql)
                .append("order by create_on desc");
        List<PrescriptionInfo> list = ywtDrugJdbcTemplate.query(sqlBuilder.toString(), paramList.toArray(), new BeanPropertyRowMapper<>(PrescriptionInfo.class));
        return list;
    }


    public Map<String, Object> getAbverQueryMapCommonNew(QueryPrescriptionListRequest request) {
        List<Object> paramList = new ArrayList<>();
        String whereSql = " ( 1=1 )";

        whereSql += " and (  pres_type = 1)";

        String doctorname = request.getDoctorname();
        if (!Checker.isNull(doctorname)) {
            whereSql += " and ( doctor_name like ?)";
            paramList.add("%" + doctorname + "%");
        }

        String patientname = request.getPatientname();
        if (!Checker.isNull(patientname)) {
            whereSql += " and ( patient_name like ?)";
            paramList.add("%" + patientname + "%");
        }
        String deptname = request.getDeptname();
        if (!Checker.isNull(deptname)) {
            whereSql += " and ( dept like ?)";
            paramList.add("%" + deptname + "%");
        }

        int hospitalId = request.getHospitalId();
        if (hospitalId > 0) {
            whereSql += " and ( hospital_id = ?)";
            paramList.add(hospitalId);
        }

        if (hospitalId == ConstantDef.NFBY_HOSPITAL_ID) {
            whereSql += " and ( source = 5)";
        } else if (hospitalId == ConstantDef.TAIHE_HOSPITAL_ID) {
            whereSql += " and ( source = 4)";
        }

        String pharmacyId = request.getPharmacyId();
        if (!Checker.isNull(pharmacyId)) {
            whereSql += " and ( pharmacy_id = ?)";
            paramList.add(Integer.parseInt(pharmacyId));
        }


        String deliveryMethod = request.getDeliveryMethod();
        if (!Checker.isNull(deliveryMethod)) {
            int deliveryInt = Integer.parseInt(deliveryMethod);
            if (deliveryInt == DeliveryMethodEnum.SelfPickUp.getValue()) {
                whereSql += " and ( (delivery_method = ? ) or (delivery_method is null and source in (1,4,5) ))";
            } else {
                whereSql += " and ( delivery_method = ?)";
            }

            paramList.add(Integer.parseInt(deliveryMethod));
        }

        // 1待支付，2-配送中/待取药，3、已完成(不包括已评价的),4、已退费，5、不通过 （存在ext_status字段），6、已评价， 7-已过期，8-待评价, 10-退费中
        String status = request.getStatus();
        if (!Checker.isNone(status)) {
            int statusInt = Integer.parseInt(status);
            if (statusInt == 1) {
                // 只查找待支付的，不查找已过期的，需要计算时间
                //把日期往后前推3天.整数往后推,负数往前移动
                Date date = DateUtil.getRelativeDate(new Date(), ConstantDef.OVERDUE_DAY_NUM);
                whereSql += " and ( status = ? and create_on > ? ) ";
                paramList.add(statusInt);
                paramList.add(date);
            } else if (statusInt == 2) {
                // 查找只是配送中/待取药 （不包含不通过的）
                whereSql += " and (refund_status is null or refund_status = 2) and ( ((ext_status & ?) != ? or ext_status is null) && status = ?  ) ";
                paramList.add(PrescriptionInfoExtStatusEnum.PresNoPass.getValue());
                paramList.add(PrescriptionInfoExtStatusEnum.PresNoPass.getValue());
                paramList.add(PrescriptionInfoStatusEnum.Dispatching.getValue());
            } else if (statusInt == 3) {
                // 查找只是已完成的，只有广三的有已完成的状态
                whereSql += " and ( status = ? ) and (refund_status is null or refund_status = 2) and pres_type = 1 ";
                paramList.add(PrescriptionInfoStatusEnum.Finished.getValue());

            } else if (statusInt == 5) {
                // 查找只是不通过的，退费的不找出来，已评价的也不找出来
                whereSql += " and ( (ext_status & ?) = ? and ext_status < ? and status != ? ) ";
                paramList.add(PrescriptionInfoExtStatusEnum.PresNoPass.getValue());
                paramList.add(PrescriptionInfoExtStatusEnum.PresNoPass.getValue());
                paramList.add(2 * PrescriptionInfoExtStatusEnum.PresNoPass.getValue());
                paramList.add(PrescriptionInfoStatusEnum.HaveARefund.getValue());
            } else if (statusInt == 6) {
                // 查找已评价的
                whereSql += " and ( (ext_status & ?) = ? and status != ? and pharmacy_id = ? ) ";
                paramList.add(PrescriptionInfoExtStatusEnum.Evaluate.getValue());
                paramList.add(PrescriptionInfoExtStatusEnum.Evaluate.getValue());
                paramList.add(PrescriptionInfoStatusEnum.HaveARefund.getValue());
                paramList.add(ConstantDef.TAIHE_PHARMACY_ID);
            } else if (statusInt == 7) {
                // 根据原型图，订单3天未支付，状态变为已过期
                //把日期往后前推3天.整数往后推,负数往前移动
                Date date = DateUtil.getRelativeDate(new Date(), ConstantDef.OVERDUE_DAY_NUM);
                whereSql += " and ( status = ? or (status = ? and create_on < ?)) ";
                paramList.add(PrescriptionInfoStatusEnum.EXPIRED.getValue());
                paramList.add(PrescriptionInfoStatusEnum.WaitForPayment.getValue());
                paramList.add(date);
            } else if (statusInt == 8) {
                // 查找只是待评价的, 顺丰返回完成状态，需要排除不通过的
                whereSql += " and ( (ext_status & ?) != ? and status = ?  and (ext_status & ?) != ? and pharmacy_id = ?  ) ";
                paramList.add(PrescriptionInfoExtStatusEnum.Evaluate.getValue());
                paramList.add(PrescriptionInfoExtStatusEnum.Evaluate.getValue());
                paramList.add(PrescriptionInfoStatusEnum.Finished.getValue());
                paramList.add(PrescriptionInfoExtStatusEnum.PresNoPass.getValue());
                paramList.add(PrescriptionInfoExtStatusEnum.PresNoPass.getValue());
                paramList.add(ConstantDef.TAIHE_PHARMACY_ID);
            } else if (statusInt == 10) {
                whereSql += " and ( refund_status = ? ) ";
                paramList.add(RefundStatusEnum.PENDING.getValue());
            } else {
                whereSql += " and ( status = ? ) and ( refund_status != ?  or refund_status is null )   ";
                paramList.add(statusInt);
                paramList.add(RefundStatusEnum.PENDING.getValue());
            }
        }

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

        String preBizNo = request.getPreBizNo();
        if (!Checker.isNone(preBizNo)) {
            whereSql += " and ( pre_biz_no like ? ) ";
            paramList.add("%" + preBizNo + "%");
        }
        String bizNo3th = request.getBizNo3th();
        if (!Checker.isNone(bizNo3th)) {
            whereSql += " and ( biz_no_3th like ? ) ";
            paramList.add("%" + bizNo3th + "%");
        }
        String sendNo = request.getSendNo();
        if (!Checker.isNone(sendNo)) {
            whereSql += " and ( send_no like ? ) ";
            paramList.add("%" + sendNo + "%");
        }
        String paymentChannel = request.getPaymentChannel();
        if (!Checker.isNone(paymentChannel)) {
            whereSql += " and ( payment_channel = ? ) ";
            paramList.add(Integer.parseInt(paymentChannel));
        }

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

        String cancelStatus = request.getCancelStatus();
        if (!Checker.isNone(cancelStatus)) {
            whereSql += " and ( cancel_status = ? ) ";
            paramList.add(Integer.parseInt(cancelStatus));
        }

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

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

        String createTimeEnd = request.getCreateTimeEnd();
        String createTimeStart = request.getCreateTimeStart();
        String payTimeEnd = request.getPayTimeEnd();
        String payTimeStart = request.getPayTimeStart();
        String refundTimeEnd = request.getRefundTimeEnd();
        String refundTimeStart = request.getRefundTimeStart();
        String billEndTime = request.getBillEndTime();
        String billStartTime = request.getBillStartTime();
        try {
            // 创建时间
            if (!Checker.isNull(createTimeEnd)) {
                whereSql += " and ( create_on < ?)";
                Date date = format.parse(createTimeEnd);
                //把日期往后增加一天.整数往后推,负数往前移动
                Calendar calendar = new GregorianCalendar();
                calendar.setTime(date);
                calendar.add(calendar.DATE, 1);
                date = calendar.getTime();   //这个时间就是日期往后推一天的结果

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

            // 支付时间
            if (!Checker.isNull(payTimeEnd)) {
                whereSql += " and ( pay_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.isNull(payTimeStart)) {
                whereSql += " and ( pay_time >= ?)";
                Date date = format.parse(payTimeStart);
                paramList.add(date);
            }

            // 退费时间搜索
            if (!Checker.isNull(refundTimeEnd)) {
                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.isNull(refundTimeStart)) {
                whereSql += " and ( refund_time >= ?)";
                Date date = format.parse(refundTimeStart);
                paramList.add(date);
            }
            // 对账时间搜索
            if (!Checker.isNull(billEndTime)) {
                whereSql += " and ( pay_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.isNull(billStartTime)) {
                whereSql += " and ( pay_time >= ? or refund_time >= ?)";
                Date date = format.parse(billStartTime);
                paramList.add(date);
                paramList.add(date);
            }

        } catch (Exception e) {
            e.printStackTrace();
            logger.error("getQueryMapCommonNew():createTimeEnd:{}\t,createTimeStart:{}\t,payTimeEnd:{}\t,payTimeStart:{}\t," +
                            "refundTimeEnd:{}\t,refundTimeStart:{}\t,billEndTime:{}\t,billStartTime:{}\t", createTimeEnd, createTimeStart, payTimeEnd,
                    payTimeStart, refundTimeEnd, refundTimeStart, billEndTime, billStartTime);
        }

        String transactionId = request.getTransactionId();
        try {
            // 处理交易流水号，考虑到性能，直接采用全匹配到方式
            if (!Checker.isNull(transactionId)) {
                OrderPayment orderPayment = orderPaymentService.getOrderPaymentByTransactionId(transactionId);
                if (!Checker.isNone(orderPayment)) {
                    whereSql += " and ( order_id = ? )";
                    paramList.add(orderPayment.getOrderId());
                } else {
                    whereSql += " and ( order_id = -1 )";
                }
            }
        } catch (Exception e) {
            logger.error("PrescriptionServices#getQueryMapCommonNew(transactionId={} ):\n {} ", transactionId, e.getMessage(), e);
        }
        Map map = new HashMap();
        map.put("whereSql", whereSql);
        map.put("paramList", paramList);
        return map;
    }


    public void downloadAdverPrescriptionDrugList(int downloadRecordId, String fileName, QueryPrescriptionListRequest request) {
        try {
            String col0 = "订单号";
            String col1 = "药品名";
            String col2 = "规格";
            String col3 = "数量";
            String col4 = "单价";
            String col5 = "总价";
            String col6 = "支付时间";
            String col7 = "退费时间";
            String col8 = "药房";
            String[] columns = new String[]{col0, col1, col2, col3, col4, col5, col6, col7, col8};
            List<PrescriptionDrugView> list = getAdverPrescriptionDrugView(request);
            ExcelDataMap map = new ExcelDataMap(columns);
            int payTotal = 0, refundTotal = 0, realTotal = 0,
                    weChatPay = 0, weChatRefundTotal = 0, weChatRealTotal = 0,
                    aliPay = 0, aliRefundTotal = 0, aliRealTotal = 0,
                    cashPay = 0, cashRefundTotal = 0, cashRealTotal = 0,
                    medicalCardPay = 0, medicalCardRefundTotal = 0, medicalCardRealTotal = 0,
                    posPay = 0, posRefundTotal = 0, posRealTotal = 0,
                    medicalInsurancePay = 0, medicalInsuranceRefundTotal = 0, medicalInsuranceRealTotal = 0,
                    icbcPay = 0, icbcRefundTotal = 0, icbcRealTotal = 0,
                    pharmacyPosPay = 0, pharmacyPosRefundTotal = 0, pharmacyPosTotal = 0;
            if (!Checker.isNone(list)) {
                Map<Integer, Pharmacy> ma = pharmacyCacheService.getPharmacyMap();
                for (PrescriptionDrugView d : list) {
                    String orderNo = Checker.getStringValue(d.getOrderNo());
                    String drugName = Checker.getStringValue(d.getDrugName());
                    String spec = Checker.getStringValue(d.getDrugSpec());
                    String quan = Checker.getStringValue(d.getQuantity() + "");
                    String price = FormatUtil.intShrink100ToStr(d.getPrice());
                    String total = FormatUtil.intShrink100ToStr(d.getTotal());
                    String payTime = FormatUtil.createTimeFormatDetail(d.getPayTime());
                    String refundTime = FormatUtil.createTimeFormatDetail(d.getRefundTime());
                    //药房
                    Pharmacy pharmacy = ma.getOrDefault(Checker.getIntegerValue(d.getPharmacyId()), null);
                    String pharmacyName = "";
                    if (!Checker.isNone(pharmacy)) {
                        pharmacyName = Checker.getStringValue(pharmacy.getName());
                    }
                    map.getStringListSafely(col0).add(orderNo);
                    map.getStringListSafely(col1).add(drugName);
                    map.getStringListSafely(col2).add(spec);
                    map.getStringListSafely(col3).add(quan);
                    map.getStringListSafely(col4).add(price);
                    map.getStringListSafely(col5).add(total);
                    map.getStringListSafely(col6).add(payTime);
                    map.getStringListSafely(col7).add(refundTime);
                    map.getStringListSafely(col8).add(pharmacyName);


                }

            }
            int size = list.size();
            List<ExcelCollectPojo> itemList = new ArrayList<>();
            int bill = 0;
            downloadRecordService.createFileAndUploadOssAndSaveToDataBase(fileName, downloadRecordId, map, itemList, size, bill);
        } catch (Exception e) {
            logger.error("/prescription/downloadPrescriptionDrugList(): {}", e.getMessage(), e);
            e.printStackTrace();
        }
    }

    public List<PrescriptionDrugView> getAdverPrescriptionDrugView(QueryPrescriptionListRequest request) {
        Map<String, Object> map = getAdverQueryMapCommon(request);
        List<Object> paramList = (List<Object>) map.get("paramList");
        String whereSql = (String) map.get("whereSql");

        StringBuilder sqlBuilder = new StringBuilder();
        // 拼凑sql语句
        sqlBuilder.append("select * from ywt_drug.prescription_drug_view where ")
                .append(whereSql)
                .append("order by id desc");
        List<PrescriptionDrugView> list = ywtDrugJdbcTemplate.query(sqlBuilder.toString(), paramList.toArray(), new BeanPropertyRowMapper<>(PrescriptionDrugView.class));
        return list;
    }


    public Map<String, Object> getAdverQueryMapCommon(QueryPrescriptionListRequest request) {
        List<Object> paramList = new ArrayList<>();
        String whereSql = " ( 1=1 )";

        whereSql += " and ( pres_type = 1 )";

        String doctorname = request.getDoctorname();
        if (!Checker.isNull(doctorname)) {
            whereSql += " and ( doctor_name like ?)";
            paramList.add("%" + doctorname + "%");
        }


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

        String deptname = request.getDeptname();
        if (!Checker.isNull(deptname)) {
            whereSql += " and ( dept like ?)";
            paramList.add("%" + deptname + "%");
        }

        String source = request.getSource();
        if (!Checker.isNull(source)) {
            whereSql += " and ( source = ?)";
            paramList.add(Integer.parseInt(source));
        }



        int hospitalId = request.getHospitalId();
        if (hospitalId == ConstantDef.NFBY_HOSPITAL_ID) {
            whereSql += " and ( source = 5)";
        } else if (hospitalId == ConstantDef.TAIHE_HOSPITAL_ID) {
            whereSql += " and ( source = 4)";
        }


        String pharmacyId = request.getPharmacyId();
        if (!Checker.isNull(pharmacyId)) {
            whereSql += " and ( pharmacy_id = ?)";
            paramList.add(Integer.parseInt(pharmacyId));
        }


        String deliveryMethod = request.getDeliveryMethod();
        if (!Checker.isNull(deliveryMethod)) {
            whereSql += " and ( delivery_method = ?)";
            paramList.add(Integer.parseInt(deliveryMethod));
        }

        // 1待支付，2-配送中/待取药，3、已完成(不包括已评价的),4、已退费，5、不通过 （存在ext_status字段），6、已评价， 7-已过期，8-待评价, 10-退费中
        String status = request.getStatus();
        if (!Checker.isNone(status)) {
            int statusInt = Integer.parseInt(status);
            if (statusInt == 1) {
                // 只查找待支付的，不查找已过期的，需要计算时间
                //把日期往后前推3天.整数往后推,负数往前移动
                Date date = DateUtil.getRelativeDate(new Date(), ConstantDef.OVERDUE_DAY_NUM);
                whereSql += " and ( status = ? and create_on > ? ) ";
                paramList.add(statusInt);
                paramList.add(date);
            } else if (statusInt == 2) {
                // 查找只是配送中/待取药 （不包含不通过的）
                whereSql += " and (refund_status is null or refund_status = 2) and ( ((ext_status & ?) != ? or ext_status is null) && status = ?  ) ";
                paramList.add(PrescriptionInfoExtStatusEnum.PresNoPass.getValue());
                paramList.add(PrescriptionInfoExtStatusEnum.PresNoPass.getValue());
                paramList.add(PrescriptionInfoStatusEnum.Dispatching.getValue());
            } else if (statusInt == 3) {
                // 查找只是已完成的，只有广三的有已完成的状态
                whereSql += " and ( status = ? ) and (refund_status is null or refund_status = 2) and pres_type = 1 ";
                paramList.add(PrescriptionInfoStatusEnum.Finished.getValue());

            } else if (statusInt == 5) {
                // 查找只是不通过的，退费的不找出来，已评价的也不找出来
                whereSql += " and ( (ext_status & ?) = ? and ext_status < ? and status != ? ) ";
                paramList.add(PrescriptionInfoExtStatusEnum.PresNoPass.getValue());
                paramList.add(PrescriptionInfoExtStatusEnum.PresNoPass.getValue());
                paramList.add(2 * PrescriptionInfoExtStatusEnum.PresNoPass.getValue());
                paramList.add(PrescriptionInfoStatusEnum.HaveARefund.getValue());
            } else if (statusInt == 6) {
                // 查找已评价的
                whereSql += " and ( (ext_status & ?) = ? and status != ? and pharmacy_id = ? ) ";
                paramList.add(PrescriptionInfoExtStatusEnum.Evaluate.getValue());
                paramList.add(PrescriptionInfoExtStatusEnum.Evaluate.getValue());
                paramList.add(PrescriptionInfoStatusEnum.HaveARefund.getValue());
                paramList.add(ConstantDef.TAIHE_PHARMACY_ID);
            } else if (statusInt == 7) {
                // 根据原型图，订单3天未支付，状态变为已过期
                //把日期往后前推3天.整数往后推,负数往前移动
                Date date = DateUtil.getRelativeDate(new Date(), ConstantDef.OVERDUE_DAY_NUM);
                whereSql += " and ( status = ? or (status = ? and create_on < ?)) ";
                paramList.add(PrescriptionInfoStatusEnum.EXPIRED.getValue());
                paramList.add(PrescriptionInfoStatusEnum.WaitForPayment.getValue());
                paramList.add(date);
            } else if (statusInt == 8) {
                // 查找只是待评价的, 顺丰返回完成状态，需要排除不通过的
                whereSql += " and ( (ext_status & ?) != ? and status = ?  and (ext_status & ?) != ? and pharmacy_id = ?  ) ";
                paramList.add(PrescriptionInfoExtStatusEnum.Evaluate.getValue());
                paramList.add(PrescriptionInfoExtStatusEnum.Evaluate.getValue());
                paramList.add(PrescriptionInfoStatusEnum.Finished.getValue());
                paramList.add(PrescriptionInfoExtStatusEnum.PresNoPass.getValue());
                paramList.add(PrescriptionInfoExtStatusEnum.PresNoPass.getValue());
                paramList.add(ConstantDef.TAIHE_PHARMACY_ID);
            } else {
                if (statusInt != 10) {
                    whereSql += " and ( status = ? ) ";
                    paramList.add(statusInt);
                }
            }

            // 退费中
            if (statusInt == 10) {
                whereSql += " and ( refund_status = ?  ) ";
                paramList.add(RefundStatusEnum.PENDING.getValue());
            } else {
                whereSql += " and ( refund_status != ?  ) ";
                paramList.add(RefundStatusEnum.PENDING.getValue());
            }

        }

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

        String preBizNo = request.getPreBizNo();
        if (!Checker.isNone(preBizNo)) {
            whereSql += " and ( pre_biz_no like ? ) ";
            paramList.add("%" + preBizNo + "%");
        }
        String bizNo3th = request.getBizNo3th();
        if (!Checker.isNone(bizNo3th)) {
            whereSql += " and ( biz_no_3th like ? ) ";
            paramList.add("%" + bizNo3th + "%");
        }
        String sendNo = request.getSendNo();
        if (!Checker.isNone(sendNo)) {
            whereSql += " and ( send_no like ? ) ";
            paramList.add("%" + sendNo + "%");
        }
        String paymentChannel = request.getPaymentChannel();
        if (!Checker.isNone(paymentChannel)) {
            whereSql += " and ( payment_channel = ? ) ";
            paramList.add(Integer.parseInt(paymentChannel));
        }

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

        String cancelStatus = request.getCancelStatus();
        if (!Checker.isNone(cancelStatus)) {
            whereSql += " and ( cancel_status = ? ) ";
            paramList.add(Integer.parseInt(cancelStatus));
        }

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

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

        String createTimeEnd = request.getCreateTimeEnd();
        String createTimeStart = request.getCreateTimeStart();
        String payTimeEnd = request.getPayTimeEnd();
        String payTimeStart = request.getPayTimeStart();
        String refundTimeEnd = request.getRefundTimeEnd();
        String refundTimeStart = request.getRefundTimeStart();

        try {
            // 创建时间
            if (!Checker.isNull(createTimeEnd)) {
                whereSql += " and ( create_on < ?)";
                Date date = format.parse(createTimeEnd);
                //把日期往后增加一天.整数往后推,负数往前移动
                Calendar calendar = new GregorianCalendar();
                calendar.setTime(date);
                calendar.add(calendar.DATE, 1);
                date = calendar.getTime();   //这个时间就是日期往后推一天的结果

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

            // 支付时间
            if (!Checker.isNull(payTimeEnd)) {
                whereSql += " and ( pay_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.isNull(payTimeStart)) {
                whereSql += " and ( pay_time >= ?)";
                Date date = format.parse(payTimeStart);
                paramList.add(date);
            }

            // 退费时间搜索
            if (!Checker.isNull(refundTimeEnd)) {
                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.isNull(refundTimeStart)) {
                whereSql += " and ( refund_time >= ?)";
                Date date = format.parse(refundTimeStart);
                paramList.add(date);
            }
            // 对账时间搜索


        } catch (Exception e) {
            e.printStackTrace();
        }
        Map map = new HashMap();
        map.put("whereSql", whereSql);
        map.put("paramList", paramList);
        return map;
    }
}
