def topups(self, req, resp):
return sql_list(
req,
'subscriber_topup',
('id', 'user_id', 'volume_gb', 'volume_metric', 'volume_span',
'volume_repeat', 'volume_expire'),
)
def nas(self, req, resp, id):
f_virtual_id = sql.Field('calabiyau_nas.virtual_id')
w_virtual_id = f_virtual_id == sql.Value(id)
select = sql.Select('calabiyau_nas')
select.where = w_virtual_id
return sql_list(req,
select,
fields=(
'id',
'name',
'INET6_NTOA(server)',
'nas_type',
'secret',
),
search={
'id': str,
'name': str,
'server': 'ip',
'nas_type': str,
'secret': str
},
order=(
'calabiyau_nas.name',
'calabiyau_nas.server',
'calabiyau_nas.nas_type',
'calabiyau_nas.secret',
))
def ips(self, req, resp, pool_id):
pool = obj(req, calabiyau_pool, sql_id=pool_id)
f_ippool_pool_id = Field('calabiyau_ippool.pool_id')
f_ippool_user_id = Field('calabiyau_ippool.user_id')
f_subscriber_id = Field('calabiyau_subscriber.id')
log.critical(type(pool.id))
v_ippool_pool_id = Value(pool['id'])
j_subscriber = f_ippool_user_id == f_subscriber_id
select = Select("calabiyau_ippool")
select.where = f_ippool_pool_id == v_ippool_pool_id
select.left_join('calabiyau_subscriber', j_subscriber)
return sql_list(req,
select,
fields=('calabiyau_ippool.id',
'INET6_NTOA(calabiyau_ippool.framedipaddress)',
'calabiyau_ippool.expiry_time',
'calabiyau_subscriber.username'),
search={
'calabiyau_ippool.framedipaddress': 'ip',
'calabiyau_ippool.expiry_time': str,
'calabiyau_subscriber.username': str
},
order=[
'calabiyau_ippool.framedipaddress',
'calabiyau_ippool.expiry_time',
'calabiyau_subscriber.username'
])
def data_monthly(self, req, resp, user_id):
user = obj(req, calabiyau_subscriber, sql_id=user_id)
f_id = sql.Field('id')
f_user_id = sql.Field('user_id')
f_today = sql.Field('today')
f_acctinputoctets = sql.Field('ROUND(SUM(acctinputoctets)' +
' / 1024 / 1024 / 1024, 2)' +
' AS acctinputoctets')
f_acctoutputoctets = sql.Field('ROUND(SUM(acctoutputoctets)' +
' / 1024 / 1024 / 1024, 2)' +
' AS acctoutputoctets')
v_user_id = sql.Value(user['id'])
select = sql.Select('calabiyau_accounting')
select.fields = (f_id,
f_today,
f_acctinputoctets,
f_acctoutputoctets,)
select.where = f_user_id == v_user_id
select.order_by = f_today('>')
select.group_by = sql.Field('YEAR(today)'), sql.Field('MONTH(today)')
return sql_list(req,
select,
limit=12)
def users(self, req, resp):
return sql_list(
req,
'tradius_user',
(
'id',
'username',
'name',
),
)
def users(self, req, resp):
return sql_list(
req,
'calabiyau_subscriber',
(
'id',
'username',
'name',
),
)
def attrs(self, req, resp, id):
where = {'group_id': id}
return sql_list(req,
'tradius_group_attr', (
'id',
'attribute',
'op',
'value',
'ctx',
),
where=where)
def packages(self, req, resp):
return sql_list(req,
'calabiyau_package',
fields=(
'id',
'name',
),
search={
'id': str,
'name': str
})
def pools(self, req, resp):
return sql_list(req,
'calabiyau_pool',
fields=(
'id',
'pool_name',
),
search={
'id': str,
'pool_name': str
})
def ips(self, req, resp, id):
pool = obj(req, tradius_pool, sql_id=id)
return sql_list(req,
'tradius_ippool', (
'id',
'pool_name',
'framedipaddress',
'nasipaddress',
'expiry_time',
'username',
),
where={'pool_name': pool['pool_name']})
def users(self, req, resp):
return sql_list(req,
'calabiyau_subscriber',
fields=(
'id',
'username',
'name',
),
search={
'id': str,
'username': str,
'name': str
})
def virtuals(self, req, resp):
return sql_list(req,
'calabiyau_virtual',
fields=(
'id',
'domain',
'name',
),
search={
'id': str,
'domain': str,
'name': str
})
def attrs(self, req, resp, user_id):
user = tradius_user()
user.sql_id(user_id)
validate_access(req, user)
where = {'user_id': user_id}
return sql_list(req,
'tradius_user_attr', (
'id',
'attribute',
'op',
'value',
),
where=where)
def attrs(self, req, resp, id):
f_package_id = sql.Field('calabiyau_package_attr.package_id')
w_package_id = f_package_id == sql.Value(id)
select = sql.Select('calabiyau_package_attr')
select.where = w_package_id
return sql_list(req,
select,
fields=('id', 'attribute', 'tag', 'value', 'ctx',
'nas_type'),
search={
'id': str,
'attribute': str,
'value': str,
'ctx': str,
'nas_type': str
})
def sessions(self, req, resp):
def ctx_val(ctx_id):
try:
return {'ctx': ctx[ctx_id]}
except IndexError:
return {'ctx': ctx_id}
f_session_id = sql.Field('calabiyau_session.id')
f_session_ctx = sql.Field('calabiyau_session.ctx')
f_session_accttype = sql.Field('calabiyau_session.accttype')
f_session_start = sql.Field('calabiyau_session.acctstarttime')
f_session_updated = sql.Field('calabiyau_session.acctupdated')
f_session_unique_id = sql.Field('calabiyau_session.acctuniqueid')
f_session_ip = sql.Field(
'INET6_NTOA(calabiyau_session.framedipaddress)')
f_nas_ip = sql.Field(
'INET6_NTOA(calabiyau_session.nasipaddress)')
f_session_username = sql.Field('calabiyau_session.username')
f_session_user_id = sql.Field('calabiyau_session.id')
select = sql.Select('calabiyau_session')
select.fields = (f_session_id,
f_session_unique_id,
f_session_start,
f_session_updated,
f_session_user_id,
f_session_username,
f_session_ip,
f_nas_ip,
f_session_ctx,
)
select.where = f_session_accttype != sql.Value('stop')
return sql_list(
req,
select,
search={
'calabiyau_session.acctstarttime': 'datetime',
'calabiyau_session.acctupdated': 'datetime',
'calabiyau_session.user_id': str,
'calabiyau_session.username': str,
'calabiyau_session.acctuniqueid': str,
'calabiyau_session.framedipaddress': 'ip',
'calabiyau_session.nasipaddress': 'ip'},
callbacks={'ctx': ctx_val})
def ips(self, req, resp, id):
pool = obj(req, calabiyau_pool, sql_id=id)
def get_username(user_id):
with db() as conn:
result = conn.execute(
'SELECT username FROM' + ' calabiyau_subscriber' +
' WHERE id = %s', user_id).fetchone()
if result:
return result
# where={'pool_id': pool['id']},
return sql_list(req,
'calabiyau_ippool',
fields=(
'id',
'framedipaddress',
'expiry_time',
'user_id',
),
callbacks={'user_id': get_username})
def virtuals(self, req, resp):
return sql_list(req, 'tradius_virtual', (
'id',
'domain',
'name',
))
def nas(self, req, resp, id):
where = {'virtual_id': id}
return sql_list(req,
'tradius_nas',
('id', 'name', 'server', 'nas_type', 'secret'),
where=where)
def pools(self, req, resp):
return sql_list(req, 'calabiyau_pool', (
'id',
'pool_name',
))
def attrs(self, req, resp, id):
where = {'package_id': id}
return sql_list(req,
'calabiyau_package_attr',
('id', 'attribute', 'value', 'ctx', 'nas_type'),
where=where)
def groups(self, req, resp):
return sql_list(req, 'tradius_group', (
'id',
'name',
))
def virtuals(self, req, resp):
return sql_list(req, 'calabiyau_virtual', (
'id',
'domain',
'name',
))
def packages(self, req, resp):
return sql_list(req, 'calabiyau_package', (
'id',
'name',
))
def pools(self, req, resp):
return sql_list(req, 'tradius_pool', (
'id',
'pool_name',
))
