EsvExcelReaderImpl.java

/*
 * Copyright 2010 James Pether Sörling
 *
 * Licensed 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.
 *
 *	$Id$
 *  $HeadURL$
*/
package com.hack23.cia.service.external.esv.impl;

import java.io.IOException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.TreeMap;

import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Component;

import com.hack23.cia.service.external.esv.api.GovernmentBodyAnnualSummary;

/**
 * The Class EsvExcelReaderImpl.
 */
@Component
final class EsvExcelReaderImpl implements EsvExcelReader {

	/** The Constant COMMENT_CELL. */
	private static final int COMMENT_CELL = 9;

	/** The Constant VAT_CELL. */
	private static final int VAT_CELL = 8;

	/** The Constant ANNUAL_HEADCOUNT_CELL. */
	private static final int ANNUAL_HEADCOUNT_CELL = 7;

	/** The Constant HEADCOUNT_CELL. */
	private static final int HEADCOUNT_CELL = 6;

	/** The Constant ORG_NUMBER_CELL. */
	private static final int ORG_NUMBER_CELL = 5;

	/** The Constant MINISTRY_CELL. */
	private static final int MINISTRY_CELL = 4;

	/** The Constant MCODE_CELL. */
	private static final int MCODE_CELL = 3;

	/** The Constant GOVERNMENT_BODY_ID_CELL. */
	private static final int GOVERNMENT_BODY_ID_CELL = 2;

	/** The Constant CONSECUTIVE_NUMBER_CELL. */
	private static final int CONSECUTIVE_NUMBER_CELL = 1;

	/** The Constant NAME_CELL. */
	private static final int NAME_CELL = 0;

	/** The Constant EXPECTED_COLUMN_LENGTH. */
	private static final int EXPECTED_COLUMN_LENGTH = 10;

	/** The Constant LOGGER. */
	private static final Logger LOGGER = LoggerFactory.getLogger(EsvExcelReaderImpl.class);

	/**
	 * Instantiates a new esv excel reader impl.
	 */
	public EsvExcelReaderImpl() {
		super();
	}

	@Override
	public Map<Integer, List<GovernmentBodyAnnualSummary>> getDataPerMinistry(final String name) {
		final Map<Integer, List<GovernmentBodyAnnualSummary>> map = new TreeMap<>();
		try {
			final HSSFWorkbook myWorkBook = new HSSFWorkbook(
					EsvExcelReaderImpl.class.getResourceAsStream("/Myndighetsinformation.xls"));

			for (int sheetNr = 0; sheetNr < myWorkBook.getNumberOfSheets(); sheetNr++) {
				addMinistryPerYearToMap(name, map, myWorkBook.getSheetAt(sheetNr));
			}

			myWorkBook.close();
		} catch (

		final IOException e) {
			LOGGER.warn("Problem loading", e);
		}

		return map;
	}

	/**
	 * Adds the ministry per year to map.
	 *
	 * @param name
	 *            the name
	 * @param map
	 *            the map
	 * @param mySheet
	 *            the my sheet
	 */
	private static void addMinistryPerYearToMap(final String name,
			final Map<Integer, List<GovernmentBodyAnnualSummary>> map, final HSSFSheet mySheet) {
		if (mySheet.getSheetName().chars().allMatch(Character::isDigit)) {

			final int year = Integer.parseInt(mySheet.getSheetName());

			final List<GovernmentBodyAnnualSummary> yearList = new ArrayList<>();
			final Iterator<Row> rowIterator = mySheet.iterator();

			// Skip header row, ignore first
			rowIterator.next();

			while (rowIterator.hasNext()) {
				addGovernmentBodyAnnualSummaryToList(name, year, yearList, rowIterator.next());
			}
			map.put(year, yearList);
		}
	}

