/*******************************************************************************
 * Licensed to the Apache Software Foundation (ASF) under one
 * or more contributor license agreements.  See the NOTICE file
 * distributed with this work for additional information
 * regarding copyright ownership.  The ASF licenses this file
 * to you under the Apache License, Version 2.0 (the
 * "License"); you may not use this file except in compliance
 * with the License.  You may obtain a copy of the License at
 *
 * http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing,
 * software distributed under the License is distributed on an
 * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
 * KIND, either express or implied.  See the License for the
 * specific language governing permissions and limitations
 * under the License.
 *******************************************************************************/
package org.apache.ofbiz.pricat.sample;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;
import java.util.Locale;
import java.util.Map;

import org.apache.poi.POIXMLException;
import org.apache.poi.hssf.usermodel.HSSFDataFormatter;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.commons.fileupload.FileItem;
import org.apache.ofbiz.pricat.AbstractPricatParser;
import org.apache.ofbiz.htmlreport.InterfaceReport;
import org.apache.ofbiz.order.finaccount.FinAccountHelper;
import org.apache.ofbiz.base.util.Debug;
import org.apache.ofbiz.base.util.UtilMisc;
import org.apache.ofbiz.base.util.UtilProperties;
import org.apache.ofbiz.base.util.UtilValidate;
import org.apache.ofbiz.entity.Delegator;
import org.apache.ofbiz.entity.GenericEntityException;
import org.apache.ofbiz.entity.GenericValue;
import org.apache.ofbiz.entity.transaction.GenericTransactionException;
import org.apache.ofbiz.service.LocalDispatcher;
import org.apache.ofbiz.service.ServiceUtil;

/**
 * Sample pricat excel parser.
 * 
 */
public class SamplePricatParser extends AbstractPricatParser {
    
    public static final String module = SamplePricatParser.class.getName();

    public static final Map<String, List<Object[]>> ColNamesList = UtilMisc.toMap("V1.1", genExcelHeaderNames("V1.1"));

    public static final int headerRowNo = 4;
    
    private List<String> headerColNames = new ArrayList<String>();
    
    public SamplePricatParser(LocalDispatcher dispatcher, Delegator delegator, Locale locale, InterfaceReport report, Map<String, String[]> facilities, File pricatFile, GenericValue userLogin) {
        super(dispatcher, delegator, locale, report, facilities, pricatFile, userLogin);
    }

    /**
     * Parse pricat excel file in xlsx format.
     * 
     */
    public void parsePricatExcel(boolean writeFile) {
        XSSFWorkbook workbook = null;
        try {
            // 1. read the pricat excel file
            FileInputStream is = new FileInputStream(pricatFile);
            
            // 2. use POI to load this bytes
            report.print(UtilProperties.getMessage(resource, "ParsePricatFileStatement", new Object[] { pricatFile.getName() }, locale), InterfaceReport.FORMAT_DEFAULT);
            try {
                workbook = new XSSFWorkbook(is);
                report.println(UtilProperties.getMessage(resource, "ok", locale), InterfaceReport.FORMAT_OK);
            } catch(IOException e) {
                report.println(e);
                report.println(UtilProperties.getMessage(resource, "PricatSuggestion", locale), InterfaceReport.FORMAT_ERROR);
                return;
            } catch(POIXMLException e) {
                report.println(e);
                report.println(UtilProperties.getMessage(resource, "PricatSuggestion", locale), InterfaceReport.FORMAT_ERROR);
                return;
            }
            
            // 3. only first sheet will be parsed
            // 3.1 verify the file has a sheet at least
            formatter = new HSSFDataFormatter(locale);
            isNumOfSheetsOK(workbook);
            
            // 3.2 verify the version is supported
            XSSFSheet sheet = workbook.getSheetAt(0);
            if (!isVersionSupported(sheet)) {
                return;
            }
            
            // 3.3 get currencyId
            existsCurrencyId(sheet);

            // 3.4 verify the table header row is just the same as column names, if not, print error and return
            if (!isTableHeaderMatched(sheet)) {
                return;
            }
            
            // 3.5 verify the first table has 6 rows at least
            containsDataRows(sheet);
            
            if (UtilValidate.isNotEmpty(errorMessages)) {
                report.println(UtilProperties.getMessage(resource, "HeaderContainsError", locale), InterfaceReport.FORMAT_ERROR);
                return;
            }
            
            // 4. parse data
            // 4.1 parse row by row and store the contents into database
            parseRowByRow(sheet);
            if (UtilValidate.isNotEmpty(errorMessages)) {
                report.println(UtilProperties.getMessage(resource, "DataContainsError", locale), InterfaceReport.FORMAT_ERROR);
                if (writeFile) {
                    sequenceNum = report.getSequenceNum();
                    writeCommentsToFile(workbook, sheet);
                }
            }
            
            // 5. clean up the log files and commented Excel files
            cleanupLogAndCommentedExcel();
        } catch (IOException e) {
            report.println(e);
            Debug.logError(e, module);
        } finally {
            if (UtilValidate.isNotEmpty(fileItems)) {
                // remove tmp files
                FileItem fi = null;
                for (int i = 0; i < fileItems.size(); i++) {
                    fi = fileItems.get(i);
                    fi.delete();
                }
            }
            if (workbook != null) {
                try {
                    workbook.close();
                } catch (IOException e) {
                    Debug.logError(e, module);
                }
            }
        }
    }

