<?php 
/* $Id$ */
namespace Mammut\Info;
(defined('MAMMUT') && (basename(__FILE__) != basename($_SERVER['PHP_SELF']))) or die('ACCESS DENIED');

use \DateTime;
use Mammut\DB\DB;
use Mammut\DB\SQLException;
use Mammut\DB\DBException;

/**
 * Universal statistic class with brute force detection
 * 
 * @author Stefan Daurer <s.daurer@q-spot.org>
 * @package Mammut\Info
 */
class Statistic extends \Mammut\StrictObject {
	const _VERSION_ = '1.1.0.1';
	protected $tables = array(); // internal storage of table names
	

	/**
	 *
	 * @var \Mammut\DB\DB
	 */
	protected $db = null;
	
	// anti brute force settings
	protected $blacklist_time = 600; // timespan which should be scaned for brute force attacks (sec)
	protected $blacklist_count = 200; // number of connections in the timespan which triggers the blacklisting (num)
	protected $blacklist_bantime = 900; // how long a ip gets banned (sec)
	protected $blacklist = array();
	protected $blacklisted = NULL;
	protected $session_time = 600; // how long a session will be marked as online (10 min)
	protected $session_lifetime = 172800; // lifetime of session entries (2 days)
	protected $current_deltime = 172800; // the delay to the cleanup of the current-table (2 days)
	const COUNT_VISITS = 'visits'; // overall page visits
	const COUNT_HITS = 'hits'; // overall page views
	const COUNT_ONLINE = 'online'; // visitors currently online
	const COUNT_ONUSER = 'onuser'; // users currently online
	const COUNT_V_24H = 'v24h'; // 24h page visits		
	const COUNT_H_24H = 'h24h'; // 24h page views
	const MAX_ONLINE = 'mx_online'; // maximum visitors online
	const MAX_ONUSER = 'mx_onuser'; // maximum users online
	const MAX_V_24H = 'mx_v24h'; // maximum 24h page visits		
	const MAX_H_24H = 'mx_h24h'; // maximum 24h page views

	/**
	 * creates a new statistic object
	 *
	 * to initialize the statistic object, use something linke this:
	 * <pre>
	 * $sinf = array();
	 * $_SESSION['statistics']	= &$sinf;
	 * $stat = new MFStatistic($myDB, 'statistic', $sinf);
	 * </pre>
	 *
	 * @param DB $database
	 *        	database connection which should be used
	 * @param string $tablePrefix
	 *        	name prefix,
	 *        	tables are
	 *        	_hour			complete statistic on hour base
	 *        	_hour_bot		same as _hour, but with user agents which are known as bots
	 *        	_current		last 48h statistic
	 *        	_current_bot	same as _current, but with user agents which are known as
	 *        	bots
	 *        	_referer		referer storage	(no bots here)
	 *        	_values			hit count, max count etc...
	 * @param string $sessionId
	 *        	unique session identifier
	 */
	public function __construct(DB &$database, $tablePrefix) {
		$this->tables['agents'] = $tablePrefix . '_agents';
		$this->tables['targets'] = $tablePrefix . '_targets';
		$this->tables['bothits'] = $tablePrefix . '_bothits';
		$this->tables['full'] = $tablePrefix . '_full';
		$this->tables['current'] = $tablePrefix . '_current';
		$this->tables['inout'] = $tablePrefix . '_inout';
		$this->tables['referer'] = $tablePrefix . '_referer';
		$this->tables['values'] = $tablePrefix . '_values';
		$this->tables['sessions'] = $tablePrefix . '_sessions';
		$this->tables['blacklist'] = $tablePrefix . '_blacklist';
		
		$this->db = &$database;
	}

	public function setSessionTime($time) {
		$this->session_time = (int) $time;
	}

	public function getSessionTime() {
		return (int) $this->session_time;
	}

