function youtube_count()
{
$q = new mysql_squid_builder();
$LIST_TABLES_YOUTUBE_DAYS = $q->LIST_TABLES_YOUTUBE_DAYS();
while (list($tablesource, $value) = each($LIST_TABLES_YOUTUBE_DAYS)) {
$sql = "SELECT SUM(hits) as thits,youtubeid FROM {$tablesource} GROUP BY youtubeid";
$results = $q->QUERY_SQL($sql);
while ($ligne = @mysql_fetch_array($results, MYSQL_ASSOC)) {
if (isset($YTBE[$ligne["youtubeid"]])) {
$YTBE[$ligne["youtubeid"]] = $YTBE[$ligne["youtubeid"]] + $ligne["thits"];
continue;
}
$YTBE[$ligne["youtubeid"]] = $ligne["thits"];
}
}
while (list($youtubeid, $count) = each($YTBE)) {
$sql = "UPDATE youtube_objects SET hits={$count} WHERE youtubeid='{$youtubeid}'";
$q->QUERY_SQL($sql);
}
}
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 ScanDays()
{
$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 = $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 = $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 = $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;
}
$prefix = "INSERT IGNORE INTO tables_day (tablename,zDate) VALUES ";
while (list($day, $dayTime) = each($ARRAY_DAYS)) {
$tablename = "dansguardian_events_" . date("Ymd", $dayTime);
if ($GLOBALS["VERBOSE"]) {
echo "{$day}: [{$tablename}]\n";
}
$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";
}
ufdbguard_admin_events("Fatal {$q->mysql_error}", __FUNCTION__, __FILE__, __LINE__, "backup");
return false;
}
}
return true;
}
function ScanDays($onlyTable = false)
{
$q = new mysql_squid_builder(true);
$ARRAY_DAYS = array();
$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 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();
}
