#!/usr/bin/perl require "config.idx"; #%bad_agentnames_last = ( # 'Inactive Agent' => 1, # 'Broker-comps' => 1, # 'Inactive Sec' => 1, # 'Subdivision' => 1, # 'Office' => 1, # 'Information' => 1 # ); $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; } } } &checklogin; if ($INPUT{action} eq "directory_view") { &directory_view; } elsif ($INPUT{action} eq "directory_add") { &directory_add; } elsif ($INPUT{action} eq "directory_adddo") { &directory_adddo; } elsif ($INPUT{action} eq "directory_edit") { &directory_edit; } elsif ($INPUT{action} eq "directory_editdo") { &directory_editdo; } elsif ($INPUT{action} eq "directory_delete") { &directory_delete; } elsif ($INPUT{action} eq "listnew_view") { &listnew_view; } elsif ($INPUT{action} eq "listnew_save") { &listnew_save; } elsif ($INPUT{action} eq "list_old") { &list_old; } else { &main; } sub list_old { 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; $total_new_contacts_found = 0; print qq~
~; my $gasth = $dbh->prepare("SELECT * FROM mlsusers"); $gasth->execute(); while ($db_agent = $gasth->fetchrow_hashref()) { $needs_updated = 0; %mls_agent = %$db_agent; my $kasth = $dbh->prepare("SELECT * FROM organizations WHERE officeid=?"); $kasth->execute($mls_agent{officeid}); $db_office = $kasth->fetchrow_hashref(); %mls_office = %$db_office; my $tasth = $dbh->prepare("SELECT * FROM trans_boarddirectory WHERE firstname = ? AND lastname = ? LIMIT 1"); $tasth->execute($mls_agent{firstname},$mls_agent{lastname}); $db_directory = $tasth->fetchrow_hashref(); %dir_agent = %$db_directory; if ($mls_office{officename} ne $dir_agent{company}) { $needs_updated = 1; } $mls_agent{phone} =~ s/\s+$//; if ($mls_agent{phone} ne $dir_agent{agent1phone}) { $needs_updated = 1; } if ($needs_updated eq "1") { print qq~ EAGLE Directory: "$dir_agent{firstname}" "$dir_agent{lastname}" "$dir_agent{agent1phone}" "$dir_agent{company}"
MEIAR Directory: "$mls_agent{firstname}" "$mls_agent{lastname}" "$mls_agent{phone}" "$mls_office{officename}"

~; } ## etc. } print qq~

~; print qq~

Found $total_new_contacts_found total new contacts~; } sub directory_delete { 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; if ($INPUT{do} eq "yes") { my $aasth = $dbh->do("DELETE FROM trans_boarddirectory where id = '$INPUT{id}'"); $status_printout = "Successfully removed person from database!"; &main; exit 0; } else { print qq~ Delete Person from Office Directory - Eagle Real Estate, Inc. - Connect.EagleSold.com ~; &header; print qq~
My Transaction Manager

Really Delete Person from Directory?
Are you sure you want to remove?
No [ Cancel ] or
~; print qq~ ~; &footer; } } sub directory_adddo { 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{type},$INPUT{firstname},$INPUT{lastname},$INPUT{agent1type},$INPUT{agent1phone},$INPUT{agent2type},$INPUT{agent2phone},$INPUT{agent3type},$INPUT{agent3phone},$INPUT{agentfax},$INPUT{email},$INPUT{companyname},$INPUT{coaddress},$INPUT{cocity},$INPUT{costate},$INPUT{cozip},$INPUT{cophone},$INPUT{showhide}); $sql_query = "INSERT INTO trans_boarddirectory values ('',?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"; my $aasth = $dbh->do($sql_query, undef, @variables); my $abasth = $dbh->prepare("select id from trans_boarddirectory ORDER BY id DESC LIMIT 0,1"); $abasth->execute; while (($thedirectoryid) = $abasth->fetchrow_array()) { $directoryid = $thedirectoryid; } print qq~ Making Changes to Datebase



