function section_blocked_form()
{
$boot = new boostrap_form();
$q = new mysql_squid_builder();
$ARRAYTABLES = $q->LIST_TABLES_BLOCKED();
while (list($tablename, $none) = each($ARRAYTABLES)) {
$time = $q->TIME_FROM_DAY_TABLE($tablename);
$days[$time] = time_to_date($time);
}
$BLOCKED_CATEGORY_LIMITS[50] = 50;
$BLOCKED_CATEGORY_LIMITS[250] = 250;
$BLOCKED_CATEGORY_LIMITS[500] = 250;
$BLOCKED_CATEGORY_LIMITS[1000] = 1000;
$BLOCKED_CATEGORY_LIMITS[2000] = 2000;
krsort($days);
$boot->set_list("QUERY_BLOCKED_DATE", "{date}", $days, $_SESSION["QUERY_BLOCKED_DATE"]);
$boot->set_field("QUERY_BLOCKED_UID", "{member}", $_SESSION["QUERY_BLOCKED_UID"]);
$boot->set_field("QUERY_BLOCKED_CATEGORY", "{category}", $_SESSION["QUERY_BLOCKED_CATEGORY"]);
$boot->set_list("BLOCKED_CATEGORY_LIMIT", "{rows}", $BLOCKED_CATEGORY_LIMITS, $_SESSION["BLOCKED_CATEGORY_LIMIT"]);
$boot->set_button("{search}");
$boot->set_CloseYahoo("YahooWin2");
$boot->set_formdescription("{advanced_search_explain}");
$boot->set_RefreshSearchs();
echo $boot->Compile();
}
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 calendar_build(){
$t=$_GET["t"];
$obj_cal = new classe_calendrier("calendar-$t");
$obj_cal->USLink=true;
$obj_cal->afficheMois();
$obj_cal->afficheSemaines(false);
$obj_cal->afficheJours(true);
$obj_cal->afficheNavigMois(true);
$obj_cal->activeJoursPasses();
$obj_cal->activeJourPresent();
$obj_cal->activeJoursEvenements();
$obj_cal->setFormatLienMois("javascript:Blurz();\" OnClick=\"javascript:NavCalendar$t('%s','%s');");
$obj_cal->setFormatLienJours("javascript:Blurz();\" OnClick=\"javascript:NavCalendarJ$t('%s','%s','%s');");
$q=new mysql_squid_builder();
if(isset($_SESSION["LIST_TABLES_BLOCKED"])){$hash=$_SESSION["LIST_TABLES_BLOCKED"];}else{
$hash=$q->LIST_TABLES_BLOCKED();
$_SESSION["LIST_TABLES_BLOCKED"]=$q->LIST_TABLES_BLOCKED();
}
if(!isset($_SESSION["LIST_TABLES_BLOCKED_EV"])){
while (list ($tablename,$none ) = each ($hash) ){
$ct=$q->COUNT_ROWS($tablename);
if($ct==0){continue;}
if(!preg_match("#^([0-9]+)_blocked$#", $tablename,$re)){continue;}
$intval=$re[1];
$Cyear=substr($intval, 0,4);
$CMonth=substr($intval,4,2);
$CDay=substr($intval,6,2);
$CDay=str_replace("_", "", $CDay);
$time=strtotime("$Cyear-$CMonth-$CDay 00:00:00");
$year=date("Y",$time);
$month=date("m",$time);
$day=date("d",$time);
$_SESSION["LIST_TABLES_BLOCKED_EV"]["$year-$month-$day"]="$ct Hits";
$obj_cal->ajouteEvenement("$year-$month-$day","$ct Hits");
}
}else{
$hash=$_SESSION["LIST_TABLES_BLOCKED_EV"];
while (list ($d,$h ) = each ($hash) ){
$obj_cal->ajouteEvenement($d,$h);
}
}
$calendar=$obj_cal->makeCalendrier($_GET["year"],$_GET["month"]);
echo $calendar;
}
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);
}
