se.nrm.dina.inventory.client.controller.ExcelFileHandler_1.java Source code

Java tutorial

Introduction

Here is the source code for se.nrm.dina.inventory.client.controller.ExcelFileHandler_1.java

Source

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package se.nrm.dina.inventory.client.controller;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.Serializable;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Optional;
import java.util.function.Predicate;
import java.util.stream.Collectors;
import java.util.stream.IntStream;
import javax.enterprise.context.SessionScoped;
import javax.faces.context.FacesContext;
import javax.inject.Inject;
import javax.inject.Named;
import javax.servlet.http.HttpServletRequest;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.record.RecordFormatException;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
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.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.primefaces.event.FileUploadEvent;
import org.primefaces.event.SelectEvent;
import org.primefaces.model.UploadedFile;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import se.nrm.dina.inventory.client.exception.InvalidExcelFileException;
import se.nrm.dina.inventory.client.services.DinaServiceExcelFileUploadClient;
import se.nrm.dina.inventory.client.util.CommonMessages;
import se.nrm.dina.inventory.client.vo.ObservationData;
import se.nrm.dina.inventory.client.vo.ExcelData;
import se.nrm.dina.inventory.client.vo.TaxaData;

/**
 *
 * @author idali
 */
@Named(value = "fileHandler")
@SessionScoped
public class ExcelFileHandler_1 implements Serializable {
    private final Logger logger = LoggerFactory.getLogger(this.getClass());

    private final static String LOCAL_TEMP_FILE_DIRECTORY = "/Users/idali/temp/inventory_temp_file";
    private final static String REMOTE_TEMP_FILE_DIRECTORY = "/home/admin/wildfly-8.1.0-0/excel/";

    private final int COLLECTION_ID_CFILE = 655361;
    private final int COLLECTION_ID_DFILE = 688128;
    private final int DISCIPLINE_ID = 655360;

    private final static String LOCAL_HOST = "localhost";
    private final String TEMP_FILE_PATH;

    private final String C_FILE = "cfile";
    private final String D_FILE = "dfile";

    private final String GENUS = "Genus";
    private final String SPECIES = "Species";
    private final String DET_DATE = "Det. date";
    private final String EVENT_ID = "EventID";
    private final String NOTES = "Notes";
    private final String MALES = "Males";
    private final String FEMALES = "Females";
    private final String TOTAL = "Total";
    private final String TRAP_ID = "TrapID";
    private final String CAT_NUM = "Cat. No";
    private final String STORAGE = "Storage";
    private final String PLACED_STORAGE = "Placed/Storage";
    private final String GUID = "GUID";
    private final String MEDIA = "Media";
    private final String AUTHOR = "Author";
    private final String NOTE_TITLE = "Notes (errors, synonyms, undescribed etc)";
    private final String SOURCE = "Source";
    private final String COMPUTED_TAXON = "Taxon name (computed)";

    private int genusIndex;
    private int speciesIndex;
    private int computedTaxonIndex;
    private int notesIndex;
    private int sourceIndex;
    private int authorIndex;
    private int guidIndex;

    private String genus;
    private String species;
    private int detDate;
    private String eventNotes;
    private int numMales;
    private int numFemales;
    private int total;
    private String storage;
    private String media;
    private int eventId;
    private int ceId;
    private int trapId;
    private String catalognumber;
    private String computedName;

    private String taxonname;
    private String guid;
    private String author;
    private String source;
    private String notes;

    private Map<String, String> computedTaxaMap;

    private Map<String, Integer> matchTaxonMap;

    private Map<String, Integer> parentTaxonMap;

    private String catNumber;
    private List<String> catNumList;

    private final int MAX_NUM_OBSERVATIOB_SHEET_COLUMN = 30;
    private final int MAX_NUM_TAXONLIST_SHEET_COLUMN = 10;

    private final String servername;

    private String excelFile;
    private String fileType;
    private Workbook workbook;

    private boolean isFileValid;

    private int loanNoColumn = -1;
    private int determinerColumn = -1;
    private int submittedDateColumn = -1;
    private int taxonColumn = -1;

    private List<String> errorMsgs;
    private List<String> waringMsgs;
    private Map<String, Integer> obsTitleMap;
    private Map<String, Integer> taxonTitleMap;

    private final List<String> observationDataList_cfile;
    private final List<String> observationDataList_dfile;
    private final List<String> taxonDataList;

    private String taxonIds;
    private String firstCoId;

    private ExcelData data;
    private String taxonFamily;
    private String loanNumber;
    private String dyntaxaDumpDate;
    private String determiner;
    private int determinerId;

    private String computedNameMapKey;

    private List<TaxaData> taxonList;
    private List<TaxaData> missDyntaxaList;
    private List<ObservationData> coDataList;

    private Map<Integer, Integer> ceMap;
    private int selectCollectionId;

    private boolean submitTaxonDisabled;
    private StringBuilder taxonGuidSb;

    @Inject
    private DinaServiceExcelFileUploadClient client;

    @Inject
    private MessageBean msgBean;

    @Inject
    private NavigationController navigate;

    @Inject
    private InventroyController inventroy;

    public ExcelFileHandler_1() {
        logger.info(this.getClass().getName());

        servername = ((HttpServletRequest) FacesContext.getCurrentInstance().getExternalContext().getRequest())
                .getServerName();
        TEMP_FILE_PATH = servername.equals(LOCAL_HOST) ? LOCAL_TEMP_FILE_DIRECTORY : REMOTE_TEMP_FILE_DIRECTORY;

        submitTaxonDisabled = true;

        observationDataList_dfile = new ArrayList();
        observationDataList_dfile.add(GENUS);
        observationDataList_dfile.add(SPECIES);
        observationDataList_dfile.add(DET_DATE);
        observationDataList_dfile.add(NOTES);
        observationDataList_dfile.add(MALES);
        observationDataList_dfile.add(FEMALES);
        observationDataList_dfile.add(CAT_NUM);
        observationDataList_dfile.add(TRAP_ID);
        observationDataList_dfile.add(EVENT_ID);
        observationDataList_dfile.add(MEDIA);

        observationDataList_cfile = new ArrayList();
        observationDataList_cfile.add(GENUS);
        observationDataList_cfile.add(SPECIES);
        observationDataList_cfile.add(DET_DATE);
        observationDataList_cfile.add(NOTES);
        observationDataList_cfile.add(MALES);
        observationDataList_cfile.add(FEMALES);
        observationDataList_cfile.add(TOTAL);
        observationDataList_cfile.add(TRAP_ID);
        observationDataList_cfile.add(EVENT_ID);

        taxonDataList = new ArrayList<>();
        taxonDataList.add(GENUS);
        taxonDataList.add(SPECIES);
        taxonDataList.add(AUTHOR);
        taxonDataList.add(GUID);
        taxonDataList.add(SOURCE);
        taxonDataList.add(COMPUTED_TAXON);

        isFileValid = false;
    }

