/usr/share/squirrelmail/plugins/squirrel_logger/show

<?php

exit;

if (isset($_POST['user']))
   $user  = $_POST['user'];

if (isset($_POST['mquery']))
   $mquery = $_POST['mquery'];
else if (isset($_GET['mquery']))
   $mquery = $_GET['mquery'];
else 
   $mquery = '';

if (isset($_POST['nuke']))
   $nuke  = $_POST['nuke'];

// This file does the queries to show user activity info
// v1.1.0 12/15/03 Pat Winn (ptwinn@velocibyte.com)
// Be sure to enable a .htaccess file or something to prevent
// just anyone using it. 
// 
// Please edit the variables below in order to get this to
// work with your particular database settings and color likes.

//==============================
// edit these variables to taste
//==============================
$db        = 'squirrelmail_logging';
$dbuser    = 'email_admin';
$dbpasswd  = 'p00stf11x';
$dbhost    = 'localhost';

$tableName     = 'user_activity';
$usernameField = 'username';
$eventField    = 'event';
$addrField     = 'remote_address';
$dateField     = 'date';
$commentsField = 'comments';

//===============================================
// if you would like different background colors
// for the list of results, change those here.
//===============================================
// the header foreground color (font color)
$headerfgcolor = "black";

// the header background color
$headerbgcolor = "tan";

// the page background color
$pagebgcolor = "white";

// the default text color
$defaulttext = "black";

// first alternating list color
$color1 = "lightyellow";

// second alternating list color
$color2 = "lightgrey";

// page title color
$titlecolor = 'black';

// table borders on or off (0 = off, 1 = on)
$tableborder = 0;


//=============================================
// Nothing below here should need to be changed
//==============================================

$version = '1.1.0';

if(isset($nuke)) {
	deleteOldData($_POST['deldate']);
	return;
}

if(isset($user)) {
	show_user($user);
	return;
} 

switch($mquery) {
	case 'all': show_info('ALL'); break;
	case 'logins': show_info('LOGIN'); break;
	case 'logouts': show_info('LOGOUT'); break;
	case 'invalid': show_info('INVALID'); break;
	case 'timeout': show_info('TIMEOUT'); break;
	case 'error': show_info('ERROR'); break;
	case 'massmailing': show_info('MASS_MAILING'); break;
  	default: do_main(); break;
}

// show all logins
// $which = which kind of activity to show
function show_info($which) {
	global $tableName, $eventField;
	if($which == 'ALL')
    	$query = "select * from $tableName";
	else
    	$query = "select * from $tableName where $eventField='" . $which . "'";

	$result = doQuery($query); 
	$rowcount = mysql_num_rows($result);

	show_results($result, $rowcount, $which);
}

// show a given user
// $query = username to show
function show_user($user) {
	global $tableName, $usernameField;
	$query = "select * from $tableName where $usernameField like '%" . $user . "%'";
	$result = doQuery($query);
	$rowcount = mysql_num_rows($result);
	show_results($result, $rowcount, $user);
}

