liquid_feedback_core
changeset 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 |
files | core.sql update/core-update.v3.1.0-v3.2.0.sql |
line diff
1.1 --- a/core.sql Sun Apr 03 20:46:10 2016 +0200 1.2 +++ b/core.sql Sun Apr 03 20:54:45 2016 +0200 1.3 @@ -2621,6 +2621,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 1.31 1.32 ------------------------------------------------------
2.1 --- a/update/core-update.v3.1.0-v3.2.0.sql Sun Apr 03 20:46:10 2016 +0200 2.2 +++ b/update/core-update.v3.1.0-v3.2.0.sql Sun Apr 03 20:54:45 2016 +0200 2.3 @@ -273,6 +273,29 @@ 2.4 AND ( "initiative2"."new_draft" OR "initiative2"."new_suggestion_count" > 0 ) 2.5 ); 2.6 2.7 +CREATE VIEW "newsletter_to_send" AS 2.8 + SELECT 2.9 + "newsletter"."id" AS "newsletter_id", 2.10 + "member"."id" AS "member_id" 2.11 + FROM "newsletter" CROSS JOIN "member" 2.12 + LEFT JOIN "privilege" ON 2.13 + "privilege"."member_id" = "member"."id" AND 2.14 + "privilege"."unit_id" = "newsletter"."unit_id" AND 2.15 + "privilege"."voting_right" = TRUE 2.16 + LEFT JOIN "subscription" ON 2.17 + "subscription"."member_id" = "member"."id" AND 2.18 + "subscription"."unit_id" = "newsletter"."unit_id" 2.19 + WHERE "newsletter"."published" >= now() 2.20 + AND "newsletter"."sent" ISNULL 2.21 + AND "member"."locked" = FALSE 2.22 + AND ( 2.23 + "member"."disable_notifications" = FALSE OR 2.24 + "newsletter"."include_all_members" = TRUE ) 2.25 + AND ( 2.26 + "newsletter"."unit_id" ISNULL OR 2.27 + "privilege"."member_id" NOTNULL OR 2.28 + "subscription"."member_id" NOTNULL ); 2.29 + 2.30 CREATE FUNCTION "get_initiatives_for_notification" 2.31 ( "member_id_p" "member"."id"%TYPE ) 2.32 RETURNS SETOF "initiative_for_notification"