5 use Data::Dump qw/dump/;
6 use Algorithm::CheckDigits;
9 # sudo apt-get install libdbd-sqlite3-perl libdbd-csv-perl libtext-unaccent-perl
14 borrowers => $ENV{FULL} || 0,
15 issues => $ENV{FULL} || 0,
16 reserves => $ENV{FULL} || 0,
17 barcode => $ENV{FULL} || 0,
20 my $f = DBI->connect("dbi:SQLite:dbname=knjiznica.sqlite","","", { RaiseError => 1, AutoCommit => 0 }) || die $DBI::errstr;
21 $f->{sqlite_unicode} = 1;
22 my $k = DBI->connect("dbi:mysql:database=koha_fer","","", { RaiseError => 1, AutoCommit => 0, mysql_enable_utf8 => 1 }) || die $DBI::errstr;
23 my $u = DBI->connect("dbi:CSV:","","", { RaiseError => 1, f_ext => '.csv', csv_sep_char => '|' }) || die $DBI::errstr;
27 my ( $dbh, $t, $k, $v, $s, $default ) = @_;
30 my $max = $v =~ s/max\((\w+)\)/$1/;
31 if ( exists $lookup->{$key} ) {
32 $hash = $lookup->{$key};
34 warn "# select $k,$v from $t";
35 my $sth = $dbh->prepare( "select $k,$v from $t" );
38 while (my $row = $sth->fetchrow_hashref() ) {
39 if ( ! $max && exists $hash->{ $row->{$k} } ) {
40 push @non_unique, $row->{$k};
43 $hash->{ $row->{$k} } = $row->{$v};
46 warn "# remove non-unique ", dump(@non_unique);
47 delete $hash->{$_} foreach @non_unique;
49 $lookup->{$key} = $hash;
50 warn "# lookup $key = ",dump($hash);
52 return unless length $s > 1;
53 if ( ! exists $lookup->{$key}->{$s} ) {
54 warn "ERROR: no $s in $key\n";
57 $lookup->{$key}->{$s};
63 my ( $table, $row ) = @_;
64 $insert->{$table} ||= $k->prepare(
65 "insert into $table ("
66 . join(',', keys %$row)
68 . join(',', map { '?' } keys %$row )
72 foreach my $c ( grep { /(date|timestamp)/ } keys %$row ) {
73 $row->{$c} =~ s/^(\d\d)\.(\d\d)\.(\d\d\d\d)/$3-$2-$1/;
74 $row->{$c} = undef if $row->{$c} eq ''; # XXX NULL dates istead of 0000-00-00
77 $insert->{$table}->execute( values %$row );
78 warn "# inserted $table ", dump $row;
84 my $sth = $f->prepare($sql);
89 while (my $row = $sth->fetchrow_hashref ) {
93 # poor man's (sqlite) outer join
94 $row->{'country'} = lookup($f, 'drzava', 'ozn_drzava', 'naz_drzava', $row->{'country'}, $row->{contry} );
95 $row->{'city'} = lookup($f, 'mjesto', 'post_ozn', 'naz_mjesto', $row->{'city'}, $row->{city} );
97 $row->{'B_country'} = lookup($f, 'drzava', 'ozn_drzava', 'naz_drzava', $row->{'B_country'}, $row->{B_country} );
98 $row->{'B_city'} = lookup($f, 'mjesto', 'post_ozn', 'naz_mjesto', $row->{'B_city'}, $row->{B_city} );
100 $row->{'B_email'} = lookup($f, 'clanost', 'ozn_vrclan||ozn_clan', 'email', $row->{'cardnumber'}, undef);
101 $row->{'emailpro'} = lookup($f, 'clanost', 'ozn_vrclan||ozn_clan', 'email_k', $row->{'cardnumber'}, undef);
103 $row->{'email'} = lookup($u, 'users', 'jmbag', 'email', substr($row->{cardnumber},1), undef); # $row->{B_email} || $row->{emailpro} )
105 $row->{'userid'} = lookup($u, 'users', 'jmbag', 'nick', substr($row->{cardnumber},1), $row->{cardnumber});
107 $row->{sex} =~ s/\x{17D}/F/;
109 $row->{city} ||= '?'; # not null
111 $row->{borrowernotes} = lookup($f, 'clanorg', 'ozn_vrclan||ozn_clan', 'napom_clan', $row->{'cardnumber'}, undef);
113 $row->{title} = lookup($f, 'titula', 'ozn_titula', 'opis_titula', $row->{title}, $row->{title});
115 $row->{dateexpiry} ||= '01.01.2020'; # next revision
117 my $jmbg = delete $row->{jmbg};
119 insert 'borrowers' => $row;
121 my $borrowernumber = $k->{mysql_insertid};
123 insert 'borrower_attributes' => {
124 borrowernumber => $borrowernumber,
129 foreach my $id ( 6,1,4,5,2 ) {
131 insert 'borrower_message_preferences' => {
132 borrowernumber => $borrowernumber,
133 message_attribute_id => $id,
134 days_in_advance => $id == 2 ? 0 : undef,
137 insert 'borrower_message_transport_preferences' => {
138 borrower_message_preference_id => $k->{mysql_insertid},
139 message_transport_type => 'email',
149 foreach (split(/;/,$sql)) {
156 if ( $import->{borrowers} ) {
159 delete from borrowers;
160 alter table borrowers auto_increment = 1;
161 delete from borrower_attributes;
162 alter table borrower_attributes auto_increment = 1;
163 delete from borrower_message_preferences where borrowernumber is not null ;
164 alter table borrower_message_preferences auto_increment = 1;
172 'S' || mbr_stud as cardnumber,
173 prez_stud as surname,
174 ime_stud as firstname,
175 djevprezime as othernames,
177 dat_rodj as dateofbirth,
180 ozn_drzava_preb as country,
181 post_ozn_preb as zipcode,
182 post_ozn_preb as city,
184 ozn_drzava_stan as B_country,
185 post_ozn_stan as B_zipcode,
186 post_ozn_stan as B_city,
187 adr_stan as B_address,
189 dat_prava_do as dateexpiry,
199 'D' || sif_djel as cardnumber,
200 ime_djel as firstname,
201 prez_djel as surname,
203 -- sif_orgjed (REF orgjed) as B_address,
204 -- sif_strsp_djel as ??,
205 tel_djel as phonepro,
206 dat_prekid_ro as dateexpiry,
209 ozn_drzava as country,
222 'O' || mbr_clan as cardnumber,
223 ime_clan as firstname,
224 prez_clan as surname,
226 zvanje_clan as ethnicity, -- XXX we are not allowerd by low to collect ethnicity
227 -- krat_poduz (REF poduz) as B_address,
228 tel_poduz as B_phone,
230 ozn_drzava as country,
240 my $sth_syspref = $k->prepare(qq{ update systempreferences set value=? where variable=? });
241 my $BorrowersTitles = join('|', @{ $k->selectcol_arrayref(qq{ select distinct title from borrowers where title != '' }) });
242 $sth_syspref->execute( $BorrowersTitles => 'BorrowersTitles' );
243 warn "# BorrowersTitles [$BorrowersTitles]";
245 } # import->{borrowers}
248 if ( $import->{issues} ) {
250 $k->do(qq{ update items set barcode=substr(barcode,1,12)*1 where length(barcode) = 13 }); # convert barcodes back for lookups to work
253 my ($table,$sql) = @_;
254 my $sth = $f->prepare($sql);
259 while (my $row = $sth->fetchrow_hashref ) {
260 $row->{borrowernumber} = lookup($k, 'borrowers', 'cardnumber' => 'borrowernumber', $row->{borrowernumber});
261 $row->{itemnumber} = lookup($k, 'items', 'barcode' => 'itemnumber', $row->{itemnumber});
264 datetime => $row->{returndate} || $row->{issuedate},
265 branch => $row->{branchcode},
267 type => $row->{returndate} ? 'return' : 'issue',
269 $stat->{$_} = $row->{$_} foreach (qw( itemnumber borrowernumber ));
271 $row->{date_due} ||= '31.12.2011';
272 insert $table => $row;
274 insert statistics => $stat;
280 ozn_vrclan||ozn_clan as borrowernumber,
281 sif_primj as itemnumber,
282 datum_do as date_due,
283 'SRE' as branchcode, -- FIXME
284 datum_vra as returndate,
285 datum_pos as issuedate,
286 datum_pos||' '||vrijeme_pos as timestamp
291 delete from old_issues;
292 alter table old_issues auto_increment = 1;
294 alter table issues auto_increment = 1;
296 issues 'old_issues' => qq{$posud_sql where datum_vra != ''};
297 issues 'issues' => qq{$posud_sql where datum_vra == ''};
301 join issues on items.itemnumber=issues.itemnumber
302 set onloan = date_due, datelastborrowed = issuedate
304 update items set datelastseen = datelastborrowed
306 create temporary table old_returndate as
307 select itemnumber,max(returndate) as returndate
309 group by itemnumber,returndate
312 join old_returndate on items.itemnumber=old_returndate.itemnumber
313 set datelastseen = returndate
314 where returndate > datelastseen or datelastseen is null
316 create temporary table old_issuedate as
317 select itemnumber,max(issuedate) as issuedate
319 group by itemnumber,issuedate
322 join old_issuedate on items.itemnumber=old_issuedate.itemnumber
323 set datelastborrowed = issuedate
324 where datelastborrowed is null
326 update old_issues set timestamp = returndate
329 }; # import->{issues}
332 if ( $import->{reserves} ) {
335 delete from reserves;
336 alter table reserves auto_increment = 1;
337 delete from old_reserves;
338 alter table old_reserves auto_increment = 1;
342 my ($table, $sql) = @_;
343 my $sth = $f->prepare($sql);
348 while (my $row = $sth->fetchrow_hashref ) {
351 $row->{found} ? 'F' :
352 $row->{itemnumber} ? 'W' : undef;
354 $row->{borrowernumber} = lookup($k, 'borrowers', 'cardnumber' => 'borrowernumber', $row->{borrowernumber});
355 $row->{biblionumber} = lookup($k, 'biblioitems', 'collectionvolume' => 'max(biblionumber)', $row->{biblionumber});
356 $row->{itemnumber} = lookup($k, 'items', 'barcode' => 'itemnumber', $row->{itemnumber});
358 insert $table => $row;
362 my $sql_reserves = qq{
364 ozn_vrclan||ozn_clan as borrowernumber,
365 datum_rezerv as reservedate,
366 sif_naslov as biblionumber,
367 'a' as constrainttype,
369 datum_obavijest as notificationdate,
370 datum_ponist as cancellationdate,
371 2 - prior_rezerv as priority,
372 datum_posud as found, -- F|W
373 datum_rezerv||' '||vrijeme_rezerv as timestamp,
374 sif_primj as itemnumber,
375 datum_obavijest as waitingdate,
376 vrijedi_do as expirationdate,
381 reserves 'old_reserves' => qq{$sql_reserves where datum_opoziv != '' or datum_ponist != '' or datum_posud != ''};
382 reserves 'reserves' => qq{$sql_reserves where datum_opoziv == '' and datum_ponist == '' and datum_posud == ''};
384 } # $import->{reserves}
387 if ( $import->{barcode} ) { # XXX must be last!
389 my $sth = $k->prepare(qq{ select itemnumber,barcode from items where length(barcode) < 13 });
390 my $sth_update = $k->prepare(qq{ update items set barcode=? where itemnumber=? });
393 warn "update ", $sth->rows, " barcodes";
395 my $ean = CheckDigits('ean');
397 while( my $row = $sth->fetchrow_hashref ) {
398 $sth_update->execute( $ean->complete( sprintf('%012d', $row->{barcode}) ), $row->{itemnumber} );
401 } # import->{barcode}
406 my $sth = $f->prepare($sql);
411 while (my $row = $sth->fetchrow_hashref ) {
412 insert 'aqbooksellers' => $row;
417 delete from aqbooksellers;
418 alter table aqbooksellers auto_increment = 1;
428 my $sth = $k->prepare(qq{
435 where categorycode = 'D' and email is null
436 group by firstname,surname
437 having count(*) = 1 -- update only unique users
439 my $update_borrower = $k->prepare(qq{
441 set email = ?, userid = ?, cardnumber = ?
442 where borrowernumber = ?
445 while (my $row = $sth->fetchrow_hashref) {
446 my $email = lc unac_string( 'utf-8', $row->{firstname} . '.' . $row->{surname} . '@fer.hr' );
447 if ( my $nick = lookup($u, 'users', 'email', 'nick', $email, undef) ) {
448 my $cardnumber = lookup($u, 'users', 'email', 'jmbag', $email, undef) || next;
449 warn "# FIXED nick $row->{borrowernumber} $nick $email $cardnumber\n";
451 $update_borrower->execute( $email, $nick, $cardnumber, $row->{borrowernumber} );