#!/usr/local/bin/php
<?php

/* Connect to MySQL-database */
mysql_connect("localhost:3360", "user", "password");
mysql_select_db("phpmailstats");

/* What time and date is it? */
$date = date("Y-m-d G:i");
$minute = date("i");
$hour = date("G");
$dayoftheweek = date("w");
$dayofthemonth = date("d");
$month = date("n");
$dayoftheyear = date("z");
$year = date("Y");

$oneminago = mktime($hour,$minute,0,$month,$dayofthemonth,$year);
$oneminago -= 60;

$olddate = date("Y-m-d G:i", $oneminago);
$oldminute = date("i", $oneminago);
$oldhour = date("G", $oneminago);
$olddayoftheweek = date("w", $oneminago);
$olddayofthemonth = date("d", $oneminago);
$oldmonth = date("n", $oneminago);
$olddayoftheyear = date("z", $oneminago);
$oldyear = date("Y", $oneminago);

/* Collect statistics for local e-mail */
$tmp = exec("/usr/sbin/mailstats | /usr/bin/grep local");
while (strstr($tmp, "  "))
	$tmp = str_replace("  ", " ", $tmp);
$statdata = explode(" ", $tmp);
$msgsfr_local = $statdata[2];
$bytes_from_local = str_replace("K", "", $statdata[3]);
$msgsto_local = $statdata[4];
$bytes_to_local = str_replace("K", "", $statdata[5]);
$msgsrej_local = $statdata[6];

/* Collect statistics for external e-mail */
$tmp = exec("/usr/sbin/mailstats | /usr/bin/grep esmtp");
while (strstr($tmp, "  "))
	$tmp = str_replace("  ", " ", $tmp);
$statdata = explode(" ", $tmp);
$msgsfr_relay = $statdata[2];
$bytes_from_relay = str_replace("K", "", $statdata[3]);
$msgsto_relay = $statdata[4];
$bytes_to_relay = str_replace("K", "", $statdata[5]);
$msgsrej_relay = $statdata[6];

