[Summary view] [Print] [Text view]

   1  <?php
   2  /*
   3   * This file is part of Zoph.
   4   *
   5   * Zoph is free software; you can redistribute it and/or modify
   6   * it under the terms of the GNU General Public License as published by
   7   * the Free Software Foundation; either version 2 of the License, or
   8   * (at your option) any later version.
   9   * 
  10   * Zoph is distributed in the hope that it will be useful,
  11   * but WITHOUT ANY WARRANTY; without even the implied warranty of
  12   * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  13   * GNU General Public License for more details.
  14   * You should have received a copy of the GNU General Public License
  15   * along with Zoph; if not, write to the Free Software
  16   * Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301  USA
  17   */
  18  function get_photos($vars, $offset, $rows, &$thumbnails, $user = null) {
  19  
  20      $good_ops = array ( "=", "!=", "less than", "more than", ">", ">=", "<", "<=", "like", "not like", "is in photo", "is not in photo" );
  21  
  22      $good_conj = array ( "and", "or" );
  23  
  24      $good_fields= array ( "location_id", "rating", "photographer_id", "date", "time", "timestamp", "name", "path", "title", "view", "description", "width", "height", "size", "aperture", "camera_make", "camera_model", "compression", "exposure", "flash_used", "focal_length", "iso_equiv", "metering_mode" );
  25  
  26      $select = "distinct ph.photo_id, ph.name, ph.path, ph.width, ph.height";
  27  
  28      if (MAX_THUMB_DESC && $user && $user->prefs->get("desc_thumbnails")) {
  29          $select .= ", ph.description";
  30      }
  31  
  32      $from_clause=DB_PREFIX . "photos as ph";
  33  
  34      if ($user && !$user->is_admin()) {
  35  //        $from["pa"] = "photo_albums";
  36  //        $from["ap"] = "album_permissions";
  37          $from_clause .= " JOIN " . DB_PREFIX . "photo_albums AS pa " .
  38              "ON ph.photo_id = pa.photo_id " .
  39              "JOIN " . DB_PREFIX . "album_permissions AS ap " .
  40              "ON pa.album_id = ap.album_id ";
  41  
  42          $where =
  43               " ap.user_id = '" . escape_string($user->get("user_id")) . "'" .
  44               " AND (ap.access_level >= ph.level)";
  45      }
  46      else {
  47          $where = "";
  48      }
  49  
  50      global $DEFAULT_ORDER;
  51      $ord = $vars["_order"];
  52      if (!$ord) { $ord = $DEFAULT_ORDER; }
  53  
  54      global $DEFAULT_DIRECTION;
  55      $dir = $vars["_dir"];
  56      if (!$dir) { $dir = $DEFAULT_DIRECTION; }
  57  
  58      $order = "ph." . $ord . " $dir";
  59      if ($ord == "date") { $order .= ", ph.time $dir"; }
  60      $order .= ", ph.photo_id $dir";
  61  
  62      while (list($key, $val) = each($vars)) {
  63  
  64          if (empty($key) || empty($val))  { continue; }
  65          if ($key[0] == '_')              { continue; }
  66          if (strpos(" $key", "PHP") == 1) { continue; }
  67  
  68          // handle refinements of searches
  69          $suffix = "";
  70          $hashPos = strrpos($key, "#");
  71          if ($hashPos > 0) { // don't care about first position
  72              $suffix = substr($key, $hashPos);
  73              $key = substr($key, 0, $hashPos);
  74          }
  75  
  76          //echo "key = $key<br>";
  77          //echo "suffix = $suffix<br>";
  78  
  79          $conj = $vars["_" . $key . $suffix . "-conj"];
  80          if (!$conj) { $conj = "and"; }
  81          if (!in_array($conj, $good_conj)) 
  82              { die ("Illegal conjunction: " . $conj); }
  83  
  84          $op = $vars["_" . $key . $suffix . "-op"];
  85          if (!$op) { $op = "="; }
  86          if (!in_array($op, $good_ops)) 
  87              { die ("Illegal operator: " . $op); }
  88  
  89          if ($val == "null") {
  90              if ($op == "=") { $op = "is"; }
  91              else if ($op = "!=") { $op = "is not"; }
  92          }
  93  
  94          if ($key == "person" || $key == "photographer") {
  95              // val could be "last_name", "last_name,first_name" or ",first_name"
  96  
  97              list($last_name, $first_name) = explode(',', $val);
  98  
  99              $key .= "_id";
 100              $val = "";
 101  
 102              $people = get_person_by_name($first_name, $last_name);
 103              if ($people && count($people) > 0) {
 104  
 105                  foreach ($people as $person) {
 106                      if ($val) { $val .= ","; }
 107                      $val .= $person->get("person_id");
 108                  }
 109              }
 110              else {
 111                  // the person did not exist, no photos should be found
 112                  $val = "-1";
 113              }
 114          }
 115  
 116          //echo "<p>key = '$key'; op = '$op', value = '$val'</p>\n";
 117  
 118          if ($key == "album_id") {
 119              $pa = "pa" . substr($suffix, 1);
 120              if ($op == "=") {
 121                  if ($where) { $where .= " $conj "; }
 122                  // If the user is not an admin, the albums table
 123                  // is already in the join
 124                  if ($user->is_admin() || $pa != "pa") {
 125                      $from["$pa"] = "photo_albums";
 126                  }
 127  
 128                  // the regexp matches a list of numbers, separated by comma's.
 129                  // "1" matches, "1," not, "1,2" matches "1,333" matches
 130                  // "1, a" not, etc.
 131                  if (!preg_match("/^([0-9]+)+(,([0-9]+))*$/", $val)) { die("$key must be numeric"); }
 132  
 133                  $op = "in";
 134                  $where .=
 135                      "($pa}.album_id $op (" . escape_string($val) . "))";
 136  
 137              }
 138              else { // assume "not in"
 139                  // a simple join won't work for the "not in" case
 140                  $excluded_albums["$pa"] = $val;
 141                  $excluded_albums["$pa}-conj"] = $conj;
 142              }
 143          }
 144          else if ($key == "category_id") {
 145              $pc = "pc" . substr($suffix, 1);
 146              if ($op == "=") {
 147                  if ($where) { $where .= " $conj "; }
 148  
 149                  $from["$pc"] = "photo_categories";
 150                  
 151                  if (!preg_match("/^([0-9]+)+(,([0-9]+))*$/", $val)) { die("$key must be numeric"); }
 152  
 153                  $op = "in";
 154                  $where .=
 155                      "($pc}.category_id $op (" . escape_string($val) . ")" .
 156                      " and $pc}.photo_id = ph.photo_id)";
 157              }
 158              else { // assume "not in"
 159                  // a simple join won't work for the "not in" case
 160                  $excluded_categories["$pc"] = $val;
 161                  $excluded_categories["$pc}-conj"] = $conj;
 162              }
 163          }
 164          else if ($key == "location_id") {
 165                  if ($where) { $where .= " $conj "; }
 166                  if(preg_match("/[a-zA-Z]+/", $val)) { die("No letters allowed in $key"); }
 167  
 168                  if ($op == "=") {
 169                      $op = "in";
 170                      $add = "";
 171                  } else {
 172                      $op = "not in";
 173                      $add = " or ph.location_id is null";
 174                  }
 175                  $where .=
 176                      "(ph.location_id $op (" . escape_string($val) . ")$add)";
 177              }
 178          else if ($key == "person_id") {
 179              $ppl = "ppl" . substr($suffix, 1);
 180              if ($op == "=") {
 181                  if ($where) { $where .= " $conj "; }
 182  
 183                  $from["$ppl"] = "photo_people";
 184  
 185                  $op = "in";
 186                  if (!is_numeric($val)) { die("$key must be numeric"); }
 187                  $where .=
 188                      "($ppl}.person_id $op (" . escape_string($val) . ")" .
 189                      " and $ppl}.photo_id = ph.photo_id)";
 190              }
 191              else {
 192                  // a simple join won't work for the "not in" case
 193                  $excluded_people["$ppl"] = $val;
 194                  $excluded_people["$ppl}-conj"] = $conj;
 195              }
 196          }
 197          else { // any other field
 198  
 199              if (strncasecmp($key, "field", 5) == 0) {
 200                  $key = $vars["_" . $key . $suffix];
 201              }
 202              if (!in_array($key, $good_fields))
 203                  { die ("Illegal field: " . $key); }
 204                
 205              $key = "ph.$key";
 206  
 207              $val = escape_string($val);
 208              if ($op == "like" or $op == "not like") {
 209                  $val = "'%" . strtolower($val) . "%'";
 210                  $key = "lower($key)";
 211              }
 212              else if ($val != "null") {
 213                  if (!is_numeric($val)) {
 214                      $val = "'" . escape_string($val) . "'";
 215                  }
 216              }
 217  
 218              if ($where) { $where .= " $conj "; }
 219              $where .= "(" . escape_string($key) . " " . $op . " " . $val;
 220              
 221              if ($op == "!=" ) {
 222                  $where .= " or " . escape_string($key) . " is null)";
 223              } else {
 224                  $where .= ")";
 225              }
 226  
 227          }
 228  
 229      }
 230  
 231      $from_clause .= generate_from_clause($from);
 232  
 233      if ($excluded_albums) {
 234          $where .= generate_excluded_albums_clause(
 235              $excluded_albums, $from_clause, $where);
 236      }
 237  
 238      if ($excluded_categories) {
 239          $where .= generate_excluded_categories_clause(
 240              $excluded_categories, $from_clause, $where);
 241      }
 242  
 243      if ($excluded_people) {
 244          $where .= generate_excluded_people_clause(
 245              $excluded_people, $from_clause, $where);
 246      }
 247      
 248      if ($where) { $where = "where $where"; }
 249  
 250      $num_photos = 0;
 251  
 252      // do this count separately since the select uses limit
 253      $query = "select count(distinct ph.photo_id) from $from_clause $where";
 254          //echo $query . "\n"; //DEBUG
 255      $num_photos = get_count_from_query($query);
 256  
 257      if ($num_photos > 0) {
 258  
 259          if ($vars["_random"] && $num_photos > 1) {
 260              // get one random result
 261              mt_srand((double) microtime() * 1000000);
 262              $offset = mt_rand(0, $num_photos - 1);
 263              $rows = 1;
 264              $num_photos = 1;
 265  
 266              // don't bother with order
 267              $query =
 268                  "select $select from $from_clause $where limit $offset, $rows";
 269          }
 270          else {
 271              $query =
 272                  "select $select from $from_clause $where order by $order " .
 273                  "limit $offset, $rows";
 274          }
 275          //echo $query . "\n"; //DEBUG
 276  
 277          $thumbnails = get_records_from_query("photo", $query);
 278  
 279      }
 280  
 281      return $num_photos;
 282  
 283  }
 284  
 285  function generate_from_clause($from_array) {
 286      $fromClause = "";
 287      $joinClause = "";
 288      if ($from_array) {
 289          while (list($abbrev, $table) = each($from_array)) {
 290  //            if ($fromClause) {
 291                  $fromClause .= " JOIN ";
 292                  $joinClause = " on $abbrev}.photo_id = ph.photo_id";
 293  //            }
 294                  
 295              $fromClause .= DB_PREFIX . "$table as $abbrev" . $joinClause;
 296          }
 297      }
 298      return $fromClause;
 299  }
 300  
 301  /*
 302    The generate_excluded methods below simulate subselects since MySQL
 303    doesn not support them.  These are kind of ugly but the problem is
 304    that for "not in" or "!=" constraints on albums, categories or people,
 305    a simple joining will not work (as it does in the non-negated case).
 306    This is because when a photo is in multiple albums or cats, or there
 307    are multiple people in the photo, the join will match one of the
 308    other rows.  I hope there is a better way to do this.
 309  */
 310  
 311  function generate_excluded_albums_clause($excluded_albums, $from, $where) {
 312  
 313      $album_from = $from;
 314      $album_constraints = "";
 315  
 316      while (list($pa, $album_ids) = each($excluded_albums)) {
 317          if (strpos($pa, "-conj")) {continue;}
 318          if ($album_from) { $album_from .= ", "; }
 319          $album_from .= DB_PREFIX . "photo_albums as $pa";
 320          $photo_id_query =
 321              "select distinct $pa}.photo_id from $album_from " .
 322              "where (ph.photo_id = $pa}.photo_id and $pa}.album_id in (" .
 323              escape_string($album_ids) . "))";
 324  
 325          if ($where) {
 326              $photo_id_query .= " and $where";
 327          }
 328  
 329          //echo $photo_id_query . "\n"; //DEBUG
 330          $ids = implode(',', get_records_from_query(null, $photo_id_query));
 331  
 332          if ($ids) {
 333              if ($album_constraints || $where) {
 334                  $album_constraints .= " " . $excluded_albums["$pa}-conj"] . " ";
 335              }
 336              $album_constraints .= "(ph.photo_id not in ($ids))";
 337          }
 338  
 339      }
 340  
 341      return $album_constraints;
 342  }
 343  
 344  function generate_excluded_categories_clause($excluded_categories, $from, $where) {
 345  
 346      $cat_from = $from;
 347      $cat_constraints = "";
 348  
 349      while (list($pc, $cat_ids) = each($excluded_categories)) {
 350          if (strpos($pc, "-conj")) {continue;}
 351          if ($cat_from) { $cat_from .= ", "; }
 352          $cat_from .= DB_PREFIX . "photo_categories as $pc";
 353          $photo_id_query =
 354              "select distinct $pc}.photo_id from $cat_from " .
 355              "where (ph.photo_id = $pc}.photo_id and $pc}.category_id in (" .
 356              escape_string($cat_ids) . "))";
 357  
 358          if ($where) {
 359              $photo_id_query .= " and $where";
 360          }
 361  
 362          //echo $photo_id_query . "\n"; //DEBUG
 363          $ids = implode(',', get_records_from_query(null, $photo_id_query));
 364  
 365          if ($ids) {
 366              if ($cat_constraints || $where) {
 367                  $cat_constraints .= " " . $excluded_categories["$pc}-conj"] . " ";
 368              }
 369              $cat_constraints .= "(ph.photo_id not in ($ids))";
 370          }
 371  
 372      }
 373  
 374      return $cat_constraints;
 375  }
 376  
 377  function generate_excluded_people_clause($excluded_people, $from, $where) {
 378  
 379      $person_from = $from;
 380      $person_constraints = "";
 381  
 382      while (list($pp, $person_ids) = each($excluded_people)) {
 383          if (strpos($pp, "-conj")) {continue;}
 384          if ($person_from) { $person_from .= ", "; }
 385          $person_from .= DB_PREFIX . "photo_people as $pp";
 386          $photo_id_query =
 387              "select distinct $pp}.photo_id from $person_from " .
 388              "where (ph.photo_id = $pp}.photo_id and $pp}.person_id in (" .
 389              escape_string($person_ids) . "))";
 390  
 391          if ($where) {
 392              $photo_id_query .= " and $where";
 393          }
 394  
 395          //echo $photo_id_query . "\n"; //DEBUG
 396          $ids = implode(',', get_records_from_query(null, $photo_id_query));
 397  
 398          if ($ids) {
 399              if ($person_constraints || $where) {
 400                  $person_constraints .= " " . $excluded_people["$pp}-conj"] . " ";
 401              }
 402              $person_constraints .= "(ph.photo_id not in ($ids))";
 403          }
 404  
 405      }
 406  
 407      return $person_constraints;
 408  }
 409  
 410  ?>