/*
 * $Id: VKCStatisticsRetriever.java,v 1.32 2007/10/30 13:16:34 marbobo Exp $
 *
 * Copyright (c) Siemens AG 2008 
 *           (c) Universität Paderborn 2008 
 * Licensed under EUPL 1.1. 
 *
 * $Log: VKCStatisticsRetriever.java,v $
 * Revision 1.32  2007/10/30 13:16:34  marbobo
 * + removed unnecessary statements
 *
 * Revision 1.31  2006/03/23 13:40:54  marbobo
 * + Unbenutzte Imports entfernt und alle nicht verwendete Elemente auskommentiert
 *
 * Revision 1.30  2005/09/15 15:39:36  jonyp
 * added retrieveDocumentStatisticSeriesCollection methods
 *
 * Revision 1.29  2005/09/15 15:23:30  jonyp
 * commented out System.out.println statement
 *
 * Revision 1.28  2005/09/13 15:34:27  jonyp
 * *** empty log message ***
 *
 * Revision 1.27  2005/09/09 14:19:27  jonyp
 * *** empty log message ***
 *
 * Revision 1.26  2005/09/06 15:01:04  jonyp
 * *** empty log message ***
 *
 * Revision 1.25  2005/09/06 06:25:42  jonyp
 * bugfix: deleted parameter in pStmt.executeQuery in retrieveDocumentStatistics
 *
 * Revision 1.24  2005/09/05 22:09:31  jonyp
 * added retrieveDocTypeStatistics methods
 *
 * Revision 1.23  2005/09/05 17:43:48  jonyp
 * *** empty log message ***
 *
 * Revision 1.22  2005/09/05 14:49:13  jonyp
 * added getDocumentStatisticSeries methods
 *
 * Revision 1.21  2004/12/17 01:18:34  marbobo
 * + Weitere Umstellungen und Optimierungen bzgl. PreparedStatements
 *
 * Revision 1.20  2004/12/11 18:33:59  marbobo
 * + Restliche Statements auf PreparedStatement umgestellt
 *
 * Revision 1.19  2004/11/24 09:25:57  lawern
 * Hibernate ist VKC-Geschichte!
 *
 * Revision 1.18  2004/11/18 19:38:24  marbobo
 * + changed order of close-stmt to resultset/connect/session  in finally-blocks
 *
 * Revision 1.17  2004/11/09 14:35:40  marbobo
 * + Umstellung von authorId auf publisherId
 * + Hinzufügen von AuthorName für Dokumente
 * + Nötwendige Änderungen betreffend AuthorName und PublisherId
 *
 * Revision 1.16  2004/07/22 13:17:54  marbobo
 * + Quellcode maintenance
 *
 * Revision 1.15  2004/07/19 14:42:30  jonyp
 * added versioned document statistics
 *
 * Revision 1.14  2004/07/13 23:25:48  jonyp
 * usable version
 *
 * Revision 1.13  2004/07/06 14:40:55  jonyp
 * preliminary
 *
 * Revision 1.11  2004/06/27 17:29:46  marbobo
 * + Quellcode maintenance
 *
 * Revision 1.10  2004/06/24 23:31:12  marbobo
 * Quellcode maintenance
 *
 * Revision 1.8  2004/06/18 15:02:47  jonyp
 * added conveninces methods
 *
 * Revision 1.6  2004/06/16 11:06:17  jonyp
 * preliminary
 *
 * Revision 1.5  2004/06/15 15:19:55  jonyp
 * preliminary
 *
 * Revision 1.4  2004/06/08 12:46:01  jonyp
 * preliminary, third time
 *
 * Revision 1.3  2004/06/08 12:34:42  jonyp
 * preliminary, second time
 *
 * Revision 1.2  2004/06/04 15:03:56  jonyp
 * added "getStatistics4System4Year"-method
 *
 * Revision 1.1  2004/06/04 14:56:41  jonyp
 * preliminary
 *
 *
 */

package vkc.statistics;

