| rev |
line source |
|
jbe@520
|
1 BEGIN;
|
|
jbe@520
|
2
|
|
jbe@520
|
3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
|
|
jbe@520
|
4 SELECT * FROM (VALUES ('3.2.2', 3, 2, 2))
|
|
jbe@520
|
5 AS "subquery"("string", "major", "minor", "revision");
|
|
jbe@520
|
6
|
|
jbe@520
|
7 CREATE OR REPLACE FUNCTION "featured_initiative"
|
|
jbe@520
|
8 ( "recipient_id_p" "member"."id"%TYPE,
|
|
jbe@520
|
9 "area_id_p" "area"."id"%TYPE )
|
|
jbe@520
|
10 RETURNS SETOF "initiative"."id"%TYPE
|
|
jbe@520
|
11 LANGUAGE 'plpgsql' STABLE AS $$
|
|
jbe@520
|
12 DECLARE
|
|
jbe@520
|
13 "counter_v" "member"."notification_counter"%TYPE;
|
|
jbe@520
|
14 "sample_size_v" "member"."notification_sample_size"%TYPE;
|
|
jbe@520
|
15 "initiative_id_ary" INT4[]; --"initiative"."id"%TYPE[]
|
|
jbe@520
|
16 "match_v" BOOLEAN;
|
|
jbe@520
|
17 "member_id_v" "member"."id"%TYPE;
|
|
jbe@520
|
18 "seed_v" TEXT;
|
|
jbe@520
|
19 "initiative_id_v" "initiative"."id"%TYPE;
|
|
jbe@520
|
20 BEGIN
|
|
jbe@520
|
21 SELECT "notification_counter", "notification_sample_size"
|
|
jbe@520
|
22 INTO "counter_v", "sample_size_v"
|
|
jbe@520
|
23 FROM "member" WHERE "id" = "recipient_id_p";
|
|
jbe@520
|
24 IF COALESCE("sample_size_v" <= 0, TRUE) THEN
|
|
jbe@520
|
25 RETURN;
|
|
jbe@520
|
26 END IF;
|
|
jbe@520
|
27 "initiative_id_ary" := '{}';
|
|
jbe@520
|
28 LOOP
|
|
jbe@520
|
29 "match_v" := FALSE;
|
|
jbe@520
|
30 FOR "member_id_v", "seed_v" IN
|
|
jbe@520
|
31 SELECT * FROM (
|
|
jbe@520
|
32 SELECT DISTINCT
|
|
jbe@520
|
33 "supporter"."member_id",
|
|
jbe@520
|
34 md5(
|
|
jbe@520
|
35 "recipient_id_p" || '-' ||
|
|
jbe@520
|
36 "counter_v" || '-' ||
|
|
jbe@520
|
37 "area_id_p" || '-' ||
|
|
jbe@520
|
38 "supporter"."member_id"
|
|
jbe@520
|
39 ) AS "seed"
|
|
jbe@520
|
40 FROM "supporter"
|
|
jbe@520
|
41 JOIN "initiative" ON "initiative"."id" = "supporter"."initiative_id"
|
|
jbe@520
|
42 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
|
|
jbe@520
|
43 WHERE "supporter"."member_id" != "recipient_id_p"
|
|
jbe@520
|
44 AND "issue"."area_id" = "area_id_p"
|
|
jbe@520
|
45 AND "issue"."state" IN ('admission', 'discussion', 'verification')
|
|
jbe@520
|
46 ) AS "subquery"
|
|
jbe@520
|
47 ORDER BY "seed"
|
|
jbe@520
|
48 LOOP
|
|
jbe@520
|
49 SELECT "initiative"."id" INTO "initiative_id_v"
|
|
jbe@520
|
50 FROM "initiative"
|
|
jbe@520
|
51 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
|
|
jbe@520
|
52 JOIN "area" ON "area"."id" = "issue"."area_id"
|
|
jbe@520
|
53 JOIN "supporter" ON "supporter"."initiative_id" = "initiative"."id"
|
|
jbe@520
|
54 LEFT JOIN "supporter" AS "self_support" ON
|
|
jbe@520
|
55 "self_support"."initiative_id" = "initiative"."id" AND
|
|
jbe@520
|
56 "self_support"."member_id" = "recipient_id_p"
|
|
jbe@520
|
57 LEFT JOIN "privilege" ON
|
|
jbe@520
|
58 "privilege"."member_id" = "recipient_id_p" AND
|
|
jbe@520
|
59 "privilege"."unit_id" = "area"."unit_id" AND
|
|
jbe@520
|
60 "privilege"."voting_right" = TRUE
|
|
jbe@520
|
61 LEFT JOIN "subscription" ON
|
|
jbe@520
|
62 "subscription"."member_id" = "recipient_id_p" AND
|
|
jbe@520
|
63 "subscription"."unit_id" = "area"."unit_id"
|
|
jbe@520
|
64 LEFT JOIN "ignored_initiative" ON
|
|
jbe@520
|
65 "ignored_initiative"."member_id" = "recipient_id_p" AND
|
|
jbe@520
|
66 "ignored_initiative"."initiative_id" = "initiative"."id"
|
|
jbe@520
|
67 WHERE "supporter"."member_id" = "member_id_v"
|
|
jbe@520
|
68 AND "issue"."area_id" = "area_id_p"
|
|
jbe@520
|
69 AND "issue"."state" IN ('admission', 'discussion', 'verification')
|
|
jbe@520
|
70 AND "initiative"."revoked" ISNULL
|
|
jbe@520
|
71 AND "self_support"."member_id" ISNULL
|
|
jbe@520
|
72 AND NOT "initiative_id_ary" @> ARRAY["initiative"."id"]
|
|
jbe@520
|
73 AND (
|
|
jbe@520
|
74 "privilege"."member_id" NOTNULL OR
|
|
jbe@520
|
75 "subscription"."member_id" NOTNULL )
|
|
jbe@520
|
76 AND "ignored_initiative"."member_id" ISNULL
|
|
jbe@520
|
77 AND NOT EXISTS (
|
|
jbe@520
|
78 SELECT NULL FROM "draft"
|
|
jbe@520
|
79 JOIN "ignored_member" ON
|
|
jbe@520
|
80 "ignored_member"."member_id" = "recipient_id_p" AND
|
|
jbe@520
|
81 "ignored_member"."other_member_id" = "draft"."author_id"
|
|
jbe@520
|
82 WHERE "draft"."initiative_id" = "initiative"."id"
|
|
jbe@520
|
83 )
|
|
jbe@520
|
84 ORDER BY md5("seed_v" || '-' || "initiative"."id")
|
|
jbe@520
|
85 LIMIT 1;
|
|
jbe@520
|
86 IF FOUND THEN
|
|
jbe@520
|
87 "match_v" := TRUE;
|
|
jbe@520
|
88 RETURN NEXT "initiative_id_v";
|
|
jbe@520
|
89 IF array_length("initiative_id_ary", 1) + 1 >= "sample_size_v" THEN
|
|
jbe@520
|
90 RETURN;
|
|
jbe@520
|
91 END IF;
|
|
jbe@520
|
92 "initiative_id_ary" := "initiative_id_ary" || "initiative_id_v";
|
|
jbe@520
|
93 END IF;
|
|
jbe@520
|
94 END LOOP;
|
|
jbe@520
|
95 EXIT WHEN NOT "match_v";
|
|
jbe@520
|
96 END LOOP;
|
|
jbe@520
|
97 RETURN;
|
|
jbe@520
|
98 END;
|
|
jbe@520
|
99 $$;
|
|
jbe@520
|
100
|
|
jbe@520
|
101 COMMIT;
|