X-Git-Url: http://git.rot13.org/?p=ferlib2koha.git;a=blobdiff_plain;f=ferlib2koha.pl;h=34b65024c8167996c26fea74134defc8ea886b2a;hp=9087e3b9051d566f9981429eff7986684e3bdd70;hb=HEAD;hpb=a40522059025b7c9063ea761ab258a28c255789f diff --git a/ferlib2koha.pl b/ferlib2koha.pl old mode 100755 new mode 100644 index 9087e3b..34b6502 --- a/ferlib2koha.pl +++ b/ferlib2koha.pl @@ -4,12 +4,15 @@ use strict; use DBI; use Data::Dump qw/dump/; use Algorithm::CheckDigits; +use Text::Unaccent; + +# sudo apt-get install libdbd-sqlite3-perl libdbd-csv-perl libtext-unaccent-perl $|++; my $import = { borrowers => $ENV{FULL} || 0, - issues => $ENV{FULL} || 1, + issues => $ENV{FULL} || 0, reserves => $ENV{FULL} || 0, barcode => $ENV{FULL} || 0, }; @@ -24,15 +27,25 @@ sub lookup { my ( $dbh, $t, $k, $v, $s, $default ) = @_; my $hash; my $key = "$t $k $v"; + my $max = $v =~ s/max\((\w+)\)/$1/; if ( exists $lookup->{$key} ) { $hash = $lookup->{$key}; } else { warn "# select $k,$v from $t"; my $sth = $dbh->prepare( "select $k,$v from $t" ); $sth->execute; + my @non_unique; while (my $row = $sth->fetchrow_hashref() ) { + if ( ! $max && exists $hash->{ $row->{$k} } ) { + push @non_unique, $row->{$k}; + next; + } $hash->{ $row->{$k} } = $row->{$v}; } + if ( @non_unique ) { + warn "# remove non-unique ", dump(@non_unique); + delete $hash->{$_} foreach @non_unique; + } $lookup->{$key} = $hash; warn "# lookup $key = ",dump($hash); } @@ -105,19 +118,52 @@ sub borrowers { insert 'borrowers' => $row; + my $borrowernumber = $k->{mysql_insertid}; + insert 'borrower_attributes' => { - borrowernumber => $k->{mysql_insertid}, + borrowernumber => $borrowernumber, code => 'JMBG', attribute => $jmbg, } if $jmbg; + foreach my $id ( 6,1,4,5,2 ) { + + insert 'borrower_message_preferences' => { + borrowernumber => $borrowernumber, + message_attribute_id => $id, + days_in_advance => $id == 2 ? 0 : undef, + }; + + insert 'borrower_message_transport_preferences' => { + borrower_message_preference_id => $k->{mysql_insertid}, + message_transport_type => 'email', + } if $id == 4; + + } + + } +} + +sub sql { + my ($db,$sql) = @_; + foreach (split(/;/,$sql)) { + next if m/^\s*$/; + warn "# sql $_"; + $db->do($_); } } if ( $import->{borrowers} ) { -$k->do(qq{ delete from borrowers where borrowernumber > 57 }); -$k->do(qq{ delete from borrower_attributes where borrowernumber > 57 }); +sql $k => qq{ +delete from borrowers; +alter table borrowers auto_increment = 1; +delete from borrower_attributes; +alter table borrower_attributes auto_increment = 1; +delete from borrower_message_preferences where borrowernumber is not null ; +alter table borrower_message_preferences auto_increment = 1; +}; + borrowers qq{ select @@ -222,7 +268,7 @@ sub issues { }; $stat->{$_} = $row->{$_} foreach (qw( itemnumber borrowernumber )); - $row->{returndate} ||= '31.12.2011'; + $row->{date_due} ||= '31.12.2011'; insert $table => $row; insert statistics => $stat; @@ -241,30 +287,56 @@ select from posud }; -$k->do('delete from old_issues'); -$k->do('delete from issues'); +sql $k => qq{ +delete from old_issues; +alter table old_issues auto_increment = 1; +delete from issues; +alter table issues auto_increment = 1; +}; issues 'old_issues' => qq{$posud_sql where datum_vra != ''}; issues 'issues' => qq{$posud_sql where datum_vra == ''}; -$k->do($_) foreach split(/;/, qq{ +sql $k => qq{ update items join issues on items.itemnumber=issues.itemnumber set onloan = date_due, datelastborrowed = issuedate ; update items set datelastseen = datelastborrowed ; +create temporary table old_returndate as +select itemnumber,max(returndate) as returndate +from old_issues +group by itemnumber,returndate +; update items -join old_issues on items.itemnumber=old_issues.itemnumber -set datelastseen = returndate where returndate > datelastseen -}); +join old_returndate on items.itemnumber=old_returndate.itemnumber +set datelastseen = returndate +where returndate > datelastseen or datelastseen is null +; +create temporary table old_issuedate as +select itemnumber,max(issuedate) as issuedate +from old_issues +group by itemnumber,issuedate +; +update items +join old_issuedate on items.itemnumber=old_issuedate.itemnumber +set datelastborrowed = issuedate +where datelastborrowed is null +; +update old_issues set timestamp = returndate +}; }; # import->{issues} if ( $import->{reserves} ) { -$k->do(qq{ delete from reserves where borrowernumber > 100 }); -$k->do(qq{ delete from old_reserves where borrowernumber > 100 }); +sql $k => qq{ +delete from reserves; +alter table reserves auto_increment = 1; +delete from old_reserves; +alter table old_reserves auto_increment = 1; +}; sub reserves { my ($table, $sql) = @_; @@ -280,7 +352,7 @@ sub reserves { $row->{itemnumber} ? 'W' : undef; $row->{borrowernumber} = lookup($k, 'borrowers', 'cardnumber' => 'borrowernumber', $row->{borrowernumber}); - $row->{biblionumber} = lookup($k, 'biblioitems', 'collectionvolume' => 'biblionumber', $row->{biblionumber}); + $row->{biblionumber} = lookup($k, 'biblioitems', 'collectionvolume' => 'max(biblionumber)', $row->{biblionumber}); $row->{itemnumber} = lookup($k, 'items', 'barcode' => 'itemnumber', $row->{itemnumber}); insert $table => $row; @@ -329,6 +401,56 @@ while( my $row = $sth->fetchrow_hashref ) { } # import->{barcode} +sub aqbooksellers { + my ($sql) = @_; + my $sth = $f->prepare($sql); + $sth->execute; + + $insert = undef; + + while (my $row = $sth->fetchrow_hashref ) { + insert 'aqbooksellers' => $row; + } +} + +sql $k => qq{ +delete from aqbooksellers; +alter table aqbooksellers auto_increment = 1; +}; +aqbooksellers qq{ +select + ozn_nacdob as notes, + opis_nacdob as name, + 'HRK' as currency +from nacdob +}; +my $sth = $k->prepare(qq{ +select + borrowernumber, + firstname, + surname, + cardnumber +from borrowers +where categorycode = 'D' and email is null +group by firstname,surname +having count(*) = 1 -- update only unique users +}); +my $update_borrower = $k->prepare(qq{ +update borrowers +set email = ?, userid = ?, cardnumber = ? +where borrowernumber = ? +}); +$sth->execute; +while (my $row = $sth->fetchrow_hashref) { + my $email = lc unac_string( 'utf-8', $row->{firstname} . '.' . $row->{surname} . '@fer.hr' ); + if ( my $nick = lookup($u, 'users', 'email', 'nick', $email, undef) ) { + my $cardnumber = lookup($u, 'users', 'email', 'jmbag', $email, undef) || next; + warn "# FIXED nick $row->{borrowernumber} $nick $email $cardnumber\n"; +eval { + $update_borrower->execute( $email, $nick, $cardnumber, $row->{borrowernumber} ); +}; + } +} $k->commit;