1 -- Evergreen DB patch 0734.tpac_holdable_check.sql
5 -- check whether patch can be applied
6 SELECT evergreen.upgrade_deps_block_check('0734', :eg_version);
8 CREATE OR REPLACE FUNCTION asset.record_has_holdable_copy ( rid BIGINT ) RETURNS BOOL AS $f$
13 JOIN asset.call_number acn ON acp.call_number = acn.id
14 JOIN asset.copy_location acpl ON acp.location = acpl.id
15 JOIN config.copy_status ccs ON acp.status = ccs.id
18 AND acp.holdable = true
19 AND acpl.holdable = true
20 AND ccs.holdable = true
21 AND acp.deleted = false
30 CREATE OR REPLACE FUNCTION asset.metarecord_has_holdable_copy ( rid BIGINT ) RETURNS BOOL AS $f$
35 JOIN asset.call_number acn ON acp.call_number = acn.id
36 JOIN asset.copy_location acpl ON acp.location = acpl.id
37 JOIN config.copy_status ccs ON acp.status = ccs.id
38 JOIN metabib.metarecord_source_map mmsm ON acn.record = mmsm.source
41 AND acp.holdable = true
42 AND acpl.holdable = true
43 AND ccs.holdable = true
44 AND acp.deleted = false
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) 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;