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