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_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
