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 a callnumber for every bib record in the database,
9 * appending the bib ID to the callnumber label to differentiate.
10 * If set, 'bib_tag' will limit the inserted callnumbers to bibs
11 * whose last_xact_id matches bib_tag
13 CREATE FUNCTION evergreen.populate_call_number
14 (ownlib INTEGER, label TEXT, bib_tag TEXT, class INTEGER DEFAULT 1)
16 INSERT INTO asset.call_number (record, creator, editor, owning_lib, label, label_class)
17 SELECT id, 1, 1, $1, $2 || id::text, $4
18 FROM biblio.record_entry
20 CASE WHEN $3 IS NULL THEN TRUE
21 ELSE last_xact_id = $3 END;
24 CREATE FUNCTION evergreen.populate_call_number
25 (ownlib INTEGER, label TEXT, bib_tag TEXT)
27 SELECT evergreen.populate_call_number($1, $2, $3, NULL);
32 * create a copy for every callnumber in the database whose label and owning_lib
33 * matches, appending the callnumber ID to the copy barcode to differentate.
35 CREATE FUNCTION evergreen.populate_copy
36 (circlib INTEGER, ownlib INTEGER, barcode TEXT, label TEXT)
38 INSERT INTO asset.copy (call_number, circ_lib, creator, editor, loan_duration, fine_level, barcode)
39 SELECT id, $1, 1, 1, 1, 1, $3 || id::text
40 FROM asset.call_number
41 WHERE record > 0 AND label LIKE $4 || '%' AND owning_lib = $2;
44 /** Returns the next (by ID) non-deleted asset.copy */
45 CREATE FUNCTION evergreen.next_copy (copy_id BIGINT) RETURNS asset.copy AS $$
46 SELECT * FROM asset.copy
47 WHERE id > $1 AND NOT deleted
51 /** Returns the next (by ID) non-deleted biblio.record_entry */
52 CREATE FUNCTION evergreen.next_bib (bib_id BIGINT) RETURNS biblio.record_entry AS $$
53 SELECT * FROM biblio.record_entry
54 WHERE id > $1 AND NOT deleted
59 /** Create one circulation */
60 CREATE FUNCTION evergreen.populate_circ (
66 recurring_fine_rule TEXT,
72 DECLARE duration config.rule_circ_duration%ROWTYPE;
73 DECLARE recurring config.rule_recurring_fine%ROWTYPE;
74 DECLARE max_fine config.rule_max_fine%ROWTYPE;
75 DECLARE due_date TIMESTAMP;
76 DECLARE xact_start TIMESTAMP;
79 SELECT INTO duration * FROM config.rule_circ_duration WHERE name = duration_rule;
80 SELECT INTO recurring * FROM config.rule_recurring_fine WHERE name = recurring_fine_rule;
81 SELECT INTO max_fine * FROM config.rule_max_fine WHERE name = max_fine_rule;
84 -- if duration is '7 days', the overdue item was due 7 days ago
85 due_date := NOW() - duration.normal;
86 -- make overdue circs appear as if they were created two durations ago
87 xact_start := NOW() - duration.normal - duration.normal;
89 due_date := NOW() + duration.normal;
93 IF duration.normal >= '1 day'::INTERVAL THEN
94 due_date := (DATE(due_date) || ' 23:59:59')::TIMESTAMP;
97 INSERT INTO action.circulation (
98 xact_start, usr, target_copy, circ_lib, circ_staff, renewal_remaining,
99 grace_period, duration, recurring_fine, max_fine, duration_rule,
100 recurring_fine_rule, max_fine_rule, due_date )
107 duration.max_renewals,
108 recurring.grace_period,
122 /** Create one hold */
123 CREATE FUNCTION evergreen.populate_hold (
130 thawdate TIMESTAMP WITH TIME ZONE
133 INSERT INTO action.hold_request (
134 requestor, hold_type, target, usr, pickup_lib,
135 request_lib, selection_ou, frozen, thaw_date)