mod_accounting_sql.pm 30 KB
Newer Older
Nigel Kukard's avatar
Nigel Kukard committed
1
# SQL accounting database
2
# Copyright (C) 2007-2019, AllWorldIT
3
#
Nigel Kukard's avatar
Nigel Kukard committed
4 5 6 7
# 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 2 of the License, or
# (at your option) any later version.
8
#
Nigel Kukard's avatar
Nigel Kukard committed
9 10 11 12
# 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.
13
#
Nigel Kukard's avatar
Nigel Kukard committed
14 15 16 17
# You should have received a copy of the GNU General Public License along
# with this program; if not, write to the Free Software Foundation, Inc.,
# 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.

Robert Anderson's avatar
Robert Anderson committed
18
package smradius::modules::accounting::mod_accounting_sql;
Nigel Kukard's avatar
Nigel Kukard committed
19 20 21 22 23 24

use strict;
use warnings;

# Modules we need
use smradius::constants;
25 26 27
use AWITPT::Cache;
use AWITPT::DB::DBLayer;
use AWITPT::Util;
Nigel Kukard's avatar
Nigel Kukard committed
28 29 30
use smradius::logging;
use smradius::util;

31
use POSIX qw(ceil);
32
use DateTime;
33
use Math::BigInt;
34
use Math::BigFloat;
35

Nigel Kukard's avatar
Nigel Kukard committed
36 37

# Exporter stuff
Nigel Kukard's avatar
Nigel Kukard committed
38 39
use base qw(Exporter);
our @EXPORT = qw(
Nigel Kukard's avatar
Nigel Kukard committed
40
);
Nigel Kukard's avatar
Nigel Kukard committed
41
our @EXPORT_OK = qw(
Nigel Kukard's avatar
Nigel Kukard committed
42 43 44 45 46 47 48 49
);



# Plugin info
our $pluginInfo = {
	Name => "SQL Accounting Database",
	Init => \&init,
50 51

	# Cleanup run by smadmin
52
	CleanupOrder => 30,
53
	Cleanup => \&cleanup,
54

Nigel Kukard's avatar
Nigel Kukard committed
55 56
	# Accounting database
	Accounting_log => \&acct_log,
57
	Accounting_getUsage => \&getUsage
Nigel Kukard's avatar
Nigel Kukard committed
58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73
};


# Module config
my $config;


