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"; |