    public boolean existsCurrencyId(XSSFSheet sheet) {
        report.print(UtilProperties.getMessage(resource, "StartCheckCurrencyId", locale), InterfaceReport.FORMAT_NOTE);
        XSSFCell currencyIdCell = sheet.getRow(2).getCell(1);
        currencyId = currencyIdCell.getStringCellValue().trim().toUpperCase();
        if (UtilValidate.isEmpty(currencyId)) {
            String errorMessage = UtilProperties.getMessage(resource, "CurrencyIdRequired", locale);
            report.println(errorMessage, InterfaceReport.FORMAT_ERROR);
            errorMessages.put(new CellReference(currencyIdCell), errorMessage);
            return false;
        } else {
            try {
                GenericValue currencyUom = delegator.findOne("Uom", UtilMisc.toMap("uomId", currencyId), false);
                if (!"CURRENCY_MEASURE".equals(currencyUom.getString("uomTypeId"))) {
                    String errorMessage = UtilProperties.getMessage(resource, "CurrencyIdNotCurrency", new Object[] {currencyId}, locale);
                    report.println(errorMessage, InterfaceReport.FORMAT_ERROR);
                    errorMessages.put(new CellReference(currencyIdCell), errorMessage);
                    return false;
                }
            } catch(GenericEntityException e) {
                String errorMessage = UtilProperties.getMessage(resource, "CurrencyIdNotFound", new Object[] {currencyId}, locale);
                report.println(errorMessage, InterfaceReport.FORMAT_ERROR);
                errorMessages.put(new CellReference(currencyIdCell), errorMessage);
                return false;
            }
            report.print(UtilProperties.getMessage(resource, "CurrencyIdIs", new Object[] {currencyId}, locale), InterfaceReport.FORMAT_NOTE);
            report.println(" ... " + UtilProperties.getMessage(resource, "ok", locale), InterfaceReport.FORMAT_OK);
        }
        return true;
    }

    public void parseRowByRow(XSSFSheet sheet) {
        int rows = sheet.getPhysicalNumberOfRows();
        List<Object[]> colNames = ColNamesList.get(pricatFileVersion);
        int colNumber = colNames.size();

        int emptyRowStart = -1;
        int emptyRowEnd = -1;
        for (int i = headerRowNo + 1; i < rows; i++) {
            XSSFRow row = sheet.getRow(i);
            if (UtilValidate.isEmpty(row) || isEmptyRow(row, colNumber, false)) {
                if (emptyRowStart == -1) {
                    report.print("(" + (i + 1) + ") ", InterfaceReport.FORMAT_NOTE);
                    emptyRowStart = i;
                } else {
                    emptyRowEnd = i;
                }
                continue;
            } else {
                if (emptyRowStart != -1) {
                    if (emptyRowEnd != -1) {
                        report.print(" - (" + (emptyRowEnd + 1) + ") ", InterfaceReport.FORMAT_NOTE);
                    }
                    report.print(UtilProperties.getMessage(resource, "ExcelEmptyRow", locale), InterfaceReport.FORMAT_NOTE);
                    report.println(" ... " + UtilProperties.getMessage(resource, "skipped", locale), InterfaceReport.FORMAT_NOTE);
                    emptyRowStart = -1;
                    emptyRowEnd = -1;
                }
            }
            report.print("(" + (i + 1) + ") ", InterfaceReport.FORMAT_NOTE);
            List<Object> cellContents = getCellContents(row, colNames, colNumber);
            try {
                if (parseCellContentsAndStore(row, cellContents)) {
                    report.println(" ... " + UtilProperties.getMessage(resource, "ok", locale), InterfaceReport.FORMAT_OK);
                } else {
                    report.println(" ... " + UtilProperties.getMessage(resource, "skipped", locale), InterfaceReport.FORMAT_NOTE);
                }
            } catch (GenericTransactionException e) {
                report.println(e);
            }
        }
        if (emptyRowEnd != -1) {
            report.print(" - (" + (emptyRowEnd + 1) + ") ", InterfaceReport.FORMAT_NOTE);
            report.print(UtilProperties.getMessage(resource, "ExcelEmptyRow", locale), InterfaceReport.FORMAT_NOTE);
            report.println(" ... " + UtilProperties.getMessage(resource, "skipped", locale), InterfaceReport.FORMAT_NOTE);
        }
    }

