From 97776542e5cf8f6cc9adf0ad4830ffa20c1bf205 Mon Sep 17 00:00:00 2001
From: Dan Scott <dan@coffeecode.net>
Date: Wed, 21 Mar 2012 00:14:56 -0400
Subject: [PATCH] TPAC: Sort copies from preferred library first
This commit makes copies from the preferred library (if any) sort first
in the displayed copy list in search results and record details, even
if the search scope would otherwise not include the preferred library's
copies at all.
Signed-off-by: Dan Scott <dan@coffeecode.net>
Signed-off-by: Jason Stephenson <jstephenson@mvlc.org>
Signed-off-by: Mike Rylander <mrylander@gmail.com>
---
.../perlmods/lib/OpenILS/WWW/EGCatLoader/Record.pm | 12 +++++--
Open-ILS/src/sql/Pg/020.schema.functions.sql | 15 ++++++++
Open-ILS/src/sql/Pg/990.schema.unapi.sql | 24 ++++++-------
.../Pg/upgrade/XXXX.schema.unapi_limit_rank.sql | 40 ++++++++++++++--------
4 files changed, 61 insertions(+), 30 deletions(-)
diff --git a/Open-ILS/src/perlmods/lib/OpenILS/WWW/EGCatLoader/Record.pm b/Open-ILS/src/perlmods/lib/OpenILS/WWW/EGCatLoader/Record.pm
index 307e321dc5..62133c99f7 100644
--- a/Open-ILS/src/perlmods/lib/OpenILS/WWW/EGCatLoader/Record.pm
+++ b/Open-ILS/src/perlmods/lib/OpenILS/WWW/EGCatLoader/Record.pm
@@ -212,7 +212,7 @@ sub mk_copy_query {
order_by => [
{ class => "aou", field => 'id',
- transform => 'evergreen.rank_ou', params => [$org]
+ transform => 'evergreen.rank_ou', params => [$org, $pref_ou]
},
{class => 'aou', field => 'name'},
{class => 'acn', field => 'label'},
@@ -235,7 +235,7 @@ sub mk_copy_query {
in => {
select => {aou => [{
column => 'id',
- transform => 'actor.org_unit_descendants',
+ transform => 'actor.org_unit_descendants',
result_field => 'id',
params => [$depth]
}]},
@@ -244,6 +244,14 @@ sub mk_copy_query {
}
}
}
+ };
+ if ($org != $pref_ou) {
+ $query->{from}->{acp}->{aou}->{filter}->{id}->{in}->{select}->{aou} = [{
+ column => 'id',
+ transform => 'actor.org_unit_descendants_pref_lib',
+ result_field => 'id',
+ params => [$depth, $pref_ou]
+ }];
}
};
diff --git a/Open-ILS/src/sql/Pg/020.schema.functions.sql b/Open-ILS/src/sql/Pg/020.schema.functions.sql
index ca4306eb2e..35afbcdb58 100644
--- a/Open-ILS/src/sql/Pg/020.schema.functions.sql
+++ b/Open-ILS/src/sql/Pg/020.schema.functions.sql
@@ -152,6 +152,21 @@ CREATE OR REPLACE FUNCTION actor.org_unit_descendants( INT ) RETURNS SETOF actor
) SELECT ou.* FROM actor.org_unit ou JOIN descendant_depth USING (id);
$$ LANGUAGE SQL ROWS 1;
+CREATE OR REPLACE FUNCTION actor.org_unit_descendants_pref_lib (
+ ou INT,
+ depth INT,
+ pref_ou INT
+) RETURNS SETOF actor.org_unit AS $$
+ SELECT ou.* FROM actor.org_unit_descendants($1, $2) AS ou
+ UNION
+ SELECT ou.* FROM actor.org_unit ou WHERE id = $3;
+$$ LANGUAGE SQL STABLE ROWS 1;
+
+COMMENT ON FUNCTION actor.org_unit_descendants_pref_lib( INT, INT, INT) IS $$
+Returns the descendants by depth of the specified library, but adds the
+preferred library if it is not contained in the set of descendants.
+$$;
+
CREATE OR REPLACE FUNCTION actor.org_unit_descendants_distance( INT ) RETURNS TABLE (id INT, distance INT) AS $$
WITH RECURSIVE org_unit_descendants_distance(id, distance) AS (
SELECT $1, 0
diff --git a/Open-ILS/src/sql/Pg/990.schema.unapi.sql b/Open-ILS/src/sql/Pg/990.schema.unapi.sql
index bac6a48472..45eca9f99d 100644
--- a/Open-ILS/src/sql/Pg/990.schema.unapi.sql
+++ b/Open-ILS/src/sql/Pg/990.schema.unapi.sql
@@ -14,22 +14,18 @@ RETURNS anyarray AS $$
SELECT ARRAY_ACCUM(x.e) FROM UNNEST( $1 ) x(e) WHERE x.e <> $2;
$$ LANGUAGE SQL STABLE;
-DROP FUNCTION IF EXISTS evergreen.rank_ou(INT, INT, INT);
CREATE OR REPLACE FUNCTION evergreen.rank_ou(lib INT, search_lib INT, pref_lib INT DEFAULT NULL)
RETURNS INTEGER AS $$
- WITH pref_libs AS (
- SELECT id, distance FROM actor.org_unit_descendants_distance(COALESCE($3, $2))
- ), search_libs AS (
+ WITH search_libs AS (
SELECT id, distance FROM actor.org_unit_descendants_distance($2)
)
SELECT COALESCE(
- (SELECT distance - 10000 FROM pref_libs WHERE id = $1),
+ (SELECT -10000 FROM actor.org_unit WHERE $1 = $3 AND id = $3),
(SELECT distance FROM search_libs WHERE id = $1),
10000
);
$$ LANGUAGE SQL STABLE;
-DROP FUNCTION IF EXISTS evergreen.rank_cp_status(INT);
CREATE OR REPLACE FUNCTION evergreen.rank_cp_status(status INT)
RETURNS INTEGER AS $$
WITH totally_available AS (
@@ -58,11 +54,12 @@ CREATE OR REPLACE FUNCTION evergreen.ranked_volumes(
ouid INT,
depth INT DEFAULT NULL,
slimit HSTORE DEFAULT NULL,
- soffset HSTORE DEFAULT NULL
+ soffset HSTORE DEFAULT NULL,
+ pref_lib INT DEFAULT NULL
) RETURNS TABLE (id BIGINT, name TEXT, label_sortkey TEXT, rank BIGINT) AS $$
SELECT ua.id, ua.name, ua.label_sortkey, MIN(ua.rank) AS rank FROM (
SELECT acn.id, aou.name, acn.label_sortkey,
- evergreen.rank_ou(aou.id, $2), evergreen.rank_cp_status(acp.status),
+ evergreen.rank_ou(aou.id, $2, $6), evergreen.rank_cp_status(acp.status),
RANK() OVER w
FROM asset.call_number acn
JOIN asset.copy acp ON (acn.id = acp.call_number)
@@ -72,14 +69,14 @@ CREATE OR REPLACE FUNCTION evergreen.ranked_volumes(
FROM actor.org_unit_type aout
INNER JOIN actor.org_unit ou ON ou_type = aout.id
WHERE ou.id = $2
- ))
+ ), $6)
) AS aou ON (acp.circ_lib = aou.id)
WHERE acn.record = $1
AND acn.deleted IS FALSE
AND acp.deleted IS FALSE
GROUP BY acn.id, acp.status, aou.name, acn.label_sortkey, aou.id
WINDOW w AS (
- ORDER BY evergreen.rank_ou(aou.id, $2), evergreen.rank_cp_status(acp.status)
+ ORDER BY evergreen.rank_ou(aou.id, $2, $6), evergreen.rank_cp_status(acp.status)
)
) AS ua
GROUP BY ua.id, ua.name, ua.label_sortkey
@@ -94,7 +91,7 @@ CREATE OR REPLACE FUNCTION evergreen.located_uris (
ouid INT,
pref_lib INT DEFAULT NULL
) RETURNS TABLE (id BIGINT, name TEXT, label_sortkey TEXT, rank INT) AS $$
- SELECT acn.id, aou.name, acn.label_sortkey, evergreen.rank_ou(aou.id, $2) AS pref_ou
+ SELECT acn.id, aou.name, acn.label_sortkey, evergreen.rank_ou(aou.id, $2, $3) AS pref_ou
FROM asset.call_number acn
INNER JOIN asset.uri_call_number_map auricnm ON acn.id = auricnm.call_number
INNER JOIN asset.uri auri ON auri.id = auricnm.uri
@@ -103,7 +100,7 @@ CREATE OR REPLACE FUNCTION evergreen.located_uris (
AND acn.deleted IS FALSE
AND auri.active IS TRUE
UNION
- SELECT acn.id, aou.name, acn.label_sortkey, evergreen.rank_ou(aou.id, $2) AS pref_ou
+ SELECT acn.id, aou.name, acn.label_sortkey, evergreen.rank_ou(aou.id, $2, $3) AS pref_ou
FROM asset.call_number acn
INNER JOIN asset.uri_call_number_map auricnm ON acn.id = auricnm.call_number
INNER JOIN asset.uri auri ON auri.id = auricnm.uri
@@ -439,7 +436,7 @@ RETURNS XML AS $F$
(SELECT XMLAGG(acn ORDER BY rank, name, label_sortkey) FROM (
-- Physical copies
SELECT unapi.acn(y.id,'xml','volume',evergreen.array_remove_item_by_value( evergreen.array_remove_item_by_value($5,'holdings_xml'),'bre'), $3, $4, $6, $7, FALSE), y.rank, name, label_sortkey
- FROM evergreen.ranked_volumes($1, $2, $4, $6, $7) AS y
+ FROM evergreen.ranked_volumes($1, $2, $4, $6, $7, $9) AS y
UNION ALL
-- Located URIs
SELECT unapi.acn(uris.id,'xml','volume',evergreen.array_remove_item_by_value( evergreen.array_remove_item_by_value($5,'holdings_xml'),'bre'), $3, $4, $6, $7, FALSE), 0, name, label_sortkey
@@ -1165,4 +1162,3 @@ EXPLAIN ANALYZE SELECT unapi.bre(36,'marcxml','record','{holdings_xml,mra,acp,ac
*/
COMMIT;
-
diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.unapi_limit_rank.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.unapi_limit_rank.sql
index 9daf37cc0b..5d71d972eb 100644
--- a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.unapi_limit_rank.sql
+++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.unapi_limit_rank.sql
@@ -19,6 +19,21 @@ BEGIN;
-- check whether patch can be applied
SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version);
+CREATE OR REPLACE FUNCTION actor.org_unit_descendants_pref_lib (
+ ou INT,
+ depth INT,
+ pref_ou INT
+) RETURNS SETOF actor.org_unit AS $$
+ SELECT ou.* FROM actor.org_unit_descendants($1, $2) AS ou
+ UNION
+ SELECT ou.* FROM actor.org_unit ou WHERE id = $3;
+$$ LANGUAGE SQL STABLE ROWS 1;
+
+COMMENT ON FUNCTION actor.org_unit_descendants_pref_lib( INT, INT, INT) IS $$
+Returns the descendants by depth of the specified library, but adds the
+preferred library if it is not contained in the set of descendants.
+$$;
+
-- The simplest way to apply all of these changes is just to replace the unapi
-- schema entirely -- the following is a copy of 990.schema.unapi.sql with
-- the initial COMMIT in place in case the upgrade_deps_block_check fails;
@@ -26,6 +41,7 @@ SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version);
-- transaction will also fail. Not graceful, but safe!
DROP SCHEMA IF EXISTS unapi CASCADE;
+BEGIN;
CREATE SCHEMA unapi;
CREATE OR REPLACE FUNCTION evergreen.org_top()
@@ -39,22 +55,18 @@ RETURNS anyarray AS $$
SELECT ARRAY_ACCUM(x.e) FROM UNNEST( $1 ) x(e) WHERE x.e <> $2;
$$ LANGUAGE SQL STABLE;
-DROP FUNCTION IF EXISTS evergreen.rank_ou(INT, INT, INT);
CREATE OR REPLACE FUNCTION evergreen.rank_ou(lib INT, search_lib INT, pref_lib INT DEFAULT NULL)
RETURNS INTEGER AS $$
- WITH pref_libs AS (
- SELECT id, distance FROM actor.org_unit_descendants_distance(COALESCE($3, $2))
- ), search_libs AS (
+ WITH search_libs AS (
SELECT id, distance FROM actor.org_unit_descendants_distance($2)
)
SELECT COALESCE(
- (SELECT distance - 10000 FROM pref_libs WHERE id = $1),
+ (SELECT -10000 FROM actor.org_unit WHERE $1 = $3 AND id = $3),
(SELECT distance FROM search_libs WHERE id = $1),
10000
);
$$ LANGUAGE SQL STABLE;
-DROP FUNCTION IF EXISTS evergreen.rank_cp_status(INT);
CREATE OR REPLACE FUNCTION evergreen.rank_cp_status(status INT)
RETURNS INTEGER AS $$
WITH totally_available AS (
@@ -83,11 +95,12 @@ CREATE OR REPLACE FUNCTION evergreen.ranked_volumes(
ouid INT,
depth INT DEFAULT NULL,
slimit HSTORE DEFAULT NULL,
- soffset HSTORE DEFAULT NULL
+ soffset HSTORE DEFAULT NULL,
+ pref_lib INT DEFAULT NULL
) RETURNS TABLE (id BIGINT, name TEXT, label_sortkey TEXT, rank BIGINT) AS $$
SELECT ua.id, ua.name, ua.label_sortkey, MIN(ua.rank) AS rank FROM (
SELECT acn.id, aou.name, acn.label_sortkey,
- evergreen.rank_ou(aou.id, $2), evergreen.rank_cp_status(acp.status),
+ evergreen.rank_ou(aou.id, $2, $6), evergreen.rank_cp_status(acp.status),
RANK() OVER w
FROM asset.call_number acn
JOIN asset.copy acp ON (acn.id = acp.call_number)
@@ -97,14 +110,14 @@ CREATE OR REPLACE FUNCTION evergreen.ranked_volumes(
FROM actor.org_unit_type aout
INNER JOIN actor.org_unit ou ON ou_type = aout.id
WHERE ou.id = $2
- ))
+ ), $6)
) AS aou ON (acp.circ_lib = aou.id)
WHERE acn.record = $1
AND acn.deleted IS FALSE
AND acp.deleted IS FALSE
GROUP BY acn.id, acp.status, aou.name, acn.label_sortkey, aou.id
WINDOW w AS (
- ORDER BY evergreen.rank_ou(aou.id, $2), evergreen.rank_cp_status(acp.status)
+ ORDER BY evergreen.rank_ou(aou.id, $2, $6), evergreen.rank_cp_status(acp.status)
)
) AS ua
GROUP BY ua.id, ua.name, ua.label_sortkey
@@ -119,7 +132,7 @@ CREATE OR REPLACE FUNCTION evergreen.located_uris (
ouid INT,
pref_lib INT DEFAULT NULL
) RETURNS TABLE (id BIGINT, name TEXT, label_sortkey TEXT, rank INT) AS $$
- SELECT acn.id, aou.name, acn.label_sortkey, evergreen.rank_ou(aou.id, $2) AS pref_ou
+ SELECT acn.id, aou.name, acn.label_sortkey, evergreen.rank_ou(aou.id, $2, $3) AS pref_ou
FROM asset.call_number acn
INNER JOIN asset.uri_call_number_map auricnm ON acn.id = auricnm.call_number
INNER JOIN asset.uri auri ON auri.id = auricnm.uri
@@ -128,7 +141,7 @@ CREATE OR REPLACE FUNCTION evergreen.located_uris (
AND acn.deleted IS FALSE
AND auri.active IS TRUE
UNION
- SELECT acn.id, aou.name, acn.label_sortkey, evergreen.rank_ou(aou.id, $2) AS pref_ou
+ SELECT acn.id, aou.name, acn.label_sortkey, evergreen.rank_ou(aou.id, $2, $3) AS pref_ou
FROM asset.call_number acn
INNER JOIN asset.uri_call_number_map auricnm ON acn.id = auricnm.call_number
INNER JOIN asset.uri auri ON auri.id = auricnm.uri
@@ -464,7 +477,7 @@ RETURNS XML AS $F$
(SELECT XMLAGG(acn ORDER BY rank, name, label_sortkey) FROM (
-- Physical copies
SELECT unapi.acn(y.id,'xml','volume',evergreen.array_remove_item_by_value( evergreen.array_remove_item_by_value($5,'holdings_xml'),'bre'), $3, $4, $6, $7, FALSE), y.rank, name, label_sortkey
- FROM evergreen.ranked_volumes($1, $2, $4, $6, $7) AS y
+ FROM evergreen.ranked_volumes($1, $2, $4, $6, $7, $9) AS y
UNION ALL
-- Located URIs
SELECT unapi.acn(uris.id,'xml','volume',evergreen.array_remove_item_by_value( evergreen.array_remove_item_by_value($5,'holdings_xml'),'bre'), $3, $4, $6, $7, FALSE), 0, name, label_sortkey
@@ -1190,4 +1203,3 @@ EXPLAIN ANALYZE SELECT unapi.bre(36,'marcxml','record','{holdings_xml,mra,acp,ac
*/
COMMIT;
-
--
2.11.0