diff options
Diffstat (limited to 'bin')
| -rwxr-xr-x | bin/install-as-user | 1 | ||||
| -rwxr-xr-x | bin/make_css | 2 | ||||
| -rwxr-xr-x | bin/open311-populate-service-list | 1 | ||||
| -rwxr-xr-x | bin/oxfordshire/open311_service_request.cgi | 427 | ||||
| -rwxr-xr-x | bin/oxfordshire/open311_service_request_update.cgi | 126 | ||||
| -rw-r--r-- | bin/oxfordshire/open311_services.pm | 150 | ||||
| -rwxr-xr-x | bin/send-comments | 28 |
7 files changed, 731 insertions, 4 deletions
diff --git a/bin/install-as-user b/bin/install-as-user index 2656195f4..2e0816966 100755 --- a/bin/install-as-user +++ b/bin/install-as-user @@ -86,6 +86,7 @@ sed -r \ -e "s,^( *BASE_URL:).*,\\1 'http://$HOST'," \ -e "s,^( *EMAIL_DOMAIN:).*,\\1 '$HOST'," \ -e "s,^( *CONTACT_EMAIL:).*,\\1 'help@$HOST'," \ + -e "s,^( *DO_NOT_REPLY_EMAIL:).*,\\1 'help@$HOST'," \ conf/general.yml-example > conf/general.yml # Create the database if it doesn't exist: diff --git a/bin/make_css b/bin/make_css index a86fd4f0d..cd78e7f13 100755 --- a/bin/make_css +++ b/bin/make_css @@ -14,7 +14,7 @@ DIRECTORY=$(cd `dirname $0`/../web && pwd) # FixMyStreet uses compass -NEWSTYLE=${1:-"fixmystreet bromley fixmybarangay barnet zurich default stevenage"} +NEWSTYLE=${1:-"fixmystreet bromley fixmybarangay barnet zurich default stevenage oxfordshire"} NEWSTYLE_REGEX=${NEWSTYLE// /\\|} for site in $NEWSTYLE; do compass compile --output-style compressed $DIRECTORY/cobrands/$site diff --git a/bin/open311-populate-service-list b/bin/open311-populate-service-list index c72c04e65..be1ace3b9 100755 --- a/bin/open311-populate-service-list +++ b/bin/open311-populate-service-list @@ -15,6 +15,7 @@ my ($opt, $usage) = describe_options( print($usage->text), exit if $opt->help; my $bodies = FixMyStreet::App->model('DB::Body')->search( { + id => { '!=', 2237 }, # XXX Until Oxfordshire does do so send_method => 'Open311' } ); my $verbose = 0; diff --git a/bin/oxfordshire/open311_service_request.cgi b/bin/oxfordshire/open311_service_request.cgi new file mode 100755 index 000000000..af0c35ca0 --- /dev/null +++ b/bin/oxfordshire/open311_service_request.cgi @@ -0,0 +1,427 @@ +#!/usr/bin/perl + +# script for absobring incoming Open311 service request POSTs and +# passing them into Bentley EXOR backend via create_enquiry stored +# procedure. +# +# mySociety: http://code.fixmystreet.com/ +#----------------------------------------------------------------- + +require 'open311_services.pm'; +use DBD::Oracle qw(:ora_types); +### for local testing (no Oracle): +### use constant { ORA_VARCHAR2=>1, ORA_DATE=>1, ORA_NUMBER=>1}; + +my %PEM_BOUND_VAR_TYPES = get_pem_field_types(); + +my $ERR_MSG = 'error'; # unique key in data hash + +# incoming (Open311, from FMS) field names +# note: attribute[*] are being sent by FMS explicitly as attributes for Oxfordshire +my %F = ( + 'ACCOUNT_ID' => 'account_id', + 'ADDRESS_ID' => 'address_id', + 'ADDRESS_STRING' => 'address_string', + 'API_KEY' => 'api_key', + 'CLOSEST_ADDRESS' => 'attribute[closest_address]', + 'DESCRIPTION' => 'description', + 'DEVICE_ID' => 'device_id', + 'EASTING' => 'attribute[easting]', + 'EMAIL' => 'email', + 'FIRST_NAME' => 'first_name', + 'FMS_ID' => 'attribute[external_id]', + 'LAST_NAME' => 'last_name', + 'LAT' => 'lat', + 'LONG' => 'long', + 'MEDIA_URL' => 'media_url', + 'NORTHING' => 'attribute[northing]', + 'PHONE' => 'phone', + 'REQUESTED_DATETIME' => 'requested_datetime', + 'SERVICE_CODE' => 'service_code', + 'STATUS' => 'status', + +); + +my $req = new CGI; + +# normally, POST requests are inserting service requests +# and GET requests are for returning service requests, although OCC aren't planning on +# using that (it's part of the Open311 spec). +# So actually the service discovery is more useful, so send in a 'services' param +# to see that. +# +# But for testing the db connection, set $TEST_SERVICE_DISCOVERY so that +# *all* requests simply do a service discovery by setting (possibly via the config file) + +if ($TEST_SERVICE_DISCOVERY) { + get_service_discovery($req); # to test +}elsif ($ENV{'REQUEST_METHOD'} eq "POST") { + post_service_request($req); +} elsif ($req -> param('services')) { + get_service_discovery($req); +} elsif ($RUN_FAKE_INSERT_TEST) { + # allow a GET to make an insert, for testing (from the commandnd line!) + print "Running fake insert test... returned: " . get_FAKE_INSERT(); + print "\nSee $OUT_FILENAME for data" if $TESTING_WRITE_TO_FILE; + print "\n"; +} else { + get_service_requests($req); +} + +#---------------------------------------------------- +# post_service_request +# accepts an incoming service request +# If everything goes well, it puts it in the database and +# returns the PEM ID to the caller +#---------------------------------------------------- +sub post_service_request { + my $req = shift; + my %data; + my $pem_id = 0; + + foreach (values %F) { + $data{$_} = $req -> param($_); + $data{$_} =~ s/^\s+|\s+$//g; # trim + } + + error_and_exit(CODE_OR_ID_NOT_PROVIDED, "missing service code (Open311 requires one)") + unless $data{$F{SERVICE_CODE}}; + error_and_exit(GENERAL_SERVICE_ERROR, "the service code you provided ($data{$F{SERVICE_CODE}}) was not recognised by this server") + unless service_exists($data{$F{SERVICE_CODE}}); + error_and_exit(GENERAL_SERVICE_ERROR, "no address or long-lat provided") + unless ( (is_longlat($data{$F{LONG}}) && is_longlat($data{$F{LAT}})) || $data{$F{ADDRESS_STRING}} ); + + $pem_id = insert_into_pem(\%data); + + if (! $pem_id) { + error_and_exit(FATAL_ERROR, $data{$ERR_MSG} || "failed to get PEM ID"); + } else { + print <<XML; +Content-type: text/xml + +<?xml version="1.0" encoding="utf-8"?> +<service_requests> + <request> + <service_request_id>$pem_id</service_request_id> + </request> +</service_requests> +XML + } +} + +#------------------------------------------------------------------ +# is_longlat +# returns true if this looks like a long/lat value +#------------------------------------------------------------------ +sub is_longlat { + return $_[0] =~ /^-?\d+\.\d+$/o? 1 : 0; +} + +#------------------------------------------------------------------ +# service_exists +# lookup the service code, to check that it exists +# SELECT det_code, det_name FROM higatlas.doc_enquiry_types WHERE +# det_dtp_code = 'REQS' AND det_dcl_code ='SERV' AND det_con_id=1 +# Actually, FMS is expected to be sending good codes because they +# come from here anyway, and are expected to be stable. But could +# cache and check... probably overkill since DB insert will probably +# throw the error anyway. +#------------------------------------------------------------------ +sub service_exists { + my $service_code = shift; + return 1; +} + +#------------------------------------------------------------------ +# dump_to_file +# args: ref to hash of data +# for testing, log the incoming data into a local file +# NB throws a fatal error +#------------------------------------------------------------------ +sub dump_to_file { + my $h = shift; # href to data hash + if (open (OUTFILE, ">$OUT_FILENAME")) { + print OUTFILE "Data dump: " . gmtime() . "\n" . '-' x 64 . "\n\n"; + foreach (sort keys %$h) { + print OUTFILE "$_ => " . $$h{$_} . "\n"; + } + print OUTFILE "\n\n" . '-' x 64 . "\n[end]\n"; + close OUTFILE; + $$h{$ERR_MSG} = "NB did not write to DB (see $OUT_FILENAME instead: switch off \$TESTING_WRITE_TO_FILE to stop this)"; + } else { + $$h{$ERR_MSG} = "failed to write to outfile ($!)"; + } + return 0; # test always throws an error so no risk of production confusion! +} + +#------------------------------------------------------------------ +# insert_into_pem +# args: hashref to data hash +# returns PEM id of the new record (or passes an error message +# into the data hash if no id is available) +#------------------------------------------------------------------ +sub insert_into_pem { + my $h = shift; # href to data hash + + my $pem_id; + my $error_value; + my $error_product; + + # set specifc vars up where further processing on them might be needed: + my $undef = undef; + my $status = $$h{$F{STATUS}}; + my $service_code = $$h{$F{SERVICE_CODE}}; + my $description = $$h{$F{DESCRIPTION}}; + my $media_url = $$h{$F{MEDIA_URL}}; + if ($media_url) { + # don't put URL for full images into the database (because they're too big to see on a Blackberry) + $media_url =~ s/\.full(\.jpe?g)$/$1/; + $description .= ($STRIP_CONTROL_CHARS ne 'ruthless'? "\n\n":" ") . "Photo: $media_url"; + } + my $location = $$h{$F{CLOSEST_ADDRESS}}; + if ($location) { + # strip out everything apart from "Nearest" preamble + $location=~s/(Nearest road)[^:]+:/$1:/; + $location=~s/(Nearest postcode)[^:]+:(.*?)(\(\w+ away\))?\s*(\n|$)/$1: $2/; + } + + my %bindings; + # comments here are suggested values + # field lengths are from OCC's Java portlet + # fixed values + $bindings{":ce_cat"} = 'REQS'; # or REQS ? + $bindings{":ce_class"} = 'SERV'; # 'FRML' ? + $bindings{":ce_contact_type"} = 'ENQUIRER'; # 'ENQUIRER' + $bindings{":ce_status_code"} = 'RE'; # RE=received (?) + $bindings{":ce_compl_user_type"}= 'USER'; # 'USER' + + # ce_incident_datetime is *not* an optional param, but FMS isn't sending it at the moment + $bindings{":ce_incident_datetime"}=$$h{$F{REQUESTED_DATETIME}} || Time::Piece->new->strftime('%Y-%m-%d %H:%M'); + + # especially FMS-specific: + $bindings{":ce_source"} = "FMS"; # important, and specific to this script! + $bindings{":ce_doc_reference"} = $$h{$F{FMS_ID}}; # FMS id + $bindings{":ce_enquiry_type"} = $service_code; + + # incoming data + $bindings{":ce_x"} = $$h{$F{EASTING}}; + $bindings{":ce_y"} = $$h{$F{NORTHING}}; + $bindings{":ce_forename"} = strip($$h{$F{FIRST_NAME}}, 30); # 'CLIFF' + $bindings{":ce_surname"} = strip($$h{$F{LAST_NAME}}, 30); # 'STEWART' + $bindings{":ce_work_phone"} = strip($$h{$F{PHONE}}, 25); # '0117 600 4200' + $bindings{":ce_email"} = strip($$h{$F{EMAIL}}, 50); # 'info@exor.co.uk' + $bindings{":ce_description"} = strip($description, 2000, $F{DESCRIPTION}); # 'Large Pothole' + + # nearest address guesstimate + $bindings{":ce_location"} = strip($location, 254); + + if ($TESTING_WRITE_TO_FILE) { + return dump_to_file(\%bindings); + } + + # everything ready: now put it into the database + my $dbh = get_db_connection(); + + my $sth = $dbh->prepare(q# + BEGIN + PEM.create_enquiry( + ce_cat => :ce_cat, + ce_class => :ce_class, + ce_forename => :ce_forename, + ce_surname => :ce_surname, + ce_contact_type => :ce_contact_type, + ce_location => :ce_location, + ce_work_phone => :ce_work_phone, + ce_email => :ce_email, + ce_description => :ce_description, + ce_enquiry_type => :ce_enquiry_type, + ce_source => :ce_source, + ce_incident_datetime => to_Date(:ce_incident_datetime,'YYYY-MM-DD HH24:MI'), + ce_x => :ce_x, + ce_y => :ce_y, + ce_doc_reference => :ce_doc_reference, + ce_status_code => :ce_status_code, + ce_compl_user_type => :ce_compl_user_type, + error_value => :error_value, + error_product => :error_product, + ce_doc_id => :ce_doc_id); + END; +#); + + foreach my $name (sort keys %bindings) { + next if grep {$name eq $_} (':error_value', ':error_product', ':ce_doc_id'); # return values (see below) + $sth->bind_param( + $name, + $bindings{$name}, + $PEM_BOUND_VAR_TYPES{$name} || ORA_VARCHAR2 + ); + } + # return values are bound explicitly here: + $sth->bind_param_inout(":error_value", \$error_value, 12); #> l_ERROR_VALUE # number + $sth->bind_param_inout(":error_product", \$error_product, 10); #> l_ERROR_PRODUCT (will always be 'DOC') + $sth->bind_param_inout(":ce_doc_id", \$pem_id, 12); #> l_ce_doc_id # number + + # not used, but from the example docs, for reference + # $sth->bind_param(":ce_contact_title", $undef); # 'MR' + # $sth->bind_param(":ce_postcode", $undef); # 'BS11EJ' NB no spaces, upper case + # $sth->bind_param(":ce_building_no", $undef); # '1' + # $sth->bind_param(":ce_building_name", $undef); # 'CLIFTON HEIGHTS' + # $sth->bind_param(":ce_street", $undef); # 'HIGH STREET' + # $sth->bind_param(":ce_town", $undef); # 'BRSITOL' + # $sth->bind_param(":ce_enquiry_type", $undef); # 'CD' , ce_source => 'T' + # $sth->bind_param(":ce_cpr_id", $undef); # '5' (priority) + # $sth->bind_param(":ce_rse_he_id", $undef); #> nm3net.get_ne_id('1200D90970/09001','L') + # $sth->bind_param(":ce_compl_target", $undef); # '08-JAN-2004' + # $sth->bind_param(":ce_compl_corresp_date",$undef); # '02-JAN-2004' + # $sth->bind_param(":ce_compl_corresp_deliv_date", $undef); # '02-JAN-2004' + # $sth->bind_param(":ce_resp_of", $undef); # 'GBOWLER' + # $sth->bind_param(":ce_hct_vip", $undef); # 'CO' + # $sth->bind_param(":ce_hct_home_phone", $undef); # '0117 900 6201' + # $sth->bind_param(":ce_hct_mobile_phone", $undef); # '07111 1111111' + # $sth->bind_param(":ce_compl_remarks", $undef); # remarks (notes) max 254 char + + $sth->execute(); + $dbh->disconnect; + + # if error, maybe need to look it up: + # error_value is the index HER_NO in table HIG_ERRORS, which has messages + # actually err_product not helpful (wil always be "DOC") + $$h{$ERR_MSG} = "$error_value $error_product" if ($error_value || $error_product); + + return $pem_id; +} + +#------------------------------------------------------------------ +# strip +# args: data, max-length, field-name +# Trims, strips control chars, truncates to max-length +# Field-name only matters for description field +#------------------------------------------------------------------ +sub strip { + my ($s, $max_len, $field_name) = @_; + if ($STRIP_CONTROL_CHARS) { + if ($STRIP_CONTROL_CHARS eq 'ruthless') { + $s =~ s/[[:cntrl:]]/ /g; # strip all control chars, simples + } elsif ($STRIP_CONTROL_CHARS eq 'desc') { + if ($field_name eq $F{DESCRIPTION}) { + $s =~ s/[^\t\n[:^cntrl:]]/ /g; # leave tabs and newlines + } else { + $s =~ s/[[:cntrl:]]/ /g; # strip all control chars, simples + } + } else { + $s =~ s/[^\t\n[:^cntrl:]]/ /g; # leave tabs and newlines + } + } + from_to($s, 'utf8', 'Windows-1252') if $ENCODE_TO_WIN1252; + return $max_len? substr($s, 0, 2000) : $s; +} + +#------------------------------------------------------------------ +# get_service_requests +# Strictly speaking, Open311 would expect the GET request for service +# requests to respond with all service requests (within a specified +# period). But as we're not using that, do a service discovery +# instead. +#------------------------------------------------------------------ +sub get_service_requests { + # error_and_exit(BAD_METHOD, "sorry, currently only handling incoming Open311 service requests: use POST method"); + get_service_discovery(); # for now +} + +#------------------------------------------------------------------ +# get_FAKE_INSERT +# for testing off command line, force the data +#------------------------------------------------------------------ +sub get_FAKE_INSERT { + my %fake_data = ( + $F{'DESCRIPTION'} => 'Testing, description: A acute (requires Latin-1): [á] ' + . ' pound sign (requires WinLatin-1): [£] omega tonos (requires UTF-8): [ώ]', + $F{'EASTING'} => '45119', + $F{'EMAIL'} => 'email@example.com', + $F{'FIRST_NAME'} => 'Dave', + $F{'FMS_ID'} => '1012', + $F{'LAST_NAME'} => 'Test', + $F{'LAT'} => '51.756741605999', + $F{'LONG'} => '-1.2596387532192', + $F{'NORTHING'} => '206709', + $F{'SERVICE_CODE'} => 'OT', + $F{'MEDIA_URL'} => 'http://www.example.com/pothole.jpg', + $F{'CLOSEST_ADDRESS'} => <<TEXT +Nearest road to the pin placed on the map (automatically generated by Bing Maps): St Giles, Oxford, OX1 3 + +Nearest postcode to the pin placed on the map (automatically generated): OX1 2LA (46m away) +TEXT + ); + return insert_into_pem(\%fake_data) +} + +#------------------------------------------------------------------ +# get_service_discovery +# Although not really implementing this, use it as a test to read the +# db and confirm connectivity. +#------------------------------------------------------------------ +sub get_service_discovery { + my $dbh = get_db_connection(); + my $ary_ref = $dbh->selectall_arrayref(qq(select det_code, det_name from higatlas.doc_enquiry_types where det_dtp_code = 'REQS' AND det_dcl_code='SERV' and det_con_id=1)); + # 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 ($code, $name) = @$row; + $xml.= <<XML; + <service> + <service_code>$code</service_code> + <metadata>false</metadata> + <type>realtime</type> + <keywords/> + <group/> + <service_name>$name</service_name> + <description/> + </service> +XML + } + } + print <<XML; +Content-type: text/xml + +<?xml version="1.0" encoding="utf-8"?> +<services> +$xml +</services> +XML +} + +#------------------------------------------------------------------ +# get_pem_field_types +# return hash of types by field name: any not explicitly set here +# can be defaulted to VARCHAR2 +#------------------------------------------------------------------ +sub get_pem_field_types { + return ( + ':ce_incident_datetime' => ORA_DATE, + ':ce_x' => ORA_NUMBER, + ':ce_y' => ORA_NUMBER, + ':ce_date_expires' => ORA_DATE, + ':ce_issue_number' => ORA_NUMBER, + ':ce_status_date' => ORA_DATE, + ':ce_compl_ack_date' => ORA_DATE, + ':ce_compl_peo_date' => ORA_DATE, + ':ce_compl_target' => ORA_DATE, + ':ce_compl_complete' => ORA_DATE, + ':ce_compl_from' => ORA_DATE, + ':ce_compl_to' => ORA_DATE, + ':ce_compl_corresp_date' => ORA_DATE, + ':ce_compl_corresp_deliv_date' => ORA_DATE, + ':ce_compl_no_of_petitioners' => ORA_NUMBER, + ':ce_compl_est_cost' => ORA_NUMBER, + ':ce_compl_adv_cost' => ORA_NUMBER, + ':ce_compl_act_cost' => ORA_NUMBER, + ':ce_compl_follow_up1' => ORA_DATE, + ':ce_compl_follow_up2' => ORA_DATE, + ':ce_compl_follow_uo3' => ORA_DATE, + ':ce_date_time_arrived' => ORA_DATE, + ':error_value' => ORA_NUMBER, + ':ce_doc_id' => ORA_NUMBER, + ) +} + diff --git a/bin/oxfordshire/open311_service_request_update.cgi b/bin/oxfordshire/open311_service_request_update.cgi new file mode 100755 index 000000000..cfceffcc8 --- /dev/null +++ b/bin/oxfordshire/open311_service_request_update.cgi @@ -0,0 +1,126 @@ +#!/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/</</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); + } + + 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; + 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'] + ]; + # only add debug now if config says we're testing + $debug_str = <<XML; + <!-- DEBUG: from: $raw_start_date => $start_date --> + <!-- DEBUG: to: $raw_end_date => $end_date --> + <!-- DEBUG: sql: $sql --> +XML + } 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..0b73cdfe6 --- /dev/null +++ b/bin/oxfordshire/open311_services.pm @@ -0,0 +1,150 @@ +#!/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; + + +################################################################### +# 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; diff --git a/bin/send-comments b/bin/send-comments index 1a12fa684..850a57758 100755 --- a/bin/send-comments +++ b/bin/send-comments @@ -1,7 +1,10 @@ #!/usr/bin/env perl -# send-reports: -# Send new problem reports to bodies +# send-comments: +# Send comments/updates on reports to bodies +# 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 @@ -61,13 +64,20 @@ while ( my $body = $bodies->next ) { $use_extended = 1; } - my $o = Open311->new( + my %open311_conf = ( endpoint => $body->endpoint, jurisdiction => $body->jurisdiction, api_key => $body->api_key, use_extended_updates => $use_extended, ); + + if ( $body->send_extended_statuses ) { + $open311_conf{extended_statuses} = 1; + } + + my $o = Open311->new( %open311_conf ); + if ( $body->areas->{2482} ) { my $endpoints = $o->endpoints; $endpoints->{update} = 'update.xml'; @@ -78,6 +88,18 @@ while ( my $body = $bodies->next ) { while ( my $comment = $comments->next ) { my $cobrand = FixMyStreet::Cobrand->get_class_for_moniker($comment->cobrand)->new(); + # actually this should be OK for any devolved endpoint if original Open311->can_be_devolved, presumably + if ( $cobrand->moniker eq "fixmybarangay") { + my $sender = $cobrand->get_body_sender( $body, $comment->problem->category ); + my $config = $sender->{config}; + $o = Open311->new( + endpoint => $config->endpoint, + jurisdiction => $config->jurisdiction, + api_key => $config->api_key, + use_extended_updates => 1, # FMB uses extended updates + ); + } + if ( $comment->send_fail_count ) { next if bromley_retry_timeout( $comment ); } |