package com.yd.api.agms.service.impl;

import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import com.google.common.base.Joiner;
import com.yd.api.agms.service.AgmsFortuneService;
import com.yd.api.agms.vo.fortune.*;
import com.yd.api.order.vo.SurrenderFortuneRequestVO;
import com.yd.api.order.vo.SurrenderFortuneResponseVO;
import com.yd.api.result.CommonResult;
import com.yd.dal.entity.agms.fortune.*;
import com.yd.dal.entity.customer.*;
import com.yd.dal.entity.customer.practitioner.PractitionerSubordinateInfo;
import com.yd.dal.entity.meta.MdIncometaxRate;
import com.yd.dal.entity.order.PoOrder;
import com.yd.dal.entity.product.Product;
import com.yd.dal.entity.product.ProductPlan;
import com.yd.dal.entity.user.AclUser;
import com.yd.dal.service.agms.AgmsFortuneDALService;
import com.yd.dal.service.customer.*;
import com.yd.dal.service.order.PoOrderDALService;
import com.yd.dal.service.product.ProductDALService;
import com.yd.dal.service.product.ProductPlanDALService;
import com.yd.dal.service.user.AclUserDALService;
import com.yd.rmi.ali.send.service.SendService;
import com.yd.rmi.cache.SystemConfigService;
import com.yd.util.CommonUtil;
import com.yd.util.config.ZHBErrorConfig;
import org.apache.commons.beanutils.BeanPropertyValueEqualsPredicate;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.util.*;

/**
 * @author xxy
 */
@Service("agmsFortuneService")
public class AgmsFortuneServiceImpl implements AgmsFortuneService {

    private AclCustomerFortuneDALService customerFortuneDalService;
    private AclCustomerFortuneWithdrawDALService customerFortuneWithdrawDalService;
    private AclCustomerFortunePayDALService customerFortunePayDalService;
    private AgmsFortuneDALService agmsFortuneDalService;
    private SystemConfigService systemConfigService;
    private AclCustomerFortunePayoutBatchDALService customerFortunePayoutBatchDalService;
    private AclCustomerFortuneStatisticDALService customerFortuneStatisticDalService;
    private AclUserDALService userDalService;
    @Autowired
    private AclPractitionerSubordinateSystemDALService aclPractitionerSubordinateSystemDalService;

    @Autowired
    public void setAclCustomerFortuneDalService(AclCustomerFortuneDALService customerFortuneDalService) {
        this.customerFortuneDalService = customerFortuneDalService;
    }

    @Autowired
    public void setAclCustomerFortuneWithdrawDalService(AclCustomerFortuneWithdrawDALService customerFortuneWithdrawDalService) {
        this.customerFortuneWithdrawDalService = customerFortuneWithdrawDalService;
    }

    @Autowired
    public void setCustomerFortunePayDalService(AclCustomerFortunePayDALService customerFortunePayDalService) {
        this.customerFortunePayDalService = customerFortunePayDalService;
    }

    @Autowired
    public void setAgmsFortuneDalService(AgmsFortuneDALService agmsFortuneDalService) {
        this.agmsFortuneDalService = agmsFortuneDalService;
    }

    @Autowired
    public void setSystemConfigService(SystemConfigService systemConfigService) {
        this.systemConfigService = systemConfigService;
    }

    @Autowired
    public void setAclCustomerFortunePayoutBatchDALService(AclCustomerFortunePayoutBatchDALService customerFortunePayoutBatchDalService) {
        this.customerFortunePayoutBatchDalService = customerFortunePayoutBatchDalService;
    }

    @Autowired
    public void setAclUserDALService(AclUserDALService userDalService) {
        this.userDalService = userDalService;
    }

    @Autowired
    public void setAclCustomerFortuneStatisticDALService(AclCustomerFortuneStatisticDALService customerFortuneStatisticDalService) {
        this.customerFortuneStatisticDalService = customerFortuneStatisticDalService;
    }

    @Autowired
    private PoOrderDALService poOrderDALService;
    @Autowired
    private ProductDALService productDALService;
    @Autowired
    private ProductPlanDALService productPlanDALService;
    @Autowired
    private AclPolicyholderService aclPolicyholderService;
    @Autowired
    private SendService sendService;
    @Autowired
    private AclPractitionerDALService aclPractitionerDALService;

    @Override
    @Transactional(rollbackFor = Exception.class)
    public CommissionPayoutStatusUpdateResponseVO commissionPayoutStatusUpdate(CommissionPayoutStatusUpdateRequestVO requestVO) {
        CommissionPayoutStatusUpdateResponseVO responseVO = new CommissionPayoutStatusUpdateResponseVO();
        CommonResult commonResult = check(requestVO);
        Long[] fortuneIds = requestVO.getFortuneIds();
        //2.    0.作废   1.待来佣   2.可发放   3.保留
        String paymentStatus = requestVO.getPaymentStatus();
        Long loginId = requestVO.getLoginId();
        //查询出需修改的佣金发放状态的财富
        List<AclCustomerFortune> customerFortunes = customerFortuneDalService.findByIds(fortuneIds);

        //已作废的记录不能修改状态
        for (AclCustomerFortune customerFortune : customerFortunes) {
            if ("0".equals(customerFortune.getStatus())) {
                responseVO.setCommonResult(new CommonResult(false, ZHBErrorConfig.getErrorInfo("830039")));
                return responseVO;
            }
        }

        //佣金预计发放批次
        String payoutBatch = requestVO.getPayoutBatch();
        //通过payoutBatch查询此批次是否再库中,在库中直接获取id,不在进行保存,获取id
        Long payoutBatchId = findPayoutBatchIdByPayoutBatch(payoutBatch, loginId);

        if (("2").equals(paymentStatus)) {
            //如paymentStatus = 2(可发放),只需统计Fortune对应Customer最后一笔未支付的Withdraw,重新计算,如没有Withdraw生成一条新的Withdraw记录
            canPaymentUpDate(paymentStatus, payoutBatchId, loginId, customerFortunes);
        } else {
            //如paymentStatus != 2(暂不可发,保留),只需统计原Fortune对应的Withdraw,重新计算
            noPaymentUpDate(paymentStatus, payoutBatchId, loginId, customerFortunes);
        }

        responseVO.setCommonResult(commonResult);
        return responseVO;
    }

    private CommonResult check(CommissionPayoutStatusUpdateRequestVO requestVO) {
        CommonResult commonResult = new CommonResult();
        boolean success = true;
        String message = ZHBErrorConfig.getErrorInfo("800000");
        if (("2").equals(requestVO.getPaymentStatus())) {
            if (CommonUtil.isNullOrBlank(requestVO.getPayoutBatch())) {
                message = ZHBErrorConfig.getErrorInfo("830021");
                success = false;
            }
            commonResult.setSuccess(success);
            commonResult.setMessage(message);
            return commonResult;
        }
        Long[] fortuneIds = requestVO.getFortuneIds();
        List<AclCustomerFortuneWithdraw> withdraws = customerFortuneWithdrawDalService.findByFortuneIds(fortuneIds);
        for (AclCustomerFortuneWithdraw withdraw : withdraws) {
            if (withdraw.getIsPaid() == 1) {
                message = ZHBErrorConfig.getErrorInfo("830024");
                commonResult.setSuccess(false);
                commonResult.setMessage(message);
                return commonResult;
            }
        }
        commonResult.setSuccess(success);
        commonResult.setMessage(message);
        return commonResult;
    }

    private Long findPayoutBatchIdByPayoutBatch(String payoutBatch, Long loginId) {
        if (CommonUtil.isNullOrBlank(payoutBatch)) {
            return null;
        }
        AclCustomerFortunePayoutBatch customerFortunePayoutBatch = customerFortunePayoutBatchDalService.findByPayoutYearmonth(payoutBatch);
        if (customerFortunePayoutBatch == null) {
            customerFortunePayoutBatch = new AclCustomerFortunePayoutBatch();
            customerFortunePayoutBatch.setPayoutYearmonth(payoutBatch);
            customerFortunePayoutBatch.setCreatedBy(loginId);
            customerFortunePayoutBatch.setUpdatedBy(loginId);
            customerFortunePayoutBatch.setCreatedAt(new Date());
            customerFortunePayoutBatch.setUpdatedAt(new Date());
            customerFortunePayoutBatchDalService.save(customerFortunePayoutBatch);
        }
        return customerFortunePayoutBatch.getId();
    }

