##########################################################
# report4.pl by Mike Moxcey #
# Fourth try at displaying data for Oracle/Perl tutorial #
# This one orders by a certian field #
# and beginning and ending dates #
##########################################################

$| = 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,date_rng
$beg_date = upper($input{'beg_date'});
$end_date = upper($input{'end_date'});
$sort_opt = $input{'sort_opt'}; # 1-pri,2-email,3-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 ($call_type eq "closed") {
$get_call="where close_date is not null";
$prn_type="Closed Calls";
}
elsif ($call_type eq "date_rng") {
$get_call="where open_date between to_date('$beg_date','DD-MON-YYYY')
and to_date('$end_date','DD-MON-YYYY') ";
$prn_type="Calls Opened between dates $beg_date and $end_date";
}
else {
$get_call="where close_date is null";
$prn_type="Open 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("$prn_type Calls Report");
print "Sorted by $sort_name\n $table_head";

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

print "</table>";
print &HtmlBotTut;