# HG changeset patch
# User jbe
# Date 1459705329 -7200
# Node ID aa94c7dbb20f8990f13bdba3dec7618beb65e181
# Parent  16536937933ea345812f3c71a8575cd27aa5ec33
New function "get_initiatives_for_notification"

diff -r 16536937933e -r aa94c7dbb20f core.sql
--- a/core.sql	Sun Apr 03 18:00:07 2016 +0200
+++ b/core.sql	Sun Apr 03 19:42:09 2016 +0200
@@ -3093,6 +3093,65 @@
 
 
 
+-------------------------
+-- Notification system --
+-------------------------
+
+CREATE FUNCTION "get_initiatives_for_notification"
+  ( "member_id_p" "member"."id"%TYPE )
+  RETURNS SETOF "initiative_for_notification"
+  LANGUAGE 'plpgsql' VOLATILE AS $$
+    DECLARE
+      "result_row"           "initiative_for_notification"%ROWTYPE;
+      "last_draft_id_v"      "draft"."id"%TYPE;
+      "last_suggestion_id_v" "suggestion"."id"%TYPE;
+    BEGIN
+      PERFORM "require_transaction_isolation"();
+      PERFORM NULL FROM "member" WHERE "id" = "member_id_p" FOR UPDATE;
+      FOR "result_row" IN
+        SELECT * FROM "initiative_for_notification"
+        WHERE "seen_by_member_id" = "member_id_p"
+      LOOP
+        SELECT "id" INTO "last_draft_id_v" FROM "draft"
+          WHERE "draft"."initiative_id" = "result_row"."id"
+          ORDER BY "id" DESC LIMIT 1;
+        SELECT "id" INTO "last_suggestion_id_v" FROM "suggestion"
+          WHERE "suggestion"."initiative_id" = "result_row"."id"
+          ORDER BY "id" DESC LIMIT 1;
+        INSERT INTO "initiative_notification_sent"
+          ("member_id", "initiative_id", "last_draft_id", "last_suggestion_id")
+          VALUES (
+            "member_id_p",
+            "result_row"."id",
+            "last_draft_id",
+            "last_suggestion_id" )
+          ON CONFLICT ("member_id", "initiative_id") DO UPDATE SET
+            "last_draft_id" = CASE
+              WHEN "last_draft_id" > "last_draft_id_v"
+              THEN "last_draft_id"
+              ELSE "last_draft_id_v"
+            END,
+            "last_suggestion_id" = CASE
+              WHEN "last_suggestion_id" > "last_suggestion_id_v"
+              THEN "last_suggestion_id"
+              ELSE "last_suggestion_id_v"
+            END;
+        RETURN NEXT "result_row";
+      END LOOP;
+      DELETE FROM "initiative_notification_sent"
+        USING "initiative", "issue"
+        WHERE "initiative_notification_sent"."member_id" = "member_id_p"
+        AND "initiative"."id" = "initiative_notification_sent"."initiative_id"
+        AND "issue"."id" = "initiative"."issue_id"
+        AND ( "issue"."closed" NOTNULL OR "issue"."fully_frozen" NOTNULL );
+      UPDATE "member" SET "notification_counter" = "notification_counter" + 1
+        WHERE "id" = "member_id_p";
+      RETURN;
+    END;
+  $$;
+
+
+
 ------------------------------------------------------------------------
 -- Regular tasks, except calculcation of snapshots and voting results --
 ------------------------------------------------------------------------