package com.k_int.aggregator.dpp.util;

import java.io.ByteArrayInputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.NoSuchElementException;
import java.util.Set;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
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.springframework.context.annotation.Scope;
import org.springframework.stereotype.Component;

import com.k_int.aggregator.util.SpreadsheetParseUtil;
import com.k_int.discover.datamodel.dto.CultureGrid_BaseDTO;
import com.k_int.discover.datamodel.dto.CultureGrid_ClassificationDTO;
import com.k_int.discover.datamodel.dto.CultureGrid_ItemDTO;
import com.k_int.discover.datamodel.ref.ClassificationType;
import com.k_int.discover.datamodel.ref.RulesStatus;

@Component(value="SpreadsheetParseUtilImpl")
@Scope(value="prototype")
public class SpreadsheetParseUtilImpl implements SpreadsheetParseUtil {

	private static Log					log										= LogFactory.getLog(SpreadsheetParseUtilImpl.class);
	private static SimpleDateFormat		sdf										= new SimpleDateFormat("dd/MM/yyyy");

	private static final String			RECORD_TYPE_COLLECTION					= "COLLECTION";
	private static final String			RECORD_TYPE_INSTITUTION					= "INSTITUTION";
	private static final String			RECORD_TYPE_ITEM						= "ITEM";
	private static final String			RECORD_TYPE_UNKNOWN						= "UNKNOWN";

	private static final String			ITEM_AUDIENCE_HEADER_TEXT				= "AUDIENCE";
	private static final String			ITEM_CLASSIFICATION_TEXT				= "TECHNIQUE";
	private static final String			ITEM_COLLECTION_HOLDER_TEXT				= "COLLECTION HOLDER";
	private static final String			ITEM_CONTRIBUTOR_HEADER_TEXT			= "CONTRIBUTOR";
	private static final String			ITEM_CREATOR_HEADER_TEXT				= "CREATOR";
	private static final String			ITEM_CREDIT_LINE_HEADER_TEXT			= "CREDITLINE";
	private static final String			ITEM_DESCRIPTION_HEADER_TEXT			= "DESCRIPTION";
	private static final String			ITEM_FORMAT_HEADER_TEXT					= "FORMAT";
	private static final String			ITEM_IDENTIFIER_HEADER_TEXT				= "IDENTIFIER";
	private static final String			ITEM_IS_PART_OF_HEADER_TEXT				= "COLLECTION IDENTIFIER";
	private static final String			ITEM_IMAGE_ALT_TEXT						= "ALT TEXT";
	private static final String			ITEM_LANGUAGE_HEADER_TEXT				= "LANGUAGE";
	private static final String			ITEM_LICENSE_HEADER_TEXT				= "LICENSE";
	private static final String			ITEM_LICENSE_TEXT_HEADER_TEXT			= "LICENSE TEXT";
	private static final String			ITEM_LOCAL_FILENAME_HEADER_TEXT			= "IMAGE FILENAME";
	private static final String			ITEM_LOCATION_HEADER_TEXT				= "LOCATION";
	private static final String			ITEM_MATERIAL_HEADER_TEXT				= "MATERIAL";
	private static final String			ITEM_MEDIUM_TEXT						= "MEDIUM";
	private static final String			ITEM_OTHER_IDENTIFIERS_HEADER_TEXT		= "OTHER IDENTIFIERS";
	private static final String			ITEM_PLACE_HEADER_TEXT					= "PLACE";
	private static final String			ITEM_PUBLISHER_HEADER_TEXT				= "PUBLISHER";
	private static final String			ITEM_RELATED_CLD_HEADER_TEXT			= "RELATED CLD";
	private static final String			ITEM_RELATED_EVENT_HEADER_TEXT			= "RELATED EVENT";
	private static final String			ITEM_RELATED_LINK_HEADER_TEXT			= "RELATED LINK";
	private static final String			ITEM_RELATED_ORGANISATION_HEADER_TEXT	= "RELATED ORGANISATION";
	private static final String			ITEM_RELATED_PERSON_HEADER_TEXT			= "RELATED PERSON";
	private static final String			ITEM_RELATED_SUBJECT_HEADER_TEXT		= "RELATED SUBJECT";
	private static final String			ITEM_RELATION_HEADER_TEXT				= "RELATION";
	private static final String			ITEM_RIGHTS_HOLDER_HEADER_TEXT			= "RIGHTS HOLDER";
	private static final String			ITEM_SIZE_HEADER_TEXT					= "SIZE";
	private static final String			ITEM_SPATIAL_HEADER_TEXT				= "SPATIAL";
	private static final String			ITEM_SUBJECT_HEADER_TEXT				= "SUBJECT";
	private static final String			ITEM_TEMPORAL_HEADER_TEXT				= "TEMPORAL";
	private static final String			ITEM_THUMBNAIL_URL_HEADER_TEXT			= "THUMBNAIL URL";
	private static final String			ITEM_TITLE_HEADER_TEXT					= "TITLE";
	private static final String			ITEM_TYPE_HEADER_TEXT					= "TYPE";

