<?php

#############################################################################
#           			                   Site@School
# Site@School is a website management system designed for primary schools.
#
# About Site@School:
# Comissioned by: City District 'de Baarsjes', Amsterdam, Netherlands
# Original idea, coding (version 1.0) and design : Mark de Haan (mark@starwave.nl)
# Ideas, improvements, expansions (version 1.1 - 2.4): Fred Stuurman 
# Current coder & maintainer: Fred Stuurman (fc.stuurman@hccnet.nl)
############################################################################
# stats.php: last changed 06/09/2005  , version: 2.3.03
#            by Fred Stuurman (fc.stuurman@hccnet.nl)
#$Id: stats.php,v 1.1 2006/10/03 08:33:55 fstuurman Exp $
############################################################################
#                        LICENSE INFORMATION
# Copyright (C) 2002, 2003, 2004 Municipality of Amsterdam, City District
#  'de Baarsjes', Amsterdam, the Netherlands.
#
# 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 2
# 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, write to the Free Software
# Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA  02111-1307, USA.
##############################################################################
if (!defined('IN_SAS'))
{
	die("Hacking attempt");
}
if (IsSet ($_GET['s']))
{
	$s = $_GET['s'];
}
else
{
	$s = "";
}
if (IsSet ($_GET['addip']))
{
	$addip = $_GET['addip'];
}
else
{
	$addip = "";
}
if (IsSet ($_GET['suboption']))
{
	$suboption = $_GET['suboption'];
}
else
{
	$suboption = "remote";
}
if (IsSet ($_POST['ipfilter']))
{
	$ipfilter = $_POST['ipfilter'];
}
else
{
	$ipfilter = "";
}
if (IsSet ($_POST['remote']))
{
	$remote = $_POST['remote'];
}
if (IsSet ($_POST['local']))
{
	$local = $_POST['local'];
}
if (IsSet ($_POST['protected']))
{
	$protected = $_POST['protected'];
}
if (IsSet ($_GET['modoption']))
{
	$modoption = $_GET['modoption'];
}
$table_stats = $dbprefix . "stats";

include ("interface/header.inc.php");
include ("interface/menubar.inc.php");
include ("interface/menustart.inc.php");
print "<span class=heading>" . $sas_lang['filemanager_menu'] . "</span><br><br>";
print "<span class=link><a href=index.php?option=stats&site=protected&suboption=local>" . $sas_lang['stats_local'] . "</a><p>
       <a href=index.php?option=stats&suboption=remote>" . $sas_lang['stats_remote'] . "</a><p>
	   <a href=index.php?option=stats&suboption=protected>" . $sas_lang['protected_area'] . "</a><p>
       <a href=index.php?option=stats&suboption=config>" . $sas_lang['configuration'] . "</a><p>
       <a href=index.php?option=stats&suboption=clear>" . $sas_lang['clear_stats'] . "</a><p></span>";
include ("interface/menuend.inc.php");
include ("interface/pagestart.inc.php");
print "<center><span class=\"heading\">" . $sas_lang['stats'] . "<br></span></center>";

if (substr("$access", 3, 1) == "0") //bit 4 off the accessrights
{
	print "<span class=\"heading\">" . $sas_lang['noaccess'] . "<br></span>";
	print "<span class=\"text\">" . $sas_lang['noaccess_introduction'] . "</span><br><br><br>";
	include ("interface/pageend.inc.php");
	include ("interface/footer.inc.php");
	exit;
}

if ($modoption == "delete")
{
	mysql_query("DELETE FROM $table_stats WHERE pageid = '$_GET[pageid]'");
}

if ($suboption == "saveconfig")
{

	$i = 0;
	$ip_filter_new = "";
	while ($i < count($ipfilter))
	{
		if ($ipfilter[$i] != "")
		{
			$ip_filter_new .= $ipfilter[$i] . ",";
		}
		$i++;
	}
	$ip_filter_new = substr($ip_filter_new, 0, strlen($ip_filter_new) - 1); //strip last comma

	mysql_query("UPDATE $table_configuration SET config_value='$ip_filter_new' WHERE config_key='stats_ip_filter'");
	print "" . $sas_lang['configuration_saved'] . "<br>";
	exit;
}

//$ipfilter = $sas_config['stats_ip_filter'];
$ipfilter = explode(",", $sas_config['stats_ip_filter']);

