Java tutorial
/* * 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; } }