4 DROP FUNCTION IF EXISTS
5 evergreen.ranked_volumes(BIGINT, INT, INT, HSTORE, HSTORE, INT);
7 CREATE OR REPLACE FUNCTION evergreen.ranked_volumes(
10 depth INT DEFAULT NULL,
11 slimit HSTORE DEFAULT NULL,
12 soffset HSTORE DEFAULT NULL,
13 pref_lib INT DEFAULT NULL,
14 includes TEXT[] DEFAULT NULL::TEXT[]
15 ) RETURNS TABLE (id BIGINT, name TEXT, label_sortkey TEXT, rank BIGINT) AS $$
16 SELECT ua.id, ua.name, ua.label_sortkey, MIN(ua.rank) AS rank FROM (
17 SELECT acn.id, aou.name, acn.label_sortkey,
18 evergreen.rank_ou(aou.id, $2, $6), evergreen.rank_cp_status(acp.status),
20 FROM asset.call_number acn
21 JOIN asset.copy acp ON (acn.id = acp.call_number)
22 JOIN actor.org_unit_descendants( $2, COALESCE(
25 FROM actor.org_unit_type aout
26 INNER JOIN actor.org_unit ou ON ou_type = aout.id
29 ) AS aou ON (acp.circ_lib = aou.id)
31 AND acn.deleted IS FALSE
32 AND acp.deleted IS FALSE
33 AND CASE WHEN ('exclude_invisible_acn' = ANY($7)) THEN
36 FROM asset.opac_visible_copies
37 WHERE copy_id = acp.id AND record = acn.record
39 GROUP BY acn.id, acp.status, aou.name, acn.label_sortkey, aou.id
41 ORDER BY evergreen.rank_ou(aou.id, $2, $6), evergreen.rank_cp_status(acp.status)
44 GROUP BY ua.id, ua.name, ua.label_sortkey
45 ORDER BY rank, ua.name, ua.label_sortkey
46 LIMIT ($4 -> 'acn')::INT
47 OFFSET ($5 -> 'acn')::INT;
51 CREATE OR REPLACE FUNCTION unapi.holdings_xml (
55 depth INT DEFAULT NULL,
56 includes TEXT[] DEFAULT NULL::TEXT[],
57 slimit HSTORE DEFAULT NULL,
58 soffset HSTORE DEFAULT NULL,
59 include_xmlns BOOL DEFAULT TRUE,
60 pref_lib INT DEFAULT NULL
66 CASE WHEN $8 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
67 CASE WHEN ('bre' = ANY ($5)) THEN 'tag:open-ils.org:U2@bre/' || $1 || '/' || $3 ELSE NULL END AS id,
68 (SELECT record_has_holdable_copy FROM asset.record_has_holdable_copy($1)) AS has_holdable
72 (SELECT XMLAGG(XMLELEMENT::XML) FROM (
75 XMLATTRIBUTES('public' as type, depth, org_unit, coalesce(transcendant,0) as transcendant, available, visible as count, unshadow)
77 FROM asset.opac_ou_record_copy_count($2, $1)
81 XMLATTRIBUTES('staff' as type, depth, org_unit, coalesce(transcendant,0) as transcendant, available, visible as count, unshadow)
83 FROM asset.staff_ou_record_copy_count($2, $1)
87 XMLATTRIBUTES('pref_lib' as type, depth, org_unit, coalesce(transcendant,0) as transcendant, available, visible as count, unshadow)
89 FROM asset.opac_ou_record_copy_count($9, $1)
94 WHEN ('bmp' = ANY ($5)) THEN
97 (SELECT XMLAGG(bmp) FROM (
98 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)
99 FROM biblio.monograph_part
107 (SELECT XMLAGG(acn ORDER BY rank, name, label_sortkey) FROM (
109 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
110 FROM evergreen.ranked_volumes($1, $2, $4, $6, $7, $9, $5) AS y
113 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
114 FROM evergreen.located_uris($1, $2, $9) AS uris
117 CASE WHEN ('ssub' = ANY ($5)) THEN
120 (SELECT XMLAGG(ssub) FROM (
121 SELECT unapi.ssub(id,'xml','subscription','{}'::TEXT[], $3, $4, $6, $7, FALSE)
122 FROM serial.subscription
123 WHERE record_entry = $1
127 CASE WHEN ('acp' = ANY ($5)) THEN
130 (SELECT XMLAGG(acp) FROM (
131 SELECT unapi.acp(p.target_copy,'xml','copy',evergreen.array_remove_item_by_value($5,'acp'), $3, $4, $6, $7, FALSE)
132 FROM biblio.peer_bib_copy_map p
133 JOIN asset.copy c ON (p.target_copy = c.id)
134 WHERE NOT c.deleted AND p.peer_record = $1
135 LIMIT ($6 -> 'acp')::INT
136 OFFSET ($7 -> 'acp')::INT
141 $F$ LANGUAGE SQL STABLE;