Changeset 1727


Ignore:
Timestamp:
Jan 17, 2007, 12:39:39 AM (18 years ago)
Author:
plg
Message:

Modification: new data model for history, more compact, more efficient. A
summary table is used as cache for history stats display.

New: a Perl script fill_history.pl was added to simulate a high load on
history table (making the efficiency of the new data model obvious).

Modification: function prepend_append_array_items moved from
include/functions_search.inc.php to include/functions_search.inc.php since
this function is used in new file admin/history.php

Modification: admin/images/*_stats.img.php replaced by a simpler and more
generic admin/images/stats.img.php unique file.

New: a history detail search page was added. Currently, only start and end
dates can be modified, it's just a beginning.

Location:
trunk
Files:
5 added
3 deleted
13 edited

Legend:

Unmodified
Added
Removed

  • trunk/admin.php

    r1705 r1727  
    8383  array(
    8484    'U_SITE_MANAGER'=> $link_start.'site_manager',
    85     'U_HISTORY'=> $link_start.'stats',
     85    'U_HISTORY_STAT'=> $link_start.'stats',
     86    'U_HISTORY_SEARCH'=> $link_start.'history',
    8687    'U_FAQ'=> $link_start.'help',
    8788    'U_SITES'=> $link_start.'remote_site',

  • trunk/admin/stats.php

    r1358 r1727  
    2525// | USA.                                                                  |
    2626// +-----------------------------------------------------------------------+
    27 if( !defined("PHPWG_ROOT_PATH") )
    28 {
    29         die ("Hacking attempt!");
     27
     28if (!defined("PHPWG_ROOT_PATH"))
     29{
     30  die ("Hacking attempt!");
    3031}
    3132
    … …  
    3334
    3435// +-----------------------------------------------------------------------+
     36// | Functions                                                             |
     37// +-----------------------------------------------------------------------+
     38
     39function get_summary($year = null, $month = null, $day = null)
     40{
     41  $query = '
     42SELECT
     43    year,
     44    month,
     45    day,
     46    hour,
     47    nb_pages
     48  FROM '.HISTORY_SUMMARY_TABLE;
     49 
     50  if (isset($day))
     51  {
     52    $query.= '
     53  WHERE year = '.$year.'
     54    AND month = '.$month.'
     55    AND day = '.$day.'
     56    AND hour IS NOT NULL
     57  ORDER BY
     58    year ASC,
     59    month ASC,
     60    day ASC,
     61    hour ASC
     62;';
     63  }
     64  elseif (isset($month))
     65  {
     66    $query.= '
     67  WHERE year = '.$year.'
     68    AND month = '.$month.'
     69    AND day IS NOT NULL
     70    AND hour IS NULL
     71  ORDER BY
     72    year ASC,
     73    month ASC,
     74    day ASC
     75;';
     76  }
     77  elseif (isset($year))
     78  {
     79    $query.= '
     80  WHERE year = '.$year.'
     81    AND month IS NOT NULL
     82    AND day IS NULL
     83  ORDER BY
     84    year ASC,
     85    month ASC
     86;';   
     87  }
     88  else
     89  {
     90    $query.= '
     91  WHERE year IS NOT NULL
     92    AND month IS NULL
     93  ORDER BY
     94    year ASC
     95;';   
     96  }
     97
     98  $result = pwg_query($query);
     99
     100  $output = array();
     101  while ($row = mysql_fetch_array($result))
     102  {
     103    array_push($output, $row);
     104  }
     105
     106  return $output;
     107}
     108
     109// +-----------------------------------------------------------------------+
    35110// | Check Access and exit when user status is not ok                      |
    36111// +-----------------------------------------------------------------------+
     112
    37113check_status(ACCESS_ADMINISTRATOR);
    38114
    39 $url_img = PHPWG_ROOT_PATH.'admin/images/';
    40 $nls_value_title = $lang['w_month'];
    41 $group_clause = "DATE_FORMAT(date,'%Y-%m') DESC";
    42 $where_clause = "1";
    43 
    44 
    45 if (isset($_GET['day']) && isset($_GET['month']) && isset($_GET['year']) )
    46 {
    47   $url_img .= 'daily_stats.img.php?year='.$_GET['year'].'&month='.$_GET['month'].'&day='.$_GET['day'];
    48   $nls_value_title = $lang['w_day'];
    49   $group_clause = "DATE_FORMAT(date,'%Y-%m-%d') ASC";
    50   $where_clause = "(YEAR(date) = ".$_GET['year']." AND MONTH(date) = ".$_GET['month']." )";
    51 }
    52 elseif (isset($_GET['month']) && isset($_GET['year']) )
    53 {
    54   $url_img .= 'monthly_stats.img.php?year='.$_GET['year'].'&month='.$_GET['month'];
    55   $nls_value_title = $lang['w_day'];
    56   $group_clause = "DATE_FORMAT(date,'%Y-%m-%d') ASC";
    57   $where_clause = "(YEAR(date) = ".$_GET['year']." AND MONTH(date) = ".$_GET['month']." )";
    58 }
    59 else
    60 {
    61   $url_img .= 'global_stats.img.php';
    62 }
    63 
    64 
    65 //----------------------------------------------------- template initialization
    66 if (isset($_GET['day']) && isset($_GET['month']) && isset($_GET['year']) )
    67 {
    68   $date_of_day=$_GET['day'].' '.$lang['month'][$_GET['month']].' '.$_GET['year'];
    69   $title_page=$lang['stats_day_title'].' : '.$date_of_day;
    70   $url_back = PHPWG_ROOT_PATH."admin.php?page=stats";
    71   $url_back = $url_back;
    72   $title_details='<a href='.$url_back.'>'.$lang['stats_day_title'].'</a>';
    73   $title_day = $date_of_day;
    74 }
    75 elseif ( isset($_GET['month']) && isset($_GET['year']) )
    76 {
    77   $date_of_day=$lang['month'][$_GET['month']].' '.$_GET['year'];
    78   $title_page=$lang['stats_month_title'].' : '.$date_of_day;
    79   $url_back = PHPWG_ROOT_PATH."admin.php?page=stats";
    80   $url_back = $url_back;
    81   $title_details='<a href='.$url_back.'>'.$lang['stats_day_title'].'</a>';
    82   $title_day=$lang['today'];
    83 }
    84 else
    85 {
    86   $date_of_day='';
    87   $title_page=$lang['stats_title'];
    88   $title_details=$lang['stats_month_title'];
    89   $title_day=$lang['today'];
    90 }
    91 
    92 
    93 $template->set_filenames( array('stats'=>'admin/stats.tpl') );
    94 
    95 $template->assign_vars(array(
    96   'L_VALUE'=>$nls_value_title,
    97   'L_PAGES_SEEN'=>$lang['stats_pages_seen'],
    98   'L_VISITORS'=>$lang['visitors'],
    99   'L_PICTURES'=>$lang['pictures'],
    100   'L_STAT_TITLE'=>$lang['stats_title'],
    101   'L_STAT_MONTH_TITLE'=>$lang['stats_month_title'],
    102   'L_STAT_MONTHLY_ALT'=>$lang['stats_global_graph_title'],
    103   'L_STAT_TITLE'=>$title_page,
    104   'L_STAT_DETAIL_TITLE'=>$title_details,
    105   'L_DATE_TITLE'=>$title_day,
    106   'L_STAT_MONTHLY_ALT'=>$lang['stats_global_graph_title'],
    107   'L_STAT_HOUR'=>$lang['stats_hour'],
    108   'L_STAT_LOGIN'=>$lang['stats_login'],
    109   'L_STAT_ADDR'=>$lang['stats_addr'],
    110   'L_STAT_CATEGORY'=>$lang['stats_category'],
    111   'L_STAT_FILE'=>$lang['stats_file'],
    112   'L_STAT_PICTURE'=>$lang['stats_picture'],
    113  
    114   'IMG_REPORT'=>$url_img
    115   ));
    116 
    117 //---------------------------------------------------------------- log  history
     115// +-----------------------------------------------------------------------+
     116// | Refresh summary from details                                          |
     117// +-----------------------------------------------------------------------+
     118
    118119$query = '
    119 SELECT DISTINCT COUNT(*) as p,
    120        DAYOFMONTH(date) as d,
    121        MONTH(date) as m,
    122        YEAR(date) as y
    123   FROM '.HISTORY_TABLE.'
    124   WHERE '.$where_clause.'
    125   GROUP BY '.$group_clause.';';
    126 
    127 $result = pwg_query( $query );
    128 $i=0;
    129 while ( $row = mysql_fetch_array( $result ) )
    130 {
    131   $where_clause="";
     120SELECT
     121    year,
     122    month,
     123    day,
     124    hour,
     125    max(id) AS max_id,
     126    COUNT(*) AS nb_pages
     127  FROM '.HISTORY_TABLE.'
     128  WHERE summarized = \'false\'
     129  GROUP BY
     130    year ASC,
     131    month ASC,
     132    day ASC,
     133    hour ASC
     134;';
     135$result = pwg_query($query);
     136
     137$need_update = array();
     138
     139$max_id = 0;
     140$is_first = true;
     141$first_time_key = null;
     142
     143while ($row = mysql_fetch_array($result))
     144{
     145  $time_keys = array(
     146    sprintf(
     147      '%4u',
     148      $row['year']
     149      ),
     150    sprintf(
     151      '%4u.%02u',
     152      $row['year'], $row['month']
     153      ),
     154    sprintf(
     155      '%4u.%02u.%02u',
     156      $row['year'], $row['month'], $row['day']
     157      ),
     158    sprintf(
     159      '%4u.%02u.%02u.%02u',
     160      $row['year'], $row['month'], $row['day'], $row['hour']
     161      ),
     162    );
     163
     164  foreach ($time_keys as $time_key)
     165  {
     166    if (!isset($need_update[$time_key]))
     167    {
     168      $need_update[$time_key] = 0;
     169    }
     170    $need_update[$time_key] += $row['nb_pages'];
     171  }
     172
     173  if ($row['max_id'] > $max_id)
     174  {
     175    $max_id = $row['max_id'];
     176  }
     177
     178  if ($is_first)
     179  {
     180    $is_first = false;
     181    $first_time_key = $time_keys[3];
     182  }
     183}
     184
     185// Only the oldest time_key might be already summarized, so we have to
     186// update the 4 corresponding lines instead of simply inserting them.
     187//
     188// For example, if the oldest unsummarized is 2005.08.25.21, the 4 lines
     189// that can be updated are:
     190//
     191// +---------------+----------+
     192// | id            | nb_pages |
     193// +---------------+----------+
     194// | 2005          |   241109 |
     195// | 2005.08       |    20133 |
     196// | 2005.08.25    |      620 |
     197// | 2005.08.25.21 |      151 |
     198// +---------------+----------+
     199
     200$existing_time_keys = array();
     201
     202if (isset($first_time_key))
     203{
     204  list($year, $month, $day, $hour) = explode('.', $first_time_key);
     205
     206  $time_keys = array(
     207    sprintf('%4u',                $year),
     208    sprintf('%4u.%02u',           $year, $month),
     209    sprintf('%4u.%02u.%02u',      $year, $month, $day),
     210    sprintf('%4u.%02u.%02u.%02u', $year, $month, $day, $hour),
     211    );
     212 
     213  $query = '
     214SELECT
     215    id,
     216    nb_pages
     217  FROM '.HISTORY_SUMMARY_TABLE.'
     218  WHERE id IN (\''.implode("', '", $time_keys).'\')
     219;';
     220  $result = pwg_query($query);
     221  while ($row = mysql_fetch_array($result))
     222  {
     223    $existing_time_keys[ $row['id'] ] = $row['nb_pages'];
     224  }
     225}
     226
     227$updates = array();
     228$inserts = array();
     229
     230foreach (array_keys($need_update) as $time_key)
     231{
     232  $time_tokens = explode('.', $time_key);
     233
     234  if (isset($existing_time_keys[$time_key]))
     235  {
     236    array_push(
     237      $updates,
     238      array(
     239        'id'       => $time_key,
     240        'nb_pages' => $existing_time_keys[$time_key] + $need_update[$time_key],
     241        )
     242      );
     243  }
     244  else
     245  {
     246    array_push(
     247      $inserts,
     248      array(
     249        'id'       => $time_key,
     250        'year'     => $time_tokens[0],
     251        'month'    => @$time_tokens[1],
     252        'day'      => @$time_tokens[2],
     253        'hour'     => @$time_tokens[3],
     254        'nb_pages' => $need_update[$time_key],
     255        )
     256      );
     257  }
     258}
     259
     260if (count($updates) > 0)
     261{
     262  mass_updates(
     263    HISTORY_SUMMARY_TABLE,
     264    array(
     265      'primary' => array('id'),
     266      'update'  => array('nb_pages'),
     267      ),
     268    $updates
     269    );
     270}
     271
     272if (count($inserts) > 0)
     273{
     274  mass_inserts(
     275    HISTORY_SUMMARY_TABLE,
     276    array_keys($inserts[0]),
     277    $inserts
     278    );
     279}
     280
     281if ($max_id != 0)
     282{
     283  $query = '
     284UPDATE '.HISTORY_TABLE.'
     285  SET summarized = \'true\'
     286  WHERE summarized = \'false\'
     287    AND id <= '.$max_id.'
     288;';
     289  pwg_query($query);
     290}
     291
     292// +-----------------------------------------------------------------------+
     293// | Page parameters check                                                 |
     294// +-----------------------------------------------------------------------+
     295
     296foreach (array('day', 'month', 'year') as $key)
     297{
     298  if (isset($_GET[$key]))
     299  {
     300    $page[$key] = (int)$_GET[$key];
     301  }
     302}
     303
     304if (isset($page['day']))
     305{
     306  if (!isset($page['month']))
     307  {
     308    die('month is missing in URL');
     309  }
     310}
     311
     312if (isset($page['month']))
     313{
     314  if (!isset($page['year']))
     315  {
     316    die('year is missing in URL');
     317  }
     318}
     319
     320$url_img = PHPWG_ROOT_PATH.'admin/images/stats.img.php';
     321
     322if (isset($page['year']))
     323{
     324  $url_img.= '?year='.$page['year'];
     325}
     326
     327if (isset($page['month']))
     328{
     329  $url_img.= '&amp;month='.$page['month'];
     330}
     331
     332if (isset($page['day']))
     333{
     334  $url_img.= '&amp;day='.$page['day'];
     335}
     336
     337$summary_lines = get_summary(
     338  @$page['year'],
     339  @$page['month'],
     340  @$page['day']
     341  );
     342
     343// +-----------------------------------------------------------------------+
     344// | Display statistics header                                             |
     345// +-----------------------------------------------------------------------+
     346
     347// page title creation
     348$title_parts = array();
     349
     350$url = PHPWG_ROOT_PATH.'admin.php?page=stats';
     351
     352array_push(
     353  $title_parts,
     354  '<a href="'.$url.'">'.l10n('Overall').'</a>'
     355  );
     356
     357$period_label = l10n('Year');
     358
     359if (isset($page['year']))
     360{
     361  $url.= '&amp;year='.$page['year'];
     362
     363  array_push(
     364    $title_parts,
     365    '<a href="'.$url.'">'.sprintf(l10n('Year %d'), $page['year']).'</a>'
     366    );
     367
     368  $period_label = l10n('Month');
     369}
     370
     371if (isset($page['month']))
     372{
     373  $url.= '&amp;month='.$page['month'];
     374
     375  array_push(
     376    $title_parts,
     377    '<a href="'.$url.'">'.$lang['month'][$page['month']].'</a>'
     378    );
     379
     380  $period_label = l10n('Day');
     381}
     382
     383if (isset($page['day']))
     384{
     385  $url.= '&amp;day='.$page['day'];
     386
     387  $time = mktime(12, 0, 0, $page['month'], $page['day'], $page['year']);
     388 
     389  $day_title = sprintf(
     390    '%u (%s)',
     391    $page['day'],
     392    $lang['day'][date('w', $time)]
     393    );
     394 
     395  array_push(
     396    $title_parts,
     397    '<a href="'.$url.'">'.$day_title.'</a>'
     398    );
     399
     400  $period_label = l10n('Hour');
     401}
     402
     403$template->set_filenames(array('stats'=>'admin/stats.tpl'));
     404
     405$template->assign_vars(
     406  array(
     407    'L_STAT_TITLE' => implode($conf['level_separator'], $title_parts),
     408    'SRC_REPORT' => $url_img,
     409    'PERIOD_LABEL' => $period_label,
     410    )
     411  );
     412
     413// +-----------------------------------------------------------------------+
     414// | Display statistic rows                                                |
     415// +-----------------------------------------------------------------------+
     416
     417$i = 1;
     418
     419foreach ($summary_lines as $line)
     420{
     421  // echo '<pre>'; print_r($line); echo '</pre>';
     422
    132423  $value = '';
    133   if (isset($_GET['month']) && isset($_GET['year']) )
    134   {
    135     $where_clause = 'DAYOFMONTH(date) = '.$row['d'].'
    136     AND MONTH(date) = '.$row['m'].'
    137     AND YEAR(date) = '.$row['y'];
    138    
    139     $week_day =
    140       $lang['day'][date('w', mktime(12,0,0,$row['m'],$row['d'],$row['y']))];
    141 
     424 
     425  if (isset($line['hour']))
     426  {
     427    $value.= $line['hour'].' '.l10n('hour');
     428  }
     429  else if (isset($line['day']))
     430  {
    142431    $url =
    143432      PHPWG_ROOT_PATH.'admin.php'
    144433      .'?page=stats'
    145       .'&amp;year='.$row['y']
    146       .'&amp;month='.$row['m']
    147       .'&amp;day='.$row['d']
     434      .'&amp;year='.$line['year']
     435      .'&amp;month='.$line['month']
     436      .'&amp;day='.$line['day']
    148437      ;
    149438
     439    $time = mktime(12, 0, 0, $line['month'], $line['day'], $line['year']);
     440   
    150441    $value = '<a href="'.$url.'">';
    151     $value.= $row['d'].' ('.$week_day.')';
    152     $value.= "</a>";
    153   }
    154   else
    155   {
    156     $current_month = $row['y']."-";
    157     if ($row['m'] <10) {$current_month.='0';}
    158     $current_month .= $row['m'];
    159    
    160     $where_clause = "DATE_FORMAT(date,'%Y-%m') = '".$current_month."'";
    161 
     442    $value.= $line['day'].' ('.$lang['day'][date('w', $time)].')';
     443    $value.= "</a>";   
     444  }
     445  else if (isset($line['month']))
     446  {
    162447    $url =
    163448      PHPWG_ROOT_PATH.'admin.php'
    164449      .'?page=stats'
    165       .'&amp;year='.$row['y']
    166       .'&amp;month='.$row['m']
     450      .'&amp;year='.$line['year']
     451      .'&amp;month='.$line['month']
    167452      ;
    168453   
    169454    $value = '<a href="'.$url.'">';
    170     $value.= $lang['month'][$row['m']].' '.$row['y'];
     455    $value.= $lang['month'][$line['month']];
    171456    $value.= "</a>";
    172457  }
    173  
    174   // Number of pictures seen
    175   $query = '
    176 SELECT COUNT(*) as p
    177     FROM '.HISTORY_TABLE.'
    178     WHERE '.$where_clause.'
    179     AND FILE = \'picture\'
    180 ;';
    181   $pictures = mysql_fetch_array(pwg_query( $query ));
    182  
    183   // Number of different visitors
    184   $query = '
    185 SELECT COUNT(*) as p, login
    186   FROM '.HISTORY_TABLE.'
    187   WHERE '.$where_clause.'
    188   GROUP BY login, IP
    189 ;';
    190   $user_results = pwg_query( $query );
    191   $nb_visitors = 0;
    192   $auth_users = array();
    193   while ( $user_array = mysql_fetch_array( $user_results ) )
    194   {
    195     if ($user_array['login'] == 'guest')
    196           $nb_visitors += 1;
    197         else
    198           array_push($auth_users, $user_array['login']);
    199   }
    200   $nb_visitors +=count(array_unique($auth_users));
    201   $class = ($i % 2)? 'row1':'row2'; $i++;
    202  
    203   $template->assign_block_vars('statrow',array(
    204       'VALUE'=>$value,
    205         'PAGES'=>$row['p'],
    206         'VISITORS'=>$nb_visitors,
    207         'IMAGES'=>$pictures['p'],
    208        
    209         'T_CLASS'=>$class
    210     ));
    211 }
    212 $nb_visitors = mysql_num_rows( $result );
    213 $days = array();
    214 $max_nb_visitors = 0;
    215 $max_pages_seen = 0;
    216 
    217 //----------------------------------------------------------- stats / jour
    218 
    219 if ( isset( $_GET['month'] ) && isset( $_GET['month'] ) && isset( $_GET['day'] ) )
    220 {  if ($_GET['day'] <10) {$current_day='0';
    221         $current_day.= $_GET['day'];}
    222   else {$current_day = $_GET['day'];}
    223   if ($_GET['month'] <10) {$current_month='0';
    224         $current_month.= $_GET['month'];}
    225   else {$current_month = $_GET['month'];}
    226   $current_year = $_GET['year'];
    227 }
    228 
    229 else
    230 {  $current_date = GetDate();
    231   if ($current_date['mday'] <10) {$current_day='0';
    232         $current_day.= $current_date['mday'];}
    233   else {$current_day = $current_date['mday'];}
    234   if ($current_date['mon'] <10) {$current_month='0';
    235         $current_month.= $current_date['mon'];}
    236   else {$current_month = $current_date['mon'];}
    237   $current_year = $current_date['year']; 
    238 }
    239 
    240 // Set WHERE clause
    241 $where = ' WHERE DATE_FORMAT(date,\'%Y-%m-%d\') = \''.$current_year."-".$current_month."-".$current_day.'\'';
    242  
    243 // Set LIMIT clause
    244 $limit = ' LIMIT ';
    245 $page['start'] = 0;
    246 if (isset($_GET['start']) and is_numeric($_GET['start'])) $page['start'] = abs($_GET['start']);
    247 $limit .= $page['start'];
    248 $limit .= ','.$conf['nb_logs_page'];
    249 
    250 $query = '
    251 SELECT DATE_FORMAT(date,\'%H:%i:%s\') AS hour,
    252      login,
    253      IP,
    254      category,
    255      file,
    256      picture
    257   FROM '.HISTORY_TABLE.
    258   $where.'
    259   ORDER BY date DESC'.
    260   $limit.
    261   ';';
    262 
    263 
    264 $result = pwg_query( $query );
    265 
    266 $i=0;
    267  
    268 while ( $row = mysql_fetch_array( $result ) )
    269 {
    270   $class = ($i % 2)? 'row1':'row2'; $i++;
    271     $template->assign_block_vars('detail',array(
    272     'HOUR'=>$row['hour'],
    273     'LOGIN'=>$row['login'],
    274     'IP'=>$row['IP'],
    275     'CATEGORY'=>$row['category'],
    276     'FILE'=>$row['file'],
    277     'PICTURE'=>$row['picture'],
    278     'T_CLASS'=>$class
    279   ));
    280   }
    281 
    282 
    283 // Get total number of logs
    284 $query = '
    285     SELECT COUNT(date) as nb_logs
    286      FROM '.HISTORY_TABLE.
    287   $where.'
    288     ;';
    289 
    290   $result = pwg_query($query);
    291   $row = mysql_fetch_array($result);
    292   $page['nb_logs']=$row['nb_logs'];
    293 
    294   //display nav bar
    295 $url = $_SERVER['PHP_SELF'].'?page=stats';
    296 $url.= isset($_GET['year']) ? '&amp;year='.$_GET['year'] : '';
    297 $url.= isset($_GET['month']) ? '&amp;month='.$_GET['month'] : '';
    298 $url.= isset($_GET['day']) ? '&amp;day='.$_GET['day'] : '';
    299 
    300 $page['navigation_bar'] =
    301 create_navigation_bar(
    302   $url,
    303   $page['nb_logs'],
    304   $page['start'],
    305   $conf['nb_logs_page']
    306   );
    307 
    308 $template->assign_block_vars(
    309   'navigation',
    310   array(
    311     'NAV_BAR' => $page['navigation_bar']
    312     )
    313   );
    314 
    315 //----------------------------------------------------------- sending html code
     458  else
     459  {
     460    // at least the year is defined
     461    $url =
     462      PHPWG_ROOT_PATH.'admin.php'
     463      .'?page=stats'
     464      .'&amp;year='.$line['year']
     465      ;
     466   
     467    $value = '<a href="'.$url.'">';
     468    $value.= $line['year'];
     469    $value.= "</a>";   
     470  }
     471 
     472  $template->assign_block_vars(
     473    'statrow',
     474    array(
     475      'VALUE' => $value,
     476      'PAGES' => $line['nb_pages'],
     477     
     478      'T_CLASS' => ($i++ % 2) ? 'row1' : 'row2'
     479      )
     480    );
     481}
     482
     483// +-----------------------------------------------------------------------+
     484// | Sending html code                                                     |
     485// +-----------------------------------------------------------------------+
     486
    316487$template->assign_var_from_handle('ADMIN_CONTENT', 'stats');
    317488?>

  • trunk/identification.php

    r1652 r1727  
    6464    }
    6565    log_user($row['id'], $remember_me);
    66     pwg_log_login( $username );
    6766    redirect(empty($redirect_to) ? make_index_url() : $redirect_to);
    6867  }

  • trunk/include/constants.php

    r1726 r1727  
    5353define('GROUPS_TABLE', $prefixeTable.'groups');
    5454define('HISTORY_TABLE', $prefixeTable.'history');
     55define('HISTORY_SUMMARY_TABLE', $prefixeTable.'history_summary');
    5556define('IMAGE_CATEGORY_TABLE', $prefixeTable.'image_category');
    5657define('IMAGES_TABLE', $prefixeTable.'images');

  • trunk/include/functions.inc.php

    r1722 r1727  
    412412}
    413413
    414 function pwg_log( $file, $category, $picture = '' )
    415 {
    416   global $conf, $user;
    417 
    418   if ( is_admin() )
    419   {
    420     $doit=$conf['history_admin'];
    421   }
    422   elseif ( $user['is_the_guest'] )
    423   {
    424     $doit=$conf['history_guest'];
    425   }
    426   else
    427   {
    428     $doit = $conf['log'];
    429   }
    430 
    431   if ($doit)
    432   {
    433     $login = ($user['id'] == $conf['guest_id'])
    434       ? 'guest' : addslashes($user['username']);
    435     insert_into_history($login, $file, $category, $picture);
    436   }
    437 }
    438 
    439 function pwg_log_login( $username )
    440 {
    441   global $conf;
    442   if ( $conf['login_history'] )
    443   {
    444     insert_into_history($username, 'login', '', '');
    445   }
    446 }
    447 
    448 // inserts a row in the history table
    449 function insert_into_history( $login, $file, $category, $picture)
    450 {
     414function pwg_log($image_id = null)
     415{
     416  global $conf, $user, $page;
     417
     418  if (!$conf['log'])
     419  {
     420    return false;
     421  }
     422
     423  if (is_admin() and !$conf['history_admin'])
     424  {
     425    return false;
     426  }
     427
     428  if ($user['is_the_guest'] and !$conf['history_guest'])
     429  {
     430    return false;
     431  }
     432
     433  $tags_string = null;
     434  if (isset($page['section']) and $page['section'] == 'tags')
     435  {
     436    $tag_ids = array();
     437    foreach ($page['tags'] as $tag)
     438    {
     439      array_push($tag_ids, $tag['id']);
     440    }
     441
     442    $tags_string = implode(',', $tag_ids);
     443  }
     444
     445  // here we ask the database the current date and time, and we extract
     446  // {year, month, day} from the current date. We could do this during the
     447  // insert query with a CURDATE(), CURTIME(), DATE_FORMAT(CURDATE(), '%Y')
     448  // ... but I (plg) think it would cost more than a double query and a PHP
     449  // extraction.
     450  $query = '
     451SELECT CURDATE(), CURTIME()
     452;';
     453  list($curdate, $curtime) = mysql_fetch_row(pwg_query($query));
     454
     455  list($curyear, $curmonth, $curday) = explode('-', $curdate);
     456  list($curhour) = explode(':', $curtime);
     457 
    451458  $query = '
    452459INSERT INTO '.HISTORY_TABLE.'
    453   (date,login,IP,file,category,picture)
     460  (
     461    date,
     462    time,
     463    year,
     464    month,
     465    day,
     466    hour,
     467    user_id,
     468    IP,
     469    section,
     470    category_id,
     471    image_id,
     472    tag_ids
     473  )
    454474  VALUES
    455   (NOW(),
    456   \''.$login.'\',
    457   \''.$_SERVER['REMOTE_ADDR'].'\',
    458   \''.addslashes($file).'\',
    459   \''.addslashes(strip_tags($category)).'\',
    460   \''.addslashes($picture).'\')
     475  (
     476    \''.$curdate.'\',
     477    \''.$curtime.'\',
     478    '.$curyear.',
     479    '.$curmonth.',
     480    '.$curday.',
     481    '.$curhour.',
     482    '.$user['id'].',
     483    \''.$_SERVER['REMOTE_ADDR'].'\',
     484    '.(isset($page['section']) ? "'".$page['section']."'" : 'NULL').',
     485    '.(isset($page['category']) ? $page['category'] : 'NULL').',
     486    '.(isset($image_id) ? $image_id : 'NULL').',
     487    '.(isset($tags_string) ? "'".$tags_string."'" : 'NULL').'
     488  )
    461489;';
    462490  pwg_query($query);
     491
     492  return true;
    463493}
    464494
    … …  
    841871
    842872  $template->assign_block_vars(
    843     $blockname, array('SELECTED' => '', 'VALUE' => 0, 'OPTION' => '--'));
     873    $blockname,
     874    array(
     875      'SELECTED' => '',
     876      'VALUE' => 0,
     877      'OPTION' => '--'
     878      )
     879    );
    844880
    845881  for ($i = 1; $i <= 31; $i++)
    … …  
    851887    }
    852888    $template->assign_block_vars(
    853       $blockname, array('SELECTED' => $selected,
    854                         'VALUE' => $i,
    855                         'OPTION' => str_pad($i, 2, '0', STR_PAD_LEFT)));
     889      $blockname,
     890      array(
     891        'SELECTED' => $selected,
     892        'VALUE' => $i,
     893        'OPTION' => str_pad($i, 2, '0', STR_PAD_LEFT)
     894        )
     895      );
    856896  }
    857897}
    … …  
    868908
    869909  $template->assign_block_vars(
    870     $blockname, array('SELECTED' => '',
    871                       'VALUE' => 0,
    872                       'OPTION' => '------------'));
     910    $blockname,
     911    array(
     912      'SELECTED' => '',
     913      'VALUE' => 0,
     914      'OPTION' => '------------')
     915    );
    873916
    874917  for ($i = 1; $i <= 12; $i++)
    … …  
    880923    }
    881924    $template->assign_block_vars(
    882       $blockname, array('SELECTED' => $selected,
    883                         'VALUE' => $i,
    884                         'OPTION' => $lang['month'][$i]));
     925      $blockname,
     926      array(
     927        'SELECTED' => $selected,
     928        'VALUE' => $i,
     929        'OPTION' => $lang['month'][$i])
     930      );
    885931  }
    886932}
    … …  
    10781124
    10791125/**
     1126 * Prepends and appends a string at each value of the given array.
     1127 *
     1128 * @param array
     1129 * @param string prefix to each array values
     1130 * @param string suffix to each array values
     1131 */
     1132function prepend_append_array_items($array, $prepend_str, $append_str)
     1133{
     1134  array_walk(
     1135    $array,
     1136    create_function('&$s', '$s = "'.$prepend_str.'".$s."'.$append_str.'";')
     1137    );
     1138
     1139  return $array;
     1140}
     1141
     1142/**
     1143 * creates an hashed based on a query, this function is a very common
     1144 * pattern used here. Among the selected columns fetched, choose one to be
     1145 * the key, another one to be the value.
     1146 *
     1147 * @param string $query
     1148 * @param string $keyname
     1149 * @param string $valuename
     1150 * @return array
     1151 */
     1152function simple_hash_from_query($query, $keyname, $valuename)
     1153{
     1154  $array = array();
     1155
     1156  $result = pwg_query($query);
     1157  while ($row = mysql_fetch_array($result))
     1158  {
     1159    $array[ $row[$keyname] ] = $row[$valuename];
     1160  }
     1161
     1162  return $array;
     1163}
     1164
     1165/**
    10801166 * Return basename of the current script
    10811167 * Lower case convertion is applied on return value

  • trunk/include/functions_search.inc.php

    r1677 r1727  
    2626// +-----------------------------------------------------------------------+
    2727
    28 
    29 /**
    30  * Prepends and appends a string at each value of the given array.
    31  *
    32  * @param array
    33  * @param string prefix to each array values
    34  * @param string suffix to each array values
    35  */
    36 function prepend_append_array_items($array, $prepend_str, $append_str)
    37 {
    38   array_walk(
    39     $array,
    40     create_function('&$s', '$s = "'.$prepend_str.'".$s."'.$append_str.'";')
    41     );
    42 
    43   return $array;
    44 }
    4528
    4629/**

  • trunk/index.php

    r1722 r1727  
    302302}
    303303//------------------------------------------------------------ log informations
    304 pwg_log('category', $page['title']);
     304pwg_log();
    305305
    306306include(PHPWG_ROOT_PATH.'include/page_header.php');

  • trunk/install/phpwebgallery_structure.sql

    r1662 r1727  
    1 1-- MySQL dump 9.11
     1-- MySQL dump 9.11
    22--
    33-- Host: localhost    Database: pwg-bsf
    … …  
    113113DROP TABLE IF EXISTS `phpwebgallery_history`;
    114114CREATE TABLE `phpwebgallery_history` (
    115   `date` datetime NOT NULL default '0000-00-00 00:00:00',
    116   `login` varchar(15) default NULL,
    117   `IP` varchar(50) NOT NULL default '',
    118   `category` varchar(150) default NULL,
    119   `file` varchar(50) default NULL,
    120   `picture` varchar(150) default NULL,
    121   KEY `history_i1` (`date`)
     115  `id` int(10) unsigned NOT NULL auto_increment,
     116  `date` date NOT NULL default '0000-00-00',
     117  `time` time NOT NULL default '00:00:00',
     118  `year` smallint(4) NOT NULL default '0',
     119  `month` tinyint(2) NOT NULL default '0',
     120  `day` tinyint(2) NOT NULL default '0',
     121  `hour` tinyint(2) NOT NULL default '0',
     122  `user_id` smallint(5) NOT NULL default '0',
     123  `IP` varchar(15) NOT NULL default '',
     124  `section` enum('categories','tags','search','list','favorites','most_visited','best_rated','recent_pics','recent_cats') default NULL,
     125  `category_id` smallint(5) default NULL,
     126  `tag_ids` varchar(50) default NULL,
     127  `image_id` mediumint(8) default NULL,
     128  `summarized` enum('true','false') default 'false',
     129  PRIMARY KEY  (`id`),
     130  KEY `history_i1` (`summarized`)
     131) TYPE=MyISAM;
     132
     133--
     134-- Table structure for table `phpwebgallery_history_summary`
     135--
     136
     137DROP TABLE IF EXISTS `phpwebgallery_history_summary`;
     138CREATE TABLE `phpwebgallery_history_summary` (
     139  `id` varchar(13) NOT NULL default '',
     140  `year` smallint(4) NOT NULL default '0',
     141  `month` tinyint(2) default NULL,
     142  `day` tinyint(2) default NULL,
     143  `hour` tinyint(2) default NULL,
     144  `nb_pages` int(11) default NULL,
     145  PRIMARY KEY  (`id`)
    122146) TYPE=MyISAM;
    123147
    … …  
    299323  `cat_id` smallint(5) unsigned NOT NULL default '0',
    300324  `max_date_last` datetime default NULL,
    301   `count_images` mediumint(8) unsigned default 0,
    302   `count_categories` mediumint(8) unsigned default 0,
    303   PRIMARY KEY  (`user_id`, `cat_id`)
     325  `count_images` mediumint(8) unsigned default '0',
     326  `count_categories` mediumint(8) unsigned default '0',
     327  PRIMARY KEY  (`user_id`,`cat_id`)
    304328) TYPE=MyISAM;
    305329
    … …  
    396420) TYPE=MyISAM;
    397421
    398 -- 
     422--
    399423-- Table structure for table `phpwebgallery_ws_access`
    400 -- 
    401 
    402 DROP TABLE IF EXISTS phpwebgallery_ws_access;
    403 CREATE TABLE phpwebgallery_ws_access (
    404   id smallint(5) unsigned NOT NULL auto_increment,
    405   name varchar(32) NOT NULL default '',
    406   access varchar(255) default NULL,
     424--
     425
     426DROP TABLE IF EXISTS `phpwebgallery_ws_access`;
     427CREATE TABLE `phpwebgallery_ws_access` (
     428  `id` smallint(5) unsigned NOT NULL auto_increment,
     429  `name` varchar(32) NOT NULL default '',
     430  `access` varchar(255) default NULL,
    407431  `start` datetime default NULL,
    408432  `end` datetime default NULL,
    409   request varchar(255) default NULL,
    410   high enum('true','false') NOT NULL default 'true',
    411   normal enum('true','false') NOT NULL default 'true',
     433  `request` varchar(255) default NULL,
     434  `high` enum('true','false') NOT NULL default 'true',
     435  `normal` enum('true','false') NOT NULL default 'true',
    412436  `limit` smallint(5) unsigned default NULL,
    413437  `comment` varchar(255) default NULL,
    414   PRIMARY KEY  (id),
    415   UNIQUE KEY name (name)
    416 ) ENGINE=MyISAM COMMENT='Access for Web Services';
    417        
     438  PRIMARY KEY  (`id`),
     439  UNIQUE KEY `name` (`name`)
     440) TYPE=MyISAM COMMENT='Access for Web Services';
     441

  • trunk/picture.php

    r1711 r1727  
    790790}
    791791//------------------------------------------------------------ log informations
    792 pwg_log('picture', $page['title'], $picture['current']['file']);
     792pwg_log($picture['current']['id']);
    793793
    794794include(PHPWG_ROOT_PATH.'include/page_header.php');

  • trunk/search.php

    r1677 r1727  
    235235}
    236236//------------------------------------------------------------ log informations
    237 pwg_log( 'search', $title );
    238237include(PHPWG_ROOT_PATH.'include/page_header.php');
    239238$template->parse('search');

  • trunk/template/yoga/admin.tpl

    r1672 r1727  
    1616        <li><a href="{U_FAQ}">{lang:instructions}</a></li>
    1717        <li><a href="{U_SITE_MANAGER}">{lang:Site manager}</a></li>
    18         <li><a href="{U_HISTORY}">{lang:history}</a></li>
     18
     19        <li>
     20          {lang:history}
     21          <ul>
     22            <li><a href="{U_HISTORY_STAT}">{lang:Statistics}</a></li>
     23            <li><a href="{U_HISTORY_SEARCH}">{lang:Search}</a></li>
     24          </ul>
     25        </li>
     26
    1927        <li><a href="{U_CAT_UPDATE}">{lang:update}</a></li>
    2028      </ul>

  • trunk/template/yoga/admin/element_set_global.tpl

    r1518 r1727  
    160160    <li><label><input type="radio" name="caddie_action" value="add_selected" /> {lang:Add selected elements to caddie}</label></li>
    161161    <!-- END not_in_caddie -->
     162
     163    <li><label><input type="radio" name="caddie_action" value="export" /> {lang:Export data}</label></li>
    162164 
    163165  </ul>

  • trunk/template/yoga/admin/stats.tpl

    r1339 r1727  
    33
    44<h3>{L_STAT_TITLE}</h3>
    5 <img class="image" src="{IMG_REPORT}" alt="{L_STAT_MONTHLY_ALT}" />
    65
    7 <h3>{L_STAT_DETAIL_TITLE}</h3>
     6<img class="image" src="{SRC_REPORT}" alt="{lang:history chart}" />
     7
    88<table class="table2" id="dailyStats">
    99  <tr class="throw">
    10     <th>{L_VALUE}</th>
    11     <th>{L_PAGES_SEEN}</th>
    12     <th>{L_VISITORS}</th>
    13     <th>{L_PICTURES}</th>
     10    <th>{PERIOD_LABEL}</th>
     11    <th>{lang:Pages seen}</th>
    1412  </tr>
    1513<!-- BEGIN statrow -->
    … …  
    1715    <td>{statrow.VALUE}</td>
    1816    <td class="number">{statrow.PAGES}</td>
    19     <td class="number">{statrow.VISITORS}</td>
    20     <td class="number">{statrow.IMAGES}</td>
    2117  </tr>
    2218<!-- END statrow -->
    2319</table>
    24 
    25 <h3>{L_DATE_TITLE}</h3>
    26 <table class="table2" id="detailedStats">
    27   <tr class="throw">
    28     <th>{L_STAT_HOUR}</th>
    29     <th>{L_STAT_LOGIN}</th>
    30     <th>{L_STAT_ADDR}</th>
    31     <th>{L_STAT_CATEGORY}</th>
    32     <th>{L_STAT_FILE}</th>
    33     <th>{L_STAT_PICTURE}</th>
    34   </tr>
    35 <!-- BEGIN detail -->
    36   <tr class="{detail.T_CLASS}">
    37     <td class="hour">{detail.HOUR}</td>
    38     <td>{detail.LOGIN}</td>
    39     <td>{detail.IP}</td>
    40     <td>{detail.CATEGORY}</td>
    41     <td>{detail.FILE}</td>
    42     <td>{detail.PICTURE}</td>
    43   </tr>
    44 <!-- END detail -->
    45 </table>
    46 
    47 <!-- BEGIN navigation -->
    48 <div class="admin">
    49 {navigation.NAV_BAR}
    50 </div>
    51 <!-- END navigation -->
Note: See TracChangeset for help on using the changeset viewer.