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

import com.github.pagehelper.PageInfo;
import com.google.common.base.Strings;
import com.yd.api.agms.service.AgmsStatisticsService;
import com.yd.api.agms.vo.statistics.*;
import com.yd.api.commission.service.CommissionService;
import com.yd.api.result.CommonResult;
import com.yd.dal.entity.customer.AclPractitioner;
import com.yd.dal.entity.meta.MdDropOptions;
import com.yd.dal.service.customer.AclPractitionerDALService;
import com.yd.dal.service.marketing.MktLeadsPoolDALService;
import com.yd.dal.service.meta.MdDropOptionsDALService;
import com.yd.util.CommonUtil;
import com.yd.util.config.ZHBErrorConfig;
import com.yd.util.page.PageInfoUtils;
import org.apache.commons.collections.CollectionUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.util.*;

@Service("agmsStatementService")
public class AgmsStatisticsServiceImpl implements AgmsStatisticsService {
    public static String CSV_COLUMN_SEPARATOR = ",";//CSV文件列分隔符
    public static String CSV_ROW_SEPARATOR = "\r\n";//CSV文件行分隔符

    @Autowired
    private CommissionService commissionService;
    @Autowired
    private MktLeadsPoolDALService mktLeadsPoolDALService;
    @Autowired
    private MdDropOptionsDALService mdDropOptionsDALService;
    @Autowired
    private AclPractitionerDALService aclPractitionerDALService;
//    @Autowired
//    private MdCityDALService mdCityDALService;
    @Override
    public FinancialStatisticsResponseVO financialPredictStatistics(FinancialStatisticsRequestVO requestVO, HttpServletResponse response) {
        FinancialStatisticsResponseVO responseVO = new FinancialStatisticsResponseVO();
        PageInfo<statementData> pageInfo = requestVO.getPageInfo();
        String inCommissionDateSpan = requestVO.getInCommissionDateSpan();//来佣开始时间
        String outCommissionDateSpan = requestVO.getOutCommissionDateSpan();//发佣开始时间
        String inCommissionStartTime = null;//来佣开始时间
        String inCommissionEndTime = null;//发佣开始时间
        String outCommissionStartTime= null;//来佣开始时间
        String outCommissionEndTime= null;//发佣开始时间
        if(!Strings.isNullOrEmpty(inCommissionDateSpan)){
            String[] inDataSpan = inCommissionDateSpan.split(":");
            if(inDataSpan.length == 1){
                inCommissionStartTime = CommonUtil.dateParseString(CommonUtil.stringParseDate(inDataSpan[0],"yyyy-MM-dd"),"yyyyMM");
            }else if(inDataSpan.length == 2){
                inCommissionStartTime = CommonUtil.dateParseString(CommonUtil.stringParseDate(inDataSpan[0],"yyyy-MM-dd"),"yyyyMM");
                inCommissionEndTime = getTime(inDataSpan[1]);
//                inCommissionEndTime = CommonUtil.dateParseString(CommonUtil.stringParseDate(inDataSpan[1],"yyyy-MM-dd"),"yyyyMM");
            }
        }
        if(!Strings.isNullOrEmpty(outCommissionDateSpan)){
            String[] outDataSpan = outCommissionDateSpan.split(":");
            if(outDataSpan.length == 1){
                outCommissionStartTime = CommonUtil.dateParseString(CommonUtil.stringParseDate(outDataSpan[0],"yyyy-MM-dd"),"yyyyMM");
            }else if(outDataSpan.length == 2){
                outCommissionStartTime = CommonUtil.dateParseString(CommonUtil.stringParseDate(outDataSpan[0],"yyyy-MM-dd"),"yyyyMM");
                outCommissionEndTime = getTime(outDataSpan[1]);
//                outCommissionEndTime = CommonUtil.dateParseString(CommonUtil.stringParseDate(outDataSpan[1],"yyyy-MM-dd"),"yyyyMM");
            }
        }
        String insurerBranchId = requestVO.getInsurerBranchId();//分公司
        String deptId = requestVO.getDeptId();//营业部
        String subordinateId = requestVO.getSubordinateId();//体系
        Integer type = requestVO.getType();
        Integer isStatement = requestVO.getIsStatement();
        if(isStatement == 1){
            List<statementData> dataList = commissionService.getStatementData(isStatement,type, inCommissionStartTime, outCommissionStartTime,inCommissionEndTime,outCommissionEndTime,insurerBranchId,deptId,subordinateId);
            createCSV(type,dataList,response);
        }else{
            PageInfo<statementData> dataPage = commissionService.getStatementDataByPage(pageInfo.getPageNum(),pageInfo.getSize(),isStatement,type, inCommissionStartTime, outCommissionStartTime,inCommissionEndTime,outCommissionEndTime,insurerBranchId,deptId,subordinateId);
            responseVO.setPredictStatementDataList(dataPage);
        }
        responseVO.setCommonResult(new CommonResult(true, ZHBErrorConfig.getErrorInfo("800000")));
        return responseVO;
    }

