Esempio n. 1
0
    def get_datatables_list(self, kwargs=None):
        default_return = {
            'recordsFiltered': 0,
            'recordsTotal': 0,
            'draw': 0,
            'data': 'null',
            'error': 'Unable to execute database query.'
        }

        data_tables = datatables.DataTables()

        custom_where = [['users.deleted_user', 0]]

        if session.get_session_user_id():
            custom_where.append(
                ['users.user_id',
                 session.get_session_user_id()])

        if kwargs.get('user_id'):
            custom_where.append(['users.user_id', kwargs.get('user_id')])

        columns = [
            'users.user_id',
            '(CASE WHEN users.friendly_name IS NULL OR TRIM(users.friendly_name) = "" \
                    THEN users.username ELSE users.friendly_name END) AS friendly_name',
            'users.thumb AS user_thumb',
            'users.custom_avatar_url AS custom_thumb',
            'COUNT(session_history.id) AS plays',
            'SUM(CASE WHEN session_history.stopped > 0 THEN (session_history.stopped - session_history.started) \
                    ELSE 0 END) - SUM(CASE WHEN session_history.paused_counter IS NULL THEN 0 ELSE \
                    session_history.paused_counter END) AS duration',
            'MAX(session_history.started) AS last_seen',
            'MAX(session_history.id) AS id',
            'session_history_metadata.full_title AS last_played',
            'session_history.ip_address', 'session_history.platform',
            'session_history.player', 'session_history.rating_key',
            'session_history_metadata.media_type',
            'session_history_metadata.thumb',
            'session_history_metadata.parent_thumb',
            'session_history_metadata.grandparent_thumb',
            'session_history_metadata.parent_title',
            'session_history_metadata.year',
            'session_history_metadata.media_index',
            'session_history_metadata.parent_media_index',
            'session_history_media_info.transcode_decision',
            'users.do_notify as do_notify',
            'users.keep_history as keep_history',
            'users.allow_guest as allow_guest'
        ]
        try:
            query = data_tables.ssp_query(
                table_name='users',
                columns=columns,
                custom_where=custom_where,
                group_by=['users.user_id'],
                join_types=[
                    'LEFT OUTER JOIN', 'LEFT OUTER JOIN', 'LEFT OUTER JOIN'
                ],
                join_tables=[
                    'session_history', 'session_history_metadata',
                    'session_history_media_info'
                ],
                join_evals=[
                    ['session_history.user_id', 'users.user_id'],
                    ['session_history.id', 'session_history_metadata.id'],
                    ['session_history.id', 'session_history_media_info.id']
                ],
                kwargs=kwargs)
        except Exception as e:
            logger.warn(
                u"Tautulli Users :: Unable to execute database query for get_list: %s."
                % e)
            return default_return

        users = query['result']

        rows = []
        for item in users:
            if item['media_type'] == 'episode' and item['parent_thumb']:
                thumb = item['parent_thumb']
            elif item['media_type'] == 'episode':
                thumb = item['grandparent_thumb']
            else:
                thumb = item['thumb']

            if item['custom_thumb'] and item['custom_thumb'] != item[
                    'user_thumb']:
                user_thumb = item['custom_thumb']
            elif item['user_thumb']:
                user_thumb = item['user_thumb']
            else:
                user_thumb = common.DEFAULT_USER_THUMB

            # Rename Mystery platform names
            platform = common.PLATFORM_NAME_OVERRIDES.get(
                item['platform'], item['platform'])

            row = {
                'user_id': item['user_id'],
                'friendly_name': item['friendly_name'],
                'user_thumb': user_thumb,
                'plays': item['plays'],
                'duration': item['duration'],
                'last_seen': item['last_seen'],
                'last_played': item['last_played'],
                'id': item['id'],
                'ip_address': item['ip_address'],
                'platform': platform,
                'player': item['player'],
                'rating_key': item['rating_key'],
                'media_type': item['media_type'],
                'thumb': thumb,
                'parent_title': item['parent_title'],
                'year': item['year'],
                'media_index': item['media_index'],
                'parent_media_index': item['parent_media_index'],
                'transcode_decision': item['transcode_decision'],
                'do_notify': helpers.checked(item['do_notify']),
                'keep_history': helpers.checked(item['keep_history']),
                'allow_guest': helpers.checked(item['allow_guest'])
            }

            rows.append(row)

        dict = {
            'recordsFiltered': query['filteredCount'],
            'recordsTotal': query['totalCount'],
            'data': session.friendly_name_to_username(rows),
            'draw': query['draw']
        }

        return dict
