/[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

Contents of /convert.pl

Parent Directory Parent Directory | Revision Log Revision Log


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

1 #! /usr/local/bin/perl -w
2 use strict;
3 $| = 1;
4
5 my $debug = 0;
6 my $raise = 0;
7 my $log = "log";
8 my $xbase = "./registar";
9 my $db = "registar";
10 my $refresh_timeout = 10;
11
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 $SIG{'__WARN__'} = sub { }; # inhibit DBI warnings.
20
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 return $dt;
49 }
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 $str =~ s/[\x00-\x1F]+//gs;
61 $str =~ s/\s*\x8D?\s*\r?\s*\n\s*/\n/gs;
62 $str =~ s/(^\s+|\s+$)//gs;
63 $str =~ s/(^["']|["']$)//g;
64 # char recoding
65 #$str =~ s/&#273;/ð/g;
66 #$str =~ s/&#269;/è/g;
67 #$str =~ s/ý/¾/g;
68 $str =~ tr/{|`~}[\\@^]/¹ð¾èæ©Ð®ÈÆ/;
69 # return $pgdbh->quote($str);
70 return $str;
71 }
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 sub CondInsert {
85 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 my @valtpl;
96 for (my $i=0; $i<=$#values; $i++) { push @valtpl,"?" }
97 $sql = "INSERT INTO $table $fields VALUES (".join(",",@valtpl).")";
98 $sth = $pgdbh->prepare($sql);
99 $ret = $sth->execute(@values);
100 my $err = $pgdbh->errstr;
101 print &Log("[$sql] [".join(",",@values)."]".$err) if ($err);
102 return $ret;
103 }
104
105 sub tblATK {
106 my ($table, $fields, $data) = @_;
107 my $count = 0;
108 my $sth = $pgdbh->prepare("INSERT INTO $table (sifra, naziv, razno) VALUES (?, ?, ?)");
109 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 if (++$refresh >= $refresh_timeout) {
128 $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 if (++$refresh >= $refresh_timeout) {
166 $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 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 CondInsert("atk", "sifra", $sif_atk,
233 "(sifra, naziv, razno)", ($sif_atk, $naz_atk, ''));
234 CondInsert("inn", "sifra", $sif_inn,
235 "(sifra, naziv, razno)", ($sif_inn, $naz_inn, ''));
236 CondInsert("proizvodjaci", "sifra", $sif_proizvodjac,
237 "(sifra, naziv, drzava, zastupnistvo, mjesto, zip, ulica_i_broj, razno, telefon, fax)",
238 ($sif_proizvodjac, $naz_proizvodjac, $drzava, $zastupnistvo, $mjesto, '', $ulica_i_broj, '', $tel, $fax));
239 CondInsert("nacin_izdavanja", "sifra", $sif_nacin_izdavanja,
240 "(sifra, opis)", ($sif_nacin_izdavanja, $naz_nacin_izdavanja));
241 CondInsert("list_fond", "sifra", $sif_list_fond,
242 "(sifra, opis)", ($sif_list_fond, $naz_list_fond));
243 CondInsert("trudnoca", "sifra", $sif_trudnoca,
244 "(sifra, opis)", ($sif_trudnoca, $naz_trudnoca));
245 CondInsert("dojencad", "sifra", $sif_dojencad,
246 "(sifra, opis)", ($sif_dojencad, $naz_dojencad));
247 CondInsert("vozac", "sifra", $sif_vozac,
248 "(sifra, opis)", ($sif_vozac, $naz_vozac));
249 CondInsert("droga", "sifra", $sif_droga,
250 "(sifra, opis)", ($sif_droga, ''));
251 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 }
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 $zastupnistvo, $mjesto, $zip, $ulica_i_broj,
296 $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 if (++$refresh >= $refresh_timeout) {
304 $refresh = 0;
305 print Progress($count, scalar(@$data));
306 }
307 }
308 $sth->finish();
309 return $count;
310 }
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 $sifra =~ s/^(..)//;
319 my $code = $1;
320 $sifra = &ConvertString($sifra);
321 $naziv = &ConvertString($naziv);
322 my $tmp = 0;
323 if ($code eq "06") {
324 $tmp += CondInsert("nacin_izdavanja", "sifra", $sifra,
325 "(sifra, opis)", ($sifra, $naziv));
326 } elsif ($code eq "07") {
327 $tmp += CondInsert("list_fond", "sifra", $sifra,
328 "(sifra, opis)", ($sifra, $naziv));
329 } elsif ($code eq "08") {
330 $tmp += CondInsert("trudnoca", "sifra", $sifra,
331 "(sifra, opis)", ($sifra, $naziv));
332 } elsif ($code eq "09") {
333 $tmp += CondInsert("dojencad", "sifra", $sifra,
334 "(sifra, opis)", ($sifra, $naziv));
335 } elsif ($code eq "10") {
336 $tmp += CondInsert("vozac", "sifra", $sifra,
337 "(sifra, opis)", ($sifra, $naziv));
338 } elsif ($code eq "11") {
339 $tmp += CondInsert("droga", "sifra", $sifra,
340 "(sifra, opis)", ($sifra, $naziv));
341 } else {
342 # UNKNOWN CODE!!!
343 print &Log("[WARNING: unknown code \"$code\"]");
344 }
345 $tmp = 0 if ($tmp < 0);
346 $count += $tmp;
347 if (++$refresh >= $refresh_timeout) {
348 $refresh = 0;
349 print Progress($count, scalar(@$data));
350 }
351 }
352 return $count;
353 }
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 print "Log left in file \"$log\".\n";
413 exit $?;

  ViewVC Help
Powered by ViewVC 1.1.26