Location: PHPKode > projects > Obsessive Website Statistics > ows/plugins/99_ows_manual.php
<?php
/*
	$Id: 99_ows_manual.php 112 2007-11-21 14:27:19Z 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/>.
	
	
	The point of this plugin is to allow construction of crazy queries that may
	or may not totally make sense. Its up to the user to try and make sense of 
	the results. A neat feature would be something that could 'save' a query.
	
	The biggest problem with this plugin is that queries it executes could 
	potentially take a ridiculous amount of time if it tries to order by a non-indexed 
	key, or execute	some other type of obscene query. Honestly, this should probably
	be done at a SQL prompt, except that manually doing joins on dimensions is REALLY
	annoying. Should add some sort of detection/timeout, and disable it using a 
	configuration directive called 'enable_DoS'.

	Note: need to triple check this one for security bugs... I don't think any exist, but SQL
	injection is a real threat here, since there is a ton of user input that is passed in the
	SQL query. We try to minimize this threat when possible by using the predefined variables
	in the arrays.
	
*/



class OWSManual implements iPlugin, iFilterPlugin, iLimitPlugin {

	var $prefix = '';
	
	var $dist_array = 
		array('yes' => array('yes','SELECT'), 'distinct' => array('distinct','SELECT DISTINCT'), 'distinctrow' => array('distinctrow','SELECT DISTINCTROW')); 
	
	var $where_array = 
		array('eq' => array('eq','='), 'neq' => array('neq','<>'), 
		'gte' => array('gte','>='), 'lte' => array('lte','<='), 'like' => array('like','LIKE'),
		'nlike' => array('nlike','NOT LIKE'), 'regexp' => array('regexp','REGEXP'), 'nregexp' => array('nregexp','NOT REGEXP'));
	
	var $ascdesc = 
		array('ASC' => array('ASC','ASC'), 'DESC' => array('DESC','DESC'));
		
	var $andor = 
		array('AND' => array('AND','AND'), 'OR' => array('OR','OR'));

	var $d_list = null;
	
	// piece of jQuery that duplicates a row.. it looks complex, but its really not
	var $onclick_duplicate = '<span class="addsub" onclick="$(this).parent().after($(this).parent().clone().css(\'display\',\'none\').fadeIn(\'normal\')).after(\'<br/>\');$(this).parent().children(\'.zlastitem\').each(function(){$(this).attr(\'disabled\',\'\').fadeIn();});">+</span> <span class="addsub" onclick="if($(this).parent().parent().children().length < 3){ return;} $(this).parent().fadeOut(\'normal\',function(){if($(this).prev().length) { $(this).prev().remove(); } else if ($(this).next().length) { $(this).next().remove(); } $(this).remove();}).children(\'.zlastitem\').each(function(){$(this).attr(\'disabled\',\'disabled\').fadeOut();});">-</span>';
		
	function __construct(){
		$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: 112 $')));
		return array(
			'author' => 'Dustin Spicuzza (OWS builtin)',
			'pluginName' => 'Manual Analysis Plugin',
			'version' => "1.0.$revision",
			'description' => 'Lets you do more advanced SQL-like queries on the statistics database by specifying the parameters that you want. Of course, this can make it harder to get the results that you want. Be aware that the optional parameters will affect the query as well.',
			'url' => 'http://obsessive.sourceforge.net/'
		);
	}

	
	
	private function getDList(){
	
		if ($this->d_list === null){
			
			$dimensions = compile_dimensions();
			ksort($dimensions);
			
			$this->d_list = array();
			
			foreach ($dimensions as $dname => $attrs){
				
				ksort($attrs);
				
				foreach ($attrs as $attr => $v)
					if ($attr != 'pnode_is')
						$this->d_list[] = $dname . '.' . $attr;
			}
		}
		
		return $this->d_list;
	}
	
	
	// returns name of filter to show to user
	public function getDisplayName(){
		return "Manual Analysis";
	}
	

