From 8bdd0c839ef1cf4b78cea27973a89e476271b854 Mon Sep 17 00:00:00 2001 From: Kyle M Hall Date: Tue, 19 Jun 2012 13:06:24 -0400 Subject: [PATCH] Bug 8253 - Fine doubling This issue only occurs when upgrading from pre-3.8 to post 3.8 Koha. The issue is caused by the change from dates to datetimes that was neccesary to implement hourly loans. All pre-3.8 fines have the date in the description. This date is how the fines script know which fine to update. If the fine script does not locate an existing row to update, it creates a new row. The switch from dates to datetimes means the fines script now looks for a format such as 'YYYY-MM-DD 00:00' ( for iso ), but all the previous fines are still in the format 'YYYY-MM-DD' and so it fails to find the matching row, and creates a new row instead. This commit consists of a database update that alters the dates in the accountlines description field to be datetimes instead. This eliminates future fine doubling. It is also safe to run on a system that has been previously upgraded, it will ignore any rows where the date is already of the format 'YYYY-MM-DD HH:MM' ( or whichever date format you have chosen ). The second part of this update will remove any existing duplicate fines that have been created as a result of this error. --- installer/data/mysql/updatedatabase.pl | 101 ++++++++++++++++++++++++++++++++ 1 files changed, 101 insertions(+), 0 deletions(-) diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl index dbceb8d..3e2aca8 100755 --- a/installer/data/mysql/updatedatabase.pl +++ b/installer/data/mysql/updatedatabase.pl @@ -5369,6 +5369,7 @@ if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) { SetVersion($DBversion); } +<<<<<<< HEAD $DBversion = "3.09.00.015"; if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) { $dbh->do(qq{ @@ -5392,6 +5393,7 @@ if (C4::Context->preference("Version") < TransformToNum($DBversion)) { SetVersion ($DBversion); } +<<<<<<< HEAD $DBversion = "3.09.00.018"; if (C4::Context->preference("Version") < TransformToNum($DBversion)) { $dbh->do("DROP TABLE IF EXISTS aqbudgetborrowers"); @@ -5556,6 +5558,105 @@ if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) { SetVersion($DBversion); } +$DBversion ="3.09.00.XXX"; +if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) { + my $pref = C4::Context->preference('dateformat'); + my ( $date_format, $date_regex ); + + if ( $pref =~ /^iso/ ) { + $date_format = '____-__-__'; + } + elsif ( $pref =~ /^metric/ ) { + $date_format = '__/__/____'; + } + elsif ( $pref =~ /^us/ ) { + $date_format = '__/__/____'; + } + else { + $date_format = '____-__-__'; + } + + my $sql = "SELECT * FROM accountlines WHERE description LIKE '%$date_format%' AND description NOT LIKE '%$date_format __:__%' AND ( accounttype = 'FU' OR accounttype = 'F' )"; + my $sth = $dbh->prepare( $sql ); + $sth->execute(); + + while ( my $row = $sth->fetchrow_hashref() ) { + my $old_description = $row->{'description'}; + + my ( $year, $month, $day ); + my $date; + + if ( $pref =~ /^iso/ ) { + ( $year, $month, $day ) = $old_description =~ /(\d+)-(\d+)-(\d+)/; + $date = "$year-$month-$day"; + } + elsif ( $pref =~ /^metric/ ) { + ( $day, $month, $year ) = $old_description =~ /(\d+)\/(\d+)\/(\d+)/; + $date = "$day/$month/$year"; + } + elsif ( $pref =~ /^us/ ) { + ( $month, $day, $year ) = $old_description =~ /(\d+)\/(\d+)\/(\d+)/; + $date = "$month/$day/$year"; + } + else { ## default to iso + ( $year, $month, $day ) = $old_description =~ /(\d+)-(\d+)-(\d+)/; + $date = "$year-$month-$day"; + } + + my $datetime = "$date 23:59"; + + my $new_description = $old_description; + $new_description =~ s/$date/$datetime/g; + + $dbh->do("UPDATE accountlines SET description = ? WHERE description = ?", undef, $new_description, $old_description); + } + + 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'}); + } + } + + print "Upgrade to $DBversion done (Fix fine descriptions and remove duplicate fines)\n"; + SetVersion($DBversion); +} + =head1 FUNCTIONS =head2 TableExists($table) -- 1.7.2.5