#!/usr/bin/perl
# 
# Copyright (c) 2006-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.
#
#					Postcode Data Exporter
#					----------------------
#
# Exports various parts of the data, in various formats
# Normally these are of the form:
#   <postcode|part postcode>	<easting>	<northing>	<lat>	<long>
#
# Supported outputs are:
#   -gpsonly
#       Only export GPS sourced postcodes, averaged if several for a postcode
#   -outer
#       Only export the outer parts of the postcodes, averaged over all
#   -outer1
#       Only export the outer part + 1st inner digit, averaged over all
#       (Excludes entries where we only have an outward, but no inward)
#   -npeonly
#       Only export NPE sourced postcodes, averaged if several for a postcode
#   -postboxonly
#       Only export dracos postbox data
#   -all
#       Outputs all postcodes, averaged if several for a postcode, along
#       with their source

use strict;
use DBI;

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

my $mode = shift;
unless($mode) {
	die("Use:\n\texport.pl <-gpsonly|-outer|-outer1|-npeonly|-all>\n");
}

# 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 "# <outward>,<inward>,<easting>,<northing>,<WGS84 lat>,<WGS84 long>,<2+6 NGR>,<grid>,<sources>\n";

# Get our data fetch query
my $query = getQuery($mode,$conn);

# Process the query
$query->execute;
while(my ($outer,$inner,$e,$n,$ie_e,$ie_n,$source) = $query->fetchrow_array) {
	# What grid are we on?
	my $grid = "osgb";
	if(!$e && $ie_e) {
		$grid = "osie";
		$e = $ie_e;
		$n = $ie_n;
	}
	
	eval {
		# Generate lat+long
		my ($lat,$long) = eastingNorthingToLatLong($e,$n,$grid);
		# Generate 2+6 NGR
		my $ngr = eastingNorthingToNGR($e,$n,$grid);

		# Make e+n look nicer
		$e =~ s/^(\d+)\.\d+$/$1/;
		$n =~ s/^(\-?\d+)\.\d+$/$1/;

		# Make lat+long look nicer
		$lat = sprintf("%0.6f", $lat);
		$long = sprintf("%0.6f", $long);

		# Convert source from array ref to CSV
		$source = join(",", @$source);
		print "$outer,$inner,$e,$n,$lat,$long,$ngr,$grid,$source\n";
	};
	if ($@) {
           print STDERR "Error processing $outer $inner\n -- $@";
	};
}

# Close down
$conn->disconnect;


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

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

	if($mode eq "-gpsonly" || $mode eq "-npeonly" || $mode eq "-postboxonly" || $mode eq "-all") {
		my $source = -1;
		if($mode eq "-gpsonly") {
			$source = getSource("FreeThePostcode.org Importer", $conn);
		} elsif($mode eq "-postboxonly") {
			$source = getSource("Dracos.co.uk Postbox Importer", $conn);
		} elsif($mode eq "-npeonly") {
			$source = getSource("NPE Postcode web submission", $conn);
		}

		my $source_sql = "";
		if($source >= 0) {
			$source_sql = " AND source = ? ";
		}

		my $sql = 
			"SELECT outward, inward, AVG(easting) AS avg_easting, AVG(northing) AS avg_northing, AVG(ie_easting) AS avg_easting_ie, AVG(ie_northing) AS avg_northing_ie, to_array(DISTINCT sources.name) AS sources ".
			"FROM postcodes   ".
			"INNER JOIN sources ON (postcodes.source = sources.id) ".
			"WHERE NOT deleted ".$source_sql.
			"GROUP BY outward, inward ".
			"ORDER BY outward, inward ";

		my $sth = $conn->prepare($sql);
		if($source >= 0) {
			$sth->bind_param(1, $source);
		}
		return $sth;
	} elsif($mode eq "-outer") {
		my $sql = 
			"SELECT outward, '###' AS inward, AVG(easting) AS avg_easting, AVG(northing) AS avg_northing, AVG(ie_easting) AS avg_easting_ie, AVG(ie_northing) AS avg_northing_ie, to_array(DISTINCT sources.name) AS sources ".
			"FROM postcodes ".
			"INNER JOIN sources ON (postcodes.source = sources.id) ".
			"WHERE NOT deleted ".
			"GROUP BY outward ".
			"ORDER BY outward ";

		my $sth = $conn->prepare($sql);
		return $sth;
	} elsif($mode eq "-outer1") {
		# Not - will exclude data where we only have an outward, and no inward
		my $sql = 
			"SELECT outward, substr(inward,1,1) || '##' AS inward, AVG(easting) AS avg_easting, AVG(northing) AS avg_northing, AVG(ie_easting) AS avg_easting_ie, AVG(ie_northing) AS avg_northing_ie, to_array(DISTINCT sources.name) AS sources ".
			"FROM postcodes ".
			"INNER JOIN sources ON (postcodes.source = sources.id) ".
			"WHERE NOT deleted AND length(inward) > 0 ".
			"GROUP BY outward, substr(inward,1,1) ".
			"ORDER BY outward, inward";

		my $sth = $conn->prepare($sql);
		return $sth;
	} else {
		die("Invalid mode '$mode'");
	}
}

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];	
}
