def fetch_sites_report(self, page = 1, rp = 25,
sortname = 'download_bytes', sortorder = 'desc',
qtype = None, query = None):
clients = DBSession.query(Domain.text,
func.count(MiniAccess.address_id.distinct()),
func.count(MiniAccess.proxy_user_id.distinct()),
func.sum(MiniAccess.count),
func.sum(MiniAccess.download_bytes),
func.sum(MiniAccess.upload_bytes),
Domain.id)
clients = clients.join(MiniAccess)
clients = clients.group_by(Domain.text)
clients = clients.filter(MiniAccess.date == session['date'])
sort_fn = get_sortfn(sortorder)
if sortname == 'address':
clients = clients.order_by(sort_fn('count_1'))
elif sortname == 'proxy_user':
clients = clients.order_by(sort_fn('count_2'))
elif sortname == 'requests':
clients = clients.order_by(sort_fn('sum_1'))
elif sortname == 'download_bytes':
clients = clients.order_by(sort_fn('sum_2'))
elif sortname == 'upload_bytes':
clients = clients.order_by(sort_fn('sum_3'))
total = clients.count()
offset = (page - 1) * rp
clients = clients.offset(offset).limit(rp)
rows = []
for data in clients:
clients = link("reports/sites_ip", data[6], data[1])
users = link("reports/sites_users", data[6], int(data[2]) - 1)
values = [ data[0], clients, users, data[3],
mb(data[4]),
mb(data[5]) ]
row = { 'id' : data[0],
'cell' : values }
rows.append(row)
return dict(page = page, total = total, rows = rows)
def fetch_users_report(self, page = 1, rp = 25,
sortname = 'download_bytes', sortorder = 'desc',
qtype = None, query = None):
clients = DBSession.query(ProxyUser.text,
func.count(MiniAccess.domain_id.distinct()),
func.sum(MiniAccess.count),
func.sum(MiniAccess.download_bytes),
func.sum(MiniAccess.upload_bytes))
clients = clients.join(MiniAccess)
clients = clients.group_by(ProxyUser.text)
clients = clients.filter(and_(MiniAccess.date == session['date'],
ProxyUser.text != '-'))
sort_fn = get_sortfn(sortorder)
if sortname == 'sites':
clients = clients.order_by(sort_fn('count_1'))
elif sortname == 'requests':
clients = clients.order_by(sort_fn('sum_1'))
elif sortname == 'download_bytes':
clients = clients.order_by(sort_fn('sum_2'))
elif sortname == 'upload_bytes':
clients = clients.order_by(sort_fn('sum_3'))
total = clients.count()
offset = (page - 1) * rp
clients = clients.offset(offset).limit(rp)
rows = []
for data in clients:
sites = link('reports/user_sites', data[0], data[1])
values = [ data[0], sites, data[2],
mb(data[3]),
mb(data[4]) ]
row = { 'id' : data[0],
'cell' : values }
rows.append(row)
return dict(page = page, total = total, rows = rows)
def fetch_main_report(self, page = 1, rp = 25, sortname = 'date',
sortorder = 'desc', qtype = None, query = None):
dash_id = DBSession.query(ProxyUser.id).filter(ProxyUser.text == "-")
dash_id = dash_id.one()[0]
count_ip = """count(distinct if(miniaccess.proxy_user_id = %d,
miniaccess.address_id, 0))""" % (dash_id)
count_user = """count(distinct if(miniaccess.proxy_user_id <> %d,
miniaccess.proxy_user_id, 0))""" % (dash_id)
if session['group_by'] == 'day':
date_col = MiniAccess.date
else:
date_col = func.concat(func.min(MiniAccess.date), ' - ',
func.max(MiniAccess.date))
#count the clients address only if the request is not authenticated
#I use literal SQL because i don't find how to do this with ORM.
main = DBSession.query(date_col,
count_ip,
count_user,
func.count(MiniAccess.domain_id.distinct()),
func.sum(MiniAccess.count),
func.sum(MiniAccess.download_bytes),
func.sum(MiniAccess.upload_bytes))
main = main.group_by(MiniAccess.date)
sort_fn = get_sortfn(sortorder)
if sortname == 'date':
main = main.order_by(sort_fn(MiniAccess.date))
elif sortname == 'clients':
main = main.order_by(sort_fn('2'))
elif sortname == 'users':
main = main.order_by(sort_fn('3'))
elif sortname == 'sites':
main = main.order_by(sort_fn('count_1'))
elif sortname == 'requests':
main = main.order_by(sort_fn('sum_1'))
elif sortname == 'download_bytes':
main = main.order_by(sort_fn('sum_2'))
elif sortname == 'upload_bytes':
main = main.order_by(sort_fn('sum_3'))
total = main.count()
offset = (page - 1) * rp
main = main.offset(offset).limit(rp)
rows = []
print main
for data in main:
urldate = data[0]
clients = link('reports/clients', urldate, data[1])
# We don't count '-' user because it's used in unauth. msgs.
users = link('reports/users', urldate, data[2])
sites = link('reports/sites', urldate, data[3])
values = [ data[0], clients, users, sites, data[4],
mb(data[5]),
mb(data[6]) ]
row = { 'id' : data[0],
'cell' : values }
rows.append(row)
return dict(page = page, total = total, rows = rows)