    @Override
    public void canPaymentUpDate(String paymentStatus, Long payoutBatchId, Long loginId, List<AclCustomerFortune> customerFortunes) {
        System.out.println("canPaymentUpDate");

        //将查询出来的财富列表根据customerId经行分类
        Map<Long, List<AclCustomerFortune>> customerFortuneMap = changeCustomerFortunesByFieldName(customerFortunes, "customerId");
        //获得所有需要修改佣金发放状的customerIds
        Set<Long> customerIds = customerFortuneMap.keySet();
        //可直接修改的withdraw记录
        Map<Long, Long> customerWithdrawMap = directModificationCustomerWithdrawMap(customerIds, payoutBatchId);

        //没有最后一笔提现  记录客户信息和此次提现的财富记录  customerId  fortunes
        Map<Long, List<AclCustomerFortune>> newCustomerWithdrawMap = new HashMap<>(16);
        //可直接修改的fortune记录(需修改佣金发放状态)
        List<AclCustomerFortune> fortuneUpdates = new ArrayList<>();
        //记录可直接修改的fortune对应的withdraw
        List<Long> withdrawUpdateIds = new ArrayList<>();
        for (Map.Entry<Long, List<AclCustomerFortune>> customerFortune : customerFortuneMap.entrySet()) {
            //获取需要修改佣金发放状态的经纪人
            Long customerId = customerFortune.getKey();
            //此customer最后一笔提现记录id
            Long withdrawId = customerWithdrawMap.get(customerId);
            //新提现的记录
            List<AclCustomerFortune> newFortunes = new ArrayList<>();
            //获取休要修改的佣金发放状态的财富记录
            List<AclCustomerFortune> fortunes = customerFortune.getValue();
            //判断这些财富记录中佣金发放状态和要修改的状态是否一致,如一致,则不需要修改
            for (AclCustomerFortune fortune : fortunes) {
                //不一致,进行修改,并保存需修改的fortune和记录需要新生成的fortune
                if (!paymentStatus.equals(fortune.getStatus())) {
                    fortune.setCommissionPayoutStatus(paymentStatus);
                    fortune.setStatus("2");
                    fortune.setCommissionPayoutBy(loginId);
                    fortune.setCommissionPayoutAt(new Date());
                    fortune.setPayoutBatchId(payoutBatchId);
                    if (CommonUtil.isNullOrZero(withdrawId)) {
                        //需生成新的withdraw
                        newFortunes.add(fortune);
                    } else {
                        //可直接修改fortune
                        fortune.setWithdrawedId(withdrawId);
                        fortuneUpdates.add(fortune);
                        withdrawUpdateIds.add(withdrawId);
                    }
                } else {
                    //paymentStatus一致,都是可发放,判断其支付批次,是否一致,一致不需要修改,不一致需要修改两笔withdraw
                    if (!payoutBatchId.equals(fortune.getPayoutBatchId())) {
                        //订单原来所属withdraw
                        withdrawUpdateIds.add(fortune.getWithdrawedId());
                        fortune.setCommissionPayoutAt(new Date());
                        fortune.setCommissionPayoutBy(loginId);
                        fortune.setPayoutBatchId(payoutBatchId);
                        fortune.setStatus("2");
                        fortune.setCommissionPayoutStatus("2");
                        if (CommonUtil.isNullOrZero(withdrawId)) {
                            //需生成新的withdraw
                            newFortunes.add(fortune);
                            fortune.setWithdrawedId(null);
                            fortuneUpdates.add(fortune);
                        } else {
                            //可直接修改fortune
                            fortune.setWithdrawedId(withdrawId);
                            fortuneUpdates.add(fortune);
                            withdrawUpdateIds.add(withdrawId);
                        }
                    }
                }
            }
            //记录客户信息和此次提现的财富记录
            if (!newFortunes.isEmpty()) {
                newCustomerWithdrawMap.put(customerId, newFortunes);
            }
        }

        //批量更新需要修改的fortune记录
        customerFortuneDalService.updateBatch(fortuneUpdates);
        try {
            Thread.sleep(100);
        } catch (InterruptedException e) {
            e.printStackTrace();
        }
        //重新计算受到影响的withdraw
        withdrawUpdateIds.removeAll(Collections.singleton(null));
        if (!withdrawUpdateIds.isEmpty()) {
            Collections.sort(withdrawUpdateIds);
        }
        recalculateWithdraw(withdrawUpdateIds, customerIds, loginId);
        //更新生成新的提现记录,及支付记录,fortune中记录对应的withdraw.id
        insertWithdraw(newCustomerWithdrawMap, loginId);
    }

    private Map<Long, Long> directModificationCustomerWithdrawMap(Set<Long> customerIds, Long payoutBatchId) {
        //查询这些customerId是这个批次的最后一笔未完成的提现记录(ag_acl_customer_fortune_withdraw)
        Map<Long, Long> customerWithdrawNoPayMap = customerFortuneWithdrawDalService.findFinalWithdrawNoPayByCustomerIds(customerIds, payoutBatchId);
        //查询这些customerId最后一笔已完成的提现记录(ag_acl_customer_fortune_withdraw)
//        Map<Long,Long> customerWithdrawPayMap = customerFortuneWithdrawDalService.findFinalWithdrawPayByCustomerIds(customerIds);
        Map<Long, Long> customerWithdrawMap = new HashMap<>(16);
        //循环未完成记录,并于已完成的记录经行比较,如果未完成的id大于已完成的,则可以直接修改withdraw;如果未完成的id小于已完成的id,则需要生成新的withdraw
        for (Map.Entry<Long, Long> customerWithdrawNoPay : customerWithdrawNoPayMap.entrySet()) {
//            Long payWithdrewId = customerWithdrawPayMap.get(customerWithdrawNoPay.getKey());
//            if (CommonUtil.isNullOrZero(payWithdrewId)){
            customerWithdrawMap.put(customerWithdrawNoPay.getKey(), customerWithdrawNoPay.getValue());
            continue;
//            }
//            if (BigDecimal.valueOf(payWithdrewId).compareTo(BigDecimal.valueOf(customerWithdrawNoPay.getValue())) > 0){
//                continue;
//            }
//            customerWithdrawMap.put(customerWithdrawNoPay.getKey(),customerWithdrawNoPay.getValue());
        }
        return customerWithdrawMap;
    }

    private void noPaymentUpDate(String paymentStatus, Long payoutBatchId, Long loginId, List<AclCustomerFortune> customerFortunes) {
        System.out.println("noPaymentUpDate");
        //需要修改的fortune记录(需修改佣金发放状态)
        List<AclCustomerFortune> fortuneUpdates = new ArrayList<>();
        //需要重新计算的withdraw.id
        List<Long> withdrawUpdateIds = new ArrayList<>();
        for (AclCustomerFortune fortune : customerFortunes) {
            //不一致,进行修改,并保存需修改的fortune和需重新计算的withdrawIds
            if (!paymentStatus.equals(fortune.getStatus())) {
                if ("0".equals(paymentStatus)) {
                    fortune.setCommissionPayoutStatus("5");
                } else if ("1".equals(paymentStatus)) {
                    fortune.setCommissionPayoutStatus("0");
                } else {
                    fortune.setCommissionPayoutStatus(paymentStatus);
                }
                fortune.setStatus(paymentStatus);
                fortune.setCommissionPayoutBy(loginId);
                fortune.setCommissionPayoutAt(new Date());
                if (!CommonUtil.isNullOrZero(payoutBatchId)) {
                    fortune.setPayoutBatchId(payoutBatchId);
                }
                Long withdrawId = fortune.getWithdrawedId();
                fortune.setWithdrawedId(null);
                fortune.setFortunePayedId(null);
                fortune.setFortunePayedId(null);
                fortuneUpdates.add(fortune);
                if (!CommonUtil.isNullOrZero(withdrawId)) {
                    withdrawUpdateIds.add(withdrawId);
                }
            }
        }
        //批量更新需要修改的fortune记录
        customerFortuneDalService.updateBatch(fortuneUpdates);
        //重新计算受到影响的withdraw
        if (!withdrawUpdateIds.isEmpty()) {
            Collections.sort(withdrawUpdateIds);
        }
        recalculateWithdraw(withdrawUpdateIds, null, loginId);
    }

