3 SELECT evergreen.upgrade_deps_block_check('0885', :eg_version);
5 CREATE OR REPLACE FUNCTION evergreen.ranked_volumes(
8 depth INT DEFAULT NULL,
9 slimit HSTORE DEFAULT NULL,
10 soffset HSTORE DEFAULT NULL,
11 pref_lib INT DEFAULT NULL,
12 includes TEXT[] DEFAULT NULL::TEXT[]
13 ) RETURNS TABLE(id BIGINT, name TEXT, label_sortkey TEXT, rank BIGINT) AS $$
14 WITH RECURSIVE ou_depth AS (
19 FROM actor.org_unit_type aout
20 INNER JOIN actor.org_unit ou ON ou_type = aout.id
24 ), descendant_depth AS (
28 FROM actor.org_unit ou
29 JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
30 JOIN anscestor_depth ad ON (ad.id = ou.id),
32 WHERE ad.depth = ou_depth.depth
37 FROM actor.org_unit ou
38 JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
39 JOIN descendant_depth ot ON (ot.id = ou.parent_ou)
40 ), anscestor_depth AS (
44 FROM actor.org_unit ou
45 JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
51 FROM actor.org_unit ou
52 JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
53 JOIN anscestor_depth ot ON (ot.parent_ou = ou.id)
55 SELECT ou.* FROM actor.org_unit ou JOIN descendant_depth USING (id)
58 SELECT ua.id, ua.name, ua.label_sortkey, MIN(ua.rank) AS rank FROM (
59 SELECT acn.id, aou.name, acn.label_sortkey,
61 FROM asset.call_number acn
62 JOIN asset.copy acp ON (acn.id = acp.call_number)
63 JOIN descendants AS aou ON (acp.circ_lib = aou.id)
64 WHERE acn.record = ANY ($1)
65 AND acn.deleted IS FALSE
66 AND acp.deleted IS FALSE
67 AND CASE WHEN ('exclude_invisible_acn' = ANY($7)) THEN
70 FROM asset.opac_visible_copies
71 WHERE copy_id = acp.id AND record = acn.record
73 GROUP BY acn.id, acp.status, aou.name, acn.label_sortkey, aou.id
77 CASE WHEN aou.id = $2 THEN -20000 END,
78 CASE WHEN aou.id = $6 THEN -10000 END,
79 (SELECT distance - 5000
80 FROM actor.org_unit_descendants_distance($6) as x
81 WHERE x.id = aou.id AND $6 IN (
82 SELECT q.id FROM actor.org_unit_descendants($2) as q)),
83 (SELECT e.distance FROM actor.org_unit_descendants_distance($2) as e WHERE e.id = aou.id),
86 evergreen.rank_cp_status(acp.status)
89 GROUP BY ua.id, ua.name, ua.label_sortkey
90 ORDER BY rank, ua.name, ua.label_sortkey
91 LIMIT ($4 -> 'acn')::INT
92 OFFSET ($5 -> 'acn')::INT;
93 $$ LANGUAGE SQL STABLE ROWS 10;
95 CREATE OR REPLACE FUNCTION evergreen.ranked_volumes
96 ( bibid BIGINT, ouid INT, depth INT DEFAULT NULL, slimit HSTORE DEFAULT NULL, soffset HSTORE DEFAULT NULL, pref_lib INT DEFAULT NULL, includes TEXT[] DEFAULT NULL::TEXT[] )
97 RETURNS TABLE (id BIGINT, name TEXT, label_sortkey TEXT, rank BIGINT)
98 AS $$ SELECT * FROM evergreen.ranked_volumes(ARRAY[$1],$2,$3,$4,$5,$6,$7) $$ LANGUAGE SQL STABLE;