BEGIN; SELECT evergreen.upgrade_deps_block_check('0770', :eg_version); CREATE OR REPLACE FUNCTION evergreen.get_barcodes(select_ou INT, type TEXT, in_barcode TEXT) RETURNS SETOF evergreen.barcode_set AS $$ DECLARE cur_barcode TEXT; barcode_len INT; completion_len INT; asset_barcodes TEXT[]; actor_barcodes TEXT[]; do_asset BOOL = false; do_serial BOOL = false; do_booking BOOL = false; do_actor BOOL = false; completion_set config.barcode_completion%ROWTYPE; BEGIN IF position('asset' in type) > 0 THEN do_asset = true; END IF; IF position('serial' in type) > 0 THEN do_serial = true; END IF; IF position('booking' in type) > 0 THEN do_booking = true; END IF; IF do_asset OR do_serial OR do_booking THEN asset_barcodes = asset_barcodes || in_barcode; END IF; IF position('actor' in type) > 0 THEN do_actor = true; actor_barcodes = actor_barcodes || in_barcode; END IF; barcode_len := length(in_barcode); FOR completion_set IN SELECT * FROM config.barcode_completion WHERE active AND org_unit IN (SELECT aou.id FROM actor.org_unit_ancestors(select_ou) aou) LOOP IF completion_set.prefix IS NULL THEN completion_set.prefix := ''; END IF; IF completion_set.suffix IS NULL THEN completion_set.suffix := ''; END IF; IF completion_set.length = 0 OR completion_set.padding IS NULL OR length(completion_set.padding) = 0 THEN cur_barcode = completion_set.prefix || in_barcode || completion_set.suffix; ELSE completion_len = completion_set.length - length(completion_set.prefix) - length(completion_set.suffix); IF completion_len >= barcode_len THEN IF completion_set.padding_end THEN cur_barcode = rpad(in_barcode, completion_len, completion_set.padding); ELSE cur_barcode = lpad(in_barcode, completion_len, completion_set.padding); END IF; cur_barcode = completion_set.prefix || cur_barcode || completion_set.suffix; END IF; END IF; IF completion_set.actor THEN actor_barcodes = actor_barcodes || cur_barcode; END IF; IF completion_set.asset THEN asset_barcodes = asset_barcodes || cur_barcode; END IF; END LOOP; IF do_asset AND do_serial THEN RETURN QUERY SELECT 'asset'::TEXT, id, barcode FROM ONLY asset.copy WHERE barcode = ANY(asset_barcodes) AND deleted = false; RETURN QUERY SELECT 'serial'::TEXT, id, barcode FROM serial.unit WHERE barcode = ANY(asset_barcodes) AND deleted = false; ELSIF do_asset THEN RETURN QUERY SELECT 'asset'::TEXT, id, barcode FROM asset.copy WHERE barcode = ANY(asset_barcodes) AND deleted = false; ELSIF do_serial THEN RETURN QUERY SELECT 'serial'::TEXT, id, barcode FROM serial.unit WHERE barcode = ANY(asset_barcodes) AND deleted = false; END IF; IF do_booking THEN RETURN QUERY SELECT 'booking'::TEXT, id::BIGINT, barcode FROM booking.resource WHERE barcode = ANY(asset_barcodes); END IF; IF do_actor THEN 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) OR c.barcode = in_barcode) AND NOT u.deleted ORDER BY usr; END IF; RETURN; END; $$ LANGUAGE plpgsql; COMMIT;