admin/include/app_controls.php

<?php
/**********************************************************
Function Library: app_controls.php
Original Author: Paul Bramscher <brams006@tc.umn.edu>
Last Modified: 09.29.2003
***********************************************************
Comments:
Functions here are generally related to drawing of HTML
form input: text boxes, drop-down boxes, and radio buttons.
Other functions include lookup and picklist related duties.

These have been separated from forms.php since they are
typically used in more than one place.  Some are quite
generic and are used throughout the system.
***********************************************************
Table of Contents:
adminReturn
authPage
authResourceDelete
authResourceEdit
authSubject
commaList
displayKeys
displayRelatedPages
dropDownAccountOmit
dropDownAuthCourses
dropDownAuthPages
dropDownAuthSubjects
dropDownCourses
dropDownCoursesubOmit
dropDownCoursesubSelected
dropDownFaculty
dropDownFacultySelected
dropDownField
dropDownFieldOmit
dropDownFieldSelected
dropDownInfotype
dropDownPageStaff
dropDownPageSubject
dropDownResource
dropDownServiceLimit
dropDownStaff
dropDownStaffOmit
dropDownStaffSelected
existsResSub
existsResSubNA
existsRow
getNotIn
locationList
lookupFaculty
lookupField
lookupStaff
radioField
recordCount
selectCoursesub
selectFaculty
selectKey
selectStaff
statQuery
textInmySQL
textOutHTML
textSearchmySQL
**********************************************************/


/**********************************************************
Function: adminReturn($sess_access_level)
Author: Paul Bramscher
Last Modified: 07.02.2003
***********************************************************
Incoming:
$sess_access_level      Access level of the current
                        session.
***********************************************************
Outgoing:
None
***********************************************************
Purpose:
A simple HTML link back to the author console, used
throughout the Lumina(r) system.  This function may be
(optionally) passed the access level of the current user
session.  Higher access will display more menu link
options.  Note that each menu, upon arrival, verifies the
session and access level so this presents no security
risk.
**********************************************************/
function adminReturn($sess_access_level) {

        include ("global_vars.php");

        // Return to admin console
        printf("<center><br>\n");
        printf("<a href=\"%sconsole.phtml\">Authoring Console</a>", $GLOBAL_ADMIN_URL);
        
        // If manager or higher
        if ($sess_access_level >= 100) printf(" | <a href=\"%sconsole_manager.phtml\">Manager Functions</a>", $GLOBAL_ADMIN_URL);
        
        // If DBA
        if ($sess_access_level == 1000) printf(" | <a href=\"%sconsole_dba.phtml\">DBA Tools</a>", $GLOBAL_ADMIN_URL);

        printf("</center>");
}


/**********************************************************
Function: authPage($con, $page_id, $sess_access_level, $sess_staff_id)
Author: Paul Bramscher
Last Modified: 01.15.2003
***********************************************************
Incoming:
$con                    Database connection string
$page_id                Page to test authorization
$sess_access_level      Access level of the current user
                        session.
$sess_staff_id          staff id of the current user
***********************************************************
Outgoing:
1 = authorized to work on this page.
0 = non-authorized.
***********************************************************
Purpose:
A check to ensure whether the current user may access the
supplied page.  The following rules apply:

(1) DBA's can edit anything.
(2) Managers can edit pages created by anyone within their unit.
(3) Page coordinators can edit pages they coordinate.
(4) Page maintainers may also edit pages they are assigned to.

Some scenarios of pages that are NOT editable:

(1) The original page creator is no longer the coordinator,
not a DBA, and not assigned as a maintainer.  S/he can no
longer edit the page.
(2) You are a unit managager and used to have access to a page
coordinated by one of your employees.  S/he switches units,
and is now under a new manager.  The page can now be accessed
by the manager of the new unit and not yourself.

etc...

**********************************************************/
function authPage($con, $page_id, $sess_access_level, $sess_staff_id){

        /* Access Table Definitions
        +-----------+--------------+---------+
        | access_id | access_level | access  |
        +-----------+--------------+---------+
        |         1 |            0 | Denied  |
        |         2 |           10 | Guest   |
        |         3 |           20 | Author  |
        |         4 |          100 | Manager |
        |         5 |         1000 | DBA     |
        +-----------+--------------+---------+
        */


        // Default no authorization
        $auth_page = 0;


        // User is an author.  Must be page coordinator or a co-maintainer.
        if ($sess_access_level == 20) {
        
                $sql = "SELECT count(DISTINCT p.page_id) AS auth_page FROM
                        page p
                        LEFT JOIN page_staff ps using (page_id)
                        WHERE p.page_id = "
                        . $page_id
                        . " AND (p.staff_coordinator = "
                        . $sess_staff_id
                        . " OR ps.staff_id = "
                        . $sess_staff_id
                        . ")";
        
        }

        
        // User is a manager.  Must be page coordinator, co-maintainer, or manager of
        // the coordinator's unit.
        else if ($sess_access_level == 100) {
        
                
                        // Determine libunit
                        $lu_sql = "SELECT libunit_id FROM libunit
                                WHERE head_staff_id = "
                                . $sess_staff_id;
                        $lu_rs = mysql_query($lu_sql, $con);
                        
                        $lu_string = "ls.libunit_id IN (";
                        $first_element = 0;
                        
                        // Concatenate the IN clause
                        while ($lu_row = mysql_fetch_array ($lu_rs)) {
                                $libunit_id = $lu_row["libunit_id"];
                                //printf("libunit id was: %d<BR><BR>", $libunit_id);                            
        
                                if ($first_element == 0) {
                                        $first_element = 1;
                                        $lu_string .= $libunit_id;
                                }
                                else $lu_string .= ", " . $libunit_id;
                        }

                        // Cleanup
                        $lu_string .= ") OR";
        
                        // If nothing found, then return a blank string
                        if ($first_element == 0) $lu_string = "";
                                
                        $sql = "SELECT count(DISTINCT p.page_id) AS auth_page
                                FROM page p
                                LEFT JOIN page_staff ps using (page_id)
                                LEFT JOIN libunit_staff ls on p.staff_coordinator = ls.staff_id
                                WHERE p.page_id = "
                                . $page_id
                                . " AND ("
                                . $lu_string
                                . " p.staff_coordinator = "
                                . $sess_staff_id                                
                                . " OR ps.staff_id = "
                                . $sess_staff_id
                                . ")";
        }
                

        // DBA.  Access everything.
        else if ($sess_access_level == "1000") {
                $auth_page = 1;
        }

        // Every other access level.  No pages at all!
        else {
                $auth_page = 0;
        }

        // Run the authorized page query if not DBA level
        if ($sess_access_level >= 20 && $sess_access_level < 1000) {

                $rs = mysql_query($sql, $con);
                $row = mysql_fetch_array ($rs);

                // Collect the access information
                $auth_page = $row["auth_page"];
        }
        
        if ($auth_page > 0) $auth_page = 1;
        
        return $auth_page;

}


/**********************************************************
Function: authResourceDelete($con, $resource_id, $sess_access_level,
        $sess_staff_account)
Author: Paul Bramscher
Last Modified: 08.26.2003
***********************************************************
Incoming:
$con                    Database connection string
$resource_id            Resource to test authorization
$sess_access_level      Access level of the current user
                        session.
$sess_staff_account             x500 id of the current user
***********************************************************
Outgoing:
1 = authorized to work on this resource
0 = non-authorized.
***********************************************************
Purpose:
A check to ensure whether the current user may delete the
supplied resource.  The following rules apply:

(1) DBA's and managers can delete all resources
(2) Any staffperson with access greater than guest and less than
manager may delete only those resource s/he has created.
**********************************************************/
function authResourceDelete($con, $resource_id, $sess_access_level,
        $sess_staff_account) {

        /* Access Table Definitions
        +-----------+--------------+---------+
        | access_id | access_level | access  |
        +-----------+--------------+---------+
        |         1 |            0 | Denied  |
        |         2 |           10 | Guest   |
        |         3 |           20 | Author  |
        |         4 |          100 | Manager |
        |         5 |         1000 | DBA     |
        +-----------+--------------+---------+
        */


        // Default no authorization
        $auth_resource = 0;

        /* If wishing to enforce resource delete access against author access,
        uncomment this portion -- it's been temporarily commented out to allow
        conversion staff the ability to tweak resources.
        */
        
        // Scenario: guest access < THE USER < manager access
        if ($sess_access_level > 10 && $sess_access_level < 100 ) {
        
                $sql = "SELECT count(r.resource_id) AS auth_resource FROM
                        resource r
                        WHERE r.resource_id = "
                        . $resource_id
                        . " AND r.account_created = '"
                        . $sess_staff_account
                        . "'";
        
                $rs = mysql_query($sql, $con);
                $row = mysql_fetch_array ($rs);

                // Collect the access information
                $auth_resource = $row["auth_resource"];
                
                if ($auth_resource > 0) $auth_resource = 1;
        }

        // Manager or higher access.  Great edit/delete access for everything.
        else if ($sess_access_level >= 100 ) {
                $auth_resource = 1;
        }
        

        /* If wishing to enforce resource edit/delete access against author access,
        uncomment the previous portion and delete this. 
        Note: 08.21.2003 - PFB. Open editing of resources by any author is now verboten.
        
        // Author or higher access.  Great edit/delete access for everything.
        if ($sess_access_level >= "10" ) {
                $auth_resource = 1;
        }
        */


        // Every other access level.  No resources at all!
        else {
                $auth_resource = 0;
        }
        
        return $auth_resource;

}


