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