| 1 |
dpavlin |
1.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"; |
Parent Directory
| 
Revision Log