/[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.6 - (hide annotations)
Tue Jun 19 10:10:48 2001 UTC (22 years, 9 months ago) by dpavlin
Branch: MAIN
Changes since 1.5: +3 -3 lines
File MIME type: text/plain
podjela atk i inn u dvije tablice (razliiti ifarnici!!!)

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.5 my @values=pop @_; # chop last argument which is array
86     my ($table, $name, $key, $fields) = @_;
87 ravilov 1.2 my $sql = "SELECT $name FROM $table WHERE ($name = $key)";
88     my $cnt = $pgdbh->do($sql);
89     return 0 if ($cnt > 0);
90 dpavlin 1.5 return 0 if ($#values == 0);
91     my @valtpl;
92     for (my $i=0; $i<=$#values; $i++) { push @valtpl,"?" }
93     $sql = "INSERT INTO $table $fields VALUES (".join(",",@valtpl).")";
94     my $sth = $pgdbh->prepare($sql);
95 dpavlin 1.4 my $ret = $sth->execute(@values);
96 ravilov 1.3 my $err = $pgdbh->errstr;
97 dpavlin 1.5 print &Log("[$sql] [".join(",",@values)."]".$err) if ($err);
98 ravilov 1.3 return $ret;
99 ravilov 1.2 }
100    
101 ravilov 1.1 sub tblATK {
102     my ($table, $fields, $data) = @_;
103     my $count = 0;
104 dpavlin 1.6 my $sth = $pgdbh->prepare("INSERT INTO $table (sifra, naziv, razno) VALUES (?, ?, ?)");
105 ravilov 1.1 unless ($sth) {
106     print &Log("[$table-prepare] ".$pgdbh->errstr);
107     return 0;
108     }
109     my $refresh = 0;
110     foreach my $row (@$data) {
111     my ($sifra, $naziv, $razno) = @$row;
112     $sifra = &ConvertString($sifra);
113     $naziv = &ConvertString($naziv);
114     $razno = &ConvertString($razno);
115     my $tmp = $sth->execute($sifra, $naziv, $razno);
116     unless (defined($tmp)) {
117     print &Log("[$table-execute] ".$pgdbh->errstr);
118     next;
119     }
120     $tmp ||= 0;
121     $tmp = 0 if ($tmp < 0);
122     $count += $tmp;
123 ravilov 1.2 if (++$refresh >= $refresh_timeout) {
124 ravilov 1.1 $refresh = 0;
125     print Progress($count, scalar(@$data));
126     }
127     }
128     $sth->finish();
129     return $count;
130     }
131    
132     sub tblDFZupa {
133     my ($table, $fields, $data) = @_;
134     my $count = 0;
135     my $sth = $pgdbh->prepare("INSERT INTO zupanije (broj, naziv, centar, pos_broj, npos_broj, spos_broj, grad, zip) VALUES (?, ?, ?, ?, ?, ?, ?, ?)");
136     unless ($sth) {
137     print &Log("[$table-prepare] ".$pgdbh->errstr);
138     return 0;
139     }
140     my $refresh = 0;
141     foreach my $row (@$data) {
142     my ($broj, $naziv, $centar, $pos_broj, $npos_broj,
143     $spos_broj, $grad, $zip, $grad_i) = @$row;
144     $broj += 0; # force numeric
145     $naziv = &ConvertString($naziv);
146     $centar = &ConvertString($centar);
147     $pos_broj = &ConvertString($pos_broj);
148     $npos_broj = &ConvertString($npos_broj);
149     $spos_broj = &ConvertString($spos_broj);
150     $grad = &ConvertString($grad);
151     $zip = &ConvertString($zip);
152     my $tmp = $sth->execute($broj, $naziv, $centar,
153     $pos_broj, $npos_broj, $spos_broj,
154     $grad, $zip);
155     unless (defined($tmp)) {
156     print &Log("[$table-execute] ".$pgdbh->errstr);
157     next;
158     }
159     $tmp = 0 if ($tmp < 0);
160     $count += $tmp;
161 ravilov 1.2 if (++$refresh >= $refresh_timeout) {
162 ravilov 1.1 $refresh = 0;
163     print Progress($count, scalar(@$data));
164     }
165     }
166     $sth->finish();
167     return $count;
168     }
169    
170     sub tblINN {
171     return &tblATK(@_);
172     }
173    
174     sub tblLijek {
175     my ($table, $fields, $data) = @_;
176 ravilov 1.3 my $count = 0;
177     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 (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
178     unless ($sth) {
179     print &Log("[$table-prepare] ".$pgdbh->errstr);
180     return 0;
181     }
182     my $refresh = 0;
183     foreach my $row (@$data) {
184     my ($sifra, $nsifra, $naziv, $sif_proizvodjac, $naz_proizvodjac,
185     $drzava, $zastupnistvo, $mjesto, $ulica_i_broj, $sif_inn,
186     $naz_inn, $sif_atk0, $naz_atk0, $sif_atk, $naz_atk,
187     $pakovanje, $sastav_1, $sastav_2, $sif_nacin_izdavanja,
188     $naz_nacin_izdavanja, $sif_list_fond, $naz_list_fond,
189     $sif_trudnoca, $naz_trudnoca, $sif_dojencad, $naz_dojencad,
190     $sif_vozac, $naz_vozac, $sif_droga, $cijena, $razno,
191     $sif_med, $glav_razno, $prazno, $djelovanje, $indikacije,
192     $oprez, $tel, $fax) = @$row;
193     $sifra += 0; # force numeric
194     $naziv = &ConvertString($naziv);
195     $sif_proizvodjac += 0; # force numeric
196     $naz_proizvodjac = &ConvertString($naz_proizvodjac);
197     $drzava = &ConvertString($drzava);
198     $zastupnistvo = &ConvertString($zastupnistvo);
199     $mjesto = &ConvertString($mjesto);
200     $ulica_i_broj = &ConvertString($ulica_i_broj);
201     $sif_inn = &ConvertString($sif_inn);
202     $naz_inn = &ConvertString($naz_inn);
203     $sif_atk = &ConvertString($sif_atk);
204     $naz_atk = &ConvertString($naz_atk);
205     $pakovanje = &ConvertString($pakovanje);
206     $sastav_1 = &ConvertString($sastav_1);
207     $sastav_2 = &ConvertString($sastav_2);
208     $sif_nacin_izdavanja = &ConvertString($sif_nacin_izdavanja);
209     $naz_nacin_izdavanja = &ConvertString($naz_nacin_izdavanja);
210     $sif_list_fond = &ConvertString($sif_list_fond);
211     $naz_list_fond = &ConvertString($naz_list_fond);
212     $sif_trudnoca = &ConvertString($sif_trudnoca);
213     $naz_trudnoca = &ConvertString($naz_trudnoca);
214     $sif_dojencad = &ConvertString($sif_dojencad);
215     $naz_dojencad = &ConvertString($naz_dojencad);
216     $sif_vozac = &ConvertString($sif_vozac);
217     $naz_vozac = &ConvertString($naz_vozac);
218     $sif_droga = &ConvertString($sif_droga);
219     $cijena = &ConvertString($cijena);
220     $razno = &ConvertString($razno);
221     $sif_med += 0; # force numeric
222     $glav_razno = &ConvertString($glav_razno);
223     $djelovanje = &ConvertString($djelovanje);
224     $indikacije = &ConvertString($indikacije);
225     $oprez = &ConvertString($oprez);
226     $tel = &ConvertString($tel);
227     $fax = &ConvertString($fax);
228 dpavlin 1.6 CondInsert("atk", "sifra", $sif_atk,
229 dpavlin 1.5 "(sifra, naziv, razno)", ($sif_atk, $naz_atk, ''));
230 dpavlin 1.6 CondInsert("inn", "sifra", $sif_inn,
231 dpavlin 1.5 "(sifra, naziv, razno)", ($sif_inn, $naz_inn, ''));
232 ravilov 1.3 CondInsert("proizvodjaci", "sifra", $sif_proizvodjac,
233     "(sifra, naziv, drzava, zastupnistvo, mjesto, zip, ulica_i_broj, razno, telefon, fax)",
234 dpavlin 1.5 ($sif_proizvodjac, $naz_proizvodjac, $drzava, $zastupnistvo, $mjesto, '', $ulica_i_broj, '', $tel, $fax));
235 ravilov 1.3 CondInsert("nacin_izdavanja", "sifra", $sif_nacin_izdavanja,
236 dpavlin 1.5 "(sifra, opis)", ($sif_nacin_izdavanja, $naz_nacin_izdavanja));
237 ravilov 1.3 CondInsert("list_fond", "sifra", $sif_list_fond,
238 dpavlin 1.5 "(sifra, opis)", ($sif_list_fond, $naz_list_fond));
239 ravilov 1.3 CondInsert("trudnoca", "sifra", $sif_trudnoca,
240 dpavlin 1.5 "(sifra, opis)", ($sif_trudnoca, $naz_trudnoca));
241 ravilov 1.3 CondInsert("dojencad", "sifra", $sif_dojencad,
242 dpavlin 1.5 "(sifra, opis)", ($sif_dojencad, $naz_dojencad));
243 ravilov 1.3 CondInsert("vozac", "sifra", $sif_vozac,
244 dpavlin 1.5 "(sifra, opis)", ($sif_vozac, $naz_vozac));
245 ravilov 1.3 CondInsert("droga", "sifra", $sif_droga,
246 dpavlin 1.5 "(sifra, opis)", ($sif_droga, ''));
247 ravilov 1.3 my $tmp = $sth->execute($sifra, $naziv, $sif_atk, $sif_inn,
248     $sif_proizvodjac, $pakovanje, $sastav_1, $sastav_2,
249     $sif_nacin_izdavanja, $sif_list_fond, $sif_trudnoca,
250     $sif_dojencad, $sif_vozac, $sif_droga, $cijena,
251     $sif_med, $razno, $glav_razno, $djelovanje,
252     $indikacije, $oprez);
253     unless (defined($tmp)) {
254     print &Log("[$table-execute] ".$pgdbh->errstr);
255     next;
256     }
257     $tmp = 0 if ($tmp < 0);
258     $count += $tmp;
259     if (++$refresh >= $refresh_timeout) {
260     $refresh = 0;
261     print Progress($count, scalar(@$data));
262     }
263     }
264     $sth->finish();
265     return $count;
266 ravilov 1.1 }
267    
268     sub tblProiz {
269     my ($table, $fields, $data) = @_;
270     my $count = 0;
271     my $sth = $pgdbh->prepare("INSERT INTO proizvodjaci (sifra, naziv, drzava, zastupnistvo, mjesto, zip, ulica_i_broj, razno, telefon, fax) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
272     unless ($sth) {
273     print &Log("[$table-prepare] ".$pgdbh->errstr);
274     return 0;
275     }
276     my $refresh = 0;
277     foreach my $row (@$data) {
278     my ($sifra, $naziv, $drzava, $zastupnistvo, $zip, $mjesto,
279     $ulica_i_broj, $razno, $tel, $fax) = @$row;
280     $sifra += 0; # force numeric
281     $naziv = &ConvertString($naziv);
282     $drzava = &ConvertString($drzava);
283     $zastupnistvo = &ConvertString($zastupnistvo);
284     $zip = &ConvertString($zip);
285     $mjesto = &ConvertString($mjesto);
286     $ulica_i_broj = &ConvertString($ulica_i_broj);
287     $razno = &ConvertString($razno);
288     $tel = &ConvertString($tel);
289     $fax = &ConvertString($fax);
290     my $tmp = $sth->execute($sifra, $naziv, $drzava,
291 ravilov 1.2 $zastupnistvo, $mjesto, $zip, $ulica_i_broj,
292 ravilov 1.1 $razno, $tel, $fax);
293     unless (defined($tmp)) {
294     print &Log("[$table-execute] ".$pgdbh->errstr);
295     next;
296     }
297     $tmp = 0 if ($tmp < 0);
298     $count += $tmp;
299 ravilov 1.2 if (++$refresh >= $refresh_timeout) {
300 ravilov 1.1 $refresh = 0;
301     print Progress($count, scalar(@$data));
302     }
303     }
304     $sth->finish();
305 ravilov 1.2 return $count;
306 ravilov 1.1 }
307    
308     sub tblVodeca {
309     my ($table, $fields, $data) = @_;
310     my $count = 0;
311     my $refresh = 0;
312     foreach my $row (@$data) {
313     my ($sifra, $naziv) = @$row;
314 ravilov 1.2 $sifra =~ s/^(..)//;
315     my $code = $1;
316     $sifra = &ConvertString($sifra);
317 ravilov 1.1 $naziv = &ConvertString($naziv);
318 ravilov 1.2 my $tmp = 0;
319     if ($code eq "06") {
320     $tmp += CondInsert("nacin_izdavanja", "sifra", $sifra,
321 dpavlin 1.5 "(sifra, opis)", ($sifra, $naziv));
322 ravilov 1.2 } elsif ($code eq "07") {
323     $tmp += CondInsert("list_fond", "sifra", $sifra,
324 dpavlin 1.5 "(sifra, opis)", ($sifra, $naziv));
325 ravilov 1.2 } elsif ($code eq "08") {
326     $tmp += CondInsert("trudnoca", "sifra", $sifra,
327 dpavlin 1.5 "(sifra, opis)", ($sifra, $naziv));
328 ravilov 1.2 } elsif ($code eq "09") {
329     $tmp += CondInsert("dojencad", "sifra", $sifra,
330 dpavlin 1.5 "(sifra, opis)", ($sifra, $naziv));
331 ravilov 1.2 } elsif ($code eq "10") {
332     $tmp += CondInsert("vozac", "sifra", $sifra,
333 dpavlin 1.5 "(sifra, opis)", ($sifra, $naziv));
334 ravilov 1.2 } elsif ($code eq "11") {
335     $tmp += CondInsert("droga", "sifra", $sifra,
336 dpavlin 1.5 "(sifra, opis)", ($sifra, $naziv));
337 ravilov 1.2 } else {
338     # UNKNOWN CODE!!!
339     print &Log("[WARNING: unknown code \"$code\"]");
340 ravilov 1.1 }
341     $tmp = 0 if ($tmp < 0);
342     $count += $tmp;
343 ravilov 1.2 if (++$refresh >= $refresh_timeout) {
344 ravilov 1.1 $refresh = 0;
345     print Progress($count, scalar(@$data));
346     }
347     }
348 ravilov 1.2 return $count;
349 ravilov 1.1 }
350    
351     my (%dbf) = (
352     "atk" => \&tblATK,
353     "atkstr" => undef,
354     "boja" => undef,
355     "dfzupa" => \&tblDFZupa,
356     "inn" => \&tblINN,
357     "innstr" => undef,
358     "lijek" => \&tblLijek,
359     "lijstr" => undef,
360     "proiz" => \&tblProiz,
361     "prostr" => undef,
362     "registar" => undef,
363     "regstr" => undef,
364     "strukt" => undef,
365     "vodeca" => \&tblVodeca
366     );
367    
368     ### MAIN
369     foreach my $table (@tables) {
370     next unless ($table =~ s/\.dbf$//i);
371     $table =~ s,^.+/([^/]+),$1,;
372     unless (exists($dbf{$table})) {
373     print "[WARNING: Don't know what to do with table \"$table\"!]\n\n";
374     next;
375     }
376     unless (defined($dbf{$table})) {
377     print "[WARNING: Ignoring table \"$table\"]\n\n";
378     next;
379     }
380     print &Log("Working on \"$table\"...");
381     my $sql = "SELECT * FROM $table";
382     my $sth = $xbdbh->prepare($sql) || die "[$sql] ".$xbdbh->errstr();
383     $sth->execute() || die "[$sql] ".$sth->errstr();
384     my @names = @{$sth->{'NAME'}};
385     my @types = @{$sth->{'TYPE'}};
386     if ($debug) {
387     print STDERR "\n#-- \"$table\" structure\n";
388     for (my $i = 0; $i <= scalar(@names); $i++) {
389     next unless ($names[$i]);
390     print STDERR "\t".$names[$i]." (".$types[$i].")\n";
391     }
392     }
393     print "Gathering data...";
394     my @rows = ();
395     while (my @arr = $sth->fetchrow_array()) {
396     push(@rows, \@arr);
397     }
398     $sth->finish();
399     print " done.\n";
400     my $nrows = $dbf{$table}($table, \@names, \@rows);
401     print &Log("Inserted $nrows of ".scalar(@rows)." records in table \"$table\" (".
402     sprintf("%.2f%%", ($nrows * 100 / scalar(@rows))).")");
403     print "\n";
404     }
405     close(LOG);
406     $xbdbh->disconnect();
407     $pgdbh->disconnect();
408 ravilov 1.2 print "Log left in file \"$log\".\n";
409 ravilov 1.1 exit $?;

  ViewVC Help
Powered by ViewVC 1.1.26