function blocked_uid_parse_array($array)
{
$q = new mysql_squid_builder();
while (list($uid, $rows) = each($array)) {
$uidtable = $q->uid_to_tablename($uid);
// $f[$uid][]="('$md5','$zdate','$sitename','$category','$hits')";
$sql = "CREATE TABLE IF NOT EXISTS `blocked_{$uidtable}` ( `zmd5` varchar(90) NOT NULL,\n\t\t`zDate` date NOT NULL,\n\t\t`hits` BIGINT UNSIGNED NOT NULL,\n\t\t`sitename` varchar(255) NOT NULL,\n\t\t`category` varchar(255),\n\t\tPRIMARY KEY (`zmd5`),\n\t\tKEY `zDate` (`zDate`),\n\t\tKEY `hits` (`hits`),\n\t\tKEY `sitename` (`sitename`) ,\n\t\tKEY `category` (`category`) )\n\t\tENGINE = MYISAM;";
$q->QUERY_SQL($sql);
if (!$q->ok) {
if ($GLOBALS["VERBOSE"]) {
echo "{$q->mysql_error} in line: " . __LINE__ . "\n";
}
return false;
}
$sql = "INSERT IGNORE INTO `blocked_{$uidtable}` (zmd5,zDate,sitename,category,hits) VALUES " . @implode(',', $rows);
$q->QUERY_SQL($sql);
if (!$q->ok) {
if ($GLOBALS["VERBOSE"]) {
echo "{$q->mysql_error} in line: " . __LINE__ . "\n";
}
return false;
}
}
return true;
}
function graph4()
{
$q = new mysql_squid_builder();
$tpl = new templates();
$tablename = "www_" . $q->uid_to_tablename($_GET["uid"]);
$sql = "SELECT SUM(hits) as hits,familysite,category FROM `{$tablename}` GROUP BY familysite,category\n\tHAVING `category`='{$_GET["category"]}'\n\tORDER BY hits DESC LIMIT 0,15";
if ($_GET["category"] == null) {
$sql = "SELECT SUM(hits) as hits,familysite,category FROM `{$tablename}` GROUP BY familysite,category\n\t\tHAVING `category` IS NULL\n\t\tORDER BY hits DESC LIMIT 0,15";
}
$results = $q->QUERY_SQL($sql);
while ($ligne = @mysql_fetch_array($results, MYSQL_ASSOC)) {
$size = $ligne["hits"];
$PieData[$ligne["familysite"]] = $size;
}
if (!$q->ok) {
$tpl->javascript_senderror($q->mysql_error, $_GET["container"]);
}
$tpl = new templates();
$highcharts = new highcharts();
$highcharts->container = $_GET["container"];
$highcharts->PieDatas = $PieData;
$highcharts->ChartType = "pie";
$highcharts->PiePlotTitle = "{hits}";
$highcharts->Title = $tpl->_ENGINE_parse_body("{$_GET["category"]}/{top_websites}/{size}");
echo $highcharts->BuildChart();
}
function www_table()
{
$page = CurrentPageName();
$tpl = new templates();
$boot = new boostrap_form();
$q = new mysql_squid_builder();
$uidtable = $q->uid_to_tablename($_GET["member-value"]);
$_GET["member-value"] = urlencode($_GET["member-value"]);
$q = new mysql_squid_builder();
$sql = "SELECT SUM(hits) as hits,SUM(size) as size,zDate FROM `www_{$uidtable}`\n\tGROUP BY familysite,zDate HAVING familysite='{$_GET["familysite"]}' ORDER BY zDate DESC";
$results = $q->QUERY_SQL($sql);
$_GET["familysite"] = urlencode($_GET["familysite"]);
if (!$q->ok) {
echo "<p class=text-error>{$q->mysql_error}</p>";
return;
}
while ($ligne = @mysql_fetch_array($results, MYSQL_ASSOC)) {
$size = FormatBytes($ligne["size"] / 1024);
$date = strtotime($ligne["zDate"] . "00:00:00");
$hits = FormatNumber($ligne["hits"]);
$dateT = date("{l} {F} d", $date);
if ($tpl->language == "fr") {
$dateT = date("{l} d {F} ", $date);
}
$dateT = $tpl->_ENGINE_parse_body($dateT);
$jshost = "Loadjs('miniadm.webstats.ByMember.website.byday.php?xtime={$date}&uid={$_GET["member-value"]}&familysite={$_GET["familysite"]}')";
$link = $boot->trswitch($jshost);
$tr[] = "\n\t\t\t\t<tr id='{$id}'>\n\t\t\t\t\t<td {$link}><i class='icon-time'></i> {$dateT}</a></td>\n\t\t\t\t\t<td {$link}><i class='icon-info-sign'></i> {$size}</td>\n\t\t\t\t\t<td {$link}><i class='icon-info-sign'></i> {$hits}</td>\n\t\t\t\t</tr>";
}
echo $tpl->_ENGINE_parse_body("\n\t\n\t\t<table class='table table-bordered table-hover'>\n\t\n\t\t\t<thead>\n\t\t\t\t<tr>\n\t\t\t\t\t<th>{date}</th>\n\t\t\t\t\t<th>{size}</th>\n\t\t\t\t\t<th>{hits}</th>\n\t\t\t\t</tr>\n\t\t\t</thead>\n\t\t\t <tbody>\n\t\t\t") . @implode("", $tr) . "</tbody></table>";
}
function websites_uid_not_categorised($uid = null, $tablename = null, $aspid = false)
{
if (isset($GLOBALS["websites_uid_not_categorised_{$uid}"])) {
return;
}
$unix = new unix();
$pidfile = "/etc/artica-postfix/pids/" . basename(__FILE__) . "." . __FUNCTION__ . ".{$uid}.pid";
if ($aspid) {
$pid = @file_get_contents($pidfile);
$myfile = basename(__FILE__);
if ($unix->process_exists($pid, $myfile)) {
ufdbguard_admin_events("Task already running PID: {$pid}, aborting current task", __FUNCTION__, __FILE__, __LINE__, "stats");
return;
}
}
$mypid = getmypid();
@file_put_contents($pidfile, $mypid);
$q = new mysql_squid_builder();
if ($uid != null) {
$uidtable = $q->uid_to_tablename($uid);
$tablename = "www_{$uidtable}";
}
if (!$q->FIELD_EXISTS($tablename, "category")) {
$q->QUERY_SQL("ALTER TABLE `{$tablename}` ADD `category` varchar(255), ADD INDEX (`category`)");
}
$sql = "SELECT familysite,`category` FROM `{$tablename}` GROUP BY familysite,`category` HAVING `category` IS NULL ";
$results = $q->QUERY_SQL($sql);
if (!$q->ok) {
if ($GLOBALS["VERBOSE"]) {
echo "############# ERROR #########\n{$q->mysql_error}\\Line:" . __LINE__ . "\n#############\n";
}
return false;
}
$c = 0;
$mysql_num_rows = mysql_num_rows($results);
if ($mysql_num_rows == 0) {
if ($GLOBALS["VERBOSE"]) {
echo "{$sql} (No rows)\n";
}
return true;
}
while ($ligne = @mysql_fetch_array($results, MYSQL_ASSOC)) {
$sitename = $ligne["familysite"];
$IpClass = new IP();
if ($IpClass->isValid($sitename)) {
if (isset($GLOBALS["IPCACHE"][$sitename])) {
$t = time();
$sitename = gethostbyaddr($sitename);
events("{$tablename}: {$ligne["familysite"]} -> {$sitename} " . $unix->distanceOfTimeInWords($t, time()) . " gethostbyaddr() LINE:" . __LINE__);
$GLOBALS["IPCACHE"][$sitename] = $sitename;
}
}
$category = $q->GET_CATEGORIES($sitename);
if ($IpClass->isValid($sitename)) {
if ($category == null) {
$category = "ipaddr";
}
$q->categorize($sitename, $category);
}
events("{$tablename}: {$ligne["familysite"]} -> {$sitename} [{$category}] LINE:" . __LINE__);
if (strlen($category) > 0) {
$category = mysql_escape_string2($category);
$ligne["familysite"] = mysql_escape_string2($ligne["familysite"]);
$sql = "UPDATE `{$tablename}` SET `category`='{$category}' WHERE familysite='{$ligne["familysite"]}'";
$q->QUERY_SQL($sql);
if (!$q->ok) {
ufdbguard_admin_events("{$q->mysql_error}", __FUNCTION__, __FILE__, __LINE__, "stats");
return;
}
}
}
}
function graph9()
{
$tpl = new templates();
$unknown = $tpl->_ENGINE_parse_body("{unknown}");
$q = new mysql_squid_builder();
$tpl = new templates();
$uidtable = $q->uid_to_tablename($_GET["member-value"]);
$sql = "SELECT SUM(hits) as hits,sitename FROM `blocked_{$uidtable}` GROUP BY\n\tsitename ORDER BY hits DESC LIMIT 0,15";
$results = $q->QUERY_SQL($sql);
while ($ligne = @mysql_fetch_array($results, MYSQL_ASSOC)) {
$size = $ligne["hits"];
if (trim($ligne["sitename"]) == null) {
$ligne["sitename"] = $unknown;
}
$PieData[$ligne["sitename"]] = $size;
}
if (!$q->ok) {
$tpl->javascript_senderror($q->mysql_error, $_GET["container"]);
}
$tpl = new templates();
$highcharts = new highcharts();
$highcharts->container = $_GET["container"];
$highcharts->PieDatas = $PieData;
$highcharts->ChartType = "pie";
$highcharts->PiePlotTitle = "{hits}";
$highcharts->Title = $tpl->_ENGINE_parse_body("{blocked}/{top_websites}/{hits}");
echo $highcharts->BuildChart();
}
function www_search()
{
$page = CurrentPageName();
$tpl = new templates();
$boot = new boostrap_form();
$familysite = $_GET["familysite"];
$q = new mysql_squid_builder();
$uidtable = $q->uid_to_tablename($_GET["member-value"]);
if (!$q->TABLE_EXISTS("`visited_sites_days`")) {
echo "<p class=text-error>No table «`visited_sites_days`» for {$familysite}</p>";
return;
}
$search = string_to_flexquery("www-search");
$sql = "SELECT SUM(hits) as hits,SUM(size) as size,`zDate`,familysite FROM `visited_sites_days` GROUP BY\n\tfamilysite,`zDate` HAVING `familysite`='{$familysite}' {$search} ORDER BY `zDate` DESC";
$results = $q->QUERY_SQL($sql);
$results = $q->QUERY_SQL($sql);
if (!$q->ok) {
echo "<p class=text-error>{$q->mysql_error}<br>{$sql}</p>";
return;
}
while ($ligne = @mysql_fetch_array($results, MYSQL_ASSOC)) {
$size = FormatBytes($ligne["size"] / 1024);
$date = strtotime($ligne["zDate"] . "00:00:00");
$hits = FormatNumber($ligne["hits"]);
$fsite = urlencode($ligne["familysite"]);
$jslink = "Loadjs('miniadm.webstats.websites.ByDayByFamilySite.php?familysite={$fsite}&xtime={$date}')";
$zdate = time_to_date($date);
$link = $boot->trswitch($jslink);
$tr[] = "\n\t\t<tr id='{$id}'>\n\t\t<td {$link}><i class='icon-time'></i> {$zdate}</a></td>\n\t\t<td {$link}><i class='icon-info-sign'></i> {$size}</td>\n\t\t<td {$link}><i class='icon-info-sign'></i> {$hits}</td>\n\t\t</tr>";
}
echo $tpl->_ENGINE_parse_body("\n\n\t\t\t<table class='table table-bordered table-hover'>\n\n\t\t\t<thead>\n\t\t\t\t<tr>\n\t\t\t\t\t<th>{date}</th>\n\t\t\t\t\t<th>{size}</th>\n\t\t\t\t\t<th>{hits}</th>\n\t\t\t\t</tr>\n\t\t\t</thead>\n\t\t\t <tbody>\n\t\t\t") . @implode("", $tr) . "</tbody></table>";
}
function categories_table()
{
$page = CurrentPageName();
$tpl = new templates();
$boot = new boostrap_form();
$q = new mysql_squid_builder();
$unknown = $tpl->_ENGINE_parse_body("{unknown}");
$uidtable = $q->uid_to_tablename($_GET["member-value"]);
$users = new usersMenus();
$uidenc = urlencode($_GET["member-value"]);
$xtime = $_GET["xtime"];
if (!$q->TABLE_EXISTS("`www_{$uidtable}`")) {
echo "<p class=text-error>No table «`www_{$uidtable}`» for {$_GET["member-value"]}</p>";
return;
}
$curdate = date("Y-m-d", $xtime);
$curdateT = time_to_date($xtime);
$sql = "SELECT SUM(hits) as hits,SUM(size) as size,category,zDate FROM `www_{$uidtable}`\n\tGROUP BY category,zDate HAVING zDate='{$curdate}' ORDER BY size DESC,hits DESC";
$results = $q->QUERY_SQL($sql);
if (!$q->ok) {
echo "<p class=text-error>{$q->mysql_error}</p>";
return;
}
$_GET["member-value"] = urlencode($_GET["member-value"]);
while ($ligne = @mysql_fetch_array($results, MYSQL_ASSOC)) {
$size = FormatBytes($ligne["size"] / 1024);
$hits = FormatNumber($ligne["hits"]);
$category = $ligne["category"];
$category_text = $ligne["category"];
if (trim($ligne["category"]) == null) {
$category_text = $unknown;
}
$categoryenc = urlencode($ligne["category"]);
$js = "Loadjs('miniadm.webstats.ByMember.ByCategory.ByDay.php?uid={$_GET["member-value"]}&category={$categoryenc}&xtime={$xtime}')";
$link = $boot->trswitch($js);
$tr[] = "\n\t\t<tr>\n\t\t<td {$link}><i class='icon-tag'></i> {$category_text}</a></td>\n\t\t<td {$link}><i class='icon-info-sign'></i> {$size}</td>\n\t\t<td {$link}><i class='icon-info-sign'></i> {$hits}</td>\n\t\t</tr>";
}
echo $tpl->_ENGINE_parse_body("\n\t\t\t<H3>{$_GET["member-value"]} {categories} {$curdateT}</H3>\n\t\t\t<table class='table table-bordered table-hover'>\n\n\t\t\t<thead>\n\t\t\t\t<tr>\n\t\t\t\t\t<th>{category}</th>\n\t\t\t\t\t<th>{size}</th>\n\t\t\t\t\t<th>{hits}</th>\n\t\t\t\t</tr>\n\t\t\t</thead>\n\t\t\t <tbody>\n\t\t\t\t") . @implode("", $tr) . "</tbody></table>\n\t\t\t<div style='text-align:right;margin-top:10px'>{$bt}</div>";
}
