Bugzilla – Attachment 11087 Details for
Bug 8253
fix fine doubling, when upgrading from 3.6 to 3.8 (or later)
Home
|
New
|
Browse
|
Search
|
[?]
|
Reports
|
Help
|
New Account
|
Log In
[x]
|
Forgot Password
Login:
[x]
[patch]
Bug 8253 - Fine doubling
Bug-8253---Fine-doubling.patch (text/plain), 6.20 KB, created by
Kyle M Hall (khall)
on 2012-07-23 20:20:41 UTC
(
hide
)
Description:
Bug 8253 - Fine doubling
Filename:
MIME Type:
Creator:
Kyle M Hall (khall)
Created:
2012-07-23 20:20:41 UTC
Size:
6.20 KB
patch
obsolete
>From 8bdd0c839ef1cf4b78cea27973a89e476271b854 Mon Sep 17 00:00:00 2001 >From: Kyle M Hall <kyle@bywatersolutions.com> >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
You cannot view the attachment while viewing its details because your browser does not support IFRAMEs.
View the attachment on a separate page
.
View Attachment As Diff
View Attachment As Raw
Actions:
View
|
Diff
|
Splinter Review
Attachments on
bug 8253
:
10369
|
10405
|
10416
|
10429
|
10436
|
11087
|
11127
|
11660
|
12653
|
12665
|
12666
|
12667
|
12685
|
12686