Choose Available Grid Reports Below for $agent_firstname $agent_lastname
$printtemplates
~;
}
print qq~
~;
}
sub reportgrid_print_address {
my($prop) = @_;
my $yasth = $dbh->prepare("SELECT * FROM trans_board WHERE id=?");
$yasth->execute($prop);
my $data_ref = $yasth->fetchrow_hashref();
my %prop_d = %$data_ref;
print qq~
$prop_d{addressnumber} $prop_d{addressdirection} $prop_d{addressstreet} $prop_d{city}, IN $prop_d{zip}
\n~;
}
sub reportgrid_print_listingdate {
my($prop) = @_;
my $yasth = $dbh->prepare("SELECT DATE_FORMAT(listingdate, '%m/%d/%Y') AS listingdate FROM trans_board WHERE id=?");
$yasth->execute($prop);
my $data_ref = $yasth->fetchrow_hashref();
my %prop_d = %$data_ref;
print qq~
$prop_d{listingdate}
\n~;
}
sub reportgrid_print_expiredate {
my($prop) = @_;
my $yasth = $dbh->prepare("SELECT DATE_FORMAT(expiredate, '%m/%d/%Y') AS expiredate FROM trans_board WHERE id=?");
$yasth->execute($prop);
my $data_ref = $yasth->fetchrow_hashref();
my %prop_d = %$data_ref;
print qq~
$prop_d{expiredate}
\n~;
}
sub reportgrid_print_tasktitle {
my($task) = @_;
my $yasth = $dbh->prepare("SELECT num_days,title FROM trans_boardtask_agents WHERE id=? LIMIT 1");
$yasth->execute($task);
my $data_ref = $yasth->fetchrow_hashref();
my %task_d = %$data_ref;
print qq~
$task_d{title} | Day #$task_d{num_days}
\n~;
}
sub reportgrid_print_taskstatus {
my ($task, $prop) = @_;
my $yasth = $dbh->prepare("SELECT (TO_DAYS(date_trigger) - TO_DAYS(now())) AS days_til_due,trans_boardtask_triggers.* FROM trans_boardtask_triggers WHERE agent_task_id=? AND idlink=? LIMIT 1");
#my $yasth = $dbh->prepare("SELECT * FROM trans_boardtask_triggers WHERE agent_task_id=? AND idlink=? LIMIT 1");
$yasth->execute($task, $prop);
my $data_ref = $yasth->fetchrow_hashref();
my %trigger_d = %$data_ref;
if ($trigger_d{date_completed} eq "0000-00-00 00:00:00") {
## not completed
if ($trigger_d{days_til_due} > 0) {
print qq~
\n~;
}
else {
print qq~
\n~;
}
}
elsif ($trigger_d{date_completed} eq "") {
## no trigger at all for this property
print qq~
X
\n~;
}
else {
print qq~
\n~;
}
}
sub reportgrid {
use DBI;
$dsn = "DBI:mysql:database=$d_name;host=localhost";
$dbh = DBI->connect($dsn, $d_username, $d_pass, {RaiseError => 1}) || die $DBI::errstr;
### GET AGENT NAME ###
my $agsth = $dbh->prepare("select firstname,lastname from agents where mlsid = '$Cook{agentid}' order by agentid ASC LIMIT 0,1");
$agsth->execute;
while (($agent_firstname,$agent_lastname) = $agsth->fetchrow_array()) {
### GET DATETIME ###
my $agsth = $dbh->prepare("select DATE_FORMAT(now(), '%b %D, %Y at %h\:%i%p')");
$agsth->execute;
while (($datenow) = $agsth->fetchrow_array()) {
print qq~
Task Report for $agent_firstname $agent_lastname - Connect.EagleSold.com
~;
## pull base template information based on passed $INPUT{id}
my $kasth = $dbh->prepare("SELECT * FROM trans_boardtask_templates WHERE id=?");
$kasth->execute($INPUT{id});
$data_ref = $kasth->fetchrow_hashref();
%template_d = %$data_ref;
## print pretty table starter
print qq~
Task Report Grid for $agent_firstname $agent_lastname as of $datenow $template_d{template_name} Property Address
~;
## find all property task triggers where this template is assigned to it
my $pasth = $dbh->prepare("SELECT idlink FROM trans_boardtask_triggers WHERE template_id=? GROUP BY idlink");
$pasth->execute($template_d{id});
while ($data_ref = $pasth->fetchrow_hashref()) {
my %trigger_d = %$data_ref;
push @all_properties, $trigger_d{idlink};
}
## @all_properties contains the IDs of all properties with this template assigned
## start the columns and print the property address
foreach $prop (@all_properties) {
&reportgrid_print_address($prop);
}
## end our first row
print qq~\n
~;
} #END GET DATETIME
} #END GET AGENT
}
sub report {
$print_header =qq~
Task Type
Activity
Description
Due Date
Actions
~;
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 $agsth = $dbh->prepare("select firstname,lastname from agents where mlsid = '$Cook{agentid}' order by agentid ASC LIMIT 0,1");
$agsth->execute;
while (($agent_firstname,$agent_lastname) = $agsth->fetchrow_array()) {
my $agsth = $dbh->prepare("select DATE_FORMAT(now(), '%b %D, %Y at %h\:%i%p')");
$agsth->execute;
while (($datenow) = $agsth->fetchrow_array()) {
print qq~
Task Report for $agent_firstname $agent_lastname - Connect.EagleSold.com
Task Report for $agent_firstname $agent_lastname as of $datenow
~;
}
}
### FIND TASKS DUE TODAY OR OVERDUE FOR TRANSACTIONS!
print qq~
Due or Overdue Transaction Tasks!
~;
### 1. Check The List Of Properties ###
my $ssth = $dbh->prepare("select id,addressnumber,addressdirection,addressstreet,city,zip,mlsnumber,status from trans_board where agent = '$Cook{agentid}' order by id DESC");
$ssth->execute;
while (($transid,$addressnumber,$addressdirection,$addresstreet,$city,$zip,$mlsnumber,$status) = $ssth->fetchrow_array()) {
my $this_address_bar = qq~
$status | $addressnumber $addressdirection $addresstreet, $city IN $zip (MLS Number $mlsnumber)
~;
my $this_address_bar_printed = "no";
### 2. Find The Template ###
if ($INPUT{template_id} ne "all") {
$extra_sql_code = " AND template_id = '$INPUT{template_id}'";
}
my $xasth = $dbh->prepare("SELECT template_id FROM trans_boardtask_triggers WHERE idlink = '$transid'$extra_sql_code GROUP BY template_id");
$xasth->execute;
while (($g_temp_id) = $xasth->fetchrow_array()) {
my $this_template_bar;
#IF TEMPLATE IS FOUND DISPLAY PROPERTY ADDRESS
### 4. Convert The Template ID To Name ###
my $basth = $dbh->prepare("select template_name from trans_boardtask_templates where id = '$g_temp_id'");
$basth->execute;
while (($template_id_name) = $basth->fetchrow_array()) {
$this_template_bar = qq~
Actions to Perform for $template_id_name
$print_header
~;
}
### 3. Find Overdue Tasks ###
my $casth = $dbh->prepare("select id,idlink,template_id,task_type,title,activity,agent_task_id,DATE_FORMAT(date_trigger, '%b %D, %Y'),notes from trans_boardtask_triggers where task_type = 'Transaction' and idlink = '$transid' AND template_id = '$g_temp_id' AND date_trigger <= now() AND date_completed = '0000-00-00 00:00:00' order by date_trigger ASC");
$casth->execute;
$trcolor = "1";
$trcolor2 = "2";
my $task_bar_data;
while (($task_triggers_id,$task_triggers_idlink,$task_triggers_template_id,$task_triggers_task_type,$task_triggers_title,$task_triggers_activity,$task_triggers_agent_task_id,$task_triggers_date_trigger,$task_triggers_notes) = $casth->fetchrow_array()) {
unless ($task_triggers_notes eq "") {$display_note = " (Note: $task_triggers_notes)";}
if ($task_triggers_activity eq "Letter") {
$task_triggers_activityl = "[ Print Letter ]";
}
elsif ($task_triggers_activity eq "E-Mail") {
$task_triggers_activityl = "[ Send E-Mail ]";
}
else {
$task_triggers_activityl = "";
}
if ($trcolor eq $trcolor2) {
$trcolor = "1";
$setcolor = "#ccccff";
}
else {
$setcolor = "#FFFFFF";
$trcolor++;
}
#$display_task_list .=qq~
$task_bar_data .= qq~
You Must Create/Assign An Action Plan To Your Prospects!
~;
}
print qq~
~;
}
sub tasks_agents {
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~
Action Plan Manager - Agent Tasks - Eagle Real Estate, Inc.
~;
&header;
print qq~
~;
if ($INPUT{do} eq "create") {
print qq~
~;
}
elsif ($INPUT{do} eq "create_save") {
@variables = ($Cook{agentid},$INPUT{task_type},$INPUT{task_category},$INPUT{title},$INPUT{activity},$INPUT{num_days},$INPUT{message});
$sql_query = "INSERT INTO trans_boardtask_agents values ('',?,?,?,?,?,?,'',?)";
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.
~;
}
elsif ($INPUT{do} eq "copy_defaults") {
$bsth = $dbh->prepare("select id,task_type,category,title,activity,num_days,message from trans_boardtask_defaults order by task_type DESC");
$bsth->execute;
while (($task_id,$task_type,$task_category,$task_title,$task_activity,$task_num_days,$task_message) = $bsth->fetchrow_array()) {
$build_default_tasks .= "\n";
}
print qq~
Copy Default Tasks to Agents Task Database
~;
}
elsif ($INPUT{do} eq "copy_defaults_save") {
@default_tasks = split(/\,/,$INPUT{agent_task_ids});
foreach $default_task_item (@default_tasks) {
### 1. SELECT DATA FROM DEFAULT TASK
my $asth = $dbh->prepare("select * from trans_boardtask_defaults where id = '$default_task_item'");
$asth->execute;
while ($data_ref = $asth->fetchrow_hashref()) {
%task_defaults = %$data_ref;
### 2. ENTER INTO AGENT TASKS
@variables = ($Cook{agentid},$task_defaults{task_type},$task_defaults{category},$task_defaults{title},$task_defaults{activity},$task_defaults{num_days},$task_defaults{message});
$sql_query = "INSERT INTO trans_boardtask_agents values ('',?,?,?,?,?,?,'',?)";
my $aasth = $dbh->do($sql_query, undef, @variables);
}
### 3. GET NEW AGENT TASK ID AND RECORD TO A VARIBLE
my $bsth = $dbh->prepare("select id from trans_boardtask_agents order by id DESC limit 0,1");
$bsth->execute;
while ($data_ref = $bsth->fetchrow_hashref()) {
%task_agent = %$data_ref;
$temp_save_agent_ids .= "$task_agent{id},";
}
}
### 4. ENTER INTO AGENT TEMPLATES
@variables = ($Cook{agentid},$INPUT{task_type},$INPUT{template_name},$temp_save_agent_ids);
$sql_query = "INSERT INTO trans_boardtask_templates values ('',?,?,?,?)";
my $casth = $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.
~;
}
elsif ($INPUT{do} eq "edit") {
my $asth = $dbh->prepare("select * from trans_boardtask_agents where id = '$INPUT{id}'");
$asth->execute;
while ($data_ref = $asth->fetchrow_hashref()) {
%task_agents = %$data_ref;
$task_agents{task_type} = "";
$task_agents{task_category} = "";
$task_agents{activity} = "";
print qq~
~;
}
}
elsif ($INPUT{do} eq "edit_save") {
@variables = ($INPUT{task_type},$INPUT{task_category},$INPUT{title},$INPUT{activity},$INPUT{num_days},$INPUT{message},$INPUT{id});
$sql_query = "update trans_boardtask_agents set task_type = ?, category = ?, title = ?, activity = ?, num_days = ?, message = ? 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.
~;
}
elsif ($INPUT{do} eq "create_template") {
$bsth = $dbh->prepare("select id,task_type,category,title,activity,num_days,message from trans_boardtask_agents where agentid = '$Cook{agentid}' order by num_days ASC");
$bsth->execute;
while (($task_id,$task_type,$task_category,$task_title,$task_activity,$task_num_days,$task_message) = $bsth->fetchrow_array()) {
$build_default_tasks .= "\n";
}
print qq~
Setup New Template From Agents Task Database
~;
}
elsif ($INPUT{do} eq "create_template_save") {
@variables = ($Cook{agentid},$INPUT{task_type},$INPUT{template_name},$INPUT{agent_task_ids});
$sql_query = "INSERT INTO trans_boardtask_templates values ('',?,?,?,?)";
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.
~;
}
elsif ($INPUT{do} eq "edit_template") {
my $asth = $dbh->prepare("select * from trans_boardtask_templates where id = '$INPUT{id}'");
$asth->execute;
while ($data_ref = $asth->fetchrow_hashref()) {
%task_templates = %$data_ref;
$task_templates{task_type} = "\n\n";
@sel_agent_tasks = split(/\,/,$task_templates{agent_task_ids});
foreach $task_id (@sel_agent_tasks) {
$this_template_includes{$task_id} = 1;
}
### POPULATE THE SELECT FIELDs
$aasth = $dbh->prepare("select id,task_type,category,title,activity,num_days from trans_boardtask_agents where agentid = '$Cook{agentid}' order by num_days ASC");
$aasth->execute;
while (($task_id,$task_type,$task_category,$task_title,$task_activity,$task_num_days) = $aasth->fetchrow_array()) {
my $data = qq~\n~;
if ($this_template_includes{$task_id} == 1) {
$build_sel_tasks .= $data;
}
else {
$build_avail_tasks .= $data;
}
}
print qq~
~;
}
}
elsif ($INPUT{do} eq "edit_template_save") {
@variables = ($INPUT{task_type},$INPUT{template_name},$INPUT{agent_task_ids},$INPUT{id});
$sql_query = "update trans_boardtask_templates set task_type = ?, template_name = ?, agent_task_ids = ? 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.
~;
}
elsif ($INPUT{do} eq "delete") {
@cvariables = ($INPUT{id});
$csql_query = "DELETE FROM trans_boardtask_agents WHERE id = ?";
my $caasth = $dbh->do($csql_query, undef, @cvariables);
print qq~
Making Changes to Datebase
Please wait, Processing You will be redirected to when finished. If not Click Here.
~;
}
elsif ($INPUT{do} eq "delete_template") {
unless ($INPUT{comfirmation} eq "Yes") {
print qq~
Really Delete $INPUT{id}? Yes | NO [ Cancel ]
~;
}
else {
@cvariables = ($INPUT{id});
$csql_query = "DELETE FROM trans_boardtask_templates WHERE id = ?";
my $caasth = $dbh->do($csql_query, undef, @cvariables);
print qq~
Making Changes to Datebase
Please wait, Processing You will be redirected to when finished. If not Click Here.
~;
unless ($INPUT{order}) { $INPUT{order} = "num_days"; }
my $asth = $dbh->prepare("select * from trans_boardtask_templates where agent_id = '$Cook{agentid}' order by id asc");
$asth->execute;
while ($data_ref = $asth->fetchrow_hashref()) {
%task_templates = %$data_ref;
@default_tasks = split(/\,/,$task_templates{agent_task_ids});
foreach $default_task_item (@default_tasks) {
my $basth = $dbh->prepare("select * from trans_boardtask_agents where id = '$default_task_item' order by num_days asc");
$basth->execute;
while ($data_ref = $basth->fetchrow_hashref()) {
%task_details = %$data_ref;
$print_itemized .= " Day $task_details{num_days} | $task_details{title} ";
}
}
print qq~
~;
&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;
print qq~
Task Manager - Eagle Real Estate, Inc.
~;
&header;
print qq~
~;
print qq~
Task Manager Create & Edit Tasks for your transaction!
$task_triggers_title$display_note
