[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

4 files changed, 220 insertions, 184 deletions

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;
@@ -23,123 +28,6 @@ subtest "GET Service List" => sub {
 <?xml version="1.0" encoding="utf-8"?>
 <services>
   <service>
-    <description>Bridges</description>
-    <group>highways</group>
-    <keywords></keywords>
-    <metadata>true</metadata>
-    <service_code>BR</service_code>
-    <service_name>Bridges</service_name>
-    <type>realtime</type>
-  </service>
-  <service>
-    <description>Carriageway Defect</description>
-    <group>highways</group>
-    <keywords></keywords>
-    <metadata>true</metadata>
-    <service_code>CD</service_code>
-    <service_name>Carriageway Defect</service_name>
-    <type>realtime</type>
-  </service>
-  <service>
-    <description>Roads/Highways</description>
-    <group>highways</group>
-    <keywords></keywords>
-    <metadata>true</metadata>
-    <service_code>CD</service_code>
-    <service_name>Roads/Highways</service_name>
-    <type>realtime</type>
-  </service>
-  <service>
-    <description>Drainage</description>
-    <group>highways</group>
-    <keywords></keywords>
-    <metadata>true</metadata>
-    <service_code>DR</service_code>
-    <service_name>Drainage</service_name>
-    <type>realtime</type>
-  </service>
-  <service>
-    <description>Debris/Spillage</description>
-    <group>highways</group>
-    <keywords></keywords>
-    <metadata>true</metadata>
-    <service_code>DS</service_code>
-    <service_name>Debris/Spillage</service_name>
-    <type>realtime</type>
-  </service>
-  <service>
-    <description>Fences</description>
-    <group>highways</group>
-    <keywords></keywords>
-    <metadata>true</metadata>
-    <service_code>FE</service_code>
-    <service_name>Fences</service_name>
-    <type>realtime</type>
-  </service>
-  <service>
-    <description>Pavements</description>
-    <group>highways</group>
-    <keywords></keywords>
-    <metadata>true</metadata>
-    <service_code>F D</service_code>
-    <service_name>Pavements</service_name>
-    <type>realtime</type>
-  </service>
-  <service>
-    <description>Gully &amp; Catchpits</description>
-    <group>highways</group>
-    <keywords></keywords>
-    <metadata>true</metadata>
-    <service_code>GC</service_code>
-    <service_name>Gully &amp; Catchpits</service_name>
-    <type>realtime</type>
-  </service>
-  <service>
-    <description>Ice/Snow</description>
-    <group>highways</group>
-    <keywords></keywords>
-    <metadata>true</metadata>
-    <service_code>IS</service_code>
-    <service_name>Ice/Snow</service_name>
-    <type>realtime</type>
-  </service>
-  <service>
-    <description>Mud &amp; Debris</description>
-    <group>highways</group>
-    <keywords></keywords>
-    <metadata>true</metadata>
-    <service_code>MD</service_code>
-    <service_name>Mud &amp; Debris</service_name>
-    <type>realtime</type>
-  </service>
-  <service>
-    <description>Manhole</description>
-    <group>highways</group>
-    <keywords></keywords>
-    <metadata>true</metadata>
-    <service_code>MH</service_code>
-    <service_name>Manhole</service_name>
-    <type>realtime</type>
-  </service>
-  <service>
-    <description>Oil Spillage</description>
-    <group>highways</group>
-    <keywords></keywords>
-    <metadata>true</metadata>
-    <service_code>OS</service_code>
-    <service_name>Oil Spillage</service_name>
-    <type>realtime</type>
-  </service>
-  <service>
-    <description>Other</description>
-    <group>highways</group>
-    <keywords></keywords>
-    <metadata>true</metadata>
-    <service_code>OT</service_code>
-    <service_name>Other</service_name>
-    <type>realtime</type>
-  </service>
-  <service>
     <description>Pothole</description>
     <group>highways</group>
     <keywords></keywords>
@@ -148,60 +36,6 @@ subtest "GET Service List" => sub {
     <service_name>Pothole</service_name>
     <type>realtime</type>
   </service>
-  <service>
-    <description>Property Damage</description>
-    <group>highways</group>
-    <keywords></keywords>
-    <metadata>true</metadata>
-    <service_code>PD</service_code>
-    <service_name>Property Damage</service_name>
-    <type>realtime</type>
-  </service>
-  <service>
-    <description>Road Marking</description>
-    <group>highways</group>
-    <keywords></keywords>
-    <metadata>true</metadata>
-    <service_code>RM</service_code>
-    <service_name>Road Marking</service_name>
-    <type>realtime</type>
-  </service>
-  <service>
-    <description>Road traffic signs</description>
-    <group>highways</group>
-    <keywords></keywords>
-    <metadata>true</metadata>
-    <service_code>SN</service_code>
-    <service_name>Road traffic signs</service_name>
-    <type>realtime</type>
-  </service>
-  <service>
-    <description>Traffic</description>
-    <group>highways</group>
-    <keywords></keywords>
-    <metadata>true</metadata>
-    <service_code>SP</service_code>
-    <service_name>Traffic</service_name>
-    <type>realtime</type>
-  </service>
-  <service>
-    <description>Utilities</description>
-    <group>highways</group>
-    <keywords></keywords>
-    <metadata>true</metadata>
-    <service_code>UT</service_code>
-    <service_name>Utilities</service_name>
-    <type>realtime</type>
-  </service>
-  <service>
-    <description>Vegetation</description>
-    <group>highways</group>
-    <keywords></keywords>
-    <metadata>true</metadata>
-    <service_code>VG</service_code>
-    <service_name>Vegetation</service_name>
-    <type>realtime</type>
-  </service>
 </services>
 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__
+  <service>
+    <description>Bridges</description>
+    <group>highways</group>
+    <keywords></keywords>
+    <metadata>true</metadata>
+    <service_code>BR</service_code>
+    <service_name>Bridges</service_name>
+    <type>realtime</type>
+  </service>
+  <service>
+    <description>Carriageway Defect</description>
+    <group>highways</group>
+    <keywords></keywords>
+    <metadata>true</metadata>
+    <service_code>CD</service_code>
+    <service_name>Carriageway Defect</service_name>
+    <type>realtime</type>
+  </service>
+  <service>
+    <description>Roads/Highways</description>
+    <group>highways</group>
+    <keywords></keywords>
+    <metadata>true</metadata>
+    <service_code>CD</service_code>
+    <service_name>Roads/Highways</service_name>
+    <type>realtime</type>
+  </service>
+  <service>
+    <description>Drainage</description>
+    <group>highways</group>
+    <keywords></keywords>
+    <metadata>true</metadata>
+    <service_code>DR</service_code>
+    <service_name>Drainage</service_name>
+    <type>realtime</type>
+  </service>
+  <service>
+    <description>Debris/Spillage</description>
+    <group>highways</group>
+    <keywords></keywords>
+    <metadata>true</metadata>
+    <service_code>DS</service_code>
+    <service_name>Debris/Spillage</service_name>
+    <type>realtime</type>
+  </service>
+  <service>
+    <description>Fences</description>
+    <group>highways</group>
+    <keywords></keywords>
+    <metadata>true</metadata>
+    <service_code>FE</service_code>
+    <service_name>Fences</service_name>
+    <type>realtime</type>
+  </service>
+  <service>
+    <description>Pavements</description>
+    <group>highways</group>
+    <keywords></keywords>
+    <metadata>true</metadata>
+    <service_code>F D</service_code>
+    <service_name>Pavements</service_name>
+    <type>realtime</type>
+  </service>
+  <service>
+    <description>Gully &amp; Catchpits</description>
+    <group>highways</group>
+    <keywords></keywords>
+    <metadata>true</metadata>
+    <service_code>GC</service_code>
+    <service_name>Gully &amp; Catchpits</service_name>
+    <type>realtime</type>
+  </service>
+  <service>
+    <description>Ice/Snow</description>
+    <group>highways</group>
+    <keywords></keywords>
+    <metadata>true</metadata>
+    <service_code>IS</service_code>
+    <service_name>Ice/Snow</service_name>
+    <type>realtime</type>
+  </service>
+  <service>
+    <description>Mud &amp; Debris</description>
+    <group>highways</group>
+    <keywords></keywords>
+    <metadata>true</metadata>
+    <service_code>MD</service_code>
+    <service_name>Mud &amp; Debris</service_name>
+    <type>realtime</type>
+  </service>
+  <service>
+    <description>Manhole</description>
+    <group>highways</group>
+    <keywords></keywords>
+    <metadata>true</metadata>
+    <service_code>MH</service_code>
+    <service_name>Manhole</service_name>
+    <type>realtime</type>
+  </service>
+  <service>
+    <description>Oil Spillage</description>
+    <group>highways</group>
+    <keywords></keywords>
+    <metadata>true</metadata>
+    <service_code>OS</service_code>
+    <service_name>Oil Spillage</service_name>
+    <type>realtime</type>
+  </service>
+  <service>
+    <description>Other</description>
+    <group>highways</group>
+    <keywords></keywords>
+    <metadata>true</metadata>
+    <service_code>OT</service_code>
+    <service_name>Other</service_name>
+    <type>realtime</type>
+  </service>
+
+  <service>
+    <description>Property Damage</description>
+    <group>highways</group>
+    <keywords></keywords>
+    <metadata>true</metadata>
+    <service_code>PD</service_code>
+    <service_name>Property Damage</service_name>
+    <type>realtime</type>
+  </service>
+  <service>
+    <description>Road Marking</description>
+    <group>highways</group>
+    <keywords></keywords>
+    <metadata>true</metadata>
+    <service_code>RM</service_code>
+    <service_name>Road Marking</service_name>
+    <type>realtime</type>
+  </service>
+  <service>
+    <description>Road traffic signs</description>
+    <group>highways</group>
+    <keywords></keywords>
+    <metadata>true</metadata>
+    <service_code>SN</service_code>
+    <service_name>Road traffic signs</service_name>
+    <type>realtime</type>
+  </service>
+  <service>
+    <description>Traffic</description>
+    <group>highways</group>
+    <keywords></keywords>
+    <metadata>true</metadata>
+    <service_code>SP</service_code>
+    <service_name>Traffic</service_name>
+    <type>realtime</type>
+  </service>
+  <service>
+    <description>Utilities</description>
+    <group>highways</group>
+    <keywords></keywords>
+    <metadata>true</metadata>
+    <service_code>UT</service_code>
+    <service_name>Utilities</service_name>
+    <type>realtime</type>
+  </service>
+  <service>
+    <description>Vegetation</description>
+    <group>highways</group>
+    <keywords></keywords>
+    <metadata>true</metadata>
+    <service_code>VG</service_code>
+    <service_name>Vegetation</service_name>
+    <type>realtime</type>
+  </service>
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;