PHP Connection Log Viewer for Grimoire

[quicky2g]

Ok first let me start by saying I am not going to change or modify any code in this script. I am not a PHP coder. I got this script from a friend who went on a PHP coding binge a while ago and now he cant remember how most of the script works. I will entertain any questions related to using the script usage.

Please read the first few lines in the script about how comments work...it is important if you want to change the script to fit your needs.

So the purpose of this script is to view the connection logs created by Grimoire. This script was specifically built to view logs for Grimoire v4.1.0 and v5.1.0. you can copy the old connection logs from v4.1.0 into the same directory as your v5.1.1 logs. Even though the logs are different formats, the script takes that into account. If you are using logs from Grimoire v4.1.0 and v5.1.1, you do not need to modify this script, other than your log file directory (Line 22) and any preferences you may want to set at the beginning of the script. If you are not using log files from v4.1.0 and v5.1.1, see lines 112 to 151. This is very important that you delete the sections of code that need to be deleted for your specific log implementation or your log searches could be slowed up a bit.

You should be able to use this script with Apache or Microsoft IIS. I have only tested it with Apache for Windows. It is up to the individual using this script to decide who has access to the webpage and if it is ssl encrypted. I personally do not use ssl for this site, but the site is only viewable by computers on my LAN and is password protected.

Make sure you enable support for PHP in whatever web server you choose or obviously this won't work.

If you are setting up a default web server, I suggest you create a file called index.php in the web root directory and copy the code below into it.

A little info about usage...

YOU MUST PUT THE NICKNAME, IP ADDRESS, OR WHATEVER ELSE YOU ARE USING IN EXACTLY AS IT APPEARS IN THE LOG. If I put in "quicky" as a search criteria for nickname, I will get nothing back...I have to put in "quicky2g". There is no wild card character although I wish there was. If any php coders have any suggestions it would be more than welcome!

Every time you reload the web page, the default setting is to view logs for today only and raw results are turned off. When you do a search with the default settings, you will see only values for each column that are different.

If you turn on the raw results, you will see something like this:
Note: The ipaddress does show up in numerical form, I just took mine out for obvious reasons.

-	12/10/2008	00:19:05	quicky2g	ipaddress	Ultramon 3.0.2(Dual Monitor Soft.)	quicky2g@gmail.com	DC++ 0.707	1.03 TiBs	A	1000 
+	12/10/2008	00:19:07	quicky2g	ipaddress	Ultramon 3.0.2(Dual Monitor Soft.)	quicky2g@gmail.com	DC++ 0.707	1.03 TiBs	A	1000 
-	12/10/2008	00:23:50	quicky2g	ipaddress	Ultramon 3.0.2(Dual Monitor Soft.)	quicky2g@gmail.com	DC++ 0.707	1.03 TiBs	A	1000 
+	12/10/2008	00:24:08	quicky2g	ipaddress	Ultramon 3.0.2(Dual Monitor Soft.)	quicky2g@gmail.com	DC++ 0.707	1.03 TiBs	A	1000 
-	12/10/2008	01:17:44	quicky2g	ipaddress	Ultramon 3.0.2(Dual Monitor Soft.)	quicky2g@gmail.com	DC++ 0.707	1.03 TiBs	A	1000

Pretty neat stuff...

So here's the code:

<title>DC Hub Connection Logs</title>
<?php
//Note: anything with "//" before it is a comment
//Anything with "/*" is the start of a comment for a block of code
//Anything with "*/" is the end of a comment for a block of code
//Rename this file to index.php and place it in your root web directory if you want it to load by default

/*
Uncomment these lines if you want to restrict access to this site to only 127.0.x.x networks
and 197.145.x.x networks. You can change the IP address values as needed. Delete this comment also
$test = "^197.145";
$test2 = "^127.0";
$ip = $_SERVER['REMOTE_ADDR'];
if (!eregi($test,$ip) && !eregi($test2,$ip)){
  echo "Please wait...";
  exit(0);
}
*/

ini_set('display_errors','FALSE'); //we don't want to see any errors; set to TRUE for debugging
error_reporting(E_ALL); //no error reporting; set to error_reporting(0); for debugging
$logfolder = "C:/0.4.1.1/scripts/Rincewind's Grimoire/Settings/Security/UserLog/Logs"; //folder containing the Grimore logs
if (is_dir($logfolder)){
	chdir($logfolder);
	$error = "";
}
else {
	$error = "Could not find log folder.";
}
$debug = FALSE; //Do not show debugging information
$dbx = array();
$forbidden = array(
    "",
    "..",
    ".",
    "RECYCLER",
    "System Volume Information",
    "Copy of "
);

//logfile format:
//connect-disconnect, date, time, name, ip, description, email, version, share, mode, speed

?>
<h1 onclick="window.location='grimoirelogsfinal.php'">Search</h1>
<?php echo "<font color=red>$error</font>" ?>
<table>
    <tr>
        <td>Location
        <td>String
        <td>Search only today
        <td>
    </tr>
    <tr>
    <form name="search" method=get action=<?php echo $_SERVER['SCRIPT_NAME'] ?>>
    	<td><select name="location">
    	    <option value=0>C/D
            <option value=1>Date
            <option value=2>Time
            <option value=3 selected>Nick
            <option value=4>IP
            <option value=5>Comment
            <option value=6>Email
            <option value=7>Version
            <option value=8>Share
            <option value=9>Mode
            <option value=10>Connection
            </select>
         <td><input type="text" name="searchstring">
         <td><input type="checkbox" name="t" checked>
         <td><button>Search</button>
     </tr>
</table>
<table>
    <tr>
        <td>C/D
        <td>Time
        <td>Date
        <td>Nick
        <td>IP
        <td>Comment
        <td>Email
        <td>Version
        <td>Share
        <td>Mode
        <td>Connection
    </tr>
    <tr>
        <td><input type="checkbox" name="0">
        <td><input type="checkbox" name="1">
        <td><input type="checkbox" name="2" checked>
        <td><input type="checkbox" name="3" checked>
        <td><input type="checkbox" name="4" checked>
        <td><input type="checkbox" name="5">
        <td><input type="checkbox" name="6" checked>
        <td><input type="checkbox" name="7">
        <td><input type="checkbox" name="8" checked>
        <td><input type="checkbox" name="9">
        <td><input type="checkbox" name="10">
    </tr>
</table>
<br><input type="checkbox" name="debug"> Debug
<br><input type="checkbox" name="raw"> Raw results <p>
<?php
if (isset($_GET['debug'])){
	$debug=TRUE;
}
if ($debug){
  echo "<tt>Opening Directory: $logfolder<br>";
}
if (isset($_GET['location'])){
	//Old log file format for logs in Grimoire v4.1.0
	//If you are using Grimoire v5.1.1 only...delete the entire section of code below
	//until the next comment where I tell you to stop
	$files = scandir($logfolder);
    	if (isset($_GET['t'])){
				$filename = "ConnectionLog-" . date('Y-m-d') . ".csv";
				$files = "";
				$files = array($filename);
			}
	for($i=0; $i<count($files); $i++){
	    if(is_file($files[$i]) && !array_search($files[$i],$forbidden)){
			if ($debug){
				echo "<tt>Loading $files[$i]...</tt><br>";
	  		}

			load_file($files[$i]);
		}
	}

	//Stop deleting the section for the Grimoire v4.1.0 logs here

	//New log file format for logs in Grimoire v5.1.1
	//If you are using a previous version of Grimoire (like v4.1.0)...delete the entire section of code below
	//until the next comment where I tell you to stop
	$files = scandir($logfolder);
    	if (isset($_GET['t'])){
				$filename = "UserLog-" . date('Y-m-d') . ".csv";
				$files = "";
				$files = array($filename);
			}
	for($i=0; $i<count($files); $i++){
	    if(is_file($files[$i]) && !array_search($files[$i],$forbidden)){
			if ($debug){
				echo "<tt>Loading $files[$i]...</tt><br>";
	  		}

			load_file($files[$i]);
		}
	}
	//Stop deleting the section for the Grimoire v5.1.1 logs here

	$results_local = parse_data();
	global $results;
	$results = $results_local;
	if ($debug){
	    echo "Results:<pre>"; print_r($results); echo "</pre>";
		for ($j=0; $j<=10; $j++){
			for ($i=0; $i<20; $i++){
				echo "($i,$j)<br>";
	  		}
	 	}
 	}
    if ($debug){
    	//echo "Search:<pre>"; print_r($dbx); echo "</pre>";
   		echo "<tt>Finished.</tt><br>";
	}
	if (isset($_GET['raw'])){

 	}
	?>
	<table border=1>
	    <tr>
	        <?php
                if (isset($_GET['0'])){
					echo "<td>C/D\r";
				}
				if (isset($_GET['1'])){
					echo "<td>Time\r";
				}
				if (isset($_GET['2'])){
					echo "<td>Date\r";
				}
				if (isset($_GET['3'])){
					echo "<td>Nick\r";
				}
				if (isset($_GET['4'])){
					echo "<td>IP\r";
				}
				if (isset($_GET['5'])){
					echo "<td>Comment\r";
				}
				if (isset($_GET['6'])){
					echo "<td>Email\r";
				}
				if (isset($_GET['7'])){
					echo "<td>Version\r";
				}
				if (isset($_GET['8'])){
					echo "<td>Share\r";
				}
				if (isset($_GET['9'])){
					echo "<td>Mode\r";
				}
				if (isset($_GET['10'])){
					echo "<td>Connection\r";
				}
			?>
        </tr>
        <tr>
            <?php
            $linkme = $_SERVER['REQUEST_URI'];
            $needle = $_GET['searchstring'];
            $linkme = strstr($linkme,$needle);
            $linkme = strstr($linkme,'&');
			if (isset($_GET['0'])){
            echo "<td valign=top>";
                foreach($results[0] as $value){
					echo "$value<br>";
			  	}
			}
			if (isset($_GET['1'])){
            echo "<td valign=top>";
                foreach($results[1] as $value){
					echo "$value<br>";
			  	}
			}
			if (isset($_GET['2'])){
            echo "<td valign=top>";
                foreach($results[2] as $value){
					echo "$value<br>";
			  	}
			}
			if (isset($_GET['3'])){
            echo "<td valign=top>";
                foreach($results[3] as $value){
					echo "<a href=\"?location=3&searchstring=$value$linkme\">$value</a><br>";
			  	}
			}
			if (isset($_GET['4'])){
            echo "<td valign=top>";
                foreach($results[4] as $value){
					echo "<a href=\"?location=4&searchstring=$value$linkme\">$value</a><br>";
			  	}
			}
			if (isset($_GET['5'])){
            echo "<td valign=top>";
                foreach($results[5] as $value){
					echo "$value<br>";
			  	}
			}
			if (isset($_GET['6'])){
            echo "<td valign=top>";
                foreach($results[6] as $value){
					echo "$value<br>";
			  	}
			}
			if (isset($_GET['7'])){
            echo "<td valign=top>";
                foreach($results[7] as $value){
					echo "$value<br>";
			  	}
			}
			if (isset($_GET['8'])){
            echo "<td valign=top>";
                foreach($results[8] as $value){
					echo "$value<br>";
			  	}
			}
			if (isset($_GET['9'])){
            echo "<td valign=top>";
                foreach($results[9] as $value){
					echo "$value<br>";
			  	}
			}
			if (isset($_GET['10'])){
            echo "<td valign=top>";
                foreach($results[10] as $value){
					echo "$value<br>";
			  	}
			}
		?>
			</tr>
			</table>
	<?php
}


function max_share($data){
  	static $maxshare = 0;
	static $maxusers = 0;
	static $currentshare = 0;
	static $currentusers = 0;
	$share = explode(" ",$data[8]);
	switch ($share[1]){
		case "KiBs":
		    $nshare = $share[0];
		    break;
		case "MiBs":
		    $nshare = $share[0]*1000;
		    break;
		case "GiBs":
		    $nshare = $share[0]*10000;
		    break;
		case "TiBs":
		    $nshare = $share[0]*100000;
		    break;
 	}

	echo "$share[0]$share[1] = $nshare KiBs<br>";
	if ($data[0] == "C"){
		$currentshare = $currentshare + $nshare;
	}

	if ($data[0] == "D"){
		$currentshare = $currentshare - $nshare;
	}

	if ($currentshare > $maxshare){
		$maxshare = $currentshare;
		$show = $maxshare/1000000;
		echo "Maxshare: $show TB<br>";
	}

}
function load_file($file){
   global $debug, $dbx;
	$line = file($file);
    for($i=0; $i<count($line); $i++){
    	$data = split(",",$line[$i]);
    	if ($debug){
			//echo "<tt>Beginning search()...</tt><br>";
 		}
    	search($data);

		if ($debug){
			//echo "<pre>"; print_r($results); echo "</pre>";
 		}
		//max_share($data);

    }
    if ($debug){
		echo "<tt>Beginning parse_data()...</tt><br>";
 	}

}
function search($data){
 global $debug, $dbx;
  $index = $_GET['location'];
	if ($data[$index] == $_GET['searchstring']){
        if ($debug){
			echo "<tt>$data[0]\t$data[1]\t$data[2]\t$data[3]\t$data[4]\t$data[5]\t$data[6]\t$data[7]\t$data[8]\t$data[9]\t$data[10]\t</tt><br>";
		}
		if (isset($_GET['raw'])){
            echo "<tt>$data[0]\t$data[1]\t$data[2]\t$data[3]\t$data[4]\t$data[5]\t$data[6]\t$data[7]\t$data[8]\t$data[9]\t$data[10]\t</tt><br>";
  		}
		$dbx[] = $data;

	}
}
function parse_data(){
  global $dbx, $debug;
 $result = array_fill(0,11,array());
$total = count($dbx);
echo "Found <b>$total</b> entries.";
    if ($debug){
		echo "<b>$total</b>";
		//$dbx[row entry (0,1,2,3,4)][current column(c/d,date,ip,nick)]
	}

	for ($j=0; $j<=10; $j++){

		for ($i=0; $i<$total; $i++){
            if ($debug){
		  		echo "<tt>($i,$j)</tt><br>";
			}
			$needle = $dbx[$i][$j];
			$haystack = $result[$j];

			if  ($debug){
				echo "<tt>Searching for {$dbx[$i][$j]} in result($j) ($i,$j)</tt><br>";
	  		}
			if (!in_array($needle,$haystack)){
                if ($debug){
				  echo "<tt>{$dbx[$i][$j]} Was not found.</tt><br>";
				  $use = count($result[$j]);
				  $use++;
			  	}
				$result[$j][] = $dbx[$i][$j];
				if ($debug){
					echo "<pre>"; print_r($result); echo "</pre>";
				}
	  		}
	  		else{
                if ($debug){
					echo "<tt>{$dbx[$i][$j]} FOUND, skipping<br>";
					break;
				}
			}

	 	}

	}
	return $result;
}

?>

Any comments, questions, thoughts, or additions to code (Only if you're a adding code not requesting it) are welcome!
ENJOY!!!!

--quicky2g

[coreno]

Hm, that code looks familiar

[quicky2g]

maybe cause you wrote it way back when lolz

[quicky2g]

ok so I did a major update to this system here...

The problem with the previous php script was that every time a search of the logs was done, the script searches through every single log file which can take a long time. There was also no ability to do searches with parts of nicknames or parts of IP subnets such as every IP address in the 37.213.112.x IP range.

I modified a script found online at http://www.bin-co.com/php/scripts/csv_import_export/ to import the Grimoire connection logs that are in a csv (Comma Separated Values) format to a database.

This script combined with a couple of other things I wrote and found will do the following...

• Import the newest Grimoire connection logs into a database each hour (Explained more later)
• Log the imports to the database for later viewing
• Provide extensive quick searching since the DB indexes the connection log info and DB's can be searched with many advanced tools

I used MySQL for the Database and Apache for Windows as the web server. These scripts are written for use with MySQL, but should be compatible with other DB software as long as the DB connection stuff is modified. IIS can be used as the web server as long as there is php support. Check out this link for help: http://www.iisadmin.co.uk/?p=4. The commands at the end of the tutorial didn't work for me and I had to do the required steps through the GUI IIS interface.

The way this works is by importing today's csv log into the database every hour. At each hour, a php script (importexporthourly.php) will delete the entire current day's contents in the database and then import all the values from the csv file. This makes it so that none of the values in the database are duplicates and the database will also have a fairly recent copy of the connection log info......clear it out and then repopulate it.

A different PHP script (importexportendofday.php) at 12:01am will delete all values in the database from the previous day and then import the contents of the csv file from the previous day. At 12:01am the database only has information up until 11:00pm of the previous day. The entries from the previous day need to be deleted and then the closed log from the previous day can imported.

You can place the scripts in any folder in your web server but you will probably want to put all the files in the same directory except for the batch files (Mentioned later).

To import all the logs when you first start to use this, use the following script (importexportall.php):

<?php
/*
The following few lines restricts this script to only run on the server it is located on
If any other computer tries to access this script, their browser will be redirected to http://www.google.com
*/
$test = "^127.0";
$ip = $_SERVER['REMOTE_ADDR'];
if (!eregi($test,$ip)){
  header( 'Location: http://www.google.com' );
  echo "Please wait...";
  exit(0);
}
//The mysql.php file includes DB connection parameters to the database
include("mysql.php");
$mysql = new mysql;
$timestamp = strtotime("today");
$today = date('Y-m-d',$timestamp);

$logfolder = "C:/0.4.1.1/scripts/Rincewind's Grimoire/Settings/Security/UserLog/Logs";
$files = scandir($logfolder);
for($i=0; $i<count($files); $i++){
	if(is_file($files[$i]) && !array_search($files[$i],$forbidden)){
		if ($debug){
			echo "<tt>Loading $files[$i]...</tt><br>";
	  	}
	load_file($files[$i]);
}
//if statement gets rid of the files "." and ".." from being imported into the DB since those files dont exist
//Change the filename "UserLog-2008-11-21.csv" to whatever today's current file is so that you don't import today's log
if($files[$i]=="."||$files[$i]==".."||$files[$i]=="UserLog-$today.csv"){
	continue;
}
$longfile = "C:/0.4.1.1/scripts/Rincewind's Grimoire/Settings/Security/UserLog/Logs/$files[$i]";
//echo "Filename: $files[$i]<br>"; //This will print the short filenames of what you are about to import
//print_r($longfile); //This will print the long filenames of what you are about to import

//CSVImport does the importing...uncomment the line to do the import
//CSVImport("connectionlogs", array('status','date','time','nickname','ip','description','email','version','sharesize','activepassive','speed'), $longfile, $files[i]);
	}

function CSVExport($query) {
    $sql_csv = mysql_query($query) or die("Error: " . mysql_error());    
    header("Content-type:text/octect-stream");
    header("Content-Disposition:attachment;filename=data.csv");
    while($row = mysql_fetch_row($sql_csv)) {
        print '"' . stripslashes(implode('","',$row)) . "\"\n";
    }
    exit;
}

//Import the contents of a CSV file after uploading it
//http://www.bin-co.com/php/scripts/csv_import_export/
/*
Arguments:
$table - The name of the table the data must be imported to
$fields - An array of fields that will be used
$file - The csv file being imported (The long name used in the import)
$filename - The csv file being imported (The short name used in logging feature)
*/
function CSVImport($table, $fields, $file, $filename) {
	global $mysql;
	$handle = fopen($file,'r');
	if(!$handle) die('Cannot open uploaded file.');

	$row_count = 0;
	$sql_query = "INSERT INTO $table(". implode(',',$fields) .") VALUES(";

    $rows = array();

    //Read the file as csv
    while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
        $row_count++;
        foreach($data as $key=>$value) {
            $data[$key] = "'" . addslashes($value) . "'";
        }
        $rows[] = implode(",",$data);
    }
    $sql_query .= implode("),(", $rows);
    $sql_query .= ")";
    fclose($handle);
	$mysql->connect();
	
	/*
	Next 3 lines are variables added by quicky2g for the logging feature
	Comment them out if you do not want to use the logging feature
	*/
	$thedate = date("m/d/y",time());
	$thetime = date("g:i:sA",time());
	$myFile = "C:/Program Files/Apache Software Foundation/Apache2.2/htdocs/hub/importexportlog/log.htm";

    if(count($rows)) { //If some records  were found
        mysql_query($sql_query) or die("MySQL Error: " . mysql_error()); // and insert the new ones.

        print 'Successfully imported '.$row_count.' record(s)';

		//quicky2g added following 4 lines for logging of script
		$fh = fopen($myFile, 'a') or die("Can't open log file");
		$stringData = "<tr><td>$thedate</td><td>$thetime</td><td>importexportall.php</td><td>success</td><td>$filename</td><td>$row_count</td><td>$table</td>\n";
		fwrite($fh, $stringData);
		fclose($fh);

    } else {
        print 'Cannot import data - no records found.';
	
		//quicky2g added following 4 lines for logging of script
		$fh = fopen($myFile, 'a') or die("Can't open log file");
		$stringData = "<tr><td>$thedate</td><td>$thetime</td><td>importexportall.php</td><td>failure</td><td>$filename</td><td>0</td><td>$table</td>\n";
		fwrite($fh, $stringData);
		fclose($fh);
    }
}