import vkc.db.helper.DBHelper;
import vkc.exception.DBException;
import vkc.view.WebShare;
import vkc.db.entities.VKCDocument;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.GregorianCalendar;
import java.util.List;

import java.sql.Connection;

public class VKCStatisticsRetriever {
	private VKCStatisticsRetriever() {
	}

	/**
	 * retrieves system statistics for a given time period
	 */
	public final static VKCSystemStatistics retrieveSystemStatistics( GregorianCalendar from, GregorianCalendar until ) throws DBException {
		Connection conn = null;
	
		try {
			conn = WebShare.database.getConnection();
				
			return retrieveSystemStatistics( conn, from, until );
		} catch ( Exception e ) {
			throw new DBException( e );
		} finally {
			DBHelper.close( conn );
		}
	}

	/**
	 * retrieves system statistics for a given time period
	 */
	public final static VKCSystemStatistics retrieveSystemStatistics( Connection conn, GregorianCalendar from, GregorianCalendar until ) throws DBException {
		VKCSystemStatistics sysStat = new VKCSystemStatistics();
	
		try {
			sysStat.setFrom( from );
			sysStat.setUntil( until );
	            
			// retrieve document statistics
            //sysStat.setDocStat( VKCStatisticsRetriever.retrieveDocumentStatistics( conn, from, until, null ) );
            
            // retrieve versioned document statistics
			//sysStat.setVersionedDocStat( VKCStatisticsRetriever.retrieveDocumentStatistics( conn, from, until, "version_no > 1" ) );
            
			// retrieve category statistics
			sysStat.setCatStat( VKCStatisticsRetriever.retrieveCategoryStatistics( conn, from, until, null ) );

            // retrieve user statistics
			sysStat.setUserStat( VKCStatisticsRetriever.retrieveUserStatistics( conn, from, until, null ) );
			
			return sysStat;
		} catch ( Exception e ) {
			throw new DBException( e );
		}
	}

	/**
	 * retrieves document statistics for a given time period
	 */
	public final static VKCDocumentStatistics getDocumentStatistics( GregorianCalendar from, GregorianCalendar until ) throws DBException {
		Connection conn = null;
	
		try {
			conn = WebShare.database.getConnection();
				
			return retrieveDocumentStatistics( conn, from, until, null );
		} catch ( Exception e ) {
			throw new DBException( e );
		} finally {
			DBHelper.close( conn );
		}
	}
	
