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);

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);

$sth = $dbh->prepare("
        select osobe.id,ime,prezime,tel,lokacije.naziv,
                org_jed.naziv,org_pod_jed.pod_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 org_jed_id=org_jed.id
                and org_pod_jed_id=org_pod_jed.pod_id
                and racuni.osoba_id=osobe.id 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();
        }

        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--/$arr[5]/g;
                s/--orgpodjed--/$arr[6]/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
41			open(HOSTNAME,"hostname \|") \|\| die "hostname: $!";
42			my $hostname=<HOSTNAME>;
43			chomp $hostname;
44			close(HOSTNAME);
45
46	dpavlin	1.5	$sth = $dbh->prepare("select ko_id from unix2ko where login='$db_user' and host='$hostname'") \|\| die $dbh->errstr();
47	dpavlin	1.4	$sth->execute() \|\| die $sth->errstr();
48			my ($ko_id)=$sth->fetchrow_array;
49	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));
50
51			print "kontakt_osoba_id (koja je pokrenula skriptu): $ko_id\n" if ($debug);
52	dpavlin	1.4
53	dpavlin	1.1	#$sth = $dbh->prepare("select id from kontakt_osobe where e_mail like '%hana%'") \|\| die $dbh->errstr();
54			#$sth->execute() \|\| die $sth->errstr();
55			#($kontakt_osoba_id) = $sth->fetchrow_array();
56
57			$sth = $dbh->prepare("select max(id) from last_print") \|\| die $dbh->errstr();
58			$sth->execute() \|\| die $sth->errstr();
59			my ($last_print_id) = $sth->fetchrow_array();
60
61			my $sql;
62
63			if (! defined($print_only_id)) {
64			$sql="select osoba_id,max(status_tip_id) from status where osoba_id > $last_print_id group by osoba_id";
65			} else {
66	dpavlin	1.3	$sql="select osoba_id,status_tip_id from status where osoba_id in ($print_only_id) and status_tip_id=3";
67	dpavlin	1.1	}
68
69			$sth = $dbh->prepare("$sql") \|\| die "$sql ",$dbh->errstr();
70			$sth->execute() \|\| die "$sql ",$sth->errstr();
71
72			#my $out_tex="out.tex";
73	dpavlin	1.6	my $out_tex="out-$$.tex";
74	dpavlin	1.1
75			open(IN,"obavjest.tex") \|\| die "obavjest.tex: $!";
76			open(OUT,"> $out_tex") \|\| die "$out_tex: $!";
77			while(<IN>) {
78			print OUT $_;
79			last if (/^%--begin_of_page--/);
80			}
81			my $begin_pos=tell(IN);
82
83			my @osoba_id_za_print;
84			my $max_osoba_id = 0;
85
86			while (my ($osoba_id,$status_tip_id) = $sth->fetchrow_array() ) {
87			if ($status_tip_id == 3) { # 3==otvoren (zadnji status)
88			push @osoba_id_za_print,$osoba_id;
89			$max_osoba_id = $osoba_id if ($osoba_id > $max_osoba_id);
90			}
91			}
92
93			print "Raèuna za insprintati: ",$#osoba_id_za_print+1,"\n";
94			exit if ($#osoba_id_za_print+1 == 0);
95
96			$sth = $dbh->prepare("
97			select osobe.id,ime,prezime,tel,lokacije.naziv,
98			org_jed.naziv,org_pod_jed.pod_naziv,
99			lok_racunala,login,passwd,alias,sifra,
100	dpavlin	1.2	objekt,kat,soba,z_rac,kontakt_osoba_id,umrezavanje
101	dpavlin	1.1	from osobe,racuni,e_mail
102			where osobe.id in (".join(",",@osoba_id_za_print).")
103			and lokacija_id=lokacije.id and org_jed_id=org_jed.id
104			and org_pod_jed_id=org_pod_jed.pod_id
105			and racuni.osoba_id=osobe.id and e_mail.osoba_id=osobe.id
106			") \|\| die $dbh->errstr();
107			$sth->execute() \|\| die $sth->errstr();
108
109
110			while (my @arr = $sth->fetchrow_array() ) {
111			print join("\|",@arr),"\n";
112
113	dpavlin	1.5	if (!$debug) {
114			$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();
115			}
116	dpavlin	1.1
117			seek(IN,$begin_pos,0);
118			while(<IN>) {
119			s/--id--/$arr[0]/g;
120			$arr[0]=~s/^\s+//g;
121			$arr[0]=~s/\s+$//g;
122			s/--ime--/$arr[1]/g;
123
124			$arr[1]=~s/^\s+//g;
125			$arr[1]=~s/\s+$//g;
126			s/--prezime--/$arr[2]/g;
127			s/--tel--/$arr[3]/g;
128			s/--lok--/$arr[4]/g;
129			s/--orgjed--/$arr[5]/g;
130			s/--orgpodjed--/$arr[6]/g;
131			s/--lokrac--/$arr[7]/g;
132			s/--login--/$arr[8]/g;
133
134
135			# ovo mora iæi dva puta da bi pretvorio sve znakove
136			# koji LaTeX-u imaju specijalno znaèenje
137			$arr[9]=~ s/([^\\])([%&\$#])/$1\\$2/g;
138			$arr[9]=~ s/([^\\])([%&\$#])/$1\\$2/g;
139			$arr[9]=~ s/^([%&\$#])/\\$1/g;
140
141			s/--passwd--/$arr[9]/g;
142
143			$arr[10]=~s/^\s+//g;
144			$arr[10]=~s/\s+$//g;
145			s/--email--/$arr[10]/g;
146
147			$arr[11]=~s/^\s+//g;
148			$arr[11]=~s/\s+$//g;
149			s/--sifra--/$arr[11]/g;
150
151			s/--obj--/$arr[12]/g;
152			s/--kat--/$arr[13]/g;
153			s/--soba--/$arr[14]/g;
154			s/--zrac--/$arr[15]/g;
155			my $ean=sprintf("2%06d%05d",$arr[11],$arr[0]);
156			s/--ean--/$ean/g;
157	dpavlin	1.2
158			if ($arr[17] == 1) {
159			s/--umrezavanje--/\\bf{Potrebno je umre¾avanje}/gi;
160			} else {
161			s/--umrezavanje--/{\\small Raèunalo je umre¾eno}/gi;
162			}
163
164	dpavlin	1.1	print OUT $_;
165
166			if (/^%--insert_status--/) {
167			my $sth2 = $dbh->prepare("select
168			datum,status_tip.opis,
169			kontakt_osobe.ime\|\|' '\|\|kontakt_osobe.prezime
170			from status,status_tip
171			where status_tip_id=status_tip.id
172			and kontakt_osobe.id=kontakt_osoba_id
173			and status.osoba_id=$arr[0]
174			order by datum
175			") \|\| die $dbh->errstr();
176			$sth2->execute() \|\| die $sth2->errstr();
177			while (my @arr2 = $sth2->fetchrow_array() ) {
178			print OUT join(" & ",@arr2)," \\\\ \n";
179			}
180			}
181			last if (/^%--end_of_page--/);
182			}
183			print OUT "\\newpage\n";
184
185			}
186
187			$dbh->do("insert into last_print values ('now'::datetime,$max_osoba_id)") if (!$print_only_id && !$debug);
188
189			$sth->finish;
190
191			while(<IN>) {
192			print OUT $_;
193			}
194			close(OUT);
195			close(IN);
196
197			my $out_no_ext=$out_tex;
198			$out_no_ext=~s/\.tex//;
199
200			my $path=$0;
201			$path=~s/\/[^\/]+//g;
202
203	dpavlin	1.2	if (!$debug) {
204			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";
205			} else {
206			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";
207
208			}