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

Last change on this file since 28090 was 27369, checked in by rvelices, 11 years ago

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