Merge remote-tracking branch 'origin/master'

3 files changed, 283 insertions, 2 deletions

diff --git a/bin/oxfordshire/open311_service_request_update.cgi b/bin/oxfordshire/open311_service_request_update.cgi
new file mode 100755
index 000000000..9dda434a7
--- /dev/null
+++ b/bin/oxfordshire/open311_service_request_update.cgi
@@ -0,0 +1,125 @@
+#!/usr/bin/perl
+
+# script for querying the higatlas.fms_update table provided by
+# Bentley and offering them up as XML service request updates.
+# https://github.com/mysociety/fixmystreet/wiki/Open311-FMS---Proposed-differences-to-Open311
+#
+# mySociety: http://code.fixmystreet.com/
+#-----------------------------------------------------------------
+
+require 'open311_services.pm';
+
+# incoming query params
+my $CGI_VAR_START_DATE = 'start_date';
+my $CGI_VAR_END_DATE   = 'end_date';
+my $CGI_VAR_NO_DEFAULT_DATE   = 'force_no_default_date'; # for testing scratchy Oracle date stuff
+my $CGI_VAR_LIMIT   = 'limit'; # for testing
+my $CGI_VAR_ANY_STATUS = 'any_status'; # for testing
+
+my $USE_ONLY_DATES = 0;  # dates not times
+my $MAX_LIMIT = 1000;
+my $STATUS_CRITERIA = "(status='OPEN' OR status='CLOSED')";
+my $req = new CGI;
+
+get_service_request_updates($req);
+
+sub prepare_for_xml {
+    my $s = shift;
+    foreach ($s) {
+        from_to($_, 'utf8', 'Windows-1252') if $DECODE_FROM_WIN1252;
+        s/</&lt;/g; # numpty escaping pending XML Simple?
+        s/>/&gt;/g;
+        s/&/&amp;/g;
+    }
+    return $s;
+}
+
+#------------------------------------------------------------------
+# get_service_discovery
+# Although not really implementing this, use it as a test to read the 
+# db and confirm connectivity.
+#
+# TABLE "HIGATLAS"."FMS_UPDATE"
+# 
+#         "ROW_ID"                                NUMBER(9,0) NOT NULL ENABLE,
+#         "SERVICE_REQUEST_ID"    NUMBER(9,0) NOT NULL ENABLE,
+#         "UPDATED_TIMEDATE"              DATE DEFAULT SYSDATE NOT NULL ENABLE,
+#         "STATUS"                                VARCHAR2(10 BYTE) NOT NULL ENABLE,
+#         "DESCRIPTION"                   VARCHAR2(254 BYTE) NOT NULL ENABLE,
+# 
+#          CONSTRAINT "FMS_UPDATE_PK" PRIMARY KEY ("ROW_ID")
+#------------------------------------------------------------------
+sub get_service_request_updates {
+    # by default, we only want last 24 hours
+    # also, limit to 1000 records
+    
+    my $raw_start_date = $req -> param($CGI_VAR_START_DATE);
+    my $raw_end_date = $req -> param($CGI_VAR_END_DATE);
+    my $start_date = get_date_or_nothing( $raw_start_date, $USE_ONLY_DATES );
+    my $end_date = get_date_or_nothing( $raw_end_date, $USE_ONLY_DATES );
+
+    if (! $req -> param($CGI_VAR_NO_DEFAULT_DATE)) {
+        $start_date = get_date_or_nothing( $YESTERDAY, $USE_ONLY_DATES ) unless ($start_date or $end_date);
+    }
+
+    my $date_format = 'YYYY-MM-DD HH24:MI:SS'; # NB: hh24 (not hh)
+
+    $start_date = "updated_timedate >= to_date('$start_date', '$date_format')" if $start_date;
+    $end_date = "updated_timedate <= to_date('$end_date', '$date_format')" if $end_date;
+
+    my $where_clause = '';
+    my @criteria = ($start_date, $end_date);
+    push @criteria, $STATUS_CRITERIA  unless $req -> param($CGI_VAR_ANY_STATUS);
+    $where_clause = join(' AND ', grep {$_} @criteria);
+    $where_clause = "WHERE $where_clause" if $where_clause;
+
+    my $sql = qq(SELECT row_id, service_request_id, to_char(updated_timedate, '$date_format'), status, description FROM higatlas.fms_update $where_clause ORDER BY updated_timedate DESC);
+
+    my $limit = $req -> param($CGI_VAR_LIMIT) =~ /^(\d{1,3})$/? $1 : $MAX_LIMIT;
+    $sql = "SELECT * FROM ($sql) WHERE ROWNUM <= $limit" if $limit;
+
+    my $debug_str = <<XML;
+        <!-- DEBUG: from: $raw_start_date => $start_date  -->
+        <!-- DEBUG: to:   $raw_end_date => $end_date -->
+        <!-- DEBUG: sql:  $sql -->
+XML
+
+    my $ary_ref;
+
+    if ($TESTING_WRITE_TO_FILE) {
+        $ary_ref = [
+            [97, 1000, '2013-01-05', 'OPEN', 'report was opened'],
+            [99, 1000, '2013-01-06', 'CLOSED', 'report was closed']
+        ];
+    } else {
+        my $dbh = get_db_connection();
+        $ary_ref = $dbh->selectall_arrayref($sql);
+    }
+
+    # rough and ready XML dump now (should use XML Simple to build/escape properly!)
+    my $xml = "";
+    foreach my $row(@{$ary_ref})  {
+        if (defined $row) {
+            my ($id, $service_req_id, $updated_at, $status, $desc) = map { prepare_for_xml($_) } @$row;
+            $updated_at=~s/(\d{4}-\d\d-\d\d) (\d\d:\d\d:\d\d)/${1}T${2}Z/; # for now assume OCC in Zulu time
+            $xml.= <<XML;
+    <request_update>
+        <update_id>$id</update_id>
+        <service_request_id>$service_req_id</service_request_id>
+        <status>$status</status>
+        <updated_datetime>$updated_at</updated_datetime>
+        <description>$desc</description>
+    </request_update>
+XML
+        }
+    }
+    print <<XML;
+Content-type: text/xml
+
+<?xml version="1.0" encoding="utf-8"?>
+<service_request_updates>
+$xml
+</service_request_updates>
+$debug_str
+XML
+}
diff --git a/bin/oxfordshire/open311_services.pm b/bin/oxfordshire/open311_services.pm
new file mode 100644
index 000000000..41b3e7153
--- /dev/null
+++ b/bin/oxfordshire/open311_services.pm
@@ -0,0 +1,153 @@
+#!/usr/bin/perl
+#
+# common stuff used by Oxfordshire Open311 glue scripts
+#
+# mySociety: http://code.fixmystreet.com/
+#-----------------------------------------------------------------
+
+use strict;
+use CGI;
+use Encode qw(from_to);
+use DBI;
+use Time::Piece;
+use DBD::Oracle qw(:ora_types);
+### for local testing (no Oracle): 
+### use constant { ORA_VARCHAR2=>1, ORA_DATE=>1, ORA_NUMBER=>1};
+
+
+###################################################################
+# Config file: values in the config file override any values set 
+#              in the code below for the following things:
+#
+#     host: SXX-SAN-FOO_BAR
+#     sid: FOOBAR
+#     port: 1531
+#     username: foo
+#     password: FooBar
+#     testing: 0
+#     encode-to-win1252: 1
+#
+# Absence of the config file fails silently in case you really are
+# using values directly set in this script.
+#------------------------------------------------------------------
+our $CONFIG_FILENAME       = "/usr/local/etc/fixmystreet.config";
+
+use constant {
+    GENERAL_SERVICE_ERROR   => 400,
+    CODE_OR_ID_NOT_FOUND    => 404,
+    CODE_OR_ID_NOT_PROVIDED => 400,
+    BAD_METHOD              => 405,
+    FATAL_ERROR             => 500
+};
+
+our $DB_SERVER_NAME    = 'FOO';
+our $DB_HOST           = $DB_SERVER_NAME; # did this just in case we need to add more to the name (e.g, domain)
+our $DB_PORT           = '1531';
+our $ORACLE_SID        = 'FOOBAR';
+our $USERNAME          = 'FIXMYSTREET';
+our $PASSWORD          = 'XXX';
+our $STORED_PROC_NAME  = 'PEM.create_enquiry';
+
+# NB can override these settings in the config file!
+
+# Strip control chars:
+#   'ruthless' removes everything (i.e. all POSIX control chars)
+#   'desc'     removes everything, but keeps tabs and newlines in the 'description' field, where they matter
+#   'normal'   keeps tabs and newlines 
+our $STRIP_CONTROL_CHARS   = 'ruthless';  
+
+our $ENCODE_TO_WIN1252      = 1; # force encoding to Win-1252 for PEM data
+our $DECODE_FROM_WIN1252    = 1; # force encoding from Win-1252 for PEM data
+
+our $TESTING_WRITE_TO_FILE  = 0;  # write to file instead of DB or (get_service_request_update) don't really read the db
+our $OUT_FILENAME           = "fms-test.txt"; # dump data here if TESTING_WRITE_TO_FILE is true
+our $TEST_SERVICE_DISCOVERY = 0;  # switch to 1 to run service discovery, which confirms the DB connection at least
+our $RUN_FAKE_INSERT_TEST   = 0;  # command-line execution attempts insert with fake data (mimics a POST request)
+
+# Config file overrides existing values for these, if present:
+if ($CONFIG_FILENAME && open(CONF, $CONFIG_FILENAME)) {
+    while (<CONF>) {
+        next if /^#/;
+        if (/^\s*password:\s*(\S+)\s*$/i) {
+            $PASSWORD = $1;
+        } elsif (/^\s*sid:\s*(\S+)\s*$/i) {
+            $ORACLE_SID = $1;
+        } elsif (/^\s*username:\s*(\S+)\s*$/i) {
+            $USERNAME = $1;
+        } elsif (/^\s*port:\s*(\S+)\s*$/i) {
+            $DB_PORT = $1;
+        } elsif (/^\s*host:\s*(\S+)\s*$/i) {
+            $DB_HOST = $1;
+        } elsif (/^\s*testing:\s*(\S+)\s*$/i) {
+            $TESTING_WRITE_TO_FILE = $1;
+        } elsif (/^\s*test-service-discovery:\s*(\S+)\s*$/i) {
+            $TEST_SERVICE_DISCOVERY = $1;
+        } elsif (/^\s*strip-control-chars:\s*(\S+)\s*$/i) {
+            $STRIP_CONTROL_CHARS = lc $1;
+        } elsif (/^\s*encode-to-win1252:\s*(\S+)\s*$/i) {
+            $ENCODE_TO_WIN1252 = $1;
+        } elsif (/^\s*decode-from-win1252:\s*(\S+)\s*$/i) {
+            $DECODE_FROM_WIN1252 = $1;
+        } elsif (/^\s*run-fake-insert-test:\s*(\S+)\s*$/i) {
+            $RUN_FAKE_INSERT_TEST = $1;
+        }
+    }
+}
+
+our $YESTERDAY = localtime() - Time::Seconds::ONE_DAY; # yesterday
+$YESTERDAY = $YESTERDAY->strftime('%Y-%m-%d');
+
+#------------------------------------------------------------------
+# error_and_exit 
+# args: HTTP status code, error message
+# Sends out the HTTP status code and message
+# and temrinates execution
+#------------------------------------------------------------------
+sub error_and_exit {
+    my ($status, $msg) = @_;
+    print "Status: $status $msg\n\n$msg\n";
+    exit;
+}
+
+
+#------------------------------------------------------------------
+# get_db_connection
+# no args: uses globals, possibly read from config
+# returns handle for the connection (otherwise terminates)
+#------------------------------------------------------------------
+sub get_db_connection {
+    return DBI->connect( "dbi:Oracle:host=$DB_HOST;sid=$ORACLE_SID;port=$DB_PORT", $USERNAME, $PASSWORD )
+        or error_and_exit(FATAL_ERROR, "failed to connect to database: " . $DBI::errstr, "");   
+}
+
+#------------------------------------------------------------------
+# get_date_or_nothing {
+# parse date from incoming request, fail silently
+# expected format: 2003-02-15T13:50:05
+# These are coming from FMS for Oxford so don't expect to need
+# need to parse anyway
+#------------------------------------------------------------------
+sub get_date_or_nothing {
+    my $d = shift;
+    my $want_date_only = shift;
+    if ($d=~s/^(\d{4}-\d\d-\d\d)(T\d\d:\d\d(:\d\d)?)?.*/$1$2/) {
+        return $1 if $want_date_only;
+        $d="$1 00:00" unless $2; # no time provided
+        $d.=":00" unless $3; # no seconds
+        $d=~s/[TZ]/ /g;
+        # no point doing any parsing if regexp has done the work
+            # eval {
+            #     $d=~s/(\d\d:\d\d).*/$1/; # bodge if we can't get DateTime installed
+            #     $d = Time::Piece->strptime( $d, '%Y-%m-%dT%H:%M:%S');
+            #     $d = $d->strftime('%Y-%m-%d %H:%M:%S');
+            # };
+            # return '' if $@;
+    } else {
+        return '';
+    }
+    return $d;
+}
+
+
+
+1;
\ No newline at end of file
diff --git a/bin/send-comments b/bin/send-comments
index 24b436ac8..232521447 100755
--- a/bin/send-comments
+++ b/bin/send-comments
@@ -1,7 +1,10 @@
 #!/usr/bin/env perl
 
-# send-reports:
-# Send new problem reports to councils
+# send-comments:
+# Send comments/updates on reports to councils
+#   In Open311 parlance these are 'service request udpates' and are sent using
+#   mySociety's proposed extension to the Open311 Georeport v2 spec:
+#   https://github.com/mysociety/fixmystreet/wiki/Open311-FMS---Proposed-differences-to-Open311
 #
 # Copyright (c) 2011 UK Citizens Online Democracy. All rights reserved.
 # Email: matthew@mysociety.org. WWW: http://www.mysociety.org