informatika.old/print/izvjestaj.pl

#!/usr/local/bin/perl -w

use strict;

my $debug=0;

use DBI;
use POSIX qw(strftime);

$debug++ if ($ARGV[0] eq "-d");

print STDERR "Debug mod: output je u /tmp/debug*\n" if ($debug);

my $currdate = strftime "%d.%m.%Y %H:%M:%S", localtime;

my $html_file=$currdate;
$html_file=~s/ /_/g;
$html_file="izvjestaji/$html_file.html";

if (! $debug) {
open(HTML,"> $html_file") || die "html: $!";
open(MAIL,"|/usr/lib/sendmail biljana.gombar\@pliva.hr,izvjestaji\@support.pliva.hr") || die "sendmail: $!";
} else {
open(HTML,"> /tmp/debug.html") || warn "html: $!";
open(MAIL,"> /tmp/debug.mail") || warn "sendmail: $!";
}

print HTML '
<html>
<title>Danas otvoreni raèuni</title>
<body bgcolor="#ffffff">
<h1>Danas otvoreni raèuni</h1>
';

print MAIL 'From: PlivaNet Core Support Team <pcst@pliva.hr>
Subject: otvoreni raèuni za e-mail

';

my $host = "host=support.pliva.hr;";

open(WHO,"who -m |") || die "who am i? $!";
my $db_user=<WHO>;
$db_user=~s/[^!]+!//;
close(WHO);
if (! defined($db_user)) {
        $db_user="user=dpavlin;"; $host="";     # works on localhost
} else {
        $db_user=~s/\s.+$//g;
        $db_user="user=$db_user;";
}


my $dbh = DBI->connect("DBI:Pg:dbname=informatika;${host}${db_user}","","") || die $DBI::errstr;
$dbh->do("set datestyle = 'german'") || die $dbh->errstr();

my $sth = $dbh->prepare("select id from last_print order by id desc limit 1,1");
$sth->execute() || die $sth->errstr();
my ($print_from_id) = $sth->fetchrow_array();