	/**
	 * retrieves document statistics for a given time period
	 */
	public final static VKCDocumentStatistics retrieveDocumentStatistics( Connection conn, GregorianCalendar from, GregorianCalendar until, String searchConditionDocs ) throws DBException {
		Timestamp tsFrom = null;
		Timestamp tsUntil = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		String sqlStatement = "";
		VKCDocumentStatistics docStat = new VKCDocumentStatistics();
		List params = new ArrayList();
	
		try {
		    if (from != null) {
                docStat.setFrom((GregorianCalendar) from.clone());
                tsFrom = new Timestamp(from.getTimeInMillis());
            }

            if (until != null) {
                docStat.setUntil((GregorianCalendar) until.clone());
                tsUntil = new Timestamp(until.getTimeInMillis());
            } 
            
			/*
			 * retrieve document statistics: 
			 * count / amount of created documents and count of authors
			 */
			sqlStatement = "SELECT COUNT(documentId) AS count_docs, SUM(docsize) AS sum_docsize, COUNT(DISTINCT publisherId) AS count_authors" +
						   "  FROM documents" + 
			               " WHERE flag = " + VKCDocument.FLAG_PUBLISHED;			                       
			
            if (tsFrom != null) {
                sqlStatement += " AND (documents.published >= ? OR documents.published IS NULL)";
                params.add(tsFrom);
            }

            if (tsUntil != null) {
                sqlStatement += " AND (documents.published <= ? OR documents.published IS NULL)";
                params.add(tsUntil);
            }
                              
			if ( searchConditionDocs != null && searchConditionDocs.length() > 0 ) {
				sqlStatement += " AND " + searchConditionDocs;
			}				            	

//			System.out.println( "From: " + tsFrom + " Until: " + tsUntil);

			pStmt = conn.prepareStatement( sqlStatement );		
            DBHelper.setElements( pStmt, params.toArray() );
			rs = pStmt.executeQuery( );
			rs.next();
					
			docStat.setCountDocs( rs.getInt( "count_docs" ) );
			docStat.setAmountDocs( rs.getLong( "sum_docsize" ) );
			docStat.setCountAuthorsOfDocs( rs.getInt( "count_authors" ) );
	
            DBHelper.close( rs );
			params = new ArrayList();
			
			/*
			 * retrieve additional document statistics: 
			 * count / amount of additional documents 
			 */
			sqlStatement = "SELECT COUNT(additionaldocumentformats.addDocFormatId) AS count_add_docs, SUM(additionaldocumentformats.docsize) AS sum_add_docsize" +
						   "  FROM additionaldocumentformats, documents" +
						   " WHERE additionaldocumentformats.documentId = documents.documentId";
	
            if (tsFrom != null) {
                sqlStatement += " AND (documents.published >= ? OR documents.published IS NULL)";
                params.add(tsFrom);
            }

            if (tsUntil != null) {
                sqlStatement += " AND (documents.published <= ? OR documents.published IS NULL)";
                params.add(tsUntil);
            }
                              
			if ( searchConditionDocs != null && searchConditionDocs.length() > 0 ) {
				sqlStatement += " AND " + searchConditionDocs;
			}				            	

//			System.out.println( sqlStatement );	

			DBHelper.close( pStmt );
			pStmt = conn.prepareStatement( sqlStatement );
            DBHelper.setElements( pStmt, params.toArray() );
			rs = pStmt.executeQuery();
			rs.next();
					
			docStat.setCountAddDocs( rs.getInt( "count_add_docs" ) );
			docStat.setAmountAddDocs( rs.getLong( "sum_add_docsize" ) );
	
            DBHelper.close( rs );
			params = new ArrayList();
	
			/*
			 * retrieve expired document statistics: 
			 * count / amount of created documents and count of authors
			 */
			sqlStatement = "SELECT COUNT(documentId) AS count_docs, SUM(docsize) AS sum_docsize, COUNT(DISTINCT publisherId) AS count_authors" +
						   "  FROM documents" +
						   " WHERE flag = " + VKCDocument.FLAG_EXPIRED; 
	        
            if (tsFrom != null) {
                sqlStatement += " AND (documents.published >= ? OR documents.published IS NULL)";
                params.add(tsFrom);
            }

            if (tsUntil != null) {
                sqlStatement += " AND (documents.published <= ? OR documents.published IS NULL)";
                params.add(tsUntil);
            }
                              
			if ( searchConditionDocs != null && searchConditionDocs.length() > 0 ) {
				sqlStatement += " AND " + searchConditionDocs;
			}				            	

//			System.out.println( sqlStatement );
	
			DBHelper.close( pStmt );
			pStmt = conn.prepareStatement( sqlStatement );
            DBHelper.setElements( pStmt, params.toArray() );
			rs = pStmt.executeQuery();
			rs.next();
					
			docStat.setCountExpiredDocs( rs.getInt( "count_docs" ) );
			docStat.setAmountExpiredDocs( rs.getLong( "sum_docsize" ) );
			docStat.setCountAuthorsOfExpiredDocs( rs.getInt( "count_authors" ) );
			
			return docStat;
		} catch ( Exception e ) {
			throw new DBException( e );
		} finally {
			DBHelper.close( rs );
			DBHelper.close( pStmt );
		}
	}

	
	public final static List retrieveDocumentStatisticSeries( GregorianCalendar from, int calenderField, int step, int count, String searchConditionDocs ) throws DBException {
		Connection conn = null;
	
		try {
			conn = WebShare.database.getConnection();
				
			return retrieveDocumentStatisticSeries( conn, from, calenderField, step, count, searchConditionDocs );
		} catch ( Exception e ) {
			throw new DBException( e );
		} finally {
			DBHelper.close( conn );
		}
	}
	