    /**
     * File uploaded from client.
     *
     * @param event
     */
    public void handleFileUpload(FileUploadEvent event) {
        logger.info("handleFileUpload : {} -- {}", event.getFile(), event.getFile().getFileName());

        errorMsgs = new ArrayList();
        UploadedFile file = event.getFile();
        excelFile = file.getFileName();

        obsTitleMap = new HashMap();
        taxonTitleMap = new HashMap();
        taxonIds = null;
        firstCoId = null;

        isFileValid = false;
        try {
            saveTempFile(file); // save excel file in temp file directory
            buildWorkbook(); // build workbook from excel file  
            validateSheets(); // validate sheets (to verify all the sheets are there)
            readInSheetTitle(1, MAX_NUM_OBSERVATIOB_SHEET_COLUMN); // read in observation sheet titles
            readInSheetTitle(4, MAX_NUM_TAXONLIST_SHEET_COLUMN); // read in taxon list sheet titles
            findFileType(); // define file type (C file or D file) 
            validateObservationSheet(); // verify observation sheet no missing column
            validateTaxonListSheet(); // verify taxon list sheet no missing column 
            isFileValid = true;
        } catch (InvalidExcelFileException e) {
            logger.error(e.getMessage());
            isFileValid = false;
            msgBean.addErrors(CommonMessages.getInstance().FAILED_UPLOAD_EXCEL_FILE, errorMsgs);
        }
    }

    public void validateFile() {
        logger.info("validateFile");

        errorMsgs = new ArrayList<>();
        waringMsgs = new ArrayList();
        try {
            validateMetadata(); // Loan no, Dyntaxa and determiner by must exists    
            validateEventIDs(); // Event ID must exists in database 

            if (fileType.equals(D_FILE)) {
                catNumList = new ArrayList();
                validateCatalogNumbers(); // catalognumber must exists for d file
                validateMedia(); // Media must exists for d file
            }
            readInTaxon();

            if (!waringMsgs.isEmpty()) {
                msgBean.addWarnings("", waringMsgs.subList(0, waringMsgs.size() >= 5 ? 4 : waringMsgs.size()));
            }

            navigate.setSectionStep(110);
        } catch (InvalidExcelFileException e) {
            logger.error(e.getMessage());
            isFileValid = false;
            submitTaxonDisabled = true;
            msgBean.addErrors(CommonMessages.getInstance().FAILED_UPLOAD_EXCEL_FILE, errorMsgs);
        } catch (Exception ex) {
            msgBean.addError(CommonMessages.getInstance().FAILED_UPLOAD_EXCEL_FILE, "Failed to upload excel file.");
        }
    }

    /**
     * Media can not be empty for D-file
     */
    private void validateMedia() {
        Sheet sheet = workbook.getSheetAt(1);
        List<Integer> list = IntStream.range(1, sheet.getLastRowNum() + 1).parallel().mapToObj(i -> sheet.getRow(i))
                .filter(r -> r.getCell(0) != null).filter(r -> r.getCell(obsTitleMap.get(MEDIA)) == null)
                .map(r -> (int) r.getRowNum()).collect(Collectors.toList());
        if (list != null && !list.isEmpty()) {
            errorMsgs.add(CommonMessages.getInstance().buildMessage(list.get(0),
                    CommonMessages.getInstance().INVALID_FILE_ON_ROW, CommonMessages.getInstance().MEDIA_IS_EMPTY));
            throw new InvalidExcelFileException(CommonMessages.getInstance().INVALID_EXCEL_FILE);
        }
    }

    private String getCatNumber(Row row) {
        Cell cell = row.getCell(obsTitleMap.get(CAT_NUM));

        if (cell == null) {
            errorMsgs.add(CommonMessages.getInstance().buildMessage(row.getRowNum() + 1,
                    CommonMessages.getInstance().INVALID_FILE_ON_ROW,
                    CommonMessages.getInstance().CATALOGNUMEBR_IS_EMPTY));
            return null;
        } else {
            catNumber = (cell.getCellType() == Cell.CELL_TYPE_STRING) ? cell.getStringCellValue()
                    : String.valueOf((int) cell.getNumericCellValue());
            if (catNumber.equals("0")) {
                errorMsgs.add(CommonMessages.getInstance().buildMessage(row.getRowNum() + 1,
                        CommonMessages.getInstance().INVALID_FILE_ON_ROW,
                        CommonMessages.getInstance().CATALOGNUMEBR_IS_EMPTY));
                return null;
            }
            if (catNumList.contains(catNumber)) {
                errorMsgs.add(CommonMessages.getInstance().buildMessage(String.valueOf(row.getRowNum() + 1),
                        catNumber, CommonMessages.getInstance().INVALID_FILE_ON_ROW,
                        CommonMessages.getInstance().CATALOGNUMBER, CommonMessages.getInstance().DUPLICATED));
                return null;
            }

            if (!client.isCatalogNumeExistInDb(catNumber, COLLECTION_ID_DFILE)) {
                errorMsgs.add(CommonMessages.getInstance().buildMessage(String.valueOf(row.getRowNum() + 1),
                        catNumber, CommonMessages.getInstance().INVALID_FILE_ON_ROW,
                        CommonMessages.getInstance().CATALOGNUMBER, CommonMessages.getInstance().EXIST_IN_DB));
                return null;
            }
            return catNumber;
        }
    }

