From db8a01e0277fa3cfe82a37b49f9d7c29b749c795 Mon Sep 17 00:00:00 2001 From: Alexander Marx Date: Wed, 6 Aug 2014 08:58:43 +0200 Subject: [PATCH] Squid-accounting: new addon for measuring proxy traffic per user/ip --- config/rootfiles/packages/squid-accounting | 13 + lfs/squid-accounting | 72 + make.sh | 2 + src/paks/squid-accounting/install.sh | 34 + src/paks/squid-accounting/uninstall.sh | 26 + src/paks/squid-accounting/update.sh | 26 + src/squid-accounting/EX-squid-accounting.menu | 5 + src/squid-accounting/accounting.cgi | 2193 +++++++++++++++++ src/squid-accounting/acct-lib.pl | 919 +++++++ src/squid-accounting/acct.de.pl | 134 + src/squid-accounting/acct.en.pl | 134 + src/squid-accounting/acct.pl | 265 ++ .../config/backup/includes/squid-accounting | 5 + src/squid-accounting/dbinstall.pl | 49 + 14 files changed, 3877 insertions(+) create mode 100644 config/rootfiles/packages/squid-accounting create mode 100644 lfs/squid-accounting create mode 100644 src/paks/squid-accounting/install.sh create mode 100644 src/paks/squid-accounting/uninstall.sh create mode 100644 src/paks/squid-accounting/update.sh create mode 100755 src/squid-accounting/EX-squid-accounting.menu create mode 100755 src/squid-accounting/accounting.cgi create mode 100755 src/squid-accounting/acct-lib.pl create mode 100644 src/squid-accounting/acct.de.pl create mode 100644 src/squid-accounting/acct.en.pl create mode 100755 src/squid-accounting/acct.pl create mode 100644 src/squid-accounting/config/backup/includes/squid-accounting create mode 100755 src/squid-accounting/dbinstall.pl diff --git a/config/rootfiles/packages/squid-accounting b/config/rootfiles/packages/squid-accounting new file mode 100644 index 0000000000..73a75ef603 --- /dev/null +++ b/config/rootfiles/packages/squid-accounting @@ -0,0 +1,13 @@ +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 diff --git a/lfs/squid-accounting b/lfs/squid-accounting new file mode 100644 index 0000000000..af34d7c007 --- /dev/null +++ b/lfs/squid-accounting @@ -0,0 +1,72 @@ +############################################################################### +# IPFire.org - An Open Source Firewall Solution # +# Copyright (C) - IPFire Development Team # +############################################################################### + +############################################################################### +# 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) + diff --git a/make.sh b/make.sh index 65ca37d743..d405a05d00 100755 --- a/make.sh +++ b/make.sh @@ -810,6 +810,8 @@ buildipfire() { ipfiremake sslscan ipfiremake owncloud ipfiremake bacula + ipfiremake perl-PDF-API2 + ipfiremake squid-accounting } buildinstaller() { diff --git a/src/paks/squid-accounting/install.sh b/src/paks/squid-accounting/install.sh new file mode 100644 index 0000000000..ec37185831 --- /dev/null +++ b/src/paks/squid-accounting/install.sh @@ -0,0 +1,34 @@ +#!/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 . # +# # +############################################################################ +# +. /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 diff --git a/src/paks/squid-accounting/uninstall.sh b/src/paks/squid-accounting/uninstall.sh new file mode 100644 index 0000000000..826df18e7d --- /dev/null +++ b/src/paks/squid-accounting/uninstall.sh @@ -0,0 +1,26 @@ +#!/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 # +# # +############################################################################ + +. /opt/pakfire/lib/functions.sh +make_backup ${NAME} +remove_files diff --git a/src/paks/squid-accounting/update.sh b/src/paks/squid-accounting/update.sh new file mode 100644 index 0000000000..89c40d0d7c --- /dev/null +++ b/src/paks/squid-accounting/update.sh @@ -0,0 +1,26 @@ +#!/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 . # +# # +############################################################################ +# +. /opt/pakfire/lib/functions.sh +./uninstall.sh +./install.sh diff --git a/src/squid-accounting/EX-squid-accounting.menu b/src/squid-accounting/EX-squid-accounting.menu new file mode 100755 index 0000000000..8779398ad1 --- /dev/null +++ b/src/squid-accounting/EX-squid-accounting.menu @@ -0,0 +1,5 @@ + $sublogs->{'32.proxy'} = {'caption' => $Lang::tr{'acct menu'}, + 'uri' => '/cgi-bin/accounting.cgi', + 'title' => $Lang::tr{'acct menu'}, + 'enabled' => 1, + }; diff --git a/src/squid-accounting/accounting.cgi b/src/squid-accounting/accounting.cgi new file mode 100755 index 0000000000..a5a9376720 --- /dev/null +++ b/src/squid-accounting/accounting.cgi @@ -0,0 +1,2193 @@ +#!/usr/bin/perl +############################################################################### +# # +# IPFire.org - A linux based firewall # +# Copyright (C) 2014 IPFire Team # +# # +# 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 . # +# # +############################################################################### + + +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 = ; + 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, "; + 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
"; + } + } + } + } + #validate namefield + if(!&validtextfield($cgiparams{'txt_billgroup'})){ + $errormessage.="$Lang::tr{'acct invalid'} $Lang::tr{'name'}
"; + } + #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'}
"; + } + #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'}
"; + } + #check all fields + if (!$errormessage){ + if(! &validnumfield($cgiparams{'txt_amount'})){ + $errormessage.="$Lang::tr{'acct invalid'} $Lang::tr{'acct amount'}
"; + }elsif(! &validtextfield($cgiparams{'txt_name'})){ + $errormessage.="$Lang::tr{'acct invalid'} $Lang::tr{'acct name'}
"; + }elsif(! &validnumfield($cgiparams{'txt_price'})){ + $errormessage.="$Lang::tr{'acct invalid'} $Lang::tr{'acct price pp'}
"; + } + } + } + + 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= ; + close(PNG) ; + my ($width,$height)=&ACCT::pngsize($PNG1) ; + + if (! &validnumfield($width)){ + $errormessage.="$Lang::tr{'acct invalid png'}
"; + unlink("$logopath/$cgiparams{'logo_grp'}/logo.png"); + }elsif($width > 400 || $height > 150){ + $errormessage.="$Lang::tr{'acct invalid pngsize'} width: $width height: $height
"; + 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
"; + } + } + } + } + #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'}
"; + } + + #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'}
"; + } + #Check all fields + if (!$errormessage){ + if(! &validnumfield($cgiparams{'txt_amount'})){ + $errormessage.="$Lang::tr{'acct invalid'} $Lang::tr{'acct amount'}
"; + }elsif(! &validtextfield($cgiparams{'txt_name'})){ + $errormessage.="$Lang::tr{'acct invalid'} $Lang::tr{'acct name'}
"; + }elsif(! &validnumfield($cgiparams{'txt_price'})){ + $errormessage.="$Lang::tr{'acct invalid'} $Lang::tr{'acct price pp'}
"; + } + } + } + #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
"; + } + } + } + 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'}
"; + } + #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'}
"; + }elsif(! &validtextfield($cgiparams{'txt_name'})){ + $errormessage.="$Lang::tr{'acct invalid'} $Lang::tr{'acct name'}
"; + }elsif(! &validnumfield($cgiparams{'txt_price'})){ + $errormessage.="$Lang::tr{'acct invalid'} $Lang::tr{'acct price pp'}
"; + } + } + } + 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< + \$(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(); + + }); + }); + +END +####################### + $settings{'SKIPURLS'} =~ tr/|/\r\n/; + $settings{'MAILTXT'} =~ tr/|/\r\n/; + my $col="style='background-color:$color{'color22'}'"; + print< + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + +
$Lang::tr{'acct logging'}
$Lang::tr{'acct expert'}
$Lang::tr{'acct multiuser'}
$Lang::tr{'acct mwst'}
$Lang::tr{'acct currency'}
$Lang::tr{'acct skipurl'}
$Lang::tr{'acct usemail'}