$i = 0;
if (($ipfilter[0] == "") AND ($suboption == "local")) //no local ip adresses given
{
	$where_clause = " WHERE s.ip LIKE ('999.9.9.999')"; //for local we need at least one dummy IP
}
elseif ($ipfilter[0] == "")
{
	$where_clause = "";
}
else
{
	foreach ($ipfilter as $ip) //create the WHERE clause from ipfilter
	{
		if ($suboption == "local")
		{
			if ($i > 0)
			{
				$where_clause .= " OR s.ip LIKE ('$ip%')";
			}
			else
			{
				$where_clause = " WHERE s.ip LIKE ('$ip%')";
			}
		}

		if ($suboption == "remote")
		{
			if ($i > 0)
			{
				$where_clause .= " AND s.ip NOT LIKE ('$ip%')";
			}
			else
			{
				$where_clause = " WHERE s.ip NOT LIKE ('$ip%')";
			}
		}
		$i++;
	}
}

if ($suboption == "clearstats")
{
	$i = 0;
	if (($ipfilter[0] == "") AND ($suboption == "local")) //no local ip adresses given
	{
		$where_clause = " WHERE public = 0 AND s.ip LIKE ('999.9.9.999')"; //for local we need at least one dummy IP
	}

	foreach ($ipfilter as $ip) //create the WHERE clause from ipfilter
	{
		if ($local == "yes")
		{
			if ($i > 0)
			{
				$where_clause_local .= " OR ip LIKE ('$ip%')";
			}
			else
			{
				$where_clause_local = " WHERE public = 0 AND ip LIKE ('$ip%')";
			}
		}
		if ($remote == "yes")
		{
			if ($i > 0)
			{
				$where_clause_remote .= " AND ip NOT LIKE ('$ip%')";
			}
			else
			{
				$where_clause_remote = " WHERE public = 0 AND ip NOT LIKE ('$ip%')";
			}
		}
		$i++;
	}
	if ($local == "yes")
	{
		mysql_query("DELETE FROM $table_stats $where_clause_local");
		print "<p><span class=\"text\">Local: " . $sas_lang['stats_cleared'] . "</span>";
	}
	if ($remote == "yes")
	{
		mysql_query("DELETE FROM $table_stats $where_clause_remote");
		print "<p><span class=\"text\">Remote:" . $sas_lang['stats_cleared'] . "</span>";
	}
	if ($protected == "yes")
	{
		mysql_query("DELETE FROM $table_stats WHERE public = 1");
		print "<p><span class=\"text\">" . $sas_lang['protected_area'] . ": " . $sas_lang['stats_cleared'] . "</span>";
	}
	print "<br><br><input type=\"button\" value=\"&nbsp;&nbsp;&nbsp;" . $sas_lang['goback'] . "\" name=\"backbutton\" onClick=\"javascript:parent.location='index.php?option=stats';\" class=\"backbutton\">";

	include ("interface/pageend.inc.php");
	include ("interface/footer.inc.php");
	exit;
}

if ($suboption == "clear")
{
	print "<FORM METHOD=\"POST\" ACTION=\"index.php?option=stats&suboption=clearstats\"><br>";
	print "<span class=\"heading\">" . $sas_lang['clear_stats'] . ":</span><br>";
	print "<p><table><tr><td colspan=\"2\"><span class=\"text\">
	       " . $sas_lang['stats_clear_info'] . "</span></td></tr>";
	print "<tr><td colspan=\"2\">&nbsp;</td></tr>";
	print "<tr><td><span class=\"text\">" . $sas_lang['stats_local'] . "</span></td><td><input type=\"checkbox\" name=\"local\" value=\"yes\"></td></tr>";
	print "<tr><td><span class=\"text\">" . $sas_lang['stats_remote'] . "</span></td><td><input type=\"checkbox\" name=\"remote\" value=\"yes\"></td></tr>";
	print "<tr><td><span class=\"text\">" . $sas_lang['protected_area'] . "</span></td><td><input type=\"checkbox\" name=\"protected\" value=\"yes\"></td></tr>";
	print "</table>";
	print "<br><br><input type=\"button\" value=\"&nbsp;&nbsp;&nbsp;" . $sas_lang['cancel'] . "\" name=\"backbutton\" onClick=\"javascript:parent.location='index.php?option=stats';\" class=\"backbutton\">";
	print "\n&nbsp;<input type=\"submit\" value=\"&nbsp;&nbsp;&nbsp;" . $sas_lang['save'] . "\" name=\"savebutton\" class=\"savebutton\">";
	print "</span></form>";
	include ("interface/pageend.inc.php");
	include ("interface/footer.inc.php");
	exit;
}