    /**
     * To verify catalognumbers is not empty.  This is only apply for d_file
     */
    private void validateCatalogNumbers() {
        logger.info("validateCatalogNumbers");

        catNumList = new ArrayList();
        Sheet sheet = workbook.getSheetAt(1);
        IntStream.range(1, sheet.getLastRowNum() + 1)
                //  .parallel()
                .mapToObj(i -> sheet.getRow(i)).filter(r -> r.getCell(0) != null).map(this::getCatNumber)
                .forEach(c -> {
                    if (c == null) {
                        throw new InvalidExcelFileException(CommonMessages.getInstance().INVALID_EXCEL_FILE);
                    }
                    catNumList.add(c);
                });
    }

    private void validateEventIDs() {
        ceMap = new HashMap();
        Sheet sheet = workbook.getSheetAt(1);
        IntStream.range(1, sheet.getLastRowNum() + 1)
                //                .parallel()
                .mapToObj(i -> sheet.getRow(i)).filter(r -> r.getCell(0) != null)
                .map(r -> r.getCell(obsTitleMap.get(EVENT_ID)))
                .map(c -> c == null ? 0
                        : c.getCellType() == Cell.CELL_TYPE_STRING ? intParse(c.getStringCellValue())
                                : (int) c.getNumericCellValue())
                .forEach(e -> {
                    if (!ceMap.containsKey(e)) {
                        int collectingeventId = client.validateEventByEventId(e, DISCIPLINE_ID);
                        if (collectingeventId != 0) {
                            ceMap.put(e, collectingeventId);
                        } else {
                            errorMsgs.add(CommonMessages.getInstance().buildMessage(e,
                                    CommonMessages.getInstance().EVENT_ID,
                                    CommonMessages.getInstance().MISSING_EVENTID_IN_DB));
                            throw new InvalidExcelFileException(CommonMessages.getInstance().INVALID_EXCEL_FILE);
                        }
                    }
                });
    }

    public void fileTypeChanged() {
        logger.info("fileTypeChanged : {}", fileType);

        String error;
        if (!validateFileType()) {
            if (fileType.equals("cfile")) {
                error = CommonMessages.getInstance().NOT_OBSERVATION_FILE;
            } else {
                error = CommonMessages.getInstance().NOT_SPECIMENS_FILE;
            }
            msgBean.addError(CommonMessages.getInstance().FAILED_UPLOAD_EXCEL_FILE, error);
            isFileValid = false;
        } else {
            isFileValid = true;
        }
    }

    private boolean validateFileType() {
        if (fileType.equals("cfile")) {
            return obsTitleMap.containsKey(TOTAL);
        } else {
            return obsTitleMap.containsKey(CAT_NUM);
        }
    }

    private void validateMetadata() {

        Sheet sheet = workbook.getSheetAt(0);

        IntStream.range(0, 10).parallel().mapToObj(i -> workbook.getSheetAt(0).getRow(3).getCell(i))
                .filter(c -> c != null).forEach(c -> {
                    if (c.getStringCellValue().equals("Loan no")) {
                        loanNoColumn = c.getColumnIndex();
                    }
                    if (c.getStringCellValue().equals("Determined by")) {
                        determinerColumn = c.getColumnIndex();
                    }

                    if (c.getStringCellValue().equals("Submitted date")) {
                        submittedDateColumn = c.getColumnIndex();
                    }

                    if (c.getStringCellValue().equals("Taxon")) {
                        taxonColumn = c.getColumnIndex();
                    }
                });

        if (loanNoColumn == -1) {
            errorMsgs.add(CommonMessages.getInstance().MISSING_LOAN_NUMBER);
            throw new InvalidExcelFileException(CommonMessages.getInstance().MISSING_DYNTAXA_DUMP_DATE);
        }
        if (submittedDateColumn == -1) {
            errorMsgs.add(CommonMessages.getInstance().MISSING_DYNTAXA_DUMP_DATE);
            throw new InvalidExcelFileException(CommonMessages.getInstance().MISSING_DYNTAXA_DUMP_DATE);
        }
        if (determinerColumn == -1) {
            errorMsgs.add(CommonMessages.getInstance().MISSING_DETERMINAER);
            throw new InvalidExcelFileException(CommonMessages.getInstance().MISSING_DETERMINAER);
        }
        if (taxonColumn == -1) {
            errorMsgs.add(CommonMessages.getInstance().MISSING_TAXON);
            throw new InvalidExcelFileException(CommonMessages.getInstance().MISSING_TAXON);
        }

        Row row = sheet.getRow(4);
        Cell cell = row.getCell(taxonColumn);
        if (cell == null) {
            errorMsgs.add(CommonMessages.getInstance().MISSING_TAXON);
            throw new InvalidExcelFileException(CommonMessages.getInstance().MISSING_TAXON);
        }
        taxonFamily = cell.getStringCellValue();

        cell = row.getCell(loanNoColumn);
        if (cell == null) {
            errorMsgs.add(CommonMessages.getInstance().MISSING_LOAN_NUMBER);
            throw new InvalidExcelFileException(CommonMessages.getInstance().MISSING_TAXON);
        }
        loanNumber = cell.getStringCellValue();

        cell = row.getCell(submittedDateColumn);
        if (cell == null) {
            errorMsgs.add(CommonMessages.getInstance().MISSING_DYNTAXA_DUMP_DATE);
            throw new InvalidExcelFileException(CommonMessages.getInstance().MISSING_DYNTAXA_DUMP_DATE);
        }
        dyntaxaDumpDate = (cell.getCellType() == Cell.CELL_TYPE_NUMERIC)
                ? String.valueOf((int) cell.getNumericCellValue())
                : cell.getStringCellValue();
        if (dyntaxaDumpDate == null || dyntaxaDumpDate.isEmpty()) {
            errorMsgs.add(CommonMessages.getInstance().MISSING_DYNTAXA_DUMP_DATE);
            throw new InvalidExcelFileException(CommonMessages.getInstance().MISSING_DYNTAXA_DUMP_DATE);
        }

        cell = row.getCell(determinerColumn);
        if (cell == null || cell.getStringCellValue().isEmpty()) {
            errorMsgs.add(CommonMessages.getInstance().MISSING_DETERMINAER);
            throw new InvalidExcelFileException(CommonMessages.getInstance().MISSING_DETERMINAER);
        }
        determiner = cell.getStringCellValue();
        getDeterminerId(determiner);
        if (determinerId == 0) {
            errorMsgs.add(CommonMessages.getInstance().determinerNotInDB(determiner));
            throw new InvalidExcelFileException(CommonMessages.getInstance().INVALID_EXCEL_FILE);
        }
    }

