source: trunk/npemap.org.uk/cgi/get-postcodes.fcgi @ 82

#!/usr/bin/perl
#
# Copyright (c) 2006 Dominic Hargreaves
# See accompanying file "LICENCE" for licence details

use strict;
use warnings;

use DBI;
use CGI::Fast qw/:standard -debug/;

use vars qw($dbname $dbhost $dbuser $dbpass);

sub print_err;
sub setup_dbh;

# Read in database config
my $config = 'npemap.conf';
do $config or die "Can't read $config!\n";


# Set up database handler to try and make sure it's ready for the first
# request
# No point in handling errors here since they'll get handled by the request
# handler
my $dbh;
setup_dbh();


my @fields = qw(mineasting minnorthing maxeasting maxnorthing);
my $max_distance = 50000; # in metres

my $cgi;
# Process incoming requests
REQUEST: while ($cgi = new CGI::Fast) {

    # In case the database went away, make sure we have a connection
    unless (setup_dbh()) {
        print_internal_err('Error setting up database connection');
        next REQUEST;
    }

    # Input validation
    foreach my $field (@fields) {
        unless (defined $cgi->param($field)) {
            print_err ("Parameter '$field' missing");
            next REQUEST;
        }
    }

    # Are the Eastings in a valid range?
    foreach (qw(mineasting maxeasting)) {
        if (($cgi->param($_) > 700000) or
            ($cgi->param($_) < 0)) {
            print_err ("Parameter '$_' must be an integer between 0 and 700,000");
            next REQUEST;
        }
    }

    # Are the Northings in a valid range?
    foreach (qw(minnorthing maxnorthing)) {
        if (($cgi->param($_) > 1300000) or
            ($cgi->param($_) < 0)) {
            print_err("Parameter '$_' must be an integer between 0 and 1,300,000");
            next REQUEST;
        }
    }
    
    # Is the box too big?
    if (($cgi->param('maxeasting') - $cgi->param('mineasting') > $max_distance) or ($cgi->param('maxnorthing') - $cgi->param('minnorthing') > $max_distance)) {
        print_err("The requested box is too large");
        next REQUEST;
    }

    my $sth = $dbh->prepare("SELECT outward || ' ' || inward AS postcode, easting, northing FROM postcodes WHERE easting BETWEEN ? AND ? AND northing BETWEEN ? AND ?");
    if ($sth->execute($cgi->param('mineasting'), $cgi->param('maxeasting'), $cgi->param('minnorthing'), $cgi->param('maxnorthing'))) {
        my $hr = $sth->fetchall_hashref('postcode');
        print "Content-type: text/javascript\n\n";

        foreach my $key (keys %$hr) {
            my $hash = $$hr{$key};
            print "addMarker('$$hash{postcode}', $$hash{easting}, $$hash{northing});\n";
        }
    } else {
        print_internal_err("Database error retrieving data");
    }

}

# No more requests to serve, so tidy up
$dbh->disconnect;

# Helper routines
sub print_err($) {
    my $err = shift;
    print header("text/plain", "400 $err");
    print "Error: $err\n";
}

sub print_internal_err($) {
    my $err = shift;
    print header("text/plain", "500 $err");
    print "Error: $err\n";
}

sub setup_dbh {
    # $dbh is global
    my $data_source = "dbi:Pg:dbname=$dbname";
    $data_source .= ";host=$dbhost" if $dbhost;
    return $dbh = DBI->connect_cached($data_source, $dbuser, $dbpass);
}