    /**
     * Check data according to business logic. If data is ok, store it.
     * 
     * @param row
     * @param cellContents
     * @return
     * @throws GenericTransactionException 
     */
    public boolean parseCellContentsAndStore(XSSFRow row, List<Object> cellContents) throws GenericTransactionException {
        if (UtilValidate.isEmpty(cellContents)) {
            return false;
        }
        switch(pricatFileVersion) {
            case "V1.1":
            default:
                return parseCellContentsAndStoreV1_X(row, cellContents);
        }
    }
    
    private boolean parseCellContentsAndStoreV1_X(XSSFRow row, List<Object> cellContents) throws GenericTransactionException {
        if (UtilValidate.isEmpty(cellContents)) {
            return false;
        }
        // 1. check if facilityId is in the facilities belong to the user, or if the name is correct for the id
        String facilityName = (String) getCellContent(cellContents, "Facility Name");
        String facilityId = (String) getCellContent(cellContents, "FacilityId");
        if (!isFacilityOk(row, facilityName, facilityId)) 
            return false;
        
        // 2. get productCategoryId
        String ownerPartyId = facilities.get(facilityId)[1];
        String productCategoryId = getProductCategoryId(cellContents, ownerPartyId);
        
        // 3. get productFeatureId of brand
        String brandName = (String) getCellContent(cellContents, "Brand");
        String brandId = getBrandId(brandName, ownerPartyId);
        if (UtilValidate.isEmpty(brandId)) {
            return false;
        }

        // 4. get productId from brandId, model name
        String modelName = (String) getCellContent(cellContents, "Style No");
        String productName = (String) getCellContent(cellContents, "Product Name");
        BigDecimal listPrice = (BigDecimal) getCellContent(cellContents, "List Price");
        String productId = getProductId(row, brandId, modelName, productName, productCategoryId, ownerPartyId, listPrice);
        if (UtilValidate.isEmpty(productId) || UtilValidate.isEmpty(listPrice)) {
            return false;
        }

        // 5. update color and size if necessary
        String color = (String) getCellContent(cellContents, "Color");
        if (UtilValidate.isEmpty(color) || UtilValidate.isEmpty(color.trim())) {
            color = defaultColorName;
        }
        String dimension = (String) getCellContent(cellContents, "Size");
        if (UtilValidate.isEmpty(dimension) || UtilValidate.isEmpty(dimension.trim())) {
            dimension = defaultDimensionName;
        }
        Map<String, Object> features = updateColorAndDimension(productId, ownerPartyId, color, dimension);
        if (ServiceUtil.isError(features)) {
            if (features.containsKey("index") && String.valueOf(features.get("index")).contains("0")) {
                int cell = headerColNames.indexOf("Color");
                XSSFCell colorCell = row.getCell(cell);
                errorMessages.put(new CellReference(colorCell), UtilProperties.getMessage(resource, "PricatColorError", locale));
            }
            if (features.containsKey("index") && String.valueOf(features.get("index")).contains("1")) {
                int cell = headerColNames.indexOf("Size");
                XSSFCell colorCell = row.getCell(cell);
                errorMessages.put(new CellReference(colorCell), UtilProperties.getMessage(resource, "PricatDimensionError", locale));
            }
            return false;
        }
        String colorId = (String) features.get("colorId");
        String dimensionId = (String) features.get("dimensionId");
        
        // 6. update skuIds by productId
        String barcode = (String) getCellContent(cellContents, "Barcode");
        BigDecimal inventory = (BigDecimal) getCellContent(cellContents, "Stock Qty");
        BigDecimal averageCost = (BigDecimal) getCellContent(cellContents, "Average Cost");
        String skuId = updateSku(row, productId, ownerPartyId, facilityId, barcode, inventory, colorId, color, dimensionId, dimension, listPrice, averageCost);
        if (UtilValidate.isEmpty(skuId)) {
            return false;
        }
        
        // 7. store prices
        BigDecimal memberPrice = (BigDecimal) getCellContent(cellContents, "Member Price");
        Map<String, Object> results = updateSkuPrice(skuId, ownerPartyId, memberPrice);
        if (ServiceUtil.isError(results)) {
            return false;
        }
        
        return true;
    }


