2 * Copyright (C) 2009 Equinox Software, Inc.
3 * Mike Rylander <miker@esilibrary.com>
5 * This program is free software; you can redistribute it and/or
6 * modify it under the terms of the GNU General Public License
7 * as published by the Free Software Foundation; either version 2
8 * of the License, or (at your option) any later version.
10 * This program is distributed in the hope that it will be useful,
11 * but WITHOUT ANY WARRANTY; without even the implied warranty of
12 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
13 * GNU General Public License for more details.
19 DROP SCHEMA IF EXISTS acq CASCADE;
20 DROP SCHEMA IF EXISTS serial CASCADE;
24 INSERT INTO config.upgrade_log (version) VALUES ('1.6.0.0');
26 CREATE TABLE config.standing_penalty (
27 id SERIAL PRIMARY KEY,
28 name TEXT NOT NULL UNIQUE,
32 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (1,'PATRON_EXCEEDS_FINES','Patron exceeds fine threshold','CIRC|HOLD|RENEW');
33 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (2,'PATRON_EXCEEDS_OVERDUE_COUNT','Patron exceeds max overdue item threshold','CIRC|HOLD|RENEW');
34 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (3,'PATRON_EXCEEDS_CHECKOUT_COUNT','Patron exceeds max checked out item threshold','CIRC');
35 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (4,'PATRON_EXCEEDS_COLLECTIONS_WARNING','Patron exceeds pre-collections warning fine threshold','CIRC|HOLD|RENEW');
37 INSERT INTO config.standing_penalty (id,name,label) VALUES (20,'ALERT_NOTE','Alerting Note, no blocks');
38 INSERT INTO config.standing_penalty (id,name,label) VALUES (21,'SILENT_NOTE','Note, no blocks');
39 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (22,'STAFF_C','Alerting block on Circ','CIRC');
40 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (23,'STAFF_CH','Alerting block on Circ and Hold','CIRC|HOLD');
41 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (24,'STAFF_CR','Alerting block on Circ and Renew','CIRC|RENEW');
42 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (25,'STAFF_CHR','Alerting block on Circ, Hold and Renew','CIRC|HOLD|RENEW');
43 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (26,'STAFF_HR','Alerting block on Hold and Renew','HOLD|RENEW');
44 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (27,'STAFF_H','Alerting block on Hold','HOLD');
45 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (28,'STAFF_R','Alerting block on Renew','RENEW');
47 SELECT SETVAL('config.standing_penalty_id_seq', 100);
49 CREATE TABLE config.billing_type (
50 id SERIAL PRIMARY KEY,
52 owner INT NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
53 default_price NUMERIC(6,2),
54 CONSTRAINT billing_type_once_per_lib UNIQUE (name, owner)
57 INSERT INTO config.billing_type (id, name, owner) VALUES ( 1, 'Overdue Materials', 1);
58 INSERT INTO config.billing_type (id, name, owner) VALUES ( 2, 'Long Overdue Collection Fee', 1);
59 INSERT INTO config.billing_type (id, name, owner) VALUES ( 3, 'Lost Materials', 1);
60 INSERT INTO config.billing_type (id, name, owner) VALUES ( 4, 'Lost Materials Processing Fee', 1);
61 INSERT INTO config.billing_type (id, name, owner) VALUES ( 5, 'System: Deposit', 1);
62 INSERT INTO config.billing_type (id, name, owner) VALUES ( 6, 'System: Rental', 1);
63 INSERT INTO config.billing_type (id, name, owner) VALUES ( 7, 'Damaged Item', 1);
64 INSERT INTO config.billing_type (id, name, owner) VALUES ( 8, 'Damaged Item Processing Fee', 1);
65 INSERT INTO config.billing_type (id, name, owner) VALUES ( 9, 'Notification Fee', 1);
67 SELECT SETVAL('config.billing_type_id_seq'::TEXT, 100);
69 ALTER TABLE actor.usr ADD COLUMN alias TEXT;
70 ALTER TABLE actor.usr ADD COLUMN juvenile BOOL;
71 ALTER TABLE auditor.actor_usr_history ADD COLUMN alias TEXT;
72 ALTER TABLE auditor.actor_usr_history ADD COLUMN juvenile BOOL;
74 ALTER TABLE actor.usr ALTER COLUMN juvenile SET DEFAULT FALSE;
75 UPDATE actor.usr SET juvenile=FALSE;
78 DROP TABLE actor.usr_standing_penalty;
79 CREATE TABLE actor.usr_standing_penalty (
80 id SERIAL PRIMARY KEY,
81 org_unit INT NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
82 usr INT NOT NULL REFERENCES actor.usr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
83 standing_penalty INT NOT NULL REFERENCES config.standing_penalty (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
84 staff INT REFERENCES actor.usr (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
85 set_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
86 stop_date TIMESTAMP WITH TIME ZONE,
89 CREATE INDEX actor_usr_standing_penalty_usr_idx ON actor.usr_standing_penalty (usr);
92 ALTER TABLE actor.usr_address ADD COLUMN pending BOOL;
93 ALTER TABLE actor.usr_address ADD COLUMN replaces INT;
94 ALTER TABLE auditor.actor_usr_address_history ADD COLUMN pending BOOL;
95 ALTER TABLE auditor.actor_usr_address_history ADD COLUMN replaces INT;
97 ALTER TABLE actor.usr_address ALTER COLUMN pending SET DEFAULT FALSE;
98 UPDATE actor.usr_address SET pending = FALSE;
101 CREATE TABLE permission.grp_penalty_threshold (
102 id SERIAL PRIMARY KEY,
103 grp INT NOT NULL REFERENCES permission.grp_tree (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
104 org_unit INT NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
105 penalty INT NOT NULL REFERENCES config.standing_penalty (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
106 threshold NUMERIC(8,2) NOT NULL,
107 CONSTRAINT penalty_grp_once UNIQUE (grp,penalty)
110 INSERT INTO permission.grp_penalty_threshold (grp,org_unit,penalty,threshold) VALUES (1,1,1,10.0);
111 INSERT INTO permission.grp_penalty_threshold (grp,org_unit,penalty,threshold) VALUES (1,1,2,10.0);
112 INSERT INTO permission.grp_penalty_threshold (grp,org_unit,penalty,threshold) VALUES (1,1,3,10.0);
113 SELECT SETVAL('permission.grp_penalty_threshold_id_seq'::TEXT, (SELECT MAX(id) FROM permission.grp_penalty_threshold));
117 CREATE OR REPLACE FUNCTION permission.usr_has_perm_at_nd(
121 RETURNS SETOF INTEGER AS $$
123 -- Return a set of all the org units for which a given user has a given
124 -- permission, granted directly (not through inheritance from a parent
127 -- The permissions apply to a minimum depth of the org unit hierarchy,
128 -- for the org unit(s) to which the user is assigned. (They also apply
129 -- to the subordinates of those org units, but we don't report the
130 -- subordinates here.)
132 -- For purposes of this function, the permission.usr_work_ou_map table
133 -- defines which users belong to which org units. I.e. we ignore the
134 -- home_ou column of actor.usr.
136 -- The result set may contain duplicates, which should be eliminated
137 -- by a DISTINCT clause.
146 n_curr_depth INTEGER;
149 -- Check for superuser
159 return; -- No user? No permissions.
162 -- Super user has all permissions everywhere
172 RETURN NEXT n_work_ou;
177 -- Translate the permission name
178 -- to a numeric permission id
188 RETURN; -- No such permission
191 -- Find the highest-level org unit (i.e. the minimum depth)
192 -- to which the permission is applied for this user
194 -- This query is modified from the one in permission.usr_perms().
196 SELECT INTO n_min_depth
200 FROM permission.usr_perm_map upm
201 WHERE upm.usr = user_id
202 AND upm.perm = n_perm
205 FROM permission.grp_perm_map gpm
206 WHERE gpm.perm = n_perm
208 SELECT (permission.grp_ancestors(
209 (SELECT profile FROM actor.usr WHERE id = user_id)
214 FROM permission.grp_perm_map p
215 WHERE p.perm = n_perm
217 SELECT (permission.grp_ancestors(m.grp)).id
218 FROM permission.usr_grp_map m
219 WHERE m.usr = user_id
224 RETURN; -- No such permission for this user
227 -- Identify the org units to which the user is assigned. Note that
228 -- we pay no attention to the home_ou column in actor.usr.
234 permission.usr_work_ou_map
237 LOOP -- For each org unit to which the user is assigned
239 -- Determine the level of the org unit by a lookup in actor.org_unit_type.
240 -- We take it on faith that this depth agrees with the actual hierarchy
241 -- defined in actor.org_unit.
246 actor.org_unit_type type
247 INNER JOIN actor.org_unit ou
248 ON ( ou.ou_type = type.id )
253 CONTINUE; -- Maybe raise exception?
256 -- Compare the depth of the work org unit to the
257 -- minimum depth, and branch accordingly
259 IF n_depth = n_min_depth THEN
261 -- The org unit is at the right depth, so return it.
263 RETURN NEXT n_work_ou;
264 ELSIF n_depth > n_min_depth THEN
266 -- Traverse the org unit tree toward the root,
267 -- until you reach the minimum depth determined above
269 n_curr_depth := n_depth;
270 n_curr_ou := n_work_ou;
271 WHILE n_curr_depth > n_min_depth LOOP
272 SELECT INTO n_curr_ou
280 n_curr_depth := n_curr_depth - 1;
283 -- This can happen only if the hierarchy defined in
284 -- actor.org_unit is corrupted, or out of sync with
285 -- the depths defined in actor.org_unit_type.
286 -- Maybe we should raise an exception here, instead
287 -- of silently ignoring the problem.
294 IF n_curr_ou IS NOT NULL THEN
295 RETURN NEXT n_curr_ou;
299 -- The permission applies only at a depth greater than the work org unit.
300 -- Use connectby() to find all dependent org units at the specified depth.
305 'actor.org_unit', -- table name
307 'parent_ou', -- recursive foreign key
308 n_work_ou::TEXT, -- id of starting point
309 (n_min_depth - n_depth) -- max depth to search, relative
310 ) -- to starting point
312 ou text, -- dependent org unit
313 parent_ou text, -- (ignore)
314 level int -- depth relative to starting point
317 level = n_min_depth - n_depth
319 RETURN NEXT n_curr_ou;
328 $$ LANGUAGE 'plpgsql';
331 CREATE OR REPLACE FUNCTION permission.usr_has_perm_at_all_nd(
335 RETURNS SETOF INTEGER AS $$
337 -- Return a set of all the org units for which a given user has a given
338 -- permission, granted either directly or through inheritance from a parent
341 -- The permissions apply to a minimum depth of the org unit hierarchy, and
342 -- to the subordinates of those org units, for the org unit(s) to which the
345 -- For purposes of this function, the permission.usr_work_ou_map table
346 -- assigns users to org units. I.e. we ignore the home_ou column of actor.usr.
348 -- The result set may contain duplicates, which should be eliminated
349 -- by a DISTINCT clause.
356 SELECT DISTINCT * FROM permission.usr_has_perm_at_nd( user_id, perm_code )
359 -- The permission applies only at a depth greater than the work org unit.
360 -- Use connectby() to find all dependent org units at the specified depth.
365 'actor.org_unit', -- table name
367 'parent_ou', -- recursive foreign key
368 n_head_ou::TEXT, -- id of starting point
369 0 -- no limit on search depth
372 ou text, -- dependent org unit
373 parent_ou text, -- (ignore)
374 level int -- (ignore)
377 RETURN NEXT n_child_ou;
384 $$ LANGUAGE 'plpgsql';
387 CREATE OR REPLACE FUNCTION permission.usr_has_perm_at(
391 RETURNS SETOF INTEGER AS $$
392 SELECT DISTINCT * FROM permission.usr_has_perm_at_nd( $1, $2 );
396 CREATE OR REPLACE FUNCTION permission.usr_has_perm_at_all(
400 RETURNS SETOF INTEGER AS $$
401 SELECT DISTINCT * FROM permission.usr_has_perm_at_all_nd( $1, $2 );
406 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;
437 SELECT * INTO attr_def FROM vandelay.import_item_attr_definition WHERE id = attr_def_id;
441 attr_set.definition := attr_def.id;
443 -- Build the combined XPath
447 WHEN attr_def.owning_lib IS NULL THEN 'null()'
448 WHEN LENGTH( attr_def.owning_lib ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.owning_lib || '"]'
449 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.owning_lib
454 WHEN attr_def.circ_lib IS NULL THEN 'null()'
455 WHEN LENGTH( attr_def.circ_lib ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circ_lib || '"]'
456 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circ_lib
461 WHEN attr_def.call_number IS NULL THEN 'null()'
462 WHEN LENGTH( attr_def.call_number ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.call_number || '"]'
463 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.call_number
468 WHEN attr_def.copy_number IS NULL THEN 'null()'
469 WHEN LENGTH( attr_def.copy_number ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.copy_number || '"]'
470 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.copy_number
475 WHEN attr_def.status IS NULL THEN 'null()'
476 WHEN LENGTH( attr_def.status ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.status || '"]'
477 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.status
482 WHEN attr_def.location IS NULL THEN 'null()'
483 WHEN LENGTH( attr_def.location ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.location || '"]'
484 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.location
489 WHEN attr_def.circulate IS NULL THEN 'null()'
490 WHEN LENGTH( attr_def.circulate ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circulate || '"]'
491 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circulate
496 WHEN attr_def.deposit IS NULL THEN 'null()'
497 WHEN LENGTH( attr_def.deposit ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.deposit || '"]'
498 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.deposit
503 WHEN attr_def.deposit_amount IS NULL THEN 'null()'
504 WHEN LENGTH( attr_def.deposit_amount ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.deposit_amount || '"]'
505 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.deposit_amount
510 WHEN attr_def.ref IS NULL THEN 'null()'
511 WHEN LENGTH( attr_def.ref ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.ref || '"]'
512 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.ref
517 WHEN attr_def.holdable IS NULL THEN 'null()'
518 WHEN LENGTH( attr_def.holdable ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.holdable || '"]'
519 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.holdable
524 WHEN attr_def.price IS NULL THEN 'null()'
525 WHEN LENGTH( attr_def.price ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.price || '"]'
526 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.price
531 WHEN attr_def.barcode IS NULL THEN 'null()'
532 WHEN LENGTH( attr_def.barcode ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.barcode || '"]'
533 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.barcode
538 WHEN attr_def.circ_modifier IS NULL THEN 'null()'
539 WHEN LENGTH( attr_def.circ_modifier ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circ_modifier || '"]'
540 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circ_modifier
545 WHEN attr_def.circ_as_type IS NULL THEN 'null()'
546 WHEN LENGTH( attr_def.circ_as_type ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circ_as_type || '"]'
547 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circ_as_type
552 WHEN attr_def.alert_message IS NULL THEN 'null()'
553 WHEN LENGTH( attr_def.alert_message ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.alert_message || '"]'
554 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.alert_message
559 WHEN attr_def.opac_visible IS NULL THEN 'null()'
560 WHEN LENGTH( attr_def.opac_visible ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.opac_visible || '"]'
561 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.opac_visible
566 WHEN attr_def.pub_note IS NULL THEN 'null()'
567 WHEN LENGTH( attr_def.pub_note ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.pub_note || '"]'
568 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.pub_note
572 WHEN attr_def.priv_note IS NULL THEN 'null()'
573 WHEN LENGTH( attr_def.priv_note ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.priv_note || '"]'
574 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.priv_note
581 call_number || '|' ||
582 copy_number || '|' ||
587 deposit_amount || '|' ||
592 circ_modifier || '|' ||
593 circ_as_type || '|' ||
594 alert_message || '|' ||
599 -- RAISE NOTICE 'XPath: %', xpath;
603 FROM xpath_table( 'id', 'marc', 'vandelay.queued_bib_record', xpath, 'id = ' || import_id )
604 AS t( id BIGINT, ol TEXT, clib TEXT, cn TEXT, cnum TEXT, cs TEXT, cl TEXT, circ TEXT,
605 dep TEXT, dep_amount TEXT, r TEXT, hold TEXT, pr TEXT, bc TEXT, circ_mod TEXT,
606 circ_as TEXT, amessage TEXT, note TEXT, pnote TEXT, opac_vis TEXT )
609 tmp_attr_set.pr = REGEXP_REPLACE(tmp_attr_set.pr, E'[^0-9\\.]', '', 'g');
610 tmp_attr_set.dep_amount = REGEXP_REPLACE(tmp_attr_set.dep_amount, E'[^0-9\\.]', '', 'g');
612 tmp_attr_set.pr := NULLIF( tmp_attr_set.pr, '' );
613 tmp_attr_set.dep_amount := NULLIF( tmp_attr_set.dep_amount, '' );
615 SELECT id INTO attr_set.owning_lib FROM actor.org_unit WHERE shortname = UPPER(tmp_attr_set.ol); -- INT
616 SELECT id INTO attr_set.circ_lib FROM actor.org_unit WHERE shortname = UPPER(tmp_attr_set.clib); -- INT
617 SELECT id INTO attr_set.status FROM config.copy_status WHERE LOWER(name) = LOWER(tmp_attr_set.cs); -- INT
619 SELECT id INTO attr_set.location
620 FROM asset.copy_location
621 WHERE LOWER(name) = LOWER(tmp_attr_set.cl)
622 AND asset.copy_location.owning_lib = COALESCE(attr_set.owning_lib, attr_set.circ_lib); -- INT
624 attr_set.circulate :=
625 LOWER( SUBSTRING( tmp_attr_set.circ, 1, 1)) IN ('t','y','1')
626 OR LOWER(tmp_attr_set.circ) = 'circulating'; -- BOOL
629 LOWER( SUBSTRING( tmp_attr_set.dep, 1, 1 ) ) IN ('t','y','1')
630 OR LOWER(tmp_attr_set.dep) = 'deposit'; -- BOOL
633 LOWER( SUBSTRING( tmp_attr_set.hold, 1, 1 ) ) IN ('t','y','1')
634 OR LOWER(tmp_attr_set.hold) = 'holdable'; -- BOOL
636 attr_set.opac_visible :=
637 LOWER( SUBSTRING( tmp_attr_set.opac_vis, 1, 1 ) ) IN ('t','y','1')
638 OR LOWER(tmp_attr_set.opac_vis) = 'visible'; -- BOOL
641 LOWER( SUBSTRING( tmp_attr_set.r, 1, 1 ) ) IN ('t','y','1')
642 OR LOWER(tmp_attr_set.r) = 'reference'; -- BOOL
644 attr_set.copy_number := tmp_attr_set.cnum::INT; -- INT,
645 attr_set.deposit_amount := tmp_attr_set.dep_amount::NUMERIC(6,2); -- NUMERIC(6,2),
646 attr_set.price := tmp_attr_set.pr::NUMERIC(8,2); -- NUMERIC(8,2),
648 attr_set.call_number := tmp_attr_set.cn; -- TEXT
649 attr_set.barcode := tmp_attr_set.bc; -- TEXT,
650 attr_set.circ_modifier := tmp_attr_set.circ_mod; -- TEXT,
651 attr_set.circ_as_type := tmp_attr_set.circ_as; -- TEXT,
652 attr_set.alert_message := tmp_attr_set.amessage; -- TEXT,
653 attr_set.pub_note := tmp_attr_set.note; -- TEXT,
654 attr_set.priv_note := tmp_attr_set.pnote; -- TEXT,
655 attr_set.alert_message := tmp_attr_set.amessage; -- TEXT,
657 RETURN NEXT attr_set;
666 CREATE OR REPLACE FUNCTION actor.org_unit_ancestors ( INT ) RETURNS SETOF actor.org_unit AS $$
668 FROM connectby('actor.org_unit'::text,'parent_ou'::text,'id'::text,'name'::text,$1::text,100,'.'::text)
669 AS t(keyid text, parent_keyid text, level int, branch text,pos int)
670 JOIN actor.org_unit a ON a.id::text = t.keyid::text
671 JOIN actor.org_unit_type tp ON tp.id = a.ou_type
672 ORDER BY tp.depth, a.name;
673 $$ LANGUAGE SQL STABLE;
675 CREATE OR REPLACE FUNCTION actor.org_unit_ancestor_setting( setting_name TEXT, org_id INT ) RETURNS SETOF actor.org_unit_setting AS $$
682 SELECT INTO setting * FROM actor.org_unit_setting WHERE org_unit = cur_org AND name = setting_name;
686 SELECT INTO cur_org parent_ou FROM actor.org_unit WHERE id = cur_org;
687 EXIT WHEN cur_org IS NULL;
693 COMMENT ON FUNCTION actor.org_unit_ancestor_setting( TEXT, INT) IS $$
695 * Search "up" the org_unit tree until we find the first occurrence of an
696 * org_unit_setting with the given name.
701 ALTER TABLE asset.copy_tranparency_map RENAME TO copy_transparency_map;
702 ALTER TABLE asset.copy_transparency_map RENAME COLUMN tansparency TO transparency;
704 CREATE TABLE asset.uri (
705 id SERIAL PRIMARY KEY,
708 use_restriction TEXT,
709 active BOOL NOT NULL DEFAULT TRUE
712 CREATE TABLE asset.uri_call_number_map (
713 id BIGSERIAL PRIMARY KEY,
714 uri INT NOT NULL REFERENCES asset.uri (id),
715 call_number INT NOT NULL REFERENCES asset.call_number (id),
716 CONSTRAINT uri_cn_once UNIQUE (uri,call_number)
718 CREATE INDEX asset_uri_call_number_map_cn_idx ON asset.uri_call_number_map (call_number);
720 -----------------------------
722 CREATE TABLE container.copy_bucket_type (
723 code TEXT PRIMARY KEY,
724 label TEXT NOT NULL UNIQUE
728 CREATE TABLE container.copy_bucket_note (
729 id SERIAL PRIMARY KEY,
730 bucket INT NOT NULL REFERENCES container.copy_bucket (id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
734 ALTER TABLE container.copy_bucket_item ADD COLUMN pos INT;
735 CREATE INDEX copy_bucket_item_bucket_idx ON container.copy_bucket_item (bucket);
737 CREATE TABLE container.copy_bucket_item_note (
738 id SERIAL PRIMARY KEY,
739 item INT NOT NULL REFERENCES container.copy_bucket_item (id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
743 -----------------------------
745 CREATE TABLE container.call_number_bucket_type (
746 code TEXT PRIMARY KEY,
747 label TEXT NOT NULL UNIQUE
750 ALTER TABLE container.call_number_bucket_item ADD COLUMN pos INT;
751 CREATE INDEX call_number_bucket_item_bucket_idx ON container.call_number_bucket_item (bucket);
753 CREATE TABLE container.call_number_bucket_note (
754 id SERIAL PRIMARY KEY,
755 bucket INT NOT NULL REFERENCES container.call_number_bucket (id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
760 CREATE TABLE container.call_number_bucket_item_note (
761 id SERIAL PRIMARY KEY,
762 item INT NOT NULL REFERENCES container.call_number_bucket_item (id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
766 ---------------------------
768 CREATE TABLE container.biblio_record_entry_bucket_type (
769 code TEXT PRIMARY KEY,
770 label TEXT NOT NULL UNIQUE
774 CREATE TABLE container.biblio_record_entry_bucket_note (
775 id SERIAL PRIMARY KEY,
776 bucket INT NOT NULL REFERENCES container.biblio_record_entry_bucket (id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
780 ALTER TABLE container.biblio_record_entry_bucket_item ADD COLUMN pos INT;
781 CREATE INDEX biblio_record_entry_bucket_item_bucket_idx ON container.biblio_record_entry_bucket_item (bucket);
783 CREATE TABLE container.biblio_record_entry_bucket_item_note (
784 id SERIAL PRIMARY KEY,
785 item INT NOT NULL REFERENCES container.biblio_record_entry_bucket_item (id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
789 ---------------------------
791 CREATE TABLE container.user_bucket_type (
792 code TEXT PRIMARY KEY,
793 label TEXT NOT NULL UNIQUE
796 CREATE TABLE container.user_bucket_note (
797 id SERIAL PRIMARY KEY,
798 bucket INT NOT NULL REFERENCES container.user_bucket (id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
802 ALTER TABLE container.user_bucket_item ADD COLUMN pos INT;
803 CREATE INDEX user_bucket_item_bucket_idx ON container.user_bucket_item (bucket);
805 CREATE TABLE container.user_bucket_item_note (
806 id SERIAL PRIMARY KEY,
807 item INT NOT NULL REFERENCES container.user_bucket_item (id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
811 -----------------------------
813 INSERT INTO config.billing_type (name,owner) SELECT DISTINCT billing_type, 1 FROM money.billing WHERE billing_type NOT IN (SELECT name FROM config.billing_type);
814 ALTER TABLE money.billing ADD COLUMN btype INT;
816 UPDATE money.billing SET btype = config.billing_type.id FROM config.billing_type WHERE config.billing_type.name = money.billing.billing_type;
817 ALTER TABLE money.billing ALTER COLUMN btype SET NOT NULL;
818 ALTER TABLE money.billing ADD CONSTRAINT btype_fkey FOREIGN KEY (btype) REFERENCES config.billing_type (id) ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED;
821 CREATE TABLE money.materialized_billable_xact_summary AS
822 SELECT * FROM money.billable_xact_summary WHERE 1=0;
824 CREATE INDEX money_mat_summary_id_idx ON money.materialized_billable_xact_summary (id);
825 CREATE INDEX money_mat_summary_usr_idx ON money.materialized_billable_xact_summary (usr);
826 CREATE INDEX money_mat_summary_xact_start_idx ON money.materialized_billable_xact_summary (xact_start);
828 /* AFTER trigger only! */
829 CREATE OR REPLACE FUNCTION money.mat_summary_create () RETURNS TRIGGER AS $$
831 INSERT INTO money.materialized_billable_xact_summary (id, usr, xact_start, xact_finish, total_paid, total_owed, balance_owed, xact_type)
832 VALUES ( NEW.id, NEW.usr, NEW.xact_start, NEW.xact_finish, 0.0, 0.0, 0.0, TG_ARGV[0]);
837 /* BEFORE or AFTER trigger only! */
838 CREATE OR REPLACE FUNCTION money.mat_summary_update () RETURNS TRIGGER AS $$
840 UPDATE money.materialized_billable_xact_summary
842 xact_start = NEW.xact_start,
843 xact_finish = NEW.xact_finish
849 /* AFTER trigger only! */
850 CREATE OR REPLACE FUNCTION money.mat_summary_delete () RETURNS TRIGGER AS $$
852 DELETE FROM money.materialized_billable_xact_summary WHERE id = OLD.id;
857 CREATE TRIGGER mat_summary_create_tgr AFTER INSERT ON money.grocery FOR EACH ROW EXECUTE PROCEDURE money.mat_summary_create ('grocery');
858 CREATE TRIGGER mat_summary_change_tgr AFTER UPDATE ON money.grocery FOR EACH ROW EXECUTE PROCEDURE money.mat_summary_update ();
859 CREATE TRIGGER mat_summary_remove_tgr AFTER DELETE ON money.grocery FOR EACH ROW EXECUTE PROCEDURE money.mat_summary_delete ();
863 /* BEFORE or AFTER trigger */
864 CREATE OR REPLACE FUNCTION money.materialized_summary_billing_add () RETURNS TRIGGER AS $$
866 IF NOT NEW.voided THEN
867 UPDATE money.materialized_billable_xact_summary
868 SET total_owed = total_owed + NEW.amount,
869 last_billing_ts = NEW.billing_ts,
870 last_billing_note = NEW.note,
871 last_billing_type = NEW.billing_type,
872 balance_owed = balance_owed + NEW.amount
880 /* AFTER trigger only! */
881 CREATE OR REPLACE FUNCTION money.materialized_summary_billing_update () RETURNS TRIGGER AS $$
883 old_billing money.billing%ROWTYPE;
884 old_voided money.billing%ROWTYPE;
887 SELECT * INTO old_billing FROM money.billing WHERE xact = NEW.xact AND NOT voided ORDER BY billing_ts DESC LIMIT 1;
888 SELECT * INTO old_voided FROM money.billing WHERE xact = NEW.xact ORDER BY billing_ts DESC LIMIT 1;
890 IF NEW.voided AND NOT OLD.voided THEN
891 IF OLD.id = old_voided.id THEN
892 UPDATE money.materialized_billable_xact_summary
893 SET last_billing_ts = old_billing.billing_ts,
894 last_billing_note = old_billing.note,
895 last_billing_type = old_billing.billing_type
899 UPDATE money.materialized_billable_xact_summary
900 SET total_owed = total_owed - NEW.amount,
901 balance_owed = balance_owed - NEW.amount
904 ELSIF NOT NEW.voided AND OLD.voided THEN
906 IF OLD.id = old_billing.id THEN
907 UPDATE money.materialized_billable_xact_summary
908 SET last_billing_ts = old_billing.billing_ts,
909 last_billing_note = old_billing.note,
910 last_billing_type = old_billing.billing_type
914 UPDATE money.materialized_billable_xact_summary
915 SET total_owed = total_owed + NEW.amount,
916 balance_owed = balance_owed + NEW.amount
920 UPDATE money.materialized_billable_xact_summary
921 SET total_owed = total_owed - (OLD.amount - NEW.amount),
922 balance_owed = balance_owed - (OLD.amount - NEW.amount)
930 /* BEFORE trigger only! */
931 CREATE OR REPLACE FUNCTION money.materialized_summary_billing_del () RETURNS TRIGGER AS $$
933 prev_billing money.billing%ROWTYPE;
934 old_billing money.billing%ROWTYPE;
936 SELECT * INTO prev_billing FROM money.billing WHERE xact = OLD.xact AND NOT voided ORDER BY billing_ts DESC LIMIT 1 OFFSET 1;
937 SELECT * INTO old_billing FROM money.billing WHERE xact = OLD.xact AND NOT voided ORDER BY billing_ts DESC LIMIT 1;
939 IF OLD.id = old_billing.id THEN
940 UPDATE money.materialized_billable_xact_summary
941 SET last_billing_ts = prev_billing.billing_ts,
942 last_billing_note = prev_billing.note,
943 last_billing_type = prev_billing.billing_type
947 IF NOT OLD.voided THEN
948 UPDATE money.materialized_billable_xact_summary
949 SET total_owed = total_owed - OLD.amount,
950 balance_owed = balance_owed + OLD.amount
958 CREATE TRIGGER mat_summary_add_tgr AFTER INSERT ON money.billing FOR EACH ROW EXECUTE PROCEDURE money.materialized_summary_billing_add ();
959 CREATE TRIGGER mat_summary_upd_tgr AFTER UPDATE ON money.billing FOR EACH ROW EXECUTE PROCEDURE money.materialized_summary_billing_update ();
960 CREATE TRIGGER mat_summary_del_tgr BEFORE DELETE ON money.billing FOR EACH ROW EXECUTE PROCEDURE money.materialized_summary_billing_del ();
963 /* BEFORE or AFTER trigger */
964 CREATE OR REPLACE FUNCTION money.materialized_summary_payment_add () RETURNS TRIGGER AS $$
966 IF NOT NEW.voided THEN
967 UPDATE money.materialized_billable_xact_summary
968 SET total_paid = total_paid + NEW.amount,
969 last_payment_ts = NEW.payment_ts,
970 last_payment_note = NEW.note,
971 last_payment_type = TG_ARGV[0],
972 balance_owed = balance_owed - NEW.amount
980 /* AFTER trigger only! */
981 CREATE OR REPLACE FUNCTION money.materialized_summary_payment_update () RETURNS TRIGGER AS $$
983 old_payment money.payment_view%ROWTYPE;
984 old_voided money.payment_view%ROWTYPE;
987 SELECT * INTO old_payment FROM money.payment_view WHERE xact = NEW.xact AND NOT voided ORDER BY payment_ts DESC LIMIT 1;
988 SELECT * INTO old_voided FROM money.payment_view WHERE xact = NEW.xact ORDER BY payment_ts DESC LIMIT 1;
990 IF NEW.voided AND NOT OLD.voided THEN
991 IF OLD.id = old_voided.id THEN
992 UPDATE money.materialized_billable_xact_summary
993 SET last_payment_ts = old_payment.payment_ts,
994 last_payment_note = old_payment.note,
995 last_payment_type = old_payment.payment_type
999 UPDATE money.materialized_billable_xact_summary
1000 SET total_paid = total_paid - NEW.amount,
1001 balance_owed = balance_owed + NEW.amount
1002 WHERE id = NEW.xact;
1004 ELSIF NOT NEW.voided AND OLD.voided THEN
1006 IF OLD.id = old_payment.id THEN
1007 UPDATE money.materialized_billable_xact_summary
1008 SET last_payment_ts = old_payment.payment_ts,
1009 last_payment_note = old_payment.note,
1010 last_payment_type = old_payment.payment_type
1011 WHERE id = OLD.xact;
1014 UPDATE money.materialized_billable_xact_summary
1015 SET total_paid = total_paid + NEW.amount,
1016 balance_owed = balance_owed - NEW.amount
1017 WHERE id = NEW.xact;
1020 UPDATE money.materialized_billable_xact_summary
1021 SET total_paid = total_paid - (OLD.amount - NEW.amount),
1022 balance_owed = balance_owed + (OLD.amount - NEW.amount)
1023 WHERE id = NEW.xact;
1028 $$ LANGUAGE PLPGSQL;
1030 /* BEFORE trigger only! */
1031 CREATE OR REPLACE FUNCTION money.materialized_summary_payment_del () RETURNS TRIGGER AS $$
1033 prev_payment money.payment_view%ROWTYPE;
1034 old_payment money.payment_view%ROWTYPE;
1036 SELECT * INTO prev_payment FROM money.payment_view WHERE xact = OLD.xact AND NOT voided ORDER BY payment_ts DESC LIMIT 1 OFFSET 1;
1037 SELECT * INTO old_payment FROM money.payment_view WHERE xact = OLD.xact AND NOT voided ORDER BY payment_ts DESC LIMIT 1;
1039 IF OLD.id = old_payment.id THEN
1040 UPDATE money.materialized_billable_xact_summary
1041 SET last_payment_ts = prev_payment.payment_ts,
1042 last_payment_note = prev_payment.note,
1043 last_payment_type = prev_payment.payment_type
1044 WHERE id = OLD.xact;
1047 IF NOT OLD.voided THEN
1048 UPDATE money.materialized_billable_xact_summary
1049 SET total_paid = total_paid - OLD.amount,
1050 balance_owed = balance_owed + OLD.amount
1051 WHERE id = OLD.xact;
1056 $$ LANGUAGE PLPGSQL;
1058 CREATE TRIGGER mat_summary_add_tgr AFTER INSERT ON money.payment FOR EACH ROW EXECUTE PROCEDURE money.materialized_summary_payment_add ('payment');
1059 CREATE TRIGGER mat_summary_upd_tgr AFTER UPDATE ON money.payment FOR EACH ROW EXECUTE PROCEDURE money.materialized_summary_payment_update ('payment');
1060 CREATE TRIGGER mat_summary_del_tgr BEFORE DELETE ON money.payment FOR EACH ROW EXECUTE PROCEDURE money.materialized_summary_payment_del ('payment');
1062 CREATE TRIGGER mat_summary_add_tgr AFTER INSERT ON money.bnm_payment FOR EACH ROW EXECUTE PROCEDURE money.materialized_summary_payment_add ('bnm_payment');
1063 CREATE TRIGGER mat_summary_upd_tgr AFTER UPDATE ON money.bnm_payment FOR EACH ROW EXECUTE PROCEDURE money.materialized_summary_payment_update ('bnm_payment');
1064 CREATE TRIGGER mat_summary_del_tgr BEFORE DELETE ON money.bnm_payment FOR EACH ROW EXECUTE PROCEDURE money.materialized_summary_payment_del ('bnm_payment');
1066 CREATE TRIGGER mat_summary_add_tgr AFTER INSERT ON money.forgive_payment FOR EACH ROW EXECUTE PROCEDURE money.materialized_summary_payment_add ('forgive_payment');
1067 CREATE TRIGGER mat_summary_upd_tgr AFTER UPDATE ON money.forgive_payment FOR EACH ROW EXECUTE PROCEDURE money.materialized_summary_payment_update ('forgive_payment');
1068 CREATE TRIGGER mat_summary_del_tgr BEFORE DELETE ON money.forgive_payment FOR EACH ROW EXECUTE PROCEDURE money.materialized_summary_payment_del ('forgive_payment');
1070 CREATE TRIGGER mat_summary_add_tgr AFTER INSERT ON money.work_payment FOR EACH ROW EXECUTE PROCEDURE money.materialized_summary_payment_add ('work_payment');
1071 CREATE TRIGGER mat_summary_upd_tgr AFTER UPDATE ON money.work_payment FOR EACH ROW EXECUTE PROCEDURE money.materialized_summary_payment_update ('work_payment');
1072 CREATE TRIGGER mat_summary_del_tgr BEFORE DELETE ON money.work_payment FOR EACH ROW EXECUTE PROCEDURE money.materialized_summary_payment_del ('work_payment');
1074 CREATE TRIGGER mat_summary_add_tgr AFTER INSERT ON money.credit_payment FOR EACH ROW EXECUTE PROCEDURE money.materialized_summary_payment_add ('credit_payment');
1075 CREATE TRIGGER mat_summary_upd_tgr AFTER UPDATE ON money.credit_payment FOR EACH ROW EXECUTE PROCEDURE money.materialized_summary_payment_update ('credit_payment');
1076 CREATE TRIGGER mat_summary_del_tgr BEFORE DELETE ON money.credit_payment FOR EACH ROW EXECUTE PROCEDURE money.materialized_summary_payment_del ('credit_payment');
1078 CREATE TRIGGER mat_summary_add_tgr AFTER INSERT ON money.goods_payment FOR EACH ROW EXECUTE PROCEDURE money.materialized_summary_payment_add ('goods_payment');
1079 CREATE TRIGGER mat_summary_upd_tgr AFTER UPDATE ON money.goods_payment FOR EACH ROW EXECUTE PROCEDURE money.materialized_summary_payment_update ('goods_payment');
1080 CREATE TRIGGER mat_summary_del_tgr BEFORE DELETE ON money.goods_payment FOR EACH ROW EXECUTE PROCEDURE money.materialized_summary_payment_del ('goods_payment');
1082 CREATE TRIGGER mat_summary_add_tgr AFTER INSERT ON money.bnm_desk_payment FOR EACH ROW EXECUTE PROCEDURE money.materialized_summary_payment_add ('bnm_desk_payment');
1083 CREATE TRIGGER mat_summary_upd_tgr AFTER UPDATE ON money.bnm_desk_payment FOR EACH ROW EXECUTE PROCEDURE money.materialized_summary_payment_update ('bnm_desk_payment');
1084 CREATE TRIGGER mat_summary_del_tgr BEFORE DELETE ON money.bnm_desk_payment FOR EACH ROW EXECUTE PROCEDURE money.materialized_summary_payment_del ('bnm_desk_payment');
1086 CREATE TRIGGER mat_summary_add_tgr AFTER INSERT ON money.cash_payment FOR EACH ROW EXECUTE PROCEDURE money.materialized_summary_payment_add ('cash_payment');
1087 CREATE TRIGGER mat_summary_upd_tgr AFTER UPDATE ON money.cash_payment FOR EACH ROW EXECUTE PROCEDURE money.materialized_summary_payment_update ('cash_payment');
1088 CREATE TRIGGER mat_summary_del_tgr BEFORE DELETE ON money.cash_payment FOR EACH ROW EXECUTE PROCEDURE money.materialized_summary_payment_del ('cash_payment');
1090 CREATE TRIGGER mat_summary_add_tgr AFTER INSERT ON money.check_payment FOR EACH ROW EXECUTE PROCEDURE money.materialized_summary_payment_add ('check_payment');
1091 CREATE TRIGGER mat_summary_upd_tgr AFTER UPDATE ON money.check_payment FOR EACH ROW EXECUTE PROCEDURE money.materialized_summary_payment_update ('check_payment');
1092 CREATE TRIGGER mat_summary_del_tgr BEFORE DELETE ON money.check_payment FOR EACH ROW EXECUTE PROCEDURE money.materialized_summary_payment_del ('check_payment');
1094 CREATE TRIGGER mat_summary_add_tgr AFTER INSERT ON money.credit_card_payment FOR EACH ROW EXECUTE PROCEDURE money.materialized_summary_payment_add ('credit_card_payment');
1095 CREATE TRIGGER mat_summary_upd_tgr AFTER UPDATE ON money.credit_card_payment FOR EACH ROW EXECUTE PROCEDURE money.materialized_summary_payment_update ('credit_card_payment');
1096 CREATE TRIGGER mat_summary_del_tgr BEFORE DELETE ON money.credit_card_payment FOR EACH ROW EXECUTE PROCEDURE money.materialized_summary_payment_del ('credit_card_payment');
1099 CREATE TRIGGER mat_summary_create_tgr AFTER INSERT ON action.circulation FOR EACH ROW EXECUTE PROCEDURE money.mat_summary_create ('circulation');
1100 CREATE TRIGGER mat_summary_change_tgr AFTER UPDATE ON action.circulation FOR EACH ROW EXECUTE PROCEDURE money.mat_summary_update ();
1101 CREATE TRIGGER mat_summary_remove_tgr AFTER DELETE ON action.circulation FOR EACH ROW EXECUTE PROCEDURE money.mat_summary_delete ();
1103 CREATE OR REPLACE VIEW action.billable_circulations AS
1105 FROM action.circulation
1106 WHERE xact_finish IS NULL;
1108 CREATE TABLE action.hold_request_cancel_cause (
1109 id SERIAL PRIMARY KEY,
1112 INSERT INTO action.hold_request_cancel_cause (id,label) VALUES (1,'Untargeted expiration');
1113 INSERT INTO action.hold_request_cancel_cause (id,label) VALUES (2,'Hold Shelf expiration');
1114 INSERT INTO action.hold_request_cancel_cause (id,label) VALUES (3,'Patron via phone');
1115 INSERT INTO action.hold_request_cancel_cause (id,label) VALUES (4,'Patron in person');
1116 INSERT INTO action.hold_request_cancel_cause (id,label) VALUES (5,'Staff forced');
1117 INSERT INTO action.hold_request_cancel_cause (id,label) VALUES (6,'Patron via OPAC');
1118 SELECT SETVAL('action.hold_request_cancel_cause_id_seq', 100);
1120 ALTER TABLE action.hold_request ADD COLUMN cancel_cause INT;
1121 ALTER TABLE action.hold_request ADD COLUMN cancel_note TEXT;
1124 ALTER TABLE config.circ_matrix_matchpoint ADD COLUMN juvenile_flag BOOL;
1125 ALTER TABLE config.circ_matrix_matchpoint ADD COLUMN circulate BOOL NOT NULL DEFAULT TRUE;
1126 ALTER TABLE config.circ_matrix_matchpoint ADD COLUMN duration_rule INT REFERENCES config.rule_circ_duration (id) DEFERRABLE INITIALLY DEFERRED;
1127 ALTER TABLE config.circ_matrix_matchpoint ADD COLUMN recurring_fine_rule INT REFERENCES config.rule_recurring_fine (id) DEFERRABLE INITIALLY DEFERRED;
1128 ALTER TABLE config.circ_matrix_matchpoint ADD COLUMN max_fine_rule INT REFERENCES config.rule_max_fine (id) DEFERRABLE INITIALLY DEFERRED;
1129 ALTER TABLE config.circ_matrix_matchpoint ADD COLUMN script_test TEXT;
1131 ALTER TABLE config.circ_matrix_matchpoint DROP CONSTRAINT ep_once_per_grp_loc_mod_marc;
1132 ALTER TABLE config.circ_matrix_matchpoint
1133 ADD CONSTRAINT ep_once_per_grp_loc_mod_marc
1134 UNIQUE (grp, org_unit, circ_modifier, marc_type, marc_form, marc_vr_format, ref_flag, juvenile_flag, usr_age_lower_bound, usr_age_upper_bound, is_renewal);
1136 UPDATE config.circ_matrix_matchpoint
1137 SET duration_rule = config.circ_matrix_ruleset.duration_rule,
1138 recurring_fine_rule = config.circ_matrix_ruleset.recurring_fine_rule,
1139 max_fine_rule = config.circ_matrix_ruleset.max_fine_rule
1140 FROM config.circ_matrix_ruleset
1141 WHERE config.circ_matrix_ruleset.matchpoint = config.circ_matrix_matchpoint.id;
1143 DROP TABLE config.circ_matrix_ruleset;
1145 ALTER TABLE config.circ_matrix_circ_mod_test DROP COLUMN circ_mod;
1146 CREATE TABLE config.circ_matrix_circ_mod_test_map (
1147 id SERIAL PRIMARY KEY,
1148 circ_mod_test INT NOT NULL REFERENCES config.circ_matrix_circ_mod_test (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
1149 circ_mod TEXT NOT NULL REFERENCES config.circ_modifier (code) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
1150 CONSTRAINT cm_once_per_test UNIQUE (circ_mod_test, circ_mod)
1153 DROP FUNCTION action.find_circ_matrix_matchpoint( context_ou INT, match_item BIGINT, match_user INT, renewal BOOL );
1154 CREATE OR REPLACE FUNCTION action.find_circ_matrix_matchpoint( context_ou INT, match_item BIGINT, match_user INT, renewal BOOL ) RETURNS config.circ_matrix_matchpoint AS $func$
1156 current_group permission.grp_tree%ROWTYPE;
1157 user_object actor.usr%ROWTYPE;
1158 item_object asset.copy%ROWTYPE;
1159 rec_descriptor metabib.rec_descriptor%ROWTYPE;
1160 current_mp config.circ_matrix_matchpoint%ROWTYPE;
1161 matchpoint config.circ_matrix_matchpoint%ROWTYPE;
1163 SELECT INTO user_object * FROM actor.usr WHERE id = match_user;
1164 SELECT INTO item_object * FROM asset.copy WHERE id = match_item;
1165 SELECT INTO rec_descriptor r.* FROM metabib.rec_descriptor r JOIN asset.call_number c USING (record) WHERE c.id = item_object.call_number;
1166 SELECT INTO current_group * FROM permission.grp_tree WHERE id = user_object.profile;
1169 -- for each potential matchpoint for this ou and group ...
1172 FROM config.circ_matrix_matchpoint m
1173 JOIN actor.org_unit_ancestors( context_ou ) d ON (m.org_unit = d.id)
1174 LEFT JOIN actor.org_unit_proximity p ON (p.from_org = context_ou AND p.to_org = d.id)
1175 WHERE m.grp = current_group.id AND m.active
1176 ORDER BY CASE WHEN p.prox IS NULL THEN 999 ELSE p.prox END,
1177 CASE WHEN m.is_renewal = renewal THEN 128 ELSE 0 END +
1178 CASE WHEN m.juvenile_flag IS NOT NULL THEN 64 ELSE 0 END +
1179 CASE WHEN m.circ_modifier IS NOT NULL THEN 32 ELSE 0 END +
1180 CASE WHEN m.marc_type IS NOT NULL THEN 16 ELSE 0 END +
1181 CASE WHEN m.marc_form IS NOT NULL THEN 8 ELSE 0 END +
1182 CASE WHEN m.marc_vr_format IS NOT NULL THEN 4 ELSE 0 END +
1183 CASE WHEN m.ref_flag IS NOT NULL THEN 2 ELSE 0 END +
1184 CASE WHEN m.usr_age_lower_bound IS NOT NULL THEN 0.5 ELSE 0 END +
1185 CASE WHEN m.usr_age_upper_bound IS NOT NULL THEN 0.5 ELSE 0 END DESC LOOP
1187 IF current_mp.circ_modifier IS NOT NULL THEN
1188 CONTINUE WHEN current_mp.circ_modifier <> item_object.circ_modifier;
1191 IF current_mp.marc_type IS NOT NULL THEN
1192 IF item_object.circ_as_type IS NOT NULL THEN
1193 CONTINUE WHEN current_mp.marc_type <> item_object.circ_as_type;
1195 CONTINUE WHEN current_mp.marc_type <> rec_descriptor.item_type;
1199 IF current_mp.marc_form IS NOT NULL THEN
1200 CONTINUE WHEN current_mp.marc_form <> rec_descriptor.item_form;
1203 IF current_mp.marc_vr_format IS NOT NULL THEN
1204 CONTINUE WHEN current_mp.marc_vr_format <> rec_descriptor.vr_format;
1207 IF current_mp.ref_flag IS NOT NULL THEN
1208 CONTINUE WHEN current_mp.ref_flag <> item_object.ref;
1211 IF current_mp.juvenile_flag IS NOT NULL THEN
1212 CONTINUE WHEN current_mp.juvenile_flag <> user_object.juvenile;
1215 IF current_mp.usr_age_lower_bound IS NOT NULL THEN
1216 CONTINUE WHEN user_object.dob IS NULL OR current_mp.usr_age_lower_bound < age(user_object.dob);
1219 IF current_mp.usr_age_upper_bound IS NOT NULL THEN
1220 CONTINUE WHEN user_object.dob IS NULL OR current_mp.usr_age_upper_bound > age(user_object.dob);
1224 -- everything was undefined or matched
1225 matchpoint = current_mp;
1227 EXIT WHEN matchpoint.id IS NOT NULL;
1230 EXIT WHEN current_group.parent IS NULL OR matchpoint.id IS NOT NULL;
1232 SELECT INTO current_group * FROM permission.grp_tree WHERE id = current_group.parent;
1237 $func$ LANGUAGE plpgsql;
1239 CREATE OR REPLACE FUNCTION action.item_user_circ_test( circ_ou INT, match_item BIGINT, match_user INT, renewal BOOL ) RETURNS SETOF action.matrix_test_result AS $func$
1241 user_object actor.usr%ROWTYPE;
1242 standing_penalty config.standing_penalty%ROWTYPE;
1243 item_object asset.copy%ROWTYPE;
1244 item_status_object config.copy_status%ROWTYPE;
1245 item_location_object asset.copy_location%ROWTYPE;
1246 result action.matrix_test_result;
1247 circ_test config.circ_matrix_matchpoint%ROWTYPE;
1248 out_by_circ_mod config.circ_matrix_circ_mod_test%ROWTYPE;
1249 circ_mod_map config.circ_matrix_circ_mod_test_map%ROWTYPE;
1253 context_org_list INT[];
1256 result.success := TRUE;
1258 -- Fail if the user is BARRED
1259 SELECT INTO user_object * FROM actor.usr WHERE id = match_user;
1261 -- Fail if we couldn't find a set of tests
1262 IF user_object.id IS NULL THEN
1263 result.fail_part := 'no_user';
1264 result.success := FALSE;
1270 IF user_object.barred IS TRUE THEN
1271 result.fail_part := 'actor.usr.barred';
1272 result.success := FALSE;
1277 -- Fail if the item can't circulate
1278 SELECT INTO item_object * FROM asset.copy WHERE id = match_item;
1279 IF item_object.circulate IS FALSE THEN
1280 result.fail_part := 'asset.copy.circulate';
1281 result.success := FALSE;
1286 -- Fail if the item isn't in a circulateable status on a non-renewal
1287 IF NOT renewal AND item_object.status NOT IN ( 0, 7, 8 ) THEN
1288 result.fail_part := 'asset.copy.status';
1289 result.success := FALSE;
1292 ELSIF renewal AND item_object.status <> 1 THEN
1293 result.fail_part := 'asset.copy.status';
1294 result.success := FALSE;
1299 -- Fail if the item can't circulate because of the shelving location
1300 SELECT INTO item_location_object * FROM asset.copy_location WHERE id = item_object.location;
1301 IF item_location_object.circulate IS FALSE THEN
1302 result.fail_part := 'asset.copy_location.circulate';
1303 result.success := FALSE;
1308 SELECT INTO circ_test * FROM action.find_circ_matrix_matchpoint(circ_ou, match_item, match_user, renewal);
1309 result.matchpoint := circ_test.id;
1311 SELECT INTO context_org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( circ_test.org_unit );
1313 -- Fail if we couldn't find a set of tests
1314 IF result.matchpoint IS NULL THEN
1315 result.fail_part := 'no_matchpoint';
1316 result.success := FALSE;
1321 -- Fail if the test is set to hard non-circulating
1322 IF circ_test.circulate IS FALSE THEN
1323 result.fail_part := 'config.circ_matrix_test.circulate';
1324 result.success := FALSE;
1330 penalty_type = '%RENEW%';
1332 penalty_type = '%CIRC%';
1335 FOR standing_penalty IN
1336 SELECT DISTINCT csp.*
1337 FROM actor.usr_standing_penalty usp
1338 JOIN config.standing_penalty csp ON (csp.id = usp.standing_penalty)
1339 WHERE usr = match_user
1340 AND usp.org_unit IN ( SELECT * FROM explode_array(context_org_list) )
1341 AND (usp.stop_date IS NULL or usp.stop_date > NOW())
1342 AND csp.block_list LIKE penalty_type LOOP
1344 result.fail_part := standing_penalty.name;
1345 result.success := FALSE;
1350 -- Fail if the user has too many items with specific circ_modifiers checked out
1351 FOR out_by_circ_mod IN SELECT * FROM config.circ_matrix_circ_mod_test WHERE matchpoint = circ_test.id LOOP
1352 SELECT INTO items_out COUNT(*)
1353 FROM action.circulation circ
1354 JOIN asset.copy cp ON (cp.id = circ.target_copy)
1355 WHERE circ.usr = match_user
1356 AND circ_lib IN ( SELECT * FROM explode_array(context_org_list) )
1357 AND circ.checkin_time IS NULL
1358 AND (circ.stop_fines IN ('MAXFINES','LONGOVERDUE') OR circ.stop_fines IS NULL)
1359 AND cp.circ_modifier IN (SELECT circ_mod FROM config.circ_matrix_circ_mod_test_map WHERE circ_mod_test = out_by_circ_mod.id);
1360 IF items_out >= out_by_circ_mod.items_out THEN
1361 result.fail_part := 'config.circ_matrix_circ_mod_test';
1362 result.success := FALSE;
1368 -- If we passed everything, return the successful matchpoint id
1375 $func$ LANGUAGE plpgsql;
1377 CREATE OR REPLACE FUNCTION actor.calculate_system_penalties( match_user INT, context_org INT ) RETURNS SETOF actor.usr_standing_penalty AS $func$
1379 user_object actor.usr%ROWTYPE;
1380 new_sp_row actor.usr_standing_penalty%ROWTYPE;
1381 existing_sp_row actor.usr_standing_penalty%ROWTYPE;
1382 collections_fines permission.grp_penalty_threshold%ROWTYPE;
1383 max_fines permission.grp_penalty_threshold%ROWTYPE;
1384 max_overdue permission.grp_penalty_threshold%ROWTYPE;
1385 max_items_out permission.grp_penalty_threshold%ROWTYPE;
1389 context_org_list INT[];
1390 current_fines NUMERIC(8,2) := 0.0;
1391 tmp_fines NUMERIC(8,2);
1394 tmp_org actor.org_unit%ROWTYPE;
1396 SELECT INTO user_object * FROM actor.usr WHERE id = match_user;
1399 SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;
1401 -- Fail if the user has a high fine balance
1403 tmp_grp := user_object.profile;
1405 SELECT * INTO max_fines FROM permission.grp_penalty_threshold WHERE grp = tmp_grp AND penalty = 1 AND org_unit = tmp_org.id;
1407 IF max_fines.threshold IS NULL THEN
1408 SELECT parent INTO tmp_grp FROM permission.grp_tree WHERE id = tmp_grp;
1413 IF tmp_grp IS NULL THEN
1418 IF max_fines.threshold IS NOT NULL OR tmp_org.parent_ou IS NULL THEN
1422 SELECT * INTO tmp_org FROM actor.org_unit WHERE id = tmp_org.parent_ou;
1426 IF max_fines.threshold IS NOT NULL THEN
1428 FOR existing_sp_row IN
1430 FROM actor.usr_standing_penalty
1431 WHERE usr = match_user
1432 AND org_unit = max_fines.org_unit
1433 AND (stop_date IS NULL or stop_date > NOW())
1434 AND standing_penalty = 1
1436 RETURN NEXT existing_sp_row;
1439 SELECT SUM(f.balance_owed) INTO current_fines
1440 FROM money.materialized_billable_xact_summary f
1443 FROM money.grocery g
1444 JOIN actor.org_unit_full_path( max_fines.org_unit ) fp ON (g.billing_location = fp.id)
1445 WHERE usr = match_user
1446 AND xact_finish IS NULL
1449 FROM action.circulation circ
1450 JOIN actor.org_unit_full_path( max_fines.org_unit ) fp ON (circ.circ_lib = fp.id)
1451 WHERE usr = match_user
1452 AND xact_finish IS NULL ) l USING (id);
1454 IF current_fines >= max_fines.threshold THEN
1455 new_sp_row.usr := match_user;
1456 new_sp_row.org_unit := max_fines.org_unit;
1457 new_sp_row.standing_penalty := 1;
1458 RETURN NEXT new_sp_row;
1462 -- Start over for max overdue
1463 SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;
1465 -- Fail if the user has too many overdue items
1467 tmp_grp := user_object.profile;
1470 SELECT * INTO max_overdue FROM permission.grp_penalty_threshold WHERE grp = tmp_grp AND penalty = 2 AND org_unit = tmp_org.id;
1472 IF max_overdue.threshold IS NULL THEN
1473 SELECT parent INTO tmp_grp FROM permission.grp_tree WHERE id = tmp_grp;
1478 IF tmp_grp IS NULL THEN
1483 IF max_overdue.threshold IS NOT NULL OR tmp_org.parent_ou IS NULL THEN
1487 SELECT INTO tmp_org * FROM actor.org_unit WHERE id = tmp_org.parent_ou;
1491 IF max_overdue.threshold IS NOT NULL THEN
1493 FOR existing_sp_row IN
1495 FROM actor.usr_standing_penalty
1496 WHERE usr = match_user
1497 AND org_unit = max_overdue.org_unit
1498 AND (stop_date IS NULL or stop_date > NOW())
1499 AND standing_penalty = 2
1501 RETURN NEXT existing_sp_row;
1504 SELECT INTO items_overdue COUNT(*)
1505 FROM action.circulation circ
1506 JOIN actor.org_unit_full_path( max_overdue.org_unit ) fp ON (circ.circ_lib = fp.id)
1507 WHERE circ.usr = match_user
1508 AND circ.checkin_time IS NULL
1509 AND circ.due_date < NOW()
1510 AND (circ.stop_fines = 'MAXFINES' OR circ.stop_fines IS NULL);
1512 IF items_overdue >= max_overdue.threshold::INT THEN
1513 new_sp_row.usr := match_user;
1514 new_sp_row.org_unit := max_overdue.org_unit;
1515 new_sp_row.standing_penalty := 2;
1516 RETURN NEXT new_sp_row;
1520 -- Start over for max out
1521 SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;
1523 -- Fail if the user has too many checked out items
1525 tmp_grp := user_object.profile;
1527 SELECT * INTO max_items_out FROM permission.grp_penalty_threshold WHERE grp = tmp_grp AND penalty = 3 AND org_unit = tmp_org.id;
1529 IF max_items_out.threshold IS NULL THEN
1530 SELECT parent INTO tmp_grp FROM permission.grp_tree WHERE id = tmp_grp;
1535 IF tmp_grp IS NULL THEN
1540 IF max_items_out.threshold IS NOT NULL OR tmp_org.parent_ou IS NULL THEN
1544 SELECT INTO tmp_org * FROM actor.org_unit WHERE id = tmp_org.parent_ou;
1549 -- Fail if the user has too many items checked out
1550 IF max_items_out.threshold IS NOT NULL THEN
1552 FOR existing_sp_row IN
1554 FROM actor.usr_standing_penalty
1555 WHERE usr = match_user
1556 AND org_unit = max_items_out.org_unit
1557 AND (stop_date IS NULL or stop_date > NOW())
1558 AND standing_penalty = 3
1560 RETURN NEXT existing_sp_row;
1563 SELECT INTO items_out COUNT(*)
1564 FROM action.circulation circ
1565 JOIN actor.org_unit_full_path( max_items_out.org_unit ) fp ON (circ.circ_lib = fp.id)
1566 WHERE circ.usr = match_user
1567 AND circ.checkin_time IS NULL
1568 AND (circ.stop_fines IN ('MAXFINES','LONGOVERDUE') OR circ.stop_fines IS NULL);
1570 IF items_out >= max_items_out.threshold::INT THEN
1571 new_sp_row.usr := match_user;
1572 new_sp_row.org_unit := max_items_out.org_unit;
1573 new_sp_row.standing_penalty := 3;
1574 RETURN NEXT new_sp_row;
1578 -- Start over for collections warning
1579 SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;
1581 -- Fail if the user has a collections-level fine balance
1583 tmp_grp := user_object.profile;
1585 SELECT * INTO max_fines FROM permission.grp_penalty_threshold WHERE grp = tmp_grp AND penalty = 4 AND org_unit = tmp_org.id;
1587 IF max_fines.threshold IS NULL THEN
1588 SELECT parent INTO tmp_grp FROM permission.grp_tree WHERE id = tmp_grp;
1593 IF tmp_grp IS NULL THEN
1598 IF max_fines.threshold IS NOT NULL OR tmp_org.parent_ou IS NULL THEN
1602 SELECT * INTO tmp_org FROM actor.org_unit WHERE id = tmp_org.parent_ou;
1606 IF max_fines.threshold IS NOT NULL THEN
1608 FOR existing_sp_row IN
1610 FROM actor.usr_standing_penalty
1611 WHERE usr = match_user
1612 AND org_unit = max_fines.org_unit
1613 AND (stop_date IS NULL or stop_date > NOW())
1614 AND standing_penalty = 4
1616 RETURN NEXT existing_sp_row;
1619 SELECT SUM(f.balance_owed) INTO current_fines
1620 FROM money.materialized_billable_xact_summary f
1623 FROM money.grocery g
1624 JOIN actor.org_unit_full_path( max_fines.org_unit ) fp ON (g.billing_location = fp.id)
1625 WHERE usr = match_user
1626 AND xact_finish IS NULL
1629 FROM action.circulation circ
1630 JOIN actor.org_unit_full_path( max_fines.org_unit ) fp ON (circ.circ_lib = fp.id)
1631 WHERE usr = match_user
1632 AND xact_finish IS NULL ) l USING (id);
1634 IF current_fines >= max_fines.threshold THEN
1635 new_sp_row.usr := match_user;
1636 new_sp_row.org_unit := max_fines.org_unit;
1637 new_sp_row.standing_penalty := 4;
1638 RETURN NEXT new_sp_row;
1645 $func$ LANGUAGE plpgsql;
1648 ALTER TABLE config.hold_matrix_matchpoint ADD COLUMN juvenile_flag BOOL;
1649 ALTER TABLE config.hold_matrix_matchpoint ADD COLUMN age_hold_protect_rule INT REFERENCES config.rule_age_hold_protect (id) DEFERRABLE INITIALLY DEFERRED;
1650 ALTER TABLE config.hold_matrix_matchpoint ADD COLUMN holdable BOOL NOT NULL DEFAULT TRUE;
1651 ALTER TABLE config.hold_matrix_matchpoint ADD COLUMN distance_is_from_owner BOOL NOT NULL DEFAULT FALSE;
1652 ALTER TABLE config.hold_matrix_matchpoint ADD COLUMN transit_range INT REFERENCES actor.org_unit_type (id) DEFERRABLE INITIALLY DEFERRED;
1653 ALTER TABLE config.hold_matrix_matchpoint ADD COLUMN max_holds INT;
1654 ALTER TABLE config.hold_matrix_matchpoint ADD COLUMN include_frozen_holds BOOL NOT NULL DEFAULT TRUE;
1655 ALTER TABLE config.hold_matrix_matchpoint ADD COLUMN stop_blocked_user BOOL NOT NULL DEFAULT FALSE;
1657 CREATE OR REPLACE FUNCTION action.find_hold_matrix_matchpoint( pickup_ou INT, request_ou INT, match_item BIGINT, match_user INT, match_requestor INT ) RETURNS INT AS $func$
1659 current_requestor_group permission.grp_tree%ROWTYPE;
1660 root_ou actor.org_unit%ROWTYPE;
1661 requestor_object actor.usr%ROWTYPE;
1662 user_object actor.usr%ROWTYPE;
1663 item_object asset.copy%ROWTYPE;
1664 item_cn_object asset.call_number%ROWTYPE;
1665 rec_descriptor metabib.rec_descriptor%ROWTYPE;
1666 current_mp_weight FLOAT;
1667 matchpoint_weight FLOAT;
1669 current_mp config.hold_matrix_matchpoint%ROWTYPE;
1670 matchpoint config.hold_matrix_matchpoint%ROWTYPE;
1672 SELECT INTO root_ou * FROM actor.org_unit WHERE parent_ou IS NULL;
1673 SELECT INTO user_object * FROM actor.usr WHERE id = match_user;
1674 SELECT INTO requestor_object * FROM actor.usr WHERE id = match_requestor;
1675 SELECT INTO item_object * FROM asset.copy WHERE id = match_item;
1676 SELECT INTO item_cn_object * FROM asset.call_number WHERE id = item_object.call_number;
1677 SELECT INTO rec_descriptor r.* FROM metabib.rec_descriptor r WHERE r.record = item_cn_object.record;
1678 SELECT INTO current_requestor_group * FROM permission.grp_tree WHERE id = requestor_object.profile;
1681 -- for each potential matchpoint for this ou and group ...
1684 FROM config.hold_matrix_matchpoint m
1685 WHERE m.requestor_grp = current_requestor_group.id AND m.active
1686 ORDER BY CASE WHEN m.circ_modifier IS NOT NULL THEN 16 ELSE 0 END +
1687 CASE WHEN m.juvenile_flag IS NOT NULL THEN 16 ELSE 0 END +
1688 CASE WHEN m.marc_type IS NOT NULL THEN 8 ELSE 0 END +
1689 CASE WHEN m.marc_form IS NOT NULL THEN 4 ELSE 0 END +
1690 CASE WHEN m.marc_vr_format IS NOT NULL THEN 2 ELSE 0 END +
1691 CASE WHEN m.ref_flag IS NOT NULL THEN 1 ELSE 0 END DESC LOOP
1693 current_mp_weight := 5.0;
1695 IF current_mp.circ_modifier IS NOT NULL THEN
1696 CONTINUE WHEN current_mp.circ_modifier <> item_object.circ_modifier;
1699 IF current_mp.marc_type IS NOT NULL THEN
1700 IF item_object.circ_as_type IS NOT NULL THEN
1701 CONTINUE WHEN current_mp.marc_type <> item_object.circ_as_type;
1703 CONTINUE WHEN current_mp.marc_type <> rec_descriptor.item_type;
1707 IF current_mp.marc_form IS NOT NULL THEN
1708 CONTINUE WHEN current_mp.marc_form <> rec_descriptor.item_form;
1711 IF current_mp.marc_vr_format IS NOT NULL THEN
1712 CONTINUE WHEN current_mp.marc_vr_format <> rec_descriptor.vr_format;
1715 IF current_mp.juvenile_flag IS NOT NULL THEN
1716 CONTINUE WHEN current_mp.juvenile_flag <> user_object.juvenile;
1719 IF current_mp.ref_flag IS NOT NULL THEN
1720 CONTINUE WHEN current_mp.ref_flag <> item_object.ref;
1724 -- caclulate the rule match weight
1725 IF current_mp.item_owning_ou IS NOT NULL AND current_mp.item_owning_ou <> root_ou.id THEN
1726 SELECT INTO tmp_weight 1.0 / (actor.org_unit_proximity(current_mp.item_owning_ou, item_cn_object.owning_lib)::FLOAT + 1.0)::FLOAT;
1727 current_mp_weight := current_mp_weight - tmp_weight;
1730 IF current_mp.item_circ_ou IS NOT NULL AND current_mp.item_circ_ou <> root_ou.id THEN
1731 SELECT INTO tmp_weight 1.0 / (actor.org_unit_proximity(current_mp.item_circ_ou, item_object.circ_lib)::FLOAT + 1.0)::FLOAT;
1732 current_mp_weight := current_mp_weight - tmp_weight;
1735 IF current_mp.pickup_ou IS NOT NULL AND current_mp.pickup_ou <> root_ou.id THEN
1736 SELECT INTO tmp_weight 1.0 / (actor.org_unit_proximity(current_mp.pickup_ou, pickup_ou)::FLOAT + 1.0)::FLOAT;
1737 current_mp_weight := current_mp_weight - tmp_weight;
1740 IF current_mp.request_ou IS NOT NULL AND current_mp.request_ou <> root_ou.id THEN
1741 SELECT INTO tmp_weight 1.0 / (actor.org_unit_proximity(current_mp.request_ou, request_ou)::FLOAT + 1.0)::FLOAT;
1742 current_mp_weight := current_mp_weight - tmp_weight;
1745 IF current_mp.user_home_ou IS NOT NULL AND current_mp.user_home_ou <> root_ou.id THEN
1746 SELECT INTO tmp_weight 1.0 / (actor.org_unit_proximity(current_mp.user_home_ou, user_object.home_ou)::FLOAT + 1.0)::FLOAT;
1747 current_mp_weight := current_mp_weight - tmp_weight;
1750 -- set the matchpoint if we found the best one
1751 IF matchpoint_weight IS NULL OR matchpoint_weight > current_mp_weight THEN
1752 matchpoint = current_mp;
1753 matchpoint_weight = current_mp_weight;
1758 EXIT WHEN current_requestor_group.parent IS NULL OR matchpoint.id IS NOT NULL;
1760 SELECT INTO current_requestor_group * FROM permission.grp_tree WHERE id = current_requestor_group.parent;
1763 RETURN matchpoint.id;
1765 $func$ LANGUAGE plpgsql;
1768 CREATE OR REPLACE FUNCTION action.hold_request_permit_test( pickup_ou INT, request_ou INT, match_item BIGINT, match_user INT, match_requestor INT ) RETURNS SETOF action.matrix_test_result AS
1772 user_object actor.usr%ROWTYPE;
1773 age_protect_object config.rule_age_hold_protect%ROWTYPE;
1774 standing_penalty config.standing_penalty%ROWTYPE;
1775 transit_range_ou_type actor.org_unit_type%ROWTYPE;
1776 transit_source actor.org_unit%ROWTYPE;
1777 item_object asset.copy%ROWTYPE;
1778 result action.matrix_test_result;
1779 hold_test config.hold_matrix_matchpoint%ROWTYPE;
1781 hold_transit_prox INT;
1782 frozen_hold_count INT;
1783 context_org_list INT[];
1786 SELECT INTO user_object * FROM actor.usr WHERE id = match_user;
1787 SELECT INTO context_org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( pickup_ou );
1789 -- Fail if we couldn't find a user
1790 IF user_object.id IS NULL THEN
1791 result.fail_part := 'no_user';
1792 result.success := FALSE;
1798 -- Fail if user is barred
1799 IF user_object.barred IS TRUE THEN
1800 result.fail_part := 'actor.usr.barred';
1801 result.success := FALSE;
1807 SELECT INTO item_object * FROM asset.copy WHERE id = match_item;
1809 -- Fail if we couldn't find a copy
1810 IF item_object.id IS NULL THEN
1811 result.fail_part := 'no_item';
1812 result.success := FALSE;
1818 SELECT INTO matchpoint_id action.find_hold_matrix_matchpoint(pickup_ou, request_ou, match_item, match_user, match_requestor);
1820 -- Fail if we couldn't find any matchpoint (requires a default)
1821 IF matchpoint_id IS NULL THEN
1822 result.fail_part := 'no_matchpoint';
1823 result.success := FALSE;
1829 SELECT INTO hold_test * FROM config.hold_matrix_matchpoint WHERE id = matchpoint_id;
1831 result.matchpoint := hold_test.id;
1832 result.success := TRUE;
1834 IF hold_test.holdable IS FALSE THEN
1835 result.fail_part := 'config.hold_matrix_test.holdable';
1836 result.success := FALSE;
1841 IF hold_test.transit_range IS NOT NULL THEN
1842 SELECT INTO transit_range_ou_type * FROM actor.org_unit_type WHERE id = hold_test.transit_range;
1843 IF hold_test.distance_is_from_owner THEN
1844 SELECT INTO transit_source ou.* FROM actor.org_unit ou JOIN asset.call_number cn ON (cn.owning_lib = ou.id) WHERE cn.id = item_object.call_number;
1846 SELECT INTO transit_source * FROM actor.org_unit WHERE id = item_object.circ_lib;
1849 PERFORM * FROM actor.org_unit_descendants( transit_source.id, transit_range_ou_type.depth ) WHERE id = pickup_ou;
1852 result.fail_part := 'transit_range';
1853 result.success := FALSE;
1859 FOR standing_penalty IN
1860 SELECT DISTINCT csp.*
1861 FROM actor.usr_standing_penalty usp
1862 JOIN config.standing_penalty csp ON (csp.id = usp.standing_penalty)
1863 WHERE usr = match_user
1864 AND usp.org_unit IN ( SELECT * FROM explode_array(context_org_list) )
1865 AND (usp.stop_date IS NULL or usp.stop_date > NOW())
1866 AND csp.block_list LIKE '%HOLD%' LOOP
1868 result.fail_part := standing_penalty.name;
1869 result.success := FALSE;
1874 IF hold_test.stop_blocked_user IS TRUE THEN
1875 FOR standing_penalty IN
1876 SELECT DISTINCT csp.*
1877 FROM actor.usr_standing_penalty usp
1878 JOIN config.standing_penalty csp ON (csp.id = usp.standing_penalty)
1879 WHERE usr = match_user
1880 AND usp.org_unit IN ( SELECT * FROM explode_array(context_org_list) )
1881 AND (usp.stop_date IS NULL or usp.stop_date > NOW())
1882 AND csp.block_list LIKE '%CIRC%' LOOP
1884 result.fail_part := standing_penalty.name;
1885 result.success := FALSE;
1891 IF hold_test.max_holds IS NOT NULL THEN
1892 SELECT INTO hold_count COUNT(*)
1893 FROM action.hold_request
1894 WHERE usr = match_user
1895 AND fulfillment_time IS NULL
1896 AND cancel_time IS NULL
1897 AND CASE WHEN hold_test.include_frozen_holds THEN TRUE ELSE frozen IS FALSE END;
1899 IF hold_count >= hold_test.max_holds THEN
1900 result.fail_part := 'config.hold_matrix_test.max_holds';
1901 result.success := FALSE;
1907 IF item_object.age_protect IS NOT NULL THEN
1908 SELECT INTO age_protect_object * FROM config.rule_age_hold_protect WHERE id = item_object.age_protect;
1910 IF item_object.create_date + age_protect_object.age > NOW() THEN
1911 IF hold_test.distance_is_from_owner THEN
1912 SELECT INTO hold_transit_prox prox FROM actor.org_unit_prox WHERE from_org = item_cn_object.owning_lib AND to_org = pickup_ou;
1914 SELECT INTO hold_transit_prox prox FROM actor.org_unit_prox WHERE from_org = item_object.circ_lib AND to_org = pickup_ou;
1917 IF hold_transit_prox > age_protect_object.prox THEN
1918 result.fail_part := 'config.rule_age_hold_protect.prox';
1919 result.success := FALSE;
1932 $func$ LANGUAGE plpgsql;
1940 CREATE TABLE acq.currency_type (
1941 code TEXT PRIMARY KEY,
1945 -- Use the ISO 4217 abbreviations for currency codes
1946 INSERT INTO acq.currency_type (code, label) VALUES ('USD','US Dollars');
1947 INSERT INTO acq.currency_type (code, label) VALUES ('CAN','Canadian Dollars');
1948 INSERT INTO acq.currency_type (code, label) VALUES ('EUR','Euros');
1950 CREATE TABLE acq.exchange_rate (
1951 id SERIAL PRIMARY KEY,
1952 from_currency TEXT NOT NULL REFERENCES acq.currency_type (code) DEFERRABLE INITIALLY DEFERRED,
1953 to_currency TEXT NOT NULL REFERENCES acq.currency_type (code) DEFERRABLE INITIALLY DEFERRED,
1954 ratio NUMERIC NOT NULL,
1955 CONSTRAINT exchange_rate_from_to_once UNIQUE (from_currency,to_currency)
1958 INSERT INTO acq.exchange_rate (from_currency,to_currency,ratio) VALUES ('USD','CAN',1.2);
1959 INSERT INTO acq.exchange_rate (from_currency,to_currency,ratio) VALUES ('USD','EUR',0.5);
1961 CREATE TABLE acq.provider (
1962 id SERIAL PRIMARY KEY,
1964 owner INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
1965 currency_type TEXT NOT NULL REFERENCES acq.currency_type (code) DEFERRABLE INITIALLY DEFERRED,
1968 CONSTRAINT provider_name_once_per_owner UNIQUE (name,owner)
1971 CREATE TABLE acq.provider_holding_subfield_map (
1972 id SERIAL PRIMARY KEY,
1973 provider INT NOT NULL REFERENCES acq.provider (id) DEFERRABLE INITIALLY DEFERRED,
1974 name TEXT NOT NULL, -- barcode, price, etc
1975 subfield TEXT NOT NULL,
1976 CONSTRAINT name_once_per_provider UNIQUE (provider,name)
1979 CREATE TABLE acq.provider_address (
1980 id SERIAL PRIMARY KEY,
1981 valid BOOL NOT NULL DEFAULT TRUE,
1983 provider INT NOT NULL REFERENCES acq.provider (id) DEFERRABLE INITIALLY DEFERRED,
1984 street1 TEXT NOT NULL,
1988 state TEXT NOT NULL,
1989 country TEXT NOT NULL,
1990 post_code TEXT NOT NULL
1993 CREATE TABLE acq.provider_contact (
1994 id SERIAL PRIMARY KEY,
1995 provider INT NOT NULL REFERENCES acq.provider (id) DEFERRABLE INITIALLY DEFERRED,
1996 name TEXT NULL NULL,
1997 role TEXT, -- free-form.. e.g. "our sales guy"
2002 CREATE TABLE acq.provider_contact_address (
2003 id SERIAL PRIMARY KEY,
2004 valid BOOL NOT NULL DEFAULT TRUE,
2006 contact INT NOT NULL REFERENCES acq.provider_contact (id) DEFERRABLE INITIALLY DEFERRED,
2007 street1 TEXT NOT NULL,
2011 state TEXT NOT NULL,
2012 country TEXT NOT NULL,
2013 post_code TEXT NOT NULL
2017 CREATE TABLE acq.funding_source (
2018 id SERIAL PRIMARY KEY,
2020 owner INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
2021 currency_type TEXT NOT NULL REFERENCES acq.currency_type (code) DEFERRABLE INITIALLY DEFERRED,
2023 CONSTRAINT funding_source_name_once_per_owner UNIQUE (name,owner)
2026 CREATE TABLE acq.funding_source_credit (
2027 id SERIAL PRIMARY KEY,
2028 funding_source INT NOT NULL REFERENCES acq.funding_source (id) DEFERRABLE INITIALLY DEFERRED,
2029 amount NUMERIC NOT NULL,
2033 CREATE TABLE acq.fund (
2034 id SERIAL PRIMARY KEY,
2035 org INT NOT NULL REFERENCES actor.org_unit (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
2037 year INT NOT NULL DEFAULT EXTRACT( YEAR FROM NOW() ),
2038 currency_type TEXT NOT NULL REFERENCES acq.currency_type (code) DEFERRABLE INITIALLY DEFERRED,
2040 CONSTRAINT name_once_per_org_year UNIQUE (org,name,year)
2043 CREATE TABLE acq.fund_debit (
2044 id SERIAL PRIMARY KEY,
2045 fund INT NOT NULL REFERENCES acq.fund (id) DEFERRABLE INITIALLY DEFERRED,
2046 origin_amount NUMERIC NOT NULL, -- pre-exchange-rate amount
2047 origin_currency_type TEXT NOT NULL REFERENCES acq.currency_type (code) DEFERRABLE INITIALLY DEFERRED,
2048 amount NUMERIC NOT NULL,
2049 encumbrance BOOL NOT NULL DEFAULT TRUE,
2050 debit_type TEXT NOT NULL,
2051 xfer_destination INT REFERENCES acq.fund (id) DEFERRABLE INITIALLY DEFERRED,
2052 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
2055 CREATE TABLE acq.fund_allocation (
2056 id SERIAL PRIMARY KEY,
2057 funding_source INT NOT NULL REFERENCES acq.funding_source (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
2058 fund INT NOT NULL REFERENCES acq.fund (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
2060 percent NUMERIC CHECK (percent IS NULL OR percent BETWEEN 0.0 AND 100.0),
2061 allocator INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
2063 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
2064 CONSTRAINT allocation_amount_or_percent CHECK ((percent IS NULL AND amount IS NOT NULL) OR (percent IS NOT NULL AND amount IS NULL))
2068 CREATE TABLE acq.picklist (
2069 id SERIAL PRIMARY KEY,
2070 owner INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
2071 creator INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
2072 editor INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
2073 org_unit INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
2075 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
2076 edit_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
2077 CONSTRAINT name_once_per_owner UNIQUE (name,owner)
2080 CREATE TABLE acq.purchase_order (
2081 id SERIAL PRIMARY KEY,
2082 owner INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
2083 creator INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
2084 editor INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
2085 ordering_agency INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
2086 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
2087 edit_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
2088 provider INT NOT NULL REFERENCES acq.provider (id) DEFERRABLE INITIALLY DEFERRED,
2089 state TEXT NOT NULL DEFAULT 'new'
2091 CREATE INDEX po_owner_idx ON acq.purchase_order (owner);
2092 CREATE INDEX po_provider_idx ON acq.purchase_order (provider);
2093 CREATE INDEX po_state_idx ON acq.purchase_order (state);
2095 CREATE TABLE acq.po_note (
2096 id SERIAL PRIMARY KEY,
2097 purchase_order INT NOT NULL REFERENCES acq.purchase_order (id) DEFERRABLE INITIALLY DEFERRED,
2098 creator INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
2099 editor INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
2100 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
2101 edit_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
2104 CREATE INDEX po_note_po_idx ON acq.po_note (purchase_order);
2106 CREATE TABLE acq.lineitem (
2107 id BIGSERIAL PRIMARY KEY,
2108 creator INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
2109 editor INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
2110 selector INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
2111 provider INT REFERENCES acq.provider (id) DEFERRABLE INITIALLY DEFERRED,
2112 purchase_order INT REFERENCES acq.purchase_order (id) DEFERRABLE INITIALLY DEFERRED,
2113 picklist INT REFERENCES acq.picklist (id) DEFERRABLE INITIALLY DEFERRED,
2114 expected_recv_time TIMESTAMP WITH TIME ZONE,
2115 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
2116 edit_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
2118 eg_bib_id INT REFERENCES biblio.record_entry (id) DEFERRABLE INITIALLY DEFERRED,
2120 item_count INT NOT NULL DEFAULT 0,
2121 state TEXT NOT NULL DEFAULT 'new',
2122 CONSTRAINT picklist_or_po CHECK (picklist IS NOT NULL OR purchase_order IS NOT NULL)
2124 CREATE INDEX li_po_idx ON acq.lineitem (purchase_order);
2125 CREATE INDEX li_pl_idx ON acq.lineitem (picklist);
2127 CREATE TABLE acq.lineitem_note (
2128 id SERIAL PRIMARY KEY,
2129 lineitem INT NOT NULL REFERENCES acq.lineitem (id) DEFERRABLE INITIALLY DEFERRED,
2130 creator INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
2131 editor INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
2132 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
2133 edit_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
2136 CREATE INDEX li_note_li_idx ON acq.lineitem_note (lineitem);
2138 CREATE TABLE acq.lineitem_detail (
2139 id BIGSERIAL PRIMARY KEY,
2140 lineitem INT NOT NULL REFERENCES acq.lineitem (id) DEFERRABLE INITIALLY DEFERRED,
2141 fund INT REFERENCES acq.fund (id) DEFERRABLE INITIALLY DEFERRED,
2142 fund_debit INT REFERENCES acq.fund_debit (id) DEFERRABLE INITIALLY DEFERRED,
2143 eg_copy_id BIGINT REFERENCES asset.copy (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
2147 collection_code TEXT,
2148 circ_modifier TEXT REFERENCES config.circ_modifier (code) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
2149 owning_lib INT REFERENCES actor.org_unit (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
2150 location INT REFERENCES asset.copy_location (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
2151 recv_time TIMESTAMP WITH TIME ZONE
2154 CREATE INDEX li_detail_li_idx ON acq.lineitem_detail (lineitem);
2156 CREATE TABLE acq.lineitem_attr_definition (
2157 id BIGSERIAL PRIMARY KEY,
2159 description TEXT NOT NULL,
2160 remove TEXT NOT NULL DEFAULT '',
2161 ident BOOL NOT NULL DEFAULT FALSE
2164 CREATE TABLE acq.lineitem_marc_attr_definition (
2165 id BIGINT PRIMARY KEY DEFAULT NEXTVAL('acq.lineitem_attr_definition_id_seq'),
2167 ) INHERITS (acq.lineitem_attr_definition);
2169 CREATE TABLE acq.lineitem_provider_attr_definition (
2170 id BIGINT PRIMARY KEY DEFAULT NEXTVAL('acq.lineitem_attr_definition_id_seq'),
2171 xpath TEXT NOT NULL,
2172 provider INT NOT NULL REFERENCES acq.provider (id) DEFERRABLE INITIALLY DEFERRED
2173 ) INHERITS (acq.lineitem_attr_definition);
2175 CREATE TABLE acq.lineitem_generated_attr_definition (
2176 id BIGINT PRIMARY KEY DEFAULT NEXTVAL('acq.lineitem_attr_definition_id_seq'),
2178 ) INHERITS (acq.lineitem_attr_definition);
2180 CREATE TABLE acq.lineitem_usr_attr_definition (
2181 id BIGINT PRIMARY KEY DEFAULT NEXTVAL('acq.lineitem_attr_definition_id_seq'),
2182 usr INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED
2183 ) INHERITS (acq.lineitem_attr_definition);
2185 CREATE TABLE acq.lineitem_local_attr_definition (
2186 id BIGINT PRIMARY KEY DEFAULT NEXTVAL('acq.lineitem_attr_definition_id_seq')
2187 ) INHERITS (acq.lineitem_attr_definition);
2189 CREATE TABLE acq.lineitem_attr (
2190 id BIGSERIAL PRIMARY KEY,
2191 definition BIGINT NOT NULL,
2192 lineitem BIGINT NOT NULL REFERENCES acq.lineitem (id) DEFERRABLE INITIALLY DEFERRED,
2193 attr_type TEXT NOT NULL,
2194 attr_name TEXT NOT NULL,
2195 attr_value TEXT NOT NULL
2198 CREATE INDEX li_attr_li_idx ON acq.lineitem_attr (lineitem);
2199 CREATE INDEX li_attr_value_idx ON acq.lineitem_attr (attr_value);
2200 CREATE INDEX li_attr_definition_idx ON acq.lineitem_attr (definition);
2206 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('title','Title of work','//*[@tag="245"]/*[contains("abcmnopr",@code)]');
2207 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('author','Author of work','//*[@tag="100" or @tag="110" or @tag="113"]/*[contains("ad",@code)]');
2208 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('language','Language of work','//*[@tag="240"]/*[@code="l"][1]');
2209 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('pagination','Pagination','//*[@tag="300"]/*[@code="a"][1]');
2210 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath, remove ) VALUES ('isbn','ISBN','//*[@tag="020"]/*[@code="a"]', $r$(?:-|\s.+$)$r$);
2211 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath, remove ) VALUES ('issn','ISSN','//*[@tag="022"]/*[@code="a"]', $r$(?:-|\s.+$)$r$);
2212 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('price','Price','//*[@tag="020" or @tag="022"]/*[@code="c"][1]');
2213 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('identifier','Identifier','//*[@tag="001"]');
2214 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('publisher','Publisher','//*[@tag="260"]/*[@code="b"][1]');
2215 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('pubdate','Publication Date','//*[@tag="260"]/*[@code="c"][1]');
2216 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('edition','Edition','//*[@tag="250"]/*[@code="a"][1]');
2218 INSERT INTO acq.lineitem_local_attr_definition ( code, description ) VALUES ('estimated_price', 'Estimated Price');
2221 CREATE TABLE acq.distribution_formula (
2222 id SERIAL PRIMARY KEY,
2224 REFERENCES actor.org_unit(id) DEFERRABLE INITIALLY DEFERRED,
2226 skip_count INT NOT NULL DEFAULT 0,
2227 CONSTRAINT acqdf_name_once_per_owner UNIQUE (name, owner)
2230 CREATE TABLE acq.distribution_formula_entry (
2231 id SERIAL PRIMARY KEY,
2232 formula INTEGER NOT NULL REFERENCES acq.distribution_formula(id)
2234 DEFERRABLE INITIALLY DEFERRED,
2235 position INTEGER NOT NULL,
2236 item_count INTEGER NOT NULL,
2237 owning_lib INTEGER REFERENCES actor.org_unit(id)
2238 DEFERRABLE INITIALLY DEFERRED,
2239 location INTEGER REFERENCES asset.copy_location(id),
2240 CONSTRAINT acqdfe_lib_once_per_formula UNIQUE( formula, position ),
2241 CONSTRAINT acqdfe_must_be_somewhere
2242 CHECK( owning_lib IS NOT NULL OR location IS NOT NULL )
2245 CREATE TABLE acq.fund_tag (
2246 id SERIAL PRIMARY KEY,
2248 REFERENCES actor.org_unit(id) DEFERRABLE INITIALLY DEFERRED,
2250 CONSTRAINT acqft_tag_once_per_owner UNIQUE (name, owner)
2253 CREATE TABLE acq.fund_tag_map (
2254 id SERIAL PRIMARY KEY,
2255 fund INTEGER NOT NULL REFERENCES acq.fund(id)
2256 DEFERRABLE INITIALLY DEFERRED,
2257 tag INTEGER REFERENCES acq.fund_tag(id)
2259 DEFERRABLE INITIALLY DEFERRED,
2260 CONSTRAINT acqftm_fund_once_per_tag UNIQUE( fund, tag )
2265 CREATE TYPE acq.flat_lineitem_holding_subfield AS (lineitem int, holding int, subfield text, data text);
2266 CREATE OR REPLACE FUNCTION acq.extract_holding_attr_table (lineitem int, tag text) RETURNS SETOF acq.flat_lineitem_holding_subfield AS $$
2269 lida acq.flat_lineitem_holding_subfield%ROWTYPE;
2272 SELECT COUNT(*) INTO counter
2277 '//*[@tag="' || tag || '"]',
2279 ) as t(i int,c text);
2281 FOR i IN 1 .. counter LOOP
2284 FROM ( SELECT id,i,t,v
2289 '//*[@tag="' || tag || '"][position()=' || i || ']/*/@code|' ||
2290 '//*[@tag="' || tag || '"][position()=' || i || ']/*[@code]',
2292 ) as t(id int,t text,v text)
2301 $$ LANGUAGE PLPGSQL;
2303 CREATE TYPE acq.flat_lineitem_detail AS (lineitem int, holding int, attr text, data text);
2304 CREATE OR REPLACE FUNCTION acq.extract_provider_holding_data ( lineitem_i int ) RETURNS SETOF acq.flat_lineitem_detail AS $$
2308 lida acq.flat_lineitem_detail%ROWTYPE;
2310 SELECT provider INTO prov_i FROM acq.lineitem WHERE id = lineitem_i;
2311 IF NOT FOUND THEN RETURN; END IF;
2313 SELECT holding_tag INTO tag_t FROM acq.provider WHERE id = prov_i;
2314 IF NOT FOUND OR tag_t IS NULL THEN RETURN; END IF;
2321 FROM acq.extract_holding_attr_table( lineitem_i, tag_t ) h
2322 JOIN acq.provider_holding_subfield_map a USING (subfield)
2323 WHERE a.provider = prov_i
2330 $$ LANGUAGE PLPGSQL;
2332 -- select * from acq.extract_provider_holding_data(699);
2334 CREATE OR REPLACE FUNCTION public.extract_acq_marc_field ( BIGINT, TEXT, TEXT) RETURNS TEXT AS $$
2335 SELECT public.extract_marc_field('acq.lineitem', $1, $2, $3);
2338 CREATE OR REPLACE FUNCTION public.ingest_acq_marc ( ) RETURNS TRIGGER AS $$
2346 FOR adef IN SELECT *,tableoid FROM acq.lineitem_attr_definition LOOP
2348 SELECT relname::TEXT INTO atype FROM pg_class WHERE oid = adef.tableoid;
2350 IF (atype NOT IN ('lineitem_usr_attr_definition','lineitem_local_attr_definition')) THEN
2351 IF (atype = 'lineitem_provider_attr_definition') THEN
2352 SELECT provider INTO prov FROM acq.lineitem_provider_attr_definition WHERE id = adef.id;
2353 CONTINUE WHEN NEW.provider IS NULL OR prov <> NEW.provider;
2356 IF (atype = 'lineitem_provider_attr_definition') THEN
2357 SELECT xpath INTO xpath_string FROM acq.lineitem_provider_attr_definition WHERE id = adef.id;
2358 ELSIF (atype = 'lineitem_marc_attr_definition') THEN
2359 SELECT xpath INTO xpath_string FROM acq.lineitem_marc_attr_definition WHERE id = adef.id;
2360 ELSIF (atype = 'lineitem_generated_attr_definition') THEN
2361 SELECT xpath INTO xpath_string FROM acq.lineitem_generated_attr_definition WHERE id = adef.id;
2364 SELECT extract_acq_marc_field(id, xpath_string, adef.remove) INTO value FROM acq.lineitem WHERE id = NEW.id;
2366 IF (value IS NOT NULL AND value <> '') THEN
2367 INSERT INTO acq.lineitem_attr (lineitem, definition, attr_type, attr_name, attr_value)
2368 VALUES (NEW.id, adef.id, atype, adef.code, value);
2377 $$ LANGUAGE PLPGSQL;
2379 CREATE OR REPLACE FUNCTION public.cleanup_acq_marc ( ) RETURNS TRIGGER AS $$
2381 IF TG_OP = 'UPDATE' THEN
2382 DELETE FROM acq.lineitem_attr
2383 WHERE lineitem = OLD.id AND attr_type IN ('lineitem_provider_attr_definition', 'lineitem_marc_attr_definition','lineitem_generated_attr_definition');
2386 DELETE FROM acq.lineitem_attr WHERE lineitem = OLD.id;
2390 $$ LANGUAGE PLPGSQL;
2392 CREATE TRIGGER cleanup_lineitem_trigger
2393 BEFORE UPDATE OR DELETE ON acq.lineitem
2394 FOR EACH ROW EXECUTE PROCEDURE public.cleanup_acq_marc();
2396 CREATE TRIGGER ingest_lineitem_trigger
2397 AFTER INSERT OR UPDATE ON acq.lineitem
2398 FOR EACH ROW EXECUTE PROCEDURE public.ingest_acq_marc();
2400 CREATE OR REPLACE FUNCTION acq.exchange_ratio ( from_ex TEXT, to_ex TEXT ) RETURNS NUMERIC AS $$
2404 IF from_ex = to_ex THEN
2408 SELECT ratio INTO rat FROM acq.exchange_rate WHERE from_currency = from_ex AND to_currency = to_ex;
2413 SELECT ratio INTO rat FROM acq.exchange_rate WHERE from_currency = to_ex AND to_currency = from_ex;
2422 $$ LANGUAGE PLPGSQL;
2424 CREATE OR REPLACE FUNCTION acq.exchange_ratio ( TEXT, TEXT, NUMERIC ) RETURNS NUMERIC AS $$
2425 SELECT $3 * acq.exchange_ratio($1, $2);
2428 CREATE OR REPLACE VIEW acq.funding_source_credit_total AS
2429 SELECT funding_source,
2430 SUM(amount) AS amount
2431 FROM acq.funding_source_credit
2434 CREATE OR REPLACE VIEW acq.funding_source_allocation_total AS
2435 SELECT funding_source,
2436 SUM(amount)::NUMERIC(100,2) AS amount
2438 SELECT funding_source,
2439 SUM(a.amount)::NUMERIC(100,2) AS amount
2440 FROM acq.fund_allocation a
2441 WHERE a.percent IS NULL
2444 SELECT funding_source,
2445 SUM( (SELECT SUM(amount) FROM acq.funding_source_credit c WHERE c.funding_source = a.funding_source) * (a.percent/100.0) )::NUMERIC(100,2) AS amount
2446 FROM acq.fund_allocation a
2447 WHERE a.amount IS NULL
2452 CREATE OR REPLACE VIEW acq.funding_source_balance AS
2453 SELECT COALESCE(c.funding_source, a.funding_source) AS funding_source,
2454 SUM(COALESCE(c.amount,0.0) - COALESCE(a.amount,0.0))::NUMERIC(100,2) AS amount
2455 FROM acq.funding_source_credit_total c
2456 FULL JOIN acq.funding_source_allocation_total a USING (funding_source)
2459 CREATE OR REPLACE VIEW acq.fund_allocation_total AS
2461 SUM(amount)::NUMERIC(100,2) AS amount
2464 SUM(a.amount * acq.exchange_ratio(s.currency_type, f.currency_type))::NUMERIC(100,2) AS amount
2465 FROM acq.fund_allocation a
2466 JOIN acq.fund f ON (a.fund = f.id)
2467 JOIN acq.funding_source s ON (a.funding_source = s.id)
2468 WHERE a.percent IS NULL
2472 SUM( (SELECT SUM(amount) FROM acq.funding_source_credit c WHERE c.funding_source = a.funding_source) * acq.exchange_ratio(s.currency_type, f.currency_type) * (a.percent/100.0) )::NUMERIC(100,2) AS amount
2473 FROM acq.fund_allocation a
2474 JOIN acq.fund f ON (a.fund = f.id)
2475 JOIN acq.funding_source s ON (a.funding_source = s.id)
2476 WHERE a.amount IS NULL
2481 CREATE OR REPLACE VIEW acq.fund_debit_total AS
2484 SUM(amount) AS amount
2488 CREATE OR REPLACE VIEW acq.fund_encumbrance_total AS
2490 SUM(amount) AS amount
2491 FROM acq.fund_debit_total
2492 WHERE encumbrance IS TRUE
2495 CREATE OR REPLACE VIEW acq.fund_spent_total AS
2497 SUM(amount) AS amount
2498 FROM acq.fund_debit_total
2499 WHERE encumbrance IS FALSE
2502 CREATE OR REPLACE VIEW acq.fund_combined_balance AS
2504 c.amount - COALESCE(d.amount,0.0) AS amount
2505 FROM acq.fund_allocation_total c
2506 LEFT JOIN acq.fund_debit_total d USING (fund);
2508 CREATE OR REPLACE VIEW acq.fund_spent_balance AS
2510 c.amount - COALESCE(d.amount,0.0) AS amount
2511 FROM acq.fund_allocation_total c
2512 LEFT JOIN acq.fund_spent_total d USING (fund);
2514 CREATE SCHEMA serial;
2516 CREATE TABLE serial.record_entry (
2517 id BIGSERIAL PRIMARY KEY,
2518 record BIGINT REFERENCES biblio.record_entry (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
2519 owning_lib INT NOT NULL DEFAULT 1 REFERENCES actor.org_unit (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
2520 creator INT NOT NULL DEFAULT 1,
2521 editor INT NOT NULL DEFAULT 1,
2523 create_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
2524 edit_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
2525 active BOOL NOT NULL DEFAULT TRUE,
2526 deleted BOOL NOT NULL DEFAULT FALSE,
2528 last_xact_id TEXT NOT NULL
2530 CREATE INDEX serial_record_entry_creator_idx ON serial.record_entry ( creator );
2531 CREATE INDEX serial_record_entry_editor_idx ON serial.record_entry ( editor );
2532 CREATE INDEX serial_record_entry_owning_lib_idx ON serial.record_entry ( owning_lib, deleted );
2534 CREATE TABLE serial.subscription (
2535 id SERIAL PRIMARY KEY,
2536 callnumber BIGINT REFERENCES asset.call_number (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
2537 uri INT REFERENCES asset.uri (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
2538 start_date DATE NOT NULL,
2539 end_date DATE -- interpret NULL as current subscription
2542 CREATE TABLE serial.binding_unit (
2543 id SERIAL PRIMARY KEY,
2544 subscription INT NOT NULL REFERENCES serial.subscription (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
2545 label TEXT NOT NULL,
2546 CONSTRAINT bu_label_once_per_sub UNIQUE (subscription, label)
2549 CREATE TABLE serial.issuance (
2550 id SERIAL PRIMARY KEY,
2551 subscription INT NOT NULL REFERENCES serial.subscription (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
2552 target_copy BIGINT REFERENCES asset.copy (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
2553 location BIGINT REFERENCES asset.copy_location(id) DEFERRABLE INITIALLY DEFERRED,
2554 binding_unit INT REFERENCES serial.binding_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
2558 CREATE TABLE serial.bib_summary (
2559 id SERIAL PRIMARY KEY,
2560 subscription INT UNIQUE NOT NULL REFERENCES serial.subscription (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
2561 generated_coverage TEXT NOT NULL,
2562 textual_holdings TEXT
2565 CREATE TABLE serial.sup_summary (
2566 id SERIAL PRIMARY KEY,
2567 subscription INT UNIQUE NOT NULL REFERENCES serial.subscription (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
2568 generated_coverage TEXT NOT NULL,
2569 textual_holdings TEXT
2572 CREATE TABLE serial.index_summary (
2573 id SERIAL PRIMARY KEY,
2574 subscription INT UNIQUE NOT NULL REFERENCES serial.subscription (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
2575 generated_coverage TEXT NOT NULL,
2576 textual_holdings TEXT
2580 CREATE OR REPLACE FUNCTION search.staged_fts (
2582 param_search_ou INT,
2584 param_searches TEXT, -- JSON hash, to be turned into a resultset via search.parse_search_args
2585 param_statuses INT[],
2586 param_locations INT[],
2587 param_audience TEXT[],
2588 param_language TEXT[],
2589 param_lit_form TEXT[],
2592 param_vformats TEXT[],
2593 param_bib_level TEXT[],
2597 param_between TEXT[],
2598 param_pref_lang TEXT,
2599 param_pref_lang_multiplier REAL,
2601 param_sort_desc BOOL,
2604 param_rel_limit INT,
2605 param_chk_limit INT,
2608 ) RETURNS SETOF search.search_result AS $func$
2611 current_res search.search_result%ROWTYPE;
2612 query_part search.search_args%ROWTYPE;
2613 phrase_query_part search.search_args%ROWTYPE;
2618 rank_adjust search.relevance_adjustment%ROWTYPE;
2623 ranks TEXT[] := '{}';
2624 query_table_alias TEXT;
2625 from_alias_array TEXT[] := '{}';
2626 used_ranks TEXT[] := '{}';
2628 mb_field_list INT[];
2629 search_org_list INT[];
2630 select_clause TEXT := 'SELECT';
2631 from_clause TEXT := ' FROM metabib.metarecord_source_map m JOIN metabib.rec_descriptor mrd ON (m.source = mrd.record) ';
2632 where_clause TEXT := ' WHERE 1=1 ';
2633 mrd_used BOOL := FALSE;
2634 sort_desc BOOL := FALSE;
2637 core_cursor REFCURSOR;
2638 core_rel_query TEXT;
2639 vis_limit_query TEXT;
2640 inner_where_clause TEXT;
2642 total_count INT := 0;
2643 check_count INT := 0;
2644 deleted_count INT := 0;
2645 visible_count INT := 0;
2646 excluded_count INT := 0;
2650 core_rel_limit := COALESCE( param_rel_limit, 25000 );
2651 core_chk_limit := COALESCE( param_chk_limit, 1000 );
2652 core_skip_chk := COALESCE( param_skip_chk, 1 );
2655 select_clause := select_clause || ' m.metarecord as id, array_accum(distinct m.source) as records,';
2657 select_clause := select_clause || ' m.source as id, array_accum(distinct m.source) as records,';
2660 -- first we need to construct the base query
2661 FOR query_part IN SELECT * FROM search.parse_search_args(param_searches) WHERE term_type = 'fts_query' LOOP
2663 inner_where_clause := 'index_vector @@ ' || query_part.term;
2665 IF query_part.field_name IS NOT NULL THEN
2667 SELECT id INTO mb_field
2668 FROM config.metabib_field
2669 WHERE field_class = query_part.field_class
2670 AND name = query_part.field_name;
2673 inner_where_clause := inner_where_clause ||
2674 ' AND ' || 'field = ' || mb_field;
2679 -- moving on to the rank ...
2680 SELECT * INTO query_part
2681 FROM search.parse_search_args(param_searches)
2682 WHERE term_type = 'fts_rank'
2683 AND table_alias = query_part.table_alias;
2685 current_rank := query_part.term || ' * ' || query_part.table_alias || '_weight.weight';
2687 IF query_part.field_name IS NOT NULL THEN
2689 SELECT array_accum(distinct id) INTO mb_field_list
2690 FROM config.metabib_field
2691 WHERE field_class = query_part.field_class
2692 AND name = query_part.field_name;
2696 SELECT array_accum(distinct id) INTO mb_field_list
2697 FROM config.metabib_field
2698 WHERE field_class = query_part.field_class;
2702 FOR rank_adjust IN SELECT * FROM search.relevance_adjustment WHERE active AND field IN ( SELECT * FROM search.explode_array( mb_field_list ) ) LOOP
2704 IF NOT rank_adjust.bump_type = ANY (used_ranks) THEN
2706 IF rank_adjust.bump_type = 'first_word' THEN
2707 SELECT term INTO tmp_text
2708 FROM search.parse_search_args(param_searches)
2709 WHERE table_alias = query_part.table_alias AND term_type = 'word'
2713 tmp_text := query_part.table_alias || '.value ILIKE ' || quote_literal( tmp_text || '%' );
2715 ELSIF rank_adjust.bump_type = 'word_order' THEN
2716 SELECT array_to_string( array_accum( term ), '%' ) INTO tmp_text
2717 FROM search.parse_search_args(param_searches)
2718 WHERE table_alias = query_part.table_alias AND term_type = 'word';
2720 tmp_text := query_part.table_alias || '.value ILIKE ' || quote_literal( '%' || tmp_text || '%' );
2722 ELSIF rank_adjust.bump_type = 'full_match' THEN
2723 SELECT array_to_string( array_accum( term ), E'\\s+' ) INTO tmp_text
2724 FROM search.parse_search_args(param_searches)
2725 WHERE table_alias = query_part.table_alias AND term_type = 'word';
2727 tmp_text := query_part.table_alias || '.value ~ ' || quote_literal( '^' || tmp_text || E'\\W*$' );
2732 IF tmp_text IS NOT NULL THEN
2733 current_rank := current_rank || ' * ( CASE WHEN ' || tmp_text ||
2734 ' THEN ' || rank_adjust.multiplier || '::REAL ELSE 1.0 END )';
2737 used_ranks := array_append( used_ranks, rank_adjust.bump_type );
2743 ranks := array_append( ranks, current_rank );
2746 FOR phrase_query_part IN
2748 FROM search.parse_search_args(param_searches)
2749 WHERE term_type = 'phrase'
2750 AND table_alias = query_part.table_alias LOOP
2752 tmp_text := replace( phrase_query_part.term, '*', E'\\*' );
2753 tmp_text := replace( tmp_text, '?', E'\\?' );
2754 tmp_text := replace( tmp_text, '+', E'\\+' );
2755 tmp_text := replace( tmp_text, '|', E'\\|' );
2756 tmp_text := replace( tmp_text, '(', E'\\(' );
2757 tmp_text := replace( tmp_text, ')', E'\\)' );
2758 tmp_text := replace( tmp_text, '[', E'\\[' );
2759 tmp_text := replace( tmp_text, ']', E'\\]' );
2761 inner_where_clause := inner_where_clause || ' AND ' || 'value ~* ' || quote_literal( E'(^|\\W+)' || regexp_replace(tmp_text, E'\\s+',E'\\\\s+','g') || E'(\\W+|\$)' );
2765 query_table := search.pick_table(query_part.field_class);
2767 from_clause := from_clause ||
2768 ' JOIN ( SELECT * FROM ' || query_table || ' WHERE ' || inner_where_clause ||
2769 CASE WHEN core_rel_limit > 0 THEN ' LIMIT ' || core_rel_limit::TEXT ELSE '' END || ' ) AS ' || query_part.table_alias ||
2770 ' ON ( m.source = ' || query_part.table_alias || '.source )' ||
2771 ' JOIN config.metabib_field AS ' || query_part.table_alias || '_weight' ||
2772 ' ON ( ' || query_part.table_alias || '.field = ' || query_part.table_alias || '_weight.id AND ' || query_part.table_alias || '_weight.search_field)';
2774 from_alias_array := array_append(from_alias_array, query_part.table_alias);
2778 IF param_pref_lang IS NOT NULL AND param_pref_lang_multiplier IS NOT NULL THEN
2779 current_rank := ' CASE WHEN mrd.item_lang = ' || quote_literal( param_pref_lang ) ||
2780 ' THEN ' || param_pref_lang_multiplier || '::REAL ELSE 1.0 END ';
2782 -- ranks := array_append( ranks, current_rank );
2785 current_rank := ' AVG( ( (' || array_to_string( ranks, ') + (' ) || ') ) * ' || current_rank || ' ) ';
2786 select_clause := select_clause || current_rank || ' AS rel,';
2788 sort_desc = param_sort_desc;
2790 IF param_sort = 'pubdate' THEN
2792 tmp_text := '999999';
2793 IF param_sort_desc THEN tmp_text := '0'; END IF;
2795 current_rank := $$ COALESCE( FIRST(NULLIF(REGEXP_REPLACE(mrd.date1, E'\\D+', '9', 'g'),'')), $$ || quote_literal(tmp_text) || $$ )::INT $$;
2797 ELSIF param_sort = 'title' THEN
2799 tmp_text := 'zzzzzz';
2800 IF param_sort_desc THEN tmp_text := ' '; END IF;
2803 ( COALESCE( FIRST ((
2804 SELECT LTRIM(SUBSTR( frt.value, COALESCE(SUBSTRING(frt.ind2 FROM E'\\d+'),'0')::INT + 1 ))
2805 FROM metabib.full_rec frt
2806 WHERE frt.record = m.source
2808 AND frt.subfield = 'a'
2810 )),$$ || quote_literal(tmp_text) || $$))
2813 ELSIF param_sort = 'author' THEN
2815 tmp_text := 'zzzzzz';
2816 IF param_sort_desc THEN tmp_text := ' '; END IF;
2819 ( COALESCE( FIRST ((
2820 SELECT LTRIM(fra.value)
2821 FROM metabib.full_rec fra
2822 WHERE fra.record = m.source
2823 AND fra.tag LIKE '1%'
2824 AND fra.subfield = 'a'
2825 ORDER BY fra.tag::text::int
2827 )),$$ || quote_literal(tmp_text) || $$))
2830 ELSIF param_sort = 'create_date' THEN
2831 current_rank := $$( FIRST (( SELECT create_date FROM biblio.record_entry rbr WHERE rbr.id = m.source)) )$$;
2832 ELSIF param_sort = 'edit_date' THEN
2833 current_rank := $$( FIRST (( SELECT edit_date FROM biblio.record_entry rbr WHERE rbr.id = m.source)) )$$;
2835 sort_desc := NOT COALESCE(param_sort_desc, FALSE);
2838 select_clause := select_clause || current_rank || ' AS rank';
2840 -- now add the other qualifiers
2841 IF param_audience IS NOT NULL AND array_upper(param_audience, 1) > 0 THEN
2842 where_clause = where_clause || $$ AND mrd.audience IN ('$$ || array_to_string(param_audience, $$','$$) || $$') $$;
2845 IF param_language IS NOT NULL AND array_upper(param_language, 1) > 0 THEN
2846 where_clause = where_clause || $$ AND mrd.item_lang IN ('$$ || array_to_string(param_language, $$','$$) || $$') $$;
2849 IF param_lit_form IS NOT NULL AND array_upper(param_lit_form, 1) > 0 THEN
2850 where_clause = where_clause || $$ AND mrd.lit_form IN ('$$ || array_to_string(param_lit_form, $$','$$) || $$') $$;
2853 IF param_types IS NOT NULL AND array_upper(param_types, 1) > 0 THEN
2854 where_clause = where_clause || $$ AND mrd.item_type IN ('$$ || array_to_string(param_types, $$','$$) || $$') $$;
2857 IF param_forms IS NOT NULL AND array_upper(param_forms, 1) > 0 THEN
2858 where_clause = where_clause || $$ AND mrd.item_form IN ('$$ || array_to_string(param_forms, $$','$$) || $$') $$;
2861 IF param_vformats IS NOT NULL AND array_upper(param_vformats, 1) > 0 THEN
2862 where_clause = where_clause || $$ AND mrd.vr_format IN ('$$ || array_to_string(param_vformats, $$','$$) || $$') $$;
2865 IF param_bib_level IS NOT NULL AND array_upper(param_bib_level, 1) > 0 THEN
2866 where_clause = where_clause || $$ AND mrd.bib_level IN ('$$ || array_to_string(param_bib_level, $$','$$) || $$') $$;
2869 IF param_before IS NOT NULL AND param_before <> '' THEN
2870 where_clause = where_clause || $$ AND mrd.date1 <= $$ || quote_literal(param_before) || ' ';
2873 IF param_after IS NOT NULL AND param_after <> '' THEN
2874 where_clause = where_clause || $$ AND mrd.date1 >= $$ || quote_literal(param_after) || ' ';
2877 IF param_during IS NOT NULL AND param_during <> '' THEN
2878 where_clause = where_clause || $$ AND $$ || quote_literal(param_during) || $$ BETWEEN mrd.date1 AND mrd.date2 $$;
2881 IF param_between IS NOT NULL AND array_upper(param_between, 1) > 1 THEN
2882 where_clause = where_clause || $$ AND mrd.date1 BETWEEN '$$ || array_to_string(param_between, $$' AND '$$) || $$' $$;
2885 core_rel_query := select_clause || from_clause || where_clause ||
2886 ' GROUP BY 1 ORDER BY 4' || CASE WHEN sort_desc THEN ' DESC' ELSE ' ASC' END || ';';
2887 --RAISE NOTICE 'Base Query: %', core_rel_query;
2889 IF param_search_ou > 0 THEN
2890 IF param_depth IS NOT NULL THEN
2891 SELECT array_accum(distinct id) INTO search_org_list FROM actor.org_unit_descendants( param_search_ou, param_depth );
2893 SELECT array_accum(distinct id) INTO search_org_list FROM actor.org_unit_descendants( param_search_ou );
2895 ELSIF param_search_ou < 0 THEN
2896 SELECT array_accum(distinct org_unit) INTO search_org_list FROM actor.org_lasso_map WHERE lasso = -param_search_ou;
2897 ELSIF param_search_ou = 0 THEN
2898 -- reserved for user lassos (ou_buckets/type='lasso') with ID passed in depth ... hack? sure.
2901 OPEN core_cursor FOR EXECUTE core_rel_query;
2905 FETCH core_cursor INTO core_result;
2906 EXIT WHEN NOT FOUND;
2909 IF total_count % 1000 = 0 THEN
2910 -- RAISE NOTICE ' % total, % checked so far ... ', total_count, check_count;
2913 IF core_chk_limit > 0 AND total_count - core_skip_chk + 1 >= core_chk_limit THEN
2914 total_count := total_count + 1;
2918 total_count := total_count + 1;
2920 CONTINUE WHEN param_skip_chk IS NOT NULL and total_count < param_skip_chk;
2922 check_count := check_count + 1;
2924 PERFORM 1 FROM biblio.record_entry b WHERE NOT b.deleted AND b.id IN ( SELECT * FROM search.explode_array( core_result.records ) );
2926 -- RAISE NOTICE ' % were all deleted ... ', core_result.records;
2927 deleted_count := deleted_count + 1;
2932 FROM biblio.record_entry b
2933 JOIN config.bib_source s ON (b.source = s.id)
2934 WHERE s.transcendant
2935 AND b.id IN ( SELECT * FROM search.explode_array( core_result.records ) );
2938 -- RAISE NOTICE ' % were all transcendant ... ', core_result.records;
2939 visible_count := visible_count + 1;
2941 current_res.id = core_result.id;
2942 current_res.rel = core_result.rel;
2946 SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
2950 current_res.record = core_result.records[1];
2952 current_res.record = NULL;
2955 RETURN NEXT current_res;
2961 FROM asset.call_number cn
2962 JOIN asset.uri_call_number_map map ON (map.call_number = cn.id)
2963 JOIN asset.uri uri ON (map.uri = uri.id)
2964 WHERE NOT cn.deleted
2965 AND cn.label = '##URI##'
2967 AND ( param_locations IS NULL OR array_upper(param_locations, 1) IS NULL )
2968 AND cn.record IN ( SELECT * FROM search.explode_array( core_result.records ) )
2969 AND cn.owning_lib IN ( SELECT * FROM search.explode_array( search_org_list ) )
2973 -- RAISE NOTICE ' % have at least one URI ... ', core_result.records;
2974 visible_count := visible_count + 1;
2976 current_res.id = core_result.id;
2977 current_res.rel = core_result.rel;
2981 SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
2985 current_res.record = core_result.records[1];
2987 current_res.record = NULL;
2990 RETURN NEXT current_res;
2995 IF param_statuses IS NOT NULL AND array_upper(param_statuses, 1) > 0 THEN
2998 FROM asset.call_number cn
2999 JOIN asset.copy cp ON (cp.call_number = cn.id)
3000 WHERE NOT cn.deleted
3002 AND cp.status IN ( SELECT * FROM search.explode_array( param_statuses ) )
3003 AND cn.record IN ( SELECT * FROM search.explode_array( core_result.records ) )
3004 AND cp.circ_lib IN ( SELECT * FROM search.explode_array( search_org_list ) )
3008 -- RAISE NOTICE ' % were all status-excluded ... ', core_result.records;
3009 excluded_count := excluded_count + 1;
3015 IF param_locations IS NOT NULL AND array_upper(param_locations, 1) > 0 THEN
3018 FROM asset.call_number cn
3019 JOIN asset.copy cp ON (cp.call_number = cn.id)
3020 WHERE NOT cn.deleted
3022 AND cp.location IN ( SELECT * FROM search.explode_array( param_locations ) )
3023 AND cn.record IN ( SELECT * FROM search.explode_array( core_result.records ) )
3024 AND cp.circ_lib IN ( SELECT * FROM search.explode_array( search_org_list ) )
3028 -- RAISE NOTICE ' % were all copy_location-excluded ... ', core_result.records;
3029 excluded_count := excluded_count + 1;
3035 IF staff IS NULL OR NOT staff THEN
3038 FROM asset.call_number cn
3039 JOIN asset.copy cp ON (cp.call_number = cn.id)
3040 JOIN actor.org_unit a ON (cp.circ_lib = a.id)
3041 JOIN asset.copy_location cl ON (cp.location = cl.id)
3042 JOIN config.copy_status cs ON (cp.status = cs.id)
3043 WHERE NOT cn.deleted
3049 AND cp.circ_lib IN ( SELECT * FROM search.explode_array( search_org_list ) )
3050 AND cn.record IN ( SELECT * FROM search.explode_array( core_result.records ) )
3054 -- RAISE NOTICE ' % were all visibility-excluded ... ', core_result.records;
3055 excluded_count := excluded_count + 1;
3062 FROM asset.call_number cn
3063 JOIN asset.copy cp ON (cp.call_number = cn.id)
3064 JOIN actor.org_unit a ON (cp.circ_lib = a.id)
3065 JOIN asset.copy_location cl ON (cp.location = cl.id)
3066 WHERE NOT cn.deleted
3068 AND cp.circ_lib IN ( SELECT * FROM search.explode_array( search_org_list ) )
3069 AND cn.record IN ( SELECT * FROM search.explode_array( core_result.records ) )
3075 FROM asset.call_number cn
3076 WHERE cn.record IN ( SELECT * FROM search.explode_array( core_result.records ) )
3080 -- RAISE NOTICE ' % were all visibility-excluded ... ', core_result.records;
3081 excluded_count := excluded_count + 1;
3089 visible_count := visible_count + 1;
3091 current_res.id = core_result.id;
3092 current_res.rel = core_result.rel;
3096 SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
3100 current_res.record = core_result.records[1];
3102 current_res.record = NULL;
3105 RETURN NEXT current_res;
3107 IF visible_count % 1000 = 0 THEN
3108 -- RAISE NOTICE ' % visible so far ... ', visible_count;
3113 current_res.id = NULL;
3114 current_res.rel = NULL;
3115 current_res.record = NULL;
3116 current_res.total = total_count;
3117 current_res.checked = check_count;
3118 current_res.deleted = deleted_count;
3119 current_res.visible = visible_count;
3120 current_res.excluded = excluded_count;
3124 RETURN NEXT current_res;
3127 $func$ LANGUAGE PLPGSQL;
3130 CREATE TABLE config.idl_field_doc (
3131 id BIGSERIAL PRIMARY KEY,
3132 fm_class TEXT NOT NULL,
3133 field TEXT NOT NULL,
3134 owner INT NOT NULL REFERENCES actor.org_unit (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
3135 string TEXT NOT NULL
3137 CREATE UNIQUE INDEX idl_field_doc_identity ON config.idl_field_doc (fm_class,field,owner);
3140 INSERT INTO config.xml_transform VALUES ( 'mods33', 'http://www.loc.gov/mods/v3', 'mods33', '');
3142 INSERT INTO container.copy_bucket_type (code,label) VALUES ('misc', 'Miscellaneous');
3143 INSERT INTO container.copy_bucket_type (code,label) VALUES ('staff_client', 'General Staff Client container');
3144 INSERT INTO container.call_number_bucket_type (code,label) VALUES ('misc', 'Miscellaneous');
3145 INSERT INTO container.biblio_record_entry_bucket_type (code,label) VALUES ('misc', 'Miscellaneous');
3146 INSERT INTO container.biblio_record_entry_bucket_type (code,label) VALUES ('staff_client', 'General Staff Client container');
3147 INSERT INTO container.biblio_record_entry_bucket_type (code,label) VALUES ('bookbag', 'Book Bag');
3148 INSERT INTO container.biblio_record_entry_bucket_type (code,label) VALUES ('reading_list', 'Reading List');
3150 INSERT INTO container.user_bucket_type (code,label) VALUES ('misc', 'Miscellaneous');
3151 INSERT INTO container.user_bucket_type (code,label) VALUES ('folks', 'Friends');
3152 INSERT INTO container.user_bucket_type (code,label) VALUES ('folks:pub_book_bags.view', 'List Published Book Bags');
3153 INSERT INTO container.user_bucket_type (code,label) VALUES ('folks:pub_book_bags.add', 'Add to Published Book Bags');
3154 INSERT INTO container.user_bucket_type (code,label) VALUES ('folks:circ.view', 'View Circulations');
3155 INSERT INTO container.user_bucket_type (code,label) VALUES ('folks:circ.renew', 'Renew Circulations');
3156 INSERT INTO container.user_bucket_type (code,label) VALUES ('folks:circ.checkout', 'Checkout Items');
3157 INSERT INTO container.user_bucket_type (code,label) VALUES ('folks:hold.view', 'View Holds');
3158 INSERT INTO container.user_bucket_type (code,label) VALUES ('folks:hold.cancel', 'Cancel Holds');
3162 CREATE SCHEMA action_trigger;
3164 CREATE TABLE action_trigger.hook (
3165 key TEXT PRIMARY KEY,
3166 core_type TEXT NOT NULL,
3168 passive BOOL NOT NULL DEFAULT FALSE
3170 INSERT INTO action_trigger.hook (key,core_type,description) VALUES ('checkout','circ','Item checked out to user');
3171 INSERT INTO action_trigger.hook (key,core_type,description) VALUES ('checkin','circ','Item checked in');
3172 INSERT INTO action_trigger.hook (key,core_type,description) VALUES ('lost','circ','Circulating Item marked Lost');
3173 INSERT INTO action_trigger.hook (key,core_type,description) VALUES ('lost.found','circ','Lost Circulating Item checked in');
3174 INSERT INTO action_trigger.hook (key,core_type,description) VALUES ('lost.auto','circ','Circulating Item automatically marked lost');
3175 INSERT INTO action_trigger.hook (key,core_type,description) VALUES ('claims_returned','circ','Circulating Item marked Claims Returned');
3176 INSERT INTO action_trigger.hook (key,core_type,description) VALUES ('claims_returned.found','circ','Claims Returned Circulating Item is checked in');
3177 INSERT INTO action_trigger.hook (key,core_type,description) VALUES ('missing','acp','Item marked Missing');
3178 INSERT INTO action_trigger.hook (key,core_type,description) VALUES ('missing.found','acp','Missing Item checked in');
3179 INSERT INTO action_trigger.hook (key,core_type,description) VALUES ('transit.start','acp','An Item is placed into transit');
3180 INSERT INTO action_trigger.hook (key,core_type,description) VALUES ('transit.finish','acp','An Item is received from a transit');
3181 INSERT INTO action_trigger.hook (key,core_type,description) VALUES ('hold_request.success','ahr','A hold is succefully placed');
3182 INSERT INTO action_trigger.hook (key,core_type,description) VALUES ('hold_request.failure','ahr','A hold is attempted by not succefully placed');
3183 INSERT INTO action_trigger.hook (key,core_type,description) VALUES ('hold.capture','ahr','A targeted Item is captured for a hold');
3184 INSERT INTO action_trigger.hook (key,core_type,description) VALUES ('hold.available','ahr','A held item is ready for pickup');
3185 INSERT INTO action_trigger.hook (key,core_type,description) VALUES ('hold_transit.start','ahtc','A hold-captured Item is placed into transit');
3186 INSERT INTO action_trigger.hook (key,core_type,description) VALUES ('hold_transit.finish','ahtc','A hold-captured Item is received from a transit');
3187 INSERT INTO action_trigger.hook (key,core_type,description,passive) VALUES ('checkout.due','circ','Checked out Item is Due',TRUE);
3188 INSERT INTO action_trigger.hook (key,core_type,description,passive) VALUES ('penalty.PATRON_EXCEEDS_FINES','ausp','Patron has exceeded allowed fines',TRUE);
3189 INSERT INTO action_trigger.hook (key,core_type,description,passive) VALUES ('penalty.PATRON_EXCEEDS_OVERDUE_COUNT','ausp','Patron has exceeded allowed overdue count',TRUE);
3190 INSERT INTO action_trigger.hook (key,core_type,description,passive) VALUES ('penalty.PATRON_EXCEEDS_CHECKOUT_COUNT','ausp','Patron has exceeded allowed checkout count',TRUE);
3191 INSERT INTO action_trigger.hook (key,core_type,description,passive) VALUES ('penalty.PATRON_EXCEEDS_COLLECTIONS_WARNING','ausp','Patron has exceeded maximum fine amount for collections department warning',TRUE);
3192 INSERT INTO action_trigger.hook (key,core_type,description,passive) VALUES ('format.po.jedi','acqpo','Formats a Purchase Order as a JEDI document',TRUE);
3193 INSERT INTO action_trigger.hook (key,core_type,description,passive) VALUES ('format.po.html','acqpo','Formats a Purchase Order as an HTML document',TRUE);
3194 INSERT INTO action_trigger.hook (key,core_type,description,passive) VALUES ('format.po.pdf','acqpo','Formats a Purchase Order as a PDF document',TRUE);
3195 INSERT INTO action_trigger.hook (key,core_type,description) VALUES ('damaged','acp','Item marked damaged');
3196 INSERT INTO action_trigger.hook (key,core_type,description) VALUES ('checkout.damaged','circ','A circulating item is marked damaged and the patron is fined');
3197 -- and much more, I'm sure
3199 -- Specialized collection modules. Given an FM object, gather some info and return a scalar or ref.
3200 CREATE TABLE action_trigger.collector (
3201 module TEXT PRIMARY KEY, -- All live under the OpenILS::Trigger::Collector:: namespace
3204 INSERT INTO action_trigger.collector (module,description) VALUES ('fourty_two','Returns the answer to life, the universe and everything');
3205 --INSERT INTO action_trigger.collector (module,description) VALUES ('CircCountsByCircMod','Count of Circulations for a User, broken down by circulation modifier');
3207 -- Simple tests on an FM object from hook.core_type to test for "should we still do this."
3208 CREATE TABLE action_trigger.validator (
3209 module TEXT PRIMARY KEY, -- All live under the OpenILS::Trigger::Validator:: namespace
3212 INSERT INTO action_trigger.validator (module,description) VALUES ('fourty_two','Returns the answer to life, the universe and everything');
3213 INSERT INTO action_trigger.validator (module,description) VALUES ('NOOP_True','Always returns true -- validation always passes');
3214 INSERT INTO action_trigger.validator (module,description) VALUES ('NOOP_False','Always returns false -- validation always fails');
3215 INSERT INTO action_trigger.validator (module,description) VALUES ('CircIsOpen','Check that the circulation is still open');
3216 INSERT INTO action_trigger.validator (module,description) VALUES ('HoldIsAvailable','Check that an item is on the hold shelf');
3217 INSERT INTO action_trigger.validator (module,description) VALUES ('CircIsOverdue','Check that the circulation is overdue');
3219 -- After an event passes validation (action_trigger.validator), the reactor processes it.
3220 CREATE TABLE action_trigger.reactor (
3221 module TEXT PRIMARY KEY, -- All live under the OpenILS::Trigger::Reactor:: namespace
3224 INSERT INTO action_trigger.reactor (module,description) VALUES ('fourty_two','Returns the answer to life, the universe and everything');
3225 INSERT INTO action_trigger.reactor (module,description) VALUES ('NOOP_True','Always returns true -- reaction always passes');
3226 INSERT INTO action_trigger.reactor (module,description) VALUES ('NOOP_False','Always returns false -- reaction always fails');
3227 INSERT INTO action_trigger.reactor (module,description) VALUES ('SendEmail','Send an email based on a user-defined template');
3228 INSERT INTO action_trigger.reactor (module,description) VALUES ('MarkItemLost','Marks a circulation and associated item as lost');
3229 INSERT INTO action_trigger.reactor (module,description) VALUES ('ApplyCircFee','Applies a billing with a pre-defined amount to a circulation');
3230 INSERT INTO action_trigger.reactor (module,description) VALUES ('ProcessTemplate', 'Processes the configured template');
3232 -- After an event is reacted to (either succes or failure) a cleanup module is run against the resulting environment
3233 CREATE TABLE action_trigger.cleanup (
3234 module TEXT PRIMARY KEY, -- All live under the OpenILS::Trigger::Cleanup:: namespace
3237 INSERT INTO action_trigger.cleanup (module,description) VALUES ('fourty_two','Returns the answer to life, the universe and everything');
3238 INSERT INTO action_trigger.cleanup (module,description) VALUES ('NOOP_True','Always returns true -- cleanup always passes');
3239 INSERT INTO action_trigger.cleanup (module,description) VALUES ('NOOP_False','Always returns false -- cleanup always fails');
3240 INSERT INTO action_trigger.cleanup (module,description) VALUES ('ClearAllPending','Remove all future, pending notifications for this target');
3242 CREATE TABLE action_trigger.event_definition (
3243 id SERIAL PRIMARY KEY,
3244 active BOOL NOT NULL DEFAULT TRUE,
3245 owner INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
3247 hook TEXT NOT NULL REFERENCES action_trigger.hook (key) DEFERRABLE INITIALLY DEFERRED,
3248 validator TEXT NOT NULL REFERENCES action_trigger.validator (module) DEFERRABLE INITIALLY DEFERRED,
3249 reactor TEXT NOT NULL REFERENCES action_trigger.reactor (module) DEFERRABLE INITIALLY DEFERRED,
3250 cleanup_success TEXT REFERENCES action_trigger.cleanup (module) DEFERRABLE INITIALLY DEFERRED,
3251 cleanup_failure TEXT REFERENCES action_trigger.cleanup (module) DEFERRABLE INITIALLY DEFERRED,
3252 delay INTERVAL NOT NULL DEFAULT '5 minutes',
3253 delay_field TEXT, -- for instance, xact_start on a circ hook ... look for fields on hook.core_type where datatype=timestamp? If not set, delay from now()
3254 group_field TEXT, -- field from this.hook.core_type to batch event targets together on, fed into reactor a group at a time.
3255 template TEXT, -- the TT block. will have an 'environment' hash (or array of hashes, grouped events) built up by validator and collector(s), which can be modified.
3256 CONSTRAINT ev_def_owner_hook_val_react_clean_delay_once UNIQUE (owner, hook, validator, reactor, delay, delay_field),
3257 CONSTRAINT ev_def_name_owner_once UNIQUE (owner, name)
3260 CREATE TABLE action_trigger.environment (
3261 id SERIAL PRIMARY KEY,
3262 event_def INT NOT NULL REFERENCES action_trigger.event_definition (id) DEFERRABLE INITIALLY DEFERRED,
3263 path TEXT, -- fields to flesh. given a hook with a core_type of circ, imagine circ_lib.parent_ou expanding to
3264 -- {flesh: 2, flesh_fields: {circ: ['circ_lib'], aou: ['parent_ou']}} ... default is to flesh all
3266 collector TEXT REFERENCES action_trigger.collector (module) DEFERRABLE INITIALLY DEFERRED, -- if set, given the object at 'path', return some data
3267 -- to be stashed at environment.<label>
3268 label TEXT CHECK (label NOT IN ('result','target','event')),
3269 CONSTRAINT env_event_label_once UNIQUE (event_def,label)
3272 CREATE TABLE action_trigger.event_output (
3273 id BIGSERIAL PRIMARY KEY,
3274 create_time TIMESTAMPTZ NOT NULL DEFAULT NOW(),
3275 is_error BOOLEAN NOT NULL DEFAULT FALSE,
3279 CREATE TABLE action_trigger.event (
3280 id BIGSERIAL PRIMARY KEY,
3281 target BIGINT NOT NULL, -- points at the id from class defined by event_def.hook.core_type
3282 event_def INT REFERENCES action_trigger.event_definition (id) DEFERRABLE INITIALLY DEFERRED,
3283 add_time TIMESTAMPTZ NOT NULL DEFAULT NOW(),
3284 run_time TIMESTAMPTZ NOT NULL,
3285 start_time TIMESTAMPTZ,
3286 update_time TIMESTAMPTZ,
3287 complete_time TIMESTAMPTZ,
3289 state TEXT NOT NULL DEFAULT 'pending' CHECK (state IN ('pending','invalid','found','collecting','collected','validating','valid','reacting','reacted','cleaning','complete','error')),
3290 template_output BIGINT REFERENCES action_trigger.event_output (id),
3291 error_output BIGINT REFERENCES action_trigger.event_output (id)
3294 CREATE TABLE action_trigger.event_params (
3295 id BIGSERIAL PRIMARY KEY,
3296 event_def INT NOT NULL REFERENCES action_trigger.event_definition (id) DEFERRABLE INITIALLY DEFERRED,
3297 param TEXT NOT NULL, -- the key under environment.event.params to store the output of ...
3298 value TEXT NOT NULL, -- ... the eval() output of this. Has access to environment (and, well, all of perl)
3299 CONSTRAINT event_params_event_def_param_once UNIQUE (event_def,param)
3304 -- Trigger Event Definitions -------------------------------------------------
3306 -- Sample Overdue Notice --
3308 INSERT INTO action_trigger.event_definition (id, active, owner, name, hook, validator, reactor, delay, delay_field, group_field, template)
3309 VALUES (1, 'f', 1, '7 Day Overdue Email Notification', 'checkout.due', 'CircIsOverdue', 'SendEmail', '7 days', 'due_date', 'usr',
3312 [%- user = target.0.usr -%]
3313 To: [%- params.recipient_email || user.email %]
3314 From: [%- params.sender_email || default_sender %]
3315 Subject: Overdue Notification
3317 Dear [% user.family_name %], [% user.first_given_name %]
3318 Our records indicate the following items are overdue.
3320 [% FOR circ IN target %]
3321 Title: [% circ.target_copy.call_number.record.simple_record.title %]
3322 Barcode: [% circ.target_copy.barcode %]
3323 Due: [% date.format(helpers.format_date(circ.due_date), '%Y-%m-%d') %]
3324 Item Cost: [% helpers.get_copy_price(circ.target_copy) %]
3325 Total Owed For Transaction: [% circ.billable_transaction.summary.total_owed %]
3326 Library: [% circ.circ_lib.name %]
3331 INSERT INTO action_trigger.environment (event_def, path) VALUES
3332 (1, 'target_copy.call_number.record.simple_record'),
3334 (1, 'billable_transaction.summary'),
3335 (1, 'circ_lib.billing_address');
3338 -- Sample Mark Long-Overdue Item Lost --
3340 INSERT INTO action_trigger.event_definition (id, active, owner, name, hook, validator, reactor, delay, delay_field)
3341 VALUES (2, 'f', 1, '90 Day Overdue Mark Lost', 'checkout.due', 'CircIsOverdue', 'MarkItemLost', '90 days', 'due_date');
3343 -- Sample Auto Mark Lost Notice --
3345 INSERT INTO action_trigger.event_definition (id, active, owner, name, hook, validator, reactor, group_field, template)
3346 VALUES (3, 'f', 1, '90 Day Overdue Mark Lost Notice', 'lost.auto', 'NOOP_True', 'SendEmail', 'usr',
3349 [%- user = target.0.usr -%]
3350 To: [%- params.recipient_email || user.email %]
3351 From: [%- params.sender_email || default_sender %]
3352 Subject: Overdue Items Marked Lost
3354 Dear [% user.family_name %], [% user.first_given_name %]
3355 The following items are 90 days overdue and have been marked LOST.
3357 [% FOR circ IN target %]
3358 Title: [% circ.target_copy.call_number.record.simple_record.title %]
3359 Barcode: [% circ.target_copy.barcode %]
3360 Due: [% date.format(helpers.format_date(circ.due_date), '%Y-%m-%d') %]
3361 Item Cost: [% helpers.get_copy_price(circ.target_copy) %]
3362 Total Owed For Transaction: [% circ.billable_transaction.summary.total_owed %]
3363 Library: [% circ.circ_lib.name %]
3369 INSERT INTO action_trigger.environment (event_def, path) VALUES
3370 (3, 'target_copy.call_number.record.simple_record'),
3372 (3, 'billable_transaction.summary'),
3373 (3, 'circ_lib.billing_address');
3375 -- Sample Purchase Order HTML Template --
3377 INSERT INTO action_trigger.event_definition (id, active, owner, name, hook, validator, reactor, template)
3378 VALUES (4, 't', 1, 'PO HTML', 'format.po.html', 'NOOP_True', 'ProcessTemplate',
3382 # find a lineitem attribute by name and optional type
3384 FOR attr IN li.attributes;
3385 IF attr.attr_name == attr_name;
3386 IF !attr_type OR attr_type == attr.attr_type;
3395 <h2>Purchase Order [% target.id %]</h2>
3397 date <b>[% date.format(date.now, '%Y%m%d') %]</b>
3401 table td { padding:5px; border:1px solid #aaa;}
3402 table { width:95%; border-collapse:collapse; }
3404 <table id='vendor-table'>
3406 <td valign='top'>Vendor</td>
3408 <div>[% target.provider.name %]</div>
3409 <div>[% target.provider.addresses.0.street1 %]</div>
3410 <div>[% target.provider.addresses.0.street2 %]</div>
3411 <div>[% target.provider.addresses.0.city %]</div>
3412 <div>[% target.provider.addresses.0.state %]</div>
3413 <div>[% target.provider.addresses.0.country %]</div>
3414 <div>[% target.provider.addresses.0.post_code %]</div>
3416 <td valign='top'>Ship to / Bill to</td>
3418 <div>[% target.ordering_agency.name %]</div>
3419 <div>[% target.ordering_agency.billing_address.street1 %]</div>
3420 <div>[% target.ordering_agency.billing_address.street2 %]</div>
3421 <div>[% target.ordering_agency.billing_address.city %]</div>
3422 <div>[% target.ordering_agency.billing_address.state %]</div>
3423 <div>[% target.ordering_agency.billing_address.country %]</div>
3424 <div>[% target.ordering_agency.billing_address.post_code %]</div>
3435 <th>ISBN or Item #</th>
3445 [% FOR li IN target.lineitems %]
3448 [% count = li.lineitem_details.size %]
3449 [% price = PROCESS get_li_attr attr_name = 'estimated_price' %]
3450 [% litotal = (price * count) %]
3451 [% subtotal = subtotal + litotal %]
3452 [% isbn = PROCESS get_li_attr attr_name = 'isbn' %]
3453 [% ident = PROCESS get_li_attr attr_name = 'identifier' %]
3455 <td>[% target.id %]</td>
3456 <td>[% isbn || ident %]</td>
3457 <td>[% PROCESS get_li_attr attr_name = 'title' %]</td>
3458 <td>[% count %]</td>
3459 <td>[% price %]</td>
3460 <td>[% litotal %]</td>
3464 <td/><td/><td/><td/>
3466 <td>[% subtotal %]</td>
3473 Total Line Item Count: [% target.lineitems.size %]
3476 INSERT INTO action_trigger.environment (event_def, path) VALUES
3477 (4, 'lineitems.lineitem_details.fund'),
3478 (4, 'lineitems.lineitem_details.location'),
3479 (4, 'lineitems.lineitem_details.owning_lib'),
3480 (4, 'ordering_agency.mailing_address'),
3481 (4, 'ordering_agency.billing_address'),
3482 (4, 'provider.addresses'),
3483 (4, 'lineitems.attributes');
3485 INSERT INTO action_trigger.event_definition (id, active, owner, name, hook, validator, reactor, delay, delay_field, group_field, template)
3486 VALUES (5, 'f', 1, 'Hold Ready for Pickup Email Notification', 'hold.available', 'HoldIsAvailable', 'SendEmail', '30 minutes', 'capture_time', 'usr',
3489 [%- user = target.0.usr -%]
3490 To: [%- params.recipient_email || user.email %]
3491 From: [%- params.sender_email || default_sender %]
3492 Subject: Hold Available Notification
3494 Dear [% user.family_name %], [% user.first_given_name %]
3495 The item(s) you requested are available for pickup from the Library.
3497 [% FOR hold IN target %]
3498 Title: [% hold.current_copy.call_number.record.simple_record.title %]
3499 Author: [% hold.current_copy.call_number.record.simple_record.author %]
3500 Call Number: [% hold.current_copy.call_number.label %]
3501 Barcode: [% hold.current_copy.barcode %]
3502 Library: [% hold.pickup_lib.name %]
3507 INSERT INTO action_trigger.environment (event_def, path) VALUES
3508 (5, 'current_copy.call_number.record.simple_record'),
3510 (5, 'pickup_lib.billing_address');
3513 SELECT SETVAL('action_trigger.event_definition_id_seq'::TEXT, 100);
3516 CREATE OR REPLACE FUNCTION asset.merge_record_assets( target_record BIGINT, source_record BIGINT ) RETURNS INT AS $func$
3518 moved_objects INT := 0;
3519 source_cn asset.call_number%ROWTYPE;
3520 target_cn asset.call_number%ROWTYPE;
3521 metarec metabib.metarecord%ROWTYPE;
3522 hold action.hold_request%ROWTYPE;
3523 ser_rec serial.record_entry%ROWTYPE;
3527 uri_text TEXT := '';
3530 -- move any 856 entries on records that have at least one MARC-mapped URI entry
3531 SELECT INTO uri_count COUNT(*)
3532 FROM asset.uri_call_number_map m
3533 JOIN asset.call_number cn ON (m.call_number = cn.id)
3534 WHERE cn.record = source_record;
3536 IF uri_count > 0 THEN
3538 SELECT COUNT(*) INTO counter
3545 ) as t(i int,c text);
3547 FOR i IN 1 .. counter LOOP
3548 SELECT '<datafield xmlns="http://www.loc.gov/MARC21/slim" tag="856">' ||
3551 '<subfield code="' || subfield || '">' ||
3554 regexp_replace(data,'&','&','g'),
3560 ) || '</datafield>' INTO uri_datafield
3564 'biblio.record_entry',
3565 '//*[@tag="856"][position()=' || i || ']/*/@code|' ||
3566 '//*[@tag="856"][position()=' || i || ']/*[@code]',
3567 'id=' || source_record
3568 ) as t(id int,subfield text,data text);
3570 uri_text := uri_text || uri_datafield;
3573 IF uri_text <> '' THEN
3574 UPDATE biblio.record_entry
3575 SET marc = regexp_replace(marc,'(</[^>]*record>)', uri_text || E'\\1')
3576 WHERE id = target_record;
3581 -- Find and move metarecords to the target record
3582 SELECT INTO metarec *
3583 FROM metabib.metarecord
3584 WHERE master_record = source_record;
3587 UPDATE metabib.metarecord
3588 SET master_record = target_record,
3590 WHERE id = metarec.id;
3592 moved_objects := moved_objects + 1;
3595 -- Find call numbers attached to the source ...
3596 FOR source_cn IN SELECT * FROM asset.call_number WHERE record = source_record LOOP
3598 SELECT INTO target_cn *
3599 FROM asset.call_number
3600 WHERE label = source_cn.label
3601 AND owning_lib = source_cn.owning_lib
3602 AND record = target_record;
3604 -- ... and if there's a conflicting one on the target ...
3607 -- ... move the copies to that, and ...
3609 SET call_number = target_cn.id
3610 WHERE call_number = source_cn.id;
3612 -- ... move V holds to the move-target call number
3613 FOR hold IN SELECT * FROM action.hold_request WHERE target = source_cn.id AND hold_type = 'V' LOOP
3615 UPDATE action.hold_request
3616 SET target = target_cn.id
3619 moved_objects := moved_objects + 1;
3624 -- ... just move the call number to the target record
3625 UPDATE asset.call_number
3626 SET record = target_record
3627 WHERE id = source_cn.id;
3630 moved_objects := moved_objects + 1;
3633 -- Find T holds targeting the source record ...
3634 FOR hold IN SELECT * FROM action.hold_request WHERE target = source_record AND hold_type = 'T' LOOP
3636 -- ... and move them to the target record
3637 UPDATE action.hold_request
3638 SET target = target_record
3641 moved_objects := moved_objects + 1;
3644 -- Find serial records targeting the source record ...
3645 FOR ser_rec IN SELECT * FROM serial.record_entry WHERE record = source_record LOOP
3646 -- ... and move them to the target record
3647 UPDATE serial.record_entry
3648 SET record = target_record
3649 WHERE id = ser_rec.id;
3651 moved_objects := moved_objects + 1;
3654 -- Finally, "delete" the source record
3655 DELETE FROM biblio.record_entry WHERE id = source_record;
3657 -- That's all, folks!
3658 RETURN moved_objects;
3660 $func$ LANGUAGE plpgsql;
3663 CREATE OR REPLACE FUNCTION actor.usr_merge_rows( table_name TEXT, col_name TEXT, src_usr INT, dest_usr INT ) RETURNS VOID AS $$
3670 sel := 'SELECT id::BIGINT FROM ' || table_name || ' WHERE ' || quote_ident(col_name) || ' = ' || quote_literal(src_usr);
3671 upd := 'UPDATE ' || table_name || ' SET ' || quote_ident(col_name) || ' = ' || quote_literal(dest_usr) || ' WHERE id = ';
3672 del := 'DELETE FROM ' || table_name || ' WHERE id = ';
3673 FOR cur_row IN EXECUTE sel LOOP
3675 --RAISE NOTICE 'Attempting to merge % %', table_name, cur_row.id;
3676 EXECUTE upd || cur_row.id;
3677 EXCEPTION WHEN unique_violation THEN
3678 --RAISE NOTICE 'Deleting conflicting % %', table_name, cur_row.id;
3679 EXECUTE del || cur_row.id;
3683 $$ LANGUAGE plpgsql;
3685 COMMENT ON FUNCTION actor.usr_merge_rows(TEXT, TEXT, INT, INT) IS $$
3687 * Attempts to move each row of the specified table from src_user to dest_user.
3688 * Where conflicts exist, the conflicting "source" row is deleted.
3693 CREATE OR REPLACE FUNCTION actor.usr_merge( src_usr INT, dest_usr INT, del_addrs BOOLEAN, del_cards BOOLEAN, deactivate_cards BOOLEAN ) RETURNS VOID AS $$
3696 -- do some initial cleanup
3697 UPDATE actor.usr SET card = NULL WHERE id = src_usr;
3698 UPDATE actor.usr SET mailing_address = NULL WHERE id = src_usr;
3699 UPDATE actor.usr SET billing_address = NULL WHERE id = src_usr;
3703 DELETE FROM actor.card where usr = src_usr;
3705 IF deactivate_cards THEN
3706 UPDATE actor.card SET active = 'f' WHERE usr = src_usr;
3708 UPDATE actor.card SET usr = dest_usr WHERE usr = src_usr;
3713 DELETE FROM actor.usr_address WHERE usr = src_usr;
3715 UPDATE actor.usr_address SET usr = dest_usr WHERE usr = src_usr;
3718 UPDATE actor.usr_note SET usr = dest_usr WHERE usr = src_usr;
3719 -- dupes are technically OK in actor.usr_standing_penalty, should manually delete them...
3720 UPDATE actor.usr_standing_penalty SET usr = dest_usr WHERE usr = src_usr;
3721 PERFORM actor.usr_merge_rows('actor.usr_org_unit_opt_in', 'usr', src_usr, dest_usr);
3722 PERFORM actor.usr_merge_rows('actor.usr_setting', 'usr', src_usr, dest_usr);
3725 PERFORM actor.usr_merge_rows('permission.usr_perm_map', 'usr', src_usr, dest_usr);
3726 PERFORM actor.usr_merge_rows('permission.usr_object_perm_map', 'usr', src_usr, dest_usr);
3727 PERFORM actor.usr_merge_rows('permission.usr_grp_map', 'usr', src_usr, dest_usr);
3728 PERFORM actor.usr_merge_rows('permission.usr_work_ou_map', 'usr', src_usr, dest_usr);
3732 PERFORM actor.usr_merge_rows('container.biblio_record_entry_bucket', 'owner', src_usr, dest_usr);
3733 PERFORM actor.usr_merge_rows('container.call_number_bucket', 'owner', src_usr, dest_usr);
3734 PERFORM actor.usr_merge_rows('container.copy_bucket', 'owner', src_usr, dest_usr);
3735 PERFORM actor.usr_merge_rows('container.user_bucket', 'owner', src_usr, dest_usr);
3736 PERFORM actor.usr_merge_rows('container.user_bucket_item', 'target_user', src_usr, dest_usr);
3739 PERFORM actor.usr_merge_rows('vandelay.queue', 'owner', src_usr, dest_usr);
3742 PERFORM actor.usr_merge_rows('money.collections_tracker', 'usr', src_usr, dest_usr);
3743 PERFORM actor.usr_merge_rows('money.collections_tracker', 'collector', src_usr, dest_usr);
3744 UPDATE money.billable_xact SET usr = dest_usr WHERE usr = src_usr;
3745 UPDATE money.billing SET voider = dest_usr WHERE voider = src_usr;
3746 UPDATE money.bnm_payment SET accepting_usr = dest_usr WHERE accepting_usr = src_usr;
3749 UPDATE action.circulation SET usr = dest_usr WHERE usr = src_usr;
3750 UPDATE action.circulation SET circ_staff = dest_usr WHERE circ_staff = src_usr;
3751 UPDATE action.circulation SET checkin_staff = dest_usr WHERE checkin_staff = src_usr;
3753 UPDATE action.hold_request SET usr = dest_usr WHERE usr = src_usr;
3754 UPDATE action.hold_request SET fulfillment_staff = dest_usr WHERE fulfillment_staff = src_usr;
3755 UPDATE action.hold_request SET requestor = dest_usr WHERE requestor = src_usr;
3756 UPDATE action.hold_notification SET notify_staff = dest_usr WHERE notify_staff = src_usr;
3758 UPDATE action.in_house_use SET staff = dest_usr WHERE staff = src_usr;
3759 UPDATE action.non_cataloged_circulation SET staff = dest_usr WHERE staff = src_usr;
3760 UPDATE action.non_cataloged_circulation SET patron = dest_usr WHERE patron = src_usr;
3761 UPDATE action.non_cat_in_house_use SET staff = dest_usr WHERE staff = src_usr;
3762 UPDATE action.survey_response SET usr = dest_usr WHERE usr = src_usr;
3765 UPDATE acq.fund_allocation SET allocator = dest_usr WHERE allocator = src_usr;
3766 PERFORM actor.usr_merge_rows('acq.picklist', 'owner', src_usr, dest_usr);
3767 UPDATE acq.purchase_order SET owner = dest_usr WHERE owner = src_usr;
3768 UPDATE acq.po_note SET creator = dest_usr WHERE creator = src_usr;
3769 UPDATE acq.po_note SET editor = dest_usr WHERE editor = src_usr;
3770 UPDATE acq.lineitem_note SET creator = dest_usr WHERE creator = src_usr;
3771 UPDATE acq.lineitem_note SET editor = dest_usr WHERE editor = src_usr;
3772 UPDATE acq.lineitem_usr_attr_definition SET usr = dest_usr WHERE usr = src_usr;
3775 UPDATE asset.copy SET creator = dest_usr WHERE creator = src_usr;
3776 UPDATE asset.copy SET editor = dest_usr WHERE editor = src_usr;
3777 UPDATE asset.copy_note SET creator = dest_usr WHERE creator = src_usr;
3778 UPDATE asset.call_number SET creator = dest_usr WHERE creator = src_usr;
3779 UPDATE asset.call_number SET editor = dest_usr WHERE editor = src_usr;
3780 UPDATE asset.call_number_note SET creator = dest_usr WHERE creator = src_usr;
3783 UPDATE serial.record_entry SET creator = dest_usr WHERE creator = src_usr;
3784 UPDATE serial.record_entry SET editor = dest_usr WHERE editor = src_usr;
3787 -- It's not uncommon to define the reporter schema in a replica
3788 -- DB only, so don't assume these tables exist in the write DB.
3790 PERFORM actor.usr_merge_rows('reporter.template', 'owner', src_usr, dest_usr);
3791 EXCEPTION WHEN undefined_table THEN
3795 PERFORM actor.usr_merge_rows('reporter.report', 'owner', src_usr, dest_usr);
3796 EXCEPTION WHEN undefined_table THEN
3800 PERFORM actor.usr_merge_rows('reporter.schedule', 'runner', src_usr, dest_usr);
3801 EXCEPTION WHEN undefined_table THEN
3805 PERFORM actor.usr_merge_rows('reporter.template_folder', 'owner', src_usr, dest_usr);
3806 EXCEPTION WHEN undefined_table THEN
3810 PERFORM actor.usr_merge_rows('reporter.report_folder', 'owner', src_usr, dest_usr);
3811 EXCEPTION WHEN undefined_table THEN
3815 PERFORM actor.usr_merge_rows('reporter.output_folder', 'owner', src_usr, dest_usr);
3816 EXCEPTION WHEN undefined_table THEN
3820 -- Finally, delete the source user
3821 DELETE FROM actor.usr WHERE id = src_usr;
3824 $$ LANGUAGE plpgsql;
3826 COMMENT ON FUNCTION actor.usr_merge(INT, INT, BOOLEAN, BOOLEAN, BOOLEAN) IS $$
3828 * Merges all user date from src_usr to dest_usr. When collisions occur,
3829 * keep dest_usr's data and delete src_usr's data.
3835 CREATE OR REPLACE FUNCTION actor.approve_pending_address(pending_id INT) RETURNS BIGINT AS $$
3839 SELECT INTO old_id replaces FROM actor.usr_address where id = pending_id;
3840 IF old_id IS NULL THEN
3841 UPDATE actor.usr_address SET pending = 'f' WHERE id = pending_id;
3844 -- address replaces an existing address
3845 DELETE FROM actor.usr_address WHERE id = -old_id;
3846 UPDATE actor.usr_address SET id = -id WHERE id = old_id;
3847 UPDATE actor.usr_address SET replaces = NULL, id = old_id, pending = 'f' WHERE id = pending_id;
3850 $$ LANGUAGE plpgsql;
3852 COMMENT ON FUNCTION actor.approve_pending_address(INT) IS $$
3854 * Replaces an address with a pending address. This is done by giving the pending
3855 * address the ID of the old address. The replaced address is retained with -id.
3862 ---------!!!!!!!!!!!!!!!!!!!!!!---------------
3863 -- Must go after COMMIT!!
3864 ---------!!!!!!!!!!!!!!!!!!!!!!---------------
3866 INSERT INTO config.z3950_source (name, label, host, port, db, auth)
3867 VALUES ('biblios', oils_i18n_gettext('biblios','biblios.net', 'czs', 'label'), 'z3950.biblios.net', 210, 'bibliographic', FALSE);
3870 INSERT INTO config.z3950_attr (id, source, name, label, code, format)
3871 VALUES (19, 'biblios','tcn', oils_i18n_gettext(19, 'Title Control Number', 'cza', 'label'), 12, 1);
3872 INSERT INTO config.z3950_attr (id, source, name, label, code, format)
3873 VALUES (20, 'biblios', 'isbn', oils_i18n_gettext(20, 'ISBN', 'cza', 'label'), 7, 6);
3874 INSERT INTO config.z3950_attr (id, source, name, label, code, format)
3875 VALUES (21, 'biblios', 'lccn', oils_i18n_gettext(21, 'LCCN', 'cza', 'label'), 9, 1);
3876 INSERT INTO config.z3950_attr (id, source, name, label, code, format)
3877 VALUES (22, 'biblios', 'author', oils_i18n_gettext(22, 'Author', 'cza', 'label'), 1003, 6);
3878 INSERT INTO config.z3950_attr (id, source, name, label, code, format)
3879 VALUES (23, 'biblios', 'title', oils_i18n_gettext(23, 'Title', 'cza', 'label'), 4, 6);
3880 INSERT INTO config.z3950_attr (id, source, name, label, code, format)
3881 VALUES (24, 'biblios', 'issn', oils_i18n_gettext(24, 'ISSN', 'cza', 'label'), 8, 1);
3882 INSERT INTO config.z3950_attr (id, source, name, label, code, format)
3883 VALUES (25, 'biblios', 'publisher', oils_i18n_gettext(25, 'Publisher', 'cza', 'label'), 1018, 6);
3884 INSERT INTO config.z3950_attr (id, source, name, label, code, format)
3885 VALUES (26, 'biblios', 'pubdate', oils_i18n_gettext(26, 'Publication Date', 'cza', 'label'), 31, 1);
3886 INSERT INTO config.z3950_attr (id, source, name, label, code, format)
3887 VALUES (27, 'biblios', 'item_type', oils_i18n_gettext(27, 'Item Type', 'cza', 'label'), 1001, 1);
3889 INSERT INTO permission.perm_list (code, description) VALUES ('DELETE_FUNDING_SOURCE', 'Allow a user to delete a funding source');
3890 INSERT INTO permission.perm_list (code, description) VALUES ('VIEW_FUNDING_SOURCE', 'Allow a user to view a funding source');
3891 INSERT INTO permission.perm_list (code, description) VALUES ('UPDATE_FUNDING_SOURCE', 'Allow a user to update a funding source');
3892 INSERT INTO permission.perm_list (code, description) VALUES ('CREATE_FUND', 'Allow a user to create a new fund');
3893 INSERT INTO permission.perm_list (code, description) VALUES ('DELETE_FUND', 'Allow a user to delete a fund');
3894 INSERT INTO permission.perm_list (code, description) VALUES ('VIEW_FUND', 'Allow a user to view a fund');
3895 INSERT INTO permission.perm_list (code, description) VALUES ('UPDATE_FUND', 'Allow a user to update a fund');
3896 INSERT INTO permission.perm_list (code, description) VALUES ('CREATE_FUND_ALLOCATION', 'Allow a user to create a new fund allocation');
3897 INSERT INTO permission.perm_list (code, description) VALUES ('DELETE_FUND_ALLOCATION', 'Allow a user to delete a fund allocation');
3898 INSERT INTO permission.perm_list (code, description) VALUES ('VIEW_FUND_ALLOCATION', 'Allow a user to view a fund allocation');
3899 INSERT INTO permission.perm_list (code, description) VALUES ('UPDATE_FUND_ALLOCATION', 'Allow a user to update a fund allocation');
3900 INSERT INTO permission.perm_list (code, description) VALUES ('GENERAL_ACQ', 'Lowest level permission required to access the ACQ interface');
3901 INSERT INTO permission.perm_list (code, description) VALUES ('CREATE_PROVIDER', 'Allow a user to create a new provider');
3902 INSERT INTO permission.perm_list (code, description) VALUES ('DELETE_PROVIDER', 'Allow a user to delate a provider');
3903 INSERT INTO permission.perm_list (code, description) VALUES ('VIEW_PROVIDER', 'Allow a user to view a provider');
3904 INSERT INTO permission.perm_list (code, description) VALUES ('UPDATE_PROVIDER', 'Allow a user to update a provider');
3905 INSERT INTO permission.perm_list (code, description) VALUES ('ADMIN_FUNDING_SOURCE', 'Allow a user to create/view/update/delete a funding source');
3906 INSERT INTO permission.perm_list (code, description) VALUES ('ADMIN_ACQ_FUND', 'Allow a user to create/view/update/delete a fund');
3907 INSERT INTO permission.perm_list (code, description) VALUES ('ADMIN_FUND', 'Allow a user to create/view/update/delete a fund');
3908 INSERT INTO permission.perm_list (code, description) VALUES ('MANAGE_FUNDING_SOURCE', 'Allow a user to view/credit/debit a funding source');
3909 INSERT INTO permission.perm_list (code, description) VALUES ('MANAGE_FUND', 'Allow a user to view/credit/debit a fund');
3910 INSERT INTO permission.perm_list (code, description) VALUES ('CREATE_PICKLIST', 'Allows a user to create a picklist');
3911 INSERT INTO permission.perm_list (code, description) VALUES ('ADMIN_PROVIDER', 'Allow a user to create/view/update/delete a provider');
3912 INSERT INTO permission.perm_list (code, description) VALUES ('MANAGE_PROVIDER', 'Allow a user to view and purchase from a provider');
3913 INSERT INTO permission.perm_list (code, description) VALUES ('VIEW_PICKLIST', 'Allow a user to view another users picklist');
3914 INSERT INTO permission.perm_list (code, description) VALUES ('DELETE_RECORD', 'Allow a staff member to directly remove a bibliographic record');
3915 INSERT INTO permission.perm_list (code, description) VALUES ('ADMIN_CURRENCY_TYPE', 'Allow a user to create/view/update/delete a currency_type');
3916 INSERT INTO permission.perm_list (code, description) VALUES ('MARK_BAD_DEBT', 'Allow a user to mark a transaction as bad (unrecoverable) debt');
3917 INSERT INTO permission.perm_list (code, description) VALUES ('VIEW_BILLING_TYPE', 'Allow a user to view billing types');
3918 INSERT INTO permission.perm_list (code, description) VALUES ('MARK_ITEM_AVAILABLE', 'Allow a user to mark an item status as ''available''');
3919 INSERT INTO permission.perm_list (code, description) VALUES ('MARK_ITEM_CHECKED_OUT', 'Allow a user to mark an item status as ''checked out''');
3920 INSERT INTO permission.perm_list (code, description) VALUES ('MARK_ITEM_BINDERY', 'Allow a user to mark an item status as ''bindery''');
3921 INSERT INTO permission.perm_list (code, description) VALUES ('MARK_ITEM_LOST', 'Allow a user to mark an item status as ''lost''');
3922 INSERT INTO permission.perm_list (code, description) VALUES ('MARK_ITEM_MISSING', 'Allow a user to mark an item status as ''missing''');
3923 INSERT INTO permission.perm_list (code, description) VALUES ('MARK_ITEM_IN_PROCESS', 'Allow a user to mark an item status as ''in process''');
3924 INSERT INTO permission.perm_list (code, description) VALUES ('MARK_ITEM_IN_TRANSIT', 'Allow a user to mark an item status as ''in transit''');
3925 INSERT INTO permission.perm_list (code, description) VALUES ('MARK_ITEM_RESHELVING', 'Allow a user to mark an item status as ''reshelving''');
3926 INSERT INTO permission.perm_list (code, description) VALUES ('MARK_ITEM_ON_HOLDS_SHELF', 'Allow a user to mark an item status as ''on holds shelf''');
3927 INSERT INTO permission.perm_list (code, description) VALUES ('MARK_ITEM_ON_ORDER', 'Allow a user to mark an item status as ''on order''');
3928 INSERT INTO permission.perm_list (code, description) VALUES ('MARK_ITEM_ILL', 'Allow a user to mark an item status as ''inter-library loan''');
3929 INSERT INTO permission.perm_list (code, description) VALUES ('group_application.user.staff.acq', 'Allows a user to add/remove/edit users in the "ACQ" group');
3930 INSERT INTO permission.perm_list (code, description) VALUES ('group_application.user.staff.acq_admin', 'Allows a user to add/remove/edit users in the "Acquisitions Administrator" group');
3931 INSERT INTO permission.perm_list (code, description) VALUES ('CREATE_PURCHASE_ORDER', 'Allows a user to create a purchase order');
3932 INSERT INTO permission.perm_list (code, description) VALUES ('VIEW_PURCHASE_ORDER', 'Allows a user to view a purchase order');
3933 INSERT INTO permission.perm_list (code, description) VALUES ('IMPORT_ACQ_LINEITEM_BIB_RECORD', 'Allows a user to import a bib record from the acq staging area (on-order record) into the ILS bib data set');
3934 INSERT INTO permission.perm_list (code, description) VALUES ('RECEIVE_PURCHASE_ORDER', 'Allows a user to mark a purchase order, lineitem, or individual copy as received');
3935 INSERT INTO permission.perm_list (code, description) VALUES ('VIEW_ORG_SETTINGS','Allows a user to view all org settings at the specified level');
3936 INSERT INTO permission.perm_list (code, description) VALUES ('CREATE_MFHD_RECORD', 'Allows a user to create a new MFHD record');
3937 INSERT INTO permission.perm_list (code, description) VALUES ('UPDATE_MFHD_RECORD', 'Allows a user to update an MFHD record');
3938 INSERT INTO permission.perm_list (code, description) VALUES ('DELETE_MFHD_RECORD', 'Allows a user to delete an MFHD record');
3939 INSERT INTO permission.perm_list (code, description) VALUES ('CREATE_FUNDING_SOURCE', 'Allow a user to create a new funding source');
3941 INSERT INTO permission.perm_list (code) VALUES ('CREATE_ACQ_FUNDING_SOURCE');
3942 INSERT INTO permission.perm_list (code) VALUES ('DELETE_ACQ_FUNDING_SOURCE');
3943 INSERT INTO permission.perm_list (code) VALUES ('UPDATE_ACQ_FUNDING_SOURCE');
3944 INSERT INTO permission.perm_list (code) VALUES ('VIEW_ACQ_FUNDING_SOURCE');
3945 INSERT INTO permission.perm_list (code) VALUES ('UPDATE_ORG_UNIT_SETTING.global.password_regex');
3946 INSERT INTO permission.perm_list (code) VALUES ('UPDATE_ORG_UNIT_SETTING.global.juvenile_age_threshold');
3947 INSERT INTO permission.perm_list (code) VALUES ('UPDATE_ORG_UNIT_SETTING.patron.password.use_phone');
3949 INSERT INTO permission.grp_tree (name, parent, description, perm_interval, usergroup, application_perm) VALUES ('Acquisitions', 3, NULL, '3 years', TRUE, 'group_application.user.staff.acq');
3950 INSERT INTO permission.grp_tree (name, parent, description, perm_interval, usergroup, application_perm) VALUES ('Acquisitions Administrators', (SELECT id FROM permission.grp_tree WHERE name = 'Acquisitions'), NULL, '3 years', TRUE, 'group_application.user.staff.acq_admin');
3952 -- You can't log into the staff client without this
3953 INSERT INTO permission.grp_perm_map (grp, perm, depth, grantable) VALUES (3, (SELECT id FROM permission.perm_list WHERE code = 'VIEW_ORG_SETTINGS'), 1, false);
3954 -- MFHD permissions are necessary for serials work; add to the default catalogers group
3955 INSERT INTO permission.grp_perm_map (grp, perm, depth, grantable) VALUES (5, (SELECT id FROM permission.perm_list WHERE code = 'CREATE_MFHD_RECORD'), 1, false);
3956 INSERT INTO permission.grp_perm_map (grp, perm, depth, grantable) VALUES (5, (SELECT id FROM permission.perm_list WHERE code = 'DELETE_MFHD_RECORD'), 1, false);
3957 INSERT INTO permission.grp_perm_map (grp, perm, depth, grantable) VALUES (5, (SELECT id FROM permission.perm_list WHERE code = 'UPDATE_MFHD_RECORD'), 1, false);
3959 -- Add basic acquisitions permissions to the Acquisitions group
3960 INSERT INTO permission.grp_perm_map (grp, perm, depth, grantable) VALUES ((SELECT id FROM permission.grp_tree WHERE name = 'Acquisitions'), (SELECT id FROM permission.perm_list WHERE code = 'GENERAL_ACQ'), 1, false);
3961 INSERT INTO permission.grp_perm_map (grp, perm, depth, grantable) VALUES ((SELECT id FROM permission.grp_tree WHERE name = 'Acquisitions'), (SELECT id FROM permission.perm_list WHERE code = 'VIEW_PICKLIST'), 1, false);
3962 INSERT INTO permission.grp_perm_map (grp, perm, depth, grantable) VALUES ((SELECT id FROM permission.grp_tree WHERE name = 'Acquisitions'), (SELECT id FROM permission.perm_list WHERE code = 'CREATE_PICKLIST'), 1, false);
3963 INSERT INTO permission.grp_perm_map (grp, perm, depth, grantable) VALUES ((SELECT id FROM permission.grp_tree WHERE name = 'Acquisitions'), (SELECT id FROM permission.perm_list WHERE code = 'CREATE_PURCHASE_ORDER'), 1, false);
3964 INSERT INTO permission.grp_perm_map (grp, perm, depth, grantable) VALUES ((SELECT id FROM permission.grp_tree WHERE name = 'Acquisitions'), (SELECT id FROM permission.perm_list WHERE code = 'VIEW_PURCHASE_ORDER'), 1, false);
3965 INSERT INTO permission.grp_perm_map (grp, perm, depth, grantable) VALUES ((SELECT id FROM permission.grp_tree WHERE name = 'Acquisitions'), (SELECT id FROM permission.perm_list WHERE code = 'RECEIVE_PURCHASE_ORDER'), 1, false);
3966 INSERT INTO permission.grp_perm_map (grp, perm, depth, grantable) VALUES ((SELECT id FROM permission.grp_tree WHERE name = 'Acquisitions'), (SELECT id FROM permission.perm_list WHERE code = 'VIEW_PROVIDER'), 1, false);
3967 INSERT INTO permission.grp_perm_map (grp, perm, depth, grantable) VALUES ((SELECT id FROM permission.grp_tree WHERE name = 'Acquisitions'), (SELECT id FROM permission.perm_list WHERE code = 'UPDATE_COPY'), 1, false);
3968 INSERT INTO permission.grp_perm_map (grp, perm, depth, grantable) VALUES ((SELECT id FROM permission.grp_tree WHERE name = 'Acquisitions'), (SELECT id FROM permission.perm_list WHERE code = 'UPDATE_VOLUME'), 1, false);
3970 -- Add acquisitions administration permissions to the Acquisitions group
3971 INSERT INTO permission.grp_perm_map (grp, perm, depth, grantable) VALUES ((SELECT id FROM permission.grp_tree WHERE name = 'Acquisitions Administrators'), (SELECT id FROM permission.perm_list WHERE code = 'ADMIN_PROVIDER'), 1, false);
3972 INSERT INTO permission.grp_perm_map (grp, perm, depth, grantable) VALUES ((SELECT id FROM permission.grp_tree WHERE name = 'Acquisitions Administrators'), (SELECT id FROM permission.perm_list WHERE code = 'ADMIN_FUNDING_SOURCE'), 1, false);
3973 INSERT INTO permission.grp_perm_map (grp, perm, depth, grantable) VALUES ((SELECT id FROM permission.grp_tree WHERE name = 'Acquisitions Administrators'), (SELECT id FROM permission.perm_list WHERE code = 'ADMIN_ACQ_FUND'), 1, false);
3974 INSERT INTO permission.grp_perm_map (grp, perm, depth, grantable) VALUES ((SELECT id FROM permission.grp_tree WHERE name = 'Acquisitions Administrators'), (SELECT id FROM permission.perm_list WHERE code = 'ADMIN_FUND'), 1, false);
3975 INSERT INTO permission.grp_perm_map (grp, perm, depth, grantable) VALUES ((SELECT id FROM permission.grp_tree WHERE name = 'Acquisitions Administrators'), (SELECT id FROM permission.perm_list WHERE code = 'ADMIN_CURRENCY_TYPE'), 1, false);
3977 SELECT SETVAL('permission.perm_list_id_seq'::TEXT, (SELECT MAX(id) FROM permission.perm_list));
3979 ALTER TABLE auditor.action_hold_request_history ADD COLUMN cancel_cause INT;
3980 ALTER TABLE auditor.action_hold_request_history ADD COLUMN cancel_note TEXT;
3982 CREATE OR REPLACE FUNCTION tmp_populate_p_b_bt () RETURNS BOOL AS $$
3987 SELECT DISTINCT xact
3993 INSERT INTO money.materialized_payment_by_billing_type (
3994 xact, payment, billing, payment_ts, billing_ts,
3995 payment_type, billing_type, amount, billing_ou, payment_ou
3996 ) SELECT xact, payment, billing, payment_ts, billing_ts,
3997 payment_type, billing_type, amount, billing_ou, payment_ou
3998 FROM money.payment_by_billing_type( p.xact );
4004 $$ LANGUAGE PLPGSQL;
4006 SELECT tmp_populate_p_b_bt();
4008 DROP FUNCTION tmp_populate_p_b_bt ();
4011 UPDATE config.xml_transform SET xslt=$$<xsl:stylesheet xmlns="http://www.loc.gov/mods/v3" xmlns:marc="http://www.loc.gov/MARC21/slim"
4012 xmlns:xlink="http://www.w3.org/1999/xlink" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
4013 exclude-result-prefixes="xlink marc" version="1.0">
4014 <xsl:output encoding="UTF-8" indent="yes" method="xml"/>
4016 <xsl:variable name="ascii">
4017 <xsl:text> !"#$%&'()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^_`abcdefghijklmnopqrstuvwxyz{|}~</xsl:text>
4020 <xsl:variable name="latin1">
4021 <xsl:text> ¡¢£¤¥¦§¨©ª«¬®¯°±²³´µ¶·¸¹º»¼½¾¿ÀÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖ×ØÙÚÛÜÝÞßàáâãäåæçèéêëìíîïðñòóôõö÷øùúûüýþÿ</xsl:text>
4023 <!-- Characters that usually don't need to be escaped -->
4024 <xsl:variable name="safe">
4025 <xsl:text>!'()*-.0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ_abcdefghijklmnopqrstuvwxyz~</xsl:text>
4028 <xsl:variable name="hex">0123456789ABCDEF</xsl:variable>
4032 <!--MARC21slim2MODS3-3.xsl
4033 Revision 1.27 - Mapped 648 to <subject> 2009/03/13 tmee
4034 Revision 1.26 - Added subfield $s mapping for 130/240/730 2008/10/16 tmee
4035 Revision 1.25 - Mapped 040e to <descriptiveStandard> and Leader/18 to <descriptive standard>aacr2 2008/09/18 tmee
4036 Revision 1.24 - Mapped 852 subfields $h, $i, $j, $k, $l, $m, $t to <shelfLocation> and 852 subfield $u to <physicalLocation> with @xlink 2008/09/17 tmee
4037 Revision 1.23 - Commented out xlink/uri for subfield 0 for 130/240/730, 100/700, 110/710, 111/711 as these are currently unactionable 2008/09/17 tmee
4038 Revision 1.22 - Mapped 022 subfield $l to type "issn-l" subfield $m to output identifier element with corresponding @type and @invalid eq 'yes'2008/09/17 tmee
4039 Revision 1.21 - Mapped 856 ind2=1 or ind2=2 to <relatedItem><location><url> 2008/07/03 tmee
4040 Revision 1.20 - Added genre w/@auth="contents of 2" and type= "musical composition" 2008/07/01 tmee
4041 Revision 1.19 - Added genre offprint for 008/24+ BK code 2 2008/07/01 tmee
4042 Revision 1.18 - Added xlink/uri for subfield 0 for 130/240/730, 100/700, 110/710, 111/711 2008/06/26 tmee
4043 Revision 1.17 - Added mapping of 662 2008/05/14 tmee
4044 Revision 1.16 - Changed @authority from "marc" to "marcgt" for 007 and 008 codes mapped to a term in <genre> 2007/07/10 tmee
4045 Revision 1.15 - For field 630, moved call to part template outside title element 2007/07/10 tmee
4046 Revision 1.14 - Fixed template isValid and fields 010, 020, 022, 024, 028, and 037 to output additional identifier elements with corresponding @type and @invalid eq 'yes' when subfields z or y (in the case of 022) exist in the MARCXML ::: 2007/01/04 17:35:20 cred
4047 Revision 1.13 - Changed order of output under cartographics to reflect schema 2006/11/28 tmee
4048 Revision 1.12 - Updated to reflect MODS 3.2 Mapping 2006/10/11 tmee
4049 Revision 1.11 - The attribute objectPart moved from <languageTerm> to <language> 2006/04/08 jrad
4050 Revision 1.10 - MODS 3.1 revisions to language and classification elements (plus ability to find marc:collection embedded in wrapper elements such as SRU zs: wrappers) 2006/02/06 ggar
4051 Revision 1.9 - Subfield $y was added to field 242 2004/09/02 10:57 jrad
4052 Revision 1.8 - Subject chopPunctuation expanded and attribute fixes 2004/08/12 jrad
4053 Revision 1.7 - 2004/03/25 08:29 jrad
4054 Revision 1.6 - Various validation fixes 2004/02/20 ntra
4055 Revision 1.5 - MODS2 to MODS3 updates, language unstacking and de-duping, chopPunctuation expanded 2003/10/02 16:18:58 ntra
4056 Revision 1.3 - Additional Changes not related to MODS Version 2.0 by ntra
4057 Revision 1.2 - Added Log Comment 2003/03/24 19:37:42 ckeith
4059 <xsl:template match="/">
4061 <xsl:when test="//marc:collection">
4062 <modsCollection xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
4063 xsi:schemaLocation="http://www.loc.gov/mods/v3 http://www.loc.gov/standards/mods/v3/mods-3-2.xsd">
4064 <xsl:for-each select="//marc:collection/marc:record">
4065 <mods version="3.3">
4066 <xsl:call-template name="marcRecord"/>
4072 <mods xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" version="3.3"
4073 xsi:schemaLocation="http://www.loc.gov/mods/v3 http://www.loc.gov/standards/mods/v3/mods-3-2.xsd">
4074 <xsl:for-each select="//marc:record">
4075 <xsl:call-template name="marcRecord"/>
4081 <xsl:template name="marcRecord">
4082 <xsl:variable name="leader" select="marc:leader"/>
4083 <xsl:variable name="leader6" select="substring($leader,7,1)"/>
4084 <xsl:variable name="leader7" select="substring($leader,8,1)"/>
4085 <xsl:variable name="controlField008" select="marc:controlfield[@tag='008']"/>
4086 <xsl:variable name="typeOf008">
4088 <xsl:when test="$leader6='a'">
4091 test="$leader7='a' or $leader7='c' or $leader7='d' or $leader7='m'">BK</xsl:when>
4092 <xsl:when test="$leader7='b' or $leader7='i' or $leader7='s'">SE</xsl:when>
4095 <xsl:when test="$leader6='t'">BK</xsl:when>
4096 <xsl:when test="$leader6='p'">MM</xsl:when>
4097 <xsl:when test="$leader6='m'">CF</xsl:when>
4098 <xsl:when test="$leader6='e' or $leader6='f'">MP</xsl:when>
4099 <xsl:when test="$leader6='g' or $leader6='k' or $leader6='o' or $leader6='r'">VM</xsl:when>
4100 <xsl:when test="$leader6='c' or $leader6='d' or $leader6='i' or $leader6='j'"
4104 <xsl:for-each select="marc:datafield[@tag='245']">
4106 <xsl:variable name="title">
4108 <xsl:when test="marc:subfield[@code='b']">
4109 <xsl:call-template name="specialSubfieldSelect">
4110 <xsl:with-param name="axis">b</xsl:with-param>
4111 <xsl:with-param name="beforeCodes">afgk</xsl:with-param>
4112 </xsl:call-template>
4115 <xsl:call-template name="subfieldSelect">
4116 <xsl:with-param name="codes">abfgk</xsl:with-param>
4117 </xsl:call-template>
4121 <xsl:variable name="titleChop">
4122 <xsl:call-template name="chopPunctuation">
4123 <xsl:with-param name="chopString">
4124 <xsl:value-of select="$title"/>
4126 </xsl:call-template>
4129 <xsl:when test="@ind2>0">
4131 <xsl:value-of select="substring($titleChop,1,@ind2)"/>
4134 <xsl:value-of select="substring($titleChop,@ind2+1)"/>
4139 <xsl:value-of select="$titleChop"/>
4143 <xsl:if test="marc:subfield[@code='b']">
4145 <xsl:call-template name="chopPunctuation">
4146 <xsl:with-param name="chopString">
4147 <xsl:call-template name="specialSubfieldSelect">
4148 <xsl:with-param name="axis">b</xsl:with-param>
4149 <xsl:with-param name="anyCodes">b</xsl:with-param>
4150 <xsl:with-param name="afterCodes">afgk</xsl:with-param>
4151 </xsl:call-template>
4153 </xsl:call-template>
4156 <xsl:call-template name="part"/>
4159 <xsl:for-each select="marc:datafield[@tag='210']">
4160 <titleInfo type="abbreviated">
4162 <xsl:call-template name="chopPunctuation">
4163 <xsl:with-param name="chopString">
4164 <xsl:call-template name="subfieldSelect">
4165 <xsl:with-param name="codes">a</xsl:with-param>
4166 </xsl:call-template>
4168 </xsl:call-template>
4170 <xsl:call-template name="subtitle"/>
4173 <xsl:for-each select="marc:datafield[@tag='242']">
4174 <titleInfo type="translated">
4175 <!--09/01/04 Added subfield $y-->
4176 <xsl:for-each select="marc:subfield[@code='y']">
4177 <xsl:attribute name="lang">
4178 <xsl:value-of select="text()"/>
4181 <xsl:for-each select="marc:subfield[@code='i']">
4182 <xsl:attribute name="displayLabel">
4183 <xsl:value-of select="text()"/>
4187 <xsl:call-template name="chopPunctuation">
4188 <xsl:with-param name="chopString">
4189 <xsl:call-template name="subfieldSelect">
4190 <!-- 1/04 removed $h, b -->
4191 <xsl:with-param name="codes">a</xsl:with-param>
4192 </xsl:call-template>
4194 </xsl:call-template>
4197 <xsl:call-template name="subtitle"/>
4198 <xsl:call-template name="part"/>
4201 <xsl:for-each select="marc:datafield[@tag='246']">
4202 <titleInfo type="alternative">
4203 <xsl:for-each select="marc:subfield[@code='i']">
4204 <xsl:attribute name="displayLabel">
4205 <xsl:value-of select="text()"/>
4209 <xsl:call-template name="chopPunctuation">
4210 <xsl:with-param name="chopString">
4211 <xsl:call-template name="subfieldSelect">
4212 <!-- 1/04 removed $h, $b -->
4213 <xsl:with-param name="codes">af</xsl:with-param>
4214 </xsl:call-template>
4216 </xsl:call-template>
4218 <xsl:call-template name="subtitle"/>
4219 <xsl:call-template name="part"/>
4223 select="marc:datafield[@tag='130']|marc:datafield[@tag='240']|marc:datafield[@tag='730'][@ind2!='2']">
4224 <titleInfo type="uniform">
4226 <!-- deleted uri for subfield 0
4227 <xsl:call-template name="uri"/>
4230 <xsl:variable name="str">
4231 <xsl:for-each select="marc:subfield">
4233 test="(contains('adfklmors',@code) and (not(../marc:subfield[@code='n' or @code='p']) or (following-sibling::marc:subfield[@code='n' or @code='p'])))">
4234 <xsl:value-of select="text()"/>
4235 <xsl:text> </xsl:text>
4239 <xsl:call-template name="chopPunctuation">
4240 <xsl:with-param name="chopString">
4241 <xsl:value-of select="substring($str,1,string-length($str)-1)"/>
4243 </xsl:call-template>
4245 <xsl:call-template name="part"/>
4248 <xsl:for-each select="marc:datafield[@tag='740'][@ind2!='2']">
4249 <titleInfo type="alternative">
4251 <xsl:call-template name="chopPunctuation">
4252 <xsl:with-param name="chopString">
4253 <xsl:call-template name="subfieldSelect">
4254 <xsl:with-param name="codes">ah</xsl:with-param>
4255 </xsl:call-template>
4257 </xsl:call-template>
4259 <xsl:call-template name="part"/>
4262 <xsl:for-each select="marc:datafield[@tag='100']">
4263 <name type="personal">
4265 <!-- deleted uri for subfield 0
4266 <xsl:call-template name="uri"/>
4269 <xsl:call-template name="nameABCDQ"/>
4270 <xsl:call-template name="affiliation"/>
4272 <roleTerm authority="marcrelator" type="text">creator</roleTerm>
4274 <xsl:call-template name="role"/>
4277 <xsl:for-each select="marc:datafield[@tag='110']">
4278 <name type="corporate">
4280 <!-- deleted uri for subfield 0
4281 <xsl:call-template name="uri"/>
4284 <xsl:call-template name="nameABCDN"/>
4286 <roleTerm authority="marcrelator" type="text">creator</roleTerm>
4288 <xsl:call-template name="role"/>
4291 <xsl:for-each select="marc:datafield[@tag='111']">
4292 <name type="conference">
4294 <!-- deleted uri for subfield 0
4295 <xsl:call-template name="uri"/>
4298 <xsl:call-template name="nameACDEQ"/>
4300 <roleTerm authority="marcrelator" type="text">creator</roleTerm>
4302 <xsl:call-template name="role"/>
4305 <xsl:for-each select="marc:datafield[@tag='700'][not(marc:subfield[@code='t'])]">
4306 <name type="personal">
4308 <!-- deleted uri for subfield 0
4309 <xsl:call-template name="uri"/>
4312 <xsl:call-template name="nameABCDQ"/>
4313 <xsl:call-template name="affiliation"/>
4314 <xsl:call-template name="role"/>
4317 <xsl:for-each select="marc:datafield[@tag='710'][not(marc:subfield[@code='t'])]">
4318 <name type="corporate">
4320 <!-- deleted uri for subfield 0
4321 <xsl:call-template name="uri"/>
4324 <xsl:call-template name="nameABCDN"/>
4325 <xsl:call-template name="role"/>
4328 <xsl:for-each select="marc:datafield[@tag='711'][not(marc:subfield[@code='t'])]">
4329 <name type="conference">
4331 <!-- deleted uri for subfield 0
4332 <xsl:call-template name="uri"/>
4335 <xsl:call-template name="nameACDEQ"/>
4336 <xsl:call-template name="role"/>
4339 <xsl:for-each select="marc:datafield[@tag='720'][not(marc:subfield[@code='t'])]">
4341 <xsl:if test="@ind1=1">
4342 <xsl:attribute name="type">
4343 <xsl:text>personal</xsl:text>
4347 <xsl:value-of select="marc:subfield[@code='a']"/>
4349 <xsl:call-template name="role"/>
4353 <xsl:if test="$leader7='c'">
4354 <xsl:attribute name="collection">yes</xsl:attribute>
4356 <xsl:if test="$leader6='d' or $leader6='f' or $leader6='p' or $leader6='t'">
4357 <xsl:attribute name="manuscript">yes</xsl:attribute>
4360 <xsl:when test="$leader6='a' or $leader6='t'">text</xsl:when>
4361 <xsl:when test="$leader6='e' or $leader6='f'">cartographic</xsl:when>
4362 <xsl:when test="$leader6='c' or $leader6='d'">notated music</xsl:when>
4363 <xsl:when test="$leader6='i'">sound recording-nonmusical</xsl:when>
4364 <xsl:when test="$leader6='j'">sound recording-musical</xsl:when>
4365 <xsl:when test="$leader6='k'">still image</xsl:when>
4366 <xsl:when test="$leader6='g'">moving image</xsl:when>
4367 <xsl:when test="$leader6='r'">three dimensional object</xsl:when>
4368 <xsl:when test="$leader6='m'">software, multimedia</xsl:when>
4369 <xsl:when test="$leader6='p'">mixed material</xsl:when>
4372 <xsl:if test="substring($controlField008,26,1)='d'">
4373 <genre authority="marcgt">globe</genre>
4376 test="marc:controlfield[@tag='007'][substring(text(),1,1)='a'][substring(text(),2,1)='r']">
4377 <genre authority="marcgt">remote-sensing image</genre>
4379 <xsl:if test="$typeOf008='MP'">
4380 <xsl:variable name="controlField008-25" select="substring($controlField008,26,1)"/>
4383 test="$controlField008-25='a' or $controlField008-25='b' or $controlField008-25='c' or marc:controlfield[@tag=007][substring(text(),1,1)='a'][substring(text(),2,1)='j']">
4384 <genre authority="marcgt">map</genre>
4387 test="$controlField008-25='e' or marc:controlfield[@tag=007][substring(text(),1,1)='a'][substring(text(),2,1)='d']">
4388 <genre authority="marcgt">atlas</genre>
4392 <xsl:if test="$typeOf008='SE'">
4393 <xsl:variable name="controlField008-21" select="substring($controlField008,22,1)"/>
4395 <xsl:when test="$controlField008-21='d'">
4396 <genre authority="marcgt">database</genre>
4398 <xsl:when test="$controlField008-21='l'">
4399 <genre authority="marcgt">loose-leaf</genre>
4401 <xsl:when test="$controlField008-21='m'">
4402 <genre authority="marcgt">series</genre>
4404 <xsl:when test="$controlField008-21='n'">
4405 <genre authority="marcgt">newspaper</genre>
4407 <xsl:when test="$controlField008-21='p'">
4408 <genre authority="marcgt">periodical</genre>
4410 <xsl:when test="$controlField008-21='w'">
4411 <genre authority="marcgt">web site</genre>
4415 <xsl:if test="$typeOf008='BK' or $typeOf008='SE'">
4416 <xsl:variable name="controlField008-24" select="substring($controlField008,25,4)"/>
4418 <xsl:when test="contains($controlField008-24,'a')">
4419 <genre authority="marcgt">abstract or summary</genre>
4421 <xsl:when test="contains($controlField008-24,'b')">
4422 <genre authority="marcgt">bibliography</genre>
4424 <xsl:when test="contains($controlField008-24,'c')">
4425 <genre authority="marcgt">catalog</genre>
4427 <xsl:when test="contains($controlField008-24,'d')">
4428 <genre authority="marcgt">dictionary</genre>
4430 <xsl:when test="contains($controlField008-24,'e')">
4431 <genre authority="marcgt">encyclopedia</genre>
4433 <xsl:when test="contains($controlField008-24,'f')">
4434 <genre authority="marcgt">handbook</genre>
4436 <xsl:when test="contains($controlField008-24,'g')">
4437 <genre authority="marcgt">legal article</genre>
4439 <xsl:when test="contains($controlField008-24,'i')">
4440 <genre authority="marcgt">index</genre>
4442 <xsl:when test="contains($controlField008-24,'k')">
4443 <genre authority="marcgt">discography</genre>
4445 <xsl:when test="contains($controlField008-24,'l')">
4446 <genre authority="marcgt">legislation</genre>
4448 <xsl:when test="contains($controlField008-24,'m')">
4449 <genre authority="marcgt">theses</genre>
4451 <xsl:when test="contains($controlField008-24,'n')">
4452 <genre authority="marcgt">survey of literature</genre>
4454 <xsl:when test="contains($controlField008-24,'o')">
4455 <genre authority="marcgt">review</genre>
4457 <xsl:when test="contains($controlField008-24,'p')">
4458 <genre authority="marcgt">programmed text</genre>
4460 <xsl:when test="contains($controlField008-24,'q')">
4461 <genre authority="marcgt">filmography</genre>
4463 <xsl:when test="contains($controlField008-24,'r')">
4464 <genre authority="marcgt">directory</genre>
4466 <xsl:when test="contains($controlField008-24,'s')">
4467 <genre authority="marcgt">statistics</genre>
4469 <xsl:when test="contains($controlField008-24,'t')">
4470 <genre authority="marcgt">technical report</genre>
4472 <xsl:when test="contains($controlField008-24,'v')">
4473 <genre authority="marcgt">legal case and case notes</genre>
4475 <xsl:when test="contains($controlField008-24,'w')">
4476 <genre authority="marcgt">law report or digest</genre>
4478 <xsl:when test="contains($controlField008-24,'z')">
4479 <genre authority="marcgt">treaty</genre>
4482 <xsl:variable name="controlField008-29" select="substring($controlField008,30,1)"/>
4484 <xsl:when test="$controlField008-29='1'">
4485 <genre authority="marcgt">conference publication</genre>
4489 <xsl:if test="$typeOf008='CF'">
4490 <xsl:variable name="controlField008-26" select="substring($controlField008,27,1)"/>
4492 <xsl:when test="$controlField008-26='a'">
4493 <genre authority="marcgt">numeric data</genre>
4495 <xsl:when test="$controlField008-26='e'">
4496 <genre authority="marcgt">database</genre>
4498 <xsl:when test="$controlField008-26='f'">
4499 <genre authority="marcgt">font</genre>
4501 <xsl:when test="$controlField008-26='g'">
4502 <genre authority="marcgt">game</genre>
4506 <xsl:if test="$typeOf008='BK'">
4507 <xsl:if test="substring($controlField008,25,1)='j'">
4508 <genre authority="marcgt">patent</genre>
4510 <xsl:if test="substring($controlField008,25,1)='2'">
4511 <genre authority="marcgt">offprint</genre>
4513 <xsl:if test="substring($controlField008,31,1)='1'">
4514 <genre authority="marcgt">festschrift</genre>
4516 <xsl:variable name="controlField008-34" select="substring($controlField008,35,1)"/>
4518 test="$controlField008-34='a' or $controlField008-34='b' or $controlField008-34='c' or $controlField008-34='d'">
4519 <genre authority="marcgt">biography</genre>
4521 <xsl:variable name="controlField008-33" select="substring($controlField008,34,1)"/>
4523 <xsl:when test="$controlField008-33='e'">
4524 <genre authority="marcgt">essay</genre>
4526 <xsl:when test="$controlField008-33='d'">
4527 <genre authority="marcgt">drama</genre>
4529 <xsl:when test="$controlField008-33='c'">
4530 <genre authority="marcgt">comic strip</genre>
4532 <xsl:when test="$controlField008-33='l'">
4533 <genre authority="marcgt">fiction</genre>
4535 <xsl:when test="$controlField008-33='h'">
4536 <genre authority="marcgt">humor, satire</genre>
4538 <xsl:when test="$controlField008-33='i'">
4539 <genre authority="marcgt">letter</genre>
4541 <xsl:when test="$controlField008-33='f'">
4542 <genre authority="marcgt">novel</genre>
4544 <xsl:when test="$controlField008-33='j'">
4545 <genre authority="marcgt">short story</genre>
4547 <xsl:when test="$controlField008-33='s'">
4548 <genre authority="marcgt">speech</genre>
4552 <xsl:if test="$typeOf008='MU'">
4553 <xsl:variable name="controlField008-30-31" select="substring($controlField008,31,2)"/>
4554 <xsl:if test="contains($controlField008-30-31,'b')">
4555 <genre authority="marcgt">biography</genre>
4557 <xsl:if test="contains($controlField008-30-31,'c')">
4558 <genre authority="marcgt">conference publication</genre>
4560 <xsl:if test="contains($controlField008-30-31,'d')">
4561 <genre authority="marcgt">drama</genre>
4563 <xsl:if test="contains($controlField008-30-31,'e')">
4564 <genre authority="marcgt">essay</genre>
4566 <xsl:if test="contains($controlField008-30-31,'f')">
4567 <genre authority="marcgt">fiction</genre>
4569 <xsl:if test="contains($controlField008-30-31,'o')">
4570 <genre authority="marcgt">folktale</genre>
4572 <xsl:if test="contains($controlField008-30-31,'h')">
4573 <genre authority="marcgt">history</genre>
4575 <xsl:if test="contains($controlField008-30-31,'k')">
4576 <genre authority="marcgt">humor, satire</genre>
4578 <xsl:if test="contains($controlField008-30-31,'m')">
4579 <genre authority="marcgt">memoir</genre>
4581 <xsl:if test="contains($controlField008-30-31,'p')">
4582 <genre authority="marcgt">poetry</genre>
4584 <xsl:if test="contains($controlField008-30-31,'r')">
4585 <genre authority="marcgt">rehearsal</genre>
4587 <xsl:if test="contains($controlField008-30-31,'g')">
4588 <genre authority="marcgt">reporting</genre>
4590 <xsl:if test="contains($controlField008-30-31,'s')">
4591 <genre authority="marcgt">sound</genre>
4593 <xsl:if test="contains($controlField008-30-31,'l')">
4594 <genre authority="marcgt">speech</genre>
4597 <xsl:if test="$typeOf008='VM'">
4598 <xsl:variable name="controlField008-33" select="substring($controlField008,34,1)"/>
4600 <xsl:when test="$controlField008-33='a'">
4601 <genre authority="marcgt">art original</genre>
4603 <xsl:when test="$controlField008-33='b'">
4604 <genre authority="marcgt">kit</genre>
4606 <xsl:when test="$controlField008-33='c'">
4607 <genre authority="marcgt">art reproduction</genre>
4609 <xsl:when test="$controlField008-33='d'">
4610 <genre authority="marcgt">diorama</genre>
4612 <xsl:when test="$controlField008-33='f'">
4613 <genre authority="marcgt">filmstrip</genre>
4615 <xsl:when test="$controlField008-33='g'">
4616 <genre authority="marcgt">legal article</genre>
4618 <xsl:when test="$controlField008-33='i'">
4619 <genre authority="marcgt">picture</genre>
4621 <xsl:when test="$controlField008-33='k'">
4622 <genre authority="marcgt">graphic</genre>
4624 <xsl:when test="$controlField008-33='l'">
4625 <genre authority="marcgt">technical drawing</genre>
4627 <xsl:when test="$controlField008-33='m'">
4628 <genre authority="marcgt">motion picture</genre>
4630 <xsl:when test="$controlField008-33='n'">
4631 <genre authority="marcgt">chart</genre>
4633 <xsl:when test="$controlField008-33='o'">
4634 <genre authority="marcgt">flash card</genre>
4636 <xsl:when test="$controlField008-33='p'">
4637 <genre authority="marcgt">microscope slide</genre>
4640 test="$controlField008-33='q' or marc:controlfield[@tag=007][substring(text(),1,1)='a'][substring(text(),2,1)='q']">
4641 <genre authority="marcgt">model</genre>
4643 <xsl:when test="$controlField008-33='r'">
4644 <genre authority="marcgt">realia</genre>
4646 <xsl:when test="$controlField008-33='s'">
4647 <genre authority="marcgt">slide</genre>
4649 <xsl:when test="$controlField008-33='t'">
4650 <genre authority="marcgt">transparency</genre>
4652 <xsl:when test="$controlField008-33='v'">
4653 <genre authority="marcgt">videorecording</genre>
4655 <xsl:when test="$controlField008-33='w'">
4656 <genre authority="marcgt">toy</genre>
4661 <!-- 1.20 047 genre tmee-->
4663 <xsl:for-each select="marc:datafield[@tag=047]">
4664 <genre authority="marcgt">
4665 <xsl:attribute name="authority">
4666 <xsl:value-of select="marc:subfield[@code='2']"/>
4668 <xsl:call-template name="subfieldSelect">
4669 <xsl:with-param name="codes">abcdef</xsl:with-param>
4670 <xsl:with-param name="delimeter">-</xsl:with-param>
4671 </xsl:call-template>
4674 <xsl:for-each select="marc:datafield[@tag=655]">
4675 <genre authority="marcgt">
4676 <xsl:attribute name="authority">
4677 <xsl:value-of select="marc:subfield[@code='2']"/>
4679 <xsl:call-template name="subfieldSelect">
4680 <xsl:with-param name="codes">abvxyz</xsl:with-param>
4681 <xsl:with-param name="delimeter">-</xsl:with-param>
4682 </xsl:call-template>
4686 <xsl:variable name="MARCpublicationCode"
4687 select="normalize-space(substring($controlField008,16,3))"/>
4688 <xsl:if test="translate($MARCpublicationCode,'|','')">
4691 <xsl:attribute name="type">code</xsl:attribute>
4692 <xsl:attribute name="authority">marccountry</xsl:attribute>
4693 <xsl:value-of select="$MARCpublicationCode"/>
4697 <xsl:for-each select="marc:datafield[@tag=044]/marc:subfield[@code='c']">
4700 <xsl:attribute name="type">code</xsl:attribute>
4701 <xsl:attribute name="authority">iso3166</xsl:attribute>
4702 <xsl:value-of select="."/>
4706 <xsl:for-each select="marc:datafield[@tag=260]/marc:subfield[@code='a']">
4709 <xsl:attribute name="type">text</xsl:attribute>
4710 <xsl:call-template name="chopPunctuationFront">
4711 <xsl:with-param name="chopString">
4712 <xsl:call-template name="chopPunctuation">
4713 <xsl:with-param name="chopString" select="."/>
4714 </xsl:call-template>
4716 </xsl:call-template>
4720 <xsl:for-each select="marc:datafield[@tag=046]/marc:subfield[@code='m']">
4721 <dateValid point="start">
4722 <xsl:value-of select="."/>
4725 <xsl:for-each select="marc:datafield[@tag=046]/marc:subfield[@code='n']">
4726 <dateValid point="end">
4727 <xsl:value-of select="."/>
4730 <xsl:for-each select="marc:datafield[@tag=046]/marc:subfield[@code='j']">
4732 <xsl:value-of select="."/>
4736 select="marc:datafield[@tag=260]/marc:subfield[@code='b' or @code='c' or @code='g']">
4738 <xsl:when test="@code='b'">
4740 <xsl:call-template name="chopPunctuation">
4741 <xsl:with-param name="chopString" select="."/>
4742 <xsl:with-param name="punctuation">
4743 <xsl:text>:,;/ </xsl:text>
4745 </xsl:call-template>
4748 <xsl:when test="@code='c'">
4750 <xsl:call-template name="chopPunctuation">
4751 <xsl:with-param name="chopString" select="."/>
4752 </xsl:call-template>
4755 <xsl:when test="@code='g'">
4757 <xsl:value-of select="."/>
4762 <xsl:variable name="dataField260c">
4763 <xsl:call-template name="chopPunctuation">
4764 <xsl:with-param name="chopString"
4765 select="marc:datafield[@tag=260]/marc:subfield[@code='c']"/>
4766 </xsl:call-template>
4768 <xsl:variable name="controlField008-7-10"
4769 select="normalize-space(substring($controlField008, 8, 4))"/>
4770 <xsl:variable name="controlField008-11-14"
4771 select="normalize-space(substring($controlField008, 12, 4))"/>
4772 <xsl:variable name="controlField008-6"
4773 select="normalize-space(substring($controlField008, 7, 1))"/>
4775 test="$controlField008-6='e' or $controlField008-6='p' or $controlField008-6='r' or $controlField008-6='t' or $controlField008-6='s'">
4776 <xsl:if test="$controlField008-7-10 and ($controlField008-7-10 != $dataField260c)">
4777 <dateIssued encoding="marc">
4778 <xsl:value-of select="$controlField008-7-10"/>
4783 test="$controlField008-6='c' or $controlField008-6='d' or $controlField008-6='i' or $controlField008-6='k' or $controlField008-6='m' or $controlField008-6='q' or $controlField008-6='u'">
4784 <xsl:if test="$controlField008-7-10">
4785 <dateIssued encoding="marc" point="start">
4786 <xsl:value-of select="$controlField008-7-10"/>
4791 test="$controlField008-6='c' or $controlField008-6='d' or $controlField008-6='i' or $controlField008-6='k' or $controlField008-6='m' or $controlField008-6='q' or $controlField008-6='u'">
4792 <xsl:if test="$controlField008-11-14">
4793 <dateIssued encoding="marc" point="end">
4794 <xsl:value-of select="$controlField008-11-14"/>
4798 <xsl:if test="$controlField008-6='q'">
4799 <xsl:if test="$controlField008-7-10">
4800 <dateIssued encoding="marc" point="start" qualifier="questionable">
4801 <xsl:value-of select="$controlField008-7-10"/>
4805 <xsl:if test="$controlField008-6='q'">
4806 <xsl:if test="$controlField008-11-14">
4807 <dateIssued encoding="marc" point="end" qualifier="questionable">
4808 <xsl:value-of select="$controlField008-11-14"/>
4812 <xsl:if test="$controlField008-6='t'">
4813 <xsl:if test="$controlField008-11-14">
4814 <copyrightDate encoding="marc">
4815 <xsl:value-of select="$controlField008-11-14"/>
4820 select="marc:datafield[@tag=033][@ind1=0 or @ind1=1]/marc:subfield[@code='a']">
4821 <dateCaptured encoding="iso8601">
4822 <xsl:value-of select="."/>
4825 <xsl:for-each select="marc:datafield[@tag=033][@ind1=2]/marc:subfield[@code='a'][1]">
4826 <dateCaptured encoding="iso8601" point="start">
4827 <xsl:value-of select="."/>
4830 <xsl:for-each select="marc:datafield[@tag=033][@ind1=2]/marc:subfield[@code='a'][2]">
4831 <dateCaptured encoding="iso8601" point="end">
4832 <xsl:value-of select="."/>
4835 <xsl:for-each select="marc:datafield[@tag=250]/marc:subfield[@code='a']">
4837 <xsl:value-of select="."/>
4840 <xsl:for-each select="marc:leader">
4844 test="$leader7='a' or $leader7='c' or $leader7='d' or $leader7='m'"
4845 >monographic</xsl:when>
4846 <xsl:when test="$leader7='b' or $leader7='i' or $leader7='s'"
4847 >continuing</xsl:when>
4851 <xsl:for-each select="marc:datafield[@tag=310]|marc:datafield[@tag=321]">
4853 <xsl:call-template name="subfieldSelect">
4854 <xsl:with-param name="codes">ab</xsl:with-param>
4855 </xsl:call-template>
4859 <xsl:variable name="controlField008-35-37"
4860 select="normalize-space(translate(substring($controlField008,36,3),'|#',''))"/>
4861 <xsl:if test="$controlField008-35-37">
4863 <languageTerm authority="iso639-2b" type="code">
4864 <xsl:value-of select="substring($controlField008,36,3)"/>
4868 <xsl:for-each select="marc:datafield[@tag=041]">
4870 select="marc:subfield[@code='a' or @code='b' or @code='d' or @code='e' or @code='f' or @code='g' or @code='h']">
4871 <xsl:variable name="langCodes" select="."/>
4873 <xsl:when test="../marc:subfield[@code='2']='rfc3066'">
4874 <!-- not stacked but could be repeated -->
4875 <xsl:call-template name="rfcLanguages">
4876 <xsl:with-param name="nodeNum">
4877 <xsl:value-of select="1"/>
4879 <xsl:with-param name="usedLanguages">
4882 <xsl:with-param name="controlField008-35-37">
4883 <xsl:value-of select="$controlField008-35-37"/>
4885 </xsl:call-template>
4889 <xsl:variable name="allLanguages">
4890 <xsl:copy-of select="$langCodes"/>
4892 <xsl:variable name="currentLanguage">
4893 <xsl:value-of select="substring($allLanguages,1,3)"/>
4895 <xsl:call-template name="isoLanguage">
4896 <xsl:with-param name="currentLanguage">
4897 <xsl:value-of select="substring($allLanguages,1,3)"/>
4899 <xsl:with-param name="remainingLanguages">
4901 select="substring($allLanguages,4,string-length($allLanguages)-3)"
4904 <xsl:with-param name="usedLanguages">
4905 <xsl:if test="$controlField008-35-37">
4906 <xsl:value-of select="$controlField008-35-37"/>
4909 </xsl:call-template>
4914 <xsl:variable name="physicalDescription">
4915 <!--3.2 change tmee 007/11 -->
4916 <xsl:if test="$typeOf008='CF' and marc:controlfield[@tag=007][substring(.,12,1)='a']">
4917 <digitalOrigin>reformatted digital</digitalOrigin>
4919 <xsl:if test="$typeOf008='CF' and marc:controlfield[@tag=007][substring(.,12,1)='b']">
4920 <digitalOrigin>digitized microfilm</digitalOrigin>
4922 <xsl:if test="$typeOf008='CF' and marc:controlfield[@tag=007][substring(.,12,1)='d']">
4923 <digitalOrigin>digitized other analog</digitalOrigin>
4925 <xsl:variable name="controlField008-23" select="substring($controlField008,24,1)"/>
4926 <xsl:variable name="controlField008-29" select="substring($controlField008,30,1)"/>
4927 <xsl:variable name="check008-23">
4929 test="$typeOf008='BK' or $typeOf008='MU' or $typeOf008='SE' or $typeOf008='MM'">
4930 <xsl:value-of select="true()"/>
4933 <xsl:variable name="check008-29">
4934 <xsl:if test="$typeOf008='MP' or $typeOf008='VM'">
4935 <xsl:value-of select="true()"/>
4940 test="($check008-23 and $controlField008-23='f') or ($check008-29 and $controlField008-29='f')">
4941 <form authority="marcform">braille</form>
4944 test="($controlField008-23=' ' and ($leader6='c' or $leader6='d')) or (($typeOf008='BK' or $typeOf008='SE') and ($controlField008-23=' ' or $controlField008='r'))">
4945 <form authority="marcform">print</form>
4948 test="$leader6 = 'm' or ($check008-23 and $controlField008-23='s') or ($check008-29 and $controlField008-29='s')">
4949 <form authority="marcform">electronic</form>
4952 test="($check008-23 and $controlField008-23='b') or ($check008-29 and $controlField008-29='b')">
4953 <form authority="marcform">microfiche</form>
4956 test="($check008-23 and $controlField008-23='a') or ($check008-29 and $controlField008-29='a')">
4957 <form authority="marcform">microfilm</form>
4961 <xsl:if test="marc:datafield[@tag=130]/marc:subfield[@code='h']">
4962 <form authority="gmd">
4963 <xsl:call-template name="chopBrackets">
4964 <xsl:with-param name="chopString">
4965 <xsl:value-of select="marc:datafield[@tag=130]/marc:subfield[@code='h']"
4968 </xsl:call-template>
4971 <xsl:if test="marc:datafield[@tag=240]/marc:subfield[@code='h']">
4972 <form authority="gmd">
4973 <xsl:call-template name="chopBrackets">
4974 <xsl:with-param name="chopString">
4975 <xsl:value-of select="marc:datafield[@tag=240]/marc:subfield[@code='h']"
4978 </xsl:call-template>
4981 <xsl:if test="marc:datafield[@tag=242]/marc:subfield[@code='h']">
4982 <form authority="gmd">
4983 <xsl:call-template name="chopBrackets">
4984 <xsl:with-param name="chopString">
4985 <xsl:value-of select="marc:datafield[@tag=242]/marc:subfield[@code='h']"
4988 </xsl:call-template>
4991 <xsl:if test="marc:datafield[@tag=245]/marc:subfield[@code='h']">
4992 <form authority="gmd">
4993 <xsl:call-template name="chopBrackets">
4994 <xsl:with-param name="chopString">
4995 <xsl:value-of select="marc:datafield[@tag=245]/marc:subfield[@code='h']"
4998 </xsl:call-template>
5001 <xsl:if test="marc:datafield[@tag=246]/marc:subfield[@code='h']">
5002 <form authority="gmd">
5003 <xsl:call-template name="chopBrackets">
5004 <xsl:with-param name="chopString">
5005 <xsl:value-of select="marc:datafield[@tag=246]/marc:subfield[@code='h']"
5008 </xsl:call-template>
5011 <xsl:if test="marc:datafield[@tag=730]/marc:subfield[@code='h']">
5012 <form authority="gmd">
5013 <xsl:call-template name="chopBrackets">
5014 <xsl:with-param name="chopString">
5015 <xsl:value-of select="marc:datafield[@tag=730]/marc:subfield[@code='h']"
5018 </xsl:call-template>
5021 <xsl:for-each select="marc:datafield[@tag=256]/marc:subfield[@code='a']">
5023 <xsl:value-of select="."/>
5026 <xsl:for-each select="marc:controlfield[@tag=007][substring(text(),1,1)='c']">
5028 <xsl:when test="substring(text(),14,1)='a'">
5029 <reformattingQuality>access</reformattingQuality>
5031 <xsl:when test="substring(text(),14,1)='p'">
5032 <reformattingQuality>preservation</reformattingQuality>
5034 <xsl:when test="substring(text(),14,1)='r'">
5035 <reformattingQuality>replacement</reformattingQuality>
5039 <!--3.2 change tmee 007/01 -->
5041 test="marc:controlfield[@tag=007][substring(text(),1,1)='c'][substring(text(),2,1)='b']">
5042 <form authority="smd">chip cartridge</form>
5045 test="marc:controlfield[@tag=007][substring(text(),1,1)='c'][substring(text(),2,1)='c']">
5046 <form authority="smd">computer optical disc cartridge</form>
5049 test="marc:controlfield[@tag=007][substring(text(),1,1)='c'][substring(text(),2,1)='j']">
5050 <form authority="smd">magnetic disc</form>
5053 test="marc:controlfield[@tag=007][substring(text(),1,1)='c'][substring(text(),2,1)='m']">
5054 <form authority="smd">magneto-optical disc</form>
5057 test="marc:controlfield[@tag=007][substring(text(),1,1)='c'][substring(text(),2,1)='o']">
5058 <form authority="smd">optical disc</form>
5061 test="marc:controlfield[@tag=007][substring(text(),1,1)='c'][substring(text(),2,1)='r']">
5062 <form authority="smd">remote</form>
5065 test="marc:controlfield[@tag=007][substring(text(),1,1)='c'][substring(text(),2,1)='a']">
5066 <form authority="smd">tape cartridge</form>
5069 test="marc:controlfield[@tag=007][substring(text(),1,1)='c'][substring(text(),2,1)='f']">
5070 <form authority="smd">tape cassette</form>
5073 test="marc:controlfield[@tag=007][substring(text(),1,1)='c'][substring(text(),2,1)='h']">
5074 <form authority="smd">tape reel</form>
5078 test="marc:controlfield[@tag=007][substring(text(),1,1)='d'][substring(text(),2,1)='a']">
5079 <form authority="smd">celestial globe</form>
5082 test="marc:controlfield[@tag=007][substring(text(),1,1)='d'][substring(text(),2,1)='e']">
5083 <form authority="smd">earth moon globe</form>
5086 test="marc:controlfield[@tag=007][substring(text(),1,1)='d'][substring(text(),2,1)='b']">
5087 <form authority="smd">planetary or lunar globe</form>
5090 test="marc:controlfield[@tag=007][substring(text(),1,1)='d'][substring(text(),2,1)='c']">
5091 <form authority="smd">terrestrial globe</form>
5095 test="marc:controlfield[@tag=007][substring(text(),1,1)='o'][substring(text(),2,1)='o']">
5096 <form authority="smd">kit</form>
5100 test="marc:controlfield[@tag=007][substring(text(),1,1)='a'][substring(text(),2,1)='d']">
5101 <form authority="smd">atlas</form>
5104 test="marc:controlfield[@tag=007][substring(text(),1,1)='a'][substring(text(),2,1)='g']">
5105 <form authority="smd">diagram</form>
5108 test="marc:controlfield[@tag=007][substring(text(),1,1)='a'][substring(text(),2,1)='j']">
5109 <form authority="smd">map</form>
5112 test="marc:controlfield[@tag=007][substring(text(),1,1)='a'][substring(text(),2,1)='q']">
5113 <form authority="smd">model</form>
5116 test="marc:controlfield[@tag=007][substring(text(),1,1)='a'][substring(text(),2,1)='k']">
5117 <form authority="smd">profile</form>
5120 test="marc:controlfield[@tag=007][substring(text(),1,1)='a'][substring(text(),2,1)='r']">
5121 <form authority="smd">remote-sensing image</form>
5124 test="marc:controlfield[@tag=007][substring(text(),1,1)='a'][substring(text(),2,1)='s']">
5125 <form authority="smd">section</form>
5128 test="marc:controlfield[@tag=007][substring(text(),1,1)='a'][substring(text(),2,1)='y']">
5129 <form authority="smd">view</form>
5133 test="marc:controlfield[@tag=007][substring(text(),1,1)='h'][substring(text(),2,1)='a']">
5134 <form authority="smd">aperture card</form>
5137 test="marc:controlfield[@tag=007][substring(text(),1,1)='h'][substring(text(),2,1)='e']">
5138 <form authority="smd">microfiche</form>
5141 test="marc:controlfield[@tag=007][substring(text(),1,1)='h'][substring(text(),2,1)='f']">
5142 <form authority="smd">microfiche cassette</form>
5145 test="marc:controlfield[@tag=007][substring(text(),1,1)='h'][substring(text(),2,1)='b']">
5146 <form authority="smd">microfilm cartridge</form>
5149 test="marc:controlfield[@tag=007][substring(text(),1,1)='h'][substring(text(),2,1)='c']">
5150 <form authority="smd">microfilm cassette</form>
5153 test="marc:controlfield[@tag=007][substring(text(),1,1)='h'][substring(text(),2,1)='d']">
5154 <form authority="smd">microfilm reel</form>
5157 test="marc:controlfield[@tag=007][substring(text(),1,1)='h'][substring(text(),2,1)='g']">
5158 <form authority="smd">microopaque</form>
5162 test="marc:controlfield[@tag=007][substring(text(),1,1)='m'][substring(text(),2,1)='c']">
5163 <form authority="smd">film cartridge</form>
5166 test="marc:controlfield[@tag=007][substring(text(),1,1)='m'][substring(text(),2,1)='f']">
5167 <form authority="smd">film cassette</form>
5170 test="marc:controlfield[@tag=007][substring(text(),1,1)='m'][substring(text(),2,1)='r']">
5171 <form authority="smd">film reel</form>
5175 test="marc:controlfield[@tag=007][substring(text(),1,1)='k'][substring(text(),2,1)='n']">
5176 <form authority="smd">chart</form>
5179 test="marc:controlfield[@tag=007][substring(text(),1,1)='k'][substring(text(),2,1)='c']">
5180 <form authority="smd">collage</form>
5183 test="marc:controlfield[@tag=007][substring(text(),1,1)='k'][substring(text(),2,1)='d']">
5184 <form authority="smd">drawing</form>
5187 test="marc:controlfield[@tag=007][substring(text(),1,1)='k'][substring(text(),2,1)='o']">
5188 <form authority="smd">flash card</form>
5191 test="marc:controlfield[@tag=007][substring(text(),1,1)='k'][substring(text(),2,1)='e']">
5192 <form authority="smd">painting</form>
5195 test="marc:controlfield[@tag=007][substring(text(),1,1)='k'][substring(text(),2,1)='f']">
5196 <form authority="smd">photomechanical print</form>
5199 test="marc:controlfield[@tag=007][substring(text(),1,1)='k'][substring(text(),2,1)='g']">
5200 <form authority="smd">photonegative</form>
5203 test="marc:controlfield[@tag=007][substring(text(),1,1)='k'][substring(text(),2,1)='h']">
5204 <form authority="smd">photoprint</form>
5207 test="marc:controlfield[@tag=007][substring(text(),1,1)='k'][substring(text(),2,1)='i']">
5208 <form authority="smd">picture</form>
5211 test="marc:controlfield[@tag=007][substring(text(),1,1)='k'][substring(text(),2,1)='j']">
5212 <form authority="smd">print</form>
5215 test="marc:controlfield[@tag=007][substring(text(),1,1)='k'][substring(text(),2,1)='l']">
5216 <form authority="smd">technical drawing</form>
5220 test="marc:controlfield[@tag=007][substring(text(),1,1)='q'][substring(text(),2,1)='q']">
5221 <form authority="smd">notated music</form>
5225 test="marc:controlfield[@tag=007][substring(text(),1,1)='g'][substring(text(),2,1)='d']">
5226 <form authority="smd">filmslip</form>
5229 test="marc:controlfield[@tag=007][substring(text(),1,1)='g'][substring(text(),2,1)='c']">
5230 <form authority="smd">filmstrip cartridge</form>
5233 test="marc:controlfield[@tag=007][substring(text(),1,1)='g'][substring(text(),2,1)='o']">
5234 <form authority="smd">filmstrip roll</form>
5237 test="marc:controlfield[@tag=007][substring(text(),1,1)='g'][substring(text(),2,1)='f']">
5238 <form authority="smd">other filmstrip type</form>
5241 test="marc:controlfield[@tag=007][substring(text(),1,1)='g'][substring(text(),2,1)='s']">
5242 <form authority="smd">slide</form>
5245 test="marc:controlfield[@tag=007][substring(text(),1,1)='g'][substring(text(),2,1)='t']">
5246 <form authority="smd">transparency</form>
5249 test="marc:controlfield[@tag=007][substring(text(),1,1)='r'][substring(text(),2,1)='r']">
5250 <form authority="smd">remote-sensing image</form>
5253 test="marc:controlfield[@tag=007][substring(text(),1,1)='s'][substring(text(),2,1)='e']">
5254 <form authority="smd">cylinder</form>
5257 test="marc:controlfield[@tag=007][substring(text(),1,1)='s'][substring(text(),2,1)='q']">
5258 <form authority="smd">roll</form>
5261 test="marc:controlfield[@tag=007][substring(text(),1,1)='s'][substring(text(),2,1)='g']">
5262 <form authority="smd">sound cartridge</form>
5265 test="marc:controlfield[@tag=007][substring(text(),1,1)='s'][substring(text(),2,1)='s']">
5266 <form authority="smd">sound cassette</form>
5269 test="marc:controlfield[@tag=007][substring(text(),1,1)='s'][substring(text(),2,1)='d']">
5270 <form authority="smd">sound disc</form>
5273 test="marc:controlfield[@tag=007][substring(text(),1,1)='s'][substring(text(),2,1)='t']">
5274 <form authority="smd">sound-tape reel</form>
5277 test="marc:controlfield[@tag=007][substring(text(),1,1)='s'][substring(text(),2,1)='i']">
5278 <form authority="smd">sound-track film</form>
5281 test="marc:controlfield[@tag=007][substring(text(),1,1)='s'][substring(text(),2,1)='w']">
5282 <form authority="smd">wire recording</form>
5286 test="marc:controlfield[@tag=007][substring(text(),1,1)='f'][substring(text(),2,1)='c']">
5287 <form authority="smd">braille</form>
5290 test="marc:controlfield[@tag=007][substring(text(),1,1)='f'][substring(text(),2,1)='b']">
5291 <form authority="smd">combination</form>
5294 test="marc:controlfield[@tag=007][substring(text(),1,1)='f'][substring(text(),2,1)='a']">
5295 <form authority="smd">moon</form>
5298 test="marc:controlfield[@tag=007][substring(text(),1,1)='f'][substring(text(),2,1)='d']">
5299 <form authority="smd">tactile, with no writing system</form>
5303 test="marc:controlfield[@tag=007][substring(text(),1,1)='t'][substring(text(),2,1)='c']">
5304 <form authority="smd">braille</form>
5307 test="marc:controlfield[@tag=007][substring(text(),1,1)='t'][substring(text(),2,1)='b']">
5308 <form authority="smd">large print</form>
5311 test="marc:controlfield[@tag=007][substring(text(),1,1)='t'][substring(text(),2,1)='a']">
5312 <form authority="smd">regular print</form>
5315 test="marc:controlfield[@tag=007][substring(text(),1,1)='t'][substring(text(),2,1)='d']">
5316 <form authority="smd">text in looseleaf binder</form>
5320 test="marc:controlfield[@tag=007][substring(text(),1,1)='v'][substring(text(),2,1)='c']">
5321 <form authority="smd">videocartridge</form>
5324 test="marc:controlfield[@tag=007][substring(text(),1,1)='v'][substring(text(),2,1)='f']">
5325 <form authority="smd">videocassette</form>
5328 test="marc:controlfield[@tag=007][substring(text(),1,1)='v'][substring(text(),2,1)='d']">
5329 <form authority="smd">videodisc</form>
5332 test="marc:controlfield[@tag=007][substring(text(),1,1)='v'][substring(text(),2,1)='r']">
5333 <form authority="smd">videoreel</form>
5337 select="marc:datafield[@tag=856]/marc:subfield[@code='q'][string-length(.)>1]">
5339 <xsl:value-of select="."/>
5340 </internetMediaType>
5342 <xsl:for-each select="marc:datafield[@tag=300]">
5344 <xsl:call-template name="subfieldSelect">
5345 <xsl:with-param name="codes">abce</xsl:with-param>
5346 </xsl:call-template>
5350 <xsl:if test="string-length(normalize-space($physicalDescription))">
5351 <physicalDescription>
5352 <xsl:copy-of select="$physicalDescription"/>
5353 </physicalDescription>
5355 <xsl:for-each select="marc:datafield[@tag=520]">
5357 <xsl:call-template name="uri"/>
5358 <xsl:call-template name="subfieldSelect">
5359 <xsl:with-param name="codes">ab</xsl:with-param>
5360 </xsl:call-template>
5363 <xsl:for-each select="marc:datafield[@tag=505]">
5365 <xsl:call-template name="uri"/>
5366 <xsl:call-template name="subfieldSelect">
5367 <xsl:with-param name="codes">agrt</xsl:with-param>
5368 </xsl:call-template>
5371 <xsl:for-each select="marc:datafield[@tag=521]">
5373 <xsl:call-template name="subfieldSelect">
5374 <xsl:with-param name="codes">ab</xsl:with-param>
5375 </xsl:call-template>
5378 <xsl:if test="$typeOf008='BK' or $typeOf008='CF' or $typeOf008='MU' or $typeOf008='VM'">
5379 <xsl:variable name="controlField008-22" select="substring($controlField008,23,1)"/>
5382 <xsl:when test="$controlField008-22='d'">
5383 <targetAudience authority="marctarget">adolescent</targetAudience>
5385 <xsl:when test="$controlField008-22='e'">
5386 <targetAudience authority="marctarget">adult</targetAudience>
5388 <xsl:when test="$controlField008-22='g'">
5389 <targetAudience authority="marctarget">general</targetAudience>
5392 test="$controlField008-22='b' or $controlField008-22='c' or $controlField008-22='j'">
5393 <targetAudience authority="marctarget">juvenile</targetAudience>
5395 <xsl:when test="$controlField008-22='a'">
5396 <targetAudience authority="marctarget">preschool</targetAudience>
5398 <xsl:when test="$controlField008-22='f'">
5399 <targetAudience authority="marctarget">specialized</targetAudience>
5403 <xsl:for-each select="marc:datafield[@tag=245]/marc:subfield[@code='c']">
5404 <note type="statement of responsibility">
5405 <xsl:value-of select="."/>
5408 <xsl:for-each select="marc:datafield[@tag=500]">
5410 <xsl:value-of select="marc:subfield[@code='a']"/>
5411 <xsl:call-template name="uri"/>
5415 <!--3.2 change tmee additional note fields-->
5417 <xsl:for-each select="marc:datafield[@tag=506]">
5418 <note type="restrictions">
5419 <xsl:call-template name="uri"/>
5420 <xsl:variable name="str">
5421 <xsl:for-each select="marc:subfield[@code!='6' or @code!='8']">
5422 <xsl:value-of select="."/>
5423 <xsl:text> </xsl:text>
5426 <xsl:value-of select="substring($str,1,string-length($str)-1)"/>
5430 <xsl:for-each select="marc:datafield[@tag=510]">
5431 <note type="citation/reference">
5432 <xsl:call-template name="uri"/>
5433 <xsl:variable name="str">
5434 <xsl:for-each select="marc:subfield[@code!='6' or @code!='8']">
5435 <xsl:value-of select="."/>
5436 <xsl:text> </xsl:text>
5439 <xsl:value-of select="substring($str,1,string-length($str)-1)"/>
5444 <xsl:for-each select="marc:datafield[@tag=511]">
5445 <note type="performers">
5446 <xsl:call-template name="uri"/>
5447 <xsl:value-of select="marc:subfield[@code='a']"/>
5450 <xsl:for-each select="marc:datafield[@tag=518]">
5452 <xsl:call-template name="uri"/>
5453 <xsl:value-of select="marc:subfield[@code='a']"/>
5457 <xsl:for-each select="marc:datafield[@tag=530]">
5458 <note type="additional physical form">
5459 <xsl:call-template name="uri"/>
5460 <xsl:variable name="str">
5461 <xsl:for-each select="marc:subfield[@code!='6' or @code!='8']">
5462 <xsl:value-of select="."/>
5463 <xsl:text> </xsl:text>
5466 <xsl:value-of select="substring($str,1,string-length($str)-1)"/>
5470 <xsl:for-each select="marc:datafield[@tag=533]">
5471 <note type="reproduction">
5472 <xsl:call-template name="uri"/>
5473 <xsl:variable name="str">
5474 <xsl:for-each select="marc:subfield[@code!='6' or @code!='8']">
5475 <xsl:value-of select="."/>
5476 <xsl:text> </xsl:text>
5479 <xsl:value-of select="substring($str,1,string-length($str)-1)"/>
5483 <xsl:for-each select="marc:datafield[@tag=534]">
5484 <note type="original version">
5485 <xsl:call-template name="uri"/>
5486 <xsl:variable name="str">
5487 <xsl:for-each select="marc:subfield[@code!='6' or @code!='8']">
5488 <xsl:value-of select="."/>
5489 <xsl:text> </xsl:text>
5492 <xsl:value-of select="substring($str,1,string-length($str)-1)"/>
5496 <xsl:for-each select="marc:datafield[@tag=538]">
5497 <note type="system details">
5498 <xsl:call-template name="uri"/>
5499 <xsl:variable name="str">
5500 <xsl:for-each select="marc:subfield[@code!='6' or @code!='8']">
5501 <xsl:value-of select="."/>
5502 <xsl:text> </xsl:text>
5505 <xsl:value-of select="substring($str,1,string-length($str)-1)"/>
5509 <xsl:for-each select="marc:datafield[@tag=583]">
5510 <note type="action">
5511 <xsl:call-template name="uri"/>
5512 <xsl:variable name="str">
5513 <xsl:for-each select="marc:subfield[@code!='6' or @code!='8']">
5514 <xsl:value-of select="."/>
5515 <xsl:text> </xsl:text>
5518 <xsl:value-of select="substring($str,1,string-length($str)-1)"/>
5523 select="marc:datafield[@tag=501 or @tag=502 or @tag=504 or @tag=507 or @tag=508 or @tag=513 or @tag=514 or @tag=515 or @tag=516 or @tag=522 or @tag=524 or @tag=525 or @tag=526 or @tag=535 or @tag=536 or @tag=540 or @tag=541 or @tag=544 or @tag=545 or @tag=546 or @tag=547 or @tag=550 or @tag=552 or @tag=555 or @tag=556 or @tag=561 or @tag=562 or @tag=565 or @tag=567 or @tag=580 or @tag=581 or @tag=584 or @tag=585 or @tag=586]">
5525 <xsl:call-template name="uri"/>
5526 <xsl:variable name="str">
5527 <xsl:for-each select="marc:subfield[@code!='6' or @code!='8']">
5528 <xsl:value-of select="."/>
5529 <xsl:text> </xsl:text>
5532 <xsl:value-of select="substring($str,1,string-length($str)-1)"/>
5536 select="marc:datafield[@tag=034][marc:subfield[@code='d' or @code='e' or @code='f' or @code='g']]">
5540 <xsl:call-template name="subfieldSelect">
5541 <xsl:with-param name="codes">defg</xsl:with-param>
5542 </xsl:call-template>
5547 <xsl:for-each select="marc:datafield[@tag=043]">
5549 <xsl:for-each select="marc:subfield[@code='a' or @code='b' or @code='c']">
5551 <xsl:attribute name="authority">
5552 <xsl:if test="@code='a'">
5553 <xsl:text>marcgac</xsl:text>
5555 <xsl:if test="@code='b'">
5556 <xsl:value-of select="following-sibling::marc:subfield[@code=2]"/>
5558 <xsl:if test="@code='c'">
5559 <xsl:text>iso3166</xsl:text>
5562 <xsl:value-of select="self::marc:subfield"/>
5567 <!-- tmee 2006/11/27 -->
5568 <xsl:for-each select="marc:datafield[@tag=255]">
5570 <xsl:for-each select="marc:subfield[@code='a' or @code='b' or @code='c']">
5572 <xsl:if test="@code='a'">
5574 <xsl:value-of select="."/>
5577 <xsl:if test="@code='b'">
5579 <xsl:value-of select="."/>
5582 <xsl:if test="@code='c'">
5584 <xsl:value-of select="."/>
5592 <xsl:apply-templates select="marc:datafield[653 >= @tag and @tag >= 600]"/>
5593 <xsl:apply-templates select="marc:datafield[@tag=656]"/>
5594 <xsl:for-each select="marc:datafield[@tag=752 or @tag=662]">
5596 <hierarchicalGeographic>
5597 <xsl:for-each select="marc:subfield[@code='a']">
5599 <xsl:call-template name="chopPunctuation">
5600 <xsl:with-param name="chopString" select="."/>
5601 </xsl:call-template>
5604 <xsl:for-each select="marc:subfield[@code='b']">
5606 <xsl:call-template name="chopPunctuation">
5607 <xsl:with-param name="chopString" select="."/>
5608 </xsl:call-template>
5611 <xsl:for-each select="marc:subfield[@code='c']">
5613 <xsl:call-template name="chopPunctuation">
5614 <xsl:with-param name="chopString" select="."/>
5615 </xsl:call-template>
5618 <xsl:for-each select="marc:subfield[@code='d']">
5620 <xsl:call-template name="chopPunctuation">
5621 <xsl:with-param name="chopString" select="."/>
5622 </xsl:call-template>
5625 <xsl:for-each select="marc:subfield[@code='e']">
5627 <xsl:call-template name="chopPunctuation">
5628 <xsl:with-param name="chopString" select="."/>
5629 </xsl:call-template>
5632 <xsl:for-each select="marc:subfield[@code='g']">
5634 <xsl:call-template name="chopPunctuation">
5635 <xsl:with-param name="chopString" select="."/>
5636 </xsl:call-template>
5639 <xsl:for-each select="marc:subfield[@code='h']">
5640 <extraterrestrialArea>
5641 <xsl:call-template name="chopPunctuation">
5642 <xsl:with-param name="chopString" select="."/>
5643 </xsl:call-template>
5644 </extraterrestrialArea>
5646 </hierarchicalGeographic>
5649 <xsl:for-each select="marc:datafield[@tag=045][marc:subfield[@code='b']]">
5652 <xsl:when test="@ind1=2">
5653 <temporal encoding="iso8601" point="start">
5654 <xsl:call-template name="chopPunctuation">
5655 <xsl:with-param name="chopString">
5656 <xsl:value-of select="marc:subfield[@code='b'][1]"/>
5658 </xsl:call-template>
5660 <temporal encoding="iso8601" point="end">
5661 <xsl:call-template name="chopPunctuation">
5662 <xsl:with-param name="chopString">
5663 <xsl:value-of select="marc:subfield[@code='b'][2]"/>
5665 </xsl:call-template>
5669 <xsl:for-each select="marc:subfield[@code='b']">
5670 <temporal encoding="iso8601">
5671 <xsl:call-template name="chopPunctuation">
5672 <xsl:with-param name="chopString" select="."/>
5673 </xsl:call-template>
5680 <xsl:for-each select="marc:datafield[@tag=050]">
5681 <xsl:for-each select="marc:subfield[@code='b']">
5682 <classification authority="lcc">
5683 <xsl:if test="../marc:subfield[@code='3']">
5684 <xsl:attribute name="displayLabel">
5685 <xsl:value-of select="../marc:subfield[@code='3']"/>
5688 <xsl:value-of select="preceding-sibling::marc:subfield[@code='a'][1]"/>
5689 <xsl:text> </xsl:text>
5690 <xsl:value-of select="text()"/>
5694 select="marc:subfield[@code='a'][not(following-sibling::marc:subfield[@code='b'])]">
5695 <classification authority="lcc">
5696 <xsl:if test="../marc:subfield[@code='3']">
5697 <xsl:attribute name="displayLabel">
5698 <xsl:value-of select="../marc:subfield[@code='3']"/>
5701 <xsl:value-of select="text()"/>
5705 <xsl:for-each select="marc:datafield[@tag=082]">
5706 <classification authority="ddc">
5707 <xsl:if test="marc:subfield[@code='2']">
5708 <xsl:attribute name="edition">
5709 <xsl:value-of select="marc:subfield[@code='2']"/>
5712 <xsl:call-template name="subfieldSelect">
5713 <xsl:with-param name="codes">ab</xsl:with-param>
5714 </xsl:call-template>
5717 <xsl:for-each select="marc:datafield[@tag=080]">
5718 <classification authority="udc">
5719 <xsl:call-template name="subfieldSelect">
5720 <xsl:with-param name="codes">abx</xsl:with-param>
5721 </xsl:call-template>
5724 <xsl:for-each select="marc:datafield[@tag=060]">
5725 <classification authority="nlm">
5726 <xsl:call-template name="subfieldSelect">
5727 <xsl:with-param name="codes">ab</xsl:with-param>
5728 </xsl:call-template>
5731 <xsl:for-each select="marc:datafield[@tag=086][@ind1=0]">
5732 <classification authority="sudocs">
5733 <xsl:value-of select="marc:subfield[@code='a']"/>
5736 <xsl:for-each select="marc:datafield[@tag=086][@ind1=1]">
5737 <classification authority="candoc">
5738 <xsl:value-of select="marc:subfield[@code='a']"/>
5741 <xsl:for-each select="marc:datafield[@tag=086]">
5743 <xsl:attribute name="authority">
5744 <xsl:value-of select="marc:subfield[@code='2']"/>
5746 <xsl:value-of select="marc:subfield[@code='a']"/>
5749 <xsl:for-each select="marc:datafield[@tag=084]">
5751 <xsl:attribute name="authority">
5752 <xsl:value-of select="marc:subfield[@code='2']"/>
5754 <xsl:call-template name="subfieldSelect">
5755 <xsl:with-param name="codes">ab</xsl:with-param>
5756 </xsl:call-template>
5759 <xsl:for-each select="marc:datafield[@tag=440]">
5760 <relatedItem type="series">
5763 <xsl:call-template name="chopPunctuation">
5764 <xsl:with-param name="chopString">
5765 <xsl:call-template name="subfieldSelect">
5766 <xsl:with-param name="codes">av</xsl:with-param>
5767 </xsl:call-template>
5769 </xsl:call-template>
5771 <xsl:call-template name="part"/>
5775 <xsl:for-each select="marc:datafield[@tag=490][@ind1=0]">
5776 <relatedItem type="series">
5779 <xsl:call-template name="chopPunctuation">
5780 <xsl:with-param name="chopString">
5781 <xsl:call-template name="subfieldSelect">
5782 <xsl:with-param name="codes">av</xsl:with-param>
5783 </xsl:call-template>
5785 </xsl:call-template>
5787 <xsl:call-template name="part"/>
5791 <xsl:for-each select="marc:datafield[@tag=510]">
5792 <relatedItem type="isReferencedBy">
5794 <xsl:call-template name="subfieldSelect">
5795 <xsl:with-param name="codes">abcx3</xsl:with-param>
5796 </xsl:call-template>
5800 <xsl:for-each select="marc:datafield[@tag=534]">
5801 <relatedItem type="original">
5802 <xsl:call-template name="relatedTitle"/>
5803 <xsl:call-template name="relatedName"/>
5804 <xsl:if test="marc:subfield[@code='b' or @code='c']">
5806 <xsl:for-each select="marc:subfield[@code='c']">
5808 <xsl:value-of select="."/>
5811 <xsl:for-each select="marc:subfield[@code='b']">
5813 <xsl:value-of select="."/>
5818 <xsl:call-template name="relatedIdentifierISSN"/>
5819 <xsl:for-each select="marc:subfield[@code='z']">
5820 <identifier type="isbn">
5821 <xsl:value-of select="."/>
5824 <xsl:call-template name="relatedNote"/>
5827 <xsl:for-each select="marc:datafield[@tag=700][marc:subfield[@code='t']]">
5829 <xsl:call-template name="constituentOrRelatedType"/>
5832 <xsl:call-template name="chopPunctuation">
5833 <xsl:with-param name="chopString">
5834 <xsl:call-template name="specialSubfieldSelect">
5835 <xsl:with-param name="anyCodes">tfklmorsv</xsl:with-param>
5836 <xsl:with-param name="axis">t</xsl:with-param>
5837 <xsl:with-param name="afterCodes">g</xsl:with-param>
5838 </xsl:call-template>
5840 </xsl:call-template>
5842 <xsl:call-template name="part"/>
5844 <name type="personal">
5846 <xsl:call-template name="specialSubfieldSelect">
5847 <xsl:with-param name="anyCodes">aq</xsl:with-param>
5848 <xsl:with-param name="axis">t</xsl:with-param>
5849 <xsl:with-param name="beforeCodes">g</xsl:with-param>
5850 </xsl:call-template>
5852 <xsl:call-template name="termsOfAddress"/>
5853 <xsl:call-template name="nameDate"/>
5854 <xsl:call-template name="role"/>
5856 <xsl:call-template name="relatedForm"/>
5857 <xsl:call-template name="relatedIdentifierISSN"/>
5860 <xsl:for-each select="marc:datafield[@tag=710][marc:subfield[@code='t']]">
5862 <xsl:call-template name="constituentOrRelatedType"/>
5865 <xsl:call-template name="chopPunctuation">
5866 <xsl:with-param name="chopString">
5867 <xsl:call-template name="specialSubfieldSelect">
5868 <xsl:with-param name="anyCodes">tfklmorsv</xsl:with-param>
5869 <xsl:with-param name="axis">t</xsl:with-param>
5870 <xsl:with-param name="afterCodes">dg</xsl:with-param>
5871 </xsl:call-template>
5873 </xsl:call-template>
5875 <xsl:call-template name="relatedPartNumName"/>
5877 <name type="corporate">
5878 <xsl:for-each select="marc:subfield[@code='a']">
5880 <xsl:value-of select="."/>
5883 <xsl:for-each select="marc:subfield[@code='b']">
5885 <xsl:value-of select="."/>
5888 <xsl:variable name="tempNamePart">
5889 <xsl:call-template name="specialSubfieldSelect">
5890 <xsl:with-param name="anyCodes">c</xsl:with-param>
5891 <xsl:with-param name="axis">t</xsl:with-param>
5892 <xsl:with-param name="beforeCodes">dgn</xsl:with-param>
5893 </xsl:call-template>
5895 <xsl:if test="normalize-space($tempNamePart)">
5897 <xsl:value-of select="$tempNamePart"/>
5900 <xsl:call-template name="role"/>
5902 <xsl:call-template name="relatedForm"/>
5903 <xsl:call-template name="relatedIdentifierISSN"/>
5906 <xsl:for-each select="marc:datafield[@tag=711][marc:subfield[@code='t']]">
5908 <xsl:call-template name="constituentOrRelatedType"/>
5911 <xsl:call-template name="chopPunctuation">
5912 <xsl:with-param name="chopString">
5913 <xsl:call-template name="specialSubfieldSelect">
5914 <xsl:with-param name="anyCodes">tfklsv</xsl:with-param>
5915 <xsl:with-param name="axis">t</xsl:with-param>
5916 <xsl:with-param name="afterCodes">g</xsl:with-param>
5917 </xsl:call-template>
5919 </xsl:call-template>
5921 <xsl:call-template name="relatedPartNumName"/>
5923 <name type="conference">
5925 <xsl:call-template name="specialSubfieldSelect">
5926 <xsl:with-param name="anyCodes">aqdc</xsl:with-param>
5927 <xsl:with-param name="axis">t</xsl:with-param>
5928 <xsl:with-param name="beforeCodes">gn</xsl:with-param>
5929 </xsl:call-template>
5932 <xsl:call-template name="relatedForm"/>
5933 <xsl:call-template name="relatedIdentifierISSN"/>
5936 <xsl:for-each select="marc:datafield[@tag=730][@ind2=2]">
5938 <xsl:call-template name="constituentOrRelatedType"/>
5941 <xsl:call-template name="chopPunctuation">
5942 <xsl:with-param name="chopString">
5943 <xsl:call-template name="subfieldSelect">
5944 <xsl:with-param name="codes">adfgklmorsv</xsl:with-param>
5945 </xsl:call-template>
5947 </xsl:call-template>
5949 <xsl:call-template name="part"/>
5951 <xsl:call-template name="relatedForm"/>
5952 <xsl:call-template name="relatedIdentifierISSN"/>
5955 <xsl:for-each select="marc:datafield[@tag=740][@ind2=2]">
5957 <xsl:call-template name="constituentOrRelatedType"/>
5960 <xsl:call-template name="chopPunctuation">
5961 <xsl:with-param name="chopString">
5962 <xsl:value-of select="marc:subfield[@code='a']"/>
5964 </xsl:call-template>
5966 <xsl:call-template name="part"/>
5968 <xsl:call-template name="relatedForm"/>
5971 <xsl:for-each select="marc:datafield[@tag=760]|marc:datafield[@tag=762]">
5972 <relatedItem type="series">
5973 <xsl:call-template name="relatedItem76X-78X"/>
5977 select="marc:datafield[@tag=765]|marc:datafield[@tag=767]|marc:datafield[@tag=777]|marc:datafield[@tag=787]">
5979 <xsl:call-template name="relatedItem76X-78X"/>
5982 <xsl:for-each select="marc:datafield[@tag=775]">
5983 <relatedItem type="otherVersion">
5984 <xsl:call-template name="relatedItem76X-78X"/>
5987 <xsl:for-each select="marc:datafield[@tag=770]|marc:datafield[@tag=774]">
5988 <relatedItem type="constituent">
5989 <xsl:call-template name="relatedItem76X-78X"/>
5992 <xsl:for-each select="marc:datafield[@tag=772]|marc:datafield[@tag=773]">
5993 <relatedItem type="host">
5994 <xsl:call-template name="relatedItem76X-78X"/>
5997 <xsl:for-each select="marc:datafield[@tag=776]">
5998 <relatedItem type="otherFormat">
5999 <xsl:call-template name="relatedItem76X-78X"/>
6002 <xsl:for-each select="marc:datafield[@tag=780]">
6003 <relatedItem type="preceding">
6004 <xsl:call-template name="relatedItem76X-78X"/>
6007 <xsl:for-each select="marc:datafield[@tag=785]">
6008 <relatedItem type="succeeding">
6009 <xsl:call-template name="relatedItem76X-78X"/>
6012 <xsl:for-each select="marc:datafield[@tag=786]">
6013 <relatedItem type="original">
6014 <xsl:call-template name="relatedItem76X-78X"/>
6017 <xsl:for-each select="marc:datafield[@tag=800]">
6018 <relatedItem type="series">
6021 <xsl:call-template name="chopPunctuation">
6022 <xsl:with-param name="chopString">
6023 <xsl:call-template name="specialSubfieldSelect">
6024 <xsl:with-param name="anyCodes">tfklmorsv</xsl:with-param>
6025 <xsl:with-param name="axis">t</xsl:with-param>
6026 <xsl:with-param name="afterCodes">g</xsl:with-param>
6027 </xsl:call-template>
6029 </xsl:call-template>
6031 <xsl:call-template name="part"/>
6033 <name type="personal">
6035 <xsl:call-template name="chopPunctuation">
6036 <xsl:with-param name="chopString">
6037 <xsl:call-template name="specialSubfieldSelect">
6038 <xsl:with-param name="anyCodes">aq</xsl:with-param>
6039 <xsl:with-param name="axis">t</xsl:with-param>
6040 <xsl:with-param name="beforeCodes">g</xsl:with-param>
6041 </xsl:call-template>
6043 </xsl:call-template>
6045 <xsl:call-template name="termsOfAddress"/>
6046 <xsl:call-template name="nameDate"/>
6047 <xsl:call-template name="role"/>
6049 <xsl:call-template name="relatedForm"/>
6052 <xsl:for-each select="marc:datafield[@tag=810]">
6053 <relatedItem type="series">
6056 <xsl:call-template name="chopPunctuation">
6057 <xsl:with-param name="chopString">
6058 <xsl:call-template name="specialSubfieldSelect">
6059 <xsl:with-param name="anyCodes">tfklmorsv</xsl:with-param>
6060 <xsl:with-param name="axis">t</xsl:with-param>
6061 <xsl:with-param name="afterCodes">dg</xsl:with-param>
6062 </xsl:call-template>
6064 </xsl:call-template>
6066 <xsl:call-template name="relatedPartNumName"/>
6068 <name type="corporate">
6069 <xsl:for-each select="marc:subfield[@code='a']">
6071 <xsl:value-of select="."/>
6074 <xsl:for-each select="marc:subfield[@code='b']">
6076 <xsl:value-of select="."/>
6080 <xsl:call-template name="specialSubfieldSelect">
6081 <xsl:with-param name="anyCodes">c</xsl:with-param>
6082 <xsl:with-param name="axis">t</xsl:with-param>
6083 <xsl:with-param name="beforeCodes">dgn</xsl:with-param>
6084 </xsl:call-template>
6086 <xsl:call-template name="role"/>
6088 <xsl:call-template name="relatedForm"/>
6091 <xsl:for-each select="marc:datafield[@tag=811]">
6092 <relatedItem type="series">
6095 <xsl:call-template name="chopPunctuation">
6096 <xsl:with-param name="chopString">
6097 <xsl:call-template name="specialSubfieldSelect">
6098 <xsl:with-param name="anyCodes">tfklsv</xsl:with-param>
6099 <xsl:with-param name="axis">t</xsl:with-param>
6100 <xsl:with-param name="afterCodes">g</xsl:with-param>
6101 </xsl:call-template>
6103 </xsl:call-template>
6105 <xsl:call-template name="relatedPartNumName"/>
6107 <name type="conference">
6109 <xsl:call-template name="specialSubfieldSelect">
6110 <xsl:with-param name="anyCodes">aqdc</xsl:with-param>
6111 <xsl:with-param name="axis">t</xsl:with-param>
6112 <xsl:with-param name="beforeCodes">gn</xsl:with-param>
6113 </xsl:call-template>
6115 <xsl:call-template name="role"/>
6117 <xsl:call-template name="relatedForm"/>
6120 <xsl:for-each select="marc:datafield[@tag='830']">
6121 <relatedItem type="series">
6124 <xsl:call-template name="chopPunctuation">
6125 <xsl:with-param name="chopString">
6126 <xsl:call-template name="subfieldSelect">
6127 <xsl:with-param name="codes">adfgklmorsv</xsl:with-param>
6128 </xsl:call-template>
6130 </xsl:call-template>
6132 <xsl:call-template name="part"/>
6134 <xsl:call-template name="relatedForm"/>
6137 <xsl:for-each select="marc:datafield[@tag='856'][@ind2='2']/marc:subfield[@code='q']">
6140 <xsl:value-of select="."/>
6141 </internetMediaType>
6144 <xsl:for-each select="marc:datafield[@tag='020']">
6145 <xsl:call-template name="isInvalid">
6146 <xsl:with-param name="type">isbn</xsl:with-param>
6147 </xsl:call-template>
6148 <xsl:if test="marc:subfield[@code='a']">
6149 <identifier type="isbn">
6150 <xsl:value-of select="marc:subfield[@code='a']"/>
6154 <xsl:for-each select="marc:datafield[@tag='024'][@ind1='0']">
6155 <xsl:call-template name="isInvalid">
6156 <xsl:with-param name="type">isrc</xsl:with-param>
6157 </xsl:call-template>
6158 <xsl:if test="marc:subfield[@code='a']">
6159 <identifier type="isrc">
6160 <xsl:value-of select="marc:subfield[@code='a']"/>
6164 <xsl:for-each select="marc:datafield[@tag='024'][@ind1='2']">
6165 <xsl:call-template name="isInvalid">
6166 <xsl:with-param name="type">ismn</xsl:with-param>
6167 </xsl:call-template>
6168 <xsl:if test="marc:subfield[@code='a']">
6169 <identifier type="ismn">
6170 <xsl:value-of select="marc:subfield[@code='a']"/>
6174 <xsl:for-each select="marc:datafield[@tag='024'][@ind1='4']">
6175 <xsl:call-template name="isInvalid">
6176 <xsl:with-param name="type">sici</xsl:with-param>
6177 </xsl:call-template>
6178 <identifier type="sici">
6179 <xsl:call-template name="subfieldSelect">
6180 <xsl:with-param name="codes">ab</xsl:with-param>
6181 </xsl:call-template>
6184 <xsl:for-each select="marc:datafield[@tag='022']">
6185 <xsl:if test="marc:subfield[@code='a']">
6186 <xsl:call-template name="isInvalid">
6187 <xsl:with-param name="type">issn</xsl:with-param>
6188 </xsl:call-template>
6189 <identifier type="issn">
6190 <xsl:value-of select="marc:subfield[@code='a']"/>
6193 <xsl:if test="marc:subfield[@code='l']">
6194 <xsl:call-template name="isInvalid">
6195 <xsl:with-param name="type">issn-l</xsl:with-param>
6196 </xsl:call-template>
6197 <identifier type="issn-l">
6198 <xsl:value-of select="marc:subfield[@code='l']"/>
6205 <xsl:for-each select="marc:datafield[@tag='010']">
6206 <xsl:call-template name="isInvalid">
6207 <xsl:with-param name="type">lccn</xsl:with-param>
6208 </xsl:call-template>
6209 <identifier type="lccn">
6210 <xsl:value-of select="normalize-space(marc:subfield[@code='a'])"/>
6213 <xsl:for-each select="marc:datafield[@tag='028']">
6215 <xsl:attribute name="type">
6217 <xsl:when test="@ind1='0'">issue number</xsl:when>
6218 <xsl:when test="@ind1='1'">matrix number</xsl:when>
6219 <xsl:when test="@ind1='2'">music plate</xsl:when>
6220 <xsl:when test="@ind1='3'">music publisher</xsl:when>
6221 <xsl:when test="@ind1='4'">videorecording identifier</xsl:when>
6224 <!--<xsl:call-template name="isInvalid"/>-->
6225 <!-- no $z in 028 -->
6226 <xsl:call-template name="subfieldSelect">
6227 <xsl:with-param name="codes">
6229 <xsl:when test="@ind1='0'">ba</xsl:when>
6230 <xsl:otherwise>ab</xsl:otherwise>
6233 </xsl:call-template>
6236 <xsl:for-each select="marc:datafield[@tag='037']">
6237 <identifier type="stock number">
6238 <!--<xsl:call-template name="isInvalid"/>-->
6239 <!-- no $z in 037 -->
6240 <xsl:call-template name="subfieldSelect">
6241 <xsl:with-param name="codes">ab</xsl:with-param>
6242 </xsl:call-template>
6245 <xsl:for-each select="marc:datafield[@tag='856'][marc:subfield[@code='u']]">
6247 <xsl:attribute name="type">
6250 test="starts-with(marc:subfield[@code='u'],'urn:doi') or starts-with(marc:subfield[@code='u'],'doi')"
6253 test="starts-with(marc:subfield[@code='u'],'urn:hdl') or starts-with(marc:subfield[@code='u'],'hdl') or starts-with(marc:subfield[@code='u'],'http://hdl.loc.gov')"
6255 <xsl:otherwise>uri</xsl:otherwise>
6260 test="starts-with(marc:subfield[@code='u'],'urn:hdl') or starts-with(marc:subfield[@code='u'],'hdl') or starts-with(marc:subfield[@code='u'],'http://hdl.loc.gov') ">
6262 select="concat('hdl:',substring-after(marc:subfield[@code='u'],'http://hdl.loc.gov/'))"
6266 <xsl:value-of select="marc:subfield[@code='u']"/>
6271 test="starts-with(marc:subfield[@code='u'],'urn:hdl') or starts-with(marc:subfield[@code='u'],'hdl')">
6272 <identifier type="hdl">
6273 <xsl:if test="marc:subfield[@code='y' or @code='3' or @code='z']">
6274 <xsl:attribute name="displayLabel">
6275 <xsl:call-template name="subfieldSelect">
6276 <xsl:with-param name="codes">y3z</xsl:with-param>
6277 </xsl:call-template>
6281 select="concat('hdl:',substring-after(marc:subfield[@code='u'],'http://hdl.loc.gov/'))"
6286 <xsl:for-each select="marc:datafield[@tag=024][@ind1=1]">
6287 <identifier type="upc">
6288 <xsl:call-template name="isInvalid"/>
6289 <xsl:value-of select="marc:subfield[@code='a']"/>
6293 <!-- 1/04 fix added $y -->
6296 <xsl:for-each select="marc:datafield[@tag=856][@ind2=1][marc:subfield[@code='u']]">
6297 <relatedItem type="otherVersion">
6300 <xsl:if test="marc:subfield[@code='y' or @code='3']">
6301 <xsl:attribute name="displayLabel">
6302 <xsl:call-template name="subfieldSelect">
6303 <xsl:with-param name="codes">y3</xsl:with-param>
6304 </xsl:call-template>
6307 <xsl:if test="marc:subfield[@code='z' ]">
6308 <xsl:attribute name="note">
6309 <xsl:call-template name="subfieldSelect">
6310 <xsl:with-param name="codes">z</xsl:with-param>
6311 </xsl:call-template>
6314 <xsl:value-of select="marc:subfield[@code='u']"/>
6319 <xsl:for-each select="marc:datafield[@tag=856][@ind2=2][marc:subfield[@code='u']]">
6323 <xsl:if test="marc:subfield[@code='y' or @code='3']">
6324 <xsl:attribute name="displayLabel">
6325 <xsl:call-template name="subfieldSelect">
6326 <xsl:with-param name="codes">y3</xsl:with-param>
6327 </xsl:call-template>
6330 <xsl:if test="marc:subfield[@code='z' ]">
6331 <xsl:attribute name="note">
6332 <xsl:call-template name="subfieldSelect">
6333 <xsl:with-param name="codes">z</xsl:with-param>
6334 </xsl:call-template>
6337 <xsl:value-of select="marc:subfield[@code='u']"/>
6343 <!-- 3.2 change tmee 856z -->
6346 <xsl:for-each select="marc:datafield[@tag=852]">
6348 <xsl:if test="marc:subfield[@code='a' or @code='b' or @code='e']">
6350 <xsl:call-template name="subfieldSelect">
6351 <xsl:with-param name="codes">abe</xsl:with-param>
6352 </xsl:call-template>
6356 <xsl:if test="marc:subfield[@code='u']">
6358 <xsl:call-template name="uri"/>
6359 <xsl:call-template name="subfieldSelect">
6360 <xsl:with-param name="codes">u</xsl:with-param>
6361 </xsl:call-template>
6366 test="marc:subfield[@code='h' or @code='i' or @code='j' or @code='k' or @code='l' or @code='m' or @code='t']">
6368 <xsl:call-template name="subfieldSelect">
6369 <xsl:with-param name="codes">hijklmt</xsl:with-param>
6370 </xsl:call-template>
6376 <xsl:for-each select="marc:datafield[@tag=506]">
6377 <accessCondition type="restrictionOnAccess">
6378 <xsl:call-template name="subfieldSelect">
6379 <xsl:with-param name="codes">abcd35</xsl:with-param>
6380 </xsl:call-template>
6383 <xsl:for-each select="marc:datafield[@tag=540]">
6384 <accessCondition type="useAndReproduction">
6385 <xsl:call-template name="subfieldSelect">
6386 <xsl:with-param name="codes">abcde35</xsl:with-param>
6387 </xsl:call-template>
6395 <xsl:for-each select="marc:leader[substring($leader,19,1)='a']">
6396 <descriptionStandard>aacr2</descriptionStandard>
6399 <xsl:for-each select="marc:datafield[@tag=040]">
6400 <xsl:if test="marc:subfield[@code='e']">
6401 <descriptionStandard>
6402 <xsl:value-of select="marc:subfield[@code='e']"/>
6403 </descriptionStandard>
6405 <recordContentSource authority="marcorg">
6406 <xsl:value-of select="marc:subfield[@code='a']"/>
6407 </recordContentSource>
6409 <xsl:for-each select="marc:controlfield[@tag=008]">
6410 <recordCreationDate encoding="marc">
6411 <xsl:value-of select="substring(.,1,6)"/>
6412 </recordCreationDate>
6415 <xsl:for-each select="marc:controlfield[@tag=005]">
6416 <recordChangeDate encoding="iso8601">
6417 <xsl:value-of select="."/>
6420 <xsl:for-each select="marc:controlfield[@tag=001]">
6422 <xsl:if test="../marc:controlfield[@tag=003]">
6423 <xsl:attribute name="source">
6424 <xsl:value-of select="../marc:controlfield[@tag=003]"/>
6427 <xsl:value-of select="."/>
6430 <xsl:for-each select="marc:datafield[@tag=040]/marc:subfield[@code='b']">
6431 <languageOfCataloging>
6432 <languageTerm authority="iso639-2b" type="code">
6433 <xsl:value-of select="."/>
6435 </languageOfCataloging>
6439 <xsl:template name="displayForm">
6440 <xsl:for-each select="marc:subfield[@code='c']">
6442 <xsl:value-of select="."/>
6446 <xsl:template name="affiliation">
6447 <xsl:for-each select="marc:subfield[@code='u']">
6449 <xsl:value-of select="."/>
6453 <xsl:template name="uri">
6454 <xsl:for-each select="marc:subfield[@code='u']|marc:subfield[@code='0']">
6455 <xsl:attribute name="xlink:href">
6456 <xsl:value-of select="."/>
6460 <xsl:template name="role">
6461 <xsl:for-each select="marc:subfield[@code='e']">
6463 <roleTerm type="text">
6464 <xsl:value-of select="."/>
6468 <xsl:for-each select="marc:subfield[@code='4']">
6470 <roleTerm authority="marcrelator" type="code">
6471 <xsl:value-of select="."/>
6476 <xsl:template name="part">
6477 <xsl:variable name="partNumber">
6478 <xsl:call-template name="specialSubfieldSelect">
6479 <xsl:with-param name="axis">n</xsl:with-param>
6480 <xsl:with-param name="anyCodes">n</xsl:with-param>
6481 <xsl:with-param name="afterCodes">fgkdlmor</xsl:with-param>
6482 </xsl:call-template>
6484 <xsl:variable name="partName">
6485 <xsl:call-template name="specialSubfieldSelect">
6486 <xsl:with-param name="axis">p</xsl:with-param>
6487 <xsl:with-param name="anyCodes">p</xsl:with-param>
6488 <xsl:with-param name="afterCodes">fgkdlmor</xsl:with-param>
6489 </xsl:call-template>
6491 <xsl:if test="string-length(normalize-space($partNumber))">
6493 <xsl:call-template name="chopPunctuation">
6494 <xsl:with-param name="chopString" select="$partNumber"/>
6495 </xsl:call-template>
6498 <xsl:if test="string-length(normalize-space($partName))">
6500 <xsl:call-template name="chopPunctuation">
6501 <xsl:with-param name="chopString" select="$partName"/>
6502 </xsl:call-template>
6506 <xsl:template name="relatedPart">
6507 <xsl:if test="@tag=773">
6508 <xsl:for-each select="marc:subfield[@code='g']">
6511 <xsl:value-of select="."/>
6515 <xsl:for-each select="marc:subfield[@code='q']">
6517 <xsl:call-template name="parsePart"/>
6522 <xsl:template name="relatedPartNumName">
6523 <xsl:variable name="partNumber">
6524 <xsl:call-template name="specialSubfieldSelect">
6525 <xsl:with-param name="axis">g</xsl:with-param>
6526 <xsl:with-param name="anyCodes">g</xsl:with-param>
6527 <xsl:with-param name="afterCodes">pst</xsl:with-param>
6528 </xsl:call-template>
6530 <xsl:variable name="partName">
6531 <xsl:call-template name="specialSubfieldSelect">
6532 <xsl:with-param name="axis">p</xsl:with-param>
6533 <xsl:with-param name="anyCodes">p</xsl:with-param>
6534 <xsl:with-param name="afterCodes">fgkdlmor</xsl:with-param>
6535 </xsl:call-template>
6537 <xsl:if test="string-length(normalize-space($partNumber))">
6539 <xsl:value-of select="$partNumber"/>
6542 <xsl:if test="string-length(normalize-space($partName))">
6544 <xsl:value-of select="$partName"/>
6548 <xsl:template name="relatedName">
6549 <xsl:for-each select="marc:subfield[@code='a']">
6552 <xsl:value-of select="."/>
6557 <xsl:template name="relatedForm">
6558 <xsl:for-each select="marc:subfield[@code='h']">
6559 <physicalDescription>
6561 <xsl:value-of select="."/>
6563 </physicalDescription>
6566 <xsl:template name="relatedExtent">
6567 <xsl:for-each select="marc:subfield[@code='h']">
6568 <physicalDescription>
6570 <xsl:value-of select="."/>
6572 </physicalDescription>
6575 <xsl:template name="relatedNote">
6576 <xsl:for-each select="marc:subfield[@code='n']">
6578 <xsl:value-of select="."/>
6582 <xsl:template name="relatedSubject">
6583 <xsl:for-each select="marc:subfield[@code='j']">
6585 <temporal encoding="iso8601">
6586 <xsl:call-template name="chopPunctuation">
6587 <xsl:with-param name="chopString" select="."/>
6588 </xsl:call-template>
6593 <xsl:template name="relatedIdentifierISSN">
6594 <xsl:for-each select="marc:subfield[@code='x']">
6595 <identifier type="issn">
6596 <xsl:value-of select="."/>
6600 <xsl:template name="relatedIdentifierLocal">
6601 <xsl:for-each select="marc:subfield[@code='w']">
6602 <identifier type="local">
6603 <xsl:value-of select="."/>
6607 <xsl:template name="relatedIdentifier">
6608 <xsl:for-each select="marc:subfield[@code='o']">
6610 <xsl:value-of select="."/>
6614 <xsl:template name="relatedItem76X-78X">
6615 <xsl:call-template name="displayLabel"/>
6616 <xsl:call-template name="relatedTitle76X-78X"/>
6617 <xsl:call-template name="relatedName"/>
6618 <xsl:call-template name="relatedOriginInfo"/>
6619 <xsl:call-template name="relatedLanguage"/>
6620 <xsl:call-template name="relatedExtent"/>
6621 <xsl:call-template name="relatedNote"/>
6622 <xsl:call-template name="relatedSubject"/>
6623 <xsl:call-template name="relatedIdentifier"/>
6624 <xsl:call-template name="relatedIdentifierISSN"/>
6625 <xsl:call-template name="relatedIdentifierLocal"/>
6626 <xsl:call-template name="relatedPart"/>
6628 <xsl:template name="subjectGeographicZ">
6630 <xsl:call-template name="chopPunctuation">
6631 <xsl:with-param name="chopString" select="."/>
6632 </xsl:call-template>
6635 <xsl:template name="subjectTemporalY">
6637 <xsl:call-template name="chopPunctuation">
6638 <xsl:with-param name="chopString" select="."/>
6639 </xsl:call-template>
6642 <xsl:template name="subjectTopic">
6644 <xsl:call-template name="chopPunctuation">
6645 <xsl:with-param name="chopString" select="."/>
6646 </xsl:call-template>
6649 <!-- 3.2 change tmee 6xx $v genre -->
6650 <xsl:template name="subjectGenre">
6652 <xsl:call-template name="chopPunctuation">
6653 <xsl:with-param name="chopString" select="."/>
6654 </xsl:call-template>
6658 <xsl:template name="nameABCDN">
6659 <xsl:for-each select="marc:subfield[@code='a']">
6661 <xsl:call-template name="chopPunctuation">
6662 <xsl:with-param name="chopString" select="."/>
6663 </xsl:call-template>
6666 <xsl:for-each select="marc:subfield[@code='b']">
6668 <xsl:value-of select="."/>
6672 test="marc:subfield[@code='c'] or marc:subfield[@code='d'] or marc:subfield[@code='n']">
6674 <xsl:call-template name="subfieldSelect">
6675 <xsl:with-param name="codes">cdn</xsl:with-param>
6676 </xsl:call-template>
6680 <xsl:template name="nameABCDQ">
6682 <xsl:call-template name="chopPunctuation">
6683 <xsl:with-param name="chopString">
6684 <xsl:call-template name="subfieldSelect">
6685 <xsl:with-param name="codes">aq</xsl:with-param>
6686 </xsl:call-template>
6688 <xsl:with-param name="punctuation">
6689 <xsl:text>:,;/ </xsl:text>
6691 </xsl:call-template>
6693 <xsl:call-template name="termsOfAddress"/>
6694 <xsl:call-template name="nameDate"/>
6696 <xsl:template name="nameACDEQ">
6698 <xsl:call-template name="subfieldSelect">
6699 <xsl:with-param name="codes">acdeq</xsl:with-param>
6700 </xsl:call-template>
6703 <xsl:template name="constituentOrRelatedType">
6704 <xsl:if test="@ind2=2">
6705 <xsl:attribute name="type">constituent</xsl:attribute>
6708 <xsl:template name="relatedTitle">
6709 <xsl:for-each select="marc:subfield[@code='t']">
6712 <xsl:call-template name="chopPunctuation">
6713 <xsl:with-param name="chopString">
6714 <xsl:value-of select="."/>
6716 </xsl:call-template>
6721 <xsl:template name="relatedTitle76X-78X">
6722 <xsl:for-each select="marc:subfield[@code='t']">
6725 <xsl:call-template name="chopPunctuation">
6726 <xsl:with-param name="chopString">
6727 <xsl:value-of select="."/>
6729 </xsl:call-template>
6731 <xsl:if test="marc:datafield[@tag!=773]and marc:subfield[@code='g']">
6732 <xsl:call-template name="relatedPartNumName"/>
6736 <xsl:for-each select="marc:subfield[@code='p']">
6737 <titleInfo type="abbreviated">
6739 <xsl:call-template name="chopPunctuation">
6740 <xsl:with-param name="chopString">
6741 <xsl:value-of select="."/>
6743 </xsl:call-template>
6745 <xsl:if test="marc:datafield[@tag!=773]and marc:subfield[@code='g']">
6746 <xsl:call-template name="relatedPartNumName"/>
6750 <xsl:for-each select="marc:subfield[@code='s']">
6751 <titleInfo type="uniform">
6753 <xsl:call-template name="chopPunctuation">
6754 <xsl:with-param name="chopString">
6755 <xsl:value-of select="."/>
6757 </xsl:call-template>
6759 <xsl:if test="marc:datafield[@tag!=773]and marc:subfield[@code='g']">
6760 <xsl:call-template name="relatedPartNumName"/>
6765 <xsl:template name="relatedOriginInfo">
6766 <xsl:if test="marc:subfield[@code='b' or @code='d'] or marc:subfield[@code='f']">
6768 <xsl:if test="@tag=775">
6769 <xsl:for-each select="marc:subfield[@code='f']">
6772 <xsl:attribute name="type">code</xsl:attribute>
6773 <xsl:attribute name="authority">marcgac</xsl:attribute>
6774 <xsl:value-of select="."/>
6779 <xsl:for-each select="marc:subfield[@code='d']">
6781 <xsl:value-of select="."/>
6784 <xsl:for-each select="marc:subfield[@code='b']">
6786 <xsl:value-of select="."/>
6792 <xsl:template name="relatedLanguage">
6793 <xsl:for-each select="marc:subfield[@code='e']">
6794 <xsl:call-template name="getLanguage">
6795 <xsl:with-param name="langString">
6796 <xsl:value-of select="."/>
6798 </xsl:call-template>
6801 <xsl:template name="nameDate">
6802 <xsl:for-each select="marc:subfield[@code='d']">
6803 <namePart type="date">
6804 <xsl:call-template name="chopPunctuation">
6805 <xsl:with-param name="chopString" select="."/>
6806 </xsl:call-template>
6810 <xsl:template name="subjectAuthority">
6811 <xsl:if test="@ind2!=4">
6812 <xsl:if test="@ind2!=' '">
6813 <xsl:if test="@ind2!=8">
6814 <xsl:if test="@ind2!=9">
6815 <xsl:attribute name="authority">
6817 <xsl:when test="@ind2=0">lcsh</xsl:when>
6818 <xsl:when test="@ind2=1">lcshac</xsl:when>
6819 <xsl:when test="@ind2=2">mesh</xsl:when>
6821 <xsl:when test="@ind2=3">nal</xsl:when>
6822 <xsl:when test="@ind2=5">csh</xsl:when>
6823 <xsl:when test="@ind2=6">rvm</xsl:when>
6824 <xsl:when test="@ind2=7">
6825 <xsl:value-of select="marc:subfield[@code='2']"/>
6834 <xsl:template name="subjectAnyOrder">
6835 <xsl:for-each select="marc:subfield[@code='v' or @code='x' or @code='y' or @code='z']">
6837 <xsl:when test="@code='v'">
6838 <xsl:call-template name="subjectGenre"/>
6840 <xsl:when test="@code='x'">
6841 <xsl:call-template name="subjectTopic"/>
6843 <xsl:when test="@code='y'">
6844 <xsl:call-template name="subjectTemporalY"/>
6846 <xsl:when test="@code='z'">
6847 <xsl:call-template name="subjectGeographicZ"/>
6852 <xsl:template name="specialSubfieldSelect">
6853 <xsl:param name="anyCodes"/>
6854 <xsl:param name="axis"/>
6855 <xsl:param name="beforeCodes"/>
6856 <xsl:param name="afterCodes"/>
6857 <xsl:variable name="str">
6858 <xsl:for-each select="marc:subfield">
6860 test="contains($anyCodes, @code) or (contains($beforeCodes,@code) and following-sibling::marc:subfield[@code=$axis]) or (contains($afterCodes,@code) and preceding-sibling::marc:subfield[@code=$axis])">
6861 <xsl:value-of select="text()"/>
6862 <xsl:text> </xsl:text>
6866 <xsl:value-of select="substring($str,1,string-length($str)-1)"/>
6869 <!-- 3.2 change tmee 6xx $v genre -->
6870 <xsl:template match="marc:datafield[@tag=600]">
6872 <xsl:call-template name="subjectAuthority"/>
6873 <name type="personal">
6874 <xsl:call-template name="termsOfAddress"/>
6876 <xsl:call-template name="chopPunctuation">
6877 <xsl:with-param name="chopString">
6878 <xsl:call-template name="subfieldSelect">
6879 <xsl:with-param name="codes">aq</xsl:with-param>
6880 </xsl:call-template>
6882 </xsl:call-template>
6884 <xsl:call-template name="nameDate"/>
6885 <xsl:call-template name="affiliation"/>
6886 <xsl:call-template name="role"/>
6888 <xsl:call-template name="subjectAnyOrder"/>
6891 <xsl:template match="marc:datafield[@tag=610]">
6893 <xsl:call-template name="subjectAuthority"/>
6894 <name type="corporate">
6895 <xsl:for-each select="marc:subfield[@code='a']">
6897 <xsl:value-of select="."/>
6900 <xsl:for-each select="marc:subfield[@code='b']">
6902 <xsl:value-of select="."/>
6905 <xsl:if test="marc:subfield[@code='c' or @code='d' or @code='n' or @code='p']">
6907 <xsl:call-template name="subfieldSelect">
6908 <xsl:with-param name="codes">cdnp</xsl:with-param>
6909 </xsl:call-template>
6912 <xsl:call-template name="role"/>
6914 <xsl:call-template name="subjectAnyOrder"/>
6917 <xsl:template match="marc:datafield[@tag=611]">
6919 <xsl:call-template name="subjectAuthority"/>
6920 <name type="conference">
6922 <xsl:call-template name="subfieldSelect">
6923 <xsl:with-param name="codes">abcdeqnp</xsl:with-param>
6924 </xsl:call-template>
6926 <xsl:for-each select="marc:subfield[@code='4']">
6928 <roleTerm authority="marcrelator" type="code">
6929 <xsl:value-of select="."/>
6934 <xsl:call-template name="subjectAnyOrder"/>
6937 <xsl:template match="marc:datafield[@tag=630]">
6939 <xsl:call-template name="subjectAuthority"/>
6942 <xsl:call-template name="chopPunctuation">
6943 <xsl:with-param name="chopString">
6944 <xsl:call-template name="subfieldSelect">
6945 <xsl:with-param name="codes">adfhklor</xsl:with-param>
6946 </xsl:call-template>
6948 </xsl:call-template>
6950 <xsl:call-template name="part"/>
6952 <xsl:call-template name="subjectAnyOrder"/>
6955 <!-- 1.27 648 tmee-->
6956 <xsl:template match="marc:datafield[@tag=648]">
6958 <xsl:if test="marc:subfield[@code=2]">
6959 <xsl:attribute name="authority">
6960 <xsl:value-of select="marc:subfield[@code=2]"/>
6963 <xsl:call-template name="uri"/>
6965 <xsl:call-template name="subjectAuthority"/>
6967 <xsl:call-template name="chopPunctuation">
6968 <xsl:with-param name="chopString">
6969 <xsl:call-template name="subfieldSelect">
6970 <xsl:with-param name="codes">abcd</xsl:with-param>
6971 </xsl:call-template>
6973 </xsl:call-template>
6975 <xsl:call-template name="subjectAnyOrder"/>
6979 <xsl:template match="marc:datafield[@tag=650]">
6981 <xsl:call-template name="subjectAuthority"/>
6983 <xsl:call-template name="chopPunctuation">
6984 <xsl:with-param name="chopString">
6985 <xsl:call-template name="subfieldSelect">
6986 <xsl:with-param name="codes">abcd</xsl:with-param>
6987 </xsl:call-template>
6989 </xsl:call-template>
6991 <xsl:call-template name="subjectAnyOrder"/>
6994 <xsl:template match="marc:datafield[@tag=651]">
6996 <xsl:call-template name="subjectAuthority"/>
6997 <xsl:for-each select="marc:subfield[@code='a']">
6999 <xsl:call-template name="chopPunctuation">
7000 <xsl:with-param name="chopString" select="."/>
7001 </xsl:call-template>
7004 <xsl:call-template name="subjectAnyOrder"/>
7007 <xsl:template match="marc:datafield[@tag=653]">
7009 <xsl:for-each select="marc:subfield[@code='a']">
7011 <xsl:value-of select="."/>
7016 <xsl:template match="marc:datafield[@tag=656]">
7018 <xsl:if test="marc:subfield[@code=2]">
7019 <xsl:attribute name="authority">
7020 <xsl:value-of select="marc:subfield[@code=2]"/>
7024 <xsl:call-template name="chopPunctuation">
7025 <xsl:with-param name="chopString">
7026 <xsl:value-of select="marc:subfield[@code='a']"/>
7028 </xsl:call-template>
7032 <xsl:template name="termsOfAddress">
7033 <xsl:if test="marc:subfield[@code='b' or @code='c']">
7034 <namePart type="termsOfAddress">
7035 <xsl:call-template name="chopPunctuation">
7036 <xsl:with-param name="chopString">
7037 <xsl:call-template name="subfieldSelect">
7038 <xsl:with-param name="codes">bc</xsl:with-param>
7039 </xsl:call-template>
7041 </xsl:call-template>
7045 <xsl:template name="displayLabel">
7046 <xsl:if test="marc:subfield[@code='i']">
7047 <xsl:attribute name="displayLabel">
7048 <xsl:value-of select="marc:subfield[@code='i']"/>
7051 <xsl:if test="marc:subfield[@code='3']">
7052 <xsl:attribute name="displayLabel">
7053 <xsl:value-of select="marc:subfield[@code='3']"/>
7057 <xsl:template name="isInvalid">
7058 <xsl:param name="type"/>
7060 test="marc:subfield[@code='z'] or marc:subfield[@code='y'] or marc:subfield[@code='m']">
7062 <xsl:attribute name="type">
7063 <xsl:value-of select="$type"/>
7065 <xsl:attribute name="invalid">
7066 <xsl:text>yes</xsl:text>
7068 <xsl:if test="marc:subfield[@code='z']">
7069 <xsl:value-of select="marc:subfield[@code='z']"/>
7071 <xsl:if test="marc:subfield[@code='y']">
7072 <xsl:value-of select="marc:subfield[@code='y']"/>
7074 <xsl:if test="marc:subfield[@code='m']">
7075 <xsl:value-of select="marc:subfield[@code='m']"/>
7080 <xsl:template name="subtitle">
7081 <xsl:if test="marc:subfield[@code='b']">
7083 <xsl:call-template name="chopPunctuation">
7084 <xsl:with-param name="chopString">
7085 <xsl:value-of select="marc:subfield[@code='b']"/>
7086 <!--<xsl:call-template name="subfieldSelect">
7087 <xsl:with-param name="codes">b</xsl:with-param>
7088 </xsl:call-template>-->
7090 </xsl:call-template>
7094 <xsl:template name="script">
7095 <xsl:param name="scriptCode"/>
7096 <xsl:attribute name="script">
7098 <xsl:when test="$scriptCode='(3'">Arabic</xsl:when>
7099 <xsl:when test="$scriptCode='(B'">Latin</xsl:when>
7100 <xsl:when test="$scriptCode='$1'">Chinese, Japanese, Korean</xsl:when>
7101 <xsl:when test="$scriptCode='(N'">Cyrillic</xsl:when>
7102 <xsl:when test="$scriptCode='(2'">Hebrew</xsl:when>
7103 <xsl:when test="$scriptCode='(S'">Greek</xsl:when>
7107 <xsl:template name="parsePart">
7108 <!-- assumes 773$q= 1:2:3<4
7109 with up to 3 levels and one optional start page
7111 <xsl:variable name="level1">
7113 <xsl:when test="contains(text(),':')">
7115 <xsl:value-of select="substring-before(text(),':')"/>
7117 <xsl:when test="not(contains(text(),':'))">
7119 <xsl:if test="contains(text(),'<')">
7121 <xsl:value-of select="substring-before(text(),'<')"/>
7123 <xsl:if test="not(contains(text(),'<'))">
7125 <xsl:value-of select="text()"/>
7130 <xsl:variable name="sici2">
7132 <xsl:when test="starts-with(substring-after(text(),$level1),':')">
7133 <xsl:value-of select="substring(substring-after(text(),$level1),2)"/>
7136 <xsl:value-of select="substring-after(text(),$level1)"/>
7140 <xsl:variable name="level2">
7142 <xsl:when test="contains($sici2,':')">
7144 <xsl:value-of select="substring-before($sici2,':')"/>
7146 <xsl:when test="contains($sici2,'<')">
7148 <xsl:value-of select="substring-before($sici2,'<')"/>
7151 <xsl:value-of select="$sici2"/>
7156 <xsl:variable name="sici3">
7158 <xsl:when test="starts-with(substring-after($sici2,$level2),':')">
7159 <xsl:value-of select="substring(substring-after($sici2,$level2),2)"/>
7162 <xsl:value-of select="substring-after($sici2,$level2)"/>
7166 <xsl:variable name="level3">
7168 <xsl:when test="contains($sici3,'<')">
7170 <xsl:value-of select="substring-before($sici3,'<')"/>
7173 <xsl:value-of select="$sici3"/>
7178 <xsl:variable name="page">
7179 <xsl:if test="contains(text(),'<')">
7180 <xsl:value-of select="substring-after(text(),'<')"/>
7183 <xsl:if test="$level1">
7186 <xsl:value-of select="$level1"/>
7190 <xsl:if test="$level2">
7193 <xsl:value-of select="$level2"/>
7197 <xsl:if test="$level3">
7200 <xsl:value-of select="$level3"/>
7204 <xsl:if test="$page">
7205 <extent unit="page">
7207 <xsl:value-of select="$page"/>
7212 <xsl:template name="getLanguage">
7213 <xsl:param name="langString"/>
7214 <xsl:param name="controlField008-35-37"/>
7215 <xsl:variable name="length" select="string-length($langString)"/>
7217 <xsl:when test="$length=0"/>
7218 <xsl:when test="$controlField008-35-37=substring($langString,1,3)">
7219 <xsl:call-template name="getLanguage">
7220 <xsl:with-param name="langString" select="substring($langString,4,$length)"/>
7221 <xsl:with-param name="controlField008-35-37" select="$controlField008-35-37"/>
7222 </xsl:call-template>
7226 <languageTerm authority="iso639-2b" type="code">
7227 <xsl:value-of select="substring($langString,1,3)"/>
7230 <xsl:call-template name="getLanguage">
7231 <xsl:with-param name="langString" select="substring($langString,4,$length)"/>
7232 <xsl:with-param name="controlField008-35-37" select="$controlField008-35-37"/>
7233 </xsl:call-template>
7237 <xsl:template name="isoLanguage">
7238 <xsl:param name="currentLanguage"/>
7239 <xsl:param name="usedLanguages"/>
7240 <xsl:param name="remainingLanguages"/>
7242 <xsl:when test="string-length($currentLanguage)=0"/>
7243 <xsl:when test="not(contains($usedLanguages, $currentLanguage))">
7245 <xsl:if test="@code!='a'">
7246 <xsl:attribute name="objectPart">
7248 <xsl:when test="@code='b'">summary or subtitle</xsl:when>
7249 <xsl:when test="@code='d'">sung or spoken text</xsl:when>
7250 <xsl:when test="@code='e'">libretto</xsl:when>
7251 <xsl:when test="@code='f'">table of contents</xsl:when>
7252 <xsl:when test="@code='g'">accompanying material</xsl:when>
7253 <xsl:when test="@code='h'">translation</xsl:when>
7257 <languageTerm authority="iso639-2b" type="code">
7258 <xsl:value-of select="$currentLanguage"/>
7261 <xsl:call-template name="isoLanguage">
7262 <xsl:with-param name="currentLanguage">
7263 <xsl:value-of select="substring($remainingLanguages,1,3)"/>
7265 <xsl:with-param name="usedLanguages">
7266 <xsl:value-of select="concat($usedLanguages,$currentLanguage)"/>
7268 <xsl:with-param name="remainingLanguages">
7270 select="substring($remainingLanguages,4,string-length($remainingLanguages))"
7273 </xsl:call-template>
7276 <xsl:call-template name="isoLanguage">
7277 <xsl:with-param name="currentLanguage">
7278 <xsl:value-of select="substring($remainingLanguages,1,3)"/>
7280 <xsl:with-param name="usedLanguages">
7281 <xsl:value-of select="concat($usedLanguages,$currentLanguage)"/>
7283 <xsl:with-param name="remainingLanguages">
7285 select="substring($remainingLanguages,4,string-length($remainingLanguages))"
7288 </xsl:call-template>
7292 <xsl:template name="chopBrackets">
7293 <xsl:param name="chopString"/>
7294 <xsl:variable name="string">
7295 <xsl:call-template name="chopPunctuation">
7296 <xsl:with-param name="chopString" select="$chopString"/>
7297 </xsl:call-template>
7299 <xsl:if test="substring($string, 1,1)='['">
7300 <xsl:value-of select="substring($string,2, string-length($string)-2)"/>
7302 <xsl:if test="substring($string, 1,1)!='['">
7303 <xsl:value-of select="$string"/>
7306 <xsl:template name="rfcLanguages">
7307 <xsl:param name="nodeNum"/>
7308 <xsl:param name="usedLanguages"/>
7309 <xsl:param name="controlField008-35-37"/>
7310 <xsl:variable name="currentLanguage" select="."/>
7312 <xsl:when test="not($currentLanguage)"/>
7314 test="$currentLanguage!=$controlField008-35-37 and $currentLanguage!='rfc3066'">
7315 <xsl:if test="not(contains($usedLanguages,$currentLanguage))">
7317 <xsl:if test="@code!='a'">
7318 <xsl:attribute name="objectPart">
7320 <xsl:when test="@code='b'">summary or subtitle</xsl:when>
7321 <xsl:when test="@code='d'">sung or spoken text</xsl:when>
7322 <xsl:when test="@code='e'">libretto</xsl:when>
7323 <xsl:when test="@code='f'">table of contents</xsl:when>
7324 <xsl:when test="@code='g'">accompanying material</xsl:when>
7325 <xsl:when test="@code='h'">translation</xsl:when>
7329 <languageTerm authority="rfc3066" type="code">
7330 <xsl:value-of select="$currentLanguage"/>
7335 <xsl:otherwise> </xsl:otherwise>
7339 <xsl:template name="datafield">
7340 <xsl:param name="tag"/>
7341 <xsl:param name="ind1">
7342 <xsl:text> </xsl:text>
7344 <xsl:param name="ind2">
7345 <xsl:text> </xsl:text>
7347 <xsl:param name="subfields"/>
7348 <xsl:element name="marc:datafield">
7349 <xsl:attribute name="tag">
7350 <xsl:value-of select="$tag"/>
7352 <xsl:attribute name="ind1">
7353 <xsl:value-of select="$ind1"/>
7355 <xsl:attribute name="ind2">
7356 <xsl:value-of select="$ind2"/>
7358 <xsl:copy-of select="$subfields"/>
7362 <xsl:template name="subfieldSelect">
7363 <xsl:param name="codes">abcdefghijklmnopqrstuvwxyz</xsl:param>
7364 <xsl:param name="delimeter">
7365 <xsl:text> </xsl:text>
7367 <xsl:variable name="str">
7368 <xsl:for-each select="marc:subfield">
7369 <xsl:if test="contains($codes, @code)">
7370 <xsl:value-of select="text()"/>
7371 <xsl:value-of select="$delimeter"/>
7375 <xsl:value-of select="substring($str,1,string-length($str)-string-length($delimeter))"/>
7378 <xsl:template name="buildSpaces">
7379 <xsl:param name="spaces"/>
7380 <xsl:param name="char">
7381 <xsl:text> </xsl:text>
7383 <xsl:if test="$spaces>0">
7384 <xsl:value-of select="$char"/>
7385 <xsl:call-template name="buildSpaces">
7386 <xsl:with-param name="spaces" select="$spaces - 1"/>
7387 <xsl:with-param name="char" select="$char"/>
7388 </xsl:call-template>
7392 <xsl:template name="chopPunctuation">
7393 <xsl:param name="chopString"/>
7394 <xsl:param name="punctuation">
7395 <xsl:text>.:,;/ </xsl:text>
7397 <xsl:variable name="length" select="string-length($chopString)"/>
7399 <xsl:when test="$length=0"/>
7400 <xsl:when test="contains($punctuation, substring($chopString,$length,1))">
7401 <xsl:call-template name="chopPunctuation">
7402 <xsl:with-param name="chopString" select="substring($chopString,1,$length - 1)"/>
7403 <xsl:with-param name="punctuation" select="$punctuation"/>
7404 </xsl:call-template>
7406 <xsl:when test="not($chopString)"/>
7408 <xsl:value-of select="$chopString"/>
7413 <xsl:template name="chopPunctuationFront">
7414 <xsl:param name="chopString"/>
7415 <xsl:variable name="length" select="string-length($chopString)"/>
7417 <xsl:when test="$length=0"/>
7418 <xsl:when test="contains('.:,;/[ ', substring($chopString,1,1))">
7419 <xsl:call-template name="chopPunctuationFront">
7420 <xsl:with-param name="chopString" select="substring($chopString,2,$length - 1)"
7422 </xsl:call-template>
7424 <xsl:when test="not($chopString)"/>
7426 <xsl:value-of select="$chopString"/>
7431 <xsl:template name="chopPunctuationBack">
7432 <xsl:param name="chopString"/>
7433 <xsl:param name="punctuation">
7434 <xsl:text>.:,;/] </xsl:text>
7436 <xsl:variable name="length" select="string-length($chopString)"/>
7438 <xsl:when test="$length=0"/>
7439 <xsl:when test="contains($punctuation, substring($chopString,$length,1))">
7440 <xsl:call-template name="chopPunctuation">
7441 <xsl:with-param name="chopString" select="substring($chopString,1,$length - 1)"/>
7442 <xsl:with-param name="punctuation" select="$punctuation"/>
7443 </xsl:call-template>
7445 <xsl:when test="not($chopString)"/>
7447 <xsl:value-of select="$chopString"/>
7452 <!-- nate added 12/14/2007 for lccn.loc.gov: url encode ampersand, etc. -->
7453 <xsl:template name="url-encode">
7455 <xsl:param name="str"/>
7457 <xsl:if test="$str">
7458 <xsl:variable name="first-char" select="substring($str,1,1)"/>
7460 <xsl:when test="contains($safe,$first-char)">
7461 <xsl:value-of select="$first-char"/>
7464 <xsl:variable name="codepoint">
7466 <xsl:when test="contains($ascii,$first-char)">
7468 select="string-length(substring-before($ascii,$first-char)) + 32"
7471 <xsl:when test="contains($latin1,$first-char)">
7473 select="string-length(substring-before($latin1,$first-char)) + 160"/>
7477 <xsl:message terminate="no">Warning: string contains a character
7478 that is out of range! Substituting "?".</xsl:message>
7479 <xsl:text>63</xsl:text>
7483 <xsl:variable name="hex-digit1"
7484 select="substring($hex,floor($codepoint div 16) + 1,1)"/>
7485 <xsl:variable name="hex-digit2" select="substring($hex,$codepoint mod 16 + 1,1)"/>
7486 <!-- <xsl:value-of select="concat('%',$hex-digit2)"/> -->
7487 <xsl:value-of select="concat('%',$hex-digit1,$hex-digit2)"/>
7490 <xsl:if test="string-length($str) > 1">
7491 <xsl:call-template name="url-encode">
7492 <xsl:with-param name="str" select="substring($str,2)"/>
7493 </xsl:call-template>
7497 </xsl:stylesheet>$$ WHERE name = 'mods33';
7499 ALTER TABLE actor.usr ALTER COLUMN juvenile SET NOT NULL;
7500 ALTER TABLE actor.usr_address ALTER COLUMN pending SET NOT NULL;
7501 ALTER TABLE config.circ_matrix_matchpoint ALTER COLUMN duration_rule SET NOT NULL;
7502 ALTER TABLE config.circ_matrix_matchpoint ALTER COLUMN recurring_fine_rule SET NOT NULL;
7503 ALTER TABLE config.circ_matrix_matchpoint ALTER COLUMN max_fine_rule SET NOT NULL;
7505 -- We're updating the IDL, so flush cached mods slim records to avoid field mismatches
7506 UPDATE metabib.metarecord SET mods = NULL;