/* Check so all tables exists, if not; create them. */
// Tables for local e-mail:
mysql_query("CREATE TABLE smtpstat_tot_local (msgsfr bigint(20) unsigned DEFAULT '0' NOT NULL, bytes_from bigint(20) unsigned DEFAULT '0' NOT NULL, msgsto bigint(20) unsigned DEFAULT '0' NOT NULL, bytes_to bigint(20) unsigned DEFAULT '0' NOT NULL, msgsrej bigint(20) unsigned DEFAULT '0' NOT NULL)");
mysql_query("CREATE TABLE smtpstat_history_local (year smallint(4) unsigned DEFAULT '0' NOT NULL, msgsfr bigint(20) unsigned DEFAULT '0' NOT NULL, bytes_from bigint(20) unsigned DEFAULT '0' NOT NULL, msgsto bigint(20) unsigned DEFAULT '0' NOT NULL, bytes_to bigint(20) unsigned DEFAULT '0' NOT NULL, msgsrej bigint(20) unsigned DEFAULT '0' NOT NULL, PRIMARY KEY (year))");
mysql_query("CREATE TABLE smtpstat_yearly_local (month tinyint(2) unsigned DEFAULT '0' NOT NULL, msgsfr bigint(20) unsigned DEFAULT '0' NOT NULL, bytes_from bigint(20) unsigned DEFAULT '0' NOT NULL, msgsto bigint(20) unsigned DEFAULT '0' NOT NULL, bytes_to bigint(20) unsigned DEFAULT '0' NOT NULL, msgsrej bigint(20) unsigned DEFAULT '0' NOT NULL, PRIMARY KEY (month))");
mysql_query("CREATE TABLE smtpstat_monthly_local (day tinyint(2) unsigned DEFAULT '0' NOT NULL, msgsfr bigint(20) unsigned DEFAULT '0' NOT NULL, bytes_from bigint(20) unsigned DEFAULT '0' NOT NULL, msgsto bigint(20) unsigned DEFAULT '0' NOT NULL, bytes_to bigint(20) unsigned DEFAULT '0' NOT NULL, msgsrej bigint(20) unsigned DEFAULT '0' NOT NULL, PRIMARY KEY (day))");
mysql_query("CREATE TABLE smtpstat_weekly_local (day tinyint(1) unsigned DEFAULT '0' NOT NULL, msgsfr bigint(20) unsigned DEFAULT '0' NOT NULL, bytes_from bigint(20) unsigned DEFAULT '0' NOT NULL, msgsto bigint(20) unsigned DEFAULT '0' NOT NULL, bytes_to bigint(20) unsigned DEFAULT '0' NOT NULL, msgsrej bigint(20) unsigned DEFAULT '0' NOT NULL,  PRIMARY KEY (day))");
mysql_query("CREATE TABLE smtpstat_daily_local (hour tinyint(2) unsigned DEFAULT '0' NOT NULL, msgsfr bigint(20) unsigned DEFAULT '0' NOT NULL, bytes_from bigint(20) unsigned DEFAULT '0' NOT NULL, msgsto bigint(20) unsigned DEFAULT '0' NOT NULL, bytes_to bigint(20) unsigned DEFAULT '0' NOT NULL, msgsrej bigint(20) unsigned DEFAULT '0' NOT NULL, PRIMARY KEY (hour))");
// Tables for external e-mail:
mysql_query("CREATE TABLE smtpstat_tot_relay (msgsfr bigint(20) unsigned DEFAULT '0' NOT NULL, bytes_from bigint(20) unsigned DEFAULT '0' NOT NULL, msgsto bigint(20) unsigned DEFAULT '0' NOT NULL, bytes_to bigint(20) unsigned DEFAULT '0' NOT NULL, msgsrej bigint(20) unsigned DEFAULT '0' NOT NULL)");
mysql_query("CREATE TABLE smtpstat_history_relay (year smallint(4) unsigned DEFAULT '0' NOT NULL, msgsfr bigint(20) unsigned DEFAULT '0' NOT NULL, bytes_from bigint(20) unsigned DEFAULT '0' NOT NULL, msgsto bigint(20) unsigned DEFAULT '0' NOT NULL, bytes_to bigint(20) unsigned DEFAULT '0' NOT NULL, msgsrej bigint(20) unsigned DEFAULT '0' NOT NULL, PRIMARY KEY (year))");
mysql_query("CREATE TABLE smtpstat_yearly_relay (month tinyint(2) unsigned DEFAULT '0' NOT NULL, msgsfr bigint(20) unsigned DEFAULT '0' NOT NULL, bytes_from bigint(20) unsigned DEFAULT '0' NOT NULL, msgsto bigint(20) unsigned DEFAULT '0' NOT NULL, bytes_to bigint(20) unsigned DEFAULT '0' NOT NULL, msgsrej bigint(20) unsigned DEFAULT '0' NOT NULL, PRIMARY KEY (month))");
mysql_query("CREATE TABLE smtpstat_monthly_relay (day tinyint(2) unsigned DEFAULT '0' NOT NULL, msgsfr bigint(20) unsigned DEFAULT '0' NOT NULL, bytes_from bigint(20) unsigned DEFAULT '0' NOT NULL, msgsto bigint(20) unsigned DEFAULT '0' NOT NULL, bytes_to bigint(20) unsigned DEFAULT '0' NOT NULL, msgsrej bigint(20) unsigned DEFAULT '0' NOT NULL, PRIMARY KEY (day))");
mysql_query("CREATE TABLE smtpstat_weekly_relay (day tinyint(1) unsigned DEFAULT '0' NOT NULL, msgsfr bigint(20) unsigned DEFAULT '0' NOT NULL, bytes_from bigint(20) unsigned DEFAULT '0' NOT NULL, msgsto bigint(20) unsigned DEFAULT '0' NOT NULL, bytes_to bigint(20) unsigned DEFAULT '0' NOT NULL, msgsrej bigint(20) unsigned DEFAULT '0' NOT NULL,  PRIMARY KEY (day))");
mysql_query("CREATE TABLE smtpstat_daily_relay (hour tinyint(2) unsigned DEFAULT '0' NOT NULL, msgsfr bigint(20) unsigned DEFAULT '0' NOT NULL, bytes_from bigint(20) unsigned DEFAULT '0' NOT NULL, msgsto bigint(20) unsigned DEFAULT '0' NOT NULL, bytes_to bigint(20) unsigned DEFAULT '0' NOT NULL, msgsrej bigint(20) unsigned DEFAULT '0' NOT NULL, PRIMARY KEY (hour))");
// Delete unused days in the database
if ($month == 2 || $month == 4 || $month == 6 || $month == 9 || $month == 11) {
	mysql_query("DELETE FROM smtpstat_monthly_local WHERE day = 30");
	mysql_query("DELETE FROM smtpstat_monthly_relay WHERE day = 30");
	if ($month == 2 && !date("L")) {
		mysql_query("DELETE FROM smtpstat_monthly_local WHERE day = 29");
		mysql_query("DELETE FROM smtpstat_monthly_relay WHERE day = 29");
	}
}


