mod_accounting_sql.pm 27.8 KB
Newer Older
Nigel Kukard's avatar
Nigel Kukard committed
1
# SQL accounting database
2
3
# Copyright (C) 2007-2016, AllWorldIT
#
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
	$config->{'accounting_select_duplicates_query'} = '
		SELECT
			ID
		FROM
			@TP@accounting
		WHERE
215
			Username = %{user.Username}
216
217
			AND AcctSessionID = %{request.Acct-Session-Id}
			AND NASIPAddress = %{request.NAS-IP-Address}
218
			AND NASPort = %{request.NAS-Port}
219
220
221
222
223
224
225
226
227
228
229
230
231
			AND PeriodKey = %{query.PeriodKey}
		ORDER BY
			ID
			LIMIT 99 OFFSET 1
	';

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

232
233
234
	$config->{'accounting_usage_cache_time'} = 300;


Nigel Kukard's avatar
Nigel Kukard committed
235
236
237
238
239
	# 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 "") {
240
241
242
243
244
245
			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
246
		}
247
248
249
250
251
252
253
254
255
		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
256
257
		if (defined($scfg->{'mod_accounting_sql'}->{'accounting_update_query'}) &&
				$scfg->{'mod_accounting_sql'}->{'accounting_update_query'} ne "") {
258
259
260
261
262
263
			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
264
		}
265
266
267
268
269
270
271
272
273
		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'};
			}
		}
274
275
276
277
278
		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'}});
279
			} else {
280
				$config->{'accounting_usage_query'} = $scfg->{'mod_accounting_sql'}->{'accounting_usage_query'};
281
			}
282
		}
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
		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'};
			}
		}
301
302
303
304
		if (defined($scfg->{'mod_accounting_sql'}->{'accounting_usage_cache_time'})) {
			if ($scfg->{'mod_accounting_sql'}{'accounting_usage_cache_time'} =~ /^\s*(yes|true|1)\s*$/i) {
				# Default?
			} elsif ($scfg->{'mod_accounting_sql'}{'accounting_usage_cache_time'} =~ /^\s*(no|false|0)\s*$/i) {
305
				$config->{'accounting_usage_cache_time'} = undef;
306
			} elsif ($scfg->{'mod_accounting_sql'}{'accounting_usage_cache_time'} =~ /^[0-9]+$/) {
307
				$config->{'accounting_usage_cache_time'} = $scfg->{'mod_accounting_sql'}{'accounting_usage_cache_time'};
308
309
310
311
312
313
314
			} 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
315
	if (defined($config->{'accounting_usage_cache_time'})) {
316
		$server->log(LOG_NOTICE,"[MOD_ACCOUNTING_SQL] getUsage caching ENABLED, cache time is %ds.",
Robert Anderson's avatar
Robert Anderson committed
317
				$config->{'accounting_usage_cache_time'});
318
319
	} else {
		$server->log(LOG_NOTICE,"[MOD_ACCOUNTING_SQL] getUsage caching DISABLED");
Nigel Kukard's avatar
Nigel Kukard committed
320
321
322
323
	}
}


