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