#!/usr/bin/perl
use DBI;
use CGI;
use Data::Dumper;

## INIT STUFF
my $path="/cgi-bin/cert.pl";
my $file;
my $cgi = new CGI;
my $table_name = 'cert';
## END INIT STUFF

$date = `/bin/date`; chomp($date);
warn "\n[ $date %]\n";
my $op = $cgi->param('op') || 'index';
warn "[ NCCAA Certification DB: op=$op ]\n";


if ($op eq 'index') {
	# do stuff here
	$vars->{pagetitle} = 'Verify Certification';
	$file = 'cert/index.html';
} elsif ($op eq 'search') {
	my $lastname = $cgi->param('lastname');
	my $id = $cgi->param('certification_number');
	$target_url = "$path?op=results&lastname=$lastname&cert_id=$id";
} elsif ($op eq 'printer_view') {
	$without_header_footer=1;
	my $pk_person = $cgi->param('id');
	# code change 23JULY2007
	# Changing the date to include the DAY
# OLD CODE	my $person_info = db_hook("select DATE_FORMAT(expiration, '%M %Y') as expiration,firstname,lastname,cert_number,pk_person from $table_name where pk_person = $pk_person");
	my $person_info = db_hook("select DATE_FORMAT(expiration, '%d %M %Y') as expiration,firstname,lastname,cert_number,pk_person from $table_name where pk_person = $pk_person");
	$vars->{firstname} = $person_info->{firstname};
	$vars->{lastname} = $person_info->{lastname};
	$vars->{cert_number} = $person_info->{cert_number};
	$vars->{expiration} = $person_info->{expiration};
	$vars->{pk_person} = $person_info->{pk_person};
	$vars->{date} = `/bin/date +"%d %B %Y"`;
	if (my $person_exp = db_hook("select * from $table_name where to_days(expiration) >= to_days(now()) and pk_person = $pk_person")) {
		$file = 'cert/printer_view.html';
	} else {
		$file = 'cert/printer_view_expired.html';
	}
} elsif ($op eq 'view') {
	$vars->{pagetitle} = 'Verification Result';
	my $pk_person = $cgi->param('id');
	# code change 23JULY2007
	# Changing the date to include the DAY
# OLD CODE	my $person_info = db_hook("select DATE_FORMAT(expiration, '%M %Y') as expiration,firstname,lastname,cert_number,pk_person from $table_name where pk_person = $pk_person");
	my $person_info = db_hook("select DATE_FORMAT(expiration, '%d %M %Y') as expiration,firstname,lastname,cert_number,pk_person from $table_name where pk_person = $pk_person");
	$vars->{firstname} = $person_info->{firstname};
	$vars->{lastname} = $person_info->{lastname};
	$vars->{cert_number} = $person_info->{cert_number};
			
	$vars->{expiration} = $person_info->{expiration};
	$vars->{pk_person} = $person_info->{pk_person};
	if (my $person_exp = db_hook("select * from $table_name where to_days(expiration) >= to_days(now()) and pk_person = $pk_person")) {
		$file = 'cert/view.html';
	} else {
		$file = 'cert/view_expired.html';
	}		
} elsif ($op eq 'view_all') {
	$vars->{pagetitle} = 'Search Results';
	#my $query = "select * from cert where to_days(expiration) >= to_days(now())";
	my $query = "select * from $table_name where to_days(expiration) >= to_days(now())";
	if (my $results = db_hooks($query)) {
		for my $r (@{$results}) {
			if ($count) { $bgcolor="e9f1f8"; $count=0; } else { $bgcolor="d1e1ef"; $count=1; }
			$vars->{results} .= "
<TR bgcolor=\"#$bgcolor\">
<TD width=40% style=\"font-size:9pt;\">$r->{firstname} $r->{lastname}</TD>
<TD align=center style=\"font-size:9pt;\">$r->{cert_number}</TD>
<TD align=center><A style=\"color:darkblue;text-decoration:none;\" HREF=\"/cgi-bin/cert.pl?op=view&id=$r->{pk_person}\"><NOBR><IMG border=0 SRC=\"/verify_$bgcolor.jpg\"></NOBR></A></TD></TR>\n";
		}
		$file='cert/results.html';
	} else {
		$file='cert/results_none.html';
	}

} elsif ($op eq 'results') {
	$vars->{pagetitle} = 'Search Results';
	my $lastname = $cgi->param('lastname');
	my $id = $cgi->param('cert_id');
	$vars->{stuff}='yeah';
	if ($id) {
		$query = "SELECT * FROM $table_name where cert_number = $id";
	} elsif ($lastname) {
		$query = "SELECT * FROM $table_name where lastname like '%$lastname%'";
	}
	if (my $results = db_hooks($query)) {
		for my $r (@{$results}) {
			if ($count) { $bgcolor="e9f1f8"; $count=0; } else { $bgcolor="d1e1ef"; $count=1; }
			$vars->{results} .= "
<TR bgcolor=\"#$bgcolor\">
<TD width=40% style=\"font-size:9pt;\">$r->{firstname} $r->{lastname}</TD>
<TD align=center style=\"font-size:9pt;\">$r->{cert_number}</TD>
<TD align=center><A style=\"color:darkblue;text-decoration:none;\" HREF=\"/cgi-bin/cert.pl?op=view&id=$r->{pk_person}\"><NOBR><IMG border=0 SRC=\"/verify_$bgcolor.jpg\"></NOBR></A></TD></TR>\n";
		}
		$file='cert/results.html';
	} else {
		$file='cert/results_none.html';
	}
} elsif ($op eq 'add_mail') {
	my $pk_person = $cgi->param('user_id');
	my $person = db_hook("select * from $table_name where pk_person = $pk_person");
	# code change 23JULY2007
	# replacing organization with address_0 and adding address_1 and address_2
	my @fields = (
		{ name => 'Name' },
		{ address_0 => 'Address' },
		{ city => 'City' },
		{ state => 'State' },
		{ zip => 'Zip' }
	);
	for my $f (@fields) {
		for my $key (keys %{$f}) {
			my $value = $cgi->param($key);
			my $name = $f->{$key};
			if (!$value) { push(@errors,"$name field is empty."); next; }
			$vars->{$key} = $value;
		}
	}
	# these should not change our result, as they are not required!
	if (my $addr = $cgi->param('address_1')) { $vars->{address_1} = $addr; }
	if (my $addr = $cgi->param('address_2')) { $vars->{address_2} = $addr; }

	$content .= "Practitioner: $person->{firstname} $person->{lastname}\n$vars->{name}\n $vars->{address_0}\n";
	if ($vars->{address_1}) { $content .= "$vars->{address_1}\n"; }
	if ($vars->{address_2}) { $content .= "$vars->{address_2}\n"; }
	$content .= "$vars->{city}, $vars->{state}  $vars->{zip}\n\n";
	if (@errors) { 
		$errors = "<FONT color=red>The following errors were found in your submission:<BR>\n";
		for my $e (@errors) {
			$errors .= "<LI>$e</LI>\n";
		}
		$errors .= "</FONT><BR>\n";
		$vars->{errors} = $errors;
		$vars->{pagetitle} = 'Verification Result';
		# code change 23JULY2007 
		# to include day.
		my $person_info = db_hook("select DATE_FORMAT(expiration, '%d %M %Y') as expiration,firstname,lastname,cert_number,pk_person from $table_name where pk_person = $pk_person");
		$vars->{firstname} = $person_info->{firstname};
		$vars->{lastname} = $person_info->{lastname};
		$vars->{cert_number} = $person_info->{cert_number};
		$vars->{expiration} = $person_info->{expiration};
		$vars->{pk_person} = $person_info->{pk_person};
		$file = 'cert/view.html'; 
	} else {
		$vars->{pagetitle} = 'Submission Successful';
		### CHANGE THIS BACK
		open(FILE,">>cert/mailing.txt");
		print FILE $content;
		close(FILE);
		$file = 'cert/thank_you.html';
	}
}
## this will eventually go in a finish() subroutine...
if ($target_url) {
	warn "Redirection: $target_url\n";
	print "Location: $target_url\n\n";
} else {
	open(FILE,"cert/header.html"); my @header = <FILE>; close(FILE);
	open(FILE,"$file"); my @content = <FILE>; close(FILE);
	open(FILE,"cert/footer.html"); my @footer = <FILE>; close(FILE);

	for my $line (@header) {
		if ($line =~ /\[\%(.*)\%\]/) {
			$name = ${1};
			$name =~ s/\s+//g;
			$line =~ s/(\[\%.*\%\])/$vars->{$name}/g;
		}
		push(@new_header,$line);
	}
	for my $line (@content) {
		if ($line =~ /\[\%(.*)\%\]/) {
			$name = ${1};
			$name =~ s/\s+//g;
			$line =~ s/(\[\%.*\%\])/$vars->{$name}/g;
		}
		push(@new_content,$line);
	}

	print "Content-type:text/html\n\n";
	if ($without_header_footer) {
		print @new_content;
	} else {
		print @new_header; print @new_content; print @footer;
	}
}
## end finish() subroutine


