def get_user_list(self, kwargs=None):
data_tables = datatables.DataTables()
columns = ['users.user_id as user_id',
'users.custom_avatar_url as thumb',
'(case when users.friendly_name is null then users.username else \
users.friendly_name end) as friendly_name',
'MAX(session_history.started) as last_seen',
'session_history.ip_address as ip_address',
'COUNT(session_history.id) as plays',
'users.username as user'
]
try:
query = data_tables.ssp_query(table_name='users',
columns=columns,
custom_where=[],
group_by=['users.user_id'],
join_types=['LEFT OUTER JOIN'],
join_tables=['session_history'],
join_evals=[['session_history.user_id', 'users.user_id']],
kwargs=kwargs)
except:
logger.warn("Unable to execute database query.")
return {'recordsFiltered': 0,
'recordsTotal': 0,
'draw': 0,
'data': 'null',
'error': 'Unable to execute database query.'}
users = query['result']
rows = []
for item in users:
if not item['thumb'] or item['thumb'] == '':
user_thumb = common.DEFAULT_USER_THUMB
else:
user_thumb = item['thumb']
row = {"plays": item['plays'],
"last_seen": item['last_seen'],
"friendly_name": item["friendly_name"],
"ip_address": item["ip_address"],
"thumb": user_thumb,
"user": item["user"],
"user_id": item['user_id']
}
rows.append(row)
dict = {'recordsFiltered': query['filteredCount'],
'recordsTotal': query['totalCount'],
'data': rows,
'draw': query['draw']
}
return dict
def get_user_unique_ips(self, kwargs=None, custom_where=None):
data_tables = datatables.DataTables()
columns = ['session_history.started as last_seen',
'session_history.ip_address as ip_address',
'COUNT(session_history.id) as play_count',
'session_history.player as platform',
'session_history_metadata.full_title as last_watched',
'session_history.user as user',
'session_history.user_id as user_id'
]
try:
query = data_tables.ssp_query(table_name='session_history',
columns=columns,
custom_where=custom_where,
group_by=['ip_address'],
join_types=['JOIN'],
join_tables=['session_history_metadata'],
join_evals=[['session_history.id', 'session_history_metadata.id']],
kwargs=kwargs)
except:
logger.warn("Unable to execute database query.")
return {'recordsFiltered': 0,
'recordsTotal': 0,
'draw': 0,
'data': 'null',
'error': 'Unable to execute database query.'}
results = query['result']
rows = []
for item in results:
row = {"last_seen": item['last_seen'],
"ip_address": item['ip_address'],
"play_count": item['play_count'],
"platform": item['platform'],
"last_watched": item['last_watched']
}
rows.append(row)
dict = {'recordsFiltered': query['filteredCount'],
'recordsTotal': query['totalCount'],
'data': rows,
'draw': query['draw']
}
return dict
def get_datatables_unique_ips(self, user_id=None, kwargs=None):
default_return = {
'recordsFiltered': 0,
'recordsTotal': 0,
'draw': 0,
'data': 'null',
'error': 'Unable to execute database query.'
}
if not session.allow_session_user(user_id):
return default_return
data_tables = datatables.DataTables()
custom_where = ['users.user_id', user_id]
columns = [
'session_history.id', 'session_history.started AS last_seen',
'session_history.ip_address',
'COUNT(session_history.id) AS play_count',
'session_history.platform', 'session_history.player',
'session_history.rating_key',
'session_history_metadata.full_title AS last_played',
'session_history_metadata.thumb',
'session_history_metadata.parent_thumb',
'session_history_metadata.grandparent_thumb',
'session_history_metadata.media_type',
'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',
'session_history.user',
'session_history.user_id as custom_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'
]
try:
query = data_tables.ssp_query(
table_name='session_history',
columns=columns,
custom_where=[custom_where],
group_by=['ip_address'],
join_types=['JOIN', 'JOIN', 'JOIN'],
join_tables=[
'users', '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_unique_ips: %s."
% e)
return default_return
results = query['result']
rows = []
for item in results:
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"]
# Rename Mystery platform names
platform = common.PLATFORM_NAME_OVERRIDES.get(
item["platform"], item["platform"])
row = {
'id': item['id'],
'last_seen': item['last_seen'],
'ip_address': item['ip_address'],
'play_count': item['play_count'],
'platform': platform,
'player': item['player'],
'last_played': item['last_played'],
'rating_key': item['rating_key'],
'thumb': thumb,
'media_type': item['media_type'],
'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'],
'friendly_name': item['friendly_name'],
'user_id': item['custom_user_id']
}
rows.append(row)
dict = {
'recordsFiltered': query['filteredCount'],
'recordsTotal': query['totalCount'],
'data': session.friendly_name_to_username(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 = [['users.deleted_user', 0]]
if session.get_session_user_id() and int(
session.get_session_access_level()) < 3:
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',
'servers.pms_name AS last_server', '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',
'LEFT OUTER JOIN'
],
join_tables=[
'session_history', 'session_history_metadata',
'session_history_media_info', 'servers'
],
join_evals=[
['session_history.user_id', 'users.user_id'],
['session_history.id', 'session_history_metadata.id'],
['session_history.id', 'session_history_media_info.id'],
['session_history.server_id', 'servers.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'],
'last_server': item['last_server'],
'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': 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
def get_datatables_user_login(self, user_id=None, kwargs=None):
default_return = {
'recordsFiltered': 0,
'recordsTotal': 0,
'draw': 0,
'data': 'null',
'error': 'Unable to execute database query.'
}
if not session.allow_session_user(user_id):
return default_return
data_tables = datatables.DataTables()
if session.get_session_user_id() and int(
session.get_session_access_level()) < 5:
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(
u"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
def get_user_list(self, kwargs=None):
data_tables = datatables.DataTables()
columns = [
'session_history.id', 'users.user_id as user_id',
'users.custom_avatar_url as user_thumb',
'(case when users.friendly_name is null then users.username else \
users.friendly_name end) as friendly_name',
'MAX(session_history.started) as last_seen',
'session_history.ip_address as ip_address',
'COUNT(session_history.id) as plays',
'session_history.platform as platform',
'session_history.player as player',
'session_history_metadata.full_title as last_watched',
'session_history_metadata.thumb',
'session_history_metadata.parent_thumb',
'session_history_metadata.grandparent_thumb',
'session_history_metadata.media_type',
'session_history.rating_key as rating_key',
'session_history_media_info.video_decision',
'users.username as user', 'users.do_notify as do_notify',
'users.keep_history as keep_history'
]
try:
query = data_tables.ssp_query(
table_name='users',
columns=columns,
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:
logger.warn("Unable to execute database query.")
return {
'recordsFiltered': 0,
'recordsTotal': 0,
'draw': 0,
'data': 'null',
'error': 'Unable to execute database query.'
}
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 not item['user_thumb'] or item['user_thumb'] == '':
user_thumb = common.DEFAULT_USER_THUMB
else:
user_thumb = item['user_thumb']
# Rename Mystery platform names
platform_names = {
'Mystery 3': 'Playstation 3',
'Mystery 4': 'Playstation 4',
'Mystery 5': 'Xbox 360'
}
platform = platform_names.get(item["platform"], item["platform"])
row = {
"id": item['id'],
"plays": item['plays'],
"last_seen": item['last_seen'],
"friendly_name": item['friendly_name'],
"ip_address": item['ip_address'],
"platform": platform,
"player": item['player'],
"last_watched": item['last_watched'],
"thumb": thumb,
"media_type": item['media_type'],
"rating_key": item['rating_key'],
"video_decision": item['video_decision'],
"user_thumb": user_thumb,
"user": item["user"],
"user_id": item['user_id'],
"do_notify": helpers.checked(item['do_notify']),
"keep_history": helpers.checked(item['keep_history'])
}
rows.append(row)
dict = {
'recordsFiltered': query['filteredCount'],
'recordsTotal': query['totalCount'],
'data': rows,
'draw': query['draw']
}
return dict
def get_user_unique_ips(self, kwargs=None, custom_where=None):
data_tables = datatables.DataTables()
# Change custom_where column name due to ambiguous column name after JOIN
custom_where[0][0] = 'custom_user_id' if custom_where[0][
0] == 'user_id' else custom_where[0][0]
columns = [
'session_history.id', 'session_history.started as last_seen',
'session_history.ip_address as ip_address',
'COUNT(session_history.id) as play_count',
'session_history.platform as platform',
'session_history.player as player',
'session_history_metadata.full_title as last_watched',
'session_history_metadata.thumb',
'session_history_metadata.parent_thumb',
'session_history_metadata.grandparent_thumb',
'session_history_metadata.media_type',
'session_history.rating_key as rating_key',
'session_history_media_info.video_decision',
'session_history.user as user',
'session_history.user_id as custom_user_id',
'(case when users.friendly_name is null then users.username else \
users.friendly_name end) as friendly_name'
]
try:
query = data_tables.ssp_query(
table_name='session_history',
columns=columns,
custom_where=custom_where,
group_by=['ip_address'],
join_types=['JOIN', 'JOIN', 'JOIN'],
join_tables=[
'users', '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:
logger.warn("Unable to execute database query.")
return {
'recordsFiltered': 0,
'recordsTotal': 0,
'draw': 0,
'data': 'null',
'error': 'Unable to execute database query.'
}
results = query['result']
rows = []
for item in results:
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"]
# Rename Mystery platform names
platform_names = {
'Mystery 3': 'Playstation 3',
'Mystery 4': 'Playstation 4',
'Mystery 5': 'Xbox 360'
}
platform = platform_names.get(item["platform"], item["platform"])
row = {
"id": item['id'],
"last_seen": item['last_seen'],
"ip_address": item['ip_address'],
"play_count": item['play_count'],
"platform": platform,
"player": item['player'],
"last_watched": item['last_watched'],
"thumb": thumb,
"media_type": item['media_type'],
"rating_key": item['rating_key'],
"video_decision": item['video_decision'],
"friendly_name": item['friendly_name']
}
rows.append(row)
dict = {
'recordsFiltered': query['filteredCount'],
'recordsTotal': query['totalCount'],
'data': rows,
'draw': query['draw']
}
return dict
def get_history(self, kwargs=None, custom_where=None):
data_tables = datatables.DataTables()
columns = [
'session_history.id', 'session_history.started as date',
'(CASE WHEN users.friendly_name IS NULL THEN session_history'
'.user ELSE users.friendly_name END) as friendly_name',
'session_history.player', 'session_history.ip_address',
'session_history_metadata.full_title as full_title',
'session_history_metadata.thumb',
'session_history_metadata.parent_thumb',
'session_history_metadata.grandparent_thumb',
'session_history_metadata.media_index',
'session_history_metadata.parent_media_index',
'session_history_metadata.parent_title',
'session_history_metadata.year', 'session_history.started',
'session_history.paused_counter', 'session_history.stopped',
'round((julianday(datetime(session_history.stopped, "unixepoch", "localtime")) - \
julianday(datetime(session_history.started, "unixepoch", "localtime"))) * 86400) - \
(CASE WHEN session_history.paused_counter IS NULL THEN 0 \
ELSE session_history.paused_counter END) as duration',
'((CASE WHEN session_history.view_offset IS NULL THEN 0.1 ELSE \
session_history.view_offset * 1.0 END) / \
(CASE WHEN session_history_metadata.duration IS NULL THEN 1.0 ELSE \
session_history_metadata.duration * 1.0 END) * 100) as percent_complete',
'session_history.grandparent_rating_key as grandparent_rating_key',
'session_history.rating_key as rating_key', 'session_history.user',
'session_history_metadata.media_type',
'session_history_media_info.video_decision',
'session_history.user_id as user_id'
]
try:
query = data_tables.ssp_query(
table_name='session_history',
columns=columns,
custom_where=custom_where,
group_by=[],
join_types=['LEFT OUTER JOIN', 'JOIN', 'JOIN'],
join_tables=[
'users', '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:
logger.warn("Unable to execute database query.")
return {
'recordsFiltered': 0,
'recordsTotal': 0,
'draw': 0,
'data': 'null',
'error': 'Unable to execute database query.'
}
history = query['result']
rows = []
for item in history:
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"]
row = {
"id": item['id'],
"date": item['date'],
"friendly_name": item['friendly_name'],
"player": item["player"],
"ip_address": item["ip_address"],
"full_title": item["full_title"],
"thumb": thumb,
"media_index": item["media_index"],
"parent_media_index": item["parent_media_index"],
"parent_title": item["parent_title"],
"year": item["year"],
"started": item["started"],
"paused_counter": item["paused_counter"],
"stopped": item["stopped"],
"duration": item["duration"],
"percent_complete": item["percent_complete"],
"grandparent_rating_key": item["grandparent_rating_key"],
"rating_key": item["rating_key"],
"user": item["user"],
"media_type": item["media_type"],
"video_decision": item["video_decision"],
"user_id": item["user_id"]
}
rows.append(row)
dict = {
'recordsFiltered': query['filteredCount'],
'recordsTotal': query['totalCount'],
'data': rows,
'draw': query['draw']
}
return dict
def get_datatables_list(self, kwargs=None):
data_tables = datatables.DataTables()
custom_where = ['library_sections.deleted_section', 0]
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_media_info.video_decision',
'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 {
'recordsFiltered': 0,
'recordsTotal': 0,
'draw': 0,
'data': 'null',
'error': 'Unable to execute database query.'
}
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'],
'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': rows,
'draw': query['draw']
}
return dict
def get_user_list(self, start='', length='', kwargs=None):
data_tables = datatables.DataTables()
start = int(start)
length = int(length)
filtered = []
totalcount = 0
search_value = ""
search_regex = ""
order_column = 1
order_dir = "desc"
if 'order[0][dir]' in kwargs:
order_dir = kwargs.get('order[0][dir]', "desc")
if 'order[0][column]' in kwargs:
order_column = kwargs.get('order[0][column]', 1)
if 'search[value]' in kwargs:
search_value = kwargs.get('search[value]', "")
if 'search[regex]' in kwargs:
search_regex = kwargs.get('search[regex]', "")
t = self.get_history_table_name()
columns = [
t + '.id', '(case when plexpy_users.friendly_name is null then ' +
t + '.user else plexpy_users.friendly_name end) as friendly_name',
t + '.time', t + '.ip_address', 'COUNT(' + t + '.title) as plays',
t + '.user', 'plexpy_users.user_id as user_id',
'plexpy_users.thumb as thumb'
]
try:
query = data_tables.ssp_query(
table_name=t,
columns=columns,
start=start,
length=length,
order_column=int(order_column),
order_dir=order_dir,
search_value=search_value,
search_regex=search_regex,
custom_where='',
group_by=(t + '.user'),
join_type='LEFT OUTER JOIN',
join_table='plexpy_users',
join_evals=[t + '.user', 'plexpy_users.username'],
kwargs=kwargs)
except:
logger.warn("Unable to open PlexWatch database.")
return {'recordsFiltered': 0, 'recordsTotal': 0, 'data': 'null'},
users = query['result']
rows = []
for item in users:
if not item['thumb'] or item['thumb'] == '':
user_thumb = common.DEFAULT_USER_THUMB
else:
user_thumb = item['thumb']
row = {
"plays": item['plays'],
"time": item['time'],
"friendly_name": item["friendly_name"],
"ip_address": item["ip_address"],
"thumb": user_thumb,
"user": item["user"],
"user_id": item['user_id']
}
rows.append(row)
dict = {
'recordsFiltered': query['filteredCount'],
'recordsTotal': query['totalCount'],
'data': rows,
}
return dict
def get_history(self, start='', length='', kwargs=None, custom_where=''):
data_tables = datatables.DataTables()
start = int(start)
length = int(length)
filtered = []
totalcount = 0
search_value = ""
search_regex = ""
order_column = 1
order_dir = "desc"
t = self.get_history_table_name()
if 'order[0][dir]' in kwargs:
order_dir = kwargs.get('order[0][dir]', "desc")
if 'order[0][column]' in kwargs:
order_column = kwargs.get('order[0][column]', "1")
if 'search[value]' in kwargs:
search_value = kwargs.get('search[value]', "")
if 'search[regex]' in kwargs:
search_regex = kwargs.get('search[regex]', "")
columns = [
t + '.id', t + '.time as date',
'(case when plexpy_users.friendly_name is null then ' + t +
'.user else plexpy_users.friendly_name end) as friendly_name',
t + '.platform', t + '.ip_address', t + '.title',
t + '.time as started', t + '.paused_counter', t + '.stopped',
'round((julianday(datetime(' + t +
'.stopped, "unixepoch", "localtime")) - \
julianday(datetime(' + t +
'.time, "unixepoch", "localtime"))) * 86400) - \
(case when ' + t + '.paused_counter is null then 0 else ' +
t + '.paused_counter end) as duration',
t + '.ratingKey as rating_key', t + '.xml', t + '.user',
t + '.grandparentRatingKey as grandparent_rating_key'
]
try:
query = data_tables.ssp_query(
table_name=t,
columns=columns,
start=start,
length=length,
order_column=int(order_column),
order_dir=order_dir,
search_value=search_value,
search_regex=search_regex,
custom_where=custom_where,
group_by='',
join_type='LEFT OUTER JOIN',
join_table='plexpy_users',
join_evals=[t + '.user', 'plexpy_users.username'],
kwargs=kwargs)
except:
logger.warn("Unable to open PlexWatch database.")
return {'recordsFiltered': 0, 'recordsTotal': 0, 'data': 'null'},
history = query['result']
rows = []
# NOTE: We are adding in a blank xml field in order enable the Datatables "searchable" parameter
for item in history:
row = {
"id": item['id'],
"date": item['date'],
"friendly_name": item['friendly_name'],
"platform": item["platform"],
"ip_address": item["ip_address"],
"title": item["title"],
"started": item["started"],
"paused_counter": item["paused_counter"],
"stopped": item["stopped"],
"rating_key": item["rating_key"],
"duration": item["duration"],
"percent_complete": 0,
"xml": "",
"user": item["user"]
}
if item['paused_counter'] > 0:
row['paused_counter'] = item['paused_counter']
else:
row['paused_counter'] = 0
if item['started']:
if item['stopped'] > 0:
stopped = item['stopped']
else:
stopped = 0
if item['paused_counter'] > 0:
paused_counter = item['paused_counter']
else:
paused_counter = 0
try:
xml_parse = minidom.parseString(
helpers.latinToAscii(item['xml']))
except:
logger.warn("Error parsing XML in PlexWatch db")
xml_head = xml_parse.getElementsByTagName('opt')
if not xml_head:
logger.warn("Error parsing XML in PlexWatch db.")
for s in xml_head:
if s.getAttribute('duration') and s.getAttribute('viewOffset'):
view_offset = helpers.cast_to_float(
s.getAttribute('viewOffset'))
duration = helpers.cast_to_float(
s.getAttribute('duration'))
if duration > 0:
row['percent_complete'] = (view_offset /
duration) * 100
else:
row['percent_complete'] = 0
rows.append(row)
dict = {
'recordsFiltered': query['filteredCount'],
'recordsTotal': query['totalCount'],
'data': rows,
}
return dict
def get_user_unique_ips(self,
start='',
length='',
kwargs=None,
custom_where=''):
data_tables = datatables.DataTables()
start = int(start)
length = int(length)
filtered = []
totalcount = 0
search_value = ""
search_regex = ""
order_column = 0
order_dir = "desc"
if 'order[0][dir]' in kwargs:
order_dir = kwargs.get('order[0][dir]', "desc")
if 'order[0][column]' in kwargs:
order_column = kwargs.get('order[0][column]', 1)
if 'search[value]' in kwargs:
search_value = kwargs.get('search[value]', "")
if 'search[regex]' in kwargs:
search_regex = kwargs.get('search[regex]', "")
t = self.get_history_table_name()
columns = [
t + '.time as last_seen', t + '.user', t + '.ip_address',
'COUNT(' + t + '.ip_address) as play_count', t + '.platform',
t + '.title as last_watched'
]
try:
query = data_tables.ssp_query(
table_name=self.get_history_table_name(),
columns=columns,
start=start,
length=length,
order_column=int(order_column),
order_dir=order_dir,
search_value=search_value,
search_regex=search_regex,
custom_where=custom_where,
group_by=(t + '.ip_address'),
join_type=None,
join_table=None,
join_evals=None,
kwargs=kwargs)
except:
logger.warn("Unable to open PlexWatch database.")
return {'recordsFiltered': 0, 'recordsTotal': 0, 'data': 'null'},
results = query['result']
rows = []
for item in results:
row = {
"last_seen": item['last_seen'],
"ip_address": item['ip_address'],
"play_count": item['play_count'],
"platform": item['platform'],
"last_watched": item['last_watched']
}
rows.append(row)
dict = {
'recordsFiltered': query['filteredCount'],
'recordsTotal': query['totalCount'],
'data': rows,
}
return dict