    private void recalculateWithdraw(List<Long> withdrawUpdateIds, Set<Long> customerIds, Long loginId) {
        //重新统计提现记录
        if (withdrawUpdateIds.isEmpty()) {
            return;
        }
        System.out.println("recalculateWithdraw");
        //查询原提现记录
        List<AclCustomerFortuneWithdraw> withdraws = customerFortuneWithdrawDalService.findByIds(withdrawUpdateIds);
        //查询原待支付记录
        List<AclCustomerFortunePay> pays = customerFortunePayDalService.findByWithdrawIds(withdrawUpdateIds);
//        pays.forEach(p-> System.out.println(p.toString()));
        //查询所有提现财富
        List<AclCustomerFortune> fortunes = customerFortuneDalService.findByWithdrawIds(withdrawUpdateIds);
        List<AclCustomerFortune> fortuneUpdates = new ArrayList<>();
//        withdraws.forEach(p-> System.out.println(p.toString()));

        //查询出修改withdraw的customerId
        if (customerIds == null) {
            Map<Long, List<AclCustomerFortune>> customerFortuneMap = changeCustomerFortunesByFieldName(fortunes, "customerId");
            customerIds = customerFortuneMap.keySet();
        }
        //如果customerIds为null,则此次需修改的withdraw提现金额为0
        if (customerIds.isEmpty()) {
            //修改withdraws/pays提现金额为0
            updateWithdrawAmountToZero(withdraws, pays, loginId);
            return;
        }
        //查询customerIds的基本财富信息(历史累积财富  已退保财富  已提现财富 等) customerId CustomerFortuneStatisticalInfo
        Map<Long, CustomerFortuneStatisticalInfo> customerFortuneStatisticalMap = agmsFortuneDalService.findFortuneStatisticalByCustomers(customerIds);
        //按withdrawedId经行整理
        Map<Long, List<AclCustomerFortune>> withdrawsFortunes = changeCustomerFortunesByFieldName(fortunes, "withdrawedId");
        for (AclCustomerFortuneWithdraw withdraw : withdraws) {
            //withdrawId
            Long withdrawId = withdraw.getId();
            //需要重新计算withdraw根据的fortune记录
            List<AclCustomerFortune> fortuneWithdraw = withdrawsFortunes.get(withdrawId);
            if (fortuneWithdraw == null) {
                List<AclCustomerFortuneWithdraw> withdrawList = new ArrayList<>();
                withdrawList.add(withdraw);
                List<AclCustomerFortunePay> payList = new ArrayList<>();
                for (AclCustomerFortunePay pay : pays) {
                    if (pay.getWithdrawId().equals(withdrawId)) {
                        payList.add(pay);
                    }
                }
                updateWithdrawAmountToZero(withdrawList, payList, loginId);
                continue;
            }
            //统计此次提现总金额
            BigDecimal withdrawAmount = fortuneWithdraw.stream()
                    .map(item -> item.getReferralAmount() == null ? BigDecimal.ZERO : item.getReferralAmount())
                    .reduce(BigDecimal.ZERO, BigDecimal::add);
            //基本财富信息(历史累积财富  已退保财富  已提现财富 等)
            CustomerFortuneStatisticalInfo customerFortuneStatisticalInfo = customerFortuneStatisticalMap.get(withdraw.getCustomerId());
            //根据customerId查询未提现财富金额  可提现财富+不可体现财富+需修改的withdraw原提现金额
            BigDecimal withdrawBeforeAmount = customerFortuneStatisticalInfo.getCashableFortune()
                    .add(customerFortuneStatisticalInfo.getUncashableFortune())
                    .add(withdraw.getWithdrawAmount());
            //重新计算此次体现后剩余金额
            BigDecimal withdrawAfterAmount = withdrawBeforeAmount.subtract(withdrawAmount);
            //重新计算税额
            BigDecimal yearDrawnFortune = customerFortuneStatisticalInfo.getYearDrawnFortune().subtract(withdraw.getWithdrawAmount());
            BigDecimal taxAmount = calcuPersonalTax(withdrawAmount, yearDrawnFortune);
            //可提现金额
            BigDecimal paidAmount = withdrawAmount.subtract(taxAmount);
            withdraw.setWithdrawBeforeAmount(withdrawBeforeAmount);
            withdraw.setWithdrawAmount(withdrawAmount);
            withdraw.setWithdrawAfterAmount(withdrawAfterAmount);
            withdraw.setTaxAmount(taxAmount);
            withdraw.setPaidAmount(paidAmount);
            withdraw.setCreatedBy(loginId);
            withdraw.setCreatedAt(new Date());
            //找到对应的pay记录
            for (AclCustomerFortunePay pay : pays) {
                if (pay.getWithdrawId().equals(withdrawId)) {
                    pay.setWithdrawAmount(withdrawAmount);
                    pay.setPayAmount(paidAmount);
                    pay.setTaxAmount(taxAmount);
                    pay.setUpdatedBy(loginId);
                    pay.setUpdatedAt(new Date());
                    fortuneWithdraw.forEach(f -> f.setFortunePayedId(pay.getId()));
                    fortuneUpdates.addAll(fortuneWithdraw);
                }
            }
        }
        //批量更新withdraw记录
        customerFortuneWithdrawDalService.updateAll(withdraws);
//        withdraws.forEach(p-> System.out.println(p.toString()));
        //批量更新pay记录
        customerFortunePayDalService.updateAll(pays);
//        pays.forEach(p-> System.out.println(p.toString()));
        //再次更新fortune->payId
        customerFortuneDalService.updateBatch(fortuneUpdates);
    }

    private void updateWithdrawAmountToZero(List<AclCustomerFortuneWithdraw> withdraws, List<AclCustomerFortunePay> pays, Long loginId) {
        withdraws.forEach(w -> {
            w.setWithdrawAmount(BigDecimal.ZERO);
            w.setWithdrawAfterAmount(w.getWithdrawBeforeAmount());
            w.setCreatedAt(new Date());
            w.setCreatedBy(loginId);
            w.setTaxAmount(BigDecimal.ZERO);
            w.setPaidAmount(BigDecimal.ZERO);
        });
        pays.forEach(pay -> {
            pay.setWithdrawAmount(BigDecimal.ZERO);
            pay.setPayAmount(BigDecimal.ZERO);
            pay.setTaxAmount(BigDecimal.ZERO);
            pay.setUpdatedBy(loginId);
            pay.setUpdatedAt(new Date());
        });
        //批量更新withdraw记录
        customerFortuneWithdrawDalService.updateAll(withdraws);
        //批量更新pay记录
        customerFortunePayDalService.updateAll(pays);
    }

    private void insertWithdraw(Map<Long, List<AclCustomerFortune>> newCustomerWithdrawMap, Long loginId) {
        if (newCustomerWithdrawMap.isEmpty()) {
            return;
        }
        System.out.println("insertWithdraw");
        //暂存需要修改的fortune信息
        List<AclCustomerFortune> fortuneList = new ArrayList<>();

        //查询customerIds的基本财富信息(历史累积财富  已退保财富  已提现财富 等) customerId CustomerFortuneStatisticalInfo
        Map<Long, CustomerFortuneStatisticalInfo> customerFortuneStatisticalMap = agmsFortuneDalService.findFortuneStatisticalByCustomers(newCustomerWithdrawMap.keySet());
        for (Map.Entry<Long, List<AclCustomerFortune>> customerWithdraw : newCustomerWithdrawMap.entrySet()) {
            Long customerId = customerWithdraw.getKey();
            List<AclCustomerFortune> fortunes = customerWithdraw.getValue();
            //基本财富信息(历史累积财富  已退保财富  已提现财富 等)
            CustomerFortuneStatisticalInfo customerFortuneStatisticalInfo = customerFortuneStatisticalMap.get(customerId);
            //根据customerId查询未提现财富金额  可提现+不可体现
            BigDecimal withdrawBeforeAmount = customerFortuneStatisticalInfo.getCashableFortune()
                    .add(customerFortuneStatisticalInfo.getUncashableFortune());
            //计算此次提现财富
            BigDecimal withdrawAmount = fortunes.stream()
                    .map(item -> item.getReferralAmount() == null ? BigDecimal.ZERO : item.getReferralAmount())
                    .reduce(BigDecimal.ZERO, BigDecimal::add);
            //重新计算此次体现后剩余金额
            BigDecimal withdrawAfterAmount = withdrawBeforeAmount.subtract(withdrawAmount);
            //重新计算税额
            BigDecimal yearDrawnFortune = customerFortuneStatisticalInfo.getYearDrawnFortune();
            BigDecimal taxAmount = calcuPersonalTax(withdrawAmount, yearDrawnFortune);
            //可提现金额
            BigDecimal paidAmount = withdrawAmount.subtract(taxAmount);
            //保存withdraw记录
            AclCustomerFortuneWithdraw withdraw = new AclCustomerFortuneWithdraw();
            withdraw.setCustomerId(customerId);
            withdraw.setWithdrawBeforeAmount(withdrawBeforeAmount);
            withdraw.setWithdrawAmount(withdrawAmount);
            withdraw.setWithdrawAfterAmount(withdrawAfterAmount);
            withdraw.setIsPaid(0);
            withdraw.setPaidAmount(paidAmount);
            withdraw.setTaxAmount(taxAmount);
            withdraw.setRequestorName(customerFortuneStatisticalInfo.getCustomerName());
            withdraw.setRequestorIdTypeId(customerFortuneStatisticalInfo.getCustomerIdTypeId());
            withdraw.setRequestorId(customerFortuneStatisticalInfo.getCustomerIdNo());
            withdraw.setRequestorBirthdate(CommonUtil.stringParseDate("yyyy-MM-dd", customerFortuneStatisticalInfo.getCustomerBirthdate()));
            withdraw.setRequestorGender(customerFortuneStatisticalInfo.getCustomerGender());
            withdraw.setStatus(0);
            withdraw.setCreatedAt(new Date());
            withdraw.setCreatedBy(loginId);
            customerFortuneWithdrawDalService.save(withdraw);
            //保存pay记录
            AclCustomerFortunePay pay = new AclCustomerFortunePay();
            pay.setWithdrawId(withdraw.getId());
            pay.setWithdrawAmount(withdrawAmount);
            pay.setCustomerId(customerId);
            pay.setCustomerName(customerFortuneStatisticalInfo.getCustomerName());
            pay.setPaidMethod(1);
            pay.setPayStatus(0);
            pay.setPayAmount(paidAmount);
            pay.setTaxAmount(taxAmount);
            pay.setCreatedAt(new Date());
            pay.setCreatedBy(loginId);
            pay.setUpdatedAt(new Date());
            pay.setUpdatedBy(loginId);
            customerFortunePayDalService.save(pay);
            //保存fortune记录
            fortunes.forEach(f -> {
                f.setWithdrawedId(withdraw.getId());
                f.setCommissionPayoutStatus("2");
                f.setCommissionPayoutAt(new Date());
                f.setCommissionPayoutBy(loginId);
                f.setFortunePayedId(pay.getId());
            });
            fortuneList.addAll(fortunes);
        }
        customerFortuneDalService.updateBatch(fortuneList);
    }

