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