Please wait, Processing
You will be redirected to when finished. If not Click Here.
~; } sub directory_add { print qq~ Company Directory -- Eagle Real Estate, Inc. - Connect.EagleSold.com ~; &header; print qq~ My Transaction Manager

Eagle Company Directory
        Navigation: Return To Directory Edit Search


Contact Information
Edit Contact Information
Name Firstname

Lastname
Contact Information
 Phone 1 Type
Number

Phone 2 Type
Number

Phone 3 Type
Number

Fax
Number

E-Mail Address


Contact Type:
Company Information:
 Company

Company Address

City

State

Zip

Company Phone
Is this contact active or inactive?
Making this contact inactive will hide from the office directory and showing setups. If the contact has switched companies DO NOT fill out new company information. There new information and company has already been transfered into the directory!!! 		Status
Cancel
~; print qq~ ~; &footer; } sub directory_editdo { 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{type},$INPUT{firstname},$INPUT{lastname},$INPUT{agent1type},$INPUT{agent1phone},$INPUT{agent2type},$INPUT{agent2phone},$INPUT{agent3type},$INPUT{agent3phone},$INPUT{agentfax},$INPUT{email},$INPUT{companyname},$INPUT{coaddress},$INPUT{cocity},$INPUT{costate},$INPUT{cozip},$INPUT{cophone},$INPUT{showhide},$INPUT{directoryid}); $sql_query = "UPDATE trans_boarddirectory set type = ?, firstname = ?, lastname = ?, agent1type = ?, agent1phone = ?, agent2type = ?, agent2phone = ?, agent3type = ?, agent3phone = ?, agentfax = ?, email = ?, company = ?, coaddress = ?, cocity = ?, costate = ?, cozip = ?, cophone = ?, showhide = ? where id = ?"; 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 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 $abasth = $dbh->prepare("select company from trans_boarddirectory GROUP BY company ASC"); $abasth->execute; while (($company) = $abasth->fetchrow_array()) { $buildcompany .= ""; } print qq~ Company Directory -- Eagle Real Estate, Inc. - Connect.EagleSold.com ~; &header; print qq~ My Transaction Manager

Eagle Company Directory
        view contacts and information the directory

$status_printout
Find A Contact To Manage Below        Add New Contact | Import Contacts
Search By Company
~; &footer; } } sub directory_edit { 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 $basth = $dbh->prepare("select id,type,firstname,lastname,agent1type,agent1phone,agent2type,agent2phone,agent3type,agent3phone,agentfax,email,company,coaddress,cocity,costate,cozip,cophone,showhide from trans_boarddirectory WHERE id = '$INPUT{directoryid}'"); $basth->execute; print qq~ Company Directory -- Eagle Real Estate, Inc. - Connect.EagleSold.com ~; &header; print qq~ ~; &footer; } sub listnew_save { 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; $total_imported = 0; @new_contacts = split(/\,/, $INPUT{newcontact}); foreach $con (@new_contacts) { my $gasth = $dbh->prepare("select * from mlsusers where agentid = ?"); $gasth->execute($con); while ($db_agent = $gasth->fetchrow_hashref()) { %mls_agent = %$db_agent; my $kasth = $dbh->prepare("select * from organizations where officeid = ?"); $kasth->execute($mls_agent{officeid}); while ($db_office = $kasth->fetchrow_hashref()) { %mls_office = %$db_office; $mls_agent{phone} =~ s/\s+$//; if ($mls_agent{lastname} =~ /Inactive/i) { $enter_status = "inactive"; } else { $enter_status = "active"; } @variables = ($mls_agent{firstname},$mls_agent{lastname},$mls_agent{phone},$mls_office{officename},$mls_office{street},$mls_office{city},$mls_office{state},$mls_office{zip},$mls_office{phone},$enter_status); $sql_query = "INSERT INTO trans_boarddirectory values ('','MLS',?,?,'',?,'','','','','','',?,?,?,?,?,?,?)"; my $vvaasth = $dbh->do($sql_query, undef, @variables); $total_imported++; } ## end office loop } ## end agent loop } ## end foreach new contact print qq~$total_imported total imported contacts~; } ## end sub sub listnew_view { 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; $total_new_contacts_found = 0; print qq~ ~; my $gasth = $dbh->prepare("select * from mlsusers"); $gasth->execute; while ($db_agent = $gasth->fetchrow_hashref()) { %mls_agent = %$db_agent; my $kasth = $dbh->prepare("select * from organizations where officeid = ?"); $kasth->execute($mls_agent{officeid}); while ($db_office = $kasth->fetchrow_hashref()) { %mls_office = %$db_office; $found_information = 0; my $casth = $dbh->prepare("select id from trans_boarddirectory where firstname = ? and lastname = ? and company = ?"); $casth->execute($mls_agent{firstname},$mls_agent{lastname},$mls_office{officename}); $id = $casth->fetchrow_array(); $found_information = 1 if $id; my $to_show_it = 1; if ($found_information eq "0" && $mls_office{officename} !~ /FNISMLS/) { foreach $key (keys %bad_agentnames_last) { if ($mls_agent{lastname} =~ /$key/) { $to_show_it = 0; } } if ($to_show_it eq "1") { ## new contact found print qq~ $mls_agent{firstname} $mls_agent{lastname} - $mls_office{officename}     $mls_agent{phone}
~; $total_new_contacts_found++; } } } ## end office while loop } ## end agent while loop print qq~

~; print qq~

Found $total_new_contacts_found total new contacts~; } ## end sub sub runsql { 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 $aaasth = $dbh->prepare("select agentid,officeid,lastname,firstname,phone from mlsusers"); $aaasth->execute; while (($agentid,$officeid,$lastname,$firstname,$agentphone) = $aaasth->fetchrow_array()) { my $baasth = $dbh->prepare("select officeid,officename,street,city,state,zip,phone from organizations where officeid = '$officeid'"); $baasth->execute; while (($coofficeid,$officename,$costreet,$cocity,$costate,$cozip,$cophone) = $baasth->fetchrow_array()) { ### id,type,firstname,lastname,agent1type,agent1phone,agent2type,agent2phone,agent3type,agent3phone,agentfax,email,company,coaddress,cocity,costate,cozip,cophone ## @variables = ($firstname,$lastname,$agentphone,$officename,$costreet,$cocity,$costate,$cozip,$cophone); $sql_query = "INSERT INTO trans_boarddirectory values ('','mls',?,?,'',?,'','','','','','',?,?,?,?,?,?)"; my $vvaasth = $dbh->do($sql_query, undef, @variables); print qq~ --- > Imported $firstname $lastname,$agentphone $coofficeid,$officename,$costreet,$cocity,$costate,$cozip,$cophone
~; } } }
Search Company Directory By Lastname
All A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z

