informatika.old/print/izvjestaj.pl

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

use strict;

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

my $debug=0;

use DBI;
use POSIX qw(strftime);

$debug++ if (defined $ARGV[0] && $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 "can't connect";
# 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>";

my $sql="
        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 "nema osoba za izvje¹taj" if (! @osoba_id_za_print);

$sth = $dbh->prepare($sql) || 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.10	my $ic_mail='biljana.gombar@pliva.hr,antonio.gulam@pliva.hr';
6	dpavlin	1.9	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.10	$debug++ if (defined $ARGV[0] && $ARGV[0] eq "-d");
14	dpavlin	1.4
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	dpavlin	1.10	my $dbh = DBI->connect("DBI:Pg:dbname=informatika;${host}${db_user}","","") \|\| die "can't connect";
59			# 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	dpavlin	1.10	my $sql="
92	dpavlin	1.1	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	dpavlin	1.10	";
104
105			die "nema osoba za izvje¹taj" if (! @osoba_id_za_print);
106
107			$sth = $dbh->prepare($sql) \|\| die $dbh->errstr();
108	dpavlin	1.1	$sth->execute() \|\| die $sth->errstr();
109
110			my $rbr=1;
111	dpavlin	1.3	my $lokacija='';
112	dpavlin	1.1
113			printf MAIL ("%3s %4s %-20s %4s %-30s %-20s\n","rbr","id","ime i prezime","tel","lokacija","org.jed");
114			print HTML '<table border=1>
115			<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>
116	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>
117	dpavlin	1.1	';
118
119	dpavlin	1.3	my $bgcol='';
120			my $mreza='';
121	dpavlin	1.5	my @za_ic;
122	dpavlin	1.6	my %mail4ko;
123	dpavlin	1.3
124	dpavlin	1.1	while (my @arr = $sth->fetchrow_array() ) {
125	dpavlin	1.5	push @za_ic,$arr[0];
126	dpavlin	1.6	$mail4ko{$arr[13]}.="\t$arr[1] $arr[2]";
127			$mail4ko{$arr[13]}.=" (bez pode¹avanja)" if ($arr[14] == 0);
128	dpavlin	1.5
129	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]");
130	dpavlin	1.3	if ($lokacija ne $arr[4]) {
131			$lokacija=$arr[4];
132			print HTML "<tr bgcolor=#000000><td colspan=6><font size=+1 color=#ffffff>$lokacija</font></td></tr>";
133			}
134	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";
135	dpavlin	1.3	if ($arr[12] == 0) {
136			$mreza='umre¾eno';
137			} else {
138	dpavlin	1.7	my $sth2 = $dbh->prepare("select count(status_tip_id) from status
139			where osoba_id = $arr[0] and status_tip_id=8") \|\|
140			die $dbh->errstr();
141			$sth2->execute() \|\| die $sth2->errstr();
142			my ($nr_of_status8) = $sth2->fetchrow_array();
143
144			if ($nr_of_status8 > 0) {
145	dpavlin	1.4	$mreza='<i>umre¾avanje obavljeno</i>';
146			} else {
147			$mreza='<b>potrebno umre¾avanje</b>';
148			}
149	dpavlin	1.3	}
150			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";
151	dpavlin	1.1	$rbr++;
152	dpavlin	1.3	if ($rbr % 2 == 0) {
153			$bgcol=' bgcolor=#eeeeee';
154			} else {
155			$bgcol='';
156			}
157	dpavlin	1.1	}
158
159
160			print HTML '
161			</table>
162			</body>
163			';
164
165			close(HTML);
166			close(MAIL);
167	dpavlin	1.6
168			foreach my $email_ko (keys %mail4ko) {
169			if (! $debug) {
170			open(MAIL,"\|/usr/lib/sendmail $email_ko") \|\| die "sendmail: $!";
171			} else {
172			open(MAIL,">> /tmp/debug.komail") \|\| die "sendmail: $!";
173			}
174
175			print MAIL 'From: PlivaNet Core Support Team <pcst@pliva.hr>
176			Subject: otvoreni raèuni za e-mail
177
178			Obavje¹tavamo Vas da su otvoreni korisnièki raèuni na intranet poslu¾itelju
179			za slijedeæe osobe:
180			';
181			foreach my $ime_pr (split(/\t/,$mail4ko{$email_ko})) {
182			print MAIL "\t$ime_pr\n";
183			}
184			print MAIL '
185			Va¹i korisnici dobiti æe pristupno ime (login) i lozinku (password) internom
186			po¹tom, a djelatnici odjela Informatika IC æe izvr¹iti pode¹avanje korisnièkog
187			raèunala.';
188
189			close(MAIL);
190			}
191	dpavlin	1.2
192	dpavlin	1.9	#print "Printanje zahtjeva za print za IC:\n./print.pl ",join(",",@za_ic),"\n";
193			foreach my $id (@za_ic) {
194			if (! $debug) {
195			$dbh->do("insert into izvjestaji (datum,osoba_id)
196			values ('$currdate',$id)") \|\| die $dbh->errstr();
197			}
198			}
199
200			$sth->finish; undef $sth;
201			$dbh->disconnect;
202