	/**
	 * updates the statistic
	 *
	 * @param int $siteid
	 *        	identificator (on systems with mutliple sites)
	 * @param int $userid
	 *        	id (0 for anonymous)
	 * @param string $module
	 *        	ident (section)
	 * @param string $view
	 *        	ident of the module
	 * @param bool $ignoreBF
	 *        	brute force ips in statistic
	 */
	public function update($siteid, $sessionid, $userid, $module, $view, $extra = '', $ignoreBF = true) {
		$old_tz = date_default_timezone_get();
		date_default_timezone_set('UTC');
		
		$oldAbortSetting = ignore_user_abort(true);
		$db = &$this->db;
		
		$uts = time();
		$utsh = gmmktime(gmdate('H', $uts), 0, 0, gmdate('m', $uts), gmdate('d', $uts), gmdate('Y', $uts));
		
		$ip = &$_SERVER['REMOTE_ADDR'];
		$target = &$_SERVER['REQUEST_URI'];
		
		$efn_site = $db->escapeColumnName('site_id');
		$efn_ip = $db->escapeColumnName('ip');
		$efn_lasthit = $db->escapeColumnName('lasthit');
		$efn_date = $db->escapeColumnName('timestamp');
		
		$efn_targetid = $db->escapeColumnName('target_id');
		$efn_toggel = $db->escapeColumnName('toggel');
		
		// update blacklist
		$sql = 'UPDATE ' . $db->escapeTableName($this->tables['blacklist']) . ' SET ' . $efn_lasthit . '=' . $uts . ',' . $efn_toggel . '=(' . $efn_toggel . '+1)%4' . ' WHERE ' . $efn_site . '=' . $siteid . ' AND ' . $efn_ip . '=' . $db->escapeValue($ip) . ' AND (' . $efn_lasthit . '>' . ($uts - $this->blacklist_bantime) . ' OR ' . $efn_lasthit . '=0)';
		
		$db->query($sql);
		if($this->isBlacklisted($siteid) && !$ignoreBF)
			return false; //	attacker!
			

		// get or create target id
			// building where cause of the query
		$where = array( // this data will be used for the insert, too. the crc mod 2000000000 is because the usage of 32 bit unsigned integer fields.
				'site_id' => $siteid,'file' => $_SERVER['PHP_SELF'],
				'file_hash' => crc32($_SERVER['PHP_SELF']) % 2000000000,				// improves performance
'instance' => $module,
				'view' => $view,'extra' => $extra,'extra_hash' => crc32($extra) % 2000000000)		// improves performance
;
		$conditions = array();
		foreach($where as $field=>$value) {
			$conditions[] = $db->escapeColumnName($field) . '=' . $db->escapeValue($value);
		}
		$where_st = implode(' AND ', $conditions);
		
		// building select query
		$sql = 'SELECT ' . $db->escapeColumnName('id') . ' FROM ' . $db->escapeTableName($this->tables['targets']);
		$sql .= ' WHERE ' . $where_st;
		
		$row = $db->getArray($sql);
		if(empty($row['id'])) { // add new entries
			$data = $where;
			$db->table($this->tables['targets'])->insert($data);
			$targetId = $db->getInsertId();
		}
		else
			$targetId = $row['id'];
		unset($data, $where, $where_st, $field, $value, $sql, $row); // cleanup
		

		$browser = UserAgent::getInstance();
		
		if($browser->isBot()) { // check if the visiter is a automated web crawler
			$botService = $browser->getBotService();
			$data = array(
					'site_id' => $siteid,'botname' => $botService,
					'date' => $db->uts2datetimeCol($uts),'target_id' => $targetId,
					'uri' => $_SERVER['REQUEST_URI']);
			$db->table($this->tables['bothits'])->insert($data);
			return true;
		}
		
		$efn_uid = $db->escapeColumnName('user_id');
		$efn_sid = $db->escapeColumnName('session_id');
		
		// TODO: add the out info in the inout table
		

		// clean up the session table
		$sql = 'DELETE FROM ' . $db->escapeTableName($this->tables['sessions']) . ' WHERE dead=1';
		$db->query($sql);
		
		// update existing sessions
		$sql = 'UPDATE ' . $db->escapeTableName($this->tables['sessions']) . ' SET ' . $efn_lasthit . '=' . $uts . ',' . $efn_toggel . '=(' . $efn_toggel . '+1)%4,' . $efn_uid . '=' . $db->escapeValue((int) $userid) . ',' . $efn_targetid . '=' . $db->escapeValue($targetId) . ' WHERE ' . $efn_site . '=' . $siteid . ' AND dead=0' . ' AND ' . $efn_sid . '=' . $db->escapeValue($sessionid) . ' AND ' . $efn_ip . '=' . $db->escapeValue($ip) . ' AND (' . $efn_lasthit . '>' . ($uts - $this->session_time) . ' OR ' . $efn_lasthit . '=0)';
		$db->query($sql);
		$num = $db->getAffectedRowCount();
		
		// marking outdated sessions
		$ts = $uts - $this->session_time;
		$sql = 'UPDATE ' . $db->escapeTableName($this->tables['sessions']) . ' SET dead=1 WHERE ' . $efn_lasthit . '<?';
		$db->executeP($sql, array($ts));
		
		if($num > 1) { // something went wrong, so we try to fix it
			$sql = 'SELECT * FROM ' . $db->escapeTableName($this->tables['sessions']) . ' WHERE ' . $efn_site . '=' . $siteid . ' AND ' . $efn_sid . '=' . $db->escapeValue($sessionid) . ' AND ' . $efn_ip . '=' . $db->escapeValue($ip) . ' ORDER BY ' . $efn_date . ' ASC';
			
			// backup the oldest dataset
			$data = $db->getArray($sql);
			
			$sql = 'DELETE FROM ' . $db->escapeTableName($this->tables['sessions']) . ' WHERE ' . $efn_site . '=' . $siteid . ' AND ' . $efn_sid . '=' . $db->escapeValue($sessionid) . ' AND ' . $efn_ip . '=' . $db->escapeValue($ip);
			$db->query($sql);
			
			// restore the dataset
			$db->table($this->tables['sessions'])->insert($data);
		}
		
		$newVisitor = false;
		
		if($num == 0) { // no updates mean the user is a new visitor, so we add session entry
			$newVisitor = true;
			$data = array(
					'site_id' => $siteid,'session_id' => $sessionid,'ip' => $ip,'timestamp' => $uts,
					'lasthit' => $uts,'target_id' => (int) $targetId,'user_id' => (int) $userid,
					'toggel' => 0,'dead' => 0);
			$db->table($this->tables['sessions'])->insert($data);
			
			$data = array(
					'site_id' => $siteid,'type' => 0,'timekey' => new \DateTime('@' . $uts),
					'agent_id' => -1,'target_id' => (int) $targetId);
			$r = $db->table($this->tables['inout'])->select($data);
			$data2 = $r->fetchArray();
			$r->close();
			
			if(empty($data2)) {
				$data['hits'] = 1;
				$data2 = $db->table($this->tables['inout'])->insert($data);
			}
			else {
				$data2 = array('hits' => $data2['hits'] + 1);
				$db->table($this->tables['inout'])->update($data2, $data);
			}
			
			if($this->blacklist_time > 0) {
				// blacklist add check
				// we don't need to check this on old sessions, because they cannot be
				// a bf-attacker 
				$ts = $uts - $this->blacklist_time;
				$sql = 'SELECT COUNT(*) AS c FROM ' . $db->escapeTableName($this->tables['sessions']) . ' WHERE ' . $efn_site . '=?' . ' AND ' . $efn_date . '=' . $efn_lasthit . ' AND ' . $efn_lasthit . '>?' . ' AND ' . $efn_ip . '=?';
				$num = $db->getObjectP($sql, array($siteid,$ts,$ip))->c;
				
				if($num > $this->blacklist_count) {
					if(!$this->isBlacklisted($siteid)) {
						$data = array(
								'site_id' => $siteid,'ip' => $ip,'timestamp' => $uts,
								'lasthit' => $uts);
						$db->table($this->tables['blacklist'])->insert($data);
						$this->blacklisted = true; // update cached value
					}
					$newVisitor = $ignoreBF;
				}
			}
		}
		unset($num, $efn_toggel); // cleanup
		

		$efn_key = $db->escapeColumnName('key');
		$efn_value = $db->escapeColumnName('value');
		
		$sql = 'UPDATE ' . $db->escapeTableName($this->tables['values']) . ' SET ' . $efn_value . '=' . $efn_value . '+1' . ' WHERE ' . $efn_site . '=' . $siteid;
		
		// if the user is a new visitor, update both entries. if not, update just the hits value
		if($newVisitor)
			$sql .= ' AND ' . $efn_key . ' IN (' . $db->escapeValue('hits') . ',' . $db->escapeValue('visits') . ')';
		else
			$sql .= ' AND ' . $efn_key . '=' . $db->escapeValue('hits');
		$db->query($sql);
		
		unset($sql, $efn_key, $efn_value); // cleanup
		

		if($db->getAffectedRowCount() == 0) {
			// add new entries
			$data = array('site_id' => $siteid,'key' => 'visits','value' => 1);
			$db->table($this->tables['values'])->insert($data);
			$data['key'] = 'hits';
			$db->table($this->tables['values'])->insert($data);
		}
		
		$this->updateMaxValues($siteid);
		
		// update the 'current data table
		$ts = $db->uts2datetimeCol($uts - $this->current_deltime);
		$db->executeP('DELETE FROM ' . $db->escapeTableName($this->tables['current']) . 'WHERE ' . $db->escapeColumnName('date') . '<?', array(
				$ts));
		$data = array(
				'site_id' => $siteid,'date' => $db->uts2datetimeCol($uts),
				'visit' => $newVisitor ? 1 : 0,'hit' => 1,'user' => (int) $userid);
		$db->table($this->tables['current'])->insert($data);
		
		$referer = &$_SERVER['HTTP_REFERER'];
		$self = empty($_SERVER['HTTP_HOST']) ? $_SERVER['SERVER_NAME'] : $_SERVER['HTTP_HOST'];
		if(($referer != '') && (!preg_match('%^((http|https|ftp)://){0,1}[a-zA-Z0-9_.-]*' . $self . '%i', $referer))) {
			$data = array(
					'site_id' => $siteid,'timekey' => new DateTime(),
					'referer' => gzdeflate($referer, 9),'target_id' => (int) $targetId,
					'target' => $target);
			
			$db->table($this->tables['referer'])->insert($data);
		}
		
		$agentId = $this->getAgentId($browser);
		
		// building where cause of the query
		$where = array( // this data will be used for the insert, too
				'site_id' => $siteid,'timekey' => $db->uts2datetimeCol($utsh),
				'target_id' => (int) $targetId,'agent_id' => (int) $agentId);
		$where_st = '1';
		$conditions = array();
		foreach($where as $field=>$value)
			$conditions[] = $db->escapeColumnName($field) . '=' . $db->escapeValue($value);
		$where_st = implode(' AND ', $conditions);
		unset($conditions);
		
		$efn_hits = $db->escapeColumnName('hits');
		$efn_visits = $db->escapeColumnName('visits');
		
		// building update query
		$sql = 'UPDATE ' . $db->escapeTableName($this->tables['full']);
		$sql .= ' SET ' . $efn_hits . '=' . $efn_hits . '+1';
		if($newVisitor)
			$sql .= ', ' . $efn_visits . '=' . $efn_visits . '+1';
		$sql .= ' WHERE ' . $where_st;
		
		$db->query($sql);
		
		if($db->getAffectedRowCount() == 0) {
			// add new entries
			$data = $where;
			$data['hits'] = 1;
			$data['visits'] = $newVisitor ? 1 : 0;
			$db->table($this->tables['full'])->insert($data);
		}
		unset($data, $where, $where_st, $field, $value, $sql, $row); // cleanup
		

		// restore the previous value
		ignore_user_abort($oldAbortSetting);
		date_default_timezone_set($old_tz);
	}

