1.1 --- a/update/core-update.v3.1.0-v3.2.0.sql	Tue Apr 05 00:55:31 2016 +0200
     1.2 +++ b/update/core-update.v3.1.0-v3.2.0.sql	Tue Apr 05 20:12:24 2016 +0200
     1.3 @@ -331,15 +331,42 @@
     1.4    );
     1.5  
     1.6  CREATE VIEW "scheduled_notification_to_send" AS
     1.7 -  SELECT "id" AS "recipient_id"
     1.8 -  FROM "member"
     1.9 -  WHERE "member"."disable_notifications" = FALSE
    1.10 -  AND COALESCE("notification_dow" = EXTRACT(DOW FROM now()), TRUE)
    1.11 -  AND "notification_hour" = EXTRACT(HOUR FROM now())
    1.12 -  AND NOT (
    1.13 -    "notification_sent" NOTNULL AND
    1.14 -    "notification_sent"::DATE = now()::DATE AND
    1.15 -    EXTRACT(HOUR FROM "notification_sent") = EXTRACT(HOUR FROM now()) );
    1.16 +  SELECT * FROM (
    1.17 +    SELECT
    1.18 +      "id" AS "recipient_id",
    1.19 +      now() - CASE WHEN "notification_dow" ISNULL THEN
    1.20 +        ( "notification_sent"::DATE + CASE
    1.21 +          WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour"
    1.22 +          THEN 0 ELSE 1 END
    1.23 +        )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour"
    1.24 +      ELSE
    1.25 +        ( "notification_sent"::DATE +
    1.26 +          ( 7 + "notification_dow" -
    1.27 +            EXTRACT(DOW FROM
    1.28 +              ( "notification_sent"::DATE + CASE
    1.29 +                WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour"
    1.30 +                THEN 0 ELSE 1 END
    1.31 +              )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour"
    1.32 +            )::INTEGER
    1.33 +          ) % 7 +
    1.34 +          CASE
    1.35 +            WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour"
    1.36 +            THEN 0 ELSE 1
    1.37 +          END
    1.38 +        )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour"
    1.39 +      END AS "pending"
    1.40 +    FROM (
    1.41 +      SELECT
    1.42 +        "id",
    1.43 +        COALESCE("notification_sent", "activated") AS "notification_sent",
    1.44 +        "notification_dow",
    1.45 +        "notification_hour"
    1.46 +      FROM "member"
    1.47 +      WHERE "disable_notifications" = FALSE
    1.48 +      AND "notification_hour" NOTNULL
    1.49 +    ) AS "subquery1"
    1.50 +  ) AS "subquery2"
    1.51 +  WHERE "pending" > '0'::INTERVAL;
    1.52  
    1.53  CREATE VIEW "newsletter_to_send" AS
    1.54    SELECT
    1.55 @@ -411,7 +438,9 @@
    1.56          AND "initiative"."id" = "initiative_notification_sent"."initiative_id"
    1.57          AND "issue"."id" = "initiative"."issue_id"
    1.58          AND ( "issue"."closed" NOTNULL OR "issue"."fully_frozen" NOTNULL );
    1.59 -      UPDATE "member" SET "notification_counter" = "notification_counter" + 1
    1.60 +      UPDATE "member" SET
    1.61 +        "notification_counter" = "notification_counter" + 1 AND
    1.62 +        "notification_sent" = now()
    1.63          WHERE "id" = "recipient_id_p";
    1.64        RETURN;
    1.65      END;