# HG changeset patch
# User jbe
# Date 1360344385 -3600
# Node ID 3a7f06a88ecb235de9e961336956e94d7dea838d
# Parent  685d38986598527daa2fad591efadfd5c40ece21
Calculate "harmonic_weight" to be used as a secondary sorting key, while the "admitted" flag shall be used as the primary sorting key for initiatives

diff -r 685d38986598 -r 3a7f06a88ecb core.sql
--- a/core.sql	Sat Feb 02 22:31:55 2013 +0100
+++ b/core.sql	Fri Feb 08 18:26:25 2013 +0100
@@ -668,7 +668,7 @@
 COMMENT ON COLUMN "initiative"."informed_supporter_count"           IS 'Calculated from table "direct_supporter_snapshot"';
 COMMENT ON COLUMN "initiative"."satisfied_supporter_count"          IS 'Calculated from table "direct_supporter_snapshot"';
 COMMENT ON COLUMN "initiative"."satisfied_informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
-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';
+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';
 COMMENT ON COLUMN "initiative"."positive_votes"         IS 'Calculated from table "direct_voter"';
 COMMENT ON COLUMN "initiative"."negative_votes"         IS 'Calculated from table "direct_voter"';
 COMMENT ON COLUMN "initiative"."direct_majority"        IS 'TRUE, if "positive_votes"/("positive_votes"+"negative_votes") is strictly greater or greater-equal than "direct_majority_num"/"direct_majority_den", and "positive_votes" is greater-equal than "direct_majority_positive", and ("positive_votes"+abstentions) is greater-equal than "direct_majority_non_negative"';
@@ -3085,6 +3085,7 @@
   SELECT
     "initiative"."issue_id",
     "initiative"."id" AS "initiative_id",
+    "initiative"."admitted",
     sum("remaining_harmonic_supporter_weight"."weight_num") AS "weight_num",
     "remaining_harmonic_supporter_weight"."weight_den"
   FROM "remaining_harmonic_supporter_weight"
@@ -3098,6 +3099,7 @@
   GROUP BY
     "initiative"."issue_id",
     "initiative"."id",
+    "initiative"."admitted",
     "remaining_harmonic_supporter_weight"."weight_den";
 
 COMMENT ON VIEW "remaining_harmonic_initiative_weight_summands" IS 'Helper view for function "set_harmonic_initiative_weights"';
@@ -3125,8 +3127,17 @@
         FOR "weight_row" IN
           SELECT * FROM "remaining_harmonic_initiative_weight_summands"
           WHERE "issue_id" = "issue_id_p"
+          AND (
+            coalesce("admitted", FALSE) = FALSE OR NOT EXISTS (
+              SELECT NULL FROM "initiative"
+              WHERE "issue_id" = "issue_id_p"
+              AND "harmonic_weight" ISNULL
+              AND coalesce("admitted", FALSE) = FALSE
+            )
+          )
           ORDER BY "initiative_id" DESC, "weight_den" DESC
-          -- NOTE: latest initiatives treated worse
+          -- NOTE: non-admitted initiatives placed first (at last positions),
+          --       latest initiatives treated worse in case of tie
         LOOP
           "summand_v" := "weight_row"."weight_num"::FLOAT / "weight_row"."weight_den"::FLOAT;
           IF "i" = 0 OR "weight_row"."initiative_id" != "id_ary"["i"] THEN
@@ -3248,7 +3259,7 @@
           SELECT * FROM "remaining_harmonic_suggestion_weight_summands"
           WHERE "initiative_id" = "initiative_id_p"
           ORDER BY "suggestion_id" DESC, "weight_den" DESC
-          -- NOTE: latest suggestions treated worse
+          -- NOTE: latest suggestions treated worse in case of tie
         LOOP
           "summand_v" := "weight_row"."weight_num"::FLOAT / "weight_row"."weight_den"::FLOAT;
           IF "i" = 0 OR "weight_row"."suggestion_id" != "id_ary"["i"] THEN
diff -r 685d38986598 -r 3a7f06a88ecb update/core-update.v2.1.0-v2.1.1.sql
--- a/update/core-update.v2.1.0-v2.1.1.sql	Sat Feb 02 22:31:55 2013 +0100
+++ b/update/core-update.v2.1.0-v2.1.1.sql	Fri Feb 08 18:26:25 2013 +0100
@@ -5,7 +5,7 @@
   AS "subquery"("string", "major", "minor", "revision");
 
 ALTER TABLE "initiative" ADD COLUMN "harmonic_weight" NUMERIC(12, 3);
-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';
+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';
 
 ALTER TABLE "suggestion" ADD COLUMN "harmonic_weight" NUMERIC(12, 3);
 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';
@@ -40,6 +40,7 @@
   SELECT
     "initiative"."issue_id",
     "initiative"."id" AS "initiative_id",
+    "initiative"."admitted",
     sum("remaining_harmonic_supporter_weight"."weight_num") AS "weight_num",
     "remaining_harmonic_supporter_weight"."weight_den"
   FROM "remaining_harmonic_supporter_weight"
@@ -53,6 +54,7 @@
   GROUP BY
     "initiative"."issue_id",
     "initiative"."id",
+    "initiative"."admitted",
     "remaining_harmonic_supporter_weight"."weight_den";
 
 COMMENT ON VIEW "remaining_harmonic_initiative_weight_summands" IS 'Helper view for function "set_harmonic_initiative_weights"';
@@ -79,8 +81,17 @@
         FOR "weight_row" IN
           SELECT * FROM "remaining_harmonic_initiative_weight_summands"
           WHERE "issue_id" = "issue_id_p"
+          AND (
+            coalesce("admitted", FALSE) = FALSE OR NOT EXISTS (
+              SELECT NULL FROM "initiative"
+              WHERE "issue_id" = "issue_id_p"
+              AND "harmonic_weight" ISNULL
+              AND coalesce("admitted", FALSE) = FALSE
+            )
+          )
           ORDER BY "initiative_id" DESC, "weight_den" DESC
-          -- NOTE: latest initiatives treated worse
+          -- NOTE: non-admitted initiatives placed first (at last positions),
+          --       latest initiatives treated worse in case of tie
         LOOP
           "summand_v" := "weight_row"."weight_num"::FLOAT / "weight_row"."weight_den"::FLOAT;
           IF "i" = 0 OR "weight_row"."initiative_id" != "id_ary"["i"] THEN
@@ -199,7 +210,7 @@
           SELECT * FROM "remaining_harmonic_suggestion_weight_summands"
           WHERE "initiative_id" = "initiative_id_p"
           ORDER BY "suggestion_id" DESC, "weight_den" DESC
-          -- NOTE: latest suggestions treated worse
+          -- NOTE: latest suggestions treated worse in case of tie
         LOOP
           "summand_v" := "weight_row"."weight_num"::FLOAT / "weight_row"."weight_den"::FLOAT;
           IF "i" = 0 OR "weight_row"."suggestion_id" != "id_ary"["i"] THEN