1.1 --- a/update/core-update.v3.2.1-v3.2.2.sql	Wed May 04 21:44:18 2016 +0200
     1.2 +++ b/update/core-update.v3.2.1-v3.2.2.sql	Wed May 04 21:47:29 2016 +0200
     1.3 @@ -98,4 +98,43 @@
     1.4      END;
     1.5    $$;
     1.6  
     1.7 +CREATE OR REPLACE VIEW "scheduled_notification_to_send" AS
     1.8 +  SELECT * FROM (
     1.9 +    SELECT
    1.10 +      "id" AS "recipient_id",
    1.11 +      now() - CASE WHEN "notification_dow" ISNULL THEN
    1.12 +        ( "notification_sent"::DATE + CASE
    1.13 +          WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour"
    1.14 +          THEN 0 ELSE 1 END
    1.15 +        )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour"
    1.16 +      ELSE
    1.17 +        ( "notification_sent"::DATE +
    1.18 +          ( 7 + "notification_dow" -
    1.19 +            EXTRACT(DOW FROM
    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 +            )::INTEGER
    1.25 +          ) % 7 +
    1.26 +          CASE
    1.27 +            WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour"
    1.28 +            THEN 0 ELSE 1
    1.29 +          END
    1.30 +        )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour"
    1.31 +      END AS "pending"
    1.32 +    FROM (
    1.33 +      SELECT
    1.34 +        "id",
    1.35 +        COALESCE("notification_sent", "activated") AS "notification_sent",
    1.36 +        "notification_dow",
    1.37 +        "notification_hour"
    1.38 +      FROM "member"
    1.39 +      WHERE "locked" = FALSE
    1.40 +      AND "disable_notifications" = FALSE
    1.41 +      AND "notification_hour" NOTNULL
    1.42 +    ) AS "subquery1"
    1.43 +  ) AS "subquery2"
    1.44 +  WHERE "pending" > '0'::INTERVAL;
    1.45 +
    1.46  COMMIT;