You will need the file mysql.php which contains:

<?php
//mysql.php
class mysql{
	function connect(){
		$this->username = "somedbuser";
		$this->password = "somedbpass";
		$this->database = "somedbname";
		
		mysql_connect('localhost',$this->username,$this->password);
		if (!@mysql_select_db($this->database)){
			echo "<b><font color=red>Error:</b></font> Could not select the database. <br><b>" . mysql_error() . "</b>.<p>";
		}
		
	}

	function query($query){
		if ($result = @mysql_query($query)){
          //echo "<br>$query";
			return $result;
		}
		else {
			//echo "<b><font color=red>Error:</b></font> An error occured while trying to contact the database. This page might not appear as intended. If you were submitting data, it did not get saved.";
            echo "<p><b><font color=red>Error:</b></font> Could not query the database. <br><b>" . mysql_error() . "</b>.<br><p>";
			echo "<br>$query";
  		}
 	}
 	
	function disconnect(){
		if(!@mysql_close()){
			//echo "<b>Error: Attemping to close an unopened session. Contact webmaster.";
  		}
 	}
}
?>

Modify the mysql.php file to your needs (username, password and database names).

In the importexportall.php file, you will probably want to modify several values before you run the script. Modify the $logfolder variable for wherever your log files are located. There are a few echo statements at the beginning of the script that you might want to run for testing to make sure that the script is accessing all the correct files to be imported. After you are sure that the script will import the files you want to be imported, uncomment the line that begins with "CSVImport".

Before running the script you will need to create a table named "connectionlogs" with these fields

'id','status','date','time','nickname','ip','description','email','version','sharesize','activepassive','speed'

The "id" field should be an auto-numbered field. The description field should be a text field. All other fields can be standard VARCHAR.

Towards the bottom of the script there are some logging features that will log if imports to the database are successful. The php script will just append a row to a table in the log.htm file. Change the variable $myFile in the script to whatever you need. The log.htm file will need to have this as the base file:

<head>
<style type="text/css">
table {
	border-width: 3px 3px 3px 3px;
	border-spacing: 4px;
	border-style: solid solid solid solid;
	border-color: black black black black;
	border-collapse: separate;
	background-color: white;
	-moz-border-radius: 6px 6px 6px 6px;
}
table th {
	font-size: 14px;
	border-width: 2px 2px 2px 2px;
	padding: 2px 2px 2px 2px;
	border-style: solid solid solid solid;
	border-color: green green green green;
	background-color: white;
	-moz-border-radius: 6px 6px 6px 6px;
}
table td {
	font-size: 12px;
	text-align: center;
	border-width: 1px 1px 1px 1px;
	padding: 1px 1px 1px 1px;
	border-style: solid solid solid solid;
	border-color: gray gray gray gray;
	background-color: white;
	-moz-border-radius: 6px 6px 6px 6px;
}
table td.endofday {
	background-color: green;
	color: white;
	border-color: black;
}
table td.failure {
	background-color: red;
	color: white;
	border-color: black;
}
</style>
<title>Import Export Log</title>
</head>

<table border="2" style="width: 1000px">
<tr><th>Date (MM/DD/YYYY)</th><th>Time (HH:MM:SS)</th><th>File</th><th>Status</th><th>Filename Imported Into DB</th><th>Records Imported</th><th>Table Name</th>

Change all the php scripts according to where your log file is located.

This is what one of my logs looks like:



Use this script to import records hourly (importexporthourly.php):

<?php
/*
The following few lines restricts this script to only run on the server it is located on
If any other computer tries to access this script, their browser will be redirected to http://www.google.com
*/
$test = "^127.0";
$ip = $_SERVER['REMOTE_ADDR'];
if (!eregi($test,$ip)){
  header( 'Location: http://www.google.com' );
  echo "Please wait...";
  exit(0);
}
//The mysql.php file includes DB connection parameters to the database
include("mysql.php");
$mysql = new mysql;
$timestamp = strtotime("today");
$today = date('Y-m-d',$timestamp);
$today2 = date('d/m/Y',$timestamp);
$thefile = "Userlog-$today.csv";
$thetable = "connectionlogs";
$filename = "C:/0.4.1.1/scripts/Rincewind's Grimoire/Settings/Security/UserLog/Logs/$thefile";

$mysql->connect();
$sql_delete_prev = "DELETE FROM $thetable WHERE DATE = '$today2'";
mysql_query($sql_delete_prev) or die("MySQL Error: " . mysql_error());
//echo "The SQL Delete Statement is: $sql_delete_prev<br>";
CSVImport("$thetable", array('status','date','time','nickname','ip','description','email','version','sharesize','activepassive','speed'), $filename, $thefile);
function CSVExport($query) {
    $sql_csv = mysql_query($query) or die("Error: " . mysql_error()); //Replace this line with what is appropriate for your DB abstraction layer
    
    header("Content-type:text/octect-stream");
    header("Content-Disposition:attachment;filename=data.csv");
    while($row = mysql_fetch_row($sql_csv)) {
        print '"' . stripslashes(implode('","',$row)) . "\"\n";
    }
    exit;
}

//Import the contents of a CSV file after uploading it
//http://www.bin-co.com/php/scripts/csv_import_export/
/*
Arguments:
$table - The name of the table the data must be imported to
$fields - An array of fields that will be used
$file - The csv file being imported (The long name used in the import)
$filename - The csv file being imported (The short name used in logging feature)
*/
function CSVImport($table, $fields, $file, $filename) {
	global $mysql;
    $handle = fopen($file,'r');
    if(!$handle) die('Cannot open uploaded file.');

    $row_count = 0;
    $sql_query = "INSERT INTO $table(". implode(',',$fields) .") VALUES(";

    $rows = array();

    //Read the file as csv
    while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
        $row_count++;
        foreach($data as $key=>$value) {
            $data[$key] = "'" . addslashes($value) . "'";
        }
        $rows[] = implode(",",$data);
    }
    $sql_query .= implode("),(", $rows);
    $sql_query .= ")";
    fclose($handle);
	$mysql->connect();

	//Next 3 lines are variables added by quicky2g for the logging feature
	$thedate = date("m/d/y",time());
	$thetime = date("g:i:sA",time());
	$myFile = "C:/Program Files/Apache Software Foundation/Apache2.2/htdocs/hub/importexportlog/log.htm";

    if(count($rows)) { //If some records were found,     
		//echo "<br>QUERY: $sql_query<br>";
        mysql_query($sql_query) or die("MySQL Error: " . mysql_error()); // and insert the new ones.
		//echo "The SQL Insert Statement is: $sql_query<br>";
        print 'Successfully imported '.$row_count.' record(s)';

		//quicky2g added following 4 lines for logging of script
		$fh = fopen($myFile, 'a') or die("Can't open log file");
		$stringData = "<tr><td>$thedate</td><td>$thetime</td><td>importexporthourly.php</td><td>success</td><td>$filename</td><td>$row_count</td><td>$table</td>\n";
		fwrite($fh, $stringData);
		fclose($fh);
    } else {
		//echo "The SQL Insert Statement is: $sql_query<br>";
        print 'Cannot import data - no records found.';
	
		//quicky2g added following 4 lines for logging of script
		$fh = fopen($myFile, 'a') or die("Can't open log file");
		$stringData = "<tr><td class='failure'>$thedate</td><td class='failure'>$thetime</td><td class='failure'>importexporthourly.php</td><td class='failure'>failure</td><td class='failure'>$filename</td><td class='failure'>0</td><td class='failure'>$table</td>\n";
		fwrite($fh, $stringData);
		fclose($fh);
    }
}

Make sure you modify the file path in the variable $filename as well as the path to the log.htm file in the $myFile variable. I scheduled this php script to run every hour through Internet Explorer 7 at 12:01am, 1:01am, 2:01am, etc through Windows task scheduler with the following batch file:

@start "" /b "C:\Program Files\Internet Explorer\iexplore.exe" http://127.0.0.1/importexporthourly.php
exit

In this case the importexporthourly.php file is located in my root web directory. IE7 is then closed at the second minute after each hour with this batch file:

taskkill /im iexplore.exe
exit

If you want to run the script in Firefox, use this batch file:

cd "C:\Program Files\Mozilla Firefox"
firefox.exe http://127.0.0.1/importexporthourly.php
exit

taskkill /im firefox.exe
exit

Use this script to import records at the end of the day (at 12:01am or 12:02am depending on how you want to schedule it) (importexportendofday.php):

<?php
/*
The following few lines restricts this script to only run on the server it is located on
If any other computer tries to access this script, their browser will be redirected to http://www.google.com
*/
$test = "^127.0";
$ip = $_SERVER['REMOTE_ADDR'];
if (!eregi($test,$ip)){
  header( 'Location: http://www.google.com' );
  echo "Please wait...";
  exit(0);
}
//The mysql.php file includes DB connection parameters to the database
include("mysql.php");
$mysql = new mysql;
$timestamp = strtotime("yesterday");
$yesterday = date('d/m/Y',$timestamp);
$yesterday2 = date('Y-m-d',$timestamp);
$thefile = "Userlog-$yesterday2.csv";
$thetable = "connectionlogs";
//echo "This is the file: $thefile<br>";
$filename = "C:/0.4.1.1/scripts/Rincewind's Grimoire/Settings/Security/UserLog/Logs/$thefile";

$mysql->connect();
$sql_delete_prev = "DELETE FROM $thetable WHERE DATE = '$yesterday'";
//echo "The SQL Delete Statement is: $sql_delete_prev<br>";
mysql_query($sql_delete_prev) or die("MySQL Error: " . mysql_error());

CSVImport("$thetable", array('status','date','time','nickname','ip','description','email','version','sharesize','activepassive','speed'), $filename, $thefile);

//echo "This is sql_delete_prev: $sql_delete_prev";
function CSVExport($query) {
    $sql_csv = mysql_query($query) or die("Error: " . mysql_error()); //Replace this line with what is appropriate for your DB abstraction layer
    
    header("Content-type:text/octect-stream");
    header("Content-Disposition:attachment;filename=data.csv");
    while($row = mysql_fetch_row($sql_csv)) {
        print '"' . stripslashes(implode('","',$row)) . "\"\n";
    }
    exit;
}

//Import the contents of a CSV file after uploading it
//http://www.bin-co.com/php/scripts/csv_import_export/
/*
Arguments:
$table - The name of the table the data must be imported to
$fields - An array of fields that will be used
$file - The csv file being imported (The long name used in the import)
$filename - The csv file being imported (The short name used in logging feature)
*/
function CSVImport($table, $fields, $file, $filename) {

global $mysql;
    $handle = fopen($file,'r');
    if(!$handle) die('Cannot open uploaded file.');

    $row_count = 0;
    $sql_query = "INSERT INTO $table(". implode(',',$fields) .") VALUES(";

    $rows = array();

    //Read the file as csv
    while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
        $row_count++;
        foreach($data as $key=>$value) {
            $data[$key] = "'" . addslashes($value) . "'";
        }
        $rows[] = implode(",",$data);
    }
    $sql_query .= implode("),(", $rows);
    $sql_query .= ")";
    fclose($handle);
	$mysql->connect();

	//Next 3 lines are variables added by quicky2g for the logging feature
	$thedate = date("m/d/y",time());
	$thetime = date("g:i:sA",time());
	$myFile = "C:/Program Files/Apache Software Foundation/Apache2.2/htdocs/hub/importexportlog/log.htm";

    if(count($rows)) { //If some records were found,
        
		//echo "<br>QUERY: $sql_query<br>";
        mysql_query($sql_query) or die("MySQL Error: " . mysql_error()); // and insert the new ones.
		//echo "The SQL Insert Statement is: $sql_query<br>";
        print 'Successfully imported '.$row_count.' record(s)';

		//quicky2g added following 4 lines for logging of script
		$fh = fopen($myFile, 'a') or die("Can't open log file");
		$stringData = "<tr><td class='endofday'>$thedate</td><td  class='endofday'>$thetime</td><td  class='endofday'>importexportendofday.php</td><td  class='endofday'>success</td><td  class='endofday'>$filename</td><td  class='endofday'>$row_count</td><td  class='endofday'>$table</td>\n";
		fwrite($fh, $stringData);
		fclose($fh);

    } else {
		//echo "The SQL Insert Statement is: $sql_query<br>";
        print 'Cannot import data - no records found.';
	
		//quicky2g added following 4 lines for logging of script
		$fh = fopen($myFile, 'a') or die("Can't open log file");
		$stringData = "<tr><td  class='failure'>$thedate</td><td  class='failure'>$thetime</td><td  class='failure'>importexportendofday.php</td><td  class='failure'>failure</td><td  class='failure'>$filename</td><td  class='failure'>0</td><td  class='failure'>$table</td>\n";
		fwrite($fh, $stringData);
		fclose($fh);
    }
}

Make sure you modify the file path in the variable $filename as well as the path to the log.htm file in the $myFile variable. I scheduled the "importexportendofday.php" script to run once a day at 12:01am in IE7. Another script kills IE7 at 12:02am.

If you want to manually enter a log file into the database for whatever reason, use this script (importexportone.php):

<?php
/*
The following few lines restricts this script to only run on the server it is located on
If any other computer tries to access this script, their browser will be redirected to http://www.google.com
*/
$test = "^127.0";
$ip = $_SERVER['REMOTE_ADDR'];
if (!eregi($test,$ip)){
  header( 'Location: http://www.google.com' );
  echo "Please wait...";
  exit(0);
}
//The mysql.php file includes DB connection parameters to the database
include("mysql.php");
$mysql = new mysql;
//Manually specify the filename to be imported on the next line and then uncomment the next 2 lines
$thefile = "UserLog-2008-11-23.csv";
$filename = "C:/0.4.1.1/scripts/Rincewind's Grimoire/Settings/Security/UserLog/Logs/$thefile";
CSVImport("connectionlogs", array('status','date','time','nickname','ip','description','email','version','sharesize','activepassive','speed'), $filename, $thefile);

function CSVExport($query) {
    $sql_csv = mysql_query($query) or die("Error: " . mysql_error()); //Replace this line with what is appropriate for your DB abstraction layer
    
    header("Content-type:text/octect-stream");
    header("Content-Disposition:attachment;filename=data.csv");
    while($row = mysql_fetch_row($sql_csv)) {
        print '"' . stripslashes(implode('","',$row)) . "\"\n";
    }
    exit;
}

//Import the contents of a CSV file after uploading it
//http://www.bin-co.com/php/scripts/csv_import_export/
/*
Arguments:
$table - The name of the table the data must be imported to
$fields - An array of fields that will be used
$file - The csv file being imported (The long name used in the import)
$filename - The csv file being imported (The short name used in logging feature)
*/
function CSVImport($table, $fields, $file, $filename) {

global $mysql;
    $handle = fopen($file,'r');
    if(!$handle) die('Cannot open uploaded file.');

    $row_count = 0;
    $sql_query = "INSERT INTO $table(". implode(',',$fields) .") VALUES(";

    $rows = array();

    //Read the file as csv
    while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
        $row_count++;
        foreach($data as $key=>$value) {
            $data[$key] = "'" . addslashes($value) . "'";
        }
        $rows[] = implode(",",$data);
    }
    $sql_query .= implode("),(", $rows);
    $sql_query .= ")";
    fclose($handle);
	$mysql->connect();
	
	//Next 3 lines are variables added by quicky2g for the logging feature
	$thedate = date("m/d/y",time());
	$thetime = date("g:i:sA",time());
	$myFile = "C:/Program Files/Apache Software Foundation/Apache2.2/htdocs/hub/importexportlog/log.htm";

    if(count($rows)) { //If some recores  were found,
        
		//echo "<br>QUERY: $sql_query<br>";
        mysql_query($sql_query) or die("MySQL Error: " . mysql_error()); // and insert the new ones.

        print 'Successfully imported '.$row_count.' record(s)';

		//quicky2g added following 4 lines for logging of script
		$fh = fopen($myFile, 'a') or die("Can't open log file");
		$stringData = "<tr><td>$thedate</td><td>$thetime</td><td>importexportone.php</td><td>success</td><td>$filename</td><td>$row_count</td><td>$table</td>\n";
		fwrite($fh, $stringData);
		fclose($fh);

    } else {
        print 'Cannot import data - no records found.';

		//quicky2g added following 4 lines for logging of script
		$fh = fopen($myFile, 'a') or die("Can't open log file");
		$stringData = "<tr><td>$thedate</td><td>$thetime</td><td>importexportone.php</td><td>failure</td><td>$filename</td><td>0</td><td>$table</td>\n";
		fwrite($fh, $stringData);
		fclose($fh);
    }
}

Make sure you modify the file path in the variable $filename as well as the path to the log.htm file in the $myFile variable.

The last issue that comes up is with the importexport log.htm file. Each time the hourly import happens it is logged to the log.htm file. If this runs for several months, the log.htm file will have a huge listing when you view it in a webpage. To sort of rotate the log file each week or every couple of days (Whatever you decide), use this batch file and schedule it to run regularly:

cd "C:\Program Files\Apache Software Foundation\Apache2.2\htdocs\hub\"

For /f "tokens=2-4 delims=/ " %%a in ('date /t') do (set date=%%a-%%b-%%c)
COPY /Y log.htm Endofweeklog_%DATE%.htm
COPY /Y originallog.htm log.htm
exit

Make sure you create a file called originallog.htm with the contents of the log.htm file that was posted above. When the batch file is run, the current log.htm file will be copied to a file named Endofweeklog_11-29-2008.htm or whatever date it is. Then the originallog.htm file will overwrite the log.htm file so that the logging report starts over again. This way your webpage will only ever have a maximum of 168 entries vs. an entire month or year.

