diff options
Diffstat (limited to 'admin/stats.php')
| -rw-r--r-- | admin/stats.php | 639 |
1 files changed, 405 insertions, 234 deletions
diff --git a/admin/stats.php b/admin/stats.php index 0941e96d8..228860b9c 100644 --- a/admin/stats.php +++ b/admin/stats.php @@ -24,295 +24,466 @@ // | Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, | // | USA. | // +-----------------------------------------------------------------------+ -if( !defined("PHPWG_ROOT_PATH") ) + +if (!defined("PHPWG_ROOT_PATH")) { - die ("Hacking attempt!"); + die ("Hacking attempt!"); } include_once(PHPWG_ROOT_PATH.'admin/include/functions.php'); // +-----------------------------------------------------------------------+ +// | Functions | +// +-----------------------------------------------------------------------+ + +function get_summary($year = null, $month = null, $day = null) +{ + $query = ' +SELECT + year, + month, + day, + hour, + nb_pages + FROM '.HISTORY_SUMMARY_TABLE; + + if (isset($day)) + { + $query.= ' + WHERE year = '.$year.' + AND month = '.$month.' + AND day = '.$day.' + AND hour IS NOT NULL + ORDER BY + year ASC, + month ASC, + day ASC, + hour ASC +;'; + } + elseif (isset($month)) + { + $query.= ' + WHERE year = '.$year.' + AND month = '.$month.' + AND day IS NOT NULL + AND hour IS NULL + ORDER BY + year ASC, + month ASC, + day ASC +;'; + } + elseif (isset($year)) + { + $query.= ' + WHERE year = '.$year.' + AND month IS NOT NULL + AND day IS NULL + ORDER BY + year ASC, + month ASC +;'; + } + else + { + $query.= ' + WHERE year IS NOT NULL + AND month IS NULL + ORDER BY + year ASC +;'; + } + + $result = pwg_query($query); + + $output = array(); + while ($row = mysql_fetch_array($result)) + { + array_push($output, $row); + } + + return $output; +} + +// +-----------------------------------------------------------------------+ // | Check Access and exit when user status is not ok | // +-----------------------------------------------------------------------+ + check_status(ACCESS_ADMINISTRATOR); -$url_img = PHPWG_ROOT_PATH.'admin/images/'; -$nls_value_title = $lang['w_month']; -$group_clause = "DATE_FORMAT(date,'%Y-%m') DESC"; -$where_clause = "1"; +// +-----------------------------------------------------------------------+ +// | Refresh summary from details | +// +-----------------------------------------------------------------------+ + +$query = ' +SELECT + year, + month, + day, + hour, + max(id) AS max_id, + COUNT(*) AS nb_pages + FROM '.HISTORY_TABLE.' + WHERE summarized = \'false\' + GROUP BY + year ASC, + month ASC, + day ASC, + hour ASC +;'; +$result = pwg_query($query); + +$need_update = array(); +$max_id = 0; +$is_first = true; +$first_time_key = null; -if (isset($_GET['day']) && isset($_GET['month']) && isset($_GET['year']) ) +while ($row = mysql_fetch_array($result)) { - $url_img .= 'daily_stats.img.php?year='.$_GET['year'].'&month='.$_GET['month'].'&day='.$_GET['day']; - $nls_value_title = $lang['w_day']; - $group_clause = "DATE_FORMAT(date,'%Y-%m-%d') ASC"; - $where_clause = "(YEAR(date) = ".$_GET['year']." AND MONTH(date) = ".$_GET['month']." )"; + $time_keys = array( + sprintf( + '%4u', + $row['year'] + ), + sprintf( + '%4u.%02u', + $row['year'], $row['month'] + ), + sprintf( + '%4u.%02u.%02u', + $row['year'], $row['month'], $row['day'] + ), + sprintf( + '%4u.%02u.%02u.%02u', + $row['year'], $row['month'], $row['day'], $row['hour'] + ), + ); + + foreach ($time_keys as $time_key) + { + if (!isset($need_update[$time_key])) + { + $need_update[$time_key] = 0; + } + $need_update[$time_key] += $row['nb_pages']; + } + + if ($row['max_id'] > $max_id) + { + $max_id = $row['max_id']; + } + + if ($is_first) + { + $is_first = false; + $first_time_key = $time_keys[3]; + } } -elseif (isset($_GET['month']) && isset($_GET['year']) ) + +// Only the oldest time_key might be already summarized, so we have to +// update the 4 corresponding lines instead of simply inserting them. +// +// For example, if the oldest unsummarized is 2005.08.25.21, the 4 lines +// that can be updated are: +// +// +---------------+----------+ +// | id | nb_pages | +// +---------------+----------+ +// | 2005 | 241109 | +// | 2005.08 | 20133 | +// | 2005.08.25 | 620 | +// | 2005.08.25.21 | 151 | +// +---------------+----------+ + +$existing_time_keys = array(); + +if (isset($first_time_key)) +{ + list($year, $month, $day, $hour) = explode('.', $first_time_key); + + $time_keys = array( + sprintf('%4u', $year), + sprintf('%4u.%02u', $year, $month), + sprintf('%4u.%02u.%02u', $year, $month, $day), + sprintf('%4u.%02u.%02u.%02u', $year, $month, $day, $hour), + ); + + $query = ' +SELECT + id, + nb_pages + FROM '.HISTORY_SUMMARY_TABLE.' + WHERE id IN (\''.implode("', '", $time_keys).'\') +;'; + $result = pwg_query($query); + while ($row = mysql_fetch_array($result)) + { + $existing_time_keys[ $row['id'] ] = $row['nb_pages']; + } +} + +$updates = array(); +$inserts = array(); + +foreach (array_keys($need_update) as $time_key) +{ + $time_tokens = explode('.', $time_key); + + if (isset($existing_time_keys[$time_key])) + { + array_push( + $updates, + array( + 'id' => $time_key, + 'nb_pages' => $existing_time_keys[$time_key] + $need_update[$time_key], + ) + ); + } + else + { + array_push( + $inserts, + array( + 'id' => $time_key, + 'year' => $time_tokens[0], + 'month' => @$time_tokens[1], + 'day' => @$time_tokens[2], + 'hour' => @$time_tokens[3], + 'nb_pages' => $need_update[$time_key], + ) + ); + } +} + +if (count($updates) > 0) { - $url_img .= 'monthly_stats.img.php?year='.$_GET['year'].'&month='.$_GET['month']; - $nls_value_title = $lang['w_day']; - $group_clause = "DATE_FORMAT(date,'%Y-%m-%d') ASC"; - $where_clause = "(YEAR(date) = ".$_GET['year']." AND MONTH(date) = ".$_GET['month']." )"; + mass_updates( + HISTORY_SUMMARY_TABLE, + array( + 'primary' => array('id'), + 'update' => array('nb_pages'), + ), + $updates + ); } -else + +if (count($inserts) > 0) { - $url_img .= 'global_stats.img.php'; + mass_inserts( + HISTORY_SUMMARY_TABLE, + array_keys($inserts[0]), + $inserts + ); } +if ($max_id != 0) +{ + $query = ' +UPDATE '.HISTORY_TABLE.' + SET summarized = \'true\' + WHERE summarized = \'false\' + AND id <= '.$max_id.' +;'; + pwg_query($query); +} -//----------------------------------------------------- template initialization -if (isset($_GET['day']) && isset($_GET['month']) && isset($_GET['year']) ) +// +-----------------------------------------------------------------------+ +// | Page parameters check | +// +-----------------------------------------------------------------------+ + +foreach (array('day', 'month', 'year') as $key) { - $date_of_day=$_GET['day'].' '.$lang['month'][$_GET['month']].' '.$_GET['year']; - $title_page=$lang['stats_day_title'].' : '.$date_of_day; - $url_back = PHPWG_ROOT_PATH."admin.php?page=stats"; - $url_back = $url_back; - $title_details='<a href='.$url_back.'>'.$lang['stats_day_title'].'</a>'; - $title_day = $date_of_day; + if (isset($_GET[$key])) + { + $page[$key] = (int)$_GET[$key]; + } } -elseif ( isset($_GET['month']) && isset($_GET['year']) ) + +if (isset($page['day'])) { - $date_of_day=$lang['month'][$_GET['month']].' '.$_GET['year']; - $title_page=$lang['stats_month_title'].' : '.$date_of_day; - $url_back = PHPWG_ROOT_PATH."admin.php?page=stats"; - $url_back = $url_back; - $title_details='<a href='.$url_back.'>'.$lang['stats_day_title'].'</a>'; - $title_day=$lang['today']; + if (!isset($page['month'])) + { + die('month is missing in URL'); + } } -else + +if (isset($page['month'])) { - $date_of_day=''; - $title_page=$lang['stats_title']; - $title_details=$lang['stats_month_title']; - $title_day=$lang['today']; + if (!isset($page['year'])) + { + die('year is missing in URL'); + } } +$url_img = PHPWG_ROOT_PATH.'admin/images/stats.img.php'; -$template->set_filenames( array('stats'=>'admin/stats.tpl') ); - -$template->assign_vars(array( - 'L_VALUE'=>$nls_value_title, - 'L_PAGES_SEEN'=>$lang['stats_pages_seen'], - 'L_VISITORS'=>$lang['visitors'], - 'L_PICTURES'=>$lang['pictures'], - 'L_STAT_TITLE'=>$lang['stats_title'], - 'L_STAT_MONTH_TITLE'=>$lang['stats_month_title'], - 'L_STAT_MONTHLY_ALT'=>$lang['stats_global_graph_title'], - 'L_STAT_TITLE'=>$title_page, - 'L_STAT_DETAIL_TITLE'=>$title_details, - 'L_DATE_TITLE'=>$title_day, - 'L_STAT_MONTHLY_ALT'=>$lang['stats_global_graph_title'], - 'L_STAT_HOUR'=>$lang['stats_hour'], - 'L_STAT_LOGIN'=>$lang['stats_login'], - 'L_STAT_ADDR'=>$lang['stats_addr'], - 'L_STAT_CATEGORY'=>$lang['stats_category'], - 'L_STAT_FILE'=>$lang['stats_file'], - 'L_STAT_PICTURE'=>$lang['stats_picture'], +if (isset($page['year'])) +{ + $url_img.= '?year='.$page['year']; +} + +if (isset($page['month'])) +{ + $url_img.= '&month='.$page['month']; +} + +if (isset($page['day'])) +{ + $url_img.= '&day='.$page['day']; +} + +$summary_lines = get_summary( + @$page['year'], + @$page['month'], + @$page['day'] + ); + +// +-----------------------------------------------------------------------+ +// | Display statistics header | +// +-----------------------------------------------------------------------+ + +// page title creation +$title_parts = array(); + +$url = PHPWG_ROOT_PATH.'admin.php?page=stats'; + +array_push( + $title_parts, + '<a href="'.$url.'">'.l10n('Overall').'</a>' + ); + +$period_label = l10n('Year'); + +if (isset($page['year'])) +{ + $url.= '&year='.$page['year']; + + array_push( + $title_parts, + '<a href="'.$url.'">'.sprintf(l10n('Year %d'), $page['year']).'</a>' + ); + + $period_label = l10n('Month'); +} + +if (isset($page['month'])) +{ + $url.= '&month='.$page['month']; + + array_push( + $title_parts, + '<a href="'.$url.'">'.$lang['month'][$page['month']].'</a>' + ); + + $period_label = l10n('Day'); +} + +if (isset($page['day'])) +{ + $url.= '&day='.$page['day']; + + $time = mktime(12, 0, 0, $page['month'], $page['day'], $page['year']); - 'IMG_REPORT'=>$url_img - )); + $day_title = sprintf( + '%u (%s)', + $page['day'], + $lang['day'][date('w', $time)] + ); + + array_push( + $title_parts, + '<a href="'.$url.'">'.$day_title.'</a>' + ); -//---------------------------------------------------------------- log history -$query = ' -SELECT DISTINCT COUNT(*) as p, - DAYOFMONTH(date) as d, - MONTH(date) as m, - YEAR(date) as y - FROM '.HISTORY_TABLE.' - WHERE '.$where_clause.' - GROUP BY '.$group_clause.';'; - -$result = pwg_query( $query ); -$i=0; -while ( $row = mysql_fetch_array( $result ) ) + $period_label = l10n('Hour'); +} + +$template->set_filenames(array('stats'=>'admin/stats.tpl')); + +$template->assign_vars( + array( + 'L_STAT_TITLE' => implode($conf['level_separator'], $title_parts), + 'SRC_REPORT' => $url_img, + 'PERIOD_LABEL' => $period_label, + ) + ); + +// +-----------------------------------------------------------------------+ +// | Display statistic rows | +// +-----------------------------------------------------------------------+ + +$i = 1; + +foreach ($summary_lines as $line) { - $where_clause=""; + // echo '<pre>'; print_r($line); echo '</pre>'; + $value = ''; - if (isset($_GET['month']) && isset($_GET['year']) ) + + if (isset($line['hour'])) + { + $value.= $line['hour'].' '.l10n('hour'); + } + else if (isset($line['day'])) { - $where_clause = 'DAYOFMONTH(date) = '.$row['d'].' - AND MONTH(date) = '.$row['m'].' - AND YEAR(date) = '.$row['y']; - - $week_day = - $lang['day'][date('w', mktime(12,0,0,$row['m'],$row['d'],$row['y']))]; - $url = PHPWG_ROOT_PATH.'admin.php' .'?page=stats' - .'&year='.$row['y'] - .'&month='.$row['m'] - .'&day='.$row['d'] + .'&year='.$line['year'] + .'&month='.$line['month'] + .'&day='.$line['day'] ; + $time = mktime(12, 0, 0, $line['month'], $line['day'], $line['year']); + $value = '<a href="'.$url.'">'; - $value.= $row['d'].' ('.$week_day.')'; - $value.= "</a>"; + $value.= $line['day'].' ('.$lang['day'][date('w', $time)].')'; + $value.= "</a>"; } - else + else if (isset($line['month'])) { - $current_month = $row['y']."-"; - if ($row['m'] <10) {$current_month.='0';} - $current_month .= $row['m']; - - $where_clause = "DATE_FORMAT(date,'%Y-%m') = '".$current_month."'"; - $url = PHPWG_ROOT_PATH.'admin.php' .'?page=stats' - .'&year='.$row['y'] - .'&month='.$row['m'] + .'&year='.$line['year'] + .'&month='.$line['month'] ; $value = '<a href="'.$url.'">'; - $value.= $lang['month'][$row['m']].' '.$row['y']; + $value.= $lang['month'][$line['month']]; $value.= "</a>"; } - - // Number of pictures seen - $query = ' -SELECT COUNT(*) as p - FROM '.HISTORY_TABLE.' - WHERE '.$where_clause.' - AND FILE = \'picture\' -;'; - $pictures = mysql_fetch_array(pwg_query( $query )); - - // Number of different visitors - $query = ' -SELECT COUNT(*) as p, login - FROM '.HISTORY_TABLE.' - WHERE '.$where_clause.' - GROUP BY login, IP -;'; - $user_results = pwg_query( $query ); - $nb_visitors = 0; - $auth_users = array(); - while ( $user_array = mysql_fetch_array( $user_results ) ) + else { - if ($user_array['login'] == 'guest') - $nb_visitors += 1; - else - array_push($auth_users, $user_array['login']); + // at least the year is defined + $url = + PHPWG_ROOT_PATH.'admin.php' + .'?page=stats' + .'&year='.$line['year'] + ; + + $value = '<a href="'.$url.'">'; + $value.= $line['year']; + $value.= "</a>"; } - $nb_visitors +=count(array_unique($auth_users)); - $class = ($i % 2)? 'row1':'row2'; $i++; - $template->assign_block_vars('statrow',array( - 'VALUE'=>$value, - 'PAGES'=>$row['p'], - 'VISITORS'=>$nb_visitors, - 'IMAGES'=>$pictures['p'], - - 'T_CLASS'=>$class - )); -} -$nb_visitors = mysql_num_rows( $result ); -$days = array(); -$max_nb_visitors = 0; -$max_pages_seen = 0; - -//----------------------------------------------------------- stats / jour - -if ( isset( $_GET['month'] ) && isset( $_GET['month'] ) && isset( $_GET['day'] ) ) -{ if ($_GET['day'] <10) {$current_day='0'; - $current_day.= $_GET['day'];} - else {$current_day = $_GET['day'];} - if ($_GET['month'] <10) {$current_month='0'; - $current_month.= $_GET['month'];} - else {$current_month = $_GET['month'];} - $current_year = $_GET['year']; -} - -else -{ $current_date = GetDate(); - if ($current_date['mday'] <10) {$current_day='0'; - $current_day.= $current_date['mday'];} - else {$current_day = $current_date['mday'];} - if ($current_date['mon'] <10) {$current_month='0'; - $current_month.= $current_date['mon'];} - else {$current_month = $current_date['mon'];} - $current_year = $current_date['year']; + $template->assign_block_vars( + 'statrow', + array( + 'VALUE' => $value, + 'PAGES' => $line['nb_pages'], + + 'T_CLASS' => ($i++ % 2) ? 'row1' : 'row2' + ) + ); } -// Set WHERE clause -$where = ' WHERE DATE_FORMAT(date,\'%Y-%m-%d\') = \''.$current_year."-".$current_month."-".$current_day.'\''; - -// Set LIMIT clause -$limit = ' LIMIT '; -$page['start'] = 0; -if (isset($_GET['start']) and is_numeric($_GET['start'])) $page['start'] = abs($_GET['start']); -$limit .= $page['start']; -$limit .= ','.$conf['nb_logs_page']; - -$query = ' -SELECT DATE_FORMAT(date,\'%H:%i:%s\') AS hour, - login, - IP, - category, - file, - picture - FROM '.HISTORY_TABLE. - $where.' - ORDER BY date DESC'. - $limit. - ';'; - - -$result = pwg_query( $query ); - -$i=0; - -while ( $row = mysql_fetch_array( $result ) ) -{ - $class = ($i % 2)? 'row1':'row2'; $i++; - $template->assign_block_vars('detail',array( - 'HOUR'=>$row['hour'], - 'LOGIN'=>$row['login'], - 'IP'=>$row['IP'], - 'CATEGORY'=>$row['category'], - 'FILE'=>$row['file'], - 'PICTURE'=>$row['picture'], - 'T_CLASS'=>$class - )); - } - - -// Get total number of logs -$query = ' - SELECT COUNT(date) as nb_logs - FROM '.HISTORY_TABLE. - $where.' - ;'; - - $result = pwg_query($query); - $row = mysql_fetch_array($result); - $page['nb_logs']=$row['nb_logs']; - - //display nav bar -$url = $_SERVER['PHP_SELF'].'?page=stats'; -$url.= isset($_GET['year']) ? '&year='.$_GET['year'] : ''; -$url.= isset($_GET['month']) ? '&month='.$_GET['month'] : ''; -$url.= isset($_GET['day']) ? '&day='.$_GET['day'] : ''; - -$page['navigation_bar'] = -create_navigation_bar( - $url, - $page['nb_logs'], - $page['start'], - $conf['nb_logs_page'] - ); - -$template->assign_block_vars( - 'navigation', - array( - 'NAV_BAR' => $page['navigation_bar'] - ) - ); +// +-----------------------------------------------------------------------+ +// | Sending html code | +// +-----------------------------------------------------------------------+ -//----------------------------------------------------------- sending html code $template->assign_var_from_handle('ADMIN_CONTENT', 'stats'); ?> |