	private static final String			SPATIAL_TYPE_UNLOWN						= "UNKNOWN";

	private boolean						columnsDetermined						= false;
	private HashMap<String, Integer>	columnMappings							= new HashMap<String, Integer>();

	private int							rowPointer;
	private Iterator<Row>				rowIterator;
	private String						recordType;
	private CultureGrid_BaseDTO			baseDTO;

	public SpreadsheetParseUtilImpl(){
		
	}
 
	/**
	 * This constructor needs to be used in order to parse a document iteratively line by line
	 * @param spreadsheet the byte[] containing the spreadsheet file
	 * @throws IOException
	 */
	public SpreadsheetParseUtilImpl(byte[] spreadsheet) throws IOException
	{
		initialize(spreadsheet);
	}
	
	@Override
	public void initialize(byte[] spreadsheet) throws IOException
	{
		Workbook wb=null;
		try
		{
			wb = WorkbookFactory.create(new ByteArrayInputStream(spreadsheet));
		} catch (InvalidFormatException e)
		{
			log.error("An error occurred while creating workbook for parsing.",e);
			throw new IOException(e.getMessage());
		}catch (IllegalArgumentException illE ){
			log.error("An error occurred while creating workbook for parsing.",illE);
			throw new IOException(illE.getMessage());
		}
		Sheet dataSheet = wb.getSheetAt(0);
		// according to documentation, datasheet.getRow(i) returns something different than the rows of iterator (physical vs logical rows).
		rowIterator = dataSheet.rowIterator();
		rowPointer = 0;
		recordType = "RECORD_TYPE_UNKNOWN";	
	}
	
	@Override
	public boolean next()
	{
		while (hasNext())
		{
			baseDTO = parseNextRow();
			if (baseDTO != null)
				return true;
		}
		return false;
	}
	
	@Override
	public Object parse()
	{
		return baseDTO;
	}
	/**
	 * Returns true if there are more rows to parse using parseNextRow
	 * @return
	 */
	private boolean hasNext(){		
		return rowIterator!=null && rowIterator.hasNext();
	}

