From a34306360faca0f505345ea82c114ddbfc13418f Mon Sep 17 00:00:00 2001 From: miker Date: Sat, 19 Mar 2011 14:33:18 +0000 Subject: [PATCH] In-database Grace intervals s it stands the grace period for a transaction is only specified on the fine generator script, but fines can be generated on checkin as well without one. As a result any checkin results in grace periods being negated. The attached patch adds in-db grace periods for all non-booking transactions, removing the grace period from the fine generator cron job script and placing it on the circulation. Grace periods are set on the recurring fine rule with an override available on the circ matchpoint, identical to how renewal count can be overridden from the duration rule already. These grace periods are respected on checkin fine generation as well as on the fine generator cron job script. The upgrade script will assume, by default, a grace period of 1, but has a \set line to change this. It will also update all circulations without a checkin by default, but another \set will allow disabling of that. [ NOTE: for the history of the patch, see: https://bugs.launchpad.net/evergreen/+bug/732679 ] Signed-off-by: Thomas Berezansky Signed-off-by: Mike Rylander git-svn-id: svn://svn.open-ils.org/ILS/trunk@19823 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- Open-ILS/examples/fm_IDL.xml | 6 + .../lib/OpenILS/Application/Circ/Circulate.pm | 28 +- .../lib/OpenILS/Application/Storage/CDBI/action.pm | 4 +- .../lib/OpenILS/Application/Storage/CDBI/config.pm | 2 +- .../Application/Storage/Publisher/action.pm | 32 +- Open-ILS/src/sql/Pg/002.schema.config.sql | 5 +- Open-ILS/src/sql/Pg/090.schema.action.sql | 9 +- Open-ILS/src/sql/Pg/100.circ_matrix.sql | 7 +- Open-ILS/src/sql/Pg/950.data.seed-values.sql | 6 +- .../sql/Pg/upgrade/0503.schema.grace_periods.sql | 470 +++++++++++++++++++++ Open-ILS/src/support-scripts/fine_generator.pl | 14 +- .../conify/global/config/circ_matrix_matchpoint.js | 1 + .../global/config/circ_matrix_matchpoint.tt2 | 2 +- .../conify/global/config/rule_recurring_fine.tt2 | 2 +- 14 files changed, 542 insertions(+), 46 deletions(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/0503.schema.grace_periods.sql diff --git a/Open-ILS/examples/fm_IDL.xml b/Open-ILS/examples/fm_IDL.xml index 6d3cf94c7..883629154 100644 --- a/Open-ILS/examples/fm_IDL.xml +++ b/Open-ILS/examples/fm_IDL.xml @@ -1245,6 +1245,7 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA + @@ -2805,6 +2806,7 @@ SELECT usr, + @@ -2866,6 +2868,7 @@ SELECT usr, + @@ -2930,6 +2933,7 @@ SELECT usr, + @@ -4571,6 +4575,7 @@ SELECT usr, + @@ -4606,6 +4611,7 @@ SELECT usr, + diff --git a/Open-ILS/src/perlmods/lib/OpenILS/Application/Circ/Circulate.pm b/Open-ILS/src/perlmods/lib/OpenILS/Application/Circ/Circulate.pm index 0e4652952..10e9a3438 100644 --- a/Open-ILS/src/perlmods/lib/OpenILS/Application/Circ/Circulate.pm +++ b/Open-ILS/src/perlmods/lib/OpenILS/Application/Circ/Circulate.pm @@ -1162,6 +1162,9 @@ sub run_indb_circ_test { $self->circ_matrix_matchpoint->duration_rule->max_renewals($results->[0]->{renewals}); } $self->circ_matrix_matchpoint->recurring_fine_rule($self->editor->retrieve_config_rules_recurring_fine($results->[0]->{recurring_fine_rule})); + if($results->[0]->{grace_period}) { + $self->circ_matrix_matchpoint->recurring_fine_rule->grace_period($results->[0]->{grace_period}); + } $self->circ_matrix_matchpoint->max_fine_rule($self->editor->retrieve_config_rules_max_fine($results->[0]->{max_fine_rule})); $self->circ_matrix_matchpoint->hard_due_date($self->editor->retrieve_config_hard_due_date($results->[0]->{hard_due_date})); } @@ -1220,7 +1223,8 @@ sub get_circ_policy { max_fine_rule => $max_fine_rule->name, max_fine => $self->get_max_fine_amount($max_fine_rule), fine_interval => $recurring_fine_rule->recurrence_interval, - renewal_remaining => $duration_rule->max_renewals + renewal_remaining => $duration_rule->max_renewals, + grace_period => $recurring_fine_rule->grace_period }; if($hard_due_date) { @@ -1833,6 +1837,7 @@ sub build_checkout_circ_object { $circ->max_fine($policy->{max_fine}); $circ->fine_interval($recurring->recurrence_interval); $circ->renewal_remaining($duration->max_renewals); + $circ->grace_period($policy->{grace_period}); } else { @@ -1841,6 +1846,7 @@ sub build_checkout_circ_object { $circ->recurring_fine_rule(OILS_UNLIMITED_CIRC_DURATION); $circ->max_fine_rule(OILS_UNLIMITED_CIRC_DURATION); $circ->renewal_remaining(0); + $circ->grace_period(0); } $circ->target_copy( $copy->id ); @@ -2928,15 +2934,25 @@ sub generate_fines { sub generate_fines_start { my $self = shift; my $reservation = shift; - - my $id = $reservation ? $self->reservation->id : $self->circ->id; + my $dt_parser = DateTime::Format::ISO8601->new; + + my $obj = $reservation ? $self->reservation : $self->circ; + + # If we have a grace period + if($obj->can('grace_period')) { + # Parse out the due date + my $due_date = $dt_parser->parse_datetime( cleanse_ISO8601($obj->due_date) ); + # Add the grace period to the due date + $due_date->add(seconds => OpenSRF::Utils->interval_to_seconds($obj->grace_period)); + # Don't generate fines on circs still in grace period + return undef if ($due_date > DateTime->now); + } if (!exists($self->{_gen_fines_req})) { $self->{_gen_fines_req} = OpenSRF::AppSession->create('open-ils.storage') ->request( 'open-ils.storage.action.circulation.overdue.generate_fines', - undef, - $id + $obj->id ); } @@ -2947,6 +2963,8 @@ sub generate_fines_finish { my $self = shift; my $reservation = shift; + return undef unless $self->{_gen_fines_req}; + my $id = $reservation ? $self->reservation->id : $self->circ->id; $self->{_gen_fines_req}->wait_complete; diff --git a/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/CDBI/action.pm b/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/CDBI/action.pm index 99772dc52..06094b40c 100644 --- a/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/CDBI/action.pm +++ b/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/CDBI/action.pm @@ -63,7 +63,7 @@ use base qw/action/; __PACKAGE__->table('action_circulation'); __PACKAGE__->columns(Primary => 'id'); __PACKAGE__->columns(Essential => qw/xact_start usr target_copy circ_lib - duration duration_rule renewal_remaining + duration duration_rule renewal_remaining grace_period recurring_fine_rule recurring_fine stop_fines max_fine max_fine_rule fine_interval stop_fines xact_finish due_date opac_renewal @@ -78,7 +78,7 @@ use base qw/action/; __PACKAGE__->table('action_open_circulation'); __PACKAGE__->columns(Primary => 'id'); __PACKAGE__->columns(Essential => qw/xact_start usr target_copy circ_lib - duration duration_rule renewal_remaining + duration duration_rule renewal_remaining grace_period recurring_fine_rule recurring_fine stop_fines max_fine max_fine_rule fine_interval stop_fines xact_finish due_date opac_renewal diff --git a/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/CDBI/config.pm b/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/CDBI/config.pm index bde437298..da03f1f14 100644 --- a/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/CDBI/config.pm +++ b/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/CDBI/config.pm @@ -59,7 +59,7 @@ package config::rules::recurring_fine; use base qw/config/; __PACKAGE__->table('config_rule_recurring_fine'); __PACKAGE__->columns(Primary => 'id'); -__PACKAGE__->columns(Essential => qw/name high normal low recurrence_interval/); +__PACKAGE__->columns(Essential => qw/name high normal low recurrence_interval grace_period/); #------------------------------------------------------------------------------- package config::rules::age_hold_protect; diff --git a/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/Publisher/action.pm b/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/Publisher/action.pm index e8aee2f5c..0760fcb00 100644 --- a/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/Publisher/action.pm +++ b/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/Publisher/action.pm @@ -95,23 +95,16 @@ __PACKAGE__->register_method( sub overdue_circs { - my $grace = shift; my $upper_interval = shift || '1 millennium'; my $idlist = shift; my $c_t = action::circulation->table; - if ($grace && $grace =~ /^\d+$/o) { - $grace = " - ($grace * (fine_interval))"; - } else { - $grace = ''; - } - my $sql = <<" SQL"; SELECT * FROM $c_t WHERE stop_fines IS NULL - AND due_date < ( CURRENT_TIMESTAMP $grace) + AND due_date < ( CURRENT_TIMESTAMP - grace_period ) AND fine_interval < ?::INTERVAL SQL @@ -125,7 +118,7 @@ sub overdue_circs { SELECT * FROM $c_t WHERE return_time IS NULL - AND end_time < ( CURRENT_TIMESTAMP $grace) + AND end_time < ( CURRENT_TIMESTAMP ) AND fine_interval IS NOT NULL AND cancel_time IS NULL SQL @@ -266,11 +259,10 @@ __PACKAGE__->register_method( sub grab_overdue { my $self = shift; my $client = shift; - my $grace = shift || ''; my $idlist = $self->api_name =~/id_list/o ? 1 : 0; - $client->respond( $idlist ? $_ : $_->to_fieldmapper ) for ( overdue_circs($grace, '', $idlist) ); + $client->respond( $idlist ? $_ : $_->to_fieldmapper ) for ( overdue_circs('', $idlist) ); return undef; @@ -787,7 +779,6 @@ sub seconds_to_interval_hash { sub generate_fines { my $self = shift; my $client = shift; - my $grace = shift; my $circ = shift; my $overbill = shift; @@ -799,7 +790,7 @@ sub generate_fines { action::circulation->search_where( { id => $circ, stop_fines => undef } ), booking::reservation->search_where( { id => $circ, return_time => undef, cancel_time => undef } ); } else { - push @circs, overdue_circs($grace); + push @circs, overdue_circs(); } my %hoo = map { ( $_->id => $_ ) } actor::org_unit::hours_of_operation->retrieve_all; @@ -823,6 +814,8 @@ sub generate_fines { $recurring_fine_method = 'fine_amount'; next unless ($c->fine_interval); } + #TODO: reservation grace periods + my $grace_period = ($is_reservation ? 0 : $c->grace_period); try { if ($self->method_lookup('open-ils.storage.transaction.current')->run) { @@ -902,8 +895,8 @@ sub generate_fines { while ( $h->$dow_open eq '00:00:00' and $h->$dow_close eq '00:00:00' ) { # if the circ lib is closed, add a day to the grace period... - $grace++; - $log->info( "Grace period for circ ".$c->id." extended to $grace intervals" ); + $grace_period+=86400; + $log->info( "Grace period for circ ".$c->id." extended to $grace_period [" . seconds_to_interval( $grace_period ) . "]" ); $log->info( "Day of week $dow open $dow_open, close $dow_close" ); $due_dt = $due_dt->add( days => 1 ); @@ -927,12 +920,11 @@ sub generate_fines { $pending_fine_count++ if ($fine_interval && ($fine_interval % 86400 == 0)); if ( $last_fine == $due # we have no fines yet - && $grace # and we have a grace period - && $pending_fine_count <= $grace # and we seem to be inside that period - && $now < $due + $fine_interval * $grace # and some date math bares that out, then + && $grace_period # and we have a grace period + && $now < $due + $grace_period # and some date math says were are within the grace period ) { - $client->respond( "Still inside grace period of: ". seconds_to_interval( $fine_interval * $grace)."\n" ); - $log->info( "Circ ".$c->id." is still inside grace period of: $grace [". seconds_to_interval( $fine_interval * $grace).']' ); + $client->respond( "Still inside grace period of: ". seconds_to_interval( $grace_period )."\n" ); + $log->info( "Circ ".$c->id." is still inside grace period of: $grace_period [". seconds_to_interval( $grace_period ).']' ); next; } diff --git a/Open-ILS/src/sql/Pg/002.schema.config.sql b/Open-ILS/src/sql/Pg/002.schema.config.sql index af763c3e0..af39c3410 100644 --- a/Open-ILS/src/sql/Pg/002.schema.config.sql +++ b/Open-ILS/src/sql/Pg/002.schema.config.sql @@ -70,7 +70,7 @@ CREATE TABLE config.upgrade_log ( install_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW() ); -INSERT INTO config.upgrade_log (version) VALUES ('0502'); -- dbwells +INSERT INTO config.upgrade_log (version) VALUES ('0503'); -- miker for tsbere CREATE TABLE config.bib_source ( id SERIAL PRIMARY KEY, @@ -420,7 +420,8 @@ CREATE TABLE config.rule_recurring_fine ( high NUMERIC(6,2) NOT NULL, normal NUMERIC(6,2) NOT NULL, low NUMERIC(6,2) NOT NULL, - recurrence_interval INTERVAL NOT NULL DEFAULT '1 day'::INTERVAL + recurrence_interval INTERVAL NOT NULL DEFAULT '1 day'::INTERVAL, + grace_period INTERVAL NOT NULL DEFAULT '1 day'::INTERVAL ); COMMENT ON TABLE config.rule_recurring_fine IS $$ /* diff --git a/Open-ILS/src/sql/Pg/090.schema.action.sql b/Open-ILS/src/sql/Pg/090.schema.action.sql index cb7391aad..3a34659d0 100644 --- a/Open-ILS/src/sql/Pg/090.schema.action.sql +++ b/Open-ILS/src/sql/Pg/090.schema.action.sql @@ -109,6 +109,7 @@ CREATE TABLE action.circulation ( checkin_staff INT, -- actor.usr.id checkin_lib INT, -- actor.org_unit.id renewal_remaining INT NOT NULL, -- derived from "circ duration" rule + grace_period INTERVAL NOT NULL, -- derived from "circ fine" rule due_date TIMESTAMP WITH TIME ZONE, stop_fines_time TIMESTAMP WITH TIME ZONE, checkin_time TIMESTAMP WITH TIME ZONE, @@ -191,7 +192,7 @@ CREATE INDEX action_aged_circulation_target_copy_idx ON action.aged_circulation CREATE OR REPLACE VIEW action.all_circulation AS SELECT id,usr_post_code, usr_home_ou, usr_profile, usr_birth_year, copy_call_number, copy_location, copy_owning_lib, copy_circ_lib, copy_bib_record, xact_start, xact_finish, target_copy, - circ_lib, circ_staff, checkin_staff, checkin_lib, renewal_remaining, due_date, + 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 @@ -200,7 +201,7 @@ CREATE OR REPLACE VIEW action.all_circulation AS 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, cp.call_number AS copy_call_number, cp.location AS 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.due_date, circ.stop_fines_time, circ.checkin_time, circ.create_time, circ.duration, + 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 @@ -233,14 +234,14 @@ BEGIN INSERT INTO action.aged_circulation (id,usr_post_code, usr_home_ou, usr_profile, usr_birth_year, copy_call_number, copy_location, copy_owning_lib, copy_circ_lib, copy_bib_record, xact_start, xact_finish, target_copy, - circ_lib, circ_staff, checkin_staff, checkin_lib, renewal_remaining, due_date, + 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) SELECT id,usr_post_code, usr_home_ou, usr_profile, usr_birth_year, copy_call_number, copy_location, copy_owning_lib, copy_circ_lib, copy_bib_record, xact_start, xact_finish, target_copy, - circ_lib, circ_staff, checkin_staff, checkin_lib, renewal_remaining, due_date, + 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 diff --git a/Open-ILS/src/sql/Pg/100.circ_matrix.sql b/Open-ILS/src/sql/Pg/100.circ_matrix.sql index c4e3b5b44..a026d2686 100644 --- a/Open-ILS/src/sql/Pg/100.circ_matrix.sql +++ b/Open-ILS/src/sql/Pg/100.circ_matrix.sql @@ -75,6 +75,7 @@ CREATE TABLE config.circ_matrix_matchpoint ( max_fine_rule INT REFERENCES config.rule_max_fine (id) DEFERRABLE INITIALLY DEFERRED, hard_due_date INT REFERENCES config.hard_due_date (id) DEFERRABLE INITIALLY DEFERRED, renewals INT, -- Renewal count override + grace_period INTERVAL, -- Grace period override script_test TEXT, -- javascript source total_copy_hold_ratio FLOAT, available_copy_hold_ratio FLOAT @@ -249,6 +250,9 @@ BEGIN IF matchpoint.renewals IS NULL THEN matchpoint.renewals := cur_matchpoint.renewals; END IF; + IF matchpoint.grace_period IS NULL THEN + matchpoint.grace_period := cur_matchpoint.grace_period; + END IF; END LOOP; -- Check required fields @@ -337,7 +341,7 @@ BEGIN END; $func$ LANGUAGE PLPGSQL; -CREATE TYPE action.circ_matrix_test_result AS ( success BOOL, fail_part TEXT, buildrows INT[], matchpoint INT, circulate BOOL, duration_rule INT, recurring_fine_rule INT, max_fine_rule INT, hard_due_date INT, renewals INT ); +CREATE TYPE action.circ_matrix_test_result AS ( success BOOL, fail_part TEXT, buildrows INT[], matchpoint INT, circulate BOOL, duration_rule INT, recurring_fine_rule INT, max_fine_rule INT, hard_due_date INT, renewals INT, grace_period INTERVAL ); CREATE OR REPLACE FUNCTION action.item_user_circ_test( circ_ou INT, match_item BIGINT, match_user INT, renewal BOOL ) RETURNS SETOF action.circ_matrix_test_result AS $func$ DECLARE user_object actor.usr%ROWTYPE; @@ -429,6 +433,7 @@ BEGIN result.max_fine_rule := circ_matchpoint.max_fine_rule; result.hard_due_date := circ_matchpoint.hard_due_date; result.renewals := circ_matchpoint.renewals; + result.grace_period := circ_matchpoint.grace_period; result.buildrows := circ_test.buildrows; -- Fail if we couldn't find a matchpoint diff --git a/Open-ILS/src/sql/Pg/950.data.seed-values.sql b/Open-ILS/src/sql/Pg/950.data.seed-values.sql index 0c3383443..bad385a37 100644 --- a/Open-ILS/src/sql/Pg/950.data.seed-values.sql +++ b/Open-ILS/src/sql/Pg/950.data.seed-values.sql @@ -194,11 +194,11 @@ INSERT INTO config.rule_max_fine VALUES SELECT SETVAL('config.rule_max_fine_id_seq'::TEXT, 100); INSERT INTO config.rule_recurring_fine VALUES - (1, oils_i18n_gettext(1, 'default', 'crrf', 'name'), 0.50, 0.10, 0.05, '1 day'); + (1, oils_i18n_gettext(1, 'default', 'crrf', 'name'), 0.50, 0.10, 0.05, '1 day', '1 day'); INSERT INTO config.rule_recurring_fine VALUES - (2, oils_i18n_gettext(2, '10_cent_per_day', 'crrf', 'name'), 0.50, 0.10, 0.10, '1 day'); + (2, oils_i18n_gettext(2, '10_cent_per_day', 'crrf', 'name'), 0.50, 0.10, 0.10, '1 day', '1 day'); INSERT INTO config.rule_recurring_fine VALUES - (3, oils_i18n_gettext(3, '50_cent_per_day', 'crrf', 'name'), 0.50, 0.50, 0.50, '1 day'); + (3, oils_i18n_gettext(3, '50_cent_per_day', 'crrf', 'name'), 0.50, 0.50, 0.50, '1 day', '1 day'); SELECT SETVAL('config.rule_recurring_fine_id_seq'::TEXT, 100); INSERT INTO config.rule_age_hold_protect VALUES diff --git a/Open-ILS/src/sql/Pg/upgrade/0503.schema.grace_periods.sql b/Open-ILS/src/sql/Pg/upgrade/0503.schema.grace_periods.sql new file mode 100644 index 000000000..05d1bcdee --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/0503.schema.grace_periods.sql @@ -0,0 +1,470 @@ +BEGIN; + +-- FAIR WARNING: +-- Using a tool such as pgadmin to run this script may fail +-- If it does, try psql command line. + +-- Change this to FALSE to disable updating existing circs +-- Otherwise will use the fine interval for the grace period +\set CircGrace TRUE + +INSERT INTO config.upgrade_log (version) VALUES ('0503'); + +-- New Columns + +ALTER TABLE config.circ_matrix_matchpoint + ADD COLUMN grace_period INTERVAL; + +ALTER TABLE config.rule_recurring_fine + ADD COLUMN grace_period INTERVAL NOT NULL DEFAULT '1 day'; + +ALTER TABLE action.circulation + ADD COLUMN grace_period INTERVAL NOT NULL DEFAULT '0 seconds'; + +ALTER TABLE action.aged_circulation + ADD COLUMN grace_period INTERVAL NOT NULL DEFAULT '0 seconds'; + +-- Remove defaults needed to stop null complaints + +ALTER TABLE action.circulation + ALTER COLUMN grace_period DROP DEFAULT; + +ALTER TABLE action.aged_circulation + ALTER COLUMN grace_period DROP DEFAULT; + +-- Drop Views + +DROP VIEW action.all_circulation; +DROP VIEW action.open_circulation; +DROP VIEW action.billable_circulations; + +-- Replace Views + +CREATE OR REPLACE VIEW action.all_circulation AS + SELECT id,usr_post_code, usr_home_ou, usr_profile, usr_birth_year, copy_call_number, copy_location, + copy_owning_lib, copy_circ_lib, copy_bib_record, xact_start, xact_finish, 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, checkin_scan_time, parent_circ + 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, + cp.call_number AS copy_call_number, cp.location AS 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 + 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 = a.id); + +CREATE OR REPLACE VIEW action.open_circulation AS + SELECT * + FROM action.circulation + WHERE checkin_time IS NULL + ORDER BY due_date; + + +CREATE OR REPLACE VIEW action.billable_circulations AS + SELECT * + FROM action.circulation + WHERE xact_finish IS NULL; + +-- Drop Functions that rely on types + +DROP FUNCTION action.item_user_circ_test(INT, BIGINT, INT, BOOL); +DROP FUNCTION action.item_user_circ_test(INT, BIGINT, INT); +DROP FUNCTION action.item_user_renew_test(INT, BIGINT, INT); + +-- Drop Types that are changing + +DROP TYPE action.circ_matrix_test_result; + +-- Replace Types + +CREATE TYPE action.circ_matrix_test_result AS ( success BOOL, fail_part TEXT, buildrows INT[], matchpoint INT, circulate BOOL, duration_rule INT, recurring_fine_rule INT, max_fine_rule INT, hard_due_date INT, renewals INT, grace_period INTERVAL ); + +-- Fix/Replace Functions + +CREATE OR REPLACE FUNCTION action.find_circ_matrix_matchpoint( context_ou INT, item_object asset.copy, user_object actor.usr, renewal BOOL ) RETURNS action.found_circ_matrix_matchpoint AS $func$ +DECLARE + cn_object asset.call_number%ROWTYPE; + rec_descriptor metabib.rec_descriptor%ROWTYPE; + cur_matchpoint config.circ_matrix_matchpoint%ROWTYPE; + matchpoint config.circ_matrix_matchpoint%ROWTYPE; + weights config.circ_matrix_weights%ROWTYPE; + user_age INTERVAL; + denominator NUMERIC(6,2); + row_list INT[]; + result action.found_circ_matrix_matchpoint; +BEGIN + -- Assume failure + result.success = false; + + -- Fetch useful data + SELECT INTO cn_object * FROM asset.call_number WHERE id = item_object.call_number; + SELECT INTO rec_descriptor * FROM metabib.rec_descriptor WHERE record = cn_object.record; + + -- Pre-generate this so we only calc it once + IF user_object.dob IS NOT NULL THEN + SELECT INTO user_age age(user_object.dob); + END IF; + + -- Grab the closest set circ weight setting. + SELECT INTO weights cw.* + FROM config.weight_assoc wa + JOIN config.circ_matrix_weights cw ON (cw.id = wa.circ_weights) + JOIN actor.org_unit_ancestors_distance( context_ou ) d ON (wa.org_unit = d.id) + WHERE active + ORDER BY d.distance + LIMIT 1; + + -- No weights? Bad admin! Defaults to handle that anyway. + IF weights.id IS NULL THEN + weights.grp := 11.0; + weights.org_unit := 10.0; + weights.circ_modifier := 5.0; + weights.marc_type := 4.0; + weights.marc_form := 3.0; + weights.marc_vr_format := 2.0; + weights.copy_circ_lib := 8.0; + weights.copy_owning_lib := 8.0; + weights.user_home_ou := 8.0; + weights.ref_flag := 1.0; + weights.juvenile_flag := 6.0; + weights.is_renewal := 7.0; + weights.usr_age_lower_bound := 0.0; + weights.usr_age_upper_bound := 0.0; + END IF; + + -- Determine the max (expected) depth (+1) of the org tree and max depth of the permisson tree + -- If you break your org tree with funky parenting this may be wrong + -- Note: This CTE is duplicated in the find_hold_matrix_matchpoint function, and it may be a good idea to split it off to a function + -- We use one denominator for all tree-based checks for when permission groups and org units have the same weighting + WITH all_distance(distance) AS ( + SELECT depth AS distance FROM actor.org_unit_type + UNION + SELECT distance AS distance FROM permission.grp_ancestors_distance((SELECT id FROM permission.grp_tree WHERE parent IS NULL)) + ) + SELECT INTO denominator MAX(distance) + 1 FROM all_distance; + + -- Loop over all the potential matchpoints + FOR cur_matchpoint IN + SELECT m.* + FROM config.circ_matrix_matchpoint m + /*LEFT*/ JOIN permission.grp_ancestors_distance( user_object.profile ) upgad ON m.grp = upgad.id + /*LEFT*/ JOIN actor.org_unit_ancestors_distance( context_ou ) ctoua ON m.org_unit = ctoua.id + LEFT JOIN actor.org_unit_ancestors_distance( cn_object.owning_lib ) cnoua ON m.copy_owning_lib = cnoua.id + LEFT JOIN actor.org_unit_ancestors_distance( item_object.circ_lib ) iooua ON m.copy_circ_lib = iooua.id + LEFT JOIN actor.org_unit_ancestors_distance( user_object.home_ou ) uhoua ON m.user_home_ou = uhoua.id + WHERE m.active + -- Permission Groups + -- AND (m.grp IS NULL OR upgad.id IS NOT NULL) -- Optional Permission Group? + -- Org Units + -- AND (m.org_unit IS NULL OR ctoua.id IS NOT NULL) -- Optional Org Unit? + AND (m.copy_owning_lib IS NULL OR cnoua.id IS NOT NULL) + AND (m.copy_circ_lib IS NULL OR iooua.id IS NOT NULL) + AND (m.user_home_ou IS NULL OR uhoua.id IS NOT NULL) + -- Circ Type + AND (m.is_renewal IS NULL OR m.is_renewal = renewal) + -- Static User Checks + AND (m.juvenile_flag IS NULL OR m.juvenile_flag = user_object.juvenile) + AND (m.usr_age_lower_bound IS NULL OR (user_age IS NOT NULL AND m.usr_age_lower_bound < user_age)) + AND (m.usr_age_upper_bound IS NULL OR (user_age IS NOT NULL AND m.usr_age_upper_bound > user_age)) + -- Static Item Checks + AND (m.circ_modifier IS NULL OR m.circ_modifier = item_object.circ_modifier) + AND (m.marc_type IS NULL OR m.marc_type = COALESCE(item_object.circ_as_type, rec_descriptor.item_type)) + AND (m.marc_form IS NULL OR m.marc_form = rec_descriptor.item_form) + AND (m.marc_vr_format IS NULL OR m.marc_vr_format = rec_descriptor.vr_format) + AND (m.ref_flag IS NULL OR m.ref_flag = item_object.ref) + ORDER BY + -- Permission Groups + CASE WHEN upgad.distance IS NOT NULL THEN 2^(2*weights.grp - (upgad.distance/denominator)) ELSE 0.0 END + + -- Org Units + CASE WHEN ctoua.distance IS NOT NULL THEN 2^(2*weights.org_unit - (ctoua.distance/denominator)) ELSE 0.0 END + + CASE WHEN cnoua.distance IS NOT NULL THEN 2^(2*weights.copy_owning_lib - (cnoua.distance/denominator)) ELSE 0.0 END + + CASE WHEN iooua.distance IS NOT NULL THEN 2^(2*weights.copy_circ_lib - (iooua.distance/denominator)) ELSE 0.0 END + + CASE WHEN uhoua.distance IS NOT NULL THEN 2^(2*weights.user_home_ou - (uhoua.distance/denominator)) ELSE 0.0 END + + -- Circ Type -- Note: 4^x is equiv to 2^(2*x) + CASE WHEN m.is_renewal IS NOT NULL THEN 4^weights.is_renewal ELSE 0.0 END + + -- Static User Checks + CASE WHEN m.juvenile_flag IS NOT NULL THEN 4^weights.juvenile_flag ELSE 0.0 END + + CASE WHEN m.usr_age_lower_bound IS NOT NULL THEN 4^weights.usr_age_lower_bound ELSE 0.0 END + + CASE WHEN m.usr_age_upper_bound IS NOT NULL THEN 4^weights.usr_age_upper_bound ELSE 0.0 END + + -- Static Item Checks + CASE WHEN m.circ_modifier IS NOT NULL THEN 4^weights.circ_modifier ELSE 0.0 END + + CASE WHEN m.marc_type IS NOT NULL THEN 4^weights.marc_type ELSE 0.0 END + + CASE WHEN m.marc_form IS NOT NULL THEN 4^weights.marc_form ELSE 0.0 END + + CASE WHEN m.marc_vr_format IS NOT NULL THEN 4^weights.marc_vr_format ELSE 0.0 END + + CASE WHEN m.ref_flag IS NOT NULL THEN 4^weights.ref_flag ELSE 0.0 END DESC, + -- Final sort on id, so that if two rules have the same sorting in the previous sort they have a defined order + -- This prevents "we changed the table order by updating a rule, and we started getting different results" + m.id LOOP + + -- Record the full matching row list + row_list := row_list || cur_matchpoint.id; + + -- No matchpoint yet? + IF matchpoint.id IS NULL THEN + -- Take the entire matchpoint as a starting point + matchpoint := cur_matchpoint; + CONTINUE; -- No need to look at this row any more. + END IF; + + -- Incomplete matchpoint? + IF matchpoint.circulate IS NULL THEN + matchpoint.circulate := cur_matchpoint.circulate; + END IF; + IF matchpoint.duration_rule IS NULL THEN + matchpoint.duration_rule := cur_matchpoint.duration_rule; + END IF; + IF matchpoint.recurring_fine_rule IS NULL THEN + matchpoint.recurring_fine_rule := cur_matchpoint.recurring_fine_rule; + END IF; + IF matchpoint.max_fine_rule IS NULL THEN + matchpoint.max_fine_rule := cur_matchpoint.max_fine_rule; + END IF; + IF matchpoint.hard_due_date IS NULL THEN + matchpoint.hard_due_date := cur_matchpoint.hard_due_date; + END IF; + IF matchpoint.total_copy_hold_ratio IS NULL THEN + matchpoint.total_copy_hold_ratio := cur_matchpoint.total_copy_hold_ratio; + END IF; + IF matchpoint.available_copy_hold_ratio IS NULL THEN + matchpoint.available_copy_hold_ratio := cur_matchpoint.available_copy_hold_ratio; + END IF; + IF matchpoint.renewals IS NULL THEN + matchpoint.renewals := cur_matchpoint.renewals; + END IF; + IF matchpoint.grace_period IS NULL THEN + matchpoint.grace_period := cur_matchpoint.grace_period; + END IF; + END LOOP; + + -- Check required fields + IF matchpoint.circulate IS NOT NULL AND + matchpoint.duration_rule IS NOT NULL AND + matchpoint.recurring_fine_rule IS NOT NULL AND + matchpoint.max_fine_rule IS NOT NULL THEN + -- All there? We have a completed match. + result.success := true; + END IF; + + -- Include the assembled matchpoint, even if it isn't complete + result.matchpoint := matchpoint; + + -- Include (for debugging) the full list of matching rows + result.buildrows := row_list; + + -- Hand the result back to caller + RETURN result; +END; +$func$ LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION action.item_user_circ_test( circ_ou INT, match_item BIGINT, match_user INT, renewal BOOL ) RETURNS SETOF action.circ_matrix_test_result AS $func$ +DECLARE + user_object actor.usr%ROWTYPE; + standing_penalty config.standing_penalty%ROWTYPE; + item_object asset.copy%ROWTYPE; + item_status_object config.copy_status%ROWTYPE; + item_location_object asset.copy_location%ROWTYPE; + result action.circ_matrix_test_result; + circ_test action.found_circ_matrix_matchpoint; + circ_matchpoint config.circ_matrix_matchpoint%ROWTYPE; + out_by_circ_mod config.circ_matrix_circ_mod_test%ROWTYPE; + circ_mod_map config.circ_matrix_circ_mod_test_map%ROWTYPE; + hold_ratio action.hold_stats%ROWTYPE; + penalty_type TEXT; + items_out INT; + context_org_list INT[]; + done BOOL := FALSE; +BEGIN + -- Assume success unless we hit a failure condition + result.success := TRUE; + + -- Fail if the user is BARRED + SELECT INTO user_object * FROM actor.usr WHERE id = match_user; + + -- Fail if we couldn't find the user + IF user_object.id IS NULL THEN + result.fail_part := 'no_user'; + result.success := FALSE; + done := TRUE; + RETURN NEXT result; + RETURN; + END IF; + + SELECT INTO item_object * FROM asset.copy WHERE id = match_item; + + -- Fail if we couldn't find the item + IF item_object.id IS NULL THEN + result.fail_part := 'no_item'; + result.success := FALSE; + done := TRUE; + RETURN NEXT result; + RETURN; + END IF; + + IF user_object.barred IS TRUE THEN + result.fail_part := 'actor.usr.barred'; + result.success := FALSE; + done := TRUE; + RETURN NEXT result; + END IF; + + -- Fail if the item can't circulate + IF item_object.circulate IS FALSE THEN + result.fail_part := 'asset.copy.circulate'; + result.success := FALSE; + done := TRUE; + RETURN NEXT result; + END IF; + + -- Fail if the item isn't in a circulateable status on a non-renewal + IF NOT renewal AND item_object.status NOT IN ( 0, 7, 8 ) THEN + result.fail_part := 'asset.copy.status'; + result.success := FALSE; + done := TRUE; + RETURN NEXT result; + ELSIF renewal AND item_object.status <> 1 THEN + result.fail_part := 'asset.copy.status'; + result.success := FALSE; + done := TRUE; + RETURN NEXT result; + END IF; + + -- Fail if the item can't circulate because of the shelving location + SELECT INTO item_location_object * FROM asset.copy_location WHERE id = item_object.location; + IF item_location_object.circulate IS FALSE THEN + result.fail_part := 'asset.copy_location.circulate'; + result.success := FALSE; + done := TRUE; + RETURN NEXT result; + END IF; + + SELECT INTO circ_test * FROM action.find_circ_matrix_matchpoint(circ_ou, item_object, user_object, renewal); + + circ_matchpoint := circ_test.matchpoint; + result.matchpoint := circ_matchpoint.id; + result.circulate := circ_matchpoint.circulate; + result.duration_rule := circ_matchpoint.duration_rule; + result.recurring_fine_rule := circ_matchpoint.recurring_fine_rule; + result.max_fine_rule := circ_matchpoint.max_fine_rule; + result.hard_due_date := circ_matchpoint.hard_due_date; + result.renewals := circ_matchpoint.renewals; + result.grace_period := circ_matchpoint.grace_period; + result.buildrows := circ_test.buildrows; + + -- Fail if we couldn't find a matchpoint + IF circ_test.success = false THEN + result.fail_part := 'no_matchpoint'; + result.success := FALSE; + done := TRUE; + RETURN NEXT result; + RETURN; -- All tests after this point require a matchpoint. No sense in running on an incomplete or missing one. + END IF; + + -- Apparently....use the circ matchpoint org unit to determine what org units are valid. + SELECT INTO context_org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( circ_matchpoint.org_unit ); + + IF renewal THEN + penalty_type = '%RENEW%'; + ELSE + penalty_type = '%CIRC%'; + END IF; + + FOR standing_penalty IN + SELECT DISTINCT csp.* + FROM actor.usr_standing_penalty usp + JOIN config.standing_penalty csp ON (csp.id = usp.standing_penalty) + WHERE usr = match_user + AND usp.org_unit IN ( SELECT * FROM unnest(context_org_list) ) + AND (usp.stop_date IS NULL or usp.stop_date > NOW()) + AND csp.block_list LIKE penalty_type LOOP + + result.fail_part := standing_penalty.name; + result.success := FALSE; + done := TRUE; + RETURN NEXT result; + END LOOP; + + -- Fail if the test is set to hard non-circulating + IF circ_matchpoint.circulate IS FALSE THEN + result.fail_part := 'config.circ_matrix_test.circulate'; + result.success := FALSE; + done := TRUE; + RETURN NEXT result; + END IF; + + -- Fail if the total copy-hold ratio is too low + IF circ_matchpoint.total_copy_hold_ratio IS NOT NULL THEN + SELECT INTO hold_ratio * FROM action.copy_related_hold_stats(match_item); + IF hold_ratio.total_copy_ratio IS NOT NULL AND hold_ratio.total_copy_ratio < circ_matchpoint.total_copy_hold_ratio THEN + result.fail_part := 'config.circ_matrix_test.total_copy_hold_ratio'; + result.success := FALSE; + done := TRUE; + RETURN NEXT result; + END IF; + END IF; + + -- Fail if the available copy-hold ratio is too low + IF circ_matchpoint.available_copy_hold_ratio IS NOT NULL THEN + IF hold_ratio.hold_count IS NULL THEN + SELECT INTO hold_ratio * FROM action.copy_related_hold_stats(match_item); + END IF; + IF hold_ratio.available_copy_ratio IS NOT NULL AND hold_ratio.available_copy_ratio < circ_matchpoint.available_copy_hold_ratio THEN + result.fail_part := 'config.circ_matrix_test.available_copy_hold_ratio'; + result.success := FALSE; + done := TRUE; + RETURN NEXT result; + END IF; + END IF; + + -- Fail if the user has too many items with specific circ_modifiers checked out + FOR out_by_circ_mod IN SELECT * FROM config.circ_matrix_circ_mod_test WHERE matchpoint = circ_matchpoint.id LOOP + SELECT INTO items_out COUNT(*) + FROM action.circulation circ + JOIN asset.copy cp ON (cp.id = circ.target_copy) + WHERE circ.usr = match_user + AND circ.circ_lib IN ( SELECT * FROM unnest(context_org_list) ) + AND circ.checkin_time IS NULL + AND (circ.stop_fines IN ('MAXFINES','LONGOVERDUE') OR circ.stop_fines IS NULL) + AND cp.circ_modifier IN (SELECT circ_mod FROM config.circ_matrix_circ_mod_test_map WHERE circ_mod_test = out_by_circ_mod.id); + IF items_out >= out_by_circ_mod.items_out THEN + result.fail_part := 'config.circ_matrix_circ_mod_test'; + result.success := FALSE; + done := TRUE; + RETURN NEXT result; + END IF; + END LOOP; + + -- If we passed everything, return the successful matchpoint id + IF NOT done THEN + RETURN NEXT result; + END IF; + + RETURN; +END; +$func$ LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION action.item_user_circ_test( INT, BIGINT, INT ) RETURNS SETOF action.circ_matrix_test_result AS $func$ + SELECT * FROM action.item_user_circ_test( $1, $2, $3, FALSE ); +$func$ LANGUAGE SQL; + +CREATE OR REPLACE FUNCTION action.item_user_renew_test( INT, BIGINT, INT ) RETURNS SETOF action.circ_matrix_test_result AS $func$ + SELECT * FROM action.item_user_circ_test( $1, $2, $3, TRUE ); +$func$ LANGUAGE SQL; + +-- Update recurring fine rules +UPDATE config.rule_recurring_fine SET grace_period=recurrence_interval; + +-- Update Circulation Data +-- Only update if we were told to and the circ hasn't been checked in +UPDATE action.circulation SET grace_period=fine_interval WHERE :CircGrace AND (checkin_time IS NULL); + +COMMIT; diff --git a/Open-ILS/src/support-scripts/fine_generator.pl b/Open-ILS/src/support-scripts/fine_generator.pl index 8859b13a7..3ab838a67 100755 --- a/Open-ILS/src/support-scripts/fine_generator.pl +++ b/Open-ILS/src/support-scripts/fine_generator.pl @@ -1,7 +1,7 @@ #!/usr/bin/perl # --------------------------------------------------------------------- -# Fine generator with default grace period param. -# ./object_dumper.pl +# Fine generator +# ./fine_generator.pl # --------------------------------------------------------------------- use strict; @@ -15,7 +15,9 @@ my $config = shift || die "bootstrap config required\n"; my $lockfile = shift || "/tmp/generate_fines-LOCK"; my $grace = shift; -$grace = '' if (!defined($grace) or $grace == 0); +if (defined($grace)) { + die "Grace period is now defined in the database. It should not be passed to the fine generator."; +} if (-e $lockfile) { open(F,$lockfile); @@ -44,7 +46,7 @@ if ($parallel == 1) { my $r = OpenSRF::AppSession ->create( 'open-ils.storage' ) - ->request( 'open-ils.storage.action.circulation.overdue.generate_fines' => $grace ); + ->request( 'open-ils.storage.action.circulation.overdue.generate_fines' ); while (!$r->complete) { $r->recv }; @@ -57,10 +59,10 @@ if ($parallel == 1) { ); my $storage = OpenSRF::AppSession->create("open-ils.storage"); - my $r = $storage->request('open-ils.storage.action.circulation.overdue.id_list', $grace); + my $r = $storage->request('open-ils.storage.action.circulation.overdue.id_list'); while (my $resp = $r->recv) { my $circ_id = $resp->content; - $multi_generator->request( 'open-ils.storage.action.circulation.overdue.generate_fines', $grace, $circ_id ); + $multi_generator->request( 'open-ils.storage.action.circulation.overdue.generate_fines', $circ_id ); } $storage->disconnect(); $multi_generator->session_wait(1); diff --git a/Open-ILS/web/js/ui/default/conify/global/config/circ_matrix_matchpoint.js b/Open-ILS/web/js/ui/default/conify/global/config/circ_matrix_matchpoint.js index f5d5b3a4b..6e8dd508e 100644 --- a/Open-ILS/web/js/ui/default/conify/global/config/circ_matrix_matchpoint.js +++ b/Open-ILS/web/js/ui/default/conify/global/config/circ_matrix_matchpoint.js @@ -23,6 +23,7 @@ function load(){ cmGrid.overrideWidgetArgs.available_copy_hold_ratio = {inherits : true}; cmGrid.overrideWidgetArgs.total_copy_hold_ratio = {inherits : true}; cmGrid.overrideWidgetArgs.renewals = {inherits : true}; + cmGrid.overrideWidgetArgs.grace_period = {inherits : true}; cmGrid.overrideWidgetArgs.hard_due_date = {inherits : true}; cmGrid.loadAll({order_by:{ccmm:'circ_modifier'}}); cmGrid.onEditPane = buildEditPaneAdditions; diff --git a/Open-ILS/web/templates/default/conify/global/config/circ_matrix_matchpoint.tt2 b/Open-ILS/web/templates/default/conify/global/config/circ_matrix_matchpoint.tt2 index c09012fbe..26b097708 100644 --- a/Open-ILS/web/templates/default/conify/global/config/circ_matrix_matchpoint.tt2 +++ b/Open-ILS/web/templates/default/conify/global/config/circ_matrix_matchpoint.tt2 @@ -9,7 +9,7 @@