sub db_hook {
	my $query = shift;
	my $db_host = 'aa0nccaa.aa-nccaa.org';
	my $db_user = 'aa0nccaa';
	my $db_pass = '1two3g0';
	my $db_name = 'aa0nccaa';
	my $db_port = '3306';
	my $db = "dbi:mysql:dbname=${db_name};host=aa0nccaa.aa-nccaa.org:${db_port}";
	$dbh = DBI->connect($db, $db_user, $db_pass) || die "Error connecting to the database: $DBI::errstr\n";
	warn "--mySQL (1 row) - $query\n";
	my $results = $dbh->prepare($query);
	$results->execute();
	my $result = $results->fetchrow_hashref();
	$results->finish();
	$dbh->disconnect();
	return $result;
}

sub db_insert {
	my $query = shift;
	my $db_host = 'aa0nccaa.aa-nccaa.org';
	my $db_user = 'aa0nccaa';
	my $db_pass = '1two3g0';
	my $db_name = 'aa0nccaa';
	my $db_port = '3306';
	my $db = "dbi:mysql:dbname=${db_name};host=aa0nccaa.aa-nccaa.org:${db_port}";
	$dbh = DBI->connect($db, $db_user, $db_pass) || die "Error connecting to the database: $DBI::errstr\n";
	my $resulted=0;
	warn "--mySQL Insert - $query\n";
	my $results = $dbh->prepare($query);
	if($results->execute()) { $resulted = 1; } 
	$results->finish();
	$dbh->commit();
	$dbh->disconnect();
	#warn Dumper($resulted);
	return $resulted;
}

sub db_hooks {
	my $query = shift;
	my $db_host = 'aa0nccaa.aa-nccaa.org';
	my $db_user = 'aa0nccaa';
	my $db_pass = '1two3g0';
	my $db_name = 'aa0nccaa';
	my $db_port = '3306';
	my $db = "dbi:mysql:dbname=${db_name};host=aa0nccaa.aa-nccaa.org:${db_port}";
	$dbh = DBI->connect($db, $db_user, $db_pass) || die "Error connecting to the database: $DBI::errstr\n";
	my $resulted;
	warn "--mySQL (Mutli-Row) - $query\n";
	my $results = $dbh->prepare($query);
	$results->execute();
	while (my $result = $results->fetchrow_hashref()) {
		push(@{$resulted},$result);
	}
	$results->finish();
	$dbh->disconnect();
	return $resulted;
}