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
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
|
<?php
# Lifter010: TODO
/**
* StandardSearch.php - Class of type SearchType used for searches with QuickSearch
*
* 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.
*
* @author Rasmus <fuhse@data-quest.de>
* @license http://www.gnu.org/licenses/gpl-2.0.html GPL version 2
* @category Stud.IP
*/
/**
* Class of type SearchType used for searches with QuickSearch
* (lib/classes/QuickSearch.php). You can search with a sql-syntax in the
* database. You just need to give in a query like for a PDB-prepare statement
* and at least the variable ":input" in the query (the :input will be replaced
* with the input of the QuickSearch userinput.
* [code]
* $search = new SQLSearch("username");
* [/code]
*
* @author Rasmus Fuhse
*
*/
class StandardSearch extends SQLSearch
{
public $search;
public $search_settings;
/**
*
* @param string $search The search type.
*
* @param Array $search_settings Settings for the selected seach type.
* Depending on the search type different settings are possible
* which can change the output or the display of the output
* of the search. The array must be an associative array
* with the setting as array key.
* The following settings are implemented:
* Search type 'room':
* - display_seats: If set to true, the seats will be displayed
* after the name of the room.
*
* @return void
*/
public function __construct($search, $search_settings = [])
{
if (is_array($search_settings)) {
$this->search_settings = $search_settings;
}
$this->avatarLike = $this->search = $search;
$this->sql = $this->getSQL();
}
/**
* returns the title/description of the searchfield
*
* @return string title/description
*/
public function getTitle()
{
switch ($this->search) {
case "username":
case "user_id":
return _("Person suchen");
case "Seminar_id":
case "AnySeminar_id":
return _("Veranstaltung suchen");
case "Arbeitsgruppe_id":
return _("Arbeitsgruppe suchen");
case "Institut_id":
return _("Einrichtung suchen");
default:
throw new UnexpectedValueException("Invalid search type {$this->search}");
}
}
/**
* returns a sql-string appropriate for the searchtype of the current class
*
* @return string
*/
private function getSQL()
{
$semester = " CONCAT('(',IFNULL(GROUP_CONCAT(DISTINCT sem1.name ORDER BY sem1.beginn SEPARATOR '-'),'" . _('unbegrenzt') . "'),')')";
switch ($this->search) {
case "username":
$this->extendedLayout = true;
$sql = "SELECT DISTINCT auth_user_md5.username";
if (empty($this->search_settings['simple_name'])) {
$sql .= ", CONCAT(auth_user_md5.Nachname, ', ', auth_user_md5.Vorname, ' (', auth_user_md5.username, ')'), auth_user_md5.perms ";
} else {
$sql .= ", CONCAT(auth_user_md5.Vorname, ' ', auth_user_md5.Nachname) ";
}
$sql .= "FROM auth_user_md5 LEFT JOIN user_info ON (user_info.user_id = auth_user_md5.user_id) " .
"LEFT JOIN user_visibility ON (user_visibility.user_id = auth_user_md5.user_id) " .
"WHERE (CONCAT(auth_user_md5.Vorname, ' ', auth_user_md5.Nachname) LIKE REPLACE(:input, ' ', '% ') " .
"OR CONCAT(auth_user_md5.Nachname, ' ', auth_user_md5.Vorname) LIKE REPLACE(:input, ' ', '% ') " .
"OR CONCAT(auth_user_md5.Nachname, ', ', auth_user_md5.Vorname) LIKE :input " .
"OR auth_user_md5.username LIKE :input) AND " . get_vis_query('auth_user_md5', 'search') .
" ORDER BY Nachname ASC, Vorname ASC";
return $sql;
case "user_id":
$this->extendedLayout = true;
$sql = "SELECT DISTINCT auth_user_md5.user_id";
if (empty($this->search_settings['simple_name'])) {
$sql .= ", CONCAT(auth_user_md5.Nachname, ', ', auth_user_md5.Vorname, ' (', auth_user_md5.username, ')'), auth_user_md5.perms ";
} else {
$sql .= ", CONCAT(auth_user_md5.Vorname, ' ', auth_user_md5.Nachname) ";
}
$sql .= "FROM auth_user_md5 LEFT JOIN user_info ON (user_info.user_id = auth_user_md5.user_id) " .
"LEFT JOIN user_visibility ON (user_visibility.user_id = auth_user_md5.user_id) " .
"WHERE (CONCAT(auth_user_md5.Vorname, ' ', auth_user_md5.Nachname) LIKE REPLACE(:input, ' ', '% ') " .
"OR CONCAT(auth_user_md5.Nachname, ' ', auth_user_md5.Vorname) LIKE REPLACE(:input, ' ', '% ') " .
"OR CONCAT(auth_user_md5.Nachname, ', ', auth_user_md5.Vorname) LIKE :input " .
"OR auth_user_md5.username LIKE :input) AND " . get_vis_query('auth_user_md5', 'search') .
" ORDER BY Nachname ASC, Vorname ASC";
return $sql;
case "Seminar_id":
return "SELECT seminare.Seminar_id, CONCAT_WS(' ', seminare.VeranstaltungsNummer, seminare.Name, ".$semester.") " .
"FROM seminare " .
"LEFT JOIN semester_courses ON (semester_courses.course_id = seminare.Seminar_id) " .
"LEFT JOIN `semester_data` sem1 ON (semester_courses.semester_id = sem1.semester_id) " .
"LEFT JOIN seminar_user ON (seminar_user.Seminar_id = seminare.Seminar_id AND seminar_user.status = 'dozent') " .
"LEFT JOIN auth_user_md5 ON (auth_user_md5.user_id = seminar_user.user_id) " .
"WHERE (seminare.Name LIKE :input " .
"OR CONCAT(auth_user_md5.Vorname, ' ', auth_user_md5.Nachname) LIKE :input " .
"OR seminare.VeranstaltungsNummer LIKE :input " .
"OR seminare.Untertitel LIKE :input " .
"OR seminare.Beschreibung LIKE :input " .
"OR seminare.Ort LIKE :input " .
"OR seminare.Sonstiges LIKE :input) " .
"AND seminare.visible = 1 " .
"AND seminare.status NOT IN ('".implode("', '", studygroup_sem_types())."') " .
" GROUP BY seminare.seminar_id ORDER BY sem1.`beginn` DESC, " .
(Config::get()->IMPORTANT_SEMNUMBER ? "seminare.`VeranstaltungsNummer`, " : "") .
"seminare.`Name`";
case "AnySeminar_id":
return "SELECT seminare.Seminar_id, CONCAT_WS(' ', seminare.VeranstaltungsNummer, seminare.Name, ".$semester.") " .
"FROM seminare " .
"LEFT JOIN semester_courses ON (semester_courses.course_id = seminare.Seminar_id) " .
"LEFT JOIN `semester_data` sem1 ON (semester_courses.semester_id = sem1.semester_id) " .
"LEFT JOIN seminar_user ON (seminar_user.Seminar_id = seminare.Seminar_id AND seminar_user.status = 'dozent') " .
"LEFT JOIN auth_user_md5 ON (auth_user_md5.user_id = seminar_user.user_id) " .
"WHERE (seminare.Name LIKE :input " .
"OR CONCAT(auth_user_md5.Vorname, ' ', auth_user_md5.Nachname) LIKE REPLACE(:input, ' ', '% ') " .
"OR CONCAT(auth_user_md5.Nachname, ' ', auth_user_md5.Vorname) LIKE REPLACE(:input, ' ', '% ') " .
"OR CONCAT(auth_user_md5.Nachname, ', ', auth_user_md5.Vorname) LIKE :input " .
"OR seminare.VeranstaltungsNummer LIKE :input " .
"OR seminare.Untertitel LIKE :input " .
"OR seminare.Beschreibung LIKE :input " .
"OR seminare.Ort LIKE :input " .
"OR seminare.Sonstiges LIKE :input) " .
" GROUP BY seminare.seminar_id ORDER BY sem1.`beginn` DESC, " .
(Config::get()->IMPORTANT_SEMNUMBER ? "seminare.`VeranstaltungsNummer`, " : "") .
"seminare.`Name`";
case "Arbeitsgruppe_id":
return "SELECT DISTINCT seminare.Seminar_id, seminare.Name " .
"FROM seminare " .
"LEFT JOIN seminar_user ON (seminar_user.Seminar_id = seminare.Seminar_id AND seminar_user.status = 'dozent') " .
"LEFT JOIN auth_user_md5 ON (auth_user_md5.user_id = seminar_user.user_id) " .
"WHERE (seminare.Name LIKE :input " .
"OR CONCAT(auth_user_md5.Vorname, ' ', auth_user_md5.Nachname) LIKE REPLACE(:input, ' ', '% ') " .
"OR CONCAT(auth_user_md5.Nachname, ' ', auth_user_md5.Vorname) LIKE REPLACE(:input, ' ', '% ') " .
"OR CONCAT(auth_user_md5.Nachname, ', ', auth_user_md5.Vorname) LIKE :input " .
"OR seminare.VeranstaltungsNummer LIKE :input " .
"OR seminare.Untertitel LIKE :input " .
"OR seminare.Beschreibung LIKE :input " .
"OR seminare.Ort LIKE :input " .
"OR seminare.Sonstiges LIKE :input) " .
"AND seminare.visible = 1 " .
"AND seminare.status IN ('".implode("', '", studygroup_sem_types())."') " .
"ORDER BY seminare.Name";
case "Institut_id":
return "SELECT DISTINCT Institute.Institut_id, Institute.Name " .
"FROM Institute " .
"LEFT JOIN range_tree ON (range_tree.item_id = Institute.Institut_id) " .
"WHERE Institute.Name LIKE :input " .
"OR Institute.Strasse LIKE :input " .
"OR Institute.email LIKE :input " .
"OR range_tree.name LIKE :input " .
"ORDER BY Institute.Name";
default:
throw new UnexpectedValueException("Invalid search type {$this->search}");
}
}
/**
* A very simple overwrite of the same method from SearchType class.
* returns the absolute path to this class for autoincluding this class.
*
* @return: path to this class
*/
public function includePath()
{
return studip_relative_path(__FILE__);
}
}
|
