Location: PHPKode > projects > ConPortal > conportal/inc/meetings/db.php
<?php
/*
 *  ConPortal - Pomona College ITS scheduling appplication
 *  Copyright (C) 2005-2006  Pomona College
 *
 *  This program is free software; you can redistribute it and/or modify
 *  it under the terms of version 2 of the GNU General Public License
 *  as published by the Free Software Foundation.
 *
 *  This program is distributed in the hope that it will be useful,
 *  but WITHOUT ANY WARRANTY; without even the implied warranty of
 *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 *  GNU General Public License for more details.
 *
 *  You should have received a copy of the GNU General Public License
 *  along with this program; if not, write to the Free Software
 *  Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301  USA
 */

/* Functions in this file:
 *    createUpdateMeeting
 *    getAttendanceForMeeting
 *    getGroupsForMeetingType
 *    getMeetingDetails
 *    getNameForMeetingType
 *    getNamesForAllMeetingTypes
 *    getPastMeetingDetails
 *    getUpcomingMeetingDetails
 *    updateMeetingAttendance
 */

/*
 * Create a meeting or update an existing one; if $pid is NULL, creates a new one,
 * otherwise, updates that meeting
 */
function createUpdateMeeting ($pid, $type, $date, $location, $status, $notes)
{
	// Do some basic parameter validation
	$result = safeQuery("select pid from meeting_types where pid = %d", $type);
	if (mysql_num_rows($result) == 0)
	{
		error("$type is not a valid meeting type.");
		return NULL;
	}
	if (!$pid and $date < date_part(time()))
	{
		error("Cannot create a meeting in the past: " . date('n/j/Y', $date));
		return NULL;
	}
	if ($status != "Scheduled/Mandatory" and
		$status != "Scheduled/Voluntary" and
		$status != "Completed" and
		$status != "Cancelled")
	{
		error("$status is not a valid status for a meeting.");
		return NULL;
	}
	
	// Assemble the query
	if ($pid)
		$query = "update meetings ";
	else
		$query = "insert into meetings ";
	$query .= sprintf("set " .
		"type = %d, " .
		"date = FROM_UNIXTIME(%d), " .
		"location = '%s', " .
		"status = '%s', " .
		"notes = '%s'",
		$type,
		$date,
		mysql_real_escape_string($location),
		mysql_real_escape_string($status),
		mysql_real_escape_string($notes));
	if ($pid)
		$query .= sprintf(" where pid = %d", $pid);
	
	safeQuery($query);
	if ($pid)
		return $pid;
	return mysql_insert_id();
}

/*
 * Returns an array of user_pid => status for the given meeting
 * Note: this doesn't necessarily have all users in it
 */
function getAttendanceForMeeting ($meetingPid)
{
	$result = safeQuery("select user_pid, status from meeting_attendance " .
		"where meeting_pid = %d", $meetingPid);
	$a = array();
	while ($row = mysql_fetch_assoc($result))
		$a[$row['user_pid']] = $row['status'];
	return $a;
}

function getGroupsForMeetingType ($type)
{
	$result = safeQuery("select group_pid from meeting_groups where meeting_type_pid = %d", $type);
	$a = array();
	while ($row = mysql_fetch_assoc($result))
		$a[] = $row['group_pid'];
	return $a;
}

function getMeetingDetails ($meetingPid)
{
	$result = safeQuery("select pid, location, type, notes, status, " .
		"UNIX_TIMESTAMP(date) as date from meetings where pid = %d", $meetingPid);
	return mysql_fetch_assoc($result);
}

function getNameForMeetingType ($type)
{
	$result = safeQuery("select name from meeting_types where pid = %d", $type);
	$row = mysql_fetch_assoc($result);
	return $row ? $row['name'] : "unknown type of meeting";
}

/*
 * Returns an associative array matching type IDs to their names
 */
function getNamesForAllMeetingTypes ()
{
	$result = safeQuery("select pid, name from meeting_types");
	$a = array();
	while ($row = mysql_fetch_assoc($result))
		$a[$row['pid']] = $row['name'];
	return $a;
}

function getPastMeetingDetails ()
{
	$result = safeQuery("select pid, location, type, notes, status, " .
		"UNIX_TIMESTAMP(date) as date from meetings where " .
		"date < NOW() and status != 'Cancelled' order by date desc");
	$user_groups = getGroupsForUser($_SESSION['pid']);
	$user_groups[] = getPrimaryGroupForUser($_SESSION['pid']);
	$a = array();
	while ($meeting = mysql_fetch_assoc($result))
	{
		// Get the groups that can see this meeting type
		$type_groups = getGroupsForMeetingType($meeting['type']);
		// See if any of the groups that the user belongs to match
		if (count(array_intersect($user_groups, $type_groups)) > 0)
			$a[] = $meeting;
	}
	return $a;
}

function getUpcomingMeetingDetails ()
{
	$result = safeQuery("select pid, location, type, notes, status, " .
		"UNIX_TIMESTAMP(date) as date from meetings where " .
		"date >= NOW() and status != 'Cancelled' order by date");
	$user_groups = getGroupsForUser($_SESSION['pid']);
	$user_groups[] = getPrimaryGroupForUser($_SESSION['pid']);
	$a = array();
	while ($meeting = mysql_fetch_assoc($result))
	{
		// Get the groups that can see this meeting type
		$type_groups = getGroupsForMeetingType($meeting['type']);
		// See if any of the groups that the user belongs to match
		if (count(array_intersect($user_groups, $type_groups)) > 0)
			$a[] = $meeting;
	}
	return $a;
}

/*
 * Takes: meeting pid, and array of user_pid => status
 */
function updateMeetingAttendance ($meetingPid, $attendance)
{
	// Do basic sanity checking on the input
	if (!getMeetingDetails($meetingPid))
	{
		error("$meetingPid is not a valid meeting");
		return false;
	}
	foreach ($attendance as $userPid => $status)
	{
		$name = getNameForUser($userPid);
		if ($name == "Unknown User")
		{
			error("Unknown user: $userPid");
			return false;
		}
		if ($status != "Present" and
			$status != "Absent/Excused" and
			$status != "Absent/Unexcused" and
			$status != "Tardy" and
			$status != "Left Early")
		{
			error("$status is a bad status");
			return false;
		}
	}

	start_transaction();
	
	// Delete previous attendance records from this meeting
	safeQuery("delete from meeting_attendance where meeting_pid = %d", $meetingPid);
	// Replace with new ones...
	$query = "insert into meeting_attendance values";
	foreach ($attendance as $userPid => $status)
		$query .= sprintf("(%d, %d, '%s'),",
			$meetingPid, $userPid, mysql_real_escape_string($status));
	// Remove trailing comma
	$query[strlen($query) - 1] = ' ';
	safeQuery($query);
			
	commit_transaction();
	return true;
}

?>
Return current item: ConPortal