+ +
+ + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + +
$Lang::tr{'acct mailaddr'}
$Lang::tr{'acct mailport'}
$Lang::tr{'acct mailuser'}*
$Lang::tr{'acct mailpass'}*
$Lang::tr{'acct tls'}
$Lang::tr{'acct mailsender'}
 
$Lang::tr{'acct subject'}
$Lang::tr{'acct mailtxt'}
+
+ + + + + + +
+
+ +END +&Header::closebox(); +#BackButton + print< + + + + + +
+ +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< + + $Lang::tr{'name'} + $Lang::tr{'acct value'} + + + $Lang::tr{'acct dbsize'} + $sizedb MB + + + $Lang::tr{'acct rrdsize'} + $sizerrd MB + + + $Lang::tr{'acct oldestdb'} + $min + + + $Lang::tr{'acct latestdb'} + $max + + +
+END + &Header::closebox(); + #print Database maintenance table + &Header::openbox('100%', 'left', $Lang::tr{'acct dbmaintenance'}); + print< +
+ + + + + + + + + + + + + + + + + + + + + +
$Lang::tr{'acct task'}$Lang::tr{'acct parameter'}$Lang::tr{'acct action'}
$Lang::tr{'acct emptydb'}
$Lang::tr{'acct emptydbtraf'}
$Lang::tr{'acct delbefore'} (ACCT,ACCT_HIST)
+
+END + &Header::closebox(); +#BackButton + print< + + + + + +
+ +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 + if ($settings{'EXPERT'} eq 'on'){ + print ""; + } + print< +
+ +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 "
$grhost
"; + print "$cgiparams{'traffic'}"; + &Header::closebox(); + print< + +
+ +
+ + +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"


