2 * Copyright (C) 2014 Equinox Software, Inc.
3 * Mike Rylander <miker@esilibrary.com>
5 * This program is free software; you can redistribute it and/or
6 * modify it under the terms of the GNU General Public License
7 * as published by the Free Software Foundation; either version 2
8 * of the License, or (at your option) any later version.
10 * This program is distributed in the hope that it will be useful,
11 * but WITHOUT ANY WARRANTY; without even the implied warranty of
12 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
13 * GNU General Public License for more details.
20 SELECT evergreen.upgrade_deps_block_check('0857', :eg_version);
22 INSERT INTO config.global_flag (name, enabled, label)
24 'opac.located_uri.act_as_copy',
27 'opac.located_uri.act_as_copy',
28 'When enabled, Located URIs will provide visiblity behavior identical to copies.',
34 CREATE OR REPLACE FUNCTION evergreen.located_uris (
37 pref_lib INT DEFAULT NULL
38 ) RETURNS TABLE (id BIGINT, name TEXT, label_sortkey TEXT, rank INT) AS $$
39 WITH all_orgs AS (SELECT COALESCE( enabled, FALSE ) AS flag FROM config.global_flag WHERE name = 'opac.located_uri.act_as_copy')
40 SELECT DISTINCT ON (id) * FROM (
41 SELECT acn.id, COALESCE(aou.name,aoud.name), acn.label_sortkey, evergreen.rank_ou(aou.id, $2, $3) AS pref_ou
42 FROM asset.call_number acn
43 INNER JOIN asset.uri_call_number_map auricnm ON acn.id = auricnm.call_number
44 INNER JOIN asset.uri auri ON auri.id = auricnm.uri
45 LEFT JOIN actor.org_unit_ancestors( COALESCE($3, $2) ) aou ON (acn.owning_lib = aou.id)
46 LEFT JOIN actor.org_unit_descendants( COALESCE($3, $2) ) aoud ON (acn.owning_lib = aoud.id),
49 AND acn.deleted IS FALSE
50 AND auri.active IS TRUE
51 AND ((NOT all_orgs.flag AND aou.id IS NOT NULL) OR COALESCE(aou.id,aoud.id) IS NOT NULL)
53 SELECT acn.id, COALESCE(aou.name,aoud.name) AS name, acn.label_sortkey, evergreen.rank_ou(aou.id, $2, $3) AS pref_ou
54 FROM asset.call_number acn
55 INNER JOIN asset.uri_call_number_map auricnm ON acn.id = auricnm.call_number
56 INNER JOIN asset.uri auri ON auri.id = auricnm.uri
57 LEFT JOIN actor.org_unit_ancestors( $2 ) aou ON (acn.owning_lib = aou.id)
58 LEFT JOIN actor.org_unit_descendants( $2 ) aoud ON (acn.owning_lib = aoud.id),
61 AND acn.deleted IS FALSE
62 AND auri.active IS TRUE
63 AND ((NOT all_orgs.flag AND aou.id IS NOT NULL) OR COALESCE(aou.id,aoud.id) IS NOT NULL))x
64 ORDER BY id, pref_ou DESC;
68 CREATE OR REPLACE FUNCTION search.query_parser_fts (
74 param_locations INT[],
81 param_pref_ou INT DEFAULT NULL
82 ) RETURNS SETOF search.search_result AS $func$
85 current_res search.search_result%ROWTYPE;
86 search_org_list INT[];
96 core_cursor REFCURSOR;
100 check_count INT := 0;
101 deleted_count INT := 0;
102 visible_count INT := 0;
103 excluded_count INT := 0;
108 check_limit := COALESCE( param_check, 1000 );
109 core_limit := COALESCE( param_limit, 25000 );
110 core_offset := COALESCE( param_offset, 0 );
112 SELECT COALESCE( enabled, FALSE ) INTO luri_as_copy FROM config.global_flag WHERE name = 'opac.located_uri.act_as_copy';
114 -- core_skip_chk := COALESCE( param_skip_chk, 1 );
116 IF param_search_ou > 0 THEN
117 IF param_depth IS NOT NULL THEN
118 SELECT ARRAY_AGG(distinct id) INTO search_org_list FROM actor.org_unit_descendants( param_search_ou, param_depth );
120 SELECT ARRAY_AGG(distinct id) INTO search_org_list FROM actor.org_unit_descendants( param_search_ou );
124 SELECT ARRAY_AGG(distinct id) INTO luri_org_list FROM actor.org_unit_full_path( param_search_ou );
126 SELECT ARRAY_AGG(distinct id) INTO luri_org_list FROM actor.org_unit_ancestors( param_search_ou );
129 ELSIF param_search_ou < 0 THEN
130 SELECT ARRAY_AGG(distinct org_unit) INTO search_org_list FROM actor.org_lasso_map WHERE lasso = -param_search_ou;
132 FOR tmp_int IN SELECT * FROM UNNEST(search_org_list) LOOP
135 SELECT ARRAY_AGG(distinct id) INTO tmp_int_list FROM actor.org_unit_full_path( tmp_int );
137 SELECT ARRAY_AGG(distinct id) INTO tmp_int_list FROM actor.org_unit_ancestors( tmp_int );
140 luri_org_list := luri_org_list || tmp_int_list;
143 SELECT ARRAY_AGG(DISTINCT x.id) INTO luri_org_list FROM UNNEST(luri_org_list) x(id);
145 ELSIF param_search_ou = 0 THEN
146 -- reserved for user lassos (ou_buckets/type='lasso') with ID passed in depth ... hack? sure.
149 IF param_pref_ou IS NOT NULL THEN
151 SELECT ARRAY_AGG(distinct id) INTO tmp_int_list FROM actor.org_unit_full_path( param_pref_ou );
153 SELECT ARRAY_AGG(distinct id) INTO tmp_int_list FROM actor.org_unit_ancestors( param_pref_ou );
156 luri_org_list := luri_org_list || tmp_int_list;
159 OPEN core_cursor FOR EXECUTE param_query;
163 FETCH core_cursor INTO core_result;
165 EXIT WHEN total_count >= core_limit;
167 total_count := total_count + 1;
169 CONTINUE WHEN total_count NOT BETWEEN core_offset + 1 AND check_limit + core_offset;
171 check_count := check_count + 1;
173 IF NOT deleted_search THEN
175 PERFORM 1 FROM biblio.record_entry b WHERE NOT b.deleted AND b.id IN ( SELECT * FROM unnest( core_result.records ) );
177 -- RAISE NOTICE ' % were all deleted ... ', core_result.records;
178 deleted_count := deleted_count + 1;
183 FROM biblio.record_entry b
184 JOIN config.bib_source s ON (b.source = s.id)
186 AND b.id IN ( SELECT * FROM unnest( core_result.records ) );
189 -- RAISE NOTICE ' % were all transcendant ... ', core_result.records;
190 visible_count := visible_count + 1;
192 current_res.id = core_result.id;
193 current_res.rel = core_result.rel;
197 SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
201 current_res.record = core_result.records[1];
203 current_res.record = NULL;
206 RETURN NEXT current_res;
212 FROM asset.call_number cn
213 JOIN asset.uri_call_number_map map ON (map.call_number = cn.id)
214 JOIN asset.uri uri ON (map.uri = uri.id)
216 AND cn.label = '##URI##'
218 AND ( param_locations IS NULL OR array_upper(param_locations, 1) IS NULL )
219 AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
220 AND cn.owning_lib IN ( SELECT * FROM unnest( luri_org_list ) )
224 -- RAISE NOTICE ' % have at least one URI ... ', core_result.records;
225 visible_count := visible_count + 1;
227 current_res.id = core_result.id;
228 current_res.rel = core_result.rel;
232 SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
236 current_res.record = core_result.records[1];
238 current_res.record = NULL;
241 RETURN NEXT current_res;
246 IF param_statuses IS NOT NULL AND array_upper(param_statuses, 1) > 0 THEN
249 FROM asset.call_number cn
250 JOIN asset.copy cp ON (cp.call_number = cn.id)
253 AND cp.status IN ( SELECT * FROM unnest( param_statuses ) )
254 AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
255 AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
260 FROM biblio.peer_bib_copy_map pr
261 JOIN asset.copy cp ON (cp.id = pr.target_copy)
263 AND cp.status IN ( SELECT * FROM unnest( param_statuses ) )
264 AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) )
265 AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
269 -- RAISE NOTICE ' % and multi-home linked records were all status-excluded ... ', core_result.records;
270 excluded_count := excluded_count + 1;
277 IF param_locations IS NOT NULL AND array_upper(param_locations, 1) > 0 THEN
280 FROM asset.call_number cn
281 JOIN asset.copy cp ON (cp.call_number = cn.id)
284 AND cp.location IN ( SELECT * FROM unnest( param_locations ) )
285 AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
286 AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
291 FROM biblio.peer_bib_copy_map pr
292 JOIN asset.copy cp ON (cp.id = pr.target_copy)
294 AND cp.location IN ( SELECT * FROM unnest( param_locations ) )
295 AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) )
296 AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
300 -- RAISE NOTICE ' % and multi-home linked records were all copy_location-excluded ... ', core_result.records;
301 excluded_count := excluded_count + 1;
308 IF staff IS NULL OR NOT staff THEN
311 FROM asset.opac_visible_copies
312 WHERE circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
313 AND record IN ( SELECT * FROM unnest( core_result.records ) )
318 FROM biblio.peer_bib_copy_map pr
319 JOIN asset.opac_visible_copies cp ON (cp.copy_id = pr.target_copy)
320 WHERE cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
321 AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) )
326 -- RAISE NOTICE ' % and multi-home linked records were all visibility-excluded ... ', core_result.records;
327 excluded_count := excluded_count + 1;
335 FROM asset.call_number cn
336 JOIN asset.copy cp ON (cp.call_number = cn.id)
339 AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
340 AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
346 FROM biblio.peer_bib_copy_map pr
347 JOIN asset.copy cp ON (cp.id = pr.target_copy)
349 AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
350 AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) )
356 FROM asset.call_number cn
357 JOIN asset.copy cp ON (cp.call_number = cn.id)
358 WHERE cn.record IN ( SELECT * FROM unnest( core_result.records ) )
363 -- RAISE NOTICE ' % and multi-home linked records were all visibility-excluded ... ', core_result.records;
364 excluded_count := excluded_count + 1;
375 visible_count := visible_count + 1;
377 current_res.id = core_result.id;
378 current_res.rel = core_result.rel;
382 SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
386 current_res.record = core_result.records[1];
388 current_res.record = NULL;
391 RETURN NEXT current_res;
393 IF visible_count % 1000 = 0 THEN
394 -- RAISE NOTICE ' % visible so far ... ', visible_count;
399 current_res.id = NULL;
400 current_res.rel = NULL;
401 current_res.record = NULL;
402 current_res.total = total_count;
403 current_res.checked = check_count;
404 current_res.deleted = deleted_count;
405 current_res.visible = visible_count;
406 current_res.excluded = excluded_count;
410 RETURN NEXT current_res;
413 $func$ LANGUAGE PLPGSQL;
415 CREATE OR REPLACE FUNCTION unapi.holdings_xml (
419 depth INT DEFAULT NULL,
420 includes TEXT[] DEFAULT NULL::TEXT[],
421 slimit HSTORE DEFAULT NULL,
422 soffset HSTORE DEFAULT NULL,
423 include_xmlns BOOL DEFAULT TRUE,
424 pref_lib INT DEFAULT NULL
430 CASE WHEN $8 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
431 CASE WHEN ('bre' = ANY ($5)) THEN 'tag:open-ils.org:U2@bre/' || $1 || '/' || $3 ELSE NULL END AS id,
432 (SELECT record_has_holdable_copy FROM asset.record_has_holdable_copy($1)) AS has_holdable
436 (SELECT XMLAGG(XMLELEMENT::XML) FROM (
439 XMLATTRIBUTES('public' as type, depth, org_unit, coalesce(transcendant,0) as transcendant, available, visible as count, unshadow)
441 FROM asset.opac_ou_record_copy_count($2, $1)
445 XMLATTRIBUTES('staff' as type, depth, org_unit, coalesce(transcendant,0) as transcendant, available, visible as count, unshadow)
447 FROM asset.staff_ou_record_copy_count($2, $1)
451 XMLATTRIBUTES('pref_lib' as type, depth, org_unit, coalesce(transcendant,0) as transcendant, available, visible as count, unshadow)
453 FROM asset.opac_ou_record_copy_count($9, $1)
458 WHEN ('bmp' = ANY ($5)) THEN
460 name monograph_parts,
461 (SELECT XMLAGG(bmp) FROM (
462 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)
463 FROM biblio.monograph_part
471 (SELECT XMLAGG(acn ORDER BY rank, name, label_sortkey) FROM (
473 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
474 FROM evergreen.ranked_volumes($1, $2, $4, $6, $7, $9, $5) AS y
477 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
478 FROM evergreen.located_uris($1, $2, $9) AS uris
481 CASE WHEN ('ssub' = ANY ($5)) THEN
484 (SELECT XMLAGG(ssub) FROM (
485 SELECT unapi.ssub(id,'xml','subscription','{}'::TEXT[], $3, $4, $6, $7, FALSE)
486 FROM serial.subscription
487 WHERE record_entry = $1
491 CASE WHEN ('acp' = ANY ($5)) THEN
494 (SELECT XMLAGG(acp) FROM (
495 SELECT unapi.acp(p.target_copy,'xml','copy',evergreen.array_remove_item_by_value($5,'acp'), $3, $4, $6, $7, FALSE)
496 FROM biblio.peer_bib_copy_map p
497 JOIN asset.copy c ON (p.target_copy = c.id)
498 WHERE NOT c.deleted AND p.peer_record = $1
499 LIMIT ($6 -> 'acp')::INT
500 OFFSET ($7 -> 'acp')::INT
505 $F$ LANGUAGE SQL STABLE;