include Config
-VER = 1.0.2
+VER = 1.0.3
THISAPP = squid-accounting-$(VER)
DIR_APP = $(DIR_SRC)/$(THISAPP)
TARGET = $(DIR_INFO)/$(THISAPP)
PROG = squid-accounting
-PAK_VER = 4
+PAK_VER = 5
DEPS = "perl-DBI perl-DBD-SQLite perl-File-ReadBackwards perl-PDF-API2 sendEmail"
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)){
+ 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
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);
+ my $mon1=$_[0];
+ my $year1=$_[1];
+ my ($from,$till)=&ACCT::getmonth($mon1,$year1);
$count=0;
#Menu to display another month
print<<END;
</select></td>
<td style='text-align: center;'><select name='year'>
END
- for (my $j=2014;$j<=($year);$j++){
+ for (my $j=2014;$j<=($year1);$j++){
if(($_[1]) eq $j){
print"<option selected>$j</option>";
}else{
<th></th>
</tr>
END
- my $res = $dbh->selectall_arrayref("SELECT SUM(BYTES),min(TIME_RUN),max(TIME_RUN),NAME from ACCT where TIME_RUN between ".$from." and ".$till." group by NAME;");
+ my $res;
+ if (($mon)+1 == $mon1 && ($year)+1900 == $year1){
+ $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;");
+ }else{
+ $res = $dbh->selectall_arrayref("SELECT SUM(BYTES),min(strftime('%s',TIME_RUN)),max(strftime('%s',TIME_RUN)),NAME from ACCT_HIST where date(TIME_RUN) > date($from,'unixepoch') and date(TIME_RUN) < date($till,'unixepoch') group by NAME;");
+ }
my $sumbytes;
my $type;
my $lineval;
<input type='image' src='/images/utilities-system-monitor.png' alt="$Lang::tr{'status'}" title="$Lang::tr{'status'}" />
<input type='hidden' name='ACTION' value='viewgraph'>
<input type='hidden' name='host' value='$name'>
- <input type='hidden' name='month' value='$mon'>
- <input type='hidden' name='year' value='$year'>
+ <input type='hidden' name='month' value='$mon1'>
+ <input type='hidden' name='year' value='$year1'>
<input type='hidden' name='traffic' value="$Lang::tr{'acct sum'} $Lang::tr{'acct traffic'} $lineval $type">
</form>
}
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');");
+ $dbh->do("insert into ACCT_HIST select datetime(TIME_RUN,'unixepoch'),NAME,SUM(BYTES) from ACCT where datetime(TIME_RUN,'unixepoch') < datetime('now','start of month') group by NAME,datetime(TIME_RUN,'unixepoch');");
+ $dbh->do("DELETE FROM ACCT WHERE datetime(TIME_RUN,'unixepoch') < date('now','start of month');");
}
sub gethourgraphdata {
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;");
+ if ($table eq 'ACCT_HIST'){
+ $res = $dbh->selectall_arrayref( "SELECT strftime('%d.%m.%Y',TIME_RUN),(SELECT SUM(BYTES)/1024/1024 FROM ACCT_HIST WHERE TIME_RUN <= ah.TIME_RUN and TIME_RUN > date($from,'unixepoch') and NAME = '".$name."') kum_bytes FROM ACCT_HIST ah WHERE date(TIME_RUN) > date(".$from.",'unixepoch') AND date(TIME_RUN) < date(".$till.",'unixepoch') AND NAME = '".$name."' group by date(TIME_RUN);");
}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;");
+ $res = $dbh->selectall_arrayref( "SELECT strftime('%d.%m.%Y',xx.tag),(SELECT SUM(BYTES)/1024/1024 FROM ACCT WHERE date(TIME_RUN,'unixepoch') <= xx.tag and TIME_RUN > ".$from." and NAME = '".$name."') kum_bytes FROM (SELECT NAME,date(TIME_RUN,'unixepoch') tag,SUM(BYTES)/1024/1024 sbytes FROM ACCT WHERE NAME='".$name."' and TIME_RUN between ".$from." and ".$till." GROUP by NAME,date(TIME_RUN,'unixepoch')) xx;");
}
$dbh=closedb;
return $res;
my $monat=$_[0]-1 if($_[0]);
my $tag=1;
my $time1=timelocal(0,0,0,$tag,$monat,$jahr);
- my $time2=0;
- if (($monat+1) == 12){
- $time2=timelocal(0,0,0,$tag,0,$jahr+1);
- }else{
- $time2=timelocal(0,0,0,$tag,$monat+1,$jahr);
- }
+ my $time2=timelocal(0,0,0,$tag,($monat+1),$jahr);
--$time2;
return ($time1,$time2);
}
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
+ #move all db entries older than this month to second table and cumulate them daily
&ACCT::movedbdata;
&ACCT::logger($settings{'LOG'},"New Month. Old trafficvalues moved to ACCT_HIST Table\n");
if ($settings{'USEMAIL'} eq 'on'){