informatika.old/print/print.pl

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

# ispisuje korisnièke obavijesti iz baze
#
# upotreba: print.pl [osoba_id]
#
# ako se ne navede osoba_id onda se printaju *SVI* zahtjevi koji jo¹
# nisu isprintani

# 2000-03-16 Dobrica Pavlinusic <dpavlin@pliva.hr>
# 2000-03-17 DbP dodatak printanja samo jedne osobe
# 2000-03-20 DbP last_print tablica

use strict;

use DBI;

my $debug=0;

my $print_only_id;

if (defined($ARGV[0])) {
        $print_only_id = $ARGV[0];
        print "Ispisujem samo podatke za osobu id: $print_only_id\n";
} else {
        undef $print_only_id;
}

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

my $sth;

open(WHO,"who am i |") || die "who am i? $!";
my $db_user=<WHO>;
chomp $db_user;
$db_user=~s/\s.+$//g;   # OSF obri¹i sve iza logina
$db_user=~s/^[^!]+!//g; # Linux obri¹i hostname
close(WHO);
if ($db_user eq "") {
        open(ID,"id |") || die "id: $!";
        my $foo=<ID>; chomp $foo;
        if ($foo=~/uid=\d+\((\w+)\)/) {
                $db_user=$1;
        } else {
                die "can't locate who is local user!";
        }
}

open(HOSTNAME,"hostname |") || die "hostname: $!";
my $hostname=<HOSTNAME>;
chomp $hostname;
close(HOSTNAME);

$sth = $dbh->prepare("select ko_id from unix2ko where login='$db_user' and host='$hostname'") || die $dbh->errstr();
$sth->execute() || die $sth->errstr();
my ($ko_id)=$sth->fetchrow_array;
die "Ne mogu naæi odgovarajuæu kontakt osobu u tablici unix2ko!\n\tlogin=$db_user host=$hostname\n" if (!defined($ko_id));

print "kontakt_osoba_id (koja je pokrenula skriptu): $ko_id\n" if ($debug);

#$sth = $dbh->prepare("select id from kontakt_osobe where e_mail like '%hana%'") || die $dbh->errstr();
#$sth->execute() || die $sth->errstr();
#($kontakt_osoba_id) = $sth->fetchrow_array();

$sth = $dbh->prepare("select max(id) from last_print") || die $dbh->errstr();
$sth->execute() || die $sth->errstr();
my ($last_print_id) = $sth->fetchrow_array();

my $sql;

if (! defined($print_only_id)) {
        $sql="select osoba_id,max(status_tip_id) from status where osoba_id > $last_print_id group by osoba_id";
} else {
        $sql="select osoba_id,status_tip_id from status where osoba_id in ($print_only_id) and status_tip_id=3";
}

$sth = $dbh->prepare("$sql") || die "$sql ",$dbh->errstr();
$sth->execute() || die "$sql ",$sth->errstr();

#my $out_tex="out.tex";
my $out_tex="out-$$.tex";

open(IN,"obavjest.tex") || die "obavjest.tex: $!";
open(OUT,"> $out_tex") || die "$out_tex: $!";
while(<IN>) {
        print OUT $_;
        last if (/^%--begin_of_page--/);
}
my $begin_pos=tell(IN);

my @osoba_id_za_print;
my $max_osoba_id = 0;

while (my ($osoba_id,$status_tip_id) = $sth->fetchrow_array() ) {
        if ($status_tip_id == 3) {      # 3==otvoren (zadnji status)
                push @osoba_id_za_print,$osoba_id;
                $max_osoba_id = $osoba_id if ($osoba_id > $max_osoba_id);
        }
}

