From f730c988b5fe4f3b0174456f598c5c722e93c96a 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 00:00' ( or whichever date format you have chosen ). --- installer/data/mysql/updatedatabase.pl | 56 ++++++++++++++++++++++++++++++++ 1 files changed, 56 insertions(+), 0 deletions(-) diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl index 9b02cb9..0eaaccc 100755 --- a/installer/data/mysql/updatedatabase.pl +++ b/installer/data/mysql/updatedatabase.pl @@ -5369,6 +5369,62 @@ 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); + } + + print "Upgrade to $DBversion done (Fix fine descriptions)\n"; + SetVersion($DBversion); +} =head1 FUNCTIONS -- 1.7.2.5