def get_user_stats(self, section_id=None): if not session.allow_session_library(section_id): return [] monitor_db = database.MonitorDatabase() user_stats = [] try: if str(section_id).isdigit(): query = 'SELECT (CASE WHEN users.friendly_name IS NULL THEN users.username ' \ 'ELSE users.friendly_name END) AS friendly_name, users.user_id, users.thumb, COUNT(user) AS user_count ' \ 'FROM session_history ' \ 'JOIN session_history_metadata ON session_history_metadata.id = session_history.id ' \ 'JOIN users ON users.user_id = session_history.user_id ' \ 'WHERE section_id = ? ' \ 'GROUP BY users.user_id ' \ 'ORDER BY user_count DESC' result = monitor_db.select(query, args=[section_id]) else: result = [] except Exception as e: logger.warn(u"PlexPy Libraries :: Unable to execute database query for get_user_stats: %s." % e) result = [] for item in result: row = {'friendly_name': item['friendly_name'], 'user_id': item['user_id'], 'user_thumb': item['thumb'], 'total_plays': item['user_count'] } user_stats.append(row) return session.mask_session_info(user_stats, mask_metadata=False)
def get_user_stats(self, section_id=None): if not session.allow_session_library(section_id): return [] monitor_db = database.MonitorDatabase() user_stats = [] try: if str(section_id).isdigit(): query = 'SELECT (CASE WHEN users.friendly_name IS NULL OR TRIM(users.friendly_name) = "" ' \ 'THEN users.username ELSE users.friendly_name END) AS friendly_name, ' \ 'users.user_id, users.thumb, COUNT(user) AS user_count ' \ 'FROM session_history ' \ 'JOIN session_history_metadata ON session_history_metadata.id = session_history.id ' \ 'JOIN users ON users.user_id = session_history.user_id ' \ 'WHERE section_id = ? ' \ 'GROUP BY users.user_id ' \ 'ORDER BY user_count DESC' result = monitor_db.select(query, args=[section_id]) else: result = [] except Exception as e: logger.warn(u"PlexPy Libraries :: Unable to execute database query for get_user_stats: %s." % e) result = [] for item in result: row = {'friendly_name': item['friendly_name'], 'user_id': item['user_id'], 'user_thumb': item['thumb'], 'total_plays': item['user_count'] } user_stats.append(row) return session.mask_session_info(user_stats, mask_metadata=False)
def get_recently_watched(self, section_id=None, limit='10'): if not session.allow_session_library(section_id): return [] monitor_db = database.MonitorDatabase() recently_watched = [] if not limit.isdigit(): limit = '10' try: if str(section_id).isdigit(): query = 'SELECT session_history.id, session_history.media_type, ' \ 'session_history.rating_key, session_history.parent_rating_key, session_history.grandparent_rating_key, ' \ 'title, parent_title, grandparent_title, thumb, parent_thumb, grandparent_thumb, media_index, parent_media_index, ' \ 'year, started, user, content_rating, labels, section_id ' \ 'FROM session_history_metadata ' \ 'JOIN session_history ON session_history_metadata.id = session_history.id ' \ 'WHERE section_id = ? ' \ 'GROUP BY (CASE WHEN session_history.media_type = "track" THEN session_history.parent_rating_key ' \ ' ELSE session_history.rating_key END) ' \ 'ORDER BY started DESC LIMIT ?' result = monitor_db.select(query, args=[section_id, limit]) else: result = [] except Exception as e: logger.warn(u"PlexPy Libraries :: Unable to execute database query for get_recently_watched: %s." % e) result = [] for row in result: if row['media_type'] == 'episode' and row['parent_thumb']: thumb = row['parent_thumb'] elif row['media_type'] == 'episode': thumb = row['grandparent_thumb'] else: thumb = row['thumb'] recent_output = {'row_id': row['id'], 'media_type': row['media_type'], 'rating_key': row['rating_key'], 'parent_rating_key': row['parent_rating_key'], 'grandparent_rating_key': row['grandparent_rating_key'], 'title': row['title'], 'parent_title': row['parent_title'], 'grandparent_title': row['grandparent_title'], 'thumb': thumb, 'media_index': row['media_index'], 'parent_media_index': row['parent_media_index'], 'year': row['year'], 'time': row['started'], 'user': row['user'], 'section_id': row['section_id'], 'content_rating': row['content_rating'], 'labels': row['labels'].split(';') if row['labels'] else (), } recently_watched.append(recent_output) return session.mask_session_info(recently_watched)
def get_user_stats(self, section_id=None, grouping=None): if not session.allow_session_library(section_id): return [] if grouping is None: grouping = plexpy.CONFIG.GROUP_HISTORY_TABLES monitor_db = database.MonitorDatabase() user_stats = [] group_by = 'session_history.reference_id' if grouping else 'session_history.id' try: if str(section_id).isdigit(): query = 'SELECT (CASE WHEN users.friendly_name IS NULL OR TRIM(users.friendly_name) = "" ' \ 'THEN users.username ELSE users.friendly_name END) AS friendly_name, ' \ 'users.user_id, users.thumb, users.custom_avatar_url AS custom_thumb, ' \ 'COUNT(DISTINCT %s) AS user_count ' \ 'FROM session_history ' \ 'JOIN session_history_metadata ON session_history_metadata.id = session_history.id ' \ 'JOIN users ON users.user_id = session_history.user_id ' \ 'WHERE section_id = ? ' \ 'GROUP BY users.user_id ' \ 'ORDER BY user_count DESC' % group_by result = monitor_db.select(query, args=[section_id]) else: result = [] except Exception as e: logger.warn( "Tautulli Libraries :: Unable to execute database query for get_user_stats: %s." % e) result = [] for item in result: if item['custom_thumb'] and item['custom_thumb'] != item['thumb']: user_thumb = item['custom_thumb'] elif item['thumb']: user_thumb = item['thumb'] else: user_thumb = common.DEFAULT_USER_THUMB row = { 'friendly_name': item['friendly_name'], 'user_id': item['user_id'], 'user_thumb': user_thumb, 'total_plays': item['user_count'] } user_stats.append(row) return session.mask_session_info(user_stats, mask_metadata=False)
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 = [['library_sections.deleted_section', 0]] if session.get_session_shared_libraries(): custom_where.append([ 'library_sections.section_id', session.get_session_shared_libraries() ]) columns = [ 'library_sections.section_id', 'library_sections.section_name', 'library_sections.section_type', 'library_sections.count', 'library_sections.parent_count', 'library_sections.child_count', 'library_sections.thumb AS library_thumb', 'library_sections.custom_thumb_url AS custom_thumb', 'library_sections.art', '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_accessed', 'MAX(session_history.id) AS id', 'session_history_metadata.full_title AS last_played', '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_metadata.content_rating', 'session_history_metadata.labels', 'library_sections.do_notify', 'library_sections.do_notify_created', 'library_sections.keep_history' ] try: query = data_tables.ssp_query( table_name='library_sections', columns=columns, custom_where=custom_where, group_by=[ 'library_sections.server_id', 'library_sections.section_id' ], join_types=[ 'LEFT OUTER JOIN', 'LEFT OUTER JOIN', 'LEFT OUTER JOIN' ], join_tables=[ 'session_history_metadata', 'session_history', 'session_history_media_info' ], join_evals=[[ 'session_history_metadata.section_id', 'library_sections.section_id' ], ['session_history_metadata.id', 'session_history.id'], [ 'session_history_metadata.id', 'session_history_media_info.id' ]], kwargs=kwargs) except Exception as e: logger.warn( u"PlexPy Libraries :: Unable to execute database query for get_list: %s." % e) return default_return result = query['result'] rows = [] for item in result: 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[ 'library_thumb']: library_thumb = item['custom_thumb'] elif item['library_thumb']: library_thumb = item['library_thumb'] else: library_thumb = common.DEFAULT_COVER_THUMB row = { 'section_id': item['section_id'], 'section_name': item['section_name'], 'section_type': item['section_type'].capitalize(), 'count': item['count'], 'parent_count': item['parent_count'], 'child_count': item['child_count'], 'library_thumb': library_thumb, 'library_art': item['art'], 'plays': item['plays'], 'duration': item['duration'], 'last_accessed': item['last_accessed'], 'id': item['id'], 'last_played': item['last_played'], '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'], 'content_rating': item['content_rating'], 'labels': item['labels'].split(';') if item['labels'] else (), 'do_notify': helpers.checked(item['do_notify']), 'do_notify_created': helpers.checked(item['do_notify_created']), 'keep_history': helpers.checked(item['keep_history']) } rows.append(row) dict = { 'recordsFiltered': query['filteredCount'], 'recordsTotal': query['totalCount'], 'data': session.mask_session_info(rows), 'draw': query['draw'] } return dict
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 = [['library_sections.deleted_section', 0]] if session.get_session_shared_libraries(): custom_where.append(['library_sections.section_id', session.get_session_shared_libraries()]) columns = ['library_sections.section_id', 'library_sections.section_name', 'library_sections.section_type', 'library_sections.count', 'library_sections.parent_count', 'library_sections.child_count', 'library_sections.thumb AS library_thumb', 'library_sections.custom_thumb_url AS custom_thumb', 'library_sections.art', '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_accessed', 'MAX(session_history.id) AS id', 'session_history_metadata.full_title AS last_played', '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_metadata.content_rating', 'session_history_metadata.labels', 'library_sections.do_notify', 'library_sections.do_notify_created', 'library_sections.keep_history' ] try: query = data_tables.ssp_query(table_name='library_sections', columns=columns, custom_where=custom_where, group_by=['library_sections.server_id', 'library_sections.section_id'], join_types=['LEFT OUTER JOIN', 'LEFT OUTER JOIN', 'LEFT OUTER JOIN'], join_tables=['session_history_metadata', 'session_history', 'session_history_media_info'], join_evals=[['session_history_metadata.section_id', 'library_sections.section_id'], ['session_history_metadata.id', 'session_history.id'], ['session_history_metadata.id', 'session_history_media_info.id']], kwargs=kwargs) except Exception as e: logger.warn(u"PlexPy Libraries :: Unable to execute database query for get_list: %s." % e) return default_return result = query['result'] rows = [] for item in result: 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['library_thumb']: library_thumb = item['custom_thumb'] elif item['library_thumb']: library_thumb = item['library_thumb'] else: library_thumb = common.DEFAULT_COVER_THUMB row = {'section_id': item['section_id'], 'section_name': item['section_name'], 'section_type': item['section_type'].capitalize(), 'count': item['count'], 'parent_count': item['parent_count'], 'child_count': item['child_count'], 'library_thumb': library_thumb, 'library_art': item['art'], 'plays': item['plays'], 'duration': item['duration'], 'last_accessed': item['last_accessed'], 'id': item['id'], 'last_played': item['last_played'], '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'], 'content_rating': item['content_rating'], 'labels': item['labels'].split(';') if item['labels'] else (), 'do_notify': helpers.checked(item['do_notify']), 'do_notify_created': helpers.checked(item['do_notify_created']), 'keep_history': helpers.checked(item['keep_history']) } rows.append(row) dict = {'recordsFiltered': query['filteredCount'], 'recordsTotal': query['totalCount'], 'data': session.mask_session_info(rows), 'draw': query['draw'] } return dict