X-Git-Url: http://git.rot13.org/?a=blobdiff_plain;f=ferlib2koha.pl;h=e702ddb8a769778fc13dbefdcc11587dcf4f30a7;hb=4429c343d51528fef3b1385aa65225dcdcc39ced;hp=5c298269bea5bb230260486a546ed5339af0b9e2;hpb=9c54a6214a24f79a5eb9a642c6957b788fa5db9c;p=ferlib2koha.git diff --git a/ferlib2koha.pl b/ferlib2koha.pl index 5c29826..e702ddb 100755 --- a/ferlib2koha.pl +++ b/ferlib2koha.pl @@ -3,12 +3,23 @@ use strict; use DBI; use Data::Dump qw/dump/; +use Algorithm::CheckDigits; + +# sudo apt-get install libdbd-sqlite3-perl libdbd-csv-perl $|++; +my $import = { + borrowers => $ENV{FULL} || 0, + issues => $ENV{FULL} || 0, + reserves => $ENV{FULL} || 0, + barcode => $ENV{FULL} || 0, +}; + my $f = DBI->connect("dbi:SQLite:dbname=knjiznica.sqlite","","", { RaiseError => 1, AutoCommit => 0 }) || die $DBI::errstr; $f->{sqlite_unicode} = 1; my $k = DBI->connect("dbi:mysql:database=koha_fer","","", { RaiseError => 1, AutoCommit => 0, mysql_enable_utf8 => 1 }) || die $DBI::errstr; +my $u = DBI->connect("dbi:CSV:","","", { RaiseError => 1, f_ext => '.csv', csv_sep_char => '|' }) || die $DBI::errstr; our $lookup; sub lookup { @@ -25,7 +36,7 @@ sub lookup { $hash->{ $row->{$k} } = $row->{$v}; } $lookup->{$key} = $hash; - warn dump($lookup); + warn "# lookup $key = ",dump($hash); } return unless length $s > 1; if ( ! exists $lookup->{$key}->{$s} ) { @@ -49,10 +60,11 @@ sub insert { foreach my $c ( grep { /(date|timestamp)/ } keys %$row ) { $row->{$c} =~ s/^(\d\d)\.(\d\d)\.(\d\d\d\d)/$3-$2-$1/; + $row->{$c} = undef if $row->{$c} eq ''; # XXX NULL dates istead of 0000-00-00 } $insert->{$table}->execute( values %$row ); - warn "# inserted ", dump $row; + warn "# inserted $table ", dump $row; } @@ -65,7 +77,7 @@ sub borrowers { while (my $row = $sth->fetchrow_hashref ) { - warn dump $row; +# warn dump $row; # poor man's (sqlite) outer join $row->{'country'} = lookup($f, 'drzava', 'ozn_drzava', 'naz_drzava', $row->{'country'}, $row->{contry} ); @@ -74,29 +86,73 @@ sub borrowers { $row->{'B_country'} = lookup($f, 'drzava', 'ozn_drzava', 'naz_drzava', $row->{'B_country'}, $row->{B_country} ); $row->{'B_city'} = lookup($f, 'mjesto', 'post_ozn', 'naz_mjesto', $row->{'B_city'}, $row->{B_city} ); - $row->{'email'} = lookup($f, 'clanost', 'ozn_vrclan||ozn_clan', 'email', $row->{'cardnumber'}, undef); + $row->{'B_email'} = lookup($f, 'clanost', 'ozn_vrclan||ozn_clan', 'email', $row->{'cardnumber'}, undef); $row->{'emailpro'} = lookup($f, 'clanost', 'ozn_vrclan||ozn_clan', 'email_k', $row->{'cardnumber'}, undef); + $row->{'email'} = lookup($u, 'users', 'jmbag', 'email', substr($row->{cardnumber},1), undef); # $row->{B_email} || $row->{emailpro} ) + + $row->{'userid'} = lookup($u, 'users', 'jmbag', 'nick', substr($row->{cardnumber},1), $row->{cardnumber}); $row->{sex} =~ s/\x{17D}/F/; $row->{city} ||= '?'; # not null + $row->{borrowernotes} = lookup($f, 'clanorg', 'ozn_vrclan||ozn_clan', 'napom_clan', $row->{'cardnumber'}, undef); + + $row->{title} = lookup($f, 'titula', 'ozn_titula', 'opis_titula', $row->{title}, $row->{title}); + + $row->{dateexpiry} ||= '01.01.2020'; # next revision + my $jmbg = delete $row->{jmbg}; 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($_); } } -$k->do(qq{ delete from borrowers where borrowernumber > 57 }); -$k->do(qq{ delete from borrower_attributes where borrowernumber > 57 }); +if ( $import->{borrowers} ) { + +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 @@ -132,7 +188,7 @@ select 'D' || sif_djel as cardnumber, ime_djel as firstname, prez_djel as surname, - ozn_titula as title, -- FIXME + ozn_titula as title, -- sif_orgjed (REF orgjed) as B_address, -- sif_strsp_djel as ??, tel_djel as phonepro, @@ -155,8 +211,8 @@ select 'O' || mbr_clan as cardnumber, ime_clan as firstname, prez_clan as surname, - ozn_titula as title, -- ? - zvanje_clan as borrowernotes, -- ? + ozn_titula as title, + zvanje_clan as ethnicity, -- XXX we are not allowerd by low to collect ethnicity -- krat_poduz (REF poduz) as B_address, tel_poduz as B_phone, -- fax_poduz as ??, @@ -170,6 +226,17 @@ select from clost }; +my $sth_syspref = $k->prepare(qq{ update systempreferences set value=? where variable=? }); +my $BorrowersTitles = join('|', @{ $k->selectcol_arrayref(qq{ select distinct title from borrowers where title != '' }) }); +$sth_syspref->execute( $BorrowersTitles => 'BorrowersTitles' ); +warn "# BorrowersTitles [$BorrowersTitles]"; + +} # import->{borrowers} + + +if ( $import->{issues} ) { + +$k->do(qq{ update items set barcode=substr(barcode,1,12)*1 where length(barcode) = 13 }); # convert barcodes back for lookups to work sub issues { my ($table,$sql) = @_; @@ -181,7 +248,19 @@ sub issues { while (my $row = $sth->fetchrow_hashref ) { $row->{borrowernumber} = lookup($k, 'borrowers', 'cardnumber' => 'borrowernumber', $row->{borrowernumber}); $row->{itemnumber} = lookup($k, 'items', 'barcode' => 'itemnumber', $row->{itemnumber}); + + my $stat = { + datetime => $row->{returndate} || $row->{issuedate}, + branch => $row->{branchcode}, + value => 0, + type => $row->{returndate} ? 'return' : 'issue', + }; + $stat->{$_} = $row->{$_} foreach (qw( itemnumber borrowernumber )); + + $row->{date_due} ||= '31.12.2011'; insert $table => $row; + + insert statistics => $stat; } } @@ -197,7 +276,142 @@ select from posud }; +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 == ''}; +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_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} ) { + +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) = @_; + my $sth = $f->prepare($sql); + $sth->execute; + + $insert = undef; + + while (my $row = $sth->fetchrow_hashref ) { + + $row->{found} = + $row->{found} ? 'F' : + $row->{itemnumber} ? 'W' : undef; + + $row->{borrowernumber} = lookup($k, 'borrowers', 'cardnumber' => 'borrowernumber', $row->{borrowernumber}); + $row->{biblionumber} = lookup($k, 'biblioitems', 'collectionvolume' => 'biblionumber', $row->{biblionumber}); + $row->{itemnumber} = lookup($k, 'items', 'barcode' => 'itemnumber', $row->{itemnumber}); + + insert $table => $row; + } +} + +my $sql_reserves = qq{ +select + ozn_vrclan||ozn_clan as borrowernumber, + datum_rezerv as reservedate, + sif_naslov as biblionumber, + 'a' as constrainttype, + 'SRE' as branchcode, + datum_obavijest as notificationdate, + datum_ponist as cancellationdate, + 2 - prior_rezerv as priority, + datum_posud as found, -- F|W + datum_rezerv||' '||vrijeme_rezerv as timestamp, + sif_primj as itemnumber, + datum_obavijest as waitingdate, + vrijedi_do as expirationdate, + 0 as lowestPriority +from rezerv +}; + +reserves 'old_reserves' => qq{$sql_reserves where datum_opoziv != '' or datum_ponist != '' or datum_posud != ''}; +reserves 'reserves' => qq{$sql_reserves where datum_opoziv == '' and datum_ponist == '' and datum_posud == ''}; + +} # $import->{reserves} + + +if ( $import->{barcode} ) { # XXX must be last! + +my $sth = $k->prepare(qq{ select itemnumber,barcode from items where length(barcode) < 13 }); +my $sth_update = $k->prepare(qq{ update items set barcode=? where itemnumber=? }); + +$sth->execute; +warn "update ", $sth->rows, " barcodes"; + +my $ean = CheckDigits('ean'); + +while( my $row = $sth->fetchrow_hashref ) { + $sth_update->execute( $ean->complete( sprintf('%012d', $row->{barcode}) ), $row->{itemnumber} ); +} + +} # 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 +}; + $k->commit;