2 * Copyright (C) 2014 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.
20 SELECT evergreen.upgrade_deps_block_check('0870', :eg_version);
22 CREATE OR REPLACE FUNCTION evergreen.located_uris (
25 pref_lib INT DEFAULT NULL
26 ) RETURNS TABLE (id BIGINT, name TEXT, label_sortkey TEXT, rank INT) AS $$
27 WITH all_orgs AS (SELECT COALESCE( enabled, FALSE ) AS flag FROM config.global_flag WHERE name = 'opac.located_uri.act_as_copy')
28 SELECT DISTINCT ON (id) * FROM (
29 SELECT acn.id, COALESCE(aou.name,aoud.name), acn.label_sortkey, evergreen.rank_ou(aou.id, $2, $3) AS pref_ou
30 FROM asset.call_number acn
31 INNER JOIN asset.uri_call_number_map auricnm ON acn.id = auricnm.call_number
32 INNER JOIN asset.uri auri ON auri.id = auricnm.uri
33 LEFT JOIN actor.org_unit_ancestors( COALESCE($3, $2) ) aou ON (acn.owning_lib = aou.id)
34 LEFT JOIN actor.org_unit_descendants( COALESCE($3, $2) ) aoud ON (acn.owning_lib = aoud.id),
36 WHERE acn.record = ANY ($1)
37 AND acn.deleted IS FALSE
38 AND auri.active IS TRUE
39 AND ((NOT all_orgs.flag AND aou.id IS NOT NULL) OR (all_orgs.flag AND COALESCE(aou.id,aoud.id) IS NOT NULL))
41 SELECT acn.id, COALESCE(aou.name,aoud.name) AS name, acn.label_sortkey, evergreen.rank_ou(aou.id, $2, $3) AS pref_ou
42 FROM asset.call_number acn
43 INNER JOIN asset.uri_call_number_map auricnm ON acn.id = auricnm.call_number
44 INNER JOIN asset.uri auri ON auri.id = auricnm.uri
45 LEFT JOIN actor.org_unit_ancestors( $2 ) aou ON (acn.owning_lib = aou.id)
46 LEFT JOIN actor.org_unit_descendants( $2 ) aoud ON (acn.owning_lib = aoud.id),
48 WHERE acn.record = ANY ($1)
49 AND acn.deleted IS FALSE
50 AND auri.active IS TRUE
51 AND ((NOT all_orgs.flag AND aou.id IS NOT NULL) OR (all_orgs.flag AND COALESCE(aou.id,aoud.id) IS NOT NULL)))x
52 ORDER BY id, pref_ou DESC;