From 6f87781e41e932f4bbd8f9fdf4f90bafdc85e1ba Mon Sep 17 00:00:00 2001 From: Kyle M Hall Date: Thu, 4 Oct 2012 10:34:49 -0400 Subject: [PATCH 2/2] [SIGNED-OFF] Bug 8253 - fix fine doubling, when upgrading from 3.6 to 3.8 - Add de-duplication script This script will remove these duplicate fines. To use, repeatably run this script until there are no more duplicates in the database. (fix_accountlines_rmdupfines_bug8253.pl) Duplicate fines would happen if you upgraded to a 3.8 version that does not have the bug8253 patch, and the misc/cronjobs/fines.pl is run. In 3.8 an upgrade to a more granular date/time was not addressed for pre-existing fine entries and this script removes the resulting duplicates. It also intelligently preserves the amount outstanding for payments already applied. If your version already had the bug8253 patch at the time of the upgrade, duplicate fines should not have been generatd. Signed-off-by: wajasu --- .../fix_accountlines_rmdupfines_bug8253.pl | 121 +++++++++++++++++++++ 1 file changed, 121 insertions(+) create mode 100755 misc/maintenance/fix_accountlines_rmdupfines_bug8253.pl diff --git a/misc/maintenance/fix_accountlines_rmdupfines_bug8253.pl b/misc/maintenance/fix_accountlines_rmdupfines_bug8253.pl new file mode 100755 index 0000000..33d62c6 --- /dev/null +++ b/misc/maintenance/fix_accountlines_rmdupfines_bug8253.pl @@ -0,0 +1,121 @@ +#!/usr/bin/perl +# +# Copyright (C) 2012 ByWater Solutions +# +# This file is part of Koha. +# +# Koha 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. +# +# Koha 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. +# +# You should have received a copy of the GNU General Public License along +# with Koha; if not, write to the Free Software Foundation, Inc., +# 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA. + +use strict; +use warnings; + +BEGIN { + # find Koha's Perl modules + # test carefully before changing this + use FindBin; + eval { require "$FindBin::Bin/../kohalib.pl" }; +} + +use C4::Context; +use C4::Installer; +use C4::Dates; + +use Getopt::Long; +use Data::Dumper; + +sub print_usage { + print <<_USAGE_ +$0: Remove duplicate fines + +Due to bug 8253, upgrading from Koha 3.6 to 3.8 may introduce duplicate fines. +This script will remove these duplicate fines. To use, repeatably run this +script until there are no more duplicates in the database. + +Parameters: + --confirm or -c Confirm you want to run the script. + --help or -h Print out this help message. +_USAGE_ +} + +my $help; +my $confirm; +my $result = GetOptions( + 'confirm|c' => \$confirm, + 'help|h' => \$help, +); +if ( $help || !$confirm ) { + print_usage(); + exit 0; +} + + +my $dbh = C4::Context->dbh; + +my $query = " + SELECT * FROM accountlines + WHERE ( accounttype = 'FU' OR accounttype = 'F' ) + AND description like '%23:59%' + ORDER BY borrowernumber, itemnumber, accountno, description +"; +my $sth = $dbh->prepare($query); +$sth->execute(); +my $results = $sth->fetchall_arrayref( {} ); + +$query = +"SELECT * FROM accountlines WHERE description LIKE ? AND description NOT LIKE ?"; +$sth = $dbh->prepare($query); + +my @fines; +foreach my $keeper (@$results) { + + warn "WORKING ON KEEPER: " . Data::Dumper::Dumper( $keeper ); + my ($description_to_match) = split( / 23:59/, $keeper->{'description'} ); + $description_to_match .= '%'; + + warn "DESCRIPTION TO MATCH: " . $description_to_match; + + $sth->execute( $description_to_match, $keeper->{'description'} ); + + my $has_changed = 0; + + while ( my $f = $sth->fetchrow_hashref() ) { + + warn "DELETING: " . Data::Dumper::Dumper( $f ); + + if ( $f->{'amountoutstanding'} < $keeper->{'amountoutstanding'} ) { + $keeper->{'amountoutstanding'} = $f->{'amountoutstanding'}; + $has_changed = 1; + } + + my $sql = + "DELETE FROM accountlines WHERE borrowernumber = ? AND accountno = ? AND itemnumber = ? AND date = ? AND description = ? LIMIT 1"; + $dbh->do( $sql, undef, $f->{'borrowernumber'}, + $f->{'accountno'}, $f->{'itemnumber'}, $f->{'date'}, + $f->{'description'} ); + } + + if ($has_changed) { + my $sql = + "UPDATE accountlines SET amountoutstanding = ? WHERE borrowernumber = ? AND accountno = ? AND itemnumber = ? AND date = ? AND description = ? LIMIT 1"; + $dbh->do( + $sql, undef, + $keeper->{'amountoutstanding'}, $keeper->{'borrowernumber'}, + $keeper->{'accountno'}, $keeper->{'itemnumber'}, + $keeper->{'date'}, $keeper->{'description'} + ); + } +} + +exit; + -- 1.7.12.1