	// this function outputs html which is displayed inside of a form
	// submit button is already defined for you. Call show_common_limits
	// to use the standard limit section (by date, by time, exclude bots, etc)
	public function showOptions(){
	
		$d_list = $this->getDList();
		
			
		// TODO: Write some neat javascript thing that assembles the SQL statement and shows you what it
		// looks like
		
		echo "<div><u>Items to show</u><p><table><tr><td>";
		
		echo "<span>" . 
			generate_select_from_array(htmlentities($this->prefix) . 'dist[]', 'yes', $this->dist_array) .
			generate_select_from_array(htmlentities($this->prefix) . 'select[]', null, $d_list, true, '-') . 
			" Group By " . generate_select_from_array(htmlentities($this->prefix) . 'group[]', null, $this->ascdesc, true, '-') . 
			" Order By " . generate_select_from_array(htmlentities($this->prefix) . 'order[]', null, $this->ascdesc, true, '-') . 
			" $this->onclick_duplicate</span>";
		
		echo "</td></tr></table></p><p><u>Conditions</u></p><p>Use optional limits to filter results (enable excessively obsessive options for best results).</p></div>";
		
		show_common_limits();	
	}


	
	// this function shows the filtered results
	public function showResults($domain){
		
		$params = array();
		$num_select = 0;
		
		$query = new SQLSelect($domain);
	
		//prn($_POST);
		$dimensions = $this->getDList();
	
		// heres the gameplan: grab all the variables, check to see if they're arrays, 
		// and if they are then check to see if the dimensions are valid. Then escape things
		// and perform the query.
	
		// grab em
	
		$dist = 		get_post_var($this->prefix . 'dist');
		$select =		get_post_var($this->prefix . 'select');
		$group = 		get_post_var($this->prefix . 'group');
		$order =		get_post_var($this->prefix . 'order');
		
		// validate all parameters (for the most part, better later)
		foreach (array($dist,$select,$group,$order) as $item)
			if (!is_array($item))
				return show_error("Item type was not posted correctly: " . htmlentities($item));
	
		
		// do the select part
		$selects = 0;
		foreach ($select as $k => $v){
		
			if ($v == -1)
				continue;
		
			if (!array_key_exists($k,$dist) || !array_key_exists($dist[$k],$this->dist_array))
				return show_error("Invalid DIST item!");
		
			// verify its in our list, then split it
			if (!in_array($v,$dimensions))
				return show_error("Invalid SELECT item!");
		
			$split = explode('.',$v);
			if (count($split) != 2)
				return show_error("Internal error: Invalid internal element for SELECT");
		
			// add the distinct
			$dnt = '';
			if ($dist[$k] != 'yes')
				$dnt = str_replace('SELECT ','',$this->dist_array[$dist[$k]][1]) . ' ';
		
			// add to the query now
			$field = $query->DIMENSION($split[0]) . '.' . db_escape_string($split[1]);
			$query->SELECT("$dnt$field" ,col_description($v));
			
			// add group
			if (!array_key_exists($k,$group) || ($group[$k] != -1 && !array_key_exists($group[$k],$this->ascdesc)))
				return show_error("Invalid GROUP item!");
				
			if ($group[$k] != -1)
				$query->GROUP_BY($field . ' ' . $this->ascdesc[$group[$k]][1]);
			
			// add order
			if (!array_key_exists($k,$order) || ($order[$k] != -1 && !array_key_exists($order[$k],$this->ascdesc)))
				return show_error("Invalid ORDER item!");
				
			if ($order[$k] != -1)
				$query->ORDER_BY($field . ' ' . $this->ascdesc[$order[$k]][1]);
				
			$selects += 1;
		}
		
		if ($selects == 0)
			return show_error("No columns selected!");
		
		
		// and we're done
		show_result_table($query);
	
	}
	
	// this function outputs html which is displayed inside of a table with two columns, inside of a
	// form. The plugin output should begin with <tr> and end with a </tr>. The form variables
	// that you use should always begin with a unique identifier. For example,
	//		echo "Form variable: <input name=\"" . md5(__FILE__) . "_form_variable_name\" value=\"\"/>";
	public function showLimits(){
		
		$d_list = $this->getDList();
		
		echo '<tr class="excessive"><td>WHERE</td><td><span>' . generate_select_from_array(htmlentities($this->prefix) . 'where[]', null, $d_list, true, '-') . generate_select_from_array( htmlentities($this->prefix) . 'whereop[]', null, $this->where_array, true, '-') . '<input type="text" name="' .  htmlentities($this->prefix) . 'wherecond[]" value="" />' . 
			'<select style="display:none" disabled="disabled" class="zlastitem" name="' .  htmlentities($this->prefix ) . 'whereand[]"><option value="AND">AND</option><option value="OR" selected>OR</option></select> ' . $this->onclick_duplicate . "</span></td></tr>";
	
	}
	
	// this function modifies the filter's SQL query. This function should look for 
	// form variables set by showOptions
	public function limitResults($domain,&$query){

		$dimensions = $this->getDList();
	
		$where = 		get_post_var($this->prefix . 'where');
		$whereop = 		get_post_var($this->prefix . 'whereop');
		$wherecond = 	get_post_var($this->prefix . 'wherecond');
		$whereand =		get_post_var($this->prefix . 'whereand');
		
		
		// validate all parameters (for the most part, better later)
		foreach (array($where,$whereop,$wherecond) as $item)
			if (!is_array($item))
				return;
	
		// add any additional conditions
		$wheresql = '';
		
		foreach ($where as $k => $v){
		
			if ($v == -1)
				continue;
		
			if ($v != -1 && !in_array($v,$dimensions))
				return show_error("Invalid WHERE item!");
				
			$split = explode('.',$v);
			if (count($split) != 2)
				return show_error("Internal error: Invalid internal element for WHERE");
		
			// add to the query now
			$field = $query->DIMENSION($split[0]) . '.' . db_escape_string($split[1]);
			$wheresql .= " $field ";
				
			if (!array_key_exists($k,$whereop) || !array_key_exists($whereop[$k],$this->where_array))
				return show_error("Invalid WHERE operator specified!");
			
			$wheresql .= $this->where_array[$whereop[$k]][1] . ' ';
			
			if (!array_key_exists($k,$wherecond))
				return show_error("Dangling WHERE condition posted in form!");
				
			if (is_numeric($wherecond[$k]))
				$wheresql .= $wherecond[$k];
			else
				$wheresql .= "'" . db_escape_string($wherecond[$k]) . "'";
			
			// special case it out
			if (is_array($whereand) && array_key_exists($k,$whereand))
				if (!array_key_exists($whereand[$k],$this->andor))
					return show_error("Invalid AND/OR condition passed!");
				else
					$wheresql .= ' ' . $this->andor[$whereand[$k]][1];
			
		}
		
		if ($wheresql != '')
			$query->WHERE($wheresql);
	
	}
	
}


$ows_manual = new OWSManual();
register_plugin('filter',$ows_manual);
register_plugin('limit',$ows_manual);

?>
Return current item: Obsessive Website Statistics