if ($suboption == "config")
{
	$ip = $_SERVER["REMOTE_ADDR"]; //get your own ip address
	print "<br><span class=\"heading\">" . $sas_lang['configuration'] . "</span><br>
	       <span class=\"text\">(" . $sas_lang['stats_own_ip'] . "<b>$ip</b>)
	       <p>" . $sas_lang['stats_ip_introduction'] . "</span><p>";
	print "<FORM METHOD=\"POST\" ACTION=\"index.php?option=stats&suboption=saveconfig\">";
	print "<table>";
	$i = 0;
	while ($i < count($ipfilter))
	{
		print "<tr><td align=\"right\"><span class=\"text\">IP$i:</span></td>";
		print "<td><input tabindex=\"2\" type=text size=15 class=\"textfield\" name=\"ipfilter[$i]\" value=\"$ipfilter[$i]\"></td></tr>";
		$i++;
	}
	if ($addip == "yes")
	{
		print "<tr><td align=\"right\"><span class=\"text\">IP$i:</span></td>";
		print "<td><input tabindex=\"2\" type=text size=15 class=\"textfield\" name=\"ipfilter[$i]\"></td></tr>";
	}
	print "</table><input type=hidden name=\"number\" value=\"$i\">";
	print "<br><br><input type=\"button\" value=\"&nbsp;&nbsp;&nbsp;" . $sas_lang['cancel'] . "\" name=\"backbutton\" 
			onClick=\"javascript:parent.location='index.php?option=stats';\" class=\"backbutton\">";
	print "\n&nbsp;<input type=\"button\" value=\"&nbsp;&nbsp;&nbsp;" . $sas_lang['stats_add_ip'] . "\" name=\"addbutton\" 
			onClick=\"javascript:parent.location='index.php?option=stats&suboption=config&addip=yes';\" class=\"savebutton\">";
	print "\n&nbsp;<input type=\"submit\" value=\"&nbsp;&nbsp;&nbsp;" . $sas_lang['save'] . "\" name=\"savebutton\" class=\"savebutton\">";
	print "</span></form>";

	exit;
}

if ((IsSet ($s) == False) OR ($s == ""))
{
	$sort = "count DESC";
} //default sort is the count field
if ($s == 1)
{
	$sort = "p.name";
}
//if($s==2) {$sort="p.id";}
if ($s == 3)
{
	$sort = "e.name";
}
if ($s == 4)
{
	$sort = "count DESC";
}
if ($s == 5)
{
	$sort = "p.createdate DESC";
}
if ($s == 6)
{
	$sort = "p.lastvisitdate DESC";
}
if ($s == 7)
{
	$sort = "p.lastupdate DESC";
}

