package com.cftech.common.typeappform.util;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.cftech.common.typeappform.model.TypeAppForm;
import com.cftech.common.typeappform.service.TypeAppFormService;
import com.cftech.core.scope.OrderType;
import com.cftech.core.sql.Conds;
import com.cftech.core.sql.Sort;
import com.cftech.core.util.Constants;
import com.cftech.core.util.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import org.junit.Test;
import org.owasp.esapi.ESAPI;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import javax.servlet.http.HttpServletResponse;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.util.List;

/**
 * @author lisw
 * @program schaeffler
 * @description 促销品统计报表明细单导出Excel
 * @createDate 2020-01-15 21:15:10
 **/
@Component
public class StatisticalDetailListUtil {

    @Autowired
    private TypeAppFormService typeAppFormService;


    private StatisticalCommonExportUtil statisticalCommonExportUtil =null;


    /**
     *促销品 导出单据明细报表  测试方法
     */
    public void exportExcel(String fileName, String sheetName, String startDate, String endDate, HttpServletResponse response) throws IOException {
        // 创建excel文件对象
        HSSFWorkbook wb = new HSSFWorkbook();
        statisticalCommonExportUtil = new StatisticalCommonExportUtil(wb);
        // 创建sheet
        Sheet sheet = wb.createSheet(sheetName);
        //设置列宽
        sheet.setDefaultColumnWidth(17);
        FullDataHeaders(sheet,wb);//填充表头
        mergedRegion(sheet);//合并单元格
        typeAppFormList(startDate,endDate,sheet);
        OutputStream outputStream = response.getOutputStream();
        response.reset();
        response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(fileName,"UTF-8")  + ".xls");
        //定义下载的类型，标明是excel文件
        response.setContentType("application/vnd.ms-excel;charset=UTF-8");
        response.setCharacterEncoding("UTF-8");
        //这时候把创建好的excel写入到输出流
        wb.write(outputStream);
        //养成好习惯，出门记得随手关门
        outputStream.close();
    }

    //填充表头数据
    private void FullDataHeaders(Sheet sheet,HSSFWorkbook wb){
        String headersRowsFirst [] = {
                "序号","申请单号","申请方相关信息","","","","","","","申请促销品明细"
                ,"","","","","审批状态","发放状态","发放时间","","","发货详情","","",""
                ,"","","管理员发货金额","供应商发货金额","需支付给供应商的金额=供应商发货总金额+物流金额",
                "退库明细","退库总金额","供应商剩余货量明细","供应商剩余库存明细","管理员评价","","","","申请人评价","","","","申请人OPENID"
        };

        String headersRow [] = {"","","申请人","部门","ORG","申请时间","Legal Entity","成本中心","审批人","申请促销品明细"
                ,"申请用途","备注","申请总金额","收货信息","","","管理员发货","供应商发货","完成发货","发货方","发货明细","发货物流信息","发货物流金额"
                ,"发货总金额","管理员发货备注","","","",
                "","","","","整体评价","促销品质量","物流服务","反馈","整体评价","促销品质量","物流服务","反馈",""
        };
        Row row = sheet.createRow(0);
        row.setHeight(statisticalCommonExportUtil.getRowHeight());
        for(int q = 0;q<headersRowsFirst.length;q++){
            {
                Cell cell =row.createCell(q);
                if(q==0 || q==1 || q==14 || q==15 || (q>21 && q<=28) || q==37){
                    //需要合并的单元格,颜色使用绿色
                    cell.setCellStyle(statisticalCommonExportUtil.getGreenCellStyle());
                }else{
                    cell.setCellStyle(statisticalCommonExportUtil.getGoldCellStyle());
                }
                cell.setCellValue(headersRowsFirst[q]);
            }
        }
        Row row1 = sheet.createRow(1);
        row1.setHeight(statisticalCommonExportUtil.getRowHeight());

        for(int k = 0;k<headersRow.length;k++)
        {
            Cell cell =row1.createCell(k);
            cell.setCellValue(headersRow[k]);
            cell.setCellStyle(statisticalCommonExportUtil.getGreenCellStyle());
        }
    }

    //填充数据

    /**
     *
     * @param startDate 申请开始日期
     * @param endDate   申请结束日期
     * @return
     */
    private List<TypeAppForm> typeAppFormList(String startDate,String endDate,Sheet sheet){
        Conds conds = new Conds();
        conds.equal("ca.del_flag", Constants.DEL_FLAG_0);
        conds.greatEqual("ca.create_time",startDate);
        conds.lessEqual("ca.create_time",endDate);
//        conds.equal("ca.issuing_state",3);
        Sort sort = new Sort("ca.create_time", OrderType.ASC);
        List<TypeAppForm> typeAppForms = typeAppFormService.fetchSearchByPage(conds,sort,0,0);
        int i = 2;
        for(TypeAppForm typeAppForm : typeAppForms){
           Row row =sheet.createRow(i);
           statisticalCommonExportUtil.createCell(0,typeAppForm.getSerialNumber(),row);
           statisticalCommonExportUtil.createCell(1,typeAppForm.getNumber(),row);
           statisticalCommonExportUtil.createCell(2,typeAppForm.getName(),row);
           statisticalCommonExportUtil.createCell(3,typeAppForm.getDname(),row);
           statisticalCommonExportUtil.createCell(4,typeAppForm.getOrg(),row);
           statisticalCommonExportUtil.createCell(5,typeAppForm.getCreateTime(),row);
           statisticalCommonExportUtil.createCell(6,typeAppForm.getLegalEntity(),row);
           statisticalCommonExportUtil.createCell(7,typeAppForm.getCostCenter(),row);
           statisticalCommonExportUtil.createCell(8,typeAppForm.getAname(),row);
           statisticalCommonExportUtil.createCell(9,typeAppForm.getItemsName(),row);
           statisticalCommonExportUtil.createCell(10,typeAppForm.getUname(),row);
           statisticalCommonExportUtil.createCell(11,typeAppForm.getRemarks(),row);
           statisticalCommonExportUtil.createThousandBit(12,typeAppForm.getAppPrice(),row, (HSSFWorkbook) sheet.getWorkbook());
           statisticalCommonExportUtil.createCell(13,typeAppForm.getAddress(),row);
           statisticalCommonExportUtil.createCell(14,typeAppForm.getStatusName(),row);
           statisticalCommonExportUtil.createCell(15,typeAppForm.getIssuingStateName(),row);
           statisticalCommonExportUtil.createCell(16,typeAppForm.getManagerGrantTime(),row);
           statisticalCommonExportUtil.createCell(17,typeAppForm.getDisGrantTime(),row);
           statisticalCommonExportUtil.createCell(18,typeAppForm.getAllGrantTime(),row);
            /**
             * 物流信息
             */
            String logistics = "";
            /**
             * 发放方
             */
            String grantSourceName="";
            /**
             * 发放明细
             */
            String grantDetails = "";
            //发货总金额
            String sumGrantPrice = "";
            if(StringUtils.isNotBlank(typeAppForm.getGrantSource())){
                String items = typeAppForm.getItems();
                switch (typeAppForm.getGrantSource()){
                    //仅供应商
                    case "0":
                        if(StringUtils.isNotBlank(typeAppForm.getDisLogisticsOrderInfo())){
                            logistics+=typeAppForm.getDisLogisticsOrderInfo();
                        }
                        grantDetails = statisticalCommonExportUtil.parseItemsSupplierJSON(items);
                        grantSourceName="仅供应商";
                        sumGrantPrice=typeAppForm.getDisTotalPrice();
                        if(StringUtils.isNotBlank(typeAppForm.getTotalPrice())){
                            BigDecimal disBig = new BigDecimal(typeAppForm.getDisTotalPrice());
                            BigDecimal managerBig = new BigDecimal(typeAppForm.getTotalPrice());
                            sumGrantPrice=disBig.add(managerBig).setScale(2, BigDecimal.ROUND_HALF_UP).toString();
                        }
                        break;
                    //仅管理员
                    case "1":
                        if(StringUtils.isNotBlank(typeAppForm.getLogisticsOrderInfo())){
                            logistics+=typeAppForm.getLogisticsOrderInfo();
                        }
                        grantDetails = statisticalCommonExportUtil.parseItemsManagerJSON(items);
                        grantSourceName="仅管理员";
                        sumGrantPrice=typeAppForm.getTotalPrice();
                        break;
                    //供应商加管理员
                    case "2":
                        grantSourceName="供应商+管理员";
                        String managerName = " 管理员:";
                        String supplierName = " 供应商:";
                        if(StringUtils.isNotBlank(typeAppForm.getLogisticsOrderInfo())){
                            logistics+=managerName+typeAppForm.getLogisticsOrderInfo();
                        }
                        if(StringUtils.isNotBlank(typeAppForm.getDisLogisticsOrderInfo())){
                            logistics+=supplierName+typeAppForm.getDisLogisticsOrderInfo();
                        }
                        grantDetails=managerName+statisticalCommonExportUtil.parseItemsManagerJSON(items)+statisticalCommonExportUtil.getLineStr();
                        grantDetails+=supplierName+statisticalCommonExportUtil.parseItemsSupplierJSON(items);
                        BigDecimal disBig = new BigDecimal(typeAppForm.getDisTotalPrice());
                        BigDecimal managerBig = new BigDecimal(typeAppForm.getTotalPrice());
                        sumGrantPrice=disBig.add(managerBig).setScale(2, BigDecimal.ROUND_HALF_UP).toString();
                        break;
                }
            }
            statisticalCommonExportUtil.createCell(19,grantSourceName,row);
            statisticalCommonExportUtil.createCell(20,grantDetails,row);
            statisticalCommonExportUtil.createCell(21,logistics,row);
            //发货物流金额
            String logistacsPriceStr = "";
//            if(StringUtils.isNotBlank(typeAppForm.getTotalLogisticsPrice())){
//                logistacsPriceStr+=" 管理员:"+typeAppForm.getTotalLogisticsPrice();
//            }
            if(StringUtils.isNotBlank(typeAppForm.getDisLogisticsPrice())){
                logistacsPriceStr=typeAppForm.getDisLogisticsPrice();
            }
            statisticalCommonExportUtil.createThousandBit(22,logistacsPriceStr,row, (HSSFWorkbook) sheet.getWorkbook());
            //发货总金额
            statisticalCommonExportUtil.createThousandBit(23,sumGrantPrice,row, (HSSFWorkbook) sheet.getWorkbook());
            //管理员录入数量备注
            statisticalCommonExportUtil.createCell(24,typeAppForm.getManagerSubmitRemarks(),row);
            //管理员发货金额
            statisticalCommonExportUtil.createThousandBit(25,typeAppForm.getTotalPrice(),row, (HSSFWorkbook) sheet.getWorkbook());
            String disTotalPrice = typeAppForm.getDisTotalPrice();
            //供应商发货金额
            statisticalCommonExportUtil.createThousandBit(26  ,disTotalPrice,row, (HSSFWorkbook) sheet.getWorkbook());
            //需要支付给供应商的金额，发放金额+物流金额
            if(StringUtils.isNotBlank(typeAppForm.getDisLogisticsPrice())){
                BigDecimal bigLogisticsDecimal = new BigDecimal(typeAppForm.getDisLogisticsPrice());
                BigDecimal bigGrantDecimal = new BigDecimal(disTotalPrice);
                BigDecimal res = bigGrantDecimal.add(bigLogisticsDecimal).setScale(2, BigDecimal.ROUND_HALF_UP);
                statisticalCommonExportUtil.createThousandBit(27,res,row, (HSSFWorkbook) sheet.getWorkbook());
            }else{
                statisticalCommonExportUtil.createCell(27,null,row);
            }
            //退库明细
            statisticalCommonExportUtil.createCell(28,typeAppForm.getCancelItemsNames(),row);
            //退库总金额
            statisticalCommonExportUtil.createThousandBit(29,typeAppForm.getCancelTotalPrice(),row, (HSSFWorkbook) sheet.getWorkbook());
            //供应商剩余货量明细
            statisticalCommonExportUtil.createCell(30,parseItemsSupplierStockJSON(typeAppForm.getItems()),row);
            //供应上剩余库存金额
            statisticalCommonExportUtil.createCell(31,parseItemsSupplierStockPriceJSON(typeAppForm.getItems()),row);
            //管理员整体评价
            statisticalCommonExportUtil.createCell(32,typeAppForm.getManagerAllEvaluate(),row);
            statisticalCommonExportUtil.createCell(33,typeAppForm.getManagerQualityEvaluate(),row);
            statisticalCommonExportUtil.createCell(34,typeAppForm.getManagerLogisticsEvaluate(),row);
            statisticalCommonExportUtil.createCell(35,typeAppForm.getManagerEvaluateRemarks(),row);
            statisticalCommonExportUtil.createCell(36,typeAppForm.getApplyAllEvaluate(),row);
            statisticalCommonExportUtil.createCell(37,typeAppForm.getApplyQualityEvaluate(),row);
            statisticalCommonExportUtil.createCell(38,typeAppForm.getApplyLogisticsEvaluate(),row);
            statisticalCommonExportUtil.createCell(39,typeAppForm.getApplyEvaluateRemarks(),row);
            statisticalCommonExportUtil.createCell(40,typeAppForm.getOpenid(),row);
           i++;
        }
        return typeAppForms;
    }

    //解析固化ITEMS JSON数据返回String供应单每单之后的库存数量的金额
    public String parseItemsSupplierStockPriceJSON(String items){
        StringBuffer parseBuffer = new StringBuffer();
        JSONArray array = JSON.parseArray(ESAPI.encoder().decodeForHTML(items));
        for(int k =0;k<array.size();k++){
            JSONObject obj = array.getJSONObject(k);
            if(obj.containsKey("afterDisStock")){
                String name = obj.getString("name");
                String count = obj.getString("afterDisStock");
                String price = obj.getString("price");
                BigDecimal priceBig = new BigDecimal(price);
                BigDecimal countBig = new BigDecimal(count);
                BigDecimal bigDecimalT = (priceBig.multiply(countBig)).setScale(2, BigDecimal.ROUND_HALF_UP);
                parseBuffer.append(name+"*"+bigDecimalT.toString()+";"+statisticalCommonExportUtil.getLineStr());
            }
        }
        return parseBuffer.toString();
    }


    //解析固化ITEMS JSON数据返回String供应单每单之后的库存数量
    public String parseItemsSupplierStockJSON(String items){
        StringBuffer parseBuffer = new StringBuffer();
        JSONArray array = JSON.parseArray(ESAPI.encoder().decodeForHTML(items));
        for(int k =0;k<array.size();k++){
            JSONObject obj = array.getJSONObject(k);
            if(obj.containsKey("afterDisStock")){
                String name = obj.getString("name");
                String count = obj.getString("afterDisStock");
                parseBuffer.append(name+"*"+count+";"+statisticalCommonExportUtil.getLineStr());
            }
        }
        return parseBuffer.toString();
    }










    //表头单元格合并
    private void mergedRegion(Sheet sheet){
        //序号合并
        CellRangeAddress region = new CellRangeAddress(0, 1, 0, 0);
        sheet.addMergedRegion(region);
        //申请单号合并
        CellRangeAddress regionApplyOrder = new CellRangeAddress(0,1,1,1);
        sheet.addMergedRegion(regionApplyOrder);
        //申请单信息合并
        CellRangeAddress applyInfo = new CellRangeAddress(0,0,2,8);
        sheet.addMergedRegion(applyInfo);
        //申请促销品明细合并
        CellRangeAddress itemDetails = new CellRangeAddress(0,0,9,13);
        sheet.addMergedRegion(itemDetails);
        //审批状态
        CellRangeAddress approvalStatus = new CellRangeAddress(0,1,14,14);
        sheet.addMergedRegion(approvalStatus);
        //发放状态
        CellRangeAddress grantStatus = new CellRangeAddress(0,1,15,15);
        sheet.addMergedRegion(grantStatus);
        //发货时间
        CellRangeAddress grantDate = new CellRangeAddress(0,0,16,18);
        sheet.addMergedRegion(grantDate);
        //发货详情
        CellRangeAddress grantDetail = new CellRangeAddress(0,0,19,24);
        sheet.addMergedRegion(grantDetail);
        //供应商发货金额到供应商剩余金额中间的合并
        for(int i =22+3;i<=28+3;i++){
            CellRangeAddress cellRangeAddress = new CellRangeAddress(0,1,i,i);
            sheet.addMergedRegion(cellRangeAddress);
        }
        //管理员评价
        CellRangeAddress managerComment = new CellRangeAddress(0,0,32,35);
        sheet.addMergedRegion(managerComment);
        //供应商评价
        CellRangeAddress supplierComment = new CellRangeAddress(0,0,36,39);
        sheet.addMergedRegion(supplierComment);
        //OPENID
        CellRangeAddress cellRangeAddress = new CellRangeAddress(0,1,40,40);
        sheet.addMergedRegion(cellRangeAddress);
    }
}
