]>
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 { | |
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 | ||
102 | sub 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 | ||
117 | sub 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 | ||
133 | sub 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 | ||
160 | sub 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 | ||
185 | sub 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 | ||
192 | sub 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 | ||
199 | sub 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 | ||
206 | sub 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 | ||
213 | sub 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 | ||
221 | sub 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 | ||
240 | sub 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 | ||
250 | sub 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 | ||
260 | sub 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 | ||
268 | sub 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 | ||
280 | sub 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 | ||
297 | sub 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 | ||
307 | sub 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 | ||
316 | sub 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 | ||
327 | sub checkusergrp { | |
328 | $dbh=connectdb; | |
329 | my $res=$dbh->selectall_arrayref("SELECT * FROM BILLINGHOST;"); | |
330 | $dbh->disconnect(); | |
331 | return $res; | |
332 | } | |
333 | ||
334 | sub 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 | ||
352 | sub 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 | ||
376 | sub 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 | ||
385 | sub 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 | ||
393 | sub 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 | ||
808 | sub 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 | ||
821 | sub 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 | ||
834 | sub 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 | ||
842 | sub 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 | ||
855 | sub 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 | ||
869 | sub 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 | ||
900 | sub 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 | ||
912 | sub 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 | ||
923 | sub 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 | } | |
930 | return 1; |