1 -- 0731.schema.vandelay_item_overlay.sql
4 SELECT evergreen.upgrade_deps_block_check('0731', :eg_version);
6 ALTER TABLE vandelay.import_item_attr_definition
7 ADD COLUMN internal_id TEXT;
9 ALTER TABLE vandelay.import_item
10 ADD COLUMN internal_id BIGINT;
12 INSERT INTO permission.perm_list ( id, code, description ) VALUES
13 ( 536, 'IMPORT_OVERLAY_COPY', oils_i18n_gettext( 536,
14 'Allows a user to overlay copy data in MARC import', 'ppl', 'description'));
16 CREATE OR REPLACE FUNCTION vandelay.ingest_bib_items ( ) RETURNS TRIGGER AS $func$
19 item_data vandelay.import_item%ROWTYPE;
22 IF TG_OP IN ('INSERT','UPDATE') AND NEW.imported_as IS NOT NULL THEN
26 SELECT item_attr_def INTO attr_def FROM vandelay.bib_queue WHERE id = NEW.queue;
28 FOR item_data IN SELECT * FROM vandelay.ingest_items( NEW.id::BIGINT, attr_def ) LOOP
29 INSERT INTO vandelay.import_item (
59 item_data.call_number,
60 item_data.copy_number,
65 item_data.deposit_amount,
70 item_data.circ_modifier,
71 item_data.circ_as_type,
72 item_data.alert_message,
75 item_data.internal_id,
76 item_data.opac_visible,
77 item_data.import_error,
78 item_data.error_detail
84 $func$ LANGUAGE PLPGSQL;
87 CREATE OR REPLACE FUNCTION vandelay.ingest_items ( import_id BIGINT, attr_def_id BIGINT ) RETURNS SETOF vandelay.import_item AS $$
113 attr_set vandelay.import_item%ROWTYPE;
120 SELECT * INTO attr_def FROM vandelay.import_item_attr_definition WHERE id = attr_def_id;
124 attr_set.definition := attr_def.id;
126 -- Build the combined XPath
130 WHEN attr_def.owning_lib IS NULL THEN 'null()'
131 WHEN LENGTH( attr_def.owning_lib ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.owning_lib || '"]'
132 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.owning_lib
137 WHEN attr_def.circ_lib IS NULL THEN 'null()'
138 WHEN LENGTH( attr_def.circ_lib ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circ_lib || '"]'
139 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circ_lib
144 WHEN attr_def.call_number IS NULL THEN 'null()'
145 WHEN LENGTH( attr_def.call_number ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.call_number || '"]'
146 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.call_number
151 WHEN attr_def.copy_number IS NULL THEN 'null()'
152 WHEN LENGTH( attr_def.copy_number ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.copy_number || '"]'
153 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.copy_number
158 WHEN attr_def.status IS NULL THEN 'null()'
159 WHEN LENGTH( attr_def.status ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.status || '"]'
160 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.status
165 WHEN attr_def.location IS NULL THEN 'null()'
166 WHEN LENGTH( attr_def.location ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.location || '"]'
167 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.location
172 WHEN attr_def.circulate IS NULL THEN 'null()'
173 WHEN LENGTH( attr_def.circulate ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circulate || '"]'
174 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circulate
179 WHEN attr_def.deposit IS NULL THEN 'null()'
180 WHEN LENGTH( attr_def.deposit ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.deposit || '"]'
181 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.deposit
186 WHEN attr_def.deposit_amount IS NULL THEN 'null()'
187 WHEN LENGTH( attr_def.deposit_amount ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.deposit_amount || '"]'
188 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.deposit_amount
193 WHEN attr_def.ref IS NULL THEN 'null()'
194 WHEN LENGTH( attr_def.ref ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.ref || '"]'
195 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.ref
200 WHEN attr_def.holdable IS NULL THEN 'null()'
201 WHEN LENGTH( attr_def.holdable ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.holdable || '"]'
202 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.holdable
207 WHEN attr_def.price IS NULL THEN 'null()'
208 WHEN LENGTH( attr_def.price ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.price || '"]'
209 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.price
214 WHEN attr_def.barcode IS NULL THEN 'null()'
215 WHEN LENGTH( attr_def.barcode ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.barcode || '"]'
216 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.barcode
221 WHEN attr_def.circ_modifier IS NULL THEN 'null()'
222 WHEN LENGTH( attr_def.circ_modifier ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circ_modifier || '"]'
223 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circ_modifier
228 WHEN attr_def.circ_as_type IS NULL THEN 'null()'
229 WHEN LENGTH( attr_def.circ_as_type ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circ_as_type || '"]'
230 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circ_as_type
235 WHEN attr_def.alert_message IS NULL THEN 'null()'
236 WHEN LENGTH( attr_def.alert_message ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.alert_message || '"]'
237 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.alert_message
242 WHEN attr_def.opac_visible IS NULL THEN 'null()'
243 WHEN LENGTH( attr_def.opac_visible ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.opac_visible || '"]'
244 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.opac_visible
249 WHEN attr_def.pub_note IS NULL THEN 'null()'
250 WHEN LENGTH( attr_def.pub_note ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.pub_note || '"]'
251 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.pub_note
255 WHEN attr_def.priv_note IS NULL THEN 'null()'
256 WHEN LENGTH( attr_def.priv_note ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.priv_note || '"]'
257 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.priv_note
262 WHEN attr_def.internal_id IS NULL THEN 'null()'
263 WHEN LENGTH( attr_def.internal_id ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.internal_id || '"]'
264 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.internal_id
272 call_number || '|' ||
273 copy_number || '|' ||
278 deposit_amount || '|' ||
283 circ_modifier || '|' ||
284 circ_as_type || '|' ||
285 alert_message || '|' ||
288 internal_id || '|' ||
293 FROM oils_xpath_table( 'id', 'marc', 'vandelay.queued_bib_record', xpath, 'id = ' || import_id )
294 AS t( id INT, ol TEXT, clib TEXT, cn TEXT, cnum TEXT, cs TEXT, cl TEXT, circ TEXT,
295 dep TEXT, dep_amount TEXT, r TEXT, hold TEXT, pr TEXT, bc TEXT, circ_mod TEXT,
296 circ_as TEXT, amessage TEXT, note TEXT, pnote TEXT, internal_id TEXT, opac_vis TEXT )
299 attr_set.import_error := NULL;
300 attr_set.error_detail := NULL;
301 attr_set.deposit_amount := NULL;
302 attr_set.copy_number := NULL;
303 attr_set.price := NULL;
305 IF tmp_attr_set.pr != '' THEN
306 tmp_str = REGEXP_REPLACE(tmp_attr_set.pr, E'[^0-9\\.]', '', 'g');
308 attr_set.import_error := 'import.item.invalid.price';
309 attr_set.error_detail := tmp_attr_set.pr; -- original value
310 RETURN NEXT attr_set; CONTINUE;
312 attr_set.price := tmp_str::NUMERIC(8,2);
315 IF tmp_attr_set.dep_amount != '' THEN
316 tmp_str = REGEXP_REPLACE(tmp_attr_set.dep_amount, E'[^0-9\\.]', '', 'g');
318 attr_set.import_error := 'import.item.invalid.deposit_amount';
319 attr_set.error_detail := tmp_attr_set.dep_amount;
320 RETURN NEXT attr_set; CONTINUE;
322 attr_set.deposit_amount := tmp_str::NUMERIC(8,2);
325 IF tmp_attr_set.cnum != '' THEN
326 tmp_str = REGEXP_REPLACE(tmp_attr_set.cnum, E'[^0-9]', '', 'g');
328 attr_set.import_error := 'import.item.invalid.copy_number';
329 attr_set.error_detail := tmp_attr_set.cnum;
330 RETURN NEXT attr_set; CONTINUE;
332 attr_set.copy_number := tmp_str::INT;
335 IF tmp_attr_set.ol != '' THEN
336 SELECT id INTO attr_set.owning_lib FROM actor.org_unit WHERE shortname = UPPER(tmp_attr_set.ol); -- INT
338 attr_set.import_error := 'import.item.invalid.owning_lib';
339 attr_set.error_detail := tmp_attr_set.ol;
340 RETURN NEXT attr_set; CONTINUE;
344 IF tmp_attr_set.clib != '' THEN
345 SELECT id INTO attr_set.circ_lib FROM actor.org_unit WHERE shortname = UPPER(tmp_attr_set.clib); -- INT
347 attr_set.import_error := 'import.item.invalid.circ_lib';
348 attr_set.error_detail := tmp_attr_set.clib;
349 RETURN NEXT attr_set; CONTINUE;
353 IF tmp_attr_set.cs != '' THEN
354 SELECT id INTO attr_set.status FROM config.copy_status WHERE LOWER(name) = LOWER(tmp_attr_set.cs); -- INT
356 attr_set.import_error := 'import.item.invalid.status';
357 attr_set.error_detail := tmp_attr_set.cs;
358 RETURN NEXT attr_set; CONTINUE;
362 IF tmp_attr_set.circ_mod != '' THEN
363 SELECT code INTO attr_set.circ_modifier FROM config.circ_modifier WHERE code = tmp_attr_set.circ_mod;
365 attr_set.import_error := 'import.item.invalid.circ_modifier';
366 attr_set.error_detail := tmp_attr_set.circ_mod;
367 RETURN NEXT attr_set; CONTINUE;
371 IF tmp_attr_set.circ_as != '' THEN
372 SELECT code INTO attr_set.circ_as_type FROM config.coded_value_map WHERE ctype = 'item_type' AND code = tmp_attr_set.circ_as;
374 attr_set.import_error := 'import.item.invalid.circ_as_type';
375 attr_set.error_detail := tmp_attr_set.circ_as;
376 RETURN NEXT attr_set; CONTINUE;
380 IF tmp_attr_set.cl != '' THEN
382 -- search up the org unit tree for a matching copy location
383 WITH RECURSIVE anscestor_depth AS (
387 FROM actor.org_unit ou
388 JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
389 WHERE ou.id = COALESCE(attr_set.owning_lib, attr_set.circ_lib)
394 FROM actor.org_unit ou
395 JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
396 JOIN anscestor_depth ot ON (ot.parent_ou = ou.id)
397 ) SELECT cpl.id INTO attr_set.location
398 FROM anscestor_depth a
399 JOIN asset.copy_location cpl ON (cpl.owning_lib = a.id)
400 WHERE LOWER(cpl.name) = LOWER(tmp_attr_set.cl)
401 ORDER BY a.depth DESC
405 attr_set.import_error := 'import.item.invalid.location';
406 attr_set.error_detail := tmp_attr_set.cs;
407 RETURN NEXT attr_set; CONTINUE;
411 attr_set.circulate :=
412 LOWER( SUBSTRING( tmp_attr_set.circ, 1, 1)) IN ('t','y','1')
413 OR LOWER(tmp_attr_set.circ) = 'circulating'; -- BOOL
416 LOWER( SUBSTRING( tmp_attr_set.dep, 1, 1 ) ) IN ('t','y','1')
417 OR LOWER(tmp_attr_set.dep) = 'deposit'; -- BOOL
420 LOWER( SUBSTRING( tmp_attr_set.hold, 1, 1 ) ) IN ('t','y','1')
421 OR LOWER(tmp_attr_set.hold) = 'holdable'; -- BOOL
423 attr_set.opac_visible :=
424 LOWER( SUBSTRING( tmp_attr_set.opac_vis, 1, 1 ) ) IN ('t','y','1')
425 OR LOWER(tmp_attr_set.opac_vis) = 'visible'; -- BOOL
428 LOWER( SUBSTRING( tmp_attr_set.r, 1, 1 ) ) IN ('t','y','1')
429 OR LOWER(tmp_attr_set.r) = 'reference'; -- BOOL
431 attr_set.call_number := tmp_attr_set.cn; -- TEXT
432 attr_set.barcode := tmp_attr_set.bc; -- TEXT,
433 attr_set.alert_message := tmp_attr_set.amessage; -- TEXT,
434 attr_set.pub_note := tmp_attr_set.note; -- TEXT,
435 attr_set.priv_note := tmp_attr_set.pnote; -- TEXT,
436 attr_set.alert_message := tmp_attr_set.amessage; -- TEXT,
437 attr_set.internal_id := tmp_attr_set.internal_id::BIGINT;
439 RETURN NEXT attr_set;