function build()
{
if (!function_exists("IsPhysicalAddress")) {
include_once dirname(__FILE__) . "/ressources/class.templates.inc";
}
$unix = new unix();
$file_duration = "/etc/squid3/squid.durations.ini";
$file_quotas_day = "/etc/squid3/squid.quotasD.ini";
$file_quotas_hour = "/etc/squid3/squid.quotasH.ini";
$php5 = $unix->LOCATE_PHP5_BIN();
$nohup = $unix->find_program("nohup");
$sql = "SELECT * FROM webfilters_quotas";
$q = new mysql_squid_builder();
$results = $q->QUERY_SQL($sql);
$array = array();
while ($ligne = mysql_fetch_assoc($results)) {
$duration = $ligne["duration"];
$xtype = $ligne["xtype"];
$value = $ligne["value"];
$array[$duration][$xtype][$value] = $ligne["maxquota"] * 1024 * 1000;
if ($GLOBALS["VERBOSE"]) {
echo "duration[{$duration}]: {$xtype} ({$value}) = {$array[$duration][$xtype][$value]} (bytes)\n";
}
}
if (count($array) == 0) {
@unlink($file_duration);
} else {
@file_put_contents($file_duration, serialize($array));
}
$table = "UserSizeD_" . date("Ymd");
if (!$q->TABLE_EXISTS($table)) {
$q->CreateUserSizeRTT_day($table);
shell_exec("{$nohup} {$php5} /usr/share/artica-postfix/exec.squid.php --build-schedules >/dev/null 2>&1 &");
}
$sql = "SELECT uid,ipaddr,hostname,account,MAC,SUM(size) as size FROM `{$table}` GROUP BY uid,ipaddr,hostname,account,MAC";
$results = $q->QUERY_SQL($sql);
$array = array();
while ($ligne = mysql_fetch_assoc($results)) {
$array["ipaddr"][$ligne["ipaddr"]] = $ligne["size"];
$array["uid"][$ligne["uid"]] = $ligne["size"];
$array["hostname"][$ligne["hostname"]] = $ligne["size"];
$array["MAC"][$ligne["MAC"]] = $ligne["size"];
if ($GLOBALS["VERBOSE"]) {
$sizeM = $ligne["size"] / 1024 / 1000;
echo date("l d") . ": {$ligne["MAC"]},{$ligne["uid"]},{$ligne["ipaddr"]} = {$ligne["size"]} ({$sizeM} M)\n";
}
}
@file_put_contents($file_quotas_day, serialize($array));
$array = array();
$sql = "SELECT DAY(zDate) as tday,HOUR(zDate) as thour,uid,ipaddr,hostname,account,MAC,SUM(size) as size FROM `UserSizeRTT` \nGROUP BY uid,ipaddr,hostname,account,MAC,tday,thour HAVING tday=DAY(NOW()) AND thour=HOUR(NOW())";
$results = $q->QUERY_SQL($sql);
$array = array();
while ($ligne = mysql_fetch_assoc($results)) {
if ($GLOBALS["VERBOSE"]) {
$sizeM = $ligne["size"] / 1024 / 1000;
echo "{$ligne["thour"]}h: {$ligne["MAC"]},{$ligne["uid"]},{$ligne["ipaddr"]} = {$ligne["size"]} ({$sizeM} M)\n";
}
$array["ipaddr"][$ligne["ipaddr"]] = $ligne["size"];
$array["uid"][$ligne["uid"]] = $ligne["size"];
$array["hostname"][$ligne["hostname"]] = $ligne["size"];
$array["MAC"][$ligne["MAC"]] = $ligne["size"];
}
@file_put_contents($file_quotas_hour, serialize($array));
MacToUid();
}
function UserSizeRTT_oldfiles()
{
if (!($handle = opendir("/var/log/artica-postfix/squid-RTTSize"))) {
ufdbguard_admin_events("Fatal: /var/log/artica-postfix/squid-RTTSize no such directory", __FUNCTION__, __FILE__, __LINE__, "stats");
return;
}
$q = new mysql_squid_builder();
$classParse = new squid_tail();
$CurrentFile = date("YmdH");
if ($GLOBALS["VERBOSE"]) {
echo __FUNCTION__ . ":: scanning /var/log/artica-postfix/squid-RTTSize\n";
}
while (false !== ($filename = readdir($handle))) {
if ($filename == ".") {
continue;
}
if ($filename == "..") {
continue;
}
if ($filename == $CurrentFile) {
continue;
}
$targetFile = "/var/log/artica-postfix/squid-RTTSize/{$filename}";
if ($GLOBALS["VERBOSE"]) {
echo __FUNCTION__ . ":: {$targetFile}\n";
}
$time = filemtime($targetFile);
$tablesuffix = date("Ymd", $time);
$tablename = "UserSizeD_{$tablesuffix}";
if (!$q->CreateUserSizeRTT_day($tablename)) {
ufdbguard_admin_events("{$tablename}: Query failed {$q->mysql_error}", __FUNCTION__, __FILE__, __LINE__, "stats");
return;
}
$RTTSIZEARRAY = unserialize(@file_get_contents($targetFile));
$Hour = date('H', $time);
$date = date("Y-m-d H:00:00", $time);
//$sql="INSERT IGNORE INTO `$tablename` (`zMD5`,`uid`,`zdate`,
//`ipaddr`,`hostname`,`account`,`MAC`,`UserAgent`,`size`,`hits`,`hour`) VALUES ".@implode(",", $rows);
if (count($RTTSIZEARRAY["UID"]) > 0) {
$f = array();
$prefix = "INSERT IGNORE INTO `{$tablename}` (`zMD5`,`zdate`,`uid`,`size`,`hits`,`hour`) VALUES ";
while (list($username, $array) = each($RTTSIZEARRAY["UID"])) {
$hits = $array["HITS"];
$size = $array["SIZE"];
$md5 = md5("{$username}{$date}{$Hour}");
echo $username . " HITS:{$hits} SIZE:{$size}\n";
$f[] = "('{$md5}','{$date}','{$username}','{$size}','{$hits}','{$Hour}')";
}
if (count($f) > 0) {
$q->QUERY_SQL($prefix . @implode(",", $f));
if (!$q->ok) {
ufdbguard_admin_events("Fatal: {$q->mysql_error}\n", __FUNCTION__, __FILE__, __LINE__, "stats");
return;
}
}
}
if (count($RTTSIZEARRAY["IP"]) > 0) {
$f = array();
$prefix = "INSERT IGNORE INTO `{$tablename}` (`zMD5`,`zdate`,`ipaddr`,`hostname`,`size`,`hits`,`hour`) VALUES ";
while (list($ip, $array) = each($RTTSIZEARRAY["IP"])) {
$hits = $array["HITS"];
$size = $array["SIZE"];
$md5 = md5("{$ip}{$date}{$Hour}");
$hostname = $classParse->GetComputerName($ip);
echo $ip . "/{$hostname} HITS:{$hits} SIZE:{$size}\n";
$f[] = "('{$md5}','{$date}','{$ip}','{$hostname}','{$size}','{$hits}','{$Hour}')";
}
if (count($f) > 0) {
$q->QUERY_SQL($prefix . @implode(",", $f));
if (!$q->ok) {
ufdbguard_admin_events("Fatal: {$q->mysql_error}\n", __FUNCTION__, __FILE__, __LINE__, "stats");
return;
}
}
}
if (count($RTTSIZEARRAY["MAC"]) > 0) {
$f = array();
$prefix = "INSERT IGNORE INTO `{$tablename}` (`zMD5`,`zdate`,`MAC`,`size`,`hits`,`hour`) VALUES ";
while (list($mac, $array) = each($RTTSIZEARRAY["MAC"])) {
$hits = $array["HITS"];
$size = $array["SIZE"];
$md5 = md5("{$mac}{$date}{$Hour}");
echo "{$mac} HITS:{$hits} SIZE:{$size}\n";
$f[] = "('{$md5}','{$date}','{$mac}','{$size}','{$hits}','{$Hour}')";
}
if (count($f) > 0) {
$q->QUERY_SQL($prefix . @implode(",", $f));
if (!$q->ok) {
ufdbguard_admin_events("Fatal: {$q->mysql_error}\n", __FUNCTION__, __FILE__, __LINE__, "stats");
return;
}
}
}
@unlink($targetFile);
}
}
function UserSizeD_REPAIR($day)
{
$time = strtotime("{$day} 00:00:00");
$sourcetable = date("Ymd", $time) . "_hour";
$q = new mysql_squid_builder();
if (!$q->TABLE_EXISTS($sourcetable)) {
if ($GLOBALS["VERBOSE"]) {
echo "<strong>Fatal!:</strong> {$sourcetable} no such table\n";
}
return;
}
$tablename = "UserSizeD_" . date("Ymd", $time);
if (!$q->CreateUserSizeRTT_day($tablename)) {
if ($GLOBALS["VERBOSE"]) {
echo "<strong>Fatal!:{$tablename}</strong> Query failed {$q->mysql_error}\n";
}
ufdbguard_admin_events("{$tablename}: Query failed {$q->mysql_error}", __FUNCTION__, __FILE__, __LINE__, "stats");
return;
}
$prefix = "INSERT IGNORE INTO `{$tablename}` (`zMD5`,`uid`,`zdate`,\n\t`ipaddr`,`hostname`,`account`,`MAC`,`UserAgent`,`size`,`hits`,`hour`) VALUES ";
$sql = "SELECT client,hostname,account,hour,MAC,SUM(size) as size,SUM(hits) as hits,uid FROM {$sourcetable}\n\tGROUP BY client,hostname,account,hour,MAC,uid";
$results = $q->QUERY_SQL($sql);
if (!$q->ok) {
if ($GLOBALS["VERBOSE"]) {
echo "<strong>Fatal!:</strong> {$q->mysql_error}\n";
}
return;
}
$total = mysql_num_rows($results);
if ($total == 0) {
events_tail("UserSizeD_REPAIR({$day}) Fatal!:No row for {$sourcetable}\n");
return;
}
$de = 0;
while ($ligne = @mysql_fetch_array($results, MYSQL_ASSOC)) {
$de++;
$zMD5 = md5(serialize($ligne));
$uid = addslashes($ligne["uid"]);
$hostname = addslashes($ligne["hostname"]);
$f[] = "('{$zMD5}','{$uid}','{$day}','{$ligne["client"]}','{$hostname}','{$ligne["account"]}','{$ligne["MAC"]}','','{$ligne["size"]}','{$ligne["hits"]}','{$ligne["hour"]}')";
if (count($f) > 500) {
$dep = round($de / $total * 100, 2);
events_tail("UserSizeD_REPAIR({$day}) {$de}/{$total} ({$dep})");
$q->QUERY_SQL($prefix . @implode(",", $f));
if (!$q->ok) {
events_tail("UserSizeD_REPAIR({$day}) Fatal! {$q->mysql_error}");
return;
}
$f = array();
}
}
if (count($f) > 0) {
$q->QUERY_SQL($prefix . @implode(",", $f));
if (!$q->ok) {
if ($GLOBALS["VERBOSE"]) {
echo "<strong>Fatal!:</strong> {$q->mysql_error}\n";
}
return;
}
$f = array();
}
if ($GLOBALS["VERBOSE"]) {
echo "<strong>SUCCESS: {$tablename}</strong>\n";
}
}