if ($suboption == "local")
{
	$result = mysql_query("SELECT p.id, p.name, SUM(s.count) as count, e.name, UNIX_TIMESTAMP(p.createdate), 
	UNIX_TIMESTAMP(p.lastvisitdate) , 
	p.section , UNIX_TIMESTAMP(p.lastupdate), s.pageid
	FROM $table_stats s 
	LEFT JOIN $table_pages p ON s.pageid=p.id 
	LEFT JOIN $table_sections e ON p.section=e.id
	$where_clause AND s.public = 0
	GROUP BY s.pageid 
	ORDER BY $sort");
	print "<span class=\"heading\">" . $sas_lang['stats_local'] . "</span><br>";
}

if ($suboption == "remote")
{
	$result = mysql_query("SELECT p.id, p.name, SUM(s.count) as count, e.name, 
	UNIX_TIMESTAMP(p.createdate), UNIX_TIMESTAMP(p.lastvisitdate) , 
	p.section , UNIX_TIMESTAMP(p.lastupdate), s.pageid
	FROM $table_stats s 
	LEFT JOIN $table_pages p ON s.pageid=p.id 
	LEFT JOIN $table_sections e ON p.section=e.id
	$where_clause AND s.public = 0
	GROUP BY s.pageid 
	ORDER BY $sort");
	print "<span class=\"heading\">" . $sas_lang['stats_remote'] . "</span><br>";
}

if ($suboption == "protected")
{
	$table_sections = $dbprefix . "prot_sections";
	$table_pages = $dbprefix . "prot_pages";

	$result = mysql_query("SELECT p.id, p.name, SUM(s.count) as count, e.name, 
	UNIX_TIMESTAMP(p.createdate), UNIX_TIMESTAMP(p.lastvisitdate) , 
	p.section , UNIX_TIMESTAMP(p.lastupdate), s.pageid
	FROM $table_stats s 
	LEFT JOIN $table_pages p ON s.pageid=p.id 
	LEFT JOIN $table_sections e ON p.section=e.id
	WHERE s.public = 1
	GROUP BY s.pageid 
	ORDER BY $sort");
	print "<span class=\"heading\">" . $sas_lang['protected_area'] . "</span><br>";
}

print "<span class=text>" . $sas_lang['stats_introduction'] . "</span><br><br>";
print "<table border=\"0\" cellpadding=\"2\" cellspacing=\"0\" style=\"border-collapse: collapse\" bordercolor=\"#111111\">";

print " <tr bgcolor=\"#638AB9\">
		  <td width=\"200\"><br><a href=index.php?option=stats&suboption=$suboption&s=1 STYLE=\"color: white; font-size: 10pt\"><b>" . $sas_lang['stats_col1'] . "</b></a></td>
		  <td align=\"left\" width=\"150\"><b><br><a href=index.php?option=stats&suboption=$suboption&s=3 STYLE=\"color: white; font-size: 10pt\">" . $sas_lang['stats_col3'] . "</b></font></td>
		  <td align=\"right\"  width=\"80\"><b><a href=index.php?option=stats&suboption=$suboption&s=4 STYLE=\"color: white; font-size: 10pt\">" . $sas_lang['stats_col4'] . "</b></font></td>
		  <td align=\"right\" width=\"100\"><b><a href=index.php?option=stats&suboption=$suboption&s=5 STYLE=\"color: white; font-size: 10pt\">" . $sas_lang['stats_col5'] . "</b></font></td>
		  <td align=\"right\" width=\"100\"><b><a href=index.php?option=stats&suboption=$suboption&s=6 STYLE=\"color: white; font-size: 10pt\">" . $sas_lang['stats_col6'] . "</b></font></td>
		  <td align=\"right\" width=\"100\"><b><a href=index.php?option=stats&suboption=$suboption&s=7 STYLE=\"color: white; font-size: 10pt\">" . $sas_lang['stats_col7'] . "</b></font></td>
		</tr>";
$i = 0;
while ($stats_list = mysql_fetch_array($result))
{
	$date1 = strftime($sas_config['dateformat'], $stats_list[4]);
	$date2 = strftime($sas_config['dateformat'], $stats_list[5]);
	$date3 = strftime($sas_config['dateformat'], $stats_list[7]);
	if ($i == 0)
	{
		if (trim($stats_list[1]) == "")
		{
			print "<tr bgcolor=\"#F7F7F7\">
							<td><span class=\"link\"><a href=\"index.php?option=stats&suboption=$suboption&modoption=delete&pageid=$stats_list[8]\">
							&nbsp;&nbsp;[" . $sas_lang['delete'] . "]</span></a></td>";
		}
		else
		{
			print "     <tr bgcolor=\"#F7F7F7\">
								<td><span class=\"link\"><a href=\"../index.php?section=$stats_list[6]&page=$stats_list[0]\">$stats_list[1]</span></a></td>";
		}
		print "		<td align=\"left\"><span class=\"text\">$stats_list[3]</span></td>
						<td align=\"right\"><span class=\"text\">$stats_list[2]</span></td>
						<td align=\"right\"><span class=\"text\">$date1</span></td>
						<td align=\"right\"><span class=\"text\">$date2</span></td>
						<td align=\"right\"><span class=\"text\">$date3</span></td>
					  </tr>";
		$i = 1;
	}
	else
	{
		if (trim($stats_list[1]) == "")
		{
			print "<tr bgcolor=\"#F7F7F7\">
							<td><span class=\"link\"><a href=\"index.php?option=stats&suboption=$suboption&modoption=delete&pageid=$stats_list[8]\">
							&nbsp;&nbsp;[" . $sas_lang['delete'] . "]</span></a></td>";
		}
		else
		{
			print "     <tr bgcolor=\"#CCDAEC\">
							<td><span class=\"link\"><a href=\"../index.php?section=$stats_list[6]&page=$stats_list[0]\">$stats_list[1]</span></a></td>";
		}
		print "<td align=\"left\"><span class=\"text\">$stats_list[3]</span></td>
						<td align=\"right\"><span class=\"text\">$stats_list[2]</span></td>
						<td align=\"right\"><span class=\"text\">$date1</span></td>
						<td align=\"right\"><span class=\"text\">$date2</span></td>
						<td align=\"right\"><span class=\"text\">$date3</span></td>
					  </tr>";
		$i = 0;
	}
}

print "</table>";

include ("interface/pageend.inc.php");
include ("interface/footer.inc.php");
?>