    @Override
    public LeadsStatisticsResponseVO leadsStatistics(LeadsStatisticsRequestVO requestVO, HttpServletResponse response) {
        LeadsStatisticsResponseVO responseVO = new LeadsStatisticsResponseVO();
        Integer expertApplyStatus = requestVO.getExpertApplyStatus(),leadsStatus = requestVO.getLeadsStatus();
        if(leadsStatus != null){
            leadsStatus = (expertApplyStatus != null) ? 2 : leadsStatus;
        }
        String leadsDateSpan = requestVO.getLeadsDateSpan();
        Integer isStatement = requestVO.getIsStatement();//0=非报表，1=报表
        PageInfo<LeadsStatisticsInfo> requestPage = requestVO.getPageInfo();

        Map<String,String> leadsDataMap = obtainDate(leadsDateSpan);
        String leadsStartTime = leadsDataMap.get("start");
        String leadsEndTime = leadsDataMap.get("end");
        if(isStatement == 1){
            List<LeadsStatisticsInfo> dataList = mktLeadsPoolDALService.findByCondition(isStatement,leadsStatus,expertApplyStatus,leadsStartTime,leadsEndTime);
            getLeadsStatement(dataList,response);
        }else{
            PageInfo<LeadsStatisticsInfo> dataPage = mktLeadsPoolDALService.findByConditionPage(requestPage.getPageNum(),requestPage.getSize(),isStatement,leadsStatus,expertApplyStatus,leadsStartTime,leadsEndTime);
            responseVO.setCommonResult(new CommonResult(true,ZHBErrorConfig.getErrorInfo("800000")));
            responseVO.setStatisticsPageInfo(dataPage);
        }
        return responseVO;
    }