## @internal
# Initialize module
sub init
{
	my $server = shift;
	my $scfg = $server->{'inifile'};


	# Enable support for database
Robert Anderson's avatar
 
Robert Anderson committed
74 75
	$server->log(LOG_NOTICE,"[MOD_ACCOUNTING_SQL] Enabling database support");
	if (!$server->{'smradius'}->{'database'}->{'enabled'}) {
Nigel Kukard's avatar
Nigel Kukard committed
76
		$server->log(LOG_NOTICE,"[MOD_ACCOUNTING_SQL] Enabling database support.");
Robert Anderson's avatar
 
Robert Anderson committed
77
		$server->{'smradius'}->{'database'}->{'enabled'} = 1;
Nigel Kukard's avatar
Nigel Kukard committed
78 79 80
	}

	# Default configs...
Robert Anderson's avatar
 
Robert Anderson committed
81
	$config->{'accounting_start_query'} = '
82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
		INSERT INTO
			@TP@accounting
		(
			Username,
			ServiceType,
			FramedProtocol,
			NASPort,
			NASPortType,
			CallingStationID,
			CalledStationID,
			NASPortID,
			AcctSessionID,
			FramedIPAddress,
			AcctAuthentic,
			EventTimestamp,
			AcctStatusType,
			NASIdentifier,
			NASIPAddress,
100
			AcctDelayTime,
Robert Anderson's avatar
Robert Anderson committed
101 102 103 104 105 106 107
			AcctSessionTime,
			AcctInputOctets,
			AcctInputGigawords,
			AcctInputPackets,
			AcctOutputOctets,
			AcctOutputGigawords,
			AcctOutputPackets,
108
			PeriodKey
109 110 111
		)
		VALUES
		(
112
			%{user.Username},
113 114 115 116 117 118
			%{request.Service-Type},
			%{request.Framed-Protocol},
			%{request.NAS-Port},
			%{request.NAS-Port-Type},
			%{request.Calling-Station-Id},
			%{request.Called-Station-Id},
119
			%{request.NAS-Port-Id},
120 121 122 123 124 125 126
			%{request.Acct-Session-Id},
			%{request.Framed-IP-Address},
			%{request.Acct-Authentic},
			%{request.Timestamp},
			%{request.Acct-Status-Type},
			%{request.NAS-Identifier},
			%{request.NAS-IP-Address},
127
			%{request.Acct-Delay-Time},
128 129 130 131 132 133 134
			%{request.Acct-Session-Time},
			%{request.Acct-Input-Octets},
			%{request.Acct-Input-Gigawords},
			%{request.Acct-Input-Packets},
			%{request.Acct-Output-Octets},
			%{request.Acct-Output-Gigawords},
			%{request.Acct-Output-Packets},
135
			%{query.PeriodKey}
136
		)
Robert Anderson's avatar
 
Robert Anderson committed
137
	';
Nigel Kukard's avatar
Nigel Kukard committed
138

139 140
	$config->{'accounting_update_get_records_query'} = '
		SELECT
141 142 143 144 145 146 147
			SUM(AcctInputOctets) AS AcctInputOctets,
			SUM(AcctInputPackets) AS AcctInputPackets,
			SUM(AcctOutputOctets) AS AcctOutputOctets,
			SUM(AcctOutputPackets) AS AcctOutputPackets,
			SUM(AcctInputGigawords) AS AcctInputGigawords,
			SUM(AcctOutputGigawords) AS AcctOutputGigawords,
			SUM(AcctSessionTime) AS AcctSessionTime,
148 149 150 151
			PeriodKey
		FROM
			@TP@accounting
		WHERE
152
			Username = %{user.Username}
153 154
			AND AcctSessionID = %{request.Acct-Session-Id}
			AND NASIPAddress = %{request.NAS-IP-Address}
155
			AND NASPort = %{request.NAS-Port}
156 157 158 159 160 161
		GROUP BY
			PeriodKey
		ORDER BY
			ID ASC
	';

Robert Anderson's avatar
 
Robert Anderson committed
162
	$config->{'accounting_update_query'} = '
163 164 165
		UPDATE
			@TP@accounting
		SET
166 167 168 169 170 171 172
			AcctSessionTime = %{query.Acct-Session-Time},
			AcctInputOctets = %{query.Acct-Input-Octets},
			AcctInputGigawords = %{query.Acct-Input-Gigawords},
			AcctInputPackets = %{query.Acct-Input-Packets},
			AcctOutputOctets = %{query.Acct-Output-Octets},
			AcctOutputGigawords = %{query.Acct-Output-Gigawords},
			AcctOutputPackets = %{query.Acct-Output-Packets},
173 174
			AcctStatusType = %{request.Acct-Status-Type}
		WHERE
175
			Username = %{user.Username}
176 177
			AND AcctSessionID = %{request.Acct-Session-Id}
			AND NASIPAddress = %{request.NAS-IP-Address}
178
			AND NASPort = %{request.NAS-Port}
179
			AND PeriodKey = %{query.PeriodKey}
Robert Anderson's avatar
 
Robert Anderson committed
180
	';
Nigel Kukard's avatar
Nigel Kukard committed
181

182 183 184 185
	$config->{'accounting_stop_status_query'} = '
		UPDATE
			@TP@accounting
		SET
186 187 188
			AcctStatusType = %{request.Acct-Status-Type},
			AcctTerminateCause = %{request.Acct-Terminate-Cause}
		WHERE
189
			Username = %{user.Username}
190 191
			AND AcctSessionID = %{request.Acct-Session-Id}
			AND NASIPAddress = %{request.NAS-IP-Address}
192
			AND NASPort = %{request.NAS-Port}
Robert Anderson's avatar
 
Robert Anderson committed
193
	';
Nigel Kukard's avatar
Nigel Kukard committed
194

195
	$config->{'accounting_usage_query'} = '
196
		SELECT
197 198 199 200 201
			SUM(AcctInputOctets) AS AcctInputOctets,
			SUM(AcctOutputOctets) AS AcctOutputOctets,
			SUM(AcctInputGigawords) AS AcctInputGigawords,
			SUM(AcctOutputGigawords) AS AcctOutputGigawords,
			SUM(AcctSessionTime) AS AcctSessionTime
202 203 204
		FROM
			@TP@accounting
		WHERE
205
			Username = %{user.Username}
206
			AND PeriodKey = %{query.PeriodKey}
207
	';
Nigel Kukard's avatar
Nigel Kukard committed
208

209 210 211 212 213 214 215 216 217 218 219 220 221 222
	$config->{'accounting_usage_query_period'} = '
		SELECT
			SUM(AcctInputOctets) AS AcctInputOctets,
			SUM(AcctOutputOctets) AS AcctOutputOctets,
			SUM(AcctInputGigawords) AS AcctInputGigawords,
			SUM(AcctOutputGigawords) AS AcctOutputGigawords,
			SUM(AcctSessionTime) AS AcctSessionTime
		FROM
			@TP@accounting
		WHERE
			Username = %{user.Username}
			AND EventTimestamp > %{query.PeriodKey}
	';

223 224 225 226 227 228
	$config->{'accounting_select_duplicates_query'} = '
		SELECT
			ID
		FROM
			@TP@accounting
		WHERE
229
			Username = %{user.Username}
230 231
			AND AcctSessionID = %{request.Acct-Session-Id}
			AND NASIPAddress = %{request.NAS-IP-Address}
232
			AND NASPort = %{request.NAS-Port}
233 234 235 236 237 238 239 240 241 242 243 244 245
			AND PeriodKey = %{query.PeriodKey}
		ORDER BY
			ID
			LIMIT 99 OFFSET 1
	';

	$config->{'accounting_delete_duplicates_query'} = '
		DELETE FROM
			@TP@accounting
		WHERE
			ID = %{query.DuplicateID}
	';

246 247 248
	$config->{'accounting_usage_cache_time'} = 300;


Nigel Kukard's avatar
Nigel Kukard committed
249 250 251 252 253
	# Setup SQL queries
	if (defined($scfg->{'mod_accounting_sql'})) {
		# Pull in queries
		if (defined($scfg->{'mod_accounting_sql'}->{'accounting_start_query'}) &&
				$scfg->{'mod_accounting_sql'}->{'accounting_start_query'} ne "") {
254 255 256 257 258 259
			if (ref($scfg->{'mod_accounting_sql'}->{'accounting_start_query'}) eq "ARRAY") {
				$config->{'accounting_start_query'} = join(' ',
						@{$scfg->{'mod_accounting_sql'}->{'accounting_start_query'}});
			} else {
				$config->{'accounting_start_query'} = $scfg->{'mod_accounting_sql'}->{'accounting_start_query'};
			}
Nigel Kukard's avatar
Nigel Kukard committed
260
		}
261 262 263 264 265 266 267 268 269
		if (defined($scfg->{'mod_accounting_sql'}->{'accounting_update_get_records_query'}) &&
				$scfg->{'mod_accounting_sql'}->{'accounting_update_get_records_query'} ne "") {
			if (ref($scfg->{'mod_accounting_sql'}->{'accounting_update_get_records_query'}) eq "ARRAY") {
				$config->{'accounting_update_get_records_query'} = join(' ',
						@{$scfg->{'mod_accounting_sql'}->{'accounting_update_get_records_query'}});
			} else {
				$config->{'accounting_update_get_records_query'} = $scfg->{'mod_accounting_sql'}->{'accounting_update_get_records_query'};
			}
		}
Nigel Kukard's avatar
Nigel Kukard committed
270 271
		if (defined($scfg->{'mod_accounting_sql'}->{'accounting_update_query'}) &&
				$scfg->{'mod_accounting_sql'}->{'accounting_update_query'} ne "") {
272 273 274 275 276 277
			if (ref($scfg->{'mod_accounting_sql'}->{'accounting_update_query'}) eq "ARRAY") {
				$config->{'accounting_update_query'} = join(' ',
						@{$scfg->{'mod_accounting_sql'}->{'accounting_update_query'}});
			} else {
				$config->{'accounting_update_query'} = $scfg->{'mod_accounting_sql'}->{'accounting_update_query'};
			}
Nigel Kukard's avatar
Nigel Kukard committed
278
		}
279 280 281 282 283 284 285 286 287
		if (defined($scfg->{'mod_accounting_sql'}->{'accounting_stop_status_query'}) &&
				$scfg->{'mod_accounting_sql'}->{'accounting_stop_status_query'} ne "") {
			if (ref($scfg->{'mod_accounting_sql'}->{'accounting_stop_status_query'}) eq "ARRAY") {
				$config->{'accounting_stop_status_query'} = join(' ',
						@{$scfg->{'mod_accounting_sql'}->{'accounting_stop_status_query'}});
			} else {
				$config->{'accounting_stop_status_query'} = $scfg->{'mod_accounting_sql'}->{'accounting_stop_status_query'};
			}
		}
288 289 290 291 292
		if (defined($scfg->{'mod_accounting_sql'}->{'accounting_usage_query'}) &&
				$scfg->{'mod_accounting_sql'}->{'accounting_usage_query'} ne "") {
			if (ref($scfg->{'mod_accounting_sql'}->{'accounting_usage_query'}) eq "ARRAY") {
				$config->{'accounting_usage_query'} = join(' ',
						@{$scfg->{'mod_accounting_sql'}->{'accounting_usage_query'}});
293
			} else {
294
				$config->{'accounting_usage_query'} = $scfg->{'mod_accounting_sql'}->{'accounting_usage_query'};
295
			}
296
		}
297 298 299 300 301 302 303 304 305
		if (defined($scfg->{'mod_accounting_sql'}->{'accounting_usage_query_period'}) &&
				$scfg->{'mod_accounting_sql'}->{'accounting_usage_query_period'} ne "") {
			if (ref($scfg->{'mod_accounting_sql'}->{'accounting_usage_query_period'}) eq "ARRAY") {
				$config->{'accounting_usage_query_period'} = join(' ',
						@{$scfg->{'mod_accounting_sql'}->{'accounting_usage_query_period'}});
			} else {
				$config->{'accounting_usage_query_period'} = $scfg->{'mod_accounting_sql'}->{'accounting_usage_query_period'};
			}
		}
306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323
		if (defined($scfg->{'mod_accounting_sql'}->{'accounting_select_duplicates_query'}) &&
				$scfg->{'mod_accounting_sql'}->{'accounting_select_duplicates_query'} ne "") {
			if (ref($scfg->{'mod_accounting_sql'}->{'accounting_select_duplicates_query'}) eq "ARRAY") {
				$config->{'accounting_select_duplicates_query'} = join(' ',
						@{$scfg->{'mod_accounting_sql'}->{'accounting_select_duplicates_query'}});
			} else {
				$config->{'accounting_select_duplicates_query'} = $scfg->{'mod_accounting_sql'}->{'accounting_select_duplicates_query'};
			}
		}
		if (defined($scfg->{'mod_accounting_sql'}->{'accounting_delete_duplicates_query'}) &&
				$scfg->{'mod_accounting_sql'}->{'accounting_delete_duplicates_query'} ne "") {
			if (ref($scfg->{'mod_accounting_sql'}->{'accounting_delete_duplicates_query'}) eq "ARRAY") {
				$config->{'accounting_delete_duplicates_query'} = join(' ',
						@{$scfg->{'mod_accounting_sql'}->{'accounting_delete_duplicates_query'}});
			} else {
				$config->{'accounting_delete_duplicates_query'} = $scfg->{'mod_accounting_sql'}->{'accounting_delete_duplicates_query'};
			}
		}
324
		if (defined($scfg->{'mod_accounting_sql'}->{'accounting_usage_cache_time'})) {
325 326 327 328 329 330 331 332 333
			# Check if we're a boolean
			if (defined(my $val = isBoolean($scfg->{'mod_accounting_sql'}{'accounting_usage_cache_time'}))) {
				# If val is true, we default to the default anyway

				# We're disabled
				if (!$val) {
					$config->{'accounting_usage_cache_time'} = undef;
				}
			# We *could* have a value...
334
			} elsif ($scfg->{'mod_accounting_sql'}{'accounting_usage_cache_time'} =~ /^[0-9]+$/) {
335
				$config->{'accounting_usage_cache_time'} = $scfg->{'mod_accounting_sql'}{'accounting_usage_cache_time'};
336 337 338 339 340 341 342
			} else {
				$server->log(LOG_NOTICE,"[MOD_ACCOUNTING_SQL] Value for 'accounting_usage_cache_time' is invalid");
			}
		}
	}

	# Log this for info sake
Robert Anderson's avatar
Robert Anderson committed
343
	if (defined($config->{'accounting_usage_cache_time'})) {
344
		$server->log(LOG_NOTICE,"[MOD_ACCOUNTING_SQL] getUsage caching ENABLED, cache time is %ds.",
Robert Anderson's avatar
Robert Anderson committed
345
				$config->{'accounting_usage_cache_time'});
346 347
	} else {
		$server->log(LOG_NOTICE,"[MOD_ACCOUNTING_SQL] getUsage caching DISABLED");
Nigel Kukard's avatar
Nigel Kukard committed
348 349 350 351
	}
}


