mod_config_sql_topups.pm 31.4 KB
Newer Older
1
# Topup support
Nigel Kukard's avatar
Nigel Kukard committed
2 3
# Copyright (C) 2007-2016, AllWorldIT
#
Robert Anderson's avatar
Robert Anderson 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.
Nigel Kukard's avatar
Nigel Kukard committed
8
#
Robert Anderson's avatar
Robert Anderson 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.
Nigel Kukard's avatar
Nigel Kukard committed
13
#
Robert Anderson's avatar
Robert Anderson 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.

18
package smradius::modules::system::mod_config_sql_topups;
Robert Anderson's avatar
Robert Anderson committed
19 20 21 22 23 24 25

use strict;
use warnings;

# Modules we need
use smradius::constants;
use smradius::logging;
26 27
use AWITPT::DB::DBLayer;
use AWITPT::Util;
Robert Anderson's avatar
Robert Anderson committed
28 29
use smradius::util;
use smradius::attributes;
30

Nigel Kukard's avatar
Nigel Kukard committed
31
use POSIX qw(ceil);
32 33
use DateTime;
use Date::Parse;
34 35
use Math::BigInt;
use Math::BigFloat;
36 37


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



# Plugin info
our $pluginInfo = {
49
	Name => "SQL Topup Config",
Robert Anderson's avatar
Robert Anderson committed
50
	Init => \&init,
51 52

	# Cleanup run by smadmin
Nigel Kukard's avatar
Nigel Kukard committed
53
	CleanupOrder => 80,
54 55
	Cleanup => \&cleanup,

Robert Anderson's avatar
Robert Anderson committed
56 57 58 59 60 61 62
	# User database
	Config_get => \&getTopups
};

# Module config
my $config;

63 64 65 66 67 68 69

# Helper functions
sub _isTrafficTopup { my $val = shift; return ($val & 1) == 1; }
sub _isUptimeTopup { my $val = shift; return ($val & 2) == 2; }
sub _isAutoTopup { my $val = shift; return ($val & 4) == 4; }


Robert Anderson's avatar
Robert Anderson committed
70 71 72 73 74 75 76 77 78 79 80 81 82 83 84
## @internal
# Initialize module
sub init
{
	my $server = shift;
	my $scfg = $server->{'inifile'};


	# Enable support for database
	if (!$server->{'smradius'}->{'database'}->{'enabled'}) {
		$server->log(LOG_NOTICE,"[MOD_USERDB_SQL] Enabling database support.");
		$server->{'smradius'}->{'database'}->{'enabled'} = 1;
	}

	# Default configs...
85
	$config->{'get_topups_summary_query'} = '
86
		SELECT
87
			@TP@topups_summary.Balance,
88 89
			@TP@topups.Type,
			@TP@topups.ID
90
		FROM
91 92 93 94 95 96 97 98 99 100 101
			@TP@topups_summary,
			@TP@topups,
			@TP@users
		WHERE
			@TP@topups.ID = @TP@topups_summary.TopupID
			AND @TP@topups.UserID = @TP@users.ID
			AND @TP@topups_summary.PeriodKey = ?
			AND @TP@topups.Depleted = 0
			AND @TP@users.Username = ?
	';

Robert Anderson's avatar
Robert Anderson committed
102
	$config->{'get_topups_query'} = '
103
		SELECT
104
			@TP@topups.ID,
105 106
			@TP@topups.Type,
			@TP@topups.Value
107
		FROM
108 109
			@TP@topups,
			@TP@users
Robert Anderson's avatar
Robert Anderson committed
110
		WHERE
111
			@TP@topups.UserID = @TP@users.ID
112
			AND @TP@topups.ValidFrom = ?
113 114 115
			AND @TP@topups.ValidTo >= ?
			AND @TP@topups.Depleted = 0
			AND @TP@users.Username = ?
Robert Anderson's avatar
Robert Anderson committed
116
	';
Nigel Kukard's avatar
Nigel Kukard committed
117

Robert Anderson's avatar
Robert Anderson committed
118 119

	# Setup SQL queries
120
	if (defined($scfg->{'mod_config_sql_topups'})) {
Robert Anderson's avatar
Robert Anderson committed
121
		# Pull in queries
122 123 124 125
		if (defined($scfg->{'mod_config_sql_topups'}->{'get_topups_summary_query'}) &&
				$scfg->{'mod_config_sql_topups'}->{'get_topups_summary_query'} ne "") {
			if (ref($scfg->{'mod_config_sql_topups'}->{'get_topups_summary_query'}) eq "ARRAY") {
				$config->{'get_topups_summary_query'} = join(' ',@{$scfg->{'mod_config_sql_topups'}->{'get_topups_summary_query'}});
126
			} else {
127
				$config->{'get_topups_summary_query'} = $scfg->{'mod_config_sql_topups'}->{'get_topups_summary_query'};
128 129 130
			}
		}

131 132 133 134
		if (defined($scfg->{'mod_config_sql_topups'}->{'get_topups_query'}) &&
				$scfg->{'mod_config_sql_topups'}->{'get_topups_query'} ne "") {
			if (ref($scfg->{'mod_config_sql_topups'}->{'get_topups_query'}) eq "ARRAY") {
				$config->{'get_topups_query'} = join(' ',@{$scfg->{'mod_config_sql_topups'}->{'get_topups_query'}});
Robert Anderson's avatar
Robert Anderson committed
135
			} else {
136
				$config->{'get_topups_query'} = $scfg->{'mod_config_sql_topups'}->{'get_topups_query'};
Robert Anderson's avatar
Robert Anderson committed
137
			}
Nigel Kukard's avatar
Nigel Kukard committed
138

Robert Anderson's avatar
Robert Anderson committed
139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155
		}
	}
}


