Squid-accounting: new Version 1.0.3 (graph updates, movedb update)

New Version. Now the data is correctly moved to hist table when month
has changed.
Also the graphs for old month starts by zero. In old version graphdata
was started by total amount of bytes.

diff --git a/lfs/squid-accounting b/lfs/squid-accounting
index 7eae4fbb624e7e865a0aebc5a0f2d732fc86e002..af7b281e4f477943b3cddfe1301464c9486de09e 100644 (file)
--- a/lfs/squid-accounting
+++ b/lfs/squid-accounting
@@ -9,13 +9,13 @@
 
 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"
 

diff --git a/src/squid-accounting/accounting.cgi b/src/squid-accounting/accounting.cgi
index eabb0c2956641bcb17676797f7ab9658fe2883c0..1ec9849eb7e3e14aae4c7bccf3b96ce0c1f4376b 100755 (executable)
--- a/src/squid-accounting/accounting.cgi
+++ b/src/squid-accounting/accounting.cgi
@@ -907,7 +907,7 @@ sub generatemonthgraph{
        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
@@ -1959,9 +1959,9 @@ END
 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;
@@ -1986,7 +1986,7 @@ 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{
@@ -2011,7 +2011,12 @@ END
                <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;
@@ -2036,8 +2041,8 @@ END
                                        <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>
                                        

diff --git a/src/squid-accounting/acct-lib.pl b/src/squid-accounting/acct-lib.pl
index 7969a5023908b9c3e5dc402e57d5f5b45f1a618b..68912358486d1f2373495e0fbe7169481a40f81d 100644 (file)
--- a/src/squid-accounting/acct-lib.pl
+++ b/src/squid-accounting/acct-lib.pl
@@ -93,8 +93,8 @@ sub delbefore {
 }
 
 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 {
@@ -119,10 +119,10 @@ sub getmonthgraphdata {
        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;
@@ -337,12 +337,7 @@ sub getmonth{
                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);
 }

diff --git a/src/squid-accounting/acct.pl b/src/squid-accounting/acct.pl
index 79fc7bae43eede00a9444d2c5e761cf7ec60793a..722268945826c0cd8d2228eb2d100f5011999131 100755 (executable)
--- a/src/squid-accounting/acct.pl
+++ b/src/squid-accounting/acct.pl
@@ -100,7 +100,7 @@ if (-f $proxyenabled && $proxylog eq $Lang::tr{'running'}){
                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'){