use Date::Manip;
use DBI();
use CGI qw(:standard);
require "../common.pl";
require "../sitevariables.pl";

$cur = CGI->new();
$mode = "default";
if($cur->param("mode")) {
	$mode = $cur->param("mode");
}
$day = $cur->param("day");
$month = $cur->param("month");
$year = $cur->param("year");
$sec = $cur->param("sec");
$min = $cur->param("min");
$hour = $cur->param("hour");

$end_min = $cur->param("end_min");
$end_hour = $cur->param("end_hour");
$end_am_pm = $cur->param("end_am_pm");

$am_pm_day = $cur->param("am_pm_day");
$laptop = $cur->param("laptop");
$conf_room = $cur->param("conf_room");

@am_or_pm = ("","AM","PM");
@end_am_or_pm = ("","AM","PM");
("","Exec","06","11","16","31","35","47","none");
@pretty_months = ("","January","Febuary","March","April","May","June","July","August","September","October","November","December");
$content .= "<table width=100% border=0><tr><td width=50%><a href=\"../index.cgi?month=$month&year=$year\"><-- back to calendar</a></td>";
$content .= "<td width=50% align=right>&nbsp;</td></tr></table>";

if($cur->param("Delete")){
	$content .= delete_appointment($day,$month,$year,$hour,$min,$conf_room,$conf_rooms); 
}

$content .= print_form();

if($cur->param("conf_room")){
	$content .= update_day();
}

$content .= display_appointments($day,$month,$year,$hour,$min,$am_pm_day,$end_hour,$end_min,$end_am_pm,$conf_room);

open (TEMPLATE, "../template.html")||dienice("Content-type: text/html\n\nCould not find template");
while (<TEMPLATE>){
	$page .= $_;
}
$page =~ s/<!-- ?content ?-->/$content/ig;
close (TEMPLATE);
print "Content-type: text/html\n\n$page";
############ BEGIN SUBROUTINES #################################

