rev |
line source |
jbe@315
|
1 BEGIN;
|
jbe@315
|
2
|
jbe@315
|
3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
|
jbe@315
|
4 SELECT * FROM (VALUES ('2.1.1', 2, 1, 1))
|
jbe@315
|
5 AS "subquery"("string", "major", "minor", "revision");
|
jbe@315
|
6
|
jbe@315
|
7 ALTER TABLE "initiative" ADD COLUMN "harmonic_weight" NUMERIC(12, 3);
|
jbe@320
|
8 COMMENT ON COLUMN "initiative"."harmonic_weight" IS 'Indicates the relevancy of the initiative, calculated from the potential supporters weighted with the harmonic series to avoid a large number of clones affecting other initiative''s sorting positions too much; shall be used as secondary sorting key after "admitted" as primary sorting key';
|
jbe@315
|
9
|
jbe@315
|
10 ALTER TABLE "suggestion" ADD COLUMN "harmonic_weight" NUMERIC(12, 3);
|
jbe@315
|
11 COMMENT ON COLUMN "suggestion"."harmonic_weight" IS 'Indicates the relevancy of the suggestion, calculated from the supporters (positive "degree") of the suggestion weighted with the harmonic series to avoid a large number of clones affecting other suggestion''s sortings position too much';
|
jbe@315
|
12
|
jbe@315
|
13 CREATE VIEW "remaining_harmonic_supporter_weight" AS
|
jbe@315
|
14 SELECT
|
jbe@315
|
15 "direct_interest_snapshot"."issue_id",
|
jbe@315
|
16 "direct_interest_snapshot"."event",
|
jbe@315
|
17 "direct_interest_snapshot"."member_id",
|
jbe@315
|
18 "direct_interest_snapshot"."weight" AS "weight_num",
|
jbe@315
|
19 count("initiative"."id") AS "weight_den"
|
jbe@315
|
20 FROM "issue"
|
jbe@315
|
21 JOIN "direct_interest_snapshot"
|
jbe@315
|
22 ON "issue"."id" = "direct_interest_snapshot"."issue_id"
|
jbe@315
|
23 AND "issue"."latest_snapshot_event" = "direct_interest_snapshot"."event"
|
jbe@315
|
24 JOIN "direct_supporter_snapshot"
|
jbe@315
|
25 ON "direct_interest_snapshot"."issue_id" = "direct_supporter_snapshot"."issue_id"
|
jbe@315
|
26 AND "direct_interest_snapshot"."event" = "direct_supporter_snapshot"."event"
|
jbe@315
|
27 AND "direct_interest_snapshot"."member_id" = "direct_supporter_snapshot"."member_id"
|
jbe@315
|
28 JOIN "initiative"
|
jbe@315
|
29 ON "direct_supporter_snapshot"."initiative_id" = "initiative"."id"
|
jbe@321
|
30 AND (
|
jbe@321
|
31 "direct_supporter_snapshot"."satisfied" = TRUE OR
|
jbe@321
|
32 coalesce("initiative"."admitted", FALSE) = FALSE
|
jbe@321
|
33 )
|
jbe@315
|
34 AND "initiative"."harmonic_weight" ISNULL
|
jbe@315
|
35 GROUP BY
|
jbe@315
|
36 "direct_interest_snapshot"."issue_id",
|
jbe@315
|
37 "direct_interest_snapshot"."event",
|
jbe@315
|
38 "direct_interest_snapshot"."member_id",
|
jbe@315
|
39 "direct_interest_snapshot"."weight";
|
jbe@315
|
40
|
jbe@315
|
41 COMMENT ON VIEW "remaining_harmonic_supporter_weight" IS 'Helper view for function "set_harmonic_initiative_weights"';
|
jbe@315
|
42
|
jbe@315
|
43 CREATE VIEW "remaining_harmonic_initiative_weight_summands" AS
|
jbe@315
|
44 SELECT
|
jbe@315
|
45 "initiative"."issue_id",
|
jbe@315
|
46 "initiative"."id" AS "initiative_id",
|
jbe@320
|
47 "initiative"."admitted",
|
jbe@315
|
48 sum("remaining_harmonic_supporter_weight"."weight_num") AS "weight_num",
|
jbe@315
|
49 "remaining_harmonic_supporter_weight"."weight_den"
|
jbe@315
|
50 FROM "remaining_harmonic_supporter_weight"
|
jbe@315
|
51 JOIN "direct_supporter_snapshot"
|
jbe@315
|
52 ON "remaining_harmonic_supporter_weight"."issue_id" = "direct_supporter_snapshot"."issue_id"
|
jbe@315
|
53 AND "remaining_harmonic_supporter_weight"."event" = "direct_supporter_snapshot"."event"
|
jbe@315
|
54 AND "remaining_harmonic_supporter_weight"."member_id" = "direct_supporter_snapshot"."member_id"
|
jbe@315
|
55 JOIN "initiative"
|
jbe@315
|
56 ON "direct_supporter_snapshot"."initiative_id" = "initiative"."id"
|
jbe@321
|
57 AND (
|
jbe@321
|
58 "direct_supporter_snapshot"."satisfied" = TRUE OR
|
jbe@321
|
59 coalesce("initiative"."admitted", FALSE) = FALSE
|
jbe@321
|
60 )
|
jbe@315
|
61 AND "initiative"."harmonic_weight" ISNULL
|
jbe@315
|
62 GROUP BY
|
jbe@315
|
63 "initiative"."issue_id",
|
jbe@315
|
64 "initiative"."id",
|
jbe@320
|
65 "initiative"."admitted",
|
jbe@315
|
66 "remaining_harmonic_supporter_weight"."weight_den";
|
jbe@315
|
67
|
jbe@315
|
68 COMMENT ON VIEW "remaining_harmonic_initiative_weight_summands" IS 'Helper view for function "set_harmonic_initiative_weights"';
|
jbe@315
|
69
|
jbe@315
|
70 CREATE FUNCTION "set_harmonic_initiative_weights"
|
jbe@315
|
71 ( "issue_id_p" "issue"."id"%TYPE )
|
jbe@315
|
72 RETURNS VOID
|
jbe@315
|
73 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@315
|
74 DECLARE
|
jbe@315
|
75 "weight_row" "remaining_harmonic_initiative_weight_summands"%ROWTYPE;
|
jbe@315
|
76 "i" INT4;
|
jbe@315
|
77 "count_v" INT4;
|
jbe@315
|
78 "summand_v" FLOAT;
|
jbe@315
|
79 "id_ary" INT4[];
|
jbe@315
|
80 "weight_ary" FLOAT[];
|
jbe@315
|
81 "min_weight_v" FLOAT;
|
jbe@315
|
82 BEGIN
|
jbe@315
|
83 UPDATE "initiative" SET "harmonic_weight" = NULL
|
jbe@315
|
84 WHERE "issue_id" = "issue_id_p";
|
jbe@315
|
85 LOOP
|
jbe@315
|
86 "min_weight_v" := NULL;
|
jbe@315
|
87 "i" := 0;
|
jbe@315
|
88 "count_v" := 0;
|
jbe@315
|
89 FOR "weight_row" IN
|
jbe@315
|
90 SELECT * FROM "remaining_harmonic_initiative_weight_summands"
|
jbe@315
|
91 WHERE "issue_id" = "issue_id_p"
|
jbe@320
|
92 AND (
|
jbe@320
|
93 coalesce("admitted", FALSE) = FALSE OR NOT EXISTS (
|
jbe@320
|
94 SELECT NULL FROM "initiative"
|
jbe@320
|
95 WHERE "issue_id" = "issue_id_p"
|
jbe@320
|
96 AND "harmonic_weight" ISNULL
|
jbe@320
|
97 AND coalesce("admitted", FALSE) = FALSE
|
jbe@320
|
98 )
|
jbe@320
|
99 )
|
jbe@315
|
100 ORDER BY "initiative_id" DESC, "weight_den" DESC
|
jbe@320
|
101 -- NOTE: non-admitted initiatives placed first (at last positions),
|
jbe@320
|
102 -- latest initiatives treated worse in case of tie
|
jbe@315
|
103 LOOP
|
jbe@315
|
104 "summand_v" := "weight_row"."weight_num"::FLOAT / "weight_row"."weight_den"::FLOAT;
|
jbe@315
|
105 IF "i" = 0 OR "weight_row"."initiative_id" != "id_ary"["i"] THEN
|
jbe@315
|
106 "i" := "i" + 1;
|
jbe@315
|
107 "count_v" := "i";
|
jbe@315
|
108 "id_ary"["i"] := "weight_row"."initiative_id";
|
jbe@315
|
109 "weight_ary"["i"] := "summand_v";
|
jbe@315
|
110 ELSE
|
jbe@315
|
111 "weight_ary"["i"] := "weight_ary"["i"] + "summand_v";
|
jbe@315
|
112 END IF;
|
jbe@315
|
113 END LOOP;
|
jbe@315
|
114 EXIT WHEN "count_v" = 0;
|
jbe@315
|
115 "i" := 1;
|
jbe@315
|
116 LOOP
|
jbe@315
|
117 "weight_ary"["i"] := "weight_ary"["i"]::NUMERIC(18,9)::NUMERIC(12,3);
|
jbe@315
|
118 IF "min_weight_v" ISNULL OR "weight_ary"["i"] < "min_weight_v" THEN
|
jbe@315
|
119 "min_weight_v" := "weight_ary"["i"];
|
jbe@315
|
120 END IF;
|
jbe@315
|
121 "i" := "i" + 1;
|
jbe@315
|
122 EXIT WHEN "i" > "count_v";
|
jbe@315
|
123 END LOOP;
|
jbe@315
|
124 "i" := 1;
|
jbe@315
|
125 LOOP
|
jbe@315
|
126 IF "weight_ary"["i"] = "min_weight_v" THEN
|
jbe@315
|
127 UPDATE "initiative" SET "harmonic_weight" = "min_weight_v"
|
jbe@315
|
128 WHERE "id" = "id_ary"["i"];
|
jbe@315
|
129 EXIT;
|
jbe@315
|
130 END IF;
|
jbe@315
|
131 "i" := "i" + 1;
|
jbe@315
|
132 END LOOP;
|
jbe@315
|
133 END LOOP;
|
jbe@316
|
134 UPDATE "initiative" SET "harmonic_weight" = 0
|
jbe@316
|
135 WHERE "issue_id" = "issue_id_p" AND "harmonic_weight" ISNULL;
|
jbe@315
|
136 END;
|
jbe@315
|
137 $$;
|
jbe@315
|
138
|
jbe@315
|
139 COMMENT ON FUNCTION "set_harmonic_initiative_weights"
|
jbe@315
|
140 ( "issue"."id"%TYPE )
|
jbe@315
|
141 IS 'Calculates and sets "harmonic_weight" of initiatives in a given issue';
|
jbe@315
|
142
|
jbe@315
|
143 CREATE VIEW "remaining_harmonic_opinion_weight" AS
|
jbe@315
|
144 SELECT
|
jbe@315
|
145 "initiative"."issue_id",
|
jbe@315
|
146 "opinion"."initiative_id",
|
jbe@315
|
147 "direct_interest_snapshot"."member_id",
|
jbe@315
|
148 "direct_interest_snapshot"."weight" AS "weight_num",
|
jbe@315
|
149 count("opinion"."suggestion_id") AS "weight_den"
|
jbe@315
|
150 FROM "issue"
|
jbe@315
|
151 JOIN "direct_interest_snapshot"
|
jbe@315
|
152 ON "issue"."id" = "direct_interest_snapshot"."issue_id"
|
jbe@315
|
153 AND "issue"."latest_snapshot_event" = "direct_interest_snapshot"."event"
|
jbe@315
|
154 JOIN "initiative"
|
jbe@315
|
155 ON "direct_interest_snapshot"."issue_id" = "initiative"."issue_id"
|
jbe@315
|
156 JOIN "opinion"
|
jbe@315
|
157 ON "direct_interest_snapshot"."member_id" = "opinion"."member_id"
|
jbe@315
|
158 AND "initiative"."id" = "opinion"."initiative_id"
|
jbe@317
|
159 AND (
|
jbe@317
|
160 ("opinion"."degree" > 0 AND "opinion"."fulfilled" = FALSE) OR
|
jbe@317
|
161 ("opinion"."degree" < 0 AND "opinion"."fulfilled" = TRUE)
|
jbe@317
|
162 )
|
jbe@318
|
163 JOIN "suggestion"
|
jbe@318
|
164 ON "opinion"."suggestion_id" = "suggestion"."id"
|
jbe@318
|
165 AND "suggestion"."harmonic_weight" ISNULL
|
jbe@315
|
166 GROUP BY
|
jbe@315
|
167 "initiative"."issue_id",
|
jbe@315
|
168 "opinion"."initiative_id",
|
jbe@315
|
169 "direct_interest_snapshot"."member_id",
|
jbe@315
|
170 "direct_interest_snapshot"."weight";
|
jbe@315
|
171
|
jbe@315
|
172 COMMENT ON VIEW "remaining_harmonic_opinion_weight" IS 'Helper view for function "set_harmonic_suggestion_weights"';
|
jbe@315
|
173
|
jbe@315
|
174 CREATE VIEW "remaining_harmonic_suggestion_weight_summands" AS
|
jbe@315
|
175 SELECT
|
jbe@315
|
176 "suggestion"."initiative_id",
|
jbe@315
|
177 "opinion"."suggestion_id",
|
jbe@315
|
178 sum("remaining_harmonic_opinion_weight"."weight_num") AS "weight_num",
|
jbe@315
|
179 "remaining_harmonic_opinion_weight"."weight_den"
|
jbe@315
|
180 FROM "remaining_harmonic_opinion_weight"
|
jbe@315
|
181 JOIN "opinion"
|
jbe@315
|
182 ON "remaining_harmonic_opinion_weight"."initiative_id" = "opinion"."initiative_id"
|
jbe@315
|
183 AND "remaining_harmonic_opinion_weight"."member_id" = "opinion"."member_id"
|
jbe@319
|
184 AND (
|
jbe@319
|
185 ("opinion"."degree" > 0 AND "opinion"."fulfilled" = FALSE) OR
|
jbe@319
|
186 ("opinion"."degree" < 0 AND "opinion"."fulfilled" = TRUE)
|
jbe@319
|
187 )
|
jbe@315
|
188 JOIN "suggestion"
|
jbe@315
|
189 ON "opinion"."suggestion_id" = "suggestion"."id"
|
jbe@315
|
190 AND "suggestion"."harmonic_weight" ISNULL
|
jbe@315
|
191 GROUP BY
|
jbe@315
|
192 "suggestion"."initiative_id",
|
jbe@315
|
193 "opinion"."suggestion_id",
|
jbe@315
|
194 "remaining_harmonic_opinion_weight"."weight_den";
|
jbe@315
|
195
|
jbe@315
|
196 COMMENT ON VIEW "remaining_harmonic_suggestion_weight_summands" IS 'Helper view for function "set_harmonic_suggestion_weights"';
|
jbe@315
|
197
|
jbe@315
|
198 CREATE FUNCTION "set_harmonic_suggestion_weights"
|
jbe@315
|
199 ( "initiative_id_p" "initiative"."id"%TYPE )
|
jbe@315
|
200 RETURNS VOID
|
jbe@315
|
201 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@315
|
202 DECLARE
|
jbe@315
|
203 "weight_row" "remaining_harmonic_suggestion_weight_summands"%ROWTYPE;
|
jbe@315
|
204 "i" INT4;
|
jbe@315
|
205 "count_v" INT4;
|
jbe@315
|
206 "summand_v" FLOAT;
|
jbe@315
|
207 "id_ary" INT4[];
|
jbe@315
|
208 "weight_ary" FLOAT[];
|
jbe@315
|
209 "min_weight_v" FLOAT;
|
jbe@315
|
210 BEGIN
|
jbe@315
|
211 UPDATE "suggestion" SET "harmonic_weight" = NULL
|
jbe@315
|
212 WHERE "initiative_id" = "initiative_id_p";
|
jbe@315
|
213 LOOP
|
jbe@315
|
214 "min_weight_v" := NULL;
|
jbe@315
|
215 "i" := 0;
|
jbe@315
|
216 "count_v" := 0;
|
jbe@315
|
217 FOR "weight_row" IN
|
jbe@315
|
218 SELECT * FROM "remaining_harmonic_suggestion_weight_summands"
|
jbe@315
|
219 WHERE "initiative_id" = "initiative_id_p"
|
jbe@315
|
220 ORDER BY "suggestion_id" DESC, "weight_den" DESC
|
jbe@320
|
221 -- NOTE: latest suggestions treated worse in case of tie
|
jbe@315
|
222 LOOP
|
jbe@315
|
223 "summand_v" := "weight_row"."weight_num"::FLOAT / "weight_row"."weight_den"::FLOAT;
|
jbe@315
|
224 IF "i" = 0 OR "weight_row"."suggestion_id" != "id_ary"["i"] THEN
|
jbe@315
|
225 "i" := "i" + 1;
|
jbe@315
|
226 "count_v" := "i";
|
jbe@315
|
227 "id_ary"["i"] := "weight_row"."suggestion_id";
|
jbe@315
|
228 "weight_ary"["i"] := "summand_v";
|
jbe@315
|
229 ELSE
|
jbe@315
|
230 "weight_ary"["i"] := "weight_ary"["i"] + "summand_v";
|
jbe@315
|
231 END IF;
|
jbe@315
|
232 END LOOP;
|
jbe@315
|
233 EXIT WHEN "count_v" = 0;
|
jbe@315
|
234 "i" := 1;
|
jbe@315
|
235 LOOP
|
jbe@315
|
236 "weight_ary"["i"] := "weight_ary"["i"]::NUMERIC(18,9)::NUMERIC(12,3);
|
jbe@315
|
237 IF "min_weight_v" ISNULL OR "weight_ary"["i"] < "min_weight_v" THEN
|
jbe@315
|
238 "min_weight_v" := "weight_ary"["i"];
|
jbe@315
|
239 END IF;
|
jbe@315
|
240 "i" := "i" + 1;
|
jbe@315
|
241 EXIT WHEN "i" > "count_v";
|
jbe@315
|
242 END LOOP;
|
jbe@315
|
243 "i" := 1;
|
jbe@315
|
244 LOOP
|
jbe@315
|
245 IF "weight_ary"["i"] = "min_weight_v" THEN
|
jbe@315
|
246 UPDATE "suggestion" SET "harmonic_weight" = "min_weight_v"
|
jbe@315
|
247 WHERE "id" = "id_ary"["i"];
|
jbe@315
|
248 EXIT;
|
jbe@315
|
249 END IF;
|
jbe@315
|
250 "i" := "i" + 1;
|
jbe@315
|
251 END LOOP;
|
jbe@315
|
252 END LOOP;
|
jbe@316
|
253 UPDATE "suggestion" SET "harmonic_weight" = 0
|
jbe@316
|
254 WHERE "initiative_id" = "initiative_id_p" AND "harmonic_weight" ISNULL;
|
jbe@315
|
255 END;
|
jbe@315
|
256 $$;
|
jbe@315
|
257
|
jbe@315
|
258 COMMENT ON FUNCTION "set_harmonic_suggestion_weights"
|
jbe@315
|
259 ( "issue"."id"%TYPE )
|
jbe@315
|
260 IS 'Calculates and sets "harmonic_weight" of suggestions in a given initiative';
|
jbe@315
|
261
|
jbe@315
|
262 CREATE OR REPLACE FUNCTION "create_snapshot"
|
jbe@315
|
263 ( "issue_id_p" "issue"."id"%TYPE )
|
jbe@315
|
264 RETURNS VOID
|
jbe@315
|
265 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@315
|
266 DECLARE
|
jbe@315
|
267 "initiative_id_v" "initiative"."id"%TYPE;
|
jbe@315
|
268 "suggestion_id_v" "suggestion"."id"%TYPE;
|
jbe@315
|
269 BEGIN
|
jbe@315
|
270 PERFORM "lock_issue"("issue_id_p");
|
jbe@315
|
271 PERFORM "create_population_snapshot"("issue_id_p");
|
jbe@315
|
272 PERFORM "create_interest_snapshot"("issue_id_p");
|
jbe@315
|
273 UPDATE "issue" SET
|
jbe@315
|
274 "snapshot" = now(),
|
jbe@315
|
275 "latest_snapshot_event" = 'periodic',
|
jbe@315
|
276 "population" = (
|
jbe@315
|
277 SELECT coalesce(sum("weight"), 0)
|
jbe@315
|
278 FROM "direct_population_snapshot"
|
jbe@315
|
279 WHERE "issue_id" = "issue_id_p"
|
jbe@315
|
280 AND "event" = 'periodic'
|
jbe@315
|
281 )
|
jbe@315
|
282 WHERE "id" = "issue_id_p";
|
jbe@315
|
283 FOR "initiative_id_v" IN
|
jbe@315
|
284 SELECT "id" FROM "initiative" WHERE "issue_id" = "issue_id_p"
|
jbe@315
|
285 LOOP
|
jbe@315
|
286 UPDATE "initiative" SET
|
jbe@315
|
287 "supporter_count" = (
|
jbe@315
|
288 SELECT coalesce(sum("di"."weight"), 0)
|
jbe@315
|
289 FROM "direct_interest_snapshot" AS "di"
|
jbe@315
|
290 JOIN "direct_supporter_snapshot" AS "ds"
|
jbe@315
|
291 ON "di"."member_id" = "ds"."member_id"
|
jbe@315
|
292 WHERE "di"."issue_id" = "issue_id_p"
|
jbe@315
|
293 AND "di"."event" = 'periodic'
|
jbe@315
|
294 AND "ds"."initiative_id" = "initiative_id_v"
|
jbe@315
|
295 AND "ds"."event" = 'periodic'
|
jbe@315
|
296 ),
|
jbe@315
|
297 "informed_supporter_count" = (
|
jbe@315
|
298 SELECT coalesce(sum("di"."weight"), 0)
|
jbe@315
|
299 FROM "direct_interest_snapshot" AS "di"
|
jbe@315
|
300 JOIN "direct_supporter_snapshot" AS "ds"
|
jbe@315
|
301 ON "di"."member_id" = "ds"."member_id"
|
jbe@315
|
302 WHERE "di"."issue_id" = "issue_id_p"
|
jbe@315
|
303 AND "di"."event" = 'periodic'
|
jbe@315
|
304 AND "ds"."initiative_id" = "initiative_id_v"
|
jbe@315
|
305 AND "ds"."event" = 'periodic'
|
jbe@315
|
306 AND "ds"."informed"
|
jbe@315
|
307 ),
|
jbe@315
|
308 "satisfied_supporter_count" = (
|
jbe@315
|
309 SELECT coalesce(sum("di"."weight"), 0)
|
jbe@315
|
310 FROM "direct_interest_snapshot" AS "di"
|
jbe@315
|
311 JOIN "direct_supporter_snapshot" AS "ds"
|
jbe@315
|
312 ON "di"."member_id" = "ds"."member_id"
|
jbe@315
|
313 WHERE "di"."issue_id" = "issue_id_p"
|
jbe@315
|
314 AND "di"."event" = 'periodic'
|
jbe@315
|
315 AND "ds"."initiative_id" = "initiative_id_v"
|
jbe@315
|
316 AND "ds"."event" = 'periodic'
|
jbe@315
|
317 AND "ds"."satisfied"
|
jbe@315
|
318 ),
|
jbe@315
|
319 "satisfied_informed_supporter_count" = (
|
jbe@315
|
320 SELECT coalesce(sum("di"."weight"), 0)
|
jbe@315
|
321 FROM "direct_interest_snapshot" AS "di"
|
jbe@315
|
322 JOIN "direct_supporter_snapshot" AS "ds"
|
jbe@315
|
323 ON "di"."member_id" = "ds"."member_id"
|
jbe@315
|
324 WHERE "di"."issue_id" = "issue_id_p"
|
jbe@315
|
325 AND "di"."event" = 'periodic'
|
jbe@315
|
326 AND "ds"."initiative_id" = "initiative_id_v"
|
jbe@315
|
327 AND "ds"."event" = 'periodic'
|
jbe@315
|
328 AND "ds"."informed"
|
jbe@315
|
329 AND "ds"."satisfied"
|
jbe@315
|
330 )
|
jbe@315
|
331 WHERE "id" = "initiative_id_v";
|
jbe@315
|
332 FOR "suggestion_id_v" IN
|
jbe@315
|
333 SELECT "id" FROM "suggestion"
|
jbe@315
|
334 WHERE "initiative_id" = "initiative_id_v"
|
jbe@315
|
335 LOOP
|
jbe@315
|
336 UPDATE "suggestion" SET
|
jbe@315
|
337 "minus2_unfulfilled_count" = (
|
jbe@315
|
338 SELECT coalesce(sum("snapshot"."weight"), 0)
|
jbe@315
|
339 FROM "issue" CROSS JOIN "opinion"
|
jbe@315
|
340 JOIN "direct_interest_snapshot" AS "snapshot"
|
jbe@315
|
341 ON "snapshot"."issue_id" = "issue"."id"
|
jbe@315
|
342 AND "snapshot"."event" = "issue"."latest_snapshot_event"
|
jbe@315
|
343 AND "snapshot"."member_id" = "opinion"."member_id"
|
jbe@315
|
344 WHERE "issue"."id" = "issue_id_p"
|
jbe@315
|
345 AND "opinion"."suggestion_id" = "suggestion_id_v"
|
jbe@315
|
346 AND "opinion"."degree" = -2
|
jbe@315
|
347 AND "opinion"."fulfilled" = FALSE
|
jbe@315
|
348 ),
|
jbe@315
|
349 "minus2_fulfilled_count" = (
|
jbe@315
|
350 SELECT coalesce(sum("snapshot"."weight"), 0)
|
jbe@315
|
351 FROM "issue" CROSS JOIN "opinion"
|
jbe@315
|
352 JOIN "direct_interest_snapshot" AS "snapshot"
|
jbe@315
|
353 ON "snapshot"."issue_id" = "issue"."id"
|
jbe@315
|
354 AND "snapshot"."event" = "issue"."latest_snapshot_event"
|
jbe@315
|
355 AND "snapshot"."member_id" = "opinion"."member_id"
|
jbe@315
|
356 WHERE "issue"."id" = "issue_id_p"
|
jbe@315
|
357 AND "opinion"."suggestion_id" = "suggestion_id_v"
|
jbe@315
|
358 AND "opinion"."degree" = -2
|
jbe@315
|
359 AND "opinion"."fulfilled" = TRUE
|
jbe@315
|
360 ),
|
jbe@315
|
361 "minus1_unfulfilled_count" = (
|
jbe@315
|
362 SELECT coalesce(sum("snapshot"."weight"), 0)
|
jbe@315
|
363 FROM "issue" CROSS JOIN "opinion"
|
jbe@315
|
364 JOIN "direct_interest_snapshot" AS "snapshot"
|
jbe@315
|
365 ON "snapshot"."issue_id" = "issue"."id"
|
jbe@315
|
366 AND "snapshot"."event" = "issue"."latest_snapshot_event"
|
jbe@315
|
367 AND "snapshot"."member_id" = "opinion"."member_id"
|
jbe@315
|
368 WHERE "issue"."id" = "issue_id_p"
|
jbe@315
|
369 AND "opinion"."suggestion_id" = "suggestion_id_v"
|
jbe@315
|
370 AND "opinion"."degree" = -1
|
jbe@315
|
371 AND "opinion"."fulfilled" = FALSE
|
jbe@315
|
372 ),
|
jbe@315
|
373 "minus1_fulfilled_count" = (
|
jbe@315
|
374 SELECT coalesce(sum("snapshot"."weight"), 0)
|
jbe@315
|
375 FROM "issue" CROSS JOIN "opinion"
|
jbe@315
|
376 JOIN "direct_interest_snapshot" AS "snapshot"
|
jbe@315
|
377 ON "snapshot"."issue_id" = "issue"."id"
|
jbe@315
|
378 AND "snapshot"."event" = "issue"."latest_snapshot_event"
|
jbe@315
|
379 AND "snapshot"."member_id" = "opinion"."member_id"
|
jbe@315
|
380 WHERE "issue"."id" = "issue_id_p"
|
jbe@315
|
381 AND "opinion"."suggestion_id" = "suggestion_id_v"
|
jbe@315
|
382 AND "opinion"."degree" = -1
|
jbe@315
|
383 AND "opinion"."fulfilled" = TRUE
|
jbe@315
|
384 ),
|
jbe@315
|
385 "plus1_unfulfilled_count" = (
|
jbe@315
|
386 SELECT coalesce(sum("snapshot"."weight"), 0)
|
jbe@315
|
387 FROM "issue" CROSS JOIN "opinion"
|
jbe@315
|
388 JOIN "direct_interest_snapshot" AS "snapshot"
|
jbe@315
|
389 ON "snapshot"."issue_id" = "issue"."id"
|
jbe@315
|
390 AND "snapshot"."event" = "issue"."latest_snapshot_event"
|
jbe@315
|
391 AND "snapshot"."member_id" = "opinion"."member_id"
|
jbe@315
|
392 WHERE "issue"."id" = "issue_id_p"
|
jbe@315
|
393 AND "opinion"."suggestion_id" = "suggestion_id_v"
|
jbe@315
|
394 AND "opinion"."degree" = 1
|
jbe@315
|
395 AND "opinion"."fulfilled" = FALSE
|
jbe@315
|
396 ),
|
jbe@315
|
397 "plus1_fulfilled_count" = (
|
jbe@315
|
398 SELECT coalesce(sum("snapshot"."weight"), 0)
|
jbe@315
|
399 FROM "issue" CROSS JOIN "opinion"
|
jbe@315
|
400 JOIN "direct_interest_snapshot" AS "snapshot"
|
jbe@315
|
401 ON "snapshot"."issue_id" = "issue"."id"
|
jbe@315
|
402 AND "snapshot"."event" = "issue"."latest_snapshot_event"
|
jbe@315
|
403 AND "snapshot"."member_id" = "opinion"."member_id"
|
jbe@315
|
404 WHERE "issue"."id" = "issue_id_p"
|
jbe@315
|
405 AND "opinion"."suggestion_id" = "suggestion_id_v"
|
jbe@315
|
406 AND "opinion"."degree" = 1
|
jbe@315
|
407 AND "opinion"."fulfilled" = TRUE
|
jbe@315
|
408 ),
|
jbe@315
|
409 "plus2_unfulfilled_count" = (
|
jbe@315
|
410 SELECT coalesce(sum("snapshot"."weight"), 0)
|
jbe@315
|
411 FROM "issue" CROSS JOIN "opinion"
|
jbe@315
|
412 JOIN "direct_interest_snapshot" AS "snapshot"
|
jbe@315
|
413 ON "snapshot"."issue_id" = "issue"."id"
|
jbe@315
|
414 AND "snapshot"."event" = "issue"."latest_snapshot_event"
|
jbe@315
|
415 AND "snapshot"."member_id" = "opinion"."member_id"
|
jbe@315
|
416 WHERE "issue"."id" = "issue_id_p"
|
jbe@315
|
417 AND "opinion"."suggestion_id" = "suggestion_id_v"
|
jbe@315
|
418 AND "opinion"."degree" = 2
|
jbe@315
|
419 AND "opinion"."fulfilled" = FALSE
|
jbe@315
|
420 ),
|
jbe@315
|
421 "plus2_fulfilled_count" = (
|
jbe@315
|
422 SELECT coalesce(sum("snapshot"."weight"), 0)
|
jbe@315
|
423 FROM "issue" CROSS JOIN "opinion"
|
jbe@315
|
424 JOIN "direct_interest_snapshot" AS "snapshot"
|
jbe@315
|
425 ON "snapshot"."issue_id" = "issue"."id"
|
jbe@315
|
426 AND "snapshot"."event" = "issue"."latest_snapshot_event"
|
jbe@315
|
427 AND "snapshot"."member_id" = "opinion"."member_id"
|
jbe@315
|
428 WHERE "issue"."id" = "issue_id_p"
|
jbe@315
|
429 AND "opinion"."suggestion_id" = "suggestion_id_v"
|
jbe@315
|
430 AND "opinion"."degree" = 2
|
jbe@315
|
431 AND "opinion"."fulfilled" = TRUE
|
jbe@315
|
432 )
|
jbe@315
|
433 WHERE "suggestion"."id" = "suggestion_id_v";
|
jbe@315
|
434 END LOOP;
|
jbe@315
|
435 PERFORM "set_harmonic_suggestion_weights"("initiative_id_v");
|
jbe@315
|
436 END LOOP;
|
jbe@315
|
437 PERFORM "set_harmonic_initiative_weights"("issue_id_p");
|
jbe@315
|
438 RETURN;
|
jbe@315
|
439 END;
|
jbe@315
|
440 $$;
|
jbe@315
|
441
|
jbe@315
|
442 SELECT "set_harmonic_initiative_weights"("id") FROM "issue";
|
jbe@315
|
443 SELECT "set_harmonic_suggestion_weights"("id") FROM "initiative";
|
jbe@315
|
444
|
jbe@315
|
445 COMMIT;
|