    private void getDeterminerId(String determiner) {

        if (determiner.contains(",")) {
            String[] agentNames = StringUtils.split(determiner, ",");
            determinerId = client.getDeterminerId(agentNames[agentNames.length - 1].trim(), agentNames[0].trim());
        } else {
            String[] agentNames = StringUtils.split(determiner);
            if (agentNames.length > 1) {
                if (determiner.contains(" van ")) {
                    determinerId = client.getDeterminerId(agentNames[0],
                            agentNames[agentNames.length - 2] + " " + agentNames[agentNames.length - 1]);
                } else {
                    determinerId = client.getDeterminerId(agentNames[0], agentNames[agentNames.length - 1]); //  Search agent by first name and last name
                }
            }
        }
    }

    /**
     * Remove uploaded file
     */
    public void removefile() {
        logger.info("removefile");

        if (excelFile != null) {
            File thisFile = new File(TEMP_FILE_PATH, excelFile);
            if (thisFile.exists()) {
                thisFile.delete();
            }
            excelFile = null;
            workbook = null;
        }

        fileType = null;
        errorMsgs = new ArrayList<>();
        obsTitleMap = new HashMap<>();
        taxonTitleMap = new HashMap();
    }

    public void resetData(boolean removeTaxon) {
        removefile();
        taxonList = new ArrayList();
        missDyntaxaList = new ArrayList();
        coDataList = new ArrayList();
        data = null;
        isFileValid = false;

        if (removeTaxon) {
            if (taxonIds != null && !taxonIds.isEmpty()) {
                client.deleteTaxon(taxonIds);
                taxonIds = null;
            }
        }
    }

    /**
     * saveTempFile to save uploaded file into temp directory
     *
     * @param uploadFile
     * @param errorMsgs
     */
    private void saveTempFile(UploadedFile uploadFile) {
        logger.info("saveTempFile : {} ", uploadFile.getFileName());

        File targetFile;
        String fileName = uploadFile.getFileName();
        try (InputStream initialStream = uploadFile.getInputstream()) {
            byte[] buffer = new byte[initialStream.available()];
            initialStream.read(buffer);

            targetFile = new File(TEMP_FILE_PATH, fileName);
            try (OutputStream outStream = new FileOutputStream(targetFile)) {
                outStream.write(buffer);
                outStream.close();
            }
            initialStream.close();
        } catch (IOException ex) {
            errorMsgs.add(ex.getMessage());
            throw new InvalidExcelFileException(ex.getMessage());
        }
    }

    /**
     * getWorkbook method to build Workbook from excel file
     *
     * @param fileName
     * @param errorMsgs
     * @throws InvalidExcelFileException
     */
    private void buildWorkbook() {
        File file = new File(TEMP_FILE_PATH, excelFile);
        try {
            workbook = WorkbookFactory.create(file);
        } catch (IOException | InvalidFormatException ex) {
            errorMsgs.add(ex.getMessage());
            throw new InvalidExcelFileException(ex.getMessage());
        } catch (RecordFormatException ex) {
            errorMsgs.add("Invalid excel file format.");
            throw new InvalidExcelFileException(ex.getMessage());
        }
    }

    private void validateSheets() {
        if (!workbook.getSheetName(0).equals("Metadata")) {
            errorMsgs.add(CommonMessages.getInstance().MISSING_METADAT_SHEET);
        }
        if (!workbook.getSheetName(1).equals("Observation")) {
            errorMsgs.add(CommonMessages.getInstance().MISSING_OBSERVATION_SHEET);
        }
        if (!workbook.getSheetName(2).equals("Trap")) {
            errorMsgs.add(CommonMessages.getInstance().MISSING_TRAP_SHEET);
        }
        if (!workbook.getSheetName(3).equals("CollectingEvent")) {
            errorMsgs.add(CommonMessages.getInstance().MISSING_COLLECTINGEVENT_SHEET);
        }
        if (!workbook.getSheetName(4).equals("TaxonList")) {
            errorMsgs.add(CommonMessages.getInstance().MISSING_TAXONLIST_SHEET);
        }
    }

    //    private Map<String, String> getTaxaByOldDyntaxaId(int taxaOldId) {
    //        int id = taxaService.getNewTaxonIdByOldTaxonId(taxaOldId);
    //        if(id == 0) {
    //            return null;
    //        }
    //        
    //        return getTaxaByDyntaxaId(id);
    //    }

    private Map<String, String> getTaxaByDyntaxaId(int dyntaxaId) {
        return client.getTaxonDyntaxaId(dyntaxaId, 11);
    }

    private String getCellStringValue(Cell cell) {
        return cell == null ? ""
                : (cell.getCellType() == Cell.CELL_TYPE_NUMERIC ? String.valueOf(getCellIntValue(cell))
                        : cell.getStringCellValue());
    }

    private int getCellIntValue(Cell cell) {
        return cell == null ? 0
                : (cell.getCellType() == Cell.CELL_TYPE_NUMERIC ? (int) cell.getNumericCellValue()
                        : intParse(cell.getStringCellValue()));
    }

