liquid_feedback_core
diff core.sql @ 524:bc6d9dc60ca4
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 | 2f1c06608def |
children | 2a2f76da1177 |
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 )