#!/usr/bin/perl
print "Content-type: text/html\n\n";
require "config.idx";
$long = $ENV{'QUERY_STRING'};
if ($long) {
	@pairs=split(/&/,$long);
	foreach $item(@pairs) {
		($name,$content)=split (/=/,$item,2);
		$content=~tr/+/ /;
		$content=~ s/%(..)/pack("c",hex($1))/ge;
		if ($INPUT{$name}) { $INPUT{$name} = $INPUT{$name}.",".$content; }
		else { $INPUT{$name} = $content; }
	}
}
else {
	read(STDIN, $buffer, $ENV{'CONTENT_LENGTH'});
	@pairs = split(/&/, $buffer);
	foreach $pair (@pairs) {
		($name, $value) = split(/=/, $pair);
		$value =~ tr/+/ /;
		$value =~ s/%([a-fA-F0-9][a-fA-F0-9])/pack("C", hex($1))/eg;
		if ($INPUT{$name}) { $INPUT{$name} = $INPUT{$name}.",".$value; }
		else { $INPUT{$name} = $value; }
	}
}

if ($INPUT{action} eq "openhouseadd")	{ &openhouseadd; }
elsif ($INPUT{action} eq "openhouselist")			{ &openhouselist; }
elsif ($INPUT{action} eq "openhousedoadd")			{ &openhousedoadd; }
elsif ($INPUT{action} eq "openhouseverify")			{ &openhouseverify; }
elsif ($INPUT{action} eq "openhouseedit")			{ &openhouseedit; }
elsif ($INPUT{action} eq "openhousedelete")			{ &openhousedelete; }
else { &main }

use DBI;
my $dsn = "DBI:mysql:database=$d_name;host=localhost";
my $dbh = DBI->connect($dsn, $d_username, $d_pass, {RaiseError => 1}) || die $DBI::errstr;

sub main {

$sql_show = " WHERE status = 'a'";
$sql_print = "Showing Active ";


if ($INPUT{sql_show} eq "a") {
$sql_show = " WHERE status = 'a'";
$sql_print = "Showing Active ";
}
elsif ($INPUT{sql_show} eq "all") {
$sql_show = "";
$sql_print = "Showing ALL ";
}

&header;

print qq~
<td valign="top" width="581">

<img src="images/headers/open_houses.gif" width="581" height="33" alt="Open Houses"><br><br>
<form method="get" action="openhouse.idx">
<input type="hidden" name="action" value="main">
<select name="sql_show" class="adminform">
<option value="$showing">$sql_print Open Houses</option>
<option value="all">---------</option>
<option value="a">Show Active Open Houses</option>
<option value="all">Show All Open Houses</option>
</select> <input type="submit" value="Change Search" class="adminform">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<a href="openhouse.idx?action=openhouseadd" style="font-size: 18px; color: red; text-decoration: underline; ">Add New Open House</a><br></form>
<b><font color="red">$status</font></b>
<table border="0" cellpadding="3" cellspacing="1" width="100%" bgcolor="black">
<tr>
<td bgcolor="black"><font color="white"><b><a href="openhouse.idx?sortby=status" class="ohadmin">Status</a></b></font></td>
<td bgcolor="black"><font color="white"><b><a href="openhouse.idx?sortby=house" class="ohadmin">MLS#</a></b></font></td>
<td bgcolor="black"><font color="white"><b><a href="openhouse.idx?sortby=house" class="ohadmin">Address</a></b></font></td>
<td bgcolor="black"><font color="white"><b><a href="openhouse.idx?sortby=agent" class="ohadmin">Agent</a></b></font></td>
<td bgcolor="black"><font color="white"><b><a href="openhouse.idx?sortby=date" class="ohadmin">Time Of Open House</a></b></font></td>
<td bgcolor="black">&nbsp;</td>
</tr>
~;
use DBI;
my $dsn = "DBI:mysql:database=$d_name;host=localhost";
my $dbh = DBI->connect($dsn, $d_username, $d_pass, {RaiseError => 1}) || die $DBI::errstr;

$trcolor = "1";
$trcolor2 = "2";

###CHECK SORT BY
unless ($INPUT{sortby}) { $INPUT{sortby} = "date"; }


my $aasth = $dbh->prepare("select id,status,house,agent,DATE_FORMAT(date, '%W %m/%d/%y'),start_timehour,start_timeminute,start_ap,end_timehour,end_timeminute,end_ap FROM openhouses$sql_show order by $INPUT{sortby} ASC");
$aasth->execute;
while (($id,$status,$house,$agent,$date,$start_timehour,$start_timeminute,$start_ap,$end_timehour,$end_timeminute,$end_ap) = $aasth->fetchrow_array()) {

my $agasth = $dbh->prepare("select firstname,lastname from agents where agentid = '$agent'");
$agasth->execute;
while (($firstname,$lastname) = $agasth->fetchrow_array()) {
$found = "yes";


my $bgasth = $dbh->prepare("select AddressNumber,AddressDirection,AddressStreet from listings_res3 where MLSNUM = '$house'");
$bgasth->execute;
while (($addressnumber,$addressdirection,$addressstreet) = $bgasth->fetchrow_array()) {
$found = "yes";

	if ($trcolor eq $trcolor2) {
	$trcolor = "1";
	$setcolor = "#FFFF99";
	}
	else {
	$setcolor = "#FFFFFF";
	$trcolor++;
	}


 if ($status eq "a") {
	$showstatus = "Active";
}
print qq~
<tr bgcolor="$setcolor" onMouseOver="this.bgColor='#FFFF00';" onMouseOut="this.bgColor='$setcolor';">
<td nowrap><b>$showstatus</b><br></td>
<td nowrap><b>$house</b><br></td>
<td nowrap><b>$addressnumber $addressdirection $addressstreet</b><br></td>
<td nowrap><b>$firstname $lastname</b><br></td>
<td nowrap><b>$date from $start_timehour:$start_timeminute $start_ap to $end_timehour:$end_timeminute $end_ap</b><br></td>
<td nowrap align="center"><a href="openhouse.idx?action=openhousedelete&id=$id"><img src="images/trans_sys/icons/delete-page-red.gif" height="14" width="14" alt="Delete $addressnumber $addressdirection $addressstreet" border="0"></a><br></td>
</tr>
~;
}
}
if ($found ne "yes") {
print qq~
<tr>
<td colspan="6">
<font color="red"><b>No Results Found!</b></font><br>
</td>
</tr>
~;
}
}

print qq~
</table><br><br>
~;
print "</td>";
&footer;

}




