liquid_feedback_core

changeset 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
files core.sql update/core-update.v3.1.0-v3.2.0.sql
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;
     2.1 --- a/update/core-update.v3.1.0-v3.2.0.sql	Tue Apr 05 00:55:31 2016 +0200
     2.2 +++ b/update/core-update.v3.1.0-v3.2.0.sql	Tue Apr 05 20:12:24 2016 +0200
     2.3 @@ -331,15 +331,42 @@
     2.4    );
     2.5  
     2.6  CREATE VIEW "scheduled_notification_to_send" AS
     2.7 -  SELECT "id" AS "recipient_id"
     2.8 -  FROM "member"
     2.9 -  WHERE "member"."disable_notifications" = FALSE
    2.10 -  AND COALESCE("notification_dow" = EXTRACT(DOW FROM now()), TRUE)
    2.11 -  AND "notification_hour" = EXTRACT(HOUR FROM now())
    2.12 -  AND NOT (
    2.13 -    "notification_sent" NOTNULL AND
    2.14 -    "notification_sent"::DATE = now()::DATE AND
    2.15 -    EXTRACT(HOUR FROM "notification_sent") = EXTRACT(HOUR FROM now()) );
    2.16 +  SELECT * FROM (
    2.17 +    SELECT
    2.18 +      "id" AS "recipient_id",
    2.19 +      now() - CASE WHEN "notification_dow" ISNULL THEN
    2.20 +        ( "notification_sent"::DATE + CASE
    2.21 +          WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour"
    2.22 +          THEN 0 ELSE 1 END
    2.23 +        )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour"
    2.24 +      ELSE
    2.25 +        ( "notification_sent"::DATE +
    2.26 +          ( 7 + "notification_dow" -
    2.27 +            EXTRACT(DOW FROM
    2.28 +              ( "notification_sent"::DATE + CASE
    2.29 +                WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour"
    2.30 +                THEN 0 ELSE 1 END
    2.31 +              )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour"
    2.32 +            )::INTEGER
    2.33 +          ) % 7 +
    2.34 +          CASE
    2.35 +            WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour"
    2.36 +            THEN 0 ELSE 1
    2.37 +          END
    2.38 +        )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour"
    2.39 +      END AS "pending"
    2.40 +    FROM (
    2.41 +      SELECT
    2.42 +        "id",
    2.43 +        COALESCE("notification_sent", "activated") AS "notification_sent",
    2.44 +        "notification_dow",
    2.45 +        "notification_hour"
    2.46 +      FROM "member"
    2.47 +      WHERE "disable_notifications" = FALSE
    2.48 +      AND "notification_hour" NOTNULL
    2.49 +    ) AS "subquery1"
    2.50 +  ) AS "subquery2"
    2.51 +  WHERE "pending" > '0'::INTERVAL;
    2.52  
    2.53  CREATE VIEW "newsletter_to_send" AS
    2.54    SELECT
    2.55 @@ -411,7 +438,9 @@
    2.56          AND "initiative"."id" = "initiative_notification_sent"."initiative_id"
    2.57          AND "issue"."id" = "initiative"."issue_id"
    2.58          AND ( "issue"."closed" NOTNULL OR "issue"."fully_frozen" NOTNULL );
    2.59 -      UPDATE "member" SET "notification_counter" = "notification_counter" + 1
    2.60 +      UPDATE "member" SET
    2.61 +        "notification_counter" = "notification_counter" + 1 AND
    2.62 +        "notification_sent" = now()
    2.63          WHERE "id" = "recipient_id_p";
    2.64        RETURN;
    2.65      END;

Impressum / About Us