	public final static List retrieveDocumentStatisticSeries( Connection conn, GregorianCalendar from, int calenderField, int step, int count, String searchConditionDocs ) throws DBException {
	
	    ArrayList serie = new ArrayList();
	    GregorianCalendar until = (GregorianCalendar) from.clone();
	    until.add(calenderField, step);
        until.add(Calendar.MILLISECOND, -1);
	    
	    for (int iterCount = 1; iterCount <= count; iterCount++) {
	        serie.add(VKCStatisticsRetriever.retrieveDocumentStatistics(conn, from, until, searchConditionDocs));
	        from.add(calenderField, step);
	        until.add(calenderField, step);
            until.add(Calendar.MILLISECOND, -1);
	    }
	    
	    return serie;
	}

    public final static List retrieveDocumentStatisticSeriesCollection( GregorianCalendar from, int calenderField, int step, int count, String searchConditionDocs ) throws DBException {
        Connection conn = null;
    
        try {
            conn = WebShare.database.getConnection();
                
            return retrieveDocumentStatisticSeriesCollection( conn, from, calenderField, step, count, searchConditionDocs );
        } catch ( Exception e ) {
            throw new DBException( e );
        } finally {
            DBHelper.close( conn );
        }
    }
    
    public final static List retrieveDocumentStatisticSeriesCollection( Connection conn, GregorianCalendar from, int calendarField, int step, int count, String searchConditionDocs ) throws DBException {

        ArrayList collection = new ArrayList();
        GregorianCalendar until = (GregorianCalendar) from.clone();
        until.add(calendarField, step);
        until.add(Calendar.MILLISECOND, -1);
        
        for (int iterCount = 1; iterCount <= count; iterCount++) {
            collection.add(VKCStatisticsRetriever.retrieveDocumentStatisticSeries( conn, from, calendarField, step, count, searchConditionDocs ));
            from.add(calendarField, step);
            until.add(calendarField, step);
            until.add(Calendar.MILLISECOND, -1);
        }
        
        return collection;
    }    
    
	public final static List retrieveDocTypeStatistics( GregorianCalendar from, GregorianCalendar until, String searchCondition ) throws DBException {
		Connection conn = null;
	
		try {
			conn = WebShare.database.getConnection();
				
			return retrieveDocTypeStatistics( conn, from, until, searchCondition );
		} catch ( Exception e ) {
			throw new DBException( e );
		} finally {
			DBHelper.close( conn );
		}
	}
	
	public final static List retrieveDocTypeStatistics( Connection conn, GregorianCalendar from, GregorianCalendar until, String searchCondition ) throws DBException {
	
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		String sqlStatement = "";
		ArrayList list = new ArrayList();
/*
		Timestamp tsFrom = null;
		Timestamp tsUntil = null;
*/	
		try {
/*
            // convert into timestamp format 
			if ( from != null && until != null ) {
				tsFrom = new Timestamp( from.getTime().getTime() );
				tsUntil = new Timestamp( until.getTime().getTime() );
			} 
*/          
			sqlStatement = "     SELECT documentTypes.extension, COUNT(documents.typeId) AS count_type" +
						   "       FROM documents " + 
			               " RIGHT JOIN documentTypes ON documents.typeId = documentTypes.docTypeId" +
                           "   GROUP BY documentTypes.extension";
						   //" WHERE flag = " + VKCDocument.FLAG_PUBLISHED +
			               //"   AND published between ? and ? ";        
			
/*
            if ( from != null && until != null ) {				
				sqlStatement += " AND published BETWEEN ? AND ?";
				params.add( tsFrom );
				params.add( tsUntil );
			}
*/	                          
			if ( searchCondition != null && searchCondition.length() > 0 ) {
				sqlStatement += " AND " + searchCondition;
			}				            	

			System.out.println( sqlStatement );	

			pStmt = conn.prepareStatement( sqlStatement );		
            //DBHelper.setElements( pStmt, params.toArray() );
			rs = pStmt.executeQuery( sqlStatement );
	
			while (rs.next()) {
				VKCDocTypeStatistics dts = new VKCDocTypeStatistics();
				dts.setExtension(rs.getString("extension"));
				dts.setCountDocType(rs.getInt("count_type"));
				
				list.add(dts);
			}
			
			return list;
		} catch (Exception ex) {
			throw new DBException(ex);
		} finally {
			DBHelper.close(rs);
			DBHelper.close(pStmt);
		}
	}
	
	
	/**
	 * retrieves category statistics for a given time period
	 */
	public final static VKCCategoryStatistics getCategoryStatistics( GregorianCalendar from, GregorianCalendar until ) throws DBException {
		Connection conn = null;
	
		try {
			conn = WebShare.database.getConnection();
				
			return retrieveCategoryStatistics( conn, from, until, null );
		} catch ( Exception e ) {
			throw new DBException( e );
		} finally {
			DBHelper.close( conn );
		}
	}
	