$sth = $dbh->prepare("select osoba_id,max(status_tip_id) from status
        where osoba_id > $print_from_id
        group by osoba_id
        ") || die $dbh->errstr();
$sth->execute() || die $sth->errstr();

my @osoba_id_za_print;

while (my ($osoba_id,$status_tip_id) = $sth->fetchrow_array() ) {
        if ($status_tip_id == 5 || $status_tip_id == 8 ) { # print/umre¾avanje
                push @osoba_id_za_print,$osoba_id;
        }
}

print MAIL "Otvorenih raèuna: ",$#osoba_id_za_print+1,"
Datum i vrijeme generiranja izvje¹taja: $currdate

Izvje¹taj za ¹tampanje se nalazi na
http://support.pliva.hr/$html_file
sa potpunim podacima o umre¾avanju, lokaciji raèunala i osobe

";
print HTML "Otvorenih raèuna: ",$#osoba_id_za_print+1,"
<br>Datum i vrijeme generiranja izvje¹taja: $currdate<p>";

$sth = $dbh->prepare("
        select osobe.id,ime,prezime,tel,lokacije.naziv,
                org_jed.naziv,org_pod_jed.pod_naziv,
                lok_racunala,objekt,kat,soba,z_rac,umrezavanje
        from osobe
        where osobe.id in (".join(",",@osoba_id_za_print).")
                and lokacija_id=lokacije.id and org_jed_id=org_jed.id
                and org_pod_jed_id=org_pod_jed.pod_id
                and podesavanje=true
        order by lokacije.naziv,org_jed.naziv
        ") || die $dbh->errstr();
$sth->execute() || die $sth->errstr();

my $rbr=1;
my $lokacija='';

printf MAIL ("%3s %4s %-20s %4s %-30s %-20s\n","rbr","id","ime i prezime","tel","lokacija","org.jed");
print HTML '<table border=1>
<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>
<tr bgcolor="#e0e0f0"><th></th><th></th><th>lokacija raèunala, mre¾a</th><th>¾</th><th colspan=2>objekat / kat / soba</th></tr>
';

my $bgcol='';
my $mreza='';
my @za_ic;

while (my @arr = $sth->fetchrow_array() ) {
        push @za_ic,$arr[0];

        printf MAIL ("%3s %4s %-20s %4s %-30s %-20s\n",$rbr,$arr[0],"$arr[1] $arr[2]",$arr[3],$arr[4],"$arr[5] $arr[6]");
        if ($lokacija ne $arr[4]) {
                $lokacija=$arr[4];
                print HTML "<tr bgcolor=#000000><td colspan=6><font size=+1 color=#ffffff>$lokacija</font></td></tr>";
        }
        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";
        if ($arr[12] == 0) {
                $mreza='umre¾eno';
        } else {
                my $rows = $dbh->do("select max(status_tip_id) from status
                        where osoba_id = $arr[0] and status_tip_id=8") || die $dbh->errstr();
                if ($rows > 0) {
                        $mreza='<i>umre¾avanje obavljeno</i>';
                } else {
                        $mreza='<b>potrebno umre¾avanje</b>';
                }
        }
        print HTML "<tr$bgcol><td colspan=2>&nbsp;</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";
        $rbr++;
        if ($rbr % 2 == 0) {
                $bgcol=' bgcolor=#eeeeee';
        } else {
                $bgcol='';
        }
}

$sth->finish;

print HTML '
</table>
</body>
';

close(HTML);
close(MAIL);

print "Printanje zahtjeva za print za IC:\n./print.pl ",join(",",@za_ic),"\n";
1	dpavlin	1.1	#!/usr/local/bin/perl -w
2
3			use strict;
4
5	dpavlin	1.3	my $debug=0;
6	dpavlin	1.2
7	dpavlin	1.1	use DBI;
8			use POSIX qw(strftime);
9
10	dpavlin	1.4	$debug++ if ($ARGV[0] eq "-d");
11
12	dpavlin	1.2	print STDERR "Debug mod: output je u /tmp/debug*\n" if ($debug);
13
14	dpavlin	1.1	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	dpavlin	1.2	if (! $debug) {
21	dpavlin	1.1	open(HTML,"> $html_file") \|\| die "html: $!";
22			open(MAIL,"\|/usr/lib/sendmail biljana.gombar\@pliva.hr,izvjestaji\@support.pliva.hr") \|\| die "sendmail: $!";
23	dpavlin	1.2	} else {
24	dpavlin	1.5	open(HTML,"> /tmp/debug.html") \|\| warn "html: $!";
25			open(MAIL,"> /tmp/debug.mail") \|\| warn "sendmail: $!";
26	dpavlin	1.2	}
27	dpavlin	1.1
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	dpavlin	1.5	my $host = "host=support.pliva.hr;";
41
42			open(WHO,"who -m \|") \|\| die "who am i? $!";
43			my $db_user=<WHO>;
44			$db_user=~s/[^!]+!//;
45			close(WHO);
46			if (! defined($db_user)) {
47			$db_user="user=dpavlin;"; $host=""; # works on localhost
48			} else {
49			$db_user=~s/\s.+$//g;
50			$db_user="user=$db_user;";
51			}
52
53
54			my $dbh = DBI->connect("DBI:Pg:dbname=informatika;${host}${db_user}","","") \|\| die $DBI::errstr;
55	dpavlin	1.1	$dbh->do("set datestyle = 'german'") \|\| die $dbh->errstr();
56
57			my $sth = $dbh->prepare("select id from last_print order by id desc limit 1,1");
58			$sth->execute() \|\| die $sth->errstr();
59			my ($print_from_id) = $sth->fetchrow_array();
60
61			$sth = $dbh->prepare("select osoba_id,max(status_tip_id) from status
62			where osoba_id > $print_from_id
63			group by osoba_id
64			") \|\| die $dbh->errstr();
65			$sth->execute() \|\| die $sth->errstr();
66
67			my @osoba_id_za_print;
68
69			while (my ($osoba_id,$status_tip_id) = $sth->fetchrow_array() ) {
70	dpavlin	1.4	if ($status_tip_id == 5 \|\| $status_tip_id == 8 ) { # print/umre¾avanje
71	dpavlin	1.1	push @osoba_id_za_print,$osoba_id;
72			}
73			}
74
75			print MAIL "Otvorenih raèuna: ",$#osoba_id_za_print+1,"
76			Datum i vrijeme generiranja izvje¹taja: $currdate
77
78	dpavlin	1.3	Izvje¹taj za ¹tampanje se nalazi na
79	dpavlin	1.1	http://support.pliva.hr/$html_file
80	dpavlin	1.3	sa potpunim podacima o umre¾avanju, lokaciji raèunala i osobe
81	dpavlin	1.1
82			";
83			print HTML "Otvorenih raèuna: ",$#osoba_id_za_print+1,"
84			<br>Datum i vrijeme generiranja izvje¹taja: $currdate<p>";
85
86			$sth = $dbh->prepare("
87			select osobe.id,ime,prezime,tel,lokacije.naziv,
88			org_jed.naziv,org_pod_jed.pod_naziv,
89	dpavlin	1.3	lok_racunala,objekt,kat,soba,z_rac,umrezavanje
90	dpavlin	1.1	from osobe
91			where osobe.id in (".join(",",@osoba_id_za_print).")
92			and lokacija_id=lokacije.id and org_jed_id=org_jed.id
93			and org_pod_jed_id=org_pod_jed.pod_id
94	dpavlin	1.3	and podesavanje=true
95	dpavlin	1.1	order by lokacije.naziv,org_jed.naziv
96			") \|\| die $dbh->errstr();
97			$sth->execute() \|\| die $sth->errstr();
98
99			my $rbr=1;
100	dpavlin	1.3	my $lokacija='';
101	dpavlin	1.1
102			printf MAIL ("%3s %4s %-20s %4s %-30s %-20s\n","rbr","id","ime i prezime","tel","lokacija","org.jed");
103			print HTML '<table border=1>
104			<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>
105	dpavlin	1.3	<tr bgcolor="#e0e0f0"><th></th><th></th><th>lokacija raèunala, mre¾a</th><th>¾</th><th colspan=2>objekat / kat / soba</th></tr>
106	dpavlin	1.1	';
107
108	dpavlin	1.3	my $bgcol='';
109			my $mreza='';
110	dpavlin	1.5	my @za_ic;
111	dpavlin	1.3
112	dpavlin	1.1	while (my @arr = $sth->fetchrow_array() ) {
113	dpavlin	1.5	push @za_ic,$arr[0];
114
115	dpavlin	1.1	printf MAIL ("%3s %4s %-20s %4s %-30s %-20s\n",$rbr,$arr[0],"$arr[1] $arr[2]",$arr[3],$arr[4],"$arr[5] $arr[6]");
116	dpavlin	1.3	if ($lokacija ne $arr[4]) {
117			$lokacija=$arr[4];
118			print HTML "<tr bgcolor=#000000><td colspan=6><font size=+1 color=#ffffff>$lokacija</font></td></tr>";
119			}
120			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";
121			if ($arr[12] == 0) {
122			$mreza='umre¾eno';
123			} else {
124	dpavlin	1.4	my $rows = $dbh->do("select max(status_tip_id) from status
125			where osoba_id = $arr[0] and status_tip_id=8") \|\| die $dbh->errstr();
126			if ($rows > 0) {
127			$mreza='<i>umre¾avanje obavljeno</i>';
128			} else {
129			$mreza='<b>potrebno umre¾avanje</b>';
130			}
131	dpavlin	1.3	}
132			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";
133	dpavlin	1.1	$rbr++;
134	dpavlin	1.3	if ($rbr % 2 == 0) {
135			$bgcol=' bgcolor=#eeeeee';
136			} else {
137			$bgcol='';
138			}
139	dpavlin	1.1	}
140
141			$sth->finish;
142
143			print HTML '
144			</table>
145			</body>
146			';
147
148			close(HTML);
149			close(MAIL);
150	dpavlin	1.2
151	dpavlin	1.5	print "Printanje zahtjeva za print za IC:\n./print.pl ",join(",",@za_ic),"\n";