352
# Function to get radius user data usage
353
# The 'period' parameter is optional and is the number of days to return usage for
354 355
sub getUsage
{
356
	my ($server,$user,$packet,$period) = @_;
357 358 359 360

	# Build template
	my $template;
	foreach my $attr ($packet->attributes) {
361
		$template->{'request'}->{$attr} = $packet->rawattr($attr)
362
	}
363 364 365 366

	# Add user details
	$template->{'user'}->{'ID'} = $user->{'ID'};
	$template->{'user'}->{'Username'} = $user->{'Username'};
367

368
	# Current PeriodKey, this is used for non-$period queries
369
	my $now = DateTime->now->set_time_zone($server->{'smradius'}->{'event_timezone'});
370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388

	# Query template to use below
	my $queryTemplate;
	# If we're doing a query for a specific period
	if (defined($period)) {
		# We need to switch out the query to the period query
		$queryTemplate = "accounting_usage_query_period";
		# Grab a clone of now, and create the start date DateTime object
		my $startDate = $now->clone->subtract( 'days' => $period );
		# And we add the start date
		$template->{'query'}->{'PeriodKey'} = $startDate->ymd();

	# If not, we just use PeriodKey as normal...
	} else {
		# Set the normal PeriodKey query template to use
		$queryTemplate = "accounting_usage_query";
		# And set the period key to this month
		$template->{'query'}->{'PeriodKey'} = $now->strftime("%Y-%m");
	}
389

390 391
	# If we using caching, check how old the result is
	if (defined($config->{'accounting_usage_cache_time'})) {
Nigel Kukard's avatar
Nigel Kukard committed
392 393
		my ($res,$val) = cacheGetComplexKeyPair('mod_accounting_sql(getUsage)',$user->{'Username'}."/".
				$template->{'query'}->{'PeriodKey'});
394
		if (defined($val) && $val->{'CachedUntil'} > $user->{'_Internal'}->{'Timestamp-Unix'}) {
395 396 397 398
			return $val;
		}
	}

399
	# Replace template entries
400
	my (@dbDoParams) = templateReplace($config->{$queryTemplate},$template);
401 402 403 404

	# Fetch data
	my $sth = DBSelect(@dbDoParams);
	if (!$sth) {
405
		$server->log(LOG_ERR,"[MOD_ACCOUNTING_SQL] Database query failed: %s",AWITPT::DB::DBLayer::error());
406 407 408
		return;
	}

409 410
	# Our usage hash
	my %usageTotals;
411 412 413
	$usageTotals{'TotalSessionTime'} = Math::BigInt->new(0);
	$usageTotals{'TotalDataInput'} = Math::BigInt->new(0);
	$usageTotals{'TotalDataOutput'} = Math::BigInt->new(0);
414

415 416 417 418
	# Pull in usage and add up
	while (my $row = hashifyLCtoMC($sth->fetchrow_hashref(),
			qw(AcctSessionTime AcctInputOctets AcctInputGigawords AcctOutputOctets AcctOutputGigawords)
	)) {
419

420 421
		# Look for session time
		if (defined($row->{'AcctSessionTime'}) && $row->{'AcctSessionTime'} > 0) {
422
			$usageTotals{'TotalSessionTime'}->badd($row->{'AcctSessionTime'});
423 424 425
		}
		# Add input usage if we have any
		if (defined($row->{'AcctInputOctets'}) && $row->{'AcctInputOctets'} > 0) {
426
			$usageTotals{'TotalDataInput'}->badd($row->{'AcctInputOctets'});
427 428
		}
		if (defined($row->{'AcctInputGigawords'}) && $row->{'AcctInputGigawords'} > 0) {
429
			my $inputGigawords = Math::BigInt->new($row->{'AcctInputGigawords'});
Nigel Kukard's avatar
Nigel Kukard committed
430
			$inputGigawords->bmul(GIGAWORD_VALUE);
431
			$usageTotals{'TotalDataInput'}->badd($inputGigawords);
432 433 434
		}
		# Add output usage if we have any
		if (defined($row->{'AcctOutputOctets'}) && $row->{'AcctOutputOctets'} > 0) {
435
			$usageTotals{'TotalDataOutput'}->badd($row->{'AcctOutputOctets'});
436 437
		}
		if (defined($row->{'AcctOutputGigawords'}) && $row->{'AcctOutputGigawords'} > 0) {
438
			my $outputGigawords = Math::BigInt->new($row->{'AcctOutputGigawords'});
Nigel Kukard's avatar
Nigel Kukard committed
439
			$outputGigawords->bmul(GIGAWORD_VALUE);
440
			$usageTotals{'TotalDataOutput'}->badd($outputGigawords);
441
		}
442
	}
443
	DBFreeRes($sth);
444

445
	# Convert to bigfloat for accuracy
446
	my $totalData = Math::BigFloat->new(0);
447
	$totalData->badd($usageTotals{'TotalDataOutput'})->badd($usageTotals{'TotalDataInput'});
448
	my $totalTime = Math::BigFloat->new(0);
449 450
	$totalTime->badd($usageTotals{'TotalSessionTime'});

Robert Anderson's avatar
Robert Anderson committed
451
	# Rounding up
452
	my %res;
Robert Anderson's avatar
Robert Anderson committed
453 454
	$res{'TotalDataUsage'} = $totalData->bdiv(1024)->bdiv(1024)->bceil()->bstr();
	$res{'TotalSessionTime'} = $totalTime->bdiv(60)->bceil()->bstr();
455

456 457
	# If we using caching and got here, it means that we must cache the result
	if (defined($config->{'accounting_usage_cache_time'})) {
Robert Anderson's avatar
Robert Anderson committed
458
		$res{'CachedUntil'} = $user->{'_Internal'}->{'Timestamp-Unix'} + $config->{'accounting_usage_cache_time'};
459

460
		# Cache the result
Robert Anderson's avatar
Robert Anderson committed
461
		cacheStoreComplexKeyPair('mod_accounting_sql(getUsage)',$user->{'Username'}."/".$template->{'query'}->{'PeriodKey'},\%res);
462 463
	}

464
	return \%res;
465 466 467
}


