5 use Data::Dump qw/dump/;
6 use Algorithm::CheckDigits;
9 # sudo apt-get install libdbd-sqlite3-perl libdbd-csv-perl libtext-unaccent-perl
13 my $f = DBI->connect("dbi:SQLite:dbname=ferlib-2011-06-27.sqlite","","", { RaiseError => 1, AutoCommit => 0 }) || die $DBI::errstr;
14 $f->{sqlite_unicode} = 1;
15 my $k = DBI->connect("dbi:mysql:database=koha_fer","root","", { RaiseError => 1, AutoCommit => 0, mysql_enable_utf8 => 1 }) || die $DBI::errstr;
16 my $u = DBI->connect("dbi:CSV:","","", { RaiseError => 1, f_ext => '.csv', csv_sep_char => '|' }) || die $DBI::errstr;
20 my ( $dbh, $t, $k, $v, $s, $default ) = @_;
23 my $max = $v =~ s/max\((\w+)\)/$1/;
24 if ( exists $lookup->{$key} ) {
25 $hash = $lookup->{$key};
27 warn "# select $k,$v from $t";
28 my $sth = $dbh->prepare( "select $k,$v from $t" );
31 while (my $row = $sth->fetchrow_hashref() ) {
32 if ( ! $max && exists $hash->{ $row->{$k} } ) {
33 push @non_unique, $row->{$k};
36 $hash->{ $row->{$k} } = $row->{$v};
39 warn "# remove non-unique ", dump(@non_unique);
40 delete $hash->{$_} foreach @non_unique;
42 $lookup->{$key} = $hash;
43 warn "# lookup $key = ",dump($hash);
45 # return unless length $s > 1;
46 if ( ! exists $lookup->{$key}->{$s} ) {
47 warn "ERROR: no $s in $key\n";
50 $lookup->{$key}->{$s};
56 my ( $table, $row ) = @_;
57 $insert->{$table} ||= $k->prepare(
58 "insert into $table ("
59 . join(',', keys %$row)
61 . join(',', map { '?' } keys %$row )
65 foreach my $c ( grep { /(date|timestamp)/ } keys %$row ) {
66 $row->{$c} =~ s/^(\d\d)\.(\d\d)\.(\d\d\d\d)/$3-$2-$1/;
67 $row->{$c} = undef if $row->{$c} eq ''; # XXX NULL dates istead of 0000-00-00
70 $insert->{$table}->execute( values %$row );
71 warn "# inserted $table ", dump $row;
77 foreach (split(/;/,$sql)) {
103 delete from subscription;
104 alter table subscription auto_increment = 1;
105 delete from subscriptionhistory;
106 alter table subscriptionhistory auto_increment = 1;
108 alter table serial auto_increment = 1;
113 broj.sif_casop as biblionumber,
114 posj_br.sif_orgjed as branchcode,
116 'v.' || broj.vol_broj || ':no.' || broj.broj as serialseq,
117 broj.god_izd || '-' || broj.mjes_izd || '-' || broj.dan_izd as publisheddate,
118 primj_bruk.dat_prim as planneddate,
119 'ND:' || primj_bruk.ozn_nacdob || ' INV:' || primj.inv_broj || ' ; napomena: ' || broj.koment_broj || ' ; ' || primj_bruk.komentar || ' ; ' || primj.komentar as notes
122 left join posj_br on posj_br.sif_broj=broj.sif_broj
123 left join primj_bruk on primj_bruk.sif_broj=broj.sif_broj and primj_bruk.sif_orgjed=branchcode
124 left join primj on primj.sif_naslov=broj.sif_broj and primj.tip_naslov='B' and primj.sif_orgjed=branchcode and primj_bruk.dat_prim=primj.dat_nabava
126 my $sth = $f->prepare($sql);
129 my $biblionumber_branchcode;
131 while (my $row = $sth->fetchrow_hashref ) {
133 warn "## row = ",dump $row;
136 $row->{biblionumber} = lookup($k, qq|biblioitems where itemtype='C'|, 'collectionvolume' => 'biblionumber', $row->{biblionumber}) || die "no biblionumber";
137 $row->{branchcode} = $row->{branchcode} ? $sif2orgjed->{ $row->{branchcode} } : 'SRE';
139 my $bn_bc_key = $row->{biblionumber} . ' ' . $row->{branchcode};
141 if ( $subscriptionid = $biblionumber_branchcode->{ $bn_bc_key } ) {
142 warn "existing subscription $subscriptionid";
145 insert 'subscription' => {
146 biblionumber => $row->{biblionumber},
147 branchcode => $row->{branchcode},
150 $biblionumber_branchcode->{ $bn_bc_key } = $subscriptionid = $k->{mysql_insertid};
152 insert 'subscriptionhistory' => {
153 biblionumber => $row->{biblionumber},
154 subscriptionid => $subscriptionid,
159 $row->{publisheddate} =~ s/-$/-01/;
160 $row->{publisheddate} =~ s/--/-01-/;
161 $row->{plannedddate} =~ s/(\d+)\.(\d+)\.(\d\d\d\d)/$3-$2-$1/;
163 $row->{notes} =~ s/ ; $//;
164 $row->{notes} =~ s/: ; /: /;
165 $row->{notes} =~ s/ ; napomena: $//;
168 biblionumber => $row->{biblionumber},
169 subscriptionid => $subscriptionid,
171 serialseq => $row->{serialseq},
172 planneddate => $row->{planneddate},
173 publisheddate => $row->{publisheddate},
174 notes => $row->{notes},