diff options
Diffstat (limited to 'bin')
| -rwxr-xr-x | bin/oxfordshire/open311_service_request_update.cgi | 119 | ||||
| -rw-r--r-- | bin/oxfordshire/open311_services.pm | 153 |
2 files changed, 272 insertions, 0 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..359944d71 --- /dev/null +++ b/bin/oxfordshire/open311_service_request_update.cgi @@ -0,0 +1,119 @@ +#!/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_NO_LIMIT = 'force_no_limit'; # for testing + +my $USE_ONLY_DATES = 0; # dates not times +my $LIMIT_CLAUSE = ' LIMIT 1000'; + +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/</</g; # numpty escaping pending XML Simple? + s/>/>/g; + s/&/&/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); + } + + $start_date = "updated_timedate >= to_date('$start_date', 'yyyy-mm-dd hh:mi:ss')" if $start_date; + $end_date = "updated_timedate <= to_date('$end_date', 'yyyy-mm-dd hh:mi:ss')" if $end_date; + + my $where_clause = ''; + $where_clause = join(' AND ', grep {$_} ($start_date, $end_date)); + $where_clause = "WHERE $where_clause" if $where_clause; + + my $limit = $LIMIT_CLAUSE unless $req -> param($CGI_VAR_NO_LIMIT); + + my $sql = qq(SELECT row_id, service_request_id, to_char(updated_timedate, 'yyyy-mm-dd hh:mi:ss'), status, description FROM higatlas.fms_update $where_clause ORDER BY updated_timedate DESC $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 |