liquid_feedback_core
diff update/core-update.v3.1.0-v3.2.0.sql @ 497:91e3d31c1de2
Added view "newsletter_to_send"
author | jbe |
---|---|
date | Sun Apr 03 20:54:45 2016 +0200 (2016-04-03) |
parents | 044af1eec28b |
children | 10b90162e982 |
line diff
1.1 --- a/update/core-update.v3.1.0-v3.2.0.sql Sun Apr 03 20:46:10 2016 +0200 1.2 +++ b/update/core-update.v3.1.0-v3.2.0.sql Sun Apr 03 20:54:45 2016 +0200 1.3 @@ -273,6 +273,29 @@ 1.4 AND ( "initiative2"."new_draft" OR "initiative2"."new_suggestion_count" > 0 ) 1.5 ); 1.6 1.7 +CREATE VIEW "newsletter_to_send" AS 1.8 + SELECT 1.9 + "newsletter"."id" AS "newsletter_id", 1.10 + "member"."id" AS "member_id" 1.11 + FROM "newsletter" CROSS JOIN "member" 1.12 + LEFT JOIN "privilege" ON 1.13 + "privilege"."member_id" = "member"."id" AND 1.14 + "privilege"."unit_id" = "newsletter"."unit_id" AND 1.15 + "privilege"."voting_right" = TRUE 1.16 + LEFT JOIN "subscription" ON 1.17 + "subscription"."member_id" = "member"."id" AND 1.18 + "subscription"."unit_id" = "newsletter"."unit_id" 1.19 + WHERE "newsletter"."published" >= now() 1.20 + AND "newsletter"."sent" ISNULL 1.21 + AND "member"."locked" = FALSE 1.22 + AND ( 1.23 + "member"."disable_notifications" = FALSE OR 1.24 + "newsletter"."include_all_members" = TRUE ) 1.25 + AND ( 1.26 + "newsletter"."unit_id" ISNULL OR 1.27 + "privilege"."member_id" NOTNULL OR 1.28 + "subscription"."member_id" NOTNULL ); 1.29 + 1.30 CREATE FUNCTION "get_initiatives_for_notification" 1.31 ( "member_id_p" "member"."id"%TYPE ) 1.32 RETURNS SETOF "initiative_for_notification"