From fdc30d3852553bd41cdb2cde6a881794e177e3d9 Mon Sep 17 00:00:00 2001 From: Kyle M Hall Date: Thu, 21 Jun 2012 12:15:11 -0400 Subject: [PATCH] Bug 8253 - Fine doubling - Remove Previous Duplicates --- installer/data/mysql/updatedatabase.pl | 48 ++++++++++++++++++++++++++++++++ 1 files changed, 48 insertions(+), 0 deletions(-) diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl index d06a188..24bace9 100755 --- a/installer/data/mysql/updatedatabase.pl +++ b/installer/data/mysql/updatedatabase.pl @@ -5392,6 +5392,54 @@ if (C4::Context->preference("Version") < TransformToNum($DBversion)) { SetVersion ($DBversion); } +$DBversion ="3.09.00.XXX"; +if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) { + 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 (Remove duplicate fines))\n"; + SetVersion($DBversion); +} + =head1 FUNCTIONS =head2 TableExists($table) -- 1.7.2.5