From 52bf3fe8e791c43448c49fa9818f5790625c724c Mon Sep 17 00:00:00 2001 From: Bill Erickson Date: Wed, 3 Aug 2016 13:34:29 -0400 Subject: [PATCH] LP#1497335 Aged/All circulation API access Various SQL, IDL, and API changes for accessing aged circulations, primarily via all_circulation objects, for imporoved staff client integration. *. Support open-ils.pcrud access to the action.all_circulation DB view / 'combcirc' class. *. Add missing parent_circ, checkin_scan_time, checkin_workstation to combcirc class. *. Add 'usr' field to combcirc. The action.all_circulation VIEW will return NULL as the 'usr' column value when returning data for an aged_circulation. *. Add virtual 'active_circ' and 'aged_circ' fields to combcirc for fleshing the related action.circulation or action.aged_circulation object. * Adds SQL functions for action.all_circ_chain and action.summarize_all_circ_chain, which pull data from aged_circulation. *. API calls open-ils.circ.[prev_]renewal_chain.retrieve_by_circ[.summary] now return data for active and aged circulations by using action.all_circ_chain and action.summarize_all_circ_chain. When using these APIs, a null value in the 'usr' column is the indication that a given circulation or circ chain summary represents an aged circulation. * API open-ils.circ.copy_details.retrieve will now optionally return aged circ data within the copy circ history. Signed-off-by: Bill Erickson --- Open-ILS/examples/fm_IDL.xml | 18 ++- .../lib/OpenILS/Application/AppUtils.pm | 2 +- .../perlmods/lib/OpenILS/Application/Circ.pm | 146 +++++++++--------- Open-ILS/src/sql/Pg/090.schema.action.sql | 94 ++++++++++- .../upgrade/XXXX.schema.aged-circ-chains.sql | 141 +++++++++++++++++ 5 files changed, 326 insertions(+), 75 deletions(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.schema.aged-circ-chains.sql diff --git a/Open-ILS/examples/fm_IDL.xml b/Open-ILS/examples/fm_IDL.xml index 485e4c693f..7126f16779 100644 --- a/Open-ILS/examples/fm_IDL.xml +++ b/Open-ILS/examples/fm_IDL.xml @@ -4209,7 +4209,7 @@ SELECT usr, - + @@ -4237,6 +4237,10 @@ SELECT usr, + + + + @@ -4254,6 +4258,8 @@ SELECT usr, + + @@ -4275,12 +4281,22 @@ SELECT usr, + + + + + + + + + + diff --git a/Open-ILS/src/perlmods/lib/OpenILS/Application/AppUtils.pm b/Open-ILS/src/perlmods/lib/OpenILS/Application/AppUtils.pm index 0fad7d97e7..83b978965f 100644 --- a/Open-ILS/src/perlmods/lib/OpenILS/Application/AppUtils.pm +++ b/Open-ILS/src/perlmods/lib/OpenILS/Application/AppUtils.pm @@ -1811,7 +1811,7 @@ sub create_uuid_string { sub create_circ_chain_summary { my($class, $e, $circ_id) = @_; - my $sum = $e->json_query({from => ['action.summarize_circ_chain', $circ_id]})->[0]; + my $sum = $e->json_query({from => ['action.summarize_all_circ_chain', $circ_id]})->[0]; return undef unless $sum; my $obj = Fieldmapper::action::circ_chain_summary->new; $obj->$_($sum->{$_}) for keys %$sum; diff --git a/Open-ILS/src/perlmods/lib/OpenILS/Application/Circ.pm b/Open-ILS/src/perlmods/lib/OpenILS/Application/Circ.pm index ba1133dcb8..530da64057 100644 --- a/Open-ILS/src/perlmods/lib/OpenILS/Application/Circ.pm +++ b/Open-ILS/src/perlmods/lib/OpenILS/Application/Circ.pm @@ -55,14 +55,20 @@ __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 + of an action.circulation object to pick up aged circs. / ); + sub retrieve_circ { - my( $s, $c, $a, $i ) = @_; + my( $s, $c, $a, $i, $all_circ ) = @_; my $e = new_editor(authtoken => $a); return $e->event unless $e->checkauth; - my $circ = $e->retrieve_action_circulation($i) or return $e->event; - if( $e->requestor->id ne $circ->usr ) { + my $method = $all_circ ? + 'retrieve_action_all_circulation' : + 'retrieve_action_circulation'; + my $circ = $e->$method($i) or return $e->event; + if( $e->requestor->id ne ($circ->usr || '') ) { return $e->event unless $e->allowed('VIEW_CIRCULATIONS'); } return $circ; @@ -783,7 +789,7 @@ sub view_circs { $count = 4 unless defined $count; } - return $e->search_action_circulation([ + return $e->search_action_all_circulation([ {target_copy => $copyid}, {limit => $count, order_by => { circ => "xact_start DESC" }} ]); @@ -1074,35 +1080,33 @@ sub copy_details { my $transit = $e->search_action_transit_copy( { target_copy => $copy_id, dest_recv_time => undef } )->[0]; - # find the latest circ, open or closed - my $circ = $e->search_action_circulation( - [ - { target_copy => $copy_id }, - { - flesh => 1, - flesh_fields => { - circ => [ - 'workstation', - 'checkin_workstation', - 'duration_rule', - 'max_fine_rule', - 'recurring_fine_rule' - ] - }, - order_by => { circ => 'xact_start desc' }, - limit => 1 - } - ] - )->[0]; - + # find the most recent circulation for the requested copy, + # be it active, completed, or aged. + my $circ = $e->search_action_all_circulation([ + { target_copy => $copy_id }, + { + flesh => 1, + flesh_fields => { + combcirc => [ + 'workstation', + 'checkin_workstation', + 'duration_rule', + 'max_fine_rule', + 'recurring_fine_rule' + ], + }, + order_by => { combcirc => 'xact_start desc' }, + limit => 1 + } + ])->[0]; return { - copy => $copy, - hold => $hold, + copy => $copy, + hold => $hold, transit => $transit, - circ => $circ, + circ => $circ, volume => $vol, - mvr => $mvr, + mvr => $mvr }; } @@ -1796,10 +1800,10 @@ sub retrieve_circ_chain { } else { - my $chain = $e->json_query({from => ['action.circ_chain', $circ_id]}); + my $chain = $e->json_query({from => ['action.all_circ_chain', $circ_id]}); for my $circ_info (@$chain) { - my $circ = Fieldmapper::action::circulation->new; + my $circ = Fieldmapper::action::all_circulation->new; $circ->$_($circ_info->{$_}) for keys %$circ_info; $conn->respond($circ); } @@ -1844,51 +1848,51 @@ sub retrieve_prev_circ_chain { return $e->event unless $e->checkauth; return $e->event unless $e->allowed('VIEW_CIRCULATIONS'); - if($self->api_name =~ /summary/) { - my $first_circ = $e->json_query({from => ['action.circ_chain', $circ_id]})->[0]; - my $target_copy = $$first_circ{'target_copy'}; - my $usr = $$first_circ{'usr'}; - my $last_circ_from_prev_chain = $e->json_query({ - 'select' => { 'circ' => ['id','usr'] }, - 'from' => 'circ', - 'where' => { - target_copy => $target_copy, - xact_start => { '<' => $$first_circ{'xact_start'} } + my $first_circ = + $e->json_query({from => ['action.all_circ_chain', $circ_id]})->[0]; + + my $prev_circ = $e->search_action_all_circulation([ + { target_copy => $first_circ->{target_copy}, + xact_start => {'<' => $first_circ->{xact_start}} + }, { + flesh => 1, + flesh_fields => { + combcirc => [ + 'active_circ', + 'aged_circ' + ] }, - 'order_by' => [{ 'class'=>'circ', 'field'=>'xact_start', 'direction'=>'desc' }], - 'limit' => 1 - })->[0]; - return undef unless $last_circ_from_prev_chain; - return undef unless $$last_circ_from_prev_chain{'id'}; - my $sum = $e->json_query({from => ['action.summarize_circ_chain', $$last_circ_from_prev_chain{'id'}]})->[0]; - return undef unless $sum; - my $obj = Fieldmapper::action::circ_chain_summary->new; - $obj->$_($sum->{$_}) for keys %$sum; - return { 'summary' => $obj, 'usr' => $$last_circ_from_prev_chain{'usr'} }; + order_by => { combcirc => 'xact_start desc' }, + limit => 1 + } + ])->[0]; - } else { + return undef unless $prev_circ; - my $first_circ = $e->json_query({from => ['action.circ_chain', $circ_id]})->[0]; - my $target_copy = $$first_circ{'target_copy'}; - my $last_circ_from_prev_chain = $e->json_query({ - 'select' => { 'circ' => ['id'] }, - 'from' => 'circ', - 'where' => { - target_copy => $target_copy, - xact_start => { '<' => $$first_circ{'xact_start'} } - }, - 'order_by' => [{ 'class'=>'circ', 'field'=>'xact_start', 'direction'=>'desc' }], - 'limit' => 1 + my $chain_usr = $prev_circ->usr; # note: may be undef + + if ($self->api_name =~ /summary/) { + my $sum = $e->json_query({ + from => [ + 'action.summarize_all_circ_chain', + $prev_circ->id + ] })->[0]; - return undef unless $last_circ_from_prev_chain; - return undef unless $$last_circ_from_prev_chain{'id'}; - my $chain = $e->json_query({from => ['action.circ_chain', $$last_circ_from_prev_chain{'id'}]}); - for my $circ_info (@$chain) { - my $circ = Fieldmapper::action::circulation->new; - $circ->$_($circ_info->{$_}) for keys %$circ_info; - $conn->respond($circ); - } + my $summary = Fieldmapper::action::circ_chain_summary->new; + $summary->$_($sum->{$_}) for keys %$sum; + + return {summary => $summary, usr => $chain_usr}; + } + + + my $chain = $e->json_query( + {from => ['action.all_circ_chain', $prev_circ->id]}); + + for my $circ_info (@$chain) { + my $circ = Fieldmapper::action::all_circulation->new; + $circ->$_($circ_info->{$_}) for keys %$circ_info; + $conn->respond($circ); } return undef; diff --git a/Open-ILS/src/sql/Pg/090.schema.action.sql b/Open-ILS/src/sql/Pg/090.schema.action.sql index 5ce80d320a..731ab3c14c 100644 --- a/Open-ILS/src/sql/Pg/090.schema.action.sql +++ b/Open-ILS/src/sql/Pg/090.schema.action.sql @@ -239,7 +239,8 @@ CREATE OR REPLACE VIEW action.all_circulation AS 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, checkin_scan_time, parent_circ + max_fine_rule, stop_fines, workstation, checkin_workstation, checkin_scan_time, parent_circ, + NULL AS usr FROM action.aged_circulation UNION ALL SELECT DISTINCT circ.id,COALESCE(a.post_code,b.post_code) AS usr_post_code, p.home_ou AS usr_home_ou, p.profile AS usr_profile, EXTRACT(YEAR FROM p.dob)::INT AS usr_birth_year, @@ -248,7 +249,7 @@ CREATE OR REPLACE VIEW action.all_circulation AS circ.checkin_lib, circ.renewal_remaining, circ.grace_period, circ.due_date, circ.stop_fines_time, circ.checkin_time, circ.create_time, circ.duration, circ.fine_interval, circ.recurring_fine, circ.max_fine, circ.phone_renewal, circ.desk_renewal, circ.opac_renewal, circ.duration_rule, circ.recurring_fine_rule, circ.max_fine_rule, circ.stop_fines, circ.workstation, circ.checkin_workstation, circ.checkin_scan_time, - circ.parent_circ + circ.parent_circ, circ.usr FROM action.circulation circ JOIN asset.copy cp ON (circ.target_copy = cp.id) JOIN asset.call_number cn ON (cp.call_number = cn.id) @@ -883,6 +884,95 @@ BEGIN END; $$ 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 $$ +DECLARE + tmp_circ action.all_circulation%ROWTYPE; + circ_0 action.all_circulation%ROWTYPE; +BEGIN + + SELECT INTO tmp_circ * FROM action.all_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.all_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.all_circulation + WHERE parent_circ = tmp_circ.id; + END LOOP; + +END; +$$ LANGUAGE 'plpgsql'; + +-- same as action.summarize_circ_chain, but returns data collected +-- from action.all_circulation, which may include aged circulations. +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%ROWTYPE; + + -- last circ in the chain + circ_n action.all_circulation%ROWTYPE; + + -- circ chain under construction + chain action.circ_chain_summary; + tmp_circ action.all_circulation%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 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/upgrade/XXXX.schema.aged-circ-chains.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.aged-circ-chains.sql new file mode 100644 index 0000000000..b61898010a --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.aged-circ-chains.sql @@ -0,0 +1,141 @@ + +BEGIN; + +-- SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version); + +DROP VIEW IF EXISTS action.all_circulation; +CREATE VIEW action.all_circulation AS + SELECT aged_circulation.id, aged_circulation.usr_post_code, + aged_circulation.usr_home_ou, aged_circulation.usr_profile, + aged_circulation.usr_birth_year, aged_circulation.copy_call_number, + aged_circulation.copy_location, aged_circulation.copy_owning_lib, + aged_circulation.copy_circ_lib, aged_circulation.copy_bib_record, + aged_circulation.xact_start, aged_circulation.xact_finish, + aged_circulation.target_copy, aged_circulation.circ_lib, + aged_circulation.circ_staff, aged_circulation.checkin_staff, + aged_circulation.checkin_lib, aged_circulation.renewal_remaining, + aged_circulation.grace_period, aged_circulation.due_date, + aged_circulation.stop_fines_time, aged_circulation.checkin_time, + aged_circulation.create_time, aged_circulation.duration, + aged_circulation.fine_interval, aged_circulation.recurring_fine, + aged_circulation.max_fine, aged_circulation.phone_renewal, + aged_circulation.desk_renewal, aged_circulation.opac_renewal, + aged_circulation.duration_rule, + aged_circulation.recurring_fine_rule, + aged_circulation.max_fine_rule, aged_circulation.stop_fines, + aged_circulation.workstation, aged_circulation.checkin_workstation, + aged_circulation.checkin_scan_time, aged_circulation.parent_circ, + NULL AS usr + FROM action.aged_circulation +UNION ALL + SELECT DISTINCT circ.id, + COALESCE(a.post_code, b.post_code) AS usr_post_code, + p.home_ou AS usr_home_ou, p.profile AS usr_profile, + date_part('year'::text, p.dob)::integer AS usr_birth_year, + cp.call_number AS copy_call_number, circ.copy_location, + cn.owning_lib AS copy_owning_lib, cp.circ_lib AS copy_circ_lib, + cn.record AS copy_bib_record, circ.xact_start, circ.xact_finish, + circ.target_copy, circ.circ_lib, circ.circ_staff, + circ.checkin_staff, circ.checkin_lib, circ.renewal_remaining, + circ.grace_period, circ.due_date, circ.stop_fines_time, + circ.checkin_time, circ.create_time, circ.duration, + circ.fine_interval, circ.recurring_fine, circ.max_fine, + circ.phone_renewal, circ.desk_renewal, circ.opac_renewal, + circ.duration_rule, circ.recurring_fine_rule, circ.max_fine_rule, + circ.stop_fines, circ.workstation, circ.checkin_workstation, + circ.checkin_scan_time, circ.parent_circ, circ.usr + FROM action.circulation circ + JOIN asset.copy cp ON circ.target_copy = cp.id +JOIN asset.call_number cn ON cp.call_number = cn.id +JOIN actor.usr p ON circ.usr = p.id +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 FUNCTION action.all_circ_chain (ctx_circ_id INTEGER) + RETURNS SETOF action.all_circulation AS $$ +DECLARE + tmp_circ action.all_circulation%ROWTYPE; + circ_0 action.all_circulation%ROWTYPE; +BEGIN + + SELECT INTO tmp_circ * FROM action.all_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.all_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.all_circulation + 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%ROWTYPE; + + -- last circ in the chain + circ_n action.all_circulation%ROWTYPE; + + -- circ chain under construction + chain action.circ_chain_summary; + tmp_circ action.all_circulation%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'; + + +COMMIT; + -- 2.43.2