function export_last_websites()
{
$q = new mysql_squid_builder();
$categories = $q->LIST_TABLES_CATEGORIES();
$prefix = "INSERT IGNORE INTO categorize (zmd5,pattern,zdate,uuid,category) VALUES ";
while (list($num, $table) = each($categories)) {
$sql = "SELECT * FROM {$table} WHERE enabled=1 ORDER BY zDate DESC LIMIT 0,1000";
$results = $GLOBALS["Q"]->QUERY_SQL($sql);
while ($ligne = @mysql_fetch_array($results, MYSQL_ASSOC)) {
if (trim($ligne["pattern"]) == null) {
$q->QUERY_SQL("DELETE FROM {$table} WHERE zmd5='{$ligne["zmd5"]}'");
writelogs_squid("{$ligne["zmd5"]} has no website.\nIt has been deleted from table {$table}", __FUNCTION__, __FILE__, __LINE__);
continue;
}
$f[] = "('{$ligne["zmd5"]}','{$ligne["pattern"]}','{$ligne["zDate"]}','{$ligne["uuid"]}','{$ligne["category"]}')";
}
if (count($f) > 0) {
$q->QUERY_SQL($prefix . @implode(",", $f));
$f = array();
}
}
}
function UncompressTable($tablename)
{
if (!$this->isTableCompressed($tablename)) {
return null;
}
unset($GLOBALS["isTableCompressed::{$tablename}"]);
if ($GLOBALS["VERBOSE"]) {
echo "Uncompress table `{$tablename}`\n";
}
writelogs_squid("Uncompress table `{$tablename}`", __FUNCTION__, __FILE__, __LINE__, "MySQL");
$unix = new unix();
$sock = new sockets();
$myisamchk = $unix->find_program("myisamchk");
$myisampack = $unix->find_program("myisampack");
$MYSQL_DATA_DIR = $sock->GET_INFO("ChangeMysqlDir");
if ($MYSQL_DATA_DIR == null) {
$MYSQL_DATA_DIR = "/var/lib/mysql";
}
$cmd = "{$myisamchk} --unpack {$MYSQL_DATA_DIR}/{$this->database}/{$tablename}.MYI 2>&1";
$esults[] = $cmd;
exec("{$cmd}", $esults);
$this->QUERY_SQL("FLUSH TABLE {$tablename}");
$this->QUERY_SQL("UPDATE tables_day SET `compressed`=0 WHERE `tablename`='{$tablename}'");
return @implode("\n", $esults);
if ($this->isTableCompressed($tablename)) {
writelogs_squid("Uncompress table `{$tablename}`: FAILED " . @implode(", ", $esults), __FUNCTION__, __FILE__, __LINE__, "stats");
}
if ($GLOBALS["VERBOSE"]) {
echo "Uncompress table done\n";
}
}
function searchwords_hour_to_day($sourcetable)
{
$time = $GLOBALS["Q"]->TIME_FROM_SEARCHHOUR_TABLE($sourcetable);
$hour = date("H", $time);
if (date("YmdH", $time) == date("YmdH")) {
if ($GLOBALS["VERBOSE"]) {
echo "{$sourcetable} -> {$hour} >skip\n";
}
return false;
}
$sql = "SELECT COUNT(zmd5) as hits,DATE_FORMAT(zDate,'%Y%m%d') as prefix,\n\tDATE_FORMAT(zDate,'%Y-%m-%d') as `newdate`,`sitename`,`ipaddr`,`hostname`,`uid`,`MAC`,`account`,`familysite`,`words` \n\tFROM `{$sourcetable}`\n\tGROUP BY prefix,sitename,ipaddr,hostname,uid,MAC,account,familysite,words,newdate HAVING LENGTH(words)>1";
$results = $GLOBALS["Q"]->QUERY_SQL($sql);
if (!$GLOBALS["Q"]->ok) {
writelogs_squid("Fatal: {$GLOBALS["Q"]->mysql_error} on `{$sourcetable}`", __FUNCTION__, __FILE__, __LINE__, "stats");
return;
}
$f = array();
while ($ligne = @mysql_fetch_array($results, MYSQL_ASSOC)) {
$zmd5 = md5(serialize($ligne));
$sitename = $ligne["sitename"];
if (preg_match("#^www\\.(.+)#", $sitename, $re)) {
$sitename = $re[1];
}
$words = addslashes(utf8_encode($ligne["words"]));
$s = "('{$zmd5}','{$ligne["hits"]}','{$sitename}','{$ligne["newdate"]}','{$hour}','{$ligne["ipaddr"]}',\n\t\t'{$ligne["hostname"]}','{$ligne["uid"]}','{$ligne["MAC"]}','{$ligne["account"]}','{$ligne["familysite"]}','{$words}')";
$f[$ligne["prefix"]][] = $s;
}
if (count($f) > 0) {
while (list($index_table, $rows) = each($f)) {
$newtable = "searchwordsD_{$index_table}";
if (!$GLOBALS["Q"]->check_SearchWords_day($index_table)) {
writelogs_squid("Fatal: Creating {$newtable} {$GLOBALS["Q"]->mysql_error}", __FUNCTION__, __FILE__, __LINE__, "stats");
return;
}
$sql = "INSERT IGNORE INTO {$newtable} (`zmd5`,`hits`,`sitename`,`zDate`,`hour`,`ipaddr`,`hostname`,`uid`,`MAC`,`account`,`familysite`,`words`) VALUES " . @implode(",", $rows);
$GLOBALS["Q"]->QUERY_SQL($sql);
if (!$GLOBALS["Q"]->ok) {
writelogs_squid("Fatal: {$GLOBALS["Q"]->mysql_error} on `{$newtable}`", __FUNCTION__, __FILE__, __LINE__, "stats");
return;
}
}
}
return true;
}
function ParseUserAuth($checkpid = false)
{
$unix = new unix();
$pidfile = "/etc/artica-postfix/pids/" . basename(__FILE__) . "." . __FUNCTION__ . ".pid";
if ($checkpid) {
$pid = @file_get_contents($pidfile);
if ($pid < 100) {
$pid = null;
}
if ($unix->process_exists($pid, basename(__FILE__))) {
$time = $unix->PROCCESS_TIME_MIN($pid);
writelogs_squid("Already executed pid {$pid} since {$time}mn-> DIE");
if ($GLOBALS["VERBOSE"]) {
echo "Already executed pid {$pid} since {$time}mn\n";
}
die;
}
@file_put_contents($pidfile, getmypid());
}
$sock = new sockets();
if (isset($GLOBALS["EnableMacAddressFilter"])) {
$GLOBALS["EnableMacAddressFilter"] = $sock->GET_INFO("EnableMacAddressFilter");
if (!is_numeric($GLOBALS["EnableMacAddressFilter"])) {
$GLOBALS["EnableMacAddressFilter"] = 1;
}
}
$hostname = $unix->hostname_g();
$MustContinue = false;
ParseUserAuthNew();
if (function_exists("system_is_overloaded")) {
$COUNT_FILES = $unix->COUNT_FILES("/var/log/artica-postfix/squid-users");
if ($COUNT_FILES < 1000) {
if (system_is_overloaded()) {
return;
}
} else {
$MustContinue = true;
}
}
$countDeFiles = 0;
if (!($handle = opendir("/var/log/artica-postfix/squid-users"))) {
@mkdir("/var/log/artica-postfix/squid-users", 0755, true);
die;
}
if (!$MustContinue) {
if (systemMaxOverloaded()) {
return;
}
}
$countDeFiles = 0;
$prefix = "INSERT IGNORE INTO UserAutDB (zmd5,MAC,ipaddr,uid,hostname,UserAgent) VALUES ";
$f = array();
while (false !== ($filename = readdir($handle))) {
if ($filename == ".") {
continue;
}
if ($filename == "..") {
continue;
}
$targetFile = "/var/log/artica-postfix/squid-users/{$filename}";
$countDeFiles++;
$content = @file_get_contents($targetFile);
$array = unserialize($content);
$ParseUserAuthArray = ParseUserAuthArray($array);
if ($ParseUserAuthArray != null) {
$f[] = $ParseUserAuthArray;
}
@unlink($targetFile);
}
if (count($f) > 0) {
$q = new mysql_squid_builder();
$q->QUERY_SQL($prefix . @implode(",", $f));
}
nmap_scan();
}
function MonthCatzFamilies_perform($sourcetable, $xtime)
{
$q = new mysql_squid_builder();
if (!$q->TABLE_EXISTS($sourcetable)) {
return true;
}
$sql = "SELECT SUM(size) as size,SUM(hits) as hits,familysite,client,hostname,MAC,size,hits,uid,catfam FROM `{$sourcetable}`\n\t\tGROUP BY familysite,client,hostname,MAC,size,hits,uid,catfam HAVING catfam>0";
$results = $q->QUERY_SQL($sql);
if (!$q->ok) {
writelogs_squid("Fatal: {$q->mysql_error} on `tables_day`", __FUNCTION__, __FILE__, __LINE__, "stats");
return;
}
if (!MonthCatzFamiliesBuildTables($xtime)) {
return;
}
$tableblockMonth = date('Ym', $xtime) . "_catfam";
$prefix = "INSERT IGNORE INTO `{$tableblockMonth}` (`zmd5`,`zDate`,`hits`,`size`,`client`,`uid`,`hostname`,`MAC`,`familysite`,`catfam`) VALUES ";
$day = date("Y-m-d", $xtime);
while ($ligne = @mysql_fetch_array($results, MYSQL_ASSOC)) {
$zmd5 = md5($day . serialize($ligne));
$f[] = "('{$zmd5}','{$day}','{$ligne["hits"]}','{$ligne["size"]}','{$ligne["client"]}','{$ligne["uid"]}','{$ligne["hostname"]}','{$ligne["MAC"]}','{$ligne["familysite"]}','{$ligne["catfam"]}')";
if (count($f) > 500) {
if ($GLOBALS["VERBOSE"]) {
echo "{$tableblockMonth} -> 500\n";
}
$q->QUERY_SQL($prefix . @implode(",", $f));
$f = array();
if (!$q->ok) {
return false;
}
}
}
if (count($f) > 0) {
if ($GLOBALS["VERBOSE"]) {
echo "{$tableblockMonth} -> " . count($f) . "\n";
}
$q->QUERY_SQL($prefix . @implode(",", $f));
$f = array();
if (!$q->ok) {
return false;
}
}
return true;
}
function inject_array($array)
{
$q = new mysql_squid_builder();
while (list($tablePrefix, $f) = each($array)) {
if (count($f) > 0) {
$tablename = "searchwords_{$tablePrefix}";
if ($GLOBALS["VERBOSE"]) {
echo "-> {$tablename} -> " . count($f) . "\n";
}
$q->check_SearchWords_hour($tablePrefix);
$prefix = "INSERT IGNORE INTO {$tablename} (`zmd5`,`sitename`,`zDate`,`ipaddr`,`hostname`,`uid`,`MAC`,`account`,`familysite`,`words`) VALUES " . @implode(",", $f);
$q->QUERY_SQL($prefix);
if (!$q->ok) {
writelogs_squid("{$q->mysql_error}", __FUNCTION__, __FILE__, __LINE__, "stats");
@mkdir("/var/log/artica-postfix/searchwords-sql-errors", 0755, true);
@file_put_contents("/var/log/artica-postfix/searchwords-sql-errors/" . md5($prefix), $prefix);
}
}
}
}
function __re_categorize_subtables($oldT1 = 0, $websites)
{
$unix = new unix();
if (!$GLOBALS["FORCE"]) {
if (SquidStatisticsTasksOverTime()) {
stats_admin_events(1, "Statistics overtime... Aborting", null, __FILE__, __LINE__);
return;
}
}
$sock = new sockets();
$RecategorizeSecondsToWaitOverload = $sock->GET_INFO("RecategorizeSecondsToWaitOverload");
$RecategorizeMaxExecutionTime = $sock->GET_INFO("RecategorizeSecondsToWaitOverload");
if (!is_numeric($RecategorizeSecondsToWaitOverload)) {
$RecategorizeSecondsToWaitOverload = 30;
}
if (!is_numeric($RecategorizeMaxExecutionTime)) {
$RecategorizeMaxExecutionTime = 210;
}
if ($oldT1 > 1) {
$t = $oldT1;
} else {
$t = time();
}
$tables_days = $GLOBALS["Q"]->LIST_TABLES_DAYS();
$tables_hours = $GLOBALS["Q"]->LIST_TABLES_HOURS();
$tables_week = $GLOBALS["Q"]->LIST_TABLES_WEEKS();
$tables_blocked_week = $GLOBALS["Q"]->LIST_TABLES_WEEKS_BLOCKED();
$tables_blocked_days = $GLOBALS["Q"]->LIST_TABLES_DAYS_BLOCKED();
$CountUpdatedTables = 0;
while (list($website, $category) = each($websites)) {
if ($website == null) {
continue;
}
if ($category == null) {
continue;
}
reset($tables_days);
reset($tables_hours);
reset($tables_week);
while (list($num, $tablename) = each($tables_days)) {
$category = addslashes($category);
$CountUpdatedTables++;
$GLOBALS["Q"]->QUERY_SQL("UPDATE {$tablename} SET category='{$category}' WHERE familysite='{$website}'");
if (!$GLOBALS["Q"]->ok) {
writelogs_squid("Fatal: mysql error on table {$tablename} {$GLOBALS["Q"]->mysql_error}", __FUNCTION__, __FILE__, __LINE__, "categorize");
return;
}
}
while (list($num, $tablename) = each($tables_hours)) {
$category = addslashes($category);
$CountUpdatedTables++;
$GLOBALS["Q"]->QUERY_SQL("UPDATE {$tablename} SET category='{$category}' WHERE sitename='{$website}'");
if (!$GLOBALS["Q"]->ok) {
writelogs_squid("Fatal: mysql error on table {$tablename} {$GLOBALS["Q"]->mysql_error}", __FUNCTION__, __FILE__, __LINE__, "categorize");
return;
}
}
while (list($num, $tablename) = each($tables_week)) {
$category = addslashes($category);
$CountUpdatedTables++;
$GLOBALS["Q"]->QUERY_SQL("UPDATE {$tablename} SET category='{$category}' WHERE sitename='{$website}'");
if (!$GLOBALS["Q"]->ok) {
writelogs_squid("Fatal: mysql error on table {$tablename} {$GLOBALS["Q"]->mysql_error}", __FUNCTION__, __FILE__, __LINE__, "categorize");
return;
}
}
while (list($num, $tablename) = each($tables_blocked_days)) {
$category = addslashes($category);
$CountUpdatedTables++;
$GLOBALS["Q"]->QUERY_SQL("UPDATE {$tablename} SET category='{$category}' WHERE website='{$website}'");
if (!$GLOBALS["Q"]->ok) {
writelogs_squid("Fatal: mysql error on table {$tablename} {$GLOBALS["Q"]->mysql_error}", __FUNCTION__, __FILE__, __LINE__, "categorize");
return;
}
}
while (list($num, $tablename) = each($tables_blocked_week)) {
$category = addslashes($category);
$CountUpdatedTables++;
$GLOBALS["Q"]->QUERY_SQL("UPDATE {$tablename} SET category='{$category}' WHERE website='{$website}'");
if (!$GLOBALS["Q"]->ok) {
writelogs_squid("Fatal: mysql error on table {$tablename} {$GLOBALS["Q"]->mysql_error}", __FUNCTION__, __FILE__, __LINE__, "categorize");
return;
}
}
if (SquidStatisticsTasksOverTime()) {
stats_admin_events(1, "Statistics overtime... Aborting", null, __FILE__, __LINE__);
return;
}
$distanceInSeconds = round(abs(time() - $t));
$distanceInMinutes = round($distanceInSeconds / 60);
if ($distanceInMinutes > $RecategorizeMaxExecutionTime) {
$took = $unix->distanceOfTimeInWords($t, time());
writelogs_squid("Re-categorized websites task aborted (Max execution time {$RecategorizeMaxExecutionTime}Mn) ({$took})", __FUNCTION__, __FILE__, __LINE__, "categorize");
return;
}
}
$took = $unix->distanceOfTimeInWords($t, time());
stats_admin_events(2, count($websites) . "re-categorized websites updated in `{$CountUpdatedTables}` MySQL tables ({$took})", __FUNCTION__, __FILE__, __LINE__, "stats");
}
function Export($asPid = false)
{
$unix = new unix();
$restartProcess = false;
$nohup = $unix->find_program("nohup");
$php5 = $unix->LOCATE_PHP5_BIN();
$restart_cmd = trim("{$nohup} {$php5} " . __FILE__ . " --export >/dev/null 2>&1 &");
if ($asPid) {
$pidfile = "/etc/artica-postfix/pids/" . basename(__FILE__) . "." . __FUNCTION__ . ".pid";
$cachetime = "/etc/artica-postfix/pids/" . basename(__FILE__) . "." . __FUNCTION__ . ".time";
$unix = new unix();
$pid = @file_get_contents($pidfile);
if ($unix->process_exists($pid)) {
WriteMyLogs("Already executed PID:{$pid}, die()", __FUNCTION__, __FILE__, __LINE__);
die;
}
@file_put_contents($pidfile, getmypid());
}
export_deleted_categories();
$q = new mysql_squid_builder();
$tables = $q->LIST_TABLES_CATEGORIES();
while (list($table, $www) = each($tables)) {
$sql = "SELECT COUNT(zmd5) as tcount FROM {$table} WHERE sended=0 and enabled=1";
$ligne = mysql_fetch_array($q->QUERY_SQL($sql));
$prefix = "INSERT IGNORE INTO categorize (zmd5 ,pattern,zDate,uuid,category) VALUES";
if ($ligne["tcount"] > 0) {
echo "{$table} {$ligne["tcount"]} items to export\n";
$results = $q->QUERY_SQL("SELECT * FROM {$table} WHERE sended=0 and enabled=1");
while ($ligne2 = mysql_fetch_array($results, MYSQL_ASSOC)) {
$f[] = "('{$ligne2["zmd5"]}','{$ligne2["pattern"]}','{$ligne2["zDate"]}','{$uuid}','{$ligne2["category"]}')";
$c++;
if (count($f) > 1000) {
$q->QUERY_SQL($prefix . @implode(",", $f));
if (!$q->ok) {
echo $q->mysql_error . "\n";
return;
}
$f = array();
}
}
$q->QUERY_SQL("UPDATE {$table} SET sended=1 WHERE sended=0");
}
}
if (count($f) > 0) {
$q->QUERY_SQL($prefix . @implode(",", $f));
$f = array();
}
$ALLCOUNT = $q->COUNT_ROWS("categorize");
if ($ALLCOUNT > 4000) {
$restartProcess = true;
}
$sql = "SELECT * FROM categorize ORDER BY zDate DESC LIMIT 0,4000";
$results = $q->QUERY_SQL($sql, "artica_backup");
if (!$q->ok) {
echo $q->mysql_error . "\n{$sql}\n";
return;
}
while ($ligne = @mysql_fetch_array($results, MYSQL_ASSOC)) {
if ($ligne["category"] == null) {
continue;
}
if ($ligne["pattern"] == null) {
continue;
}
if ($ligne["zmd5"] == null) {
continue;
}
$logsExp[] = "{$ligne["pattern"]}:{$ligne["category"]}";
$array[$ligne["zmd5"]] = array("category" => $ligne["category"], "pattern" => $ligne["pattern"], "uuid" => $ligne["uuid"]);
}
if (!is_array($array)) {
WriteMyLogs("Nothing to export", __FUNCTION__, __FILE__, __LINE__);
return;
}
if (count($array) == 0) {
WriteMyLogs("Nothing to export", __FUNCTION__, __FILE__, __LINE__);
return;
}
WriteMyLogs("Exporting " . count($array) . " websites", __FUNCTION__, __FILE__, __LINE__);
$f = base64_encode(serialize($array));
$curl = new ccurl("http://www.artica.fr/shalla-orders.php");
$curl->parms["COMMUNITY_POST"] = $f;
if (!$curl->get()) {
writelogs("Failed exporting " . count($array) . " categorized websites to Artica cloud repository servers", __FUNCTION__, __FILE__, __LINE__);
$unix->send_email_events("Failed exporting " . count($array) . " categorized websites to Artica cloud repository servers", null, "proxy");
writelogs_squid("Failed exporting " . count($array) . " categorized websites to Artica cloud repository servers \"{$curl->error}\"", __FUNCTION__, __FILE__, __LINE__, "export");
return null;
}
if (preg_match("#<ANSWER>OK</ANSWER>#is", $curl->data)) {
WriteMyLogs("Exporting success " . count($array) . " websites", __FUNCTION__, __FILE__, __LINE__);
if (count($logsExp) < 10) {
$textadd = @implode(",", $logsExp);
}
writelogs_squid("Success exporting " . count($array) . " categorized websites to Artica cloud repository servers", __FUNCTION__, __FILE__, __LINE__, "export");
writelogs("Deleting websites...", __FUNCTION__, __FILE__, __LINE__);
while (list($md5, $datas) = each($array)) {
$sql = "DELETE FROM categorize WHERE zmd5='{$md5}'";
$q->QUERY_SQL($sql, "artica_backup");
}
if ($restartProcess) {
writelogs("{$restart_cmd}", __FUNCTION__, __FILE__, __LINE__);
shell_exec($restart_cmd);
} else {
$q->QUERY_SQL("OPTIMIZE TABLE categorize", "artica_backup");
}
} else {
writelogs_squid("Failed exporting " . count($array) . " categorized websites to Artica cloud repository servers \"{$curl->data}\"", __FUNCTION__, __FILE__, __LINE__, "export");
}
}
function categorize($day = null)
{
$unix = new unix();
$pidfile = "/etc/artica-postfix/pids/" . basename(__FILE__) . ".{$day}." . __FUNCTION__ . ".pid";
$pid = @file_get_contents($pidfile);
if ($unix->process_exists($pid, basename(__FILE__))) {
if ($GLOBALS["VERBOSE"]) {
echo "Already executed pid {$pid}\n";
}
return;
}
if ($day == null) {
return;
}
if (system_is_overloaded()) {
echo "Overloaded system, aborting task\n";
writelogs_squid("Overloaded system, aborting task", __FUNCTION__, __FILE__, __LINE__, "categorize");
return;
}
$daySource = $day;
$mypid = getmypid();
@file_put_contents($pidfile, $mypid);
$q = new mysql_squid_builder();
$time = strtotime("{$day} 00:00:00");
$day = str_replace("-", "", $day);
$table = "{$day}_hour";
$table_blocked = "{$day}_blocked";
$table_month = date("Ym", $time) . "_day";
$table_week = date("YW", $time) . "_week";
$table_week_blocked = date("YW", $time) . "_blocked_week";
$ipClass = new IP();
echo "{$daySource} time: {$time} Table day={$table}, table_blocked={$table_blocked}, table_month={$table_month}, table_week={$table_week}\n";
events("{$daySource} time: {$time} Table day={$table}, table_blocked={$table_blocked}, table_month={$table_month}, table_week={$table_week}");
$t = time();
$f = 0;
if (!$q->TABLE_EXISTS($table)) {
echo $table . " no such table\n";
return;
}
$sql = "SELECT sitename,category FROM {$table} GROUP BY sitename,category HAVING LENGTH(category)=0";
events("{$sql}");
$results = $q->QUERY_SQL($sql);
if (!$q->ok) {
writelogs_squid("Re-categorized table {$table} Query failed: `{$sql}` ({$q->mysql_error})", __FUNCTION__, __FILE__, __LINE__, "categorize");
}
if (!$q->TABLE_EXISTS($table_month)) {
if (!$q->CreateMonthTable($table_month)) {
writelogs_squid("failed Create {$table_month} table {$q->mysql_error}", __FUNCTION__, __FILE__, __LINE__, "categorize");
}
}
if (!$q->TABLE_EXISTS($table_week)) {
if (!$q->CreateWeekTable($table_week)) {
writelogs_squid("failed Create {$table_week} table {$q->mysql_error}", __FUNCTION__, __FILE__, __LINE__, "categorize");
}
}
$L = 0;
$q->QUERY_SQL("DELETE FROM `catztemp` WHERE `category`=''");
while ($ligne = @mysql_fetch_array($results, MYSQL_ASSOC)) {
$website = trim($ligne["sitename"]);
if (preg_match("#^www\\.(.+)#", $website, $re)) {
$q->QUERY_SQL("UPDATE {$table} SET sitename='{$re[1]}' WHERE sitename='{$website}'");
$q->QUERY_SQL("UPDATE {$table_month} SET sitename='{$re[1]}' WHERE sitename='{$website}'");
$q->QUERY_SQL("UPDATE {$table_week} SET sitename='{$re[1]}' WHERE sitename='{$website}'");
$q->QUERY_SQL("UPDATE {$table_blocked} SET website='{$re[1]}' WHERE sitename='{$website}'");
$q->QUERY_SQL("UPDATE {$table_week_blocked} SET website='{$re[1]}' WHERE sitename='{$website}'");
$website = $re[1];
}
if ($website == null) {
continue;
}
if ($ipClass->isValid($website)) {
$website = gethostbyaddr($website);
}
if (isset($GLOBALS[__FUNCTION__][$website])) {
$category = $GLOBALS[__FUNCTION__][$website];
}
$category = $q->GET_CATEGORIES($website);
if ($category == null) {
if ($ipClass->isValid($website)) {
$category = "ipaddr";
}
}
events("{$day}] {$website} = {$category}");
$GLOBALS[__FUNCTION__][$website] = $category;
if ($L > 500) {
if (system_is_overloaded()) {
ufdbguard_admin_events("Fatal: Overloaded system, die();", __FUNCTION__, __FILE__, __LINE__, "stats");
return;
}
$L = 0;
}
if ($category == null) {
continue;
}
$f++;
events("Update {$table} {$website} = {$category}");
$q->QUERY_SQL("UPDATE {$table} SET category='{$category}' WHERE sitename='{$website}'");
if (!$q->ok) {
writelogs_squid("Re-categorized table {$table} failed {$q->mysql_error}", __FUNCTION__, __FILE__, __LINE__, "categorize");
}
events("Update {$table_month} {$website} = {$category}");
$q->QUERY_SQL("UPDATE {$table_month} SET category='{$category}' WHERE sitename='{$website}'");
if (!$q->ok) {
writelogs_squid("Re-categorized table {$table_month} failed {$q->mysql_error}", __FUNCTION__, __FILE__, __LINE__, "categorize");
}
events("Update {$table_week} {$website} = {$category}");
$q->QUERY_SQL("UPDATE {$table_week} SET category='{$category}' WHERE sitename='{$website}'");
if (!$q->ok) {
writelogs_squid("Re-categorized table {$table_week} failed {$q->mysql_error}", __FUNCTION__, __FILE__, __LINE__, "categorize");
}
events("Update {$table_blocked} {$website} = {$category}");
$q->QUERY_SQL("UPDATE {$table_blocked} SET category='{$category}' WHERE website='{$website}'");
if (!$q->ok) {
writelogs_squid("Re-categorized table {$table_blocked} failed {$q->mysql_error}", __FUNCTION__, __FILE__, __LINE__, "categorize");
}
events("Update {$table_week_blocked} {$website} = {$category}");
if ($q->CreateWeekBlockedTable($table_week_blocked)) {
}
$q->QUERY_SQL("UPDATE {$table_week_blocked} SET category='{$category}' WHERE website='{$website}'");
if (!$q->ok) {
writelogs_squid("Re-categorized table {$table_week_blocked} failed {$q->mysql_error}", __FUNCTION__, __FILE__, __LINE__, "categorize");
}
}
$took = $unix->distanceOfTimeInWords($t, time());
if ($f > 0) {
ufdbguard_admin_events("Re-categorized table {$table} with {$f} websites ({$took})", __FUNCTION__, __FILE__, __LINE__, "statistics");
}
if ($GLOBALS["VERBOSE"]) {
echo "recategorize_singleday({$day}) FINISH\n";
}
}
function Export($asPid = false)
{
return;
$unix = new unix();
$restartProcess = false;
$nohup = $unix->find_program("nohup");
$php5 = $unix->LOCATE_PHP5_BIN();
$cachetime = "/etc/artica-postfix/pids/" . basename(__FILE__) . "." . __FUNCTION__ . ".time";
if ($GLOBALS["VERBOSE"]) {
echo "CacheTime: {$cachetime}\n";
}
$restart_cmd = trim("{$nohup} {$php5} " . __FILE__ . " --export >/dev/null 2>&1 &");
$sock = new sockets();
shell_exec(trim("{$nohup} {$php5} " . __FILE__ . " --export-not-categorized >/dev/null 2>&1 &"));
if ($asPid) {
$pidfile = "/etc/artica-postfix/pids/" . basename(__FILE__) . "." . __FUNCTION__ . ".pid";
$unix = new unix();
$pid = @file_get_contents($pidfile);
if ($unix->process_exists($pid, basename(__FILE__))) {
WriteMyLogs("Already executed PID:{$pid}, die()", __FUNCTION__, __FILE__, __LINE__);
die;
}
@file_put_contents($pidfile, getmypid());
}
$uuid = $unix->GetUniqueID();
if ($uuid == null) {
if ($GLOBALS["VERBOSE"]) {
echo "No system ID !\n";
}
return;
}
$q = new mysql_squid_builder();
$tables = $q->LIST_TABLES_CATEGORIES();
$c = 0;
while (list($table, $www) = each($tables)) {
$limit = null;
$limitupate = null;
if (!preg_match("#category_(.+?)\$#", $table)) {
continue;
}
if (!$q->TABLE_EXISTS($table)) {
continue;
}
$sql = "SELECT COUNT(zmd5) as tcount FROM {$table} WHERE sended=0 and enabled=1";
$q->CreateCategoryTable(null, $table);
$ligne = mysql_fetch_array($q->QUERY_SQL($sql));
if (!$q->ok) {
writelogs("{$table} {$q->mysql_error}", __FUNCTION__, __FILE__, __LINE__);
return;
}
$prefix = "INSERT IGNORE INTO categorize (zmd5 ,pattern,zDate,uuid,category) VALUES";
if ($ligne["tcount"] > 0) {
writelogs("{$table} {$ligne["tcount"]} items to export", __FUNCTION__, __FILE__, __LINE__);
if ($ligne["tcount"] > 5000) {
$limit = "LIMIT 0,5000";
$limitupate = "LIMIT 5000";
}
$results = $q->QUERY_SQL("SELECT * FROM {$table} WHERE sended=0 AND enabled=1 {$limit}");
while ($ligne2 = mysql_fetch_array($results, MYSQL_ASSOC)) {
$md5 = md5("{$ligne2["category"]}{$ligne2["pattern"]}");
$f[] = "('{$md5}','{$ligne2["pattern"]}','{$ligne2["zDate"]}','{$uuid}','{$ligne2["category"]}')";
$c++;
if (count($f) > 1000) {
$q->QUERY_SQL($prefix . @implode(",", $f));
if (!$q->ok) {
echo $q->mysql_error . "\n";
return;
}
$f = array();
}
}
$q->QUERY_SQL("UPDATE {$table} SET sended=1 WHERE sended=0 {$limitupate}");
}
}
if (count($f) > 0) {
$q->QUERY_SQL($prefix . @implode(",", $f));
$f = array();
}
$ALLCOUNT = $q->COUNT_ROWS("categorize");
if ($GLOBALS["VERBOSE"]) {
echo "Total row in categorize table: {$ALLCOUNT}\n";
}
if ($ALLCOUNT > 2000) {
$restartProcess = true;
}
$sql = "SELECT * FROM categorize ORDER BY zDate DESC LIMIT 0,2000";
if ($GLOBALS["VERBOSE"]) {
echo "Execute query\n";
}
$results = $q->QUERY_SQL($sql, "artica_backup");
if (!$q->ok) {
echo $q->mysql_error . "\n{$sql}\n";
return;
}
while ($ligne = @mysql_fetch_array($results, MYSQL_ASSOC)) {
if ($ligne["category"] == null) {
continue;
}
if ($ligne["pattern"] == null) {
continue;
}
if ($ligne["zmd5"] == null) {
continue;
}
$logsExp[] = "{$ligne["pattern"]}:{$ligne["category"]}";
$array[$ligne["zmd5"]] = array("category" => $ligne["category"], "pattern" => $ligne["pattern"], "uuid" => $ligne["uuid"]);
}
if (!isset($array)) {
WriteMyLogs("Nothing to export", __FUNCTION__, __FILE__, __LINE__);
return;
}
if (!is_array($array)) {
WriteMyLogs("Nothing to export", __FUNCTION__, __FILE__, __LINE__);
return;
}
if (count($array) == 0) {
WriteMyLogs("Nothing to export", __FUNCTION__, __FILE__, __LINE__);
return;
}
$WHITELISTED["1636b7346f2e261c5b21abfcaef45a69"] = true;
$WHITELISTED["8cdd119c-2dc1-452d-b9d0-451c6046464f"] = true;
if (!isset($WHITELISTED[$uuid])) {
if (count($array) > 500) {
$q->QUERY_SQL("TRUNCATE TABLE categorize_delete");
WriteMyLogs("Too much categories to export " . count($array) . ">500, aborting", __FUNCTION__, __FILE__, __LINE__, "export");
}
}
WriteMyLogs("Exporting " . count($array) . " websites", __FUNCTION__, __FILE__, __LINE__);
$f = base64_encode(serialize($array));
$unix = new unix();
$URIBASE = $unix->MAIN_URI();
if ($GLOBALS["VERBOSE"]) {
echo "Sending " . strlen($f) . " bytes to repository server\n";
}
$curl = new ccurl("{$URIBASE}/shalla-orders.php", false);
$curl->parms["COMMUNITY_POST"] = $f;
if (!$curl->get()) {
writelogs("Failed exporting " . count($array) . " categorized websites to Artica cloud repository servers", __FUNCTION__, __FILE__, __LINE__);
squid_admin_mysql("Failed exporting " . count($array) . " categorized websites to Artica cloud repository servers \"{$curl->error}\"", null, __FILE__, __LINE__, "export");
return null;
}
if (preg_match("#<ANSWER>OK</ANSWER>#is", $curl->data)) {
squid_admin_mysql(2, "Exporting success " . count($array) . " websites", null, __FILE__, __LINE__);
if (count($logsExp) < 10) {
$textadd = @implode(",", $logsExp);
}
$curl = new ccurl("{$URIBASE}/webfilters-instant.php?checks=yes", false);
$curl->NoHTTP_POST = true;
if (!$curl->get()) {
squid_admin_mysql(1, "Failed to order to build webfilter instant with HTTP ERROR: `{$curl->error}`", null, __FILE__, __LINE__, "export");
}
if (preg_match("#<ANSWER>OK</ANSWER>#is", $curl->data)) {
writelogs_squid("Success to order to build webfilter instant", __FUNCTION__, __FILE__, __LINE__, "export");
} else {
writelogs_squid("Failed to order to build webfilter instant ANSWER NOT OK in server response.", __FUNCTION__, __FILE__, __LINE__, "export");
if ($GLOBALS["VERBOSE"]) {
echo $curl->data;
}
}
writelogs("Deleting websites...", __FUNCTION__, __FILE__, __LINE__);
while (list($md5, $datas) = each($array)) {
$sql = "DELETE FROM categorize WHERE zmd5='{$md5}'";
$q->QUERY_SQL($sql, "artica_backup");
}
if ($restartProcess) {
writelogs("{$restart_cmd}", __FUNCTION__, __FILE__, __LINE__);
shell_exec($restart_cmd);
} else {
$q->QUERY_SQL("OPTIMIZE TABLE categorize", "artica_backup");
}
} else {
WriteMyLogs("Failed exporting " . count($array) . " categorized websites to Artica cloud repository servers \"{$curl->data}\"", __FUNCTION__, __FILE__, __LINE__, "export");
}
}
function summarize_days()
{
if (isset($GLOBALS["summarize_days_executed"])) {
return;
}
$GLOBALS["summarize_days_executed"] = true;
$unix = new unix();
if (!$GLOBALS["VERBOSE"]) {
$timefile = "/etc/artica-postfix/pids/" . basename(__FILE__) . "." . __FUNCTION__ . ".time";
if ($unix->file_time_min($timefile) < 480) {
return;
}
@unlink($timefile);
@file_put_contents($timefile, time());
}
$q = new mysql_squid_builder();
$q->CheckTables();
$sql = "SELECT tablename,zDate,DATE_FORMAT(zDate,'%Y%m%d') as dpref FROM tables_day";
$results = $q->QUERY_SQL($sql);
if (!$q->ok) {
writelogs_squid("Fatal: {$q->mysql_error} on `tables_day`", __FUNCTION__, __FILE__, __LINE__, "stats");
return;
}
$TOTAL = mysql_num_rows($results);
$c = 0;
while ($ligne = @mysql_fetch_array($results, MYSQL_ASSOC)) {
$dpref = $ligne["dpref"];
$c++;
$prc = round($c / $TOTAL * 100, 2);
events("Scanning: {$ligne["tablename"]} {$prc}% {$ligne["zDate"]}");
_summarize_days($dpref, $ligne["tablename"], $ligne["zDate"]);
}
writelogs_squid("Success Summarize " . mysql_num_rows($results) . " day tables", __FUNCTION__, __FILE__, __LINE__, "stats");
}
function perform($tablesource, $nexttable, $dayNum)
{
if (!$GLOBALS["Q"]->CreateMonthTable($nexttable)) {
if ($GLOBALS["VERBOSE"]) {
echo "{$nexttable}, failed\n";
}
return false;
}
$accounts = $GLOBALS["Q"]->ACCOUNTS_ISP();
$GLOBALS["Q"]->QUERY_SQL("UPDATE `{$tablesource}` SET MAC='' WHERE MAC='00:00:00:00:00:00'");
$sql = "SELECT SUM(size) as size,SUM(hits) as hits,\n\tfamilysite,client,account,remote_ip\n\t,MAC,country,uid,category,cached FROM `{$tablesource}` \n\tGROUP BY familysite,client,account,remote_ip,MAC,country,uid,category,cached";
if ($GLOBALS["VERBOSE"]) {
echo "{$nexttable}, QUERY\n{$sql}\n";
}
$results = $GLOBALS["Q"]->QUERY_SQL($sql);
if (!$GLOBALS["Q"]->ok) {
writelogs_squid("Fatal: {$GLOBALS["Q"]->mysql_error} on `{$tablesource}`\n" . @implode("\n", $GLOBALS["REPAIR_MYSQL_TABLE"]), __FUNCTION__, __FILE__, __LINE__, "stats");
return false;
}
$prefix = "INSERT IGNORE INTO {$nexttable} (\n\t`zMD5`,`day`,`size`,`hits`,`familysite`,`client`,`account`,`remote_ip`\n\t,`MAC`,`country`,`uid`,`category`,`cached`\n\t) VALUES ";
$d = 0;
$TOT = mysql_num_rows($results);
if ($GLOBALS["VERBOSE"]) {
echo "{$nexttable}, LOOP ON {$TOT}\n";
}
while ($ligne = @mysql_fetch_array($results, MYSQL_ASSOC)) {
$zMD5 = md5(serialize($ligne));
while (list($key, $value) = each($ligne)) {
$ligne[$key] = mysql_escape_string2($value);
}
$d++;
$f[] = "('{$zMD5}','{$dayNum}','{$ligne["size"]}','{$ligne["hits"]}','{$ligne["familysite"]}','{$ligne["client"]}','{$ligne["account"]}',\n\t\t'{$ligne["remote_ip"]}','{$ligne["MAC"]}','{$ligne["country"]}','{$ligne["uid"]}','{$ligne["category"]}','{$ligne["cached"]}')";
if (count($f) > 1000) {
if ($GLOBALS["VERBOSE"]) {
echo "{$d} - " . ($TOT - $d) . "\n";
}
$GLOBALS["Q"]->QUERY_SQL($prefix . @implode(",", $f));
$f = array();
if (!$GLOBALS["Q"]->ok) {
writelogs_squid("Fatal: {$GLOBALS["Q"]->mysql_error} on `{$nexttable}`", __FUNCTION__, __FILE__, __LINE__, "stats");
return;
}
}
}
if (count($f) > 0) {
$GLOBALS["Q"]->QUERY_SQL($prefix . @implode(",", $f));
if (!$GLOBALS["Q"]->ok) {
writelogs_squid("Fatal: {$GLOBALS["Q"]->mysql_error} on `{$nexttable}`", __FUNCTION__, __FILE__, __LINE__, "stats");
return;
}
}
return true;
}
function _table_hours_perform($tablename)
{
if (!isset($GLOBALS["Q"])) {
$GLOBALS["Q"] = new mysql_squid_builder();
}
if (!preg_match("#squidhour_([0-9]+)#", $tablename, $re)) {
writelogs_squid("NOT AN HOUR TABLE `{$tablename}`", __FUNCTION__, __FILE__, __LINE__, "stats");
return;
}
$hour = $re[1];
$year = substr($hour, 0, 4);
$month = substr($hour, 4, 2);
$day = substr($hour, 6, 2);
$compressed = false;
$f = array();
$dansguardian_table = "dansguardian_events_{$year}{$month}{$day}";
$accounts = $GLOBALS["Q"]->ACCOUNTS_ISP();
if (!$GLOBALS["Q"]->Check_dansguardian_events_table($dansguardian_table)) {
return false;
}
if (!$GLOBALS["Q"]->FIELD_EXISTS("{$tablename}", "category")) {
$GLOBALS["Q"]->QUERY_SQL("ALTER TABLE `{$tablename}` ADD `category` VARCHAR( 60 ) NOT NULL ,ADD INDEX ( `category` )");
}
$sql = "SELECT COUNT(ID) as hits,SUM(QuerySize) as QuerySize,DATE_FORMAT(zDate,'%Y-%m-%d %H:00:00') as zDate,sitename,uri,TYPE,REASON,CLIENT,uid,remote_ip,country,cached,MAC,hostname,category FROM {$tablename} GROUP BY sitename,uri,TYPE,REASON,CLIENT,uid,remote_ip,country,cached,MAC,zDate,hostname,category";
if ($GLOBALS["VERBOSE"]) {
echo $sql . "\n";
}
$results = $GLOBALS["Q"]->QUERY_SQL($sql);
if (!$GLOBALS["Q"]->ok) {
stats_admin_events(1, "Fatal: {$GLOBALS["Q"]->mysql_error} on `{$tablename}`", @implode("\n", $GLOBALS["REPAIR_MYSQL_TABLE"]), __FILE__, __LINE__, "stats");
if (strpos(" {$GLOBALS["Q"]->mysql_error}", "is marked as crashed and should be repaired") > 0) {
$q1 = new mysql();
$q1->REPAIR_TABLE("squidlogs", $tablename);
stats_admin_events(2, "try to repair table `{$tablename}`", $GLOBALS["REPAIR_MYSQL_TABLE"], __FILE__, __LINE__, "stats");
}
return false;
}
$prefix = "INSERT IGNORE INTO {$dansguardian_table} (sitename,uri,TYPE,REASON,CLIENT,MAC,zDate,zMD5,uid,remote_ip,country,QuerySize,hits,cached,hostname,account,category) VALUES ";
$ip = new IP();
$SUM = mysql_num_rows($results);
$d = 0;
while ($ligne = @mysql_fetch_array($results, MYSQL_ASSOC)) {
$zmd = array();
while (list($key, $value) = each($ligne)) {
$ligne[$key] = mysql_escape_string2($value);
$zmd[] = $value;
}
$zMD5 = md5(@implode("", $zmd));
$accountclient = null;
if (isset($accounts[$ligne["CLIENT"]])) {
$accountclient = $accounts[$ligne["CLIENT"]];
}
$uid = $ligne["uid"];
if ($uid == null) {
$uid = $GLOBALS["Q"]->MacToUid($ligne["MAC"]);
if (is_numeric($uid)) {
$uid = null;
}
}
if ($uid == null) {
$uid = $GLOBALS["Q"]->IpToUid($ligne["CLIENT"]);
if (is_numeric($uid)) {
$uid = null;
}
}
$uid = mysql_escape_string2($uid);
$hostname = $ligne["hostname"];
if ($hostname == null) {
$hostname = $GLOBALS["Q"]->MacToHost($ligne["MAC"]);
if (is_numeric($uid)) {
$uid = null;
}
}
if ($hostname == null) {
$hostname = $GLOBALS["Q"]->IpToHost($ligne["CLIENT"]);
if (is_numeric($uid)) {
$uid = null;
}
}
if ($ip->isValid($hostname)) {
$hostname = null;
}
$hostname = mysql_escape_string2($hostname);
$d++;
$f[] = "('{$ligne["sitename"]}','{$ligne["uri"]}','{$ligne["TYPE"]}','{$ligne["REASON"]}','{$ligne["CLIENT"]}','{$ligne["MAC"]}','{$ligne["zDate"]}','{$zMD5}','{$uid}','{$ligne["remote_ip"]}','{$ligne["country"]}','{$ligne["QuerySize"]}','{$ligne["hits"]}','{$ligne["cached"]}','{$hostname}','{$accountclient}','{$ligne["category"]}')";
if (count($f) > 500) {
ToSyslog("{$dansguardian_table}: {$d}/{$SUM}");
$GLOBALS["Q"]->UncompressTable($dansguardian_table);
$GLOBALS["Q"]->QUERY_SQL($prefix . @implode(",", $f));
$f = array();
if (!$GLOBALS["Q"]->ok) {
stats_admin_events(1, "MySQL Error ", "{$GLOBALS["Q"]->mysql_error} on `{$dansguardian_table}`", __FILE__, __LINE__, "stats");
return;
}
}
}
if (count($f) > 0) {
$GLOBALS["Q"]->UncompressTable($dansguardian_table);
$GLOBALS["Q"]->QUERY_SQL($prefix . @implode(",", $f));
if (!$GLOBALS["Q"]->ok) {
stats_admin_events(1, "MySQL Error ", "{$GLOBALS["Q"]->mysql_error} on `{$dansguardian_table}`", __FILE__, __LINE__, "stats");
return;
}
$squid_stats_tools = new squid_stats_tools();
$squid_stats_tools->NoCategorize = true;
$squid_stats_tools->check_table_days();
}
stats_admin_events(2, "Success updating {$dansguardian_table} with {$d} events", __FILE__, __LINE__);
return true;
}
function _table_hours_perform($tablename)
{
if (!isset($GLOBALS["Q"])) {
$GLOBALS["Q"] = new mysql_squid_builder();
}
if (!preg_match("#ngixattck_([0-9]+)#", $tablename, $re)) {
writelogs_squid("NOT AN HOUR TABLE `{$tablename}`", __FUNCTION__, __FILE__, __LINE__, "stats");
return;
}
$q = new mysql_squid_builder();
$time = $q->TIME_FROM_HOUR_TEMP_TABLE($tablename);
if ($GLOBALS["VERBOSE"]) {
echo "{$tablename} - {$time} - " . date("Y-m-d", $time) . "\n";
}
$sql = "SELECT HOUR(zDate) as `hour`,COUNT(keyr) as hits,`ipaddr`,`familysite`,`hostname`,`country`,`servername`\n\tFROM `{$tablename}` GROUP BY `hour`,`ipaddr`,`familysite`,`hostname`,`country`,`servername`";
if ($GLOBALS["VERBOSE"]) {
echo $sql . "\n";
}
$results = $GLOBALS["Q"]->QUERY_SQL($sql);
if (!$GLOBALS["Q"]->ok) {
writelogs_squid("Fatal: {$GLOBALS["Q"]->mysql_error} on `{$tablename}`\n" . @implode("\n", $GLOBALS["REPAIR_MYSQL_TABLE"]), __FUNCTION__, __FILE__, __LINE__, "stats");
if (strpos(" {$GLOBALS["Q"]->mysql_error}", "is marked as crashed and should be repaired") > 0) {
$q1 = new mysql();
writelogs_squid("try to repair table `{$tablename}`", __FUNCTION__, __FILE__, __LINE__, "stats");
$q1->REPAIR_TABLE("squidlogs", $tablename);
writelogs_squid(@implode("\n", $GLOBALS["REPAIR_MYSQL_TABLE"]), __FUNCTION__, __FILE__, __LINE__, "stats");
}
return false;
}
if (mysql_num_rows($results) == 0) {
return true;
}
$timekey = date('Ymd', $time);
$tabledest = "ngixattckd_{$timekey}";
if (!$q->check_nginx_attacks_DAY($timekey)) {
return false;
}
$prefix = "INSERT IGNORE INTO {$tabledest} (zmd5,`hour`,`ipaddr`,`familysite`,`hostname`,`country`,`servername`,`hits`) VALUES ";
$d = 0;
while ($ligne = @mysql_fetch_array($results, MYSQL_ASSOC)) {
$zmd = array();
while (list($key, $value) = each($ligne)) {
$ligne[$key] = mysql_escape_string2($value);
$zmd[] = $value;
}
$zMD5 = md5(@implode("", $zmd));
$f[] = "('{$zMD5}','{$ligne["hour"]}','{$ligne["ipaddr"]}','{$ligne["familysite"]}','{$ligne["hostname"]}','{$ligne["country"]}','{$ligne["servername"]}','{$ligne["hits"]}')";
if (count($f) > 500) {
$q->QUERY_SQL($prefix . @implode(",", $f));
$f = array();
if (!$q->ok) {
writelogs_squid("Fatal: {$q->mysql_error} on `{$tabledest}`", __FUNCTION__, __FILE__, __LINE__, "stats");
return;
}
}
}
if (count($f) > 0) {
$q->QUERY_SQL($prefix . @implode(",", $f));
$f = array();
if (!$q->ok) {
writelogs_squid("Fatal: {$q->mysql_error} on `{$tabledest}`", __FUNCTION__, __FILE__, __LINE__, "stats");
return;
}
}
return true;
}
function _youtube_dayz($sourcetable)
{
$q = new mysql_squid_builder();
if (!$q->TABLE_EXISTS($sourcetable)) {
return true;
}
$sql = "SELECT zDate,ipaddr,hostname,uid,MAC,account,youtubeid,SUM(hits) as hits FROM {$sourcetable}\n\tGROUP BY zDate,ipaddr,hostname,uid,MAC,account,youtubeid";
$results = $q->QUERY_SQL($sql);
if (!$q->ok) {
writelogs_squid("Fatal: {$q->mysql_error} on `{$sourcetable}`", __FUNCTION__, __FILE__, __LINE__, "stats");
return;
}
youtube_events("_youtube_dayz(): {$sourcetable} " . mysql_num_rows($results) . " rows");
$f = array();
while ($ligne = @mysql_fetch_array($results, MYSQL_ASSOC)) {
$f[] = "('{$ligne["zDate"]}','{$ligne["hits"]}','{$ligne["ipaddr"]}','{$ligne["hostname"]}','{$ligne["uid"]}','{$ligne["MAC"]}','{$ligne["youtubeid"]}')";
}
$prefix = "INSERT IGNORE INTO youtube_dayz (zDate,hits,ipaddr,hostname,uid,MAC,youtubeid) VALUES ";
if (count($f) > 0) {
$q->QUERY_SQL($prefix . @implode(",", $f));
if (!$q->ok) {
writelogs_squid("Fatal: {$q->mysql_error} on `{$sourcetable}`", __FUNCTION__, __FILE__, __LINE__, "stats");
return;
}
}
return true;
}
function events_tail($text)
{
if (function_exists("debug_backtrace")) {
$trace = debug_backtrace();
if (isset($trace[1])) {
$sourcefile = basename($trace[1]["file"]);
$sourcefunction = $trace[1]["function"];
$sourceline = $trace[1]["line"];
}
}
if ($GLOBALS["VERBOSE"]) {
echo "{$sourcefunction}:: {$text} (in line {$sourceline})\n";
}
writelogs_squid($text, $sourcefunction, __FILE__, $sourceline, "stats", true);
}
if (!$q->ok) {
writelogs_squid($q->mysql_error, __FUNCTION__, __FILE__, __LINE__, "categorize");
}
}
while (list($num, $table) = each($table_days)) {
if ($GLOBALS["VERBOSE"]) {
echo "Update {$table}\n";
}
$q->QUERY_SQL("UPDATE {$table} SET category='{$categories}' WHERE sitename='{$website}'");
if (!$q->ok) {
writelogs_squid($q->mysql_error, __FUNCTION__, __FILE__, __LINE__, "categorize");
}
}
$took = $unix->distanceOfTimeInWords($t, time());
$q->QUERY_SQL("DELETE FROM categorize_changes WHERE `zmd5`='{$ligne["zmd5"]}'");
writelogs_squid("{$website}/{$categories} has been re-categorized in " . count($table_days) . " days tables and " . count($table_hours) . " hours tables ({$took})", __FUNCTION__, __FILE__, __LINE__, "categorize");
}
echo "Finish...\n";
function events($text)
{
if ($GLOBALS["VERBOSE"]) {
echo $text . "\n";
}
$common = "/var/log/artica-postfix/squid.stats.log";
$size = @filesize($common);
if ($size > 100000) {
@unlink($common);
}
$pid = getmypid();
$date = date("Y-m-d H:i:s");
$GLOBALS["CLASS_UNIX"]->events(basename(__FILE__) . "{$date} {$text}");
