]>
Commit | Line | Data |
---|---|---|
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 | ############################################################################### | |
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; | |
a5f5ccfc | 30 | use File::Temp qw/ tempfile tempdir /; |
db8a01e0 AM |
31 | |
32 | ############################################################################### | |
33 | my $dbh; | |
34 | my $dsn="dbi:SQLite:dbname=/var/ipfire/accounting/acct.db"; | |
35 | my ($sec,$min,$hour,$mday,$mon,$year,$wday,$ydat,$isdst)=localtime(); | |
36 | my %mainsettings; | |
37 | ############################################################################### | |
38 | ||
39 | &General::readhash("/var/ipfire/main/settings", \%mainsettings); | |
40 | my $uplang=uc($mainsettings{'LANGUAGE'}); | |
41 | ||
42 | ############# | |
43 | # Functions # | |
44 | ############# | |
45 | ||
46 | sub connectdb { | |
47 | $dbh = DBI->connect($dsn, "", "",{RaiseError => 1, AutoCommit => 1})or die "ERROR $!"; | |
48 | return $dbh; | |
49 | } | |
50 | ||
51 | sub closedb { | |
52 | $dbh->disconnect(); | |
53 | return $dbh; | |
54 | } | |
55 | ||
56 | sub 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 | ||
70 | sub cleardbtraf { | |
71 | &connectdb; | |
72 | $dbh->do("DELETE FROM ACCT;"); | |
73 | $dbh->do("DELETE FROM ACCT_HIST;"); | |
74 | &closedb; | |
75 | } | |
76 | ||
77 | sub 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 | ||
87 | sub 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 | ||
95 | sub movedbdata { | |
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');"); | |
98 | } | |
99 | ||
100 | sub gethourgraphdata { | |
101 | my $table=$_[0]; | |
102 | my $from=$_[1]; | |
103 | my $till=$_[2]; | |
104 | my $name=$_[3]; | |
105 | my $res; | |
106 | $dbh=connectdb; | |
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."';"); | |
109 | }else{ | |
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."';"); | |
111 | } | |
112 | return $res; | |
113 | } | |
114 | ||
115 | sub getmonthgraphdata { | |
116 | my $table=$_[0]; | |
117 | my $from=$_[1]; | |
118 | my $till=$_[2]; | |
119 | my $name=$_[3]; | |
120 | my $res; | |
121 | $dbh=connectdb; | |
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;"); | |
124 | }else{ | |
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;"); | |
126 | } | |
127 | $dbh=closedb; | |
128 | return $res; | |
129 | } | |
130 | ||
131 | sub writeaddr { | |
132 | my $comp = $_[0]; | |
133 | my $type = $_[1]; | |
134 | my $name1 = $_[2]; | |
135 | my $str = $_[3]; | |
136 | my $nr = $_[4]; | |
137 | my $post = $_[5]; | |
138 | my $city = $_[6]; | |
139 | my $bank = $_[7]; | |
140 | my $iban = $_[8]; | |
141 | my $bic = $_[9]; | |
142 | my $blz = $_[10]; | |
143 | my $kto = $_[11]; | |
144 | my $mail = $_[12]; | |
145 | my $inet = $_[13]; | |
146 | my $hrb = $_[14]; | |
147 | my $ustid = $_[15]; | |
148 | my $tel = $_[16]; | |
149 | my $fax = $_[17]; | |
150 | $dbh=&connectdb; | |
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 ); | |
155 | $dbh=&closedb; | |
156 | } | |
157 | ||
158 | sub updateaddr { | |
159 | my $type = $_[0]; | |
160 | my $comp = $_[1]; | |
161 | my $name1 = $_[2]; | |
162 | my $str = $_[3]; | |
163 | my $nr = $_[4]; | |
164 | my $post = $_[5]; | |
165 | my $city = $_[6]; | |
166 | my $bank = $_[7]; | |
167 | my $iban = $_[8]; | |
168 | my $bic = $_[9]; | |
169 | my $blz = $_[10]; | |
170 | my $kto = $_[11]; | |
171 | my $mail = $_[12]; | |
172 | my $inet = $_[13]; | |
173 | my $hrb = $_[14]; | |
174 | my $ustid = $_[15]; | |
175 | my $tel = $_[16]; | |
176 | my $fax = $_[17]; | |
177 | my $oldname = $_[18]; | |
178 | $dbh=&connectdb; | |
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."; | |
180 | $dbh=&closedb; | |
181 | } | |
182 | ||
183 | sub deladdr { | |
184 | my $comp = $_[0]; | |
185 | $dbh=&connectdb; | |
186 | $dbh->do("DELETE FROM ACCT_ADDR WHERE COMPANY=?", undef,$comp )or die "Could not delete address $comp!"; | |
187 | $dbh=&closedb; | |
188 | } | |
189 | ||
190 | sub getaddresses { | |
191 | $dbh=&connectdb; | |
192 | my $res=$dbh->selectall_arrayref("SELECT (SELECT COUNT(NAME) FROM BILLINGGRP GROUP BY NAME),* FROM ACCT_ADDR ORDER BY COMPANY;"); | |
193 | $dbh=&closedb; | |
194 | return $res; | |
195 | } | |
196 | ||
197 | sub gethosts{ | |
198 | $dbh=&connectdb; | |
199 | my $res =$dbh->selectall_arrayref("SELECT NAME from ACCT GROUP BY NAME ORDER BY NAME;"); | |
200 | $dbh=&closedb; | |
201 | return $res; | |
202 | } | |
203 | ||
204 | sub getbillgroups { | |
205 | $dbh=connectdb; | |
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;"); | |
207 | return $res; | |
208 | $dbh->disconnect(); | |
209 | } | |
210 | ||
211 | sub getextrabillpos { | |
212 | my $grp=$_[0]; | |
213 | $dbh=&connectdb; | |
214 | my $res=$dbh->selectall_arrayref("SELECT * from BILLPOS WHERE GRP =?", undef,$grp); | |
215 | $dbh=&closedb; | |
216 | return $res; | |
217 | } | |
218 | ||
219 | sub savebillgroup { | |
220 | my $grp=$_[0]; | |
221 | my $txt=$_[1]; | |
222 | my $host=$_[2]; | |
223 | my $cust=$_[3]; | |
224 | my $ust=$_[4]; | |
225 | my @ips=@{$_[5]}; | |
226 | $dbh=&connectdb; | |
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 $!"; | |
234 | } | |
235 | $dbh=&closedb; | |
236 | } | |
237 | ||
238 | sub updatebillgrouphost { | |
239 | my $oldgrp=$_[0]; | |
240 | my $newgrp=$_[1]; | |
241 | $dbh=&connectdb; | |
242 | my $sql = "UPDATE BILLINGGRP SET HOST=? WHERE HOST=?;"; | |
243 | my $sth = $dbh->prepare( $sql ); | |
244 | $sth->execute( $newgrp,$oldgrp ); | |
245 | $dbh=&closedb; | |
246 | } | |
247 | ||
248 | sub updatebillgroupcust { | |
249 | my $oldgrp=$_[0]; | |
250 | my $newgrp=$_[1]; | |
251 | $dbh=&connectdb; | |
252 | my $sql = "UPDATE BILLINGGRP SET CUST=? WHERE CUST=?;"; | |
253 | my $sth = $dbh->prepare( $sql ); | |
254 | $sth->execute( $newgrp,$oldgrp ); | |
255 | $dbh=&closedb; | |
256 | } | |
257 | ||
258 | sub deletebillgroup { | |
259 | my $name=shift; | |
260 | $dbh=connectdb; | |
261 | $dbh->do("DELETE FROM BILLINGGRP WHERE NAME=?;", undef,$name); | |
262 | $dbh->do("DELETE FROM BILLINGHOST WHERE GRP=?;", undef,$name); | |
263 | &closedb; | |
264 | } | |
265 | ||
266 | sub savebillpos { | |
267 | my $grp=$_[0]; | |
268 | my $amnt=$_[1]; | |
269 | my $pos=$_[2]; | |
270 | my $price=$_[3]; | |
271 | $dbh=&connectdb; | |
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 $!"; | |
275 | $dbh->disconnect(); | |
276 | } | |
277 | ||
278 | sub updatebillpos { | |
279 | my $oldgrp=shift; | |
280 | my $newgrp=shift; | |
281 | $dbh=&connectdb; | |
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 ); | |
288 | $dbh=&closedb; | |
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") | |
292 | ||
293 | } | |
294 | ||
295 | sub delbillpos_single { | |
296 | my $pos=$_[0]; | |
297 | my $grp=$_[1]; | |
298 | my $sql = "DELETE FROM BILLPOS WHERE GRP=? AND POS=?;"; | |
299 | $dbh=&connectdb; | |
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 $!"; | |
302 | $dbh=&closedb; | |
303 | } | |
304 | ||
305 | sub delbillpos { | |
306 | my $grp=$_[0]; | |
307 | my $sql = "DELETE FROM BILLPOS WHERE GRP=?;"; | |
308 | $dbh=&connectdb; | |
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 $!"; | |
311 | $dbh=&closedb; | |
312 | } | |
313 | ||
314 | sub listhosts{ | |
315 | my $name=$_[0]; | |
316 | my $a; | |
317 | my $res=$dbh->selectall_arrayref("SELECT * FROM BILLINGHOST WHERE GRP='".$name."';"); | |
318 | foreach my $gzu (@$res){ | |
319 | my ($x,$y)=@$gzu; | |
320 | $a.= "|$y"; | |
321 | } | |
322 | return $a; | |
323 | } | |
324 | ||
325 | sub checkusergrp { | |
326 | $dbh=connectdb; | |
327 | my $res=$dbh->selectall_arrayref("SELECT * FROM BILLINGHOST;"); | |
328 | $dbh->disconnect(); | |
329 | return $res; | |
330 | } | |
331 | ||
332 | sub getmonth{ | |
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(); | |
336 | my $jahr=$_[1]; | |
337 | my $monat=$_[0]-1 if($_[0]); | |
338 | my $tag=1; | |
339 | my $time1=timelocal(0,0,0,$tag,$monat,$jahr); | |
340 | my $time2=timelocal(0,0,0,$tag,($monat+1),$jahr); | |
341 | --$time2; | |
342 | return ($time1,$time2); | |
343 | } | |
344 | ||
345 | sub GetTaValues { | |
346 | $dbh=&connectdb; | |
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 $!"; | |
351 | my $nri1 = @$all; | |
352 | my @return; | |
353 | my $cnt=0; | |
354 | if ($nri1 eq "0"){ | |
355 | $return[$cnt]="999"; | |
356 | } | |
357 | else | |
358 | { | |
359 | foreach my $row (@$all){ | |
360 | my ($bytes,$billgrp,$host) = @$row; | |
361 | $return[$cnt]="$bytes,$billgrp,$host"; | |
362 | $cnt++; | |
363 | } | |
364 | } | |
365 | &closedb; | |
366 | return @return; | |
367 | } | |
368 | ||
369 | sub getTaAddress { | |
370 | my $grp=$_[0]; | |
371 | my $type=$_[1]; | |
372 | $dbh=&connectdb; | |
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); | |
374 | &closedb; | |
375 | return $res; | |
376 | } | |
377 | ||
378 | sub checkbillgrp { | |
379 | my $comp=$_[0]; | |
380 | $dbh=&connectdb; | |
381 | my $res=$dbh->selectall_arrayref("SELECT NAME,HOST,CUST FROM BILLINGGRP;"); | |
382 | &closedb; | |
383 | return $res; | |
384 | } | |
385 | ||
386 | sub pdf2 { | |
387 | my @billar = @{$_[0]}; #DATA from sendbill (just host/values) | |
388 | my $month = $_[1]; | |
389 | $month = '0'.$month if $month < 10; | |
390 | my $year = $_[2]; | |
391 | my $mwst = $_[3]; | |
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]}; | |
395 | my $grp = $_[7]; | |
396 | my $cur = $_[8]; #(Eur,USD,...) | |
397 | my $preview = $_[9]; | |
398 | my $no = &getBillNr; | |
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 | |
403 | my $x = 500; | |
404 | my $y = 1; | |
405 | my $zwsum; | |
406 | my $pages = 0; | |
407 | my $anzbillpos = @billpos; | |
408 | my $anz = (@billar+$anzbillpos)/18; #Total pages | |
409 | $anz = ceil($anz); #round the $anz value | |
410 | my $aktpage=1; | |
411 | my $sum=0; | |
412 | my $sum1=0; | |
413 | my $lines; | |
414 | my $title; | |
415 | my $txt; | |
416 | my $txt1; | |
417 | my $txt2; | |
418 | my $txt3; | |
419 | my $txt4; | |
420 | my $txt5; | |
421 | my $fnt; | |
422 | my $fnt1; | |
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); | |
426 | ||
427 | #First of all check if directory exists, else create it | |
428 | if(! -d "$path/$grp" && $preview ne 'on'){ | |
429 | mkdir("$path/$grp",0777); | |
430 | } | |
431 | ||
432 | #Check if we are creating a preview or a real bill | |
433 | if($preview eq 'on'){ | |
a5f5ccfc | 434 | $filename="$path/".tempfile( SUFFIX => ".pdf", ); |
db8a01e0 AM |
435 | } |
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; | |
442 | } | |
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; | |
446 | } | |
447 | ||
448 | ||
449 | #Generate PDF File | |
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'); | |
455 | ||
456 | #Set lines | |
457 | $lines = $page->gfx; | |
458 | $title = $page->gfx; | |
459 | $lines->strokecolor('grey'); | |
460 | $lines->linewidth('0.5'); | |
461 | ||
462 | #Fill BILL DATA into PDF | |
463 | setlocale(LC_ALL, "$mainsettings{'LANGUAGE'}_$uplang"); | |
464 | foreach (@billar) { | |
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 | |
470 | if ($y % 18 == 0) { | |
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 | |
476 | $zwsum=0; | |
477 | $pages++; | |
478 | $aktpage++; | |
479 | $x=500; | |
480 | $page=$pdf->page; | |
481 | #draw lines | |
482 | $lines = $page->gfx; | |
483 | $title = $page->gfx; | |
484 | $lines->strokecolor('grey'); | |
485 | $lines->linewidth('0.5'); | |
486 | } | |
487 | ||
488 | #TITLES | |
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 | |
494 | ||
495 | # Generate Tables | |
496 | $lines->move(59, 745); | |
497 | $lines->line(545, 745); | |
498 | $lines->move(59, 563); | |
499 | $lines->line(545, 563); | |
500 | ||
501 | # Addressbox | |
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 | |
510 | ||
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 | |
526 | ||
527 | #SUM BOX | |
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 | |
534 | ||
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 | |
542 | ||
543 | #Make lines Visible | |
544 | $lines->stroke; | |
545 | $title->stroke; | |
546 | if (-f "/srv/web/ipfire/html/accounting/logo/$grp/logo.png"){ | |
547 | #Image LOGO | |
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); | |
553 | } | |
554 | ||
555 | #Set Fonts | |
556 | $txt = $page->text; | |
557 | $txt1 = $page->text; | |
558 | $txt2 = $page->text; | |
559 | $txt3 = $page->text; | |
560 | $txt4 = $page->text; | |
561 | $txt5 = $page->text; | |
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 | |
569 | ||
570 | #if $cent not set, set it to 0.5 | |
571 | if(!$cent){$cent='0.005';} | |
572 | ||
573 | #if MWst not set, set it to 19% | |
574 | if(!$mwst){$mwst='19';} | |
575 | ||
576 | # Titles | |
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 | |
587 | ||
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"; | |
594 | #INSERT RECIPIENT | |
595 | my $o=675; | |
596 | $txt2->translate(78, 685); | |
597 | $txt2->text(decode('utf8',$rec_name)); | |
598 | if($rec_name1){ | |
599 | $txt1->translate(78, $o); | |
600 | $txt1->text(decode('utf8',$rec_name1)); | |
601 | $o=$o-15; | |
602 | }else{ | |
603 | $o=$o-15; | |
604 | } | |
605 | $txt1->translate(78, $o); | |
606 | $txt1->text(decode('utf8',$rec_str)); | |
607 | $o=$o-10; | |
608 | $txt1->translate(78, $o); | |
609 | $txt1->text(decode('utf8',$rec_city)); | |
610 | ||
611 | # INSERT SENDER | |
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"; | |
616 | ||
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)); | |
621 | my $j=705; | |
622 | if($name1_host){ | |
623 | $txt4->translate(420, $j); | |
624 | $txt4->text(decode('utf8',$name1_host)); | |
625 | $j=$j-8; | |
626 | } | |
627 | $txt4->translate(420, $j); | |
628 | $txt4->text(decode('utf8',$send_str)); #STR | |
629 | $j=$j-8; | |
630 | $txt4->translate(420, $j); | |
631 | $txt4->text(decode('utf8',$send_city)); #PLZ.City | |
632 | #Print optional Values tel,fax | |
633 | my $i=680; | |
634 | if($tel_host){ | |
635 | $txt4->translate(420, $i); | |
636 | $txt4->text($Lang::tr{'acct tel'}); #Tel | |
637 | $txt4->translate(480, $i); | |
638 | $txt4->text($tel_host); #Telnr | |
639 | $i=$i-8; | |
640 | } | |
641 | if($fax_host){ | |
642 | $txt4->translate(420, $i); | |
643 | $txt4->text($Lang::tr{'acct fax'}); #Fax | |
644 | $txt4->translate(480, $i); | |
645 | $txt4->text($fax_host); #Faxnr | |
646 | $i=$i-8; | |
647 | } | |
648 | if($internet){ | |
649 | $txt4->translate(420, $i); | |
650 | $txt4->text($Lang::tr{'acct inet'}); #Internet | |
651 | $txt4->translate(480, $i); | |
652 | $txt4->text($internet); #www-address | |
653 | $i=$i-8; | |
654 | } | |
655 | $txt5->translate(420, 650); | |
656 | $txt5->text(decode('utf8',$Lang::tr{'acct bank'})); #"BANK" | |
657 | $txt4->lead(7); | |
658 | $txt4->translate(420, 640); | |
659 | $txt4->paragraph(decode('utf8',$bank), 130, 20, -align => "justify"); #Bankname | |
660 | if($iban){ | |
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 | |
669 | } | |
670 | if($blz){ | |
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 | |
679 | } | |
680 | ||
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 | |
686 | if($hrb){ | |
687 | $txt4->translate(420, 580); | |
688 | $txt4->text($Lang::tr{'acct hrb'}); #USTID | |
689 | $txt4->translate(480, 580); | |
690 | $txt4->text($hrb); #ustid | |
691 | } | |
692 | ################################################################ | |
693 | ||
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"); | |
705 | ||
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); | |
711 | $txt1->text($a1); | |
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)); | |
718 | ||
719 | #Build SUMMARY | |
720 | $summen[$y-1]="$y,$a2,$sum"; | |
721 | $zwsum=$zwsum+$sum; | |
722 | $x=$x-15; | |
723 | $y++; | |
724 | } | |
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)); | |
737 | #Build SUMMARY | |
738 | my $zu=$amount * $price; | |
739 | $summen[$y-1]="$y,'0',$zu"; | |
740 | $zwsum=$zwsum+$zu; | |
741 | $x=$x-15; | |
742 | $y++; | |
743 | } | |
744 | foreach (@summen){ | |
745 | my ($a1,$a2,$a3) = split( /\,/, $_ ); | |
746 | $sum=$sum+$a2; | |
747 | $sum1=$sum1+$a3; | |
748 | } | |
749 | ||
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); | |
765 | my $endsum=$sum1; | |
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)); | |
775 | ||
776 | #Print the optional Billtext if any | |
777 | $txt4->translate(60, 170); | |
778 | $txt4->paragraph(decode('utf8',$text), 300, 40, -align => "justify"); #Bankname | |
779 | ||
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); | |
787 | } | |
788 | ||
789 | $txt->textend; #END Text | |
790 | $pdf->save; #Save pdf | |
791 | $pdf->end( ); #END | |
792 | if ($preview ne 'on'){ | |
793 | &fillBill($path.$grp,$name,$no,$grp); | |
794 | } | |
a5f5ccfc AM |
795 | if($preview eq 'on'){ |
796 | return $filename; | |
797 | } | |
db8a01e0 AM |
798 | return '0'; |
799 | } | |
800 | ||
801 | sub getBillNr { | |
802 | $dbh=&connectdb; | |
803 | my $year1=$year+1900; | |
804 | my $no1; | |
805 | my $res=$dbh->selectall_arrayref("SELECT MAX(NO) FROM BILLS;"); | |
806 | foreach my $row (@$res){ | |
807 | ($no1) = @$row; | |
808 | } | |
809 | if(!$no1){$no1=$year1."1000";} | |
810 | $no1++; | |
811 | return $no1; | |
812 | } | |
813 | ||
814 | sub fillBill { | |
815 | my $path=$_[0]; | |
816 | my $name=$_[1]; | |
817 | my $no=$_[2]; | |
818 | my $grp=$_[3]; | |
819 | my $sth = $dbh->prepare("INSERT INTO BILLS (NO,GRP,PATH,NAME,DATE) VALUES (?,?,?,?,?);"); | |
820 | my $year1=$year+1900; | |
821 | ++$mon; | |
822 | $sth->execute($no,$grp,$path,$name,"$mday.$mon.$year1"); | |
823 | $sth->finish(); | |
824 | $dbh->disconnect(); | |
825 | } | |
826 | ||
827 | sub getbills { | |
828 | my $grp=shift; | |
829 | $dbh=&connectdb; | |
830 | my $res=$dbh->selectall_arrayref("SELECT * FROM BILLS WHERE GRP=?;",undef, $grp); | |
831 | $dbh->disconnect(); | |
832 | return $res; | |
833 | } | |
834 | ||
835 | sub pngsize { | |
836 | my $Buffer = shift; | |
837 | my ($width,$height) = ( undef, undef ); | |
838 | ||
839 | if ($Buffer =~ /IHDR(.{8})/) { | |
840 | my $PNG = $1; | |
841 | ($width,$height) = unpack( "NN", $PNG ); | |
842 | } else { | |
843 | $width=$Lang::tr{'acct invalid png'}; | |
844 | }; | |
845 | return ($width,$height); | |
846 | } | |
847 | ||
848 | sub gifsize { | |
849 | my ($GIF)=@_; | |
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) ){ | |
853 | return; | |
854 | } | |
855 | ($a,$b,$c,$d)=unpack("C"x4,$s); | |
856 | ||
857 | $width= $b<<8|$a; | |
858 | $height= $d<<8|$c; | |
859 | return ($width,$height); | |
860 | } | |
861 | ||
862 | sub jpegsize { | |
863 | my ($JPEG)=@ _ ; | |
864 | my ($count)=2 ; | |
865 | my ($length)=length($JPEG) ; | |
866 | my ($ch)="" ; | |
867 | my ($c1,$c2,$a,$b,$c,$d,$width,$height) ; | |
868 | ||
869 | while (($ch ne "\xda") && ($count<$length)) { | |
870 | while (($ch ne "\xff") && ($count < $length)) { | |
871 | $ch=substr($JPEG,$count,1); | |
872 | $count++; | |
873 | } | |
874 | ||
875 | while (($ch eq "\xff") && ($count<$length)) { | |
876 | $ch=substr($JPEG,$count,1); | |
877 | $count++; | |
878 | } | |
879 | ||
880 | if ((ord($ch) >= 0xC0) && (ord($ch) <= 0xC3)) { | |
881 | $count+=3; | |
882 | ($a,$b,$c,$d)=unpack("C"x4,substr($JPEG,$count,4)); | |
883 | $width=$c<<8|$d; | |
884 | $height=$a<<8|$b; | |
885 | return($width,$height); | |
886 | }else { | |
887 | ($c1,$c2)= unpack("C"x2,substr($JPEG,$count,2)); | |
888 | $count += $c1<<8|$c2; | |
889 | } | |
890 | } | |
891 | } | |
892 | ||
893 | sub time{ | |
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); | |
898 | $year +=1900; | |
899 | $mday=sprintf("%02d",$mday); | |
900 | $mon=sprintf("%02d",$mon+1); | |
901 | my $res="$mday.$mon.$year $hour:$min:$sec - "; | |
902 | return $res; | |
903 | } | |
904 | ||
905 | sub logger{ | |
906 | my $settings=shift; | |
907 | my $msg=shift; | |
908 | #open LOGFILE | |
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"; | |
912 | close (ACCTLOG); | |
913 | } | |
914 | } | |
915 | ||
916 | sub updateccaddr { | |
917 | my $addr=shift; | |
918 | my $cust=shift; | |
919 | $dbh=&connectdb; | |
920 | $dbh->do("UPDATE ACCT_ADDR SET CCMAIL=? WHERE COMPANY=? ;",undef, $addr, $cust); | |
921 | $dbh->disconnect(); | |
922 | } | |
923 | return 1; |