    public String updateSku(XSSFRow row, String productId, String ownerPartyId, String facilityId, String barcode, BigDecimal inventory,
            String colorId, String color, String dimensionId, String dimension, BigDecimal listPrice, BigDecimal averageCost) {
        return "sampleSkuId";
    }

    public String getProductId(XSSFRow row, String brandId, String modelName, String productName, String productCategoryId, String ownerPartyId, BigDecimal listPrice) {
        return "sampleProductId";
    }

    public Object getCellContent(List<Object> cellContents, String colName) {
        if (UtilValidate.isNotEmpty(headerColNames) && headerColNames.contains(colName)) {
            return cellContents.get(headerColNames.indexOf(colName));
        }
        return null;
    }

    public String getProductCategoryId(List<Object> cellContents, String ownerPartyId) {
        return "sampleProductCategoryId";
    }

    public boolean isFacilityOk(XSSFRow row, String facilityName, String facilityId) {
        if (!facilities.containsKey(facilityId)) {
            if (UtilValidate.isEmpty(facilityId) && facilities.keySet().size() == 1) {
                if (UtilValidate.isEmpty(facilityName)) {
                    return true;
                } else {
                    String theFacilityId = (String) facilities.keySet().toArray()[0];
                    String name = facilities.get(theFacilityId)[0];
                    if (!name.equals(facilityName)) {
                        String errorMessage = UtilProperties.getMessage(resource, "FacilityNameNotMatchId", new Object[]{theFacilityId, name, facilityName}, locale);
                        report.println();
                        report.print(errorMessage, InterfaceReport.FORMAT_ERROR);
                        XSSFCell cell = row.getCell(0);
                        errorMessages.put(new CellReference(cell), errorMessage);
                        return false;
                    }
                }
            } else {
                String errorMessage = UtilProperties.getMessage(resource, "FacilityNotBelongToYou", new Object[]{facilityName, facilityId}, locale);
                report.println();
                report.print(errorMessage, InterfaceReport.FORMAT_ERROR);
                XSSFCell cell = row.getCell(1);
                errorMessages.put(new CellReference(cell), errorMessage);
                return false;
            }
        } else {
            String name = facilities.get(facilityId)[0];
            if (!name.equals(facilityName)) {
                String errorMessage = UtilProperties.getMessage(resource, "FacilityNameNotMatchId", new Object[]{facilityId, name, facilityName}, locale);
                report.println();
                report.print(errorMessage, InterfaceReport.FORMAT_ERROR);
                XSSFCell cell = row.getCell(0);
                errorMessages.put(new CellReference(cell), errorMessage);
                return false;
            }
        }
        return true;
    }