// show results of search
// $result = query result set
// $rowcount = number of rows in result set
// $which = what was searched for
function show_results($result, $rowcount, $which) {
	global $usernameField, $eventField, $addrField, $dateField, $commentsField;
	global $color1, $color2, $PHP_SELF, $version;
	global $pagebgcolor, $defaulttext, $tableborder;
	global $headerfgcolor, $headerbgcolor, $titlecolor;
        $PHP_SELF = $_SERVER['PHP_SELF'];

	echo "<html><head><title>SquirrelMail User Activity Log Viewer (v" . $version . ")</title>\n";
	echo "<meta name=\"author\" content=\"Pat Winn (ptwinn@velocibyte.com)\">\n";
	echo "</head>\n";
	echo "<body bgcolor=\"" . $pagebgcolor . "\" text=\"" . $defaulttext . "\">\n";
	echo "<center><h2><font color=" . $titlecolor . ">Search Results For: " . $which . "</font></h2>\n";
	echo "<br><br>\n";
	echo "<table border=" . $tableborder . ">";
        echo "<tr><td colspan='2' align='left'>Total: $rowcount</td>"
           . "<td colspan='2' align='right'><a href='" . $PHP_SELF . "'>&lt;&lt; Back</a></td></tr>";
	echo "<tr><th bgcolor=\"" . $headerbgcolor . "\"><font color=\"" . $headerfgcolor . "\">User</font></th><th bgcolor=\"" . $headerbgcolor . "\"><font color=\"" . $headerfgcolor . "\">Activity</font></th><th bgcolor=\"" . $headerbgcolor . "\"><font color=\"" . $headerfgcolor . "\">From Host</font></th><th bgcolor=\"" . $headerbgcolor . "\"><font color=\"" . $headerfgcolor . "\">Date</font></th></tr>";

	$c = 0;
	for($i = 0 ; $i < $rowcount ; $i++) {
		if($c == 0)
        		$color = $color1;
        	else
            		$color = $color2;

		$user = mysql_result($result, $i, $usernameField) . "&nbsp&nbsp&nbsp&nbsp";
		$activity = mysql_result($result, $i, $eventField);
		$fromHost = mysql_result($result, $i, $addrField) . "&nbsp&nbsp&nbsp&nbsp";
		$dateOccured = mysql_result($result, $i, $dateField);
		$comments = mysql_result($result, $i, $commentsField);

		if(mysql_result($result, $i, $eventField) == 'INVALID')
			$color = 'ff2727';

		$popupScript = "newwin=window.open(\"\", \"comments\", \"scrollbars=yes, resizable=yes, width=600, height=400\"); newwin.document.open(); newwin.document.write(\"<html><head><title>Log Comments/Details</title></head><body>" . $comments . "</body></html>\"); newwin.document.close(); return false;";

        	echo "<tr><td bgcolor=".$color.">" . $user . "</td><td bgcolor=".$color."><a href='#' onclick='$popupScript'>" . $activity ."</a>&nbsp;&nbsp;&nbsp;&nbsp;</td><td bgcolor=".$color.">" . $fromHost . "</td><td bgcolor=".$color.">" . $dateOccured . "</td></tr>\n";

		if($c == 1)
			$c = 0;
		else
			$c = 1;
	}

	echo "</table><br><br><center>";
	echo "<form action=\"" . $PHP_SELF . "\" method=post>";
	echo "<input type=submit value=\"Search Again\">";
	echo "</form>";
	echo "</center></td></tr>";
	echo "</table><br><br><br></center></body></html>";
}

// show the search/query page (default action)
function do_main() {
	global $PHP_SELF, $version;

	echo "<html>\n";
	echo "<head>\n";
	echo "<title>SquirrelMail User Activity Log Tools (v" . $version . ")</title>\n";
	echo "<meta name='author' content='Pat Winn (ptwinn@velocibyte.com)'>\n";
	echo "</head>\n";
	echo "<body>\n";
	echo "<center><h2>SquirrelMail User Activity Log Tools</h2>\n";
	echo "<hr><br><br><table><tr><td align=left>\n";
	echo "<form action=\"" . $PHP_SELF . "\" method=post>\n";
	echo "<table>\n";
	echo "<tr><td><b>Log Viewing Functions</b></td></tr>\n";
	echo "<tr><td>Show <a href='" . $PHP_SELF . "?mquery=all'>All activity</a></td></tr>\n";
	echo "<tr><td>Search for <a href='" . $PHP_SELF . "?mquery=logins'>Logins</a></td></tr>\n";
	echo "<tr><td>Search for <a href='" . $PHP_SELF . "?mquery=logouts'>Logouts</a></td></tr>\n";
	echo "<tr><td>Search for <a href='" . $PHP_SELF . "?mquery=timeout'>Timeouts</a></td></tr>\n";
	echo "<tr><td>Search for <a href='" . $PHP_SELF . "?mquery=invalid'>Invalid Logins</a></td></tr>\n";
	echo "<tr><td>Search for <a href='" . $PHP_SELF . "?mquery=error'>Errors</a></td></tr>\n";
	echo "<tr><td>Search for <a href='" . $PHP_SELF . "?mquery=massmailing'>Mass Mailings</a></td></tr>\n";
	echo "<tr><td>&nbsp</td></tr>\n";
	echo "<tr><td>Search for user: <input type=text name=user>&nbsp;&nbsp;\n";
	echo "<input type=submit value='Go Get It'></center></td></tr>\n";
	echo "<tr><td>(Hint: You can search for partial user names like ant instead of anthony)</td></tr></table>\n";
	echo "</form></td></tr><tr><td align=left><br><br>";
 	echo "<form action=\"" . $PHP_SELF . "\" method=post>\n"; 
	echo "<table><tr><td>&nbsp</td></tr>\n";
	echo "<tr><td><b>Management functions</b></td></tr>\n";
	echo "<tr><td>Delete data older than <input type=text size=10 name=deldate value=" . getolddate() . ">&nbsp;&nbsp;";
	echo "<input type=submit value='Delete' name=nuke></center></td></tr>\n";
	echo "<tr><td align=left><font color=red>(WARNING: This will NOT ask for verification!)</td></tr>\n";
	echo "</table></form></table></center></body></html>\n";
}

