do no send alerts for non public reports

4 files changed, 231 insertions, 9 deletions

diff --git a/db/alert_types.sql b/db/alert_types.sql
index 4ebd33cf9..4116d29dc 100644
--- a/db/alert_types.sql
+++ b/db/alert_types.sql
@@ -17,7 +17,7 @@ insert into alert_type
     item_title, item_link, item_description, template)
 values ('new_problems', '', '',
     'New problems on FixMyStreet', '/', 'The latest problems reported by users',
-    'problem', 'problem.state in (''confirmed'', ''investigating'', ''planned'', ''in progress'', ''fixed'', ''fixed - council'', ''fixed - user'', ''closed'')', 'created desc',
+    'problem', 'problem.non_public = ''f'' and problem.state in (''confirmed'', ''investigating'', ''planned'', ''in progress'', ''fixed'', ''fixed - council'', ''fixed - user'', ''closed'')', 'created desc',
     '{{title}}, {{confirmed}}', '/report/{{id}}', '{{detail}}', 'alert-problem');
 
 -- New fixed problems anywhere on the site
@@ -28,7 +28,7 @@ insert into alert_type
     item_title, item_link, item_description, template)
 values ('new_fixed_problems', '', '',
     'Problems recently reported fixed on FixMyStreet', '/', 'The latest problems reported fixed by users',
-    'problem', 'problem.state in (''fixed'', ''fixed - user'', ''fixed - council'')', 'lastupdate desc',
+    'problem', 'problem.non_public = ''f'' and problem.state in (''fixed'', ''fixed - user'', ''fixed - council'')', 'lastupdate desc',
     '{{title}}, {{confirmed}}', '/report/{{id}}', '{{detail}}', 'alert-problem');
 
 -- New problems around a location
@@ -39,7 +39,7 @@ insert into alert_type
     item_title, item_link, item_description, template)
 values ('local_problems', '', '',
     'New local problems on FixMyStreet', '/', 'The latest local problems reported by users',
-    'problem_find_nearby(?, ?, ?) as nearby,problem', 'nearby.problem_id = problem.id and problem.state in (''confirmed'', ''investigating'', ''planned'', ''in progress'', ''fixed'', ''fixed - council'', ''fixed - user'', ''closed'')', 'created desc',
+    'problem_find_nearby(?, ?, ?) as nearby,problem', 'nearby.problem_id = problem.id and problem.non_public = ''f'' and problem.state in (''confirmed'', ''investigating'', ''planned'', ''in progress'', ''fixed'', ''fixed - council'', ''fixed - user'', ''closed'')', 'created desc',
     '{{title}}, {{confirmed}}', '/report/{{id}}', '{{detail}}', 'alert-problem-nearby');
 
 -- New problems around a location
@@ -50,7 +50,7 @@ insert into alert_type
     item_title, item_link, item_description, template)
 values ('local_problems_state', '', '',
     'New local problems on FixMyStreet', '/', 'The latest local problems reported by users',
-    'problem_find_nearby(?, ?, ?) as nearby,problem', 'nearby.problem_id = problem.id and problem.state in (?)', 'created desc',
+    'problem_find_nearby(?, ?, ?) as nearby,problem', 'nearby.problem_id = problem.id and problem.non_public = ''f'' and problem.state in (?)', 'created desc',
     '{{title}}, {{confirmed}}', '/report/{{id}}', '{{detail}}', 'alert-problem-nearby');
 
 -- New problems around a postcode
