From df7976e4c295d41e43db5c82e263cf55bc5796ac Mon Sep 17 00:00:00 2001 From: erickson Date: Wed, 2 Dec 2009 14:35:05 +0000 Subject: [PATCH] added stored proc to take a circ and determine the full chain of associated circs and companion proc to summarize the chain. added fm class accs for action::circ_chain_summary. add ML method to turn the stored proc output into FM objects. added unrecoverd field to action.circulation since it was missing git-svn-id: svn://svn.open-ils.org/ILS/trunk@15053 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- Open-ILS/examples/fm_IDL.xml | 17 ++++ Open-ILS/src/perlmods/OpenILS/Application/Circ.pm | 55 +++++------ Open-ILS/src/sql/Pg/002.schema.config.sql | 2 +- Open-ILS/src/sql/Pg/090.schema.action.sql | 96 +++++++++++++++++++ .../src/sql/Pg/upgrade/0101.schema.circ-chain.sql | 103 +++++++++++++++++++++ 5 files changed, 245 insertions(+), 28 deletions(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/0101.schema.circ-chain.sql diff --git a/Open-ILS/examples/fm_IDL.xml b/Open-ILS/examples/fm_IDL.xml index cb8c04270a..c72e6b1b63 100644 --- a/Open-ILS/examples/fm_IDL.xml +++ b/Open-ILS/examples/fm_IDL.xml @@ -112,6 +112,22 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA + + + + + + + + + + + + + + + + @@ -2174,6 +2190,7 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA + diff --git a/Open-ILS/src/perlmods/OpenILS/Application/Circ.pm b/Open-ILS/src/perlmods/OpenILS/Application/Circ.pm index 99495b4756..54f7b6384a 100644 --- a/Open-ILS/src/perlmods/OpenILS/Application/Circ.pm +++ b/Open-ILS/src/perlmods/OpenILS/Application/Circ.pm @@ -1390,43 +1390,44 @@ __PACKAGE__->register_method( } ); +__PACKAGE__->register_method( + method => "retrieve_circ_chain", + api_name => "open-ils.circ.renewal_chain.retrieve_by_circ.summary", + signature => { + desc => q/Given a circulation, this returns all circulation objects + that are part of the same chain of renewals./, + params => [ + {desc => 'Authentication token', type => 'string'}, + {desc => 'Circ ID', type => 'number'}, + ], + return => {desc => q/List of circ objects, orderd by oldest circ first/} + } +); + sub retrieve_circ_chain { my($self, $conn, $auth, $circ_id) = @_; my $e = new_editor(authtoken => $auth); return $e->event unless $e->checkauth; + return $e->event unless $e->allowed('VIEW_CIRCULATIONS'); - # grab the base circ and all parent (previous) circs by fleshing - my $base_circ = $e->retrieve_action_circulation([ - $circ_id, - { - flesh => -1, - flesh_fields => {circ => [qw/parent_circ workstation checkin_workstation/]} - } - ]) or return $e->event; - - return $e->event unless $e->allowed('VIEW_CIRCULATIONS', $base_circ->circ_lib); + if($self->api_name =~ /summary/) { + my $sum = $e->json_query({from => ['action.summarize_circ_chain', $circ_id]})->[0]; + return undef unless $sum; + my $obj = Fieldmapper::action::circ_chain_summary->new; + $obj->$_($sum->{$_}) for keys %$sum; + return $obj; - # send each circ to the caller, starting with the oldest circulation - my @chain; - my $circ = $base_circ; - while($circ) { - push(@chain, $circ); + } else { - # unflesh for consistency - my $parent = $circ->parent_circ; - $circ->parent_circ($parent->id) if $parent; + my $chain = $e->json_query({from => ['action.circ_chain', $circ_id]}); - $circ = $parent; + for my $circ_info (@$chain) { + my $circ = Fieldmapper::action::circulation->new; + $circ->$_($circ_info->{$_}) for keys %$circ_info; + $conn->respond($circ); + } } - $conn->respond($_) for reverse(@chain); - - # base circ may not be the end of the chain. see if there are any subsequent circs - $circ = $base_circ; - $conn->respond($circ) while ($circ = $e->search_action_circulation([ - {parent_circ => $circ->id}, - {flesh => 1, flesh_fields => {circ => [qw/workstation checkin_workstation/]}} - ])->[0]); return undef; } diff --git a/Open-ILS/src/sql/Pg/002.schema.config.sql b/Open-ILS/src/sql/Pg/002.schema.config.sql index 3cf3cbc205..8d046713dc 100644 --- a/Open-ILS/src/sql/Pg/002.schema.config.sql +++ b/Open-ILS/src/sql/Pg/002.schema.config.sql @@ -51,7 +51,7 @@ CREATE TABLE config.upgrade_log ( install_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW() ); -INSERT INTO config.upgrade_log (version) VALUES ('0100'); -- Scott McKellar +INSERT INTO config.upgrade_log (version) VALUES ('0101'); -- berick CREATE TABLE config.bib_source ( id SERIAL PRIMARY KEY, diff --git a/Open-ILS/src/sql/Pg/090.schema.action.sql b/Open-ILS/src/sql/Pg/090.schema.action.sql index 302fdedcef..11e7600ec6 100644 --- a/Open-ILS/src/sql/Pg/090.schema.action.sql +++ b/Open-ILS/src/sql/Pg/090.schema.action.sql @@ -474,5 +474,101 @@ CREATE OR REPLACE VIEW action.unfulfilled_hold_innermost_loop AS WHERE l.count = m.min; +-- represents a circ chain summary +CREATE TYPE action.circ_chain_summary AS ( + num_circs INTEGER, + start_time TIMESTAMP WITH TIME ZONE, + checkout_workstation TEXT, + last_renewal_time TIMESTAMP WITH TIME ZONE, -- NULL if no renewals + last_stop_fines TEXT, + last_stop_fines_time TIMESTAMP WITH TIME ZONE, + last_renewal_workstation TEXT, -- NULL if no renewals + last_checkin_workstation TEXT, + last_checkin_time TIMESTAMP WITH TIME ZONE, + last_checkin_scan_time TIMESTAMP WITH TIME ZONE +); + + +CREATE OR REPLACE FUNCTION action.circ_chain ( ctx_circ_id INTEGER ) RETURNS SETOF action.circulation AS $$ +DECLARE + tmp_circ action.circulation%ROWTYPE; + circ_0 action.circulation%ROWTYPE; +BEGIN + + SELECT INTO tmp_circ * FROM action.circulation 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.circulation 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.circulation WHERE parent_circ = tmp_circ.id; + END LOOP; + +END; +$$ LANGUAGE 'plpgsql'; + +CREATE OR REPLACE FUNCTION action.summarize_circ_chain ( ctx_circ_id INTEGER ) RETURNS action.circ_chain_summary AS $$ + +DECLARE + + -- first circ in the chain + circ_0 action.circulation%ROWTYPE; + + -- last circ in the chain + circ_n action.circulation%ROWTYPE; + + -- circ chain under construction + chain action.circ_chain_summary; + tmp_circ action.circulation%ROWTYPE; + +BEGIN + + chain.num_circs := 0; + FOR tmp_circ IN SELECT * FROM action.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'; + + COMMIT; diff --git a/Open-ILS/src/sql/Pg/upgrade/0101.schema.circ-chain.sql b/Open-ILS/src/sql/Pg/upgrade/0101.schema.circ-chain.sql new file mode 100644 index 0000000000..691c990ed3 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/0101.schema.circ-chain.sql @@ -0,0 +1,103 @@ +BEGIN; + +INSERT INTO config.upgrade_log (version) VALUES ('0101'); + +-- represents a circ chain summary +CREATE TYPE action.circ_chain_summary AS ( + num_circs INTEGER, + start_time TIMESTAMP WITH TIME ZONE, + checkout_workstation TEXT, + last_renewal_time TIMESTAMP WITH TIME ZONE, -- NULL if no renewals + last_stop_fines TEXT, + last_stop_fines_time TIMESTAMP WITH TIME ZONE, + last_renewal_workstation TEXT, -- NULL if no renewals + last_checkin_workstation TEXT, + last_checkin_time TIMESTAMP WITH TIME ZONE, + last_checkin_scan_time TIMESTAMP WITH TIME ZONE +); + + +CREATE OR REPLACE FUNCTION action.circ_chain ( ctx_circ_id INTEGER ) RETURNS SETOF action.circulation AS $$ +DECLARE + tmp_circ action.circulation%ROWTYPE; + circ_0 action.circulation%ROWTYPE; +BEGIN + + SELECT INTO tmp_circ * FROM action.circulation 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.circulation 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.circulation WHERE parent_circ = tmp_circ.id; + END LOOP; + +END; +$$ LANGUAGE 'plpgsql'; + +CREATE OR REPLACE FUNCTION action.summarize_circ_chain ( ctx_circ_id INTEGER ) RETURNS action.circ_chain_summary AS $$ + +DECLARE + + -- first circ in the chain + circ_0 action.circulation%ROWTYPE; + + -- last circ in the chain + circ_n action.circulation%ROWTYPE; + + -- circ chain under construction + chain action.circ_chain_summary; + tmp_circ action.circulation%ROWTYPE; + +BEGIN + + chain.num_circs := 0; + FOR tmp_circ IN SELECT * FROM action.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'; + + +COMMIT; + + -- 2.11.0