liquid_feedback_core
changeset 521:65bd17395c28
Exclude locked members as recipients in view "scheduled_notification_to_send"
author | jbe |
---|---|
date | Wed May 04 21:47:29 2016 +0200 (2016-05-04) |
parents | 053190248598 |
children | 2f1c06608def |
files | core.sql update/core-update.v3.2.1-v3.2.2.sql |
line diff
1.1 --- a/core.sql Wed May 04 21:44:18 2016 +0200 1.2 +++ b/core.sql Wed May 04 21:47:29 2016 +0200 1.3 @@ -2710,7 +2710,8 @@ 1.4 "notification_dow", 1.5 "notification_hour" 1.6 FROM "member" 1.7 - WHERE "disable_notifications" = FALSE 1.8 + WHERE "locked" = FALSE 1.9 + AND "disable_notifications" = FALSE 1.10 AND "notification_hour" NOTNULL 1.11 ) AS "subquery1" 1.12 ) AS "subquery2"
2.1 --- a/update/core-update.v3.2.1-v3.2.2.sql Wed May 04 21:44:18 2016 +0200 2.2 +++ b/update/core-update.v3.2.1-v3.2.2.sql Wed May 04 21:47:29 2016 +0200 2.3 @@ -98,4 +98,43 @@ 2.4 END; 2.5 $$; 2.6 2.7 +CREATE OR REPLACE VIEW "scheduled_notification_to_send" AS 2.8 + SELECT * FROM ( 2.9 + SELECT 2.10 + "id" AS "recipient_id", 2.11 + now() - CASE WHEN "notification_dow" ISNULL THEN 2.12 + ( "notification_sent"::DATE + CASE 2.13 + WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour" 2.14 + THEN 0 ELSE 1 END 2.15 + )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour" 2.16 + ELSE 2.17 + ( "notification_sent"::DATE + 2.18 + ( 7 + "notification_dow" - 2.19 + EXTRACT(DOW FROM 2.20 + ( "notification_sent"::DATE + CASE 2.21 + WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour" 2.22 + THEN 0 ELSE 1 END 2.23 + )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour" 2.24 + )::INTEGER 2.25 + ) % 7 + 2.26 + CASE 2.27 + WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour" 2.28 + THEN 0 ELSE 1 2.29 + END 2.30 + )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour" 2.31 + END AS "pending" 2.32 + FROM ( 2.33 + SELECT 2.34 + "id", 2.35 + COALESCE("notification_sent", "activated") AS "notification_sent", 2.36 + "notification_dow", 2.37 + "notification_hour" 2.38 + FROM "member" 2.39 + WHERE "locked" = FALSE 2.40 + AND "disable_notifications" = FALSE 2.41 + AND "notification_hour" NOTNULL 2.42 + ) AS "subquery1" 2.43 + ) AS "subquery2" 2.44 + WHERE "pending" > '0'::INTERVAL; 2.45 + 2.46 COMMIT;