[Sommaire] [Imprimer]

   1  <?php
   2  /* -*- tab-width: 4; indent-tabs-mode: nil; c-basic-offset: 4 -*- */
   3  /*
   4  # ***** BEGIN LICENSE BLOCK *****
   5  # This file is part of Plume CMS, a website management application.
   6  # Copyright (C) 2001-2005 Loic d'Anterroches and contributors.
   7  #
   8  # Plume CMS is free software; you can redistribute it and/or modify
   9  # it under the terms of the GNU General Public License as published by
  10  # the Free Software Foundation; either version 2 of the License, or
  11  # (at your option) any later version.
  12  #
  13  # Plume CMS is distributed in the hope that it will be useful,
  14  # but WITHOUT ANY WARRANTY; without even the implied warranty of
  15  # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  16  # GNU 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., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301  USA
  21  #
  22  # ***** END LICENSE BLOCK ***** */
  23  
  24  require_once dirname(__FILE__).'/lib.sqlutils.php';
  25  
  26  /**
  27   * This class regroups a set of static methods used to create
  28   * the SQL queries used in the manager to select a set of
  29   * resources. 
  30   * All the classes needing SQL queries against the main tables should
  31   * use these methods. If needed the queries can be refactored. The goal
  32   * is to be able to provide some abstraction for a possible port to
  33   * another RDBMS.
  34   */
  35  class SQL
  36  {
  37      /**
  38       * Get a user by its user id.
  39       *
  40       * @param int User id
  41       * @return string Ready to use SQL
  42       */
  43      function getUser($id)
  44      {
  45          $con =& pxDBConnect();
  46          return 'SELECT * FROM '.$con->pfx.'users 
  47                  WHERE user_id=\''.$con->escapeStr($id).'\'';
  48      }
  49  
  50      /**
  51       * Get website levels for a user
  52       *
  53       * @param int User id
  54       * @return string Ready to use SQL
  55       */
  56      function getWebsiteLevels($user_id)
  57      {
  58          $con =& pxDBConnect();
  59          return 'SELECT * FROM '.$con->pfx.'grants
  60                  LEFT JOIN '.$con->pfx.'websites 
  61                    ON '.$con->pfx.'websites.website_id='
  62                    .$con->pfx.'grants.website_id
  63                  WHERE user_id LIKE \''.$con->esc($user_id).'\'';
  64      }
  65  
  66      /**
  67       * Get a website by its id.
  68       *
  69       * @param string Website id
  70       * @return string Ready to use SQL
  71       */
  72      function getWebsite($id)
  73      {
  74          $con =& pxDBConnect();
  75          return 'SELECT * FROM '.$con->pfx.'websites
  76                  WHERE website_id=\''.$con->escapeStr($id).'\'';
  77      }
  78  
  79  
  80      /**
  81       * Get a resource by its identifier.
  82       * If the identifier is only composed of digits it is considered as
  83       * being the 'resource_id' else the 'identifier'.
  84       * If the category id is empty, the left join is using the main
  85       * category for the link.
  86       *
  87       * @param mixed Identifier or resource id
  88       * @param int Category id ('')
  89       * @param string Website id ('')
  90       * @return string Ready to use SQL
  91       */
  92      function getResourceByIdentifier($id, $catid='', $website='')
  93      {
  94          $con =& pxDBConnect();
  95          if (empty($catid)) {
  96              $r = SQL::getResources();
  97          } else {
  98              $r = SQL::getResources(false);
  99              $r .= "\n".'WHERE '.$con->pfx.'categories.category_id=\''
 100                  .$con->esc($catid).'\'';
 101          }
 102          if (preg_match('/^[0-9]+$/', $id)) {
 103              $r .= ' AND '.$con->pfx.'resources.resource_id=\''
 104                  .$con->escapeStr($id).'\'';
 105          } else {
 106              $r .= ' AND '.$con->pfx.'resources.identifier=\''
 107                  .$con->escapeStr($id).'\'';
 108          }
 109          if ($website != '') {
 110              $r .= ' AND '.$con->pfx.'resources.website_id=\''
 111                  .$con->escapeStr($website).'\'';
 112          }
 113          return $r;
 114      }
 115  
 116      /**
 117       * Get a resource by its path. 
 118       * The path do not include the category path.
 119       *
 120       * @param string Path
 121       * @param string Website id - all the websites if not provided ('')
 122       * @return string Ready to use SQL
 123       */
 124      function getResourceByPath($path, $website='')
 125      {
 126          $con =& pxDBConnect();
 127          $r = SQL::getResources().' AND '
 128              .$con->pfx.'resources.path=\''.$con->esc($path).'\'';
 129          if (!empty($website)) {
 130              $r .= ' AND '.$con->pfx.'resources.website_id=\''
 131                  .$con->esc($website).'\'';
 132          }
 133          return $r;
 134      }
 135  
 136      /**
 137       * Get comment by id.
 138       * If a resource id is given, the comment must be associated to the given
 139       * resource.
 140       *
 141       * @param int Id of the comment.
 142       * @param int Id of the resource ('')
 143       * @return string Ready to use SQL
 144       */
 145      function getCommentById($id, $resource_id='')
 146      {
 147          $con =& pxDBConnect();
 148          $r = 'SELECT * FROM '
 149              .$con->pfx.'comments '
 150              .'LEFT JOIN '.$con->pfx.'resources '
 151              .'ON '.$con->pfx.'resources.resource_id='
 152              .$con->pfx.'comments.resource_id '
 153              .'WHERE comment_id=\''.$con->esc($id).'\'';
 154          if (!empty($resource_id)) {
 155              $r .= ' AND '.$con->pfx.'comments.resource_id=\''
 156                  .$con->esc($resource_id).'\'';
 157          }
 158          return $r;
 159      }
 160  
 161      /**
 162       * Get comments for a given website.
 163       * Left join on the associated resources, if no website id is given, all
 164       * the comments are returned.
 165       *
 166       * @param string Website id ('')
 167       * @param string Resource id ('')
 168       * @param int Status ('')
 169       * @param string Modification date order of the comments ('ASC')
 170       * @param int Limit (0)
 171       * @return string Ready to use SQL
 172       */
 173      function getComments($website_id='', $resource_id='', 
 174                           $status='', $order='ASC', $limit=0)
 175      {
 176          $con =& pxDBConnect();
 177          $r = 'SELECT * FROM '.$con->pfx.'comments '
 178              .'LEFT JOIN '.$con->pfx.'resources '
 179              .'ON '.$con->pfx.'resources.resource_id='
 180              .$con->pfx.'comments.resource_id';
 181          if (!empty($website_id) or !empty($resource_id) or !empty($status)) {
 182              $r .= ' WHERE ';
 183          }
 184          $cond = array();
 185          if (!empty($website_id)) {
 186              $cond[] = $con->pfx.'resources.website_id=\''
 187                  .$con->esc($website_id).'\' ';
 188          }
 189          if (!empty($resource_id)) {
 190              $cond[] = $con->pfx.'resources.resource_id=\''
 191                  .$con->esc($resource_id).'\'';
 192          }
 193          if (!empty($status)) {
 194              $cond[] = $con->pfx.'comments.comment_status=\''
 195                  .$con->esc($status).'\'';
 196          }
 197          $cond_string = join(' AND ', $cond);
 198  
 199          $r .= $cond_string.'  ORDER BY '
 200              .$con->pfx.'comments.comment_update '.$order;
 201          if ($limit > 0) {
 202              $r .= ' LIMIT '.$limit;
 203          }
 204          return $r;
 205      }
 206  
 207      /**
 208       * Get a category by path.
 209       *
 210       * @param string Path
 211       * @param string Website id - all the websites if not provided ('')
 212       * @return string Ready to use SQL
 213       */
 214      function getCategoryByPath($path, $website='')
 215      {
 216          $con =& pxDBConnect();
 217          $r = 'SELECT * FROM '.$con->pfx.'categories
 218             LEFT JOIN '.$con->pfx.'websites 
 219               ON '.$con->pfx.'websites.website_id='
 220              .$con->pfx.'categories.website_id
 221                WHERE '.$con->pfx.'categories.category_path=\''
 222              .$con->esc($path).'\'';
 223          if (!empty($website)) {
 224              $r .= ' AND '.$con->pfx.'categories.website_id=\''
 225                  .$con->esc($website).'\'';
 226          }
 227          return $r;
 228      }
 229  
 230      /**
 231       * Get a category by its id.
 232       *
 233       * @param int Id 
 234       * @return string Ready to use SQL
 235       */
 236      function getCategoryById($id)
 237      {
 238          $con =& pxDBConnect();
 239          return 'SELECT * FROM '.$con->pfx.'categories
 240             LEFT JOIN '.$con->pfx.'websites 
 241               ON '.$con->pfx.'websites.website_id='
 242              .$con->pfx.'categories.website_id
 243                WHERE '.$con->pfx.'categories.category_id=\''
 244              .$con->esc($id).'\'';
 245      }
 246  
 247      /**
 248       * Get an online resource in a category.
 249       *
 250       * If first parameter is not an integer, it is considered as the path.
 251       *
 252       * @param mixed Resource path or id
 253       * @param string Category path 
 254       * @param string Website id
 255       * @return string Ready to use SQL
 256       */
 257      function getOnlineResourceInCat($res, $cat, $website)
 258      {
 259          $con =& pxDBConnect();
 260          $r = SQL::getResources(false);
 261          $r .= ' WHERE '
 262              .$con->pfx.'resources.website_id=\''.$con->esc($website).'\'
 263              AND category_path LIKE \''.$con->esc($cat).'\'
 264              AND '.$con->pfx.'resources.publicationdate <= '.date::stamp().'
 265              AND '.$con->pfx.'resources.enddate >= '.date::stamp().'
 266              AND '.$con->pfx.'resources.status=1';
 267          if (preg_match('/^[0-9]+$/', $res)) {
 268              $r .= "\n".'AND '.$con->pfx.'resources.resource_id=\''
 269                  .$con->esc($res).'\'';
 270          } else {
 271              $r .= "\n".'AND '.$con->pfx.'resources.path LIKE \''
 272                  .$con->esc($res).'\'';
 273          }
 274          return $r;
 275      }
 276  
 277  
 278  
 279      /**
 280       * Get resources.
 281       *
 282       * Get all the basic data, if the category returned is the main category.
 283       * a WHERE clause is already open.
 284       * Can be used to then further limit by website, id or user.
 285       *
 286       * @param bool Associated to the main category (true)
 287       * @return string Ready to use SQL
 288       */
 289      function getResources($main_category=true)
 290      {
 291          $con =& pxDBConnect();
 292          $sql = 'SELECT * FROM '.$con->pfx.'resources
 293             LEFT JOIN '.$con->pfx.'categoryasso 
 294               ON '.$con->pfx.'categoryasso.identifier='
 295               .$con->pfx.'resources.identifier
 296             LEFT JOIN '.$con->pfx.'categories 
 297               ON '.$con->pfx.'categoryasso.category_id='
 298               .$con->pfx.'categories.category_id
 299             LEFT JOIN '.$con->pfx.'websites 
 300               ON '.$con->pfx.'websites.website_id='
 301              .$con->pfx.'resources.website_id
 302             LEFT JOIN '.$con->pfx.'subtypes 
 303               ON '.$con->pfx.'subtypes.subtype_id='
 304              .$con->pfx.'resources.subtype_id';
 305  
 306          if ($main_category == true) {
 307              include_once dirname(__FILE__).'/class.resource.php';
 308              $sql .= "\n".'WHERE categoryasso_type=\''
 309                  .PX_RESOURCE_CATEGORY_MAIN.'\'';
 310          }
 311          return $sql;
 312      }
 313  
 314      /**
 315       * Get resources in a category.
 316       *
 317       * @param id Category id
 318       * @return string Ready to use SQL
 319       */
 320      function getResourcesInCat($id)
 321      {
 322          $con =& pxDBConnect();
 323          return 'SELECT * FROM '.$con->pfx.'resources
 324             LEFT JOIN '.$con->pfx.'categoryasso 
 325               ON '.$con->pfx.'categoryasso.identifier='
 326               .$con->pfx.'resources.identifier
 327             LEFT JOIN '.$con->pfx.'categories 
 328               ON '.$con->pfx.'categoryasso.category_id='
 329               .$con->pfx.'categories.category_id
 330             LEFT JOIN '.$con->pfx.'websites 
 331               ON '.$con->pfx.'websites.website_id='
 332              .$con->pfx.'resources.website_id
 333             WHERE '
 334              .$con->pfx.'categories.category_id=\''.$con->esc($id).'\'';
 335      }
 336  
 337      /**
 338       * Get the last resources.
 339       *
 340       * @param string Website id ('')
 341       * @param string Type of resource ('')
 342       * @param int Category id ('')
 343       * @param int Maximum number of results ('')
 344       * @return string Ready to use SQL
 345       */
 346      function getLastResources($website='', $type='', $category='', $limit='')
 347      {
 348          $con =& pxDBConnect();
 349          if ($category == '') {
 350              $main_category = true;
 351          } else {
 352              $main_category = false;
 353          }
 354          $sql = SQL::getResources($main_category);
 355          if ($main_category == false) {
 356              $sql .= "\n". 'WHERE '
 357                  .$con->pfx.'categories.category_id=\''
 358                  .$con->esc($category).'\'';
 359          }
 360          if ($website != '') {
 361              $sql .= "\n".'AND '.$con->pfx.'resources.website_id=\''
 362                  .$con->esc($website).'\'';
 363          }
 364          if ($type != '') {
 365              $sql .= "\n".'AND '.$con->pfx.'resources.type_id=\''
 366                  .$con->esc($type).'\'';
 367          }
 368          $sql .= ' ORDER BY '.$con->pfx.'resources.modifdate DESC';
 369          if ($limit != '') {
 370              $limit = (preg_match('/^[0-9]+$/',$limit)) ? '0,'.$limit : $limit;
 371              $sql .= ' LIMIT '.$con->esc($limit);
 372          }
 373          return $sql;
 374      }
 375  
 376  
 377  }
 378  
 379  ?>