Location: PHPKode > projects > ConPortal > conportal/inc/shiftgroups/db.php
<?php
/*
 *  ConPortal - Pomona College ITS scheduling appplication
 *  Copyright (C) 2005-2008  Pomona College & Bucknell University
 *
 *  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
 */

function createShiftgroup ($name, $startDate, $endDate)
{
	// Check that this doesn't overlap any existing shiftgroups
	$result = safeQuery("select name from shiftgroups where " .
		"start_date <= FROM_UNIXTIME(%d) and " .
		"end_date >= FROM_UNIXTIME(%d)",
		$endDate, $startDate);
	if ($row = mysql_fetch_assoc($result))
	{
		error("Can't create shiftgroup, overlaps with {$row['name']}");
		return NULL;
	}
	
	// Create it
	safeQuery("insert into shiftgroups set name = '%s', " .
		"start_date = FROM_UNIXTIME(%d), end_date = FROM_UNIXTIME(%d)",
		mysql_real_escape_string($name),
		$startDate, $endDate);
	return mysql_insert_id();
}

/*
 * Return an associative array of the shiftgroup specified by its PID
   Edited 1/8/2009 by George Casper to add first week protection support
 */
function getShiftgroupDetails ($pid)
{
	$result = safeQuery("select pid, name, first_week_protection, " .
		"UNIX_TIMESTAMP(start_date) as start_date, " .
		"UNIX_TIMESTAMP(end_date) as end_date " .
		"from shiftgroups where pid = %d", $pid);
    if (mysql_num_rows($result) > 0)
        return mysql_fetch_assoc($result);
    else
        return NULL;
}

/*
 * Array of PIDs of shiftgroups
 */
function getShiftgroups ()
{
	$result = safeQuery("select pid from shiftgroups ORDER BY pid DESC");
    if (mysql_num_rows($result) > 0)
	{
        $array = array();
        while ($row = mysql_fetch_assoc($result))
            $array[] = $row['pid'];
        return $array;
    }
    return NULL;
}

/* 
 * Array of PIDs and names of shiftgroups; extention of function above.  
 */
function getShiftgroupsPidsAndNames ()
{
	$result = safeQuery("select pid, name from shiftgroups ORDER BY pid DESC");
    if (mysql_num_rows($result) > 0)
	{
        $array = array();
        while ($row = mysql_fetch_assoc($result))
            $array[] = $row;
        return $array;
    }
    return NULL;
}


/*
 * Return the pid of the shiftgroup containing $timestamp
 */
function getShiftgroupByDate ($timestamp)
{
	$result = safeQuery("select pid from shiftgroups where " .
		"start_date <= FROM_UNIXTIME(%d) and end_date >= FROM_UNIXTIME(%d)",
		$timestamp, $timestamp);
    if (mysql_num_rows($result) == 1)
    {
		$row = mysql_fetch_assoc($result);
        return $row['pid'];
    }
    else if (mysql_num_rows($result) == 0)
    {
        return NULL;
    }
	else
	{
		error("Multiple shiftgroups returned for " . date("M j Y", $timestamp));
		$row = mysql_fetch_assoc($result);
        return $row['pid'];
	}
}

/*
 * this function iterates through the shift groups
 * and sets the $_SESSION['shift_shiftgroup'] and
 * the first one that contains the current day.
 * Assume that no shift groups that are
 * overlapping for general use.
 */
function setShiftgroupByDate($timestamp)
{
    $_SESSION['shift_shiftgroup'] = getShiftgroupByDate($timestamp);

	// FIXME: this should go away after all date/time handling code has been cleaned up
    // Set shift_timestamp to 12am on the date requested.  This is necessary
    // because code elsewhere assumes that shift_timestamp is 12am, in order
    // to be able to compare equal with dates retrieved from SQL, which
    // have a time component of 12am when they are converted to timestamps.
    $_SESSION['shift_timestamp'] = strtotime("12AM", $timestamp);
}

/* This is a dump of Shiftgroup data, merged with data it pulls from other tables.
	Informational to help Meredith (and others!) understand what data a Shiftgroup
	determines.  Also, not bad to check and make sure things are being set properly.  */
function getShiftgroupExtendedInfo()
{
	$result = safeQuery("SELECT shiftgroups.name as sgname, shiftgroups.start_date as sgstart, shiftgroups.end_date as sgend, shiftgroups.send_email as email, (seconds/3600) as hours, groups.description as groupn FROM shiftgroups LEFT JOIN shift_draw ON shiftgroups.pid = shift_draw.shift_group  LEFT JOIN groups ON groups.pid = shift_draw.group");
	if (mysql_num_rows($result) > 0)
	{
        $array = array();
        while ($row = mysql_fetch_assoc($result))
	{
		//make the 1/0 values more human readable!
		if($row['email'])
		{
			$row['email'] = "Yes";
		}
		else
		{
			$row['email'] = "No";
		}
            $array[] = $row;
	}
        return $array;
    }
	//else, no result - *should* never happen :)
    return NULL;
}



//added 4/9/08 to see email should be sent or not for a given shiftgroup
function checkIfShouldSendEmail($shiftgroupPid)
{
	$result = safeQuery("SELECT send_email FROM shiftgroups WHERE pid = " . $shiftgroupPid ."");
	$row = mysql_fetch_assoc($result);
	return $row['send_email'];
}
 
function getShiftgroupsForEmailOrFirstWeekForm()
{
	$result = safeQuery("SELECT pid, name, send_email, first_week_protection FROM shiftgroups");
	if (mysql_num_rows($result) > 0)
	{
        $array = array();
        while ($row = mysql_fetch_assoc($result))
	{
            $array[] = $row;
	}
        return $array;
    }
	//else, no result - *should* never happen :)
    return NULL;
}

function setShiftgroupsForEmailForm($inputArray)
{
	foreach($inputArray as $row)
	{
		$result = safeQuery("UPDATE shiftgroups SET send_email = " . $row['send_email'] . 
		" WHERE pid = " . $row['pid']);
	}
	return;
}

// 1/8/2009 mostly a mirror of the above
function setShiftgroupsForFirstWeekProtectionForm($inputArray)
{
	foreach($inputArray as $row)
	{
		$result = safeQuery("UPDATE shiftgroups SET first_week_protection = " . $row['first_week_protection'] . 
		" WHERE pid = " . $row['pid']);
	}
	return;
}

?>
Return current item: ConPortal