From 9b4591e3f9f88aa335c2ba6d4e928ca6fbc879da Mon Sep 17 00:00:00 2001 From: "a. bellenir" Date: Tue, 5 Jun 2018 15:56:24 -0400 Subject: [PATCH] LP#1775216: inconsistent availability counts between staff client and opac update asset.staff_ou_record_copy_count to get an accurate available count. borrow asset.opac_ou_record_copy_count's logic for counting available copies so that statuses with is_available set to 't' in config.copy_status are used instead of using the hardcoded status id list (0,7,12) Signed-off-by: a. bellenir Signed-off-by: Kathy Lussier --- Open-ILS/src/sql/Pg/040.schema.asset.sql | 4 ++- ...hema.lp1775216_consistent_avail_counts.sql | 36 +++++++++++++++++++ 2 files changed, 39 insertions(+), 1 deletion(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/1112.schema.lp1775216_consistent_avail_counts.sql diff --git a/Open-ILS/src/sql/Pg/040.schema.asset.sql b/Open-ILS/src/sql/Pg/040.schema.asset.sql index a7870aa30e..3206c29372 100644 --- a/Open-ILS/src/sql/Pg/040.schema.asset.sql +++ b/Open-ILS/src/sql/Pg/040.schema.asset.sql @@ -625,13 +625,15 @@ 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( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ), + 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) 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 new file mode 100644 index 0000000000..e76d09e90d --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/1112.schema.lp1775216_consistent_avail_counts.sql @@ -0,0 +1,36 @@ +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 + 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; + + 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