Location: PHPKode > projects > Obsessive Website Statistics > ows/plugins/30_ows_aggregate.php
<?php
/*
	$Id: 30_ows_aggregate.php 89 2007-08-15 01:14:52Z randomperson83 $

	Obsessive Web Statistics
    Copyright (C) 2007 Dustin Spicuzza <hide@address.com>

    This program is free software: you can redistribute it and/or modify
    it under the terms of the GNU General Public License as published by
    the Free Software Foundation, either version 3 of the License, or
    (at your option) any later version.

    This program is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
    GNU General Public License for more details.

    You should have received a copy of the GNU General Public License
    along with this program.  If not, see <http://www.gnu.org/licenses/>.
	
*/


class OWSAggregate implements iPlugin, iFilterPlugin{

	var $prefix;

	// types of reports
	// description =>		post var,	show default
	var $report_types = array(
		'Yearly' => 	array('byyear', 	false),
		'Monthly' => 	array('bymonth', 	true),
		'Month-Day' => 	array('bymonthday', false),
		'Weekly' => 	array('byweek', 	false),
		'Daily' => 		array('byday', 		true),
		'Weekday' => 	array('byweekday', 	false),
		'Hourly' => 	array('byhour', 	true),
		'1' => null,
		'Visited Pages' =>		array('visited', 	false),
		'Referrers' => 			array('referrers', 	false),
		'Domain Referrers' => 	array('refdomain', 	false)
	);
	
	
	// array of column options
	//	attribute => 			attribute, 		default 	use default  		dimension
	//											selection,	array for select,
	var $cols = array(
		'host' =>		array('host',			'unique', 	true, 	'host'),
		'pages' =>		array('pages',			'count', 	true,	'request'),
		'request' =>	array('request', 		'no', 		true, 	'request'),
		'filename' =>	array('filename', 		'count', 	true,	'request'),
		'bytes' =>		array('bytes', 			'total', 	false, 	'bytes'),
		'referrer' =>	array('referrer', 		'no', 		true,	'referrer'),
		'agent' =>		array('agent', 			'no', 		true,	'agent')
	);
	
	// arrays for generate_select_from_array
	var $col_disp = array(
		array('count','Total'),
		array('unique','Unique'),
		array('no','-')
	);
	
	var $col_disp_sum = array(
		array('total','Total'),
		array('no','-'));
	
	
	function __construct(){
	
		// adding things to the global key map
		global $db_key_map;
		$db_key_map['pages'] = array('Pages');
		$db_key_map['pages_s'] = array('Pages');
	
		$this->prefix = $this->getPluginId() . '_';
	}

	// this should return a unique ID identifying the plugin, should start with an alpha,
	// should use basename instead of just __FILE__ otherwise it could expose path information
	public function getPluginId(){
		return 'p'. md5(basename(__FILE__) . get_class());
	}


	// returns an associative array describing the plugin
	public function getPluginInformation(){
		 
		// automagically increment the revision number :)
		$revision = trim(str_replace('Rev:','',str_replace('$','','$Rev: 89 $')));
		return array(
			'author' => 'Dustin Spicuzza with Victor La (OWS builtin)',
			'pluginName' => 'Aggregate Analysis Plugin',
			'version' => "1.0.$revision",
			'description' => 'Shows Aggregate Analysis information',
			'url' => 'http://obsessive.sourceforge.net/'
		);
	}

	
	// returns name of filter to show to user
	public function getDisplayName(){
		return "Aggregate Analysis";
	}
	
