From 09ca1e82ca77e010130e1900692c883a3989fcf7 Mon Sep 17 00:00:00 2001 From: rurban Date: Sat, 12 Feb 2005 17:26:24 +0000 Subject: [PATCH] renamed to Sql. Added translations (inc not yet) git-svn-id: svn://svn.code.sf.net/p/phpwiki/code/trunk@4583 96ab9672-09ca-45d6-a79d-3d69d39ca109 --- ...eAccessLog.php => AnalyseAccessLogSql.php} | 121 +++++++++++++++--- 1 file changed, 105 insertions(+), 16 deletions(-) rename lib/plugin/{AnalyseAccessLog.php => AnalyseAccessLogSql.php} (63%) mode change 100644 => 100755 diff --git a/lib/plugin/AnalyseAccessLog.php b/lib/plugin/AnalyseAccessLogSql.php old mode 100644 new mode 100755 similarity index 63% rename from lib/plugin/AnalyseAccessLog.php rename to lib/plugin/AnalyseAccessLogSql.php index fed1f4c36..b83f2ffff --- a/lib/plugin/AnalyseAccessLog.php +++ b/lib/plugin/AnalyseAccessLogSql.php @@ -1,5 +1,5 @@ _getWhereConditions($args); + if ($where_conditions<>'') + $where_conditions = 'WHERE '.$where_conditions.' '; + $query = "SELECT " + ."left(referer,length(referer)-instr(reverse(referer),'?')) AS Referring_URL, " + ."count(*) AS Referral_Count " + ."FROM $accesslog " + .$where_conditions + ."GROUP BY Referring_URL"; + } elseif ($args['mode']=='referring_domains') { if ($where_conditions<>'') $where_conditions = 'WHERE '.$where_conditions.' '; @@ -106,7 +116,7 @@ extends WikiPlugin .$where_conditions ."GROUP BY User"; - } elseif ($args['mode']=='host-users') { + } elseif ($args['mode']=='host_users') { if ($where_conditions<>'') $where_conditions = 'WHERE '.$where_conditions.' '; $query = "SELECT " @@ -116,14 +126,90 @@ extends WikiPlugin ."FROM $accesslog " .$where_conditions ."GROUP BY Remote_Host, User"; + + } elseif ($args['mode']=="search_bots") { + // This queries for all entries in the SQL access log table that + // have a dns name that I know to be a web search engine crawler and + // categorises the results into time buckets as per the list below + // 0 - 1 minute - 60 + // 1 - 1 hour - 3600 = 60 * 60 + // 2 - 1 day - 86400 = 60 * 60 * 24 + // 3 - 1 week - 604800 = 60 * 60 * 24 * 7 + // 4 - 1 month - 2629800 = 60 * 60 * 24 * 365.25 / 12 + // 5 - 1 year - 31557600 = 60 * 60 * 24 * 365.25 + + $now = time(); + $query = "SELECT " + ."IF($now-time_stamp<60, '"._("0 - last minute")."'," + ."IF($now-time_stamp<3600, '"._("1 - 1 minute to 1 hour")."'," + ."IF($now-time_stamp<86400, '"._("2 - 1 hour to 1 day")."'," + ."IF($now-time_stamp<604800, '"._("3 - 1 day to 1 week")."'," + ."IF($now-time_stamp<2629800, '"._("4 - 1 week to 1 month")."'," + ."IF($now-time_stamp<31557600, '"._("5 - 1 month to 1 year")."'," + ."'"._("6 - more than 1 year")."')))))) AS 'Time_Scale', " + ."remote_host AS 'Remote_Host', " + ."count(*) AS 'Access_Count' " + ."FROM $accesslog " + ."WHERE (remote_host LIKE '%googlebot.com' " + ."OR remote_host LIKE '%alexa.com' " + ."OR remote_host LIKE '%inktomisearch.com' " + ."OR remote_host LIKE '%msnbot.msn.com') " + .($where_conditions ? 'AND '.$where_conditions : '') + ."GROUP BY 'Time_Scale', 'Remote_Host'"; + + } elseif ($args['mode']=="search_bots_hits") { + // This queries for all entries in the SQL access log table that + // have a dns name that I know to be a web search engine crawler and + // displays the URI that was hit. + // If PHPSESSID appears in the URI, just display the URI to the left of this + + $now = time(); + $query = "SELECT " + ."IF($now-time_stamp<60, '"._("0 - last minute")."'," + ."IF($now-time_stamp<3600, '"._("1 - 1 minute to 1 hour")."'," + ."IF($now-time_stamp<86400, '"._("2 - 1 hour to 1 day")."'," + ."IF($now-time_stamp<604800, '"._("3 - 1 day to 1 week")."'," + ."IF($now-time_stamp<2629800, '"._("4 - 1 week to 1 month")."'," + ."IF($now-time_stamp<31557600, '"._("5 - 1 month to 1 year")."'," + ."'"._("6 - more than 1 year")."')))))) AS 'Time_Scale', " + ."remote_host AS 'Remote_Host', " + ."IF(instr(request_uri, 'PHPSESS')=0, request_uri,left(request_uri, instr(request_uri, 'PHPSESS')-2)) AS 'Request_URI' " + ."FROM $accesslog " + ."WHERE (remote_host LIKE '%googlebot.com' " + ."OR remote_host LIKE '%alexa.com' " + ."OR remote_host LIKE '%inktomisearch.com' " + ."OR remote_host LIKE '%msnbot.msn.com') " + .($where_conditions ? 'AND '.$where_conditions : '') + ."ORDER BY time_stamp"; } return $query; } + /** Honeypot for xgettext. Those strings are translated dynamically. + */ + function _locale_dummy() { + $dummy = array( + // mode caption + _("referring_urls"), + _("external_referers"), + _("referring_domains"), + _("remote_hosts"), + _("users"), + _("host_users"), + _("search_bots"), + _("search_bots_hits"), + // period header + _("minutes"), + _("hours"), + _("days"), + _("weeks"), + ); + } + function getDefaultArguments() { return array( 'mode' => 'referring_domains', - // referring_domains, referring_urls, remote_hosts, users, host-users + // referring_domains, referring_urls, remote_hosts, users, host_users 'caption' => '', // blank means use the mode as the caption/title for the output 'local_referrers' => 'true', // only show external referring sites @@ -134,7 +220,7 @@ extends WikiPlugin } function getName () { - return _("AnalyseAccessLog"); + return _("AnalyseAccessLogSql"); } function getDescription () { @@ -173,9 +259,9 @@ extends WikiPlugin // otherwise the headers will not be ready $tbody = $this->_getQueryResults($query, $dbi); - return HTML::table(array( 'border' => 1, - 'cellspacing' => 1, - 'cellpadding' => 1), + return HTML::table(array('border' => 1, + 'cellspacing' => 1, + 'cellpadding' => 1), HTML::caption(HTML::h1(HTML::br(),$this->_getCaption($args))), HTML::thead($this->_theadrow), $tbody); @@ -202,7 +288,7 @@ extends WikiPlugin } function _setHeaders($row) { - if(!$this->_headerSet) { + if (!$this->_headerSet) { foreach ($row as $key => $value) { $this->_theadrow->pushContent(HTML::th(_($key))); } @@ -242,7 +328,7 @@ extends WikiPlugin } // The assumed contract is that there is a space at the end of the - // conditions string,so that following SQL clauses (such as GROUP BY) + // conditions string, so that following SQL clauses (such as GROUP BY) // will not cause a syntax error if ($where_conditions<>'') $where_conditions = $where_conditions.' '; @@ -253,15 +339,18 @@ extends WikiPlugin function _getCaption(&$args) { $caption = $args['caption']; if ($caption=='') - $caption = _($args['mode']); + $caption = gettext($args['mode']); if ($args['period']<>'' && $args['count']) - $caption = $caption." - ".$args['count']." "._($args['period']); + $caption = $caption." - ".$args['count']." ". gettext($args['period']); return $caption; } } // $Log: not supported by cvs2svn $ +// Revision 1.1 2005/02/02 20:41:02 rurban +// Accesslog sql queries +// // For emacs users // Local Variables: -- 2.45.0