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

Last change on this file since 12627 was 10344, checked in by grum, 14 years ago

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