viewvc.cgi/registar/convert.pl

#! /usr/local/bin/perl -w
use strict;
$| = 1;

my $debug = 0;
my $raise = 0;
my $log = "log";
my $xbase = "./registar";
my $db = "registar";
my $refresh_timeout = 10;

open(LOG, ">$log") || die "can't open log: $!";

use DBI;
my $xbdbh = DBI->connect("DBI:XBase:$xbase", "", "",
        { 'RaiseError' => $raise }) || die $DBI::errstr;
my $pgdbh = DBI->connect("DBI:Pg:dbname=$db", "dpavlin", "",
        { 'RaiseError' => $raise }) || die $DBI::errstr;
$SIG{'__WARN__'} = sub { };     # inhibit DBI warnings.

my @tables = ();
if (@ARGV > 0) {
        push(@tables, @ARGV);
} else {
        push(@tables, <$xbase/*.dbf>);
        # Sto da radim sa ovima?
        #push(@tables, <$xbase/*.slk>);
        #push(@tables, <$xbase/*.sylk>);
        # OLD GLOBBING METHOD
        # opendir(DIR, $xbase) || die "can't open dir $xbase";
        # @tables = grep { /\.dbf/i || /\.slk/i || /\.sylk/i } readdir(DIR);
        # closedir(DIR);
}

sub ConvertDate {
        my ($dt) = @_;
        if ($dt =~ m,^([12][09]\d\d)([0-1]\d)([0-3]\d)$,) {
                $dt = ($2 < 1 || $2 > 12 || $1 < 1900 || $3 > 31) ? undef : "$1-$2-$3";
        } elsif ($dt =~ m,^([12][09]\d\d)([0-1]\d)$,) {
                $dt = ($2 >= 1 && $2 <= 12) ? "$1-$2-01" : "$1-01-01";
        } elsif ($dt =~ m,^([12][09]\d\d)$,) {
                $dt = "$1-01-01";
        } else {
                print STDERR "datum? ($dt) [nuke]\n";
                # $dt = "NULL";
                $dt = undef;
        }
        return $dt;
}

sub ConvertBool {
        my ($b) = @_;
        $b ||= 0;
        $b = ($b != 0) ? "true" : "false";
        return $b;
}

sub ConvertString {
        my $str = join("\n", @_);
        $str =~ s/[\x00-\x1F]+//gs;
        $str =~ s/\s*\x8D?\s*\r?\s*\n\s*/\n/gs;
        $str =~ s/(^\s+|\s+$)//gs;
        $str =~ s/(^["']|["']$)//g;
        # char recoding
        #$str =~ s/&#273;/ð/g;
        #$str =~ s/&#269;/è/g;
        #$str =~ s/ý/¾/g;
        $str =~ tr/{|`~}[\\@^]/¹ð¾èæ©Ð®ÈÆ/;
#       return $pgdbh->quote($str);
        return $str;
}

sub Progress {
        my ($count, $total) = @_;
        return sprintf("%6.2f%%", ($count * 100 / scalar($total)))." done.\r";
}

sub Log {
        my $msg = join("\n", @_);
        print LOG "$msg\n";
        return "$msg\n";
}

sub CondInsert {
        my $table = shift @_;
        my $name = shift @_;
        my $key = shift @_;
        my $fields = shift @_;
        my @values=@_;          # chop argument(s) are values array
        my $sql = "SELECT count($name) FROM $table WHERE ($name = ?)";
        my $sth = $pgdbh->prepare($sql);
        my $ret = $sth->execute($key);
        my ($cnt) = $sth->fetchrow_array();
        return 0 if (!$cnt == 0);
        my @valtpl;
        for (my $i=0; $i<=$#values; $i++) { push @valtpl,"?" }
        $sql = "INSERT INTO $table $fields VALUES (".join(",",@valtpl).")";
        $sth = $pgdbh->prepare($sql);
        $ret = $sth->execute(@values);
        my $err = $pgdbh->errstr;
        print &Log("[$sql] [".join(",",@values)."]".$err) if ($err);
        return $ret;
}

sub tblATK {
        my ($table, $fields, $data) = @_;
        my $count = 0;
        my $sth = $pgdbh->prepare("INSERT INTO $table (sifra, naziv, razno) VALUES (?, ?, ?)");
        unless ($sth) {
                print &Log("[$table-prepare] ".$pgdbh->errstr);
                return 0;
        }
        my $refresh = 0;
        foreach my $row (@$data) {
                my ($sifra, $naziv, $razno) = @$row;
                $sifra = &ConvertString($sifra);
                $naziv = &ConvertString($naziv);
                $razno = &ConvertString($razno);
                my $tmp = $sth->execute($sifra, $naziv, $razno);
                unless (defined($tmp)) {
                        print &Log("[$table-execute] ".$pgdbh->errstr);
                        next;
                }
                $tmp ||= 0;
                $tmp = 0 if ($tmp < 0);
                $count += $tmp;
                if (++$refresh >= $refresh_timeout) {
                        $refresh = 0;
                        print Progress($count, scalar(@$data));
                }
        }
        $sth->finish();
        return $count;
}

sub tblDFZupa {
        my ($table, $fields, $data) = @_;
        my $count = 0;
        my $sth = $pgdbh->prepare("INSERT INTO zupanije (broj, naziv, centar, pos_broj, npos_broj, spos_broj, grad, zip) VALUES (?, ?, ?, ?, ?, ?, ?, ?)");
        unless ($sth) {
                print &Log("[$table-prepare] ".$pgdbh->errstr);
                return 0;
        }
        my $refresh = 0;
        foreach my $row (@$data) {
                my ($broj, $naziv, $centar, $pos_broj, $npos_broj,
                        $spos_broj, $grad, $zip, $grad_i) = @$row;
                $broj += 0;     # force numeric
                $naziv = &ConvertString($naziv);
                $centar = &ConvertString($centar);
                $pos_broj = &ConvertString($pos_broj);
                $npos_broj = &ConvertString($npos_broj);
                $spos_broj = &ConvertString($spos_broj);
                $grad = &ConvertString($grad);
                $zip = &ConvertString($zip);
                my $tmp = $sth->execute($broj, $naziv, $centar,
                        $pos_broj, $npos_broj, $spos_broj,
                        $grad, $zip);
                unless (defined($tmp)) {
                        print &Log("[$table-execute] ".$pgdbh->errstr);
                        next;
                }
                $tmp = 0 if ($tmp < 0);
                $count += $tmp;
                if (++$refresh >= $refresh_timeout) {
                        $refresh = 0;
                        print Progress($count, scalar(@$data));
                }
        }
        $sth->finish();
        return $count;
}

sub tblINN {
        return &tblATK(@_);
}

sub tblLijek {
        my ($table, $fields, $data) = @_;
        my $count = 0;
        my $sth = $pgdbh->prepare("INSERT INTO lijekovi (sifra, naziv, sif_atk, sif_inn, sif_proizvodjac, pakovanje, sastav_1, sastav_2, sif_nacin_izdavanja, sif_list_fond, sif_trudnoca, sif_dojencad, sif_vozac, sif_droga, cijena, sif_med, razno, glav_razno, djelovanje, indikacije, oprez) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
        unless ($sth) {
                print &Log("[$table-prepare] ".$pgdbh->errstr);
                return 0;
        }
        my $refresh = 0;
        foreach my $row (@$data) {
                my ($sifra, $nsifra, $naziv, $sif_proizvodjac, $naz_proizvodjac,
                        $drzava, $zastupnistvo, $mjesto, $ulica_i_broj, $sif_inn,
                        $naz_inn, $sif_atk0, $naz_atk0, $sif_atk, $naz_atk,
                        $pakovanje, $sastav_1, $sastav_2, $sif_nacin_izdavanja,
                        $naz_nacin_izdavanja, $sif_list_fond, $naz_list_fond,
                        $sif_trudnoca, $naz_trudnoca, $sif_dojencad, $naz_dojencad,
                        $sif_vozac, $naz_vozac, $sif_droga, $cijena, $razno,
                        $sif_med, $glav_razno, $prazno, $djelovanje, $indikacije,
                        $oprez, $tel, $fax) = @$row;
                $sifra += 0;    # force numeric
                $naziv = &ConvertString($naziv);
                $sif_proizvodjac += 0;  # force numeric
                $naz_proizvodjac = &ConvertString($naz_proizvodjac);
                $drzava = &ConvertString($drzava);
                $zastupnistvo = &ConvertString($zastupnistvo);
                $mjesto = &ConvertString($mjesto);
                $ulica_i_broj = &ConvertString($ulica_i_broj);
                $sif_inn = &ConvertString($sif_inn);
                $naz_inn = &ConvertString($naz_inn);
                $sif_atk = &ConvertString($sif_atk);
                $naz_atk = &ConvertString($naz_atk);
                $pakovanje = &ConvertString($pakovanje);
                $sastav_1 = &ConvertString($sastav_1);
                $sastav_2 = &ConvertString($sastav_2);
                $sif_nacin_izdavanja = &ConvertString($sif_nacin_izdavanja);
                $naz_nacin_izdavanja = &ConvertString($naz_nacin_izdavanja);
                $sif_list_fond = &ConvertString($sif_list_fond);
                $naz_list_fond = &ConvertString($naz_list_fond);
                $sif_trudnoca = &ConvertString($sif_trudnoca);
                $naz_trudnoca = &ConvertString($naz_trudnoca);
                $sif_dojencad = &ConvertString($sif_dojencad);
                $naz_dojencad = &ConvertString($naz_dojencad);
                $sif_vozac = &ConvertString($sif_vozac);
                $naz_vozac = &ConvertString($naz_vozac);
                $sif_droga = &ConvertString($sif_droga);
                $cijena = &ConvertString($cijena);
                $razno = &ConvertString($razno);
                $sif_med += 0;  # force numeric
                $glav_razno = &ConvertString($glav_razno);
                $djelovanje = &ConvertString($djelovanje);
                $indikacije = &ConvertString($indikacije);
                $oprez = &ConvertString($oprez);
                $tel = &ConvertString($tel);
                $fax = &ConvertString($fax);
                CondInsert("atk", "sifra", $sif_atk,
                        "(sifra, naziv, razno)", ($sif_atk, $naz_atk, ''));
                CondInsert("inn", "sifra", $sif_inn,
                        "(sifra, naziv, razno)", ($sif_inn, $naz_inn, ''));
                CondInsert("proizvodjaci", "sifra", $sif_proizvodjac,
                        "(sifra, naziv, drzava, zastupnistvo, mjesto, zip, ulica_i_broj, razno, telefon, fax)",
                        ($sif_proizvodjac, $naz_proizvodjac, $drzava, $zastupnistvo, $mjesto, '', $ulica_i_broj, '', $tel, $fax));
                CondInsert("nacin_izdavanja", "sifra", $sif_nacin_izdavanja,
                        "(sifra, opis)", ($sif_nacin_izdavanja, $naz_nacin_izdavanja));
                CondInsert("list_fond", "sifra", $sif_list_fond,
                        "(sifra, opis)", ($sif_list_fond, $naz_list_fond));
                CondInsert("trudnoca", "sifra", $sif_trudnoca,
                        "(sifra, opis)", ($sif_trudnoca, $naz_trudnoca));
                CondInsert("dojencad", "sifra", $sif_dojencad,
                        "(sifra, opis)", ($sif_dojencad, $naz_dojencad));
                CondInsert("vozac", "sifra", $sif_vozac,
                        "(sifra, opis)", ($sif_vozac, $naz_vozac));
                CondInsert("droga", "sifra", $sif_droga,
                        "(sifra, opis)", ($sif_droga, ''));
                my $tmp = $sth->execute($sifra, $naziv, $sif_atk, $sif_inn,
                        $sif_proizvodjac, $pakovanje, $sastav_1, $sastav_2,
                        $sif_nacin_izdavanja, $sif_list_fond, $sif_trudnoca,
                        $sif_dojencad, $sif_vozac, $sif_droga, $cijena,
                        $sif_med, $razno, $glav_razno, $djelovanje,
                        $indikacije, $oprez);
                unless (defined($tmp)) {
                        print &Log("[$table-execute] ".$pgdbh->errstr);
                        next;
                }
                $tmp = 0 if ($tmp < 0);
                $count += $tmp;
                if (++$refresh >= $refresh_timeout) {
                        $refresh = 0;
                        print Progress($count, scalar(@$data));
                }
        }
        $sth->finish();
        return $count;
}

sub tblProiz {
        my ($table, $fields, $data) = @_;
        my $count = 0;
        my $sth = $pgdbh->prepare("INSERT INTO proizvodjaci (sifra, naziv, drzava, zastupnistvo, mjesto, zip, ulica_i_broj, razno, telefon, fax) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
        unless ($sth) {
                print &Log("[$table-prepare] ".$pgdbh->errstr);
                return 0;
        }
        my $refresh = 0;
        foreach my $row (@$data) {
                my ($sifra, $naziv, $drzava, $zastupnistvo, $zip, $mjesto,
                        $ulica_i_broj, $razno, $tel, $fax) = @$row;
                $sifra += 0;    # force numeric
                $naziv = &ConvertString($naziv);
                $drzava = &ConvertString($drzava);
                $zastupnistvo = &ConvertString($zastupnistvo);
                $zip = &ConvertString($zip);
                $mjesto = &ConvertString($mjesto);
                $ulica_i_broj = &ConvertString($ulica_i_broj);
                $razno = &ConvertString($razno);
                $tel = &ConvertString($tel);
                $fax = &ConvertString($fax);
                my $tmp = $sth->execute($sifra, $naziv, $drzava,
                        $zastupnistvo, $mjesto, $zip, $ulica_i_broj,
                        $razno, $tel, $fax);
                unless (defined($tmp)) {
                        print &Log("[$table-execute] ".$pgdbh->errstr);
                        next;
                }
                $tmp = 0 if ($tmp < 0);
                $count += $tmp;
                if (++$refresh >= $refresh_timeout) {
                        $refresh = 0;
                        print Progress($count, scalar(@$data));
                }
        }
        $sth->finish();
        return $count;
}

sub tblVodeca {
        my ($table, $fields, $data) = @_;
        my $count = 0;
        my $refresh = 0;
        foreach my $row (@$data) {
                my ($sifra, $naziv) = @$row;
                $sifra =~ s/^(..)//;
                my $code = $1;
                $sifra = &ConvertString($sifra);
                $naziv = &ConvertString($naziv);
                my $tmp = 0;
                if ($code eq "06") {
                        $tmp += CondInsert("nacin_izdavanja", "sifra", $sifra,
                                "(sifra, opis)", ($sifra, $naziv));
                } elsif ($code eq "07") {
                        $tmp += CondInsert("list_fond", "sifra", $sifra,
                                "(sifra, opis)", ($sifra, $naziv));
                } elsif ($code eq "08") {
                        $tmp += CondInsert("trudnoca", "sifra", $sifra,
                                "(sifra, opis)", ($sifra, $naziv));
                } elsif ($code eq "09") {
                        $tmp += CondInsert("dojencad", "sifra", $sifra,
                                "(sifra, opis)", ($sifra, $naziv));
                } elsif ($code eq "10") {
                        $tmp += CondInsert("vozac", "sifra", $sifra,
                                "(sifra, opis)", ($sifra, $naziv));
                } elsif ($code eq "11") {
                        $tmp += CondInsert("droga", "sifra", $sifra,
                                "(sifra, opis)", ($sifra, $naziv));
                } else {
                        # UNKNOWN CODE!!!
                        print &Log("[WARNING: unknown code \"$code\"]");
                }
                $tmp = 0 if ($tmp < 0);
                $count += $tmp;
                if (++$refresh >= $refresh_timeout) {
                        $refresh = 0;
                        print Progress($count, scalar(@$data));
                }
        }
        return $count;
}

my (%dbf) = (
        "atk" => \&tblATK,
        "atkstr" => undef,
        "boja" => undef,
        "dfzupa" => \&tblDFZupa,
        "inn" => \&tblINN,
        "innstr" => undef,
        "lijek" => \&tblLijek,
        "lijstr" => undef,
        "proiz" => \&tblProiz,
        "prostr" => undef,
        "registar" => undef,
        "regstr" => undef,
        "strukt" => undef,
        "vodeca" => \&tblVodeca
);

### MAIN
foreach my $table (@tables) {
        next unless ($table =~ s/\.dbf$//i);
        $table =~ s,^.+/([^/]+),$1,;
        unless (exists($dbf{$table})) {
                print "[WARNING: Don't know what to do with table \"$table\"!]\n\n";
                next;
        }
        unless (defined($dbf{$table})) {
                print "[WARNING: Ignoring table \"$table\"]\n\n";
                next;
        }
        print &Log("Working on \"$table\"...");
        my $sql = "SELECT * FROM $table";
        my $sth = $xbdbh->prepare($sql) || die "[$sql] ".$xbdbh->errstr();
        $sth->execute() || die "[$sql] ".$sth->errstr();
        my @names = @{$sth->{'NAME'}};
        my @types = @{$sth->{'TYPE'}};
        if ($debug) {
                print STDERR "\n#-- \"$table\" structure\n";
                for (my $i = 0; $i <= scalar(@names); $i++) {
                        next unless ($names[$i]);
                        print STDERR "\t".$names[$i]." (".$types[$i].")\n";
                }
        }
        print "Gathering data...";
        my @rows = ();
        while (my @arr = $sth->fetchrow_array()) {
                push(@rows, \@arr);
        }
        $sth->finish();
        print " done.\n";
        my $nrows = $dbf{$table}($table, \@names, \@rows);
        print &Log("Inserted $nrows of ".scalar(@rows)." records in table \"$table\" (".
                sprintf("%.2f%%", ($nrows * 100 / scalar(@rows))).")");
        print "\n";
}
close(LOG);
$xbdbh->disconnect();
$pgdbh->disconnect();
print "Log left in file \"$log\".\n";
exit $?;
1	ravilov	1.1	#! /usr/local/bin/perl -w
2			use strict;
3			$\| = 1;
4
5			my $debug = 0;
6	ravilov	1.2	my $raise = 0;
7	ravilov	1.1	my $log = "log";
8			my $xbase = "./registar";
9			my $db = "registar";
10	ravilov	1.2	my $refresh_timeout = 10;
11	ravilov	1.1
12			open(LOG, ">$log") \|\| die "can't open log: $!";
13
14			use DBI;
15			my $xbdbh = DBI->connect("DBI:XBase:$xbase", "", "",
16			{ 'RaiseError' => $raise }) \|\| die $DBI::errstr;
17			my $pgdbh = DBI->connect("DBI:Pg:dbname=$db", "dpavlin", "",
18			{ 'RaiseError' => $raise }) \|\| die $DBI::errstr;
19	ravilov	1.2	$SIG{'__WARN__'} = sub { }; # inhibit DBI warnings.
20	ravilov	1.1
21			my @tables = ();
22			if (@ARGV > 0) {
23			push(@tables, @ARGV);
24			} else {
25			push(@tables, <$xbase/*.dbf>);
26			# Sto da radim sa ovima?
27			#push(@tables, <$xbase/*.slk>);
28			#push(@tables, <$xbase/*.sylk>);
29			# OLD GLOBBING METHOD
30			# opendir(DIR, $xbase) \|\| die "can't open dir $xbase";
31			# @tables = grep { /\.dbf/i \|\| /\.slk/i \|\| /\.sylk/i } readdir(DIR);
32			# closedir(DIR);
33			}
34
35			sub ConvertDate {
36			my ($dt) = @_;
37			if ($dt =~ m,^([12][09]\d\d)([0-1]\d)([0-3]\d)$,) {
38			$dt = ($2 < 1 \|\| $2 > 12 \|\| $1 < 1900 \|\| $3 > 31) ? undef : "$1-$2-$3";
39			} elsif ($dt =~ m,^([12][09]\d\d)([0-1]\d)$,) {
40			$dt = ($2 >= 1 && $2 <= 12) ? "$1-$2-01" : "$1-01-01";
41			} elsif ($dt =~ m,^([12][09]\d\d)$,) {
42			$dt = "$1-01-01";
43			} else {
44			print STDERR "datum? ($dt) [nuke]\n";
45			# $dt = "NULL";
46			$dt = undef;
47			}
48	dpavlin	1.4	return $dt;
49	ravilov	1.1	}
50
51			sub ConvertBool {
52			my ($b) = @_;
53			$b \|\|= 0;
54			$b = ($b != 0) ? "true" : "false";
55			return $b;
56			}
57
58			sub ConvertString {
59			my $str = join("\n", @_);
60	ravilov	1.3	$str =~ s/[\x00-\x1F]+//gs;
61	ravilov	1.1	$str =~ s/\s\x8D?\s\r?\s\n\s/\n/gs;
62			$str =~ s/(^\s+\|\s+$)//gs;
63	dpavlin	1.4	$str =~ s/(^["']\|["']$)//g;
64	ravilov	1.1	# char recoding
65			#$str =~ s/đ/ð/g;
66			#$str =~ s/č/è/g;
67			#$str =~ s/ý/¾/g;
68			$str =~ tr/{\|`~}[\\@^]/¹ð¾èæ©Ð®ÈÆ/;
69	dpavlin	1.4	# return $pgdbh->quote($str);
70			return $str;
71	ravilov	1.1	}
72
73			sub Progress {
74			my ($count, $total) = @_;
75			return sprintf("%6.2f%%", ($count * 100 / scalar($total)))." done.\r";
76			}
77
78			sub Log {
79			my $msg = join("\n", @_);
80			print LOG "$msg\n";
81			return "$msg\n";
82			}
83
84	ravilov	1.2	sub CondInsert {
85	dpavlin	1.7	my $table = shift @_;
86			my $name = shift @_;
87			my $key = shift @_;
88			my $fields = shift @_;
89			my @values=@_; # chop argument(s) are values array
90			my $sql = "SELECT count($name) FROM $table WHERE ($name = ?)";
91			my $sth = $pgdbh->prepare($sql);
92			my $ret = $sth->execute($key);
93			my ($cnt) = $sth->fetchrow_array();
94			return 0 if (!$cnt == 0);
95	dpavlin	1.5	my @valtpl;
96			for (my $i=0; $i<=$#values; $i++) { push @valtpl,"?" }
97			$sql = "INSERT INTO $table $fields VALUES (".join(",",@valtpl).")";
98	dpavlin	1.7	$sth = $pgdbh->prepare($sql);
99			$ret = $sth->execute(@values);
100	ravilov	1.3	my $err = $pgdbh->errstr;
101	dpavlin	1.5	print &Log("[$sql] [".join(",",@values)."]".$err) if ($err);
102	ravilov	1.3	return $ret;
103	ravilov	1.2	}
104
105	ravilov	1.1	sub tblATK {
106			my ($table, $fields, $data) = @_;
107			my $count = 0;
108	dpavlin	1.6	my $sth = $pgdbh->prepare("INSERT INTO $table (sifra, naziv, razno) VALUES (?, ?, ?)");
109	ravilov	1.1	unless ($sth) {
110			print &Log("[$table-prepare] ".$pgdbh->errstr);
111			return 0;
112			}
113			my $refresh = 0;
114			foreach my $row (@$data) {
115			my ($sifra, $naziv, $razno) = @$row;
116			$sifra = &ConvertString($sifra);
117			$naziv = &ConvertString($naziv);
118			$razno = &ConvertString($razno);
119			my $tmp = $sth->execute($sifra, $naziv, $razno);
120			unless (defined($tmp)) {
121			print &Log("[$table-execute] ".$pgdbh->errstr);
122			next;
123			}
124			$tmp \|\|= 0;
125			$tmp = 0 if ($tmp < 0);
126			$count += $tmp;
127	ravilov	1.2	if (++$refresh >= $refresh_timeout) {
128	ravilov	1.1	$refresh = 0;
129			print Progress($count, scalar(@$data));
130			}
131			}
132			$sth->finish();
133			return $count;
134			}
135
136			sub tblDFZupa {
137			my ($table, $fields, $data) = @_;
138			my $count = 0;
139			my $sth = $pgdbh->prepare("INSERT INTO zupanije (broj, naziv, centar, pos_broj, npos_broj, spos_broj, grad, zip) VALUES (?, ?, ?, ?, ?, ?, ?, ?)");
140			unless ($sth) {
141			print &Log("[$table-prepare] ".$pgdbh->errstr);
142			return 0;
143			}
144			my $refresh = 0;
145			foreach my $row (@$data) {
146			my ($broj, $naziv, $centar, $pos_broj, $npos_broj,
147			$spos_broj, $grad, $zip, $grad_i) = @$row;
148			$broj += 0; # force numeric
149			$naziv = &ConvertString($naziv);
150			$centar = &ConvertString($centar);
151			$pos_broj = &ConvertString($pos_broj);
152			$npos_broj = &ConvertString($npos_broj);
153			$spos_broj = &ConvertString($spos_broj);
154			$grad = &ConvertString($grad);
155			$zip = &ConvertString($zip);
156			my $tmp = $sth->execute($broj, $naziv, $centar,
157			$pos_broj, $npos_broj, $spos_broj,
158			$grad, $zip);
159			unless (defined($tmp)) {
160			print &Log("[$table-execute] ".$pgdbh->errstr);
161			next;
162			}
163			$tmp = 0 if ($tmp < 0);
164			$count += $tmp;
165	ravilov	1.2	if (++$refresh >= $refresh_timeout) {
166	ravilov	1.1	$refresh = 0;
167			print Progress($count, scalar(@$data));
168			}
169			}
170			$sth->finish();
171			return $count;
172			}
173
174			sub tblINN {
175			return &tblATK(@_);
176			}
177
178			sub tblLijek {
179			my ($table, $fields, $data) = @_;
180	ravilov	1.3	my $count = 0;
181			my $sth = $pgdbh->prepare("INSERT INTO lijekovi (sifra, naziv, sif_atk, sif_inn, sif_proizvodjac, pakovanje, sastav_1, sastav_2, sif_nacin_izdavanja, sif_list_fond, sif_trudnoca, sif_dojencad, sif_vozac, sif_droga, cijena, sif_med, razno, glav_razno, djelovanje, indikacije, oprez) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
182			unless ($sth) {
183			print &Log("[$table-prepare] ".$pgdbh->errstr);
184			return 0;
185			}
186			my $refresh = 0;
187			foreach my $row (@$data) {
188			my ($sifra, $nsifra, $naziv, $sif_proizvodjac, $naz_proizvodjac,
189			$drzava, $zastupnistvo, $mjesto, $ulica_i_broj, $sif_inn,
190			$naz_inn, $sif_atk0, $naz_atk0, $sif_atk, $naz_atk,
191			$pakovanje, $sastav_1, $sastav_2, $sif_nacin_izdavanja,
192			$naz_nacin_izdavanja, $sif_list_fond, $naz_list_fond,
193			$sif_trudnoca, $naz_trudnoca, $sif_dojencad, $naz_dojencad,
194			$sif_vozac, $naz_vozac, $sif_droga, $cijena, $razno,
195			$sif_med, $glav_razno, $prazno, $djelovanje, $indikacije,
196			$oprez, $tel, $fax) = @$row;
197			$sifra += 0; # force numeric
198			$naziv = &ConvertString($naziv);
199			$sif_proizvodjac += 0; # force numeric
200			$naz_proizvodjac = &ConvertString($naz_proizvodjac);
201			$drzava = &ConvertString($drzava);
202			$zastupnistvo = &ConvertString($zastupnistvo);
203			$mjesto = &ConvertString($mjesto);
204			$ulica_i_broj = &ConvertString($ulica_i_broj);
205			$sif_inn = &ConvertString($sif_inn);
206			$naz_inn = &ConvertString($naz_inn);
207			$sif_atk = &ConvertString($sif_atk);
208			$naz_atk = &ConvertString($naz_atk);
209			$pakovanje = &ConvertString($pakovanje);
210			$sastav_1 = &ConvertString($sastav_1);
211			$sastav_2 = &ConvertString($sastav_2);
212			$sif_nacin_izdavanja = &ConvertString($sif_nacin_izdavanja);
213			$naz_nacin_izdavanja = &ConvertString($naz_nacin_izdavanja);
214			$sif_list_fond = &ConvertString($sif_list_fond);
215			$naz_list_fond = &ConvertString($naz_list_fond);
216			$sif_trudnoca = &ConvertString($sif_trudnoca);
217			$naz_trudnoca = &ConvertString($naz_trudnoca);
218			$sif_dojencad = &ConvertString($sif_dojencad);
219			$naz_dojencad = &ConvertString($naz_dojencad);
220			$sif_vozac = &ConvertString($sif_vozac);
221			$naz_vozac = &ConvertString($naz_vozac);
222			$sif_droga = &ConvertString($sif_droga);
223			$cijena = &ConvertString($cijena);
224			$razno = &ConvertString($razno);
225			$sif_med += 0; # force numeric
226			$glav_razno = &ConvertString($glav_razno);
227			$djelovanje = &ConvertString($djelovanje);
228			$indikacije = &ConvertString($indikacije);
229			$oprez = &ConvertString($oprez);
230			$tel = &ConvertString($tel);
231			$fax = &ConvertString($fax);
232	dpavlin	1.6	CondInsert("atk", "sifra", $sif_atk,
233	dpavlin	1.5	"(sifra, naziv, razno)", ($sif_atk, $naz_atk, ''));
234	dpavlin	1.6	CondInsert("inn", "sifra", $sif_inn,
235	dpavlin	1.5	"(sifra, naziv, razno)", ($sif_inn, $naz_inn, ''));
236	ravilov	1.3	CondInsert("proizvodjaci", "sifra", $sif_proizvodjac,
237			"(sifra, naziv, drzava, zastupnistvo, mjesto, zip, ulica_i_broj, razno, telefon, fax)",
238	dpavlin	1.5	($sif_proizvodjac, $naz_proizvodjac, $drzava, $zastupnistvo, $mjesto, '', $ulica_i_broj, '', $tel, $fax));
239	ravilov	1.3	CondInsert("nacin_izdavanja", "sifra", $sif_nacin_izdavanja,
240	dpavlin	1.5	"(sifra, opis)", ($sif_nacin_izdavanja, $naz_nacin_izdavanja));
241	ravilov	1.3	CondInsert("list_fond", "sifra", $sif_list_fond,
242	dpavlin	1.5	"(sifra, opis)", ($sif_list_fond, $naz_list_fond));
243	ravilov	1.3	CondInsert("trudnoca", "sifra", $sif_trudnoca,
244	dpavlin	1.5	"(sifra, opis)", ($sif_trudnoca, $naz_trudnoca));
245	ravilov	1.3	CondInsert("dojencad", "sifra", $sif_dojencad,
246	dpavlin	1.5	"(sifra, opis)", ($sif_dojencad, $naz_dojencad));
247	ravilov	1.3	CondInsert("vozac", "sifra", $sif_vozac,
248	dpavlin	1.5	"(sifra, opis)", ($sif_vozac, $naz_vozac));
249	ravilov	1.3	CondInsert("droga", "sifra", $sif_droga,
250	dpavlin	1.5	"(sifra, opis)", ($sif_droga, ''));
251	ravilov	1.3	my $tmp = $sth->execute($sifra, $naziv, $sif_atk, $sif_inn,
252			$sif_proizvodjac, $pakovanje, $sastav_1, $sastav_2,
253			$sif_nacin_izdavanja, $sif_list_fond, $sif_trudnoca,
254			$sif_dojencad, $sif_vozac, $sif_droga, $cijena,
255			$sif_med, $razno, $glav_razno, $djelovanje,
256			$indikacije, $oprez);
257			unless (defined($tmp)) {
258			print &Log("[$table-execute] ".$pgdbh->errstr);
259			next;
260			}
261			$tmp = 0 if ($tmp < 0);
262			$count += $tmp;
263			if (++$refresh >= $refresh_timeout) {
264			$refresh = 0;
265			print Progress($count, scalar(@$data));
266			}
267			}
268			$sth->finish();
269			return $count;
270	ravilov	1.1	}
271
272			sub tblProiz {
273			my ($table, $fields, $data) = @_;
274			my $count = 0;
275			my $sth = $pgdbh->prepare("INSERT INTO proizvodjaci (sifra, naziv, drzava, zastupnistvo, mjesto, zip, ulica_i_broj, razno, telefon, fax) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
276			unless ($sth) {
277			print &Log("[$table-prepare] ".$pgdbh->errstr);
278			return 0;
279			}
280			my $refresh = 0;
281			foreach my $row (@$data) {
282			my ($sifra, $naziv, $drzava, $zastupnistvo, $zip, $mjesto,
283			$ulica_i_broj, $razno, $tel, $fax) = @$row;
284			$sifra += 0; # force numeric
285			$naziv = &ConvertString($naziv);
286			$drzava = &ConvertString($drzava);
287			$zastupnistvo = &ConvertString($zastupnistvo);
288			$zip = &ConvertString($zip);
289			$mjesto = &ConvertString($mjesto);
290			$ulica_i_broj = &ConvertString($ulica_i_broj);
291			$razno = &ConvertString($razno);
292			$tel = &ConvertString($tel);
293			$fax = &ConvertString($fax);
294			my $tmp = $sth->execute($sifra, $naziv, $drzava,
295	ravilov	1.2	$zastupnistvo, $mjesto, $zip, $ulica_i_broj,
296	ravilov	1.1	$razno, $tel, $fax);
297			unless (defined($tmp)) {
298			print &Log("[$table-execute] ".$pgdbh->errstr);
299			next;
300			}
301			$tmp = 0 if ($tmp < 0);
302			$count += $tmp;
303	ravilov	1.2	if (++$refresh >= $refresh_timeout) {
304	ravilov	1.1	$refresh = 0;
305			print Progress($count, scalar(@$data));
306			}
307			}
308			$sth->finish();
309	ravilov	1.2	return $count;
310	ravilov	1.1	}
311
312			sub tblVodeca {
313			my ($table, $fields, $data) = @_;
314			my $count = 0;
315			my $refresh = 0;
316			foreach my $row (@$data) {
317			my ($sifra, $naziv) = @$row;
318	ravilov	1.2	$sifra =~ s/^(..)//;
319			my $code = $1;
320			$sifra = &ConvertString($sifra);
321	ravilov	1.1	$naziv = &ConvertString($naziv);
322	ravilov	1.2	my $tmp = 0;
323			if ($code eq "06") {
324			$tmp += CondInsert("nacin_izdavanja", "sifra", $sifra,
325	dpavlin	1.5	"(sifra, opis)", ($sifra, $naziv));
326	ravilov	1.2	} elsif ($code eq "07") {
327			$tmp += CondInsert("list_fond", "sifra", $sifra,
328	dpavlin	1.5	"(sifra, opis)", ($sifra, $naziv));
329	ravilov	1.2	} elsif ($code eq "08") {
330			$tmp += CondInsert("trudnoca", "sifra", $sifra,
331	dpavlin	1.5	"(sifra, opis)", ($sifra, $naziv));
332	ravilov	1.2	} elsif ($code eq "09") {
333			$tmp += CondInsert("dojencad", "sifra", $sifra,
334	dpavlin	1.5	"(sifra, opis)", ($sifra, $naziv));
335	ravilov	1.2	} elsif ($code eq "10") {
336			$tmp += CondInsert("vozac", "sifra", $sifra,
337	dpavlin	1.5	"(sifra, opis)", ($sifra, $naziv));
338	ravilov	1.2	} elsif ($code eq "11") {
339			$tmp += CondInsert("droga", "sifra", $sifra,
340	dpavlin	1.5	"(sifra, opis)", ($sifra, $naziv));
341	ravilov	1.2	} else {
342			# UNKNOWN CODE!!!
343			print &Log("[WARNING: unknown code \"$code\"]");
344	ravilov	1.1	}
345			$tmp = 0 if ($tmp < 0);
346			$count += $tmp;
347	ravilov	1.2	if (++$refresh >= $refresh_timeout) {
348	ravilov	1.1	$refresh = 0;
349			print Progress($count, scalar(@$data));
350			}
351			}
352	ravilov	1.2	return $count;
353	ravilov	1.1	}
354
355			my (%dbf) = (
356			"atk" => \&tblATK,
357			"atkstr" => undef,
358			"boja" => undef,
359			"dfzupa" => \&tblDFZupa,
360			"inn" => \&tblINN,
361			"innstr" => undef,
362			"lijek" => \&tblLijek,
363			"lijstr" => undef,
364			"proiz" => \&tblProiz,
365			"prostr" => undef,
366			"registar" => undef,
367			"regstr" => undef,
368			"strukt" => undef,
369			"vodeca" => \&tblVodeca
370			);
371
372			### MAIN
373			foreach my $table (@tables) {
374			next unless ($table =~ s/\.dbf$//i);
375			$table =~ s,^.+/([^/]+),$1,;
376			unless (exists($dbf{$table})) {
377			print "[WARNING: Don't know what to do with table \"$table\"!]\n\n";
378			next;
379			}
380			unless (defined($dbf{$table})) {
381			print "[WARNING: Ignoring table \"$table\"]\n\n";
382			next;
383			}
384			print &Log("Working on \"$table\"...");
385			my $sql = "SELECT * FROM $table";
386			my $sth = $xbdbh->prepare($sql) \|\| die "[$sql] ".$xbdbh->errstr();
387			$sth->execute() \|\| die "[$sql] ".$sth->errstr();
388			my @names = @{$sth->{'NAME'}};
389			my @types = @{$sth->{'TYPE'}};
390			if ($debug) {
391			print STDERR "\n#-- \"$table\" structure\n";
392			for (my $i = 0; $i <= scalar(@names); $i++) {
393			next unless ($names[$i]);
394			print STDERR "\t".$names[$i]." (".$types[$i].")\n";
395			}
396			}
397			print "Gathering data...";
398			my @rows = ();
399			while (my @arr = $sth->fetchrow_array()) {
400			push(@rows, \@arr);
401			}
402			$sth->finish();
403			print " done.\n";
404			my $nrows = $dbf{$table}($table, \@names, \@rows);
405			print &Log("Inserted $nrows of ".scalar(@rows)." records in table \"$table\" (".
406			sprintf("%.2f%%", ($nrows * 100 / scalar(@rows))).")");
407			print "\n";
408			}
409			close(LOG);
410			$xbdbh->disconnect();
411			$pgdbh->disconnect();
412	ravilov	1.2	print "Log left in file \"$log\".\n";
413	ravilov	1.1	exit $?;