package lu.tudor.santec.gecamed.importer.converter;

import java.io.File;
import java.io.FileInputStream;
import java.util.Date;

import org.apache.commons.lang3.StringEscapeUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

/**
 * @author jens.ferring(at)tudor.lu
 * 
 * @version
 * <br>$Log: ExcelToXmlConverter.java,v $
 */

public class ExcelToXmlConverter extends GECAMedImportConverter
{
	private File input;
	
	private HSSFWorkbook	workbook;
	private HSSFSheet		sheet;
	
	private int	columnSsn			= 0;
	private int	columnTitle			= 3;
	private int	columnFirstname		= 1;
	private int	columnLastname		= 2;
	private int	columnBirthname		= 6;
	private int	columnGender		= 4;
	private int	columnBirthday		= 5;
	private int columnCreated		= 7;
	private int	columnInsurance		= 9;

	private int	columnCountry		= 14;
	private int columnZip			= 12;
	private int	columnLocality		= 13;
	private int	columnStreet		= 10;
	private int	columnStreetNo		= 11;
	
	private int currentRow;
	private int totalRows;
	
	
	
	public ExcelToXmlConverter ()
	{
		
	}
	
	
	public void setInputFile (File excelFile)
	{
		this.input	= excelFile;
	}


	public void perpare ()
	{
		try
		{
			workbook	= new HSSFWorkbook(new FileInputStream(input));
			sheet		= workbook.getSheetAt(0);
			totalRows	= sheet.getPhysicalNumberOfRows();
			currentRow	= 1;
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
	}
	
	
	public boolean readNextPatient ()
	{
		if (currentRow >= totalRows)
			return false;
		
		
		setPatientData(
				getCellValue(currentRow, columnSsn), 
				getCellValue(currentRow, columnTitle), 
				getCellValue(currentRow, columnFirstname), 
				getCellValue(currentRow, columnLastname), 
				getCellValue(currentRow, columnBirthname), 
				getCellValue(currentRow, columnGender), 
				getCellValueAsDate(currentRow, columnBirthday),
				getCellValueAsDate(currentRow, columnCreated),
				getCellValue(currentRow, columnInsurance));
		
		setAddressData(
				getCellValue(currentRow, columnCountry), 
				getCellValue(currentRow, columnZip), 
				getCellValue(currentRow, columnLocality), 
				getCellValue(currentRow, columnStreet), 
				getCellValue(currentRow, columnStreetNo));
		
		currentRow++;
		
		return super.lastname != null;
	}
	
	
	
	/* ======================================== */
	// HELP METHODS
	/* ======================================== */
	
	private String getCellValue (int rowNumber, int columnNumber)
	{
		HSSFRow		row		= sheet.getRow(rowNumber);
		if (row == null)
			return null;
		
		HSSFCell	cell	= row.getCell(columnNumber);
		if (cell == null)
			return null;
		
		String result;
		switch (cell.getCellType())
		{
			case HSSFCell.CELL_TYPE_BLANK:
				result = null;
				break;
				
//			case HSSFCell.CELL_TYPE_BOOLEAN:
//				break;
				
			case HSSFCell.CELL_TYPE_ERROR:
				System.err.println("Cell("+rowNumber+", "+columnNumber+") has an error");
				result = null;
				break;

//			case HSSFCell.CELL_TYPE_FORMULA:
//				switch (cell.getCachedFormulaResultType())
//				{
//					case HSSFCell.CELL_TYPE_NUMERIC:
//						result = String.valueOf(cell.getNumericCellValue());
//						break;
//					
//					default:
//						result = cell.getStringCellValue();
//				}
//				break;
				
			case HSSFCell.CELL_TYPE_STRING:
				result = cell.getStringCellValue();
				break;
				
			default:
				result = cell.toString();
		}
		
		return result == null ? null : StringEscapeUtils.escapeXml(result);
	}
	
	
	public Date getCellValueAsDate (int rowNumber, int columnNumber)
	{
		HSSFRow		row		= sheet.getRow(rowNumber);
		if (row == null)
			return null;
		
		HSSFCell	cell	= row.getCell(columnNumber);
		if (cell == null)
			return null;
		
		return cell.getDateCellValue();
	}
}
