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