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