Nigel Kukard's avatar
Nigel Kukard committed
468 469 470 471 472 473 474 475 476 477 478 479
## @log
# Try find a user
#
# @param server Server object
# @param user User object
# @param packet Radius packet
#
# @return Result
sub acct_log
{
	my ($server,$user,$packet) = @_;

480

Nigel Kukard's avatar
Nigel Kukard committed
481 482 483
	# Build template
	my $template;
	foreach my $attr ($packet->attributes) {
484
		$template->{'request'}->{$attr} = $packet->rawattr($attr);
Nigel Kukard's avatar
Nigel Kukard committed
485
	}
486 487
	# Fix event timestamp
	$template->{'request'}->{'Timestamp'} = $user->{'_Internal'}->{'Timestamp'};
488

489 490 491
	# Add user details
	$template->{'user'}->{'ID'} = $user->{'ID'};
	$template->{'user'}->{'Username'} = $user->{'Username'};
Nigel Kukard's avatar
Nigel Kukard committed
492

493
	# Current PeriodKey
494
	my $now = DateTime->now->set_time_zone($server->{'smradius'}->{'event_timezone'});
495 496 497 498
	my $periodKey = $now->strftime("%Y-%m");

	# For our queries
	$template->{'query'}->{'PeriodKey'} = $periodKey;
Nigel Kukard's avatar
Nigel Kukard committed
499

500 501 502
	# Default to being a new period, only if we update on INTERIM or STOP do we set this to 0
	my $newPeriod = 1;

503
	#
Robert Anderson's avatar
Robert Anderson committed
504
	# U P D A T E   &   S T O P   P A C K E T
505
	#
506
	if ($packet->rawattr('Acct-Status-Type') eq "2" || $packet->rawattr('Acct-Status-Type') eq "3") {
Robert Anderson's avatar
 
Robert Anderson committed
507
		# Replace template entries
508
		my @dbDoParams = templateReplace($config->{'accounting_update_get_records_query'},$template);
Robert Anderson's avatar
 
Robert Anderson committed
509

510 511
		# Fetch previous records of the same session
		my $sth = DBSelect(@dbDoParams);
Robert Anderson's avatar
 
Robert Anderson committed
512
		if (!$sth) {
513
			$server->log(LOG_ERR,"[MOD_ACCOUNTING_SQL] Database query failed: %s",AWITPT::DB::DBLayer::error());
514 515 516
			return;
		}

Nigel Kukard's avatar
Nigel Kukard committed
517 518 519 520 521 522
		# Convert session total gigawords into bytes
		my $totalInputBytes = Math::BigInt->new($template->{'request'}->{'Acct-Input-Gigawords'});
		my $totalOutputBytes = Math::BigInt->new($template->{'request'}->{'Acct-Output-Gigawords'});
		$totalInputBytes->bmul(GIGAWORD_VALUE);
		$totalOutputBytes->bmul(GIGAWORD_VALUE);
		# Add byte counters
523 524
		$totalInputBytes->badd($template->{'request'}->{'Acct-Input-Octets'});
		$totalOutputBytes->badd($template->{'request'}->{'Acct-Output-Octets'});
525 526 527 528 529
		# Packets, no conversion
		my $totalInputPackets = Math::BigInt->new($template->{'request'}->{'Acct-Input-Packets'});
		my $totalOutputPackets = Math::BigInt->new($template->{'request'}->{'Acct-Output-Packets'});
		# We don't need bigint here, but why not ... lets keep everything standard
		my $totalSessionTime = Math::BigInt->new($template->{'request'}->{'Acct-Session-Time'});
530 531

		# Loop through previous records and subtract them from our session totals
Nigel Kukard's avatar
Nigel Kukard committed
532
		while (my $sessionPart = hashifyLCtoMC($sth->fetchrow_hashref(),
533 534
				qw(AcctInputOctets AcctInputPackets AcctOutputOctets AcctOutputPackets AcctInputGigawords AcctOutputGigawords
					SessionTime PeriodKey)
Nigel Kukard's avatar
Nigel Kukard committed
535
		)) {
536 537 538 539 540 541 542 543
			# Make sure we treat undef values sort of sanely
			$sessionPart->{'AcctInputGigawords'} //= 0;
			$sessionPart->{'AcctInputOctets'} //= 0;
			$sessionPart->{'AcctOutputGigawords'} //= 0;
			$sessionPart->{'AcctOutputOctets'} //= 0;
			$sessionPart->{'AcctInputPackets'} //= 0;
			$sessionPart->{'AcctOutputPackets'} //= 0;
			$sessionPart->{'AcctSessionTime'} //= 0;
544

Nigel Kukard's avatar
Nigel Kukard committed
545 546 547 548 549 550
			# Convert the gigawords into bytes
			my $sessionInputBytes = Math::BigInt->new($sessionPart->{'AcctInputGigawords'});
			my $sessionOutputBytes = Math::BigInt->new($sessionPart->{'AcctOutputGigawords'});
			$sessionInputBytes->bmul(GIGAWORD_VALUE);
			$sessionOutputBytes->bmul(GIGAWORD_VALUE);
			# Add the byte counters
551 552
			$sessionInputBytes->badd($sessionPart->{'AcctInputOctets'});
			$sessionOutputBytes->badd($sessionPart->{'AcctOutputOctets'});
553
			# And packets
554 555
			my $sessionInputPackets = Math::BigInt->new($sessionPart->{'AcctInputPackets'});
			my $sessionOutputPackets = Math::BigInt->new($sessionPart->{'AcctOutputPackets'});
556
			# Finally session time
557
			my $sessionSessionTime = Math::BigInt->new($sessionPart->{'AcctSessionTime'});
558 559 560 561

			# Check if this record is from an earlier period
			if (defined($sessionPart->{'PeriodKey'}) && $sessionPart->{'PeriodKey'} ne $periodKey) {

562
				# Subtract from our total, we can hit NEG!!! ... we check for that below
563 564
				$totalInputBytes->bsub($sessionInputBytes);
				$totalOutputBytes->bsub($sessionOutputBytes);
565 566 567
				$totalInputPackets->bsub($sessionInputPackets);
				$totalOutputPackets->bsub($sessionOutputPackets);
				$totalSessionTime->bsub($sessionSessionTime);
568 569

				# We need to continue this session in a new entry
Robert Anderson's avatar
Robert Anderson committed
570
				$newPeriod = 1;
571
			}
Robert Anderson's avatar
 
Robert Anderson committed
572
		}
573
		DBFreeRes($sth);
Nigel Kukard's avatar
Nigel Kukard committed
574

575
		# Sanitize
576
		if ($totalInputBytes->is_neg()) {
577 578
			$totalInputBytes->bzero();
		}
579
		if ($totalOutputBytes->is_neg()) {
580 581
			$totalOutputBytes->bzero();
		}
582
		if ($totalInputPackets->is_neg()) {
583 584
			$totalInputPackets->bzero();
		}
585
		if ($totalOutputPackets->is_neg()) {
586 587
			$totalOutputPackets->bzero();
		}
588
		if ($totalSessionTime->is_neg()) {
589 590 591
			$totalSessionTime->bzero();
		}

592
		# Re-calculate
Nigel Kukard's avatar
Nigel Kukard committed
593 594
		my ($inputGigawordsStr,$inputOctetsStr) = $totalInputBytes->bdiv(GIGAWORD_VALUE);
		my ($outputGigawordsStr,$outputOctetsStr) = $totalOutputBytes->bdiv(GIGAWORD_VALUE);
595 596

		# Conversion to strings
597 598 599 600
		$template->{'query'}->{'Acct-Input-Gigawords'} = $inputGigawordsStr->bstr();
		$template->{'query'}->{'Acct-Input-Octets'} = $inputOctetsStr->bstr();
		$template->{'query'}->{'Acct-Output-Gigawords'} = $outputGigawordsStr->bstr();
		$template->{'query'}->{'Acct-Output-Octets'} = $outputOctetsStr->bstr();
601

602 603
		$template->{'query'}->{'Acct-Input-Packets'} = $totalInputPackets->bstr();
		$template->{'query'}->{'Acct-Output-Packets'} = $totalOutputPackets->bstr();
604

605
		$template->{'query'}->{'Acct-Session-Time'} = $totalSessionTime->bstr();
606

607

Robert Anderson's avatar
Robert Anderson committed
608 609
		# Replace template entries
		@dbDoParams = templateReplace($config->{'accounting_update_query'},$template);
610

Robert Anderson's avatar
Robert Anderson committed
611 612 613 614
		# Update database
		$sth = DBDo(@dbDoParams);
		if (!$sth) {
			$server->log(LOG_ERR,"[MOD_ACCOUNTING_SQL] Failed to update accounting ALIVE record: ".
615
					AWITPT::DB::DBLayer::error());
Robert Anderson's avatar
Robert Anderson committed
616 617
			return MOD_RES_NACK;
		}
618

Robert Anderson's avatar
Robert Anderson committed
619 620 621 622 623
		# If we updated *something* ...
		if ($sth ne "0E0") {
			# Be very sneaky .... if we updated something, this is obviously NOT a new period
			$newPeriod = 0;
			# If we updated a few things ... possibly duplicates?
624
			if ($sth > 1) {
625 626
				fixDuplicates($server, $template);
			}
627
		}
Robert Anderson's avatar
Robert Anderson committed
628 629
	}

630 631

	#
Robert Anderson's avatar
Robert Anderson committed
632 633 634
	# S T A R T   P A C K E T
	#
	# Possible aswell if we are missing a start packet for this session or for the period
635 636
	#

637
	if ($packet->rawattr('Acct-Status-Type') eq "1" || $newPeriod) {
Robert Anderson's avatar
 
Robert Anderson committed
638
		# Replace template entries
Robert Anderson's avatar
Robert Anderson committed
639 640 641
		my @dbDoParams = templateReplace($config->{'accounting_start_query'},$template);
		# Insert into database
		my $sth = DBDo(@dbDoParams);
642
		if (!$sth) {
Robert Anderson's avatar
Robert Anderson committed
643
			$server->log(LOG_ERR,"[MOD_ACCOUNTING_SQL] Failed to insert accounting START record: ".
644
					AWITPT::DB::DBLayer::error());
645 646
			return MOD_RES_NACK;
		}
647 648 649 650 651 652 653 654 655
		# Update first login?
		if (defined($user->{'_UserDB'}->{'Users_data_get'}) && defined($user->{'_UserDB'}->{'Users_data_set'})) {
			# Try get his first login
			my $firstLogin = $user->{'_UserDB'}->{'Users_data_get'}($server,$user,'global','FirstLogin');
			# If we don't get it, set it
			if (!defined($firstLogin)) {
				$user->{'_UserDB'}->{'Users_data_set'}($server,$user,'global','FirstLogin',$user->{'_Internal'}->{'Timestamp-Unix'});
			}
		}
Robert Anderson's avatar
Robert Anderson committed
656
	}
657

658

Robert Anderson's avatar
Robert Anderson committed
659 660 661
	#
	# S T O P   P A C K E T   specifics
	#
662

663
	if ($packet->rawattr('Acct-Status-Type') eq "2") {
664 665

		# Replace template entries
Robert Anderson's avatar
Robert Anderson committed
666
		my @dbDoParams = templateReplace($config->{'accounting_stop_status_query'},$template);
667 668

		# Update database (status)
Robert Anderson's avatar
Robert Anderson committed
669
		my $sth = DBDo(@dbDoParams);
Robert Anderson's avatar
 
Robert Anderson committed
670
		if (!$sth) {
671
			$server->log(LOG_ERR,"[MOD_ACCOUNTING_SQL] Failed to update accounting STOP record: %s",AWITPT::DB::DBLayer::error());
Robert Anderson's avatar
 
Robert Anderson committed
672 673
			return MOD_RES_NACK;
		}
Nigel Kukard's avatar
Nigel Kukard committed
674 675
	}

676

Nigel Kukard's avatar
Nigel Kukard committed
677 678 679 680
	return MOD_RES_ACK;
}