print "Raèuna za insprintati: ",$#osoba_id_za_print+1,"\n";
exit if ($#osoba_id_za_print+1 == 0);
my $server=3;
$sth = $dbh->prepare("
        select osobe.id,ime,prezime,tel,lokacije.naziv,
                lok_racunala,login,passwd,alias,sifra,
                objekt,kat,soba,z_rac,kontakt_osoba_id,umrezavanje
        from osobe,racuni,e_mail
        where osobe.id in (".join(",",@osoba_id_za_print).")
                and lokacija_id=lokacije.id 
                and racuni.osoba_id=osobe.id and racuni.server_id=$server
                and e_mail.osoba_id=osobe.id
        ") || die $dbh->errstr();
$sth->execute() || die $sth->errstr();


while (my @arr = $sth->fetchrow_array() ) {
        print join("|",@arr),"\n";

        if (!$debug) {
                $dbh->do("insert into status (osoba_id,kontakt_osoba_id,datum,status_tip_id) values ($arr[0],$ko_id,'now'::datetime,5)") || die $dbh->errstr();
        }

        my $sth2 = $dbh->prepare("select naziv,mt from sap_osobe,sap_oj where sap_oj.sifra_oj = sap_oj and sap_sifra = $arr[11]");
        $sth2->execute() || die $sth2->errstr();
        my ($sap_org_jed,$mt) = $sth2->fetchrow_array();

        seek(IN,$begin_pos,0);
        while(<IN>) {
                s/--id--/$arr[0]/g;
                $arr[0]=~s/^\s+//g;
                $arr[0]=~s/\s+$//g;
                s/--ime--/$arr[1]/g;

                $arr[1]=~s/^\s+//g;
                $arr[1]=~s/\s+$//g;
                s/--prezime--/$arr[2]/g;
                s/--tel--/$arr[3]/g;
                s/--lok--/$arr[4]/g;
                s/--orgjed--/$sap_org_jed/g;
                s/--mt--/$mt/g;
                s/--lokrac--/$arr[7]/g;
                s/--login--/$arr[8]/g;


                # ovo mora iæi dva puta da bi pretvorio sve znakove
                # koji LaTeX-u imaju specijalno znaèenje
                $arr[9]=~ s/([^\\])([%&\$#])/$1\\$2/g;
                $arr[9]=~ s/([^\\])([%&\$#])/$1\\$2/g;
                $arr[9]=~ s/^([%&\$#])/\\$1/g;

                s/--passwd--/$arr[9]/g;
                
                $arr[10]=~s/^\s+//g;
                $arr[10]=~s/\s+$//g;
                s/--email--/$arr[10]/g;

                $arr[11]=~s/^\s+//g;
                $arr[11]=~s/\s+$//g;
                s/--sifra--/$arr[11]/g;
        
                s/--obj--/$arr[12]/g;
                s/--kat--/$arr[13]/g;
                s/--soba--/$arr[14]/g;
                s/--zrac--/$arr[15]/g;
                my $ean=sprintf("2%06d%05d",$arr[11],$arr[0]);
                s/--ean--/$ean/g;
                
                if ($arr[17] == 1) {    
                        s/--umrezavanje--/\\bf{Potrebno je umre¾avanje}/gi;
                } else {
                        s/--umrezavanje--/{\\small Raèunalo je umre¾eno}/gi;
                }
        
                print OUT $_;

                if (/^%--insert_status--/) {
                        my $sth2 = $dbh->prepare("select
                                datum,status_tip.opis,
                                kontakt_osobe.ime||' '||kontakt_osobe.prezime
                                from status,status_tip
                                where status_tip_id=status_tip.id
                                        and kontakt_osobe.id=kontakt_osoba_id
                                        and status.osoba_id=$arr[0]
                                order by datum
                        ") || die $dbh->errstr();
                        $sth2->execute() || die $sth2->errstr();
                        while (my @arr2 = $sth2->fetchrow_array() ) {
                                print OUT join(" & ",@arr2)," \\\\ \n";
                        }
                }
                last if (/^%--end_of_page--/);
        }
        print OUT "\\newpage\n";

}

$dbh->do("insert into last_print values ('now'::datetime,$max_osoba_id)") if (!$print_only_id && !$debug);

$sth->finish;

while(<IN>) {
        print OUT $_;
}
close(OUT);
close(IN);

my $out_no_ext=$out_tex;
$out_no_ext=~s/\.tex//;

my $path=$0;
$path=~s/\/[^\/]+//g;

if (!$debug) {
        system "$path/codean.pl $out_no_ext.tex $out_no_ext.tex2 && mv $out_no_ext.tex2 $out_no_ext.tex && latex $out_no_ext.tex && dvips $out_no_ext.dvi && lpr $out_no_ext.ps";
} else {
        system "$path/codean.pl $out_no_ext.tex $out_no_ext.tex2 && mv $out_no_ext.tex2 $out_no_ext.tex && latex $out_no_ext.tex && dvips $out_no_ext.dvi";

}
1	dpavlin	1.1	#!/usr/local/bin/perl -w
2
3			# ispisuje korisnièke obavijesti iz baze
4			#
5			# upotreba: print.pl [osoba_id]
6			#
7			# ako se ne navede osoba_id onda se printaju SVI zahtjevi koji jo¹
8			# nisu isprintani
9
10			# 2000-03-16 Dobrica Pavlinusic <dpavlin@pliva.hr>
11			# 2000-03-17 DbP dodatak printanja samo jedne osobe
12			# 2000-03-20 DbP last_print tablica
13
14			use strict;
15
16			use DBI;
17
18			my $debug=0;
19
20			my $print_only_id;
21
22			if (defined($ARGV[0])) {
23			$print_only_id = $ARGV[0];
24			print "Ispisujem samo podatke za osobu id: $print_only_id\n";
25			} else {
26			undef $print_only_id;
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;
33
34	dpavlin	1.4	open(WHO,"who am i \|") \|\| die "who am i? $!";
35			my $db_user=<WHO>;
36			chomp $db_user;
37	dpavlin	1.5	$db_user=~s/\s.+$//g; # OSF obri¹i sve iza logina
38			$db_user=~s/^[^!]+!//g; # Linux obri¹i hostname
39	dpavlin	1.4	close(WHO);
40	dpavlin	1.7	if ($db_user eq "") {
41			open(ID,"id \|") \|\| die "id: $!";
42			my $foo=<ID>; chomp $foo;
43			if ($foo=~/uid=\d+\((\w+)\)/) {
44			$db_user=$1;
45			} else {
46			die "can't locate who is local user!";
47			}
48			}
49	dpavlin	1.4
50			open(HOSTNAME,"hostname \|") \|\| die "hostname: $!";
51			my $hostname=<HOSTNAME>;
52			chomp $hostname;
53			close(HOSTNAME);
54
55	dpavlin	1.5	$sth = $dbh->prepare("select ko_id from unix2ko where login='$db_user' and host='$hostname'") \|\| die $dbh->errstr();
56	dpavlin	1.4	$sth->execute() \|\| die $sth->errstr();
57			my ($ko_id)=$sth->fetchrow_array;
58	dpavlin	1.5	die "Ne mogu naæi odgovarajuæu kontakt osobu u tablici unix2ko!\n\tlogin=$db_user host=$hostname\n" if (!defined($ko_id));
59
60			print "kontakt_osoba_id (koja je pokrenula skriptu): $ko_id\n" if ($debug);
61	dpavlin	1.4
62	dpavlin	1.1	#$sth = $dbh->prepare("select id from kontakt_osobe where e_mail like '%hana%'") \|\| die $dbh->errstr();
63			#$sth->execute() \|\| die $sth->errstr();
64			#($kontakt_osoba_id) = $sth->fetchrow_array();
65
66			$sth = $dbh->prepare("select max(id) from last_print") \|\| die $dbh->errstr();
67			$sth->execute() \|\| die $sth->errstr();
68			my ($last_print_id) = $sth->fetchrow_array();
69
70			my $sql;
71
72			if (! defined($print_only_id)) {
73			$sql="select osoba_id,max(status_tip_id) from status where osoba_id > $last_print_id group by osoba_id";
74			} else {
75	dpavlin	1.3	$sql="select osoba_id,status_tip_id from status where osoba_id in ($print_only_id) and status_tip_id=3";
76	dpavlin	1.1	}
77
78			$sth = $dbh->prepare("$sql") \|\| die "$sql ",$dbh->errstr();
79			$sth->execute() \|\| die "$sql ",$sth->errstr();
80
81			#my $out_tex="out.tex";
82	dpavlin	1.6	my $out_tex="out-$$.tex";
83	dpavlin	1.1
84			open(IN,"obavjest.tex") \|\| die "obavjest.tex: $!";
85			open(OUT,"> $out_tex") \|\| die "$out_tex: $!";
86			while(<IN>) {
87			print OUT $_;
88			last if (/^%--begin_of_page--/);
89			}
90			my $begin_pos=tell(IN);
91
92			my @osoba_id_za_print;
93			my $max_osoba_id = 0;
94
95			while (my ($osoba_id,$status_tip_id) = $sth->fetchrow_array() ) {
96			if ($status_tip_id == 3) { # 3==otvoren (zadnji status)
97			push @osoba_id_za_print,$osoba_id;
98			$max_osoba_id = $osoba_id if ($osoba_id > $max_osoba_id);
99			}
100			}
101
102			print "Raèuna za insprintati: ",$#osoba_id_za_print+1,"\n";
103			exit if ($#osoba_id_za_print+1 == 0);
104	dpavlin	1.8	my $server=3;
105	dpavlin	1.1	$sth = $dbh->prepare("
106			select osobe.id,ime,prezime,tel,lokacije.naziv,
107			lok_racunala,login,passwd,alias,sifra,
108	dpavlin	1.2	objekt,kat,soba,z_rac,kontakt_osoba_id,umrezavanje
109	dpavlin	1.1	from osobe,racuni,e_mail
110			where osobe.id in (".join(",",@osoba_id_za_print).")
111	dpavlin	1.9	and lokacija_id=lokacije.id
112	dpavlin	1.8	and racuni.osoba_id=osobe.id and racuni.server_id=$server
113			and e_mail.osoba_id=osobe.id
114	dpavlin	1.1	") \|\| die $dbh->errstr();
115			$sth->execute() \|\| die $sth->errstr();
116
117
118			while (my @arr = $sth->fetchrow_array() ) {
119			print join("\|",@arr),"\n";
120
121	dpavlin	1.5	if (!$debug) {
122			$dbh->do("insert into status (osoba_id,kontakt_osoba_id,datum,status_tip_id) values ($arr[0],$ko_id,'now'::datetime,5)") \|\| die $dbh->errstr();
123			}
124	dpavlin	1.1
125	dpavlin	1.8	my $sth2 = $dbh->prepare("select naziv,mt from sap_osobe,sap_oj where sap_oj.sifra_oj = sap_oj and sap_sifra = $arr[11]");
126			$sth2->execute() \|\| die $sth2->errstr();
127			my ($sap_org_jed,$mt) = $sth2->fetchrow_array();
128
129	dpavlin	1.1	seek(IN,$begin_pos,0);
130			while(<IN>) {
131			s/--id--/$arr[0]/g;
132			$arr[0]=~s/^\s+//g;
133			$arr[0]=~s/\s+$//g;
134			s/--ime--/$arr[1]/g;
135
136			$arr[1]=~s/^\s+//g;
137			$arr[1]=~s/\s+$//g;
138			s/--prezime--/$arr[2]/g;
139			s/--tel--/$arr[3]/g;
140			s/--lok--/$arr[4]/g;
141	dpavlin	1.8	s/--orgjed--/$sap_org_jed/g;
142			s/--mt--/$mt/g;
143	dpavlin	1.1	s/--lokrac--/$arr[7]/g;
144			s/--login--/$arr[8]/g;
145
146
147			# ovo mora iæi dva puta da bi pretvorio sve znakove
148			# koji LaTeX-u imaju specijalno znaèenje
149			$arr[9]=~ s/([^\\])([%&\$#])/$1\\$2/g;
150			$arr[9]=~ s/([^\\])([%&\$#])/$1\\$2/g;
151			$arr[9]=~ s/^([%&\$#])/\\$1/g;
152
153			s/--passwd--/$arr[9]/g;
154
155			$arr[10]=~s/^\s+//g;
156			$arr[10]=~s/\s+$//g;
157			s/--email--/$arr[10]/g;
158
159			$arr[11]=~s/^\s+//g;
160			$arr[11]=~s/\s+$//g;
161			s/--sifra--/$arr[11]/g;
162
163			s/--obj--/$arr[12]/g;
164			s/--kat--/$arr[13]/g;
165			s/--soba--/$arr[14]/g;
166			s/--zrac--/$arr[15]/g;
167			my $ean=sprintf("2%06d%05d",$arr[11],$arr[0]);
168			s/--ean--/$ean/g;
169	dpavlin	1.2
170			if ($arr[17] == 1) {
171			s/--umrezavanje--/\\bf{Potrebno je umre¾avanje}/gi;
172			} else {
173			s/--umrezavanje--/{\\small Raèunalo je umre¾eno}/gi;
174			}
175
176	dpavlin	1.1	print OUT $_;
177
178			if (/^%--insert_status--/) {
179			my $sth2 = $dbh->prepare("select
180			datum,status_tip.opis,
181			kontakt_osobe.ime\|\|' '\|\|kontakt_osobe.prezime
182			from status,status_tip
183			where status_tip_id=status_tip.id
184			and kontakt_osobe.id=kontakt_osoba_id
185			and status.osoba_id=$arr[0]
186			order by datum
187			") \|\| die $dbh->errstr();
188			$sth2->execute() \|\| die $sth2->errstr();
189			while (my @arr2 = $sth2->fetchrow_array() ) {
190			print OUT join(" & ",@arr2)," \\\\ \n";
191			}
192			}
193			last if (/^%--end_of_page--/);
194			}
195			print OUT "\\newpage\n";
196
197			}
198
199			$dbh->do("insert into last_print values ('now'::datetime,$max_osoba_id)") if (!$print_only_id && !$debug);
200
201			$sth->finish;
202
203			while(<IN>) {
204			print OUT $_;
205			}
206			close(OUT);
207			close(IN);
208
209			my $out_no_ext=$out_tex;
210			$out_no_ext=~s/\.tex//;
211
212			my $path=$0;
213			$path=~s/\/[^\/]+//g;
214
215	dpavlin	1.2	if (!$debug) {
216			system "$path/codean.pl $out_no_ext.tex $out_no_ext.tex2 && mv $out_no_ext.tex2 $out_no_ext.tex && latex $out_no_ext.tex && dvips $out_no_ext.dvi && lpr $out_no_ext.ps";
217			} else {
218			system "$path/codean.pl $out_no_ext.tex $out_no_ext.tex2 && mv $out_no_ext.tex2 $out_no_ext.tex && latex $out_no_ext.tex && dvips $out_no_ext.dvi";
219
220			}