Subversion Repositories DevTools

Rev

Blame | Last modification | View Log | RSS feed 

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