From 479ea8e07ae45474425642955f01e3b767e62fab Mon Sep 17 00:00:00 2001 From: Dan Scott Date: Mon, 15 Aug 2011 13:42:55 -0400 Subject: [PATCH] LP#790329 org_lasso search is broken Fix the JavaScript error when we attempt to create a feed for the shortname of the org_unit_lasso (lassos have no shortname) - which lets the request hit the backend Perl module, which in turn chokes on a test for OU that only allows two conditions: either all digits is treated as a numeric ID for an org unit, or it is a shortname. Of course, lassos are negative integers, so we have to modify the test slightly to make it happier. There was no depth for org lasso copy counts, and this was handled correctly in the main case by the asset.opac_lasso_record_copy_count() function and its staff variant, but when a copy was not found at a given org_unit in the lasso a request was made for the non-existent depth and the function would error out. Signed-off-by: Dan Scott --- .../Application/Storage/Publisher/metabib.pm | 2 +- Open-ILS/src/sql/Pg/040.schema.asset.sql | 8 +- .../Pg/upgrade/XXXX.lp790329_opac_copy_count.sql | 130 +++++++++++++++++++++ 3 files changed, 135 insertions(+), 5 deletions(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.lp790329_opac_copy_count.sql diff --git a/Open-ILS/src/perlmods/OpenILS/Application/Storage/Publisher/metabib.pm b/Open-ILS/src/perlmods/OpenILS/Application/Storage/Publisher/metabib.pm index 0c09ed686e..31c4f1b080 100644 --- a/Open-ILS/src/perlmods/OpenILS/Application/Storage/Publisher/metabib.pm +++ b/Open-ILS/src/perlmods/OpenILS/Application/Storage/Publisher/metabib.pm @@ -2875,7 +2875,7 @@ sub query_parser_fts { if (my ($filter) = $query->parse_tree->find_filter('site')) { $ou = $filter->args->[0] if (@{$filter->args}); } - $ou = actor::org_unit->search( { shortname => $ou } )->next->id if ($ou and $ou !~ /^\d+$/); + $ou = actor::org_unit->search( { shortname => $ou } )->next->id if ($ou and $ou !~ /^(-)?\d+$/); # gather lasso, as with $ou diff --git a/Open-ILS/src/sql/Pg/040.schema.asset.sql b/Open-ILS/src/sql/Pg/040.schema.asset.sql index acfa065c90..f9e48de93f 100644 --- a/Open-ILS/src/sql/Pg/040.schema.asset.sql +++ b/Open-ILS/src/sql/Pg/040.schema.asset.sql @@ -456,7 +456,7 @@ BEGIN GROUP BY 1,2,6; IF NOT FOUND THEN - RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans; + RETURN QUERY SELECT -1, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans; END IF; END LOOP; @@ -518,7 +518,7 @@ BEGIN GROUP BY 1,2,6; IF NOT FOUND THEN - RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans; + RETURN QUERY SELECT -1, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans; END IF; END LOOP; @@ -602,7 +602,7 @@ BEGIN GROUP BY 1,2,6; IF NOT FOUND THEN - RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans; + RETURN QUERY SELECT -1, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans; END IF; END LOOP; @@ -666,7 +666,7 @@ BEGIN GROUP BY 1,2,6; IF NOT FOUND THEN - RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans; + RETURN QUERY SELECT -1, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans; END IF; END LOOP; diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.lp790329_opac_copy_count.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.lp790329_opac_copy_count.sql new file mode 100644 index 0000000000..1b9995b8e9 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.lp790329_opac_copy_count.sql @@ -0,0 +1,130 @@ +BEGIN; + +CREATE OR REPLACE FUNCTION asset.opac_lasso_metarecord_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 + 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.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP + RETURN QUERY + SELECT -1, + ans.id, + COUNT( av.id ), + SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ), + COUNT( av.id ), + trans + FROM + actor.org_unit_descendants(ans.id) d + JOIN asset.opac_visible_copies av ON (av.record = rid AND av.circ_lib = d.id) + JOIN asset.copy cp ON (cp.id = av.id) + JOIN metabib.metarecord_source_map m ON (m.source = av.record) + GROUP BY 1,2,6; + + IF NOT FOUND THEN + RETURN QUERY SELECT -1, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans; + END IF; + + END LOOP; + + RETURN; +END; +$f$ LANGUAGE PLPGSQL; + + +CREATE OR REPLACE FUNCTION asset.staff_lasso_metarecord_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 + 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.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP + RETURN QUERY + SELECT -1, + ans.id, + COUNT( cp.id ), + SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ), + COUNT( cp.id ), + trans + FROM + actor.org_unit_descendants(ans.id) d + JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted) + JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number AND NOT cn.deleted) + JOIN metabib.metarecord_source_map m ON (m.source = cn.record) + GROUP BY 1,2,6; + + IF NOT FOUND THEN + RETURN QUERY SELECT -1, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans; + END IF; + + END LOOP; + + RETURN; +END; +$f$ LANGUAGE PLPGSQL; + +CREATE OR REPLACE FUNCTION asset.opac_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 + 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.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP + RETURN QUERY + SELECT -1, + ans.id, + COUNT( av.id ), + SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ), + COUNT( av.id ), + trans + FROM + actor.org_unit_descendants(ans.id) d + JOIN asset.opac_visible_copies av ON (av.record = rid AND av.circ_lib = d.id) + JOIN asset.copy cp ON (cp.id = av.id) + GROUP BY 1,2,6; + + IF NOT FOUND THEN + RETURN QUERY SELECT -1, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans; + END IF; + + END LOOP; + + RETURN; +END; +$f$ LANGUAGE PLPGSQL; + +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 + 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.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP + RETURN QUERY + SELECT -1, + ans.id, + COUNT( cp.id ), + SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ), + COUNT( cp.id ), + trans + FROM + actor.org_unit_descendants(ans.id) d + JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.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 -1, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans; + END IF; + + END LOOP; + + RETURN; +END; +$f$ LANGUAGE PLPGSQL; + +COMMIT; -- 2.11.0