-
-
Notifications
You must be signed in to change notification settings - Fork 235
/
alert_types_eha.sql
95 lines (87 loc) · 5.44 KB
/
alert_types_eha.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
-- New updates on a particular problem report
insert into alert_type
(ref, head_sql_query, head_table,
head_title, head_link, head_description,
item_table, item_where, item_order,
item_title, item_link, item_description, template)
values ('new_updates', 'select * from problem where id=?', 'problem',
'Updates on {{title}}', '/', 'Updates on {{title}}',
'comment', 'comment.state=''confirmed''', 'created desc',
'Update by {{name}}', '/report/{{problem_id}}#comment_{{id}}', '{{text}}', 'alert-update');
-- New problems anywhere on the site
insert into alert_type
(ref, head_sql_query, head_table,
head_title, head_link, head_description,
item_table, item_where, item_order,
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'', ''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
insert into alert_type
(ref, head_sql_query, head_table,
head_title, head_link, head_description,
item_table, item_where, item_order,
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'', ''sixed - council'', ''fixed - user'')', 'lastupdate desc',
'{{title}}, {{confirmed}}', '/report/{{id}}', '{{detail}}', 'alert-problem');
-- New problems around a location
insert into alert_type
(ref, head_sql_query, head_table,
head_title, head_link, head_description,
item_table, item_where, item_order,
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'', ''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
insert into alert_type
(ref, head_sql_query, head_table,
head_title, head_link, head_description,
item_table, item_where, item_order,
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'', ''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
insert into alert_type
(ref, head_sql_query, head_table,
head_title, head_link, head_description,
item_table, item_where, item_order,
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'', ''investigating'', ''planned'', ''in progress'', ''fixed'', ''fixed - council'', ''fixed - user'', ''closed'') and (bodies_str like ''%''||?||''%''
or bodies_str is null) and areas like ''%,''||?||'',%''', 'created desc',
'{{title}}, {{confirmed}}', '/report/{{id}}', '{{detail}}', 'alert-problem-council'
);
-- New problems within a particular ward sent to a particular council
insert into alert_type
(ref, head_sql_query, head_table,
head_title, head_link, head_description,
item_table, item_where, item_order,
item_title, item_link, item_description, template)
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'', ''investigating'', ''planned'', ''in progress'', ''fixed'', ''fixed - council'', ''fixed - user'', ''closed'') and (bodies_str like ''%''||?||''%''
or bodies_str is null) and areas like ''%,''||?||'',%''', 'created desc',
'{{title}}, {{confirmed}}', '/report/{{id}}', '{{detail}}', 'alert-problem-ward'
);
-- New problems within a particular voting area (ward, constituency, whatever)
insert into alert_type
(ref, head_sql_query, head_table,
head_title, head_link, head_description,
item_table, item_where, item_order,
item_title, item_link, item_description, template)
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'', ''investigating'', ''planned'', ''in progress'', ''fixed'', ''fixed - council'', ''fixed - user'', ''closed'') and areas like ''%,''||?||'',%''', 'created desc',
'{{title}}, {{confirmed}}', '/report/{{id}}', '{{detail}}', 'alert-problem-area'
);