Source code for galaxy.webapps.reports.controllers.users

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