324
325
326
# Function to get radius user data usage
sub getUsage
{
327
	my ($server,$user,$packet) = @_;
328
329
330
331

	# Build template
	my $template;
	foreach my $attr ($packet->attributes) {
332
		$template->{'request'}->{$attr} = $packet->rawattr($attr)
333
	}
334
335
336
337

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

	# Current PeriodKey
340
	my $now = DateTime->now->set_time_zone($server->{'smradius'}->{'event_timezone'});
341
	$template->{'query'}->{'PeriodKey'} = $now->strftime("%Y-%m");
342

343
344
	# If we using caching, check how old the result is
	if (defined($config->{'accounting_usage_cache_time'})) {
Nigel Kukard's avatar
Nigel Kukard committed
345
346
		my ($res,$val) = cacheGetComplexKeyPair('mod_accounting_sql(getUsage)',$user->{'Username'}."/".
				$template->{'query'}->{'PeriodKey'});
347
		if (defined($val) && $val->{'CachedUntil'} > $user->{'_Internal'}->{'Timestamp-Unix'}) {
348
349
350
351
			return $val;
		}
	}

352
	# Replace template entries
353
	my (@dbDoParams) = templateReplace($config->{'accounting_usage_query'},$template);
354
355
356
357

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

362
363
	# Our usage hash
	my %usageTotals;
364
365
366
	$usageTotals{'TotalSessionTime'} = Math::BigInt->new();
	$usageTotals{'TotalDataInput'} = Math::BigInt->new();
	$usageTotals{'TotalDataOutput'} = Math::BigInt->new();
367

368
369
370
371
	# Pull in usage and add up
	while (my $row = hashifyLCtoMC($sth->fetchrow_hashref(),
			qw(AcctSessionTime AcctInputOctets AcctInputGigawords AcctOutputOctets AcctOutputGigawords)
	)) {
372

373
374
		# Look for session time
		if (defined($row->{'AcctSessionTime'}) && $row->{'AcctSessionTime'} > 0) {
375
			$usageTotals{'TotalSessionTime'}->badd($row->{'AcctSessionTime'});
376
377
378
		}
		# Add input usage if we have any
		if (defined($row->{'AcctInputOctets'}) && $row->{'AcctInputOctets'} > 0) {
379
			$usageTotals{'TotalDataInput'}->badd($row->{'AcctInputOctets'});
380
381
		}
		if (defined($row->{'AcctInputGigawords'}) && $row->{'AcctInputGigawords'} > 0) {
382
383
384
			my $inputGigawords = Math::BigInt->new($row->{'AcctInputGigawords'});
			$inputGigawords->bmul(UINT_MAX);
			$usageTotals{'TotalDataInput'}->badd($inputGigawords);
385
386
387
		}
		# Add output usage if we have any
		if (defined($row->{'AcctOutputOctets'}) && $row->{'AcctOutputOctets'} > 0) {
388
			$usageTotals{'TotalDataOutput'}->badd($row->{'AcctOutputOctets'});
389
390
		}
		if (defined($row->{'AcctOutputGigawords'}) && $row->{'AcctOutputGigawords'} > 0) {
391
392
393
			my $outputGigawords = Math::BigInt->new($row->{'AcctOutputGigawords'});
			$outputGigawords->bmul(UINT_MAX);
			$usageTotals{'TotalDataOutput'}->badd($outputGigawords);
394
		}
395
	}
396
	DBFreeRes($sth);
397

398
399
400
401
402
403
	# Convert to bigfloat for accuracy
	my $totalData = Math::BigFloat->new();
	$totalData->badd($usageTotals{'TotalDataOutput'})->badd($usageTotals{'TotalDataInput'});
	my $totalTime = Math::BigFloat->new();
	$totalTime->badd($usageTotals{'TotalSessionTime'});

Robert Anderson's avatar
Robert Anderson committed
404
	# Rounding up
405
	my %res;
Robert Anderson's avatar
Robert Anderson committed
406
407
	$res{'TotalDataUsage'} = $totalData->bdiv(1024)->bdiv(1024)->bceil()->bstr();
	$res{'TotalSessionTime'} = $totalTime->bdiv(60)->bceil()->bstr();
408

409
410
	# 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
411
		$res{'CachedUntil'} = $user->{'_Internal'}->{'Timestamp-Unix'} + $config->{'accounting_usage_cache_time'};
412

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

417
	return \%res;
418
419
420
}


Nigel Kukard's avatar
Nigel Kukard committed
421
422
423
424
425
426
427
428
429
430
431
432
## @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) = @_;

433

Nigel Kukard's avatar
Nigel Kukard committed
434
435
436
	# Build template
	my $template;
	foreach my $attr ($packet->attributes) {
437
		$template->{'request'}->{$attr} = $packet->rawattr($attr);
Nigel Kukard's avatar
Nigel Kukard committed
438
	}
439
440
	# Fix event timestamp
	$template->{'request'}->{'Timestamp'} = $user->{'_Internal'}->{'Timestamp'};
441

442
443
444
	# Add user details
	$template->{'user'}->{'ID'} = $user->{'ID'};
	$template->{'user'}->{'Username'} = $user->{'Username'};
Nigel Kukard's avatar
Nigel Kukard committed
445

446
	# Current PeriodKey
447
	my $now = DateTime->now->set_time_zone($server->{'smradius'}->{'event_timezone'});
448
449
450
451
	my $periodKey = $now->strftime("%Y-%m");

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

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

456
	#
Robert Anderson's avatar
Robert Anderson committed
457
	# U P D A T E   &   S T O P   P A C K E T
458
	#
