def query_trafficdata(ip, user_id):
"""Query traffic input/output for IP
:param ip: a valid ip
:param user_id: an id of a mysql user tuple
:return: a dict containing the traffic data in the form of
{'history': [('weekday', in, out, credit), …], 'credit': credit}
"""
trafficdata = sql_query(
"SELECT t.timetag - %(today)s AS day, input, output, amount "
"FROM traffic.tuext AS t "
"LEFT OUTER JOIN credit AS c ON t.timetag = c.timetag "
"WHERE ip = %(ip)s AND c.user_id = %(uid)s "
"AND t.timetag BETWEEN %(weekago)s AND %(today)s "
"ORDER BY 'day' DESC ",
{'today': timetag_from_timestamp(),
'weekago': timetag_from_timestamp() - 6,
'ip': ip,
'uid': user_id}
).fetchall()
if not trafficdata:
raise DBQueryEmpty('No trafficdata retrieved for user {}@{}'
.format(user_id, ip))
traffic = {'history': [], 'credit': 0}
returned_days = [int(i['day']) for i in trafficdata]
# loop through expected days ([-6..0])
for d in range(-6, 1):
day = datetime.date.fromtimestamp(
timestamp_from_timetag(timetag_from_timestamp() + d)
).strftime('%w')
if d in returned_days:
# pick the to `d` corresponding item of the mysql-result
i = next((x for x in trafficdata if x['day'] == d), None)
(input, output, credit) = (
round(i[param] / 1024.0, 2)
for param in ['input', 'output', 'amount']
)
traffic['history'].append(
(WEEKDAYS[day], input, output, credit))
else:
traffic['history'].append(
(WEEKDAYS[day], 0.0, 0.0, 0.0))
traffic['credit'] = (lambda x: x[3] - x[1] - x[2])(traffic['history'][-1])
return traffic
def query_current_credit(uid=None, ip=None):
"""Returns the current credit in MiB
:param uid: The id of the user
:param ip: The ip of the user
:return: The current amount of credit or False if foreign IP
"""
if uid is None:
if ip is None:
raise AttributeError('Either ip or user_id must be specified!')
user_id = user_id_from_ip(ip)
if user_id is 0:
return False # IP doesn't correspond to any user
else:
user_id = user_id_from_uid(uid)
ip = ip_from_user_id(user_id)
try:
result = sql_query(
"SELECT amount - input - output as current "
"FROM traffic.tuext AS t "
"LEFT OUTER JOIN credit AS c ON t.timetag = c.timetag "
"WHERE ip = %(ip)s AND c.user_id = %(user_id)s "
"AND t.timetag = %(today)s",
{'today': timetag_from_timestamp(), 'ip': ip, 'user_id': user_id}
).fetchone()
except OperationalError as e:
logger.critical('Unable to connect to MySQL server',
extra={'data': {'exception_args': e.args}})
raise
else:
return round(result['current'] / 1024, 2)
