From 7118e229dec0aa94263e399a7881fcbd897d828c Mon Sep 17 00:00:00 2001 From: Chris Sharp Date: Thu, 9 Apr 2020 13:35:35 -0400 Subject: [PATCH] add batch forgive fines script --- overdues/README.md | 31 +++++++ overdues/batch_forgive_fines.pl | 100 +++++++++++++++++++++ .../legacy/batch_void_fines.sh | 0 3 files changed, 131 insertions(+) create mode 100644 overdues/README.md create mode 100755 overdues/batch_forgive_fines.pl rename batch_void_fines.sh => overdues/legacy/batch_void_fines.sh (100%) diff --git a/overdues/README.md b/overdues/README.md new file mode 100644 index 0000000..e24f0a2 --- /dev/null +++ b/overdues/README.md @@ -0,0 +1,31 @@ +Batch Forgive Fines +=================== + +Create a table to work from +--------------------------- + +- Example 1: Forgive all fines/fees resulting from circulations at Thomas County Public Library: + +`select x.id as xact_id, u.last_xact_id as usr_last_xact_id, u.id as usr_id, x.* into myschema.my_forgiven_fines_and_fees from money.materialized_billable_xact_summary x join action.circulation c on (x.id = c.id) join actor.usr u on (c.usr = u.id) join actor.org_unit cl on (c.circ_lib = cl.id) where cl.shortname ~ '^TCPLS' and x.balance_owed > '0.00';` + +- Example 2: Forgive just fines, excluding fees and lost/longoverdue charges: + +`select x.id as xact_id, u.last_xact_id as usr_last_xact_id, u.id as usr_id, x.* into myschema.my_forgiven_fines_only from money.materialized_billable_xact_summary x join action.circulation c on (x.id = c.id) join actor.usr u on (c.usr = u.id) join actor.org_unit cl on (c.circ_lib = cl.id) where cl.shortname ~ '^TCPLS' and x.balance_owed > '0.00' and x.last_billing_type ~* '^overdue';` + +- Example 3: Forgive "grocery" (staff-created) bills: + +`select x.id as xact_id, u.last_xact_id as usr_last_xact_id, u.id as usr_id, x.* into myschema.my_forgiven_grocery from money.materialized_billable_xact_summary x join money.grocery g on (x.id = g.id) join actor.usr u on (g.usr = u.id) join actor.org_unit gl on (g.billing_location = gl.id) where cl.shortname ~ '^TCPLS' and x.balance_owed > '0.00';` + +Adjust the Perl to include the table +------------------------------------ + +Edit `batch_forgive_fines.pl` and change the assignment of `$source_table` to be the name of the table you created above. + +Run the script +-------------- + +You need the username and password of a privileged Evergreen user (ex. "admin") and the name of a workstation (ex. "STATELIB-A-csharp-firefox"). Then run: + +`./batch_forgive_fines.pl ` + +substituting the actual username, password, and workstation name (no brackets). diff --git a/overdues/batch_forgive_fines.pl b/overdues/batch_forgive_fines.pl new file mode 100755 index 0000000..a9eefdc --- /dev/null +++ b/overdues/batch_forgive_fines.pl @@ -0,0 +1,100 @@ +#!/usr/bin/perl +# --------------------------------------------------------------- +# Copyright © 2014 Merrimack Valley Library Consortium +# Jason Stephenson +# Copyright (C) 2018 Georgia Public Library Service (Modifications) +# Chris Sharp +# +# This program is free software; you can redistribute it and/or modify +# it under the terms of the GNU General Public License as published by +# the Free Software Foundation; either version 2 of the License, or +# (at your option) any later version. +# +# This program is distributed in the hope that it will be useful, +# but WITHOUT ANY WARRANTY; without even the implied warranty of +# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +# GNU General Public License for more details. +# --------------------------------------------------------------- + +use strict; +use warnings; + +use OpenILS::Utils::Cronscript; +use Data::Dumper; +use DBI; + +my $source_table = "csharp.tcpls_circ_fines_purge"; + +my $apputils = 'OpenILS::Application::AppUtils'; + +my $script = OpenILS::Utils::Cronscript->new({nolockfile=>1}); + +my $login = { + username => $ARGV[0], + password => $ARGV[1], + workstation => $ARGV[2], + type => 'staff' +}; + +my $authtoken = $script->authenticate($login); + +die "failed to authenticate" unless($authtoken); + +END { + $script->logout(); +} + +sub retrieve_usr_last_xact_id { + my $usr_id = shift; + my $e = $script->editor(authtoken=>$authtoken); + my $user = $e->retrieve_actor_user($usr_id) if ($usr_id); + return $e->event unless($user); + my $last_xact_id = $user->last_xact_id; + return $last_xact_id; +} + + +my $settings = { + host => "db01", + db => "evergreen", + user => "evergreen" +}; + +my $dbh = DBI->connect('DBI:Pg:dbname=' . $settings->{'db'} . ';host=' . $settings->{'host'}, $settings->{'user'}, + undef, + { + RaiseError => 1, + ShowErrorStatement => 0, + AutoCommit => 0 + } +) or die DBI->errstr; + +my $query = "select xact_id, usr_last_xact_id, usr_id, sum(balance_owed) as balance_owed from $source_table where balance_owed > '0.00' group by 1, 2, 3"; + +my ($xact_id, $usr_last_xact_id, $usr_id, $balance_owed); + +my $sth = $dbh->prepare($query); +$sth->execute(); +my $ref = $sth->bind_columns(\($xact_id, $usr_last_xact_id, $usr_id, $balance_owed)); + +while ($sth->fetch) { + print "applying forgive payment of $balance_owed to xact_id: $xact_id for usr_id: $usr_id\n"; + my @payments = [$xact_id, $balance_owed]; + $usr_last_xact_id = retrieve_usr_last_xact_id($usr_id); + my $result = $apputils->simplereq( + 'open-ils.circ', + 'open-ils.circ.money.payment', + $authtoken, + { + payment_type => "forgive_payment", + userid => $usr_id, + note => "Bills forgiven per TCPLS System Director - 4/9/2020", + payments => \@payments + }, + $usr_last_xact_id + ); + print Dumper $result; +} + +$sth->finish(); +$dbh->disconnect(); diff --git a/batch_void_fines.sh b/overdues/legacy/batch_void_fines.sh similarity index 100% rename from batch_void_fines.sh rename to overdues/legacy/batch_void_fines.sh -- 2.11.0