diff options
| -rwxr-xr-x | bin/oxfordshire/open311_service_request.cgi | 172 | ||||
| -rw-r--r-- | perllib/FixMyStreet/SendReport/Open311.pm | 15 |
2 files changed, 71 insertions, 116 deletions
diff --git a/bin/oxfordshire/open311_service_request.cgi b/bin/oxfordshire/open311_service_request.cgi index 109981fda..de3b56da8 100755 --- a/bin/oxfordshire/open311_service_request.cgi +++ b/bin/oxfordshire/open311_service_request.cgi @@ -9,9 +9,11 @@ use strict; use CGI; +use Time::Piece; use DBI; use DBD::Oracle qw(:ora_types); -use Time::Piece; +### 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 @@ -52,20 +54,9 @@ my $STORED_PROC_NAME = 'PEM.create_enquiry'; # 'normal' keeps tabs and newlines my $STRIP_CONTROL_CHARS = 'normal'; -my $TESTING_WRITE_TO_FILE = 0; # write to file instead of DB -my $OUT_FILENAME = "fms-test.txt"; -my $TEST_SERVICE_DISCOVERY=0; # switch to 1 to run service discovery, which confirms the DB connection at least - -my %PEM_BOUND_VAR_TYPES; -my @PEM_BOUND_VAR_NAMES; -foreach (get_pem_field_names()) { - if (s/__(NUMBER|DATE)$//) { - push @PEM_BOUND_VAR_NAMES, $_; - $PEM_BOUND_VAR_TYPES{$_} = $1 || ''; # empty for varchar2 - } else { - push @PEM_BOUND_VAR_NAMES, $_; - } -} +my $TESTING_WRITE_TO_FILE = 0; # write to file instead of DB +my $OUT_FILENAME = "fms-test.txt"; +my $TEST_SERVICE_DISCOVERY = 0; # switch to 1 to run service discovery, which confirms the DB connection at least # Config file overrides existing values for these, if present: if ($CONFIG_FILENAME && open(CONF, $CONFIG_FILENAME)) { @@ -98,9 +89,12 @@ if ($CONFIG_FILENAME && open(CONF, $CONFIG_FILENAME)) { # $ENV{PATH} ="$ORACLE_HOME/bin"; # $ENV{LD_LIBRARY_PATH}="$ORACLE_HOME/lib"; +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', @@ -116,11 +110,14 @@ my %F = ( 'LAT' => 'lat', 'LONG' => 'long', 'MEDIA_URL' => 'media_url', + 'NEAREST_STREET' => 'attribute[nearest_street]', 'NORTHING' => 'attribute[northing]', 'PHONE' => 'phone', + 'POSTCODE' => 'attribute[postcode]', 'REQUESTED_DATETIME' => 'requested_datetime', 'SERVICE_CODE' => 'service_code', 'STATUS' => 'status', + ); my $req = new CGI; @@ -294,6 +291,9 @@ sub insert_into_pem { my $service_code = $$h{$F{SERVICE_CODE}}; my $description = $$h{$F{DESCRIPTION}}; my $media_url = $$h{$F{MEDIA_URL}}; + my $postcode = uc $$h{$F{POSTCODE}}; # postcode must be in upper case... + $postcode =~s/\s+//g; # ...and no spaces in postcode + if ($media_url) { $description .= ($STRIP_CONTROL_CHARS ne 'ruthless'? "\n\n":" ") . "Photo: $media_url"; } @@ -306,6 +306,8 @@ sub insert_into_pem { ce_forename => :ce_forename, ce_surname => :ce_surname, ce_contact_type => :ce_contact_type, + ce_postcode => :ce_postcode, + ce_street => :ce_street, ce_work_phone => :ce_work_phone, ce_email => :ce_email, ce_description => :ce_description, @@ -348,16 +350,22 @@ sub insert_into_pem { $bindings{":ce_work_phone"} = substr($$h{$F{PHONE}}, 0, 25); # '0117 600 4200' $bindings{":ce_email"} = substr($$h{$F{EMAIL}}, 0, 50); # 'info@exor.co.uk' $bindings{":ce_description"} = substr($description, 0, 2000); # 'Large Pothole' + $bindings{":ce_street"} = substr($$h{$F{NEAREST_STREET}}, 0, 80); # calculated/human postcode + $bindings{":ce_postcode"} = substr($postcode, 0, 8); # calculated nearest street foreach my $name (sort keys %bindings) { - next if grep {$name eq $_} (':error_value', ':error_product', ':ce_doc_id'); # return values - my $type = $PEM_BOUND_VAR_TYPES{$name} || 'VARCHAR2'; + next if grep {$name eq $_} (':error_value', ':error_product', ':ce_doc_id'); # return values (see below) $sth->bind_param( $name, $bindings{$name}, - $type eq 'NUMBER'? ORA_NUMBER : ($type eq 'DATE'? ORA_DATE : ORA_VARCHAR2 ) + $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' @@ -378,11 +386,6 @@ sub insert_into_pem { # $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 - - # return values: - $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 $sth->execute(); $dbh->disconnect; @@ -424,6 +427,8 @@ sub get_FAKE_INSERT { $F{'NORTHING'} => '206709', $F{'SERVICE_CODE'} => 'OT', $F{'MEDIA_URL'} => 'http://www.example.com/pothole.jpg', + $F{'POSTCODE'} => 'OX20 1SZ', + $F{'NEAREST_STREET'} => 'Testit Street' ); return insert_into_pem(\%fake_data) } @@ -465,98 +470,35 @@ XML } #------------------------------------------------------------------ -# get_pem_field_names -# return list of params for the stored procedure *in the correct order* -# Also: __NUMBER and __DATE suffixes for anything that isn't a VARCHAR2 -# This also adds the colon prefix to all names. -# Note the last three of these are inout params, and are handled -# as a special (hardcoded) case when used (see above). -# -# These are currently only used for type lookup, so should probably -# move them into the code as a name => type lookup; furthermore most of -# these are not used, and default to varchar2 anyway. +# 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_names { - return map {":$_"} qw( -ce_cat -ce_class -ce_title -ce_contact_title -ce_forename -ce_surname -ce_contact_type -ce_flag -ce_pcode_id -ce_postcode -ce_building_no -ce_building_name -ce_street -ce_locality -ce_town -ce_county -ce_organisation -ce_work_phone -ce_email -ce_location -ce_description -ce_enquiry_type -ce_source -ce_incident_datetime__DATE -ce_cpr_id -ce_asset_id -ce_rse_he_id -ce_x__NUMBER -ce_y__NUMBER -ce_date_expires__DATE -ce_doc_reference -ce_issue_number__NUMBER -ce_category -ce_status_date__DATE -ce_resp_of -ce_compl_ack_flag -ce_compl_ack_date__DATE -ce_compl_flag -ce_compl_peo_date__DATE -ce_compl_target__DATE -ce_compl_complete__DATE -ce_compl_referred_to -ce_compl_police_notif_flag -ce_compl_date_police_notif -ce_compl_from__DATE -ce_compl_to__DATE -ce_compl_claim -ce_compl_corresp_date__DATE -ce_compl_corresp_deliv_date__DATE -ce_compl_no_of_petitioners__NUMBER -ce_compl_remarks -ce_compl_cause -ce_compl_injuries -ce_compl_damgae -ce_compl_action -ce_compl_litigation_flag -ce_compl_litigation_reason -ce_compl_claim_no -ce_compl_determination -ce_compl_est_cost__NUMBER -ce_compl_adv_cost__NUMBER -ce_compl_act_cost__NUMBER -ce_compl_follow_up1__DATE -ce_compl_follow_up2__DATE -ce_compl_follow_uo3__DATE -ce_compl_insurance_claim -ce_compl_summons_received -ce_compl_user_type -ce_hct_vip -ce_hct_home_phone -ce_hct_mobile_phone -ce_hct_fax -ce_had_sub_build_name -ce_had_property_type -ce_status_code -ce_date_time_arrived__DATE -ce_reason_late -error_value__NUMBER -error_product -ce_doc_id__NUMBER +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/perllib/FixMyStreet/SendReport/Open311.pm b/perllib/FixMyStreet/SendReport/Open311.pm index 0f9c2276a..259dc7202 100644 --- a/perllib/FixMyStreet/SendReport/Open311.pm +++ b/perllib/FixMyStreet/SendReport/Open311.pm @@ -70,10 +70,23 @@ sub send { $extended_desc = 0; } - # extra Oxfordshire fields: send northing and easting, and the FMS id + # extra Oxfordshire fields: send nearest street, postcode, northing and easting, and the FMS id if ( $row->council =~ /$COUNCIL_ID_OXFORDSHIRE/ ) { + my ($postcode, $nearest_street) = ('', ''); + for ($h->{closest_address}) { + $postcode = sprintf("%-10s", $1) if /Nearest postcode [^:]+: ((\w{1,4}\s?\w+|\w+))/; + # use partial postcode or comma as delimiter, strip leading number (possible letter 221B) off too + # "99 Foo Street, London N11 1XX" becomes Foo Street + # "99 Foo Street N11 1XX" becomes Foo Street + $nearest_street = $1 if /Nearest road [^:]+: (?:\d+\w? )?(.*?)(\b[A-Z]+\d|,|$)/m; + } + $postcode = mySociety::PostcodeUtil::is_valid_postcode($h->{query}) + ? $h->{query} : $postcode; # use given postcode if available + my $extra = $row->extra; push @$extra, { name => 'external_id', value => $row->id }; + push @$extra, { name => 'postcode', value => $postcode } if $postcode; + push @$extra, { name => 'nearest_street', value => $nearest_street } if $nearest_street; if ( $row->used_map || ( !$row->used_map && !$row->postcode ) ) { push @$extra, { name => 'northing', value => $h->{northing} }; push @$extra, { name => 'easting', value => $h->{easting} }; |