	/**
	 * Parse a single row at a time. 
	 * 
	 * @return null returned when an error occurred with parsing. Parsing is cancelled if an error is encountered.
	 * @throws NullPointerException
	 *             Most likely when there is no rowIterator/rowPointer - SpreadsheetParsingUtils(byte[] document) has not been used
	 */
	private CultureGrid_BaseDTO parseNextRow() throws NullPointerException
	{
		CultureGrid_BaseDTO returnVal = null;
		try
		{
			if (rowIterator.hasNext())			
				returnVal = parseRow(++rowPointer, rowIterator.next()).values().iterator().next();
		} catch (ParsingException e)
		{
			rowIterator = null;
			log.error("Parsing exception thrown - stopping processing as it can't be relied on to be correct hierarchically");
		} catch(NoSuchElementException e){
			//Thrown on first couple of rows of parsing, this is expected, do nothing
		}
		
		return returnVal;
	}

	
	/**
	 * Parse all the document. Should not be called during the process of parsing rows using parseNextRow(), as it will affect the results of that method due to shared parameters.
	 * 
	 * @param content
	 * @return
	 */
	public LinkedHashMap<String, CultureGrid_BaseDTO> parseCGSpreadsheet(byte[] content)
	{

		LinkedHashMap<String, CultureGrid_BaseDTO> returnValue = new LinkedHashMap<String, CultureGrid_BaseDTO>();

		Workbook wb = null;

		try
		{
			wb = WorkbookFactory.create(new ByteArrayInputStream(content));
			if (wb != null)
			{
				Sheet dataSheet = wb.getSheetAt(0);
				if (dataSheet != null)
				{
					// Loop over the rows and get the information we need
					Iterator<Row> rowIter = dataSheet.rowIterator();

					recordType = RECORD_TYPE_UNKNOWN;

					int rowCtr = 0;
					while (rowIter.hasNext())
					{
						Row nextRow = rowIter.next();
						try
						{
							returnValue.putAll(parseRow(++rowCtr, nextRow));
						} catch (NullPointerException nullP)
						{
							// Null returned from parseRow means something went wrong and parse should stop
							break;
						}
					}
				} else
				{
					log.error("No sheet found in the workbook to read");
				}
			} else
			{
				log.error("No workbook found to read");
			}
		} catch (IOException ioe)
		{
			log.error("IOException thrown!");
			ioe.printStackTrace();

		} catch (InvalidFormatException ife)
		{
			log.error("InvalidFormatException thrown!");
			ife.printStackTrace();
		} catch (ParsingException pe)
		{
			log.info("Parsing exception thrown - stopping processing as it can't be relied on to be correct hierarchically");
		}

		return returnValue;
	}
	
	private Map<String, CultureGrid_BaseDTO> parseRow(int rowCtr, Row nextRow) throws ParsingException
	{
		Map<String, CultureGrid_BaseDTO> returnValue = new LinkedHashMap<String, CultureGrid_BaseDTO>();

		log.debug("About to parse row with ctr: " + rowCtr);

		if (rowCtr == 1 && nextRow != null)
		{
			// If we're looking at the first row then parse it to work out the record type

			recordType = workoutRecordsType(nextRow);

			if (RECORD_TYPE_UNKNOWN.equals(recordType))
			{
				// Unable to work out the type - exit out
				return null;

			}

			// Currently we're only able to deal with item records so if the type isn't ITEM
			// then we need to stop processing
			if (!RECORD_TYPE_ITEM.equals(recordType))
			{
				log.info("Record type for an uploaded spreadsheet worked out to be " + recordType + " but we can only deal with " + RECORD_TYPE_ITEM + " currently so not continuing");
				return null;

			}

		}

		if (rowCtr == 2 && nextRow != null)
		{
			// If we're looking at the 2nd row then parse it to check for the column ordering

			try
			{
				workoutColumnNumbers(nextRow, recordType);
			} catch (UnsupportedOperationException uoe)
			{
				log.info("Exception thrown when trying to work out column numbers - unsupported type? Record type: " + recordType);
				uoe.printStackTrace();
				return null;
			}

			// Check that we're happy that we've worked out the column ordering sufficiently to continue
			// and exit out if not
			if (!columnsDetermined)
			{
				log.info("Unable to determine the column ordering to a sufficient standard can't continue processing.");
				return null;
			}
		}

		// Ignore the first two rows and any null rows
		if (rowCtr > 2 && nextRow != null)
		{

			CultureGrid_BaseDTO rowDTO = parseRow(nextRow, recordType);

			if (rowDTO != null)
			{
				log.debug("Non-null dto returned for row ctr: " + rowCtr);
				// We have a valid returned row - remember it
				returnValue.put(rowDTO.getIdentifier(), rowDTO);
			} else
			{
				log.debug("Null dto returned for row ctr: " + rowCtr);
			}

		} else
		{
			log.debug("null row retrieved row ctr: " + rowCtr);
		}

		return returnValue;
	}

