# HG changeset patch
# User jbe
# Date 1462475589 -7200
# Node ID bc6d9dc60ca4530e25ad401618fd84c109fbf982
# Parent  30b67ca0c242204a2b8ea81aa2b765625482e7d4
Require members to be "activated" in order to receive messages/notifications; New views "member_eligible_to_be_notified" and "member_to_notify"

diff -r 30b67ca0c242 -r bc6d9dc60ca4 core.sql
--- a/core.sql	Wed May 04 22:46:39 2016 +0200
+++ b/core.sql	Thu May 05 21:13:09 2016 +0200
@@ -1959,6 +1959,20 @@
 ------------------------------------------
 
 
+CREATE VIEW "member_eligible_to_be_notified" AS
+  SELECT * FROM "member"
+  WHERE "activated" NOTNULL AND "locked" = FALSE;
+
+COMMENT ON VIEW "member_eligible_to_be_notified" IS 'Filtered "member" table containing only activated and non-locked members (used as helper view for "member_to_notify" and "newsletter_to_send")';
+
+
+CREATE VIEW "member_to_notify" AS
+  SELECT * FROM "member_eligible_to_be_notified"
+  WHERE "disable_notifications" = FALSE;
+
+COMMENT ON VIEW "member_to_notify" IS 'Filtered "member" table containing only members that are eligible to and wish to receive notifications; NOTE: "notify_email" may still be NULL and might need to be checked by frontend (this allows other means of messaging)';
+
+
 CREATE VIEW "unit_delegation" AS
   SELECT
     "unit"."id" AS "unit_id",
@@ -2709,10 +2723,8 @@
         COALESCE("notification_sent", "activated") AS "notification_sent",
         "notification_dow",
         "notification_hour"
-      FROM "member"
-      WHERE "locked" = FALSE
-      AND "disable_notifications" = FALSE
-      AND "notification_hour" NOTNULL
+      FROM "member_to_notify"
+      WHERE "notification_hour" NOTNULL
     ) AS "subquery1"
   ) AS "subquery2"
   WHERE "pending" > '0'::INTERVAL;
@@ -2728,7 +2740,7 @@
     "member"."id" AS "recipient_id",
     "newsletter"."id" AS "newsletter_id",
     "newsletter"."published"
-  FROM "newsletter" CROSS JOIN "member"
+  FROM "newsletter" CROSS JOIN "member_eligible_to_be_notified" AS "member"
   LEFT JOIN "privilege" ON
     "privilege"."member_id" = "member"."id" AND
     "privilege"."unit_id" = "newsletter"."unit_id" AND
@@ -2738,7 +2750,6 @@
     "subscription"."unit_id" = "newsletter"."unit_id"
   WHERE "newsletter"."published" <= now()
   AND "newsletter"."sent" ISNULL
-  AND "member"."locked" = FALSE
   AND (
     "member"."disable_notifications" = FALSE OR
     "newsletter"."include_all_members" = TRUE )
diff -r 30b67ca0c242 -r bc6d9dc60ca4 update/core-update.v3.2.1-v3.2.2.sql
--- a/update/core-update.v3.2.1-v3.2.2.sql	Wed May 04 22:46:39 2016 +0200
+++ b/update/core-update.v3.2.1-v3.2.2.sql	Thu May 05 21:13:09 2016 +0200
@@ -16,6 +16,18 @@
   AND "locked" = TRUE
   AND "active" = FALSE;
 
+CREATE VIEW "member_eligible_to_be_notified" AS
+  SELECT * FROM "member"
+  WHERE "activated" NOTNULL AND "locked" = FALSE;
+
+COMMENT ON VIEW "member_eligible_to_be_notified" IS 'Filtered "member" table containing only activated and non-locked members (used as helper view for "member_to_notify" and "newsletter_to_send")';
+
+CREATE VIEW "member_to_notify" AS
+  SELECT * FROM "member_eligible_to_be_notified"
+  WHERE "disable_notifications" = FALSE;
+
+COMMENT ON VIEW "member_to_notify" IS 'Filtered "member" table containing only members that are eligible to and wish to receive notifications; NOTE: "notify_email" may still be NULL and might need to be checked by frontend (this allows other means of messaging)';
+
 CREATE OR REPLACE FUNCTION "featured_initiative"
   ( "recipient_id_p" "member"."id"%TYPE,
     "area_id_p"      "area"."id"%TYPE )
@@ -141,14 +153,35 @@
         COALESCE("notification_sent", "activated") AS "notification_sent",
         "notification_dow",
         "notification_hour"
-      FROM "member"
-      WHERE "locked" = FALSE
-      AND "disable_notifications" = FALSE
-      AND "notification_hour" NOTNULL
+      FROM "member_to_notify"
+      WHERE "notification_hour" NOTNULL
     ) AS "subquery1"
   ) AS "subquery2"
   WHERE "pending" > '0'::INTERVAL;
 
+CREATE OR REPLACE VIEW "newsletter_to_send" AS
+  SELECT
+    "member"."id" AS "recipient_id",
+    "newsletter"."id" AS "newsletter_id",
+    "newsletter"."published"
+  FROM "newsletter" CROSS JOIN "member_eligible_to_be_notified" AS "member"
+  LEFT JOIN "privilege" ON
+    "privilege"."member_id" = "member"."id" AND
+    "privilege"."unit_id" = "newsletter"."unit_id" AND
+    "privilege"."voting_right" = TRUE
+  LEFT JOIN "subscription" ON
+    "subscription"."member_id" = "member"."id" AND
+    "subscription"."unit_id" = "newsletter"."unit_id"
+  WHERE "newsletter"."published" <= now()
+  AND "newsletter"."sent" ISNULL
+  AND (
+    "member"."disable_notifications" = FALSE OR
+    "newsletter"."include_all_members" = TRUE )
+  AND (
+    "newsletter"."unit_id" ISNULL OR
+    "privilege"."member_id" NOTNULL OR
+    "subscription"."member_id" NOTNULL );
+
 CREATE OR REPLACE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)
   RETURNS VOID
   LANGUAGE 'plpgsql' VOLATILE AS $$