From fb529800c65398d9192246bcec31763800767e77 Mon Sep 17 00:00:00 2001 From: Lebbeous Fogle-Weekley Date: Fri, 26 Apr 2013 18:00:02 -0400 Subject: [PATCH] TODO move this view into upgrade file, teach stuff in YYYY to use it, etc Signed-off-by: Lebbeous Fogle-Weekley --- grand-visibility.sql | 57 ++++++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 57 insertions(+) create mode 100644 grand-visibility.sql diff --git a/grand-visibility.sql b/grand-visibility.sql new file mode 100644 index 0000000000..ea61d43f4c --- /dev/null +++ b/grand-visibility.sql @@ -0,0 +1,57 @@ +CREATE OR REPLACE VIEW asset.visible_holdings AS +SELECT + bre.id AS record, + COALESCE( + aovc.circ_lib, acn_for_uri_map.owning_lib, acp_for_peer_bibs.circ_lib + ) AS ou, +-- aovc.copy_id, +-- aucnm.id AS uri_id, +-- FALSE as transcendant, +-- bre.deleted OR +-- NOT COALESCE(auri.active, TRUE) OR +-- COALESCE(acp_for_peer_bibs.deleted, FALSE) AS deleted, + COALESCE(aovc_acp.location, acp_for_peer_bibs.location) AS copy_location +FROM biblio.record_entry bre +LEFT JOIN asset.opac_visible_copies aovc + ON (aovc.record = bre.id) +LEFT JOIN asset.copy aovc_acp + ON (aovc.copy_id = aovc_acp.id) +LEFT JOIN asset.call_number acn_for_uri_map + ON (acn_for_uri_map.record = aovc.record) +LEFT JOIN asset.uri_call_number_map aucnm + ON (aucnm.call_number = acn_for_uri_map.id) +LEFT JOIN asset.uri auri + ON (aucnm.uri = auri.id) +LEFT JOIN biblio.peer_bib_copy_map bpbcm + ON (bpbcm.peer_record = bre.id) +LEFT JOIN asset.copy acp_for_peer_bibs + ON (bpbcm.target_copy = acp_for_peer_bibs.id ) +LEFT JOIN asset.copy_location peer_copy_loc + ON (peer_copy_loc.id = acp_for_peer_bibs.location ) +LEFT JOIN config.copy_status peer_copy_status + ON (peer_copy_status.id = acp_for_peer_bibs.status) +WHERE + NOT bre.deleted AND + COALESCE(auri.active, TRUE) AND + NOT COALESCE(acp_for_peer_bibs.deleted, FALSE) AND + COALESCE( + acp_for_peer_bibs.opac_visible, + peer_copy_loc.opac_visible, + peer_copy_status.opac_visible, + TRUE -- URIs and things on aovc are already considered visible + ) +UNION +SELECT + bre.id AS record, + aou.id AS ou, +-- NULL AS copy_id, +-- NULL AS uri_id, +-- TRUE AS transcendant, +-- bre.deleted, + NULL AS copy_location + --TRUE AS visible +FROM biblio.record_entry bre +INNER JOIN config.bib_source cbs + ON (cbs.id = bre.source AND cbs.transcendant) +INNER JOIN actor.org_unit aou ON (true) +WHERE NOT bre.deleted -- 2.11.0