rev |
line source |
jbe@478
|
1 BEGIN;
|
jbe@478
|
2
|
jbe@478
|
3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
|
jbe@478
|
4 SELECT * FROM (VALUES ('3.2.0', 3, 2, 0))
|
jbe@478
|
5 AS "subquery"("string", "major", "minor", "revision");
|
jbe@478
|
6
|
jbe@499
|
7 -- TODO: preliminary script
|
jbe@499
|
8
|
jbe@478
|
9 ALTER TABLE "member" ADD COLUMN "disable_notifications" BOOLEAN NOT NULL DEFAULT FALSE;
|
jbe@492
|
10 ALTER TABLE "member" ADD COLUMN "notification_counter" INT4 NOT NULL DEFAULT 1;
|
jbe@492
|
11 ALTER TABLE "member" ADD COLUMN "notification_sample_size" INT4 NOT NULL DEFAULT 3;
|
jbe@492
|
12 ALTER TABLE "member" ADD COLUMN "notification_dow" INT4 CHECK ("notification_dow" BETWEEN 0 AND 6);
|
jbe@492
|
13 ALTER TABLE "member" ADD COLUMN "notification_hour" INT4 CHECK ("notification_hour" BETWEEN 0 AND 23);
|
jbe@478
|
14
|
jbe@478
|
15 UPDATE "member" SET "disable_notifications" = TRUE WHERE "notify_level" = 'none';
|
jbe@499
|
16
|
jbe@500
|
17 DROP VIEW "selected_event_seen_by_member";
|
jbe@500
|
18 DROP VIEW "event_seen_by_member";
|
jbe@499
|
19 ALTER TABLE "member" DROP COLUMN "notify_level";
|
jbe@499
|
20 DROP TYPE "notify_level";
|
jbe@478
|
21
|
jbe@478
|
22 CREATE TABLE "subscription" (
|
jbe@478
|
23 PRIMARY KEY ("member_id", "unit_id"),
|
jbe@478
|
24 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
jbe@478
|
25 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
|
jbe@478
|
26 CREATE INDEX "subscription_unit_id_idx" ON "subscription" ("unit_id");
|
jbe@492
|
27
|
jbe@492
|
28 COMMENT ON TABLE "subscription" IS 'An entry in this table denotes that the member wishes to receive notifications regardless of his/her privileges in the given unit';
|
jbe@492
|
29
|
jbe@492
|
30 CREATE TABLE "ignored_area" (
|
jbe@492
|
31 PRIMARY KEY ("member_id", "area_id"),
|
jbe@492
|
32 "member_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
jbe@492
|
33 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
|
jbe@492
|
34 CREATE INDEX "ignored_area_area_id_idx" ON "ignored_area" ("area_id");
|
jbe@492
|
35
|
jbe@492
|
36 COMMENT ON TABLE "ignored_area" IS 'An entry in this table denotes that the member does not wish to receive notifications for the given subject area unless he/she declared interested in a particular issue';
|
jbe@492
|
37
|
jbe@492
|
38 CREATE TABLE "initiative_notification_sent" (
|
jbe@492
|
39 PRIMARY KEY ("member_id", "initiative_id"),
|
jbe@492
|
40 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
jbe@492
|
41 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
jbe@492
|
42 "last_draft_id" INT8 NOT NULL,
|
jbe@495
|
43 "last_suggestion_id" INT8 );
|
jbe@492
|
44 CREATE INDEX "initiative_notification_sent_initiative_idx" ON "initiative_notification_sent" ("initiative_id");
|
jbe@478
|
45
|
jbe@496
|
46 CREATE TABLE "newsletter" (
|
jbe@496
|
47 "id" SERIAL4 PRIMARY KEY,
|
jbe@496
|
48 "published" TIMESTAMPTZ NOT NULL,
|
jbe@496
|
49 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
jbe@496
|
50 "include_all_members" BOOLEAN NOT NULL,
|
jbe@496
|
51 "sent" TIMESTAMPTZ,
|
jbe@496
|
52 "subject" TEXT NOT NULL,
|
jbe@496
|
53 "content" TEXT NOT NULL );
|
jbe@496
|
54 CREATE INDEX "newsletter_unit_id_idx" ON "newsletter" ("unit_id", "published");
|
jbe@496
|
55 CREATE INDEX "newsletter_all_units_published_idx" ON "newsletter" ("published") WHERE "unit_id" ISNULL;
|
jbe@496
|
56 CREATE INDEX "newsletter_published_idx" ON "newsletter" ("published");
|
jbe@496
|
57
|
jbe@499
|
58 CREATE VIEW "event_for_notification" AS
|
jbe@499
|
59 SELECT
|
jbe@499
|
60 "member"."id" AS "recipient_id",
|
jbe@499
|
61 "event".*
|
jbe@499
|
62 FROM "member" CROSS JOIN "event"
|
jbe@499
|
63 JOIN "issue" ON "issue"."id" = "event"."issue_id"
|
jbe@499
|
64 JOIN "area" ON "area"."id" = "issue"."area_id"
|
jbe@499
|
65 LEFT JOIN "privilege" ON
|
jbe@499
|
66 "privilege"."member_id" = "member"."id" AND
|
jbe@499
|
67 "privilege"."unit_id" = "area"."unit_id" AND
|
jbe@499
|
68 "privilege"."voting_right" = TRUE
|
jbe@499
|
69 LEFT JOIN "subscription" ON
|
jbe@499
|
70 "subscription"."member_id" = "member"."id" AND
|
jbe@499
|
71 "subscription"."unit_id" = "area"."unit_id"
|
jbe@499
|
72 LEFT JOIN "ignored_area" ON
|
jbe@499
|
73 "ignored_area"."member_id" = "member"."id" AND
|
jbe@499
|
74 "ignored_area"."area_id" = "issue"."area_id"
|
jbe@499
|
75 LEFT JOIN "interest" ON
|
jbe@499
|
76 "interest"."member_id" = "member"."id" AND
|
jbe@499
|
77 "interest"."issue_id" = "event"."issue_id"
|
jbe@499
|
78 LEFT JOIN "supporter" ON
|
jbe@499
|
79 "supporter"."member_id" = "member"."id" AND
|
jbe@499
|
80 "supporter"."initiative_id" = "event"."initiative_id"
|
jbe@499
|
81 WHERE ("privilege"."member_id" NOTNULL OR "subscription"."member_id" NOTNULL)
|
jbe@499
|
82 AND ("ignored_area"."member_id" ISNULL OR "interest"."member_id" NOTNULL)
|
jbe@499
|
83 AND (
|
jbe@499
|
84 "event"."event" = 'issue_state_changed'::"event_type" OR
|
jbe@499
|
85 ( "event"."event" = 'initiative_revoked'::"event_type" AND
|
jbe@499
|
86 "supporter"."member_id" NOTNULL ) );
|
jbe@499
|
87
|
jbe@478
|
88 CREATE VIEW "updated_initiative" AS
|
jbe@478
|
89 SELECT
|
jbe@499
|
90 "supporter"."member_id" AS "recipient_id",
|
jbe@478
|
91 FALSE AS "featured",
|
jbe@499
|
92 "supporter"."initiative_id"
|
jbe@499
|
93 FROM "supporter"
|
jbe@499
|
94 JOIN "initiative" ON "supporter"."initiative_id" = "initiative"."id"
|
jbe@478
|
95 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
|
jbe@499
|
96 LEFT JOIN "initiative_notification_sent" AS "sent" ON
|
jbe@499
|
97 "sent"."member_id" = "supporter"."member_id" AND
|
jbe@499
|
98 "sent"."initiative_id" = "supporter"."initiative_id"
|
jbe@499
|
99 LEFT JOIN "ignored_initiative" ON
|
jbe@499
|
100 "ignored_initiative"."member_id" = "supporter"."member_id" AND
|
jbe@499
|
101 "ignored_initiative"."initiative_id" = "supporter"."initiative_id"
|
jbe@480
|
102 WHERE "issue"."state" IN ('admission', 'discussion')
|
jbe@503
|
103 AND "initiative"."revoked" ISNULL
|
jbe@499
|
104 AND "ignored_initiative"."member_id" ISNULL
|
jbe@478
|
105 AND (
|
jbe@478
|
106 EXISTS (
|
jbe@478
|
107 SELECT NULL FROM "draft"
|
jbe@499
|
108 LEFT JOIN "ignored_member" ON
|
jbe@499
|
109 "ignored_member"."member_id" = "supporter"."member_id" AND
|
jbe@499
|
110 "ignored_member"."other_member_id" = "draft"."author_id"
|
jbe@499
|
111 WHERE "draft"."initiative_id" = "supporter"."initiative_id"
|
jbe@478
|
112 AND "draft"."id" > "supporter"."draft_id"
|
jbe@499
|
113 AND "ignored_member"."member_id" ISNULL
|
jbe@478
|
114 ) OR EXISTS (
|
jbe@478
|
115 SELECT NULL FROM "suggestion"
|
jbe@492
|
116 LEFT JOIN "opinion" ON
|
jbe@492
|
117 "opinion"."member_id" = "supporter"."member_id" AND
|
jbe@492
|
118 "opinion"."suggestion_id" = "suggestion"."id"
|
jbe@499
|
119 LEFT JOIN "ignored_member" ON
|
jbe@499
|
120 "ignored_member"."member_id" = "supporter"."member_id" AND
|
jbe@499
|
121 "ignored_member"."other_member_id" = "suggestion"."author_id"
|
jbe@499
|
122 WHERE "suggestion"."initiative_id" = "supporter"."initiative_id"
|
jbe@492
|
123 AND "opinion"."member_id" ISNULL
|
jbe@499
|
124 AND COALESCE("suggestion"."id" > "sent"."last_suggestion_id", TRUE)
|
jbe@499
|
125 AND "ignored_member"."member_id" ISNULL
|
jbe@478
|
126 )
|
jbe@478
|
127 );
|
jbe@478
|
128
|
jbe@478
|
129 CREATE FUNCTION "featured_initiative"
|
jbe@499
|
130 ( "recipient_id_p" "member"."id"%TYPE,
|
jbe@499
|
131 "area_id_p" "area"."id"%TYPE )
|
jbe@499
|
132 RETURNS SETOF "initiative"."id"%TYPE
|
jbe@478
|
133 LANGUAGE 'plpgsql' STABLE AS $$
|
jbe@478
|
134 DECLARE
|
jbe@499
|
135 "counter_v" "member"."notification_counter"%TYPE;
|
jbe@499
|
136 "sample_size_v" "member"."notification_sample_size"%TYPE;
|
jbe@499
|
137 "initiative_id_ary" INT4[]; --"initiative"."id"%TYPE[]
|
jbe@499
|
138 "match_v" BOOLEAN;
|
jbe@478
|
139 "member_id_v" "member"."id"%TYPE;
|
jbe@478
|
140 "seed_v" TEXT;
|
jbe@499
|
141 "initiative_id_v" "initiative"."id"%TYPE;
|
jbe@478
|
142 BEGIN
|
jbe@499
|
143 SELECT "notification_counter", "notification_sample_size"
|
jbe@499
|
144 INTO "counter_v", "sample_size_v"
|
jbe@499
|
145 FROM "member" WHERE "id" = "recipient_id_p";
|
jbe@478
|
146 "initiative_id_ary" := '{}';
|
jbe@478
|
147 LOOP
|
jbe@478
|
148 "match_v" := FALSE;
|
jbe@478
|
149 FOR "member_id_v", "seed_v" IN
|
jbe@478
|
150 SELECT * FROM (
|
jbe@478
|
151 SELECT DISTINCT
|
jbe@478
|
152 "supporter"."member_id",
|
jbe@499
|
153 md5(
|
jbe@499
|
154 "recipient_id_p" || '-' ||
|
jbe@499
|
155 "counter_v" || '-' ||
|
jbe@499
|
156 "area_id_p" || '-' ||
|
jbe@499
|
157 "supporter"."member_id"
|
jbe@499
|
158 ) AS "seed"
|
jbe@478
|
159 FROM "supporter"
|
jbe@478
|
160 JOIN "initiative" ON "initiative"."id" = "supporter"."initiative_id"
|
jbe@478
|
161 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
|
jbe@499
|
162 WHERE "supporter"."member_id" != "recipient_id_p"
|
jbe@478
|
163 AND "issue"."area_id" = "area_id_p"
|
jbe@478
|
164 AND "issue"."state" IN ('admission', 'discussion', 'verification')
|
jbe@478
|
165 ) AS "subquery"
|
jbe@478
|
166 ORDER BY "seed"
|
jbe@478
|
167 LOOP
|
jbe@499
|
168 SELECT "initiative"."id" INTO "initiative_id_v"
|
jbe@478
|
169 FROM "initiative"
|
jbe@478
|
170 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
|
jbe@499
|
171 JOIN "area" ON "area"."id" = "issue"."area_id"
|
jbe@478
|
172 JOIN "supporter" ON "supporter"."initiative_id" = "initiative"."id"
|
jbe@478
|
173 LEFT JOIN "supporter" AS "self_support" ON
|
jbe@478
|
174 "self_support"."initiative_id" = "initiative"."id" AND
|
jbe@499
|
175 "self_support"."member_id" = "recipient_id_p"
|
jbe@499
|
176 LEFT JOIN "privilege" ON
|
jbe@499
|
177 "privilege"."member_id" = "recipient_id_p" AND
|
jbe@499
|
178 "privilege"."unit_id" = "area"."unit_id" AND
|
jbe@499
|
179 "privilege"."voting_right" = TRUE
|
jbe@499
|
180 LEFT JOIN "subscription" ON
|
jbe@499
|
181 "subscription"."member_id" = "recipient_id_p" AND
|
jbe@499
|
182 "subscription"."unit_id" = "area"."unit_id"
|
jbe@499
|
183 LEFT JOIN "ignored_initiative" ON
|
jbe@499
|
184 "ignored_initiative"."member_id" = "recipient_id_p" AND
|
jbe@499
|
185 "ignored_initiative"."initiative_id" = "initiative"."id"
|
jbe@478
|
186 WHERE "supporter"."member_id" = "member_id_v"
|
jbe@478
|
187 AND "issue"."area_id" = "area_id_p"
|
jbe@478
|
188 AND "issue"."state" IN ('admission', 'discussion', 'verification')
|
jbe@503
|
189 AND "initiative"."revoked" ISNULL
|
jbe@478
|
190 AND "self_support"."member_id" ISNULL
|
jbe@478
|
191 AND NOT "initiative_id_ary" @> ARRAY["initiative"."id"]
|
jbe@499
|
192 AND (
|
jbe@499
|
193 "privilege"."member_id" NOTNULL OR
|
jbe@499
|
194 "subscription"."member_id" NOTNULL )
|
jbe@499
|
195 AND "ignored_initiative"."member_id" ISNULL
|
jbe@499
|
196 AND NOT EXISTS (
|
jbe@499
|
197 SELECT NULL FROM "draft"
|
jbe@499
|
198 JOIN "ignored_member" ON
|
jbe@499
|
199 "ignored_member"."member_id" = "recipient_id_p" AND
|
jbe@499
|
200 "ignored_member"."other_member_id" = "draft"."author_id"
|
jbe@499
|
201 WHERE "draft"."initiative_id" = "initiative"."id"
|
jbe@499
|
202 )
|
jbe@478
|
203 ORDER BY md5("seed_v" || '-' || "initiative"."id")
|
jbe@478
|
204 LIMIT 1;
|
jbe@478
|
205 IF FOUND THEN
|
jbe@478
|
206 "match_v" := TRUE;
|
jbe@499
|
207 RETURN NEXT "initiative_id_v";
|
jbe@499
|
208 IF array_length("initiative_id_ary", 1) + 1 >= "sample_size_v" THEN
|
jbe@478
|
209 RETURN;
|
jbe@478
|
210 END IF;
|
jbe@499
|
211 "initiative_id_ary" := "initiative_id_ary" || "initiative_id_v";
|
jbe@478
|
212 END IF;
|
jbe@478
|
213 END LOOP;
|
jbe@478
|
214 EXIT WHEN NOT "match_v";
|
jbe@478
|
215 END LOOP;
|
jbe@478
|
216 RETURN;
|
jbe@478
|
217 END;
|
jbe@478
|
218 $$;
|
jbe@478
|
219
|
jbe@478
|
220 CREATE VIEW "updated_or_featured_initiative" AS
|
jbe@478
|
221 SELECT
|
jbe@499
|
222 "subquery".*,
|
jbe@478
|
223 NOT EXISTS (
|
jbe@478
|
224 SELECT NULL FROM "initiative" AS "better_initiative"
|
jbe@499
|
225 WHERE "better_initiative"."issue_id" = "initiative"."issue_id"
|
jbe@484
|
226 AND
|
jbe@502
|
227 ( COALESCE("better_initiative"."supporter_count", -1),
|
jbe@484
|
228 -"better_initiative"."id" ) >
|
jbe@502
|
229 ( COALESCE("initiative"."supporter_count", -1),
|
jbe@485
|
230 -"initiative"."id" )
|
jbe@499
|
231 ) AS "leading"
|
jbe@499
|
232 FROM (
|
jbe@499
|
233 SELECT * FROM "updated_initiative"
|
jbe@499
|
234 UNION ALL
|
jbe@499
|
235 SELECT
|
jbe@499
|
236 "member"."id" AS "recipient_id",
|
jbe@499
|
237 TRUE AS "featured",
|
jbe@499
|
238 "featured_initiative_id" AS "initiative_id"
|
jbe@499
|
239 FROM "member" CROSS JOIN "area"
|
jbe@499
|
240 CROSS JOIN LATERAL
|
jbe@499
|
241 "featured_initiative"("member"."id", "area"."id") AS "featured_initiative_id"
|
jbe@499
|
242 JOIN "initiative" ON "initiative"."id" = "featured_initiative_id"
|
jbe@499
|
243 ) AS "subquery"
|
jbe@499
|
244 JOIN "initiative" ON "initiative"."id" = "subquery"."initiative_id";
|
jbe@478
|
245
|
jbe@478
|
246 CREATE VIEW "leading_complement_initiative" AS
|
jbe@478
|
247 SELECT * FROM (
|
jbe@499
|
248 SELECT DISTINCT ON ("uf_initiative"."recipient_id", "initiative"."issue_id")
|
jbe@499
|
249 "uf_initiative"."recipient_id",
|
jbe@478
|
250 FALSE AS "featured",
|
jbe@499
|
251 "uf_initiative"."initiative_id",
|
jbe@499
|
252 TRUE AS "leading"
|
jbe@492
|
253 FROM "updated_or_featured_initiative" AS "uf_initiative"
|
jbe@499
|
254 JOIN "initiative" AS "uf_initiative_full" ON
|
jbe@499
|
255 "uf_initiative_full"."id" = "uf_initiative"."initiative_id"
|
jbe@492
|
256 JOIN "initiative" ON
|
jbe@499
|
257 "initiative"."issue_id" = "uf_initiative_full"."issue_id"
|
jbe@503
|
258 WHERE "initiative"."revoked" ISNULL
|
jbe@478
|
259 ORDER BY
|
jbe@499
|
260 "uf_initiative"."recipient_id",
|
jbe@478
|
261 "initiative"."issue_id",
|
jbe@502
|
262 "initiative"."supporter_count" DESC,
|
jbe@478
|
263 "initiative"."id"
|
jbe@478
|
264 ) AS "subquery"
|
jbe@478
|
265 WHERE NOT EXISTS (
|
jbe@478
|
266 SELECT NULL FROM "updated_or_featured_initiative" AS "other"
|
jbe@499
|
267 WHERE "other"."recipient_id" = "subquery"."recipient_id"
|
jbe@499
|
268 AND "other"."initiative_id" = "subquery"."initiative_id"
|
jbe@478
|
269 );
|
jbe@478
|
270
|
jbe@492
|
271 CREATE VIEW "unfiltered_initiative_for_notification" AS
|
jbe@499
|
272 SELECT
|
jbe@499
|
273 "subquery".*,
|
jbe@499
|
274 "supporter"."member_id" NOTNULL AS "supported",
|
jbe@499
|
275 CASE WHEN "supporter"."member_id" NOTNULL THEN
|
jbe@499
|
276 EXISTS (
|
jbe@499
|
277 SELECT NULL FROM "draft"
|
jbe@499
|
278 WHERE "draft"."initiative_id" = "subquery"."initiative_id"
|
jbe@499
|
279 AND "draft"."id" > "supporter"."draft_id"
|
jbe@499
|
280 )
|
jbe@499
|
281 ELSE
|
jbe@499
|
282 EXISTS (
|
jbe@499
|
283 SELECT NULL FROM "draft"
|
jbe@499
|
284 WHERE "draft"."initiative_id" = "subquery"."initiative_id"
|
jbe@499
|
285 AND COALESCE("draft"."id" > "sent"."last_draft_id", TRUE)
|
jbe@499
|
286 )
|
jbe@499
|
287 END AS "new_draft",
|
jbe@499
|
288 CASE WHEN "supporter"."member_id" NOTNULL THEN
|
jbe@499
|
289 ( SELECT count(1) FROM "suggestion"
|
jbe@499
|
290 LEFT JOIN "opinion" ON
|
jbe@499
|
291 "opinion"."member_id" = "supporter"."member_id" AND
|
jbe@499
|
292 "opinion"."suggestion_id" = "suggestion"."id"
|
jbe@499
|
293 WHERE "suggestion"."initiative_id" = "subquery"."initiative_id"
|
jbe@499
|
294 AND "opinion"."member_id" ISNULL
|
jbe@499
|
295 AND COALESCE("suggestion"."id" > "sent"."last_suggestion_id", TRUE)
|
jbe@499
|
296 )
|
jbe@499
|
297 ELSE
|
jbe@499
|
298 ( SELECT count(1) FROM "suggestion"
|
jbe@499
|
299 WHERE "suggestion"."initiative_id" = "subquery"."initiative_id"
|
jbe@499
|
300 AND COALESCE("suggestion"."id" > "sent"."last_suggestion_id", TRUE)
|
jbe@499
|
301 )
|
jbe@499
|
302 END AS "new_suggestion_count"
|
jbe@499
|
303 FROM (
|
jbe@499
|
304 SELECT * FROM "updated_or_featured_initiative"
|
jbe@499
|
305 UNION ALL
|
jbe@499
|
306 SELECT * FROM "leading_complement_initiative"
|
jbe@499
|
307 ) AS "subquery"
|
jbe@499
|
308 LEFT JOIN "supporter" ON
|
jbe@499
|
309 "supporter"."member_id" = "subquery"."recipient_id" AND
|
jbe@499
|
310 "supporter"."initiative_id" = "subquery"."initiative_id"
|
jbe@499
|
311 LEFT JOIN "initiative_notification_sent" AS "sent" ON
|
jbe@499
|
312 "sent"."member_id" = "subquery"."recipient_id" AND
|
jbe@499
|
313 "sent"."initiative_id" = "subquery"."initiative_id";
|
jbe@478
|
314
|
jbe@492
|
315 CREATE VIEW "initiative_for_notification" AS
|
jbe@499
|
316 SELECT "unfiltered1".*
|
jbe@499
|
317 FROM "unfiltered_initiative_for_notification" "unfiltered1"
|
jbe@499
|
318 JOIN "initiative" AS "initiative1" ON
|
jbe@499
|
319 "initiative1"."id" = "unfiltered1"."initiative_id"
|
jbe@499
|
320 JOIN "issue" AS "issue1" ON "issue1"."id" = "initiative1"."issue_id"
|
jbe@492
|
321 WHERE EXISTS (
|
jbe@492
|
322 SELECT NULL
|
jbe@499
|
323 FROM "unfiltered_initiative_for_notification" "unfiltered2"
|
jbe@499
|
324 JOIN "initiative" AS "initiative2" ON
|
jbe@499
|
325 "initiative2"."id" = "unfiltered2"."initiative_id"
|
jbe@499
|
326 JOIN "issue" AS "issue2" ON "issue2"."id" = "initiative2"."issue_id"
|
jbe@499
|
327 WHERE "unfiltered1"."recipient_id" = "unfiltered2"."recipient_id"
|
jbe@492
|
328 AND "issue1"."area_id" = "issue2"."area_id"
|
jbe@499
|
329 AND ("unfiltered2"."new_draft" OR "unfiltered2"."new_suggestion_count" > 0 )
|
jbe@492
|
330 );
|
jbe@492
|
331
|
jbe@497
|
332 CREATE VIEW "newsletter_to_send" AS
|
jbe@497
|
333 SELECT
|
jbe@499
|
334 "member"."id" AS "recipient_id",
|
jbe@499
|
335 "newsletter"."id" AS "newsletter_id"
|
jbe@497
|
336 FROM "newsletter" CROSS JOIN "member"
|
jbe@497
|
337 LEFT JOIN "privilege" ON
|
jbe@497
|
338 "privilege"."member_id" = "member"."id" AND
|
jbe@497
|
339 "privilege"."unit_id" = "newsletter"."unit_id" AND
|
jbe@497
|
340 "privilege"."voting_right" = TRUE
|
jbe@497
|
341 LEFT JOIN "subscription" ON
|
jbe@497
|
342 "subscription"."member_id" = "member"."id" AND
|
jbe@497
|
343 "subscription"."unit_id" = "newsletter"."unit_id"
|
jbe@498
|
344 WHERE "newsletter"."published" <= now()
|
jbe@497
|
345 AND "newsletter"."sent" ISNULL
|
jbe@497
|
346 AND "member"."locked" = FALSE
|
jbe@497
|
347 AND (
|
jbe@497
|
348 "member"."disable_notifications" = FALSE OR
|
jbe@497
|
349 "newsletter"."include_all_members" = TRUE )
|
jbe@497
|
350 AND (
|
jbe@497
|
351 "newsletter"."unit_id" ISNULL OR
|
jbe@497
|
352 "privilege"."member_id" NOTNULL OR
|
jbe@497
|
353 "subscription"."member_id" NOTNULL );
|
jbe@497
|
354
|
jbe@492
|
355 CREATE FUNCTION "get_initiatives_for_notification"
|
jbe@501
|
356 ( "recipient_id_p" "member"."id"%TYPE )
|
jbe@492
|
357 RETURNS SETOF "initiative_for_notification"
|
jbe@492
|
358 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@492
|
359 DECLARE
|
jbe@492
|
360 "result_row" "initiative_for_notification"%ROWTYPE;
|
jbe@492
|
361 "last_draft_id_v" "draft"."id"%TYPE;
|
jbe@492
|
362 "last_suggestion_id_v" "suggestion"."id"%TYPE;
|
jbe@492
|
363 BEGIN
|
jbe@492
|
364 PERFORM "require_transaction_isolation"();
|
jbe@501
|
365 PERFORM NULL FROM "member" WHERE "id" = "recipient_id_p" FOR UPDATE;
|
jbe@492
|
366 FOR "result_row" IN
|
jbe@492
|
367 SELECT * FROM "initiative_for_notification"
|
jbe@501
|
368 WHERE "recipient_id" = "recipient_id_p"
|
jbe@492
|
369 LOOP
|
jbe@492
|
370 SELECT "id" INTO "last_draft_id_v" FROM "draft"
|
jbe@499
|
371 WHERE "draft"."initiative_id" = "result_row"."initiative_id"
|
jbe@492
|
372 ORDER BY "id" DESC LIMIT 1;
|
jbe@492
|
373 SELECT "id" INTO "last_suggestion_id_v" FROM "suggestion"
|
jbe@499
|
374 WHERE "suggestion"."initiative_id" = "result_row"."initiative_id"
|
jbe@492
|
375 ORDER BY "id" DESC LIMIT 1;
|
jbe@492
|
376 INSERT INTO "initiative_notification_sent"
|
jbe@492
|
377 ("member_id", "initiative_id", "last_draft_id", "last_suggestion_id")
|
jbe@492
|
378 VALUES (
|
jbe@501
|
379 "recipient_id_p",
|
jbe@499
|
380 "result_row"."initiative_id",
|
jbe@493
|
381 "last_draft_id_v",
|
jbe@493
|
382 "last_suggestion_id_v" )
|
jbe@492
|
383 ON CONFLICT ("member_id", "initiative_id") DO UPDATE SET
|
jbe@492
|
384 "last_draft_id" = CASE
|
jbe@494
|
385 WHEN "initiative_notification_sent"."last_draft_id" > "last_draft_id_v"
|
jbe@494
|
386 THEN "initiative_notification_sent"."last_draft_id"
|
jbe@492
|
387 ELSE "last_draft_id_v"
|
jbe@492
|
388 END,
|
jbe@492
|
389 "last_suggestion_id" = CASE
|
jbe@494
|
390 WHEN "initiative_notification_sent"."last_suggestion_id" > "last_suggestion_id_v"
|
jbe@494
|
391 THEN "initiative_notification_sent"."last_suggestion_id"
|
jbe@492
|
392 ELSE "last_suggestion_id_v"
|
jbe@492
|
393 END;
|
jbe@492
|
394 RETURN NEXT "result_row";
|
jbe@492
|
395 END LOOP;
|
jbe@492
|
396 DELETE FROM "initiative_notification_sent"
|
jbe@492
|
397 USING "initiative", "issue"
|
jbe@501
|
398 WHERE "initiative_notification_sent"."member_id" = "recipient_id_p"
|
jbe@492
|
399 AND "initiative"."id" = "initiative_notification_sent"."initiative_id"
|
jbe@492
|
400 AND "issue"."id" = "initiative"."issue_id"
|
jbe@492
|
401 AND ( "issue"."closed" NOTNULL OR "issue"."fully_frozen" NOTNULL );
|
jbe@492
|
402 UPDATE "member" SET "notification_counter" = "notification_counter" + 1
|
jbe@501
|
403 WHERE "id" = "recipient_id_p";
|
jbe@492
|
404 RETURN;
|
jbe@492
|
405 END;
|
jbe@492
|
406 $$;
|
jbe@492
|
407
|
jbe@478
|
408 COMMIT;
|