b2evolution PHP Cross Reference Blogging Systems

Source: /inc/users/views/_user_stats.view.php - 353 lines - 11455 bytes - Summary - Text - Print

Description: This file implements the UI view for the users statistics. This file is part of the evoCore framework - {@link http://evocore.net/} See also {@link http://sourceforge.net/projects/evocms/}.

   1  <?php
   2  /**

   3   * This file implements the UI view for the users statistics.

   4   *

   5   * This file is part of the evoCore framework - {@link http://evocore.net/}

   6   * See also {@link http://sourceforge.net/projects/evocms/}.

   7   *

   8   * @copyright (c)2003-2014 by Francois Planque - {@link http://fplanque.com/}

   9   *

  10   * {@internal License choice

  11   * - If you have received this file as part of a package, please find the license.txt file in

  12   *   the same folder or the closest folder above for complete license terms.

  13   * - If you have received this file individually (e-g: from http://evocms.cvs.sourceforge.net/)

  14   *   then you must choose one of the following licenses before using the file:

  15   *   - GNU General Public License 2 (GPL) - http://www.opensource.org/licenses/gpl-license.php

  16   *   - Mozilla Public License 1.1 (MPL) - http://www.opensource.org/licenses/mozilla1.1.php

  17   * }}

  18   *

  19   * {@internal Open Source relicensing agreement:

  20   * }}

  21   *

  22   * @package admin

  23   *

  24   * {@internal Below is a list of authors who have contributed to design/coding of this file: }}

  25   * @author fplanque: Francois PLANQUE

  26   *

  27   * @version $Id: _user_stats.view.php 879 2012-02-22 13:20:54Z yura $

  28   */
  29  if( !defined('EVO_MAIN_INIT') ) die( 'Please, do not access this page directly.' );
  30  
  31  global $dispatcher;
  32  
  33  /*** Email statistics ***/

  34  
  35  $SQL = new SQL();
  36  $SQL->SELECT( 'dom_name,
  37      COUNT( IF( user_status = \'new\', 1, NULL ) ) AS cnt_new,
  38      COUNT( IF( user_status = \'activated\', 1, NULL ) ) AS cnt_activated,
  39      COUNT( IF( user_status = \'autoactivated\', 1, NULL ) ) AS cnt_autoactivated,
  40      COUNT( IF( user_status = \'emailchanged\', 1, NULL ) ) AS cnt_emailchanged,
  41      COUNT( IF( user_status = \'deactivated\', 1, NULL ) ) AS cnt_deactivated,
  42      COUNT( IF( user_status = \'failedactivation\', 1, NULL ) ) AS cnt_failedactivation,
  43      COUNT( IF( user_status = \'closed\', 1, NULL ) ) AS cnt_closed,
  44      ( COUNT( IF( user_status IN( \'activated\', \'autoactivated\' ), 1, NULL ) ) / COUNT( * ) ) AS percent' );
  45  $SQL->FROM( 'T_basedomains' );
  46  $SQL->FROM_add( 'LEFT JOIN T_users ON user_email_dom_ID = dom_ID' );
  47  $SQL->WHERE( 'dom_type = \'email\'' );
  48  $SQL->GROUP_BY( 'dom_ID' );
  49  
  50  $count_SQL = new SQL();
  51  $count_SQL->SELECT( 'COUNT( dom_ID )' );
  52  $count_SQL->FROM( 'T_basedomains' );
  53  $count_SQL->WHERE( 'dom_type = \'email\'' );
  54  
  55  // Create result set:

  56  $Results = new Results( $SQL->get(), 'dom_', '--D', NULL, $count_SQL->get() );
  57  
  58  $Results->title = T_('Email statistics');
  59  
  60  $Results->cols[] = array(
  61          'th' => T_('Email domain'),
  62          'td' => '$dom_name$',
  63          'order' => 'dom_name',
  64          'th_class' => 'shrinkwrap',
  65          'td_class' => 'shrinkwrap',
  66      );
  67  
  68  $Results->cols[] = array(
  69          'th' => T_('# New'),
  70          'td' => '$cnt_new$',
  71          'order' => 'cnt_new',
  72          'default_dir' => 'D',
  73      );
  74  
  75  $Results->cols[] = array(
  76          'th' => T_('# Activated'),
  77          'td' => '$cnt_activated$',
  78          'order' => 'cnt_activated',
  79          'default_dir' => 'D',
  80      );
  81  
  82  $Results->cols[] = array(
  83          'th' => T_('# Autoactivated'),
  84          'td' => '$cnt_autoactivated$',
  85          'order' => 'cnt_autoactivated',
  86          'default_dir' => 'D',
  87      );
  88  
  89  $Results->cols[] = array(
  90          'th' => T_('# Email changed'),
  91          'td' => '$cnt_emailchanged$',
  92          'order' => 'cnt_emailchanged',
  93          'default_dir' => 'D',
  94      );
  95  
  96  $Results->cols[] = array(
  97          'th' => T_('# Deactivated'),
  98          'td' => '$cnt_deactivated$',
  99          'order' => 'cnt_deactivated',
 100          'default_dir' => 'D',
 101      );
 102  
 103  $Results->cols[] = array(
 104          'th' => T_('# Failed Activation'),
 105          'td' => '$cnt_failedactivation$',
 106          'order' => 'cnt_failedactivation',
 107          'default_dir' => 'D',
 108      );
 109  
 110  $Results->cols[] = array(
 111          'th' => T_('# Closed'),
 112          'td' => '$cnt_closed$',
 113          'order' => 'cnt_closed',
 114          'default_dir' => 'D',
 115      );
 116  
 117  /**

 118   * Format percent value

 119   *

 120   * @param float Percent

 121   * @return string Formatted percent

 122   */
 123  function stats_active_percent( $percent )
 124  {
 125      return floor( $percent * 100 ).' %';
 126  }
 127  $Results->cols[] = array(
 128          'th' => /* xgettext:no-php-format */ T_('% of active users'),
 129          'td' => '%stats_active_percent( #percent# )%',
 130          'order' => 'percent',
 131          'default_dir' => 'D',
 132      );
 133  
 134  $Results->display( array(
 135          'page_url' => $dispatcher.'?ctrl=users&amp;tab=stats'
 136      ) );
 137  
 138  
 139  /*** Gender statistics ***/

 140  
 141  // Get total values

 142  $SQL = new SQL();
 143  $SQL->SELECT( 'COUNT( * )' );
 144  $SQL->FROM( 'T_users' );
 145  // Active users

 146  $SQL->WHERE( 'user_status IN( \'activated\', \'autoactivated\' )' );
 147  $total_cnt_active = $DB->get_var( $SQL->get() );
 148  
 149  // Not active users

 150  $SQL->WHERE( 'user_status NOT IN( \'activated\', \'autoactivated\' )' );
 151  $total_cnt_notactive = $DB->get_var( $SQL->get() );
 152  
 153  // Users with pictures

 154  $SQL->WHERE( 'user_avatar_file_ID IS NOT NULL' );
 155  $total_cnt_pictured = $DB->get_var( $SQL->get() );
 156  
 157  // Get all records

 158  $SQL = new SQL();
 159  $SQL->SELECT( 'IF( user_gender IN ( "M", "F" ), user_gender, "A" ) AS gender_sign,
 160      COUNT( IF( user_status IN( \'activated\', \'autoactivated\' ), 1, NULL ) ) AS cnt_active,
 161      COUNT( IF( user_status IN( \'activated\', \'autoactivated\' ), NULL, 1 ) ) AS cnt_notactive,
 162      COUNT( IF( user_avatar_file_ID IS NOT NULL, 1, NULL ) ) AS cnt_pictured' );
 163  $SQL->FROM( 'T_users' );
 164  $SQL->GROUP_BY( 'gender_sign' );
 165  
 166  // Create result set:

 167  $Results = new Results( $SQL->get(), 'gender_', 'D', NULL, 3 );
 168  
 169  $Results->title = T_('Gender statistics');
 170  
 171  /**

 172   * Get formatted data with current value and percentage

 173   *

 174   * @param integer Cell value

 175   * @param integer Total of the column

 176   * @return string Value (Percent)

 177   */
 178  function stats_gender_value( $value, $total )
 179  {
 180      $percent = $total == 0 ? 0 : floor( $value / $total * 100 );
 181  
 182      return $value.' ('.$percent.'%)';
 183  }
 184  
 185  /**

 186   * Convert gender value from DB short name to full readable name

 187   *

 188   * @param string Gender value

 189   * @return string Gender name

 190   */
 191  function stats_gender_name( $gender )
 192  {
 193      switch( $gender )
 194      {
 195          case 'F':
 196              return T_('Women');
 197              break;
 198          case 'M':
 199              return T_('Men');
 200              break;
 201          default:
 202              return T_('Unknown');
 203              break;
 204      }
 205  }
 206  $Results->cols[] = array(
 207          'th' => T_('Gender'),
 208          'td' => '%stats_gender_name( #gender_sign# )%',
 209          'order' => 'gender_sign',
 210          'th_class' => 'shrinkwrap',
 211          'td_class' => 'shrinkwrap',
 212          'total' => '<strong>'.T_('Total').'</strong>',
 213      );
 214  
 215  $Results->cols[] = array(
 216          'th' => T_('# Active'),
 217          'td' => '%stats_gender_value( #cnt_active#, '.$total_cnt_active.' )%',
 218          'order' => 'cnt_active',
 219          'default_dir' => 'D',
 220          'total' => $total_cnt_active,
 221      );
 222  
 223  $Results->cols[] = array(
 224          'th' => T_('# Not active'),
 225          'td' => '%stats_gender_value( #cnt_notactive#, '.$total_cnt_notactive.' )%',
 226          'order' => 'cnt_notactive',
 227          'default_dir' => 'D',
 228          'total' => $total_cnt_notactive,
 229      );
 230  
 231  $Results->cols[] = array(
 232          'th' => T_('# With profile picture'),
 233          'td' => '%stats_gender_value( #cnt_pictured#, '.$total_cnt_pictured.' )%',
 234          'order' => 'cnt_pictured',
 235          'default_dir' => 'D',
 236          'total' => $total_cnt_pictured,
 237      );
 238  
 239  echo '<br />';
 240  $Results->display( array(
 241          'page_url' => $dispatcher.'?ctrl=users&amp;tab=stats'
 242      ) );
 243  
 244  
 245  /*** Graph of registrations per day ***/

 246  
 247  echo '<h2>'.T_('# registrations per day').'</h2>';
 248  
 249  global $AdminUI, $user_gender_color;
 250  
 251  $SQL = new SQL();
 252  $SQL->SELECT( 'SQL_NO_CACHE COUNT(*) AS users,
 253          CONCAT( IF( user_gender IN ( "M", "F" ), user_gender, "G" ), "_", IF( user_status IN ( "activated", "autoactivated" ), "active", "notactive" ) ) AS user_gender_status,
 254          EXTRACT(YEAR FROM user_created_datetime) AS year,
 255          EXTRACT(MONTH FROM user_created_datetime) AS month,
 256          EXTRACT(DAY FROM user_created_datetime) AS day' );
 257  $SQL->FROM( 'T_users' );
 258  $SQL->WHERE( 'user_created_datetime >= '.$DB->quote( date( 'Y-m-d H:i:s', mktime( date( 'H' ), date( 'i' ), date( 's' ), date( 'm' ) - 1, date( 'd' ), date( 'Y' ) ) ) ) );
 259  $SQL->GROUP_BY( 'year, month, day, user_gender_status' );
 260  $SQL->ORDER_BY( 'year DESC, month DESC, day DESC, user_gender_status' );
 261  $res_users = $DB->get_results( $SQL->get(), ARRAY_A, 'Get user summary' );
 262  
 263  /*

 264   * Chart

 265   */
 266  if( count( $res_users ) )
 267  {
 268      $last_date = 0;
 269  
 270  /*

 271  ONE COLOR for user_gender = F AND status IN ( activated, autoactivated )

 272  ONE COLOR for user_gender = F AND status NOT IN ( activated, autoactivated )

 273  ONE COLOR for user_gender = M AND status IN ( activated, autoactivated )

 274  ONE COLOR for user_gender = M AND status NOT IN ( activated, autoactivated )

 275  ONE COLOR for user_gender = NULL AND status IN ( activated, autoactivated )

 276  ONE COLOR for user_gender = NULL AND status NOT IN ( activated, autoactivated )

 277  */
 278      $col_mapping = array(
 279              'F_active'    => 1,
 280              'F_notactive' => 2,
 281              'M_active'    => 3,
 282              'M_notactive' => 4,
 283              'G_active'    => 5,
 284              'G_notactive' => 6,
 285          );
 286  
 287      $chart[ 'chart_data' ][ 0 ] = array();
 288      $chart[ 'chart_data' ][ 1 ] = array();
 289      $chart[ 'chart_data' ][ 2 ] = array();
 290      $chart[ 'chart_data' ][ 3 ] = array();
 291      $chart[ 'chart_data' ][ 4 ] = array();
 292      $chart[ 'chart_data' ][ 5 ] = array();
 293      $chart[ 'chart_data' ][ 6 ] = array();
 294  
 295      $count = 0;
 296      foreach( $res_users as $row_stats )
 297      {
 298          $this_date = mktime( 0, 0, 0, $row_stats['month'], $row_stats['day'], $row_stats['year'] );
 299          if( $last_date != $this_date )
 300          { // We just hit a new day, let's display the previous one:
 301                  $last_date = $this_date;    // that'll be the next one

 302                  $count ++;
 303                  array_unshift( $chart[ 'chart_data' ][ 0 ], date( locale_datefmt(), $last_date ) );
 304                  array_unshift( $chart[ 'chart_data' ][ 1 ], 0 );
 305                  array_unshift( $chart[ 'chart_data' ][ 2 ], 0 );
 306                  array_unshift( $chart[ 'chart_data' ][ 3 ], 0 );
 307                  array_unshift( $chart[ 'chart_data' ][ 4 ], 0 );
 308                  array_unshift( $chart[ 'chart_data' ][ 5 ], 0 );
 309                  array_unshift( $chart[ 'chart_data' ][ 6 ], 0 );
 310          }
 311          if( ! empty ( $col_mapping[ $row_stats['user_gender_status'] ] ) )
 312          {    // those users are calculated here
 313              $col = $col_mapping[ $row_stats['user_gender_status'] ];
 314              $chart['chart_data'][$col][0] = $row_stats['users'];
 315          }
 316      }
 317  
 318  /*

 319  ONE COLOR for user_gender = F AND status IN ( activated, autoactivated )

 320  ONE COLOR for user_gender = F AND status NOT IN ( activated, autoactivated )

 321  ONE COLOR for user_gender = M AND status IN ( activated, autoactivated )

 322  ONE COLOR for user_gender = M AND status NOT IN ( activated, autoactivated )

 323  ONE COLOR for user_gender = NULL AND status IN ( activated, autoactivated )

 324  ONE COLOR for user_gender = NULL AND status NOT IN ( activated, autoactivated )

 325  */
 326      array_unshift( $chart[ 'chart_data' ][ 0 ], '' );
 327      array_unshift( $chart[ 'chart_data' ][ 1 ], 'Women (Active)' );
 328      array_unshift( $chart[ 'chart_data' ][ 2 ], 'Women (Inactive)' );
 329      array_unshift( $chart[ 'chart_data' ][ 3 ], 'Men (Active)' );
 330      array_unshift( $chart[ 'chart_data' ][ 4 ], 'Men (Inactive)' );
 331      array_unshift( $chart[ 'chart_data' ][ 5 ], 'Unknown (Active)' );
 332      array_unshift( $chart[ 'chart_data' ][ 6 ], 'Unknown (Inactive)' );
 333  
 334      // Include common chart properties:

 335      require dirname(__FILE__).'/inc/_bar_chart.inc.php';
 336  
 337      $chart[ 'series_color' ] = array (
 338              $user_gender_color['women_active'],
 339              $user_gender_color['women_notactive'],
 340              $user_gender_color['men_active'],
 341              $user_gender_color['men_notactive'],
 342              $user_gender_color['nogender_active'],
 343              $user_gender_color['nogender_notactive'],
 344          );
 345  
 346  
 347      echo '<div class="center">';
 348      load_funcs('_ext/_swfcharts.php');
 349      DrawChart( $chart );
 350      echo '</div>';
 351  }
 352  
 353  ?>

title

Description

title

Description

title

Description

title

title

Body