diff options
| -rw-r--r-- | db/alert_types.sql | 18 | ||||
| -rw-r--r-- | db/schema_0024-add_non_public_categories_and_problems.sql | 28 | ||||
| -rw-r--r-- | perllib/FixMyStreet/DB/ResultSet/AlertType.pm | 4 | ||||
| -rw-r--r-- | t/app/controller/alert_new.t | 190 |
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(); |