import calendar
import logging
import operator
from datetime import (
date,
datetime,
timedelta,
)
import sqlalchemy as sa
from markupsafe import escape
from sqlalchemy import (
false,
func,
select,
)
import galaxy.model
from galaxy import util
from galaxy.webapps.base.controller import (
BaseUIController,
web,
)
from galaxy.webapps.reports.controllers.jobs import sorter
from galaxy.webapps.reports.controllers.query import ReportQueryBuilder
log = logging.getLogger(__name__)
[docs]class Users(BaseUIController, ReportQueryBuilder):
[docs] @web.expose
def registered_users(self, trans, **kwd):
message = escape(util.restore_text(kwd.get("message", "")))
stmt = select(func.count(galaxy.model.User.id))
num_users = trans.sa_session.scalar(stmt)
return trans.fill_template("/webapps/reports/registered_users.mako", num_users=num_users, message=message)
[docs] @web.expose
def registered_users_per_month(self, trans, **kwd):
message = escape(util.restore_text(kwd.get("message", "")))
specs = sorter("date", kwd)
sort_id = specs.sort_id
order = specs.order
arrow = specs.arrow
q = (
sa.select(
self.select_month(galaxy.model.User.table.c.create_time).label("date"),
sa.func.count(galaxy.model.User.table.c.id).label("num_users"),
)
.group_by(self.group_by_month(galaxy.model.User.table.c.create_time))
.order_by(specs.exc_order)
)
users = []
for row in trans.sa_session.execute(q):
users.append((row.date.strftime("%Y-%m"), row.num_users, row.date.strftime("%B"), row.date.strftime("%Y")))
return trans.fill_template(
"/webapps/reports/registered_users_per_month.mako",
order=order,
arrow=arrow,
sort_id=sort_id,
users=users,
message=message,
)
[docs] @web.expose
def specified_month(self, trans, **kwd):
message = escape(util.restore_text(kwd.get("message", "")))
# If specified_date is not received, we'll default to the current month
specified_date = kwd.get("specified_date", datetime.utcnow().strftime("%Y-%m-%d"))
specified_month = specified_date[:7]
year, month = map(int, specified_month.split("-"))
start_date = date(year, month, 1)
end_date = start_date + timedelta(days=calendar.monthrange(year, month)[1])
month_label = start_date.strftime("%B")
year_label = start_date.strftime("%Y")
q = (
sa.select(
self.select_day(galaxy.model.User.table.c.create_time).label("date"),
sa.func.count(galaxy.model.User.table.c.id).label("num_users"),
)
.where(
sa.and_(
galaxy.model.User.table.c.create_time >= start_date,
galaxy.model.User.table.c.create_time < end_date,
)
)
.select_from(galaxy.model.User.table)
.group_by(self.group_by_day(galaxy.model.User.table.c.create_time))
.order_by(sa.desc("date"))
)
users = []
for row in trans.sa_session.execute(q):
users.append(
(row.date.strftime("%Y-%m-%d"), row.date.strftime("%d"), row.num_users, row.date.strftime("%A"))
)
return trans.fill_template(
"/webapps/reports/registered_users_specified_month.mako",
month_label=month_label,
year_label=year_label,
month=month,
users=users,
message=message,
)
[docs] @web.expose
def specified_date(self, trans, **kwd):
message = escape(util.restore_text(kwd.get("message", "")))
# If specified_date is not received, we'll default to the current month
specified_date = kwd.get("specified_date", datetime.utcnow().strftime("%Y-%m-%d"))
year, month, day = map(int, specified_date.split("-"))
start_date = date(year, month, day)
end_date = start_date + timedelta(days=1)
day_of_month = start_date.strftime("%d")
day_label = start_date.strftime("%A")
month_label = start_date.strftime("%B")
year_label = start_date.strftime("%Y")
q = (
sa.select(
self.select_day(galaxy.model.User.table.c.create_time).label("date"), galaxy.model.User.table.c.email
)
.where(
sa.and_(
galaxy.model.User.table.c.create_time >= start_date,
galaxy.model.User.table.c.create_time < end_date,
)
)
.select_from(galaxy.model.User.table)
.order_by(galaxy.model.User.table.c.email)
)
users = []
for row in trans.sa_session.execute(q):
users.append(row.email)
return trans.fill_template(
"/webapps/reports/registered_users_specified_date.mako",
specified_date=start_date,
day_label=day_label,
month_label=month_label,
year_label=year_label,
day_of_month=day_of_month,
users=users,
message=message,
)
[docs] @web.expose
def last_access_date(self, trans, **kwd):
message = escape(util.restore_text(kwd.get("message", "")))
specs = sorter("one", kwd)
sort_id = specs.sort_id
order = specs.order
arrow = specs.arrow
def name_to_num(name):
num = None
if name is not None and name.lower() == "zero":
num = 0
else:
num = 1
return num
if order == "desc":
_order = True
else:
_order = False
days_not_logged_in = kwd.get("days_not_logged_in", 90)
if not days_not_logged_in:
days_not_logged_in = 0
cutoff_time = datetime.utcnow() - timedelta(days=int(days_not_logged_in))
users = []
stmt = select(galaxy.model.User).filter(galaxy.model.User.deleted == false()).order_by(galaxy.model.User.email)
for user in trans.sa_session.scalars(stmt).all():
current_galaxy_session = user.current_galaxy_session
if current_galaxy_session:
last_galaxy_session = current_galaxy_session
if last_galaxy_session.update_time < cutoff_time:
users.append((user.email, last_galaxy_session.update_time.strftime("%Y-%m-%d")))
else:
# The user has never logged in
users.append((user.email, "never logged in"))
users = sorted(users, key=operator.itemgetter(name_to_num(sort_id)), reverse=_order)
return trans.fill_template(
"/webapps/reports/users_last_access_date.mako",
order=order,
arrow=arrow,
sort_id=sort_id,
days_not_logged_in=days_not_logged_in,
users=users,
message=message,
)
[docs] @web.expose
def user_disk_usage(self, trans, **kwd):
message = escape(util.restore_text(kwd.get("message", "")))
specs = sorter("disk_usage", kwd)
sort_id = specs.sort_id
order = specs.order
arrow = specs.arrow
if order == "desc":
_order = True
else:
_order = False
user_cutoff = int(kwd.get("user_cutoff", 60))
# disk_usage isn't indexed
all_users = trans.sa_session.scalars(select(galaxy.model.User)).all()
sort_attrgetter = operator.attrgetter(str(sort_id))
users = sorted(all_users, key=lambda x: sort_attrgetter(x) or 0, reverse=_order)
if user_cutoff:
users = users[:user_cutoff]
return trans.fill_template(
"/webapps/reports/users_user_disk_usage.mako",
order=order,
arrow=arrow,
sort_id=sort_id,
users=users,
user_cutoff=user_cutoff,
message=message,
)
[docs] @web.expose
def history_per_user(self, trans, **kwd):
message = escape(util.restore_text(kwd.get("message", "")))
user_cutoff = int(kwd.get("user_cutoff", 60))
sorting = 0 if kwd.get("sorting", "User") == "User" else 1
descending = 1 if kwd.get("descending", "desc") == "desc" else -1
reverse = descending == 1
sort_keys = (lambda v: v[0].lower(), lambda v: v[1])
req = (
sa.select(
sa.func.count(galaxy.model.History.table.c.id).label("history"),
galaxy.model.User.table.c.username.label("username"),
)
.select_from(sa.outerjoin(galaxy.model.History.table, galaxy.model.User.table))
.where(galaxy.model.History.table.c.user_id == galaxy.model.User.table.c.id)
.group_by("username")
.order_by(sa.desc("username"), "history")
)
histories = [
(_.username if _.username is not None else "Unknown", _.history) for _ in trans.sa_session.execute(req)
]
histories.sort(key=sort_keys[sorting], reverse=reverse)
if user_cutoff != 0:
histories = histories[:user_cutoff]
return trans.fill_template(
"/webapps/reports/history_per_user.mako",
histories=histories,
user_cutoff=user_cutoff,
sorting=sorting,
descending=descending,
message=message,
)