	/**
	 * updates the max values if nessesary
	 */
	private function updateMaxValues($siteId) {
		// get current values and update max values if needed
		$online = $this->getCount(self::COUNT_ONLINE, $siteId);
		$onuser = $this->getCount(self::COUNT_ONUSER, $siteId);
		$db = &$this->db;
		
		$result = $db->getObjectListP('SELECT * FROM ' . $db->escapeTableName($this->tables['values']) . ' WHERE ' . $db->escapeColumnName('site_id') . '=?', array(
				$siteId));
		
		$values = array();
		foreach($result as $row)
			$values[$row->key] = $row->value;
			
			// max online counter
		if(!isset($values['max_on'])) {
			$data = array('site_id' => $siteId,'key' => 'max_on','value' => $online);
			$db->table($this->tables['values'])->insert($data);
		}
		elseif($values['max_on'] < $online) {
			$where = array('site_id' => $siteId,'key' => 'max_on');
			$data = array('value' => $online);
			$db->table($this->tables['values'])->update($data, $where);
		}
		// max user online counter
		if(!isset($values['max_uon'])) {
			$data = array('site_id' => $siteId,'key' => 'max_uon','value' => $onuser);
			$db->table($this->tables['values'])->insert($data);
		}
		elseif($values['max_uon'] < $onuser) {
			$where = array('site_id' => $siteId,'key' => 'max_uon');
			$data = array('value' => $onuser);
			$db->table($this->tables['values'])->update($data, $where);
		}
	}