	/**
	 * retrieves category statistics for a given time period
	 */
	public final static VKCCategoryStatistics retrieveCategoryStatistics( Connection conn, GregorianCalendar from, GregorianCalendar until, String searchConditionCats ) throws DBException {
		Timestamp tsFrom = null;
		Timestamp tsUntil = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		String sqlStatement = "";
		VKCCategoryStatistics catStat = new VKCCategoryStatistics();
		List params = new ArrayList();
	
		try {
			catStat.setFrom( from );
			catStat.setUntil( until );

			//	convert into timestamp format 
			if ( from != null && until != null ) {
				tsFrom = new Timestamp( from.getTimeInMillis() );
				tsUntil = new Timestamp( until.getTimeInMillis() );
			} 
	            
			/*
			 * retrieve document statistics: 
			 * count / amount of created documents and count of creators
			 */
			sqlStatement = "SELECT COUNT(categoryId) AS count_cats, COUNT(DISTINCT creatorId) AS count_creators" +
						   "  FROM categories";
	
			if ( from != null && until != null ) {
				sqlStatement += " WHERE created BETWEEN ? AND ?";
				params.add( tsFrom );
				params.add( tsUntil );
			}
	
			if ( searchConditionCats != null && searchConditionCats.length() > 0 ) {
				sqlStatement += " AND " + searchConditionCats;
			}
	
			pStmt = conn.prepareStatement( sqlStatement );
            DBHelper.setElements( pStmt, params.toArray() );
			rs = pStmt.executeQuery();
			rs.next();
	
	        catStat.setCountCreatedCats( rs.getInt( "count_cats" ) );
			catStat.setCountCreators( rs.getInt( "count_creators" ) );
			
			return catStat;
		} catch ( Exception e ) {
			throw new DBException( e );
		} finally {
			DBHelper.close( rs );
			DBHelper.close( pStmt );
		}
	}

	/**
	 * retrieves user statistics for a given time period
	 */
	public final static VKCUserStatistics retrieveUserStatistics( GregorianCalendar from, GregorianCalendar until ) throws DBException {
		Connection conn = null;
	
		try {
			conn = WebShare.database.getConnection();
				
			return retrieveUserStatistics( conn, from, until, null );
		} catch ( Exception e ) {
			throw new DBException( e );
		} finally {
			DBHelper.close( conn );
		}
	}

