View | Details | Raw Unified | Return to bug 8253
Collapse All | Expand All

(-)a/installer/data/mysql/updatedatabase.pl (-1 / +99 lines)
Lines 5556-5561 if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) { Link Here
5556
    SetVersion($DBversion);
5556
    SetVersion($DBversion);
5557
}
5557
}
5558
5558
5559
$DBversion ="3.09.00.XXX";
5560
if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) {
5561
    my $pref = C4::Context->preference('dateformat');
5562
    my ( $date_format, $date_regex );
5563
5564
    if ( $pref =~ /^iso/ ) {
5565
        $date_format = '____-__-__';
5566
    }
5567
    elsif ( $pref =~ /^metric/ ) {
5568
        $date_format = '__/__/____';
5569
    }
5570
    elsif ( $pref =~ /^us/ ) {
5571
        $date_format = '__/__/____';
5572
    }
5573
    else {
5574
        $date_format = '____-__-__';
5575
    }
5576
5577
    my $sql = "SELECT * FROM accountlines WHERE description LIKE '%$date_format%' AND description NOT LIKE '%$date_format __:__%' AND ( accounttype = 'FU' OR accounttype = 'F' )";
5578
    my $sth = $dbh->prepare( $sql );
5579
    $sth->execute();
5580
5581
    while ( my $row = $sth->fetchrow_hashref() ) {
5582
        my $old_description = $row->{'description'};
5583
5584
        my ( $year, $month, $day );
5585
        my $date;
5586
5587
        if ( $pref =~ /^iso/ ) {
5588
            ( $year, $month, $day ) = $old_description =~ /(\d+)-(\d+)-(\d+)/;
5589
            $date = "$year-$month-$day";
5590
        }
5591
        elsif ( $pref =~ /^metric/ ) {
5592
            ( $day, $month, $year ) = $old_description =~ /(\d+)\/(\d+)\/(\d+)/;
5593
            $date = "$day/$month/$year";
5594
        }
5595
        elsif ( $pref =~ /^us/ ) {
5596
            ( $month, $day, $year ) = $old_description =~ /(\d+)\/(\d+)\/(\d+)/;
5597
            $date = "$month/$day/$year";
5598
        }
5599
        else { ## default to iso
5600
            ( $year, $month, $day ) = $old_description =~ /(\d+)-(\d+)-(\d+)/;
5601
            $date = "$year-$month-$day";
5602
        }
5603
5604
        my $datetime = "$date 23:59";
5605
5606
        my $new_description = $old_description;
5607
        $new_description =~ s/$date/$datetime/g;
5608
5609
        $dbh->do("UPDATE accountlines SET description = ? WHERE description = ?", undef, $new_description, $old_description);
5610
    }
5611
5612
    my $query = "
5613
        SELECT * FROM accountlines
5614
        WHERE ( accounttype =  'FU' OR accounttype =  'F' )
5615
        AND description like '%23:59%'
5616
        ORDER BY borrowernumber, itemnumber, accountno, description
5617
    ";
5618
    my $sth = $dbh->prepare( $query );
5619
    $sth->execute();
5620
    my $results = $sth->fetchall_arrayref({});
5621
5622
    $query = "SELECT * FROM accountlines WHERE description LIKE ? AND description NOT LIKE ?";
5623
    $sth = $dbh->prepare( $query );
5624
5625
    my @fines;
5626
    foreach my $keeper ( @$results ) {
5627
        #warn "WORKING ON KEEPER: " . Data::Dumper::Dumper( $keeper );
5628
        my ( $description_to_match ) = split( / 23:59/, $keeper->{'description'} );
5629
        $description_to_match .= '%';
5630
        #warn "DESCRIPTION TO MATCH: " . $description_to_match;
5631
5632
        $sth->execute( $description_to_match, $keeper->{'description'} );
5633
5634
        my $has_changed = 0;
5635
5636
        while ( my $f = $sth->fetchrow_hashref() ) {
5637
            #warn "DELETING: " . Data::Dumper::Dumper( $f );
5638
5639
            if ( $f->{'amountoutstanding'} < $keeper->{'amountoutstanding'} ) {
5640
                $keeper->{'amountoutstanding'} = $f->{'amountoutstanding'};
5641
                $has_changed = 1;
5642
            }
5643
5644
            my $sql = "DELETE FROM accountlines WHERE borrowernumber = ? AND accountno = ? AND itemnumber = ? AND date = ? AND description = ? LIMIT 1";
5645
            $dbh->do($sql,undef,$f->{'borrowernumber'},$f->{'accountno'},$f->{'itemnumber'}, $f->{'date'}, $f->{'description'});
5646
        }
5647
5648
        if ( $has_changed ) {
5649
            my $sql = "UPDATE accountlines SET amountoutstanding = ? WHERE borrowernumber = ? AND accountno = ? AND itemnumber = ? AND date = ? AND description = ? LIMIT 1";
5650
            $dbh->do($sql,undef,$keeper->{'amountoutstanding'},$keeper->{'borrowernumber'},$keeper->{'accountno'},$keeper->{'itemnumber'}, $keeper->{'date'}, $keeper->{'description'});
5651
        }
5652
    }
5653
5654
    print "Upgrade to $DBversion done (Fix fine descriptions and remove duplicate fines)\n";
5655
    SetVersion($DBversion);
5656
}
5657
5559
=head1 FUNCTIONS
5658
=head1 FUNCTIONS
5560
5659
5561
=head2 TableExists($table)
5660
=head2 TableExists($table)
5562
- 

Return to bug 8253