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 $$