convert casopisi

diff --git a/casopisi2koha.pl b/casopisi2koha.pl
new file mode 100755 (executable)
index 0000000..3cf5e46
--- /dev/null
+++ b/casopisi2koha.pl
@@ -0,0 +1,180 @@
+#!/usr/bin/perl -w
+
+use strict;
+use DBI;
+use Data::Dump qw/dump/;
+use Algorithm::CheckDigits;
+use Text::Unaccent;
+
+# sudo apt-get install libdbd-sqlite3-perl libdbd-csv-perl libtext-unaccent-perl
+
+$|++;
+
+my $f = DBI->connect("dbi:SQLite:dbname=ferlib-2011-06-27.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 ( $dbh, $t, $k, $v, $s, $default ) = @_;
+       my $hash;
+       my $key = "$t $k $v";
+       my $max = $v =~ s/max\((\w+)\)/$1/;
+       if ( exists $lookup->{$key} ) {
+               $hash = $lookup->{$key};
+       } else {
+               warn "# select $k,$v from $t";
+               my $sth = $dbh->prepare( "select $k,$v from $t" );
+               $sth->execute;
+               my @non_unique;
+               while (my $row = $sth->fetchrow_hashref() ) {
+                       if ( ! $max && exists $hash->{ $row->{$k} } ) {
+                               push @non_unique, $row->{$k};
+                               next;
+                       }
+                       $hash->{ $row->{$k} } = $row->{$v};
+               }
+               if ( @non_unique ) {
+                       warn "# remove non-unique ", dump(@non_unique);
+                       delete $hash->{$_} foreach @non_unique;
+               }
+               $lookup->{$key} = $hash;
+               warn "# lookup $key = ",dump($hash);
+       }
+#      return unless length $s > 1;
+       if ( ! exists $lookup->{$key}->{$s} ) {
+               warn "ERROR: no $s in $key\n";
+               return $default;
+       } else {
+               $lookup->{$key}->{$s};
+       }
+}
+
+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/;
+               $row->{$c} = undef if $row->{$c} eq ''; # XXX NULL dates istead of 0000-00-00
+       }
+
+       $insert->{$table}->execute( values %$row );
+       warn "# inserted $table ", dump $row;
+
+}
+
+sub sql {
+       my ($db,$sql) = @_;
+       foreach (split(/;/,$sql)) {
+               next if m/^\s*$/;
+               warn "# sql $_";
+               $db->do($_);
+       }
+}
+
+
+my $sif2orgjed = {
+       56 => 'SRE',
+       1  => 'ZPF',
+       2  => 'ZPM',
+       3  => 'ZOEM',
+       4  => 'ZESA',
+       5  => 'ZPR',
+       6  => 'ZVNE',
+       7  => 'ZTEL',
+       8  => 'ZESOI',
+       9  => 'ZARI',
+       10  => 'ZEA',
+       11  => 'ZEMRIS',
+       12  => 'ZRK',
+       15  => 'CIP'
+};
+
+sql $k => qq{
+delete from subscription;
+alter table subscription auto_increment = 1;
+delete from subscriptionhistory;
+alter table subscriptionhistory auto_increment = 1;
+delete from serial;
+alter table serial auto_increment = 1;
+};
+
+my $sql = qq{
+select distinct
+  broj.sif_casop as biblionumber,
+  posj_br.sif_orgjed as branchcode,
+
+  'v.' || broj.vol_broj || ':no.' || broj.broj as serialseq,
+  broj.god_izd || '-' || broj.mjes_izd || '-' || broj.dan_izd as planneddate,
+  primj_bruk.dat_prim as publisheddate,
+  'ND:' || primj_bruk.ozn_nacdob || ' INV:' || primj.inv_broj || ' ; napomena: ' || broj.koment_broj || ' ; ' || primj_bruk.komentar || ' ; ' || primj.komentar as notes
+
+from broj
+left join posj_br on posj_br.sif_broj=broj.sif_broj
+left join primj_bruk on primj_bruk.sif_broj=broj.sif_broj and  primj_bruk.sif_orgjed=branchcode
+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
+};
+my $sth = $f->prepare($sql);
+$sth->execute;
+
+my $biblionumber_branchcode;
+
+while (my $row = $sth->fetchrow_hashref ) {
+
+       warn "## row = ",dump $row;
+       my $subscriptionid;
+
+       $row->{biblionumber} = lookup($k, qq|biblioitems where itemtype='C'|, 'collectionvolume' => 'biblionumber', $row->{biblionumber}) || die "no biblionumber";
+       $row->{branchcode} = $row->{branchcode} ? $sif2orgjed->{ $row->{branchcode} } : 'SRE';
+
+       my $bn_bc_key = $row->{biblionumber} . ' ' . $row->{branchcode};
+
+       if ( $subscriptionid = $biblionumber_branchcode->{ $bn_bc_key } ) {
+               warn "existing subscription $subscriptionid";
+       } else {
+
+               insert 'subscription' => {
+                       biblionumber => $row->{biblionumber},
+                       branchcode   => $row->{branchcode},
+               };
+
+               $biblionumber_branchcode->{ $bn_bc_key } = $subscriptionid = $k->{mysql_insertid};
+
+               insert 'subscriptionhistory' => {
+                       biblionumber =>   $row->{biblionumber},
+                       subscriptionid => $subscriptionid,
+               };
+
+       }
+
+       $row->{planneddate} =~ s/-$/-01/;
+       $row->{planneddate} =~ s/--/-01-/;
+       $row->{publisheddate} =~ s/(\d+)\.(\d+)\.(\d\d\d\d)/$3-$2-$1/;
+
+       $row->{notes} =~ s/ ; $//;
+       $row->{notes} =~ s/:  ; /: /;
+       $row->{notes} =~ s/ ; napomena: $//;
+
+       insert 'serial' => {
+               biblionumber =>   $row->{biblionumber},
+               subscriptionid => $subscriptionid,
+               status => 1,
+               serialseq => $row->{serialseq},
+               planneddate => $row->{planneddate},
+               publisheddate => $row->{publisheddate},
+               notes => $row->{notes},
+       };
+
+}
+
+$k->commit;
+