/[registar]/convert.pl
This is repository of my old source code which isn't updated any more. Go to git.rot13.org for current projects!
ViewVC logotype

Annotation of /convert.pl

Parent Directory Parent Directory | Revision Log Revision Log


Revision 1.7 - (hide annotations)
Tue Jun 19 13:39:05 2001 UTC (18 years ago) by dpavlin
Branch: MAIN
CVS Tags: HEAD
Changes since 1.6: +12 -8 lines
File MIME type: text/plain
CondInsert working again

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/&#273;/ð/g;
66     #$str =~ s/&#269;/è/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 $?;

  ViewVC Help
Powered by ViewVC 1.1.26