/* Update dayily data in smtpstat_daily and add data to smtpstat_*_tot. Also reset /etc/sendmail.st. */
if ($minute == 0) {
	mysql_query("DELETE FROM smtpstat_daily_local WHERE hour = $oldhour");
	mysql_query("INSERT INTO smtpstat_daily_local VALUES ($oldhour, $msgsfr_local, $bytes_from_local, $msgsto_local, $bytes_to_local, $msgsrej_local)");
	mysql_query("DELETE FROM smtpstat_daily_relay WHERE hour = $oldhour");
	mysql_query("INSERT INTO smtpstat_daily_relay VALUES ($oldhour, $msgsfr_relay, $bytes_from_relay, $msgsto_relay, $bytes_to_relay, $msgsrej_relay)");
	if (mysql_numrows(mysql_query("SELECT * FROM smtpstat_tot_local")) == 0) {
		mysql_query("INSERT INTO smtpstat_tot_local VALUES (0,0,0,0,0)");
		mysql_query("UPDATE smtpstat_daily_local SET msgsfr=0,bytes_from=0,msgsto=0,bytes_to=0,msgsrej=0 WHERE hour=$oldhour");
	}
	if (mysql_numrows(mysql_query("SELECT * FROM smtpstat_tot_relay")) == 0) {
		mysql_query("INSERT INTO smtpstat_tot_relay VALUES (0,0,0,0,0)");
		mysql_query("UPDATE smtpstat_daily_relay SET msgsfr=0,bytes_from=0,msgsto=0,bytes_to=0,msgsrej=0 WHERE hour=$oldhour");
	}
	mysql_query("UPDATE smtpstat_tot_local SET msgsfr=msgsfr+$msgsfr_local, bytes_from=bytes_from+$bytes_from_local, msgsto=msgsto+$msgsto_local, bytes_to=bytes_to+$bytes_to_local, msgsrej=msgsrej+$msgsrej_local");
	mysql_query("UPDATE smtpstat_tot_relay SET msgsfr=msgsfr+$msgsfr_relay, bytes_from=bytes_from+$bytes_from_relay, msgsto=msgsto+$msgsto_relay, bytes_to=bytes_to+$bytes_to_relay, msgsrej=msgsrej+$msgsrej_relay");
	exec("/bin/echo > /etc/sendmail.st");
} else {
	print "Error! Read the INSTALL file included in the tarball.\n";
	exit;
}

