liquid_feedback_core

changeset 521:65bd17395c28

Exclude locked members as recipients in view "scheduled_notification_to_send"
author jbe
date Wed May 04 21:47:29 2016 +0200 (2016-05-04)
parents 053190248598
children 2f1c06608def
files core.sql update/core-update.v3.2.1-v3.2.2.sql
line diff
     1.1 --- a/core.sql	Wed May 04 21:44:18 2016 +0200
     1.2 +++ b/core.sql	Wed May 04 21:47:29 2016 +0200
     1.3 @@ -2710,7 +2710,8 @@
     1.4          "notification_dow",
     1.5          "notification_hour"
     1.6        FROM "member"
     1.7 -      WHERE "disable_notifications" = FALSE
     1.8 +      WHERE "locked" = FALSE
     1.9 +      AND "disable_notifications" = FALSE
    1.10        AND "notification_hour" NOTNULL
    1.11      ) AS "subquery1"
    1.12    ) AS "subquery2"
     2.1 --- a/update/core-update.v3.2.1-v3.2.2.sql	Wed May 04 21:44:18 2016 +0200
     2.2 +++ b/update/core-update.v3.2.1-v3.2.2.sql	Wed May 04 21:47:29 2016 +0200
     2.3 @@ -98,4 +98,43 @@
     2.4      END;
     2.5    $$;
     2.6  
     2.7 +CREATE OR REPLACE VIEW "scheduled_notification_to_send" AS
     2.8 +  SELECT * FROM (
     2.9 +    SELECT
    2.10 +      "id" AS "recipient_id",
    2.11 +      now() - CASE WHEN "notification_dow" ISNULL THEN
    2.12 +        ( "notification_sent"::DATE + CASE
    2.13 +          WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour"
    2.14 +          THEN 0 ELSE 1 END
    2.15 +        )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour"
    2.16 +      ELSE
    2.17 +        ( "notification_sent"::DATE +
    2.18 +          ( 7 + "notification_dow" -
    2.19 +            EXTRACT(DOW FROM
    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 +            )::INTEGER
    2.25 +          ) % 7 +
    2.26 +          CASE
    2.27 +            WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour"
    2.28 +            THEN 0 ELSE 1
    2.29 +          END
    2.30 +        )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour"
    2.31 +      END AS "pending"
    2.32 +    FROM (
    2.33 +      SELECT
    2.34 +        "id",
    2.35 +        COALESCE("notification_sent", "activated") AS "notification_sent",
    2.36 +        "notification_dow",
    2.37 +        "notification_hour"
    2.38 +      FROM "member"
    2.39 +      WHERE "locked" = FALSE
    2.40 +      AND "disable_notifications" = FALSE
    2.41 +      AND "notification_hour" NOTNULL
    2.42 +    ) AS "subquery1"
    2.43 +  ) AS "subquery2"
    2.44 +  WHERE "pending" > '0'::INTERVAL;
    2.45 +
    2.46  COMMIT;

Impressum / About Us