/**********************************************************
Function: authResourceEdit($con, $resource_id, $sess_access_level,
        $sess_staff_account)
Author: Paul Bramscher
Last Modified: 08.26.2003
***********************************************************
Incoming:
$con                    Database connection string
$resource_id            Resource to test authorization
$sess_access_level      Access level of the current user
                        session.
$sess_staff_account             x500 id of the current user
***********************************************************
Outgoing:
1 = authorized to work on this resource
0 = non-authorized.
***********************************************************
Purpose:
A check to ensure whether the current user may edit the
supplied resource.  The following rules apply:

(1) Authors and above may edit all resources
**********************************************************/
function authResourceEdit($con, $resource_id, $sess_access_level,
        $sess_staff_account) {

        /* Access Table Definitions
        +-----------+--------------+---------+
        | access_id | access_level | access  |
        +-----------+--------------+---------+
        |         1 |            0 | Denied  |
        |         2 |           10 | Guest   |
        |         3 |           20 | Author  |
        |         4 |          100 | Manager |
        |         5 |         1000 | DBA     |
        +-----------+--------------+---------+
        */


        // Default no authorization
        $auth_resource = 0;

        /* If wishing to enforce resource edit/delete access against author access,
        uncomment this portion -- it's been temporarily commented out to allow
        conversion staff the ability to tweak resources.
        Note: 08.21.2003 - PFB. Resource editing access is now being enforced again.
        
        
        // Scenario: guest access < THE USER < manager access
        if ($sess_access_level > 10 && $sess_access_level < 100 ) {
        
                $sql = "SELECT count(r.resource_id) AS auth_resource FROM
                        resource r
                        WHERE r.resource_id = "
                        . $resource_id
                        . " AND r.account_created = '"
                        . $sess_staff_account
                        . "'";
                        
                $rs = mysql_query($sql, $con);
                $row = mysql_fetch_array ($rs);

                // Collect the access information
                $auth_resource = $row["auth_resource"];
                
                if ($auth_resource > 0) $auth_resource = 1;
        }


        // Manager or higher access.  Great edit/delete access for everything.
        else if ($sess_access_level >= "100" ) {
                $auth_resource = 1;
        }
        */

        /* If wishing to enforce resource edit/delete access against author access,
        uncomment the previous portion and delete this. 
        Note: 08.21.2003 - PFB. Open editing of resources by any author is now verboten.
        Note: 08.26.2003 - PFB. Editing of resources is allowed, but not deleting.
        */
        
        // Author or higher access.  Great edit/delete access for everything.
        if ($sess_access_level >= 10 ) {
                $auth_resource = 1;
        }


        // Every other access level.  No resources at all!
        else {
                $auth_resource = 0;
        }

        // Testing
        // printf("sql was: %s<BR>", $sql);
        // printf("sess acl: %s<BR>", $sess_access_level);
        // printf("sess_staff_account: %s<BR>", $sess_staff_account);
        // printf("auth res: %s<br>", $auth_resource);
        
        return $auth_resource;

}


/**********************************************************
Function: authSubject($con, $sess_access_level, $sess_staff_id,
        $subject_id)
Author: Paul Bramscher
Last Modified: 01.22.2003
***********************************************************
Incoming:
$con                    Database connection string
$sess_access_level      Access level of the current user
                        session.
$sess_staff_id          staff id of the current user
$subject_id             Subject to test authorization
***********************************************************
Outgoing:
1 = authorized to work on this subject
0 = non-authorized.
***********************************************************
Purpose:
A check to ensure whether the current user may access the
supplied subject.  The following rules apply:

(1) DBA's can edit anything.
(2) Managers can edit subjects maintained by anyone within their unit,
subjects to which they are personally assigned.
(3) Any staffperson, regardless of access, may edit subjects to
which they are assigned.


**********************************************************/
function authSubject($con, $sess_access_level, $sess_staff_id,
        $subject_id){

        /* Access Table Definitions
        +-----------+--------------+---------+
        | access_id | access_level | access  |
        +-----------+--------------+---------+
        |         1 |            0 | Denied  |
        |         2 |           10 | Guest   |
        |         3 |           20 | Author  |
        |         4 |          100 | Manager |
        |         5 |         1000 | DBA     |
        +-----------+--------------+---------+
        */


        // Default no authorization
        $auth_subject = 0;


        // Scenario: guest access < THE USER < manager access
        if ($sess_access_level > 10 && $sess_access_level < 100 ) {
        
                $sql = "SELECT count(DISTINCT s.subject_id) AS auth_subject FROM
                        subject s
                        LEFT JOIN sub_staff st using (subject_id)
                        WHERE s.subject_id = "
                        . $subject_id
                        . " AND st.staff_id = "
                        . $sess_staff_id;
        
        }

        
        // User is a manager.
        else if ($sess_access_level == 100) {
        
                
                        // Determine libunit
                        $lu_sql = "SELECT libunit_id FROM libunit
                                WHERE head_staff_id = "
                                . $sess_staff_id;
                        $lu_rs = mysql_query($lu_sql, $con);
                        
                        $lu_string = "ls.libunit_id IN (";
                        $first_element = 0;
                        
                        // Concatenate the IN clause
                        while ($lu_row = mysql_fetch_array ($lu_rs)) {
                                $libunit_id = $lu_row["libunit_id"];
                                //printf("libunit id was: %d<BR><BR>", $libunit_id);                            
        
                                if ($first_element == 0) {
                                        $first_element = 1;
                                        $lu_string .= $libunit_id;
                                }
                                else $lu_string .= ", " . $libunit_id;
                        }

                        // Cleanup
                        $lu_string .= ") OR";
        
                        // If nothing found, then return a blank string
                        if ($first_element == 0) $lu_string = "";


                        $sql = "SELECT count(DISTINCT s.subject_id) AS auth_subject FROM
                                subject s
                                LEFT JOIN sub_staff st using (subject_id)
                                LEFT JOIN libunit_staff ls on st.staff_id = ls.staff_id
                                WHERE s.subject_id = "
                                . $subject_id
                                . " AND ("
                                . $lu_string                    
                                . " st.staff_id = "
                                . $sess_staff_id
                                . ")";
        }
                

        // DBA.  Access everything.
        else if ($sess_access_level == "1000") {
                $auth_subject = 1;
        }

        // Every other access level.  No subjects at all!
        else {
                $auth_subject = 0;
        }

        // Testing
        // printf("sql was: %s<BR>", $sql);

        // Run the authorized subject query if not DBA level
        if ($sess_access_level >= 20 && $sess_access_level < 1000) {

                $rs = mysql_query($sql, $con);
                $row = mysql_fetch_array ($rs);

                // Collect the access information
                $auth_subject = $row["auth_subject"];
        }
        
        if ($auth_subject > 0) $auth_subject = 1;
        
        return $auth_subject;

}


/**********************************************************
Function: commaList($con, $displayField, $sql)
Author: Paul Bramscher
Last Modified: 07.02.2002
***********************************************************
Incoming:
$con                    Database connection string
$displayField           Field to display
$sql                    SQL to execute
***********************************************************
Outgoing:
None
***********************************************************
Purpose:
Performs a lookup similar to lookupField, but instead of
returning the descriptive value for an ID field, it returns
a comma-separated list of results.
**********************************************************/
function commaList($con, $displayField, $sql){
        $rs = mysql_query($sql, $con);
        
        $first = 1;
        while ($row = mysql_fetch_array ($rs)) {
                $result = $row[$displayField];
                if ($first == 1) {
                        $first = 0;
                        printf("%s", $result);
                }
                else printf(", %s", $result);
        }
}


/**********************************************************
Function: displayKeys($con, $table, $field_display,
$field_value, $limit)
Author: Paul Bramscher
Last Modified: 7.26.2001
***********************************************************
Incoming:
$con                    Database connection string
$table                  Table in database to search
$field_display          Select displayed to user
$field_value            Actual value of the HTML tag
$limit                  Field to limit $field_display by
***********************************************************
Outgoing:
None
***********************************************************
Purpose:
Does a simple text dump to show descriptive values and
their associated primary keys in a small table.  If
$limit is defined, then limit the result set to display
only $field_display which matches a like cause.
**********************************************************/
function displayKeys($con, $table, $field_display,
        $field_value, $limit){
        
        // Build the sql
        $sql = "SELECT " . $field_display . ", "
                . $field_value . " FROM " . $table;
        if (strlen($limit) > 1){
                $sql .= " WHERE "
                        . $field_display
                        . " LIKE '%"
                        . $limit
                        . "%'";
        }
        
        // Order the display
        $sql .= " ORDER BY " . $field_display;
        
        $rs = mysql_query($sql, $con);

        printf("<td><b>Description</b></td>");
        printf("<td><b>Key</b></td></tr>");

        while ($row = mysql_fetch_array ($rs)) {
                $field_display_item = $row[$field_display];
                $field_value_item = $row[$field_value];
                printf("<tr><td>"
                        . $field_display_item
                        . "</td><td>"
                        . $field_value_item
                        . "</td></tr>");
        };
}


/**********************************************************
Function: displayRelatedPages($con, $subject_id)
Author: Paul Bramscher
Last Modified: 06.17.2003
***********************************************************
Incoming:
$con                    Database connection string
$subject_id             Subject of the RQS page
***********************************************************
Outgoing:
None
***********************************************************
Purpose:
Displays any PageScribe pages associated with the supplied
subject id.
**********************************************************/
function displayRelatedPages($con, $subject_id){
        $sql = "SELECT p.page_id, p.page_title
                FROM page p, sub_page sp
                WHERE sp.subject_id = "
                        . $subject_id
                        . " AND sp.page_id = p.page_id ORDER BY p.page_title";
        
        $rs = mysql_query($sql, $con);
        while ($row = mysql_fetch_array ($rs)) {
                $page_id = $row["page_id"];
                $page_title = $row["page_title"];

                // Pull out any HTML
                $page_title = strip_tags($page_title);
                
                printf("<a href=\"page.phtml?page_id=%s\">%s</a><BR>\n", $page_id, $page_title);
        };
}


/**********************************************************
Function: dropDownAccountOmit($con, $omit)
Author: Paul Bramscher
Last Modified: 04.01.2003
***********************************************************
Incoming:
$con                    Database connection string
$omit                   String of staff's to omit
***********************************************************
Outgoing:
None
***********************************************************
Purpose:
Populates a drop-down box on an HTML form with select
statements.  $omit limits output.  Similar to dropDownStaffOmit,
but instead of returning the staff_id as the HTML value,
it returns the staff_account.  This was done for situations in
which a relational tie with the staff table is not desirable
after the value is inserted.  For example, historical statistics.
**********************************************************/
function dropDownAccountOmit($con, $omit){
        $sql = "SELECT * from staff " . $omit .
        " ORDER BY last_name, first_name";
        
        $rs = mysql_query($sql, $con);
        while ($row = mysql_fetch_array ($rs)) {
                $last_name = $row["last_name"];
                $first_name = $row["first_name"];
                $staff_id = $row["staff_id"];
                $staff_account = $row["staff_account"];
                
                // Make it look more friendly
                if ($staff_id == 1) $staff = "(N/A)";
                else $staff = $last_name . ", " . $first_name . " (" . $staff_account . ")";
                printf("<option value = \"" . $staff_account
                        . "\" >" . $staff . "</option>\n");
        };
}