	/**
	 * retrieves user statistics for a given time period
	 */
	public final static VKCUserStatistics retrieveUserStatistics( Connection conn, GregorianCalendar from, GregorianCalendar until, String searchConditionUsers ) throws DBException {
		Timestamp tsFrom = null;
		Timestamp tsUntil = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		String sqlStatement = "";
		VKCUserStatistics userStat = new VKCUserStatistics();
		List params = new ArrayList();
	
		try {
			userStat.setFrom( from );
			userStat.setUntil( until );
	       
			//	convert into timestamp format 
			if ( from != null && until != null ) {
				tsFrom = new Timestamp( from.getTimeInMillis() );
				tsUntil = new Timestamp( until.getTimeInMillis() );
			} 

			/*
			 * count of users and count of creators 
			 */
			sqlStatement = "SELECT COUNT(*) AS count_users, COUNT (DISTINCT creatorId) AS count_creators" +
							   "  FROM users";
	
			if ( from != null && until != null ) {
				sqlStatement += " WHERE created BETWEEN ? AND ?";
				params.add( tsFrom );
				params.add( tsUntil );
			}
	
			if ( searchConditionUsers != null && searchConditionUsers.length() > 0 ) {
				sqlStatement += " AND " + searchConditionUsers;
			}
	 
			pStmt = conn.prepareStatement( sqlStatement );
            DBHelper.setElements( pStmt, params.toArray() );
			rs = pStmt.executeQuery();
			rs.next();
	
			userStat.setCountUsers( rs.getInt( "count_users" ) );
			userStat.setCountCreators( rs.getInt( "count_creators" ) );
	
			rs.close();
			params = new ArrayList();

			/*
			 * count of active users 
			 */
			sqlStatement = "SELECT COUNT(*) AS count_active_users" +
							   "  FROM users" +
							   " WHERE accountvalid <> 0"; 
	
			if ( from != null && until != null ) {
				sqlStatement += " AND mostrecentlogin BETWEEN ? AND ?";
				params.add( tsFrom );
				params.add( tsUntil );
			}
	
			if ( searchConditionUsers != null && searchConditionUsers.length() > 0 ) {
				sqlStatement += " AND " + searchConditionUsers;
			}
	 
			DBHelper.close( pStmt );
			pStmt = conn.prepareStatement( sqlStatement );
            DBHelper.setElements( pStmt, params.toArray() );
			rs = pStmt.executeQuery();
			rs.next();
	
			userStat.setCountActiveUsers( rs.getInt( "count_active_users" ) );
	
			rs.close();
			params = new ArrayList();
	
			/*
			 * count of passive users 
			 */
			sqlStatement = "SELECT COUNT(*) AS count_passive_users" +
							   "  FROM users" +
							   " WHERE accountvalid = 0";
	
			if ( from != null && until != null ) {
				sqlStatement += " AND created BETWEEN ? AND ?";
				params.add( tsFrom );
				params.add( tsUntil );
			}
	
			if ( searchConditionUsers != null && searchConditionUsers.length() > 0 ) {
				sqlStatement += " AND " + searchConditionUsers;
			}
	 
			DBHelper.close( pStmt );
			pStmt = conn.prepareStatement( sqlStatement );
            DBHelper.setElements( pStmt, params.toArray() );
			rs = pStmt.executeQuery();
			rs.next();
	
			userStat.setCountPassiveUsers( rs.getInt( "count_passive_users" ) );
	
			rs.close();
			params = new ArrayList();
	
			/*
			 * count of users with dedicated adminprivileges
			 */
			sqlStatement = "SELECT COUNT(*) AS count_dadmin_users" +
							   "  FROM users" +
							   " WHERE accountvalid <> 0" + 
							   "   AND adminprivileges = 0" +
							   "   AND (manageownusers <> 0 OR manageowngroups <> 0)";
	
			if ( from != null && until != null ) {
				sqlStatement += " AND created BETWEEN ? AND ?";
				params.add( tsFrom );
				params.add( tsUntil );
			}
	
			if ( searchConditionUsers != null && searchConditionUsers.length() > 0 ) {
				sqlStatement += " AND " + searchConditionUsers;
			}
	 
			DBHelper.close( pStmt );
			pStmt = conn.prepareStatement( sqlStatement );
            DBHelper.setElements( pStmt, params.toArray() );
			rs = pStmt.executeQuery();
			rs.next();
	
			userStat.setCountDAdminUsers( rs.getInt( "count_dadmin_users" ) );
	
			rs.close();
			params = new ArrayList();
			
			/*
			 * count of users with admin privileges
			 */
			sqlStatement = "SELECT COUNT(*) AS count_admin_users" +
							   "  FROM users" +
							   " WHERE accountvalid <> 0" + 
							   "   AND adminprivileges <> 0";
	
			if ( from != null && until != null ) {
				sqlStatement += " AND created BETWEEN ? AND ?";
				params.add( tsFrom );
				params.add( tsUntil );
			}
	
			if ( searchConditionUsers != null && searchConditionUsers.length() > 0 ) {
				sqlStatement += " AND " + searchConditionUsers;
			}
	 
			DBHelper.close( pStmt );
			pStmt = conn.prepareStatement( sqlStatement );
            DBHelper.setElements( pStmt, params.toArray() );
			rs = pStmt.executeQuery();
			rs.next();
	
			userStat.setCountAdminUsers( rs.getInt( "count_admin_users" ) );
	
			rs.close();
			params = new ArrayList();
	
			/*
			 * count of users created by dedicated admin users 
			 */
			sqlStatement = "SELECT COUNT(userId) AS count_users_dAdmin, COUNT(DISTINCT creatorId) AS count_creators" +
						   "  FROM users" +
						   " WHERE creatorId IN (SELECT userId" +
						   "                           FROM users" +
						   "                          WHERE manageownusers = 1)";
	
			if ( from != null && until != null ) {
				sqlStatement += " AND created BETWEEN ? AND ?";
				params.add( tsFrom );
				params.add( tsUntil );
			}
	    
			if ( searchConditionUsers != null && searchConditionUsers.length() > 0 ) {
				sqlStatement += " AND " + searchConditionUsers;
			}
	  
			DBHelper.close( pStmt );
			pStmt = conn.prepareStatement( sqlStatement );
            DBHelper.setElements( pStmt, params.toArray() );
			rs = pStmt.executeQuery();
			rs.next();
			
			return userStat;
		} catch ( Exception e ) {
			throw new DBException( e );
		} finally {
			DBHelper.close( rs );
			DBHelper.close( pStmt );
		}
	}
	
