Feature Issue ID 0000601: Filter all public pages with only recent elements

It's a finalized version.
Obsolete code of draft are removed.

You can filter categories and images with recent date period on your screen selection.
In the future, filter could be easy done on other type data (plugin?)

You can flat categories and sub-categories with a recent date period of your choice.

Next, perhaps, a panel to choice recent date for the 2 features.

On draft, there have problem with MySql 5, be careful!

Css problem not resolved:
  - Menu "Categories" is bad centered
  - Icon on dark too on the top


git-svn-id: http://piwigo.org/svn/trunk@1677 68402e56-0260-453c-a942-63ccdbb3a9ee

1 files changed, 192 insertions, 81 deletions

diff --git a/include/functions_user.inc.php b/include/functions_user.inc.php
index d59a97e4d..dba5072fb 100644
--- a/include/functions_user.inc.php
+++ b/include/functions_user.inc.php
@@ -143,12 +143,11 @@ function setup_style($style)
   return new Template(PHPWG_ROOT_PATH.'template/'.$style);
 }
 
-function build_user( $user_id, $use_cache, $filter_global_mode = false )
+function build_user( $user_id, $use_cache )
 {
   global $conf;
-
   $user['id'] = $user_id;
-  $user = array_merge( $user, getuserdata($user_id, $use_cache, $filter_global_mode) );
+  $user = array_merge( $user, getuserdata($user_id, $use_cache) );
   if ( $user['id'] == $conf['guest_id'])
   {
     $user['is_the_guest']=true;
@@ -167,7 +166,6 @@ function build_user( $user_id, $use_cache, $filter_global_mode = false )
   {
     $user['is_the_guest']=false;
   }
-
   // calculation of the number of picture to display per page
   $user['nb_image_page'] = $user['nb_image_line'] * $user['nb_line_page'];
 
@@ -198,7 +196,7 @@ function build_user( $user_id, $use_cache, $filter_global_mode = false )
  * @param boolean use_cache
  * @param array
  */
-function getuserdata($user_id, $use_cache, $filter_global_mode = false )
+function getuserdata($user_id, $use_cache)
 {
   global $conf;
 
@@ -267,21 +265,20 @@ SELECT ui.*, uc.*
 
   if ($use_cache)
   {
-    $userdata['filter_global_mode'] = $filter_global_mode;
-
     if (!isset($userdata['need_update'])
         or !is_bool($userdata['need_update'])
-        or $userdata['need_update'] == true
-        or $filter_global_mode  // not optimize condition RubTag
-       )
+        or $userdata['need_update'] == true)
     {
       $userdata['forbidden_categories'] =
         calculate_permissions($userdata['id'], $userdata['status']);
 
-      update_user_cache_categories($userdata);
+      update_user_cache_categories($userdata['id'], $userdata['forbidden_categories']);
 
       // Set need update are done
-      $userdata['need_update'] = $userdata['filter_global_mode']; // for draft always update RubTag
+      $userdata['need_update'] = false;
+
+      // Indicate update done
+      $userdata['need_update_done'] = true;
 
       $query = '
 SELECT COUNT(DISTINCT(image_id)) as total
@@ -306,8 +303,10 @@ INSERT INTO '.USER_CACHE_TABLE.'
 ;';
       pwg_query($query);
     }
-
+    else
     {
+      // Indicate update not done
+      $userdata['need_update_done'] = false;
     }
   }
 
@@ -328,6 +327,8 @@ function check_user_favorites()
     return;
   }
 
+  // $filter['visible_categories'] and $filter['visible_images']
+  // must be not used because filter <> restriction
   // retrieving images allowed : belonging to at least one authorized
   // category
   $query = '
@@ -335,7 +336,14 @@ SELECT DISTINCT f.image_id
   FROM '.FAVORITES_TABLE.' AS f INNER JOIN '.IMAGE_CATEGORY_TABLE.' AS ic
     ON f.image_id = ic.image_id
   WHERE f.user_id = '.$user['id'].'
-    AND ic.category_id NOT IN ('.$user['forbidden_categories'].')
+'.get_sql_condition_FandF
+  (
+    array
+      (
+        'forbidden_categories' => 'ic.category_id',
+      ),
+    'AND'
+  ).'
 ;';
   $result = pwg_query($query);
   $authorizeds = array();
@@ -454,7 +462,7 @@ SELECT id
 }
 
 /**
- * compute data of categories branches
+ * compute data of categories branches (one branch only)
  */
 function compute_branch_cat_data(&$cats, &$list_cat_id, &$level, &$ref_level)
 {
@@ -496,60 +504,82 @@ function compute_branch_cat_data(&$cats, &$list_cat_id, &$level, &$ref_level)
 }
 
 /**
- * update data of user_cache_categories
- *
- * @param array userdata
- * @return null
+ * compute data of categories branches
  */
