1 |
#!/usr/local/bin/perl -w |
2 |
|
3 |
use DBI; |
4 |
my $dbh2 = DBI->connect("DBI:Pg:dbname=informatika","","") || die $DBI::errstr; |
5 |
my $dbh = DBI->connect("DBI:Pg:dbname=informatika","","") || die $DBI::errstr; |
6 |
|
7 |
my %lokacija_id_trans = ( |
8 |
"Borongaj" => 3, |
9 |
"Dubrava" => 13, |
10 |
"Glina" => 8, |
11 |
"Hrvatski Leskovac" => 5, |
12 |
"Jagiæeva " => 12, |
13 |
"Jagiæeva" => 12, |
14 |
"Jagiæeva 31" => 12, |
15 |
"Kalinovica" => 6, |
16 |
"Kalonovica" => 6, |
17 |
"lokacija" => 15, |
18 |
"Osijek" => 11, |
19 |
"PBF" => 1, |
20 |
"Planinska" => 3, |
21 |
"Rijeka" => 10, |
22 |
"Savski Marof" => 4, |
23 |
"S. Marof" => 4, |
24 |
"Trogir" => 9, |
25 |
"UGV" => 2, |
26 |
"Vukovarska" => 2 |
27 |
); |
28 |
|
29 |
open(IN,"zaotvaranje.txt") || die "zaotvaranje: $!"; |
30 |
while(<IN>) { |
31 |
chomp; |
32 |
s/\015//g; # kill cr |
33 |
|
34 |
tr/ðèæÐÈÆ/¹ð¾èæ©Ð®ÈÆ/; # 1250 -> iso8859-2 |
35 |
|
36 |
s/^"//; |
37 |
s/"$//; |
38 |
s/\t"/\t/g; |
39 |
s/"\t/\t/g; |
40 |
s/ */ /g; |
41 |
|
42 |
next if (/^#/); |
43 |
|
44 |
my ($ime,$prezime,$sifrarad,$lokacija,$objekt,$kat,$soba,$z,$oznaka,$lozinka,$email,$telefon,$datumzaprimanja,$status_id1,$kontakt_id1,$datummreza,$status_id2,$kontakt_id2,$datumserver,$status_id3,$kontakt_id3,$lupdate,$status_id4,$kontakt_id4) = split(/\t/,$_); |
45 |
|
46 |
sub fill_status { |
47 |
my ($osoba_id,$datum,$status_id,$kontakt_id) = @_; |
48 |
$sql="insert into status (osoba_id,datum,status_tip_id,kontakt_osoba_id) values ($osoba_id,?,?,?)"; |
49 |
$sth = $dbh2->prepare("$sql") || die $dbh2->errstr(); |
50 |
|
51 |
if ($datum && $datum ne "") { |
52 |
$sth->execute($datum,$status_id,$kontakt_id) || die $sth->errstr(); |
53 |
} |
54 |
} |
55 |
|
56 |
if ($ime && $prezime && $ime eq $last_ime && $prezime eq $last_prezime) { |
57 |
fill_status($osoba_id,$lupdate,$status_id4,$kontakt_id4); |
58 |
next; |
59 |
} |
60 |
|
61 |
$zap_plive="false"; |
62 |
$org_jed_id=0; |
63 |
$org_pod_jed_id=0; |
64 |
$lokacija_id=15; # nepoznato |
65 |
$lokacija_id=$lokacija_id_trans{$lokacija} if (defined($lokacija_id_trans{$lokacija})); |
66 |
|
67 |
if ($sifrarad ne "" && $sifrarad=~/^\d+$/) { |
68 |
$sth = $dbh->prepare("select sifra,ime,prezime,mt,orgjed from sap where sifra='$sifrarad'") || die $dbh->errstr(); |
69 |
|
70 |
$sth->execute() || die $sth->errstr(); |
71 |
if (($sifra,$ime,$prezime,undef,$sap_orgjed) = $sth->fetchrow_array()) { |
72 |
$zap_plive="true"; |
73 |
$sth = $dbh->prepare("select nad_id,pod_id from sap2orgjed where sap='$sap_orgjed'") || die $dbh->errstr(); |
74 |
$sth->execute() || die $sth->errstr(); |
75 |
if (($org_jed_id,$org_pod_jed_id) = $sth->fetchrow_array()) { |
76 |
} else { |
77 |
print "warn: can't find orgjed $sap_orgjed\n"; |
78 |
($org_jed_id,$org_pod_jed_id) = (0,0); |
79 |
} |
80 |
|
81 |
} |
82 |
} |
83 |
|
84 |
|
85 |
$sql="insert into osobe |
86 |
(ime,prezime,zap_plive,sifra, |
87 |
org_jed_id,org_pod_jed_id,lokacija_id, |
88 |
objekt,kat,soba,tel,shell,datum_unesen, |
89 |
datum_otvoren,datum_instalacije,kontakt_osoba_id, |
90 |
lok_racunala,podesavanje,z_rac,datum_printanja,umrezavanje) |
91 |
values |
92 |
('$ime','$prezime',$zap_plive,'$sifra', |
93 |
$org_jed_id,$org_pod_jed_id,$lokacija_id, |
94 |
'$objekt','$kat','$soba','$telefon',false,null, |
95 |
null,null,$kontakt_id1, |
96 |
'',false,'$z',null,false) |
97 |
"; |
98 |
|
99 |
# print "--$sql\n"; |
100 |
$dbh2->do("$sql") || die $dbh2->errstr(); |
101 |
$sth = $dbh2->prepare("select last_value from osobe_id_seq") || die $dbh2->errstr(); |
102 |
$sth->execute() || die $sth->errstr(); |
103 |
($osoba_id) = $sth->fetchrow_array(); |
104 |
|
105 |
fill_status($osoba_id,$datummreza,$status_id1,$kontakt_id1); |
106 |
fill_status($osoba_id,$datummreza,$status_id2,$kontakt_id2); |
107 |
fill_status($osoba_id,$datumserver,$status_id3,$kontakt_id3); |
108 |
fill_status($osoba_id,$lupdate,$status_id4,$kontakt_id4); |
109 |
|
110 |
$lozinka=~s/'/\\'/g; |
111 |
$sql="insert into racuni (osoba_id,login,passwd) |
112 |
values ($osoba_id,'$oznaka','$lozinka')"; |
113 |
$dbh2->do("$sql") || die $dbh2->errstr(); |
114 |
|
115 |
$sql="insert into e_mail (osoba_id,alias) |
116 |
values ($osoba_id,'$email')"; |
117 |
$dbh2->do("$sql") || die $dbh2->errstr(); |
118 |
|
119 |
$last_ime=$ime; |
120 |
$last_prezime=$prezime; |
121 |
|
122 |
} |
123 |
|
124 |
print "\n"; |