Source code for uw.local.grad.db.cursor

"""Cursor class for Graduate Admissions.

Inherits from the usual cursor class with added methods for commonly-needed
queries.
"""

from uw.local.dbtools import Cursor
from uw.sql.dbapi import make_execute_optional_tuple, make_execute_tuples, make_execute_values, make_execute_required_value

class Cursor (Cursor):
    unitapp_by_id = make_execute_optional_tuple ("select * from work_application natural left join (work_application_status_current natural join work_application_status_code) where appl_id=%(appl_id)s")
    """Get unitapp database information for a single appl_id."""

    unitapps_by_ids = make_execute_tuples ("select * from work_application natural left join (work_application_status_current natural join work_application_status_code) where appl_id=ANY(%(appl_ids)s) ORDER BY appl_id")
    """Get unitapp database information for multiple appl_ids."""

    _user_unitapp_roles = make_execute_values ("select role_id from work_application natural join work_role_assignment natural join uw_unit where (appl_id, person_id)=(%(appl_id)s, %(person_id)s)")
    """Internal function to get the database information for a given person_id."""

    def user_unitapp_roles (cursor, person_id, unitapp, roles=None):
        """Get the roles for the user with the given person_id and appl_id in the unitapp.

        :param cursor: database cursor
        :param person_id: faculty person id
        :param unitapp: a given unit application to check the roles on
        :param roles: roles to add to the other user
        :type unitapp: UnitApplication
        """
        result = set (cursor._user_unitapp_roles (appl_id=unitapp.appl_id, person_id=person_id))
        if roles is None:
            return result
        else:
            return result & set (roles)

    _user_unit_roles = make_execute_values ("select role_id from work_role_assignment natural join uw_unit where (unit_code, person_id)=(%(unit_code)s, %(person_id)s)")
    """Get the role_id for the given unit_code and person_id."""

    def user_unit_roles (cursor, person_id, unit_code, roles=None):
        """Get the roles for the user with the given person_id and unit_code

        :param cursor: database cursor
        :param person_id: faculty person id
        :param unit_code: a unit code for a faculty (e.g. CS)
        :param roles: roles to add to the other roles
        """
        result = set (cursor._user_unit_roles (unit_code=unit_code, person_id=person_id))
        if roles is None:
            return result
        else:
            return result & set (roles)

    user_has_role = make_execute_required_value ("select exists (select from _grad.work_role_assignment where role_id = any (%(role_codes)s) and person_id = %(person_id)s)")
    """Return whether a given person_id has a role_id."""