@@ -, +, @@ --- installer/data/mysql/updatedatabase.pl | 99 ++++++++++++++++++++++++++++++++ 1 files changed, 99 insertions(+), 0 deletions(-) --- a/installer/data/mysql/updatedatabase.pl +++ a/installer/data/mysql/updatedatabase.pl @@ -5556,6 +5556,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) --