# HG changeset patch
# User jbe
# Date 1459810531 -7200
# Node ID d07e6a046d41a6bded265c6470a40ef498352d25
# Parent  986d8f3a68952423dc5fddd8d2a45ffdd7009a0c
New column "notification_sent" in "member" table; New view "scheduled_notification_to_send"

diff -r 986d8f3a6895 -r d07e6a046d41 core.sql
--- a/core.sql	Tue Apr 05 00:19:33 2016 +0200
+++ b/core.sql	Tue Apr 05 00:55:31 2016 +0200
@@ -118,6 +118,7 @@
         "notification_sample_size" INT4         NOT NULL DEFAULT 3,
         "notification_dow"         INT4         CHECK ("notification_dow" BETWEEN 0 AND 6),
         "notification_hour"        INT4         CHECK ("notification_hour" BETWEEN 0 AND 23),
+        "notification_sent"        TIMESTAMP,
         "login_recovery_expiry"        TIMESTAMPTZ,
         "password_reset_secret"        TEXT     UNIQUE,
         "password_reset_secret_expiry" TIMESTAMPTZ,
@@ -2610,6 +2611,17 @@
     AND ("unfiltered2"."new_draft" OR "unfiltered2"."new_suggestion_count" > 0 )
   );
 
+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()) );
+
 CREATE VIEW "newsletter_to_send" AS
   SELECT
     "member"."id" AS "recipient_id",
diff -r 986d8f3a6895 -r d07e6a046d41 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:19:33 2016 +0200
+++ b/update/core-update.v3.1.0-v3.2.0.sql	Tue Apr 05 00:55:31 2016 +0200
@@ -11,6 +11,7 @@
 ALTER TABLE "member" ADD COLUMN "notification_sample_size" INT4 NOT NULL DEFAULT 3;
 ALTER TABLE "member" ADD COLUMN "notification_dow" INT4 CHECK ("notification_dow" BETWEEN 0 AND 6);
 ALTER TABLE "member" ADD COLUMN "notification_hour" INT4 CHECK ("notification_hour" BETWEEN 0 AND 23);
+ALTER TABLE "member" ADD COLUMN "notification_sent" TIMESTAMP;
 
 UPDATE "member" SET "disable_notifications" = TRUE WHERE "notify_level" = 'none';
 
@@ -329,6 +330,17 @@
     AND ("unfiltered2"."new_draft" OR "unfiltered2"."new_suggestion_count" > 0 )
   );
 
+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()) );
+
 CREATE VIEW "newsletter_to_send" AS
   SELECT
     "member"."id" AS "recipient_id",