-function update_user_cache_categories(&$userdata)
+function compute_categories_data(&$cats)
 {
-  // delete user cache
-  $query = '
-DELETE FROM '.USER_CACHE_CATEGORIES_TABLE.'
-  WHERE user_id = '.$userdata['id'].'
-;';
-  pwg_query($query);
+  $ref_level = 0;
+  $level = 0;
+  $list_cat_id = array();
 
-  /*$query = '
-SELECT id cat_id, date_last max_date_last, nb_images count_images, global_rank
-  FROM '.CATEGORIES_TABLE;
-  if ($userdata['forbidden_categories'] != '')
+  foreach ($cats as $id => $category)
   {
-    $query.= '
-    WHERE id NOT IN ('.$userdata['forbidden_categories'].')';
+    // Compute
+    $level = substr_count($category['global_rank'], '.') + 1;
+    if ($level > $ref_level)
+    {
+      array_push($list_cat_id, $id);
+    }
+    else
+    {
+      compute_branch_cat_data($cats, $list_cat_id, $level, $ref_level);
+      array_push($list_cat_id, $id);
+    }
+    $ref_level = $level;
   }
-  $query.= ';';*/
 
+  $level = 1;
+  compute_branch_cat_data($cats, $list_cat_id, $level, $ref_level);
+}
 
+/**
+ * get computed array of categories
+ *
+ * @param int user_id
+ * @param list user_forbidden_categories
+ * @param bool filter_enabled
+ * @param int recent_period
+ * @return array
+ */
+function get_computed_categories($user_id, $user_forbidden_categories, $filter_enabled, $recent_period = 0)
+{
   $query = '
-SELECT c.id cat_id, date_last max_date_last, nb_images count_images, global_rank';
+SELECT
+  c.id cat_id,
+  date_last max_date_last,
+  nb_images count_images,
+  global_rank';
 
-  if (!$userdata['filter_global_mode'])
+  if (!$filter_enabled)
   {
     $query.= '
-  FROM '.CATEGORIES_TABLE.' as C';
+FROM '.CATEGORIES_TABLE.' as c';
   }
   else
   {
     // Count by date_available to avoid count null
-    $query.= ', count(date_available) filtered_count_images, max(date_available) max_date_available
-  FROM '.CATEGORIES_TABLE.' as C
-    LEFT JOIN '.IMAGE_CATEGORY_TABLE.' AS ic
-    ON ic.category_id = c.id LEFT JOIN '.IMAGES_TABLE.' AS i 
-    ON ic.image_id = i.id AND i.date_available  > SUBDATE(
-      CURRENT_DATE,INTERVAL '.$userdata['recent_period'].' DAY)';
+    $query.= ',
+  count(date_available) filtered_count_images,
+  max(date_available) max_date_available
+FROM '.CATEGORIES_TABLE.' as c
+    LEFT JOIN '.IMAGE_CATEGORY_TABLE.' AS ic ON ic.category_id = c.id
+    LEFT JOIN '.IMAGES_TABLE.' AS i
+      ON ic.image_id = i.id AND 
+          i.date_available > SUBDATE(CURRENT_DATE,INTERVAL '.$recent_period.' DAY)';
   }
 
-  if ($userdata['forbidden_categories'] != '')
+  if ($user_forbidden_categories != '')
   {
     $query.= '
-    WHERE C.id NOT IN ('.$userdata['forbidden_categories'].')';
+WHERE
+  c.id NOT IN ('.$user_forbidden_categories.')';
   }
 
-  if ($userdata['filter_global_mode'])
+  if ($filter_enabled)
   {
     $query.= '
-  GROUP BY c.id';
+GROUP BY
+  c.id';
   }
   $query.= ';';
 
@@ -558,10 +588,11 @@ SELECT c.id cat_id, date_last max_date_last, nb_images count_images, global_rank
   $cats = array();
   while ($row = mysql_fetch_assoc($result))
   {
-    $row['user_id'] = $userdata['id'];
+    $row['user_id'] = $user_id;
     $row['count_categories'] = 0;
-    if ($userdata['filter_global_mode'])
+    if ($filter_enabled)
     {
+      $row['nb_images'] = $row['filtered_count_images'];
       $row['count_images'] = $row['filtered_count_images'];
       $row['max_date_last'] = $row['max_date_available'];
     }
@@ -569,50 +600,52 @@ SELECT c.id cat_id, date_last max_date_last, nb_images count_images, global_rank
   }
   usort($cats, 'global_rank_compare');
 
-  $ref_level = 0;
-  $level = 0;
-  $list_cat_id = array();
-
-  foreach ($cats as $id => $category)
-  {
-    // Compute
-    $level = substr_count($category['global_rank'], '.') + 1;
-    if ($level > $ref_level)
-    {
-      array_push($list_cat_id, $id);
-    }
-    else
-    {
-      compute_branch_cat_data($cats, $list_cat_id, $level, $ref_level);
-      array_push($list_cat_id, $id);
-    }
-    $ref_level = $level;
-  }
-
-  $level = 1;
-  compute_branch_cat_data($cats, $list_cat_id, $level, $ref_level);
+  compute_categories_data($cats);
 
-  if ($userdata['filter_global_mode'])
+  if ($filter_enabled)
   {
-    $forbidden_cats = array();
-    $forbidden_cats = explode(',', $userdata['forbidden_categories']);
     $cat_tmp = $cats;
     $cats = array();
   
-    foreach ($cat_tmp as $cat_id => $category)
+    foreach ($cat_tmp as $category)
     {
-      if (empty($category['max_date_last']))
-      {
-        array_push($forbidden_cats, $category['cat_id']);
-      }
-      else
+      if (!empty($category['max_date_last']))
       {
-        array_push($cats, $category);
+        // Re-init counters
+        $category['count_categories'] = 0;
+        $category['nb_images'] = $category['filtered_count_images'];
+        $category['count_images'] = $category['filtered_count_images'];
+        // Keep category
+        $cats[$category['cat_id']] = $category;
+        
       }
     }
-    $userdata['forbidden_categories'] = implode(',', array_unique($forbidden_cats));
+    // Compute a second time
+    compute_categories_data($cats);
   }
 
+  return $cats;
+}
+
+/**
+ * update data of user_cache_categories
+ *
+ * @param int user_id
+ * @param list user_forbidden_categories
+ * @param bool filter_enabled
+ * @return null
+ */
+function update_user_cache_categories($user_id, $user_forbidden_categories)
+{
+  // delete user cache
+  $query = '
+DELETE FROM '.USER_CACHE_CATEGORIES_TABLE.'
+  WHERE user_id = '.$user_id.'
+;';
+  pwg_query($query);
+
+  $cats = get_computed_categories($user_id, $user_forbidden_categories, false);
+
   include_once(PHPWG_ROOT_PATH.'admin/include/functions.php');
   mass_inserts
   (
@@ -1014,4 +1047,82 @@ function get_email_address_as_display_text($email_address)
   }
 }
 
