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 acq CASCADE;
20 DROP SCHEMA 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_tranparency_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)
832 VALUES ( NEW.id, NEW.usr, NEW.xact_start, NEW.xact_finish, 0.0, 0.0, 0.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 ();
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 ('bnm_payment');
1087 CREATE TRIGGER mat_summary_upd_tgr AFTER UPDATE ON money.cash_payment FOR EACH ROW EXECUTE PROCEDURE money.materialized_summary_payment_update ('bnm_payment');
1088 CREATE TRIGGER mat_summary_del_tgr BEFORE DELETE ON money.cash_payment FOR EACH ROW EXECUTE PROCEDURE money.materialized_summary_payment_del ('bnm_payment');
1090 CREATE TRIGGER mat_summary_add_tgr AFTER INSERT ON money.check_payment FOR EACH ROW EXECUTE PROCEDURE money.materialized_summary_payment_add ('bnm_payment');
1091 CREATE TRIGGER mat_summary_upd_tgr AFTER UPDATE ON money.check_payment FOR EACH ROW EXECUTE PROCEDURE money.materialized_summary_payment_update ('bnm_payment');
1092 CREATE TRIGGER mat_summary_del_tgr BEFORE DELETE ON money.check_payment FOR EACH ROW EXECUTE PROCEDURE money.materialized_summary_payment_del ('bnm_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 ();
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_recuring_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.full_rec (
2535 id BIGSERIAL PRIMARY KEY,
2536 record BIGINT NOT NULL REFERENCES serial.record_entry(id) DEFERRABLE INITIALLY DEFERRED,
2537 tag CHAR(3) NOT NULL,
2541 value TEXT NOT NULL,
2542 index_vector tsvector NOT NULL
2544 CREATE INDEX serial_full_rec_record_idx ON serial.full_rec (record);
2545 CREATE INDEX serial_full_rec_tag_part_idx ON serial.full_rec (SUBSTRING(tag FROM 2));
2546 CREATE TRIGGER serial_full_rec_fti_trigger
2547 BEFORE UPDATE OR INSERT ON serial.full_rec
2548 FOR EACH ROW EXECUTE PROCEDURE tsearch2(index_vector, value);
2550 CREATE INDEX serial_full_rec_index_vector_idx ON serial.full_rec USING GIST (index_vector);
2551 /* Enable LIKE to use an index for database clusters with locales other than C or POSIX */
2552 CREATE INDEX serial_full_rec_value_tpo_index ON serial.full_rec (value text_pattern_ops);
2554 CREATE TABLE serial.subscription (
2555 id SERIAL PRIMARY KEY,
2556 callnumber BIGINT REFERENCES asset.call_number (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
2557 uri INT REFERENCES asset.uri (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
2558 start_date DATE NOT NULL,
2559 end_date DATE -- interpret NULL as current subscription
2562 CREATE TABLE serial.binding_unit (
2563 id SERIAL PRIMARY KEY,
2564 subscription INT NOT NULL REFERENCES serial.subscription (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
2565 label TEXT NOT NULL,
2566 CONSTRAINT bu_label_once_per_sub UNIQUE (subscription, label)
2569 CREATE TABLE serial.issuance (
2570 id SERIAL PRIMARY KEY,
2571 subscription INT NOT NULL REFERENCES serial.subscription (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
2572 target_copy BIGINT REFERENCES asset.copy (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
2573 location BIGINT REFERENCES asset.copy_location(id) DEFERRABLE INITIALLY DEFERRED,
2574 binding_unit INT REFERENCES serial.binding_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
2578 CREATE TABLE serial.bib_summary (
2579 id SERIAL PRIMARY KEY,
2580 subscription INT UNIQUE NOT NULL REFERENCES serial.subscription (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
2581 generated_coverage TEXT NOT NULL,
2582 textual_holdings TEXT
2585 CREATE TABLE serial.sup_summary (
2586 id SERIAL PRIMARY KEY,
2587 subscription INT UNIQUE NOT NULL REFERENCES serial.subscription (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
2588 generated_coverage TEXT NOT NULL,
2589 textual_holdings TEXT
2592 CREATE TABLE serial.index_summary (
2593 id SERIAL PRIMARY KEY,
2594 subscription INT UNIQUE NOT NULL REFERENCES serial.subscription (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
2595 generated_coverage TEXT NOT NULL,
2596 textual_holdings TEXT
2600 CREATE OR REPLACE FUNCTION search.staged_fts (
2602 param_search_ou INT,
2604 param_searches TEXT, -- JSON hash, to be turned into a resultset via search.parse_search_args
2605 param_statuses INT[],
2606 param_locations INT[],
2607 param_audience TEXT[],
2608 param_language TEXT[],
2609 param_lit_form TEXT[],
2612 param_vformats TEXT[],
2613 param_bib_level TEXT[],
2617 param_between TEXT[],
2618 param_pref_lang TEXT,
2619 param_pref_lang_multiplier REAL,
2621 param_sort_desc BOOL,
2624 param_rel_limit INT,
2625 param_chk_limit INT,
2628 ) RETURNS SETOF search.search_result AS $func$
2631 current_res search.search_result%ROWTYPE;
2632 query_part search.search_args%ROWTYPE;
2633 phrase_query_part search.search_args%ROWTYPE;
2638 rank_adjust search.relevance_adjustment%ROWTYPE;
2643 ranks TEXT[] := '{}';
2644 query_table_alias TEXT;
2645 from_alias_array TEXT[] := '{}';
2646 used_ranks TEXT[] := '{}';
2648 mb_field_list INT[];
2649 search_org_list INT[];
2650 select_clause TEXT := 'SELECT';
2651 from_clause TEXT := ' FROM metabib.metarecord_source_map m JOIN metabib.rec_descriptor mrd ON (m.source = mrd.record) ';
2652 where_clause TEXT := ' WHERE 1=1 ';
2653 mrd_used BOOL := FALSE;
2654 sort_desc BOOL := FALSE;
2657 core_cursor REFCURSOR;
2658 core_rel_query TEXT;
2659 vis_limit_query TEXT;
2660 inner_where_clause TEXT;
2662 total_count INT := 0;
2663 check_count INT := 0;
2664 deleted_count INT := 0;
2665 visible_count INT := 0;
2666 excluded_count INT := 0;
2670 core_rel_limit := COALESCE( param_rel_limit, 25000 );
2671 core_chk_limit := COALESCE( param_chk_limit, 1000 );
2672 core_skip_chk := COALESCE( param_skip_chk, 1 );
2675 select_clause := select_clause || ' m.metarecord as id, array_accum(distinct m.source) as records,';
2677 select_clause := select_clause || ' m.source as id, array_accum(distinct m.source) as records,';
2680 -- first we need to construct the base query
2681 FOR query_part IN SELECT * FROM search.parse_search_args(param_searches) WHERE term_type = 'fts_query' LOOP
2683 inner_where_clause := 'index_vector @@ ' || query_part.term;
2685 IF query_part.field_name IS NOT NULL THEN
2687 SELECT id INTO mb_field
2688 FROM config.metabib_field
2689 WHERE field_class = query_part.field_class
2690 AND name = query_part.field_name;
2693 inner_where_clause := inner_where_clause ||
2694 ' AND ' || 'field = ' || mb_field;
2699 -- moving on to the rank ...
2700 SELECT * INTO query_part
2701 FROM search.parse_search_args(param_searches)
2702 WHERE term_type = 'fts_rank'
2703 AND table_alias = query_part.table_alias;
2705 current_rank := query_part.term || ' * ' || query_part.table_alias || '_weight.weight';
2707 IF query_part.field_name IS NOT NULL THEN
2709 SELECT array_accum(distinct id) INTO mb_field_list
2710 FROM config.metabib_field
2711 WHERE field_class = query_part.field_class
2712 AND name = query_part.field_name;
2716 SELECT array_accum(distinct id) INTO mb_field_list
2717 FROM config.metabib_field
2718 WHERE field_class = query_part.field_class;
2722 FOR rank_adjust IN SELECT * FROM search.relevance_adjustment WHERE active AND field IN ( SELECT * FROM search.explode_array( mb_field_list ) ) LOOP
2724 IF NOT rank_adjust.bump_type = ANY (used_ranks) THEN
2726 IF rank_adjust.bump_type = 'first_word' THEN
2727 SELECT term INTO tmp_text
2728 FROM search.parse_search_args(param_searches)
2729 WHERE table_alias = query_part.table_alias AND term_type = 'word'
2733 tmp_text := query_part.table_alias || '.value ILIKE ' || quote_literal( tmp_text || '%' );
2735 ELSIF rank_adjust.bump_type = 'word_order' THEN
2736 SELECT array_to_string( array_accum( term ), '%' ) INTO tmp_text
2737 FROM search.parse_search_args(param_searches)
2738 WHERE table_alias = query_part.table_alias AND term_type = 'word';
2740 tmp_text := query_part.table_alias || '.value ILIKE ' || quote_literal( '%' || tmp_text || '%' );
2742 ELSIF rank_adjust.bump_type = 'full_match' THEN
2743 SELECT array_to_string( array_accum( term ), E'\\s+' ) INTO tmp_text
2744 FROM search.parse_search_args(param_searches)
2745 WHERE table_alias = query_part.table_alias AND term_type = 'word';
2747 tmp_text := query_part.table_alias || '.value ~ ' || quote_literal( '^' || tmp_text || E'\\W*$' );
2752 IF tmp_text IS NOT NULL THEN
2753 current_rank := current_rank || ' * ( CASE WHEN ' || tmp_text ||
2754 ' THEN ' || rank_adjust.multiplier || '::REAL ELSE 1.0 END )';
2757 used_ranks := array_append( used_ranks, rank_adjust.bump_type );
2763 ranks := array_append( ranks, current_rank );
2766 FOR phrase_query_part IN
2768 FROM search.parse_search_args(param_searches)
2769 WHERE term_type = 'phrase'
2770 AND table_alias = query_part.table_alias LOOP
2772 tmp_text := replace( phrase_query_part.term, '*', E'\\*' );
2773 tmp_text := replace( tmp_text, '?', E'\\?' );
2774 tmp_text := replace( tmp_text, '+', E'\\+' );
2775 tmp_text := replace( tmp_text, '|', E'\\|' );
2776 tmp_text := replace( tmp_text, '(', E'\\(' );
2777 tmp_text := replace( tmp_text, ')', E'\\)' );
2778 tmp_text := replace( tmp_text, '[', E'\\[' );
2779 tmp_text := replace( tmp_text, ']', E'\\]' );
2781 inner_where_clause := inner_where_clause || ' AND ' || 'value ~* ' || quote_literal( E'(^|\\W+)' || regexp_replace(tmp_text, E'\\s+',E'\\\\s+','g') || E'(\\W+|\$)' );
2785 query_table := search.pick_table(query_part.field_class);
2787 from_clause := from_clause ||
2788 ' JOIN ( SELECT * FROM ' || query_table || ' WHERE ' || inner_where_clause ||
2789 CASE WHEN core_rel_limit > 0 THEN ' LIMIT ' || core_rel_limit::TEXT ELSE '' END || ' ) AS ' || query_part.table_alias ||
2790 ' ON ( m.source = ' || query_part.table_alias || '.source )' ||
2791 ' JOIN config.metabib_field AS ' || query_part.table_alias || '_weight' ||
2792 ' ON ( ' || query_part.table_alias || '.field = ' || query_part.table_alias || '_weight.id AND ' || query_part.table_alias || '_weight.search_field)';
2794 from_alias_array := array_append(from_alias_array, query_part.table_alias);
2798 IF param_pref_lang IS NOT NULL AND param_pref_lang_multiplier IS NOT NULL THEN
2799 current_rank := ' CASE WHEN mrd.item_lang = ' || quote_literal( param_pref_lang ) ||
2800 ' THEN ' || param_pref_lang_multiplier || '::REAL ELSE 1.0 END ';
2802 -- ranks := array_append( ranks, current_rank );
2805 current_rank := ' AVG( ( (' || array_to_string( ranks, ') + (' ) || ') ) * ' || current_rank || ' ) ';
2806 select_clause := select_clause || current_rank || ' AS rel,';
2808 sort_desc = param_sort_desc;
2810 IF param_sort = 'pubdate' THEN
2812 tmp_text := '999999';
2813 IF param_sort_desc THEN tmp_text := '0'; END IF;
2815 current_rank := $$ COALESCE( FIRST(NULLIF(REGEXP_REPLACE(mrd.date1, E'\\D+', '9', 'g'),'')), $$ || quote_literal(tmp_text) || $$ )::INT $$;
2817 ELSIF param_sort = 'title' THEN
2819 tmp_text := 'zzzzzz';
2820 IF param_sort_desc THEN tmp_text := ' '; END IF;
2823 ( COALESCE( FIRST ((
2824 SELECT LTRIM(SUBSTR( frt.value, COALESCE(SUBSTRING(frt.ind2 FROM E'\\d+'),'0')::INT + 1 ))
2825 FROM metabib.full_rec frt
2826 WHERE frt.record = m.source
2828 AND frt.subfield = 'a'
2830 )),$$ || quote_literal(tmp_text) || $$))
2833 ELSIF param_sort = 'author' THEN
2835 tmp_text := 'zzzzzz';
2836 IF param_sort_desc THEN tmp_text := ' '; END IF;
2839 ( COALESCE( FIRST ((
2840 SELECT LTRIM(fra.value)
2841 FROM metabib.full_rec fra
2842 WHERE fra.record = m.source
2843 AND fra.tag LIKE '1%'
2844 AND fra.subfield = 'a'
2845 ORDER BY fra.tag::text::int
2847 )),$$ || quote_literal(tmp_text) || $$))
2850 ELSIF param_sort = 'create_date' THEN
2851 current_rank := $$( FIRST (( SELECT create_date FROM biblio.record_entry rbr WHERE rbr.id = m.source)) )$$;
2852 ELSIF param_sort = 'edit_date' THEN
2853 current_rank := $$( FIRST (( SELECT edit_date FROM biblio.record_entry rbr WHERE rbr.id = m.source)) )$$;
2855 sort_desc := NOT COALESCE(param_sort_desc, FALSE);
2858 select_clause := select_clause || current_rank || ' AS rank';
2860 -- now add the other qualifiers
2861 IF param_audience IS NOT NULL AND array_upper(param_audience, 1) > 0 THEN
2862 where_clause = where_clause || $$ AND mrd.audience IN ('$$ || array_to_string(param_audience, $$','$$) || $$') $$;
2865 IF param_language IS NOT NULL AND array_upper(param_language, 1) > 0 THEN
2866 where_clause = where_clause || $$ AND mrd.item_lang IN ('$$ || array_to_string(param_language, $$','$$) || $$') $$;
2869 IF param_lit_form IS NOT NULL AND array_upper(param_lit_form, 1) > 0 THEN
2870 where_clause = where_clause || $$ AND mrd.lit_form IN ('$$ || array_to_string(param_lit_form, $$','$$) || $$') $$;
2873 IF param_types IS NOT NULL AND array_upper(param_types, 1) > 0 THEN
2874 where_clause = where_clause || $$ AND mrd.item_type IN ('$$ || array_to_string(param_types, $$','$$) || $$') $$;
2877 IF param_forms IS NOT NULL AND array_upper(param_forms, 1) > 0 THEN
2878 where_clause = where_clause || $$ AND mrd.item_form IN ('$$ || array_to_string(param_forms, $$','$$) || $$') $$;
2881 IF param_vformats IS NOT NULL AND array_upper(param_vformats, 1) > 0 THEN
2882 where_clause = where_clause || $$ AND mrd.vr_format IN ('$$ || array_to_string(param_vformats, $$','$$) || $$') $$;
2885 IF param_bib_level IS NOT NULL AND array_upper(param_bib_level, 1) > 0 THEN
2886 where_clause = where_clause || $$ AND mrd.bib_level IN ('$$ || array_to_string(param_bib_level, $$','$$) || $$') $$;
2889 IF param_before IS NOT NULL AND param_before <> '' THEN
2890 where_clause = where_clause || $$ AND mrd.date1 <= $$ || quote_literal(param_before) || ' ';
2893 IF param_after IS NOT NULL AND param_after <> '' THEN
2894 where_clause = where_clause || $$ AND mrd.date1 >= $$ || quote_literal(param_after) || ' ';
2897 IF param_during IS NOT NULL AND param_during <> '' THEN
2898 where_clause = where_clause || $$ AND $$ || quote_literal(param_during) || $$ BETWEEN mrd.date1 AND mrd.date2 $$;
2901 IF param_between IS NOT NULL AND array_upper(param_between, 1) > 1 THEN
2902 where_clause = where_clause || $$ AND mrd.date1 BETWEEN '$$ || array_to_string(param_between, $$' AND '$$) || $$' $$;
2905 core_rel_query := select_clause || from_clause || where_clause ||
2906 ' GROUP BY 1 ORDER BY 4' || CASE WHEN sort_desc THEN ' DESC' ELSE ' ASC' END || ';';
2907 --RAISE NOTICE 'Base Query: %', core_rel_query;
2909 IF param_search_ou > 0 THEN
2910 IF param_depth IS NOT NULL THEN
2911 SELECT array_accum(distinct id) INTO search_org_list FROM actor.org_unit_descendants( param_search_ou, param_depth );
2913 SELECT array_accum(distinct id) INTO search_org_list FROM actor.org_unit_descendants( param_search_ou );
2915 ELSIF param_search_ou < 0 THEN
2916 SELECT array_accum(distinct org_unit) INTO search_org_list FROM actor.org_lasso_map WHERE lasso = -param_search_ou;
2917 ELSIF param_search_ou = 0 THEN
2918 -- reserved for user lassos (ou_buckets/type='lasso') with ID passed in depth ... hack? sure.
2921 OPEN core_cursor FOR EXECUTE core_rel_query;
2925 FETCH core_cursor INTO core_result;
2926 EXIT WHEN NOT FOUND;
2929 IF total_count % 1000 = 0 THEN
2930 -- RAISE NOTICE ' % total, % checked so far ... ', total_count, check_count;
2933 IF core_chk_limit > 0 AND total_count - core_skip_chk + 1 >= core_chk_limit THEN
2934 total_count := total_count + 1;
2938 total_count := total_count + 1;
2940 CONTINUE WHEN param_skip_chk IS NOT NULL and total_count < param_skip_chk;
2942 check_count := check_count + 1;
2944 PERFORM 1 FROM biblio.record_entry b WHERE NOT b.deleted AND b.id IN ( SELECT * FROM search.explode_array( core_result.records ) );
2946 -- RAISE NOTICE ' % were all deleted ... ', core_result.records;
2947 deleted_count := deleted_count + 1;
2952 FROM biblio.record_entry b
2953 JOIN config.bib_source s ON (b.source = s.id)
2954 WHERE s.transcendant
2955 AND b.id IN ( SELECT * FROM search.explode_array( core_result.records ) );
2958 -- RAISE NOTICE ' % were all transcendant ... ', core_result.records;
2959 visible_count := visible_count + 1;
2961 current_res.id = core_result.id;
2962 current_res.rel = core_result.rel;
2966 SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
2970 current_res.record = core_result.records[1];
2972 current_res.record = NULL;
2975 RETURN NEXT current_res;
2981 FROM asset.call_number cn
2982 JOIN asset.uri_call_number_map map ON (map.call_number = cn.id)
2983 JOIN asset.uri uri ON (map.uri = uri.id)
2984 WHERE NOT cn.deleted
2985 AND cn.label = '##URI##'
2987 AND ( param_locations IS NULL OR array_upper(param_locations, 1) IS NULL )
2988 AND cn.record IN ( SELECT * FROM search.explode_array( core_result.records ) )
2989 AND cn.owning_lib IN ( SELECT * FROM search.explode_array( search_org_list ) )
2993 -- RAISE NOTICE ' % have at least one URI ... ', core_result.records;
2994 visible_count := visible_count + 1;
2996 current_res.id = core_result.id;
2997 current_res.rel = core_result.rel;
3001 SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
3005 current_res.record = core_result.records[1];
3007 current_res.record = NULL;
3010 RETURN NEXT current_res;
3015 IF param_statuses IS NOT NULL AND array_upper(param_statuses, 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.status IN ( SELECT * FROM search.explode_array( param_statuses ) )
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 status-excluded ... ', core_result.records;
3029 excluded_count := excluded_count + 1;
3035 IF param_locations IS NOT NULL AND array_upper(param_locations, 1) > 0 THEN
3038 FROM asset.call_number cn
3039 JOIN asset.copy cp ON (cp.call_number = cn.id)
3040 WHERE NOT cn.deleted
3042 AND cp.location IN ( SELECT * FROM search.explode_array( param_locations ) )
3043 AND cn.record IN ( SELECT * FROM search.explode_array( core_result.records ) )
3044 AND cp.circ_lib IN ( SELECT * FROM search.explode_array( search_org_list ) )
3048 -- RAISE NOTICE ' % were all copy_location-excluded ... ', core_result.records;
3049 excluded_count := excluded_count + 1;
3055 IF staff IS NULL OR NOT staff THEN
3058 FROM asset.call_number cn
3059 JOIN asset.copy cp ON (cp.call_number = cn.id)
3060 JOIN actor.org_unit a ON (cp.circ_lib = a.id)
3061 JOIN asset.copy_location cl ON (cp.location = cl.id)
3062 JOIN config.copy_status cs ON (cp.status = cs.id)
3063 WHERE NOT cn.deleted
3069 AND cp.circ_lib IN ( SELECT * FROM search.explode_array( search_org_list ) )
3070 AND cn.record IN ( SELECT * FROM search.explode_array( core_result.records ) )
3074 -- RAISE NOTICE ' % were all visibility-excluded ... ', core_result.records;
3075 excluded_count := excluded_count + 1;
3082 FROM asset.call_number cn
3083 JOIN asset.copy cp ON (cp.call_number = cn.id)
3084 JOIN actor.org_unit a ON (cp.circ_lib = a.id)
3085 JOIN asset.copy_location cl ON (cp.location = cl.id)
3086 WHERE NOT cn.deleted
3088 AND cp.circ_lib IN ( SELECT * FROM search.explode_array( search_org_list ) )
3089 AND cn.record IN ( SELECT * FROM search.explode_array( core_result.records ) )
3095 FROM asset.call_number cn
3096 WHERE cn.record IN ( SELECT * FROM search.explode_array( core_result.records ) )
3100 -- RAISE NOTICE ' % were all visibility-excluded ... ', core_result.records;
3101 excluded_count := excluded_count + 1;
3109 visible_count := visible_count + 1;
3111 current_res.id = core_result.id;
3112 current_res.rel = core_result.rel;
3116 SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
3120 current_res.record = core_result.records[1];
3122 current_res.record = NULL;
3125 RETURN NEXT current_res;
3127 IF visible_count % 1000 = 0 THEN
3128 -- RAISE NOTICE ' % visible so far ... ', visible_count;
3133 current_res.id = NULL;
3134 current_res.rel = NULL;
3135 current_res.record = NULL;
3136 current_res.total = total_count;
3137 current_res.checked = check_count;
3138 current_res.deleted = deleted_count;
3139 current_res.visible = visible_count;
3140 current_res.excluded = excluded_count;
3144 RETURN NEXT current_res;
3147 $func$ LANGUAGE PLPGSQL;
3150 CREATE TABLE config.idl_field_doc (
3151 id BIGSERIAL PRIMARY KEY,
3152 fm_class TEXT NOT NULL,
3153 field TEXT NOT NULL,
3154 owner INT NOT NULL REFERENCES actor.org_unit (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
3155 string TEXT NOT NULL
3157 CREATE UNIQUE INDEX idl_field_doc_identity ON config.idl_field_doc (fm_class,field,owner);
3160 INSERT INTO config.xml_transform VALUES ( 'mods33', 'http://www.loc.gov/mods/v3', 'mods33', '');
3162 INSERT INTO container.copy_bucket_type (code,label) VALUES ('misc', 'Miscellaneous');
3163 INSERT INTO container.copy_bucket_type (code,label) VALUES ('staff_client', 'General Staff Client container');
3164 INSERT INTO container.call_number_bucket_type (code,label) VALUES ('misc', 'Miscellaneous');
3165 INSERT INTO container.biblio_record_entry_bucket_type (code,label) VALUES ('misc', 'Miscellaneous');
3166 INSERT INTO container.biblio_record_entry_bucket_type (code,label) VALUES ('staff_client', 'General Staff Client container');
3167 INSERT INTO container.biblio_record_entry_bucket_type (code,label) VALUES ('bookbag', 'Book Bag');
3168 INSERT INTO container.biblio_record_entry_bucket_type (code,label) VALUES ('reading_list', 'Reading List');
3170 INSERT INTO container.user_bucket_type (code,label) VALUES ('misc', 'Miscellaneous');
3171 INSERT INTO container.user_bucket_type (code,label) VALUES ('folks', 'Friends');
3172 INSERT INTO container.user_bucket_type (code,label) VALUES ('folks:pub_book_bags.view', 'List Published Book Bags');
3173 INSERT INTO container.user_bucket_type (code,label) VALUES ('folks:pub_book_bags.add', 'Add to Published Book Bags');
3174 INSERT INTO container.user_bucket_type (code,label) VALUES ('folks:circ.view', 'View Circulations');
3175 INSERT INTO container.user_bucket_type (code,label) VALUES ('folks:circ.renew', 'Renew Circulations');
3176 INSERT INTO container.user_bucket_type (code,label) VALUES ('folks:circ.checkout', 'Checkout Items');
3177 INSERT INTO container.user_bucket_type (code,label) VALUES ('folks:hold.view', 'View Holds');
3178 INSERT INTO container.user_bucket_type (code,label) VALUES ('folks:hold.cancel', 'Cancel Holds');
3182 CREATE SCHEMA action_trigger;
3184 CREATE TABLE action_trigger.hook (
3185 key TEXT PRIMARY KEY,
3186 core_type TEXT NOT NULL,
3188 passive BOOL NOT NULL DEFAULT FALSE
3190 INSERT INTO action_trigger.hook (key,core_type,description) VALUES ('checkout','circ','Item checked out to user');
3191 INSERT INTO action_trigger.hook (key,core_type,description) VALUES ('checkin','circ','Item checked in');
3192 INSERT INTO action_trigger.hook (key,core_type,description) VALUES ('lost','circ','Circulating Item marked Lost');
3193 INSERT INTO action_trigger.hook (key,core_type,description) VALUES ('lost.found','circ','Lost Circulating Item checked in');
3194 INSERT INTO action_trigger.hook (key,core_type,description) VALUES ('lost.auto','circ','Circulating Item automatically marked lost');
3195 INSERT INTO action_trigger.hook (key,core_type,description) VALUES ('claims_returned','circ','Circulating Item marked Claims Returned');
3196 INSERT INTO action_trigger.hook (key,core_type,description) VALUES ('claims_returned.found','circ','Claims Returned Circulating Item is checked in');
3197 INSERT INTO action_trigger.hook (key,core_type,description) VALUES ('missing','acp','Item marked Missing');
3198 INSERT INTO action_trigger.hook (key,core_type,description) VALUES ('missing.found','acp','Missing Item checked in');
3199 INSERT INTO action_trigger.hook (key,core_type,description) VALUES ('transit.start','acp','An Item is placed into transit');
3200 INSERT INTO action_trigger.hook (key,core_type,description) VALUES ('transit.finish','acp','An Item is received from a transit');
3201 INSERT INTO action_trigger.hook (key,core_type,description) VALUES ('hold_request.success','ahr','A hold is succefully placed');
3202 INSERT INTO action_trigger.hook (key,core_type,description) VALUES ('hold_request.failure','ahr','A hold is attempted by not succefully placed');
3203 INSERT INTO action_trigger.hook (key,core_type,description) VALUES ('hold.capture','ahr','A targeted Item is captured for a hold');
3204 INSERT INTO action_trigger.hook (key,core_type,description) VALUES ('hold.available','ahr','A held item is ready for pickup');
3205 INSERT INTO action_trigger.hook (key,core_type,description) VALUES ('hold_transit.start','ahtc','A hold-captured Item is placed into transit');
3206 INSERT INTO action_trigger.hook (key,core_type,description) VALUES ('hold_transit.finish','ahtc','A hold-captured Item is received from a transit');
3207 INSERT INTO action_trigger.hook (key,core_type,description,passive) VALUES ('checkout.due','circ','Checked out Item is Due',TRUE);
3208 INSERT INTO action_trigger.hook (key,core_type,description,passive) VALUES ('penalty.PATRON_EXCEEDS_FINES','ausp','Patron has exceeded allowed fines',TRUE);
3209 INSERT INTO action_trigger.hook (key,core_type,description,passive) VALUES ('penalty.PATRON_EXCEEDS_OVERDUE_COUNT','ausp','Patron has exceeded allowed overdue count',TRUE);
3210 INSERT INTO action_trigger.hook (key,core_type,description,passive) VALUES ('penalty.PATRON_EXCEEDS_CHECKOUT_COUNT','ausp','Patron has exceeded allowed checkout count',TRUE);
3211 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);
3212 INSERT INTO action_trigger.hook (key,core_type,description,passive) VALUES ('format.po.jedi','acqpo','Formats a Purchase Order as a JEDI document',TRUE);
3213 INSERT INTO action_trigger.hook (key,core_type,description,passive) VALUES ('format.po.html','acqpo','Formats a Purchase Order as an HTML document',TRUE);
3214 INSERT INTO action_trigger.hook (key,core_type,description,passive) VALUES ('format.po.pdf','acqpo','Formats a Purchase Order as a PDF document',TRUE);
3215 INSERT INTO action_trigger.hook (key,core_type,description) VALUES ('damaged','acp','Item marked damaged');
3216 INSERT INTO action_trigger.hook (key,core_type,description) VALUES ('checkout.damaged','circ','A circulating item is marked damaged and the patron is fined');
3217 -- and much more, I'm sure
3219 -- Specialized collection modules. Given an FM object, gather some info and return a scalar or ref.
3220 CREATE TABLE action_trigger.collector (
3221 module TEXT PRIMARY KEY, -- All live under the OpenILS::Trigger::Collector:: namespace
3224 INSERT INTO action_trigger.collector (module,description) VALUES ('fourty_two','Returns the answer to life, the universe and everything');
3225 --INSERT INTO action_trigger.collector (module,description) VALUES ('CircCountsByCircMod','Count of Circulations for a User, broken down by circulation modifier');
3227 -- Simple tests on an FM object from hook.core_type to test for "should we still do this."
3228 CREATE TABLE action_trigger.validator (
3229 module TEXT PRIMARY KEY, -- All live under the OpenILS::Trigger::Validator:: namespace
3232 INSERT INTO action_trigger.validator (module,description) VALUES ('fourty_two','Returns the answer to life, the universe and everything');
3233 INSERT INTO action_trigger.validator (module,description) VALUES ('NOOP_True','Always returns true -- validation always passes');
3234 INSERT INTO action_trigger.validator (module,description) VALUES ('NOOP_False','Always returns false -- validation always fails');
3235 INSERT INTO action_trigger.validator (module,description) VALUES ('CircIsOpen','Check that the circulation is still open');
3236 INSERT INTO action_trigger.validator (module,description) VALUES ('HoldIsAvailable','Check that an item is on the hold shelf');
3237 INSERT INTO action_trigger.validator (module,description) VALUES ('CircIsOverdue','Check that the circulation is overdue');
3239 -- After an event passes validation (action_trigger.validator), the reactor processes it.
3240 CREATE TABLE action_trigger.reactor (
3241 module TEXT PRIMARY KEY, -- All live under the OpenILS::Trigger::Reactor:: namespace
3244 INSERT INTO action_trigger.reactor (module,description) VALUES ('fourty_two','Returns the answer to life, the universe and everything');
3245 INSERT INTO action_trigger.reactor (module,description) VALUES ('NOOP_True','Always returns true -- reaction always passes');
3246 INSERT INTO action_trigger.reactor (module,description) VALUES ('NOOP_False','Always returns false -- reaction always fails');
3247 INSERT INTO action_trigger.reactor (module,description) VALUES ('SendEmail','Send an email based on a user-defined template');
3248 INSERT INTO action_trigger.reactor (module,description) VALUES ('GenerateBatchOverduePDF','Output a batch PDF of overdue notices for printing');
3249 INSERT INTO action_trigger.reactor (module,description) VALUES ('MarkItemLost','Marks a circulation and associated item as lost');
3250 INSERT INTO action_trigger.reactor (module,description) VALUES ('ApplyCircFee','Applies a billing with a pre-defined amount to a circulation');
3251 INSERT INTO action_trigger.reactor (module,description) VALUES ('ProcessTemplate', 'Processes the configured template');
3253 -- After an event is reacted to (either succes or failure) a cleanup module is run against the resulting environment
3254 CREATE TABLE action_trigger.cleanup (
3255 module TEXT PRIMARY KEY, -- All live under the OpenILS::Trigger::Cleanup:: namespace
3258 INSERT INTO action_trigger.cleanup (module,description) VALUES ('fourty_two','Returns the answer to life, the universe and everything');
3259 INSERT INTO action_trigger.cleanup (module,description) VALUES ('NOOP_True','Always returns true -- cleanup always passes');
3260 INSERT INTO action_trigger.cleanup (module,description) VALUES ('NOOP_False','Always returns false -- cleanup always fails');
3261 INSERT INTO action_trigger.cleanup (module,description) VALUES ('ClearAllPending','Remove all future, pending notifications for this target');
3263 CREATE TABLE action_trigger.event_definition (
3264 id SERIAL PRIMARY KEY,
3265 active BOOL NOT NULL DEFAULT TRUE,
3266 owner INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
3268 hook TEXT NOT NULL REFERENCES action_trigger.hook (key) DEFERRABLE INITIALLY DEFERRED,
3269 validator TEXT NOT NULL REFERENCES action_trigger.validator (module) DEFERRABLE INITIALLY DEFERRED,
3270 reactor TEXT NOT NULL REFERENCES action_trigger.reactor (module) DEFERRABLE INITIALLY DEFERRED,
3271 cleanup_success TEXT REFERENCES action_trigger.cleanup (module) DEFERRABLE INITIALLY DEFERRED,
3272 cleanup_failure TEXT REFERENCES action_trigger.cleanup (module) DEFERRABLE INITIALLY DEFERRED,
3273 delay INTERVAL NOT NULL DEFAULT '5 minutes',
3274 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()
3275 group_field TEXT, -- field from this.hook.core_type to batch event targets together on, fed into reactor a group at a time.
3276 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.
3277 CONSTRAINT ev_def_owner_hook_val_react_clean_delay_once UNIQUE (owner, hook, validator, reactor, delay, delay_field),
3278 CONSTRAINT ev_def_name_owner_once UNIQUE (owner, name)
3281 CREATE TABLE action_trigger.environment (
3282 id SERIAL PRIMARY KEY,
3283 event_def INT NOT NULL REFERENCES action_trigger.event_definition (id) DEFERRABLE INITIALLY DEFERRED,
3284 path TEXT, -- fields to flesh. given a hook with a core_type of circ, imagine circ_lib.parent_ou expanding to
3285 -- {flesh: 2, flesh_fields: {circ: ['circ_lib'], aou: ['parent_ou']}} ... default is to flesh all
3287 collector TEXT REFERENCES action_trigger.collector (module) DEFERRABLE INITIALLY DEFERRED, -- if set, given the object at 'path', return some data
3288 -- to be stashed at environment.<label>
3289 label TEXT CHECK (label NOT IN ('result','target','event')),
3290 CONSTRAINT env_event_label_once UNIQUE (event_def,label)
3293 CREATE TABLE action_trigger.event_output (
3294 id BIGSERIAL PRIMARY KEY,
3295 create_time TIMESTAMPTZ NOT NULL DEFAULT NOW(),
3296 is_error BOOLEAN NOT NULL DEFAULT FALSE,
3300 CREATE TABLE action_trigger.event (
3301 id BIGSERIAL PRIMARY KEY,
3302 target BIGINT NOT NULL, -- points at the id from class defined by event_def.hook.core_type
3303 event_def INT REFERENCES action_trigger.event_definition (id) DEFERRABLE INITIALLY DEFERRED,
3304 add_time TIMESTAMPTZ NOT NULL DEFAULT NOW(),
3305 run_time TIMESTAMPTZ NOT NULL,
3306 start_time TIMESTAMPTZ,
3307 update_time TIMESTAMPTZ,
3308 complete_time TIMESTAMPTZ,
3310 state TEXT NOT NULL DEFAULT 'pending' CHECK (state IN ('pending','invalid','found','collecting','collected','validating','valid','reacting','reacted','cleaning','complete','error')),
3311 template_output BIGINT REFERENCES action_trigger.event_output (id),
3312 error_output BIGINT REFERENCES action_trigger.event_output (id)
3315 CREATE TABLE action_trigger.event_params (
3316 id BIGSERIAL PRIMARY KEY,
3317 event_def INT NOT NULL REFERENCES action_trigger.event_definition (id) DEFERRABLE INITIALLY DEFERRED,
3318 param TEXT NOT NULL, -- the key under environment.event.params to store the output of ...
3319 value TEXT NOT NULL, -- ... the eval() output of this. Has access to environment (and, well, all of perl)
3320 CONSTRAINT event_params_event_def_param_once UNIQUE (event_def,param)
3325 -- Trigger Event Definitions -------------------------------------------------
3327 -- Sample Overdue Notice --
3329 INSERT INTO action_trigger.event_definition (id, active, owner, name, hook, validator, reactor, delay, delay_field, group_field, template)
3330 VALUES (1, 'f', 1, '7 Day Overdue Email Notification', 'checkout.due', 'CircIsOverdue', 'SendEmail', '7 days', 'due_date', 'usr',
3333 [%- user = target.0.usr -%]
3334 To: [%- params.recipient_email || user.email %]
3335 From: [%- params.sender_email || default_sender %]
3336 Subject: Overdue Notification
3338 Dear [% user.family_name %], [% user.first_given_name %]
3339 Our records indicate the following items are overdue.
3341 [% FOR circ IN target %]
3342 Title: [% circ.target_copy.call_number.record.simple_record.title %]
3343 Barcode: [% circ.target_copy.barcode %]
3344 Due: [% date.format(helpers.format_date(circ.due_date), '%Y-%m-%d') %]
3345 Item Cost: [% helpers.get_copy_price(circ.target_copy) %]
3346 Total Owed For Transaction: [% circ.billable_transaction.summary.total_owed %]
3347 Library: [% circ.circ_lib.name %]
3352 INSERT INTO action_trigger.environment (event_def, path) VALUES
3353 (1, 'target_copy.call_number.record.simple_record'),
3355 (1, 'billable_transaction.summary'),
3356 (1, 'circ_lib.billing_address');
3359 -- Sample Mark Long-Overdue Item Lost --
3361 INSERT INTO action_trigger.event_definition (id, active, owner, name, hook, validator, reactor, delay, delay_field)
3362 VALUES (2, 'f', 1, '90 Day Overdue Mark Lost', 'checkout.due', 'CircIsOverdue', 'MarkItemLost', '90 days', 'due_date');
3364 -- Sample Auto Mark Lost Notice --
3366 INSERT INTO action_trigger.event_definition (id, active, owner, name, hook, validator, reactor, group_field, template)
3367 VALUES (3, 'f', 1, '90 Day Overdue Mark Lost Notice', 'lost.auto', 'NOOP_True', 'SendEmail', 'usr',
3370 [%- user = target.0.usr -%]
3371 To: [%- params.recipient_email || user.email %]
3372 From: [%- params.sender_email || default_sender %]
3373 Subject: Overdue Items Marked Lost
3375 Dear [% user.family_name %], [% user.first_given_name %]
3376 The following items are 90 days overdue and have been marked LOST.
3378 [% FOR circ IN target %]
3379 Title: [% circ.target_copy.call_number.record.simple_record.title %]
3380 Barcode: [% circ.target_copy.barcode %]
3381 Due: [% date.format(helpers.format_date(circ.due_date), '%Y-%m-%d') %]
3382 Item Cost: [% helpers.get_copy_price(circ.target_copy) %]
3383 Total Owed For Transaction: [% circ.billable_transaction.summary.total_owed %]
3384 Library: [% circ.circ_lib.name %]
3390 INSERT INTO action_trigger.environment (event_def, path) VALUES
3391 (3, 'target_copy.call_number.record.simple_record'),
3393 (3, 'billable_transaction.summary'),
3394 (3, 'circ_lib.billing_address');
3396 -- Sample Purchase Order HTML Template --
3398 INSERT INTO action_trigger.event_definition (id, active, owner, name, hook, validator, reactor, template)
3399 VALUES (4, 't', 1, 'PO HTML', 'format.po.html', 'NOOP_True', 'ProcessTemplate',
3403 # find a lineitem attribute by name and optional type
3405 FOR attr IN li.attributes;
3406 IF attr.attr_name == attr_name;
3407 IF !attr_type OR attr_type == attr.attr_type;
3416 <h2>Purchase Order [% target.id %]</h2>
3418 date <b>[% date.format(date.now, '%Y%m%d') %]</b>
3422 table td { padding:5px; border:1px solid #aaa;}
3423 table { width:95%; border-collapse:collapse; }
3425 <table id='vendor-table'>
3427 <td valign='top'>Vendor</td>
3429 <div>[% target.provider.name %]</div>
3430 <div>[% target.provider.addresses.0.street1 %]</div>
3431 <div>[% target.provider.addresses.0.street2 %]</div>
3432 <div>[% target.provider.addresses.0.city %]</div>
3433 <div>[% target.provider.addresses.0.state %]</div>
3434 <div>[% target.provider.addresses.0.country %]</div>
3435 <div>[% target.provider.addresses.0.post_code %]</div>
3437 <td valign='top'>Ship to / Bill to</td>
3439 <div>[% target.ordering_agency.name %]</div>
3440 <div>[% target.ordering_agency.billing_address.street1 %]</div>
3441 <div>[% target.ordering_agency.billing_address.street2 %]</div>
3442 <div>[% target.ordering_agency.billing_address.city %]</div>
3443 <div>[% target.ordering_agency.billing_address.state %]</div>
3444 <div>[% target.ordering_agency.billing_address.country %]</div>
3445 <div>[% target.ordering_agency.billing_address.post_code %]</div>
3456 <th>ISBN or Item #</th>
3466 [% FOR li IN target.lineitems %]
3469 [% count = li.lineitem_details.size %]
3470 [% price = PROCESS get_li_attr attr_name = 'estimated_price' %]
3471 [% litotal = (price * count) %]
3472 [% subtotal = subtotal + litotal %]
3473 [% isbn = PROCESS get_li_attr attr_name = 'isbn' %]
3474 [% ident = PROCESS get_li_attr attr_name = 'identifier' %]
3476 <td>[% target.id %]</td>
3477 <td>[% isbn || ident %]</td>
3478 <td>[% PROCESS get_li_attr attr_name = 'title' %]</td>
3479 <td>[% count %]</td>
3480 <td>[% price %]</td>
3481 <td>[% litotal %]</td>
3485 <td/><td/><td/><td/>
3487 <td>[% subtotal %]</td>
3494 Total Line Item Count: [% target.lineitems.size %]
3497 INSERT INTO action_trigger.environment (event_def, path) VALUES
3498 (4, 'lineitems.lineitem_details.fund'),
3499 (4, 'lineitems.lineitem_details.location'),
3500 (4, 'lineitems.lineitem_details.owning_lib'),
3501 (4, 'ordering_agency.mailing_address'),
3502 (4, 'ordering_agency.billing_address'),
3503 (4, 'provider.addresses'),
3504 (4, 'lineitems.attributes');
3506 INSERT INTO action_trigger.event_definition (id, active, owner, name, hook, validator, reactor, delay, delay_field, group_field, template)
3507 VALUES (5, 'f', 1, 'Hold Ready for Pickup Email Notification', 'hold.available', 'HoldIsAvailable', 'SendEmail', '30 minutes', 'capture_time', 'usr',
3510 [%- user = target.0.usr -%]
3511 To: [%- params.recipient_email || user.email %]
3512 From: [%- params.sender_email || default_sender %]
3513 Subject: Hold Available Notification
3515 Dear [% user.family_name %], [% user.first_given_name %]
3516 The item(s) you requested are available for pickup from the Library.
3518 [% FOR hold IN target %]
3519 Title: [% hold.current_copy.call_number.record.simple_record.title %]
3520 Author: [% hold.current_copy.call_number.record.simple_record.author %]
3521 Call Number: [% hold.current_copy.call_number.label %]
3522 Barcode: [% hold.current_copy.barcode %]
3523 Library: [% hold.pickup_lib.name %]
3528 INSERT INTO action_trigger.environment (event_def, path) VALUES
3529 (5, 'current_copy.call_number.record.simple_record'),
3531 (5, 'pickup_lib.billing_address');
3534 SELECT SETVAL('action_trigger.event_definition_id_seq'::TEXT, 100);
3537 CREATE OR REPLACE FUNCTION asset.merge_record_assets( target_record BIGINT, source_record BIGINT ) RETURNS INT AS $func$
3539 moved_objects INT := 0;
3540 source_cn asset.call_number%ROWTYPE;
3541 target_cn asset.call_number%ROWTYPE;
3542 metarec metabib.metarecord%ROWTYPE;
3543 hold action.hold_request%ROWTYPE;
3544 ser_rec serial.record_entry%ROWTYPE;
3548 uri_text TEXT := '';
3551 -- move any 856 entries on records that have at least one MARC-mapped URI entry
3552 SELECT INTO uri_count COUNT(*)
3553 FROM asset.uri_call_number_map m
3554 JOIN asset.call_number cn ON (m.call_number = cn.id)
3555 WHERE cn.record = source_record;
3557 IF uri_count > 0 THEN
3559 SELECT COUNT(*) INTO counter
3566 ) as t(i int,c text);
3568 FOR i IN 1 .. counter LOOP
3569 SELECT '<datafield xmlns="http://www.loc.gov/MARC21/slim" tag="856">' ||
3572 '<subfield code="' || subfield || '">' ||
3575 regexp_replace(data,'&','&','g'),
3581 ) || '</datafield>' INTO uri_datafield
3585 'biblio.record_entry',
3586 '//*[@tag="856"][position()=' || i || ']/*/@code|' ||
3587 '//*[@tag="856"][position()=' || i || ']/*[@code]',
3588 'id=' || source_record
3589 ) as t(id int,subfield text,data text);
3591 uri_text := uri_text || uri_datafield;
3594 IF uri_text <> '' THEN
3595 UPDATE biblio.record_entry
3596 SET marc = regexp_replace(marc,'(</[^>]*record>)', uri_text || E'\\1')
3597 WHERE id = target_record;
3602 -- Find and move metarecords to the target record
3603 SELECT INTO metarec *
3604 FROM metabib.metarecord
3605 WHERE master_record = source_record;
3608 UPDATE metabib.metarecord
3609 SET master_record = target_record,
3611 WHERE id = metarec.id;
3613 moved_objects := moved_objects + 1;
3616 -- Find call numbers attached to the source ...
3617 FOR source_cn IN SELECT * FROM asset.call_number WHERE record = source_record LOOP
3619 SELECT INTO target_cn *
3620 FROM asset.call_number
3621 WHERE label = source_cn.label
3622 AND owning_lib = source_cn.owning_lib
3623 AND record = target_record;
3625 -- ... and if there's a conflicting one on the target ...
3628 -- ... move the copies to that, and ...
3630 SET call_number = target_cn.id
3631 WHERE call_number = source_cn.id;
3633 -- ... move V holds to the move-target call number
3634 FOR hold IN SELECT * FROM action.hold_request WHERE target = source_cn.id AND hold_type = 'V' LOOP
3636 UPDATE action.hold_request
3637 SET target = target_cn.id
3640 moved_objects := moved_objects + 1;
3645 -- ... just move the call number to the target record
3646 UPDATE asset.call_number
3647 SET record = target_record
3648 WHERE id = source_cn.id;
3651 moved_objects := moved_objects + 1;
3654 -- Find T holds targeting the source record ...
3655 FOR hold IN SELECT * FROM action.hold_request WHERE target = source_record AND hold_type = 'T' LOOP
3657 -- ... and move them to the target record
3658 UPDATE action.hold_request
3659 SET target = target_record
3662 moved_objects := moved_objects + 1;
3665 -- Find serial records targeting the source record ...
3666 FOR ser_rec IN SELECT * FROM serial.record_entry WHERE record = source_record LOOP
3667 -- ... and move them to the target record
3668 UPDATE serial.record_entry
3669 SET record = target_record
3670 WHERE id = ser_rec.id;
3672 moved_objects := moved_objects + 1;
3675 -- Finally, "delete" the source record
3676 DELETE FROM biblio.record_entry WHERE id = source_record;
3678 -- That's all, folks!
3679 RETURN moved_objects;
3681 $func$ LANGUAGE plpgsql;
3684 CREATE OR REPLACE FUNCTION actor.usr_merge_rows( table_name TEXT, col_name TEXT, src_usr INT, dest_usr INT ) RETURNS VOID AS $$
3691 sel := 'SELECT id::BIGINT FROM ' || table_name || ' WHERE ' || quote_ident(col_name) || ' = ' || quote_literal(src_usr);
3692 upd := 'UPDATE ' || table_name || ' SET ' || quote_ident(col_name) || ' = ' || quote_literal(dest_usr) || ' WHERE id = ';
3693 del := 'DELETE FROM ' || table_name || ' WHERE id = ';
3694 FOR cur_row IN EXECUTE sel LOOP
3696 --RAISE NOTICE 'Attempting to merge % %', table_name, cur_row.id;
3697 EXECUTE upd || cur_row.id;
3698 EXCEPTION WHEN unique_violation THEN
3699 --RAISE NOTICE 'Deleting conflicting % %', table_name, cur_row.id;
3700 EXECUTE del || cur_row.id;
3704 $$ LANGUAGE plpgsql;
3706 COMMENT ON FUNCTION actor.usr_merge_rows(TEXT, TEXT, INT, INT) IS $$
3708 * Attempts to move each row of the specified table from src_user to dest_user.
3709 * Where conflicts exist, the conflicting "source" row is deleted.
3714 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 $$
3717 -- do some initial cleanup
3718 UPDATE actor.usr SET card = NULL WHERE id = src_usr;
3719 UPDATE actor.usr SET mailing_address = NULL WHERE id = src_usr;
3720 UPDATE actor.usr SET billing_address = NULL WHERE id = src_usr;
3724 DELETE FROM actor.card where usr = src_usr;
3726 IF deactivate_cards THEN
3727 UPDATE actor.card SET active = 'f' WHERE usr = src_usr;
3729 UPDATE actor.card SET usr = dest_usr WHERE usr = src_usr;
3734 DELETE FROM actor.usr_address WHERE usr = src_usr;
3736 UPDATE actor.usr_address SET usr = dest_usr WHERE usr = src_usr;
3739 UPDATE actor.usr_note SET usr = dest_usr WHERE usr = src_usr;
3740 -- dupes are technically OK in actor.usr_standing_penalty, should manually delete them...
3741 UPDATE actor.usr_standing_penalty SET usr = dest_usr WHERE usr = src_usr;
3742 PERFORM actor.usr_merge_rows('actor.usr_org_unit_opt_in', 'usr', src_usr, dest_usr);
3743 PERFORM actor.usr_merge_rows('actor.usr_setting', 'usr', src_usr, dest_usr);
3746 PERFORM actor.usr_merge_rows('permission.usr_perm_map', 'usr', src_usr, dest_usr);
3747 PERFORM actor.usr_merge_rows('permission.usr_object_perm_map', 'usr', src_usr, dest_usr);
3748 PERFORM actor.usr_merge_rows('permission.usr_grp_map', 'usr', src_usr, dest_usr);
3749 PERFORM actor.usr_merge_rows('permission.usr_work_ou_map', 'usr', src_usr, dest_usr);
3753 PERFORM actor.usr_merge_rows('container.biblio_record_entry_bucket', 'owner', src_usr, dest_usr);
3754 PERFORM actor.usr_merge_rows('container.call_number_bucket', 'owner', src_usr, dest_usr);
3755 PERFORM actor.usr_merge_rows('container.copy_bucket', 'owner', src_usr, dest_usr);
3756 PERFORM actor.usr_merge_rows('container.user_bucket', 'owner', src_usr, dest_usr);
3757 PERFORM actor.usr_merge_rows('container.user_bucket_item', 'target_user', src_usr, dest_usr);
3760 PERFORM actor.usr_merge_rows('vandelay.queue', 'owner', src_usr, dest_usr);
3763 PERFORM actor.usr_merge_rows('money.collections_tracker', 'usr', src_usr, dest_usr);
3764 PERFORM actor.usr_merge_rows('money.collections_tracker', 'collector', src_usr, dest_usr);
3765 UPDATE money.billable_xact SET usr = dest_usr WHERE usr = src_usr;
3766 UPDATE money.billing SET voider = dest_usr WHERE voider = src_usr;
3767 UPDATE money.bnm_payment SET accepting_usr = dest_usr WHERE accepting_usr = src_usr;
3770 UPDATE action.circulation SET usr = dest_usr WHERE usr = src_usr;
3771 UPDATE action.circulation SET circ_staff = dest_usr WHERE circ_staff = src_usr;
3772 UPDATE action.circulation SET checkin_staff = dest_usr WHERE checkin_staff = src_usr;
3774 UPDATE action.hold_request SET usr = dest_usr WHERE usr = src_usr;
3775 UPDATE action.hold_request SET fulfillment_staff = dest_usr WHERE fulfillment_staff = src_usr;
3776 UPDATE action.hold_request SET requestor = dest_usr WHERE requestor = src_usr;
3777 UPDATE action.hold_notification SET notify_staff = dest_usr WHERE notify_staff = src_usr;
3779 UPDATE action.in_house_use SET staff = dest_usr WHERE staff = src_usr;
3780 UPDATE action.non_cataloged_circulation SET staff = dest_usr WHERE staff = src_usr;
3781 UPDATE action.non_cataloged_circulation SET patron = dest_usr WHERE patron = src_usr;
3782 UPDATE action.non_cat_in_house_use SET staff = dest_usr WHERE staff = src_usr;
3783 UPDATE action.survey_response SET usr = dest_usr WHERE usr = src_usr;
3786 UPDATE acq.fund_allocation SET allocator = dest_usr WHERE allocator = src_usr;
3787 PERFORM actor.usr_merge_rows('acq.picklist', 'owner', src_usr, dest_usr);
3788 UPDATE acq.purchase_order SET owner = dest_usr WHERE owner = src_usr;
3789 UPDATE acq.po_note SET creator = dest_usr WHERE creator = src_usr;
3790 UPDATE acq.po_note SET editor = dest_usr WHERE editor = src_usr;
3791 UPDATE acq.lineitem_note SET creator = dest_usr WHERE creator = src_usr;
3792 UPDATE acq.lineitem_note SET editor = dest_usr WHERE editor = src_usr;
3793 UPDATE acq.lineitem_usr_attr_definition SET usr = dest_usr WHERE usr = src_usr;
3796 UPDATE asset.copy SET creator = dest_usr WHERE creator = src_usr;
3797 UPDATE asset.copy SET editor = dest_usr WHERE editor = src_usr;
3798 UPDATE asset.copy_note SET creator = dest_usr WHERE creator = src_usr;
3799 UPDATE asset.call_number SET creator = dest_usr WHERE creator = src_usr;
3800 UPDATE asset.call_number SET editor = dest_usr WHERE editor = src_usr;
3801 UPDATE asset.call_number_note SET creator = dest_usr WHERE creator = src_usr;
3804 UPDATE serial.record_entry SET creator = dest_usr WHERE creator = src_usr;
3805 UPDATE serial.record_entry SET editor = dest_usr WHERE editor = src_usr;
3808 -- It's not uncommon to define the reporter schema in a replica
3809 -- DB only, so don't assume these tables exist in the write DB.
3811 PERFORM actor.usr_merge_rows('reporter.template', 'owner', src_usr, dest_usr);
3812 EXCEPTION WHEN undefined_table THEN
3816 PERFORM actor.usr_merge_rows('reporter.report', 'owner', src_usr, dest_usr);
3817 EXCEPTION WHEN undefined_table THEN
3821 PERFORM actor.usr_merge_rows('reporter.schedule', 'runner', src_usr, dest_usr);
3822 EXCEPTION WHEN undefined_table THEN
3826 PERFORM actor.usr_merge_rows('reporter.template_folder', 'owner', src_usr, dest_usr);
3827 EXCEPTION WHEN undefined_table THEN
3831 PERFORM actor.usr_merge_rows('reporter.report_folder', 'owner', src_usr, dest_usr);
3832 EXCEPTION WHEN undefined_table THEN
3836 PERFORM actor.usr_merge_rows('reporter.output_folder', 'owner', src_usr, dest_usr);
3837 EXCEPTION WHEN undefined_table THEN
3841 -- Finally, delete the source user
3842 DELETE FROM actor.usr WHERE id = src_usr;
3845 $$ LANGUAGE plpgsql;
3847 COMMENT ON FUNCTION actor.usr_merge(INT, INT, BOOLEAN, BOOLEAN, BOOLEAN) IS $$
3849 * Merges all user date from src_usr to dest_usr. When collisions occur,
3850 * keep dest_usr's data and delete src_usr's data.
3856 CREATE OR REPLACE FUNCTION actor.approve_pending_address(pending_id INT) RETURNS BIGINT AS $$
3860 SELECT INTO old_id replaces FROM actor.usr_address where id = pending_id;
3861 IF old_id IS NULL THEN
3862 UPDATE actor.usr_address SET pending = 'f' WHERE id = pending_id;
3865 -- address replaces an existing address
3866 DELETE FROM actor.usr_address WHERE id = -old_id;
3867 UPDATE actor.usr_address SET id = -id WHERE id = old_id;
3868 UPDATE actor.usr_address SET replaces = NULL, id = old_id, pending = 'f' WHERE id = pending_id;
3871 $$ LANGUAGE plpgsql;
3873 COMMENT ON FUNCTION actor.approve_pending_address(INT) IS $$
3875 * Replaces an address with a pending address. This is done by giving the pending
3876 * address the ID of the old address. The replaced address is retained with -id.
3883 ---------!!!!!!!!!!!!!!!!!!!!!!---------------
3884 -- Must go after COMMIT!!
3885 ---------!!!!!!!!!!!!!!!!!!!!!!---------------
3887 INSERT INTO config.z3950_source (name, label, host, port, db, auth)
3888 VALUES ('biblios', oils_i18n_gettext('biblios','biblios.net', 'czs', 'label'), 'z3950.biblios.net', 210, 'bibliographic', FALSE);
3891 INSERT INTO config.z3950_attr (id, source, name, label, code, format)
3892 VALUES (19, 'biblios','tcn', oils_i18n_gettext(19, 'Title Control Number', 'cza', 'label'), 12, 1);
3893 INSERT INTO config.z3950_attr (id, source, name, label, code, format)
3894 VALUES (20, 'biblios', 'isbn', oils_i18n_gettext(20, 'ISBN', 'cza', 'label'), 7, 6);
3895 INSERT INTO config.z3950_attr (id, source, name, label, code, format)
3896 VALUES (21, 'biblios', 'lccn', oils_i18n_gettext(21, 'LCCN', 'cza', 'label'), 9, 1);
3897 INSERT INTO config.z3950_attr (id, source, name, label, code, format)
3898 VALUES (22, 'biblios', 'author', oils_i18n_gettext(22, 'Author', 'cza', 'label'), 1003, 6);
3899 INSERT INTO config.z3950_attr (id, source, name, label, code, format)
3900 VALUES (23, 'biblios', 'title', oils_i18n_gettext(23, 'Title', 'cza', 'label'), 4, 6);
3901 INSERT INTO config.z3950_attr (id, source, name, label, code, format)
3902 VALUES (24, 'biblios', 'issn', oils_i18n_gettext(24, 'ISSN', 'cza', 'label'), 8, 1);
3903 INSERT INTO config.z3950_attr (id, source, name, label, code, format)
3904 VALUES (25, 'biblios', 'publisher', oils_i18n_gettext(25, 'Publisher', 'cza', 'label'), 1018, 6);
3905 INSERT INTO config.z3950_attr (id, source, name, label, code, format)
3906 VALUES (26, 'biblios', 'pubdate', oils_i18n_gettext(26, 'Publication Date', 'cza', 'label'), 31, 1);
3907 INSERT INTO config.z3950_attr (id, source, name, label, code, format)
3908 VALUES (27, 'biblios', 'item_type', oils_i18n_gettext(27, 'Item Type', 'cza', 'label'), 1001, 1);
3910 INSERT INTO permission.perm_list (code, description) VALUES ('DELETE_FUNDING_SOURCE', 'Allow a user to delete a funding source');
3911 INSERT INTO permission.perm_list (code, description) VALUES ('VIEW_FUNDING_SOURCE', 'Allow a user to view a funding source');
3912 INSERT INTO permission.perm_list (code, description) VALUES ('UPDATE_FUNDING_SOURCE', 'Allow a user to update a funding source');
3913 INSERT INTO permission.perm_list (code, description) VALUES ('CREATE_FUND', 'Allow a user to create a new fund');
3914 INSERT INTO permission.perm_list (code, description) VALUES ('DELETE_FUND', 'Allow a user to delete a fund');
3915 INSERT INTO permission.perm_list (code, description) VALUES ('VIEW_FUND', 'Allow a user to view a fund');
3916 INSERT INTO permission.perm_list (code, description) VALUES ('UPDATE_FUND', 'Allow a user to update a fund');
3917 INSERT INTO permission.perm_list (code, description) VALUES ('CREATE_FUND_ALLOCATION', 'Allow a user to create a new fund allocation');
3918 INSERT INTO permission.perm_list (code, description) VALUES ('DELETE_FUND_ALLOCATION', 'Allow a user to delete a fund allocation');
3919 INSERT INTO permission.perm_list (code, description) VALUES ('VIEW_FUND_ALLOCATION', 'Allow a user to view a fund allocation');
3920 INSERT INTO permission.perm_list (code, description) VALUES ('UPDATE_FUND_ALLOCATION', 'Allow a user to update a fund allocation');
3921 INSERT INTO permission.perm_list (code, description) VALUES ('GENERAL_ACQ', 'Lowest level permission required to access the ACQ interface');
3922 INSERT INTO permission.perm_list (code, description) VALUES ('CREATE_PROVIDER', 'Allow a user to create a new provider');
3923 INSERT INTO permission.perm_list (code, description) VALUES ('DELETE_PROVIDER', 'Allow a user to delate a provider');
3924 INSERT INTO permission.perm_list (code, description) VALUES ('VIEW_PROVIDER', 'Allow a user to view a provider');
3925 INSERT INTO permission.perm_list (code, description) VALUES ('UPDATE_PROVIDER', 'Allow a user to update a provider');
3926 INSERT INTO permission.perm_list (code, description) VALUES ('ADMIN_FUNDING_SOURCE', 'Allow a user to create/view/update/delete a funding source');
3927 INSERT INTO permission.perm_list (code, description) VALUES ('ADMIN_FUND', 'Allow a user to create/view/update/delete a fund');
3928 INSERT INTO permission.perm_list (code, description) VALUES ('MANAGE_FUNDING_SOURCE', 'Allow a user to view/credit/debit a funding source');
3929 INSERT INTO permission.perm_list (code, description) VALUES ('MANAGE_FUND', 'Allow a user to view/credit/debit a fund');
3930 INSERT INTO permission.perm_list (code, description) VALUES ('CREATE_PICKLIST', 'Allows a user to create a picklist');
3931 INSERT INTO permission.perm_list (code, description) VALUES ('ADMIN_PROVIDER', 'Allow a user to create/view/update/delete a provider');
3932 INSERT INTO permission.perm_list (code, description) VALUES ('MANAGE_PROVIDER', 'Allow a user to view and purchase from a provider');
3933 INSERT INTO permission.perm_list (code, description) VALUES ('VIEW_PICKLIST', 'Allow a user to view another users picklist');
3934 INSERT INTO permission.perm_list (code, description) VALUES ('DELETE_RECORD', 'Allow a staff member to directly remove a bibliographic record');
3935 INSERT INTO permission.perm_list (code, description) VALUES ('ADMIN_CURRENCY_TYPE', 'Allow a user to create/view/update/delete a currency_type');
3936 INSERT INTO permission.perm_list (code, description) VALUES ('MARK_BAD_DEBT', 'Allow a user to mark a transaction as bad (unrecoverable) debt');
3937 INSERT INTO permission.perm_list (code, description) VALUES ('VIEW_BILLING_TYPE', 'Allow a user to view billing types');
3938 INSERT INTO permission.perm_list (code, description) VALUES ('MARK_ITEM_AVAILABLE', 'Allow a user to mark an item status as ''available''');
3939 INSERT INTO permission.perm_list (code, description) VALUES ('MARK_ITEM_CHECKED_OUT', 'Allow a user to mark an item status as ''checked out''');
3940 INSERT INTO permission.perm_list (code, description) VALUES ('MARK_ITEM_BINDERY', 'Allow a user to mark an item status as ''bindery''');
3941 INSERT INTO permission.perm_list (code, description) VALUES ('MARK_ITEM_LOST', 'Allow a user to mark an item status as ''lost''');
3942 INSERT INTO permission.perm_list (code, description) VALUES ('MARK_ITEM_MISSING', 'Allow a user to mark an item status as ''missing''');
3943 INSERT INTO permission.perm_list (code, description) VALUES ('MARK_ITEM_IN_PROCESS', 'Allow a user to mark an item status as ''in process''');
3944 INSERT INTO permission.perm_list (code, description) VALUES ('MARK_ITEM_IN_TRANSIT', 'Allow a user to mark an item status as ''in transit''');
3945 INSERT INTO permission.perm_list (code, description) VALUES ('MARK_ITEM_RESHELVING', 'Allow a user to mark an item status as ''reshelving''');
3946 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''');
3947 INSERT INTO permission.perm_list (code, description) VALUES ('MARK_ITEM_ON_ORDER', 'Allow a user to mark an item status as ''on order''');
3948 INSERT INTO permission.perm_list (code, description) VALUES ('MARK_ITEM_ILL', 'Allow a user to mark an item status as ''inter-library loan''');
3949 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');
3950 INSERT INTO permission.perm_list (code, description) VALUES ('CREATE_PURCHASE_ORDER', 'Allows a user to create a purchase order');
3951 INSERT INTO permission.perm_list (code, description) VALUES ('VIEW_PURCHASE_ORDER', 'Allows a user to view a purchase order');
3952 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');
3953 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');
3954 INSERT INTO permission.perm_list (code, description) VALUES ('VIEW_ORG_SETTINGS','Allows a user to view all org settings at the specified level');
3955 INSERT INTO permission.perm_list (code, description) VALUES ('CREATE_MFHD_RECORD', 'Allows a user to create a new MFHD record');
3956 INSERT INTO permission.perm_list (code, description) VALUES ('UPDATE_MFHD_RECORD', 'Allows a user to update an MFHD record');
3957 INSERT INTO permission.perm_list (code, description) VALUES ('DELETE_MFHD_RECORD', 'Allows a user to delete an MFHD record');
3958 INSERT INTO permission.perm_list (code, description) VALUES ('CREATE_FUNDING_SOURCE', 'Allow a user to create a new funding source');
3960 INSERT INTO permission.perm_list (code) VALUES ('CREATE_ACQ_FUNDING_SOURCE');
3961 INSERT INTO permission.perm_list (code) VALUES ('DELETE_ACQ_FUNDING_SOURCE');
3962 INSERT INTO permission.perm_list (code) VALUES ('UPDATE_ACQ_FUNDING_SOURCE');
3963 INSERT INTO permission.perm_list (code) VALUES ('VIEW_ACQ_FUNDING_SOURCE');
3964 INSERT INTO permission.perm_list (code) VALUES ('UPDATE_ORG_UNIT_SETTING.global.password_regex');
3965 INSERT INTO permission.perm_list (code) VALUES ('UPDATE_ORG_UNIT_SETTING.global.juvenile_age_threshold');
3966 INSERT INTO permission.perm_list (code) VALUES ('UPDATE_ORG_UNIT_SETTING.patron.password.use_phone');
3968 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');
3971 SELECT SETVAL('permission.perm_list_id_seq'::TEXT, (SELECT MAX(id) FROM permission.perm_list));
3973 ALTER TABLE auditor.action_hold_request_history ADD COLUMN cancel_cause INT;
3974 ALTER TABLE auditor.action_hold_request_history ADD COLUMN cancel_note TEXT;
3976 CREATE OR REPLACE FUNCTION tmp_populate_p_b_bt () RETURNS BOOL AS $$
3981 SELECT DISTINCT xact
3987 INSERT INTO money.materialized_payment_by_billing_type (
3988 xact, payment, billing, payment_ts, billing_ts,
3989 payment_type, billing_type, amount, billing_ou, payment_ou
3990 ) SELECT xact, payment, billing, payment_ts, billing_ts,
3991 payment_type, billing_type, amount, billing_ou, payment_ou
3992 FROM money.payment_by_billing_type( p.xact );
3998 $$ LANGUAGE PLPGSQL;
4000 SELECT tmp_populate_p_b_bt();
4002 DROP FUNCTION tmp_populate_p_b_bt ();
4005 UPDATE config.xml_transform SET xslt=$$<xsl:stylesheet xmlns="http://www.loc.gov/mods/v3" xmlns:marc="http://www.loc.gov/MARC21/slim"
4006 xmlns:xlink="http://www.w3.org/1999/xlink" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
4007 exclude-result-prefixes="xlink marc" version="1.0">
4008 <xsl:output encoding="UTF-8" indent="yes" method="xml"/>
4010 <xsl:variable name="ascii">
4011 <xsl:text> !"#$%&'()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^_`abcdefghijklmnopqrstuvwxyz{|}~</xsl:text>
4014 <xsl:variable name="latin1">
4015 <xsl:text> ¡¢£¤¥¦§¨©ª«¬®¯°±²³´µ¶·¸¹º»¼½¾¿ÀÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖ×ØÙÚÛÜÝÞßàáâãäåæçèéêëìíîïðñòóôõö÷øùúûüýþÿ</xsl:text>
4017 <!-- Characters that usually don't need to be escaped -->
4018 <xsl:variable name="safe">
4019 <xsl:text>!'()*-.0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ_abcdefghijklmnopqrstuvwxyz~</xsl:text>
4022 <xsl:variable name="hex">0123456789ABCDEF</xsl:variable>
4026 <!--MARC21slim2MODS3-3.xsl
4027 Revision 1.27 - Mapped 648 to <subject> 2009/03/13 tmee
4028 Revision 1.26 - Added subfield $s mapping for 130/240/730 2008/10/16 tmee
4029 Revision 1.25 - Mapped 040e to <descriptiveStandard> and Leader/18 to <descriptive standard>aacr2 2008/09/18 tmee
4030 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
4031 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
4032 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
4033 Revision 1.21 - Mapped 856 ind2=1 or ind2=2 to <relatedItem><location><url> 2008/07/03 tmee
4034 Revision 1.20 - Added genre w/@auth="contents of 2" and type= "musical composition" 2008/07/01 tmee
4035 Revision 1.19 - Added genre offprint for 008/24+ BK code 2 2008/07/01 tmee
4036 Revision 1.18 - Added xlink/uri for subfield 0 for 130/240/730, 100/700, 110/710, 111/711 2008/06/26 tmee
4037 Revision 1.17 - Added mapping of 662 2008/05/14 tmee
4038 Revision 1.16 - Changed @authority from "marc" to "marcgt" for 007 and 008 codes mapped to a term in <genre> 2007/07/10 tmee
4039 Revision 1.15 - For field 630, moved call to part template outside title element 2007/07/10 tmee
4040 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
4041 Revision 1.13 - Changed order of output under cartographics to reflect schema 2006/11/28 tmee
4042 Revision 1.12 - Updated to reflect MODS 3.2 Mapping 2006/10/11 tmee
4043 Revision 1.11 - The attribute objectPart moved from <languageTerm> to <language> 2006/04/08 jrad
4044 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
4045 Revision 1.9 - Subfield $y was added to field 242 2004/09/02 10:57 jrad
4046 Revision 1.8 - Subject chopPunctuation expanded and attribute fixes 2004/08/12 jrad
4047 Revision 1.7 - 2004/03/25 08:29 jrad
4048 Revision 1.6 - Various validation fixes 2004/02/20 ntra
4049 Revision 1.5 - MODS2 to MODS3 updates, language unstacking and de-duping, chopPunctuation expanded 2003/10/02 16:18:58 ntra
4050 Revision 1.3 - Additional Changes not related to MODS Version 2.0 by ntra
4051 Revision 1.2 - Added Log Comment 2003/03/24 19:37:42 ckeith
4053 <xsl:template match="/">
4055 <xsl:when test="//marc:collection">
4056 <modsCollection xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
4057 xsi:schemaLocation="http://www.loc.gov/mods/v3 http://www.loc.gov/standards/mods/v3/mods-3-2.xsd">
4058 <xsl:for-each select="//marc:collection/marc:record">
4059 <mods version="3.3">
4060 <xsl:call-template name="marcRecord"/>
4066 <mods xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" version="3.3"
4067 xsi:schemaLocation="http://www.loc.gov/mods/v3 http://www.loc.gov/standards/mods/v3/mods-3-2.xsd">
4068 <xsl:for-each select="//marc:record">
4069 <xsl:call-template name="marcRecord"/>
4075 <xsl:template name="marcRecord">
4076 <xsl:variable name="leader" select="marc:leader"/>
4077 <xsl:variable name="leader6" select="substring($leader,7,1)"/>
4078 <xsl:variable name="leader7" select="substring($leader,8,1)"/>
4079 <xsl:variable name="controlField008" select="marc:controlfield[@tag='008']"/>
4080 <xsl:variable name="typeOf008">
4082 <xsl:when test="$leader6='a'">
4085 test="$leader7='a' or $leader7='c' or $leader7='d' or $leader7='m'">BK</xsl:when>
4086 <xsl:when test="$leader7='b' or $leader7='i' or $leader7='s'">SE</xsl:when>
4089 <xsl:when test="$leader6='t'">BK</xsl:when>
4090 <xsl:when test="$leader6='p'">MM</xsl:when>
4091 <xsl:when test="$leader6='m'">CF</xsl:when>
4092 <xsl:when test="$leader6='e' or $leader6='f'">MP</xsl:when>
4093 <xsl:when test="$leader6='g' or $leader6='k' or $leader6='o' or $leader6='r'">VM</xsl:when>
4094 <xsl:when test="$leader6='c' or $leader6='d' or $leader6='i' or $leader6='j'"
4098 <xsl:for-each select="marc:datafield[@tag='245']">
4100 <xsl:variable name="title">
4102 <xsl:when test="marc:subfield[@code='b']">
4103 <xsl:call-template name="specialSubfieldSelect">
4104 <xsl:with-param name="axis">b</xsl:with-param>
4105 <xsl:with-param name="beforeCodes">afgk</xsl:with-param>
4106 </xsl:call-template>
4109 <xsl:call-template name="subfieldSelect">
4110 <xsl:with-param name="codes">abfgk</xsl:with-param>
4111 </xsl:call-template>
4115 <xsl:variable name="titleChop">
4116 <xsl:call-template name="chopPunctuation">
4117 <xsl:with-param name="chopString">
4118 <xsl:value-of select="$title"/>
4120 </xsl:call-template>
4123 <xsl:when test="@ind2>0">
4125 <xsl:value-of select="substring($titleChop,1,@ind2)"/>
4128 <xsl:value-of select="substring($titleChop,@ind2+1)"/>
4133 <xsl:value-of select="$titleChop"/>
4137 <xsl:if test="marc:subfield[@code='b']">
4139 <xsl:call-template name="chopPunctuation">
4140 <xsl:with-param name="chopString">
4141 <xsl:call-template name="specialSubfieldSelect">
4142 <xsl:with-param name="axis">b</xsl:with-param>
4143 <xsl:with-param name="anyCodes">b</xsl:with-param>
4144 <xsl:with-param name="afterCodes">afgk</xsl:with-param>
4145 </xsl:call-template>
4147 </xsl:call-template>
4150 <xsl:call-template name="part"/>
4153 <xsl:for-each select="marc:datafield[@tag='210']">
4154 <titleInfo type="abbreviated">
4156 <xsl:call-template name="chopPunctuation">
4157 <xsl:with-param name="chopString">
4158 <xsl:call-template name="subfieldSelect">
4159 <xsl:with-param name="codes">a</xsl:with-param>
4160 </xsl:call-template>
4162 </xsl:call-template>
4164 <xsl:call-template name="subtitle"/>
4167 <xsl:for-each select="marc:datafield[@tag='242']">
4168 <titleInfo type="translated">
4169 <!--09/01/04 Added subfield $y-->
4170 <xsl:for-each select="marc:subfield[@code='y']">
4171 <xsl:attribute name="lang">
4172 <xsl:value-of select="text()"/>
4175 <xsl:for-each select="marc:subfield[@code='i']">
4176 <xsl:attribute name="displayLabel">
4177 <xsl:value-of select="text()"/>
4181 <xsl:call-template name="chopPunctuation">
4182 <xsl:with-param name="chopString">
4183 <xsl:call-template name="subfieldSelect">
4184 <!-- 1/04 removed $h, b -->
4185 <xsl:with-param name="codes">a</xsl:with-param>
4186 </xsl:call-template>
4188 </xsl:call-template>
4191 <xsl:call-template name="subtitle"/>
4192 <xsl:call-template name="part"/>
4195 <xsl:for-each select="marc:datafield[@tag='246']">
4196 <titleInfo type="alternative">
4197 <xsl:for-each select="marc:subfield[@code='i']">
4198 <xsl:attribute name="displayLabel">
4199 <xsl:value-of select="text()"/>
4203 <xsl:call-template name="chopPunctuation">
4204 <xsl:with-param name="chopString">
4205 <xsl:call-template name="subfieldSelect">
4206 <!-- 1/04 removed $h, $b -->
4207 <xsl:with-param name="codes">af</xsl:with-param>
4208 </xsl:call-template>
4210 </xsl:call-template>
4212 <xsl:call-template name="subtitle"/>
4213 <xsl:call-template name="part"/>
4217 select="marc:datafield[@tag='130']|marc:datafield[@tag='240']|marc:datafield[@tag='730'][@ind2!='2']">
4218 <titleInfo type="uniform">
4220 <!-- deleted uri for subfield 0
4221 <xsl:call-template name="uri"/>
4224 <xsl:variable name="str">
4225 <xsl:for-each select="marc:subfield">
4227 test="(contains('adfklmors',@code) and (not(../marc:subfield[@code='n' or @code='p']) or (following-sibling::marc:subfield[@code='n' or @code='p'])))">
4228 <xsl:value-of select="text()"/>
4229 <xsl:text> </xsl:text>
4233 <xsl:call-template name="chopPunctuation">
4234 <xsl:with-param name="chopString">
4235 <xsl:value-of select="substring($str,1,string-length($str)-1)"/>
4237 </xsl:call-template>
4239 <xsl:call-template name="part"/>
4242 <xsl:for-each select="marc:datafield[@tag='740'][@ind2!='2']">
4243 <titleInfo type="alternative">
4245 <xsl:call-template name="chopPunctuation">
4246 <xsl:with-param name="chopString">
4247 <xsl:call-template name="subfieldSelect">
4248 <xsl:with-param name="codes">ah</xsl:with-param>
4249 </xsl:call-template>
4251 </xsl:call-template>
4253 <xsl:call-template name="part"/>
4256 <xsl:for-each select="marc:datafield[@tag='100']">
4257 <name type="personal">
4259 <!-- deleted uri for subfield 0
4260 <xsl:call-template name="uri"/>
4263 <xsl:call-template name="nameABCDQ"/>
4264 <xsl:call-template name="affiliation"/>
4266 <roleTerm authority="marcrelator" type="text">creator</roleTerm>
4268 <xsl:call-template name="role"/>
4271 <xsl:for-each select="marc:datafield[@tag='110']">
4272 <name type="corporate">
4274 <!-- deleted uri for subfield 0
4275 <xsl:call-template name="uri"/>
4278 <xsl:call-template name="nameABCDN"/>
4280 <roleTerm authority="marcrelator" type="text">creator</roleTerm>
4282 <xsl:call-template name="role"/>
4285 <xsl:for-each select="marc:datafield[@tag='111']">
4286 <name type="conference">
4288 <!-- deleted uri for subfield 0
4289 <xsl:call-template name="uri"/>
4292 <xsl:call-template name="nameACDEQ"/>
4294 <roleTerm authority="marcrelator" type="text">creator</roleTerm>
4296 <xsl:call-template name="role"/>
4299 <xsl:for-each select="marc:datafield[@tag='700'][not(marc:subfield[@code='t'])]">
4300 <name type="personal">
4302 <!-- deleted uri for subfield 0
4303 <xsl:call-template name="uri"/>
4306 <xsl:call-template name="nameABCDQ"/>
4307 <xsl:call-template name="affiliation"/>
4308 <xsl:call-template name="role"/>
4311 <xsl:for-each select="marc:datafield[@tag='710'][not(marc:subfield[@code='t'])]">
4312 <name type="corporate">
4314 <!-- deleted uri for subfield 0
4315 <xsl:call-template name="uri"/>
4318 <xsl:call-template name="nameABCDN"/>
4319 <xsl:call-template name="role"/>
4322 <xsl:for-each select="marc:datafield[@tag='711'][not(marc:subfield[@code='t'])]">
4323 <name type="conference">
4325 <!-- deleted uri for subfield 0
4326 <xsl:call-template name="uri"/>
4329 <xsl:call-template name="nameACDEQ"/>
4330 <xsl:call-template name="role"/>
4333 <xsl:for-each select="marc:datafield[@tag='720'][not(marc:subfield[@code='t'])]">
4335 <xsl:if test="@ind1=1">
4336 <xsl:attribute name="type">
4337 <xsl:text>personal</xsl:text>
4341 <xsl:value-of select="marc:subfield[@code='a']"/>
4343 <xsl:call-template name="role"/>
4347 <xsl:if test="$leader7='c'">
4348 <xsl:attribute name="collection">yes</xsl:attribute>
4350 <xsl:if test="$leader6='d' or $leader6='f' or $leader6='p' or $leader6='t'">
4351 <xsl:attribute name="manuscript">yes</xsl:attribute>
4354 <xsl:when test="$leader6='a' or $leader6='t'">text</xsl:when>
4355 <xsl:when test="$leader6='e' or $leader6='f'">cartographic</xsl:when>
4356 <xsl:when test="$leader6='c' or $leader6='d'">notated music</xsl:when>
4357 <xsl:when test="$leader6='i'">sound recording-nonmusical</xsl:when>
4358 <xsl:when test="$leader6='j'">sound recording-musical</xsl:when>
4359 <xsl:when test="$leader6='k'">still image</xsl:when>
4360 <xsl:when test="$leader6='g'">moving image</xsl:when>
4361 <xsl:when test="$leader6='r'">three dimensional object</xsl:when>
4362 <xsl:when test="$leader6='m'">software, multimedia</xsl:when>
4363 <xsl:when test="$leader6='p'">mixed material</xsl:when>
4366 <xsl:if test="substring($controlField008,26,1)='d'">
4367 <genre authority="marcgt">globe</genre>
4370 test="marc:controlfield[@tag='007'][substring(text(),1,1)='a'][substring(text(),2,1)='r']">
4371 <genre authority="marcgt">remote-sensing image</genre>
4373 <xsl:if test="$typeOf008='MP'">
4374 <xsl:variable name="controlField008-25" select="substring($controlField008,26,1)"/>
4377 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']">
4378 <genre authority="marcgt">map</genre>
4381 test="$controlField008-25='e' or marc:controlfield[@tag=007][substring(text(),1,1)='a'][substring(text(),2,1)='d']">
4382 <genre authority="marcgt">atlas</genre>
4386 <xsl:if test="$typeOf008='SE'">
4387 <xsl:variable name="controlField008-21" select="substring($controlField008,22,1)"/>
4389 <xsl:when test="$controlField008-21='d'">
4390 <genre authority="marcgt">database</genre>
4392 <xsl:when test="$controlField008-21='l'">
4393 <genre authority="marcgt">loose-leaf</genre>
4395 <xsl:when test="$controlField008-21='m'">
4396 <genre authority="marcgt">series</genre>
4398 <xsl:when test="$controlField008-21='n'">
4399 <genre authority="marcgt">newspaper</genre>
4401 <xsl:when test="$controlField008-21='p'">
4402 <genre authority="marcgt">periodical</genre>
4404 <xsl:when test="$controlField008-21='w'">
4405 <genre authority="marcgt">web site</genre>
4409 <xsl:if test="$typeOf008='BK' or $typeOf008='SE'">
4410 <xsl:variable name="controlField008-24" select="substring($controlField008,25,4)"/>
4412 <xsl:when test="contains($controlField008-24,'a')">
4413 <genre authority="marcgt">abstract or summary</genre>
4415 <xsl:when test="contains($controlField008-24,'b')">
4416 <genre authority="marcgt">bibliography</genre>
4418 <xsl:when test="contains($controlField008-24,'c')">
4419 <genre authority="marcgt">catalog</genre>
4421 <xsl:when test="contains($controlField008-24,'d')">
4422 <genre authority="marcgt">dictionary</genre>
4424 <xsl:when test="contains($controlField008-24,'e')">
4425 <genre authority="marcgt">encyclopedia</genre>
4427 <xsl:when test="contains($controlField008-24,'f')">
4428 <genre authority="marcgt">handbook</genre>
4430 <xsl:when test="contains($controlField008-24,'g')">
4431 <genre authority="marcgt">legal article</genre>
4433 <xsl:when test="contains($controlField008-24,'i')">
4434 <genre authority="marcgt">index</genre>
4436 <xsl:when test="contains($controlField008-24,'k')">
4437 <genre authority="marcgt">discography</genre>
4439 <xsl:when test="contains($controlField008-24,'l')">
4440 <genre authority="marcgt">legislation</genre>
4442 <xsl:when test="contains($controlField008-24,'m')">
4443 <genre authority="marcgt">theses</genre>
4445 <xsl:when test="contains($controlField008-24,'n')">
4446 <genre authority="marcgt">survey of literature</genre>
4448 <xsl:when test="contains($controlField008-24,'o')">
4449 <genre authority="marcgt">review</genre>
4451 <xsl:when test="contains($controlField008-24,'p')">
4452 <genre authority="marcgt">programmed text</genre>
4454 <xsl:when test="contains($controlField008-24,'q')">
4455 <genre authority="marcgt">filmography</genre>
4457 <xsl:when test="contains($controlField008-24,'r')">
4458 <genre authority="marcgt">directory</genre>
4460 <xsl:when test="contains($controlField008-24,'s')">
4461 <genre authority="marcgt">statistics</genre>
4463 <xsl:when test="contains($controlField008-24,'t')">
4464 <genre authority="marcgt">technical report</genre>
4466 <xsl:when test="contains($controlField008-24,'v')">
4467 <genre authority="marcgt">legal case and case notes</genre>
4469 <xsl:when test="contains($controlField008-24,'w')">
4470 <genre authority="marcgt">law report or digest</genre>
4472 <xsl:when test="contains($controlField008-24,'z')">
4473 <genre authority="marcgt">treaty</genre>
4476 <xsl:variable name="controlField008-29" select="substring($controlField008,30,1)"/>
4478 <xsl:when test="$controlField008-29='1'">
4479 <genre authority="marcgt">conference publication</genre>
4483 <xsl:if test="$typeOf008='CF'">
4484 <xsl:variable name="controlField008-26" select="substring($controlField008,27,1)"/>
4486 <xsl:when test="$controlField008-26='a'">
4487 <genre authority="marcgt">numeric data</genre>
4489 <xsl:when test="$controlField008-26='e'">
4490 <genre authority="marcgt">database</genre>
4492 <xsl:when test="$controlField008-26='f'">
4493 <genre authority="marcgt">font</genre>
4495 <xsl:when test="$controlField008-26='g'">
4496 <genre authority="marcgt">game</genre>
4500 <xsl:if test="$typeOf008='BK'">
4501 <xsl:if test="substring($controlField008,25,1)='j'">
4502 <genre authority="marcgt">patent</genre>
4504 <xsl:if test="substring($controlField008,25,1)='2'">
4505 <genre authority="marcgt">offprint</genre>
4507 <xsl:if test="substring($controlField008,31,1)='1'">
4508 <genre authority="marcgt">festschrift</genre>
4510 <xsl:variable name="controlField008-34" select="substring($controlField008,35,1)"/>
4512 test="$controlField008-34='a' or $controlField008-34='b' or $controlField008-34='c' or $controlField008-34='d'">
4513 <genre authority="marcgt">biography</genre>
4515 <xsl:variable name="controlField008-33" select="substring($controlField008,34,1)"/>
4517 <xsl:when test="$controlField008-33='e'">
4518 <genre authority="marcgt">essay</genre>
4520 <xsl:when test="$controlField008-33='d'">
4521 <genre authority="marcgt">drama</genre>
4523 <xsl:when test="$controlField008-33='c'">
4524 <genre authority="marcgt">comic strip</genre>
4526 <xsl:when test="$controlField008-33='l'">
4527 <genre authority="marcgt">fiction</genre>
4529 <xsl:when test="$controlField008-33='h'">
4530 <genre authority="marcgt">humor, satire</genre>
4532 <xsl:when test="$controlField008-33='i'">
4533 <genre authority="marcgt">letter</genre>
4535 <xsl:when test="$controlField008-33='f'">
4536 <genre authority="marcgt">novel</genre>
4538 <xsl:when test="$controlField008-33='j'">
4539 <genre authority="marcgt">short story</genre>
4541 <xsl:when test="$controlField008-33='s'">
4542 <genre authority="marcgt">speech</genre>
4546 <xsl:if test="$typeOf008='MU'">
4547 <xsl:variable name="controlField008-30-31" select="substring($controlField008,31,2)"/>
4548 <xsl:if test="contains($controlField008-30-31,'b')">
4549 <genre authority="marcgt">biography</genre>
4551 <xsl:if test="contains($controlField008-30-31,'c')">
4552 <genre authority="marcgt">conference publication</genre>
4554 <xsl:if test="contains($controlField008-30-31,'d')">
4555 <genre authority="marcgt">drama</genre>
4557 <xsl:if test="contains($controlField008-30-31,'e')">
4558 <genre authority="marcgt">essay</genre>
4560 <xsl:if test="contains($controlField008-30-31,'f')">
4561 <genre authority="marcgt">fiction</genre>
4563 <xsl:if test="contains($controlField008-30-31,'o')">
4564 <genre authority="marcgt">folktale</genre>
4566 <xsl:if test="contains($controlField008-30-31,'h')">
4567 <genre authority="marcgt">history</genre>
4569 <xsl:if test="contains($controlField008-30-31,'k')">
4570 <genre authority="marcgt">humor, satire</genre>
4572 <xsl:if test="contains($controlField008-30-31,'m')">
4573 <genre authority="marcgt">memoir</genre>
4575 <xsl:if test="contains($controlField008-30-31,'p')">
4576 <genre authority="marcgt">poetry</genre>
4578 <xsl:if test="contains($controlField008-30-31,'r')">
4579 <genre authority="marcgt">rehearsal</genre>
4581 <xsl:if test="contains($controlField008-30-31,'g')">
4582 <genre authority="marcgt">reporting</genre>
4584 <xsl:if test="contains($controlField008-30-31,'s')">
4585 <genre authority="marcgt">sound</genre>
4587 <xsl:if test="contains($controlField008-30-31,'l')">
4588 <genre authority="marcgt">speech</genre>
4591 <xsl:if test="$typeOf008='VM'">
4592 <xsl:variable name="controlField008-33" select="substring($controlField008,34,1)"/>
4594 <xsl:when test="$controlField008-33='a'">
4595 <genre authority="marcgt">art original</genre>
4597 <xsl:when test="$controlField008-33='b'">
4598 <genre authority="marcgt">kit</genre>
4600 <xsl:when test="$controlField008-33='c'">
4601 <genre authority="marcgt">art reproduction</genre>
4603 <xsl:when test="$controlField008-33='d'">
4604 <genre authority="marcgt">diorama</genre>
4606 <xsl:when test="$controlField008-33='f'">
4607 <genre authority="marcgt">filmstrip</genre>
4609 <xsl:when test="$controlField008-33='g'">
4610 <genre authority="marcgt">legal article</genre>
4612 <xsl:when test="$controlField008-33='i'">
4613 <genre authority="marcgt">picture</genre>
4615 <xsl:when test="$controlField008-33='k'">
4616 <genre authority="marcgt">graphic</genre>
4618 <xsl:when test="$controlField008-33='l'">
4619 <genre authority="marcgt">technical drawing</genre>
4621 <xsl:when test="$controlField008-33='m'">
4622 <genre authority="marcgt">motion picture</genre>
4624 <xsl:when test="$controlField008-33='n'">
4625 <genre authority="marcgt">chart</genre>
4627 <xsl:when test="$controlField008-33='o'">
4628 <genre authority="marcgt">flash card</genre>
4630 <xsl:when test="$controlField008-33='p'">
4631 <genre authority="marcgt">microscope slide</genre>
4634 test="$controlField008-33='q' or marc:controlfield[@tag=007][substring(text(),1,1)='a'][substring(text(),2,1)='q']">
4635 <genre authority="marcgt">model</genre>
4637 <xsl:when test="$controlField008-33='r'">
4638 <genre authority="marcgt">realia</genre>
4640 <xsl:when test="$controlField008-33='s'">
4641 <genre authority="marcgt">slide</genre>
4643 <xsl:when test="$controlField008-33='t'">
4644 <genre authority="marcgt">transparency</genre>
4646 <xsl:when test="$controlField008-33='v'">
4647 <genre authority="marcgt">videorecording</genre>
4649 <xsl:when test="$controlField008-33='w'">
4650 <genre authority="marcgt">toy</genre>
4655 <!-- 1.20 047 genre tmee-->
4657 <xsl:for-each select="marc:datafield[@tag=047]">
4658 <genre authority="marcgt">
4659 <xsl:attribute name="authority">
4660 <xsl:value-of select="marc:subfield[@code='2']"/>
4662 <xsl:call-template name="subfieldSelect">
4663 <xsl:with-param name="codes">abcdef</xsl:with-param>
4664 <xsl:with-param name="delimeter">-</xsl:with-param>
4665 </xsl:call-template>
4668 <xsl:for-each select="marc:datafield[@tag=655]">
4669 <genre authority="marcgt">
4670 <xsl:attribute name="authority">
4671 <xsl:value-of select="marc:subfield[@code='2']"/>
4673 <xsl:call-template name="subfieldSelect">
4674 <xsl:with-param name="codes">abvxyz</xsl:with-param>
4675 <xsl:with-param name="delimeter">-</xsl:with-param>
4676 </xsl:call-template>
4680 <xsl:variable name="MARCpublicationCode"
4681 select="normalize-space(substring($controlField008,16,3))"/>
4682 <xsl:if test="translate($MARCpublicationCode,'|','')">
4685 <xsl:attribute name="type">code</xsl:attribute>
4686 <xsl:attribute name="authority">marccountry</xsl:attribute>
4687 <xsl:value-of select="$MARCpublicationCode"/>
4691 <xsl:for-each select="marc:datafield[@tag=044]/marc:subfield[@code='c']">
4694 <xsl:attribute name="type">code</xsl:attribute>
4695 <xsl:attribute name="authority">iso3166</xsl:attribute>
4696 <xsl:value-of select="."/>
4700 <xsl:for-each select="marc:datafield[@tag=260]/marc:subfield[@code='a']">
4703 <xsl:attribute name="type">text</xsl:attribute>
4704 <xsl:call-template name="chopPunctuationFront">
4705 <xsl:with-param name="chopString">
4706 <xsl:call-template name="chopPunctuation">
4707 <xsl:with-param name="chopString" select="."/>
4708 </xsl:call-template>
4710 </xsl:call-template>
4714 <xsl:for-each select="marc:datafield[@tag=046]/marc:subfield[@code='m']">
4715 <dateValid point="start">
4716 <xsl:value-of select="."/>
4719 <xsl:for-each select="marc:datafield[@tag=046]/marc:subfield[@code='n']">
4720 <dateValid point="end">
4721 <xsl:value-of select="."/>
4724 <xsl:for-each select="marc:datafield[@tag=046]/marc:subfield[@code='j']">
4726 <xsl:value-of select="."/>
4730 select="marc:datafield[@tag=260]/marc:subfield[@code='b' or @code='c' or @code='g']">
4732 <xsl:when test="@code='b'">
4734 <xsl:call-template name="chopPunctuation">
4735 <xsl:with-param name="chopString" select="."/>
4736 <xsl:with-param name="punctuation">
4737 <xsl:text>:,;/ </xsl:text>
4739 </xsl:call-template>
4742 <xsl:when test="@code='c'">
4744 <xsl:call-template name="chopPunctuation">
4745 <xsl:with-param name="chopString" select="."/>
4746 </xsl:call-template>
4749 <xsl:when test="@code='g'">
4751 <xsl:value-of select="."/>
4756 <xsl:variable name="dataField260c">
4757 <xsl:call-template name="chopPunctuation">
4758 <xsl:with-param name="chopString"
4759 select="marc:datafield[@tag=260]/marc:subfield[@code='c']"/>
4760 </xsl:call-template>
4762 <xsl:variable name="controlField008-7-10"
4763 select="normalize-space(substring($controlField008, 8, 4))"/>
4764 <xsl:variable name="controlField008-11-14"
4765 select="normalize-space(substring($controlField008, 12, 4))"/>
4766 <xsl:variable name="controlField008-6"
4767 select="normalize-space(substring($controlField008, 7, 1))"/>
4769 test="$controlField008-6='e' or $controlField008-6='p' or $controlField008-6='r' or $controlField008-6='t' or $controlField008-6='s'">
4770 <xsl:if test="$controlField008-7-10 and ($controlField008-7-10 != $dataField260c)">
4771 <dateIssued encoding="marc">
4772 <xsl:value-of select="$controlField008-7-10"/>
4777 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'">
4778 <xsl:if test="$controlField008-7-10">
4779 <dateIssued encoding="marc" point="start">
4780 <xsl:value-of select="$controlField008-7-10"/>
4785 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'">
4786 <xsl:if test="$controlField008-11-14">
4787 <dateIssued encoding="marc" point="end">
4788 <xsl:value-of select="$controlField008-11-14"/>
4792 <xsl:if test="$controlField008-6='q'">
4793 <xsl:if test="$controlField008-7-10">
4794 <dateIssued encoding="marc" point="start" qualifier="questionable">
4795 <xsl:value-of select="$controlField008-7-10"/>
4799 <xsl:if test="$controlField008-6='q'">
4800 <xsl:if test="$controlField008-11-14">
4801 <dateIssued encoding="marc" point="end" qualifier="questionable">
4802 <xsl:value-of select="$controlField008-11-14"/>
4806 <xsl:if test="$controlField008-6='t'">
4807 <xsl:if test="$controlField008-11-14">
4808 <copyrightDate encoding="marc">
4809 <xsl:value-of select="$controlField008-11-14"/>
4814 select="marc:datafield[@tag=033][@ind1=0 or @ind1=1]/marc:subfield[@code='a']">
4815 <dateCaptured encoding="iso8601">
4816 <xsl:value-of select="."/>
4819 <xsl:for-each select="marc:datafield[@tag=033][@ind1=2]/marc:subfield[@code='a'][1]">
4820 <dateCaptured encoding="iso8601" point="start">
4821 <xsl:value-of select="."/>
4824 <xsl:for-each select="marc:datafield[@tag=033][@ind1=2]/marc:subfield[@code='a'][2]">
4825 <dateCaptured encoding="iso8601" point="end">
4826 <xsl:value-of select="."/>
4829 <xsl:for-each select="marc:datafield[@tag=250]/marc:subfield[@code='a']">
4831 <xsl:value-of select="."/>
4834 <xsl:for-each select="marc:leader">
4838 test="$leader7='a' or $leader7='c' or $leader7='d' or $leader7='m'"
4839 >monographic</xsl:when>
4840 <xsl:when test="$leader7='b' or $leader7='i' or $leader7='s'"
4841 >continuing</xsl:when>
4845 <xsl:for-each select="marc:datafield[@tag=310]|marc:datafield[@tag=321]">
4847 <xsl:call-template name="subfieldSelect">
4848 <xsl:with-param name="codes">ab</xsl:with-param>
4849 </xsl:call-template>
4853 <xsl:variable name="controlField008-35-37"
4854 select="normalize-space(translate(substring($controlField008,36,3),'|#',''))"/>
4855 <xsl:if test="$controlField008-35-37">
4857 <languageTerm authority="iso639-2b" type="code">
4858 <xsl:value-of select="substring($controlField008,36,3)"/>
4862 <xsl:for-each select="marc:datafield[@tag=041]">
4864 select="marc:subfield[@code='a' or @code='b' or @code='d' or @code='e' or @code='f' or @code='g' or @code='h']">
4865 <xsl:variable name="langCodes" select="."/>
4867 <xsl:when test="../marc:subfield[@code='2']='rfc3066'">
4868 <!-- not stacked but could be repeated -->
4869 <xsl:call-template name="rfcLanguages">
4870 <xsl:with-param name="nodeNum">
4871 <xsl:value-of select="1"/>
4873 <xsl:with-param name="usedLanguages">
4876 <xsl:with-param name="controlField008-35-37">
4877 <xsl:value-of select="$controlField008-35-37"/>
4879 </xsl:call-template>
4883 <xsl:variable name="allLanguages">
4884 <xsl:copy-of select="$langCodes"/>
4886 <xsl:variable name="currentLanguage">
4887 <xsl:value-of select="substring($allLanguages,1,3)"/>
4889 <xsl:call-template name="isoLanguage">
4890 <xsl:with-param name="currentLanguage">
4891 <xsl:value-of select="substring($allLanguages,1,3)"/>
4893 <xsl:with-param name="remainingLanguages">
4895 select="substring($allLanguages,4,string-length($allLanguages)-3)"
4898 <xsl:with-param name="usedLanguages">
4899 <xsl:if test="$controlField008-35-37">
4900 <xsl:value-of select="$controlField008-35-37"/>
4903 </xsl:call-template>
4908 <xsl:variable name="physicalDescription">
4909 <!--3.2 change tmee 007/11 -->
4910 <xsl:if test="$typeOf008='CF' and marc:controlfield[@tag=007][substring(.,12,1)='a']">
4911 <digitalOrigin>reformatted digital</digitalOrigin>
4913 <xsl:if test="$typeOf008='CF' and marc:controlfield[@tag=007][substring(.,12,1)='b']">
4914 <digitalOrigin>digitized microfilm</digitalOrigin>
4916 <xsl:if test="$typeOf008='CF' and marc:controlfield[@tag=007][substring(.,12,1)='d']">
4917 <digitalOrigin>digitized other analog</digitalOrigin>
4919 <xsl:variable name="controlField008-23" select="substring($controlField008,24,1)"/>
4920 <xsl:variable name="controlField008-29" select="substring($controlField008,30,1)"/>
4921 <xsl:variable name="check008-23">
4923 test="$typeOf008='BK' or $typeOf008='MU' or $typeOf008='SE' or $typeOf008='MM'">
4924 <xsl:value-of select="true()"/>
4927 <xsl:variable name="check008-29">
4928 <xsl:if test="$typeOf008='MP' or $typeOf008='VM'">
4929 <xsl:value-of select="true()"/>
4934 test="($check008-23 and $controlField008-23='f') or ($check008-29 and $controlField008-29='f')">
4935 <form authority="marcform">braille</form>
4938 test="($controlField008-23=' ' and ($leader6='c' or $leader6='d')) or (($typeOf008='BK' or $typeOf008='SE') and ($controlField008-23=' ' or $controlField008='r'))">
4939 <form authority="marcform">print</form>
4942 test="$leader6 = 'm' or ($check008-23 and $controlField008-23='s') or ($check008-29 and $controlField008-29='s')">
4943 <form authority="marcform">electronic</form>
4946 test="($check008-23 and $controlField008-23='b') or ($check008-29 and $controlField008-29='b')">
4947 <form authority="marcform">microfiche</form>
4950 test="($check008-23 and $controlField008-23='a') or ($check008-29 and $controlField008-29='a')">
4951 <form authority="marcform">microfilm</form>
4955 <xsl:if test="marc:datafield[@tag=130]/marc:subfield[@code='h']">
4956 <form authority="gmd">
4957 <xsl:call-template name="chopBrackets">
4958 <xsl:with-param name="chopString">
4959 <xsl:value-of select="marc:datafield[@tag=130]/marc:subfield[@code='h']"
4962 </xsl:call-template>
4965 <xsl:if test="marc:datafield[@tag=240]/marc:subfield[@code='h']">
4966 <form authority="gmd">
4967 <xsl:call-template name="chopBrackets">
4968 <xsl:with-param name="chopString">
4969 <xsl:value-of select="marc:datafield[@tag=240]/marc:subfield[@code='h']"
4972 </xsl:call-template>
4975 <xsl:if test="marc:datafield[@tag=242]/marc:subfield[@code='h']">
4976 <form authority="gmd">
4977 <xsl:call-template name="chopBrackets">
4978 <xsl:with-param name="chopString">
4979 <xsl:value-of select="marc:datafield[@tag=242]/marc:subfield[@code='h']"
4982 </xsl:call-template>
4985 <xsl:if test="marc:datafield[@tag=245]/marc:subfield[@code='h']">
4986 <form authority="gmd">
4987 <xsl:call-template name="chopBrackets">
4988 <xsl:with-param name="chopString">
4989 <xsl:value-of select="marc:datafield[@tag=245]/marc:subfield[@code='h']"
4992 </xsl:call-template>
4995 <xsl:if test="marc:datafield[@tag=246]/marc:subfield[@code='h']">
4996 <form authority="gmd">
4997 <xsl:call-template name="chopBrackets">
4998 <xsl:with-param name="chopString">
4999 <xsl:value-of select="marc:datafield[@tag=246]/marc:subfield[@code='h']"
5002 </xsl:call-template>
5005 <xsl:if test="marc:datafield[@tag=730]/marc:subfield[@code='h']">
5006 <form authority="gmd">
5007 <xsl:call-template name="chopBrackets">
5008 <xsl:with-param name="chopString">
5009 <xsl:value-of select="marc:datafield[@tag=730]/marc:subfield[@code='h']"
5012 </xsl:call-template>
5015 <xsl:for-each select="marc:datafield[@tag=256]/marc:subfield[@code='a']">
5017 <xsl:value-of select="."/>
5020 <xsl:for-each select="marc:controlfield[@tag=007][substring(text(),1,1)='c']">
5022 <xsl:when test="substring(text(),14,1)='a'">
5023 <reformattingQuality>access</reformattingQuality>
5025 <xsl:when test="substring(text(),14,1)='p'">
5026 <reformattingQuality>preservation</reformattingQuality>
5028 <xsl:when test="substring(text(),14,1)='r'">
5029 <reformattingQuality>replacement</reformattingQuality>
5033 <!--3.2 change tmee 007/01 -->
5035 test="marc:controlfield[@tag=007][substring(text(),1,1)='c'][substring(text(),2,1)='b']">
5036 <form authority="smd">chip cartridge</form>
5039 test="marc:controlfield[@tag=007][substring(text(),1,1)='c'][substring(text(),2,1)='c']">
5040 <form authority="smd">computer optical disc cartridge</form>
5043 test="marc:controlfield[@tag=007][substring(text(),1,1)='c'][substring(text(),2,1)='j']">
5044 <form authority="smd">magnetic disc</form>
5047 test="marc:controlfield[@tag=007][substring(text(),1,1)='c'][substring(text(),2,1)='m']">
5048 <form authority="smd">magneto-optical disc</form>
5051 test="marc:controlfield[@tag=007][substring(text(),1,1)='c'][substring(text(),2,1)='o']">
5052 <form authority="smd">optical disc</form>
5055 test="marc:controlfield[@tag=007][substring(text(),1,1)='c'][substring(text(),2,1)='r']">
5056 <form authority="smd">remote</form>
5059 test="marc:controlfield[@tag=007][substring(text(),1,1)='c'][substring(text(),2,1)='a']">
5060 <form authority="smd">tape cartridge</form>
5063 test="marc:controlfield[@tag=007][substring(text(),1,1)='c'][substring(text(),2,1)='f']">
5064 <form authority="smd">tape cassette</form>
5067 test="marc:controlfield[@tag=007][substring(text(),1,1)='c'][substring(text(),2,1)='h']">
5068 <form authority="smd">tape reel</form>
5072 test="marc:controlfield[@tag=007][substring(text(),1,1)='d'][substring(text(),2,1)='a']">
5073 <form authority="smd">celestial globe</form>
5076 test="marc:controlfield[@tag=007][substring(text(),1,1)='d'][substring(text(),2,1)='e']">
5077 <form authority="smd">earth moon globe</form>
5080 test="marc:controlfield[@tag=007][substring(text(),1,1)='d'][substring(text(),2,1)='b']">
5081 <form authority="smd">planetary or lunar globe</form>
5084 test="marc:controlfield[@tag=007][substring(text(),1,1)='d'][substring(text(),2,1)='c']">
5085 <form authority="smd">terrestrial globe</form>
5089 test="marc:controlfield[@tag=007][substring(text(),1,1)='o'][substring(text(),2,1)='o']">
5090 <form authority="smd">kit</form>
5094 test="marc:controlfield[@tag=007][substring(text(),1,1)='a'][substring(text(),2,1)='d']">
5095 <form authority="smd">atlas</form>
5098 test="marc:controlfield[@tag=007][substring(text(),1,1)='a'][substring(text(),2,1)='g']">
5099 <form authority="smd">diagram</form>
5102 test="marc:controlfield[@tag=007][substring(text(),1,1)='a'][substring(text(),2,1)='j']">
5103 <form authority="smd">map</form>
5106 test="marc:controlfield[@tag=007][substring(text(),1,1)='a'][substring(text(),2,1)='q']">
5107 <form authority="smd">model</form>
5110 test="marc:controlfield[@tag=007][substring(text(),1,1)='a'][substring(text(),2,1)='k']">
5111 <form authority="smd">profile</form>
5114 test="marc:controlfield[@tag=007][substring(text(),1,1)='a'][substring(text(),2,1)='r']">
5115 <form authority="smd">remote-sensing image</form>
5118 test="marc:controlfield[@tag=007][substring(text(),1,1)='a'][substring(text(),2,1)='s']">
5119 <form authority="smd">section</form>
5122 test="marc:controlfield[@tag=007][substring(text(),1,1)='a'][substring(text(),2,1)='y']">
5123 <form authority="smd">view</form>
5127 test="marc:controlfield[@tag=007][substring(text(),1,1)='h'][substring(text(),2,1)='a']">
5128 <form authority="smd">aperture card</form>
5131 test="marc:controlfield[@tag=007][substring(text(),1,1)='h'][substring(text(),2,1)='e']">
5132 <form authority="smd">microfiche</form>
5135 test="marc:controlfield[@tag=007][substring(text(),1,1)='h'][substring(text(),2,1)='f']">
5136 <form authority="smd">microfiche cassette</form>
5139 test="marc:controlfield[@tag=007][substring(text(),1,1)='h'][substring(text(),2,1)='b']">
5140 <form authority="smd">microfilm cartridge</form>
5143 test="marc:controlfield[@tag=007][substring(text(),1,1)='h'][substring(text(),2,1)='c']">
5144 <form authority="smd">microfilm cassette</form>
5147 test="marc:controlfield[@tag=007][substring(text(),1,1)='h'][substring(text(),2,1)='d']">
5148 <form authority="smd">microfilm reel</form>
5151 test="marc:controlfield[@tag=007][substring(text(),1,1)='h'][substring(text(),2,1)='g']">
5152 <form authority="smd">microopaque</form>
5156 test="marc:controlfield[@tag=007][substring(text(),1,1)='m'][substring(text(),2,1)='c']">
5157 <form authority="smd">film cartridge</form>
5160 test="marc:controlfield[@tag=007][substring(text(),1,1)='m'][substring(text(),2,1)='f']">
5161 <form authority="smd">film cassette</form>
5164 test="marc:controlfield[@tag=007][substring(text(),1,1)='m'][substring(text(),2,1)='r']">
5165 <form authority="smd">film reel</form>
5169 test="marc:controlfield[@tag=007][substring(text(),1,1)='k'][substring(text(),2,1)='n']">
5170 <form authority="smd">chart</form>
5173 test="marc:controlfield[@tag=007][substring(text(),1,1)='k'][substring(text(),2,1)='c']">
5174 <form authority="smd">collage</form>
5177 test="marc:controlfield[@tag=007][substring(text(),1,1)='k'][substring(text(),2,1)='d']">
5178 <form authority="smd">drawing</form>
5181 test="marc:controlfield[@tag=007][substring(text(),1,1)='k'][substring(text(),2,1)='o']">
5182 <form authority="smd">flash card</form>
5185 test="marc:controlfield[@tag=007][substring(text(),1,1)='k'][substring(text(),2,1)='e']">
5186 <form authority="smd">painting</form>
5189 test="marc:controlfield[@tag=007][substring(text(),1,1)='k'][substring(text(),2,1)='f']">
5190 <form authority="smd">photomechanical print</form>
5193 test="marc:controlfield[@tag=007][substring(text(),1,1)='k'][substring(text(),2,1)='g']">
5194 <form authority="smd">photonegative</form>
5197 test="marc:controlfield[@tag=007][substring(text(),1,1)='k'][substring(text(),2,1)='h']">
5198 <form authority="smd">photoprint</form>
5201 test="marc:controlfield[@tag=007][substring(text(),1,1)='k'][substring(text(),2,1)='i']">
5202 <form authority="smd">picture</form>
5205 test="marc:controlfield[@tag=007][substring(text(),1,1)='k'][substring(text(),2,1)='j']">
5206 <form authority="smd">print</form>
5209 test="marc:controlfield[@tag=007][substring(text(),1,1)='k'][substring(text(),2,1)='l']">
5210 <form authority="smd">technical drawing</form>
5214 test="marc:controlfield[@tag=007][substring(text(),1,1)='q'][substring(text(),2,1)='q']">
5215 <form authority="smd">notated music</form>
5219 test="marc:controlfield[@tag=007][substring(text(),1,1)='g'][substring(text(),2,1)='d']">
5220 <form authority="smd">filmslip</form>
5223 test="marc:controlfield[@tag=007][substring(text(),1,1)='g'][substring(text(),2,1)='c']">
5224 <form authority="smd">filmstrip cartridge</form>
5227 test="marc:controlfield[@tag=007][substring(text(),1,1)='g'][substring(text(),2,1)='o']">
5228 <form authority="smd">filmstrip roll</form>
5231 test="marc:controlfield[@tag=007][substring(text(),1,1)='g'][substring(text(),2,1)='f']">
5232 <form authority="smd">other filmstrip type</form>
5235 test="marc:controlfield[@tag=007][substring(text(),1,1)='g'][substring(text(),2,1)='s']">
5236 <form authority="smd">slide</form>
5239 test="marc:controlfield[@tag=007][substring(text(),1,1)='g'][substring(text(),2,1)='t']">
5240 <form authority="smd">transparency</form>
5243 test="marc:controlfield[@tag=007][substring(text(),1,1)='r'][substring(text(),2,1)='r']">
5244 <form authority="smd">remote-sensing image</form>
5247 test="marc:controlfield[@tag=007][substring(text(),1,1)='s'][substring(text(),2,1)='e']">
5248 <form authority="smd">cylinder</form>
5251 test="marc:controlfield[@tag=007][substring(text(),1,1)='s'][substring(text(),2,1)='q']">
5252 <form authority="smd">roll</form>
5255 test="marc:controlfield[@tag=007][substring(text(),1,1)='s'][substring(text(),2,1)='g']">
5256 <form authority="smd">sound cartridge</form>
5259 test="marc:controlfield[@tag=007][substring(text(),1,1)='s'][substring(text(),2,1)='s']">
5260 <form authority="smd">sound cassette</form>
5263 test="marc:controlfield[@tag=007][substring(text(),1,1)='s'][substring(text(),2,1)='d']">
5264 <form authority="smd">sound disc</form>
5267 test="marc:controlfield[@tag=007][substring(text(),1,1)='s'][substring(text(),2,1)='t']">
5268 <form authority="smd">sound-tape reel</form>
5271 test="marc:controlfield[@tag=007][substring(text(),1,1)='s'][substring(text(),2,1)='i']">
5272 <form authority="smd">sound-track film</form>
5275 test="marc:controlfield[@tag=007][substring(text(),1,1)='s'][substring(text(),2,1)='w']">
5276 <form authority="smd">wire recording</form>
5280 test="marc:controlfield[@tag=007][substring(text(),1,1)='f'][substring(text(),2,1)='c']">
5281 <form authority="smd">braille</form>
5284 test="marc:controlfield[@tag=007][substring(text(),1,1)='f'][substring(text(),2,1)='b']">
5285 <form authority="smd">combination</form>
5288 test="marc:controlfield[@tag=007][substring(text(),1,1)='f'][substring(text(),2,1)='a']">
5289 <form authority="smd">moon</form>
5292 test="marc:controlfield[@tag=007][substring(text(),1,1)='f'][substring(text(),2,1)='d']">
5293 <form authority="smd">tactile, with no writing system</form>
5297 test="marc:controlfield[@tag=007][substring(text(),1,1)='t'][substring(text(),2,1)='c']">
5298 <form authority="smd">braille</form>
5301 test="marc:controlfield[@tag=007][substring(text(),1,1)='t'][substring(text(),2,1)='b']">
5302 <form authority="smd">large print</form>
5305 test="marc:controlfield[@tag=007][substring(text(),1,1)='t'][substring(text(),2,1)='a']">
5306 <form authority="smd">regular print</form>
5309 test="marc:controlfield[@tag=007][substring(text(),1,1)='t'][substring(text(),2,1)='d']">
5310 <form authority="smd">text in looseleaf binder</form>
5314 test="marc:controlfield[@tag=007][substring(text(),1,1)='v'][substring(text(),2,1)='c']">
5315 <form authority="smd">videocartridge</form>
5318 test="marc:controlfield[@tag=007][substring(text(),1,1)='v'][substring(text(),2,1)='f']">
5319 <form authority="smd">videocassette</form>
5322 test="marc:controlfield[@tag=007][substring(text(),1,1)='v'][substring(text(),2,1)='d']">
5323 <form authority="smd">videodisc</form>
5326 test="marc:controlfield[@tag=007][substring(text(),1,1)='v'][substring(text(),2,1)='r']">
5327 <form authority="smd">videoreel</form>
5331 select="marc:datafield[@tag=856]/marc:subfield[@code='q'][string-length(.)>1]">
5333 <xsl:value-of select="."/>
5334 </internetMediaType>
5336 <xsl:for-each select="marc:datafield[@tag=300]">
5338 <xsl:call-template name="subfieldSelect">
5339 <xsl:with-param name="codes">abce</xsl:with-param>
5340 </xsl:call-template>
5344 <xsl:if test="string-length(normalize-space($physicalDescription))">
5345 <physicalDescription>
5346 <xsl:copy-of select="$physicalDescription"/>
5347 </physicalDescription>
5349 <xsl:for-each select="marc:datafield[@tag=520]">
5351 <xsl:call-template name="uri"/>
5352 <xsl:call-template name="subfieldSelect">
5353 <xsl:with-param name="codes">ab</xsl:with-param>
5354 </xsl:call-template>
5357 <xsl:for-each select="marc:datafield[@tag=505]">
5359 <xsl:call-template name="uri"/>
5360 <xsl:call-template name="subfieldSelect">
5361 <xsl:with-param name="codes">agrt</xsl:with-param>
5362 </xsl:call-template>
5365 <xsl:for-each select="marc:datafield[@tag=521]">
5367 <xsl:call-template name="subfieldSelect">
5368 <xsl:with-param name="codes">ab</xsl:with-param>
5369 </xsl:call-template>
5372 <xsl:if test="$typeOf008='BK' or $typeOf008='CF' or $typeOf008='MU' or $typeOf008='VM'">
5373 <xsl:variable name="controlField008-22" select="substring($controlField008,23,1)"/>
5376 <xsl:when test="$controlField008-22='d'">
5377 <targetAudience authority="marctarget">adolescent</targetAudience>
5379 <xsl:when test="$controlField008-22='e'">
5380 <targetAudience authority="marctarget">adult</targetAudience>
5382 <xsl:when test="$controlField008-22='g'">
5383 <targetAudience authority="marctarget">general</targetAudience>
5386 test="$controlField008-22='b' or $controlField008-22='c' or $controlField008-22='j'">
5387 <targetAudience authority="marctarget">juvenile</targetAudience>
5389 <xsl:when test="$controlField008-22='a'">
5390 <targetAudience authority="marctarget">preschool</targetAudience>
5392 <xsl:when test="$controlField008-22='f'">
5393 <targetAudience authority="marctarget">specialized</targetAudience>
5397 <xsl:for-each select="marc:datafield[@tag=245]/marc:subfield[@code='c']">
5398 <note type="statement of responsibility">
5399 <xsl:value-of select="."/>
5402 <xsl:for-each select="marc:datafield[@tag=500]">
5404 <xsl:value-of select="marc:subfield[@code='a']"/>
5405 <xsl:call-template name="uri"/>
5409 <!--3.2 change tmee additional note fields-->
5411 <xsl:for-each select="marc:datafield[@tag=506]">
5412 <note type="restrictions">
5413 <xsl:call-template name="uri"/>
5414 <xsl:variable name="str">
5415 <xsl:for-each select="marc:subfield[@code!='6' or @code!='8']">
5416 <xsl:value-of select="."/>
5417 <xsl:text> </xsl:text>
5420 <xsl:value-of select="substring($str,1,string-length($str)-1)"/>
5424 <xsl:for-each select="marc:datafield[@tag=510]">
5425 <note type="citation/reference">
5426 <xsl:call-template name="uri"/>
5427 <xsl:variable name="str">
5428 <xsl:for-each select="marc:subfield[@code!='6' or @code!='8']">
5429 <xsl:value-of select="."/>
5430 <xsl:text> </xsl:text>
5433 <xsl:value-of select="substring($str,1,string-length($str)-1)"/>
5438 <xsl:for-each select="marc:datafield[@tag=511]">
5439 <note type="performers">
5440 <xsl:call-template name="uri"/>
5441 <xsl:value-of select="marc:subfield[@code='a']"/>
5444 <xsl:for-each select="marc:datafield[@tag=518]">
5446 <xsl:call-template name="uri"/>
5447 <xsl:value-of select="marc:subfield[@code='a']"/>
5451 <xsl:for-each select="marc:datafield[@tag=530]">
5452 <note type="additional physical form">
5453 <xsl:call-template name="uri"/>
5454 <xsl:variable name="str">
5455 <xsl:for-each select="marc:subfield[@code!='6' or @code!='8']">
5456 <xsl:value-of select="."/>
5457 <xsl:text> </xsl:text>
5460 <xsl:value-of select="substring($str,1,string-length($str)-1)"/>
5464 <xsl:for-each select="marc:datafield[@tag=533]">
5465 <note type="reproduction">
5466 <xsl:call-template name="uri"/>
5467 <xsl:variable name="str">
5468 <xsl:for-each select="marc:subfield[@code!='6' or @code!='8']">
5469 <xsl:value-of select="."/>
5470 <xsl:text> </xsl:text>
5473 <xsl:value-of select="substring($str,1,string-length($str)-1)"/>
5477 <xsl:for-each select="marc:datafield[@tag=534]">
5478 <note type="original version">
5479 <xsl:call-template name="uri"/>
5480 <xsl:variable name="str">
5481 <xsl:for-each select="marc:subfield[@code!='6' or @code!='8']">
5482 <xsl:value-of select="."/>
5483 <xsl:text> </xsl:text>
5486 <xsl:value-of select="substring($str,1,string-length($str)-1)"/>
5490 <xsl:for-each select="marc:datafield[@tag=538]">
5491 <note type="system details">
5492 <xsl:call-template name="uri"/>
5493 <xsl:variable name="str">
5494 <xsl:for-each select="marc:subfield[@code!='6' or @code!='8']">
5495 <xsl:value-of select="."/>
5496 <xsl:text> </xsl:text>
5499 <xsl:value-of select="substring($str,1,string-length($str)-1)"/>
5503 <xsl:for-each select="marc:datafield[@tag=583]">
5504 <note type="action">
5505 <xsl:call-template name="uri"/>
5506 <xsl:variable name="str">
5507 <xsl:for-each select="marc:subfield[@code!='6' or @code!='8']">
5508 <xsl:value-of select="."/>
5509 <xsl:text> </xsl:text>
5512 <xsl:value-of select="substring($str,1,string-length($str)-1)"/>
5517 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]">
5519 <xsl:call-template name="uri"/>
5520 <xsl:variable name="str">
5521 <xsl:for-each select="marc:subfield[@code!='6' or @code!='8']">
5522 <xsl:value-of select="."/>
5523 <xsl:text> </xsl:text>
5526 <xsl:value-of select="substring($str,1,string-length($str)-1)"/>
5530 select="marc:datafield[@tag=034][marc:subfield[@code='d' or @code='e' or @code='f' or @code='g']]">
5534 <xsl:call-template name="subfieldSelect">
5535 <xsl:with-param name="codes">defg</xsl:with-param>
5536 </xsl:call-template>
5541 <xsl:for-each select="marc:datafield[@tag=043]">
5543 <xsl:for-each select="marc:subfield[@code='a' or @code='b' or @code='c']">
5545 <xsl:attribute name="authority">
5546 <xsl:if test="@code='a'">
5547 <xsl:text>marcgac</xsl:text>
5549 <xsl:if test="@code='b'">
5550 <xsl:value-of select="following-sibling::marc:subfield[@code=2]"/>
5552 <xsl:if test="@code='c'">
5553 <xsl:text>iso3166</xsl:text>
5556 <xsl:value-of select="self::marc:subfield"/>
5561 <!-- tmee 2006/11/27 -->
5562 <xsl:for-each select="marc:datafield[@tag=255]">
5564 <xsl:for-each select="marc:subfield[@code='a' or @code='b' or @code='c']">
5566 <xsl:if test="@code='a'">
5568 <xsl:value-of select="."/>
5571 <xsl:if test="@code='b'">
5573 <xsl:value-of select="."/>
5576 <xsl:if test="@code='c'">
5578 <xsl:value-of select="."/>
5586 <xsl:apply-templates select="marc:datafield[653 >= @tag and @tag >= 600]"/>
5587 <xsl:apply-templates select="marc:datafield[@tag=656]"/>
5588 <xsl:for-each select="marc:datafield[@tag=752 or @tag=662]">
5590 <hierarchicalGeographic>
5591 <xsl:for-each select="marc:subfield[@code='a']">
5593 <xsl:call-template name="chopPunctuation">
5594 <xsl:with-param name="chopString" select="."/>
5595 </xsl:call-template>
5598 <xsl:for-each select="marc:subfield[@code='b']">
5600 <xsl:call-template name="chopPunctuation">
5601 <xsl:with-param name="chopString" select="."/>
5602 </xsl:call-template>
5605 <xsl:for-each select="marc:subfield[@code='c']">
5607 <xsl:call-template name="chopPunctuation">
5608 <xsl:with-param name="chopString" select="."/>
5609 </xsl:call-template>
5612 <xsl:for-each select="marc:subfield[@code='d']">
5614 <xsl:call-template name="chopPunctuation">
5615 <xsl:with-param name="chopString" select="."/>
5616 </xsl:call-template>
5619 <xsl:for-each select="marc:subfield[@code='e']">
5621 <xsl:call-template name="chopPunctuation">
5622 <xsl:with-param name="chopString" select="."/>
5623 </xsl:call-template>
5626 <xsl:for-each select="marc:subfield[@code='g']">
5628 <xsl:call-template name="chopPunctuation">
5629 <xsl:with-param name="chopString" select="."/>
5630 </xsl:call-template>
5633 <xsl:for-each select="marc:subfield[@code='h']">
5634 <extraterrestrialArea>
5635 <xsl:call-template name="chopPunctuation">
5636 <xsl:with-param name="chopString" select="."/>
5637 </xsl:call-template>
5638 </extraterrestrialArea>
5640 </hierarchicalGeographic>
5643 <xsl:for-each select="marc:datafield[@tag=045][marc:subfield[@code='b']]">
5646 <xsl:when test="@ind1=2">
5647 <temporal encoding="iso8601" point="start">
5648 <xsl:call-template name="chopPunctuation">
5649 <xsl:with-param name="chopString">
5650 <xsl:value-of select="marc:subfield[@code='b'][1]"/>
5652 </xsl:call-template>
5654 <temporal encoding="iso8601" point="end">
5655 <xsl:call-template name="chopPunctuation">
5656 <xsl:with-param name="chopString">
5657 <xsl:value-of select="marc:subfield[@code='b'][2]"/>
5659 </xsl:call-template>
5663 <xsl:for-each select="marc:subfield[@code='b']">
5664 <temporal encoding="iso8601">
5665 <xsl:call-template name="chopPunctuation">
5666 <xsl:with-param name="chopString" select="."/>
5667 </xsl:call-template>
5674 <xsl:for-each select="marc:datafield[@tag=050]">
5675 <xsl:for-each select="marc:subfield[@code='b']">
5676 <classification authority="lcc">
5677 <xsl:if test="../marc:subfield[@code='3']">
5678 <xsl:attribute name="displayLabel">
5679 <xsl:value-of select="../marc:subfield[@code='3']"/>
5682 <xsl:value-of select="preceding-sibling::marc:subfield[@code='a'][1]"/>
5683 <xsl:text> </xsl:text>
5684 <xsl:value-of select="text()"/>
5688 select="marc:subfield[@code='a'][not(following-sibling::marc:subfield[@code='b'])]">
5689 <classification authority="lcc">
5690 <xsl:if test="../marc:subfield[@code='3']">
5691 <xsl:attribute name="displayLabel">
5692 <xsl:value-of select="../marc:subfield[@code='3']"/>
5695 <xsl:value-of select="text()"/>
5699 <xsl:for-each select="marc:datafield[@tag=082]">
5700 <classification authority="ddc">
5701 <xsl:if test="marc:subfield[@code='2']">
5702 <xsl:attribute name="edition">
5703 <xsl:value-of select="marc:subfield[@code='2']"/>
5706 <xsl:call-template name="subfieldSelect">
5707 <xsl:with-param name="codes">ab</xsl:with-param>
5708 </xsl:call-template>
5711 <xsl:for-each select="marc:datafield[@tag=080]">
5712 <classification authority="udc">
5713 <xsl:call-template name="subfieldSelect">
5714 <xsl:with-param name="codes">abx</xsl:with-param>
5715 </xsl:call-template>
5718 <xsl:for-each select="marc:datafield[@tag=060]">
5719 <classification authority="nlm">
5720 <xsl:call-template name="subfieldSelect">
5721 <xsl:with-param name="codes">ab</xsl:with-param>
5722 </xsl:call-template>
5725 <xsl:for-each select="marc:datafield[@tag=086][@ind1=0]">
5726 <classification authority="sudocs">
5727 <xsl:value-of select="marc:subfield[@code='a']"/>
5730 <xsl:for-each select="marc:datafield[@tag=086][@ind1=1]">
5731 <classification authority="candoc">
5732 <xsl:value-of select="marc:subfield[@code='a']"/>
5735 <xsl:for-each select="marc:datafield[@tag=086]">
5737 <xsl:attribute name="authority">
5738 <xsl:value-of select="marc:subfield[@code='2']"/>
5740 <xsl:value-of select="marc:subfield[@code='a']"/>
5743 <xsl:for-each select="marc:datafield[@tag=084]">
5745 <xsl:attribute name="authority">
5746 <xsl:value-of select="marc:subfield[@code='2']"/>
5748 <xsl:call-template name="subfieldSelect">
5749 <xsl:with-param name="codes">ab</xsl:with-param>
5750 </xsl:call-template>
5753 <xsl:for-each select="marc:datafield[@tag=440]">
5754 <relatedItem type="series">
5757 <xsl:call-template name="chopPunctuation">
5758 <xsl:with-param name="chopString">
5759 <xsl:call-template name="subfieldSelect">
5760 <xsl:with-param name="codes">av</xsl:with-param>
5761 </xsl:call-template>
5763 </xsl:call-template>
5765 <xsl:call-template name="part"/>
5769 <xsl:for-each select="marc:datafield[@tag=490][@ind1=0]">
5770 <relatedItem type="series">
5773 <xsl:call-template name="chopPunctuation">
5774 <xsl:with-param name="chopString">
5775 <xsl:call-template name="subfieldSelect">
5776 <xsl:with-param name="codes">av</xsl:with-param>
5777 </xsl:call-template>
5779 </xsl:call-template>
5781 <xsl:call-template name="part"/>
5785 <xsl:for-each select="marc:datafield[@tag=510]">
5786 <relatedItem type="isReferencedBy">
5788 <xsl:call-template name="subfieldSelect">
5789 <xsl:with-param name="codes">abcx3</xsl:with-param>
5790 </xsl:call-template>
5794 <xsl:for-each select="marc:datafield[@tag=534]">
5795 <relatedItem type="original">
5796 <xsl:call-template name="relatedTitle"/>
5797 <xsl:call-template name="relatedName"/>
5798 <xsl:if test="marc:subfield[@code='b' or @code='c']">
5800 <xsl:for-each select="marc:subfield[@code='c']">
5802 <xsl:value-of select="."/>
5805 <xsl:for-each select="marc:subfield[@code='b']">
5807 <xsl:value-of select="."/>
5812 <xsl:call-template name="relatedIdentifierISSN"/>
5813 <xsl:for-each select="marc:subfield[@code='z']">
5814 <identifier type="isbn">
5815 <xsl:value-of select="."/>
5818 <xsl:call-template name="relatedNote"/>
5821 <xsl:for-each select="marc:datafield[@tag=700][marc:subfield[@code='t']]">
5823 <xsl:call-template name="constituentOrRelatedType"/>
5826 <xsl:call-template name="chopPunctuation">
5827 <xsl:with-param name="chopString">
5828 <xsl:call-template name="specialSubfieldSelect">
5829 <xsl:with-param name="anyCodes">tfklmorsv</xsl:with-param>
5830 <xsl:with-param name="axis">t</xsl:with-param>
5831 <xsl:with-param name="afterCodes">g</xsl:with-param>
5832 </xsl:call-template>
5834 </xsl:call-template>
5836 <xsl:call-template name="part"/>
5838 <name type="personal">
5840 <xsl:call-template name="specialSubfieldSelect">
5841 <xsl:with-param name="anyCodes">aq</xsl:with-param>
5842 <xsl:with-param name="axis">t</xsl:with-param>
5843 <xsl:with-param name="beforeCodes">g</xsl:with-param>
5844 </xsl:call-template>
5846 <xsl:call-template name="termsOfAddress"/>
5847 <xsl:call-template name="nameDate"/>
5848 <xsl:call-template name="role"/>
5850 <xsl:call-template name="relatedForm"/>
5851 <xsl:call-template name="relatedIdentifierISSN"/>
5854 <xsl:for-each select="marc:datafield[@tag=710][marc:subfield[@code='t']]">
5856 <xsl:call-template name="constituentOrRelatedType"/>
5859 <xsl:call-template name="chopPunctuation">
5860 <xsl:with-param name="chopString">
5861 <xsl:call-template name="specialSubfieldSelect">
5862 <xsl:with-param name="anyCodes">tfklmorsv</xsl:with-param>
5863 <xsl:with-param name="axis">t</xsl:with-param>
5864 <xsl:with-param name="afterCodes">dg</xsl:with-param>
5865 </xsl:call-template>
5867 </xsl:call-template>
5869 <xsl:call-template name="relatedPartNumName"/>
5871 <name type="corporate">
5872 <xsl:for-each select="marc:subfield[@code='a']">
5874 <xsl:value-of select="."/>
5877 <xsl:for-each select="marc:subfield[@code='b']">
5879 <xsl:value-of select="."/>
5882 <xsl:variable name="tempNamePart">
5883 <xsl:call-template name="specialSubfieldSelect">
5884 <xsl:with-param name="anyCodes">c</xsl:with-param>
5885 <xsl:with-param name="axis">t</xsl:with-param>
5886 <xsl:with-param name="beforeCodes">dgn</xsl:with-param>
5887 </xsl:call-template>
5889 <xsl:if test="normalize-space($tempNamePart)">
5891 <xsl:value-of select="$tempNamePart"/>
5894 <xsl:call-template name="role"/>
5896 <xsl:call-template name="relatedForm"/>
5897 <xsl:call-template name="relatedIdentifierISSN"/>
5900 <xsl:for-each select="marc:datafield[@tag=711][marc:subfield[@code='t']]">
5902 <xsl:call-template name="constituentOrRelatedType"/>
5905 <xsl:call-template name="chopPunctuation">
5906 <xsl:with-param name="chopString">
5907 <xsl:call-template name="specialSubfieldSelect">
5908 <xsl:with-param name="anyCodes">tfklsv</xsl:with-param>
5909 <xsl:with-param name="axis">t</xsl:with-param>
5910 <xsl:with-param name="afterCodes">g</xsl:with-param>
5911 </xsl:call-template>
5913 </xsl:call-template>
5915 <xsl:call-template name="relatedPartNumName"/>
5917 <name type="conference">
5919 <xsl:call-template name="specialSubfieldSelect">
5920 <xsl:with-param name="anyCodes">aqdc</xsl:with-param>
5921 <xsl:with-param name="axis">t</xsl:with-param>
5922 <xsl:with-param name="beforeCodes">gn</xsl:with-param>
5923 </xsl:call-template>
5926 <xsl:call-template name="relatedForm"/>
5927 <xsl:call-template name="relatedIdentifierISSN"/>
5930 <xsl:for-each select="marc:datafield[@tag=730][@ind2=2]">
5932 <xsl:call-template name="constituentOrRelatedType"/>
5935 <xsl:call-template name="chopPunctuation">
5936 <xsl:with-param name="chopString">
5937 <xsl:call-template name="subfieldSelect">
5938 <xsl:with-param name="codes">adfgklmorsv</xsl:with-param>
5939 </xsl:call-template>
5941 </xsl:call-template>
5943 <xsl:call-template name="part"/>
5945 <xsl:call-template name="relatedForm"/>
5946 <xsl:call-template name="relatedIdentifierISSN"/>
5949 <xsl:for-each select="marc:datafield[@tag=740][@ind2=2]">
5951 <xsl:call-template name="constituentOrRelatedType"/>
5954 <xsl:call-template name="chopPunctuation">
5955 <xsl:with-param name="chopString">
5956 <xsl:value-of select="marc:subfield[@code='a']"/>
5958 </xsl:call-template>
5960 <xsl:call-template name="part"/>
5962 <xsl:call-template name="relatedForm"/>
5965 <xsl:for-each select="marc:datafield[@tag=760]|marc:datafield[@tag=762]">
5966 <relatedItem type="series">
5967 <xsl:call-template name="relatedItem76X-78X"/>
5971 select="marc:datafield[@tag=765]|marc:datafield[@tag=767]|marc:datafield[@tag=777]|marc:datafield[@tag=787]">
5973 <xsl:call-template name="relatedItem76X-78X"/>
5976 <xsl:for-each select="marc:datafield[@tag=775]">
5977 <relatedItem type="otherVersion">
5978 <xsl:call-template name="relatedItem76X-78X"/>
5981 <xsl:for-each select="marc:datafield[@tag=770]|marc:datafield[@tag=774]">
5982 <relatedItem type="constituent">
5983 <xsl:call-template name="relatedItem76X-78X"/>
5986 <xsl:for-each select="marc:datafield[@tag=772]|marc:datafield[@tag=773]">
5987 <relatedItem type="host">
5988 <xsl:call-template name="relatedItem76X-78X"/>
5991 <xsl:for-each select="marc:datafield[@tag=776]">
5992 <relatedItem type="otherFormat">
5993 <xsl:call-template name="relatedItem76X-78X"/>
5996 <xsl:for-each select="marc:datafield[@tag=780]">
5997 <relatedItem type="preceding">
5998 <xsl:call-template name="relatedItem76X-78X"/>
6001 <xsl:for-each select="marc:datafield[@tag=785]">
6002 <relatedItem type="succeeding">
6003 <xsl:call-template name="relatedItem76X-78X"/>
6006 <xsl:for-each select="marc:datafield[@tag=786]">
6007 <relatedItem type="original">
6008 <xsl:call-template name="relatedItem76X-78X"/>
6011 <xsl:for-each select="marc:datafield[@tag=800]">
6012 <relatedItem type="series">
6015 <xsl:call-template name="chopPunctuation">
6016 <xsl:with-param name="chopString">
6017 <xsl:call-template name="specialSubfieldSelect">
6018 <xsl:with-param name="anyCodes">tfklmorsv</xsl:with-param>
6019 <xsl:with-param name="axis">t</xsl:with-param>
6020 <xsl:with-param name="afterCodes">g</xsl:with-param>
6021 </xsl:call-template>
6023 </xsl:call-template>
6025 <xsl:call-template name="part"/>
6027 <name type="personal">
6029 <xsl:call-template name="chopPunctuation">
6030 <xsl:with-param name="chopString">
6031 <xsl:call-template name="specialSubfieldSelect">
6032 <xsl:with-param name="anyCodes">aq</xsl:with-param>
6033 <xsl:with-param name="axis">t</xsl:with-param>
6034 <xsl:with-param name="beforeCodes">g</xsl:with-param>
6035 </xsl:call-template>
6037 </xsl:call-template>
6039 <xsl:call-template name="termsOfAddress"/>
6040 <xsl:call-template name="nameDate"/>
6041 <xsl:call-template name="role"/>
6043 <xsl:call-template name="relatedForm"/>
6046 <xsl:for-each select="marc:datafield[@tag=810]">
6047 <relatedItem type="series">
6050 <xsl:call-template name="chopPunctuation">
6051 <xsl:with-param name="chopString">
6052 <xsl:call-template name="specialSubfieldSelect">
6053 <xsl:with-param name="anyCodes">tfklmorsv</xsl:with-param>
6054 <xsl:with-param name="axis">t</xsl:with-param>
6055 <xsl:with-param name="afterCodes">dg</xsl:with-param>
6056 </xsl:call-template>
6058 </xsl:call-template>
6060 <xsl:call-template name="relatedPartNumName"/>
6062 <name type="corporate">
6063 <xsl:for-each select="marc:subfield[@code='a']">
6065 <xsl:value-of select="."/>
6068 <xsl:for-each select="marc:subfield[@code='b']">
6070 <xsl:value-of select="."/>
6074 <xsl:call-template name="specialSubfieldSelect">
6075 <xsl:with-param name="anyCodes">c</xsl:with-param>
6076 <xsl:with-param name="axis">t</xsl:with-param>
6077 <xsl:with-param name="beforeCodes">dgn</xsl:with-param>
6078 </xsl:call-template>
6080 <xsl:call-template name="role"/>
6082 <xsl:call-template name="relatedForm"/>
6085 <xsl:for-each select="marc:datafield[@tag=811]">
6086 <relatedItem type="series">
6089 <xsl:call-template name="chopPunctuation">
6090 <xsl:with-param name="chopString">
6091 <xsl:call-template name="specialSubfieldSelect">
6092 <xsl:with-param name="anyCodes">tfklsv</xsl:with-param>
6093 <xsl:with-param name="axis">t</xsl:with-param>
6094 <xsl:with-param name="afterCodes">g</xsl:with-param>
6095 </xsl:call-template>
6097 </xsl:call-template>
6099 <xsl:call-template name="relatedPartNumName"/>
6101 <name type="conference">
6103 <xsl:call-template name="specialSubfieldSelect">
6104 <xsl:with-param name="anyCodes">aqdc</xsl:with-param>
6105 <xsl:with-param name="axis">t</xsl:with-param>
6106 <xsl:with-param name="beforeCodes">gn</xsl:with-param>
6107 </xsl:call-template>
6109 <xsl:call-template name="role"/>
6111 <xsl:call-template name="relatedForm"/>
6114 <xsl:for-each select="marc:datafield[@tag='830']">
6115 <relatedItem type="series">
6118 <xsl:call-template name="chopPunctuation">
6119 <xsl:with-param name="chopString">
6120 <xsl:call-template name="subfieldSelect">
6121 <xsl:with-param name="codes">adfgklmorsv</xsl:with-param>
6122 </xsl:call-template>
6124 </xsl:call-template>
6126 <xsl:call-template name="part"/>
6128 <xsl:call-template name="relatedForm"/>
6131 <xsl:for-each select="marc:datafield[@tag='856'][@ind2='2']/marc:subfield[@code='q']">
6134 <xsl:value-of select="."/>
6135 </internetMediaType>
6138 <xsl:for-each select="marc:datafield[@tag='020']">
6139 <xsl:call-template name="isInvalid">
6140 <xsl:with-param name="type">isbn</xsl:with-param>
6141 </xsl:call-template>
6142 <xsl:if test="marc:subfield[@code='a']">
6143 <identifier type="isbn">
6144 <xsl:value-of select="marc:subfield[@code='a']"/>
6148 <xsl:for-each select="marc:datafield[@tag='024'][@ind1='0']">
6149 <xsl:call-template name="isInvalid">
6150 <xsl:with-param name="type">isrc</xsl:with-param>
6151 </xsl:call-template>
6152 <xsl:if test="marc:subfield[@code='a']">
6153 <identifier type="isrc">
6154 <xsl:value-of select="marc:subfield[@code='a']"/>
6158 <xsl:for-each select="marc:datafield[@tag='024'][@ind1='2']">
6159 <xsl:call-template name="isInvalid">
6160 <xsl:with-param name="type">ismn</xsl:with-param>
6161 </xsl:call-template>
6162 <xsl:if test="marc:subfield[@code='a']">
6163 <identifier type="ismn">
6164 <xsl:value-of select="marc:subfield[@code='a']"/>
6168 <xsl:for-each select="marc:datafield[@tag='024'][@ind1='4']">
6169 <xsl:call-template name="isInvalid">
6170 <xsl:with-param name="type">sici</xsl:with-param>
6171 </xsl:call-template>
6172 <identifier type="sici">
6173 <xsl:call-template name="subfieldSelect">
6174 <xsl:with-param name="codes">ab</xsl:with-param>
6175 </xsl:call-template>
6178 <xsl:for-each select="marc:datafield[@tag='022']">
6179 <xsl:if test="marc:subfield[@code='a']">
6180 <xsl:call-template name="isInvalid">
6181 <xsl:with-param name="type">issn</xsl:with-param>
6182 </xsl:call-template>
6183 <identifier type="issn">
6184 <xsl:value-of select="marc:subfield[@code='a']"/>
6187 <xsl:if test="marc:subfield[@code='l']">
6188 <xsl:call-template name="isInvalid">
6189 <xsl:with-param name="type">issn-l</xsl:with-param>
6190 </xsl:call-template>
6191 <identifier type="issn-l">
6192 <xsl:value-of select="marc:subfield[@code='l']"/>
6199 <xsl:for-each select="marc:datafield[@tag='010']">
6200 <xsl:call-template name="isInvalid">
6201 <xsl:with-param name="type">lccn</xsl:with-param>
6202 </xsl:call-template>
6203 <identifier type="lccn">
6204 <xsl:value-of select="normalize-space(marc:subfield[@code='a'])"/>
6207 <xsl:for-each select="marc:datafield[@tag='028']">
6209 <xsl:attribute name="type">
6211 <xsl:when test="@ind1='0'">issue number</xsl:when>
6212 <xsl:when test="@ind1='1'">matrix number</xsl:when>
6213 <xsl:when test="@ind1='2'">music plate</xsl:when>
6214 <xsl:when test="@ind1='3'">music publisher</xsl:when>
6215 <xsl:when test="@ind1='4'">videorecording identifier</xsl:when>
6218 <!--<xsl:call-template name="isInvalid"/>-->
6219 <!-- no $z in 028 -->
6220 <xsl:call-template name="subfieldSelect">
6221 <xsl:with-param name="codes">
6223 <xsl:when test="@ind1='0'">ba</xsl:when>
6224 <xsl:otherwise>ab</xsl:otherwise>
6227 </xsl:call-template>
6230 <xsl:for-each select="marc:datafield[@tag='037']">
6231 <identifier type="stock number">
6232 <!--<xsl:call-template name="isInvalid"/>-->
6233 <!-- no $z in 037 -->
6234 <xsl:call-template name="subfieldSelect">
6235 <xsl:with-param name="codes">ab</xsl:with-param>
6236 </xsl:call-template>
6239 <xsl:for-each select="marc:datafield[@tag='856'][marc:subfield[@code='u']]">
6241 <xsl:attribute name="type">
6244 test="starts-with(marc:subfield[@code='u'],'urn:doi') or starts-with(marc:subfield[@code='u'],'doi')"
6247 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')"
6249 <xsl:otherwise>uri</xsl:otherwise>
6254 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') ">
6256 select="concat('hdl:',substring-after(marc:subfield[@code='u'],'http://hdl.loc.gov/'))"
6260 <xsl:value-of select="marc:subfield[@code='u']"/>
6265 test="starts-with(marc:subfield[@code='u'],'urn:hdl') or starts-with(marc:subfield[@code='u'],'hdl')">
6266 <identifier type="hdl">
6267 <xsl:if test="marc:subfield[@code='y' or @code='3' or @code='z']">
6268 <xsl:attribute name="displayLabel">
6269 <xsl:call-template name="subfieldSelect">
6270 <xsl:with-param name="codes">y3z</xsl:with-param>
6271 </xsl:call-template>
6275 select="concat('hdl:',substring-after(marc:subfield[@code='u'],'http://hdl.loc.gov/'))"
6280 <xsl:for-each select="marc:datafield[@tag=024][@ind1=1]">
6281 <identifier type="upc">
6282 <xsl:call-template name="isInvalid"/>
6283 <xsl:value-of select="marc:subfield[@code='a']"/>
6287 <!-- 1/04 fix added $y -->
6290 <xsl:for-each select="marc:datafield[@tag=856][@ind2=1][marc:subfield[@code='u']]">
6291 <relatedItem type="otherVersion">
6294 <xsl:if test="marc:subfield[@code='y' or @code='3']">
6295 <xsl:attribute name="displayLabel">
6296 <xsl:call-template name="subfieldSelect">
6297 <xsl:with-param name="codes">y3</xsl:with-param>
6298 </xsl:call-template>
6301 <xsl:if test="marc:subfield[@code='z' ]">
6302 <xsl:attribute name="note">
6303 <xsl:call-template name="subfieldSelect">
6304 <xsl:with-param name="codes">z</xsl:with-param>
6305 </xsl:call-template>
6308 <xsl:value-of select="marc:subfield[@code='u']"/>
6313 <xsl:for-each select="marc:datafield[@tag=856][@ind2=2][marc:subfield[@code='u']]">
6317 <xsl:if test="marc:subfield[@code='y' or @code='3']">
6318 <xsl:attribute name="displayLabel">
6319 <xsl:call-template name="subfieldSelect">
6320 <xsl:with-param name="codes">y3</xsl:with-param>
6321 </xsl:call-template>
6324 <xsl:if test="marc:subfield[@code='z' ]">
6325 <xsl:attribute name="note">
6326 <xsl:call-template name="subfieldSelect">
6327 <xsl:with-param name="codes">z</xsl:with-param>
6328 </xsl:call-template>
6331 <xsl:value-of select="marc:subfield[@code='u']"/>
6337 <!-- 3.2 change tmee 856z -->
6340 <xsl:for-each select="marc:datafield[@tag=852]">
6342 <xsl:if test="marc:subfield[@code='a' or @code='b' or @code='e']">
6344 <xsl:call-template name="subfieldSelect">
6345 <xsl:with-param name="codes">abe</xsl:with-param>
6346 </xsl:call-template>
6350 <xsl:if test="marc:subfield[@code='u']">
6352 <xsl:call-template name="uri"/>
6353 <xsl:call-template name="subfieldSelect">
6354 <xsl:with-param name="codes">u</xsl:with-param>
6355 </xsl:call-template>
6360 test="marc:subfield[@code='h' or @code='i' or @code='j' or @code='k' or @code='l' or @code='m' or @code='t']">
6362 <xsl:call-template name="subfieldSelect">
6363 <xsl:with-param name="codes">hijklmt</xsl:with-param>
6364 </xsl:call-template>
6370 <xsl:for-each select="marc:datafield[@tag=506]">
6371 <accessCondition type="restrictionOnAccess">
6372 <xsl:call-template name="subfieldSelect">
6373 <xsl:with-param name="codes">abcd35</xsl:with-param>
6374 </xsl:call-template>
6377 <xsl:for-each select="marc:datafield[@tag=540]">
6378 <accessCondition type="useAndReproduction">
6379 <xsl:call-template name="subfieldSelect">
6380 <xsl:with-param name="codes">abcde35</xsl:with-param>
6381 </xsl:call-template>
6389 <xsl:for-each select="marc:leader[substring($leader,19,1)='a']">
6390 <descriptionStandard>aacr2</descriptionStandard>
6393 <xsl:for-each select="marc:datafield[@tag=040]">
6394 <xsl:if test="marc:subfield[@code='e']">
6395 <descriptionStandard>
6396 <xsl:value-of select="marc:subfield[@code='e']"/>
6397 </descriptionStandard>
6399 <recordContentSource authority="marcorg">
6400 <xsl:value-of select="marc:subfield[@code='a']"/>
6401 </recordContentSource>
6403 <xsl:for-each select="marc:controlfield[@tag=008]">
6404 <recordCreationDate encoding="marc">
6405 <xsl:value-of select="substring(.,1,6)"/>
6406 </recordCreationDate>
6409 <xsl:for-each select="marc:controlfield[@tag=005]">
6410 <recordChangeDate encoding="iso8601">
6411 <xsl:value-of select="."/>
6414 <xsl:for-each select="marc:controlfield[@tag=001]">
6416 <xsl:if test="../marc:controlfield[@tag=003]">
6417 <xsl:attribute name="source">
6418 <xsl:value-of select="../marc:controlfield[@tag=003]"/>
6421 <xsl:value-of select="."/>
6424 <xsl:for-each select="marc:datafield[@tag=040]/marc:subfield[@code='b']">
6425 <languageOfCataloging>
6426 <languageTerm authority="iso639-2b" type="code">
6427 <xsl:value-of select="."/>
6429 </languageOfCataloging>
6433 <xsl:template name="displayForm">
6434 <xsl:for-each select="marc:subfield[@code='c']">
6436 <xsl:value-of select="."/>
6440 <xsl:template name="affiliation">
6441 <xsl:for-each select="marc:subfield[@code='u']">
6443 <xsl:value-of select="."/>
6447 <xsl:template name="uri">
6448 <xsl:for-each select="marc:subfield[@code='u']|marc:subfield[@code='0']">
6449 <xsl:attribute name="xlink:href">
6450 <xsl:value-of select="."/>
6454 <xsl:template name="role">
6455 <xsl:for-each select="marc:subfield[@code='e']">
6457 <roleTerm type="text">
6458 <xsl:value-of select="."/>
6462 <xsl:for-each select="marc:subfield[@code='4']">
6464 <roleTerm authority="marcrelator" type="code">
6465 <xsl:value-of select="."/>
6470 <xsl:template name="part">
6471 <xsl:variable name="partNumber">
6472 <xsl:call-template name="specialSubfieldSelect">
6473 <xsl:with-param name="axis">n</xsl:with-param>
6474 <xsl:with-param name="anyCodes">n</xsl:with-param>
6475 <xsl:with-param name="afterCodes">fgkdlmor</xsl:with-param>
6476 </xsl:call-template>
6478 <xsl:variable name="partName">
6479 <xsl:call-template name="specialSubfieldSelect">
6480 <xsl:with-param name="axis">p</xsl:with-param>
6481 <xsl:with-param name="anyCodes">p</xsl:with-param>
6482 <xsl:with-param name="afterCodes">fgkdlmor</xsl:with-param>
6483 </xsl:call-template>
6485 <xsl:if test="string-length(normalize-space($partNumber))">
6487 <xsl:call-template name="chopPunctuation">
6488 <xsl:with-param name="chopString" select="$partNumber"/>
6489 </xsl:call-template>
6492 <xsl:if test="string-length(normalize-space($partName))">
6494 <xsl:call-template name="chopPunctuation">
6495 <xsl:with-param name="chopString" select="$partName"/>
6496 </xsl:call-template>
6500 <xsl:template name="relatedPart">
6501 <xsl:if test="@tag=773">
6502 <xsl:for-each select="marc:subfield[@code='g']">
6505 <xsl:value-of select="."/>
6509 <xsl:for-each select="marc:subfield[@code='q']">
6511 <xsl:call-template name="parsePart"/>
6516 <xsl:template name="relatedPartNumName">
6517 <xsl:variable name="partNumber">
6518 <xsl:call-template name="specialSubfieldSelect">
6519 <xsl:with-param name="axis">g</xsl:with-param>
6520 <xsl:with-param name="anyCodes">g</xsl:with-param>
6521 <xsl:with-param name="afterCodes">pst</xsl:with-param>
6522 </xsl:call-template>
6524 <xsl:variable name="partName">
6525 <xsl:call-template name="specialSubfieldSelect">
6526 <xsl:with-param name="axis">p</xsl:with-param>
6527 <xsl:with-param name="anyCodes">p</xsl:with-param>
6528 <xsl:with-param name="afterCodes">fgkdlmor</xsl:with-param>
6529 </xsl:call-template>
6531 <xsl:if test="string-length(normalize-space($partNumber))">
6533 <xsl:value-of select="$partNumber"/>
6536 <xsl:if test="string-length(normalize-space($partName))">
6538 <xsl:value-of select="$partName"/>
6542 <xsl:template name="relatedName">
6543 <xsl:for-each select="marc:subfield[@code='a']">
6546 <xsl:value-of select="."/>
6551 <xsl:template name="relatedForm">
6552 <xsl:for-each select="marc:subfield[@code='h']">
6553 <physicalDescription>
6555 <xsl:value-of select="."/>
6557 </physicalDescription>
6560 <xsl:template name="relatedExtent">
6561 <xsl:for-each select="marc:subfield[@code='h']">
6562 <physicalDescription>
6564 <xsl:value-of select="."/>
6566 </physicalDescription>
6569 <xsl:template name="relatedNote">
6570 <xsl:for-each select="marc:subfield[@code='n']">
6572 <xsl:value-of select="."/>
6576 <xsl:template name="relatedSubject">
6577 <xsl:for-each select="marc:subfield[@code='j']">
6579 <temporal encoding="iso8601">
6580 <xsl:call-template name="chopPunctuation">
6581 <xsl:with-param name="chopString" select="."/>
6582 </xsl:call-template>
6587 <xsl:template name="relatedIdentifierISSN">
6588 <xsl:for-each select="marc:subfield[@code='x']">
6589 <identifier type="issn">
6590 <xsl:value-of select="."/>
6594 <xsl:template name="relatedIdentifierLocal">
6595 <xsl:for-each select="marc:subfield[@code='w']">
6596 <identifier type="local">
6597 <xsl:value-of select="."/>
6601 <xsl:template name="relatedIdentifier">
6602 <xsl:for-each select="marc:subfield[@code='o']">
6604 <xsl:value-of select="."/>
6608 <xsl:template name="relatedItem76X-78X">
6609 <xsl:call-template name="displayLabel"/>
6610 <xsl:call-template name="relatedTitle76X-78X"/>
6611 <xsl:call-template name="relatedName"/>
6612 <xsl:call-template name="relatedOriginInfo"/>
6613 <xsl:call-template name="relatedLanguage"/>
6614 <xsl:call-template name="relatedExtent"/>
6615 <xsl:call-template name="relatedNote"/>
6616 <xsl:call-template name="relatedSubject"/>
6617 <xsl:call-template name="relatedIdentifier"/>
6618 <xsl:call-template name="relatedIdentifierISSN"/>
6619 <xsl:call-template name="relatedIdentifierLocal"/>
6620 <xsl:call-template name="relatedPart"/>
6622 <xsl:template name="subjectGeographicZ">
6624 <xsl:call-template name="chopPunctuation">
6625 <xsl:with-param name="chopString" select="."/>
6626 </xsl:call-template>
6629 <xsl:template name="subjectTemporalY">
6631 <xsl:call-template name="chopPunctuation">
6632 <xsl:with-param name="chopString" select="."/>
6633 </xsl:call-template>
6636 <xsl:template name="subjectTopic">
6638 <xsl:call-template name="chopPunctuation">
6639 <xsl:with-param name="chopString" select="."/>
6640 </xsl:call-template>
6643 <!-- 3.2 change tmee 6xx $v genre -->
6644 <xsl:template name="subjectGenre">
6646 <xsl:call-template name="chopPunctuation">
6647 <xsl:with-param name="chopString" select="."/>
6648 </xsl:call-template>
6652 <xsl:template name="nameABCDN">
6653 <xsl:for-each select="marc:subfield[@code='a']">
6655 <xsl:call-template name="chopPunctuation">
6656 <xsl:with-param name="chopString" select="."/>
6657 </xsl:call-template>
6660 <xsl:for-each select="marc:subfield[@code='b']">
6662 <xsl:value-of select="."/>
6666 test="marc:subfield[@code='c'] or marc:subfield[@code='d'] or marc:subfield[@code='n']">
6668 <xsl:call-template name="subfieldSelect">
6669 <xsl:with-param name="codes">cdn</xsl:with-param>
6670 </xsl:call-template>
6674 <xsl:template name="nameABCDQ">
6676 <xsl:call-template name="chopPunctuation">
6677 <xsl:with-param name="chopString">
6678 <xsl:call-template name="subfieldSelect">
6679 <xsl:with-param name="codes">aq</xsl:with-param>
6680 </xsl:call-template>
6682 <xsl:with-param name="punctuation">
6683 <xsl:text>:,;/ </xsl:text>
6685 </xsl:call-template>
6687 <xsl:call-template name="termsOfAddress"/>
6688 <xsl:call-template name="nameDate"/>
6690 <xsl:template name="nameACDEQ">
6692 <xsl:call-template name="subfieldSelect">
6693 <xsl:with-param name="codes">acdeq</xsl:with-param>
6694 </xsl:call-template>
6697 <xsl:template name="constituentOrRelatedType">
6698 <xsl:if test="@ind2=2">
6699 <xsl:attribute name="type">constituent</xsl:attribute>
6702 <xsl:template name="relatedTitle">
6703 <xsl:for-each select="marc:subfield[@code='t']">
6706 <xsl:call-template name="chopPunctuation">
6707 <xsl:with-param name="chopString">
6708 <xsl:value-of select="."/>
6710 </xsl:call-template>
6715 <xsl:template name="relatedTitle76X-78X">
6716 <xsl:for-each select="marc:subfield[@code='t']">
6719 <xsl:call-template name="chopPunctuation">
6720 <xsl:with-param name="chopString">
6721 <xsl:value-of select="."/>
6723 </xsl:call-template>
6725 <xsl:if test="marc:datafield[@tag!=773]and marc:subfield[@code='g']">
6726 <xsl:call-template name="relatedPartNumName"/>
6730 <xsl:for-each select="marc:subfield[@code='p']">
6731 <titleInfo type="abbreviated">
6733 <xsl:call-template name="chopPunctuation">
6734 <xsl:with-param name="chopString">
6735 <xsl:value-of select="."/>
6737 </xsl:call-template>
6739 <xsl:if test="marc:datafield[@tag!=773]and marc:subfield[@code='g']">
6740 <xsl:call-template name="relatedPartNumName"/>
6744 <xsl:for-each select="marc:subfield[@code='s']">
6745 <titleInfo type="uniform">
6747 <xsl:call-template name="chopPunctuation">
6748 <xsl:with-param name="chopString">
6749 <xsl:value-of select="."/>
6751 </xsl:call-template>
6753 <xsl:if test="marc:datafield[@tag!=773]and marc:subfield[@code='g']">
6754 <xsl:call-template name="relatedPartNumName"/>
6759 <xsl:template name="relatedOriginInfo">
6760 <xsl:if test="marc:subfield[@code='b' or @code='d'] or marc:subfield[@code='f']">
6762 <xsl:if test="@tag=775">
6763 <xsl:for-each select="marc:subfield[@code='f']">
6766 <xsl:attribute name="type">code</xsl:attribute>
6767 <xsl:attribute name="authority">marcgac</xsl:attribute>
6768 <xsl:value-of select="."/>
6773 <xsl:for-each select="marc:subfield[@code='d']">
6775 <xsl:value-of select="."/>
6778 <xsl:for-each select="marc:subfield[@code='b']">
6780 <xsl:value-of select="."/>
6786 <xsl:template name="relatedLanguage">
6787 <xsl:for-each select="marc:subfield[@code='e']">
6788 <xsl:call-template name="getLanguage">
6789 <xsl:with-param name="langString">
6790 <xsl:value-of select="."/>
6792 </xsl:call-template>
6795 <xsl:template name="nameDate">
6796 <xsl:for-each select="marc:subfield[@code='d']">
6797 <namePart type="date">
6798 <xsl:call-template name="chopPunctuation">
6799 <xsl:with-param name="chopString" select="."/>
6800 </xsl:call-template>
6804 <xsl:template name="subjectAuthority">
6805 <xsl:if test="@ind2!=4">
6806 <xsl:if test="@ind2!=' '">
6807 <xsl:if test="@ind2!=8">
6808 <xsl:if test="@ind2!=9">
6809 <xsl:attribute name="authority">
6811 <xsl:when test="@ind2=0">lcsh</xsl:when>
6812 <xsl:when test="@ind2=1">lcshac</xsl:when>
6813 <xsl:when test="@ind2=2">mesh</xsl:when>
6815 <xsl:when test="@ind2=3">nal</xsl:when>
6816 <xsl:when test="@ind2=5">csh</xsl:when>
6817 <xsl:when test="@ind2=6">rvm</xsl:when>
6818 <xsl:when test="@ind2=7">
6819 <xsl:value-of select="marc:subfield[@code='2']"/>
6828 <xsl:template name="subjectAnyOrder">
6829 <xsl:for-each select="marc:subfield[@code='v' or @code='x' or @code='y' or @code='z']">
6831 <xsl:when test="@code='v'">
6832 <xsl:call-template name="subjectGenre"/>
6834 <xsl:when test="@code='x'">
6835 <xsl:call-template name="subjectTopic"/>
6837 <xsl:when test="@code='y'">
6838 <xsl:call-template name="subjectTemporalY"/>
6840 <xsl:when test="@code='z'">
6841 <xsl:call-template name="subjectGeographicZ"/>
6846 <xsl:template name="specialSubfieldSelect">
6847 <xsl:param name="anyCodes"/>
6848 <xsl:param name="axis"/>
6849 <xsl:param name="beforeCodes"/>
6850 <xsl:param name="afterCodes"/>
6851 <xsl:variable name="str">
6852 <xsl:for-each select="marc:subfield">
6854 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])">
6855 <xsl:value-of select="text()"/>
6856 <xsl:text> </xsl:text>
6860 <xsl:value-of select="substring($str,1,string-length($str)-1)"/>
6863 <!-- 3.2 change tmee 6xx $v genre -->
6864 <xsl:template match="marc:datafield[@tag=600]">
6866 <xsl:call-template name="subjectAuthority"/>
6867 <name type="personal">
6868 <xsl:call-template name="termsOfAddress"/>
6870 <xsl:call-template name="chopPunctuation">
6871 <xsl:with-param name="chopString">
6872 <xsl:call-template name="subfieldSelect">
6873 <xsl:with-param name="codes">aq</xsl:with-param>
6874 </xsl:call-template>
6876 </xsl:call-template>
6878 <xsl:call-template name="nameDate"/>
6879 <xsl:call-template name="affiliation"/>
6880 <xsl:call-template name="role"/>
6882 <xsl:call-template name="subjectAnyOrder"/>
6885 <xsl:template match="marc:datafield[@tag=610]">
6887 <xsl:call-template name="subjectAuthority"/>
6888 <name type="corporate">
6889 <xsl:for-each select="marc:subfield[@code='a']">
6891 <xsl:value-of select="."/>
6894 <xsl:for-each select="marc:subfield[@code='b']">
6896 <xsl:value-of select="."/>
6899 <xsl:if test="marc:subfield[@code='c' or @code='d' or @code='n' or @code='p']">
6901 <xsl:call-template name="subfieldSelect">
6902 <xsl:with-param name="codes">cdnp</xsl:with-param>
6903 </xsl:call-template>
6906 <xsl:call-template name="role"/>
6908 <xsl:call-template name="subjectAnyOrder"/>
6911 <xsl:template match="marc:datafield[@tag=611]">
6913 <xsl:call-template name="subjectAuthority"/>
6914 <name type="conference">
6916 <xsl:call-template name="subfieldSelect">
6917 <xsl:with-param name="codes">abcdeqnp</xsl:with-param>
6918 </xsl:call-template>
6920 <xsl:for-each select="marc:subfield[@code='4']">
6922 <roleTerm authority="marcrelator" type="code">
6923 <xsl:value-of select="."/>
6928 <xsl:call-template name="subjectAnyOrder"/>
6931 <xsl:template match="marc:datafield[@tag=630]">
6933 <xsl:call-template name="subjectAuthority"/>
6936 <xsl:call-template name="chopPunctuation">
6937 <xsl:with-param name="chopString">
6938 <xsl:call-template name="subfieldSelect">
6939 <xsl:with-param name="codes">adfhklor</xsl:with-param>
6940 </xsl:call-template>
6942 </xsl:call-template>
6944 <xsl:call-template name="part"/>
6946 <xsl:call-template name="subjectAnyOrder"/>
6949 <!-- 1.27 648 tmee-->
6950 <xsl:template match="marc:datafield[@tag=648]">
6952 <xsl:if test="marc:subfield[@code=2]">
6953 <xsl:attribute name="authority">
6954 <xsl:value-of select="marc:subfield[@code=2]"/>
6957 <xsl:call-template name="uri"/>
6959 <xsl:call-template name="subjectAuthority"/>
6961 <xsl:call-template name="chopPunctuation">
6962 <xsl:with-param name="chopString">
6963 <xsl:call-template name="subfieldSelect">
6964 <xsl:with-param name="codes">abcd</xsl:with-param>
6965 </xsl:call-template>
6967 </xsl:call-template>
6969 <xsl:call-template name="subjectAnyOrder"/>
6973 <xsl:template match="marc:datafield[@tag=650]">
6975 <xsl:call-template name="subjectAuthority"/>
6977 <xsl:call-template name="chopPunctuation">
6978 <xsl:with-param name="chopString">
6979 <xsl:call-template name="subfieldSelect">
6980 <xsl:with-param name="codes">abcd</xsl:with-param>
6981 </xsl:call-template>
6983 </xsl:call-template>
6985 <xsl:call-template name="subjectAnyOrder"/>
6988 <xsl:template match="marc:datafield[@tag=651]">
6990 <xsl:call-template name="subjectAuthority"/>
6991 <xsl:for-each select="marc:subfield[@code='a']">
6993 <xsl:call-template name="chopPunctuation">
6994 <xsl:with-param name="chopString" select="."/>
6995 </xsl:call-template>
6998 <xsl:call-template name="subjectAnyOrder"/>
7001 <xsl:template match="marc:datafield[@tag=653]">
7003 <xsl:for-each select="marc:subfield[@code='a']">
7005 <xsl:value-of select="."/>
7010 <xsl:template match="marc:datafield[@tag=656]">
7012 <xsl:if test="marc:subfield[@code=2]">
7013 <xsl:attribute name="authority">
7014 <xsl:value-of select="marc:subfield[@code=2]"/>
7018 <xsl:call-template name="chopPunctuation">
7019 <xsl:with-param name="chopString">
7020 <xsl:value-of select="marc:subfield[@code='a']"/>
7022 </xsl:call-template>
7026 <xsl:template name="termsOfAddress">
7027 <xsl:if test="marc:subfield[@code='b' or @code='c']">
7028 <namePart type="termsOfAddress">
7029 <xsl:call-template name="chopPunctuation">
7030 <xsl:with-param name="chopString">
7031 <xsl:call-template name="subfieldSelect">
7032 <xsl:with-param name="codes">bc</xsl:with-param>
7033 </xsl:call-template>
7035 </xsl:call-template>
7039 <xsl:template name="displayLabel">
7040 <xsl:if test="marc:subfield[@code='i']">
7041 <xsl:attribute name="displayLabel">
7042 <xsl:value-of select="marc:subfield[@code='i']"/>
7045 <xsl:if test="marc:subfield[@code='3']">
7046 <xsl:attribute name="displayLabel">
7047 <xsl:value-of select="marc:subfield[@code='3']"/>
7051 <xsl:template name="isInvalid">
7052 <xsl:param name="type"/>
7054 test="marc:subfield[@code='z'] or marc:subfield[@code='y'] or marc:subfield[@code='m']">
7056 <xsl:attribute name="type">
7057 <xsl:value-of select="$type"/>
7059 <xsl:attribute name="invalid">
7060 <xsl:text>yes</xsl:text>
7062 <xsl:if test="marc:subfield[@code='z']">
7063 <xsl:value-of select="marc:subfield[@code='z']"/>
7065 <xsl:if test="marc:subfield[@code='y']">
7066 <xsl:value-of select="marc:subfield[@code='y']"/>
7068 <xsl:if test="marc:subfield[@code='m']">
7069 <xsl:value-of select="marc:subfield[@code='m']"/>
7074 <xsl:template name="subtitle">
7075 <xsl:if test="marc:subfield[@code='b']">
7077 <xsl:call-template name="chopPunctuation">
7078 <xsl:with-param name="chopString">
7079 <xsl:value-of select="marc:subfield[@code='b']"/>
7080 <!--<xsl:call-template name="subfieldSelect">
7081 <xsl:with-param name="codes">b</xsl:with-param>
7082 </xsl:call-template>-->
7084 </xsl:call-template>
7088 <xsl:template name="script">
7089 <xsl:param name="scriptCode"/>
7090 <xsl:attribute name="script">
7092 <xsl:when test="$scriptCode='(3'">Arabic</xsl:when>
7093 <xsl:when test="$scriptCode='(B'">Latin</xsl:when>
7094 <xsl:when test="$scriptCode='$1'">Chinese, Japanese, Korean</xsl:when>
7095 <xsl:when test="$scriptCode='(N'">Cyrillic</xsl:when>
7096 <xsl:when test="$scriptCode='(2'">Hebrew</xsl:when>
7097 <xsl:when test="$scriptCode='(S'">Greek</xsl:when>
7101 <xsl:template name="parsePart">
7102 <!-- assumes 773$q= 1:2:3<4
7103 with up to 3 levels and one optional start page
7105 <xsl:variable name="level1">
7107 <xsl:when test="contains(text(),':')">
7109 <xsl:value-of select="substring-before(text(),':')"/>
7111 <xsl:when test="not(contains(text(),':'))">
7113 <xsl:if test="contains(text(),'<')">
7115 <xsl:value-of select="substring-before(text(),'<')"/>
7117 <xsl:if test="not(contains(text(),'<'))">
7119 <xsl:value-of select="text()"/>
7124 <xsl:variable name="sici2">
7126 <xsl:when test="starts-with(substring-after(text(),$level1),':')">
7127 <xsl:value-of select="substring(substring-after(text(),$level1),2)"/>
7130 <xsl:value-of select="substring-after(text(),$level1)"/>
7134 <xsl:variable name="level2">
7136 <xsl:when test="contains($sici2,':')">
7138 <xsl:value-of select="substring-before($sici2,':')"/>
7140 <xsl:when test="contains($sici2,'<')">
7142 <xsl:value-of select="substring-before($sici2,'<')"/>
7145 <xsl:value-of select="$sici2"/>
7150 <xsl:variable name="sici3">
7152 <xsl:when test="starts-with(substring-after($sici2,$level2),':')">
7153 <xsl:value-of select="substring(substring-after($sici2,$level2),2)"/>
7156 <xsl:value-of select="substring-after($sici2,$level2)"/>
7160 <xsl:variable name="level3">
7162 <xsl:when test="contains($sici3,'<')">
7164 <xsl:value-of select="substring-before($sici3,'<')"/>
7167 <xsl:value-of select="$sici3"/>
7172 <xsl:variable name="page">
7173 <xsl:if test="contains(text(),'<')">
7174 <xsl:value-of select="substring-after(text(),'<')"/>
7177 <xsl:if test="$level1">
7180 <xsl:value-of select="$level1"/>
7184 <xsl:if test="$level2">
7187 <xsl:value-of select="$level2"/>
7191 <xsl:if test="$level3">
7194 <xsl:value-of select="$level3"/>
7198 <xsl:if test="$page">
7199 <extent unit="page">
7201 <xsl:value-of select="$page"/>
7206 <xsl:template name="getLanguage">
7207 <xsl:param name="langString"/>
7208 <xsl:param name="controlField008-35-37"/>
7209 <xsl:variable name="length" select="string-length($langString)"/>
7211 <xsl:when test="$length=0"/>
7212 <xsl:when test="$controlField008-35-37=substring($langString,1,3)">
7213 <xsl:call-template name="getLanguage">
7214 <xsl:with-param name="langString" select="substring($langString,4,$length)"/>
7215 <xsl:with-param name="controlField008-35-37" select="$controlField008-35-37"/>
7216 </xsl:call-template>
7220 <languageTerm authority="iso639-2b" type="code">
7221 <xsl:value-of select="substring($langString,1,3)"/>
7224 <xsl:call-template name="getLanguage">
7225 <xsl:with-param name="langString" select="substring($langString,4,$length)"/>
7226 <xsl:with-param name="controlField008-35-37" select="$controlField008-35-37"/>
7227 </xsl:call-template>
7231 <xsl:template name="isoLanguage">
7232 <xsl:param name="currentLanguage"/>
7233 <xsl:param name="usedLanguages"/>
7234 <xsl:param name="remainingLanguages"/>
7236 <xsl:when test="string-length($currentLanguage)=0"/>
7237 <xsl:when test="not(contains($usedLanguages, $currentLanguage))">
7239 <xsl:if test="@code!='a'">
7240 <xsl:attribute name="objectPart">
7242 <xsl:when test="@code='b'">summary or subtitle</xsl:when>
7243 <xsl:when test="@code='d'">sung or spoken text</xsl:when>
7244 <xsl:when test="@code='e'">libretto</xsl:when>
7245 <xsl:when test="@code='f'">table of contents</xsl:when>
7246 <xsl:when test="@code='g'">accompanying material</xsl:when>
7247 <xsl:when test="@code='h'">translation</xsl:when>
7251 <languageTerm authority="iso639-2b" type="code">
7252 <xsl:value-of select="$currentLanguage"/>
7255 <xsl:call-template name="isoLanguage">
7256 <xsl:with-param name="currentLanguage">
7257 <xsl:value-of select="substring($remainingLanguages,1,3)"/>
7259 <xsl:with-param name="usedLanguages">
7260 <xsl:value-of select="concat($usedLanguages,$currentLanguage)"/>
7262 <xsl:with-param name="remainingLanguages">
7264 select="substring($remainingLanguages,4,string-length($remainingLanguages))"
7267 </xsl:call-template>
7270 <xsl:call-template name="isoLanguage">
7271 <xsl:with-param name="currentLanguage">
7272 <xsl:value-of select="substring($remainingLanguages,1,3)"/>
7274 <xsl:with-param name="usedLanguages">
7275 <xsl:value-of select="concat($usedLanguages,$currentLanguage)"/>
7277 <xsl:with-param name="remainingLanguages">
7279 select="substring($remainingLanguages,4,string-length($remainingLanguages))"
7282 </xsl:call-template>
7286 <xsl:template name="chopBrackets">
7287 <xsl:param name="chopString"/>
7288 <xsl:variable name="string">
7289 <xsl:call-template name="chopPunctuation">
7290 <xsl:with-param name="chopString" select="$chopString"/>
7291 </xsl:call-template>
7293 <xsl:if test="substring($string, 1,1)='['">
7294 <xsl:value-of select="substring($string,2, string-length($string)-2)"/>
7296 <xsl:if test="substring($string, 1,1)!='['">
7297 <xsl:value-of select="$string"/>
7300 <xsl:template name="rfcLanguages">
7301 <xsl:param name="nodeNum"/>
7302 <xsl:param name="usedLanguages"/>
7303 <xsl:param name="controlField008-35-37"/>
7304 <xsl:variable name="currentLanguage" select="."/>
7306 <xsl:when test="not($currentLanguage)"/>
7308 test="$currentLanguage!=$controlField008-35-37 and $currentLanguage!='rfc3066'">
7309 <xsl:if test="not(contains($usedLanguages,$currentLanguage))">
7311 <xsl:if test="@code!='a'">
7312 <xsl:attribute name="objectPart">
7314 <xsl:when test="@code='b'">summary or subtitle</xsl:when>
7315 <xsl:when test="@code='d'">sung or spoken text</xsl:when>
7316 <xsl:when test="@code='e'">libretto</xsl:when>
7317 <xsl:when test="@code='f'">table of contents</xsl:when>
7318 <xsl:when test="@code='g'">accompanying material</xsl:when>
7319 <xsl:when test="@code='h'">translation</xsl:when>
7323 <languageTerm authority="rfc3066" type="code">
7324 <xsl:value-of select="$currentLanguage"/>
7329 <xsl:otherwise> </xsl:otherwise>
7333 <xsl:template name="datafield">
7334 <xsl:param name="tag"/>
7335 <xsl:param name="ind1">
7336 <xsl:text> </xsl:text>
7338 <xsl:param name="ind2">
7339 <xsl:text> </xsl:text>
7341 <xsl:param name="subfields"/>
7342 <xsl:element name="marc:datafield">
7343 <xsl:attribute name="tag">
7344 <xsl:value-of select="$tag"/>
7346 <xsl:attribute name="ind1">
7347 <xsl:value-of select="$ind1"/>
7349 <xsl:attribute name="ind2">
7350 <xsl:value-of select="$ind2"/>
7352 <xsl:copy-of select="$subfields"/>
7356 <xsl:template name="subfieldSelect">
7357 <xsl:param name="codes">abcdefghijklmnopqrstuvwxyz</xsl:param>
7358 <xsl:param name="delimeter">
7359 <xsl:text> </xsl:text>
7361 <xsl:variable name="str">
7362 <xsl:for-each select="marc:subfield">
7363 <xsl:if test="contains($codes, @code)">
7364 <xsl:value-of select="text()"/>
7365 <xsl:value-of select="$delimeter"/>
7369 <xsl:value-of select="substring($str,1,string-length($str)-string-length($delimeter))"/>
7372 <xsl:template name="buildSpaces">
7373 <xsl:param name="spaces"/>
7374 <xsl:param name="char">
7375 <xsl:text> </xsl:text>
7377 <xsl:if test="$spaces>0">
7378 <xsl:value-of select="$char"/>
7379 <xsl:call-template name="buildSpaces">
7380 <xsl:with-param name="spaces" select="$spaces - 1"/>
7381 <xsl:with-param name="char" select="$char"/>
7382 </xsl:call-template>
7386 <xsl:template name="chopPunctuation">
7387 <xsl:param name="chopString"/>
7388 <xsl:param name="punctuation">
7389 <xsl:text>.:,;/ </xsl:text>
7391 <xsl:variable name="length" select="string-length($chopString)"/>
7393 <xsl:when test="$length=0"/>
7394 <xsl:when test="contains($punctuation, substring($chopString,$length,1))">
7395 <xsl:call-template name="chopPunctuation">
7396 <xsl:with-param name="chopString" select="substring($chopString,1,$length - 1)"/>
7397 <xsl:with-param name="punctuation" select="$punctuation"/>
7398 </xsl:call-template>
7400 <xsl:when test="not($chopString)"/>
7402 <xsl:value-of select="$chopString"/>
7407 <xsl:template name="chopPunctuationFront">
7408 <xsl:param name="chopString"/>
7409 <xsl:variable name="length" select="string-length($chopString)"/>
7411 <xsl:when test="$length=0"/>
7412 <xsl:when test="contains('.:,;/[ ', substring($chopString,1,1))">
7413 <xsl:call-template name="chopPunctuationFront">
7414 <xsl:with-param name="chopString" select="substring($chopString,2,$length - 1)"
7416 </xsl:call-template>
7418 <xsl:when test="not($chopString)"/>
7420 <xsl:value-of select="$chopString"/>
7425 <xsl:template name="chopPunctuationBack">
7426 <xsl:param name="chopString"/>
7427 <xsl:param name="punctuation">
7428 <xsl:text>.:,;/] </xsl:text>
7430 <xsl:variable name="length" select="string-length($chopString)"/>
7432 <xsl:when test="$length=0"/>
7433 <xsl:when test="contains($punctuation, substring($chopString,$length,1))">
7434 <xsl:call-template name="chopPunctuation">
7435 <xsl:with-param name="chopString" select="substring($chopString,1,$length - 1)"/>
7436 <xsl:with-param name="punctuation" select="$punctuation"/>
7437 </xsl:call-template>
7439 <xsl:when test="not($chopString)"/>
7441 <xsl:value-of select="$chopString"/>
7446 <!-- nate added 12/14/2007 for lccn.loc.gov: url encode ampersand, etc. -->
7447 <xsl:template name="url-encode">
7449 <xsl:param name="str"/>
7451 <xsl:if test="$str">
7452 <xsl:variable name="first-char" select="substring($str,1,1)"/>
7454 <xsl:when test="contains($safe,$first-char)">
7455 <xsl:value-of select="$first-char"/>
7458 <xsl:variable name="codepoint">
7460 <xsl:when test="contains($ascii,$first-char)">
7462 select="string-length(substring-before($ascii,$first-char)) + 32"
7465 <xsl:when test="contains($latin1,$first-char)">
7467 select="string-length(substring-before($latin1,$first-char)) + 160"/>
7471 <xsl:message terminate="no">Warning: string contains a character
7472 that is out of range! Substituting "?".</xsl:message>
7473 <xsl:text>63</xsl:text>
7477 <xsl:variable name="hex-digit1"
7478 select="substring($hex,floor($codepoint div 16) + 1,1)"/>
7479 <xsl:variable name="hex-digit2" select="substring($hex,$codepoint mod 16 + 1,1)"/>
7480 <!-- <xsl:value-of select="concat('%',$hex-digit2)"/> -->
7481 <xsl:value-of select="concat('%',$hex-digit1,$hex-digit2)"/>
7484 <xsl:if test="string-length($str) > 1">
7485 <xsl:call-template name="url-encode">
7486 <xsl:with-param name="str" select="substring($str,2)"/>
7487 </xsl:call-template>
7491 </xsl:stylesheet>$$ WHERE name = 'mods33';
7493 ALTER TABLE actor.usr ALTER COLUMN juvenile SET NOT NULL;
7494 ALTER TABLE actor.usr_address ALTER COLUMN pending SET NOT NULL;
7495 ALTER TABLE config.circ_matrix_matchpoint ALTER COLUMN duration_rule SET NOT NULL;
7496 ALTER TABLE config.circ_matrix_matchpoint ALTER COLUMN recurring_fine_rule SET NOT NULL;
7497 ALTER TABLE config.circ_matrix_matchpoint ALTER COLUMN max_fine_rule SET NOT NULL;