liquid_feedback_core

changeset 524:bc6d9dc60ca4 v3.2.2

Require members to be "activated" in order to receive messages/notifications; New views "member_eligible_to_be_notified" and "member_to_notify"
author jbe
date Thu May 05 21:13:09 2016 +0200 (2016-05-05)
parents 30b67ca0c242
children b81a90434b8b
files core.sql update/core-update.v3.2.1-v3.2.2.sql
line diff
     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 )
     2.1 --- a/update/core-update.v3.2.1-v3.2.2.sql	Wed May 04 22:46:39 2016 +0200
     2.2 +++ b/update/core-update.v3.2.1-v3.2.2.sql	Thu May 05 21:13:09 2016 +0200
     2.3 @@ -16,6 +16,18 @@
     2.4    AND "locked" = TRUE
     2.5    AND "active" = FALSE;
     2.6  
     2.7 +CREATE VIEW "member_eligible_to_be_notified" AS
     2.8 +  SELECT * FROM "member"
     2.9 +  WHERE "activated" NOTNULL AND "locked" = FALSE;
    2.10 +
    2.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")';
    2.12 +
    2.13 +CREATE VIEW "member_to_notify" AS
    2.14 +  SELECT * FROM "member_eligible_to_be_notified"
    2.15 +  WHERE "disable_notifications" = FALSE;
    2.16 +
    2.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)';
    2.18 +
    2.19  CREATE OR REPLACE FUNCTION "featured_initiative"
    2.20    ( "recipient_id_p" "member"."id"%TYPE,
    2.21      "area_id_p"      "area"."id"%TYPE )
    2.22 @@ -141,14 +153,35 @@
    2.23          COALESCE("notification_sent", "activated") AS "notification_sent",
    2.24          "notification_dow",
    2.25          "notification_hour"
    2.26 -      FROM "member"
    2.27 -      WHERE "locked" = FALSE
    2.28 -      AND "disable_notifications" = FALSE
    2.29 -      AND "notification_hour" NOTNULL
    2.30 +      FROM "member_to_notify"
    2.31 +      WHERE "notification_hour" NOTNULL
    2.32      ) AS "subquery1"
    2.33    ) AS "subquery2"
    2.34    WHERE "pending" > '0'::INTERVAL;
    2.35  
    2.36 +CREATE OR REPLACE VIEW "newsletter_to_send" AS
    2.37 +  SELECT
    2.38 +    "member"."id" AS "recipient_id",
    2.39 +    "newsletter"."id" AS "newsletter_id",
    2.40 +    "newsletter"."published"
    2.41 +  FROM "newsletter" CROSS JOIN "member_eligible_to_be_notified" AS "member"
    2.42 +  LEFT JOIN "privilege" ON
    2.43 +    "privilege"."member_id" = "member"."id" AND
    2.44 +    "privilege"."unit_id" = "newsletter"."unit_id" AND
    2.45 +    "privilege"."voting_right" = TRUE
    2.46 +  LEFT JOIN "subscription" ON
    2.47 +    "subscription"."member_id" = "member"."id" AND
    2.48 +    "subscription"."unit_id" = "newsletter"."unit_id"
    2.49 +  WHERE "newsletter"."published" <= now()
    2.50 +  AND "newsletter"."sent" ISNULL
    2.51 +  AND (
    2.52 +    "member"."disable_notifications" = FALSE OR
    2.53 +    "newsletter"."include_all_members" = TRUE )
    2.54 +  AND (
    2.55 +    "newsletter"."unit_id" ISNULL OR
    2.56 +    "privilege"."member_id" NOTNULL OR
    2.57 +    "subscription"."member_id" NOTNULL );
    2.58 +
    2.59  CREATE OR REPLACE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)
    2.60    RETURNS VOID
    2.61    LANGUAGE 'plpgsql' VOLATILE AS $$

Impressum / About Us