Open-ILS/src/sql/Pg/upgrade/0991.function.unapi.ranked_volumes.sql

   1 BEGIN;
   2
   3 SELECT evergreen.upgrade_deps_block_check('0991', :eg_version);
   4
   5 CREATE OR REPLACE FUNCTION evergreen.ranked_volumes(
   6     bibid BIGINT[],
   7     ouid INT,
   8     depth INT DEFAULT NULL,
   9     slimit HSTORE DEFAULT NULL,
  10     soffset HSTORE DEFAULT NULL,
  11     pref_lib INT DEFAULT NULL,
  12     includes TEXT[] DEFAULT NULL::TEXT[]
  13 ) RETURNS TABLE(id BIGINT, name TEXT, label_sortkey TEXT, rank BIGINT) AS $$
  14     WITH RECURSIVE ou_depth AS (
  15         SELECT COALESCE(
  16             $3,
  17             (
  18                 SELECT depth
  19                 FROM actor.org_unit_type aout
  20                     INNER JOIN actor.org_unit ou ON ou_type = aout.id
  21                 WHERE ou.id = $2
  22             )
  23         ) AS depth
  24     ), descendant_depth AS (
  25         SELECT  ou.id,
  26                 ou.parent_ou,
  27                 out.depth
  28         FROM  actor.org_unit ou
  29                 JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
  30                 JOIN anscestor_depth ad ON (ad.id = ou.id),
  31                 ou_depth
  32         WHERE ad.depth = ou_depth.depth
  33             UNION ALL
  34         SELECT  ou.id,
  35                 ou.parent_ou,
  36                 out.depth
  37         FROM  actor.org_unit ou
  38                 JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
  39                 JOIN descendant_depth ot ON (ot.id = ou.parent_ou)
  40     ), anscestor_depth AS (
  41         SELECT  ou.id,
  42                 ou.parent_ou,
  43                 out.depth
  44         FROM  actor.org_unit ou
  45                 JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
  46         WHERE ou.id = $2
  47             UNION ALL
  48         SELECT  ou.id,
  49                 ou.parent_ou,
  50                 out.depth
  51         FROM  actor.org_unit ou
  52                 JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
  53                 JOIN anscestor_depth ot ON (ot.parent_ou = ou.id)
  54     ), descendants as (
  55         SELECT ou.* FROM actor.org_unit ou JOIN descendant_depth USING (id)
  56     )
  57
  58     SELECT ua.id, ua.name, ua.label_sortkey, MIN(ua.rank) AS rank FROM (
  59         SELECT acn.id, owning_lib.name, acn.label_sortkey,
  60             evergreen.rank_cp(acp),
  61             RANK() OVER w
  62         FROM asset.call_number acn
  63             JOIN asset.copy acp ON (acn.id = acp.call_number)
  64             JOIN descendants AS aou ON (acp.circ_lib = aou.id)
  65             JOIN actor.org_unit AS owning_lib ON (acn.owning_lib = owning_lib.id)
  66         WHERE acn.record = ANY ($1)
  67             AND acn.deleted IS FALSE
  68             AND acp.deleted IS FALSE
  69             AND CASE WHEN ('exclude_invisible_acn' = ANY($7)) THEN
  70                 EXISTS (
  71                     SELECT 1
  72                     FROM asset.opac_visible_copies
  73                     WHERE copy_id = acp.id AND record = acn.record
  74                 ) ELSE TRUE END
  75         GROUP BY acn.id, evergreen.rank_cp(acp), owning_lib.name, acn.label_sortkey, aou.id
  76         WINDOW w AS (
  77             ORDER BY
  78                 COALESCE(
  79                     CASE WHEN aou.id = $2 THEN -20000 END,
  80                     CASE WHEN aou.id = $6 THEN -10000 END,
  81                     (SELECT distance - 5000
  82                         FROM actor.org_unit_descendants_distance($6) as x
  83                         WHERE x.id = aou.id AND $6 IN (
  84                             SELECT q.id FROM actor.org_unit_descendants($2) as q)),
  85                     (SELECT e.distance FROM actor.org_unit_descendants_distance($2) as e WHERE e.id = aou.id),
  86                     1000
  87                 ),
  88                 evergreen.rank_cp(acp)
  89         )
  90     ) AS ua
  91     GROUP BY ua.id, ua.name, ua.label_sortkey
  92     ORDER BY rank, ua.name, ua.label_sortkey
  93     LIMIT ($4 -> 'acn')::INT
  94     OFFSET ($5 -> 'acn')::INT;
  95 $$ LANGUAGE SQL STABLE ROWS 10;
  96
  97 COMMIT;
  98