/*******************************************************************************
 * 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
	 * @param document
	 * @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;
	}
}