--- /dev/null
+etc/fcron.hourly/squid-accounting
+var/log/accounting.log
+srv/web/ipfire/cgi-bin/accounting.cgi
+srv/web/ipfire/html/accounting/logo
+usr/local/bin/acct.pl
+var/ipfire/accounting
+var/ipfire/accounting/dbinstall.pl
+var/ipfire/accounting/bill
+var/ipfire/accounting/acct-lib.pl
+var/ipfire/addon-lang/acct.de.pl
+var/ipfire/addon-lang/acct.en.pl
+var/ipfire/menu.d/EX-squid-accounting.menu
+var/ipfire/backup/addons/includes/squid-accounting
--- /dev/null
+###############################################################################
+# IPFire.org - An Open Source Firewall Solution #
+# Copyright (C) - IPFire Development Team <info@ipfire.org> #
+###############################################################################
+
+###############################################################################
+# Definitions
+###############################################################################
+
+include Config
+
+VER = 1.0.1
+
+THISAPP = squid-accounting-$(VER)
+DIR_APP = $(DIR_SRC)/$(THISAPP)
+TARGET = $(DIR_INFO)/$(THISAPP)
+PROG = squid-accounting
+PAK_VER = 2
+
+DEPS = "perl-DBI perl-DBD-SQLite perl-File-ReadBackwards perl-PDF-API2 sendEmail"
+
+###############################################################################
+# Top-level Rules
+###############################################################################
+
+install : $(TARGET)
+
+check :
+
+download :
+
+md5 :
+
+dist:
+ @$(PAK)
+
+###############################################################################
+# Installation Details
+###############################################################################
+
+$(TARGET) : $(patsubst %,$(DIR_DL)/%,$(objects))
+ @$(PREBUILD)
+ @rm -rf $(DIR_APP) && mkdir $(DIR_APP) && cp -R $(DIR_SRC)/src/squid-accounting/ $(DIR_APP)
+ cd $(DIR_APP)
+ mkdir -p /var/ipfire/accounting/bill
+ mkdir -p /srv/web/ipfire/html/accounting/logo
+ chmod -R 777 /srv/web/ipfire/html/accounting
+
+#Touch Logfile
+ touch /var/log/accounting.log
+ chmod 777 /var/log/accounting.log
+#Set permissions for logo and graphs
+ chmod -R 777 /srv/web/ipfire/html/accounting
+
+
+ install -v -m 755 $(DIR_APP)/squid-accounting/accounting.cgi /srv/web/ipfire/cgi-bin/
+ install -v -m 755 $(DIR_APP)/squid-accounting/acct.pl /usr/local/bin/
+ install -v -m 755 $(DIR_APP)/squid-accounting/acct-lib.pl /var/ipfire/accounting/
+ install -v -m 655 $(DIR_APP)/squid-accounting/acct.de.pl /var/ipfire/addon-lang/
+ install -v -m 655 $(DIR_APP)/squid-accounting/acct.en.pl /var/ipfire/addon-lang/
+ install -v -m 655 $(DIR_APP)/squid-accounting/EX-squid-accounting.menu /var/ipfire/menu.d/
+ install -v -m 755 $(DIR_APP)/squid-accounting/dbinstall.pl /var/ipfire/accounting/
+ install -v -m 644 $(DIR_APP)/squid-accounting/config/backup/includes/squid-accounting \
+ /var/ipfire/backup/addons/includes/squid-accounting
+
+#activate hourly logging of proxy logfile
+ ln -sf /usr/local/bin/acct.pl /etc/fcron.hourly/squid-accounting
+
+ chown -R nobody.nobody /var/ipfire/accounting
+ @rm -rf $(DIR_APP)
+ @$(POSTBUILD)
+
ipfiremake sslscan
ipfiremake owncloud
ipfiremake bacula
+ ipfiremake perl-PDF-API2
+ ipfiremake squid-accounting
}
buildinstaller() {
--- /dev/null
+#!/bin/bash
+############################################################################
+# #
+# This file is part of the IPFire Firewall. #
+# #
+# IPFire is free software; you can redistribute it and/or modify #
+# it under the terms of the GNU General Public License as published by #
+# the Free Software Foundation; either version 2 of the License, or #
+# (at your option) any later version. #
+# #
+# IPFire is distributed in the hope that it will be useful, #
+# but WITHOUT ANY WARRANTY; without even the implied warranty of #
+# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the #
+# GNU General Public License for more details. #
+# #
+# You should have received a copy of the GNU General Public License #
+# along with IPFire; if not, write to the Free Software #
+# Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA #
+# #
+# Copyright (C) 2009 IPFire-Team <info@ipfire.org>. #
+# #
+############################################################################
+#
+. /opt/pakfire/lib/functions.sh
+extract_files
+restore_backup ${NAME}
+
+#Generate SQLite DB if it does not exist
+if [ ! -f /var/ipfire/accounting/acct.db ]; then
+ perl /var/ipfire/accounting/dbinstall.pl
+ chmod 777 /var/ipfire/accounting/acct.db
+fi
+rm -f /var/ipfire/accounting/dbinstall.pl
+/usr/local/bin/update-lang-cache
--- /dev/null
+#!/bin/bash
+############################################################################
+# #
+# This file is part of the IPFire Firewall. #
+# #
+# IPFire is free software; you can redistribute it and/or modify #
+# it under the terms of the GNU General Public License as published by #
+# the Free Software Foundation; either version 2 of the License, or #
+# (at your option) any later version. #
+# #
+# IPFire is distributed in the hope that it will be useful, #
+# but WITHOUT ANY WARRANTY; without even the implied warranty of #
+# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the #
+# GNU General Public License for more details. #
+# #
+# You should have received a copy of the GNU General Public License #
+# along with IPFire; if not, write to the Free Software #
+# Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA #
+# #
+# Copyright (C) 2014 IPFire-Team <alexander.marx@ipfire.org> #
+# #
+############################################################################
+
+. /opt/pakfire/lib/functions.sh
+make_backup ${NAME}
+remove_files
--- /dev/null
+#!/bin/bash
+############################################################################
+# #
+# This file is part of the IPFire Firewall. #
+# #
+# IPFire is free software; you can redistribute it and/or modify #
+# it under the terms of the GNU General Public License as published by #
+# the Free Software Foundation; either version 2 of the License, or #
+# (at your option) any later version. #
+# #
+# IPFire is distributed in the hope that it will be useful, #
+# but WITHOUT ANY WARRANTY; without even the implied warranty of #
+# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the #
+# GNU General Public License for more details. #
+# #
+# You should have received a copy of the GNU General Public License #
+# along with IPFire; if not, write to the Free Software #
+# Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA #
+# #
+# Copyright (C) 2007 IPFire-Team <info@ipfire.org>. #
+# #
+############################################################################
+#
+. /opt/pakfire/lib/functions.sh
+./uninstall.sh
+./install.sh
--- /dev/null
+ $sublogs->{'32.proxy'} = {'caption' => $Lang::tr{'acct menu'},
+ 'uri' => '/cgi-bin/accounting.cgi',
+ 'title' => $Lang::tr{'acct menu'},
+ 'enabled' => 1,
+ };
--- /dev/null
+#!/usr/bin/perl
+###############################################################################
+# #
+# IPFire.org - A linux based firewall #
+# Copyright (C) 2014 IPFire Team <alexander.marx@ipfire.org> #
+# #
+# This program is free software: you can redistribute it and/or modify #
+# it under the terms of the GNU General Public License as published by #
+# the Free Software Foundation, either version 3 of the License, or #
+# (at your option) any later version. #
+# #
+# This program is distributed in the hope that it will be useful, #
+# but WITHOUT ANY WARRANTY; without even the implied warranty of #
+# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the #
+# GNU General Public License for more details. #
+# #
+# You should have received a copy of the GNU General Public License #
+# along with this program. If not, see <http://www.gnu.org/licenses/>. #
+# #
+###############################################################################
+
+
+use Time::Local;
+use File::Find;
+use File::Path;
+use GD::Graph::area;
+use GD::Graph::bars;
+use LWP::UserAgent;
+use POSIX;
+use locale;
+
+# enable only the following on debugging purpose
+#use warnings;
+#use CGI::Carp 'fatalsToBrowser';
+
+require '/var/ipfire/general-functions.pl';
+require "${General::swroot}/lang.pl";
+require "${General::swroot}/header.pl";
+require "${General::swroot}/accounting/acct-lib.pl";
+
+unless (-e "${General::swroot}/accounting/settings.conf") { system("touch ${General::swroot}/accounting/settings.conf"); }
+
+my $settingsfile="${General::swroot}/accounting/settings.conf";
+my $proxyenabled="${General::swroot}/proxy/enable";
+my $logopath="/srv/web/ipfire/html/accounting/logo";
+my %settings=();
+my %mainsettings;
+my %color;
+my %cgiparams=();
+my ($sec,$min,$hour,$mday,$mon,$year,$wday,$ydat,$isdst)=localtime();
+my $dbh;
+my %checked=();
+my $errormessage='';
+my @ips;
+my $count=0;
+my $col;
+my $proxlog=$Lang::tr{'stopped'};
+my $proxsrv=$Lang::tr{'stopped'};
+
+&Header::getcgihash(\%cgiparams);
+&General::readhash("${General::swroot}/main/settings", \%mainsettings);
+&General::readhash("/srv/web/ipfire/html/themes/".$mainsettings{'THEME'}."/include/colors.txt", \%color);
+&General::readhash("$settingsfile", \%settings) if(-f $settingsfile);
+
+#Find out which lang is set (used later to set decimal separator correctly)
+my $uplang=uc($mainsettings{'LANGUAGE'});
+setlocale LC_NUMERIC,"$mainsettings{'LANGUAGE'}_$uplang";
+
+if ($cgiparams{'BILLVIEW'} eq "show"){
+ my $file=$cgiparams{'file'};
+ my $name=$cgiparams{'name'};
+ open(DLFILE, "<$file") or die "Unable to open $file: $!";
+ my @fileholder = <DLFILE>;
+ print "Content-Type:application/pdf\n";
+ my @fileinfo = stat("$file");
+ print "Content-Length:$fileinfo[7]\n";
+ print "Content-Disposition:attachment;filename='$name';\n\n";
+ print @fileholder;
+ exit (0);
+}
+if ($cgiparams{'BILLACTION'} eq "open_preview"){
+ #Generate preview Bill
+ my $rggrp=$cgiparams{'txt_billgroup'};
+ my $address_cust = &ACCT::getTaAddress($rggrp,'CUST');
+ my $address_host = &ACCT::getTaAddress($rggrp,'HOST');
+ my $billpos = &ACCT::getextrabillpos($rggrp);
+ my $mwst=$settings{'MWST'};
+ my $cur = $settings{'CURRENCY'};
+ my @now = localtime(time);
+ $now[5] = $now[5] + 1900;
+ my $actmonth = $now[4]+1;
+ my $actyear = $now[5];
+ my ($from,$till)=&ACCT::getmonth($actmonth,$actyear);
+ my @billar = &ACCT::GetTaValues($from,$till,$rggrp);
+ &ACCT::pdf2(\@billar,$actmonth,$actyear,$mwst,$address_cust,$address_host,$billpos,$rggrp,$cur,"on");
+ #Show PDF preview
+ open(DLFILE, "</var/ipfire/accounting/bill/tmp.pdf") or die "Unable to open tmp.pdf: $!";
+ my @fileholder = <DLFILE>;
+ print "Content-Type:application/pdf\n";
+ my @fileinfo = stat("/var/ipfire/accounting/bill/tmp.pdf");
+ print "Content-Length:$fileinfo[7]\n";
+ print "Content-Disposition:attachment;filename='tmp.pdf';\n\n";
+ print @fileholder;
+ exit (0);
+}
+
+&Header::showhttpheaders();
+
+if ($cgiparams{'ACTION'} eq "$Lang::tr{'acct config'}"){ #ConfigButton from Menu
+ &configsite;
+}
+if ($cgiparams{'ACTION'} eq "$Lang::tr{'acct addresses'}"){ #AddressmgmntButton from Menu
+ &addressmgmnt;
+}
+if ($cgiparams{'ACTION'} eq "$Lang::tr{'acct billgroup'}"){ #BillgroupButton from Menu
+ &billgroupsite;
+}
+if ($cgiparams{'ACTION'} eq "$Lang::tr{'acct maintenance'}"){ #MaintenanceButton from Menu
+ &expertsite;
+}
+if ($cgiparams{'ACTION'} eq "$Lang::tr{'save'}"){ #SaveButton on configsite
+ my @val=split('\r\n',$cgiparams{'txt_skipurls'});
+ my $skipurls;
+ foreach my $line (@val){
+ $skipurls.="|".$line;
+ }
+ $skipurls=$skipurls."|";
+ $skipurls=~ s/\|\|/\|/g;
+ my @txt=split('\r\n',$cgiparams{'txt_mailtxt'});
+ my $mailtxt;
+ foreach my $txt (@txt){
+ $mailtxt.="|".$txt;
+ }
+ #Check fields
+ if ($cgiparams{'USEMAIL'} eq 'on'){
+ $errormessage=&checkmailsettings;
+ }elsif($cgiparams{'USEMAIL'} ne 'on'){
+ $cgiparams{'txt_mailserver'}='';
+ $cgiparams{'txt_mailport'}='';
+ $cgiparams{'txt_mailuser'}='';
+ $cgiparams{'txt_mailpass'}='';
+ $cgiparams{'mail_tls'}='';
+ $cgiparams{'txt_mailsender'}='';
+ $cgiparams{'txt_mailsubject'}='';
+ $mailtxt='';
+ }
+ if(!$errormessage){
+ open (TXT, ">$settingsfile") or die("Die Datei konnte nicht geöffnet werden: $!\n");
+ close TXT;
+ $settings{'LOG'} = $cgiparams{'logging'};
+ $settings{'EXPERT'} = $cgiparams{'expert'};
+ $settings{'MULTIUSER'} = $cgiparams{'multiuser'};
+ $settings{'MWST'} = $cgiparams{'txt_mwst'};
+ $settings{'CURRENCY'} = $cgiparams{'txt_currency'};
+ $settings{'SKIPURLS'} = $skipurls;
+ $settings{'USEMAIL'} = $cgiparams{'USEMAIL'};
+ $settings{'MAILSRV'} = $cgiparams{'txt_mailserver'};
+ $settings{'MAILPORT'} = $cgiparams{'txt_mailport'};
+ $settings{'MAILUSER'} = $cgiparams{'txt_mailuser'};
+ $settings{'MAILPASS'} = $cgiparams{'txt_mailpass'};
+ $settings{'TLS'} = $cgiparams{'mail_tls'};
+ $settings{'MAILSENDER'} = $cgiparams{'txt_mailsender'};
+ $settings{'MAILSUB'} = $cgiparams{'txt_mailsubject'};
+ $settings{'MAILTXT'} = $mailtxt;
+ &General::writehash("$settingsfile", \%settings);
+ }else{
+ $cgiparams{'update'}='on';
+ &configsite;
+ }
+}
+if ($cgiparams{'ACTION'} eq "$Lang::tr{'acct view'}"){ #If special month and year is given on mainsite
+ &mainsite($cgiparams{'month'},$cgiparams{'year'});
+ exit 0;
+}
+if ($cgiparams{'ACTION'} eq "$Lang::tr{'acct commit'}"){ #Maintenance function
+ &ACCT::logger($settings{'LOG'},"Clear DB (all data) committed).\n");
+ &ACCT::cleardbtraf;
+ &expertsite;
+}
+if ($cgiparams{'ACTION2'} eq "$Lang::tr{'acct commit'}"){ #Maintenance function
+ &ACCT::logger($settings{'LOG'},"Clear DB (only traffic data) committed).\n");
+ &ACCT::cleardb;
+ &expertsite;
+}
+if ($cgiparams{'ACTION1'} eq "$Lang::tr{'acct commit'}"){ #Maintenance Function
+ #Get Timestamp
+ my ($a,$b)=&ACCT::getmonth($cgiparams{'expmonth'},$cgiparams{'expyear'});
+ &ACCT::delbefore(($a-1));
+ &expertsite;
+}
+if ($cgiparams{'ACTION'} eq "viewgraph"){ #Graph icon on hosttable (viewhosttable)
+ &graphsite($cgiparams{'month'},$cgiparams{'year'},$cgiparams{'host'});
+}
+if ($cgiparams{'ACTION1'} eq "$Lang::tr{'save'}"){ #SaveButton when adding Address
+ $errormessage=&checkaddress;
+
+ if ($errormessage){
+ &addressmgmnt($errormessage);
+ }else{
+ &ACCT::writeaddr(
+ $cgiparams{'txt_company'},
+ $cgiparams{'rdo_companytype'},
+ $cgiparams{'txt_name1'},
+ $cgiparams{'txt_str'},
+ $cgiparams{'txt_str_nr'},
+ $cgiparams{'txt_plz'},
+ $cgiparams{'txt_city'},
+ $cgiparams{'txt_bank'},
+ $cgiparams{'txt_iban'},
+ $cgiparams{'txt_bic'},
+ $cgiparams{'txt_blz'},
+ $cgiparams{'txt_kto'},
+ $cgiparams{'txt_email'},
+ $cgiparams{'txt_inet'},
+ $cgiparams{'txt_hrb'},
+ $cgiparams{'txt_ustid'},
+ $cgiparams{'txt_tel'},
+ $cgiparams{'txt_fax'},
+ );
+ &ACCT::logger($settings{'LOG'},"Created new address $cgiparams{'txt_company'} as $cgiparams{'rdo_companytype'}.\n");
+ %cgiparams=();
+ }
+ &addressmgmnt;
+}
+if ($cgiparams{'ACTION'} eq "edit_addr"){ #Pencil icon in Address overwiev on Addressmgmnt site
+ $cgiparams{'update'} = 'on';
+ $cgiparams{'oldcompname'}=$cgiparams{'txt_company'};
+ &addressmgmnt;
+}
+if ($cgiparams{'ACTION'} eq "$Lang::tr{'update'}"){ #UpdateButton when editing Address
+ $cgiparams{'update'} = 'on';
+ $errormessage=&checkaddress;
+ if ($errormessage){
+ &addressmgmnt($errormessage);
+ }else{
+ &ACCT::updateaddr(
+ $cgiparams{'rdo_companytype'},
+ $cgiparams{'txt_company'},
+ $cgiparams{'txt_name1'},
+ $cgiparams{'txt_str'},
+ $cgiparams{'txt_str_nr'},
+ $cgiparams{'txt_plz'},
+ $cgiparams{'txt_city'},
+ $cgiparams{'txt_bank'},
+ $cgiparams{'txt_iban'},
+ $cgiparams{'txt_bic'},
+ $cgiparams{'txt_blz'},
+ $cgiparams{'txt_kto'},
+ $cgiparams{'txt_email'},
+ $cgiparams{'txt_inet'},
+ $cgiparams{'txt_hrb'},
+ $cgiparams{'txt_ustid'},
+ $cgiparams{'txt_tel'},
+ $cgiparams{'txt_fax'},
+ $cgiparams{'oldcompname'}
+ );
+ my $res=&ACCT::getbillgroups;
+ foreach my $line (@$res){
+ my($name,$host,$cust)=@$line;
+ if($host eq $cgiparams{'oldcompname'}){
+ &ACCT::updatebillgrouphost($cgiparams{'oldcompname'},$cgiparams{'txt_company'});
+ }elsif ($cust eq $cgiparams{'oldcompname'}){
+ &ACCT::updatebillgroupcust($cgiparams{'oldcompname'},$cgiparams{'txt_company'});
+ }
+ }
+
+ &ACCT::logger($settings{'LOG'},"Edited address $cgiparams{'oldcompname'}.\n");
+ %cgiparams=();
+ }
+ &addressmgmnt;
+}
+if ($cgiparams{'ACTION'} eq "del_addr"){ #Trash icon in Address overview on Addressmgmnt site
+ my $res=&ACCT::checkbillgrp;
+ foreach my $line (@$res){
+ my ($grp,$host,$cust) = @$line;
+ if ($cgiparams{'txt_company'} eq $host){
+ $errormessage="$Lang::tr{'acct err hostdel'} $grp";
+ }
+ if ($cgiparams{'txt_company'} eq $cust){
+ $errormessage="$Lang::tr{'acct err custdel'} $grp";
+ }
+ }
+ if (! $errormessage){
+ &ACCT::deladdr($cgiparams{'txt_company'});
+ &ACCT::logger($settings{'LOG'},"Deleted address $cgiparams{'txt_company'}.\n");
+ }
+ $cgiparams{'txt_company'}='';
+ &addressmgmnt;
+}
+if ($cgiparams{'BILLACTION'} eq "$Lang::tr{'save'}"){ #SaveButton when adding BillingGroups
+ #check if a group with the same name already exists in DB
+ my $res=&ACCT::getbillgroups;
+ foreach my $row (@$res) {
+ my ($group)=@$row;
+ if ($group eq $cgiparams{'txt_billgroup'}){
+ $errormessage.=$Lang::tr{'acct billgroupexists'};
+ }
+ }
+ #Check if a selected user is in another group already
+ if ($settings{'MULTIUSER'} ne 'on'){
+ #split hosts into array
+ my @user=split(/\|/,$cgiparams{'sel_hosts'});
+ my $res=&ACCT::checkusergrp;
+ foreach my $val (@$res){
+ my ($grp,$usr)=@$val;
+ foreach my $wanted (@user){
+ if($usr eq $wanted){
+ $errormessage.="$usr $Lang::tr{'acct usermulti'} $grp<br>";
+ }
+ }
+ }
+ }
+ #validate namefield
+ if(!&validtextfield($cgiparams{'txt_billgroup'})){
+ $errormessage.="$Lang::tr{'acct invalid'} $Lang::tr{'name'}<br>";
+ }
+ #FIXME: Validate CENT amount (num with .)
+ #if used colon, replace with .
+ $cgiparams{'txt_cent'} =~ tr/,/./;
+ if(!&validnumfield($cgiparams{'txt_cent'})){
+ $errormessage.="$Lang::tr{'acct invalid'} $Lang::tr{'acct cent'}<br>";
+ }
+ #fill array
+ my @ips=split (/\|/,$cgiparams{'sel_hosts'});
+
+ #Check if we use extra bill positions
+ if($cgiparams{'txt_amount'} || $cgiparams{'txt_name'}|| $cgiparams{'txt_price'}){
+ if (!$cgiparams{'txt_amount'} || !$cgiparams{'txt_name'} || !$cgiparams{'txt_price'}){
+ $errormessage.="$Lang::tr{'acct invalid billpos'}<br>";
+ }
+ #check all fields
+ if (!$errormessage){
+ if(! &validnumfield($cgiparams{'txt_amount'})){
+ $errormessage.="$Lang::tr{'acct invalid'} $Lang::tr{'acct amount'}<br>";
+ }elsif(! &validtextfield($cgiparams{'txt_name'})){
+ $errormessage.="$Lang::tr{'acct invalid'} $Lang::tr{'acct name'}<br>";
+ }elsif(! &validnumfield($cgiparams{'txt_price'})){
+ $errormessage.="$Lang::tr{'acct invalid'} $Lang::tr{'acct price pp'}<br>";
+ }
+ }
+ }
+
+ if ($errormessage){
+ &billgroupsite($errormessage);
+ }else{
+ #check if we use extra positions
+ if ($cgiparams{'txt_amount'}){
+ &ACCT::savebillpos(
+ $cgiparams{'txt_posbillgroup'},
+ $cgiparams{'txt_amount'},
+ $cgiparams{'txt_name'},
+ $cgiparams{'txt_price'});
+ &ACCT::logger($settings{'LOG'},"Saved new fixed billposition $cgiparams{'txt_name'} into billgroup $cgiparams{'txt_posbillgroup'} .\n");
+ }
+
+ #save new group
+ &ACCT::savebillgroup(
+ $cgiparams{'txt_billgroup'},
+ $cgiparams{'txt_billtext1'},
+ $cgiparams{'dd_host'},
+ $cgiparams{'dd_cust'},
+ $cgiparams{'txt_cent'},
+ \@ips);
+ &ACCT::logger($settings{'LOG'},"Saved new billgroup $cgiparams{'txt_billgroup'}.\n");
+ %cgiparams=();
+ }
+ &billgroupsite;
+}
+if ($cgiparams{'BILLACTION'} eq "$Lang::tr{'update'}"){ #UpdateButton when editing BillingGroups
+
+
+ my $filename=$cgiparams{'uploaded_file'};
+ if($filename){
+ #First check if logo dir exists
+ if (! -d "$logopath/$cgiparams{'logo_grp'}"){
+ mkpath("$logopath/$cgiparams{'logo_grp'}",0,01777);
+ }
+ #Save File
+ my ($filehandle) = CGI::upload('uploaded_file');
+ open (UPLOADFILE, ">$logopath/$cgiparams{'logo_grp'}/logo.png");
+ binmode $filehandle;
+ while ( <$filehandle> ) {
+ print UPLOADFILE;
+ }
+ close (UPLOADFILE);
+
+ #Check dimensions of uploaded file
+ open (PNG , "<$logopath/$cgiparams{'logo_grp'}/logo.png") ;
+ local $/;
+ my $PNG1=<PNG> ;
+ close(PNG) ;
+ my ($width,$height)=&ACCT::pngsize($PNG1) ;
+
+ if (! &validnumfield($width)){
+ $errormessage.="$Lang::tr{'acct invalid png'}<br>";
+ unlink("$logopath/$cgiparams{'logo_grp'}/logo.png");
+ }elsif($width > 400 || $height > 150){
+ $errormessage.="$Lang::tr{'acct invalid pngsize'} width: $width height: $height<br>";
+ unlink("$logopath/$cgiparams{'logo_grp'}/logo.png");
+ }
+
+ }
+ #Check if a group with the same name already exists in DB
+ my $res=&ACCT::getbillgroups;
+ foreach my $row (@$res) {
+ my ($group)=@$row;
+ if (($group eq $cgiparams{'txt_billgroup'}) && ($cgiparams{'oldname'} ne $cgiparams{'txt_billgroup'})){
+ $errormessage.=$Lang::tr{'acct billgroupexists'};
+ }
+ }
+ #Check if a selected user is in another group already
+ if ($settings{'MULTIUSER'} ne 'on'){
+ #split hosts into array
+ my @user=split(/\|/,$cgiparams{'sel_hosts'});
+ my $res=&ACCT::checkusergrp;
+ foreach my $val (@$res){
+ my ($grp,$usr)=@$val;
+ foreach my $wanted (@user){
+ if($usr eq $wanted && $grp ne $cgiparams{'txt_billgroup'}){
+ $errormessage.="$usr $Lang::tr{'acct usermulti'} $grp<br>";
+ }
+ }
+ }
+ }
+ #Validate namefield
+ if(!&validtextfield($cgiparams{'txt_billgroup'})){
+ $errormessage.="$Lang::tr{'acct invalid'} $Lang::tr{'name'}";
+ }
+ #Validate CENT amount (num with .)
+ #if used colon, replace with .
+ $cgiparams{'txt_cent'} =~ tr/,/./;
+ if(!&validnumfield($cgiparams{'txt_cent'})){
+ $errormessage.="$Lang::tr{'acct invalid'} $Lang::tr{'acct cent'}<br>";
+ }
+
+ #Fill array
+ my @ips=split ( /\|/,$cgiparams{'sel_hosts'});
+
+ #Check if we use extra bill positions
+ if($cgiparams{'txt_amount'} || $cgiparams{'txt_name'}|| $cgiparams{'txt_price'}){
+ if (!$cgiparams{'txt_amount'} || !$cgiparams{'txt_name'} || !$cgiparams{'txt_price'}){
+ $errormessage.="$Lang::tr{'acct invalid billpos'}<br>";
+ }
+ #Check all fields
+ if (!$errormessage){
+ if(! &validnumfield($cgiparams{'txt_amount'})){
+ $errormessage.="$Lang::tr{'acct invalid'} $Lang::tr{'acct amount'}<br>";
+ }elsif(! &validtextfield($cgiparams{'txt_name'})){
+ $errormessage.="$Lang::tr{'acct invalid'} $Lang::tr{'acct name'}<br>";
+ }elsif(! &validnumfield($cgiparams{'txt_price'})){
+ $errormessage.="$Lang::tr{'acct invalid'} $Lang::tr{'acct price pp'}<br>";
+ }
+ }
+ }
+ #Check if we added some CC mail recipients
+ if($cgiparams{'txt_ccmail'}){
+ $cgiparams{'txt_ccmail'}=~ s/ //g;
+ #Split line into single addresses and check each one
+ my @ccaddr = split(",",$cgiparams{'txt_ccmail'});
+ foreach my $cc (@ccaddr){
+ if (! &General::validemail($cc)){
+ $errormessage.="$Lang::tr{'acct invalid'} $Lang::tr{'acct ccmail'} $cc<br>";
+ }
+ }
+ }
+ if ($errormessage){
+ $cgiparams{'update'} = 'on';
+ &billgroupsite();
+ }else{
+ #update fixedbillpositions if defined
+ if ($cgiparams{'oldname'} ne $cgiparams{'txt_billgroup'}){
+ &ACCT::updatebillpos($cgiparams{'oldname'},$cgiparams{'txt_billgroup'});
+ }
+ #Check if we use extra positions
+ if ($cgiparams{'txt_amount'}){
+ &ACCT::savebillpos(
+ $cgiparams{'txt_billgroup'},
+ $cgiparams{'txt_amount'},
+ $cgiparams{'txt_name'},
+ $cgiparams{'txt_price'});
+ }
+ #Check if we have NEW cc Mails
+ if($cgiparams{'txt_ccmail'} ne $cgiparams{'oldccmail'}){
+ &ACCT::updateccaddr($cgiparams{'txt_ccmail'},$cgiparams{'dd_cust'});
+ }
+ &ACCT::deletebillgroup($cgiparams{'oldname'});
+ &ACCT::savebillgroup(
+ $cgiparams{'txt_billgroup'},
+ $cgiparams{'txt_billtext1'},
+ $cgiparams{'dd_host'},
+ $cgiparams{'dd_cust'},
+ $cgiparams{'txt_cent'},
+ \@ips);
+ %cgiparams=();
+ }
+ &billgroupsite;
+}
+if ($cgiparams{'BILLACTION'} eq "edit_billgroup"){ #Pencil icon in Billgroup table
+ $cgiparams{'update'} = 'on';
+ &billgroupsite;
+}
+if ($cgiparams{'BILLACTION'} eq "delete_billgroup"){ #Trash icon in Billgroup table
+ &deletefiles($cgiparams{'txt_billgroup'});
+ &ACCT::delbillpos($cgiparams{'txt_billgroup'});
+ &ACCT::deletebillgroup($cgiparams{'txt_billgroup'});
+ &ACCT::logger($settings{'LOG'},"Deleted billgroup $cgiparams{'txt_billgroup'}.\n");
+ &billgroupsite;
+}
+if ($cgiparams{'BILLACTION'} eq "open_billgroup"){ #Folder icon on billgrouptable (viewtablebillgroup)
+ &billoverview($cgiparams{'txt_billgroup'});
+}
+if ($cgiparams{'BILLPOS'} eq "delpos"){ #Trash icon in Billpos table
+ $cgiparams{'update'} = 'on';
+ &ACCT::delbillpos_single($cgiparams{'txt_billpos'},$cgiparams{'txt_billgroup'});
+ &ACCT::logger($settings{'LOG'},"Deleted fixed billposition $cgiparams{'txt_billpos'} from billgroup $cgiparams{'txt_billgroup'}.\n");
+ &billgroupsite;
+}
+if ($cgiparams{'BILLPOS'} eq "$Lang::tr{'save'}"){ #Savebutton in Billpos table
+ $cgiparams{'update'}='on';
+ #Check if we use extra bill positions
+ if($cgiparams{'txt_amount'} || $cgiparams{'txt_name'}|| $cgiparams{'txt_price'}){
+ if (!$cgiparams{'txt_amount'} || !$cgiparams{'txt_name'} || !$cgiparams{'txt_price'}){
+ $errormessage.="$Lang::tr{'acct invalid billpos'}<br>";
+ }
+ #check all fields
+ if (!$errormessage){
+ #if used colon, replace with .
+ $cgiparams{'txt_price'} =~ tr/,/./;
+ if(! &validnumfield($cgiparams{'txt_amount'})){
+ $errormessage.="$Lang::tr{'acct invalid'} $Lang::tr{'acct amount'}<br>";
+ }elsif(! &validtextfield($cgiparams{'txt_name'})){
+ $errormessage.="$Lang::tr{'acct invalid'} $Lang::tr{'acct name'}<br>";
+ }elsif(! &validnumfield($cgiparams{'txt_price'})){
+ $errormessage.="$Lang::tr{'acct invalid'} $Lang::tr{'acct price pp'}<br>";
+ }
+ }
+ }
+ if ($errormessage){
+ &billgroupsite($errormessage);
+ }else{
+ #check if we use extra positions
+ if ($cgiparams{'txt_amount'}){
+ &ACCT::savebillpos(
+ $cgiparams{'txt_billgroup'},
+ $cgiparams{'txt_amount'},
+ $cgiparams{'txt_name'},
+ $cgiparams{'txt_price'});
+ }
+ &ACCT::logger($settings{'LOG'},"Added fixed billposition $cgiparams{'txt_amount'} $cgiparams{'txt_name'} with price $cgiparams{'txt_price'} to billgroup $cgiparams{'txt_billgroup'}.\n");
+ }
+ %cgiparams=();
+ &billgroupsite;
+}
+
+
+#Check if we already have settings
+if ( -z $settingsfile){
+ &configsite;
+}else{
+ &mainsite(($mon+1),($year+1900));
+ exit 0;
+}
+
+sub configsite{
+ my $proxymessage='';
+ my $blockactivation='';
+ #If update set fieldvalues new
+ if($cgiparams{'update'} eq 'on'){
+ $settings{'USEMAIL'} = 'on';
+ $settings{'MAILSRV'} = $cgiparams{'txt_mailserver'};
+ $settings{'MAILPORT'} = $cgiparams{'txt_mailport'};
+ $settings{'MAILUSER'} = $cgiparams{'txt_mailuser'};
+ $settings{'MAILPASS'} = $cgiparams{'txt_mailpass'};
+ $settings{'MAILSUB'} = $cgiparams{'txt_mailsubject'};
+ $settings{'MAILTXT'} = $cgiparams{'txt_mailtxt'};
+ }
+ #find preselections
+ $checked{'expert'}{$settings{'EXPERT'}} = 'CHECKED';
+ $checked{'logging'}{$settings{'LOG'}} = 'CHECKED';
+ $checked{'multiuser'}{$settings{'MULTIUSER'}} = 'CHECKED';
+ $checked{'usemail'}{$settings{'USEMAIL'}} = 'CHECKED';
+ $checked{'mail_tls'}{$settings{'TLS'}} = 'CHECKED';
+
+ #Open site
+ &Header::openpage($Lang::tr{'acct settings'}, 1, '');
+ &Header::openbigbox('100%', 'center');
+ &error;
+ &Header::openbox('100%', 'left', $Lang::tr{'acct config'});
+
+ #### JAVA SCRIPT ####
+ print<<END;
+<script>
+ \$(document).ready(function() {
+ // Show/Hide elements when NAT checkbox is checked.
+ if (\$("#MAIL").attr("checked")) {
+
+ } else {
+ \$(".MAILSRV").hide();
+ }
+
+ // Show NAT area when "use nat" checkbox is clicked
+ \$("#MAIL").change(function() {
+ \$(".MAILSRV").toggle();
+
+ });
+ });
+</script>
+END
+#######################
+ $settings{'SKIPURLS'} =~ tr/|/\r\n/;
+ $settings{'MAILTXT'} =~ tr/|/\r\n/;
+ my $col="style='background-color:$color{'color22'}'";
+ print<<END;
+ <form method='post' action='$ENV{'SCRIPT_NAME'}'>
+ <table style='width:100%'>
+ <tr>
+ <th colspan='3'></th>
+ </tr>
+ <tr>
+ <td style='width:24em'>$Lang::tr{'acct logging'}</td>
+ <td><input type='checkbox' name='logging' $checked{'logging'}{'on'}></td>
+ <td></td>
+ </tr>
+ <tr>
+ <td>$Lang::tr{'acct expert'}</td>
+ <td><input type='checkbox' name='expert' $checked{'expert'}{'on'}></td>
+ <td></td>
+ </tr>
+ <tr>
+ <td>$Lang::tr{'acct multiuser'}</td>
+ <td><input type='checkbox' name='multiuser' $checked{'multiuser'}{'on'}></td>
+ <td></td>
+ <tr>
+ <tr>
+ <td>$Lang::tr{'acct mwst'}</td>
+ <td><input type='text' name='txt_mwst' value='$settings{'MWST'}' style='width:22em;'></td>
+ <td></td>
+ <tr>
+ <td>$Lang::tr{'acct currency'}</td>
+ <td><input type='text' name='txt_currency' value='$settings{'CURRENCY'}' style='width:22em;'></td>
+ <td></td>
+
+ <tr>
+ <td valign='top'>$Lang::tr{'acct skipurl'}</td>
+ <td style='padding-left:0.2em;'><textarea name="txt_skipurls" cols="20" rows="6" style='width:22em;'>$settings{'SKIPURLS'}</textarea></td>
+ <td></td>
+ </tr>
+ <tr>
+ <td>$Lang::tr{'acct usemail'}</td>
+ <td><label><input type='checkbox' name='USEMAIL' id='MAIL' $checked{'usemail'}{'on'}></label></td>
+ <td></td>
+ </tr>
+ </table><br>
+
+ <div class="MAILSRV">
+ <table style='width:100%;'>
+ <tr>
+ <td style='width:24em'>$Lang::tr{'acct mailaddr'}</td>
+ <td><input type='text' name='txt_mailserver' value='$settings{'MAILSRV'}' style='width:22em;'></td>
+ </tr>
+ <tr>
+ <td>$Lang::tr{'acct mailport'}</td>
+ <td><input type='text' name='txt_mailport' value='$settings{'MAILPORT'}' size='3'></td>
+ </tr>
+ <tr>
+ <td>$Lang::tr{'acct mailuser'}<img src='/blob.gif' alt='*' /></td>
+ <td><input type='text' name='txt_mailuser' value='$settings{'MAILUSER'}' style='width:22em;'></td>
+ </tr>
+ <tr>
+ <td>$Lang::tr{'acct mailpass'}<img src='/blob.gif' alt='*' /></td>
+ <td><input type='password' name='txt_mailpass' value='$settings{'MAILPASS'}' style='width:22em;' ></td>
+ </tr>
+ <tr>
+ <td>$Lang::tr{'acct tls'}</td>
+ <td><input type='checkbox' name='mail_tls' $checked{'mail_tls'}{'on'}></td>
+ </tr>
+ <tr>
+ <td>$Lang::tr{'acct mailsender'}</td>
+ <td><input type='text' name='txt_mailsender' value='$settings{'MAILSENDER'}' style='width:22em;'></td>
+ </tr>
+ <tr>
+ <td colspan='2'> </td>
+ </tr>
+ <tr>
+ <td>$Lang::tr{'acct subject'}</td>
+ <td><input type='text' name='txt_mailsubject' value='$settings{'MAILSUB'}' style='width:22em;'></td>
+ </tr>
+ <tr>
+ <td valign='top' >$Lang::tr{'acct mailtxt'}</td>
+ <td style='padding-left:0.2em;'><textarea name="txt_mailtxt" cols="34" rows="6" style='width: 22em;'>$settings{'MAILTXT'}</textarea></td>
+
+ </table>
+ </div>
+
+
+ <table style='width:100%;'>
+ <tr>
+ <td colspan='3' display:inline align='right'><input type='submit' name='ACTION' value='$Lang::tr{'save'}'></td>
+ </tr>
+ </table>
+ <br>
+ </form>
+END
+&Header::closebox();
+#BackButton
+ print<<END;
+ <form method='post' action='$ENV{'SCRIPT_NAME'}'>
+ <table style='width:100%;'>
+ <tr>
+ <td></td>
+ <td align='right'><input type='submit' name='ACTION' value='$Lang::tr{'back'}'></td>
+ </tr>
+ </table>
+ </form>
+END
+ &Header::closebigbox();
+ &Header::closepage();
+ exit 0;
+}
+
+sub deletefiles{
+ my $grp=shift;
+ rmtree ("$logopath/$grp");
+ rmtree ("${General::swroot}/accounting/bill/$grp");
+}
+
+sub mainsite{
+ my $mon=$_[0];
+ my $year=$_[1];
+ if ($_[0]){$mon=$_[0];}
+ &Header::openpage($Lang::tr{'acct title'}, 1, '');
+ &Header::openbigbox('100%', 'center');
+ &checkproxy;
+ &statusbox;
+ &menu;
+ &viewtablehosts(($mon),$year);
+ &Header::closebigbox();
+ &Header::closepage();
+}
+
+sub expertsite{
+ &Header::openpage($Lang::tr{'acct maintenance'}, 1, '');
+ &Header::openbigbox('100%', 'center');
+
+ #Get size of Databasefile
+ my $sizedb= -s "/var/ipfire/accounting/acct.db";
+ $sizedb = sprintf"%.2f", $sizedb/(1024*1024);
+ #Get size of Directory, where all bills are stored
+ my $sizerrd = 0;
+ find(sub { $sizerrd += -s if -f $_ }, "${General::swroot}/accounting/bill");
+ $sizerrd = sprintf"%.2f", $sizerrd/(1024*1024);
+
+ &ACCT::connectdb;
+ #Get latest and earliest entry of DB
+ my ($min,$max) = &ACCT::getminmax;
+ $min=&getdate($min);
+ $max=&getdate($max);
+ #Print status table
+ &Header::openbox('100%', 'left', $Lang::tr{'acct status'});
+ print<<END;
+ <table style='width:100%;' cellspacong='0' class='tbl'>
+ <tr>
+ <th align='left' width='30%'>$Lang::tr{'name'}</th>
+ <th align='left'>$Lang::tr{'acct value'}</th>
+ </tr>
+ <tr>
+ <td>$Lang::tr{'acct dbsize'}</td>
+ <td>$sizedb MB</td>
+ </tr>
+ <tr>
+ <td>$Lang::tr{'acct rrdsize'}</td>
+ <td>$sizerrd MB</td>
+ </tr>
+ <tr>
+ <td>$Lang::tr{'acct oldestdb'}</td>
+ <td>$min</td>
+ </tr>
+ <tr>
+ <td>$Lang::tr{'acct latestdb'}</td>
+ <td>$max</td>
+ </tr>
+ </table>
+ <br>
+END
+ &Header::closebox();
+ #print Database maintenance table
+ &Header::openbox('100%', 'left', $Lang::tr{'acct dbmaintenance'});
+ print<<END;
+ <br>
+ <form method='post' action='$ENV{'SCRIPT_NAME'}'>
+ <table style='width:100%;' cellspacing='0' class='tbl'>
+ <tr>
+ <th>$Lang::tr{'acct task'}</th>
+ <th>$Lang::tr{'acct parameter'}</th>
+ <th>$Lang::tr{'acct action'}</th>
+ </tr>
+ <tr>
+ <td>$Lang::tr{'acct emptydb'}</td>
+ <td></td>
+ <td><input type='submit' name='ACTION2' value='$Lang::tr{'acct commit'}' style='width:10em'></td>
+ </tr>
+ <tr>
+ <td>$Lang::tr{'acct emptydbtraf'}</td>
+ <td></td>
+ <td><input type='submit' name='ACTION' value='$Lang::tr{'acct commit'}' style='width:10em'></td>
+ </tr>
+ <tr>
+ <td>$Lang::tr{'acct delbefore'} (ACCT,ACCT_HIST)</td>
+ <td><select name='expmonth'>
+END
+ for(my $i=1;$i<13;$i++){
+ print"<option>$i</option>";
+ }
+ print"</select><select name='expyear'>";
+ for(my $i=2014;$i<=($year+1900);$i++){
+ print"<option>$i</option>";
+ }
+ print<<END;
+ </select></td>
+ <td><input type='submit' name='ACTION1' value='$Lang::tr{'acct commit'}' style='width:10em'></td>
+ </tr>
+ </table>
+ </form>
+END
+ &Header::closebox();
+#BackButton
+ print<<END;
+ <form method='post' action='$ENV{'SCRIPT_NAME'}'>
+ <table style='width:100%;'>
+ <tr>
+ <td></td>
+ <td align='right'><input type='submit' name='ACTION' value='$Lang::tr{'back'}'></td>
+ </tr>
+ </table>
+ </form>
+END
+
+
+ &Header::closebigbox();
+ &Header::closepage();
+ &ACCT::closedb;
+ exit 0;
+}
+
+sub getdate{
+ #GET : Timestamp in seconds since 1.1.1970
+ #GIVES: Date in Format dd.mm.yyyy HH:MM:SS
+ my $val = $_[0];
+ my $y=sprintf("%02d",(localtime($val))[5]-100);
+ my $Y=sprintf("%04d",(localtime($val))[5]+1900);
+ my $m=sprintf("%02d",(localtime($val))[4]+1);
+ my $d=sprintf("%02d",(localtime($val))[3]);
+ my $H=sprintf("%02d",(localtime($val))[2]);
+ my $M=sprintf("%02d",(localtime($val))[1]);
+ my $S=sprintf("%02d",(localtime($val))[0]);
+
+ return "$d.$m.$Y $H:$M:$S";
+}
+
+sub menu{
+ &Header::openbox('100%', 'left', $Lang::tr{'menu'});
+ print<<END;
+ <form method='post' action='$ENV{'SCRIPT_NAME'}'>
+ <table style='width: 100%;'>
+ <tr>
+ <td style='display:inline;'>
+ <input type='submit' name='ACTION' value='$Lang::tr{'acct config'}'>
+ <input type='submit' name='ACTION' value='$Lang::tr{'acct addresses'}'>
+ <input type='submit' name='ACTION' value='$Lang::tr{'acct billgroup'}'>
+END
+ if ($settings{'EXPERT'} eq 'on'){
+ print "<input type='submit' name='ACTION' value='$Lang::tr{'acct maintenance'}'>";
+ }
+ print<<END;
+ </td>
+ </tr>
+ </table>
+ </form>
+END
+ &Header::closebox();
+}
+
+sub graphsite{
+ my $grmon=$_[0];
+ my $gryear=$_[1];
+ my $grhost=$_[2];
+
+ &Header::openpage("$Lang::tr{'acct host detail'} $grhost", 1, '');
+ &Header::openbigbox('100%', 'center');
+ &Header::openbox('100%', 'left', );
+
+ &generatemonthgraph($grmon,$gryear,$grhost);
+
+ &Header::closebox();
+ &Header::closebigbox();
+ &Header::closepage();
+ exit 0;
+}
+
+sub generatemonthgraph{
+ my $grmon=$_[0];
+ my $gryear=$_[1];
+ my $grhost=$_[2];
+ my ($from,$till) = &ACCT::getmonth($grmon,$gryear);
+ my @values=();
+ my $sth;
+ my $cnt=0;
+ #If we want to show Data from within last 2 months, get DATA from ACCT
+ if ( ! $grmon < ($mon+1) && $gryear == ($year+1900)){
+ $sth=&ACCT::getmonthgraphdata("ACCT",$from,$till,$grhost);
+ }else{
+ #If we want to show data from a date older than last two months, use ACCT_HIST
+ $sth=&ACCT::getmonthgraphdata("ACCT_HIST",$from,$till,$grhost);
+ }
+ foreach( @$sth ) {
+ $cnt++;
+ foreach my $i (0..$#$_) {
+ push (@{$values[$i]},($_->[$i]));
+ }
+ }
+
+ for my $graph (GD::Graph::area->new(600,200))
+ {
+ my $name = $cgiparams{'host'};
+ $graph->set(
+ bgclr => 'white',
+ fgclr => 'black',
+ boxclr => '#eeeeee',
+ accentclr => 'dblue',
+ valuesclr => '#ffff77',
+ labelclr => 'black',
+ axislabelcl =>'black',
+ legendclr =>'black',
+ valuesclr =>'black',
+ textclr =>'black',
+ dclrs => [qw(lgreen lred)],
+ x_label => $Lang::tr{'date'},
+ y_label => $Lang::tr{'acct mb'},
+ title => $name,
+ y_long_ticks => 1,
+ #x_label_skip => 3, #skip every 3 x-axis title
+ x_label_position => 0,
+ transparent => 0,
+ );
+
+ $graph->set_legend("$Lang::tr{'acct traffic'}");
+ $graph->set(x_labels_vertical => 1, values_vertical => 1);
+ my $gd=$graph->plot(\@values);
+ open(IMG, '>/srv/web/ipfire/html/accounting/tmpgraph.png') or die $!;
+ binmode IMG;
+ print IMG $gd->png;
+ }
+ #Show Box with monthly graph for this host
+ &Header::openbox('100%', 'left', $Lang::tr{'acct traffic monthly'});
+ print "<center><img src='/accounting/tmpgraph.png' alt='$grhost'></center>";
+ print "<b>$cgiparams{'traffic'}</b>";
+ &Header::closebox();
+ print<<END;
+ <table style='width:100%;'>
+ <tr>
+ <form method='post' action='$ENV{'SCRIPT_NAME'}'>
+ <td align='right'><input type='submit' name='ACTION' value='$Lang::tr{'back'}'></td>
+ </form>
+ </tr>
+ </table>
+END
+}
+
+sub generatehourgraph{
+ my $grmon=$_[0];
+ my $gryear=$_[1];
+ my $grhost=$_[2];
+ my ($from,$till) = &ACCT::getmonth($grmon,$gryear);
+ my @values=();
+ my $sth;
+ my $cnt=0;
+ #If we want to show Data from within last 2 months, get DATA from ACCT
+ if ( ! $grmon < ($mon+1) && $gryear == ($year+1900)){
+ $sth=&ACCT::getgraphdata("ACCT",$from,$till,$grhost);
+ }else{
+ #If we want to show data from a date older than last two months, use ACCT_HIST
+ $sth=&ACCT::getgraphdata("ACCT_HIST",$from,$till,$grhost);
+ }
+ foreach( @$sth ) {
+ $cnt++;
+ foreach my $i (0..$#$_) {
+ #print "$_->[$i] "
+ if ($i == 1){
+ push (@{$values[$i]},($_->[$i]/1024/1024));
+ }else{
+ push (@{$values[$i]},$_->[$i]);
+ }
+ }
+ }
+
+ print"<br><br><br>";
+
+ for my $graph (GD::Graph::area->new(600,200))
+ {
+ my $name = $cgiparams{'host'};
+ print STDERR "Processing $name mit sosse\n";
+
+ $graph->set(
+ x_label => 'X Label',
+ y_label => 'Y label',
+ title => 'An Area Graph',
+ #y_max_value => 40,
+ #y_tick_number => 8,
+ #y_label_skip => 2,
+ #accent_treshold => 41,
+ transparent => 0,
+ );
+
+ $graph->set_legend( 'one', 'two' );
+ my $gd=$graph->plot(\@values);
+ open(IMG, '>/srv/web/ipfire/html/test/file.png') or die $!;
+ binmode IMG;
+ print IMG $gd->png;
+ }
+ sleep 1;
+ print "<img src='/test/file.png' alt='Tanzmaus'>";
+}
+
+sub statusbox{
+ my $bgcolor1='';
+ my $bgcolor2='';
+ my $message;
+ if ($proxsrv eq $Lang::tr{'stopped'}){
+ $bgcolor1="bgcolor='${Header::colourred}' align='center'";
+ $message="<br>$Lang::tr{'acct proxy_enable'}";
+ }else{
+ $bgcolor1="bgcolor='${Header::colourgreen}' align='center'";
+ }
+ if ($proxlog eq $Lang::tr{'stopped'}){
+ $bgcolor2="bgcolor='${Header::colourred}' align='center'";
+ $message=$message."<br>$Lang::tr{'acct proxylog_enable'}";
+ }else{
+ $bgcolor2="bgcolor='${Header::colourgreen}' align='center'";
+ }
+ &Header::openbox('100%', 'left', );
+ print<<EOF;
+ <center><table width='50%' class='tbl'>
+ <tr>
+ <th>$Lang::tr{'service'}</th>
+ <th>$Lang::tr{'status'}</th>
+ </tr>
+ <tr>
+ <td bgcolor='$color{'color22'}'>$Lang::tr{'proxy'}</td>
+ <td $bgcolor1><font color='white'>$proxsrv</td>
+ <tr>
+ <tr>
+ <td bgcolor='$color{'color20'}'>$Lang::tr{'logging'}</td>
+ <td $bgcolor2><font color='white'>$proxlog</td>
+ </tr>
+EOF
+ if ($message){
+ print"<tr><td colspan='2'><font color='red'>$message<br>$Lang::tr{'acct nodata'}</td></tr>";
+ }
+ print"</table>";
+ &Header::closebox();
+}
+
+sub calcbytes{
+ #GET: Value (bytes)
+ #GIVES: Value (in MB,GB,TB) With Type ("MB","GB","TB")
+ my $val=$_[0];
+ my $type;
+ if (($val/1024) < 1024){
+ #Calc KB
+ $val=sprintf "%.2f",$val/1024;
+ $type=$Lang::tr{'acct kb'};
+ }elsif (($val/(1024*1024)) < 1024){
+ #Calc MB
+ $val=sprintf "%.2f",$val/(1024*1024);
+ $type=$Lang::tr{'acct mb'};
+ }elsif(($val/(1024*1024*1024)) < 1024){
+ #Calc GB
+ $val=sprintf "%.2f",$val/(1024*1024*1024);
+ $type=$Lang::tr{'acct gb'};
+ }elsif(($val/(1024*1024*1024*1024)) < 1024){
+ #Calc TB
+ $val=sprintf "%.2f",$val/(1024*1024*1024*1024);
+ $type=$Lang::tr{'acct tb'};
+ }
+ return ($val,$type);
+}
+
+sub addressmgmnt{
+ #This function shows the site "address management"
+ &Header::openpage("$Lang::tr{'acct addresses'}", 1, '');
+ &Header::openbigbox('100%', 'center');
+ &error;
+ my $col1;
+ my $host=0;
+ my $cust=0;
+ #Get all Addresses from DB
+ my $res = &ACCT::getaddresses;
+
+&Header::openbox('100%', 'left',$Lang::tr{'acct edit_addr'} );
+
+#When no address preselected, set COMPANYTYPE to "CUST"
+if ($cgiparams{'rdo_companytype'} eq ''){
+ $cgiparams{'rdo_companytype'} = 'CUST';
+}
+$checked{'rdo_companytype'}{$cgiparams{'rdo_companytype'}} = 'CHECKED';
+
+#NEW AddressBox
+print<<END;
+ <form method='post' action='$ENV{'SCRIPT_NAME'}'>
+ <table style=width:100%;'>
+ <tr>
+ <td style='width:8em;'></td>
+ <td></td>
+ <td colspan='2'><font size="1">$Lang::tr{'acct hint_hoster'}:</font></td>
+ </tr>
+ <tr>
+ <td>$Lang::tr{'acct companytype'}</td>
+ <td>
+ <input type='radio' name='rdo_companytype' value='CUST' $checked{'rdo_companytype'}{'CUST'}>$Lang::tr{'acct customer'}
+ <input type='radio' name='rdo_companytype' value='HOST' $checked{'rdo_companytype'}{'HOST'}>$Lang::tr{'acct hoster'}</td>
+ <td style='width:8em;'>$Lang::tr{'acct bank'}</td>
+ <td>
+ <input type='text' name='txt_bank' value='$cgiparams{'txt_bank'}' style='width:25em;'></td>
+ </tr>
+ <tr>
+ <td>$Lang::tr{'acct company'}</td>
+ <td>
+ <input type='text' name='txt_company' value='$cgiparams{'txt_company'}' style='width:25em;'></td>
+ <td>$Lang::tr{'acct iban'}<img src='/blob.gif' alt='*' /></td>
+ <td>
+ <input type='text' name='txt_iban' value='$cgiparams{'txt_iban'}' style='width:25em;'></td>
+ </tr>
+ <tr>
+ <td>$Lang::tr{'acct name1'}<img src='/blob.gif' alt='*' /></td>
+ <td>
+ <input type='text' name='txt_name1' value='$cgiparams{'txt_name1'}' style='width:25em;'></td>
+ <td>$Lang::tr{'acct bic'}<img src='/blob.gif' alt='*' /></td>
+ <td>
+ <input type='text' name='txt_bic' maxlength='8' value='$cgiparams{'txt_bic'}' style='width:25em;'></td>
+ </tr>
+ <tr>
+ <td>$Lang::tr{'acct str'}</td>
+ <td align='left'>
+ <input type='text' name='txt_str' value='$cgiparams{'txt_str'}' style='width:25em;'></td>
+ <td>$Lang::tr{'acct blz'}</td>
+ <td>
+ <input type='text' name='txt_blz' maxlength='8' value='$cgiparams{'txt_blz'}' style='width:25em;'></td>
+ </tr>
+ <tr>
+ <td>$Lang::tr{'acct str_nr'}</td>
+ <td><input type='text' name='txt_str_nr' value='$cgiparams{'txt_str_nr'}' style='width:25em;'></td>
+ <td>$Lang::tr{'acct kto'}</td>
+ <td>
+ <input type='text' name='txt_kto' value='$cgiparams{'txt_kto'}' style='width:25em;'></td>
+ </tr>
+
+
+ <tr>
+ <td>$Lang::tr{'acct plz'}</td>
+ <td>
+ <input type='text' name='txt_plz' value='$cgiparams{'txt_plz'}' style='width:25em;'></td>
+ <td>$Lang::tr{'acct email'}</td>
+ <td><input type='text' name='txt_email' value='$cgiparams{'txt_email'}' style='width:25em;'></td>
+ </tr>
+ <tr>
+ <td>$Lang::tr{'acct city'}</td>
+ <td><input type='text' name='txt_city' value='$cgiparams{'txt_city'}' style='width:25em;'></td>
+ <td>$Lang::tr{'acct inet'}<img src='/blob.gif' alt='*' /></td>
+ <td>
+ <input type='text' name='txt_inet' value='$cgiparams{'txt_inet'}' style='width:25em;'></td>
+ </tr>
+ <tr>
+ <td></td>
+ <td></td>
+ <td>$Lang::tr{'acct hrb'}<img src='/blob.gif' alt='*' /></td>
+ <td>
+ <input type='text' name='txt_hrb' value='$cgiparams{'txt_hrb'}' style='width:25em;'></td>
+ </tr>
+ <tr>
+ <td></td>
+ <td></td>
+ <td>$Lang::tr{'acct ustid'}</td>
+ <td><input type='text' name='txt_ustid' value='$cgiparams{'txt_ustid'}' style='width:25em;'></td>
+ </tr>
+ <tr>
+ <td></td>
+ <td></td>
+ <td>$Lang::tr{'acct tel'}<img src='/blob.gif' alt='*' /></td>
+ <td>
+ <input type='text' name='txt_tel' value='$cgiparams{'txt_tel'}' style='width:25em;'></td>
+ </tr>
+ <tr>
+ <td></td>
+ <td></td>
+ <td>$Lang::tr{'acct fax'}<img src='/blob.gif' alt='*' /></td>
+ <td>
+ <input type='text' name='txt_fax' value='$cgiparams{'txt_fax'}' style='width:25em;'></td>
+ </tr>
+ <tr>
+ <td colspan='6'><img src='/blob.gif' alt='*' /><font size="1">$Lang::tr{'acct optional'}</font></td>
+ </tr>
+ <tr>
+END
+ if($cgiparams{'update'} eq 'on'){
+ print"<input type='hidden' name='oldcompname' value='$cgiparams{'oldcompname'}'>";
+ print "<td colspan='6' align='right' display:inline><input type='submit' name='ACTION' value='$Lang::tr{'update'}'></td>";
+ }else{
+ print "<td colspan='6' align='right' display:inline><input type='submit' name='ACTION1' value='$Lang::tr{'save'}'></td>";
+ }
+ print"</tr></table></form>";
+&Header::closebox();
+#Upper BackButton
+print<<END;
+ <form method='post' action='$ENV{'SCRIPT_NAME'}'>
+ <table style='width:100%;'>
+ <tr>
+ <td align='right'><input type='submit' name='ACTION' value='$Lang::tr{'back'}'></td>
+ </tr>
+ </table>
+ </form>
+END
+#Check if we need to show HOSTBOX and/or CUSTBOX
+foreach my $row (@$res) {
+ my ($gr,$comp,$type,$name1,$str,$nr,$plz,$city,$bank,$iban,$bic,$blz,$kto,$email,$inet,$hrb,$ustid,$tel,$fax) = @$row;
+ if ($type eq 'HOST'){
+ $host=1;
+ }
+ if ($type eq 'CUST'){
+ $cust=1;
+ }
+}
+#Show HOSTER Addresses if any
+ if ($host){
+ $count=0;
+ #EXISTING HOST BOX
+ &Header::openbox('100%', 'left',$Lang::tr{'acct exst_host_addr'} );
+ my $float;
+ print "<table style='width:100%'>";
+ foreach my $row (@$res) {
+ #SET colors for tablerows
+ $col="style='background-color:$color{'color22'}'";
+ $col1="style='background-color:#e2d8d8'";
+ my ($gr,$comp,$type,$name1,$str,$nr,$plz,$city,$bank,$iban,$bic,$blz,$kto,$email,$inet,$hrb,$ustid,$tel,$fax) = @$row;
+ if ($cgiparams{'oldcompname'} eq $comp){
+ $col="style='background-color:yellow'";
+ $col1="style='background-color:yellow'";
+ }
+ if ($type eq 'HOST'){
+ $count++;
+ if($count % 2){
+ print"<tr><td width='50%' valign='top' align='center'>";
+ }else{
+ print"</td><td width='50%' valign='top' align='center'>";
+ }
+ print<<END;
+ <table style='width:90%;' cellspacing='0' class='tbl'>
+ <tr>
+ <th align='left' width='50%'>$Lang::tr{'acct company'}</th>
+ <th align='left' width='10%' >$Lang::tr{'acct bank'}</th>
+ <th width='38%' ></th>
+ <th align='right' width='1%' style="PADDING: 0px">
+ <form method='post' action='$ENV{'SCRIPT_NAME'}'>
+ <input type='image' src='/images/edit.gif' alt=$Lang::tr{'acct edit'} title='$Lang::tr{'acct edit'}' />
+ <input type='hidden' name='ACTION' value='edit_addr'>
+ <input type='hidden' name='rdo_companytype' value='$type'>
+ <input type='hidden' name='txt_company' value='$comp'>
+ <input type='hidden' name='txt_name1' value='$name1'>
+ <input type='hidden' name='txt_str' value='$str'>
+ <input type='hidden' name='txt_str_nr' value='$nr'>
+ <input type='hidden' name='txt_plz' value='$plz'>
+ <input type='hidden' name='txt_city' value='$city'>
+ <input type='hidden' name='txt_bank' value='$bank'>
+ <input type='hidden' name='txt_iban' value='$iban'>
+ <input type='hidden' name='txt_bic' value='$bic'>
+ <input type='hidden' name='txt_blz' value='$blz'>
+ <input type='hidden' name='txt_kto' value='$kto'>
+ <input type='hidden' name='txt_email' value='$email'>
+ <input type='hidden' name='txt_inet' value='$inet'>
+ <input type='hidden' name='txt_hrb' value='$hrb'>
+ <input type='hidden' name='txt_ustid' value='$ustid'>
+ <input type='hidden' name='txt_tel' value='$tel'>
+ <input type='hidden' name='txt_fax' value='$fax'>
+ </form></th>
+ <th width='1%' style="PADDING: 0px">
+ <form method='post' action='$ENV{'SCRIPT_NAME'}'>
+ <input type='image' src='/images/delete.gif' alt=$Lang::tr{'acct deladr'} title=$Lang::tr{'acct deladr'} />
+ <input type='hidden' name='txt_company' value='$comp'>
+ <input type='hidden' name='ACTION' value='del_addr'>
+ </form>
+ </th>
+ </tr>
+ <tr>
+ <td $col><b>$comp</b></td>
+ <td $col1></td>
+ <td colspan='3' $col1></td>
+ </tr>
+ <tr>
+ <td $col>$name1</td>
+ <td $col1 width='35%'>$Lang::tr{'acct bank'}</td>
+ <td colspan='3' $col1>$bank</td>
+ </tr>
+ <tr>
+ <td $col></td>
+ <td $col1>$Lang::tr{'acct iban'}</td>
+ <td colspan='3' $col1>$iban</td>
+ </tr>
+ <tr>
+ <td $col>$str $nr</td>
+ <td $col1>$Lang::tr{'acct bic'}</td>
+ <td colspan='3' $col1>$bic</td>
+ </tr>
+ <tr>
+ <td $col>$plz $city</td>
+ <td $col1>$Lang::tr{'acct blz'}</td>
+ <td colspan='3' $col1>$blz</td>
+ </tr>
+ <tr>
+ <td $col></td>
+ <td $col1>$Lang::tr{'acct kto'}</td>
+ <td colspan='3' $col1>$kto</td>
+ </tr>
+ </table>
+ <br>
+ </td>
+END
+ if (! $count % 2){
+ print"</tr>";
+ }
+ }
+ }
+ if ( ($count % 2)){
+ print"<td width='50%' valign='top' align='center'></td></tr>";
+ }
+ print "</table>";
+ &Header::closebox();
+ }else{
+ &Header::openbox('100%', 'left',$Lang::tr{'acct exst_host_addr'} );
+ print $Lang::tr{'acct host empty'};
+ &Header::closebox();
+ }
+#Show CUSTOMER Addresses if any
+ if($cust){
+ $count=0;
+ #EXISTING CUSTOMER BOX
+ &Header::openbox('100%', 'left',$Lang::tr{'acct exst_cust_addr'} );
+ print "<table style='width:100%;'><tr>";
+ foreach my $row (@$res) {
+ #SET colors for tablerows
+ $col="style='background-color:$color{'color22'}'";
+ my ($gr,$comp,$type,$name1,$str,$nr,$plz,$city,$bank,$iban,$bic,$blz,$kto,$email,$inet,$hrb,$ustid,$tel,$fax) = @$row;
+ if ($cgiparams{'oldcompname'} eq $comp){
+ $col="style='background-color:yellow'";
+ }
+ if ($type eq 'CUST'){
+ $count++;
+ print"<td width='15%' valign='top' align='center'>";
+
+ print<<END;
+ <table style='width:90%;' cellspacing='0' class='tbl'>
+ <tr>
+ <th align='left'>
+ $Lang::tr{'acct company'}
+ </th>
+ <th width='1%' style="PADDING: 0px">
+ <form method='post' action='$ENV{'SCRIPT_NAME'}'>
+ <input type='image' src='/images/edit.gif' alt='$Lang::tr{'acct edit'}' title='$Lang::tr{'acct edit'}' />
+ <input type='hidden' name='ACTION' value='edit_addr'>
+ <input type='hidden' name='rdo_companytype' value='$type'>
+ <input type='hidden' name='txt_company' value='$comp'>
+ <input type='hidden' name='txt_name1' value='$name1'>
+ <input type='hidden' name='txt_str' value='$str'>
+ <input type='hidden' name='txt_str_nr' value='$nr'>
+ <input type='hidden' name='txt_plz' value='$plz'>
+ <input type='hidden' name='txt_city' value='$city'>
+ <input type='hidden' name='txt_bank' value='$bank'>
+ <input type='hidden' name='txt_iban' value='$iban'>
+ <input type='hidden' name='txt_bic' value='$bic'>
+ <input type='hidden' name='txt_blz' value='$blz'>
+ <input type='hidden' name='txt_kto' value='$kto'>
+ <input type='hidden' name='txt_email' value='$email'>
+ <input type='hidden' name='txt_inet' value='$inet'>
+ <input type='hidden' name='txt_hrb' value='$hrb'>
+ <input type='hidden' name='txt_ustid' value='$ustid'>
+ <input type='hidden' name='txt_tel' value='$tel'>
+ <input type='hidden' name='txt_fax' value='$fax'>
+ </form>
+ </th>
+ <th width='1%' style="padding: 0px">
+ <form method='post' action='$ENV{'SCRIPT_NAME'}'>
+ <input type='image' src='/images/delete.gif' alt=$Lang::tr{'acct deladr'} title=$Lang::tr{'acct deladr'} />
+ <input type='hidden' name='ACTION' value='del_addr' />
+ <input type='hidden' name='txt_company' value='$comp' />
+ </form>
+ </th>
+ </tr>
+ <tr>
+ <td colspan='3' $col><b>$comp</b></td>
+ </tr>
+ <tr>
+ <td colspan='3' $col>$name1</td>
+ </tr>
+ <tr>
+ <td colspan='3' $col>$str $nr</td>
+ </tr>
+ <tr>
+ <td colspan='3' $col>$plz $city</td>
+ </tr>
+ </table><br>
+ </td>
+
+END
+ if(! ($count % 3)) {
+ print"</tr><tr>";
+ }
+ }
+
+ }
+ if ($count %2){
+ print"<td width='15%' valign='top' align='center'></td></tr>";
+ }
+ print"</table>";
+ &Header::closebox();
+ }else{
+ &Header::openbox('100%', 'left',$Lang::tr{'acct exst_cust_addr'} );
+ print $Lang::tr{'acct cust empty'};
+ &Header::closebox();
+ }
+ &Header::closebigbox();
+ &Header::closepage();
+ exit 0;
+}
+
+sub checkfield{
+ my $field=$_[0];
+ my $fieldvalue=$_[1];
+ my $errormessage=$_[2];
+
+ if (!&validtextfield($fieldvalue)){
+ if(!$fieldvalue){
+ $errormessage.="$Lang::tr{'acct empty field'} $field<br>";
+ }else{
+ $errormessage.="$Lang::tr{'acct invalid'} $field<br>";
+ }
+ }
+ return $errormessage;
+}
+
+sub checkaddress{
+ #Check if an address with the same name alread exists
+ if ($cgiparams{'update'} ne 'on'){
+ my $res=&ACCT::getaddresses;
+ foreach my $row (@$res) {
+ my ($anz,$name)=@$row;
+ if ($name eq $cgiparams{'txt_company'}){
+ $errormessage.=$Lang::tr{'acct companyexists'};
+ }
+ }
+ }
+ #Check Companyfield
+ $errormessage=&checkfield($Lang::tr{'acct company'},$cgiparams{'txt_company'},$errormessage);
+
+ #Check Name1
+ if($cgiparams{'txt_name1'}){
+ $errormessage=&checkfield($Lang::tr{'acct name1'},$cgiparams{'txt_name1'},$errormessage);
+ }
+
+ #Check Name2
+ if($cgiparams{'txt_name2'}){
+ $errormessage=&checkfield($Lang::tr{'acct name2'},$cgiparams{'txt_name2'},$errormessage);
+ }
+
+ #Check STREET
+ $errormessage=&checkfield($Lang::tr{'acct str'},$cgiparams{'txt_str'},$errormessage);
+
+ #Check STREET-NR
+ if (! $cgiparams{'txt_str_nr'}){
+ $errormessage.="$Lang::tr{'acct empty field'} $Lang::tr{'acct str_nr'}<br>";
+ }else{
+ if(! &validalphanumfield($cgiparams{'txt_str_nr'})){
+ $errormessage.="$Lang::tr{'acct invalid'} $Lang::tr{'acct str_nr'}<br>";
+ }
+ }
+
+ #Check POSTAL-CODE
+ if (! $cgiparams{'txt_plz'}){
+ $errormessage.="$Lang::tr{'acct empty field'} $Lang::tr{'acct plz'}<br>";
+ }else{
+ if(! &validnumfield($cgiparams{'txt_plz'})){
+ $errormessage.="$Lang::tr{'acct invalid'} $Lang::tr{'acct plz'}<br>";
+ }
+ }
+
+ #Check CITY
+ $errormessage=&checkfield($Lang::tr{'acct city'},$cgiparams{'txt_city'},$errormessage);
+
+ #Check E-MAIL
+ if(! $cgiparams{'txt_email'}){
+ $errormessage.="$Lang::tr{'acct empty field'} $Lang::tr{'acct email'}<br>";
+ }else{
+ if (! &General::validemail($cgiparams{'txt_email'})){
+ $errormessage.="<br>$Lang::tr{'acct invalid'} $Lang::tr{'acct email'}";
+ }
+ }
+
+ #Check all fields required for companytype "HOST"
+ if ($cgiparams{'rdo_companytype'} eq 'HOST'){
+ #Check BANK
+ $errormessage=&checkfield($Lang::tr{'acct bank'},$cgiparams{'txt_bank'},$errormessage);
+
+ #Check IBAN - optional
+ if($cgiparams{'txt_iban'}){
+ if(!&validalphanumfield($cgiparams{'txt_iban'})){
+ $errormessage.="$Lang::tr{'acct invalid'} $Lang::tr{'acct iban'}<br>";
+ }
+ }
+ if($cgiparams{'txt_bic'}){
+ if(!&validalphanumfield($cgiparams{'txt_bic'})){
+ $errormessage.="$Lang::tr{'acct invalid'} $Lang::tr{'acct bic'}<br>";
+ }
+ }
+ if(($cgiparams{'txt_iban'} && $cgiparams{'txt_blz'})||(!$cgiparams{'txt_iban'} && $cgiparams{'txt_blz'})){
+ #Check BLZ
+ if(! &validnumfield($cgiparams{'txt_blz'})){
+ $errormessage.="$Lang::tr{'acct invalid'} $Lang::tr{'acct blz'}<br>";
+ }
+ #Check BANKACCOUNT
+ if($cgiparams{'txt_kto'}){
+ if(! &validnumfield($cgiparams{'txt_kto'})){
+ $errormessage.="$Lang::tr{'acct invalid'} $Lang::tr{'acct kto'}<br>";
+ }
+ }elsif(!$cgiparams{'txt_kto'}){
+ $errormessage.="$Lang::tr{'acct empty field'} $Lang::tr{'acct kto'}<br>";
+ }
+ }elsif(!$cgiparams{'txt_iban'} && !$cgiparams{'txt_blz'}){
+ $errormessage.="$Lang::tr{'acct empty field'} $Lang::tr{'acct blz'}<br>";
+ #Check BANKACCOUNT
+ if($cgiparams{'txt_kto'}){
+ if(! &validnumfield($cgiparams{'txt_kto'})){
+ $errormessage.="$Lang::tr{'acct invalid'} $Lang::tr{'acct kto'}<br>";
+ }
+ }elsif(!$cgiparams{'txt_kto'}){
+ $errormessage.="$Lang::tr{'acct empty field'} $Lang::tr{'acct kto'}<br>";
+ }
+ }
+
+ #Check Internet
+ if($cgiparams{'txt_inet'}){
+ if ($cgiparams{'txt_inet'} =~ m/([a-z]+:\/\/)??([a-z0-9\-]+\.){1}(([a-z0-9\-]+\.){0,})([a-z0-9\-]+){1}/o) {
+ $cgiparams{'txt_inet'}=$2.$3.$5;
+ } else {
+ $errormessage.="$Lang::tr{'acct invalid'} $Lang::tr{'acct inet'}<br>";
+ }
+ }
+ #Check Hrb
+ if($cgiparams{'txt_hrb'}){
+ $errormessage=&checkfield($Lang::tr{'acct hrb'},$cgiparams{'txt_hrb'},$errormessage);
+ }
+
+ }
+
+ #Check Phone
+ if($cgiparams{'txt_tel'}){
+ if (!&validphonefield($cgiparams{'txt_tel'})){
+ $errormessage.="$Lang::tr{'acct invalid'} $Lang::tr{'acct tel'}<br>";
+ }
+ }
+
+ return $errormessage;
+}
+
+sub checkproxy{
+ if(-f "${General::swroot}/proxy/enable"){
+ $proxsrv=$Lang::tr{'running'};
+ }else{
+ $proxsrv=$Lang::tr{'stopped'};
+ }
+ my $srce = "${General::swroot}/proxy/squid.conf";
+ my $string1 = 'access\.log';
+ open(FH, $srce);
+ while(my $line = <FH>) {
+ if($line =~ m/$string1/) {
+ $proxlog=$Lang::tr{'running'};
+ }
+ }
+ close FH;
+ return;
+}
+
+sub validtextfield{
+ #GET: Input from a Textfield
+ #GIVES: True if valid, false if not valid
+ my $remark = $_[0];
+
+ # Each part should be at least two characters in length
+ # but no more than 63 characters
+ if (length ($remark) < 1 || length ($remark) > 255) {
+ return 0;}
+ # Only valid characters are a-z, A-Z, 0-9 and -
+ if ($remark !~ /^[a-zäöüA-ZÖÄÜ0-9-.:;\&\|\ß_()\/\s]*$/) {
+ return 0;}
+ # First character can only be a letter or a digit
+ if (substr ($remark, 0, 1) !~ /^[a-zäöüA-ZÖÄÜ0-9(]*$/) {
+ return 0;}
+ # Last character can only be a letter or a digit
+ if (substr ($remark, -1, 1) !~ /^[a-zöäüA-ZÖÄÜ0-9.:;_)]*$/) {
+ return 0;}
+ return 1;
+}
+
+sub validnumfield{
+ #GET: Input from a numeric field
+ #GIVES: True if valid, false if not valid
+ my $num = $_[0];
+
+ # Each part should be at least two characters in length
+ # but no more than 63 characters
+ if (length ($num) < 1 || length ($num) > 255) {
+ return 0;}
+ # Only valid characters are a-z, A-Z, 0-9 and -
+ if ($num !~ /^[0-9.]*$/) {
+ return 0;}
+ # First character can only be a letter or a digit
+ if (substr ($num, 0, 1) !~ /^[0-9]*$/) {
+ return 0;}
+ # Last character can only be a letter or a digit
+ if (substr ($num, -1, 1) !~ /^[0-9]*$/) {
+ return 0;}
+ return 1;
+}
+
+sub validphonefield{
+ #GET: Input from a numeric field
+ #GIVES: True if valid, false if not valid
+ my $num = $_[0];
+
+ # Each part should be at least two characters in length
+ # but no more than 63 characters
+ if (length ($num) < 1 || length ($num) > 255) {
+ return 0;}
+ # Only valid characters are a-z, A-Z, 0-9 and -
+ if ($num !~ /^[0-9-()\+ ]*$/) {
+ return 0;}
+ # First character can only be a letter or a digit
+ if (substr ($num, 0, 1) !~ /^[0-9(\+]*$/) {
+ return 0;}
+ # Last character can only be a digit
+ if (substr ($num, -1, 1) !~ /^[0-9]*$/) {
+ return 0;}
+ return 1;
+}
+
+sub validalphanumfield{
+ #GET: Input from a numeric field
+ #GIVES: True if valid, false if not valid
+ my $remark = $_[0];
+
+ # Each part should be at least two characters in length
+ # but no more than 63 characters
+ if (length ($remark) < 1 || length ($remark) > 255) {
+ return 0;}
+ # Only valid characters are a-z, A-Z, 0-9 and -
+ if ($remark !~ /^[0-9a-zA-Z]*$/) {
+ return 0;}
+ # First character can only be a letter or a digit
+ if (substr ($remark, 0, 1) !~ /^[0-9A-Za-z]*$/) {
+ return 0;}
+ # Last character can only be a letter or a digit
+ if (substr ($remark, -1, 1) !~ /^[0-9a-zA-Z]*$/) {
+ return 0;}
+ return 1;
+}
+
+sub error{
+ if ($errormessage) {
+ &Header::openbox('100%', 'left', $Lang::tr{'error messages'});
+ print "<class name='base'>$errormessage\n";
+ print " </class>\n";
+ &Header::closebox();
+ }
+}
+
+sub billgroupsite{
+ &Header::openpage("$Lang::tr{'acct billgroup'}", 1, '');
+ &Header::openbigbox('100%', 'center');
+ &error;
+ my $host;
+ my $cust;
+ my @oldhosts;
+ my $grp;
+ my $mailrcpt;
+ my $ccmailrcpt;
+ #Get addresses from DB
+ my $res = &ACCT::getaddresses;
+ #Check if we need to show NEW-BillGROUP-BOX or Hint
+ foreach my $row (@$res) {
+ my ($gr,$comp,$type,$name1,$str,$nr,$plz,$city,$bank,$iban,$bic,$blz,$kto,$email,$inet,$hrb,$ust,$tel,$fax,$ccmail) = @$row;
+ $grp=$gr;
+ $mailrcpt=$email;
+ $ccmailrcpt=$ccmail;
+ if ($type eq 'HOST'){
+ $host=1;
+ }
+ if ($type eq 'CUST'){
+ $cust=1;
+ }
+ }
+if ($host && $cust){
+ #Fill CCMAIL
+ if (!$cgiparams{'txt_ccmail'}){
+ $cgiparams{'txt_ccmail'}=$ccmailrcpt;
+ }
+ &Header::openbox('100%', 'left',"$Lang::tr{'acct edit_addr'}" );
+ print<<END;
+ <form method='post' action='$ENV{'SCRIPT_NAME'}' ENCTYPE='multipart/form-data'>
+ <table style='width:100%;'>
+ <tr>
+ <td style='width: 22em;'>$Lang::tr{'name'}</td>
+ <td><input type='text' name='txt_billgroup' value='$cgiparams{'txt_billgroup'}' style='width: 24em;'></td>
+ </tr>
+ <tr>
+ <td><br></td>
+ </tr>
+ <tr>
+ <td valign='top'>$Lang::tr{'acct billtext1'}<img src='/blob.gif' alt='*' /></td>
+ <td><textarea name='txt_billtext1' cols='40' rows='5' style='width: 24em;' maxlength='300'>$cgiparams{'txt_billtext1'}</textarea></td>
+ </tr>
+ <tr><td><br></td></tr>
+END
+ #Print Dropdown Menu for HOSTER and CUSTOMER
+ print "<tr><td>$Lang::tr{'acct hoster'}</td><td><select name='dd_host' style='width: 24.3em;'>";
+ foreach my $row (@$res) {
+ my ($gr,$comp,$type) = @$row;
+ if ($type eq 'HOST'){
+ if($cgiparams{'dd_host'} eq $comp){
+ print"<option selected>$comp</option>";
+ }else{
+ print"<option>$comp</option>";
+ }
+ }
+ }
+ print "</select></td></tr><tr><td><br></td></tr>";
+ print "<tr><td>$Lang::tr{'acct customer'}</td><td><select name='dd_cust' style='width: 24.3em;'>";
+ foreach my $row (@$res) {
+ my ($gr,$comp,$type) = @$row;
+ if ($type eq 'CUST'){
+ if($cgiparams{'dd_cust'} eq $comp){
+ print"<option selected>$comp</option>";
+ }else{
+ print"<option>$comp</option>";
+ }
+ }
+ }
+ print "</select></td></tr>";
+ print "<tr><td><br></td></tr>";
+ #Print multiselectbox for hosts/users which should be part of this group
+ my $hosts=&ACCT::gethosts;
+ print"<tr><td valign='top'>$Lang::tr{'acct members'}</td><td><select name='sel_hosts' multiple size='8' style='width: 24em;' >";
+ #If update, split $cgiparams{'sel_hosts'} and preselect values from selectbox
+ if($cgiparams{'update'} eq 'on'){
+ $cgiparams{'oldname'}=$cgiparams{'txt_billgroup'};
+ @oldhosts=split(/\|/,$cgiparams{'sel_hosts'});
+ }
+ foreach my $row (@$hosts) {
+ my ($val)=@$row;
+ my $sel=0;
+ foreach my $old (@oldhosts){
+ if ($old eq $val){
+ $sel=1;
+ }
+ }
+ if ($sel){
+ print"<option selected>$val</option>";
+ }else{
+ print"<option>$val</option>";
+ }
+ }
+ print"</select></td></tr>";
+ print "<tr><td><br></td></tr>";
+ #set right decimal seperator for cent value
+ setlocale(LC_NUMERIC,"$mainsettings{'LANGUAGE'}_$uplang");
+ my $val=sprintf"%.3f",$cgiparams{'txt_cent'};
+ print"<tr><td>$Lang::tr{'acct cent'}</td><td><input type='text' name='txt_cent' value='$val' size='3'>$settings{'CURRENCY'} </td></tr>";
+ #Optional note
+ print"<tr><td colspan='2' align='left'><img src='/blob.gif' alt='*' /><font size='1'>$Lang::tr{'acct optional'}</font></td></tr>";
+ print"<tr><td colspan='2' align='right'><br><br>";
+ print"</td></tr></table>";
+
+ #LOGO Upload if update eq 'on'
+ if ($cgiparams{'update'} eq 'on'){
+ print<<END;
+ <table style='width:100%;'>
+ <tr>
+ <td style='width: 22em;'>$Lang::tr{'acct logo upload'}</td>
+ <td><INPUT TYPE="file" NAME="uploaded_file" SIZE=30 MAXLENGTH=80></td>
+ <input type='hidden' name='logo_grp' value='$cgiparams{'txt_billgroup'}' />
+ </tr>
+ <tr>
+ <td><br>$Lang::tr{'acct logo'}</td>
+END
+ #Show Logo in webinterface with 1/2 size if set
+ if (-f "$logopath/$cgiparams{'txt_billgroup'}/logo.png"){
+ print"<td><img src='/accounting/logo/$cgiparams{'txt_billgroup'}/logo.png' alt='$logopath/$cgiparams{'txt_billgroup'}/logo.png' width='25%' height='25%' /></td></tr>";
+ }else{
+ print"<td><br>$Lang::tr{'no'}</td></tr>";
+ }
+ #Show optional CC Mailadresses
+ print<<END;
+ <tr>
+ <td><br>$Lang::tr{'acct mailrcpt'}</td>
+ <td><br>$mailrcpt</td>
+ </tr>
+ <tr>
+ <td><br>$Lang::tr{'acct ccmail'}</td>
+ <td><br><input type='text' name='txt_ccmail' value='$cgiparams{'txt_ccmail'}' style='width: 24.3em;'></td>
+ </tr>
+
+
+
+END
+ print"</table>";
+ }
+ print"<table style='width:100%;'><tr><td align='right'>";
+ #Print SAVE or EDIT Button
+ if($cgiparams{'update'} eq 'on'){
+ print "<input type='submit' name='BILLACTION' value='$Lang::tr{'update'}'>";
+ print "<input type='hidden' name='oldname' value='$cgiparams{'oldname'}'>";
+ print "<input type='hidden' name='oldccmail' value='$ccmailrcpt'>";
+ }else{
+ print "<input type='submit' name='BILLACTION' value='$Lang::tr{'save'}'>";
+ }
+ print"</td></tr></table><br></form><br>";
+ &Header::closebox();
+#BackButton
+ print<<END;
+ <form method='post' action='$ENV{'SCRIPT_NAME'}'>
+ <table style='width:100%;'>
+ <tr>
+ <td align='right'>
+ <input type='submit' name='ACTION' value='$Lang::tr{'back'}'>
+ </td>
+ </tr>
+ </table>
+ </form>
+END
+ #Show Box for fixed positions if update
+ if ($cgiparams{'update'} eq 'on'){
+ &viewtablebillpos($cgiparams{'txt_billgroup'});
+ }
+ if($grp >0){
+ &viewtablebillgroups;
+ }
+
+}else{
+ &Header::openbox('100%', 'left',"$Lang::tr{'hint'}" );
+ print "$Lang::tr{'acct hint billgrp'}";
+ &Header::closebox();
+ #BackButton
+print<<END;
+ <table style='width:100%;'>
+ <tr>
+ <form method='post' action='$ENV{'SCRIPT_NAME'}'>
+ <td align='right'><input type='submit' name='ACTION' value='$Lang::tr{'back'}'></td>
+ </form>
+ </tr>
+ </table>
+END
+}
+ &Header::closebigbox();
+ &Header::closepage();
+ exit 0;
+}
+
+sub viewtablebillgroups{
+ $count=0;
+ &Header::openbox('100%', 'left',"$Lang::tr{'acct billgroup'}" );
+ #Get DATA from table BILLINGGRP
+ my $res = &ACCT::getbillgroups;
+
+ #Print table billinggroup
+ print<<END;
+ <table style='width:100%;' cellspacing='0' class='tbl'>
+ <tr>
+ <th align='left'>$Lang::tr{'name'}</th>
+ <th align='left'>$Lang::tr{'acct hoster'}</th>
+ <th align='left'>$Lang::tr{'acct customer'}</th>
+ <th align='left'>$Lang::tr{'acct members'}</th>
+ <th align='left' colspan='5'></th>
+ </tr>
+END
+ foreach my $line (@$res){
+ $count++;
+ if($count % 2){
+ $col="style='background-color:$color{'color22'}'";
+ }else{
+ $col="style='background-color:$color{'color20'}'";
+ }
+ my ($name,$host,$cust,$txt,$amount,$cent) = @$line;
+ print<<END;
+ <tr>
+ <td $col>$name</td>
+ <td $col>$host</td>
+ <td $col>$cust</td>
+ <td $col>$amount</td>
+ <td width='1%' $col>
+END
+ my $members=&ACCT::listhosts($name);
+ my @mem=split(/\|/,$members);
+ my $msg;
+ foreach my $m (@mem){
+ $msg.=$m."\n";
+ }
+ print"<img src='/images/computer.png' alt=$Lang::tr{'acct members'} title='".$msg."' /></td>";
+ print<<END;
+
+ <td width='1%' $col>
+ <form method='post' action='$ENV{'SCRIPT_NAME'}'>
+ <input type='image' src='/images/edit.gif' alt='$Lang::tr{'edit'}' title='$Lang::tr{'edit'}' />
+ <input type='hidden' name='BILLACTION' value='edit_billgroup'>
+ <input type='hidden' name='txt_billgroup' value='$name'>
+ <input type='hidden' name='txt_billtext1' value='$txt'>
+ <input type='hidden' name='dd_host' value='$host'>
+ <input type='hidden' name='dd_cust' value='$cust'>
+ <input type='hidden' name='sel_hosts' value='$members'>
+ <input type='hidden' name='txt_cent' value='$cent'>
+ </form>
+ </td>
+
+ <td width='1%' $col>
+ <form method='post' action='$ENV{'SCRIPT_NAME'}'>
+ <input type='image' src='/images/folder-open.png' alt='$Lang::tr{'edit'}' title='$Lang::tr{'acct billarchive'}' />
+ <input type='hidden' name='BILLACTION' value='open_billgroup'>
+ <input type='hidden' name='txt_billgroup' value='$name'>
+ </form>
+ </td>
+
+ <td width='1%' $col>
+ <form method='post' action='$ENV{'SCRIPT_NAME'}'>
+ <input type='image' src='/images/document-new.png' alt='$Lang::tr{'acct preview'}' title='$Lang::tr{'acct preview'}' />
+ <input type='hidden' name='BILLACTION' value='open_preview'>
+ <input type='hidden' name='txt_billgroup' value='$name'>
+ </form>
+ </td>
+ <td width='1%' $col>
+ <form method='post' action='$ENV{'SCRIPT_NAME'}'>
+ <input type='image' src='/images/delete.gif' alt=$Lang::tr{'delete'} title="$Lang::tr{'delete'}" />
+ <input type='hidden' name='BILLACTION' value='delete_billgroup'>
+ <input type='hidden' name='txt_billgroup' value='$name'>
+ </form>
+ </td>
+ </tr>
+END
+ }
+ print "</table>";
+ &ACCT::closedb;
+ &Header::closebox();
+}
+
+sub viewtablehosts{
+ $dbh=&ACCT::connectdb;
+ &Header::openbox('100%', 'left', $Lang::tr{'acct hosts'});
+ my $mon=$_[0];
+ my $year=$_[1];
+ my ($from,$till)=&ACCT::getmonth($mon,$year);
+ $count=0;
+ #Menu to display another month
+ print<<END;
+ <form method='post' action='$ENV{'SCRIPT_NAME'}'>
+ <table style='width:100%;'>
+ <tr>
+ <td style='width:5%; text-align:center;'>$Lang::tr{'acct month'}</td>
+ <td style='width:10%; text-align: center;'>$Lang::tr{'acct year'}</td>
+ <td></td>
+ </tr>
+ <tr>
+ <td><select name='month'>
+END
+ for (my $i=1;$i<13;$i++){
+ if(($_[0]) eq $i){
+ print"<option selected>$i</option>";
+ }else{
+ print"<option>$i</option>";
+ }
+ }
+ print<<END;
+ </select></td>
+ <td style='text-align: center;'><select name='year'>
+END
+ for (my $j=2014;$j<=($year);$j++){
+ if(($_[1]) eq $j){
+ print"<option selected>$j</option>";
+ }else{
+ print"<option>$j</option>";
+ }
+ }
+ print<<END;
+ </select></td>
+ <td><input type='submit' name='ACTION' value='$Lang::tr{'acct view'}'></td>
+ </tr>
+ </table></form>
+ <br>
+END
+ #View table with all hosts
+ print<<END;
+ <table style='width:100%;' class='tbl'>
+ <tr>
+ <th>$Lang::tr{'name'}</th>
+ <th>$Lang::tr{'acct traffic'}</th>
+ <th>$Lang::tr{'from'}</th>
+ <th>$Lang::tr{'to'}</th>
+ <th></th>
+ </tr>
+END
+ my $res = $dbh->selectall_arrayref("SELECT SUM(BYTES),min(TIME_RUN),max(TIME_RUN),NAME from ACCT where TIME_RUN between ".$from." and ".$till." group by NAME;");
+ my $sumbytes;
+ my $type;
+ my $lineval;
+ if (@$res){
+ foreach my $row (@$res) {
+ $count++;
+ $lineval='';
+ $type='';
+ if($count % 2){
+ $col="background-color:$color{'color22'};";
+ }else{
+ $col="background-color:$color{'color20'};";
+ }
+ my ($bytes, $mintime, $maxtime, $name) = @$row;
+ $sumbytes +=$bytes;
+ ($lineval,$type) = &calcbytes($bytes);
+ #Print Line
+ print"<tr><td style='$col'>$name</td><td style='$col text-align: right;'>$lineval $type</td><td style='$col text-align: center;'>".&getdate($mintime)."</td><td style='$col text-align: center;'>".&getdate($maxtime)."</td>";
+ print<<END;
+ <td style='$col'>
+ <form method='post' action='$ENV{'SCRIPT_NAME'}'>
+ <input type='image' src='/images/utilities-system-monitor.png' alt="$Lang::tr{'status'}" title="$Lang::tr{'status'}" />
+ <input type='hidden' name='ACTION' value='viewgraph'>
+ <input type='hidden' name='host' value='$name'>
+ <input type='hidden' name='month' value='$mon'>
+ <input type='hidden' name='year' value='$year'>
+ <input type='hidden' name='traffic' value="$Lang::tr{'acct sum'} $Lang::tr{'acct traffic'} $lineval $type">
+ </form>
+
+ </td>
+ </tr>
+END
+ }
+ }else{
+ print "<tr><td colspan='5'><center>$Lang::tr{'acct no data'}</td></tr>";
+ }
+ print "</table>";
+ &Header::closebox();
+ &ACCT::closedb;
+}
+
+sub viewtablebillpos{
+ my $grp=$_[0];
+ #BOX for extra billpositions
+ &Header::openbox('100%', 'left',"$Lang::tr{'acct fix billpos'} $grp" );
+ #Table for optional billpositions
+ print<<END;
+ <center><table style='width:65%' cellspacing='0' class='tbl' border='0'>
+ <tr>
+ <th align='left'>$Lang::tr{'acct amount'}</th>
+ <th align='left' style='padding-left:1.2em'>$Lang::tr{'acct name'}</th>
+ <th align='left'>$Lang::tr{'acct price pp'}</th>
+ <th></th>
+ </tr>
+END
+ #Fill Table for extra billpositions if any
+ if ($cgiparams{'update'} eq 'on'){
+ my $res = &ACCT::getextrabillpos($cgiparams{'txt_billgroup'});
+ $count=0;
+ foreach my $line (@$res){
+ $count++;
+ if($count % 2){
+ $col="style='background-color:$color{'color22'}'";
+ }else{
+ $col="style='background-color:$color{'color20'}'";
+ }
+ my ($grp,$amnt,$pos,$price) = @$line;
+ setlocale(LC_NUMERIC,"$mainsettings{'LANGUAGE'}_$uplang");
+ my $locale_price=sprintf"%.2f",$price;
+ print "<tr><form method='post' action='$ENV{'SCRIPT_NAME'}'><td $col style='padding-right:1.2em' align='right'>$amnt</td><td $col style='padding-left:1.2em'>$pos</td><td $col style='padding-right:1.2em' align='right'>$locale_price $settings{'CURRENCY'}</td>";
+ print "<td $col><input type='image' src='/images/delete.gif' alt=$Lang::tr{'delete'} title=$Lang::tr{'delete'} >";
+ print "<input type='hidden' name='BILLPOS' value='delpos'>";
+ print "<input type='hidden' name='txt_billgroup' value='$grp'>";
+ print "<input type='hidden' name='txt_billpos' value='$pos'></form></tr>";
+ }
+ }
+ print<<END;
+ <tr>
+
+ <td ><form method='post' action='$ENV{'SCRIPT_NAME'}'><input type='text' name='txt_amount' value='$cgiparams{'txt_amount'}' size='3'></td>
+ <td><input type='text' name='txt_name' value='$cgiparams{'txt_name'}' size='40'></td>
+ <td ><input type='text' name='txt_price' value='$cgiparams{'txt_price'}' size='6'></td>
+ <td></td>
+ </tr>
+ </table><br>
+ <table style='width:100%'>
+ <tr>
+ <td align='right'><input type='submit' name='BILLPOS' value='$Lang::tr{'save'}'></td>
+ <input type='hidden' name='txt_billgroup' value='$grp'>
+ </form>
+ </tr>
+ </table></form>
+END
+
+ &Header::closebox();
+}
+
+sub billoverview{
+ my $grp=shift;
+ my $col;
+ my $count=0;
+ #Open site
+ &Header::openpage($Lang::tr{'acct billoverview'}, 1, '');
+ &Header::openbigbox('100%', 'center');
+ &Header::openbox('100%', 'left', $grp);
+ my $res=&ACCT::getbills($grp);
+
+ if (@$res > 0){
+ print<<END;
+ <table style='width:100%' cellspacing='0' class='tbl' border='0'>
+ <tr>
+ <th>$Lang::tr{'acct nr'}</th>
+ <th>$Lang::tr{'acct path'}</th>
+ <th>$Lang::tr{'name'}</th>
+ <th>$Lang::tr{'acct generated'}</th>
+ <th></th>
+ </tr>
+
+END
+ foreach my $row (@$res){
+ $count++;
+ if($count % 2){
+ $col="style='background-color:$color{'color22'}'";
+ }else{
+ $col="style='background-color:$color{'color20'}'";
+ }
+ my ($no,$path,$name,$date,$dbgrp) = @$row;
+
+ print "<tr><td $col>$no</td><td $col>$path</td><td $col>$name</td><td $col>$date</td><td $col>";
+ print "<form method='post' action='$ENV{'SCRIPT_NAME'}'><input type='image' src='/images/updbooster/updxl-src-adobe.gif' alt='$Lang::tr{'edit'}' title='$Lang::tr{'edit'}' />";
+ print "<input type='hidden' name='BILLVIEW' value='show'>";
+ my $file="$path/$name";
+ print "<input type='hidden' name='file' value='$file'>";
+ print "<input type='hidden' name='name' value='$name'>";
+ print"</form></td></tr>";
+ }
+ print "</table><br>";
+ }else{
+ print "<center>$Lang::tr{'acct no data'}";
+ }
+ &Header::closebox();
+
+ #BackButton
+print<<END;
+ <table style='width:100%'>
+ <tr>
+ <form method='post' action='$ENV{'SCRIPT_NAME'}'>
+ <td align='right'><input type='submit' name='ACTION' value='$Lang::tr{'back'}'></td>
+ </form>
+ </tr>
+ </table>
+END
+ &Header::closebigbox();
+ &Header::closepage();
+ exit 0;
+}
+
+sub checkmailsettings{
+ #Check if mailserver is an ip address or a domain
+ if ($cgiparams{'txt_mailserver'} =~ /^(\d+)\.(\d+)\.(\d+)\.(\d+)$/){
+ if (! &General::validip($cgiparams{'txt_mailserver'})){
+ $errormessage.="$Lang::tr{'acct invalid mailip'} $cgiparams{'txt_mailserver'}<br>";
+ }
+ }elsif(! &General::validfqdn($cgiparams{'txt_mailserver'})){
+ $errormessage.="$Lang::tr{'acct invalid mailfqdn'} $cgiparams{'txt_mailserver'}<br>";
+ }
+ #Check valid mailserverport
+ if($cgiparams{'txt_mailport'} < 1 || $cgiparams{'txt_mailport'} > 65535){
+ $errormessage.="$Lang::tr{'acct invalid mailport'} $cgiparams{'txt_mailport'}<br>";
+ }
+ #Check valid sender
+ if(! $cgiparams{'txt_mailsender'}){
+ $errormessage.="$Lang::tr{'acct empty field'} $Lang::tr{'acct mailsender'}<br>";
+ }else{
+ if (! &General::validemail($cgiparams{'txt_mailsender'})){
+ $errormessage.="<br>$Lang::tr{'acct invalid'} $Lang::tr{'acct mailsender'}<br>";
+ }
+ }
+ return $errormessage;
+}
--- /dev/null
+#!/usr/bin/perl
+###############################################################################
+# #
+# IPFire.org - A linux based firewall #
+# Copyright (C) 2014 IPFire Team <alexander.marx@ipfire.org> #
+# #
+# This program is free software: you can redistribute it and/or modify #
+# it under the terms of the GNU General Public License as published by #
+# the Free Software Foundation, either version 3 of the License, or #
+# (at your option) any later version. #
+# #
+# This program is distributed in the hope that it will be useful, #
+# but WITHOUT ANY WARRANTY; without even the implied warranty of #
+# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the #
+# GNU General Public License for more details. #
+# #
+# You should have received a copy of the GNU General Public License #
+# along with this program. If not, see <http://www.gnu.org/licenses/>. #
+# #
+###############################################################################
+package ACCT;
+
+use DBI;
+use POSIX;
+use Time::Local;
+use PDF::API2;
+use utf8;
+use Encode;
+use File::Copy;
+
+###############################################################################
+my $dbh;
+my $dsn="dbi:SQLite:dbname=/var/ipfire/accounting/acct.db";
+my ($sec,$min,$hour,$mday,$mon,$year,$wday,$ydat,$isdst)=localtime();
+my %mainsettings;
+###############################################################################
+
+&General::readhash("/var/ipfire/main/settings", \%mainsettings);
+my $uplang=uc($mainsettings{'LANGUAGE'});
+
+#############
+# Functions #
+#############
+
+sub connectdb {
+ $dbh = DBI->connect($dsn, "", "",{RaiseError => 1, AutoCommit => 1})or die "ERROR $!";
+ return $dbh;
+}
+
+sub closedb {
+ $dbh->disconnect();
+ return $dbh;
+}
+
+sub getminmax {
+ my $min;
+ my $max;
+ $dbh=&connectdb;
+ my $sth = $dbh->prepare('Select min(TIME_RUN),max(TIME_RUN) from ACCT;');
+ $sth->execute;
+ while ( my @row = $sth->fetchrow_array ) {
+ $min=$row[0];
+ $max=$row[1];
+ }
+ $dbh->disconnect();
+ return ($min,$max);
+}
+
+sub cleardbtraf {
+ &connectdb;
+ $dbh->do("DELETE FROM ACCT;");
+ $dbh->do("DELETE FROM ACCT_HIST;");
+ &closedb;
+}
+
+sub cleardb {
+ &connectdb;
+ $dbh->do("DELETE FROM ACCT;");
+ $dbh->do("DELETE FROM ACCT_HIST;");
+ $dbh->do("DELETE FROM ACCT_ADDR ");
+ $dbh->do("DELETE FROM BILLINGGRP");
+ $dbh->do("DELETE FROM BILLINGHOST");
+ &closedb;
+}
+
+sub delbefore {
+ my $till=$_[0];
+ &connectdb;
+ $dbh->do("DELETE FROM ACCT WHERE TIME_RUN < ".$till.";");
+ $dbh->do("DELETE FROM ACCT_HIST WHERE TIME_RUN < date('".$till."','unixepoch');");
+ &closedb;
+}
+
+sub movedbdata {
+ $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');");
+ $dbh->do("DELETE FROM ACCT WHERE datetime(TIME_RUN,'unixepoch') < date('now','-2 months');");
+}
+
+sub gethourgraphdata {
+ my $table=$_[0];
+ my $from=$_[1];
+ my $till=$_[2];
+ my $name=$_[3];
+ my $res;
+ $dbh=connectdb;
+ if ($table eq 'ACCT'){
+ $res = $dbh->selectall_arrayref( "SELECT TIME_RUN,BYTES FROM ACCT WHERE TIME_RUN BETWEEN ".$from." AND ".$till." AND NAME = '".$name."';");
+ }else{
+ $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."';");
+ }
+ return $res;
+}
+
+sub getmonthgraphdata {
+ my $table=$_[0];
+ my $from=$_[1];
+ my $till=$_[2];
+ my $name=$_[3];
+ my $res;
+ $dbh=connectdb;
+ if ($table eq 'ACCT'){
+ $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;");
+ }else{
+ $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;");
+ }
+ $dbh=closedb;
+ return $res;
+}
+
+sub writeaddr {
+ my $comp = $_[0];
+ my $type = $_[1];
+ my $name1 = $_[2];
+ my $str = $_[3];
+ my $nr = $_[4];
+ my $post = $_[5];
+ my $city = $_[6];
+ my $bank = $_[7];
+ my $iban = $_[8];
+ my $bic = $_[9];
+ my $blz = $_[10];
+ my $kto = $_[11];
+ my $mail = $_[12];
+ my $inet = $_[13];
+ my $hrb = $_[14];
+ my $ustid = $_[15];
+ my $tel = $_[16];
+ my $fax = $_[17];
+ $dbh=&connectdb;
+ #COMPANY,TYPE,NAME1,STR,NR,POSTCODE,CITY,BANK,IBAN,BLZ,ACCOUNT,EMAIL,INTERNET,HRB,USTID,TEL,FAX
+ 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 ( ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
+ my $sth = $dbh->prepare( $sql );
+ $sth->execute( $comp,$type,$name1,$str,$nr,$post,$city,$bank,$iban,$bic,$blz,$kto,$mail,$inet,$hrb,$ustid,$tel,$fax );
+ $dbh=&closedb;
+}
+
+sub updateaddr {
+ my $type = $_[0];
+ my $comp = $_[1];
+ my $name1 = $_[2];
+ my $str = $_[3];
+ my $nr = $_[4];
+ my $post = $_[5];
+ my $city = $_[6];
+ my $bank = $_[7];
+ my $iban = $_[8];
+ my $bic = $_[9];
+ my $blz = $_[10];
+ my $kto = $_[11];
+ my $mail = $_[12];
+ my $inet = $_[13];
+ my $hrb = $_[14];
+ my $ustid = $_[15];
+ my $tel = $_[16];
+ my $fax = $_[17];
+ my $oldname = $_[18];
+ $dbh=&connectdb;
+ $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.";
+ $dbh=&closedb;
+}
+
+sub deladdr {
+ my $comp = $_[0];
+ $dbh=&connectdb;
+ $dbh->do("DELETE FROM ACCT_ADDR WHERE COMPANY=?", undef,$comp )or die "Could not delete address $comp!";
+ $dbh=&closedb;
+}
+
+sub getaddresses {
+ $dbh=&connectdb;
+ my $res=$dbh->selectall_arrayref("SELECT (SELECT COUNT(NAME) FROM BILLINGGRP GROUP BY NAME),* FROM ACCT_ADDR ORDER BY COMPANY;");
+ $dbh=&closedb;
+ return $res;
+}
+
+sub gethosts{
+ $dbh=&connectdb;
+ my $res =$dbh->selectall_arrayref("SELECT NAME from ACCT GROUP BY NAME ORDER BY NAME;");
+ $dbh=&closedb;
+ return $res;
+}
+
+sub getbillgroups {
+ $dbh=connectdb;
+ 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;");
+ return $res;
+ $dbh->disconnect();
+}
+
+sub getextrabillpos {
+ my $grp=$_[0];
+ $dbh=&connectdb;
+ my $res=$dbh->selectall_arrayref("SELECT * from BILLPOS WHERE GRP =?", undef,$grp);
+ $dbh=&closedb;
+ return $res;
+}
+
+sub savebillgroup {
+ my $grp=$_[0];
+ my $txt=$_[1];
+ my $host=$_[2];
+ my $cust=$_[3];
+ my $ust=$_[4];
+ my @ips=@{$_[5]};
+ $dbh=&connectdb;
+ my $sql = "INSERT INTO BILLINGGRP (NAME,BILLTEXT,HOST,CUST,CENT) VALUES (?,?,?,?,?)";
+ my $sth = $dbh->prepare( $sql );
+ $sth->execute( $grp,$txt,$host,$cust,$ust );
+ foreach my $ip (@ips){
+ my $sql = "INSERT INTO BILLINGHOST (GRP,HOST) VALUES (?,?)";
+ my $sth = $dbh->prepare( $sql ) or die "Could not prepare insert into BILLINGHOST $!";
+ $sth->execute( $grp,$ip ) or die "Could not execute INSERT into BILLINGHOST $!";
+ }
+ $dbh=&closedb;
+}
+
+sub updatebillgrouphost {
+ my $oldgrp=$_[0];
+ my $newgrp=$_[1];
+ $dbh=&connectdb;
+ my $sql = "UPDATE BILLINGGRP SET HOST=? WHERE HOST=?;";
+ my $sth = $dbh->prepare( $sql );
+ $sth->execute( $newgrp,$oldgrp );
+ $dbh=&closedb;
+}
+
+sub updatebillgroupcust {
+ my $oldgrp=$_[0];
+ my $newgrp=$_[1];
+ $dbh=&connectdb;
+ my $sql = "UPDATE BILLINGGRP SET CUST=? WHERE CUST=?;";
+ my $sth = $dbh->prepare( $sql );
+ $sth->execute( $newgrp,$oldgrp );
+ $dbh=&closedb;
+}
+
+sub deletebillgroup {
+ my $name=shift;
+ $dbh=connectdb;
+ $dbh->do("DELETE FROM BILLINGGRP WHERE NAME=?;", undef,$name);
+ $dbh->do("DELETE FROM BILLINGHOST WHERE GRP=?;", undef,$name);
+ &closedb;
+}
+
+sub savebillpos {
+ my $grp=$_[0];
+ my $amnt=$_[1];
+ my $pos=$_[2];
+ my $price=$_[3];
+ $dbh=&connectdb;
+ my $sql = "INSERT INTO BILLPOS (GRP,AMOUNT,POS,PRICE) VALUES (?,?,?,?)";
+ my $sth = $dbh->prepare( $sql )or die "Could not prepare insert into BILLINGPOS $!";
+ $sth->execute( $grp,$amnt,$pos,$price ) or die "Could not execute INSERT into BILLINGHOST $!";
+ $dbh->disconnect();
+}
+
+sub updatebillpos {
+ my $oldgrp=shift;
+ my $newgrp=shift;
+ $dbh=&connectdb;
+ my $sql = "UPDATE BILLPOS SET GRP=? WHERE GRP=?;";
+ my $sth = $dbh->prepare( $sql );
+ $sth->execute( $newgrp,$oldgrp );
+ my $sql1 = "UPDATE BILLS SET GRP=? WHERE GRP=?;";
+ my $sth1 = $dbh->prepare( $sql1 );
+ $sth1->execute( $newgrp,$oldgrp );
+ $dbh=&closedb;
+ #Now rename directories
+ rename ("/srv/web/ipfire/html/accounting/logo/$oldgrp","/srv/web/ipfire/html/accounting/logo/$newgrp");
+ rename ("/var/ipfire/accounting/bill/$oldgrp","/var/ipfire/accounting/bill/$newgrp")
+
+}
+
+sub delbillpos_single {
+ my $pos=$_[0];
+ my $grp=$_[1];
+ my $sql = "DELETE FROM BILLPOS WHERE GRP=? AND POS=?;";
+ $dbh=&connectdb;
+ my $sth = $dbh->prepare( $sql )or die "Could not prepare DELETE POS from BILLINGPOS $!";
+ $sth->execute( $grp,$pos ) or die "Could not execute DELETE from BILLINGHOST $!";
+ $dbh=&closedb;
+}
+
+sub delbillpos {
+ my $grp=$_[0];
+ my $sql = "DELETE FROM BILLPOS WHERE GRP=?;";
+ $dbh=&connectdb;
+ my $sth = $dbh->prepare( $sql )or die "Could not prepare DELETE POS from BILLINGPOS $!";
+ $sth->execute( $grp ) or die "Could not execute DELETE from BILLINGHOST $!";
+ $dbh=&closedb;
+}
+
+sub listhosts{
+ my $name=$_[0];
+ my $a;
+ my $res=$dbh->selectall_arrayref("SELECT * FROM BILLINGHOST WHERE GRP='".$name."';");
+ foreach my $gzu (@$res){
+ my ($x,$y)=@$gzu;
+ $a.= "|$y";
+ }
+ return $a;
+}
+
+sub checkusergrp {
+ $dbh=connectdb;
+ my $res=$dbh->selectall_arrayref("SELECT * FROM BILLINGHOST;");
+ $dbh->disconnect();
+ return $res;
+}
+
+sub getmonth{
+ #GET : 1. month 2. year
+ #GIVES: 1.day of given month AND last day of given month in seconds since 1.1.1970
+ ($sec,$min,$hour,$mday,$mon,$year,$wday,$ydat,$isdst)=localtime();
+ my $jahr=$_[1];
+ my $monat=$_[0]-1 if($_[0]);
+ my $tag=1;
+ my $time1=timelocal(0,0,0,$tag,$monat,$jahr);
+ my $time2=timelocal(0,0,0,$tag,($monat+1),$jahr);
+ --$time2;
+ return ($time1,$time2);
+}
+
+sub GetTaValues {
+ $dbh=&connectdb;
+ my $from = $_[0]; #unixtimestamp
+ my $till = $_[1]; #unixtimestamp
+ my $grp = $_[2]; #Billgroupname
+ 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 $!";
+ my $nri1 = @$all;
+ my @return;
+ my $cnt=0;
+ if ($nri1 eq "0"){
+ $return[$cnt]="999";
+ }
+ else
+ {
+ foreach my $row (@$all){
+ my ($bytes,$billgrp,$host) = @$row;
+ $return[$cnt]="$bytes,$billgrp,$host";
+ $cnt++;
+ }
+ }
+ &closedb;
+ return @return;
+}
+
+sub getTaAddress {
+ my $grp=$_[0];
+ my $type=$_[1];
+ $dbh=&connectdb;
+ 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);
+ &closedb;
+ return $res;
+}
+
+sub checkbillgrp {
+ my $comp=$_[0];
+ $dbh=&connectdb;
+ my $res=$dbh->selectall_arrayref("SELECT NAME,HOST,CUST FROM BILLINGGRP;");
+ &closedb;
+ return $res;
+}
+
+sub pdf2 {
+ my @billar = @{$_[0]}; #DATA from sendbill (just host/values)
+ my $month = $_[1];
+ $month = '0'.$month if $month < 10;
+ my $year = $_[2];
+ my $mwst = $_[3];
+ my @address_cust= @{$_[4]}; #Array which contains customer and hoster adresses and some additional info from billgroup
+ my @address_host= @{$_[5]};
+ my @billpos = @{$_[6]};
+ my $grp = $_[7];
+ my $cur = $_[8]; #(Eur,USD,...)
+ my $preview = $_[9];
+ my $no = &getBillNr;
+ my $name = $month."-".$year."-".$no.".pdf";
+ my $path ="/var/ipfire/accounting/bill/";
+ my $filename = "$path/$grp/$name";
+ my @summen; #Used for counting the sums
+ my $x = 500;
+ my $y = 1;
+ my $zwsum;
+ my $pages = 0;
+ my $anzbillpos = @billpos;
+ my $anz = (@billar+$anzbillpos)/18; #Total pages
+ $anz = ceil($anz); #round the $anz value
+ my $aktpage=1;
+ my $sum=0;
+ my $sum1=0;
+ my $lines;
+ my $title;
+ my $txt;
+ my $txt1;
+ my $txt2;
+ my $txt3;
+ my $txt4;
+ my $txt5;
+ my $fnt;
+ my $fnt1;
+ my $fulldate = strftime('%d.%m.%Y',localtime(time()));
+ 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);
+ my($company_cust,$type_cust,$name1_cust,$str_cust,$str_nr_cust,$plz_cust,$city_cust);
+
+ #First of all check if directory exists, else create it
+ if(! -d "$path/$grp" && $preview ne 'on'){
+ mkdir("$path/$grp",0777);
+ }
+
+ #Check if we are creating a preview or a real bill
+ if($preview eq 'on'){
+ $filename="$path/tmp.pdf";
+ }
+ ####################################################################
+ #Prepare DATA from arrays
+ ####################################################################
+ #Get HOSTER for this grp
+ foreach my $addrline (@address_host){
+ ($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;
+ }
+ #Get CUST for this grp
+ foreach my $addrline_cust (@address_cust){
+ ($company_cust,$type_cust,$name1_cust,$str_cust,$str_nr_cust,$plz_cust,$city_cust)=@$addrline_cust;
+ }
+
+
+ #Generate PDF File
+ my $pdf = PDF::API2->new(-file => $filename);
+ $pdf->mediabox('A4');
+ my $page = $pdf->page;
+ $fnt = $pdf->corefont('Helvetica');
+ $fnt1 = $pdf->corefont('HelveticaBold');
+
+ #Set lines
+ $lines = $page->gfx;
+ $title = $page->gfx;
+ $lines->strokecolor('grey');
+ $lines->linewidth('0.5');
+
+ #Fill BILL DATA into PDF
+ setlocale(LC_ALL, "$mainsettings{'LANGUAGE'}_$uplang");
+ foreach (@billar) {
+ my ($a1,$a2) = split( /\,/, $_ );
+ $a2=sprintf"%.2f",($a2/1024/1024);
+ my $sum=(($a2)*$cent);
+ $sum = sprintf"%.2f",($sum);
+ # Seitenwechsel ermitteln
+ if ($y % 18 == 0) {
+ $txt1->translate(390, 120);
+ $txt1->text($Lang::tr{'acct pdf zwsum'}); #Pos
+ $zwsum=sprintf("%.2f",($zwsum));
+ $txt1->translate(540, 120);
+ $txt1->text_right("$zwsum".decode('utf8',$cur)); #Pos
+ $zwsum=0;
+ $pages++;
+ $aktpage++;
+ $x=500;
+ $page=$pdf->page;
+ #draw lines
+ $lines = $page->gfx;
+ $title = $page->gfx;
+ $lines->strokecolor('grey');
+ $lines->linewidth('0.5');
+ }
+
+ #TITLES
+ $title->linewidth(14);
+ $title->move(385, 168);
+ $title->line(545, 168); #Title of SUMBOX
+ $title->move(60, 523);
+ $title->line(545, 523);#Bottom horiz. line of Title
+
+ # Generate Tables
+ $lines->move(59, 745);
+ $lines->line(545, 745);
+ $lines->move(59, 563);
+ $lines->line(545, 563);
+
+ # Addressbox
+ $lines->move(61, 710);
+ $lines->line(61, 715, 66, 715); #TL
+ $lines->move(61, 610);
+ $lines->line(61, 605, 66, 605); #BL
+ $lines->move(285, 715);
+ $lines->line(290, 715, 290, 710); #TR
+ $lines->move(290, 610);
+ $lines->line(290, 605, 285, 605); #BR
+
+ # Table for positions
+ $lines->move(60, 530);
+ $lines->line(60, 200); #First vert. line POS
+ $lines->move(90, 523);
+ $lines->line(90, 200); #Second vert. line
+ $lines->move(280, 523);
+ $lines->line(280, 200); #third vert. line
+ $lines->move(385, 523);
+ $lines->line(385, 200); #third vert. line
+ $lines->move(430, 523);
+ $lines->line(430, 200); #fourth vert. line
+ $lines->move(545, 530);
+ $lines->line(545, 200); #fifth vert. line
+ $lines->move(60, 200);
+ $lines->line(545, 200); #Bottom horizontal line
+
+ #SUM BOX
+ $lines->move(385, 175);
+ $lines->line(385, 115); #Left vert. line of SUMBOX
+ $lines->move(545, 175);
+ $lines->line(545, 115); #Right vert. line of SUMBOX
+ $lines->move(385, 115);
+ $lines->line(545, 115); #Bottom horiz. line of SUMBOX
+
+ #Lines on right side after sender and after "bank"
+ $lines->move(420, 723);
+ $lines->line(545, 723);# Line "Sender"
+ $lines->move(420, 648);
+ $lines->line(545, 648);# Line "Bank"
+ $lines->move(420, 600);
+ $lines->line(545, 600);# Line HRB/USTID
+
+ #Make lines Visible
+ $lines->stroke;
+ $title->stroke;
+ if (-f "/srv/web/ipfire/html/accounting/logo/$grp/logo.png"){
+ #Image LOGO
+ my $gfx = $page->gfx;
+ my $image = $pdf->image_png("/srv/web/ipfire/html/accounting/logo/$grp/logo.png");
+ my $width= $image->width;
+ my $height= $image->height;
+ $gfx->image($image, (545+($width/2))-$width, 750,0.5);
+ }
+
+ #Set Fonts
+ $txt = $page->text;
+ $txt1 = $page->text;
+ $txt2 = $page->text;
+ $txt3 = $page->text;
+ $txt4 = $page->text;
+ $txt5 = $page->text;
+ $txt->textstart; #Begin Text
+ $txt->font($fnt, 10); #Set fontsize for font1
+ $txt1->font($fnt, 8); #Set fontsize for font2
+ $txt2->font($fnt1, 10); #Set fontsize for font3
+ $txt3->font($fnt1, 16); #Set fontsize for font4
+ $txt4->font($fnt, 6); #Set fontsize for font5
+ $txt5->font($fnt1, 6); #Set fontsize for font6
+
+ #if $cent not set, set it to 0.5
+ if(!$cent){$cent='0.005';}
+
+ #if MWst not set, set it to 19%
+ if(!$mwst){$mwst='19';}
+
+ # Titles
+ $txt1->translate(65,520);
+ $txt1->text($Lang::tr{'acct pos'}); #Pos
+ $txt1->translate(95, 520);
+ $txt1->text($Lang::tr{'acct name'}); #Host/Name
+ $txt1->translate(285, 520);
+ $txt1->text($Lang::tr{'acct amount'}); #Traffic
+ $txt1->translate(390, 520);
+ $txt1->text($Lang::tr{'acct cent1'}); #Price /MB
+ $txt1->translate(435, 520);
+ $txt1->text($Lang::tr{'acct pdf price'}); #Sum
+
+ ####################################################################
+ #Fill Recipient address
+ my $rec_name= "$company_cust";
+ my $rec_name1="$name1_cust";
+ my $rec_str = "$str_cust $str_nr_cust";
+ my $rec_city = "$plz_cust $city_cust";
+ #INSERT RECIPIENT
+ my $o=675;
+ $txt2->translate(78, 685);
+ $txt2->text(decode('utf8',$rec_name));
+ if($rec_name1){
+ $txt1->translate(78, $o);
+ $txt1->text(decode('utf8',$rec_name1));
+ $o=$o-15;
+ }else{
+ $o=$o-15;
+ }
+ $txt1->translate(78, $o);
+ $txt1->text(decode('utf8',$rec_str));
+ $o=$o-10;
+ $txt1->translate(78, $o);
+ $txt1->text(decode('utf8',$rec_city));
+
+ # INSERT SENDER
+ my $send_name= "$company_host";
+ my $send_str = "$str_host $str_nr_host";
+ my $send_city = "$plz_host $city_host";
+ my $send_bank ="$bank";
+
+ $txt5->translate(420, 725);
+ $txt5->text(decode('utf8',$Lang::tr{'acct pdf prov'}));
+ $txt5->translate(420, 715);
+ $txt5->text(decode('utf8',$send_name));
+ my $j=705;
+ if($name1_host){
+ $txt4->translate(420, $j);
+ $txt4->text(decode('utf8',$name1_host));
+ $j=$j-8;
+ }
+ $txt4->translate(420, $j);
+ $txt4->text(decode('utf8',$send_str)); #STR
+ $j=$j-8;
+ $txt4->translate(420, $j);
+ $txt4->text(decode('utf8',$send_city)); #PLZ.City
+ #Print optional Values tel,fax
+ my $i=680;
+ if($tel_host){
+ $txt4->translate(420, $i);
+ $txt4->text($Lang::tr{'acct tel'}); #Tel
+ $txt4->translate(480, $i);
+ $txt4->text($tel_host); #Telnr
+ $i=$i-8;
+ }
+ if($fax_host){
+ $txt4->translate(420, $i);
+ $txt4->text($Lang::tr{'acct fax'}); #Fax
+ $txt4->translate(480, $i);
+ $txt4->text($fax_host); #Faxnr
+ $i=$i-8;
+ }
+ if($internet){
+ $txt4->translate(420, $i);
+ $txt4->text($Lang::tr{'acct inet'}); #Internet
+ $txt4->translate(480, $i);
+ $txt4->text($internet); #www-address
+ $i=$i-8;
+ }
+ $txt5->translate(420, 650);
+ $txt5->text(decode('utf8',$Lang::tr{'acct bank'})); #"BANK"
+ $txt4->lead(7);
+ $txt4->translate(420, 640);
+ $txt4->paragraph(decode('utf8',$bank), 130, 20, -align => "justify"); #Bankname
+ if($iban){
+ $txt4->translate(420, 625);
+ $txt4->text($Lang::tr{'acct iban'}); #iban
+ $txt4->translate(480, 625);
+ $txt4->text(decode('utf8',$iban)); #iban
+ $txt4->translate(420, 619);
+ $txt4->text($Lang::tr{'acct bic'}); #bic
+ $txt4->translate(480, 619);
+ $txt4->text(decode('utf8',$bic)); #bic
+ }
+ if($blz){
+ $txt4->translate(420, 613);
+ $txt4->text($Lang::tr{'acct blz'}); #blz
+ $txt4->translate(420, 607);
+ $txt4->text($Lang::tr{'acct kto'}); #kto
+ $txt4->translate(480, 613);
+ $txt4->text(decode('utf8',$blz)); #blz
+ $txt4->translate(480, 607);
+ $txt4->text(decode('utf8',$kto)); #kto
+ }
+
+ #Print USTID and optional HRB
+ $txt4->translate(420, 590);
+ $txt4->text($Lang::tr{'acct ustid'}); #USTID
+ $txt4->translate(480, 590);
+ $txt4->text($stnr); #ustid
+ if($hrb){
+ $txt4->translate(420, 580);
+ $txt4->text($Lang::tr{'acct hrb'}); #USTID
+ $txt4->translate(480, 580);
+ $txt4->text($hrb); #ustid
+ }
+ ################################################################
+
+ #Print Date, Pages ....
+ $txt3->translate(59, 545);
+ $txt3->text($Lang::tr{'acct pdf billtxt'});
+ $txt1->translate(160, 545);
+ $txt1->text("$no $Lang::tr{'acct billnr'}");
+ $txt1->translate(60, 532);
+ $txt1->text("$Lang::tr{'acct pdf time'} $month/$year");
+ $txt1->translate(545, 550);
+ $txt1->text_right("$Lang::tr{'acct pdf date'} $fulldate");
+ $txt1->translate(545, 532);
+ $txt1->text_right("$Lang::tr{'acct pdf page'} $aktpage / $anz");
+
+ if ($a1 eq '999'){last;}
+ #Print DATA from array to Position table
+ $txt1->translate(80, $x);
+ $txt1->text_right($y);
+ $txt1->translate(95, $x);
+ $txt1->text($a1);
+ $txt1->translate(380, $x);
+ $txt1->text_right("$a2 MB");
+ $txt1->translate(425, $x);
+ $txt1->text_right("$cent ".decode('utf8',$cur));
+ $txt1->translate(540, $x);
+ $txt1->text_right("$sum ".decode('utf8',$cur));
+
+ #Build SUMMARY
+ $summen[$y-1]="$y,$a2,$sum";
+ $zwsum=$zwsum+$sum;
+ $x=$x-15;
+ $y++;
+ }
+ #Print extra billpositions
+ foreach my $line (@billpos){
+ my ($grp,$amount,$art,$price)=@$line;
+ #Print DATA from array to Position table
+ $txt1->translate(80, $x);
+ $txt1->text_right($y);
+ $txt1->translate(95, $x);
+ $txt1->text(decode('utf8',$art));
+ $txt1->translate(380, $x);
+ $txt1->text_right($amount." pcs");
+ $txt1->translate(540, $x);
+ $txt1->text_right("$price ".decode('utf8',$cur));
+ #Build SUMMARY
+ my $zu=$amount * $price;
+ $summen[$y-1]="$y,'0',$zu";
+ $zwsum=$zwsum+$zu;
+ $x=$x-15;
+ $y++;
+ }
+ foreach (@summen){
+ my ($a1,$a2,$a3) = split( /\,/, $_ );
+ $sum=$sum+$a2;
+ $sum1=$sum1+$a3;
+ }
+
+ # Last Line in positiontable prints the sum of all traffic (therefor txt2 which is BOLD)
+ $txt2->translate(95, 205);
+ $txt2->text($Lang::tr{'acct pdf sum1'}); #SUM
+ $txt2->translate(427, 205);
+ $txt2->text_right($cent); #cent
+ $txt2->translate(380, 205);
+ $txt2->text_right("$sum MB"); #MB
+ $sum1=sprintf("%.2f",($sum1));
+ $txt2->translate(540, 205);
+ $txt2->text_right("$sum1 ".decode('utf8',$cur)); #SUM Eur
+ $txt->translate(390, 150);
+ $txt->text($Lang::tr{'acct pdf sum1'});
+ $txt->translate(540, 150);
+ $txt->text_right("$sum1 ".decode('utf8',$cur));
+ $txt->translate(390, 135);
+ my $endsum=$sum1;
+ $txt->text("$Lang::tr{'acct mwst_name'} $mwst%");
+ my $sum1=sprintf("%.2f",($sum1/100*$mwst));
+ $txt->translate(540, 135);
+ $txt->text_right("$sum1 ".decode('utf8',$cur));
+ $txt2->translate(390, 120);
+ $txt2->text($Lang::tr{'acct sum total'});
+ my $endsum=sprintf("%.2f",($sum1+$endsum));
+ $txt2->translate(540, 120);
+ $txt2->text_right("$endsum ".decode('utf8',$cur));
+
+ #Print the optional Billtext if any
+ $txt4->translate(60, 170);
+ $txt4->paragraph(decode('utf8',$text), 300, 40, -align => "justify"); #Bankname
+
+ #Watermark if preview
+ if ($preview eq 'on'){
+ my $eg_trans = $pdf->egstate();
+ $eg_trans->transparency(0.9);
+ $txt5->egstate($eg_trans);
+ $txt5->textlabel(80, 400, $fnt, 60, "PDF preview", -rotate => 40);
+ $txt5->textlabel(150, 330, $fnt, 60, "IPFire accounting", -rotate => 40);
+ }
+
+ $txt->textend; #END Text
+ $pdf->save; #Save pdf
+ $pdf->end( ); #END
+ if ($preview ne 'on'){
+ &fillBill($path.$grp,$name,$no,$grp);
+ }
+ return '0';
+}
+
+sub getBillNr {
+ $dbh=&connectdb;
+ my $year1=$year+1900;
+ my $no1;
+ my $res=$dbh->selectall_arrayref("SELECT MAX(NO) FROM BILLS;");
+ foreach my $row (@$res){
+ ($no1) = @$row;
+ }
+ if(!$no1){$no1=$year1."1000";}
+ $no1++;
+ return $no1;
+}
+
+sub fillBill {
+ my $path=$_[0];
+ my $name=$_[1];
+ my $no=$_[2];
+ my $grp=$_[3];
+ my $sth = $dbh->prepare("INSERT INTO BILLS (NO,GRP,PATH,NAME,DATE) VALUES (?,?,?,?,?);");
+ my $year1=$year+1900;
+ ++$mon;
+ $sth->execute($no,$grp,$path,$name,"$mday.$mon.$year1");
+ $sth->finish();
+ $dbh->disconnect();
+}
+
+sub getbills {
+ my $grp=shift;
+ $dbh=&connectdb;
+ my $res=$dbh->selectall_arrayref("SELECT * FROM BILLS WHERE GRP=?;",undef, $grp);
+ $dbh->disconnect();
+ return $res;
+}
+
+sub pngsize {
+ my $Buffer = shift;
+ my ($width,$height) = ( undef, undef );
+
+ if ($Buffer =~ /IHDR(.{8})/) {
+ my $PNG = $1;
+ ($width,$height) = unpack( "NN", $PNG );
+ } else {
+ $width=$Lang::tr{'acct invalid png'};
+ };
+ return ($width,$height);
+}
+
+sub gifsize {
+ my ($GIF)=@_;
+ my ($type,$a,$b,$c,$d,$s,$width,$height) ;
+ $type=substr($GIF,0,6);
+ if(!($type =~ m/GIF8[7,9]a/) || (length($s=substr($GIF, 6, 4))!=4) ){
+ return;
+ }
+ ($a,$b,$c,$d)=unpack("C"x4,$s);
+
+ $width= $b<<8|$a;
+ $height= $d<<8|$c;
+ return ($width,$height);
+}
+
+sub jpegsize {
+ my ($JPEG)=@ _ ;
+ my ($count)=2 ;
+ my ($length)=length($JPEG) ;
+ my ($ch)="" ;
+ my ($c1,$c2,$a,$b,$c,$d,$width,$height) ;
+
+ while (($ch ne "\xda") && ($count<$length)) {
+ while (($ch ne "\xff") && ($count < $length)) {
+ $ch=substr($JPEG,$count,1);
+ $count++;
+ }
+
+ while (($ch eq "\xff") && ($count<$length)) {
+ $ch=substr($JPEG,$count,1);
+ $count++;
+ }
+
+ if ((ord($ch) >= 0xC0) && (ord($ch) <= 0xC3)) {
+ $count+=3;
+ ($a,$b,$c,$d)=unpack("C"x4,substr($JPEG,$count,4));
+ $width=$c<<8|$d;
+ $height=$a<<8|$b;
+ return($width,$height);
+ }else {
+ ($c1,$c2)= unpack("C"x2,substr($JPEG,$count,2));
+ $count += $c1<<8|$c2;
+ }
+ }
+}
+
+sub time{
+ ($sec,$min,$hour,$mday,$mon,$year,$wday,$ydat,$isdst)=localtime();
+ $hour=sprintf("%02d",$hour);
+ $min=sprintf("%02d",$min);
+ $sec=sprintf("%02d",$sec);
+ $year +=1900;
+ $mday=sprintf("%02d",$mday);
+ $mon=sprintf("%02d",$mon+1);
+ my $res="$mday.$mon.$year $hour:$min:$sec - ";
+ return $res;
+}
+
+sub logger{
+ my $settings=shift;
+ my $msg=shift;
+ #open LOGFILE
+ if ($settings eq 'on'){
+ open ACCTLOG,">>/var/log/accounting.log" || print "could not open /var/log/accounting.log ";
+ print ACCTLOG &time."$msg";
+ close (ACCTLOG);
+ }
+}
+
+sub updateccaddr {
+ my $addr=shift;
+ my $cust=shift;
+ $dbh=&connectdb;
+ $dbh->do("UPDATE ACCT_ADDR SET CCMAIL=? WHERE COMPANY=? ;",undef, $addr, $cust);
+ $dbh->disconnect();
+}
+return 1;
--- /dev/null
+# Added for Squid accounting
+%tr = (
+%tr,
+'acct action' => 'Aktion',
+'acct activate' => 'Aktivieren',
+'acct addresses' => 'Adressverwaltung',
+'acct bank' => 'Bank',
+'acct bic' => 'BIC',
+'acct billarchive' => 'Rechnungsarchiv',
+'acct billgroup' => 'Rechnungsgruppen',
+'acct billgroupexists' => 'Eine Abrechnungsgruppe mit diesem Namen existiert bereits',
+'acct billtext1' => 'Rechnungstext',
+'acct billnr' => '(Rechnungsnummer immer angeben)',
+'acct billoverview' => 'Rechnungsübersicht',
+'acct blz' => 'BLZ',
+'acct ccmail' => 'E-Mail Kopie an',
+'acct currency' => 'Währung (EUR,USD)',
+'acct traffic' => 'Traffic',
+'acct amount' => 'Anzahl',
+'acct cent' => 'Preis je MB (z.B 0.003)',
+'acct cent1' => 'Preis MB',
+'acct city' => 'Ort',
+'acct config' => 'Konfiguration',
+'acct commit' => 'Ausführen',
+'acct company' => 'Firma',
+'acct companyexists' => 'Eine Firma mit diesem Namen existiert bereits',
+'acct companytype' => 'Typ',
+'acct customer' => 'Kunde',
+'acct cust empty' => 'Noch keine Kunden-Adressen angelegt',
+'acct dbsize' => 'Datenbankgröße',
+'acct dbmaintenance' => 'Datenbank-Wartung',
+'acct delbefore' => 'Alle Einträge löschen vor',
+'acct deladr' => 'Löschen',
+'acct edit' => 'Bearbeiten',
+'acct edit_addr' => 'Anlegen/Bearbeiten',
+'acct email' => 'E-Mail',
+'acct emptydb' => 'Datenbank leeren (alles)',
+'acct emptydbtraf' => 'Datenbank leeren (nur Trafficdaten)',
+'acct empty field' => 'Feld darf nicht leer sein: ',
+'acct entries' => 'Einträge',
+'acct err custdel' => 'Die Adresse kann nicht gelöscht werden, weil sie als Kunde in einer Rechnungsgruppe verwendet wird - Rechnungsgruppe',
+'acct err hostdel' => 'Die Adresse kann nicht gelöscht werden, weil sie als Hoster in einer Rechnungsgruppe verwendet wird - Rechnungsgruppe',
+'acct exst_cust_addr' => 'Kunden-Adressen',
+'acct exst_host_addr' => 'Hoster-Adressen',
+'acct expert' => 'Wartungsbereich aktivieren',
+'acct expdelbefore' => 'Löschen',
+'acct fax' => 'Fax',
+'acct fix billpos' => 'Feste Rechnungspositionen der Rechnungsgruppe',
+'acct gb' => 'GB',
+'acct generated' => 'Erzeugt',
+'acct hint billgrp' => 'Bitte zuerst mindestens eine Hoster-Adresse und eine Kunden-Adresse anlegen',
+'acct hint_hoster' => 'Muss nur ausgefüllt werden, wenn Typ "Hoster"',
+'acct hrb' => 'HRB-Nr',
+'acct hoster' => 'Hoster',
+'acct hosts' => 'Hostübersicht',
+'acct host empty' => 'Noch keine Hoster-Adressen angelegt',
+'acct host detail' => 'Host-Details',
+'acct iban' => 'IBAN',
+'acct inet' => 'Internet',
+'acct interval' => 'Checkintervall',
+'acct invalid' => 'Ungültiger Wert für',
+'acct invalid billpos' => 'Es müssen alle Felder für Extra-Rechnungspositionen ausgefüllt sein',
+'acct invalid mailip' => 'Ungültige IP-Adresse für Mailserver',
+'acct invalid mailfqdn' => 'Ungültiger FQDN für Mailserver',
+'acct invalid mailport' => 'Ungültiger Port für Mailserver',
+'acct invalid png' => 'Ungültige PNG Datei',
+'acct invalid pngsize' => 'Ungültige PNG Größe',
+'acct kb' => 'KB',
+'acct kto' => 'KTO',
+'acct latestdb' => 'Letzter DB-Eintrag',
+'acct logging' => 'Logging (/var/log/accounting.log) aktivieren',
+'acct logo' => 'Aktuelles Logo',
+'acct logo upload' => 'Logo Hochladen (PNG, max. 400x150)',
+'acct maintenance' => 'Wartung',
+'acct mailaddr' => 'Mailserver-Adresse',
+'acct mailport' => 'Mailserver-Port',
+'acct mailuser' => 'Benutzername',
+'acct mailpass' => 'Passwort',
+'acct mailrcpt' => 'E-Mail-Empfänger',
+'acct mailsender' => 'E-Mail Absender',
+'acct mailtxt' => 'Text der Rechnungsmails',
+'acct mb' => 'MB',
+'acct members' => 'Mitglieder',
+'acct menu' => 'Proxy-Abrechnung',
+'acct month' => 'Monat',
+'acct multiuser' => 'User dürfen in mehreren Gruppen sein',
+'acct mwst_name' => 'USt.',
+'acct mwst' => 'Mehrwertsteuer in %',
+'acct name1' => 'Name 1',
+'acct name2' => 'Name 2',
+'acct nr' => 'RG-Nr.',
+'acct oldestdb' => 'Erster DB-Eintrag',
+'acct optional' => 'Diese Felder sind optional',
+'acct parameter' => 'Parameter',
+'acct path' => 'Pfad',
+'acct pdf billtxt' => 'Rechnung',
+'acct pdf date' => 'Datum',
+'acct pdf zwsum' => 'Zwischensumme',
+'acct pdf page' => 'Seite',
+'acct pdf prov' => 'Absender',
+'acct pdf sum1' => 'Summe',
+'acct pdf price' => 'Preis',
+'acct pdf time' => 'Abrechnung',
+'acct plz' => 'PLZ',
+'acct preview' => 'Rechnungsvorschau',
+'acct price pp' => 'Einzelpreis',
+'acct proxy_enable' => 'Bitte zuerst Proxy aktivieren',
+'acct pos' => 'Pos.',
+'acct sum total' => 'Gesamtsumme',
+'acct name' => 'Bezeichnung',
+'acct no data' => 'Keine Daten vorhanden',
+'acct rrdsize' => 'Größe des Rechnungs-Verzeichnisses',
+'acct settings' => 'Accounting-Einstellungen',
+'acct skipurl' => 'Diese URLs nicht bewerten',
+'acct status' => 'Status',
+'acct str' => 'Straße',
+'acct str_nr' => 'Nr.',
+'acct subject' => 'Betreff der Rechnungsmails',
+'acct sum' => 'Summe',
+'acct task' => 'Aufgabe',
+'acct tb' => 'TB',
+'acct tel' => 'Telefon',
+'acct tls' => 'Benutze TLS',
+'acct traffic monthly' => 'Monatliches Datenvolumen',
+'acct title' => 'Webproxy-Abrechnung',
+'acct usemail' => 'Mailversand aktivieren',
+'acct usermulti' => 'befindet sich bereits in Gruppe',
+'acct ustid' => 'USt-ID',
+'acct edit settings' => 'Einstellungen bearbeiten',
+'acct value' => 'Wert',
+'acct view' => 'Anzeigen',
+'acct year' => 'Jahr'
+);
+# End Squid accounting
--- /dev/null
+# Added for Squid accounting
+%tr = (
+%tr,
+'acct action' => 'Action',
+'acct activate' => 'Activate',
+'acct addresses' => 'Address Management',
+'acct bank' => 'Bank',
+'acct bic' => 'BIC',
+'acct billarchive' => 'Bill Archive',
+'acct billgroup' => 'Billing Groups',
+'acct billgroupexists' => 'A billing group with this name already exists',
+'acct billtext1' => 'Bill text',
+'acct billnr' => '(always state bill number)',
+'acct billoverview' => 'Bill overview',
+'acct blz' => 'Bank code',
+'acct ccmail' => 'E-Mail CC-Addresses',
+'acct currency' => 'Currency (EUR,USD)',
+'acct traffic' => 'Traffic',
+'acct amount' => 'Amount',
+'acct cent' => 'Price/MB (e.g. 0.003)',
+'acct cent1' => 'Price MB',
+'acct city' => 'City',
+'acct config' => 'Configuration',
+'acct commit' => 'Execute',
+'acct company' => 'Company',
+'acct companyexists' => 'A Company with this name already exists',
+'acct companytype' => 'Type',
+'acct customer' => 'Customer',
+'acct cust empty' => 'No Customer addresses defined',
+'acct dbsize' => 'Database size',
+'acct dbmaintenance' => 'Database maintenance',
+'acct delbefore' => 'Delete all entries before',
+'acct deladr' => 'Delete',
+'acct edit' => 'Edit',
+'acct edit_addr' => 'Add / Edit',
+'acct email' => 'E-Mail',
+'acct emptydb' => 'Erase Database (all data)',
+'acct emptydbtraf' => 'Erase Database (only traffic data)',
+'acct empty field' => 'Field must not be empty: ',
+'acct entries' => 'Entries',
+'acct err custdel' => 'Address can not be deleted. It is defined as customer in a Bill group - Bill group',
+'acct err hostdel' => 'Address can not be deleted. It is defined as provider in a Bill group - Bill group',
+'acct exst_cust_addr' => 'Customer addresses',
+'acct exst_host_addr' => 'Provider addresses',
+'acct expert' => 'Activate maintenance area',
+'acct expdelbefore' => 'Delete',
+'acct fax' => 'Faximile',
+'acct fix billpos' => 'Fixed bill positions of bill group',
+'acct gb' => 'GB',
+'acct generated' => 'Generated',
+'acct hint billgrp' => 'You have to define a provider and a customer address first',
+'acct hint_hoster' => 'Has only to be filled when type "provider"',
+'acct hrb' => 'HRB-No',
+'acct hoster' => 'Provider',
+'acct hosts' => 'Host Overview',
+'acct host empty' => 'No provider addresses defined',
+'acct host detail' => 'Host Details',
+'acct iban' => 'IBAN',
+'acct inet' => 'Internet',
+'acct interval' => 'Checkintervall',
+'acct invalid' => 'Invalid value for',
+'acct invalid billpos' => 'You have to fill in all fields for fixed bill positions',
+'acct invalid mailip' => 'Invalid IP address for mailserver',
+'acct invalid mailfqdn' => 'Invalid FQDN for mailserver',
+'acct invalid mailport' => 'Invalid port for mailserver',
+'acct invalid png' => 'Invalid PNG file',
+'acct invalid pngsize' => 'Invalid PNG size',
+'acct kb' => 'KB',
+'acct kto' => 'Account Number',
+'acct latestdb' => 'Last DB entry',
+'acct logging' => 'Activate Logging (/var/log/accounting.log)',
+'acct logo' => 'Actual Logo',
+'acct logo upload' => 'Upload Logo (PNG, max. 400x150)',
+'acct maintenance' => 'Maintenance',
+'acct mailaddr' => 'Mailserver address',
+'acct mailport' => 'Mailserver port',
+'acct mailuser' => 'Username',
+'acct mailpass' => 'Password',
+'acct mailrcpt' => 'E-Mail Recipient',
+'acct mailsender' => 'E-Mail Sender',
+'acct mailtxt' => 'Text for bill mail',
+'acct mb' => 'MB',
+'acct members' => 'Members',
+'acct menu' => 'Proxy Accounting',
+'acct month' => 'Month',
+'acct multiuser' => 'User can be in multiple groups',
+'acct mwst_name' => 'VAT',
+'acct mwst' => 'Tax rate',
+'acct name1' => 'Name 1',
+'acct name2' => 'Name 2',
+'acct nr' => 'RG-No.',
+'acct oldestdb' => 'Fist DB Entry',
+'acct optional' => 'These fields are optional',
+'acct parameter' => 'Parameter',
+'acct path' => 'Path',
+'acct pdf billtxt' => 'Bill',
+'acct pdf date' => 'Date',
+'acct pdf zwsum' => 'subtotal',
+'acct pdf page' => 'Page',
+'acct pdf prov' => 'Sender',
+'acct pdf sum1' => 'Sum',
+'acct pdf price' => 'Price',
+'acct pdf time' => 'Accounting',
+'acct plz' => 'Postcode',
+'acct preview' => 'Bill preview',
+'acct price pp' => 'Unit price',
+'acct proxy_enable' => 'Please actiavte proxy first',
+'acct pos' => 'Pos.',
+'acct sum total' => 'Total',
+'acct name' => 'Description',
+'acct no data' => 'No Data available',
+'acct rrdsize' => 'Size of Bill directory',
+'acct settings' => 'Accounting settings',
+'acct skipurl' => 'Skip these URLs (Intranet)',
+'acct status' => 'Status',
+'acct str' => 'Steet',
+'acct str_nr' => 'No.',
+'acct subject' => 'Subject',
+'acct sum' => 'Sum',
+'acct task' => 'Task',
+'acct tb' => 'TB',
+'acct tel' => 'Telephone',
+'acct tls' => 'Use TLS',
+'acct traffic monthly' => 'Monthly data volume',
+'acct title' => 'Webproxy Accounting',
+'acct usemail' => 'Send bills via Mail',
+'acct usermulti' => 'is already in group',
+'acct ustid' => 'USt-ID',
+'acct edit settings' => 'Edit settings',
+'acct value' => 'Value',
+'acct view' => 'Show',
+'acct year' => 'Year'
+);
+# End Squid accounting
--- /dev/null
+#!/usr/bin/perl
+###############################################################################
+# #
+# IPFire.org - A linux based firewall #
+# Copyright (C) 2014 IPFire Team <alexander.marx@ipfire.org> #
+# #
+# This program is free software: you can redistribute it and/or modify #
+# it under the terms of the GNU General Public License as published by #
+# the Free Software Foundation, either version 3 of the License, or #
+# (at your option) any later version. #
+# #
+# This program is distributed in the hope that it will be useful, #
+# but WITHOUT ANY WARRANTY; without even the implied warranty of #
+# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the #
+# GNU General Public License for more details. #
+# #
+# You should have received a copy of the GNU General Public License #
+# along with this program. If not, see <http://www.gnu.org/licenses/>. #
+# #
+###############################################################################
+
+
+###########
+# Modules #
+###########
+
+use Time::Local;
+use File::ReadBackwards;
+use strict;
+#use warnings;
+
+require '/var/ipfire/general-functions.pl';
+require "${General::swroot}/accounting/acct-lib.pl";
+require "${General::swroot}/lang.pl";
+
+#############
+# Variables #
+#############
+
+my $count = 0;
+my $dbh;
+my $logfile = "/var/log/squid/access.log";
+my $line = '';
+my $checktime = 3600; #1 hour = 3600 sec
+my $starttime = time;
+my ($time,$elapsed,$ip,$state,$bytes,$method,$url,$user,$peerstate,$type); #split logfileline into variables
+my $name;
+my $name1;
+my $settingsfile = "${General::swroot}/accounting/settings.conf";
+my $proxyenabled = "${General::swroot}/proxy/enable";
+my %counter = ();
+my %counterip = ();
+my %settings = ();
+my %toplist = ();
+my ($sec,$min,$hour,$mday,$mon,$year,$wday,$ydat,$isdst)=localtime();
+my $skipurlcount=0;
+my $skipurlsum=0;
+&General::readhash("$settingsfile", \%settings);
+my $skipurl=$settings{'SKIPURLS'};
+$skipurl="'".$skipurl."'";
+my ($mini,$max)=&ACCT::getminmax;
+my $now = localtime;
+my $proxylog;
+my $proxysrv;
+
+########
+# Main #
+########
+
+&checkproxy;
+
+#If we have a disabled file and the proxy is off, we don't need to check anything, exit!
+if((! -f $proxyenabled || $proxylog eq $Lang::tr{'stopped'}) && -f "${General::swroot}/accounting/disabled"){
+ &ACCT::logger($settings{'LOG'}," Proxy or proxylogging disabled - exiting with no data collection\n");
+ exit 0;
+}
+#If proxy was turned off within last hour, we need to check missing minutes and write a disabled file
+if ((! -f $proxyenabled || $proxylog eq $Lang::tr{'stopped'}) && ! -f "${General::swroot}/accounting/disabled"){
+ $checktime = (time-$max);
+ open (FH,">${General::swroot}/accounting/disabled");
+ close (FH);
+ &ACCT::logger($settings{'LOG'}," Proxy or proxylogging was disabled during last hour - just checking meantime and disabling data collection\n");
+}
+
+#If proxy is on, we are doing a normal run. maybe we had a disabled file, so delete it here
+if (-f $proxyenabled && $proxylog eq $Lang::tr{'running'}){
+ #check if we are running again after the was shutdown and reenabled
+ if (-f "${General::swroot}/accounting/disabled"){
+ unlink("${General::swroot}/accounting/disabled");
+ }
+ #Find out if the month changed
+ $dbh=&ACCT::connectdb;
+ my $m=sprintf("%d",(localtime((time-3600)))[4]+1);
+ &ACCT::logger($settings{'LOG'},"month before one hour $m, now is ".($mon+1)."\n");
+ if ($m < ($mon+1) || $m == '12' && ($mon+1) == '1'){
+ #Logrotate
+ my $year1=$year+1900;
+ system ("tar", "cfz", "/var/log/accounting-$m-$year1.tar.gz", "/var/log/accounting.log");
+ unlink ("/var/log/accounting.log");
+ open (FH,">/var/log/accounting.log");
+ close (FH);
+ chmod 0755, "/var/log/accounting.log";
+ #move all db entries older than 2 months to second table and cumulate them hourly
+ &ACCT::movedbdata;
+ &ACCT::logger($settings{'LOG'},"New Month. Old trafficvalues moved to ACCT_HIST Table\n");
+ if ($settings{'USEMAIL'} eq 'on'){
+ &ACCT::logger($settings{'LOG'},"Mailserver is activated - Now sending bills via mail...\n");
+ my $res=&ACCT::getbillgroups;
+ foreach my $line (@$res){
+ my ($grp) = @$line;
+ &sendbill($grp,$settings{'MWST'},$settings{'CURRENCY'});
+ }
+ }else{
+ &ACCT::logger($settings{'LOG'},"Mailserver is deactivated - We are NOT sending bills via mail...\n");
+ }
+ }
+
+ &ACCT::logger($settings{'LOG'},"Start reading last hour of access.log\n");
+ &readlog;
+ &fill_db;
+ &ACCT::closedb;
+ $skipurlsum=sprintf("%.2f",$skipurlsum/(1024*1024));
+ &ACCT::logger($settings{'LOG'},"skipped: $skipurlcount Adressen\n");
+ &ACCT::logger($settings{'LOG'},"skipped: $skipurlsum MB\n") if ($skipurl);
+}
+#############
+# functions #
+#############
+
+sub checkproxy{
+ if(-f "${General::swroot}/proxy/enable"){
+ $proxysrv=$Lang::tr{'running'};
+ }else{
+ $proxysrv=$Lang::tr{'stopped'};
+ }
+ my $srce = "${General::swroot}/proxy/squid.conf";
+ my $string1 = 'access\.log';
+ open(FH, $srce);
+ while(my $line = <FH>) {
+ if($line =~ m/$string1/) {
+ $proxylog=$Lang::tr{'running'};
+ }
+ }
+ close FH;
+ return;
+}
+
+sub readlog{
+ my $url1;
+ my $user1;
+ $count = 0;
+ my $urlcnt=0;
+ &ACCT::logger($settings{'LOG'},"Start: $now. Reading data back till: ".localtime(($starttime-$checktime)).".\n");
+ #Open Logfile and begin to read the file backwards
+ my $bw = File::ReadBackwards->new( $logfile ) or die "can't read $logfile $!" ;
+ while( defined( $line = $bw->readline ) ) {
+ undef $url1;
+ chomp $line;
+ #Divide $line into single variables to get timestamp and check if we are within hte desired timerange
+ ($time,$elapsed,$ip,$state,$bytes,$method,$url,$user,$peerstate,$type)=split(m/\s+/, $line);
+ $count += $bytes;
+ $time = substr($time, 0, -4);
+ if (($time > ($starttime-$checktime))){
+ #Skip DENIED stated lines (can be reactivated later)
+ next if ($state =~ m/DENIED/);
+
+ #extract site name
+ if ($url =~ m/([a-z]+:\/\/)??([a-z0-9\-]+\.){1}(([a-z0-9\-]+\.){0,})([a-z0-9\-]+){1}(:[0-9]+)?\/(.*)/o) {
+ $url=$2.$3.$5;
+ } else {
+ my ($a,$b)=split(":",$url);
+ $url=$a;
+ }
+
+ #Skip special URLs like intranet and webservers from local network
+ if ($url =~ m/$skipurl/o) {
+ $skipurlcount++;
+ $skipurlsum+=$bytes;
+ next;
+ };
+
+ #Increase urlcounter
+ $urlcnt++;
+
+ #Get Data for accounting
+ $counter{$user}{'bytes'} += $bytes if ($user ne '-');
+ $counter{$ip}{'bytes'} += $bytes;
+ }else{
+ #If we are out of timewindow, break
+ last;
+ }
+ }
+ $count=sprintf("%.2f",$count/(1024*1024));
+ &ACCT::logger($settings{'LOG'},"got $count MB from $urlcnt URLs this run.\n");
+ $bw->close;
+}
+sub fill_db{
+ my $tim=time();
+ #Fill ACCT table with accounting information
+ foreach my $name (sort keys %counter){
+ foreach my $bytes (keys %{ $counter{$name} }) {
+ $dbh->do("insert into ACCT (TIME_RUN,NAME,BYTES) values ('$tim','$name','$counter{$name}{$bytes}');");
+ }
+ }
+}
+sub sendbill {
+ my $rggrp=$_[0];
+ my $mwst=$_[1];
+ my $cur = $_[2];
+ my @now = localtime(time);
+ $now[5] = $now[5] + 1900;
+ my $actmonth = $now[4]+1;
+ my $month = '0'.$actmonth if $actmonth < 10;
+ my $actyear = $now[5];
+ my ($from,$till)=&ACCT::getmonth($actmonth,$actyear); #FIXME month and year as variables!
+ my @billar = &ACCT::GetTaValues($from,$till,$rggrp);
+ my $address_cust = &ACCT::getTaAddress($rggrp,'CUST');
+ my $address_host = &ACCT::getTaAddress($rggrp,'HOST');
+ my $billpos = &ACCT::getextrabillpos($rggrp);
+ my $no = &ACCT::getBillNr;
+ my $back = &ACCT::pdf2(\@billar,$actmonth,$actyear,$mwst,$address_cust,$address_host,$billpos,$rggrp,$cur);
+ my ($company_cust,$type_cust,$name1_cust,$str_cust,$str_nr_cust,$plz_cust,$city_cust,$bank,$iban,$bic,$blz,$kto,$email,$internet,$hrb,$stnr,$tel_host,$fax_host,$ccmail,$billgrp,$text,$host,$cust,$cent);
+
+ foreach my $addrline_cust (@$address_cust){
+ ($company_cust,$type_cust,$name1_cust,$str_cust,$str_nr_cust,$plz_cust,$city_cust,$bank,$iban,$bic,$blz,$kto,$email,$internet,$hrb,$stnr,$tel_host,$fax_host,$ccmail,$billgrp,$text,$host,$cust,$cent)=@$addrline_cust;
+ }
+
+ if ($back eq '0'){
+ &ACCT::logger($settings{'LOG'},"Bill for $company_cust successfully created.\n");
+ my $file="'/var/ipfire/accounting/bill/$rggrp/$month-$actyear-$no.pdf'";
+ $settings{'MAILTXT'} =~ tr/\|/\r\n/ ;
+ my $cmd = "/usr/local/bin/sendEmail ";
+ $cmd .= " -f $settings{'MAILSENDER'}"; #Sender
+ $cmd .= " -t $email"; #Recipient
+ if ($ccmail){
+ $cmd .= " -cc $ccmail";
+ }
+ #Send Mail via TLS?
+ if ($settings{'TLS'} eq 'on'){
+ $cmd .= " -o tls=yes"; #TLS
+ }
+ $cmd .= " -u '$settings{'MAILSUB'}'"; #Subject
+ $cmd .= " -m '$settings{'MAILTXT'}'"; #Mailtext
+ $cmd .= " -s $settings{'MAILSRV'}:$settings{'MAILPORT'}"; #Mailserver:port
+ $cmd .= " -a $file";
+ my $res=system ($cmd);
+ if ($res == 0){
+ &ACCT::logger($settings{'LOG'},"Bill for $company_cust successfully sent.\n");
+ }elsif ($res > 0){
+ &ACCT::logger($settings{'LOG'},"ERROR: Bill for $company_cust NOT sent.\n");
+ }
+ return 0;
+
+ }else{
+ &ACCT::logger($settings{'LOG'},"ERROR Bill for $company_cust could not be created.\n");
+ my $cmd = "/usr/local/bin/sendEmail ";
+ $cmd .= " -f $settings{'MAILSENDER'}";
+ $cmd .= " -t $settings{'MAILSENDER'}";
+ $cmd .= " -u Fehler Squid Accounting";
+ $cmd .= " -m 'Die Rechnung konnte nicht erzeugt und per Mail versendet werden' $company_cust";
+ $cmd .= " -s $settings{'MAILSRV'}:$settings{'MAILPORT'}";
+ my $res=system ($cmd);
+ return 0;
+ }
+}
--- /dev/null
+/var/ipfire/accounting/settings.conf
+/var/ipfire/accounting/bill
+/srv/web/ipfire/html/accounting
+/var/ipfire/accounting/acct.db
+/var/log/accounting.log
--- /dev/null
+#!/usr/bin/perl
+###############################################################################
+# #
+# IPFire.org - A linux based firewall #
+# Copyright (C) 2014 IPFire Team <alexander.marx@ipfire.org> #
+# #
+# This program is free software: you can redistribute it and/or modify #
+# it under the terms of the GNU General Public License as published by #
+# the Free Software Foundation, either version 3 of the License, or #
+# (at your option) any later version. #
+# #
+# This program is distributed in the hope that it will be useful, #
+# but WITHOUT ANY WARRANTY; without even the implied warranty of #
+# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the #
+# GNU General Public License for more details. #
+# #
+# You should have received a copy of the GNU General Public License #
+# along with this program. If not, see <http://www.gnu.org/licenses/>. #
+# #
+###############################################################################
+
+
+use DBI;
+
+###############################################################################
+my $dbh;
+my $dsn="dbi:SQLite:dbname=/var/ipfire/accounting/acct.db";
+###############################################################################
+
+
+$dbh = DBI->connect($dsn, "", "",{RaiseError => 1, AutoCommit => 1})or die "ERROR $!";
+
+$dbh->do('CREATE TABLE ACCT (TIME_RUN "NUM",NAME "TXT",BYTES "NUM");');
+
+$dbh->do('CREATE TABLE ACCT_HIST (TIME_RUN "NUM",NAME "TXT",BYTES "NUM");');
+
+$dbh->do('CREATE TABLE ACCT_ADDR (COMPANY "TXT",TYPE "TXT",NAME1 "TXT", STR "TXT", NR "TXT", POSTCODE "NUM", CITY "TXT",BANK "TXT",IBAN "TXT",BIC "TXT",BLZ "NUM",ACCOUNT "NUM", EMAIL "TXT",INTERNET "TXT", HRB "TXT", USTID "TXT", TEL "TXT", FAX "TXT", CCMAIL "TXT");');
+
+$dbh->do('CREATE TABLE BILLINGGRP (NAME "TXT",BILLTEXT "TXT",HOST "TXT",CUST "TXT",CENT "NUM");');
+
+$dbh->do('CREATE TABLE BILLINGHOST (GRP "TXT",HOST "TXT");');
+
+$dbh->do('CREATE TABLE BILLPOS (GRP "TXT",AMOUNT "INT", POS "TXT", PRICE "NUM");');
+
+$dbh->do('CREATE TABLE BILLS (NO "NUM", PATH "TXT", NAME "TXT", DATE "NUM", GRP "TXT");');
+
+$dbh->disconnect();
+
+exit 0;
projects / ipfire-2.x.git / commitdiff