459
	if ($packet->rawattr('Acct-Status-Type') eq "2" || $packet->rawattr('Acct-Status-Type') eq "3") {
Robert Anderson's avatar
   
Robert Anderson committed
460
		# Replace template entries
461
		my @dbDoParams = templateReplace($config->{'accounting_update_get_records_query'},$template);
Robert Anderson's avatar
   
Robert Anderson committed
462

463
464
		# Fetch previous records of the same session
		my $sth = DBSelect(@dbDoParams);
Robert Anderson's avatar
   
Robert Anderson committed
465
		if (!$sth) {
466
			$server->log(LOG_ERR,"[MOD_ACCOUNTING_SQL] Database query failed: %s",AWITPT::DB::DBLayer::error());
467
468
469
470
471
472
473
474
475
476
			return;
		}

		# Convert session total gigawords/octets into bytes
		my $totalInputBytes = Math::BigInt->new();
		$totalInputBytes->badd($template->{'request'}->{'Acct-Input-Gigawords'})->bmul(UINT_MAX);
		$totalInputBytes->badd($template->{'request'}->{'Acct-Input-Octets'});
		my $totalOutputBytes = Math::BigInt->new();
		$totalOutputBytes->badd($template->{'request'}->{'Acct-Output-Gigawords'})->bmul(UINT_MAX);
		$totalOutputBytes->badd($template->{'request'}->{'Acct-Output-Octets'});
477
478
479
480
481
		# 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'});
482
483

		# Loop through previous records and subtract them from our session totals
Nigel Kukard's avatar
Nigel Kukard committed
484
		while (my $sessionPart = hashifyLCtoMC($sth->fetchrow_hashref(),
485
486
				qw(AcctInputOctets AcctInputPackets AcctOutputOctets AcctOutputPackets AcctInputGigawords AcctOutputGigawords
					SessionTime PeriodKey)
Nigel Kukard's avatar
Nigel Kukard committed
487
		)) {
488
489
490

			# Convert this session usage to bytes
			my $sessionInputBytes = Math::BigInt->new();
491
492
			$sessionInputBytes->badd($sessionPart->{'AcctInputGigawods'})->bmul(UINT_MAX);
			$sessionInputBytes->badd($sessionPart->{'AcctInputOctets'});
493
			my $sessionOutputBytes = Math::BigInt->new();
494
495
			$sessionOutputBytes->badd($sessionPart->{'AcctOutputGigawods'})->bmul(UINT_MAX);
			$sessionOutputBytes->badd($sessionPart->{'AcctOutputOctets'});
496
			# And packets
497
498
			my $sessionInputPackets = Math::BigInt->new($sessionPart->{'AcctInputPackets'});
			my $sessionOutputPackets = Math::BigInt->new($sessionPart->{'AcctOutputPackets'});
499
			# Finally session time
500
			my $sessionSessionTime = Math::BigInt->new($sessionPart->{'AcctSessionTime'});
501
502
503
504

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

505
				# Subtract from our total, we can hit NEG!!! ... we check for that below
506
507
				$totalInputBytes->bsub($sessionInputBytes);
				$totalOutputBytes->bsub($sessionOutputBytes);
508
509
510
				$totalInputPackets->bsub($sessionInputPackets);
				$totalOutputPackets->bsub($sessionOutputPackets);
				$totalSessionTime->bsub($sessionSessionTime);
511
512

				# We need to continue this session in a new entry
Robert Anderson's avatar
Robert Anderson committed
513
				$newPeriod = 1;
514
			}
Robert Anderson's avatar
   
Robert Anderson committed
515
		}
516
		DBFreeRes($sth);
Nigel Kukard's avatar
Nigel Kukard committed
517

518
		# Sanitize
519
		if ($totalInputBytes->is_neg()) {
520
521
			$totalInputBytes->bzero();
		}
522
		if ($totalOutputBytes->is_neg()) {
523
524
			$totalOutputBytes->bzero();
		}
525
		if ($totalInputPackets->is_neg()) {
526
527
			$totalInputPackets->bzero();
		}
528
		if ($totalOutputPackets->is_neg()) {
529
530
			$totalOutputPackets->bzero();
		}
531
		if ($totalSessionTime->is_neg()) {
532
533
534
			$totalSessionTime->bzero();
		}

535
536
537
538
539
		# Re-calculate
		my ($inputGigawordsStr,$inputOctetsStr) = $totalInputBytes->bdiv(UINT_MAX);
		my ($outputGigawordsStr,$outputOctetsStr) = $totalOutputBytes->bdiv(UINT_MAX);

		# Conversion to strings
540
541
542
543
		$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();
544

545
546
		$template->{'query'}->{'Acct-Input-Packets'} = $totalInputPackets->bstr();
		$template->{'query'}->{'Acct-Output-Packets'} = $totalOutputPackets->bstr();
547

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

550

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

Robert Anderson's avatar
Robert Anderson committed
554
555
556
557
		# Update database
		$sth = DBDo(@dbDoParams);
		if (!$sth) {
			$server->log(LOG_ERR,"[MOD_ACCOUNTING_SQL] Failed to update accounting ALIVE record: ".
558
					AWITPT::DB::DBLayer::error());
Robert Anderson's avatar
Robert Anderson committed
559
560
			return MOD_RES_NACK;
		}
