]>
git.ipfire.org Git - people/jschlag/ipfire-2.x.git/blob - src/squid-accounting/acct-lib.pl
2 ###############################################################################
4 # IPFire.org - A linux based firewall #
5 # Copyright (C) 2014 IPFire Team <alexander.marx@ipfire.org> #
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. #
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. #
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/>. #
20 ###############################################################################
30 use File
:: Temp qw
/ tempfile tempdir / ;
32 ###############################################################################
34 my $dsn = "dbi:SQLite:dbname=/var/ipfire/accounting/acct.db" ;
35 my ( $sec , $min , $hour , $mday , $mon , $year , $wday , $ydat , $isdst )= localtime ();
37 ###############################################################################
39 & General
:: readhash
( "/var/ipfire/main/settings" , \
%mainsettings );
40 my $uplang = uc ( $mainsettings { 'LANGUAGE' });
47 $dbh = DBI
-> connect ( $dsn , "" , "" ,{ RaiseError
=> 1 , AutoCommit
=> 1 }) or die "ERROR $!" ;
60 my $sth = $dbh -> prepare ( 'Select min(TIME_RUN),max(TIME_RUN) from ACCT;' );
62 while ( my @row = $sth -> fetchrow_array ) {
72 $dbh -> do ( "DELETE FROM ACCT;" );
73 $dbh -> do ( "DELETE FROM ACCT_HIST;" );
79 $dbh -> do ( "DELETE FROM ACCT;" );
80 $dbh -> do ( "DELETE FROM ACCT_HIST;" );
81 $dbh -> do ( "DELETE FROM ACCT_ADDR " );
82 $dbh -> do ( "DELETE FROM BILLINGGRP" );
83 $dbh -> do ( "DELETE FROM BILLINGHOST" );
90 $dbh -> do ( "DELETE FROM ACCT WHERE TIME_RUN < " . $till . ";" );
91 $dbh -> do ( "DELETE FROM ACCT_HIST WHERE TIME_RUN < date('" . $till . "','unixepoch');" );
96 $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');" );
97 $dbh -> do ( "DELETE FROM ACCT WHERE datetime(TIME_RUN,'unixepoch') < date('now','-2 months');" );
100 sub gethourgraphdata
{
107 if ( $table eq 'ACCT' ){
108 $res = $dbh -> selectall_arrayref ( "SELECT TIME_RUN,BYTES FROM ACCT WHERE TIME_RUN BETWEEN " . $from . " AND " . $till . " AND NAME = '" . $name . "';" );
110 $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 . "';" );
115 sub getmonthgraphdata
{
122 if ( $table eq 'ACCT' ){
123 $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;" );
125 $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;" );
151 #COMPANY,TYPE,NAME1,STR,NR,POSTCODE,CITY,BANK,IBAN,BLZ,ACCOUNT,EMAIL,INTERNET,HRB,USTID,TEL,FAX
152 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 ( ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)" ;
153 my $sth = $dbh -> prepare ( $sql );
154 $sth -> execute ( $comp , $type , $name1 , $str , $nr , $post , $city , $bank , $iban , $bic , $blz , $kto , $mail , $inet , $hrb , $ustid , $tel , $fax );
177 my $oldname = $_ [ 18 ];
179 $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." ;
186 $dbh -> do ( "DELETE FROM ACCT_ADDR WHERE COMPANY=?" , undef , $comp ) or die "Could not delete address $comp !" ;
192 my $res = $dbh -> selectall_arrayref ( "SELECT (SELECT COUNT(NAME) FROM BILLINGGRP GROUP BY NAME),* FROM ACCT_ADDR ORDER BY COMPANY;" );
199 my $res = $dbh -> selectall_arrayref ( "SELECT NAME from ACCT GROUP BY NAME ORDER BY NAME;" );
206 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;" );
211 sub getextrabillpos
{
214 my $res = $dbh -> selectall_arrayref ( "SELECT * from BILLPOS WHERE GRP =?" , undef , $grp );
227 my $sql = "INSERT INTO BILLINGGRP (NAME,BILLTEXT,HOST,CUST,CENT) VALUES (?,?,?,?,?)" ;
228 my $sth = $dbh -> prepare ( $sql );
229 $sth -> execute ( $grp , $txt , $host , $cust , $ust );
230 foreach my $ip ( @ips ){
231 my $sql = "INSERT INTO BILLINGHOST (GRP,HOST) VALUES (?,?)" ;
232 my $sth = $dbh -> prepare ( $sql ) or die "Could not prepare insert into BILLINGHOST $!" ;
233 $sth -> execute ( $grp , $ip ) or die "Could not execute INSERT into BILLINGHOST $!" ;
238 sub updatebillgrouphost
{
242 my $sql = "UPDATE BILLINGGRP SET HOST=? WHERE HOST=?;" ;
243 my $sth = $dbh -> prepare ( $sql );
244 $sth -> execute ( $newgrp , $oldgrp );
248 sub updatebillgroupcust
{
252 my $sql = "UPDATE BILLINGGRP SET CUST=? WHERE CUST=?;" ;
253 my $sth = $dbh -> prepare ( $sql );
254 $sth -> execute ( $newgrp , $oldgrp );
258 sub deletebillgroup
{
261 $dbh -> do ( "DELETE FROM BILLINGGRP WHERE NAME=?;" , undef , $name );
262 $dbh -> do ( "DELETE FROM BILLINGHOST WHERE GRP=?;" , undef , $name );
272 my $sql = "INSERT INTO BILLPOS (GRP,AMOUNT,POS,PRICE) VALUES (?,?,?,?)" ;
273 my $sth = $dbh -> prepare ( $sql ) or die "Could not prepare insert into BILLINGPOS $!" ;
274 $sth -> execute ( $grp , $amnt , $pos , $price ) or die "Could not execute INSERT into BILLINGHOST $!" ;
282 my $sql = "UPDATE BILLPOS SET GRP=? WHERE GRP=?;" ;
283 my $sth = $dbh -> prepare ( $sql );
284 $sth -> execute ( $newgrp , $oldgrp );
285 my $sql1 = "UPDATE BILLS SET GRP=? WHERE GRP=?;" ;
286 my $sth1 = $dbh -> prepare ( $sql1 );
287 $sth1 -> execute ( $newgrp , $oldgrp );
289 #Now rename directories
290 rename ( "/srv/web/ipfire/html/accounting/logo/ $oldgrp " , "/srv/web/ipfire/html/accounting/logo/ $newgrp " );
291 rename ( "/var/ipfire/accounting/bill/ $oldgrp " , "/var/ipfire/accounting/bill/ $newgrp " )
295 sub delbillpos_single
{
298 my $sql = "DELETE FROM BILLPOS WHERE GRP=? AND POS=?;" ;
300 my $sth = $dbh -> prepare ( $sql ) or die "Could not prepare DELETE POS from BILLINGPOS $!" ;
301 $sth -> execute ( $grp , $pos ) or die "Could not execute DELETE from BILLINGHOST $!" ;
307 my $sql = "DELETE FROM BILLPOS WHERE GRP=?;" ;
309 my $sth = $dbh -> prepare ( $sql ) or die "Could not prepare DELETE POS from BILLINGPOS $!" ;
310 $sth -> execute ( $grp ) or die "Could not execute DELETE from BILLINGHOST $!" ;
317 my $res = $dbh -> selectall_arrayref ( "SELECT * FROM BILLINGHOST WHERE GRP='" . $name . "';" );
318 foreach my $gzu ( @
$res ){
327 my $res = $dbh -> selectall_arrayref ( "SELECT * FROM BILLINGHOST;" );
333 #GET : 1. month 2. year
334 #GIVES: 1.day of given month AND last day of given month in seconds since 1.1.1970
335 ( $sec , $min , $hour , $mday , $mon , $year , $wday , $ydat , $isdst )= localtime ();
337 my $monat = $_ [ 0 ]- 1 if ( $_ [ 0 ]);
339 my $time1 = timelocal
( 0 , 0 , 0 , $tag , $monat , $jahr );
340 my $time2 = timelocal
( 0 , 0 , 0 , $tag ,( $monat + 1 ), $jahr );
342 return ( $time1 , $time2 );
347 my $from = $_ [ 0 ]; #unixtimestamp
348 my $till = $_ [ 1 ]; #unixtimestamp
349 my $grp = $_ [ 2 ]; #Billgroupname
350 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 $!" ;
359 foreach my $row ( @
$all ){
360 my ( $bytes , $billgrp , $host ) = @
$row ;
361 $return [ $cnt ]= " $bytes , $billgrp , $host " ;
373 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 );
381 my $res = $dbh -> selectall_arrayref ( "SELECT NAME,HOST,CUST FROM BILLINGGRP;" );
387 my @billar = @
{ $_ [ 0 ]}; #DATA from sendbill (just host/values)
389 $month = '0' . $month if $month < 10 ;
392 my @address_cust = @
{ $_ [ 4 ]}; #Array which contains customer and hoster adresses and some additional info from billgroup
393 my @address_host = @
{ $_ [ 5 ]};
394 my @billpos = @
{ $_ [ 6 ]};
396 my $cur = $_ [ 8 ]; #(Eur,USD,...)
399 my $name = $month . "-" . $year . "-" . $no . ".pdf" ;
400 my $path = "/var/ipfire/accounting/bill/" ;
401 my $filename = " $path / $grp / $name " ;
402 my @summen ; #Used for counting the sums
407 my $anzbillpos = @billpos ;
408 my $anz = ( @billar + $anzbillpos )/ 18 ; #Total pages
409 $anz = ceil
( $anz ); #round the $anz value
423 my $fulldate = strftime
( ' %d . %m . %Y ' , localtime ( time ()));
424 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 );
425 my ( $company_cust , $type_cust , $name1_cust , $str_cust , $str_nr_cust , $plz_cust , $city_cust );
427 #First of all check if directory exists, else create it
428 if (! - d
" $path / $grp " && $preview ne 'on' ){
429 mkdir ( " $path / $grp " , 0777 );
432 #Check if we are creating a preview or a real bill
433 if ( $preview eq 'on' ){
434 $filename = " $path /" . tempfile
( SUFFIX
=> ".pdf" , );
436 ####################################################################
437 #Prepare DATA from arrays
438 ####################################################################
439 #Get HOSTER for this grp
440 foreach my $addrline ( @address_host ){
441 ( $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 ;
443 #Get CUST for this grp
444 foreach my $addrline_cust ( @address_cust ){
445 ( $company_cust , $type_cust , $name1_cust , $str_cust , $str_nr_cust , $plz_cust , $city_cust )= @
$addrline_cust ;
450 my $pdf = PDF
:: API2
-> new (- file
=> $filename );
451 $pdf -> mediabox ( 'A4' );
452 my $page = $pdf -> page ;
453 $fnt = $pdf -> corefont ( 'Helvetica' );
454 $fnt1 = $pdf -> corefont ( 'HelveticaBold' );
459 $lines -> strokecolor ( 'grey' );
460 $lines -> linewidth ( '0.5' );
462 #Fill BILL DATA into PDF
463 setlocale
( LC_ALL
, " $mainsettings {'LANGUAGE'}_ $uplang " );
465 my ( $a1 , $a2 ) = split ( /\,/ , $_ );
466 $a2 = sprintf "%.2f" ,( $a2 /1024/ 1024 );
467 my $sum =(( $a2 )* $cent );
468 $sum = sprintf "%.2f" ,( $sum );
469 # Seitenwechsel ermitteln
471 $txt1 -> translate ( 390 , 120 );
472 $txt1 -> text ( $Lang :: tr
{ 'acct pdf zwsum' }); #Pos
473 $zwsum = sprintf ( "%.2f" ,( $zwsum ));
474 $txt1 -> translate ( 540 , 120 );
475 $txt1 -> text_right ( " $zwsum " . decode
( 'utf8' , $cur )); #Pos
484 $lines -> strokecolor ( 'grey' );
485 $lines -> linewidth ( '0.5' );
489 $title -> linewidth ( 14 );
490 $title -> move ( 385 , 168 );
491 $title -> line ( 545 , 168 ); #Title of SUMBOX
492 $title -> move ( 60 , 523 );
493 $title -> line ( 545 , 523 ); #Bottom horiz. line of Title
496 $lines -> move ( 59 , 745 );
497 $lines -> line ( 545 , 745 );
498 $lines -> move ( 59 , 563 );
499 $lines -> line ( 545 , 563 );
502 $lines -> move ( 61 , 710 );
503 $lines -> line ( 61 , 715 , 66 , 715 ); #TL
504 $lines -> move ( 61 , 610 );
505 $lines -> line ( 61 , 605 , 66 , 605 ); #BL
506 $lines -> move ( 285 , 715 );
507 $lines -> line ( 290 , 715 , 290 , 710 ); #TR
508 $lines -> move ( 290 , 610 );
509 $lines -> line ( 290 , 605 , 285 , 605 ); #BR
511 # Table for positions
512 $lines -> move ( 60 , 530 );
513 $lines -> line ( 60 , 200 ); #First vert. line POS
514 $lines -> move ( 90 , 523 );
515 $lines -> line ( 90 , 200 ); #Second vert. line
516 $lines -> move ( 280 , 523 );
517 $lines -> line ( 280 , 200 ); #third vert. line
518 $lines -> move ( 385 , 523 );
519 $lines -> line ( 385 , 200 ); #third vert. line
520 $lines -> move ( 430 , 523 );
521 $lines -> line ( 430 , 200 ); #fourth vert. line
522 $lines -> move ( 545 , 530 );
523 $lines -> line ( 545 , 200 ); #fifth vert. line
524 $lines -> move ( 60 , 200 );
525 $lines -> line ( 545 , 200 ); #Bottom horizontal line
528 $lines -> move ( 385 , 175 );
529 $lines -> line ( 385 , 115 ); #Left vert. line of SUMBOX
530 $lines -> move ( 545 , 175 );
531 $lines -> line ( 545 , 115 ); #Right vert. line of SUMBOX
532 $lines -> move ( 385 , 115 );
533 $lines -> line ( 545 , 115 ); #Bottom horiz. line of SUMBOX
535 #Lines on right side after sender and after "bank"
536 $lines -> move ( 420 , 723 );
537 $lines -> line ( 545 , 723 ); # Line "Sender"
538 $lines -> move ( 420 , 648 );
539 $lines -> line ( 545 , 648 ); # Line "Bank"
540 $lines -> move ( 420 , 600 );
541 $lines -> line ( 545 , 600 ); # Line HRB/USTID
546 if (- f
"/srv/web/ipfire/html/accounting/logo/ $grp /logo.png" ){
548 my $gfx = $page -> gfx ;
549 my $image = $pdf -> image_png ( "/srv/web/ipfire/html/accounting/logo/ $grp /logo.png" );
550 my $width = $image -> width ;
551 my $height = $image -> height ;
552 $gfx -> image ( $image , ( 545 +( $width / 2 ))- $width , 750 , 0.5 );
562 $txt -> textstart ; #Begin Text
563 $txt -> font ( $fnt , 10 ); #Set fontsize for font1
564 $txt1 -> font ( $fnt , 8 ); #Set fontsize for font2
565 $txt2 -> font ( $fnt1 , 10 ); #Set fontsize for font3
566 $txt3 -> font ( $fnt1 , 16 ); #Set fontsize for font4
567 $txt4 -> font ( $fnt , 6 ); #Set fontsize for font5
568 $txt5 -> font ( $fnt1 , 6 ); #Set fontsize for font6
570 #if $cent not set, set it to 0.5
571 if (! $cent ){ $cent = '0.005' ;}
573 #if MWst not set, set it to 19%
574 if (! $mwst ){ $mwst = '19' ;}
577 $txt1 -> translate ( 65 , 520 );
578 $txt1 -> text ( $Lang :: tr
{ 'acct pos' }); #Pos
579 $txt1 -> translate ( 95 , 520 );
580 $txt1 -> text ( $Lang :: tr
{ 'acct name' }); #Host/Name
581 $txt1 -> translate ( 285 , 520 );
582 $txt1 -> text ( $Lang :: tr
{ 'acct amount' }); #Traffic
583 $txt1 -> translate ( 390 , 520 );
584 $txt1 -> text ( $Lang :: tr
{ 'acct cent1' }); #Price /MB
585 $txt1 -> translate ( 435 , 520 );
586 $txt1 -> text ( $Lang :: tr
{ 'acct pdf price' }); #Sum
588 ####################################################################
589 #Fill Recipient address
590 my $rec_name = " $company_cust " ;
591 my $rec_name1 = " $name1_cust " ;
592 my $rec_str = " $str_cust $str_nr_cust " ;
593 my $rec_city = " $plz_cust $city_cust " ;
596 $txt2 -> translate ( 78 , 685 );
597 $txt2 -> text ( decode
( 'utf8' , $rec_name ));
599 $txt1 -> translate ( 78 , $o );
600 $txt1 -> text ( decode
( 'utf8' , $rec_name1 ));
605 $txt1 -> translate ( 78 , $o );
606 $txt1 -> text ( decode
( 'utf8' , $rec_str ));
608 $txt1 -> translate ( 78 , $o );
609 $txt1 -> text ( decode
( 'utf8' , $rec_city ));
612 my $send_name = " $company_host " ;
613 my $send_str = " $str_host $str_nr_host " ;
614 my $send_city = " $plz_host $city_host " ;
615 my $send_bank = " $bank " ;
617 $txt5 -> translate ( 420 , 725 );
618 $txt5 -> text ( decode
( 'utf8' , $Lang :: tr
{ 'acct pdf prov' }));
619 $txt5 -> translate ( 420 , 715 );
620 $txt5 -> text ( decode
( 'utf8' , $send_name ));
623 $txt4 -> translate ( 420 , $j );
624 $txt4 -> text ( decode
( 'utf8' , $name1_host ));
627 $txt4 -> translate ( 420 , $j );
628 $txt4 -> text ( decode
( 'utf8' , $send_str )); #STR
630 $txt4 -> translate ( 420 , $j );
631 $txt4 -> text ( decode
( 'utf8' , $send_city )); #PLZ.City
632 #Print optional Values tel,fax
635 $txt4 -> translate ( 420 , $i );
636 $txt4 -> text ( $Lang :: tr
{ 'acct tel' }); #Tel
637 $txt4 -> translate ( 480 , $i );
638 $txt4 -> text ( $tel_host ); #Telnr
642 $txt4 -> translate ( 420 , $i );
643 $txt4 -> text ( $Lang :: tr
{ 'acct fax' }); #Fax
644 $txt4 -> translate ( 480 , $i );
645 $txt4 -> text ( $fax_host ); #Faxnr
649 $txt4 -> translate ( 420 , $i );
650 $txt4 -> text ( $Lang :: tr
{ 'acct inet' }); #Internet
651 $txt4 -> translate ( 480 , $i );
652 $txt4 -> text ( $internet ); #www-address
655 $txt5 -> translate ( 420 , 650 );
656 $txt5 -> text ( decode
( 'utf8' , $Lang :: tr
{ 'acct bank' })); #"BANK"
658 $txt4 -> translate ( 420 , 640 );
659 $txt4 -> paragraph ( decode
( 'utf8' , $bank ), 130 , 20 , - align
=> "justify" ); #Bankname
661 $txt4 -> translate ( 420 , 625 );
662 $txt4 -> text ( $Lang :: tr
{ 'acct iban' }); #iban
663 $txt4 -> translate ( 480 , 625 );
664 $txt4 -> text ( decode
( 'utf8' , $iban )); #iban
665 $txt4 -> translate ( 420 , 619 );
666 $txt4 -> text ( $Lang :: tr
{ 'acct bic' }); #bic
667 $txt4 -> translate ( 480 , 619 );
668 $txt4 -> text ( decode
( 'utf8' , $bic )); #bic
671 $txt4 -> translate ( 420 , 613 );
672 $txt4 -> text ( $Lang :: tr
{ 'acct blz' }); #blz
673 $txt4 -> translate ( 420 , 607 );
674 $txt4 -> text ( $Lang :: tr
{ 'acct kto' }); #kto
675 $txt4 -> translate ( 480 , 613 );
676 $txt4 -> text ( decode
( 'utf8' , $blz )); #blz
677 $txt4 -> translate ( 480 , 607 );
678 $txt4 -> text ( decode
( 'utf8' , $kto )); #kto
681 #Print USTID and optional HRB
682 $txt4 -> translate ( 420 , 590 );
683 $txt4 -> text ( $Lang :: tr
{ 'acct ustid' }); #USTID
684 $txt4 -> translate ( 480 , 590 );
685 $txt4 -> text ( $stnr ); #ustid
687 $txt4 -> translate ( 420 , 580 );
688 $txt4 -> text ( $Lang :: tr
{ 'acct hrb' }); #USTID
689 $txt4 -> translate ( 480 , 580 );
690 $txt4 -> text ( $hrb ); #ustid
692 ################################################################
694 #Print Date, Pages ....
695 $txt3 -> translate ( 59 , 545 );
696 $txt3 -> text ( $Lang :: tr
{ 'acct pdf billtxt' });
697 $txt1 -> translate ( 160 , 545 );
698 $txt1 -> text ( " $no $Lang ::tr{'acct billnr'}" );
699 $txt1 -> translate ( 60 , 532 );
700 $txt1 -> text ( " $Lang ::tr{'acct pdf time'} $month / $year " );
701 $txt1 -> translate ( 545 , 550 );
702 $txt1 -> text_right ( " $Lang ::tr{'acct pdf date'} $fulldate " );
703 $txt1 -> translate ( 545 , 532 );
704 $txt1 -> text_right ( " $Lang ::tr{'acct pdf page'} $aktpage / $anz " );
706 if ( $a1 eq '999' ){ last ;}
707 #Print DATA from array to Position table
708 $txt1 -> translate ( 80 , $x );
709 $txt1 -> text_right ( $y );
710 $txt1 -> translate ( 95 , $x );
712 $txt1 -> translate ( 380 , $x );
713 $txt1 -> text_right ( " $a2 MB" );
714 $txt1 -> translate ( 425 , $x );
715 $txt1 -> text_right ( " $cent " . decode
( 'utf8' , $cur ));
716 $txt1 -> translate ( 540 , $x );
717 $txt1 -> text_right ( " $sum " . decode
( 'utf8' , $cur ));
720 $summen [ $y - 1 ]= " $y , $a2 , $sum " ;
725 #Print extra billpositions
726 foreach my $line ( @billpos ){
727 my ( $grp , $amount , $art , $price )= @
$line ;
728 #Print DATA from array to Position table
729 $txt1 -> translate ( 80 , $x );
730 $txt1 -> text_right ( $y );
731 $txt1 -> translate ( 95 , $x );
732 $txt1 -> text ( decode
( 'utf8' , $art ));
733 $txt1 -> translate ( 380 , $x );
734 $txt1 -> text_right ( $amount . " pcs" );
735 $txt1 -> translate ( 540 , $x );
736 $txt1 -> text_right ( " $price " . decode
( 'utf8' , $cur ));
738 my $zu = $amount * $price ;
739 $summen [ $y - 1 ]= " $y ,'0', $zu " ;
745 my ( $a1 , $a2 , $a3 ) = split ( /\,/ , $_ );
750 # Last Line in positiontable prints the sum of all traffic (therefor txt2 which is BOLD)
751 $txt2 -> translate ( 95 , 205 );
752 $txt2 -> text ( $Lang :: tr
{ 'acct pdf sum1' }); #SUM
753 $txt2 -> translate ( 427 , 205 );
754 $txt2 -> text_right ( $cent ); #cent
755 $txt2 -> translate ( 380 , 205 );
756 $txt2 -> text_right ( " $sum MB" ); #MB
757 $sum1 = sprintf ( "%.2f" ,( $sum1 ));
758 $txt2 -> translate ( 540 , 205 );
759 $txt2 -> text_right ( " $sum1 " . decode
( 'utf8' , $cur )); #SUM Eur
760 $txt -> translate ( 390 , 150 );
761 $txt -> text ( $Lang :: tr
{ 'acct pdf sum1' });
762 $txt -> translate ( 540 , 150 );
763 $txt -> text_right ( " $sum1 " . decode
( 'utf8' , $cur ));
764 $txt -> translate ( 390 , 135 );
766 $txt -> text ( " $Lang ::tr{'acct mwst_name'} $mwst %" );
767 my $sum1 = sprintf ( "%.2f" ,( $sum1 / 100 * $mwst ));
768 $txt -> translate ( 540 , 135 );
769 $txt -> text_right ( " $sum1 " . decode
( 'utf8' , $cur ));
770 $txt2 -> translate ( 390 , 120 );
771 $txt2 -> text ( $Lang :: tr
{ 'acct sum total' });
772 my $endsum = sprintf ( "%.2f" ,( $sum1 + $endsum ));
773 $txt2 -> translate ( 540 , 120 );
774 $txt2 -> text_right ( " $endsum " . decode
( 'utf8' , $cur ));
776 #Print the optional Billtext if any
777 $txt4 -> translate ( 60 , 170 );
778 $txt4 -> paragraph ( decode
( 'utf8' , $text ), 300 , 40 , - align
=> "justify" ); #Bankname
780 #Watermark if preview
781 if ( $preview eq 'on' ){
782 my $eg_trans = $pdf -> egstate ();
783 $eg_trans -> transparency ( 0.9 );
784 $txt5 -> egstate ( $eg_trans );
785 $txt5 -> textlabel ( 80 , 400 , $fnt , 60 , "PDF preview" , - rotate
=> 40 );
786 $txt5 -> textlabel ( 150 , 330 , $fnt , 60 , "IPFire accounting" , - rotate
=> 40 );
789 $txt -> textend ; #END Text
790 $pdf -> save ; #Save pdf
792 if ( $preview ne 'on' ){
793 & fillBill
( $path . $grp , $name , $no , $grp );
795 if ( $preview eq 'on' ){
803 my $year1 = $year + 1900 ;
805 my $res = $dbh -> selectall_arrayref ( "SELECT MAX(NO) FROM BILLS;" );
806 foreach my $row ( @
$res ){
809 if (! $no1 ){ $no1 = $year1 . "1000" ;}
819 my $sth = $dbh -> prepare ( "INSERT INTO BILLS (NO,GRP,PATH,NAME,DATE) VALUES (?,?,?,?,?);" );
820 my $year1 = $year + 1900 ;
822 $sth -> execute ( $no , $grp , $path , $name , " $mday . $mon . $year1 " );
830 my $res = $dbh -> selectall_arrayref ( "SELECT * FROM BILLS WHERE GRP=?;" , undef , $grp );
837 my ( $width , $height ) = ( undef , undef );
839 if ( $Buffer =~ /IHDR(.{8})/ ) {
841 ( $width , $height ) = unpack ( "NN" , $PNG );
843 $width = $Lang :: tr
{ 'acct invalid png' };
845 return ( $width , $height );
850 my ( $type , $a , $b , $c , $d , $s , $width , $height ) ;
851 $type = substr ( $GIF , 0 , 6 );
852 if (!( $type =~ m/GIF8[7,9]a/ ) || ( length ( $s = substr ( $GIF , 6 , 4 ))!= 4 ) ){
855 ( $a , $b , $c , $d )= unpack ( "C" x4
, $s );
859 return ( $width , $height );
865 my ( $length )= length ( $JPEG ) ;
867 my ( $c1 , $c2 , $a , $b , $c , $d , $width , $height ) ;
869 while (( $ch ne " \xda " ) && ( $count < $length )) {
870 while (( $ch ne " \xff " ) && ( $count < $length )) {
871 $ch = substr ( $JPEG , $count , 1 );
875 while (( $ch eq " \xff " ) && ( $count < $length )) {
876 $ch = substr ( $JPEG , $count , 1 );
880 if (( ord ( $ch ) >= 0xC0 ) && ( ord ( $ch ) <= 0xC3 )) {
882 ( $a , $b , $c , $d )= unpack ( "C" x4
, substr ( $JPEG , $count , 4 ));
885 return ( $width , $height );
887 ( $c1 , $c2 )= unpack ( "C" x2
, substr ( $JPEG , $count , 2 ));
888 $count += $c1 << 8 | $c2 ;
894 ( $sec , $min , $hour , $mday , $mon , $year , $wday , $ydat , $isdst )= localtime ();
895 $hour = sprintf ( " %02d " , $hour );
896 $min = sprintf ( " %02d " , $min );
897 $sec = sprintf ( " %02d " , $sec );
899 $mday = sprintf ( " %02d " , $mday );
900 $mon = sprintf ( " %02d " , $mon + 1 );
901 my $res = " $mday . $mon . $year $hour : $min : $sec - " ;
909 if ( $settings eq 'on' ){
910 open ACCTLOG
, ">>/var/log/accounting.log" || print "could not open /var/log/accounting.log " ;
911 print ACCTLOG
& time . " $msg " ;
920 $dbh -> do ( "UPDATE ACCT_ADDR SET CCMAIL=? WHERE COMPANY=? ;" , undef , $addr , $cust );