    private BigDecimal calcuPersonalTax(BigDecimal drawnFortune, BigDecimal yearDrawnFortune) {
        //drawnFortune为此次提现额
        //yearDrawnFortune今年已提现费用
        //计算今年已交税额
        if (!CommonUtil.isNullOrZero(yearDrawnFortune)) {
            yearDrawnFortune = BigDecimal.ZERO;
        }
        BigDecimal yearTax = calculateTax(yearDrawnFortune);
        //总共提现金额
        BigDecimal amount = drawnFortune.add(yearDrawnFortune);
        BigDecimal tax = calculateTax(amount);

        //总税额减去已交税额
        tax = tax.subtract(yearTax);
        return tax.setScale(2, BigDecimal.ROUND_HALF_UP);
    }

    private BigDecimal calculateTax(BigDecimal yearTax) {
        List<MdIncometaxRate> incometaxs = systemConfigService.findIncometax();
        //累计提现金额 /1.06* (1-20%)*(1-25%) = 应纳税所得额
        yearTax = yearTax.divide(BigDecimal.valueOf(1.06), 3, BigDecimal.ROUND_HALF_UP).multiply(BigDecimal.valueOf(0.6));
        for (MdIncometaxRate incometax : incometaxs) {
            if (incometax.getTaxableFrom().compareTo(yearTax) <= 0 && yearTax.compareTo(incometax.getTaxableTo()) < 0) {
                //已交税额
                yearTax = yearTax.multiply(BigDecimal.valueOf(incometax.getTaxRate()))
                        .divide(BigDecimal.valueOf(100), 3, BigDecimal.ROUND_HALF_UP)
                        .subtract(incometax.getEstDeductable());
                break;
            }
        }
        return yearTax;
    }

    /**
     * 根据name(字段名整理fortune记录)
     *
     * @param customerFortunes 未整理的fortune记录
     * @param fieldName        根据哪个字段名整理
     * @return 整理后的fortune
     */
    private Map<Long, List<AclCustomerFortune>> changeCustomerFortunesByFieldName(List<AclCustomerFortune> customerFortunes, String fieldName) {
        Map<Long, List<AclCustomerFortune>> map = new HashMap<>(16);
        for (AclCustomerFortune list : customerFortunes) {
            List<AclCustomerFortune> customerFortuneList = map.get(getFieldValueByName(fieldName, list));
            customerFortuneList = Optional.ofNullable(customerFortuneList).orElse(new ArrayList<>());
            if (customerFortuneList.isEmpty()) {
                customerFortuneList.add(list);
                map.put(getFieldValueByName(fieldName, list), customerFortuneList);
            } else {
                customerFortuneList.add(list);
            }
        }
        return map;
    }

    /**
     * 根据属性名获取属性值
     */
    private Long getFieldValueByName(String fieldName, Object o) {
        try {
            String firstLetter = fieldName.substring(0, 1).toUpperCase();
            String getter = "get" + firstLetter + fieldName.substring(1);
            Method method = o.getClass().getMethod(getter);
            return (Long) method.invoke(o, new Object[]{});
        } catch (Exception e) {
            return null;
        }
    }


    @Override
    public CommissionPayoutStatusQueryResponseVO commissionPayoutStatusQuery(CommissionPayoutStatusQueryRequestVO requestVO) {
        CommissionPayoutStatusQueryResponseVO responseVO = new CommissionPayoutStatusQueryResponseVO();
        CommissionPayoutStatusQueryInfo info = new CommissionPayoutStatusQueryInfo();
        BeanUtils.copyProperties(requestVO, info);
        //体系查询调整，如果体系为S3，则需要查询它下面的所有子集
        PractitionerSubordinateInfo practitionerSubordinateInfo = new PractitionerSubordinateInfo();
        BeanUtils.copyProperties(requestVO, practitionerSubordinateInfo);
        practitionerSubordinateInfo.setDeptId(requestVO.getInsurerBranchDeptId());
        List<Long> subsystemIdList = aclPractitionerSubordinateSystemDalService.getLongs(practitionerSubordinateInfo);
        info.setSubsystemIdList(subsystemIdList);

        List<CommissionPayoutStatus> commissionPayoutStatusList = agmsFortuneDalService.commissionPayoutStatusQuery(info);
        int totalSingular = 0;
        BigDecimal totalOrderPrice = BigDecimal.ZERO;
        BigDecimal totalReferralAmount = BigDecimal.ZERO;
        List<Long> orderIdList = new ArrayList<>();
        Set<String> nameSet = new HashSet<>();
        for (CommissionPayoutStatus commissionPayoutStatus : commissionPayoutStatusList) {
            nameSet.add(commissionPayoutStatus.getPractitionerName());

            Long orderId = commissionPayoutStatus.getOrderId();
            totalReferralAmount = totalReferralAmount.add(commissionPayoutStatus.getReferralAmount() == null ? BigDecimal.ZERO : commissionPayoutStatus.getReferralAmount());
            if (orderId != null) {
                if (orderIdList.contains(orderId)) {
                    continue;
                }
                orderIdList.add(orderId);
                ++totalSingular;
                totalOrderPrice = totalOrderPrice.add(commissionPayoutStatus.getOrderPrice() == null ? BigDecimal.ZERO : commissionPayoutStatus.getOrderPrice());
            }
        }
        responseVO.setLifePractitionerCount(nameSet.size());
        responseVO.setTotalSingular(totalSingular);
        responseVO.setTotalOrderPrice(totalOrderPrice);
        responseVO.setTotalReferralAmount(totalReferralAmount);
        responseVO.setCommissionPayoutStatusList(commissionPayoutStatusList);

        // 分页
        this.queryByPage(info, responseVO);

        responseVO.setCommonResult(new CommonResult(true, ZHBErrorConfig.getErrorInfo("800000")));
        return responseVO;
    }

    private void queryByPage(CommissionPayoutStatusQueryInfo info, CommissionPayoutStatusQueryResponseVO responseVO) {
        PageInfo<CommissionPayoutStatus> page = info.getPage();

        PageHelper.startPage(page.getPageNum(), page.getPageSize(), null);

        List<CommissionPayoutStatus> dataList = agmsFortuneDalService.commissionPayoutStatusQuery(info);

        PageInfo<CommissionPayoutStatus> pageInfo = new PageInfo<>(dataList);

        // 设置resp
        responseVO.setCommissionPayoutStatusList(pageInfo.getList());
        pageInfo.setList(null);
        responseVO.setPage(pageInfo);
    }

    @Override
    public WithdrawQueryResponseVO withdrawQuery(WithdrawQueryRequestVO requestVO) {
        WithdrawQueryResponseVO responseVO = new WithdrawQueryResponseVO();
        WithdrawQueryInfo info = new WithdrawQueryInfo();
        BeanUtils.copyProperties(requestVO, info);
        //体系查询调整，如果体系为S3，则需要查询它下面的所有子集
        PractitionerSubordinateInfo practitionerSubordinateInfo = new PractitionerSubordinateInfo();
        BeanUtils.copyProperties(requestVO, practitionerSubordinateInfo);
        practitionerSubordinateInfo.setDeptId(requestVO.getInsurerBranchDeptId());
        List<Long> subsystemIdList = aclPractitionerSubordinateSystemDalService.getLongs(practitionerSubordinateInfo);
        info.setSubsystemIdList(subsystemIdList);

        List<WithdrawLabelInfo> withdraws = agmsFortuneDalService.transformForWithdrawLabel(info);
        responseVO.setTotalPeople(withdraws.size());
        BigDecimal totalReferralAmount = withdraws.stream()
                .map(f -> f.getReferralAmount() == null ? BigDecimal.ZERO : f.getReferralAmount())
                .reduce(BigDecimal.ZERO, BigDecimal::add);
        responseVO.setTotalReferralAmount(totalReferralAmount);
        responseVO.setCommonResult(new CommonResult(true, ZHBErrorConfig.getErrorInfo("800000")));
        responseVO.setWithdraws(withdraws);
        return responseVO;
    }

