Location: PHPKode > projects > ConPortal > conportal/inc/appointments/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:
 *    checkAppointmentShift
 *    checkAppointmentTime
 *    checkNumAppointments
 *    createAppointment
 *    getAppointmentDetails
 *    getAppointmentsForDate
 *    getAppointmentsForRange
 *    getUncancelledAppointmentsForRangeAndPosition
 *    getUncancelledAppointmentsForShift
 *    isAppointmentValid
 *    updateAppointmentDateTimePosition
 *    updateAppointmentStatus
 */

/*************************************************************
 * Checks to make see if there is another appointment        *
 * during this shift. Returns true if there is at least one. *
 *************************************************************/
function checkAppointmentShift ($shiftPid, $timestamp)
{
    $appts = getUncancelledAppointmentsForShift($shiftPid, $timestamp);
    return (is_array($appts) && count($appts) > 0);
}

/*
 * Check that appointment start time is at least 15 minutes
 * after shift start and 30 minutes before shift end.
 */
function checkAppointmentTime ($startDateTime, $shiftPid)
{
	$shift = getShiftDetails($shiftPid);
	// normalize all times to within the same day (range of 0 to num_secs_in_a_day)
	$startTime = time_part($shift['start_time']);
	$endTime   = time_part($shift['end_time']);
	$startDateTime = time_part($startDateTime);
	return ($startDateTime >= $startTime + 15*60 and $startDateTime <= $endTime - 30*60);
}

/*
 *  Check if number of appointments at this time is less than
 *  number of shifts (i.e. there should always be at least one
 *  free shift).
 */
function checkNumAppointments ($startDateTime)
{
	// Get list of shifts at this time
	$shifts = getShiftsForTime($startDateTime);

	$numShifts = count($shifts);
	// Count appointments in each shift
	// Start at 1 for the appointment we are currently creating
	$numAppts = 1;
	foreach ($shifts as $pid)
	{
		if (checkAppointmentShift($pid, $startDateTime))
			++$numAppts;
	}
	return ($numAppts < $numShifts);
}

/**********************************************************
 * creates a new appointment given all the necessary data *
 **********************************************************/
function createAppointment ($start_time, $position, $ticket, $desc, $authorPid)
{
	safeQuery("insert into appointments set start_time=FROM_UNIXTIME(%d), " .
		"position='%s', ticket_number=%d, " .
		"description='%s', author=%d, " .
		"author_datetime=NOW(), status='Scheduled'",
		$start_time,
		$position,
		$ticket,
		mysql_real_escape_string($desc),
		$authorPid);
			
	emailAssignee(mysql_insert_id());
}

/************************************************************
 * returns associative array of appointment data, given pid *
 ************************************************************/
function getAppointmentDetails ($pid)
{
	$result = safeQuery("select pid, description, ticket_number, author, status, position, " .
		"UNIX_TIMESTAMP(start_time) as start_time, " .
		"UNIX_TIMESTAMP(author_datetime) as author_datetime " .
		"from appointments where pid = %d", $pid);
	return mysql_fetch_assoc($result);
}

/************************************************
 * returns array of pids of all appointments on *
 * a given date                                 *
 ************************************************/
function getAppointmentsForDate ($timestamp)
{
	$start = date_part($timestamp);
	$end = $start + 86400; // 60 sec/min * 60 min/hr * 24 hr/day = 86400 sec/day
	return getAppointmentsForRange($start, $end);
}

/******************************************************
 * Returns an array of all appointments in a given    *
 * range inclusive. The appointments are given by pid *
 ******************************************************/
function getAppointmentsForRange ($startTime, $endTime)
{
	$result = safeQuery("select pid from appointments where " . 
		"start_time >= FROM_UNIXTIME(%d) and " .
		"start_time < FROM_UNIXTIME(%d)",
		$startTime, $endTime);
	$a = array();
	while ($row = mysql_fetch_assoc($result))
		$a[] = $row['pid'];
	return $a;
}

/****************************************************************
 * Returns an array of pids of appointments within a time range *
 * and assigned to a given position.                            *
 ****************************************************************/
function getUncancelledAppointmentsForRangeAndPosition ($startTime, $endTime, $position)
{
	$result = safeQuery("select pid from appointments where " .
		"start_time >= FROM_UNIXTIME(%d) and " .
		"start_time < FROM_UNIXTIME(%d) and " .
		"position = %d and status <> 'Cancelled'",
		$startTime, $endTime, $position);
	$a = array();
	while ($row = mysql_fetch_assoc($result))
		$a[] = $row['pid'];
	return $a;
}

/*************************************************************
 * Returns an array of pids for the appointments that happen *
 * during a given shift on a given day.                      *
 *************************************************************/
function getUncancelledAppointmentsForShift ($shiftPid, $shiftTimestamp)
{
	$shiftDetails = getShiftDetails($shiftPid);
	// First lets make sure the given shift does occur on the given date
	if (strcasecmp(date("l", $shiftTimestamp), 
		$shiftDetails['day_of_week']) != 0)
	{
		return NULL;
	}
	// Now lets get the shift position, start time, and end time
	// The times are stored as offsets from the start of the day
	$position = $shiftDetails['position'];
	$startTime = time_part($shiftDetails['start_time']);
	$endTime = time_part($shiftDetails['end_time']);
	// Chop the time portion off of datetime
	$date = date_part($shiftTimestamp);
	// Shift the times to the proper date
	$startTime += $date;
	$endTime += $date;
	return getUncancelledAppointmentsForRangeAndPosition($startTime, $endTime,
	                                                     $position);
}

/****************************************************
 * Checks to see if an appointment can be scheduled *
 * at the specified time and position. Returns true *
 * or false.                                        *
 ****************************************************/
function isAppointmentValid ($startDateTime, $position)
{
    $shift = getShiftForTimeAndPosition($startDateTime, $position);
	if (!$shift)
	{
		error("Could not find shift at time and position of appointment.");
		return false;
	}
	if (checkAppointmentShift($shift, $startDateTime))
	{
		error("There is already another appointment during that shift.");
		return false;
	}
	if (!checkShiftCovered($shift))
	{
		error("Can't create an appointment during a shift that's not covered.");
		return false;
	}
	if (!checkNumAppointments($startDateTime))
	{
		error("Too many appointments.  There must be at least one person covering the desk.");
		return false;
	}
	if (!checkAppointmentTime($startDateTime, $shift))
	{
		error("Bad appointment time.  Appointment must start at least 15 minutes after " .
			"shift begins and at least 30 minutes before it ends.");
		return false;
	}
	if ($startDateTime <= mktime())
	{
		error("Can't create an appointment in the past!");
		return false;
	}
    return true;
}

/************************************************
 * update appointment date/time/position fields *
 ************************************************/
function updateAppointmentDateTimePosition ($apptPid, $newDateTime, $position)
{
	safeQuery("update appointments set position = %d, " .
		"start_time = FROM_UNIXTIME(%d) " .
		"where pid = %d",
		$position,
		$newDateTime,
		$apptPid);
}


/***********************************
 * update appointment status field *
 ***********************************/
function updateAppointmentStatus ($apptPid, $newStatus)
{
	safeQuery("update appointments set status = '%s' " .
		"where pid = %d",
		mysql_real_escape_string($newStatus),
		$apptPid);
	emailAssignee($apptPid);
}

?>
Return current item: ConPortal