sub openhouseverify {

$printhouse = "$INPUT{house}";
$printagent = "$INPUT{agent}";

use DBI;
my $dsn = "DBI:mysql:database=$d_name;host=localhost";
my $dbh = DBI->connect($dsn, $d_username, $d_pass, {RaiseError => 1}) || die $DBI::errstr;

my $aasth = $dbh->prepare("select Price,AddressNumber,AddressDirection,AddressStreet,City,Zip,MLSNUM,AgentID FROM listings_res3 where MLSNUM = '$printhouse'");
$aasth->execute;
while (($Price,$AddressNumber,$AddressDirection,$AddressStreet,$City,$Zip,$MLSNUM,$AgentID) = $aasth->fetchrow_array()) {
$printhouse = "$MLSNUM - $AddressNumber $AddressDirection $AddressStreet - $City";
}
my $basth = $dbh->prepare("select mlsid,firstname,lastname from agents WHERE mlsid = '$printagent'");
$basth->execute;
while (($mlsid,$firstname,$lastname) = $basth->fetchrow_array()) {
$printagent = "$lastname, $firstname";
}
&header;
print qq~
<td valign="top" width="581">
<img src="images/headers/open_houses.gif" width="581" height="33" alt="Open Houses"><br><br>
<font size="3"><b>Verify New Open House</b><font><br><br>
<form method="get" action="openhouse.idx">
<input type="hidden" name="action" value="openhousedoadd">
Property Being Held Open<br>
<b>MLS Number $printhouse</b><br><br>
Agent At Open House<br>
<b>$printagent</b><br><br>
Time Of Open House<br>
<b>$INPUT{date} from $INPUT{start_timehour}:$INPUT{start_timeminute} $INPUT{start_ap} to $INPUT{end_timehour}:$INPUT{end_timeminute} $INPUT{end_ap}</b><br><br>
<input type="hidden" name="status" value="a">
<input type="hidden" name="house" value="$INPUT{house}">
<input type="hidden" name="agent" value="$INPUT{agent}">
<input type="hidden" name="date" value="$INPUT{date}">
<input type="hidden" name="start_timehour" value="$INPUT{start_timehour}">
<input type="hidden" name="start_timeminute" value="$INPUT{start_timeminute}">
<input type="hidden" name="start_ap" value="$INPUT{start_ap}">
<input type="hidden" name="end_timehour" value="$INPUT{end_timehour}">
<input type="hidden" name="end_timeminute" value="$INPUT{end_timeminute}">
<input type="hidden" name="end_ap" value="$INPUT{end_ap}">
<input type="submit" value="Looks Correct, Add To Website!">
</form>

~;
$dbh->disconnect;
print "</td>";
&footer;
}


sub openhousedoadd {

use DBI;
my $dsn = "DBI:mysql:database=$d_name;host=localhost";
my $dbh = DBI->connect($dsn, $d_username, $d_pass, {RaiseError => 1}) || die $DBI::errstr;

@variables = ($INPUT{status},$INPUT{house},$INPUT{agent},$INPUT{date},$INPUT{start_timehour},$INPUT{start_timeminute},$INPUT{start_ap},$INPUT{end_timehour},$INPUT{end_timeminute},$INPUT{end_ap});

$sql_query = "INSERT INTO openhouses values ('',?,?,?,?,?,?,?,?,?,?)";
my $aasth = $dbh->do($sql_query, undef, @variables);
$dbh->disconnect;
$status = "Successfully Added Open House!";
&main;
}

