lib.php 13.8 KB
Newer Older
Martyn Smith's avatar
Martyn Smith committed
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
<?php
/**
 * This program is part of Mahara
 *
 *  This program is free software; you can redistribute it and/or modify
 *  it under the terms of the GNU General Public License as published by
 *  the Free Software Foundation; either version 2 of the License, or
 *  (at your option) any later version.
 *
 *  This program is distributed in the hope that it will be useful,
 *  but WITHOUT ANY WARRANTY; without even the implied warranty of
 *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 *  GNU General Public License for more details.
 *
 *  You should have received a copy of the GNU General Public License
 *  along with this program; if not, write to the Free Software
 *  Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301 USA
 *
 * @package    mahara
 * @subpackage search-internal
 * @author     Martyn Smith <martyn@catalyst.net.nz>
 * @license    http://www.gnu.org/copyleft/gpl.html GNU GPL
 * @copyright  (C) 2006,2007 Catalyst IT Ltd http://catalyst.net.nz
 *
 */

defined('INTERNAL') || die();

/**
30
 * The internal search plugin which searches against the
Martyn Smith's avatar
Martyn Smith committed
31
32
33
34
35
36
37
 * Mahara database.
 */
class PluginSearchInternal extends PluginSearch {

    /**
     * Implement user searching with SQL
     *
38
39
     * NOTE: user with ID zero should never be returned
     *
Martyn Smith's avatar
Martyn Smith committed
40
41
42
43
44
45
46
47
     * @param string  The query string
     * @param integer How many results to return
     * @param integer What result to start at (0 == first result)
     * @return array  A data structure containing results looking like ...
     *         $results = array(
     *               count   => integer, // total number of results
     *               limit   => integer, // how many results are returned
     *               offset  => integer, // starting from which result
48
     *               data    => array(   // the result records
Martyn Smith's avatar
Martyn Smith committed
49
     *                   array(
50
51
52
53
54
55
56
     *                       id            => integer,
     *                       username      => string,
     *                       institution   => string,
     *                       firstname     => string,
     *                       lastname      => string,
     *                       preferredname => string,
     *                       email         => string,
Martyn Smith's avatar
Martyn Smith committed
57
58
     *                   ),
     *                   array(
59
60
61
62
63
64
65
     *                       id            => integer,
     *                       username      => string,
     *                       institution   => string,
     *                       firstname     => string,
     *                       lastname      => string,
     *                       preferredname => string,
     *                       email         => string,
Martyn Smith's avatar
Martyn Smith committed
66
67
68
69
70
71
     *                   ),
     *                   array(...),
     *               ),
     *           );
     */
    public static function search_user($query_string, $limit, $offset = 0) {
72
73
        safe_require('artefact', 'internal');
        $publicfields = array_keys(ArtefactTypeProfile::get_public_fields());
74
75
76
        if (empty($publicfields)) {
            $publicfields = array('preferredname');
        }
77
        $prefix = get_config('dbprefix');
Martyn Smith's avatar
Martyn Smith committed
78
        if ( is_postgres() ) {
79
            $fieldlist = "('" . join("','", $publicfields) . "')";
Martyn Smith's avatar
Martyn Smith committed
80

81
82
            $count = get_field_sql('
                SELECT 
Martyn Smith's avatar
Martyn Smith committed
83
                    COUNT(DISTINCT u.id)
84
                FROM
Martyn Smith's avatar
Martyn Smith committed
85
86
                    ' . $prefix . 'usr u
                    LEFT JOIN ' . $prefix . 'artefact a ON u.id=a.owner
87
                WHERE
Martyn Smith's avatar
Martyn Smith committed
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
                    u.id <> 0
                    AND ((
                            u.preferredname IS NULL
                            AND (
                                u.firstname ILIKE \'%\' || ? || \'%\'
                                OR u.lastname ILIKE \'%\' || ? || \'%\'
                            )
                        )
                        OR (
                            a.artefacttype IN ' . $fieldlist . '
                            AND ( a.title ILIKE \'%\' || ? || \'%\')
                        )
                    )
                ',
                array($query_string, $query_string, $query_string)
            );
104

105
106
            if ($count > 0) {
                $users = get_records_sql_assoc('
107
                    SELECT DISTINCT ON (u.firstname, u.lastname, u.id)
108
                        u.id, u.username, u.institution, u.firstname, u.lastname, u.preferredname
109
110
111
112
                    FROM ' . $prefix . 'artefact a
                        INNER JOIN ' . $prefix .'usr u ON u.id = a.owner
                    WHERE
                        u.id <> 0
Martyn Smith's avatar
Martyn Smith committed
113
114
115
116
117
118
119
120
121
122
123
124
125
126
                        AND ((
                                u.preferredname IS NULL
                                AND (
                                    u.firstname ILIKE \'%\' || ? || \'%\'
                                    OR u.lastname ILIKE \'%\' || ? || \'%\'
                                )
                            )
                            OR (
                                a.artefacttype IN ' . $fieldlist . '
                                AND ( a.title ILIKE \'%\' || ? || \'%\')
                            )
                        )
                    ORDER BY u.firstname, u.lastname, u.id',
                array($query_string, $query_string, $query_string),
127
128
                $offset,
                $limit);
129

130
                $userlist = '('.join(',', array_map(create_function('$u','return $u->id;'), $users)).')';
Martyn Smith's avatar
Martyn Smith committed
131
                log_debug($userlist);
132

133
134
135
136
                $data = get_records_sql_array('
                    SELECT 
                        u.id, a.artefacttype, a.title
                    FROM
Martyn Smith's avatar
Martyn Smith committed
137
138
                        ' . $prefix . 'usr u
                        LEFT JOIN ' . $prefix . 'artefact a ON u.id=a.owner AND a.artefacttype IN ' . $fieldlist . '
139
                    WHERE
Martyn Smith's avatar
Martyn Smith committed
140
                        u.id IN ' . $userlist . '
141
                    ORDER BY u.firstname, u.lastname, u.id, a.artefacttype',
142
                    array());
143

Martyn Smith's avatar
Martyn Smith committed
144
                log_debug($data);
145
                if (!empty($data)) {
146
147
148
149
150
151
152
153
                    foreach ($users as &$user) {
                        $user->name = display_name($user);
                        unset($user->username);
                        unset($user->institution);
                        unset($user->firstname);
                        unset($user->lastname);
                        unset($user->preferredname);
                    }
154
                    foreach ($data as $rec) {
Martyn Smith's avatar
Martyn Smith committed
155
156
157
                        if (!isset($rec->artefacttype)) {
                            continue;
                        }
158
159
160
161
162
                        if ($rec->artefacttype == 'email') {
                            $users[$rec->id]->email[] = $rec->title;
                        }
                        else {
                            $users[$rec->id]->{$rec->artefacttype} = $rec->title;
163
164
                        }
                    }
165
                    $data = array_values($users);
166
                }
167
168
169
170
            }
            else {
                $data = false;
            }
Martyn Smith's avatar
Martyn Smith committed
171
172
173
174
175
        }
        // TODO
        // else if ( is_mysql() ) {
        // }
        else {
Nigel McNie's avatar
Nigel McNie committed
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
            throw new SQLException('search_user() is not implemented for your database engine (' . get_config('dbtype') . ')');
        }

        return array(
            'count'   => $count,
            'limit'   => $limit,
            'offset'  => $offset,
            'data'    => $data,
        );
    }
    
    /**
     * Implement group searching with SQL
     *
     * @param string  The query string
     * @param integer How many results to return
     * @param integer What result to start at (0 == first result)
     * @return array  A data structure containing results looking like ...
     *         $results = array(
     *               count   => integer, // total number of results
     *               limit   => integer, // how many results are returned
     *               offset  => integer, // starting from which result
     *               data    => array(   // the result records
     *                   array(
200
201
202
203
204
205
     *                       id          => integer,
     *                       name        => string,
     *                       owner       => integer,
     *                       description => string,
     *                       ctime       => string,
     *                       mtime       => string,
Nigel McNie's avatar
Nigel McNie committed
206
207
     *                   ),
     *                   array(
208
209
210
211
212
213
     *                       id          => integer,
     *                       name        => string,
     *                       owner       => integer,
     *                       description => string,
     *                       ctime       => string,
     *                       mtime       => string,
Nigel McNie's avatar
Nigel McNie committed
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
     *                   ),
     *                   array(...),
     *               ),
     *           );
     */
    public static function search_group($query_string, $limit, $offset = 0) {
        global $USER;
        if ( is_postgres() ) {
            $data = get_records_sql_array("
                SELECT
                    id, name, owner, description, ctime, mtime
                FROM
                    " . get_config('dbprefix') . "usr_group u
                WHERE
                    owner = ?
                    AND (
                        name ILIKE '%' || ? || '%' 
                        OR description ILIKE '%' || ? || '%' 
                    )
                ",
                array($USER->get('id'), $query_string, $query_string),
                $offset,
                $limit
            );

            $count = get_field_sql("
                SELECT
                    COUNT(*)
                FROM
                    " . get_config('dbprefix') . "usr_group u
                WHERE
                    owner = ?
                    AND (
                        name ILIKE '%' || ? || '%' 
                        OR description ILIKE '%' || ? || '%' 
                    )
            ",
Nigel McNie's avatar
Nigel McNie committed
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
                array($USER->get('id'), $query_string, $query_string)
            );
        }
        // TODO
        // else if ( is_mysql() ) {
        // }
        else {
            throw new SQLException('search_group() is not implemented for your database engine (' . get_config('dbtype') . ')');
        }

        return array(
            'count'   => $count,
            'limit'   => $limit,
            'offset'  => $offset,
            'data'    => $data,
        );
    }
    
    /**
     * Implement community searching with SQL
     *
     * @param string  The query string
     * @param integer How many results to return
     * @param integer What result to start at (0 == first result)
     * @return array  A data structure containing results looking like ...
     *         $results = array(
     *               count   => integer, // total number of results
     *               limit   => integer, // how many results are returned
     *               offset  => integer, // starting from which result
     *               data    => array(   // the result records
     *                   array(
     *                       id            => integer,
283
284
285
286
287
288
     *                       name          => string,
     *                       description   => string,
     *                       jointype      => string,
     *                       owner         => string,
     *                       ctime         => string,
     *                       mtime         => string,
Nigel McNie's avatar
Nigel McNie committed
289
290
291
     *                   ),
     *                   array(
     *                       id            => integer,
292
293
294
295
296
297
     *                       name          => string,
     *                       description   => string,
     *                       jointype      => string,
     *                       owner         => string,
     *                       ctime         => string,
     *                       mtime         => string,
Nigel McNie's avatar
Nigel McNie committed
298
299
300
301
302
     *                   ),
     *                   array(...),
     *               ),
     *           );
     */
303
    public static function search_community($query_string, $limit, $offset=0, $all=false) {
Nigel McNie's avatar
Nigel McNie committed
304
305
        global $USER;
        if ( is_postgres() ) {
306
            $sql = "
Nigel McNie's avatar
Nigel McNie committed
307
308
309
310
                SELECT
                    id, name, description, jointype, owner, ctime, mtime
                FROM
                    " . get_config('dbprefix') . "community
311
                WHERE (
Nigel McNie's avatar
Nigel McNie committed
312
313
                    name ILIKE '%' || ? || '%' 
                    OR description ILIKE '%' || ? || '%' 
314
315
316
317
                )";
            $values = array($query_string, $query_string);
            if (!$all) {
                $sql .=  "AND ( 
318
319
320
                    owner = ? OR id IN (
                        SELECT community FROM " . get_config('dbprefix') . "community_member WHERE member = ?
                    )
321
322
323
324
325
                )";
                $values[] = $USER->get('id');
                $values[] = $USER->get('id');
            }
            $data = get_records_sql_array($sql, $values, $offset, $limit);
Nigel McNie's avatar
Nigel McNie committed
326

327
            $sql = "
Nigel McNie's avatar
Nigel McNie committed
328
329
330
                SELECT
                    COUNT(*)
                FROM
Martyn Smith's avatar
Martyn Smith committed
331
                    " . get_config('dbprefix') . "community u
332
333
334
                WHERE (
                    name ILIKE '%' || ? || '%' 
                    OR description ILIKE '%' || ? || '%' 
335
336
337
338
339
340
                )";
            if (!$all) {
                $sql .= "AND ( 
                        owner = ? OR id IN (
                            SELECT community FROM " . get_config('dbprefix') . "community_member WHERE member = ?
                        )
Nigel McNie's avatar
Nigel McNie committed
341
                    )
342
343
344
                ";
            }
            $count = get_field_sql($sql, $values);
Nigel McNie's avatar
Nigel McNie committed
345
346
347
348
349
        }
        // TODO
        // else if ( is_mysql() ) {
        // }
        else {
Nigel McNie's avatar
Nigel McNie committed
350
            throw new SQLException('search_community() is not implemented for your database engine (' . get_config('dbtype') . ')');
Martyn Smith's avatar
Martyn Smith committed
351
352
353
354
355
356
        }

        return array(
            'count'   => $count,
            'limit'   => $limit,
            'offset'  => $offset,
357
            'data'    => $data,
Martyn Smith's avatar
Martyn Smith committed
358
359
360
361
362
        );
    }
}

?>