1 -- Evergreen DB patch 0536.schema.lazy_circ-barcode_lookup.sql
3 -- FIXME: insert description of change, if needed
7 -- check whether patch can be applied
8 INSERT INTO config.upgrade_log (version) VALUES ('0536');
10 INSERT INTO config.org_unit_setting_type ( name, label, description, datatype) VALUES ( 'circ.staff_client.actor_on_checkout', 'Load patron from Checkout', 'When scanning barcodes into Checkout auto-detect if a new patron barcode is scanned and auto-load the new patron.', 'bool');
12 CREATE TABLE config.barcode_completion (
13 id SERIAL PRIMARY KEY,
14 active BOOL NOT NULL DEFAULT true,
15 org_unit INT NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
18 length INT NOT NULL DEFAULT 0,
20 padding_end BOOL NOT NULL DEFAULT false,
21 asset BOOL NOT NULL DEFAULT true,
22 actor BOOL NOT NULL DEFAULT true
25 CREATE TYPE evergreen.barcode_set AS (type TEXT, id BIGINT, barcode TEXT);
27 CREATE OR REPLACE FUNCTION evergreen.get_barcodes(select_ou INT, type TEXT, in_barcode TEXT) RETURNS SETOF evergreen.barcode_set AS $$
32 asset_barcodes TEXT[];
33 actor_barcodes TEXT[];
34 do_asset BOOL = false;
35 do_serial BOOL = false;
36 do_booking BOOL = false;
37 do_actor BOOL = false;
38 completion_set config.barcode_completion%ROWTYPE;
41 IF position('asset' in type) > 0 THEN
44 IF position('serial' in type) > 0 THEN
47 IF position('booking' in type) > 0 THEN
50 IF do_asset OR do_serial OR do_booking THEN
51 asset_barcodes = asset_barcodes || in_barcode;
53 IF position('actor' in type) > 0 THEN
55 actor_barcodes = actor_barcodes || in_barcode;
58 barcode_len := length(in_barcode);
61 SELECT * FROM config.barcode_completion
63 AND org_unit IN (SELECT aou.id FROM actor.org_unit_ancestors(select_ou) aou)
65 IF completion_set.prefix IS NULL THEN
66 completion_set.prefix := '';
68 IF completion_set.suffix IS NULL THEN
69 completion_set.suffix := '';
71 IF completion_set.length = 0 OR completion_set.padding IS NULL OR length(completion_set.padding) = 0 THEN
72 cur_barcode = completion_set.prefix || in_barcode || completion_set.suffix;
74 completion_len = completion_set.length - length(completion_set.prefix) - length(completion_set.suffix);
75 IF completion_len >= barcode_len THEN
76 IF completion_set.padding_end THEN
77 cur_barcode = rpad(in_barcode, completion_len, completion_set.padding);
79 cur_barcode = lpad(in_barcode, completion_len, completion_set.padding);
81 cur_barcode = completion_set.prefix || cur_barcode || completion_set.suffix;
84 IF completion_set.actor THEN
85 actor_barcodes = actor_barcodes || cur_barcode;
87 IF completion_set.asset THEN
88 asset_barcodes = asset_barcodes || cur_barcode;
92 IF do_asset AND do_serial THEN
93 RETURN QUERY SELECT 'asset'::TEXT, id, barcode FROM ONLY asset.copy WHERE barcode = ANY(asset_barcodes) AND deleted = false;
94 RETURN QUERY SELECT 'serial'::TEXT, id, barcode FROM serial.unit WHERE barcode = ANY(asset_barcodes) AND deleted = false;
96 RETURN QUERY SELECT 'asset'::TEXT, id, barcode FROM asset.copy WHERE barcode = ANY(asset_barcodes) AND deleted = false;
98 RETURN QUERY SELECT 'serial'::TEXT, id, barcode FROM serial.unit WHERE barcode = ANY(asset_barcodes) AND deleted = false;
101 RETURN QUERY SELECT 'booking'::TEXT, id::BIGINT, barcode FROM booking.resource WHERE barcode = ANY(asset_barcodes);
104 RETURN QUERY SELECT 'actor'::TEXT, c.usr::BIGINT, c.barcode FROM actor.card c JOIN actor.usr u ON c.usr = u.id WHERE c.barcode = ANY(actor_barcodes) AND c.active AND NOT u.deleted ORDER BY usr;
110 COMMENT ON FUNCTION evergreen.get_barcodes(INT, TEXT, TEXT) IS $$
111 Given user input, find an appropriate barcode in the proper class.
113 Will add prefix/suffix information to do so, and return all results.