"; + + 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 "Tanzmaus"; +} + +sub statusbox{ + my $bgcolor1=''; + my $bgcolor2=''; + my $message; + if ($proxsrv eq $Lang::tr{'stopped'}){ + $bgcolor1="bgcolor='${Header::colourred}' align='center'"; + $message="
$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."
$Lang::tr{'acct proxylog_enable'}"; + }else{ + $bgcolor2="bgcolor='${Header::colourgreen}' align='center'"; + } + &Header::openbox('100%', 'left', ); + print< + + + + + + + + + + + + +EOF + if ($message){ + print""; + } + print"
$Lang::tr{'service'}$Lang::tr{'status'}
$Lang::tr{'proxy'}$proxsrv
$Lang::tr{'logging'}$proxlog
$message
$Lang::tr{'acct nodata'}
"; + &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 + if($cgiparams{'update'} eq 'on'){ + print""; + print ""; + }else{ + print ""; + } + print"
$Lang::tr{'acct hint_hoster'}:
$Lang::tr{'acct companytype'} + $Lang::tr{'acct customer'}   + $Lang::tr{'acct hoster'}$Lang::tr{'acct bank'} +
$Lang::tr{'acct company'} + $Lang::tr{'acct iban'}* +
$Lang::tr{'acct name1'}* + $Lang::tr{'acct bic'}* +
$Lang::tr{'acct str'} + $Lang::tr{'acct blz'} +
$Lang::tr{'acct str_nr'}$Lang::tr{'acct kto'} +
$Lang::tr{'acct plz'} + $Lang::tr{'acct email'}
$Lang::tr{'acct city'}$Lang::tr{'acct inet'}* +
$Lang::tr{'acct hrb'}* +
$Lang::tr{'acct ustid'}
$Lang::tr{'acct tel'}* +
$Lang::tr{'acct fax'}* +
*$Lang::tr{'acct optional'}
"; +&Header::closebox(); +#Upper BackButton +print< + + + + +
+ +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 ""; + 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" + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + +
"; + }else{ + print""; + } + print< +
$Lang::tr{'acct company'}$Lang::tr{'acct bank'} +
+ + + + + + + + + + + + + + + + + + + + +
+
+ + + +
+
$comp
$name1$Lang::tr{'acct bank'}$bank
$Lang::tr{'acct iban'}$iban
$str $nr$Lang::tr{'acct bic'}$bic
$plz $city$Lang::tr{'acct blz'}$blz
$Lang::tr{'acct kto'}$kto
+
+ +END + if (! $count % 2){ + print""; + } + } + } + if ( ($count % 2)){ + print""; + } + print ""; + &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 ""; + 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" + + + + + + + + + + + + + + + + +
"; + + print< +
+ $Lang::tr{'acct company'} + +
+ + + + + + + + + + + + + + + + + + + + +
+ 		+
+ + + +
+
$comp
$name1
$str $nr
$plz $city

