X-Git-Url: http://git.rot13.org/?a=blobdiff_plain;f=ferlib2koha.pl;h=96434da64fbed17fb10349acf62d6761108e3b6f;hb=9093dc7f75fd859541745fff9324b64d800df9fa;hp=761a449c024bbaf6c7f3b983626ad5e3c7daf90a;hpb=383f472359d8d052a75dd39d9c0bede692c84949;p=ferlib2koha.git diff --git a/ferlib2koha.pl b/ferlib2koha.pl index 761a449..96434da 100755 --- a/ferlib2koha.pl +++ b/ferlib2koha.pl @@ -6,20 +6,25 @@ use Data::Dump qw/dump/; $|++; +my $import = { + borrowers => 1, + issues => 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; 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}; @@ -30,7 +35,7 @@ sub lookup { 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}; } @@ -46,6 +51,11 @@ sub insert { . 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 ", dump $row; @@ -63,28 +73,42 @@ sub borrowers { 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'} ); + $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('drzava', 'ozn_drzava', 'naz_drzava', $row->{'B_country'} ); - $row->{'B_city'} = lookup('mjesto', 'post_ozn', 'naz_mjesto', $row->{'B_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} ); - foreach my $c ( grep { /date/ } keys %$row ) { - $row->{$c} =~ s/(\d\d)\.(\d\d)\.(\d\d\d\d)/$3-$2-$1/; - } + $row->{'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->{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; + } } -$k->do(qq{ -delete from borrowers where borrowernumber > 57 -}); +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 @@ -107,8 +131,9 @@ 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 }; @@ -119,7 +144,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, @@ -130,7 +155,8 @@ 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 }; @@ -141,8 +167,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 ??, @@ -150,9 +176,52 @@ 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_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} + +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}); + insert $table => $row; + } +} + +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->{issuses} ) { + +$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 == ''}; + +}; # import->{issues} + $k->commit;