/**********************************************************
Function: dropDownAuthCourses($con, $sess_access_level, $sess_staff_id)
Author: Paul Bramscher
Last Modified: 01.07.2003
***********************************************************
Incoming:
$con                    Database connection string
$sess_access_level      Access level of the current user
                        session.
$sess_staff_id          staff id of the current user
***********************************************************
Outgoing:
None
***********************************************************
Purpose:
Identical to dropDownAuthPages, except for a pagetype_id = 3
and extra LEFT JOIN to the course table to fetch the
course_concat field as the course title.

Populates a drop-down box on an HTML form with select
options of CourseScribe pages that the current
user has authorization to edit.  The following rules apply:

(1) DBA's can edit anything.
(2) Managers can edit pages created by anyone within their unit.
(3) Page coordinators can edit pages they coordinate.
(4) Page maintainers may also edit pages they are assigned to.

Some scenarios of pages that are NOT editable:

(1) The original page creator is no longer the coordinator,
not a DBA, and not assigned as a maintainer.  S/he can no
longer edit the page.
(2) You are a unit managager and used to have access to a page
coordinated by one of your employees.  S/he switches units,
and is now under a new manager.  The course page can now be
accessed by the manager of the new unit and not yourself.

etc...

**********************************************************/
function dropDownAuthCourses($con, $sess_access_level, $sess_staff_id){

        /* Access Table Definitions
        +-----------+--------------+---------+
        | access_id | access_level | access  |
        +-----------+--------------+---------+
        |         1 |            0 | Denied  |
        |         2 |           10 | Guest   |
        |         3 |           20 | Author  |
        |         4 |          100 | Manager |
        |         5 |         1000 | DBA     |
        +-----------+--------------+---------+
        */


        // Behave differently based on access level
        switch ($sess_access_level) {
        
                // Author.  Start from the page table.
                case "20" :
                        $page_sql = "SELECT DISTINCT
                                p.page_id,
                                c.course_concat
                                FROM page p
                                LEFT JOIN page_staff ps using (page_id)
                                LEFT JOIN course c on p.page_id = c.page_id
                                WHERE p.pagetype_id = 3
                                AND
                                (ps.staff_id = "
                                . $sess_staff_id
                                . " OR p.staff_coordinator = "
                                . $sess_staff_id
                                . ") ORDER BY c.course_concat";
                        break;

                // Manager.  Start from the libunit table, work down to pages
                case "100" :
                
                        // Determine libunit
                        $lu_sql = "SELECT libunit_id FROM libunit
                                WHERE head_staff_id = "
                                . $sess_staff_id;
                        $lu_rs = mysql_query($lu_sql, $con);
                        
                        $lu_string = "ls.libunit_id IN (";
                        $first_element = 0;
                        
                        // Concatenate the IN clause
                        while ($lu_row = mysql_fetch_array ($lu_rs)) {
                                $libunit_id = $lu_row["libunit_id"];                            
        
                                if ($first_element == 0) {
                                        $first_element = 1;
                                        $lu_string .= $libunit_id;
                                }
                                else $lu_string .= ", " . $libunit_id;
                        }

                        // Cleanup
                        $lu_string .= ") OR";
        
                        // If nothing found, then return a blank string
                        if ($first_element == 0) $lu_string = "";
                                
                        $page_sql = "SELECT DISTINCT
                                p.page_id,
                                c.course_concat
                                FROM page p
                                LEFT JOIN page_staff ps using (page_id)
                                LEFT JOIN course c on p.page_id = c.page_id
                                LEFT JOIN libunit_staff ls on p.staff_coordinator = ls.staff_id
                                WHERE p.pagetype_id = 3
                                AND ("
                                . $lu_string
                                . " ps.staff_id = "
                                . $sess_staff_id
                                . " OR p.staff_coordinator = "
                                . $sess_staff_id
                                . ") ORDER BY c.course_concat";
                        break;

                // DBA.  Access everything.
                case "1000" :
                        dropDownCourses($con);
                        break;

                // Every other access level.  No pages at all!
                default :
                        break;
                
        }

        // Run the authorized page query if not DBA level
        if ($sess_access_level >= 20 && $sess_access_level < 1000) {

                $page_rs = mysql_query($page_sql, $con);

                while ($page_row = mysql_fetch_array ($page_rs)) {

                        // Collect the page information
                        $page_id = $page_row["page_id"];
                        $course_concat = $page_row["course_concat"];
                        
                        if (strlen($course_concat) > 45) $course_concat = substr($course_concat, 0, 45) . "...";

                        // Print the options    
                        printf("<option value=\"%d\">%s</option>\n", $page_id, $course_concat);
                }

        }

}


/**********************************************************
Function: dropDownAuthPages($con, $sess_access_level, $sess_staff_id)
Author: Paul Bramscher
Last Modified: 01.07.2003
***********************************************************
Incoming:
$con                    Database connection string
$sess_access_level      Access level of the current user
                        session.
$sess_staff_id          staff id of the current user
***********************************************************
Outgoing:
None
***********************************************************
Purpose:
Populates a drop-down box on an HTML form with select
options of PageScribe pages that the current
user has authorization to edit.  The following rules apply:

(1) DBA's can edit anything.
(2) Managers can edit pages created by anyone within their unit.
(3) Page coordinators can edit pages they coordinate.
(4) Page maintainers may also edit pages they are assigned to.

Some scenarios of pages that are NOT editable:

(1) The original page creator is no longer the coordinator,
not a DBA, and not assigned as a maintainer.  S/he can no
longer edit the page.
(2) You are a unit managager and used to have access to a page
coordinated by one of your employees.  S/he switches units,
and is now under a new manager.  The page can now be accessed
by the manager of the new unit and not yourself.

etc...

**********************************************************/
function dropDownAuthPages($con, $sess_access_level, $sess_staff_id){

        /* Access Table Definitions
        +-----------+--------------+---------+
        | access_id | access_level | access  |
        +-----------+--------------+---------+
        |         1 |            0 | Denied  |
        |         2 |           10 | Guest   |
        |         3 |           20 | Author  |
        |         4 |          100 | Manager |
        |         5 |         1000 | DBA     |
        +-----------+--------------+---------+
        */


        // Behave differently based on access level
        switch ($sess_access_level) {
        
                // Editor.  Start from the page table.
                case "20" :
                        $page_sql = "SELECT DISTINCT
                                p.page_id,
                                p.page_title
                                FROM page p
                                LEFT JOIN page_staff ps using (page_id)
                                WHERE p.pagetype_id = 2
                                AND
                                (ps.staff_id = "
                                . $sess_staff_id
                                . " OR p.staff_coordinator = "
                                . $sess_staff_id
                                . ") ORDER BY p.page_title";
                        break;

                // Manager.  Start from the libunit table, work down to pages
                case "100" :
                
                        // Determine libunit
                        $lu_sql = "SELECT libunit_id FROM libunit
                                WHERE head_staff_id = "
                                . $sess_staff_id;
                        $lu_rs = mysql_query($lu_sql, $con);
                        
                        $lu_string = "ls.libunit_id IN (";
                        $first_element = 0;
                        
                        // Concatenate the IN clause
                        while ($lu_row = mysql_fetch_array ($lu_rs)) {
                                $libunit_id = $lu_row["libunit_id"];
                                printf("libunit id was: %d<BR><BR>", $libunit_id);                              
        
                                if ($first_element == 0) {
                                        $first_element = 1;
                                        $lu_string .= $libunit_id;
                                }
                                else $lu_string .= ", " . $libunit_id;
                        }

                        // Cleanup
                        $lu_string .= ") OR";
        
                        // If nothing found, then return a blank string
                        if ($first_element == 0) $lu_string = "";
                                
                        $page_sql = "SELECT DISTINCT
                                p.page_id,
                                p.page_title
                                FROM page p
                                LEFT JOIN page_staff ps using (page_id)
                                LEFT JOIN libunit_staff ls on p.staff_coordinator = ls.staff_id
                                WHERE p.pagetype_id = 2
                                AND ("
                                . $lu_string
                                . " ps.staff_id = "
                                . $sess_staff_id
                                . " OR p.staff_coordinator = "
                                . $sess_staff_id
                                . ") ORDER BY p.page_title";
                        break;

                // DBA.  Access everything.
                case "1000" :
                        dropDownFieldOmit($con, "page", "page_title", "page_id", " WHERE pagetype_id = 2");
                        break;

                // Every other access level.  No pages at all!
                default :
                        break;
                
        }

        // Run the authorized page query if not DBA level
        if ($sess_access_level >= 20 && $sess_access_level < 1000) {

                $page_rs = mysql_query($page_sql, $con);

                while ($page_row = mysql_fetch_array ($page_rs)) {

                        // Collect the page information
                        $page_id = $page_row["page_id"];
                        $page_title = $page_row["page_title"];

                        if (strlen($page_title) > 39) $page_title = substr($page_title, 0, 39) . "...";

                        // Print the options            
                        printf("<option value=\"%d\">%s</option>\n", $page_id, $page_title);
                }


        // printf("sql was: %s<BR>", $page_sql);

        }

}


/**********************************************************
Function: dropDownAuthSubjects($con, $sess_access_level, $sess_staff_id)
Author: Paul Bramscher
Last Modified: 01.22.2003
***********************************************************
Incoming:
$con                    Database connection string
$sess_access_level      Access level of the current user
                        session.
$sess_staff_id          staff id of the current user
***********************************************************
Outgoing:
None
***********************************************************
Purpose:
Populates a drop-down box on an HTML form with select
options of SubjectBuilder pages that the current
user has authorization to edit.  The following rules apply:

(1) DBA's can edit anything.
(2) Managers can edit subjects on behalf of anyone within their unit.
(3) Any staffperson can manage subjects to which s/he is assigned.

**********************************************************/
function dropDownAuthSubjects($con, $sess_access_level, $sess_staff_id){

        /* Access Table Definitions
        +-----------+--------------+---------+
        | access_id | access_level | access  |
        +-----------+--------------+---------+
        |         1 |            0 | Denied  |
        |         2 |           10 | Guest   |
        |         3 |           20 | Author  |
        |         4 |          100 | Manager |
        |         5 |         1000 | DBA     |
        +-----------+--------------+---------+
        */


        // Behave differently based on access level
        switch ($sess_access_level) {
        
                // Editor.  Start from the subb_staff table.
                case "20" :
                        $sql = "SELECT
                                s.subject_id,
                                s.subject
                                FROM subject s
                                LEFT JOIN sub_staff ss using (subject_id)
                                WHERE ss.staff_id = "
                                . $sess_staff_id
                                . " ORDER BY s.subject";
                        break;

                // Manager.  Start from the libunit table, work down to pages
                case "100" :
                
                        // Determine libunit
                        $lu_sql = "SELECT libunit_id FROM libunit
                                WHERE head_staff_id = "
                                . $sess_staff_id;
                        $lu_rs = mysql_query($lu_sql, $con);
                        
                        $lu_string = "ls.libunit_id IN (";
                        $first_element = 0;
                        
                        // Concatenate the IN clause
                        while ($lu_row = mysql_fetch_array ($lu_rs)) {
                                $libunit_id = $lu_row["libunit_id"];
                                printf("libunit id was: %d<BR><BR>", $libunit_id);                              
        
                                if ($first_element == 0) {
                                        $first_element = 1;
                                        $lu_string .= $libunit_id;
                                }
                                else $lu_string .= ", " . $libunit_id;
                        }

                        // Cleanup
                        $lu_string .= ") OR";
        
                        // If nothing found, then return a blank string
                        if ($first_element == 0) $lu_string = "";
                                
                        $sql = "SELECT DISTINCT
                                s.subject_id,
                                s.subject
                                FROM subject s
                                LEFT JOIN sub_staff ss using (subject_id)
                                LEFT JOIN libunit_staff ls on ss.staff_id = ls.staff_id
                                WHERE "
                                . $lu_string
                                . " ss.staff_id = "
                                . $sess_staff_id
                                . " ORDER BY s.subject";

                        break;

                // DBA.  Access everything.
                case "1000" :
                        dropDownFieldOmit($con, "subject", "subject", "subject_id", "WHERE SUBJECT_ID > 1");
                        break;

                // Every other access level.  No pages at all!
                default :
                        break;
                
        }

        // Run the authorized page query if not DBA level
        if ($sess_access_level >= 20 && $sess_access_level < 1000) {

                $rs = mysql_query($sql, $con);

                while ($row = mysql_fetch_array ($rs)) {

                        // Collect the page information
                        $subject_id = $row["subject_id"];
                        $subject = $row["subject"];

                        // Print the option             
                        printf("<option value=\"%d\">%s</option>\n", $subject_id, $subject);
                }


        printf("sql was: %s<BR>", $sql);

        }

}


/**********************************************************
Function: dropDownCourses($con)
Author: Paul Bramscher
Last Modified: 11.26.2003
***********************************************************
Incoming:
$con                    Database connection string
***********************************************************
Outgoing:
None
***********************************************************
Purpose:
Populates a drop-down box on an HTML form with courseScribe
courses, listed by course name and designator.
**********************************************************/
function dropDownCourses($con) {

        $sql = "SELECT page_id, course_concat
                FROM course
                ORDER BY course_concat";
                
        $rs = mysql_query($sql, $con);
        while ($row = mysql_fetch_array ($rs)) {
                $page_id = $row["page_id"];
                $course_concat = $row["course_concat"];

                if (strlen($course_concat) > 45) $course_concat = substr($course_concat, 0, 45) . "...";
                
                printf("<option value = \"" . $page_id
                        . "\" >" . $course_concat . "</option>\n");
        };
}


/**********************************************************
Function: dropDownCoursesubOmit($con, $omit)
Author: Paul Bramscher
Last Modified: 06.04.2003
***********************************************************
Incoming:
$con                    Database connection string
$omit                   String of coursesub's to omit
***********************************************************
Outgoing:
None
***********************************************************
Purpose:
Populates a drop-down box on an HTML form with select
statements.  $omit limits output.
**********************************************************/
function dropDownCoursesubOmit($con, $omit){

        $sql = "SELECT coursesub, coursesub_descr, coursesub_id, cip_code FROM
                coursesub "
                . $omit
                . " ORDER BY coursesub_descr";
        
        // Fetch the values
        $rs = mysql_query($sql, $con);
        while ($row = mysql_fetch_array ($rs)) {
                $coursesub = $row["coursesub"];
                $coursesub_id = $row["coursesub_id"];
                $coursesub_descr = $row["coursesub_descr"];
                $cip_code = $row["cip_code"];
                
                // Limit length
                if (strlen($coursesub_descr) > 30) $coursesub_descr = substr($coursesub_descr, 0, 30) . "...";
                
                printf("<option value = \"%s\">%s | %s [%s]</option>", $coursesub_id, $coursesub, $coursesub_descr, $cip_code);
        }
}


/**********************************************************
Function: dropDownCoursesubSelected($con, $limit, $preselected)
Author: Paul Bramscher
Last Modified: 08.18.2003
***********************************************************
Incoming:
$con                    Database connection string
$limit                  Additional limit on the box
$preselected            Pre-selected course subject
***********************************************************
Outgoing:
None
***********************************************************
Purpose:
Populates a drop-down box on an HTML form with select
statements.  $limit limits output.
**********************************************************/
function dropDownCoursesubSelected($con, $limit, $preselected){

        $sql = "SELECT
                coursesub,
                coursesub_descr,
                coursesub_id,
                cip_code
                FROM coursesub ";

        // Concatenate a limit if provided      
        if (strlen($limit) > 0) $sql .= $limit;
                
        $sql .= " ORDER BY coursesub_descr";
        
        // Fetch the values
        $rs = mysql_query($sql, $con);
        while ($row = mysql_fetch_array ($rs)) {
                $coursesub = $row["coursesub"];
                $coursesub_id = $row["coursesub_id"];
                $coursesub_descr = $row["coursesub_descr"];
                $cip_code = $row["cip_code"];
                
                // Limit length
                if (strlen($coursesub_descr) > 25) $coursesub_descr = substr($coursesub_descr, 0, 30) . "...";

                printf("<option value = \"%d\"", $coursesub_id);
                if ($coursesub_id == $preselected) printf(" selected ");
                printf(">" . $coursesub . " | " . $coursesub_descr . " [" . $cip_code . "]</option>\n");

        }
}


/**********************************************************
Function: dropDownFaculty($con)
Author: Paul Bramscher
Last Modified: 12.02.2002
***********************************************************
Incoming:
$con                    Database connection string
***********************************************************
Outgoing:
None
***********************************************************
Purpose:
Populates a drop-down box on an HTML form with select
options of faculty members.  They are displayed and ordered
in the following format: "last name, first name (x500id)".
**********************************************************/
function dropDownFaculty($con){
        // Build the SQL.
        $sql = "SELECT *
                FROM faculty
                ORDER BY faculty_lastname, faculty_firstname, faculty_account";

        $rs = mysql_query($sql, $con);
        while ($row = mysql_fetch_array ($rs)) {
                $faculty_id = $row["faculty_id"];
                $faculty_lastname = $row["faculty_lastname"];
                $faculty_firstname = $row["faculty_firstname"];
                $faculty_account = $row["faculty_account"];

                // Make it look more friendly
                if ($faculty_id == 1) $faculty = "(N/A)";
                else {
                        $faculty = $faculty_lastname . ", " . $faculty_firstname;
                        if (strlen($faculty_account) > 0) $faculty .= " (" . $faculty_account . ")";
                }
                
                printf("<option value = \"" . $faculty_id
                        . "\" >" . $faculty . "</option>\n");
        };
}


/**********************************************************
Function: dropDownFacultyOmit($con, $omit)
Author: Paul Bramscher
Last Modified: 12.12.2002
***********************************************************
Incoming:
$con                    Database connection string
$omit                   String of human's to omit
***********************************************************
Outgoing:
None
***********************************************************
Purpose:
Populates a drop-down box on an HTML form with select
statements.  $omit limits output.
**********************************************************/
function dropDownFacultyOmit($con, $omit){
        $sql = "SELECT * FROM faculty "
                . $omit
                . " ORDER BY faculty_lastname, faculty_firstname";
        
        $rs = mysql_query($sql, $con);
        while ($row = mysql_fetch_array ($rs)) {
                $faculty_id = $row["faculty_id"];
                $faculty_lastname = $row["faculty_lastname"];
                $faculty_firstname = $row["faculty_firstname"];
                $faculty_account = $row["faculty_account"];

                // Make it look more friendly
                if ($faculty_id == 1) $faculty = "(N/A)";
                else {
                        $faculty = $faculty_lastname . ", " . $faculty_firstname;
                        if (strlen($faculty_account) > 0) $faculty .= " (" . $faculty_account . ")";
                }
                
                printf("<option value = \"" . $faculty_id
                        . "\" >" . $faculty . "</option>\n");
        };
}


/**********************************************************
Function: dropDownFacultySelected($con, $limit, $preselected)
Author: Paul Bramscher
Last Modified: 12.12.2002
***********************************************************
Incoming:
$con                    Database connection string
$limit                  Any WHERE clause
$preselected            Incoming faculty person to preselect
***********************************************************
Outgoing:
None
***********************************************************
Purpose:
Populates a drop-down box on an HTML form with select
statements.  $omit limits output.
**********************************************************/
function dropDownFacultySelected($con, $limit, $preselected){
        $sql = "SELECT * FROM faculty "
                . $limit
                . " ORDER BY faculty_lastname, faculty_firstname";
        
        $rs = mysql_query($sql, $con);
        while ($row = mysql_fetch_array ($rs)) {
                $faculty_id = $row["faculty_id"];
                $faculty_lastname = $row["faculty_lastname"];
                $faculty_firstname = $row["faculty_firstname"];
                $faculty_account = $row["faculty_account"];

                // Make it look more friendly
                if ($faculty_id == 1) $faculty = "(N/A)";
                else {
                        $faculty = $faculty_lastname . ", " . $faculty_firstname;
                }
                
                printf("<option value = \"%d\"", $faculty_id);
                if ($faculty_id == $preselected) printf(" selected ");
                printf(">" . $faculty . "</option>\n");
        };
}


/**********************************************************
Function: dropDownField($con, $table, $field_display,
$field_value)
Author: Paul Bramscher
Last Modified: 7.26.2000
***********************************************************
Incoming:
$con                    Database connection string
$table                  Table in database to search
$field_display          Select displayed to user
$field_value            Actual value of the HTML tag
***********************************************************
Outgoing:
None
***********************************************************
Purpose:
Populates a drop-down box on an HTML form with select
options.  They are ordered by the $field_display field.
Typically, $field_value is the primary key field.
**********************************************************/
function dropDownField($con, $table, $field_display, $field_value){

        $sql = "SELECT "
                . $field_display
                . ", "
                . $field_value
                . " FROM "
                . $table
                . " ORDER BY "
                . $field_display;
 
        $rs = mysql_query($sql, $con);
        while ($row = mysql_fetch_array ($rs)) {
                $field_display_item = $row[$field_display];
                if (strlen($field_display_item) > 40) $field_display_item = substr($field_display_item, 0, 39) . "...";
                $field_value_item = $row[$field_value];
                printf("<option value = \"" . $field_value_item
                        . "\" >" . $field_display_item . "</option>\n");
        };
}


/**********************************************************
Function: dropDownFieldOmit($con, $table, $field_display,
$field_value, $where)
Author: Paul Bramscher
Last Modified: 8.14.2002
***********************************************************
Incoming:
$con                    Database connection string
$table                  Table in database to search
$field_display          Select displayed to user
$field_value            Actual value of the HTML tag
$where                  SQL criteria in the list to exlude
***********************************************************
Outgoing:
None
***********************************************************
Purpose:
Populates a drop-down box on an HTML form with select
options.  They are ordered by the $field_display field.
Typically, $field_value is the primary key field.  $where
can be used to filter out results.
**********************************************************/
function dropDownFieldOmit($con, $table, $field_display,
        $field_value, $where){

        $sql = "SELECT "
                . $field_display
                . ", "
                . $field_value
                . " FROM "
                . $table
                . " "
                . $where
                . " ORDER BY "
                . $field_display;
        $rs = mysql_query($sql, $con);
        while ($row = mysql_fetch_array ($rs)) {
                $field_display_item = $row[$field_display];
                if (strlen($field_display_item) > 40) $field_display_item = substr($field_display_item, 0, 39) . "...";                
                $field_value_item = $row[$field_value];
                printf("<option value = \"" . $field_value_item
                        . "\" >" . $field_display_item . "</option>\n");
        };
}


/**********************************************************
Function: dropDownFieldSelected($con, $table, $field_display,
$field_value, $limit, $preselected)
Author: Paul Bramscher
Last Modified: 12.04.2002
***********************************************************
Incoming:
$con                    Database connection string
$table                  Table in database to search
$field_display          Select displayed to user
$field_value            Actual value of the HTML tag
$preselected            A selected $field_value
$limit                  A WHERE clause
***********************************************************
Outgoing:
None
***********************************************************
Purpose:
Populates a drop-down box on an HTML form with select
options.  They are ordered by the $field_display field.
Typically, $field_value is the primary key field.  The
parameter $preselected determines which (single) selection
is selected.
**********************************************************/
function dropDownFieldSelected($con, $table, $field_display,
        $field_value, $limit, $preselected){

        $sql = "SELECT "
                . $field_display
                . ", "
                . $field_value
                . " FROM "
                . $table
                . " "
                . $limit
                . " ORDER BY "
                . $field_display;
        
        // Debugging
        //printf("sql was: %s<BR>", $sql);
        
        $rs = mysql_query($sql, $con);
        while ($row = mysql_fetch_array ($rs)) {
                $field_display_item = $row[$field_display];
                $field_value_item = $row[$field_value];
                if (strlen($field_display_item) > 50) $field_display_item = substr($field_display_item, 0, 49) . "...";
                
                printf("<option value = \"" . $field_value_item . "\" ");
                if ($field_value_item == $preselected) printf (" selected");
                printf(">" . $field_display_item . "</option>\n");
        };
}


/**********************************************************
Function: dropDownInfotype($con, $infotype_id, $subject_id)
Author: Paul Bramscher
Last Modified: 12.04.2002
***********************************************************
Incoming:
$con                    Database connection string
$subject_id             Subject on which to limit the list
***********************************************************
Outgoing:
None
***********************************************************
Purpose:
Populates a drop-down box on an HTML form with select
options of information types including general and
master subject-specific.
**********************************************************/
function dropDownInfotype($con, $infotype_id, $subject_id){

        // Initialize
        $selected_infotype_id = $infotype_id;
        
        /*
        Collect a string of mastersubjects for this subject.
        This will be used in a later SQL query to limit list of infotypes based
        on subject.
        */
        
        $sql = "SELECT sm.mastersubject_id FROM sub_mastersubject sm WHERE sm.subject_id = "
                . $subject_id;

        // Debugging
        //printf("sql was: %s<BR>", $sql);

        // Build the string.  Start with the "(N/A)" and the "(ALL)" master subjects.
        $masterstring = "(1, 2 ";

        $rs = mysql_query($sql, $con);
        while ($row = mysql_fetch_array ($rs)) {
                $mastersubject_id = $row["mastersubject_id"];
                $masterstring .= ", " . $mastersubject_id;
        }
        $masterstring .= ")";

        // Build the list of infotypes appropriate to all of the mastersubjects found
        $sql = "SELECT i.infotype, i.infotype_id, mi.masterinfotype
        FROM infotype i, masterinfotype mi
        WHERE i.masterinfotype_id = mi.masterinfotype_id
        AND i.infotype_id > 0 and mi.masterinfotype_id > 0
        AND i.mastersubject_id IN " . $masterstring .
        " ORDER BY mi.masterinfotype";
        printf("sql was: %s<br>", $sql);

        $rs = mysql_query($sql, $con);
        while ($row = mysql_fetch_array ($rs)) {
                $masterinfotype = $row["masterinfotype"];
                $infotype_id = $row["infotype_id"];
                $infotype = $row["infotype"];


                // Make it look more friendly
                $useroutput = $masterinfotype . " -> " . $infotype;

                printf("<option value = \"%d\"", $infotype_id);
                if ($infotype_id == $selected_infotype_id) printf (" selected ");
                printf(">" . $useroutput . "</option>\n");
        };
}


/**********************************************************
Function: dropDownPageStaff($con, $page_id)
Author: Paul Bramscher
Last Modified: 10.23.2002
***********************************************************
Incoming:
$con                    Database connection string
$page_id                PageScribe page involved
***********************************************************
Outgoing:
None
***********************************************************
Purpose:
Populates a drop-down box on an HTML form with select
options of staff members.  They are displayed and ordered
in the following format: "last name, first name (x500id)".
The staff displayed are those associated as PageScribe
maintainers.
**********************************************************/
function dropDownPageStaff($con, $page_id){

        // Build the SQL.
        $sql = "SELECT s.first_name, s.last_name, s.staff_id, s.staff_account
                FROM staff s, page_staff ps
                WHERE ps.page_id = "
                . $page_id
                . " AND s.staff_id > 1 AND ps.staff_id = s.staff_id ORDER BY last_name, first_name, staff_account";

        $rs = mysql_query($sql, $con);
        while ($row = mysql_fetch_array ($rs)) {
                $staff_id = $row["staff_id"];
                $last_name = $row["last_name"];
                $first_name = $row["first_name"];
                $staff_account = $row["staff_account"];

                // Make it look more friendly
                $staff = $last_name . ", " . $first_name . " (" . $staff_account . ")";
                printf("<option value = \"" . $staff_id
                        . "\" >" . $staff . "</option>\n");
        };
}


/**********************************************************
Function: dropDownPageSubject($con, $page_id)
Author: Paul Bramscher
Last Modified: 06.17.2003
***********************************************************
Incoming:
$con                    Database connection string
$page_id                PageScribe page involved
***********************************************************
Outgoing:
None
***********************************************************
Purpose:
Populates a drop-down box on an HTML form with select
options of associated RQS subjects for the supplied page id.
**********************************************************/
function dropDownPageSubject($con, $page_id){

        // Build the SQL.
        $sql = "SELECT s.subject_id, s.subject
                FROM subject s, page_subject ps
                WHERE ps.page_id = "
                . $page_id
                . " AND s.subject_id > 1 AND ps.subject_id = s.subject_id ORDER BY s.subject";

        $rs = mysql_query($sql, $con);
        while ($row = mysql_fetch_array ($rs)) {
                $subject_id = $row["subject_id"];
                $subject = $row["subject"];

                printf("<option value = \"" . $subject_id
                        . "\" >" . $subject . "</option>\n");
        };
}


/**********************************************************
Function: dropDownResource($con, $key_id, $letter, $masterinfotype_id, $feature_id)
Author: Paul Bramscher
Last Modified: 05.28.2003
***********************************************************
Incoming:
$con                    Database connection string
$key_id                 "0" for a new resource, otherwise
                        indicates a pre-selected resource
$limit                  Starting with letter A-Z or freetext.
$masterinfotype_id      To limit by the masterinfotype.
$feature_id             To limit by feature id
***********************************************************
Outgoing:
None
***********************************************************
Purpose:
Populates an HTML drop-down box of resources in the
following format: ID#:TT:AA.  Where ID is the resource
ID#, TT is the first 35 char. of the title, and AA is the
first 35 characters of the author.

NOTE: Currently only titles are displayed, the author
portion has been commented out.

If incoming $limit is a single character (a letter), then
the query checks against all titles starting with it.
Otherwise it checks against all titles or annotations
containing that string.
**********************************************************/
function dropDownResource($con, $key_id, $limit, $masterinfotype_id, $feature_id){

        // Clean up the limit string
        if (strlen($limit) > 0) $limit = textSearchmySQL($limit);

        // Build the query
        $sql = "SELECT DISTINCT r.resource_id, r.title, r.author
                FROM resource r
                LEFT JOIN infotype i using (infotype_id)
                LEFT JOIN masterinfotype m on i.masterinfotype_id = m.masterinfotype_id
                LEFT JOIN res_feature rf on r.resource_id = rf.resource_id
                WHERE r.infotype_id = i.infotype_id AND i.masterinfotype_id = m.masterinfotype_id";
        
        // If a single letter, limit to title match
        if (strlen($limit) == 1) $sql .= " AND title LIKE '" . $limit . "%'";
        
        // If incoming freetext, query title or annotation
        if (strlen($limit) > 1) $sql .= " AND ((title LIKE '%" . $limit . "%') OR (annotation LIKE '%" . $limit . "%'))";
        

        // Limit by masterinfotype_id
        if ($masterinfotype_id > 0) $sql .= " AND m.masterinfotype_id = " . $masterinfotype_id;
        
        // Limit by feature
        if ($feature_id > 0) $sql .= " AND rf.feature_id = " . $feature_id;
        
        $sql .=" ORDER BY title, author, resource_id";
        
        // Debugging
        // printf("sql was: %s<BR>", $sql);
        
        $rs = mysql_query($sql, $con);
        while ($row = mysql_fetch_array ($rs)) {
                $resource_id = $row["resource_id"];
                $title = $row["title"];
                //$author = $row["author"];

                // Trim and add ellipsis
                if (strlen($title) > 65) $title = substr($title, 0, 65) . "...";
                //if (strlen($author) > 35) $author = substr($author, 0, 35) . "...";

                $display = $title;
                //if (strlen($author) > 0) $display .= " | " .$author;
                $display .= " | ID: " . $resource_id;

                if ($resource_id == $key_id) $selected = "selected";
                else $selected = "";

                printf("<option %s value = \""
                        . $resource_id
                        . "\" >"
                        . $display . "</option>\n", $selected);
        };
}


/**********************************************************
Function: dropDownServiceLimit($con, $servicetype_id)
Author: Paul Bramscher
Last Modified: 06.16.2003
***********************************************************
Incoming:
$con                    Database connection string
$servicetype_id         Service type to limit the picklist
***********************************************************
Outgoing:
None
***********************************************************
Purpose:
Populates a drop-down box on an HTML form with select
options for library services, limited to a supplied service
type id.
**********************************************************/
function dropDownServiceLimit($con, $servicetype_id){

        $sql = "SELECT DISTINCT s.service, s.service_id FROM service s
                LEFT JOIN serv_servtype ss using (service_id)
                LEFT JOIN servicetype v on ss.servicetype_id = v.servicetype_id";
                
                if ($servicetype_id > 0) {
                        $sql .= " WHERE ss.servicetype_id = "
                                . $servicetype_id;
                }
                
                $sql .= " ORDER BY s.service";
        
        // Debugging
        // printf("sql was: %s<BR>", $sql);
        
        $rs = mysql_query($sql, $con);
        while ($row = mysql_fetch_array ($rs)) {
                $service = $row["service"];
                $service_id = $row["service_id"];
                if (strlen($service) > 50) $service = substr($service, 0, 49) . "...";
                
                printf("<option value=\"%d\">%s</option>\n", $service_id, $service);
        };
}


/**********************************************************
Function: dropDownStaff($con)
Author: Paul Bramscher
Last Modified: 6.7.2001
***********************************************************
Incoming:
$con                    Database connection string
***********************************************************
Outgoing:
None
***********************************************************
Purpose:
Populates a drop-down box on an HTML form with select
options of staff members.  They are displayed and ordered
in the following format: "last name, first name (x500id)".
**********************************************************/
function dropDownStaff($con){

        // Build the SQL
        $sql = "SELECT *
                FROM staff
                ORDER BY last_name, first_name, staff_account";

        $rs = mysql_query($sql, $con);
        while ($row = mysql_fetch_array ($rs)) {
                $staff_id = $row["staff_id"];
                $last_name = $row["last_name"];
                $first_name = $row["first_name"];
                $staff_account = $row["staff_account"];

                // Make it look more friendly
                if ($staff_id == 1) $staff = "(N/A)";
                else $staff = $last_name . ", " . $first_name . " (" . $staff_account . ")";
                printf("<option value = \"" . $staff_id
                        . "\" >" . $staff . "</option>\n");
        };
}


/**********************************************************
Function: dropDownStaffOmit($con, $omit)
Author: Paul Bramscher
Last Modified: 10.08.2002
***********************************************************
Incoming:
$con                    Database connection string
$omit                   String of staff's to omit
***********************************************************
Outgoing:
None
***********************************************************
Purpose:
Populates a drop-down box on an HTML form with select
statements.  $omit limits output.
**********************************************************/
function dropDownStaffOmit($con, $omit){

        // Build the SQL
        $sql = "SELECT * from staff "
                . $omit
                . " ORDER BY last_name, first_name";
        
        $rs = mysql_query($sql, $con);
        while ($row = mysql_fetch_array ($rs)) {
                $last_name = $row["last_name"];
                $first_name = $row["first_name"];
                $staff_id = $row["staff_id"];
                $staff_account = $row["staff_account"];
                
                // Make it look more friendly
                if ($staff_id == 1) $staff = "(N/A)";
                else $staff = $last_name . ", " . $first_name . " (" . $staff_account . ")";
                printf("<option value = \"" . $staff_id
                        . "\" >" . $staff . "</option>\n");
        };
}


/**********************************************************
Function: dropDownStaffSelected($con, $selected_id)
Author: Paul Bramscher
Last Modified: 6.12.2001
***********************************************************
Incoming:
$con                    Database connection string
$selected_id            Selected staff id
***********************************************************
Outgoing:
None
***********************************************************
Purpose:
Populates a drop-down box on an HTML form with select
statements.  Selected id represents the preselected staff.
**********************************************************/
function dropDownStaffSelected($con, $selected_id){

        // Build the SQL
        $sql = "SELECT * from staff WHERE staff_id > 1 ORDER BY last_name, first_name";
        $rs = mysql_query($sql, $con);
        while ($row = mysql_fetch_array ($rs)) {
                $last_name = $row["last_name"];
                $first_name = $row["first_name"];
                $staff_id = $row["staff_id"];
                printf("<option value = \"" . $staff_id . "\" ");
                if ($staff_id == $selected_id) printf (" selected");
                printf(">" . $last_name . ", " . $first_name . "</option>\n");
        };
}


/**********************************************************
Function: existsFaculty
Author: Paul Bramscher
Last Modified: 06.24.2003
***********************************************************
Incoming:
$con                            Database connection string
$faculty_firstname              Faculty first name
$faculty_lastname               Faculty last name
***********************************************************
Outgoing:
"1" if a match exists, "0" if not
***********************************************************
Purpose:
This function checks to see whether a match against the
supplied faculty first and last name already exists.
**********************************************************/
function existsFaculty($con, $faculty_firstname, $faculty_lastname){

        // Build the sql
        $faculty_firstname = textInmySQL($faculty_firstname);
        $faculty_lastname = textInmySQL($faculty_lastname);        
        
        $sql = "SELECT * FROM faculty WHERE faculty_firstname = '"
                . $faculty_firstname
                . "' AND faculty_lastname = '"
                . $faculty_lastname
                . "'";

        $faculty_id = 0;
        $rs = mysql_query($sql, $con);
        
        // Concatenate the NOT IN clause
        while ($row = mysql_fetch_array ($rs)) {
                if ($faculty_id == 0) $faculty_id = $row["faculty_id"];
        }

        return $faculty_id;
}


/**********************************************************
Function: existsResSub
Author: Paul Bramscher
Last Modified: 12.09.2002
***********************************************************
Incoming:
$con                            Database connection string
$resource_id                    Record ID
$subject_id                     Subject ID
***********************************************************
Outgoing:
"1" if an association already exists, "0" if not
***********************************************************
Purpose:
This function checks to see whether a resource has already
been attached to a given subject id.
**********************************************************/
function existsResSub($con, $resource_id, $subject_id){

        // Build the sql
        $sql = "SELECT * FROM res_sub_infotype WHERE resource_id = "
                . $resource_id
                . " AND subject_id = "
                . $subject_id;

        $rs = mysql_query($sql, $con);
        $rowcount = mysql_num_rows($rs);
        if ($rowcount >= 1){
                $rowcount = 1;
        }
        else $rowcount = 0;
        
        return $rowcount;
}


/**********************************************************
Function: existsResSubNA
Author: Paul Bramscher
Last Modified: 05.27.2003
***********************************************************
Incoming:
$con                            Database connection string
$resource_id                    Record ID
$subject_id                     Subject ID
***********************************************************
Outgoing:
"1" if an association already exists, "0" if not
***********************************************************
Purpose:
This function is similar to existsResSub, but with checks
specifically to see whether the (N/A) type information type
is currently assigned to the resource-subject combination.
**********************************************************/
function existsResSubNA($con, $resource_id, $subject_id){

        // Build the sql
        $sql = "SELECT * FROM res_sub_infotype WHERE resource_id = "
                . $resource_id
                . " AND subject_id = "
                . $subject_id
                . " AND infotype_id = 1";

        $rs = mysql_query($sql, $con);
        $rowcount = mysql_num_rows($rs);
        if ($rowcount == 1){
                $rowcount = 1;
        }
        else $rowcount = 0;
        
        return $rowcount;
}


/**********************************************************
Function: existsRow
Author: Paul Bramscher
Last Modified: 12.09.2002
***********************************************************
Incoming:
$con                    Database connection string
$table                  Table to search
$key_field              Field against which to search
$key_id                 Limit for the where clause
***********************************************************
Outgoing:
"1" if the row exists, "0" if not
***********************************************************
Purpose:
Useful in performing delete and other operations to make
sure that a row entity exists matching the supplied key
before going any further.
**********************************************************/
function existsRow($con, $table, $key_field, $limit_id){
        // Cast as integer to avoid future problems
        $limit_id = (int) $limit_id;
        
        // Build the sql
        $sql = "SELECT "
                . $key_field
                . " FROM "
                .  $table
                . " WHERE "
                . $key_field
                . "="
                . $limit_id;

        $rs = mysql_query($sql, $con);
        $rowcount = mysql_num_rows($rs);
        if ($rowcount >= 1){
                $rowcount = 1;
        }
        else $rowcount = 0;

        return $rowcount;
}


/**********************************************************
Function: getNotIn
Author: Paul Bramscher  
Last Modified: 12.09.2002
***********************************************************
Incoming:
$con                    Database connection string
$in_field               Name of ID field to return
$static_field           Name of the "other" ID field in the
                        bridging table
$static_value           Value for the other ID field to
                        limit by
$table                  Bridging table involved
***********************************************************
Outgoing:
$in_string              Concatenated string of $in_field
                        ID's to be excluded from a drop-down
                        box.
***********************************************************
Purpose:
This applies to a bridging table with a dual primary key.
For example, in the res_loc table, produce a list of all
location_id's associated with a particular resource_id.
This list is comma-separated and returned to an
assignment-type form in which drop-down boxes of the
current selections are displayed alongside remaining
selections.  The remaining (available) selections need to
exclude the current ones.  Thus, this function builds the
NOT IN portion of the SQL WHERE clause.
**********************************************************/
function getNotIn($con, $in_field, $static_field,
        $static_value, $table) {

        // Initialze
        $in_string = "(";
        $first_element = 0;

        // Build the sql
        $sql = "SELECT "
                . $in_field
                . " FROM "
                . $table
                . " WHERE "
                . $static_field
                . " = "
                . $static_value;
        
        $rs = mysql_query($sql, $con);

        // Concatenate the NOT IN clause
        while ($row = mysql_fetch_array ($rs)) {
                $in_element = $row[$in_field];

                if ($first_element == 0) {
                        $first_element = 1;
                        $in_string .= "'" . $in_element . "'";
                }
                else $in_string .= ", '" . $in_element . "'";
        }

        // Cleanup
        $in_string .= ")";
        
        // If nothing found, then return a blank string
        if ($first_element == 0) $in_string = "";

        // Done, return to calling function
        return $in_string;
}


/**********************************************************
Function: locationList($con, $resource_id)
Author: Paul Bramscher
Last Modified: 07.02.2002
***********************************************************
Incoming:
$con                    Database connection string
$resource_id            Resource ID to check locations on
***********************************************************
Outgoing:
None
***********************************************************
Purpose:
Looks up location(s) for this resource
**********************************************************/
function locationList($con, $resource_id){

        // Initialize
        $locations = "";
        $first = 1;

        $sql = "SELECT location FROM location l, res_loc rl WHERE rl.resource_id = "
                . $resource_id
                . " AND rl.location_id = l.location_id ORDER BY location";

        $rs = mysql_query($sql, $con);
        while ($row = mysql_fetch_array ($rs)) {
                $location = $row["location"];
                if ($first == 1) {
                        $first = 0;
                        $locations = $location;
                }
                else $locations .= "; " . $location;
        }
        
        return $locations;
}


/**********************************************************
Function: lookupFaculty($con, $faculty_id)
Author: Paul Bramscher
Last Modified: 12.03.2002
***********************************************************
Incoming:
$con                    Database connection string
$faculty_id             Faculty ID number to retrieve
***********************************************************
Outgoing:
Faculty name in {First Name} {Last Name} format.
***********************************************************
Purpose:
Performs a lookup on faculty ID to retrieve name.
**********************************************************/
function lookupFaculty($con, $faculty_id){
        $sql = "SELECT faculty_firstname, faculty_lastname FROM faculty WHERE faculty_id = "
                . $faculty_id;
        $rs = mysql_query($sql, $con);
        $row = mysql_fetch_array ($rs);
        $faculty_lastname = $row["faculty_lastname"];
        $faculty_firstname = $row["faculty_firstname"];
        
        $faculty_name .= $faculty_firstname . " " . $faculty_lastname;
        
        // Debugging
        //printf("sql was: %s<BR>", $sql);
        
        return $faculty_name;
}


/**********************************************************
Function: lookupField($con, $table, $key_field, $key_value,
$desc_field)
Author: Paul Bramscher
Last Modified: 9.14.2000
***********************************************************
Incoming:
$con                    Database connection string
$table                  Table in database to search
$key_field              Name of field on which to lookup
$key_value              Value to attempt lookup with
$desc_field             Descriptive value to return
***********************************************************
Outgoing:
A descriptive name for an ID number. 
***********************************************************
Purpose:
Performs a lookup, typically on an ID field, to retrieve a
user-friendly descriptive name to the page.
**********************************************************/
function lookupField($con, $table, $key_field, $key_value,
        $desc_field){
        
        $sql = "SELECT "
                . $desc_field
                . " FROM "
                . $table
                . " WHERE "
                . $key_field
                . "='"
                . $key_value
                . "'";
        $rs = mysql_query($sql, $con);
        $row = mysql_fetch_array ($rs);
        if (mysql_num_rows($rs) == 1) $result = $row[$desc_field];
        else $result = "";
        
        return $result;
}


/**********************************************************
Function: lookupStaff($con, $staff_id)
Author: Paul Bramscher
Last Modified: 7.22.2002
***********************************************************
Incoming:
$con                    Database connection string
$staff_id               Staff ID number to retrieve
***********************************************************
Outgoing:
Staff name in {First Name} {Last Name} format.
***********************************************************
Purpose:
Performs a lookup on staff ID to retrieve name.
**********************************************************/
function lookupStaff($con, $staff_id){

        $sql = "SELECT first_name, last_name FROM staff WHERE staff_id = "
                . $staff_id;
        $rs = mysql_query($sql, $con);
        $row = mysql_fetch_array ($rs);
        $last_name = $row["last_name"];
        $first_name = $row["first_name"];
        
        $staff_name .= $first_name . " " . $last_name;
        
        // Debugging
        //printf("sql was: %s<BR>", $sql);
        
        return $staff_name;
}


/**********************************************************
Function: radioField($con, $table, $field_display,
$field_value, $position)
Author: Paul Bramscher
Last Modified: 12.04.2002
***********************************************************
Incoming:
$con                    Database connection string
$table                  Table in database to search
$field_display          Select displayed to user
$field_value            Actual value of the HTML tag
$position               Either "V" vertical or "H"
                        horizontal to indicate display of
                        radio buttons
***********************************************************
Outgoing:
None
***********************************************************
Purpose:
Populates radio boxes on an HTML form with values from a
table.  They are ordered by $field_display in either
horizontal or vertical fashion.  Each checkbox in the set
is named $field_display.
**********************************************************/
function radioField($con, $table, $field_display,
$field_value, $positon){

        $rd_sql = "SELECT "
                . $field_display
                . ", "
                . $field_value
                . " FROM "
                . $table
                . " ORDER BY "
                . $field_display;
        $rd_rs = mysql_query($rd_sql, $con);
        while ($rd_row = mysql_fetch_array ($rd_rs)) {
                $field_display_item = $rd_row[$field_display];
                $field_value_item = $rd_row[$field_value];
                printf("<input type='radio' name='"
                        . $field_display
                        . " value =\"" 
                        . $field_value_item
                        . "\" > "
                        . $field_display_item
                        . " ");
                if ($position =="V"){
                        printf("<BR>\n");
                }
        };
}


/**********************************************************
Function: recordCount($con, $table, $limit_field,
        $limit_where, $type)
Author: Paul Bramscher
Last Modified: 01.15.2004
***********************************************************
Incoming:
$con                    Database connection string
$table                  Table in database to query
$limit_field            Field to limit
$limit_where            Criteria upon which to limit  
$type                   "N" for numeric or "C" for character  
***********************************************************
Outgoing:
None
***********************************************************
Purpose:
This function is typically used in conjunction with a
printf statement elsewhere "This operation affects
N record(s).  Do you wish to proceed?"  This function
returns a value for N.
**********************************************************/
function recordCount($con, $table, $limit_field,
        $limit_where, $type) {
        
        // Guard against single quotes
        $limit_where = addslashes($limit_where);

        $sql = "SELECT "
                . $limit_field
                . " FROM "
                . $table
                . " WHERE "
                . $limit_field
                . "=";

        // If the field is numeric
        if ($type == "N") {
                $sql .= $limit_where;
        }

        // If character, use single-quotes
        else {
                $sql .= "'"
                        .$limit_where
                        . "'";
        }

        // Open the query and take a row count
        $rs = mysql_query($sql, $con);
        $count = mysql_num_rows($rs);
        
        return $count;
}


/**********************************************************
Function: selectCoursesub($con, $limit)
Author: Paul Bramscher
Last Modified: 07.02.2003
***********************************************************
Incoming:
$con                    Database connection string
$limit                  String to limit the results by
***********************************************************
Outgoing:
None
***********************************************************
Purpose:
Draws a table with all course subjects for basic
command add/edit/ delete selection.
**********************************************************/
function selectCoursesub($con, $limit){

        printf("<table width=\"90%%\" border = \"1\" cellpadding = \"2\" >");
        printf("<tr>");
        printf("<td class = \"cellPlain\">ID</td>");
        printf("<td class = \"cellPlain\">Subject</td>");
        printf("<td class = \"cellPlain\">Subject Description</td>");
        printf("<td class = \"cellPlain\">Campus</td>");
        printf("<td class = \"cellPlain\">CIP Code</td>");
        printf("<td class = \"cellPlain\">Select</td>");
        printf("</tr>");

        // Build the sql
        $sql = "SELECT 
                c.coursesub_id,
                c.coursesub,
                c.coursesub_descr,
                c.cip_code,
                p.campus
                FROM
                coursesub c
                LEFT JOIN campus p on c.campus_id = p.campus_id
                WHERE coursesub_id > 1";
                
        if (strlen($limit) > 0 && $limit != "*") {
        
                $limit = textSearchmySQL($limit);
        
                $sql .= " AND (coursesub LIKE '%"
                . $limit
                . "%' OR coursesub_descr LIKE '%"
                . $limit
                . "%')";

        }

        // Order the display
        $sql .= " ORDER BY coursesub_descr";

        $rs = mysql_query($sql, $con);
        
        // Initialize row counter
        $rowcount = 0;
        
        while ($row = mysql_fetch_array ($rs)) {
                $coursesub_id = $row["coursesub_id"];
                $coursesub = Trim($row["coursesub"]);
                $coursesub_descr = Trim($row["coursesub_descr"]);
                $campus = Trim($row["campus"]);
                $cip_code = Trim($row["cip_code"]);
                
                // Make every other row colored
                if ($rowcount % 2 == 0) $color = " class=\"backLight\" ";
                else $color = "";               
        
                printf("<tr>");

                printf("<td %s>%d</td>", $color, $coursesub_id);
                printf("<td %s>%s</td>", $color, $coursesub);
                printf("<td %s>%s</td>", $color, $coursesub_descr);
                printf("<td %s>%s</td>", $color, $campus);
                printf("<td %s>%s</td>", $color, $cip_code);

                printf("<td %s>&nbsp;<input type = \"Radio\" name = \"coursesub_id\" value =\"%d\" >&nbsp;", $color, $coursesub_id);

                printf("</tr>");
                
                $rowcount++;
        };
        printf("</table>");
}


/**********************************************************
Function: selectFaculty($con)
Author: Paul Bramscher
Last Modified: 07.02.2002
***********************************************************
Incoming:
$con                    Database connection string
***********************************************************
Outgoing:
None
***********************************************************
Purpose:
Draws a table with all faculty for basic command add/edit/
delete selection.
**********************************************************/
function selectFaculty($con){

        printf("<table width=\"90%%\" border = \"1\" cellpadding = \"2\" >");
        printf("<tr>");
        printf("<td class = \"cellPlain\">ID</td>");
        printf("<td class = \"cellPlain\">Faculty name</td>");
        printf("<td class = \"cellPlain\">Account</td>");
        printf("<td class = \"cellPlain\">Email</td>");
        printf("<td class = \"cellPlain\">Select</td>");
        printf("</tr>");

        // Build the sql
        $sql = "SELECT 
                f.faculty_id,
                f.faculty_firstname,
                f.faculty_lastname,
                f.faculty_account,
                f.faculty_email
                FROM
                faculty f
                WHERE faculty_id > 1";

        // Order the display
        $sql .= " ORDER BY faculty_lastname, faculty_firstname";

        $rs = mysql_query($sql, $con);
        
        // Row counter
        $rowcount = 0;
        
        while ($row = mysql_fetch_array ($rs)) {
                $faculty_id = $row["faculty_id"];
                $faculty_firstname = Trim($row["faculty_firstname"]);
                $faculty_lastname = Trim($row["faculty_lastname"]);
                $faculty_account = $row["faculty_account"];
                $faculty_email = $row["faculty_email"];         

                // For display purposes
                if (strlen($faculty_account) < 1) $faculty_account = "&nbsp;";
                if (strlen($faculty_email) < 1) $faculty_email = "&nbsp;";

                // Make every other row colored
                if ($rowcount % 2 == 0) $color = " class=\"backLight\" ";
                else $color = "";  
        
                printf("<tr>");

                printf("<td %s>%d</td>", $color, $faculty_id);
                printf("<td %s>%s, %s</td>", $color, $faculty_lastname, $faculty_firstname);
                printf("<td %s>%s</td>", $color, $faculty_account);
                printf("<td %s>%s</td>", $color, $faculty_email);

                printf("<td %s>&nbsp;<input type = \"Radio\" name = \"faculty_id\" value =\"%d\" >&nbsp;", $color, $faculty_id);

                printf("</tr>");
                
                $rowcount++;
        };
        printf("</table>");
}


/**********************************************************
Function: selectKey($con, $display, $display_field, 
$key_field, $limit, $table)
Author: Paul Bramscher
Last Modified: 06.25.2003
***********************************************************
Incoming:
$con                    Database connection string
$display                A form header to display to user
$display_field          Meaningful field in the table to display
$key_field              The table primary key (no composites)
$table                  Table in database to search
$limit                  Field to limit $field_display by
***********************************************************
Outgoing:
None
***********************************************************
Purpose:
Does a simple text dump to show descriptive values and
their associated primary keys in a small table.  If
$limit is defined, then limit the result set to display
only $display_field which matches a like cause.  Each
result is indicated by a radio button named key_id to be
used in a form to select one of them for further
processing (editing, deleting, etc).

Note that the pick-list filters out the primary key #1,
(N/A) rows which are present in many tables as system
placeholders.
**********************************************************/
function selectKey($con, $display, $display_field,
        $key_field, $limit, $table){

        $sql = "SELECT "
                . $display_field
                . ", "
                . $key_field
                . " FROM "
                . $table
                . " WHERE ("
                . $key_field
                . " != 1 AND "
                . $display_field
                . " != '(N/A)')";

        if (strlen($limit) > 0){

                // Attach a limit unless "*" is indicated
                if ($limit != "*") {
                        $sql .= " AND "
                                . $display_field
                                . " LIKE '%"
                                . $limit
                                . "%'";

                }
                
                
                // Order the display
                $sql .= " ORDER BY " . $display_field;
        
                $rs = mysql_query($sql, $con);

                printf("<table width=\"60%%\" border = \"1\" >\n");
                printf("<tr><td class=\"cellPlain\">Description</td>\n");
                // printf("<td><b>Key</b></td>");
                printf("<td class=\"cellPlain\">Select</td></tr>\n");

                $rowcount = 0;

                while ($row = mysql_fetch_array ($rs)) {
                        $field_display_item = $row[$display_field];
                        $field_value_item = (int) Trim($row[$key_field]);
                        
                        // Make every other row colored
                        if ($rowcount % 2 == 0) $color = " class=\"backLight\" ";
                        else $color = "";                       
                        
                        printf("<tr>\n");
                        printf("<td %s>%s", $color, $field_display_item);
                        printf("</td>\n");
                        printf("<td %s><input type =\"Radio\" name =\"key_id\" value =\"%s\"></td>\n",
                                $color, $field_value_item);
                        printf("</tr>\n");
                        
                        /*
                        printf("<tr><td>"
                                . $field_display_item
                                . "</td><td>"
                                . "<input type = \"Radio\" name = \"key_id\" value =\""
                                . $field_value_item
                                . "\"></td></tr>\n");
                        */
                                
                        $rowcount++;                            
                };
                printf("</table>\n");
        }
        else {
                printf("You must limit by some criteria before proceeding.<BR>\n");
        }
}


/**********************************************************
Function: selectStaff($con)
Author: Paul Bramscher
Last Modified: 04.28.2003
***********************************************************
Incoming:
$con                    Database connection string
***********************************************************
Outgoing:
None
***********************************************************
Purpose:
Draws a table with all staff for basic command add/edit/
delete selection.
**********************************************************/
function selectStaff($con){

        printf("<table width=\"90%%\" border = \"1\" cellpadding = \"2\" >");
        printf("<tr>");
        printf("<td class = \"cellPlain\">ID</td>");
        printf("<td class = \"cellPlain\">Staff name</td>");
        printf("<td class = \"cellPlain\">Staff Account</td>");
        printf("<td class = \"cellPlain\">Local pwd?</td>");
        printf("<td class = \"cellPlain\">Access</td>");
        printf("<td class = \"cellPlain\">Level</td>"); 
        printf("<td class = \"cellPlain\">Last login</td>");
        printf("<td class = \"cellPlain\">Last ip</td>");
        printf("<td class = \"cellPlain\">Select</td>");
        printf("</tr>");

        // Build the sql
        $sql = "SELECT 
                s.staff_id,
                s.staff_account,
                s.password,
                s.first_name,
                s.last_name,
                s.last_login,
                s.last_ip,
                a.access,
                a.access_level
                FROM
                staff s, access a
                WHERE staff_id > 1 AND s.access_id = a.access_id";

        // Order the display
        $sql .= " ORDER BY last_name, first_name";

        $rs = mysql_query($sql, $con);
        
        $rowcount = 0;
        
        while ($row = mysql_fetch_array ($rs)) {
                $first_name = Trim($row["first_name"]);
                $last_name = Trim($row["last_name"]);
                $staff_account = $row["staff_account"];
                $staff_id = $row["staff_id"];
                $access = Trim($row["access"]);
                $access_level = Trim($row["access_level"]);             
                $last_login = Trim($row["last_login"]);
                $last_ip = Trim($row["last_ip"]);

                // See whether the local password has been set
                if (strlen($row["password"]) > 0) $local_pwd = "<b>Yes</b>";
                else $local_pwd = "No";

                // For display purposes
                if (strlen($last_login) < 1) $last_login = "&nbsp;";
                if (strlen($last_ip) < 1) $last_ip = "&nbsp;";

                // Make every other row colored
                if ($rowcount % 2 == 0) $color = " class=\"backLight\" ";
                else $color = "";
        
                printf("<tr>");

                printf("<td %s>%d</td>", $color, $staff_id);
                printf("<td %s>%s, %s</td>", $color, $last_name, $first_name);
                printf("<td %s>%s</td>", $color, $staff_account);
                printf("<td %s>%s</td>", $color, $local_pwd);
                printf("<td %s>%s</td>", $color, $access);
                printf("<td %s>%d</td>", $color, $access_level);
                printf("<td %s>%s</td>", $color, $last_login);
                printf("<td %s>%s</td>", $color, $last_ip);

                printf("<td %s>&nbsp;<input type = \"Radio\" name = \"staff_id\"
                        value =\"%d\" >&nbsp;", $color, $staff_id);

                printf("</tr>");
                
                $rowcount++;
        };
        printf("</table>");
}


/**********************************************************
Function: statQuery($con, $sql)
Author: Paul Bramscher
Last Modified: 06.24.2003
***********************************************************
Incoming:
$con                    Database connection string
$sql                    A SQL statement to execute.  The
                        SELECT must have a COUNT(*) in it.
                        The stat logic should be in WHERE.
***********************************************************
Outgoing:
$result                 The numerical result of the query,
                        or else "UNKNOWN" in case of error.
***********************************************************
Purpose:
Accepts a simple SQL statement, performs the lookup, and
drawns the result in a two-column table row.  The first
column is $label explaining the purpose of the lookup,
the second column is the numerical figure of the $sql.
**********************************************************/
 
function statQuery($con, $sql){
        $rs = mysql_query($sql, $con);
        $row = mysql_fetch_array ($rs);

        // We should be getting only a single row.
        if (mysql_num_rows($rs) == 1) $result = $row["COUNT(*)"];
        else $result = "UNKNOWN";

        return $result;
}


/**********************************************************
Function: textInmySQL
Author: Paul Bramscher
Last Modified: 09.23.2003
***********************************************************
Purpose:
This function creates filtering rules for input into
mySQL.  Depending on your configuration of the PHP
variable magic_quotes_gpc, security needs, and possible
foreign character set issues you may want to change this.
**********************************************************/
function textInmySQL($incoming) {
        
        // Replace single quotes with two single quotes 
        $outgoing = ereg_replace("'","''",$incoming);

        return $outgoing;

}

/**********************************************************
Function: textOutHTML
Author: Paul Bramscher
Last Modified: 04.21.2003
***********************************************************
Purpose:
This function will help render some data more presentable
on HTML forms, particularly pre-setting form field values
which might contain a double-quote.  This function may also
be extended to cover other cases.
**********************************************************/
function textOutHTML($incoming) {

        // Replace double quotes with the HTML &quot;
        $outgoing = ereg_replace("\"","&quot;",$incoming);

        return $outgoing;

}


/**********************************************************
Function: textSearchmySQL
Author: Paul Bramscher
Last Modified: 05.20.2003
***********************************************************
Purpose:
This function is to be used on HTML search forms
that might contain a single quote.  They are escaped with
a backslash.  Any other potential SELECT filtering can
happen here.
**********************************************************/
function textSearchmySQL($incoming) {

        // Replace single quotes with two single quotes
        // You may need to uncomment this.
        $outgoing = ereg_replace("'","\'",$incoming);

        // You may need to comment this out, and uncomment the previous.
        //$outgoing = $incoming;

        return $outgoing;

}
?>