# 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 data cumulation functionality for FreeRadius accounting tables
#
# Cumulation of 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 of cumulation
# !!! EDIT THE @year PARAMETER ONLY !!!

SET @year = "2010";

# create a temporary radacct table

CREATE TABLE radacct1 LIKE radacct;
CREATE TABLE rm_radacct1 LIKE rm_radacct;

# cumulate data in the specified year

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

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

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

# delete the cumulated year from radacct

DELETE FROM radacct WHERE acctstarttime LIKE @yearlike;
DELETE FROM rm_radacct WHERE acctstarttime LIKE @yearlike;

# copy cumulated data

INSERT INTO radacct SELECT * FROM radacct1;
INSERT INTO rm_radacct SELECT * FROM rm_radacct1;

# drop temporary table

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