Changeset 690

Timestamp:

Sep 5, 2009, 2:25:35 PM (12 years ago)

Author:

Dominic Hargreaves

Message:

first stab at problem reporting

File:

• trunk/npemap.org.uk/scripts/report-foreign-deletions (copied) (copied from trunk/npemap.org.uk/scripts/process-problems) (3 diffs)

trunk/npemap.org.uk/scripts/report-foreign-deletions

@@ -1 +1 @@
 #!/usr/bin/perl
 #
-# Copyright (c) 2006-2008 Dominic Hargreaves
+# Copyright (c) 2006-2009 Dominic Hargreaves
 # Permission is hereby granted, free of charge, to any person obtaining a
 # copy of this software and associated documentation files (the "Software"),
@@ -25 +25 @@
 use FindBin;
 use AppConfig qw(:argcount :expand);
+use MIME::Lite;
 
 # Find our private perl libraries
 use lib "$FindBin::Bin/../perllib";
 use NPEMap;
-
-my $config =  AppConfig->new(
-    {   GLOBAL => {
-            ARGCOUNT => ARGCOUNT_ONE,
-            EXPAND   => EXPAND_ALL
-        }
-    },
-    'sameonly|s!',
-    'quiet|q!',
-    'report|r!',
-);
-
-$config->args;
-
-# Optionally, limit by the postcode ID
-my $postcode_id = shift;
-my $postcode_where = "";
-if($postcode_id && $postcode_id =~ /^\d+$/) {
-        $postcode_where = " AND p.id = $postcode_id ";
-}
-
-$postcode_where .= " AND p.ip = b.ip " if $config->sameonly;
+use NPEMap::Config;
 
 # Get the postcodes
@@ -56 +36 @@
 
 my $sql = <<EOF;
-SELECT   p.id, outward, inward, p.ip AS sub_ip, b.ip AS prob_ip, b.reason,
-         reporter_email, p.created_at AS sub_date, b.created_at AS prob_date,
-         p.easting, p.northing, s.name AS source_name
-FROM     bad_postcodes AS b INNER JOIN postcodes AS p ON (b.postcode = p.id)
-         INNER JOIN sources AS s ON (p.source = s.id)
-WHERE    not b.actioned   $postcode_where
-ORDER BY p.id, b.created_at
+SELECT 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;
 EOF
 
 my $sth = $dbh->prepare($sql);
-$sth->execute or die $dbh->errstr;
 
-if ($sth->rows == 0) {
-    print "No problems\n" unless $config->quiet;
-    exit 0;
-}
+my $source_sth = $dbh->prepare('SELECT id,name,contact FROM sources WHERE contact IS NOT NULL');
+$source_sth->execute or die $dbh->errstr;
 
-# Build up a list of allowed IDs to delete
-my %probids;
+my $source;
+while ($source = $source_sth->fetchrow_hashref) {
+    $sth->execute($source->{id});
+    
+    my @problems = @{$sth->fetchall_arrayref};
 
-my $hr;
-while ($hr = $sth->fetchrow_hashref) {
-    $probids{$hr->{'id'}}++;
-    $hr->{'reporter_email'} = 'anon' unless $hr->{'reporter_email'};
-    $hr->{'prob_ip'} = 'unknown IP' unless $hr->{'prob_ip'};
-    $hr->{'sub_ip'} = '' unless $hr->{'sub_ip'};
-    print $hr->{'id'} . ': ' . $hr->{'outward'} . ' ' . $hr->{'inward'} . ': ';
-    print "by " . $hr->{'reporter_email'};
-    print " at " . $hr->{'prob_ip'};
-    print ' (SAME IP)' if ($hr->{'prob_ip'} eq $hr->{'sub_ip'});
-    print "\n";
-    print " ";
-    print "Reason: " . $hr->{'reason'} . ' ' if $hr->{'reason'};
-    print "(". $hr->{'prob_date'} . ")\n";
-    print " http://www.npemap.org.uk/tiles/map.html#" .
-        int($hr->{'easting'} / 1000) . ',' . int($hr->{'northing'} / 1000) . ",1\n";
-    print " Source: " . $hr->{'source_name'} . "\n";
-}
+        if (@problems) {
 
-my $delsth = $dbh->prepare("UPDATE postcodes SET deleted = 't', delete_reason = 1 WHERE id = ?");
-my $actionsth = $dbh->prepare("UPDATE bad_postcodes SET actioned = 't' WHERE postcode = ?");
-my $updsth = $dbh->prepare("UPDATE postcodes SET outward = ?, inward = ? WHERE id = ?");
+        my $body = <<EOF;
+Some postcodes that we got from
+$source->{name}
+have been reported as bad on npemap.org.uk:
 
-# Now prompt for deletions
+NPEmap ID,postcode,easting,northing,ie_easting_ie_northing,reason
+EOF
 
-exit if $config->report;
+        $body .= join ',', @{$_} foreach (@problems);
 
-print "Warning: no validation of postcodes input here\n";
-while (1) {
-    unless (%probids) {
-        print "No more problems.\n" unless $config->quiet;
-        last;
-    }
-    print "ID to resolve? (^C to exit): ";
-    my $input = <STDIN>;
-    chomp $input;
-    if ($probids{$input}) {
-        print "d for delete, i to ignore, comma separated postcode to update: ";
-        my $input2 = <STDIN>;
-        chomp $input2;
-        if ($input2 =~ /^d$/i) {
-            $delsth->execute($input) or warn $dbh->errstr;
-        } elsif ($input2 =~ /^i$/i) {
-            # Nothing. We'll mark it as actioned below
-        } elsif ($input2 =~ /(\w*),(\w*)/) {
-            $updsth->execute($1, $2, $input) or warn $dbh->errstr;
-        } else {
-            print "invalid input\n";
-            next;
-        }
-        $actionsth->execute($input) or warn $dbh->errstr;
-        delete $probids{$input};
-    } else {
-        print "$input is not a valid deletion candidate.\n";
-    }
-}
+        my $msg = MIME::Lite->new(
+            From    => $adminemail,
+            To      => $source->{contact},
+            Subject => 'Problem postcodes reported on npemap.org.uk',
+            Data    => $body
+        );
+        $msg->send;
+
 
 $dbh->disconnect;