def get_user_friendly_name(self, user=None, user_id=None):
if user_id:
monitor_db = database.MonitorDatabase()
query = 'select username, ' \
'(CASE WHEN friendly_name IS NULL THEN username ELSE friendly_name END),' \
'do_notify, keep_history, custom_avatar_url as thumb ' \
'FROM users WHERE user_id = ?'
result = monitor_db.select(query, args=[user_id])
if result:
user_detail = {
'user_id': user_id,
'user': result[0][0],
'friendly_name': result[0][1],
'thumb': result[0][4],
'do_notify': helpers.checked(result[0][2]),
'keep_history': helpers.checked(result[0][3])
}
return user_detail
else:
user_detail = {
'user_id': user_id,
'user': '',
'friendly_name': '',
'do_notify': '',
'thumb': '',
'keep_history': ''
}
return user_detail
elif user:
monitor_db = database.MonitorDatabase()
query = 'select user_id, ' \
'(CASE WHEN friendly_name IS NULL THEN username ELSE friendly_name END),' \
'do_notify, keep_history, custom_avatar_url as thumb ' \
'FROM users WHERE username = ?'
result = monitor_db.select(query, args=[user])
if result:
user_detail = {
'user_id': result[0][0],
'user': user,
'friendly_name': result[0][1],
'thumb': result[0][4],
'do_notify': helpers.checked(result[0][2]),
'keep_history': helpers.checked(result[0][3])
}
return user_detail
else:
user_detail = {
'user_id': None,
'user': user,
'friendly_name': '',
'do_notify': '',
'thumb': '',
'keep_history': ''
}
return user_detail
return None
def get_user_stats(self, section_id=None):
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 user, 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 = {
'user': item['user'],
'user_id': item['user_id'],
'thumb': item['thumb'],
'total_plays': item['user_count']
}
user_stats.append(row)
return user_stats
def delete_all_history(self, section_id=None):
monitor_db = database.MonitorDatabase()
try:
if section_id.isdigit():
logger.info(
u"PlexPy Libraries :: Deleting all history for library id %s from database."
% section_id)
session_history_media_info_del = \
monitor_db.action('DELETE FROM '
'session_history_media_info '
'WHERE session_history_media_info.id IN (SELECT session_history_media_info.id '
'FROM session_history_media_info '
'JOIN session_history_metadata ON session_history_media_info.id = session_history_metadata.id '
'WHERE session_history_metadata.section_id = ?)', [section_id])
session_history_del = \
monitor_db.action('DELETE FROM '
'session_history '
'WHERE session_history.id IN (SELECT session_history.id '
'FROM session_history '
'JOIN session_history_metadata ON session_history.id = session_history_metadata.id '
'WHERE session_history_metadata.section_id = ?)', [section_id])
session_history_metadata_del = \
monitor_db.action('DELETE FROM '
'session_history_metadata '
'WHERE session_history_metadata.section_id = ?', [section_id])
return 'Deleted all items for section_id %s.' % section_id
else:
return 'Unable to delete items, section_id not valid.'
except Exception as e:
logger.warn(
u"PlexPy Libraries :: Unable to execute database query for delete_all_history: %s."
% e)
def set_user_login(self,
user_id=None,
user=None,
user_group=None,
ip_address=None,
host=None,
user_agent=None,
success=0):
if user_id is None or str(user_id).isdigit():
monitor_db = database.MonitorDatabase()
keys = {'timestamp': int(time.time()), 'user_id': user_id}
values = {
'user': user,
'user_group': user_group,
'ip_address': ip_address,
'host': host,
'user_agent': user_agent,
'success': success
}
try:
monitor_db.upsert(table_name='user_login',
key_dict=keys,
value_dict=values)
except Exception as e:
logger.warn(
u"Tautulli Users :: Unable to execute database query for set_login_log: %s."
% e)
def import_from_tautulli(import_database=None, import_ignore_interval=0):
logger.info(u"Tautulli Importer :: Data import from %s in progress..." %
import_database)
try:
import_db = sqlite3.connect(import_database, timeout=20)
import_db.row_factory = database.dict_factory
tautulli_db = database.MonitorDatabase()
account_list = import_users(import_db, tautulli_db)
import_servers(import_db, tautulli_db, import_ignore_interval)
notifier_lookup = import_notifiers(import_db, tautulli_db)
import_newsletters(import_db, tautulli_db, notifier_lookup)
logger.info(
u"Tautulli Importer :: Tautulli data import complete successfully."
)
import_db.close()
for user_id in account_list:
account = plexpy.PLEXTV_ACCOUNTS.reinit_account(user_id=user_id)
account.refresh_servers()
account.refresh_users()
plexpy.PMS_SERVERS.update_unowned_servers()
except Exception as e:
logger.error(
u"Tautulli Importer :: Failed to import tautulli database: %s" % e)
def add_mobile_device(device_id=None,
device_name=None,
device_token=None,
friendly_name=None):
db = database.MonitorDatabase()
keys = {'device_id': device_id}
values = {'device_name': device_name, 'device_token': device_token}
if friendly_name:
values['friendly_name'] = friendly_name
try:
result = db.upsert(table_name='mobile_devices',
key_dict=keys,
value_dict=values)
except Exception as e:
logger.warn(
u"Tautulli MobileApp :: Failed to register mobile device in the database: %s."
% e)
return
if result == 'insert':
logger.info(
u"Tautulli MobileApp :: Registered mobile device '%s' in the database."
% device_name)
else:
logger.debug(
u"Tautulli MobileApp :: Re-registered mobile device '%s' in the database."
% device_name)
return True
def set_mobile_device_config(mobile_device_id=None, **kwargs):
if str(mobile_device_id).isdigit():
mobile_device_id = int(mobile_device_id)
else:
logger.error(
u"Tautulli MobileApp :: Unable to set exisiting mobile device: invalid mobile_device_id %s."
% mobile_device_id)
return False
keys = {'id': mobile_device_id}
values = {}
if kwargs.get('friendly_name'):
values['friendly_name'] = kwargs['friendly_name']
db = database.MonitorDatabase()
try:
db.upsert(table_name='mobile_devices',
key_dict=keys,
value_dict=values)
logger.info(
u"Tautulli MobileApp :: Updated mobile device agent: mobile_device_id %s."
% mobile_device_id)
return True
except Exception as e:
logger.warn(
u"Tautulli MobileApp :: Unable to update mobile device: %s." % e)
return False
def get_watch_time_stats(self, user_id=None, grouping=None):
if not session.allow_session_user(user_id):
return []
if grouping is None:
grouping = plexpy.CONFIG.GROUP_HISTORY_TABLES
monitor_db = database.MonitorDatabase()
time_queries = [1, 7, 30, 0]
user_watch_time_stats = []
group_by = 'reference_id' if grouping else 'id'
for days in time_queries:
try:
if days > 0:
if str(user_id).isdigit():
query = 'SELECT (SUM(stopped - started) - ' \
' SUM(CASE WHEN paused_counter IS NULL THEN 0 ELSE paused_counter END)) AS total_time, ' \
'COUNT(DISTINCT %s) AS total_plays ' \
'FROM session_history ' \
'WHERE datetime(stopped, "unixepoch", "localtime") >= datetime("now", "-%s days", "localtime") ' \
'AND user_id = ? ' % (group_by, days)
result = monitor_db.select(query, args=[user_id])
else:
result = []
else:
if str(user_id).isdigit():
query = 'SELECT (SUM(stopped - started) - ' \
' SUM(CASE WHEN paused_counter IS NULL THEN 0 ELSE paused_counter END)) AS total_time, ' \
'COUNT(DISTINCT %s) AS total_plays ' \
'FROM session_history ' \
'WHERE user_id = ? ' % group_by
result = monitor_db.select(query, args=[user_id])
else:
result = []
except Exception as e:
logger.warn(
u"Tautulli Users :: Unable to execute database query for get_watch_time_stats: %s."
% e)
result = []
for item in result:
if item['total_time']:
total_time = item['total_time']
total_plays = item['total_plays']
else:
total_time = 0
total_plays = 0
row = {
'query_days': days,
'total_time': total_time,
'total_plays': total_plays
}
user_watch_time_stats.append(row)
return user_watch_time_stats
def write(self):
if self.ID:
key_dict = {'id': self.ID}
elif self.PMS_IDENTIFIER:
key_dict = {'PMS_IDENTIFIER': self.PMS_IDENTIFIER}
else:
return
values_dict = {}
for key in self._CONFIG_DEFINITIONS.keys():
values_dict[key.lower()] = super(ServerConfig,
self).__getattr__(key)
if 'id' in values_dict:
values_dict.pop('id')
try:
logger.info(
"Tautulli ServerConfig :: %s: Writing configuration to database"
% self.PMS_NAME)
monitor_db = database.MonitorDatabase()
result = monitor_db.upsert('servers',
key_dict=key_dict,
value_dict=values_dict)
if result == 'insert':
super(ServerConfig,
self).__setattr__('ID', monitor_db.last_insert_id())
except Exception as e:
logger.error(
"Tautulli ServerConfig :: %s: Error writing configuration: %s"
% (self.PMS_NAME, e))
def delete(self, keep_history=False):
logger.info(u"Tautulli Servers :: %s: Deleting server from database." %
self.CONFIG.PMS_NAME)
self.CONFIG.PMS_IS_ENABLED = False
self.CONFIG.PMS_IS_DELETED = True
if self.WS_CONNECTED:
self.shutdown()
if not keep_history:
try:
delete_history = self.delete_all_history()
delete_libraries = self.delete_all_libraries()
delete_users = self.delete_all_users()
monitor_db = database.MonitorDatabase()
logger.info(
u"Tautulli Servers :: %s: Deleting server from database." %
self.CONFIG.PMS_NAME)
server_del = monitor_db.action(
'DELETE FROM servers '
'WHERE id = ?', [self.CONFIG.ID])
return True
except Exception as e:
logger.warn(
"Tautulli Servers :: %s: Unable to execute database query for delete_all_history: %s."
% (self.CONFIG.PMS_NAME, e))
return False
return True
def delete_all_user_history(self, user_id=None):
monitor_db = database.MonitorDatabase()
if user_id.isdigit():
logger.info(
u"PlexPy DataFactory :: Deleting all history for user id %s from database."
% user_id)
session_history_media_info_del = \
monitor_db.action('DELETE FROM '
'session_history_media_info '
'WHERE session_history_media_info.id IN (SELECT session_history_media_info.id '
'FROM session_history_media_info '
'JOIN session_history ON session_history_media_info.id = session_history.id '
'WHERE session_history.user_id = ?)', [user_id])
session_history_metadata_del = \
monitor_db.action('DELETE FROM '
'session_history_metadata '
'WHERE session_history_metadata.id IN (SELECT session_history_metadata.id '
'FROM session_history_metadata '
'JOIN session_history ON session_history_metadata.id = session_history.id '
'WHERE session_history.user_id = ?)', [user_id])
session_history_del = \
monitor_db.action('DELETE FROM '
'session_history '
'WHERE session_history.user_id = ?', [user_id])
return 'Deleted all items for user_id %s.' % user_id
else:
return 'Unable to delete items. Input user_id not valid.'
def set_notify_state(session, state, agent_info):
if session and state and agent_info:
monitor_db = database.MonitorDatabase()
if state == 'play':
values = {'on_play': int(time.time())}
elif state == 'stop':
values = {'on_stop': int(time.time())}
elif state == 'pause':
values = {'on_pause': int(time.time())}
elif state == 'resume':
values = {'on_resume': int(time.time())}
elif state == 'buffer':
values = {'on_buffer': int(time.time())}
elif state == 'watched':
values = {'on_watched': int(time.time())}
else:
return
keys = {
'session_key': session['session_key'],
'rating_key': session['rating_key'],
'user_id': session['user_id'],
'user': session['user'],
'agent_id': agent_info['id'],
'agent_name': agent_info['name']
}
monitor_db.upsert(table_name='notify_log',
key_dict=keys,
value_dict=values)
else:
logger.error('PlexPy Notifier :: Unable to set notify state.')
def get_tokens(self, server_id=None, user_id=None):
if user_id:
try:
monitor_db = database.MonitorDatabase()
where = ''
if server_id:
where = ' AND user_shared_libraries.server_id = %s ' % server_id
query = 'SELECT users.allow_guest, users.user_token, user_shared_libraries.server_token' \
' FROM users ' \
' INNER JOIN user_shared_libraries ' \
' ON users.id = user_shared_libraries.id' \
' WHERE users.user_id = ? AND users.deleted_user = 0 ' + where
result = monitor_db.select_single(query, args=[user_id])
if result:
tokens = {
'allow_guest': result['allow_guest'],
'user_token': result['user_token'],
'server_token': result['server_token']
}
return tokens
else:
return None
except:
return None
return None
def delete_all_history(self, user_id=None):
monitor_db = database.MonitorDatabase()
try:
if str(user_id).isdigit():
logger.info(
u"Tautulli Users :: Deleting all history for user id %s from database."
% user_id)
session_history_media_info_del = \
monitor_db.action('DELETE FROM '
'session_history_media_info '
'WHERE session_history_media_info.id IN (SELECT session_history_media_info.id '
'FROM session_history_media_info '
'JOIN session_history ON session_history_media_info.id = session_history.id '
'WHERE session_history.user_id = ?)', [user_id])
session_history_metadata_del = \
monitor_db.action('DELETE FROM '
'session_history_metadata '
'WHERE session_history_metadata.id IN (SELECT session_history_metadata.id '
'FROM session_history_metadata '
'JOIN session_history ON session_history_metadata.id = session_history.id '
'WHERE session_history.user_id = ?)', [user_id])
session_history_del = \
monitor_db.action('DELETE FROM '
'session_history '
'WHERE session_history.user_id = ?', [user_id])
return 'Deleted all items for user_id %s.' % user_id
else:
return 'Unable to delete items. Input user_id not valid.'
except Exception as e:
logger.warn(
u"Tautulli Users :: Unable to execute database query for delete_all_history: %s."
% e)
def delete(self, user_id=None):
monitor_db = database.MonitorDatabase()
try:
if str(user_id).isdigit():
self.delete_all_history(user_id)
logger.info(
u"Tautulli Users :: Deleting user with id %s from database."
% user_id)
monitor_db.action(
'UPDATE users SET deleted_user = 1 WHERE user_id = ?',
[user_id])
monitor_db.action(
'UPDATE users SET keep_history = 0 WHERE user_id = ?',
[user_id])
monitor_db.action(
'UPDATE users SET do_notify = 0 WHERE user_id = ?',
[user_id])
return 'Deleted user with id %s.' % user_id
else:
return 'Unable to delete user, user_id not valid.'
except Exception as e:
logger.warn(
u"Tautulli Users :: Unable to execute database query for delete: %s."
% e)
def get_filters(self, user_id=None):
import urllib.parse
if not user_id:
return {}
try:
monitor_db = database.MonitorDatabase()
query = 'SELECT filter_all, filter_movies, filter_tv, filter_music, filter_photos FROM users ' \
'WHERE user_id = ?'
result = monitor_db.select_single(query, args=[user_id])
except Exception as e:
logger.warn(
u"Tautulli Users :: Unable to execute database query for get_filters: %s."
% e)
result = {}
filters_list = {}
for k, v in result.items():
filters = {}
for f in v.split('|'):
if 'contentRating=' in f or 'label=' in f:
filters.update(dict(urllib.parse.parse_qsl(f)))
filters['content_rating'] = tuple(
f for f in filters.pop('contentRating', '').split(',') if f)
filters['labels'] = tuple(
f for f in filters.pop('label', '').split(',') if f)
filters_list[k] = filters
return filters_list
def get_player_stats(self, user_id=None):
monitor_db = database.MonitorDatabase()
player_stats = []
result_id = 0
try:
if str(user_id).isdigit():
query = 'SELECT player, COUNT(player) as player_count, platform ' \
'FROM session_history ' \
'WHERE user_id = ? ' \
'GROUP BY player ' \
'ORDER BY player_count DESC'
result = monitor_db.select(query, args=[user_id])
else:
result = []
except Exception as e:
logger.warn(u"PlexPy Users :: Unable to execute database query for get_player_stats: %s." % e)
result = []
for item in result:
# Rename Mystery platform names
platform_type = common.PLATFORM_NAME_OVERRIDES.get(item['platform'], item['platform'])
row = {'player_name': item['player'],
'platform_type': platform_type,
'total_plays': item['player_count'],
'result_id': result_id
}
player_stats.append(row)
result_id += 1
return player_stats
def delete_all_libraries(self):
logger.info(
u"Tautulli Servers :: %s: Deleting all libraries from database." %
self.CONFIG.PMS_NAME)
monitor_db = database.MonitorDatabase()
query = 'SELECT id FROM library_sections ' \
'WHERE server_id = ?'
result = monitor_db.select(query, [self.CONFIG.ID])
if result:
home_library_cards = plexpy.CONFIG.HOME_LIBRARY_CARDS
for library_id in result:
libID = str(library_id['id']).encode("utf-8").decode("utf-8")
if libID in home_library_cards:
home_library_cards.remove(libID)
plexpy.CONFIG.__setattr__('HOME_LIBRARY_CARDS', home_library_cards)
plexpy.CONFIG.write()
try:
monitor_db.action(
'DELETE FROM '
'library_sections '
'WHERE server_id = ?', [self.CONFIG.ID])
return True
except Exception as e:
logger.warn(
u"Tautulli Servers :: %s: Unable to execute database query for delete_all_libraries: %s."
% (self.CONFIG.PMS_NAME, e))
return False
def refresh_users():
logger.info("Requesting users list refresh...")
result = PlexTV().get_full_users_list()
monitor_db = database.MonitorDatabase()
if len(result) > 0:
for item in result:
control_value_dict = {"username": item['username']}
new_value_dict = {
"user_id": item['user_id'],
"username": item['username'],
"thumb": item['thumb'],
"email": item['email'],
"is_home_user": item['is_home_user'],
"is_allow_sync": item['is_allow_sync'],
"is_restricted": item['is_restricted']
}
# Check if we've set a custom avatar if so don't overwrite it.
if item['user_id']:
avatar_urls = monitor_db.select(
'SELECT thumb, custom_avatar_url '
'FROM users WHERE user_id = ?', [item['user_id']])
if avatar_urls:
if not avatar_urls[0]['custom_avatar_url'] or \
avatar_urls[0]['custom_avatar_url'] == avatar_urls[0]['thumb']:
new_value_dict['custom_avatar_url'] = item['thumb']
else:
new_value_dict['custom_avatar_url'] = item['thumb']
monitor_db.upsert('users', new_value_dict, control_value_dict)
logger.info("Users list refreshed.")
else:
logger.warn("Unable to refresh users list.")
def get_user_platform_stats(self, user=None, user_id=None):
monitor_db = database.MonitorDatabase()
platform_stats = []
result_id = 0
try:
if user_id:
query = 'SELECT player, COUNT(player) as player_count, platform ' \
'FROM session_history ' \
'WHERE user_id = ? ' \
'GROUP BY player ' \
'ORDER BY player_count DESC'
result = monitor_db.select(query, args=[user_id])
else:
query = 'SELECT player, COUNT(player) as player_count, platform ' \
'FROM session_history ' \
'WHERE user = ? ' \
'GROUP BY player ' \
'ORDER BY player_count DESC'
result = monitor_db.select(query, args=[user])
except:
logger.warn("Unable to execute database query.")
return None
for item in result:
row = {'platform_name': item[0],
'platform_type': item[2],
'total_plays': item[1],
'result_id': result_id
}
platform_stats.append(row)
result_id += 1
return platform_stats
def get_recently_watched(self, user=None, user_id=None, limit='10'):
monitor_db = database.MonitorDatabase()
recently_watched = []
if not limit.isdigit():
limit = '10'
try:
if user_id:
query = 'SELECT session_history.id, session_history.media_type, session_history.rating_key, title, ' \
'grandparent_title, thumb, parent_thumb, grandparent_thumb, media_index, parent_media_index, year, started, user ' \
'FROM session_history_metadata ' \
'JOIN session_history ON session_history_metadata.id = session_history.id ' \
'WHERE user_id = ? AND session_history.media_type != "track" ORDER BY started DESC LIMIT ?'
result = monitor_db.select(query, args=[user_id, limit])
elif user:
query = 'SELECT session_history.id, session_history.media_type, session_history.rating_key, title, ' \
'grandparent_title, thumb, parent_thumb, grandparent_thumb, media_index, parent_media_index, year, started, user ' \
'FROM session_history_metadata ' \
'JOIN session_history ON session_history_metadata.id = session_history.id ' \
'WHERE user = ? AND session_history.media_type != "track" ORDER BY started DESC LIMIT ?'
result = monitor_db.select(query, args=[user, limit])
else:
query = 'SELECT session_history.id, session_history.media_type, session_history.rating_key, title, ' \
'grandparent_title, thumb, parent_thumb, grandparent_thumb, media_index, parent_media_index, year, started, user ' \
'FROM session_history_metadata WHERE session_history.media_type != "track"' \
'JOIN session_history ON session_history_metadata.id = session_history.id ' \
'ORDER BY started DESC LIMIT ?'
result = monitor_db.select(query, args=[limit])
except:
logger.warn("Unable to execute database query.")
return None
for row in result:
if row[1] == 'episode' and row[6]:
thumb = row[6]
elif row[1] == 'episode':
thumb = row[7]
else:
thumb = row[5]
recent_output = {
'row_id': row[0],
'type': row[1],
'rating_key': row[2],
'title': row[3],
'parent_title': row[4],
'thumb': thumb,
'index': row[8],
'parent_index': row[9],
'year': row[10],
'time': row[11],
'user': row[12]
}
recently_watched.append(recent_output)
return recently_watched
def __setattr__(self, key, value):
if key.upper() in self.boolKeys:
value = bool_int(value)
super(ServerConfig, self).__setattr__(key, value)
if key in self._CONFIG_DEFINITIONS.keys():
monitor_db = database.MonitorDatabase()
monitor_db.action('UPDATE servers SET %s = ? '
'WHERE id = ? ' % key.lower(),
args=[value, self.ID])
def get_total_plays_per_hourofday(self, time_range='30', y_axis='plays'):
monitor_db = database.MonitorDatabase()
if not time_range.isdigit():
time_range = '30'
if y_axis == 'plays':
query = 'select strftime("%H", datetime(started, "unixepoch", "localtime")) as hourofday, ' \
'COUNT(id) ' \
'FROM session_history ' \
'WHERE datetime(stopped, "unixepoch", "localtime") >= ' \
'datetime("now", "-' + time_range + ' days", "localtime") AND ' \
'(media_type = "episode" OR media_type = "movie") ' \
'GROUP BY hourofday ' \
'ORDER BY hourofday'
result = monitor_db.select(query)
y_axis_label = 'Total plays'
else:
query = 'select strftime("%H", datetime(started, "unixepoch", "localtime")) as hourofday, ' \
'SUM(case when media_type != "track" and stopped > 0 then (stopped - started) else 0 end) as duration ' \
'FROM session_history ' \
'WHERE datetime(stopped, "unixepoch", "localtime") >= ' \
'datetime("now", "-' + time_range + ' days", "localtime") AND ' \
'(media_type = "episode" OR media_type = "movie") ' \
'GROUP BY hourofday ' \
'ORDER BY hourofday'
result = monitor_db.select(query)
y_axis_label = 'Total duration'
hours_list = [
'00', '01', '02', '03', '04', '05', '06', '07', '08', '09', '10',
'11', '12', '13', '14', '15', '16', '17', '18', '19', '20', '21',
'22', '23'
]
categories = []
series_1 = []
for hour_item in hours_list:
categories.append(hour_item)
series_1_value = 0
for item in result:
if hour_item == item[0]:
series_1_value = item[1]
break
else:
series_1_value = 0
series_1.append(series_1_value)
series_1_output = {'name': y_axis_label, 'data': series_1}
output = {'categories': categories, 'series': [series_1_output]}
return output
def blacklist_logger():
db = database.MonitorDatabase()
notifiers = db.select(
'SELECT newsletter_config, email_config FROM newsletters')
for n in notifiers:
config = json.loads(n['newsletter_config'] or '{}')
logger.blacklist_config(config)
email_config = json.loads(n['email_config'] or '{}')
logger.blacklist_config(email_config)
def __init__(self):
self.SCHED = BackgroundScheduler()
self.SCHED_LOCK = threading.Lock()
db = database.MonitorDatabase()
result = db.select('SELECT * FROM servers')
for pms in result:
name = pms['pms_name']
vars(self)[name] = plexServer(pms)
self.initialize_scheduler()
def get_metadata_details(self, row_id):
monitor_db = database.MonitorDatabase()
if row_id:
query = 'SELECT rating_key, parent_rating_key, grandparent_rating_key, title, parent_title, grandparent_title, ' \
'full_title, media_index, parent_media_index, thumb, parent_thumb, grandparent_thumb, art, media_type, ' \
'year, originally_available_at, added_at, updated_at, last_viewed_at, content_rating, summary, rating, ' \
'duration, guid, directors, writers, actors, genres, studio ' \
'FROM session_history_metadata ' \
'WHERE id = ?'
result = monitor_db.select(query=query, args=[row_id])
else:
result = []
metadata = {}
for item in result:
directors = item['directors'].split(
';') if item['directors'] else []
writers = item['writers'].split(';') if item['writers'] else []
actors = item['actors'].split(';') if item['actors'] else []
genres = item['genres'].split(';') if item['genres'] else []
metadata = {
'type': item['media_type'],
'rating_key': item['rating_key'],
'parent_rating_key': item['parent_rating_key'],
'grandparent_rating_key': item['grandparent_rating_key'],
'grandparent_title': item['grandparent_title'],
'parent_index': item['parent_media_index'],
'parent_title': item['parent_title'],
'index': item['media_index'],
'studio': item['studio'],
'title': item['title'],
'content_rating': item['content_rating'],
'summary': item['summary'],
'rating': item['rating'],
'duration': item['duration'],
'year': item['year'],
'thumb': item['thumb'],
'parent_thumb': item['parent_thumb'],
'grandparent_thumb': item['grandparent_thumb'],
'art': item['art'],
'originally_available_at': item['originally_available_at'],
'added_at': item['added_at'],
'updated_at': item['updated_at'],
'last_viewed_at': item['last_viewed_at'],
'guid': item['guid'],
'writers': writers,
'directors': directors,
'genres': genres,
'actors': actors
}
return metadata
def get_users(self):
monitor_db = database.MonitorDatabase()
try:
query = 'SELECT id, user_id, username, friendly_name, thumb, custom_avatar_url, email, ' \
'is_plextv, is_admin, is_home_user, is_allow_sync, is_restricted, ' \
'do_notify, keep_history, allow_guest, ' \
'filter_all, filter_movies, filter_tv, filter_music, filter_photos ' \
'FROM users WHERE deleted_user = 0'
result = monitor_db.select(query=query)
except Exception as e:
logger.warn(
u"Tautulli Users :: Unable to execute database query for get_users: %s."
% e)
return None
users = []
for item in result:
user = {
'user_id': item['user_id'],
'username': item['username'],
'friendly_name': item['friendly_name'] or item['username'],
'thumb': item['custom_avatar_url'] or item['thumb'],
'email': item['email'],
'is_plextv': item['is_plextv'],
'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'],
'allow_guest': item['allow_guest'],
'filter_all': item['filter_all'],
'filter_movies': item['filter_movies'],
'filter_tv': item['filter_tv'],
'filter_music': item['filter_music'],
'filter_photos': item['filter_photos'],
}
query = 'SELECT shared_libraries ' \
'FROM user_shared_libraries ' \
'WHERE id = ? '
result_shared_libraries = monitor_db.select(query,
args=[item['id']])
shared_libraries = []
for item_shared_libraries in result_shared_libraries:
sl = tuple(item_shared_libraries['shared_libraries'].split(
';')) if item_shared_libraries['shared_libraries'] else ()
shared_libraries.extend(sl)
user['shared_libraries'] = ';'.join(shared_libraries)
users.append(user)
return users
def get_watch_time_stats(self, section_id=None):
monitor_db = database.MonitorDatabase()
time_queries = [1, 7, 30, 0]
library_watch_time_stats = []
for days in time_queries:
try:
if days > 0:
if str(section_id).isdigit():
query = 'SELECT (SUM(stopped - started) - ' \
'SUM(CASE WHEN paused_counter is null THEN 0 ELSE paused_counter END)) as total_time, ' \
'COUNT(session_history.id) AS total_plays ' \
'FROM session_history ' \
'JOIN session_history_metadata ON session_history_metadata.id = session_history.id ' \
'WHERE datetime(stopped, "unixepoch", "localtime") >= datetime("now", "-%s days", "localtime") ' \
'AND section_id = ?' % days
result = monitor_db.select(query, args=[section_id])
else:
result = []
else:
if str(section_id).isdigit():
query = 'SELECT (SUM(stopped - started) - ' \
'SUM(CASE WHEN paused_counter is null THEN 0 ELSE paused_counter END)) as total_time, ' \
'COUNT(session_history.id) AS total_plays ' \
'FROM session_history ' \
'JOIN session_history_metadata ON session_history_metadata.id = session_history.id ' \
'WHERE section_id = ?'
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_watch_time_stats: %s."
% e)
result = []
for item in result:
if item['total_time']:
total_time = item['total_time']
total_plays = item['total_plays']
else:
total_time = 0
total_plays = 0
row = {
'query_days': days,
'total_time': total_time,
'total_plays': total_plays
}
library_watch_time_stats.append(row)
return library_watch_time_stats
def delete_newsletter(newsletter_id=None):
db = database.MonitorDatabase()
if str(newsletter_id).isdigit():
logger.debug(
u"Tautulli Newsletters :: Deleting newsletter_id %s from the database."
% newsletter_id)
result = db.action('DELETE FROM newsletters WHERE id = ?',
args=[newsletter_id])
return True
else:
return False
def delete_mobile_device(mobile_device_id=None):
db = database.MonitorDatabase()
if mobile_device_id:
logger.debug(
u"Tautulli MobileApp :: Deleting device_id %s from the database." %
mobile_device_id)
result = db.action('DELETE FROM mobile_devices WHERE id = ?',
args=[mobile_device_id])
return True
else:
return False
