source: extensions/UserStat/userstat_aip.class.inc.php @ 4580

Last change on this file since 4580 was 4270, checked in by grum, 15 years ago

[UserStat] Adding a directory for the plugin UserStat
  • Property svn:executable set to *
File size: 15.3 KB
Line 
1<?php
2/* -----------------------------------------------------------------------------
3  Plugin     : UserStat
4  Author     : Grum
5    email    : grum@piwigo.org
6    website  : http://photos.grum.fr
7
8    << May the Little SpaceFrog be with you ! >>
9  ------------------------------------------------------------------------------
10  See main.inc.php for release information
11
12  AI classe => manage integration in administration interface
13
14  --------------------------------------------------------------------------- */
15if (!defined('PHPWG_ROOT_PATH')) { die('Hacking attempt!'); }
16
17include_once('userstat_aim.class.inc.php');
18include_once(PHPWG_ROOT_PATH.'admin/include/tabsheet.class.php');
19include_once(PHPWG_PLUGINS_PATH.'grum_plugins_classes-2/ajax.class.inc.php');
20
21class UserStat_AIP extends UserStat_AIM
22{
23  protected $tabsheet;
24  protected $ajax;
25
26  function UserStat_AIP($prefixeTable, $filelocation)
27  {
28    parent::__construct($prefixeTable, $filelocation);
29
30    $this->load_config();
31    $this->init_events();
32
33    $this->tabsheet = new tabsheet();
34    $this->tabsheet->add('global_stats',
35                          l10n('us_tsGlobal'),
36                          $this->page_link.'&amp;fUserStat_tabsheet=global_stats');
37    $this->tabsheet->add('users_stats',
38                          l10n('us_tsUsers'),
39                          $this->page_link.'&amp;fUserStat_tabsheet=users_stats');
40
41    $this->ajax = new Ajax();
42  }
43
44  /*
45    initialize events call for the plugin
46  */
47  function init_events()
48  {
49    add_event_handler('loc_end_page_header', array(&$this->css, 'apply_CSS'));
50  }
51
52
53
54  /* ---------------------------------------------------------------------------
55  Public classe functions
56  --------------------------------------------------------------------------- */
57
58  /*
59    manage plugin integration into piwigo's admin interface
60  */
61  public function manage()
62  {
63    global $template;
64
65    $this->returnAjaxContent();
66
67    $template->set_filename('plugin_admin_content', dirname(__FILE__)."/admin/userstat_admin.tpl");
68
69    $this->initRequest();
70
71
72    if($_REQUEST['fUserStat_tabsheet']=='global_stats')
73    {
74      $this->displayGlobalStats();
75    }
76    elseif($_REQUEST['fUserStat_tabsheet']=='users_stats')
77    {
78      $this->displayUsersStats();
79    }
80
81    $this->tabsheet->select($_REQUEST['fUserStat_tabsheet']);
82    $this->tabsheet->assign();
83    $selected_tab=$this->tabsheet->get_selected();
84    $template->assign($this->tabsheet->get_titlename(), "[".$selected_tab['caption']."]");
85
86    $template_plugin["USERSTAT_VERSION"] = "<i>UserStat</i> ".l10n('us_version').USERSTAT_VERSION;
87    $template_plugin["USERSTAT_PAGE"] = $_REQUEST['fUserStat_tabsheet'];
88
89    $template->assign('plugin', $template_plugin);
90    $template->assign_var_from_handle('ADMIN_CONTENT', 'plugin_admin_content');
91  }
92
93  /* ---------------------------------------------------------------------------
94  Private classe functions
95  --------------------------------------------------------------------------- */
96
97  /*
98    return ajax content
99  */
100  protected function returnAjaxContent()
101  {
102    global $ajax, $template;
103
104    if(isset($_REQUEST['ajaxfct']))
105    {
106      //$this->debug("AJAXFCT:".$_REQUEST['ajaxfct']);
107      $result="<p class='errors'>An error has occured</p>";
108      switch($_REQUEST['ajaxfct'])
109      {
110        case 'userStat':
111          $result=$this->ajaxGetUserStat($_REQUEST['userId']);
112          break;
113      }
114      //$template->
115      $this->ajax->return_result($result);
116    }
117  }
118
119
120
121  private function initRequest()
122  {
123    //initialise $REQUEST values if not defined
124    if(!array_key_exists('fUserStat_tabsheet', $_REQUEST))
125    {
126      $_REQUEST['fUserStat_tabsheet']='global_stats';
127    }
128  }
129
130
131  private function displayGlobalStats()
132  {
133    global $template;
134
135    $template->set_filename('body_page', dirname(__FILE__)."/admin/userstat_global.tpl");
136
137    $template_datas = array();
138
139    $template_datas["propertiesUsers"] = $this->getNumberOfUsers();
140    $template_datas["languagesUsers"] = $this->getLanguagesOfUsers();
141    $template_datas["templatesUsers"] = $this->getTemplatesOfUsers();
142
143    $template->assign('datas', $template_datas);
144    $template->assign_var_from_handle('USERSTAT_BODY_PAGE', 'body_page');
145  }
146
147
148  private function displayUsersStats()
149  {
150    global $template;
151
152    $template->set_filename('body_page', dirname(__FILE__)."/admin/userstat_users.tpl");
153
154    $template_datas = array();
155
156    $template_datas["ajaxUrl"] = $this->page_link;
157    $template_datas["users"] = $this->getUsersGlobalStats();
158
159    $template->assign('datas', $template_datas);
160    $template->assign_var_from_handle('USERSTAT_BODY_PAGE', 'body_page');
161  }
162
163
164
165  private function makeUserStats($userId)
166  {
167    $returned=array();
168    $userStats=$this->getUserStats($userId);
169
170//$this->format_link($stats[$i]["CatName"], )." / ";
171
172    $catList = "";
173    foreach($userStats as $key => $val)
174    {
175      ($catList=="")?$catList=$val["upperCats"]:$catList.=",".$val["upperCats"];
176    }
177
178    $sql="
179SELECT name as categoryName,
180       id as categoryId,
181       '' AS nbRates,
182       '' AS maxRate,
183       '' AS minRate,
184       '' AS avgRate,
185       '' AS devRate,
186       '' AS nbDaysR,
187       '' AS lastDayR,
188       '' AS delayR,
189       '' AS nbComments,
190       '' AS nbDaysC,
191       '' AS lastDayC,
192       '' AS delayC,
193       IF(dir IS NULL, 'N', 'Y') AS physical,
194       global_rank,
195       uppercats
196FROM ".CATEGORIES_TABLE."
197WHERE id IN (".$catList.")
198ORDER BY global_rank;";
199
200    $result=pwg_query($sql);
201    if($result)
202    {
203      while($row=mysql_fetch_assoc($result))
204      {
205        $row['indent']=substr_count($row['global_rank'], '.');
206        if(array_key_exists($row['categoryId'], $userStats))
207        {
208          foreach($userStats[$row['categoryId']] as $key => $val)
209          {
210            $row[$key]=$val;
211          }
212        }
213        $row['categoryName']=$this->format_link($row['categoryName'], PHPWG_ROOT_PATH."index.php?/category/".$row['categoryId']);
214        if($row['indent']>0)
215        {
216          $row['categoryName']=str_repeat("&nbsp;", $row['indent']*5)."+ - - ".$row['categoryName'];
217        }
218        $returned[]=$row;
219      }
220    }
221
222    return($returned);
223  }
224
225
226  /*
227    format text : <a href="$link">$value</a>
228  */
229  private function format_link($value, $link)
230  {
231    return("<a href='$link'>$value</a>");
232  }
233
234
235  /* ---------------------------------------------------------------------------
236   * SQL Requests functions
237   * ------------------------------------------------------------------------- */
238
239  private function getUserName($userId)
240  {
241    $sql="SELECT username FROM ".USERS_TABLE." WHERE id = ".$userId;
242    $result=pwg_query($sql);
243    if($result)
244    {
245      $row=mysql_fetch_assoc($result);
246      return($row["username"]);
247    }
248    return("#".$userId);
249  }
250
251  /*
252   * returns an array
253   *   - "total" : total number of users
254   *   - "withmail" : total number with email
255   *   - "withoutMail" : total number without email
256  */
257  private function getNumberOfUsers()
258  {
259    $returned=Array(
260      "total" => 0,
261      "withMail" => 0,
262      "withoutMail" => 0);
263
264    $sql="SELECT 'total' AS property, count(id) AS counter
265          FROM ".USERS_TABLE."
266          GROUP BY property
267          UNION
268          SELECT IF(mail_address is null, 'withoutMail', 'withMail') AS property, COUNT(IF(mail_address is null, 'withoutMail', 'withMail')) AS counter
269          FROM ".USERS_TABLE."
270          GROUP BY property
271          UNION
272          SELECT IF(enabled_high != true, 'withoutHD', 'withHD') AS property, count(IF(enabled_high != true , 'withoutHD', 'withHD')) AS counter
273          FROM ".USER_INFOS_TABLE."
274          GROUP BY property
275          ";
276    $result=pwg_query($sql);
277    if($result)
278    {
279      while($row=mysql_fetch_row($result))
280      {
281        $returned[]=Array(
282          "property" => $row[0],
283          "value" => $row[1],
284          "label" => l10n("us_".$row[0])
285        );
286      }
287    }
288    return($returned);
289  }
290
291  /*
292   * returns an array of used languages
293   * each row is an array :
294   *  "nbUsers" : number of users using the language
295   *  "language" : language used
296  */
297  private function getLanguagesOfUsers()
298  {
299    $returned = array();
300    $list = get_languages();
301
302    $sql="SELECT count(user_id) as nbUsers, language FROM ".USER_INFOS_TABLE." GROUP BY language ORDER BY nbUsers DESC";
303    $result=pwg_query($sql);
304    if($result)
305    {
306      while($row=mysql_fetch_assoc($result))
307      {
308        $row["humanReadable"]=$list[$row["language"]];
309        $returned[]=$row;
310      }
311    }
312    return($returned);
313  }
314
315  /*
316   * returns an array of used templates/themes
317   * each row is an array :
318   *  "nbUsers"  : number of users using the language
319   *  "template" : template/theme used
320  */
321  private function getTemplatesOfUsers()
322  {
323    $returned = array();
324
325    $sql="SELECT count(user_id) as nbUsers, template FROM ".USER_INFOS_TABLE." GROUP BY template ORDER BY nbUsers DESC";
326    $result=pwg_query($sql);
327    if($result)
328    {
329      while($row=mysql_fetch_assoc($result))
330      {
331        $returned[]=$row;
332      }
333    }
334    return($returned);
335  }
336
337  /*
338   * returns an array of users stats properties (global stats)
339   * each row is an array :
340   *  "name"       : the user name
341   *  "id"         : the user id
342   *  "nbRates"    : number of rates made by the user
343   *  "maxRate"    : highest rate made by the user
344   *  "minRate"    : lowest rate made by the user
345   *  "avgRate"    : average rate made by th user
346   *  "devRate"    : deviation rate made by the user
347   *  "nbDaysR"    : number of days where the user made a rate
348   *  "lastDayR"   : last date when a user made a rate
349   *  "delayR"     : delay (in days) between the first and the last rate
350   *  "nbComments" : number of comment post by the user
351   *  "nbDaysC"    : number of days where the user post a comment
352   *  "lastDayC"   : last date when a user post a comment
353   *  "delayC"     : delay (in days) between the first and the last posted comment
354  */
355  private function getUsersGlobalStats()
356  {
357    $returned=array();
358
359    $sql="
360SELECT name,
361       MAX(id) AS id,
362       MAX(nbRates) AS nbRates,
363       MAX(maxRate) AS maxRate,
364       MAX(minRate) AS minRate,
365       MAX(avgRate) AS avgRate,
366       MAX(devRate) AS devRate,
367       MAX(lastDayR) AS lastDayR,
368       MAX(delayR) AS delayR,
369       MAX(nbComments) AS nbComments,
370       MAX(lastDayC) AS lastDayC,
371       MAX(delayC) AS delayC
372FROM (
373      SELECT  username AS name,
374              user_id AS id,
375              COUNT(element_id) AS nbRates,
376              MAX(rate) AS maxRate,
377              MIN(rate) AS minRate,
378              ROUND(AVG(rate), 2) AS avgRate,
379              ROUND(STDDEV(rate), 2) AS devRate,
380              MAX(rt.date) AS lastDayR,
381              DATEDIFF(CURDATE(), MAX(rt.date)) AS delayR,
382              '' AS nbComments,
383              '' AS lastDayC,
384              '' AS delayC
385      FROM ".RATE_TABLE." as rt
386            JOIN ".USERS_TABLE." ut ON ut.id = rt.user_id
387      GROUP BY user_id
388      UNION
389      SELECT author AS name,
390          ut.id AS id,
391          '' AS nbRates,
392          '' AS maxRate,
393          '' AS minRate,
394          '' AS avgRate,
395          '' AS devRate,
396          '' AS lastDayR,
397          '' AS delayR,
398          COUNT(ct.id) AS nbComments,
399          MAX(DATE(date)) AS lastDayC,
400          DATEDIFF(CURDATE(), MAX(date)) AS delayC
401      FROM ".COMMENTS_TABLE." as ct
402           JOIN ".USERS_TABLE." ut ON ut.username = ct.author
403      GROUP BY author
404     ) AS t1
405GROUP BY id;";
406
407
408    $result=pwg_query($sql);
409    if($result)
410    {
411      while($row=mysql_fetch_assoc($result))
412      {
413        $returned[]=$row;
414      }
415    }
416
417    return($returned);
418  }
419
420
421  /*
422   * returns an array of user stats properties
423   *
424   * parameters :
425   *  $userId : the user id
426   *
427   * each row is an array :
428   *  "categoryId" : the category id
429   *  "catName"    : the categery name
430   *  "physical"   : "Y" if the category is physical, "N" if it's a virtual category
431   *  "nbRates"    : number of rates made by the user
432   *  "maxRate"    : highest rate made by the user
433   *  "minRate"    : lowest rate made by the user
434   *  "avgRate"    : average rate made by th user
435   *  "devRate"    : deviation rate made by the user
436   *  "nbDaysR"    : number of days where the user made a rate
437   *  "lastDayR"   : last date when a user made a rate
438   *  "delayR"     : delay (in days) between the first and the last rate
439   *  "nbComments" : number of comment post by the user
440   *  "nbDaysC"    : number of days where the user post a comment
441   *  "lastDayC"   : last date when a user post a comment
442   *  "delayC"     : delay (in days) between the first and the last posted comment
443  */
444  private function getUserStats($userId)
445  {
446    $returned=array();
447
448    $sql="
449SELECT t1.name AS name,
450       MAX(t1.id) AS id,
451       MAX(nbRates) AS nbRates,
452       MAX(maxRate) AS maxRate,
453       MAX(minRate) AS minRate,
454       MAX(avgRate) AS avgRate,
455       MAX(devRate) AS devRate,
456       MAX(lastDayR) AS lastDayR,
457       MAX(delayR) AS delayR,
458       MAX(nbComments) AS nbComments,
459       MAX(lastDayC) AS lastDayC,
460       MAX(delayC) AS delayC,
461       category_id as categoryId,
462       uppercats AS upperCats
463FROM (
464      SELECT username AS name,
465             user_id AS id,
466             COUNT(element_id) as nbRates,
467             MAX(rate) AS maxRate,
468             MIN(rate) AS minRate,
469             ROUND(AVG(rate),2) AS avgRate,
470             ROUND(STDDEV(rate),2) AS devRate,
471             MAX(rt.date) AS lastDayR,
472             DATEDIFF(CURDATE(), MAX(rt.date)) AS delayR,
473             '' AS nbComments,
474             '' AS lastDayC,
475             '' AS delayC,
476             category_id
477      FROM ".RATE_TABLE." AS rt
478           JOIN ".USERS_TABLE." AS ut ON ut.id = rt.user_id
479                LEFT OUTER JOIN ".IMAGE_CATEGORY_TABLE." AS ict ON ict.image_id = rt.element_id
480      WHERE ut.id = ".$userId."
481      GROUP BY id, category_id
482      UNION
483      SELECT author AS name,
484             ut2.id AS id,
485             '' AS nbRates,
486             '' AS maxRate,
487             '' AS minRate,
488             '' AS avgRate,
489             '' AS devRate,
490             '' AS lastDayR,
491             '' AS delayR,
492             COUNT(ct2.id) AS nbComments,
493             MAX(DATE(date)) AS lastDayC,
494             DATEDIFF(CURDATE(), MAX(date)) AS delayC,
495             category_id
496      FROM ".COMMENTS_TABLE." AS ct2
497           JOIN ".USERS_TABLE." AS ut2 ON ut2.username = ct2.author
498                LEFT OUTER JOIN ".IMAGE_CATEGORY_TABLE." AS ict2 ON ict2.image_id = ct2.image_id
499      WHERE ut2.id = ".$userId."
500      GROUP BY id
501     ) AS t1
502       LEFT OUTER JOIN ".CATEGORIES_TABLE." AS ct ON t1.category_id = ct.id
503GROUP BY t1.id, category_id";
504
505    $result=pwg_query($sql);
506    if($result)
507    {
508      while($row=mysql_fetch_assoc($result))
509      {
510        $returned[$row['categoryId']]=$row;
511      }
512    }
513
514    return($returned);
515  }
516
517
518
519
520  /* ---------------------------------------------------------------------------
521   * AJAX functions
522   * ------------------------------------------------------------------------- */
523
524  private function ajaxGetUserStat($userId)
525  {
526    $local_tpl = new Template(USERSTAT_PATH."admin/", "");
527    $local_tpl->set_filename('body_page',
528                  dirname($this->filelocation).'/admin/userstat_userstat.tpl');
529
530    $template_datas["list"] = $this->makeUserStats($userId);
531    $template_datas["userName"] = $this->getUserName($userId);
532
533    $local_tpl->assign('datas', $template_datas);
534    return($local_tpl->parse('body_page', true));
535  }
536
537
538} // UserStat_AI class
539
540
541?>
Note: See TracBrowser for help on using the repository browser.