	private static String getValueAsString(Cell cell)
	{
		String returnValue = null;

		if (cell != null)
		{

			switch (cell.getCellType())
			{

				case Cell.CELL_TYPE_BLANK:
					returnValue = "";
					break;
				case Cell.CELL_TYPE_BOOLEAN:
					boolean val = cell.getBooleanCellValue();
					if (val)
					{
						returnValue = "true";
					} else
					{
						returnValue = "false";
					}
					break;
				case Cell.CELL_TYPE_FORMULA:
					returnValue = cell.getRichStringCellValue().getString();
					break;
				case Cell.CELL_TYPE_NUMERIC:
					if (DateUtil.isCellDateFormatted(cell))
					{
						// Date!
						Date valAsDate = cell.getDateCellValue();
						returnValue = sdf.format(valAsDate);
					} else
					{
						// Tell the cell it is actually a string and start again
						cell.setCellType(Cell.CELL_TYPE_STRING);
						returnValue = getValueAsString(cell);
					}
					break;
				case Cell.CELL_TYPE_STRING:
					returnValue = cell.getRichStringCellValue().getString();
					break;
				default:
					returnValue = "";

			}
		}

		return returnValue;
	}

	private CultureGrid_BaseDTO parseRow(Row row, String recordType) throws ParsingException
	{
		// Now loop through the cells in the row..

		CultureGrid_BaseDTO returnVal = null;

		if (RECORD_TYPE_ITEM.equals(recordType))
		{

			String alt_text = getCellVal(row,columnMappings.get(ITEM_IMAGE_ALT_TEXT));
			String audience = getCellVal(row, columnMappings.get(ITEM_AUDIENCE_HEADER_TEXT));
			String classification = getCellVal(row,columnMappings.get(ITEM_CLASSIFICATION_TEXT));
			String collectionHolder = getCellVal(row,columnMappings.get(ITEM_COLLECTION_HOLDER_TEXT));
			String contributor = getCellVal(row, columnMappings.get(ITEM_CONTRIBUTOR_HEADER_TEXT));
			String creator = getCellVal(row, columnMappings.get(ITEM_CREATOR_HEADER_TEXT));
			String credit2Line = getCellVal(row, columnMappings.get(ITEM_CREDIT_LINE_HEADER_TEXT));
			String description = getCellVal(row, columnMappings.get(ITEM_DESCRIPTION_HEADER_TEXT));
			String format = getCellVal(row, columnMappings.get(ITEM_FORMAT_HEADER_TEXT));
			String identifier = getCellVal(row, columnMappings.get(ITEM_IDENTIFIER_HEADER_TEXT));
			String isPartOf = getCellVal(row, columnMappings.get(ITEM_IS_PART_OF_HEADER_TEXT));
			String language = getCellVal(row, columnMappings.get(ITEM_LANGUAGE_HEADER_TEXT));
			String license = getCellVal(row, columnMappings.get(ITEM_LICENSE_HEADER_TEXT));
			String licenseText = getCellVal(row, columnMappings.get(ITEM_LICENSE_TEXT_HEADER_TEXT));
			String localFileName = getCellVal(row, columnMappings.get(ITEM_LOCAL_FILENAME_HEADER_TEXT));
			String location = getCellVal(row, columnMappings.get(ITEM_LOCATION_HEADER_TEXT));
			String material = getCellVal(row, columnMappings.get(ITEM_MATERIAL_HEADER_TEXT));
			String medium = getCellVal(row,columnMappings.get(ITEM_MEDIUM_TEXT));
			String otherIdentifiers = getCellVal(row, columnMappings.get(ITEM_OTHER_IDENTIFIERS_HEADER_TEXT));
			String place = getCellVal(row, columnMappings.get(ITEM_PLACE_HEADER_TEXT));
			String publisher = getCellVal(row, columnMappings.get(ITEM_PUBLISHER_HEADER_TEXT));
			String relatedCLD = getCellVal(row, columnMappings.get(ITEM_RELATED_CLD_HEADER_TEXT));
			String relatedEvent = getCellVal(row, columnMappings.get(ITEM_RELATED_EVENT_HEADER_TEXT));
			String relatedLink = getCellVal(row, columnMappings.get(ITEM_RELATED_LINK_HEADER_TEXT));
			String relatedOrganisation = getCellVal(row, columnMappings.get(ITEM_RELATED_ORGANISATION_HEADER_TEXT));
			String relatedPerson = getCellVal(row, columnMappings.get(ITEM_RELATED_PERSON_HEADER_TEXT));
			String relatedSubject = getCellVal(row, columnMappings.get(ITEM_RELATED_SUBJECT_HEADER_TEXT));
			String relation = getCellVal(row, columnMappings.get(ITEM_RELATION_HEADER_TEXT));
			String rightsHolder = getCellVal(row, columnMappings.get(ITEM_RIGHTS_HOLDER_HEADER_TEXT));
			String size = getCellVal(row, columnMappings.get(ITEM_SIZE_HEADER_TEXT));
			String spatial = getCellVal(row, columnMappings.get(ITEM_SPATIAL_HEADER_TEXT));
			String subjects = getCellVal(row, columnMappings.get(ITEM_SUBJECT_HEADER_TEXT));
			String temporal = getCellVal(row, columnMappings.get(ITEM_TEMPORAL_HEADER_TEXT));
			String thumbnailURL = getCellVal(row, columnMappings.get(ITEM_THUMBNAIL_URL_HEADER_TEXT));
			String title = getCellVal(row, columnMappings.get(ITEM_TITLE_HEADER_TEXT));
			String type = getCellVal(row, columnMappings.get(ITEM_TYPE_HEADER_TEXT));

			// Separate out the values that should be sets - separated by ';'
			Set<String> audienceSet = convertValToSet(audience);
			Set<String> contributorSet = convertValToSet(contributor);
			Set<String> creatorSet = convertValToSet(creator);
			Set<String> isPartOfSet = convertValToSet(isPartOf);
			Set<String> otherDcIdentifiersSet = convertValToSet(otherIdentifiers);
			Set<String> placeSet = convertValToSet(place);
			Set<String> relatedCLDSet = convertValToSet(relatedCLD);
			Set<String> subjectSet = convertValToSet(subjects);
			Set<String> typeSet = convertValToSet(type);

			log.debug("**** type worked out to be : " + type);
			if (typeSet != null)
			{
				log.debug("**** and typeSet now has size: " + typeSet.size());
				int ctr = 0;
				for (String aType : typeSet)
				{
					log.debug("typeSet[" + ctr + "] = " + aType);
					ctr++;
				}
			}

			// For spatial - split the value by ';' to get individual entries and then try to
			// separate by ':' to get the type / value separation
			Set<String> tempSpatial = convertValToSet(spatial);
			Map<String, String> spatialMap = new HashMap<String, String>();
			if (tempSpatial != null)
			{
				for (String aSpatialVal : tempSpatial)
				{
					if (aSpatialVal != null && !"".equals(aSpatialVal.trim()))
					{
						// We have a spatial value - does it have a type specified?
						if (aSpatialVal.contains(":"))
						{
							String[] valAndType = aSpatialVal.split(":");
							String val = null;
							String valType = null;

							if (valAndType.length >= 2)
							{
								val = valAndType[1];
								valType = valAndType[0];
							} else
							{
								valType = SPATIAL_TYPE_UNLOWN;
								val = aSpatialVal;
							}

							if (val != null && valType != null)
							{
								spatialMap.put(valType, val);
							} else
							{
								log.debug("Unable to work out a spatial type and value for the spatial string: " + aSpatialVal);
							}
						} else
						{
							// No separator therefore no type specified
							spatialMap.put(SPATIAL_TYPE_UNLOWN, aSpatialVal);
						}
					}
				}
			}
			

	
			CultureGrid_ItemDTO itemDTO = new CultureGrid_ItemDTO();
			itemDTO.setAltText(alt_text);
			itemDTO.setAudiences(audienceSet);
			itemDTO.setCollectionHolder(collectionHolder);
			itemDTO.setContributor(contributorSet);
			itemDTO.setCreators(creatorSet);
			itemDTO.setCreditLine(credit2Line);
			itemDTO.getLegacySingleValues().setDescription(description);
			itemDTO.setMedium(medium);
			itemDTO.setFormat(format);
			itemDTO.setIdentifier(identifier);
			if (license == null || license.trim().isEmpty())
			{
				itemDTO.setLicenseText(licenseText);
			} else
			{
				if (license.trim().startsWith("http"))
				{
					itemDTO.setLicense(license);
					itemDTO.setLicenseText(licenseText);
				} else
				{
					itemDTO.setLicenseText(license);
				}
			}
			itemDTO.setLocation(location);
			itemDTO.setIsPartOf(isPartOfSet);
			itemDTO.getLegacySingleValues().setLanguage(language);
			itemDTO.setMaterials(material);
			itemDTO.setOtherDcIdentifiers(otherDcIdentifiersSet);
			itemDTO.setPlace(placeSet);
			itemDTO.setPublisher(publisher);
			itemDTO.setRelatedCLDs(relatedCLDSet);
			if ((identifier != null) && ((relatedLink == null) || relatedLink.trim().isEmpty()))
			{
				// If the identifier begins with http or https then set the related link to that since we do not have one
				if (identifier.startsWith("http://") || identifier.startsWith("https://"))
				{
					itemDTO.getLegacySingleValues().setLink(identifier);
				}
			} else
			{
				itemDTO.getLegacySingleValues().setLink(relatedLink);
			}
			itemDTO.setRelatedSubject(relatedSubject);
			itemDTO.setRelation(relation);
			itemDTO.setRightsHolder(rightsHolder);
			itemDTO.setRulesStatus(RulesStatus.NOT_PROCESSED);
			itemDTO.setSize(size);
			itemDTO.setSpatial(spatialMap);
			itemDTO.getLegacySingleValues().setDefaultSubjects(subjectSet);
			itemDTO.setSubjectEvent(relatedEvent);
			itemDTO.setSubjectOrganisation(relatedOrganisation);
			itemDTO.setSubjectPerson(relatedPerson);
			itemDTO.setTempLocalFilename(localFileName);
			itemDTO.setTemporal(temporal);
			itemDTO.getLegacySingleValues().setThumbnail(thumbnailURL);
			itemDTO.setTitle(title);
			itemDTO.setTypes(typeSet);

			
			for(String s: convertValToList(classification)){
				itemDTO.addClassification(new CultureGrid_ClassificationDTO(ClassificationType.TECHNIQUE,s));
			}
			
			itemDTO.getLegacySingleValues().set();
			
			// Check that we have the required information for this row before returning it
			if (!checkRequiredData(itemDTO))
			{
				// We don't have what we need - log that
				log.debug("Row parsed but didn't have the required information so thrown away. Identifier: " + ((itemDTO.getIdentifier() == null) ? "<null>" : itemDTO.getIdentifier()));
			} else
			{
				// We do have what we need - remember the object for return
				returnVal = itemDTO;
			}
		} else
		{
			// Currently unsupported..
			throw (new UnsupportedOperationException("Attempt to parse a row with an unsupported record type: " + recordType));
		}

 		return returnVal;
	}

