# HG changeset patch
# User jbe
# Date 1459879944 -7200
# Node ID be7942edfd1ee2afb59dcf65c4218efd699f7b6c
# Parent  d07e6a046d41a6bded265c6470a40ef498352d25
New algorithm for view "scheduled_notification_to_send"; Update "notification_sent" column in "get_initiatives_for_notification" function

diff -r d07e6a046d41 -r be7942edfd1e core.sql
--- a/core.sql	Tue Apr 05 00:55:31 2016 +0200
+++ b/core.sql	Tue Apr 05 20:12:24 2016 +0200
@@ -149,6 +149,8 @@
           UNIQUE ("authority", "authority_uid"),
         CONSTRAINT "authority_login_requires_authority"
           CHECK ("authority" NOTNULL OR "authority_login" ISNULL),
+        CONSTRAINT "notification_dow_requires_notification_hour"
+          CHECK ("notification_dow" ISNULL OR "notification_hour" NOTNULL),
         CONSTRAINT "name_not_null_if_activated"
           CHECK ("activated" ISNULL OR "name" NOTNULL) );
 CREATE INDEX "member_authority_login_idx" ON "member" ("authority_login");
@@ -2612,15 +2614,42 @@
   );
 
 CREATE VIEW "scheduled_notification_to_send" AS
-  SELECT "id" AS "recipient_id"
-  FROM "member"
-  WHERE "member"."disable_notifications" = FALSE
-  AND COALESCE("notification_dow" = EXTRACT(DOW FROM now()), TRUE)
-  AND "notification_hour" = EXTRACT(HOUR FROM now())
-  AND NOT (
-    "notification_sent" NOTNULL AND
-    "notification_sent"::DATE = now()::DATE AND
-    EXTRACT(HOUR FROM "notification_sent") = EXTRACT(HOUR FROM now()) );
+  SELECT * FROM (
+    SELECT
+      "id" AS "recipient_id",
+      now() - CASE WHEN "notification_dow" ISNULL THEN
+        ( "notification_sent"::DATE + CASE
+          WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour"
+          THEN 0 ELSE 1 END
+        )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour"
+      ELSE
+        ( "notification_sent"::DATE +
+          ( 7 + "notification_dow" -
+            EXTRACT(DOW FROM
+              ( "notification_sent"::DATE + CASE
+                WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour"
+                THEN 0 ELSE 1 END
+              )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour"
+            )::INTEGER
+          ) % 7 +
+          CASE
+            WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour"
+            THEN 0 ELSE 1
+          END
+        )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour"
+      END AS "pending"
+    FROM (
+      SELECT
+        "id",
+        COALESCE("notification_sent", "activated") AS "notification_sent",
+        "notification_dow",
+        "notification_hour"
+      FROM "member"
+      WHERE "disable_notifications" = FALSE
+      AND "notification_hour" NOTNULL
+    ) AS "subquery1"
+  ) AS "subquery2"
+  WHERE "pending" > '0'::INTERVAL;
 
 CREATE VIEW "newsletter_to_send" AS
   SELECT
@@ -3181,7 +3210,9 @@
         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
+      UPDATE "member" SET
+        "notification_counter" = "notification_counter" + 1 AND
+        "notification_sent" = now()
         WHERE "id" = "recipient_id_p";
       RETURN;
     END;
diff -r d07e6a046d41 -r be7942edfd1e update/core-update.v3.1.0-v3.2.0.sql
--- a/update/core-update.v3.1.0-v3.2.0.sql	Tue Apr 05 00:55:31 2016 +0200
+++ b/update/core-update.v3.1.0-v3.2.0.sql	Tue Apr 05 20:12:24 2016 +0200
@@ -331,15 +331,42 @@
   );
 
 CREATE VIEW "scheduled_notification_to_send" AS
-  SELECT "id" AS "recipient_id"
-  FROM "member"
-  WHERE "member"."disable_notifications" = FALSE
-  AND COALESCE("notification_dow" = EXTRACT(DOW FROM now()), TRUE)
-  AND "notification_hour" = EXTRACT(HOUR FROM now())
-  AND NOT (
-    "notification_sent" NOTNULL AND
-    "notification_sent"::DATE = now()::DATE AND
-    EXTRACT(HOUR FROM "notification_sent") = EXTRACT(HOUR FROM now()) );
+  SELECT * FROM (
+    SELECT
+      "id" AS "recipient_id",
+      now() - CASE WHEN "notification_dow" ISNULL THEN
+        ( "notification_sent"::DATE + CASE
+          WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour"
+          THEN 0 ELSE 1 END
+        )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour"
+      ELSE
+        ( "notification_sent"::DATE +
+          ( 7 + "notification_dow" -
+            EXTRACT(DOW FROM
+              ( "notification_sent"::DATE + CASE
+                WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour"
+                THEN 0 ELSE 1 END
+              )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour"
+            )::INTEGER
+          ) % 7 +
+          CASE
+            WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour"
+            THEN 0 ELSE 1
+          END
+        )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour"
+      END AS "pending"
+    FROM (
+      SELECT
+        "id",
+        COALESCE("notification_sent", "activated") AS "notification_sent",
+        "notification_dow",
+        "notification_hour"
+      FROM "member"
+      WHERE "disable_notifications" = FALSE
+      AND "notification_hour" NOTNULL
+    ) AS "subquery1"
+  ) AS "subquery2"
+  WHERE "pending" > '0'::INTERVAL;
 
 CREATE VIEW "newsletter_to_send" AS
   SELECT
@@ -411,7 +438,9 @@
         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
+      UPDATE "member" SET
+        "notification_counter" = "notification_counter" + 1 AND
+        "notification_sent" = now()
         WHERE "id" = "recipient_id_p";
       RETURN;
     END;