#!/usr/bin/perl -wT 

#########################################################
#							#
# warnvercgi.pl 					#
#							#
# Donald J. Miller III					#
# c. 2002						#
#########################################################

# Setup our environment, load libraries, etc. 
use DBI;
use CGI;
use CGI::Carp qw(fatalsToBrowser);

$CGI::POST_MAX=1024*100;
$CGI::DISABLE_UPLOADS=1;

# Begin main part of script. 

my $dbname = 'WARNMON';
my $title = "Submission Status";
#$query = new CGI;
#my $remote = $query->remote_addr();
my $retval = 0;
my $data_hashref;
my $data_arrayref;


#connect to our database
($dbh) = connectdb();

#Check how we were called.  Were we called from the parent form or
#were we called from inside the insertRecord function?

howCalled($dbh,$query);

#If called from the form, define query CGI object.
$query = new CGI;

#make sure requester is on authorized domain.
#auth_host($remote);

print $query->header;

# check that we got some required parameters from the form.
#check_param();

print $query->start_html(-title=>"$title",
				 -BGCOLOR=>'white');


$retval = insertRecord($dbh) if ($query->param() && !(defined($query->param('action'))));

	if ($retval = 0) {
		print "<b>Your office statistics have been added to the database.</b>";

	}
	elsif ($retval=1){"<b>Database error!  You may need to resubmit your statistics.</b>";}

$data_arrayref = getRecord($dbh);

foreach my $array_hashref (@{$data_arrayref}) 
{
	displayRecord($array_hashref);

}

print $query->end_html;

disconnectdb($dbh);

#end of main program


########################################END OF MAIN PROGRAM##################################################

sub connectdb {...snipped

sub insertRecord {

	my ($dbh) = @_;
	my ($office,$table,$month,$year,$warntype,$numWarn,$numVer,$numEvents);
	my ($miss,$avgLt,$pod,$far,$csi,$sth);
	my $retval = 0;

	$table = "office_statistics";
	$month = ($query->param('month') + 1);
	$year = $query->param('year');
	$office = $query->param('office'); 
	$warntype = $query->param('warntype');
	$numWarn = $query->param('numWarn');
	$numVer = $query->param('numVer');
	$numEvent = $query->param('numEvent');
	$miss = $query->param('numMiss');
	$avgLt = $query->param('avgLead');
	$pod = $query->param('pod');
	$far = $query->param('far');
	$csi = $query->param('csi');
	my $numUnVer = $numWarn-$numVer;

	#check if record already exists.  If it does, ask user if they want to update the record.

	$sth = $dbh->prepare(qq{SELECT COUNT(*) FROM $table where month=$month AND year=$year AND office=\'$office\' AND warn_category=\'$warntype\'});
	$sth->execute();
	$count = $sth->fetchrow_arrayref;
	$sth->finish;

	if ($count->[0]>0) {
		print "<b>Warning! You are trying to change a record previously stored in the database.<b><br>";
		print "<br>This is the record in the database.<br>";
		$sth = $dbh->prepare(qq{SELECT * FROM $table where month=$month AND year=$year AND office=\'$office\' AND warn_category=\'$warntype\'});
		$sth->execute();
		my $data = $sth->fetchrow_hashref;
		$sth->finish;
		displayRecord($data);
		print "<br>Do you still want to update this record?<br>";
		print $query->start_form;
		print $query->submit('action','Yes');
		print $query->submit('action','No');
		print $query->endform;

		$retval=3;
		return $retval;
		
	}


#SQL for inserting records.
	
	my $sql = qq{INSERT INTO $table (month,year,office,warn_category,num_warnings,verified_warnings,unverified_warnings,num_events,missed_events,avg_lt,pod,far,csi) VALUES ( ?,?,?,?,?,?,?,?,?,?,?,?,?)};

	$sth = $dbh->prepare ( $sql );
	$sth->execute($month,$year,$office,$warntype,$numWarn,$numVer,$numUnVer,$numEvent,$miss,$avgLt,$pod,$far,$csi);
	if ($@) {
		warn "Database error: $DBI::errstr\n";
		$retval = 1;
		$sth->finish;
		return $retval;
	}

	$sth->finish();
	#getRecord($dbh,$table);

	$retval = 0;
	return $retval;
		

}#insertRecord


sub getRecord {...snipped

sub displayRecord {...snipped.
	
		

sub updateRecord {
	
	my ($dbh,$query) = @_;
	my $table = "office_statistics";
	my $quer = "UPDATE $table";
	my $delim = " SET ";

	my $month = ($query->param('month') + 1);
	my $year = $query->param('year');
	my $office = $query->param('office'); 
	my $warntype = $query->param('warntype');
	my $numWarn = $query->param('numWarn');
	my $numVer = $query->param('numVer');
	my $numEvent = $query->param('numEvent');
	my $miss = $query->param('numMiss');
	my $avgLt = $query->param('avgLead');
	my $pod = $query->param('pod');
	my $far = $query->param('far');
	my $csi = $query->param('csi');
	my $numUnVer = $numWarn-$numVer;
	my ($sth,@hashArray);
	

}# updateRecord

sub howCalled {

	my ($cgiObj) = $query;
	my $cgi = new CGI; 

	if (defined($cgi->param('action'))) {
		if ($cgi->param('action') eq 'Yes') {

			updateRecord($dbh,$cgiObj);
	
		}
		elsif ($cgi->param('action') eq 'No') {
			print "Okay, the record will not be stored.  You may return to the form to reenter data.";
				
		}
		exit;
	}
	else {} 
		

}