    public boolean isTableHeaderMatched(XSSFSheet sheet) {
        List<Object[]> columnNames = ColNamesList.get(pricatFileVersion);
        short cols = sheet.getRow(headerRowNo).getLastCellNum();
        report.print(UtilProperties.getMessage(resource, "StartCheckHeaderColNum", new Object[] {pricatFileVersion}, locale), InterfaceReport.FORMAT_NOTE);
        if (cols != columnNames.size()) {
            report.print(UtilProperties.getMessage(resource, "HeaderColNumNotMatch", new Object[] {String.valueOf(cols), String.valueOf(columnNames.size())}, locale), InterfaceReport.FORMAT_WARNING);
            if (cols < columnNames.size()) {
                report.println(UtilProperties.getMessage(resource, "HeaderColNumShortThanRequired", new Object[] {String.valueOf(columnNames.size())}, locale), InterfaceReport.FORMAT_ERROR);
                return false;
            } else {
                report.println(UtilProperties.getMessage(resource, "UseHeaderColNum", new Object[] {String.valueOf(columnNames.size())}, locale), InterfaceReport.FORMAT_WARNING);
                cols = (short) columnNames.size();
            }
        } else {
            report.println(UtilProperties.getMessage(resource, "ok", locale), InterfaceReport.FORMAT_OK);
        }
        
        report.print(UtilProperties.getMessage(resource, "StartCheckHeaderColLabel", new Object[] {pricatFileVersion}, locale), InterfaceReport.FORMAT_NOTE);
        boolean foundLabelNotMatch = false;
        for (int i = 0; i < cols; i++) {
            String coltext = sheet.getRow(headerRowNo).getCell(i).getStringCellValue().trim();
            headerColNames.add(coltext);
            Object[] versionColumn = columnNames.get(i);
            if (!coltext.equals(versionColumn[0])) {
                report.println(UtilProperties.getMessage(resource, "HeaderColLabelNotMatch", new Object[] {String.valueOf(headerRowNo + 1), String.valueOf(i + 1), coltext, versionColumn[0]}, locale), InterfaceReport.FORMAT_ERROR);
                foundLabelNotMatch = true;
            } else {
                report.print(" " + coltext, InterfaceReport.FORMAT_NOTE);
                if (i < cols - 1) {
                    report.print(",", InterfaceReport.FORMAT_NOTE);
                }
            }
        }
        if (foundLabelNotMatch) {
            report.println();
            return false;
        }
        report.println(" ... " + UtilProperties.getMessage(resource, "ok", locale), InterfaceReport.FORMAT_OK);
        return true;
    }

    public boolean isVersionSupported(XSSFSheet sheet) {
        report.print(UtilProperties.getMessage(resource, "StartCheckPricatVersion", locale), InterfaceReport.FORMAT_NOTE);
        pricatFileVersion = sheet.getRow(2).getCell(0).getStringCellValue().trim();
        if (ColNamesList.containsKey(pricatFileVersion)) {
            report.print(" " + pricatFileVersion + " ... ", InterfaceReport.FORMAT_NOTE);
            report.println(UtilProperties.getMessage(resource, "ok", locale), InterfaceReport.FORMAT_OK);
        } else {
            report.println(UtilProperties.getMessage(resource, "error", locale), InterfaceReport.FORMAT_ERROR);
            report.println(UtilProperties.getMessage(resource, "PricatVersionNotSupport", new Object[] {pricatFileVersion}, locale), InterfaceReport.FORMAT_ERROR);
            return false;
        }
        return true;
    }

    public boolean containsDataRows(XSSFSheet sheet) {
        int rows = sheet.getPhysicalNumberOfRows();
        if (rows > headerRowNo + 1) {
            report.println(UtilProperties.getMessage(resource, "PricatTableRows", new Object[] {String.valueOf(headerRowNo + 1), String.valueOf(rows - headerRowNo - 1), sheet.getSheetName()}, locale), InterfaceReport.FORMAT_NOTE);
        } else {
            report.println(UtilProperties.getMessage(resource, "PricatNoDataRows", new Object[] {sheet.getSheetName()}, locale), InterfaceReport.FORMAT_ERROR);
            return false;
        }
        return true;
    }

    /**
     * The Object[] have 4 elements, they are:
     * 1. Header Label Name.
     * 2. Cell data type to return.
     * 3. Boolean value to indicate whether the column is required.
     * 4. Boolean value to indicate whether the column is a price when cell data type is BigDecimal, this element is optional.
     * 
     * @param version
     * @return List of Object[]
     */
    private static List<Object[]> genExcelHeaderNames(String version){
        switch (version) {
            case "V1.1":
            default:
                return genExcelHeaderNamesV1_1();
        }
    }