$msgsfr_local = 0; $bytes_from_local = 0; $msgsto_local = 0; $bytes_to_local = 0; $msgsrej_local = 0;
$msgsfr_relay = 0; $bytes_from_relay = 0; $msgsto_relay = 0; $bytes_to_relay = 0; $msgsrej_relay = 0;

/* Save statistics of the previous year */
if ($dayoftheyear == 1 && $hour == 0 && $minute == 0) {
	$howmany =  mysql_numrows(mysql_query("SELECT * FROM smtpstat_yearly_local"));
	for ($i = 0; $i <= $howmany; $i++) {
		$sqlres = mysql_query("SELECT * FROM smtpstat_yearly_local WHERE month=$i");
		$msgsfr_local += mysql_result($sqlres, 0, "msgsfr");
		$bytes_from_local += mysql_result($sqlres, 0, "bytes_from");
		$msgsto_local += mysql_result($sqlres, 0, "msgsto");
		$bytes_to_local += mysql_result($sqlres, 0, "bytes_to");
		$msgsrej_local += mysql_result($sqlres, 0, "msgsrej");
	}
	mysql_query("INSERT INTO smtpstat_history VALUES ($oldyear, $msgsfr_local, $bytes_from_local, $msgsto_local, $bytes_to_local, $msgsrej_local)");
	$howmany =  mysql_numrows(mysql_query("SELECT * FROM smtpstat_yearly_relay"));
	for ($i = 0; $i <= $howmany; $i++) {
		$sqlres = mysql_query("SELECT * FROM smtpstat_yearly_relay WHERE month=$i");
		$msgsfr_relay += mysql_result($sqlres, 0, "msgsfr");
		$bytes_from_relay += mysql_result($sqlres, 0, "bytes_from");
		$msgsto_relay += mysql_result($sqlres, 0, "msgsto");
		$bytes_to_relay += mysql_result($sqlres, 0, "bytes_to");
		$msgsrej_relay += mysql_result($sqlres, 0, "msgsrej");
	}
	mysql_query("INSERT INTO smtpstat_history VALUES ($oldyear, $msgsfr_relay, $bytes_from_relay, $msgsto_relay, $bytes_to_relay, $msgsrej_relay)");
}

$msgsfr_local = 0; $bytes_from_local = 0; $msgsto_local = 0; $bytes_to_local = 0; $msgsrej_local = 0;
$msgsfr_relay = 0; $bytes_from_relay = 0; $msgsto_relay = 0; $bytes_to_relay = 0; $msgsrej_relay = 0;

/* Save statistics of the previous month */
if ($dayofthemonth == 1 && $hour == 0 && $minute == 0) {
	$howmany =  mysql_numrows(mysql_query("SELECT * FROM smtpstat_monthly_local"));
	for ($i = 0; $i <= $howmany; $i++) {
		$sqlres = mysql_query("SELECT * FROM smtpstat_monthly_local WHERE day=$i");
		$msgsfr_local += mysql_result($sqlres, 0, "msgsfr");
		$bytes_from_local += mysql_result($sqlres, 0, "bytes_from");
		$msgsto_local += mysql_result($sqlres, 0, "msgsto");
		$bytes_to_local += mysql_result($sqlres, 0, "bytes_to");
		$msgsrej_local += mysql_result($sqlres, 0, "msgsrej");
	}
	mysql_query("DELETE FROM smtpstat_yearly_local WHERE month = $oldmonth");
	mysql_query("INSERT INTO smtpstat_yearly_local VALUES ($oldmonth, $msgsfr_local, $bytes_from_local, $msgsto_local, $bytes_to_local, $msgsrej_local)");
	$howmany =  mysql_numrows(mysql_query("SELECT * FROM smtpstat_monthly_relay"));
	for ($i = 0; $i <= $howmany; $i++) {
		$sqlres = mysql_query("SELECT * FROM smtpstat_monthly_relay WHERE day=$i");
		$msgsfr_relay += mysql_result($sqlres, 0, "msgsfr");
		$bytes_from_relay += mysql_result($sqlres, 0, "bytes_from");
		$msgsto_relay += mysql_result($sqlres, 0, "msgsto");
		$bytes_to_relay += mysql_result($sqlres, 0, "bytes_to");
		$msgsrej_relay += mysql_result($sqlres, 0, "msgsrej");
	}
	mysql_query("DELETE FROM smtpstat_yearly_relay WHERE month = $oldmonth");
	mysql_query("INSERT INTO smtpstat_yearly_relay VALUES ($oldmonth, $msgsfr_relay, $bytes_from_relay, $msgsto_relay, $bytes_to_relay, $msgsrej_relay)");
}

