4 SELECT evergreen.upgrade_deps_block_check('0824', :eg_version);
6 INSERT INTO config.org_unit_setting_type
7 (grp, name, label, description, datatype, fm_class)
10 'vandelay.item.barcode.auto',
12 'vandelay.item.barcode.auto',
13 'Vandelay Generate Default Barcodes',
16 'vandelay.item.barcode.auto',
17 'Auto-generate deault item barcodes when no item barcode is present',
23 'vandelay.item.barcode.prefix',
25 'vandelay.item.barcode.prefix',
26 'Vandelay Default Barcode Prefix',
29 'vandelay.item.barcode.prefix',
30 'Apply this prefix to any auto-generated item barcodes',
36 'vandelay.item.call_number.auto',
38 'vandelay.item.call_number.auto',
39 'Vandelay Generate Default Call Numbers',
42 'vandelay.item.call_number.auto',
43 'Auto-generate default item call numbers when no item call number is present',
49 'vandelay.item.call_number.prefix',
51 'vandelay.item.call_number.prefix',
52 'Vandelay Default Call Number Prefix',
55 'vandelay.item.call_number.prefix',
56 'Apply this prefix to any auto-generated item call numbers',
62 'vandelay.item.copy_location.default',
64 'vandelay.item.copy_location.default',
65 'Vandelay Default Copy Location',
68 'vandelay.item.copy_location.default',
69 'Default copy location value for imported items',
75 'vandelay.item.circ_modifier.default',
77 'vandelay.item.circ_modifier.default',
78 'Vandelay Default Circulation Modifier',
81 'vandelay.item.circ_modifier.default',
82 'Default circulation modifier value for imported items',
89 CREATE OR REPLACE FUNCTION vandelay.ingest_items ( import_id BIGINT, attr_def_id BIGINT ) RETURNS SETOF vandelay.import_item AS $$
115 attr_set vandelay.import_item%ROWTYPE;
122 SELECT * INTO attr_def FROM vandelay.import_item_attr_definition WHERE id = attr_def_id;
126 attr_set.definition := attr_def.id;
128 -- Build the combined XPath
132 WHEN attr_def.owning_lib IS NULL THEN 'null()'
133 WHEN LENGTH( attr_def.owning_lib ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.owning_lib || '"]'
134 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.owning_lib
139 WHEN attr_def.circ_lib IS NULL THEN 'null()'
140 WHEN LENGTH( attr_def.circ_lib ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circ_lib || '"]'
141 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circ_lib
146 WHEN attr_def.call_number IS NULL THEN 'null()'
147 WHEN LENGTH( attr_def.call_number ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.call_number || '"]'
148 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.call_number
153 WHEN attr_def.copy_number IS NULL THEN 'null()'
154 WHEN LENGTH( attr_def.copy_number ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.copy_number || '"]'
155 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.copy_number
160 WHEN attr_def.status IS NULL THEN 'null()'
161 WHEN LENGTH( attr_def.status ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.status || '"]'
162 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.status
167 WHEN attr_def.location IS NULL THEN 'null()'
168 WHEN LENGTH( attr_def.location ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.location || '"]'
169 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.location
174 WHEN attr_def.circulate IS NULL THEN 'null()'
175 WHEN LENGTH( attr_def.circulate ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circulate || '"]'
176 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circulate
181 WHEN attr_def.deposit IS NULL THEN 'null()'
182 WHEN LENGTH( attr_def.deposit ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.deposit || '"]'
183 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.deposit
188 WHEN attr_def.deposit_amount IS NULL THEN 'null()'
189 WHEN LENGTH( attr_def.deposit_amount ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.deposit_amount || '"]'
190 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.deposit_amount
195 WHEN attr_def.ref IS NULL THEN 'null()'
196 WHEN LENGTH( attr_def.ref ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.ref || '"]'
197 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.ref
202 WHEN attr_def.holdable IS NULL THEN 'null()'
203 WHEN LENGTH( attr_def.holdable ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.holdable || '"]'
204 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.holdable
209 WHEN attr_def.price IS NULL THEN 'null()'
210 WHEN LENGTH( attr_def.price ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.price || '"]'
211 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.price
216 WHEN attr_def.barcode IS NULL THEN 'null()'
217 WHEN LENGTH( attr_def.barcode ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.barcode || '"]'
218 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.barcode
223 WHEN attr_def.circ_modifier IS NULL THEN 'null()'
224 WHEN LENGTH( attr_def.circ_modifier ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circ_modifier || '"]'
225 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circ_modifier
230 WHEN attr_def.circ_as_type IS NULL THEN 'null()'
231 WHEN LENGTH( attr_def.circ_as_type ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circ_as_type || '"]'
232 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circ_as_type
237 WHEN attr_def.alert_message IS NULL THEN 'null()'
238 WHEN LENGTH( attr_def.alert_message ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.alert_message || '"]'
239 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.alert_message
244 WHEN attr_def.opac_visible IS NULL THEN 'null()'
245 WHEN LENGTH( attr_def.opac_visible ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.opac_visible || '"]'
246 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.opac_visible
251 WHEN attr_def.pub_note IS NULL THEN 'null()'
252 WHEN LENGTH( attr_def.pub_note ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.pub_note || '"]'
253 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.pub_note
257 WHEN attr_def.priv_note IS NULL THEN 'null()'
258 WHEN LENGTH( attr_def.priv_note ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.priv_note || '"]'
259 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.priv_note
264 WHEN attr_def.internal_id IS NULL THEN 'null()'
265 WHEN LENGTH( attr_def.internal_id ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.internal_id || '"]'
266 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.internal_id
274 call_number || '|' ||
275 copy_number || '|' ||
280 deposit_amount || '|' ||
285 circ_modifier || '|' ||
286 circ_as_type || '|' ||
287 alert_message || '|' ||
290 internal_id || '|' ||
295 FROM oils_xpath_table( 'id', 'marc', 'vandelay.queued_bib_record', xpath, 'id = ' || import_id )
296 AS t( id INT, ol TEXT, clib TEXT, cn TEXT, cnum TEXT, cs TEXT, cl TEXT, circ TEXT,
297 dep TEXT, dep_amount TEXT, r TEXT, hold TEXT, pr TEXT, bc TEXT, circ_mod TEXT,
298 circ_as TEXT, amessage TEXT, note TEXT, pnote TEXT, internal_id TEXT, opac_vis TEXT )
301 attr_set.import_error := NULL;
302 attr_set.error_detail := NULL;
303 attr_set.deposit_amount := NULL;
304 attr_set.copy_number := NULL;
305 attr_set.price := NULL;
306 attr_set.circ_modifier := NULL;
307 attr_set.location := NULL;
308 attr_set.barcode := NULL;
309 attr_set.call_number := NULL;
311 IF tmp_attr_set.pr != '' THEN
312 tmp_str = REGEXP_REPLACE(tmp_attr_set.pr, E'[^0-9\\.]', '', 'g');
314 attr_set.import_error := 'import.item.invalid.price';
315 attr_set.error_detail := tmp_attr_set.pr; -- original value
316 RETURN NEXT attr_set; CONTINUE;
318 attr_set.price := tmp_str::NUMERIC(8,2);
321 IF tmp_attr_set.dep_amount != '' THEN
322 tmp_str = REGEXP_REPLACE(tmp_attr_set.dep_amount, E'[^0-9\\.]', '', 'g');
324 attr_set.import_error := 'import.item.invalid.deposit_amount';
325 attr_set.error_detail := tmp_attr_set.dep_amount;
326 RETURN NEXT attr_set; CONTINUE;
328 attr_set.deposit_amount := tmp_str::NUMERIC(8,2);
331 IF tmp_attr_set.cnum != '' THEN
332 tmp_str = REGEXP_REPLACE(tmp_attr_set.cnum, E'[^0-9]', '', 'g');
334 attr_set.import_error := 'import.item.invalid.copy_number';
335 attr_set.error_detail := tmp_attr_set.cnum;
336 RETURN NEXT attr_set; CONTINUE;
338 attr_set.copy_number := tmp_str::INT;
341 IF tmp_attr_set.ol != '' THEN
342 SELECT id INTO attr_set.owning_lib FROM actor.org_unit WHERE shortname = UPPER(tmp_attr_set.ol); -- INT
344 attr_set.import_error := 'import.item.invalid.owning_lib';
345 attr_set.error_detail := tmp_attr_set.ol;
346 RETURN NEXT attr_set; CONTINUE;
350 IF tmp_attr_set.clib != '' THEN
351 SELECT id INTO attr_set.circ_lib FROM actor.org_unit WHERE shortname = UPPER(tmp_attr_set.clib); -- INT
353 attr_set.import_error := 'import.item.invalid.circ_lib';
354 attr_set.error_detail := tmp_attr_set.clib;
355 RETURN NEXT attr_set; CONTINUE;
359 IF tmp_attr_set.cs != '' THEN
360 SELECT id INTO attr_set.status FROM config.copy_status WHERE LOWER(name) = LOWER(tmp_attr_set.cs); -- INT
362 attr_set.import_error := 'import.item.invalid.status';
363 attr_set.error_detail := tmp_attr_set.cs;
364 RETURN NEXT attr_set; CONTINUE;
368 IF COALESCE(tmp_attr_set.circ_mod, '') = '' THEN
370 -- no circ mod defined, see if we should apply a default
371 SELECT INTO attr_set.circ_modifier TRIM(BOTH '"' FROM value)
372 FROM actor.org_unit_ancestor_setting(
373 'vandelay.item.circ_modifier.default',
377 -- make sure the value from the org setting is still valid
378 PERFORM 1 FROM config.circ_modifier WHERE code = attr_set.circ_modifier;
380 attr_set.import_error := 'import.item.invalid.circ_modifier';
381 attr_set.error_detail := tmp_attr_set.circ_mod;
382 RETURN NEXT attr_set; CONTINUE;
387 SELECT code INTO attr_set.circ_modifier FROM config.circ_modifier WHERE code = tmp_attr_set.circ_mod;
389 attr_set.import_error := 'import.item.invalid.circ_modifier';
390 attr_set.error_detail := tmp_attr_set.circ_mod;
391 RETURN NEXT attr_set; CONTINUE;
395 IF tmp_attr_set.circ_as != '' THEN
396 SELECT code INTO attr_set.circ_as_type FROM config.coded_value_map WHERE ctype = 'item_type' AND code = tmp_attr_set.circ_as;
398 attr_set.import_error := 'import.item.invalid.circ_as_type';
399 attr_set.error_detail := tmp_attr_set.circ_as;
400 RETURN NEXT attr_set; CONTINUE;
404 IF COALESCE(tmp_attr_set.cl, '') = '' THEN
405 -- no location specified, see if we should apply a default
407 SELECT INTO attr_set.location TRIM(BOTH '"' FROM value)
408 FROM actor.org_unit_ancestor_setting(
409 'vandelay.item.copy_location.default',
413 -- make sure the value from the org setting is still valid
414 PERFORM 1 FROM asset.copy_location WHERE id = attr_set.location;
416 attr_set.import_error := 'import.item.invalid.location';
417 attr_set.error_detail := tmp_attr_set.cs;
418 RETURN NEXT attr_set; CONTINUE;
422 -- search up the org unit tree for a matching copy location
423 WITH RECURSIVE anscestor_depth AS (
427 FROM actor.org_unit ou
428 JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
429 WHERE ou.id = COALESCE(attr_set.owning_lib, attr_set.circ_lib)
434 FROM actor.org_unit ou
435 JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
436 JOIN anscestor_depth ot ON (ot.parent_ou = ou.id)
437 ) SELECT cpl.id INTO attr_set.location
438 FROM anscestor_depth a
439 JOIN asset.copy_location cpl ON (cpl.owning_lib = a.id)
440 WHERE LOWER(cpl.name) = LOWER(tmp_attr_set.cl)
441 ORDER BY a.depth DESC
445 attr_set.import_error := 'import.item.invalid.location';
446 attr_set.error_detail := tmp_attr_set.cs;
447 RETURN NEXT attr_set; CONTINUE;
451 attr_set.circulate :=
452 LOWER( SUBSTRING( tmp_attr_set.circ, 1, 1)) IN ('t','y','1')
453 OR LOWER(tmp_attr_set.circ) = 'circulating'; -- BOOL
456 LOWER( SUBSTRING( tmp_attr_set.dep, 1, 1 ) ) IN ('t','y','1')
457 OR LOWER(tmp_attr_set.dep) = 'deposit'; -- BOOL
460 LOWER( SUBSTRING( tmp_attr_set.hold, 1, 1 ) ) IN ('t','y','1')
461 OR LOWER(tmp_attr_set.hold) = 'holdable'; -- BOOL
463 attr_set.opac_visible :=
464 LOWER( SUBSTRING( tmp_attr_set.opac_vis, 1, 1 ) ) IN ('t','y','1')
465 OR LOWER(tmp_attr_set.opac_vis) = 'visible'; -- BOOL
468 LOWER( SUBSTRING( tmp_attr_set.r, 1, 1 ) ) IN ('t','y','1')
469 OR LOWER(tmp_attr_set.r) = 'reference'; -- BOOL
471 attr_set.call_number := tmp_attr_set.cn; -- TEXT
472 attr_set.barcode := tmp_attr_set.bc; -- TEXT,
473 attr_set.alert_message := tmp_attr_set.amessage; -- TEXT,
474 attr_set.pub_note := tmp_attr_set.note; -- TEXT,
475 attr_set.priv_note := tmp_attr_set.pnote; -- TEXT,
476 attr_set.alert_message := tmp_attr_set.amessage; -- TEXT,
477 attr_set.internal_id := tmp_attr_set.internal_id::BIGINT;
479 RETURN NEXT attr_set;