| 1 |
#!/usr/local/bin/perl -w |
| 2 |
|
| 3 |
# usage: cat file_sa_siframa | ./batch-open.pl |
| 4 |
# |
| 5 |
# file_sa_siframa ima za prvo polje (tab delimited) sifre radnika iz SAP-a |
| 6 |
|
| 7 |
use DBI; |
| 8 |
|
| 9 |
my $debug=0; |
| 10 |
|
| 11 |
my $import=0; # 1==import, file format za import: |
| 12 |
# ¹ifra_radnika <tab> e-mail alias <tab> login <tab> password |
| 13 |
|
| 14 |
my $dbh = DBI->connect("DBI:Pg:dbname=informatika","","") || die $DBI::errstr; |
| 15 |
if ($debug) { |
| 16 |
$dbh2 = DBI->connect("DBI:Pg:dbname=informatika2","","") || die $DBI::errstr; |
| 17 |
} else { |
| 18 |
$dbh2 = DBI->connect("DBI:Pg:dbname=informatika","","") || die $DBI::errstr; |
| 19 |
} |
| 20 |
|
| 21 |
my $max_osoba_id; # za last_print |
| 22 |
|
| 23 |
while(<>) { |
| 24 |
chomp; |
| 25 |
s/\015//g; # kill cr |
| 26 |
|
| 27 |
tr/ðèæÐÈÆ/¹ð¾èæ©Ð®ÈÆ/; # 1250 -> iso8859-2 |
| 28 |
|
| 29 |
s/^"//; |
| 30 |
s/"$//; |
| 31 |
s/\t"/\t/g; |
| 32 |
s/"\t/\t/g; |
| 33 |
s/ */ /g; |
| 34 |
|
| 35 |
next if (/^#/); |
| 36 |
|
| 37 |
if (m/\d+\t\S+\t\S+/) { |
| 38 |
print "warn: forced import based on input format!\n"; |
| 39 |
$import=1; |
| 40 |
} |
| 41 |
|
| 42 |
if ($import) { |
| 43 |
($sifrarad,$email,$login,$passwd,undef) = split(/\t/,$_,5); |
| 44 |
} else { |
| 45 |
$sifrarad=$_; |
| 46 |
} |
| 47 |
|
| 48 |
sub fill_status { |
| 49 |
my ($osoba_id,$datum,$status_id,$kontakt_id) = @_; |
| 50 |
$sql="insert into status (osoba_id,datum,status_tip_id,kontakt_osoba_id) values ($osoba_id,?,?,?)"; |
| 51 |
$sth = $dbh2->prepare("$sql") || die $dbh2->errstr(); |
| 52 |
|
| 53 |
if ($datum && $datum ne "") { |
| 54 |
$sth->execute($datum,$status_id,$kontakt_id) || die $sth->errstr(); |
| 55 |
} |
| 56 |
} |
| 57 |
|
| 58 |
$zap_plive="true"; |
| 59 |
$org_jed_id=0; |
| 60 |
$org_pod_jed_id=0; |
| 61 |
$lokacija_id=15; # nepoznato |
| 62 |
$kontakt_id=1; # PCST |
| 63 |
|
| 64 |
if ($sifrarad ne "" && $sifrarad=~/^\d+$/) { |
| 65 |
$sth = $dbh->prepare("select sifra,ime,prezime,mt,orgjed from sap where sifra='$sifrarad'") || die $dbh->errstr(); |
| 66 |
|
| 67 |
$sth->execute() || die $sth->errstr(); |
| 68 |
if (($sifra,$ime,$prezime,undef,$sap_orgjed) = $sth->fetchrow_array()) { |
| 69 |
$zap_plive="true"; |
| 70 |
$sth = $dbh->prepare("select nad_id,pod_id from sap2orgjed where sap='$sap_orgjed'") || die $dbh->errstr(); |
| 71 |
$sth->execute() || die $sth->errstr(); |
| 72 |
if (($org_jed_id,$org_pod_jed_id) = $sth->fetchrow_array()) { |
| 73 |
} else { |
| 74 |
print "warn: can't find orgjed $sap_orgjed\n"; |
| 75 |
($org_jed_id,$org_pod_jed_id) = (0,0); |
| 76 |
} |
| 77 |
} else { |
| 78 |
print "warn: can't find employe $sifrarad !"; |
| 79 |
} |
| 80 |
} |
| 81 |
|
| 82 |
|
| 83 |
$sql="insert into osobe |
| 84 |
(ime,prezime,zap_plive,sifra, |
| 85 |
org_jed_id,org_pod_jed_id,lokacija_id, |
| 86 |
objekt,kat,soba,tel,shell,datum_unesen, |
| 87 |
datum_otvoren,datum_instalacije,kontakt_osoba_id, |
| 88 |
lok_racunala,podesavanje,z_rac,datum_printanja,umrezavanje) |
| 89 |
values |
| 90 |
('$ime','$prezime',$zap_plive,'$sifra', |
| 91 |
$org_jed_id,$org_pod_jed_id,$lokacija_id, |
| 92 |
'','','','$telefon',false,null, |
| 93 |
null,null,$kontakt_id, |
| 94 |
'',false,'',null,false) |
| 95 |
"; |
| 96 |
|
| 97 |
# print "--$sql\n"; |
| 98 |
$dbh2->do("$sql") || die $dbh2->errstr(); |
| 99 |
$sth = $dbh2->prepare("select last_value from osobe_id_seq") || die $dbh2->errstr(); |
| 100 |
$sth->execute() || die $sth->errstr(); |
| 101 |
($osoba_id) = $sth->fetchrow_array(); |
| 102 |
|
| 103 |
fill_status($osoba_id,"now()",1,$kontakt_id); # unesen |
| 104 |
|
| 105 |
if ($import) { |
| 106 |
fill_status($osoba_id,"now()",3,$kontakt_id); # otvoren |
| 107 |
|
| 108 |
$passwd=~s/'/\\'/g; |
| 109 |
$sql="insert into racuni (osoba_id,login,passwd) |
| 110 |
values ($osoba_id,'$login','$passwd')"; |
| 111 |
$dbh2->do("$sql") || die $dbh2->errstr(); |
| 112 |
|
| 113 |
$sql="insert into e_mail (osoba_id,alias) |
| 114 |
values ($osoba_id,'$email')"; |
| 115 |
$dbh2->do("$sql") || die $dbh2->errstr(); |
| 116 |
|
| 117 |
$max_osoba_id = $osoba_id if ($osoba_id > $max_osoba_id); |
| 118 |
} |
| 119 |
|
| 120 |
} |
| 121 |
|
| 122 |
if ($import) { |
| 123 |
$dbh->do("insert into last_print values ('now'::datetime,$max_osoba_id)"); |
| 124 |
} |
| 125 |
|
| 126 |
print "\n"; |
Parent Directory
| 
Revision Log