Subversion Repositories DevTools

/*

 * 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;

	}

}