From 807eb73734bff70b9f16c61f68fce7291565b428 Mon Sep 17 00:00:00 2001 From: Hakim Cassimally Date: Tue, 19 Aug 2014 10:26:31 +0000 Subject: [Warwickshire] Integration bits during/after visit - Tweaks required to get things working in WCC environment - update Open311 to onsite changes - updated Open311 parameters after WCC's Bentley and County Highways workshop. - ... including ce_cpr_id --- perllib/Open311/Endpoint/Integration/Exor.pm | 67 ++++- perllib/Open311/Endpoint/Integration/Warwick.pm | 38 +-- t/open311/endpoint/Endpoint_Warwick.pm | 5 - t/open311/endpoint/exor/DBD/Oracle.pm | 8 + t/open311/endpoint/warwick.t | 374 ++++++++++++------------ t/open311/endpoint/warwick_dbd.t | 17 ++ 6 files changed, 291 insertions(+), 218 deletions(-) create mode 100644 t/open311/endpoint/exor/DBD/Oracle.pm create mode 100644 t/open311/endpoint/warwick_dbd.t diff --git a/perllib/Open311/Endpoint/Integration/Exor.pm b/perllib/Open311/Endpoint/Integration/Exor.pm index 5e0077c3e..0d5264115 100644 --- a/perllib/Open311/Endpoint/Integration/Exor.pm +++ b/perllib/Open311/Endpoint/Integration/Exor.pm @@ -6,6 +6,7 @@ with 'Open311::Endpoint::Role::ConfigFile'; use DBI; use MooX::HandlesVia; use DateTime::Format::Oracle; # default format 'YYYY-MM-DD HH24:MI:SS' # NB: hh24 (not hh) +use Encode qw(from_to); # declare our constants, as we may not be able to easily install DBD::Oracle # on a development system! @@ -14,7 +15,9 @@ use DateTime::Format::Oracle; # default format 'YYYY-MM-DD HH24:MI:SS' # NB: hh2 sub ORA_DATE (); sub ORA_NUMBER (); sub ORA_VARCHAR2 (); +no warnings 'redefine'; use DBD::Oracle qw(:ora_types); + BEGIN { *ORA_DATE = *ORA_NUMBER = *ORA_VARCHAR2 = sub () { 1 } unless $DBD::Oracle::VERSION; @@ -22,7 +25,10 @@ BEGIN { has ora_dt => ( is => 'lazy', - default => sub { 'DateTime::Format::Oracle' }, + default => sub { + $ENV{NLS_DATE_FORMAT} = 'YYYY-MM-DD HH24:MI'; + return 'DateTime::Format::Oracle' + }, # NB: we just return the class name. This is to smooth over odd API, # for consistency with w3_dt ); @@ -43,6 +49,11 @@ has max_limit => ( default => 1000, ); +has encode_to_win1252 => ( + is => 'ro', + default => 1, +); + has _connection_details => ( is => 'lazy', default => sub { @@ -104,6 +115,36 @@ has testing => ( default => 0, ); +has ce_cat => ( + is => 'ro', + default => 'DEF', +); + +has ce_class => ( + is => 'ro', + default => 'N/A', +); + +has ce_cpr_id => ( + is => 'ro', + default => 5, +); + +has ce_contact_type => ( + is => 'ro', + default => 'PU', +); + +has ce_status_code => ( + is => 'ro', + default => 'RE', +); + +has ce_compl_user_type => ( + is => 'ro', + default => 'USER', +); + #------------------------------------------------------------------ # pem_field_types # return hash of types by field name: any not explicitly set here @@ -152,11 +193,8 @@ sub pem_field_type { } -sub sanitize_text { - my ($self, $text) = @_; -} - sub services { + # not currently used as Warwick.pm uses a hardcoded list. die "TODO"; } @@ -187,7 +225,6 @@ sub strip { $text = _strip_ruthless($text); } } - # from_to($s, 'utf8', 'Windows-1252') if $ENCODE_TO_WIN1252; # separate into own method return $max_len ? substr($text, 0, $max_len) : $text; } @@ -212,16 +249,17 @@ sub post_service_request { 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' + # fixed values (configurable via config) + $bindings{":ce_cat"} = $self->ce_cat; + $bindings{":ce_class"} = $self->ce_class; + $bindings{":ce_contact_type"} = $self->ce_contact_type; + $bindings{":ce_status_code"} = $self->ce_status_code; + $bindings{":ce_compl_user_type"}= $self->ce_compl_user_type; + $bindings{":ce_cpr_id"} = $self->ce_cpr_id; # ce_incident_datetime is *not* an optional param, but FMS isn't sending it at the moment $bindings{":ce_incident_datetime"}=$args->{requested_datetime} - || $self->w3_dt->format_datetime( DateTime->now ); + || $self->ora_dt->format_datetime( DateTime->now ); # especially FMS-specific: $bindings{":ce_source"} = "FMS"; # important, and specific to this script! @@ -287,6 +325,7 @@ sub insert_into_db { PEM.create_enquiry( ce_cat => :ce_cat, ce_class => :ce_class, + ce_cpr_id => :ce_cpr_id, ce_forename => :ce_forename, ce_surname => :ce_surname, ce_contact_type => :ce_contact_type, @@ -328,8 +367,6 @@ sub insert_into_db { # $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' diff --git a/perllib/Open311/Endpoint/Integration/Warwick.pm b/perllib/Open311/Endpoint/Integration/Warwick.pm index 0680f7ba1..bc57a8e8c 100644 --- a/perllib/Open311/Endpoint/Integration/Warwick.pm +++ b/perllib/Open311/Endpoint/Integration/Warwick.pm @@ -10,26 +10,26 @@ has '+default_service_notice' => ( sub services { # TODO, get this from ::Exor my @services = ( - [ BR => 'Bridges' ], - [ CD => 'Carriageway Defect' ], - [ CD => 'Roads/Highways' ], - [ DR => 'Drainage' ], - [ DS => 'Debris/Spillage' ], - [ FE => 'Fences' ], - [ 'F D' => 'Pavements' ], - [ GC => 'Gully & Catchpits' ], - [ IS => 'Ice/Snow' ], - [ MD => 'Mud & Debris' ], - [ MH => 'Manhole' ], - [ OS => 'Oil Spillage' ], - [ OT => 'Other' ], + # [ BR => 'Bridges' ], + # [ CD => 'Carriageway Defect' ], + # [ CD => 'Roads/Highways' ], + # [ DR => 'Drainage' ], + # [ DS => 'Debris/Spillage' ], + # [ FE => 'Fences' ], + # [ 'F D' => 'Pavements' ], + # [ GC => 'Gully & Catchpits' ], + # [ IS => 'Ice/Snow' ], + # [ MD => 'Mud & Debris' ], + # [ MH => 'Manhole' ], + # [ OS => 'Oil Spillage' ], + # [ OT => 'Other' ], [ PO => 'Pothole' ], - [ PD => 'Property Damage' ], - [ RM => 'Road Marking' ], - [ SN => 'Road traffic signs' ], - [ SP => 'Traffic' ], - [ UT => 'Utilities' ], - [ VG => 'Vegetation' ], + # [ PD => 'Property Damage' ], + # [ RM => 'Road Marking' ], + # [ SN => 'Road traffic signs' ], + # [ SP => 'Traffic' ], + # [ UT => 'Utilities' ], + # [ VG => 'Vegetation' ], ); return map { my ($code, $name) = @$_; diff --git a/t/open311/endpoint/Endpoint_Warwick.pm b/t/open311/endpoint/Endpoint_Warwick.pm index f4710f63b..c097f177e 100644 --- a/t/open311/endpoint/Endpoint_Warwick.pm +++ b/t/open311/endpoint/Endpoint_Warwick.pm @@ -1,11 +1,6 @@ package t::open311::endpoint::Endpoint_Warwick; use Web::Simple; -use Module::Loaded; -BEGIN { - mark_as_loaded('DBD::Oracle'); -} - our %BINDINGS; our $UPDATES_SQL; diff --git a/t/open311/endpoint/exor/DBD/Oracle.pm b/t/open311/endpoint/exor/DBD/Oracle.pm new file mode 100644 index 000000000..d84580d10 --- /dev/null +++ b/t/open311/endpoint/exor/DBD/Oracle.pm @@ -0,0 +1,8 @@ +package DBD::Oracle; # test +use strict; use warnings; + +sub ORA_DATE () { 2 }; +sub ORA_NUMBER () { 3 } ; +sub ORA_VARCHAR2 () { 5 }; + +1; diff --git a/t/open311/endpoint/warwick.t b/t/open311/endpoint/warwick.t index 8d0b7284b..e144b745a 100644 --- a/t/open311/endpoint/warwick.t +++ b/t/open311/endpoint/warwick.t @@ -8,7 +8,12 @@ use Data::Dumper; use JSON; use FixMyStreet::App; + +use Module::Loaded; +BEGIN { mark_as_loaded('DBD::Oracle') } + use t::open311::endpoint::Endpoint_Warwick; + use LWP::Protocol::PSGI; use Open311::PopulateServiceList; use Open311::GetServiceRequestUpdates; @@ -22,123 +27,6 @@ subtest "GET Service List" => sub { my $expected = < - - Bridges - highways - - true - BR - Bridges - realtime - - - Carriageway Defect - highways - - true - CD - Carriageway Defect - realtime - - - Roads/Highways - highways - - true - CD - Roads/Highways - realtime - - - Drainage - highways - - true - DR - Drainage - realtime - - - Debris/Spillage - highways - - true - DS - Debris/Spillage - realtime - - - Fences - highways - - true - FE - Fences - realtime - - - Pavements - highways - - true - F D - Pavements - realtime - - - Gully & Catchpits - highways - - true - GC - Gully & Catchpits - realtime - - - Ice/Snow - highways - - true - IS - Ice/Snow - realtime - - - Mud & Debris - highways - - true - MD - Mud & Debris - realtime - - - Manhole - highways - - true - MH - Manhole - realtime - - - Oil Spillage - highways - - true - OS - Oil Spillage - realtime - - - Other - highways - - true - OT - Other - realtime - Pothole highways @@ -148,60 +36,6 @@ subtest "GET Service List" => sub { Pothole realtime - - Property Damage - highways - - true - PD - Property Damage - realtime - - - Road Marking - highways - - true - RM - Road Marking - realtime - - - Road traffic signs - highways - - true - SN - Road traffic signs - realtime - - - Traffic - highways - - true - SP - Traffic - realtime - - - Utilities - highways - - true - UT - Utilities - realtime - - - Vegetation - highways - - true - VG - Vegetation - realtime - XML is $res->content, $expected @@ -237,18 +71,19 @@ subtest "POST OK" => sub { ':ce_y' => '100', ':ce_x' => '100', ':ce_work_phone' => '', - ':ce_contact_type' => 'ENQUIRER', + ':ce_contact_type' => 'PU', ':ce_source' => 'FMS', ':ce_doc_reference' => '1001', ':ce_enquiry_type' => 'PO', ':ce_email' => '', ':ce_description' => '', ':ce_location' => '22 Acacia Avenue', - ':ce_incident_datetime' => '2014-01-01T12:00:00Z', - ':ce_class' => 'SERV', + ':ce_incident_datetime' => '2014-01-01 12:00', + ':ce_class' => 'N/A', + ':ce_cpr_id' => 5, ':ce_compl_user_type' => 'USER', ':ce_status_code' => 'RE', - ':ce_cat' => 'REQS', + ':ce_cat' => 'DEF', ':ce_forename' => 'BOB' }, 'bindings as expected'; @@ -280,11 +115,11 @@ SELECT * FROM ( SELECT row_id, service_request_id, - to_char(updated_timedate, 'YYYY-MM-DD HH24:MI:SS'), + to_char(updated_timedate, 'YYYY-MM-DD HH24:MI'), status, description FROM higatlas.fms_update - WHERE updated_timedate >= to_date(2013-12-31 12:00:00, YYYY-MM-DD HH24:MI:SS) AND (status='OPEN' OR status='CLOSED') + WHERE updated_timedate >= to_date(2013-12-31 12:00, YYYY-MM-DD HH24:MI) AND (status='OPEN' OR status='CLOSED') ORDER BY updated_timedate DESC) WHERE ROWNUM <= 1000 SQL @@ -343,7 +178,7 @@ subtest "End to end" => sub { my $p = Open311::PopulateServiceList->new( bodies => $bodies, verbose => 0 ); $p->process_bodies; - is $body->contacts->count, 19, 'Categories imported from Open311'; + is $body->contacts->count, 1, 'Categories imported from Open311'; }; set_fixed_time('2014-07-20T15:05:00Z'); @@ -378,7 +213,14 @@ subtest "End to end" => sub { ALLOWED_COBRANDS => [ 'fixmystreet' ], SEND_REPORTS_ON_STAGING => 1, }, sub { - self_rs($problem)->send_reports; + ## we can't (yet) just do following due to + ## https://github.com/mysociety/fixmystreet/issues/893 + # self_rs($problem)->send_reports; + + ## instead, as we are in a transaction, we'll just delete everything else. + my $rs = FixMyStreet::App->model('DB::Problem'); + $rs->search({ id => { '!=', $problem->id } })->delete; + $rs->send_reports; }; $problem->discard_changes; @@ -425,3 +267,177 @@ sub self_rs { # create a result-set with just this body (see also DBIx::Class::Helper::Row::SelfResultSet) return $row->result_source->resultset->search( $row->ident_condition ); } + +__END__ + + Bridges + highways + + true + BR + Bridges + realtime + + + Carriageway Defect + highways + + true + CD + Carriageway Defect + realtime + + + Roads/Highways + highways + + true + CD + Roads/Highways + realtime + + + Drainage + highways + + true + DR + Drainage + realtime + + + Debris/Spillage + highways + + true + DS + Debris/Spillage + realtime + + + Fences + highways + + true + FE + Fences + realtime + + + Pavements + highways + + true + F D + Pavements + realtime + + + Gully & Catchpits + highways + + true + GC + Gully & Catchpits + realtime + + + Ice/Snow + highways + + true + IS + Ice/Snow + realtime + + + Mud & Debris + highways + + true + MD + Mud & Debris + realtime + + + Manhole + highways + + true + MH + Manhole + realtime + + + Oil Spillage + highways + + true + OS + Oil Spillage + realtime + + + Other + highways + + true + OT + Other + realtime + + + + Property Damage + highways + + true + PD + Property Damage + realtime + + + Road Marking + highways + + true + RM + Road Marking + realtime + + + Road traffic signs + highways + + true + SN + Road traffic signs + realtime + + + Traffic + highways + + true + SP + Traffic + realtime + + + Utilities + highways + + true + UT + Utilities + realtime + + + Vegetation + highways + + true + VG + Vegetation + realtime + diff --git a/t/open311/endpoint/warwick_dbd.t b/t/open311/endpoint/warwick_dbd.t new file mode 100644 index 000000000..9120c6467 --- /dev/null +++ b/t/open311/endpoint/warwick_dbd.t @@ -0,0 +1,17 @@ +use strict; use warnings; + +=head1 NAME + +warwick_dbd.t - test that Oracle constants can be imported if present + +=cut + +use Test::More; + +use lib 't/open311/endpoint/exor/'; + +use DBD::Oracle; # fake from above test lib (or real if installed) +use t::open311::endpoint::Endpoint_Warwick; + +ok 1; +done_testing; -- cgit v1.2.3