/*
 * Created on 1/04/2005
 */
package DMS;

import java.sql.Connection;
import java.util.Vector;
import java.util.HashMap;
import java.util.Enumeration;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.ResultSet;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Calendar;
import java.util.GregorianCalendar;
import javax.servlet.http.HttpServletRequest;
import java.sql.Timestamp;
import java.util.TimeZone;


/**
 * @author mtayler
 */
public class dbTemplateData {

	private int FRecPos; 
	private Vector FRecords;
	private Vector FFilteredRecords;
	private templateItem FTemplate;
	private HttpServletRequest FRequest;
	private Calendar FLastLoadTime = null;	
	
	private static int LOAD_INTERVAL_MIN = 1; //interval that the data should be reloaded
	
	public dbTemplateData(templateItem template, HttpServletRequest request) {
		FRecords = new Vector();
		FFilteredRecords = new Vector();
		FTemplate = template;
		FRequest = request;
	}

	private String getParameter(String key) {
		if (FRequest!=null) return FRequest.getParameter(key);
		else return null;
	}
	
	private String getData(String name, String datatype, String format, ResultSet rs) {
		try {
			if (datatype.toLowerCase().equals("string")||datatype.toLowerCase().equals("varchar")||datatype.toLowerCase().equals("char")) {
				return rs.getString(name); 
			} else  if (datatype.toLowerCase().equals("number")||datatype.toLowerCase().equals("int")||datatype.toLowerCase().equals("integer")||datatype.toLowerCase().equals("bigint")) {
				double data = rs.getDouble(name);						
				DecimalFormat df = new DecimalFormat(format);				
				return df.format(data);												
			} else  if (datatype.toLowerCase().equals("date")||datatype.toLowerCase().equals("datetime")) {	
				//need to add 8 hours to the date because the db stores the date as GMT+0 instead of GMT+8
				GregorianCalendar cal = new GregorianCalendar(TimeZone.getTimeZone("GMT+08:00"));								
				Timestamp data = rs.getTimestamp(name, cal);				
				
				SimpleDateFormat df = new SimpleDateFormat(format);			
				return df.format(data);
			}			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return "";
	}
	
	private boolean isFilteredRecord(HashMap map)  {
		columnItem [] cols = FTemplate.getColumns().getColumns();
		
		for (int i = 0; i<cols.length;i++) {
			
			String dataValue = DMSUtils.NVL((String)map.get(cols[i].getName()));
			
			if (cols[i].getDataType().equals("date")||cols[i].getDataType().equals("datetime")) {
				String nameToDay="daterangefield" + cols[i].getName() + "ToDay";
				String nameToMonth="daterangefield" + cols[i].getName() + "ToMonth"; 
				String nameToYear="daterangefield" + cols[i].getName() + "ToYear";
				
				String nameFromDay="daterangefield" + cols[i].getName() + "FromDay";
				String nameFromMonth="daterangefield" + cols[i].getName() + "FromMonth"; 
				String nameFromYear="daterangefield" + cols[i].getName() + "FromYear";
				

				String defaultValueToDay = DMSUtils.NVL(getParameter(nameToDay));
				String defaultValueToMonth = DMSUtils.NVL(getParameter(nameToMonth));
				String defaultValueToYear = DMSUtils.NVL(getParameter(nameToYear));
				
				String defaultValueFromDay = DMSUtils.NVL(getParameter(nameFromDay));
				String defaultValueFromMonth = DMSUtils.NVL(getParameter(nameFromMonth));
				String defaultValueFromYear = DMSUtils.NVL(getParameter(nameFromYear));
				
				Timestamp toDate = DMSUtils.StringToTimestampEndOfDay(defaultValueToYear, defaultValueToMonth, defaultValueToDay);
				Timestamp fromDate = DMSUtils.StringToTimestamp(defaultValueFromYear, defaultValueFromMonth, defaultValueFromDay);						
				Timestamp dataValueDate = DMSUtils.StringToTimestamp(dataValue, cols[i].getFormat());

				if (dataValueDate!=null &&  ((fromDate!=null && fromDate.compareTo(dataValueDate) > 0) || (toDate!=null && toDate.compareTo(dataValueDate)<0))) {
					return false;
				}
			} else if (cols[i].getDataType().equals("number")||cols[i].getDataType().equals("integer")||cols[i].getDataType().equals("int")||cols[i].getDataType().equals("bigint")) {
				String nameFrom="numberrangefield" + cols[i].getName() + "From";
				String nameTo="numberrangefield" + cols[i].getName() + "To";
				
				String defaultValueFrom = DMSUtils.NVL(getParameter(nameFrom));
				String defaultValueTo = DMSUtils.NVL(getParameter(nameTo));
				
				Integer fromInt = DMSUtils.StringToInteger(defaultValueFrom);
				Integer toInt = DMSUtils.StringToInteger(defaultValueTo);
				Integer dataValueInt = DMSUtils.StringToInteger(dataValue);
								
				if (dataValueInt!=null &&  ((fromInt!=null && fromInt.compareTo(dataValueInt) > 0) || (toInt!=null && toInt.compareTo(dataValueInt)<0))) {
					return false;
				}				
				
			} else {
				String filterValue = DMSUtils.NVL(getParameter("filterfield"+cols[i].getName()));
				if (!filterValue.equals("") && !filterValue.equals("ALL") && !filterValue.equals(dataValue)) {
					return false;
				}				
			}
			
		}				
		return true;
	}

	public String getSortField() {
		Enumeration e = null;
		
		if (FRequest!=null) e = FRequest.getParameterNames();
		
		while (e!=null && e.hasMoreElements()) {
			String name = DMSUtils.NVL((String) e.nextElement());
			if (name.length()>=9 && name.toLowerCase().substring(0,9).equals("sortfield")) {
				String value = DMSUtils.NVL(getParameter(name));				
				if (value.equals("asc") || value.equals("desc")) 
					return name.toLowerCase();
			}
		}
		return "";
	}

	private void sortValue(String sortField, boolean isAscending, HashMap data, int startIndex, int endIndex) {
		String firstRecordValue = getFieldValue(sortField, startIndex);
		String lastRecordValue = getFieldValue(sortField, endIndex);
		String newValue = (String)data.get(sortField);
		
		columnItem item = FTemplate.getColumnByName(sortField);
		String datatype = item.getDataType();
		String format = item.getFormat();
			
		if (DMSUtils.compareToValue(firstRecordValue, newValue, datatype, format, isAscending)>0) { //if newValue is less than firstValue
			FFilteredRecords.insertElementAt(data,startIndex); //insert before the first record
		} else if (DMSUtils.compareToValue(lastRecordValue, newValue, datatype, format, isAscending)<=0) { //if newValue is greater than or equal to the lastValue				
			FFilteredRecords.insertElementAt(data,endIndex+1); //insert after the last record
		} else if (startIndex == endIndex-1) { //if there are no more records in between
			FFilteredRecords.insertElementAt(data,startIndex+1); //insert after the first record
		} else {
			//recursive sort
			int midIndex = (startIndex + endIndex) / 2;									
			String midRecordValue = getFieldValue(sortField, midIndex);
						
			if (DMSUtils.compareToValue(midRecordValue, newValue, datatype, format, isAscending)>0) { //if newValue is less than midRecordValue
				sortValue(sortField, isAscending, data, startIndex+1, midIndex);
			} else {
				sortValue(sortField, isAscending, data, midIndex, endIndex-1);
			}			
		}		
	}
			
	private void addFilteredRecord(HashMap data) {
		//get the field to sort on
		String sortFieldParam = getSortField();		
		boolean isAscending = DMSUtils.NVL(getParameter(sortFieldParam)).toLowerCase().equals("asc");		
		
		if (FFilteredRecords.size()==0 || sortFieldParam.equals("")) { //if empty or no sorting is performed append the record						
			FFilteredRecords.add(data);
		} else {
			String sortField = sortFieldParam.substring(9);
			
			sortValue(sortField, isAscending, data, 0, FFilteredRecords.size()-1);
		}
	}
	
	private boolean hasTimeExceeded() {
		if (FLastLoadTime!=null) {
			long lastTimeMin = FLastLoadTime.getTimeInMillis()/60000;
			
			Calendar currentTime = new GregorianCalendar();
			currentTime.setTime(new Date());			
			long newTimeMin = currentTime.getTimeInMillis()/60000;
			
			return (newTimeMin-lastTimeMin)>LOAD_INTERVAL_MIN;
		}		
		return true;
	}
	
	public void load(HttpServletRequest request) {
		FRequest = request; //update request object		
		
		FFilteredRecords.clear(); //clear old records
		FRecPos = -1;
	
		if (!hasTimeExceeded()) {
			for (int i = 0; i < FRecords.size(); i++) {
				HashMap data = (HashMap)FRecords.get(i);
				if (isFilteredRecord(data)) addFilteredRecord(data);
			}						
		} else {
			//reset time
			FLastLoadTime = new GregorianCalendar();
			FLastLoadTime.setTime(new Date());
			
			FRecords.clear(); //clear old records
			
			Connection conn = dbConnector.getConnection(FTemplate.getDSName());
			try {
				try {
					String SQL = FTemplate.getSQL();
					PreparedStatement pstmnt = conn.prepareStatement(SQL);
					
					ResultSet rs = pstmnt.executeQuery();
					try {
						while (rs.next()) {
							HashMap data = new HashMap();
							
							columnItem [] cols = FTemplate.getColumns().getColumns();					
							for (int i = 0; i< cols.length; i++) {
								String sValue = getData(cols[i].getName(),cols[i].getDataType(), cols[i].getFormat(), rs);
								data.put(cols[i].getName(), sValue);
							}
				
							FRecords.add(data);
							if (isFilteredRecord(data)) addFilteredRecord(data);
							
						}
					} finally {
						rs.close();
					}				
				} finally {
					conn.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		} 
	}
	
	public boolean next() {
		FRecPos++;			
		return FRecPos<FFilteredRecords.size(); 
	}
	
	public void first() {
		if (FFilteredRecords.size()>0) FRecPos=0;			
	}
	
	public void last() {
		if (FFilteredRecords.size()>0) FRecPos=FFilteredRecords.size()-1;			
	}
		
	public String getFieldValue(String fieldname, int index) {
		String fieldvalue = "";
		if (index>-1 && index<FFilteredRecords.size()) {			
			HashMap map = (HashMap)FFilteredRecords.get(index);			
			fieldvalue = (String)map.get(fieldname);
		}
		return fieldvalue;
	}
	
	public String getFieldValue(String fieldname) {
		return getFieldValue(fieldname, FRecPos);
	}	

	public HashMap getRecord(int index) {
		if (index>-1 && index<FFilteredRecords.size()) {
			return (HashMap)FFilteredRecords.get(index);
		}
		return null;
	}	
	
	public HashMap getRecord() {
		return getRecord(FRecPos);
	}	
	
	public String[] getDistinctValues(String fieldname) {
		Vector v = new Vector();
		
		for (int i=0;i<FRecords.size();i++) {
			HashMap map = (HashMap)FRecords.get(i);
			String data = (String)map.get(fieldname);
			if (!v.contains(data)) v.add(data);
		}
		return (String[])v.toArray(new String[0]);
	}
	
	public int getTotalRecordSize() {
		return FRecords.size();
	}

	public int getFilteredRecordSize() {
		return FFilteredRecords.size();		
	}
	
	/**
	 * @return Returns the fTemplate.
	 */
	public templateItem getTemplate() {
		return FTemplate;
	}
}