function getShifts($start, $end, $type, $date)
{
global $area, $db, $showUnposted;
$prevDay = date("Y-m-d", strtotime($date . " -1 day"));
$nextDay = date("Y-m-d", strtotime($date . " +1 day"));
// This is the query I wrote to simplify the logic of the query and help speed things up ~Mika
if (!isSchedulePosted($date) && !$showUnposted) {
return array();
} else {
try {
$scheduleQuery = $db->prepare("SELECT * FROM `scheduleWeekly` WHERE `area` = :area AND `deleted` = 0 AND `hourType` = :type AND `startDate` = :day AND CONCAT(`startDate`, ' ', `startTime`) <= :start AND CONCAT(`endDate`, ' ', `endTime`) > :end");
$scheduleQuery->execute(array(':area' => $area, ':type' => $type, ':day' => $date, ':start' => $date . ' ' . $start . ':00', ':end' => $date . ' ' . $start . ':00'));
} catch (PDOException $e) {
exit("error in query");
}
$shifts = array();
while ($cur = $scheduleQuery->fetch(PDO::FETCH_ASSOC)) {
$shifts[] = $cur;
}
return $shifts;
}
}
//Default Mode query
$timeQueryParams[':start'] = $periodDate->format('Y-m-d');
$timeQueryParams[':end'] = $periodDate->format('Y-m-d');
$timeQueryParams[':area'] = $area;
$timeQueryString = "SELECT scheduleDefault.employee, scheduleHourTypes.name, scheduleDefault.startDate, scheduleDefault.endDate, scheduleDefault.startTime, scheduleDefault.endTime, scheduleDefault.hourTotal, scheduleSemesters.name AS period, scheduleSemesters.ID AS periodID FROM `scheduleDefault` LEFT JOIN `scheduleHourTypes` ON scheduleHourTypes.ID = scheduleDefault.hourType RIGHT JOIN `scheduleSemesters` ON scheduleDefault.period = scheduleSemesters.ID WHERE ( `employee` IN (" . $employeeList . ") OR `employee` IS NULL) AND (`hourType` IN (" . $hourTypeList . ") OR `hourType` IS NULL) AND (scheduleSemesters.startDate <= :start AND scheduleSemesters.endDate >= :end) AND scheduleSemesters.area = :area AND (scheduleDefault.deleted != '1' OR scheduleDefault.deleted IS NULL) ORDER BY scheduleHourTypes.name ASC";
}
try {
$timeQuery = $db->prepare($timeQueryString);
$timeQuery->execute($timeQueryParams);
} catch (PDOException $e) {
exit("2error in query");
}
$row = $timeQuery->fetch();
//test for results
//if no results are obtained after running either query above (ie. no employees scheduled during period), run the query bellow to retrieve the period for the date selected
if ($row == false || !isSchedulePosted($date->format('Y-m-d')) && !can("update", "1689443f-8c4c-4874-8ee3-a3137db32d85")) {
try {
$timeQuery = $db->prepare("SELECT name AS period, ID AS periodID FROM scheduleSemesters WHERE scheduleSemesters.startDate <= :start AND scheduleSemesters.endDate >= :end AND `area`= :area");
$timeQuery->execute(array(':start' => $periodDate->format('Y-m-d'), ':end' => $periodDate->format('Y-m-d'), ':area' => $area));
} catch (PDOException $e) {
exit("3error in query");
}
} else {
try {
//reset the query after testing for results
$timeQuery->execute($timeQueryParams);
} catch (PDOException $e) {
exit("4error in query");
}
}
while ($row = $timeQuery->fetch(PDO::FETCH_ASSOC)) {
function getWhosHereOnHour($hour, $date)
{
global $area, $db;
$dayOfWeek = strtolower(date("D", strtotime($date)));
$semester = getSemester($date);
$hour = date('H:i:s', $hour);
if (isSchedulePosted($date) || can("update", "1689443f-8c4c-4874-8ee3-a3137db32d85")) {
try {
$employeeQuery = $db->prepare("SELECT employee, hourType FROM `scheduleWeekly` WHERE `area`=:area AND `deleted`=0 AND :start >= CONCAT(`startDate`,' ',`startTime`) AND :end < CONCAT(`endDate`,' ',`endTime`);");
$employeeQuery->execute(array(':area' => $area, ':start' => $date . " " . $hour, ':end' => $date . " " . $hour));
} catch (PDOException $e) {
exit("error in query");
}
$return = array();
while ($curEmployee = $employeeQuery->fetch(PDO::FETCH_ASSOC)) {
$return[] = $curEmployee;
}
return $return;
} else {
//If the schedule today is not posted, return an empty array
$return = array();
return $return;
}
}
