branches/paul_xx/public_controls.php

<?php
/**********************************************************
Function Library: public_controls.php
Original Author: Paul Bramscher <brams006@umn.edu>
Last Modified: 03.04.2004
***********************************************************
Comments:
Publicly accessible and executable functions.
***********************************************************
Table of Contents:
displayCourseHeader
displayCoursePers
displayRelatedPages
displayRelatedSubjects
displayTOC
dropDownCoursesubSelected
dropDownFacultySelected
dropDownFieldOmit
dropDownFieldSelected
existsRow
lookupField
lookupStaff
pageLoadStats
printAlphabet
subLoadStats
textOutHTML
textSearchmySQL
**********************************************************/


/**********************************************************
Function: displayCourseHeader
Author: Paul Bramscher
Last Modified: 11.21.2003
***********************************************************
Purpose:
Output the course related information for a course page.
**********************************************************/  
function displayCourseHeader($page_id, $pagetitle_style) {

        // Retrieve current information
        $sql = "SELECT *
                FROM
                course c
                LEFT JOIN term t using (term_id)                
                LEFT JOIN campus p on c.campus_id = p.campus_id 
                LEFT JOIN coursesub s on c.coursesub_id = s.coursesub_id
                
                WHERE
                page_id = " . $page_id;
        
        $rs = xx_tryquery($sql);
        $row = xx_fetch_array ($rs, xx_ASSOC);

        $course_id = $row["course_id"]; 
        $staff_id_created = $row["staff_id_created"];
        $staff_id_edited = $row["staff_id_edited"];
        $coursesub_id = $row["coursesub_id"];
        $coursesub = $row["coursesub"]; 
        $course_num = $row["course_num"];
        $course_section = $row["course_section"];
        $course_concat = $row["course_concat"]; 
        $term_id = $row["term_id"];
        $term = $row["term"];   
        $course_year = $row["course_year"];
        $campus_id = $row["campus_id"];
        $campus = $row["campus"];
        $courseheader = $row["courseheader"];   
        $introheader1 = $row["introheader1"];
        $intromessage1 = $row["intromessage1"];
        $introheader2 = $row["introheader2"];
        $intromessage2 = $row["intromessage2"];
        
        // Course Header
        if (strlen($courseheader) > 1) printf("%s<BR>\n", $courseheader);
        
        // Start the span class
        if ($pagetitle_style > 0) printf("<span class=\"S%d\">%s</span>", $pagetitle_style, $course_concat);
        else printf("%s", $course_concat);
        printf("<BR><BR>\n");
        
        // Display course term
        if (strlen($term) > 1 && $term_id > 1) printf("%s", $term);
        
        // Display Comma
        if (strlen($term) > 1 && strlen($course_year) > 1) printf(", ");
        
        // Display course year
        if (strlen($course_year) > 1) printf("%s", $course_year);
        
        printf("<BR>");
        
        // Campus
        if (strlen($campus) > 0 && $campus_id > 1) printf("%s<BR>\n", $campus);
        
        // Intro header1
        if (strlen($introheader1) > 0) {
                printf("<center><b>\n");
                printf("%s", $introheader1);
                printf("</b></center><br>\n");
        
        }
        
        // Intro Message1
        if (strlen($intromessage1) > 0) {       
                printf("%s", $intromessage1);
                printf("<br>\n");
        }
        
        // Intro header2
        if (strlen($introheader2) > 0) {
                printf("<center><b>\n");
                printf("%s", $introheader2);
                printf("</b></center><br>\n");
        
        }
        
        // Intro Message2
        if (strlen($intromessage2) > 0) {       
                printf("%s", $intromessage2);
                printf("<br>\n");
        }

}


