1.1 --- a/core.sql	Wed May 04 22:46:39 2016 +0200
     1.2 +++ b/core.sql	Thu May 05 21:13:09 2016 +0200
     1.3 @@ -1959,6 +1959,20 @@
     1.4  ------------------------------------------
     1.5  
     1.6  
     1.7 +CREATE VIEW "member_eligible_to_be_notified" AS
     1.8 +  SELECT * FROM "member"
     1.9 +  WHERE "activated" NOTNULL AND "locked" = FALSE;
    1.10 +
    1.11 +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")';
    1.12 +
    1.13 +
    1.14 +CREATE VIEW "member_to_notify" AS
    1.15 +  SELECT * FROM "member_eligible_to_be_notified"
    1.16 +  WHERE "disable_notifications" = FALSE;
    1.17 +
    1.18 +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)';
    1.19 +
    1.20 +
    1.21  CREATE VIEW "unit_delegation" AS
    1.22    SELECT
    1.23      "unit"."id" AS "unit_id",
    1.24 @@ -2709,10 +2723,8 @@
    1.25          COALESCE("notification_sent", "activated") AS "notification_sent",
    1.26          "notification_dow",
    1.27          "notification_hour"
    1.28 -      FROM "member"
    1.29 -      WHERE "locked" = FALSE
    1.30 -      AND "disable_notifications" = FALSE
    1.31 -      AND "notification_hour" NOTNULL
    1.32 +      FROM "member_to_notify"
    1.33 +      WHERE "notification_hour" NOTNULL
    1.34      ) AS "subquery1"
    1.35    ) AS "subquery2"
    1.36    WHERE "pending" > '0'::INTERVAL;
    1.37 @@ -2728,7 +2740,7 @@
    1.38      "member"."id" AS "recipient_id",
    1.39      "newsletter"."id" AS "newsletter_id",
    1.40      "newsletter"."published"
    1.41 -  FROM "newsletter" CROSS JOIN "member"
    1.42 +  FROM "newsletter" CROSS JOIN "member_eligible_to_be_notified" AS "member"
    1.43    LEFT JOIN "privilege" ON
    1.44      "privilege"."member_id" = "member"."id" AND
    1.45      "privilege"."unit_id" = "newsletter"."unit_id" AND
    1.46 @@ -2738,7 +2750,6 @@
    1.47      "subscription"."unit_id" = "newsletter"."unit_id"
    1.48    WHERE "newsletter"."published" <= now()
    1.49    AND "newsletter"."sent" ISNULL
    1.50 -  AND "member"."locked" = FALSE
    1.51    AND (
    1.52      "member"."disable_notifications" = FALSE OR
    1.53      "newsletter"."include_all_members" = TRUE )