<?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());
?>