    /**
     * Get V1.1 pricat excel header names and attributes. 
     * 
     * @return list of Object[]
     */
    private static List<Object[]> genExcelHeaderNamesV1_1() {
        List<Object[]> listHeaderName = new ArrayList<Object[]>();
        listHeaderName.add(new Object[] {"Facility Name", 
                                         XSSFCell.CELL_TYPE_STRING,
                                         Boolean.TRUE});
        listHeaderName.add(new Object[] {"FacilityId",
                                         XSSFCell.CELL_TYPE_STRING,
                                         Boolean.TRUE});
        listHeaderName.add(new Object[] {"Category L1",
                                         XSSFCell.CELL_TYPE_STRING,
                                         Boolean.FALSE});
        listHeaderName.add(new Object[] {"Category L2",
                                         XSSFCell.CELL_TYPE_STRING,
                                         Boolean.FALSE});
        listHeaderName.add(new Object[] {"Category L3",
                                         XSSFCell.CELL_TYPE_STRING,
                                         Boolean.FALSE});
        listHeaderName.add(new Object[] {"Category L4",
                                         XSSFCell.CELL_TYPE_STRING,
                                         Boolean.FALSE});
        listHeaderName.add(new Object[] {"Brand",
                                         XSSFCell.CELL_TYPE_STRING,
                                         Boolean.TRUE});
        listHeaderName.add(new Object[] {"Style No",
                                         XSSFCell.CELL_TYPE_STRING,
                                         Boolean.TRUE});
        listHeaderName.add(new Object[] {"Product Name",
                                         XSSFCell.CELL_TYPE_STRING,
                                         Boolean.TRUE});
        listHeaderName.add(new Object[] {"Color",
                                         XSSFCell.CELL_TYPE_STRING,
                                         Boolean.FALSE});
        listHeaderName.add(new Object[] {"Size",
                                         XSSFCell.CELL_TYPE_STRING,
                                         Boolean.FALSE});
        listHeaderName.add(new Object[] {"Barcode",
                                         XSSFCell.CELL_TYPE_STRING,
                                         Boolean.FALSE});
        listHeaderName.add(new Object[] {"Stock Qty",
                                         XSSFCell.CELL_TYPE_NUMERIC,
                                         Boolean.TRUE});
        listHeaderName.add(new Object[] {"Average Cost",
                                         XSSFCell.CELL_TYPE_NUMERIC,
                                         Boolean.TRUE,
                                         Boolean.TRUE});
        listHeaderName.add(new Object[] {"List Price",
                                         XSSFCell.CELL_TYPE_NUMERIC,
                                         Boolean.TRUE,
                                         Boolean.TRUE});
        listHeaderName.add(new Object[] {"Member Price",
                                            XSSFCell.CELL_TYPE_NUMERIC,
                                            Boolean.FALSE,
                                            Boolean.TRUE});
        return listHeaderName;
    }

    @Override
    public void parsePricatExcel() {
        parsePricatExcel(true);
    }