+ + +END + if(! ($count % 3)) { + print""; + } + } + + } + if ($count %2){ + print""; + } + print""; + &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
"; + }else{ + $errormessage.="$Lang::tr{'acct invalid'} $field
"; + } + } + 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'}
"; + }else{ + if(! &validalphanumfield($cgiparams{'txt_str_nr'})){ + $errormessage.="$Lang::tr{'acct invalid'} $Lang::tr{'acct str_nr'}
"; + } + } + + #Check POSTAL-CODE + if (! $cgiparams{'txt_plz'}){ + $errormessage.="$Lang::tr{'acct empty field'} $Lang::tr{'acct plz'}
"; + }else{ + if(! &validnumfield($cgiparams{'txt_plz'})){ + $errormessage.="$Lang::tr{'acct invalid'} $Lang::tr{'acct plz'}
"; + } + } + + #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'}
"; + }else{ + if (! &General::validemail($cgiparams{'txt_email'})){ + $errormessage.="
$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'}
"; + } + } + if($cgiparams{'txt_bic'}){ + if(!&validalphanumfield($cgiparams{'txt_bic'})){ + $errormessage.="$Lang::tr{'acct invalid'} $Lang::tr{'acct bic'}
"; + } + } + 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'}
"; + } + #Check BANKACCOUNT + if($cgiparams{'txt_kto'}){ + if(! &validnumfield($cgiparams{'txt_kto'})){ + $errormessage.="$Lang::tr{'acct invalid'} $Lang::tr{'acct kto'}
"; + } + }elsif(!$cgiparams{'txt_kto'}){ + $errormessage.="$Lang::tr{'acct empty field'} $Lang::tr{'acct kto'}
"; + } + }elsif(!$cgiparams{'txt_iban'} && !$cgiparams{'txt_blz'}){ + $errormessage.="$Lang::tr{'acct empty field'} $Lang::tr{'acct blz'}
"; + #Check BANKACCOUNT + if($cgiparams{'txt_kto'}){ + if(! &validnumfield($cgiparams{'txt_kto'})){ + $errormessage.="$Lang::tr{'acct invalid'} $Lang::tr{'acct kto'}
"; + } + }elsif(!$cgiparams{'txt_kto'}){ + $errormessage.="$Lang::tr{'acct empty field'} $Lang::tr{'acct kto'}
"; + } + } + + #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'}
"; + } + } + #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'}
"; + } + } + + 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 = ) { + 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 "$errormessage\n"; + print " \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 + #Print Dropdown Menu for HOSTER and CUSTOMER + print ""; + print ""; + print ""; + #Print multiselectbox for hosts/users which should be part of this group + my $hosts=&ACCT::gethosts; + print""; + print ""; + #set right decimal seperator for cent value + setlocale(LC_NUMERIC,"$mainsettings{'LANGUAGE'}_$uplang"); + my $val=sprintf"%.3f",$cgiparams{'txt_cent'}; + print""; + #Optional note + print""; + print"
$Lang::tr{'name'}

$Lang::tr{'acct billtext1'}*

$Lang::tr{'acct hoster'}

$Lang::tr{'acct customer'}

$Lang::tr{'acct members'}

$Lang::tr{'acct cent'}$settings{'CURRENCY'}
*$Lang::tr{'acct optional'}


"; + print"
"; + + #LOGO Upload if update eq 'on' + if ($cgiparams{'update'} eq 'on'){ + print< + + $Lang::tr{'acct logo upload'} + + + + +
$Lang::tr{'acct logo'} +END + #Show Logo in webinterface with 1/2 size if set + if (-f "$logopath/$cgiparams{'txt_billgroup'}/logo.png"){ + print"$logopath/$cgiparams{"; + }else{ + print"
$Lang::tr{'no'}"; + } + #Show optional CC Mailadresses + print< +
$Lang::tr{'acct mailrcpt'} +
$mailrcpt + + +
$Lang::tr{'acct ccmail'} +
+ + + + +END + print""; + } + print"
"; + #Print SAVE or EDIT Button + if($cgiparams{'update'} eq 'on'){ + print ""; + print ""; + print ""; + }else{ + print ""; + } + print"


