liquid_feedback_core
diff core.sql @ 505:be7942edfd1e
New algorithm for view "scheduled_notification_to_send"; Update "notification_sent" column in "get_initiatives_for_notification" function
author | jbe |
---|---|
date | Tue Apr 05 20:12:24 2016 +0200 (2016-04-05) |
parents | d07e6a046d41 |
children | f5aba1fbb845 |
line diff
1.1 --- a/core.sql Tue Apr 05 00:55:31 2016 +0200 1.2 +++ b/core.sql Tue Apr 05 20:12:24 2016 +0200 1.3 @@ -149,6 +149,8 @@ 1.4 UNIQUE ("authority", "authority_uid"), 1.5 CONSTRAINT "authority_login_requires_authority" 1.6 CHECK ("authority" NOTNULL OR "authority_login" ISNULL), 1.7 + CONSTRAINT "notification_dow_requires_notification_hour" 1.8 + CHECK ("notification_dow" ISNULL OR "notification_hour" NOTNULL), 1.9 CONSTRAINT "name_not_null_if_activated" 1.10 CHECK ("activated" ISNULL OR "name" NOTNULL) ); 1.11 CREATE INDEX "member_authority_login_idx" ON "member" ("authority_login"); 1.12 @@ -2612,15 +2614,42 @@ 1.13 ); 1.14 1.15 CREATE VIEW "scheduled_notification_to_send" AS 1.16 - SELECT "id" AS "recipient_id" 1.17 - FROM "member" 1.18 - WHERE "member"."disable_notifications" = FALSE 1.19 - AND COALESCE("notification_dow" = EXTRACT(DOW FROM now()), TRUE) 1.20 - AND "notification_hour" = EXTRACT(HOUR FROM now()) 1.21 - AND NOT ( 1.22 - "notification_sent" NOTNULL AND 1.23 - "notification_sent"::DATE = now()::DATE AND 1.24 - EXTRACT(HOUR FROM "notification_sent") = EXTRACT(HOUR FROM now()) ); 1.25 + SELECT * FROM ( 1.26 + SELECT 1.27 + "id" AS "recipient_id", 1.28 + now() - CASE WHEN "notification_dow" ISNULL THEN 1.29 + ( "notification_sent"::DATE + CASE 1.30 + WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour" 1.31 + THEN 0 ELSE 1 END 1.32 + )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour" 1.33 + ELSE 1.34 + ( "notification_sent"::DATE + 1.35 + ( 7 + "notification_dow" - 1.36 + EXTRACT(DOW FROM 1.37 + ( "notification_sent"::DATE + CASE 1.38 + WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour" 1.39 + THEN 0 ELSE 1 END 1.40 + )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour" 1.41 + )::INTEGER 1.42 + ) % 7 + 1.43 + CASE 1.44 + WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour" 1.45 + THEN 0 ELSE 1 1.46 + END 1.47 + )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour" 1.48 + END AS "pending" 1.49 + FROM ( 1.50 + SELECT 1.51 + "id", 1.52 + COALESCE("notification_sent", "activated") AS "notification_sent", 1.53 + "notification_dow", 1.54 + "notification_hour" 1.55 + FROM "member" 1.56 + WHERE "disable_notifications" = FALSE 1.57 + AND "notification_hour" NOTNULL 1.58 + ) AS "subquery1" 1.59 + ) AS "subquery2" 1.60 + WHERE "pending" > '0'::INTERVAL; 1.61 1.62 CREATE VIEW "newsletter_to_send" AS 1.63 SELECT 1.64 @@ -3181,7 +3210,9 @@ 1.65 AND "initiative"."id" = "initiative_notification_sent"."initiative_id" 1.66 AND "issue"."id" = "initiative"."issue_id" 1.67 AND ( "issue"."closed" NOTNULL OR "issue"."fully_frozen" NOTNULL ); 1.68 - UPDATE "member" SET "notification_counter" = "notification_counter" + 1 1.69 + UPDATE "member" SET 1.70 + "notification_counter" = "notification_counter" + 1 AND 1.71 + "notification_sent" = now() 1.72 WHERE "id" = "recipient_id_p"; 1.73 RETURN; 1.74 END;