## @getTopups
# Try to get topup information
#
# @param server Server object
# @param user User
# @param packet Radius packet
#
# @return Result
sub getTopups
{
	my ($server,$user,$packet) = @_;

156

157 158 159 160 161 162
	# Fetch all summaries
	my $trafficSummaries = {
		'traffic' => 0,
		'uptime' => 0,
		'traffic-topup' => 0,
		'uptime-topup' => 0,
163 164
		'traffic-auto' => 0,
		'uptime-auto' => 0,
165 166
	};

167
	# Check to see if we have a username
168
	my $username = $user->{'Username'};
169 170 171 172 173 174

	# Skip this module if we don't have a username
	if (!defined($username)) {
		return MOD_RES_SKIP;
	}

175 176 177 178 179 180 181
	# Make time for month begin
	my $now = DateTime->from_epoch( epoch => $user->{'_Internal'}->{'Timestamp-Unix'} );
	my $thisMonth = DateTime->new( year => $now->year, month => $now->month, day => 1 );

	# Format period key
	my $periodKey = $thisMonth->strftime("%Y-%m");

Robert Anderson's avatar
Robert Anderson committed
182
	# Query database
183
	my $sth = DBSelect($config->{'get_topups_summary_query'},$periodKey,$username);
Robert Anderson's avatar
Robert Anderson committed
184
	if (!$sth) {
Nigel Kukard's avatar
Nigel Kukard committed
185
		$server->log(LOG_ERR,"Failed to get topup information: %s",AWITPT::DB::DBLayer::Error());
Robert Anderson's avatar
Robert Anderson committed
186 187
		return MOD_RES_NACK;
	}
Nigel Kukard's avatar
Nigel Kukard committed
188
	while (my $row = hashifyLCtoMC($sth->fetchrow_hashref(), qw(Balance Type ID))) {
189
		_trafficSummaryAdd($trafficSummaries,$row,'Balance');
Robert Anderson's avatar
Robert Anderson committed
190
	}
191 192 193
	DBFreeRes($sth);

	# Query database
194
	$sth = DBSelect($config->{'get_topups_query'},$thisMonth->ymd,$now->ymd,$username);
195
	if (!$sth) {
Nigel Kukard's avatar
Nigel Kukard committed
196
		$server->log(LOG_ERR,"Failed to get topup information: %s",AWITPT::DB::DBLayer::Error());
197 198
		return MOD_RES_NACK;
	}
199
	# Fetch all new topups
200
	my (@trafficTopups,@uptimeTopups);
Nigel Kukard's avatar
Nigel Kukard committed
201
	while (my $row = hashifyLCtoMC($sth->fetchrow_hashref(), qw(ID Type Value))) {
202
		_trafficSummaryAdd($trafficSummaries,$row,'Value');
203
	}
Robert Anderson's avatar
Robert Anderson committed
204 205
	DBFreeRes($sth);

206
	# Save configuration for the user
207
	processConfigAttribute($server,$user,{ 'Name' => 'SMRadius-Capping-Traffic-Topup',
208
			'Operator' => ':=', 'Value' => $trafficSummaries->{'traffic'} });
209
	processConfigAttribute($server,$user,{ 'Name' => 'SMRadius-Capping-Uptime-Topup',
210 211 212 213 214
			'Operator' => ':=', 'Value' => $trafficSummaries->{'uptime'} });
	processConfigAttribute($server,$user,{ 'Name' => 'SMRadius-Capping-Traffic-AutoTopup',
			'Operator' => ':=', 'Value' => $trafficSummaries->{'traffic-auto'} });
	processConfigAttribute($server,$user,{ 'Name' => 'SMRadius-Capping-Uptime-AutoTopup',
			'Operator' => ':=', 'Value' => $trafficSummaries->{'uptime-auto'} });
215

Robert Anderson's avatar
Robert Anderson committed
216 217 218 219
	return MOD_RES_ACK;
}