+/*
+ * Compute sql where condition with restrict and filter data
+ *
+ * FandF: Forbidden and Filters
+ *
+ * @param $condition_fields array: 
+ *            keys are condition to aply and 
+ *            values are sql field to use
+ *            array('forbidden_categories' => 'ic.category_id')
+ *        $prefix_condition string:
+ *            this value are concatenated if sql is not empty
+ *        $force_one_condition:
+ *            if there are not condition , use this condition "1 = 1"
+ *
+ * @return string sql where/conditions
+ */
+function get_sql_condition_FandF($condition_fields, $prefix_condition = null, $force_one_condition = false)
+{
+  global $user, $filter;
+
+  $sql_list = array();
+
+  foreach ($condition_fields as $condition => $field_name)
+  {
+    switch($condition)
+    {
+      case 'forbidden_categories':
+        if (!empty($user['forbidden_categories']))
+        {
+          $sql_list[] = $field_name.' NOT IN ('.$user['forbidden_categories'].')';
+        }
+        break;
+
+      case 'visible_categories':
+        if (!empty($filter['visible_categories']))
+        {
+          $sql_list[] = $field_name.' IN ('.$filter['visible_categories'].')';
+        }
+        break;
+
+      case 'visible_images':
+        if (!empty($filter['visible_images']))
+        {
+          $sql_list[] = $field_name.' IN ('.$filter['visible_images'].')';
+        }
+        break;
+
+      default:
+        die('Unknow condition');
+        break;
+
+    }
+  }
+
+  if (count($sql_list) > 0)
+  {
+    $sql = '('.implode(' AND ', $sql_list).')';
+  }
+  else
+  {
+    if ($force_one_condition)
+    {
+      $sql = '1 = 1';
+    }
+    else
+    {
+      $sql = '';
+    }
+  }
+
+  if (isset($prefix_condition) and !empty($sql))
+  {
+    $sql = $prefix_condition.' '.$sql;
+  }
+
+  return $sql;
+}
+
 ?>