liquid_feedback_core

changeset 491:aa94c7dbb20f

New function "get_initiatives_for_notification"
author jbe
date Sun Apr 03 19:42:09 2016 +0200 (2016-04-03)
parents 16536937933e
children 917a65357efb
files core.sql
line diff
     1.1 --- a/core.sql	Sun Apr 03 18:00:07 2016 +0200
     1.2 +++ b/core.sql	Sun Apr 03 19:42:09 2016 +0200
     1.3 @@ -3093,6 +3093,65 @@
     1.4  
     1.5  
     1.6  
     1.7 +-------------------------
     1.8 +-- Notification system --
     1.9 +-------------------------
    1.10 +
    1.11 +CREATE FUNCTION "get_initiatives_for_notification"
    1.12 +  ( "member_id_p" "member"."id"%TYPE )
    1.13 +  RETURNS SETOF "initiative_for_notification"
    1.14 +  LANGUAGE 'plpgsql' VOLATILE AS $$
    1.15 +    DECLARE
    1.16 +      "result_row"           "initiative_for_notification"%ROWTYPE;
    1.17 +      "last_draft_id_v"      "draft"."id"%TYPE;
    1.18 +      "last_suggestion_id_v" "suggestion"."id"%TYPE;
    1.19 +    BEGIN
    1.20 +      PERFORM "require_transaction_isolation"();
    1.21 +      PERFORM NULL FROM "member" WHERE "id" = "member_id_p" FOR UPDATE;
    1.22 +      FOR "result_row" IN
    1.23 +        SELECT * FROM "initiative_for_notification"
    1.24 +        WHERE "seen_by_member_id" = "member_id_p"
    1.25 +      LOOP
    1.26 +        SELECT "id" INTO "last_draft_id_v" FROM "draft"
    1.27 +          WHERE "draft"."initiative_id" = "result_row"."id"
    1.28 +          ORDER BY "id" DESC LIMIT 1;
    1.29 +        SELECT "id" INTO "last_suggestion_id_v" FROM "suggestion"
    1.30 +          WHERE "suggestion"."initiative_id" = "result_row"."id"
    1.31 +          ORDER BY "id" DESC LIMIT 1;
    1.32 +        INSERT INTO "initiative_notification_sent"
    1.33 +          ("member_id", "initiative_id", "last_draft_id", "last_suggestion_id")
    1.34 +          VALUES (
    1.35 +            "member_id_p",
    1.36 +            "result_row"."id",
    1.37 +            "last_draft_id",
    1.38 +            "last_suggestion_id" )
    1.39 +          ON CONFLICT ("member_id", "initiative_id") DO UPDATE SET
    1.40 +            "last_draft_id" = CASE
    1.41 +              WHEN "last_draft_id" > "last_draft_id_v"
    1.42 +              THEN "last_draft_id"
    1.43 +              ELSE "last_draft_id_v"
    1.44 +            END,
    1.45 +            "last_suggestion_id" = CASE
    1.46 +              WHEN "last_suggestion_id" > "last_suggestion_id_v"
    1.47 +              THEN "last_suggestion_id"
    1.48 +              ELSE "last_suggestion_id_v"
    1.49 +            END;
    1.50 +        RETURN NEXT "result_row";
    1.51 +      END LOOP;
    1.52 +      DELETE FROM "initiative_notification_sent"
    1.53 +        USING "initiative", "issue"
    1.54 +        WHERE "initiative_notification_sent"."member_id" = "member_id_p"
    1.55 +        AND "initiative"."id" = "initiative_notification_sent"."initiative_id"
    1.56 +        AND "issue"."id" = "initiative"."issue_id"
    1.57 +        AND ( "issue"."closed" NOTNULL OR "issue"."fully_frozen" NOTNULL );
    1.58 +      UPDATE "member" SET "notification_counter" = "notification_counter" + 1
    1.59 +        WHERE "id" = "member_id_p";
    1.60 +      RETURN;
    1.61 +    END;
    1.62 +  $$;
    1.63 +
    1.64 +
    1.65 +
    1.66  ------------------------------------------------------------------------
    1.67  -- Regular tasks, except calculcation of snapshots and voting results --
    1.68  ------------------------------------------------------------------------

Impressum / About Us