From 4a244416e1d5cc01104fb583c58b6a8d816cec95 Mon Sep 17 00:00:00 2001 From: Bill Erickson Date: Thu, 1 Jun 2017 10:56:11 -0400 Subject: [PATCH] LP#1695007 All-circulations slim DB VIEW Adds a new view action.all_circulation_slim which collects all action.circulation and action.aged_circulation rows into a single set, without the added joins for copy and patron data imposed by the existing action.aged_circulation view. The new leaner view is now used by various other views and APIs in place of the all_circulation view: DB: action.all_circ_chain() DB: action.summarize_all_circ_chain() DB: rating.percent_time_circulating() API: open-ils.circ.retrieve API: open-ils.circ.copy_checkout_history.retrieve API: open-ils.circ.copy_details.retrieve API: open-ils.circ.renewal_chain.retrieve_by_circ[.summary] API: open-ils.circ.prev_renewal_chain.retrieve_by_circ[.summary] To Test (webstaff style): [1] Open a copy in the web staff Item Status / Detail View interface that has live and aged circulations. [2] Open the Recent Circ History tab [3] Confirm relevent fields for both types of circs display as expected and that the Patron field for the aged circulation shows a value of Signed-off-by: Bill Erickson Signed-off-by: Mike Rylander --- Open-ILS/examples/fm_IDL.xml | 74 ++++++++ .../src/perlmods/lib/OpenILS/Application/Circ.pm | 24 +-- Open-ILS/src/sql/Pg/090.schema.action.sql | 60 +++++-- Open-ILS/src/sql/Pg/220.schema.rating.sql | 2 +- .../sql/Pg/upgrade/XXXX.schema.all-circs-slim.sql | 193 +++++++++++++++++++++ Open-ILS/web/js/ui/default/staff/cat/item/app.js | 10 +- 6 files changed, 335 insertions(+), 28 deletions(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.schema.all-circs-slim.sql diff --git a/Open-ILS/examples/fm_IDL.xml b/Open-ILS/examples/fm_IDL.xml index cfcdd1024a..43aed7d9b9 100644 --- a/Open-ILS/examples/fm_IDL.xml +++ b/Open-ILS/examples/fm_IDL.xml @@ -4244,6 +4244,80 @@ SELECT usr, + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + diff --git a/Open-ILS/src/perlmods/lib/OpenILS/Application/Circ.pm b/Open-ILS/src/perlmods/lib/OpenILS/Application/Circ.pm index 54230dcc13..280ecddf0b 100644 --- a/Open-ILS/src/perlmods/lib/OpenILS/Application/Circ.pm +++ b/Open-ILS/src/perlmods/lib/OpenILS/Application/Circ.pm @@ -55,7 +55,7 @@ __PACKAGE__->register_method( Retrieve a circ object by id @param authtoken Login session key @pararm circid The id of the circ object - @param all_circ Returns an action.all_circulation object instead + @param all_circ Returns an action.all_circulation_slim object instead of an action.circulation object to pick up aged circs. / ); @@ -65,7 +65,7 @@ sub retrieve_circ { my $e = new_editor(authtoken => $a); return $e->event unless $e->checkauth; my $method = $all_circ ? - 'retrieve_action_all_circulation' : + 'retrieve_action_all_circulation_slim' : 'retrieve_action_circulation'; my $circ = $e->$method($i) or return $e->event; if( $e->requestor->id ne ($circ->usr || '') ) { @@ -794,9 +794,9 @@ sub view_circs { $count = 4 unless defined $count; } - return $e->search_action_all_circulation([ + return $e->search_action_all_circulation_slim([ {target_copy => $copyid}, - {limit => $count, order_by => { combcirc => "xact_start DESC" }} + {limit => $count, order_by => { aacs => "xact_start DESC" }} ]); } @@ -1155,12 +1155,12 @@ sub copy_details { # find the most recent circulation for the requested copy, # be it active, completed, or aged. - my $circ = $e->search_action_all_circulation([ + my $circ = $e->search_action_all_circulation_slim([ { target_copy => $copy_id }, { flesh => 1, flesh_fields => { - combcirc => [ + aacs => [ 'workstation', 'checkin_workstation', 'duration_rule', @@ -1168,7 +1168,7 @@ sub copy_details { 'recurring_fine_rule' ], }, - order_by => { combcirc => 'xact_start desc' }, + order_by => { aacs => 'xact_start desc' }, limit => 1 } ])->[0]; @@ -1876,7 +1876,7 @@ sub retrieve_circ_chain { my $chain = $e->json_query({from => ['action.all_circ_chain', $circ_id]}); for my $circ_info (@$chain) { - my $circ = Fieldmapper::action::all_circulation->new; + my $circ = Fieldmapper::action::all_circulation_slim->new; $circ->$_($circ_info->{$_}) for keys %$circ_info; $conn->respond($circ); } @@ -1924,18 +1924,18 @@ sub retrieve_prev_circ_chain { my $first_circ = $e->json_query({from => ['action.all_circ_chain', $circ_id]})->[0]; - my $prev_circ = $e->search_action_all_circulation([ + my $prev_circ = $e->search_action_all_circulation_slim([ { target_copy => $first_circ->{target_copy}, xact_start => {'<' => $first_circ->{xact_start}} }, { flesh => 1, flesh_fields => { - combcirc => [ + aacs => [ 'active_circ', 'aged_circ' ] }, - order_by => { combcirc => 'xact_start desc' }, + order_by => { aacs => 'xact_start desc' }, limit => 1 } ])->[0]; @@ -1963,7 +1963,7 @@ sub retrieve_prev_circ_chain { {from => ['action.all_circ_chain', $prev_circ->id]}); for my $circ_info (@$chain) { - my $circ = Fieldmapper::action::all_circulation->new; + my $circ = Fieldmapper::action::all_circulation_slim->new; $circ->$_($circ_info->{$_}) for keys %$circ_info; $conn->respond($circ); } diff --git a/Open-ILS/src/sql/Pg/090.schema.action.sql b/Open-ILS/src/sql/Pg/090.schema.action.sql index 11ee4c3865..3de949ea0b 100644 --- a/Open-ILS/src/sql/Pg/090.schema.action.sql +++ b/Open-ILS/src/sql/Pg/090.schema.action.sql @@ -258,6 +258,47 @@ CREATE OR REPLACE VIEW action.all_circulation AS LEFT JOIN actor.usr_address a ON (p.mailing_address = a.id) LEFT JOIN actor.usr_address b ON (p.billing_address = b.id); +CREATE OR REPLACE VIEW action.all_circulation_slim AS + SELECT * FROM action.circulation +UNION ALL + SELECT + id, + NULL AS usr, + xact_start, + xact_finish, + unrecovered, + target_copy, + circ_lib, + circ_staff, + checkin_staff, + checkin_lib, + renewal_remaining, + grace_period, + due_date, + stop_fines_time, + checkin_time, + create_time, + duration, + fine_interval, + recurring_fine, + max_fine, + phone_renewal, + desk_renewal, + opac_renewal, + duration_rule, + recurring_fine_rule, + max_fine_rule, + stop_fines, + workstation, + checkin_workstation, + copy_location, + checkin_scan_time, + parent_circ + FROM action.aged_circulation +; + + + CREATE OR REPLACE FUNCTION action.age_circ_on_delete () RETURNS TRIGGER AS $$ DECLARE found char := 'N'; @@ -896,13 +937,13 @@ $$ LANGUAGE 'plpgsql'; -- same as action.circ_chain, but returns action.all_circulation -- rows which may include aged circulations. CREATE OR REPLACE FUNCTION action.all_circ_chain (ctx_circ_id INTEGER) - RETURNS SETOF action.all_circulation AS $$ + RETURNS SETOF action.all_circulation_slim AS $$ DECLARE - tmp_circ action.all_circulation%ROWTYPE; - circ_0 action.all_circulation%ROWTYPE; + tmp_circ action.all_circulation_slim%ROWTYPE; + circ_0 action.all_circulation_slim%ROWTYPE; BEGIN - SELECT INTO tmp_circ * FROM action.all_circulation WHERE id = ctx_circ_id; + SELECT INTO tmp_circ * FROM action.all_circulation_slim WHERE id = ctx_circ_id; IF tmp_circ IS NULL THEN RETURN NEXT tmp_circ; @@ -911,7 +952,7 @@ BEGIN -- find the front of the chain WHILE TRUE LOOP - SELECT INTO tmp_circ * FROM action.all_circulation + SELECT INTO tmp_circ * FROM action.all_circulation_slim WHERE id = tmp_circ.parent_circ; IF tmp_circ IS NULL THEN EXIT; @@ -926,7 +967,7 @@ BEGIN EXIT; END IF; RETURN NEXT tmp_circ; - SELECT INTO tmp_circ * FROM action.all_circulation + SELECT INTO tmp_circ * FROM action.all_circulation_slim WHERE parent_circ = tmp_circ.id; END LOOP; @@ -941,14 +982,14 @@ CREATE OR REPLACE FUNCTION action.summarize_all_circ_chain DECLARE -- first circ in the chain - circ_0 action.all_circulation%ROWTYPE; + circ_0 action.all_circulation_slim%ROWTYPE; -- last circ in the chain - circ_n action.all_circulation%ROWTYPE; + circ_n action.all_circulation_slim%ROWTYPE; -- circ chain under construction chain action.circ_chain_summary; - tmp_circ action.all_circulation%ROWTYPE; + tmp_circ action.all_circulation_slim%ROWTYPE; BEGIN @@ -981,7 +1022,6 @@ BEGIN END; $$ LANGUAGE 'plpgsql'; - CREATE OR REPLACE FUNCTION action.usr_visible_holds (usr_id INT) RETURNS SETOF action.hold_request AS $func$ DECLARE h action.hold_request%ROWTYPE; diff --git a/Open-ILS/src/sql/Pg/220.schema.rating.sql b/Open-ILS/src/sql/Pg/220.schema.rating.sql index 3494a7b6de..8fed4cd7ae 100644 --- a/Open-ILS/src/sql/Pg/220.schema.rating.sql +++ b/Open-ILS/src/sql/Pg/220.schema.rating.sql @@ -835,7 +835,7 @@ BEGIN FROM asset.copy cp JOIN precalc_copy_filter_bib_list c ON (cp.id = c.copy) JOIN asset.call_number cn ON (cn.id = cp.call_number) - LEFT JOIN action.all_circulation circ ON ( + LEFT JOIN action.all_circulation_slim circ ON ( circ.target_copy = cp.id AND stop_fines NOT IN ( 'LOST', diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.all-circs-slim.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.all-circs-slim.sql new file mode 100644 index 0000000000..914044f838 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.all-circs-slim.sql @@ -0,0 +1,193 @@ +BEGIN; + +-- SELECT evergreen.upgrade_deps_block_check('1XXX', :eg_version); + +CREATE OR REPLACE VIEW action.all_circulation_slim AS + SELECT * FROM action.circulation +UNION ALL + SELECT + id, + NULL AS usr, + xact_start, + xact_finish, + unrecovered, + target_copy, + circ_lib, + circ_staff, + checkin_staff, + checkin_lib, + renewal_remaining, + grace_period, + due_date, + stop_fines_time, + checkin_time, + create_time, + duration, + fine_interval, + recurring_fine, + max_fine, + phone_renewal, + desk_renewal, + opac_renewal, + duration_rule, + recurring_fine_rule, + max_fine_rule, + stop_fines, + workstation, + checkin_workstation, + copy_location, + checkin_scan_time, + parent_circ + FROM action.aged_circulation +; + +DROP FUNCTION action.summarize_all_circ_chain(INTEGER); +DROP FUNCTION action.all_circ_chain(INTEGER); + +CREATE OR REPLACE FUNCTION action.all_circ_chain (ctx_circ_id INTEGER) + RETURNS SETOF action.all_circulation_slim AS $$ +DECLARE + tmp_circ action.all_circulation_slim%ROWTYPE; + circ_0 action.all_circulation_slim%ROWTYPE; +BEGIN + + SELECT INTO tmp_circ * FROM action.all_circulation_slim WHERE id = ctx_circ_id; + + IF tmp_circ IS NULL THEN + RETURN NEXT tmp_circ; + END IF; + circ_0 := tmp_circ; + + -- find the front of the chain + WHILE TRUE LOOP + SELECT INTO tmp_circ * FROM action.all_circulation_slim + WHERE id = tmp_circ.parent_circ; + IF tmp_circ IS NULL THEN + EXIT; + END IF; + circ_0 := tmp_circ; + END LOOP; + + -- now send the circs to the caller, oldest to newest + tmp_circ := circ_0; + WHILE TRUE LOOP + IF tmp_circ IS NULL THEN + EXIT; + END IF; + RETURN NEXT tmp_circ; + SELECT INTO tmp_circ * FROM action.all_circulation_slim + WHERE parent_circ = tmp_circ.id; + END LOOP; + +END; +$$ LANGUAGE 'plpgsql'; + +CREATE OR REPLACE FUNCTION action.summarize_all_circ_chain + (ctx_circ_id INTEGER) RETURNS action.circ_chain_summary AS $$ + +DECLARE + + -- first circ in the chain + circ_0 action.all_circulation_slim%ROWTYPE; + + -- last circ in the chain + circ_n action.all_circulation_slim%ROWTYPE; + + -- circ chain under construction + chain action.circ_chain_summary; + tmp_circ action.all_circulation_slim%ROWTYPE; + +BEGIN + + chain.num_circs := 0; + FOR tmp_circ IN SELECT * FROM action.all_circ_chain(ctx_circ_id) LOOP + + IF chain.num_circs = 0 THEN + circ_0 := tmp_circ; + END IF; + + chain.num_circs := chain.num_circs + 1; + circ_n := tmp_circ; + END LOOP; + + chain.start_time := circ_0.xact_start; + chain.last_stop_fines := circ_n.stop_fines; + chain.last_stop_fines_time := circ_n.stop_fines_time; + chain.last_checkin_time := circ_n.checkin_time; + chain.last_checkin_scan_time := circ_n.checkin_scan_time; + SELECT INTO chain.checkout_workstation name FROM actor.workstation WHERE id = circ_0.workstation; + SELECT INTO chain.last_checkin_workstation name FROM actor.workstation WHERE id = circ_n.checkin_workstation; + + IF chain.num_circs > 1 THEN + chain.last_renewal_time := circ_n.xact_start; + SELECT INTO chain.last_renewal_workstation name FROM actor.workstation WHERE id = circ_n.workstation; + END IF; + + RETURN chain; + +END; +$$ LANGUAGE 'plpgsql'; + +CREATE OR REPLACE FUNCTION rating.percent_time_circulating(badge_id INT) + RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$ +DECLARE + badge rating.badge_with_orgs%ROWTYPE; +BEGIN + + SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id; + + PERFORM rating.precalc_bibs_by_copy(badge_id); + + DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN ( + SELECT id FROM precalc_filter_bib_list + INTERSECT + SELECT id FROM precalc_bibs_by_copy_list + ); + + ANALYZE precalc_copy_filter_bib_list; + + RETURN QUERY + SELECT bib, + SUM(COALESCE(circ_time,0))::NUMERIC / SUM(age)::NUMERIC + FROM (SELECT cn.record AS bib, + cp.id, + EXTRACT( EPOCH FROM AGE(cp.active_date) ) + 1 AS age, + SUM( -- time copy spent circulating + EXTRACT( + EPOCH FROM + AGE( + COALESCE(circ.checkin_time, circ.stop_fines_time, NOW()), + circ.xact_start + ) + ) + )::NUMERIC AS circ_time + FROM asset.copy cp + JOIN precalc_copy_filter_bib_list c ON (cp.id = c.copy) + JOIN asset.call_number cn ON (cn.id = cp.call_number) + LEFT JOIN action.all_circulation_slim circ ON ( + circ.target_copy = cp.id + AND stop_fines NOT IN ( + 'LOST', + 'LONGOVERDUE', + 'CLAIMSRETURNED', + 'LONGOVERDUE' + ) + AND NOT ( + checkin_time IS NULL AND + stop_fines = 'MAXFINES' + ) + ) + WHERE cn.owning_lib = ANY (badge.orgs) + AND cp.active_date IS NOT NULL + -- Next line requires that copies with no circs (circ.id IS NULL) also not be deleted + AND ((circ.id IS NULL AND NOT cp.deleted) OR circ.id IS NOT NULL) + GROUP BY 1,2,3 + ) x + GROUP BY 1; +END; +$f$ LANGUAGE PLPGSQL STRICT; + + +-- ROLLBACK; +COMMIT; + diff --git a/Open-ILS/web/js/ui/default/staff/cat/item/app.js b/Open-ILS/web/js/ui/default/staff/cat/item/app.js index 865a842389..dabc637d23 100644 --- a/Open-ILS/web/js/ui/default/staff/cat/item/app.js +++ b/Open-ILS/web/js/ui/default/staff/cat/item/app.js @@ -89,7 +89,7 @@ function(egCore , egCirc , $uibModal , $q , $timeout , $window , egConfirmDialog limit : 1 } - //Retrieve separate copy, combcirc, and accs information + //Retrieve separate copy, aacs, and accs information service.getCopy = function(barcode, id) { if (barcode) return egCore.pcrud.search( 'acp', {barcode : barcode, deleted : 'f'}, @@ -99,7 +99,7 @@ function(egCore , egCirc , $uibModal , $q , $timeout , $window , egConfirmDialog .then(function(copy) {return copy}); } service.getCirc = function(id) { - return egCore.pcrud.search('combcirc', { target_copy : id }, + return egCore.pcrud.search('aacs', { target_copy : id }, service.circFlesh).then(function(circ) {return circ}); } service.getSummary = function(id) { @@ -1556,11 +1556,11 @@ function($scope , $q , $location , $routeParams , $timeout , $window , egCore , }).then(function(count) { - egCore.pcrud.search('combcirc', + egCore.pcrud.search('aacs', {target_copy : copyId}, { flesh : 2, flesh_fields : { - combcirc : [ + aacs : [ 'usr', 'workstation', 'checkin_workstation', @@ -1568,7 +1568,7 @@ function($scope , $q , $location , $routeParams , $timeout , $window , egCore , ], au : ['card'] }, - order_by : {combcirc : 'xact_start desc'}, + order_by : {aacs : 'xact_start desc'}, limit : count } -- 2.11.0