3 SELECT evergreen.upgrade_deps_block_check('0866', :eg_version);
5 DROP FUNCTION asset.record_has_holdable_copy (BIGINT);
6 CREATE FUNCTION asset.record_has_holdable_copy ( rid BIGINT, ou INT DEFAULT NULL) RETURNS BOOL AS $f$
11 JOIN asset.call_number acn ON acp.call_number = acn.id
12 JOIN asset.copy_location acpl ON acp.location = acpl.id
13 JOIN config.copy_status ccs ON acp.status = ccs.id
16 AND acp.holdable = true
17 AND acpl.holdable = true
18 AND ccs.holdable = true
19 AND acp.deleted = false
20 AND acp.circ_lib IN (SELECT id FROM actor.org_unit_descendants(COALESCE($2,(SELECT id FROM evergreen.org_top()))))
29 DROP FUNCTION asset.metarecord_has_holdable_copy (BIGINT);
30 CREATE FUNCTION asset.metarecord_has_holdable_copy ( rid BIGINT, ou INT DEFAULT NULL) 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
45 AND acp.circ_lib IN (SELECT id FROM actor.org_unit_descendants(COALESCE($2,(SELECT id FROM evergreen.org_top()))))
54 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$
59 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;
61 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
66 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
70 actor.org_unit_descendants(ans.id) d
71 JOIN asset.opac_visible_copies av ON (av.circ_lib = d.id)
72 JOIN asset.copy cp ON (cp.id = av.copy_id)
73 JOIN metabib.metarecord_source_map m ON (m.metarecord = rid AND m.source = av.record)
77 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
86 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$
91 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;
93 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
98 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
102 actor.org_unit_descendants(ans.id) d
103 JOIN asset.opac_visible_copies av ON (av.circ_lib = d.id)
104 JOIN asset.copy cp ON (cp.id = av.copy_id)
105 JOIN metabib.metarecord_source_map m ON (m.metarecord = rid AND m.source = av.record)
109 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
116 $f$ LANGUAGE PLPGSQL;
118 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$
123 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;
125 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
130 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
134 actor.org_unit_descendants(ans.id) d
135 JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
136 JOIN asset.call_number cn ON (cn.id = cp.call_number AND NOT cn.deleted)
137 JOIN metabib.metarecord_source_map m ON (m.metarecord = rid AND m.source = cn.record)
141 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
148 $f$ LANGUAGE PLPGSQL;
150 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$
155 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;
157 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
162 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
166 actor.org_unit_descendants(ans.id) d
167 JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
168 JOIN asset.call_number cn ON (cn.id = cp.call_number AND NOT cn.deleted)
169 JOIN metabib.metarecord_source_map m ON (m.metarecord = rid AND m.source = cn.record)
173 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
180 $f$ LANGUAGE PLPGSQL;
182 CREATE OR REPLACE FUNCTION unapi.mmr_mra (
188 depth INT DEFAULT NULL,
189 slimit HSTORE DEFAULT NULL,
190 soffset HSTORE DEFAULT NULL,
191 include_xmlns BOOL DEFAULT TRUE,
192 pref_lib INT DEFAULT NULL
197 CASE WHEN $9 THEN 'http://open-ils.org/spec/indexing/v1' ELSE NULL END AS xmlns,
198 'tag:open-ils.org:U2@mmr/' || $1 AS metarecord
200 (SELECT XMLAGG(foo.y)
202 SELECT DISTINCT ON (COALESCE(cvm.id,uvm.id))
203 COALESCE(cvm.id,uvm.id),
208 cvm.value AS "coded-value",
217 FROM metabib.record_attr_flat mra
218 JOIN config.record_attr_definition rad ON (mra.attr = rad.name)
219 LEFT JOIN config.coded_value_map cvm ON (cvm.ctype = mra.attr AND code = mra.value)
220 LEFT JOIN metabib.uncontrolled_record_attr_value uvm ON (uvm.attr = mra.attr AND uvm.value = mra.value)
222 WITH aou AS (SELECT COALESCE(id, (evergreen.org_top()).id) AS id
223 FROM actor.org_unit WHERE shortname = $5 LIMIT 1)
225 FROM metabib.metarecord_source_map, aou
226 WHERE metarecord = $1 AND (
228 SELECT 1 FROM asset.opac_visible_copies
229 WHERE record = source AND circ_lib IN (
230 SELECT id FROM actor.org_unit_descendants(aou.id, $6))
233 OR EXISTS (SELECT 1 FROM located_uris(source, aou.id, $10) LIMIT 1)
240 $F$ LANGUAGE SQL STABLE;
242 CREATE OR REPLACE FUNCTION evergreen.ranked_volumes(
245 depth INT DEFAULT NULL,
246 slimit HSTORE DEFAULT NULL,
247 soffset HSTORE DEFAULT NULL,
248 pref_lib INT DEFAULT NULL,
249 includes TEXT[] DEFAULT NULL::TEXT[]
250 ) RETURNS TABLE (id BIGINT, name TEXT, label_sortkey TEXT, rank BIGINT) AS $$
251 SELECT ua.id, ua.name, ua.label_sortkey, MIN(ua.rank) AS rank FROM (
252 SELECT acn.id, aou.name, acn.label_sortkey,
253 evergreen.rank_ou(aou.id, $2, $6), evergreen.rank_cp_status(acp.status),
255 FROM asset.call_number acn
256 JOIN asset.copy acp ON (acn.id = acp.call_number)
257 JOIN actor.org_unit_descendants( $2, COALESCE(
260 FROM actor.org_unit_type aout
261 INNER JOIN actor.org_unit ou ON ou_type = aout.id
264 ) AS aou ON (acp.circ_lib = aou.id)
265 WHERE acn.record = ANY ($1)
266 AND acn.deleted IS FALSE
267 AND acp.deleted IS FALSE
268 AND CASE WHEN ('exclude_invisible_acn' = ANY($7)) THEN
271 FROM asset.opac_visible_copies
272 WHERE copy_id = acp.id AND record = acn.record
274 GROUP BY acn.id, acp.status, aou.name, acn.label_sortkey, aou.id
276 ORDER BY evergreen.rank_ou(aou.id, $2, $6), evergreen.rank_cp_status(acp.status)
279 GROUP BY ua.id, ua.name, ua.label_sortkey
280 ORDER BY rank, ua.name, ua.label_sortkey
281 LIMIT ($4 -> 'acn')::INT
282 OFFSET ($5 -> 'acn')::INT;
286 CREATE OR REPLACE FUNCTION evergreen.ranked_volumes
287 ( 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[] )
288 RETURNS TABLE (id BIGINT, name TEXT, label_sortkey TEXT, rank BIGINT)
289 AS $$ SELECT * FROM evergreen.ranked_volumes(ARRAY[$1],$2,$3,$4,$5,$6,$7) $$ LANGUAGE SQL STABLE;
292 CREATE OR REPLACE FUNCTION evergreen.located_uris (
295 pref_lib INT DEFAULT NULL
296 ) RETURNS TABLE (id BIGINT, name TEXT, label_sortkey TEXT, rank INT) AS $$
297 WITH all_orgs AS (SELECT COALESCE( enabled, FALSE ) AS flag FROM config.global_flag WHERE name = 'opac.located_uri.act_as_copy')
298 SELECT DISTINCT ON (id) * FROM (
299 SELECT acn.id, COALESCE(aou.name,aoud.name), acn.label_sortkey, evergreen.rank_ou(aou.id, $2, $3) AS pref_ou
300 FROM asset.call_number acn
301 INNER JOIN asset.uri_call_number_map auricnm ON acn.id = auricnm.call_number
302 INNER JOIN asset.uri auri ON auri.id = auricnm.uri
303 LEFT JOIN actor.org_unit_ancestors( COALESCE($3, $2) ) aou ON (acn.owning_lib = aou.id)
304 LEFT JOIN actor.org_unit_descendants( COALESCE($3, $2) ) aoud ON (acn.owning_lib = aoud.id),
306 WHERE acn.record = ANY ($1)
307 AND acn.deleted IS FALSE
308 AND auri.active IS TRUE
309 AND ((NOT all_orgs.flag AND aou.id IS NOT NULL) OR COALESCE(aou.id,aoud.id) IS NOT NULL)
311 SELECT acn.id, COALESCE(aou.name,aoud.name) AS name, acn.label_sortkey, evergreen.rank_ou(aou.id, $2, $3) AS pref_ou
312 FROM asset.call_number acn
313 INNER JOIN asset.uri_call_number_map auricnm ON acn.id = auricnm.call_number
314 INNER JOIN asset.uri auri ON auri.id = auricnm.uri
315 LEFT JOIN actor.org_unit_ancestors( $2 ) aou ON (acn.owning_lib = aou.id)
316 LEFT JOIN actor.org_unit_descendants( $2 ) aoud ON (acn.owning_lib = aoud.id),
318 WHERE acn.record = ANY ($1)
319 AND acn.deleted IS FALSE
320 AND auri.active IS TRUE
321 AND ((NOT all_orgs.flag AND aou.id IS NOT NULL) OR COALESCE(aou.id,aoud.id) IS NOT NULL))x
322 ORDER BY id, pref_ou DESC;
326 CREATE OR REPLACE FUNCTION evergreen.located_uris ( bibid BIGINT, ouid INT, pref_lib INT DEFAULT NULL)
327 RETURNS TABLE (id BIGINT, name TEXT, label_sortkey TEXT, rank INT)
328 AS $$ SELECT * FROM evergreen.located_uris(ARRAY[$1],$2,$3) $$ LANGUAGE SQL STABLE;
331 CREATE OR REPLACE FUNCTION unapi.mmr_holdings_xml (
335 depth INT DEFAULT NULL,
336 includes TEXT[] DEFAULT NULL::TEXT[],
337 slimit HSTORE DEFAULT NULL,
338 soffset HSTORE DEFAULT NULL,
339 include_xmlns BOOL DEFAULT TRUE,
340 pref_lib INT DEFAULT NULL
346 CASE WHEN $8 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
347 CASE WHEN ('mmr' = ANY ($5)) THEN 'tag:open-ils.org:U2@mmr/' || $1 || '/' || $3 ELSE NULL END AS id,
348 (SELECT metarecord_has_holdable_copy FROM asset.metarecord_has_holdable_copy($1)) AS has_holdable
352 (SELECT XMLAGG(XMLELEMENT::XML) FROM (
355 XMLATTRIBUTES('public' as type, depth, org_unit, coalesce(transcendant,0) as transcendant, available, visible as count, unshadow)
357 FROM asset.opac_ou_metarecord_copy_count($2, $1)
361 XMLATTRIBUTES('staff' as type, depth, org_unit, coalesce(transcendant,0) as transcendant, available, visible as count, unshadow)
363 FROM asset.staff_ou_metarecord_copy_count($2, $1)
367 XMLATTRIBUTES('pref_lib' as type, depth, org_unit, coalesce(transcendant,0) as transcendant, available, visible as count, unshadow)
369 FROM asset.opac_ou_metarecord_copy_count($9, $1)
373 -- XXX monograph_parts and foreign_copies are skipped in MRs ... put them back some day?
376 (SELECT XMLAGG(acn ORDER BY rank, name, label_sortkey) FROM (
378 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
379 FROM evergreen.ranked_volumes((SELECT ARRAY_AGG(source) FROM metabib.metarecord_source_map WHERE metarecord = $1), $2, $4, $6, $7, $9, $5) AS y
382 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
383 FROM evergreen.located_uris((SELECT ARRAY_AGG(source) FROM metabib.metarecord_source_map WHERE metarecord = $1), $2, $9) AS uris
386 CASE WHEN ('ssub' = ANY ($5)) THEN
389 (SELECT XMLAGG(ssub) FROM (
390 SELECT unapi.ssub(id,'xml','subscription','{}'::TEXT[], $3, $4, $6, $7, FALSE)
391 FROM serial.subscription
392 WHERE record_entry IN (SELECT source FROM metabib.metarecord_source_map WHERE metarecord = $1)
397 $F$ LANGUAGE SQL STABLE;
399 CREATE OR REPLACE FUNCTION unapi.mmr (
405 depth INT DEFAULT NULL,
406 slimit HSTORE DEFAULT NULL,
407 soffset HSTORE DEFAULT NULL,
408 include_xmlns BOOL DEFAULT TRUE,
409 pref_lib INT DEFAULT NULL
413 mmrec metabib.metarecord%ROWTYPE;
414 leadrec biblio.record_entry%ROWTYPE;
415 subrec biblio.record_entry%ROWTYPE;
416 layout unapi.bre_output_layout%ROWTYPE;
417 xfrm config.xml_transform%ROWTYPE;
419 xml_buf TEXT; -- growing XML document
420 tmp_xml TEXT; -- single-use XML string
421 xml_frag TEXT; -- single-use XML fragment
426 subxml XML; -- subordinate records elements
431 -- xpath for extracting bre.marc values from subordinate records
432 -- so they may be appended to the MARC of the master record prior
433 -- to XSLT processing.
434 -- subjects, isbn, issn, upc -- anything else?
436 '//*[starts-with(@tag, "6") or @tag="020" or @tag="022" or @tag="024"]';
438 IF org = '-' OR org IS NULL THEN
439 SELECT shortname INTO org FROM evergreen.org_top();
442 SELECT id INTO ouid FROM actor.org_unit WHERE shortname = org;
448 SELECT INTO mmrec * FROM metabib.metarecord WHERE id = obj_id;
453 -- TODO: aggregate holdings from constituent records
454 IF format = 'holdings_xml' THEN -- the special case
455 output := unapi.mmr_holdings_xml(
456 obj_id, ouid, org, depth,
457 evergreen.array_remove_item_by_value(includes,'holdings_xml'),
458 slimit, soffset, include_xmlns);
462 SELECT * INTO layout FROM unapi.bre_output_layout WHERE name = format;
464 IF layout.name IS NULL THEN
468 SELECT * INTO xfrm FROM config.xml_transform WHERE name = layout.transform;
470 SELECT INTO leadrec * FROM biblio.record_entry WHERE id = mmrec.master_record;
472 -- Grab distinct MVF for all records if requested
473 IF ('mra' = ANY (includes)) THEN
474 axml := unapi.mmr_mra(obj_id,NULL,NULL,NULL,org,depth,NULL,NULL,TRUE,pref_lib);
479 xml_buf = leadrec.marc;
482 IF ('holdings_xml' = ANY (includes)) THEN
483 hxml := unapi.mmr_holdings_xml(
484 obj_id, ouid, org, depth,
485 evergreen.array_remove_item_by_value(includes,'holdings_xml'),
486 slimit, soffset, include_xmlns, pref_lib);
490 parts := '{}'::TEXT[];
491 FOR subrec IN SELECT bre.* FROM biblio.record_entry bre
492 JOIN metabib.metarecord_source_map mmsm ON (mmsm.source = bre.id)
493 JOIN metabib.metarecord mmr ON (mmr.id = mmsm.metarecord)
494 WHERE mmr.id = obj_id
495 ORDER BY CASE WHEN bre.id = mmr.master_record THEN 0 ELSE bre.id END
496 LIMIT COALESCE((slimit->'bre')::INT, 5) LOOP
498 IF subrec.id = leadrec.id THEN CONTINUE; END IF;
499 -- Append choice data from the the non-lead records to the
500 -- the lead record document
502 parts := parts || xpath(sub_xpath, subrec.marc::XML)::TEXT[];
505 SELECT ARRAY_TO_STRING( ARRAY_AGG( DISTINCT p ), '' )::XML INTO subxml FROM UNNEST(parts) p;
507 -- append data from the subordinate records to the
508 -- main record document before applying the XSLT
510 IF subxml IS NOT NULL THEN
511 xml_buf := REGEXP_REPLACE(xml_buf,
512 '</record>(.*?)$', subxml || '</record>' || E'\\1');
515 IF format = 'marcxml' THEN
516 -- If we're not using the prefixed namespace in
517 -- this record, then remove all declarations of it
518 IF xml_buf !~ E'<marc:' THEN
519 xml_buf := REGEXP_REPLACE(xml_buf,
520 ' xmlns:marc="http://www.loc.gov/MARC21/slim"', '', 'g');
523 xml_buf := oils_xslt_process(xml_buf, xfrm.xslt)::XML;
526 -- update top_el to reflect the change in xml_buf, which may
527 -- now be a different type of document (e.g. record -> mods)
528 top_el := REGEXP_REPLACE(xml_buf, E'^.*?<((?:\\S+:)?' ||
529 layout.holdings_element || ').*$', E'\\1');
531 IF axml IS NOT NULL THEN
532 xml_buf := REGEXP_REPLACE(xml_buf,
533 '</' || top_el || '>(.*?)$', axml || '</' || top_el || E'>\\1');
536 IF hxml IS NOT NULL THEN
537 xml_buf := REGEXP_REPLACE(xml_buf,
538 '</' || top_el || '>(.*?)$', hxml || '</' || top_el || E'>\\1');
541 IF ('mmr.unapi' = ANY (includes)) THEN
542 output := REGEXP_REPLACE(
544 '</' || top_el || '>(.*?)',
548 'http://www.w3.org/1999/xhtml' AS xmlns,
550 'tag:open-ils.org:U2@mmr/' || obj_id || '/' || org AS title
552 )::TEXT || '</' || top_el || E'>\\1'
558 -- remove ignorable whitesace
559 output := REGEXP_REPLACE(output::TEXT,E'>\\s+<','><','gs')::XML;
562 $F$ LANGUAGE PLPGSQL STABLE;