    /**
     * Get data by version definition.
     * 
     * @param row
     * @param colNames 
     * @param size 
     * @return
     */
    public List<Object> getCellContents(XSSFRow row, List<Object[]> colNames, int size) {
        List<Object> results = new ArrayList<Object>();
        boolean foundError = false;
        if (isEmptyRow(row, size, true)) {
            return null;
        }
        
        // check and get data
        for (int i = 0; i < size; i++) {
            XSSFCell cell = null;
            if (row.getPhysicalNumberOfCells() > i) {
                cell = row.getCell(i);
            }
            if (cell == null) {
                if (((Boolean) colNames.get(i)[2]).booleanValue() && (facilities.keySet().size() > 1 || (facilities.keySet().size() == 1 && i >= 2))) {
                    report.print(UtilProperties.getMessage(resource, "ErrorColCannotEmpty", new Object[] {colNames.get(i)[0]}, locale), InterfaceReport.FORMAT_WARNING);
                    cell = row.createCell(i);
                    errorMessages.put(new CellReference(cell), UtilProperties.getMessage(resource, "ErrorColCannotEmpty", new Object[] {colNames.get(i)[0]}, locale));
                    foundError = true;
                    results.add(null);
                    continue;
                } else {
                    cell = row.createCell(i);
                }
            }
            int cellType = cell.getCellType();
            String cellValue = formatter.formatCellValue(cell);
            if (UtilValidate.isNotEmpty(cellValue) && UtilValidate.isNotEmpty(cellValue.trim())) {
                if (cellType == XSSFCell.CELL_TYPE_FORMULA) {
                    try {
                        cellValue = BigDecimal.valueOf(cell.getNumericCellValue()).setScale(FinAccountHelper.decimals, FinAccountHelper.rounding).toString();
                    } catch (IllegalStateException e) {
                        try {
                            cellValue = cell.getStringCellValue();
                        } catch (IllegalStateException e1) {
                            // do nothing
                        }
                    }
                    report.print(((i == 0)?"":", ") + cellValue, InterfaceReport.FORMAT_NOTE);
                } else {
                    report.print(((i == 0)?"":", ") + cellValue, InterfaceReport.FORMAT_NOTE);
                }
            } else {
                report.print(((i == 0)?"":","), InterfaceReport.FORMAT_NOTE);
            }
            if (((Boolean) colNames.get(i)[2]).booleanValue() && UtilValidate.isEmpty(cellValue) && (facilities.keySet().size() > 1 || (facilities.keySet().size() == 1 && i >= 2))) {
                report.print(UtilProperties.getMessage(resource, "ErrorColCannotEmpty", new Object[] {colNames.get(i)[0]}, locale), InterfaceReport.FORMAT_WARNING);
                errorMessages.put(new CellReference(cell), UtilProperties.getMessage(resource, "ErrorColCannotEmpty", new Object[] {colNames.get(i)[0]}, locale));
                foundError = true;
                results.add(null);
                continue;
            }
            if (((Boolean) colNames.get(i)[2]).booleanValue() && cellType != (int) colNames.get(i)[1]) {
                // String warningMessage = "";
                if ((int) colNames.get(i)[1] == XSSFCell.CELL_TYPE_STRING) {
                    if (UtilValidate.isNotEmpty(cellValue) && UtilValidate.isNotEmpty(cellValue.trim())) {
                        results.add(cellValue);
                    } else {
                        results.add(null);
                    }
                } else if ((int) colNames.get(i)[1] == XSSFCell.CELL_TYPE_NUMERIC) {
                    if (cell.getCellType() != XSSFCell.CELL_TYPE_STRING) {
                        cell.setCellType(XSSFCell.CELL_TYPE_STRING);
                    }
                    try {
                        results.add(BigDecimal.valueOf(Double.parseDouble(cell.getStringCellValue())).setScale(FinAccountHelper.decimals, FinAccountHelper.rounding));
                    } catch (NumberFormatException e) {
                        results.add(null);
                        errorMessages.put(new CellReference(cell), UtilProperties.getMessage(resource, "ErrorParseValueToNumeric", locale));
                    }
                }
            } else {
                if (UtilValidate.isEmpty(cellValue) || UtilValidate.isEmpty(cellValue.trim())) {
                    results.add(null);
                    continue;
                }
                if ((int) colNames.get(i)[1] == XSSFCell.CELL_TYPE_STRING) {
                    if (cell.getCellType() == XSSFCell.CELL_TYPE_STRING) {
                        cellValue = cell.getStringCellValue().trim();
                        results.add(cellValue);
                    } else {
                        results.add(cellValue.trim());
                    }
                } else if ((int) colNames.get(i)[1] == XSSFCell.CELL_TYPE_NUMERIC) {
                    if (cell.getCellType() == XSSFCell.CELL_TYPE_STRING) {
                        try {
                            results.add(BigDecimal.valueOf(Double.valueOf(cell.getStringCellValue())));
                        } catch (NumberFormatException e) {
                            results.add(null);
                            errorMessages.put(new CellReference(cell), UtilProperties.getMessage(resource, "ErrorParseValueToNumeric", locale));
                        }
                    } else if (cell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) {
                        try {
                            results.add(BigDecimal.valueOf(cell.getNumericCellValue()).setScale(FinAccountHelper.decimals, FinAccountHelper.rounding));
                        } catch (NumberFormatException e) {
                            results.add(null);
                            errorMessages.put(new CellReference(cell), UtilProperties.getMessage(resource, "ErrorParseValueToNumeric", locale));
                        }
                    } else {
                        try {
                            results.add(BigDecimal.valueOf(Double.valueOf(cellValue)).setScale(FinAccountHelper.decimals, FinAccountHelper.rounding));
                        } catch (NumberFormatException e) {
                            results.add(null);
                            errorMessages.put(new CellReference(cell), UtilProperties.getMessage(resource, "ErrorParseValueToNumeric", locale));
                        }
                    }
                }
            }
        }
        if (foundError) {
            return null;
        }
        return results;
    }

    protected int getHeaderRowNo() {
        return headerRowNo;
    }
}