/[libdata]/trunk/admin/include/app_controls.php
This is repository of my old source code which isn't updated any more. Go to git.rot13.org for current projects!
ViewVC logotype

Contents of /trunk/admin/include/app_controls.php

Parent Directory Parent Directory | Revision Log Revision Log


Revision 13 - (show annotations)
Thu Jan 15 16:05:55 2004 UTC (17 years, 9 months ago) by dpavlin
File size: 89554 byte(s)
Initial revision

1 <?php
2 /**********************************************************
3 Function Library: app_controls.php
4 Original Author: Paul Bramscher <brams006@tc.umn.edu>
5 Last Modified: 09.29.2003
6 ***********************************************************
7 Comments:
8 Functions here are generally related to drawing of HTML
9 form input: text boxes, drop-down boxes, and radio buttons.
10 Other functions include lookup and picklist related duties.
11
12 These have been separated from forms.php since they are
13 typically used in more than one place. Some are quite
14 generic and are used throughout the system.
15 ***********************************************************
16 Table of Contents:
17 adminReturn
18 authPage
19 authResourceDelete
20 authResourceEdit
21 authSubject
22 commaList
23 displayKeys
24 displayRelatedPages
25 dropDownAccountOmit
26 dropDownAuthCourses
27 dropDownAuthPages
28 dropDownAuthSubjects
29 dropDownCourses
30 dropDownCoursesubOmit
31 dropDownCoursesubSelected
32 dropDownFaculty
33 dropDownFacultySelected
34 dropDownField
35 dropDownFieldOmit
36 dropDownFieldSelected
37 dropDownInfotype
38 dropDownPageStaff
39 dropDownPageSubject
40 dropDownResource
41 dropDownServiceLimit
42 dropDownStaff
43 dropDownStaffOmit
44 dropDownStaffSelected
45 existsResSub
46 existsResSubNA
47 existsRow
48 getNotIn
49 locationList
50 lookupFaculty
51 lookupField
52 lookupStaff
53 radioField
54 recordCount
55 selectCoursesub
56 selectFaculty
57 selectKey
58 selectStaff
59 statQuery
60 textInmySQL
61 textOutHTML
62 textSearchmySQL
63 **********************************************************/
64
65
66
67 /**********************************************************
68 Function: adminReturn($sess_access_level)
69 Author: Paul Bramscher
70 Last Modified: 07.02.2003
71 ***********************************************************
72 Incoming:
73 $sess_access_level Access level of the current
74 session.
75 ***********************************************************
76 Outgoing:
77 None
78 ***********************************************************
79 Purpose:
80 A simple HTML link back to the author console, used
81 throughout the Lumina(r) system. This function may be
82 (optionally) passed the access level of the current user
83 session. Higher access will display more menu link
84 options. Note that each menu, upon arrival, verifies the
85 session and access level so this presents no security
86 risk.
87 **********************************************************/
88 function adminReturn($sess_access_level) {
89
90 include ("global_vars.php");
91
92 // Return to admin console
93 printf("<center><br>\n");
94 printf("<a href=\"%sconsole.phtml\">Authoring Console</a>", $GLOBAL_ADMIN_URL);
95
96 // If manager or higher
97 if ($sess_access_level >= 100) printf(" | <a href=\"%sconsole_manager.phtml\">Manager Functions</a>", $GLOBAL_ADMIN_URL);
98
99 // If DBA
100 if ($sess_access_level == 1000) printf(" | <a href=\"%sconsole_dba.phtml\">DBA Tools</a>", $GLOBAL_ADMIN_URL);
101
102 printf("</center>");
103 }
104
105
106 /**********************************************************
107 Function: authPage($con, $page_id, $sess_access_level, $sess_staff_id)
108 Author: Paul Bramscher
109 Last Modified: 01.15.2003
110 ***********************************************************
111 Incoming:
112 $con Database connection string
113 $page_id Page to test authorization
114 $sess_access_level Access level of the current user
115 session.
116 $sess_staff_id staff id of the current user
117 ***********************************************************
118 Outgoing:
119 1 = authorized to work on this page.
120 0 = non-authorized.
121 ***********************************************************
122 Purpose:
123 A check to ensure whether the current user may access the
124 supplied page. The following rules apply:
125
126 (1) DBA's can edit anything.
127 (2) Managers can edit pages created by anyone within their unit.
128 (3) Page coordinators can edit pages they coordinate.
129 (4) Page maintainers may also edit pages they are assigned to.
130
131 Some scenarios of pages that are NOT editable:
132
133 (1) The original page creator is no longer the coordinator,
134 not a DBA, and not assigned as a maintainer. S/he can no
135 longer edit the page.
136 (2) You are a unit managager and used to have access to a page
137 coordinated by one of your employees. S/he switches units,
138 and is now under a new manager. The page can now be accessed
139 by the manager of the new unit and not yourself.
140
141 etc...
142
143 **********************************************************/
144 function authPage($con, $page_id, $sess_access_level, $sess_staff_id){
145
146 /* Access Table Definitions
147 +-----------+--------------+---------+
148 | access_id | access_level | access |
149 +-----------+--------------+---------+
150 | 1 | 0 | Denied |
151 | 2 | 10 | Guest |
152 | 3 | 20 | Author |
153 | 4 | 100 | Manager |
154 | 5 | 1000 | DBA |
155 +-----------+--------------+---------+
156 */
157
158
159 // Default no authorization
160 $auth_page = 0;
161
162
163 // User is an author. Must be page coordinator or a co-maintainer.
164 if ($sess_access_level == 20) {
165
166 $sql = "SELECT count(DISTINCT p.page_id) AS auth_page FROM
167 page p
168 LEFT JOIN page_staff ps using (page_id)
169 WHERE p.page_id = "
170 . $page_id
171 . " AND (p.staff_coordinator = "
172 . $sess_staff_id
173 . " OR ps.staff_id = "
174 . $sess_staff_id
175 . ")";
176
177 }
178
179
180 // User is a manager. Must be page coordinator, co-maintainer, or manager of
181 // the coordinator's unit.
182 else if ($sess_access_level == 100) {
183
184
185 // Determine libunit
186 $lu_sql = "SELECT libunit_id FROM libunit
187 WHERE head_staff_id = "
188 . $sess_staff_id;
189 $lu_rs = mysql_query($lu_sql, $con);
190
191 $lu_string = "ls.libunit_id IN (";
192 $first_element = 0;
193
194 // Concatenate the IN clause
195 while ($lu_row = mysql_fetch_array ($lu_rs)) {
196 $libunit_id = $lu_row["libunit_id"];
197 //printf("libunit id was: %d<BR><BR>", $libunit_id);
198
199 if ($first_element == 0) {
200 $first_element = 1;
201 $lu_string .= $libunit_id;
202 }
203 else $lu_string .= ", " . $libunit_id;
204 }
205
206 // Cleanup
207 $lu_string .= ") OR";
208
209 // If nothing found, then return a blank string
210 if ($first_element == 0) $lu_string = "";
211
212 $sql = "SELECT count(DISTINCT p.page_id) AS auth_page
213 FROM page p
214 LEFT JOIN page_staff ps using (page_id)
215 LEFT JOIN libunit_staff ls on p.staff_coordinator = ls.staff_id
216 WHERE p.page_id = "
217 . $page_id
218 . " AND ("
219 . $lu_string
220 . " p.staff_coordinator = "
221 . $sess_staff_id
222 . " OR ps.staff_id = "
223 . $sess_staff_id
224 . ")";
225 }
226
227
228
229 // DBA. Access everything.
230 else if ($sess_access_level == "1000") {
231 $auth_page = 1;
232 }
233
234 // Every other access level. No pages at all!
235 else {
236 $auth_page = 0;
237 }
238
239 // Run the authorized page query if not DBA level
240 if ($sess_access_level >= 20 && $sess_access_level < 1000) {
241
242 $rs = mysql_query($sql, $con);
243 $row = mysql_fetch_array ($rs);
244
245 // Collect the access information
246 $auth_page = $row["auth_page"];
247 }
248
249 if ($auth_page > 0) $auth_page = 1;
250
251 return $auth_page;
252
253 }
254
255
256 /**********************************************************
257 Function: authResourceDelete($con, $resource_id, $sess_access_level,
258 $sess_staff_account)
259 Author: Paul Bramscher
260 Last Modified: 08.26.2003
261 ***********************************************************
262 Incoming:
263 $con Database connection string
264 $resource_id Resource to test authorization
265 $sess_access_level Access level of the current user
266 session.
267 $sess_staff_account x500 id of the current user
268 ***********************************************************
269 Outgoing:
270 1 = authorized to work on this resource
271 0 = non-authorized.
272 ***********************************************************
273 Purpose:
274 A check to ensure whether the current user may delete the
275 supplied resource. The following rules apply:
276
277 (1) DBA's and managers can delete all resources
278 (2) Any staffperson with access greater than guest and less than
279 manager may delete only those resource s/he has created.
280 **********************************************************/
281 function authResourceDelete($con, $resource_id, $sess_access_level,
282 $sess_staff_account) {
283
284 /* Access Table Definitions
285 +-----------+--------------+---------+
286 | access_id | access_level | access |
287 +-----------+--------------+---------+
288 | 1 | 0 | Denied |
289 | 2 | 10 | Guest |
290 | 3 | 20 | Author |
291 | 4 | 100 | Manager |
292 | 5 | 1000 | DBA |
293 +-----------+--------------+---------+
294 */
295
296
297 // Default no authorization
298 $auth_resource = 0;
299
300 /* If wishing to enforce resource delete access against author access,
301 uncomment this portion -- it's been temporarily commented out to allow
302 conversion staff the ability to tweak resources.
303 */
304
305 // Scenario: guest access < THE USER < manager access
306 if ($sess_access_level > 10 && $sess_access_level < 100 ) {
307
308 $sql = "SELECT count(r.resource_id) AS auth_resource FROM
309 resource r
310 WHERE r.resource_id = "
311 . $resource_id
312 . " AND r.account_created = '"
313 . $sess_staff_account
314 . "'";
315
316 $rs = mysql_query($sql, $con);
317 $row = mysql_fetch_array ($rs);
318
319 // Collect the access information
320 $auth_resource = $row["auth_resource"];
321
322 if ($auth_resource > 0) $auth_resource = 1;
323 }
324
325 // Manager or higher access. Great edit/delete access for everything.
326 else if ($sess_access_level >= 100 ) {
327 $auth_resource = 1;
328 }
329
330
331 /* If wishing to enforce resource edit/delete access against author access,
332 uncomment the previous portion and delete this.
333 Note: 08.21.2003 - PFB. Open editing of resources by any author is now verboten.
334
335 // Author or higher access. Great edit/delete access for everything.
336 if ($sess_access_level >= "10" ) {
337 $auth_resource = 1;
338 }
339 */
340
341
342 // Every other access level. No resources at all!
343 else {
344 $auth_resource = 0;
345 }
346
347 return $auth_resource;
348
349 }
350
351
352 /**********************************************************
353 Function: authResourceEdit($con, $resource_id, $sess_access_level,
354 $sess_staff_account)
355 Author: Paul Bramscher
356 Last Modified: 08.26.2003
357 ***********************************************************
358 Incoming:
359 $con Database connection string
360 $resource_id Resource to test authorization
361 $sess_access_level Access level of the current user
362 session.
363 $sess_staff_account x500 id of the current user
364 ***********************************************************
365 Outgoing:
366 1 = authorized to work on this resource
367 0 = non-authorized.
368 ***********************************************************
369 Purpose:
370 A check to ensure whether the current user may edit the
371 supplied resource. The following rules apply:
372
373 (1) Authors and above may edit all resources
374 **********************************************************/
375 function authResourceEdit($con, $resource_id, $sess_access_level,
376 $sess_staff_account) {
377
378 /* Access Table Definitions
379 +-----------+--------------+---------+
380 | access_id | access_level | access |
381 +-----------+--------------+---------+
382 | 1 | 0 | Denied |
383 | 2 | 10 | Guest |
384 | 3 | 20 | Author |
385 | 4 | 100 | Manager |
386 | 5 | 1000 | DBA |
387 +-----------+--------------+---------+
388 */
389
390
391 // Default no authorization
392 $auth_resource = 0;
393
394 /* If wishing to enforce resource edit/delete access against author access,
395 uncomment this portion -- it's been temporarily commented out to allow
396 conversion staff the ability to tweak resources.
397 Note: 08.21.2003 - PFB. Resource editing access is now being enforced again.
398
399
400 // Scenario: guest access < THE USER < manager access
401 if ($sess_access_level > 10 && $sess_access_level < 100 ) {
402
403 $sql = "SELECT count(r.resource_id) AS auth_resource FROM
404 resource r
405 WHERE r.resource_id = "
406 . $resource_id
407 . " AND r.account_created = '"
408 . $sess_staff_account
409 . "'";
410
411 $rs = mysql_query($sql, $con);
412 $row = mysql_fetch_array ($rs);
413
414 // Collect the access information
415 $auth_resource = $row["auth_resource"];
416
417 if ($auth_resource > 0) $auth_resource = 1;
418 }
419
420
421
422 // Manager or higher access. Great edit/delete access for everything.
423 else if ($sess_access_level >= "100" ) {
424 $auth_resource = 1;
425 }
426 */
427
428 /* If wishing to enforce resource edit/delete access against author access,
429 uncomment the previous portion and delete this.
430 Note: 08.21.2003 - PFB. Open editing of resources by any author is now verboten.
431 Note: 08.26.2003 - PFB. Editing of resources is allowed, but not deleting.
432 */
433
434 // Author or higher access. Great edit/delete access for everything.
435 if ($sess_access_level >= 10 ) {
436 $auth_resource = 1;
437 }
438
439
440 // Every other access level. No resources at all!
441 else {
442 $auth_resource = 0;
443 }
444
445 // Testing
446 // printf("sql was: %s<BR>", $sql);
447 // printf("sess acl: %s<BR>", $sess_access_level);
448 // printf("sess_staff_account: %s<BR>", $sess_staff_account);
449 // printf("auth res: %s<br>", $auth_resource);
450
451 return $auth_resource;
452
453 }
454
455
456 /**********************************************************
457 Function: authSubject($con, $sess_access_level, $sess_staff_id,
458 $subject_id)
459 Author: Paul Bramscher
460 Last Modified: 01.22.2003
461 ***********************************************************
462 Incoming:
463 $con Database connection string
464 $sess_access_level Access level of the current user
465 session.
466 $sess_staff_id staff id of the current user
467 $subject_id Subject to test authorization
468 ***********************************************************
469 Outgoing:
470 1 = authorized to work on this subject
471 0 = non-authorized.
472 ***********************************************************
473 Purpose:
474 A check to ensure whether the current user may access the
475 supplied subject. The following rules apply:
476
477 (1) DBA's can edit anything.
478 (2) Managers can edit subjects maintained by anyone within their unit,
479 subjects to which they are personally assigned.
480 (3) Any staffperson, regardless of access, may edit subjects to
481 which they are assigned.
482
483
484 **********************************************************/
485 function authSubject($con, $sess_access_level, $sess_staff_id,
486 $subject_id){
487
488 /* Access Table Definitions
489 +-----------+--------------+---------+
490 | access_id | access_level | access |
491 +-----------+--------------+---------+
492 | 1 | 0 | Denied |
493 | 2 | 10 | Guest |
494 | 3 | 20 | Author |
495 | 4 | 100 | Manager |
496 | 5 | 1000 | DBA |
497 +-----------+--------------+---------+
498 */
499
500
501 // Default no authorization
502 $auth_subject = 0;
503
504
505 // Scenario: guest access < THE USER < manager access
506 if ($sess_access_level > 10 && $sess_access_level < 100 ) {
507
508 $sql = "SELECT count(DISTINCT s.subject_id) AS auth_subject FROM
509 subject s
510 LEFT JOIN sub_staff st using (subject_id)
511 WHERE s.subject_id = "
512 . $subject_id
513 . " AND st.staff_id = "
514 . $sess_staff_id;
515
516 }
517
518
519 // User is a manager.
520 else if ($sess_access_level == 100) {
521
522
523 // Determine libunit
524 $lu_sql = "SELECT libunit_id FROM libunit
525 WHERE head_staff_id = "
526 . $sess_staff_id;
527 $lu_rs = mysql_query($lu_sql, $con);
528
529 $lu_string = "ls.libunit_id IN (";
530 $first_element = 0;
531
532 // Concatenate the IN clause
533 while ($lu_row = mysql_fetch_array ($lu_rs)) {
534 $libunit_id = $lu_row["libunit_id"];
535 //printf("libunit id was: %d<BR><BR>", $libunit_id);
536
537 if ($first_element == 0) {
538 $first_element = 1;
539 $lu_string .= $libunit_id;
540 }
541 else $lu_string .= ", " . $libunit_id;
542 }
543
544 // Cleanup
545 $lu_string .= ") OR";
546
547 // If nothing found, then return a blank string
548 if ($first_element == 0) $lu_string = "";
549
550
551 $sql = "SELECT count(DISTINCT s.subject_id) AS auth_subject FROM
552 subject s
553 LEFT JOIN sub_staff st using (subject_id)
554 LEFT JOIN libunit_staff ls on st.staff_id = ls.staff_id
555 WHERE s.subject_id = "
556 . $subject_id
557 . " AND ("
558 . $lu_string
559 . " st.staff_id = "
560 . $sess_staff_id
561 . ")";
562 }
563
564
565
566 // DBA. Access everything.
567 else if ($sess_access_level == "1000") {
568 $auth_subject = 1;
569 }
570
571 // Every other access level. No subjects at all!
572 else {
573 $auth_subject = 0;
574 }
575
576 // Testing
577 // printf("sql was: %s<BR>", $sql);
578
579 // Run the authorized subject query if not DBA level
580 if ($sess_access_level >= 20 && $sess_access_level < 1000) {
581
582 $rs = mysql_query($sql, $con);
583 $row = mysql_fetch_array ($rs);
584
585 // Collect the access information
586 $auth_subject = $row["auth_subject"];
587 }
588
589 if ($auth_subject > 0) $auth_subject = 1;
590
591 return $auth_subject;
592
593 }
594
595
596 /**********************************************************
597 Function: commaList($con, $displayField, $sql)
598 Author: Paul Bramscher
599 Last Modified: 07.02.2002
600 ***********************************************************
601 Incoming:
602 $con Database connection string
603 $displayField Field to display
604 $sql SQL to execute
605 ***********************************************************
606 Outgoing:
607 None
608 ***********************************************************
609 Purpose:
610 Performs a lookup similar to lookupField, but instead of
611 returning the descriptive value for an ID field, it returns
612 a comma-separated list of results.
613 **********************************************************/
614 function commaList($con, $displayField, $sql){
615 $rs = mysql_query($sql, $con);
616
617 $first = 1;
618 while ($row = mysql_fetch_array ($rs)) {
619 $result = $row[$displayField];
620 if ($first == 1) {
621 $first = 0;
622 printf("%s", $result);
623 }
624 else printf(", %s", $result);
625 }
626 }
627
628
629 /**********************************************************
630 Function: displayKeys($con, $table, $field_display,
631 $field_value, $limit)
632 Author: Paul Bramscher
633 Last Modified: 7.26.2001
634 ***********************************************************
635 Incoming:
636 $con Database connection string
637 $table Table in database to search
638 $field_display Select displayed to user
639 $field_value Actual value of the HTML tag
640 $limit Field to limit $field_display by
641 ***********************************************************
642 Outgoing:
643 None
644 ***********************************************************
645 Purpose:
646 Does a simple text dump to show descriptive values and
647 their associated primary keys in a small table. If
648 $limit is defined, then limit the result set to display
649 only $field_display which matches a like cause.
650 **********************************************************/
651 function displayKeys($con, $table, $field_display,
652 $field_value, $limit){
653
654 // Build the sql
655 $sql = "SELECT " . $field_display . ", "
656 . $field_value . " FROM " . $table;
657 if (strlen($limit) > 1){
658 $sql .= " WHERE "
659 . $field_display
660 . " LIKE '%"
661 . $limit
662 . "%'";
663 }
664
665 // Order the display
666 $sql .= " ORDER BY " . $field_display;
667
668 $rs = mysql_query($sql, $con);
669
670 printf("<td><b>Description</b></td>");
671 printf("<td><b>Key</b></td></tr>");
672
673 while ($row = mysql_fetch_array ($rs)) {
674 $field_display_item = $row[$field_display];
675 $field_value_item = $row[$field_value];
676 printf("<tr><td>"
677 . $field_display_item
678 . "</td><td>"
679 . $field_value_item
680 . "</td></tr>");
681 };
682 }
683
684
685 /**********************************************************
686 Function: displayRelatedPages($con, $subject_id)
687 Author: Paul Bramscher
688 Last Modified: 06.17.2003
689 ***********************************************************
690 Incoming:
691 $con Database connection string
692 $subject_id Subject of the RQS page
693 ***********************************************************
694 Outgoing:
695 None
696 ***********************************************************
697 Purpose:
698 Displays any PageScribe pages associated with the supplied
699 subject id.
700 **********************************************************/
701 function displayRelatedPages($con, $subject_id){
702 $sql = "SELECT p.page_id, p.page_title
703 FROM page p, sub_page sp
704 WHERE sp.subject_id = "
705 . $subject_id
706 . " AND sp.page_id = p.page_id ORDER BY p.page_title";
707
708 $rs = mysql_query($sql, $con);
709 while ($row = mysql_fetch_array ($rs)) {
710 $page_id = $row["page_id"];
711 $page_title = $row["page_title"];
712
713 // Pull out any HTML
714 $page_title = strip_tags($page_title);
715
716 printf("<a href=\"page.phtml?page_id=%s\">%s</a><BR>\n", $page_id, $page_title);
717 };
718 }
719
720
721 /**********************************************************
722 Function: dropDownAccountOmit($con, $omit)
723 Author: Paul Bramscher
724 Last Modified: 04.01.2003
725 ***********************************************************
726 Incoming:
727 $con Database connection string
728 $omit String of staff's to omit
729 ***********************************************************
730 Outgoing:
731 None
732 ***********************************************************
733 Purpose:
734 Populates a drop-down box on an HTML form with select
735 statements. $omit limits output. Similar to dropDownStaffOmit,
736 but instead of returning the staff_id as the HTML value,
737 it returns the staff_account. This was done for situations in
738 which a relational tie with the staff table is not desirable
739 after the value is inserted. For example, historical statistics.
740 **********************************************************/
741 function dropDownAccountOmit($con, $omit){
742 $sql = "SELECT * from staff " . $omit .
743 " ORDER BY last_name, first_name";
744
745 $rs = mysql_query($sql, $con);
746 while ($row = mysql_fetch_array ($rs)) {
747 $last_name = $row["last_name"];
748 $first_name = $row["first_name"];
749 $staff_id = $row["staff_id"];
750 $staff_account = $row["staff_account"];
751
752 // Make it look more friendly
753 if ($staff_id == 1) $staff = "(N/A)";
754 else $staff = $last_name . ", " . $first_name . " (" . $staff_account . ")";
755 printf("<option value = \"" . $staff_account
756 . "\" >" . $staff . "</option>\n");
757 };
758 }
759
760
761 /**********************************************************
762 Function: dropDownAuthCourses($con, $sess_access_level, $sess_staff_id)
763 Author: Paul Bramscher
764 Last Modified: 01.07.2003
765 ***********************************************************
766 Incoming:
767 $con Database connection string
768 $sess_access_level Access level of the current user
769 session.
770 $sess_staff_id staff id of the current user
771 ***********************************************************
772 Outgoing:
773 None
774 ***********************************************************
775 Purpose:
776 Identical to dropDownAuthPages, except for a pagetype_id = 3
777 and extra LEFT JOIN to the course table to fetch the
778 course_concat field as the course title.
779
780 Populates a drop-down box on an HTML form with select
781 options of CourseScribe pages that the current
782 user has authorization to edit. The following rules apply:
783
784 (1) DBA's can edit anything.
785 (2) Managers can edit pages created by anyone within their unit.
786 (3) Page coordinators can edit pages they coordinate.
787 (4) Page maintainers may also edit pages they are assigned to.
788
789 Some scenarios of pages that are NOT editable:
790
791 (1) The original page creator is no longer the coordinator,
792 not a DBA, and not assigned as a maintainer. S/he can no
793 longer edit the page.
794 (2) You are a unit managager and used to have access to a page
795 coordinated by one of your employees. S/he switches units,
796 and is now under a new manager. The course page can now be
797 accessed by the manager of the new unit and not yourself.
798
799 etc...
800
801 **********************************************************/
802 function dropDownAuthCourses($con, $sess_access_level, $sess_staff_id){
803
804 /* Access Table Definitions
805 +-----------+--------------+---------+
806 | access_id | access_level | access |
807 +-----------+--------------+---------+
808 | 1 | 0 | Denied |
809 | 2 | 10 | Guest |
810 | 3 | 20 | Author |
811 | 4 | 100 | Manager |
812 | 5 | 1000 | DBA |
813 +-----------+--------------+---------+
814 */
815
816
817 // Behave differently based on access level
818 switch ($sess_access_level) {
819
820 // Author. Start from the page table.
821 case "20" :
822 $page_sql = "SELECT DISTINCT
823 p.page_id,
824 c.course_concat
825 FROM page p
826 LEFT JOIN page_staff ps using (page_id)
827 LEFT JOIN course c on p.page_id = c.page_id
828 WHERE p.pagetype_id = 3
829 AND
830 (ps.staff_id = "
831 . $sess_staff_id
832 . " OR p.staff_coordinator = "
833 . $sess_staff_id
834 . ") ORDER BY c.course_concat";
835 break;
836
837 // Manager. Start from the libunit table, work down to pages
838 case "100" :
839
840 // Determine libunit
841 $lu_sql = "SELECT libunit_id FROM libunit
842 WHERE head_staff_id = "
843 . $sess_staff_id;
844 $lu_rs = mysql_query($lu_sql, $con);
845
846 $lu_string = "ls.libunit_id IN (";
847 $first_element = 0;
848
849 // Concatenate the IN clause
850 while ($lu_row = mysql_fetch_array ($lu_rs)) {
851 $libunit_id = $lu_row["libunit_id"];
852
853 if ($first_element == 0) {
854 $first_element = 1;
855 $lu_string .= $libunit_id;
856 }
857 else $lu_string .= ", " . $libunit_id;
858 }
859
860 // Cleanup
861 $lu_string .= ") OR";
862
863 // If nothing found, then return a blank string
864 if ($first_element == 0) $lu_string = "";
865
866 $page_sql = "SELECT DISTINCT
867 p.page_id,
868 c.course_concat
869 FROM page p
870 LEFT JOIN page_staff ps using (page_id)
871 LEFT JOIN course c on p.page_id = c.page_id
872 LEFT JOIN libunit_staff ls on p.staff_coordinator = ls.staff_id
873 WHERE p.pagetype_id = 3
874 AND ("
875 . $lu_string
876 . " ps.staff_id = "
877 . $sess_staff_id
878 . " OR p.staff_coordinator = "
879 . $sess_staff_id
880 . ") ORDER BY c.course_concat";
881 break;
882
883 // DBA. Access everything.
884 case "1000" :
885 dropDownCourses($con);
886 break;
887
888 // Every other access level. No pages at all!
889 default :
890 break;
891
892 }
893
894 // Run the authorized page query if not DBA level
895 if ($sess_access_level >= 20 && $sess_access_level < 1000) {
896
897 $page_rs = mysql_query($page_sql, $con);
898
899 while ($page_row = mysql_fetch_array ($page_rs)) {
900
901 // Collect the page information
902 $page_id = $page_row["page_id"];
903 $course_concat = $page_row["course_concat"];
904
905 if (strlen($course_concat) > 45) $course_concat = substr($course_concat, 0, 45) . "...";
906
907 // Print the options
908 printf("<option value=\"%d\">%s</option>\n", $page_id, $course_concat);
909 }
910
911 }
912
913 }
914
915
916 /**********************************************************
917 Function: dropDownAuthPages($con, $sess_access_level, $sess_staff_id)
918 Author: Paul Bramscher
919 Last Modified: 01.07.2003
920 ***********************************************************
921 Incoming:
922 $con Database connection string
923 $sess_access_level Access level of the current user
924 session.
925 $sess_staff_id staff id of the current user
926 ***********************************************************
927 Outgoing:
928 None
929 ***********************************************************
930 Purpose:
931 Populates a drop-down box on an HTML form with select
932 options of PageScribe pages that the current
933 user has authorization to edit. The following rules apply:
934
935 (1) DBA's can edit anything.
936 (2) Managers can edit pages created by anyone within their unit.
937 (3) Page coordinators can edit pages they coordinate.
938 (4) Page maintainers may also edit pages they are assigned to.
939
940 Some scenarios of pages that are NOT editable:
941
942 (1) The original page creator is no longer the coordinator,
943 not a DBA, and not assigned as a maintainer. S/he can no
944 longer edit the page.
945 (2) You are a unit managager and used to have access to a page
946 coordinated by one of your employees. S/he switches units,
947 and is now under a new manager. The page can now be accessed
948 by the manager of the new unit and not yourself.
949
950 etc...
951
952 **********************************************************/
953 function dropDownAuthPages($con, $sess_access_level, $sess_staff_id){
954
955 /* Access Table Definitions
956 +-----------+--------------+---------+
957 | access_id | access_level | access |
958 +-----------+--------------+---------+
959 | 1 | 0 | Denied |
960 | 2 | 10 | Guest |
961 | 3 | 20 | Author |
962 | 4 | 100 | Manager |
963 | 5 | 1000 | DBA |
964 +-----------+--------------+---------+
965 */
966
967
968 // Behave differently based on access level
969 switch ($sess_access_level) {
970
971 // Editor. Start from the page table.
972 case "20" :
973 $page_sql = "SELECT DISTINCT
974 p.page_id,
975 p.page_title
976 FROM page p
977 LEFT JOIN page_staff ps using (page_id)
978 WHERE p.pagetype_id = 2
979 AND
980 (ps.staff_id = "
981 . $sess_staff_id
982 . " OR p.staff_coordinator = "
983 . $sess_staff_id
984 . ") ORDER BY p.page_title";
985 break;
986
987 // Manager. Start from the libunit table, work down to pages
988 case "100" :
989
990 // Determine libunit
991 $lu_sql = "SELECT libunit_id FROM libunit
992 WHERE head_staff_id = "
993 . $sess_staff_id;
994 $lu_rs = mysql_query($lu_sql, $con);
995
996 $lu_string = "ls.libunit_id IN (";
997 $first_element = 0;
998
999 // Concatenate the IN clause
1000 while ($lu_row = mysql_fetch_array ($lu_rs)) {
1001 $libunit_id = $lu_row["libunit_id"];
1002 printf("libunit id was: %d<BR><BR>", $libunit_id);
1003
1004 if ($first_element == 0) {
1005 $first_element = 1;
1006 $lu_string .= $libunit_id;
1007 }
1008 else $lu_string .= ", " . $libunit_id;
1009 }
1010
1011 // Cleanup
1012 $lu_string .= ") OR";
1013
1014 // If nothing found, then return a blank string
1015 if ($first_element == 0) $lu_string = "";
1016
1017 $page_sql = "SELECT DISTINCT
1018 p.page_id,
1019 p.page_title
1020 FROM page p
1021 LEFT JOIN page_staff ps using (page_id)
1022 LEFT JOIN libunit_staff ls on p.staff_coordinator = ls.staff_id
1023 WHERE p.pagetype_id = 2
1024 AND ("
1025 . $lu_string
1026 . " ps.staff_id = "
1027 . $sess_staff_id
1028 . " OR p.staff_coordinator = "
1029 . $sess_staff_id
1030 . ") ORDER BY p.page_title";
1031 break;
1032
1033 // DBA. Access everything.
1034 case "1000" :
1035 dropDownFieldOmit($con, "page", "page_title", "page_id", " WHERE pagetype_id = 2");
1036 break;
1037
1038 // Every other access level. No pages at all!
1039 default :
1040 break;
1041
1042 }
1043
1044 // Run the authorized page query if not DBA level
1045 if ($sess_access_level >= 20 && $sess_access_level < 1000) {
1046
1047 $page_rs = mysql_query($page_sql, $con);
1048
1049 while ($page_row = mysql_fetch_array ($page_rs)) {
1050
1051 // Collect the page information
1052 $page_id = $page_row["page_id"];
1053 $page_title = $page_row["page_title"];
1054
1055 if (strlen($page_title) > 39) $page_title = substr($page_title, 0, 39) . "...";
1056
1057 // Print the options
1058 printf("<option value=\"%d\">%s</option>\n", $page_id, $page_title);
1059 }
1060
1061
1062 // printf("sql was: %s<BR>", $page_sql);
1063
1064 }
1065
1066 }
1067
1068
1069 /**********************************************************
1070 Function: dropDownAuthSubjects($con, $sess_access_level, $sess_staff_id)
1071 Author: Paul Bramscher
1072 Last Modified: 01.22.2003
1073 ***********************************************************
1074 Incoming:
1075 $con Database connection string
1076 $sess_access_level Access level of the current user
1077 session.
1078 $sess_staff_id staff id of the current user
1079 ***********************************************************
1080 Outgoing:
1081 None
1082 ***********************************************************
1083 Purpose:
1084 Populates a drop-down box on an HTML form with select
1085 options of SubjectBuilder pages that the current
1086 user has authorization to edit. The following rules apply:
1087
1088 (1) DBA's can edit anything.
1089 (2) Managers can edit subjects on behalf of anyone within their unit.
1090 (3) Any staffperson can manage subjects to which s/he is assigned.
1091
1092 **********************************************************/
1093 function dropDownAuthSubjects($con, $sess_access_level, $sess_staff_id){
1094
1095 /* Access Table Definitions
1096 +-----------+--------------+---------+
1097 | access_id | access_level | access |
1098 +-----------+--------------+---------+
1099 | 1 | 0 | Denied |
1100 | 2 | 10 | Guest |
1101 | 3 | 20 | Author |
1102 | 4 | 100 | Manager |
1103 | 5 | 1000 | DBA |
1104 +-----------+--------------+---------+
1105 */
1106
1107
1108 // Behave differently based on access level
1109 switch ($sess_access_level) {
1110
1111 // Editor. Start from the subb_staff table.
1112 case "20" :
1113 $sql = "SELECT
1114 s.subject_id,
1115 s.subject
1116 FROM subject s
1117 LEFT JOIN sub_staff ss using (subject_id)
1118 WHERE ss.staff_id = "
1119 . $sess_staff_id
1120 . " ORDER BY s.subject";
1121 break;
1122
1123 // Manager. Start from the libunit table, work down to pages
1124 case "100" :
1125
1126 // Determine libunit
1127 $lu_sql = "SELECT libunit_id FROM libunit
1128 WHERE head_staff_id = "
1129 . $sess_staff_id;
1130 $lu_rs = mysql_query($lu_sql, $con);
1131
1132 $lu_string = "ls.libunit_id IN (";
1133 $first_element = 0;
1134
1135 // Concatenate the IN clause
1136 while ($lu_row = mysql_fetch_array ($lu_rs)) {
1137 $libunit_id = $lu_row["libunit_id"];
1138 printf("libunit id was: %d<BR><BR>", $libunit_id);
1139
1140 if ($first_element == 0) {
1141 $first_element = 1;
1142 $lu_string .= $libunit_id;
1143 }
1144 else $lu_string .= ", " . $libunit_id;
1145 }
1146
1147 // Cleanup
1148 $lu_string .= ") OR";
1149
1150 // If nothing found, then return a blank string
1151 if ($first_element == 0) $lu_string = "";
1152
1153 $sql = "SELECT DISTINCT
1154 s.subject_id,
1155 s.subject
1156 FROM subject s
1157 LEFT JOIN sub_staff ss using (subject_id)
1158 LEFT JOIN libunit_staff ls on ss.staff_id = ls.staff_id
1159 WHERE "
1160 . $lu_string
1161 . " ss.staff_id = "
1162 . $sess_staff_id
1163 . " ORDER BY s.subject";
1164
1165 break;
1166
1167 // DBA. Access everything.
1168 case "1000" :
1169 dropDownFieldOmit($con, "subject", "subject", "subject_id", "WHERE SUBJECT_ID > 1");
1170 break;
1171
1172 // Every other access level. No pages at all!
1173 default :
1174 break;
1175
1176 }
1177
1178 // Run the authorized page query if not DBA level
1179 if ($sess_access_level >= 20 && $sess_access_level < 1000) {
1180
1181 $rs = mysql_query($sql, $con);
1182
1183 while ($row = mysql_fetch_array ($rs)) {
1184
1185 // Collect the page information
1186 $subject_id = $row["subject_id"];
1187 $subject = $row["subject"];
1188
1189 // Print the option
1190 printf("<option value=\"%d\">%s</option>\n", $subject_id, $subject);
1191 }
1192
1193
1194 printf("sql was: %s<BR>", $sql);
1195
1196 }
1197
1198 }
1199
1200
1201 /**********************************************************
1202 Function: dropDownCourses($con)
1203 Author: Paul Bramscher
1204 Last Modified: 11.26.2003
1205 ***********************************************************
1206 Incoming:
1207 $con Database connection string
1208 ***********************************************************
1209 Outgoing:
1210 None
1211 ***********************************************************
1212 Purpose:
1213 Populates a drop-down box on an HTML form with courseScribe
1214 courses, listed by course name and designator.
1215 **********************************************************/
1216 function dropDownCourses($con) {
1217
1218 $sql = "SELECT page_id, course_concat
1219 FROM course
1220 ORDER BY course_concat";
1221
1222 $rs = mysql_query($sql, $con);
1223 while ($row = mysql_fetch_array ($rs)) {
1224 $page_id = $row["page_id"];
1225 $course_concat = $row["course_concat"];
1226
1227 if (strlen($course_concat) > 45) $course_concat = substr($course_concat, 0, 45) . "...";
1228
1229 printf("<option value = \"" . $page_id
1230 . "\" >" . $course_concat . "</option>\n");
1231 };
1232 }
1233
1234
1235 /**********************************************************
1236 Function: dropDownCoursesubOmit($con, $omit)
1237 Author: Paul Bramscher
1238 Last Modified: 06.04.2003
1239 ***********************************************************
1240 Incoming:
1241 $con Database connection string
1242 $omit String of coursesub's to omit
1243 ***********************************************************
1244 Outgoing:
1245 None
1246 ***********************************************************
1247 Purpose:
1248 Populates a drop-down box on an HTML form with select
1249 statements. $omit limits output.
1250 **********************************************************/
1251 function dropDownCoursesubOmit($con, $omit){
1252
1253 $sql = "SELECT coursesub, coursesub_descr, coursesub_id, cip_code FROM
1254 coursesub "
1255 . $omit
1256 . " ORDER BY coursesub_descr";
1257
1258 // Fetch the values
1259 $rs = mysql_query($sql, $con);
1260 while ($row = mysql_fetch_array ($rs)) {
1261 $coursesub = $row["coursesub"];
1262 $coursesub_id = $row["coursesub_id"];
1263 $coursesub_descr = $row["coursesub_descr"];
1264 $cip_code = $row["cip_code"];
1265
1266 // Limit length
1267 if (strlen($coursesub_descr) > 30) $coursesub_descr = substr($coursesub_descr, 0, 30) . "...";
1268
1269 printf("<option value = \"%s\">%s | %s [%s]</option>", $coursesub_id, $coursesub, $coursesub_descr, $cip_code);
1270 }
1271 }
1272
1273
1274 /**********************************************************
1275 Function: dropDownCoursesubSelected($con, $limit, $preselected)
1276 Author: Paul Bramscher
1277 Last Modified: 08.18.2003
1278 ***********************************************************
1279 Incoming:
1280 $con Database connection string
1281 $limit Additional limit on the box
1282 $preselected Pre-selected course subject
1283 ***********************************************************
1284 Outgoing:
1285 None
1286 ***********************************************************
1287 Purpose:
1288 Populates a drop-down box on an HTML form with select
1289 statements. $limit limits output.
1290 **********************************************************/
1291 function dropDownCoursesubSelected($con, $limit, $preselected){
1292
1293 $sql = "SELECT
1294 coursesub,
1295 coursesub_descr,
1296 coursesub_id,
1297 cip_code
1298 FROM coursesub ";
1299
1300 // Concatenate a limit if provided
1301 if (strlen($limit) > 0) $sql .= $limit;
1302
1303 $sql .= " ORDER BY coursesub_descr";
1304
1305 // Fetch the values
1306 $rs = mysql_query($sql, $con);
1307 while ($row = mysql_fetch_array ($rs)) {
1308 $coursesub = $row["coursesub"];
1309 $coursesub_id = $row["coursesub_id"];
1310 $coursesub_descr = $row["coursesub_descr"];
1311 $cip_code = $row["cip_code"];
1312
1313 // Limit length
1314 if (strlen($coursesub_descr) > 25) $coursesub_descr = substr($coursesub_descr, 0, 30) . "...";
1315
1316 printf("<option value = \"%d\"", $coursesub_id);
1317 if ($coursesub_id == $preselected) printf(" selected ");
1318 printf(">" . $coursesub . " | " . $coursesub_descr . " [" . $cip_code . "]</option>\n");
1319
1320 }
1321 }
1322
1323
1324 /**********************************************************
1325 Function: dropDownFaculty($con)
1326 Author: Paul Bramscher
1327 Last Modified: 12.02.2002
1328 ***********************************************************
1329 Incoming:
1330 $con Database connection string
1331 ***********************************************************
1332 Outgoing:
1333 None
1334 ***********************************************************
1335 Purpose:
1336 Populates a drop-down box on an HTML form with select
1337 options of faculty members. They are displayed and ordered
1338 in the following format: "last name, first name (x500id)".
1339 **********************************************************/
1340 function dropDownFaculty($con){
1341 // Build the SQL.
1342 $sql = "SELECT *
1343 FROM faculty
1344 ORDER BY faculty_lastname, faculty_firstname, faculty_account";
1345
1346 $rs = mysql_query($sql, $con);
1347 while ($row = mysql_fetch_array ($rs)) {
1348 $faculty_id = $row["faculty_id"];
1349 $faculty_lastname = $row["faculty_lastname"];
1350 $faculty_firstname = $row["faculty_firstname"];
1351 $faculty_account = $row["faculty_account"];
1352
1353 // Make it look more friendly
1354 if ($faculty_id == 1) $faculty = "(N/A)";
1355 else {
1356 $faculty = $faculty_lastname . ", " . $faculty_firstname;
1357 if (strlen($faculty_account) > 0) $faculty .= " (" . $faculty_account . ")";
1358 }
1359
1360 printf("<option value = \"" . $faculty_id
1361 . "\" >" . $faculty . "</option>\n");
1362 };
1363 }
1364
1365
1366 /**********************************************************
1367 Function: dropDownFacultyOmit($con, $omit)
1368 Author: Paul Bramscher
1369 Last Modified: 12.12.2002
1370 ***********************************************************
1371 Incoming:
1372 $con Database connection string
1373 $omit String of human's to omit
1374 ***********************************************************
1375 Outgoing:
1376 None
1377 ***********************************************************
1378 Purpose:
1379 Populates a drop-down box on an HTML form with select
1380 statements. $omit limits output.
1381 **********************************************************/
1382 function dropDownFacultyOmit($con, $omit){
1383 $sql = "SELECT * FROM faculty "
1384 . $omit
1385 . " ORDER BY faculty_lastname, faculty_firstname";
1386
1387 $rs = mysql_query($sql, $con);
1388 while ($row = mysql_fetch_array ($rs)) {
1389 $faculty_id = $row["faculty_id"];
1390 $faculty_lastname = $row["faculty_lastname"];
1391 $faculty_firstname = $row["faculty_firstname"];
1392 $faculty_account = $row["faculty_account"];
1393
1394 // Make it look more friendly
1395 if ($faculty_id == 1) $faculty = "(N/A)";
1396 else {
1397 $faculty = $faculty_lastname . ", " . $faculty_firstname;
1398 if (strlen($faculty_account) > 0) $faculty .= " (" . $faculty_account . ")";
1399 }
1400
1401 printf("<option value = \"" . $faculty_id
1402 . "\" >" . $faculty . "</option>\n");
1403 };
1404 }
1405
1406
1407 /**********************************************************
1408 Function: dropDownFacultySelected($con, $limit, $preselected)
1409 Author: Paul Bramscher
1410 Last Modified: 12.12.2002
1411 ***********************************************************
1412 Incoming:
1413 $con Database connection string
1414 $limit Any WHERE clause
1415 $preselected Incoming faculty person to preselect
1416 ***********************************************************
1417 Outgoing:
1418 None
1419 ***********************************************************
1420 Purpose:
1421 Populates a drop-down box on an HTML form with select
1422 statements. $omit limits output.
1423 **********************************************************/
1424 function dropDownFacultySelected($con, $limit, $preselected){
1425 $sql = "SELECT * FROM faculty "
1426 . $limit
1427 . " ORDER BY faculty_lastname, faculty_firstname";
1428
1429 $rs = mysql_query($sql, $con);
1430 while ($row = mysql_fetch_array ($rs)) {
1431 $faculty_id = $row["faculty_id"];
1432 $faculty_lastname = $row["faculty_lastname"];
1433 $faculty_firstname = $row["faculty_firstname"];
1434 $faculty_account = $row["faculty_account"];
1435
1436 // Make it look more friendly
1437 if ($faculty_id == 1) $faculty = "(N/A)";
1438 else {
1439 $faculty = $faculty_lastname . ", " . $faculty_firstname;
1440 }
1441
1442 printf("<option value = \"%d\"", $faculty_id);
1443 if ($faculty_id == $preselected) printf(" selected ");
1444 printf(">" . $faculty . "</option>\n");
1445 };
1446 }
1447
1448
1449 /**********************************************************
1450 Function: dropDownField($con, $table, $field_display,
1451 $field_value)
1452 Author: Paul Bramscher
1453 Last Modified: 7.26.2000
1454 ***********************************************************
1455 Incoming:
1456 $con Database connection string
1457 $table Table in database to search
1458 $field_display Select displayed to user
1459 $field_value Actual value of the HTML tag
1460 ***********************************************************
1461 Outgoing:
1462 None
1463 ***********************************************************
1464 Purpose:
1465 Populates a drop-down box on an HTML form with select
1466 options. They are ordered by the $field_display field.
1467 Typically, $field_value is the primary key field.
1468 **********************************************************/
1469 function dropDownField($con, $table, $field_display, $field_value){
1470
1471 $sql = "SELECT "
1472 . $field_display
1473 . ", "
1474 . $field_value
1475 . " FROM "
1476 . $table
1477 . " ORDER BY "
1478 . $field_display;
1479
1480 $rs = mysql_query($sql, $con);
1481 while ($row = mysql_fetch_array ($rs)) {
1482 $field_display_item = $row[$field_display];
1483 if (strlen($field_display_item) > 40) $field_display_item = substr($field_display_item, 0, 39) . "...";
1484 $field_value_item = $row[$field_value];
1485 printf("<option value = \"" . $field_value_item
1486 . "\" >" . $field_display_item . "</option>\n");
1487 };
1488 }
1489
1490
1491 /**********************************************************
1492 Function: dropDownFieldOmit($con, $table, $field_display,
1493 $field_value, $where)
1494 Author: Paul Bramscher
1495 Last Modified: 8.14.2002
1496 ***********************************************************
1497 Incoming:
1498 $con Database connection string
1499 $table Table in database to search
1500 $field_display Select displayed to user
1501 $field_value Actual value of the HTML tag
1502 $where SQL criteria in the list to exlude
1503 ***********************************************************
1504 Outgoing:
1505 None
1506 ***********************************************************
1507 Purpose:
1508 Populates a drop-down box on an HTML form with select
1509 options. They are ordered by the $field_display field.
1510 Typically, $field_value is the primary key field. $where
1511 can be used to filter out results.
1512 **********************************************************/
1513 function dropDownFieldOmit($con, $table, $field_display,
1514 $field_value, $where){
1515
1516 $sql = "SELECT "
1517 . $field_display
1518 . ", "
1519 . $field_value
1520 . " FROM "
1521 . $table
1522 . " "
1523 . $where
1524 . " ORDER BY "
1525 . $field_display;
1526 $rs = mysql_query($sql, $con);
1527 while ($row = mysql_fetch_array ($rs)) {
1528 $field_display_item = $row[$field_display];
1529 if (strlen($field_display_item) > 40) $field_display_item = substr($field_display_item, 0, 39) . "...";
1530 $field_value_item = $row[$field_value];
1531 printf("<option value = \"" . $field_value_item
1532 . "\" >" . $field_display_item . "</option>\n");
1533 };
1534 }
1535
1536
1537 /**********************************************************
1538 Function: dropDownFieldSelected($con, $table, $field_display,
1539 $field_value, $limit, $preselected)
1540 Author: Paul Bramscher
1541 Last Modified: 12.04.2002
1542 ***********************************************************
1543 Incoming:
1544 $con Database connection string
1545 $table Table in database to search
1546 $field_display Select displayed to user
1547 $field_value Actual value of the HTML tag
1548 $preselected A selected $field_value
1549 $limit A WHERE clause
1550 ***********************************************************
1551 Outgoing:
1552 None
1553 ***********************************************************
1554 Purpose:
1555 Populates a drop-down box on an HTML form with select
1556 options. They are ordered by the $field_display field.
1557 Typically, $field_value is the primary key field. The
1558 parameter $preselected determines which (single) selection
1559 is selected.
1560 **********************************************************/
1561 function dropDownFieldSelected($con, $table, $field_display,
1562 $field_value, $limit, $preselected){
1563
1564 $sql = "SELECT "
1565 . $field_display
1566 . ", "
1567 . $field_value
1568 . " FROM "
1569 . $table
1570 . " "
1571 . $limit
1572 . " ORDER BY "
1573 . $field_display;
1574
1575 // Debugging
1576 //printf("sql was: %s<BR>", $sql);
1577
1578 $rs = mysql_query($sql, $con);
1579 while ($row = mysql_fetch_array ($rs)) {
1580 $field_display_item = $row[$field_display];
1581 $field_value_item = $row[$field_value];
1582 if (strlen($field_display_item) > 50) $field_display_item = substr($field_display_item, 0, 49) . "...";
1583
1584 printf("<option value = \"" . $field_value_item . "\" ");
1585 if ($field_value_item == $preselected) printf (" selected");
1586 printf(">" . $field_display_item . "</option>\n");
1587 };
1588 }
1589
1590
1591 /**********************************************************
1592 Function: dropDownInfotype($con, $infotype_id, $subject_id)
1593 Author: Paul Bramscher
1594 Last Modified: 12.04.2002
1595 ***********************************************************
1596 Incoming:
1597 $con Database connection string
1598 $subject_id Subject on which to limit the list
1599 ***********************************************************
1600 Outgoing:
1601 None
1602 ***********************************************************
1603 Purpose:
1604 Populates a drop-down box on an HTML form with select
1605 options of information types including general and
1606 master subject-specific.
1607 **********************************************************/
1608 function dropDownInfotype($con, $infotype_id, $subject_id){
1609
1610 // Initialize
1611 $selected_infotype_id = $infotype_id;
1612
1613 /*
1614 Collect a string of mastersubjects for this subject.
1615 This will be used in a later SQL query to limit list of infotypes based
1616 on subject.
1617 */
1618
1619 $sql = "SELECT sm.mastersubject_id FROM sub_mastersubject sm WHERE sm.subject_id = "
1620 . $subject_id;
1621
1622 // Debugging
1623 //printf("sql was: %s<BR>", $sql);
1624
1625 // Build the string. Start with the "(N/A)" and the "(ALL)" master subjects.
1626 $masterstring = "(1, 2 ";
1627
1628 $rs = mysql_query($sql, $con);
1629 while ($row = mysql_fetch_array ($rs)) {
1630 $mastersubject_id = $row["mastersubject_id"];
1631 $masterstring .= ", " . $mastersubject_id;
1632 }
1633 $masterstring .= ")";
1634
1635 // Build the list of infotypes appropriate to all of the mastersubjects found
1636 $sql = "SELECT i.infotype, i.infotype_id, mi.masterinfotype
1637 FROM infotype i, masterinfotype mi
1638 WHERE i.masterinfotype_id = mi.masterinfotype_id
1639 AND i.infotype_id > 0 and mi.masterinfotype_id > 0
1640 AND i.mastersubject_id IN " . $masterstring .
1641 " ORDER BY mi.masterinfotype";
1642 printf("sql was: %s<br>", $sql);
1643
1644 $rs = mysql_query($sql, $con);
1645 while ($row = mysql_fetch_array ($rs)) {
1646 $masterinfotype = $row["masterinfotype"];
1647 $infotype_id = $row["infotype_id"];
1648 $infotype = $row["infotype"];
1649
1650
1651 // Make it look more friendly
1652 $useroutput = $masterinfotype . " -> " . $infotype;
1653
1654 printf("<option value = \"%d\"", $infotype_id);
1655 if ($infotype_id == $selected_infotype_id) printf (" selected ");
1656 printf(">" . $useroutput . "</option>\n");
1657 };
1658 }
1659
1660
1661 /**********************************************************
1662 Function: dropDownPageStaff($con, $page_id)
1663 Author: Paul Bramscher
1664 Last Modified: 10.23.2002
1665 ***********************************************************
1666 Incoming:
1667 $con Database connection string
1668 $page_id PageScribe page involved
1669 ***********************************************************
1670 Outgoing:
1671 None
1672 ***********************************************************
1673 Purpose:
1674 Populates a drop-down box on an HTML form with select
1675 options of staff members. They are displayed and ordered
1676 in the following format: "last name, first name (x500id)".
1677 The staff displayed are those associated as PageScribe
1678 maintainers.
1679 **********************************************************/
1680 function dropDownPageStaff($con, $page_id){
1681
1682 // Build the SQL.
1683 $sql = "SELECT s.first_name, s.last_name, s.staff_id, s.staff_account
1684 FROM staff s, page_staff ps
1685 WHERE ps.page_id = "
1686 . $page_id
1687 . " AND s.staff_id > 1 AND ps.staff_id = s.staff_id ORDER BY last_name, first_name, staff_account";
1688
1689 $rs = mysql_query($sql, $con);
1690 while ($row = mysql_fetch_array ($rs)) {
1691 $staff_id = $row["staff_id"];
1692 $last_name = $row["last_name"];
1693 $first_name = $row["first_name"];
1694 $staff_account = $row["staff_account"];
1695
1696 // Make it look more friendly
1697 $staff = $last_name . ", " . $first_name . " (" . $staff_account . ")";
1698 printf("<option value = \"" . $staff_id
1699 . "\" >" . $staff . "</option>\n");
1700 };
1701 }
1702
1703
1704 /**********************************************************
1705 Function: dropDownPageSubject($con, $page_id)
1706 Author: Paul Bramscher
1707 Last Modified: 06.17.2003
1708 ***********************************************************
1709 Incoming:
1710 $con Database connection string
1711 $page_id PageScribe page involved
1712 ***********************************************************
1713 Outgoing:
1714 None
1715 ***********************************************************
1716 Purpose:
1717 Populates a drop-down box on an HTML form with select
1718 options of associated RQS subjects for the supplied page id.
1719 **********************************************************/
1720 function dropDownPageSubject($con, $page_id){
1721
1722 // Build the SQL.
1723 $sql = "SELECT s.subject_id, s.subject
1724 FROM subject s, page_subject ps
1725 WHERE ps.page_id = "
1726 . $page_id
1727 . " AND s.subject_id > 1 AND ps.subject_id = s.subject_id ORDER BY s.subject";
1728
1729 $rs = mysql_query($sql, $con);
1730 while ($row = mysql_fetch_array ($rs)) {
1731 $subject_id = $row["subject_id"];
1732 $subject = $row["subject"];
1733
1734 printf("<option value = \"" . $subject_id
1735 . "\" >" . $subject . "</option>\n");
1736 };
1737 }
1738
1739
1740 /**********************************************************
1741 Function: dropDownResource($con, $key_id, $letter, $masterinfotype_id, $feature_id)
1742 Author: Paul Bramscher
1743 Last Modified: 05.28.2003
1744 ***********************************************************
1745 Incoming:
1746 $con Database connection string
1747 $key_id "0" for a new resource, otherwise
1748 indicates a pre-selected resource
1749 $limit Starting with letter A-Z or freetext.
1750 $masterinfotype_id To limit by the masterinfotype.
1751 $feature_id To limit by feature id
1752 ***********************************************************
1753 Outgoing:
1754 None
1755 ***********************************************************
1756 Purpose:
1757 Populates an HTML drop-down box of resources in the
1758 following format: ID#:TT:AA. Where ID is the resource
1759 ID#, TT is the first 35 char. of the title, and AA is the
1760 first 35 characters of the author.
1761
1762 NOTE: Currently only titles are displayed, the author
1763 portion has been commented out.
1764
1765 If incoming $limit is a single character (a letter), then
1766 the query checks against all titles starting with it.
1767 Otherwise it checks against all titles or annotations
1768 containing that string.
1769 **********************************************************/
1770 function dropDownResource($con, $key_id, $limit, $masterinfotype_id, $feature_id){
1771
1772 // Clean up the limit string
1773 if (strlen($limit) > 0) $limit = textSearchmySQL($limit);
1774
1775 // Build the query
1776 $sql = "SELECT DISTINCT r.resource_id, r.title, r.author
1777 FROM resource r
1778 LEFT JOIN infotype i using (infotype_id)
1779 LEFT JOIN masterinfotype m on i.masterinfotype_id = m.masterinfotype_id
1780 LEFT JOIN res_feature rf on r.resource_id = rf.resource_id
1781 WHERE r.infotype_id = i.infotype_id AND i.masterinfotype_id = m.masterinfotype_id";
1782
1783 // If a single letter, limit to title match
1784 if (strlen($limit) == 1) $sql .= " AND title LIKE '" . $limit . "%'";
1785
1786 // If incoming freetext, query title or annotation
1787 if (strlen($limit) > 1) $sql .= " AND ((title LIKE '%" . $limit . "%') OR (annotation LIKE '%" . $limit . "%'))";
1788
1789
1790 // Limit by masterinfotype_id
1791 if ($masterinfotype_id > 0) $sql .= " AND m.masterinfotype_id = " . $masterinfotype_id;
1792
1793 // Limit by feature
1794 if ($feature_id > 0) $sql .= " AND rf.feature_id = " . $feature_id;
1795
1796 $sql .=" ORDER BY title, author, resource_id";
1797
1798 // Debugging
1799 // printf("sql was: %s<BR>", $sql);
1800
1801 $rs = mysql_query($sql, $con);
1802 while ($row = mysql_fetch_array ($rs)) {
1803 $resource_id = $row["resource_id"];
1804 $title = $row["title"];
1805 //$author = $row["author"];
1806
1807 // Trim and add ellipsis
1808 if (strlen($title) > 65) $title = substr($title, 0, 65) . "...";
1809 //if (strlen($author) > 35) $author = substr($author, 0, 35) . "...";
1810
1811 $display = $title;
1812 //if (strlen($author) > 0) $display .= " | " .$author;
1813 $display .= " | ID: " . $resource_id;
1814
1815 if ($resource_id == $key_id) $selected = "selected";
1816 else $selected = "";
1817
1818 printf("<option %s value = \""
1819 . $resource_id
1820 . "\" >"
1821 . $display . "</option>\n", $selected);
1822 };
1823 }
1824
1825
1826 /**********************************************************
1827 Function: dropDownServiceLimit($con, $servicetype_id)
1828 Author: Paul Bramscher
1829 Last Modified: 06.16.2003
1830 ***********************************************************
1831 Incoming:
1832 $con Database connection string
1833 $servicetype_id Service type to limit the picklist
1834 ***********************************************************
1835 Outgoing:
1836 None
1837 ***********************************************************
1838 Purpose:
1839 Populates a drop-down box on an HTML form with select
1840 options for library services, limited to a supplied service
1841 type id.
1842 **********************************************************/
1843 function dropDownServiceLimit($con, $servicetype_id){
1844
1845 $sql = "SELECT DISTINCT s.service, s.service_id FROM service s
1846 LEFT JOIN serv_servtype ss using (service_id)
1847 LEFT JOIN servicetype v on ss.servicetype_id = v.servicetype_id";
1848
1849 if ($servicetype_id > 0) {
1850 $sql .= " WHERE ss.servicetype_id = "
1851 . $servicetype_id;
1852 }
1853
1854 $sql .= " ORDER BY s.service";
1855
1856 // Debugging
1857 // printf("sql was: %s<BR>", $sql);
1858
1859 $rs = mysql_query($sql, $con);
1860 while ($row = mysql_fetch_array ($rs)) {
1861 $service = $row["service"];
1862 $service_id = $row["service_id"];
1863 if (strlen($service) > 50) $service = substr($service, 0, 49) . "...";
1864
1865 printf("<option value=\"%d\">%s</option>\n", $service_id, $service);
1866 };
1867 }
1868
1869
1870 /**********************************************************
1871 Function: dropDownStaff($con)
1872 Author: Paul Bramscher
1873 Last Modified: 6.7.2001
1874 ***********************************************************
1875 Incoming:
1876 $con Database connection string
1877 ***********************************************************
1878 Outgoing:
1879 None
1880 ***********************************************************
1881 Purpose:
1882 Populates a drop-down box on an HTML form with select
1883 options of staff members. They are displayed and ordered
1884 in the following format: "last name, first name (x500id)".
1885 **********************************************************/
1886 function dropDownStaff($con){
1887
1888 // Build the SQL
1889 $sql = "SELECT *
1890 FROM staff
1891 ORDER BY last_name, first_name, staff_account";
1892
1893 $rs = mysql_query($sql, $con);
1894 while ($row = mysql_fetch_array ($rs)) {
1895 $staff_id = $row["staff_id"];
1896 $last_name = $row["last_name"];
1897 $first_name = $row["first_name"];
1898 $staff_account = $row["staff_account"];
1899
1900 // Make it look more friendly
1901 if ($staff_id == 1) $staff = "(N/A)";
1902 else $staff = $last_name . ", " . $first_name . " (" . $staff_account . ")";
1903 printf("<option value = \"" . $staff_id
1904 . "\" >" . $staff . "</option>\n");
1905 };
1906 }
1907
1908
1909 /**********************************************************
1910 Function: dropDownStaffOmit($con, $omit)
1911 Author: Paul Bramscher
1912 Last Modified: 10.08.2002
1913 ***********************************************************
1914 Incoming:
1915 $con Database connection string
1916 $omit String of staff's to omit
1917 ***********************************************************
1918 Outgoing:
1919 None
1920 ***********************************************************
1921 Purpose:
1922 Populates a drop-down box on an HTML form with select
1923 statements. $omit limits output.
1924 **********************************************************/
1925 function dropDownStaffOmit($con, $omit){
1926
1927 // Build the SQL
1928 $sql = "SELECT * from staff "
1929 . $omit
1930 . " ORDER BY last_name, first_name";
1931
1932 $rs = mysql_query($sql, $con);
1933 while ($row = mysql_fetch_array ($rs)) {
1934 $last_name = $row["last_name"];
1935 $first_name = $row["first_name"];
1936 $staff_id = $row["staff_id"];
1937 $staff_account = $row["staff_account"];
1938
1939 // Make it look more friendly
1940 if ($staff_id == 1) $staff = "(N/A)";
1941 else $staff = $last_name . ", " . $first_name . " (" . $staff_account . ")";
1942 printf("<option value = \"" . $staff_id
1943 . "\" >" . $staff . "</option>\n");
1944 };
1945 }
1946
1947
1948 /**********************************************************
1949 Function: dropDownStaffSelected($con, $selected_id)
1950 Author: Paul Bramscher
1951 Last Modified: 6.12.2001
1952 ***********************************************************
1953 Incoming:
1954 $con Database connection string
1955 $selected_id Selected staff id
1956 ***********************************************************
1957 Outgoing:
1958 None
1959 ***********************************************************
1960 Purpose:
1961 Populates a drop-down box on an HTML form with select
1962 statements. Selected id represents the preselected staff.
1963 **********************************************************/
1964 function dropDownStaffSelected($con, $selected_id){
1965
1966 // Build the SQL
1967 $sql = "SELECT * from staff WHERE staff_id > 1 ORDER BY last_name, first_name";
1968 $rs = mysql_query($sql, $con);
1969 while ($row = mysql_fetch_array ($rs)) {
1970 $last_name = $row["last_name"];
1971 $first_name = $row["first_name"];
1972 $staff_id = $row["staff_id"];
1973 printf("<option value = \"" . $staff_id . "\" ");
1974 if ($staff_id == $selected_id) printf (" selected");
1975 printf(">" . $last_name . ", " . $first_name . "</option>\n");
1976 };
1977 }
1978
1979
1980 /**********************************************************
1981 Function: existsFaculty
1982 Author: Paul Bramscher
1983 Last Modified: 06.24.2003
1984 ***********************************************************
1985 Incoming:
1986 $con Database connection string
1987 $faculty_firstname Faculty first name
1988 $faculty_lastname Faculty last name
1989 ***********************************************************
1990 Outgoing:
1991 "1" if a match exists, "0" if not
1992 ***********************************************************
1993 Purpose:
1994 This function checks to see whether a match against the
1995 supplied faculty first and last name already exists.
1996 **********************************************************/
1997 function existsFaculty($con, $faculty_firstname, $faculty_lastname){
1998
1999 // Build the sql
2000 $faculty_firstname = textInmySQL($faculty_firstname);
2001 $faculty_lastname = textInmySQL($faculty_lastname);
2002
2003 $sql = "SELECT * FROM faculty WHERE faculty_firstname = '"
2004 . $faculty_firstname
2005 . "' AND faculty_lastname = '"
2006 . $faculty_lastname
2007 . "'";
2008
2009 $faculty_id = 0;
2010 $rs = mysql_query($sql, $con);
2011
2012 // Concatenate the NOT IN clause
2013 while ($row = mysql_fetch_array ($rs)) {
2014 if ($faculty_id == 0) $faculty_id = $row["faculty_id"];
2015 }
2016
2017 return $faculty_id;
2018 }
2019
2020
2021 /**********************************************************
2022 Function: existsResSub
2023 Author: Paul Bramscher
2024 Last Modified: 12.09.2002
2025 ***********************************************************
2026 Incoming:
2027 $con Database connection string
2028 $resource_id Record ID
2029 $subject_id Subject ID
2030 ***********************************************************
2031 Outgoing:
2032 "1" if an association already exists, "0" if not
2033 ***********************************************************
2034 Purpose:
2035 This function checks to see whether a resource has already
2036 been attached to a given subject id.
2037 **********************************************************/
2038 function existsResSub($con, $resource_id, $subject_id){
2039
2040 // Build the sql
2041 $sql = "SELECT * FROM res_sub_infotype WHERE resource_id = "
2042 . $resource_id
2043 . " AND subject_id = "
2044 . $subject_id;
2045
2046 $rs = mysql_query($sql, $con);
2047 $rowcount = mysql_num_rows($rs);
2048 if ($rowcount >= 1){
2049 $rowcount = 1;
2050 }
2051 else $rowcount = 0;
2052
2053 return $rowcount;
2054 }
2055
2056
2057 /**********************************************************
2058 Function: existsResSubNA
2059 Author: Paul Bramscher
2060 Last Modified: 05.27.2003
2061 ***********************************************************
2062 Incoming:
2063 $con Database connection string
2064 $resource_id Record ID
2065 $subject_id Subject ID
2066 ***********************************************************
2067 Outgoing:
2068 "1" if an association already exists, "0" if not
2069 ***********************************************************
2070 Purpose:
2071 This function is similar to existsResSub, but with checks
2072 specifically to see whether the (N/A) type information type
2073 is currently assigned to the resource-subject combination.
2074 **********************************************************/
2075 function existsResSubNA($con, $resource_id, $subject_id){
2076
2077 // Build the sql
2078 $sql = "SELECT * FROM res_sub_infotype WHERE resource_id = "
2079 . $resource_id
2080 . " AND subject_id = "
2081 . $subject_id
2082 . " AND infotype_id = 1";
2083
2084 $rs = mysql_query($sql, $con);
2085 $rowcount = mysql_num_rows($rs);
2086 if ($rowcount == 1){
2087 $rowcount = 1;
2088 }
2089 else $rowcount = 0;
2090
2091 return $rowcount;
2092 }
2093
2094
2095 /**********************************************************
2096 Function: existsRow
2097 Author: Paul Bramscher
2098 Last Modified: 12.09.2002
2099 ***********************************************************
2100 Incoming:
2101 $con Database connection string
2102 $table Table to search
2103 $key_field Field against which to search
2104 $key_id Limit for the where clause
2105 ***********************************************************
2106 Outgoing:
2107 "1" if the row exists, "0" if not
2108 ***********************************************************
2109 Purpose:
2110 Useful in performing delete and other operations to make
2111 sure that a row entity exists matching the supplied key
2112 before going any further.
2113 **********************************************************/
2114 function existsRow($con, $table, $key_field, $limit_id){
2115 // Cast as integer to avoid future problems
2116 $limit_id = (int) $limit_id;
2117
2118 // Build the sql
2119 $sql = "SELECT "
2120 . $key_field
2121 . " FROM "
2122 . $table
2123 . " WHERE "
2124 . $key_field
2125 . "="
2126 . $limit_id;
2127
2128 $rs = mysql_query($sql, $con);
2129 $rowcount = mysql_num_rows($rs);
2130 if ($rowcount >= 1){
2131 $rowcount = 1;
2132 }
2133 else $rowcount = 0;
2134
2135 return $rowcount;
2136 }
2137
2138
2139 /**********************************************************
2140 Function: getNotIn
2141 Author: Paul Bramscher
2142 Last Modified: 12.09.2002
2143 ***********************************************************
2144 Incoming:
2145 $con Database connection string
2146 $in_field Name of ID field to return
2147 $static_field Name of the "other" ID field in the
2148 bridging table
2149 $static_value Value for the other ID field to
2150 limit by
2151 $table Bridging table involved
2152 ***********************************************************
2153 Outgoing:
2154 $in_string Concatenated string of $in_field
2155 ID's to be excluded from a drop-down
2156 box.
2157 ***********************************************************
2158 Purpose:
2159 This applies to a bridging table with a dual primary key.
2160 For example, in the res_loc table, produce a list of all
2161 location_id's associated with a particular resource_id.
2162 This list is comma-separated and returned to an
2163 assignment-type form in which drop-down boxes of the
2164 current selections are displayed alongside remaining
2165 selections. The remaining (available) selections need to
2166 exclude the current ones. Thus, this function builds the
2167 NOT IN portion of the SQL WHERE clause.
2168 **********************************************************/
2169 function getNotIn($con, $in_field, $static_field,
2170 $static_value, $table) {
2171
2172 // Initialze
2173 $in_string = "(";
2174 $first_element = 0;
2175
2176 // Build the sql
2177 $sql = "SELECT "
2178 . $in_field
2179 . " FROM "
2180 . $table
2181 . " WHERE "
2182 . $static_field
2183 . " = "
2184 . $static_value;
2185
2186 $rs = mysql_query($sql, $con);
2187
2188 // Concatenate the NOT IN clause
2189 while ($row = mysql_fetch_array ($rs)) {
2190 $in_element = $row[$in_field];
2191
2192 if ($first_element == 0) {
2193 $first_element = 1;
2194 $in_string .= "'" . $in_element . "'";
2195 }
2196 else $in_string .= ", '" . $in_element . "'";
2197 }
2198
2199 // Cleanup
2200 $in_string .= ")";
2201
2202 // If nothing found, then return a blank string
2203 if ($first_element == 0) $in_string = "";
2204
2205 // Done, return to calling function
2206 return $in_string;
2207 }
2208
2209
2210 /**********************************************************
2211 Function: locationList($con, $resource_id)
2212 Author: Paul Bramscher
2213 Last Modified: 07.02.2002
2214 ***********************************************************
2215 Incoming:
2216 $con Database connection string
2217 $resource_id Resource ID to check locations on
2218 ***********************************************************
2219 Outgoing:
2220 None
2221 ***********************************************************
2222 Purpose:
2223 Looks up location(s) for this resource
2224 **********************************************************/
2225 function locationList($con, $resource_id){
2226
2227 // Initialize
2228 $locations = "";
2229 $first = 1;
2230
2231 $sql = "SELECT location FROM location l, res_loc rl WHERE rl.resource_id = "
2232 . $resource_id
2233 . " AND rl.location_id = l.location_id ORDER BY location";
2234
2235 $rs = mysql_query($sql, $con);
2236 while ($row = mysql_fetch_array ($rs)) {
2237 $location = $row["location"];
2238 if ($first == 1) {
2239 $first = 0;
2240 $locations = $location;
2241 }
2242 else $locations .= "; " . $location;
2243 }
2244
2245 return $locations;
2246 }
2247
2248
2249 /**********************************************************
2250 Function: lookupFaculty($con, $faculty_id)
2251 Author: Paul Bramscher
2252 Last Modified: 12.03.2002
2253 ***********************************************************
2254 Incoming:
2255 $con Database connection string
2256 $faculty_id Faculty ID number to retrieve
2257 ***********************************************************
2258 Outgoing:
2259 Faculty name in {First Name} {Last Name} format.
2260 ***********************************************************
2261 Purpose:
2262 Performs a lookup on faculty ID to retrieve name.
2263 **********************************************************/
2264 function lookupFaculty($con, $faculty_id){
2265 $sql = "SELECT faculty_firstname, faculty_lastname FROM faculty WHERE faculty_id = "
2266 . $faculty_id;
2267 $rs = mysql_query($sql, $con);
2268 $row = mysql_fetch_array ($rs);
2269 $faculty_lastname = $row["faculty_lastname"];
2270 $faculty_firstname = $row["faculty_firstname"];
2271
2272 $faculty_name .= $faculty_firstname . " " . $faculty_lastname;
2273
2274 // Debugging
2275 //printf("sql was: %s<BR>", $sql);
2276
2277 return $faculty_name;
2278 }
2279
2280
2281 /**********************************************************
2282 Function: lookupField($con, $table, $key_field, $key_value,
2283 $desc_field)
2284 Author: Paul Bramscher
2285 Last Modified: 9.14.2000
2286 ***********************************************************
2287 Incoming:
2288 $con Database connection string
2289 $table Table in database to search
2290 $key_field Name of field on which to lookup
2291 $key_value Value to attempt lookup with
2292 $desc_field Descriptive value to return
2293 ***********************************************************
2294 Outgoing:
2295 A descriptive name for an ID number.
2296 ***********************************************************
2297 Purpose:
2298 Performs a lookup, typically on an ID field, to retrieve a
2299 user-friendly descriptive name to the page.
2300 **********************************************************/
2301 function lookupField($con, $table, $key_field, $key_value,
2302 $desc_field){
2303
2304 $sql = "SELECT "
2305 . $desc_field
2306 . " FROM "
2307 . $table
2308 . " WHERE "
2309 . $key_field
2310 . "='"
2311 . $key_value
2312 . "'";
2313 $rs = mysql_query($sql, $con);
2314 $row = mysql_fetch_array ($rs);
2315 if (mysql_num_rows($rs) == 1) $result = $row[$desc_field];
2316 else $result = "";
2317
2318 return $result;
2319 }
2320
2321
2322 /**********************************************************
2323 Function: lookupStaff($con, $staff_id)
2324 Author: Paul Bramscher
2325 Last Modified: 7.22.2002
2326 ***********************************************************
2327 Incoming:
2328 $con Database connection string
2329 $staff_id Staff ID number to retrieve
2330 ***********************************************************
2331 Outgoing:
2332 Staff name in {First Name} {Last Name} format.
2333 ***********************************************************
2334 Purpose:
2335 Performs a lookup on staff ID to retrieve name.
2336 **********************************************************/
2337 function lookupStaff($con, $staff_id){
2338
2339 $sql = "SELECT first_name, last_name FROM staff WHERE staff_id = "
2340 . $staff_id;
2341 $rs = mysql_query($sql, $con);
2342 $row = mysql_fetch_array ($rs);
2343 $last_name = $row["last_name"];
2344 $first_name = $row["first_name"];
2345
2346 $staff_name .= $first_name . " " . $last_name;
2347
2348 // Debugging
2349 //printf("sql was: %s<BR>", $sql);
2350
2351 return $staff_name;
2352 }
2353
2354
2355 /**********************************************************
2356 Function: radioField($con, $table, $field_display,
2357 $field_value, $position)
2358 Author: Paul Bramscher
2359 Last Modified: 12.04.2002
2360 ***********************************************************
2361 Incoming:
2362 $con Database connection string
2363 $table Table in database to search
2364 $field_display Select displayed to user
2365 $field_value Actual value of the HTML tag
2366 $position Either "V" vertical or "H"
2367 horizontal to indicate display of
2368 radio buttons
2369 ***********************************************************
2370 Outgoing:
2371 None
2372 ***********************************************************
2373 Purpose:
2374 Populates radio boxes on an HTML form with values from a
2375 table. They are ordered by $field_display in either
2376 horizontal or vertical fashion. Each checkbox in the set
2377 is named $field_display.
2378 **********************************************************/
2379 function radioField($con, $table, $field_display,
2380 $field_value, $positon){
2381
2382 $rd_sql = "SELECT "
2383 . $field_display
2384 . ", "
2385 . $field_value
2386 . " FROM "
2387 . $table
2388 . " ORDER BY "
2389 . $field_display;
2390 $rd_rs = mysql_query($rd_sql, $con);
2391 while ($rd_row = mysql_fetch_array ($rd_rs)) {
2392 $field_display_item = $rd_row[$field_display];
2393 $field_value_item = $rd_row[$field_value];
2394 printf("<input type='radio' name='"
2395 . $field_display
2396 . " value =\""
2397 . $field_value_item
2398 . "\" > "
2399 . $field_display_item
2400 . " ");
2401 if ($position =="V"){
2402 printf("<BR>\n");
2403 }
2404 };
2405 }
2406
2407
2408 /**********************************************************
2409 Function: recordCount($con, $table, $limit_field,
2410 $limit_where, $type)
2411 Author: Paul Bramscher
2412 Last Modified: 01.15.2004
2413 ***********************************************************
2414 Incoming:
2415 $con Database connection string
2416 $table Table in database to query
2417 $limit_field Field to limit
2418 $limit_where Criteria upon which to limit
2419 $type "N" for numeric or "C" for character
2420 ***********************************************************
2421 Outgoing:
2422 None
2423 ***********************************************************
2424 Purpose:
2425 This function is typically used in conjunction with a
2426 printf statement elsewhere "This operation affects
2427 N record(s). Do you wish to proceed?" This function
2428 returns a value for N.
2429 **********************************************************/
2430 function recordCount($con, $table, $limit_field,
2431 $limit_where, $type) {
2432
2433 // Guard against single quotes
2434 $limit_where = addslashes($limit_where);
2435
2436 $sql = "SELECT "
2437 . $limit_field
2438 . " FROM "
2439 . $table
2440 . " WHERE "
2441 . $limit_field
2442 . "=";
2443
2444 // If the field is numeric
2445 if ($type == "N") {
2446 $sql .= $limit_where;
2447 }
2448
2449 // If character, use single-quotes
2450 else {
2451 $sql .= "'"
2452 .$limit_where
2453 . "'";
2454 }
2455
2456 // Open the query and take a row count
2457 $rs = mysql_query($sql, $con);
2458 $count = mysql_num_rows($rs);
2459
2460 return $count;
2461 }
2462
2463
2464 /**********************************************************
2465 Function: selectCoursesub($con, $limit)
2466 Author: Paul Bramscher
2467 Last Modified: 07.02.2003
2468 ***********************************************************
2469 Incoming:
2470 $con Database connection string
2471 $limit String to limit the results by
2472 ***********************************************************
2473 Outgoing:
2474 None
2475 ***********************************************************
2476 Purpose:
2477 Draws a table with all course subjects for basic
2478 command add/edit/ delete selection.
2479 **********************************************************/
2480 function selectCoursesub($con, $limit){
2481
2482 printf("<table width=\"90%%\" border = \"1\" cellpadding = \"2\" >");
2483 printf("<tr>");
2484 printf("<td class = \"cellPlain\">ID</td>");
2485 printf("<td class = \"cellPlain\">Subject</td>");
2486 printf("<td class = \"cellPlain\">Subject Description</td>");
2487 printf("<td class = \"cellPlain\">Campus</td>");
2488 printf("<td class = \"cellPlain\">CIP Code</td>");
2489 printf("<td class = \"cellPlain\">Select</td>");
2490 printf("</tr>");
2491
2492 // Build the sql
2493 $sql = "SELECT
2494 c.coursesub_id,
2495 c.coursesub,
2496 c.coursesub_descr,
2497 c.cip_code,
2498 p.campus
2499 FROM
2500 coursesub c
2501 LEFT JOIN campus p on c.campus_id = p.campus_id
2502 WHERE coursesub_id > 1";
2503
2504 if (strlen($limit) > 0 && $limit != "*") {
2505
2506 $limit = textSearchmySQL($limit);
2507
2508 $sql .= " AND (coursesub LIKE '%"
2509 . $limit
2510 . "%' OR coursesub_descr LIKE '%"
2511 . $limit
2512 . "%')";
2513
2514 }
2515
2516 // Order the display
2517 $sql .= " ORDER BY coursesub_descr";
2518
2519 $rs = mysql_query($sql, $con);
2520
2521 // Initialize row counter
2522 $rowcount = 0;
2523
2524 while ($row = mysql_fetch_array ($rs)) {
2525 $coursesub_id = $row["coursesub_id"];
2526 $coursesub = Trim($row["coursesub"]);
2527 $coursesub_descr = Trim($row["coursesub_descr"]);
2528 $campus = Trim($row["campus"]);
2529 $cip_code = Trim($row["cip_code"]);
2530
2531 // Make every other row colored
2532 if ($rowcount % 2 == 0) $color = " class=\"backLight\" ";
2533 else $color = "";
2534
2535 printf("<tr>");
2536
2537 printf("<td %s>%d</td>", $color, $coursesub_id);
2538 printf("<td %s>%s</td>", $color, $coursesub);
2539 printf("<td %s>%s</td>", $color, $coursesub_descr);
2540 printf("<td %s>%s</td>", $color, $campus);
2541 printf("<td %s>%s</td>", $color, $cip_code);
2542
2543 printf("<td %s>&nbsp;<input type = \"Radio\" name = \"coursesub_id\" value =\"%d\" >&nbsp;", $color, $coursesub_id);
2544
2545 printf("</tr>");
2546
2547 $rowcount++;
2548 };
2549 printf("</table>");
2550 }
2551
2552
2553 /**********************************************************
2554 Function: selectFaculty($con)
2555 Author: Paul Bramscher
2556 Last Modified: 07.02.2002
2557 ***********************************************************
2558 Incoming:
2559 $con Database connection string
2560 ***********************************************************
2561 Outgoing:
2562 None
2563 ***********************************************************
2564 Purpose:
2565 Draws a table with all faculty for basic command add/edit/
2566 delete selection.
2567 **********************************************************/
2568 function selectFaculty($con){
2569
2570 printf("<table width=\"90%%\" border = \"1\" cellpadding = \"2\" >");
2571 printf("<tr>");
2572 printf("<td class = \"cellPlain\">ID</td>");
2573 printf("<td class = \"cellPlain\">Faculty name</td>");
2574 printf("<td class = \"cellPlain\">Account</td>");
2575 printf("<td class = \"cellPlain\">Email</td>");
2576 printf("<td class = \"cellPlain\">Select</td>");
2577 printf("</tr>");
2578
2579 // Build the sql
2580 $sql = "SELECT
2581 f.faculty_id,
2582 f.faculty_firstname,
2583 f.faculty_lastname,
2584 f.faculty_account,
2585 f.faculty_email
2586 FROM
2587 faculty f
2588 WHERE faculty_id > 1";
2589
2590 // Order the display
2591 $sql .= " ORDER BY faculty_lastname, faculty_firstname";
2592
2593 $rs = mysql_query($sql, $con);
2594
2595 // Row counter
2596 $rowcount = 0;
2597
2598 while ($row = mysql_fetch_array ($rs)) {
2599 $faculty_id = $row["faculty_id"];
2600 $faculty_firstname = Trim($row["faculty_firstname"]);
2601 $faculty_lastname = Trim($row["faculty_lastname"]);
2602 $faculty_account = $row["faculty_account"];
2603 $faculty_email = $row["faculty_email"];
2604
2605 // For display purposes
2606 if (strlen($faculty_account) < 1) $faculty_account = "&nbsp;";
2607 if (strlen($faculty_email) < 1) $faculty_email = "&nbsp;";
2608
2609 // Make every other row colored
2610 if ($rowcount % 2 == 0) $color = " class=\"backLight\" ";
2611 else $color = "";
2612
2613 printf("<tr>");
2614
2615 printf("<td %s>%d</td>", $color, $faculty_id);
2616 printf("<td %s>%s, %s</td>", $color, $faculty_lastname, $faculty_firstname);
2617 printf("<td %s>%s</td>", $color, $faculty_account);
2618 printf("<td %s>%s</td>", $color, $faculty_email);
2619
2620 printf("<td %s>&nbsp;<input type = \"Radio\" name = \"faculty_id\" value =\"%d\" >&nbsp;", $color, $faculty_id);
2621
2622 printf("</tr>");
2623
2624 $rowcount++;
2625 };
2626 printf("</table>");
2627 }
2628
2629
2630 /**********************************************************
2631 Function: selectKey($con, $display, $display_field,
2632 $key_field, $limit, $table)
2633 Author: Paul Bramscher
2634 Last Modified: 06.25.2003
2635 ***********************************************************
2636 Incoming:
2637 $con Database connection string
2638 $display A form header to display to user
2639 $display_field Meaningful field in the table to display
2640 $key_field The table primary key (no composites)
2641 $table Table in database to search
2642 $limit Field to limit $field_display by
2643 ***********************************************************
2644 Outgoing:
2645 None
2646 ***********************************************************
2647 Purpose:
2648 Does a simple text dump to show descriptive values and
2649 their associated primary keys in a small table. If
2650 $limit is defined, then limit the result set to display
2651 only $display_field which matches a like cause. Each
2652 result is indicated by a radio button named key_id to be
2653 used in a form to select one of them for further
2654 processing (editing, deleting, etc).
2655
2656 Note that the pick-list filters out the primary key #1,
2657 (N/A) rows which are present in many tables as system
2658 placeholders.
2659 **********************************************************/
2660 function selectKey($con, $display, $display_field,
2661 $key_field, $limit, $table){
2662
2663 $sql = "SELECT "
2664 . $display_field
2665 . ", "
2666 . $key_field
2667 . " FROM "
2668 . $table
2669 . " WHERE ("
2670 . $key_field
2671 . " != 1 AND "
2672 . $display_field
2673 . " != '(N/A)')";
2674
2675 if (strlen($limit) > 0){
2676
2677 // Attach a limit unless "*" is indicated
2678 if ($limit != "*") {
2679 $sql .= " AND "
2680 . $display_field
2681 . " LIKE '%"
2682 . $limit
2683 . "%'";
2684
2685 }
2686
2687
2688 // Order the display
2689 $sql .= " ORDER BY " . $display_field;
2690
2691 $rs = mysql_query($sql, $con);
2692
2693 printf("<table width=\"60%%\" border = \"1\" >\n");
2694 printf("<tr><td class=\"cellPlain\">Description</td>\n");
2695 // printf("<td><b>Key</b></td>");
2696 printf("<td class=\"cellPlain\">Select</td></tr>\n");
2697
2698 $rowcount = 0;
2699
2700 while ($row = mysql_fetch_array ($rs)) {
2701 $field_display_item = $row[$display_field];
2702 $field_value_item = (int) Trim($row[$key_field]);
2703
2704 // Make every other row colored
2705 if ($rowcount % 2 == 0) $color = " class=\"backLight\" ";
2706 else $color = "";
2707
2708 printf("<tr>\n");
2709 printf("<td %s>%s", $color, $field_display_item);
2710 printf("</td>\n");
2711 printf("<td %s><input type =\"Radio\" name =\"key_id\" value =\"%s\"></td>\n",
2712 $color, $field_value_item);
2713 printf("</tr>\n");
2714
2715 /*
2716 printf("<tr><td>"
2717 . $field_display_item
2718 . "</td><td>"
2719 . "<input type = \"Radio\" name = \"key_id\" value =\""
2720 . $field_value_item
2721 . "\"></td></tr>\n");
2722 */
2723
2724 $rowcount++;
2725 };
2726 printf("</table>\n");
2727 }
2728 else {
2729 printf("You must limit by some criteria before proceeding.<BR>\n");
2730 }
2731 }
2732
2733
2734 /**********************************************************
2735 Function: selectStaff($con)
2736 Author: Paul Bramscher
2737 Last Modified: 04.28.2003
2738 ***********************************************************
2739 Incoming:
2740 $con Database connection string
2741 ***********************************************************
2742 Outgoing:
2743 None
2744 ***********************************************************
2745 Purpose:
2746 Draws a table with all staff for basic command add/edit/
2747 delete selection.
2748 **********************************************************/
2749 function selectStaff($con){
2750
2751 printf("<table width=\"90%%\" border = \"1\" cellpadding = \"2\" >");
2752 printf("<tr>");
2753 printf("<td class = \"cellPlain\">ID</td>");
2754 printf("<td class = \"cellPlain\">Staff name</td>");
2755 printf("<td class = \"cellPlain\">Staff Account</td>");
2756 printf("<td class = \"cellPlain\">Local pwd?</td>");
2757 printf("<td class = \"cellPlain\">Access</td>");
2758 printf("<td class = \"cellPlain\">Level</td>");
2759 printf("<td class = \"cellPlain\">Last login</td>");
2760 printf("<td class = \"cellPlain\">Last ip</td>");
2761 printf("<td class = \"cellPlain\">Select</td>");
2762 printf("</tr>");
2763
2764 // Build the sql
2765 $sql = "SELECT
2766 s.staff_id,
2767 s.staff_account,
2768 s.password,
2769 s.first_name,
2770 s.last_name,
2771 s.last_login,
2772 s.last_ip,
2773 a.access,
2774 a.access_level
2775 FROM
2776 staff s, access a
2777 WHERE staff_id > 1 AND s.access_id = a.access_id";
2778
2779 // Order the display
2780 $sql .= " ORDER BY last_name, first_name";
2781
2782 $rs = mysql_query($sql, $con);
2783
2784 $rowcount = 0;
2785
2786 while ($row = mysql_fetch_array ($rs)) {
2787 $first_name = Trim($row["first_name"]);
2788 $last_name = Trim($row["last_name"]);
2789 $staff_account = $row["staff_account"];
2790 $staff_id = $row["staff_id"];
2791 $access = Trim($row["access"]);
2792 $access_level = Trim($row["access_level"]);
2793 $last_login = Trim($row["last_login"]);
2794 $last_ip = Trim($row["last_ip"]);
2795
2796 // See whether the local password has been set
2797 if (strlen($row["password"]) > 0) $local_pwd = "<b>Yes</b>";
2798 else $local_pwd = "No";
2799
2800 // For display purposes
2801 if (strlen($last_login) < 1) $last_login = "&nbsp;";
2802 if (strlen($last_ip) < 1) $last_ip = "&nbsp;";
2803
2804 // Make every other row colored
2805 if ($rowcount % 2 == 0) $color = " class=\"backLight\" ";
2806 else $color = "";
2807
2808 printf("<tr>");
2809
2810 printf("<td %s>%d</td>", $color, $staff_id);
2811 printf("<td %s>%s, %s</td>", $color, $last_name, $first_name);
2812 printf("<td %s>%s</td>", $color, $staff_account);
2813 printf("<td %s>%s</td>", $color, $local_pwd);
2814 printf("<td %s>%s</td>", $color, $access);
2815 printf("<td %s>%d</td>", $color, $access_level);
2816 printf("<td %s>%s</td>", $color, $last_login);
2817 printf("<td %s>%s</td>", $color, $last_ip);
2818
2819 printf("<td %s>&nbsp;<input type = \"Radio\" name = \"staff_id\"
2820 value =\"%d\" >&nbsp;", $color, $staff_id);
2821
2822 printf("</tr>");
2823
2824 $rowcount++;
2825 };
2826 printf("</table>");
2827 }
2828
2829
2830 /**********************************************************
2831 Function: statQuery($con, $sql)
2832 Author: Paul Bramscher
2833 Last Modified: 06.24.2003
2834 ***********************************************************
2835 Incoming:
2836 $con Database connection string
2837 $sql A SQL statement to execute. The
2838 SELECT must have a COUNT(*) in it.
2839 The stat logic should be in WHERE.
2840 ***********************************************************
2841 Outgoing:
2842 $result The numerical result of the query,
2843 or else "UNKNOWN" in case of error.
2844 ***********************************************************
2845 Purpose:
2846 Accepts a simple SQL statement, performs the lookup, and
2847 drawns the result in a two-column table row. The first
2848 column is $label explaining the purpose of the lookup,
2849 the second column is the numerical figure of the $sql.
2850 **********************************************************/
2851
2852 function statQuery($con, $sql){
2853 $rs = mysql_query($sql, $con);
2854 $row = mysql_fetch_array ($rs);
2855
2856 // We should be getting only a single row.
2857 if (mysql_num_rows($rs) == 1) $result = $row["COUNT(*)"];
2858 else $result = "UNKNOWN";
2859
2860 return $result;
2861 }
2862
2863
2864 /**********************************************************
2865 Function: textInmySQL
2866 Author: Paul Bramscher
2867 Last Modified: 09.23.2003
2868 ***********************************************************
2869 Purpose:
2870 This function creates filtering rules for input into
2871 mySQL. Depending on your configuration of the PHP
2872 variable magic_quotes_gpc, security needs, and possible
2873 foreign character set issues you may want to change this.
2874 **********************************************************/
2875 function textInmySQL($incoming) {
2876
2877 // Replace single quotes with two single quotes
2878 $outgoing = ereg_replace("'","''",$incoming);
2879
2880 return $outgoing;
2881
2882 }
2883
2884 /**********************************************************
2885 Function: textOutHTML
2886 Author: Paul Bramscher
2887 Last Modified: 04.21.2003
2888 ***********************************************************
2889 Purpose:
2890 This function will help render some data more presentable
2891 on HTML forms, particularly pre-setting form field values
2892 which might contain a double-quote. This function may also
2893 be extended to cover other cases.
2894 **********************************************************/
2895 function textOutHTML($incoming) {
2896
2897 // Replace double quotes with the HTML &quot;
2898 $outgoing = ereg_replace("\"","&quot;",$incoming);
2899
2900 return $outgoing;
2901
2902 }
2903
2904
2905 /**********************************************************
2906 Function: textSearchmySQL
2907 Author: Paul Bramscher
2908 Last Modified: 05.20.2003
2909 ***********************************************************
2910 Purpose:
2911 This function is to be used on HTML search forms
2912 that might contain a single quote. They are escaped with
2913 a backslash. Any other potential SELECT filtering can
2914 happen here.
2915 **********************************************************/
2916 function textSearchmySQL($incoming) {
2917
2918 // Replace single quotes with two single quotes
2919 // You may need to uncomment this.
2920 $outgoing = ereg_replace("'","\'",$incoming);
2921
2922 // You may need to comment this out, and uncomment the previous.
2923 //$outgoing = $incoming;
2924
2925 return $outgoing;
2926
2927 }
2928 ?>

  ViewVC Help
Powered by ViewVC 1.1.26