# HG changeset patch
# User jbe
# Date 1402439652 -7200
# Node ID 603f6e4bbedf3a8179acdcc685c262f1fcf0441d
# Parent  5676b0d3383318be6c37ee300fcdfaa6e753addf
Bugfix in "close_voting" function that only affected PostgreSQL versions prior 9.1

diff -r 5676b0d33833 -r 603f6e4bbedf core.sql
--- a/core.sql	Thu May 22 12:40:30 2014 +0200
+++ b/core.sql	Wed Jun 11 00:34:12 2014 +0200
@@ -3777,7 +3777,7 @@
           JOIN "vote" AS "agg"
           ON "initiative"."id" = "agg"."initiative_id"
           AND "vote"."member_id" = "agg"."member_id"
-          GROUP BY "vote"."initiative_id", "vote"."member_id"
+          GROUP BY "vote"."initiative_id", "vote"."member_id", "vote"."grade"
         ) AS "subquery"
         WHERE "vote"."issue_id" = "issue_id_p"
         AND "vote"."initiative_id" = "subquery"."initiative_id"
diff -r 5676b0d33833 -r 603f6e4bbedf update/core-update.v3.0.1-v3.0.2.sql
--- a/update/core-update.v3.0.1-v3.0.2.sql	Thu May 22 12:40:30 2014 +0200
+++ b/update/core-update.v3.0.1-v3.0.2.sql	Wed Jun 11 00:34:12 2014 +0200
@@ -28,6 +28,121 @@
 COMMENT ON COLUMN "initiative"."reverse_beat_path"      IS 'TRUE, if there is a beat path (may include ties) from this initiative to the status quo; set to NULL if "policy"."defeat_strength" is set to ''simple''';
  
 
+CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
+  RETURNS VOID
+  LANGUAGE 'plpgsql' VOLATILE AS $$
+    DECLARE
+      "area_id_v"   "area"."id"%TYPE;
+      "unit_id_v"   "unit"."id"%TYPE;
+      "member_id_v" "member"."id"%TYPE;
+    BEGIN
+      PERFORM "require_transaction_isolation"();
+      SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";
+      SELECT "unit_id" INTO "unit_id_v" FROM "area"  WHERE "id" = "area_id_v";
+      -- override protection triggers:
+      INSERT INTO "temporary_transaction_data" ("key", "value")
+        VALUES ('override_protection_triggers', TRUE::TEXT);
+      -- delete timestamp of voting comment:
+      UPDATE "direct_voter" SET "comment_changed" = NULL
+        WHERE "issue_id" = "issue_id_p";
+      -- delete delegating votes (in cases of manual reset of issue state):
+      DELETE FROM "delegating_voter"
+        WHERE "issue_id" = "issue_id_p";
+      -- delete votes from non-privileged voters:
+      DELETE FROM "direct_voter"
+        USING (
+          SELECT
+            "direct_voter"."member_id"
+          FROM "direct_voter"
+          JOIN "member" ON "direct_voter"."member_id" = "member"."id"
+          LEFT JOIN "privilege"
+          ON "privilege"."unit_id" = "unit_id_v"
+          AND "privilege"."member_id" = "direct_voter"."member_id"
+          WHERE "direct_voter"."issue_id" = "issue_id_p" AND (
+            "member"."active" = FALSE OR
+            "privilege"."voting_right" ISNULL OR
+            "privilege"."voting_right" = FALSE
+          )
+        ) AS "subquery"
+        WHERE "direct_voter"."issue_id" = "issue_id_p"
+        AND "direct_voter"."member_id" = "subquery"."member_id";
+      -- consider delegations:
+      UPDATE "direct_voter" SET "weight" = 1
+        WHERE "issue_id" = "issue_id_p";
+      PERFORM "add_vote_delegations"("issue_id_p");
+      -- mark first preferences:
+      UPDATE "vote" SET "first_preference" = "subquery"."first_preference"
+        FROM (
+          SELECT
+            "vote"."initiative_id",
+            "vote"."member_id",
+            CASE WHEN "vote"."grade" > 0 THEN
+              CASE WHEN "vote"."grade" = max("agg"."grade") THEN TRUE ELSE FALSE END
+            ELSE NULL
+            END AS "first_preference"
+          FROM "vote"
+          JOIN "initiative"  -- NOTE: due to missing index on issue_id
+          ON "vote"."issue_id" = "initiative"."issue_id"
+          JOIN "vote" AS "agg"
+          ON "initiative"."id" = "agg"."initiative_id"
+          AND "vote"."member_id" = "agg"."member_id"
+          GROUP BY "vote"."initiative_id", "vote"."member_id", "vote"."grade"
+        ) AS "subquery"
+        WHERE "vote"."issue_id" = "issue_id_p"
+        AND "vote"."initiative_id" = "subquery"."initiative_id"
+        AND "vote"."member_id" = "subquery"."member_id";
+      -- finish overriding protection triggers (avoids garbage):
+      DELETE FROM "temporary_transaction_data"
+        WHERE "key" = 'override_protection_triggers';
+      -- materialize battle_view:
+      -- NOTE: "closed" column of issue must be set at this point
+      DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
+      INSERT INTO "battle" (
+        "issue_id",
+        "winning_initiative_id", "losing_initiative_id",
+        "count"
+      ) SELECT
+        "issue_id",
+        "winning_initiative_id", "losing_initiative_id",
+        "count"
+        FROM "battle_view" WHERE "issue_id" = "issue_id_p";
+      -- set voter count:
+      UPDATE "issue" SET
+        "voter_count" = (
+          SELECT coalesce(sum("weight"), 0)
+          FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
+        )
+        WHERE "id" = "issue_id_p";
+      -- calculate "first_preference_votes":
+      UPDATE "initiative"
+        SET "first_preference_votes" = coalesce("subquery"."sum", 0)
+        FROM (
+          SELECT "vote"."initiative_id", sum("direct_voter"."weight")
+          FROM "vote" JOIN "direct_voter"
+          ON "vote"."issue_id" = "direct_voter"."issue_id"
+          AND "vote"."member_id" = "direct_voter"."member_id"
+          WHERE "vote"."first_preference"
+          GROUP BY "vote"."initiative_id"
+        ) AS "subquery"
+        WHERE "initiative"."issue_id" = "issue_id_p"
+        AND "initiative"."admitted"
+        AND "initiative"."id" = "subquery"."initiative_id";
+      -- copy "positive_votes" and "negative_votes" from "battle" table:
+      UPDATE "initiative" SET
+        "positive_votes" = "battle_win"."count",
+        "negative_votes" = "battle_lose"."count"
+        FROM "battle" AS "battle_win", "battle" AS "battle_lose"
+        WHERE
+          "battle_win"."issue_id" = "issue_id_p" AND
+          "battle_win"."winning_initiative_id" = "initiative"."id" AND
+          "battle_win"."losing_initiative_id" ISNULL AND
+          "battle_lose"."issue_id" = "issue_id_p" AND
+          "battle_lose"."losing_initiative_id" = "initiative"."id" AND
+          "battle_lose"."winning_initiative_id" ISNULL;
+    END;
+  $$;
+
+
 DROP FUNCTION "calculate_ranks"("issue"."id"%TYPE);
 DROP FUNCTION "defeat_strength"(INT4, INT4);