~; if ($INPUT{contact} or $INPUT{company}) { if ($INPUT{contact}) { $sql = "WHERE lastname LIKE '$INPUT{contact}%' ORDER by lastname"; $searchtype = "contact"; } elsif ($INPUT{company}) { $sql = "WHERE company = '$INPUT{company}' ORDER by company"; $searchtype = "company"; } my $basth = $dbh->prepare("select id,firstname,lastname,company,showhide from trans_boarddirectory $sql ASC"); $basth->execute; $rowcolor{on} = "#FFFFFF"; $rowcolor{off} = "#FFFF99"; $color_count = "0"; $row_switch = "on"; print qq~ ~; while (($directoryid,$firstname,$lastname,$company,$showhide) = $basth->fetchrow_array()) { if ($showhide eq "inactive") { $sshowhide = "InActive"; } else { $sshowhide = ""; } $company = substr($company,0,11); print qq~ ~; if ($color_count eq "1") { print qq~~; $color_count = "0"; } else { $color_count = "1"; } if ($color_count eq "0") { if ($row_switch eq "on") { $row_switch = "off"; } else { $row_switch = "on"; } } $ccfound = "yes"; } print qq~~ if ($color_count eq "1"); print qq~
$sshowhide $lastname, $firstname - $company Edit
~; 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.
~; } } sub directory_view { 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 $basth = $dbh->prepare("select id,type,firstname,lastname,agent1type,agent1phone,agent2type,agent2phone,agent3type,agent3phone,agentfax,email,company,coaddress,cocity,costate,cozip,cophone,showhide from trans_boarddirectory WHERE id = '$INPUT{directoryid}'"); $basth->execute; print qq~ Company Directory -- Eagle Real Estate, Inc. - Connect.EagleSold.com ~; &header; print qq~ 		My Transaction Manager

Eagle Company Directory
        Navigation: Return To Directory Edit Search


Contact Information
~; while (($id,$type,$firstname,$lastname,$agent1type,$agent1phone,$agent2type,$agent2phone,$agent3type,$agent3phone,$agentfax,$email,$company,$coaddress,$cocity,$costate,$cozip,$cophone,$showhide) = $basth->fetchrow_array()) { print qq~
Directory Contact Actions

[ Setup A Showing ]

[ Edit Information ]

[ Delete Contact ]
Contact status: $showhide
$firstname $lastname
$agent1type $agent1phone
$agent2type $agent2phone
$agent3type $agent3phone
Fax: $agentfax
E-Mail: $email


Contact Type: $type

 Company Information:
$company
$coaddress
$cocity, $costate $cozip
Company Phone: $cophone


Showings History
~; my $aasth = $dbh->prepare("select status,mlsnumber,appointmentdate,timerange1,timerange2 from trans_boardshowings WHERE showingagent = '$id' ORDER BY appointmentdate desc"); $aasth->execute; $trcolor = "1"; $trcolor2 = "2"; while (($status,$mlsnumber,$appointmentdate,$timerange1,$timerange2) = $aasth->fetchrow_array()) { my $aaaddress = $dbh->prepare("select addressnumber,addressdirection,addressstreet from trans_board where mlsnumber = '$mlsnumber' LIMIT 0,1"); $aaaddress->execute; while (($addressnumber,$addressdirection,$addressstreet) = $aaaddress->fetchrow_array()) { $propertyaddress = "$addressnumber $addressdirection $addressstreet"; } if ($trcolor eq $trcolor2) { $trcolor = "1"; $setcolor = "#FFFFFF"; } else { $setcolor = "#CCCCFF"; $trcolor++; } print qq~ ~; $found = "yes"; } unless ($found eq "yes") { print qq~ ~; } print qq~
Status Property Address MLS Number Appointment
$status
 $propertyaddress
$mlsnumber
 $appointmentdate $timerange1-$timerange2
No Showings Found For This Contact


Transaction(s) History as of December 1st, 2004
~; my $casth = $dbh->prepare("select id,agent,status,selleragent,sellercoagent,buyeragent,buyercoagent,addressnumber,addressdirection,addressstreet,city,state,zip,solddate from trans_board WHERE (buyeragent = '$id' or buyercoagent = '$id' or sellercoagent = '$id' or selleragent = '$id') ORDER BY solddate desc"); $casth->execute; $trcolor = "1"; $trcolor2 = "2"; while (($transid,$transagent,$status,$selleragent,$sellercoagent,$buyeragent,$buyercoagent,$addressnumber,$addressdirection,$addressstreet,$city,$state,$zip,$solddate) = $casth->fetchrow_array()) { if ($trcolor eq $trcolor2) { $trcolor = "1"; $setcolor = "#FFFFFF"; } else { $setcolor = "#CCCCFF"; $trcolor++; } if ($solddate eq '0000-00-00') { $solddate = "Not Sold Yet"; } if ($selleragent eq "$id") { $transtype = "Seller Agent"; } if ($sellercoagent eq "$id") { $transtype = "Seller Co Agent"; } if ($buyeragent eq "$id") { $transtype = "Buyer Agent"; } if ($buyercoagent eq "$id") { $transtype = "Buyer Co Agent"; } if ($selleragent eq $buyeragent) { $transtype = "Buyer & Seller Agent"; } print qq~ ~; $found = "yes"; } unless ($found eq "yes") { print qq~ ~; } print qq~
  Address City Status Sold Date How Involved In Transaction
View $addressnumber $addressdirection $addressstreet $city
$status
$solddate
$transtype
No History Found For This Contact
~; print qq~ 		My Transaction Manager

Eagle Company Directory
        Navigation: Return To Directory Edit Search


Contact Information
~; while (($id,$type,$firstname,$lastname,$agent1type,$agent1phone,$agent2type,$agent2phone,$agent3type,$agent3phone,$agentfax,$email,$company,$coaddress,$cocity,$costate,$cozip,$cophone,$showhide) = $basth->fetchrow_array()) { if ($agent1type eq "") { $getagent1type = ""; $getagent1type .= ""; } else { $getagent1type = ""; $getagent1type .= ""; } if ($agent2type eq "") { $getagent2type = ""; $getagent2type .= ""; } else { $getagent2type = ""; $getagent2type .= ""; } if ($agent3type eq "") { $getagent3type = ""; $getagent3type .= ""; } else { $getagent3type = ""; $getagent3type .= ""; } if ($showhide eq "active") { $getshowhide = ""; $getshowhide .= ""; } else { $getshowhide = ""; $getshowhide .= ""; } print qq~
Edit Contact Information
Name Firstname

Lastname
Contact Information
 Phone 1 Type
Number

Phone 2 Type
Number

Phone 3 Type
Number

Fax
Number

E-Mail Address


Contact Type:
Company Information:
 Company

Company Address

City

State

Zip

Company Phone
Is this contact active or inactive?
Making this contact inactive will hide from the office directory and showing setups. If the contact has switched companies DO NOT fill out new company information. There new information and company has already been transfered into the directory!!! 		Status
Cancel
~; } print qq~