source: trunk/include/functions_category.inc.php @ 27369

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

more query2array
  • Property svn:eol-style set to LF
File size: 16.1 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\category
26 */
27
28
29/**
30 * Callback used for sorting by global_rank
31 */
32function global_rank_compare($a, $b)
33{
34  return strnatcasecmp($a['global_rank'], $b['global_rank']);
35}
36
37/**
38 * Callback used for sorting by rank
39 */
40function rank_compare($a, $b)
41{
42  return $a['rank'] - $b['rank'];
43}
44
45/**
46 * Is the category accessible to the connected user ?
47 * If the user is not authorized to see this category, script exits
48 *
49 * @param int $category_id
50 */
51function check_restrictions($category_id)
52{
53  global $user;
54
55  // $filter['visible_categories'] and $filter['visible_images']
56  // are not used because it's not necessary (filter <> restriction)
57  if (in_array($category_id, explode(',', $user['forbidden_categories'])))
58  {
59    access_denied();
60  }
61}
62
63/**
64 * Returns template vars for main categories menu.
65 *
66 * @return array[]
67 */
68function get_categories_menu()
69{
70  global $page, $user, $filter, $conf;
71
72  $query = '
73SELECT ';
74  // From CATEGORIES_TABLE
75  $query.= '
76  id, name, permalink, nb_images, global_rank,';
77  // From USER_CACHE_CATEGORIES_TABLE
78  $query.= '
79  date_last, max_date_last, count_images, count_categories';
80
81  // $user['forbidden_categories'] including with USER_CACHE_CATEGORIES_TABLE
82  $query.= '
83FROM '.CATEGORIES_TABLE.' INNER JOIN '.USER_CACHE_CATEGORIES_TABLE.'
84  ON id = cat_id and user_id = '.$user['id'];
85
86  // Always expand when filter is activated
87  if (!$user['expand'] and !$filter['enabled'])
88  {
89    $where = '
90(id_uppercat is NULL';
91    if (isset($page['category']))
92    {
93      $where .= ' OR id_uppercat IN ('.$page['category']['uppercats'].')';
94    }
95    $where .= ')';
96  }
97  else
98  {
99    $where = '
100  '.get_sql_condition_FandF
101    (
102      array
103        (
104          'visible_categories' => 'id',
105        ),
106      null,
107      true
108    );
109  }
110
111  $where = trigger_event('get_categories_menu_sql_where',
112    $where, $user['expand'], $filter['enabled'] );
113
114  $query.= '
115WHERE '.$where.'
116;';
117
118  $result = pwg_query($query);
119  $cats = array();
120  $selected_category = isset($page['category']) ? $page['category'] : null;
121  while ($row = pwg_db_fetch_assoc($result))
122  {
123    $child_date_last = @$row['max_date_last']> @$row['date_last'];
124    $row = array_merge($row,
125      array(
126        'NAME' => trigger_event(
127          'render_category_name',
128          $row['name'],
129          'get_categories_menu'
130          ),
131        'TITLE' => get_display_images_count(
132          $row['nb_images'],
133          $row['count_images'],
134          $row['count_categories'],
135          false,
136          ' / '
137          ),
138        'URL' => make_index_url(array('category' => $row)),
139        'LEVEL' => substr_count($row['global_rank'], '.') + 1,
140        'SELECTED' => $selected_category['id'] == $row['id'] ? true : false,
141        'IS_UPPERCAT' => $selected_category['id_uppercat'] == $row['id'] ? true : false,
142        )
143      );
144    if ($conf['index_new_icon'])
145    {
146      $row['icon_ts'] = get_icon($row['max_date_last'], $child_date_last);
147    }
148    $cats[] = $row;
149    if ($row['id']==@$page['category']['id']) //save the number of subcats for later optim
150      $page['category']['count_categories'] = $row['count_categories'];
151  }
152  usort($cats, 'global_rank_compare');
153
154  // Update filtered data
155  if (function_exists('update_cats_with_filtered_data'))
156  {
157    update_cats_with_filtered_data($cats);
158  }
159
160  return $cats;
161}
162
163/**
164 * Retrieves informations about a category.
165 *
166 * @param int $id
167 * @return array
168 */
169function get_cat_info($id)
170{
171  $query = '
172SELECT *
173  FROM '.CATEGORIES_TABLE.'
174  WHERE id = '.$id.'
175;';
176  $cat = pwg_db_fetch_assoc(pwg_query($query));
177  if (empty($cat))
178    return null;
179
180  foreach ($cat as $k => $v)
181  {
182    // If the field is true or false, the variable is transformed into a
183    // boolean value.
184    if ($cat[$k] == 'true' or $cat[$k] == 'false')
185    {
186      $cat[$k] = get_boolean( $cat[$k] );
187    }
188  }
189
190  $upper_ids = explode(',', $cat['uppercats']);
191  if ( count($upper_ids)==1 )
192  {// no need to make a query for level 1
193    $cat['upper_names'] = array(
194        array(
195          'id' => $cat['id'],
196          'name' => $cat['name'],
197          'permalink' => $cat['permalink'],
198          )
199      );
200  }
201  else
202  {
203    $query = '
204  SELECT id, name, permalink
205    FROM '.CATEGORIES_TABLE.'
206    WHERE id IN ('.$cat['uppercats'].')
207  ;';
208    $names = query2array($query, 'id');
209
210    // category names must be in the same order than uppercats list
211    $cat['upper_names'] = array();
212    foreach ($upper_ids as $cat_id)
213    {
214      $cat['upper_names'][] = $names[$cat_id];
215    }
216  }
217  return $cat;
218}
219
220/**
221 * Returns an array of image orders available for users/visitors.
222 * Each entry is an array containing
223 *  0: name
224 *  1: SQL ORDER command
225 *  2: visiblity (true or false)
226 *
227 * @return array[]
228 */
229function get_category_preferred_image_orders()
230{
231  global $conf, $page;
232
233  return trigger_event('get_category_preferred_image_orders', array(
234    array(l10n('Default'),                        '',                     true),
235    array(l10n('Photo title, A &rarr; Z'),        'name ASC',             true),
236    array(l10n('Photo title, Z &rarr; A'),        'name DESC',            true),
237    array(l10n('Date created, new &rarr; old'),   'date_creation DESC',   true),
238    array(l10n('Date created, old &rarr; new'),   'date_creation ASC',    true),
239    array(l10n('Date posted, new &rarr; old'),    'date_available DESC',  true),
240    array(l10n('Date posted, old &rarr; new'),    'date_available ASC',   true),
241    array(l10n('Rating score, high &rarr; low'),  'rating_score DESC',    $conf['rate']),
242    array(l10n('Rating score, low &rarr; high'),  'rating_score ASC',     $conf['rate']),
243    array(l10n('Visits, high &rarr; low'),        'hit DESC',             true),
244    array(l10n('Visits, low &rarr; high'),        'hit ASC',              true),
245    array(l10n('Permissions'),                    'level DESC',           is_admin()),
246    ));
247}
248
249/**
250 * Assign a template var useable with {html_options} from a list of categories
251 *
252 * @param array[] $categories (at least id,name,global_rank,uppercats for each)
253 * @param int[] $selected ids of selected items
254 * @param string $blockname variable name in template
255 * @param bool $fullname full breadcrumb or not
256 */
257function display_select_categories($categories,
258                                   $selecteds,
259                                   $blockname,
260                                   $fullname = true)
261{
262  global $template;
263
264  $tpl_cats = array();
265  foreach ($categories as $category)
266  {
267    if ($fullname)
268    {
269      $option = strip_tags(
270        get_cat_display_name_cache(
271          $category['uppercats'],
272          null
273          )
274        );
275    }
276    else
277    {
278      $option = str_repeat('&nbsp;',
279                           (3 * substr_count($category['global_rank'], '.')));
280      $option.= '- ';
281      $option.= strip_tags(
282        trigger_event(
283          'render_category_name',
284          $category['name'],
285          'display_select_categories'
286          )
287        );
288    }
289    $tpl_cats[ $category['id'] ] = $option;
290  }
291
292  $template->assign( $blockname, $tpl_cats);
293  $template->assign( $blockname.'_selected', $selecteds);
294}
295
296/**
297 * Same as display_select_categories but categories are ordered by rank
298 * @see display_select_categories()
299 */
300function display_select_cat_wrapper($query,
301                                    $selecteds,
302                                    $blockname,
303                                    $fullname = true)
304{
305  $categories = query2array($query);
306  usort($categories, 'global_rank_compare');
307  display_select_categories($categories, $selecteds, $blockname, $fullname);
308}
309
310/**
311 * Returns all subcategory identifiers of given category ids
312 *
313 * @param int[] $ids
314 * @return int[]
315 */
316function get_subcat_ids($ids)
317{
318  $query = '
319SELECT DISTINCT(id)
320  FROM '.CATEGORIES_TABLE.'
321  WHERE ';
322  foreach ($ids as $num => $category_id)
323  {
324    is_numeric($category_id)
325      or trigger_error(
326        'get_subcat_ids expecting numeric, not '.gettype($category_id),
327        E_USER_WARNING
328      );
329    if ($num > 0)
330    {
331      $query.= '
332    OR ';
333    }
334    $query.= 'uppercats '.DB_REGEX_OPERATOR.' \'(^|,)'.$category_id.'(,|$)\'';
335  }
336  $query.= '
337;';
338  return array_from_query($query, 'id');
339}
340
341/**
342 * Finds a matching category id from a potential list of permalinks
343 *
344 * @param string[] $permalinks
345 * @param int &$idx filled with the index in $permalinks that matches
346 * @return int|null
347 */
348function get_cat_id_from_permalinks($permalinks, &$idx)
349{
350  $in = '';
351  foreach($permalinks as $permalink)
352  {
353    if ( !empty($in) ) $in.=', ';
354    $in .= '\''.$permalink.'\'';
355  }
356  $query ='
357SELECT cat_id AS id, permalink, 1 AS is_old
358  FROM '.OLD_PERMALINKS_TABLE.'
359  WHERE permalink IN ('.$in.')
360UNION
361SELECT id, permalink, 0 AS is_old
362  FROM '.CATEGORIES_TABLE.'
363  WHERE permalink IN ('.$in.')
364;';
365  $perma_hash = query2array($query, 'permalink');
366
367  if ( empty($perma_hash) )
368    return null;
369  for ($i=count($permalinks)-1; $i>=0; $i--)
370  {
371    if ( isset( $perma_hash[ $permalinks[$i] ] ) )
372    {
373      $idx = $i;
374      $cat_id = $perma_hash[ $permalinks[$i] ]['id'];
375      if ($perma_hash[ $permalinks[$i] ]['is_old'])
376      {
377        $query='
378UPDATE '.OLD_PERMALINKS_TABLE.' SET last_hit=NOW(), hit=hit+1
379  WHERE permalink=\''.$permalinks[$i].'\' AND cat_id='.$cat_id.'
380  LIMIT 1';
381        pwg_query($query);
382      }
383      return $cat_id;
384    }
385  }
386  return null;
387}
388
389/**
390 * Returns display text for images counter of category
391 *
392 * @param int $cat_nb_images nb images directly in category
393 * @param int $cat_count_images nb images in category (including subcats)
394 * @param int $cat_count_categories nb subcats
395 * @param bool $short_message if true append " in this album"
396 * @param string $separator
397 * @return string
398 */
399function get_display_images_count($cat_nb_images, $cat_count_images, $cat_count_categories, $short_message = true, $separator = '\n')
400{
401  $display_text = '';
402
403  if ($cat_count_images > 0)
404  {
405    if ($cat_nb_images > 0 and $cat_nb_images < $cat_count_images)
406    {
407      $display_text.= get_display_images_count($cat_nb_images, $cat_nb_images, 0, $short_message, $separator).$separator;
408      $cat_count_images-= $cat_nb_images;
409      $cat_nb_images = 0;
410    }
411
412    //at least one image direct or indirect
413    $display_text.= l10n_dec('%d photo', '%d photos', $cat_count_images);
414
415    if ($cat_count_categories == 0 or $cat_nb_images == $cat_count_images)
416    {
417      //no descendant categories or descendants do not contain images
418      if (!$short_message)
419      {
420        $display_text.= ' '.l10n('in this album');
421      }
422    }
423    else
424    {
425      $display_text.= ' '.l10n_dec('in %d sub-album', 'in %d sub-albums', $cat_count_categories);
426    }
427  }
428
429  return $display_text;
430}
431
432/**
433 * Find a random photo among all photos inside an album (including sub-albums)
434 *
435 * @param array $category (at least id,uppercats,count_images)
436 * @param bool $recursive
437 * @return int|null
438 */
439function get_random_image_in_category($category, $recursive=true)
440{
441  $image_id = null;
442  if ($category['count_images']>0)
443  {
444    $query = '
445SELECT image_id
446  FROM '.CATEGORIES_TABLE.' AS c
447    INNER JOIN '.IMAGE_CATEGORY_TABLE.' AS ic ON ic.category_id = c.id
448  WHERE ';
449    if ($recursive)
450    {
451      $query.= '
452    (c.id='.$category['id'].' OR uppercats LIKE \''.$category['uppercats'].',%\')';
453    }
454    else
455    {
456      $query.= '
457    c.id='.$category['id'];
458    }
459    $query.= '
460    '.get_sql_condition_FandF
461    (
462      array
463        (
464          'forbidden_categories' => 'c.id',
465          'visible_categories' => 'c.id',
466          'visible_images' => 'image_id',
467        ),
468      "\n  AND"
469    ).'
470  ORDER BY '.DB_RANDOM_FUNCTION.'()
471  LIMIT 1
472;';
473    $result = pwg_query($query);
474    if (pwg_db_num_rows($result) > 0)
475    {
476      list($image_id) = pwg_db_fetch_row($result);
477    }
478  }
479
480  return $image_id;
481}
482
483/**
484 * Get computed array of categories, that means cache data of all categories
485 * available for the current user (count_categories, count_images, etc.).
486 *
487 * @param array &$userdata
488 * @param int $filter_days number of recent days to filter on or null
489 * @return array
490 */
491function get_computed_categories(&$userdata, $filter_days=null)
492{
493  $query = 'SELECT c.id AS cat_id, id_uppercat';
494  // Count by date_available to avoid count null
495  $query .= ',
496  MAX(date_available) AS date_last, COUNT(date_available) AS nb_images
497FROM '.CATEGORIES_TABLE.' as c
498  LEFT JOIN '.IMAGE_CATEGORY_TABLE.' AS ic ON ic.category_id = c.id
499  LEFT JOIN '.IMAGES_TABLE.' AS i
500    ON ic.image_id = i.id
501      AND i.level<='.$userdata['level'];
502
503  if ( isset($filter_days) )
504  {
505    $query .= ' AND i.date_available > '.pwg_db_get_recent_period_expression($filter_days);
506  }
507
508  if ( !empty($userdata['forbidden_categories']) )
509  {
510    $query.= '
511  WHERE c.id NOT IN ('.$userdata['forbidden_categories'].')';
512  }
513
514  $query.= '
515  GROUP BY c.id';
516
517  $result = pwg_query($query);
518
519  $userdata['last_photo_date'] = null;
520  $cats = array();
521  while ($row = pwg_db_fetch_assoc($result))
522  {
523    $row['user_id'] = $userdata['id'];
524    $row['nb_categories'] = 0;
525    $row['count_categories'] = 0;
526    $row['count_images'] = (int)$row['nb_images'];
527    $row['max_date_last'] = $row['date_last'];
528    if ($row['date_last'] > $userdata['last_photo_date'])
529    {
530      $userdata['last_photo_date'] = $row['date_last'];
531    }
532
533    $cats[$row['cat_id']] = $row;
534  }
535
536  foreach ($cats as $cat)
537  {
538    if ( !isset( $cat['id_uppercat'] ) )
539      continue;
540
541    $parent = & $cats[ $cat['id_uppercat'] ];
542    $parent['nb_categories']++;
543
544    do
545    {
546      $parent['count_images'] += $cat['nb_images'];
547      $parent['count_categories']++;
548
549      if ((empty($parent['max_date_last'])) or ($parent['max_date_last'] < $cat['date_last']))
550      {
551        $parent['max_date_last'] = $cat['date_last'];
552      }
553
554      if ( !isset( $parent['id_uppercat'] ) )
555        break;
556      $parent = & $cats[$parent['id_uppercat']];
557    }
558    while (true);
559    unset($parent);
560  }
561
562  if ( isset($filter_days) )
563  {
564    foreach ($cats as $category)
565    {
566      if (empty($category['max_date_last']))
567      {
568        remove_computed_category($cats, $category);
569      }
570    }
571  }
572  return $cats;
573}
574
575/**
576 * Removes a category from computed array of categories and updates counters.
577 *
578 * @param array &$cats
579 * @param array $cat category to remove
580 */
581function remove_computed_category(&$cats, $cat)
582{
583  if ( isset($cats[$cat['id_uppercat']]) )
584  {
585    $parent = &$cats[ $cat['id_uppercat'] ];
586    $parent['nb_categories']--;
587
588    do
589    {
590      $parent['count_images'] -= $cat['nb_images'];
591      $parent['count_categories'] -= 1+$cat['count_categories'];
592
593      if ( !isset($cats[$parent['id_uppercat']]) )
594      {
595        break;
596      }
597      $parent = &$cats[$parent['id_uppercat']];
598    }
599    while (true);
600  }
601
602  unset($cats[$cat['cat_id']]);
603}
604
605?>
Note: See TracBrowser for help on using the repository browser.