	private static Set<String> convertValToSet(String val)
	{
		Set<String> result = (Set<String>)convertValToCollection(val, new HashSet<String>());
		return result;
	}
	
	private static List<String> convertValToList(String val)
	{
		List<String> result = (List<String>) convertValToCollection(val, new ArrayList<String>());
		return result;
	}
	
	private static Collection<String> convertValToCollection(String val, Collection<String> collection){
		Collection<String >returnValue = collection;

		if (val != null)
		{
			String[] splitVal = val.split(";");
			for (String thisVal : splitVal)
			{
				if ((thisVal != null) && !thisVal.trim().isEmpty())
				{
					returnValue.add(thisVal);
				}
			}
		}

		return returnValue;
		
	}
	private static String getCellVal(Row row, Integer index)
	{
		String returnValue = null;

		if ((index != null) && (index > -1))
		{
			returnValue = getValueAsString(row.getCell(index.intValue()));
		}

		return returnValue;

	}

	private static String workoutRecordsType(Row headRow)
	{
		int numOfCells = headRow.getLastCellNum();

		String returnVal = RECORD_TYPE_UNKNOWN;

		if (numOfCells < 2)
		{
			// Wrong number of cells - deal with it..
			log.debug("When working out the record type for an uploaded spreadsheet the wrong number of columns was detected - can't work out the type");
		} else
		{

			// Look at the value in column 1 (0 based) and use that to decide
			String cellVal = getCellVal(headRow, 1);

			if (RECORD_TYPE_ITEM.equalsIgnoreCase(cellVal))
			{
				returnVal = RECORD_TYPE_ITEM;
			} else if (RECORD_TYPE_COLLECTION.equalsIgnoreCase(cellVal))
			{
				returnVal = RECORD_TYPE_COLLECTION;
			} else if (RECORD_TYPE_INSTITUTION.equalsIgnoreCase(cellVal))
			{
				returnVal = RECORD_TYPE_INSTITUTION;
			}
		}

		return returnVal;

	}

