#######################################################
# load_data.pl by Mike Moxcey #
# Step one of Inserting data 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";

########
# main #
########

&ReadParse(*input);

$name = $input{'name'};
$email = $input{'email'};
$problem = fix_string($input{'problem'});
$priority = $input{'priority'};
$software = $input{'software'};
$close_flag = $input{'close_flag'}; # Y or N

$seq=$input{'seq'};
$close_date=$input{'close_date'};

#########################
# Check Required Fields #
#########################

if (length($name) < 1) {&FieldRequiredError('Name')}

####################
# set closure date #
####################

if ($close_flag eq 'Y')

{$cdate='SYSDATE';}

else ### open call set date to null ###

{$cdate="''";} #pass a null string w/no quotes

###################
# Define SQL Code #
###################

if ($seq) { # if existing call,

#set update display vars and sql_code

$msg="Updating";

$get_seq="where seq=$seq";

@sql_code = <<ENDOFSQL;

update oratut.calls

set name=initcap('$name'), priority='$priority',

email='$email', problem='$problem',

software='$software', close_date=$cdate

where seq=$seq;

ENDOFSQL

}

else { #set insert display vars and sql_code

$msg="Inserting";

$get_seq="where seq=(select max(seq) from oratut.calls)";

@sql_code = <<ENDOFSQL;

insert into oratut.calls

(seq, name, priority, open_date, email, problem, software, close_date)

values (

oratut.seq_nr.nextval,

initcap('$name'), '$priority', SYSDATE, '$email',

'$problem', '$software', $cdate);

ENDOFSQL

}

@sql_show = <<ENDOFSQL;

select

'<h3>ID Nr:<em>', seq,

'</em></h3> Priority:<em>', priority,

'</em> Date:<em>', open_date,

'</em> Time:<em>', to_char(open_date,'HH:MI PM'),

'</em><blockquote><p>User:<em>', name,

'</em><br>Email:<em>', email,

'</em></blockquote><p>',

problem

from oratut.calls $get_seq;

ENDOFSQL

########################
# Load Data #
# Display info to User #
########################

print &PrintHeader;
print &HtmlTopTut("$msg Call Data");

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

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