2 * 'id' gives us insert sort order
3 * 'tag' is mapped to the last_xact_id at import
5 CREATE TABLE marcxml_import (id SERIAL PRIMARY KEY, marc TEXT, tag TEXT);
8 * Create an address for a given actor.org_unit
10 * The "address_type" parameter accepts a TEXT value that contains the
11 * strings 'mailing', 'interlibrary', 'billing', and 'holds' to enable
12 * you to provide granular control over which address is associated for
13 * each function; if given NULL, then all functions are associated with
14 * the incoming address.
16 * This will happily create duplicate addresses if given duplicate info.
18 CREATE FUNCTION evergreen.create_aou_address
19 (owning_lib INTEGER, street1 TEXT, street2 TEXT, city TEXT, state TEXT, country TEXT,
20 post_code TEXT, address_type TEXT)
23 INSERT INTO actor.org_address (org_unit, street1, street2, city, state, country, post_code)
24 VALUES ($1, $2, $3, $4, $5, $6, $7);
27 UPDATE actor.org_unit SET holds_address = currval('actor.org_address_id_seq'), ill_address = currval('actor.org_address_id_seq'), billing_address = currval('actor.org_address_id_seq'), mailing_address = currval('actor.org_address_id_seq') WHERE id = $1;
30 UPDATE actor.org_unit SET holds_address = currval('actor.org_address_id_seq') WHERE id = $1;
32 IF $8 ~ 'interlibrary' THEN
33 UPDATE actor.org_unit SET ill_address = currval('actor.org_address_id_seq') WHERE id = $1;
35 IF $8 ~ 'billing' THEN
36 UPDATE actor.org_unit SET billing_address = currval('actor.org_address_id_seq') WHERE id = $1;
38 IF $8 ~ 'mailing' THEN
39 UPDATE actor.org_unit SET mailing_address = currval('actor.org_address_id_seq') WHERE id = $1;
45 * create a callnumber for every bib record in the database,
46 * appending the bib ID to the callnumber label to differentiate.
47 * If set, 'bib_tag' will limit the inserted callnumbers to bibs
48 * whose last_xact_id matches bib_tag
50 CREATE FUNCTION evergreen.populate_call_number
51 (ownlib INTEGER, label TEXT, bib_tag TEXT, class INTEGER DEFAULT 1)
53 INSERT INTO asset.call_number (record, creator, editor, owning_lib, label, label_class)
54 SELECT id, 1, 1, $1, $2 || id::text, $4
55 FROM biblio.record_entry
57 CASE WHEN $3 IS NULL THEN TRUE
58 ELSE last_xact_id = $3 END;
61 CREATE FUNCTION evergreen.populate_call_number
62 (ownlib INTEGER, label TEXT, bib_tag TEXT)
64 SELECT evergreen.populate_call_number($1, $2, $3, NULL);
69 * create a copy for every callnumber in the database whose label and owning_lib
70 * matches, appending the callnumber ID to the copy barcode to differentate.
72 CREATE FUNCTION evergreen.populate_copy
73 (circlib INTEGER, ownlib INTEGER, barcode TEXT, label TEXT)
75 INSERT INTO asset.copy (call_number, circ_lib, creator, editor, loan_duration, fine_level, barcode)
76 SELECT id, $1, 1, 1, 1, 1, $3 || id::text
77 FROM asset.call_number
78 WHERE record > 0 AND label LIKE $4 || '%' AND owning_lib = $2;
81 /** Returns the next (by ID) non-deleted asset.copy */
82 CREATE FUNCTION evergreen.next_copy (copy_id BIGINT) RETURNS asset.copy AS $$
83 SELECT * FROM asset.copy
84 WHERE id > $1 AND NOT deleted
88 /** Returns the next (by ID) non-deleted biblio.record_entry */
89 CREATE FUNCTION evergreen.next_bib (bib_id BIGINT) RETURNS biblio.record_entry AS $$
90 SELECT * FROM biblio.record_entry
91 WHERE id > $1 AND NOT deleted
96 /** Create one circulation */
97 CREATE FUNCTION evergreen.populate_circ (
103 recurring_fine_rule TEXT,
109 DECLARE duration config.rule_circ_duration%ROWTYPE;
110 DECLARE recurring config.rule_recurring_fine%ROWTYPE;
111 DECLARE max_fine config.rule_max_fine%ROWTYPE;
112 DECLARE patron actor.usr%ROWTYPE;
113 DECLARE xact_base_date TIMESTAMP;
114 DECLARE due_date TIMESTAMP;
115 DECLARE xact_start TIMESTAMP;
118 SELECT INTO duration * FROM config.rule_circ_duration WHERE name = duration_rule;
119 SELECT INTO recurring * FROM config.rule_recurring_fine WHERE name = recurring_fine_rule;
120 SELECT INTO max_fine * FROM config.rule_max_fine WHERE name = max_fine_rule;
121 SELECT INTO patron * FROM actor.usr WHERE id = patron_id;
123 IF patron.expire_date < NOW() THEN
124 xact_base_date = patron.expire_date;
126 xact_base_date = NOW();
130 -- if duration is '7 days', the overdue item was due 7 days ago
131 due_date := xact_base_date - duration.normal;
132 -- make overdue circs appear as if they were created two durations ago
133 xact_start := xact_base_date - duration.normal - duration.normal;
135 due_date := xact_base_date + duration.normal;
136 xact_start := xact_base_date;
139 IF duration.normal >= '1 day'::INTERVAL THEN
140 due_date := (DATE(due_date) || ' 23:59:59')::TIMESTAMP;
143 INSERT INTO action.circulation (
144 xact_start, usr, target_copy, circ_lib, circ_staff, renewal_remaining,
145 grace_period, duration, recurring_fine, max_fine, duration_rule,
146 recurring_fine_rule, max_fine_rule, due_date )
153 duration.max_renewals,
154 recurring.grace_period,
164 -- mark copy as checked out
165 UPDATE asset.copy SET status = 1 WHERE id = copy_id;
172 /** Create one hold */
173 CREATE FUNCTION evergreen.populate_hold (
180 thawdate TIMESTAMP WITH TIME ZONE
183 INSERT INTO action.hold_request (
184 requestor, hold_type, target, usr, pickup_lib,
185 request_lib, selection_ou, frozen, thaw_date)