Open311 Warwick (Exor) Integration

 ::Integration::Warwick subclasses ::Integration::Exor
 refactor request_class and new_request
 Exor service
 Warwickshire updates retrieval, with datetimes
 stubbing out of Oracle constants, for local testing

We also edit FMS's core PopulateServiceList routine to hide system
fields from FMS:

Bromley/Warwickshire send metadata in their services/FOO.xml
advising that you can pass, e.g. attributes[easting].

FMS by default shows all of these to the user to fill in, however
we don't *want* the user to supply these, rather they are added
by the cobrand.

Bromley had an exception for this (keyed by $body->areas->id).
We write this more generally for Warwickshire too, keying instead
by $body->name (as this is far less likely to be overridden for
installs using global or custom Mapit's)

15 files changed, 906 insertions, 21 deletions

diff --git a/perllib/Open311/Endpoint.pm b/perllib/Open311/Endpoint.pm
index 0abb52aab..7916d090f 100644
--- a/perllib/Open311/Endpoint.pm
+++ b/perllib/Open311/Endpoint.pm
@@ -118,8 +118,6 @@ sub check_jurisdiction_id {
     return first { $jurisdiction_id eq $_ } $self->get_jurisdiction_ids;
 }
 
-
-
 =head2 Configurable arguments
 
     * default_service_notice - default for <service_notice> if not
@@ -141,6 +139,7 @@ sub check_jurisdiction_id {
             service_request_id  => ...
             # etc.
         }
+    * request_class - class to instantiate for requests via new_request
 
 =cut
 
@@ -182,6 +181,17 @@ has jurisdiction_ids => (
     }
 );
 
+has request_class => (
+    is => 'ro',
+    isa => Str,
+    default => 'Open311::Endpoint::Service::Request',
+);
+
+sub new_request {
+    my ($self, %args) = @_;
+    return $self->request_class->new( %args );
+}
+
 =head2 Other accessors
 
 You may additionally wish to replace the following objects.
@@ -241,6 +251,11 @@ has w3_dt => (
     default => sub { DateTime::Format::W3CDTF->new },
 );
 