	/**
	 * scans the agent table for a matching entry.
	 * if none is found, a new one is created. the id
	 * of the entry is returned
	 */
	public function getAgentId(&$browser) {
		$db = &$this->db;
		
		// try to get user agent id, add him if not found
		$row = $db->getArrayP('SELECT ' . $db->escapeColumnName('id') . ' FROM ' . $db->escapeTableName($this->tables['agents']) . 'WHERE ' . $db->escapeColumnName('name') . '=?', array(
				$browser->getAgentString()));
		
		if(empty($row['id'])) {
			$data = array('name' => $browser->getAgentString());
			$db->table($this->tables['agents'])->insert($data);
			return (int) $db->getInsertId();
		}
		else
			return (int) $row['id'];
	}

	/**
	 * returns the count of visits/hits in a specific
	 * 
	 * @param string $what
	 *        	of the MFStatistic::VAL_* constants
	 * @return int count of the selected type, or false if type was invalid
	 */
	public function getCount($what = self::COUNT_VISITS, $siteid = 0) {
		$db = &$this->db;
		
		$valuetable = $db->table($this->tables['values']);
		$uts = time();
		
		try {
			switch($what) {
				case self::COUNT_VISITS:
				case self::COUNT_HITS:
					$infodata = array();
					$infodata['site_id'] = $siteid;
					$infodata['key'] = ($what == self::COUNT_VISITS ? 'visits' : 'hits');
					$result = $valuetable->select($infodata);
					if($result->getRowCount() > 0)
						$value = $result->fetchObject()->value;
					else
						$value = 0;
					$result->close();
					return (int) $value;
				break;
				
				case self::MAX_ONLINE:
				case self::MAX_ONUSER:
					$infodata = array();
					$infodata['site_id'] = $siteid;
					$infodata['key'] = ($what == self::MAX_ONLINE ? 'max_on' : 'max_uon');
					$result = $valuetable->select($infodata);
					if($result->getRowCount() > 0)
						$value = $result->fetchObject()->value;
					else
						$value = 0;
					$result->close();
					return (int) $value;
				break;
				
				case self::COUNT_ONLINE:
				case self::COUNT_ONUSER:
					$sql = 'SELECT COUNT(*) AS c FROM ' . $db->escapeTableName($this->tables['sessions']) . ' WHERE ' . $db->escapeColumnName('site_id') . '=?' . ' AND ' . $db->escapeColumnName('lasthit') . '>?';
					
					if($what == self::COUNT_ONUSER)
						$sql .= ' AND ' . $db->escapeColumnName('user_id') . '>0';
					return (int) $db->getObjectP($sql, array($siteid,$uts - $this->session_time))->c;
				break;
				
				case self::COUNT_V_24H:
					
					$datekey = date('YmdH', time() - (24 * 3600));
					$v24h = (int) $db->getObject('SELECT SUM(`visits`) AS c FROM ' . $db->escapeTableName($this->tables['hour']) . ' WHERE (`hour` + DAY *100 + MONTH *10000 + year *1000000) > ' . $datekey . ' AND `siteid`=' . $siteid)->c;
					return $v24h;
				break;
				
				case self::MAX_V_24H:
					$infodata = array();
					$infodata['siteid'] = $siteid;
					$infodata['key'] = 'max_24h';
					$result = $valuetable->select($infodata);
					if($result->getRowCount() > 0)
						$value = $result->fetchObject()->value;
					else
						$value = 0;
					$result->close();
					return (int) $value;
				break;
			}
		}
		catch(SQLException $e) {
			echo 'SQL ERROR:' . $e->getMessage() . ' (SQL=' . $e->getQuery() . ')<br/>';
		}
	}

