Changeset 690 for trunk


Ignore:
Timestamp:
Sep 5, 2009, 2:25:35 PM (10 years ago)
Author:
Dominic Hargreaves
Message:

first stab at problem reporting

File:
1 copied

Legend:

Unmodified
Added
Removed
  • trunk/npemap.org.uk/scripts/report-foreign-deletions

    r686 r690  
    11#!/usr/bin/perl
    22#
    3 # Copyright (c) 2006-2008 Dominic Hargreaves
     3# Copyright (c) 2006-2009 Dominic Hargreaves
    44# Permission is hereby granted, free of charge, to any person obtaining a
    55# copy of this software and associated documentation files (the "Software"),
     
    2525use FindBin;
    2626use AppConfig qw(:argcount :expand);
     27use MIME::Lite;
    2728
    2829# Find our private perl libraries
    2930use lib "$FindBin::Bin/../perllib";
    3031use NPEMap;
    31 
    32 my $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
    46 my $postcode_id = shift;
    47 my $postcode_where = "";
    48 if($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;
     32use NPEMap::Config;
    5333
    5434# Get the postcodes
     
    5636
    5737my $sql = <<EOF;
    58 SELECT   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
    61 FROM     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)
    63 WHERE    not b.actioned   $postcode_where
    64 ORDER BY p.id, b.created_at
     38SELECT id,outward||' '||inward AS postcode,easting,northing,ie_easting,ie_northing,reason from postcodes p inner join bad_postcodes b on (p.id=b.postcode) where source=? and delete_reason=1 and deleted='t' order by id;
    6539EOF
    6640
    6741my $sth = $dbh->prepare($sql);
    68 $sth->execute or die $dbh->errstr;
    6942
    70 if ($sth->rows == 0) {
    71     print "No problems\n" unless $config->quiet;
    72     exit 0;
    73 }
     43my $source_sth = $dbh->prepare('SELECT id,name,contact FROM sources WHERE contact IS NOT NULL');
     44$source_sth->execute or die $dbh->errstr;
    7445
    75 # Build up a list of allowed IDs to delete
    76 my %probids;
     46my $source;
     47while ($source = $source_sth->fetchrow_hashref) {
     48    $sth->execute($source->{id});
     49   
     50    my @problems = @{$sth->fetchall_arrayref};
    7751
    78 my $hr;
    79 while ($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 }
     52        if (@problems) {
    9653
    97 my $delsth = $dbh->prepare("UPDATE postcodes SET deleted = 't', delete_reason = 1 WHERE id = ?");
    98 my $actionsth = $dbh->prepare("UPDATE bad_postcodes SET actioned = 't' WHERE postcode = ?");
    99 my $updsth = $dbh->prepare("UPDATE postcodes SET outward = ?, inward = ? WHERE id = ?");
     54        my $body = <<EOF;
     55Some postcodes that we got from
     56$source->{name}
     57have been reported as bad on npemap.org.uk:
    10058
    101 # Now prompt for deletions
     59NPEmap ID,postcode,easting,northing,ie_easting_ie_northing,reason
     60EOF
    10261
    103 exit if $config->report;
     62        $body .= join ',', @{$_} foreach (@problems);
    10463
    105 print "Warning: no validation of postcodes input here\n";
    106 while (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 }
     64        my $msg = MIME::Lite->new(
     65            From    => $adminemail,
     66            To      => $source->{contact},
     67            Subject => 'Problem postcodes reported on npemap.org.uk',
     68            Data    => $body
     69        );
     70        $msg->send;
     71
    13472
    13573$dbh->disconnect;
Note: See TracChangeset for help on using the changeset viewer.