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

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

Return to bug 8253