	public function getTimeStats($startdate = false, $enddate = false, $steptype = 'hour', $siteid = false, $file = false, $instance = false, $view = false, $extra = false, $desc = false) {
		$steptype = strtoupper($steptype);
		
		$db = $this->db;
		$efn_siteid = $db->escapeColumnName('site_id');
		$efn_timekey = $db->escapeColumnName('timekey');
		$efn_hits = $db->escapeColumnName('hits');
		$efn_visits = $db->escapeColumnName('visits');
		$efn_site = $db->escapeColumnName('siteid');
		
		if($startdate === false || $enddate === false) {
			$row = $db->getArray('SELECT MIN(' . $efn_timekey . ') AS min_date, MAX(' . $efn_timekey . ') AS max_date ' . 'FROM ' . $db->escapeTableName($this->tables['full']) . ($siteid ? ' WHERE ' . $efn_site . '=' . $siteid : ''));
			$startdate = ($startdate === false) ? $db->datetimeCol2uts($row['min_date']) : $startdate;
			$enddate = ($enddate === false) ? $db->datetimeCol2uts($row['max_date']) : $enddate;
			unset($row);
		}
		
		if($startdate > $enddate)
		    throw new \BadMethodCallException('startdate > enddate');
		
		$stepstring = '';
		switch($steptype) {
			case 'H':
			case 'HOUR':
				$stepstring = '+1 hour';
				$datepart = getdate($startdate);
				$startdate = mktime($datepart['hours'], 00, 00, $datepart['mon'], $datepart['mday'], $datepart['year']);
				$datepart = getdate($enddate);
				$enddate = mktime($datepart['hours'], 00, 00, $datepart['mon'], $datepart['mday'], $datepart['year']);
			break;
			case 'D':
			case 'DAY':
				$stepstring = '+1 day';
				$datepart = getdate($startdate);
				$startdate = mktime(00, 00, 00, $datepart['mon'], $datepart['mday'], $datepart['year']);
				$datepart = getdate($enddate);
				$enddate = mktime(00, 00, 00, $datepart['mon'], $datepart['mday'], $datepart['year']);
			break;
			case 'W':
			case 'WEEK':
				$stepstring = '+7 days';
				$datepart = getdate($startdate);
				$startdate = mktime(00, 00, 00, $datepart['mon'], $datepart['mday'], $datepart['year']);
				$datepart = getdate($enddate);
				$enddate = mktime(00, 00, 00, $datepart['mon'], $datepart['mday'], $datepart['year']);
			break;
			case 'M':
			case 'MONTH':
				$stepstring = '+1 month';
				$datepart = getdate($startdate);
				$startdate = mktime(00, 00, 00, $datepart['mon'], $datepart['mday'], $datepart['year']);
				$datepart = getdate($enddate);
				$enddate = mktime(00, 00, 00, $datepart['mon'], $datepart['mday'], $datepart['year']);
			break;
			case 'Y':
			case 'YEAR':
				$stepstring = '+1 year';
				$datepart = getdate($startdate);
				$startdate = mktime(00, 00, 00, $datepart['mon'], date('t', $startdate), $datepart['year']);
				$datepart = getdate($enddate);
				$enddate = mktime(00, 00, 00, $datepart['mon'], date('t', $startdate), $datepart['year']);
			break;
			default:
				throw new \BadMethodCallException('steptype can only be hour,day,week,month or year');
		}
		
		$fieldlist = 'a.' . $db->escapeColumnName('timekey');
		if($siteid >= 0 || $siteid === true)
			$fieldlist .= ',a.' . $db->escapeColumnName('site_id');
		if($file !== false)
			$fieldlist .= ',b.' . $db->escapeColumnName('file');
		if($instance !== false)
			$fieldlist .= ',b.' . $db->escapeColumnName('instance');
		if($view !== false)
			$fieldlist .= ',b.' . $db->escapeColumnName('view');
		if($extra !== false)
			$fieldlist .= ',b.' . $db->escapeColumnName('extra');
		
		$values = array();
		$old_tz = date_default_timezone_get();
		date_default_timezone_set('UTC');
		$i = 0;
		
		while($startdate < $enddate && $i < 1000) { // add missing entries
			$i++;
			if($stepstring == '+1 month') {
				// keep in mind: met +1, but dst +2 (for germany) - so we need some workaround
				date_default_timezone_set($old_tz);
				$datepart = getdate($startdate);
				$intervalend = mktime(00, 00, 00, $datepart['mon'], date('t', $startdate) + 1, $datepart['year']);
				date_default_timezone_set('UTC');
			}
			else
				$intervalend = strtotime($stepstring, $startdate);
			
			if(!isset($values[$startdate])) {
				$values[$startdate] = array('iso' => date('Y-m-d H:i:s', $startdate) . ' UTC');
			}
			
			$sql = 'SELECT ' . $fieldlist . ',SUM(' . $efn_hits . ') AS sum_hits, SUM(' . $efn_visits . ') AS sum_visits ';
			$sql .= 'FROM ' . $db->escapeTableName($this->tables['full']) . ' AS a ';
			if($file !== false || $instance !== false || $view !== false || $extra !== false) {
				$sql .= 'LEFT OUTER JOIN ' . $db->escapeTableName($this->tables['targets']) . ' AS b ';
				$sql .= 'ON a.' . $db->escapeColumnName('target_id') . '=b.' . $db->escapeColumnName('id') . ' ';
			}
			if($startdate != $intervalend)
				$sql .= 'WHERE a.' . $efn_timekey . '>=' . $db->escapeValue($db->uts2datetimeCol($startdate)) . ' ' . 'AND a.' . $efn_timekey . '<' . $db->escapeValue($db->uts2datetimeCol($intervalend)) . ' ';
			else
				$sql .= 'WHERE a.' . $efn_timekey . '==' . $db->escapeValue($db->uts2datetimeCol($startdate)) . ' ';
			
			if(is_numeric($siteid))
				$sql .= 'AND a.' . $efn_siteid . '=' . $siteid . ' ';
			$sql .= 'GROUP BY ' . $fieldlist . ' ';
			$sql .= 'ORDER BY ' . $fieldlist;
			
			$result = $db->query($sql);
			unset($sql); // cleanup
			

			while($row = $result->fetchObject()) {
				
				// find/create the target value
				$target = &$values[$startdate];
				if($siteid === true) {
					if(!isset($target['#site'][$row->site_id]))
						$target['#site'][$row->site_id] = array();
					$target = &$target['#site'][$row->site_id];
				}
				if($file !== false) {
					if(!isset($target['#files'][$row->file]))
						$target['#files'][$row->file] = array();
					$target = &$target['#files'][$row->file];
				}
				if($instance !== false) {
					if(!isset($target['#instance'][$row->instance]))
						$target['#instance'][$row->instance] = array();
					$target = &$target['#instance'][$row->instance];
				}
				if($view !== false) {
					if(!isset($target['#view'][$row->view]))
						$target['#view'][$row->view] = array();
					$target = &$target['#view'][$row->view];
				}
				if($extra !== false) {
					if(!isset($target['#extra'][$row->extra]))
						$target['#extra'][$row->extra] = array();
					$target = &$target['#extra'][$row->extra];
				}
				
				// insert/update the value into the target leave
				if(!isset($target['visits']))
					$target['visits'] = 0;
				if(!isset($target['hits']))
					$target['hits'] = 0;
				
				$target['visits'] += (int) $row->sum_visits;
				$target['hits'] += (int) $row->sum_hits;
			}
			$result->close();
			unset($result);
			
			$startdate = $intervalend;
		}
		
		date_default_timezone_set($old_tz);
		
		if($desc)
			krsort($values, SORT_NUMERIC);
		else
			ksort($values, SORT_NUMERIC);
		
		return $values;
	}

