X-Git-Url: http://git.rot13.org/?a=blobdiff_plain;f=ferlib2koha.pl;h=a3abc3b77d4ccb14109377b9a93fdeb79df30408;hb=b383cf4a1638464b6c95968d2aabcb825e0a5d55;hp=2d06b10037bb29cf89798a98f92ee3bbb6c722e1;hpb=29bdd55935134f9b6313bc26ca47aeb12de2b8eb;p=ferlib2koha.git diff --git a/ferlib2koha.pl b/ferlib2koha.pl index 2d06b10..a3abc3b 100755 --- a/ferlib2koha.pl +++ b/ferlib2koha.pl @@ -3,45 +3,125 @@ use strict; use DBI; use Data::Dump qw/dump/; +use Algorithm::CheckDigits; $|++; +my $import = { + borrowers => 0, + issues => 0, + barcode => 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 { - my ($t, $k, $v, $s ) = @_; + my ( $dbh, $t, $k, $v, $s, $default ) = @_; my $hash; my $key = "$t $k $v"; if ( exists $lookup->{$key} ) { $hash = $lookup->{$key}; } else { warn "# select $k,$v from $t"; - my $sth = $f->prepare( "select $k,$v from $t" ); + my $sth = $dbh->prepare( "select $k,$v from $t" ); $sth->execute; while (my $row = $sth->fetchrow_hashref() ) { $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} ) { warn "ERROR: no $s in $key\n"; - return $s; + return $default; } else { $lookup->{$key}->{$s}; } } -# select all posts which have been read or unread -my $sql = qq{ +our $insert; +sub insert { + my ( $table, $row ) = @_; + $insert->{$table} ||= $k->prepare( + "insert into $table (" + . join(',', keys %$row) + . ") values (" + . join(',', map { '?' } keys %$row ) + . ")" + ); + + foreach my $c ( grep { /(date|timestamp)/ } keys %$row ) { + $row->{$c} =~ s/^(\d\d)\.(\d\d)\.(\d\d\d\d)/$3-$2-$1/; + } + + $insert->{$table}->execute( values %$row ); + warn "# inserted $table ", dump $row; + +} + +sub borrowers { + my $sql = shift; + my $sth = $f->prepare($sql); + $sth->execute; + + $insert = undef; + + while (my $row = $sth->fetchrow_hashref ) { + + warn dump $row; + + # poor man's (sqlite) outer join + $row->{'country'} = lookup($f, 'drzava', 'ozn_drzava', 'naz_drzava', $row->{'country'}, $row->{contry} ); + $row->{'city'} = lookup($f, 'mjesto', 'post_ozn', 'naz_mjesto', $row->{'city'}, $row->{city} ); + + $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->{'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; + + insert 'borrower_attributes' => { + borrowernumber => $k->{mysql_insertid}, + code => 'JMBG', + attribute => $jmbg, + } if $jmbg; + + } +} + +if ( $import->{borrowers} ) { + +$k->do(qq{ delete from borrowers where borrowernumber > 57 }); +$k->do(qq{ delete from borrower_attributes where borrowernumber > 57 }); + +borrowers qq{ select 'S' as categorycode, 'SRE' as branchcode, - mbr_stud as cardnumber, + 'S' || mbr_stud as cardnumber, prez_stud as surname, ime_stud as firstname, djevprezime as othernames, @@ -58,19 +138,20 @@ select post_ozn_stan as B_city, adr_stan as B_address, tel_stud as phone, - dat_prava_do as dateexpiry + dat_prava_do as dateexpiry, -- aktivan as ??, + jmbg_stud as jmbg from studk }; -$sql = qq{ +borrowers qq{ select 'D' as categorycode, 'SRE' as branchcode, - sif_djel as cardnumber, + '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, @@ -81,19 +162,20 @@ select post_ozn as zipcode, post_ozn as city, adr_stan as address, - tel_stan as phone + tel_stan as phone, + jmbg_djel as jmbg from djelat }; -$sql = qq{ +borrowers qq{ select 'O' as categorycode, 'SRE' as branchcode, '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 ??, @@ -101,50 +183,129 @@ select post_ozn as zipcode, post_ozn as city, adr_stan as address, - tel_stan as phone + tel_stan as phone, -- aktivan as ??, + jmbg_clan as jmbg from clost }; -my $sth = $f->prepare($sql); -$sth->execute; +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]"; -our $insert; -sub insert { - my ( $table, $row ) = @_; - $insert->{$table} ||= $k->prepare( - "insert into $table (" - . join(',', keys %$row) - . ") values (" - . join(',', map { '?' } keys %$row ) - . ")" - ); - $insert->{$table}->execute( values %$row ); - warn "# inserted ", dump $row; +} # import->{borrowers} + +sub issues { + my ($table,$sql) = @_; + my $sth = $f->prepare($sql); + $sth->execute; + $insert = undef; + + while (my $row = $sth->fetchrow_hashref ) { + $row->{borrowernumber} = lookup($k, 'borrowers', 'cardnumber' => 'borrowernumber', $row->{borrowernumber}); + $row->{itemnumber} = lookup($k, 'items', 'barcode' => 'itemnumber', $row->{itemnumber}); + $row->{returndate} = '31.12.2011'; + insert $table => $row; + } } -while (my $row = $sth->fetchrow_hashref ) { +my $posud_sql = qq{ +select + ozn_vrclan||ozn_clan as borrowernumber, + sif_primj as itemnumber, + datum_do as date_due, + 'SRE' as branchcode, -- FIXME + datum_vra as returndate, + datum_pos as issuedate, + datum_pos||' '||vrijeme_pos as timestamp +from posud +}; + +if ( $import->{issues} ) { + +$k->do('delete from old_issues'); +$k->do('delete from issues'); +issues 'old_issues' => qq{$posud_sql where datum_vra != ''}; +issues 'issues' => qq{$posud_sql where datum_vra == ''}; + +$k->do(qq{ +update items +join issues on items.itemnumber=issues.itemnumber +set onloan = date_due, datelastborrowed = issuedate +}); - warn dump $row; - # poor man's (sqlite) outer join - $row->{'country'} = lookup('drzava', 'ozn_drzava', 'naz_drzava', $row->{'country'} ); - $row->{'city'} = lookup('mjesto', 'post_ozn', 'naz_mjesto', $row->{'city'} ); +}; # import->{issues} - $row->{'B_country'} = lookup('drzava', 'ozn_drzava', 'naz_drzava', $row->{'B_country'} ); - $row->{'B_city'} = lookup('mjesto', 'post_ozn', 'naz_mjesto', $row->{'B_city'} ); - foreach my $c ( grep { /date/ } keys %$row ) { - $row->{$c} =~ s/(\d\d)\.(\d\d)\.(\d\d\d\d)/$3-$2-$1/; + +$k->do(qq{ delete from reserves where borrowernumber > 100 }); +$k->do(qq{ delete from old_reserves where borrowernumber > 100 }); + +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, + 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 == ''}; + + + +if ( $import->{barcode} ) { # XXX must be last! - $row->{sex} =~ s/\x{17D}/F/; +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=? }); - $row->{city} ||= '?'; # not null +$sth->execute; +warn "update ", $sth->rows, " barcodes"; - insert 'borrowers' => $row; +my $ean = CheckDigits('ean'); +while( my $row = $sth->fetchrow_hashref ) { + $sth_update->execute( $ean->complete( sprintf('%012d', $row->{barcode}) ), $row->{itemnumber} ); } +} # import->{barcode} + + + + $k->commit;