LLC Reservation Report
From $startmonth/$startday/$startyear to $endmonth/$endday/$endyear
$dpt
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))
{
$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());
$row1 = mysql_fetch_row($result);
$TotalSec = $row1[1];
$TotalHour = $row1[1]/3600;
$TotalTimes = $row1[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 = '3'";
$result = mysql_query($query, $connection) or die ("Error in query: $query. " . mysql_error());
$row1 = mysql_fetch_row($result);
$CHour = $row1[1]/3600;
$CTimes = $row1[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());
$row1 = mysql_fetch_row($result);
$AHour = $row1[1]/3600;
$ATimes = $row1[0];
print << $row2[1] - $row2[0]
| Name |
Times |
Hours |
Percentage (In $dpt department) |
Cancelled Times |
Cancelled Hours |
Absent Times |
Absent Hours |
END;
$bgcolor = '#DDDDDD';
$query1 = "SELECT FirstName, LastName, InstructorID from SR_instructor WHERE SR_instructor.Department='$dpt'";
$result1 = mysql_query($query1, $connection) or die ("Error in query: $query1. " . mysql_error());
while($row1 = mysql_fetch_row($result1))
{
$query = "SELECT count(*), sum(TIME_TO_SEC(EndTime)-TIME_TO_SEC(StartTime)) from SR_reservation WHERE SR_reservation.InstructorID='$row1[2]' 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;
$Time = $row[0];
$Percentage = $TotalSec == 0 ? 0 : round(100.0*$row[1]/$TotalSec);
$Width = 2*$Percentage;
$query = "SELECT count(*), sum(TIME_TO_SEC(EndTime)-TIME_TO_SEC(StartTime)) from SR_reservation WHERE SR_reservation.InstructorID='$row1[2]' 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);
$CancelTime = $row[0];
$CancelHour = $row[1]/3600;
$query = "SELECT count(*), sum(TIME_TO_SEC(EndTime)-TIME_TO_SEC(StartTime)) from SR_reservation WHERE SR_reservation.InstructorID='$row1[2]' 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);
$AbsentTime = $row[0];
$AbsentHour = $row[1]/3600;
print <<
$row1[0] $row1[1] |
$Time |
$Hour |
|
$CancelTime |
$CancelHour |
$AbsentTime |
$AbsentHour |
END;
if ($bgcolor == '#DDDDDD'){$bgcolor = '#EEEEED';}else{$bgcolor = '#DDDDDD';}
}
print <<
Total: |
$TotalTimes |
$TotalHour |
|
$CTimes |
$CHour |
$ATimes |
$AHour |
END;
}
$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());
$row1 = mysql_fetch_row($result);
$TotalSec = $row1[1];
$TotalHour = $row1[1]/3600;
$TotalTimes = $row1[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 = '3'";
$result = mysql_query($query, $connection) or die ("Error in query: $query. " . mysql_error());
$row1 = mysql_fetch_row($result);
$CHour = $row1[1]/3600;
$CTimes = $row1[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());
$row1 = mysql_fetch_row($result);
$AHour = $row1[1]/3600;
$ATimes = $row1[0];
print <<Total
| Name |
Times |
Hours |
Percentage (In $dpt department) |
Cancelled Times |
Cancelled Hours |
Absent Times |
Absent Hours |
END;
$bgcolor = '#DDDDDD';
mysql_data_seek($result1, 0);
while($row1 = mysql_fetch_row($result1))
{
$query = "SELECT count(*), sum(TIME_TO_SEC(EndTime)-TIME_TO_SEC(StartTime)) from SR_reservation WHERE SR_reservation.InstructorID='$row1[2]' 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;
$Time = $row[0];
$Percentage = $TotalSec == 0 ? 0 : round(100.0*$row[1]/$TotalSec);
$Width = 2*$Percentage;
$query = "SELECT count(*), sum(TIME_TO_SEC(EndTime)-TIME_TO_SEC(StartTime)) from SR_reservation WHERE SR_reservation.InstructorID='$row1[2]' 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);
$CancelTime = $row[0];
$CancelHour = $row[1]/3600;
$query = "SELECT count(*), sum(TIME_TO_SEC(EndTime)-TIME_TO_SEC(StartTime)) from SR_reservation WHERE SR_reservation.InstructorID='$row1[2]' 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);
$AbsentTime = $row[0];
$AbsentHour = $row[1]/3600;
print <<
$row1[0] $row1[1] |
$Time |
$Hour |
|
$CancelTime |
$CancelHour |
$AbsentTime |
$AbsentHour |
END;
if ($bgcolor == '#DDDDDD'){$bgcolor = '#EEEEED';}else{$bgcolor = '#DDDDDD';}
}
print <<
Total: |
$TotalTimes |
$TotalHour |
|
$CTimes |
$CHour |
$ATimes |
$AHour |
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, 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());
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
| Tasks |
Performed |
Percentage |
END;
$bgcolor = '#DDDDDD';
for ($i=0;$i<$n_rows;$i++)
{
$Percentage = round(100.0*$count[$i]/$TotalTimes);
$Width = 2*$Percentage;
print <<
$task[$i] |
$count[$i] out of $TotalTimes |
|
END;
if ($bgcolor == '#DDDDDD'){$bgcolor = '#EEEEED';}else{$bgcolor = '#DDDDDD';}
}
$Percentage = round(100.0*$none/$TotalTimes);
$Width = 2*$Percentage;
print <<
Not Specified |
$none out of $TotalTimes |
|
END;
?>
|