681 682 683 684 685 686 687 688 689 690 691 692
# Resolve duplicate records
sub fixDuplicates
{
	my ($server, $template) = @_;


	# Replace template entries
	my @dbDoParams = templateReplace($config->{'accounting_select_duplicates_query'},$template);

	# Select duplicates
	my $sth = DBSelect(@dbDoParams);
	if (!$sth) {
693
		$server->log(LOG_ERR,"[MOD_ACCOUNTING_SQL] Database query failed: %s",AWITPT::DB::DBLayer::error());
694 695 696 697 698
		return;
	}

	# Pull in duplicates
	my @IDList;
Nigel Kukard's avatar
Nigel Kukard committed
699
	while (my $duplicates = hashifyLCtoMC($sth->fetchrow_hashref(), qw(ID))) {
700 701 702 703 704 705 706 707 708 709 710 711 712 713 714 715
		push(@IDList,$duplicates->{'ID'});
	}
	DBFreeRes($sth);

	# Loop through IDs and delete
	DBBegin();
	foreach my $duplicateID (@IDList) {
		# Add ID list to the template
		$template->{'query'}->{'DuplicateID'} = $duplicateID;

		# Replace template entries
		@dbDoParams = templateReplace($config->{'accounting_delete_duplicates_query'},$template);

		# Delete duplicates
		$sth = DBDo(@dbDoParams);
		if (!$sth) {
716
			$server->log(LOG_ERR,"[MOD_ACCOUNTING_SQL] Database query failed: %s",AWITPT::DB::DBLayer::error());
717 718 719 720 721 722 723 724 725 726 727 728 729 730
			DBRollback();
			return;
		}
	}

	# Commit changes to the database
	$server->log(LOG_DEBUG,"[MOD_ACCOUNTING_SQL] Duplicate accounting records deleted");
	DBCommit();


	return
}


