# HG changeset patch
# User jbe
# Date 1462391249 -7200
# Node ID 65bd17395c28702dae3e2108bcba8dd201cb1b7c
# Parent  053190248598f464a11b60093a52227ee7540903
Exclude locked members as recipients in view "scheduled_notification_to_send"

diff -r 053190248598 -r 65bd17395c28 core.sql
--- a/core.sql	Wed May 04 21:44:18 2016 +0200
+++ b/core.sql	Wed May 04 21:47:29 2016 +0200
@@ -2710,7 +2710,8 @@
         "notification_dow",
         "notification_hour"
       FROM "member"
-      WHERE "disable_notifications" = FALSE
+      WHERE "locked" = FALSE
+      AND "disable_notifications" = FALSE
       AND "notification_hour" NOTNULL
     ) AS "subquery1"
   ) AS "subquery2"
diff -r 053190248598 -r 65bd17395c28 update/core-update.v3.2.1-v3.2.2.sql
--- a/update/core-update.v3.2.1-v3.2.2.sql	Wed May 04 21:44:18 2016 +0200
+++ b/update/core-update.v3.2.1-v3.2.2.sql	Wed May 04 21:47:29 2016 +0200
@@ -98,4 +98,43 @@
     END;
   $$;
 
+CREATE OR REPLACE VIEW "scheduled_notification_to_send" AS
+  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 "locked" = FALSE
+      AND "disable_notifications" = FALSE
+      AND "notification_hour" NOTNULL
+    ) AS "subquery1"
+  ) AS "subquery2"
+  WHERE "pending" > '0'::INTERVAL;
+
 COMMIT;