From 227163b2ae15f90707c8e8d9be26daea6c7d2474 Mon Sep 17 00:00:00 2001
From: Struan Donald <struan@exo.org.uk>
Date: Tue, 20 Sep 2011 15:14:29 +0100
Subject: update alert where clauses with extra states

---
 ...ma_0009-update_alerts_problem_state_queries.sql | 28 ++++++++++++++++++++++
 1 file changed, 28 insertions(+)
 create mode 100644 db/schema_0009-update_alerts_problem_state_queries.sql

(limited to 'db/schema_0009-update_alerts_problem_state_queries.sql')

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;
-- 
cgit v1.2.3

