source: trunk/include/functions_notification.inc.php @ 20384

Last change on this file since 20384 was 19703, checked in by plg, 11 years ago

update Piwigo headers to 2013 (the end of the world didn't occur as expected on r12922)

  • Property svn:eol-style set to LF
File size: 16.8 KB
Line 
1<?php
2// +-----------------------------------------------------------------------+
3// | Piwigo - a PHP based photo gallery                                    |
4// +-----------------------------------------------------------------------+
5// | Copyright(C) 2008-2013 Piwigo Team                  http://piwigo.org |
6// | Copyright(C) 2003-2008 PhpWebGallery Team    http://phpwebgallery.net |
7// | Copyright(C) 2002-2003 Pierrick LE GALL   http://le-gall.net/pierrick |
8// +-----------------------------------------------------------------------+
9// | This program is free software; you can redistribute it and/or modify  |
10// | it under the terms of the GNU General Public License as published by  |
11// | the Free Software Foundation                                          |
12// |                                                                       |
13// | This program is distributed in the hope that it will be useful, but   |
14// | WITHOUT ANY WARRANTY; without even the implied warranty of            |
15// | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU      |
16// | General Public License for more details.                              |
17// |                                                                       |
18// | You should have received a copy of the GNU General Public License     |
19// | along with this program; if not, write to the Free Software           |
20// | Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, |
21// | USA.                                                                  |
22// +-----------------------------------------------------------------------+
23
24// +-----------------------------------------------------------------------+
25// |                               functions                               |
26// +-----------------------------------------------------------------------+
27
28/*
29 * get standard sql where in order to
30 * restict an filter caregories and images
31 *
32 * IMAGE_CATEGORY_TABLE muste named ic in the query
33 *
34 * @param none
35 *
36 * @return string sql where
37 */
38function get_std_sql_where_restrict_filter($prefix_condition, $img_field='ic.image_id', $force_one_condition = false)
39{
40  return get_sql_condition_FandF
41          (
42            array
43              (
44                'forbidden_categories' => 'ic.category_id',
45                'visible_categories' => 'ic.category_id',
46                'visible_images' => $img_field
47              ),
48            $prefix_condition,
49            $force_one_condition
50          );
51}
52
53/*
54 * Execute custom notification query
55 *
56 * @param string action ('count' or 'info')
57 * @param string type of query ('new_comments', 'unvalidated_comments', 'new_elements', 'updated_categories', 'new_users')
58 * @param string start (mysql datetime format)
59 * @param string end (mysql datetime format)
60 *
61 * @return integer for action count
62 *         array for info
63 */
64function custom_notification_query($action, $type, $start, $end)
65{
66  global $user;
67
68  switch($type)
69  {
70    case 'new_comments':
71      $query = '
72  FROM '.COMMENTS_TABLE.' AS c
73     , '.IMAGE_CATEGORY_TABLE.' AS ic
74  WHERE c.image_id = ic.image_id';
75      if (!empty($start))
76      {
77        $query .= '
78    AND c.validation_date > \''.$start.'\'';
79      }
80      if (!empty($end))
81      {
82        $query .= '
83    AND c.validation_date <= \''.$end.'\'';
84      }
85      $query .= get_std_sql_where_restrict_filter('AND').'
86;';
87      break;
88    case 'unvalidated_comments':
89      $query = '
90  FROM '.COMMENTS_TABLE.'
91  WHERE 1=1';
92      if (!empty($start))
93      {
94        $query .= ' AND date> \''.$start.'\'';
95      }
96      if (!empty($end))
97      {
98        $query .= ' AND date <= \''.$end.'\'';
99      }
100      $query .= ' AND validated = \'false\'
101;';
102      break;
103    case 'new_elements':
104      $query = '
105  FROM '.IMAGES_TABLE.' INNER JOIN '.IMAGE_CATEGORY_TABLE.' AS ic ON image_id = id
106  WHERE 1=1';
107      if (!empty($start))
108      {
109        $query .= ' AND date_available > \''.$start.'\'';
110      }
111      if (!empty($end))
112      {
113        $query .= ' AND date_available <= \''.$end.'\'';
114      }
115      $query .= get_std_sql_where_restrict_filter('AND', 'id').'
116;';
117      break;
118    case 'updated_categories':
119      $query = '
120  FROM '.IMAGES_TABLE.' INNER JOIN '.IMAGE_CATEGORY_TABLE.' AS ic ON image_id = id
121  WHERE 1=1';
122      if (!empty($start))
123      {
124        $query .= ' AND date_available > \''.$start.'\'';
125      }
126      if (!empty($end))
127      {
128        $query .= ' AND date_available <= \''.$end.'\'';
129      }
130      $query .= get_std_sql_where_restrict_filter('AND', 'id').'
131;';
132      break;
133    case 'new_users':
134      $query = '
135  FROM '.USER_INFOS_TABLE.'
136  WHERE 1=1';
137      if (!empty($start))
138      {
139        $query .= ' AND registration_date > \''.$start.'\'';
140      }
141      if (!empty($end))
142      {
143        $query .= ' AND registration_date <= \''.$end.'\'';
144      }
145      $query .= '
146;';
147      break;
148    default:
149      // stop this function and return nothing
150      return;
151      break;
152  }
153
154  switch($action)
155  {
156    case 'count':
157      switch($type)
158      {
159        case 'new_comments':
160          $field_id = 'c.id';
161          break;
162        case 'unvalidated_comments':
163          $field_id = 'id';
164          break;
165        case 'new_elements':
166          $field_id = 'image_id';
167          break;
168        case 'updated_categories':
169          $field_id = 'category_id';
170          break;
171        case 'new_users':
172          $field_id = 'user_id';
173          break;
174    }
175    $query = 'SELECT count(distinct '.$field_id.') as CountId
176'.$query;
177    list($count) = pwg_db_fetch_row(pwg_query($query));
178    return $count;
179
180    break;
181    case 'info':
182      switch($type)
183      {
184        case 'new_comments':
185          $fields = array('c.id');
186          break;
187        case 'unvalidated_comments':
188          $fields = array('id');
189          break;
190        case 'new_elements':
191          $fields = array('image_id');
192          break;
193        case 'updated_categories':
194          $fields = array('category_id');
195          break;
196        case 'new_users':
197          $fields = array('user_id');
198          break;
199      }
200
201    $query = 'SELECT distinct '.implode(', ', $fields).'
202'.$query;
203    $result = pwg_query($query);
204
205    $infos = array();
206
207    while ($row = pwg_db_fetch_assoc($result))
208    {
209      array_push($infos, $row);
210    }
211
212    return $infos;
213
214    break;
215  }
216
217  //return is done on previous switch($action)
218}
219
220/**
221 * new comments between two dates, according to authorized categories
222 *
223 * @param string start (mysql datetime format)
224 * @param string end (mysql datetime format)
225 * @param string forbidden categories (comma separated)
226 * @return count comment ids
227 */
228function nb_new_comments($start, $end)
229{
230  return custom_notification_query('count', 'new_comments', $start, $end);
231}
232
233/**
234 * new comments between two dates, according to authorized categories
235 *
236 * @param string start (mysql datetime format)
237 * @param string end (mysql datetime format)
238 * @param string forbidden categories (comma separated)
239 * @return array comment ids
240 */
241function new_comments($start, $end)
242{
243  return custom_notification_query('info', 'new_comments', $start, $end);
244}
245
246/**
247 * unvalidated at a precise date
248 *
249 * Comments that are registered and not validated yet on a precise date
250 *
251 * @param string start (mysql datetime format)
252 * @param string end (mysql datetime format)
253 * @return count comment ids
254 */
255function nb_unvalidated_comments($start, $end)
256{
257  return custom_notification_query('count', 'unvalidated_comments', $start, $end);
258}
259
260
261/**
262 * new elements between two dates, according to authorized categories
263 *
264 * @param string start (mysql datetime format)
265 * @param string end (mysql datetime format)
266 * @param string forbidden categories (comma separated)
267 * @return count element ids
268 */
269function nb_new_elements($start, $end)
270{
271  return custom_notification_query('count', 'new_elements', $start, $end);
272}
273
274/**
275 * new elements between two dates, according to authorized categories
276 *
277 * @param string start (mysql datetime format)
278 * @param string end (mysql datetime format)
279 * @param string forbidden categories (comma separated)
280 * @return array element ids
281 */
282function new_elements($start, $end)
283{
284  return custom_notification_query('info', 'new_elements', $start, $end);
285}
286
287/**
288 * updated categories between two dates, according to authorized categories
289 *
290 * @param string start (mysql datetime format)
291 * @param string end (mysql datetime format)
292 * @param string forbidden categories (comma separated)
293 * @return count element ids
294 */
295function nb_updated_categories($start, $end)
296{
297  return custom_notification_query('count', 'updated_categories', $start, $end);
298}
299
300/**
301 * updated categories between two dates, according to authorized categories
302 *
303 * @param string start (mysql datetime format)
304 * @param string end (mysql datetime format)
305 * @param string forbidden categories (comma separated)
306 * @return array element ids
307 */
308function updated_categories($start, $end)
309{
310  return custom_notification_query('info', 'updated_categories', $start, $end);
311}
312
313/**
314 * new registered users between two dates
315 *
316 * @param string start (mysql datetime format)
317 * @param string end (mysql datetime format)
318 * @return count user ids
319 */
320function nb_new_users($start, $end)
321{
322  return custom_notification_query('count', 'new_users', $start, $end);
323}
324
325/**
326 * new registered users between two dates
327 *
328 * @param string start (mysql datetime format)
329 * @param string end (mysql datetime format)
330 * @return array user ids
331 */
332function new_users($start, $end)
333{
334  return custom_notification_query('info', 'new_users', $start, $end);
335}
336
337/**
338 * There are new between two dates ?
339 *
340 * Informations : number of new comments, number of new elements, number of
341 * updated categories. Administrators are also informed about : number of
342 * unvalidated comments, number of new users (TODO : number of unvalidated
343 * elements)
344 *
345 * @param string start date (mysql datetime format)
346 * @param string end date (mysql datetime format)
347 *
348 * @return boolean : true if exist news else false
349 */
350function news_exists($start, $end)
351{
352  return (
353          (nb_new_comments($start, $end) > 0) or
354          (nb_new_elements($start, $end) > 0) or
355          (nb_updated_categories($start, $end) > 0) or
356          ((is_admin()) and (nb_unvalidated_comments($start, $end) > 0)) or
357          ((is_admin()) and (nb_new_users($start, $end) > 0)));
358}
359
360/**
361 * Formats a news line and adds it to the array (e.g. '5 new elements')
362 */
363function add_news_line(&$news, $count, $singular_fmt_key, $plural_fmt_key, $url='', $add_url=false)
364{
365  if ($count > 0)
366  {
367    $line = l10n_dec($singular_fmt_key, $plural_fmt_key, $count);
368    if ($add_url and !empty($url) )
369    {
370      $line = '<a href="'.$url.'">'.$line.'</a>';
371    }
372    array_push($news, $line);
373  }
374}
375
376/**
377 * What's new between two dates ?
378 *
379 * Informations : number of new comments, number of new elements, number of
380 * updated categories. Administrators are also informed about : number of
381 * unvalidated comments, number of new users (TODO : number of unvalidated
382 * elements)
383 *
384 * @param string start date (mysql datetime format)
385 * @param string end date (mysql datetime format)
386 * @param bool exclude_img_cats if true, no info about new images/categories
387 * @param bool add_url add html A link around news
388 *
389 * @return array of news
390 */
391function news($start, $end, $exclude_img_cats=false, $add_url=false)
392{
393  $news = array();
394
395  if (!$exclude_img_cats)
396  {
397    add_news_line( $news,
398      nb_new_elements($start, $end), '%d new photo', '%d new photos',
399      make_index_url(array('section'=>'recent_pics')), $add_url );
400  }
401
402  if (!$exclude_img_cats)
403  {
404    add_news_line( $news,
405      nb_updated_categories($start, $end), '%d album updated', '%d albums updated',
406      make_index_url(array('section'=>'recent_cats')), $add_url );
407  }
408
409  add_news_line( $news,
410      nb_new_comments($start, $end), '%d new comment', '%d new comments',
411      get_root_url().'comments.php', $add_url );
412
413  if (is_admin())
414  {
415    add_news_line( $news,
416        nb_unvalidated_comments($start, $end), '%d comment to validate', '%d comments to validate',
417        get_root_url().'admin.php?page=comments', $add_url );
418
419    add_news_line( $news,
420        nb_new_users($start, $end), '%d new user', '%d new users',
421        get_root_url().'admin.php?page=user_list', $add_url );
422  }
423
424  return $news;
425}
426
427/**
428 * returns information about recently published elements grouped by post date
429 * @param int max_dates maximum returned number of recent dates
430 * @param int max_elements maximum returned number of elements per date
431 * @param int max_cats maximum returned number of categories per date
432 */
433function get_recent_post_dates($max_dates, $max_elements, $max_cats)
434{
435  global $conf, $user;
436
437  $where_sql = get_std_sql_where_restrict_filter('WHERE', 'i.id', true);
438
439  $query = '
440SELECT date_available,
441      COUNT(DISTINCT id) AS nb_elements,
442      COUNT(DISTINCT category_id) AS nb_cats
443  FROM '.IMAGES_TABLE.' i INNER JOIN '.IMAGE_CATEGORY_TABLE.' AS ic ON id=image_id
444  '.$where_sql.'
445  GROUP BY date_available
446  ORDER BY date_available DESC
447  LIMIT '.$max_dates.'
448;';
449  $result = pwg_query($query);
450  $dates = array();
451  while ($row = pwg_db_fetch_assoc($result))
452  {
453    array_push($dates, $row);
454  }
455
456  for ($i=0; $i<count($dates); $i++)
457  {
458    if ($max_elements>0)
459    { // get some thumbnails ...
460      $query = '
461SELECT DISTINCT i.*
462  FROM '.IMAGES_TABLE.' i INNER JOIN '.IMAGE_CATEGORY_TABLE.' AS ic ON id=image_id
463  '.$where_sql.'
464    AND date_available=\''.$dates[$i]['date_available'].'\'
465  ORDER BY '.DB_RANDOM_FUNCTION.'()
466  LIMIT '.$max_elements.'
467;';
468      $dates[$i]['elements'] = array();
469      $result = pwg_query($query);
470      while ($row = pwg_db_fetch_assoc($result))
471      {
472        array_push($dates[$i]['elements'], $row);
473      }
474    }
475
476    if ($max_cats>0)
477    {// get some categories ...
478      $query = '
479SELECT DISTINCT c.uppercats, COUNT(DISTINCT i.id) AS img_count
480  FROM '.IMAGES_TABLE.' i INNER JOIN '.IMAGE_CATEGORY_TABLE.' AS ic ON i.id=image_id
481    INNER JOIN '.CATEGORIES_TABLE.' c ON c.id=category_id
482  '.$where_sql.'
483    AND date_available=\''.$dates[$i]['date_available'].'\'
484  GROUP BY category_id, c.uppercats
485  ORDER BY img_count DESC
486  LIMIT '.$max_cats.'
487;';
488      $dates[$i]['categories'] = array();
489      $result = pwg_query($query);
490      while ($row = pwg_db_fetch_assoc($result))
491      {
492        array_push($dates[$i]['categories'], $row);
493      }
494    }
495  }
496  return $dates;
497}
498
499/*
500  Call function get_recent_post_dates but
501  the parameters to be passed to the function, as an indexed array.
502
503*/
504function get_recent_post_dates_array($args)
505{
506  return
507    get_recent_post_dates
508    (
509      (empty($args['max_dates']) ? 3 : $args['max_dates']),
510      (empty($args['max_elements']) ? 3 : $args['max_elements']),
511      (empty($args['max_cats']) ? 3 : $args['max_cats'])
512    );
513}
514
515
516/**
517 * returns html description about recently published elements grouped by post date
518 * @param $date_detail: selected date computed by get_recent_post_dates function
519 */
520function get_html_description_recent_post_date($date_detail)
521{
522  global $conf;
523
524  $description = '<ul>';
525
526  $description .=
527        '<li>'
528        .l10n_dec('%d new photo', '%d new photos', $date_detail['nb_elements'])
529        .' ('
530        .'<a href="'.make_index_url(array('section'=>'recent_pics')).'">'
531          .l10n('Recent photos').'</a>'
532        .')'
533        .'</li><br>';
534
535  foreach($date_detail['elements'] as $element)
536  {
537    $tn_src = DerivativeImage::thumb_url($element);
538    $description .= '<a href="'.
539                    make_picture_url(array(
540                        'image_id' => $element['id'],
541                        'image_file' => $element['file'],
542                      ))
543                    .'"><img src="'.$tn_src.'"></a>';
544  }
545  $description .= '...<br>';
546
547  $description .=
548        '<li>'
549        .l10n_dec('%d album updated', '%d albums updated',
550                  $date_detail['nb_cats'])
551        .'</li>';
552
553  $description .= '<ul>';
554  foreach($date_detail['categories'] as $cat)
555  {
556    $description .=
557          '<li>'
558          .get_cat_display_name_cache($cat['uppercats'])
559          .' ('.
560          l10n_dec('%d new photo',
561                   '%d new photos', $cat['img_count']).')'
562          .'</li>';
563  }
564  $description .= '</ul>';
565
566  $description .= '</ul>';
567
568  return $description;
569}
570
571/**
572 * returns title about recently published elements grouped by post date
573 * @param $date_detail: selected date computed by get_recent_post_dates function
574 */
575function get_title_recent_post_date($date_detail)
576{
577  global $lang;
578
579  $date = $date_detail['date_available'];
580  $exploded_date = strptime($date, '%Y-%m-%d %H:%M:%S');
581
582  $title = l10n_dec('%d new photo', '%d new photos', $date_detail['nb_elements']);
583  $title .= ' ('.$lang['month'][1+$exploded_date['tm_mon']].' '.$exploded_date['tm_mday'].')';
584
585  return $title;
586}
587
588if(!function_exists("strptime"))
589{
590                function strptime($date, $fmt)
591                {
592                        if ($fmt != '%Y-%m-%d %H:%M:%S')
593                                die('Invalid strptime format '.$fmt);
594                        list($y,$m,$d,$H,$M,$S) = preg_split('/[-: ]/', $date);
595                        $res = localtime( mktime($H,$M,$S,$m,$d,$y), true );
596                        return $res;
597                }
598 }
599?>
Note: See TracBrowser for help on using the repository browser.