@@ -61,7 +61,7 @@ insert into alert_type
     item_title, item_link, item_description, template)
 values ('postcode_local_problems', '', '',
     'New problems near {{POSTCODE}} on FixMyStreet', '/', 'The latest local problems reported by users',
-    'problem_find_nearby(?, ?, ?) as nearby,problem', 'nearby.problem_id = problem.id and problem.state in (''confirmed'', ''investigating'', ''planned'', ''in progress'', ''fixed'', ''fixed - council'', ''fixed - user'', ''closed'')', 'created desc',
+    'problem_find_nearby(?, ?, ?) as nearby,problem', 'nearby.problem_id = problem.id and problem.non_public = ''f'' and problem.state in (''confirmed'', ''investigating'', ''planned'', ''in progress'', ''fixed'', ''fixed - council'', ''fixed - user'', ''closed'')', 'created desc',
     '{{title}}, {{confirmed}}', '/report/{{id}}', '{{detail}}', 'alert-problem-nearby');
 
 -- New problems around a postcode with a particular state
@@ -72,7 +72,7 @@ insert into alert_type
     item_title, item_link, item_description, template)
 values ('postcode_local_problems_state', '', '',
     'New problems near {{POSTCODE}} on FixMyStreet', '/', 'The latest local problems reported by users',
-    'problem_find_nearby(?, ?, ?) as nearby,problem', 'nearby.problem_id = problem.id and problem.state in (?)', 'created desc',
+    'problem_find_nearby(?, ?, ?) as nearby,problem', 'nearby.problem_id = problem.id and problem.non_public = ''f'' and problem.state in (?)', 'created desc',
     '{{title}}, {{confirmed}}', '/report/{{id}}', '{{detail}}', 'alert-problem-nearby');
 
 -- New problems sent to a particular council
@@ -83,7 +83,7 @@ insert into alert_type
     item_title, item_link, item_description, template)
 values ('council_problems', '', '',
     'New problems to {{COUNCIL}} on FixMyStreet', '/reports', 'The latest problems for {{COUNCIL}} reported by users',
-    'problem', 'problem.state in (''confirmed'', ''investigating'', ''planned'', ''in progress'', ''fixed'', ''fixed - council'', ''fixed - user'', ''closed'') and (council like ''%''||?||''%''
+    'problem', 'problem.non_public = ''f'' and problem.state in (''confirmed'', ''investigating'', ''planned'', ''in progress'', ''fixed'', ''fixed - council'', ''fixed - user'', ''closed'') and (council like ''%''||?||''%''
         or council is null) and areas like ''%,''||?||'',%''', 'created desc',
     '{{title}}, {{confirmed}}', '/report/{{id}}', '{{detail}}', 'alert-problem-council'
 );
@@ -97,7 +97,7 @@ insert into alert_type
 values ('ward_problems', '', '',
     'New problems for {{COUNCIL}} within {{WARD}} ward on FixMyStreet', '/reports',
     'The latest problems for {{COUNCIL}} within {{WARD}} ward reported by users',
-    'problem', 'problem.state in (''confirmed'', ''investigating'', ''planned'', ''in progress'', ''fixed'', ''fixed - council'', ''fixed - user'', ''closed'') and (council like ''%''||?||''%''
+    'problem', 'problem.non_public = ''f'' and problem.state in (''confirmed'', ''investigating'', ''planned'', ''in progress'', ''fixed'', ''fixed - council'', ''fixed - user'', ''closed'') and (council like ''%''||?||''%''
         or council is null) and areas like ''%,''||?||'',%''', 'created desc',
     '{{title}}, {{confirmed}}', '/report/{{id}}', '{{detail}}', 'alert-problem-ward'
 );
@@ -111,7 +111,7 @@ insert into alert_type
 values ('area_problems', '', '',
     'New problems within {{NAME}}''s boundary on FixMyStreet', '/reports',
     'The latest problems within {{NAME}}''s boundary reported by users', 'problem',
-    'problem.state in (''confirmed'', ''investigating'', ''planned'', ''in progress'', ''fixed'', ''fixed - council'', ''fixed - user'', ''closed'') and areas like ''%,''||?||'',%''', 'created desc',
+    'problem.non_public = ''f'' and problem.state in (''confirmed'', ''investigating'', ''planned'', ''in progress'', ''fixed'', ''fixed - council'', ''fixed - user'', ''closed'') and areas like ''%,''||?||'',%''', 'created desc',
     '{{title}}, {{confirmed}}', '/report/{{id}}', '{{detail}}', 'alert-problem-area'
 );
 
