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', ))