sub openhouseadd {

use DBI;
my $dsn = "DBI:mysql:database=$d_name;host=localhost";
my $dbh = DBI->connect($dsn, $d_username, $d_pass, {RaiseError => 1}) || die $DBI::errstr;

	my $dateaasth = $dbh->prepare("select DATE_FORMAT(now(), '%Y-%m-%d')");
	$dateaasth->execute;
	while (($newbackdate) = $dateaasth->fetchrow_array()) {
	$findsdate = "$newbackdate";
	}

my $aasth = $dbh->prepare("select Price,AddressNumber,AddressDirection,AddressStreet,City,Zip,MLSNUM,AgentID FROM listings_res3 where ListingOffice1ID = '48' ORDER BY AddressStreet ASC");
$aasth->execute;

&header;
print qq~
<td valign="top" width="581">
<img src="images/headers/open_houses.gif" width="581" height="33" alt="Open Houses"><br>
<script language="JavaScript" src="calendar1.js"></script>

<table border="0" cellpadding="0" cellspacing="3">
	<tr>
		<td valign="top" width="100%">
		<font size="3"><b>Setup New Open House</b><font><br>
		<form method="get" name="openhouse" action="openhouse.idx">
		<input type="hidden" name="action" value="openhouseverify"><br>
		<font size="2"><b>1 - Choose Listing To Be Open</b></font><br>
			<select name="house" class="form">
~;
		while (($Price,$AddressNumber,$AddressDirection,$AddressStreet,$City,$Zip,$MLSNUM,$AgentID) = $aasth->fetchrow_array()) {
			print qq~
				<option value="$MLSNUM">$AddressStreet $AddressNumber $AddressDirection - $City - $MLSNUM</option>\n
			~;
		}
print qq~
			</select>
<br>
<hr color="navy" width="70%">
		<font size="2"><b>2 - Choose Agent That Will Be At Open House</b></font><br>
		<select name="agent" class="form">
~;
my $basth = $dbh->prepare("select agentid,firstname,lastname from agents where display = '1' ORDER BY lastname ASC");
$basth->execute;
while (($agentid,$firstname,$lastname) = $basth->fetchrow_array()) {
print qq~
	<option value="$agentid">$lastname, $firstname</option>\n
~;
}
print qq~
		</select>
<br>
<hr color="navy" width="70%">
		<font size="2"><b>3 - Choose Date and Time To Be Open</b></font><br>
<input type="text" size="10" name="date" value="$findsdate" class="form">&nbsp;<a href="javascript:cal1.popup();"><img src="images/cal.gif" width="16" height="16" border="0" alt="Click Here to Pick up the date"></a>

			<script language="JavaScript">
			<!-- //
				var cal1 = new calendar1(document.forms['openhouse'].elements['date']);
				cal1.year_scroll = true;
				cal1.time_comp = false;

			//-->
			</script>
 from 
<select name="start_timehour" size="1" class="form">
	<Option value="1">1</option>
	<Option value="2" selected>2</option>
	<Option value="3">3</option>
	<Option value="4">4</option>
	<Option value="5">5</option>
	<Option value="6">6</option>
	<Option value="7">7</option>
	<Option value="8">8</option>
	<Option value="9">9</option>
	<Option value="10">10</option>
	<Option value="11">11</option>
	<Option value="12">12</option>
</select>:<select name="start_timeminute" size="1" class="form">
	<Option value="15">15</option>
	<Option value="30">30</option>
	<Option value="45">45</option>
	<Option value="00" selected>00</option>
</select>
 <select name="start_ap" size="1" class="form">
	<Option value="PM" selected>PM</option>
	<Option value="AM">AM</option>
</select> to 
<select name="end_timehour" size="1" class="form">
	<Option value="1">1</option>
	<Option value="2">2</option>
	<Option value="3">3</option>
	<Option value="4" selected>4</option>
	<Option value="5">5</option>
	<Option value="6">6</option>
	<Option value="7">7</option>
	<Option value="8">8</option>
	<Option value="9">9</option>
	<Option value="10">10</option>
	<Option value="11">11</option>
	<Option value="12">12</option>
</select>:<select name="end_timeminute" size="1" class="form">
	<Option value="15">15</option>
	<Option value="30">30</option>
	<Option value="45">45</option>
	<Option value="00" selected>00</option>
</select> 
<select name="end_ap" size="1" class="form">
	<Option value="PM" selected>PM</option>
	<Option value="AM">AM</option>
</select>
		<br><br><br>
		<input type="submit" class="form" value="Next >>>">
		</form>
		</font></font></td>
	</tr>
</table>

</td>
~;
$dbh->disconnect;
print "</td>";
&footer;
}

sub openhousedelete {
use DBI;
my $dsn = "DBI:mysql:database=$d_name;host=localhost";
my $dbh = DBI->connect($dsn, $d_username, $d_pass, {RaiseError => 1}) || die $DBI::errstr;
my $aasth = $dbh->do("delete from agents where agentid = '$INPUT{mlsid}'");
$dbh->disconnect;
@variables = ($INPUT{id});
$sql_query = "delete from openhouses where id = ?";
my $aasth = $dbh->do($sql_query, undef, @variables);
$status = "Successfully Deleted!";
&main;
}