-
Notifications
You must be signed in to change notification settings - Fork 207
/
NamesList.php
317 lines (267 loc) · 16.5 KB
/
NamesList.php
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
207
208
209
210
211
212
213
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
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
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
<?php
#**************************************************************************
# openSIS is a free student information system for public and non-public
# schools from Open Solutions for Education, Inc. web: www.os4ed.com
#
# openSIS is web-based, open source, and comes packed with features that
# include student demographic info, scheduling, grade book, attendance,
# report cards, eligibility, transcripts, parent portal,
# student portal and more.
#
# Visit the openSIS web site at http://www.opensis.com to learn more.
# If you have question regarding this system or the license, please send
# an email to [email protected].
#
# This program is released under the terms of the GNU General Public License as
# published by the Free Software Foundation, version 2 of the License.
# See license.txt.
#
# 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, see <http://www.gnu.org/licenses/>.
#
#***************************************************************************************
error_reporting(0);
include("Data.php");
include("Warehouse.php");
$keyword = $_REQUEST['str'];
$block_id = $_REQUEST['block_id'];
if (User('PROFILE') == 'student')
$user_id = UserStudentID();
else
$user_id = UserID();
$username_user = DBGet(DBQuery('SELECT USERNAME FROM login_authentication WHERE USER_ID=' . $user_id . ' AND PROFILE_ID=' . User('PROFILE_ID')));
$username_user = $username_user[1]['USERNAME'];
if (User('PROFILE_ID') == 0) {
$tmp_q = '';
$tmp_a = array();
$tmp_stu_arr = array();
$tmp_stf_arr = array();
$tmp_p_arr = array();
$tmp_q = DBGet(DBQuery('SELECT STUDENT_ID FROM students'));
foreach ($tmp_q as $tmp_a) {
$tmp_stu_arr[] = $tmp_a['STUDENT_ID'];
}
$tmp_q = '';
$tmp_a = array();
$tmp_q = DBGet(DBQuery('SELECT STAFF_ID FROM staff'));
foreach ($tmp_q as $tmp_a) {
$tmp_stf_arr[] = $tmp_a['STAFF_ID'];
}
$tmp_q = '';
$tmp_a = array();
$tmp_q = DBGet(DBQuery('SELECT STAFF_ID FROM people'));
foreach ($tmp_q as $tmp_a) {
$tmp_p_arr[] = $tmp_a['STAFF_ID'];
}
} elseif (User('PROFILE_ID') != 0 && User('PROFILE') == 'admin') {
$schools = DBGet(DBQuery('SELECT GROUP_CONCAT(SCHOOL_ID) as SCHOOL_ID FROM staff_school_relationship WHERE STAFF_ID=' . $user_id . ' AND (START_DATE=\'0000-00-00\' OR START_DATE<=\'' . date('Y-m-d') . '\') AND (END_DATE=\'0000-00-00\' OR END_DATE IS NULL OR END_DATE>=\'' . date('Y-m-d') . '\') '));
$schools = $schools[1]['SCHOOL_ID'];
$tmp_q = '';
$tmp_a = array();
$tmp_stu_arr = array();
$tmp_stf_arr = array();
$tmp_p_arr = array();
$tmp_q = DBGet(DBQuery('SELECT DISTINCT STUDENT_ID FROM student_enrollment WHERE SCHOOL_ID IN (' . $schools . ') AND (START_DATE=\'0000-00-00\' OR START_DATE<=\'' . date('Y-m-d') . '\') AND (END_DATE=\'0000-00-00\' OR END_DATE IS NULL OR END_DATE>=\'' . date('Y-m-d') . '\') '));
foreach ($tmp_q as $tmp_a) {
$tmp_stu_arr[] = $tmp_a['STUDENT_ID'];
}
$tmp_q = '';
$tmp_a = array();
$tmp_q = DBGet(DBQuery('SELECT DISTINCT STAFF_ID FROM staff_school_relationship WHERE SCHOOL_ID IN (' . $schools . ') AND (START_DATE=\'0000-00-00\' OR START_DATE<=\'' . date('Y-m-d') . '\') AND (END_DATE=\'0000-00-00\' OR END_DATE IS NULL OR END_DATE>=\'' . date('Y-m-d') . '\') '));
foreach ($tmp_q as $tmp_a) {
$tmp_stf_arr[] = $tmp_a['STAFF_ID'];
}
$tmp_q = '';
$tmp_a = array();
$tmp_q = DBGet(DBQuery('SELECT DISTINCT sjp.PERSON_ID FROM student_enrollment se,students_join_people sjp WHERE se.SCHOOL_ID IN (' . $schools . ') AND (se.START_DATE=\'0000-00-00\' OR se.START_DATE<=\'' . date('Y-m-d') . '\') AND (se.END_DATE=\'0000-00-00\' OR se.END_DATE IS NULL OR se.END_DATE>=\'' . date('Y-m-d') . '\') AND se.STUDENT_ID=sjp.STUDENT_ID '));
foreach ($tmp_q as $tmp_a) {
$tmp_p_arr[] = $tmp_a['PERSON_ID'];
}
} elseif (User('PROFILE') == 'parent' || User('PROFILE') == 'student') {
$course_periods = DBGet(DBQuery('SELECT GROUP_CONCAT(course_period_id) as COURSE_PERIOD_ID FROM schedule WHERE STUDENT_ID=' . UserStudentID()));
$course_periods = $course_periods[1]['COURSE_PERIOD_ID'];
$tmp_q = '';
$tmp_a = array();
$tmp_stu_arr = array();
$tmp_stf_arr = array();
$tmp_p_arr = array();
if (User('PROFILE') == 'parent') {
$tmp_q = DBGet(DBQuery('SELECT DISTINCT se.STUDENT_ID FROM student_enrollment se,students_join_people sjp WHERE (se.START_DATE=\'0000-00-00\' OR se.START_DATE<=\'' . date('Y-m-d') . '\') AND (se.END_DATE=\'0000-00-00\' OR se.END_DATE IS NULL OR se.END_DATE>=\'' . date('Y-m-d') . '\') AND se.STUDENT_ID=sjp.STUDENT_ID AND sjp.PERSON_ID=' . $user_id));
foreach ($tmp_q as $tmp_a) {
$stu_arr[] = $tmp_a['STUDENT_ID'];
$tmp_stu_arr[] = $tmp_a['STUDENT_ID'];
}
$student_id = implode(',', $stu_arr);
$asso_people = DBGet(DBQuery('select person_id from students_join_people where student_id in(' . $student_id . ')'));
foreach ($asso_people as $asso_v) {
$tmp_p_arr[] = $asso_v['PERSON_ID'];
}
}
if (User('PROFILE') == 'student') {
$tmp_q = DBGet(DBQuery('SELECT DISTINCT sjp.PERSON_ID FROM student_enrollment se,students_join_people sjp WHERE (se.START_DATE=\'0000-00-00\' OR se.START_DATE<=\'' . date('Y-m-d') . '\') AND (se.END_DATE=\'0000-00-00\' OR se.END_DATE IS NULL OR se.END_DATE>=\'' . date('Y-m-d') . '\') AND se.STUDENT_ID=sjp.STUDENT_ID AND sjp.STUDENT_ID=' . $user_id));
foreach ($tmp_q as $tmp_a) {
$tmp_p_arr[] = $tmp_a['PERSON_ID'];
}
}
if ($course_periods != '') {
$tmp_q = '';
$tmp_a = array();
$tmp_q = DBGet(DBQuery('SELECT TEACHER_ID,SECONDARY_TEACHER_ID FROM course_periods WHERE COURSE_PERIOD_ID IN (' . $course_periods . ') '));
foreach ($tmp_q as $tmp_a) {
$tmp_stf_arr[] = $tmp_a['TEACHER_ID'];
if ($tmp_a['SECONDARY_TEACHER_ID'] != '')
$tmp_stf_arr[] = $tmp_a['SECONDARY_TEACHER_ID'];
}
}
$tmp_q = '';
$tmp_a = array();
$tmp_q = DBGet(DBQuery('SELECT s.STAFF_ID FROM staff s,staff_school_relationship ssr WHERE PROFILE=\'admin\' AND ssr.STAFF_ID=s.STAFF_ID AND (ssr.START_DATE=\'0000-00-00\' OR ssr.START_DATE<=\'' . date('Y-m-d') . '\') AND (ssr.END_DATE=\'0000-00-00\' OR ssr.END_DATE IS NULL OR ssr.END_DATE>=\'' . date('Y-m-d') . '\') AND ssr.SCHOOL_ID=' . UserSchool()));
foreach ($tmp_q as $tmp_a) {
$tmp_stf_arr[] = $tmp_a['STAFF_ID'];
}
} elseif (User('PROFILE') == 'teacher') {
$schools = DBGet(DBQuery('SELECT GROUP_CONCAT(SCHOOL_ID) as SCHOOL_ID FROM staff_school_relationship WHERE STAFF_ID=' . $user_id . ' AND (START_DATE=\'0000-00-00\' OR START_DATE<=\'' . date('Y-m-d') . '\') AND (END_DATE=\'0000-00-00\' OR END_DATE IS NULL OR END_DATE>=\'' . date('Y-m-d') . '\') '));
$schools = $schools[1]['SCHOOL_ID'];
$course_periods = DBGet(DBQuery('SELECT GROUP_CONCAT(course_period_id) as COURSE_PERIOD_ID FROM course_periods WHERE TEACHER_ID=' . $user_id . ' OR SECONDARY_TEACHER_ID=' . $user_id));
$course_periods = $course_periods[1]['COURSE_PERIOD_ID'];
//////////////// new for associate teacher ////////////
$course_periods_ass = DBGet(DBQuery('SELECT GROUP_CONCAT(course_period_id) as COURSE_PERIOD_ID FROM course_periods WHERE BEGIN_DATE <=\'' . date('Y-m-d') . '\' and END_DATE>=\'' . date('Y-m-d') . '\' AND SCHOOL_ID=\'' . $schools . '\' AND (TEACHER_ID=' . $user_id . ' OR SECONDARY_TEACHER_ID=' . $user_id . ')'));
$course_periods_ass = $course_periods_ass[1]['COURSE_PERIOD_ID'];
$ass_tec = array();
$course_periods_ass_tec = DBGet(DBQuery(' SELECT TEACHER_ID, SECONDARY_TEACHER_ID FROM course_periods WHERE COURSE_PERIOD_ID in (' . $course_periods_ass . ')'));
foreach ($course_periods_ass_tec as $ass_tec_k => $ass_tec_v) {
if ($ass_tec_v['TEACHER_ID'] != $user_id)
$ass_tec[] = $ass_tec_v['TEACHER_ID'];
if (($ass_tec_v['SECONDARY_TEACHER_ID'] != $user_id) && !empty($ass_tec_v['SECONDARY_TEACHER_ID']))
$ass_tec[] = $ass_tec_v['SECONDARY_TEACHER_ID'];
}
$ass_tec = array_unique($ass_tec);
//////////////// end for associate teacher ////////////
$tmp_q = '';
$tmp_a = array();
$tmp_stu_arr = array();
$tmp_stf_arr = array();
$tmp_p_arr = array();
if ($course_periods != '') {
$tmp_q = DBGet(DBQuery('SELECT DISTINCT se.STUDENT_ID FROM student_enrollment se,schedule s WHERE (se.START_DATE=\'0000-00-00\' OR se.START_DATE<=\'' . date('Y-m-d') . '\') AND (se.END_DATE=\'0000-00-00\' OR se.END_DATE IS NULL OR se.END_DATE>=\'' . date('Y-m-d') . '\') AND se.STUDENT_ID=s.STUDENT_ID AND s.COURSE_PERIOD_ID IN (' . $course_periods . ')'));
foreach ($tmp_q as $tmp_a) {
$tmp_stu_arr[] = $tmp_a['STUDENT_ID'];
$tmp_qa = DBGet(DBQuery('SELECT DISTINCT PERSON_ID FROM students_join_people WHERE STUDENT_ID=' . $tmp_a['STUDENT_ID']));
foreach ($tmp_qa as $tmp_aa) {
$tmp_p_arr[] = $tmp_aa['PERSON_ID'];
}
}
}
$tmp_q = '';
$tmp_a = array();
$tmp_q = DBGet(DBQuery('SELECT s.STAFF_ID FROM staff s,staff_school_relationship ssr WHERE PROFILE=\'admin\' AND ssr.STAFF_ID=s.STAFF_ID AND (ssr.START_DATE=\'0000-00-00\' OR ssr.START_DATE<=\'' . date('Y-m-d') . '\') AND (ssr.END_DATE=\'0000-00-00\' OR ssr.END_DATE IS NULL OR ssr.END_DATE>=\'' . date('Y-m-d') . '\') AND ssr.SCHOOL_ID IN (' . $schools . ')'));
foreach ($tmp_q as $tmp_a) {
$tmp_stf_arr[] = $tmp_a['STAFF_ID'];
}
///////////// new for ass tec ////
if (count($ass_tec) > 0) {
foreach ($ass_tec as $ass_v) {
$tmp_stf_arr[] = $ass_v;
}
}
///////////// new for ass tec ////
}
if ($keyword == "")
echo "";
else {
$sql_staff = "SELECT * FROM login_authentication,staff WHERE login_authentication.user_id=staff.staff_id and first_name LIKE '$keyword%' and username IS NOT NULL and login_authentication.profile_id NOT IN(3,4) AND staff.staff_id in (" . implode(',', $tmp_stf_arr) . ") ORDER BY last_name";
$sql_student = "SELECT * FROM login_authentication,students WHERE login_authentication.user_id=students.student_id and first_name LIKE '$keyword%' and username IS NOT NULL and login_authentication.profile_id=3 " . (count($tmp_stu_arr) > 0 ? " AND students.student_id IN (" . implode(',', $tmp_stu_arr) . ")" : "") . " ORDER BY last_name";
$sql_people = "SELECT * FROM login_authentication,people WHERE login_authentication.user_id=people.staff_id and first_name LIKE '$keyword%' and username IS NOT NULL and login_authentication.profile_id=4 " . (count($tmp_p_arr) > 0 ? " AND people.staff_id IN (" . implode(',', $tmp_p_arr) . ")" : "") . " ORDER BY last_name";
$result_staff = DBGet(DBQuery($sql_staff));
$result_student = DBGet(DBQuery($sql_student));
$result_people = DBGet(DBQuery($sql_people));
if (count($result_staff) > 0) {
foreach ($result_staff as $row) {
$str = ucfirst(trim($row['LAST_NAME'])) . ', ' . ucfirst(trim($row['FIRST_NAME'])) . ' - ' . ucfirst(trim($row['USERNAME']));
if (trim($row['USERNAME'] != ""))
echo '<li><a id="search' . $row['STAFF_ID'] . '" onclick="a(\'' . $row['USERNAME'] . '\',\'' . $block_id . '\')">' . $str . '</a></li>';
}
} else
echo "";
if (count($result_student) > 0 && count($tmp_stu_arr) > 0) {
foreach ($result_student as $row_student) {
$str = ucfirst(trim($row_student['LAST_NAME'])) . ', ' . ucfirst(trim($row_student['FIRST_NAME'])) . ' - ' . ucfirst(trim($row_student['USERNAME']));
if (trim($row_student['USERNAME'] != ""))
echo '<li><a id="search' . $row_student['STUDENT_ID'] . '" onclick="a(\'' . $row_student['USERNAME'] . '\',\'' . $block_id . '\')">' . $str . '</a></li>';
}
} else
echo "";
if (count($result_people) > 0 && count($tmp_p_arr) > 0) {
foreach ($result_people as $row_people) {
$str = ucfirst(trim($row_people['LAST_NAME'])) . ', ' . ucfirst(trim($row_people['FIRST_NAME'])) . ' - ' . ucfirst(trim($row_people['USERNAME']));
if (trim($row_people['USERNAME'] != ""))
echo '<li><a id="search' . $row_people['STAFF_ID'] . '" onclick="a(\'' . $row_people['USERNAME'] . '\',\'' . $block_id . '\')">' . $str . '</a></li>';
}
} else
echo "";
}
$pos = strpos($keyword, ',');
$lastpos = strrpos($keyword, ',');
$str1 = substr($keyword, $pos + 1, strlen($keyword));
$str2 = substr($keyword, $lastpos + 1, strlen($keyword));
if ($str2 != "") {
if ($pos != 0 || $lastpos != 0) {
$sql_staff = "SELECT * FROM login_authentication,staff WHERE login_authentication.user_id=staff.staff_id and (first_name LIKE '$str1%' or first_name LIKE '$str2%') and username IS NOT NULL and login_authentication.profile_id NOT IN(3,4) AND staff.staff_id in (" . implode(',', $tmp_stf_arr) . ") ORDER BY last_name";
$sql_student = "SELECT * FROM login_authentication,students WHERE login_authentication.user_id=students.student_id and (first_name LIKE '$str1%' or first_name LIKE '$str2%') and username IS NOT NULL and login_authentication.profile_id=3 " . (count($tmp_stu_arr) > 0 ? " AND students.student_id IN (" . implode(',', $tmp_stu_arr) . ")" : "") . " ORDER BY last_name";
$sql_people = "SELECT * FROM login_authentication,people WHERE login_authentication.user_id=people.staff_id and (first_name LIKE '$str1%' or first_name LIKE '$str2%') and username IS NOT NULL and login_authentication.profile_id=4 " . (count($tmp_p_arr) > 0 ? " AND people.staff_id IN (" . implode(',', $tmp_p_arr) . ")" : "") . " ORDER BY last_name";
$result_staff = DBGet(DBQuery($sql_staff));
$result_student = DBGet(DBQuery($sql_student));
$result_people = DBGet(DBQuery($sql_people));
if (count($result_staff) > 0) {
foreach ($result_staff as $row) {
$str = ucfirst(trim($row['LAST_NAME'])) . ', ' . ucfirst(trim($row['FIRST_NAME'])) . ' - ' . ucfirst(trim($row['USERNAME']));
$newpos = $lastpos + 1;
if (trim($row['USERNAME'] != ""))
echo '<li><a id="search' . $row['STAFF_ID'] . '" onclick="b(\'' . $newpos . '\',\'' . $row['USERNAME'] . '\',\'' . $block_id . '\');">' . $str . '</a></li>';
}
} else
echo "";
if (count($result_student) > 0 && count($tmp_stu_arr) > 0) {
foreach ($result_student as $row_student) {
$str = ucfirst(trim($row_student['LAST_NAME'])) . ', ' . ucfirst(trim($row_student['FIRST_NAME'])) . ' - ' . ucfirst(trim($row_student['USERNAME']));
$newpos = $lastpos + 1;
if (trim($row_student['USERNAME'] != ""))
echo '<li><a id="search' . $row_student['STUDENT_ID'] . '" onclick="b(\'' . $newpos . '\',\'' . $row_student['USERNAME'] . '\',\'' . $block_id . '\')">' . $str . '</a></li>';
}
} else
echo "";
if (count($result_people) > 0 && count($tmp_p_arr) > 0) {
foreach ($result_people as $row_people) {
$str = ucfirst(trim($row_people['LAST_NAME'])) . ', ' . ucfirst(trim($row_people['FIRST_NAME'])) . ' - ' . ucfirst(trim($row_people['USERNAME']));
$newpos = $lastpos + 1;
if (trim($row_people['USERNAME'] != ""))
echo '<li><a id="search' . $row_people['STUDENT_ID'] . '" onclick="b(\'' . $newpos . '\',\'' . $row_people['USERNAME'] . '\',\'' . $block_id . '\')">' . $str . '</a></li>';
}
} else
echo "";
}
} else
echo "";
$group_id = DBGet(DBQuery("select distinct group_id,group_name from mail_group where group_name LIKE '$keyword%' AND user_name='" . $username_user . "' "));
if (count($group_id) > 0) {
foreach ($group_id as $row) {
$str = strtolower($row['GROUP_NAME']);
$id = $row['GROUP_ID'];
$group = DBGet(DBQuery("select * from mail_groupmembers where group_id=$id"));
foreach ($group as $r) {
$name[] = $r['USER_NAME'];
}
if (!empty($name) && count($name) > 0)
$username = implode(',', $name);
echo '<li><a id="search' . $row['GROUP_ID'] . '" onclick="a(\'' . $str . '\',\'' . $block_id . '\')">' . $str . '</a></li>';
}
}
else {
echo "";
}
?>