	// this function outputs html which is displayed inside of a form
	// submit button is already defined for you
	public function showOptions(){
	
		$jquery_onclick = '<span class="addsub" onclick="$(this).before(\'<br/>\').before($(this).prev().prev().clone().css(\'display\',\'none\').fadeIn(\'normal\'));">+</span> ';
		$jquery_onclick .= '<span class="addsub" onclick="if(!$(this).prev().prev().prev().length){ return;} $(this).prev().prev().fadeOut(\'normal\',function(){$(this).prev().remove();$(this).remove();});">-</span>';
	
		// checkboxes: type of report:
		echo  '<div class="floater"><p><u>First Column:</u></p><div class="alignright">';
			
		foreach ($this->report_types as $k => $v)
			if ($v == null)
				echo "<br/>";
			else
				echo $k . plugin_checkbox($this,$v[0],$v[1]) . '<br/>';
			
		echo '</div></div>';

			
		// add (useful) columns the user can display
		echo '<div class="floater"><p><u>Counts to display:</u></p><table>';
		foreach($this->cols as $col)
			echo '<tr><td>' . col_name($col[0]) . '</td><td>' . generate_select_from_array($this->prefix . "c_$col[0]", $col[1], ($col[2] ? $this->col_disp : $this->col_disp_sum)) . '</td></tr>';
			
		echo '</table></div>';
		
		// create a select array
		$s = array(array('pkey','First Column'),array('ckey','Chronological Order'));
		foreach ($this->cols as $col)
			$s[] = array($col[0],col_name($col[0]));
			
		echo '<div class="floater"><p><u>Sorting:</u></p><table>' . 
			'<tr><td>Sort By</td><td><span>' . 
			generate_select_from_array($this->prefix . 'sortby[]','pkey',$s) . ' ' . 
			generate_select_from_array($this->prefix . 'ascdesc[]','desc',array(array('asc','Ascending'),array('desc','Descending'))) . 
			"</span> $jquery_onclick</td></tr></table></div>";
	
		show_common_limits();	
	}
	
	

		
	// this function shows the filtered results
	public function showResults($domain){
	
		global $cfg;
		
		foreach ($this->report_types as $qtype){
		
			if (get_post_var($this->prefix . $qtype[0]) != 'yes')
				continue;
				
			// get the query
			if (($query = $this->create_query($domain,$qtype[0])) === false)
				continue;
			
			show_result_table($query);
			
			echo "</div>";
		}
	}
	
	
	// gets the variable column based on post variables
	// Security note: this isn't user input, so nothing needs to get escaped, input is
	// directly from our defined column
	private function get_column($col, &$query){
		
		$varname = $col[0];
		$var = get_post_var($this->prefix . "c_$varname");
		
		// pages is a special case
		if ($varname == 'pages'){
		
			// ignore it
			if ($var == '' || $var == 'no')
				return true;
		
			$dim = $query->DIMENSION('request');
			return "SUM(IF($dim.is_page=TRUE,1,0))";
		}
		
		if ($var != '' && $var != 'no')
			$dimension = $query->DIMENSION($col[3]);
		
		switch($var){
			case 'count':
				return "COUNT($dimension.$varname)";
				
			case 'unique':
				return "COUNT(DISTINCT $dimension.$varname)";
				
			case 'total':
				return "SUM($dimension.$varname)";
				
			case "":
			case "no":
				return true;
				
			default:
				return false;
		}
	}
	
	
	// returns a query object of the correct type
	private function create_query($domain,$type){
	
		$query = new SQLSelect($domain);
		$o_pkey = '';		// this is used to do ordering
		$pkey = '';			// this is used to do ordering
		
		echo "<div class=\"floater\">";
		
		// each type should set the 'pkey' so the latter part of
		// the routine knows what to sort on
		
		// the time based items will order by id, instead of the real thing
		switch ($type){
			
			case 'byyear':
				$dimension = $query->DIMENSION('date');
				$o_pkey = "$dimension.year";
		
				echo "<h4>Yearly Aggregate Stats</h4>";
				$query->SELECT($o_pkey, col_description('Year'));
				$query->GROUP_BY($o_pkey);
				
				$pkey = $o_pkey;
				break;
			
			case 'bymonth':
				$dimension = $query->DIMENSION('date');
				$o_pkey = "CONCAT($dimension.month,'/',$dimension.year)";
				
				echo "<h4>Monthly Aggregate Stats</h4>";
				$query->SELECT($o_pkey, col_description("Month"));
				$query->GROUP_BY($o_pkey);
				
				$pkey = $query->FACT_TABLE() . ".id";
				break;
			
			case 'bymonthday':
				$dimension = $query->DIMENSION('date');
				$o_pkey = "CONCAT(MONTHNAME($dimension.date),' ',DAY($dimension.date))";
				
				echo "<h4>Month-Day Aggregate Stats</h4>";
				$query->SELECT($o_pkey, col_description("Day of year"));
				$query->GROUP_BY($o_pkey);
				
				$pkey = $query->FACT_TABLE() . ".id";
				break;
		
			case 'byweek':
				$dimension = $query->DIMENSION('date');
				$o_pkey = "$dimension.week";
			
				echo "<h4>Weekly Aggregate Stats</h4>";
				$query->SELECT($o_pkey, col_description("Week"));
				$query->GROUP_BY($o_pkey);
				
				$pkey = $query->FACT_TABLE() . ".id";
				break;
			
			case 'byday':
				$dimension = $query->DIMENSION('date');
				$o_pkey = "$dimension.date";
			
				echo "<h4>Daily Aggregate Stats</h4>";
				$query->SELECT($o_pkey, col_description("Date"));
				$query->GROUP_BY($o_pkey);
								
				$pkey = $o_pkey;
				break;
		
			case 'byweekday':
				$dimension = $query->DIMENSION('date');
				$o_pkey = "$dimension.day_of_week";
				
				echo "<h4>Weekday Aggregate Stats</h4>";
				$query->SELECT($o_pkey, col_description("Weekday"));
				$query->GROUP_BY($o_pkey);
				
				$pkey = $o_pkey;
				break;

			case 'byhour':
				$dimension = $query->DIMENSION('time');
				$o_pkey = "$dimension.hour";
				
				echo "<h4>Hourly Aggregate Stats</h4>";
				$query->SELECT($o_pkey, col_description("Hour"));
				$query->GROUP_BY($o_pkey);
				
				$pkey = $o_pkey;
				break;
				
			case 'visited':
				$dimension = $query->DIMENSION('request');
				$o_pkey = "$dimension.request";
				
				echo "<h4>Visited Pages Aggregate Stats</h4>";
				$query->SELECT($o_pkey, col_description('Visited Pages'));
				$query->GROUP_BY($o_pkey);
				
				$pkey = $o_pkey;
				break;
				
			case 'referrers':
				$dimension = $query->DIMENSION('referrer');
				$o_pkey = "$dimension.referrer";
			
				echo "<h4>Referrers Aggregate Stats</h4>";
				$query->SELECT($o_pkey,'referrer');
				$query->GROUP_BY($o_pkey);
				
				$pkey = $o_pkey;
				break;
				
			case 'refdomain':
				$dimension = $query->DIMENSION('referrer');
				$o_pkey = "$dimension.domain";
			
				echo "<h4>Referrer by domain Aggregate Stats</h4>";
				$query->SELECT($o_pkey,col_description('Domain'));
				$query->GROUP_BY($o_pkey);
				
				$pkey = $o_pkey;
				break;
				
			default:
				return false;
		}
		
			
		// order by.. magic, i tell ya.
		$sortby = get_post_var($this->prefix . 'sortby');
		$ascdesc = get_post_var($this->prefix . 'ascdesc');
		
		if (is_array($sortby) && is_array($ascdesc)){
			$sortby = array_unique($sortby);
			
			foreach ($sortby as $k => $v){
			
				// ascending or decending?
				$ad = (array_key_exists($k, $ascdesc) && in_array($ascdesc[$k],array('asc','desc')) ? " " . strtoupper($ascdesc[$k]) : "");
			
				// order by primary column
				if ($v == 'pkey')
					$query->ORDER_BY("$pkey$ad");
				
				// order by date... sorta
				else if ($v == 'ckey')
					$query->ORDER_BY($query->FACT_TABLE() . ".id$ad");
				
				// order by stuff
				else if (array_key_exists($v, $this->cols)){
				
					// get the column
					if (($col = $this->get_column($this->cols[$v],$query)) === false)
						return show_error("Invalid column option specified!");
					
					// if it was valid, include it
					if ($col !== true)
						$query->ORDER_BY($col . $ad);
				}else{
					return show_error("Invalid ordering column specified!");
				}
			}
		}
		
		
		
		// add the columns we're going to need
		foreach ($this->cols as $otype){
			
			if (($col = $this->get_column($otype,$query)) === false)
				return show_error("Invalid column option specified!");
			
			if ($col !== true)
				$query->SELECT($col,$otype[0] . '_s');
				
		}
		
	
		return $query;
	}


}

register_plugin('filter',new OWSAggregate());

?>
Return current item: Obsessive Website Statistics