731 732 733
# Add up totals function
sub cleanup
{
734
	my ($server,$runForDate) = @_;
735 736


737 738 739 740 741 742 743 744 745
	# The datetime now
	my $now = DateTime->from_epoch(epoch => $runForDate)->set_time_zone($server->{'smradius'}->{'event_timezone'});

	# Use truncate to set all values after 'month' to their default values
	my $thisMonth = $now->clone()->truncate( to => "month" );

	# Last month..
	my $lastMonth = $thisMonth->clone()->subtract( months => 1 );
	my $prevPeriodKey = $lastMonth->strftime("%Y-%m");
746

747 748 749 750 751 752 753

	# Begin transaction
	DBBegin();

	$server->log(LOG_NOTICE,"[MOD_ACCOUNTING_SQL] Cleanup => Removing previous accounting summaries (if any)");

	# Delete duplicate records
754
	# NK: MYSQL SPECIFIC
755 756 757 758 759 760 761 762 763
	my $sth = DBDo('
		DELETE FROM
			@TP@accounting_summary
		WHERE
			STR_TO_DATE(PeriodKey,"%Y-%m") >= ?',
		$prevPeriodKey
	);
	if (!$sth) {
		$server->log(LOG_ERR,"[MOD_ACCOUNTING_SQL] Cleanup => Failed to delete accounting summary record: ".
764
				AWITPT::DB::DBLayer::error());
765 766
		DBRollback();
		return;
767 768
	}

769
	$server->log(LOG_NOTICE,"[MOD_ACCOUNTING_SQL] Cleanup => Generating accounting summaries");
770

771
	# Select totals for last month
772
	$sth = DBSelect('
773 774
		SELECT
			Username,
775 776 777 778 779
			AcctSessionTime,
			AcctInputOctets,
			AcctInputGigawords,
			AcctOutputOctets,
			AcctOutputGigawords
780 781 782
		FROM
			@TP@accounting
		WHERE
783
			PeriodKey = ?
784
		',
785
		$prevPeriodKey
786 787 788
	);
	if (!$sth) {
		$server->log(LOG_ERR,"[MOD_ACCOUNTING_SQL] Cleanup => Failed to select accounting record: ".
789
				AWITPT::DB::DBLayer::error());
790 791 792 793
		return;
	}

	# Load items into array
794 795
	my %usageTotals;
	while (my $row = hashifyLCtoMC($sth->fetchrow_hashref(),
796
			qw(Username AcctSessionTime AcctInputOctets AcctInputGigawords AcctOutputOctets AcctOutputGigawords)
797 798 799 800 801 802
	)) {

		# check if we've seen this user, if so just add up
		if (defined($usageTotals{$row->{'Username'}})) {
			# Look for session time
			if (defined($row->{'AcctSessionTime'}) && $row->{'AcctSessionTime'} > 0) {
803
				$usageTotals{$row->{'Username'}}{'TotalSessionTime'}->badd($row->{'AcctSessionTime'});
804 805 806
			}
			# Add input usage if we have any
			if (defined($row->{'AcctInputOctets'}) && $row->{'AcctInputOctets'} > 0) {
807
				$usageTotals{$row->{'Username'}}{'TotalDataInput'}->badd($row->{'AcctInputOctets'});
808 809
			}
			if (defined($row->{'AcctInputGigawords'}) && $row->{'AcctInputGigawords'} > 0) {
810
				my $inputGigawords = Math::BigInt->new($row->{'AcctInputGigawords'});
Nigel Kukard's avatar
Nigel Kukard committed
811
				$inputGigawords->bmul(GIGAWORD_VALUE);
812
				$usageTotals{$row->{'Username'}}{'TotalDataInput'}->badd($inputGigawords);
813 814 815
			}
			# Add output usage if we have any
			if (defined($row->{'AcctOutputOctets'}) && $row->{'AcctOutputOctets'} > 0) {
816
				$usageTotals{$row->{'Username'}}{'TotalDataOutput'}->badd($row->{'AcctOutputOctets'});
817 818
			}
			if (defined($row->{'AcctOutputGigawords'}) && $row->{'AcctOutputGigawords'} > 0) {
819
				my $outputGigawords = Math::BigInt->new($row->{'AcctOutputGigawords'});
Nigel Kukard's avatar
Nigel Kukard committed
820
				$outputGigawords->bmul(GIGAWORD_VALUE);
821
				$usageTotals{$row->{'Username'}}{'TotalDataOutput'}->badd($outputGigawords);
822 823 824 825
			}

		# This is a new record...
		} else {
826 827

			# Make BigInts for this user
828 829 830
			$usageTotals{$row->{'Username'}}{'TotalSessionTime'} = Math::BigInt->new(0);
			$usageTotals{$row->{'Username'}}{'TotalDataInput'} = Math::BigInt->new(0);
			$usageTotals{$row->{'Username'}}{'TotalDataOutput'} = Math::BigInt->new(0);
831

832 833
			# Look for session time
			if (defined($row->{'AcctSessionTime'}) && $row->{'AcctSessionTime'} > 0) {
834
				$usageTotals{$row->{'Username'}}{'TotalSessionTime'}->badd($row->{'AcctSessionTime'});
835
			}
836
			# Add input usage if we have any
837
			if (defined($row->{'AcctInputOctets'}) && $row->{'AcctInputOctets'} > 0) {
838
				$usageTotals{$row->{'Username'}}{'TotalDataInput'}->badd($row->{'AcctInputOctets'});
839 840
			}
			if (defined($row->{'AcctInputGigawords'}) && $row->{'AcctInputGigawords'} > 0) {
841
				my $inputGigawords = Math::BigInt->new($row->{'AcctInputGigawords'});
Nigel Kukard's avatar
Nigel Kukard committed
842
				$inputGigawords->bmul(GIGAWORD_VALUE);
843
				$usageTotals{$row->{'Username'}}{'TotalDataInput'}->badd($inputGigawords);
844
			}
845
			# Add output usage if we have any
846
			if (defined($row->{'AcctOutputOctets'}) && $row->{'AcctOutputOctets'} > 0) {
847
				$usageTotals{$row->{'Username'}}{'TotalDataOutput'}->badd($row->{'AcctOutputOctets'});
848 849
			}
			if (defined($row->{'AcctOutputGigawords'}) && $row->{'AcctOutputGigawords'} > 0) {
850
				my $outputGigawords = Math::BigInt->new($row->{'AcctOutputGigawords'});
Nigel Kukard's avatar
Nigel Kukard committed
851
				$outputGigawords->bmul(GIGAWORD_VALUE);
852
				$usageTotals{$row->{'Username'}}{'TotalDataOutput'}->badd($outputGigawords);
853
			}
854

855
		}
856 857
	}

858
	$server->log(LOG_NOTICE,"[MOD_ACCOUNTING_SQL] Cleanup => Creating new accounting summaries");
859

860 861
	# Loop through users and insert totals
	foreach my $username (keys %usageTotals) {
862 863 864 865 866 867 868 869

		# Convert to bigfloat for accuracy
		my $totalDataOutput = Math::BigFloat->new($usageTotals{$username}{'TotalDataOutput'});
		my $totalDataInput = Math::BigFloat->new($usageTotals{$username}{'TotalDataInput'});
		my $totalTime = Math::BigFloat->new($usageTotals{$username}{'TotalSessionTime'});

		# Rounding up
		my $res;
Robert Anderson's avatar
Robert Anderson committed
870 871 872
		$res->{'TotalDataInput'} = $totalDataInput->bdiv(1024)->bdiv(1024)->bceil()->bstr();
		$res->{'TotalDataOutput'} = $totalDataOutput->bdiv(1024)->bdiv(1024)->bceil()->bstr();
		$res->{'TotalSessionTime'} = $totalTime->bdiv(60)->bceil()->bstr();
873

874 875
		# Do query
		$sth = DBDo('
876
			INSERT INTO
877
				@TP@accounting_summary
878 879 880 881 882 883 884 885 886 887 888
			(
				Username,
				PeriodKey,
				TotalSessionTime,
				TotalInput,
				TotalOutput
			)
			VALUES
				(?,?,?,?,?)
			',
			$username,
889
			$prevPeriodKey,
890 891 892
			$res->{'TotalSessionTime'},
			$res->{'TotalDataInput'},
			$res->{'TotalDataOutput'}
893
		);
894 895
		if (!$sth) {
			$server->log(LOG_ERR,"[MOD_ACCOUNTING_SQL] Cleanup => Failed to create accounting summary record: ".
896
					AWITPT::DB::DBLayer::error());
897 898
			DBRollback();
			return;
899 900
		}

901 902 903 904
		# Lets log
		$server->log(LOG_DEBUG,"[MOD_ACCOUNTING_SQL] Cleanup => INSERT: Username = '%s', PeriodKey = '%s', ".
				"TotalSessionTime = '%s', TotalInput = '%s', TotalOutput = '%s'", $username, $prevPeriodKey,
				$res->{'TotalSessionTime'}, $res->{'TotalDataInput'}, $res->{'TotalDataOutput'});
905 906 907 908
	}

	# Commit if succeeded
	DBCommit();
909
	$server->log(LOG_NOTICE,"[MOD_ACCOUNTING_SQL] Cleanup => Accounting summaries created");
910 911 912
}


Nigel Kukard's avatar
Nigel Kukard committed
913

Nigel Kukard's avatar
Nigel Kukard committed
914
1;
Nigel Kukard's avatar
Nigel Kukard committed
915
# vim: ts=4