diff --git a/db/schema_0024-add_non_public_categories_and_problems.sql b/db/schema_0024-add_non_public_categories_and_problems.sql
index 007368ac0..c133b3ef2 100644
--- a/db/schema_0024-add_non_public_categories_and_problems.sql
+++ b/db/schema_0024-add_non_public_categories_and_problems.sql
@@ -6,4 +6,32 @@ BEGIN;
     ALTER TABLE problem
         ADD COLUMN non_public BOOLEAN DEFAULT 'f';
 
+
+    UPDATE alert_type set item_where = 'nearby.problem_id = problem.id and problem.non_public = ''f'' and problem.state in 
+    (''confirmed'', ''investigating'', ''planned'', ''in progress'', 
+     ''fixed'', ''fixed - council'', ''fixed - user'', ''closed'')' WHERE ref = 'postcode_local_problems';
+    UPDATE alert_type set item_where = 'problem.non_public = ''f'' and problem.state in 
+        (''confirmed'', ''investigating'', ''planned'', ''in progress'', 
+         ''fixed'', ''fixed - council'', ''fixed - user'', ''closed'' )' WHERE ref = 'new_problems';
+    UPDATE alert_type set item_where = 'problem.non_public = ''f'' and problem.state in (''fixed'', ''fixed - user'', ''fixed - council'')' WHERE ref = 'new_fixed_problems';
+    UPDATE alert_type set item_where = 'nearby.problem_id = problem.id and problem.non_public = ''f'' and problem.state in 
+    (''confirmed'', ''investigating'', ''planned'', ''in progress'', 
+     ''fixed'', ''fixed - council'', ''fixed - user'', ''closed'')' WHERE ref = 'local_problems';
+    UPDATE alert_type set item_where = 'problem.non_public = ''f'' and problem.state in 
+    (''confirmed'', ''investigating'', ''planned'', ''in progress'', 
+      ''fixed'', ''fixed - council'', ''fixed - user'', ''closed'') and 
+    (council like ''%''||?||''%'' or council is null) and 
+    areas like ''%,''||?||'',%''' WHERE ref = 'council_problems';
+    UPDATE alert_type set item_where = 'problem.non_public = ''f'' and problem.state in 
+    (''confirmed'', ''investigating'', ''planned'', ''in progress'', 
+     ''fixed'', ''fixed - council'', ''fixed - user'', ''closed'') and 
+    (council like ''%''||?||''%'' or council is null) and 
+    areas like ''%,''||?||'',%''' WHERE ref = 'ward_problems';
+    UPDATE alert_type set item_where = 'problem.non_public = ''f'' and problem.state in 
+    (''confirmed'', ''investigating'', ''planned'', ''in progress'', 
+     ''fixed'', ''fixed - council'', ''fixed - user'', ''closed'') and 
+    areas like ''%,''||?||'',%''' WHERE ref = 'area_problems';
+
+commit;
+
 COMMIT;
diff --git a/perllib/FixMyStreet/DB/ResultSet/AlertType.pm b/perllib/FixMyStreet/DB/ResultSet/AlertType.pm
index a0320ccc3..2d206d84e 100644
--- a/perllib/FixMyStreet/DB/ResultSet/AlertType.pm
+++ b/perllib/FixMyStreet/DB/ResultSet/AlertType.pm
@@ -65,6 +65,9 @@ sub email_alerts ($) {
             # call checks if this is the host that sends mail for this cobrand.
             next unless $cobrand->email_host;
 
+            # this is for the new_updates alerts
+            next if $row->{non_public} and $row->{user_id} != $row->{alert_user_id};
+
             my $hashref_restriction = $cobrand->site_restriction( $row->{cobrand_data} );
 
             FixMyStreet::App->model('DB::AlertSent')->create( {
@@ -151,6 +154,7 @@ sub email_alerts ($) {
             where nearby.problem_id = problem.id
             and problem.user_id = users.id
             and problem.state in ($states)
+            and problem.non_public = 'f'
             and problem.confirmed >= ? and problem.confirmed >= ms_current_timestamp() - '7 days'::interval
             and (select whenqueued from alert_sent where alert_sent.alert_id = ? and alert_sent.parameter::integer = problem.id) is null
             and users.email <> ?
diff --git a/t/app/controller/alert_new.t b/t/app/controller/alert_new.t
index d66590c57..84634273a 100644
--- a/t/app/controller/alert_new.t
+++ b/t/app/controller/alert_new.t
@@ -464,4 +464,194 @@ subtest "Test normal alert signups and that alerts are sent" => sub {
     $mech->delete_user($user2);
 };
 
+for my $test (
+    {
+        desc => 'check non public reports are not included in council problems alerts',
+        alert_params => {
+            alert_type => 'council_problems',
+            parameter => '2651',
+            parameter2 => '2651',
+        }
+    },
+    {
+        desc => 'check non public reports are not included in ward problems alerts',
+        alert_params => {
+            alert_type => 'ward_problems',
+            parameter => '2651',
+            parameter2 => '20728',
+        }
+    },
+    {
+        desc => 'check non public reports are not included in local problems alerts',
+        alert_params => {
+            alert_type => 'local_problems',
+            parameter => '-3.189944',
+            parameter2 => '55.951963',
+        }
+    },
+    {
+        desc => 'check non public reports are not included in area problems alerts',
+        alert_params => {
+            alert_type => 'area_problems',
+            parameter => '20728',
+            parameter2 => '20728',
+        }
+    },
+) {
+    subtest $test->{desc} => sub {
+        my $user1 = FixMyStreet::App->model('DB::User')
+          ->find_or_create( { email => 'reporter@example.com', name => 'Reporter User' } );
+        ok $user1, "created test user";
+        $user1->alerts->delete;
+
+        my $user2 = FixMyStreet::App->model('DB::User')
+          ->find_or_create( { email => 'alerts@example.com', name => 'Alert User' } );
+        ok $user2, "created test user";
+        $user2->alerts->delete;
+
+        my $dt = DateTime->now->add( minutes => -30 );
+        my $r_dt = $dt->clone->add( minutes => 20 );
+
+        my $alert_params = $test->{alert_params};
+        $alert_params->{user} = $user1;
+        $alert_params->{whensubscribed} = $dt;
+        $alert_params->{confirmed} = 1;
+
+        my $alert_user1 = FixMyStreet::App->model('DB::Alert')->create( $alert_params );
+        ok $alert_user1, "alert created";
+
+        my $report = FixMyStreet::App->model('DB::Problem')->find_or_create( {
+            postcode           => 'EH1 1BB',
+            council            => '2651',
+            areas              => ',11808,135007,14419,134935,2651,20728,',
+            category           => 'Street lighting',
+            title              => 'Alert test for non public reports',
+            detail             => 'Testing Detail',
+            used_map           => 1,
+            name               => $user2->name,
+            anonymous          => 0,
+            state              => 'confirmed',
+            confirmed          => $r_dt,
+            lastupdate         => $r_dt,
+            whensent           => $r_dt->clone->add( minutes => 5 ),
+            lang               => 'en-gb',
+            service            => '',
+            cobrand            => 'default',
+            cobrand_data       => '',
+            send_questionnaire => 1,
+            latitude           => '55.951963',
+            longitude          => '-3.189944',
+            user_id            => $user2->id,
+            non_public         => 1,
+        } );
+
+        $mech->clear_emails_ok;
+        FixMyStreet::App->model('DB::AlertType')->email_alerts();
+        $mech->email_count_is(0);
+
+        $report->update( { non_public => 0 } );
+        FixMyStreet::App->model('DB::AlertType')->email_alerts();
+        $mech->email_count_is(1);
+        my $email = $mech->get_email;
+        like $email->body, qr/Alert test for non public reports/, 'alert contains public report';
+
+        $mech->delete_user( $user1 );
+        $mech->delete_user( $user2 );
+    };
+}
+
+subtest 'check new updates alerts for non public reports only go to report owner' => sub {
+    my $user1 = FixMyStreet::App->model('DB::User')
+      ->find_or_create( { email => 'reporter@example.com', name => 'Reporter User' } );
+    ok $user1, "created test user";
+    $user1->alerts->delete;
+
+    my $user2 = FixMyStreet::App->model('DB::User')
+      ->find_or_create( { email => 'alerts@example.com', name => 'Alert User' } );
+    ok $user2, "created test user";
+    $user2->alerts->delete;
+
+    my $user3 = FixMyStreet::App->model('DB::User')
+      ->find_or_create( { email => 'updates@example.com', name => 'Update User' } );
+    ok $user3, "created test user";
+
+    my $dt = DateTime->now->add( minutes => -30 );
+    my $r_dt = $dt->clone->add( minutes => 20 );
+
+    my $report = FixMyStreet::App->model('DB::Problem')->find_or_create( {
+        postcode           => 'EH1 1BB',
+        council            => '2651',
+        areas              => ',11808,135007,14419,134935,2651,20728,',
+        category           => 'Street lighting',
+        title              => 'Alert test for non public reports',
+        detail             => 'Testing Detail',
+        used_map           => 1,
+        name               => $user2->name,
+        anonymous          => 0,
+        state              => 'confirmed',
+        confirmed          => $r_dt,
+        lastupdate         => $r_dt,
+        whensent           => $r_dt->clone->add( minutes => 5 ),
+        lang               => 'en-gb',
+        service            => '',
+        cobrand            => 'default',
+        cobrand_data       => '',
+        send_questionnaire => 1,
+        latitude           => '55.951963',
+        longitude          => '-3.189944',
+        user_id            => $user2->id,
+        non_public         => 1,
+    } );
+
+    my $update = FixMyStreet::App->model('DB::Comment')->create( {
+        problem_id => $report->id,
+        user_id    => $user3->id,
+        name       => 'Anonymous User',
+        mark_fixed => 'false',
+        text       => 'This is some more update text',
+        state      => 'confirmed',
+        confirmed  => $r_dt->clone->add( minutes => 8 ),
+        anonymous  => 't',
+    } );
+
+    my $alert_user1 = FixMyStreet::App->model('DB::Alert')->create( {
+            user       => $user1,
+            alert_type => 'new_updates',
+            parameter  => $report->id,
+            confirmed  => 1,
+            whensubscribed => $dt,
+    } );
+    ok $alert_user1, "alert created";
+
+
+    $mech->clear_emails_ok;
+    FixMyStreet::App->model('DB::AlertType')->email_alerts();
+    $mech->email_count_is(0);
+
+    my $alert_user2 = FixMyStreet::App->model('DB::Alert')->create( {
+            user       => $user2,
+            alert_type => 'new_updates',
+            parameter  => $report->id,
+            confirmed  => 1,
+            whensubscribed => $dt,
+    } );
+    ok $alert_user2, "alert created";
+
+    FixMyStreet::App->model('DB::AlertType')->email_alerts();
+    $mech->email_count_is(1);
+    my $email = $mech->get_email;
+    like $email->body, qr/This is some more update text/, 'alert contains update text';
+
+    $mech->clear_emails_ok;
+    $report->update( { non_public => 0 } );
+    FixMyStreet::App->model('DB::AlertType')->email_alerts();
+    $mech->email_count_is(1);
+    $email = $mech->get_email;
+    like $email->body, qr/This is some more update text/, 'alert contains update text';
+
+    $mech->delete_user( $user1 );
+    $mech->delete_user( $user2 );
+    $mech->delete_user( $user3 );
+};
+
 done_testing();