    private TaxaData buildTaxaData(Row row) {

        int taxonId = 0;
        int dyntaxaId = 0;
        String dyntaxa = null;
        String dyntaxaGuid = null;

        Map<String, String> dyntaxaMap = new HashMap();
        Cell cell = row.getCell(computedTaxonIndex);
        taxonname = getCellStringValue(cell);

        cell = row.getCell(genusIndex);
        genus = cell.getStringCellValue();

        cell = row.getCell(speciesIndex);
        species = cell.getStringCellValue();

        if (taxonname == null || taxonname.isEmpty()) {
            errorMsgs.add(CommonMessages.getInstance().TAXON_COMPUTED_IS_EMPTY);
            return null;
        } else if (!taxonname.trim().equals((genus + " " + species).trim())) {
            errorMsgs
                    .add("Row " + (row.getRowNum() + 1) + CommonMessages.getInstance().TAXON_COMPUTED_IS_INCORRECT);
            return null;
        }

        cell = row.getCell(authorIndex);
        author = getCellStringValue(cell);

        cell = row.getCell(notesIndex);
        notes = getCellStringValue(cell);

        cell = row.getCell(guidIndex);
        guid = getCellStringValue(cell);

        cell = row.getCell(sourceIndex);
        source = getCellStringValue(cell);

        if (source.equals("Dyntaxa") || (guid != null && guid.contains("dyntaxa"))) {
            if (!guid.isEmpty()) {
                if (guid.contains("://")) {
                    dyntaxaId = Integer.parseInt(StringUtils.substringAfterLast(guid, "/"));
                } else if (guid.contains("urn:")) {
                    dyntaxaId = Integer.parseInt(StringUtils.substringAfterLast(guid, ":"));
                    int rowNumber = row.getRowNum() + 1;
                    waringMsgs.add("Taxon on row number: " + rowNumber
                            + " Taxon guid in taxonlist might be incorrect, please make sure it is right before continue.");
                } else {
                    errorMsgs.add("Unable to read taxon id from guid in taxon list sheet");
                    return null;
                }
                dyntaxaMap = getTaxaByDyntaxaId(dyntaxaId);
            }

            if (dyntaxaMap.isEmpty()) {
                if (dyntaxaId < 6005095) {
                    //                     dyntaxaMap = getTaxaByOldDyntaxaId(dyntaxaId);
                } else {
                    int rowNumber = row.getRowNum() + 1;
                    errorMsgs.add("Taxon on row number: " + rowNumber + " is not in database. ");
                    return null;
                }

                if (dyntaxaMap == null) {
                    int rowNumber = row.getRowNum() + 1;
                    errorMsgs.add("Wrong guid or taxon not in database on row number: " + rowNumber
                            + " in taxon list sheet. ");
                    return null;
                }
                if (dyntaxaMap.isEmpty()) {
                    errorMsgs.add(CommonMessages.getInstance().buildMessage(row.getRowNum() + 1,
                            CommonMessages.getInstance().TAXON_ON_ROW, CommonMessages.getInstance().NOT_IN_DB));
                    return null;
                } else {
                    taxonId = Integer.parseInt(dyntaxaMap.get("taxonId"));
                    dyntaxa = dyntaxaMap.get("taxonName");
                    dyntaxaGuid = dyntaxaMap.get("guid");
                    return null;
                    //                    return new TaxaData(taxonId, replaceSP(taxonname, dyntaxaDumpDate, false), guid, source, author, notes, "Synonym", dyntaxaId, dyntaxa, dyntaxaGuid);
                }
            } else {
                taxonId = Integer.parseInt(dyntaxaMap.get("taxonId"));
                dyntaxa = dyntaxaMap.get("taxonName");
                dyntaxaGuid = dyntaxaMap.get("guid");
                return null;
                //                return new TaxaData(taxonId, replaceSP(taxonname, dyntaxaDumpDate, false), guid, source, author, notes, "Synonym", dyntaxaId, dyntaxa, dyntaxaGuid); 
            }
        } else {
            if (isNameValid(taxonname)) {

                if (taxonname.contains("sp.")) {
                    taxonname = (taxonname.replace("sp.", "")).trim();
                }
                String type = "Synonym";
                dyntaxaMap = client.getDyntaxaByName(taxonname, 11);
                if (dyntaxaMap.isEmpty()) {
                    dyntaxaMap = client.getDyntaxaByName(genus, 11);
                    type = "Parent";
                }

                if (!dyntaxaMap.isEmpty()) {
                    dyntaxaGuid = dyntaxaMap.get("guid");
                    taxonId = Integer.parseInt(dyntaxaMap.get("taxonId"));
                    if (type.equals("Parent")) {
                        dyntaxa = genus + " [" + dyntaxaGuid + "]";
                    } else {
                        dyntaxa = getComputedTaxonNameAndGuid(taxonname, dyntaxaGuid);
                    }
                }

                //                if(!dyntaxaMap.isEmpty()) {
                //                    type = "Synonym"; 
                //                    taxonId = Integer.parseInt(dyntaxaMap.get("taxonId"));
                //                    dyntaxaGuid = dyntaxaMap.get("guid");
                //                    dyntaxa = getComputedTaxonNameAndGuid(taxonname, dyntaxaGuid);
                //                } else {
                //                    dyntaxaMap = client.getDyntaxaByName(genus, 11); 
                //                    if(!dyntaxaMap.isEmpty()) {
                //                        taxonId = Integer.parseInt(dyntaxaMap.get("taxonId"));
                //                        dyntaxa = dyntaxaMap.get("taxonName");
                //                        dyntaxaGuid = dyntaxaMap.get("guid");
                //                    }
                //                }
                return null;
                //                return new TaxaData(taxonId, replaceSP(taxonname, dyntaxaDumpDate, type.equals("Parent")), guid, source, author, notes, type, dyntaxaId, dyntaxa, dyntaxaGuid); 
            } else {
                errorMsgs.add(CommonMessages.getInstance().buildMessage(row.getRowNum() + 1,
                        CommonMessages.getInstance().INVALID_TAXON_NAME_ON_ROW, ""));
                return null;
            }
        }
    }

    private String getComputedTaxonNameAndGuid(String taxonName, String dyntaxaGuid) {
        taxonGuidSb = new StringBuilder();
        taxonGuidSb.append(taxonName);
        taxonGuidSb.append(" [");
        taxonGuidSb.append(dyntaxaGuid);
        taxonGuidSb.append("]");
        return taxonGuidSb.toString();
    }

