| rev |
line source |
|
jbe@360
|
1 BEGIN;
|
|
jbe@360
|
2
|
|
jbe@360
|
3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
|
|
jbe@360
|
4 SELECT * FROM (VALUES ('2.2.1', 2, 2, 1))
|
|
jbe@360
|
5 AS "subquery"("string", "major", "minor", "revision");
|
|
jbe@360
|
6
|
|
jbe@360
|
7 ALTER TABLE "initiative" ADD COLUMN "final_suggestion_order_calculated" BOOLEAN NOT NULL DEFAULT FALSE;
|
|
jbe@360
|
8 COMMENT ON COLUMN "initiative"."final_suggestion_order_calculated" IS 'Set to TRUE, when "proportional_order" of suggestions has been calculated the last time';
|
|
jbe@360
|
9
|
|
jbe@360
|
10 ALTER TABLE "suggestion" ADD COLUMN "proportional_order" INT4;
|
|
jbe@378
|
11 COMMENT ON COLUMN "suggestion"."proportional_order" IS 'To be used for sorting suggestions within an initiative; NULL values sort last; updated by "lf_update_suggestion_order"';
|
|
jbe@360
|
12
|
|
jbe@360
|
13 CREATE VIEW "initiative_suggestion_order_calculation" AS
|
|
jbe@360
|
14 SELECT
|
|
jbe@360
|
15 "initiative"."id" AS "initiative_id",
|
|
jbe@360
|
16 ("issue"."closed" NOTNULL OR "issue"."fully_frozen" NOTNULL) AS "final"
|
|
jbe@360
|
17 FROM "initiative" JOIN "issue"
|
|
jbe@360
|
18 ON "initiative"."issue_id" = "issue"."id"
|
|
jbe@360
|
19 WHERE ("issue"."closed" ISNULL AND "issue"."fully_frozen" ISNULL)
|
|
jbe@360
|
20 OR ("initiative"."final_suggestion_order_calculated" = FALSE);
|
|
jbe@360
|
21 COMMENT ON VIEW "initiative_suggestion_order_calculation" IS 'Initiatives, where the "proportional_order" of its suggestions has to be calculated';
|
|
jbe@360
|
22 COMMENT ON COLUMN "initiative_suggestion_order_calculation"."final" IS 'Set to TRUE, if the issue is fully frozen or closed, and the calculation has to be done only once for one last time';
|
|
jbe@360
|
23
|
|
jbe@360
|
24 CREATE VIEW "individual_suggestion_ranking" AS
|
|
jbe@360
|
25 SELECT
|
|
jbe@360
|
26 "opinion"."initiative_id",
|
|
jbe@360
|
27 "opinion"."member_id",
|
|
jbe@360
|
28 "direct_interest_snapshot"."weight",
|
|
jbe@360
|
29 CASE WHEN
|
|
jbe@360
|
30 ("opinion"."degree" = 2 AND "opinion"."fulfilled" = FALSE) OR
|
|
jbe@360
|
31 ("opinion"."degree" = -2 AND "opinion"."fulfilled" = TRUE)
|
|
jbe@360
|
32 THEN 1 ELSE
|
|
jbe@360
|
33 CASE WHEN
|
|
jbe@360
|
34 ("opinion"."degree" = 1 AND "opinion"."fulfilled" = FALSE) OR
|
|
jbe@360
|
35 ("opinion"."degree" = -1 AND "opinion"."fulfilled" = TRUE)
|
|
jbe@360
|
36 THEN 2 ELSE
|
|
jbe@360
|
37 CASE WHEN
|
|
jbe@360
|
38 ("opinion"."degree" = 2 AND "opinion"."fulfilled" = TRUE) OR
|
|
jbe@360
|
39 ("opinion"."degree" = -2 AND "opinion"."fulfilled" = FALSE)
|
|
jbe@360
|
40 THEN 3 ELSE 4 END
|
|
jbe@360
|
41 END
|
|
jbe@360
|
42 END AS "preference",
|
|
jbe@360
|
43 "opinion"."suggestion_id"
|
|
jbe@360
|
44 FROM "opinion"
|
|
jbe@360
|
45 JOIN "initiative" ON "initiative"."id" = "opinion"."initiative_id"
|
|
jbe@360
|
46 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
|
|
jbe@360
|
47 JOIN "direct_interest_snapshot"
|
|
jbe@360
|
48 ON "direct_interest_snapshot"."issue_id" = "issue"."id"
|
|
jbe@360
|
49 AND "direct_interest_snapshot"."event" = "issue"."latest_snapshot_event"
|
|
jbe@360
|
50 AND "direct_interest_snapshot"."member_id" = "opinion"."member_id";
|
|
jbe@360
|
51 COMMENT ON VIEW "individual_suggestion_ranking" IS 'Helper view for "lf_update_suggestion_order" to allow a proportional ordering of suggestions within an initiative';
|
|
jbe@360
|
52
|
|
jbe@360
|
53 COMMIT;
|