function _access_dbmonth($sourcetable, $monthtable)
{
$berekley = new parse_berekley_dbs();
$q = new mysql_squid_builder();
$sql = $berekley->ACCESS_PARSE_TABLE_STRING($monthtable);
if (!$q->QUERY_SQL($sql)) {
if ($GLOBALS["VERBOSE"]) {
echo "{$monthtable} {$q->mysql_error}\n";
}
return;
}
$results = $q->QUERY_SQL("SELECT zDate,familysite,category,hour,uid,ipaddr,MAC,SUM(hits) as hits, SUM(size) as size\n\tFROM {$sourcetable} GROUP BY zDate,familysite,category,hour,uid,ipaddr,MAC");
$f = array();
if (!$q->ok) {
return false;
}
while ($ligne = @mysql_fetch_array($results, MYSQL_ASSOC)) {
$md5 = md5(serialize($ligne));
$familysite = mysql_escape_string2($ligne["familysite"]);
$uid = trim($ligne["uid"]);
$MAC = $ligne["MAC"];
$ipaddr = $ligne["ipaddr"];
if ($uid == null) {
$uid = $q->MacToUid($MAC);
}
if ($uid == null) {
$uid = $q->IpToUid($ipaddr);
}
$uid = mysql_escape_string2($ligne["uid"]);
$category = mysql_escape_string2($ligne["category"]);
$zDate = $ligne["zDate"];
$MAC = $ligne["MAC"];
$hour = $ligne["hour"];
$size = $ligne["size"];
$hits = $ligne["hits"];
$f[] = "('{$md5}','{$zDate}','{$hour}','{$familysite}','{$category}','{$uid}','{$MAC}','{$ipaddr}','{$hits}','{$size}')";
}
if (count($f) > 0) {
$q->QUERY_SQL("INSERT IGNORE INTO `{$monthtable}` \n\t\t(`zmd5`,`zDate`,`hour`,`familysite`,`category`,`uid`,`MAC`,`ipaddr`,`hits`,`size`) VALUES\n\t\t" . @implode(",", $f));
if (!$q->ok) {
return false;
}
}
return true;
}
function db_dump($filepath)
{
preg_match("#([0-9]+)_#", basename($filepath), $re);
$time = WEEK_TIME_FROM_INT($re[1]);
echo "{$time} " . date("Y-m-d", $time) . " WEEK: " . date("YW", $time) . " MONTH: " . date("Ym", $time) . "\n";
$class = new parse_berekley_dbs();
$array = $class->SQUID_QUOTASIZES($filepath);
echo count($array) . " element(s)\n";
}
function parse()
{
$export_path = "/home/artica/squid/dbExport";
$TimeFile = "/etc/artica-postfix/pids/exec.squid.stats.notcached-week.php.time";
$pidfile = "/etc/artica-postfix/pids/exec.squid.stats.notcached-week.php.pid";
$unix = new unix();
$pid = @file_get_contents($pidfile);
if ($pid < 100) {
$pid = null;
}
if ($unix->process_exists($pid, basename(__FILE__))) {
$timepid = $unix->PROCCESS_TIME_MIN($pid);
if ($GLOBALS["VERBOSE"]) {
echo "{$pid} already executed since {$timepid}Mn\n";
}
if ($timepid < 30) {
return;
}
$kill = $unix->find_program("kill");
unix_system_kill_force($pid);
}
@file_put_contents($pidfile, getmypid());
if (!$GLOBALS["VERBOSE"]) {
$time = $unix->file_time_min($TimeFile);
if ($time < 30) {
echo "Current {$time}Mn, require at least 30mn\n";
return;
}
}
@unlink($TimeFile);
@file_put_contents($TimeFile, time());
$files = $unix->DirFiles("/var/log/squid", "[0-9]+_NOTCACHED_WEEK\\.db");
$currentWeek = date("YW");
@mkdir($export_path, 0755, true);
$q = new mysql_squid_builder();
while (list($filename, $none) = each($files)) {
if (!preg_match("#^([0-9]+)_#", $filename, $re)) {
continue;
}
$WeekName = $re[1];
$fullpath = "/var/log/squid/{$filename}";
echo " {$fullpath} -> {$WeekName} -> {$currentWeek}\n";
$berekley = new parse_berekley_dbs();
$tablename = "{$WeekName}_not_cached";
$sql = $berekley->NOT_CACHED_WEEK_PARSE_TABLE_STRING($tablename);
$q->QUERY_SQL($sql);
if (!$q->ok) {
squid_admin_mysql(0, "MySQL Error", $q->mysql_error, __FILE__, __LINE__);
return;
}
if ($WeekName == $currentWeek) {
$q->QUERY_SQL("TRUNCATE TABLE {$tablename}");
}
$array = $berekley->NOT_CACHED_WEEK_PARSE_DB($fullpath);
if (!$array) {
continue;
}
$prefix = $berekley->NOT_CACHED_WEEK_TABLE_PREFIX($tablename);
$sql = $prefix . " " . @implode(",", $array);
$q->QUERY_SQL($sql);
if (!$q->ok) {
squid_admin_mysql(0, "MySQL Error", $q->mysql_error, __FILE__, __LINE__);
return;
}
if ($WeekName == $currentWeek) {
continue;
}
if (!@copy($fullpath, "{$export_path}/{$filename}")) {
continue;
}
@unlink($fullpath);
}
@mkdir("/usr/share/artica-postfix/ressources/logs/stats", 0755, true);
$ligne = mysql_fetch_array($q->QUERY_SQL("SELECT SUM(size) as size FROM {$currentWeek}_not_cached"));
@file_put_contents("/usr/share/artica-postfix/ressources/logs/stats/NOT_CACHED", $ligne["size"]);
@chmod("/usr/share/artica-postfix/ressources/logs/stats/NOT_CACHED", 0755);
}
function parse()
{
$export_path = "/home/artica/squid/dbExport";
$TimeFile = "/etc/artica-postfix/pids/exec.squid.stats.protos.php.time";
$pidfile = "/etc/artica-postfix/pids/exec.squid.stats.protos.php.pid";
$unix = new unix();
$pid = @file_get_contents($pidfile);
if ($pid < 100) {
$pid = null;
}
if ($unix->process_exists($pid, basename(__FILE__))) {
$timepid = $unix->PROCCESS_TIME_MIN($pid);
if ($GLOBALS["VERBOSE"]) {
echo "{$pid} already executed since {$timepid}Mn\n";
}
if ($timepid < 240) {
return;
}
$kill = $unix->find_program("kill");
unix_system_kill_force($pid);
}
@file_put_contents($pidfile, getmypid());
if (!$GLOBALS["VERBOSE"]) {
$time = $unix->file_time_min($TimeFile);
if ($time < 240) {
echo "Current {$time}Mn, require at least 240mn\n";
return;
}
}
@unlink($TimeFile);
@file_put_contents($TimeFile, time());
$files = $unix->DirFiles("/var/log/squid", "[0-9]+_proto\\.db");
$currentDay = date("Ymd");
@mkdir($export_path, 0755, true);
$q = new mysql_squid_builder();
while (list($filename, $none) = each($files)) {
if (!preg_match("#^([0-9]+)_#", $filename, $re)) {
continue;
}
$DayName = $re[1];
$fullpath = "/var/log/squid/{$filename}";
echo " {$fullpath} -> {$DayName} -> {$currentDay}\n";
$berekley = new parse_berekley_dbs();
$tablename = "{$DayName}_proto";
$sql = $berekley->PROTO_DAY_PARSE_TABLE_STRING($tablename);
$q->QUERY_SQL($sql);
if (!$q->ok) {
squid_admin_mysql(0, "MySQL Error", $q->mysql_error, __FILE__, __LINE__);
return;
}
if ($DayName == $currentDay) {
$q->QUERY_SQL("TRUNCATE TABLE {$tablename}");
}
$array = $berekley->PROTO_DAY_PARSE_DB($fullpath);
if (!$array) {
continue;
}
$prefix = $berekley->PROTO_DAY_TABLE_PREFIX($tablename);
$sql = $prefix . " " . @implode(",", $array);
$q->QUERY_SQL($sql);
if (!$q->ok) {
squid_admin_mysql(0, "MySQL Error", $q->mysql_error, __FILE__, __LINE__);
return;
}
if ($DayName == $currentDay) {
continue;
}
if (!@copy($fullpath, "{$export_path}/{$filename}")) {
continue;
}
@unlink($fullpath);
}
}
function Cached_websites()
{
$date = date("YW") . "_wwwcached.db";
$dbfile = "/var/log/squid/{$date}";
if (!is_file($dbfile)) {
return;
}
$wberk = new parse_berekley_dbs();
$q = new mysql_squid_builder();
$sql = $wberk->SQUID_CACHED_SITES_TABLE_STRING("CACHED_SITES", "MEMORY");
$q->QUERY_SQL($sql);
if (!$q->ok) {
return;
}
$array = $wberk->SQUID_CACHED_SITES($dbfile);
if (count($array) == 0) {
return;
}
$prefix = $wberk->SQUID_CACHED_SITES_TABLE_PREFIX("CACHED_SITES");
$q->QUERY_SQL($prefix . @implode(",", $array));
if (!$q->ok) {
echo $q->mysql_error;
}
}
function parsedb($mainpath)
{
$unix = new unix();
$f = $unix->DirFiles($mainpath, "[0-9]+_proto\\.db");
$export_path = "/home/artica/squid/dbExport";
@mkdir($export_path, 0755, true);
$berekley = new parse_berekley_dbs();
$q = new mysql_squid_builder();
while (list($filename, $none) = each($f)) {
preg_match("#([0-9]+)_#", $filename, $re);
$xdate = $re[1];
$xtime = $berekley->TIME_FROM_DAY_INT($xdate);
echo "{$filename} ( {$xdate} )\n";
if (date("Y-m-d", $xtime) == "1970-01-01") {
continue;
}
if (date("Y-m-d", $xtime) == date("Y-m-d")) {
if (!$q->QUERY_SQL($berekley->PROTO_PARSE_TABLE_STRING("PROTO_RTT"))) {
continue;
}
$q->QUERY_SQL("TRUNCATE TABLE PROTO_RTT");
$array = $berekley->PROTO_PARSE_DB("{$mainpath}/{$filename}", $xdate);
$prefix = $berekley->PROTO_PARSE_TABLE_PREFIX("PROTO_RTT");
if (!$array) {
continue;
}
$sql = $prefix . " " . @implode(",", $array);
$q->QUERY_SQL($sql);
continue;
}
$tablename = date("Ym", $xtime) . "_proto";
if (!$q->QUERY_SQL($berekley->PROTO_PARSE_TABLE_STRING($tablename))) {
continue;
}
$array = $berekley->PROTO_PARSE_DB("{$mainpath}/{$filename}", $xdate);
$prefix = $berekley->PROTO_PARSE_TABLE_PREFIX($tablename);
if (!$array) {
continue;
}
$sql = $prefix . " " . @implode(",", $array);
$q->QUERY_SQL($sql);
if (!$q->ok) {
continue;
}
if (!@copy("{$mainpath}/{$filename}", "{$export_path}/{$filename}")) {
continue;
}
@unlink("{$mainpath}/{$filename}");
}
@unlink("/usr/share/artica-postfix/ressources/logs/web/TOP_PROTO_SIZE.db");
@unlink("/usr/share/artica-postfix/ressources/logs/web/TOP_PROTO_HITS.db");
$sql = "SELECT * FROM PROTO_RTT ORDER BY size DESC;";
$results = $q->QUERY_SQL($sql);
$array = array();
if (mysql_num_rows($results) > 1) {
while ($ligne = mysql_fetch_array($results, MYSQL_ASSOC)) {
$ligne["size"] = $ligne["size"] / 1024;
$ligne["size"] = $ligne["size"] / 1024;
$ligne["size"] = round($ligne["size"], 2);
if ($GLOBALS["VERBOSE"]) {
echo "{$ligne["size"]}MB = {$ligne["proto"]}\n";
}
if ($GLOBALS["VERBOSE"]) {
echo "{$ligne["hits"]}hits = {$ligne["proto"]}\n";
}
$MAIN_SIZE[$ligne["proto"]] = $ligne["size"];
$MAIN_HITS[$ligne["proto"]] = $ligne["hits"];
}
@mkdir("/usr/share/artica-postfix/ressources/logs/web", 0755, true);
@file_put_contents("/usr/share/artica-postfix/ressources/logs/web/TOP_PROTO_SIZE.db", serialize($MAIN_SIZE));
@file_put_contents("/usr/share/artica-postfix/ressources/logs/web/TOP_PROTO_HITS.db", serialize($MAIN_HITS));
@chmod("/usr/share/artica-postfix/ressources/logs/web/TOP_PROTO_SIZE.db", 0755);
@chmod("/usr/share/artica-postfix/ressources/logs/web/TOP_PROTO_HITS.db", 0755);
}
}
function parse()
{
$TimeFile = "/etc/artica-postfix/pids/exec.squid.stats.mime.parser.php.time";
$pidfile = "/etc/artica-postfix/pids/exec.squid.stats.mime.parser.php.pid";
$unix = new unix();
$pid = $unix->get_pid_from_file($pidfile);
if ($unix->process_exists($pid, basename(__FILE__))) {
$timepid = $unix->PROCCESS_TIME_MIN($pid);
if ($GLOBALS["VERBOSE"]) {
echo "{$pid} already executed since {$timepid}Mn\n";
}
if ($timepid < 14) {
return;
}
$kill = $unix->find_program("kill");
unix_system_kill_force($pid);
}
@file_put_contents($pidfile, getmypid());
$TimeExec = $unix->file_time_min($TimeFile);
if (!$GLOBALS["FORCE"]) {
if ($TimeExec < 20) {
return;
}
}
@unlink($TimeFile);
@file_put_contents($TimeFile, time());
$f = $unix->DirFiles("/var/log/squid", "[0-9]+_mime\\.db");
$export_path = "/home/artica/squid/dbExport";
@mkdir($export_path, 0755, true);
$berekley = new parse_berekley_dbs();
$q = new mysql_squid_builder();
while (list($filename, $none) = each($f)) {
preg_match("#([0-9]+)_#", $filename, $re);
$xdate = $re[1];
$xtime = $berekley->TIME_FROM_DAY_INT($xdate);
echo "{$filename} ( {$xdate} )\n";
if (date("Y-m-d", $xtime) == date("Y-m-d")) {
if (!$q->QUERY_SQL($berekley->MIME_PARSE_TABLE_STRING("MIME_RTT"))) {
continue;
}
$q->QUERY_SQL("TRUNCATE TABLE MIME_RTT");
$array = $berekley->MIME_PARSE_DB("/var/log/squid/{$filename}", $xdate);
$prefix = $berekley->MIME_PARSE_TABLE_PREFIX("MIME_RTT");
if (!$array) {
continue;
}
$sql = $prefix . " " . @implode(",", $array);
$q->QUERY_SQL($sql);
continue;
}
$tablename = date("Ym", $xtime) . "_mime";
if (!$q->QUERY_SQL($berekley->MIME_PARSE_TABLE_STRING($tablename))) {
continue;
}
$array = $berekley->MIME_PARSE_DB("/var/log/squid/{$filename}", $xdate);
$prefix = $berekley->MIME_PARSE_TABLE_PREFIX($tablename);
if (!$array) {
continue;
}
$sql = $prefix . " " . @implode(",", $array);
$q->QUERY_SQL($sql);
if (!$q->ok) {
continue;
}
if (!@copy("/var/log/squid/{$filename}", "{$export_path}/{$filename}")) {
continue;
}
@unlink("/var/log/squid/{$filename}");
}
@unlink("/usr/share/artica-postfix/ressources/logs/web/TOP_MIME.db");
$sql = "SELECT SUM(size) as size,mime_type FROM MIME_RTT GROUP BY mime_type ORDER BY size DESC LIMIT 0,15;";
$results = $q->QUERY_SQL($sql);
$array = array();
if (mysql_num_rows($results) > 1) {
while ($ligne = mysql_fetch_array($results, MYSQL_ASSOC)) {
$ligne["size"] = $ligne["size"] / 1024;
$ligne["size"] = $ligne["size"] / 1024;
$ligne["size"] = round($ligne["size"], 2);
if ($GLOBALS["VERBOSE"]) {
echo "{$ligne["size"]}MB = {$ligne["mime_type"]}\n";
}
if (preg_match("#application\\/(.+)#", $ligne["mime_type"], $re)) {
$ligne["mime_type"] = $re[1];
}
$MAIN[$ligne["mime_type"]] = $ligne["size"];
}
@mkdir("/usr/share/artica-postfix/ressources/logs/web", 0755, true);
@file_put_contents("/usr/share/artica-postfix/ressources/logs/web/TOP_MIME.db", serialize($MAIN));
@chmod("/usr/share/artica-postfix/ressources/logs/web/TOP_MIME.db", 0755);
}
gencache_TOPCACHED();
}
