source: trunk/comments.php @ 26759

Revision 26759, 16.8 KB checked in by rvelices, 6 years ago (diff)

one less expensive query in comments.php (triple table join is already being done and we can use the result)

  • Property svn:eol-style set to LF
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// |                           initialization                              |
26// +-----------------------------------------------------------------------+
27define('PHPWG_ROOT_PATH','./');
28include_once(PHPWG_ROOT_PATH.'include/common.inc.php');
29include_once(PHPWG_ROOT_PATH.'include/functions_comment.inc.php');
30
31if (!$conf['activate_comments'])
32{
33  page_not_found(null);
34}
35
36// +-----------------------------------------------------------------------+
37// | Check Access and exit when user status is not ok                      |
38// +-----------------------------------------------------------------------+
39check_status(ACCESS_GUEST);
40
41$url_self = PHPWG_ROOT_PATH.'comments.php'
42  .get_query_string_diff(array('delete','edit','validate','pwg_token'));
43
44$sort_order = array(
45  'DESC' => l10n('descending'),
46  'ASC'  => l10n('ascending')
47  );
48
49// sort_by : database fields proposed for sorting comments list
50$sort_by = array(
51  'date' => l10n('comment date'),
52  'image_id' => l10n('photo')
53  );
54
55// items_number : list of number of items to display per page
56$items_number = array(5,10,20,50,'all');
57
58// if the default value is not in the expected values, we add it in the $items_number array
59if (!in_array($conf['comments_page_nb_comments'], $items_number))
60{
61  $items_number_new = array();
62
63  $is_inserted = false;
64
65  foreach ($items_number as $number)
66  {
67    if ($number > $conf['comments_page_nb_comments'] or ($number == 'all' and !$is_inserted))
68    {
69      $items_number_new[] = $conf['comments_page_nb_comments'];
70      $is_inserted = true;
71    }
72   
73    $items_number_new[] = $number;
74  }
75
76  $items_number = $items_number_new;
77}
78
79// since when display comments ?
80//
81$since_options = array(
82  1 => array('label' => l10n('today'),
83             'clause' => 'date > '.pwg_db_get_recent_period_expression(1)),
84  2 => array('label' => l10n('last %d days', 7),
85             'clause' => 'date > '.pwg_db_get_recent_period_expression(7)),
86  3 => array('label' => l10n('last %d days', 30),
87             'clause' => 'date > '.pwg_db_get_recent_period_expression(30)),
88  4 => array('label' => l10n('the beginning'),
89             'clause' => '1=1') // stupid but generic
90  );
91 
92trigger_action('loc_begin_comments');
93
94if (!empty($_GET['since']) && is_numeric($_GET['since']))
95{
96  $page['since'] = $_GET['since'];
97}
98else
99{
100  $page['since'] = 4;
101}
102
103// on which field sorting
104//
105$page['sort_by'] = 'date';
106// if the form was submitted, it overloads default behaviour
107if (isset($_GET['sort_by']) and isset($sort_by[$_GET['sort_by']]) )
108{
109  $page['sort_by'] = $_GET['sort_by'];
110}
111
112// order to sort
113//
114$page['sort_order'] = 'DESC';
115// if the form was submitted, it overloads default behaviour
116if (isset($_GET['sort_order']) and isset($sort_order[$_GET['sort_order']]))
117{
118  $page['sort_order'] = $_GET['sort_order'];
119}
120
121// number of items to display
122//
123$page['items_number'] = $conf['comments_page_nb_comments'];
124if (isset($_GET['items_number']))
125{
126  $page['items_number'] = $_GET['items_number'];
127}
128if ( !is_numeric($page['items_number']) and $page['items_number']!='all' )
129{
130  $page['items_number'] = 10;
131}
132
133$page['where_clauses'] = array();
134
135// which category to filter on ?
136if (isset($_GET['cat']) and 0 != $_GET['cat'])
137{
138  check_input_parameter('cat', $_GET, false, PATTERN_ID);
139
140  $category_ids = get_subcat_ids(array($_GET['cat']));
141  if (empty($category_ids))
142  {
143    $category_ids = array(-1);
144  }
145
146  $page['where_clauses'][] =
147    'category_id IN ('.implode(',', $category_ids).')';
148}
149
150// search a particular author
151if (!empty($_GET['author']))
152{
153  $page['where_clauses'][] =
154    '(u.'.$conf['user_fields']['username'].' = \''.$_GET['author'].'\' OR author = \''.$_GET['author'].'\')';
155}
156
157// search a specific comment (if you're coming directly from an admin
158// notification email)
159if (!empty($_GET['comment_id']))
160{
161  check_input_parameter('comment_id', $_GET, false, PATTERN_ID);
162
163  // currently, the $_GET['comment_id'] is only used by admins from email
164  // for management purpose (validate/delete)
165  if (!is_admin())
166  {
167    $login_url =
168      get_root_url().'identification.php?redirect='
169      .urlencode(urlencode($_SERVER['REQUEST_URI']))
170      ;
171    redirect($login_url);
172  }
173
174  $page['where_clauses'][] = 'com.id = '.$_GET['comment_id'];
175}
176
177// search a substring among comments content
178if (!empty($_GET['keyword']))
179{
180  $page['where_clauses'][] =
181    '('.
182    implode(' AND ',
183            array_map(
184              create_function(
185                '$s',
186                'return "content LIKE \'%$s%\'";'
187                ),
188              preg_split('/[\s,;]+/', $_GET['keyword'] )
189              )
190      ).
191    ')';
192}
193
194$page['where_clauses'][] = $since_options[$page['since']]['clause'];
195
196// which status to filter on ?
197if ( !is_admin() )
198{
199  $page['where_clauses'][] = 'validated=\'true\'';
200}
201
202$page['where_clauses'][] = get_sql_condition_FandF
203  (
204    array
205      (
206        'forbidden_categories' => 'category_id',
207        'visible_categories' => 'category_id',
208        'visible_images' => 'ic.image_id'
209      ),
210    '', true
211  );
212
213// +-----------------------------------------------------------------------+
214// |                         comments management                           |
215// +-----------------------------------------------------------------------+
216
217$comment_id = null;
218$action = null;
219
220$actions = array('delete', 'validate', 'edit');
221foreach ($actions as $loop_action)
222{
223  if (isset($_GET[$loop_action]))
224  {
225    $action = $loop_action;
226    check_input_parameter($action, $_GET, false, PATTERN_ID);
227    $comment_id = $_GET[$action];
228    break;
229  }
230}
231
232if (isset($action))
233{
234  $comment_author_id = get_comment_author_id($comment_id);
235
236  if (can_manage_comment($action, $comment_author_id))
237  {
238    $perform_redirect = false;
239
240    if ('delete' == $action)
241    {
242      check_pwg_token();
243      delete_user_comment($comment_id);
244      $perform_redirect = true;
245    }
246
247    if ('validate' == $action)
248    {
249      check_pwg_token();
250      validate_user_comment($comment_id);
251      $perform_redirect = true;
252    }
253
254    if ('edit' == $action)
255    {
256      if (!empty($_POST['content']))
257      {
258        check_pwg_token();
259        $comment_action = update_user_comment(
260          array(
261            'comment_id' => $_GET['edit'],
262            'image_id' => $_POST['image_id'],
263            'content' => $_POST['content'],
264            'website_url' => @$_POST['website_url'],
265            ),
266          $_POST['key']
267          );
268       
269        switch ($comment_action)
270        {
271          case 'moderate':
272            $_SESSION['page_infos'][] = l10n('An administrator must authorize your comment before it is visible.');
273          case 'validate':
274            $_SESSION['page_infos'][] = l10n('Your comment has been registered');
275            $perform_redirect = true;
276            break;
277          case 'reject':
278            $_SESSION['page_errors'][] = l10n('Your comment has NOT been registered because it did not pass the validation rules');
279            break;
280          default:
281            trigger_error('Invalid comment action '.$comment_action, E_USER_WARNING);
282        }
283      }
284     
285      $edit_comment = $_GET['edit'];
286    }
287
288    if ($perform_redirect)
289    {
290      redirect($url_self);
291    }
292  }
293}
294
295// +-----------------------------------------------------------------------+
296// |                       page header and options                         |
297// +-----------------------------------------------------------------------+
298
299$title= l10n('User comments');
300$page['body_id'] = 'theCommentsPage';
301
302$template->set_filenames(array('comments'=>'comments.tpl'));
303$template->assign(
304  array(
305    'F_ACTION'=>PHPWG_ROOT_PATH.'comments.php',
306    'F_KEYWORD'=> @htmlspecialchars(stripslashes($_GET['keyword'], ENT_QUOTES, 'utf-8')),
307    'F_AUTHOR'=> @htmlspecialchars(stripslashes($_GET['author'], ENT_QUOTES, 'utf-8')),
308    )
309  );
310
311// +-----------------------------------------------------------------------+
312// |                          form construction                            |
313// +-----------------------------------------------------------------------+
314
315// Search in a particular category
316$blockname = 'categories';
317
318$query = '
319SELECT id, name, uppercats, global_rank
320  FROM '.CATEGORIES_TABLE.'
321'.get_sql_condition_FandF
322  (
323    array
324      (
325        'forbidden_categories' => 'id',
326        'visible_categories' => 'id'
327      ),
328    'WHERE'
329  ).'
330;';
331display_select_cat_wrapper($query, array(@$_GET['cat']), $blockname, true);
332
333// Filter on recent comments...
334$tpl_var=array();
335foreach ($since_options as $id => $option)
336{
337  $tpl_var[ $id ] = $option['label'];
338}
339$template->assign( 'since_options', $tpl_var);
340$template->assign( 'since_options_selected', $page['since']);
341
342// Sort by
343$template->assign( 'sort_by_options', $sort_by);
344$template->assign( 'sort_by_options_selected', $page['sort_by']);
345
346// Sorting order
347$template->assign( 'sort_order_options', $sort_order);
348$template->assign( 'sort_order_options_selected', $page['sort_order']);
349
350
351// Number of items
352$blockname = 'items_number_option';
353$tpl_var=array();
354foreach ($items_number as $option)
355{
356  $tpl_var[ $option ] = is_numeric($option) ? $option : l10n($option);
357}
358$template->assign( 'item_number_options', $tpl_var);
359$template->assign( 'item_number_options_selected', $page['items_number']);
360
361
362// +-----------------------------------------------------------------------+
363// |                            navigation bar                             |
364// +-----------------------------------------------------------------------+
365
366if (isset($_GET['start']) and is_numeric($_GET['start']))
367{
368  $start = $_GET['start'];
369}
370else
371{
372  $start = 0;
373}
374
375// +-----------------------------------------------------------------------+
376// |                        last comments display                          |
377// +-----------------------------------------------------------------------+
378
379$comments = array();
380$element_ids = array();
381$category_ids = array();
382
383$query = '
384SELECT SQL_CALC_FOUND_ROWS com.id AS comment_id,
385       com.image_id,
386       com.author,
387       com.author_id,
388       u.'.$conf['user_fields']['email'].' AS user_email,
389       com.email,
390       com.date,
391       com.website_url,
392       com.content,
393       com.validated
394  FROM '.IMAGE_CATEGORY_TABLE.' AS ic
395    INNER JOIN '.COMMENTS_TABLE.' AS com
396    ON ic.image_id = com.image_id
397    LEFT JOIN '.USERS_TABLE.' As u
398    ON u.'.$conf['user_fields']['id'].' = com.author_id
399  WHERE '.implode('
400    AND ', $page['where_clauses']).'
401  GROUP BY comment_id
402  ORDER BY '.$page['sort_by'].' '.$page['sort_order'];
403if ('all' != $page['items_number'])
404{
405  $query.= '
406  LIMIT '.$page['items_number'].' OFFSET '.$start;
407}
408$query.= '
409;';
410$result = pwg_query($query);
411while ($row = pwg_db_fetch_assoc($result))
412{
413  $comments[] = $row;
414  $element_ids[] = $row['image_id'];
415}
416list($counter) = pwg_db_fetch_row(pwg_query('SELECT FOUND_ROWS()'));
417
418$url = PHPWG_ROOT_PATH.'comments.php'
419  .get_query_string_diff(array('start','edit','delete','validate','pwg_token'));
420
421$navbar = create_navigation_bar($url,
422                                $counter,
423                                $start,
424                                $page['items_number'],
425                                '');
426
427$template->assign('navbar', $navbar);
428
429
430if (count($comments) > 0)
431{
432  // retrieving element informations
433  $elements = array();
434  $query = '
435SELECT *
436  FROM '.IMAGES_TABLE.'
437  WHERE id IN ('.implode(',', $element_ids).')
438;';
439  $result = pwg_query($query);
440  while ($row = pwg_db_fetch_assoc($result))
441  {
442    $elements[$row['id']] = $row;
443  }
444
445  // retrieving category informations
446  $query = '
447SELECT c.id, name, permalink, uppercats, com.id as comment_id
448  FROM '.CATEGORIES_TABLE.' AS c
449  LEFT JOIN '.IMAGE_CATEGORY_TABLE.' AS ic
450  ON c.id=ic.category_id
451  LEFT JOIN '.COMMENTS_TABLE.' AS com
452  ON ic.image_id=com.image_id
453  '.get_sql_condition_FandF
454    (
455      array
456      (
457        'forbidden_categories' => 'c.id',
458        'visible_categories' => 'c.id'
459       ),
460      'WHERE'
461     ).'
462;';
463  $categories = hash_from_query($query, 'comment_id');
464
465  foreach ($comments as $comment)
466  {
467    if (!empty($elements[$comment['image_id']]['name']))
468    {
469      $name=$elements[$comment['image_id']]['name'];
470    }
471    else
472    {
473      $name=get_name_from_file($elements[$comment['image_id']]['file']);
474    }
475
476    // source of the thumbnail picture
477    $src_image = new SrcImage($elements[$comment['image_id']]);
478
479    // link to the full size picture
480    $url = make_picture_url(
481      array(
482        'category' => $categories[ $comment['comment_id'] ],
483        'image_id' => $comment['image_id'],
484        'image_file' => $elements[$comment['image_id']]['file'],
485        )
486      );
487     
488    $email = null;
489    if (!empty($comment['user_email']))
490    {
491      $email = $comment['user_email'];
492    }
493    else if (!empty($comment['email']))
494    {
495      $email = $comment['email'];
496    }
497
498    $tpl_comment = array(
499      'ID' => $comment['comment_id'],
500      'U_PICTURE' => $url,
501      'src_image' => $src_image,
502      'ALT' => $name,
503      'AUTHOR' => trigger_event('render_comment_author', $comment['author']),
504      'WEBSITE_URL' => $comment['website_url'],
505      'DATE'=>format_date($comment['date'], true),
506      'CONTENT'=>trigger_event('render_comment_content',$comment['content']),
507      );
508     
509    if (is_admin())
510    {
511      $tpl_comment['EMAIL'] = $email;
512    }
513
514    if (can_manage_comment('delete', $comment['author_id']))
515    {
516      $tpl_comment['U_DELETE'] = add_url_params(
517        $url_self,
518        array(
519          'delete' => $comment['comment_id'],
520          'pwg_token' => get_pwg_token(),
521          )
522        );
523    }
524
525    if (can_manage_comment('edit', $comment['author_id']))
526    {
527      $tpl_comment['U_EDIT'] = add_url_params(
528        $url_self,
529        array(
530          'edit' => $comment['comment_id']
531          )
532        );
533
534      if (isset($edit_comment) and ($comment['comment_id'] == $edit_comment))
535      {
536        $tpl_comment['IN_EDIT'] = true;
537        $key = get_ephemeral_key(2, $comment['image_id']);
538        $tpl_comment['KEY'] = $key;
539        $tpl_comment['IMAGE_ID'] = $comment['image_id'];
540        $tpl_comment['CONTENT'] = $comment['content'];
541        $tpl_comment['PWG_TOKEN'] = get_pwg_token();
542        $tpl_comment['U_CANCEL'] = $url_self;
543      }
544    }
545
546    if (can_manage_comment('validate', $comment['author_id']))
547    {
548      if ('true' != $comment['validated'])
549      {
550        $tpl_comment['U_VALIDATE'] = add_url_params(
551          $url_self,
552          array(
553            'validate'=> $comment['comment_id'],
554            'pwg_token' => get_pwg_token(),
555            )
556          );
557      }
558    }
559    $template->append('comments', $tpl_comment);
560  }
561}
562
563$derivative_params = trigger_event('get_comments_derivative_params', ImageStdParams::get_by_type(IMG_THUMB) );
564$template->assign( 'derivative_params', $derivative_params );
565
566// include menubar
567$themeconf = $template->get_template_vars('themeconf');
568if (!isset($themeconf['hide_menu_on']) OR !in_array('theCommentsPage', $themeconf['hide_menu_on']))
569{
570  include( PHPWG_ROOT_PATH.'include/menubar.inc.php');
571}
572
573// +-----------------------------------------------------------------------+
574// |                           html code display                           |
575// +-----------------------------------------------------------------------+
576include(PHPWG_ROOT_PATH.'include/page_header.php');
577trigger_action('loc_end_comments');
578flush_page_messages();
579$template->pparse('comments');
580include(PHPWG_ROOT_PATH.'include/page_tail.php');
581?>
Note: See TracBrowser for help on using the repository browser.