b2evolution PHP Cross Reference Blogging Systems

Source: /inc/users/model/_userquery.class.php - 363 lines - 10474 bytes - Summary - Text - Print

Description: This file implements the UserQuery class. This file is part of the b2evolution/evocms project - {@link http://b2evolution.net/}. See also {@link http://sourceforge.net/projects/evocms/}.

   1  <?php
   2  /**

   3   * This file implements the UserQuery class.

   4   *

   5   * This file is part of the b2evolution/evocms project - {@link http://b2evolution.net/}.

   6   * See also {@link http://sourceforge.net/projects/evocms/}.

   7   *

   8   * @copyright (c)2003-2014 by Francois Planque - {@link http://fplanque.com/}.

   9  *

  10   * @license http://b2evolution.net/about/license.html GNU General Public License (GPL)

  11   *

  12   * {@internal Open Source relicensing agreement:

  13   * EVO FACTORY grants Francois PLANQUE the right to license

  14   * EVO FACTORY contributions to this file and the b2evolution project

  15   * under any OSI approved OSS license (http://www.opensource.org/licenses/).

  16   * }}

  17   *

  18   * @package evocore

  19   *

  20   * {@internal Below is a list of authors who have contributed to design/coding of this file: }}

  21   * @author asimo: Evo Factory / Attila Simo

  22   *

  23   * @version $Id: _userquery.class.php 13 2011-10-24 23:42:53Z fplanque $

  24   */
  25  if( !defined('EVO_MAIN_INIT') ) die( 'Please, do not access this page directly.' );
  26  
  27  load_class( '_core/model/db/_sql.class.php', 'SQL' );
  28  
  29  /**

  30   * UserQuery: help constructing queries on Users

  31   * @package evocore

  32   */
  33  class UserQuery extends SQL
  34  {
  35      /**

  36       * Fields of users table to search by keywords

  37       * 

  38       */
  39      var $keywords_fields = 'user_login, user_firstname, user_lastname, user_nickname, user_email';
  40  
  41      /**

  42       * Constructor.

  43       *

  44       * @param string Name of table in database

  45       * @param string Prefix of fields in the table

  46       * @param string Name of the ID field (including prefix)

  47       * @param array Query params

  48       */
  49  	function UserQuery( $dbtablename = 'T_users', $dbprefix = 'user_', $dbIDname = 'user_ID', $params = array() )
  50      {
  51          global $collections_Module;
  52  
  53          $this->dbtablename = $dbtablename;
  54          $this->dbprefix = $dbprefix;
  55          $this->dbIDname = $dbIDname;
  56  
  57          // Params to build query

  58          $params = array_merge( array(
  59                  'join_group'   => true,
  60                  'join_session' => false,
  61                  'join_country' => true,
  62                  'join_city'    => true,
  63                  'grouped'      => false,
  64              ), $params );
  65  
  66          $this->SELECT( 'user_ID, user_login, user_nickname, user_lastname, user_firstname, user_gender, user_source, user_created_datetime, user_profileupdate_date, user_lastseen_ts, user_level, user_status, user_avatar_file_ID, user_email, user_url, user_age_min, user_age_max, user_pass, user_locale, user_unsubscribe_key, user_reg_ctry_ID, user_ctry_ID, user_rgn_ID, user_subrg_ID, user_city_ID, user_grp_ID' );
  67          $this->SELECT_add( ', IF( user_avatar_file_ID IS NOT NULL, 1, 0 ) as has_picture' );
  68          $this->FROM( $this->dbtablename );
  69  
  70          if( $params['join_group'] )
  71          { // Join Group
  72              $this->SELECT_add( ', grp_ID, grp_name' );
  73              $this->FROM_add( 'LEFT JOIN T_groups ON user_grp_ID = grp_ID ' );
  74          }
  75  
  76          if( $params['join_session'] )
  77          { // Join Session
  78              $this->SELECT_add( ', MAX(T_sessions.sess_lastseen_ts) as sess_lastseen' );
  79              $this->FROM_add( 'LEFT JOIN T_sessions ON user_ID = sess_user_ID' );
  80          }
  81  
  82          if( $params['join_country'] )
  83          { // Join Country
  84              $this->SELECT_add( ', c.ctry_name, c.ctry_code, rc.ctry_name AS reg_ctry_name, rc.ctry_code AS reg_ctry_code' );
  85              $this->FROM_add( 'LEFT JOIN T_regional__country AS c ON user_ctry_ID = c.ctry_ID ' );
  86              $this->FROM_add( 'LEFT JOIN T_regional__country AS rc ON user_reg_ctry_ID = rc.ctry_ID ' );
  87          }
  88  
  89          if( $params['join_city'] )
  90          { // Join City
  91              $this->SELECT_add( ', city_name, city_postcode' );
  92              $this->FROM_add( 'LEFT JOIN T_regional__city ON user_city_ID = city_ID ' );
  93          }
  94  
  95          if( isset( $collections_Module ) )
  96          {    // We are handling blogs:
  97              $this->SELECT_add( ', COUNT( DISTINCT blog_ID ) AS nb_blogs' );
  98              $this->FROM_add( 'LEFT JOIN T_blogs on user_ID = blog_owner_user_ID ' );
  99          }
 100          else
 101          {
 102              $this->SELECT_add( ', 0 AS nb_blogs' );
 103          }
 104  
 105          $this->WHERE( 'user_ID IS NOT NULL' );
 106          if( $params['grouped'] )
 107          { // Group by user group
 108              $this->GROUP_BY( 'user_ID, grp_ID' );
 109              $this->ORDER_BY( 'grp_name, *, user_profileupdate_date DESC, user_lastseen_ts DESC, user_ID ASC' );
 110          }
 111          else
 112          {
 113              $this->GROUP_BY( 'user_ID' );
 114              $this->ORDER_BY( '*, user_profileupdate_date DESC, user_lastseen_ts DESC, user_ID ASC' );
 115          }
 116      }
 117  
 118  
 119      /**

 120       * Restrict with keywords

 121       *

 122       * @param string Keyword search string

 123       */
 124  	function where_keywords( $keywords )
 125      {
 126          global $DB;
 127  
 128          if( empty( $keywords ) )
 129          {
 130              return;
 131          }
 132  
 133          $search = array();
 134  
 135          $kw_array = explode( ' ', $keywords );
 136          foreach( $kw_array as $kw )
 137          {
 138              // Note: we use CONCAT_WS (Concat With Separator) because CONCAT returns NULL if any arg is NULL

 139              $search[] = 'CONCAT_WS( " ", '.$this->keywords_fields.' ) LIKE "%'.$DB->escape($kw).'%"';
 140          }
 141  
 142          if( count( $search ) > 0 )
 143          {
 144              $this->WHERE_and( implode( ' AND ', $search ) );
 145          }
 146      }
 147  
 148  
 149      /**

 150       * Restrict with gender

 151       *

 152       * @param string Gender ( M, F, MF )

 153       */
 154  	function where_gender( $gender )
 155      {
 156          global $DB;
 157  
 158          if( empty( $gender ) )
 159          {
 160              return;
 161          }
 162  
 163          if( $gender == 'MF' )
 164          {    // Get men AND women
 165              $this->WHERE_and( 'user_gender IN ( "M", "F" )' );
 166          }
 167          else
 168          {    // Get men OR women
 169              $this->WHERE_and( 'user_gender = '.$DB->quote( $gender ) );
 170          }
 171      }
 172  
 173  
 174      /**

 175       * Restrict to user status, currenlty activated also means autoactivated users

 176       *

 177       * @param string user status ( 'activated', 'deactivated', 'new', 'emailchanged', 'failedactivation', 'closed' )

 178       * @param boolean set true to include users only with the given status, or set false to exclude users with the given status

 179       * @param boolean set true to make exact comparing with selected status

 180       */
 181  	function where_status( $status, $include = true, $exactly = false )
 182      {
 183          global $DB;
 184  
 185          if( empty( $status ) )
 186          {
 187              return;
 188          }
 189  
 190          if( $status == 'activated' && !$exactly )
 191          { // Activated and Autoactivated users
 192              if( $include )
 193              {
 194                  $this->WHERE_and( 'user_status = '.$DB->quote( 'activated' ).' OR user_status = '.$DB->quote( 'autoactivated' ) );
 195              }
 196              else
 197              {
 198                  $this->WHERE_and( 'user_status <> '.$DB->quote( 'activated' ).' AND user_status <> '.$DB->quote( 'autoactivated' ) );
 199              }
 200          }
 201          else
 202          { // Other status check
 203              // init compare, which depends if we want to include or exclude users with the given status

 204              $compare = $include ? ' = ' : ' <> ';
 205              $this->WHERE_and( 'user_status'.$compare.$DB->quote( $status ) );
 206          }
 207  
 208          return;
 209      }
 210  
 211  
 212      /**

 213       * Restrict to reported users

 214       *

 215       * @param boolean is reported

 216       */
 217  	function where_reported( $reported )
 218      {
 219          if( empty( $reported ) || !$reported )
 220          {
 221              return;
 222          }
 223  
 224          $this->SELECT_add( ', COUNT( DISTINCT urep_reporter_ID ) AS user_rep' );
 225          $this->FROM_add( ' LEFT JOIN T_users__reports ON urep_target_user_ID = user_ID' );
 226          $this->WHERE_and( 'urep_reporter_ID IS NOT NULL' );
 227      }
 228  
 229  
 230      /**

 231       * Restrict to users with custom notifcation sender settings

 232       *

 233       * @param boolean with custom sender email

 234       * @param boolean with custom sender name

 235       */
 236  	function where_custom_sender( $custom_sender_email, $custom_sender_name )
 237      {
 238          global $DB, $Settings;
 239  
 240          if( $custom_sender_email )
 241          { // restrict to users with custom notification sender email address
 242              $this->FROM_add( ' LEFT JOIN T_users__usersettings as custom_sender_email ON custom_sender_email.uset_user_ID = user_ID AND custom_sender_email.uset_name = "notification_sender_email"' );
 243              $this->WHERE_and( 'custom_sender_email.uset_value IS NOT NULL AND custom_sender_email.uset_value <> '.$DB->quote( $Settings->get( 'notification_sender_email' ) ) );
 244          }
 245  
 246          if( $custom_sender_name )
 247          { // restrict to users with custom notification sender name
 248              $this->FROM_add( ' LEFT JOIN T_users__usersettings as custom_sender_name ON custom_sender_name.uset_user_ID = user_ID AND custom_sender_name.uset_name = "notification_sender_name"' );
 249              $this->WHERE_and( 'custom_sender_name.uset_value IS NOT NULL AND custom_sender_name.uset_value <> '.$DB->quote( $Settings->get( 'notification_sender_name' ) ) );
 250          }
 251      }
 252  
 253  
 254      /**

 255       * Restrict with user group

 256       *

 257       * @param integer User group ID

 258       */
 259  	function where_group( $group_ID )
 260      {
 261          global $DB;
 262  
 263          $group_ID = (int)$group_ID;
 264  
 265          if( $group_ID < 1 )
 266          { // Group Id may be '0' - to show all groups, '-1' - to show all groups as ungrouped list
 267              return;
 268          }
 269  
 270          $this->WHERE_and( 'user_grp_ID = '.$DB->quote( $group_ID ) );
 271      }
 272  
 273  
 274      /**

 275       * Restrict with location (Country | Region | Subregion | City)

 276       *

 277       * @param string Field name of location (ctry | rgn | subrg | city)

 278       * @param integer Location ID

 279       */
 280  	function where_location( $location, $ID )
 281      {
 282          global $DB;
 283  
 284          if( empty( $ID ) )
 285          {
 286              return;
 287          }
 288  
 289          $this->WHERE_and( 'user_'.$location.'_ID = '.$DB->quote( $ID ) );
 290      }
 291  
 292  
 293      /**

 294       * Restrict with age group

 295       *

 296       * @param integer Age min

 297       * @param integer Age max

 298       */
 299  	function where_age_group( $age_min, $age_max )
 300      {
 301          global $DB;
 302  
 303          $sql_age = array();
 304  
 305          if( $age_min > 0 )
 306          {    // search_min_value BETWEEN user_age_min AND user_age_max
 307              $sql_age[] = '( '.$DB->quote( $age_min ).' >= user_age_min AND '.$DB->quote( $age_min ).' <= user_age_max )';
 308          }
 309  
 310          if( $age_max > 0 )
 311          {    // search_max_value BETWEEN user_age_min AND user_age_max
 312              $sql_age[] = '( '.$DB->quote( $age_max ).' >= user_age_min AND '.$DB->quote( $age_max ).' <= user_age_max )';
 313          }
 314  
 315          if( count( $sql_age ) > 0 )
 316          {
 317              $this->WHERE_and( implode( ' OR ', $sql_age ) );
 318          }
 319      }
 320  
 321  
 322      /**

 323       * Restrict with user fields

 324       *

 325       * @param array User fields

 326       */
 327  	function where_userfields( $userfields )
 328      {
 329          global $DB;
 330  
 331          if( empty( $userfields ) )
 332          {
 333              return;
 334          }
 335  
 336          $criteria_where_clauses = array();
 337          foreach( $userfields as $field )
 338          {
 339              $type = (int)$field['type'];
 340              $value = trim( strip_tags( $field['value'] ) );
 341              if( $type > 0 && $value != '' )
 342              {    // Filter by Specific criteria
 343                  $words = explode( ' ', $value );
 344                  if( count( $words ) > 0 )
 345                  {
 346                      foreach( $words as $word )
 347                      {
 348                          $criteria_where_clauses[] = 'uf_ufdf_ID = "'.$DB->escape($type).'" AND uf_varchar LIKE "%'.$DB->escape($word).'%"';
 349                      }
 350                  }
 351              }
 352          }
 353  
 354          if( count( $criteria_where_clauses ) > 0 )
 355          {    // Some creteria is defined
 356              $this->FROM_add( ' LEFT JOIN T_users__fields ON uf_user_ID = user_ID' );
 357              $this->WHERE_and( ' ( ( '.implode( ' ) OR ( ', $criteria_where_clauses ).' ) ) ' );
 358          }
 359      }
 360  
 361  }
 362  
 363  ?>

title

Description

title

Description

title

Description

title

title

Body