com.aurel.track.exchange.excel.ExcelFieldMatchBL.java Source code

Java tutorial

Introduction

Here is the source code for com.aurel.track.exchange.excel.ExcelFieldMatchBL.java

Source

/**
 * Genji Scrum Tool and Issue Tracker
 * Copyright (C) 2015 Steinbeis GmbH & Co. KG Task Management Solutions
    
 * <a href="http://www.trackplus.com">Genji Scrum Tool</a>
 *
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program. If not, see <http://www.gnu.org/licenses/>.
 */

/* $Id:$ */

package com.aurel.track.exchange.excel;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;
import java.util.Locale;
import java.util.Map;
import java.util.Set;
import java.util.SortedMap;
import java.util.TreeMap;

import org.apache.commons.lang3.exception.ExceptionUtils;
import org.apache.logging.log4j.Logger;
import org.apache.logging.log4j.LogManager;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.aurel.track.accessControl.AccessBeans;
import com.aurel.track.admin.customize.role.FieldsRestrictionsToRoleBL;
import com.aurel.track.admin.customize.treeConfig.field.FieldBL;
import com.aurel.track.admin.customize.treeConfig.field.FieldConfigBL;
import com.aurel.track.beans.TFieldBean;
import com.aurel.track.beans.TFieldConfigBean;
import com.aurel.track.beans.TReportLayoutBean;
import com.aurel.track.beans.TRoleFieldBean;
import com.aurel.track.fieldType.constants.SystemFields;
import com.aurel.track.fieldType.runtime.base.IFieldTypeRT;
import com.aurel.track.fieldType.runtime.bl.FieldRuntimeBL;
import com.aurel.track.fieldType.types.FieldTypeManager;
import com.aurel.track.resources.LocalizeUtil;
import com.aurel.track.util.GeneralUtils;
import com.aurel.track.util.IntegerStringBean;

public class ExcelFieldMatchBL {

    private static final Logger LOGGER = LogManager.getLogger(ExcelFieldMatchBL.class);

    public static int LOCAL_PARENT_PSEUDO_COLUMN = -1;

    /**
     * Get the workbook and returns its sheets
     * @param excelMappingsDirectory
      * @param fileName
     * @return Map<SheetID, SheetName>
     */
    static Workbook loadWorkbook(String excelMappingsDirectory, String fileName) {
        FileInputStream inputStream = null;
        try {
            inputStream = new FileInputStream(new File(excelMappingsDirectory, fileName));
        } catch (FileNotFoundException e) {
            LOGGER.warn("Loading the workbook from directory " + excelMappingsDirectory + " and file " + fileName
                    + "  failed with " + e.getMessage());
            LOGGER.debug(ExceptionUtils.getStackTrace(e));
            return null;
        }
        try {
            if (fileName.endsWith("xls") || fileName.endsWith("XLS")) {
                return new HSSFWorkbook(inputStream);
            } else {
                if (fileName.endsWith("xlsx") || fileName.endsWith("XLSX")) {
                    return new XSSFWorkbook(inputStream);
                }
            }
        } catch (IOException e) {
            LOGGER.warn("Getting the excel sheets failed with " + e.getMessage());
            LOGGER.debug(ExceptionUtils.getStackTrace(e));
        }
        return null;
    }