sub print_form {
	my $return;
	my ($sec,$min,$hr,$mday,$mon,$year_localtime,$wday,$yday,$isdst) = localtime(time());
	my $current_year = $year_localtime + 1900;
	my $current_month = $mon + 1;
	
	my $current_sec = $sec;
	my $current_min = $min;
	my $current_hour = $hr;
	
	$return .= "
<center>
<form method=post>
<table bgcolor=$edittitlebg cellpadding=0 cellspacing=1 border=0 width=375><tr><td>
<table bgcolor=$editborder cellpadding=4 cellspacing=0 width=100%>
<tr bgcolor=$edittitlebg><td><font color=$edittitlefont>Reserve a Conference Room</font></td></tr>
<tr><td>Date&nbsp;&nbsp;&nbsp;&nbsp;
<select name=month>";
	for($index=1;$index < 13;$index++){
		if($month){$current_month=$month;}
		if($current_month==$index){
			$return .="<option value=$index selected>$pretty_months[$index]</option>\n";
		}else{
			$return .="<option value=$index>$pretty_months[$index]</option>\n";
		}
	}
	$return .= "
</select> &nbsp;&nbsp;&nbsp;&nbsp; <select name=day>";
	for($index=1;$index < 32;$index++){
		if($day){$mday=$day; $current_day = $day;}
		if($index == $mday){
			$return .="<option value=$index selected>$index</option>\n";
		}else{
			$return .="<option value=$index>$index</option>\n";
		}
	}
	$return .= "
</select> &nbsp;&nbsp;&nbsp;&nbsp; <select name=year>";
	if($year){$current_year = $year; }
	for($index=$current_year-5;$index < $current_year+5;$index++){
		if($current_year==$index){
			$return .="<option value=\"$index\" selected>$index</option>\n";
		}else{
			$return .="<option value=$index>$index</option>\n";
		}
	}
	$return .= "
</select><br>
<br>
</select> Start Time&nbsp;&nbsp;&nbsp;&nbsp; <select name=hour>";
	if($hour){$current_hour = $hour; }
	#for($index=0;$index < 24;$index++){
	for($index=1;$index < 13;$index++){
		if($current_hour==$index){
			$return .="<option value=\"$index\" selected>$index</option>\n";
		}else{
			$return .="<option value=$index>$index</option>\n";
		}
	}
	$return .= "
</select> : <select name=min>";
	if($min){$current_min = $min;}
	for($index=0;$index < 60;$index++){
		if($current_min==$index){
			if($index < 10) {
				$return .="<option value=\"$index\" selected>0$index</option>\n";
			}else{$return .="<option value=\"$index\" selected>$index</option>\n"; }
		}else{
			if($index < 10) {
			   $return .="<option value=$index>0$index</option>\n";
			}else{ $return .="<option value=$index>$index</option>\n"; }
		}
	}
	$return .= "
</select>&nbsp;&nbsp;&nbsp;&nbsp;<select name=am_pm_day>";
	if(am_pm_day) {
		for($index=1;$index < 3; $index++) {
			if($index == 1) { 
	           	   $return .="<option value=$index selected>$am_or_pm[1]</option>\n";
			}
			if($index == 2) {
	           	   $return .="<option value=$index selected>$am_or_pm[2]</option>\n";
			}
		}
	}
	$return .= "
</select>
<br><br>
</select>End Time&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <select name=endy_hour>";
	if($endy_hour){
		$end_hour = $endy_hour;
	}
	#for($index=0;$index < 24;$index++){
	for($index=1;$index < 13;$index++){
		if($current_hour==$index){
			$return .="<option value=\"$index\" selected>$index</option>\n";
		}else{
			$return .="<option value=$index>$index</option>\n";
		}
	}
	$return .= "
</select> : <select name=endy_min>";
	if($endy_min){
		$end_min = $endy_min;
	}
	for($index=0;$index < 60;$index++){
		if($current_min==$index){
			if($index < 10) {
				$return .="<option value=\"$index\" selected>0$index</option>\n";
			}else{$return .="<option value=\"$index\" selected>$index</option>\n"; }
		}else{
			if($index < 10) {
			   $return .="<option value=$index>0$index</option>\n";
			}else{ $return .="<option value=$index>$index</option>\n"; }
		}
	}
	$return .= "
</select>&nbsp;&nbsp;&nbsp;&nbsp;<select name=end_am_pm>";
	if(end_am_pm) {
		for($index=1;$index < 3; $index++) {
		   if($index == 1) { 
		      $return .="<option value=$index selected>$end_am_or_pm[1]</option>\n";
		   }
		   if($index == 2) {
		      $return .="<option value=$index selected>$end_am_or_pm[2]</option>\n";
		   }
		}
	}
	$return .= "
</select>
<br><br>
Conference Room
	</select> &nbsp;&nbsp;&nbsp;&nbsp; <select name=conf_room>";
		for($index=1;$index < 9;$index++){
			$return .="<option value=$index selected>$conf_rooms[$index]</option>\n";
		}
		$return .= "
	</select><br>
<!--<input type=radio name=type value=weekly>every week<br>
<input type=radio name=type value=biweekly>every week<br>-->
<br>Please input the following information<br>
<FORM METHOD=POST ACTION=/var/www/cgi-bin/sqlcalendar/protected/update.cgi>
<PRE>
<tr><td>
First Name:&nbsp;<INPUT NAME=$fname SIZE=34><br><br>
Last Name:&nbsp;<INPUT NAME=$lname SIZE=34><br><br>
Client:&nbsp;<INPUT NAME=$client SIZE=34><br><br>
<input type=submit value=Reserve>
</td></tr>
</font>
</PRE>

</td></tr>
</table>
</td></tr></table>
</form>
</center>
";
	return $return;
}

sub display_appointments($$$){
	$db = DBI->connect("DBI:mysql:database=dbname;host=localhost", mysql, "");
	if(! $db) {
		print "!!Connection Failed!!<P>";
		die();
	}
	my $return;
	$return .= "<p><center>
	<table bgcolor=$edittitlebg cellpadding=0 cellspacing=1 border=0 width=375>
	<tr><td>
		<table bgcolor=$editborder cellpadding=4 cellspacing=0 width=100%>
			<tr bgcolor=$edittitlebg><td>
			<font color=$edittitlefont>Today's Reserved Conference Rooms</font>
			</td></tr>
		</table>
	</td></tr>
	</table>
	<table> 
 	   <tr> <td>
	      ";
         	         $newDate = "$year:$month:$day";
	         $query = "SELECT confName, start_time, end_time FROM Reserves_Conf WHERE resDate='$newDate'"; 
	         $select = $db->prepare($query);
	         $select->errstr, "\n";
	         $rv = $select->execute() or die "Couldn't execute query '$query' \n";
	         $select->bind_columns(undef, \$confName,\$start_time,\$end_time);
	         while($select->fetchrow_arrayref()) {
		  if($confName eq "Exec") {
	               $return .= "<left><form method=post><input type=submit value=Delete>
		     $confName &nbsp;&nbsp;&nbsp;&nbsp; $start_time &nbsp;&nbsp;&nbsp;&nbsp; $end_time\n 
		     </form></left><br>"; 
	            }
		  else {
	               $return .= "<left><form method=post><input type=submit value=Delete>
		     $confName &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; $start_time &nbsp;&nbsp;&nbsp;&nbsp; 
		$end_time\n 
		     </form></left><br>"; 
		  }
	         }
	         $return .= "
	   </td> </tr>
	</table>
	</center></p>";
	return $return;
}