	public function getBotStats($siteid = 0, $startdate = false, $enddate = false, $desc = true) {
		$db = $this->db;
		$efn_siteid = $db->escapeColumnName('site_id');
		$efn_botname = $db->escapeColumnName('botname');
		$efn_date = $db->escapeColumnName('date');
		
		$sql = 'SELECT COUNT(*) AS c, ' . $efn_botname . ' FROM ' . $db->escapeTableName($this->tables['bothits']) . ' WHERE ' . $efn_siteid . '=' . $siteid;
		
		if($startdate)
			$sql .= ' AND ' . $efn_date . '>=' . $db->uts2DatetimeCol($startdate);
		if($enddate)
			$sql .= ' AND ' . $efn_date . '<=' . $db->uts2DatetimeCol($enddate);
		
		$sql .= ' GROUP BY ' . $efn_botname . ' ORDER BY c ' . ($desc ? 'DESC' : 'ASC');
		
		$values = array();
		$result = $db->query($sql);
		
		while($row = $result->fetchObject())
			$values[$row->botname] = $row->c;
		
		return $values;
	}

	public function getAgentStats($siteid = 0, $startdate = false, $enddate = false, $desc = true) {
		$db = $this->db;
//			$efn_date = $db->escapeColumnName('date');
		$efn_timekey = $db->escapeColumnName('timekey');
		$efn_agentid = $db->escapeColumnName('agent_id');
		
		$sql = 'SELECT COUNT(*) as c, ' . $efn_agentid . ' ' . 'FROM ' . $db->escapeTableName($this->tables['full']) . ' ' . 'WHERE 1=1 ';
		
		if(!empty($startdate))
			$sql .= ' AND ' . $efn_timekey . '>=\'' . $db->uts2DatetimeCol((int) $startdate) . '\' ';
		if(!empty($enddate))
			$sql .= ' AND ' . $efn_timekey . '<\'' . $db->uts2DatetimeCol((int) $enddate) . '\' ';
		$sql .= 'GROUP BY ' . $efn_agentid;
		
		$values = array();
		$rows = $db->getObjectList($sql);
		
		$all = 0;
		foreach($rows as $row) {
			$agent = $db->getObjectP("SELECT * FROM " . $db->escapeTableName($this->tables['agents']) . " WHERE id=?", array(
					$row->agent_id));
			$browser = new UserAgent($agent->name);
			unset($agent);
			if(!$browser->isBot()) {
				$all += $row->c;
				
				$info = $browser->getBrowserInfo();
				$value = 0;
				if(isset($values['browser'][$info['name']]['_']))
					$value = (int) $values['browser'][$info['name']]['_'];
				$value += $row->c;
				$values['browser'][$info['name']]['_'] = $value;
				
				$value = 0;
				if(isset($values['browser'][$info['name']][$info['major']]['_']))
					$value = (int) $values['browser'][$info['name']][$info['major']]['_'];
				$value += $row->c;
				$values['browser'][$info['name']][$info['major']]['_'] = $value;
				
				$info = $browser->getOSInfo();
				$value = 0;
				if(isset($values['os'][$info['id']]['_']))
					$value = (int) $values['os'][$info['id']]['_'];
				$value += $row->c;
				$values['os'][$info['id']]['_'] = $value;
				
				$info = $browser->getOSInfo();
				$value = 0;
				if(isset($values['os'][$info['id']][$info['subid']]['_']))
					$value = (int) $values['os'][$info['id']][$info['subid']]['_'];
				$value += $row->c;
				$values['os'][$info['id']][$info['subid']]['_'] = $value;
				
				unset($info);
			}
		}
		$values['_'] = $all;
		
		return $values;
	}

