SHARE
TWEET

evergreen ranking functions

a guest Apr 30th, 2014 187 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE OR REPLACE FUNCTION evergreen.rank_cp_status(STATUS INT)
  2. RETURNS INTEGER AS $$
  3.     WITH totally_available AS (
  4.         SELECT id, 0 AS avail_rank
  5.         FROM config.copy_status
  6.         WHERE opac_visible IS TRUE
  7.             AND copy_active IS TRUE
  8.             AND id != 1 -- "Checked out"
  9.     ), almost_available AS (
  10.         SELECT id, 10 AS avail_rank
  11.         FROM config.copy_status
  12.         WHERE holdable IS TRUE
  13.             AND opac_visible IS TRUE
  14.             AND copy_active IS FALSE
  15.             OR id = 1 -- "Checked out"
  16.     )
  17.     SELECT COALESCE(
  18.         (SELECT avail_rank FROM totally_available WHERE $1 IN (id)),
  19.         (SELECT avail_rank FROM almost_available WHERE $1 IN (id)),
  20.         100
  21.     );
  22. $$ LANGUAGE SQL STABLE;
  23.  
  24. CREATE OR REPLACE FUNCTION evergreen.ranked_volumes(
  25.     bibid BIGINT[],
  26.     ouid INT,
  27.     depth INT DEFAULT NULL,
  28.     slimit HSTORE DEFAULT NULL,
  29.     soffset HSTORE DEFAULT NULL,
  30.     pref_lib INT DEFAULT NULL,
  31.     includes TEXT[] DEFAULT NULL::TEXT[]
  32. ) RETURNS TABLE (id BIGINT, name TEXT, label_sortkey TEXT, rank BIGINT) AS $$
  33.     SELECT ua.id, ua.name, ua.label_sortkey, MIN(ua.rank) AS rank FROM (
  34.         SELECT acn.id, aou.name, acn.label_sortkey,
  35.             evergreen.rank_ou(aou.id, $2, $6), evergreen.rank_cp_status(acp.STATUS),
  36.             RANK() OVER w
  37.         FROM asset.call_number acn
  38.             JOIN asset.copy acp ON (acn.id = acp.call_number)
  39.             JOIN actor.org_unit_descendants( $2, COALESCE(
  40.                 $3, (
  41.                     SELECT depth
  42.                     FROM actor.org_unit_type aout
  43.                         INNER JOIN actor.org_unit ou ON ou_type = aout.id
  44.                     WHERE ou.id = $2
  45.                 ), $6)
  46.             ) AS aou ON (acp.circ_lib = aou.id)
  47.         WHERE acn.record = ANY ($1)
  48.             AND acn.deleted IS FALSE
  49.             AND acp.deleted IS FALSE
  50.             AND CASE WHEN ('exclude_invisible_acn' = ANY($7)) THEN
  51.                 EXISTS (
  52.                     SELECT 1
  53.                     FROM asset.opac_visible_copies
  54.                     WHERE copy_id = acp.id AND record = acn.record
  55.                 ) ELSE TRUE END
  56.         GROUP BY acn.id, acp.STATUS, aou.name, acn.label_sortkey, aou.id
  57.         WINDOW w AS (
  58.             ORDER BY evergreen.rank_ou(aou.id, $2, $6), evergreen.rank_cp_status(acp.STATUS)
  59.         )
  60.     ) AS ua
  61.     GROUP BY ua.id, ua.name, ua.label_sortkey
  62.     ORDER BY rank, ua.name, ua.label_sortkey
  63.     LIMIT ($4 -> 'acn')::INT
  64.     OFFSET ($5 -> 'acn')::INT;
  65. $$
  66. LANGUAGE SQL STABLE;
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Top