package com.chinaitop.depot.intelligent.utils;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletRequest;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @auther mafy
 * @createtime 2017/10/26 0026
 * 导入公共类，直接调用importFile方法即可
 */
@Service
public class ImportServiceImpl {



    public String importFile(MultipartFile file, String fileType, HttpServletRequest request, String[] titleArr, boolean[] emptyArr, List<List<String>> lists, Integer numLines) throws Exception{

        // 服务器路径
        String rootPath = request.getSession().getServletContext().getRealPath("");
        // 文件相对路径
        String relativePath = "/upload/" + fileType;
        //创建临时文件
        createTemp(file,rootPath,relativePath);
        //读取临时文件，并且返回错误信息
        String fileAllpath = rootPath+relativePath+"/"+file.getOriginalFilename();

        String retVal = readExcelValue(fileAllpath,titleArr,emptyArr,lists,numLines);

        return retVal;
    }

    /**
     * 获取第一行的代储点名称和代储库名称
     * @return
     * @throws Exception
     */
    public Map<String, Object> nameData(MultipartFile file, String fileType, HttpServletRequest request) throws Exception{
        Map<String, Object> modelMap = new HashMap<String, Object>();
        // 服务器路径
        String rootPath = request.getSession().getServletContext().getRealPath("");
        // 文件相对路径
        String relativePath = "/upload/" + fileType;
        //读取临时文件，并且返回错误信息
        String fileAllpath = rootPath+relativePath+"/"+file.getOriginalFilename();
        //获取File
        File tempFile = new File(fileAllpath);
        Row row = getWorkbook(tempFile,fileAllpath).getSheetAt(0).getRow(0);
        //第一行第二列，第四列是名称
        modelMap.put("agentName",row.getCell(1).toString());
        modelMap.put("agentDepotName",row.getCell(3).toString());
        //删除临时文件
        if(tempFile.exists()){ tempFile.delete(); }
        return modelMap;
    }

    /**
     * 粮情信息数据
     * @return
     * @throws Exception
     */
    public Map<String, Object> temperData(MultipartFile file, String fileType, HttpServletRequest request) throws Exception{
        Map<String, Object> modelMap = new HashMap<String, Object>();
        // 服务器路径
        String rootPath = request.getSession().getServletContext().getRealPath("");
        // 文件相对路径
        String relativePath = "/upload/" + fileType;
        //读取临时文件，并且返回错误信息
        String fileAllpath = rootPath+relativePath+"/"+file.getOriginalFilename();
        //获取File
        File tempFile = new File(fileAllpath);
        Row row = getWorkbook(tempFile,fileAllpath).getSheetAt(0).getRow(0);
        //第一行第二列，第四列是名称
        modelMap.put("depotName",row.getCell(1).toString());//粮库名称
        modelMap.put("storehouseName",row.getCell(3).toString());//仓房名称
        modelMap.put("warehouseName",row.getCell(5).toString());//货位名称

        row = getWorkbook(tempFile,fileAllpath).getSheetAt(0).getRow(1);
        modelMap.put("houseTemperature",row.getCell(1).toString());//仓温
        modelMap.put("houseHumidity",row.getCell(3).toString());//仓湿
        modelMap.put("gasTemperature",row.getCell(5).toString());//气温

        row = getWorkbook(tempFile,fileAllpath).getSheetAt(0).getRow(2);
        modelMap.put("gasHumidity",row.getCell(1).toString());//气湿
        modelMap.put("detectionTime",row.getCell(3).toString());//检测时间

        //删除临时文件
        if(tempFile.exists()){ tempFile.delete(); }
        return modelMap;
    }

    /**
     * 创建临时文件
     * @param file 上传过来的文件流
     * @param rootPath 根目录
     * @param relativePath 相对路径
     * @throws Exception
     */
    public void createTemp(MultipartFile file, String rootPath, String relativePath) throws Exception{
        // 如路径不存在，则创建它
        File directory = new File(rootPath + relativePath);
        if (!directory.exists()) {
            directory.mkdirs();
        }
        String filePath = relativePath + "/" + file.getOriginalFilename();
        // 复制 文件
        file.transferTo(new File(rootPath + filePath));
    }


    /**
     * 解析Excel里面的数据，目前只支持1个sheet
     * @param fileName
     * @return
     */
    public String readExcelValue(String fileName,String[] titleArr,boolean[] emptyArr,List<List<String>> list,Integer numLines){

        //获取File
        File tempFile = new File(fileName);

        StringBuilder errorMSG = new StringBuilder("");//错误信息收集器
        int sheetNum = 1;//sheet的总数
        //循环每一个sheet
        for (int i = 0; i < sheetNum; i++) {
            String err = everySheet(getWorkbook(tempFile,fileName).getSheetAt(i),titleArr,emptyArr,list,numLines);
            if(ParameterUtil.isnotnull(err)){
                errorMSG.append("第"+(i+1)+"个sheet，名称为："+getWorkbook(tempFile,fileName).getSheetName(i)+"，错误信息："+err);
            }
        }

        if(ParameterUtil.isequal(numLines,0)){
            //删除临时文件
            if(tempFile.exists()){ tempFile.delete(); }
        }

        return errorMSG.toString();
    }


    //获取workbook
    private Workbook getWorkbook(File file , String FileName){
        Workbook wb = null;
        try {
            InputStream is = new FileInputStream(file);
            if(ExcelImportUtils.isExcel2007(FileName))
                wb = new XSSFWorkbook(is);
            else
                wb = new HSSFWorkbook(is);
        } catch (IOException e) {
            e.printStackTrace();
        }
        return wb;
    }


    //遍历每一个sheet，获取值和错误信息
    private String  everySheet(Sheet sheet,String[] titleArr,boolean[] emptyArr,List<List<String>> list,Integer numLines){
        String errorMsg = "", br = "<br/>";
        //得到当前sheet的Excel的行数
        int totalRows=sheet.getPhysicalNumberOfRows();
        //总列数
        int totalCells = 0;
        //得到Excel的列数(前提是有行数)，从第二行算起
        if(totalRows>=2 && sheet.getRow(1+numLines) != null){
            totalCells=sheet.getRow(0+numLines).getPhysicalNumberOfCells();
        }else{
            return "请确认excel的数据";
        }

        if (!ExcelImportUtils.isSame(sheet.getRow(numLines),totalCells,titleArr)){ return "导入excel和模板不一致"; }//判断标题是否正确

        //开始处理数据，循环Excel行数,从第二行开始。标题不入库
        for(int r=(1+numLines);r<totalRows;r++){
            List<String> rowList = new ArrayList<String>();
            String rowMessage = "";
            Row row = sheet.getRow(r);
            if (row == null){
                errorMsg += br+"第"+(r+1+numLines)+"行数据异常，请仔细检查！";
                continue;
            }

            //循环Excel的列
            for(int c = 0; c <totalCells; c++){
                Cell cell = row.getCell(c);
                if(cell == null){
                    rowList.add("");
                }else{
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    String cellVal = cell.getStringCellValue();
                    if((StringUtils.isEmpty(cellVal) && !emptyArr[c])){
                        errorMsg+=br+"第"+(r+1+numLines)+"行，第"+(c+1)+"列数据不可为空或者数据异常";
                        rowList.add("error");
                    }else{
                        //正常数据处理
                        rowList.add(cellVal);
                    }
                }
                if(c == totalCells-1) list.add(rowList);//将每一行的数据添加到list中
            }
        }
        return errorMsg;
    }
}