From 327daf4e40f171b442a700f355c473f48177fffb Mon Sep 17 00:00:00 2001 From: "a. bellenir" Date: Fri, 15 Jun 2018 15:53:10 -0400 Subject: [PATCH] LP#1587620: inconsistent copy counts between opac and staff client for peer bibs staff copy counts should include peer bib copies, per LP1587620. Signed-off-by: a. bellenir Signed-off-by: Kathy Lussier --- Open-ILS/src/sql/Pg/040.schema.asset.sql | 54 ++++++++++++------- ...hema.lp1775216_consistent_avail_counts.sql | 41 ++++++++------ 2 files changed, 60 insertions(+), 35 deletions(-) diff --git a/Open-ILS/src/sql/Pg/040.schema.asset.sql b/Open-ILS/src/sql/Pg/040.schema.asset.sql index 79aa442f54..04d98b3f66 100644 --- a/Open-ILS/src/sql/Pg/040.schema.asset.sql +++ b/Open-ILS/src/sql/Pg/040.schema.asset.sql @@ -617,39 +617,53 @@ BEGIN END; $f$ LANGUAGE PLPGSQL; -CREATE OR REPLACE FUNCTION asset.staff_ou_record_copy_count (org INT, rid BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$ -DECLARE - ans RECORD; +CREATE OR REPLACE FUNCTION asset.staff_ou_record_copy_count(org integer, rid bigint) + RETURNS TABLE(depth integer, org_unit integer, visible bigint, available bigint, unshadow bigint, transcendant integer) + LANGUAGE plpgsql +AS $function$ +DECLARE + ans RECORD; trans INT; -BEGIN +BEGIN SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) WHERE src.transcendant AND b.id = rid; FOR ans IN SELECT u.id, t.depth FROM actor.org_unit_ancestors(org) AS u JOIN actor.org_unit_type t ON (u.ou_type = t.id) LOOP RETURN QUERY - WITH available_statuses AS (SELECT ARRAY_AGG(id) AS ids FROM config.copy_status WHERE is_available) - SELECT ans.depth, - ans.id, - COUNT( cp.id ), - SUM( (cp.status = ANY (available_statuses.ids))::INT ), - SUM( CASE WHEN cl.opac_visible AND cp.opac_visible THEN 1 ELSE 0 END), - trans - FROM - available_statuses, - actor.org_unit_descendants(ans.id) d - JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted) - JOIN asset.copy_location cl ON (cp.location = cl.id AND NOT cl.deleted) - JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number AND NOT cn.deleted) - GROUP BY 1,2,6; + WITH available_statuses AS (SELECT ARRAY_AGG(id) AS ids FROM config.copy_status WHERE is_available), + cp AS( + SELECT cp.id, + (cp.status = ANY (available_statuses.ids))::INT as available, + (cl.opac_visible AND cp.opac_visible)::INT as opac_visible + FROM + available_statuses, + actor.org_unit_descendants(ans.id) d + JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted) + JOIN asset.copy_location cl ON (cp.location = cl.id AND NOT cl.deleted) + JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number AND NOT cn.deleted) + ), + peer AS ( + select cp.id, + (cp.status = ANY (available_statuses.ids))::INT as available, + (cl.opac_visible AND cp.opac_visible)::INT as opac_visible + FROM + available_statuses, + actor.org_unit_descendants(ans.id) d + JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted) + JOIN asset.copy_location cl ON (cp.location = cl.id AND NOT cl.deleted) + JOIN biblio.peer_bib_copy_map bp ON (bp.peer_record = rid AND bp.target_copy = cp.id) + ) + select ans.depth, ans.id, count(id), sum(x.available::int), sum(x.opac_visible::int), trans + from ((select * from cp) union (select * from peer)) x + group by 1,2,6; IF NOT FOUND THEN RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans; END IF; END LOOP; - RETURN; END; -$f$ LANGUAGE PLPGSQL; +$function$; CREATE OR REPLACE FUNCTION asset.staff_lasso_record_copy_count (i_lasso INT, rid BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$ DECLARE diff --git a/Open-ILS/src/sql/Pg/upgrade/1112.schema.lp1775216_consistent_avail_counts.sql b/Open-ILS/src/sql/Pg/upgrade/1112.schema.lp1775216_consistent_avail_counts.sql index b4d36c53e7..2b069f3bc3 100644 --- a/Open-ILS/src/sql/Pg/upgrade/1112.schema.lp1775216_consistent_avail_counts.sql +++ b/Open-ILS/src/sql/Pg/upgrade/1112.schema.lp1775216_consistent_avail_counts.sql @@ -10,27 +10,38 @@ BEGIN FOR ans IN SELECT u.id, t.depth FROM actor.org_unit_ancestors(org) AS u JOIN actor.org_unit_type t ON (u.ou_type = t.id) LOOP RETURN QUERY - WITH available_statuses AS (SELECT ARRAY_AGG(id) AS ids FROM config.copy_status WHERE is_available) - SELECT ans.depth, - ans.id, - COUNT( cp.id ), - SUM( (cp.status = ANY (available_statuses.ids))::INT ), - SUM( CASE WHEN cl.opac_visible AND cp.opac_visible THEN 1 ELSE 0 END), - trans - FROM - available_statuses, - actor.org_unit_descendants(ans.id) d - JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted) - JOIN asset.copy_location cl ON (cp.location = cl.id AND NOT cl.deleted) - JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number AND NOT cn.deleted) - GROUP BY 1,2,6; + WITH available_statuses AS (SELECT ARRAY_AGG(id) AS ids FROM config.copy_status WHERE is_available), + cp AS( + SELECT cp.id, + (cp.status = ANY (available_statuses.ids))::INT as available, + (cl.opac_visible AND cp.opac_visible)::INT as opac_visible + FROM + available_statuses, + actor.org_unit_descendants(ans.id) d + JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted) + JOIN asset.copy_location cl ON (cp.location = cl.id AND NOT cl.deleted) + JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number AND NOT cn.deleted) + ), + peer AS ( + SELECT cp.id, + (cp.status = ANY (available_statuses.ids))::INT as available, + (cl.opac_visible AND cp.opac_visible)::INT as opac_visible + FROM + available_statuses, + actor.org_unit_descendants(ans.id) d + JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted) + JOIN asset.copy_location cl ON (cp.location = cl.id AND NOT cl.deleted) + JOIN biblio.peer_bib_copy_map bp ON (bp.peer_record = rid AND bp.target_copy = cp.id) + ) + SELECT ans.depth, ans.id, count(id), sum(x.available::int), sum(x.opac_visible::int), trans + FROM ((select * from cp) union (select * from peer)) x + GROUP BY 1,2,6; IF NOT FOUND THEN RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans; END IF; END LOOP; - RETURN; END; $function$; -- 2.43.2