	public function getRefererStats($siteid = 0, $startdate = false, $enddate = false, $desc = true) {
	}

	public function getActiveSessionList($siteId = 0, $timespan = 900) {
		$db = $this->db;
		
		$efn_timestamp = $db->escapeColumnName('timestamp');
		$efn_file = $db->escapeColumnName('file');
		$efn_instance = $db->escapeColumnName('instance');
		$efn_view = $db->escapeColumnName('view');
		
		if($siteId != 0)
			$result = $db->getObjectListP("SELECT a.session_id, a.user_id, a.ip, a.{$efn_timestamp}, a.lasthit, b.{$efn_file}, b.{$efn_instance}, b.{$efn_view}, b.extra FROM " . $db->escapeTableName($this->tables['sessions']) . " a LEFT JOIN " . $db->escapeTableName($this->tables['targets']) . " b ON a.target_id=b.id AND a.site_id=b.site_id WHERE a.site_id=? AND a.lasthit>?", array(
					$siteId,time() - $timespan));
		else
			$result = $db->getObjectListP("SELECT a.session_id, a.user_id, a.ip, a.{$efn_timestamp}, a.lasthit, b.{$efn_file}, b.{$efn_instance}, b.{$efn_view}, b.extra FROM " . $db->escapeTableName($this->tables['sessions']) . " a LEFT JOIN " . $db->escapeTableName($this->tables['targets']) . " b ON a.target_id=b.id AND a.site_id=b.site_id WHERE a.lasthit>?", array(
					time() - $timespan));
		return $result;
	}

