87c0da1ca2af306a1c7babc429aaecc4e4bc7a2a
[working/Evergreen.git] / Open-ILS / src / sql / Pg / upgrade / XXXX.schema.rank_cp_visibility.sql
1 -- Evergreen DB patch XXXX.schema.rank_cp_visibility.sql
2 --
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
5 --
6 BEGIN;
7
8 -- check whether patch can be applied
9 SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version);
10
11 -- function is being expanded and renamed, so drop the old version
12 DROP FUNCTION IF EXISTS evergreen.rank_cp_status(INT);
13
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)
18 RETURNS INTEGER AS $$
19 DECLARE
20     copy asset.copy%ROWTYPE;
21 BEGIN
22     SELECT * INTO copy FROM asset.copy WHERE id = copy_id;
23     RETURN evergreen.rank_cp(copy);
24 END;
25 $$ LANGUAGE PLPGSQL STABLE;
26
27 CREATE OR REPLACE FUNCTION evergreen.rank_cp(copy asset.copy)
28 RETURNS INTEGER AS $$
29 DECLARE
30     rank INT;
31 BEGIN
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"
45     )
46     SELECT COALESCE(
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))
51         END,
52         100
53     ) INTO rank;
54
55     RETURN rank;
56 END;
57 $$ LANGUAGE PLPGSQL STABLE;
58
59 CREATE OR REPLACE FUNCTION evergreen.ranked_volumes(
60     bibid BIGINT[], 
61     ouid INT,
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 (
69         SELECT COALESCE(
70             $3,
71             (
72                 SELECT depth
73                 FROM actor.org_unit_type aout
74                     INNER JOIN actor.org_unit ou ON ou_type = aout.id
75                 WHERE ou.id = $2
76             )
77         ) AS depth
78     ), descendant_depth AS (
79         SELECT  ou.id,
80                 ou.parent_ou,
81                 out.depth
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),
85                 ou_depth
86         WHERE ad.depth = ou_depth.depth
87             UNION ALL
88         SELECT  ou.id,
89                 ou.parent_ou,
90                 out.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 (
95         SELECT  ou.id,
96                 ou.parent_ou,
97                 out.depth
98         FROM  actor.org_unit ou
99                 JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
100         WHERE ou.id = $2
101             UNION ALL
102         SELECT  ou.id,
103                 ou.parent_ou,
104                 out.depth
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)
108     ), descendants as (
109         SELECT ou.* FROM actor.org_unit ou JOIN descendant_depth USING (id)
110     )
111
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),
115             RANK() OVER w
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 
123                 EXISTS (
124                     SELECT 1 
125                     FROM asset.opac_visible_copies 
126                     WHERE copy_id = acp.id AND record = acn.record
127                 ) ELSE TRUE END
128         GROUP BY acn.id, evergreen.rank_cp(acp), aou.name, acn.label_sortkey, aou.id
129         WINDOW w AS (
130             ORDER BY 
131                 COALESCE(
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),
139                     1000
140                 ),
141                 evergreen.rank_cp(acp)
142         )
143     ) AS ua
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;
149
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$
151         SELECT  XMLELEMENT(
152                     name volume,
153                     XMLATTRIBUTES(
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
159                     ),
160                     unapi.aou( owning_lib, $2, 'owning_lib', evergreen.array_remove_item_by_value($4,'acn'), $5, $6, $7, $8),
161                     CASE 
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
168                                           FROM  asset.copy cp
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
175                                     )x)
176                                 )
177                             ELSE
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
182                                           FROM  asset.copy cp
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
189                                     )x)
190                                 )
191                             END
192                         ELSE NULL
193                     END,
194                     XMLELEMENT(
195                         name uris,
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)
197                     ),
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
201                 ) AS x
202           FROM  asset.call_number acn
203                 JOIN actor.org_unit o ON (o.id = acn.owning_lib)
204           WHERE acn.id = $1
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;
208
209 COMMIT;