[Summary view] [Print] [Text view]

   1  <?php
   2  /**
   3   * @package     Joomla.Administrator
   4   * @subpackage  com_users
   5   *
   6   * @copyright   Copyright (C) 2005 - 2012 Open Source Matters, Inc. All rights reserved.
   7   * @license     GNU General Public License version 2 or later; see LICENSE.txt
   8   */
   9  
  10  defined('_JEXEC') or die;
  11  
  12  jimport('joomla.application.component.modellist');
  13  
  14  /**
  15   * Methods supporting a list of user records.
  16   *
  17   * @package     Joomla.Administrator
  18   * @subpackage  com_users
  19   * @since       1.6
  20   */
  21  class UsersModelUsers extends JModelList
  22  {
  23      /**
  24       * Constructor.
  25       *
  26       * @param   array  $config  An optional associative array of configuration settings.
  27       *
  28       * @see     JController
  29       * @since   1.6
  30       */
  31  	public function __construct($config = array())
  32      {
  33          if (empty($config['filter_fields']))
  34          {
  35              $config['filter_fields'] = array(
  36                  'id', 'a.id',
  37                  'name', 'a.name',
  38                  'username', 'a.username',
  39                  'email', 'a.email',
  40                  'block', 'a.block',
  41                  'sendEmail', 'a.sendEmail',
  42                  'registerDate', 'a.registerDate',
  43                  'lastvisitDate', 'a.lastvisitDate',
  44                  'activation', 'a.activation',
  45              );
  46          }
  47  
  48          parent::__construct($config);
  49      }
  50  
  51      /**
  52       * Method to auto-populate the model state.
  53       *
  54       * Note. Calling getState in this method will result in recursion.
  55       *
  56       * @return  void
  57       *
  58       * @since   1.6
  59       */
  60  	protected function populateState($ordering = null, $direction = null)
  61      {
  62          // Initialise variables.
  63          $app = JFactory::getApplication('administrator');
  64  
  65          // Adjust the context to support modal layouts.
  66          if ($layout = JRequest::getVar('layout', 'default'))
  67          {
  68              $this->context .= '.'.$layout;
  69          }
  70  
  71          // Load the filter state.
  72          $search = $this->getUserStateFromRequest($this->context.'.filter.search', 'filter_search');
  73          $this->setState('filter.search', $search);
  74  
  75          $active = $this->getUserStateFromRequest($this->context.'.filter.active', 'filter_active');
  76          $this->setState('filter.active', $active);
  77  
  78          $state = $this->getUserStateFromRequest($this->context.'.filter.state', 'filter_state');
  79          $this->setState('filter.state', $state);
  80  
  81          $groupId = $this->getUserStateFromRequest($this->context.'.filter.group', 'filter_group_id', null, 'int');
  82          $this->setState('filter.group_id', $groupId);
  83  
  84          $range = $this->getUserStateFromRequest($this->context.'.filter.range', 'filter_range');
  85          $this->setState('filter.range', $range);
  86  
  87          $groups = json_decode(base64_decode(JRequest::getVar('groups', '', 'default', 'BASE64')));
  88          if (isset($groups))
  89          {
  90              JArrayHelper::toInteger($groups);
  91          }
  92          $this->setState('filter.groups', $groups);
  93  
  94          $excluded = json_decode(base64_decode(JRequest::getVar('excluded', '', 'default', 'BASE64')));
  95          if (isset($excluded))
  96          {
  97              JArrayHelper::toInteger($excluded);
  98          }
  99          $this->setState('filter.excluded', $excluded);
 100  
 101          // Load the parameters.
 102          $params = JComponentHelper::getParams('com_users');
 103          $this->setState('params', $params);
 104  
 105          // List state information.
 106          parent::populateState('a.name', 'asc');
 107      }
 108  
 109      /**
 110       * Method to get a store id based on model configuration state.
 111       *
 112       * This is necessary because the model is used by the component and
 113       * different modules that might need different sets of data or different
 114       * ordering requirements.
 115       *
 116       * @param   string  $id  A prefix for the store id.
 117       *
 118       * @return  string  A store id.
 119       *
 120       * @since   1.6
 121       */
 122  	protected function getStoreId($id = '')
 123      {
 124          // Compile the store id.
 125          $id    .= ':'.$this->getState('filter.search');
 126          $id    .= ':'.$this->getState('filter.active');
 127          $id    .= ':'.$this->getState('filter.state');
 128          $id    .= ':'.$this->getState('filter.group_id');
 129          $id .= ':'.$this->getState('filter.range');
 130  
 131          return parent::getStoreId($id);
 132      }
 133  
 134      /**
 135       * Gets the list of users and adds expensive joins to the result set.
 136       *
 137       * @return  mixed  An array of data items on success, false on failure.
 138       *
 139       * @since   1.6
 140       */
 141  	public function getItems()
 142      {
 143          // Get a storage key.
 144          $store = $this->getStoreId();
 145  
 146          // Try to load the data from internal storage.
 147          if (empty($this->cache[$store]))
 148          {
 149              $groups = $this->getState('filter.groups');
 150              $groupId = $this->getState('filter.group_id');
 151              if (isset($groups) && (empty($groups) || $groupId && !in_array($groupId, $groups)))
 152              {
 153                  $items = array();
 154              }
 155              else
 156              {
 157                  $items = parent::getItems();
 158              }
 159  
 160              // Bail out on an error or empty list.
 161              if (empty($items))
 162              {
 163                  $this->cache[$store] = $items;
 164  
 165                  return $items;
 166              }
 167  
 168              // Joining the groups with the main query is a performance hog.
 169              // Find the information only on the result set.
 170  
 171              // First pass: get list of the user id's and reset the counts.
 172              $userIds = array();
 173              foreach ($items as $item)
 174              {
 175                  $userIds[] = (int) $item->id;
 176                  $item->group_count = 0;
 177                  $item->group_names = '';
 178                  $item->note_count = 0;
 179              }
 180  
 181              // Get the counts from the database only for the users in the list.
 182              $db = $this->getDbo();
 183              $query = $db->getQuery(true);
 184  
 185              // Join over the group mapping table.
 186              $query->select('map.user_id, COUNT(map.group_id) AS group_count')
 187                  ->from('#__user_usergroup_map AS map')
 188                  ->where('map.user_id IN ('.implode(',', $userIds).')')
 189                  ->group('map.user_id')
 190                  // Join over the user groups table.
 191                  ->join('LEFT', '#__usergroups AS g2 ON g2.id = map.group_id');
 192  
 193              $db->setQuery($query);
 194  
 195              // Load the counts into an array indexed on the user id field.
 196              $userGroups = $db->loadObjectList('user_id');
 197  
 198              $error = $db->getErrorMsg();
 199              if ($error)
 200              {
 201                  $this->setError($error);
 202  
 203                  return false;
 204              }
 205  
 206              $query->clear()
 207                  ->select('n.user_id, COUNT(n.id) As note_count')
 208                  ->from('#__user_notes AS n')
 209                  ->where('n.user_id IN ('.implode(',', $userIds).')')
 210                  ->where('n.state >= 0')
 211                  ->group('n.user_id');
 212  
 213              $db->setQuery((string) $query);
 214  
 215              // Load the counts into an array indexed on the aro.value field (the user id).
 216              $userNotes = $db->loadObjectList('user_id');
 217  
 218              $error = $db->getErrorMsg();
 219              if ($error)
 220              {
 221                  $this->setError($error);
 222  
 223                  return false;
 224              }
 225  
 226              // Second pass: collect the group counts into the master items array.
 227              foreach ($items as &$item)
 228              {
 229                  if (isset($userGroups[$item->id]))
 230                  {
 231                      $item->group_count = $userGroups[$item->id]->group_count;
 232                      //Group_concat in other databases is not supported
 233                      $item->group_names = $this->_getUserDisplayedGroups($item->id);
 234                  }
 235  
 236                  if (isset($userNotes[$item->id]))
 237                  {
 238                      $item->note_count = $userNotes[$item->id]->note_count;
 239                  }
 240              }
 241  
 242              // Add the items to the internal cache.
 243              $this->cache[$store] = $items;
 244          }
 245  
 246          return $this->cache[$store];
 247      }
 248  
 249      /**
 250       * Build an SQL query to load the list data.
 251       *
 252       * @return  JDatabaseQuery
 253       *
 254       * @since   1.6
 255       */
 256  	protected function getListQuery()
 257      {
 258          // Create a new query object.
 259          $db = $this->getDbo();
 260          $query = $db->getQuery(true);
 261  
 262          // Select the required fields from the table.
 263          $query->select(
 264              $this->getState(
 265                  'list.select',
 266                  'a.*'
 267              )
 268          );
 269  
 270          $query->from($db->quoteName('#__users').' AS a');
 271  
 272          // If the model is set to check item state, add to the query.
 273          $state = $this->getState('filter.state');
 274  
 275          if (is_numeric($state))
 276          {
 277              $query->where('a.block = '.(int) $state);
 278          }
 279  
 280          // If the model is set to check the activated state, add to the query.
 281          $active = $this->getState('filter.active');
 282  
 283          if (is_numeric($active))
 284          {
 285              if ($active == '0')
 286              {
 287                  $query->where('a.activation = '.$db->quote(''));
 288              }
 289              elseif ($active == '1')
 290              {
 291                  $query->where($query->length('a.activation').' = 32');
 292              }
 293          }
 294  
 295          // Filter the items over the group id if set.
 296          $groupId = $this->getState('filter.group_id');
 297          $groups = $this->getState('filter.groups');
 298  
 299          if ($groupId || isset($groups))
 300          {
 301              $query->join('LEFT', '#__user_usergroup_map AS map2 ON map2.user_id = a.id');
 302              $query->group('a.id,a.name,a.username,a.password,a.usertype,a.block,a.sendEmail,a.registerDate,a.lastvisitDate,a.activation,a.params,a.email');
 303  
 304              if ($groupId)
 305              {
 306                  $query->where('map2.group_id = '.(int) $groupId);
 307              }
 308  
 309              if (isset($groups))
 310              {
 311                  $query->where('map2.group_id IN ('.implode(',', $groups).')');
 312              }
 313          }
 314  
 315          // Filter the items over the search string if set.
 316          if ($this->getState('filter.search') !== '')
 317          {
 318              // Escape the search token.
 319              $token    = $db->Quote('%'.$db->escape($this->getState('filter.search')).'%');
 320  
 321              // Compile the different search clauses.
 322              $searches    = array();
 323              $searches[]    = 'a.name LIKE '.$token;
 324              $searches[]    = 'a.username LIKE '.$token;
 325              $searches[]    = 'a.email LIKE '.$token;
 326  
 327              // Add the clauses to the query.
 328              $query->where('('.implode(' OR ', $searches).')');
 329          }
 330  
 331          // Add filter for registration ranges select list
 332          $range = $this->getState('filter.range');
 333  
 334          // Apply the range filter.
 335          if ($range = $this->getState('filter.range'))
 336          {
 337              jimport('joomla.utilities.date');
 338  
 339              // Get UTC for now.
 340              $dNow = new JDate;
 341              $dStart = clone $dNow;
 342  
 343              switch ($range)
 344              {
 345                  case 'past_week':
 346                      $dStart->modify('-7 day');
 347                      break;
 348  
 349                  case 'past_1month':
 350                      $dStart->modify('-1 month');
 351                      break;
 352  
 353                  case 'past_3month':
 354                      $dStart->modify('-3 month');
 355                      break;
 356  
 357                  case 'past_6month':
 358                      $dStart->modify('-6 month');
 359                      break;
 360  
 361                  case 'post_year':
 362                  case 'past_year':
 363                      $dStart->modify('-1 year');
 364                      break;
 365  
 366                  case 'today':
 367                      // Ranges that need to align with local 'days' need special treatment.
 368                      $app    = JFactory::getApplication();
 369                      $offset    = $app->getCfg('offset');
 370  
 371                      // Reset the start time to be the beginning of today, local time.
 372                      $dStart    = new JDate('now', $offset);
 373                      $dStart->setTime(0, 0, 0);
 374  
 375                      // Now change the timezone back to UTC.
 376                      $tz = new DateTimeZone('GMT');
 377                      $dStart->setTimezone($tz);
 378                      break;
 379              }
 380  
 381              if ($range == 'post_year')
 382              {
 383                  $query->where(
 384                      'a.registerDate < '.$db->quote($dStart->format('Y-m-d H:i:s'))
 385                  );
 386              }
 387              else
 388              {
 389                  $query->where(
 390                      'a.registerDate >= '.$db->quote($dStart->format('Y-m-d H:i:s')).
 391                      ' AND a.registerDate <='.$db->quote($dNow->format('Y-m-d H:i:s'))
 392                  );
 393              }
 394          }
 395  
 396          // Filter by excluded users
 397          $excluded = $this->getState('filter.excluded');
 398          if (!empty($excluded))
 399          {
 400              $query->where('id NOT IN ('.implode(',', $excluded).')');
 401          }
 402  
 403          // Add the list ordering clause.
 404          $query->order($db->escape($this->getState('list.ordering', 'a.name')).' '.$db->escape($this->getState('list.direction', 'ASC')));
 405  
 406          return $query;
 407      }
 408      //sqlsrv change
 409  	function _getUserDisplayedGroups($user_id)
 410      {
 411          $db = JFactory::getDbo();
 412          $sql = "SELECT title FROM ".$db->quoteName('#__usergroups')." ug left join ".
 413                  $db->quoteName('#__user_usergroup_map')." map on (ug.id = map.group_id)".
 414                  " WHERE map.user_id=".$user_id;
 415  
 416          $db->setQuery($sql);
 417          $result = $db->loadColumn();
 418          return implode("\n", $result);
 419      }
 420  }