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
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
|
INSERT INTO state (label, type, name) VALUES ('investigating', 'open', 'Investigating');
INSERT INTO state (label, type, name) VALUES ('in progress', 'open', 'In progress');
INSERT INTO state (label, type, name) VALUES ('planned', 'open', 'Planned');
INSERT INTO state (label, type, name) VALUES ('action scheduled', 'open', 'Action scheduled');
INSERT INTO state (label, type, name) VALUES ('unable to fix', 'closed', 'No further action');
INSERT INTO state (label, type, name) VALUES ('not responsible', 'closed', 'Not responsible');
INSERT INTO state (label, type, name) VALUES ('duplicate', 'closed', 'Duplicate');
INSERT INTO state (label, type, name) VALUES ('internal referral', 'closed', 'Internal referral');
INSERT INTO state (label, type, name) VALUES ('fixed', 'fixed', 'Fixed');
-- 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 problems on {{SITE_NAME}}', '/', 'The latest problems reported by users',
'problem',
'problem.non_public = ''f'' and problem.state NOT IN
(''unconfirmed'', ''hidden'', ''partial'')',
'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', '', '',
'Problems recently reported fixed on {{SITE_NAME}}', '/', 'The latest problems reported fixed by users',
'problem', 'problem.non_public = ''f'' and problem.state in (''fixed'', ''fixed - user'', ''fixed - council'')', '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 problems on {{SITE_NAME}}', '/', 'The latest local problems reported by users',
'problem_find_nearby(?, ?, ?) as nearby,problem',
'nearby.problem_id = problem.id and problem.non_public = ''f'' and problem.state NOT IN
(''unconfirmed'', ''hidden'', ''partial'')',
'created desc',
'{{title}}, {{confirmed}}', '/report/{{id}}', '{{detail}}', 'alert-problem-nearby');
-- 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_state', '', '',
'New local problems on {{SITE_NAME}}', '/', 'The latest local problems reported by users',
'problem_find_nearby(?, ?, ?) as nearby,problem', 'nearby.problem_id = problem.id and problem.non_public = ''f'' and problem.state in (?)', '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 problems near {{POSTCODE}} on {{SITE_NAME}}', '/', 'The latest local problems reported by users',
'problem_find_nearby(?, ?, ?) as nearby,problem',
'nearby.problem_id = problem.id and problem.non_public = ''f'' and problem.state NOT IN
(''unconfirmed'', ''hidden'', ''partial'')',
'created desc',
'{{title}}, {{confirmed}}', '/report/{{id}}', '{{detail}}', 'alert-problem-nearby');
-- New problems around a postcode with a particular state
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_state', '', '',
'New problems near {{POSTCODE}} on {{SITE_NAME}}', '/', 'The latest local problems reported by users',
'problem_find_nearby(?, ?, ?) as nearby,problem', 'nearby.problem_id = problem.id and problem.non_public = ''f'' and problem.state in (?)', 'created desc',
'{{title}}, {{confirmed}}', '/report/{{id}}', '{{detail}}', 'alert-problem-nearby');
-- New problems sent to a particular body
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 problems to {{COUNCIL}} on {{SITE_NAME}}', '/reports', 'The latest problems for {{COUNCIL}} reported by users',
'problem',
'problem.non_public = ''f'' and problem.state NOT IN
(''unconfirmed'', ''hidden'', ''partial'') AND
regexp_split_to_array(bodies_str, '','') && ARRAY[?]',
'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 problems for {{COUNCIL}} within {{WARD}} ward on {{SITE_NAME}}', '/reports',
'The latest problems for {{COUNCIL}} within {{WARD}} ward reported by users',
'problem',
'problem.non_public = ''f'' and problem.state NOT IN
(''unconfirmed'', ''hidden'', ''partial'') AND
(regexp_split_to_array(bodies_str, '','') && ARRAY[?] 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 problems within {{NAME}}''s boundary on {{SITE_NAME}}', '/reports',
'The latest problems within {{NAME}}''s boundary reported by users', 'problem',
'problem.non_public = ''f'' and problem.state NOT IN
(''unconfirmed'', ''hidden'', ''partial'') AND
areas like ''%,''||?||'',%''',
'created desc',
'{{title}}, {{confirmed}}', '/report/{{id}}', '{{detail}}', 'alert-problem-area'
);
|