    @Override
    public FortunePayToOrderResponseVO fortunePayToOrder(FortunePayToOrderRequestVO requestVO) {
        FortunePayToOrderResponseVO responseVO = new FortunePayToOrderResponseVO();
        Long payId = requestVO.getPayId();
        //查询所有订单,并根据支付id标记本次提现订单
        List<FortunePayToOrderInfo> fortunePayToOrderInfos = agmsFortuneDalService.fortunePayToOrder(new Long[]{payId});
        BigDecimal totalOrderPrice = fortunePayToOrderInfos.stream()
                .map(f -> f.getOrderPrice() == null ? BigDecimal.ZERO : f.getOrderPrice())
                .reduce(BigDecimal.ZERO, BigDecimal::add);
        BigDecimal totalReferralAmount = fortunePayToOrderInfos.stream()
                .map(f -> f.getReferralAmount() == null ? BigDecimal.ZERO : f.getReferralAmount())
                .reduce(BigDecimal.ZERO, BigDecimal::add);
        responseVO.setTotalSingular(fortunePayToOrderInfos.size());
        responseVO.setTotalOrderPrice(totalOrderPrice);
        responseVO.setTotalReferralAmount(totalReferralAmount);
        responseVO.setFortunePayToOrderInfos(fortunePayToOrderInfos);
        responseVO.setCommonResult(new CommonResult(true, ZHBErrorConfig.getErrorInfo("800000")));
        return responseVO;
    }

    @Override
    @SuppressWarnings("unchecked")
    @Transactional(rollbackFor = Exception.class)
    public FortunePayResponseVO fortunePay(FortunePayRequestVO requestVO) {
        FortunePayResponseVO responseVO = new FortunePayResponseVO();
        CommonResult commonResult = check(requestVO);
        Long[] payIds = requestVO.getPayIds();
        Long loginId = requestVO.getLoginId();
        AclUser user = userDalService.selectByPrimaryKey(loginId);
        //查询所有pay记录和withdraw记录
        List<AclCustomerFortunePay> customerFortunePays = customerFortunePayDalService.findByIds(payIds);
        Long[] withdrawIds = new Long[customerFortunePays.size()];
        for (int i = 0; i < customerFortunePays.size(); i++) {
            withdrawIds[i] = customerFortunePays.get(i).getWithdrawId();
        }
        List<AclCustomerFortuneWithdraw> customerFortuneWithdraws = customerFortuneWithdrawDalService.findByIds(Arrays.asList(withdrawIds));
        //查询经纪人的静态记录
        Long[] customerIds = new Long[customerFortunePays.size()];
        for (int i = 0; i < customerFortunePays.size(); i++) {
            customerIds[i] = customerFortunePays.get(i).getCustomerId();
        }
        List<AclCustomerFortuneStatistic> customerFortuneStatistics = customerFortuneStatisticDalService.findByCustomerIds(customerIds);
        //fortune记录
        List<AclCustomerFortune> fortunes = customerFortuneDalService.findByWithdrawIds(Arrays.asList(withdrawIds));
        //更新静态表
        for (AclCustomerFortuneStatistic statistic : customerFortuneStatistics) {
            Long customerId = statistic.getCustomerId();
            BeanPropertyValueEqualsPredicate predicateClause = new BeanPropertyValueEqualsPredicate("customerId", customerId);
            List<AclCustomerFortuneWithdraw> withdraws = (List<AclCustomerFortuneWithdraw>) CollectionUtils.select(customerFortuneWithdraws, predicateClause);
            if (withdraws.isEmpty()) {
                continue;
            }
            for (AclCustomerFortuneWithdraw withdraw : withdraws) {
//                if (CommonUtil.isNullOrZero(withdraw.getIsPaid())){
                BigDecimal drawnFortune = statistic.getDrawnFortune();
                if (CommonUtil.isNullOrZero(drawnFortune)) {
                    drawnFortune = BigDecimal.ZERO;
                }
                drawnFortune = drawnFortune.add(withdraw.getWithdrawAmount());
                statistic.setDrawnFortune(drawnFortune);
//                }
            }
        }
        customerFortuneStatisticDalService.updateAll(customerFortuneStatistics);
        //修改withdraw记录
        customerFortuneWithdraws.forEach(w -> {
            w.setIsPaid(1);
            w.setStatus(1);
            w.setPaidDate(new Date());
        });
        customerFortuneWithdrawDalService.updateAll(customerFortuneWithdraws);
        //修改pay表
        customerFortunePays.forEach(p -> {
            p.setPayStatus(1);
            p.setPaidBy(user.getName());
            p.setUpdatedAt(new Date());
            p.setUpdatedBy(loginId);
            p.setPayDate(new Date());
        });
        customerFortunePayDalService.updateAll(customerFortunePays);
        //修改fortune
        fortunes.forEach(f -> {
            f.setCommissionPayoutStatus("4");
            f.setStatus("4");//新发佣检核状态字段
        });
        customerFortuneDalService.updateBatch(fortunes);
        responseVO.setCommonResult(commonResult);
        return responseVO;
    }

    private CommonResult check(FortunePayRequestVO requestVO) {
        CommonResult commonResult = new CommonResult();
        boolean success = true;
        String message = ZHBErrorConfig.getErrorInfo("800000");
        Long[] payIds = requestVO.getPayIds();
        if (payIds == null || payIds.length == 0) {
            success = false;
            message = ZHBErrorConfig.getErrorInfo("830022");
        }
        commonResult.setSuccess(success);
        commonResult.setMessage(message);
        return commonResult;
    }


    @Override
    public SurrenderFortuneResponseVO surrenderFortune(SurrenderFortuneRequestVO requestVO) {
        //退保,更新order中status=4  并更新statistic表
        SurrenderFortuneResponseVO resp = new SurrenderFortuneResponseVO();
        Long orderId = requestVO.getOrderId();
        //判断此订单是否可以退保
        //保单财富以进行提现申请,无法进行退保
        PoOrder poOrder = poOrderDALService.findByIdAndStatus(orderId, 3);
        if (poOrder == null) {
            resp.setCommonResult(new CommonResult(false, ZHBErrorConfig.getErrorInfo("830065")));
            return resp;
        }

        //修改订单状态
        poOrder.setStatus(4);
        poOrderDALService.update(poOrder);

        //财富此订单对应的财富
        List<AclCustomerFortune> fortunes = customerFortuneDalService.findByOrderId(orderId);
        for (AclCustomerFortune fortune : fortunes) {
            //查询财富对应的customer的statistic
            AclCustomerFortuneStatistic statistic = customerFortuneStatisticDalService.findByCustomerId(fortune.getCustomerId());
            BigDecimal cancelledFortune = statistic.getCancelledFortune();
            if (CommonUtil.isNullOrZero(cancelledFortune)) {
                cancelledFortune = BigDecimal.ZERO;
            }
            cancelledFortune = cancelledFortune
                    .add(fortune.getReferralAmount());
            statistic.setCancelledFortune(cancelledFortune);
            customerFortuneStatisticDalService.update(statistic);

            //生成对应的负向记录  (commission_amount/fyc_amount/referral_amount)
            AclCustomerFortune fortuneNew = new AclCustomerFortune();
            org.springframework.beans.BeanUtils.copyProperties(fortune, fortuneNew);

            Double commissionAmount = -fortune.getCommissionAmount().doubleValue();
            fortuneNew.setCommissionAmount(BigDecimal.valueOf(commissionAmount));

            Double fycAmount = -fortune.getFycAmount().doubleValue();
            fortuneNew.setFycAmount(BigDecimal.valueOf(fycAmount));

            Double referralAmount = -fortune.getReferralAmount().doubleValue();
            fortuneNew.setReferralAmount(BigDecimal.valueOf(referralAmount));
            fortuneNew.setCreatedAt(new Date());
            fortuneNew.setWithdrawedId(null);
            fortuneNew.setFortunePayedId(null);
            fortuneNew.setId(null);
            customerFortuneDalService.save(fortuneNew);
        }

        //发送邮件
        String email = systemConfigService.getSingleConfigValue("FortuneUpdateToAddress");
        List<String> ccList = systemConfigService.getListConfigValue("FortuneUpdateToCCAddress");
        String[] ccAddresses = ccList.toArray(new String[ccList.size()]);

        PoOrder order = poOrderDALService.findByOrderId(orderId);
        Integer configLevel = order.getConfigLevel();
        String name = "";
        if (configLevel == 2) {
            Long productId = order.getProductId();
            Product product = productDALService.findById(productId);
            name = product.getName();
        } else if (configLevel == 3) {
            Long planId = order.getPlanId();
            ProductPlan productPlan = productPlanDALService.findById(planId);
            name = productPlan.getName();
        }
        String messageText = "订单id:" + order.getId() + "<br>" +
                "订单号:" + order.getOrderNo() + "<br>" +
                "产品名称:" + name + "<br>" +
                "产品金额:" + order.getOrderPrice() + "<br>";
        List<AclPolicyholder> policyHolders = aclPolicyholderService.findByOrderId(orderId);
        for (AclPolicyholder policyHolder : policyHolders) {
            Integer type = policyHolder.getType();
            if (type == 2) {
                messageText += "投标人:<br>" +
                        "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;姓名:" + policyHolder.getName() +
                        "&nbsp;&nbsp;&nbsp;&nbsp;电话" + policyHolder.getMobileNo() + "<br>";
            } else if (type == 3) {
                messageText += "被保人:<br>" +
                        "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;姓名:" + policyHolder.getName();
                if (!CommonUtil.isNullOrBlank(policyHolder.getMobileNo())) {
                    messageText += "&nbsp;&nbsp;&nbsp;&nbsp;电话" + policyHolder.getMobileNo() + "<br>";
                }
            }
        }

        String subject = "回退财富";
        sendService.sendEmailOrSMS("email", email, "3", messageText, null, subject, ccAddresses, "回复财富", 99, null);
        resp.setCommonResult(new CommonResult(true, ZHBErrorConfig.getErrorInfo("800000")));
        return resp;
    }

