LP#1234845: Stamping upgrade script for improved evergreen.ranked_volumes()
[working/Evergreen.git] / Open-ILS / src / sql / Pg / upgrade / 0885.function.ranked_volumes.sql
1 BEGIN;
2
3 SELECT evergreen.upgrade_deps_block_check('0885', :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, aou.name, acn.label_sortkey,
60             RANK() OVER w
61         FROM asset.call_number acn
62             JOIN asset.copy acp ON (acn.id = acp.call_number)
63             JOIN descendants AS aou ON (acp.circ_lib = aou.id)
64         WHERE acn.record = ANY ($1)
65             AND acn.deleted IS FALSE
66             AND acp.deleted IS FALSE
67             AND CASE WHEN ('exclude_invisible_acn' = ANY($7)) THEN
68                 EXISTS (
69                     SELECT 1
70                     FROM asset.opac_visible_copies
71                     WHERE copy_id = acp.id AND record = acn.record
72                 ) ELSE TRUE END
73         GROUP BY acn.id, acp.status, aou.name, acn.label_sortkey, aou.id
74         WINDOW w AS (
75             ORDER BY
76                 COALESCE(
77                     CASE WHEN aou.id = $2 THEN -20000 END,
78                     CASE WHEN aou.id = $6 THEN -10000 END,
79                     (SELECT distance - 5000
80                         FROM actor.org_unit_descendants_distance($6) as x
81                         WHERE x.id = aou.id AND $6 IN (
82                             SELECT q.id FROM actor.org_unit_descendants($2) as q)),
83                     (SELECT e.distance FROM actor.org_unit_descendants_distance($2) as e WHERE e.id = aou.id),
84                     1000
85                 ),
86                 evergreen.rank_cp_status(acp.status)
87         )
88     ) AS ua
89     GROUP BY ua.id, ua.name, ua.label_sortkey
90     ORDER BY rank, ua.name, ua.label_sortkey
91     LIMIT ($4 -> 'acn')::INT
92     OFFSET ($5 -> 'acn')::INT;
93 $$ LANGUAGE SQL STABLE ROWS 10;
94
95 CREATE OR REPLACE FUNCTION evergreen.ranked_volumes
96     ( bibid BIGINT, ouid INT, depth INT DEFAULT NULL, slimit HSTORE DEFAULT NULL, soffset HSTORE DEFAULT NULL, pref_lib INT DEFAULT NULL, includes TEXT[] DEFAULT NULL::TEXT[] )
97     RETURNS TABLE (id BIGINT, name TEXT, label_sortkey TEXT, rank BIGINT)
98     AS $$ SELECT * FROM evergreen.ranked_volumes(ARRAY[$1],$2,$3,$4,$5,$6,$7) $$ LANGUAGE SQL STABLE;
99
100 COMMIT;