Squid-accounting: new addon for measuring proxy traffic per user/ip
[ipfire-2.x.git] / src / squid-accounting / acct-lib.pl
1 #!/usr/bin/perl
2 ###############################################################################
3 #                                                                             #
4 # IPFire.org - A linux based firewall                                         #
5 # Copyright (C) 2014  IPFire Team  <alexander.marx@ipfire.org>                #
6 #                                                                             #
7 # This program is free software: you can redistribute it and/or modify        #
8 # it under the terms of the GNU General Public License as published by        #
9 # the Free Software Foundation, either version 3 of the License, or           #
10 # (at your option) any later version.                                         #
11 #                                                                             #
12 # This program is distributed in the hope that it will be useful,             #
13 # but WITHOUT ANY WARRANTY; without even the implied warranty of              #
14 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the               #
15 # GNU General Public License for more details.                                #
16 #                                                                             #
17 # You should have received a copy of the GNU General Public License           #
18 # along with this program.  If not, see <http://www.gnu.org/licenses/>.       #
19 #                                                                             #
20 ###############################################################################
21 package ACCT;
22
23 use DBI;
24 use POSIX;
25 use Time::Local;
26 use PDF::API2;
27 use utf8;
28 use Encode;
29 use File::Copy;
30
31 ###############################################################################
32 my $dbh;
33 my $dsn="dbi:SQLite:dbname=/var/ipfire/accounting/acct.db";
34 my ($sec,$min,$hour,$mday,$mon,$year,$wday,$ydat,$isdst)=localtime();
35 my %mainsettings;
36 ###############################################################################
37
38 &General::readhash("/var/ipfire/main/settings", \%mainsettings);
39 my $uplang=uc($mainsettings{'LANGUAGE'});
40
41 #############
42 # Functions #
43 #############
44
45 sub connectdb {
46         $dbh = DBI->connect($dsn, "", "",{RaiseError => 1, AutoCommit => 1})or die "ERROR $!";
47         return $dbh;
48 }
49
50 sub closedb {
51         $dbh->disconnect();
52         return $dbh;
53 }
54
55 sub getminmax {
56         my $min;
57         my $max;
58         $dbh=&connectdb;
59         my $sth = $dbh->prepare('Select min(TIME_RUN),max(TIME_RUN) from ACCT;');
60         $sth->execute;
61         while ( my @row = $sth->fetchrow_array ) {
62                 $min=$row[0];
63                 $max=$row[1];
64         }
65         $dbh->disconnect();
66         return ($min,$max);
67 }
68
69 sub cleardbtraf {
70         &connectdb;
71         $dbh->do("DELETE FROM ACCT;");
72         $dbh->do("DELETE FROM ACCT_HIST;");
73         &closedb;
74 }
75
76 sub cleardb {
77         &connectdb;
78         $dbh->do("DELETE FROM ACCT;");
79         $dbh->do("DELETE FROM ACCT_HIST;");
80         $dbh->do("DELETE FROM ACCT_ADDR ");
81         $dbh->do("DELETE FROM BILLINGGRP");
82         $dbh->do("DELETE FROM BILLINGHOST");
83         &closedb;
84 }
85
86 sub delbefore {
87         my $till=$_[0];
88         &connectdb;
89         $dbh->do("DELETE FROM ACCT WHERE TIME_RUN < ".$till.";");
90         $dbh->do("DELETE FROM ACCT_HIST WHERE TIME_RUN < date('".$till."','unixepoch');");
91         &closedb;
92 }
93
94 sub movedbdata {
95         $dbh->do("insert into ACCT_HIST select datetime(TIME_RUN,'unixepoch'),NAME,SUM(BYTES) from ACCT where  date(TIME_RUN,'unixepoch') < date('now','-2 months') group by NAME,date(TIME_RUN,'unixepoch');");
96         $dbh->do("DELETE FROM ACCT WHERE datetime(TIME_RUN,'unixepoch') < date('now','-2 months');");
97 }
98
99 sub gethourgraphdata {
100         my $table=$_[0];
101         my $from=$_[1];
102         my $till=$_[2];
103         my $name=$_[3];
104         my $res;
105         $dbh=connectdb;
106         if ($table eq 'ACCT'){
107                 $res = $dbh->selectall_arrayref( "SELECT TIME_RUN,BYTES FROM ACCT WHERE TIME_RUN BETWEEN ".$from." AND ".$till." AND NAME = '".$name."';");
108         }else{
109                 $res = $dbh->selectall_arrayref( "SELECT TIME_RUN,BYTES FROM ACCT_HIST WHERE TIME_RUN BETWEEN date(".$from.",'unixepoch') AND date(".$till.",'unixepoch') AND NAME = '".$name."';");
110         }
111         return $res;
112 }
113
114 sub getmonthgraphdata {
115         my $table=$_[0];
116         my $from=$_[1];
117         my $till=$_[2];
118         my $name=$_[3];
119         my $res;
120         $dbh=connectdb;
121         if ($table eq 'ACCT'){
122                 $res = $dbh->selectall_arrayref( "SELECT  strftime('%d.%m.%Y',xx.tag),(SELECT SUM(BYTES)/1024/1024 FROM ACCT WHERE date(TIME_RUN,'unixepoch') <= xx.tag and NAME = '".$name."') kum_bytes FROM (SELECT date(TIME_RUN,'unixepoch') tag,SUM(BYTES)/1024/1024 sbytes FROM ACCT WHERE NAME='".$name."' and TIME_RUN between ".$from." and ".$till." GROUP by date(TIME_RUN,'unixepoch')) xx;");
123         }else{
124                 $res = $dbh->selectall_arrayref( "SELECT TIME_RUN, (SELECT SUM(BYTES)/1024/1024 FROM ACCT_HIST WHERE TIME_RUN <= ah.TIME_RUN and NAME = '".$name."') kum_bytes FROM ACCT_HIST ah WHERE TIME_RUN BETWEEN date(".$from.",'unixepoch') AND date(".$till.",'unixepoch') AND NAME = '".$name."' group by TIME_RUN;");
125         }
126         $dbh=closedb;
127         return $res;
128 }
129
130 sub writeaddr {
131         my $comp  = $_[0];
132         my $type  = $_[1];
133         my $name1 = $_[2];
134         my $str   = $_[3];
135         my $nr    = $_[4];
136         my $post  = $_[5];
137         my $city  = $_[6];
138         my $bank  = $_[7];
139         my $iban  = $_[8];
140         my $bic   = $_[9];
141         my $blz   = $_[10];
142         my $kto   = $_[11];
143         my $mail  = $_[12];
144         my $inet  = $_[13];
145         my $hrb   = $_[14];
146         my $ustid = $_[15];
147         my $tel   = $_[16];
148         my $fax   = $_[17];
149         $dbh=&connectdb;
150         #COMPANY,TYPE,NAME1,STR,NR,POSTCODE,CITY,BANK,IBAN,BLZ,ACCOUNT,EMAIL,INTERNET,HRB,USTID,TEL,FAX
151         my $sql = "INSERT INTO ACCT_ADDR (COMPANY,TYPE,NAME1,STR,NR,POSTCODE,CITY,BANK,IBAN,BIC,BLZ,ACCOUNT,EMAIL,INTERNET,HRB,USTID,TEL,FAX) VALUES ( ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
152         my $sth = $dbh->prepare( $sql );
153         $sth->execute( $comp,$type,$name1,$str,$nr,$post,$city,$bank,$iban,$bic,$blz,$kto,$mail,$inet,$hrb,$ustid,$tel,$fax );
154         $dbh=&closedb;
155 }
156
157 sub updateaddr {
158         my $type  = $_[0];
159         my $comp  = $_[1];
160         my $name1 = $_[2];
161         my $str   = $_[3];
162         my $nr    = $_[4];
163         my $post  = $_[5];
164         my $city  = $_[6];
165         my $bank  = $_[7];
166         my $iban  = $_[8];
167         my $bic   = $_[9];
168         my $blz   = $_[10];
169         my $kto   = $_[11];
170         my $mail  = $_[12];
171         my $inet  = $_[13];
172         my $hrb   = $_[14];
173         my $ustid = $_[15];
174         my $tel   = $_[16];
175         my $fax   = $_[17];
176         my $oldname = $_[18];
177         $dbh=&connectdb;
178         $dbh->do("UPDATE ACCT_ADDR SET COMPANY=?,TYPE=?,NAME1=?,STR=?,NR=?,POSTCODE=?,CITY=?,BANK=?,IBAN=?,BIC=?,BLZ=?,ACCOUNT=?,EMAIL=?,INTERNET=?,HRB=?,USTID=?,TEL=?,FAX=? WHERE COMPANY=?", undef,$comp,$type,$name1,$str,$nr,$post,$city,$bank,$iban,$bic,$blz,$kto,$mail,$inet,$hrb,$ustid,$tel,$fax,$oldname)or die "Could not UPDATE Address.";
179         $dbh=&closedb;
180 }
181
182 sub deladdr {
183         my $comp    = $_[0];
184         $dbh=&connectdb;
185         $dbh->do("DELETE FROM ACCT_ADDR WHERE COMPANY=?", undef,$comp )or die "Could not delete address $comp!";
186         $dbh=&closedb;
187 }
188
189 sub getaddresses {
190         $dbh=&connectdb;
191         my $res=$dbh->selectall_arrayref("SELECT (SELECT COUNT(NAME) FROM BILLINGGRP GROUP BY NAME),* FROM ACCT_ADDR ORDER BY COMPANY;");
192         $dbh=&closedb;
193         return $res;
194 }
195
196 sub gethosts{
197         $dbh=&connectdb;
198         my $res =$dbh->selectall_arrayref("SELECT NAME from ACCT GROUP BY NAME ORDER BY NAME;");
199         $dbh=&closedb;
200         return $res;
201 }
202
203 sub getbillgroups {
204         $dbh=connectdb;
205         my $res=$dbh->selectall_arrayref("SELECT NAME,HOST,CUST,BILLTEXT,(SELECT COUNT(HOST) FROM BILLINGHOST WHERE BILLINGGRP.NAME=BILLINGHOST.GRP),CENT FROM BILLINGGRP ORDER BY NAME;");
206         return $res;
207         $dbh->disconnect();
208 }
209
210 sub getextrabillpos {
211         my $grp=$_[0];
212         $dbh=&connectdb;
213         my $res=$dbh->selectall_arrayref("SELECT * from BILLPOS WHERE GRP =?", undef,$grp);
214         $dbh=&closedb;
215         return $res;
216 }
217
218 sub savebillgroup {
219         my $grp=$_[0];
220         my $txt=$_[1];
221         my $host=$_[2];
222         my $cust=$_[3];
223         my $ust=$_[4];
224         my @ips=@{$_[5]};
225         $dbh=&connectdb;
226         my $sql = "INSERT INTO BILLINGGRP (NAME,BILLTEXT,HOST,CUST,CENT) VALUES (?,?,?,?,?)";
227         my $sth = $dbh->prepare( $sql );
228         $sth->execute( $grp,$txt,$host,$cust,$ust );
229         foreach my $ip (@ips){
230                 my $sql = "INSERT INTO BILLINGHOST (GRP,HOST) VALUES (?,?)";
231                 my $sth = $dbh->prepare( $sql ) or die "Could not prepare insert into BILLINGHOST $!";
232                 $sth->execute( $grp,$ip ) or die "Could not execute INSERT into BILLINGHOST $!";
233         }
234         $dbh=&closedb;
235 }
236
237 sub updatebillgrouphost {
238         my $oldgrp=$_[0];
239         my $newgrp=$_[1];
240         $dbh=&connectdb;
241         my $sql = "UPDATE BILLINGGRP SET HOST=? WHERE HOST=?;";
242         my $sth = $dbh->prepare( $sql );
243         $sth->execute( $newgrp,$oldgrp );
244         $dbh=&closedb;
245 }
246
247 sub updatebillgroupcust {
248         my $oldgrp=$_[0];
249         my $newgrp=$_[1];
250         $dbh=&connectdb;
251         my $sql = "UPDATE BILLINGGRP SET CUST=? WHERE CUST=?;";
252         my $sth = $dbh->prepare( $sql );
253         $sth->execute( $newgrp,$oldgrp );
254         $dbh=&closedb;
255 }
256
257 sub deletebillgroup {
258         my $name=shift;
259         $dbh=connectdb;
260         $dbh->do("DELETE FROM BILLINGGRP WHERE NAME=?;", undef,$name);
261         $dbh->do("DELETE FROM BILLINGHOST WHERE GRP=?;", undef,$name);
262         &closedb;
263 }
264
265 sub savebillpos {
266         my $grp=$_[0];
267         my $amnt=$_[1];
268         my $pos=$_[2];
269         my $price=$_[3];
270         $dbh=&connectdb;
271         my $sql = "INSERT INTO BILLPOS (GRP,AMOUNT,POS,PRICE) VALUES (?,?,?,?)";
272         my $sth = $dbh->prepare( $sql )or die "Could not prepare insert into BILLINGPOS $!";
273         $sth->execute( $grp,$amnt,$pos,$price ) or die "Could not execute INSERT into BILLINGHOST $!";
274         $dbh->disconnect();
275 }
276
277 sub updatebillpos {
278         my $oldgrp=shift;
279         my $newgrp=shift;
280         $dbh=&connectdb;
281         my $sql = "UPDATE BILLPOS SET GRP=? WHERE GRP=?;";
282         my $sth = $dbh->prepare( $sql );
283         $sth->execute( $newgrp,$oldgrp );
284         my $sql1 = "UPDATE BILLS SET GRP=? WHERE GRP=?;";
285         my $sth1 = $dbh->prepare( $sql1 );
286         $sth1->execute( $newgrp,$oldgrp );
287         $dbh=&closedb;
288         #Now rename directories
289         rename ("/srv/web/ipfire/html/accounting/logo/$oldgrp","/srv/web/ipfire/html/accounting/logo/$newgrp");
290         rename ("/var/ipfire/accounting/bill/$oldgrp","/var/ipfire/accounting/bill/$newgrp")
291         
292 }
293
294 sub delbillpos_single {
295         my $pos=$_[0];
296         my $grp=$_[1];
297         my $sql = "DELETE FROM BILLPOS WHERE GRP=? AND POS=?;";
298         $dbh=&connectdb;
299         my $sth = $dbh->prepare( $sql )or die "Could not prepare DELETE POS from BILLINGPOS $!";
300         $sth->execute( $grp,$pos ) or die "Could not execute DELETE from BILLINGHOST $!";
301         $dbh=&closedb;
302 }
303
304 sub delbillpos {
305         my $grp=$_[0];
306         my $sql = "DELETE FROM BILLPOS WHERE GRP=?;";
307         $dbh=&connectdb;
308         my $sth = $dbh->prepare( $sql )or die "Could not prepare DELETE POS from BILLINGPOS $!";
309         $sth->execute( $grp ) or die "Could not execute DELETE from BILLINGHOST $!";
310         $dbh=&closedb;
311 }
312
313 sub listhosts{
314         my $name=$_[0];
315         my $a;
316         my $res=$dbh->selectall_arrayref("SELECT * FROM BILLINGHOST WHERE GRP='".$name."';");
317         foreach my $gzu (@$res){
318                 my ($x,$y)=@$gzu;
319                 $a.= "|$y";
320         }
321         return $a;
322 }
323
324 sub checkusergrp {
325         $dbh=connectdb;
326         my $res=$dbh->selectall_arrayref("SELECT * FROM BILLINGHOST;");
327         $dbh->disconnect();
328         return $res;
329 }
330
331 sub getmonth{
332         #GET  : 1. month   2. year
333         #GIVES: 1.day of given month AND last day of given month in seconds since 1.1.1970
334         ($sec,$min,$hour,$mday,$mon,$year,$wday,$ydat,$isdst)=localtime();
335                 my $jahr=$_[1];
336                 my $monat=$_[0]-1 if($_[0]);
337                 my $tag=1;
338                 my $time1=timelocal(0,0,0,$tag,$monat,$jahr);
339                 my $time2=timelocal(0,0,0,$tag,($monat+1),$jahr);
340                 --$time2;
341                 return ($time1,$time2);
342 }
343
344 sub GetTaValues {
345         $dbh=&connectdb;
346         my $from = $_[0]; #unixtimestamp
347         my $till = $_[1]; #unixtimestamp
348         my $grp  = $_[2]; #Billgroupname
349         my $all = $dbh->selectall_arrayref("SELECT bh.HOST,SUM(ac.BYTES) sbytes,bh.GRP FROM ACCT ac ,BILLINGHOST bh WHERE ac.NAME=bh.HOST AND bh.GRP=? AND ac.TIME_RUN between ? AND ? GROUP BY bh.GRP,bh.HOST;", undef, $grp, $from, $till) or die "Could not fetch Groupdata $!"; 
350         my $nri1 = @$all;
351         my @return;
352         my $cnt=0;
353         if ($nri1 eq "0"){
354                 $return[$cnt]="999";
355         }
356         else
357         {
358                 foreach my $row (@$all){
359                         my ($bytes,$billgrp,$host) = @$row;
360                         $return[$cnt]="$bytes,$billgrp,$host";
361                         $cnt++;
362                 }
363         }
364         &closedb;
365         return @return;
366 }
367
368 sub getTaAddress {
369         my $grp=$_[0];
370         my $type=$_[1];
371         $dbh=&connectdb;
372         my $res = $dbh->selectall_arrayref("select * from ACCT_ADDR,BILLINGGRP where (BILLINGGRP.HOST=ACCT_ADDR.COMPANY AND BILLINGGRP.NAME=? AND ACCT_ADDR.TYPE=?) or (BILLINGGRP.CUST=ACCT_ADDR.COMPANY and BILLINGGRP.NAME=? AND ACCT_ADDR.TYPE=?);", undef, $grp,$type,$grp,$type);
373         &closedb;
374         return $res;
375 }
376
377 sub checkbillgrp {
378         my $comp=$_[0];
379         $dbh=&connectdb;
380         my $res=$dbh->selectall_arrayref("SELECT NAME,HOST,CUST FROM BILLINGGRP;");
381         &closedb;
382         return $res;
383 }
384
385 sub pdf2 {
386         my @billar              = @{$_[0]}; #DATA from sendbill (just host/values)
387         my $month               = $_[1];
388         $month                  = '0'.$month if $month < 10;
389         my $year                = $_[2];
390         my $mwst                = $_[3];
391         my @address_cust= @{$_[4]}; #Array which contains customer and hoster adresses and some additional info from billgroup
392         my @address_host= @{$_[5]};
393         my @billpos             = @{$_[6]};
394         my $grp                 = $_[7];
395         my $cur                 = $_[8]; #(Eur,USD,...)
396         my $preview             = $_[9];
397         my $no                  = &getBillNr;
398         my $name                = $month."-".$year."-".$no.".pdf";
399         my $path                ="/var/ipfire/accounting/bill/";
400         my $filename    = "$path/$grp/$name";
401         my @summen; #Used for counting the sums
402         my $x                   = 500;
403         my $y                   = 1;
404         my $zwsum;
405         my $pages               = 0;
406         my $anzbillpos  = @billpos;
407         my $anz                 = (@billar+$anzbillpos)/18; #Total pages
408         $anz = ceil($anz); #round the $anz value
409         my $aktpage=1;
410         my $sum=0;
411         my $sum1=0;
412         my $lines;
413         my $title;
414         my $txt;
415         my $txt1;
416         my $txt2;
417         my $txt3;
418         my $txt4;
419         my $txt5;
420         my $fnt;
421         my $fnt1;
422         my $fulldate    = strftime('%d.%m.%Y',localtime(time()));
423         my($company_host,$type_host,$name1_host,$str_host,$str_nr_host,$plz_host,$city_host,$bank,$iban,$bic,$blz,$kto,$email,$internet,$hrb,$stnr,$tel_host,$fax_host,$ccmail,$billgrp,$text,$host,$cust,$cent);
424         my($company_cust,$type_cust,$name1_cust,$str_cust,$str_nr_cust,$plz_cust,$city_cust);
425
426         #First of all check if directory exists, else create it
427         if(! -d "$path/$grp" && $preview ne 'on'){
428                 mkdir("$path/$grp",0777);
429         }
430
431         #Check if we are creating a preview or a real bill
432         if($preview eq 'on'){
433                 $filename="$path/tmp.pdf";
434         }
435         ####################################################################
436         #Prepare DATA from arrays
437         ####################################################################
438         #Get HOSTER for this grp
439         foreach my $addrline (@address_host){
440                 ($company_host,$type_host,$name1_host,$str_host,$str_nr_host,$plz_host,$city_host,$bank,$iban,$bic,$blz,$kto,$email,$internet,$hrb,$stnr,$tel_host,$fax_host,$ccmail,$billgrp,$text,$host,$cust,$cent)=@$addrline;
441         }
442         #Get CUST for this grp
443         foreach my $addrline_cust (@address_cust){
444                 ($company_cust,$type_cust,$name1_cust,$str_cust,$str_nr_cust,$plz_cust,$city_cust)=@$addrline_cust;
445         }
446
447
448         #Generate PDF File
449         my $pdf  = PDF::API2->new(-file => $filename);
450         $pdf->mediabox('A4');
451         my $page = $pdf->page;
452         $fnt = $pdf->corefont('Helvetica');
453         $fnt1 = $pdf->corefont('HelveticaBold');
454
455         #Set lines
456         $lines = $page->gfx;
457         $title = $page->gfx;
458         $lines->strokecolor('grey');
459         $lines->linewidth('0.5');
460
461         #Fill BILL DATA into PDF
462         setlocale(LC_ALL, "$mainsettings{'LANGUAGE'}_$uplang");
463         foreach (@billar) {
464                 my ($a1,$a2) = split( /\,/, $_ );
465                 $a2=sprintf"%.2f",($a2/1024/1024);
466                 my $sum=(($a2)*$cent);
467                 $sum = sprintf"%.2f",($sum);
468                 # Seitenwechsel ermitteln
469                 if ($y % 18 == 0) {
470                         $txt1->translate(390, 120);
471                         $txt1->text($Lang::tr{'acct pdf zwsum'}); #Pos
472                         $zwsum=sprintf("%.2f",($zwsum));
473                         $txt1->translate(540, 120);
474                         $txt1->text_right("$zwsum".decode('utf8',$cur)); #Pos
475                         $zwsum=0;
476                         $pages++;
477                         $aktpage++;
478                         $x=500;
479                         $page=$pdf->page;
480                         #draw lines
481                         $lines = $page->gfx;
482                         $title = $page->gfx;
483                         $lines->strokecolor('grey');
484                         $lines->linewidth('0.5');
485                 }
486
487                 #TITLES
488                 $title->linewidth(14);
489                 $title->move(385, 168);
490                 $title->line(545, 168); #Title of SUMBOX
491                 $title->move(60, 523);
492                 $title->line(545, 523);#Bottom horiz. line of Title
493
494                 # Generate Tables
495                 $lines->move(59, 745);
496                 $lines->line(545, 745);
497                 $lines->move(59, 563);
498                 $lines->line(545, 563);
499
500                 # Addressbox
501                 $lines->move(61, 710);
502                 $lines->line(61, 715, 66, 715); #TL
503                 $lines->move(61, 610);
504                 $lines->line(61, 605, 66, 605); #BL
505                 $lines->move(285, 715);
506                 $lines->line(290, 715, 290, 710); #TR
507                 $lines->move(290, 610);
508                 $lines->line(290, 605, 285, 605); #BR
509
510                 # Table for positions
511                 $lines->move(60, 530);
512                 $lines->line(60, 200); #First vert. line POS
513                 $lines->move(90, 523);
514                 $lines->line(90, 200); #Second vert. line
515                 $lines->move(280, 523);
516                 $lines->line(280, 200); #third vert. line
517                 $lines->move(385, 523);
518                 $lines->line(385, 200); #third vert. line
519                 $lines->move(430, 523);
520                 $lines->line(430, 200); #fourth vert. line
521                 $lines->move(545, 530);
522                 $lines->line(545, 200); #fifth vert. line
523                 $lines->move(60, 200);
524                 $lines->line(545, 200); #Bottom horizontal line
525
526                 #SUM BOX
527                 $lines->move(385, 175);
528                 $lines->line(385, 115); #Left vert. line of SUMBOX
529                 $lines->move(545, 175);
530                 $lines->line(545, 115); #Right vert. line of SUMBOX
531                 $lines->move(385, 115);
532                 $lines->line(545, 115); #Bottom horiz. line of SUMBOX
533
534                 #Lines on right side after sender and after "bank"
535                 $lines->move(420, 723);
536                 $lines->line(545, 723);# Line "Sender"
537                 $lines->move(420, 648);
538                 $lines->line(545, 648);# Line "Bank"
539                 $lines->move(420, 600);
540                 $lines->line(545, 600);# Line HRB/USTID
541
542                 #Make lines Visible
543                 $lines->stroke;
544                 $title->stroke;
545                 if (-f "/srv/web/ipfire/html/accounting/logo/$grp/logo.png"){
546                 #Image LOGO
547                         my $gfx = $page->gfx;
548                         my $image = $pdf->image_png("/srv/web/ipfire/html/accounting/logo/$grp/logo.png");
549                         my $width= $image->width;
550                         my $height= $image->height;
551                         $gfx->image($image, (545+($width/2))-$width, 750,0.5);
552                 }
553
554                 #Set Fonts
555                 $txt = $page->text;
556                 $txt1 = $page->text;
557                 $txt2 = $page->text;
558                 $txt3 = $page->text;
559                 $txt4 = $page->text;
560                 $txt5 = $page->text;
561                 $txt->textstart;                #Begin Text
562                 $txt->font($fnt, 10);   #Set fontsize for font1
563                 $txt1->font($fnt, 8);   #Set fontsize for font2
564                 $txt2->font($fnt1, 10); #Set fontsize for font3
565                 $txt3->font($fnt1, 16); #Set fontsize for font4
566                 $txt4->font($fnt, 6);   #Set fontsize for font5
567                 $txt5->font($fnt1, 6);  #Set fontsize for font6
568
569                 #if $cent not set, set it to 0.5
570                 if(!$cent){$cent='0.005';}
571
572                 #if MWst not set, set it to 19%
573                 if(!$mwst){$mwst='19';}
574
575                 # Titles
576                 $txt1->translate(65,520);
577                 $txt1->text($Lang::tr{'acct pos'}); #Pos
578                 $txt1->translate(95, 520);
579                 $txt1->text($Lang::tr{'acct name'}); #Host/Name
580                 $txt1->translate(285, 520);
581                 $txt1->text($Lang::tr{'acct amount'}); #Traffic
582                 $txt1->translate(390, 520);
583                 $txt1->text($Lang::tr{'acct cent1'}); #Price /MB
584                 $txt1->translate(435, 520);
585                 $txt1->text($Lang::tr{'acct pdf price'}); #Sum
586
587         ####################################################################
588                 #Fill Recipient address
589                 my $rec_name= "$company_cust";
590                 my $rec_name1="$name1_cust";
591                 my $rec_str = "$str_cust $str_nr_cust";
592                 my $rec_city = "$plz_cust $city_cust";
593                 #INSERT RECIPIENT
594                 my $o=675;
595                 $txt2->translate(78, 685);
596                 $txt2->text(decode('utf8',$rec_name));
597                 if($rec_name1){
598                         $txt1->translate(78, $o);
599                         $txt1->text(decode('utf8',$rec_name1));
600                         $o=$o-15;
601                 }else{
602                         $o=$o-15;
603                 }
604                 $txt1->translate(78, $o);
605                 $txt1->text(decode('utf8',$rec_str));
606                 $o=$o-10;
607                 $txt1->translate(78, $o);
608                 $txt1->text(decode('utf8',$rec_city));
609
610                 # INSERT SENDER
611                 my $send_name= "$company_host";
612                 my $send_str = "$str_host $str_nr_host";
613                 my $send_city = "$plz_host $city_host";
614                 my $send_bank ="$bank";
615
616                 $txt5->translate(420, 725);
617                 $txt5->text(decode('utf8',$Lang::tr{'acct pdf prov'}));
618                 $txt5->translate(420, 715);
619                 $txt5->text(decode('utf8',$send_name));
620                 my $j=705;
621                 if($name1_host){
622                         $txt4->translate(420, $j);
623                         $txt4->text(decode('utf8',$name1_host));
624                         $j=$j-8;
625                 }
626                 $txt4->translate(420, $j);
627                 $txt4->text(decode('utf8',$send_str)); #STR
628                 $j=$j-8;
629                 $txt4->translate(420, $j);
630                 $txt4->text(decode('utf8',$send_city)); #PLZ.City
631                 #Print optional Values tel,fax
632                 my $i=680;
633                 if($tel_host){
634                         $txt4->translate(420, $i);
635                         $txt4->text($Lang::tr{'acct tel'}); #Tel
636                         $txt4->translate(480, $i);
637                         $txt4->text($tel_host); #Telnr
638                         $i=$i-8;
639                 }
640                 if($fax_host){
641                         $txt4->translate(420, $i);
642                         $txt4->text($Lang::tr{'acct fax'}); #Fax
643                         $txt4->translate(480, $i);
644                         $txt4->text($fax_host); #Faxnr
645                         $i=$i-8;
646                 }
647                 if($internet){
648                         $txt4->translate(420, $i);
649                         $txt4->text($Lang::tr{'acct inet'}); #Internet
650                         $txt4->translate(480, $i);
651                         $txt4->text($internet); #www-address
652                         $i=$i-8;
653                 }
654                 $txt5->translate(420, 650);
655                 $txt5->text(decode('utf8',$Lang::tr{'acct bank'})); #"BANK"
656                 $txt4->lead(7); 
657                 $txt4->translate(420, 640);
658                 $txt4->paragraph(decode('utf8',$bank), 130, 20, -align => "justify"); #Bankname
659                 if($iban){
660                         $txt4->translate(420, 625);
661                         $txt4->text($Lang::tr{'acct iban'}); #iban
662                         $txt4->translate(480, 625);
663                         $txt4->text(decode('utf8',$iban)); #iban
664                         $txt4->translate(420, 619);
665                         $txt4->text($Lang::tr{'acct bic'}); #bic
666                         $txt4->translate(480, 619);
667                         $txt4->text(decode('utf8',$bic)); #bic
668                 }
669                 if($blz){
670                         $txt4->translate(420, 613);
671                         $txt4->text($Lang::tr{'acct blz'}); #blz
672                         $txt4->translate(420, 607);
673                         $txt4->text($Lang::tr{'acct kto'}); #kto
674                         $txt4->translate(480, 613);
675                         $txt4->text(decode('utf8',$blz)); #blz
676                         $txt4->translate(480, 607);
677                         $txt4->text(decode('utf8',$kto)); #kto
678                 }
679
680                 #Print USTID and optional HRB
681                 $txt4->translate(420, 590);
682                 $txt4->text($Lang::tr{'acct ustid'}); #USTID
683                 $txt4->translate(480, 590);
684                 $txt4->text($stnr); #ustid
685                 if($hrb){
686                         $txt4->translate(420, 580);
687                         $txt4->text($Lang::tr{'acct hrb'}); #USTID
688                         $txt4->translate(480, 580);
689                         $txt4->text($hrb); #ustid
690                 }
691                 ################################################################
692
693                 #Print Date, Pages ....
694                 $txt3->translate(59, 545);
695                 $txt3->text($Lang::tr{'acct pdf billtxt'});
696                 $txt1->translate(160, 545);
697                 $txt1->text("$no $Lang::tr{'acct billnr'}");
698                 $txt1->translate(60, 532);
699                 $txt1->text("$Lang::tr{'acct pdf time'} $month/$year");
700                 $txt1->translate(545, 550);
701                 $txt1->text_right("$Lang::tr{'acct pdf date'} $fulldate");
702                 $txt1->translate(545, 532);
703                 $txt1->text_right("$Lang::tr{'acct pdf page'} $aktpage / $anz");
704
705                 if ($a1 eq '999'){last;}
706                 #Print DATA from array to Position table
707                 $txt1->translate(80, $x);
708                 $txt1->text_right($y);
709                 $txt1->translate(95, $x);
710                 $txt1->text($a1);
711                 $txt1->translate(380, $x);
712                 $txt1->text_right("$a2 MB");
713                 $txt1->translate(425, $x);
714                 $txt1->text_right("$cent ".decode('utf8',$cur));
715                 $txt1->translate(540, $x);
716                 $txt1->text_right("$sum ".decode('utf8',$cur));
717
718                 #Build SUMMARY
719                 $summen[$y-1]="$y,$a2,$sum";
720                 $zwsum=$zwsum+$sum;
721                 $x=$x-15;
722                 $y++;
723         }
724         #Print extra billpositions
725         foreach my $line (@billpos){
726                 my ($grp,$amount,$art,$price)=@$line;
727                 #Print DATA from array to Position table
728                 $txt1->translate(80, $x);
729                 $txt1->text_right($y);
730                 $txt1->translate(95, $x);
731                 $txt1->text(decode('utf8',$art));
732                 $txt1->translate(380, $x);
733                 $txt1->text_right($amount." pcs");
734                 $txt1->translate(540, $x);
735                 $txt1->text_right("$price ".decode('utf8',$cur));
736                 #Build SUMMARY
737                 my $zu=$amount * $price;
738                 $summen[$y-1]="$y,'0',$zu";
739                 $zwsum=$zwsum+$zu;
740                 $x=$x-15;
741                 $y++;
742         }
743         foreach (@summen){
744                 my ($a1,$a2,$a3) = split( /\,/, $_ );
745                 $sum=$sum+$a2;
746                 $sum1=$sum1+$a3;
747         }
748
749         # Last Line in positiontable prints the sum of all traffic (therefor txt2 which is BOLD)
750         $txt2->translate(95, 205);
751         $txt2->text($Lang::tr{'acct pdf sum1'});        #SUM
752         $txt2->translate(427, 205);
753         $txt2->text_right($cent);                                       #cent
754         $txt2->translate(380, 205);
755         $txt2->text_right("$sum MB");                           #MB
756         $sum1=sprintf("%.2f",($sum1));
757         $txt2->translate(540, 205);
758         $txt2->text_right("$sum1 ".decode('utf8',$cur));                                #SUM Eur
759         $txt->translate(390, 150);
760         $txt->text($Lang::tr{'acct pdf sum1'});
761         $txt->translate(540, 150);
762         $txt->text_right("$sum1 ".decode('utf8',$cur));
763         $txt->translate(390, 135);
764         my $endsum=$sum1;
765         $txt->text("$Lang::tr{'acct mwst_name'} $mwst%");
766         my $sum1=sprintf("%.2f",($sum1/100*$mwst));
767         $txt->translate(540, 135);
768         $txt->text_right("$sum1 ".decode('utf8',$cur));
769         $txt2->translate(390, 120);
770         $txt2->text($Lang::tr{'acct sum total'});
771         my $endsum=sprintf("%.2f",($sum1+$endsum));
772         $txt2->translate(540, 120);
773         $txt2->text_right("$endsum ".decode('utf8',$cur));
774
775         #Print the optional Billtext if any
776         $txt4->translate(60, 170);
777         $txt4->paragraph(decode('utf8',$text), 300, 40, -align => "justify"); #Bankname
778
779         #Watermark if preview
780         if ($preview eq 'on'){
781                 my $eg_trans = $pdf->egstate();
782                 $eg_trans->transparency(0.9);
783                 $txt5->egstate($eg_trans);
784                 $txt5->textlabel(80, 400, $fnt, 60, "PDF preview", -rotate => 40);
785                 $txt5->textlabel(150, 330, $fnt, 60, "IPFire accounting", -rotate => 40);
786         }
787
788         $txt->textend;                  #END Text
789         $pdf->save;                             #Save pdf
790         $pdf->end( );                   #END
791         if ($preview ne 'on'){
792                 &fillBill($path.$grp,$name,$no,$grp);
793         }
794         return '0';
795 }
796
797 sub getBillNr {
798         $dbh=&connectdb;
799         my $year1=$year+1900;
800         my $no1;
801         my $res=$dbh->selectall_arrayref("SELECT MAX(NO) FROM BILLS;");
802         foreach my $row (@$res){
803                 ($no1) = @$row;
804         }
805         if(!$no1){$no1=$year1."1000";}
806         $no1++;
807         return $no1;
808 }
809
810 sub fillBill {
811         my $path=$_[0];
812         my $name=$_[1];
813         my $no=$_[2];
814         my $grp=$_[3];
815         my $sth = $dbh->prepare("INSERT INTO BILLS (NO,GRP,PATH,NAME,DATE) VALUES (?,?,?,?,?);");
816         my $year1=$year+1900;
817         ++$mon;
818         $sth->execute($no,$grp,$path,$name,"$mday.$mon.$year1");
819         $sth->finish();
820         $dbh->disconnect();
821 }
822
823 sub getbills {
824         my $grp=shift;
825         $dbh=&connectdb;
826         my $res=$dbh->selectall_arrayref("SELECT * FROM BILLS WHERE GRP=?;",undef, $grp);
827         $dbh->disconnect();
828         return $res;
829 }
830
831 sub pngsize {
832         my $Buffer = shift;
833         my ($width,$height) = ( undef, undef );
834
835         if ($Buffer =~ /IHDR(.{8})/) {
836                 my $PNG = $1;
837                 ($width,$height) = unpack( "NN", $PNG );
838         } else { 
839                 $width=$Lang::tr{'acct invalid png'};
840         };
841         return ($width,$height);
842 }
843
844 sub gifsize {
845         my ($GIF)=@_;
846         my ($type,$a,$b,$c,$d,$s,$width,$height) ;
847         $type=substr($GIF,0,6);
848         if(!($type =~ m/GIF8[7,9]a/) || (length($s=substr($GIF, 6, 4))!=4) ){
849                 return;
850         }
851         ($a,$b,$c,$d)=unpack("C"x4,$s);
852
853         $width= $b<<8|$a;
854         $height= $d<<8|$c;
855         return ($width,$height);
856 }
857
858 sub jpegsize {
859         my ($JPEG)=@ _ ;
860         my ($count)=2 ;
861         my ($length)=length($JPEG) ;
862         my ($ch)="" ;
863         my ($c1,$c2,$a,$b,$c,$d,$width,$height) ;
864
865         while (($ch ne "\xda") && ($count<$length)) {
866                 while (($ch ne "\xff") && ($count < $length)) {
867                         $ch=substr($JPEG,$count,1); 
868                         $count++;
869                 }
870
871                 while (($ch eq "\xff") && ($count<$length)) {
872                         $ch=substr($JPEG,$count,1); 
873                         $count++;
874                 }
875
876                 if ((ord($ch) >= 0xC0) && (ord($ch) <= 0xC3)) {
877                         $count+=3;
878                         ($a,$b,$c,$d)=unpack("C"x4,substr($JPEG,$count,4));
879                         $width=$c<<8|$d;
880                         $height=$a<<8|$b;
881                         return($width,$height);
882                 }else {
883                         ($c1,$c2)= unpack("C"x2,substr($JPEG,$count,2));
884                         $count += $c1<<8|$c2;
885                 }
886         }
887 }
888
889 sub time{
890         ($sec,$min,$hour,$mday,$mon,$year,$wday,$ydat,$isdst)=localtime();
891         $hour=sprintf("%02d",$hour);
892         $min=sprintf("%02d",$min);
893         $sec=sprintf("%02d",$sec);
894         $year +=1900;
895         $mday=sprintf("%02d",$mday);
896         $mon=sprintf("%02d",$mon+1);
897         my $res="$mday.$mon.$year $hour:$min:$sec - ";
898         return $res;
899 }
900
901 sub logger{
902         my $settings=shift;
903         my $msg=shift;
904         #open LOGFILE
905         if ($settings eq 'on'){
906                 open ACCTLOG,">>/var/log/accounting.log" || print "could not open /var/log/accounting.log ";
907                 print ACCTLOG &time."$msg";
908                 close (ACCTLOG);
909         }
910 }
911
912 sub updateccaddr {
913         my $addr=shift;
914         my $cust=shift;
915         $dbh=&connectdb;
916         $dbh->do("UPDATE ACCT_ADDR SET CCMAIL=? WHERE COMPANY=? ;",undef, $addr, $cust);
917         $dbh->disconnect();
918 }
919 return 1;