diff options
Diffstat (limited to 'db')
| -rw-r--r-- | db/alert_types.sql | 14 | ||||
| -rw-r--r-- | db/alert_types_eha.sql | 14 | ||||
| -rw-r--r-- | db/schema_0009-update_alerts_problem_state_queries.sql | 28 |
3 files changed, 42 insertions, 14 deletions
diff --git a/db/alert_types.sql b/db/alert_types.sql index eca83bb6f..4ebd33cf9 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'', ''fixed'')', 'created desc', + 'problem', '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'')', 'lastupdate desc', + 'problem', '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'', ''fixed'')', 'created desc', + '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', '{{title}}, {{confirmed}}', '/report/{{id}}', '{{detail}}', 'alert-problem-nearby'); -- New problems around a location @@ -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'', ''fixed'')', 'created desc', + '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', '{{title}}, {{confirmed}}', '/report/{{id}}', '{{detail}}', 'alert-problem-nearby'); -- New problems around a postcode with a particular state @@ -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'', ''fixed'') and (council like ''%''||?||''%'' + 'problem', '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'', ''fixed'') and (council like ''%''||?||''%'' + 'problem', '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'', ''fixed'') and areas like ''%,''||?||'',%''', 'created desc', + '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/alert_types_eha.sql b/db/alert_types_eha.sql index 17f44fcd9..af074717b 100644 --- a/db/alert_types_eha.sql +++ b/db/alert_types_eha.sql @@ -17,7 +17,7 @@ insert into alert_type item_title, item_link, item_description, template) values ('new_problems', '', '', 'New reports on reportemptyhomes.com', '/', 'The latest empty properties reported by users', - 'problem', 'problem.state in (''confirmed'', ''fixed'')', 'created desc', + 'problem', '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', '', '', 'Properties recently reported as put back to use on reportemptyhomes.com', '/', 'The latest properties reported back to use by users', - 'problem', 'problem.state in (''fixed'')', 'lastupdate desc', + 'problem', 'problem.state in (''fixed'', ''sixed - council'', ''fixed - user'')', '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 reports on reportemptyhomes.com', '/', 'The latest local reports reported by users', - 'problem_find_nearby(?, ?, ?) as nearby,problem', 'nearby.problem_id = problem.id and problem.state in (''confirmed'', ''fixed'')', 'created desc', + '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', '{{title}}, {{confirmed}}', '/report/{{id}}', '{{detail}}', 'alert-problem-nearby'); -- New problems around a postcode @@ -50,7 +50,7 @@ insert into alert_type item_title, item_link, item_description, template) values ('postcode_local_problems', '', '', 'New reports on reportemptyhomes.com near {{POSTCODE}}', '/', 'The latest local reports reported by users', - 'problem_find_nearby(?, ?, ?) as nearby,problem', 'nearby.problem_id = problem.id and problem.state in (''confirmed'', ''fixed'')', 'created desc', + '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', '{{title}}, {{confirmed}}', '/report/{{id}}', '{{detail}}', 'alert-problem-nearby'); -- New problems sent to a particular council @@ -61,7 +61,7 @@ insert into alert_type item_title, item_link, item_description, template) values ('council_problems', '', '', 'New reports to {{COUNCIL}} on reportemptyhomes.com', '/reports', 'The latest reports for {{COUNCIL}} reported by users', - 'problem', 'problem.state in (''confirmed'', ''fixed'') and (council like ''%''||?||''%'' + 'problem', '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' ); @@ -75,7 +75,7 @@ insert into alert_type values ('ward_problems', '', '', 'New reports for {{COUNCIL}} within {{WARD}} ward on reportemptyhomes.com', '/reports', 'The latest reports for {{COUNCIL}} within {{WARD}} ward reported by users', - 'problem', 'problem.state in (''confirmed'', ''fixed'') and (council like ''%''||?||''%'' + 'problem', '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' ); @@ -89,7 +89,7 @@ insert into alert_type values ('area_problems', '', '', 'New reports within {{NAME}}''s boundary on reportemptyhomes.com', '/reports', 'The latest reports within {{NAME}}''s boundary reported by users', 'problem', - 'problem.state in (''confirmed'', ''fixed'') and areas like ''%,''||?||'',%''', 'created desc', + '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_0009-update_alerts_problem_state_queries.sql b/db/schema_0009-update_alerts_problem_state_queries.sql new file mode 100644 index 000000000..fc5de927a --- /dev/null +++ b/db/schema_0009-update_alerts_problem_state_queries.sql @@ -0,0 +1,28 @@ +begin; + + UPDATE alert_type set item_where = 'nearby.problem_id = problem.id 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.state in + (''confirmed'', ''investigating'', ''planned'', ''in progress'', + ''fixed'', ''fixed - council'', ''fixed - user'', ''closed'' )' WHERE ref = 'new_problems'; + UPDATE alert_type set item_where = '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.state in + (''confirmed'', ''investigating'', ''planned'', ''in progress'', + ''fixed'', ''fixed - council'', ''fixed - user'', ''closed'')' WHERE ref = 'local_problems'; + UPDATE alert_type set item_where = '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.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.state in + (''confirmed'', ''investigating'', ''planned'', ''in progress'', + ''fixed'', ''fixed - council'', ''fixed - user'', ''closed'') and + areas like ''%,''||?||'',%''' WHERE ref = 'area_problems'; + +commit; |