Open-ILS/src/sql/Pg/upgrade/0924.schema.rank_cp_visibility.sql

   1 -- Evergreen DB patch 0924.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('0924', :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;