I used PHP Runner to view database entries. The wizard makes it super easy to customize exactly what you want to see in a web browser. Make sure you sort the entries from highest to lowest auto-numbered id from the database so that the page will always display the newest connection log entries even when you do searches. This was the final result I got:

The main page


Advanced Searching


Advanced Search Results


Enjoy!!!

[ATAG]

Excellent job

PS: Why don't you use cURL instead of IE/Firefox? It's available on many platforms and runs mutch faster then a gui based browser.

[Rincewind]

Nice work quicky2g.

[quicky2g]

I am using cURL now to run the scripts instead of IE7 or firefox. I used the version from here.

This offers a huge improvement over using IE7 or firefox because the GUI is command line and uses much less resources to run. There is also no need to have a second batch file that kills the browser.

For example, replace the two batch files used before to run the importexporthourly.php script with this one:

cd "C:\Program Files\Apache Software Foundation\Apache2.2\htdocs\cURL"
curl.exe http://127.0.0.1/importexporthourly.php
exit

Make sure to modify your path to wherever cURL is located and also modify your script being executed.

Thanks for the tip ATAG!!!

[quicky2g]

I've spent the last 3 months updating this system and have done some very cool things with it that I hope people will enjoy.

If you used the script I provided before you probably noticed that there was no way to do queries against the database based on share sizes or times because the script just imported the time string and the share size string right from the log without doing any type of conversion to PHP time or PHP bytes for the size. I also personally didn't like the date format in European format vs. American format.

The new script converts time into PHP seconds, sharesizes into bytes, and has the option to change the date format to the American format or leave it at the European format.

Once I had the database setup, I realized there were some very cool queries I could do against the DB. I have created an easy to use log analysis system based on PHP (Screenshots down below).

All of the files mentioned here should be placed in the same web directory to work in their default operation.

These scripts were coded and tested to work with IIS and MySQL on Windows Server 2003. Apache on Windows should work just fine but I don't know how other DB's or Linux will work because I haven't tested it. Any feedback would be much appreciated.

Note: All instructions are commented in each of the files. You only need to modify the scripts in the "User Configuration Area".

The code displays well with the comments if you open it in Notepad++ (download here:http://www.download.com/Notepad-/3000-2352_4-10327521.html?tag=mncol) or some other type of editor other than notepad or wordpad.

The info you provide about your DB and the tables in the create.php file needs to be the same info as in your mysql.php file. Fill the info about the mysql.php file and the create.php file before you run any scripts.

To first setup your DB use the following script (You only need to put connection information for your DB in this one file):

"create.php"

<?php
/*
Purpose of File:
Creates your database and tables you need to run the other scripts
*/
//Start user configuration area
/*
Modify the variable $thedb with the name of the database you will be using
If the database does not exist, the database will be created for you
If the database already exists, existing tables will be left alone
*/
$thedb="hubdb";
//Modify table variables below for whatever the table names you want in your DB
$connectionlogstable="connectionlogs";
$importlogtable="importlog";
$reglogtable="reglog";
$bydaytable="byday";
$byhourtable="byhour";
//Modify $create variables below to "yes" or "no" to create needed tables in your database
$createconnectionlogs="yes";
$createimportlog="yes";
$createreglog="yes";
$createbyday="yes";
$createbyhour="yes";
//End user configuration area
/*
The following few lines restricts this script to only run on the server it is located on
If any other computer/ip address tries to access this script, their browser will be redirected to google
*/
$test = "^127.0";
$ip = $_SERVER['REMOTE_ADDR'];
if (!eregi($test,$ip)){
  header( 'Location: http://www.google.com' );
  echo "Please wait...";
  exit(0);
}
include ("mysql.php");
$mysql = new mysql;
$mysql->connect();

$getdbnames = mysql_query("SHOW DATABASES");
$dbyes=0;
while(list($dbname) = mysql_fetch_array($getdbnames)) {
	if($dbname==$thedb){
		$dbyes=1;
	}
}

if ($dbyes==1) {
	echo "Database '$thedb' already exists<br />"; 
} else {
	echo "Database '$thedb' does not exist<br />";
	$createdb="CREATE DATABASE $thedb";
	mysql_query($createdb);
	echo "The database '$thedb' was created<br />";
}

$connectionlogs="CREATE TABLE $thedb.$connectionlogstable (
  `id` INTEGER NOT NULL AUTO_INCREMENT,
  `status` VARCHAR(45),
  `date` VARCHAR(45),
  `time` BIGINT(20),
  `nickname` VARCHAR(45),
  `ip` VARCHAR(45),
  `description` TEXT,
  `email` VARCHAR(45),
  `version` VARCHAR(45),
  `sharesize` BIGINT(15),
  `activepassive` VARCHAR(45),
  `speed` VARCHAR(45),
  PRIMARY KEY (`id`));";