Esempio n. 2
0
    def get_datatables_user_login(self, user_id=None, kwargs=None):
        default_return = {
            'recordsFiltered': 0,
            'recordsTotal': 0,
            'draw': 0,
            'data': []
        }

        if not session.allow_session_user(user_id):
            return default_return

        data_tables = datatables.DataTables()

        if session.get_session_user_id():
            custom_where = [[
                'user_login.user_id',
                session.get_session_user_id()
            ]]
        else:
            custom_where = [['user_login.user_id', user_id]] if user_id else []

        columns = [
            'user_login.timestamp', 'user_login.user_id', 'user_login.user',
            'user_login.user_group', 'user_login.ip_address',
            'user_login.host', 'user_login.user_agent', 'user_login.success',
            '(CASE WHEN users.friendly_name IS NULL OR TRIM(users.friendly_name) = "" \
                    THEN users.username ELSE users.friendly_name END) AS friendly_name'
        ]

        try:
            query = data_tables.ssp_query(
                table_name='user_login',
                columns=columns,
                custom_where=custom_where,
                group_by=[],
                join_types=['LEFT OUTER JOIN'],
                join_tables=['users'],
                join_evals=[['user_login.user_id', 'users.user_id']],
                kwargs=kwargs)
        except Exception as e:
            logger.warn(
                "Tautulli Users :: Unable to execute database query for get_datatables_user_login: %s."
                % e)
            return default_return

        results = query['result']

        rows = []
        for item in results:
            (os, browser) = httpagentparser.simple_detect(item['user_agent'])

            row = {
                'timestamp': item['timestamp'],
                'user_id': item['user_id'],
                'user_group': item['user_group'],
                'ip_address': item['ip_address'],
                'host': item['host'],
                'user_agent': item['user_agent'],
                'os': os,
                'browser': browser,
                'success': item['success'],
                'friendly_name': item['friendly_name'] or item['user']
            }

            rows.append(row)

        dict = {
            'recordsFiltered': query['filteredCount'],
            'recordsTotal': query['totalCount'],
            'data': session.friendly_name_to_username(rows),
            'draw': query['draw']
        }

        return dict
Esempio n. 3
0
        def get_user_details(user_id=user_id, user=user, email=email):
            monitor_db = database.MonitorDatabase()

            try:
                if str(user_id).isdigit():
                    query = 'SELECT user_id, username, friendly_name, thumb AS user_thumb, custom_avatar_url AS custom_thumb, ' \
                            'email, is_admin, is_home_user, is_allow_sync, is_restricted, do_notify, keep_history, deleted_user, ' \
                            'allow_guest, shared_libraries ' \
                            'FROM users ' \
                            'WHERE user_id = ? '
                    result = monitor_db.select(query, args=[user_id])
                elif user:
                    query = 'SELECT user_id, username, friendly_name, thumb AS user_thumb, custom_avatar_url AS custom_thumb, ' \
                            'email, is_admin, is_home_user, is_allow_sync, is_restricted, do_notify, keep_history, deleted_user, ' \
                            'allow_guest, shared_libraries ' \
                            'FROM users ' \
                            'WHERE username = ? COLLATE NOCASE '
                    result = monitor_db.select(query, args=[user])
                elif email:
                    query = 'SELECT user_id, username, friendly_name, thumb AS user_thumb, custom_avatar_url AS custom_thumb, ' \
                            'email, is_admin, is_home_user, is_allow_sync, is_restricted, do_notify, keep_history, deleted_user, ' \
                            'allow_guest, shared_libraries ' \
                            'FROM users ' \
                            'WHERE email = ? COLLATE NOCASE '
                    result = monitor_db.select(query, args=[email])
                else:
                    result = []
            except Exception as e:
                logger.warn(
                    u"Tautulli Users :: Unable to execute database query for get_details: %s."
                    % e)
                result = []

            user_details = {}
            if result:
                for item in result:
                    if session.get_session_user_id():
                        friendly_name = session.get_session_user()
                    elif item['friendly_name']:
                        friendly_name = item['friendly_name']
                    else:
                        friendly_name = item['username']

                    if item['custom_thumb'] and item['custom_thumb'] != item[
                            'user_thumb']:
                        user_thumb = item['custom_thumb']
                    elif item['user_thumb']:
                        user_thumb = item['user_thumb']
                    else:
                        user_thumb = common.DEFAULT_USER_THUMB

                    shared_libraries = tuple(item['shared_libraries'].split(
                        ';')) if item['shared_libraries'] else ()

                    user_details = {
                        'user_id': item['user_id'],
                        'username': item['username'],
                        'friendly_name': friendly_name,
                        'user_thumb': user_thumb,
                        'email': item['email'],
                        'is_admin': item['is_admin'],
                        'is_home_user': item['is_home_user'],
                        'is_allow_sync': item['is_allow_sync'],
                        'is_restricted': item['is_restricted'],
                        'do_notify': item['do_notify'],
                        'keep_history': item['keep_history'],
                        'deleted_user': item['deleted_user'],
                        'allow_guest': item['allow_guest'],
                        'shared_libraries': shared_libraries
                    }
            return user_details
