1 |
dpavlin |
1.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"; |