$importlog="CREATE TABLE $thedb.$importlogtable (
  `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `date` VARCHAR(15),
  `time` VARCHAR(15),
  `executedfile` VARCHAR(25),
  `status` VARCHAR(7),
  `filenameimported` VARCHAR(45),
  `numrecords` INTEGER,
  `tablename` VARCHAR(20),
  PRIMARY KEY (`id`)
  );";
$reglog="CREATE TABLE $thedb.$reglogtable (
  `id` INTEGER NOT NULL AUTO_INCREMENT,
  `nickname` VARCHAR(20),
  `time` INTEGER,
  `action` VARCHAR(80),
  PRIMARY KEY (`id`)
  );";
$byday="CREATE TABLE $thedb.$bydaytable (
  `id` INTEGER NOT NULL AUTO_INCREMENT,
  `day` VARCHAR(9),
  `value` INTEGER,
  PRIMARY KEY (`id`)
  );";
$byhour="CREATE TABLE $thedb.$byhourtable (
  `id` INTEGER NOT NULL AUTO_INCREMENT,
  `hour` INTEGER,
  `value` INTEGER,
  PRIMARY KEY (`id`)
  );";

if($createconnectionlogs=="yes"){
	$connectionlogs=mysql_query($connectionlogs);
	if($connectionlogs=="1"){
		echo "Table '<b>$connectionlogstable</b>' created successfully<br />";
	} else {
		echo "Table '<b>$connectionlogstable</b>' not created<br />";
	}
} else {
	echo "Chose not to create table '<b>$connectionlogstable</b>'<br />";
}

if($createimportlog=="yes"){
	$importlog=mysql_query($importlog);
	if($importlog=="1"){
		echo "Table '<b>$importlogtable</b>' created successfully<br />";
	} else {
		echo "Table '<b>$importlogtable</b>' not created<br />";
	}
} else {
	echo "Chose not to create table '<b>$importlogtable</b>'<br />";
}

if($createreglog=="yes"){
	$reglog=mysql_query($reglog);
	if($reglog=="1"){
		echo "Table '<b>$reglogtable</b>' created successfully<br />";
	} else {
		echo "Table '<b>$reglogtable</b>' not created<br />";
	}
} else {
	echo "Chose not to create table '<b>$reglogtable</b>'<br />";
}

if($createbyday=="yes"){
	$byday=mysql_query($byday);
	$createdays="INSERT INTO $thedb.$bydaytable (day, value) values ('Sun','0');"; mysql_query($createdays);
	$createdays="INSERT INTO $thedb.$bydaytable (day, value) values ('Mon','0');"; mysql_query($createdays);
	$createdays="INSERT INTO $thedb.$bydaytable (day, value) values ('Tue','0');"; mysql_query($createdays);
	$createdays="INSERT INTO $thedb.$bydaytable (day, value) values ('Wed','0');"; mysql_query($createdays);
	$createdays="INSERT INTO $thedb.$bydaytable (day, value) values ('Thu','0');"; mysql_query($createdays);
	$createdays="INSERT INTO $thedb.$bydaytable (day, value) values ('Fri','0');"; mysql_query($createdays);
	$createdays="INSERT INTO $thedb.$bydaytable (day, value) values ('Sat','0');"; mysql_query($createdays);
	if($byday=="1"){
		echo "Table '<b>$bydaytable</b>' created successfully<br />";
	} else {
		echo "Table '<b>$bydaytable</b>' not created<br />";
	}
} else {
	echo "Chose not to create table '<b>$bydaytable</b>'<br />";
}

if($createbyhour=="yes"){
	$byhour=mysql_query($byhour);
	$createhours="INSERT INTO $thedb.$byhourtable (hour, value) values ('0','0');"; mysql_query($createhours);
	$createhours="INSERT INTO $thedb.$byhourtable (hour, value) values ('1','0');"; mysql_query($createhours);
	$createhours="INSERT INTO $thedb.$byhourtable (hour, value) values ('2','0');"; mysql_query($createhours);
	$createhours="INSERT INTO $thedb.$byhourtable (hour, value) values ('3','0');"; mysql_query($createhours);
	$createhours="INSERT INTO $thedb.$byhourtable (hour, value) values ('4','0');"; mysql_query($createhours);
	$createhours="INSERT INTO $thedb.$byhourtable (hour, value) values ('5','0');"; mysql_query($createhours);
	$createhours="INSERT INTO $thedb.$byhourtable (hour, value) values ('6','0');"; mysql_query($createhours);
	$createhours="INSERT INTO $thedb.$byhourtable (hour, value) values ('7','0');"; mysql_query($createhours);
	$createhours="INSERT INTO $thedb.$byhourtable (hour, value) values ('8','0');"; mysql_query($createhours);
	$createhours="INSERT INTO $thedb.$byhourtable (hour, value) values ('9','0');"; mysql_query($createhours);
	$createhours="INSERT INTO $thedb.$byhourtable (hour, value) values ('10','0');"; mysql_query($createhours);
	$createhours="INSERT INTO $thedb.$byhourtable (hour, value) values ('11','0');"; mysql_query($createhours);
	$createhours="INSERT INTO $thedb.$byhourtable (hour, value) values ('12','0');"; mysql_query($createhours);
	$createhours="INSERT INTO $thedb.$byhourtable (hour, value) values ('13','0');"; mysql_query($createhours);
	$createhours="INSERT INTO $thedb.$byhourtable (hour, value) values ('14','0');"; mysql_query($createhours);
	$createhours="INSERT INTO $thedb.$byhourtable (hour, value) values ('15','0');"; mysql_query($createhours);
	$createhours="INSERT INTO $thedb.$byhourtable (hour, value) values ('16','0');"; mysql_query($createhours);
	$createhours="INSERT INTO $thedb.$byhourtable (hour, value) values ('17','0');"; mysql_query($createhours);
	$createhours="INSERT INTO $thedb.$byhourtable (hour, value) values ('18','0');"; mysql_query($createhours);
	$createhours="INSERT INTO $thedb.$byhourtable (hour, value) values ('19','0');"; mysql_query($createhours);
	$createhours="INSERT INTO $thedb.$byhourtable (hour, value) values ('20','0');"; mysql_query($createhours);
	$createhours="INSERT INTO $thedb.$byhourtable (hour, value) values ('21','0');"; mysql_query($createhours);
	$createhours="INSERT INTO $thedb.$byhourtable (hour, value) values ('22','0');"; mysql_query($createhours);
	$createhours="INSERT INTO $thedb.$byhourtable (hour, value) values ('23','0');"; mysql_query($createhours);
	if($byhour=="1"){
		echo "Table '<b>$byhourtable</b>' created successfully<br />";
	} else {
		echo "Table '<b>$byhourtable</b>' not created<br />";
	}
} else {
	echo "Chose not to create table '<b>$byhourtable</b>'<br />";
}
$mysql->disconnect();
?>

"mysql.php"

<?php
/*
Purpose of File:
Contains connection info about your DB and all the tables in the DB
*/
/*
Notes on modifying this file:

1 - All of the "get" functions are used by other scripts as a reference to what tables are used
	Modify the table names (For example the $importlog variable)
	according to what you put in the create.php file
2 - The getGrimoireLogFolder function needs to specify the path to your Grimoire Logs
	DO NOT PUT A SLASH AT THE END OF THE FOLDER PATH...other scripts take care of it for you
2 - Modify the $server variable to whatever the hostname or IP address is of your DB
	that will be accessible by this script
3 - Modify the $username and $password variables for access to your DB
4 - Modify the $database variable for the name of your DB
	This is the same DB name that you used in the create.php file

*/
class mysql{
	function getConnectionLogs(){
		$connectionlogs="connectionlogs";
		return $connectionlogs;
	}
	function getRegLogs(){
		$reglog="reglog";
		return $reglog;
	}
	function getImportLogs(){
		$importlog="importlog";
		return $importlog;
	}
	function getByDay(){
		$byday="byday";
		return $byday;
	}
	function getByHour(){
		$byhour="byhour";
		return $byhour;
	}
	function getGrimoireLogFolder(){
		$logfolder="C:/0.4.1.1/scripts/Rincewind's Grimoire/Settings/Security/UserLog/Logs";
		return $logfolder;
	}
	function getRegLogFolder(){
		$logfolder = "C:/0.4.1.1/scripts/Rincewind's Grimoire/Settings/Security/Registration/Logs";
		return $logfolder;
	}
	function connect(){
		$server = "localhost";
		$username = "root";
		$password = "password";
		$database="hubdb";
		$connection = mysql_connect($server,$username,$password);
		if (!@mysql_select_db($database)){
			echo "<br />Error: Could not connect to MySQL Database. The error is " . mysql_error() . "<br />";
		}
	return $database;
	}
	function disconnect(){
		mysql_close($connection);
		if(!@mysql_close()){
			echo "<br />Error: Could not close MySQL Connection<br />";
		}
 	}
}
?>

Once you're DB is created and your mysql.php file contains all the connection information, your are ready to import your Grimoire Logs.

This script imports all your logs the first time...so you only need to run this once. I noticed for some reason my server will only import a certain amount of log files at a time and then stops. When the script runs it outputs in your web browser which files were imported to the DB. Move those files out of the log folder directory and run the script again. Make sure you set the $wipeout variable to "no" after you run the script the first time.


"importall.php"

<?php
/*
Purpose of File:
Imports all grimoire logs into the connectionlogs table
*/
//Start user configuration area
/*
Modify the $dateformat to "european" if you want the date to display in the format
12/04/2008 for April 12th, 2008 or keep the value American for the date to display
in the format 04/12/2008 for April 12th, 2008
*/
$dateformat="american";
/*
Modify the $debug variable if you want to print out the files that will be imported
The import to the database will no be executed
*/
$debug="no";
/*
Modify the $wipeout variable to "no" if you do not want to erase your current connectionlogs DB
Otherwise your current connectionlogs DB will be erased
*/
$wipeout="yes";
//End user configuration area
/*
The following few lines restricts this script to only run on the server it is located on
If any other computer/ip address tries to access this script, their browser will be redirected to google
*/
$test = "^127.0";
$ip = $_SERVER['REMOTE_ADDR'];
if (!eregi($test,$ip)){
  header( 'Location: http://www.google.com' );
  echo "Please wait...";
  exit(0);
}
include ("mysql.php");
$mysql = new mysql;
$thedb=$mysql->connect();
$connectionlogstable=$mysql->getConnectionLogs();
$importlogtable=$mysql->getImportLogs();
$logfolder=$mysql->getGrimoireLogFolder();
if($wipeout=="yes"){
	$sql_delete_prev = "DELETE FROM $thedb.$connectionlogstable;";
	mysql_query($sql_delete_prev) or die("MySQL Error: " . mysql_error());
	echo "<b>DB Status:</b> Previous DB $thedb.$connectionlogstable successfully erased<br />";
} else {
	echo "<b>DB Status:</b> Previous DB $thedb.$connectionlogstable not erased<br />";
}
$files = scandir($logfolder);
if($debug=="yes"){
	echo "<b>These files will be imported:</b><br />";
} else {
	echo "<b>These files were imported to $thedb.$connectionlogstable:</b><br />";
}
$total=0;
for($i=0; $i<(count($files)-1); $i++){
	if (!($debug=="yes")){
		if(is_file($files[$i])){
			load_file($files[$i]);
		}
	}
	if(strstr($files[$i],'UserLog')){
		$temp=substr($files[$i],8);
		list($tempdate, $tempextension) = explode(".", $temp);
		list($yeartoday, $monthtoday, $daytoday) = explode("-", $tempdate);
	} else {
		$temp=substr($files[$i],14);
		list($tempdate, $tempextension) = explode(".", $temp);
		list($yeartoday, $monthtoday, $daytoday) = explode("-", $tempdate);
	}	
	$timestamp = strtotime("today");
	$today = date('Y-m-d',$timestamp);
	if($files[$i]=="."||$files[$i]==".."||$files[$i]=="UserLog-$today.csv"||$files[$i]=="ConnectionLog-$today.csv"){
		continue;
	} else {
		$longfile="$logfolder/" . "$files[$i]";
		if (!($debug=="yes")){
			CSVImport("$thedb.$connectionlogstable", array('status','date','time','nickname','ip','description','email','version','sharesize','activepassive','speed'), $longfile, $files[$i], $monthtoday, $daytoday, $yeartoday, $importlogtable, $dateformat, &$total);
		} else {
			echo $files[$i] . "<br />";
		}
	}
}
function CSVImport($table, $fields, $file, $filename, $monthtoday, $daytoday, $yeartoday, $importlogtable, $dateformat, &$total) {
	global $mysql;
	$handle = fopen($file,'r');
	if(!$handle) die('Cannot open uploaded file.');
	$row_count = 0;
	$sql_query = "INSERT INTO $table(". implode(',',$fields) .") VALUES(";
    $rows = array();
    while (($data = fgetcsv($handle, 5000, ",")) !== FALSE) {
        $counter=1;
		$row_count++;
        foreach($data as $key=>$value) {
			if ($counter==2){
				if($dateformat=="american"){
					list($day, $month, $year) = explode("/", $value);
					$value=$month."/".$day."/".$year;
				}
			}
			if($counter==3){
				list($hour, $minutes, $seconds) = explode(":", $value);
				$value=mktime($hour,$minutes,$seconds,$monthtoday,$daytoday,$yeartoday);
			}
			if($counter==9){
				list($sizevalue, $sizeextension) = explode(" ", $value);
				if($sizeextension=="KiBs"){
					$value=round(($sizevalue*1024),0);
				}
				if($sizeextension=="MiBs"){
					$value=round(($sizevalue*1048576),0);
				}
				if($sizeextension=="GiBs"){
					$value=round(($sizevalue*1073741824),0);
				}
				if($sizeextension=="TiBs"){
					$value=round(($sizevalue*1099511627776),0);
				}
			}
			$counter+=1;
            $data[$key] = "'" . addslashes($value) . "'";
        }
        $rows[] = implode(",",$data);
    }
    $sql_query .= implode("),(", $rows);
    $sql_query .= ")";
    fclose($handle);
	
	$thedate = date("m/d/Y",time());
	$thetime = date("g:i:sa",time());
    if(count($rows)) {
        mysql_query($sql_query);
        echo "Successfully imported $row_count record(s) from $filename<br />";
		$sql_log = "INSERT INTO $thedb.$importlogtable (date,time,executedfile,status,filenameimported,numrecords,tablename) VALUES('$thedate','$thetime','importall.php','success','$filename','$row_count','$table');";
		mysql_query($sql_log);
    } else {
        echo "Cannot import data - no records found for $filename<br />";
		$sql_log = "INSERT INTO $thedb.$importlogtable (date,time,executedfile,status,filenameimported,numrecords,tablename) VALUES('$thedate','$thetime','importall.php','failure','$filename','0','$table');";
		mysql_query($sql_log);
    }
	$total+=$row_count;
}
echo "Total records imported: $total";
$mysql->disconnect();
?>

To keep your DB up to date, schedule the importhourly.php file to run each hour of the day (Except between 12am and 1am...read the script notes about why).

You can schedule files to run with cURL and a batch file as noted in the last post (This is still what I am using).

"importhourly.php"

<?php
/*
Purpose of File:
-Wipes out your connectionlogs for today and then reimports them
-Schedule this to run hourly to keep your connectionlogs db up to date with the log files
-This script should be scheduled to run before the byhour.php script each hour
-DO NOT SCHEDULE THIS SCRIPT TO RUN AT 12AM...the importendofday.php script
 should run sometime between 12am and 1am...this script can run sometime after 1am
 and each hour after that
*/
//Start user configuration area
/*
Modify the $old variable to "yes" if you are using an older version of Grimoire
that logs with the file format "ConnectionLog-2008-10-07.csv"
instead of the new format that logs in the format "UserLog-2008-10-07.csv"
*/
$old="no";
/*
Modify the $dateformat to "european" if you want the date to display in the format
12/04/2008 for April 12th, 2008 or keep the value American for the date to display
in the format 04/12/2008 for April 12th, 2008
*/
$dateformat="american";
//End user configuration area part

/*
The following few lines restricts this script to only run on the server it is located on
If any other computer/ip address tries to access this script, their browser will be redirected to google
*/
$test = "^127.0";
$ip = $_SERVER['REMOTE_ADDR'];
if (!eregi($test,$ip)){
  header( 'Location: http://www.google.com' );
  echo "Please wait...";
  exit(0);
}
include ("mysql.php");
$mysql = new mysql;
$thedb=$mysql->connect();
$connectionlogstable=$mysql->getConnectionLogs();
$importlogtable=$mysql->getImportLogs();
$logfolder=$mysql->getGrimoireLogFolder();
$todaydate=time();
$todaymonth=date('m',$todaydate);
$todayday=date('d',$todaydate);
$todayyear=date('Y',$todaydate);
$todaymorningvalue=mktime(0,0,0,$todaymonth,$todayday,$todayyear);
$timestamp = strtotime("today");
$today = date('Y-m-d',$timestamp);
if($old=="no"){
	$thefile = "Userlog-$today.csv";
} else {
	$thefile = "ConnectionLog-$today.csv";
}
$filename = "$logfolder/" . "$thefile";
$sql_delete_prev = "DELETE FROM $thedb.$connectionlogstable WHERE time > '$todaymorningvalue'";
mysql_query($sql_delete_prev);

CSVImport("$thedb.$connectionlogstable", array('status','date','time','nickname','ip','description','email','version','sharesize','activepassive','speed'), $filename, $thefile, $importlogtable, $dateformat);

function CSVImport($table, $fields, $file, $filename, $importlogtable, $dateformat) {
	global $mysql;
    $handle = fopen($file,'r');
    if(!$handle) die('Cannot open uploaded file.');

    $row_count = 0;
    $sql_query = "INSERT INTO $table(". implode(',',$fields) .") VALUES(";
	$rows = array();
    while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
		$counter=1;
        $row_count++;
        foreach($data as $key=>$value) {
			if ($counter==2){
				if($dateformat=="american"){
					list($day, $month, $year) = explode("/", $value);
					$value=$month."/".$day."/".$year;
				}
			}
			if($counter==3){
				$datenow=time();
				$monthtoday=date('m',$datenow);
				$daytoday=date('d',$datenow);
				$yeartoday=date('Y',$datenow);
				list($hour, $minutes, $seconds) = explode(":", $value);
				$value=mktime($hour,$minutes,$seconds,$monthtoday,$daytoday,$yeartoday);
			}
			if($counter==9){
				list($sizevalue, $sizeextension) = explode(" ", $value);
				if($sizeextension=="KiBs"){
					$value=round(($sizevalue*1024),0);
				}
				if($sizeextension=="MiBs"){
					$value=round(($sizevalue*1048576),0);
				}
				if($sizeextension=="GiBs"){
					$value=round(($sizevalue*1073741824),0);
				}
				if($sizeextension=="TiBs"){
					$value=round(($sizevalue*1099511627776),0);
				}
			}
			$counter+=1;
			$data[$key] = "'" . addslashes($value) . "'";
        }
        $rows[] = implode(",",$data);
    }
    $sql_query .= implode("),(", $rows);
    $sql_query .= ")";
    fclose($handle);
	$thedate = date("m/d/Y",time());
	$thetime = date("g:i:sa",time());
    if(count($rows)) {
		mysql_query($sql_query);
		print 'Successfully imported '.$row_count.' record(s)';
		$sql_log = "INSERT INTO $thedb.$importlogtable (date,time,executedfile,status,filenameimported,numrecords,tablename) VALUES('$thedate','$thetime','importhourly.php','success','$filename','$row_count','$table');";
		mysql_query($sql_log);
    } else {
		print 'Cannot import data - no records found.';
		$sql_log = "INSERT INTO $thedb.$importlogtable (date,time,executedfile,status,filenameimported,numrecords,tablename) VALUES('$thedate','$thetime','importhourly.php','failure','$filename','0','$table');";
		mysql_query($sql_log);
    }
}
$mysql->disconnect();
?>

As mentioned in the notes about the importhourly.php script, you will also need to schedule a script that runs at the beginning of each day to import the previous days log file. Use this script:

"importendofday"

<?php
/*
Purpose of File:
-Imports the previous days Grimoire log
Note: Your DB will only have information up until 11pm by using the importhourly.php file
	  This script will wipe out the previous days info in the database and then re-import
	  the closed log file with the info from the entire day (Including 11pm-12am).
-This script should be scheduled to run anytime after 12:00am each day (Run only once each day)
-This script should be scheduled to run before the byday.php or byhour.php files are scheduled
*/
//Start user configuration area
/*
Modify the $old variable to "yes" if you are using an older version of Grimoire
that logs with the file format "ConnectionLog-2008-10-07.csv"
instead of the new format that logs in the format "UserLog-2008-10-07.csv"
*/
$old="no";
/*
Modify the $dateformat to "european" if you want the date to display in the format
12/04/2008 for April 12th, 2008 or keep the value American for the date to display
in the format 04/12/2008 for April 12th, 2008
*/
$dateformat="american";
//End user configuration area part

/*
The following few lines restricts this script to only run on the server it is located on
If any other computer/ip address tries to access this script, their browser will be redirected to google
*/
$test = "^127.0";
$ip = $_SERVER['REMOTE_ADDR'];
if (!eregi($test,$ip)){
  header( 'Location: http://www.google.com' );
  echo "Please wait...";
  exit(0);
}
include ("mysql.php");
$mysql = new mysql;
$thedb=$mysql->connect();
$connectionlogstable=$mysql->getConnectionLogs();
$importlogtable=$mysql->getImportLogs();
$logfolder=$mysql->getGrimoireLogFolder();
$timestamp = strtotime("yesterday");
$yesterday = date('m/d/Y',$timestamp);
$yesterday2 = date('Y-m-d',$timestamp);
if($old=="no"){
	$thefile = "Userlog-$yesterday2.csv";
} else {
	$thefile = "ConnectionLog-$yesterday2.csv";
}
$filename = "$logfolder/" . "$thefile";
$sql_delete_prev = "DELETE FROM $thedb.$connectionlogstable WHERE DATE = '$yesterday'";
mysql_query($sql_delete_prev) or die("MySQL Error: " . mysql_error());

CSVImport("$thedb.$connectionlogstable", array('status','date','time','nickname','ip','description','email','version','sharesize','activepassive','speed'), $filename, $thefile, $importlogtable, $dateformat);
function CSVImport($table, $fields, $file, $filename, $importlogtable, $dateformat) {
	global $mysql;
    $handle = fopen($file,'r');
    if(!$handle) die('Cannot open uploaded file.');

    $row_count = 0;
    $sql_query = "INSERT INTO $table(". implode(',',$fields) .") VALUES(";
    $rows = array();
    while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
		$counter=1;
        $row_count++;
        foreach($data as $key=>$value) {
			if ($counter==2){
				if($dateformat=="american"){
					list($day, $month, $year) = explode("/", $value);
					$value=$month."/".$day."/".$year;
				}
			}
			if($counter==3){
				$timestamp = strtotime("yesterday");
				$monthtoday=date('m',$timestamp);
				$daytoday=date('d',$timestamp);
				$yeartoday=date('Y',$timestamp);
				list($hour, $minutes, $seconds) = explode(":", $value);
				$value=mktime($hour,$minutes,$seconds,$monthtoday,$daytoday,$yeartoday);
			}
			if($counter==9){
				list($sizevalue, $sizeextension) = explode(" ", $value);
				if($sizeextension=="KiBs"){
					$value=round(($sizevalue*1024),0);
				}
				if($sizeextension=="MiBs"){
					$value=round(($sizevalue*1048576),0);
				}
				if($sizeextension=="GiBs"){
					$value=round(($sizevalue*1073741824),0);
				}
				if($sizeextension=="TiBs"){
					$value=round(($sizevalue*1099511627776),0);
				}
			}
			$counter+=1;
            $data[$key] = "'" . addslashes($value) . "'";
        }
        $rows[] = implode(",",$data);
    }
    $sql_query .= implode("),(", $rows);
    $sql_query .= ")";
    fclose($handle);
	$thedate = date("m/d/Y",time());
	$thetime = date("g:i:sa",time());
	if(count($rows)) {
        mysql_query($sql_query);
        print 'Successfully imported '.$row_count.' record(s)';
		$sql_log = "INSERT INTO $thedb.$importlogtable (date,time,executedfile,status,filenameimported,numrecords,tablename) VALUES('$thedate','$thetime','importendofday.php','success','$filename','$row_count','$table');";
		mysql_query($sql_log);
    } else {
        print 'Cannot import data - no records found.';
		$sql_log = "INSERT INTO $thedb.$importlogtable (date,time,executedfile,status,filenameimported,numrecords,tablename) VALUES('$thedate','$thetime','importendofday.php','failure','$filename','0','$table');";
		mysql_query($sql_log);
    }
}
$mysql->disconnect();
?>

Here are some scripts to import you Grimoire registration logs too...

This one imports all of your Registration logs for the first time:

"reglogimportall.php"

<?php
/*
Purpose of File:
Imports all the Registration logs into the reglog table
*/
//Start user configuration area
/*
Modify the $wipeout variable to "no" if you do not want to erase your current reglog DB
Otherwise your current reglog DB will be erased
*/
$wipeout="yes";
//End user configuration area part 1...see bottom of script for part 2
/*
The following few lines restricts this script to only run on the server it is located on
If any other computer/ip address tries to access this script, their browser will be redirected to google
*/
$test = "^127.0";
$ip = $_SERVER['REMOTE_ADDR'];
if (!eregi($test,$ip)){
  header( 'Location: http://www.google.com' );
  echo "Please wait...";
  exit(0);
}
include("mysql.php");
$mysql = new mysql;
$thedb=$mysql->connect();
$reglogstable=$mysql->getRegLogs();
$logfolder=$mysql->getRegLogFolder();
if (is_dir($logfolder)){
	chdir($logfolder);
}
$files = scandir($logfolder);
$arraysize=sizeof($files);
$counter=0;
if($wipeout=="yes"){
	$howmanybefore="select count(id) as idcount FROM $thedb.$reglogstable;";
	$howmanybefore=mysql_query($howmanybefore);
	$howmanybefore = mysql_fetch_object($howmanybefore);
	$before = $howmanybefore->idcount;
	$deleteprev="DELETE FROM $thedb.$reglogstable;";
	mysql_query($deleteprev);
	echo "<b>DB Status:</b> DB $thedb.$reglogstable successfully erased '$before' records<br />";
} else {
	echo "<b>DB Status:</b> Previous DB $thedb.$connectionlogstable not erased<br />";
}
for($i=0; $i<$arraysize; $i++){
	if(is_file($files[$i])){
		list($garbage, $date)=explode("-",$files[$i],2);
		list($date, $garbage)=explode(".",$date,2);
		list($year, $month, $day)=explode("-",$date);
		$handle = @fopen($files[$i], "r");
		if ($handle) {
			while (!feof($handle)) {
				$contentline = fgets($handle, 4096);
				list($time, $secondpart) = explode(" - ", $contentline);
				if($time){
					list($hour, $minute, $second) = explode(":",$time);
					$time=mktime($hour,$minute,$second,$month,$day,$year);
					list($nickname, $action) = explode(" ",$secondpart,2);
					//Start user configuration area
					/*
						Modify case statement below with the names of your profiles
						Add any needed profiles and remove unneeded profiles
					*/
					switch($nickname){
						case "Master": list($nickname, $action) = explode(" ",$action,2); break;
						case "Operator": list($nickname, $action) = explode(" ",$action,2); break;
						case "Reg": list($nickname, $action) = explode(" ",$action,2); break;
						case "TS": list($nickname, $action) = explode(" ",$action,2); break;
						case "NoShare": list($nickname, $action) = explode(" ",$action,2); break;
					}
					//End user configuration area
					$action = substr($action,0,strlen($action)-2);
					$insertquery="INSERT INTO $thedb.$reglogstable (nickname,time,action) values ('$nickname','$time','$action');";
					mysql_query($insertquery);
					$counter+=1;
				}
			}
			fclose($handle);
		}
		echo "<b>$counter</b> records imported for <b>$month/$day/$year</b><br />";
		$counter=0;
	}
}
$mysql->disconnect();
?>
</body>
</html>

The reglogimporthourly.php file should be scheduled to run each hour of the day:

"reglogimporthourly.php"

<?php
/*
Purpose of File:
-Imports Registration logs from the last hour into the reglog table
-Schedule this script to run hourly
*/
//See bottom of script for user configuration
/*
The following few lines restricts this script to only run on the server it is located on
If any other computer/ip address tries to access this script, their browser will be redirected to google
*/
$test = "^127.0";
$ip = $_SERVER['REMOTE_ADDR'];
if (!eregi($test,$ip)){
  header( 'Location: http://www.google.com' );
  echo "Please wait...";
  exit(0);
}
include("mysql.php");
$mysql = new mysql;
$thedb=$mysql->connect();
$reglogstable=$mysql->getRegLogs();
$logfolder=$mysql->getRegLogFolder();
if (is_dir($logfolder)){
	chdir($logfolder);
}
$begintime=0;
$before=0;
$todaytime=time();
$hournow=date('H',$todaytime);
if ($hournow=="00"){
	$todaytime=strtotime("yesterday");
	$todaymonth=date('m',$todaytime);
	$todayday=date('d',$todaytime);
	$todayyear=date('Y',$todaytime);
	$begintime=mktime(0,0,0,$todaymonth,$todayday,$todayyear);
} else {
	$todaymonth=date('m',$todaytime);
	$todayday=date('d',$todaytime);
	$todayyear=date('Y',$todaytime);	
	$begintime=mktime(0,0,0,$todaymonth,$todayday,$todayyear);
}
$logfile="RegLog-$todayyear-$todaymonth-$todayday.txt";
$logfile="$logfolder/$logfile";
$counter=0;
if(is_file($logfile)){
	$handle = @fopen($logfile, "r");
	if ($handle) {
		$howmanybefore="select count(id) as idcount FROM $thedb.$reglogstable where time > $begintime";
		$howmanybefore=mysql_query($howmanybefore);
		$howmanybefore = mysql_fetch_object($howmanybefore);
		$before = $howmanybefore->idcount;
		$deleteprev="DELETE FROM $thedb.$reglogstable where time > $begintime;";
		mysql_query($deleteprev);
		while (!feof($handle)) {
			$contentline = fgets($handle, 4096);
			list($time, $secondpart) = explode(" - ", $contentline);
			if($time){
				list($hour, $minute, $second) = explode(":",$time);
				$time=mktime($hour,$minute,$second,$todaymonth,$todayday,$todayyear);
				list($nickname, $action) = explode(" ",$secondpart,2);
				//Start user configuration area
					/*
						Modify case statement below with the names of your profiles
						Add any needed profiles and remove unneeded profiles
					*/
					switch($nickname){
						case "Master": list($nickname, $action) = explode(" ",$action,2); break;
						case "Operator": list($nickname, $action) = explode(" ",$action,2); break;
						case "Reg": list($nickname, $action) = explode(" ",$action,2); break;
						case "TS": list($nickname, $action) = explode(" ",$action,2); break;
						case "NoShare": list($nickname, $action) = explode(" ",$action,2); break;
					}
				//End user configuration area
				$action = substr($action,0,strlen($action)-2);
				$insertquery="INSERT INTO $thedb.$reglogstable (nickname,time,action) values ('$nickname','$time','$action');";
				mysql_query($insertquery);
				$counter+=1;
			}
		}
		fclose($handle);
	}
	echo "<b>$before</b> records deleted before the import for <b>$todaymonth/$todayday/$todayyear</b><br />";
	echo "<b>$counter</b> records imported for <b>$todaymonth/$todayday/$todayyear</b><br />";
	$counter=0;
}
$mysql->disconnect();
?>

This is the main interface for the web GUI that I have created (You can edit the CSS file below to change all the pages):

"index.php"

<html>
<head>
<link rel='stylesheet' type='text/css' href='default.css'>
<title>Admin Stuff</title>
<style type='text/css'>
table.tb
{
	top: 90px;
	left: 20px;
}
</style>
</head>
<body>
<h1 class='title'>Admin Stuff<br /><a class="admin" href="index.php">Admin Stuff</a></h1>
<table border='0' class='tb'>
<tr>
<td><ul>
	<li><a href='accounts.php'>Accounts</a></li>
	<li><a href='notregistered.php'>Accounts not Registered</a></li>
	<li><a href='reglog.php'>Registration Log</a></li>
	<li><a href='manualreglogimport.php'>Manual Registration Log Import</a></li>
	<li><a href='manualgrimoireimport.php'>Manual Grimoire Log Import</a></li>
	<li><a href='connectdisconnectall.php'>Top Connects/Disconnects by Nickname</a></li>
	<li><a href='connectdisconnecttoday.php'>Top Connects/Disconnects by Nickname in Last 24 Hours</a></li>
	<li><a href='versions.php'>Top Connects/Disconnects by Client Software</a></li>
	<li><a href='viewdayhour.php'>Connects/Disconnects by Day and Hour</a></li>
	<li><a href='topshare.php'>Top Shares</a></li>
	<li><a href='multip.php'>Nicknames Using the Same IP</a></li>
	<li><a href='time.php'>PHP Time Conversions</a></li>
</ul></td>
</tr>
</table>
</body>
</html>

Here's a screenshot of what it looks like (I was doing a mouseover on the Registration Log link):



Here is the css file for all the pages:

"default.css"

body { background-color: black; }
h1.title { font-family: Albany,Trebuchet MS;	color: white; position: absolute; top: 32px; left: 30px; }
h1 a.admin { font-family: Albany,Trebuchet MS; font-size: 14px; color:#4169E1; position: absolute; top: 40px; left: 10px; }
h1 a.admin:hover { color:#FFD700; text-decoration:none; }
div.links { font-family: Albany,Trebuchet MS; font-size: 16px; font-weight: bold; font-style: italic; position: absolute; top: 75px; left: 120px; }
a:link { font-family: Albany,Trebuchet MS; color:#4169E1; text-decoration:none; }
a:visited { font-family: Albany,Trebuchet MS; color:#4169E1; text-decoration:none; }
a:hover { font-family: Albany,Trebuchet MS; color:#FFD700; text-decoration:none; }
table tr { font-family: Albany,Trebuchet MS; text-align: center; }
table.tb { font-family: Albany,Trebuchet MS; color: white; position: absolute; }
table.tb th { text-align: center; }
table.tb td { text-align: left; }
table.tb2 { font-family: Albany,Trebuchet MS; color: white; position: absolute; }
table.tb2 th { text-align: center; }
table.tb3 { font-family: Albany,Trebuchet MS; color: white; position: absolute; }
table.tb3 th { text-align: center; }
font.results { font-family: Albany,Trebuchet MS; position: absolute; color: white; }

This script will modify the alternating row background colors...modify it to your liking:

"rowbackground.php"

<?php
/*
Purpose of File:
Sets the background and font color of the alternating background rows in the web tables
*/
/*
Modify the color variables for whatever row backgrounds you want
to be displayed on each page...This file effects all pages with table displays
that have alternating row colors
*/
if($count % 2){
	$fontcolor="black";
	$backgroundcolor="gray";
	$count+=1;
} else {
	$fontcolor="white";
	$backgroundcolor="black";
	$count+=1;
}
?>

Please see my next post for more stuff...I hit the 50,000 character limit in a post  

[quicky2g]

Ok...back for more...

I previously posted a link about an account viewer here: http://forum.ptokax.org/index.php?topic=8155
The new one utilizes some of the Grimoire log DB features. Use this script instead:

"accounts.php"

<?php
/*
Purpose of File:
Used to display info about accounts
*/
//Start user configuration area part 1
//Modify $dir and $userlist variable for whatever folder and file path your RegisteredUsers.xml file is in
$dir = "C:/0.4.1.1/cfg/";
$userlist = "RegisteredUsers.xml";
$userlistfull="$dir/" . "$userlist";
//Modify the case statement below for the text strings related to the profile numbers
function findProf ($profnumber) {
	switch($profnumber){
		case 0: $displayprof="Master"; break;
		case 1: $displayprof="Operator"; break;
		case 2: $displayprof="NoShare"; break;
		case 3: $displayprof="Reg"; break;
		case 4: $displayprof="TS"; break;
	}
	return $displayprof;
}
//Modify the $viewpass variable to "yes" if you want to output the passwords on the page
$viewpass="no";
/*
Modify the $begin variables to change the starting time that the logs will look at in their queries
For example the accounts considered in this script will be any account logged in the database after
January 1st, 2009 12:00am (Because of the settings below which can be modified)
The hour variable goes from 0 to 23, the minute and second variables from 0 to 59,
the month variable from 1 to 12, the day variable from 1 to 31, and the year variable...pretty obvious
*/
$beginhour="0";
$beginminute="0";
$beginsecond="0";
$beginmonth="1";
$beginday="1";
$beginyear="2009";
$begintime=mktime($beginhour,$beginminute,$beginsecond,$beginmonth,$beginday,$beginyear);
/*
End user configuration area part 1...see below in html code where to modify your
webpage display for the profiles you use
*/
include ("mysql.php");
$mysql = new mysql;
$thedb=$mysql->connect();
$connectionlogstable=$mysql->getConnectionLogs();
?>
<html>
<head>
<title>Accounts</title>
<link rel="stylesheet" type="text/css" href="default.css">
<style type="text/css">
font.total1
{
	font-family: Albany,Trebuchet MS;
	color: white;
	position: absolute;
	left: 185px;
	top: 30px;
}
font.total2
{
	font-family: Albany,Trebuchet MS;
	color: white;
	position: absolute;
	left: 185px;
	top: 50px;
}
table td {
	width: 80px;
	text-align: center;
	padding: 1px 1px 1px 1px;
}
table.tb
{
	top: 100px;
	left: 20px;
}
table.tb2
{
	top: 280px;
	left: 20px;
}
table.tb3
{
	top: 480px;
	left: 40px;
}
table.tb3 td {
	width: 120px;
	padding: 3px 3px 3px 3px;
}
font.results
{
	left: 40px;
	top: 445px;
}
</style>
</head>
<body>
<h1 class="title">Accounts<br /><a class="admin" href="index.php">Admin Stuff</a></h1>
<?php
$totaluserquery="SELECT id FROM $thedb.$connectionlogstable where time > '$begintime' group by nickname;";
$result=mysql_query($totaluserquery);
$total1=0;
while(list($id) = mysql_fetch_array($result)) { $total1+=1; }
$xml = simplexml_load_file($dir.$userlist);
$total2 = count($xml->RegisteredUser);
echo "<font class='total1' size='4'>($total1 Total Users Have Connected)</font>";
echo "<font class='total2' size='4'>($total2 Total Registered Users)</font>";
?>
<table class="tb" border="0">
<tr><td>
	<fieldset style="width: 450px"><legend><b><i><font color="white">View Nicknames by Profile</font></i></b></legend>
		<form method="GET">
			<table border="0">
			<?php
			/*
			Start user configuration area part 2
			Modify this table row to change the web page display of whatever profiles you are using
			Remove table headers that you don't need
			Add any table headers that you do need
			*/
			?>
			<tr>
				<th>&nbsp;&nbsp;<font color='white'>Master</font>&nbsp;&nbsp;</th>
				<th>&nbsp;&nbsp;<font color='white'>Operator</font>&nbsp;&nbsp;</th>
				<th>&nbsp;&nbsp;<font color='white'>NoShare</font>&nbsp;&nbsp;</th>
				<th>&nbsp;&nbsp;<font color='white'>Reg</font>&nbsp;&nbsp;</th>
				<th>&nbsp;&nbsp;<font color='white'>TS</font>&nbsp;&nbsp;</th>
			</tr>
			<?php
			/*
			Modify this table row to change the values of the profiles you are using
			Typically Master profiles are 0, Operators are 1, etc.
			Remove any table definitions you don't need
			Add any table definitions you do need
			*/
			?>
			<tr>
				<td><center><input type="radio" value="0" name="prof" <?php if(isset($_GET['prof']) && ($_GET['prof']==0)){echo "checked";}?>></center></td>
				<td><center><input type="radio" value="1" name="prof" <?php if(isset($_GET['prof']) && ($_GET['prof']==1)){echo "checked";}?>></center></td>
				<td><center><input type="radio" value="2" name="prof" <?php if(isset($_GET['prof']) && ($_GET['prof']==2)){echo "checked";}?>></center></td>
				<td><center><input type="radio" value="3" name="prof" <?php if(isset($_GET['prof']) && ($_GET['prof']==3)){echo "checked";}?>></center></td>
				<td><center><input type="radio" value="4" name="prof" <?php if(isset($_GET['prof']) && ($_GET['prof']==4)){echo "checked";}?>></center></td>
			</tr>
			<?php
			//End user configuration area part 2
			?>
			</table>
			<br />
			<center><input type="submit" value="View" name="profsub"></center>
			<center><font color="white">Check to see IP Addresses</font>
			<input type="checkbox" name="ip"></center>
		</form>
	</fieldset>
</td></tr>
</table>
<br />
<table class="tb2" border="0">
<tr><td colspan="2">
	<fieldset style="width: 450px"><legend><b><i><font color="white">Search for a Nickname</font></i></b></legend>
		<form method="GET">
			<br />
			<center><input style="width: 250px" type="text" name="nicksearch" value="<?php if (isset($_GET['nicksearch'])) { echo $_GET['nicksearch']; } ?>" />
			<input type="submit" value="Search" /></center>
			<font color="white">Check to see IP Addresses</font>
			<input type="checkbox" name="ip">
			</form>
			<center>Enter a "." (dot) to see all nicknames</center>
	</fieldset>
</td></tr>
</table>
<?php
if (isset($_GET['prof'])) {
	$desiredprofile = $_GET['prof'];
}
if (isset($_GET['nicksearch'])) {
	$desiredstring = $_GET['nicksearch'];
}
if(isset($_GET['ip'])) {
	$theip=$_GET['ip'];
}
//Uncomment next line to force profile output on page for a profile level
//$desiredprofile = "0";
$dom = new DOMDocument();
$dom->load($userlistfull);
$regcounter = 0;
if (isset($_GET['ip']) && ((isset($_GET['nicksearch']) && !$desiredstring=="") || isset($_GET['prof']))) {
	if (isset($_GET['nicksearch'])) {
		if ($viewpass=="no"){
			echo "<table class='tb3' border='1'><tr><th>Registered Number</th><th>Nickname</th><th>Profile</th><th>IP Address(es)</th></tr>";
		} else {
			echo "<table class='tb3' border='1'><tr><th>Registered Number</th><th>Nickname</th><th>Password</th><th>Profile</th><th>IP Addresses</th></tr>";
		}
	} else {
		if ($viewpass=="no"){
			echo "<table class='tb3' border='1'><tr><th>Registered Number</th><th>Nickname</th><th>IP Address(es)</th></tr>";
		} else {
			echo "<table class='tb3' border='1'><tr><th>Registered Number</th><th>Nickname</th><th>Password</th><th>IP Address(es)</th></tr>";
		}
	}
} else if ((isset($_GET['nicksearch']) && !$desiredstring=="") || (isset($_GET['prof']))) {
	if (isset($_GET['nicksearch'])) {
		if ($viewpass=="no"){
			echo "<table class='tb3' border='1'><tr><th>Registered Number</th><th>Nickname</th><th>Profile</th></tr>";
		} else {
			echo "<table class='tb3' border='1'><tr><th>Registered Number</th><th>Nickname</th><th>Password</th><th>Profile</th></tr>";
		}
	} else {
		if ($viewpass=="no"){
			echo "<table class='tb3' border='1'><tr><th>Registered Number</th><th>Nickname</th></tr>";
		} else {
			echo "<table class='tb3' border='1'><tr><th>Registered Number</th><th>Nickname</th><th>Password</th></tr>";
		}
	}
}
$regcount=0;
$rowcountprof=0;
$rowcountnick=0;
if ((isset($_GET['nicksearch']) && !$desiredstring=="") || (isset($_GET['prof']))) {
	foreach ($dom->getElementsByTagname('RegisteredUser') as $element) {
		$Nick = $element->getAttribute('Nick');
		foreach (($element->childNodes) as $e) {
			if (is_a($e, 'DOMElement')) {
				if ($e->tagName == 'Nick') {
					$Nick = htmlspecialchars($e->textContent);
				} elseif ($e->tagName == 'Password') {
					$Password = htmlspecialchars($e->textContent);
				} elseif ($e->tagName == 'Profile') {
					$Profile = htmlspecialchars($e->textContent);
				}
			}
		}
		$regcount+=1;
		if ($Profile == $desiredprofile){
			$count=$rowcountprof;
			include("rowbackground.php");
			$rowcountprof+=1;
			if(isset($_GET['ip'])) {
				$query="SELECT distinct(ip) FROM $thedb.$connectionlogstable where nickname='$Nick' and time > '$begintime' order by ip desc;";
				$result = mysql_query($query);
				$ipvaluecount=0;
				$valuear[]=0;
				while(list($ipvalues) = mysql_fetch_array($result)) {
					if ($ipvaluecount==0) {
						if ($viewpass=="no"){
							echo "<tr style='color: $fontcolor; background: $backgroundcolor;'><td>$regcount</td><td>$Nick</td><td>$ipvalues";
						} else {
							echo "<tr style='color: $fontcolor; background: $backgroundcolor;'><td>$regcount</td><td>$Nick</td><td>$Password</td><td>$ipvalues";
						}
						$ipvaluecount+=1;
					} else {
						echo "<br />$ipvalues";
					}
				}
				echo "</td></tr>";
			} else {
				if ($viewpass=="no"){
					echo "<tr style='color: $fontcolor; background: $backgroundcolor;'><td>$regcount</td><td>$Nick</td></tr>";
				} else {
					echo "<tr style='color: $fontcolor; background: $backgroundcolor;'><td>$regcount</td><td>$Nick</td><td>$Password</td></tr>";
				}
			}
		} else if (eregi($desiredstring,$Nick)){
			$count=$rowcountnick;
			include("rowbackground.php");
			$rowcountnick+=1;
			if(isset($_GET['ip'])) {
				$query="SELECT distinct(ip) FROM $thedb.$connectionlogstable where nickname='$Nick' and time > '$begintime' order by ip desc;";
				$result = mysql_query($query);
				$ipvaluecount=0;
				while(list($ipvalues) = mysql_fetch_array($result)) {
					if ($ipvaluecount==0) {
						if ($viewpass=="no"){
							echo "<tr style='color: $fontcolor; background: $backgroundcolor;'><td>$regcount</td><td>$Nick</td><td>" . findProf($Profile) . "</td><td>$ipvalues";
						} else {
							echo "<tr style='color: $fontcolor; background: $backgroundcolor;'><td>$regcount</td><td>$Nick</td><td>$Password</td><td>" . findProf($Profile) . "</td><td>$ipvalues";
						}
						$ipvaluecount+=1;
					} else {
						echo "<br />$ipvalues";
					}
				}
				echo "</td></tr>";
			} else {
				if (isset($_GET['nicksearch'])) {
					if ($viewpass=="no"){
						echo "<tr style='color: $fontcolor; background: $backgroundcolor;'><td>$regcount</td><td>$Nick</td><td>" . findProf($Profile) . "</td></tr>";
					} else {
						echo "<tr style='color: $fontcolor; background: $backgroundcolor;'><td>$regcount</td><td>$Nick</td><td>$Password</td><td>" . findProf($Profile) . "</td></tr>";
					}
				} else {
					if ($viewpass=="no"){
						echo "<tr style='color: $fontcolor; background: $backgroundcolor;'><td>$regcount</td><td>$Nick</td></tr>";
					} else {
						echo "<tr style='color: $fontcolor; background: $backgroundcolor;'><td>$regcount</td><td>$Nick</td><td>$Password</td></tr>";
					}
				}
			}
		}
	}
}
if (isset($_GET['prof']) && $desiredprofile=="0") {
	echo "<font class='results'><b>" . $rowcountprof . "</b> users with the profile: <b>'" . findProf($_GET['prof']) . "':</b></font><br /><br />";
} else if (isset($_GET['prof'])) {
	echo "<font class='results'><b>" . $rowcountprof . "</b> users with the profile: <b>'" . findProf($_GET['prof']) . "':</b></font><br /><br />";
} else if (isset($_GET['nicksearch']) && !$desiredstring=="") {
	echo "<font class='results'><b>" . $rowcountnick . "</b> user(s) matching the string <b>'$desiredstring':</b></font><br /><br />";
} else if (isset($_GET['nicksearch']) && $desiredstring=="") {
	echo "<font class='results'>Please enter a valid nickname search string</font>";
} else if (isset($_GET['profsub']) && !isset($_GET['prof'])) {
	echo "<font class='results'>Please select a profile to view</font>";
}
$mysql->disconnect();
?>
</body>
</html>

Here is a screenshot of the new accounts page:



To view accounts that have connected to your hub but have not registered, use this script:

"notregistered.php"

<?php
/*
Purpose of File:
Displays nicknames that have connected to your hub but not registered
with the option to view their IP address
*/
//Start user configuration area
//Modify $dir and $userlist variable for whatever folder and file path your RegisteredUsers.xml file is in
$dir = "C:/0.4.1.1/cfg/";
$userlist = "RegisteredUsers.xml";
$userlistfull="$dir" . "$userlist";
/*
Modify the $begin variables to change the starting time that the logs will look at in their queries
For example the accounts considered in this script will be any account logged in the database after
January 1st, 2009 12:00am (Because of the settings below which can be modified)
The hour variable goes from 0 to 23, the minute and second variables from 0 to 59,
the month variable from 1 to 12, the day variable from 1 to 31, and the year variable...pretty obvious
*/
$beginhour="0";
$beginminute="0";
$beginsecond="0";
$beginmonth="1";
$beginday="1";
$beginyear="2009";
$begintime=mktime($beginhour,$beginminute,$beginsecond,$beginmonth,$beginday,$beginyear);
//End user configuration area
include ("mysql.php");
$mysql = new mysql;
$thedb=$mysql->connect();
$connectionlogstable=$mysql->getConnectionLogs();
?>
<html>
<head>
<title>Accounts not Registered</title>
<link rel="stylesheet" type="text/css" href="default.css">
<style type="text/css">
font.total1
{
	font-family: Albany,Trebuchet MS;
	color: white;
	position: absolute;
	left: 410px;
	top: 50px;
}
font.total2
{
	font-family: Albany,Trebuchet MS;
	color: white;
	position: absolute;
	left: 410px;
	top: 30px;
}
font.results
{
	left: 40px;
	top: 165px;
}
table.tb
{
	left: 20px;
	top: 100px;
}
table.tb2
{
	left: 40px;
	top: 205px;
}
</style>
</head>
<body>
<h1 class="title">Accounts not Registered<br /><a class="admin" href="index.php">Admin Stuff</a></h1>
<?php
$totaluserquery="SELECT id FROM $thedb.$connectionlogstable where time > '$begintime' group by nickname;";
$result=mysql_query($totaluserquery);
$total1=0;
while(list($id) = mysql_fetch_array($result)) {	$total1+=1; }
$xml = simplexml_load_file($dir.$userlist);
$total2 = count($xml->RegisteredUser);
echo "<font class='total1' size='4'>($total1 Total Users Have Connected)</font>";
echo "<font class='total2' size='4'>($total2 Total Registered Users)</font>";
?>
<table border="0" class="tb">
<form method="GET">
<tr>
	<td>View Nicknames and IP Addresses</td><td><input type="submit" value="View" name="seeip"></td>
</tr>
<tr>
	<td>View Just Nicknames</td><td><input type="submit" value="View"></td>
</tr>
</form>
</table>
<?php
$arcount=0;
//Uncomment next line to force profile output on page for a profile level
//$desiredprofile = "0";
$dom = new DOMDocument();
$dom->load($userlistfull);
foreach ($dom->getElementsByTagname('RegisteredUser') as $element) {
	$Nick = $element->getAttribute('Nick');
	foreach (($element->childNodes) as $e) {
		if (is_a($e, 'DOMElement')) {
			if ($e->tagName == 'Nick') {
				$Nick = htmlspecialchars($e->textContent);
			} elseif ($e->tagName == 'Password') {
				$Password = htmlspecialchars($e->textContent);
			} elseif ($e->tagName == 'Profile') {
				$Profile = htmlspecialchars($e->textContent);
			}
		}
	}
	$ar[$arcount]=$Nick;
	$arcount+=1;
}
$findnick="SELECT nickname FROM $thedb.$connectionlogstable where time > '$begintime' group by nickname;";
$findnick=mysql_query($findnick);
$findnickcount=0;
$howmany=0;
if(isset($_GET['seeip'])) {
	echo "<table class='tb2' border='1' cellpadding='10px'><tr><th>Number</th><th>Unregistered Nickname</th><th>Last IP Address</th></tr>";
} else {
	echo "<table class='tb2' border='1' cellpadding='10px'><tr><th>Number</th><th>Unregistered Nickname</th></tr>";
}
while(list($nick) = mysql_fetch_array($findnick)) {
	$found=0;
	for ($loopcounter=0; $loopcounter<$arcount; $loopcounter++) {
		$nickvalue=$ar[$loopcounter];
		/*if ($nickvalue=="$nick"){
			$found=1;
		}*/
                /*
                Comparison above can lead to problems if nick in DB has a different case than what is registered
                For example this script will detect quicky2g as a different nick than Quicky2g
                Use below comparison which is case insensitive instead
                */
                $result=strcasecmp($nickvalue,$nick);
		if ($result==0){
			$found=1;
		}
	}
	if($found=="0"){
		$howmany+=1;
		include("rowbackground.php");
		if(isset($_GET['seeip'])) {
			$theip="SELECT ip FROM $thedb.$connectionlogstable where time > '$begintime' and nickname='$nick' order by id desc limit 1;";
			$theip=mysql_query($theip);
			$theip = mysql_fetch_object($theip);
			$theip = $theip->ip;
			echo "<tr style='color: $fontcolor; background: $backgroundcolor;'><td>$howmany</td><td>$nick</td><td>$theip</td></tr>";
		} else {
			echo "<tr style='color: $fontcolor; background: $backgroundcolor;'><td>$howmany</td><td>$nick</td></tr>";
		}
	}
	$findnickcount+=1;
}
echo "</table>";
echo "<font class='results'><b>$howmany</b> Unregistered Nicknames:</font>";
$mysql->disconnect();
?>
</body>
</html>

Here is a screenshot of the accounts not registered page:



Still more to go...see my next post

[quicky2g]

Use this script to view the Grimoire registration log:

"reglog.php"

<?php
/*
Purpose of File:
Displays the registration logs
*/
//No user configuration needed
include ("mysql.php");
$mysql = new mysql;
$thedb=$mysql->connect();
$reglogstable=$mysql->getRegLogs();
?>
<html>
<head>
<link rel="stylesheet" type="text/css" href="default.css">
<style type="text/css">
table.tb
{
	top: 130px;
	left: 20px;
}
font.results
{
	top: 230px;
	left: 40px;
}
table.tb3
{
	top: 270px;
	left: 40px;
}
</style>
<title>Registration Log</title>
</head>
<body>
<h1 class="title">Registration Log<br /><a class="admin" href="index.php">Admin Stuff</a></h1>
<table border="0" class="tb">
<tr>
	<form method="GET">
		<td><font color="white" style="font-family: Albany,Trebuchet MS">Nickname:</font></td>
		<td><input style="width: 380px" type="text" name="nicksearch" value="<?php if (isset($_GET['nicksearch'])) { echo $_GET['nicksearch']; } ?>" /></td>
		<td><input type="submit" value="Search"></td>
	</form>
</tr>
<tr>
	<form method="GET">
		<td><font color="white" style="font-family: Albany,Trebuchet MS">Time:</font></td>
		<td>
			<select name="timesearch" style="width: 380px;">
				<option value="hour" <?php if(isset($_GET['timesearch']) && ($_GET['timesearch']=='hour')){echo "selected";}?>>Last Hour</option>
				<option value="12hour" <?php if(isset($_GET['timesearch']) && ($_GET['timesearch']=='12hour')){echo "selected";}?>>Last 12 Hours</option>
				<option value="24hour" <?php if(isset($_GET['timesearch']) && ($_GET['timesearch']=='24hour')){echo "selected";}?>>Last 24 Hours</option>
				<option value="week" <?php if(isset($_GET['timesearch']) && ($_GET['timesearch']=='week')){echo "selected";}?>>Last Week</option>
				<option value="30days" <?php if(isset($_GET['timesearch']) && ($_GET['timesearch']=='30days')){echo "selected";}?>>Last 30 Days</option>
				<option value="all" <?php if(isset($_GET['timesearch']) && ($_GET['timesearch']=='all')){echo "selected";}?>>All</option>
			</select>
		</td>
		<td><input class="view" type="submit" value="Search"></td>
	</form>
</tr>
<tr>
	<form method="GET">
		<td><font color="white" style="font-family: Albany,Trebuchet MS">Action:</font></td>
		<td>
		<select style="width: 380px;" name="action">
		<?php
		$result = mysql_query("SELECT distinct action FROM $thedb.$reglogstable order by action asc;");     
		while($each = mysql_fetch_assoc($result)){
			echo "<option value='" . $each['action'] . "'>" . $each['action'] . "</option>";
		}
		mysql_free_result($result);
		?>
		</select>
		</td>
		<td><input type="submit" value="Search"></td>
	</form>
</tr>
</table>
<?php
$currenttime=time();
if (isset($_GET['nicksearch'])) {
	$nicksearch=$_GET['nicksearch'];
	$result = mysql_query("SELECT * FROM $thedb.$reglogstable where nickname like '%$nicksearch%' ORDER BY id DESC;");
	$result2 = mysql_query("SELECT COUNT(id) as id FROM $thedb.$reglogstable where nickname like '%$nicksearch%';");
	$theresult2 = mysql_fetch_object($result2);
	$result2value = $theresult2->id;
	echo "<font class='results' color='white'><b>$result2value</b> entries for nickname <b>'$nicksearch'</b></font>";
}

if (isset($_GET['timesearch'])) {
	$timesearchvalue=$_GET['timesearch'];
	switch ($timesearchvalue)
	{
	case "hour":
		$chosentime=$currenttime-3600;
		$result = mysql_query("SELECT * FROM $thedb.$reglogstable where time>$chosentime ORDER BY id DESC;");
		$result2 = mysql_query("SELECT COUNT(id) as id FROM $thedb.$reglogstable where time>$chosentime;");
		$theresult2 = mysql_fetch_object($result2);
		$result2value = $theresult2->id;
		echo "<font class='results' color='white'><b>$result2value</b> entries for the last hour:</font><br /><br />";
		break;
	case "12hour":
		$chosentime=$currenttime-43200;
		$result = mysql_query("SELECT * FROM $thedb.$reglogstable where time>$chosentime ORDER BY id DESC;");
		$result2 = mysql_query("SELECT COUNT(id) as id FROM $thedb.$reglogstable where time>$chosentime;");
		$theresult2 = mysql_fetch_object($result2);
		$result2value = $theresult2->id;
		echo "<font class='results' color='white'><b>$result2value</b> entries for the last 12 hours:</font><br /><br />";
		break;
	case "24hour":
		$chosentime=$currenttime-86400;
		$result = mysql_query("SELECT * FROM $thedb.$reglogstable where time>$chosentime ORDER BY id DESC;");
		$result2 = mysql_query("SELECT COUNT(id) as id FROM $thedb.$reglogstable where time>$chosentime;");
		$theresult2 = mysql_fetch_object($result2);
		$result2value = $theresult2->id;
		echo "<font class='results' color='white'><b>$result2value</b> entries for the last 24 hours:</font><br /><br />";
		break;
	case "week":
		$chosentime=$currenttime-604800;
		$result = mysql_query("SELECT * FROM $thedb.$reglogstable where time>$chosentime ORDER BY id DESC;");
		$result2 = mysql_query("SELECT COUNT(id) as id FROM $thedb.$reglogstable where time>$chosentime;");
		$theresult2 = mysql_fetch_object($result2);
		$result2value = $theresult2->id;
		echo "<font class='results' color='white'><b>$result2value</b> entries for the last week:</font><br /><br />";
		break;
	case "30days":
		$chosentime=$currenttime-2592000;
		$result = mysql_query("SELECT * FROM $thedb.$reglogstable where time>$chosentime ORDER BY id DESC;");
		$result2 = mysql_query("SELECT COUNT(id) as id FROM $thedb.$reglogstable where time>$chosentime;");
		$theresult2 = mysql_fetch_object($result2);
		$result2value = $theresult2->id;
		echo "<font class='results' color='white'><b>$result2value</b> entries for the last 30 days:</font><br /><br />";
		break;
	case "all":
		$result = mysql_query("SELECT * FROM $thedb.$reglogstable ORDER BY id DESC;");
		$result2 = mysql_query("SELECT COUNT(id) as id FROM $thedb.$reglogstable;");
		$theresult2 = mysql_fetch_object($result2);
		$result2value = $theresult2->id;
		echo "<font class='results' color='white'>All entries (<b>$result2value</b>):</font><br /><br />";
		break;
	default:
		$chosentime=$currenttime-3600;
		$result = mysql_query("SELECT * FROM $thedb.$reglogstable where time>$chosentime ORDER BY id DESC;");
		$result2 = mysql_query("SELECT COUNT(id) as id FROM $thedb.$reglogstable where time>$chosentime;");
		$theresult2 = mysql_fetch_object($result2);
		$result2value = $theresult2->id;
		echo "<font class='results' color='white'><b>$result2value</b> entries for the last hour:</font><br /><br />";
	}
}

if (isset($_GET['action'])) {
	$action=$_GET['action'];
	$result = mysql_query("SELECT * FROM $thedb.$reglogstable where action = '$action' ORDER BY id DESC;");
	$result2 = mysql_query("SELECT COUNT(id) as id FROM $thedb.$reglogstable where action = '$action';");
	$theresult2 = mysql_fetch_object($result2);
	$result2value = $theresult2->id;
	echo "<font class='results' color='white'><b>$result2value</b> entries for '<b>$action</b>':</font>";
}

if (!isset($_GET['nicksearch']) && !isset($_GET['timesearch']) && !isset($_GET['action'])) {
	$chosentime=$currenttime-3600;
	$result = mysql_query("SELECT * FROM $thedb.$reglogstable where time>$chosentime ORDER BY id DESC;");
	$result2 = mysql_query("SELECT COUNT(id) as id FROM $thedb.$reglogstable where time>$chosentime;");
	$theresult2 = mysql_fetch_object($result2);
	$result2value = $theresult2->id;
	echo "<font class='results' color='white'><b>$result2value</b> entries for the last hour:</font><br /><br />";
}

$count=0;
echo "<table class='tb3' border='1' cellpadding='10px'><tr style='color: white;'><th>ID</th><th>Nick</th><th>Time</th><th>Action</th></tr>";
while(list($id, $nick, $time, $action) = mysql_fetch_array($result))
{
	$time=date("m/d/Y h:i:sa",$time);
	include("rowbackground.php");
	echo "<tr style='color: $fontcolor; background: $backgroundcolor;'><td>$count</td><td>$nick</td><td>$time</td><td>$action</td></tr>";
}
echo "</table>";
$mysql->disconnect();
?>
</body>
</html>

Here is a screenshot of the registration logs page:



Use this script to manually import your registration logs from a web GUI:

"manualreglogimport.php"

<?php
/*
Purpose of File:
-Manually import your Registration logs from a web based GUI
-This script functions the same way the reglogimporthourly.php script functions...just with a web based GUI
*/
//See bottom of script for user configuration
include ("mysql.php");
$mysql = new mysql;
$thedb=$mysql->connect();
$reglogstable=$mysql->getRegLogs();
$logfolder=$mysql->getRegLogFolder();
?>
<html>
<head>
<title>Manual Registration Log Import</title>
<link rel="stylesheet" type="text/css" href="default.css">
<style type="text/css">
table.tb
{
	top: 130px;
	left: 40px;
}
font.results
{
	position: absolute;
	color: white;
	top: 180px;
	left: 40px;
}
</style>
</head>
<body>
<h1 class="title">Manual Registration Log Import<br /><a class="admin" href="index.php">Admin Stuff</a></h1>
<table border="0" class="tb">
<tr>
	<form method="GET">
		<td><input type="submit" value="Import Log" name="import"></td>
	</form>
</tr>
</table>
<?php
if (isset($_GET['import'])) {
	if (is_dir($logfolder)){
		chdir($logfolder);
	}
	$begintime=0;
	$before=0;
	$todaytime=time();
	$hournow=date('H',$todaytime);
	if ($hournow=="00"){
		$todaytime=strtotime("yesterday");
		$todaymonth=date('m',$todaytime);
		$todayday=date('d',$todaytime);
		$todayyear=date('Y',$todaytime);
		$begintime=mktime(0,0,0,$todaymonth,$todayday,$todayyear);
	} else {
		$todaymonth=date('m',$todaytime);
		$todayday=date('d',$todaytime);
		$todayyear=date('Y',$todaytime);	
		$begintime=mktime(0,0,0,$todaymonth,$todayday,$todayyear);
	}
	$logfile="RegLog-$todayyear-$todaymonth-$todayday.txt";
	$logfile="$logfolder/$logfile";
	$counter=0;
	if(is_file($logfile)){
		$handle = @fopen($logfile, "r");
		if ($handle) {
			$howmanybefore="select count(id) as idcount FROM $thedb.$reglogstable where time > $begintime";
			$howmanybefore=mysql_query($howmanybefore);
			$howmanybefore = mysql_fetch_object($howmanybefore);
			$before = $howmanybefore->idcount;
			$deleteprev="DELETE FROM $thedb.$reglogstable where time > $begintime;";
			mysql_query($deleteprev);
			while (!feof($handle)) {
				$contentline = fgets($handle, 4096);
				list($time, $secondpart) = explode(" - ", $contentline);
				if($time){
					list($hour, $minute, $second) = explode(":",$time);
					$time=mktime($hour,$minute,$second,$todaymonth,$todayday,$todayyear);
					list($nickname, $action) = explode(" ",$secondpart,2);
					//Start user configuration area
					/*
						Modify case statement below with the names of your profiles
						Add any needed profiles and remove unneeded profiles
					*/
					switch($nickname){
						case "Master": list($nickname, $action) = explode(" ",$action,2); break;
						case "Operator": list($nickname, $action) = explode(" ",$action,2); break;
						case "Reg": list($nickname, $action) = explode(" ",$action,2); break;
						case "TS": list($nickname, $action) = explode(" ",$action,2); break;
						case "NoShare": list($nickname, $action) = explode(" ",$action,2); break;
					}
					//End user configuration area
					$action = substr($action,0,strlen($action)-2);
					$insertquery="INSERT INTO $thedb.$reglogstable (nickname,time,action) values ('$nickname','$time','$action');";
					mysql_query($insertquery);
					$counter+=1;
				}
			}
			fclose($handle);
		}
		echo "<font class='results'><b>$before</b> records deleted before the import for <b>$todaymonth/$todayday/$todayyear</b><br />";
		echo "<b>$counter</b> records imported for <b>$todaymonth/$todayday/$todayyear</b></font>";
		$counter=0;
	} else {
		echo "<font class='results'>No records imported or deleted. Most likely no log file today<br />";
	}
}
$mysql->disconnect();
?>
</body>
</html>

Here is a screenshot of the manual registration logs import page:



Use this script to manually import your Grimore logs from a web GUI:

"manualgrimoireimport.php"

<?php
/*
Purpose of File:
-Manually import your Grimoire logs from a web based GUI
-This script functions the same way the importhouly.php script functions...just with a web based GUI
*/
//Start user configuration area
/*
Modify the $old variable to "yes" if you are using an older version of Grimoire
that logs with the file format "ConnectionLog-2008-10-07.csv"
instead of the new format that logs in the format "UserLog-2008-10-07.csv"
*/
$old="no";
/*
Modify the $dateformat to "european" if you want the date to display in the format
12/04/2008 for April 12th, 2008 or keep the value American for the date to display
in the format 04/12/2008 for April 12th, 2008
*/
$dateformat="american";
//End user configuration area part
include ("mysql.php");
$mysql = new mysql;
$thedb=$mysql->connect();
$connectionlogstable=$mysql->getConnectionLogs();
$importlogtable=$mysql->getImportLogs();
$logfolder=$mysql->getGrimoireLogFolder();
?>
<html>
<head>
<title>Manual Grimoire Log Import</title>
<link rel="stylesheet" type="text/css" href="default.css">
<style type="text/css">
table.tb
{
	top: 130px;
	left: 40px;
}
font.results
{
	position: absolute;
	color: white;
	top: 180px;
	left: 40px;
}
</style>
</head>
<body>
<h1 class="title">Manual Grimoire Log Import<br /><a class="admin" href="index.php">Admin Stuff</a></h1>
<table border="0" class="tb">
<tr>
	<form method="GET">
		<td><input type="submit" value="Import Log" name="import"></td>
	</form>
</tr>
</table>
<?php
$todaydate=time();
$todaymonth=date('m',$todaydate);
$todayday=date('d',$todaydate);
$todayyear=date('Y',$todaydate);
$todaymorningvalue=mktime(0,0,0,$todaymonth,$todayday,$todayyear);
$timestamp = strtotime("today");
$today = date('Y-m-d',$timestamp);
if($old=="no"){
	$thefile = "Userlog-$today.csv";
} else {
	$thefile = "ConnectionLog-$today.csv";
}
$filename = "$logfolder/" . "$thefile";
$sql_delete_prev = "DELETE FROM $thedb.$connectionlogstable WHERE time > '$todaymorningvalue'";
mysql_query($sql_delete_prev) or die("MySQL Error: " . mysql_error());
if (isset($_GET['import'])) {
	CSVImport("$thedb.$connectionlogstable", array('status','date','time','nickname','ip','description','email','version','sharesize','activepassive','speed'), $filename, $thefile, $importlogtable, $dateformat);
}
function CSVImport($table, $fields, $file, $filename, $importlogtable, $dateformat) {
	global $mysql;
    $handle = fopen($file,'r');
    if(!$handle) die('Cannot open uploaded file.');
    $row_count = 0;
    $sql_query = "INSERT INTO $table(". implode(',',$fields) .") VALUES(";
	$rows = array();
    while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
		$counter=1;
        $row_count++;
        foreach($data as $key=>$value) {
			if ($counter==2){
				if($dateformat=="american"){
					list($day, $month, $year) = explode("/", $value);
					$value=$month."/".$day."/".$year;
				}
			}
			if($counter==3){
				$datenow=time();
				$monthtoday=date('m',$datenow);
				$daytoday=date('d',$datenow);
				$yeartoday=date('Y',$datenow);
				list($hour, $minutes, $seconds) = explode(":", $value);
				$value=mktime($hour,$minutes,$seconds,$monthtoday,$daytoday,$yeartoday);
			}
			if($counter==9){
				list($sizevalue, $sizeextension) = explode(" ", $value);
				if($sizeextension=="KiBs"){
					$value=round(($sizevalue*1024),0);
				}
				if($sizeextension=="MiBs"){
					$value=round(($sizevalue*1048576),0);
				}
				if($sizeextension=="GiBs"){
					$value=round(($sizevalue*1073741824),0);
				}
				if($sizeextension=="TiBs"){
					$value=round(($sizevalue*1099511627776),0);
				}
			}
			$counter+=1;
			$data[$key] = "'" . addslashes($value) . "'";
        }
        $rows[] = implode(",",$data);
    }
    $sql_query .= implode("),(", $rows);
    $sql_query .= ")";
    fclose($handle);
	$thedate = date("m/d/Y",time());
	$thetime = date("g:i:sa",time());
    if(count($rows)) {
        mysql_query($sql_query);
        echo "<font class='results'>Successfully imported <b>$row_count</b> record(s) from <b>$filename</b></font>";
		$sql_log = "INSERT INTO $thedb.$importlogtable (date,time,executedfile,status,filenameimported,numrecords,tablename) VALUES('$thedate','$thetime','importhourly.php','success','$filename','$row_count','$table');";
		mysql_query($sql_log);
    } else {
        echo "<font class='results'>Cannot import data - no records found.</font>";
		$sql_log = "INSERT INTO $thedb.$importlogtable (date,time,executedfile,status,filenameimported,numrecords,tablename) VALUES('$thedate','$thetime','importhourly.php','failure','$filename','0','$table');";
		mysql_query($sql_log);
    }
}
$mysql->disconnect();
?>

Here is a screenshot of the manual Grimoire logs import page:



See the next post for more....

[quicky2g]

Back for more...

Use this script to view the nicknames that have the most amount of connects/disconnects to you hub:

"connectdisconnectall.php"

<?php
/*
Purpose of File:
Displays the top usernames with the most connects/disconnects from your hub
*/
//Start user configuration area
/*
Modify the $begin variables to change the starting time that the logs will look at in their queries
For example the accounts considered in this script will be any account logged in the database after
January 1st, 2009 12:00am (Because of the settings below which can be modified)
The hour variable goes from 0 to 23, the minute and second variables from 0 to 59,
the month variable from 1 to 12, the day variable from 1 to 31, and the year variable...pretty obvious
*/
$beginhour="0";
$beginminute="0";
$beginsecond="0";
$beginmonth="1";
$beginday="1";
$beginyear="2009";
$begintime=mktime($beginhour,$beginminute,$beginsecond,$beginmonth,$beginday,$beginyear);
//Modify $howmany variable for however many results you want to see on the page
$howmany="10";
//End user configuration area
include ("mysql.php");
$mysql = new mysql;
$thedb=$mysql->connect();
$connectionlogstable=$mysql->getConnectionLogs();
?>
<html>
<head>
<title>Top Connects/Disconnects by Nickname</title>
<link rel="stylesheet" type="text/css" href="default.css">
<style type="text/css">
table.tb
{
	top: 100px;
	left: 40px;
}
</style>
</head>
<body>
<h1 class="title">Top Connects/Disconnects by Nickname<br /><a class="admin" href="index.php">Admin Stuff</a></h1>
<?php
$result = mysql_query("SELECT count(nickname) as nickcount, nickname FROM $thedb.$connectionlogstable where time > '$begintime' group by nickname order by nickcount desc limit $howmany;");
$count=0;
echo "<table class='tb' border='1' cellpadding='10px'><tr style='color: white;'><th>Rank</th><th>Nickname</th><th>Number of Connects/Disconnects</th></tr>";
while(list($nickcount, $nickname) = mysql_fetch_array($result))
{
	include("rowbackground.php");
	echo "<tr style='color: $fontcolor; background: $backgroundcolor;'><td><center>$count</center></td><td><center>$nickname</center></td><td><center>$nickcount</center></td></tr>";
}
echo "</table>";
$mysql->disconnect();
?>
</body>
</html>

Here is a screenshot of the connect/disconnect page:



Use this script to view the nicknames that have the most amount of connects/disconnects to you hub in the last 24 hours:

"connectdisconnecttoday.php"

<?php
/*
Purpose of File:
Displays the top usernames with the most connects/disconnects from your hub in the last 24 hours
*/
//Start user configuration area
//Modify $howmany variable for however many results you want to see on the page
$howmany="10";
//End user configuration area
include ("mysql.php");
$mysql = new mysql;
$thedb=$mysql->connect();
$connectionlogstable=$mysql->getConnectionLogs();
?>
<html>
<head>
<title>Top Connects/Disconnects by Nickname in Last 24 Hours</title>
<link rel="stylesheet" type="text/css" href="default.css">
<style type="text/css">
table.tb2
{
	top: 110px;
	left: 40px;
}
table.tb2 th
{
	width: 50px;
}
</style>
</head>
<body>
<h1 class="title"><font size="5px">Top Connects/Disconnects by Nickname in Last 24 Hours</font><br /><a class="admin" href="index.php">Admin Stuff</a></h1>
<?php
$currenttime=time();
$neededtime=$currenttime-86400;
$result="SELECT count(status) as connectcount, nickname, ip FROM $thedb.$connectionlogstable where time > '$neededtime' group by nickname order by connectcount desc limit $howmany;";
$result = mysql_query($result);
$count=0;
echo "<table class='tb2' border='1' cellpadding='10px'><tr style='color: white;'><th>ID</th><th># of Connects/Disconnects</th><th>Nickname</th><th>IP Address</th></tr>";
while(list($connectcount, $nickname, $theip) = mysql_fetch_array($result))
{
	include("rowbackground.php");
	echo "<tr style='color: $fontcolor; background: $backgroundcolor;'><td>$count</td><td>$connectcount</td><td>$nickname</td><td>$theip</td></tr>";
}
echo "</table>";
$thedb=$mysql->disconnect();
?>
</body>
</html>

Here is a screenshot of the connect/disconnect page for the last 24 hours:



Use this script to view the client softwares that are connecting to your hub (This is my favorite script):

"versions.php"

<?php
/*
Purpose of File:
Displays info about all the different client softwares that are connecting to your hub
with the ability to see total connects/disconnects for each version,
the total number of nicknames using each client type and each nickname that
is using each client software
*/
//Start user configuration area
/*
Modify the $begin variables to change the starting time that the logs will look at in their queries
For example the accounts and dc++ versions considered in this script will be anything in the database after
January 1st, 2009 12:00am (Because of the settings below which can be modified)
The hour variable goes from 0 to 23, the minute and second variables from 0 to 59,
the month variable from 1 to 12, the day variable from 1 to 31, and the year variable...pretty obvious
*/
$beginhour="0";
$beginminute="0";
$beginsecond="0";
$beginmonth="1";
$beginday="1";
$beginyear="2009";
$begintime=mktime($beginhour,$beginminute,$beginsecond,$beginmonth,$beginday,$beginyear);
/*
Modify $nickperline variable to change the amount of nicknames displayed on each line
when you view the client software and the nicknames using that software
*/
$nickperline="4";
//End user configuration area
include ("mysql.php");
$mysql = new mysql;
$thedb=$mysql->connect();
$connectionlogstable=$mysql->getConnectionLogs();
?>
<html>
<head>
<title>Top Connects/Disconnects by Client Software</title>
<link rel="stylesheet" type="text/css" href="default.css">
<style type="text/css">
table.tb
{
	top: 110px;
	left: 20px;
}
table.tb2
{
	top: 180px;
	left: 40px;
}
</style>
</head>
<body>
<h1 class="title">Top Connects/Disconnects by Client Software<br /><a class="admin" href="index.php">Admin Stuff</a></h1>
<table border="0" class="tb">
<form method="GET">
<tr>
	<td>View Nickname for each Client Software</td><td><input type="submit" value="View" name="seenicks"></td>
</tr>
<tr>
	<td>View without Nicknames for each Client Software</td><td><input type="submit" value="View"></td>
</tr>
</form>
</table>
<?php
$result = mysql_query("SELECT count(nickname) as nickcount, version FROM $thedb.$connectionlogstable where time > '$begintime' group by version order by nickcount desc;");
$count=0;
if (isset($_GET['seenicks'])) {
	echo "<table class='tb2' border='1' cellpadding='10px'><tr style='color: white;'><th>Rank</th><th># of Connects/Disconnects</th><th>Client Software</th><th>Nicknames</th></tr>";
} else {
	echo "<table class='tb2' border='1' cellpadding='10px'><tr style='color: white;'><th>Rank</th><th># of Connects/Disconnects</th><th>Client Software</th></tr>";
}
while(list($nickcount, $version) = mysql_fetch_array($result))
{
	include("rowbackground.php");
	if (isset($_GET['seenicks'])) {
		$nicks="SELECT distinct(version), nickname FROM $thedb.$connectionlogstable where version='$version' and time > '$begintime' order by version;";
		$nicks=mysql_query($nicks);
		$rowcounter=0;
		$tdcounter=0;
		$totalnicks=0;
		while(list($version, $nick) = mysql_fetch_array($nicks)){
			if($rowcounter==0){
				echo "<tr style='color: $fontcolor; background: $backgroundcolor;'><td>$count</td><td>$nickcount</td><td>$version</td><td>$nick";
				$rowcounter+=1;
				$tdcounter+=1;
				$totalnicks+=1;
			} else {
				if($tdcounter<$nickperline){
					echo ", $nick";
					$tdcounter+=1;
					$totalnicks+=1;
				} else {
					echo "<br />$nick";
					$tdcounter=1;
					$totalnicks+=1;
				}
			}
		}
		echo "<br /><b><i>Total: $totalnicks</i></b></td></tr>";
	} else {
		echo "<tr style='color: $fontcolor; background: $backgroundcolor;'><td>$count</td><td>$nickcount</td><td>$version</td></tr>";
	}
}	
echo "</table>";
$mysql->disconnect();
?>
</body>
</html>

Here is a screenshot of the client versions page:



Here is a screenshot of the client versions with the nicknames included:



Please see my next post for more...

[quicky2g]

Not tired of me yet?....here's more......

These next few scripts you the ability to view how many people are connecting to your hub on an average daily basis. The other script will allow you to view how many people are connecting to your hub on an hourly average.

These SQL queries in these scripts were so intensive to run, that I scheduled them to run and only calculate an hour or a day of the week at a time. Otherwise I couldn't even get the queries to complete sometimes. The scripts need to be scheduled to run according to the notes in the scripts. The results will be stored in a table. The web page just displays the table with the final calculations.

Use this script to do the daily calculations:

"byday.php"

<?php
/*
Purpose of File:
-Calculates the average amount of connects/disconnects from your hub
 on a daily schedule based on information from your connectionlogs DB
-Stores the calculated results in your DB for viewing by the viewdayhour.php file
-This script should be scheduled to run at the beginning of the day
 after your importendofday.php file has been run
*/
//Start user configuration area
/*
Modify the $begin variables to change the starting time that the logs will look at in their queries
For example the days considered in this script will be anything in the database after
January 1st, 2009 12:00am (Because of the settings below which can be modified)
The hour variable goes from 0 to 23, the minute and second variables from 0 to 59,
the month variable from 1 to 12, the day variable from 1 to 31, and the year variable...pretty obvious
*/
$beginhour="0";
$beginminute="0";
$beginsecond="0";
$beginmonth="1";
$beginday="1";
$beginyear="2009";
$begintime=mktime($beginhour,$beginminute,$beginsecond,$beginmonth,$beginday,$beginyear);
//End user configuration area
include ("mysql.php");
$mysql = new mysql;
$thedb=$mysql->connect();
$connectionlogstable=$mysql->getConnectionLogs();
$bydaytable=$mysql->getByDay();
/*
The following few lines restricts this script to only run on the server it is located on
If any other computer/ip address tries to access this script, their browser will be redirected to google
*/
$test = "^127.0";
$ip = $_SERVER['REMOTE_ADDR'];
if (!eregi($test,$ip)){
  header( 'Location: http://www.google.com' );
  echo "Please wait...";
  exit(0);
}
function doQuery (&$timestart, &$timeend, &$whichcount, &$whicharray, $db, $table) {
	$query="SELECT count(id) as idcount FROM $db.$table where time > '$timestart' and time < '$timeend';";
	$query=mysql_query($query);
	$query = mysql_fetch_object($query);
	$query = $query->idcount;
	$whicharray[$whichcount]=$query;
	$whichcount+=1;
}
function getAvg ($whichcounter, &$whicharray, &$whichtotal, &$whichaverage) {
	for ($counter=0; $counter<=$whichcounter-1; $counter+=1){
		$whichtotal+=$whicharray[$counter];
	}
	$whichaverage=round(($whichtotal/$whichcounter),0);
}
$yesterday=strtotime("yesterday");
//Uncomment next line to import days manually...comment the line above too
//$yesterday=mktime(0,0,0,1,31,2009);
$yesterdaydayofweek=date('D',$yesterday);
$comparetime=$begintime;
$sincetime=$comparetime;
$endofdaytime=$comparetime+86400;
$daycount=0;
$daytotal=0;
$dayavg=0;
while ($comparetime<=$yesterday){
	$comparedaydayofweek=date('D',$comparetime);
	if ($comparedaydayofweek==$yesterdaydayofweek){
		doQuery($comparetime, $endofdaytime, $daycount, $dayar, $thedb, $connectionlogstable);
		$comparetime+=86400;
		$endofdaytime+=86400;
	} else {
		$comparetime+=86400;
		$endofdaytime+=86400;
	}
}
getAvg($daycount, $dayar, $daytotal, $dayavg);
//Commented lines below will echo out the day arrays if you uncomment the lines
//echo "Day: $yesterdaydayofweek<br />";
//print_r($dayar);
//echo "<br />This is the average:$dayavg<br /><br />";
$query="UPDATE $thedb.$bydaytable SET value='$dayavg' WHERE day='$yesterdaydayofweek';";
mysql_query($query);
$sincetime=date("m/d/Y",$sincetime);
echo "Average of <b>$dayavg</b> records for <b>$yesterdaydayofweek</b> since <b>$sincetime</b>";
$mysql->disconnect();
?>

Use this script to do the hourly calculations:

<?php
/*
Purpose of File:
-Calculates the average amount of connects/disconnects from your hub
 on an hourly schedule based on information from your connectionlogs DB
-Stores the calculated results in your DB for viewing by the viewdayhour.php file
-This script should be scheduled to run each hour of the day
 after your importhourly.php or importendofday.php(Only at 12am) file has been run
*/
//Start user configuration area
/*
Modify the $begin variables to change the starting time that the logs will look at in their queries
For example the hours considered in this script will be anything in the database after
January 1st, 2009 12:00am (Because of the settings below which can be modified)
The hour variable goes from 0 to 23, the minute and second variables from 0 to 59,
the month variable from 1 to 12, the day variable from 1 to 31, and the year variable...pretty obvious
*/
$beginhour="0";
$beginminute="0";
$beginsecond="0";
$beginmonth="1";
$beginday="1";
$beginyear="2009";
$begintime=mktime($beginhour,$beginminute,$beginsecond,$beginmonth,$beginday,$beginyear);
//End user configuration area
/*
The following few lines restricts this script to only run on the server it is located on
If any other computer/ip address tries to access this script, their browser will be redirected to google
*/
$test = "^127.0";
$ip = $_SERVER['REMOTE_ADDR'];
if (!eregi($test,$ip)){
  header( 'Location: http://www.google.com' );
  echo "Please wait...";
  exit(0);
}
include ("mysql.php");
$mysql = new mysql;
$thedb=$mysql->connect();
$connectionlogstable=$mysql->getConnectionLogs();
$byhourtable=$mysql->getByHour();
function doQuery (&$timestart, &$timeend, &$whichcount, &$whicharray, $db, $table) {
	$query="SELECT count(id) as idcount FROM $db.$table where time > '$timestart' and time < '$timeend';";
	$query=mysql_query($query);
	$query = mysql_fetch_object($query);
	$query = $query->idcount;
	$whicharray[$whichcount]=$query;
	$whichcount+=1;
}
function getAvg ($whichcounter, &$whicharray, &$whichtotal, &$whichaverage) {
	for ($counter=0; $counter<=$whichcounter-1; $counter+=1){
		$whichtotal+=$whicharray[$counter];
	}
	$whichaverage=round(($whichtotal/$whichcounter),0);
}
$timenow=time();
$monthtoday=date('m',$timenow);
$daytoday=date('d',$timenow);
$yeartoday=date('Y',$timenow);
$hournow=date('H',$timenow);
if($hournow=="0"){
	$hour="23";
} else {
	$hour=$hournow-1;
}
//uncomment and modify hour variable to a number below to import hours manually
//$hour=23;
$lasthour=mktime($hour,0,0,$monthtoday,$daytoday,$yeartoday);
$comparehour=$hour;
$comparetime=$begintime;
$sincetime=$comparetime;
$endofhourtime=$comparetime + 3600;
$hourcount=0;
$hourtotal=0;
$houravg=0;
while ($comparetime<=$lasthour){
	$comparethehour=date('H',$comparetime);
	if ($comparethehour==$hour){
		doQuery($comparetime, $endofhourtime, $hourcount, $hourar, $thedb, $connectionlogstable);
		$comparetime+=3600;
		$endofhourtime+=3600;
	} else {
		$comparetime+=3600;
		$endofhourtime+=3600;
	}
}
getAvg($hourcount, $hourar, $hourtotal, $houravg);

$query="UPDATE $thedb.$byhourtable SET value='$houravg' WHERE hour='$hour';";
mysql_query($query);
switch ($hour) {
	case "0": $hourprintout="12am - 1am"; break;
	case "1": $hourprintout="1am - 2am"; break;
	case "2": $hourprintout="2am - 3am"; break;
	case "3": $hourprintout="3am - 4am"; break;
	case "4": $hourprintout="4am - 5am"; break;
	case "5": $hourprintout="5am - 6am"; break;
	case "6": $hourprintout="6am - 7am"; break;
	case "7": $hourprintout="7am - 8am"; break;
	case "8": $hourprintout="8am - 9am"; break;
	case "9": $hourprintout="9am - 10am"; break;
	case "10": $hourprintout="10am - 11am"; break;
	case "11": $hourprintout="11am - 12pm"; break;
	case "12": $hourprintout="12pm - 1pm"; break;
	case "13": $hourprintout="1pm - 2pm"; break;
	case "14": $hourprintout="2pm - 3pm"; break;
	case "15": $hourprintout="3pm - 4pm"; break;
	case "16": $hourprintout="4pm - 5pm"; break;
	case "17": $hourprintout="5pm - 6pm"; break;
	case "18": $hourprintout="6pm - 7pm"; break;
	case "19": $hourprintout="7pm - 8pm"; break;
	case "20": $hourprintout="8pm - 9pm"; break;
	case "21": $hourprintout="9pm - 10pm"; break;
	case "22": $hourprintout="10pm - 11pm"; break;
	case "23": $hourprintout="11pm - 12am"; break;
}
//Commented lines below will echo out the day arrays
//echo "Hour: $hourprintout<br />";
//print_r($hourar);
//echo "<br />This is the average: $houravg<br /><br />";
$sincetime=date("m/d/Y",$sincetime);
echo "Average of <b>$houravg</b> records for <b>$hourprintout</b> since <b>$sincetime</b>";
$mysql->disconnect();
?>

Use this script to display the results:

"viewdayhour.php"

<?php
/*
Purpose of File:
-View the results of the byday.php and byhour.php calculations
-Gives info about how many connects/disconnects there are on average for each hour of the day
 as well as the average amount of connects/disconnects for each day of the week
*/
//Start user configuration area
/*
Modify the $begin variables to change the display time on the page
For example the date displayed on this page will be
January 1st, 2009 12:00am (Because of the settings below which can be modified)
The hour variable goes from 0 to 23, the minute and second variables from 0 to 59,
the month variable from 1 to 12, the day variable from 1 to 31, and the year variable...pretty obvious
*/
$beginhour="0";
$beginminute="0";
$beginsecond="0";
$beginmonth="1";
$beginday="1";
$beginyear="2009";
$begintime=mktime($beginhour,$beginminute,$beginsecond,$beginmonth,$beginday,$beginyear);
//End user configuration area
include ("mysql.php");
$mysql = new mysql;
$thedb=$mysql->connect();
$bydaytable=$mysql->getByDay();
$byhourtable=$mysql->getByHour();
?>
<html>
<head>
<link rel="stylesheet" type="text/css" href="default.css">
<style type="text/css">
table.tb
{
	top: 110px;
	left: 20px;
}
table.tb2
{
	top: 180px;
	left: 40px;
}
font.results
{
	top: 200px;
	left: 40px;
}
</style>
<title>Connects/Disconnects by Day and Hour</title>
</head>
<body>
<h1 class="title">Connects/Disconnects by Day and Hour<br /><a class="admin" href="index.php">Admin Stuff</a></h1>
<table border="0" class="tb">
<tr>
	<form method="GET">
		<td><font color="white" style="font-family: Albany,Trebuchet MS">View by Day:</font></td>
		<td><input class="view" type="submit" value="View" name="byday"></td>
	</form>
</tr>
<tr>
	<form method="GET">
		<td><font color="white" style="font-family: Albany,Trebuchet MS">View by Hour:</font></td>
		<td><input class="view" type="submit" value="View" name="byhour"></td>
	</form>
</tr>
</table>
<?php
if (isset($_GET['byday'])) {
	$result="SELECT day, value FROM $thedb.$bydaytable;";
}

if (isset($_GET['byhour'])) {
	$result="SELECT hour, value FROM $thedb.$byhourtable;";
}
if (!isset($_GET['byhour']) && !isset($_GET['byhour'])) {
	$result="SELECT day, value FROM $thedb.$bydaytable;";
}
$result=mysql_query($result);
$count=0;
$sincetime=date("m/d/Y",$begintime);
if (isset($_GET['byday'])) {
	echo "<table class='tb2' border='1' cellpadding='10px'><tr style='color: white;'><th>Day</th><th>Average Number of<br />Connects/Disconnects<br />since $sincetime</th></tr>";
} else if (isset($_GET['byhour'])) {
	echo "<table class='tb2' border='1' cellpadding='10px'><tr style='color: white;'><th>Hour</th><th>Average Number of<br />Connects/Disconnects<br />since $sincetime</th></tr>";
} else {
	echo "<table class='tb2' border='1' cellpadding='10px'><tr style='color: white;'><th>Day</th><th>Average Number of<br />Connects/Disconnects<br />since $sincetime</th></tr>";
}
while(list($identifier, $value) = mysql_fetch_array($result))
{
	if (isset($_GET['byday'])) {
		switch ($identifier) {
			case "Sun": $identifier="Sunday"; break;
			case "Mon": $identifier="Monday"; break;
			case "Tue": $identifier="Tuesday"; break;
			case "Wed": $identifier="Wednesday"; break;
			case "Thu": $identifier="Thursday"; break;
			case "Fri": $identifier="Friday"; break;
			case "Sat": $identifier="Saturday"; break;
		}
	}
	if (isset($_GET['byhour'])) {
		switch ($identifier) {
			case "0": $identifier="12am - 1am"; break;
			case "1": $identifier="1am - 2am"; break;
			case "2": $identifier="2am - 3am"; break;
			case "3": $identifier="3am - 4am"; break;
			case "4": $identifier="4am - 5am"; break;
			case "5": $identifier="5am - 6am"; break;
			case "6": $identifier="6am - 7am"; break;
			case "7": $identifier="7am - 8am"; break;
			case "8": $identifier="8am - 9am"; break;
			case "9": $identifier="9am - 10am"; break;
			case "10": $identifier="10am - 11am"; break;
			case "11": $identifier="11am - 12pm"; break;
			case "12": $identifier="12pm - 1pm"; break;
			case "13": $identifier="1pm - 2pm"; break;
			case "14": $identifier="2pm - 3pm"; break;
			case "15": $identifier="3pm - 4pm"; break;
			case "16": $identifier="4pm - 5pm"; break;
			case "17": $identifier="5pm - 6pm"; break;
			case "18": $identifier="6pm - 7pm"; break;
			case "19": $identifier="7pm - 8pm"; break;
			case "20": $identifier="8pm - 9pm"; break;
			case "21": $identifier="9pm - 10pm"; break;
			case "22": $identifier="10pm - 11pm"; break;
			case "23": $identifier="11pm - 12am"; break;
		}
	}
	include("rowbackground.php");
	echo "<tr style='color: $fontcolor; background: $backgroundcolor;'><td>$identifier</td><td>$value</td></tr>";
}
echo "</table>";
$mysql->disconnect();
?>
</body>
</html>

Here is a screenshot of the byday results:



Here is a screenshot of the byhour results:



More to come soon.......

[quicky2g]

Back again....

Use this script to view the top sharers that have ever connected to your hub:

"topshare.php"

<?php
/*
Purpose of File:
Displays the top sharers on your hub in a web GUI
*/
//Start user configuration area
/*
Modify the $begin variables to change the starting time that the logs will look at in their queries
For example the accounts and dc++ versions considered in this script will be anything in the database after
January 1st, 2009 12:00am (Because of the settings below which can be modified)
The hour variable goes from 0 to 23, the minute and second variables from 0 to 59,
the month variable from 1 to 12, the day variable from 1 to 31, and the year variable...pretty obvious
*/
$beginhour="0";
$beginminute="0";
$beginsecond="0";
$beginmonth="1";
$beginday="1";
$beginyear="2009";
$begintime=mktime($beginhour,$beginminute,$beginsecond,$beginmonth,$beginday,$beginyear);
/*
Modify $nickperline variable to change the amount of nicknames displayed on each line
when you view the client software and the nicknames using that software
*/
//Modify the $howmanytops variable to change how many of the top sharers will be displayed
$howmanytops="20";
//End user configuration area
include ("mysql.php");
$mysql = new mysql;
$thedb=$mysql->connect();
$connectionlogstable=$mysql->getConnectionLogs();
?>
<html>
<head>
<title>Top Shares</title>
<link rel="stylesheet" type="text/css" href="default.css">
<style type="text/css">
table.tb
{
	top: 110px;
	left: 20px;
}
table.tb2
{
	top: 190px;
	left: 40px;
}
</style>
</head>
<body>
<h1 class="title">Top Shares<br /><a class="admin" href="index.php">Admin Stuff</a></h1>
<table border="0" class="tb">
	<tr>
		<form method="GET">
			<td><font color="white" style="font-family: Albany,Trebuchet MS">Nickname:</font></td>
			<td><input style="width: 150px" type="text" name="nicksearch" value="<?php if(isset($_GET['nicksearch'])) {echo $_GET['nicksearch'];}?>"></td>
			<td><input type="submit" value="Search"></td>
		</form>
	</tr>
	<tr>
		<form method="GET">
			<td><font color="white" style="font-family: Albany,Trebuchet MS">View All:</font></td>
			<td><input type="submit" value="View All"></td>
		</form>
	</tr>
</table>
<?php
if (isset($_GET['nicksearch'])) {
	$thenick=$_GET['nicksearch'];
	$query="select nickname, sharesize from $thedb.$connectionlogstable where time > '$begintime' and nickname like '%$thenick%' order by sharesize desc limit 1;";
} else {
	$query="select nickname, max(sharesize) as share from $thedb.$connectionlogstable where time > '$begintime' group by nickname order by max(sharesize) desc limit $howmanytops;";
}
$query=mysql_query($query);
$count=0;
if (isset($_GET['when'])) {
	echo "<table class='tb2' border='1' class='tb' cellpadding='10px'><tr style='color: white;'><th>ID</th><th>Nickname</th><th>Sharesize</th><th>When?</th></tr>";
} else {
	echo "<table class='tb2' border='1' class='tb' cellpadding='10px'><tr style='color: white;'><th>ID</th><th>Nickname</th><th>Sharesize</th></tr>";
}
while(list($nickname, $sharesize) = mysql_fetch_array($query))
{
	if (isset($_GET['when'])) {
		$findtime="SELECT time FROM $thedb.$connectionlogstable where nickname='$nickname' and time > '$begintime' and sharesize='$sharesize' limit 1;";
		$findtime=mysql_query($findtime);
		$findtime = mysql_fetch_object($findtime);
		$findtime = $findtime->time;
		$findtime=date("M d, Y",$findtime);
	}
	if($sharesize<1073741824){
	$sharesize=round(($sharesize/1048576),2);
	$sharesize=$sharesize." Mb";
	} elseif($sharesize>1099511627776) {
		$sharesize=round(($sharesize/1099511627776),2);
		$sharesize=$sharesize." Tb";
	} else {
		$sharesize=round(($sharesize/1073741824),2);
		$sharesize=$sharesize." Gb";
	}
	include("rowbackground.php");
	if (isset($_GET['when'])) {
		echo "<tr style='color: $fontcolor; background: $backgroundcolor;'><td>$count</td><td>$nickname</td><td>$sharesize</td><td>$findtime</td></tr>";
	} else {
		echo "<tr style='color: $fontcolor; background: $backgroundcolor;'><td>$count</td><td>$nickname</td><td>$sharesize</td></tr>";
	}
}
echo "</table>";
$mysql->disconnect();
?>
</body>
</html>

Here is a screenshot of the top sharers page:



Use this script to view nicknames that are using more than one IP address to connect to your hub:

"multip.php"

<?php
/*
Purpose of File:
Displays nicknames that are using the same IP address
*/
//Start user configuration area
/*
Modify the $begin variables to change the starting time that the logs will look at in their queries
For example the accounts and dc++ versions considered in this script will be anything in the database after
January 1st, 2009 12:00am (Because of the settings below which can be modified)
The hour variable goes from 0 to 23, the minute and second variables from 0 to 59,
the month variable from 1 to 12, the day variable from 1 to 31, and the year variable...pretty obvious
*/
$beginhour="0";
$beginminute="0";
$beginsecond="0";
$beginmonth="1";
$beginday="1";
$beginyear="2009";
$begintime=mktime($beginhour,$beginminute,$beginsecond,$beginmonth,$beginday,$beginyear);
//End user configuration area
include ("mysql.php");
$mysql = new mysql;
$thedb=$mysql->connect();
$connectionlogstable=$mysql->getConnectionLogs();
?>
<html>
<head>
<link rel="stylesheet" type="text/css" href="default.css">
<style type="text/css">
font.results
{
	top: 100px;
	left: 40px;
}
table.tb2
{
	top: 140px;
	left: 40px;
}
</style>
<title>Nicknames Using the Same IP</title>
</head>
<body>
<h1 class="title">Nicknames Using the Same IP<br /><a class="admin" href="index.php">Admin Stuff</a></h1>
<?php
$findmultip="SELECT distinct(ip),nickname FROM $thedb.$connectionlogstable where time > '$begintime' order by ip desc;";
$findmultip=mysql_query($findmultip);
echo "<table class='tb2' border='1' cellpadding='10px'><tr style='color: white;'><th>ID</th><th>IP</th><th>Nicknames</th></tr>";
$count=0;
$rowbegin=0;
$previp="";
$prevnick="";
while(list($theip, $nickname) = mysql_fetch_array($findmultip)) {
	if($previp==$theip){
		if($rowbegin==0){
			include("rowbackground.php");
			echo "<tr style='color: $fontcolor; background: $backgroundcolor;'><td>$count</td><td>$theip</td><td>$prevnick";
			$rowbegin=1;
		}
		echo ", $nickname";
	} else {
		if(!$rowbegin==0){
			echo "</td></tr>";
		}
		$rowbegin=0;
	}
	$previp=$theip;
	$prevnick=$nickname;
}
echo "</table>";
echo "<font class='results'>'<b>$count</b>' total IP addresses being shared:</font>";
$mysql->disconnect();
?>
</body>
</html>

Here is a screenshot of the multiple IP page:



This next script is more of a troubleshooting utility for PHP but you might find it useful in your DB queries:

"time.php"

<?php
/*
Purpose of File:
Does PHP time conversions in a web based GUI
*/
//Start user configuration area
//Modify the $fontcol variable to whatever color you want the font color on the page to be
$fontcol="white";
//End user configuration area part
?>
<html>
<head>
<link rel='stylesheet' type='text/css' href='default.css'>
<title>PHP Time Conversions</title>
<style type="text/css">
table.tb
{
	padding: 5px;
	top: 100px;
	left: 20px;
}
table.tb2
{
	padding: 5px;
	top: 220px;
	left: 20px;
}
table.tb2 td
{
	text-align: left;
}
font.results
{
	top: 330px;
	left: 40px;
}
</style>
</head>
<body bgcolor="white">
<h1 class="title">PHP Time Conversions<br /><a class="admin" href="index.php">Admin Stuff</a></h1>
<table border="0" class="tb">
<form method="GET">
<tr><td colspan="6"><font color="<?php echo $fontcol; ?>" style="font-family: Albany,Trebuchet MS"><b><i>Calculate PHP time from a date</i></b></font></td></tr>
<tr>
		<td><font color="<?php echo $fontcol; ?>" style="font-family: Albany,Trebuchet MS">Month:</font></td>
		<td>
			<select name="month" style="width: 80px;">
			<?php
			for($count=2; $count<=13; $count++) {
				$month=mktime(0,0,0,$count,0,0);
				$month=date("M",$month);
				$count-=1;
				echo "<option value='" . $count . "'>" . $month . "</option>";
				$count+=1;
			}
			?>
			</select>
		</td>
		<td><font color="<?php echo $fontcol; ?>" style="font-family: Albany,Trebuchet MS">Day:</font></td>
		<td>
			<select name="day" style="width: 80px;">
			<?php
			$count=1;
			while($count<=31){
				echo "<option value='" . $count . "'>" . $count . "</option>";
				$count++;
			}
			?>
			</select>
		</td>
		<td><font color="<?php echo $fontcol; ?>" style="font-family: Albany,Trebuchet MS">Year:</font></td>
		<td>
			<select name="year" style="width: 80px;">
			<?php
			$year=date("Y",time());
			$count=1969;
			while($year>=1969){
				echo "<option value='" . $year . "'>" . $year . "</option>";
				$year--;
			}
			?>
			</select>
		</td>
</tr>
<tr>
		<td><font color="<?php echo $fontcol; ?>" style="font-family: Albany,Trebuchet MS">Hour:</font></td>
		<td>
			<select name="hour" style="width: 80px;">
			<?php
			for($count=0; $count<=23; $count++) {
				$hour=mktime($count,0,0,0,0,0);
				$hour=date("ga",$hour);
				echo "<option value='" . $count . "'>" . $hour . "</option>";
			}
			?>
			</select>
		</td>
		<td><font color="<?php echo $fontcol; ?>" style="font-family: Albany,Trebuchet MS">Minute:</font></td>
		<td>
			<select name="minute" style="width: 80px;">
			<?php
			$count=0;
			while($count<=59){
				echo "<option value='" . $count . "'>" . $count . "</option>";
				$count++;
			}
			?>
			</select>
		</td>
		<td><font color="<?php echo $fontcol; ?>" style="font-family: Albany,Trebuchet MS">Second:</font></td>
		<td>
			<select name="second" style="width: 80px;">
			<?php
			$count=0;
			while($count<=59){
				echo "<option value='" . $count . "'>" . $count . "</option>";
				$count++;
			}
			?>
			</select>
		</td>
</tr>
<tr>
	<td colspan="6"><input class="view" type="submit" value="Calculate Time" name="calc1"></td>
</tr>
</form>
</table>
<table border="0" class="tb2">
<form class="calc2" method="GET">
<tr><td colspan="6"><font color="<?php echo $fontcol; ?>" style="font-family: Albany,Trebuchet MS"><b><i>Calculate a date from PHP time</i></b></font></td></tr>
<tr>
	<td colspan="6"><input style="width: 150px" type="text" name="timecalc" value="<?php if(isset($_GET['timecalc'])) {echo $_GET['timecalc'];}?>"></td>
</tr>
<tr>
	<td colspan="6"><input class="view" type="submit" value="Calculate Date" name="calc2"></td>
</tr>
</form>
</table>
</body>
</html>
<?php
if (isset($_GET['calc1'])) {
	$hour=$_GET['hour'];
	$minute=$_GET['minute'];
	$second=$_GET['second'];
	$month=$_GET['month'];
	$day=$_GET['day'];
	$year=$_GET['year'];
	$value=mktime($hour,$minute,$second,$month,$day,$year);
	echo "<font class='results'>The PHP time for <b>" . date("D",$value) . " " . date("M d, Y g:i:sa",$value) . "</b> is <b>$value</b></font>";
}
if (isset($_GET['calc2'])) {
	$timecalc=$_GET['timecalc'];
	echo "<font class='results'>The date for <b>$timecalc</b> is: <b>" . date("D",$timecalc) . " " . date("M d, Y g:i:sa",$timecalc) . "</b></font>";
}
?>

Here is a screenshot of the PHP time script:



Here is another screenshot of the PHP time script:



This final script cleans up the importlog. Importing the Grimoire logs is so important for all these scripts, that part of the import script logs the success or failure of the import script. Each time hourly script runs it logs its success or failure which could generate alot of entries over a long period of time. The only logging that really matters is the importendofday.php script which imports the entire previous day once the log file is closed and no longer used. So, this script removes all successful entries of the importhourly.php script from the importlog table. Schedule this script to run once a day after the importendofday.php script.

"importlogdeleteold.php"

<?php
/*
Purpose of File:
-Prunes old log entries from the importlog table
-If this script does not run, your importlog table will keep a record of
 every single hour that the importhourly.php script runs...
 which would be 8,760 entries per year...kinda useless
-This script will delete the previous days entries for the importhourly.php script
 Any import failures will be left in the DB and the importendofday.php logging is also left untouched
*/
//No user configuration needed
/*
The following few lines restricts this script to only run on the server it is located on
If any other computer/ip address tries to access this script, their browser will be redirected to google
*/
include ("mysql.php");
$mysql = new mysql;
$thedb=$mysql->connect();
$importlogtable=$mysql->getImportLogs();
$test = "^127.0";
$ip = $_SERVER['REMOTE_ADDR'];
if (!eregi($test,$ip)){
  header( 'Location: http://www.google.com' );
  echo "Please wait...";
  exit(0);
}

$timestamp = strtotime("yesterday");
$yesterday = date('m/d/Y',$timestamp);
$sqlcountbefore = "select count(id) FROM $thedb.$importlogtable where date='$yesterday';";
$result = mysql_query($sqlcountbefore);
$howmanybefore = mysql_fetch_array($result);

$sqldelete = "delete FROM $thedb.$importlogtable where date='$yesterday' and executedfile='importhourly.php' and status='success';";
mysql_query($sqldelete);

$sqlcountafter = "select count(id) FROM $thedb.$importlogtable where date='$yesterday';";
$result2 = mysql_query($sqlcountafter);
$howmanyafter = mysql_fetch_array($result2);

echo "$howmanybefore[0] record(s) before and $howmanyafter[0] record(s) after for the date: $yesterday";
?>

On another note...I still use the PHPRunner package to view the connectionlogs DB. If anyone still wants to use that, you will need to do a conversion for the time and sharesize fields because they were converted in the import script. In the visual editor, right click on the field, go to properties, then make the field a custom one. For the sharesize field use this code:

if($value<1073741824){
	$value=round(($value/1048576),2);
	$value=$value." Mb";
} elseif($value>1099511627776) {
	$value=round(($value/1099511627776),2);
	$value=$value." Tb";
} else {
	$value=round(($value/1073741824),2);
	$value=$value." Gb";
}

For the time field use this code:

$value=date("h:i:sa",$value);

Well that's it from me for now...Any questions are welcome.  ;D

[Arcanum]

quicky2g  nice CMS fot PtokaX! one wish: can you puck it all in one zip file and attach it here? it's not convenient to leave the sourse in topics...
second wish: can you write a module for Joomla to use your project in that CMS?

[quicky2g]

Attached is the zip file. I have never used Joomla but I will take a look into it. It may take me a little bit to get around to it depending on how complex it is.

[Arcanum]

thank you for attach!
very nice... but where can I write a way to xml file?
can you write full ReadMe for dummies & insert it to attach?

[quicky2g]

Which xml file do you need access to? All of the PHP files have comments on what they do plus the posts explain things pretty well but I guess I could do a readme.

[Arcanum]

for every file that need access ))) how is going your readme&
sorry for my english...