    /**
     * Get the workbook and returns its sheets
     * @param workbook
     * @return Map<SheetID, SheetName>
     */
    static List<IntegerStringBean> loadSheetNames(Workbook workbook) {
        List<IntegerStringBean> sheetList = new ArrayList<IntegerStringBean>();
        if (workbook != null) {
            for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
                sheetList.add(new IntegerStringBean(workbook.getSheetName(i), Integer.valueOf(i)));
            }
        }
        return sheetList;
    }

    /**
     * Returns the first row headers (field names) mapped to the column indexes
     * @return  Map<ColumnNumber, ColumnHeader>
     */
    static SortedMap<Integer, String> getFirstRowHeaders(Workbook hSSFWorkbook, Integer sheetID) {
        SortedMap<Integer, String> firstRowMap = new TreeMap<Integer, String>();
        if (hSSFWorkbook == null || sheetID == null) {
            return firstRowMap;
        }
        //first search for duplicate columns
        Set<String> sameColumnNames = new HashSet<String>();
        Set<String> columnNames = new HashSet<String>();
        Sheet sheet = hSSFWorkbook.getSheetAt(sheetID.intValue());
        Row firstRow = sheet.getRow(0);
        if (firstRow != null) {
            for (Cell cell : firstRow) {
                String columnHeader = ExcelImportBL.getStringCellValue(cell);
                if (columnHeader != null && !"".equals(columnHeader)) {
                    if (columnNames.contains(columnHeader)) {
                        sameColumnNames.add(columnHeader);
                    } else {
                        columnNames.add(columnHeader);
                    }
                }
            }
        }
        sheet = hSSFWorkbook.getSheetAt(sheetID.intValue());
        firstRow = sheet.getRow(0);
        if (firstRow != null) {
            for (Cell cell : firstRow) {
                String columnHeader = ExcelImportBL.getStringCellValue(cell);
                if (columnHeader != null && !"".equals(columnHeader)) {
                    if (sameColumnNames.contains(columnHeader)) {
                        //for duplicate columns add also the column index
                        columnHeader += " (" + cell.getColumnIndex() + ")";
                    }
                    firstRowMap.put(Integer.valueOf(cell.getColumnIndex()), columnHeader);
                }
            }
        }
        return firstRowMap;
    }

    /**
     * Returns the first row of a sheet where there is the fields name
     * @return  Map<ColumnNumber, FieldLabelName>
     */
    static SortedMap<Integer, String> getFirstRowNumericToLetter(Workbook hSSFWorkbook, Integer sheetID) {
        SortedMap<Integer, String> firstRowMap = new TreeMap<Integer, String>();
        if (hSSFWorkbook == null || sheetID == null) {
            return firstRowMap;
        }
        Sheet sheet = hSSFWorkbook.getSheetAt(sheetID.intValue());
        Row firstRow = sheet.getRow(0);
        if (firstRow != null) {
            for (Cell cell : firstRow) {
                firstRowMap.put(Integer.valueOf(cell.getColumnIndex()), colNumericToLetter(cell.getColumnIndex()));
            }
        }
        return firstRowMap;
    }

    /**
     * Get the excel letter for the column
     * @param colNum
     * @return
     */
    static String colNumericToLetter(int colNum) {
        StringBuffer sb = new StringBuffer();
        int cycleNum = colNum / 26;
        int withinNum = colNum - (cycleNum * 26);
        if (cycleNum > 0) {
            sb.append((char) ((cycleNum - 1) + 'A'));
        }
        sb.append((char) (withinNum + 'A'));
        return (sb.toString());
    }

    /**
     * Get the localized TFieldConfigBeans
     * @param locale
     * @return
     */
    static List<IntegerStringBean> getFieldConfigs(Integer personID, Locale locale) {
        List<TFieldConfigBean> fieldConfigsList = FieldRuntimeBL.getLocalizedDefaultFieldConfigs(locale);
        List<IntegerStringBean> matchableFieldsList = new LinkedList<IntegerStringBean>();
        //null fieldID: by selecting the empty entry the field be ignored
        matchableFieldsList.add(new IntegerStringBean("-", 0));
        for (TFieldConfigBean fieldConfigBean : fieldConfigsList) {
            Integer fieldID = fieldConfigBean.getField();
            IFieldTypeRT fieldTypeRT = FieldTypeManager.getFieldTypeRT(fieldID);
            if (fieldTypeRT != null && fieldTypeRT.mightMatchExcelColumn()) {
                matchableFieldsList.add(new IntegerStringBean(fieldConfigBean.getLabel(), fieldID));
            }
        }
        List<Integer> fieldIDs = new LinkedList<Integer>();
        fieldIDs.add(FieldsRestrictionsToRoleBL.PSEUDO_COLUMNS.WATCHERS);
        Map<Integer, Integer> fieldRestrictions = AccessBeans.getFieldRestrictions(personID, null, null, fieldIDs,
                false);
        Integer watchersFlag = fieldRestrictions.get(FieldsRestrictionsToRoleBL.PSEUDO_COLUMNS.WATCHERS);
        if (watchersFlag == null || watchersFlag.intValue() == TRoleFieldBean.ACCESSFLAG.READ_WRITE) {
            matchableFieldsList.add(new IntegerStringBean(
                    LocalizeUtil.getLocalizedTextFromApplicationResources(
                            TReportLayoutBean.PSEUDO_COLUMN_LABELS.INFORMANT_LIST, locale),
                    TReportLayoutBean.PSEUDO_COLUMNS.INFORMANT_LIST));
            matchableFieldsList.add(new IntegerStringBean(
                    LocalizeUtil.getLocalizedTextFromApplicationResources(
                            TReportLayoutBean.PSEUDO_COLUMN_LABELS.CONSULTANT_LIST, locale),
                    TReportLayoutBean.PSEUDO_COLUMNS.CONSULTANT_LIST));
        }
        matchableFieldsList.add(new IntegerStringBean(
                LocalizeUtil.getLocalizedTextFromApplicationResources(
                        "admin.actions.importExcel.fieldMatch.hierarchyColumn", locale),
                LOCAL_PARENT_PSEUDO_COLUMN));
        Collections.sort(matchableFieldsList);
        return matchableFieldsList;
    }

    /**
     * Fields which might be used as identifier columns
     * the other are not implemented at the persistence level
     * @return
     */
    static Set<Integer> getPossibleIdentifierFields() {
        Set<Integer> identifierFieldSet = new HashSet<Integer>();
        identifierFieldSet.add(SystemFields.INTEGER_PROJECT);
        identifierFieldSet.add(SystemFields.INTEGER_RELEASESCHEDULED);
        identifierFieldSet.add(SystemFields.INTEGER_RELEASENOTICED);
        identifierFieldSet.add(SystemFields.INTEGER_MANAGER);
        identifierFieldSet.add(SystemFields.INTEGER_RESPONSIBLE);
        identifierFieldSet.add(SystemFields.INTEGER_ORIGINATOR);
        identifierFieldSet.add(SystemFields.INTEGER_CHANGEDBY);
        identifierFieldSet.add(SystemFields.INTEGER_ISSUETYPE);
        identifierFieldSet.add(SystemFields.INTEGER_STATE);
        identifierFieldSet.add(SystemFields.INTEGER_PRIORITY);
        identifierFieldSet.add(SystemFields.INTEGER_SEVERITY);
        identifierFieldSet.add(SystemFields.INTEGER_SYNOPSIS);
        identifierFieldSet.add(SystemFields.INTEGER_CREATEDATE);
        identifierFieldSet.add(SystemFields.INTEGER_STARTDATE);
        identifierFieldSet.add(SystemFields.INTEGER_ENDDATE);
        identifierFieldSet.add(SystemFields.INTEGER_ISSUENO);
        identifierFieldSet.add(SystemFields.INTEGER_BUILD);
        identifierFieldSet.add(SystemFields.INTEGER_SUBMITTEREMAIL);
        identifierFieldSet.add(SystemFields.INTEGER_SUPERIORWORKITEM);
        return identifierFieldSet;
    }

    /**
     * Fields which if mapped are mandatory identifier fields
     * (their values can't be changed)
     * @return
     */
    static Set<Integer> getMandatoryIdentifierFields() {
        Set<Integer> mandatoryIdentifierFields = new HashSet<Integer>();
        mandatoryIdentifierFields.add(SystemFields.INTEGER_ORIGINATOR);
        //mandatoryIdentifierFields.add(SystemFields.INTEGER_CREATEDATE);
        mandatoryIdentifierFields.add(SystemFields.INTEGER_ISSUENO);
        return mandatoryIdentifierFields;
    }

    /**
     * Get a label based map with the localized default field configs
     * @param locale
     * @return
     */
    private static Map<String, TFieldConfigBean> getLocalizedDefaultFieldConfigsMap(Locale locale) {
        return getLabelBasedFieldConfigsMap(FieldRuntimeBL.getLocalizedDefaultFieldConfigs(locale));
    }

    /**
     * Get a label based map with the not localized default field configs
     * @return
     */
    private static Map<String, TFieldConfigBean> getDefaultFieldConfigsMap() {
        return getLabelBasedFieldConfigsMap(FieldConfigBL.loadDefault());
    }

    /**
     * Get a label based map of the field configs
     * @param fieldConfigBeansList
     * @return
     */
    private static Map<String, TFieldConfigBean> getLabelBasedFieldConfigsMap(
            List<TFieldConfigBean> fieldConfigBeansList) {
        Map<String, TFieldConfigBean> fieldConfigBeansMap = new HashMap<String, TFieldConfigBean>();
        if (fieldConfigBeansList != null) {
            Iterator<TFieldConfigBean> iterator = fieldConfigBeansList.iterator();
            while (iterator.hasNext()) {
                TFieldConfigBean fieldConfigBean = iterator.next();
                String configLabel = fieldConfigBean.getLabel();
                if (configLabel != null) {
                    fieldConfigBeansMap.put(configLabel, fieldConfigBean);
                }
            }
        }
        return fieldConfigBeansMap;
    }

    /**
     * Get a label based map of all defined fields
     * @return
     */
    private static Map<String, TFieldConfigBean> getFieldNameBasedFieldConfigsMap() {
        Map<String, TFieldConfigBean> fieldConfigBeansMap = new HashMap<String, TFieldConfigBean>();
        Map<Integer, TFieldBean> fieldMap = GeneralUtils.createMapFromList(FieldBL.loadAll());
        List<TFieldConfigBean> fieldConfigBeansList = FieldConfigBL.loadDefault();
        Iterator<TFieldConfigBean> iterator = fieldConfigBeansList.iterator();
        while (iterator.hasNext()) {
            TFieldConfigBean fieldConfigBean = iterator.next();
            TFieldBean fieldBean = fieldMap.get(fieldConfigBean.getField());
            if (fieldBean != null) {
                fieldConfigBeansMap.put(fieldBean.getName(), fieldConfigBean);
            }
        }
        return fieldConfigBeansMap;
    }

    static Map<String, Integer> prepareBestMatchByLabel(Set<String> excelColumnNames,
            Map<String, Integer> previousMappings, Locale locale) {
        if (excelColumnNames == null) {
            return previousMappings;
        }
        //do not match the previously matched columns
        excelColumnNames.removeAll(previousMappings.keySet());

        if (!excelColumnNames.isEmpty()) {
            //match by localized config labels
            addMatch(excelColumnNames, getLocalizedDefaultFieldConfigsMap(locale), previousMappings);
        }
        if (!excelColumnNames.isEmpty()) {
            //match by not localized config labels
            addMatch(excelColumnNames, getDefaultFieldConfigsMap(), previousMappings);
        }
        if (!excelColumnNames.isEmpty()) {
            //match by field names
            addMatch(excelColumnNames, getFieldNameBasedFieldConfigsMap(), previousMappings);
        }
        return previousMappings;
    }

    /**
     * Match by fieldConfigBean labels
     * @param excelColumnNames
     * @param fieldConfigsMap
     * @param previousMappings
     */
    private static void addMatch(Set<String> excelColumnNames, Map<String, TFieldConfigBean> fieldConfigsMap,
            Map<String, Integer> previousMappings) {
        if (!excelColumnNames.isEmpty()) {
            Iterator<String> itrExcelColumNames = excelColumnNames.iterator();
            while (itrExcelColumNames.hasNext()) {
                String columName = itrExcelColumNames.next();
                if (fieldConfigsMap.containsKey(columName)) {
                    TFieldConfigBean fieldConfigBean = fieldConfigsMap.get(columName);
                    if (fieldConfigBean != null) {
                        previousMappings.put(columName, fieldConfigBean.getObjectID());
                        itrExcelColumNames.remove();
                    }
                }
            }
        }
    }

    /**
     * Gets a columnIndex to fieldID map
     * @param columNameToFieldIDMap
     * @param columnIndexToColumNameMap
     * @return
     */
    static Map<Integer, Integer> getColumnIndexToFieldIDMap(Map<String, Integer> columNameToFieldIDMap,
            SortedMap<Integer, String> columnIndexToColumNameMap) {
        Map<Integer, Integer> columnIndexToFieldIDMap = new HashMap<Integer, Integer>();
        Iterator<Integer> iterator = columnIndexToColumNameMap.keySet().iterator();
        while (iterator.hasNext()) {
            Integer columnIndex = iterator.next();
            String columnName = columnIndexToColumNameMap.get(columnIndex);
            if (columNameToFieldIDMap.containsKey(columnName)) {
                columnIndexToFieldIDMap.put(columnIndex, columNameToFieldIDMap.get(columnName));
            }
        }
        return columnIndexToFieldIDMap;
    }

    /**
     * Gets a columnName to fieldID map
     * @param columnIndexToFieldIDMap
     * @param columnIndexToColumNameMap
     * @return
     */
    static Map<String, Integer> getColumnNameToFieldIDMap(Map<Integer, Integer> columnIndexToFieldIDMap,
            SortedMap<Integer, String> columnIndexToColumNameMap) {
        Map<String, Integer> columNameToFieldIDMap = new HashMap<String, Integer>();
        Iterator<Integer> iterator = columnIndexToColumNameMap.keySet().iterator();
        while (iterator.hasNext()) {
            Integer columnIndex = iterator.next();
            String columnName = columnIndexToColumNameMap.get(columnIndex);
            if (columnIndexToFieldIDMap.containsKey(columnIndex)) {
                Integer fieldID = columnIndexToFieldIDMap.get(columnIndex);
                if (fieldID != null && fieldID.intValue() != 0) {
                    columNameToFieldIDMap.put(columnName, columnIndexToFieldIDMap.get(columnIndex));
                }
            }
        }
        return columNameToFieldIDMap;
    }
}