]> git.ipfire.org Git - ipfire-2.x.git/blame - src/squid-accounting/acct-lib.pl
projects / ipfire-2.x.git / blame
? search:
summary | shortlog | log | commit | commitdiff | tree
blob | blame (incremental) | history | HEAD
Squid-accounting: new addon for measuring proxy traffic per user/ip
[ipfire-2.x.git] / src / squid-accounting / acct-lib.pl
CommitLineData
db8a01e0
AM		 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###############################################################################
21package ACCT;
22
23use DBI;
24use POSIX;
25use Time::Local;
26use PDF::API2;
27use utf8;
28use Encode;
29use File::Copy;
30
31###############################################################################
32my $dbh;
33my $dsn="dbi:SQLite:dbname=/var/ipfire/accounting/acct.db";
34my ($sec,$min,$hour,$mday,$mon,$year,$wday,$ydat,$isdst)=localtime();
35my %mainsettings;
36###############################################################################
37
38&General::readhash("/var/ipfire/main/settings", \%mainsettings);
39my $uplang=uc($mainsettings{'LANGUAGE'});
40
41#############
42# Functions #
43#############
44
45sub connectdb {
46 $dbh = DBI->connect($dsn, "", "",{RaiseError => 1, AutoCommit => 1})or die "ERROR $!";
47 return $dbh;
48}
49
50sub closedb {
51 $dbh->disconnect();
52 return $dbh;
53}
54
55sub 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
69sub cleardbtraf {
70 &connectdb;
71 $dbh->do("DELETE FROM ACCT;");
72 $dbh->do("DELETE FROM ACCT_HIST;");
73 &closedb;
74}
75
76sub 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
86sub 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
94sub 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
99sub 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
114sub 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
130sub 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
157sub 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
182sub 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
189sub 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
196sub 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
203sub 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
210sub 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
218sub 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
237sub 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
247sub 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
257sub 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
265sub 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
277sub 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
294sub 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
304sub 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
313sub 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
324sub checkusergrp {
325 $dbh=connectdb;
326 my $res=$dbh->selectall_arrayref("SELECT * FROM BILLINGHOST;");
327 $dbh->disconnect();
328 return $res;
329}
330
331sub 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
344sub 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
368sub 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
377sub 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
385sub 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
797sub 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
810sub 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
823sub 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
831sub 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
844sub 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
858sub 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
889sub 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
901sub 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
912sub 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}
919return 1;
IPFire 2.x development tree