rev |
line source |
jbe@87
|
1 BEGIN;
|
jbe@87
|
2
|
jbe@87
|
3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
|
jbe@87
|
4 SELECT * FROM (VALUES ('1.2.9', 1, 2, 9))
|
jbe@87
|
5 AS "subquery"("string", "major", "minor", "revision");
|
jbe@87
|
6
|
jbe@87
|
7 CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
|
jbe@87
|
8 RETURNS VOID
|
jbe@87
|
9 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@87
|
10 DECLARE
|
jbe@87
|
11 "issue_row" "issue"%ROWTYPE;
|
jbe@87
|
12 "member_id_v" "member"."id"%TYPE;
|
jbe@87
|
13 BEGIN
|
jbe@87
|
14 PERFORM "lock_issue"("issue_id_p");
|
jbe@87
|
15 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
|
jbe@87
|
16 DELETE FROM "delegating_voter"
|
jbe@87
|
17 WHERE "issue_id" = "issue_id_p";
|
jbe@87
|
18 DELETE FROM "direct_voter"
|
jbe@87
|
19 WHERE "issue_id" = "issue_id_p"
|
jbe@87
|
20 AND "autoreject" = TRUE;
|
jbe@87
|
21 DELETE FROM "direct_voter" USING "member"
|
jbe@87
|
22 WHERE "direct_voter"."member_id" = "member"."id"
|
jbe@87
|
23 AND "direct_voter"."issue_id" = "issue_id_p"
|
jbe@87
|
24 AND "member"."active" = FALSE;
|
jbe@87
|
25 UPDATE "direct_voter" SET "weight" = 1
|
jbe@87
|
26 WHERE "issue_id" = "issue_id_p";
|
jbe@87
|
27 PERFORM "add_vote_delegations"("issue_id_p");
|
jbe@87
|
28 FOR "member_id_v" IN
|
jbe@87
|
29 SELECT "interest"."member_id"
|
jbe@87
|
30 FROM "interest"
|
jbe@87
|
31 JOIN "member"
|
jbe@87
|
32 ON "interest"."member_id" = "member"."id"
|
jbe@87
|
33 LEFT JOIN "direct_voter"
|
jbe@87
|
34 ON "interest"."member_id" = "direct_voter"."member_id"
|
jbe@87
|
35 AND "interest"."issue_id" = "direct_voter"."issue_id"
|
jbe@87
|
36 LEFT JOIN "delegating_voter"
|
jbe@87
|
37 ON "interest"."member_id" = "delegating_voter"."member_id"
|
jbe@87
|
38 AND "interest"."issue_id" = "delegating_voter"."issue_id"
|
jbe@87
|
39 WHERE "interest"."issue_id" = "issue_id_p"
|
jbe@87
|
40 AND "interest"."autoreject" = TRUE
|
jbe@87
|
41 AND "member"."active"
|
jbe@87
|
42 AND "direct_voter"."member_id" ISNULL
|
jbe@87
|
43 AND "delegating_voter"."member_id" ISNULL
|
jbe@87
|
44 UNION SELECT "membership"."member_id"
|
jbe@87
|
45 FROM "membership"
|
jbe@87
|
46 JOIN "member"
|
jbe@87
|
47 ON "membership"."member_id" = "member"."id"
|
jbe@87
|
48 LEFT JOIN "interest"
|
jbe@87
|
49 ON "membership"."member_id" = "interest"."member_id"
|
jbe@87
|
50 AND "interest"."issue_id" = "issue_id_p"
|
jbe@87
|
51 LEFT JOIN "direct_voter"
|
jbe@87
|
52 ON "membership"."member_id" = "direct_voter"."member_id"
|
jbe@87
|
53 AND "direct_voter"."issue_id" = "issue_id_p"
|
jbe@87
|
54 LEFT JOIN "delegating_voter"
|
jbe@87
|
55 ON "membership"."member_id" = "delegating_voter"."member_id"
|
jbe@87
|
56 AND "delegating_voter"."issue_id" = "issue_id_p"
|
jbe@87
|
57 WHERE "membership"."area_id" = "issue_row"."area_id"
|
jbe@87
|
58 AND "membership"."autoreject" = TRUE
|
jbe@87
|
59 AND "member"."active"
|
jbe@87
|
60 AND "interest"."autoreject" ISNULL
|
jbe@87
|
61 AND "direct_voter"."member_id" ISNULL
|
jbe@87
|
62 AND "delegating_voter"."member_id" ISNULL
|
jbe@87
|
63 LOOP
|
jbe@87
|
64 INSERT INTO "direct_voter"
|
jbe@87
|
65 ("member_id", "issue_id", "weight", "autoreject") VALUES
|
jbe@87
|
66 ("member_id_v", "issue_id_p", 1, TRUE);
|
jbe@87
|
67 INSERT INTO "vote" (
|
jbe@87
|
68 "member_id",
|
jbe@87
|
69 "issue_id",
|
jbe@87
|
70 "initiative_id",
|
jbe@87
|
71 "grade"
|
jbe@87
|
72 ) SELECT
|
jbe@87
|
73 "member_id_v" AS "member_id",
|
jbe@87
|
74 "issue_id_p" AS "issue_id",
|
jbe@87
|
75 "id" AS "initiative_id",
|
jbe@87
|
76 -1 AS "grade"
|
jbe@87
|
77 FROM "initiative" WHERE "issue_id" = "issue_id_p";
|
jbe@87
|
78 END LOOP;
|
jbe@87
|
79 PERFORM "add_vote_delegations"("issue_id_p");
|
jbe@87
|
80 UPDATE "issue" SET
|
jbe@87
|
81 "closed" = now(),
|
jbe@87
|
82 "voter_count" = (
|
jbe@87
|
83 SELECT coalesce(sum("weight"), 0)
|
jbe@87
|
84 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
|
jbe@87
|
85 )
|
jbe@87
|
86 WHERE "id" = "issue_id_p";
|
jbe@87
|
87 UPDATE "initiative" SET
|
jbe@87
|
88 "positive_votes" = "vote_counts"."positive_votes",
|
jbe@87
|
89 "negative_votes" = "vote_counts"."negative_votes",
|
jbe@87
|
90 "agreed" = CASE WHEN "majority_strict" THEN
|
jbe@87
|
91 "vote_counts"."positive_votes" * "majority_den" >
|
jbe@87
|
92 "majority_num" *
|
jbe@87
|
93 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
|
jbe@87
|
94 ELSE
|
jbe@87
|
95 "vote_counts"."positive_votes" * "majority_den" >=
|
jbe@87
|
96 "majority_num" *
|
jbe@87
|
97 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
|
jbe@87
|
98 END
|
jbe@87
|
99 FROM
|
jbe@87
|
100 ( SELECT
|
jbe@87
|
101 "initiative"."id" AS "initiative_id",
|
jbe@87
|
102 coalesce(
|
jbe@87
|
103 sum(
|
jbe@87
|
104 CASE WHEN "grade" > 0 THEN "direct_voter"."weight" ELSE 0 END
|
jbe@87
|
105 ),
|
jbe@87
|
106 0
|
jbe@87
|
107 ) AS "positive_votes",
|
jbe@87
|
108 coalesce(
|
jbe@87
|
109 sum(
|
jbe@87
|
110 CASE WHEN "grade" < 0 THEN "direct_voter"."weight" ELSE 0 END
|
jbe@87
|
111 ),
|
jbe@87
|
112 0
|
jbe@87
|
113 ) AS "negative_votes"
|
jbe@87
|
114 FROM "initiative"
|
jbe@87
|
115 JOIN "issue" ON "initiative"."issue_id" = "issue"."id"
|
jbe@87
|
116 JOIN "policy" ON "issue"."policy_id" = "policy"."id"
|
jbe@87
|
117 LEFT JOIN "direct_voter"
|
jbe@87
|
118 ON "direct_voter"."issue_id" = "initiative"."issue_id"
|
jbe@87
|
119 LEFT JOIN "vote"
|
jbe@87
|
120 ON "vote"."initiative_id" = "initiative"."id"
|
jbe@87
|
121 AND "vote"."member_id" = "direct_voter"."member_id"
|
jbe@87
|
122 WHERE "initiative"."issue_id" = "issue_id_p"
|
jbe@87
|
123 AND "initiative"."admitted" -- NOTE: NULL case is handled too
|
jbe@87
|
124 GROUP BY "initiative"."id"
|
jbe@87
|
125 ) AS "vote_counts",
|
jbe@87
|
126 "issue",
|
jbe@87
|
127 "policy"
|
jbe@87
|
128 WHERE "vote_counts"."initiative_id" = "initiative"."id"
|
jbe@87
|
129 AND "issue"."id" = "initiative"."issue_id"
|
jbe@87
|
130 AND "policy"."id" = "issue"."policy_id";
|
jbe@87
|
131 -- NOTE: "closed" column of issue must be set at this point
|
jbe@87
|
132 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
|
jbe@87
|
133 INSERT INTO "battle" (
|
jbe@87
|
134 "issue_id",
|
jbe@87
|
135 "winning_initiative_id", "losing_initiative_id",
|
jbe@87
|
136 "count"
|
jbe@87
|
137 ) SELECT
|
jbe@87
|
138 "issue_id",
|
jbe@87
|
139 "winning_initiative_id", "losing_initiative_id",
|
jbe@87
|
140 "count"
|
jbe@87
|
141 FROM "battle_view" WHERE "issue_id" = "issue_id_p";
|
jbe@87
|
142 END;
|
jbe@87
|
143 $$;
|
jbe@87
|
144
|
jbe@87
|
145 COMMIT;
|