    private void getLeadsStatement(List<LeadsStatisticsInfo> resultList, HttpServletResponse response) {
        String tableName = "YD_leadsStatistics_";
        String[] header = new String[]{"数量","请求SME时间","指派SME时间","SME","SME手机","开谈时间","经纪人","经纪人手机","商机时间","商机姓名","商机手机","商机微信号","年龄","性别","城市","血型","星座","预计FYP","预计FYC","预计件数","成交时间","商机来源","跟进状态"};
        //声明一个工作簿
        HSSFWorkbook workbook = new HSSFWorkbook();
        //生成一个表格，设置表格名称为
        String fn = tableName + System.currentTimeMillis();
        HSSFSheet sheet = workbook.createSheet(fn);
        //设置表格列宽度为10个字节
        sheet.setDefaultColumnWidth(18);
        //创建标题的显示样式
        HSSFCellStyle headerStyle = workbook.createCellStyle();
        headerStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.index);
        headerStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
//        headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//居中
        //创建第一行表头
        HSSFRow headRow = sheet.createRow(0);
        //遍历添加表头(下面模拟遍历学生，也是同样的操作过程)
        HSSFCell cell;
        HSSFRichTextString text;
        for (int i = 0; i < header.length; i++) {
            //创建一个单元格
            cell = headRow.createCell(i);
            //创建一个内容对象
            text = new HSSFRichTextString(header[i]);
            //将内容对象的文字内容写入到单元格中
            cell.setCellValue(text);
            cell.setCellStyle(headerStyle);
        }
        HSSFRow row;
        for(int i = 0; i< resultList.size(); i++) {
            //创建一行
            row = sheet.createRow(i + 1);
            //第1列创建并赋值--数量
            row.createCell(0).setCellValue(new HSSFRichTextString(String.valueOf(i + 1)));
            //第2列创建并赋值--请求SME时间
            row.createCell(1).setCellValue(new HSSFRichTextString(resultList.get(i).getRequestSMEDate()));
            //第3列创建并赋值--指派SME时间
            row.createCell(2).setCellValue(new HSSFRichTextString(resultList.get(i).getAssignSMEDate()));
            //第4列创建并赋值--SME
            row.createCell(3).setCellValue(new HSSFRichTextString(resultList.get(i).getSme()));
            //第5列创建并赋值--SME手机
            row.createCell(4).setCellValue(new HSSFRichTextString(resultList.get(i).getSmeMobileNo()));
            //第6列创建并赋值--开谈时间
            row.createCell(5).setCellValue(new HSSFRichTextString(resultList.get(i).getTalkTime()));
            //第7列创建并赋值--经纪人
            row.createCell(6).setCellValue(new HSSFRichTextString(resultList.get(i).getPractitionerName()));
            //第8列创建并赋值--经纪人手机
            row.createCell(7).setCellValue(new HSSFRichTextString(resultList.get(i).getPractitionerMobileNo()));
            //第9列创建并赋值--商机时间
            row.createCell(8).setCellValue(new HSSFRichTextString(resultList.get(i).getLeadsCreateTime()));
            //第10列创建并赋值--商机姓名
            row.createCell(9).setCellValue(new HSSFRichTextString(resultList.get(i).getLeadsName()));
            //第10列创建并赋值--商机手机号
            row.createCell(10).setCellValue(new HSSFRichTextString(resultList.get(i).getLeadsMobileNo()));
            //第11列创建并赋值--商机微信号
            row.createCell(11).setCellValue(new HSSFRichTextString(resultList.get(i).getLeadsWeChatId()));
            //第12列创建并赋值--年龄
            row.createCell(12).setCellValue(new HSSFRichTextString(resultList.get(i).getLeadsAge()));
            //第13列创建并赋值--性别
            row.createCell(13).setCellValue(new HSSFRichTextString(resultList.get(i).getLeadsGender()));
            //第14列创建并赋值--城市
            row.createCell(14).setCellValue(new HSSFRichTextString(resultList.get(i).getLeadsCity()));
            //第15列创建并赋值--血型
            row.createCell(15).setCellValue(new HSSFRichTextString(resultList.get(i).getLeadsBloodType()));
            //第16列创建并赋值--星座
            row.createCell(16).setCellValue(new HSSFRichTextString(resultList.get(i).getLeadsZodiacType()));
            //第17列创建并赋值--预计FYP
            row.createCell(17).setCellValue(new HSSFRichTextString(CommonUtil.doubleParseString(resultList.get(i).getPredictFYP(), "##,###,###,##0.00")));
            //第18列创建并赋值--预计FYC
            row.createCell(18).setCellValue(new HSSFRichTextString(CommonUtil.doubleParseString(resultList.get(i).getPredictFYC(), "##,###,###,##0.00")));
            //第19列创建并赋值--预计件数
            row.createCell(19).setCellValue(new HSSFRichTextString(CommonUtil.doubleParseString(resultList.get(i).getPredictPieces(), "###########")));
            //第20列创建并赋值--成交时间
            row.createCell(20).setCellValue(new HSSFRichTextString(resultList.get(i).getDealTime()));
            //第21列创建并赋值--商机来源
            row.createCell(21).setCellValue(new HSSFRichTextString(resultList.get(i).getLeadsSource()));
            //第22列创建并赋值--跟进状态
            row.createCell(22).setCellValue(new HSSFRichTextString(resultList.get(i).getLeadsStatus()));
        }
        try {
            //准备将Excel的输出流通过response输出到页面下载
            //八进制输出流
            response.setContentType("application/octet-stream");
            //这后面可以设置导出Excel的名称，此例中名为student.xls
            response.setHeader("Content-disposition", "attachment;filename="+fn+".xls");
            //下载报表
            response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");
            //刷新缓冲
            response.flushBuffer();
            //workbook将Excel写入到response的输出流中，供页面下载
            workbook.write(response.getOutputStream());
        } catch (IOException e) {
            e.printStackTrace();
        }
    }


    /**
     * 按照条件筛选
     * @param resultList 结果
     * @param expertApplyStatus 0/null：全部，1：未申请，2：申请中，3：已指派，
     */
    private List<LeadsStatisticsInfo> screenResult(List<LeadsStatisticsInfo> resultList, Integer expertApplyStatus) {
        List<LeadsStatisticsInfo> isAssignList,unapplyList,applyingList,isAssignExpertList;
        isAssignList = new ArrayList<>();
        for(LeadsStatisticsInfo item : resultList){
            if(item.getAssignId() != null){
                isAssignList.add(item);
            }
        }
        if(expertApplyStatus == null || expertApplyStatus == 0){//已派遣的全部
            return isAssignList;
        }else if(!isAssignList.isEmpty()){
            unapplyList = new ArrayList<>();
            applyingList = new ArrayList<>();
            isAssignExpertList = new ArrayList<>();
            for(LeadsStatisticsInfo item : isAssignList) {
                if (item.getRequestSMEDate() == null) {
                    unapplyList.add(item);//未申请
                }else{
                    if(item.getAssignSMEDate() == null){
                        applyingList.add(item);//申请中
                    }else{
                        isAssignExpertList.add(item);//已派遣专家
                    }
                }
            }
            if(expertApplyStatus == 1){//未申请
                return unapplyList;
            }else if(expertApplyStatus == 2){//申请中
                return applyingList;
            }else if(expertApplyStatus == 3){//已指派
                return isAssignExpertList;
            }else{//全部
                return isAssignList;
            }
        }
        return resultList;
    }

    /**
     * 整合响应信息
     * @param leadsInfos 商机信息
     * @param leadsAssignMap 商机派遣信息
     * @param leadsStatusMap 商机状态信息
     * @param dropMap 基础信息
     * @param practitionerMap 经纪人信息
     * @return 整合信息
     */
    private List<LeadsStatisticsInfo> integrateResponse(List<LeadsStatisticsInfo> leadsInfos, Map<Long, LeadsStatisticsInfo> leadsAssignMap, Map<Long, Long> leadsStatusMap, Map<Long, String> dropMap, Map<Long, AclPractitioner> practitionerMap) {
        List<LeadsStatisticsInfo> resultList = new ArrayList<>();
        Long assignId,leadsCustomerId,smePractitionerId,practitionerId;
        LeadsStatisticsInfo assignInfo;
        AclPractitioner practitioner,smePractitioner;
        for (LeadsStatisticsInfo item : leadsInfos){
            assignId = item.getAssignId();
            leadsCustomerId = item.getLeadsCustomerId();
            item.setLeadsSource(dropMap.get(item.getLeadsSourceId()));
            item.setLeadsStatus(dropMap.get(leadsStatusMap.get(leadsCustomerId)));
            item.setLeadsBloodType(dropMap.get(item.getLeadsBloodTypeId()));
            item.setLeadsZodiacType(dropMap.get(item.getLeadsZodiacTypeId()));
            assignInfo = leadsAssignMap.get(assignId);
            if(assignInfo != null && leadsCustomerId.equals(assignInfo.getLeadsCustomerId())){
                practitionerId = assignInfo.getPractitionerId();
                smePractitionerId = assignInfo.getSmePractitionerId();
                if(practitionerId != null){//经纪人
                    practitioner = practitionerMap.get(practitionerId);
                    item.setPractitionerId(practitionerId);
                    item.setPractitionerName(practitioner.getName());
                    item.setPractitionerMobileNo(practitioner.getMobileNo());
                }
                if(smePractitionerId != null){//专家
                    smePractitioner = practitionerMap.get(smePractitionerId);
                    item.setSmePractitionerId(smePractitionerId);
                    item.setSme(smePractitioner.getName());
                    item.setSmeMobileNo(smePractitioner.getMobileNo());
                }
                item.setRequestSMEDate(assignInfo.getRequestSMEDate());
                item.setAssignSMEDate(assignInfo.getAssignSMEDate());
                item.setSmePractitionerId(assignInfo.getSmePractitionerId());
                item.setPredictFYC(assignInfo.getPredictFYC());
                item.setPredictFYP(assignInfo.getPredictFYP());
                item.setPredictPieces(assignInfo.getPredictPieces());
                item.setDealTime(assignInfo.getDealTime());
            }
            resultList.add(item);
        }
        return resultList;
    }


    //塞选未分配的商机
    private List<LeadsStatisticsInfo> getIsNotAssignLeads(List<LeadsStatisticsInfo> leadsInfos) {
        List<LeadsStatisticsInfo> resultList = new ArrayList<>();
        if(leadsInfos != null && leadsInfos.size() > 0){
            for(LeadsStatisticsInfo item : leadsInfos){
                if(item.getPractitionerId() == null){
                    resultList.add(item);
                }
            }
        }
        return resultList;
    }


    /**
     * 获取查询的起止时间
     * @param dateSpan 时间段
     * @return map
     */
    private Map<String, String> obtainDate(String dateSpan) {
        Map<String, String> dateMap = new HashMap<>();
        String startTime = null,endTime = null;
        if(!Strings.isNullOrEmpty(dateSpan)){
            if(dateSpan.contains(":")){
                if(dateSpan.startsWith(":")){
                    endTime = dateSpan.substring(1);
                }else if(dateSpan.endsWith(":")){
                    startTime = dateSpan.substring(0,dateSpan.length()-1);
                }else{
                    String[] dateArr = dateSpan.split(":");
                    startTime = dateArr[0];
                    endTime = dateArr[1];
                }
            }else if("0".equals(dateSpan)){//今天
                startTime = CommonUtil.dateParseString(new Date(),"yyyy-MM-dd");
                endTime = getSpanDate("+");
            }else if("1".equals(dateSpan)){//昨天
                startTime = getSpanDate("-");
                endTime = CommonUtil.dateParseString(new Date(),"yyyy-MM-dd");
            }
        }
        dateMap.put("start",startTime);
        dateMap.put("end",endTime);
        return dateMap;
    }

    private String getSpanDate(String operate) {
        Calendar calendar = Calendar.getInstance();
        calendar.setTime(new Date());
        if("+".equals(operate)){
            calendar.add(Calendar.DAY_OF_MONTH,1);
        }else{
            calendar.add(Calendar.DAY_OF_MONTH,-1);
        }
        return CommonUtil.dateParseString(calendar.getTime(),"yyyy-MM-dd");
    }

    private String getTime(String time) {
        Date date = CommonUtil.stringParseDate(time,"yyyy-MM-dd");
        if(date != null){
            Calendar calendar = Calendar.getInstance();
            calendar.setTime(date);
            calendar.add(Calendar.DAY_OF_MONTH,-1);
            date = calendar.getTime();
        }
        return CommonUtil.dateParseString(date,"yyyyMM");
    }

    private void createCSV(Integer type, List<statementData> dataList, HttpServletResponse response) {
//        String CSV_COLUMN_SEPARATOR = ",";//CSV文件列分隔符
//        String CSV_ROW_SEPARATOR = "\r\n";//CSV文件行分隔符
        String charset = "UTF-8"; // 读取字符编码
        String[] columnName ;
        String tableName ;
        if(type == 2){
            tableName = "YD_FinancialStatement_predict_";
            columnName = new String[]{"序号","预估来佣年月","预估发佣年月","保单号","分公司","营业部","体系","经纪人姓名","员工编号","职阶","职阶率","保费","FYC","RYC","应收佣金","应发佣金","佣奖类型"};
        }else{
            tableName = "YD_FinancialStatement_actual_";
            columnName = new String[]{"序号","实际来佣年月","实际发佣年月","保单号","分公司","营业部","体系","经纪人姓名","员工编号","职阶","职阶率","保费","FYC","RYC","应收佣金","应发佣金","佣奖类型"};
        }
        // 保证线程安全
        StringBuilder buf = new StringBuilder();
        // 组装表头
        for (String title : columnName) {
            buf.append(title).append(CSV_COLUMN_SEPARATOR);
        }
        buf.append(CSV_ROW_SEPARATOR);
        // 组装数据
        if (CollectionUtils.isNotEmpty(dataList)) {
            for (int i = 0; i < dataList.size(); i++) {
                statementData statementData = dataList.get(i);//遍历每个对象
                buf.append(i + 1).append(CSV_COLUMN_SEPARATOR);
                buf.append(statementData.getInCommissionTime()).append(CSV_COLUMN_SEPARATOR);
                buf.append(statementData.getOutCommissionTime()).append(CSV_COLUMN_SEPARATOR);
                buf.append(statementData.getPolicyNo()+"\t").append(CSV_COLUMN_SEPARATOR);
                buf.append(statementData.getInsurerBranch()).append(CSV_COLUMN_SEPARATOR);
                buf.append(statementData.getDept()).append(CSV_COLUMN_SEPARATOR);
                buf.append(statementData.getSubordinate()).append(CSV_COLUMN_SEPARATOR);
                buf.append(statementData.getPractitioner()).append(CSV_COLUMN_SEPARATOR);
                buf.append(statementData.getPractitionerIdEG()).append(CSV_COLUMN_SEPARATOR);
                buf.append(statementData.getTitle()).append(CSV_COLUMN_SEPARATOR);
                buf.append(statementData.getTitleRate()).append(CSV_COLUMN_SEPARATOR);
                buf.append(statementData.getPremium()).append(CSV_COLUMN_SEPARATOR);
                buf.append(statementData.getFYC()).append(CSV_COLUMN_SEPARATOR);
                buf.append(statementData.getRYC()).append(CSV_COLUMN_SEPARATOR);
                buf.append(statementData.getRightReceiveCommission()).append(CSV_COLUMN_SEPARATOR);
                buf.append(statementData.getRightPayCommission()).append(CSV_COLUMN_SEPARATOR);
                buf.append(statementData.getCommissionType()).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();
                }
            }
        }

    }
}