	/**
	 *  retrieves system statistics for today
	 */
	public final static VKCSystemStatistics retrieveSystemStatistics4Today() throws DBException {
		GregorianCalendar todayStart = new GregorianCalendar();
		GregorianCalendar todayEnd = new GregorianCalendar();
		
		todayStart.set( Calendar.HOUR_OF_DAY, 0 );
		todayStart.set( Calendar.MINUTE, 0 );
		todayStart.set( Calendar.SECOND, 0 );
		todayStart.set( Calendar.MILLISECOND, 0 );
		
		todayEnd.set( Calendar.HOUR_OF_DAY, 23 );
		todayEnd.set( Calendar.MINUTE, 59 );
		todayEnd.set( Calendar.SECOND, 59 );
		todayStart.set( Calendar.MILLISECOND, 999 );
		
		return retrieveSystemStatistics( todayStart, todayEnd );	
	}

	/**
	 *  retrieves system statistics for this week
	 */
	public final static VKCSystemStatistics retrieveSystemStatistics4CurrentWeek() throws DBException {
		GregorianCalendar today = new GregorianCalendar();
	
		today.setFirstDayOfWeek( Calendar.MONDAY );
		int dayOfWeek = today.get( Calendar.DAY_OF_WEEK );
		int daysFromMonday = ( dayOfWeek + 5 ) % 7;
	
        // compute monday of the week
		long timeMillis = today.getTimeInMillis();
		long mondayMillis = timeMillis - ( daysFromMonday * ( 60 * 60 * 24 * 1000 ) );
		GregorianCalendar startWeek = new GregorianCalendar();
		startWeek.setTimeInMillis( mondayMillis );
		
		// compute sunday of the week
		long sundayMillis = timeMillis - ( ( daysFromMonday - 6 ) * ( 60 * 60 * 24 * 1000 ) );
		GregorianCalendar endWeek = new GregorianCalendar();
		endWeek.setTimeInMillis( sundayMillis );
				
		startWeek.set( Calendar.HOUR_OF_DAY, 0 );
		startWeek.set( Calendar.MINUTE, 0 );
		startWeek.set( Calendar.SECOND, 0 );
		startWeek.set( Calendar.MILLISECOND, 0 );
		
		endWeek.set( Calendar.HOUR_OF_DAY, 23 );
		endWeek.set( Calendar.MINUTE, 59 );
		endWeek.set( Calendar.SECOND, 59 );
		endWeek.set( Calendar.MILLISECOND, 999 );
		
		return retrieveSystemStatistics( startWeek, endWeek );	
	}

