1 |
#!/usr/local/bin/perl -w |
2 |
|
3 |
use strict; |
4 |
|
5 |
my $debug=0; |
6 |
|
7 |
use DBI; |
8 |
use POSIX qw(strftime); |
9 |
|
10 |
$debug++ if ($ARGV[0] eq "-d"); |
11 |
|
12 |
print STDERR "Debug mod: output je u /tmp/debug*\n" if ($debug); |
13 |
|
14 |
my $currdate = strftime "%d.%m.%Y %H:%M:%S", localtime; |
15 |
|
16 |
my $html_file=$currdate; |
17 |
$html_file=~s/ /_/g; |
18 |
$html_file="izvjestaji/$html_file.html"; |
19 |
|
20 |
if (! $debug) { |
21 |
open(HTML,"> $html_file") || die "html: $!"; |
22 |
open(MAIL,"|/usr/lib/sendmail biljana.gombar\@pliva.hr,izvjestaji\@support.pliva.hr") || die "sendmail: $!"; |
23 |
} else { |
24 |
open(HTML,"> /tmp/debug.html") || die "html: $!"; |
25 |
open(MAIL,"> /tmp/debug.mail") || die "sendmail: $!"; |
26 |
} |
27 |
|
28 |
print HTML ' |
29 |
<html> |
30 |
<title>Danas otvoreni raèuni</title> |
31 |
<body bgcolor="#ffffff"> |
32 |
<h1>Danas otvoreni raèuni</h1> |
33 |
'; |
34 |
|
35 |
print MAIL 'From: PlivaNet Core Support Team <pcst@pliva.hr> |
36 |
Subject: otvoreni raèuni za e-mail |
37 |
|
38 |
'; |
39 |
|
40 |
my $dbh = DBI->connect("DBI:Pg:dbname=informatika","","") || die $DBI::errstr; |
41 |
$dbh->do("set datestyle = 'german'") || die $dbh->errstr(); |
42 |
|
43 |
my $sth = $dbh->prepare("select id from last_print order by id desc limit 1,1"); |
44 |
$sth->execute() || die $sth->errstr(); |
45 |
my ($print_from_id) = $sth->fetchrow_array(); |
46 |
|
47 |
$sth = $dbh->prepare("select osoba_id,max(status_tip_id) from status |
48 |
where osoba_id > $print_from_id |
49 |
group by osoba_id |
50 |
") || die $dbh->errstr(); |
51 |
$sth->execute() || die $sth->errstr(); |
52 |
|
53 |
my @osoba_id_za_print; |
54 |
|
55 |
while (my ($osoba_id,$status_tip_id) = $sth->fetchrow_array() ) { |
56 |
if ($status_tip_id == 5 || $status_tip_id == 8 ) { # print/umre¾avanje |
57 |
push @osoba_id_za_print,$osoba_id; |
58 |
} |
59 |
} |
60 |
|
61 |
print MAIL "Otvorenih raèuna: ",$#osoba_id_za_print+1," |
62 |
Datum i vrijeme generiranja izvje¹taja: $currdate |
63 |
|
64 |
Izvje¹taj za ¹tampanje se nalazi na |
65 |
http://support.pliva.hr/$html_file |
66 |
sa potpunim podacima o umre¾avanju, lokaciji raèunala i osobe |
67 |
|
68 |
"; |
69 |
print HTML "Otvorenih raèuna: ",$#osoba_id_za_print+1," |
70 |
<br>Datum i vrijeme generiranja izvje¹taja: $currdate<p>"; |
71 |
|
72 |
$sth = $dbh->prepare(" |
73 |
select osobe.id,ime,prezime,tel,lokacije.naziv, |
74 |
org_jed.naziv,org_pod_jed.pod_naziv, |
75 |
lok_racunala,objekt,kat,soba,z_rac,umrezavanje |
76 |
from osobe |
77 |
where osobe.id in (".join(",",@osoba_id_za_print).") |
78 |
and lokacija_id=lokacije.id and org_jed_id=org_jed.id |
79 |
and org_pod_jed_id=org_pod_jed.pod_id |
80 |
and podesavanje=true |
81 |
order by lokacije.naziv,org_jed.naziv |
82 |
") || die $dbh->errstr(); |
83 |
$sth->execute() || die $sth->errstr(); |
84 |
|
85 |
my $rbr=1; |
86 |
my $lokacija=''; |
87 |
|
88 |
printf MAIL ("%3s %4s %-20s %4s %-30s %-20s\n","rbr","id","ime i prezime","tel","lokacija","org.jed"); |
89 |
print HTML '<table border=1> |
90 |
<tr bgcolor="#e0e0f0"><th>rbr</th><th>id</th><th>ime i prezime</th><th>tel</th><th>lokacija</th><th>org.jed</th></tr> |
91 |
<tr bgcolor="#e0e0f0"><th></th><th></th><th>lokacija raèunala, mre¾a</th><th>¾</th><th colspan=2>objekat / kat / soba</th></tr> |
92 |
'; |
93 |
|
94 |
my $bgcol=''; |
95 |
my $mreza=''; |
96 |
|
97 |
while (my @arr = $sth->fetchrow_array() ) { |
98 |
printf MAIL ("%3s %4s %-20s %4s %-30s %-20s\n",$rbr,$arr[0],"$arr[1] $arr[2]",$arr[3],$arr[4],"$arr[5] $arr[6]"); |
99 |
if ($lokacija ne $arr[4]) { |
100 |
$lokacija=$arr[4]; |
101 |
print HTML "<tr bgcolor=#000000><td colspan=6><font size=+1 color=#ffffff>$lokacija</font></td></tr>"; |
102 |
} |
103 |
print HTML "<tr$bgcol><td>$rbr</td><td>$arr[0]</td><td>$arr[1] $arr[2]</td><td>tel: $arr[3]</td><td><small>$arr[4]</small></td><td>$arr[5]<br>$arr[6]</td></tr>\n"; |
104 |
if ($arr[12] == 0) { |
105 |
$mreza='umre¾eno'; |
106 |
} else { |
107 |
my $rows = $dbh->do("select max(status_tip_id) from status |
108 |
where osoba_id = $arr[0] and status_tip_id=8") || die $dbh->errstr(); |
109 |
if ($rows > 0) { |
110 |
$mreza='<i>umre¾avanje obavljeno</i>'; |
111 |
} else { |
112 |
$mreza='<b>potrebno umre¾avanje</b>'; |
113 |
} |
114 |
} |
115 |
print HTML "<tr$bgcol><td colspan=2> </td><td><small>$arr[7] $mreza</small></a><td><small>¾-$arr[11]</small></td><td colspan=2><small>$arr[8] / $arr[9] / $arr[10]</small></td></tr>\n"; |
116 |
$rbr++; |
117 |
if ($rbr % 2 == 0) { |
118 |
$bgcol=' bgcolor=#eeeeee'; |
119 |
} else { |
120 |
$bgcol=''; |
121 |
} |
122 |
} |
123 |
|
124 |
$sth->finish; |
125 |
|
126 |
print HTML ' |
127 |
</table> |
128 |
</body> |
129 |
'; |
130 |
|
131 |
close(HTML); |
132 |
close(MAIL); |
133 |
|