#!/usr/bin/perl
#
# Author:  Petter Reinholdtsen
# Date:    2007-03-31
# License: GNU General Public License
#
# Import Excel file received from the people behind
# www.partifinansiering.no into a geo-referenced SQL database.
#
# Based on example code from Spreadsheet::ParseExcel

use strict;
use warnings;
use Spreadsheet::ParseExcel;

use DBI;
use mydatabase;

my $sum;
my $count;

my $year = 2005;

use vars qw($dbh);

$dbh = dbi_connect();

my $oBook = Spreadsheet::ParseExcel::Workbook->Parse('partifinansiering-bidragsytere.xls');
my($iR, $iC, $oWkS, $oWkC);
foreach my $oWkS (@{$oBook->{Worksheet}}) {
    print "--------- SHEET:", $oWkS->{Name}, "\n";
    for (my $iR = $oWkS->{MinRow} +3 ; # +3 to skip the headers
         defined $oWkS->{MaxRow} && $iR <= $oWkS->{MaxRow} ; $iR++) {

        my ($partinavn, $bidragsyternavn, $bidragsyteradresse, $bidragsverdi)
            = (get_cell_value($oWkS->{Cells}[$iR][0]),
               get_cell_value($oWkS->{Cells}[$iR][1]),
               get_cell_value($oWkS->{Cells}[$iR][2]),
               get_cell_value($oWkS->{Cells}[$iR][3]));

        next unless (defined $bidragsverdi && $bidragsverdi > 0);
        $sum += $bidragsverdi;
        $count++;

        for (my $iC = $oWkS->{MinCol} ;
             defined $oWkS->{MaxCol} && $iC <= $oWkS->{MaxCol} ; $iC++) {
            $oWkC = $oWkS->{Cells}[$iR][$iC];
            print "( $iR , $iC ) =>", $oWkC->Value, "\n" if($oWkC);
        }

        insert_donation($partinavn, $bidragsyternavn, $bidragsyteradresse,
                        $bidragsverdi);
    }
}

print "Bidragssum $sum\n";
print "Bidragsantall $count\n";

dbi_disconnect($dbh);

sub get_cell_value {
    my $cellref = shift;
    return $cellref->Value if ($cellref);
    return undef;
}

sub find_party {
    my $partinavn = shift;
    my $id = 1;
    my $hashref =
        $dbh->selectrow_hashref("SELECT * FROM partier WHERE partinavn = ?",
                                undef, $partinavn);
    if ($hashref->{partinavn}) {
        $id = $hashref->{partiid};
    } else {
        $dbh->do("INSERT INTO partier (partinavn) VALUES (?)", undef,
                 $partinavn);
        $id = find_party($partinavn);
    }
    return $id;
}

# Work around the inconsistent address field, make sure some
# semi-useful postcode is set.
sub split_address {
    my $addrline = shift;

    my ($addresse, $postnr, $poststed);
    if ($addrline =~ m/^Nedlagt/) {
        # No address
    } elsif ($addrline =~ m/^(\d{4}) (.+)$/) {
        $postnr = $1;
        $poststed = $2;
    } elsif ($addrline =~ m/^([^,]+),? +(\d{4}) (.+)$/) {
        $addresse = $1;
        $postnr = $2;
        $poststed = $3;
    } elsif ($addrline =~ m/^([^,]+),? +([^\d].+)$/) {
        $addresse = $1;
        $poststed = $2;
    } elsif ($addrline =~ m/^(.+)$/) {
        $poststed = $1;
    }

    return ($addresse, $postnr, $poststed);
}

sub insert_donation {
    my ($partinavn, $bidragsyternavn, $bidragsyteradresse, $bidragsverdi) = @_;
    my ($addresse, $postnr, $poststed) = split_address($bidragsyteradresse);

    my $id = find_party($partinavn);

    my $sql = "INSERT INTO donasjoner (bidragsaar, mottakerid,".
        "bidragsverdi, bidragsyternavn, bidragsyteradresse,".
        "bidragsyterpostnr, bidragsyterpoststed)".
        "VALUES (?, ?, ?, ?, ?, ?, ?)";
    my @values = ($year, $id, $bidragsverdi, $bidragsyternavn,
                  $addresse, $postnr, $poststed);
    print "V: '", join("' '", @values),"'\n";
    $dbh->do($sql, undef, @values);
}