+has time_zone => (
+    is => 'ro',
+    default => 'Europe/London',
+);
+
 sub maybe_inflate_datetime {
     my ($self, $dt) = @_;
     return unless $dt;
@@ -403,7 +418,7 @@ sub POST_Service_Request_input_schema {
     my $service = $self->service($service_code)
         or return; # we can't fetch service, so signal error TODO
 
-    my %attributes;
+    my %attributes = ( required => {}, optional => {} );
     for my $attribute ($service->get_attributes) {
         my $section = $attribute->required ? 'required' : 'optional';
         my $key = sprintf 'attribute[%s]', $attribute->code;
@@ -440,7 +455,7 @@ sub POST_Service_Request_input_schema {
                 phone => '//str',
                 description => '//str',
                 media_url => '//str',
-                %{ $attributes{optional} },
+                %{ $attributes{optional} || {}},
                 (map %$_, @address_options),
                 $self->get_jurisdiction_id_optional_clause,
             },
@@ -492,6 +507,13 @@ sub POST_Service_Request {
     my $service_code = $args->{service_code};
     my $service = $self->service($service_code);
 
+    for my $k (keys %$args) {
+        if ($k =~ /^attribute\[(\w+)\]$/) {
+            my $value = delete $args->{$k};
+            $args->{attributes}{$1} = $value;
+        }
+    }
+
     my @service_requests = $self->post_service_request( $service, $args );
         
     return {
@@ -639,7 +661,12 @@ sub format_service_requests {
                     ),
                     (
                         map {
-                            $_ => $self->w3_dt->format_datetime( $request->$_ ),
+                            if (my $dt = $request->$_) {
+                                $_ => $self->w3_dt->format_datetime( $dt )
+                            }
+                            else {
+                                ()
+                            }
                         }
                         qw/
                             requested_datetime
@@ -729,8 +756,10 @@ sub call_api {
             $schema->assert_valid( $data );
         };
         if ($@) {
+            use Data::Dumper;
             return Open311::Endpoint::Result->error( 500,
                 "Error in output for $api_name",
+                Dumper($data),
                 split /\n/, $@,
                 # map $_->struct, @{ $@->failures },
             );
diff --git a/perllib/Open311/Endpoint/Integration/Exor.pm b/perllib/Open311/Endpoint/Integration/Exor.pm
new file mode 100644
index 000000000..5e0077c3e
--- /dev/null
+++ b/perllib/Open311/Endpoint/Integration/Exor.pm
@@ -0,0 +1,421 @@
+package Open311::Endpoint::Integration::Exor;
+use Web::Simple;
+extends 'Open311::Endpoint';
+with 'Open311::Endpoint::Role::mySociety';
+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)
+
+# declare our constants, as we may not be able to easily install DBD::Oracle
+# on a development system!
+# t/open311/endpoint/warwick.t disables DBD::Oracle from loading, so the default
+# stubbed values will be used instead:
+sub ORA_DATE ();
+sub ORA_NUMBER ();
+sub ORA_VARCHAR2 ();
+use DBD::Oracle qw(:ora_types);
+BEGIN {
+*ORA_DATE = *ORA_NUMBER = *ORA_VARCHAR2 = sub () { 1 }
+    unless $DBD::Oracle::VERSION;
+}
+
+has ora_dt => (
+    is => 'lazy',
+    default => sub { 'DateTime::Format::Oracle' }, 
+        # NB: we just return the class name. This is to smooth over odd API,
+        # for consistency with w3_dt
+);
+
+sub parse_ora_date {
+    my ($self, $date_string) = @_;
+
+    my $date = $self->ora_dt->parse_datetime( $date_string );
+
+    # will be in floating time_zone so set
+    $date->set_time_zone( $self->time_zone );
+
+    return $date;
+}
+
+has max_limit => (
+    is => 'ro',
+    default => 1000,
+);
+
+has _connection_details => (
+    is => 'lazy',
+    default => sub {
+        my $self = shift;
+        my $DB_HOST = $self->db_host;
+        my $ORACLE_SID = $self->oracle_sid;
+        my $DB_PORT = $self->db_port;
+        my $USERNAME = $self->db_username;
+        my $PASSWORD = $self->db_password;
+        return [ "dbi:Oracle:host=$DB_HOST;sid=$ORACLE_SID;port=$DB_PORT", $USERNAME, $PASSWORD ]
+    },
+    handles_via => 'Array',
+    handles => {
+        connection_details => 'elements',
+        dsn => [ get => 0 ],
+    },
+);
+
+has dbh => (
+    is => 'lazy',
+    default => sub {
+        my $self = shift;
+        return DBI->connect( $self->connection_details );
+    }
+);
+
+has db_host => (
+    is => 'ro',
+    default => 'localhost',
+);
+
+has oracle_sid => (
+    is => 'ro',
+    default => '1000',  # DUMMY
+);
+
+has db_port => (
+    is => 'ro',
+    default => 1531,
+);
+
+has db_username => (
+    is => 'ro',
+    default => 'FIXMYSTREET',
+);
+
+has db_password => (
+    is => 'ro',
+    default => 'SUPERSEEKRIT', # DUMMY
+);
+
+has strip_control_characters => (
+    is => 'ro',
+    default => 'ruthless',
+);
+
+has testing => (
+    is => 'ro',
+    default => 0,
+);
+
+#------------------------------------------------------------------
+# pem_field_types
+# return hash of types by field name: any not explicitly set here
+# can be defaulted to VARCHAR2
+#------------------------------------------------------------------
+has get_pem_field_types => (
+    is => 'ro',
+    handles_via => 'Hash',
+    default => sub {
+        {
+            ':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,
+        }
+    },
+    handles => {
+        get_pem_field_type => 'get',
+
+    },
+);
+
+sub pem_field_type {
+    my ($self, $field) = @_;
+    return $self->get_pem_field_type($field) || ORA_VARCHAR2;
+}
+
+
+sub sanitize_text {
+    my ($self, $text) = @_;
+}
+
+sub services {
+    die "TODO";
+}
+
+sub _strip_ruthless {
+    my $text = shift or return '';
+    $text =~ s/[[:cntrl:]]/ /g; # strip all control chars, simples
+    return $text;
+}
+
+sub _strip_non_ruthless {
+    my $text = shift or return '';
+    # slightly odd doubly negated character class
+    $text =~ s/[^\t\n[:^cntrl:]]/ /g; # leave tabs and newlines
+    return $text;
+}
+sub strip {
+    my ($self, $text, $max_len, $prefer_non_ruthless) = @_;
+    use Carp 'confess';
+    confess 'EEEK' unless $self;
+    if (my $scc = $self->strip_control_characters) {
+        if ($scc eq 'ruthless') {
+            $text = _strip_ruthless($text);
+        }
+        elsif ($prefer_non_ruthless) {
+            $text = _strip_non_ruthless($text);
+        }
+        else {
+            $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;
+}
+
+sub post_service_request {
+    my ($self, $service, $args) = @_;
+    die "No such service" unless $service;
+
+    if ($args->{media_url}) {
+        # don't put URL for full images into the database (because they're too big to see on a Blackberry)
+        $args->{media_url} =~ s/\.full(\.jpe?g)$/$1/;
+        $args->{description} .= $self->strip( "\n\n") . 'Photo: ' . $args->{media_url};
+    }
+    my $attributes = $args->{attributes};
+    my $location = $attributes->{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"}=$args->{requested_datetime}
+        || $self->w3_dt->format_datetime( DateTime->now );
+
+    # especially FMS-specific:
+    $bindings{":ce_source"}        = "FMS";           # important, and specific to this script!
+    $bindings{":ce_doc_reference"} = $attributes->{external_id}; # FMS ID
+    $bindings{":ce_enquiry_type"}  = $args->{service_code};
+
+    # incoming data
+    $bindings{":ce_x"}             = $attributes->{easting};
+    $bindings{":ce_y"}             = $attributes->{northing};
+    $bindings{":ce_forename"}      = uc $self->strip($args->{first_name}, 30);    # 'CLIFF'
+    $bindings{":ce_surname"}       = uc $self->strip($args->{last_name}, 30);     # 'STEWART'
+    $bindings{":ce_work_phone"}    = $self->strip($args->{phone}, 25);            # '0117 600 4200'
+    $bindings{":ce_email"}         = uc $self->strip($args->{email}, 50);         # 'info@exor.co.uk'
+    $bindings{":ce_description"}   = $self->strip($args->{description}, 1970, 1); # 'Large Pothole'
+
+    # nearest address guesstimate
+    $bindings{":ce_location"}      = $self->strip($location, 254);
+    
+    if ($self->testing) {
+        warn Dumper(\%bindings); use Data::Dumper;
+    }
+
+    my ($pem_id, $error_value, $error_product) = $self->insert_into_db(\%bindings);
+
+    # 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 (will always be "DOC")
+    die "$error_value $error_product" if $error_value || $error_product; 
+
+    my $request = $self->new_request(
+
+        # NB: possible race condition between next_request_id and _add_request
+        # (this is fine for synchronous test-cases)
+        
+        service => $service,
+        service_request_id => $pem_id,
+        status => 'open',
+        description => $args->{description},
+        agency_responsible => '',
+        requested_datetime => DateTime->now(),
+        updated_datetime => DateTime->now(),
+        address => $args->{address_string} // '',
+        address_id => $args->{address_id} // '',
+        media_url => $args->{media_url} // '',
+        zipcode => $args->{zipcode} // '',
+        attributes => $attributes,
+
+    );
+
+    return $request;
+}
+
+sub insert_into_db {
+    my ($self, $bindings) = @_;
+    my %bindings = %$bindings;
+
+    my ($pem_id, $error_value, $error_product);
+    
+    my $dbh = $self->dbh;
+
+    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}, 
+            $self->pem_field_type( $name ),
+        );  
+    }
+    # 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;
+
+    return ($pem_id, $error_value, $error_product);
+}
+
+sub get_service_request_updates {
+    my ($self, $args) = @_;
+
+    # ignore jurisdiction_id for now
+    #
+    my $start_date = $self->maybe_inflate_datetime( $args->{start_date} );
+    my $end_date   = $self->maybe_inflate_datetime( $args->{end_date} );
+
+    unless ($self->testing) {
+        $start_date = DateTime->now->subtract( days => 1 )
+            unless ($start_date or $end_date);
+    }
+
+    my $w3_dt = $self->w3_dt;
+    my $ora_dt = $self->ora_dt;
+    my $ORA_DT_FORMAT = $ora_dt->nls_date_format;
+
+    my @where;
+
+    push @where, sprintf 
+        'updated_timedate >= to_date(%s, %s)',
+        $ora_dt->format_datetime($start_date), $ORA_DT_FORMAT
+        if $start_date;
+
+    push @where, sprintf 
+        'updated_timedate <= to_date(%s, %s)',
+        $ora_dt->format_datetime($end_date), $ORA_DT_FORMAT
+        if $end_date;
+
+    push @where, "(status='OPEN' OR status='CLOSED')"
+        unless $self->testing;
+
+    my $WHERE_CLAUSE = @where ?
+        'WHERE ' . join(' AND ', grep {$_} @where)
+        : '';
+
+    my $sql = qq(
+        SELECT
+            row_id,
+            service_request_id,
+            to_char(updated_timedate, '$ORA_DT_FORMAT'),
+            status,
+            description
+        FROM higatlas.fms_update
+        $WHERE_CLAUSE
+        ORDER BY updated_timedate DESC);
+
+    my $limit = $self->max_limit; # also allow testing to modify this?
+    $sql = "SELECT * FROM ($sql) WHERE ROWNUM <= $limit" if $limit;
+
+    my @data = $self->get_updates_from_sql( $sql );
+
+    my @updates = map {
+        Open311::Endpoint::Service::Request::Update->new(
+            update_id => $_->{row_id},
+            service_request_id => $_->{service_request_id},
+            updated_datetime => $self->parse_ora_date( $_->{updated_datetime} ),
+            status => $_->{status},
+            description => $_->{description}
+        )
+    } @data;
+
+    return @updates;
+}
+
+sub get_updates_from_sql {
+    my ($self, $sql) = @_;
+    my $dbh = $self->dbh;
+    my $ary_ref = $dbh->selectall_arrayref($sql, { Slice => {} } );
+    return @$ary_ref;
+}
+
+1;
diff --git a/perllib/Open311/Endpoint/Integration/Warwick.pm b/perllib/Open311/Endpoint/Integration/Warwick.pm
new file mode 100644
index 000000000..0680f7ba1
--- /dev/null
+++ b/perllib/Open311/Endpoint/Integration/Warwick.pm
@@ -0,0 +1,47 @@
+package Open311::Endpoint::Integration::Warwick;
+use Web::Simple;
+extends 'Open311::Endpoint::Integration::Exor';
+use Open311::Endpoint::Service::Exor;
+
+has '+default_service_notice' => (
+    default => 'Warwickshire Open311 Endpoint',
+);
+
+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' ],
+        [ PO => 'Pothole' ],
+        [ PD => 'Property Damage' ],
+        [ RM => 'Road Marking' ],
+        [ SN => 'Road traffic signs' ],
+        [ SP => 'Traffic' ],
+        [ UT => 'Utilities' ],
+        [ VG => 'Vegetation' ],
+    );
+    return map {
+        my ($code, $name) = @$_;
+        Open311::Endpoint::Service::Exor->new(
+            service_code => $code,
+            service_name => $name,
+            description => $name,
+            type => 'realtime',
+            keywords => [qw/ /],
+            group => 'highways',
+        ),
+    } @services;
+}
+
+1;
diff --git a/perllib/Open311/Endpoint/Result.pm b/perllib/Open311/Endpoint/Result.pm
index 2d3c42154..61454e749 100644
--- a/perllib/Open311/Endpoint/Result.pm
+++ b/perllib/Open311/Endpoint/Result.pm
@@ -27,7 +27,7 @@ sub error {
                     ref $_ eq 'HASH' ? $_ :
                     {
                         code => $code,
-                        description => $_,
+                        description => "$_",
                     }
                 } @errors,
             ],
diff --git a/perllib/Open311/Endpoint/Role/mySociety.pm b/perllib/Open311/Endpoint/Role/mySociety.pm
index 42afe73bb..3669c585b 100644
--- a/perllib/Open311/Endpoint/Role/mySociety.pm
+++ b/perllib/Open311/Endpoint/Role/mySociety.pm
@@ -35,6 +35,12 @@ L<Open311::Endpoint::Service::Request::mySociety> objects.
 use Moo::Role;
 no warnings 'illegalproto';
 
+use Open311::Endpoint::Service::Request::mySociety;
+has '+request_class' => (
+    is => 'ro',
+    default => 'Open311::Endpoint::Service::Request::mySociety',
+);
+
 around dispatch_request => sub {
     my ($orig, $self, @args) = @_;
     my @dispatch = $self->$orig(@args);
@@ -115,7 +121,7 @@ sub format_updates {
                     ),
                     (
                         map {
-                            $_ => $self->w3_dt->format_datetime( $update->$_ ),
+                            $_ => $self->w3_dt->format_datetime( $update->$_ ), 
                         }
                         qw/
                             updated_datetime
diff --git a/perllib/Open311/Endpoint/Service.pm b/perllib/Open311/Endpoint/Service.pm
index 282e5f921..2c28c6d79 100644
--- a/perllib/Open311/Endpoint/Service.pm
+++ b/perllib/Open311/Endpoint/Service.pm
@@ -28,6 +28,7 @@ has description => (
 has keywords => (
     is => 'ro',
     isa => ArrayRef[Str],
+    default => sub { [] },
 );
 
 has group => (
@@ -43,6 +44,7 @@ has type => (
 has attributes => (
     is => 'ro',
     isa => ArrayRef[ InstanceOf['Open311::Endpoint::Service::Attribute'] ],
+    default => sub { [] },
     handles_via => 'Array',
     handles => {
         has_attributes => 'count',
diff --git a/perllib/Open311/Endpoint/Service/Exor.pm b/perllib/Open311/Endpoint/Service/Exor.pm
new file mode 100644
index 000000000..6261875c1
--- /dev/null
+++ b/perllib/Open311/Endpoint/Service/Exor.pm
@@ -0,0 +1,44 @@
+package Open311::Endpoint::Service::Exor;
+use Moo;
+extends 'Open311::Endpoint::Service';
+use Open311::Endpoint::Service::Attribute;
+
+has '+attributes' => (
+    is => 'ro',
+    default => sub { [
+        Open311::Endpoint::Service::Attribute->new(
+            code => 'easting',
+            variable => 0, # set by server
+            datatype => 'number',
+            required => 1,
+            datatype_description => 'a number',
+            description => 'easting',
+        ),
+        Open311::Endpoint::Service::Attribute->new(
+            code => 'northing',
+            variable => 0, # set by server
+            datatype => 'number',
+            required => 1,
+            datatype_description => 'a number',
+            description => 'northing',
+        ),
+        Open311::Endpoint::Service::Attribute->new(
+            code => 'closest_address',
+            variable => 0, # set by server
+            datatype => 'string',
+            required => 1,
+            datatype_description => 'an address',
+            description => 'closest address',
+        ),
+        Open311::Endpoint::Service::Attribute->new(
+            code => 'external_id',
+            variable => 0, # set by server
+            datatype => 'string',
+            required => 1,
+            datatype_description => 'an id',
+            description => 'external system ID',
+        ),
+    ] },
+);
+
+1;
diff --git a/perllib/Open311/Endpoint/Service/Request.pm b/perllib/Open311/Endpoint/Service/Request.pm
index 1d835f2d5..8dfa5df3a 100644
--- a/perllib/Open311/Endpoint/Service/Request.pm
+++ b/perllib/Open311/Endpoint/Service/Request.pm
@@ -55,11 +55,13 @@ has agency_responsible => (
 has requested_datetime => (
     is => 'ro',
     isa => Maybe[ InstanceOf['DateTime'] ],
+    default => sub { DateTime->now() },
 );
 
 has updated_datetime => (
     is => 'rw',
     isa => Maybe[ InstanceOf['DateTime'] ],
+    default => sub { DateTime->now() },
 );
 
 has expected_datetime => (
diff --git a/perllib/Open311/Endpoint/Service/Request/mySociety.pm b/perllib/Open311/Endpoint/Service/Request/mySociety.pm
index 748065ad8..85e31b26f 100644
--- a/perllib/Open311/Endpoint/Service/Request/mySociety.pm
+++ b/perllib/Open311/Endpoint/Service/Request/mySociety.pm
@@ -31,7 +31,6 @@ sub add_update {
     $self->_add_update($update);
 }
 
-
 sub last_update {
     my $self = shift;
     return $self->has_updates ? $self->get_update(-1) : undef;
diff --git a/perllib/Open311/PopulateServiceList.pm b/perllib/Open311/PopulateServiceList.pm
index c5fc4a506..d1a4d607f 100644
--- a/perllib/Open311/PopulateServiceList.pm
+++ b/perllib/Open311/PopulateServiceList.pm
@@ -228,10 +228,13 @@ sub _add_meta_to_contact {
         sort { $a->{order} <=> $b->{order} }
         @{ $meta_data->{attributes}->{attribute} };
 
-    # we add these later on from bromley so don't list them here
-    # as we don't want to display them
-    if ( $self->_current_body->areas->{2482} ) {
-        my %ignore = map { $_ => 1 } qw/
+    # Some Open311 endpoints, such as Bromley and Warwickshire send <metadata>
+    # for attributes which we *don't* want to display to the user (e.g. as
+    # fields in "category_extras"
+
+    my %override = (
+        #2482
+        'Bromley Council' => [qw(
             service_request_id_ext
             requested_datetime
             report_url
@@ -243,8 +246,18 @@ sub _add_meta_to_contact {
             report_title
             public_anonymity_required
             email_alerts_requested
-        /;
+        ) ],
+        #2242, 
+        'Warwickshire County Council' => [qw(
+            external_id
+            easting
+            northing
+            closest_address
+        ) ],
+    );
 
+    if (my $override = $override{ $self->_current_body->name }) {
+        my %ignore = map { $_ => 1 } @{ $override };
         @meta = grep { ! $ignore{ $_->{ code } } } @meta;
     }
 
diff --git a/t/open311/endpoint.t b/t/open311/endpoint.t
index fd794feec..7e684c491 100644
--- a/t/open311/endpoint.t
+++ b/t/open311/endpoint.t
@@ -244,7 +244,8 @@ CONTENT
 subtest "GET Service Requests" => sub {
 
     my $res = $endpoint->run_test_request( GET => '/requests.xml', );
-    ok $res->is_success, 'valid request';
+    ok $res->is_success, 'valid request'
+        or die $res->content;
     my $xml = <<CONTENT;
 <?xml version="1.0" encoding="utf-8"?>
 <service_requests>
diff --git a/t/open311/endpoint/Endpoint1.pm b/t/open311/endpoint/Endpoint1.pm
index 257a94b64..c4119075c 100644
--- a/t/open311/endpoint/Endpoint1.pm
+++ b/t/open311/endpoint/Endpoint1.pm
@@ -9,8 +9,6 @@ use t::open311::endpoint::ServiceType1;
 use Open311::Endpoint::Service::Attribute;
 use Open311::Endpoint::Service::Request;
 
-use constant request_class => 'Open311::Endpoint::Service::Request';
-
 sub services {
     return (
         t::open311::endpoint::ServiceType1->new(
@@ -76,7 +74,7 @@ sub services {
 sub post_service_request {
     my ($self, $service, $args) = @_;
 
-    my $request = $self->request_class->new(
+    my $request = $self->new_request(
 
         # NB: possible race condition between next_request_id and _add_request
         # (this is fine for synchronous test-cases)
diff --git a/t/open311/endpoint/Endpoint2.pm b/t/open311/endpoint/Endpoint2.pm
index 2b55b1e10..664a7f3d6 100644
--- a/t/open311/endpoint/Endpoint2.pm
+++ b/t/open311/endpoint/Endpoint2.pm
@@ -3,10 +3,6 @@ use Web::Simple;
 extends 't::open311::endpoint::Endpoint1';
 with 'Open311::Endpoint::Role::mySociety';
 
-use Open311::Endpoint::Service::Request::mySociety;
-
-use constant request_class => 'Open311::Endpoint::Service::Request::mySociety';
-
 sub get_service_request_updates {
     my ($self, $args) = @_;
 
diff --git a/t/open311/endpoint/Endpoint_Warwick.pm b/t/open311/endpoint/Endpoint_Warwick.pm
new file mode 100644
index 000000000..f4710f63b
--- /dev/null
+++ b/t/open311/endpoint/Endpoint_Warwick.pm
@@ -0,0 +1,36 @@
+package t::open311::endpoint::Endpoint_Warwick;
+use Web::Simple;
+
+use Module::Loaded;
+BEGIN {
+    mark_as_loaded('DBD::Oracle');
+}
+
+our %BINDINGS;
+our $UPDATES_SQL;
+
+extends 'Open311::Endpoint::Integration::Warwick';
+
+sub insert_into_db {
+    my ($self, $bindings) = @_;
+
+    %BINDINGS = %$bindings;
+    # return ($pem_id, $error_value, $error_product);
+    return (1001);
+}
+
+sub get_updates_from_sql {
+    my ($self, $sql) = @_;
+    $UPDATES_SQL = $sql;
+    return (
+        {
+            row_id => 999,
+            service_request_id => 1001,
+            updated_datetime => '2014-07-23 11:07:00',
+            status => 'closed',
+            description => 'Closed the ticket',
+        }
+    );
+}
+
+1;
diff --git a/t/open311/endpoint/warwick.t b/t/open311/endpoint/warwick.t
new file mode 100644
index 000000000..7021a37d7
--- /dev/null
+++ b/t/open311/endpoint/warwick.t
@@ -0,0 +1,291 @@
+use strict; use warnings;
+
+use Test::More;
+use Test::LongString;
+use Test::MockTime ':all';
+
+use Data::Dumper;
+use JSON;
+
+use t::open311::endpoint::Endpoint_Warwick;
+
+my $endpoint = t::open311::endpoint::Endpoint_Warwick->new;
+my $json = JSON->new;
+
+subtest "GET Service List" => sub {
+    my $res = $endpoint->run_test_request( GET => '/services.xml' );
+    ok $res->is_success, 'xml success';
+    my $expected = <<XML;
+<?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>
+    <metadata>true</metadata>
+    <service_code>PO</service_code>
+    <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
+        or diag $res->content;
+};
+
+subtest "POST OK" => sub {
+    set_fixed_time('2014-01-01T12:00:00Z');
+    my $res = $endpoint->run_test_request( 
+        POST => '/requests.json', 
+        api_key => 'test',
+        service_code => 'PO',
+        address_string => '22 Acacia Avenue',
+        first_name => 'Bob',
+        last_name => 'Mould',
+        'attribute[easting]' => 100,
+        'attribute[northing]' => 100,
+        'attribute[external_id]' => 1001,
+        'attribute[closest_address]' => '22 Acacia Avenue',
+    );
+    ok $res->is_success, 'valid request'
+        or diag $res->content;
+
+    is_deeply $json->decode($res->content),
+        [ {
+            "service_notice" => "Warwickshire Open311 Endpoint",
+            "service_request_id" => 1001
+        } ], 'correct json returned';
+
+    is_deeply \%t::open311::endpoint::Endpoint_Warwick::BINDINGS, 
+        {
+          ':ce_surname' => 'MOULD',
+          ':ce_y' => '100',
+          ':ce_x' => '100',
+          ':ce_work_phone' => '',
+          ':ce_contact_type' => 'ENQUIRER',
+          ':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_compl_user_type' => 'USER',
+          ':ce_status_code' => 'RE',
+          ':ce_cat' => 'REQS',
+          ':ce_forename' => 'BOB'
+        }, 
+        'bindings as expected';
+};
+
+subtest 'updates' => sub {
+    my $res = $endpoint->run_test_request( GET => '/servicerequestupdates.xml', );
+    ok $res->is_success, 'valid request' or diag $res->content;
+
+my $expected = <<XML;
+<?xml version="1.0" encoding="utf-8"?>
+<service_request_updates>
+  <request_updates>
+    <description>Closed the ticket</description>
+    <media_url></media_url>
+    <service_request_id>1001</service_request_id>
+    <status>closed</status>
+    <update_id>999</update_id>
+    <updated_datetime>2014-07-23T11:07:00+01:00</updated_datetime>
+  </request_updates>
+</service_request_updates>
+XML
+
+    is_string $res->content, $expected, 'xml string ok'
+    or diag $res->content;
+
+    chomp (my $expected_sql = <<SQL);
+SELECT * FROM (
+        SELECT
+            row_id,
+            service_request_id,
+            to_char(updated_timedate, 'YYYY-MM-DD HH24:MI:SS'),
+            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')
+        ORDER BY updated_timedate DESC) WHERE ROWNUM <= 1000
+SQL
+
+    is_string $t::open311::endpoint::Endpoint_Warwick::UPDATES_SQL, $expected_sql, 'SQL as expected';
+};
+
+restore_time();
+done_testing;