From cc24a589d1cf674c16ed6156cd489098597d6724 Mon Sep 17 00:00:00 2001 From: Mike Rylander Date: Fri, 7 Feb 2014 10:27:11 -0500 Subject: [PATCH] Teach evergreen.located_uris() about the new URI visiblity flag Signed-off-by: Mike Rylander --- Open-ILS/src/sql/Pg/990.schema.unapi.sql | 13 ++++++--- .../XXXX.function.located_uris_act_as_copies.sql | 31 ++++++++++++++++++++++ 2 files changed, 41 insertions(+), 3 deletions(-) diff --git a/Open-ILS/src/sql/Pg/990.schema.unapi.sql b/Open-ILS/src/sql/Pg/990.schema.unapi.sql index 08562278d0..dd7eaea529 100644 --- a/Open-ILS/src/sql/Pg/990.schema.unapi.sql +++ b/Open-ILS/src/sql/Pg/990.schema.unapi.sql @@ -113,23 +113,30 @@ 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 $$ + WITH all_orgs AS (SELECT COALESCE( enabled, FALSE ) AS flag FROM config.global_flag WHERE name = 'opac.located_uri.act_as_copy') 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 - INNER JOIN actor.org_unit_ancestors( COALESCE($3, $2) ) aou ON (acn.owning_lib = aou.id) + LEFT JOIN actor.org_unit_ancestors( COALESCE($3, $2) ) aou ON (acn.owning_lib = aou.id) + LEFT JOIN actor.org_unit_descendants( COALESCE($3, $2) ) aoud ON (acn.owning_lib = aoud.id), + all_orgs WHERE acn.record = $1 AND acn.deleted IS FALSE AND auri.active IS TRUE + AND ((NOT all_orgs.flag AND aou.id IS NOT NULL) OR COALESCE(aou.id,aoud.id) IS NOT NULL) UNION 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 - INNER JOIN actor.org_unit_ancestors( $2 ) aou ON (acn.owning_lib = aou.id) + LEFT JOIN actor.org_unit_ancestors( $2 ) aou ON (acn.owning_lib = aou.id) + LEFT JOIN actor.org_unit_descendants( $2 ) aoud ON (acn.owning_lib = aoud.id), + all_orgs WHERE acn.record = $1 AND acn.deleted IS FALSE - AND auri.active IS TRUE; + AND auri.active IS TRUE + AND ((NOT all_orgs.flag AND aou.id IS NOT NULL) OR COALESCE(aou.id,aoud.id) IS NOT NULL) $$ LANGUAGE SQL STABLE; diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.function.located_uris_act_as_copies.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.function.located_uris_act_as_copies.sql index 0a1ea2b57c..6c1a968a49 100644 --- a/Open-ILS/src/sql/Pg/upgrade/XXXX.function.located_uris_act_as_copies.sql +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.function.located_uris_act_as_copies.sql @@ -29,6 +29,37 @@ VALUES ( ) ); +CREATE OR REPLACE FUNCTION evergreen.located_uris ( + bibid BIGINT, + ouid INT, + pref_lib INT DEFAULT NULL +) RETURNS TABLE (id BIGINT, name TEXT, label_sortkey TEXT, rank INT) AS $$ + WITH all_orgs AS (SELECT COALESCE( enabled, FALSE ) AS flag FROM config.global_flag WHERE name = 'opac.located_uri.act_as_copy') + 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 + LEFT JOIN actor.org_unit_ancestors( COALESCE($3, $2) ) aou ON (acn.owning_lib = aou.id) + LEFT JOIN actor.org_unit_descendants( COALESCE($3, $2) ) aoud ON (acn.owning_lib = aoud.id), + all_orgs + WHERE acn.record = $1 + AND acn.deleted IS FALSE + AND auri.active IS TRUE + AND ((NOT all_orgs.flag AND aou.id IS NOT NULL) OR COALESCE(aou.id,aoud.id) IS NOT NULL) + UNION + 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 + LEFT JOIN actor.org_unit_ancestors( $2 ) aou ON (acn.owning_lib = aou.id) + LEFT JOIN actor.org_unit_descendants( $2 ) aoud ON (acn.owning_lib = aoud.id), + all_orgs + WHERE acn.record = $1 + AND acn.deleted IS FALSE + AND auri.active IS TRUE + AND ((NOT all_orgs.flag AND aou.id IS NOT NULL) OR COALESCE(aou.id,aoud.id) IS NOT NULL) +$$ +LANGUAGE SQL STABLE; CREATE OR REPLACE FUNCTION search.query_parser_fts ( -- 2.11.0