def get_user_gravatar_image(self, user=None): myDB = db.DBConnection() user_info = None try: query = 'SELECT xml ' \ 'FROM %s ' \ 'WHERE user = ? ' \ 'ORDER BY id DESC LIMIT 1' % self.get_history_table_name() result = myDB.select_single(query, args=[user]) except: logger.warn("Unable to open PlexWatch database.") return None xml_data = helpers.latinToAscii(result) try: xml_parse = minidom.parseString(xml_data) except: logger.warn("Error parsing XML for Plexwatch Database.") return None xml_head = xml_parse.getElementsByTagName('User') if not xml_head: logger.warn("Error parsing XML for Plexwatch Database.") return None for a in xml_head: user_id = self.get_xml_attr(a, 'id') user_thumb = self.get_xml_attr(a, 'thumb') user_info = {'user_id': user_id, 'user_thumb': user_thumb} return user_info
def get_total_plays_per_day(self, time_range='30'): myDB = db.DBConnection() if not time_range.isdigit(): time_range = '30' try: query = 'SELECT date(time, "unixepoch", "localtime") as date_played, ' \ 'SUM(case when episode = "" then 0 else 1 end) as tv_count, ' \ 'SUM(case when episode = "" then 1 else 0 end) as movie_count ' \ 'FROM %s ' \ 'WHERE datetime(stopped, "unixepoch", "localtime") >= datetime("now", "-%s days", "localtime") ' \ 'GROUP BY date_played ' \ 'ORDER BY time ASC' % (self.get_history_table_name(), time_range) result = myDB.select(query) except: logger.warn("Unable to open PlexWatch database.") return None # create our date range as some days may not have any data # but we still want to display them base = datetime.date.today() date_list = [ base - datetime.timedelta(days=x) for x in range(0, int(time_range)) ] categories = [] series_1 = [] series_2 = [] for date_item in sorted(date_list): date_string = date_item.strftime('%Y-%m-%d') categories.append(date_string) series_1_value = 0 series_2_value = 0 for item in result: if date_string == item[0]: series_1_value = item[1] series_2_value = item[2] break else: series_1_value = 0 series_2_value = 0 series_1.append(series_1_value) series_2.append(series_2_value) series_1_output = {'name': 'TV', 'data': series_1} series_2_output = {'name': 'Movies', 'data': series_2} output = { 'categories': categories, 'series': [series_1_output, series_2_output] } return output
def check_db_tables(): try: myDB = db.DBConnection() query = 'CREATE TABLE IF NOT EXISTS plexpy_users (id INTEGER PRIMARY KEY AUTOINCREMENT, ' \ 'user_id INTEGER DEFAULT NULL UNIQUE, username TEXT NOT NULL UNIQUE, ' \ 'friendly_name TEXT, thumb TEXT, email TEXT, is_home_user INTEGER DEFAULT NULL, ' \ 'is_allow_sync INTEGER DEFAULT NULL, is_restricted INTEGER DEFAULT NULL)' result = myDB.action(query) except: logger.debug(u"Unable to create users table.")
def set_user_friendly_name(self, user=None, friendly_name=None): if user: if friendly_name.strip() == '': friendly_name = None myDB = db.DBConnection() control_value_dict = {"username": user} new_value_dict = {"friendly_name": friendly_name} myDB.upsert('plexpy_users', new_value_dict, control_value_dict)
def get_recently_watched(self, user=None, limit='10'): myDB = db.DBConnection() recently_watched = [] if not limit.isdigit(): limit = '10' try: if user: query = 'SELECT time, user, xml FROM %s WHERE user = ? ORDER BY time DESC LIMIT ?' % \ (self.get_history_table_name()) xml = myDB.select(query, args=[user, limit]) else: query = 'SELECT time, user, xml FROM %s ORDER BY time DESC LIMIT ?' % \ (self.get_history_table_name()) xml = myDB.select(query, args=[limit]) except: logger.warn("Unable to open PlexWatch database.") return None for row in xml: xml_data = helpers.latinToAscii(row[2]) try: xml_parse = minidom.parseString(xml_data) except: logger.warn("Error parsing XML for Plex stream data.") return None xml_head = xml_parse.getElementsByTagName('opt') if not xml_head: logger.warn("Error parsing XML for Plex stream data.") return None for a in xml_head: if self.get_xml_attr(a, 'type') == 'episode': thumb = self.get_xml_attr(a, 'parentThumb') else: thumb = self.get_xml_attr(a, 'thumb') recent_output = { 'type': self.get_xml_attr(a, 'type'), 'rating_key': self.get_xml_attr(a, 'ratingKey'), 'title': self.get_xml_attr(a, 'title'), 'thumb': thumb, 'index': self.get_xml_attr(a, 'index'), 'parentIndex': self.get_xml_attr(a, 'parentIndex'), 'year': self.get_xml_attr(a, 'year'), 'time': row[0], 'user': row[1] } recently_watched.append(recent_output) return recently_watched
def _dic_from_query(self, query): myDB = db.DBConnection() rows = myDB.select(query) rows_as_dic = [] for row in rows: row_as_dic = dict(zip(row.keys(), row)) rows_as_dic.append(row_as_dic) return rows_as_dic
def get_user_friendly_name(self, user=None): if user: try: myDB = db.DBConnection() query = 'select friendly_name FROM plexpy_users WHERE username = ?' result = myDB.select_single(query, args=[user]) if result: return result else: return user except: return user return None
def get_user_details(self, user=None, user_id=None): try: myDB = db.DBConnection() t = self.get_history_table_name() if user: query = 'SELECT user_id, username, friendly_name, email, ' \ 'thumb, is_home_user, is_allow_sync, is_restricted ' \ 'FROM plexpy_users ' \ 'WHERE username = ? ' \ 'UNION ALL ' \ 'SELECT null, user, null, null, null, null, null, null ' \ 'FROM %s ' \ 'WHERE user = ? ' \ 'GROUP BY user ' \ 'LIMIT 1' % t result = myDB.select(query, args=[user, user]) elif user_id: query = 'select user_id, username, friendly_name, email, thumb, ' \ 'is_home_user, is_allow_sync, is_restricted FROM plexpy_users WHERE user_id = ? LIMIT 1' result = myDB.select(query, args=[user_id]) if result: for item in result: if not item['friendly_name']: friendly_name = item['username'] else: friendly_name = item['friendly_name'] if not item['thumb'] or item['thumb'] == '': user_thumb = common.DEFAULT_USER_THUMB else: user_thumb = item['thumb'] user_details = { "user_id": item['user_id'], "username": item['username'], "friendly_name": friendly_name, "email": item['email'], "thumb": user_thumb, "is_home_user": item['is_home_user'], "is_allow_sync": item['is_allow_sync'], "is_restricted": item['is_restricted'] } return user_details else: return None except: return None return None
def get_total_plays_per_dayofweek(self, time_range='30'): myDB = db.DBConnection() if not time_range.isdigit(): time_range = '30' query = 'SELECT strftime("%w", datetime(time, "unixepoch", "localtime")) as daynumber, ' \ 'case cast (strftime("%w", datetime(time, "unixepoch", "localtime")) as integer) ' \ 'when 0 then "Sunday" ' \ 'when 1 then "Monday" ' \ 'when 2 then "Tuesday" ' \ 'when 3 then "Wednesday" ' \ 'when 4 then "Thursday" ' \ 'when 5 then "Friday" ' \ 'else "Saturday" end as dayofweek, ' \ 'COUNT(id) as total_plays ' \ 'from ' + self.get_history_table_name() + ' ' + \ 'WHERE datetime(stopped, "unixepoch", "localtime") >= ' \ 'datetime("now", "-' + time_range + ' days", "localtime") ' \ 'GROUP BY dayofweek ' \ 'ORDER BY daynumber' result = myDB.select(query) days_list = [ 'Sunday', 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday' ] categories = [] series_1 = [] for day_item in days_list: categories.append(day_item) series_1_value = 0 for item in result: if day_item == item[1]: series_1_value = item[2] break else: series_1_value = 0 series_1.append(series_1_value) series_1_output = {'name': 'Total plays', 'data': series_1} output = {'categories': categories, 'series': [series_1_output]} return output
def get_user_platform_stats(self, user=None): myDB = db.DBConnection() platform_stats = [] result_id = 0 try: query = 'SELECT platform, COUNT(platform) as platform_count, xml ' \ 'FROM grouped ' \ 'WHERE user = ? ' \ 'GROUP BY platform ' \ 'ORDER BY platform_count DESC' result = myDB.select(query, args=[user]) except: logger.warn("Unable to open PlexWatch database.") return None for item in result: xml_data = helpers.latinToAscii(item[2]) try: xml_parse = minidom.parseString(xml_data) except: logger.warn("Error parsing XML for Plex stream data.") return None xml_head = xml_parse.getElementsByTagName('Player') if not xml_head: logger.warn("Error parsing XML for Plex stream data.") return None for a in xml_head: platform_type = self.get_xml_attr(a, 'platform') row = { 'platform_name': item[0], 'platform_type': platform_type, 'total_plays': item[1], 'result_id': result_id } platform_stats.append(row) result_id += 1 return platform_stats
def get_total_plays_per_hourofday(self, time_range='30'): myDB = db.DBConnection() if not time_range.isdigit(): time_range = '30' query = 'select strftime("%H", datetime(time, "unixepoch", "localtime")) as hourofday, ' \ 'COUNT(id) ' \ 'FROM ' + self.get_history_table_name() + ' ' + \ 'WHERE datetime(stopped, "unixepoch", "localtime") >= ' \ 'datetime("now", "-' + time_range + ' days", "localtime") ' \ 'GROUP BY hourofday ' \ 'ORDER BY hourofday' result = myDB.select(query) 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': 'Total plays', 'data': series_1} output = {'categories': categories, 'series': [series_1_output]} return output
def get_user_watch_time_stats(self, user=None): myDB = db.DBConnection() time_queries = [1, 7, 30, 0] user_watch_time_stats = [] for days in time_queries: if days > 0: query = 'SELECT (SUM(stopped - time) - ' \ 'SUM(CASE WHEN paused_counter is null THEN 0 ELSE paused_counter END)) as total_time, ' \ 'COUNT(id) AS total_plays ' \ 'FROM %s ' \ 'WHERE datetime(stopped, "unixepoch", "localtime") >= datetime("now", "-%s days", "localtime") ' \ 'AND user = ?' % (self.get_history_table_name(), days) result = myDB.select(query, args=[user]) else: query = 'SELECT (SUM(stopped - time) - ' \ 'SUM(CASE WHEN paused_counter is null THEN 0 ELSE paused_counter END)) as total_time, ' \ 'COUNT(id) AS total_plays ' \ 'FROM %s ' \ 'WHERE user = ?' % self.get_history_table_name() result = myDB.select(query, args=[user]) for item in result: if item[0]: total_time = item[0] total_plays = item[1] 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 refresh_users(): logger.info("Requesting users list refresh...") result = PlexTV().get_full_users_list() pw_db = db.DBConnection() 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'] } pw_db.upsert('plexpy_users', new_value_dict, control_value_dict) logger.info("Users list refreshed.") else: logger.warn("Unable to refresh users list.")
def get_home_stats(self, time_range='30'): myDB = db.DBConnection() if not time_range.isdigit(): time_range = '30' stats_queries = ["top_tv", "popular_tv", "top_users", "top_platforms"] home_stats = [] for stat in stats_queries: if 'top_tv' in stat: top_tv = [] try: query = 'SELECT orig_title, COUNT(orig_title) as total_plays, ' \ 'grandparentRatingKey, MAX(time) as last_watch, xml ' \ 'FROM %s ' \ 'WHERE datetime(stopped, "unixepoch", "localtime") ' \ '>= datetime("now", "-%s days", "localtime") ' \ 'AND episode != "" ' \ 'GROUP BY orig_title ' \ 'ORDER BY total_plays DESC LIMIT 10' % (self.get_history_table_name(), time_range) result = myDB.select(query) except: logger.warn("Unable to open PlexWatch database.") return None for item in result: xml_data = helpers.latinToAscii(item[4]) try: xml_parse = minidom.parseString(xml_data) except: logger.warn( "Error parsing XML for Plexwatch database.") return None xml_head = xml_parse.getElementsByTagName('opt') if not xml_head: logger.warn( "Error parsing XML for Plexwatch database.") return None for a in xml_head: grandparent_thumb = self.get_xml_attr( a, 'grandparentThumb') row = { 'title': item[0], 'total_plays': item[1], 'users_watched': '', 'rating_key': item[2], 'last_play': item[3], 'grandparent_thumb': grandparent_thumb, 'thumb': '', 'user': '', 'friendly_name': '', 'platform_type': '', 'platform': '' } top_tv.append(row) home_stats.append({'stat_id': stat, 'rows': top_tv}) elif 'popular_tv' in stat: popular_tv = [] try: query = 'SELECT orig_title, COUNT(DISTINCT user) as users_watched, grandparentRatingKey, ' \ 'MAX(time) as last_watch, xml, COUNT(id) as total_plays ' \ 'FROM %s ' \ 'WHERE datetime(stopped, "unixepoch", "localtime") ' \ '>= datetime("now", "-%s days", "localtime") ' \ 'AND episode != "" ' \ 'GROUP BY orig_title ' \ 'ORDER BY users_watched DESC, total_plays DESC ' \ 'LIMIT 10' % (self.get_history_table_name(), time_range) result = myDB.select(query) except: logger.warn("Unable to open PlexWatch database.") return None for item in result: xml_data = helpers.latinToAscii(item[4]) try: xml_parse = minidom.parseString(xml_data) except: logger.warn( "Error parsing XML for Plexwatch database.") return None xml_head = xml_parse.getElementsByTagName('opt') if not xml_head: logger.warn( "Error parsing XML for Plexwatch database.") return None for a in xml_head: grandparent_thumb = self.get_xml_attr( a, 'grandparentThumb') row = { 'title': item[0], 'users_watched': item[1], 'rating_key': item[2], 'last_play': item[3], 'total_plays': item[5], 'grandparent_thumb': grandparent_thumb, 'thumb': '', 'user': '', 'friendly_name': '', 'platform_type': '', 'platform': '' } popular_tv.append(row) home_stats.append({'stat_id': stat, 'rows': popular_tv}) elif 'top_users' in stat: top_users = [] try: s = self.get_history_table_name() query = 'SELECT user, ' \ '(case when friendly_name is null then user else friendly_name end) as friendly_name,' \ 'COUNT(' + s + '.id) as total_plays, MAX(time) as last_watch, thumb ' \ 'FROM ' + s + ' ' \ 'LEFT OUTER JOIN plexpy_users ON ' + s + '.user = plexpy_users.username ' \ 'WHERE datetime(stopped, "unixepoch", "localtime") >= ' \ 'datetime("now", "-' + time_range + ' days", "localtime") '\ 'GROUP BY ' + s + '.user ' \ 'ORDER BY total_plays DESC LIMIT 10' result = myDB.select(query) except: logger.warn("Unable to open PlexWatch database.") return None for item in result: if not item['thumb'] or item['thumb'] == '': user_thumb = common.DEFAULT_USER_THUMB else: user_thumb = item[4] thumb = self.get_user_gravatar_image(item[0]) row = { 'user': item[0], 'friendly_name': item[1], 'total_plays': item[2], 'last_play': item[3], 'thumb': user_thumb, 'grandparent_thumb': '', 'users_watched': '', 'rating_key': '', 'title': '', 'platform_type': '', 'platform': '' } top_users.append(row) home_stats.append({'stat_id': stat, 'rows': top_users}) elif 'top_platforms' in stat: top_platform = [] try: query = 'SELECT platform, COUNT(id) as total_plays, MAX(time) as last_watch, xml ' \ 'FROM %s ' \ 'WHERE datetime(stopped, "unixepoch", "localtime") ' \ '>= datetime("now", "-%s days", "localtime") ' \ 'GROUP BY platform ' \ 'ORDER BY total_plays DESC' % (self.get_history_table_name(), time_range) result = myDB.select(query) except: logger.warn("Unable to open PlexWatch database.") return None for item in result: xml_data = helpers.latinToAscii(item[3]) try: xml_parse = minidom.parseString(xml_data) except: logger.warn( "Error parsing XML for Plexwatch database.") return None xml_head = xml_parse.getElementsByTagName('Player') if not xml_head: logger.warn( "Error parsing XML for Plexwatch database.") return None for a in xml_head: platform_type = self.get_xml_attr(a, 'platform') row = { 'platform': item[0], 'total_plays': item[1], 'last_play': item[2], 'platform_type': platform_type, 'title': '', 'thumb': '', 'grandparent_thumb': '', 'users_watched': '', 'rating_key': '', 'user': '', 'friendly_name': '' } top_platform.append(row) top_platform_aggr = self.group_and_sum_dataset( top_platform, 'platform_type', ['total_plays'], 'total_plays') home_stats.append({'stat_id': stat, 'rows': top_platform_aggr}) return home_stats
def _getHistory(self, iDisplayStart=0, iDisplayLength=100, sSearch="", iSortCol_0='0', sSortDir_0='asc', **kwargs): iDisplayStart = int(iDisplayStart) iDisplayLength = int(iDisplayLength) filtered = [] totalcount = 0 myDB = db.DBConnection() db_table = db.DBConnection().get_history_table_name() sortcolumn = 'time' sortbyhavepercent = False if iSortCol_0 == '1': sortcolumn = 'user' if iSortCol_0 == '2': sortcolumn = 'platform' elif iSortCol_0 == '3': sortcolumn = 'ip_address' elif iSortCol_0 == '4': sortcolumn = 'title' elif iSortCol_0 == '5': sortcolumn = 'time' elif iSortCol_0 == '6': sortcolumn = 'paused_counter' elif iSortCol_0 == '7': sortcolumn = 'stopped' elif iSortCol_0 == '8': sortbyhavepercent = True if sSearch == "": query = 'SELECT * from %s order by %s COLLATE NOCASE %s' % ( db_table, sortcolumn, sSortDir_0) filtered = myDB.select(query) totalcount = len(filtered) else: query = 'SELECT * from ' + db_table + ' WHERE user LIKE "%' + sSearch + \ '%" OR title LIKE "%' + sSearch + '%"' + 'ORDER BY %s COLLATE NOCASE %s' % (sortcolumn, sSortDir_0) filtered = myDB.select(query) totalcount = myDB.select('SELECT COUNT(*) from processed')[0][0] history = filtered[iDisplayStart:(iDisplayStart + iDisplayLength)] rows = [] for item in history: row = { "date": item['time'], "user": item["user"], "platform": item["platform"], "ip_address": item["ip_address"], "title": item["title"], "started": item["time"], "paused": item["paused_counter"], "stopped": item["stopped"], "duration": "", "percent_complete": 0, } if item['paused_counter'] > 0: row['paused'] = item['paused_counter'] else: row['paused'] = 0 if item['time']: if item['stopped'] > 0: stopped = item['stopped'] else: stopped = 0 if item['paused_counter'] > 0: paused_counter = item['paused_counter'] else: paused_counter = 0 row['duration'] = stopped - item['time'] + paused_counter try: xml_parse = minidom.parseString( helpers.latinToAscii(item['xml'])) except IOError, e: logger.warn("Error parsing XML in PlexWatch db: %s" % e) xml_head = xml_parse.getElementsByTagName('opt') if not xml_head: logger.warn("Error parsing XML in PlexWatch db: %s" % e) 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)
def __init__(self): self.ssp_db = db.DBConnection()
def get_stream_details(self, row_id=None): myDB = db.DBConnection() if row_id: query = 'SELECT xml from %s where id = ?' % ( self.get_history_table_name()) xml = myDB.select_single(query, args=[row_id]) xml_data = helpers.latinToAscii(xml) else: return None try: xml_parse = minidom.parseString(xml_data) except: logger.warn("Error parsing XML for Plex stream data.") return None xml_head = xml_parse.getElementsByTagName('opt') if not xml_head: logger.warn("Error parsing XML for Plex stream data.") return None stream_output = {} for a in xml_head: media_type = self.get_xml_attr(a, 'type') title = self.get_xml_attr(a, 'title') grandparent_title = self.get_xml_attr(a, 'grandparentTitle') if a.getElementsByTagName('TranscodeSession'): transcode_data = a.getElementsByTagName('TranscodeSession') for transcode_session in transcode_data: transcode_video_dec = self.get_xml_attr( transcode_session, 'videoDecision') transcode_video_codec = self.get_xml_attr( transcode_session, 'videoCodec') transcode_height = self.get_xml_attr( transcode_session, 'height') transcode_width = self.get_xml_attr( transcode_session, 'width') transcode_audio_dec = self.get_xml_attr( transcode_session, 'audioDecision') transcode_audio_codec = self.get_xml_attr( transcode_session, 'audioCodec') transcode_audio_channels = self.get_xml_attr( transcode_session, 'audioChannels') else: transcode_data = a.getElementsByTagName('Media') for transcode_session in transcode_data: transcode_video_dec = 'direct play' transcode_video_codec = self.get_xml_attr( transcode_session, 'videoCodec') transcode_height = self.get_xml_attr( transcode_session, 'height') transcode_width = self.get_xml_attr( transcode_session, 'width') transcode_audio_dec = 'direct play' transcode_audio_codec = self.get_xml_attr( transcode_session, 'audioCodec') transcode_audio_channels = self.get_xml_attr( transcode_session, 'audioChannels') if a.getElementsByTagName('Media'): stream_data = a.getElementsByTagName('Media') for stream_item in stream_data: stream_output = { 'container': self.get_xml_attr(stream_item, 'container'), 'bitrate': self.get_xml_attr(stream_item, 'bitrate'), 'video_resolution': self.get_xml_attr(stream_item, 'videoResolution'), 'width': self.get_xml_attr(stream_item, 'width'), 'height': self.get_xml_attr(stream_item, 'height'), 'aspect_ratio': self.get_xml_attr(stream_item, 'aspectRatio'), 'video_framerate': self.get_xml_attr(stream_item, 'videoFrameRate'), 'video_codec': self.get_xml_attr(stream_item, 'videoCodec'), 'audio_codec': self.get_xml_attr(stream_item, 'audioCodec'), 'audio_channels': self.get_xml_attr(stream_item, 'audioChannels'), 'transcode_video_dec': transcode_video_dec, 'transcode_video_codec': transcode_video_codec, 'transcode_height': transcode_height, 'transcode_width': transcode_width, 'transcode_audio_dec': transcode_audio_dec, 'transcode_audio_codec': transcode_audio_codec, 'transcode_audio_channels': transcode_audio_channels, 'media_type': media_type, 'title': title, 'grandparent_title': grandparent_title } return stream_output