	/**
	 * defines the parameter of a "brute force attack"
	 *
	 * @param int $countlimit
	 *        	of unique visits per time frame. more visits activete brute force
	 *        	methods
	 * @param int $timelimit
	 *        	frame in seconds, visits in this time frame are counted
	 * @param int $bantime
	 *        	long an ip gets banned (default: 900 seconds = 15 min)
	 */
	public function setBlacklistLimits($countlimit = -1, $timelimit = -1, $bantime = 900) {
		$this->blacklist_time = (int) $countlimit;
		$this->blacklist_count = (int) $timelimit;
		$this->blacklist_bantime = (int) $bantime;
	}

	/**
	 * returns a list of all ips which are seems to try to brute force the page.
	 * brute forcer typically try to access a page very often in a specific time, without
	 * sessions (activity time).
	 * the resulting array is a list of all ips which have accessed the page $counterlimit
	 * times in the last $timelimit seconds,
	 * but haven't stayed longer then $maxactivity seconds. the defaults are
	 * 'one visit every second for the last hour with a maximum of 3 seconds per visit'.
	 * commom browsers aren't affacted by this becaus they accept sessions, so the
	 * maxactivity will be exclude them for being listed.
	 */
	public function getBlacklist($siteid = 0) {
		if(array_key_exists((int) $siteid, $this->blacklist))
			return $this->blacklist[(int) $siteid];
		
		$db = &$this->db;
		
		$this->blacklist[(int) $siteid] = array();
		$result = $db->table($this->tables['blacklist'])->select(array('site_id' => $siteid));
		while($next = $result->fetchArray()) {
			$this->blacklist[(int) $siteid][] = $next['ip'];
		}
		return $this->blacklist[(int) $siteid];
	}

	/**
	 *
	 * @return boolean if the current remote address behaviour looks like a brute force
	 *         attacker
	 */
	public function isBlacklisted($siteid = 0) {
		if(is_null($this->blacklisted))
			$this->blacklisted = (boolean) (in_array($_SERVER['REMOTE_ADDR'], $this->getBlacklist($siteid)));
		return $this->blacklisted;
	}

	/**
	 * creates the tables needed by the script
	 *
	 * @param boolean $overwrite
	 *        	this to true allows the method to overwrite existing tables
	 */
	public function createTables($overwrite = false) {
	}

	/**
	 * checks if all tables needed by the statistic class exists
	 * 
	 * @return boolean true if all tables needed by this class exist
	 */
	public function tablesExist() {
		return (count($this->tablesMissing()) == 0);
	}

	/**
	 *
	 * @return array a list of the idents of tables missing.
	 */
	public function tablesMissing() {
		$missing = array();
		foreach($this->tables as $id=>$name) {
			try {
				$this->db->table($name);
			}
			catch(DBException $e) {
				$missing[$id] = $name;
			}
		}
		return $missing;
	}
}