    /**
     * read in taxon list from excel file
     */
    private void readInTaxon() throws Exception {
        logger.info("readintaxon");

        Sheet sheet = workbook.getSheetAt(4);
        taxonList = new ArrayList();
        missDyntaxaList = new ArrayList();

        int rowEndNumber = sheet.getLastRowNum();

        parentTaxonMap = new HashMap();

        //        Row row;
        //        TaxaData tx;
        //        for(int i = 1; i <= rowEndNumber + 1; i++) {
        //            row = sheet.getRow(i);
        //            if(row.getCell(0) != null && !row.getCell(0).getStringCellValue().isEmpty()) {
        //       
        //                tx = buildTaxaData(row);
        //                logger.info("processed : {}", tx);
        //                if(tx == null) { 
        //                    throw new InvalidExcelFileException(CommonMessages.getInstance().INVALID_EXCEL_FILE);
        //                } else {
        //                    if (tx.getSource().equals("Dyntaxa")) {
        //                        taxonList.add(tx);
        //                    } else {
        //                        missDyntaxaList.add(tx);
        //                    }
        //                }
        //            } else {
        //                break;
        //            }
        //        }

        IntStream.range(1, rowEndNumber + 1)
                //                .parallel()
                .mapToObj(i -> sheet.getRow(i))
                .filter(r -> r.getCell(0) != null && !r.getCell(0).getStringCellValue().isEmpty())
                .map(this::buildTaxaData)
                //                .collect(Collectors.toList()) 
                //                .parallelStream()
                .forEach(t -> {
                    if (t == null) {
                        throw new InvalidExcelFileException(CommonMessages.getInstance().INVALID_EXCEL_FILE);
                    } else {
                        if (t.getSource().equals("Dyntaxa") || (guid != null && guid.contains("dyntaxa"))) {
                            taxonList.add(t);
                        } else {
                            missDyntaxaList.add(t);
                        }
                    }
                });

        Collections.sort(taxonList,
                (TaxaData t1, TaxaData t2) -> t1.getComputedName().compareTo(t2.getComputedName()));
        Collections.sort(missDyntaxaList,
                (TaxaData t1, TaxaData t2) -> t1.getComputedName().compareTo(t2.getComputedName()));

        enableDisableButton();
    }

    /**
     * to validate species name one word, alaphbetical only
     * @param taxaFullName
     * @return 
     */
    private boolean isNameValid(String taxaFullName) {
        String name = StringUtils.substringAfter(taxaFullName, " ");
        if (name != null && !name.isEmpty()) {
            return name.matches("[a-z.]+");
        }
        return true;
    }

    private String replaceSP(String fullName, String dyntaxaDumpDate, boolean isNew) {
        if (fullName.endsWith("sp.")) {
            fullName = StringUtils.substringBefore(fullName, " sp.");
        }
        return isNew ? fullName.trim() : fullName.trim() + "_" + dyntaxaDumpDate;
    }

    private void readInSheetTitle(int numOfSheet, int maxNumColumn) {

        Sheet sheet = workbook.getSheetAt(numOfSheet); // get second sheet (Observation)
        Row row = sheet.getRow(0); // read first row (title)

        IntStream.range(0, maxNumColumn)
                //                        .parallel()
                .filter(i -> row.getCell(i) != null).mapToObj(i -> row.getCell(i)).forEach(c -> {
                    if (numOfSheet == 1) {
                        obsTitleMap.put(c.getStringCellValue(), c.getColumnIndex());
                    } else {
                        if (c.getStringCellValue().isEmpty() && c.getColumnIndex() < 6) {
                            taxonTitleMap.put(NOTE_TITLE, c.getColumnIndex());
                        } else if (StringUtils.containsIgnoreCase(c.getStringCellValue(), "note")) {
                            taxonTitleMap.put(NOTE_TITLE, c.getColumnIndex());
                        } else {
                            taxonTitleMap.put(c.getStringCellValue(), c.getColumnIndex());
                        }
                    }
                });

        if (numOfSheet == 4) {
            genusIndex = taxonTitleMap.get(GENUS);
            speciesIndex = taxonTitleMap.get(SPECIES);
            computedTaxonIndex = taxonTitleMap.get(COMPUTED_TAXON);
            notesIndex = taxonTitleMap.get(NOTE_TITLE);
            sourceIndex = taxonTitleMap.get(SOURCE);
            authorIndex = taxonTitleMap.get(AUTHOR);
            guidIndex = taxonTitleMap.get(GUID);
        }
    }

    private void findFileType() {
        logger.info("findFileType");

        fileType = null;
        if (excelFile.contains("_C_") || excelFile.contains("_C.xls") || excelFile.contains("_C-")
                || excelFile.contains("_C(")) {
            if (obsTitleMap.containsKey(TOTAL)) {
                fileType = C_FILE;
                selectCollectionId = COLLECTION_ID_CFILE;
            }
        } else if (excelFile.contains("_D_") || excelFile.contains("_D.xls") || excelFile.contains("_D-")
                || excelFile.contains("_D(")) {
            if (obsTitleMap.containsKey(CAT_NUM)) {
                fileType = D_FILE;
                selectCollectionId = COLLECTION_ID_DFILE;
            }
        } else {
            errorMsgs.add(CommonMessages.getInstance().UNDEFINED_FILE_TYPE);
            throw new InvalidExcelFileException(CommonMessages.getInstance().INVALID_EXCEL_FILE);
        }
    }

    private void validateObservationSheet() {
        List<String> list = new ArrayList();

        if (fileType == null) {
            errorMsgs.add(CommonMessages.getInstance().UNDEFINED_FILE_TYPE);
            throw new InvalidExcelFileException(CommonMessages.getInstance().INVALID_EXCEL_FILE);
        } else {
            if (fileType.equals(C_FILE)) {
                list.addAll(observationDataList_cfile);
            } else {
                list.addAll(observationDataList_dfile);
            }

            Optional<String> missingTitle = list.parallelStream().filter(t -> !obsTitleMap.containsKey(t))
                    .findAny();

            if (missingTitle.isPresent()) {
                errorMsgs.add(CommonMessages.getInstance().buildMessage(missingTitle.get(),
                        CommonMessages.getInstance().OBSERVATION_COLUMN_NOT_MAPPING,
                        CommonMessages.getInstance().MISSING));
                throw new InvalidExcelFileException(CommonMessages.getInstance().INVALID_EXCEL_FILE);
            }
        }
    }

