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

Last change on this file since 318 was 318, checked in by Dominic Hargreaves, 15 years ago

Add script to help process problem postcodes

  • Property svn:executable set to *
File size: 1.8 KB
Line 
1#!/usr/bin/perl
2
3use strict;
4use warnings;
5
6use FindBin;
7
8# Find our private perl libraries
9use lib "$FindBin::Bin/../perllib";
10use NPEMap;
11
12my $dbh = setup_dbh() or die $!;
13
14my $sql = <<EOF;
15SELECT   p.id, outward, inward, p.ip AS sub_ip, b.ip AS prob_ip, b.reason,
16         reporter_email, p.created_at AS sub_date, b.created_at AS prob_date,
17         p.easting, p.northing
18FROM     bad_postcodes AS b INNER JOIN postcodes AS p ON (b.postcode = p.id)
19WHERE    not b.actioned
20ORDER BY p.id, b.created_at
21EOF
22
23my $sth = $dbh->prepare($sql);
24$sth->execute or die $dbh->errstr;
25
26if ($sth->rows == 0) {
27    print "No problems\n";
28    exit 0;
29}
30
31# Build up a list of allowed IDs to delete
32my %probids;
33
34my $hr;
35while ($hr = $sth->fetchrow_hashref) {
36    $probids{$hr->{'id'}}++;
37    print $hr->{'id'} . ': ' . $hr->{'outward'} . ' ' . $hr->{'inward'} . ': ';
38    print "by " . $hr->{'reporter_email'} || 'anon';
39    print " at " . $hr->{'prob_ip'} || 'unknown IP';
40    print ' (SAME IP)' if ($hr->{'prob_ip'} eq $hr->{'sub_ip'});
41    print "\n";
42    print " http://www.npemap.org.uk/tiles/map.html#" .
43        int($hr->{'easting'} / 1000) . ',' . int($hr->{'northing'} / 1000) . ",1\n";
44}
45
46my $del1sth = $dbh->prepare("UPDATE postcodes SET deleted = 't', delete_reason = 1 WHERE id = ?");
47my $del2sth = $dbh->prepare("UPDATE bad_postcodes SET actioned = 't' WHERE postcode = ?");
48
49# Now prompt for deletions
50while (1) {
51    unless (%probids) {
52        print "No more problems.\n";
53        last;
54    }
55    print "Resolve problem by deleting (^C to exit): ";
56    my $input = <STDIN>;
57    chomp $input;
58    if ($probids{$input}) {
59        $del1sth->execute($input) or warn $dbh->errstr;
60        $del2sth->execute($input) or warn $dbh->errstr;
61        delete $probids{$input};
62    } else {
63        print "$input is not a valid deletion candidate.\n";
64    }
65}
66
67$dbh->disconnect;
Note: See TracBrowser for help on using the repository browser.