informatika.old/print/izvjestaj.pl

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

use strict;

my $ic_mail='biljana.gombar@pliva.hr';
my $mreze_mail='davorin.koprivnjak@pliva.hr';

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 "%Y-%m-%d %H:%M:%S", localtime;

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

if (! $debug) {
open(HTML,"> $html_file") || die "html: $!";
open(MAIL,"|/usr/lib/sendmail $ic_mail,$mreze_mail,izvjestaji\@support.pliva.hr") || die "sendmail: $!";
} else {
open(HTML,"> /tmp/debug.html") || die "html: $!";
open(MAIL,"> /tmp/debug.mail") || die "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 "
Datum i vrijeme generiranja izvje¹taja: $currdate

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

";
print HTML "
<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,
                kontakt_osobe.e_mail,podesavanje
        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
                and kontakt_osobe.id=kontakt_osoba_id
        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;
my %mail4ko;

while (my @arr = $sth->fetchrow_array() ) {
        push @za_ic,$arr[0];
        $mail4ko{$arr[13]}.="\t$arr[1] $arr[2]";
        $mail4ko{$arr[13]}.=" (bez pode¹avanja)" if ($arr[14] == 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><a href=\"http://support.pliva.hr/obavijest.php?id=$arr[0]\">$arr[0]</a></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 $sth2 = $dbh->prepare("select count(status_tip_id) from status
                        where osoba_id = $arr[0] and status_tip_id=8") ||
                                die $dbh->errstr();
                $sth2->execute() || die $sth2->errstr();
                my ($nr_of_status8) = $sth2->fetchrow_array();
                        
                if ($nr_of_status8 > 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='';
        }
}


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

close(HTML);
close(MAIL);

foreach my $email_ko (keys %mail4ko) {
        if (! $debug) {
                open(MAIL,"|/usr/lib/sendmail $email_ko") || die "sendmail: $!";
        } else {
                open(MAIL,">> /tmp/debug.komail") || die "sendmail: $!";
        }

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

Obavje¹tavamo Vas da su otvoreni korisnièki raèuni na intranet poslu¾itelju
za slijedeæe osobe:
';
        foreach my $ime_pr (split(/\t/,$mail4ko{$email_ko})) {
                print MAIL "\t$ime_pr\n";
        }
        print MAIL '
Va¹i korisnici dobiti æe pristupno ime (login) i lozinku (password) internom
po¹tom, a djelatnici odjela Informatika IC æe izvr¹iti pode¹avanje korisnièkog
raèunala.';

        close(MAIL);
}

#print "Printanje zahtjeva za print za IC:\n./print.pl ",join(",",@za_ic),"\n";
foreach my $id (@za_ic) {
        if (! $debug) {
                $dbh->do("insert into izvjestaji (datum,osoba_id)
                        values ('$currdate',$id)") || die $dbh->errstr();
        }
}

$sth->finish; undef $sth;
$dbh->disconnect;

1	dpavlin	1.1	#!/usr/local/bin/perl -w
2
3			use strict;
4
5	dpavlin	1.9	my $ic_mail='biljana.gombar@pliva.hr';
6			my $mreze_mail='davorin.koprivnjak@pliva.hr';
7
8	dpavlin	1.3	my $debug=0;
9	dpavlin	1.2
10	dpavlin	1.1	use DBI;
11			use POSIX qw(strftime);
12
13	dpavlin	1.4	$debug++ if ($ARGV[0] eq "-d");
14
15	dpavlin	1.2	print STDERR "Debug mod: output je u /tmp/debug*\n" if ($debug);
16
17	dpavlin	1.9	my $currdate = strftime "%Y-%m-%d %H:%M:%S", localtime;
18	dpavlin	1.1
19			my $html_file=$currdate;
20			$html_file=~s/ /_/g;
21			$html_file="izvjestaji/$html_file.html";
22	dpavlin	1.9	my $html_date=$currdate;
23			$html_date=~s/ /%20/g;
24	dpavlin	1.1
25	dpavlin	1.2	if (! $debug) {
26	dpavlin	1.1	open(HTML,"> $html_file") \|\| die "html: $!";
27	dpavlin	1.9	open(MAIL,"\|/usr/lib/sendmail $ic_mail,$mreze_mail,izvjestaji\@support.pliva.hr") \|\| die "sendmail: $!";
28	dpavlin	1.2	} else {
29	dpavlin	1.6	open(HTML,"> /tmp/debug.html") \|\| die "html: $!";
30			open(MAIL,"> /tmp/debug.mail") \|\| die "sendmail: $!";
31	dpavlin	1.2	}
32	dpavlin	1.1
33			print HTML '
34			<html>
35			<title>Danas otvoreni raèuni</title>
36			<body bgcolor="#ffffff">
37			<h1>Danas otvoreni raèuni</h1>
38			';
39
40			print MAIL 'From: PlivaNet Core Support Team <pcst@pliva.hr>
41			Subject: otvoreni raèuni za e-mail
42
43			';
44
45	dpavlin	1.5	my $host = "host=support.pliva.hr;";
46
47			open(WHO,"who -m \|") \|\| die "who am i? $!";
48			my $db_user=<WHO>;
49			$db_user=~s/[^!]+!//;
50			close(WHO);
51			if (! defined($db_user)) {
52			$db_user="user=dpavlin;"; $host=""; # works on localhost
53			} else {
54			$db_user=~s/\s.+$//g;
55			$db_user="user=$db_user;";
56			}
57
58
59			my $dbh = DBI->connect("DBI:Pg:dbname=informatika;${host}${db_user}","","") \|\| die $DBI::errstr;
60	dpavlin	1.1	$dbh->do("set datestyle = 'german'") \|\| die $dbh->errstr();
61
62			my $sth = $dbh->prepare("select id from last_print order by id desc limit 1,1");
63			$sth->execute() \|\| die $sth->errstr();
64			my ($print_from_id) = $sth->fetchrow_array();
65
66			$sth = $dbh->prepare("select osoba_id,max(status_tip_id) from status
67			where osoba_id > $print_from_id
68			group by osoba_id
69			") \|\| die $dbh->errstr();
70			$sth->execute() \|\| die $sth->errstr();
71
72			my @osoba_id_za_print;
73
74			while (my ($osoba_id,$status_tip_id) = $sth->fetchrow_array() ) {
75	dpavlin	1.4	if ($status_tip_id == 5 \|\| $status_tip_id == 8 ) { # print/umre¾avanje
76	dpavlin	1.1	push @osoba_id_za_print,$osoba_id;
77			}
78			}
79
80	dpavlin	1.7	print MAIL "
81	dpavlin	1.1	Datum i vrijeme generiranja izvje¹taja: $currdate
82
83	dpavlin	1.3	Izvje¹taj za ¹tampanje se nalazi na
84	dpavlin	1.9	http://support.pliva.hr/ic.php?datum=$html_date
85	dpavlin	1.3	sa potpunim podacima o umre¾avanju, lokaciji raèunala i osobe
86	dpavlin	1.1
87			";
88	dpavlin	1.7	print HTML "
89	dpavlin	1.1	<br>Datum i vrijeme generiranja izvje¹taja: $currdate<p>";
90
91			$sth = $dbh->prepare("
92			select osobe.id,ime,prezime,tel,lokacije.naziv,
93			org_jed.naziv,org_pod_jed.pod_naziv,
94	dpavlin	1.6	lok_racunala,objekt,kat,soba,z_rac,umrezavanje,
95			kontakt_osobe.e_mail,podesavanje
96	dpavlin	1.1	from osobe
97			where osobe.id in (".join(",",@osoba_id_za_print).")
98			and lokacija_id=lokacije.id and org_jed_id=org_jed.id
99			and org_pod_jed_id=org_pod_jed.pod_id
100	dpavlin	1.3	and podesavanje=true
101	dpavlin	1.6	and kontakt_osobe.id=kontakt_osoba_id
102	dpavlin	1.1	order by lokacije.naziv,org_jed.naziv
103			") \|\| die $dbh->errstr();
104			$sth->execute() \|\| die $sth->errstr();
105
106			my $rbr=1;
107	dpavlin	1.3	my $lokacija='';
108	dpavlin	1.1
109			printf MAIL ("%3s %4s %-20s %4s %-30s %-20s\n","rbr","id","ime i prezime","tel","lokacija","org.jed");
110			print HTML '<table border=1>
111			<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>
112	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>
113	dpavlin	1.1	';
114
115	dpavlin	1.3	my $bgcol='';
116			my $mreza='';
117	dpavlin	1.5	my @za_ic;
118	dpavlin	1.6	my %mail4ko;
119	dpavlin	1.3
120	dpavlin	1.1	while (my @arr = $sth->fetchrow_array() ) {
121	dpavlin	1.5	push @za_ic,$arr[0];
122	dpavlin	1.6	$mail4ko{$arr[13]}.="\t$arr[1] $arr[2]";
123			$mail4ko{$arr[13]}.=" (bez pode¹avanja)" if ($arr[14] == 0);
124	dpavlin	1.5
125	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]");
126	dpavlin	1.3	if ($lokacija ne $arr[4]) {
127			$lokacija=$arr[4];
128			print HTML "<tr bgcolor=#000000><td colspan=6><font size=+1 color=#ffffff>$lokacija</font></td></tr>";
129			}
130	dpavlin	1.8	print HTML "<tr$bgcol><td>$rbr</td><td><a href=\"http://support.pliva.hr/obavijest.php?id=$arr[0]\">$arr[0]</a></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";
131	dpavlin	1.3	if ($arr[12] == 0) {
132			$mreza='umre¾eno';
133			} else {
134	dpavlin	1.7	my $sth2 = $dbh->prepare("select count(status_tip_id) from status
135			where osoba_id = $arr[0] and status_tip_id=8") \|\|
136			die $dbh->errstr();
137			$sth2->execute() \|\| die $sth2->errstr();
138			my ($nr_of_status8) = $sth2->fetchrow_array();
139
140			if ($nr_of_status8 > 0) {
141	dpavlin	1.4	$mreza='<i>umre¾avanje obavljeno</i>';
142			} else {
143			$mreza='<b>potrebno umre¾avanje</b>';
144			}
145	dpavlin	1.3	}
146			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";
147	dpavlin	1.1	$rbr++;
148	dpavlin	1.3	if ($rbr % 2 == 0) {
149			$bgcol=' bgcolor=#eeeeee';
150			} else {
151			$bgcol='';
152			}
153	dpavlin	1.1	}
154
155
156			print HTML '
157			</table>
158			</body>
159			';
160
161			close(HTML);
162			close(MAIL);
163	dpavlin	1.6
164			foreach my $email_ko (keys %mail4ko) {
165			if (! $debug) {
166			open(MAIL,"\|/usr/lib/sendmail $email_ko") \|\| die "sendmail: $!";
167			} else {
168			open(MAIL,">> /tmp/debug.komail") \|\| die "sendmail: $!";
169			}
170
171			print MAIL 'From: PlivaNet Core Support Team <pcst@pliva.hr>
172			Subject: otvoreni raèuni za e-mail
173
174			Obavje¹tavamo Vas da su otvoreni korisnièki raèuni na intranet poslu¾itelju
175			za slijedeæe osobe:
176			';
177			foreach my $ime_pr (split(/\t/,$mail4ko{$email_ko})) {
178			print MAIL "\t$ime_pr\n";
179			}
180			print MAIL '
181			Va¹i korisnici dobiti æe pristupno ime (login) i lozinku (password) internom
182			po¹tom, a djelatnici odjela Informatika IC æe izvr¹iti pode¹avanje korisnièkog
183			raèunala.';
184
185			close(MAIL);
186			}
187	dpavlin	1.2
188	dpavlin	1.9	#print "Printanje zahtjeva za print za IC:\n./print.pl ",join(",",@za_ic),"\n";
189			foreach my $id (@za_ic) {
190			if (! $debug) {
191			$dbh->do("insert into izvjestaji (datum,osoba_id)
192			values ('$currdate',$id)") \|\| die $dbh->errstr();
193			}
194			}
195
196			$sth->finish; undef $sth;
197			$dbh->disconnect;
198