    Predicate<String> pTitle = t -> !taxonTitleMap.containsKey(t);

    private void validateTaxonListSheet() {

        Optional<String> missingTitle = taxonDataList.parallelStream().filter(t -> !taxonTitleMap.containsKey(t))
                .findAny();

        if (missingTitle.isPresent()) {
            errorMsgs.add(CommonMessages.getInstance().buildMessage(missingTitle.get(),
                    CommonMessages.getInstance().TAXON_COLUMN_NOT_MAPPING, CommonMessages.getInstance().MISSING));
            throw new InvalidExcelFileException(CommonMessages.getInstance().INVALID_EXCEL_FILE);
        }
    }

    public List<String> autoComplete(String query) {
        logger.info("autoComplete");

        matchTaxonMap = client.getMatchedTaxon(query, 11);
        return matchTaxonMap.entrySet().stream().map(e -> e.getKey()).collect(Collectors.toList());
    }

    Predicate<String> enableButton = t -> t == null || t.isEmpty();

    private void enableDisableButton() {
        logger.info("enableDisableButton");
        submitTaxonDisabled = missDyntaxaList.stream().map(t -> t.getComputedName())
                // .anyMatch(t -> t == null || t.isEmpty()); 
                .anyMatch(enableButton);
    }

    public void itemSelect(SelectEvent event) {
        logger.info("itemSelect ");

        String key = event.getObject().toString();
        if (!parentTaxonMap.containsKey(key)) {
            parentTaxonMap.put(key, matchTaxonMap.get(key));
        } else {
            if (parentTaxonMap.get(key) == null) {
                parentTaxonMap.remove(key);
                parentTaxonMap.put(key, matchTaxonMap.get(key));
            }
        }
        enableDisableButton();
    }

    private TaxaData buildTaxaData(TaxaData data) {

        logger.info("buildTaxaData : {}", parentTaxonMap);

        String taxon = data.getComputedName().trim();
        int id = data.getTaxaId();

        if (id == 0) {
            if (parentTaxonMap != null && !parentTaxonMap.isEmpty()) {
                id = parentTaxonMap.get(taxon);
            }
        }
        return null;
        //        return new TaxaData(id, data.getComputedName(), data.getGuid(), data.getSource(), data.getAuthor(),                 
        //                            data.getNotes(), data.getType(), data.getDyntaxaId(), data.getDyntaxaName(), 
        //                            data.getDyntaxaGuid()); 
    }

    //    public void submitTaxon() {
    //        logger.info("submitTaxon");
    //
    //        errorMsgs = new ArrayList();
    //        waringMsgs = new ArrayList();
    //        
    //        List<TaxaData> newMissDyntaxaList = missDyntaxaList.stream()
    //                                        .map(this::buildTaxaData)
    //                                        .collect(Collectors.toList()); 
    //        data = new ExcelData(loanNumber, dyntaxaDumpDate, determiner, excelFile, fileType, determinerId, taxonList, newMissDyntaxaList);
    //          
    //        String result = client.submitTaxon(inventroy.getLoggedInUserId(), data);
    //        if (!result.equals("Error")) {
    //            taxonIds = result.substring(1, result.length() - 1);
    //            try {
    //                readInObservationData();
    //                if (!waringMsgs.isEmpty()) {
    //                    msgBean.addWarnings(excelFile, waringMsgs);
    //                }
    //            } catch (InvalidExcelFileException e) {
    //                msgBean.addErrors(CommonMessages.getInstance().INVALID_EXCEL_FILE, errorMsgs);
    //            }
    //            navigate.setSectionStep(120);
    //        } else {
    //            msgBean.addError(CommonMessages.getInstance().FAILED_UPLOAD_TAXONLIST, CommonMessages.getInstance().FAILED_UPLOAD_TAXONLIST);
    //        }
    //    }

    //    public void submitObservation() {
    //        logger.info("submitObservation");
    //
    //        data = new ExcelData(loanNumber, dyntaxaDumpDate, determiner, excelFile, fileType, determinerId, coDataList);
    //        String result = client.submitObservations(inventroy.getLoggedInUserId(), selectCollectionId, data);
    //
    //        if (!result.equals("Error")) {
    //            firstCoId = result; 
    //            removefile();
    //            navigate.setSectionStep(130);
    //        } else {
    //            msgBean.addError(CommonMessages.getInstance().FAILED_UPLOAD_OBSERVATION_DATA, CommonMessages.getInstance().FAILED_UPLOAD_OBSERVATION_DATA);
    //        }
    //    }

    private int getIndex(String key, Map<String, Integer> map) {
        return map.get(key);
    }

