Merge remote branch 'origin/master' into open311-consumer

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;