# THIS SCRIPTS COMES WITH ABSOLUTELY NO WARRANTY! USE IT ON YOUR OWN RISK!
# ALWAYS CREATE A FULL DATABASE BACKUP BEFORE EXECUTING THIS SCRIPT!
#
# This SQL script provides a radacct table pruning functionality
#
# Removal of old accounting data is neccessary if radacct table stores too much records
# and the login process is slown down due to a high load on the MySQL server
#
# Copyright 2004-2018, DMA Softlab LLC

# year to delete
# !!! EDIT THE @year PARAMETER ONLY !!!

SET @year = "2015";

# create a temporary radacct table

CREATE TABLE radacct1 (
  `username` varchar(64) NOT NULL default '',
  `acctsessiontime` int(12) default NULL,
  `acctinputoctets` bigint(20) default NULL,
  `acctoutputoctets` bigint(20) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

# cumulate datas from the previous years from radacct -> radacct1 

SET @yearlike = CONCAT(@year, "%");
SET @cumuldatetime = CONCAT(@year, "-12-31 23:59:59");

INSERT INTO radacct1 (username, acctsessiontime, acctinputoctets, acctoutputoctets)
SELECT username, SUM(acctsessiontime), SUM(acctinputoctets), SUM(acctoutputoctets)
FROM radacct
WHERE acctstarttime LIKE @yearlike
GROUP BY username;

# delete all data from radacct in the specified year

DELETE FROM radacct WHERE acctstarttime LIKE @yearlike;

# create a temporary rm_radacct table

CREATE TABLE rm_radacct1 (
  `username` varchar(64) NOT NULL default '',
  `acctsessiontime` int(12) default NULL,
  `ulbytes` bigint(20) default NULL,
  `dlbytes` bigint(20) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

# cumulate datas from the previous years from rm_radacct -> rm_radacct1 

SET @yearlike = CONCAT(@year, "%");
SET @cumuldatetime = CONCAT(@year, "-12-31 23:59:59");

INSERT INTO rm_radacct1 (username, acctsessiontime, ulbytes, dlbytes)
SELECT username, SUM(acctsessiontime), SUM(ulbytes), SUM(dlbytes)
FROM rm_radacct
WHERE acctstarttime LIKE @yearlike
GROUP BY username;

# delete all data from rm_radacct in the specified year

DELETE FROM rm_radacct WHERE acctstarttime LIKE @yearlike;

# substract the deleted values (radacct1) from radacct

UPDATE rm_users, radacct1
SET rm_users.uptimelimit = rm_users.uptimelimit - radacct1.acctsessiontime,
    rm_users.uplimit     = rm_users.uplimit     - radacct1.acctinputoctets,
    rm_users.downlimit   = rm_users.downlimit   - radacct1.acctoutputoctets,
    rm_users.comblimit   = rm_users.comblimit   - radacct1.acctoutputoctets - radacct1.acctinputoctets
WHERE rm_users.username = radacct1.username;

# substract the deleted values (rm_radacct1) from radacct

UPDATE rm_users, rm_radacct1
SET rm_users.uptimelimit = rm_users.uptimelimit + rm_radacct1.acctsessiontime,
    rm_users.uplimit     = rm_users.uplimit     + rm_radacct1.ulbytes,
    rm_users.downlimit   = rm_users.downlimit   + rm_radacct1.dlbytes,
    rm_users.comblimit   = rm_users.comblimit   + rm_radacct1.dlbytes + rm_radacct1.ulbytes
WHERE rm_users.username = rm_radacct1.username;

# drop temporary table

DROP TABLE `radacct1`;
DROP TABLE `rm_radacct1`;
