3 SELECT evergreen.upgrade_deps_block_check('0929', :eg_version);
5 INSERT INTO vandelay.import_error ( code, description ) VALUES (
6 'import.item.invalid.stat_cat_format', oils_i18n_gettext('import.item.invalid.stat_cat_format', 'Bad format for stat cat data, should be like: CAT 1|VALUE 1', 'vie', 'description') );
7 INSERT INTO vandelay.import_error ( code, description ) VALUES (
8 'import.item.invalid.stat_cat_data', oils_i18n_gettext('import.item.invalid.stat_cat_data', 'Invalid stat cat data', 'vie', 'description') );
10 ALTER TABLE vandelay.import_item_attr_definition
11 ADD COLUMN stat_cat_data TEXT;
13 ALTER TABLE vandelay.import_item
14 ADD COLUMN stat_cat_data TEXT;
16 CREATE OR REPLACE FUNCTION vandelay.ingest_items ( import_id BIGINT, attr_def_id BIGINT ) RETURNS SETOF vandelay.import_item AS $$
43 attr_set vandelay.import_item%ROWTYPE;
50 SELECT * INTO attr_def FROM vandelay.import_item_attr_definition WHERE id = attr_def_id;
54 attr_set.definition := attr_def.id;
56 -- Build the combined XPath
60 WHEN attr_def.owning_lib IS NULL THEN 'null()'
61 WHEN LENGTH( attr_def.owning_lib ) = 1 THEN '*[@code="' || attr_def.owning_lib || '"]'
62 ELSE '*' || attr_def.owning_lib
67 WHEN attr_def.circ_lib IS NULL THEN 'null()'
68 WHEN LENGTH( attr_def.circ_lib ) = 1 THEN '*[@code="' || attr_def.circ_lib || '"]'
69 ELSE '*' || attr_def.circ_lib
74 WHEN attr_def.call_number IS NULL THEN 'null()'
75 WHEN LENGTH( attr_def.call_number ) = 1 THEN '*[@code="' || attr_def.call_number || '"]'
76 ELSE '*' || attr_def.call_number
81 WHEN attr_def.copy_number IS NULL THEN 'null()'
82 WHEN LENGTH( attr_def.copy_number ) = 1 THEN '*[@code="' || attr_def.copy_number || '"]'
83 ELSE '*' || attr_def.copy_number
88 WHEN attr_def.status IS NULL THEN 'null()'
89 WHEN LENGTH( attr_def.status ) = 1 THEN '*[@code="' || attr_def.status || '"]'
90 ELSE '*' || attr_def.status
95 WHEN attr_def.location IS NULL THEN 'null()'
96 WHEN LENGTH( attr_def.location ) = 1 THEN '*[@code="' || attr_def.location || '"]'
97 ELSE '*' || attr_def.location
102 WHEN attr_def.circulate IS NULL THEN 'null()'
103 WHEN LENGTH( attr_def.circulate ) = 1 THEN '*[@code="' || attr_def.circulate || '"]'
104 ELSE '*' || attr_def.circulate
109 WHEN attr_def.deposit IS NULL THEN 'null()'
110 WHEN LENGTH( attr_def.deposit ) = 1 THEN '*[@code="' || attr_def.deposit || '"]'
111 ELSE '*' || attr_def.deposit
116 WHEN attr_def.deposit_amount IS NULL THEN 'null()'
117 WHEN LENGTH( attr_def.deposit_amount ) = 1 THEN '*[@code="' || attr_def.deposit_amount || '"]'
118 ELSE '*' || attr_def.deposit_amount
123 WHEN attr_def.ref IS NULL THEN 'null()'
124 WHEN LENGTH( attr_def.ref ) = 1 THEN '*[@code="' || attr_def.ref || '"]'
125 ELSE '*' || attr_def.ref
130 WHEN attr_def.holdable IS NULL THEN 'null()'
131 WHEN LENGTH( attr_def.holdable ) = 1 THEN '*[@code="' || attr_def.holdable || '"]'
132 ELSE '*' || attr_def.holdable
137 WHEN attr_def.price IS NULL THEN 'null()'
138 WHEN LENGTH( attr_def.price ) = 1 THEN '*[@code="' || attr_def.price || '"]'
139 ELSE '*' || attr_def.price
144 WHEN attr_def.barcode IS NULL THEN 'null()'
145 WHEN LENGTH( attr_def.barcode ) = 1 THEN '*[@code="' || attr_def.barcode || '"]'
146 ELSE '*' || attr_def.barcode
151 WHEN attr_def.circ_modifier IS NULL THEN 'null()'
152 WHEN LENGTH( attr_def.circ_modifier ) = 1 THEN '*[@code="' || attr_def.circ_modifier || '"]'
153 ELSE '*' || attr_def.circ_modifier
158 WHEN attr_def.circ_as_type IS NULL THEN 'null()'
159 WHEN LENGTH( attr_def.circ_as_type ) = 1 THEN '*[@code="' || attr_def.circ_as_type || '"]'
160 ELSE '*' || attr_def.circ_as_type
165 WHEN attr_def.alert_message IS NULL THEN 'null()'
166 WHEN LENGTH( attr_def.alert_message ) = 1 THEN '*[@code="' || attr_def.alert_message || '"]'
167 ELSE '*' || attr_def.alert_message
172 WHEN attr_def.opac_visible IS NULL THEN 'null()'
173 WHEN LENGTH( attr_def.opac_visible ) = 1 THEN '*[@code="' || attr_def.opac_visible || '"]'
174 ELSE '*' || attr_def.opac_visible
179 WHEN attr_def.pub_note IS NULL THEN 'null()'
180 WHEN LENGTH( attr_def.pub_note ) = 1 THEN '*[@code="' || attr_def.pub_note || '"]'
181 ELSE '*' || attr_def.pub_note
185 WHEN attr_def.priv_note IS NULL THEN 'null()'
186 WHEN LENGTH( attr_def.priv_note ) = 1 THEN '*[@code="' || attr_def.priv_note || '"]'
187 ELSE '*' || attr_def.priv_note
192 WHEN attr_def.internal_id IS NULL THEN 'null()'
193 WHEN LENGTH( attr_def.internal_id ) = 1 THEN '*[@code="' || attr_def.internal_id || '"]'
194 ELSE '*' || attr_def.internal_id
199 WHEN attr_def.stat_cat_data IS NULL THEN 'null()'
200 WHEN LENGTH( attr_def.stat_cat_data ) = 1 THEN '*[@code="' || attr_def.stat_cat_data || '"]'
201 ELSE '*' || attr_def.stat_cat_data
206 xpaths := ARRAY[owning_lib, circ_lib, call_number, copy_number, status, location, circulate,
207 deposit, deposit_amount, ref, holdable, price, barcode, circ_modifier, circ_as_type,
208 alert_message, pub_note, priv_note, internal_id, stat_cat_data, opac_visible];
212 FROM oils_xpath_tag_to_table( (SELECT marc FROM vandelay.queued_bib_record WHERE id = import_id), attr_def.tag, xpaths)
213 AS t( ol TEXT, clib TEXT, cn TEXT, cnum TEXT, cs TEXT, cl TEXT, circ TEXT,
214 dep TEXT, dep_amount TEXT, r TEXT, hold TEXT, pr TEXT, bc TEXT, circ_mod TEXT,
215 circ_as TEXT, amessage TEXT, note TEXT, pnote TEXT, internal_id TEXT,
216 stat_cat_data TEXT, opac_vis TEXT )
219 attr_set.import_error := NULL;
220 attr_set.error_detail := NULL;
221 attr_set.deposit_amount := NULL;
222 attr_set.copy_number := NULL;
223 attr_set.price := NULL;
224 attr_set.circ_modifier := NULL;
225 attr_set.location := NULL;
226 attr_set.barcode := NULL;
227 attr_set.call_number := NULL;
229 IF tmp_attr_set.pr != '' THEN
230 tmp_str = REGEXP_REPLACE(tmp_attr_set.pr, E'[^0-9\\.]', '', 'g');
232 attr_set.import_error := 'import.item.invalid.price';
233 attr_set.error_detail := tmp_attr_set.pr; -- original value
234 RETURN NEXT attr_set; CONTINUE;
236 attr_set.price := tmp_str::NUMERIC(8,2);
239 IF tmp_attr_set.dep_amount != '' THEN
240 tmp_str = REGEXP_REPLACE(tmp_attr_set.dep_amount, E'[^0-9\\.]', '', 'g');
242 attr_set.import_error := 'import.item.invalid.deposit_amount';
243 attr_set.error_detail := tmp_attr_set.dep_amount;
244 RETURN NEXT attr_set; CONTINUE;
246 attr_set.deposit_amount := tmp_str::NUMERIC(8,2);
249 IF tmp_attr_set.cnum != '' THEN
250 tmp_str = REGEXP_REPLACE(tmp_attr_set.cnum, E'[^0-9]', '', 'g');
252 attr_set.import_error := 'import.item.invalid.copy_number';
253 attr_set.error_detail := tmp_attr_set.cnum;
254 RETURN NEXT attr_set; CONTINUE;
256 attr_set.copy_number := tmp_str::INT;
259 IF tmp_attr_set.ol != '' THEN
260 SELECT id INTO attr_set.owning_lib FROM actor.org_unit WHERE shortname = UPPER(tmp_attr_set.ol); -- INT
262 attr_set.import_error := 'import.item.invalid.owning_lib';
263 attr_set.error_detail := tmp_attr_set.ol;
264 RETURN NEXT attr_set; CONTINUE;
268 IF tmp_attr_set.clib != '' THEN
269 SELECT id INTO attr_set.circ_lib FROM actor.org_unit WHERE shortname = UPPER(tmp_attr_set.clib); -- INT
271 attr_set.import_error := 'import.item.invalid.circ_lib';
272 attr_set.error_detail := tmp_attr_set.clib;
273 RETURN NEXT attr_set; CONTINUE;
277 IF tmp_attr_set.cs != '' THEN
278 SELECT id INTO attr_set.status FROM config.copy_status WHERE LOWER(name) = LOWER(tmp_attr_set.cs); -- INT
280 attr_set.import_error := 'import.item.invalid.status';
281 attr_set.error_detail := tmp_attr_set.cs;
282 RETURN NEXT attr_set; CONTINUE;
286 IF COALESCE(tmp_attr_set.circ_mod, '') = '' THEN
288 -- no circ mod defined, see if we should apply a default
289 SELECT INTO attr_set.circ_modifier TRIM(BOTH '"' FROM value)
290 FROM actor.org_unit_ancestor_setting(
291 'vandelay.item.circ_modifier.default',
295 -- make sure the value from the org setting is still valid
296 PERFORM 1 FROM config.circ_modifier WHERE code = attr_set.circ_modifier;
298 attr_set.import_error := 'import.item.invalid.circ_modifier';
299 attr_set.error_detail := tmp_attr_set.circ_mod;
300 RETURN NEXT attr_set; CONTINUE;
305 SELECT code INTO attr_set.circ_modifier FROM config.circ_modifier WHERE code = tmp_attr_set.circ_mod;
307 attr_set.import_error := 'import.item.invalid.circ_modifier';
308 attr_set.error_detail := tmp_attr_set.circ_mod;
309 RETURN NEXT attr_set; CONTINUE;
313 IF tmp_attr_set.circ_as != '' THEN
314 SELECT code INTO attr_set.circ_as_type FROM config.coded_value_map WHERE ctype = 'item_type' AND code = tmp_attr_set.circ_as;
316 attr_set.import_error := 'import.item.invalid.circ_as_type';
317 attr_set.error_detail := tmp_attr_set.circ_as;
318 RETURN NEXT attr_set; CONTINUE;
322 IF COALESCE(tmp_attr_set.cl, '') = '' THEN
323 -- no location specified, see if we should apply a default
325 SELECT INTO attr_set.location TRIM(BOTH '"' FROM value)
326 FROM actor.org_unit_ancestor_setting(
327 'vandelay.item.copy_location.default',
331 -- make sure the value from the org setting is still valid
332 PERFORM 1 FROM asset.copy_location WHERE id = attr_set.location;
334 attr_set.import_error := 'import.item.invalid.location';
335 attr_set.error_detail := tmp_attr_set.cs;
336 RETURN NEXT attr_set; CONTINUE;
340 -- search up the org unit tree for a matching copy location
341 WITH RECURSIVE anscestor_depth AS (
345 FROM actor.org_unit ou
346 JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
347 WHERE ou.id = COALESCE(attr_set.owning_lib, attr_set.circ_lib)
352 FROM actor.org_unit ou
353 JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
354 JOIN anscestor_depth ot ON (ot.parent_ou = ou.id)
355 ) SELECT cpl.id INTO attr_set.location
356 FROM anscestor_depth a
357 JOIN asset.copy_location cpl ON (cpl.owning_lib = a.id)
358 WHERE LOWER(cpl.name) = LOWER(tmp_attr_set.cl)
359 ORDER BY a.depth DESC
363 attr_set.import_error := 'import.item.invalid.location';
364 attr_set.error_detail := tmp_attr_set.cs;
365 RETURN NEXT attr_set; CONTINUE;
369 attr_set.circulate :=
370 LOWER( SUBSTRING( tmp_attr_set.circ, 1, 1)) IN ('t','y','1')
371 OR LOWER(tmp_attr_set.circ) = 'circulating'; -- BOOL
374 LOWER( SUBSTRING( tmp_attr_set.dep, 1, 1 ) ) IN ('t','y','1')
375 OR LOWER(tmp_attr_set.dep) = 'deposit'; -- BOOL
378 LOWER( SUBSTRING( tmp_attr_set.hold, 1, 1 ) ) IN ('t','y','1')
379 OR LOWER(tmp_attr_set.hold) = 'holdable'; -- BOOL
381 attr_set.opac_visible :=
382 LOWER( SUBSTRING( tmp_attr_set.opac_vis, 1, 1 ) ) IN ('t','y','1')
383 OR LOWER(tmp_attr_set.opac_vis) = 'visible'; -- BOOL
386 LOWER( SUBSTRING( tmp_attr_set.r, 1, 1 ) ) IN ('t','y','1')
387 OR LOWER(tmp_attr_set.r) = 'reference'; -- BOOL
389 attr_set.call_number := tmp_attr_set.cn; -- TEXT
390 attr_set.barcode := tmp_attr_set.bc; -- TEXT,
391 attr_set.alert_message := tmp_attr_set.amessage; -- TEXT,
392 attr_set.pub_note := tmp_attr_set.note; -- TEXT,
393 attr_set.priv_note := tmp_attr_set.pnote; -- TEXT,
394 attr_set.alert_message := tmp_attr_set.amessage; -- TEXT,
395 attr_set.internal_id := tmp_attr_set.internal_id::BIGINT;
396 attr_set.stat_cat_data := tmp_attr_set.stat_cat_data; -- TEXT,
398 RETURN NEXT attr_set;
409 CREATE OR REPLACE FUNCTION vandelay.ingest_bib_items ( ) RETURNS TRIGGER AS $func$
412 item_data vandelay.import_item%ROWTYPE;
415 IF TG_OP IN ('INSERT','UPDATE') AND NEW.imported_as IS NOT NULL THEN
419 SELECT item_attr_def INTO attr_def FROM vandelay.bib_queue WHERE id = NEW.queue;
421 FOR item_data IN SELECT * FROM vandelay.ingest_items( NEW.id::BIGINT, attr_def ) LOOP
422 INSERT INTO vandelay.import_item (
450 item_data.definition,
451 item_data.owning_lib,
453 item_data.call_number,
454 item_data.copy_number,
459 item_data.deposit_amount,
464 item_data.circ_modifier,
465 item_data.circ_as_type,
466 item_data.alert_message,
469 item_data.internal_id,
470 item_data.opac_visible,
471 item_data.stat_cat_data,
472 item_data.import_error,
473 item_data.error_detail
479 $func$ LANGUAGE PLPGSQL;