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