561

Robert Anderson's avatar
Robert Anderson committed
562
563
564
565
566
		# 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?
567
			if ($sth > 1) {
568
569
				fixDuplicates($server, $template);
			}
570
		}
Robert Anderson's avatar
Robert Anderson committed
571
572
	}

573
574

	#
Robert Anderson's avatar
Robert Anderson committed
575
576
577
	# 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
578
579
	#

580
	if ($packet->rawattr('Acct-Status-Type') eq "1" || $newPeriod) {
Robert Anderson's avatar
   
Robert Anderson committed
581
		# Replace template entries
Robert Anderson's avatar
Robert Anderson committed
582
583
584
		my @dbDoParams = templateReplace($config->{'accounting_start_query'},$template);
		# Insert into database
		my $sth = DBDo(@dbDoParams);
585
		if (!$sth) {
Robert Anderson's avatar
Robert Anderson committed
586
			$server->log(LOG_ERR,"[MOD_ACCOUNTING_SQL] Failed to insert accounting START record: ".
587
					AWITPT::DB::DBLayer::error());
588
589
			return MOD_RES_NACK;
		}
590
591
592
593
594
595
596
597
598
		# 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
599
	}
600

601

Robert Anderson's avatar
Robert Anderson committed
602
603
604
	#
	# S T O P   P A C K E T   specifics
	#
605

606
	if ($packet->rawattr('Acct-Status-Type') eq "2") {
607
608

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

		# Update database (status)
Robert Anderson's avatar
Robert Anderson committed
612
		my $sth = DBDo(@dbDoParams);
Robert Anderson's avatar
   
Robert Anderson committed
613
		if (!$sth) {
614
			$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
615
616
			return MOD_RES_NACK;
		}
Nigel Kukard's avatar
Nigel Kukard committed
617
618
	}

619

Nigel Kukard's avatar
Nigel Kukard committed
620
621
622
623
	return MOD_RES_ACK;
}


624
625
626
627
628
629
630
631
632
633
634
635
# 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) {
636
		$server->log(LOG_ERR,"[MOD_ACCOUNTING_SQL] Database query failed: %s",AWITPT::DB::DBLayer::error());
637
638
639
640
641
		return;
	}

	# Pull in duplicates
	my @IDList;
Nigel Kukard's avatar
Nigel Kukard committed
642
	while (my $duplicates = hashifyLCtoMC($sth->fetchrow_hashref(), qw(ID))) {
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
		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) {
659
			$server->log(LOG_ERR,"[MOD_ACCOUNTING_SQL] Database query failed: %s",AWITPT::DB::DBLayer::error());
660
661
662
663
664
665
666
667
668
669
670
671
672
673
			DBRollback();
			return;
		}
	}

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


	return
}


674
675
676
# Add up totals function
sub cleanup
{
677
	my ($server,$runForDate) = @_;
678
679


680
681
682
683
684
685
686
687
688
	# 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");
689

690
691
692
693
694
695
696

	# Begin transaction
	DBBegin();

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

	# Delete duplicate records
697
	# NK: MYSQL SPECIFIC
698
699
700
701
702
703
704
705
706
	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: ".
707
				AWITPT::DB::DBLayer::error());
708
709
		DBRollback();
		return;
710
711
	}

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

714
	# Select totals for last month
715
	$sth = DBSelect('
716
717
		SELECT
			Username,
718
719
720
721
722
			AcctSessionTime,
			AcctInputOctets,
			AcctInputGigawords,
			AcctOutputOctets,
			AcctOutputGigawords
723
724
725
		FROM
			@TP@accounting
		WHERE
726
			PeriodKey = ?
727
		',
728
		$prevPeriodKey
729
730
731
	);
	if (!$sth) {
		$server->log(LOG_ERR,"[MOD_ACCOUNTING_SQL] Cleanup => Failed to select accounting record: ".
732
				AWITPT::DB::DBLayer::error());
733
734
735
736
		return;
	}

	# Load items into array