220
# Topup summary function
221 222
sub cleanup
{
Nigel Kukard's avatar
Nigel Kukard committed
223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246
	my ($server,$runForDate) = @_;

	# 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" );
	# Format this month period key
	my $curPeriodKey = $thisMonth->strftime("%Y-%m");

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

	# Next month..
	my $nextMonth = $thisMonth->clone()->add( months => 1 );
	my $unix_nextMonth = $nextMonth->epoch();

	# Get a timestamp for this user
	my $depletedTimestamp = $now->strftime('%Y-%m-%d %H:%M:%S');



	$server->log(LOG_NOTICE,"[MOD_CONFIG_SQL_TOPUPS] Cleanup => Generating list of users");
247 248 249

	# TODO - be more dynamic, we may not be using SQL users
	# Get all usernames
250
	my $sth = DBSelect('SELECT ID, Username FROM @TP@users');
251 252

	if (!$sth) {
Nigel Kukard's avatar
Nigel Kukard committed
253
		$server->log(LOG_ERR,"[MOD_CONFIG_SQL_TOPUPS] Cleanup => Failed to select users: ".
254
				AWITPT::DB::DBLayer::Error());
255 256 257 258 259
		return;
	}

	# Create hash of usernames
	my %users;
Nigel Kukard's avatar
Nigel Kukard committed
260
	while (my $user = hashifyLCtoMC($sth->fetchrow_hashref(), qw(ID Username))) {
261
		$users{$user->{'ID'}} = $user->{'Username'};
262 263 264 265
	}

	# Finished for now
	DBFreeRes($sth);
Nigel Kukard's avatar
Nigel Kukard committed
266

Nigel Kukard's avatar
Nigel Kukard committed
267 268
	# Start of multiple queries
	DBBegin();
269

Nigel Kukard's avatar
Nigel Kukard committed
270
	$server->log(LOG_NOTICE,"[MOD_CONFIG_SQL_TOPUPS] Cleanup => Removing all old topup summaries");
271

Nigel Kukard's avatar
Nigel Kukard committed
272
	# Remove topup summaries
273
	# NK: MYSQL SPECIFIC
Nigel Kukard's avatar
Nigel Kukard committed
274 275 276 277 278 279 280 281 282
	$sth = DBDo('
		DELETE FROM
			@TP@topups_summary
		WHERE
			STR_TO_DATE(PeriodKey,"%Y-%m") >= ?',
		$curPeriodKey
	);
	if (!$sth) {
		$server->log(LOG_ERR,"[MOD_CONFIG_SQL_TOPUPS] Cleanup => Failed to delete topup summaries: ".
283
				AWITPT::DB::DBLayer::Error());
Nigel Kukard's avatar
Nigel Kukard committed
284 285 286
		DBRollback();
		return;
	}
287

Nigel Kukard's avatar
Nigel Kukard committed
288 289 290 291 292 293 294 295 296 297 298 299
	# Undeplete topups
	$sth = DBDo('
		UPDATE
			@TP@topups
		SET
			Depleted = 0,
			SMAdminDepletedOn = NULL
		WHERE
			SMAdminDepletedOn >= ?', $thisMonth->ymd()
	);
	if (!$sth) {
		$server->log(LOG_ERR,"[MOD_CONFIG_SQL_TOPUPS] Cleanup => Failed to undeplete topups: ".
300
				AWITPT::DB::DBLayer::Error());
Nigel Kukard's avatar
Nigel Kukard committed
301 302 303
		DBRollback();
		return;
	}
304

Nigel Kukard's avatar
Nigel Kukard committed
305
	$server->log(LOG_NOTICE,"[MOD_CONFIG_SQL_TOPUPS] Cleanup => Retrieving accounting summaries");
306

Nigel Kukard's avatar
Nigel Kukard committed
307 308 309 310 311 312 313 314 315 316 317 318
	# Undeplete topup summaries
	$sth = DBDo('
		UPDATE
			@TP@topups_summary
		SET
			Depleted = 0,
			SMAdminDepletedOn = NULL
		WHERE
			SMAdminDepletedOn >= ?', $thisMonth->ymd()
	);
	if (!$sth) {
		$server->log(LOG_ERR,"[MOD_CONFIG_SQL_TOPUPS] Cleanup => Failed to retrieve accounting summaries: ".
319
				AWITPT::DB::DBLayer::Error());
Nigel Kukard's avatar
Nigel Kukard committed
320 321 322
		DBRollback();
		return;
	}
323

324
	# Loop through users
325
	foreach my $userID (keys %users) {
326
		my $username = $users{$userID};
327 328

		# TODO - in future we must be more dynamic, we may not be using SQL accunting
329 330 331

		# Get traffic and uptime usage for last month
		my $sth = DBSelect('
332
			SELECT
Nigel Kukard's avatar
Nigel Kukard committed
333 334 335
				TotalInput,
				TotalOutput,
				TotalSessionTime
336
			FROM
Nigel Kukard's avatar
Nigel Kukard committed
337
				@TP@accounting_summary
338
			WHERE
339
				PeriodKey = ?
340 341
				AND Username = ?
			',
342
			$prevPeriodKey,$username
343 344
		);
		if (!$sth) {
Nigel Kukard's avatar
Nigel Kukard committed
345
			$server->log(LOG_ERR,"[MOD_CONFIG_SQL_TOPUPS] Cleanup => Failed to select accounting summary record: ".
346
					AWITPT::DB::DBLayer::Error());
347 348 349
			goto FAIL_ROLLBACK;
		}

350 351
		# Our usage hash
		my %usageTotals;
352
		$usageTotals{'TotalSessionTime'} = Math::BigInt->new();
Nigel Kukard's avatar
Nigel Kukard committed
353
		$usageTotals{'TotalDataUsage'} = Math::BigInt->new();
354

355
		# Pull in usage and add up
Nigel Kukard's avatar
Nigel Kukard committed
356 357
		if (my $row = hashifyLCtoMC($sth->fetchrow_hashref(),
				qw(TotalSessionTime TotalInput TotalOutput)
358
		)) {
359

360
			# Look for session time
Nigel Kukard's avatar
Nigel Kukard committed
361 362
			if (defined($row->{'TotalSessionTime'}) && $row->{'TotalSessionTime'} > 0) {
				$usageTotals{'TotalSessionTime'}->badd($row->{'TotalSessionTime'});
363 364
			}
			# Add input usage if we have any
Nigel Kukard's avatar
Nigel Kukard committed
365 366
			if (defined($row->{'TotalInput'}) && $row->{'TotalInput'} > 0) {
				$usageTotals{'TotalDataUsage'}->badd($row->{'TotalInput'});
367 368
			}
			# Add output usage if we have any
Nigel Kukard's avatar
Nigel Kukard committed
369 370
			if (defined($row->{'TotalOutput'}) && $row->{'TotalOutput'} > 0) {
				$usageTotals{'TotalDataUsage'}->badd($row->{'TotalOutput'});
371
			}
372
		}
373
		DBFreeRes($sth);
374

Nigel Kukard's avatar
Nigel Kukard committed
375
		# Log the summary
Nigel Kukard's avatar
Nigel Kukard committed
376 377 378
		$server->log(LOG_NOTICE,"[MOD_CONFIG_SQL_TOPUPS] Cleanup => Username '%s', PeriodKey '%s', TotalSessionTime '%s', ".
				" TotalDataUsage '%s'",$username,$prevPeriodKey,$usageTotals{'TotalSessionTime'}->bstr(),
				$usageTotals{'TotalDataUsage'}->bstr(),	$usageTotals{'TotalDataUsage'}->bstr());
379

380 381 382 383
		# Get user traffic and uptime limits from group attributes
		# FIXME - Support for realm config
		$sth = DBSelect('
			SELECT
384
				@TP@group_attributes.Name, @TP@group_attributes.Operator, @TP@group_attributes.Value
385
			FROM
386
				@TP@group_attributes, @TP@users_to_groups
387 388
			WHERE
				@TP@group_attributes.GroupID = @TP@users_to_groups.GroupID
389
				AND @TP@users_to_groups.UserID = ?
390
			',
391
			$userID
392
		);
393

394 395
		if (!$sth) {
			$server->log(LOG_ERR,"[MOD_CONFIG_SQL_TOPUPS] Cleanup => Failed to select group usage caps: ".
396
					AWITPT::DB::DBLayer::Error());
397 398
			goto FAIL_ROLLBACK;
		}
399

400 401
		# Store limits in capRecord hash
		my %capRecord;
Nigel Kukard's avatar
Nigel Kukard committed
402
		while (my $row = hashifyLCtoMC($sth->fetchrow_hashref(), qw(Name Operator Value))) {
403

404 405 406 407 408 409
			if (defined($row->{'Name'})) {
				if ($row->{'Name'} eq 'SMRadius-Capping-Traffic-Limit') {
					if (defined($row->{'Operator'}) && $row->{'Operator'} eq ':=') {
						if (defined($row->{'Value'}) && $row->{'Value'} =~ /^[\d]+$/) {
							$capRecord{'TrafficLimit'} = $row->{'Value'};
						} else {
Nigel Kukard's avatar
Nigel Kukard committed
410 411
							$server->log(LOG_ERR,"[MOD_CONFIG_SQL_TOPUPS] Cleanup => SMRadius-Capping-Traffic-Limit ".
									"value invalid for user '".$username."'");
412 413 414
						}
					} else {
						$server->log(LOG_ERR,"[MOD_CONFIG_SQL_TOPUPS] Cleanup => Incorrect '".$row->{'Name'}."' operator '"
415
								.$row->{'Operator'}."' used  for user '".$username."'");
416
					}
417
				}
418 419 420 421 422
				if ($row->{'Name'} eq 'SMRadius-Capping-Uptime-Limit') {
					if (defined($row->{'Operator'}) && $row->{'Operator'} eq ':=') {
						if (defined($row->{'Value'}) && $row->{'Value'} =~ /^[\d]+$/) {
							$capRecord{'UptimeLimit'} = $row->{'Value'};
						} else {
Nigel Kukard's avatar
Nigel Kukard committed
423 424
							$server->log(LOG_ERR,"[MOD_CONFIG_SQL_TOPUPS] Cleanup => SMRadius-Capping-Uptime-Limit value ".
							"invalid for user '".$username."'");
425 426 427
						}
					} else {
						$server->log(LOG_ERR,"[MOD_CONFIG_SQL_TOPUPS] Cleanup => Incorrect '".$row->{'Name'}."' operator '"
428
								.$row->{'Operator'}."' used  for user '".$username."'");
429
					}
430 431 432 433 434 435 436 437
				}
			}
		}

		# Finished for now
		DBFreeRes($sth);

		# Get user traffic and uptime limits from user attributes
438 439
		$sth = DBSelect('
			SELECT
440
				Name, Operator, Value
441
			FROM
442
				@TP@user_attributes
443
			WHERE
444
				UserID = ?
445
			',
446
			$userID
447 448 449
		);

		if (!$sth) {
450
			$server->log(LOG_ERR,"[MOD_CONFIG_SQL_TOPUPS] Cleanup => Failed to select user usage caps: ".
451
					AWITPT::DB::DBLayer::Error());
452 453 454
			goto FAIL_ROLLBACK;
		}

455
		# Store limits in capRecord hash
Nigel Kukard's avatar
Nigel Kukard committed
456
		while (my $row = hashifyLCtoMC($sth->fetchrow_hashref(), qw(Name Operator Value))) {
457

458 459 460 461 462 463
			if (defined($row->{'Name'})) {
				if ($row->{'Name'} eq 'SMRadius-Capping-Traffic-Limit') {
					if (defined($row->{'Operator'}) && $row->{'Operator'} eq ':=') {
						if (defined($row->{'Value'}) && $row->{'Value'} =~ /^[\d]+$/) {
							$capRecord{'TrafficLimit'} = $row->{'Value'};
						} else {
Nigel Kukard's avatar
Nigel Kukard committed
464 465
							$server->log(LOG_ERR,"[MOD_CONFIG_SQL_TOPUPS] Cleanup => SMRadius-Capping-Traffic-Limit value ".
									"invalid for user '".$username."'");
466 467 468
						}
					} else {
						$server->log(LOG_ERR,"[MOD_CONFIG_SQL_TOPUPS] Cleanup => Incorrect '".$row->{'Name'}."' operator '"
469
								.$row->{'Operator'}."' used  for user '".$username."'");
470
					}
471
				}
472 473 474 475 476
				if ($row->{'Name'} eq 'SMRadius-Capping-Uptime-Limit') {
					if (defined($row->{'Operator'}) && $row->{'Operator'} eq ':=') {
						if (defined($row->{'Value'}) && $row->{'Value'} =~ /^[\d]+$/) {
							$capRecord{'UptimeLimit'} = $row->{'Value'};
						} else {
Nigel Kukard's avatar
Nigel Kukard committed
477 478
							$server->log(LOG_ERR,"[MOD_CONFIG_SQL_TOPUPS] Cleanup => SMRadius-Capping-Uptime-Limit value ".
									"invalid for user '".$username."'");
479 480 481
						}
					} else {
						$server->log(LOG_ERR,"[MOD_CONFIG_SQL_TOPUPS] Cleanup => Incorrect '".$row->{'Name'}."' operator '"
482
								.$row->{'Operator'}."' used  for user '".$username."'");
483
					}
484
				}
485 486 487 488 489 490
			}
		}

		# Finished for now
		DBFreeRes($sth);

Nigel Kukard's avatar
Nigel Kukard committed
491 492 493 494 495
		$server->log(LOG_NOTICE,"[MOD_CONFIG_SQL_TOPUPS] Cleanup =>     CAP: ".
				"SMRadius-Capping-Traffic-Limit '%s', SMRadius-Capping-Uptime-Limit '%s'",
				$capRecord{'TrafficLimit'} ? $capRecord{'TrafficLimit'} : "-",
				$capRecord{'UptimeLimit'} ? $capRecord{'TrafficLimit'} : "-"
		);
496 497 498 499 500 501 502 503 504

		# Get users topups that are still valid from topups_summary, must not be depleted
		$sth = DBSelect('
			SELECT
				@TP@topups_summary.TopupID,
				@TP@topups_summary.Balance,
				@TP@topups.ValidTo,
				@TP@topups.Type
			FROM
505
				@TP@topups_summary, @TP@topups
506 507
			WHERE
				@TP@topups_summary.Depleted = 0
508
				AND @TP@topups.Depleted = 0
509
				AND @TP@topups_summary.TopupID = @TP@topups.ID
510
				AND @TP@topups.UserID = ?
511 512 513 514
				AND @TP@topups_summary.PeriodKey = ?
			ORDER BY
				@TP@topups.Timestamp
			',
515
			$userID, $prevPeriodKey
516 517 518 519
		);

		if (!$sth) {
			$server->log(LOG_ERR,"[MOD_CONFIG_SQL_TOPUPS] Cleanup => Failed to select topup summaries: ".
520
					AWITPT::DB::DBLayer::Error());
521
			goto FAIL_ROLLBACK;
522
		}
523 524 525 526 527


		# Add previous valid topups to lists
		my @trafficSummary = ();
		my @uptimeSummary = ();
Nigel Kukard's avatar
Nigel Kukard committed
528
		while (my $row = hashifyLCtoMC($sth->fetchrow_hashref(), qw(TopupID Balance Value ValidTo Type))) {
529 530

			if (defined($row->{'ValidTo'})) {
531
				# Convert string to unix time
532
				my $unix_validTo = str2time($row->{'ValidTo'});
Nigel Kukard's avatar
Nigel Kukard committed
533
				# Process traffic topup
534
				if (_isTrafficTopup($row->{'Type'})) {
Nigel Kukard's avatar
Nigel Kukard committed
535
					push(@trafficSummary, {
536 537
							TopupID => $row->{'TopupID'},
							Balance => $row->{'Balance'},
538
							ValidTo => $unix_validTo,
539
							Type => $row->{'Type'}
540
					});
Nigel Kukard's avatar
Nigel Kukard committed
541 542 543 544 545 546 547 548 549

					$server->log(LOG_NOTICE,"[MOD_CONFIG_SQL_TOPUPS] Cleanup =>     TRAFFIC SUMMARY TOPUP: ".
							"ID '%s', Balance '%s', ValidTo '%s'",
							$row->{'TopupID'},
							$row->{'Balance'},
							DateTime->from_epoch(epoch => $unix_validTo)->strftime("%F")
					);

				# Process uptime topup
550
				} elsif (_isUptimeTopup($row->{'Type'})) {
Nigel Kukard's avatar
Nigel Kukard committed
551
					push(@uptimeSummary, {
552 553
							TopupID => $row->{'TopupID'},
							Balance => $row->{'Balance'},
554
							ValidTo => $unix_validTo,
555
							Type => $row->{'Type'}
556
					});
Nigel Kukard's avatar
Nigel Kukard committed
557 558 559 560 561 562 563

					$server->log(LOG_NOTICE,"[MOD_CONFIG_SQL_TOPUPS] Cleanup =>     UPTIME SUMMARY TOPUP: ".
							"ID '%s', Balance '%s', ValidTo '%s'",
							$$row->{'TopupID'},
							$row->{'Balance'},
							DateTime->from_epoch(epoch => $unix_validTo)->strftime("%F")
					);
564 565
				}
			}
566 567
		}

568 569 570 571 572
		# Finished for now
		DBFreeRes($sth);


		# Get topups from last month
573 574
		$sth = DBSelect('
			SELECT
575
				ID, Value, Type, ValidTo
576
			FROM
577
				@TP@topups
578
			WHERE
579 580 581 582
				Depleted = 0
				AND UserID = ?
				AND ValidFrom = ?
				AND ValidTo >= ?
583
			ORDER BY
584
				Timestamp
585
			',
586
			$userID,$lastMonth,$thisMonth
587 588 589 590
		);

		if (!$sth) {
			$server->log(LOG_ERR,"[MOD_CONFIG_SQL_TOPUPS] Cleanup => Failed to select topups: ".
591
					AWITPT::DB::DBLayer::Error());
592 593 594 595
			goto FAIL_ROLLBACK;
		}

		# Loop with the topups and push them into arrays
596
		my (@trafficTopups,@uptimeTopups);
Nigel Kukard's avatar
Nigel Kukard committed
597
		while (my $row = hashifyLCtoMC($sth->fetchrow_hashref(), qw(ID Value Type ValidTo))) {
598

599
			# Convert string to unix time
600
			my $unix_validTo = str2time($row->{'ValidTo'});
601
			# If this is a traffic topup ...
602
			if (_isTrafficTopup($row->{'Type'})) {
603
				push(@trafficTopups, {
604 605
					ID => $row->{'ID'},
					Value => $row->{'Value'},
606
					ValidTo => $unix_validTo
607 608
				});

Nigel Kukard's avatar
Nigel Kukard committed
609 610 611 612 613 614 615
				$server->log(LOG_NOTICE,"[MOD_CONFIG_SQL_TOPUPS] Cleanup =>     TRAFFIC TOPUP: ".
						"ID '%s', Balance '%s', ValidTo '%s'",
						$row->{'ID'},
						$row->{'Value'},
						DateTime->from_epoch(epoch => $unix_validTo)->strftime("%F")
				);

616
			# Or a uptime topup...
617
			} elsif (_isUptimeTopup($row->{'Type'})) {
618
				push(@uptimeTopups, {
619 620
					ID => $row->{'ID'},
					Value => $row->{'Value'},
621
					ValidTo => $unix_validTo
622
				});
Nigel Kukard's avatar
Nigel Kukard committed
623 624 625 626 627 628 629

				$server->log(LOG_NOTICE,"[MOD_CONFIG_SQL_TOPUPS] Cleanup =>     UPTIME TOPUP: ".
						"ID '%s', Balance '%s', ValidTo '%s'",
						$row->{'ID'},
						$row->{'Value'},
						DateTime->from_epoch(epoch => $unix_validTo)->strftime("%F")
				);
630 631 632 633 634 635
			}
		}

		# Finished for now
		DBFreeRes($sth);

636 637
		# List of summaries depleted
		my @depletedSummary = ();
638 639
		my @depletedTopups = ();

640 641
		# Summaries to be edited/repeated
		my @summaryTopups = ();
642

643
		# Calculate excess usage if necessary
644
		my $trafficOverUsage = 0;
645
		if (defined($capRecord{'TrafficLimit'}) && $capRecord{'TrafficLimit'} > 0) {
646
			$trafficOverUsage = $usageTotals{'TotalDataUsage'} - $capRecord{'TrafficLimit'};
647 648
		} elsif (!(defined($capRecord{'TrafficLimit'}))) {
			$trafficOverUsage = $usageTotals{'TotalDataUsage'};
649 650 651 652
		}

		# User has started using topup bandwidth..
		if ($trafficOverUsage > 0) {
Nigel Kukard's avatar
Nigel Kukard committed
653 654 655 656
			$server->log(LOG_NOTICE,"[MOD_CONFIG_SQL_TOPUPS] Cleanup =>     TRAFFIC OVERAGE: $trafficOverUsage");

			# Sort topups first expiring first
			my @sortedTrafficSummary = sort { $a->{'ValidTo'} cmp $b->{'ValidTo'} } @trafficSummary;
657 658

			# Loop with previous topups, setting them depleted or repeating as necessary
Nigel Kukard's avatar
Nigel Kukard committed
659
			foreach my $summaryItem (@sortedTrafficSummary) {
660

661 662 663 664
				# Summary has not been used, if valid add to list to be repeated
				if ($trafficOverUsage <= 0 && $summaryItem->{'ValidTo'} >= $unix_nextMonth) {
					push(@summaryTopups, {
							ID => $summaryItem->{'TopupID'},
Nigel Kukard's avatar
Nigel Kukard committed
665
							PeriodKey => $curPeriodKey,
666
							Balance => $summaryItem->{'Balance'}
667
					});
Nigel Kukard's avatar
Nigel Kukard committed
668 669 670 671 672 673 674

					$server->log(LOG_NOTICE,"[MOD_CONFIG_SQL_TOPUPS] Cleanup =>     TRAFFIC SUMMARY UNUSED: ".
							"TOPUPID '%s', Balance '%s'",
							$summaryItem->{'TopupID'},
							$summaryItem->{'Balance'},
					);

675 676 677 678 679 680 681
				# Topup summary depleted
				} elsif ($summaryItem->{'Balance'} <= $trafficOverUsage) {
					push(@depletedSummary, $summaryItem->{'TopupID'});
					push(@depletedTopups, $summaryItem->{'TopupID'});

					# Excess traffic remaining
					$trafficOverUsage -= $summaryItem->{'Balance'};
Nigel Kukard's avatar
Nigel Kukard committed
682 683 684 685 686 687 688 689

					$server->log(LOG_NOTICE,"[MOD_CONFIG_SQL_TOPUPS] Cleanup =>     TRAFFIC SUMMARY DEPLETED: ".
							"TOPUPID '%s', Balance '%s', Overage Left '%s'",
							$summaryItem->{'TopupID'},
							$summaryItem->{'Balance'},
							$trafficOverUsage
					);

690 691 692 693 694 695
				# Topup summary still alive
				} else {
					my $trafficRemaining = $summaryItem->{'Balance'} - $trafficOverUsage;
					if ($summaryItem->{'ValidTo'} >= $unix_nextMonth) {
						push(@summaryTopups, {
								ID => $summaryItem->{'TopupID'},
Nigel Kukard's avatar
Nigel Kukard committed
696
								PeriodKey => $curPeriodKey,
697 698 699
								Balance => $trafficRemaining
						});
					}
700 701

					$trafficOverUsage = 0;
Nigel Kukard's avatar
Nigel Kukard committed
702 703 704 705 706 707 708

					$server->log(LOG_NOTICE,"[MOD_CONFIG_SQL_TOPUPS] Cleanup =>     TRAFFIC SUMMARY USAGE: ".
							"TOPUPID '%s', Balance '%s', Overage Left '%s'",
							$summaryItem->{'TopupID'},
							$trafficRemaining,
							$trafficOverUsage
					);
709 710 711
				}
			}

Nigel Kukard's avatar
Nigel Kukard committed
712 713 714
			# Sort topups first expiring first
			my @sortedTrafficTopups = sort { $a->{'ValidTo'} cmp $b->{'ValidTo'} } @trafficTopups;

715
			# Loop with topups, setting them depleted or adding summary as necessary
Nigel Kukard's avatar
Nigel Kukard committed
716
			foreach my $topup (@sortedTrafficTopups) {
717

718 719 720 721
				# Topup has not been used, if valid add to summary
				if ($trafficOverUsage <= 0 && $topup->{'ValidTo'} >= $unix_nextMonth) {
					push(@summaryTopups, {
							ID => $topup->{'ID'},
Nigel Kukard's avatar
Nigel Kukard committed
722
							PeriodKey => $curPeriodKey,
723 724
							Balance => $topup->{'Value'}
					});
Nigel Kukard's avatar
Nigel Kukard committed
725 726 727 728 729 730 731

					$server->log(LOG_NOTICE,"[MOD_CONFIG_SQL_TOPUPS] Cleanup =>     TRAFFIC TOPUP UNUSED: ".
							"TOPUPID '%s', Balance '%s'",
							$topup->{'ID'},
							$topup->{'Value'}
					);

732
				# Topup depleted
733
				} elsif ($topup->{'Value'} <= $trafficOverUsage) {
734
					push(@depletedTopups, $topup->{'ID'});
735

736
					# Excess traffic remaining
737
					$trafficOverUsage -= $topup->{'Value'};
Nigel Kukard's avatar
Nigel Kukard committed
738 739 740 741 742 743 744 745

					$server->log(LOG_NOTICE,"[MOD_CONFIG_SQL_TOPUPS] Cleanup =>     TRAFFIC TOPUP DEPLETED: ".
							"TOPUPID '%s', Balance '%s', Overage Left '%s'",
							$topup->{'ID'},
							$topup->{'Value'},
							$trafficOverUsage
					);

746 747
				# Topup still alive
				} else {
748 749
					# Check if this summary exists in the list
					my $trafficRemaining = $topup->{'Value'} - $trafficOverUsage;
750

751
					if ($topup->{'ValidTo'} >= $unix_nextMonth) {
752 753
						push(@summaryTopups, {
								ID => $topup->{'ID'},
Nigel Kukard's avatar
Nigel Kukard committed
754
								PeriodKey => $curPeriodKey,
755 756
								Balance => $trafficRemaining
						});
757
					}
758 759

					$trafficOverUsage = 0;
Nigel Kukard's avatar
Nigel Kukard committed
760 761 762 763 764 765 766

					$server->log(LOG_NOTICE,"[MOD_CONFIG_SQL_TOPUPS] Cleanup =>     TRAFFIC TOPUP USAGE: ".
							"TOPUPID '%s', Balance '%s', Overage Left '%s'",
							$topup->{'ID'},
							$trafficRemaining,
							$trafficOverUsage
					);
767 768
				}
			}
769

770 771 772 773 774 775 776 777
		# User has not used up cap but may have topups to carry over
		} else {
			# Check for summaries
			foreach my $summaryItem (@trafficSummary) {
				# Add summary
				if ($summaryItem->{'ValidTo'} >= $unix_nextMonth) {
					push(@summaryTopups, {
							ID => $summaryItem->{'TopupID'},
Nigel Kukard's avatar
Nigel Kukard committed
778
							PeriodKey => $curPeriodKey,
779
							Balance => $summaryItem->{'Balance'}
780
					});
Nigel Kukard's avatar
Nigel Kukard committed
781 782 783 784 785 786

					$server->log(LOG_NOTICE,"[MOD_CONFIG_SQL_TOPUPS] Cleanup =>     TRAFFIC SUMMARY CARRY: ".
							"TOPUPID '%s', Balance '%s'",
							$summaryItem->{'TopupID'},
							$summaryItem->{'Balance'}
					);
787 788 789 790 791 792 793 794
				}
			}
			# Check for topups
			foreach my $topup (@trafficTopups) {
				# Add to summaries
				if ($topup->{'ValidTo'} >= $unix_nextMonth) {
					push(@summaryTopups, {
							ID => $topup->{'ID'},
Nigel Kukard's avatar
Nigel Kukard committed
795
							PeriodKey => $curPeriodKey,
796 797
							Balance => $topup->{'Value'}
					});
Nigel Kukard's avatar
Nigel Kukard committed
798 799 800 801 802 803

					$server->log(LOG_NOTICE,"[MOD_CONFIG_SQL_TOPUPS] Cleanup =>     TRAFFIC TOPUP CARRY: ".
							"TOPUPID '%s', Balance '%s'",
							$topup->{'ID'},
							$topup->{'Value'}
					);
804 805 806 807
				}
			}
		}

808

809 810 811
		# Calculate excess usage if necessary
		my $uptimeOverUsage = 0;
		if (defined($capRecord{'UptimeLimit'}) && $capRecord{'UptimeLimit'} > 0) {
812
			$uptimeOverUsage = $usageTotals{'TotalSessionTime'} - $capRecord{'UptimeLimit'};
813 814
		} elsif (!(defined($capRecord{'UptimeLimit'}))) {
			$uptimeOverUsage = $usageTotals{'TotalSessionTime'};
815 816 817 818
		}

		# User has started using topup uptime..
		if ($uptimeOverUsage > 0) {
Nigel Kukard's avatar
Nigel Kukard committed
819
			$server->log(LOG_NOTICE,"[MOD_CONFIG_SQL_TOPUPS] Cleanup =>     UPTIME OVERAGE: $uptimeOverUsage");
Nigel Kukard's avatar
Nigel Kukard committed
820

Nigel Kukard's avatar
Nigel Kukard committed
821 822
			# Sort topups first expiring first
			my @sortedUptimeSummary = sort { $a->{'ValidTo'} cmp $b->{'ValidTo'} } @uptimeSummary;
823 824

			# Loop with previous topups, setting them depleted or repeating as necessary
Nigel Kukard's avatar
Nigel Kukard committed
825
			foreach my $summaryItem (@sortedUptimeSummary) {
826 827 828 829
				# Summary has not been used, if valid add to list to be repeated
				if ($uptimeOverUsage <= 0 && $summaryItem->{'ValidTo'} >= $unix_nextMonth) {
					push(@summaryTopups, {
							ID => $summaryItem->{'TopupID'},
Nigel Kukard's avatar
Nigel Kukard committed
830
							PeriodKey => $curPeriodKey,
831
							Balance => $summaryItem->{'Balance'}
832
					});
Nigel Kukard's avatar
Nigel Kukard committed
833 834 835 836 837 838 839

					$server->log(LOG_NOTICE,"[MOD_CONFIG_SQL_TOPUPS] Cleanup =>     UPTIME SUMMARY UNUSED: ".
							"TOPUPID '%s', Balance '%s'",
							$summaryItem->{'TopupID'},
							$summaryItem->{'Balance'},
					);

840 841 842 843 844 845 846
				# Topup summary depleted
				} elsif ($summaryItem->{'Balance'} <= $uptimeOverUsage) {
					push(@depletedSummary, $summaryItem->{'TopupID'});
					push(@depletedTopups, $summaryItem->{'TopupID'});

					# Excess uptime remaining
					$uptimeOverUsage -= $summaryItem->{'Balance'};
Nigel Kukard's avatar
Nigel Kukard committed
847 848 849 850 851 852 853 854

					$server->log(LOG_NOTICE,"[MOD_CONFIG_SQL_TOPUPS] Cleanup =>     UPTIME SUMMARY DEPLETED: ".
							"TOPUPID '%s', Balance '%s', Overage Left '%s'",
							$summaryItem->{'TopupID'},
							$summaryItem->{'Balance'},
							$uptimeOverUsage
					);

855 856 857 858 859 860
				# Topup summary still alive
				} else {
					my $uptimeRemaining = $summaryItem->{'Balance'} - $uptimeOverUsage;
					if ($summaryItem->{'ValidTo'} >= $unix_nextMonth) {
						push(@summaryTopups, {
								ID => $summaryItem->{'TopupID'},
Nigel Kukard's avatar
Nigel Kukard committed
861
								PeriodKey => $curPeriodKey,
862 863 864
								Balance => $uptimeRemaining
						});
					}
865 866

					$uptimeOverUsage = 0;
Nigel Kukard's avatar
Nigel Kukard committed
867 868 869 870 871 872 873

					$server->log(LOG_NOTICE,"[MOD_CONFIG_SQL_TOPUPS] Cleanup =>     UPTIME SUMMARY USAGE: ".
							"TOPUPID '%s', Balance '%s', Overage Left '%s'",
							$summaryItem->{'TopupID'},
							$uptimeRemaining,
							$uptimeOverUsage
					);
874 875 876
				}
			}

Nigel Kukard's avatar
Nigel Kukard committed
877 878 879
			# Sort topups first expiring first
			my @sortedUptimeTopups = sort { $a->{'ValidTo'} cmp $b->{'ValidTo'} } @uptimeTopups;

880
			# Loop with topups, setting them depleted or adding summary as necessary
Nigel Kukard's avatar
Nigel Kukard committed
881
			foreach my $topup (@sortedUptimeTopups) {
882 883 884 885
				# Topup has not been used, if valid add to summary
				if ($uptimeOverUsage <= 0 && $topup->{'ValidTo'} >= $unix_nextMonth) {
					push(@summaryTopups, {
							ID => $topup->{'ID'},
Nigel Kukard's avatar
Nigel Kukard committed
886
							PeriodKey => $curPeriodKey,
887 888
							Balance => $topup->{'Value'}
					});
Nigel Kukard's avatar
Nigel Kukard committed
889 890 891 892 893 894 895

					$server->log(LOG_NOTICE,"[MOD_CONFIG_SQL_TOPUPS] Cleanup =>     UPTIME TOPUP UNUSED: ".
							"TOPUPID '%s', Balance '%s'",
							$topup->{'ID'},
							$topup->{'Value'}
					);

896
				# Topup depleted
897
				} elsif ($topup->{'Value'} <= $uptimeOverUsage) {
898 899
					push(@depletedTopups, $topup->{'ID'});
					# Excess uptime remaining
900
					$uptimeOverUsage -= $topup->{'Value'};
Nigel Kukard's avatar
Nigel Kukard committed
901 902 903 904 905 906 907 908

					$server->log(LOG_NOTICE,"[MOD_CONFIG_SQL_TOPUPS] Cleanup =>     UPTIME TOPUP DEPLETED: ".
							"TOPUPID '%s', Balance '%s', Overage Left '%s'",
							$topup->{'ID'},
							$topup->{'Value'},
							$uptimeOverUsage
					);

909 910
				# Topup still alive
				} else {
911
					my $uptimeRemaining = $topup->{'Value'} - $uptimeOverUsage;
912
					if ($topup->{'ValidTo'} >= $unix_nextMonth) {
913 914
						push(@summaryTopups, {
								ID => $topup->{'ID'},
Nigel Kukard's avatar
Nigel Kukard committed
915
								PeriodKey => $curPeriodKey,
916 917
								Balance => $uptimeRemaining
						});
918
					}
919 920

					$uptimeOverUsage = 0;
Nigel Kukard's avatar
Nigel Kukard committed
921 922 923 924 925 926 927

					$server->log(LOG_NOTICE,"[MOD_CONFIG_SQL_TOPUPS] Cleanup =>     UPTIME TOPUP USAGE: ".
							"TOPUPID '%s', Balance '%s', Overage Left '%s'",
							$topup->{'ID'},
							$uptimeRemaining,
							$uptimeOverUsage
					);
928 929
				}
			}
930

931 932 933 934 935 936 937 938
		# User has not used up cap but may have topups to carry over
		} else {
			# Check for summaries
			foreach my $summaryItem (@uptimeSummary) {
				# Add summary
				if ($summaryItem->{'ValidTo'} >= $unix_nextMonth) {
					push(@summaryTopups, {
							ID => $summaryItem->{'TopupID'},
Nigel Kukard's avatar
Nigel Kukard committed
939
							PeriodKey => $curPeriodKey,
940
							Balance => $summaryItem->{'Balance'}
941
					});
Nigel Kukard's avatar
Nigel Kukard committed
942 943 944 945 946 947

					$server->log(LOG_NOTICE,"[MOD_CONFIG_SQL_TOPUPS] Cleanup =>     UPTIME SUMMARY CARRY: ".
							"TOPUPID '%s', Balance '%s'",
							$summaryItem->{'TopupID'},
							$summaryItem->{'Balance'}
					);
948 949 950 951 952 953 954 955
				}
			}
			# Check for topups
			foreach my $topup (@uptimeTopups) {
				# Check if summary exists
				if ($topup->{'ValidTo'} >= $unix_nextMonth) {
					push(@summaryTopups, {
							ID => $topup->{'ID'},
Nigel Kukard's avatar
Nigel Kukard committed
956
							PeriodKey => $curPeriodKey,
957 958
							Balance => $topup->{'Value'}
					});
Nigel Kukard's avatar
Nigel Kukard committed
959 960 961 962 963 964

					$server->log(LOG_NOTICE,"[MOD_CONFIG_SQL_TOPUPS] Cleanup =>     UPTIME TOPUP CARRY: ".
							"TOPUPID '%s', Balance '%s'",
							$topup->{'ID'},
							$topup->{'Value'}
					);
965 966 967 968
				}
			}
		}

969 970
		# Loop through summary topups
		foreach my $summaryTopup (@summaryTopups) {
Nigel Kukard's avatar
Nigel Kukard committed
971 972 973 974 975 976 977 978
			# Create topup summaries
			$sth = DBDo('
				INSERT INTO
					@TP@topups_summary (TopupID,PeriodKey,Balance)
				VALUES
					(?,?,?)
				',
				$summaryTopup->{'ID'},$curPeriodKey,$summaryTopup->{'Balance'}
979 980 981
			);

			if (!$sth) {
Nigel Kukard's avatar
Nigel Kukard committed
982
				$server->log(LOG_ERR,"[MOD_CONFIG_SQL_TOPUPS] Cleanup => Failed to create topup summary: ".
983
						AWITPT::DB::DBLayer::Error());
984 985 986
				goto FAIL_ROLLBACK;
			}

Nigel Kukard's avatar
Nigel Kukard committed
987 988 989 990 991
			$server->log(LOG_NOTICE,"[MOD_CONFIG_SQL_TOPUPS] Cleanup =>     CREATE TOPUP SUMMARY: ".
					"TOPUPID '%s', Balance '%s'",
					$summaryTopup->{'ID'},
					$summaryTopup->{'Balance'}
			);
992 993
		}

994
		# Loop through topups that are depleted
995
		foreach my $topupID (@depletedTopups) {
996 997 998 999 1000
			# Set users depleted topups
			$sth = DBSelect('
				UPDATE
					@TP@topups
				SET
1001 1002
					Depleted = 1,
					SMAdminDepletedOn = ?
1003
				WHERE
1004
					ID = ?
1005
				',
1006
				$depletedTimestamp,$topupID
1007 1008
			);
			if (!$sth) {
Nigel Kukard's avatar
Nigel Kukard committed
1009
				$server->log(LOG_ERR,"[MOD_CONFIG_SQL_TOPUPS] Cleanup => Failed to deplete topup: ".
1010
						AWITPT::DB::DBLayer::Error());
1011 1012
				goto FAIL_ROLLBACK;
			}
Nigel Kukard's avatar
Nigel Kukard committed
1013 1014 1015 1016 1017

			$server->log(LOG_NOTICE,"[MOD_CONFIG_SQL_TOPUPS] Cleanup =>     DEPLETED TOPUP: ".
					"TOPUPID '%s'",
					$topupID
			);
1018
		}
1019 1020 1021 1022 1023 1024 1025 1026

		# Loop through topup summary items that are depleted
		foreach my $topupID (@depletedSummary) {
			# Set users depleted topup summaries
			$sth = DBSelect('
				UPDATE
					@TP@topups_summary
				SET
1027 1028
					Depleted = 1,
					SMAdminDepletedOn = ?
1029 1030 1031
				WHERE
					TopupID = ?
				',
1032
				$depletedTimestamp,$topupID
1033 1034 1035
			);
			if (!$sth) {
				$server->log(LOG_ERR,"[MOD_CONFIG_SQL_TOPUPS] Cleanup => Failed to update topups_summary: ".
1036
						AWITPT::DB::DBLayer::Error());
1037 1038
				goto FAIL_ROLLBACK;
			}
Nigel Kukard's avatar
Nigel Kukard committed
1039 1040 1041 1042 1043

			$server->log(LOG_NOTICE,"[MOD_CONFIG_SQL_TOPUPS] Cleanup =>     DEPLETED TOPUP SUMMARY: ".
					"TOPUPID '%s'",
					$topupID
			);
1044
		}
1045 1046 1047
	}

	# Finished
Nigel Kukard's avatar
Nigel Kukard committed
1048
	$server->log(LOG_NOTICE,"[MOD_CONFIG_SQL_TOPUPS] Cleanup => Topups have been updated and summaries created");
1049 1050 1051 1052 1053 1054 1055 1056 1057 1058
	DBCommit();
	return;

FAIL_ROLLBACK:
	DBRollback();
	$server->log(LOG_NOTICE,"[MOD_CONFIG_SQL_TOPUPS] Cleanup => Database has been rolled back, no records updated");
	return;
}


1059 1060 1061 1062 1063 1064 1065 1066 1067 1068 1069 1070

## @internal
# Function snippet to add up traffic summaries based on topup types
sub _trafficSummaryAdd
{
	my ($trafficSummaries,$topup,$key) = @_;

	# First we add up NON-autotopup's
	if (!_isAutoTopup($topup->{'Type'})) {

		# Add the topup amount to the appropriate hash entry
		if (_isTrafficTopup($topup->{'Type'})) {
1071
			$trafficSummaries->{'traffic'} += $topup->{$key};
1072 1073

		} elsif (_isUptimeTopup($topup->{'Type'})) {
1074
			$trafficSummaries->{'uptime'} += $topup->{$key};
1075 1076 1077 1078 1079 1080
		}

	# Next we add up auto-topups
	} else {
		if (_isTrafficTopup($topup->{'Type'})) {
			# Add to traffic summary list
1081
			$trafficSummaries->{'traffic-auto'} += $topup->{$key};
1082 1083 1084

		} elsif (_isUptimeTopup($topup->{'Type'})) {