-- Evergreen DB patch 0734.tpac_holdable_check.sql -- BEGIN; -- check whether patch can be applied SELECT evergreen.upgrade_deps_block_check('0734', :eg_version); CREATE OR REPLACE FUNCTION asset.record_has_holdable_copy ( rid BIGINT ) RETURNS BOOL AS $f$ BEGIN PERFORM 1 FROM asset.copy acp JOIN asset.call_number acn ON acp.call_number = acn.id JOIN asset.copy_location acpl ON acp.location = acpl.id JOIN config.copy_status ccs ON acp.status = ccs.id WHERE acn.record = rid AND acp.holdable = true AND acpl.holdable = true AND ccs.holdable = true AND acp.deleted = false LIMIT 1; IF FOUND THEN RETURN true; END IF; RETURN FALSE; END; $f$ LANGUAGE PLPGSQL; CREATE OR REPLACE FUNCTION asset.metarecord_has_holdable_copy ( rid BIGINT ) RETURNS BOOL AS $f$ BEGIN PERFORM 1 FROM asset.copy acp JOIN asset.call_number acn ON acp.call_number = acn.id JOIN asset.copy_location acpl ON acp.location = acpl.id JOIN config.copy_status ccs ON acp.status = ccs.id JOIN metabib.metarecord_source_map mmsm ON acn.record = mmsm.source WHERE mmsm.metarecord = rid AND acp.holdable = true AND acpl.holdable = true AND ccs.holdable = true AND acp.deleted = false LIMIT 1; IF FOUND THEN RETURN true; END IF; RETURN FALSE; END; $f$ LANGUAGE PLPGSQL; CREATE OR REPLACE FUNCTION unapi.holdings_xml ( bid BIGINT, ouid INT, org TEXT, depth INT DEFAULT NULL, includes TEXT[] DEFAULT NULL::TEXT[], slimit HSTORE DEFAULT NULL, soffset HSTORE DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE, pref_lib INT DEFAULT NULL ) RETURNS XML AS $F$ SELECT XMLELEMENT( name holdings, XMLATTRIBUTES( CASE WHEN $8 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns, CASE WHEN ('bre' = ANY ($5)) THEN 'tag:open-ils.org:U2@bre/' || $1 || '/' || $3 ELSE NULL END AS id, (SELECT record_has_holdable_copy FROM asset.record_has_holdable_copy($1)) AS has_holdable ), XMLELEMENT( name counts, (SELECT XMLAGG(XMLELEMENT::XML) FROM ( SELECT XMLELEMENT( name count, XMLATTRIBUTES('public' as type, depth, org_unit, coalesce(transcendant,0) as transcendant, available, visible as count, unshadow) )::text FROM asset.opac_ou_record_copy_count($2, $1) UNION SELECT XMLELEMENT( name count, XMLATTRIBUTES('staff' as type, depth, org_unit, coalesce(transcendant,0) as transcendant, available, visible as count, unshadow) )::text FROM asset.staff_ou_record_copy_count($2, $1) UNION SELECT XMLELEMENT( name count, XMLATTRIBUTES('pref_lib' as type, depth, org_unit, coalesce(transcendant,0) as transcendant, available, visible as count, unshadow) )::text FROM asset.opac_ou_record_copy_count($9, $1) ORDER BY 1 )x) ), CASE WHEN ('bmp' = ANY ($5)) THEN XMLELEMENT( name monograph_parts, (SELECT XMLAGG(bmp) FROM ( 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) FROM biblio.monograph_part WHERE record = $1 )x) ) ELSE NULL END, XMLELEMENT( name volumes, (SELECT XMLAGG(acn ORDER BY rank, name, label_sortkey) FROM ( -- Physical copies 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 FROM evergreen.ranked_volumes($1, $2, $4, $6, $7, $9) AS y UNION ALL -- Located URIs 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 FROM evergreen.located_uris($1, $2, $9) AS uris )x) ), CASE WHEN ('ssub' = ANY ($5)) THEN XMLELEMENT( name subscriptions, (SELECT XMLAGG(ssub) FROM ( SELECT unapi.ssub(id,'xml','subscription','{}'::TEXT[], $3, $4, $6, $7, FALSE) FROM serial.subscription WHERE record_entry = $1 )x) ) ELSE NULL END, CASE WHEN ('acp' = ANY ($5)) THEN XMLELEMENT( name foreign_copies, (SELECT XMLAGG(acp) FROM ( SELECT unapi.acp(p.target_copy,'xml','copy',evergreen.array_remove_item_by_value($5,'acp'), $3, $4, $6, $7, FALSE) FROM biblio.peer_bib_copy_map p JOIN asset.copy c ON (p.target_copy = c.id) WHERE NOT c.deleted AND p.peer_record = $1 LIMIT ($6 -> 'acp')::INT OFFSET ($7 -> 'acp')::INT )x) ) ELSE NULL END ); $F$ LANGUAGE SQL STABLE; COMMIT;