3 SELECT evergreen.upgrade_deps_block_check('0975', :eg_version);
5 CREATE OR REPLACE FUNCTION vandelay.ingest_items ( import_id BIGINT, attr_def_id BIGINT ) RETURNS SETOF vandelay.import_item AS $$
33 attr_set vandelay.import_item%ROWTYPE;
40 SELECT * INTO attr_def FROM vandelay.import_item_attr_definition WHERE id = attr_def_id;
44 attr_set.definition := attr_def.id;
46 -- Build the combined XPath
50 WHEN attr_def.owning_lib IS NULL THEN 'null()'
51 WHEN LENGTH( attr_def.owning_lib ) = 1 THEN '*[@code="' || attr_def.owning_lib || '"]'
52 ELSE '*' || attr_def.owning_lib
57 WHEN attr_def.circ_lib IS NULL THEN 'null()'
58 WHEN LENGTH( attr_def.circ_lib ) = 1 THEN '*[@code="' || attr_def.circ_lib || '"]'
59 ELSE '*' || attr_def.circ_lib
64 WHEN attr_def.call_number IS NULL THEN 'null()'
65 WHEN LENGTH( attr_def.call_number ) = 1 THEN '*[@code="' || attr_def.call_number || '"]'
66 ELSE '*' || attr_def.call_number
71 WHEN attr_def.copy_number IS NULL THEN 'null()'
72 WHEN LENGTH( attr_def.copy_number ) = 1 THEN '*[@code="' || attr_def.copy_number || '"]'
73 ELSE '*' || attr_def.copy_number
78 WHEN attr_def.status IS NULL THEN 'null()'
79 WHEN LENGTH( attr_def.status ) = 1 THEN '*[@code="' || attr_def.status || '"]'
80 ELSE '*' || attr_def.status
85 WHEN attr_def.location IS NULL THEN 'null()'
86 WHEN LENGTH( attr_def.location ) = 1 THEN '*[@code="' || attr_def.location || '"]'
87 ELSE '*' || attr_def.location
92 WHEN attr_def.circulate IS NULL THEN 'null()'
93 WHEN LENGTH( attr_def.circulate ) = 1 THEN '*[@code="' || attr_def.circulate || '"]'
94 ELSE '*' || attr_def.circulate
99 WHEN attr_def.deposit IS NULL THEN 'null()'
100 WHEN LENGTH( attr_def.deposit ) = 1 THEN '*[@code="' || attr_def.deposit || '"]'
101 ELSE '*' || attr_def.deposit
106 WHEN attr_def.deposit_amount IS NULL THEN 'null()'
107 WHEN LENGTH( attr_def.deposit_amount ) = 1 THEN '*[@code="' || attr_def.deposit_amount || '"]'
108 ELSE '*' || attr_def.deposit_amount
113 WHEN attr_def.ref IS NULL THEN 'null()'
114 WHEN LENGTH( attr_def.ref ) = 1 THEN '*[@code="' || attr_def.ref || '"]'
115 ELSE '*' || attr_def.ref
120 WHEN attr_def.holdable IS NULL THEN 'null()'
121 WHEN LENGTH( attr_def.holdable ) = 1 THEN '*[@code="' || attr_def.holdable || '"]'
122 ELSE '*' || attr_def.holdable
127 WHEN attr_def.price IS NULL THEN 'null()'
128 WHEN LENGTH( attr_def.price ) = 1 THEN '*[@code="' || attr_def.price || '"]'
129 ELSE '*' || attr_def.price
134 WHEN attr_def.barcode IS NULL THEN 'null()'
135 WHEN LENGTH( attr_def.barcode ) = 1 THEN '*[@code="' || attr_def.barcode || '"]'
136 ELSE '*' || attr_def.barcode
141 WHEN attr_def.circ_modifier IS NULL THEN 'null()'
142 WHEN LENGTH( attr_def.circ_modifier ) = 1 THEN '*[@code="' || attr_def.circ_modifier || '"]'
143 ELSE '*' || attr_def.circ_modifier
148 WHEN attr_def.circ_as_type IS NULL THEN 'null()'
149 WHEN LENGTH( attr_def.circ_as_type ) = 1 THEN '*[@code="' || attr_def.circ_as_type || '"]'
150 ELSE '*' || attr_def.circ_as_type
155 WHEN attr_def.alert_message IS NULL THEN 'null()'
156 WHEN LENGTH( attr_def.alert_message ) = 1 THEN '*[@code="' || attr_def.alert_message || '"]'
157 ELSE '*' || attr_def.alert_message
162 WHEN attr_def.opac_visible IS NULL THEN 'null()'
163 WHEN LENGTH( attr_def.opac_visible ) = 1 THEN '*[@code="' || attr_def.opac_visible || '"]'
164 ELSE '*' || attr_def.opac_visible
169 WHEN attr_def.pub_note IS NULL THEN 'null()'
170 WHEN LENGTH( attr_def.pub_note ) = 1 THEN '*[@code="' || attr_def.pub_note || '"]'
171 ELSE '*' || attr_def.pub_note
175 WHEN attr_def.priv_note IS NULL THEN 'null()'
176 WHEN LENGTH( attr_def.priv_note ) = 1 THEN '*[@code="' || attr_def.priv_note || '"]'
177 ELSE '*' || attr_def.priv_note
182 WHEN attr_def.internal_id IS NULL THEN 'null()'
183 WHEN LENGTH( attr_def.internal_id ) = 1 THEN '*[@code="' || attr_def.internal_id || '"]'
184 ELSE '*' || attr_def.internal_id
189 WHEN attr_def.stat_cat_data IS NULL THEN 'null()'
190 WHEN LENGTH( attr_def.stat_cat_data ) = 1 THEN '*[@code="' || attr_def.stat_cat_data || '"]'
191 ELSE '*' || attr_def.stat_cat_data
196 WHEN attr_def.parts_data IS NULL THEN 'null()'
197 WHEN LENGTH( attr_def.parts_data ) = 1 THEN '*[@code="' || attr_def.parts_data || '"]'
198 ELSE '*' || attr_def.parts_data
203 xpaths := ARRAY[owning_lib, circ_lib, call_number, copy_number, status, location, circulate,
204 deposit, deposit_amount, ref, holdable, price, barcode, circ_modifier, circ_as_type,
205 alert_message, pub_note, priv_note, internal_id, stat_cat_data, parts_data, opac_visible];
209 FROM oils_xpath_tag_to_table( (SELECT marc FROM vandelay.queued_bib_record WHERE id = import_id), attr_def.tag, xpaths)
210 AS t( ol TEXT, clib TEXT, cn TEXT, cnum TEXT, cs TEXT, cl TEXT, circ TEXT,
211 dep TEXT, dep_amount TEXT, r TEXT, hold TEXT, pr TEXT, bc TEXT, circ_mod TEXT,
212 circ_as TEXT, amessage TEXT, note TEXT, pnote TEXT, internal_id TEXT,
213 stat_cat_data TEXT, parts_data TEXT, opac_vis TEXT )
216 attr_set.import_error := NULL;
217 attr_set.error_detail := NULL;
218 attr_set.deposit_amount := NULL;
219 attr_set.copy_number := NULL;
220 attr_set.price := NULL;
221 attr_set.circ_modifier := NULL;
222 attr_set.location := NULL;
223 attr_set.barcode := NULL;
224 attr_set.call_number := NULL;
226 IF tmp_attr_set.pr != '' THEN
227 tmp_str = REGEXP_REPLACE(tmp_attr_set.pr, E'[^0-9\\.]', '', 'g');
229 attr_set.import_error := 'import.item.invalid.price';
230 attr_set.error_detail := tmp_attr_set.pr; -- original value
231 RETURN NEXT attr_set; CONTINUE;
233 attr_set.price := tmp_str::NUMERIC(8,2);
236 IF tmp_attr_set.dep_amount != '' THEN
237 tmp_str = REGEXP_REPLACE(tmp_attr_set.dep_amount, E'[^0-9\\.]', '', 'g');
239 attr_set.import_error := 'import.item.invalid.deposit_amount';
240 attr_set.error_detail := tmp_attr_set.dep_amount;
241 RETURN NEXT attr_set; CONTINUE;
243 attr_set.deposit_amount := tmp_str::NUMERIC(8,2);
246 IF tmp_attr_set.cnum != '' THEN
247 tmp_str = REGEXP_REPLACE(tmp_attr_set.cnum, E'[^0-9]', '', 'g');
249 attr_set.import_error := 'import.item.invalid.copy_number';
250 attr_set.error_detail := tmp_attr_set.cnum;
251 RETURN NEXT attr_set; CONTINUE;
253 attr_set.copy_number := tmp_str::INT;
256 IF tmp_attr_set.ol != '' THEN
257 SELECT id INTO attr_set.owning_lib FROM actor.org_unit WHERE shortname = UPPER(tmp_attr_set.ol); -- INT
259 attr_set.import_error := 'import.item.invalid.owning_lib';
260 attr_set.error_detail := tmp_attr_set.ol;
261 RETURN NEXT attr_set; CONTINUE;
265 IF tmp_attr_set.clib != '' THEN
266 SELECT id INTO attr_set.circ_lib FROM actor.org_unit WHERE shortname = UPPER(tmp_attr_set.clib); -- INT
268 attr_set.import_error := 'import.item.invalid.circ_lib';
269 attr_set.error_detail := tmp_attr_set.clib;
270 RETURN NEXT attr_set; CONTINUE;
274 IF tmp_attr_set.cs != '' THEN
275 SELECT id INTO attr_set.status FROM config.copy_status WHERE LOWER(name) = LOWER(tmp_attr_set.cs); -- INT
277 attr_set.import_error := 'import.item.invalid.status';
278 attr_set.error_detail := tmp_attr_set.cs;
279 RETURN NEXT attr_set; CONTINUE;
283 IF COALESCE(tmp_attr_set.circ_mod, '') = '' THEN
285 -- no circ mod defined, see if we should apply a default
286 SELECT INTO attr_set.circ_modifier TRIM(BOTH '"' FROM value)
287 FROM actor.org_unit_ancestor_setting(
288 'vandelay.item.circ_modifier.default',
292 -- make sure the value from the org setting is still valid
293 PERFORM 1 FROM config.circ_modifier WHERE code = attr_set.circ_modifier;
295 attr_set.import_error := 'import.item.invalid.circ_modifier';
296 attr_set.error_detail := tmp_attr_set.circ_mod;
297 RETURN NEXT attr_set; CONTINUE;
302 SELECT code INTO attr_set.circ_modifier FROM config.circ_modifier WHERE code = tmp_attr_set.circ_mod;
304 attr_set.import_error := 'import.item.invalid.circ_modifier';
305 attr_set.error_detail := tmp_attr_set.circ_mod;
306 RETURN NEXT attr_set; CONTINUE;
310 IF tmp_attr_set.circ_as != '' THEN
311 SELECT code INTO attr_set.circ_as_type FROM config.coded_value_map WHERE ctype = 'item_type' AND code = tmp_attr_set.circ_as;
313 attr_set.import_error := 'import.item.invalid.circ_as_type';
314 attr_set.error_detail := tmp_attr_set.circ_as;
315 RETURN NEXT attr_set; CONTINUE;
319 IF COALESCE(tmp_attr_set.cl, '') = '' THEN
320 -- no location specified, see if we should apply a default
322 SELECT INTO attr_set.location TRIM(BOTH '"' FROM value)
323 FROM actor.org_unit_ancestor_setting(
324 'vandelay.item.copy_location.default',
328 -- make sure the value from the org setting is still valid
329 PERFORM 1 FROM asset.copy_location
330 WHERE id = attr_set.location AND NOT deleted;
332 attr_set.import_error := 'import.item.invalid.location';
333 attr_set.error_detail := tmp_attr_set.cs;
334 RETURN NEXT attr_set; CONTINUE;
338 -- search up the org unit tree for a matching copy location
339 WITH RECURSIVE anscestor_depth AS (
343 FROM actor.org_unit ou
344 JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
345 WHERE ou.id = COALESCE(attr_set.owning_lib, attr_set.circ_lib)
350 FROM actor.org_unit ou
351 JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
352 JOIN anscestor_depth ot ON (ot.parent_ou = ou.id)
353 ) SELECT cpl.id INTO attr_set.location
354 FROM anscestor_depth a
355 JOIN asset.copy_location cpl ON (cpl.owning_lib = a.id)
356 WHERE LOWER(cpl.name) = LOWER(tmp_attr_set.cl)
358 ORDER BY a.depth DESC
362 attr_set.import_error := 'import.item.invalid.location';
363 attr_set.error_detail := tmp_attr_set.cs;
364 RETURN NEXT attr_set; CONTINUE;
368 attr_set.circulate :=
369 LOWER( SUBSTRING( tmp_attr_set.circ, 1, 1)) IN ('t','y','1')
370 OR LOWER(tmp_attr_set.circ) = 'circulating'; -- BOOL
373 LOWER( SUBSTRING( tmp_attr_set.dep, 1, 1 ) ) IN ('t','y','1')
374 OR LOWER(tmp_attr_set.dep) = 'deposit'; -- BOOL
377 LOWER( SUBSTRING( tmp_attr_set.hold, 1, 1 ) ) IN ('t','y','1')
378 OR LOWER(tmp_attr_set.hold) = 'holdable'; -- BOOL
380 attr_set.opac_visible :=
381 LOWER( SUBSTRING( tmp_attr_set.opac_vis, 1, 1 ) ) IN ('t','y','1')
382 OR LOWER(tmp_attr_set.opac_vis) = 'visible'; -- BOOL
385 LOWER( SUBSTRING( tmp_attr_set.r, 1, 1 ) ) IN ('t','y','1')
386 OR LOWER(tmp_attr_set.r) = 'reference'; -- BOOL
388 attr_set.call_number := tmp_attr_set.cn; -- TEXT
389 attr_set.barcode := tmp_attr_set.bc; -- TEXT,
390 attr_set.alert_message := tmp_attr_set.amessage; -- TEXT,
391 attr_set.pub_note := tmp_attr_set.note; -- TEXT,
392 attr_set.priv_note := tmp_attr_set.pnote; -- TEXT,
393 attr_set.alert_message := tmp_attr_set.amessage; -- TEXT,
394 attr_set.internal_id := tmp_attr_set.internal_id::BIGINT;
395 attr_set.stat_cat_data := tmp_attr_set.stat_cat_data; -- TEXT,
396 attr_set.parts_data := tmp_attr_set.parts_data; -- TEXT,
398 RETURN NEXT attr_set;