[UK] Add SQL problem restrictions for RSS feeds.

7 files changed, 37 insertions, 27 deletions

diff --git a/perllib/FixMyStreet/App/Controller/Rss.pm b/perllib/FixMyStreet/App/Controller/Rss.pm
index cb59689b4..c418c92b3 100755
--- a/perllib/FixMyStreet/App/Controller/Rss.pm
+++ b/perllib/FixMyStreet/App/Controller/Rss.pm
@@ -223,8 +223,11 @@ sub query_main : Private {
     # FIXME Do this in a nicer way at some point in the future...
     my $query = 'select * from ' . $alert_type->item_table . ' where '
         . ($alert_type->head_table ? $alert_type->head_table . '_id=? and ' : '')
-        . $alert_type->item_where . ' order by '
-        . $alert_type->item_order;
+        . $alert_type->item_where . ' ';
+    if ($c->cobrand->can('problems_sql_restriction')) {
+        $query .= $c->cobrand->problems_sql_restriction($alert_type->item_table);
+    }
+    $query .= ' order by ' . $alert_type->item_order;
     my $rss_limit = FixMyStreet->config('RSS_LIMIT');
     $query .= " limit $rss_limit" unless $c->stash->{type} =~ /^all/;
 
diff --git a/perllib/FixMyStreet/Cobrand/FixMyStreet.pm b/perllib/FixMyStreet/Cobrand/FixMyStreet.pm
index 03bc0c82b..a6161b570 100644
--- a/perllib/FixMyStreet/Cobrand/FixMyStreet.pm
+++ b/perllib/FixMyStreet/Cobrand/FixMyStreet.pm
@@ -41,6 +41,10 @@ sub problems_restriction {
     my $table = ref $rs eq 'FixMyStreet::DB::ResultSet::Nearby' ? 'problem' : 'me';
     return $rs->search({ "$table.cobrand" => { '!=' => 'tfl' } });
 }
+sub problems_sql_restriction {
+    my $self = shift;
+    return "AND cobrand != 'tfl'";
+}
 
 sub relative_url_for_report {
     my ( $self, $report ) = @_;
diff --git a/perllib/FixMyStreet/Cobrand/Hounslow.pm b/perllib/FixMyStreet/Cobrand/Hounslow.pm
index ab131cf84..fffaf52e9 100644
--- a/perllib/FixMyStreet/Cobrand/Hounslow.pm
+++ b/perllib/FixMyStreet/Cobrand/Hounslow.pm
@@ -166,12 +166,6 @@ sub lookup_site_code_config { {
 
 # Hounslow don't want any reports made before their go-live date visible on
 # their cobrand at all.
-sub problems_restriction {
-    my ($self, $rs) = @_;
-    my $table = ref $rs eq 'FixMyStreet::DB::ResultSet::Nearby' ? 'problem' : 'me';
-    return $rs->to_body($self->body)->search({
-      "$table.confirmed" => { '>=', '2019-05-06' }
-    });
-}
+sub cut_off_date { '2019-05-06' }
 
 1;
diff --git a/perllib/FixMyStreet/Cobrand/IsleOfWight.pm b/perllib/FixMyStreet/Cobrand/IsleOfWight.pm
index 83431c532..ab79be832 100644
--- a/perllib/FixMyStreet/Cobrand/IsleOfWight.pm
+++ b/perllib/FixMyStreet/Cobrand/IsleOfWight.pm
@@ -50,14 +50,7 @@ sub updates_disallowed {
 
 # Island Roads don't want any reports made before their go-live date visible on
 # their cobrand at all.
-sub problems_restriction {
-    my ($self, $rs) = @_;
-    return $rs if FixMyStreet->config('STAGING_SITE') or FixMyStreet->test_mode;
-    my $table = ref $rs eq 'FixMyStreet::DB::ResultSet::Nearby' ? 'problem' : 'me';
-    return $rs->to_body($self->body)->search({
-      "$table.confirmed" => { '>=', '2019-09-30' }
-    });
-}
+sub cut_off_date { '2019-09-30' }
 
 sub get_geocoder { 'OSM' }
 
diff --git a/perllib/FixMyStreet/Cobrand/TfL.pm b/perllib/FixMyStreet/Cobrand/TfL.pm
index 1423a7c63..c91b8a79c 100644
--- a/perllib/FixMyStreet/Cobrand/TfL.pm
+++ b/perllib/FixMyStreet/Cobrand/TfL.pm
@@ -103,13 +103,7 @@ sub report_sent_confirmation_email { 'id' }
 sub report_age { '6 weeks' }
 
 # We don't want any reports made before the go-live date visible
-sub problems_restriction {
-    my ($self, $rs) = @_;
-    my $table = ref $rs eq 'FixMyStreet::DB::ResultSet::Nearby' ? 'problem' : 'me';
-    return $rs->to_body($self->body)->search({
-      "$table.confirmed" => { '>=', '2019-12-09 12:00' }
-    });
-}
+sub cut_off_date { '2019-12-09 12:00' }
 
 sub password_expiry {
     return if FixMyStreet->test_mode;
diff --git a/perllib/FixMyStreet/Cobrand/UKCouncils.pm b/perllib/FixMyStreet/Cobrand/UKCouncils.pm
index bb3f6bbbd..ef0bcf4fb 100644
--- a/perllib/FixMyStreet/Cobrand/UKCouncils.pm
+++ b/perllib/FixMyStreet/Cobrand/UKCouncils.pm
@@ -56,10 +56,32 @@ sub body {
     return $body;
 }
 
+sub cut_off_date { '' }
+
 sub problems_restriction {
     my ($self, $rs) = @_;
     return $rs if FixMyStreet->staging_flag('skip_checks');
-    return $rs->to_body($self->body);
+    $rs = $rs->to_body($self->body);
+    if (my $date = $self->cut_off_date) {
+        my $table = ref $rs eq 'FixMyStreet::DB::ResultSet::Nearby' ? 'problem' : 'me';
+        $rs = $rs->search({
+            "$table.confirmed" => { '>=', $date }
+        });
+    }
+    return $rs;
+}
+
+sub problems_sql_restriction {
+    my ($self, $item_table) = @_;
+    my $q = '';
+    if (!$self->is_two_tier && $item_table ne 'comment') {
+        my $body_id = $self->body->id;
+        $q .= "AND regexp_split_to_array(bodies_str, ',') && ARRAY['$body_id']";
+    }
+    if (my $date = $self->cut_off_date) {
+        $q .= " AND confirmed >= '$date'";
+    }
+    return $q;
 }
 
 sub problems_on_map_restriction {
diff --git a/t/cobrand/isleofwight.t b/t/cobrand/isleofwight.t
index c28c30503..bd837b3c6 100644
--- a/t/cobrand/isleofwight.t
+++ b/t/cobrand/isleofwight.t
@@ -68,8 +68,8 @@ $admin_user->user_body_permissions->create({
 });
 
 my @reports = $mech->create_problems_for_body(1, $isleofwight->id, 'An Isle of wight report', {
-    confirmed => '2019-05-25 09:00',
-    lastupdate => '2019-05-25 09:00',
+    confirmed => '2019-10-25 09:00',
+    lastupdate => '2019-10-25 09:00',
     latitude => 50.7108,
     longitude => -1.29573,
     user => $user,