liquid_feedback_core
view update/core-update.v3.2.1-v3.2.2.sql @ 521:65bd17395c28
Exclude locked members as recipients in view "scheduled_notification_to_send"
| author | jbe |
|---|---|
| date | Wed May 04 21:47:29 2016 +0200 (2016-05-04) |
| parents | 053190248598 |
| children | 2f1c06608def |
line source
1 BEGIN;
3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
4 SELECT * FROM (VALUES ('3.2.2', 3, 2, 2))
5 AS "subquery"("string", "major", "minor", "revision");
7 CREATE OR REPLACE FUNCTION "featured_initiative"
8 ( "recipient_id_p" "member"."id"%TYPE,
9 "area_id_p" "area"."id"%TYPE )
10 RETURNS SETOF "initiative"."id"%TYPE
11 LANGUAGE 'plpgsql' STABLE AS $$
12 DECLARE
13 "counter_v" "member"."notification_counter"%TYPE;
14 "sample_size_v" "member"."notification_sample_size"%TYPE;
15 "initiative_id_ary" INT4[]; --"initiative"."id"%TYPE[]
16 "match_v" BOOLEAN;
17 "member_id_v" "member"."id"%TYPE;
18 "seed_v" TEXT;
19 "initiative_id_v" "initiative"."id"%TYPE;
20 BEGIN
21 SELECT "notification_counter", "notification_sample_size"
22 INTO "counter_v", "sample_size_v"
23 FROM "member" WHERE "id" = "recipient_id_p";
24 IF COALESCE("sample_size_v" <= 0, TRUE) THEN
25 RETURN;
26 END IF;
27 "initiative_id_ary" := '{}';
28 LOOP
29 "match_v" := FALSE;
30 FOR "member_id_v", "seed_v" IN
31 SELECT * FROM (
32 SELECT DISTINCT
33 "supporter"."member_id",
34 md5(
35 "recipient_id_p" || '-' ||
36 "counter_v" || '-' ||
37 "area_id_p" || '-' ||
38 "supporter"."member_id"
39 ) AS "seed"
40 FROM "supporter"
41 JOIN "initiative" ON "initiative"."id" = "supporter"."initiative_id"
42 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
43 WHERE "supporter"."member_id" != "recipient_id_p"
44 AND "issue"."area_id" = "area_id_p"
45 AND "issue"."state" IN ('admission', 'discussion', 'verification')
46 ) AS "subquery"
47 ORDER BY "seed"
48 LOOP
49 SELECT "initiative"."id" INTO "initiative_id_v"
50 FROM "initiative"
51 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
52 JOIN "area" ON "area"."id" = "issue"."area_id"
53 JOIN "supporter" ON "supporter"."initiative_id" = "initiative"."id"
54 LEFT JOIN "supporter" AS "self_support" ON
55 "self_support"."initiative_id" = "initiative"."id" AND
56 "self_support"."member_id" = "recipient_id_p"
57 LEFT JOIN "privilege" ON
58 "privilege"."member_id" = "recipient_id_p" AND
59 "privilege"."unit_id" = "area"."unit_id" AND
60 "privilege"."voting_right" = TRUE
61 LEFT JOIN "subscription" ON
62 "subscription"."member_id" = "recipient_id_p" AND
63 "subscription"."unit_id" = "area"."unit_id"
64 LEFT JOIN "ignored_initiative" ON
65 "ignored_initiative"."member_id" = "recipient_id_p" AND
66 "ignored_initiative"."initiative_id" = "initiative"."id"
67 WHERE "supporter"."member_id" = "member_id_v"
68 AND "issue"."area_id" = "area_id_p"
69 AND "issue"."state" IN ('admission', 'discussion', 'verification')
70 AND "initiative"."revoked" ISNULL
71 AND "self_support"."member_id" ISNULL
72 AND NOT "initiative_id_ary" @> ARRAY["initiative"."id"]
73 AND (
74 "privilege"."member_id" NOTNULL OR
75 "subscription"."member_id" NOTNULL )
76 AND "ignored_initiative"."member_id" ISNULL
77 AND NOT EXISTS (
78 SELECT NULL FROM "draft"
79 JOIN "ignored_member" ON
80 "ignored_member"."member_id" = "recipient_id_p" AND
81 "ignored_member"."other_member_id" = "draft"."author_id"
82 WHERE "draft"."initiative_id" = "initiative"."id"
83 )
84 ORDER BY md5("seed_v" || '-' || "initiative"."id")
85 LIMIT 1;
86 IF FOUND THEN
87 "match_v" := TRUE;
88 RETURN NEXT "initiative_id_v";
89 IF array_length("initiative_id_ary", 1) + 1 >= "sample_size_v" THEN
90 RETURN;
91 END IF;
92 "initiative_id_ary" := "initiative_id_ary" || "initiative_id_v";
93 END IF;
94 END LOOP;
95 EXIT WHEN NOT "match_v";
96 END LOOP;
97 RETURN;
98 END;
99 $$;
101 CREATE OR REPLACE VIEW "scheduled_notification_to_send" AS
102 SELECT * FROM (
103 SELECT
104 "id" AS "recipient_id",
105 now() - CASE WHEN "notification_dow" ISNULL THEN
106 ( "notification_sent"::DATE + CASE
107 WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour"
108 THEN 0 ELSE 1 END
109 )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour"
110 ELSE
111 ( "notification_sent"::DATE +
112 ( 7 + "notification_dow" -
113 EXTRACT(DOW FROM
114 ( "notification_sent"::DATE + CASE
115 WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour"
116 THEN 0 ELSE 1 END
117 )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour"
118 )::INTEGER
119 ) % 7 +
120 CASE
121 WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour"
122 THEN 0 ELSE 1
123 END
124 )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour"
125 END AS "pending"
126 FROM (
127 SELECT
128 "id",
129 COALESCE("notification_sent", "activated") AS "notification_sent",
130 "notification_dow",
131 "notification_hour"
132 FROM "member"
133 WHERE "locked" = FALSE
134 AND "disable_notifications" = FALSE
135 AND "notification_hour" NOTNULL
136 ) AS "subquery1"
137 ) AS "subquery2"
138 WHERE "pending" > '0'::INTERVAL;
140 COMMIT;