source: trunk/admin/user_list_backend.php @ 27997

Last change on this file since 27997 was 27997, checked in by plg, 6 years ago

merge r27996 from branch 2.6 to trunk

bug 3065 fixed: avoid SQL errors with external authentication

File size: 6.9 KB
Line 
1<?php
2// +-----------------------------------------------------------------------+
3// | Piwigo - a PHP based photo gallery                                    |
4// +-----------------------------------------------------------------------+
5// | Copyright(C) 2008-2014 Piwigo Team                  http://piwigo.org |
6// | Copyright(C) 2003-2008 PhpWebGallery Team    http://phpwebgallery.net |
7// | Copyright(C) 2002-2003 Pierrick LE GALL   http://le-gall.net/pierrick |
8// +-----------------------------------------------------------------------+
9// | This program is free software; you can redistribute it and/or modify  |
10// | it under the terms of the GNU General Public License as published by  |
11// | the Free Software Foundation                                          |
12// |                                                                       |
13// | This program is distributed in the hope that it will be useful, but   |
14// | WITHOUT ANY WARRANTY; without even the implied warranty of            |
15// | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU      |
16// | 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., 59 Temple Place - Suite 330, Boston, MA 02111-1307, |
21// | USA.                                                                  |
22// +-----------------------------------------------------------------------+
23
24define('PHPWG_ROOT_PATH','../');
25define('IN_ADMIN', true);
26
27include_once(PHPWG_ROOT_PATH.'include/common.inc.php');
28
29check_status(ACCESS_ADMINISTRATOR);
30       
31/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
32 * Easy set variables
33 */
34       
35/* Array of database columns which should be read and sent back to DataTables. Use a space where
36 * you want to insert a non-database field (for example a counter or static image)
37 */
38$aColumns = array(
39  $conf['user_fields']['id'],
40  $conf['user_fields']['username'],
41  'status',
42  $conf['user_fields']['email'],
43  'recent_period',
44  'level',
45  'registration_date'
46  );
47
48$aColumns = trigger_change('user_list_columns', $aColumns);
49       
50/* Indexed column (used for fast and accurate table cardinality) */
51$sIndexColumn = 'user_id';
52       
53/* DB table to use */
54$sTable = USERS_TABLE.' INNER JOIN '.USER_INFOS_TABLE.' AS ui ON '.$conf['user_fields']['id'].' = ui.user_id';
55
56/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
57 * If you just want to use the basic configuration for DataTables with PHP server-side, there is
58 * no need to edit below this line
59 */
60
61/*
62 * Paging
63 */
64$sLimit = "";
65if ( isset( $_REQUEST['iDisplayStart'] ) && $_REQUEST['iDisplayLength'] != '-1' )
66{
67  $sLimit = "LIMIT ".pwg_db_real_escape_string( $_REQUEST['iDisplayStart'] ).", ".
68    pwg_db_real_escape_string( $_REQUEST['iDisplayLength'] );
69}
70       
71       
72/*
73 * Ordering
74 */
75if ( isset( $_REQUEST['iSortCol_0'] ) )
76{
77  $sOrder = "ORDER BY  ";
78  for ( $i=0 ; $i<intval( $_REQUEST['iSortingCols'] ) ; $i++ )
79  {
80    if ( $_REQUEST[ 'bSortable_'.intval($_REQUEST['iSortCol_'.$i]) ] == "true" )
81    {
82      $sOrder .= $aColumns[ intval( $_REQUEST['iSortCol_'.$i] ) ]."
83                                        ".pwg_db_real_escape_string( $_REQUEST['sSortDir_'.$i] ) .", ";
84    }
85  }
86               
87  $sOrder = substr_replace( $sOrder, "", -2 );
88  if ( $sOrder == "ORDER BY" )
89  {
90    $sOrder = "";
91  }
92}
93       
94       
95/*
96 * Filtering
97 * NOTE this does not match the built-in DataTables filtering which does it
98 * word by word on any field. It's possible to do here, but concerned about efficiency
99 * on very large tables, and MySQL's regex functionality is very limited
100 */
101$sWhere = "";
102if ( $_REQUEST['sSearch'] != "" )
103{
104  $sWhere = "WHERE (";
105  for ( $i=0 ; $i<count($aColumns) ; $i++ )
106  {
107    $sWhere .= $aColumns[$i]." LIKE '%".pwg_db_real_escape_string( $_REQUEST['sSearch'] )."%' OR ";
108  }
109  $sWhere = substr_replace( $sWhere, "", -3 );
110  $sWhere .= ')';
111}
112       
113/* Individual column filtering */
114for ( $i=0 ; $i<count($aColumns) ; $i++ )
115{
116  if (isset($_REQUEST['bSearchable_'.$i]) && isset($_REQUEST['sSearch_'.$i])
117      &&$_REQUEST['bSearchable_'.$i] == "true" && $_REQUEST['sSearch_'.$i] != ''
118    )
119  {
120    if ( $sWhere == "" )
121    {
122      $sWhere = "WHERE ";
123    }
124    else
125    {
126      $sWhere .= " AND ";
127    }
128    $sWhere .= $aColumns[$i]." LIKE '%".pwg_db_real_escape_string($_REQUEST['sSearch_'.$i])."%' ";
129  }
130}
131       
132       
133/*
134 * SQL queries
135 * Get data to display
136 */
137$sQuery = "
138                SELECT SQL_CALC_FOUND_ROWS ".str_replace(" , ", " ", implode(", ", $aColumns))."
139                FROM   $sTable
140                $sWhere
141                $sOrder
142                $sLimit
143        ";
144$rResult = pwg_query($sQuery);
145       
146/* Data set length after filtering */
147$rResultFilterTotal = pwg_query('SELECT FOUND_ROWS();');
148list($iFilteredTotal) = pwg_db_fetch_row($rResultFilterTotal);
149       
150/* Total data set length */
151$sQuery = "
152                SELECT COUNT(".$sIndexColumn.")
153                FROM   $sTable
154        ";
155$rResultTotal = pwg_query($sQuery);
156$aResultTotal = pwg_db_fetch_array($rResultTotal);
157$iTotal = $aResultTotal[0];
158       
159       
160/*
161 * Output
162 */
163$output = array(
164  "sEcho" => intval($_REQUEST['sEcho']),
165  "iTotalRecords" => $iTotal,
166  "iTotalDisplayRecords" => $iFilteredTotal,
167  "aaData" => array()
168        );
169
170$user_ids = array();
171
172while ( $aRow = pwg_db_fetch_array( $rResult ) )
173{
174  $user_ids[] = $aRow[ $conf['user_fields']['id'] ];
175 
176  $row = array();
177  for ( $i=0 ; $i<count($aColumns) ; $i++ )
178  {
179    if ( $aColumns[$i] == "status" )
180    {
181      $row[] = l10n('user_status_'.$aRow[ $aColumns[$i] ]);
182    }
183    else if ( $aColumns[$i] == "level" )
184    {
185      $row[] = $aRow[ $aColumns[$i] ] == 0 ? '' : l10n(sprintf('Level %d', $aRow[ $aColumns[$i] ]));
186    }
187    else if ( $aColumns[$i] != ' ' )
188    {
189      /* General output */
190      $colname = $aColumns[$i];
191      foreach ($conf['user_fields'] as $real_name => $alias)
192      {
193        if ($aColumns[$i] == $real_name)
194        {
195          $colname = $alias;
196        }
197      }
198     
199      $row[] = $aRow[$colname];
200    }
201
202  }
203  $output['aaData'][] = $row;
204}
205
206// replace "recent_period" by the list of groups
207if (count($user_ids) > 0)
208{
209  $groups_of_user = array();
210 
211  $query = '
212SELECT
213    user_id,
214    GROUP_CONCAT(name ORDER BY name SEPARATOR ", ") AS groups
215  FROM '.USER_GROUP_TABLE.'
216    JOIN '.GROUPS_TABLE.' ON id = group_id
217  WHERE user_id IN ('.implode(',', $user_ids).')
218  GROUP BY user_id
219;';
220  $result = pwg_query($query);
221  while ($row = pwg_db_fetch_assoc($result))
222  {
223    $groups_of_user[ $row['user_id'] ] = $row['groups'];
224  }
225
226  $key_replace = array_search('recent_period', $aColumns);
227 
228  // replacement
229  foreach (array_keys($output['aaData']) as $idx)
230  {
231    $user_id = $output['aaData'][$idx][0];
232    $output['aaData'][$idx][$key_replace] = isset($groups_of_user[$user_id]) ? $groups_of_user[$user_id] : '';
233  }
234}
235
236$output = trigger_change('after_render_user_list', $output);
237       
238echo json_encode( $output );
239?>
Note: See TracBrowser for help on using the repository browser.