    private ObservationData buildCollectionObjectData(Row row) {
        Cell cell = row.getCell(0);
        genus = cell.getStringCellValue();

        cell = row.getCell(getIndex(SPECIES, obsTitleMap));
        species = getCellStringValue(cell);
        if (species == null || species.isEmpty()) {
            errorMsgs.add(CommonMessages.getInstance().buildMessage(row.getRowNum() + 1,
                    CommonMessages.getInstance().INVALID_FILE_ON_ROW,
                    CommonMessages.getInstance().MISSING_SPECIES));
            return null;
        } else {
            computedNameMapKey = genus + " " + species;
            if (computedTaxaMap.containsKey(computedNameMapKey)) {
                computedName = computedTaxaMap.get(computedNameMapKey);
            } else {
                computedName = getComputedName(genus, species);
                computedTaxaMap.put(computedNameMapKey, computedName);
            }
        }

        cell = row.getCell(getIndex(DET_DATE, obsTitleMap));
        detDate = getCellIntValue(cell);

        cell = row.getCell(getIndex(MALES, obsTitleMap));
        numMales = getCellIntValue(cell);

        cell = row.getCell(getIndex(FEMALES, obsTitleMap));
        numFemales = getCellIntValue(cell);

        if (fileType.equals(D_FILE)) {
            cell = row.getCell(getIndex(CAT_NUM, obsTitleMap));
            catalognumber = getCellStringValue(cell);
            if (catalognumber == null || catalognumber.isEmpty()) {
                errorMsgs.add(CommonMessages.getInstance().buildMessage(row.getRowNum() + 1,
                        CommonMessages.getInstance().INVALID_FILE_ON_ROW,
                        CommonMessages.getInstance().MISSING_CATALOGNUMBER));
            }
        } else {
            cell = row.getCell(getIndex(TOTAL, obsTitleMap));
            total = (cell.getCellType() == Cell.CELL_TYPE_STRING) ? intParse(cell.getStringCellValue())
                    : (int) cell.getNumericCellValue();
        }

        cell = row.getCell(getIndex(NOTES, obsTitleMap));
        eventNotes = getCellStringValue(cell);

        cell = row.getCell(getIndex(TRAP_ID, obsTitleMap));
        trapId = getCellIntValue(cell);

        cell = row.getCell(getIndex(EVENT_ID, obsTitleMap));
        eventId = getCellIntValue(cell);

        if (obsTitleMap.containsKey(STORAGE)) {
            cell = row.getCell(getIndex(STORAGE, obsTitleMap));
        } else if (obsTitleMap.containsKey(PLACED_STORAGE)) {
            cell = row.getCell(getIndex(PLACED_STORAGE, obsTitleMap));
        } else {
            cell = null;
        }

        storage = getCellStringValue(cell);

        cell = row.getCell(getIndex(MEDIA, obsTitleMap));
        media = getCellStringValue(cell);

        ceId = ceMap.get(eventId);
        ObservationData coData = new ObservationData(eventId, ceId, trapId, genus, species, computedName,
                catalognumber, storage, media, String.valueOf(detDate), eventNotes, numMales, numFemales, total);

        if (computedName == null) {
            coDataList.add(0, coData);
            errorMsgs.add(CommonMessages.getInstance().buildInvalidTaxonNotInTaxonListMessage(row.getRowNum() + 1,
                    genus, species));
        } else if (eventId == 0) {
            waringMsgs.add(CommonMessages.getInstance().buildMessage(row.getRowNum() + 1, "Row",
                    CommonMessages.getInstance().MISSING_COLLECTINGEVENT));
            coDataList.add(0, coData);
        } else {
            coDataList.add(coData);
        }
        return coData;
    }

    private void readInObservationData() {
        logger.info("readInObservationData");

        coDataList = new ArrayList();
        Sheet sheet = workbook.getSheetAt(1);
        computedTaxaMap = new HashMap<>();

        boolean isInvalid = IntStream.range(1, sheet.getLastRowNum() + 1)
                //                                                            .parallel()
                .mapToObj(i -> sheet.getRow(i))
                .filter(r -> r.getCell(0) != null && !r.getCell(0).getStringCellValue().isEmpty())
                .map(this::buildCollectionObjectData).anyMatch(d -> d.getComputedName() == null);
        if (isInvalid) {
            throw new InvalidExcelFileException(CommonMessages.getInstance().INVALID_EXCEL_FILE);
        }
    }

    public void deleteUploadedData() {
        logger.info("deleteUploadedData");

        if (firstCoId != null && !firstCoId.isEmpty()) {
            client.deleteCos(firstCoId, data.getExcelfilename());
            firstCoId = null;
        }

        if (taxonIds != null && !taxonIds.isEmpty()) {
            client.deleteTaxon(taxonIds);
            taxonIds = null;
        }
        resetData(false);
        navigate.setSectionStep(100);
    }

    /**
     * Parse String to int.  
     * @param strValue
     * @return int
     */
    private int intParse(String strValue) {
        try {
            return Integer.parseInt(strValue);
        } catch (java.lang.NumberFormatException ex) {
            return 0;
        }
    }

    public boolean getFileInvaild() {
        return !errorMsgs.isEmpty();
    }

    private boolean isValid(String name, List<TaxaData> list) {
        return list.stream().anyMatch(t -> (t.getComputedName().equals(name)));
    }

    private String getComputedName(String genus, String species) {

        StringBuilder sb = new StringBuilder();
        sb.append(genus);
        if (!species.equals("sp.")) {
            sb.append(" ");
            sb.append(species);
        }

        if (isValid(sb.toString(), missDyntaxaList)) {
            return sb.toString();
        } else {
            sb.append("_");
            sb.append(dyntaxaDumpDate);
            if (isValid(sb.toString(), taxonList)) {
                return sb.toString();
            } else if (isValid(sb.toString(), missDyntaxaList)) {
                return sb.toString();
            }
        }
        return null;
    }

    public List<TaxaData> getTaxonList() {
        return taxonList;
    }

    public List<TaxaData> getMissDyntaxaList() {
        return missDyntaxaList;
    }

    public List<ObservationData> getCoDataList() {
        return coDataList;
    }

    public String getDyntaxaDumpDate() {
        return dyntaxaDumpDate;
    }

    public String getDeterminer() {
        return determiner;
    }

    public boolean isIsFileValid() {
        return isFileValid;
    }

    public String getExcelFile() {
        return excelFile;
    }

    public String getFileType() {
        return fileType;
    }

    public void setFileType(String fileType) {
        this.fileType = fileType;
    }

    public boolean isCFile() {
        return data.getFileTpye().equals(C_FILE);
    }

    public String getUploadedFileName() {
        return data.getExcelfilename();
    }

    public String getTaxonFamily() {
        return taxonFamily;
    }

    public int getTotalTaxon() {
        return taxonList.size() + missDyntaxaList.size();
    }

    public int getTotalObservations() {
        return coDataList.size();
    }

    public boolean isSubmitTaxonDisabled() {
        return submitTaxonDisabled;
    }
}