function ScanDays($onlyTable = false)
{
$q = new mysql_squid_builder(true);
$ARRAY_DAYS = array();
$tables = $q->LIST_TABLES_dansguardian_events();
while (list($tablename, $line) = each($tables)) {
$dayTime = $q->TIME_FROM_DANSGUARDIAN_EVENTS_TABLE($tablename);
$day = date("Y-m-d", $dayTime);
$ARRAY_DAYS[$day] = $dayTime;
$TABLES[$tablename] = true;
}
$tables = $q->LIST_TABLES_HOURS();
while (list($tablename, $line) = each($tables)) {
$dayTime = $q->TIME_FROM_HOUR_TABLE($tablename);
$day = date("Y-m-d", $dayTime);
$ARRAY_DAYS[$day] = $dayTime;
$TABLES[$tablename] = true;
}
$tables = $q->LIST_TABLES_YOUTUBE_DAYS();
//youtubeday_
while (list($tablename, $line) = each($tables)) {
$dayTime = $q->TIME_FROM_YOUTUBE_DAY_TABLE($tablename);
$day = date("Y-m-d", $dayTime);
$ARRAY_DAYS[$day] = $dayTime;
$TABLES[$tablename] = true;
}
$tables = $q->LIST_TABLES_USERSIZED();
//youtubeday_
while (list($tablename, $line) = each($tables)) {
$dayTime = $q->TIME_FROM_USERSIZED_TABLE($tablename);
$day = date("Y-m-d", $dayTime);
$ARRAY_DAYS[$day] = $dayTime;
$TABLES[$tablename] = true;
}
$tables = $q->LIST_TABLES_QUOTADAY();
//youtubeday_
while (list($tablename, $line) = each($tables)) {
$dayTime = $q->TIME_FROM_QUOTADAY_TABLE($tablename);
$day = date("Y-m-d", $dayTime);
$ARRAY_DAYS[$day] = $dayTime;
$TABLES[$tablename] = true;
}
$tables = $q->LIST_CAT_FAMDAY();
//youtubeday_
while (list($tablename, $line) = each($tables)) {
$dayTime = $q->TIME_FROM_CAT_FAMDAY_TABLE($tablename);
$day = date("Y-m-d", $dayTime);
$ARRAY_DAYS[$day] = $dayTime;
$TABLES[$tablename] = true;
}
$prefix = "INSERT IGNORE INTO tables_day (tablename,zDate) VALUES ";
while (list($day, $dayTime) = each($ARRAY_DAYS)) {
$tablename = "dansguardian_events_" . date("Ymd", $dayTime);
$f[] = "('{$tablename}','{$day}')";
}
if (count($f) > 0) {
$q->QUERY_SQL($prefix . @implode(",", $f));
if (!$q->ok) {
if ($GLOBALS["VERBOSE"]) {
echo "Fatal {$q->mysql_error}\n";
}
squid_admin_purge(0, "Fatal {$q->mysql_error}", "Backup process cannot be performed", __FILE__, __LINE__);
ufdbguard_admin_events("Fatal {$q->mysql_error}", __FUNCTION__, __FILE__, __LINE__, "backup");
return false;
}
}
if ($onlyTable) {
return $TABLES;
}
return true;
}
function updates_retranslation($MAC, $uid)
{
$GLOBALS["Q"] = new mysql_squid_builder();
if ($GLOBALS["VERBOSE"]) {
"echo Loading...\n";
}
$unix = new unix();
if ($GLOBALS["VERBOSE"]) {
"echo Loading done...\n";
}
$pidfile = "/etc/artica-postfix/pids/" . basename(__FILE__) . "." . md5($MAC . $uid) . ".pid";
$timefile = "/etc/artica-postfix/pids/" . basename(__FILE__) . "." . md5($MAC . $uid) . ".time";
$pid = @file_get_contents($pidfile);
if (!$GLOBALS["FORCE"]) {
if ($pid < 100) {
$pid = null;
}
$unix = new unix();
if ($unix->process_exists($pid, basename(__FILE__))) {
if ($GLOBALS["VERBOSE"]) {
echo "Already executed pid {$pid}\n";
}
return;
}
$mypid = getmypid();
@file_put_contents($pidfile, $mypid);
}
$q = new mysql_squid_builder();
$sql = "UPDATE youtube_all SET uid='{$uid}' WHERE MAC='{$MAC}'";
$q->QUERY_SQL($sql);
$sql = "UPDATE UserAuthDaysGrouped SET uid='{$uid}' WHERE MAC='{$MAC}'";
$q->QUERY_SQL($sql);
$sql = "UPDATE UserAuthDays SET uid='{$uid}' WHERE MAC='{$MAC}'";
$q->QUERY_SQL($sql);
$TABLES = $q->LIST_TABLES_QUOTADAY();
while (list($tablename, $rows) = each($TABLES)) {
$sql = "UPDATE `{$tablename}` SET uid='{$uid}' WHERE MAC='{$MAC}'";
$q->QUERY_SQL($sql);
if (!$q->ok) {
echo $q->mysql_error;
}
}
$TABLES = $q->LIST_TABLES_QUOTAMONTH();
while (list($tablename, $rows) = each($TABLES)) {
$sql = "UPDATE `{$tablename}` SET uid='{$uid}' WHERE MAC='{$MAC}'";
$q->QUERY_SQL($sql);
if (!$q->ok) {
echo $q->mysql_error;
}
}
$TABLES = $q->LIST_TABLES_dansguardian_events();
while (list($tablename, $rows) = each($TABLES)) {
$sql = "UPDATE `{$tablename}` SET uid='{$uid}' WHERE MAC='{$MAC}'";
$q->QUERY_SQL($sql);
if (!$q->ok) {
echo $q->mysql_error;
}
}
$TABLES = $q->LIST_TABLES_USERSIZED();
while (list($tablename, $rows) = each($TABLES)) {
$sql = "UPDATE `{$tablename}` SET uid='{$uid}' WHERE MAC='{$MAC}'";
$q->QUERY_SQL($sql);
if (!$q->ok) {
echo $q->mysql_error;
}
}
$TABLES = $q->LIST_TABLES_YOUTUBE_HOURS();
while (list($tablename, $rows) = each($TABLES)) {
$sql = "UPDATE `{$tablename}` SET uid='{$uid}' WHERE MAC='{$MAC}'";
$q->QUERY_SQL($sql);
if (!$q->ok) {
echo $q->mysql_error;
}
}
$TABLES = $q->LIST_TABLES_YOUTUBE_DAYS();
while (list($tablename, $rows) = each($TABLES)) {
$sql = "UPDATE `{$tablename}` SET uid='{$uid}' WHERE MAC='{$MAC}'";
$q->QUERY_SQL($sql);
if (!$q->ok) {
echo $q->mysql_error;
}
}
$TABLES = $q->LIST_TABLES_YOUTUBE_WEEK();
while (list($tablename, $rows) = each($TABLES)) {
$sql = "UPDATE `{$tablename}` SET uid='{$uid}' WHERE MAC='{$MAC}'";
$q->QUERY_SQL($sql);
if (!$q->ok) {
echo $q->mysql_error;
}
}
$TABLES = $q->LIST_TABLES_SEARCHWORDS_DAY();
while (list($tablename, $rows) = each($TABLES)) {
$sql = "UPDATE `{$tablename}` SET uid='{$uid}' WHERE MAC='{$MAC}'";
$q->QUERY_SQL($sql);
if (!$q->ok) {
echo $q->mysql_error;
}
}
$TABLES = $q->LIST_TABLES_SEARCHWORDS_HOURS();
while (list($tablename, $rows) = each($TABLES)) {
$sql = "UPDATE `{$tablename}` SET uid='{$uid}' WHERE MAC='{$MAC}'";
$q->QUERY_SQL($sql);
if (!$q->ok) {
echo $q->mysql_error;
}
}
$TABLES = $q->LIST_TABLES_MONTH();
while (list($tablename, $rows) = each($TABLES)) {
$sql = "UPDATE `{$tablename}` SET uid='{$uid}' WHERE MAC='{$MAC}'";
$q->QUERY_SQL($sql);
if (!$q->ok) {
echo $q->mysql_error;
}
}
}
function repair_from_sources_tables()
{
$unix = new unix();
$pidfile = "/etc/artica-postfix/pids/" . basename(__FILE__) . "." . __FUNCTION__ . ".pid";
$timefile = "/etc/artica-postfix/pids/" . basename(__FILE__) . "." . __FUNCTION__ . ".time";
if ($GLOBALS["VERBOSE"]) {
echo "time: {$timefile}\n";
}
$pid = @file_get_contents($pidfile);
if (!$GLOBALS["VERBOSE"]) {
if (!$GLOBALS["FORCE"]) {
if ($unix->process_exists($pid, basename(__FILE__))) {
if ($GLOBALS["VERBOSE"]) {
echo "Already executed pid {$pid}\n";
}
return;
}
if ($pid < 100) {
$pid = null;
}
$mypid = getmypid();
@file_put_contents($pidfile, $mypid);
}
}
if ($GLOBALS["FORCE_TIME"]) {
$timeexec = $unix->file_time_min($timefile);
if ($timeexec < 240) {
return;
}
}
@unlink($timefile);
@file_put_contents($timefile, time());
$Prefix = "/usr/share/artica-postfix";
$php5 = $unix->LOCATE_PHP5_BIN();
$nohup = $unix->find_program("nohup");
$EXEC_NICE = $unix->EXEC_NICE();
$q = new mysql_squid_builder();
$C = 0;
$array = $q->LIST_TABLES_dansguardian_events();
$current = "dansguardian_events_" . date("Ymd");
while (list($tablename, $none) = each($array)) {
if ($tablename == $current) {
continue;
}
$time = $q->TIME_FROM_DANSGUARDIAN_EVENTS_TABLE($tablename);
$xtime = date("Y-m-d", $time);
$hour_table = date("Ymd", $time) . "_hour";
$member_table = date("Ymd", $time) . "_members";
$SUM_SOURCE = $q->COUNT_ROWS($tablename);
if ($SUM_SOURCE == 0) {
continue;
}
$SUM_DEST = $q->COUNT_ROWS($hour_table);
$PERC = $SUM_DEST / $SUM_SOURCE * 100;
$PERC = intval($PERC);
echo "{$xtime}] {$SUM_SOURCE} - {$SUM_DEST} = {$PERC}% - {$tablename}\n";
if ($PERC < 5) {
if (!$q->CreateHourTable($hour_table)) {
echo "{$xtime}] {$tablename} unable to create {$hour_table}\n";
continue;
}
_repair_from_sources_tables($tablename, $hour_table);
$q->QUERY_SQL("UPDATE tables_day SET `totalsize`='0',`requests`=0,`MembersCount`=0,`month_flow`=0,weekdone=0,weekbdone=0 WHERE tablename='{$tablename}'");
$C++;
}
$ligne1 = mysql_fetch_array($q->QUERY_SQL("SELECT SUM(hits) as hits FROM {$tablename}"));
if (!$q->ok) {
echo $q->mysql_error;
}
$SumDehits_src = $ligne1["hits"];
$ligne1 = mysql_fetch_array($q->QUERY_SQL("SELECT SUM(hits) as hits FROM {$member_table}"));
$SumDehits_dest = $ligne1["hits"];
$PERC = $SumDehits_dest / $SumDehits_src * 100;
$PERC = intval($PERC);
echo "{$xtime}] {$SumDehits_src} - {$SumDehits_dest} = {$PERC}% - {$member_table}\n";
if ($PERC < 90) {
if (!$q->CreateMembersDayTable($hour_table)) {
echo "{$xtime}] {$tablename} unable to create {$hour_table}\n";
continue;
}
_repair_members_sources_tables($tablename, $member_table);
$C++;
}
}
if ($C > 0) {
shell_exec(trim("{$EXEC_NICE} {$php5} {$Prefix}/exec.squid.stats.totals.php --repair --byschedule --schedule-id={$GLOBALS["SCHEDULE_ID"]}"));
}
}
function REPAIR_WORKING_TABLES()
{
$q = new mysql_squid_builder();
$LIST_TABLES_DAYS = $q->LIST_TABLES_HOURS();
events(count($LIST_TABLES_DAYS) . " daily tables");
while (list($tablename, $ligne) = each($LIST_TABLES_DAYS)) {
$xtime = $q->TIME_FROM_HOUR_TABLE($tablename);
$xdate = date("Y-m-d", $xtime);
$danstable = "dansguardian_events_" . date("Ymd", $xtime);
if ($GLOBALS["VERBOSE"]) {
echo "{$tablename}: {$danstable} = {$xdate}\n";
}
$q->QUERY_SQL("INSERT IGNORE INTO tables_day (tablename,zDate) VALUES ('{$tablename}','{$xdate}')");
}
$currentTABLE = date("Ymd") . "_hour";
$LIST_TABLES_dansguardian_events = $q->LIST_TABLES_dansguardian_events();
while (list($tablename, $ligne) = each($LIST_TABLES_dansguardian_events)) {
$xtime = $q->TIME_FROM_DANSGUARDIAN_EVENTS_TABLE($tablename);
$hour_table = date("Ymd", $xtime) . "_hour";
if ($currentTABLE == $hour_table) {
continue;
}
if (!$q->TABLE_EXISTS($hour_table)) {
events(" ####### WARNING - NO TABLE {$hour_table} #############");
}
}
}
function clean_squid_stats_dbs()
{
$sock = new sockets();
$DisableArticaProxyStatistics = $sock->GET_INFO("DisableArticaProxyStatistics");
$CleanArticaSquidDatabases = $sock->GET_INFO("CleanArticaSquidDatabases");
if (!is_numeric($DisableArticaProxyStatistics)) {
$DisableArticaProxyStatistics = 0;
}
if (!is_numeric($CleanArticaSquidDatabases)) {
$CleanArticaSquidDatabases = 0;
}
if (!$GLOBALS["FORCE"]) {
if ($CleanArticaSquidDatabases == 0) {
echo "Option is not activated...\n";
return;
}
}
$q = new mysql_squid_builder();
$tables = $q->LIST_TABLES_DAYS();
$rows = 0;
$count_tables = 0;
while (list($num, $table) = each($tables)) {
$rows = $rows + $q->COUNT_ROWS($table);
if ($GLOBALS["VERBOSE"]) {
echo " Delete table {$table} {$rows} rows \n";
}
$count_tables++;
$q->DELETE_TABLE($table);
}
$tables = $q->LIST_TABLES_DAYS_BLOCKED();
while (list($num, $table) = each($tables)) {
$rows = $rows + $q->COUNT_ROWS($table);
if ($GLOBALS["VERBOSE"]) {
echo " Delete table {$table} {$rows} rows \n";
}
$count_tables++;
$q->DELETE_TABLE($table);
}
$tables = $q->LIST_TABLES_HOURS();
while (list($num, $table) = each($tables)) {
$rows = $rows + $q->COUNT_ROWS($table);
if ($GLOBALS["VERBOSE"]) {
echo " Delete table {$table} {$rows} rows \n";
}
$count_tables++;
$q->DELETE_TABLE($table);
}
$tables = $q->LIST_TABLES_MEMBERS();
while (list($num, $table) = each($tables)) {
$rows = $rows + $q->COUNT_ROWS($table);
if ($GLOBALS["VERBOSE"]) {
echo " Delete table {$table} {$rows} rows \n";
}
$count_tables++;
$q->DELETE_TABLE($table);
}
$tables = $q->LIST_TABLES_MONTH();
while (list($num, $table) = each($tables)) {
$rows = $rows + $q->COUNT_ROWS($table);
if ($GLOBALS["VERBOSE"]) {
echo " Delete table {$table} {$rows} rows \n";
}
$count_tables++;
$q->DELETE_TABLE($table);
}
$tables = $q->LIST_TABLES_WEEKS();
while (list($num, $table) = each($tables)) {
$rows = $rows + $q->COUNT_ROWS($table);
if ($GLOBALS["VERBOSE"]) {
echo " Delete table {$table} {$rows} rows \n";
}
$count_tables++;
$q->DELETE_TABLE($table);
}
$tables = $q->LIST_TABLES_WORKSHOURS();
while (list($num, $table) = each($tables)) {
$rows = $rows + $q->COUNT_ROWS($table);
if ($GLOBALS["VERBOSE"]) {
echo " Delete table {$table} {$rows} rows \n";
}
$count_tables++;
$q->DELETE_TABLE($table);
}
$tables = $q->LIST_TABLES_BLOCKED_WEEK();
while (list($num, $table) = each($tables)) {
$rows = $rows + $q->COUNT_ROWS($table);
if ($GLOBALS["VERBOSE"]) {
echo " Delete table {$table} {$rows} rows \n";
}
$count_tables++;
$q->DELETE_TABLE($table);
}
$tables = $q->LIST_TABLES_BLOCKED_DAY();
while (list($num, $table) = each($tables)) {
$rows = $rows + $q->COUNT_ROWS($table);
if ($GLOBALS["VERBOSE"]) {
echo " Delete table {$table} {$rows} rows \n";
}
$count_tables++;
$q->DELETE_TABLE($table);
}
$tables = $q->LIST_TABLES_VISITED();
while (list($num, $table) = each($tables)) {
$rows = $rows + $q->COUNT_ROWS($table);
if ($GLOBALS["VERBOSE"]) {
echo " Delete table {$table} {$rows} rows \n";
}
$count_tables++;
$q->DELETE_TABLE($table);
}
$tables = $q->LIST_TABLES_dansguardian_events();
while (list($num, $table) = each($tables)) {
$rows = $rows + $q->COUNT_ROWS($table);
if ($GLOBALS["VERBOSE"]) {
echo " Delete table {$table} {$rows} rows \n";
}
$count_tables++;
$q->DELETE_TABLE($table);
}
$q = new mysql_catz();
$tables = $q->LIST_TABLES_CATEGORIES();
while (list($num, $table) = each($tables)) {
$rows = $rows + $q->COUNT_ROWS($table);
if ($GLOBALS["VERBOSE"]) {
echo " Delete table {$table} {$rows} rows \n";
}
$count_tables++;
$q->DELETE_TABLE($table);
}
$q = new mysql();
if ($q->DATABASE_EXISTS("catz")) {
$q->DELETE_DATABASE("catz");
}
if ($count_tables > 0) {
mysql_admin_mysql(1, "Restarting MySQL service...", null, __FILE__, __LINE__);
shell_exec("/etc/init.d/mysql restart");
}
$sock->TOP_NOTIFY("{$count_tables} statistics tables as been deleted with {$rows} rows", "info");
//print_r($tables);
}
function macuid()
{
$pidfile = "/etc/artica-postfix/pids/" . basename(__FILE__) . "." . __FUNCTION__ . ".pid";
$timefile = "/etc/artica-postfix/pids/" . basename(__FILE__) . "." . __FUNCTION__ . ".time";
$unix = new unix();
$pid = @file_get_contents($pidfile);
if ($unix->process_exists($pid, basename(__FILE__))) {
if ($GLOBALS["VERBOSE"]) {
echo "Already executed pid {$pid}\n";
}
return;
}
$mypid = getmypid();
@file_put_contents($pidfile, $mypid);
$q = new mysql_squid_builder();
$sql = "SELECT * FROM webfilters_nodes WHERE LENGTH(uid)>1";
$results = $q->QUERY_SQL($sql, "artica_backup");
while ($ligne = mysql_fetch_assoc($results)) {
if ($ligne["MAC"] == "00:00:00:00:00:00") {
continue;
}
if (!IsPhysicalAddress($ligne["MAC"])) {
continue;
}
if ($GLOBALS["VERBOSE"]) {
echo "{$ligne["MAC"]} = {$ligne["uid"]}\n";
}
$array[$ligne["MAC"]] = $ligne["uid"];
}
$sql = "SELECT * FROM webfilters_ipaddr WHERE LENGTH(uid)>1";
$results = $q->QUERY_SQL($sql, "artica_backup");
while ($ligne = mysql_fetch_assoc($results)) {
$array2[$ligne["ipaddr"]] = $ligne["uid"];
}
$tablesBrutes = $q->LIST_TABLES_HOURS();
while (list($tablename, $none) = each($tablesBrutes)) {
reset($array);
while (list($mac, $uid) = each($array)) {
$uid = mysql_escape_string2($uid);
if ($GLOBALS["VERBOSE"]) {
echo "{$tablename}, {$mac} -> {$uid}\n";
}
$q->QUERY_SQL("UPDATE {$tablename} SET uid='{$uid}' WHERE MAC='{$mac}'");
}
reset($array2);
while (list($ipaddr, $uid) = each($array)) {
$uid = mysql_escape_string2($uid);
if ($GLOBALS["VERBOSE"]) {
echo "{$tablename}, {$mac} -> {$uid}\n";
}
$q->QUERY_SQL("UPDATE {$tablename} SET uid='{$uid}' WHERE client='{$ipaddr}' AND LENGTH(uid)=0");
}
}
$tablesBrutes = $q->LIST_TABLES_dansguardian_events();
while (list($tablename, $none) = each($tablesBrutes)) {
reset($array);
while (list($mac, $uid) = each($array)) {
$uid = mysql_escape_string2($uid);
if ($GLOBALS["VERBOSE"]) {
echo "{$tablename}, {$mac} -> {$uid}\n";
}
if (IsCompressed($tablename)) {
Uncompress($tablename);
}
$q->QUERY_SQL("UPDATE {$tablename} SET uid='{$uid}' WHERE MAC='{$mac}'");
}
reset($array2);
while (list($ipaddr, $uid) = each($array)) {
$uid = mysql_escape_string2($uid);
if ($GLOBALS["VERBOSE"]) {
echo "{$tablename}, {$mac} -> {$uid}\n";
}
$q->QUERY_SQL("UPDATE {$tablename} SET uid='{$uid}' WHERE CLIENT='{$ipaddr}' AND LENGTH(uid)=0");
}
}
$tablesBrutes = $q->LIST_TABLES_BLOCKED_DAY();
while (list($tablename, $none) = each($tablesBrutes)) {
reset($array);
while (list($mac, $uid) = each($array)) {
$uid = mysql_escape_string2($uid);
if ($GLOBALS["VERBOSE"]) {
echo "{$tablename}, {$mac} -> {$uid}\n";
}
if (IsCompressed($tablename)) {
Uncompress($tablename);
}
$q->QUERY_SQL("UPDATE {$tablename} SET uid='{$uid}' WHERE MAC='{$mac}'");
}
}
$tablesBrutes = $q->LIST_TABLES_BLOCKED_WEEK();
while (list($tablename, $none) = each($tablesBrutes)) {
reset($array);
while (list($mac, $uid) = each($array)) {
if ($GLOBALS["VERBOSE"]) {
echo "{$tablename}, {$mac} -> {$uid}\n";
}
if (IsCompressed($tablename)) {
Uncompress($tablename);
}
$q->QUERY_SQL("UPDATE {$tablename} SET uid='{$uid}' WHERE MAC='{$mac}'");
}
}
$tablesBrutes = $q->LIST_TABLES_YOUTUBE_DAYS();
while (list($tablename, $none) = each($tablesBrutes)) {
reset($array);
while (list($mac, $uid) = each($array)) {
$uid = mysql_escape_string2($uid);
if ($GLOBALS["VERBOSE"]) {
echo "{$tablename}, {$mac} -> {$uid}\n";
}
if (IsCompressed($tablename)) {
Uncompress($tablename);
}
$q->QUERY_SQL("UPDATE {$tablename} SET uid='{$uid}' WHERE MAC='{$mac}'");
}
reset($array2);
while (list($ipaddr, $uid) = each($array)) {
$uid = mysql_escape_string2($uid);
if ($GLOBALS["VERBOSE"]) {
echo "{$tablename}, {$mac} -> {$uid}\n";
}
$q->QUERY_SQL("UPDATE {$tablename} SET uid='{$uid}' WHERE ipaddr='{$ipaddr}' AND LENGTH(uid)=0");
}
}
$tablesBrutes = $q->LIST_TABLES_YOUTUBE_WEEK();
while (list($tablename, $none) = each($tablesBrutes)) {
reset($array);
while (list($mac, $uid) = each($array)) {
$uid = mysql_escape_string2($uid);
if ($GLOBALS["VERBOSE"]) {
echo "{$tablename}, {$mac} -> {$uid}\n";
}
if (IsCompressed($tablename)) {
Uncompress($tablename);
}
$q->QUERY_SQL("UPDATE {$tablename} SET uid='{$uid}' WHERE MAC='{$mac}'");
}
reset($array2);
while (list($ipaddr, $uid) = each($array)) {
$uid = mysql_escape_string2($uid);
if ($GLOBALS["VERBOSE"]) {
echo "{$tablename}, {$mac} -> {$uid}\n";
}
$q->QUERY_SQL("UPDATE {$tablename} SET uid='{$uid}' WHERE ipaddr='{$ipaddr}' AND LENGTH(uid)=0");
}
}
uid_resets();
}
function start_export()
{
$unix = new unix();
$sock = new sockets();
$q = new mysql_squid_builder();
$ArticaProxyStatisticsBackupFolder = $sock->GET_INFO("ArticaProxyStatisticsBackupFolder");
if ($ArticaProxyStatisticsBackupFolder == null) {
$ArticaProxyStatisticsBackupFolder = "/home/artica/squid/backup-statistics";
}
$ArticaProxyStatisticsBackupFolder = $ArticaProxyStatisticsBackupFolder . "/export";
$LIST_TABLES_YOUTUBE_HOURS = $q->LIST_TABLES_YOUTUBE_HOURS();
$LIST_TABLES_SIZEHOURS = $q->LIST_TABLES_SIZEHOURS();
$LIST_TABLES_QUOTA_HOURS = $q->LIST_TABLES_QUOTA_HOURS();
$LIST_TABLES_QUOTADAY = $q->LIST_TABLES_QUOTADAY();
$LIST_TABLES_QUOTAMONTH = $q->LIST_TABLES_QUOTAMONTH();
$LIST_TABLES_SEARCHWORDS_HOURS = $q->LIST_TABLES_SEARCHWORDS_HOURS();
$LIST_TABLES_SEARCHWORDS_DAY = $q->LIST_TABLES_SEARCHWORDS_DAY();
$LIST_TABLES_dansguardian_events = $q->LIST_TABLES_dansguardian_events();
$LIST_TABLES_HOURS = $q->LIST_TABLES_HOURS();
$LIST_TABLES_USERSIZED = $q->LIST_TABLES_USERSIZED();
$LIST_TABLES_YOUTUBE_WEEK = $q->LIST_TABLES_YOUTUBE_WEEK();
$LIST_TABLES_WEEKS = $q->LIST_TABLES_WEEKS();
$LIST_TABLES_MEMBERS = $q->LIST_TABLES_MEMBERS();
$LIST_TABLES_GSIZE = $q->LIST_TABLES_GSIZE();
$LIST_TABLES_GCACHE = $q->LIST_TABLES_GCACHE();
$LIST_TABLES_VISITED = $q->LIST_TABLES_VISITED();
$LIST_TABLES_BLOCKED = $q->LIST_TABLES_BLOCKED();
$LIST_TABLES_BLOCKED_WEEK = $q->LIST_TABLES_BLOCKED_WEEK();
$LIST_TABLES_BLOCKED_DAY = $q->LIST_TABLES_BLOCKED_DAY();
$LIST_TABLES_WWWUID = $q->LIST_TABLES_WWWUID();
$LIST_CAT_FAMDAY = $q->LIST_CAT_FAMDAY();
while (list($tablename, $none) = each($LIST_TABLES_YOUTUBE_HOURS)) {
if (trim($tablename) == null) {
continue;
}
$EXPORT_SOURCES[$tablename] = true;
}
while (list($tablename, $none) = each($LIST_TABLES_SIZEHOURS)) {
if (trim($tablename) == null) {
continue;
}
$EXPORT_SOURCES[$tablename] = true;
}
while (list($tablename, $none) = each($LIST_TABLES_QUOTADAY)) {
if (trim($tablename) == null) {
continue;
}
$EXPORT_SOURCES[$tablename] = true;
}
while (list($tablename, $none) = each($LIST_TABLES_QUOTAMONTH)) {
if (trim($tablename) == null) {
continue;
}
$EXPORT_SOURCES[$tablename] = true;
}
while (list($tablename, $none) = each($LIST_TABLES_SEARCHWORDS_HOURS)) {
if (trim($tablename) == null) {
continue;
}
$EXPORT_SOURCES[$tablename] = true;
}
while (list($tablename, $none) = each($LIST_TABLES_SEARCHWORDS_DAY)) {
if (trim($tablename) == null) {
continue;
}
$EXPORT_SOURCES[$tablename] = true;
}
while (list($tablename, $none) = each($LIST_TABLES_QUOTA_HOURS)) {
if (trim($tablename) == null) {
continue;
}
$EXPORT_SOURCES[$tablename] = true;
}
while (list($tablename, $none) = each($LIST_TABLES_dansguardian_events)) {
if (trim($tablename) == null) {
continue;
}
$EXPORT_SOURCES[$tablename] = true;
}
while (list($tablename, $none) = each($LIST_TABLES_HOURS)) {
if (trim($tablename) == null) {
continue;
}
$EXPORT_SOURCES[$tablename] = true;
}
while (list($tablename, $none) = each($LIST_TABLES_USERSIZED)) {
if (trim($tablename) == null) {
continue;
}
$EXPORT_SOURCES[$tablename] = true;
}
while (list($tablename, $none) = each($LIST_TABLES_BLOCKED_WEEK)) {
if (trim($tablename) == null) {
continue;
}
$EXPORT_SOURCES[$tablename] = true;
}
while (list($tablename, $none) = each($LIST_TABLES_BLOCKED)) {
if (trim($tablename) == null) {
continue;
}
$EXPORT_SOURCES[$tablename] = true;
}
while (list($tablename, $none) = each($LIST_TABLES_YOUTUBE_WEEK)) {
if (trim($tablename) == null) {
continue;
}
$EXPORT_SOURCES[$tablename] = true;
}
while (list($tablename, $none) = each($LIST_TABLES_WEEKS)) {
if (trim($tablename) == null) {
continue;
}
$EXPORT_SOURCES[$tablename] = true;
}
while (list($tablename, $none) = each($LIST_TABLES_MEMBERS)) {
if (trim($tablename) == null) {
continue;
}
$EXPORT_SOURCES[$tablename] = true;
}
while (list($tablename, $none) = each($LIST_TABLES_GSIZE)) {
if (trim($tablename) == null) {
continue;
}
$EXPORT_SOURCES[$tablename] = true;
}
while (list($tablename, $none) = each($LIST_TABLES_GCACHE)) {
if (trim($tablename) == null) {
continue;
}
$EXPORT_SOURCES[$tablename] = true;
}
while (list($tablename, $none) = each($LIST_TABLES_VISITED)) {
if (trim($tablename) == null) {
continue;
}
$EXPORT_SOURCES[$tablename] = true;
}
while (list($tablename, $none) = each($LIST_TABLES_BLOCKED_DAY)) {
if (trim($tablename) == null) {
continue;
}
$EXPORT_SOURCES[$tablename] = true;
}
while (list($tablename, $none) = each($LIST_CAT_FAMDAY)) {
if (trim($tablename) == null) {
continue;
}
$EXPORT_SOURCES[$tablename] = true;
}
while (list($tablename, $none) = each($LIST_TABLES_WWWUID)) {
if (trim($tablename) == null) {
continue;
}
$EXPORT_SOURCES[$tablename] = true;
}
while (list($tablename, $none) = each($EXPORT_SOURCES)) {
if (trim($tablename) == null) {
continue;
}
$EXPORT_DESTINATIONS[] = $tablename;
}
@mkdir($ArticaProxyStatisticsBackupFolder, 0755, true);
$target_file = $ArticaProxyStatisticsBackupFolder . "/" . time() . ".sql.gz";
if (is_file($target_file)) {
@unlink($target_file);
}
$EXPORT_DESTINATIONS[] = "visited_sites";
$EXPORT_DESTINATIONS[] = "youtube_objects";
$EXPORT_DESTINATIONS[] = "UserAgents";
$EXPORT_DESTINATIONS[] = "UserAutDB";
$EXPORT_DESTINATIONS[] = "UserAuthDays";
$EXPORT_DESTINATIONS[] = "UserAuthDaysGrouped";
$EXPORT_DESTINATIONS[] = "UserSizeRTT";
$EXPORT_DESTINATIONS[] = "allsizes";
$unix = new unix();
$mysqldump = $unix->find_program("mysqldump");
$bzip2 = $unix->find_program("bzip2");
$bzip2_cmd = "| {$bzip2} ";
$AllTables = @implode(" ", $EXPORT_DESTINATIONS);
$cmd = "{$mysqldump} -S /var/run/mysqld/squid-db.sock --single-transaction --skip-add-drop-table --no-create-db --insert-ignore --skip-add-locks --skip-lock-tables squidlogs {$AllTables} {$bzip2_cmd}> {$target_file} 2>&1";
$t = time();
$failed = false;
if ($GLOBALS["VERBOSE"]) {
echo $cmd . "\n";
}
exec($cmd, $results);
while (list($index, $line) = each($results)) {
if (preg_match("#Couldn't#", $line)) {
@unlink($target_file);
stats_admin_events(0, "Exporting tables failed {$line} took:" . $unix->distanceOfTimeInWords($t, time()), null, __FILE__, __LINE__);
return;
}
if (preg_match("#Error\\s+([0-9]+)#", $line)) {
@unlink($target_file);
stats_admin_events(0, "Exporting tables failed {$line} took:" . $unix->distanceOfTimeInWords($t, time()), null, __FILE__, __LINE__);
return;
}
echo "{$line}\n";
}
$size = @filesize($target_file);
if ($size < 10000) {
@unlink($target_file);
stats_admin_events(0, "Exporting tables failed {$size}Bytes < 10000bytes took:" . $unix->distanceOfTimeInWords($t, time()), null, __FILE__, __LINE__);
return;
}
if ($GLOBALS["VERBOSE"]) {
echo "{$target_file} {$size}Bytes " . FormatBytes($size / 1024) . "\n";
}
reset($EXPORT_SOURCES);
while (list($tablename, $none) = each($EXPORT_SOURCES)) {
$q->QUERY_SQL("DROP TABLE {$tablename}");
if ($GLOBALS["VERBOSE"]) {
echo "Removing table {$tablename}\n";
}
}
}
function build_report($ID, $nopid = false)
{
if (!is_numeric($ID)) {
ufdbguard_admin_events("Not a numeric ID", __FUNCTION__, __FILE__, __LINE__, "reports");
return;
}
$t = time();
$unix = new unix();
$tablename = "WebTrackMem{$ID}";
$tableBlock = "WebTrackMeB{$ID}";
if (!$nopid) {
$pidfile = "/etc/artica-postfix/pids/" . basename(__FILE__) . "." . __FUNCTION__ . ".{$ID}.pid";
$pid = @file_get_contents($pidfile);
if ($pid < 100) {
$pid = null;
}
if ($unix->process_exists($pid, basename(__FILE__))) {
ufdbguard_admin_events("Already executed pid {$pid}", __FUNCTION__, __FILE__, __LINE__, "reports");
if ($GLOBALS["VERBOSE"]) {
echo "Already executed pid {$pid}\n";
}
return;
}
}
if ($GLOBALS["VERBOSE"]) {
echo "Building report {$ID}\n";
}
$q = new mysql_squid_builder();
if ($q->TABLE_EXISTS($tablename)) {
$q->DELETE_TABLE($tablename);
}
if ($q->TABLE_EXISTS($tableBlock)) {
$q->DELETE_TABLE($tableBlock);
}
if (!$q->CreateMemberReportTable($tablename)) {
ufdbguard_admin_events("could not create table {$tablename}", __FUNCTION__, __FILE__, __LINE__, "reports");
return;
}
if (!$q->CreateMemberReportBlockTable($tableBlock)) {
ufdbguard_admin_events("could not create table {$tablename}", __FUNCTION__, __FILE__, __LINE__, "reports");
return;
}
$rp = new squid_report($ID);
$LIST_TABLES_dansguardian_events = $q->LIST_TABLES_dansguardian_events();
progress(10, $ID);
$counttables = count($LIST_TABLES_dansguardian_events);
$prefix = "INSERT IGNORE INTO {$tablename} (`zMD5`,`sitename`,`familysite`,`{$rp->userfield}`,`zDate`,`size`,`hits`,`category`) VALUES ";
while (list($sourcetable, $ligne) = each($LIST_TABLES_dansguardian_events)) {
$c++;
$sql = $rp->BuildQuery($sourcetable);
$results = $q->QUERY_SQL($sql);
if (!$q->ok) {
ufdbguard_admin_events("{$q->mysql_error}\n{$sql}", __FUNCTION__, __FILE__, __LINE__, "reports");
return;
}
if ($GLOBALS["VERBOSE"]) {
echo "Parsing {$sourcetable} \n{$sql}\n-> `" . mysql_num_rows($results) . "` rows\n";
}
if (mysql_num_rows($results) == 0) {
continue;
}
$purc = round($c / $counttables, 2) * 100;
progress($purc, $ID);
$f = array();
while ($ligne = @mysql_fetch_array($results, MYSQL_ASSOC)) {
$md5 = md5(serialize($ligne));
$sitename = $ligne["sitename"];
$familysite = $q->GetFamilySites($sitename);
if (!isset($GLOBALS["CATEGORY"][$sitename])) {
$GLOBALS["CATEGORY"][$sitename] = $q->GET_CATEGORIES($sitename);
}
$category = $GLOBALS["CATEGORY"][$sitename];
$source = addslashes($ligne["source"]);
$zDate = $ligne["zDate"];
$size = $ligne["size"];
$hits = $ligne["hits"];
$category = addslashes($category);
$f[] = "('{$md5}','{$sitename}','{$familysite}','{$source}','{$zDate}','{$size}','{$hits}','{$category}')";
}
if (count($f) == 0) {
continue;
}
$q->QUERY_SQL($prefix . @implode(",", $f));
if (!$q->ok) {
ufdbguard_admin_events("{$q->mysql_error}", __FUNCTION__, __FILE__, __LINE__, "reports");
return;
}
if (system_is_overloaded(__FILE__)) {
sleep(5);
}
}
if ($rp->csv == 1) {
SaveCSV($ID);
}
$LIST_TABLES_BLOCKED = $q->LIST_TABLES_BLOCKED();
$prefix = "INSERT IGNORE INTO {$tableBlock} (`zMD5`,`zDate`,`hits`,`website`,`category`,`rulename`,`event`,`why`,`explain`,`blocktype`,`{$rp->userfield}`) VALUES ";
while (list($sourcetable, $ligne) = each($LIST_TABLES_BLOCKED)) {
$c++;
if ($GLOBALS["VERBOSE"]) {
echo "Parsing {$sourcetable}\n";
}
$sql = $rp->BuildQueryBlock($sourcetable);
if (!$q->FIELD_EXISTS("{$sourcetable}", "uid")) {
$q->QUERY_SQL("ALTER TABLE `{$sourcetable}` ADD `uid` VARCHAR( 128 ) NOT NULL ,ADD INDEX ( `uid` )");
}
$results = $q->QUERY_SQL($sql);
if (!$q->ok) {
ufdbguard_admin_events("{$q->mysql_error}\n{$sql}", __FUNCTION__, __FILE__, __LINE__, "reports");
return;
}
if (mysql_num_rows($results) == 0) {
continue;
}
$purc = round($c / $counttables, 2) * 100;
progress($purc, $ID);
$f = array();
while ($ligne = @mysql_fetch_array($results, MYSQL_ASSOC)) {
$zMD5 = md5(serialize($ligne));
$sitename = $ligne["sitename"];
$category = $ligne["category"];
if ($category == null) {
if (!isset($GLOBALS["CATEGORY"][$sitename])) {
$GLOBALS["CATEGORY"][$sitename] = $q->GET_CATEGORIES($sitename);
}
$category = $GLOBALS["CATEGORY"][$sitename];
}
$source = addslashes($ligne["source"]);
$zDate = $ligne["zDate"];
$hits = $ligne["hits"];
$rulename = $ligne["rulename"];
$event = $ligne["event"];
$why = $ligne["why"];
$explain = $ligne["explain"];
$blocktype = $ligne["blocktype"];
$category = addslashes($category);
$f[] = "('{$zMD5}','{$zDate}','{$hits}','{$sitename}','{$category}','{$rulename}','{$event}','{$why}','{$explain}','{$blocktype}','{$source}')";
}
if (system_is_overloaded(__FILE__)) {
sleep(5);
}
}
$myisamchk = $unix->find_program("myisamchk");
$myisampack = $unix->find_program("myisampack");
$mysql_data = $unix->MYSQL_DATA_DIR();
echo "OPTIMIZE TABLE {$tableBlock}\n";
$q->QUERY_SQL("OPTIMIZE TABLE {$tableBlock}");
echo "OPTIMIZE TABLE {$tablename}\n";
$q->QUERY_SQL("OPTIMIZE TABLE {$tablename}");
echo "LOCK TABLE {$tablename}\n";
$q->QUERY_SQL("LOCK TABLE {$tablename} WRITE");
echo "LOCK TABLE {$tableBlock}\n";
$q->QUERY_SQL("LOCK TABLE {$tableBlock} WRITE");
$q->QUERY_SQL("FLUSH TABLE {$tableBlock}");
$q->QUERY_SQL("FLUSH TABLE {$tablename}");
echo "myisamchk {$tablename}\n";
shell_exec("{$myisamchk} -cFU {$mysql_data}/squidlogs/{$tablename}.MYI");
echo "myisamchk {$tableBlock}\n";
shell_exec("{$myisamchk} -cFU {$mysql_data}/squidlogs/{$tableBlock}.MYI");
echo "myisampack {$tablename}\n";
shell_exec("{$myisampack} -f {$mysql_data}/squidlogs/{$tablename}.MYI");
echo "myisampack {$tableBlock}\n";
shell_exec("{$myisampack} -f {$mysql_data}/squidlogs/{$tableBlock}.MYI");
$q->QUERY_SQL("FLUSH TABLE {$tablename}");
$q->QUERY_SQL("FLUSH TABLE {$tableBlock}");
$rp->set_duration($unix->distanceOfTimeInWords($t, time(), true));
progress(100, $ID);
}
function clean_empty_tables()
{
$unix = new unix();
///etc/artica-postfix/pids/exec.squid.stats.hours.php.clean_empty_tables.time
$pidfile = "/etc/artica-postfix/pids/" . basename(__FILE__) . "." . __FUNCTION__ . ".pid";
$timefile = "/etc/artica-postfix/pids/" . basename(__FILE__) . "." . __FUNCTION__ . ".time";
if ($GLOBALS["VERBOSE"]) {
echo "Time File: {$timefile}\n";
}
$pid = @file_get_contents($pidfile);
if ($GLOBALS["FORCE"]) {
ToSyslog("macscan(): Executed in --force mode");
}
if (!$GLOBALS["FORCE"]) {
if ($pid < 100) {
$pid = null;
}
$unix = new unix();
if ($unix->process_exists($pid, basename(__FILE__))) {
if ($GLOBALS["VERBOSE"]) {
echo "Already executed pid {$pid}\n";
}
ToSyslog("clean_empty_tables(): already executed pid {$pid}");
return;
}
$timeexec = $unix->file_time_min($timefile);
if ($timeexec < 30) {
return;
}
$mypid = getmypid();
@file_put_contents($pidfile, $mypid);
}
@unlink($timefile);
@file_put_contents($timefile, time());
$q = new mysql_squid_builder();
$TABLES = $q->LIST_TABLES_HOURS_TEMP();
$current = "squidhour_" . date("YmdH");
while (list($tablename, $none) = each($TABLES)) {
if ($tablename == $current) {
continue;
}
if ($q->COUNT_ROWS($tablename) > 0) {
continue;
}
$q->QUERY_SQL("DROP TABLE `{$tablename}`");
}
$TABLES = $q->LIST_TABLES_SIZEHOURS();
$current = "sizehour_" . date("YmdH");
while (list($tablename, $none) = each($TABLES)) {
if ($tablename == $current) {
continue;
}
if ($q->COUNT_ROWS($tablename) > 0) {
continue;
}
$q->QUERY_SQL("DROP TABLE `{$tablename}`");
}
$TABLES = $q->LIST_TABLES_dansguardian_events();
$current = " dansguardian_events_" . date("Ymd");
while (list($tablename, $none) = each($TABLES)) {
if ($tablename == $current) {
continue;
}
if ($q->COUNT_ROWS($tablename) > 0) {
continue;
}
$q->QUERY_SQL("DROP TABLE `{$tablename}`");
}
}
