liquid_feedback_core
changeset 468:c39ff9540f4d
Work on "issue_for_notification" table
author | jbe |
---|---|
date | Sat Mar 26 22:24:23 2016 +0100 (2016-03-26) |
parents | 1e7e8b025346 |
children | c1e283fd6483 |
files | core.sql |
line diff
1.1 --- a/core.sql Sat Mar 26 02:13:42 2016 +0100 1.2 +++ b/core.sql Sat Mar 26 22:24:23 2016 +0100 1.3 @@ -2393,7 +2393,7 @@ 1.4 COMMENT ON VIEW "event_seen_by_member" IS 'Events as seen by a member, depending on its memberships, interests and support, but ignoring members "notify_level"'; 1.5 1.6 1.7 -CREATE VIEW "issues_for_notification" AS 1.8 +CREATE VIEW "issue_for_notification" AS 1.9 SELECT 1.10 "member"."id" AS "member_id", 1.11 "issue"."id" AS "issue_id" 1.12 @@ -2405,11 +2405,21 @@ 1.13 LEFT JOIN "subscription" 1.14 ON "subscription"."member_id" = "member"."id" 1.15 AND "subscription"."unit_id" = "area"."unit_id" 1.16 + LEFT JOIN "interest" 1.17 + ON "interest"."member_id" = "member"."id" 1.18 + AND "interest"."issue_id" = "issue"."id" 1.19 + LEFT JOIN "ignored_area" 1.20 + ON "ignored_area"."member_id" = "member"."id" 1.21 + AND "ignored_area"."area_id" = "issue"."area_id" 1.22 WHERE 1.23 ( "privilege"."initiative_right" OR "privilege"."voting_right" OR 1.24 - "subscription"."member_id" NOTNULL ); -- TODO: add further conditions 1.25 - 1.26 -COMMENT ON VIEW "issues_for_notification" IS 'Issues that are considered in notifications sent to the member'; 1.27 + "subscription"."member_id" NOTNULL ) AND 1.28 + ( ( "issue"."state" IN ('admission', 'discussion', 'verification') AND 1.29 + "interest"."member_id" NOTNULL ) OR 1.30 + ( "issue"."state" IN ('discussion', 'verification') AND 1.31 + "ignored_area"."member_id" ISNULL ) ); -- TODO: add certain issues in admission phase 1.32 + 1.33 +COMMENT ON VIEW "issue_for_notification" IS 'Issues that are considered in notifications sent to the member'; 1.34 1.35 1.36