3 SELECT evergreen.upgrade_deps_block_check('0912', :eg_version);
5 ALTER TABLE asset.copy_location ADD COLUMN deleted BOOLEAN NOT NULL DEFAULT FALSE;
7 CREATE OR REPLACE RULE protect_copy_location_delete AS
8 ON DELETE TO asset.copy_location DO INSTEAD (
9 UPDATE asset.copy_location SET deleted = TRUE WHERE OLD.id = asset.copy_location.id;
10 UPDATE acq.lineitem_detail SET location = NULL WHERE location = OLD.id;
11 DELETE FROM asset.copy_location_order WHERE location = OLD.id;
12 DELETE FROM asset.copy_location_group_map WHERE location = OLD.id;
13 DELETE FROM config.circ_limit_set_copy_loc_map WHERE copy_loc = OLD.id;
16 ALTER TABLE asset.copy_location DROP CONSTRAINT acl_name_once_per_lib;
17 CREATE UNIQUE INDEX acl_name_once_per_lib ON asset.copy_location (name, owning_lib) WHERE deleted = FALSE OR deleted IS FALSE;
19 CREATE OR REPLACE FUNCTION asset.acp_location_fixer()
22 new_copy_location INT;
24 IF (TG_OP = 'UPDATE') THEN
25 IF NEW.location = OLD.location AND NEW.call_number = OLD.call_number AND NEW.circ_lib = OLD.circ_lib THEN
29 SELECT INTO new_copy_location acpl.id FROM asset.copy_location acpl JOIN actor.org_unit_ancestors_distance((SELECT owning_lib FROM asset.call_number WHERE id = NEW.call_number)) aouad ON acpl.owning_lib = aouad.id WHERE deleted IS FALSE AND name = (SELECT name FROM asset.copy_location WHERE id = NEW.location) ORDER BY distance LIMIT 1;
30 IF new_copy_location IS NULL THEN
31 SELECT INTO new_copy_location acpl.id FROM asset.copy_location acpl JOIN actor.org_unit_ancestors_distance(NEW.circ_lib) aouad ON acpl.owning_lib = aouad.id WHERE deleted IS FALSE AND name = (SELECT name FROM asset.copy_location WHERE id = NEW.location) ORDER BY distance LIMIT 1;
33 IF new_copy_location IS NOT NULL THEN
34 NEW.location = new_copy_location;
40 CREATE OR REPLACE FUNCTION asset.staff_ou_record_copy_count (org INT, rid BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$
45 SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) WHERE src.transcendant AND b.id = rid;
47 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
52 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
53 SUM( CASE WHEN cl.opac_visible AND cp.opac_visible THEN 1 ELSE 0 END),
56 actor.org_unit_descendants(ans.id) d
57 JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
58 JOIN asset.copy_location cl ON (cp.location = cl.id AND NOT cl.deleted)
59 JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number AND NOT cn.deleted)
63 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
72 CREATE OR REPLACE FUNCTION asset.staff_lasso_record_copy_count (i_lasso INT, rid BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$
77 SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) WHERE src.transcendant AND b.id = rid;
79 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
84 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
85 SUM( CASE WHEN cl.opac_visible AND cp.opac_visible THEN 1 ELSE 0 END),
88 actor.org_unit_descendants(ans.id) d
89 JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
90 JOIN asset.copy_location cl ON (cp.location = cl.id AND NOT cl.deleted)
91 JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number AND NOT cn.deleted)
95 RETURN QUERY SELECT -1, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
102 $f$ LANGUAGE PLPGSQL;
104 CREATE OR REPLACE FUNCTION asset.record_has_holdable_copy ( rid BIGINT, ou INT DEFAULT NULL) RETURNS BOOL AS $f$
109 JOIN asset.call_number acn ON acp.call_number = acn.id
110 JOIN asset.copy_location acpl ON acp.location = acpl.id
111 JOIN config.copy_status ccs ON acp.status = ccs.id
114 AND acp.holdable = true
115 AND acpl.holdable = true
116 AND ccs.holdable = true
117 AND acp.deleted = false
118 AND acpl.deleted = false
119 AND acp.circ_lib IN (SELECT id FROM actor.org_unit_descendants(COALESCE($2,(SELECT id FROM evergreen.org_top()))))
126 $f$ LANGUAGE PLPGSQL;
128 CREATE OR REPLACE FUNCTION asset.metarecord_has_holdable_copy ( rid BIGINT, ou INT DEFAULT NULL) RETURNS BOOL AS $f$
133 JOIN asset.call_number acn ON acp.call_number = acn.id
134 JOIN asset.copy_location acpl ON acp.location = acpl.id
135 JOIN config.copy_status ccs ON acp.status = ccs.id
136 JOIN metabib.metarecord_source_map mmsm ON acn.record = mmsm.source
138 mmsm.metarecord = rid
139 AND acp.holdable = true
140 AND acpl.holdable = true
141 AND ccs.holdable = true
142 AND acp.deleted = false
143 AND acpl.deleted = false
144 AND acp.circ_lib IN (SELECT id FROM actor.org_unit_descendants(COALESCE($2,(SELECT id FROM evergreen.org_top()))))
151 $f$ LANGUAGE PLPGSQL;
153 CREATE OR REPLACE FUNCTION asset.refresh_opac_visible_copies_mat_view () RETURNS VOID AS $$
155 TRUNCATE TABLE asset.opac_visible_copies;
157 INSERT INTO asset.opac_visible_copies (copy_id, circ_lib, record)
158 SELECT cp.id, cp.circ_lib, cn.record
160 JOIN asset.call_number cn ON (cn.id = cp.call_number)
161 JOIN actor.org_unit a ON (cp.circ_lib = a.id)
162 JOIN asset.copy_location cl ON (cp.location = cl.id)
163 JOIN config.copy_status cs ON (cp.status = cs.id)
164 JOIN biblio.record_entry b ON (cn.record = b.id)
174 SELECT cp.id, cp.circ_lib, pbcm.peer_record AS record
176 JOIN biblio.peer_bib_copy_map pbcm ON (pbcm.target_copy = cp.id)
177 JOIN actor.org_unit a ON (cp.circ_lib = a.id)
178 JOIN asset.copy_location cl ON (cp.location = cl.id)
179 JOIN config.copy_status cs ON (cp.status = cs.id)
189 CREATE OR REPLACE FUNCTION asset.cache_copy_visibility () RETURNS TRIGGER as $func$
196 do_add BOOLEAN := false;
197 do_remove BOOLEAN := false;
200 SELECT cp.id, cp.circ_lib, cn.record, cn.id AS call_number, cp.location, cp.status
202 JOIN asset.call_number cn ON (cn.id = cp.call_number)
203 JOIN actor.org_unit a ON (cp.circ_lib = a.id)
204 JOIN asset.copy_location cl ON (cp.location = cl.id)
205 JOIN config.copy_status cs ON (cp.status = cs.id)
206 JOIN biblio.record_entry b ON (cn.record = b.id)
217 SELECT cp.id, cp.circ_lib, pbcm.peer_record AS record, NULL AS call_number, cp.location, cp.status
219 JOIN biblio.peer_bib_copy_map pbcm ON (pbcm.target_copy = cp.id)
220 JOIN actor.org_unit a ON (cp.circ_lib = a.id)
221 JOIN asset.copy_location cl ON (cp.location = cl.id)
222 JOIN config.copy_status cs ON (cp.status = cs.id)
231 INSERT INTO asset.opac_visible_copies (copy_id, circ_lib, record)
232 SELECT DISTINCT ON (id, record) id, circ_lib, record FROM (
238 remove_query := $$ DELETE FROM asset.opac_visible_copies WHERE copy_id IN ( SELECT id FROM asset.copy WHERE $$;
240 IF TG_TABLE_NAME = 'peer_bib_copy_map' THEN
241 IF TG_OP = 'INSERT' THEN
242 add_peer_query := add_peer_query || ' AND cp.id = ' || NEW.target_copy || ' AND pbcm.peer_record = ' || NEW.peer_record;
243 EXECUTE add_front || add_peer_query || add_back;
246 remove_query := 'DELETE FROM asset.opac_visible_copies WHERE copy_id = ' || OLD.target_copy || ' AND record = ' || OLD.peer_record || ';';
247 EXECUTE remove_query;
252 IF TG_OP = 'INSERT' THEN
254 IF TG_TABLE_NAME IN ('copy', 'unit') THEN
255 add_base_query := add_base_query || ' AND cp.id = ' || NEW.id;
256 EXECUTE add_front || add_base_query || add_back;
263 -- handle items first, since with circulation activity
264 -- their statuses change frequently
265 IF TG_TABLE_NAME IN ('copy', 'unit') THEN
267 IF OLD.location <> NEW.location OR
268 OLD.call_number <> NEW.call_number OR
269 OLD.status <> NEW.status OR
270 OLD.circ_lib <> NEW.circ_lib THEN
271 -- any of these could change visibility, but
272 -- we'll save some queries and not try to calculate
273 -- the change directly
278 IF OLD.deleted <> NEW.deleted THEN
286 IF OLD.opac_visible <> NEW.opac_visible THEN
287 IF OLD.opac_visible THEN
289 ELSIF NOT do_remove THEN -- handle edge case where deleted item
290 -- is also marked opac_visible
298 DELETE FROM asset.opac_visible_copies WHERE copy_id = NEW.id;
301 add_base_query := add_base_query || ' AND cp.id = ' || NEW.id;
302 add_peer_query := add_peer_query || ' AND cp.id = ' || NEW.id;
303 EXECUTE add_front || add_base_query || ' UNION ' || add_peer_query || add_back;
310 IF TG_TABLE_NAME IN ('call_number', 'copy_location', 'record_entry') THEN -- these have a 'deleted' column
312 IF OLD.deleted AND NEW.deleted THEN -- do nothing
316 ELSIF NEW.deleted THEN -- remove rows
318 IF TG_TABLE_NAME = 'call_number' THEN
319 DELETE FROM asset.opac_visible_copies WHERE copy_id IN (SELECT id FROM asset.copy WHERE call_number = NEW.id);
320 ELSIF TG_TABLE_NAME = 'copy_location' THEN
321 DELETE FROM asset.opac_visible_copies WHERE copy_id IN (SELECT id FROM asset.copy WHERE location = NEW.id);
322 ELSIF TG_TABLE_NAME = 'record_entry' THEN
323 DELETE FROM asset.opac_visible_copies WHERE record = NEW.id;
328 ELSIF OLD.deleted THEN -- add rows
330 IF TG_TABLE_NAME = 'call_number' THEN
331 add_base_query := add_base_query || ' AND cn.id = ' || NEW.id;
332 EXECUTE add_front || add_base_query || add_back;
333 ELSIF TG_TABLE_NAME = 'copy_location' THEN
334 add_base_query := add_base_query || 'AND cl.id = ' || NEW.id;
335 EXECUTE add_front || add_base_query || add_back;
336 ELSIF TG_TABLE_NAME = 'record_entry' THEN
337 add_base_query := add_base_query || ' AND cn.record = ' || NEW.id;
338 add_peer_query := add_peer_query || ' AND pbcm.peer_record = ' || NEW.id;
339 EXECUTE add_front || add_base_query || ' UNION ' || add_peer_query || add_back;
348 IF TG_TABLE_NAME = 'call_number' THEN
350 IF OLD.record <> NEW.record THEN
351 -- call number is linked to different bib
352 remove_query := remove_query || 'call_number = ' || NEW.id || ');';
353 EXECUTE remove_query;
354 add_base_query := add_base_query || ' AND cn.id = ' || NEW.id;
355 EXECUTE add_front || add_base_query || add_back;
362 IF TG_TABLE_NAME IN ('record_entry') THEN
363 RETURN NEW; -- don't have 'opac_visible'
366 -- actor.org_unit, asset.copy_location, asset.copy_status
367 IF NEW.opac_visible = OLD.opac_visible THEN -- do nothing
371 ELSIF NEW.opac_visible THEN -- add rows
373 IF TG_TABLE_NAME = 'org_unit' THEN
374 add_base_query := add_base_query || ' AND cp.circ_lib = ' || NEW.id;
375 add_peer_query := add_peer_query || ' AND cp.circ_lib = ' || NEW.id;
376 ELSIF TG_TABLE_NAME = 'copy_location' THEN
377 add_base_query := add_base_query || ' AND cp.location = ' || NEW.id;
378 add_peer_query := add_peer_query || ' AND cp.location = ' || NEW.id;
379 ELSIF TG_TABLE_NAME = 'copy_status' THEN
380 add_base_query := add_base_query || ' AND cp.status = ' || NEW.id;
381 add_peer_query := add_peer_query || ' AND cp.status = ' || NEW.id;
384 EXECUTE add_front || add_base_query || ' UNION ' || add_peer_query || add_back;
388 IF TG_TABLE_NAME = 'org_unit' THEN
389 remove_query := 'DELETE FROM asset.opac_visible_copies WHERE circ_lib = ' || NEW.id || ';';
390 ELSIF TG_TABLE_NAME = 'copy_location' THEN
391 remove_query := remove_query || 'location = ' || NEW.id || ');';
392 ELSIF TG_TABLE_NAME = 'copy_status' THEN
393 remove_query := remove_query || 'status = ' || NEW.id || ');';
396 EXECUTE remove_query;
402 $func$ LANGUAGE PLPGSQL;
404 -- updated copy location validity test to disallow deleted locations
405 CREATE OR REPLACE FUNCTION vandelay.ingest_items ( import_id BIGINT, attr_def_id BIGINT ) RETURNS SETOF vandelay.import_item AS $$
431 attr_set vandelay.import_item%ROWTYPE;
438 SELECT * INTO attr_def FROM vandelay.import_item_attr_definition WHERE id = attr_def_id;
442 attr_set.definition := attr_def.id;
444 -- Build the combined XPath
448 WHEN attr_def.owning_lib IS NULL THEN 'null()'
449 WHEN LENGTH( attr_def.owning_lib ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.owning_lib || '"]'
450 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.owning_lib
455 WHEN attr_def.circ_lib IS NULL THEN 'null()'
456 WHEN LENGTH( attr_def.circ_lib ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circ_lib || '"]'
457 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circ_lib
462 WHEN attr_def.call_number IS NULL THEN 'null()'
463 WHEN LENGTH( attr_def.call_number ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.call_number || '"]'
464 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.call_number
469 WHEN attr_def.copy_number IS NULL THEN 'null()'
470 WHEN LENGTH( attr_def.copy_number ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.copy_number || '"]'
471 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.copy_number
476 WHEN attr_def.status IS NULL THEN 'null()'
477 WHEN LENGTH( attr_def.status ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.status || '"]'
478 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.status
483 WHEN attr_def.location IS NULL THEN 'null()'
484 WHEN LENGTH( attr_def.location ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.location || '"]'
485 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.location
490 WHEN attr_def.circulate IS NULL THEN 'null()'
491 WHEN LENGTH( attr_def.circulate ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circulate || '"]'
492 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circulate
497 WHEN attr_def.deposit IS NULL THEN 'null()'
498 WHEN LENGTH( attr_def.deposit ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.deposit || '"]'
499 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.deposit
504 WHEN attr_def.deposit_amount IS NULL THEN 'null()'
505 WHEN LENGTH( attr_def.deposit_amount ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.deposit_amount || '"]'
506 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.deposit_amount
511 WHEN attr_def.ref IS NULL THEN 'null()'
512 WHEN LENGTH( attr_def.ref ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.ref || '"]'
513 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.ref
518 WHEN attr_def.holdable IS NULL THEN 'null()'
519 WHEN LENGTH( attr_def.holdable ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.holdable || '"]'
520 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.holdable
525 WHEN attr_def.price IS NULL THEN 'null()'
526 WHEN LENGTH( attr_def.price ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.price || '"]'
527 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.price
532 WHEN attr_def.barcode IS NULL THEN 'null()'
533 WHEN LENGTH( attr_def.barcode ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.barcode || '"]'
534 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.barcode
539 WHEN attr_def.circ_modifier IS NULL THEN 'null()'
540 WHEN LENGTH( attr_def.circ_modifier ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circ_modifier || '"]'
541 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circ_modifier
546 WHEN attr_def.circ_as_type IS NULL THEN 'null()'
547 WHEN LENGTH( attr_def.circ_as_type ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circ_as_type || '"]'
548 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circ_as_type
553 WHEN attr_def.alert_message IS NULL THEN 'null()'
554 WHEN LENGTH( attr_def.alert_message ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.alert_message || '"]'
555 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.alert_message
560 WHEN attr_def.opac_visible IS NULL THEN 'null()'
561 WHEN LENGTH( attr_def.opac_visible ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.opac_visible || '"]'
562 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.opac_visible
567 WHEN attr_def.pub_note IS NULL THEN 'null()'
568 WHEN LENGTH( attr_def.pub_note ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.pub_note || '"]'
569 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.pub_note
573 WHEN attr_def.priv_note IS NULL THEN 'null()'
574 WHEN LENGTH( attr_def.priv_note ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.priv_note || '"]'
575 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.priv_note
580 WHEN attr_def.internal_id IS NULL THEN 'null()'
581 WHEN LENGTH( attr_def.internal_id ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.internal_id || '"]'
582 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.internal_id
590 call_number || '|' ||
591 copy_number || '|' ||
596 deposit_amount || '|' ||
601 circ_modifier || '|' ||
602 circ_as_type || '|' ||
603 alert_message || '|' ||
606 internal_id || '|' ||
611 FROM oils_xpath_table( 'id', 'marc', 'vandelay.queued_bib_record', xpath, 'id = ' || import_id )
612 AS t( id INT, ol TEXT, clib TEXT, cn TEXT, cnum TEXT, cs TEXT, cl TEXT, circ TEXT,
613 dep TEXT, dep_amount TEXT, r TEXT, hold TEXT, pr TEXT, bc TEXT, circ_mod TEXT,
614 circ_as TEXT, amessage TEXT, note TEXT, pnote TEXT, internal_id TEXT, opac_vis TEXT )
617 attr_set.import_error := NULL;
618 attr_set.error_detail := NULL;
619 attr_set.deposit_amount := NULL;
620 attr_set.copy_number := NULL;
621 attr_set.price := NULL;
622 attr_set.circ_modifier := NULL;
623 attr_set.location := NULL;
624 attr_set.barcode := NULL;
625 attr_set.call_number := NULL;
627 IF tmp_attr_set.pr != '' THEN
628 tmp_str = REGEXP_REPLACE(tmp_attr_set.pr, E'[^0-9\\.]', '', 'g');
630 attr_set.import_error := 'import.item.invalid.price';
631 attr_set.error_detail := tmp_attr_set.pr; -- original value
632 RETURN NEXT attr_set; CONTINUE;
634 attr_set.price := tmp_str::NUMERIC(8,2);
637 IF tmp_attr_set.dep_amount != '' THEN
638 tmp_str = REGEXP_REPLACE(tmp_attr_set.dep_amount, E'[^0-9\\.]', '', 'g');
640 attr_set.import_error := 'import.item.invalid.deposit_amount';
641 attr_set.error_detail := tmp_attr_set.dep_amount;
642 RETURN NEXT attr_set; CONTINUE;
644 attr_set.deposit_amount := tmp_str::NUMERIC(8,2);
647 IF tmp_attr_set.cnum != '' THEN
648 tmp_str = REGEXP_REPLACE(tmp_attr_set.cnum, E'[^0-9]', '', 'g');
650 attr_set.import_error := 'import.item.invalid.copy_number';
651 attr_set.error_detail := tmp_attr_set.cnum;
652 RETURN NEXT attr_set; CONTINUE;
654 attr_set.copy_number := tmp_str::INT;
657 IF tmp_attr_set.ol != '' THEN
658 SELECT id INTO attr_set.owning_lib FROM actor.org_unit WHERE shortname = UPPER(tmp_attr_set.ol); -- INT
660 attr_set.import_error := 'import.item.invalid.owning_lib';
661 attr_set.error_detail := tmp_attr_set.ol;
662 RETURN NEXT attr_set; CONTINUE;
666 IF tmp_attr_set.clib != '' THEN
667 SELECT id INTO attr_set.circ_lib FROM actor.org_unit WHERE shortname = UPPER(tmp_attr_set.clib); -- INT
669 attr_set.import_error := 'import.item.invalid.circ_lib';
670 attr_set.error_detail := tmp_attr_set.clib;
671 RETURN NEXT attr_set; CONTINUE;
675 IF tmp_attr_set.cs != '' THEN
676 SELECT id INTO attr_set.status FROM config.copy_status WHERE LOWER(name) = LOWER(tmp_attr_set.cs); -- INT
678 attr_set.import_error := 'import.item.invalid.status';
679 attr_set.error_detail := tmp_attr_set.cs;
680 RETURN NEXT attr_set; CONTINUE;
684 IF COALESCE(tmp_attr_set.circ_mod, '') = '' THEN
686 -- no circ mod defined, see if we should apply a default
687 SELECT INTO attr_set.circ_modifier TRIM(BOTH '"' FROM value)
688 FROM actor.org_unit_ancestor_setting(
689 'vandelay.item.circ_modifier.default',
693 -- make sure the value from the org setting is still valid
694 PERFORM 1 FROM config.circ_modifier WHERE code = attr_set.circ_modifier;
696 attr_set.import_error := 'import.item.invalid.circ_modifier';
697 attr_set.error_detail := tmp_attr_set.circ_mod;
698 RETURN NEXT attr_set; CONTINUE;
703 SELECT code INTO attr_set.circ_modifier FROM config.circ_modifier WHERE code = tmp_attr_set.circ_mod;
705 attr_set.import_error := 'import.item.invalid.circ_modifier';
706 attr_set.error_detail := tmp_attr_set.circ_mod;
707 RETURN NEXT attr_set; CONTINUE;
711 IF tmp_attr_set.circ_as != '' THEN
712 SELECT code INTO attr_set.circ_as_type FROM config.coded_value_map WHERE ctype = 'item_type' AND code = tmp_attr_set.circ_as;
714 attr_set.import_error := 'import.item.invalid.circ_as_type';
715 attr_set.error_detail := tmp_attr_set.circ_as;
716 RETURN NEXT attr_set; CONTINUE;
720 IF COALESCE(tmp_attr_set.cl, '') = '' THEN
721 -- no location specified, see if we should apply a default
723 SELECT INTO attr_set.location TRIM(BOTH '"' FROM value)
724 FROM actor.org_unit_ancestor_setting(
725 'vandelay.item.copy_location.default',
729 -- make sure the value from the org setting is still valid
730 PERFORM 1 FROM asset.copy_location
731 WHERE id = attr_set.location AND NOT deleted;
733 attr_set.import_error := 'import.item.invalid.location';
734 attr_set.error_detail := tmp_attr_set.cs;
735 RETURN NEXT attr_set; CONTINUE;
739 -- search up the org unit tree for a matching copy location
740 WITH RECURSIVE anscestor_depth AS (
744 FROM actor.org_unit ou
745 JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
746 WHERE ou.id = COALESCE(attr_set.owning_lib, attr_set.circ_lib)
751 FROM actor.org_unit ou
752 JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
753 JOIN anscestor_depth ot ON (ot.parent_ou = ou.id)
754 ) SELECT cpl.id INTO attr_set.location
755 FROM anscestor_depth a
756 JOIN asset.copy_location cpl ON (cpl.owning_lib = a.id)
757 WHERE LOWER(cpl.name) = LOWER(tmp_attr_set.cl)
759 ORDER BY a.depth DESC
763 attr_set.import_error := 'import.item.invalid.location';
764 attr_set.error_detail := tmp_attr_set.cs;
765 RETURN NEXT attr_set; CONTINUE;
769 attr_set.circulate :=
770 LOWER( SUBSTRING( tmp_attr_set.circ, 1, 1)) IN ('t','y','1')
771 OR LOWER(tmp_attr_set.circ) = 'circulating'; -- BOOL
774 LOWER( SUBSTRING( tmp_attr_set.dep, 1, 1 ) ) IN ('t','y','1')
775 OR LOWER(tmp_attr_set.dep) = 'deposit'; -- BOOL
778 LOWER( SUBSTRING( tmp_attr_set.hold, 1, 1 ) ) IN ('t','y','1')
779 OR LOWER(tmp_attr_set.hold) = 'holdable'; -- BOOL
781 attr_set.opac_visible :=
782 LOWER( SUBSTRING( tmp_attr_set.opac_vis, 1, 1 ) ) IN ('t','y','1')
783 OR LOWER(tmp_attr_set.opac_vis) = 'visible'; -- BOOL
786 LOWER( SUBSTRING( tmp_attr_set.r, 1, 1 ) ) IN ('t','y','1')
787 OR LOWER(tmp_attr_set.r) = 'reference'; -- BOOL
789 attr_set.call_number := tmp_attr_set.cn; -- TEXT
790 attr_set.barcode := tmp_attr_set.bc; -- TEXT,
791 attr_set.alert_message := tmp_attr_set.amessage; -- TEXT,
792 attr_set.pub_note := tmp_attr_set.note; -- TEXT,
793 attr_set.priv_note := tmp_attr_set.pnote; -- TEXT,
794 attr_set.alert_message := tmp_attr_set.amessage; -- TEXT,
795 attr_set.internal_id := tmp_attr_set.internal_id::BIGINT;
797 RETURN NEXT attr_set;