#!/usr/bin/perl

# Copyright (c) 2006-2007 David Sheldon
# Permission is hereby granted, free of charge, to any person obtaining a
# copy of this software and associated documentation files (the "Software"),
# to deal in the Software without restriction, including without limitation
# the rights to use, copy, modify, merge, publish, distribute, sublicense,
# and/or sell copies of the Software, and to permit persons to whom the
# Software is furnished to do so, subject to the following conditions:
#
# The above copyright notice and this permission notice shall be included in
# all copies or substantial portions of the Software.
#
# THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
# IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
# FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL
# THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
# LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING
# FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS
# IN THE SOFTWARE.
#
#					Postcode Data Exporter



use strict;
use DBI;
use FindBin;
use Sys::Hostname;
my $POSTCODE_CACHE ;
my $POSTCODE_DIR ;
my $IMAGE_CACHE_DIR ;

if (hostname eq 'urchin') {
  $POSTCODE_CACHE = "/srv/www/npemap.org.uk/postcodeine/postcodes.dat";
  $POSTCODE_DIR =   "/srv/www/npemap.org.uk/postcodeine/";
  $IMAGE_CACHE_DIR ="/srv/www/npemap.org.uk/postcodeine/cache";
} else {
  $POSTCODE_CACHE = "/tmp/postcodes.dat";
  $POSTCODE_DIR =   "/tmp/";
  $IMAGE_CACHE_DIR ="/tmp/cache";
}

use lib "$FindBin::Bin/../../perllib";
use NPEMap::Postcodes;
use NPEMap;

sub setup_dbh;

my $dbh;

unless ($dbh = setup_dbh()) {
    die "Unable to connect to database";
}

my $csv = $POSTCODE_DIR . "NPE.csv";

open OUT, ">$csv" or die "unable to open $csv for writing.";

my $query = getQuery($dbh);

# Process the query
$query->execute;
while(my ($outer,$e,$n) = $query->fetchrow_array) {
    $e =~ s/^(\d+)\.\d+$/$1/;
    $n =~ s/^(\d+)\.\d+$/$1/;
    my $pcode = uc($outer);
    if (length($pcode) > 2 && $e && $n) {
	    print OUT "\"$pcode\",$e,$n\n";
    }
}
close OUT;

$dbh->disconnect;

if (-f $POSTCODE_CACHE) {
    unlink $POSTCODE_CACHE or die "Unable to delete $POSTCODE_CACHE: $!";
}

foreach my $file (glob("$IMAGE_CACHE_DIR/*.png")) {
    unlink $file or die "unable to delete $file: $!";
}

# Get a query returning 
# 		outer . inner, easting, northing
sub getQuery {
	my ($conn) = @_;

    my $sql = 
        "SELECT outward || inward, AVG(easting) AS avg_easting, AVG(northing) AS avg_northing ".
        "FROM postcodes  ".
        "WHERE NOT deleted ".
        "GROUP BY outward || inward ".
        "ORDER BY outward || inward ";

    my $sth = $conn->prepare($sql);
    return $sth;
}

