1 -- Evergreen DB patch XXXX.schema.rank_cp_visibility.sql
3 -- rank_cp() is meant to return the most-available copies, so it needs to
4 -- factor in the opac_visible flag on the copies themselves
8 -- check whether patch can be applied
9 SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version);
11 -- function is being expanded and renamed, so drop the old version
12 DROP FUNCTION IF EXISTS evergreen.rank_cp_status(INT);
14 -- this version exists mainly to accommodate JSON query transform limitations
15 -- (the transform argument must be an IDL field, not an entire row/object)
16 -- XXX is there another way?
17 CREATE OR REPLACE FUNCTION evergreen.rank_cp(copy_id BIGINT)
20 copy asset.copy%ROWTYPE;
22 SELECT * INTO copy FROM asset.copy WHERE id = copy_id;
23 RETURN evergreen.rank_cp(copy);
25 $$ LANGUAGE PLPGSQL STABLE;
27 CREATE OR REPLACE FUNCTION evergreen.rank_cp(copy asset.copy)
32 WITH totally_available AS (
33 SELECT id, 0 AS avail_rank
34 FROM config.copy_status
35 WHERE opac_visible IS TRUE
36 AND copy_active IS TRUE
37 AND id != 1 -- "Checked out"
38 ), almost_available AS (
39 SELECT id, 10 AS avail_rank
40 FROM config.copy_status
41 WHERE holdable IS TRUE
42 AND opac_visible IS TRUE
43 AND copy_active IS FALSE
44 OR id = 1 -- "Checked out"
47 CASE WHEN NOT copy.opac_visible THEN 100 END,
48 (SELECT avail_rank FROM totally_available WHERE copy.status IN (id)),
49 CASE WHEN copy.holdable THEN
50 (SELECT avail_rank FROM almost_available WHERE copy.status IN (id))
57 $$ LANGUAGE PLPGSQL STABLE;
59 CREATE OR REPLACE FUNCTION evergreen.ranked_volumes(
62 depth INT DEFAULT NULL,
63 slimit HSTORE DEFAULT NULL,
64 soffset HSTORE DEFAULT NULL,
65 pref_lib INT DEFAULT NULL,
66 includes TEXT[] DEFAULT NULL::TEXT[]
67 ) RETURNS TABLE(id BIGINT, name TEXT, label_sortkey TEXT, rank BIGINT) AS $$
68 WITH RECURSIVE ou_depth AS (
73 FROM actor.org_unit_type aout
74 INNER JOIN actor.org_unit ou ON ou_type = aout.id
78 ), descendant_depth AS (
82 FROM actor.org_unit ou
83 JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
84 JOIN anscestor_depth ad ON (ad.id = ou.id),
86 WHERE ad.depth = ou_depth.depth
91 FROM actor.org_unit ou
92 JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
93 JOIN descendant_depth ot ON (ot.id = ou.parent_ou)
94 ), anscestor_depth AS (
98 FROM actor.org_unit ou
99 JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
105 FROM actor.org_unit ou
106 JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
107 JOIN anscestor_depth ot ON (ot.parent_ou = ou.id)
109 SELECT ou.* FROM actor.org_unit ou JOIN descendant_depth USING (id)
112 SELECT ua.id, ua.name, ua.label_sortkey, MIN(ua.rank) AS rank FROM (
113 SELECT acn.id, aou.name, acn.label_sortkey,
114 evergreen.rank_cp(acp),
116 FROM asset.call_number acn
117 JOIN asset.copy acp ON (acn.id = acp.call_number)
118 JOIN descendants AS aou ON (acp.circ_lib = aou.id)
119 WHERE acn.record = ANY ($1)
120 AND acn.deleted IS FALSE
121 AND acp.deleted IS FALSE
122 AND CASE WHEN ('exclude_invisible_acn' = ANY($7)) THEN
125 FROM asset.opac_visible_copies
126 WHERE copy_id = acp.id AND record = acn.record
128 GROUP BY acn.id, evergreen.rank_cp(acp), aou.name, acn.label_sortkey, aou.id
132 CASE WHEN aou.id = $2 THEN -20000 END,
133 CASE WHEN aou.id = $6 THEN -10000 END,
134 (SELECT distance - 5000
135 FROM actor.org_unit_descendants_distance($6) as x
136 WHERE x.id = aou.id AND $6 IN (
137 SELECT q.id FROM actor.org_unit_descendants($2) as q)),
138 (SELECT e.distance FROM actor.org_unit_descendants_distance($2) as e WHERE e.id = aou.id),
141 evergreen.rank_cp(acp)
144 GROUP BY ua.id, ua.name, ua.label_sortkey
145 ORDER BY rank, ua.name, ua.label_sortkey
146 LIMIT ($4 -> 'acn')::INT
147 OFFSET ($5 -> 'acn')::INT;
148 $$ LANGUAGE SQL STABLE ROWS 10;
150 CREATE OR REPLACE FUNCTION unapi.acn ( obj_id BIGINT, format TEXT, ename TEXT, includes TEXT[], org TEXT, depth INT DEFAULT NULL, slimit HSTORE DEFAULT NULL, soffset HSTORE DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE ) RETURNS XML AS $F$
154 CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
155 'tag:open-ils.org:U2@acn/' || acn.id AS id,
156 acn.id AS vol_id, o.shortname AS lib,
157 o.opac_visible AS opac_visible,
158 deleted, label, label_sortkey, label_class, record
160 unapi.aou( owning_lib, $2, 'owning_lib', evergreen.array_remove_item_by_value($4,'acn'), $5, $6, $7, $8),
162 WHEN ('acp' = ANY ($4)) THEN
163 CASE WHEN $6 IS NOT NULL THEN
164 XMLELEMENT( name copies,
165 (SELECT XMLAGG(acp ORDER BY rank_avail) FROM (
166 SELECT unapi.acp( cp.id, 'xml', 'copy', evergreen.array_remove_item_by_value($4,'acn'), $5, $6, $7, $8, FALSE),
167 evergreen.rank_cp(cp) AS rank_avail
169 JOIN actor.org_unit_descendants( (SELECT id FROM actor.org_unit WHERE shortname = $5), $6) aoud ON (cp.circ_lib = aoud.id)
170 WHERE cp.call_number = acn.id
171 AND cp.deleted IS FALSE
172 ORDER BY rank_avail, COALESCE(cp.copy_number,0), cp.barcode
173 LIMIT ($7 -> 'acp')::INT
174 OFFSET ($8 -> 'acp')::INT
178 XMLELEMENT( name copies,
179 (SELECT XMLAGG(acp ORDER BY rank_avail) FROM (
180 SELECT unapi.acp( cp.id, 'xml', 'copy', evergreen.array_remove_item_by_value($4,'acn'), $5, $6, $7, $8, FALSE),
181 evergreen.rank_cp(cp) AS rank_avail
183 JOIN actor.org_unit_descendants( (SELECT id FROM actor.org_unit WHERE shortname = $5) ) aoud ON (cp.circ_lib = aoud.id)
184 WHERE cp.call_number = acn.id
185 AND cp.deleted IS FALSE
186 ORDER BY rank_avail, COALESCE(cp.copy_number,0), cp.barcode
187 LIMIT ($7 -> 'acp')::INT
188 OFFSET ($8 -> 'acp')::INT
196 (SELECT XMLAGG(auri) FROM (SELECT unapi.auri(uri,'xml','uri', evergreen.array_remove_item_by_value($4,'acn'), $5, $6, $7, $8, FALSE) FROM asset.uri_call_number_map WHERE call_number = acn.id)x)
198 unapi.acnp( acn.prefix, 'marcxml', 'prefix', evergreen.array_remove_item_by_value($4,'acn'), $5, $6, $7, $8, FALSE),
199 unapi.acns( acn.suffix, 'marcxml', 'suffix', evergreen.array_remove_item_by_value($4,'acn'), $5, $6, $7, $8, FALSE),
200 CASE WHEN ('bre' = ANY ($4)) THEN unapi.bre( acn.record, 'marcxml', 'record', evergreen.array_remove_item_by_value($4,'acn'), $5, $6, $7, $8, FALSE) ELSE NULL END
202 FROM asset.call_number acn
203 JOIN actor.org_unit o ON (o.id = acn.owning_lib)
205 AND acn.deleted IS FALSE
206 GROUP BY acn.id, o.shortname, o.opac_visible, deleted, label, label_sortkey, label_class, owning_lib, record, acn.prefix, acn.suffix;
207 $F$ LANGUAGE SQL STABLE;