/**********************************************************
Function: displayCoursePers
Author: Paul Bramscher
Last Modified: 03.04.2004
***********************************************************
Purpose:
Displays the personnel associated with the course, by
looking up the course id based on the page id.
**********************************************************/
function displayCoursePers($page_id) {

        // Determine the course id
        $course_id = lookupfield("course", "page_id", $page_id, "course_id");

        // Retrieve current information
        $sql = "SELECT *
                FROM
                course_personnel cp
                LEFT JOIN staff s using (staff_id)
                LEFT JOIN stafftitle st on cp.stafftitle_id = st.stafftitle_id
                LEFT JOIN faculty f on cp.faculty_id = f.faculty_id
                
                WHERE
                course_id = " . $course_id . " ORDER BY personnel_id";
        
        $rs = xx_tryquery($sql);
        
        // See how many in this record set
        $num_personnel = xx_num_rows($rs);

        if ($num_personnel > 0) {

                // Display course personnel
                printf("<BR>\n");
                printf("<b>Personnel:</b><br>\n");


                while ($row = xx_fetch_array ($rs, xx_ASSOC)) {

                        // Initialize
                        $last_name = "";
                        $first_name = "";
                        $email = "";
                        $stafftitle = "";

                        // Fetch the general stuff
                        $personnel_id = $row["personnel_id"];
                        $staff_id = $row["staff_id"];
                        $faculty_id = $row["faculty_id"];
                        $stafftitle = $row["stafftitle"];
                        $stafftitle_id = $row["stafftitle_id"];

                        // Row is staff. (Not 0, NULL, 1=N/A).
                        if ($staff_id > 1) {

                                // Gather the staff table fields
                                $last_name = $row["last_name"];
                                $first_name = $row["first_name"];
                                $email = $row["staff_email"];

                        }

                        // Row is faculty (Not 0, NULL, 1=N/A).
                        else if ($faculty_id > 1) {

                                // Gather the faculty table fields
                                $last_name = $row["faculty_lastname"];
                                $first_name = $row["faculty_firstname"];
                                $email = $row["faculty_email"];

                        }

                        // Row is "other", use the freetext fields
                        else {

                                // Gather the catch-all fields
                                $last_name = $row["pers_lastname"];
                                $first_name = $row["pers_firstname"];
                                $email = $row["pers_email"];
                        }


                        // Display the data
                        if ($stafftitle_id > 1) printf("%s: ", $stafftitle);
                        printf("%s %s <a href=\"mailto:%s\">%s</a><br>\n",
                                $first_name, $last_name, $email, $email);

                } // all course_personnel rows

        } // if there were any persons attached to this course

}


