diff options
Diffstat (limited to 'db/schema_0009-update_alerts_problem_state_queries.sql')
| -rw-r--r-- | db/schema_0009-update_alerts_problem_state_queries.sql | 28 |
1 files changed, 28 insertions, 0 deletions
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; |