737
738
	my %usageTotals;
	while (my $row = hashifyLCtoMC($sth->fetchrow_hashref(),
739
			qw(Username AcctSessionTime AcctInputOctets AcctInputGigawords AcctOutputOctets AcctOutputGigawords)
740
741
742
743
744
745
	)) {

		# 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) {
746
				$usageTotals{$row->{'Username'}}{'TotalSessionTime'}->badd($row->{'AcctSessionTime'});
747
748
749
			}
			# Add input usage if we have any
			if (defined($row->{'AcctInputOctets'}) && $row->{'AcctInputOctets'} > 0) {
750
				$usageTotals{$row->{'Username'}}{'TotalDataInput'}->badd($row->{'AcctInputOctets'});
751
752
			}
			if (defined($row->{'AcctInputGigawords'}) && $row->{'AcctInputGigawords'} > 0) {
753
754
755
				my $inputGigawords = Math::BigInt->new($row->{'AcctInputGigawords'});
				$inputGigawords->bmul(UINT_MAX);
				$usageTotals{$row->{'Username'}}{'TotalDataInput'}->badd($inputGigawords);
756
757
758
			}
			# Add output usage if we have any
			if (defined($row->{'AcctOutputOctets'}) && $row->{'AcctOutputOctets'} > 0) {
759
				$usageTotals{$row->{'Username'}}{'TotalDataOutput'}->badd($row->{'AcctOutputOctets'});
760
761
			}
			if (defined($row->{'AcctOutputGigawords'}) && $row->{'AcctOutputGigawords'} > 0) {
762
763
764
				my $outputGigawords = Math::BigInt->new($row->{'AcctOutputGigawords'});
				$outputGigawords->bmul(UINT_MAX);
				$usageTotals{$row->{'Username'}}{'TotalDataOutput'}->badd($outputGigawords);
765
766
767
768
			}

		# This is a new record...
		} else {
769
770
771
772
773
774

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

775
776
			# Look for session time
			if (defined($row->{'AcctSessionTime'}) && $row->{'AcctSessionTime'} > 0) {
777
				$usageTotals{$row->{'Username'}}{'TotalSessionTime'}->badd($row->{'AcctSessionTime'});
778
			}
779
			# Add input usage if we have any
780
			if (defined($row->{'AcctInputOctets'}) && $row->{'AcctInputOctets'} > 0) {
781
				$usageTotals{$row->{'Username'}}{'TotalDataInput'}->badd($row->{'AcctInputOctets'});
782
783
			}
			if (defined($row->{'AcctInputGigawords'}) && $row->{'AcctInputGigawords'} > 0) {
784
785
786
				my $inputGigawords = Math::BigInt->new($row->{'AcctInputGigawords'});
				$inputGigawords->bmul(UINT_MAX);
				$usageTotals{$row->{'Username'}}{'TotalDataInput'}->badd($inputGigawords);
787
			}
788
			# Add output usage if we have any
789
			if (defined($row->{'AcctOutputOctets'}) && $row->{'AcctOutputOctets'} > 0) {
790
				$usageTotals{$row->{'Username'}}{'TotalDataOutput'}->badd($row->{'AcctOutputOctets'});
791
792
			}
			if (defined($row->{'AcctOutputGigawords'}) && $row->{'AcctOutputGigawords'} > 0) {
793
794
795
				my $outputGigawords = Math::BigInt->new($row->{'AcctOutputGigawords'});
				$outputGigawords->bmul(UINT_MAX);
				$usageTotals{$row->{'Username'}}{'TotalDataOutput'}->badd($outputGigawords);
796
			}
797

798
		}
799
800
	}

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

803
804
	# Loop through users and insert totals
	foreach my $username (keys %usageTotals) {
805
806
807
808
809
810
811
812

		# 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
813
814
815
		$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();
816

817
818
		# Do query
		$sth = DBDo('
819
			INSERT INTO
820
				@TP@accounting_summary
821
822
823
824
825
826
827
828
829
830
831
			(
				Username,
				PeriodKey,
				TotalSessionTime,
				TotalInput,
				TotalOutput
			)
			VALUES
				(?,?,?,?,?)
			',
			$username,
832
			$prevPeriodKey,
833
834
835
			$res->{'TotalSessionTime'},
			$res->{'TotalDataInput'},
			$res->{'TotalDataOutput'}
836
		);
837
838
		if (!$sth) {
			$server->log(LOG_ERR,"[MOD_ACCOUNTING_SQL] Cleanup => Failed to create accounting summary record: ".
839
					AWITPT::DB::DBLayer::error());
840
841
			DBRollback();
			return;
842
843
		}

844
845
846
847
		# 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'});
848
849
850
851
	}

	# Commit if succeeded
	DBCommit();
852
	$server->log(LOG_NOTICE,"[MOD_ACCOUNTING_SQL] Cleanup => Accounting summaries created");
853
854
855
}


Nigel Kukard's avatar
Nigel Kukard committed
856

Nigel Kukard's avatar
Nigel Kukard committed
857
1;
Nigel Kukard's avatar
Nigel Kukard committed
858
# vim: ts=4