Esempio n. 4
0
    def get_user_details(self,
                         user_id=None,
                         user=None,
                         email=None,
                         include_last_seen=False):
        last_seen = 'NULL'
        join = ''
        if include_last_seen:
            last_seen = 'MAX(session_history.started)'
            join = 'LEFT OUTER JOIN session_history ON users.user_id = session_history.user_id'

        monitor_db = database.MonitorDatabase()

        try:
            if str(user_id).isdigit():
                where = 'users.user_id = ?'
                args = [user_id]
            elif user:
                where = 'users.username = ?'
                args = [user]
            elif email:
                where = 'users.email = ?'
                args = [email]
            else:
                raise Exception('Missing user_id, username, or email')

            query = 'SELECT users.id AS row_id, users.user_id, username, friendly_name, ' \
                    'thumb AS user_thumb, custom_avatar_url AS custom_thumb, ' \
                    'email, is_active, is_admin, is_home_user, is_allow_sync, is_restricted, ' \
                    'do_notify, keep_history, deleted_user, ' \
                    'allow_guest, shared_libraries, %s AS last_seen ' \
                    'FROM users %s ' \
                    'WHERE %s COLLATE NOCASE' % (last_seen, join, where)
            result = monitor_db.select(query, args=args)
        except Exception as e:
            logger.warn(
                "Tautulli Users :: Unable to execute database query for get_user_details: %s."
                % e)
            result = []

        user_details = {}
        if result:
            for item in result:
                if session.get_session_user_id():
                    friendly_name = session.get_session_user()
                elif item['friendly_name']:
                    friendly_name = item['friendly_name']
                else:
                    friendly_name = item['username']

                if item['custom_thumb'] and item['custom_thumb'] != item[
                        'user_thumb']:
                    user_thumb = item['custom_thumb']
                elif item['user_thumb']:
                    user_thumb = item['user_thumb']
                else:
                    user_thumb = common.DEFAULT_USER_THUMB

                shared_libraries = tuple(item['shared_libraries'].split(
                    ';')) if item['shared_libraries'] else ()

                user_details = {
                    'row_id': item['row_id'],
                    'user_id': item['user_id'],
                    'username': item['username'],
                    'friendly_name': friendly_name,
                    'user_thumb': user_thumb,
                    'email': item['email'],
                    'is_active': item['is_active'],
                    'is_admin': item['is_admin'],
                    'is_home_user': item['is_home_user'],
                    'is_allow_sync': item['is_allow_sync'],
                    'is_restricted': item['is_restricted'],
                    'do_notify': item['do_notify'],
                    'keep_history': item['keep_history'],
                    'deleted_user': item['deleted_user'],
                    'allow_guest': item['allow_guest'],
                    'shared_libraries': shared_libraries,
                    'last_seen': item['last_seen']
                }
        return user_details