4 SELECT evergreen.upgrade_deps_block_check('0769', :eg_version);
6 DROP FUNCTION IF EXISTS
7 evergreen.ranked_volumes(BIGINT, INT, INT, HSTORE, HSTORE, INT);
9 CREATE OR REPLACE FUNCTION evergreen.ranked_volumes(
12 depth INT DEFAULT NULL,
13 slimit HSTORE DEFAULT NULL,
14 soffset HSTORE DEFAULT NULL,
15 pref_lib INT DEFAULT NULL,
16 includes TEXT[] DEFAULT NULL::TEXT[]
17 ) RETURNS TABLE (id BIGINT, name TEXT, label_sortkey TEXT, rank BIGINT) AS $$
18 SELECT ua.id, ua.name, ua.label_sortkey, MIN(ua.rank) AS rank FROM (
19 SELECT acn.id, aou.name, acn.label_sortkey,
20 evergreen.rank_ou(aou.id, $2, $6), evergreen.rank_cp_status(acp.status),
22 FROM asset.call_number acn
23 JOIN asset.copy acp ON (acn.id = acp.call_number)
24 JOIN actor.org_unit_descendants( $2, COALESCE(
27 FROM actor.org_unit_type aout
28 INNER JOIN actor.org_unit ou ON ou_type = aout.id
31 ) AS aou ON (acp.circ_lib = aou.id)
33 AND acn.deleted IS FALSE
34 AND acp.deleted IS FALSE
35 AND CASE WHEN ('exclude_invisible_acn' = ANY($7)) THEN
38 FROM asset.opac_visible_copies
39 WHERE copy_id = acp.id AND record = acn.record
41 GROUP BY acn.id, acp.status, aou.name, acn.label_sortkey, aou.id
43 ORDER BY evergreen.rank_ou(aou.id, $2, $6), evergreen.rank_cp_status(acp.status)
46 GROUP BY ua.id, ua.name, ua.label_sortkey
47 ORDER BY rank, ua.name, ua.label_sortkey
48 LIMIT ($4 -> 'acn')::INT
49 OFFSET ($5 -> 'acn')::INT;
53 CREATE OR REPLACE FUNCTION unapi.holdings_xml (
57 depth INT DEFAULT NULL,
58 includes TEXT[] DEFAULT NULL::TEXT[],
59 slimit HSTORE DEFAULT NULL,
60 soffset HSTORE DEFAULT NULL,
61 include_xmlns BOOL DEFAULT TRUE,
62 pref_lib INT DEFAULT NULL
68 CASE WHEN $8 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
69 CASE WHEN ('bre' = ANY ($5)) THEN 'tag:open-ils.org:U2@bre/' || $1 || '/' || $3 ELSE NULL END AS id,
70 (SELECT record_has_holdable_copy FROM asset.record_has_holdable_copy($1)) AS has_holdable
74 (SELECT XMLAGG(XMLELEMENT::XML) FROM (
77 XMLATTRIBUTES('public' as type, depth, org_unit, coalesce(transcendant,0) as transcendant, available, visible as count, unshadow)
79 FROM asset.opac_ou_record_copy_count($2, $1)
83 XMLATTRIBUTES('staff' as type, depth, org_unit, coalesce(transcendant,0) as transcendant, available, visible as count, unshadow)
85 FROM asset.staff_ou_record_copy_count($2, $1)
89 XMLATTRIBUTES('pref_lib' as type, depth, org_unit, coalesce(transcendant,0) as transcendant, available, visible as count, unshadow)
91 FROM asset.opac_ou_record_copy_count($9, $1)
96 WHEN ('bmp' = ANY ($5)) THEN
99 (SELECT XMLAGG(bmp) FROM (
100 SELECT unapi.bmp( id, 'xml', 'monograph_part', evergreen.array_remove_item_by_value( evergreen.array_remove_item_by_value($5,'bre'), 'holdings_xml'), $3, $4, $6, $7, FALSE)
101 FROM biblio.monograph_part
109 (SELECT XMLAGG(acn ORDER BY rank, name, label_sortkey) FROM (
111 SELECT unapi.acn(y.id,'xml','volume',evergreen.array_remove_item_by_value( evergreen.array_remove_item_by_value($5,'holdings_xml'),'bre'), $3, $4, $6, $7, FALSE), y.rank, name, label_sortkey
112 FROM evergreen.ranked_volumes($1, $2, $4, $6, $7, $9, $5) AS y
115 SELECT unapi.acn(uris.id,'xml','volume',evergreen.array_remove_item_by_value( evergreen.array_remove_item_by_value($5,'holdings_xml'),'bre'), $3, $4, $6, $7, FALSE), 0, name, label_sortkey
116 FROM evergreen.located_uris($1, $2, $9) AS uris
119 CASE WHEN ('ssub' = ANY ($5)) THEN
122 (SELECT XMLAGG(ssub) FROM (
123 SELECT unapi.ssub(id,'xml','subscription','{}'::TEXT[], $3, $4, $6, $7, FALSE)
124 FROM serial.subscription
125 WHERE record_entry = $1
129 CASE WHEN ('acp' = ANY ($5)) THEN
132 (SELECT XMLAGG(acp) FROM (
133 SELECT unapi.acp(p.target_copy,'xml','copy',evergreen.array_remove_item_by_value($5,'acp'), $3, $4, $6, $7, FALSE)
134 FROM biblio.peer_bib_copy_map p
135 JOIN asset.copy c ON (p.target_copy = c.id)
136 WHERE NOT c.deleted AND p.peer_record = $1
137 LIMIT ($6 -> 'acp')::INT
138 OFFSET ($7 -> 'acp')::INT
143 $F$ LANGUAGE SQL STABLE;