    @Override
    public ExportFortunePayResponseVO exportFortunePay(ExportFortunePayRequestVO requestVO, HttpServletResponse response) {
        ExportFortunePayResponseVO responseVO = new ExportFortunePayResponseVO();
        WithdrawQueryInfo info = new WithdrawQueryInfo();
        BeanUtils.copyProperties(requestVO, info);
        //体系查询调整，如果体系为S3，则需要查询它下面的所有子集
        PractitionerSubordinateInfo practitionerSubordinateInfo = new PractitionerSubordinateInfo();
        BeanUtils.copyProperties(requestVO, practitionerSubordinateInfo);
        practitionerSubordinateInfo.setDeptId(requestVO.getInsurerBranchDeptId());
        List<Long> subsystemIdList = aclPractitionerSubordinateSystemDalService.getLongs(practitionerSubordinateInfo);
        info.setSubsystemIdList(subsystemIdList);

        List<WithdrawLabelInfo> withdraws = agmsFortuneDalService.transformForWithdrawLabel(info);
        Long[] payIds = new Long[withdraws.size()];
        for (int i = 0; i < withdraws.size(); i++) {
            payIds[i] = withdraws.get(i).getPayId();
        }
        List<FortunePayToOrderInfo> fortunePayToOrderInfos = agmsFortuneDalService.fortunePayToOrder(payIds);
        createCSV(fortunePayToOrderInfos, response);
        responseVO.setCommonResult(new CommonResult(true, ZHBErrorConfig.getErrorInfo("800000")));
        return responseVO;
    }

    @Override
    public FortunePayoutBatchQueryResponseVO fortunePayoutBatchQuery() {
        FortunePayoutBatchQueryResponseVO responseVO = new FortunePayoutBatchQueryResponseVO();
        PageHelper.clearPage();//偶然遇到了这个问题，同一个查询结果一会是10个（预期），一会是真实个数（刷新/重启应用就又变成真实个数）  查看日志，发现了问题：结果为10个时查询SQL自动加上了Limit 10
        List<String> payoutBatch = customerFortunePayoutBatchDalService.findAll();
        responseVO.setPayoutBatchInfos(payoutBatch);
        responseVO.setCommonResult(new CommonResult(true, ZHBErrorConfig.getErrorInfo("800000")));
        return responseVO;
    }

    @Override
    public ExportCommissionPayoutStatusQueryResponseVO exportCommissionPayoutStatusQuery(CommissionPayoutStatusQueryRequestVO requestVO, HttpServletResponse response) {
        ExportCommissionPayoutStatusQueryResponseVO responseVO = new ExportCommissionPayoutStatusQueryResponseVO();
        CommissionPayoutStatusQueryInfo info = new CommissionPayoutStatusQueryInfo();
        BeanUtils.copyProperties(requestVO, info);
        //体系查询调整，如果体系为S3，则需要查询它下面的所有子集
        PractitionerSubordinateInfo practitionerSubordinateInfo = new PractitionerSubordinateInfo();
        BeanUtils.copyProperties(requestVO, practitionerSubordinateInfo);
        practitionerSubordinateInfo.setDeptId(requestVO.getInsurerBranchDeptId());
        List<Long> subsystemIdList = aclPractitionerSubordinateSystemDalService.getLongs(practitionerSubordinateInfo);
        info.setSubsystemIdList(subsystemIdList);

        List<CommissionPayoutStatus> commissionPayoutStatusList = agmsFortuneDalService.commissionPayoutStatusQuery(info);

        // 导出csv
        exportCommissionPayoutStatusQueryCSV(commissionPayoutStatusList, response);
        responseVO.setCommonResult(new CommonResult(true, ZHBErrorConfig.getErrorInfo("800000")));
        return responseVO;
    }

    @Override
    public ExportCommissionPayoutStatusQueryResponseVO exportCommissionPayoutStatusQuerySheet2(CommissionPayoutStatusQueryRequestVO requestVO, HttpServletResponse response) {
        ExportCommissionPayoutStatusQueryResponseVO responseVO = new ExportCommissionPayoutStatusQueryResponseVO();
        CommissionPayoutStatusQueryInfo info = new CommissionPayoutStatusQueryInfo();
        BeanUtils.copyProperties(requestVO, info);
        //体系查询调整，如果体系为S3，则需要查询它下面的所有子集
        PractitionerSubordinateInfo practitionerSubordinateInfo = new PractitionerSubordinateInfo();
        BeanUtils.copyProperties(requestVO, practitionerSubordinateInfo);
        practitionerSubordinateInfo.setDeptId(requestVO.getInsurerBranchDeptId());
        List<Long> subsystemIdList = aclPractitionerSubordinateSystemDalService.getLongs(practitionerSubordinateInfo);
        info.setSubsystemIdList(subsystemIdList);

        List<CommissionPayoutStatus> commissionPayoutStatusList = agmsFortuneDalService.commissionPayoutStatusQuery(info);

        List<Long> fortuneIdList = new ArrayList<>();
        for (CommissionPayoutStatus item : commissionPayoutStatusList) {
            fortuneIdList.add(item.getFortuneId());
        }
        // 查询销售经纪人所属s1，s2，s3的C08，C10记录
        List<Long> fortuneIdList1 = agmsFortuneDalService.querySalePractitionerFortune1(fortuneIdList);
        List<Long> fortuneIdList0 = agmsFortuneDalService.querySalePractitionerFortune(fortuneIdList);
        List<Long> fortuneIdList2 = new ArrayList<>();// 非销售经纪人所属s1，s2，s3的C08，C10记录
        for (Long item : fortuneIdList0) {
            if (!fortuneIdList1.contains(item)) {
                fortuneIdList2.add(item);
            }
        }

        // 导出xlsx
        XSSFWorkbook wkb = new XSSFWorkbook();
        // 保单发佣清算明细报表Sheet页
        generateSheet2(wkb, fortuneIdList, fortuneIdList2);
        // 设置响应
        String fn = "ZHB_SalaryReport_" + System.currentTimeMillis() + ".xlsx";
        String headStr = "attachment; filename=\"" + fn + "\"";
        xlsxDownload(wkb, headStr, response);
        responseVO.setCommonResult(new CommonResult(true, ZHBErrorConfig.getErrorInfo("800000")));
        return responseVO;
    }

    @Override
    public CommissionPayoutStatusUpdateResponseVO commissionPayoutAddRemark(CommissionPayoutAddRemarkRequestVO requestVO) {
        CommissionPayoutStatusUpdateResponseVO responseVO = new CommissionPayoutStatusUpdateResponseVO();

        Long fortuneId = requestVO.getFortuneId();
        String remark = requestVO.getRemark();
        Long loginId = requestVO.getLoginId();
        if (fortuneId == null) {
            responseVO.setCommonResult(new CommonResult(false, "fortuneId不能为空"));
            return responseVO;
        }
        if (StringUtils.isBlank(remark)) {
            responseVO.setCommonResult(new CommonResult(false, "remark不能为空"));
            return responseVO;
        }
        agmsFortuneDalService.commissionPayoutAddRemark(requestVO);

        responseVO.setCommonResult(new CommonResult(true, ZHBErrorConfig.getErrorInfo("800000")));
        return responseVO;
    }

