# HG changeset patch
# User jbe
# Date 1459709685 -7200
# Node ID 91e3d31c1de21ce1bd2d4dfe5704252e060bf84b
# Parent  044af1eec28b66eecaf08fa4c4ce7fd95de81069
Added view "newsletter_to_send"

diff -r 044af1eec28b -r 91e3d31c1de2 core.sql
--- a/core.sql	Sun Apr 03 20:46:10 2016 +0200
+++ b/core.sql	Sun Apr 03 20:54:45 2016 +0200
@@ -2621,6 +2621,29 @@
     AND ( "initiative2"."new_draft" OR "initiative2"."new_suggestion_count" > 0 )
   );
 
+CREATE VIEW "newsletter_to_send" AS
+  SELECT
+    "newsletter"."id" AS "newsletter_id",
+    "member"."id" AS "member_id"
+  FROM "newsletter" CROSS JOIN "member"
+  LEFT JOIN "privilege" ON
+    "privilege"."member_id" = "member"."id" AND
+    "privilege"."unit_id" = "newsletter"."unit_id" AND
+    "privilege"."voting_right" = TRUE
+  LEFT JOIN "subscription" ON
+    "subscription"."member_id" = "member"."id" AND
+    "subscription"."unit_id" = "newsletter"."unit_id"
+  WHERE "newsletter"."published" >= now()
+  AND "newsletter"."sent" ISNULL
+  AND "member"."locked" = FALSE
+  AND (
+    "member"."disable_notifications" = FALSE OR
+    "newsletter"."include_all_members" = TRUE )
+  AND (
+    "newsletter"."unit_id" ISNULL OR
+    "privilege"."member_id" NOTNULL OR
+    "subscription"."member_id" NOTNULL );
+
 
 
 ------------------------------------------------------
diff -r 044af1eec28b -r 91e3d31c1de2 update/core-update.v3.1.0-v3.2.0.sql
--- a/update/core-update.v3.1.0-v3.2.0.sql	Sun Apr 03 20:46:10 2016 +0200
+++ b/update/core-update.v3.1.0-v3.2.0.sql	Sun Apr 03 20:54:45 2016 +0200
@@ -273,6 +273,29 @@
     AND ( "initiative2"."new_draft" OR "initiative2"."new_suggestion_count" > 0 )
   );
 
+CREATE VIEW "newsletter_to_send" AS
+  SELECT
+    "newsletter"."id" AS "newsletter_id",
+    "member"."id" AS "member_id"
+  FROM "newsletter" CROSS JOIN "member"
+  LEFT JOIN "privilege" ON
+    "privilege"."member_id" = "member"."id" AND
+    "privilege"."unit_id" = "newsletter"."unit_id" AND
+    "privilege"."voting_right" = TRUE
+  LEFT JOIN "subscription" ON
+    "subscription"."member_id" = "member"."id" AND
+    "subscription"."unit_id" = "newsletter"."unit_id"
+  WHERE "newsletter"."published" >= now()
+  AND "newsletter"."sent" ISNULL
+  AND "member"."locked" = FALSE
+  AND (
+    "member"."disable_notifications" = FALSE OR
+    "newsletter"."include_all_members" = TRUE )
+  AND (
+    "newsletter"."unit_id" ISNULL OR
+    "privilege"."member_id" NOTNULL OR
+    "subscription"."member_id" NOTNULL );
+
 CREATE FUNCTION "get_initiatives_for_notification"
   ( "member_id_p" "member"."id"%TYPE )
   RETURNS SETOF "initiative_for_notification"