// return the current date
function dateNow($format="%Y%m%d")
{
	return(strftime($format,time()));
}

// get a date to use for example in delete query
function getolddate() {
	$this_year  = dateNow("%Y");
	$this_month = dateNow("%m");
	return($this_year . "-" . $this_month . "-01");
}

// delete old data from the database
// $_deldate = date to use (delete all data prior to this date)
// Returns: nothing
function deleteOldData($_deldate) {
	global $tableName, $dateField;
	global $db, $dbuser, $dbpasswd, $dbhost;

	if($_deldate != null) {
		// check that it's a valid date so we aren't doing something stupid
		$tmp = explode("-", $_deldate);
        $_yr = $tmp[0];
        $_mo = $tmp[1];
        $_da = $tmp[2];
		if(checkdate($_mo, $_da, $_yr)) {
			// good to go
			$query = "delete from $tableName where $dateField < '" . $_deldate . " 00:00:00'";
			$conn = mysql_connect($dbhost, $dbuser, $dbpasswd);
			mysql_select_db($db, $conn);
			$result = mysql_query($query);
			if(!$result) {
				echo "<center><br><br><br><font color=red size=+2>ERROR: " . mysql_error() . "\n";
        		echo "<br>Delete failed.</font></center>\n";
				mysql_close();
				return;
			}

			mysql_close();

			echo "<html><body><center><br><br><br>\n";
			echo "<font color=green size=+2>Deleted data prior to " . $_deldate . "</font><br><br>";
			echo "<form action=\"" . $PHP_SELF . "\" method=post>\n";
			echo "<input type=submit value='O.K.'></form></center></body></html>\n";
		} else {
			echo "<html><body><center><br><br><br>\n";
			echo "<font color=red size=+2>Please enter a valid date!</font><br><br>";
			echo "<form action=\"" . $PHP_SELF . "\" method=post>\n";
			echo "<input type=submit value='O.K.'></form></center></body></html>\n";
		}
	}
}

// Execute a database query
// $query = query string to use
// Returns: result set (empty if nothing found)
function doQuery($query) {
	global $db, $dbuser, $dbpasswd, $dbhost;

	$conn = mysql_connect($dbhost, $dbuser, $dbpasswd);
	mysql_select_db($db, $conn);
	$result = mysql_query($query . " order by date");
	mysql_close();
	$err = mysql_error();

	if($err) {
		echo "ERROR: Error code = " . $err . "\n";
		echo "<br>Search failed.\n";
		exit;
	}

	return($result);
}
squirrelmail-logger 2.3.1-1 / usr / share / squirrelmail / plugins / squirrel_logger / show_stats.php
