BEGIN; SELECT evergreen.upgrade_deps_block_check('0866', :eg_version); DROP FUNCTION asset.record_has_holdable_copy (BIGINT); CREATE FUNCTION asset.record_has_holdable_copy ( rid BIGINT, ou INT DEFAULT NULL) 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 AND acp.circ_lib IN (SELECT id FROM actor.org_unit_descendants(COALESCE($2,(SELECT id FROM evergreen.org_top())))) LIMIT 1; IF FOUND THEN RETURN true; END IF; RETURN FALSE; END; $f$ LANGUAGE PLPGSQL; DROP FUNCTION asset.metarecord_has_holdable_copy (BIGINT); CREATE FUNCTION asset.metarecord_has_holdable_copy ( rid BIGINT, ou INT DEFAULT NULL) 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 AND acp.circ_lib IN (SELECT id FROM actor.org_unit_descendants(COALESCE($2,(SELECT id FROM evergreen.org_top())))) LIMIT 1; IF FOUND THEN RETURN true; END IF; RETURN FALSE; END; $f$ LANGUAGE PLPGSQL; CREATE OR REPLACE FUNCTION asset.opac_ou_metarecord_copy_count (org INT, rid BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$ DECLARE ans RECORD; trans INT; BEGIN SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) JOIN metabib.metarecord_source_map m ON (m.source = b.id) WHERE src.transcendant AND m.metarecord = rid; FOR ans IN SELECT u.id, t.depth FROM actor.org_unit_ancestors(org) AS u JOIN actor.org_unit_type t ON (u.ou_type = t.id) LOOP RETURN QUERY SELECT ans.depth, ans.id, COUNT( av.id ), SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ), COUNT( av.id ), trans FROM actor.org_unit_descendants(ans.id) d JOIN asset.opac_visible_copies av ON (av.circ_lib = d.id) JOIN asset.copy cp ON (cp.id = av.copy_id) JOIN metabib.metarecord_source_map m ON (m.metarecord = rid AND m.source = av.record) GROUP BY 1,2,6; IF NOT FOUND THEN RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans; END IF; END LOOP; RETURN; END; $f$ LANGUAGE PLPGSQL; CREATE OR REPLACE FUNCTION asset.opac_lasso_metarecord_copy_count (i_lasso INT, rid BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$ DECLARE ans RECORD; trans INT; BEGIN SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) JOIN metabib.metarecord_source_map m ON (m.source = b.id) WHERE src.transcendant AND m.metarecord = rid; FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP RETURN QUERY SELECT -1, ans.id, COUNT( av.id ), SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ), COUNT( av.id ), trans FROM actor.org_unit_descendants(ans.id) d JOIN asset.opac_visible_copies av ON (av.circ_lib = d.id) JOIN asset.copy cp ON (cp.id = av.copy_id) JOIN metabib.metarecord_source_map m ON (m.metarecord = rid AND m.source = av.record) GROUP BY 1,2,6; IF NOT FOUND THEN RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans; END IF; END LOOP; RETURN; END; $f$ LANGUAGE PLPGSQL; CREATE OR REPLACE FUNCTION asset.staff_ou_metarecord_copy_count (org INT, rid BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$ DECLARE ans RECORD; trans INT; BEGIN SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) JOIN metabib.metarecord_source_map m ON (m.source = b.id) WHERE src.transcendant AND m.metarecord = rid; FOR ans IN SELECT u.id, t.depth FROM actor.org_unit_ancestors(org) AS u JOIN actor.org_unit_type t ON (u.ou_type = t.id) LOOP RETURN QUERY SELECT ans.depth, ans.id, COUNT( cp.id ), SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ), COUNT( cp.id ), trans FROM actor.org_unit_descendants(ans.id) d JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted) JOIN asset.call_number cn ON (cn.id = cp.call_number AND NOT cn.deleted) JOIN metabib.metarecord_source_map m ON (m.metarecord = rid AND m.source = cn.record) GROUP BY 1,2,6; IF NOT FOUND THEN RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans; END IF; END LOOP; RETURN; END; $f$ LANGUAGE PLPGSQL; CREATE OR REPLACE FUNCTION asset.staff_lasso_metarecord_copy_count (i_lasso INT, rid BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$ DECLARE ans RECORD; trans INT; BEGIN SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) JOIN metabib.metarecord_source_map m ON (m.source = b.id) WHERE src.transcendant AND m.metarecord = rid; FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP RETURN QUERY SELECT -1, ans.id, COUNT( cp.id ), SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ), COUNT( cp.id ), trans FROM actor.org_unit_descendants(ans.id) d JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted) JOIN asset.call_number cn ON (cn.id = cp.call_number AND NOT cn.deleted) JOIN metabib.metarecord_source_map m ON (m.metarecord = rid AND m.source = cn.record) GROUP BY 1,2,6; IF NOT FOUND THEN RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans; END IF; END LOOP; RETURN; END; $f$ LANGUAGE PLPGSQL; CREATE OR REPLACE FUNCTION unapi.mmr_mra ( obj_id BIGINT, format TEXT, ename TEXT, includes TEXT[], org TEXT, depth INT DEFAULT NULL, 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 attributes, XMLATTRIBUTES( CASE WHEN $9 THEN 'http://open-ils.org/spec/indexing/v1' ELSE NULL END AS xmlns, 'tag:open-ils.org:U2@mmr/' || $1 AS metarecord ), (SELECT XMLAGG(foo.y) FROM ( SELECT DISTINCT ON (COALESCE(cvm.id,uvm.id)) COALESCE(cvm.id,uvm.id), XMLELEMENT( name field, XMLATTRIBUTES( mra.attr AS name, cvm.value AS "coded-value", cvm.id AS "cvmid", rad.composite, rad.multi, rad.filter, rad.sorter ), mra.value ) FROM metabib.record_attr_flat mra JOIN config.record_attr_definition rad ON (mra.attr = rad.name) LEFT JOIN config.coded_value_map cvm ON (cvm.ctype = mra.attr AND code = mra.value) LEFT JOIN metabib.uncontrolled_record_attr_value uvm ON (uvm.attr = mra.attr AND uvm.value = mra.value) WHERE mra.id IN ( WITH aou AS (SELECT COALESCE(id, (evergreen.org_top()).id) AS id FROM actor.org_unit WHERE shortname = $5 LIMIT 1) SELECT source FROM metabib.metarecord_source_map, aou WHERE metarecord = $1 AND ( EXISTS ( SELECT 1 FROM asset.opac_visible_copies WHERE record = source AND circ_lib IN ( SELECT id FROM actor.org_unit_descendants(aou.id, $6)) LIMIT 1 ) OR EXISTS (SELECT 1 FROM located_uris(source, aou.id, $10) LIMIT 1) ) ) ORDER BY 1 )foo(id,y) ) ) $F$ LANGUAGE SQL STABLE; CREATE OR REPLACE FUNCTION evergreen.ranked_volumes( 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[] ) RETURNS TABLE (id BIGINT, name TEXT, label_sortkey TEXT, rank BIGINT) AS $$ SELECT ua.id, ua.name, ua.label_sortkey, MIN(ua.rank) AS rank FROM ( SELECT acn.id, aou.name, acn.label_sortkey, evergreen.rank_ou(aou.id, $2, $6), evergreen.rank_cp_status(acp.status), RANK() OVER w FROM asset.call_number acn JOIN asset.copy acp ON (acn.id = acp.call_number) JOIN actor.org_unit_descendants( $2, COALESCE( $3, ( SELECT depth FROM actor.org_unit_type aout INNER JOIN actor.org_unit ou ON ou_type = aout.id WHERE ou.id = $2 ), $6) ) AS aou ON (acp.circ_lib = aou.id) WHERE acn.record = ANY ($1) AND acn.deleted IS FALSE AND acp.deleted IS FALSE AND CASE WHEN ('exclude_invisible_acn' = ANY($7)) THEN EXISTS ( SELECT 1 FROM asset.opac_visible_copies WHERE copy_id = acp.id AND record = acn.record ) ELSE TRUE END GROUP BY acn.id, acp.status, aou.name, acn.label_sortkey, aou.id WINDOW w AS ( ORDER BY evergreen.rank_ou(aou.id, $2, $6), evergreen.rank_cp_status(acp.status) ) ) AS ua GROUP BY ua.id, ua.name, ua.label_sortkey ORDER BY rank, ua.name, ua.label_sortkey LIMIT ($4 -> 'acn')::INT OFFSET ($5 -> 'acn')::INT; $$ LANGUAGE SQL STABLE; CREATE OR REPLACE FUNCTION evergreen.ranked_volumes ( 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[] ) RETURNS TABLE (id BIGINT, name TEXT, label_sortkey TEXT, rank BIGINT) AS $$ SELECT * FROM evergreen.ranked_volumes(ARRAY[$1],$2,$3,$4,$5,$6,$7) $$ LANGUAGE SQL STABLE; CREATE OR REPLACE FUNCTION evergreen.located_uris ( bibid BIGINT[], ouid INT, pref_lib INT DEFAULT NULL ) RETURNS TABLE (id BIGINT, name TEXT, label_sortkey TEXT, rank INT) AS $$ WITH all_orgs AS (SELECT COALESCE( enabled, FALSE ) AS flag FROM config.global_flag WHERE name = 'opac.located_uri.act_as_copy') SELECT DISTINCT ON (id) * FROM ( SELECT acn.id, COALESCE(aou.name,aoud.name), acn.label_sortkey, evergreen.rank_ou(aou.id, $2, $3) AS pref_ou FROM asset.call_number acn INNER JOIN asset.uri_call_number_map auricnm ON acn.id = auricnm.call_number INNER JOIN asset.uri auri ON auri.id = auricnm.uri LEFT JOIN actor.org_unit_ancestors( COALESCE($3, $2) ) aou ON (acn.owning_lib = aou.id) LEFT JOIN actor.org_unit_descendants( COALESCE($3, $2) ) aoud ON (acn.owning_lib = aoud.id), all_orgs WHERE acn.record = ANY ($1) AND acn.deleted IS FALSE AND auri.active IS TRUE AND ((NOT all_orgs.flag AND aou.id IS NOT NULL) OR COALESCE(aou.id,aoud.id) IS NOT NULL) UNION SELECT acn.id, COALESCE(aou.name,aoud.name) AS name, acn.label_sortkey, evergreen.rank_ou(aou.id, $2, $3) AS pref_ou FROM asset.call_number acn INNER JOIN asset.uri_call_number_map auricnm ON acn.id = auricnm.call_number INNER JOIN asset.uri auri ON auri.id = auricnm.uri LEFT JOIN actor.org_unit_ancestors( $2 ) aou ON (acn.owning_lib = aou.id) LEFT JOIN actor.org_unit_descendants( $2 ) aoud ON (acn.owning_lib = aoud.id), all_orgs WHERE acn.record = ANY ($1) AND acn.deleted IS FALSE AND auri.active IS TRUE AND ((NOT all_orgs.flag AND aou.id IS NOT NULL) OR COALESCE(aou.id,aoud.id) IS NOT NULL))x ORDER BY id, pref_ou DESC; $$ LANGUAGE SQL STABLE; CREATE OR REPLACE FUNCTION evergreen.located_uris ( bibid BIGINT, ouid INT, pref_lib INT DEFAULT NULL) RETURNS TABLE (id BIGINT, name TEXT, label_sortkey TEXT, rank INT) AS $$ SELECT * FROM evergreen.located_uris(ARRAY[$1],$2,$3) $$ LANGUAGE SQL STABLE; CREATE OR REPLACE FUNCTION unapi.mmr_holdings_xml ( mid 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 ('mmr' = ANY ($5)) THEN 'tag:open-ils.org:U2@mmr/' || $1 || '/' || $3 ELSE NULL END AS id, (SELECT metarecord_has_holdable_copy FROM asset.metarecord_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_metarecord_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_metarecord_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_metarecord_copy_count($9, $1) ORDER BY 1 )x) ), -- XXX monograph_parts and foreign_copies are skipped in MRs ... put them back some day? 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((SELECT ARRAY_AGG(source) FROM metabib.metarecord_source_map WHERE metarecord = $1), $2, $4, $6, $7, $9, $5) 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), uris.rank, name, label_sortkey FROM evergreen.located_uris((SELECT ARRAY_AGG(source) FROM metabib.metarecord_source_map WHERE metarecord = $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 IN (SELECT source FROM metabib.metarecord_source_map WHERE metarecord = $1) )x) ) ELSE NULL END ); $F$ LANGUAGE SQL STABLE; CREATE OR REPLACE FUNCTION unapi.mmr ( obj_id BIGINT, format TEXT, ename TEXT, includes TEXT[], org TEXT, depth INT DEFAULT NULL, slimit HSTORE DEFAULT NULL, soffset HSTORE DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE, pref_lib INT DEFAULT NULL ) RETURNS XML AS $F$ DECLARE mmrec metabib.metarecord%ROWTYPE; leadrec biblio.record_entry%ROWTYPE; subrec biblio.record_entry%ROWTYPE; layout unapi.bre_output_layout%ROWTYPE; xfrm config.xml_transform%ROWTYPE; ouid INT; xml_buf TEXT; -- growing XML document tmp_xml TEXT; -- single-use XML string xml_frag TEXT; -- single-use XML fragment top_el TEXT; output XML; hxml XML; axml XML; subxml XML; -- subordinate records elements sub_xpath TEXT; parts TEXT[]; BEGIN -- xpath for extracting bre.marc values from subordinate records -- so they may be appended to the MARC of the master record prior -- to XSLT processing. -- subjects, isbn, issn, upc -- anything else? sub_xpath := '//*[starts-with(@tag, "6") or @tag="020" or @tag="022" or @tag="024"]'; IF org = '-' OR org IS NULL THEN SELECT shortname INTO org FROM evergreen.org_top(); END IF; SELECT id INTO ouid FROM actor.org_unit WHERE shortname = org; IF ouid IS NULL THEN RETURN NULL::XML; END IF; SELECT INTO mmrec * FROM metabib.metarecord WHERE id = obj_id; IF NOT FOUND THEN RETURN NULL::XML; END IF; -- TODO: aggregate holdings from constituent records IF format = 'holdings_xml' THEN -- the special case output := unapi.mmr_holdings_xml( obj_id, ouid, org, depth, evergreen.array_remove_item_by_value(includes,'holdings_xml'), slimit, soffset, include_xmlns); RETURN output; END IF; SELECT * INTO layout FROM unapi.bre_output_layout WHERE name = format; IF layout.name IS NULL THEN RETURN NULL::XML; END IF; SELECT * INTO xfrm FROM config.xml_transform WHERE name = layout.transform; SELECT INTO leadrec * FROM biblio.record_entry WHERE id = mmrec.master_record; -- Grab distinct MVF for all records if requested IF ('mra' = ANY (includes)) THEN axml := unapi.mmr_mra(obj_id,NULL,NULL,NULL,org,depth,NULL,NULL,TRUE,pref_lib); ELSE axml := NULL::XML; END IF; xml_buf = leadrec.marc; hxml := NULL::XML; IF ('holdings_xml' = ANY (includes)) THEN hxml := unapi.mmr_holdings_xml( obj_id, ouid, org, depth, evergreen.array_remove_item_by_value(includes,'holdings_xml'), slimit, soffset, include_xmlns, pref_lib); END IF; subxml := NULL::XML; parts := '{}'::TEXT[]; FOR subrec IN SELECT bre.* FROM biblio.record_entry bre JOIN metabib.metarecord_source_map mmsm ON (mmsm.source = bre.id) JOIN metabib.metarecord mmr ON (mmr.id = mmsm.metarecord) WHERE mmr.id = obj_id ORDER BY CASE WHEN bre.id = mmr.master_record THEN 0 ELSE bre.id END LIMIT COALESCE((slimit->'bre')::INT, 5) LOOP IF subrec.id = leadrec.id THEN CONTINUE; END IF; -- Append choice data from the the non-lead records to the -- the lead record document parts := parts || xpath(sub_xpath, subrec.marc::XML)::TEXT[]; END LOOP; SELECT ARRAY_TO_STRING( ARRAY_AGG( DISTINCT p ), '' )::XML INTO subxml FROM UNNEST(parts) p; -- append data from the subordinate records to the -- main record document before applying the XSLT IF subxml IS NOT NULL THEN xml_buf := REGEXP_REPLACE(xml_buf, '(.*?)$', subxml || '' || E'\\1'); END IF; IF format = 'marcxml' THEN -- If we're not using the prefixed namespace in -- this record, then remove all declarations of it IF xml_buf !~ E' mods) top_el := REGEXP_REPLACE(xml_buf, E'^.*?<((?:\\S+:)?' || layout.holdings_element || ').*$', E'\\1'); IF axml IS NOT NULL THEN xml_buf := REGEXP_REPLACE(xml_buf, '(.*?)$', axml || '\\1'); END IF; IF hxml IS NOT NULL THEN xml_buf := REGEXP_REPLACE(xml_buf, '(.*?)$', hxml || '\\1'); END IF; IF ('mmr.unapi' = ANY (includes)) THEN output := REGEXP_REPLACE( xml_buf, '(.*?)', XMLELEMENT( name abbr, XMLATTRIBUTES( 'http://www.w3.org/1999/xhtml' AS xmlns, 'unapi-id' AS class, 'tag:open-ils.org:U2@mmr/' || obj_id || '/' || org AS title ) )::TEXT || '\\1' ); ELSE output := xml_buf; END IF; -- remove ignorable whitesace output := REGEXP_REPLACE(output::TEXT,E'>\\s+<','><','gs')::XML; RETURN output; END; $F$ LANGUAGE PLPGSQL STABLE; COMMIT;