From 568bbef57478c8109679303a248af9e9b1750655 Mon Sep 17 00:00:00 2001 From: miker Date: Mon, 9 Aug 2010 20:54:35 +0000 Subject: [PATCH] In order to speed up the API call where we ask for the number of visible (or total, in the staff client), available and "unshadow" (aka total) copies, as well as record transcendance visiblity, we create here 10 stored procedures. Eight of them implement the record/metarecord, staff/opac, OU/lasso varients, and two more are wrappers simplifying the calling API. git-svn-id: svn://svn.open-ils.org/ILS/trunk@17143 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- .../perlmods/OpenILS/Application/Search/Biblio.pm | 30 +-- Open-ILS/src/sql/Pg/002.schema.config.sql | 2 +- Open-ILS/src/sql/Pg/040.schema.asset.sql | 244 ++++++++++++++++++++ ...schema.copy-visibility-count-as-stored-proc.sql | 250 +++++++++++++++++++++ 4 files changed, 512 insertions(+), 14 deletions(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/0366.schema.copy-visibility-count-as-stored-proc.sql diff --git a/Open-ILS/src/perlmods/OpenILS/Application/Search/Biblio.pm b/Open-ILS/src/perlmods/OpenILS/Application/Search/Biblio.pm index 4e94693c4c..2ec58be197 100644 --- a/Open-ILS/src/perlmods/OpenILS/Application/Search/Biblio.pm +++ b/Open-ILS/src/perlmods/OpenILS/Application/Search/Biblio.pm @@ -245,7 +245,9 @@ __PACKAGE__->register_method( includes the context org unit and all parent org units. Object includes the keys "transcendant", "count", "org_unit", "depth", "unshadow", "available". Each is a count, except "org_unit" which is - the context org unit and "depth" which is the depth of the context org unit + the context org unit and "depth" which is the depth of the context org + unit. "depth" is always -1 when the count from a lasso search is + performed, since depth doesn't mean anything in a lasso context. /, type => 'array' } @@ -253,23 +255,25 @@ __PACKAGE__->register_method( ); sub record_id_to_copy_count { - my( $self, $client, $org_id, $record_id ) = @_; - - return [] unless $record_id; + my( $self, $client, $org_id, $record_id ) = @_; - my $method = "open-ils.storage.biblio.record_entry.copy_count.atomic"; - my $key = "record"; + return [] unless $record_id; - if($self->api_name =~ /metarecord/) { - $method = "open-ils.storage.metabib.metarecord.copy_count.atomic"; - $key = "metarecord"; - } + my $key = $self->api_name =~ /metarecord/ ? 'metarecord' : 'record'; + my $staff =~ $self->api_name =~ /staff/ ? 't' : 'f'; - $method =~ s/atomic/staff\.atomic/og if($self->api_name =~ /staff/ ); + my $data = $U->cstorereq( + "open-ils.cstore.json_query.atomic", + { from => ['asset.' . $key . '_copy_count' => $org_id => $record_id => $staff] } + ); - my $count = $U->storagereq($method, org_unit => $org_id, $key => $record_id); + my @count; + for my $d ( @$data ) { # fix up the key name change required by stored-proc version + $$d{count} = delete $$d{visible}; + push @count, $d; + } - return [ sort { $a->{depth} <=> $b->{depth} } @$count ]; + return [ sort { $a->{depth} <=> $b->{depth} } @count ]; } diff --git a/Open-ILS/src/sql/Pg/002.schema.config.sql b/Open-ILS/src/sql/Pg/002.schema.config.sql index 5f22bd7b6c..41f8e3a803 100644 --- a/Open-ILS/src/sql/Pg/002.schema.config.sql +++ b/Open-ILS/src/sql/Pg/002.schema.config.sql @@ -68,7 +68,7 @@ CREATE TABLE config.upgrade_log ( install_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW() ); -INSERT INTO config.upgrade_log (version) VALUES ('0365'); -- dbs +INSERT INTO config.upgrade_log (version) VALUES ('0366'); -- miker CREATE TABLE config.bib_source ( id SERIAL PRIMARY KEY, diff --git a/Open-ILS/src/sql/Pg/040.schema.asset.sql b/Open-ILS/src/sql/Pg/040.schema.asset.sql index 061fa41f53..4eb931e69a 100644 --- a/Open-ILS/src/sql/Pg/040.schema.asset.sql +++ b/Open-ILS/src/sql/Pg/040.schema.asset.sql @@ -385,5 +385,249 @@ CREATE VIEW stats.fleshed_call_number AS FROM asset.call_number cn JOIN metabib.rec_descriptor rd ON (rd.record = cn.record); +CREATE OR REPLACE FUNCTION asset.opac_ou_record_copy_count (org INT, record BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$ +DECLARE + ans RECORD; +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 + SELECT ans.depth, + ans.id, + COUNT( av.id ), + SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ), + COUNT( av.id ), + CASE WHEN src.transcendant THEN 1 ELSE NULL::INT END + FROM + actor.org_unit_descendants(ans.id) d + JOIN asset.opac_visible_copies av ON (av.record = record AND av.circ_lib = d.id) + JOIN asset.copy cp ON (cp.id = av.id) + JOIN biblio.record_entry b ON (b.id = av.record) + LEFT JOIN config.bib_source src ON (b.source = src.id) + GROUP BY 1,2,6; + END LOOP; + + RETURN; +END; +$f$ LANGUAGE PLPGSQL; + +CREATE OR REPLACE FUNCTION asset.opac_lasso_record_copy_count (i_lasso INT, record BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$ +DECLARE + ans RECORD; +BEGIN + 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 ), + CASE WHEN src.transcendant THEN 1 ELSE NULL::INT END + FROM + actor.org_unit_descendants(ans.id) d + JOIN asset.opac_visible_copies av ON (av.record = record AND av.circ_lib = d.id) + JOIN asset.copy cp ON (cp.id = av.id) + JOIN biblio.record_entry b ON (b.id = av.record) + LEFT JOIN config.bib_source src ON (b.source = src.id) + GROUP BY 1,2,6; + END LOOP; + + RETURN; +END; +$f$ LANGUAGE PLPGSQL; + +CREATE OR REPLACE FUNCTION asset.staff_ou_record_copy_count (org INT, record BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$ +DECLARE + ans RECORD; +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 + SELECT ans.depth, + ans.id, + COUNT( cp.id ), + SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ), + COUNT( cp.id ), + CASE WHEN src.transcendant THEN 1 ELSE NULL::INT END + FROM + actor.org_unit_descendants(ans.id) d + JOIN asset.copy cp ON (cp.circ_lib = d.id) + JOIN asset.call_number cn ON (cn.record = record AND cn.id = cp.call_number) + JOIN biblio.record_entry b ON (b.id = cn.record) + LEFT JOIN config.bib_source src ON (b.source = src.id) + GROUP BY 1,2,6; + END LOOP; + + RETURN; +END; +$f$ LANGUAGE PLPGSQL; + +CREATE OR REPLACE FUNCTION asset.staff_lasso_record_copy_count (i_lasso INT, record BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$ +DECLARE + ans RECORD; +BEGIN + 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 ), + CASE WHEN src.transcendant THEN 1 ELSE NULL::INT END + FROM + actor.org_unit_descendants(ans.id) d + JOIN asset.copy cp ON (cp.circ_lib = d.id) + JOIN asset.call_number cn ON (cn.record = record AND cn.id = cp.call_number) + JOIN biblio.record_entry b ON (b.id = cn.record) + LEFT JOIN config.bib_source src ON (b.source = src.id) + GROUP BY 1,2,6; + END LOOP; + + RETURN; +END; +$f$ LANGUAGE PLPGSQL; + +CREATE OR REPLACE OR REPLACE FUNCTION asset.record_copy_count ( place INT, record BIGINT, staff BOOL) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$ +BEGIN + IF staff IS TRUE THEN + IF place > 0 THEN + RETURN QUERY SELECT * FROM asset.staff_ou_record_copy_count( place, record ); + ELSE + RETURN QUERY SELECT * FROM asset.staff_lasso_record_copy_count( -place, record ); + END IF; + ELSE + IF place > 0 THEN + RETURN QUERY SELECT * FROM asset.opac_ou_record_copy_count( place, record ); + ELSE + RETURN QUERY SELECT * FROM asset.opac_lasso_record_copy_count( -place, record ); + END IF; + END IF; + + RETURN; +END; +$f$ LANGUAGE PLPGSQL; + +CREATE OR REPLACE FUNCTION asset.opac_ou_metarecord_copy_count (org INT, record BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$ +DECLARE + ans RECORD; +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 + SELECT ans.depth, + ans.id, + COUNT( av.id ), + SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ), + COUNT( av.id ), + CASE WHEN src.transcendant THEN 1 ELSE NULL::INT END + FROM + actor.org_unit_descendants(ans.id) d + JOIN asset.opac_visible_copies av ON (av.record = record AND av.circ_lib = d.id) + JOIN asset.copy cp ON (cp.id = av.id) + JOIN biblio.record_entry b ON (b.id = av.record) + JOIN metabib.metarecord_source_map m ON (m.source = av.record) + LEFT JOIN config.bib_source src ON (b.source = src.id) + GROUP BY 1,2,6; + END LOOP; + + RETURN; +END; +$f$ LANGUAGE PLPGSQL; + +CREATE OR REPLACE FUNCTION asset.opac_lasso_metarecord_copy_count (i_lasso INT, record BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$ +DECLARE + ans RECORD; +BEGIN + 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 ), + CASE WHEN src.transcendant THEN 1 ELSE NULL::INT END + FROM + actor.org_unit_descendants(ans.id) d + JOIN asset.opac_visible_copies av ON (av.record = record AND av.circ_lib = d.id) + JOIN asset.copy cp ON (cp.id = av.id) + JOIN biblio.record_entry b ON (b.id = av.record) + JOIN metabib.metarecord_source_map m ON (m.source = av.record) + LEFT JOIN config.bib_source src ON (b.source = src.id) + GROUP BY 1,2,6; + END LOOP; + + RETURN; +END; +$f$ LANGUAGE PLPGSQL; + +CREATE OR REPLACE FUNCTION asset.staff_ou_metarecord_copy_count (org INT, record BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$ +DECLARE + ans RECORD; +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 + SELECT ans.depth, + ans.id, + COUNT( cp.id ), + SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ), + COUNT( cp.id ), + CASE WHEN src.transcendant THEN 1 ELSE NULL::INT END + FROM + actor.org_unit_descendants(ans.id) d + JOIN asset.copy cp ON (cp.circ_lib = d.id) + JOIN asset.call_number cn ON (cn.record = record AND cn.id = cp.call_number) + JOIN biblio.record_entry b ON (b.id = cn.record) + JOIN metabib.metarecord_source_map m ON (m.source = cn.record) + LEFT JOIN config.bib_source src ON (b.source = src.id) + GROUP BY 1,2,6; + END LOOP; + + RETURN; +END; +$f$ LANGUAGE PLPGSQL; + +CREATE OR REPLACE FUNCTION asset.staff_lasso_metarecord_copy_count (i_lasso INT, record BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$ +DECLARE + ans RECORD; +BEGIN + 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 ), + CASE WHEN src.transcendant THEN 1 ELSE NULL::INT END + FROM + actor.org_unit_descendants(ans.id) d + JOIN asset.copy cp ON (cp.circ_lib = d.id) + JOIN asset.call_number cn ON (cn.record = record AND cn.id = cp.call_number) + JOIN biblio.record_entry b ON (b.id = cn.record) + JOIN metabib.metarecord_source_map m ON (m.source = cn.record) + LEFT JOIN config.bib_source src ON (b.source = src.id) + GROUP BY 1,2,6; + END LOOP; + + RETURN; +END; +$f$ LANGUAGE PLPGSQL; + +CREATE OR REPLACE OR REPLACE FUNCTION asset.metarecord_copy_count ( place INT, record BIGINT, staff BOOL) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$ +BEGIN + IF staff IS TRUE THEN + IF place > 0 THEN + RETURN QUERY SELECT * FROM asset.staff_ou_metarecord_copy_count( place, record ); + ELSE + RETURN QUERY SELECT * FROM asset.staff_lasso_metarecord_copy_count( -place, record ); + END IF; + ELSE + IF place > 0 THEN + RETURN QUERY SELECT * FROM asset.opac_ou_metarecord_copy_count( place, record ); + ELSE + RETURN QUERY SELECT * FROM asset.opac_lasso_metarecord_copy_count( -place, record ); + END IF; + END IF; + + RETURN; +END; +$f$ LANGUAGE PLPGSQL; + COMMIT; diff --git a/Open-ILS/src/sql/Pg/upgrade/0366.schema.copy-visibility-count-as-stored-proc.sql b/Open-ILS/src/sql/Pg/upgrade/0366.schema.copy-visibility-count-as-stored-proc.sql new file mode 100644 index 0000000000..c41648780e --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/0366.schema.copy-visibility-count-as-stored-proc.sql @@ -0,0 +1,250 @@ +BEGIN; + +INSERT INTO config.upgrade_log (version) VALUES ('0366'); -- miker + +CREATE OR REPLACE FUNCTION asset.opac_ou_record_copy_count (org INT, record BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$ +DECLARE + ans RECORD; +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 + SELECT ans.depth, + ans.id, + COUNT( av.id ), + SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ), + COUNT( av.id ), + CASE WHEN src.transcendant THEN 1 ELSE NULL::INT END + FROM + actor.org_unit_descendants(ans.id) d + JOIN asset.opac_visible_copies av ON (av.record = record AND av.circ_lib = d.id) + JOIN asset.copy cp ON (cp.id = av.id) + JOIN biblio.record_entry b ON (b.id = av.record) + LEFT JOIN config.bib_source src ON (b.source = src.id) + GROUP BY 1,2,6; + END LOOP; + + RETURN; +END; +$f$ LANGUAGE PLPGSQL; + +CREATE OR REPLACE FUNCTION asset.opac_lasso_record_copy_count (i_lasso INT, record BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$ +DECLARE + ans RECORD; +BEGIN + 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 ), + CASE WHEN src.transcendant THEN 1 ELSE NULL::INT END + FROM + actor.org_unit_descendants(ans.id) d + JOIN asset.opac_visible_copies av ON (av.record = record AND av.circ_lib = d.id) + JOIN asset.copy cp ON (cp.id = av.id) + JOIN biblio.record_entry b ON (b.id = av.record) + LEFT JOIN config.bib_source src ON (b.source = src.id) + GROUP BY 1,2,6; + END LOOP; + + RETURN; +END; +$f$ LANGUAGE PLPGSQL; + +CREATE OR REPLACE FUNCTION asset.staff_ou_record_copy_count (org INT, record BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$ +DECLARE + ans RECORD; +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 + SELECT ans.depth, + ans.id, + COUNT( cp.id ), + SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ), + COUNT( cp.id ), + CASE WHEN src.transcendant THEN 1 ELSE NULL::INT END + FROM + actor.org_unit_descendants(ans.id) d + JOIN asset.copy cp ON (cp.circ_lib = d.id) + JOIN asset.call_number cn ON (cn.record = record AND cn.id = cp.call_number) + JOIN biblio.record_entry b ON (b.id = cn.record) + LEFT JOIN config.bib_source src ON (b.source = src.id) + GROUP BY 1,2,6; + END LOOP; + + RETURN; +END; +$f$ LANGUAGE PLPGSQL; + +CREATE OR REPLACE FUNCTION asset.staff_lasso_record_copy_count (i_lasso INT, record BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$ +DECLARE + ans RECORD; +BEGIN + 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 ), + CASE WHEN src.transcendant THEN 1 ELSE NULL::INT END + FROM + actor.org_unit_descendants(ans.id) d + JOIN asset.copy cp ON (cp.circ_lib = d.id) + JOIN asset.call_number cn ON (cn.record = record AND cn.id = cp.call_number) + JOIN biblio.record_entry b ON (b.id = cn.record) + LEFT JOIN config.bib_source src ON (b.source = src.id) + GROUP BY 1,2,6; + END LOOP; + + RETURN; +END; +$f$ LANGUAGE PLPGSQL; + +CREATE OR REPLACE OR REPLACE FUNCTION asset.record_copy_count ( place INT, record BIGINT, staff BOOL) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$ +BEGIN + IF staff IS TRUE THEN + IF place > 0 THEN + RETURN QUERY SELECT * FROM asset.staff_ou_record_copy_count( place, record ); + ELSE + RETURN QUERY SELECT * FROM asset.staff_lasso_record_copy_count( -place, record ); + END IF; + ELSE + IF place > 0 THEN + RETURN QUERY SELECT * FROM asset.opac_ou_record_copy_count( place, record ); + ELSE + RETURN QUERY SELECT * FROM asset.opac_lasso_record_copy_count( -place, record ); + END IF; + END IF; + + RETURN; +END; +$f$ LANGUAGE PLPGSQL; + +CREATE OR REPLACE FUNCTION asset.opac_ou_metarecord_copy_count (org INT, record BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$ +DECLARE + ans RECORD; +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 + SELECT ans.depth, + ans.id, + COUNT( av.id ), + SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ), + COUNT( av.id ), + CASE WHEN src.transcendant THEN 1 ELSE NULL::INT END + FROM + actor.org_unit_descendants(ans.id) d + JOIN asset.opac_visible_copies av ON (av.record = record AND av.circ_lib = d.id) + JOIN asset.copy cp ON (cp.id = av.id) + JOIN biblio.record_entry b ON (b.id = av.record) + JOIN metabib.metarecord_source_map m ON (m.source = av.record) + LEFT JOIN config.bib_source src ON (b.source = src.id) + GROUP BY 1,2,6; + END LOOP; + + RETURN; +END; +$f$ LANGUAGE PLPGSQL; + +CREATE OR REPLACE FUNCTION asset.opac_lasso_metarecord_copy_count (i_lasso INT, record BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$ +DECLARE + ans RECORD; +BEGIN + 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 ), + CASE WHEN src.transcendant THEN 1 ELSE NULL::INT END + FROM + actor.org_unit_descendants(ans.id) d + JOIN asset.opac_visible_copies av ON (av.record = record AND av.circ_lib = d.id) + JOIN asset.copy cp ON (cp.id = av.id) + JOIN biblio.record_entry b ON (b.id = av.record) + JOIN metabib.metarecord_source_map m ON (m.source = av.record) + LEFT JOIN config.bib_source src ON (b.source = src.id) + GROUP BY 1,2,6; + END LOOP; + + RETURN; +END; +$f$ LANGUAGE PLPGSQL; + +CREATE OR REPLACE FUNCTION asset.staff_ou_metarecord_copy_count (org INT, record BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$ +DECLARE + ans RECORD; +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 + SELECT ans.depth, + ans.id, + COUNT( cp.id ), + SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ), + COUNT( cp.id ), + CASE WHEN src.transcendant THEN 1 ELSE NULL::INT END + FROM + actor.org_unit_descendants(ans.id) d + JOIN asset.copy cp ON (cp.circ_lib = d.id) + JOIN asset.call_number cn ON (cn.record = record AND cn.id = cp.call_number) + JOIN biblio.record_entry b ON (b.id = cn.record) + JOIN metabib.metarecord_source_map m ON (m.source = cn.record) + LEFT JOIN config.bib_source src ON (b.source = src.id) + GROUP BY 1,2,6; + END LOOP; + + RETURN; +END; +$f$ LANGUAGE PLPGSQL; + +CREATE OR REPLACE FUNCTION asset.staff_lasso_metarecord_copy_count (i_lasso INT, record BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$ +DECLARE + ans RECORD; +BEGIN + 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 ), + CASE WHEN src.transcendant THEN 1 ELSE NULL::INT END + FROM + actor.org_unit_descendants(ans.id) d + JOIN asset.copy cp ON (cp.circ_lib = d.id) + JOIN asset.call_number cn ON (cn.record = record AND cn.id = cp.call_number) + JOIN biblio.record_entry b ON (b.id = cn.record) + JOIN metabib.metarecord_source_map m ON (m.source = cn.record) + LEFT JOIN config.bib_source src ON (b.source = src.id) + GROUP BY 1,2,6; + END LOOP; + + RETURN; +END; +$f$ LANGUAGE PLPGSQL; + +CREATE OR REPLACE OR REPLACE FUNCTION asset.metarecord_copy_count ( place INT, record BIGINT, staff BOOL) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$ +BEGIN + IF staff IS TRUE THEN + IF place > 0 THEN + RETURN QUERY SELECT * FROM asset.staff_ou_metarecord_copy_count( place, record ); + ELSE + RETURN QUERY SELECT * FROM asset.staff_lasso_metarecord_copy_count( -place, record ); + END IF; + ELSE + IF place > 0 THEN + RETURN QUERY SELECT * FROM asset.opac_ou_metarecord_copy_count( place, record ); + ELSE + RETURN QUERY SELECT * FROM asset.opac_lasso_metarecord_copy_count( -place, record ); + END IF; + END IF; + + RETURN; +END; +$f$ LANGUAGE PLPGSQL; + + COMMIT; + -- 2.11.0