	/**
	 *  retrieves system statistics for this year
	 */
	public final static VKCSystemStatistics retrieveSystemStatistics4CurrentMonth() throws DBException {
		GregorianCalendar startMonth = new GregorianCalendar();
		GregorianCalendar endMonth = new GregorianCalendar();
				
		startMonth.set( Calendar.DATE, 1 );
		startMonth.set( Calendar.HOUR_OF_DAY, 0 );
		startMonth.set( Calendar.MINUTE, 0 );
		startMonth.set( Calendar.SECOND, 0 );
		startMonth.set( Calendar.MILLISECOND, 0 );
		
		endMonth.set( Calendar.DAY_OF_MONTH, endMonth.getActualMaximum( Calendar.DAY_OF_MONTH ) );
		endMonth.set( Calendar.HOUR_OF_DAY, 23 );
		endMonth.set( Calendar.MINUTE, 59 );
		endMonth.set( Calendar.SECOND, 59 );
		endMonth.set( Calendar.MILLISECOND, 999 );
		
		return retrieveSystemStatistics( startMonth, endMonth );	
	}

	/**
	 *  retrieves system statistics for this year
	 */
	public final static VKCSystemStatistics retrieveSystemStatistics4CurrentYear() throws DBException {
		GregorianCalendar startYear = new GregorianCalendar();
		GregorianCalendar endYear = new GregorianCalendar();
				
		startYear.set( Calendar.MONTH, 1);
		startYear.set( Calendar.DAY_OF_MONTH, 0);
		startYear.set( Calendar.HOUR_OF_DAY, 0);
		startYear.set( Calendar.MINUTE, 0);
		startYear.set( Calendar.SECOND, 0);
		startYear.set( Calendar.MILLISECOND, 0);
		
		endYear.set( Calendar.MONTH, 11 );
		endYear.set( Calendar.DAY_OF_MONTH, 31 );
		endYear.set( Calendar.HOUR_OF_DAY, 23 );
		endYear.set( Calendar.MINUTE, 59 );
		endYear.set( Calendar.SECOND, 59 );
		endYear.set( Calendar.MILLISECOND, 999 );
		
		return retrieveSystemStatistics( startYear, endYear );	
	}

	/**
	 *  retrieves system statistics for all
	 */
	public final static VKCSystemStatistics retrieveSystemStatistics4All() throws DBException {
		GregorianCalendar start = new GregorianCalendar();
		GregorianCalendar today = new GregorianCalendar();
		
		start.set( Calendar.YEAR, 2003 );
		start.set( Calendar.MONTH, 0 );
		start.set( Calendar.DAY_OF_MONTH, 1 );
		start.set( Calendar.HOUR_OF_DAY, 0 );
		start.set( Calendar.MINUTE, 0 );
		start.set( Calendar.SECOND, 0 );
		start.set( Calendar.MILLISECOND, 0 );
		
		today.set( Calendar.HOUR_OF_DAY, 23 );
		today.set( Calendar.MINUTE, 59 );
		today.set( Calendar.SECOND, 59 );
		today.set( Calendar.MILLISECOND, 999 );
		
		return retrieveSystemStatistics( start, today );	
	}
}