#!/usr/bin/perl

use DBI;
use CGI;
use CommUnity::Process;
use strict;

if ($ENV{'HTTP_REFERER'} =~ /google/) {
	print "Status: 302 moved\n";
	print "Location: /genealogy/\n\n";
	exit 0;
}

# read template
my $template = "templates/search.htm";
open(IN, "<$template");
$template = join("", <IN>);
close(IN);

my %hash = ();

my $cgi = new CGI;
my $surname = $cgi->param("surname");
my $forename = $cgi->param("forename");
my $exact = $cgi->param("exact");
my $year = $cgi->param("year");

$hash{cgi} = { %$cgi };

if ($year && ($surname || $forename)) {

	$hash{searching} = 1;

	my $db = DBI->connect("dbi:mysql:rpb-census:mysql.community.net.uk",
		"rpb", "juicy+offer");

	my $sql = <<EOT;

SELECT
	entry.*,
	FLOOR(dataset.year + 0.25 - entry.age_num) AS yob
FROM	entry, dataset
WHERE	entry.dataset = dataset.id AND
	dataset.year = ?

EOT

	my (@predicates, @parameters);
	push(@parameters, $year);

	if ($surname ne "") {
		if ($exact) {
			push(@predicates, "surname = ?");
		} else {
			push(@predicates, "surname_sx = LEFT(SOUNDEX(?), 4)");
		}
		push(@parameters, $surname);
	}

	if ($forename ne "") {
		if ($exact) {
			push(@predicates, "forename = ?");
		} else {
			push(@predicates, "forename_sx = LEFT(SOUNDEX(?), 4)");
		}
		push(@parameters, $forename);
	}

	map { $_ = '(' . $_ . ')' } @predicates;
	if (@predicates) {
		$sql .= ' AND (' . join(' AND ', @predicates) . ')';
	}
	$sql .= ' ORDER BY entry.age_num DESC';

	my $sth = $db->prepare($sql);
	$sth->execute(@parameters);

	$hash{sql} = $sql;
	$hash{results} = $sth->fetchall_arrayref({});
}

print "Content-type: text/html\n\n";

print process($template, %hash);