Sorry, your selected date $startmonth/$startday/$startyear or $endmonth/$endday/$endyear is invalid. Please go back and change your date selection."; exit(); } include("../include/dbconfig.php"); $connection = mysql_connect($db_host, $db_user, $db_pass) or die ("Unable to connect!"); mysql_select_db($db_name); $today = date("F j, Y, g:i a"); // March 10, 2001, 5:16 pm $query = "SELECT StartDate, EndDate, Memo from SR_semester WHERE Year = '$syear' AND Semester = '$semester'"; $result3 = mysql_query($query, $connection) or die ("Error in query: $query. " . mysql_error()); $row = mysql_fetch_row($result3); $check_startdate = $row[0]; $check_enddate = $row[1]; if ($startdate < $check_startdate){ print "Sorry, your selected date $startdate is before the $check_startdate. Please go back and change your date selection."; exit(); } else if ($enddate > $check_enddate){ print "Sorry, your selected date $enddate is after the $check_enddate. Please go back and change your date selection."; exit(); } print << Report - From $startmonth/$startday/$startyear To $endmonth/$endday/$endyear END; ?>
">Calendar View | Manage | Sign Out

LLC Reservation Report

From $startmonth/$startday/$startyear to $endmonth/$endday/$endyear END; $query = "SELECT RoomNo, Title from SR_room"; $result2 = mysql_query($query, $connection) or die ("Error in query: $query. " . mysql_error()); while ($row2 = mysql_fetch_row($result2)) { print <<$row2[1] - $row2[0] END; $query = "SELECT count(*), sum(TIME_TO_SEC(EndTime)-TIME_TO_SEC(StartTime)) from SR_reservation WHERE Date <= '$enddate' and Date >= '$startdate' and RoomNo='$row2[0]' AND (Status = '1' or Status = '2')"; $result = mysql_query($query, $connection) or die ("Error in query: $query. " . mysql_error()); $row = mysql_fetch_row($result); $TotalTimes = $row[0]; $TotalSec = $row[1]; $TotalHour = $row[1]/3600; $query = "SELECT count(*), sum(TIME_TO_SEC(EndTime)-TIME_TO_SEC(StartTime)) from SR_reservation WHERE Date <= '$enddate' and Date >= '$startdate' and RoomNo='$row2[0]' AND Status = '3'"; $result = mysql_query($query, $connection) or die ("Error in query: $query. " . mysql_error()); $row = mysql_fetch_row($result); $CTimes = $row[0]; $CHour = $row[1]/3600; $query = "SELECT count(*), sum(TIME_TO_SEC(EndTime)-TIME_TO_SEC(StartTime)) from SR_reservation WHERE Date <= '$enddate' and Date >= '$startdate' and RoomNo='$row2[0]' AND Status = '4'"; $result = mysql_query($query, $connection) or die ("Error in query: $query. " . mysql_error()); $row = mysql_fetch_row($result); $ATimes = $row[0]; $AHour = $row[1]/3600; $query = "SELECT count(*), sum(TIME_TO_SEC(EndTime)-TIME_TO_SEC(StartTime)) from SR_reservation WHERE Date <= '$enddate' and Date >= '$startdate' and RoomNo='$row2[0]' AND (Status = '1' or Status = '2') and StartTime<'16:00:00'"; $result = mysql_query($query, $connection) or die ("Error in query: $query. " . mysql_error()); $row = mysql_fetch_row($result); $TotalTimes1 = $row[0]; $TotalHour1 = $row[1]/3600; $query = "SELECT count(*), sum(TIME_TO_SEC(EndTime)-TIME_TO_SEC(StartTime)) from SR_reservation WHERE Date <= '$enddate' and Date >= '$startdate' and RoomNo='$row2[0]' AND (Status = '1' or Status = '2') and StartTime<'19:00:00' and StartTime>='16:00:00'"; $result = mysql_query($query, $connection) or die ("Error in query: $query. " . mysql_error()); $row = mysql_fetch_row($result); $TotalTimes2 = $row[0]; $TotalHour2 = $row[1]/3600; $query = "SELECT count(*), sum(TIME_TO_SEC(EndTime)-TIME_TO_SEC(StartTime)) from SR_reservation WHERE Date <= '$enddate' and Date >= '$startdate' and RoomNo='$row2[0]' AND (Status = '1' or Status = '2') and StartTime>='19:00:00'"; $result = mysql_query($query, $connection) or die ("Error in query: $query. " . mysql_error()); $row = mysql_fetch_row($result); $TotalTimes3 = $row[0]; $TotalHour3 = $row[1]/3600; $bgcolor = '#DDDDDD'; $query0 = "SELECT Department from SR_department"; $result0 = mysql_query($query0, $connection) or die ("Error in query: $query0. " . mysql_error()); while($row0 = mysql_fetch_row($result0)) { $dpt = $row0[0]; $Hour = 0; $Percentage = 0; $Width = 0; $query = "SELECT count(*), sum(TIME_TO_SEC(EndTime)-TIME_TO_SEC(StartTime)) from SR_reservation, SR_instructor WHERE SR_reservation.InstructorID=SR_instructor.InstructorID and SR_instructor.Department='$dpt' and Date <= '$enddate' and Date >= '$startdate' and RoomNo='$row2[0]' AND (SR_reservation.Status = '1' or SR_reservation.Status = '2')"; $result = mysql_query($query, $connection) or die ("Error in query: $query. " . mysql_error()); $row = mysql_fetch_row($result); $Hour = $row[1]/3600; $Times = $row[0]; // $Percentage = round(100.0*$row[1]/$TotalSec); if ($row[1] == 0){ $Percentage = 0; } else { $Percentage = round(100.0*$row[1]/$TotalSec); } $Width = 2*$Percentage; $query = "SELECT count(*), sum(TIME_TO_SEC(EndTime)-TIME_TO_SEC(StartTime)) from SR_reservation, SR_instructor WHERE SR_reservation.InstructorID=SR_instructor.InstructorID and SR_instructor.Department='$dpt' and Date <= '$enddate' and Date >= '$startdate' and RoomNo='$row2[0]' AND SR_reservation.Status = '3'"; $result = mysql_query($query, $connection) or die ("Error in query: $query. " . mysql_error()); $row = mysql_fetch_row($result); $CancelHour = $row[1]/3600; $CancelTimes = $row[0]; $query = "SELECT count(*), sum(TIME_TO_SEC(EndTime)-TIME_TO_SEC(StartTime)) from SR_reservation, SR_instructor WHERE SR_reservation.InstructorID=SR_instructor.InstructorID and SR_instructor.Department='$dpt' and Date <= '$enddate' and Date >= '$startdate' and RoomNo='$row2[0]' AND SR_reservation.Status = '4'"; $result = mysql_query($query, $connection) or die ("Error in query: $query. " . mysql_error()); $row = mysql_fetch_row($result); $AbsentHour = $row[1]/3600; $AbsentTimes = $row[0]; $query = "SELECT count(*), sum(TIME_TO_SEC(EndTime)-TIME_TO_SEC(StartTime)) from SR_reservation, SR_instructor WHERE SR_reservation.InstructorID=SR_instructor.InstructorID and SR_instructor.Department='$dpt' and Date <= '$enddate' and Date >= '$startdate' and RoomNo='$row2[0]' AND (SR_reservation.Status = '1' or SR_reservation.Status = '2') and StartTime<'16:00:00'"; $result = mysql_query($query, $connection) or die ("Error in query: $query. " . mysql_error()); $row = mysql_fetch_row($result); $Hour1 = $row[1]/3600; $Times1 = $row[0]; $query = "SELECT count(*), sum(TIME_TO_SEC(EndTime)-TIME_TO_SEC(StartTime)) from SR_reservation, SR_instructor WHERE SR_reservation.InstructorID=SR_instructor.InstructorID and SR_instructor.Department='$dpt' and Date <= '$enddate' and Date >= '$startdate' and RoomNo='$row2[0]' AND (SR_reservation.Status = '1' or SR_reservation.Status = '2') and StartTime<'19:00:00' and StartTime>='16:00:00'"; $result = mysql_query($query, $connection) or die ("Error in query: $query. " . mysql_error()); $row = mysql_fetch_row($result); $Hour2 = $row[1]/3600; $Times2 = $row[0]; $query = "SELECT count(*), sum(TIME_TO_SEC(EndTime)-TIME_TO_SEC(StartTime)) from SR_reservation, SR_instructor WHERE SR_reservation.InstructorID=SR_instructor.InstructorID and SR_instructor.Department='$dpt' and Date <= '$enddate' and Date >= '$startdate' and RoomNo='$row2[0]' AND (SR_reservation.Status = '1' or SR_reservation.Status = '2') and StartTime>='19:00:00'"; $result = mysql_query($query, $connection) or die ("Error in query: $query. " . mysql_error()); $row = mysql_fetch_row($result); $Hour3 = $row[1]/3600; $Times3 = $row[0]; print << END; if ($bgcolor == '#DDDDDD'){$bgcolor = '#EEEEED';}else{$bgcolor = '#DDDDDD';} } print <<
Department Times Hours 8:00AM - 4:00PM 4:00PM - 7:00PM 7:00PM - 9:00PM Percentage (Hours/Total) Cancelled Absent
Times Hours Times Hours Times Hours Times Hours Times Hours
$dpt $Times $Hour $Times1 $Hour1 $Times2 $Hour2 $Times3 $Hour3
 $Percentage%
 $CancelTimes $CancelHour $AbsentTimes $AbsentHour
Total: $TotalTimes $TotalHour $TotalTimes1 $TotalHour1 $TotalTimes2 $TotalHour2 $TotalTimes3 $TotalHour3
 100%
 $CTimes $CHour $ATimes $AHour
END; } print <<Total Lab Hours END; $query = "SELECT count(*), sum(TIME_TO_SEC(EndTime)-TIME_TO_SEC(StartTime)) from SR_reservation WHERE Date <= '$enddate' and Date >= '$startdate' AND (Status = '1' or Status = '2')"; $result = mysql_query($query, $connection) or die ("Error in query: $query. " . mysql_error()); $row = mysql_fetch_row($result); $TotalTimes = $row[0]; $TotalSec = $row[1]; $TotalHour = $row[1]/3600; $query = "SELECT count(*), sum(TIME_TO_SEC(EndTime)-TIME_TO_SEC(StartTime)) from SR_reservation WHERE Date <= '$enddate' and Date >= '$startdate' AND Status = '3'"; $result = mysql_query($query, $connection) or die ("Error in query: $query. " . mysql_error()); $row = mysql_fetch_row($result); $CTimes = $row[0]; $CHour = $row[1]/3600; $query = "SELECT count(*), sum(TIME_TO_SEC(EndTime)-TIME_TO_SEC(StartTime)) from SR_reservation WHERE Date <= '$enddate' and Date >= '$startdate' AND Status = '4'"; $result = mysql_query($query, $connection) or die ("Error in query: $query. " . mysql_error()); $row = mysql_fetch_row($result); $ATimes = $row[0]; $AHour = $row[1]/3600; $query = "SELECT count(*), sum(TIME_TO_SEC(EndTime)-TIME_TO_SEC(StartTime)) from SR_reservation WHERE Date <= '$enddate' and Date >= '$startdate' AND (Status = '1' or Status = '2') and StartTime<'16:00:00'"; $result = mysql_query($query, $connection) or die ("Error in query: $query. " . mysql_error()); $row = mysql_fetch_row($result); $TotalTimes1 = $row[0]; $TotalHour1 = $row[1]/3600; $query = "SELECT count(*), sum(TIME_TO_SEC(EndTime)-TIME_TO_SEC(StartTime)) from SR_reservation WHERE Date <= '$enddate' and Date >= '$startdate' AND (Status = '1' or Status = '2') and StartTime<'19:00:00' and StartTime>='16:00:00'"; $result = mysql_query($query, $connection) or die ("Error in query: $query. " . mysql_error()); $row = mysql_fetch_row($result); $TotalTimes2 = $row[0]; $TotalHour2 = $row[1]/3600; $query = "SELECT count(*), sum(TIME_TO_SEC(EndTime)-TIME_TO_SEC(StartTime)) from SR_reservation WHERE Date <= '$enddate' and Date >= '$startdate' AND (Status = '1' or Status = '2') and StartTime>='19:00:00'"; $result = mysql_query($query, $connection) or die ("Error in query: $query. " . mysql_error()); $row = mysql_fetch_row($result); $TotalTimes3 = $row[0]; $TotalHour3 = $row[1]/3600; $bgcolor = '#DDDDDD'; $query0 = "SELECT Department from SR_department"; $result0 = mysql_query($query0, $connection) or die ("Error in query: $query0. " . mysql_error()); while($row0 = mysql_fetch_row($result0)) { $dpt = $row0[0]; $Hour = 0; $Percentage = 0; $Width = 0; $query = "SELECT count(*), sum(TIME_TO_SEC(EndTime)-TIME_TO_SEC(StartTime)) from SR_reservation, SR_instructor WHERE SR_reservation.InstructorID=SR_instructor.InstructorID and SR_instructor.Department='$dpt' and Date <= '$enddate' and Date >= '$startdate' AND (SR_reservation.Status = '1' or SR_reservation.Status = '2')"; $result = mysql_query($query, $connection) or die ("Error in query: $query. " . mysql_error()); $row = mysql_fetch_row($result); $Hour = $row[1]/3600; $Times = $row[0]; $Percentage = round(100.0*$row[1]/$TotalSec); $Width = 2*$Percentage; $query = "SELECT count(*), sum(TIME_TO_SEC(EndTime)-TIME_TO_SEC(StartTime)) from SR_reservation, SR_instructor WHERE SR_reservation.InstructorID=SR_instructor.InstructorID and SR_instructor.Department='$dpt' and Date <= '$enddate' and Date >= '$startdate' AND SR_reservation.Status = '3'"; $result = mysql_query($query, $connection) or die ("Error in query: $query. " . mysql_error()); $row = mysql_fetch_row($result); $CancelHour = $row[1]/3600; $CancelTimes = $row[0]; $query = "SELECT count(*), sum(TIME_TO_SEC(EndTime)-TIME_TO_SEC(StartTime)) from SR_reservation, SR_instructor WHERE SR_reservation.InstructorID=SR_instructor.InstructorID and SR_instructor.Department='$dpt' and Date <= '$enddate' and Date >= '$startdate' AND SR_reservation.Status = '4'"; $result = mysql_query($query, $connection) or die ("Error in query: $query. " . mysql_error()); $row = mysql_fetch_row($result); $AbsentHour = $row[1]/3600; $AbsentTimes = $row[0]; $query = "SELECT count(*), sum(TIME_TO_SEC(EndTime)-TIME_TO_SEC(StartTime)) from SR_reservation, SR_instructor WHERE SR_reservation.InstructorID=SR_instructor.InstructorID and SR_instructor.Department='$dpt' and Date <= '$enddate' and Date >= '$startdate' AND (SR_reservation.Status = '1' or SR_reservation.Status = '2') and StartTime<'16:00:00'"; $result = mysql_query($query, $connection) or die ("Error in query: $query. " . mysql_error()); $row = mysql_fetch_row($result); $Hour1 = $row[1]/3600; $Times1 = $row[0]; $query = "SELECT count(*), sum(TIME_TO_SEC(EndTime)-TIME_TO_SEC(StartTime)) from SR_reservation, SR_instructor WHERE SR_reservation.InstructorID=SR_instructor.InstructorID and SR_instructor.Department='$dpt' and Date <= '$enddate' and Date >= '$startdate' AND (SR_reservation.Status = '1' or SR_reservation.Status = '2') and StartTime<'19:00:00' and StartTime>='16:00:00'"; $result = mysql_query($query, $connection) or die ("Error in query: $query. " . mysql_error()); $row = mysql_fetch_row($result); $Hour2 = $row[1]/3600; $Times2 = $row[0]; $query = "SELECT count(*), sum(TIME_TO_SEC(EndTime)-TIME_TO_SEC(StartTime)) from SR_reservation, SR_instructor WHERE SR_reservation.InstructorID=SR_instructor.InstructorID and SR_instructor.Department='$dpt' and Date <= '$enddate' and Date >= '$startdate' AND (SR_reservation.Status = '1' or SR_reservation.Status = '2') and StartTime>='19:00:00'"; $result = mysql_query($query, $connection) or die ("Error in query: $query. " . mysql_error()); $row = mysql_fetch_row($result); $Hour3 = $row[1]/3600; $Times3 = $row[0]; print << END; if ($bgcolor == '#DDDDDD'){$bgcolor = '#EEEEED';}else{$bgcolor = '#DDDDDD';} } print <<
Department Times Hours 8:00AM - 4:00PM 4:00PM - 7:00PM 7:00PM - 9:00PM Percentage (Hours/Total) Cancelled Absent
Times Hours Times Hours Times Hours Times Hours Times Hours
$dpt $Times $Hour $Times1 $Hour1 $Times2 $Hour2 $Times3 $Hour3
 $Percentage%
 $CancelTimes $CancelHour $AbsentTimes $AbsentHour
Total: $TotalTimes $TotalHour $TotalTimes1 $TotalHour1 $TotalTimes2 $TotalHour2 $TotalTimes3 $TotalHour3
 100%
 $CTimes $CHour $ATimes $AHour

Instructors

END; // $query = "SELECT Department, count(*) from SR_instructor group by Department"; $query = "SELECT Department from SR_instructor group by Department"; // $query = "SELECT Department, count(*) from SR_instructor INNER JOIN SR_course c ON c.instructorID = SR_instructor.instructorID where c.Year='$syear' and c.Semester='$semester' group by Department"; $result = mysql_query($query, $connection) or die ("Error in query: $query. " . mysql_error()); $bgcolor = '#DDDDDD'; while ($row = mysql_fetch_row($result)) { $query1 = "SELECT count(*) from SR_reservation, SR_instructor WHERE SR_reservation.InstructorID=SR_instructor.InstructorID and SR_instructor.Department='$row[0]' and Date <= '$enddate' and Date >= '$startdate' AND (SR_reservation.Status = '1' or SR_reservation.Status = '2') group by SR_instructor.InstructorID"; //echo $query1; $result1 = mysql_query($query1, $connection) or die ("Error in query: $query1. " . mysql_error()); $num_rows = mysql_num_rows($result1); $query4 = "SELECT DISTINCT Department, i.InstructorID FROM SR_instructor i, SR_course c WHERE c.Year = '$syear' AND c.Semester = '$semester' AND c.InstructorID = i.InstructorID AND department ='$row[0]'"; $result4 = mysql_query($query4, $connection) or die ("Error in query: $query1. " . mysql_error()); $total_ins = mysql_num_rows($result4); //$Percentage = round(100.0*$num_rows/$row[1]); if ($total_ins == 0){ $Percentage = 0; } else { $Percentage = round(100.0*$num_rows/$total_ins); } $Width = 2*$Percentage; print << END; if ($bgcolor == '#DDDDDD'){$bgcolor = '#EEEEED';}else{$bgcolor = '#DDDDDD';} } //$query = "SELECT count(*) from SR_instructor";$semester, $syear $query = "SELECT DISTINCT Department, i.InstructorID FROM SR_instructor i, SR_course c WHERE c.Year = '$syear' AND c.Semester = '$semester' AND c.InstructorID = i.InstructorID"; $result = mysql_query($query, $connection) or die ("Error in query: $query. " . mysql_error()); // $row = mysql_fetch_row($result); $total_people = mysql_num_rows($result); $query1 = "SELECT count(*) from SR_reservation WHERE Date <= '$enddate' and Date >= '$startdate' AND (SR_reservation.Status = '1' or SR_reservation.Status = '2') group by InstructorID"; $result1 = mysql_query($query1, $connection) or die ("Error in query: $query1. " . mysql_error()); $num_rows = mysql_num_rows($result1); // $Percentage = round(100.0*$num_rows/$row[0]); $Percentage = round(100.0*$num_rows/$total_people); $Width = 2*$Percentage; print <<
Department Instructors Used LLC Percentage (In each department)
$row[0] $total_ins $num_rows
 $Percentage%
Total: $total_people $num_rows
 $Percentage%

END; $query = "SELECT distinct Task from SR_task order by Place"; $result = mysql_query($query, $connection) or die ("Error in query: $query. " . mysql_error()); $n_rows = mysql_num_rows($result); $i=0; while($row = mysql_fetch_row($result)) { $task[$i]=$row[0]; $count[$i]=0; $i++; } $none=0; $query = "SELECT Tasks from SR_reservation WHERE Date <= '$enddate' and Date >= '$startdate' AND (Status = '1' or Status = '2')"; $result = mysql_query($query, $connection) or die ("Error in query: $query. " . mysql_error()); while($row = mysql_fetch_row($result)) { if ($row[0]==''){$none++;continue;}; for ($i=0;$i<$n_rows;$i++) { if (strpos($row[0], $task[$i]) === false){} else {$count[$i]++;} } }; print <<Tasks END; $bgcolor = '#DDDDDD'; for ($i=0;$i<$n_rows;$i++) { $Percentage = round(100.0*$count[$i]/$TotalTimes); $Width = 2*$Percentage; print << END; if ($bgcolor == '#DDDDDD'){$bgcolor = '#EEEEED';}else{$bgcolor = '#DDDDDD';} } $Percentage = round(100.0*$none/$TotalTimes); $Width = 2*$Percentage; print <<
Tasks Performed Percentage
$task[$i] $count[$i] out of $TotalTimes
 $Percentage%
Not Specified $none out of $TotalTimes
 $Percentage%
END; ?>