/**********************************************************
Function: displayRelatedPages($subject_id)
Author: Paul Bramscher
Last Modified: 03.04.2004
***********************************************************
Incoming:
$subject_id             Subject of the RQS page
***********************************************************
Outgoing:
None
***********************************************************
Purpose:
Displays any PageScribe pages associated with the supplied
subject id.
**********************************************************/
function displayRelatedPages($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 = xx_tryquery($sql);
        while ($row = xx_fetch_array ($rs, xx_ASSOC)) {
                $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: displayRelatedSubjects($subject_id)
Author: Paul Bramscher
Last Modified: 03.04.2004
***********************************************************
Incoming:
$subject_id             Subject of the RQS page
***********************************************************
Outgoing:
None
***********************************************************
Purpose:
Displays any RQS pages associated with the supplied
subject id.
**********************************************************/
function displayRelatedSubjects($subject_id){

        $sql = "SELECT s.subject, so.othersub_id
                FROM subject s, sub_othersub so
                WHERE so.subject_id = "
                . $subject_id
                . " AND so.othersub_id = s.subject_id ORDER BY s.subject";
                
        $rs = xx_tryquery($sql);
        while ($row = xx_fetch_array ($rs, xx_ASSOC)) {
                $othersub_id = $row["othersub_id"];
                $subject = $row["subject"];     

                printf("<a href=\"rqs.phtml?subject_id=%s\">%s</a><BR>\n", $othersub_id, $subject);
        };


}


/**********************************************************
Function: displayTOC
Author: Paul Bramscher
Last Modified: 03.04.2004
***********************************************************
Purpose:
Generates and displays a table of contents (TOC) for the
supplied page id.  This is done dynamically, and uses
root-level elements on the given page.

This function allows for the optional display of a
two-column table of contents, split at the midpoint
(rounded up for odd numbers of root elements).
**********************************************************/
function displayTOC($page_id, $wrap_toc) {

        // Calculate number of elements
        $sql = "SELECT COUNT(*) as num_elements FROM element WHERE indent_level < 1 AND page_id = "
                . $page_id;
        $rs = xx_tryquery($sql);
        $row = xx_fetch_array ($rs);
        $num_elements = $row["num_elements"];

        // Calculate midpoint element   
        if ($num_elements > 0) $midpoint = ceil($num_elements / 2);
        else $midpoint = 0;

        if ($num_elements > 0) {

                // Retrieve all elements on that page
                $sql = "SELECT
                        e.element_id,
                        e.label,                
                        r.resource_id,
                        r.title,
                        l.location_id,
                        l.location,
                        v.service_id,
                        v.service,
                        s.staff_id,
                        s.last_name,
                        s.first_name,
                        b.subject_id,
                        b.subject

                        FROM
                        page p
                        LEFT JOIN element e using (page_id)
                        LEFT JOIN resource r on e.resource_id = r.resource_id           
                        LEFT JOIN location l on e.location_id = l.location_id
                        LEFT JOIN service v on e.service_id = v.service_id
                        LEFT JOIN staff s on e.staff_id = s.staff_id
                        LEFT JOIN subject b on e.subject_id = b.subject_id

                        WHERE
                        p.page_id = "
                        . $page_id
                        . " AND e.indent_level < 1 ORDER BY e.element_order";

                $rs = xx_tryquery($sql);

                // Build a general ToC anchor
                printf("<a name=\"toc\"></a>\n");
                printf("<b>Table of Contents:</b><br>\n");
                printf("<table>\n");

                // Build the (single) row
                printf("<tr>\n");

                // Populate the left cell
                printf("<td valign=\"top\"><ul>\n");
                
                $row_num = 0;
                
                while ($row = xx_fetch_array ($rs, xx_ASSOC)) {
                
                        // Check for the midpoint
                        if ($row_num  == $midpoint && $wrap_toc == 1) {
                        
                                // Close the ul tag & table cell
                                printf("</ul>\n");
                                printf("</td>\n");
                                
                                // Start a new one
                                printf("<td valign=\"top\">\n");
                                printf("<ul>\n");
                        
                        }
                
                        // General
                        $element_id = $row["element_id"];

                        // Resource
                        $resource_id = $row["resource_id"];                     
                        $title = $row["title"];

                        // Label/unique
                        $label  = $row["label"];

                        // Location
                        $location_id = $row["location_id"];                     
                        $location = $row["location"];

                        // Service
                        $service_id = $row["service_id"];                       
                        $service = $row["service"];

                        // Staff
                        $staff_id = $row["staff_id"];                   
                        $last_name = $row["last_name"];
                        $first_name = $row["first_name"];

                        // Subject
                        $subject_id = $row["subject_id"];                       
                        $subject = $row["subject"];

                        // Determine what sort of element it is
                        $toc_label = "";
                        if ($resource_id > 0) $toc_label = $title;
                        else if (strlen($label) > 0) $toc_label = $label;
                        else if ($location_id > 0) $toc_label = $location;
                        else if ($service_id > 0) $toc_label = $service;
                        else if ($staff_id > 0) $toc_label = $first_name . " " . $last_name;
                        else if ($subject_id > 0) $toc_label = $subject;                        

                        // Last-minute error check
                        if (strlen($toc_label) > 0) printf("<li><a href=\"#toc%d\">%s</a></li>\n", $element_id, $toc_label);    

                        $row_num++;

                } // end elements
                
                // Close things
                printf("</ul>\n");
                printf("</td></tr></table><br>\n");
                
                
        } // at least one item on this page
}


/**********************************************************
Function: dropDownCoursesubSelected($limit, $preselected)
Author: Paul Bramscher
Last Modified: 03.04.2004
***********************************************************
Incoming:
$limit                  Initial limit to the result set
$preselected            Incoming course subject to preselect
***********************************************************
Outgoing:
None
***********************************************************
Purpose:
Populates a drop-down box on an HTML form with select
statements.  Note that only course subjects (designators)
which have associated and published CourseLib pages will
appear in the drop down box.
**********************************************************/
function dropDownCoursesubSelected($limit, $preselected){


        $sql = "SELECT DISTINCT s.coursesub
                FROM coursesub s, course c, page p "
                . $limit
                . "
                AND s.coursesub_id = c.coursesub_id
                AND c.page_id = p.page_id
                AND p.published = '1'
                AND s.coursesub_id = c.coursesub_id "
                . " ORDER BY s.coursesub";

        $rs = xx_tryquery($sql);
        while ($row = xx_fetch_array ($rs, xx_ASSOC)) {
                $coursesub = $row["coursesub"];
                
                printf("<option value = \"%s\"", $coursesub);
                if ($coursesub == $preselected) printf(" selected ");
                printf(">" . $coursesub . "</option>\n");
        };
}


/**********************************************************
Function: dropDownFacultySelected($limit, $preselected)
Author: Paul Bramscher
Last Modified: 03.04.2004
***********************************************************
Incoming:
$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.  The faculty are displayed only if they
(a) are currently teaching 1+ courselib courses and
(b) that course(s) is published.
**********************************************************/
function dropDownFacultySelected($limit, $preselected){

        $sql = "SELECT DISTINCT
                f.faculty_id,
                f.faculty_lastname,
                f.faculty_firstname
                FROM faculty f, course c, course_personnel cp, page p "
                . $limit
                . "
                AND f.faculty_id = cp.faculty_id
                AND cp.course_id = c.course_id
                AND c.page_id = p.page_id
                AND p.published = '1'"
                . " ORDER BY f.faculty_lastname, f.faculty_firstname";
        
        $rs = xx_tryquery($sql);
        while ($row = xx_fetch_array ($rs, xx_ASSOC)) {
                $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: dropDownFieldOmit($table, $field_display,
$field_value, $where)
Author: Paul Bramscher
Last Modified: 03.04.2004
***********************************************************
Incoming:
$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($table, $field_display,
        $field_value, $where){

        $sql = "SELECT "
                . $field_display
                . ", "
                . $field_value
                . " FROM "
                . $table
                . " "
                . $where
                . " ORDER BY "
                . $field_display;
        $rs = xx_tryquery($sql);
        while ($row = xx_fetch_array ($rs, xx_ASSOC)) {
                $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($table, $field_display,
$field_value, $limit, $preselected)
Author: Paul Bramscher
Last Modified: 03.04.2004
***********************************************************
Incoming:
$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($table, $field_display,
        $field_value, $limit, $preselected){

        $sql = "SELECT "
                . $field_display
                . ", "
                . $field_value
                . " FROM "
                . $table
                . " "
                . $limit
                . " ORDER BY "
                . $field_display;
        
        $rs = xx_tryquery($sql);
        while ($row = xx_fetch_array ($rs, xx_ASSOC)) {
                $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: existsRow
Author: Paul Bramscher
Last Modified: 03.04.2004
***********************************************************
Incoming:
$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($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 = xx_tryquery($sql);
        $rowcount = xx_num_rows($rs);
        if ($rowcount >= 1){
                $rowcount = 1;
        }
        else $rowcount = 0;

        return $rowcount;
}


/**********************************************************
Function: lookupField($table, $key_field, $key_value,
$desc_field)
Author: Paul Bramscher
Last Modified: 03.04.2004
***********************************************************
Incoming:
$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($table, $key_field, $key_value,
        $desc_field){
        
        $sql = "SELECT "
                . $desc_field
                . " FROM "
                . $table
                . " WHERE "
                . $key_field
                . "='"
                . $key_value
                . "'";
        $rs = xx_tryquery($sql);
        $row = xx_fetch_array ($rs, xx_ASSOC);
        if (xx_num_rows($rs) == 1) $result = $row[$desc_field];
        else $result = "";
        
        return $result;
}


/**********************************************************
Function: lookupStaff($staff_id)
Author: Paul Bramscher
Last Modified: 03.04.2004
***********************************************************
Incoming:
$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($staff_id){

        $sql = "SELECT first_name, last_name FROM staff WHERE staff_id = "
                . $staff_id;
        $rs = xx_tryquery($sql);
        $row = xx_fetch_array ($rs, xx_ASSOC);
        $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: pageLoadStats
Author: Paul Bramscher
Last Modified: 03.04.2004
***********************************************************
Purpose:
Logs a statistical entry for a PageScribe/CourseLib page
load.  Page id, date, and IP address are currently logged.
It would be relatively trivial to add other fields,
such as browser type, refer URL, etc. both to the database
and this function here.  However, care must be taken so
that the database doesn't grow too large.  Currently there
exists no automatic mechanism to purge or archive stats.
**********************************************************/
function pageLoadStats($page_id){

        // Add a row for page usage
        
        // Fetch user ip
        $user_ip = $GLOBALS["REMOTE_ADDR"];
        
        // Do the stat stuff here
        $sql = "INSERT INTO libstats.pagestats (
                        page_id,
                        visit_date,
                        user_ip) VALUES ("
                        . $page_id
                        . ", now(), '"
                        . $user_ip
                        . "')";
        xx_tryquery($sql);

}

/**********************************************************
Function: printAlphabet
Author: Paul Bramscher
Last Modified: 07.07.2003
***********************************************************
Purpose:
Prints upper-case alphabet characters separated by the
specified separator character, wrapped with the specified
URL.
**********************************************************/
function printAlphabet($separator_char, $url) {

        printf("<table class=\"strong\" border=\"0\" cellpadding=\"0\" cellspacing=\"0\">\n");
        printf("<tr><td>\n");
        
        for ($ascii_char = 65; $ascii_char <= 90; $ascii_char++) {
                printf("<a href= \"%s?letter=%s\">%s</a>", $url,
                        chr($ascii_char), chr($ascii_char));
                if ($ascii_char != 90) printf ("%s", $separator_char);
        }

        printf("</td></tr>\n");
        printf("</table>\n");


}

/**********************************************************
Function: subLoadStats
Author: Paul Bramscher
Last Modified: 03.04.2004
***********************************************************
Purpose:
Logs a statistical entry for a RQS page load.  Subject id,
date, and IP address are currently logged.  Note that
both default (screen) and printer-friendly loads for the
same subject are logged with this function.
No differentiation between them is made, only between
subjects
**********************************************************/
function subLoadStats($subject_id){

        // Add a row for page usage
        
        // Fetch user ip
        $user_ip = $GLOBALS["REMOTE_ADDR"];
        
        // Do the stat stuff here
        $sql = "INSERT INTO libstats.substats (
                        subject_id,
                        visit_date,
                        user_ip) VALUES ("
                        . $subject_id
                        . ", now(), '"
                        . $user_ip
                        . "')";
        xx_tryquery($sql);

}

/**********************************************************
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
        $outgoing = ereg_replace("'","\'",$incoming);

        return $outgoing;

}
?>