sub update_day(){
	my $update_hour= $cur->param("hour"); my $end_hour= $cur->param("endy_hour");
	my $update_min= $cur->param("min"); my $end_min= $cur->param("endy_min");
	my $update_year= $cur->param("year"); my $update_ampm = $cur->param("am_pm_day");
	my $update_month= $cur->param("month"); my $update_endam = $cur->param("end_am_pm");
	my $update_day= $cur->param("day");  my $update_conf_room = $cur->param("conf_room");
	my $update_conf_rooms = $cur->param("conf_rooms");

	$db = DBI->connect("DBI:mysql:database=dbname;host=localhost", mysql, "");
	if(! $db) {
		print "!!Connection Failed!!<P>";
		die();
	}
if($conf_rooms[$update_conf_room] eq "none") { $resCurrent = 1;}
else {
	if(($update_hour == 12) && ($am_or_pm[$update_ampm] eq 'AM')) {
		$update_hour = 0;
	}
	if(($end_hour == 12) && ($end_am_or_pm[$update_endam] eq 'AM')) {
		$end_hour = 0;
	}
	if(($update_hour != 12) && ($end_hour != 12)) {
	   if($am_or_pm[$update_ampm] eq 'PM') { $update_hour = $update_hour + 12; } 
	   if($end_am_or_pm[$update_endam] eq 'PM') { $end_hour = $end_hour + 12; }
	}
   
	    
	if ($resCurrent == 1) {
	   print "!! Conference Room: $conf_room, has been reserved during this time period !! \n<P>"
	}
	else {
	   $totime = "$update_hour:$update_min:00";
	   $endtime = "$end_hour:$end_min:00";
	   $resrvDate = sprintf("%04d-%02d-%02d", $update_year, $update_month, $update_day);
	   $statement = qq[insert into Reserves_Conf(confID,confName,start_time,end_time,	
	 	         resDate,id) values('$confID','$conf_rooms[$update_conf_room]',
		         '$totime','$endtime','$resrvDate','100000')];
	   $insert = $db->prepare($statement) or die "Couldn't prepare the query:", 
             $insert->errstr, "\n";
	   $rv = $insert->execute or die "Order insert failed: ", $insert->errstr, "\n";
          }
}
	$db->disconnect();			
}

sub delete_appointment($$$$){
	my $update_conf_rooms = $cur->param("conf_rooms"); my $update_conf_room = $cur->param("conf_room");
	my $update_hour = $cur->param("hour"); my $update_min = $cur->param("min"); 
	my $update_year = $cur->param("year");my $update_month = $cur->param("month"); 
	my $update_day = $cur->param("day");
	$db = DBI->connect("DBI:mysql:database=dbname;host=localhost", mysql, "");
	if(! $db) {
		print "!!Connection Failed!!<P>";
		die();
	}

	$query = "select confName from Reserves_Conf WHERE confName = '$update_conf_rooms[$update_conf_room]'"; 
	$select = $db->prepare($query);
	$select->errstr, "\n";
	$rv = $select->execute() or die "Couldn't execute query '$query' \n";
	$select->bind_columns(undef, \$confName);

	$newDate = sprintf("%04d-%02d-%02d", $update_year, $update_month, $update_day);
	$newTime = "$update_hour:$update_min:00";
	$statement = "DELETE FROM Reserves_Conf WHERE resDate='$newDate' AND start_time='$newTime'";
	$statement = $db->prepare($statement);
	$statement->errstr, "\n";
	$rv = $statement->execute() or die "Couldn't execute query '$query' \n";
          $db->disconnect();			
}