1.1 --- a/update/core-update.v3.2.1-v3.2.2.sql	Wed May 04 22:46:39 2016 +0200
     1.2 +++ b/update/core-update.v3.2.1-v3.2.2.sql	Thu May 05 21:13:09 2016 +0200
     1.3 @@ -16,6 +16,18 @@
     1.4    AND "locked" = TRUE
     1.5    AND "active" = FALSE;
     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 +CREATE VIEW "member_to_notify" AS
    1.14 +  SELECT * FROM "member_eligible_to_be_notified"
    1.15 +  WHERE "disable_notifications" = FALSE;
    1.16 +
    1.17 +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.18 +
    1.19  CREATE OR REPLACE FUNCTION "featured_initiative"
    1.20    ( "recipient_id_p" "member"."id"%TYPE,
    1.21      "area_id_p"      "area"."id"%TYPE )
    1.22 @@ -141,14 +153,35 @@
    1.23          COALESCE("notification_sent", "activated") AS "notification_sent",
    1.24          "notification_dow",
    1.25          "notification_hour"
    1.26 -      FROM "member"
    1.27 -      WHERE "locked" = FALSE
    1.28 -      AND "disable_notifications" = FALSE
    1.29 -      AND "notification_hour" NOTNULL
    1.30 +      FROM "member_to_notify"
    1.31 +      WHERE "notification_hour" NOTNULL
    1.32      ) AS "subquery1"
    1.33    ) AS "subquery2"
    1.34    WHERE "pending" > '0'::INTERVAL;
    1.35  
    1.36 +CREATE OR REPLACE VIEW "newsletter_to_send" AS
    1.37 +  SELECT
    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_eligible_to_be_notified" AS "member"
    1.42 +  LEFT JOIN "privilege" ON
    1.43 +    "privilege"."member_id" = "member"."id" AND
    1.44 +    "privilege"."unit_id" = "newsletter"."unit_id" AND
    1.45 +    "privilege"."voting_right" = TRUE
    1.46 +  LEFT JOIN "subscription" ON
    1.47 +    "subscription"."member_id" = "member"."id" AND
    1.48 +    "subscription"."unit_id" = "newsletter"."unit_id"
    1.49 +  WHERE "newsletter"."published" <= now()
    1.50 +  AND "newsletter"."sent" ISNULL
    1.51 +  AND (
    1.52 +    "member"."disable_notifications" = FALSE OR
    1.53 +    "newsletter"."include_all_members" = TRUE )
    1.54 +  AND (
    1.55 +    "newsletter"."unit_id" ISNULL OR
    1.56 +    "privilege"."member_id" NOTNULL OR
    1.57 +    "subscription"."member_id" NOTNULL );
    1.58 +
    1.59  CREATE OR REPLACE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)
    1.60    RETURNS VOID
    1.61    LANGUAGE 'plpgsql' VOLATILE AS $$