"; + &Header::closebox(); +#BackButton + print< + + + + +
+ +
+ +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 +} + &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< + + $Lang::tr{'name'} + $Lang::tr{'acct hoster'} + $Lang::tr{'acct customer'} + $Lang::tr{'acct members'} + + +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< + $name + $host + $cust + $amount + +END + my $members=&ACCT::listhosts($name); + my @mem=split(/\|/,$members); + my $msg; + foreach my $m (@mem){ + $msg.=$m."\n"; + } + print"$Lang::tr{'acct"; + print< +
+ + + + + + + + +
+ + + +
+ + + +
+ + + +
+ + + +
+ + +
+ + + +
+ + +END + } + print ""; + &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< + + + + + + + + + + + +
$Lang::tr{'acct month'}$Lang::tr{'acct year'}
+
+END + #View table with all hosts + print< + + $Lang::tr{'name'} + $Lang::tr{'acct traffic'} + $Lang::tr{'from'} + $Lang::tr{'to'} + + +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"$name$lineval $type".&getdate($mintime)."".&getdate($maxtime).""; + print< +
+ + + + + + +
+ + + +END + } + }else{ + print "
$Lang::tr{'acct no data'}"; + } + print ""; + &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 + #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 ""; + print ""; + } + } + print< + + + + + + +
$Lang::tr{'acct amount'}$Lang::tr{'acct name'}$Lang::tr{'acct price pp'}
$amnt$pos$locale_price $settings{'CURRENCY'}"; + print ""; + print ""; + print "

+ + + + + + +
+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< + + $Lang::tr{'acct nr'} + $Lang::tr{'acct path'} + $Lang::tr{'name'} + $Lang::tr{'acct generated'} + + + +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 "$no$path$name$date"; + print "
"; + print ""; + my $file="$path/$name"; + print ""; + print ""; + print"
"; + } + print "
"; + }else{ + print "
$Lang::tr{'acct no data'}"; + } + &Header::closebox(); + + #BackButton +print< + +
+ +
+ + +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'}
"; + } + }elsif(! &General::validfqdn($cgiparams{'txt_mailserver'})){ + $errormessage.="$Lang::tr{'acct invalid mailfqdn'} $cgiparams{'txt_mailserver'}
"; + } + #Check valid mailserverport + if($cgiparams{'txt_mailport'} < 1 || $cgiparams{'txt_mailport'} > 65535){ + $errormessage.="$Lang::tr{'acct invalid mailport'} $cgiparams{'txt_mailport'}
"; + } + #Check valid sender + if(! $cgiparams{'txt_mailsender'}){ + $errormessage.="$Lang::tr{'acct empty field'} $Lang::tr{'acct mailsender'}
"; + }else{ + if (! &General::validemail($cgiparams{'txt_mailsender'})){ + $errormessage.="
$Lang::tr{'acct invalid'} $Lang::tr{'acct mailsender'}
"; + } + } + return $errormessage; +} diff --git a/src/squid-accounting/acct-lib.pl b/src/squid-accounting/acct-lib.pl new file mode 100755 index 0000000000..96456c9b0b --- /dev/null +++ b/src/squid-accounting/acct-lib.pl @@ -0,0 +1,919 @@ +#!/usr/bin/perl +############################################################################### +# # +# IPFire.org - A linux based firewall # +# Copyright (C) 2014 IPFire Team # +# # +# 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 . # +# # +############################################################################### +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; diff --git a/src/squid-accounting/acct.de.pl b/src/squid-accounting/acct.de.pl new file mode 100644 index 0000000000..2110cf7014 --- /dev/null +++ b/src/squid-accounting/acct.de.pl @@ -0,0 +1,134 @@ +# 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 diff --git a/src/squid-accounting/acct.en.pl b/src/squid-accounting/acct.en.pl new file mode 100644 index 0000000000..bdbf5b2ac6 --- /dev/null +++ b/src/squid-accounting/acct.en.pl @@ -0,0 +1,134 @@ +# 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 diff --git a/src/squid-accounting/acct.pl b/src/squid-accounting/acct.pl new file mode 100755 index 0000000000..59898b75fe --- /dev/null +++ b/src/squid-accounting/acct.pl @@ -0,0 +1,265 @@ +#!/usr/bin/perl +############################################################################### +# # +# IPFire.org - A linux based firewall # +# Copyright (C) 2014 IPFire Team # +# # +# 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 . # +# # +############################################################################### + + +########### +# 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 = ) { + 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; + } +} diff --git a/src/squid-accounting/config/backup/includes/squid-accounting b/src/squid-accounting/config/backup/includes/squid-accounting new file mode 100644 index 0000000000..53116a5c03 --- /dev/null +++ b/src/squid-accounting/config/backup/includes/squid-accounting @@ -0,0 +1,5 @@ +/var/ipfire/accounting/settings.conf +/var/ipfire/accounting/bill +/srv/web/ipfire/html/accounting +/var/ipfire/accounting/acct.db +/var/log/accounting.log diff --git a/src/squid-accounting/dbinstall.pl b/src/squid-accounting/dbinstall.pl new file mode 100755 index 0000000000..0443732b7e --- /dev/null +++ b/src/squid-accounting/dbinstall.pl @@ -0,0 +1,49 @@ +#!/usr/bin/perl +############################################################################### +# # +# IPFire.org - A linux based firewall # +# Copyright (C) 2014 IPFire Team # +# # +# 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 . # +# # +############################################################################### + + +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; -- 2.39.2