$msgsfr_local = 0; $bytes_from_local = 0; $msgsto_local = 0; $bytes_to_local = 0; $msgsrej_local = 0;
$msgsfr_relay = 0; $bytes_from_relay = 0; $msgsto_relay = 0; $bytes_to_relay = 0; $msgsrej_relay = 0;

/* Save statistics of the previous day */
if ($hour == 0 && $minute == 0) {
	$howmany =  mysql_numrows(mysql_query("SELECT * FROM smtpstat_daily_local"));
	for ($i = 0; $i <= $howmany; $i++) {
		$sqlres = mysql_query("SELECT * FROM smtpstat_daily_local WHERE hour=$i");
		$msgsfr_local += mysql_result($sqlres, 0, "msgsfr");
		$bytes_from_local += mysql_result($sqlres, 0, "bytes_from");
		$msgsto_local += mysql_result($sqlres, 0, "msgsto");
		$bytes_to_local += mysql_result($sqlres, 0, "bytes_to");
		$msgsrej_local += mysql_result($sqlres, 0, "msgsrej");
	}
	mysql_query("DELETE FROM smtpstat_weekly_local WHERE day = $olddayoftheweek");
	mysql_query("INSERT INTO smtpstat_weekly_local VALUES ($olddayoftheweek, $msgsfr_local, $bytes_from_local, $msgsto_local, $bytes_to_local, $msgsrej_local)");
	mysql_query("DELETE FROM smtpstat_monthly_local WHERE day = $olddayofthemonth");
	mysql_query("INSERT INTO smtpstat_monthly_local VALUES ($olddayofthemonth, $msgsfr_local, $bytes_from_local, $msgsto_local, $bytes_to_local, $msgsrej_local)");
	$howmany =  mysql_numrows(mysql_query("SELECT * FROM smtpstat_daily_relay"));
	for ($i = 0; $i <= $howmany; $i++) {
		$sqlres = mysql_query("SELECT * FROM smtpstat_daily_relay WHERE hour=$i");
		$msgsfr_relay += mysql_result($sqlres, 0, "msgsfr");
		$bytes_from_relay += mysql_result($sqlres, 0, "bytes_from");
		$msgsto_relay += mysql_result($sqlres, 0, "msgsto");
		$bytes_to_relay += mysql_result($sqlres, 0, "bytes_to");
		$msgsrej_relay += mysql_result($sqlres, 0, "msgsrej");
	}
	mysql_query("DELETE FROM smtpstat_weekly_relay WHERE day = $olddayoftheweek");
	mysql_query("INSERT INTO smtpstat_weekly_relay VALUES ($olddayoftheweek, $msgsfr_relay, $bytes_from_relay, $msgsto_relay, $bytes_to_relay, $msgsrej_relay)");
	mysql_query("DELETE FROM smtpstat_monthly_relay WHERE day = $olddayofthemonth");
	mysql_query("INSERT INTO smtpstat_monthly_relay VALUES ($olddayofthemonth, $msgsfr_relay, $bytes_from_relay, $msgsto_relay, $bytes_to_relay, $msgsrej_relay)");
}

?>