    private void generateSheet2(XSSFWorkbook wkb, List<Long> fortuneIdList, List<Long> fortuneIdList2) {
        XSSFSheet sheet2 = wkb.createSheet("保单清算明细报表");
        // 1.设置表头
        String[] columnName = new String[]{"序号", "对账月", "投保人", "保单号", "保司", "保费", "产品", "承保时间", "年期",
                "FYC%", "业绩折标率%", "分佣%", "业绩%", "RYC%", "保单FYC", "佣金FYC", "业绩FYC", "保单RYC", "佣金RYC", "渠道",
                "FYC体系", "销售经纪人", "销售经纪人职级", "职级率",
                "首年销售佣金", "续年销售佣金", "续期辅导人", "续期辅导奖金率", "续期辅导奖", "经纪人月销售加码奖金%", "经纪人月销售加码奖金",
                "推荐人", "个人推荐率", "个人推荐奖￥", "S1体系推荐率", "S1体系推荐奖金￥",
                "一代辅导人", "一代辅导人职级", "一代辅导人职级岗位津贴%", "一代辅导人岗位津贴",
                "二代辅导人", "二代辅导人职级", "二代辅导人职级岗位津贴%", "二代辅导人岗位津贴",
                "所属 S1分部", "S1团队达标奖金率", "S1团队达标奖金￥（季度）", "S1团队长辅导津贴比率", "S1团队长辅导津贴", "S1续期绩效奖金",
                "所属 S2体系", "S2团队达标奖金率", "S2团队达标奖金￥（季度）", "S2团队长辅导津贴比率", "S2团队长辅导津贴", "S2续期绩效奖金",
                "所属 S3纵队", "S3团队达标奖金率", "S3团队达标奖金￥（季度）", "S3团队长辅导津贴比率", "S3团队长辅导津贴",
                "S1育成人", "S1育成人职级", "S1育成奖金率", "S1育成金额",
                "C14直接销售加码佣金率", "C14直接销售加码佣金",
                "基本法属性", "S01-首年度销售佣金", "S03-首年度辅导奖金",
                "R01-首年度直接销售佣金（旧）", "R02-续年度直接销售拥金(旧)", "R03-续年度辅导佣金(旧)", "R04-续年度绩效奖金(旧)",
                "S1推荐人", "S2推荐人", "S2体系推荐率", "S2体系推荐奖金￥", "S3推荐人", "S3体系推荐率", "S3体系推荐奖金￥",
                "S2育成人", "S2育成人职级", "S2育成奖金率", "S2育成金额", "S3育成人", "S3育成人职级", "S3育成奖金率", "S3育成金额",
                "其它税前加扣款1", "其它税前加扣款2", "其它税前加扣款3", "其它税前加扣款4", "其它"};
        XSSFRow rownum = sheet2.createRow(0);
        for (int j = 0; j < columnName.length; j++) {
            rownum.createCell(j).setCellValue(columnName[j]);
        }
        // 2.查询Sheet2数据
        List<Map<String, Object>> dataList = agmsFortuneDalService.querySheet2Data(fortuneIdList, fortuneIdList2);
        // 将结果集中的推荐人、体系等id替换为中文
        if (CollectionUtils.isNotEmpty(dataList)) {
            exportSalaryDetailsSheet2queryOrganization(dataList);
        }
        // 3.遍历数据，产生数据行
        if (null != dataList) {
            for (int i = 0; i < dataList.size(); i++) {
                Map<String, Object> values = dataList.get(i);
                rownum = sheet2.createRow(i + 1);
                // 每行序号
                rownum.createCell(0).setCellValue(i + 1);
                for (int j = 0; j < columnName.length; j++) {
                    rownum.createCell(j + 1).setCellValue(values.get(j + "") != null ? values.get(j + "").toString() : null);
                }
            }
        }
    }

    private void exportSalaryDetailsSheet2queryOrganization(List<Map<String, Object>> list) {

        List<AclPractitioner> aclPractitionerList = aclPractitionerDALService.findAll();
        Map<Long, AclPractitioner> aclPractitionerMap = new HashMap<>();
        if (aclPractitionerList != null && aclPractitionerList.size() > 0) {
            for (AclPractitioner item : aclPractitionerList) {
                aclPractitionerMap.put(item.getId(), item);
            }
        }
        List<AclPractitionerSubordinateSystem> subordinateSystemList = aclPractitionerSubordinateSystemDalService.findAll();
        Map<Long, AclPractitionerSubordinateSystem> subordinateSystemMap = new HashMap<>();
        if (subordinateSystemList != null && subordinateSystemList.size() > 0) {
            for (AclPractitionerSubordinateSystem item : subordinateSystemList) {
                subordinateSystemMap.put(item.getId(), item);
            }
        }

        //经纪人
        AclPractitioner practitioner = null;
        //所属体系
        AclPractitionerSubordinateSystem subordinateSystem = null;

        Map<String, Object> item = null;
        for (int i = 0; i < list.size(); i++) {
            item = list.get(i);
            //FYC体系 [19]
            if (null != item.get("19") && StringUtils.isNotBlank(item.get("19").toString())) {
                subordinateSystem = subordinateSystemMap.get(Long.valueOf(item.get("19").toString()));
                item.put("19", subordinateSystem != null ? subordinateSystem.getName() : null);
            }

            //续期辅导人 [25]
            if (null != item.get("25") && StringUtils.isNotBlank(item.get("25").toString())) {
                String[] ids = item.get("25").toString().split(",");
                for (int j = 0; j < ids.length; j++) {
                    practitioner = aclPractitionerMap.get(Long.valueOf(ids[j]));
                    if (practitioner != null) {
                        ids[j] = practitioner.getName();
                    }
                }
                item.put("25", Joiner.on(",").join(Arrays.asList(ids)));
            }
            //所属S1分部 [43]
            if (null != item.get("43") && StringUtils.isNotBlank(item.get("43").toString())) {
                subordinateSystem = subordinateSystemMap.get(Long.valueOf(item.get("43").toString()));
                item.put("43", subordinateSystem != null ? subordinateSystem.getName() : null);
            }
            //所属S2体系 [49]
            if (null != item.get("49") && StringUtils.isNotBlank(item.get("49").toString())) {
                subordinateSystem = subordinateSystemMap.get(Long.valueOf(item.get("49").toString()));
                item.put("49", subordinateSystem != null ? subordinateSystem.getName() : null);
            }
            //所属S3纵队 [55]
            if (null != item.get("55") && StringUtils.isNotBlank(item.get("55").toString())) {
                subordinateSystem = subordinateSystemMap.get(Long.valueOf(item.get("55").toString()));
                item.put("55", subordinateSystem != null ? subordinateSystem.getName() : null);
            }

            //44-47
            item.put("44", this.processMerge(item.get("44"), aclPractitionerMap));
            item.put("45", this.processMerge(item.get("45"), aclPractitionerMap));
            item.put("46", this.processMerge(item.get("46"), aclPractitionerMap));
            item.put("47", this.processMerge(item.get("47"), aclPractitionerMap));
            //50-53
            item.put("50", this.processMerge(item.get("50"), aclPractitionerMap));
            item.put("51", this.processMerge(item.get("51"), aclPractitionerMap));
            item.put("52", this.processMerge(item.get("52"), aclPractitionerMap));
            item.put("53", this.processMerge(item.get("53"), aclPractitionerMap));
            //56-59
            item.put("56", this.processMerge(item.get("56"), aclPractitionerMap));
            item.put("57", this.processMerge(item.get("57"), aclPractitionerMap));
            item.put("58", this.processMerge(item.get("58"), aclPractitionerMap));
            item.put("59", this.processMerge(item.get("59"), aclPractitionerMap));
            //71-72
            item.put("71", this.processMerge(item.get("71"), aclPractitionerMap));
            item.put("72", this.processMerge(item.get("72"), aclPractitionerMap));

        }
    }

    private String processMerge(Object o, Map<Long, AclPractitioner> aclPractitionerMap) {
        if (null != o && StringUtils.isNotBlank(o.toString())) {
            String[] practitionerId_values = o.toString().split(",");
            if (practitionerId_values.length > 1) {
                for (int j = 0; j < practitionerId_values.length; j++) {
                    String[] split = practitionerId_values[j].split("-");
                    if (StringUtils.isNotBlank(split[0]) && split.length == 2) {
                        Long practitionerId = Long.valueOf(split[0]);
                        AclPractitioner practitioner = aclPractitionerMap.get(practitionerId);
                        if (practitioner != null) {
                            String strNew = practitioner.getName() + "-" + split[1];
                            practitionerId_values[j] = strNew;
                        }
                    }
                }
                return Joiner.on(",").join(Arrays.asList(practitionerId_values));
            } else {
                return o.toString();
            }
        }
        return null;
    }