	private void workoutColumnNumbers(Row headRow, String recordType)
	{

		if (RECORD_TYPE_ITEM.equals(recordType))
		{
			// Item record - work out the relevant columns and whether we have the information we'll need
			int numOfCells = headRow.getLastCellNum();
			for (int ctr = 0; ctr < numOfCells; ctr++)
			{
				// Loop through all of the columns and work out what each one is
				String collTitle = getCellVal(headRow, ctr);
				if (collTitle != null)
				{
					collTitle = collTitle.trim();
					if (!collTitle.isEmpty())
					{
						columnMappings.put(collTitle.toUpperCase(), new Integer(ctr));
					}
				}
			}

			// We've now worked through all of the columns - check that we have the columns
			// that we care about meaning it's OK to continue
			if ((columnMappings.get(ITEM_IDENTIFIER_HEADER_TEXT) == null) ||
					(columnMappings.get(ITEM_TITLE_HEADER_TEXT) == null) ||
					(columnMappings.get(ITEM_DESCRIPTION_HEADER_TEXT) == null))
			{
				// We don't have an identifier, title or description - complain
				columnsDetermined = false;
			} else
			{
				// We have the mandatory columns - good to continue
				columnsDetermined = true;
			}

		} else
		{
			// Currently only support items so complain if it's anything else
			throw (new UnsupportedOperationException("Unable to parse a spreadsheet containing records of a type other than ITEM currently."));
		}

	}

	private boolean checkRequiredData(CultureGrid_BaseDTO baseDTO)
	{

		boolean returnValue = false;

		if (baseDTO instanceof CultureGrid_ItemDTO)
		{
			// Item - check the relevant fields
			CultureGrid_ItemDTO itemDTO = (CultureGrid_ItemDTO) baseDTO;

			if ((itemDTO.getIdentifier() != null) && !itemDTO.getIdentifier().trim().isEmpty() &&
					(itemDTO.getTitle() != null) && !itemDTO.getTitle().trim().isEmpty() &&
					(itemDTO.getLegacySingleValues().getDescription() != null) && !itemDTO.getLegacySingleValues().getDescription().trim().isEmpty() &&
					(itemDTO.getTypes() != null) && !itemDTO.getTypes().isEmpty())
			{

				// We have all of the fields that we want - remember that
				returnValue = true;
			} else
			{
				// We don't have the fields we want
				returnValue = false;
			}
		} else
		{
			// Unsupported type - complain
			log.error("Unsupported object received in the checkRequiredData method. Type: " + baseDTO.getClass().getName());
			throw (new UnsupportedOperationException("Attempt to check for required data on an unsupported type: " + baseDTO.getClass().getName()));
		}

		return returnValue;

	}
}