#!/usr/local/bin/perl

##############################################
# show_call_data.pl by Mike Moxcey #
# Generates reports for Oracle/Perl tutorial #
##############################################

$| = 1; # prevents forking so stuff is printed in correct order

############################################
# Define include files #
############################################

require "cgi-lib.pl"; #ReadParse
require "oracle.pl"; #&sql,fix_string
require "oratut.pl";

&ReadParse (*input);

$call_type = $input{'call_type'}; # open,closed,all
$sort_opt = $input{'sort_opt'}; # 1-pri,2-email,3-date

$dte_rng = $input{'dte_rng'}; #if date range is chosen =Y
$beg_date = $input{'beg_date'};
$end_date = $input{'end_date'};

#############################
# define hdr and field vars #
#############################

$f_name= "'<td align=center>', name,'</td>'";
$f_date= "'<td align=center>', to_char(open_date,'DD-MON-YY HH:MI AM'),'</td>'";
$f_pri= "'<td align=center>', priority,'</td>'";
$f_email= "'<td align=center>', email,'</td>'";
$f_seq= "'<td align=center>', seq,'</td>'";
$f_prob= "'<td align=center>', substr(problem,1,30),'</td>'";

$h_name="<th>Caller Name</th>";
$h_date="<th>Entry Date</th>";
$h_pri="<th>Pri</th>";
$h_email="<th>Email</th>";
$h_seq="<th>ID Nr</th>";
$h_prob="<th>Problem</th>";

#############
# call type #
#############

if ($dte_rng eq "Y") {

if ($call_type eq "closed") {
$date_var="close_date";
$date_prn="Calls Closed between";
}
else {
$date_var="open_date";
$date_prn="Calls Opened between";
}
$get_call="where $date_var between to_date('$beg_date','DD-MON-YYYY')
and to_date('$end_date','DD-MON-YYYY') ";
$prn_head="$date_prn dates $beg_date and $end_date";
}

else { # no date range

if ($call_type eq "closed") {
$get_call="where close_date is not null";
$prn_head="Closed Calls";
}
elsif ($call_type eq "open") {
$get_call="where close_date is null";
$prn_head="Open Calls";
}
else {
$get_call=" "; #null line DO NOT PUT ON ITS OWN LINE IN SQL
$prn_head="All Calls";
}

}

################
# sort options #
################

if ($sort_opt eq "2") {
$sort_opt = "email";
$sort_name = "E-mail";
$var_list="'<tr>', $f_email, $f_name, $f_pri, $f_seq, $f_date, $f_prob, '</tr>'";
$table_head= "<table border><tr>$h_email $h_name $h_pri $h_seq $h_date $h_prob </tr>";
}
elsif ($sort_opt eq "3") {
$sort_opt = "open_date desc";
$sort_name = "Entry Date Descending";
$var_list="'<tr>', $f_date, $f_pri, $f_email, $f_name, $f_seq, $f_prob, '</tr>'";
$table_head= "<table border><tr>$h_date $h_pri $h_email $h_name$h_seq $h_prob </tr>";
}
else {
$sort_opt = "priority";
$sort_name = "Priority";
$var_list="'<tr>', $f_pri, $f_seq, $f_date, $f_name, $f_email, $f_prob, '</tr>'";
$table_head= "<table border><tr> $h_pri $h_seq $h_date $h_name $h_email $h_prob </tr>";
}

@sql_code =<<ENDOFSQL;
select $var_list
from oratut.calls $get_call
order by $sort_opt;
ENDOFSQL

################
# display form #
################

print &PrintHeader;
print &HtmlTopTut("Report of $prn_head");
print "Sorted by $sort_name\n $table_head";

&sql($sql_login,@sql_code);
##DEBUG print "SQLShow:@sql_code!";

print "</table>";
##DEBUG print "dte_rng=$dte_rng! date_var=$date_var!";
print &HtmlBotTut;


home