1 |
dpavlin |
1.1 |
#!/usr/local/bin/perl -w |
2 |
|
|
|
3 |
|
|
use strict; |
4 |
|
|
|
5 |
|
|
use DBI; |
6 |
|
|
use POSIX qw(strftime); |
7 |
|
|
|
8 |
|
|
my $currdate = strftime "%d.%m.%Y %H:%M:%S", localtime; |
9 |
|
|
|
10 |
|
|
my $html_file=$currdate; |
11 |
|
|
$html_file=~s/ /_/g; |
12 |
|
|
$html_file="izvjestaji/$html_file.html"; |
13 |
|
|
|
14 |
|
|
open(HTML,"> $html_file") || die "html: $!"; |
15 |
|
|
open(MAIL,"|/usr/lib/sendmail biljana.gombar\@pliva.hr,izvjestaji\@support.pliva.hr") || die "sendmail: $!"; |
16 |
|
|
|
17 |
|
|
print HTML ' |
18 |
|
|
<html> |
19 |
|
|
<title>Danas otvoreni raèuni</title> |
20 |
|
|
<body bgcolor="#ffffff"> |
21 |
|
|
<h1>Danas otvoreni raèuni</h1> |
22 |
|
|
'; |
23 |
|
|
|
24 |
|
|
print MAIL 'From: PlivaNet Core Support Team <pcst@pliva.hr> |
25 |
|
|
Subject: otvoreni raèuni za e-mail |
26 |
|
|
|
27 |
|
|
'; |
28 |
|
|
|
29 |
|
|
my $dbh = DBI->connect("DBI:Pg:dbname=informatika","","") || die $DBI::errstr; |
30 |
|
|
$dbh->do("set datestyle = 'german'") || die $dbh->errstr(); |
31 |
|
|
|
32 |
|
|
my $sth = $dbh->prepare("select id from last_print order by id desc limit 1,1"); |
33 |
|
|
$sth->execute() || die $sth->errstr(); |
34 |
|
|
my ($print_from_id) = $sth->fetchrow_array(); |
35 |
|
|
|
36 |
|
|
$sth = $dbh->prepare("select osoba_id,max(status_tip_id) from status |
37 |
|
|
where osoba_id > $print_from_id |
38 |
|
|
group by osoba_id |
39 |
|
|
") || die $dbh->errstr(); |
40 |
|
|
$sth->execute() || die $sth->errstr(); |
41 |
|
|
|
42 |
|
|
my @osoba_id_za_print; |
43 |
|
|
|
44 |
|
|
while (my ($osoba_id,$status_tip_id) = $sth->fetchrow_array() ) { |
45 |
|
|
if ($status_tip_id == 5) { # print |
46 |
|
|
push @osoba_id_za_print,$osoba_id; |
47 |
|
|
} |
48 |
|
|
} |
49 |
|
|
|
50 |
|
|
print MAIL "Otvorenih raèuna: ",$#osoba_id_za_print+1," |
51 |
|
|
Datum i vrijeme generiranja izvje¹taja: $currdate |
52 |
|
|
|
53 |
|
|
Izvje¹taj se nalazi i na |
54 |
|
|
http://support.pliva.hr/$html_file |
55 |
|
|
(npr. za ispis na ¹tampaèu) |
56 |
|
|
|
57 |
|
|
"; |
58 |
|
|
print HTML "Otvorenih raèuna: ",$#osoba_id_za_print+1," |
59 |
|
|
<br>Datum i vrijeme generiranja izvje¹taja: $currdate<p>"; |
60 |
|
|
|
61 |
|
|
$sth = $dbh->prepare(" |
62 |
|
|
select osobe.id,ime,prezime,tel,lokacije.naziv, |
63 |
|
|
org_jed.naziv,org_pod_jed.pod_naziv, |
64 |
|
|
lok_racunala,objekt,kat,soba,z_rac |
65 |
|
|
from osobe |
66 |
|
|
where osobe.id in (".join(",",@osoba_id_za_print).") |
67 |
|
|
and lokacija_id=lokacije.id and org_jed_id=org_jed.id |
68 |
|
|
and org_pod_jed_id=org_pod_jed.pod_id |
69 |
|
|
order by lokacije.naziv,org_jed.naziv |
70 |
|
|
") || die $dbh->errstr(); |
71 |
|
|
$sth->execute() || die $sth->errstr(); |
72 |
|
|
|
73 |
|
|
my $rbr=1; |
74 |
|
|
|
75 |
|
|
printf MAIL ("%3s %4s %-20s %4s %-30s %-20s\n","rbr","id","ime i prezime","tel","lokacija","org.jed"); |
76 |
|
|
print HTML '<table border=1> |
77 |
|
|
<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> |
78 |
|
|
'; |
79 |
|
|
|
80 |
|
|
while (my @arr = $sth->fetchrow_array() ) { |
81 |
|
|
printf MAIL ("%3s %4s %-20s %4s %-30s %-20s\n",$rbr,$arr[0],"$arr[1] $arr[2]",$arr[3],$arr[4],"$arr[5] $arr[6]"); |
82 |
|
|
print HTML "<tr><td>$rbr</td><td>$arr[0]</td><td>$arr[1] $arr[2]</td><td>$arr[3]</td><td>$arr[4]</td><td>$arr[5]<br>$arr[6]</td></tr>\n"; |
83 |
|
|
$rbr++; |
84 |
|
|
} |
85 |
|
|
|
86 |
|
|
$sth->finish; |
87 |
|
|
|
88 |
|
|
print HTML ' |
89 |
|
|
</table> |
90 |
|
|
</body> |
91 |
|
|
'; |
92 |
|
|
|
93 |
|
|
close(HTML); |
94 |
|
|
close(MAIL); |