~;
}
}
sub overview_showingsedit_do {
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{mlsnumber},$INPUT{showingagent},$INPUT{showingtype},$INPUT{showingdate},$INPUT{timerange1},$INPUT{timerange2},$INPUT{enteredby},$INPUT{status},$INPUT{confirmeddatetime},$INPUT{confirmedwith},$INPUT{notes},$INPUT{email_seller},$INPUT{email_agent},$INPUT{showingid});
$sql_query = "update trans_boardshowings set mlsnumber = ?, showingagent = ?, showingtype = ?, appointmentdate = ?, timerange1 = ?, timerange2 = ?, enteredby = ?, status = ?, confirmeddatetime = ?, confirmedwith = ?, notes = ?, email_seller = ?, email_agent = ? where showingid = ?";
my $aasth = $dbh->do($sql_query, undef, @variables);
print qq~
Making Changes to Datebase
Please wait, Processing You will be redirected to when finished. If not Click Here.
~;
}
sub overview_showingsedit {
print qq~
Showing Administration -The ORR Team - RE/MAX Real Estate Groups
~;
&header;
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 showingid,mlsnumber,status,enteredby,showingagent,showingtype,appointmentdate,timerange1,timerange2,confirmeddatetime,confirmedwith,notes,email_seller,email_agent from trans_boardshowings where showingid = '$INPUT{showingid}'");
$aasth->execute;
while (($showingid,$mlsnumber,$status,$enteredby,$showingagent,$showingtype,$appointmentdate,$timerange1,$timerange2,$confirmeddatetime,$confirmedwith,$notes,$email_agent,$email_seller) = $aasth->fetchrow_array()) {
my $baasth = $dbh->prepare("select id,firstname,lastname,company from trans_boarddirectory where id = '$showingagent'");
$baasth->execute;
while (($sadirectoryid,$safirstname,$salastname,$sacompany) = $baasth->fetchrow_array()) {
$populateshowingagent = "\n";
$populateshowingagent .= "\n";
}
my $aashowingagent = $dbh->prepare("select id,firstname,lastname,company from trans_boarddirectory ORDER By company ASC");
$aashowingagent->execute;
while (($gbid,$gbfirstname,$gblastname,$gbcompany) = $aashowingagent->fetchrow_array()) {
$getshowingagent .= "";
}
$gettimerange1 = "";
$gettimerange1 .= "";
$gettimerange2 = "";
$gettimerange2 .= "";
$getenteredby = "";
$getenteredby .= "";
$getstatus = "";
$getstatus .= "";
$getshowingtype = "";
$getshowingtype .= "";
$getemail_seller = "";
$getemail_seller .= "";
$getemail_agent = "";
$getemail_agent .= "";
if ($confirmeddatetime eq "0000-00-00 00:00:00") {
$confirmeddatetime = "";
}
if ($confirmedwith eq "") {
$confirmedwith = "Not Confirmed";
}
print qq~
manage orrteam property showings and company directory in this area
~;
}
print qq~
~;
&footer;
}
sub overview_showings {
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;
print qq~
Showing Administration -The ORR Team - RE/MAX Real Estate Groups
~;
&header;
print qq~
~;
if ($INPUT{enternf} eq "yes") {
@variables = ($INPUT{showingid},$INPUT{mlsnumber},$INPUT{comments});
$sql_query = "INSERT INTO trans_boardfeedback values (?,now(),?,?)";
my $aasth = $dbh->do($sql_query, undef, @variables);
# Sets Default E-Mail From Address #
$from_email = "Showings\@ORRHomes.com";
my $idaasth = $dbh->prepare("select showingid,mlsnumber,status,enteredby,showingtype,showingagent,DATE_FORMAT(appointmentdate, '%b %D, %Y'),timerange1,timerange2,DATE_FORMAT(confirmeddatetime, '%b %D, %Y at %h\:%i%p'),confirmedwith,notes,confcode,email_seller,email_agent from trans_boardshowings where showingid = '$INPUT{showingid}'");
$idaasth->execute;
while (($data_showingid,$data_mlsnumber,$data_status,$data_enteredby,$data_showingtype,$data_showingagent,$data_appointmentdate,$data_timerange1,$data_timerange2,$data_confirmeddatetime,$data_confirmedwith,$data_notes,$data_confcode,$data_emailseller,$data_emailagent) = $idaasth->fetchrow_array()) {
$showpicture = &image_format("full","$data_mlsnumber");
if ($data_notes eq "") { $data_notes = "None Noted"; }
my $sagentaasth = $dbh->prepare("select firstname,lastname,company from trans_boarddirectory where id = '$data_showingagent'");
$sagentaasth->execute;
while (($sagent_firstname,$sagent_lastname,$sagent_company) = $sagentaasth->fetchrow_array()) {
$showingagent_firstname = $sagent_firstname;
$showingagent_lastname = $sagent_lastname;
$showingagent_company = $sagent_company;
}
my $aaasth = $dbh->prepare("select addressnumber,addressdirection,addressstreet,city,state,zip from trans_board where mlsnumber = '$data_mlsnumber' AND (type = 'BSP' or type = 'SP' or type = 'Listing') LIMIT 0,1");
$aaasth->execute;
while (($l_addressnumber,$l_addressdirection,$l_addressstreet,$l_city,$l_state,$l_zip) = $aaasth->fetchrow_array()) {
$address_short =qq~$l_addressnumber $l_addressdirection $l_addressstreet~;
$address_long =qq~$l_addressnumber $l_addressdirection $l_addressstreet $l_city, $l_state $l_zip~;
}
### EMAIL NOTIFICATIONS OF FEEDBACK RECEIVED SHOWING!!!!!!!!!
if ($data_showingtype eq "Showing") {
################################## Notify Owner(s) ####### OKAY TO SEND EMAIL IF INSPECTION #### CHECK IF EMAIL DISABLED #################################
my $oaasth = $dbh->prepare("select mlsnumber,showingtype,email_seller from trans_boardshowings where showingid = '$data_showingid'");
$oaasth->execute;
while (($owner_mlsnumber,$tb_type,$tb_email_seller) = $oaasth->fetchrow_array()) {
if ($tb_email_seller eq "Yes") {
my $o2aasth = $dbh->prepare("select id from trans_board where mlsnumber = '$owner_mlsnumber' AND (type = 'BSP' or type = 'SP' or type = 'Listing')");
$o2aasth->execute;
while (($owner2_id) = $o2aasth->fetchrow_array()) {
my $sbaasth = $dbh->prepare("select contactid from trans_boardtranstocontact WHERE transid = '$owner2_id' AND type = 'Seller'");
$sbaasth->execute;
while (($tempcontactid) = $sbaasth->fetchrow_array()) {
my $sddasth = $dbh->prepare("select id,agent,type,firstname,lastname,address,spouse,city,state,zip,phone1type,phone1number,phone2type,phone2number,phone3type,phone3number,faxnumber,email,emailtwo,notes from trans_boardcontact WHERE id = '$tempcontactid'");
$sddasth->execute;
while (($s_contactid,$s_agent,$s_type,$s_firstname,$s_lastname,$s_address,$s_spouse,$s_city,$s_state,$s_zip,$s_phone1type,$s_phone1number,$s_phone2type,$s_phone2number,$s_phone3type,$s_phone3number,$s_faxnumber,$s_email,$s_emailtwo,$s_notes) = $sddasth->fetchrow_array()) {
if ($s_spouse ne "") {
$s_spouse = " & $s_spouse ";
}
else {
$s_spouse = "";
}
open(MAIL,"|/usr/sbin/sendmail -t");
print MAIL "To: $s_email\n";
unless ($s_emailtwo eq "") {
print MAIL "To: $s_emailtwo\n";
}
print MAIL "From: $from_email\n";
print MAIL "Subject: Feedback Received For $tb_type at $address_short on $data_appointmentdate from $data_timerange1 - $data_timerange2\n";
print MAIL "Content-type: text/html\n\n";
print MAIL qq~
The ORR Home Selling Team - RE/MAX Real Estate Groups
Dear $s_firstname $s_spouse $s_lastname
This notification has been sent to notify you we have received feedback for the following showing:
Property Address $address_long
Appointment Type $tb_type
Unfiltered Feedback Received from Showing Agent: $INPUT{comments}
$db_date Date
$data_appointmentdate
Showing Time
$data_timerange1 - $data_timerange2
If you have any questions regarding this appointment please call us today!
The ORR Home Selling Team and RE/MAX look forward to serving you. If at anytime you have questions or comments please call us at 765-741-9111
~;
close(MAIL);
$send_subject =qq~Feedback Received For $tb_type at $address_short on $data_appointmentdate from $data_timerange1 - $data_timerange2~;
$send_body =qq~
The ORR Home Selling Team - RE/MAX Real Estate Groups
Dear $s_firstname $s_spouse $s_lastname
This notification has been sent to notify you we have received feedback for the following showing:
Property Address $address_long
Appointment Type $tb_type
Unfiltered Feedback Received from Showing Agent: $INPUT{comments}
$db_date Date
$data_appointmentdate
Showing Time
$data_timerange1 - $data_timerange2
If you have any questions regarding this appointment please call us today!
The ORR Home Selling Team and RE/MAX look forward to serving you. If at anytime you have questions or comments please call us at 765-741-9111
~;
######## RECORD COPY OF EMAIL IN DATABASE #################
@evariables = ("$data_showingid","Owner","$s_email","$from_email","$send_subject","$send_body");
$esql_query = "INSERT INTO trans_boardshowingsemails values ('',?,?,now(),?,?,?,?)";
my $zzaasth = $dbh->do($esql_query, undef, @evariables);
###########################################################
}
}
}
}
}
########## END NOTIFY Owner(s) #####################################
########## NOTIFY LISTING AGENT(S) ###################################
## Listing Agent Information
my $laasth = $dbh->prepare("select mlsnumber,showingtype from trans_boardshowings where showingid = '$data_showingid'");
$laasth->execute;
while (($lagent_mlsnumber,$tb_type) = $laasth->fetchrow_array()) {
my $l2aasth = $dbh->prepare("select agent from trans_board where mlsnumber = '$lagent_mlsnumber' AND (type = 'BSP' or type = 'SP' or type = 'Listing')");
$l2aasth->execute;
while (($l2agent_agent) = $l2aasth->fetchrow_array()) {
my $l3aasth = $dbh->prepare("select firstname,lastname,email from agents where mlsid = '$l2agent_agent' order by mlsid ASC LIMIT 0,1");
$l3aasth->execute;
while (($l3agent_firstname,$l3agent_lastname,$l3agent_email) = $l3aasth->fetchrow_array()) {
open(MAIL,"|/usr/sbin/sendmail -t");
print MAIL "To: $l3agent_email\n";
print MAIL "From: $from_email\n";
print MAIL "Subject: Feedback Received For $tb_type $address_short on $data_appointmentdate from $data_timerange1-$data_timerange2\n";
print MAIL "Content-type: text/html\n\n";
print MAIL qq~
The ORR Home Selling Team - RE/MAX Real Estate Groups
Dear $l3agent_firstname $l3agent_lastname
This notification has been sent from $data_enteredby to notify you the we have received showing feedback. Details are below:
Property Address $address_long
Appointment Type $tb_type
Unfiltered Feedback Received from Showing Agent: $INPUT{comments}
~;
close(MAIL);
$send_la_subject =qq~Feedback Received For $tb_type $address_short on $data_appointmentdate from $data_timerange1-$data_timerange2~;
$send_la_body =qq~
The ORR Home Selling Team - RE/MAX Real Estate Groups
Dear $l3agent_firstname $l3agent_lastname
This notification has been sent from $data_enteredby to notify you the we have received showing feedback. Details are below:
Property Address $address_long
Appointment Type $tb_type
Unfiltered Feedback Received from Showing Agent: $INPUT{comments}
to quickly leave feedback. Your feedback is very much appreciated!
~;
}
else {
$confirmation_showingagent =qq~
Appointment Confirmation Status
Confirmed $data_confirmeddatetime
~;
}
$confirmation_email = "yes";
}
else {
$confirmation = "Not Yet Confirmed -- Please Contact The ORR Team at 765-741-9111 to confirm";
$confirmation_showingagent =qq~
Showing Confirmation Status
Not Yet Confirmed -- We are trying to confirm with seller. We will send you a confirmation email when confirmed
~;
$confirmation_email = "no";
}
my $aaasth = $dbh->prepare("select addressnumber,addressdirection,addressstreet,city,state,zip from trans_board where mlsnumber = '$data_mlsnumber' AND (type = 'BSP' or type = 'SP' or type = 'Listing') LIMIT 0,1");
$aaasth->execute;
while (($l_addressnumber,$l_addressdirection,$l_addressstreet,$l_city,$l_state,$l_zip) = $aaasth->fetchrow_array()) {
$address_short =qq~$l_addressnumber $l_addressdirection $l_addressstreet~;
$address_long =qq~$l_addressnumber $l_addressdirection $l_addressstreet $l_city, $l_state $l_zip~;
}
### EMAIL NOTIFICATIONS OF CONFIRMED NEW SHOWING CONFIRMED2IN !!!!!!!!!
################################## Notify Owner(s) ####### OKAY TO SEND EMAIL IF INSPECTION #######################################
my $oaasth = $dbh->prepare("select mlsnumber,showingtype,email_seller from trans_boardshowings where showingid = '$data_showingid'");
$oaasth->execute;
while (($owner_mlsnumber,$tb_type,$tb_email_seller) = $oaasth->fetchrow_array()) {
if ($tb_email_seller eq "Yes") {
my $o2aasth = $dbh->prepare("select id from trans_board where mlsnumber = '$owner_mlsnumber' AND (type = 'BSP' or type = 'SP' or type = 'Listing')");
$o2aasth->execute;
while (($owner2_id) = $o2aasth->fetchrow_array()) {
my $sbaasth = $dbh->prepare("select contactid from trans_boardtranstocontact WHERE transid = '$owner2_id' AND type = 'Seller'");
$sbaasth->execute;
while (($tempcontactid) = $sbaasth->fetchrow_array()) {
my $sddasth = $dbh->prepare("select id,agent,type,firstname,lastname,address,spouse,city,state,zip,phone1type,phone1number,phone2type,phone2number,phone3type,phone3number,faxnumber,email,emailtwo,notes from trans_boardcontact WHERE id = '$tempcontactid'");
$sddasth->execute;
while (($s_contactid,$s_agent,$s_type,$s_firstname,$s_lastname,$s_address,$s_spouse,$s_city,$s_state,$s_zip,$s_phone1type,$s_phone1number,$s_phone2type,$s_phone2number,$s_phone3type,$s_phone3number,$s_faxnumber,$s_email,$s_emailtwo,$s_notes) = $sddasth->fetchrow_array()) {
if ($s_spouse ne "") {
$s_spouse = " & $s_spouse ";
}
else {
$s_spouse = "";
}
open(MAIL,"|/usr/sbin/sendmail -t");
print MAIL "To: $s_email\n";
unless ($s_emailtwo eq "") {
print MAIL "To: $s_emailtwo\n";
}
print MAIL "From: $from_email\n";
print MAIL "Subject: Confirmed $tb_type For $address_short on $data_appointmentdate from $data_timerange1 - $data_timerange2\n";
print MAIL "Content-type: text/html\n\n";
print MAIL qq~
The ORR Home Selling Team - RE/MAX Real Estate Groups
Dear $s_firstname $s_spouse $s_lastname
This notification has been sent from $INPUT{enteredby} to inform you of the following $tb_type appointment has been confirmed:
Property Address $address_long
Appointment Type $tb_type
Appointment Notes: $data_notes
$tb_type Date
$data_appointmentdate
Appointment Time
$data_timerange1 - $data_timerange2
Confirmation Status
$confirmation
You will automatically receive showing feedback notifications once showing once feedback has been received from showing agent!
The ORR Home Selling Team and RE/MAX Real Estate Groups look forward to serving you. If at anytime you have questions or comments please contact us 765-741-9111.
~;
close(MAIL);
$send_subject =qq~Confirmed $tb_type For $address_short on $data_appointmentdate from $data_timerange1 - $data_timerange2~;
$send_body =qq~
The ORR Home Selling Team - RE/MAX Real Estate Groups
Dear $s_firstname $s_spouse $s_lastname
This notification has been sent from $INPUT{enteredby} to inform you of the following $tb_type appointment has been confirmed:
Property Address $address_long
Appointment Type $tb_type
Appointment Notes: $data_notes
$tb_type Date
$data_appointmentdate
Appointment Time
$data_timerange1 - $data_timerange2
Confirmation Status
$confirmation
You will automatically receive showing feedback notifications once showing once feedback has been received from showing agent!
The ORR Home Selling Team and RE/MAX Real Estate Groups look forward to serving you. If at anytime you have questions or comments please contact us 765-741-9111.
~;
######## RECORD COPY OF EMAIL IN DATABASE #################
@evariables = ("$data_showingid","Owner","$s_email","$from_email","$send_subject","$send_body");
$esql_query = "INSERT INTO trans_boardshowingsemails values ('',?,?,now(),?,?,?,?)";
my $zzaasth = $dbh->do($esql_query, undef, @evariables);
###########################################################
}
}
}
}
}
########## END NOTIFY Owner(s) #####################################
########## NOTIFY LISTING AGENT(S) ################# ALWAYS SEND EMAIL NO MATTER WHAT ##################
## Listing Agent Information
my $laasth = $dbh->prepare("select mlsnumber,showingtype from trans_boardshowings where showingid = '$data_showingid'");
$laasth->execute;
while (($lagent_mlsnumber,$tb_type) = $laasth->fetchrow_array()) {
my $l2aasth = $dbh->prepare("select agent from trans_board where mlsnumber = '$lagent_mlsnumber' AND (type = 'BSP' or type = 'SP' or type = 'Listing')");
$l2aasth->execute;
while (($l2agent_agent) = $l2aasth->fetchrow_array()) {
my $l3aasth = $dbh->prepare("select firstname,lastname,email from agents where mlsid = '$l2agent_agent' order by mlsid ASC LIMIT 0,1");
$l3aasth->execute;
while (($l3agent_firstname,$l3agent_lastname,$l3agent_email) = $l3aasth->fetchrow_array()) {
open(MAIL,"|/usr/sbin/sendmail -t");
print MAIL "To: $l3agent_email\n";
print MAIL "From: $from_email\n";
print MAIL "Subject: Confirmed $tb_type For $address_short on $data_appointmentdate from $data_timerange1-$data_timerange2\n";
print MAIL "Content-type: text/html\n\n";
print MAIL qq~
The ORR Home Selling Team - RE/MAX Real Estate Groups
Dear $l3agent_firstname $l3agent_lastname
This notification has been sent from $INPUT{enteredby} to notify you the following $tb_type appointment for your listing. Details of the $tb_type are below:
You will automatically receive a feedback notification of your showing once feedback has been received from showing agent!
~;
close(MAIL);
$send_la_subject =qq~Confirmed $tb_type For $address_short on $data_appointmentdate from $data_timerange1-$data_timerange2~;
$send_la_body =qq~
The ORR Home Selling Team - RE/MAX Real Estate Groups
Dear $l3agent_firstname $l3agent_lastname
This notification has been sent from $INPUT{enteredby} to notify you the following $tb_type appointment for your listing. Details of the $tb_type are below:
You will automatically receive a feedback notification of your showing once feedback has been received from showing agent!
~;
######## RECORD COPY OF EMAIL IN DATABASE #################
@evariables = ("$data_showingid","ListingAgent","$l3agent_email","$from_email","$send_la_subject","$send_la_body");
$esql_query = "INSERT INTO trans_boardshowingsemails values ('',?,?,now(),?,?,?,?)";
my $zzaasth = $dbh->do($esql_query, undef, @evariables);
###########################################################
}
}
}
########## END NOTIFY LISTING AGENT
########## NOTIFY SHOWING AGENT ### OPTION ADDED NOT TO SEND TO AGENT OR INSPECTOR ###
if ($confirmation_email eq "yes") {
my $laasth = $dbh->prepare("select mlsnumber,showingtype,showingagent,email_agent from trans_boardshowings where showingid = '$data_showingid'");
$laasth->execute;
while (($sagent_mlsnumber,$tb_type,$sagent_showingagent,$sagent_emailagent) = $laasth->fetchrow_array()) {
if ($sagent_emailagent eq "Yes") {
my $baasth = $dbh->prepare("select id,firstname,lastname,email,company from trans_boarddirectory where id = '$sagent_showingagent'");
$baasth->execute;
while (($sagent_directoryid,$sagent_firstname,$sagent_lastname,$sagent_email,$sagent_company) = $baasth->fetchrow_array()) {
open(MAIL,"|/usr/sbin/sendmail -t");
print MAIL "To: $sagent_email\n";
print MAIL "From: $from_email\n";
print MAIL "Subject: Confirmed $tb_type For $address_short on $data_appointmentdate from $data_timerange1-$data_timerange2\n";
print MAIL "Content-type: text/html\n\n";
print MAIL qq~
The ORR Home Selling Team - RE/MAX Real Estate Groups
Dear $sagent_firstname $sagent_lastname
This notification has been sent from $INPUT{enteredby} to notify we have received your $tb_type appointment request. Details of the appointment are below:
~;
close(MAIL);
$send_sa_subject =qq~Confirmed $tb_type For $address_short on $data_appointmentdate from $data_timerange1-$data_timerange2~;
$send_sa_body =qq~
The ORR Home Selling Team - RE/MAX Real Estate Groups
Dear $sagent_firstname $sagent_lastname
This notification has been sent from $INPUT{enteredby} to notify we have received your $tb_type appointment request. Details of the appointment are below:
~;
######## RECORD COPY OF EMAIL IN DATABASE #################
@evariables = ("$data_showingid","ShowingAgent","$sagent_email","$from_email","$send_sa_subject","$send_sa_body");
$esql_query = "INSERT INTO trans_boardshowingsemails values ('',?,?,now(),?,?,?,?)";
my $zzaasth = $dbh->do($esql_query, undef, @evariables);
###########################################################
}
}
}
}
########## END NOTIFY SHOWING AGENT
}
########## END EMAIL NOTIFICATIONS ################################
}
if ($INPUT{enternotes} eq "yes") {
@vvariables = ($INPUT{notes},$INPUT{showingid});
$vsql_query = "update trans_boardshowings set notes = ? where showingid = ?";
my $vaasth = $dbh->do($vsql_query, undef, @vvariables);
}
print qq~
~;
my $aasth = $dbh->prepare("select showingid,mlsnumber,status,enteredby,showingagent,showingtype,DATE_FORMAT(appointmentdate, '%b %D, %Y'),timerange1,timerange2,DATE_FORMAT(confirmeddatetime, '%b %D, %Y at %h\:%i%p'),confirmedwith,notes,confcode,email_seller,email_agent from trans_boardshowings where showingid = '$INPUT{showingid}'");
$aasth->execute;
while (($showingid,$mlsnumber,$status,$enteredby,$showingagent,$showingtype,$appointmentdate,$timerange1,$timerange2,$confirmeddatetime,$confirmedwith,$notes,$confcode,$email_seller,$email_agent) = $aasth->fetchrow_array()) {
if ($confirmedwith eq "") { $confirmedwith = "N/A"; }
if ($confirmeddatetime eq "0000-00-00 00:00:00" || $confirmeddatetime eq "") {
$dconfirmation =qq~
Date Time Confirmed Not Yet Confirmed! Confirmed With Not Yet Confirmed!
Submit Confirmation
~;
}
else {
$dconfirmation =qq~
Date Time Confirmed $confirmeddatetime Confirmed With $confirmedwith
~;
}
print qq~
Showing Details for
~;
#### EXTRACT DATA FROM OTHER TABLES ###
## Showing Agent Information
my $baasth = $dbh->prepare("select id,firstname,lastname,email,company from trans_boarddirectory where id = '$showingagent'");
$baasth->execute;
while (($sagent_directoryid,$sagent_firstname,$sagent_lastname,$sagent_email,$sagent_company) = $baasth->fetchrow_array()) {
$showingagent_directoryid = $sagent_directoryid;
$showingagent_firstname = $sagent_firstname;
$showingagent_lastname = $sagent_lastname;
$showingagent_email = $sagent_email;
$showingagent_company = $sagent_company;
}
## Listing Agent Information
my $laasth = $dbh->prepare("select mlsnumber from trans_boardshowings where showingid = '$INPUT{showingid}'");
$laasth->execute;
while (($lagent_mlsnumber) = $laasth->fetchrow_array()) {
my $l2aasth = $dbh->prepare("select agent from trans_board where mlsnumber = '$lagent_mlsnumber' AND (type = 'BSP' or type = 'SP' or type = 'Listing')");
$l2aasth->execute;
while (($l2agent_agent) = $l2aasth->fetchrow_array()) {
my $l3aasth = $dbh->prepare("select firstname,lastname,email from agents where mlsid = '$l2agent_agent' order by lastname desc LIMIT 0,1");
$l3aasth->execute;
while (($l3agent_firstname,$l3agent_lastname,$l3agent_email) = $l3aasth->fetchrow_array()) {
$listingagent_firstname = $l3agent_firstname;
$listingagent_lastname = $l3agent_lastname;
$listingagent_email = $l3agent_email;
}
}
}
## Owners Information
my $oaasth = $dbh->prepare("select mlsnumber from trans_boardshowings where showingid = '$INPUT{showingid}'");
$oaasth->execute;
while (($owner_mlsnumber) = $oaasth->fetchrow_array()) {
my $o2aasth = $dbh->prepare("select id from trans_board where mlsnumber = '$owner_mlsnumber' AND (type = 'BSP' or type = 'SP' or type = 'Listing')");
$o2aasth->execute;
while (($owner2_id) = $o2aasth->fetchrow_array()) {
my $sbaasth = $dbh->prepare("select contactid from trans_boardtranstocontact WHERE transid = '$owner2_id' AND type = 'Seller'");
$sbaasth->execute;
while (($tempcontactid) = $sbaasth->fetchrow_array()) {
my $sddasth = $dbh->prepare("select id,agent,type,firstname,lastname,address,spouse,city,state,zip,phone1type,phone1number,phone2type,phone2number,phone3type,phone3number,faxnumber,email,emailtwo,notes from trans_boardcontact WHERE id = '$tempcontactid'");
$sddasth->execute;
while (($s_contactid,$s_agent,$s_type,$s_firstname,$s_lastname,$s_address,$s_spouse,$s_city,$s_state,$s_zip,$s_phone1type,$s_phone1number,$s_phone2type,$s_phone2number,$s_phone3type,$s_phone3number,$s_faxnumber,$s_email,$s_emailtwo,$s_notes) = $sddasth->fetchrow_array()) {
if ($s_spouse ne "") {
$s_spouse = " & $s_spouse ";
}
else {
$s_spouse = "";
}
$owner_info.=qq~
$s_firstname $s_spouse $s_lastname
$s_address $s_city, $s_state, $s_zip
$s_phone1type
$s_phone1number
$s_phone2type
$s_phone2number
$s_phone3type
$s_phone3number
E-Mail
$s_email
E-Mail #2
$s_emailtwo
~;
}
}
}
}
my $zaasth = $dbh->prepare("select addressnumber,addressdirection,addressstreet,city,state,zip from trans_board where mlsnumber = '$mlsnumber' AND (type = 'BSP' or type = 'SP' or type = 'Listing') LIMIT 0,1");
$zaasth->execute;
while (($addressnumber,$addressdirection,$addressstreet,$city,$state,$zip) = $zaasth->fetchrow_array()) {
print qq~
$addressnumber $addressdirection $addressstreet $city, $state $zip
~;
}
print qq~
~;
if (-e "/home/muncieml/public_html/images/photos/thumbnail/meiar$INPUT{mlsnumber}\.jpg") {
print qq~
~;
}
print qq~
Status
$status Appointment Type
$showingtype Entered By
$enteredby Date
$appointmentdate Showing Time
$timerange1 - $timerange2 Showing Confirmation Number
$confcode
$dconfirmation
MLS Number
$mlsnumber Showing Agent $showingagent_firstname $showingagent_lastname $showingagent_company Listing Agent
$listingagent_firstname $listingagent_lastname
ORRTeam with RE/MAX E-Mail Notifications
Seller $email_seller
Agent $email_agent
Owner E-Mail Notifications
~;
my $oaasth = $dbh->prepare("select emailid,emailto,subject from trans_boardshowingsemails where showingid = '$showingid' AND emailtype = 'Owner'");
$oaasth->execute;
while (($o_emailid,$o_emailto,$o_subject) = $oaasth->fetchrow_array()) {
$o_found = "yes";
print qq~
$o_emailto - $o_subject
~;
}
unless ($o_found eq "yes") {
print qq~
None Sent Yet
~;
}
print qq~
Showing Agent E-Mail Notifications
~;
my $slaasth = $dbh->prepare("select emailid,emailto,subject from trans_boardshowingsemails where showingid = '$showingid' AND emailtype = 'ShowingAgent'");
$slaasth->execute;
while (($sl_emailid,$sl_emailto,$sl_subject) = $slaasth->fetchrow_array()) {
$sl_found = "yes";
print qq~
$sl_emailto - $sl_subject
~;
}
unless ($sl_found eq "yes") {
print qq~
None Sent Yet
~;
}
print qq~
Listing Agent E-Mail Notifications
~;
my $elaasth = $dbh->prepare("select emailid,emailto,subject from trans_boardshowingsemails where showingid = '$showingid' AND emailtype = 'ListingAgent'");
$elaasth->execute;
while (($el_emailid,$el_emailto,$el_subject) = $elaasth->fetchrow_array()) {
$el_found = "yes";
print qq~
$el_emailto - $el_subject
~;
}
unless ($el_found eq "yes") {
print qq~
None Sent Yet
~;
}
print qq~
Owner Information
$owner_info Showing Instructions
~;
my $azaasth = $dbh->prepare("select showinginstructions from trans_board where mlsnumber = '$INPUT{mlsnumber}' AND (status = 'Active' or status = 'Pending')");
$azaasth->execute;
while (($showinginstructions) = $azaasth->fetchrow_array()) {
print qq~
$showinginstructions
~;
}
print qq~
Showing Notes (Viewable By Client/Listing and Showing Agents)
~;
if ($notes eq "") {
print qq~
~;
}
else {
print qq~
$notes
~;
}
print qq~
Feedback from Showing Agent
~;
my $feedasth = $dbh->prepare("select sid,comments from trans_boardfeedback where sid = '$INPUT{showingid}'");
$feedasth->execute;
while (($showing_sid,$showing_comments) = $feedasth->fetchrow_array()) {
print qq~
to quickly leave feedback. Your feedback is very much appreciated!
~;
}
else {
$confirmation_showingagent =qq~
Appointment Confirmation Status
Confirmed $data_confirmeddatetime
~;
}
$confirmation_email = "yes";
}
else {
$confirmation = "Not Yet Confirmed -- Please Contact The ORR Team at 765-741-9111 to confirm!";
$confirmation_email = "no";
}
my $aaasth = $dbh->prepare("select addressnumber,addressdirection,addressstreet,city,state,zip from trans_board where mlsnumber = '$data_mlsnumber' AND (type = 'BSP' or type = 'SP' or type = 'Listing') LIMIT 0,1");
$aaasth->execute;
while (($l_addressnumber,$l_addressdirection,$l_addressstreet,$l_city,$l_state,$l_zip) = $aaasth->fetchrow_array()) {
$address_short =qq~$l_addressnumber $l_addressdirection $l_addressstreet~;
$address_long =qq~$l_addressnumber $l_addressdirection $l_addressstreet $l_city, $l_state $l_zip~;
}
### EMAIL NOTIFICATIONS OF NEW SHOWING!!!!!!!!!
################################## Notify Owner(s) ####### OKAY TO SEND EMAIL IF INSPECTION #######################################
my $oaasth = $dbh->prepare("select mlsnumber,showingtype,email_seller from trans_boardshowings where showingid = '$data_showingid'");
$oaasth->execute;
while (($owner_mlsnumber,$tb_type,$tb_email_seller) = $oaasth->fetchrow_array()) {
if ($tb_email_seller eq "Yes") {
my $o2aasth = $dbh->prepare("select id from trans_board where mlsnumber = '$owner_mlsnumber' AND (type = 'BSP' or type = 'SP' or type = 'Listing')");
$o2aasth->execute;
while (($owner2_id) = $o2aasth->fetchrow_array()) {
my $sbaasth = $dbh->prepare("select contactid from trans_boardtranstocontact WHERE transid = '$owner2_id' AND type = 'Seller'");
$sbaasth->execute;
while (($tempcontactid) = $sbaasth->fetchrow_array()) {
my $sddasth = $dbh->prepare("select id,agent,type,firstname,lastname,address,spouse,city,state,zip,phone1type,phone1number,phone2type,phone2number,phone3type,phone3number,faxnumber,email,emailtwo,notes from trans_boardcontact WHERE id = '$tempcontactid'");
$sddasth->execute;
while (($s_contactid,$s_agent,$s_type,$s_firstname,$s_lastname,$s_address,$s_spouse,$s_city,$s_state,$s_zip,$s_phone1type,$s_phone1number,$s_phone2type,$s_phone2number,$s_phone3type,$s_phone3number,$s_faxnumber,$s_email,$s_emailtwo,$s_notes) = $sddasth->fetchrow_array()) {
if ($s_spouse ne "") {
$s_spouse = " & $s_spouse ";
}
else {
$s_spouse = "";
}
open(MAIL,"|/usr/sbin/sendmail -t");
print MAIL "To: $s_email\n";
unless ($s_emailtwo eq "") {
print MAIL "To: $s_emailtwo\n";
}
print MAIL "From: $from_email\n";
print MAIL "Subject: $tb_type Appointment Requested For $address_short on $data_appointmentdate from $data_timerange1 - $data_timerange2\n";
print MAIL "Content-type: text/html\n\n";
print MAIL qq~
The ORR Home Selling Team - RE/MAX Real Estate Groups
Dear $s_firstname $s_spouse $s_lastname
This notification has been sent from $INPUT{enteredby} to inform you of the following appointment:
Property Address $address_long
Appointment Type $tb_type
Appointment Notes: $data_notes
$tb_type Date
$data_appointmentdate
Appointment Time
$data_timerange1 - $data_timerange2
Confirmation Status
$confirmation
You will automatically receive showing feedback notifications once showing once feedback has been received from showing agent!
The ORR Home Selling Team and RE/MAX Real Estate Groups look forward to serving you. If at anytime you have questions or comments please contact us 765-741-9111.
~;
close(MAIL);
$send_subject =qq~$tb_type Appointment Requested For $address_short on $data_appointmentdate from $data_timerange1 - $data_timerange2~;
$send_body =qq~
The ORR Home Selling Team - RE/MAX Real Estate Groups
Dear $s_firstname $s_spouse $s_lastname
This notification has been sent from $INPUT{enteredby} to inform you of the following appointment:
Property Address $address_long
Appointment Type $tb_type
Appointment Notes: $data_notes
$tb_type Date
$data_appointmentdate
Appointment Time
$data_timerange1 - $data_timerange2
Confirmation Status
$confirmation
You will automatically receive showing feedback notifications once showing once feedback has been received from showing agent!
The ORR Home Selling Team and RE/MAX Real Estate Groups look forward to serving you. If at anytime you have questions or comments please contact us 765-741-9111.
~;
######## RECORD COPY OF EMAIL IN DATABASE #################
@evariables = ("$data_showingid","Owner","$s_email","$from_email","$send_subject","$send_body");
$esql_query = "INSERT INTO trans_boardshowingsemails values ('',?,?,now(),?,?,?,?)";
my $zzaasth = $dbh->do($esql_query, undef, @evariables);
###########################################################
}
}
}
}
}
########## END NOTIFY Owner(s) #####################################
########## NOTIFY LISTING AGENT(S) ################# ALWAYS SEND EMAIL NO MATTER WHAT ##################
## Listing Agent Information
my $laasth = $dbh->prepare("select mlsnumber,showingtype from trans_boardshowings where showingid = '$data_showingid'");
$laasth->execute;
while (($lagent_mlsnumber,$tb_type) = $laasth->fetchrow_array()) {
my $l2aasth = $dbh->prepare("select agent from trans_board where mlsnumber = '$lagent_mlsnumber' AND (type = 'BSP' or type = 'SP' or type = 'Listing')");
$l2aasth->execute;
while (($l2agent_agent) = $l2aasth->fetchrow_array()) {
my $l3aasth = $dbh->prepare("select firstname,lastname,email from agents where mlsid = '$l2agent_agent' order by mlsid ASC LIMIT 0,1");
$l3aasth->execute;
while (($l3agent_firstname,$l3agent_lastname,$l3agent_email) = $l3aasth->fetchrow_array()) {
open(MAIL,"|/usr/sbin/sendmail -t");
print MAIL "To: $l3agent_email\n";
print MAIL "From: $from_email\n";
print MAIL "Subject: $tb_type Appointment Requested For $address_short on $data_appointmentdate from $data_timerange1-$data_timerange2\n";
print MAIL "Content-type: text/html\n\n";
print MAIL qq~
The ORR Home Selling Team - RE/MAX Real Estate Groups
Dear $l3agent_firstname $l3agent_lastname
This notification has been sent from $INPUT{enteredby} to notify you the following $tb_type appointment for your listing. Details of the $tb_type are below:
You will automatically receive a feedback notification of your showing once feedback has been received from showing agent!
~;
close(MAIL);
$send_subject =qq~$tb_type Appointment Requested For $address_short on $data_appointmentdate from $data_timerange1-$data_timerange2~;
$send_body =qq~
The ORR Home Selling Team - RE/MAX Real Estate Groups
Dear $l3agent_firstname $l3agent_lastname
This notification has been sent from $INPUT{enteredby} to notify you the following $tb_type appointment for your listing. Details of the $tb_type are below:
You will automatically receive a feedback notification of your showing once feedback has been received from showing agent!
~;
######## RECORD COPY OF EMAIL IN DATABASE #################
@evariables = ("$data_showingid","ListingAgent","$l3agent_email","$from_email","$send_subject","$send_body");
$esql_query = "INSERT INTO trans_boardshowingsemails values ('',?,?,now(),?,?,?,?)";
my $zzaasth = $dbh->do($esql_query, undef, @evariables);
###########################################################
}
}
}
########## END NOTIFY LISTING AGENT
########## NOTIFY SHOWING AGENT ### OPTION ADDED NOT TO SEND TO AGENT OR INSPECTOR ###
if ($confirmation_email eq "yes") {
my $laasth = $dbh->prepare("select mlsnumber,showingtype,showingagent,email_agent from trans_boardshowings where showingid = '$data_showingid'");
$laasth->execute;
while (($sagent_mlsnumber,$tb_type,$sagent_showingagent,$sagent_emailagent) = $laasth->fetchrow_array()) {
if ($sagent_emailagent eq "Yes") {
my $baasth = $dbh->prepare("select id,firstname,lastname,email,company from trans_boarddirectory where id = '$sagent_showingagent'");
$baasth->execute;
while (($sagent_directoryid,$sagent_firstname,$sagent_lastname,$sagent_email,$sagent_company) = $baasth->fetchrow_array()) {
open(MAIL,"|/usr/sbin/sendmail -t");
print MAIL "To: $sagent_email\n";
print MAIL "From: $from_email\n";
print MAIL "Subject: $tb_type Appointment For $address_short on $data_appointmentdate from $data_timerange1-$data_timerange2\n";
print MAIL "Content-type: text/html\n\n";
print MAIL qq~
The ORR Home Selling Team - RE/MAX Real Estate Groups
Dear $sagent_firstname $sagent_lastname
This notification has been sent from $INPUT{enteredby} to notify we have received your $tb_type appointment request. Details of the appointment are below:
~;
close(MAIL);
$send_subject =qq~$tb_type Appointment For $address_short on $data_appointmentdate from $data_timerange1-$data_timerange2~;
$send_body =qq~
The ORR Home Selling Team - RE/MAX Real Estate Groups
Dear $sagent_firstname $sagent_lastname
This notification has been sent from $INPUT{enteredby} to notify we have received your $tb_type appointment request. Details of the appointment are below:
~;
######## RECORD COPY OF EMAIL IN DATABASE #################
@evariables = ("$data_showingid","ShowingAgent","$sagent_email","$from_email","$send_subject","$send_body");
$esql_query = "INSERT INTO trans_boardshowingsemails values ('',?,?,now(),?,?,?,?)";
my $zzaasth = $dbh->do($esql_query, undef, @evariables);
###########################################################
}
}
}
}
########## END NOTIFY SHOWING AGENT
}
########## END EMAIL NOTIFICATIONS ################################
print qq~
Entering into Datebase
Please wait, Completing Showing Request
~;
}
sub quickload_2 {
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 $gbasth = $dbh->prepare("select now()");
$gbasth->execute;
$today_is_datetime = $gbasth->fetchrow_array();
($today_date,$today_time) = split(/ /,$today_is_datetime);
($t_year,$t_month,$t_day) = split(/\-/,$today_date);
my $zaasth = $dbh->prepare("select addressnumber,addressdirection,addressstreet,showinginstructions from trans_board where mlsnumber = '$INPUT{mlsnumber}' AND (status = 'Active' or status = 'Pending')");
$zaasth->execute;
while (($addressnumber,$addressdirection,$addressstreet,$showinginstructions) = $zaasth->fetchrow_array()) {
$realaddress = "$addressnumber $addressdirection $addressstreet";
$realshowinginstructions = $showinginstructions;
}
## Owners Information
my $o2aasth = $dbh->prepare("select id from trans_board where mlsnumber = '$INPUT{mlsnumber}' AND (type = 'BSP' or type = 'SP' or type = 'Listing')");
$o2aasth->execute;
while (($owner2_id) = $o2aasth->fetchrow_array()) {
my $sbaasth = $dbh->prepare("select contactid from trans_boardtranstocontact WHERE transid = '$owner2_id' AND type = 'Seller'");
$sbaasth->execute;
while (($tempcontactid) = $sbaasth->fetchrow_array()) {
my $sddasth = $dbh->prepare("select id,agent,type,firstname,lastname,address,spouse,city,state,zip,phone1type,phone1number,phone2type,phone2number,phone3type,phone3number,faxnumber,email,emailtwo,notes from trans_boardcontact WHERE id = '$tempcontactid'");
$sddasth->execute;
while (($s_contactid,$s_agent,$s_type,$s_firstname,$s_lastname,$s_address,$s_spouse,$s_city,$s_state,$s_zip,$s_phone1type,$s_phone1number,$s_phone2type,$s_phone2number,$s_phone3type,$s_phone3number,$s_faxnumber,$s_email,$s_emailtwo,$s_notes) = $sddasth->fetchrow_array()) {
if ($s_spouse ne "") {
$s_spouse = " & $s_spouse ";
}
else {
$s_spouse = "";
}
$owner_info.=qq~
$s_firstname $s_spouse $s_lastname
$s_address $s_city, $s_state, $s_zip
$s_phone1type
$s_phone1number
$s_phone2type
$s_phone2number
$s_phone3type
$s_phone3number
E-Mail
$s_email
E-Mail #2
$s_emailtwo
~;
}
}
}
print qq~
Showing Administration -The ORR Team - RE/MAX Real Estate Groups
~;
&header;
print qq~
manage orrteam property showings and company directory in this area
~;
print qq~
~;
&footer;
}
sub check_trans {
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 $zaasth = $dbh->prepare("select id from trans_board where mlsnumber = '$INPUT{mlsnumber}' AND (status = 'Active' or status = 'Pending')");
$zaasth->execute;
while (($mlsid) = $zaasth->fetchrow_array()) {
$check = $mlsid;
}
if ($check && $INPUT{mlsnumber}) {
my $aasth = $dbh->prepare("select id,type,mlsnumber,addressnumber,addressdirection,addressstreet,city,state,zip from trans_board where mlsnumber = '$INPUT{mlsnumber}' AND (status = 'Active' or status = 'Pending')");
$aasth->execute;
while (($id,$type,$mlsnumber,$addressnumber,$addressdirection,$addressstreet,$city,$state,$zip) = $aasth->fetchrow_array()) {
$new_addressstreet = ucfirst(lc($addressstreet));
print qq~
~;
}
}
elsif ($INPUT{mlsnumber}) {
print qq~
MLS # Not In System
~;
}
else {
print qq~
Enter MLS # To Check
~;
}
}
sub quickload_1 {
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 $abasth = $dbh->prepare("select company from trans_boarddirectory GROUP BY company ASC");
$abasth->execute;
while (($company) = $abasth->fetchrow_array()) {
$buildcompany .= "";
}
print qq~
Showing Administration -The ORR Team - RE/MAX Real Estate Groups
~;
&header;
print qq~
manage orrteam property showings and company directory in this area
~;
unless ($ccfound eq "yes") {
print qq~
No Contacts Found, Click on the first letter in the alphabet above that corresponds with your contacts lastname to try another search. If your contact is not listed you can create a new contact by clicking on the link above.
~;
}
}
else {
print qq~
You may click on the first letter in the alphabet above that corresponds with your contacts lastname to search your contacts.
~;
}
print qq~
Choose Property To Be Shown
~;
unless ($INPUT{loadcontact} eq "yes") {
$disabled = "disabled";
$message = "You need to search a contact above then load them before finding a property ";
}
else {
$message = "$INPUT{name} has been loaded ";
}
print qq~
$message
~;
&footer;
}
sub 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;
my $gbasth = $dbh->prepare("select now()");
$gbasth->execute;
$today_is_datetime = $gbasth->fetchrow_array();
($today_date,$today_time) = split(/ /,$today_is_datetime);
($t_year,$t_month,$t_day) = split(/\-/,$today_date);
$sqlstartdate = "$t_year-$t_month=$t_day";
if ($INPUT{startdate}) {
$findsdate = "$INPUT{startdate}";
}
else {
my $dateaasth = $dbh->prepare("select DATE_FORMAT(DATE_ADD(now(), INTERVAL -14 DAY), '%Y-%m-%d')");
$dateaasth->execute;
while (($newbackdate) = $dateaasth->fetchrow_array()) {
$findsdate = "$newbackdate";
}
}
if ($INPUT{enddate}) {
$findldate = "$INPUT{enddate}";
}
else {
my $datebasth = $dbh->prepare("select DATE_FORMAT(DATE_ADD(now(), INTERVAL 14 DAY), '%Y-%m-%d')");
$datebasth->execute;
while (($newstartdate) = $datebasth->fetchrow_array()) {
$findldate = "$newstartdate";
}
}
$viewtype = $INPUT{viewtype};
if ($INPUT{changestatus} eq "yes") {
if ($INPUT{status} eq "Delete") {
@dvariables = ($INPUT{showingid});
$sql_query = "DELETE FROM trans_boardshowings WHERE showingid = ?";
my $aasth = $dbh->do($sql_query, undef, @dvariables);
$sql_query2 = "DELETE FROM trans_boardshowingsemails WHERE showingid = ?";
my $basth = $dbh->do($sql_query2, undef, @dvariables);
}
else {
@svariables = ($INPUT{status},$INPUT{showingid});
$ssql_query = "update trans_boardshowings set status = ? where showingid = ?";
my $saasth = $dbh->do($ssql_query, undef, @svariables);
##### NOTIFY SELLER AND LISTING AGENT OF CANCELLED SHOWING HERE #####
if ($INPUT{status} eq "Cancelled") {
my $idaasth = $dbh->prepare("select showingid,mlsnumber,status,enteredby,showingagent,showingtype,DATE_FORMAT(appointmentdate, '%b %D, %Y'),timerange1,timerange2,DATE_FORMAT(confirmeddatetime, '%b %D, %Y at %h\:%i%p'),confirmedwith,notes,confcode,email_seller,email_agent from trans_boardshowings WHERE showingid = '$INPUT{showingid}'");
$idaasth->execute;
while (($data_showingid,$data_mlsnumber,$data_status,$data_enteredby,$data_showingagent,$data_showingtype,$data_appointmentdate,$data_timerange1,$data_timerange2,$data_confirmeddatetime,$data_confirmedwith,$data_notes,$data_confcode,$data_emailseller,$data_emailagent) = $idaasth->fetchrow_array()) {
my $sagentaasth = $dbh->prepare("select firstname,lastname,company from trans_boarddirectory where id = '$INPUT{showingid}'");
$sagentaasth->execute;
while (($sagent_firstname,$sagent_lastname,$sagent_company) = $sagentaasth->fetchrow_array()) {
$showingagent_firstname = $sagent_firstname;
$showingagent_lastname = $sagent_lastname;
$showingagent_company = $sagent_company;
}
my $aaasth = $dbh->prepare("select addressnumber,addressdirection,addressstreet,city,state,zip from trans_board where mlsnumber = '$data_mlsnumber' AND (type = 'BSP' or type = 'SP' or type = 'Listing') LIMIT 0,1");
$aaasth->execute;
while (($l_addressnumber,$l_addressdirection,$l_addressstreet,$l_city,$l_state,$l_zip) = $aaasth->fetchrow_array()) {
$address_short =qq~$l_addressnumber $l_addressdirection $l_addressstreet~;
$address_long =qq~$l_addressnumber $l_addressdirection $l_addressstreet $l_city, $l_state $l_zip~;
}
################################## Notify Owner(s) ####### OKAY TO SEND EMAIL IF INSPECTION #######################################
my $oaasth = $dbh->prepare("select mlsnumber,showingtype,email_seller from trans_boardshowings where showingid = '$INPUT{showingid}'");
$oaasth->execute;
while (($owner_mlsnumber,$tb_type,$tb_email_seller) = $oaasth->fetchrow_array()) {
if ($tb_email_seller eq "Yes") {
my $o2aasth = $dbh->prepare("select id from trans_board where mlsnumber = '$owner_mlsnumber' AND (type = 'BSP' or type = 'SP' or type = 'Listing')");
$o2aasth->execute;
while (($owner2_id) = $o2aasth->fetchrow_array()) {
my $sbaasth = $dbh->prepare("select contactid from trans_boardtranstocontact WHERE transid = '$owner2_id' AND type = 'Seller'");
$sbaasth->execute;
while (($tempcontactid) = $sbaasth->fetchrow_array()) {
my $sddasth = $dbh->prepare("select id,agent,type,firstname,lastname,address,spouse,city,state,zip,phone1type,phone1number,phone2type,phone2number,phone3type,phone3number,faxnumber,email,emailtwo,notes from trans_boardcontact WHERE id = '$tempcontactid'");
$sddasth->execute;
while (($s_contactid,$s_agent,$s_type,$s_firstname,$s_lastname,$s_address,$s_spouse,$s_city,$s_state,$s_zip,$s_phone1type,$s_phone1number,$s_phone2type,$s_phone2number,$s_phone3type,$s_phone3number,$s_faxnumber,$s_email,$s_emailtwo,$s_notes) = $sddasth->fetchrow_array()) {
if ($s_spouse ne "") {
$s_spouse = " & $s_spouse ";
}
else {
$s_spouse = "";
}
$from_email = "Showings\@ORRHomes.com";
$showpicture = &image_format("full","$owner_mlsnumber");
if ($data_notes eq "") { $data_notes = "None Noted"; }
open(MAIL,"|/usr/sbin/sendmail -t");
print MAIL "To: $s_email\n";
unless ($s_emailtwo eq "") {
print MAIL "To: $s_emailtwo\n";
}
print MAIL "From: $from_email\n";
print MAIL "Subject: Cancelled $tb_type Appointment For $address_short on $data_appointmentdate from $data_timerange1 - $data_timerange2\n";
print MAIL "Content-type: text/html\n\n";
print MAIL qq~
The ORR Home Selling Team - RE/MAX Real Estate Groups
Dear $s_firstname $s_spouse $s_lastname
This notification has been sent from $INPUT{enteredby} to inform you of the following appointment has been cancelled:
Property Address $address_long
Appointment Type $tb_type
Reason For Cancellation / Showing Notes: $data_notes
$tb_type Date
$data_appointmentdate
Showing Time
$data_timerange1 - $data_timerange2
The ORR Home Selling Team and RE/MAX Real Estate Groups look forward to serving you. If at anytime you have questions or comments please contact us 765-741-9111.
~;
close(MAIL);
$send_subject =qq~Cancelled $tb_type Appointment For $address_short on $data_appointmentdate from $data_timerange1 - $data_timerange2~;
$send_body =qq~
The ORR Home Selling Team - RE/MAX Real Estate Groups
Dear $s_firstname $s_spouse $s_lastname
This notification has been sent from $INPUT{enteredby} to inform you of the following appointment has been cancelled:
Property Address $address_long
Appointment Type $tb_type
Reason For Cancellation / Showing Notes: $data_notes
$tb_type Date
$data_appointmentdate
Showing Time
$data_timerange1 - $data_timerange2
The ORR Home Selling Team and RE/MAX Real Estate Groups look forward to serving you. If at anytime you have questions or comments please contact us 765-741-9111.
~;
######## RECORD COPY OF EMAIL IN DATABASE #################
@evariables = ("$INPUT{showingid}","Owner","$s_email","$from_email","$send_subject","$send_body");
$esql_query = "INSERT INTO trans_boardshowingsemails values ('',?,?,now(),?,?,?,?)";
my $zzaasth = $dbh->do($esql_query, undef, @evariables);
###########################################################
}
}
}
}
}
#END SELLER NOTIFICATIONS
########## NOTIFY LISTING AGENT(S) ################# ALWAYS SEND EMAIL NO MATTER WHAT ##################
## Listing Agent Information
my $laasth = $dbh->prepare("select mlsnumber,showingtype from trans_boardshowings where showingid = '$INPUT{showingid}'");
$laasth->execute;
while (($lagent_mlsnumber,$tb_type) = $laasth->fetchrow_array()) {
my $l2aasth = $dbh->prepare("select agent from trans_board where mlsnumber = '$lagent_mlsnumber' AND (type = 'BSP' or type = 'SP' or type = 'Listing')");
$l2aasth->execute;
while (($l2agent_agent) = $l2aasth->fetchrow_array()) {
my $l3aasth = $dbh->prepare("select firstname,lastname,email from agents where mlsid = '$l2agent_agent' order by mlsid ASC LIMIT 0,1");
$l3aasth->execute;
while (($l3agent_firstname,$l3agent_lastname,$l3agent_email) = $l3aasth->fetchrow_array()) {
my $sagentaasth = $dbh->prepare("select firstname,lastname,company from trans_boarddirectory where id = '$data_showingagent'");
$sagentaasth->execute;
while (($sagent_firstname,$sagent_lastname,$sagent_company) = $sagentaasth->fetchrow_array()) {
$showingagent_firstname = $sagent_firstname;
$showingagent_lastname = $sagent_lastname;
$showingagent_company = $sagent_company;
}
$from_email = "Showings\@ORRHomes.com";
$showpicture = &image_format("full","$lagent_mlsnumber");
if ($data_notes eq "") { $data_notes = "None Noted"; }
open(MAIL,"|/usr/sbin/sendmail -t");
print MAIL "To: $l3agent_email\n";
print MAIL "From: $from_email\n";
print MAIL "Subject: Cancelled $tb_type Appointment For $address_short on $data_appointmentdate from $data_timerange1-$data_timerange2\n";
print MAIL "Content-type: text/html\n\n";
print MAIL qq~
The ORR Home Selling Team - RE/MAX Real Estate Groups
Dear $l3agent_firstname $l3agent_lastname
This notification has been sent to notify you the following $tb_type appointment for your listing has been cancelled. Details of the cancelled appointment are below:
Property Address $address_long
Appointment Type $tb_type
Reason For Cancellation / Showing Notes: $data_notes
The ORR Home Selling Team and RE/MAX Real Estate Groups look forward to serving you. If at anytime you have questions or comments please contact us 765-741-9111.
~;
close(MAIL);
$send_subject =qq~Cancelled $tb_type Appointment For $address_short on $data_appointmentdate from $data_timerange1-$data_timerange2~;
$send_body =qq~
The ORR Home Selling Team - RE/MAX Real Estate Groups
Dear $l3agent_firstname $l3agent_lastname
This notification has been sent to notify you the following $tb_type appointment for your listing has been cancelled. Details of the cancelled appointment are below:
Property Address $address_long
Appointment Type $tb_type
Reason For Cancellation / Showing Notes: $data_notes
The ORR Home Selling Team and RE/MAX Real Estate Groups look forward to serving you. If at anytime you have questions or comments please contact us 765-741-9111.
~;
######## RECORD COPY OF EMAIL IN DATABASE #################
@evariables = ("$data_showingid","ListingAgent","$l3agent_email","$from_email","$send_subject","$send_body");
$esql_query = "INSERT INTO trans_boardshowingsemails values ('',?,?,now(),?,?,?,?)";
my $zzaasth = $dbh->do($esql_query, undef, @evariables);
###########################################################
}
}
}
########## END NOTIFY LISTING AGENT
#### DO NOT REMOVE ENDINGS
}
}
}
}
if ($INPUT{viewtype} eq "nofeedback") {
$viewtypeoption = "";
$viewtypeoption .= "";
my $pasth = $dbh->prepare("select sid from trans_boardfeedback group by sid");
$pasth->execute;
my $temp_add_sql;
while (($ret_sid) = $pasth->fetchrow_array()) {
$temp_add_sql .= qq~showingid != $ret_sid AND ~;
}
$temp_add_sql =~ s/ AND $//i;
$viewtypesql = "AND ($temp_add_sql) And (status != 'Cancelled' and status != 'Completed')";
}
elsif ($INPUT{viewtype} eq "Open") {
$viewtypeoption = "";
$viewtypeoption .= "";
$viewtypesql = "AND status = '$viewtype'";
}
elsif ($INPUT{viewtype} eq "notconfirmed") {
$viewtypeoption = "";
$viewtypeoption .= "";
$viewtypesql = "AND confirmeddatetime = '0000-00-00 00:00:00' And (status != 'Cancelled' and status != 'Completed')";
}
elsif ($INPUT{viewtype} eq "Cancelled") {
$viewtypeoption = "";
$viewtypeoption .= "";
$viewtypesql = "AND status = '$viewtype'";
}
elsif ($INPUT{viewtype} eq "Completed") {
$viewtypeoption = "";
$viewtypeoption .= "";
$viewtypesql = "AND status = '$viewtype'";
}
elsif ($INPUT{viewtype} eq "all") {
$viewtypeoption = "";
$viewtypeoption .= "";
$viewtypesql = "";
}
else {
$viewtypeoption = "";
$viewtypeoption .= "";
$viewtypesql = "AND status = 'Open'";
}
print qq~
Showing Administration -The ORR Team - RE/MAX Real Estate Groups
~;
&header;
print qq~
manage orrteam property showings and company directory in this area
Setup A Quick Showing
![\"No](\"images/trans_sys/button_yield.gif\")
![\"Confirmed](\"images/trans_sys/button_check.gif\")