jbe@520: BEGIN; jbe@520: jbe@520: CREATE OR REPLACE VIEW "liquid_feedback_version" AS jbe@520: SELECT * FROM (VALUES ('3.2.2', 3, 2, 2)) jbe@520: AS "subquery"("string", "major", "minor", "revision"); jbe@520: jbe@520: CREATE OR REPLACE FUNCTION "featured_initiative" jbe@520: ( "recipient_id_p" "member"."id"%TYPE, jbe@520: "area_id_p" "area"."id"%TYPE ) jbe@520: RETURNS SETOF "initiative"."id"%TYPE jbe@520: LANGUAGE 'plpgsql' STABLE AS $$ jbe@520: DECLARE jbe@520: "counter_v" "member"."notification_counter"%TYPE; jbe@520: "sample_size_v" "member"."notification_sample_size"%TYPE; jbe@520: "initiative_id_ary" INT4[]; --"initiative"."id"%TYPE[] jbe@520: "match_v" BOOLEAN; jbe@520: "member_id_v" "member"."id"%TYPE; jbe@520: "seed_v" TEXT; jbe@520: "initiative_id_v" "initiative"."id"%TYPE; jbe@520: BEGIN jbe@520: SELECT "notification_counter", "notification_sample_size" jbe@520: INTO "counter_v", "sample_size_v" jbe@520: FROM "member" WHERE "id" = "recipient_id_p"; jbe@520: IF COALESCE("sample_size_v" <= 0, TRUE) THEN jbe@520: RETURN; jbe@520: END IF; jbe@520: "initiative_id_ary" := '{}'; jbe@520: LOOP jbe@520: "match_v" := FALSE; jbe@520: FOR "member_id_v", "seed_v" IN jbe@520: SELECT * FROM ( jbe@520: SELECT DISTINCT jbe@520: "supporter"."member_id", jbe@520: md5( jbe@520: "recipient_id_p" || '-' || jbe@520: "counter_v" || '-' || jbe@520: "area_id_p" || '-' || jbe@520: "supporter"."member_id" jbe@520: ) AS "seed" jbe@520: FROM "supporter" jbe@520: JOIN "initiative" ON "initiative"."id" = "supporter"."initiative_id" jbe@520: JOIN "issue" ON "issue"."id" = "initiative"."issue_id" jbe@520: WHERE "supporter"."member_id" != "recipient_id_p" jbe@520: AND "issue"."area_id" = "area_id_p" jbe@520: AND "issue"."state" IN ('admission', 'discussion', 'verification') jbe@520: ) AS "subquery" jbe@520: ORDER BY "seed" jbe@520: LOOP jbe@520: SELECT "initiative"."id" INTO "initiative_id_v" jbe@520: FROM "initiative" jbe@520: JOIN "issue" ON "issue"."id" = "initiative"."issue_id" jbe@520: JOIN "area" ON "area"."id" = "issue"."area_id" jbe@520: JOIN "supporter" ON "supporter"."initiative_id" = "initiative"."id" jbe@520: LEFT JOIN "supporter" AS "self_support" ON jbe@520: "self_support"."initiative_id" = "initiative"."id" AND jbe@520: "self_support"."member_id" = "recipient_id_p" jbe@520: LEFT JOIN "privilege" ON jbe@520: "privilege"."member_id" = "recipient_id_p" AND jbe@520: "privilege"."unit_id" = "area"."unit_id" AND jbe@520: "privilege"."voting_right" = TRUE jbe@520: LEFT JOIN "subscription" ON jbe@520: "subscription"."member_id" = "recipient_id_p" AND jbe@520: "subscription"."unit_id" = "area"."unit_id" jbe@520: LEFT JOIN "ignored_initiative" ON jbe@520: "ignored_initiative"."member_id" = "recipient_id_p" AND jbe@520: "ignored_initiative"."initiative_id" = "initiative"."id" jbe@520: WHERE "supporter"."member_id" = "member_id_v" jbe@520: AND "issue"."area_id" = "area_id_p" jbe@520: AND "issue"."state" IN ('admission', 'discussion', 'verification') jbe@520: AND "initiative"."revoked" ISNULL jbe@520: AND "self_support"."member_id" ISNULL jbe@520: AND NOT "initiative_id_ary" @> ARRAY["initiative"."id"] jbe@520: AND ( jbe@520: "privilege"."member_id" NOTNULL OR jbe@520: "subscription"."member_id" NOTNULL ) jbe@520: AND "ignored_initiative"."member_id" ISNULL jbe@520: AND NOT EXISTS ( jbe@520: SELECT NULL FROM "draft" jbe@520: JOIN "ignored_member" ON jbe@520: "ignored_member"."member_id" = "recipient_id_p" AND jbe@520: "ignored_member"."other_member_id" = "draft"."author_id" jbe@520: WHERE "draft"."initiative_id" = "initiative"."id" jbe@520: ) jbe@520: ORDER BY md5("seed_v" || '-' || "initiative"."id") jbe@520: LIMIT 1; jbe@520: IF FOUND THEN jbe@520: "match_v" := TRUE; jbe@520: RETURN NEXT "initiative_id_v"; jbe@520: IF array_length("initiative_id_ary", 1) + 1 >= "sample_size_v" THEN jbe@520: RETURN; jbe@520: END IF; jbe@520: "initiative_id_ary" := "initiative_id_ary" || "initiative_id_v"; jbe@520: END IF; jbe@520: END LOOP; jbe@520: EXIT WHEN NOT "match_v"; jbe@520: END LOOP; jbe@520: RETURN; jbe@520: END; jbe@520: $$; jbe@520: jbe@521: CREATE OR REPLACE VIEW "scheduled_notification_to_send" AS jbe@521: SELECT * FROM ( jbe@521: SELECT jbe@521: "id" AS "recipient_id", jbe@521: now() - CASE WHEN "notification_dow" ISNULL THEN jbe@521: ( "notification_sent"::DATE + CASE jbe@521: WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour" jbe@521: THEN 0 ELSE 1 END jbe@521: )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour" jbe@521: ELSE jbe@521: ( "notification_sent"::DATE + jbe@521: ( 7 + "notification_dow" - jbe@521: EXTRACT(DOW FROM jbe@521: ( "notification_sent"::DATE + CASE jbe@521: WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour" jbe@521: THEN 0 ELSE 1 END jbe@521: )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour" jbe@521: )::INTEGER jbe@521: ) % 7 + jbe@521: CASE jbe@521: WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour" jbe@521: THEN 0 ELSE 1 jbe@521: END jbe@521: )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour" jbe@521: END AS "pending" jbe@521: FROM ( jbe@521: SELECT jbe@521: "id", jbe@521: COALESCE("notification_sent", "activated") AS "notification_sent", jbe@521: "notification_dow", jbe@521: "notification_hour" jbe@521: FROM "member" jbe@521: WHERE "locked" = FALSE jbe@521: AND "disable_notifications" = FALSE jbe@521: AND "notification_hour" NOTNULL jbe@521: ) AS "subquery1" jbe@521: ) AS "subquery2" jbe@521: WHERE "pending" > '0'::INTERVAL; jbe@521: jbe@520: COMMIT;