function start_week()
{
$unix = new unix();
$hostname = $unix->hostname_g();
$now = InfluxQueryFromUTC(strtotime("-7 day"));
$today = date("Y-m-d", $now) . " 00:00:00";
$sql = "SELECT SUM(TX) as TX, SUM(RX) as RX,ETH FROM ethrxtx WHERE proxyname='{$hostname}' AND time >'{$today}' group by time(4h),ETH";
$influx = new influx();
echo "{$sql}\n";
$main = $influx->QUERY_SQL($sql);
$c = 0;
$f = array();
foreach ($main as $row) {
$time = date("Y-m-d H:i:s", InfluxToTime($row->time));
$ETH = $row->ETH;
if ($ETH == "lo") {
continue;
}
$RX = $row->RX;
$TX = $row->TX;
$f[] = "('{$time}','{$ETH}','{$RX}','{$TX}')";
}
if (count($f) == 0) {
return;
}
$q = new mysql();
if ($q->TABLE_EXISTS("RXTX_WEEK", "artica_events")) {
$q->QUERY_SQL("TRUNCATE TABLE `RXTX_WEEK`", "artica_events");
}
$sql = "CREATE TABLE IF NOT EXISTS `RXTX_WEEK`\n\t(`ZDATE` DATETIME,\n\t`RX` INT UNSIGNED NOT NULL DEFAULT 1,\n\t`TX` INT UNSIGNED NOT NULL DEFAULT 1,\n\t`ETH` VARCHAR(60),\n\tKEY `ZDATE`(`ZDATE`),\n\tKEY `RX`(`RX`),\n\tKEY `TX`(`TX`),\n\tKEY `ETH`(`ETH`) ) ENGINE = MYISAM;";
$q->QUERY_SQL($sql, "artica_events");
if (!$q->ok) {
return;
}
$q->QUERY_SQL("INSERT IGNORE INTO RXTX_WEEK (ZDATE,ETH,RX,TX) VALUES " . @implode(",", $f), "artica_events");
}
function BUILD_REPORT($md5)
{
build_progress("{building_query}", 5);
$unix = new unix();
$q = new mysql_squid_builder();
$ligne = mysql_fetch_array($q->QUERY_SQL("SELECT * FROM reports_cache WHERE `zmd5`='{$md5}'"));
$params = unserialize($ligne["params"]);
$influx = new influx();
$from = InfluxQueryFromUTC($params["FROM"]);
$to = InfluxQueryFromUTC($params["TO"]);
$interval = $params["INTERVAL"];
$md5_table = $md5;
$searchsites_sql = null;
$USER_FIELD = $params["USER"];
$SEARCH = $params["SEARCH"];
if ($SEARCH != null) {
$searchuser_sql = str_replace("*", "%", $SEARCH);
if (strpos(" {$SEARCH}", "%") > 0) {
$searchuser_sql = " HAVING {$USER_FIELD} LIKE '{$SEARCH}'";
} else {
$searchuser_sql = " HAVING `{$USER_FIELD}`='{$SEARCH}'";
}
}
if (!GRAB_DATAS($ligne, $md5)) {
build_progress("{unable_to_query_to_bigdata}", 110);
return;
}
$q = new mysql_squid_builder();
//zDate,familysite,user,size,hits
$sql = "SELECT SUM(SIZE) as SIZE, SUM(RQS) as RQS,{$USER_FIELD},`ZDATE` FROM `tmp_{$md5}user` \n\tGROUP BY {$USER_FIELD},`ZDATE` {$searchuser_sql} ORDER BY `ZDATE`";
echo "********** SQL **********\n";
echo $sql . "\n";
echo "********** SQL **********\n";
$results = $q->QUERY_SQL($sql);
if (!$q->ok) {
echo "{$sql}\n{$q->mysql_error}\n";
build_progress("MySQL error [" . __LINE__ . "]", 110);
return;
}
$c = 0;
while ($ligne = @mysql_fetch_array($results, MYSQL_ASSOC)) {
$time = $ligne["ZDATE"];
$c++;
if (intval($ligne["SIZE"]) == 0) {
continue;
}
$size = $ligne["SIZE"] / 1024;
$size = round($size / 1024, 2);
$xdata[] = $ligne["ZDATE"];
$ydata[] = $size;
}
echo "Building chronology\n";
$sql = "SELECT SUM(SIZE) as SIZE, SUM(RQS) as RQS,`ZDATE`,FAMILYSITE,{$USER_FIELD}\n\tFROM `tmp_{$md5}user` GROUP BY FAMILYSITE,{$USER_FIELD},ZDATE {$searchuser_sql} ORDER BY ZDATE";
echo "********** SQL **********\n";
echo $sql . "\n";
echo "********** SQL **********\n";
$results = $q->QUERY_SQL($sql);
if (!$q->ok) {
echo "{$sql}\n{$q->mysql_error}\n";
build_progress("MySQL error [" . __LINE__ . "]", 110);
return;
}
$c = 0;
$MAIN["CSV"][] = array("time", "bytes", "site", "hits");
while ($ligne = @mysql_fetch_array($results, MYSQL_ASSOC)) {
$c++;
$time = $ligne["ZDATE"];
if ($GLOBALS["VERBOSE"]) {
echo "CHRONOS: {$ligne["FAMILYSITE"]} {$time}'\n";
}
$MAIN["CRONOS"][] = array("TIME" => $time, "BYTES" => $ligne["SIZE"], "SITE" => $ligne["FAMILYSITE"], "RQS" => $ligne["RQS"]);
$MAIN["CSV"][] = array("{$time}", "{$ligne["SIZE"]}", "{$ligne["FAMILYSITE"]}", "{$ligne["RQS"]}");
}
$MAIN["GRAPH1"]["xdata"] = $xdata;
$MAIN["GRAPH1"]["ydata"] = $ydata;
if ($c == 0) {
build_progress("{chronology}: {failed} C == 0", 110);
return;
}
//----------------------------------------------------------------------------------------------------------
$results = $q->QUERY_SQL("SELECT USERID as uid,IPADDR as ipaddr,MAC as mac FROM `tmp_{$md5}user` GROUP BY uid,ipaddr,mac");
build_progress("{parsing_data} (2)", 25);
while ($ligne = @mysql_fetch_array($results, MYSQL_ASSOC)) {
$IDENT[] = array("IPADDR" => $ligne["ipaddr"], "USERID" => $ligne["uid"], "MAC" => $ligne["mac"]);
}
//----------------------------------------------------------------------------------------------------------
$sql = "SELECT SUM(SIZE) as SIZE,FAMILYSITE,{$USER_FIELD} FROM `tmp_{$md5}user` \n\tGROUP BY FAMILYSITE,{$USER_FIELD} {$searchuser_sql} ORDER BY SIZE DESC";
echo "********** SQL **********\n";
echo $sql . "\n";
echo "********** SQL **********\n";
$results = $q->QUERY_SQL($sql);
build_progress("{parsing_data} (2)", 30);
if (!$q->ok) {
echo "{$sql}\n{$q->mysql_error}\n";
build_progress("MySQL error [" . __LINE__ . "]", 110);
return;
}
$tt = 0;
while ($ligne = @mysql_fetch_array($results, MYSQL_ASSOC)) {
$size = $ligne["SIZE"];
$FAMILYSITE = $ligne["FAMILYSITE"];
$size = $size / 1024;
$PieData2[$FAMILYSITE] = $size;
if ($GLOBALS["VERBOSE"]) {
echo "PIE DATA: {$FAMILYSITE}' -> {$size}\n";
}
if ($tt < 11) {
$MAIN["GRAPH2"]["PIEDATA"][$FAMILYSITE] = $size;
$MAIN["GRAPH2"]["TABLE"][$FAMILYSITE] = $size;
}
$tt++;
$MAIN["FAMS"][$FAMILYSITE] = $ligne["SIZE"];
}
//----------------------------------------------------------------------------------------------------------
echo "MD5:{$GLOBALS["zMD5"]}\n";
REMOVE_TABLES($GLOBALS["zMD5"]);
$encoded_data = base64_encode(serialize($MAIN));
$datasize = strlen($encoded_data);
echo "Saving " . strlen($encoded_data) . " bytes...\n";
$q->QUERY_SQL("UPDATE reports_cache SET `builded`=1,`values`='{$encoded_data}',`values_size`='{$datasize}' WHERE `zmd5`='{$GLOBALS["zMD5"]}'");
if (!$q->ok) {
echo $q->mysql_error . "\n";
build_progress("MySQL {failed}", 110);
return;
}
build_progress("{success}", 100);
}
function BUILD_REPORT($md5)
{
build_progress("{building_query}", 5);
$unix = new unix();
$q = new mysql_squid_builder();
$ligne = mysql_fetch_array($q->QUERY_SQL("SELECT * FROM reports_cache WHERE `zmd5`='{$md5}'"));
$params = unserialize($ligne["params"]);
$influx = new influx();
$mintime = strtotime("2008-01-01 00:00:00");
$params["TO"] = intval($params["TO"]);
$params["FROM"] = abs(intval($params["FROM"]));
if ($params["FROM"] < $mintime) {
$params["FROM"] = strtotime(date("Y-m-d 00:00:00"));
}
$params["TO"] = intval($params["TO"]);
if ($params["TO"] < $mintime) {
$params["TO"] = time();
}
$influx = new influx();
$to = InfluxQueryFromUTC($params["TO"]);
$from = InfluxQueryFromUTC($params["FROM"]);
$interval = $params["INTERVAL"];
$user = $params["USER"];
$md5_table = $md5;
if (!GRAB_DATAS($ligne, $md5)) {
build_progress("{unable_to_query_to_bigdata}", 110);
return false;
}
$q = new postgres_sql();
$q->QUERY_SQL("COPY (SELECT * from \"{$md5}report\") To '/tmp/{$md5}report.csv' with CSV HEADER;");
$values_size = @filesize("/tmp/{$md5}report.csv");
$values = mysql_escape_string2(@file_get_contents("/tmp/{$md5}report.csv"));
echo "MD5:{$GLOBALS["zMD5"]} {$values_size}Bytes " . FormatBytes($values_size / 1024) . "\n";
$q = new mysql_squid_builder();
$q->QUERY_SQL("UPDATE reports_cache SET `builded`=1,`values`='{$values}',`values_size`='{$values_size}' WHERE `zmd5`='{$GLOBALS["zMD5"]}'");
if (!$q->ok) {
echo $q->mysql_error . "\n";
build_progress("PostGreSQL {failed}", 110);
return;
}
build_progress("{success}", 100);
}
function parse()
{
$TimeFile = "/etc/artica-postfix/pids/exec.squid.interface-size.php.time";
$pidfile = "/etc/artica-postfix/pids/exec.squid.interface-size.php.pid";
$unix = new unix();
$sock = new sockets();
$EnableKerbAuth = intval($sock->GET_INFO("EnableKerbAuth"));
$ActiveDirectoryEmergency = intval($sock->GET_INFO("ActiveDirectoryEmergency"));
if (!is_file("/etc/artica-postfix/settings/Daemons/SQUIDEnable")) {
@file_put_contents("/etc/artica-postfix/settings/Daemons/SQUIDEnable", 1);
}
if ($ActiveDirectoryEmergency == 1) {
$EnableKerbAuth = 0;
}
$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 (!$GLOBALS["FORCE"]) {
if ($timepid < 14) {
return;
}
$kill = $unix->find_program("kill");
unix_system_kill_force($pid);
}
}
@file_put_contents($pidfile, getmypid());
if (!$GLOBALS["FORCE"]) {
if (!$GLOBALS["VERBOSE"]) {
$time = $unix->file_time_min($TimeFile);
if ($time < 14) {
echo "Current {$time}Mn, require at least 14mn\n";
return;
}
}
}
@unlink($TimeFile);
@file_put_contents($TimeFile, time());
$sock = new sockets();
$SquidPerformance = intval($sock->GET_INFO("SquidPerformance"));
events("Proxy performance set to {$SquidPerformance}");
build_progress("{refresh_dashboard_values}", 10);
system_values();
$php = $unix->LOCATE_PHP5_BIN();
build_progress("{refresh_dashboard_values}", 11);
$dateint = InfluxQueryFromUTC(strtotime("-48 hours"));
$date = date("Y-m-d H:00:00", $dateint);
$qSimple = new mysql();
$sql = "SELECT COUNT(ID) as tcount FROM squid_admin_mysql WHERE severity=0 AND zDate>'{$date}'";
$ligne = mysql_fetch_array($qSimple->QUERY_SQL($sql, "artica_events"));
@file_put_contents("{$GLOBALS["BASEDIR"]}/WATCHDOG_COUNT_EVENTS", $ligne["tcount"]);
@chmod("{$GLOBALS["BASEDIR"]}/WATCHDOG_COUNT_EVENTS", 0777);
build_progress("{refresh_dashboard_values} APACHE_STATISTICS (2)", 11);
APACHE_STATISTICS();
build_progress("{refresh_dashboard_values} NETWORK_INTERFACES_RXTX (3)", 11);
NETWORK_INTERFACES_RXTX();
build_progress("{refresh_dashboard_values} COUNT_OF_SURICATA (4)", 11);
COUNT_OF_SURICATA();
build_progress("{refresh_dashboard_values} NOT_CATEGORIZED (5)", 11);
NOT_CATEGORIZED();
$SQUIDEnable = intval(@file_get_contents("/etc/artica-postfix/settings/Daemons/SQUIDEnable"));
if ($SQUIDEnable == 0) {
build_progress("{done}", 100);
return;
}
if ($SquidPerformance > 1) {
if (is_file("/etc/cron.d/artica-stats-hourly")) {
@unlink("/etc/cron.d/artica-stats-hourly");
system("/etc/init.d/cron reload");
}
build_progress("{statistics_are_disabled}", 110);
die;
}
if (!is_file("/etc/cron.d/artica-stats-hourly")) {
@unlink("/etc/cron.d/artica-stats-hourly");
}
@mkdir("/usr/share/artica-postfix/ressources/interface-cache", 0755, true);
$t1 = time();
$q = new mysql_squid_builder();
$tables[] = "dashboard_size_day";
$tables[] = "dashboard_countwebsite_day";
$tables[] = "dashboard_countuser_day";
$tables[] = "dashboard_user_day";
$tables[] = "dashboard_notcached";
$tables[] = "dashboard_cached";
$tables[] = "dashboard_blocked_day";
while (list($num, $table) = each($tables)) {
if (!$q->TABLE_EXISTS($table)) {
events("Table: {$table} is not yet ready...");
continue;
}
$NUM = $q->COUNT_ROWS($table);
events("Table: {$table} {$NUM} rows");
}
build_progress("{calculate_cache_rate}", 12);
CachedOrNot();
squidhour_clean();
$t1 = time();
$influx = new influx();
$now = InfluxQueryFromUTC(strtotime("-24 hour"));
build_progress("{refresh_dashboard_values}", 13);
// -----------------------------------------------------------------------------------------------------
build_progress("{refresh_dashboard_values}", 14);
// -----------------------------------------------------------------------------------------------------
build_progress("{cleaning_databases}", 16);
squidhour_clean();
build_progress("{refresh_dashboard_values}", 17);
FLUX_RQS();
build_progress("{refresh_dashboard_values}", 18);
build_progress("{refresh_dashboard_values}", 19);
//USERAGENTS();
build_progress("{calculate_dates}", 20);
MAX_MIN();
backup_size();
build_progress("{refresh_dashboard_values}", 21);
WEBFILTERING();
build_progress("{refresh_dashboard_values}", 22);
$f = array();
// -----------------------------------------------------------------------------------------------------
$q = new mysql_squid_builder();
$ligne = @mysql_fetch_array($q->QUERY_SQL("SELECT COUNT(*) as tcount FROM proxy_ports WHERE enabled=1 AND transparent=1 AND Tproxy=1"));
if ($q->ok) {
@file_put_contents("{$GLOBALS["BASEDIR"]}/COUNT_DE_TRANSPARENT", intval($ligne["tcount"]));
}
// -----------------------------------------------------------------------------------------------------
build_progress("{refresh_dashboard_values}", 51);
$MAIN = array();
$xdata = array();
$ydata = array();
$f = array();
// -----------------------------------------------------------------------------------------------------
// Calcul des caches en cours.
$SquidCacheLevel = $sock->GET_INFO("SquidCacheLevel");
if (!is_numeric($SquidCacheLevel)) {
$SquidCacheLevel = 4;
}
if ($SquidCacheLevel == 0) {
@file_put_contents("{$GLOBALS["BASEDIR"]}/COUNT_DE_CACHES", 0);
}
build_progress("{refresh_dashboard_values}", 52);
$q = new mysql();
$sql = "SELECT cache_size,cache_type FROM squid_caches_center WHERE remove=0";
$xsize = 0;
$results = $q->QUERY_SQL($sql, "artica_backup");
while ($ligne = @mysql_fetch_array($results, MYSQL_ASSOC)) {
$cache_size = $ligne["cache_size"];
$cache_type = $ligne["cache_type"];
if ($cache_type == "Cachenull") {
continue;
}
$xsize = $xsize + $cache_size;
}
if ($GLOBALS["VERBOSE"]) {
echo "COUNT_DE_CACHES: {$xsize}MB\n";
}
@file_put_contents("{$GLOBALS["BASEDIR"]}/COUNT_DE_CACHES", $xsize);
if ($GLOBALS["PROGRESS"]) {
build_progress("{refresh_dashboard_values}", 90);
system("{$php} /usr/share/artica-postfix/exec.status.php --all --verbose");
}
build_progress("{refresh_dashboard_values} {done}", 100);
// -----------------------------------------------------------------------------------------------------
}
function cpustats()
{
$xdata = array();
$ydata = array();
$unix = new unix();
$hostname = $unix->hostname_g();
$filecache = dirname(__FILE__) . "/ressources/logs/web/cpustatsH.db";
$filecache_load = dirname(__FILE__) . "/ressources/logs/web/INTERFACE_LOAD_AVGH.db";
$filecache_mem = dirname(__FILE__) . "/ressources/logs/web/INTERFACE_LOAD_AVG2H.db";
$now = InfluxQueryFromUTC(strtotime("-24 hour"));
$influx = new influx();
$sql = "SELECT MEAN(CPU_STATS) as cpu,MEAN(LOAD_AVG) as load,MEAN(MEM_STATS) as memory FROM SYSTEM where proxyname='{$hostname}' and time > {$now}s GROUP BY time(10m) ORDER BY ASC";
if ($GLOBALS["VERBOSE"]) {
echo "{$sql}\n";
}
$main = $influx->QUERY_SQL($sql);
foreach ($main as $row) {
$time = InfluxToTime($row->time);
if (!is_numeric($row->cpu)) {
continue;
}
if (!is_numeric($row->load)) {
continue;
}
$min = date("l H:i", $time) . "mn";
$xdata[] = $min;
$ydata[] = round($row->cpu, 2);
$ydataL[] = round($row->load, 2);
$ydataM[] = round($row->memory, 2);
if ($GLOBALS["VERBOSE"]) {
echo "{$min} -> {$row->cpu} | {$row->load} | {$row->memory}\n";
}
}
if (count($xdata) > 1) {
$ARRAY = array($xdata, $ydata);
$ARRAYL = array($xdata, $ydataL);
$ARRAYM = array($xdata, $ydataM);
if ($GLOBALS["VERBOSE"]) {
echo "-> {$filecache}\n";
}
@file_put_contents($filecache, serialize($ARRAY));
@file_put_contents($filecache_load, serialize($ARRAYL));
@file_put_contents($filecache_mem, serialize($ARRAYM));
@chmod($filecache, 0755);
@chmod($filecache_load, 0755);
@chmod($filecache_mem, 0755);
}
$xdata = array();
$ydata = array();
$ydataL = array();
$ydataM = array();
$filecache = dirname(__FILE__) . "/ressources/logs/web/cpustats.db";
$filecache_load = dirname(__FILE__) . "/ressources/logs/web/INTERFACE_LOAD_AVG.db";
$filecache_mem = dirname(__FILE__) . "/ressources/logs/web/INTERFACE_LOAD_AVG2.db";
$now = InfluxQueryFromUTC(strtotime("-168 hour"));
$influx = new influx();
$sql = "SELECT MEAN(CPU_STATS) as cpu,MEAN(LOAD_AVG) as load,MEAN(MEM_STATS) as memory FROM SYSTEM where proxyname='{$hostname}' and time > {$now}s GROUP BY time(1h) ORDER BY ASC";
if ($GLOBALS["VERBOSE"]) {
echo "{$sql}\n";
}
$main = $influx->QUERY_SQL($sql);
foreach ($main as $row) {
$time = InfluxToTime($row->time);
if (!is_numeric($row->cpu)) {
continue;
}
if (!is_numeric($row->load)) {
continue;
}
$min = date("l H:00", $time);
$xdata[] = $min;
$ydata[] = round($row->cpu, 2);
$ydataL[] = round($row->load, 2);
$ydataM[] = round($row->memory / 1024, 2);
if ($GLOBALS["VERBOSE"]) {
echo "{$min} -> {$row->cpu} | {$row->load} | {$row->memory}\n";
}
}
if (count($xdata) > 1) {
$ARRAY = array($xdata, $ydata);
$ARRAYL = array($xdata, $ydataL);
$ARRAYM = array($xdata, $ydataM);
if ($GLOBALS["VERBOSE"]) {
echo "-> {$filecache}\n";
}
@file_put_contents($filecache, serialize($ARRAY));
@file_put_contents($filecache_load, serialize($ARRAYL));
@file_put_contents($filecache_mem, serialize($ARRAYM));
@chmod($filecache, 0755);
@chmod($filecache_load, 0755);
@chmod($filecache_mem, 0755);
}
}
function parse()
{
$TimeFile = "/etc/artica-postfix/pids/exec.squid.interface-size.php.time";
$pidfile = "/etc/artica-postfix/pids/exec.squid.interface-size.php.pid";
$unix = new unix();
$sock = new sockets();
$EnableKerbAuth = intval($sock->GET_INFO("EnableKerbAuth"));
$ActiveDirectoryEmergency = intval($sock->GET_INFO("ActiveDirectoryEmergency"));
if ($ActiveDirectoryEmergency == 1) {
$EnableKerbAuth = 0;
}
$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 (!$GLOBALS["FORCE"]) {
if ($timepid < 14) {
return;
}
$kill = $unix->find_program("kill");
unix_system_kill_force($pid);
}
}
@file_put_contents($pidfile, getmypid());
if (!$GLOBALS["FORCE"]) {
if (!$GLOBALS["VERBOSE"]) {
$time = $unix->file_time_min($TimeFile);
if ($time < 14) {
echo "Current {$time}Mn, require at least 14mn\n";
return;
}
}
}
@unlink($TimeFile);
@file_put_contents($TimeFile, time());
$sock = new sockets();
$SquidPerformance = intval($sock->GET_INFO("SquidPerformance"));
events("Proxy performance set to {$SquidPerformance}");
build_progress("{refresh_dashboard_values}", 10);
system_values();
$php = $unix->LOCATE_PHP5_BIN();
build_progress("{refresh_dashboard_values}", 11);
$dateint = InfluxQueryFromUTC(strtotime("-48 hours"));
$date = date("Y-m-d H:00:00", $dateint);
$qSimple = new mysql();
$sql = "SELECT COUNT(ID) as tcount FROM squid_admin_mysql WHERE severity=0 AND zDate>'{$date}'";
$ligne = mysql_fetch_array($qSimple->QUERY_SQL($sql, "artica_events"));
@file_put_contents("{$GLOBALS["BASEDIR"]}/WATCHDOG_COUNT_EVENTS", $ligne["tcount"]);
@chmod("{$GLOBALS["BASEDIR"]}/WATCHDOG_COUNT_EVENTS", 0777);
if ($SquidPerformance > 1) {
if (is_file("/etc/cron.d/artica-stats-hourly")) {
@unlink("/etc/cron.d/artica-stats-hourly");
system("/etc/init.d/cron reload");
}
build_progress("{statistics_are_disabled}", 110);
die;
}
if (!is_file("/etc/cron.d/artica-stats-hourly")) {
@unlink("/etc/cron.d/artica-stats-hourly");
}
@mkdir("/usr/share/artica-postfix/ressources/interface-cache", 0755, true);
$t1 = time();
$q = new mysql_squid_builder();
$tables[] = "dashboard_size_day";
$tables[] = "dashboard_countwebsite_day";
$tables[] = "dashboard_countuser_day";
$tables[] = "dashboard_user_day";
$tables[] = "dashboard_notcached";
$tables[] = "dashboard_cached";
$tables[] = "dashboard_blocked_day";
while (list($num, $table) = each($array)) {
if (!$q->TABLE_EXISTS($table)) {
events("Table: {$table} is not yet ready...");
continue;
}
$NUM = $q->COUNT_ROWS($table);
events("Table: {$table} {$NUM} rows");
}
build_progress("{calculate_cache_rate}", 12);
CachedOrNot();
squidhour_clean();
$t1 = time();
$influx = new influx();
$now = InfluxQueryFromUTC(strtotime("-24 hour"));
build_progress("{refresh_dashboard_values}", 13);
// -----------------------------------------------------------------------------------------------------
$COUNT_DE_SNI_CERTS_TIME = 1000000;
$COUNT_DE_SNI_CERTS = "{$GLOBALS["BASEDIR"]}/COUNT_DE_SNI_CERTS";
if (is_file($COUNT_DE_SNI_CERTS)) {
$COUNT_DE_SNI_CERTS_TIME = $unix->file_time_min($COUNT_DE_SNI_CERTS);
}
if ($GLOBALS["VERBOSE"]) {
echo "COUNT_DE_SNI_CERTS_TIME = {$COUNT_DE_SNI_CERTS_TIME}\n";
$COUNT_DE_SNI_CERTS_TIME = 999999;
}
if ($COUNT_DE_SNI_CERTS_TIME > 60) {
$sql = "SELECT COUNT(website) as tcount,website FROM sni_certs WHERE time > {$now}s GROUP BY time(24h),website";
$f = array();
echo $sql . "\n";
$main = $influx->QUERY_SQL($sql);
foreach ($main as $row) {
$website = $row->website;
$count = intval($row->tcount);
if ($GLOBALS["VERBOSE"]) {
echo "SNI: {$website} -> {$count}\n";
}
$f[] = "('{$count}','{$website}')";
}
if (count($f) > 0) {
$q = new mysql_squid_builder();
$q->QUERY_SQL("CREATE TABLE IF NOT EXISTS sni_certs (`hits` BIGINT UNSIGNED, `websites` VARCHAR(128) NOT NULL PRIMARY KEY, KEY `hits` (`hits`) ) ENGINE=MYISAM");
$q->QUERY_SQL("TRUNCATE TABLE sni_certs");
$q->QUERY_SQL("INSERT IGNORE INTO sni_certs (hits,websites) VALUES " . @implode(",", $f));
@unlink($COUNT_DE_SNI_CERTS);
@file_put_contents($COUNT_DE_SNI_CERTS, count($f));
}
}
build_progress("{refresh_dashboard_values}", 14);
$NETS = $unix->NETWORK_ALL_INTERFACES();
// -----------------------------------------------------------------------------------------------------
while (list($Interface, $array) = each($NETS)) {
$sql = "SELECT SUM(RX) as size FROM ethrxtx WHERE time > {$now}s AND ETH='{$Interface}' GROUP BY time(10m) ORDER BY ASC";
if ($GLOBALS["VERBOSE"]) {
echo "\n*****\n{$sql}\n******\n";
}
$MAIN = array();
$xdata = array();
$ydata = array();
$main = $influx->QUERY_SQL($sql);
foreach ($main as $row) {
$time = InfluxToTime($row->time);
$min = date("H:i", $time);
$size = intval($row->size) / 1024;
if ($GLOBALS["VERBOSE"]) {
echo "({$time}): ethrxtx {$Interface}:RX: {$min} -> {$size}\n";
}
$size = $size / 1024;
if (round($size) == 0) {
continue;
}
$xdata[] = $min;
$ydata[] = round($size);
}
$MAIN["xdata"] = $xdata;
$MAIN["ydata"] = $ydata;
@file_put_contents("{$GLOBALS["BASEDIR"]}/FLUX_{$Interface}_RX", serialize($MAIN));
if (count($xdata) < 2) {
@unlink("{$GLOBALS["BASEDIR"]}/FLUX_{$Interface}_RX");
}
$sql = "SELECT SUM(TX) as size FROM ethrxtx WHERE time > {$now}s AND ETH='{$Interface}' GROUP BY time(10m) ORDER BY ASC";
$MAIN = array();
$xdata = array();
$ydata = array();
build_progress("{refresh_dashboard_values}", 15);
$main = $influx->QUERY_SQL($sql);
foreach ($main as $row) {
$time = InfluxToTime($row->time);
$min = date("H:i", $time);
$size = intval($row->size) / 1024;
$size = $size / 1024;
if ($size == 0) {
continue;
}
if ($GLOBALS["VERBOSE"]) {
echo "ethrxtx {$Interface}:TX: {$min} -> {$size}\n";
}
$xdata[] = $min;
$ydata[] = round($size);
}
$MAIN["xdata"] = $xdata;
$MAIN["ydata"] = $ydata;
@file_put_contents("{$GLOBALS["BASEDIR"]}/FLUX_{$Interface}_TX", serialize($MAIN));
if (count($xdata) < 2) {
@unlink("{$GLOBALS["BASEDIR"]}/FLUX_{$Interface}_TX");
}
}
// -----------------------------------------------------------------------------------------------------
build_progress("{cleaning_databases}", 16);
squidhour_clean();
build_progress("{refresh_dashboard_values}", 17);
FLUX_RQS();
build_progress("{refresh_dashboard_values}", 18);
build_progress("{refresh_dashboard_values}", 19);
USERAGENTS();
build_progress("{calculate_dates}", 20);
MAX_MIN();
backup_size();
build_progress("{refresh_dashboard_values}", 21);
WEBFILTERING();
build_progress("{refresh_dashboard_values}", 22);
$f = array();
// -----------------------------------------------------------------------------------------------------
$q = new mysql_squid_builder();
$ligne = @mysql_fetch_array($q->QUERY_SQL("SELECT COUNT(*) as tcount FROM proxy_ports WHERE enabled=1 AND transparent=1 AND Tproxy=1"));
if ($q->ok) {
@file_put_contents("{$GLOBALS["BASEDIR"]}/COUNT_DE_TRANSPARENT", intval($ligne["tcount"]));
}
// -----------------------------------------------------------------------------------------------------
build_progress("{refresh_dashboard_values}", 51);
$MAIN = array();
$xdata = array();
$ydata = array();
$f = array();
// -----------------------------------------------------------------------------------------------------
// Calcul des caches en cours.
$SquidCacheLevel = $sock->GET_INFO("SquidCacheLevel");
if (!is_numeric($SquidCacheLevel)) {
$SquidCacheLevel = 4;
}
if ($SquidCacheLevel == 0) {
@file_put_contents("{$GLOBALS["BASEDIR"]}/COUNT_DE_CACHES", 0);
}
build_progress("{refresh_dashboard_values}", 52);
$q = new mysql();
$sql = "SELECT cache_size,cache_type FROM squid_caches_center WHERE remove=0";
$xsize = 0;
$results = $q->QUERY_SQL($sql, "artica_backup");
while ($ligne = @mysql_fetch_array($results, MYSQL_ASSOC)) {
$cache_size = $ligne["cache_size"];
$cache_type = $ligne["cache_type"];
if ($cache_type == "Cachenull") {
continue;
}
$xsize = $xsize + $cache_size;
}
if ($GLOBALS["VERBOSE"]) {
echo "COUNT_DE_CACHES: {$xsize}MB\n";
}
@file_put_contents("{$GLOBALS["BASEDIR"]}/COUNT_DE_CACHES", $xsize);
if ($GLOBALS["PROGRESS"]) {
build_progress("{refresh_dashboard_values}", 90);
system("{$php} /usr/share/artica-postfix/exec.status.php --all --verbose");
}
build_progress("{refresh_dashboard_values} {done}", 100);
// -----------------------------------------------------------------------------------------------------
}
function BUILD_REPORT($md5)
{
build_progress("{building_query}", 5);
$unix = new unix();
$q = new mysql_squid_builder();
$ligne = mysql_fetch_array($q->QUERY_SQL("SELECT * FROM reports_cache WHERE `zmd5`='{$md5}'"));
$params = unserialize($ligne["params"]);
$influx = new influx();
$to = InfluxQueryFromUTC($params["TO"]);
$from = InfluxQueryFromUTC($params["FROM"]);
$interval = $params["INTERVAL"];
$user = $params["USER"];
$md5_table = $md5;
if (!GRAB_DATAS($ligne, $md5)) {
build_progress("{unable_to_query_to_bigdata}", 110);
}
$q = new mysql_squid_builder();
$per["10m"] = "DATE_FORMAT(zDate,'%m-%d %Hh') as tdate";
$per["1h"] = "DATE_FORMAT(zDate,'%m-%d %Hh') as tdate";
$per["1d"] = "DATE_FORMAT(zDate,'%m-%d') as tdate";
$per["1w"] = "DATE_FORMAT(zDate,'%U') as tdate";
$per["30d"] = "DATE_FORMAT(zDate,'%m') as tdate";
$datformat = $per[$interval];
$results = $q->QUERY_SQL("SELECT user,size FROM `{$md5}user` ORDER BY size DESC");
build_progress("{parsing_data} (2)", 25);
$c = 0;
while ($ligne = @mysql_fetch_array($results, MYSQL_ASSOC)) {
$USER = $ligne["user"];
$SIZE = $ligne["size"];
$BIGDATA[$USER] = $SIZE;
$c++;
}
build_progress("{$c} {rows}", 8);
echo "{$c} rows....\n";
REMOVE_TABLES($md5);
$encoded_data = base64_encode(serialize($BIGDATA));
$datasize = strlen($encoded_data);
echo "Saving " . strlen($encoded_data) . " bytes...\n";
$q->QUERY_SQL("UPDATE reports_cache SET `builded`=1,`values`='{$encoded_data}',`values_size`='{$datasize}' WHERE `zmd5`='{$GLOBALS["zMD5"]}'");
if (!$q->ok) {
echo $q->mysql_error . "\n";
build_progress("MySQL {failed}", 110);
return;
}
build_progress("{success}", 100);
}
function xsFLOW($md5)
{
build_progress("{building_query}", 5);
$unix = new unix();
$q = new mysql_squid_builder();
$ligne = mysql_fetch_array($q->QUERY_SQL("SELECT * FROM reports_cache WHERE `zmd5`='{$md5}'"));
$params = unserialize($ligne["params"]);
$influx = new influx();
$from = InfluxQueryFromUTC($params["FROM"]);
$to = InfluxQueryFromUTC($params["TO"]);
$interval = $params["INTERVAL"];
$user = $params["USER"];
$md5_table = $md5;
if (!GRAB_DATAS($ligne, $md5)) {
build_progress("{unable_to_query_to_bigdata}", 110);
}
$q = new mysql_squid_builder();
$per["10m"] = "DATE_FORMAT(zDate,'%m-%d %Hh') as tdate";
$per["1h"] = "DATE_FORMAT(zDate,'%m-%d %Hh') as tdate";
$per["1d"] = "DATE_FORMAT(zDate,'%m-%d') as tdate";
$per["1w"] = "DATE_FORMAT(zDate,'%U') as tdate";
$per["30d"] = "DATE_FORMAT(zDate,'%m') as tdate";
$datformat = $per[$interval];
$sql = "SELECT SUM(size) as size,{$datformat} FROM `{$md5}sites` GROUP BY tdate ORDER BY tdate";
echo "{$sql}\n";
$results = $q->QUERY_SQL($sql);
build_progress("{parsing_data} (2)", 25);
$c = 0;
while ($ligne = @mysql_fetch_array($results, MYSQL_ASSOC)) {
$size = $ligne["size"] / 1024;
$size = round($size / 1024);
if ($GLOBALS["VERBOSE"]) {
echo "{$ligne["tdate"]} = {$size}MB\n";
}
if ($size == 0) {
continue;
}
$c++;
$xdata[] = $ligne["tdate"];
$ydata[] = $size;
}
build_progress("{$c} {rows}", 8);
echo "{$c} rows....\n";
if (count($xdata) < 2) {
$q->QUERY_SQL("DROP TABLE `{$md5}sites`");
build_progress("{$c} {rows} ({only})", 110);
REMOVE_TABLES($md5);
return;
}
$time = time();
$MAIN["GRAPH1"]["xdata"] = $xdata;
$MAIN["GRAPH1"]["ydata"] = $ydata;
$xdata = array();
$ydata = array();
$sql = "SELECT SUM(size) as size,familysite FROM `{$md5}sites` GROUP BY familysite ORDER BY size DESC LIMIT 0,10";
echo "{$sql}\n";
$results = $q->QUERY_SQL($sql);
build_progress("{parsing_data} (2)", 30);
$c = 0;
while ($ligne = @mysql_fetch_array($results, MYSQL_ASSOC)) {
$size = $ligne["size"];
$FAMILYSITE = $ligne["familysite"];
$PieData2[$FAMILYSITE] = $size;
$size = $size / 1024;
$size = round($size / 1024, 2);
$PieData[$FAMILYSITE] = $size;
$c++;
}
$MAIN["GRAPH2"]["PIEDATA"] = $PieData;
$MAIN["GRAPH2"]["TABLE"] = $PieData2;
build_progress("{saving}", 50);
if ($GLOBALS["zMD5"] == null) {
build_progress("MD5 - > NULL {failed}", 110);
return;
}
build_progress("{$c} {rows}", 60);
build_progress("{building_query} {$user} (3)", 70);
$results = $q->QUERY_SQL("SELECT user,size FROM `{$md5}users` ORDER BY size DESC LIMIT 0,20");
build_progress("{parsing_data} {$user} (3)", 90);
while ($ligne = @mysql_fetch_array($results, MYSQL_ASSOC)) {
$size = $ligne["size"];
$FAMILYSITE = $ligne["user"];
$PieData4[$FAMILYSITE] = $size;
$size = $size / 1024;
$size = round($size / 1024, 2);
$PieData3[$FAMILYSITE] = $size;
}
$MAIN["GRAPH3"]["PIEDATA"] = $PieData3;
$MAIN["GRAPH3"]["TABLE"] = $PieData4;
$MAIN["GRAPH3"]["TYPE"] = $user;
$MAIN["CSV1"] = $GLOBALS["CSV1"];
$MAIN["CSV2"] = $GLOBALS["CSV2"];
echo "MD5:{$GLOBALS["zMD5"]}\n";
REMOVE_TABLES($md5);
$encoded_data = base64_encode(serialize($MAIN));
$datasize = strlen($encoded_data);
echo "Saving " . strlen($encoded_data) . " bytes...\n";
$q->QUERY_SQL("UPDATE reports_cache SET `builded`=1,`values`='{$encoded_data}',`values_size`='{$datasize}' WHERE `zmd5`='{$GLOBALS["zMD5"]}'");
if (!$q->ok) {
echo $q->mysql_error . "\n";
build_progress("MySQL {failed}", 110);
return;
}
build_progress("{success}", 100);
}
function BUILD_REPORT($md5)
{
build_progress("{building_query}", 5);
$unix = new unix();
$q = new mysql_squid_builder();
$ligne = mysql_fetch_array($q->QUERY_SQL("SELECT * FROM reports_cache WHERE `zmd5`='{$md5}'"));
$params = unserialize($ligne["params"]);
$influx = new influx();
$from = InfluxQueryFromUTC($params["FROM"]);
$to = InfluxQueryFromUTC($params["TO"]);
$interval = $params["INTERVAL"];
$user = $params["USER"];
$md5_table = $md5;
if (!GRAB_DATAS($ligne, $md5)) {
build_progress("{unable_to_query_to_bigdata}", 110);
return;
}
$q = new mysql_squid_builder();
$per["10m"] = "DATE_FORMAT(zDate,'%m-%d %Hh') as tdate";
$per["1h"] = "DATE_FORMAT(zDate,'%m-%d %Hh') as tdate";
$per["1d"] = "DATE_FORMAT(zDate,'%m-%d') as tdate";
$per["1w"] = "DATE_FORMAT(zDate,'%U') as tdate";
$per["30d"] = "DATE_FORMAT(zDate,'%m') as tdate";
$datformat = $per[$interval];
//----------------------------------------------------------------------------------------------------------
$results = $q->QUERY_SQL("SELECT SUM(size) as size,familysite FROM `{$md5}user` GROUP BY familysite ORDER BY size DESC LIMIT 0,10");
build_progress("{parsing_data} (2)", 25);
$c = 0;
while ($ligne = @mysql_fetch_array($results, MYSQL_ASSOC)) {
$size = $ligne["size"];
$size = round($size / 1024);
$c++;
$FAMILYSITE = $ligne["familysite"];
$TOP_WEBSITES_SIZE[$FAMILYSITE] = $size;
}
build_progress("{$c} {rows}", 30);
//----------------------------------------------------------------------------------------------------------
$results = $q->QUERY_SQL("SELECT SUM(hits) as hits,familysite FROM `{$md5}user` GROUP BY familysite ORDER BY hits DESC LIMIT 0,10");
build_progress("{parsing_data} (2)", 25);
while ($ligne = @mysql_fetch_array($results, MYSQL_ASSOC)) {
$hits = $ligne["hits"];
$FAMILYSITE = $ligne["familysite"];
$TOP_WEBSITES_HITS[$FAMILYSITE] = $hits;
}
//----------------------------------------------------------------------------------------------------------
$results = $q->QUERY_SQL("SELECT SUM(size) as size,{$user} FROM `{$md5}user` GROUP BY {$user} ORDER BY size DESC LIMIT 0,10");
build_progress("{parsing_data} (2)", 25);
while ($ligne = @mysql_fetch_array($results, MYSQL_ASSOC)) {
$size = $ligne["size"];
$size = round($size / 1024);
$USER = $ligne[$user];
echo "USER: {$USER} (" . FormatBytes($size) . ")\n";
$TOP_WEBSITES_MEMBERS[$USER] = $size;
}
//----------------------------------------------------------------------------------------------------------
$MAIN["CSV"] = $GLOBALS["CSV1"];
$MAIN["TOP_WEBSITES_SIZE"] = $TOP_WEBSITES_SIZE;
$MAIN["TOP_WEBSITES_MEMBERS"] = $TOP_WEBSITES_MEMBERS;
$MAIN["TOP_WEBSITES_HITS"] = $TOP_WEBSITES_HITS;
echo "MD5:{$GLOBALS["zMD5"]}\n";
REMOVE_TABLES($GLOBALS["zMD5"]);
$encoded_data = base64_encode(serialize($MAIN));
$datasize = strlen($encoded_data);
echo "Saving " . strlen($encoded_data) . " bytes...\n";
$q->QUERY_SQL("UPDATE reports_cache SET `builded`=1,`values`='{$encoded_data}',`values_size`='{$datasize}' WHERE `zmd5`='{$GLOBALS["zMD5"]}'");
if (!$q->ok) {
echo $q->mysql_error . "\n";
build_progress("MySQL {failed}", 110);
return;
}
build_progress("{success}", 100);
}
function BUILD_REPORT($md5)
{
build_progress("{building_query}", 5);
$unix = new unix();
$q = new mysql_squid_builder();
$ligne = mysql_fetch_array($q->QUERY_SQL("SELECT * FROM reports_cache WHERE `zmd5`='{$md5}'"));
$params = unserialize($ligne["params"]);
$influx = new influx();
$from = InfluxQueryFromUTC($params["FROM"]);
$to = InfluxQueryFromUTC($params["TO"]);
$interval = $params["INTERVAL"];
$user = $params["USER"];
$md5_table = $md5;
if (!GRAB_DATAS($ligne, $md5)) {
build_progress("{unable_to_query_to_bigdata}", 110);
return;
}
$q = new mysql_squid_builder();
$per["10m"] = "DATE_FORMAT(zDate,'%m-%d %Hh') as tdate";
$per["1h"] = "DATE_FORMAT(zDate,'%m-%d %Hh') as tdate";
$per["1d"] = "DATE_FORMAT(zDate,'%m-%d') as tdate";
$per["1w"] = "DATE_FORMAT(zDate,'%U') as tdate";
$per["30d"] = "DATE_FORMAT(zDate,'%m') as tdate";
$datformat = $per[$interval];
$md5_table = "`{$md5}user`";
//----------------------------------------------------------------------------------------------------------
$sql = "SELECT SUM(hits) as hits,UID,CLIENT FROM {$md5_table} GROUP BY UID,CLIENT ORDER by hits DESC LIMIT 0,15";
$results = $q->QUERY_SQL($sql);
build_progress("{parsing_data} (2)", 15);
$c = 0;
while ($ligne = @mysql_fetch_array($results, MYSQL_ASSOC)) {
$c++;
$MAIN["GRAPH1"][$ligne["UID"] . "/" . $ligne["CLIENT"]] = $ligne["hits"];
}
build_progress("{$c} {rows}", 30);
//----------------------------------------------------------------------------------------------------------
$sql = "SELECT SUM(hits) as hits,CATEGORY FROM {$md5_table} GROUP BY CATEGORY ORDER by hits DESC LIMIT 0,15";
$results = $q->QUERY_SQL($sql);
build_progress("{parsing_data} (2)", 15);
while ($ligne = @mysql_fetch_array($results, MYSQL_ASSOC)) {
$MAIN["GRAPH2"][$ligne["CATEGORY"]] = $ligne["hits"];
}
//----------------------------------------------------------------------------------------------------------
$sql = "SELECT SUM(hits) as hits,RULENAME FROM {$md5_table} GROUP BY RULENAME ORDER by hits DESC LIMIT 0,15";
$results = $q->QUERY_SQL($sql);
build_progress("{parsing_data} (2)", 15);
while ($ligne = @mysql_fetch_array($results, MYSQL_ASSOC)) {
$MAIN["GRAPH3"][$ligne["RULENAME"]] = $ligne["hits"];
}
//----------------------------------------------------------------------------------------------------------
$sql = "SELECT SUM(hits) as hits,WEBSITE FROM {$md5_table} GROUP BY WEBSITE ORDER by hits DESC LIMIT 0,15";
$results = $q->QUERY_SQL($sql);
build_progress("{parsing_data} (2)", 15);
while ($ligne = @mysql_fetch_array($results, MYSQL_ASSOC)) {
$MAIN["GRAPH4"][$ligne["WEBSITE"]] = $ligne["hits"];
}
//----------------------------------------------------------------------------------------------------------
$MAIN["csv"] = $GLOBALS["CSV1"];
echo "MD5:{$GLOBALS["zMD5"]}\n";
REMOVE_TABLES($GLOBALS["zMD5"]);
$encoded_data = base64_encode(serialize($MAIN));
$datasize = strlen($encoded_data);
echo "Saving " . strlen($encoded_data) . " bytes...\n";
$q->QUERY_SQL("UPDATE reports_cache SET `builded`=1,`values`='{$encoded_data}',`values_size`='{$datasize}' WHERE `zmd5`='{$GLOBALS["zMD5"]}'");
if (!$q->ok) {
echo $q->mysql_error . "\n";
build_progress("MySQL {failed}", 110);
return;
}
build_progress("{success}", 100);
}
function mQuotaWebFilter()
{
$unix = new unix();
$pidfile = "/etc/artica-postfix/pids/exec.squid.stats.hours.php.mQuotaWebFilter.pid";
$timefile = "/etc/artica-postfix/pids/exec.squid.stats.hours.php.mQuotaWebFilter.time";
$sock = new sockets();
$SquidPerformance = intval($sock->GET_INFO("SquidPerformance"));
if ($SquidPerformance > 1) {
die;
}
$pid = $unix->get_pid_from_file($pidfile);
if (!$GLOBALS["FORCE"]) {
if ($pid < 100) {
$pid = null;
}
$unix = new unix();
if ($unix->process_exists($pid, basename(__FILE__))) {
$rpcessTime = $unix->PROCCESS_TIME_MIN($pid);
if ($GLOBALS["VERBOSE"]) {
echo "Already executed pid {$pid} since {$rpcessTime}Mn\n";
}
if ($rpcessTime < 10) {
return;
}
$unix->KILL_PROCESS($pid, 9);
}
$timeexec = $unix->file_time_min($timefile);
if ($timeexec < 30) {
return;
}
$mypid = getmypid();
@file_put_contents($pidfile, $mypid);
}
build_last_hour();
$ipClass = new IP();
if ($GLOBALS["VERBOSE"]) {
echo "Time File: {$timefile}\n";
}
$ARRAY["TIME_BUILD"] = time();
$q = new mysql_squid_builder();
$influx = new influx();
$date_from = InfluxQueryFromUTC(strtotime("-1 hour"));
$sql = "SELECT SUM(SIZE) as size,USERID,IPADDR,MAC,CATEGORY FROM access_log GROUP BY time(1h) ,USERID,IPADDR,MAC,CATEGORY WHERE time > {$date_from}s";
if ($GLOBALS["VERBOSE"]) {
echo "{$sql}\n";
}
$main = $influx->QUERY_SQL($sql);
foreach ($main as $row) {
$CATEGORY = $row->CATEGORY;
$USERID = strtolower($row->USERID);
$IPADDR = $row->IPADDR;
$MAC = $row->MAC;
$size = intval($row->size);
if ($size == 0) {
continue;
}
if ($CATEGORY != null) {
if (!isset($ARRAY["categories"][$CATEGORY]["HOUR"][$USERID])) {
$ARRAY["categories"][$CATEGORY]["HOUR"][$USERID] = $size;
} else {
$size_old = intval($ARRAY["categories"][$CATEGORY]["HOUR"][$USERID]);
$size_old = $size_old + $size;
$ARRAY["categories"][$CATEGORY]["HOUR"][$USERID] = $size_old;
}
if (!isset($ARRAY["categories"][$CATEGORY]["HOUR"][$IPADDR])) {
$ARRAY["categories"][$CATEGORY]["HOUR"][$IPADDR] = $size;
} else {
$size_old = $ARRAY["categories"][$CATEGORY]["HOUR"][$IPADDR];
$size_old = $size_old + $size;
if ($size_old == 0) {
echo "Warning {$CATEGORY}/{$IPADDR} {$ARRAY["categories"][$CATEGORY]["HOUR"][$IPADDR]} + {$size} = 0\n";
}
$ARRAY["categories"][$CATEGORY]["HOUR"][$IPADDR] = $size_old;
}
if (!isset($ARRAY["categories"][$CATEGORY]["HOUR"][$MAC])) {
$ARRAY["categories"][$CATEGORY]["HOUR"][$MAC] = $size;
} else {
$size_old = $ARRAY["categories"][$CATEGORY]["HOUR"][$MAC];
$size_old = $size_old + $size;
$ARRAY["categories"][$CATEGORY]["HOUR"][$MAC] = $size_old;
}
}
if (!isset($ARRAY["UID"][$USERID]["HOUR"])) {
$ARRAY["UID"][$USERID]["HOUR"] = $size;
} else {
$size_old = $ARRAY["UID"][$USERID]["HOUR"];
$size_old = $size_old + $size;
$ARRAY["UID"][$USERID]["HOUR"] = $size_old;
}
if (!isset($ARRAY["IPADDR"][$IPADDR]["HOUR"])) {
$ARRAY["IPADDR"][$IPADDR]["HOUR"] = $size;
} else {
$size_old = $ARRAY["IPADDR"][$IPADDR]["HOUR"];
$size_old = $size_old + $size;
$ARRAY["IPADDR"][$IPADDR]["HOUR"] = $size_old;
}
if ($ipClass->IsvalidMAC($MAC)) {
if (!isset($ARRAY["MAC"][$MAC]["HOUR"])) {
$ARRAY["MAC"][$MAC]["HOUR"] = $size;
} else {
$size_old = $ARRAY["MAC"][$MAC]["HOUR"];
$size_old = $size_old + $size;
$ARRAY["MAC"][$MAC]["HOUR"] = $size_old;
}
}
}
//-----------------------------------------------------------------------------------------------
$date_from = InfluxQueryFromUTC(strtotime("-1 day"));
$sql = "SELECT SUM(SIZE) as size,USERID,IPADDR,MAC,CATEGORY FROM access_hour GROUP BY time(1d) ,USERID,IPADDR,MAC,CATEGORY WHERE time > {$date_from}s";
if ($GLOBALS["VERBOSE"]) {
echo "{$sql}\n";
}
$main = $influx->QUERY_SQL($sql);
foreach ($main as $row) {
$CATEGORY = $row->CATEGORY;
$USERID = strtolower($row->USERID);
$IPADDR = $row->IPADDR;
$MAC = $row->MAC;
$size = intval($row->size);
if ($size == 0) {
continue;
}
if ($CATEGORY != null) {
if (!isset($ARRAY["categories"][$CATEGORY]["DAY"][$USERID])) {
$ARRAY["categories"][$CATEGORY]["DAY"][$USERID] = $size;
} else {
$size_old = intval($ARRAY["categories"][$CATEGORY]["DAY"][$USERID]);
$size_old = $size_old + $size;
$ARRAY["categories"][$CATEGORY]["DAY"][$USERID] = $size_old;
}
if (!isset($ARRAY["categories"][$CATEGORY]["DAY"][$IPADDR])) {
$ARRAY["categories"][$CATEGORY]["DAY"][$IPADDR] = $size;
} else {
$size_old = $ARRAY["categories"][$CATEGORY]["DAY"][$IPADDR];
$size_old = $size_old + $size;
$ARRAY["categories"][$CATEGORY]["DAY"][$IPADDR] = $size_old;
}
if (!isset($ARRAY["categories"][$CATEGORY]["DAY"][$MAC])) {
$ARRAY["categories"][$CATEGORY]["DAY"][$MAC] = $size;
} else {
$size_old = $ARRAY["categories"][$CATEGORY]["DAY"][$MAC];
$size_old = $size_old + $size;
$ARRAY["categories"][$CATEGORY]["DAY"][$MAC] = $size_old;
}
}
if (!isset($ARRAY["UID"][$USERID]["DAY"])) {
$ARRAY["UID"][$USERID]["DAY"] = $size;
} else {
$size_old = $ARRAY["UID"][$USERID]["DAY"];
$size_old = $size_old + $size;
$ARRAY["UID"][$USERID]["DAY"] = $size_old;
}
if (!isset($ARRAY["IPADDR"][$IPADDR]["DAY"])) {
$ARRAY["IPADDR"][$IPADDR]["DAY"] = $size;
} else {
$size_old = $ARRAY["IPADDR"][$IPADDR]["DAY"];
$size_old = $size_old + $size;
$ARRAY["IPADDR"][$IPADDR]["DAY"] = $size_old;
}
if ($ipClass->IsvalidMAC($MAC)) {
if (!isset($ARRAY["MAC"][$MAC]["DAY"])) {
$ARRAY["MAC"][$MAC]["DAY"] = $size;
} else {
$size_old = $ARRAY["MAC"][$MAC]["DAY"];
$size_old = $size_old + $size;
$ARRAY["MAC"][$MAC]["DAY"] = $size_old;
}
}
}
//-----------------------------------------------------------------------------------------------
$influx = new influx();
$date_from = strtotime("-1 week");
$sql = "SELECT SUM(SIZE) as size,USERID,IPADDR,MAC,CATEGORY FROM access_hour GROUP BY time(1w) ,USERID,IPADDR,MAC,CATEGORY WHERE time > {$date_from}s";
$main = $influx->QUERY_SQL($sql);
foreach ($main as $row) {
$CATEGORY = $row->CATEGORY;
$USERID = strtolower($row->USERID);
$IPADDR = $row->IPADDR;
$MAC = $row->MAC;
$size = intval($row->size);
if ($size == 0) {
continue;
}
if ($CATEGORY != null) {
if (!isset($ARRAY["categories"][$CATEGORY]["WEEK"][$USERID])) {
$ARRAY["categories"][$CATEGORY]["WEEK"][$USERID] = $size;
} else {
$size_old = $ARRAY["categories"][$CATEGORY]["WEEK"][$USERID];
$size_old = $size_old + $size;
$ARRAY["categories"][$CATEGORY]["WEEK"][$USERID] = $size_old;
}
if (!isset($ARRAY["categories"][$CATEGORY]["WEEK"][$IPADDR])) {
$ARRAY["categories"][$CATEGORY]["WEEK"][$IPADDR] = $size;
} else {
$size_old = $ARRAY["categories"][$CATEGORY]["WEEK"][$IPADDR];
$size_old = $size_old + $size;
$ARRAY["categories"][$CATEGORY]["WEEK"][$IPADDR] = $size_old;
}
if (!isset($ARRAY["categories"][$CATEGORY]["WEEK"][$MAC])) {
$ARRAY["categories"][$CATEGORY]["WEEK"][$MAC] = $size;
} else {
$size_old = $ARRAY["categories"][$CATEGORY]["WEEK"][$MAC];
$size_old = $size_old + $size;
$ARRAY["categories"][$CATEGORY]["WEEK"][$MAC] = $size_old;
}
}
if ($USERID != null) {
if (!isset($ARRAY["UID"][$USERID]["WEEK"])) {
$ARRAY["UID"][$USERID]["WEEK"] = $size;
} else {
$size_old = $ARRAY["UID"][$USERID]["WEEK"];
$size_old = $size_old + $size;
$ARRAY["UID"][$USERID]["WEEK"] = $size_old;
}
}
if ($ipClass->isValid($IPADDR)) {
if (!isset($ARRAY["IPADDR"][$IPADDR]["WEEK"])) {
$ARRAY["IPADDR"][$IPADDR]["IPADDR"] = $size;
} else {
$size_old = $ARRAY["IPADDR"][$IPADDR]["WEEK"];
$size_old = $size_old + $size;
$ARRAY["IPADDR"][$IPADDR]["WEEK"] = $size_old;
}
}
if ($ipClass->IsvalidMAC($MAC)) {
if (!isset($ARRAY["MAC"][$MAC]["WEEK"])) {
$ARRAY["MAC"][$MAC]["WEEK"] = $size;
} else {
$size_old = $ARRAY["MAC"][$MAC]["WEEK"];
$size_old = $size_old + $size;
$ARRAY["MAC"][$MAC]["WEEK"] = $size_old;
}
}
}
//-----------------------------------------------------------------------------------------------
if ($GLOBALS["VERBOSE"]) {
print_r($ARRAY);
}
@unlink("/usr/share/squid3/CurrentSizesUsers.db");
@file_put_contents("/usr/share/squid3/CurrentSizesUsers.db", serialize($ARRAY));
@file_put_contents("/usr/share/artica-postfix/ressources/logs/web/CurrentSizesUsers.db", serialize($ARRAY));
@chmod("/usr/share/artica-postfix/ressources/logs/web/CurrentSizesUsers.db", 0755);
}
function BUILD_REPORT($md5)
{
build_progress("{building_query}", 5);
$unix = new unix();
$q = new mysql_squid_builder();
$ligne = mysql_fetch_array($q->QUERY_SQL("SELECT * FROM reports_cache WHERE `zmd5`='{$md5}'"));
$params = unserialize($ligne["params"]);
$influx = new influx();
$from = InfluxQueryFromUTC($params["FROM"]);
$to = InfluxQueryFromUTC($params["TO"]);
$interval = $params["INTERVAL"];
$user = $params["USER"];
$md5_table = $md5;
if (!GRAB_DATAS($ligne, $md5)) {
build_progress("{unable_to_query_to_bigdata}", 110);
return;
}
$q = new mysql_squid_builder();
$per["10m"] = "DATE_FORMAT(zDate,'%m-%d %Hh') as tdate";
$per["1h"] = "DATE_FORMAT(zDate,'%m-%d %Hh') as tdate";
$per["1d"] = "DATE_FORMAT(zDate,'%m-%d') as tdate";
$per["1w"] = "DATE_FORMAT(zDate,'%U') as tdate";
$per["30d"] = "DATE_FORMAT(zDate,'%m') as tdate";
$datformat = $per[$interval];
$md5_table = "`{$md5}user`";
//----------------------------------------------------------------------------------------------------------
$sql = "SELECT {$datformat},SUM(size) as size FROM {$md5_table} GROUP BY tdate ORDER by tdate";
build_progress("{parsing_data} (1)", 7);
$results = $q->QUERY_SQL($sql);
if (!$q->ok) {
echo $q->mysql_error . "\n";
build_progress("{parsing_data} (1) {failed}", 110);
REMOVE_TABLES($md5);
die;
}
$c = 0;
while ($ligne = @mysql_fetch_array($results, MYSQL_ASSOC)) {
$ligne["size"] = round($ligne["size"] / 1024);
$MAIN["GRAPH0"]["ydata"][] = $ligne["size"];
$MAIN["GRAPH0"]["xdata"][] = $ligne["tdate"];
$c++;
}
build_progress("{$c} {rows}", 30);
//----------------------------------------------------------------------------------------------------------
$sql = "SELECT {$datformat},SUM(hits) as hits FROM {$md5_table} GROUP BY zdate ORDER by tdate";
build_progress("{parsing_data}", 35);
$results = $q->QUERY_SQL($sql);
if (!$q->ok) {
echo $q->mysql_error . "\n";
build_progress("{parsing_data} {failed}", 110);
REMOVE_TABLES($md5);
die;
}
if (mysql_num_rows($results) == 0) {
build_progress("{parsing_data} {no_data}", 7);
echo $sql . "\n";
sleep(10);
REMOVE_TABLES($md5);
build_progress("{parsing_data} {failed}", 110);
}
while ($ligne = @mysql_fetch_array($results, MYSQL_ASSOC)) {
$MAIN["GRAPH1"]["ydata"][] = $ligne["hits"];
$MAIN["GRAPH1"]["xdata"][] = $ligne["tdate"];
}
//----------------------------------------------------------------------------------------------------------
$sql = "SELECT SUM(size) as size,familysite FROM {$md5_table} GROUP BY familysite ORDER by size DESC LIMIT 0,15";
$results = $q->QUERY_SQL($sql);
if (!$q->ok) {
echo $q->mysql_error . "\n";
build_progress("{parsing_data} {failed}", 110);
REMOVE_TABLES($md5);
die;
}
if (mysql_num_rows($results) == 0) {
build_progress("{parsing_data} {no_data}", 7);
echo $sql . "\n";
sleep(10);
REMOVE_TABLES($md5);
build_progress("{parsing_data} {failed}", 110);
}
build_progress("{parsing_data}", 40);
while ($ligne = @mysql_fetch_array($results, MYSQL_ASSOC)) {
$ligne["size"] = round($ligne["size"] / 1024);
$MAIN["GRAPH2"][$ligne["familysite"]] = $ligne["size"];
}
//----------------------------------------------------------------------------------------------------------
$sql = "SELECT SUM(size) as size,{$user} FROM {$md5_table} GROUP BY {$user} ORDER by size DESC LIMIT 0,15";
$results = $q->QUERY_SQL($sql);
if (!$q->ok) {
echo $q->mysql_error . "\n";
build_progress("{parsing_data} {failed}", 110);
REMOVE_TABLES($md5);
die;
}
if (mysql_num_rows($results) == 0) {
build_progress("{parsing_data} {no_data}", 7);
echo $sql . "\n";
sleep(10);
REMOVE_TABLES($md5);
build_progress("{parsing_data} {failed}", 110);
}
build_progress("{parsing_data}", 45);
while ($ligne = @mysql_fetch_array($results, MYSQL_ASSOC)) {
$ligne["size"] = round($ligne["size"] / 1024);
$MAIN["GRAPH3"][$ligne[$user]] = $ligne["size"];
}
//----------------------------------------------------------------------------------------------------------
$sql = "SELECT SUM(hits) as hits,category FROM {$md5_table} GROUP BY category ORDER by hits DESC LIMIT 0,15";
$results = $q->QUERY_SQL($sql);
build_progress("{parsing_data}", 50);
while ($ligne = @mysql_fetch_array($results, MYSQL_ASSOC)) {
$category = $ligne["category"];
if ($category == null) {
$category = "unknown";
}
$MAIN["GRAPH4"][$category] = $ligne["hits"];
}
//----------------------------------------------------------------------------------------------------------
$sql = "SELECT SUM(size) as size,category FROM {$md5_table} GROUP BY category ORDER by size DESC LIMIT 0,15";
$results = $q->QUERY_SQL($sql);
build_progress("{parsing_data}", 55);
while ($ligne = @mysql_fetch_array($results, MYSQL_ASSOC)) {
$category = $ligne["category"];
$ligne["size"] = $ligne["size"] / 1024;
if ($category == null) {
$category = "unknown";
}
$MAIN["GRAPH5"][$category] = $ligne["size"];
}
//----------------------------------------------------------------------------------------------------------
$MAIN["csv"] = $GLOBALS["CSV1"];
echo "MD5:{$GLOBALS["zMD5"]}\n";
REMOVE_TABLES($GLOBALS["zMD5"]);
$encoded_data = base64_encode(serialize($MAIN));
$datasize = strlen($encoded_data);
echo "Saving " . strlen($encoded_data) . " bytes...\n";
$q->QUERY_SQL("UPDATE reports_cache SET `builded`=1,`values`='{$encoded_data}',`values_size`='{$datasize}' WHERE `zmd5`='{$GLOBALS["zMD5"]}'");
if (!$q->ok) {
echo $q->mysql_error . "\n";
build_progress("MySQL {failed}", 110);
return;
}
build_progress("{success}", 100);
}
