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 | # Optionally, limit by the postcode ID |
---|
13 | my $postcode_id = shift; |
---|
14 | my $postcode_where = ""; |
---|
15 | if($postcode_id && $postcode_id =~ /^\d+$/) { |
---|
16 | $postcode_where = " AND p.id = $postcode_id "; |
---|
17 | } |
---|
18 | |
---|
19 | # Get the postcodes |
---|
20 | my $dbh = setup_dbh() or die $!; |
---|
21 | |
---|
22 | my $sql = <<EOF; |
---|
23 | SELECT p.id, outward, inward, p.ip AS sub_ip, b.ip AS prob_ip, b.reason, |
---|
24 | reporter_email, p.created_at AS sub_date, b.created_at AS prob_date, |
---|
25 | p.easting, p.northing |
---|
26 | FROM bad_postcodes AS b INNER JOIN postcodes AS p ON (b.postcode = p.id) |
---|
27 | WHERE not b.actioned $postcode_where |
---|
28 | ORDER BY p.id, b.created_at |
---|
29 | EOF |
---|
30 | |
---|
31 | my $sth = $dbh->prepare($sql); |
---|
32 | $sth->execute or die $dbh->errstr; |
---|
33 | |
---|
34 | if ($sth->rows == 0) { |
---|
35 | print "No problems\n"; |
---|
36 | exit 0; |
---|
37 | } |
---|
38 | |
---|
39 | # Build up a list of allowed IDs to delete |
---|
40 | my %probids; |
---|
41 | |
---|
42 | my $hr; |
---|
43 | while ($hr = $sth->fetchrow_hashref) { |
---|
44 | $probids{$hr->{'id'}}++; |
---|
45 | $hr->{'reporter_email'} = 'anon' unless $hr->{'reporter_email'}; |
---|
46 | $hr->{'prob_ip'} = 'unknown IP' unless $hr->{'prob_ip'}; |
---|
47 | $hr->{'sub_ip'} = '' unless $hr->{'sub_ip'}; |
---|
48 | print $hr->{'id'} . ': ' . $hr->{'outward'} . ' ' . $hr->{'inward'} . ': '; |
---|
49 | print "by " . $hr->{'reporter_email'}; |
---|
50 | print " at " . $hr->{'prob_ip'}; |
---|
51 | print ' (SAME IP)' if ($hr->{'prob_ip'} eq $hr->{'sub_ip'}); |
---|
52 | print "\n"; |
---|
53 | print " "; |
---|
54 | print "Reason: " . $hr->{'reason'} . ' ' if $hr->{'reason'}; |
---|
55 | print "(". $hr->{'prob_date'} . ")\n"; |
---|
56 | print " http://www.npemap.org.uk/tiles/map.html#" . |
---|
57 | int($hr->{'easting'} / 1000) . ',' . int($hr->{'northing'} / 1000) . ",1\n"; |
---|
58 | } |
---|
59 | |
---|
60 | my $delsth = $dbh->prepare("UPDATE postcodes SET deleted = 't', delete_reason = 1 WHERE id = ?"); |
---|
61 | my $actionsth = $dbh->prepare("UPDATE bad_postcodes SET actioned = 't' WHERE postcode = ?"); |
---|
62 | my $updsth = $dbh->prepare("UPDATE postcodes SET outward = ?, inward = ? WHERE id = ?"); |
---|
63 | |
---|
64 | # Now prompt for deletions |
---|
65 | |
---|
66 | print "Warning: no validation of postcodes input here\n"; |
---|
67 | while (1) { |
---|
68 | unless (%probids) { |
---|
69 | print "No more problems.\n"; |
---|
70 | last; |
---|
71 | } |
---|
72 | print "ID to resolve? (^C to exit): "; |
---|
73 | my $input = <STDIN>; |
---|
74 | chomp $input; |
---|
75 | if ($probids{$input}) { |
---|
76 | print "d for delete or comma separated postcode to update: "; |
---|
77 | my $input2 = <STDIN>; |
---|
78 | chomp $input2; |
---|
79 | if ($input2 =~ /^d$/i) { |
---|
80 | $delsth->execute($input) or warn $dbh->errstr; |
---|
81 | } elsif ($input2 =~ /(\w*),(\w*)/) { |
---|
82 | $updsth->execute($1, $2, $input) or warn $dbh->errstr; |
---|
83 | } else { |
---|
84 | print "invalid input\n"; |
---|
85 | next; |
---|
86 | } |
---|
87 | $actionsth->execute($input) or warn $dbh->errstr; |
---|
88 | delete $probids{$input}; |
---|
89 | } else { |
---|
90 | print "$input is not a valid deletion candidate.\n"; |
---|
91 | } |
---|
92 | } |
---|
93 | |
---|
94 | $dbh->disconnect; |
---|