source: trunk/npemap.org.uk/scripts/process-problems @ 328

#!/usr/bin/perl

use strict;
use warnings;

use FindBin;

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

# Optionally, limit by the postcode ID
my $postcode_id = shift;
my $postcode_where = "";
if($postcode_id && $postcode_id =~ /^\d+$/) {
        $postcode_where = " AND p.id = $postcode_id ";
}

# Get the postcodes
my $dbh = setup_dbh() or die $!;

my $sql = <<EOF;
SELECT   p.id, outward, inward, p.ip AS sub_ip, b.ip AS prob_ip, b.reason,
         reporter_email, p.created_at AS sub_date, b.created_at AS prob_date,
         p.easting, p.northing
FROM     bad_postcodes AS b INNER JOIN postcodes AS p ON (b.postcode = p.id)
WHERE    not b.actioned   $postcode_where
ORDER BY p.id, b.created_at
EOF

my $sth = $dbh->prepare($sql);
$sth->execute or die $dbh->errstr;

if ($sth->rows == 0) {
    print "No problems\n";
    exit 0;
}

# Build up a list of allowed IDs to delete
my %probids;

my $hr;
while ($hr = $sth->fetchrow_hashref) {
    $probids{$hr->{'id'}}++;
    $hr->{'reporter_email'} = 'anon' unless $hr->{'reporter_email'};
    $hr->{'prob_ip'} = 'unknown IP' unless $hr->{'prob_ip'};
    $hr->{'sub_ip'} = '' unless $hr->{'sub_ip'};
    print $hr->{'id'} . ': ' . $hr->{'outward'} . ' ' . $hr->{'inward'} . ': ';
    print "by " . $hr->{'reporter_email'};
    print " at " . $hr->{'prob_ip'};
    print ' (SAME IP)' if ($hr->{'prob_ip'} eq $hr->{'sub_ip'});
    print "\n";
    print " ";
    print "Reason: " . $hr->{'reason'} . ' ' if $hr->{'reason'};
    print "(". $hr->{'prob_date'} . ")\n";
    print " http://www.npemap.org.uk/tiles/map.html#" .
        int($hr->{'easting'} / 1000) . ',' . int($hr->{'northing'} / 1000) . ",1\n";
}

my $delsth = $dbh->prepare("UPDATE postcodes SET deleted = 't', delete_reason = 1 WHERE id = ?");
my $actionsth = $dbh->prepare("UPDATE bad_postcodes SET actioned = 't' WHERE postcode = ?");
my $updsth = $dbh->prepare("UPDATE postcodes SET outward = ?, inward = ? WHERE id = ?");

# Now prompt for deletions

print "Warning: no validation of postcodes input here\n";
while (1) {
    unless (%probids) {
        print "No more problems.\n";
        last;
    }
    print "ID to resolve? (^C to exit): ";
    my $input = <STDIN>;
    chomp $input;
    if ($probids{$input}) {
        print "d for delete or comma separated postcode to update: ";
        my $input2 = <STDIN>;
        chomp $input2;
        if ($input2 =~ /^d$/i) {
            $delsth->execute($input) or warn $dbh->errstr;
        } elsif ($input2 =~ /(\w*),(\w*)/) {
            $updsth->execute($1, $2, $input) or warn $dbh->errstr;
        } else {
            print "invalid input\n";
            next;
        }
        $actionsth->execute($input) or warn $dbh->errstr;
        delete $probids{$input};
    } else {
        print "$input is not a valid deletion candidate.\n";
    }
}

$dbh->disconnect;