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

Last change on this file since 635 was 635, checked in by Dominic Hargreaves, 13 years ago

licence tastic

  • Property svn:executable set to *
File size: 4.5 KB
Line 
1#!/usr/bin/perl
2#
3# Copyright (c) 2006-2008 Dominic Hargreaves
4# Permission is hereby granted, free of charge, to any person obtaining a
5# copy of this software and associated documentation files (the "Software"),
6# to deal in the Software without restriction, including without limitation
7# the rights to use, copy, modify, merge, publish, distribute, sublicense,
8# and/or sell copies of the Software, and to permit persons to whom the
9# Software is furnished to do so, subject to the following conditions:
10#
11# The above copyright notice and this permission notice shall be included in
12# all copies or substantial portions of the Software.
13#
14# THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
15# IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
16# FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL
17# THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
18# LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING
19# FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS
20# IN THE SOFTWARE.
21#
22use strict;
23use warnings;
24
25use FindBin;
26use AppConfig qw(:argcount :expand);
27
28# Find our private perl libraries
29use lib "$FindBin::Bin/../perllib";
30use NPEMap;
31
32my $config =  AppConfig->new(
33    {   GLOBAL => {
34            ARGCOUNT => ARGCOUNT_ONE,
35            EXPAND   => EXPAND_ALL
36        }
37    },
38    'sameonly|s!',
39    'quiet|q!',
40    'report|r!',
41);
42
43$config->args;
44
45# Optionally, limit by the postcode ID
46my $postcode_id = shift;
47my $postcode_where = "";
48if($postcode_id && $postcode_id =~ /^\d+$/) {
49        $postcode_where = " AND p.id = $postcode_id ";
50}
51
52$postcode_where .= " AND p.ip = b.ip " if $config->sameonly;
53
54# Get the postcodes
55my $dbh = setup_dbh() or die $!;
56
57my $sql = <<EOF;
58SELECT   p.id, outward, inward, p.ip AS sub_ip, b.ip AS prob_ip, b.reason,
59         reporter_email, p.created_at AS sub_date, b.created_at AS prob_date,
60         p.easting, p.northing, s.name AS source_name
61FROM     bad_postcodes AS b INNER JOIN postcodes AS p ON (b.postcode = p.id)
62         INNER JOIN sources AS s ON (p.source = s.id)
63WHERE    not b.actioned   $postcode_where
64ORDER BY p.id, b.created_at
65EOF
66
67my $sth = $dbh->prepare($sql);
68$sth->execute or die $dbh->errstr;
69
70if ($sth->rows == 0) {
71    print "No problems\n" unless $config->quiet;
72    exit 0;
73}
74
75# Build up a list of allowed IDs to delete
76my %probids;
77
78my $hr;
79while ($hr = $sth->fetchrow_hashref) {
80    $probids{$hr->{'id'}}++;
81    $hr->{'reporter_email'} = 'anon' unless $hr->{'reporter_email'};
82    $hr->{'prob_ip'} = 'unknown IP' unless $hr->{'prob_ip'};
83    $hr->{'sub_ip'} = '' unless $hr->{'sub_ip'};
84    print $hr->{'id'} . ': ' . $hr->{'outward'} . ' ' . $hr->{'inward'} . ': ';
85    print "by " . $hr->{'reporter_email'};
86    print " at " . $hr->{'prob_ip'};
87    print ' (SAME IP)' if ($hr->{'prob_ip'} eq $hr->{'sub_ip'});
88    print "\n";
89    print " ";
90    print "Reason: " . $hr->{'reason'} . ' ' if $hr->{'reason'};
91    print "(". $hr->{'prob_date'} . ")\n";
92    print " http://www.npemap.org.uk/tiles/map.html#" .
93        int($hr->{'easting'} / 1000) . ',' . int($hr->{'northing'} / 1000) . ",1\n";
94    print " Source: " . $hr->{'source_name'} . "\n";
95}
96
97my $delsth = $dbh->prepare("UPDATE postcodes SET deleted = 't', delete_reason = 1 WHERE id = ?");
98my $actionsth = $dbh->prepare("UPDATE bad_postcodes SET actioned = 't' WHERE postcode = ?");
99my $updsth = $dbh->prepare("UPDATE postcodes SET outward = ?, inward = ? WHERE id = ?");
100
101# Now prompt for deletions
102
103exit if $config->report;
104
105print "Warning: no validation of postcodes input here\n";
106while (1) {
107    unless (%probids) {
108        print "No more problems.\n" unless $config->quiet;
109        last;
110    }
111    print "ID to resolve? (^C to exit): ";
112    my $input = <STDIN>;
113    chomp $input;
114    if ($probids{$input}) {
115        print "d for delete, i to ignore, comma separated postcode to update: ";
116        my $input2 = <STDIN>;
117        chomp $input2;
118        if ($input2 =~ /^d$/i) {
119            $delsth->execute($input) or warn $dbh->errstr;
120        } elsif ($input2 =~ /^i$/i) {
121            # Nothing. We'll mark it as actioned below
122        } elsif ($input2 =~ /(\w*),(\w*)/) {
123            $updsth->execute($1, $2, $input) or warn $dbh->errstr;
124        } else {
125            print "invalid input\n";
126            next;
127        }
128        $actionsth->execute($input) or warn $dbh->errstr;
129        delete $probids{$input};
130    } else {
131        print "$input is not a valid deletion candidate.\n";
132    }
133}
134
135$dbh->disconnect;
Note: See TracBrowser for help on using the repository browser.