    private void exportCommissionPayoutStatusQueryCSV(List<CommissionPayoutStatus> commissionPayoutStatusList, HttpServletResponse response) {
        String[] columnName = new String[]{"序号", "来佣检核年月", "发佣状态", "预计发佣年月", "实际发佣年月", "保险公司", "保单号", "保费", "来佣金额", "佣金类型", "应发佣率", "应发金额",
                "经纪人", "经纪人职级", "体系", "营业部", "购买方案", "缴费年限", "保障期间", "备注"};
        String tableName = "YD_CommissionPayout" + System.currentTimeMillis() + ".csv";

        String CSV_COLUMN_SEPARATOR = ",";//CSV文件列分隔符
        String CSV_ROW_SEPARATOR = "\r\n";//CSV文件行分隔符
        // 保证线程安全
        StringBuilder buf = new StringBuilder();
        // 组装表头
        for (String title : columnName) {
            buf.append(title).append(CSV_COLUMN_SEPARATOR);
        }
        buf.append(CSV_ROW_SEPARATOR);
        // 组装数据
        if (CollectionUtils.isNotEmpty(commissionPayoutStatusList)) {
            for (int i = 0; i < commissionPayoutStatusList.size(); i++) {
                CommissionPayoutStatus info = commissionPayoutStatusList.get(i);//遍历每个对象
                buf.append(i + 1).append(CSV_COLUMN_SEPARATOR);
                buf.append(info.getCommissionPayoutYearmonth()).append("\t").append(CSV_COLUMN_SEPARATOR);
                buf.append(info.getCommissionPayoutStatus()).append(CSV_COLUMN_SEPARATOR);
                buf.append(CommonUtil.dateParseString(info.getPredictMonthPeriod(), "yyyy-MM")).append("\t").append(CSV_COLUMN_SEPARATOR);
                buf.append(info.getMonthPeriod()).append(CSV_COLUMN_SEPARATOR);
                buf.append(info.getInsurerName() == null ? "" : info.getInsurerName()).append(CSV_COLUMN_SEPARATOR);
                buf.append("\t").append(info.getPolicyNo() == null ? "" : info.getPolicyNo()).append("\t").append(CSV_COLUMN_SEPARATOR);
                buf.append(info.getOrderPrice() == null ? 0 : info.getOrderPrice()).append(CSV_COLUMN_SEPARATOR);
                buf.append(info.getCommissionAmount() == null ? 0 : info.getCommissionAmount()).append(CSV_COLUMN_SEPARATOR);
                buf.append(info.getCommissionType() == null ? "" : info.getCommissionType()).append(CSV_COLUMN_SEPARATOR);
                buf.append(info.getReferralRate() == null ? 0 : info.getReferralRate()).append(CSV_COLUMN_SEPARATOR);
                buf.append(info.getReferralAmount() == null ? 0 : info.getReferralAmount()).append(CSV_COLUMN_SEPARATOR);
                buf.append(info.getPractitionerName() == null ? "" : info.getPractitionerName()).append(CSV_COLUMN_SEPARATOR);
                buf.append(info.getPractitionerLevel() == null ? "" : info.getPractitionerLevel()).append(CSV_COLUMN_SEPARATOR);
                buf.append(info.getSubordinateSystemName() == null ? "" : info.getSubordinateSystemName()).append(CSV_COLUMN_SEPARATOR);
                buf.append(info.getInsurerBranchDeptName() == null ? "" : info.getInsurerBranchDeptName()).append(CSV_COLUMN_SEPARATOR);
                buf.append(info.getProductName() == null ? "" : info.getProductName()).append(CSV_COLUMN_SEPARATOR);
                buf.append(info.getPaymentTermUnit() == null ? "" : info.getPaymentTermUnit()).append(CSV_COLUMN_SEPARATOR);
                buf.append(info.getCoverTermType() == null ? "" : info.getCoverTermType()).append(CSV_COLUMN_SEPARATOR);
                buf.append(info.getRemark() == null ? "" : info.getRemark()).append(CSV_COLUMN_SEPARATOR);

                buf.append(CSV_ROW_SEPARATOR);
            }
        }
        // 设置文件后缀
        setCSVResponse(response, tableName, buf);
    }

    private void setCSVResponse(HttpServletResponse response, String fn, StringBuilder data) {
        String charset = "UTF-8"; // 读取字符编码
//        String fn = tableName + System.currentTimeMillis()  + ".csv";
        String headStr = "attachment; filename=\"" + fn + "\"";
        // 设置响应
        response.setContentType("APPLICATION/ms-csv.numberformat");
        response.setCharacterEncoding(charset);
        response.setHeader("Content-Disposition", headStr);
        response.setHeader("Cache-Control", "max-age=30");
        response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");
        response.setHeader("Pragma", "public");
        OutputStream os = null;
        try {
            os = response.getOutputStream();
            os.write(data.toString().getBytes("GBK"));
            os.flush();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (os != null) {
                try {
                    os.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    public static void xlsxDownload(Workbook wkb, String headStr, HttpServletResponse response) {
        response.setContentType("application/vnd.ms-excel;charset=UTF-8");
        response.setCharacterEncoding("UTF-8");
        response.setHeader("Content-Disposition", headStr);
        response.setHeader("Cache-Control", "max-age=30");
        response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");
        response.setHeader("Pragma", "public");
        OutputStream os = null;
        try {
            os = response.getOutputStream();
            wkb.write(os);
            os.flush();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (os != null) {
                try {
                    os.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    private void createCSV(List<FortunePayToOrderInfo> fortunePayToOrderInfos, HttpServletResponse response) {
        String charset = "UTF-8"; // 读取字符编码
        String[] columnName = new String[]{"序号", "预计发佣年月", "经纪人", "佣金", "应发佣金", "首年销售佣金(fyc)", "保单号", "保费", "佣金率", "发佣状态", "佣金类型", "手机号", "职级", "分公司", "营业部", "体系", "购买方案"};
        String tableName = "YD_Export_Fortune_Pay";
        String CSV_COLUMN_SEPARATOR = ",";//CSV文件列分隔符
        String CSV_ROW_SEPARATOR = "\r\n";//CSV文件行分隔符
        // 保证线程安全
        StringBuilder buf = new StringBuilder();
        // 组装表头
        for (String title : columnName) {
            buf.append(title).append(CSV_COLUMN_SEPARATOR);
        }
        buf.append(CSV_ROW_SEPARATOR);
        // 组装数据
        if (CollectionUtils.isNotEmpty(fortunePayToOrderInfos)) {
            for (int i = 0; i < fortunePayToOrderInfos.size(); i++) {
                FortunePayToOrderInfo info = fortunePayToOrderInfos.get(i);//遍历每个对象
                buf.append(i + 1).append(CSV_COLUMN_SEPARATOR);
                buf.append(info.getPayoutYearmonth()).append(CSV_COLUMN_SEPARATOR);
                buf.append(info.getPractitionerName()).append(CSV_COLUMN_SEPARATOR);
                buf.append(info.getCommissionAmount() == null ? 0 : info.getCommissionAmount()).append(CSV_COLUMN_SEPARATOR);
                buf.append(info.getReferralAmount() == null ? 0 : info.getReferralAmount()).append(CSV_COLUMN_SEPARATOR);
                buf.append(info.getFycAmount() == null ? 0 : info.getFycAmount()).append(CSV_COLUMN_SEPARATOR);
                buf.append(info.getPolicyNo() == null ? "" : "P_" + info.getPolicyNo()).append(CSV_COLUMN_SEPARATOR);
                buf.append(info.getOrderPrice() == null ? 0 : info.getOrderPrice()).append(CSV_COLUMN_SEPARATOR);
                buf.append(info.getCommissionRate() == null ? 0 : info.getCommissionRate()).append(CSV_COLUMN_SEPARATOR);
                buf.append(info.getCommissionPayoutStatus()).append(CSV_COLUMN_SEPARATOR);
                buf.append(info.getCommissionType() == null ? "" : info.getCommissionType()).append(CSV_COLUMN_SEPARATOR);
                buf.append(info.getMobileNo()).append(CSV_COLUMN_SEPARATOR);
                buf.append(info.getPractitionerLevel() == null ? "" : info.getPractitionerLevel()).append(CSV_COLUMN_SEPARATOR);
                buf.append(info.getInsurerBranchName() == null ? "" : info.getInsurerBranchName()).append(CSV_COLUMN_SEPARATOR);
                buf.append(info.getInsurerBranchDeptName() == null ? "" : info.getInsurerBranchDeptName()).append(CSV_COLUMN_SEPARATOR);
                buf.append(info.getSubordinateSystemName() == null ? "" : info.getSubordinateSystemName()).append(CSV_COLUMN_SEPARATOR);
                buf.append(info.getProductName() == null ? "" : info.getProductName()).append(CSV_COLUMN_SEPARATOR);
                buf.append(CSV_ROW_SEPARATOR);
            }
        }
        // 设置文件后缀
        String fn = tableName + System.currentTimeMillis() + ".csv";
        String headStr = "attachment; filename=\"" + fn + "\"";
        // 设置响应
        response.setContentType("APPLICATION/ms-csv.numberformat");
        response.setCharacterEncoding(charset);
        response.setHeader("Content-Disposition", headStr);
        response.setHeader("Cache-Control", "max-age=30");
        response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");
        response.setHeader("Pragma", "public");
        OutputStream os = null;
        try {
            os = response.getOutputStream();
            os.write(buf.toString().getBytes("GBK"));
            os.flush();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (os != null) {
                try {
                    os.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}