	/**
	 * Adds the government body annual summary to list.
	 *
	 * @param name
	 *            the name
	 * @param year
	 *            the year
	 * @param yearList
	 *            the year list
	 * @param row
	 *            the row
	 */
	private static void addGovernmentBodyAnnualSummaryToList(final String name, final int year,
			final List<GovernmentBodyAnnualSummary> yearList, final Row row) {
		if (row.getLastCellNum() == EXPECTED_COLUMN_LENGTH) {

			final GovernmentBodyAnnualSummary governmentBodyAnnualSummary = createGovernmentBodyAnnualSummaryFromRow(
					year, row);

			if (name == null || name.equalsIgnoreCase(governmentBodyAnnualSummary.getMinistry())) {
				yearList.add(governmentBodyAnnualSummary);
			}
		}
	}

	/**
	 * Gets the integer.
	 *
	 * @param str
	 *            the str
	 * @return the integer
	 */
	private static int getInteger(final String str) {
		if (str == null || str.trim().length() == 0) {
			return 0;
		} else {
			return Integer.parseInt(str);
		}
	}

	@Override
	public Map<Integer, GovernmentBodyAnnualSummary> getDataPerGovernmentBody(final String name) {
		final Map<Integer, GovernmentBodyAnnualSummary> map = new TreeMap<>();
		try {
			final HSSFWorkbook myWorkBook = new HSSFWorkbook(
					EsvExcelReaderImpl.class.getResourceAsStream("/Myndighetsinformation.xls"));

			for (int sheetNr = 0; sheetNr < myWorkBook.getNumberOfSheets(); sheetNr++) {
				final HSSFSheet mySheet = myWorkBook.getSheetAt(sheetNr);

				addDataForYearToMap(name, map, mySheet);
			}
			myWorkBook.close();
		} catch (

		final IOException e) {
			LOGGER.warn("Problem loading", e);
		}

		return map;
	}

	/**
	 * Adds the data for year to map.
	 *
	 * @param name
	 *            the name
	 * @param map
	 *            the map
	 * @param mySheet
	 *            the my sheet
	 */
	private static void addDataForYearToMap(final String name, final Map<Integer, GovernmentBodyAnnualSummary> map,
			final HSSFSheet mySheet) {
		if (mySheet.getSheetName().chars().allMatch(Character::isDigit)) {
			final int year = Integer.parseInt(mySheet.getSheetName());
			final Iterator<Row> rowIterator = mySheet.iterator();

			rowIterator.next();

			while (rowIterator.hasNext()) {
				addGovernmentBodyAnnualSummaryToMap(name, map, year, rowIterator.next());
			}
		}
	}

	/**
	 * Adds the government body annual summary to map.
	 *
	 * @param name
	 *            the name
	 * @param map
	 *            the map
	 * @param year
	 *            the year
	 * @param row
	 *            the row
	 */
	private static void addGovernmentBodyAnnualSummaryToMap(final String name, final Map<Integer, GovernmentBodyAnnualSummary> map,
			final int year, final Row row) {
		if (row.getLastCellNum() == EXPECTED_COLUMN_LENGTH) {

			final GovernmentBodyAnnualSummary governmentBodyAnnualSummary = createGovernmentBodyAnnualSummaryFromRow(
					year, row);

			if (name == null || name.equalsIgnoreCase(governmentBodyAnnualSummary.getName())) {
				map.put(year, governmentBodyAnnualSummary);
			}
		}
	}

	/**
	 * Creates the government body annual summary from row.
	 *
	 * @param year
	 *            the year
	 * @param row
	 *            the row
	 * @return the government body annual summary
	 */
	private static GovernmentBodyAnnualSummary createGovernmentBodyAnnualSummaryFromRow(final int year, final Row row) {
		return new GovernmentBodyAnnualSummary(year, row.getCell(NAME_CELL).toString(), getInteger(row.getCell(CONSECUTIVE_NUMBER_CELL).toString()),
				row.getCell(GOVERNMENT_BODY_ID_CELL).toString(), row.getCell(MCODE_CELL).toString(), row.getCell(MINISTRY_CELL).toString(),
				row.getCell(ORG_NUMBER_CELL).toString(), getInteger(row.getCell(HEADCOUNT_CELL).toString()), getInteger(row.getCell(ANNUAL_HEADCOUNT_CELL).toString()),
				row.getCell(VAT_CELL).toString(), row.getCell(COMMENT_CELL).toString());
	}

}