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

Last change on this file since 29531 was 16015, checked in by grum, 12 years ago

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