From 73a8eaaf265b992bc37b7930c1a4c0a5f75e6a57 Mon Sep 17 00:00:00 2001 From: Dan Wells Date: Mon, 12 Feb 2018 10:11:33 -0500 Subject: [PATCH] LP#1748924 Enhanced Billing Timestamp Support As discussed at Hack-a-way 2016, rather than continue to try to cram multiple meanings into one timestamp, let's create a complete set of all the useful timestamps for a typical billing. In this new config, every billing will have a 'create_date', then most (overdues) will also describe when they start and end. billing_ts is now deprecated, but will continue to exist for backwards compatibility. It will be managed by trigger to approximate its current definition; equal to 'period_end' for overdues, equal to 'create_date' for other billings. Signed-off-by: Dan Wells Signed-off-by: Jeff Godin --- Open-ILS/examples/fm_IDL.xml | 5 +++- .../OpenILS/Application/Circ/CircCommon.pm | 25 +++++++++++-------- .../lib/OpenILS/Application/Circ/Circulate.pm | 3 ++- .../lib/OpenILS/Application/Circ/Money.pm | 3 ++- Open-ILS/src/sql/Pg/080.schema.money.sql | 18 +++++++++++-- 5 files changed, 38 insertions(+), 16 deletions(-) diff --git a/Open-ILS/examples/fm_IDL.xml b/Open-ILS/examples/fm_IDL.xml index ae534967bd..b272b9ffab 100644 --- a/Open-ILS/examples/fm_IDL.xml +++ b/Open-ILS/examples/fm_IDL.xml @@ -7987,7 +7987,10 @@ SELECT usr, - + + + + diff --git a/Open-ILS/src/perlmods/lib/OpenILS/Application/Circ/CircCommon.pm b/Open-ILS/src/perlmods/lib/OpenILS/Application/Circ/CircCommon.pm index c7caea75a3..879644f189 100644 --- a/Open-ILS/src/perlmods/lib/OpenILS/Application/Circ/CircCommon.pm +++ b/Open-ILS/src/perlmods/lib/OpenILS/Application/Circ/CircCommon.pm @@ -199,7 +199,7 @@ sub reopen_xact { sub create_bill { - my($class, $e, $amount, $btype, $type, $xactid, $note, $billing_ts) = @_; + my($class, $e, $amount, $btype, $type, $xactid, $note, $period_start, $period_end) = @_; $logger->info("The system is charging $amount [$type] on xact $xactid"); $note ||= 'SYSTEM GENERATED'; @@ -209,7 +209,8 @@ sub create_bill { my $bill = Fieldmapper::money::billing->new; $bill->xact($xactid); $bill->amount($amount); - $bill->billing_ts($billing_ts); + $bill->period_start($period_start); + $bill->period_end($period_end); $bill->billing_type($type); $bill->btype($btype); $bill->note($note); @@ -579,7 +580,7 @@ sub generate_fines { my $tz = $U->ou_ancestor_setting_value( $c->$circ_lib_method, 'lib.timezone') || 'local'; - my ($latest_billing_ts, $latest_amount) = ('',0); + my ($latest_period_end, $latest_amount) = ('',0); for (my $bill = 1; $bill <= $pending_fine_count; $bill++) { if ($current_fine_total >= $max_fine) { @@ -596,16 +597,17 @@ sub generate_fines { } # Use org time zone (or default to 'local') - my $billing_ts = DateTime->from_epoch( epoch => $last_fine, time_zone => $tz ); + my $period_end = DateTime->from_epoch( epoch => $last_fine, time_zone => $tz ); my $current_bill_count = $bill; while ( $current_bill_count ) { - $billing_ts->add( seconds_to_interval_hash( $fine_interval ) ); + $period_end->add( seconds_to_interval_hash( $fine_interval ) ); $current_bill_count--; } + my $period_start = $period_end->clone->subtract( seconds_to_interval_hash( $fine_interval - 1 ) ); - my $timestamptz = $billing_ts->strftime('%FT%T%z'); + my $timestamptz = $period_end->strftime('%FT%T%z'); if (!$skip_closed_check) { - my $dow = $billing_ts->day_of_week_0(); + my $dow = $period_end->day_of_week_0(); my $dow_open = "dow_${dow}_open"; my $dow_close = "dow_${dow}_close"; @@ -630,7 +632,7 @@ sub generate_fines { } $current_fine_total += $this_billing_amount; $latest_amount += $this_billing_amount; - $latest_billing_ts = $timestamptz; + $latest_period_end = $timestamptz; my $bill = Fieldmapper::money::billing->new; $bill->xact($c->id); @@ -638,13 +640,14 @@ sub generate_fines { $bill->billing_type("Overdue materials"); $bill->btype(1); $bill->amount(sprintf('%0.2f', $this_billing_amount/100)); - $bill->billing_ts($timestamptz); + $bill->period_start($period_start->strftime('%FT%T%z')); + $bill->period_end($timestamptz); $e->create_money_billing($bill); } - $conn->respond( "\t\tAdding fines totaling $latest_amount for overdue up to $latest_billing_ts\n" ) - if ($conn and $latest_billing_ts and $latest_amount); + $conn->respond( "\t\tAdding fines totaling $latest_amount for overdue up to $latest_period_end\n" ) + if ($conn and $latest_period_end and $latest_amount); # Calculate penalties inline 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 deb9541e32..3f3d9461b1 100644 --- a/Open-ILS/src/perlmods/lib/OpenILS/Application/Circ/Circulate.pm +++ b/Open-ILS/src/perlmods/lib/OpenILS/Application/Circ/Circulate.pm @@ -4222,7 +4222,8 @@ sub checkin_handle_lost_or_lo_now_found_restore_od { $ods->[0]->billing_type(), $self->circ->id(), "System: $tag RETURNED - OVERDUES REINSTATED", - $ods->[0]->billing_ts() # date this restoration the same as the last overdue (for possible subsequent fine generation) + $ods->[-1]->period_start(), + $ods->[0]->period_end() # date this restoration the same as the last overdue (for possible subsequent fine generation) ); } } diff --git a/Open-ILS/src/perlmods/lib/OpenILS/Application/Circ/Money.pm b/Open-ILS/src/perlmods/lib/OpenILS/Application/Circ/Money.pm index f177e5d120..009da4001e 100644 --- a/Open-ILS/src/perlmods/lib/OpenILS/Application/Circ/Money.pm +++ b/Open-ILS/src/perlmods/lib/OpenILS/Application/Circ/Money.pm @@ -978,7 +978,8 @@ sub _rebill_xact { $billing->billing_type, $xact_id, "System: MANUAL ADJUSTMENT, BILLING #".$billing->id." REINSTATED\n(PREV: ".$billing->note.")", - $billing->billing_ts() + $billing->period_start(), + $billing->period_end() ); return $evt if $evt; $rebill_amount += $billing->amount; diff --git a/Open-ILS/src/sql/Pg/080.schema.money.sql b/Open-ILS/src/sql/Pg/080.schema.money.sql index 6265ae8da2..b5f3b27241 100644 --- a/Open-ILS/src/sql/Pg/080.schema.money.sql +++ b/Open-ILS/src/sql/Pg/080.schema.money.sql @@ -51,18 +51,32 @@ CREATE INDEX m_g_usr_idx ON "money".grocery (usr); CREATE TABLE money.billing ( id BIGSERIAL PRIMARY KEY, xact BIGINT NOT NULL, -- money.billable_xact.id - billing_ts TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(), + billing_ts TIMESTAMP WITH TIME ZONE NOT NULL, -- DEPRECATED, legacy only voided BOOL NOT NULL DEFAULT FALSE, voider INT, void_time TIMESTAMP WITH TIME ZONE, amount NUMERIC(6,2) NOT NULL, billing_type TEXT NOT NULL, btype INT NOT NULL REFERENCES config.billing_type (id) ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED, - note TEXT + note TEXT, + create_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(), + period_start TIMESTAMP WITH TIME ZONE, + period_end TIMESTAMP WITH TIME ZONE ); CREATE INDEX m_b_xact_idx ON money.billing (xact); CREATE INDEX m_b_time_idx ON money.billing (billing_ts); +CREATE INDEX m_b_create_date_idx ON money.billing (create_date); +CREATE INDEX m_b_period_start_idx ON money.billing (period_start); +CREATE INDEX m_b_period_end_idx ON money.billing (period_end); CREATE INDEX m_b_voider_idx ON money.billing (voider); -- helps user merge function speed +CREATE OR REPLACE FUNCTION money.maintain_billing_ts () RETURNS TRIGGER AS $$ +BEGIN + NEW.billing_ts := COALESCE(NEW.period_end, NEW.create_date); + RETURN NEW; +END; +$$ LANGUAGE PLPGSQL; +CREATE TRIGGER maintain_billing_ts_tgr BEFORE INSERT OR UPDATE ON money.billing FOR EACH ROW EXECUTE PROCEDURE money.maintain_billing_ts(); + CREATE TABLE money.payment ( id BIGSERIAL PRIMARY KEY, -- 2.43.2