#!/usr/bin/perl
#
# Copyright (c) 2007 Nick Burch
# 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.
#
#					Raw Postcode Data Exporter
#					--------------------------
#
# Exports the raw postcode data, including IDs.
# Doesn't export deleted postcodes, but does export ones with open
#  problem reports.
# Normally used by people wishing to check our data for errors

use strict;
use DBI;

# Find our private perl libraries
use FindBin;
use lib "$FindBin::Bin/../../perllib";
use NPEMap::GeoConverter;

# What to connect with
my $dbname = $ENV{DBNAME} || "npemap";
my $dbhost = $ENV{DBHOST} || "";
my $dbuser = $ENV{DBUSER} || "npemap";
my $dbpass = $ENV{DBPASS} || "";

# Connect to the database
my $dsn = "dbi:Pg:dbname=$dbname";
$dsn .= ";host=$dbhost" if $dbhost;

my $conn = DBI->connect($dsn, $dbuser, $dbpass,
					{ PrintError => 0, RaiseError => 1, AutoCommit => 1 } );

# Print out our header
print "# Generated on ".gmtime()."\n";
print "#\n";
print "# This information is made available under the Public Domain\n";
print "# Dedication and License version 1.0 whose full text can be found\n";
print "# at http://www.npemap.org.uk/pddl, and comes from \n";
print "# http://www.npemap.org.uk/\n";
print "#\n";
print "# This is raw postcode data, and not intended for end users. See \n";
print "#  http://www.npemap.org.uk/data/ for formatted data\n";
print "#\n";
print "# <internal id>,<outward>,<inward>,<easting>,<northing>,<grid>,<problem reported>\n";

# Get our data fetch query
my $source = getSource("NPE Postcode web submission", $conn);
my $query = getQuery($conn);

# Process the query
$query->execute;
while(my ($id,$outer,$inner,$e,$n,$ie_e,$ie_n,$problem) = $query->fetchrow_array) {
	# What grid are we on?
	my $grid = "osgb";
	if(!$e && $ie_e) {
		$grid = "osie";
		$e = $ie_e;
		$n = $ie_n;
	}
	
	# Make e+n look nicer
	$e =~ s/^(\d+)\.\d+$/$1/;
	$n =~ s/^(\-?\d+)\.\d+$/$1/;

	print "$id,$outer,$inner,$e,$n,$grid,$problem\n";
}

# Close down
$conn->disconnect;


############################################################################

# Get a query returning 
# 		id, outer, inner, easting, northing
sub getQuery($) {
	my $sql = 
		"SELECT id, outward, inward, easting, northing, ie_easting, ie_northing, reason IS NOT NULL AS problem ".
		"FROM postcodes ".
		"LEFT OUTER JOIN bad_postcodes ON (id = postcode AND NOT actioned) ".
		"WHERE source = ? AND NOT deleted ".
		"ORDER BY outward, inward ";

	my $sth = $conn->prepare($sql);
	$sth->bind_param(1, $source);
	return $sth;
}

sub getSource($$) {
	my ($source,$conn) = @_;

	my $sth = $conn->prepare("SELECT id FROM sources WHERE name = ?");
	$sth->bind_param(1, $source);
	$sth->execute;

	my $s = undef;
	my @row = $sth->fetchrow_array;
	if($sth->fetchrow_array) {
		die("Should only find one source with name '$source', got more than one!");
	}

	unless(@row) {
		die("Can't find id for source with name '$source'");
	}

	return $row[0];	
}
