def manual_import_all(): show_ids_to_add = ['111550', '164130'] existing = [int(x[0]) for x in utils.mysql_select("SELECT alternateId FROM media m, alternateIds ai, alternateKey ak where m.mediaId = ai.mediaId and m.mediaType = ai.mediaType and ai.alternateKeyId = ak.id and ak.name = 'trakt';")] for traktId in show_ids_to_add: if trakt_api.authenticate(): seasons = trakt_api.get_season_info(traktId) for season in seasons: for episode in season['episodes']: if episode['ids']['trakt'] not in existing: importer(4, traktId, seasonNumber=season['number'], episodeNumber=episode['number'])
def search_guid_override(mediaType, guid): try: query = "SELECT mediaId FROM plex_possibleGuid WHERE mediaType = '%s' and guid = '%s';" % ( mediaType, guid) results = utils.mysql_select(query) return [x[0] for x in results] except Exception as e: logger.error('Error on line {} - {} - {}'.format( type(e).__name__, sys.exc_info()[-1].tb_lineno, e)) return None
def trakt_id_from_grandparent_guid(guid): trakt_id = None try: trakt_id = utils.mysql_select( "SELECT id, alternateId FROM alternateIds WHERE (mediaId, mediaType) IN (SELECT mediaId, mediaType FROM alternateIds WHERE alternateId = '%s' and mediaType = '2') AND alternateKeyId = 2;" % guid)[0][1] except IndexError: pass except Exception as e: logger.error('Error on line {} - {} - {}'.format( type(e).__name__, sys.exc_info()[-1].tb_lineno, e)) return trakt_id
def import_view(page_max=0): movies = trakt_api.get_movie_list('history', page_max) episodes = trakt_api.get_episode_list('history', page_max) logger.info("%s Movies Found and %s Episodes Found" % (len(movies), len(episodes))) old_ids = [(int(x), int(y)) for x, y in utils.mysql_select("SELECT w.mediaType, w.id FROM watched w, alternateKey ak WHERE w.sourceId = ak.id and ak.name = 'trakt'")] new_ids = [(1, int(x['id'])) for x in movies] + [(4, int(x['id'])) for x in episodes] insert_ids = list(set(new_ids).difference(old_ids)) logger.info("%s Need to be Added to Watched" % len(insert_ids)) logger.debug(("old_ids ids", len(old_ids))) logger.debug(("new_ids ids", len(new_ids))) logger.debug(("insert ids", len(insert_ids))) for mediaType, insert_id in tqdm(insert_ids): media_id = None media_single = None if mediaType == utils.get_media_type("movie"): media_single = list(filter(lambda x: x['id'] == insert_id, movies))[0] for k in media_single['movie']['ids']: media_id = utils.search_media_id(mediaType, k, media_single['movie']['ids'][k]) if media_id: break else: media_id = importer(mediaType, media_single['movie']['ids']['trakt']) elif mediaType == utils.get_media_type("episode"): media_single = list(filter(lambda x: x['id'] == insert_id, episodes))[0] for k in media_single['episode']['ids']: media_id = utils.search_media_id(mediaType, k, media_single['episode']['ids'][k]) if media_id: break else: media_id = importer(mediaType, media_single['show']['ids']['trakt'], seasonNumber=media_single['episode']['season'], episodeNumber=media_single['episode']['number']) if media_id is not None: utils.add_watched(mediaType, media_id, datetime.fromisoformat(media_single['watched_at'][:-1]), 2, insert_id) else: logger.error("Media ID Not Found- (%s,%s)" % (mediaType, insert_id)) if page_max == 0: leftover_ids = list(set(old_ids).difference(new_ids)) if len(leftover_ids) > 0: logger.info("Trakt IDs in Table but not in Trakt.tv - %s" % len(leftover_ids)) utils.mysql_execute("DELETE FROM watched WHERE (mediaType, id) IN %s" % str(leftover_ids).replace('[', '(').replace(']', ')')) utils.mysql_execute("DELETE FROM plex_compare WHERE watchedId IN %s" % str([y for x, y in leftover_ids]).replace('[', '(').replace(']', ')')) else: logger.debug("No Leftover Episodes") return len(insert_ids) > 0 or len(leftover_ids) > 0 else: return False
def add_watched(): results = utils.mysql_select( "SELECT CAST(mediaType AS CHAR), guid FROM plex_possibleGuid ppg where (mediaType, mediaId) in (SELECT mediaType, mediaId FROM watched WHERE id NOT IN (select watchedId from plex_compare));" ) all_items = gather_plex() plex_guid = [] plex_guid_reference = [] for x in all_items: guid = x.guid media_type = str( list( filter(lambda z: int(z[2]) == x.librarySectionID, config.section_ids))[0][1]) plex_guid.append((media_type, guid)) plex_guid_reference.append((guid, guid)) guids = x.guids for y in guids: plex_guid.append((media_type, y.id)) plex_guid_reference.append((y.id, guid)) insert_ids = list(set(plex_guid).intersection(results)) if insert_ids: items_to_mark = [] for media_type, guid in insert_ids: item_guid = list( filter(lambda x: x[0] == guid, plex_guid_reference))[0][1] item = list(filter(lambda x: x.guid == item_guid, all_items))[0] items_to_mark.append(item) items_to_mark = list(set([i for i in items_to_mark])) logger.info("Adding New Records in Plex Database - %s Records" % len(items_to_mark)) for plex_obj in items_to_mark: logger.debug(("Marking As Watched", plex_obj.guid, create_plex_title(plex_obj))) plex_obj.markWatched() else: logger.info("No Records to Add to Plex Database")
def import_view(): logger.debug("Importing Plex Views into Table") results = plex_select( "SELECT id, account_id, guid, metadata_type, library_section_id, grandparent_title, parent_index, parent_title, `index`, title, thumb_url, viewed_at, grandparent_guid, originally_available_at, device_id FROM metadata_item_views where account_id = ? and library_section_id in (1,2) and metadata_type in (1,4);", str(config.account_id)) logger.info("%s Plex Views Found" % (len(results))) old_ids = [(int(x), int(y)) for x, y in utils.mysql_select( "SELECT metadata_type, plex_id FROM plex_views")] rewatch_ids = [(int(x), int(y)) for x, y in utils.mysql_select( "select pv.metadata_type, pv.plex_id from plex_rewatch r, episodes e, plex_views pv where pv.media_id = e.id and r.showId = e.showId and pv.viewed_at < r.start_date" )] new_ids = [(int(metadata_type), int(unique_id)) for unique_id, account_id, guid, metadata_type, library_section_id, grandparent_title, parent_index, parent_title, index, title, thumb_url, viewed_at, grandparent_guid, originally_available_at, device_id in results] insert_ids = list(set(new_ids).difference(old_ids)) logger.info("Adding Records to Plex Views - %s" % len(insert_ids)) if config.insert_limits > 0: insert_ids = random.sample( insert_ids, config.insert_limits if len(insert_ids) >= config.insert_limits else len(insert_ids)) for media_type, insert_id in tqdm(insert_ids): (plex_id, account_id, original_guid, metadata_type, library_section_id, grandparent_title, parent_index, parent_title, index, title, thumb_url, viewed_at, grandparent_guid, originally_available_at, device_id) = list(filter(lambda x: x[0] == insert_id, results))[0] if 'plex' in original_guid: item = plexAPI.library.search(guid=original_guid)[0] guids = item.guids else: guids = original_guid item = None media_id = guid_to_media_id(metadata_type, guids, original_guid=original_guid, plex=item) if media_id is not None: query = "INSERT IGNORE INTO plex_views (media_id, plex_id, account_id,guid,metadata_type,library_section_id,grandparent_title,parent_index,parent_title,`index`,title,thumb_url,viewed_at,grandparent_guid,originally_available_at,device_id) VALUES ('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s');" % ( media_id, plex_id, account_id, original_guid, metadata_type, library_section_id, escape_string(grandparent_title), parent_index, escape_string(parent_title), index, escape_string(title), thumb_url, viewed_at, grandparent_guid, originally_available_at, device_id) if not utils.mysql_execute(query): logger.error("Failed Inserting Record - %s" % query) else: logger.error("Media ID Not Found- (%s,%s,%s,%s,%s)" % (create_plex_title(item), metadata_type, guids, original_guid, insert_id)) leftover_ids = list( set(list(set(old_ids).difference(new_ids))).difference(rewatch_ids)) if len(leftover_ids) > 0: logger.info("Plex IDs in Table but not in Plex DB - %s" % len(leftover_ids)) utils.mysql_execute( "DELETE FROM plex_views WHERE (metadata_type, plex_id) IN %s" % str(leftover_ids).replace('[', '(').replace(']', ')')) utils.mysql_execute("DELETE FROM plex_compare WHERE plexId IN %s" % str([y for x, y in leftover_ids]).replace( '[', '(').replace(']', ')')) else: logger.debug("No Leftover Episodes") logger.debug("Finished Importing Plex Views into Table") return len(insert_ids) > 0 or len(leftover_ids) > 0
def update_db(): try: sessions_count = len(plexAPI.sessions()) except Exception as e: logger.error('Error on line {} - {} - {}'.format( type(e).__name__, sys.exc_info()[-1].tb_lineno, e)) sessions_count = 0 if plex_down or sessions_count == 0: logger.info("Stopping Plex Service") os.system("service plexmediaserver stop") results = utils.mysql_select( "SELECT plex_compare.plexId, watchedAt from plex_compare join plexWatchedComparison on (plex_compare.watchedId = plexWatchedComparison.watchedId and plex_compare.plexId = plexWatchedComparison.plexId) where timeDiff != 0" ) logger.info("Updating Watched Time in Plex Database - %s Records" % len(results)) count = 0 for plex_id, watched_at in results: count += utils.mysql_execute( "UPDATE plex_views SET viewed_at = '%s' WHERE plex_id= '%s';" % (watched_at.strftime("%Y-%m-%d %H:%M:%S"), plex_id)) plex_execute( "UPDATE metadata_item_views SET viewed_at = ? WHERE id = ?", (watched_at.strftime("%Y-%m-%d %H:%M:%S"), plex_id)) if count != len(results): logger.error("Affected %s row count is not correct" % count) current_plex_ids = [ int(unique_id) for unique_id, account_id, guid, metadata_type, library_section_id, grandparent_title, parent_index, parent_title, index, title, thumb_url, viewed_at, grandparent_guid, originally_available_at, device_id in plex_select( "SELECT id, account_id, guid, metadata_type, library_section_id, grandparent_title, parent_index, parent_title, `index`, title, thumb_url, viewed_at, grandparent_guid, originally_available_at, device_id FROM metadata_item_views where account_id = ? and library_section_id in (1,2) and metadata_type in (1,4);", str(config.account_id)) ] db_plex_ids = [ int(x) for x, y in utils.mysql_select( "SELECT plex_id, metadata_type FROM plex_views WHERE plex_id in (select plexId from plex_compare);" ) ] plex_ids_to_remove = list( set(current_plex_ids).difference(db_plex_ids)) logger.info("Removing Records in Plex Database with No Matches - %s" % len(plex_ids_to_remove)) if len(plex_ids_to_remove) > 0: plex_execute( "DELETE FROM metadata_item_views where account_id = 1 and library_section_id in (1,2) and id in %s;" % str(plex_ids_to_remove).replace('[', '(').replace(']', ')')) utils.mysql_execute( "DELETE FROM plex_views WHERE plex_id not in (select plexId from plex_compare)" ) logger.info("Remove Records From Unfinished Rewatch") current_plex_ids = [ int(unique_id) for unique_id, account_id, guid, metadata_type, library_section_id, grandparent_title, parent_index, parent_title, index, title, thumb_url, viewed_at, grandparent_guid, originally_available_at, device_id in plex_select( "SELECT id, account_id, guid, metadata_type, library_section_id, grandparent_title, parent_index, parent_title, `index`, title, thumb_url, viewed_at, grandparent_guid, originally_available_at, device_id FROM metadata_item_views where account_id = ? and library_section_id in (1,2) and metadata_type in (1,4);", str(config.account_id)) ] rewatch_ids = [ x for x, y in utils.mysql_select( "select plexId, watchedId from plex_compare where watchedId in (select w.id from watched w, episodes e, plex_rewatch r where w.mediaId = e.id and e.showId = r.showID and watchedAt < r.start_date and r.end_date is null);" ) ] rewatch_to_remove = list( set(list(rewatch_ids)).intersection(list(current_plex_ids))) logger.info("Removed from Plex Database - %s" % len(rewatch_to_remove)) if len(rewatch_to_remove) > 0: plex_execute( "DELETE FROM metadata_item_views where account_id = 1 and library_section_id in (1,2) and id in ?;", str(rewatch_to_remove).replace('[', '(').replace(']', ')')) plex_execute( "DELETE FROM metadata_item_settings WHERE account_id = 1 and (account_id, guid) not in (SELECT account_id, guid FROM metadata_item_views) AND guid in (SELECT guid FROM metadata_items WHERE metadata_type IN (1,4))" ) current_plex_ids = [ int(unique_id) for unique_id, account_id, guid, metadata_type, library_section_id, grandparent_title, parent_index, parent_title, index, title, thumb_url, viewed_at, grandparent_guid, originally_available_at, device_id in plex_select( "SELECT id, account_id, guid, metadata_type, library_section_id, grandparent_title, parent_index, parent_title, `index`, title, thumb_url, viewed_at, grandparent_guid, originally_available_at, device_id FROM metadata_item_views where account_id = ? and library_section_id in (1,2) and metadata_type in (1,4);", str(config.account_id)) ] rewatch_ids = [ x for x, y in utils.mysql_select( "select plexId, watchedId from plex_compare where watchedId in (select w.id from watched w, episodes e, plex_rewatch r where w.mediaId = e.id and e.showId = r.showID and watchedAt < r.start_date and r.end_date is not null);" ) ] insert_ids = list(set(rewatch_ids).difference(current_plex_ids)) logger.info("Add Records From Finished Rewatched - %s" % len(insert_ids)) if len(insert_ids) > 0: results = utils.mysql_select( "SELECT plex_id, account_id, guid, metadata_type, library_section_id, grandparent_title, parent_index, parent_title, `index`, title, thumb_url, viewed_at, grandparent_guid, originally_available_at, device_id FROM plex_views pv WHERE plex_id IN %s;" % str(insert_ids).replace('[', '(').replace(']', ')')) for unique_id, account_id, guid, metadata_type, library_section_id, grandparent_title, parent_index, parent_title, index, title, thumb_url, viewed_at, grandparent_guid, originally_available_at, device_id in results: plex_execute( "INSERT INTO metadata_item_views (id, account_id,guid,metadata_type,library_section_id,grandparent_title,parent_index,parent_title,`index`,title,thumb_url,viewed_at,grandparent_guid,originally_available_at,device_id) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);", (unique_id, account_id, guid, metadata_type, library_section_id, escape_string(grandparent_title), parent_index, escape_string(parent_title), index, escape_string(title), thumb_url, viewed_at, grandparent_guid, originally_available_at, device_id)) logger.info("Updating records in metadata_item_settings") plex_execute( "update metadata_item_settings set last_viewed_at = (select miv.viewed_at from (select guid, account_id, max(viewed_at) viewed_at from metadata_item_views group by guid, account_id) miv where miv.guid = metadata_item_settings.guid and miv.account_id = metadata_item_settings.account_id);" ) plex_execute( "update metadata_item_settings set view_count = (select miv.count from (select guid, account_id, count() count from metadata_item_views group by guid, account_id) miv where miv.guid = metadata_item_settings.guid and miv.account_id = metadata_item_settings.account_id) where (account_id, guid) IN (select miv.account_id, miv.guid from (select guid, account_id, count() count from metadata_item_views group by guid, account_id) miv, metadata_item_settings mis where miv.guid = mis.guid and miv.account_id = mis.account_id and miv.count != mis.view_count);" ) logger.info("Starting Plex Service") os.system("service plexmediaserver start") else: write_temp('missed_plex', '1') logger.info("Plex Currently in Use. Not Updating")
def sync_collected(mode=None, value=None): # Mode for gather_plex plex_all = gather_plex(mode, value) plex_guids = [(list( filter(lambda x: x[2] == int(item.section().key), config.section_ids))[0][1], item.guid) for item in plex_all] query = "SELECT c.mediaType, alternateId FROM collected c JOIN alternateIds ai ON (c.mediaId=ai.mediaId and c.mediaType=ai.mediaType) WHERE alternateKeyID = 7 and removedAt IS NULL;" if value is not None: query = "%s AND alternateId = '%s';" % (query[:-1], value) collection_guid = utils.mysql_select(query) insert_ids = [ (x, y) for x, y in list(set(plex_guids).difference(collection_guid)) ] logger.debug(('plex', len(plex_guids))) logger.debug(('db', len(collection_guid))) logger.debug(('difference', len(insert_ids))) logger.info("Adding %s Record(s) to Collected" % len(insert_ids)) if config.insert_limits > 0: insert_ids = random.sample( insert_ids, config.insert_limits if len(insert_ids) >= config.insert_limits else len(insert_ids)) for metadata_type, original_guid in tqdm(insert_ids): item = list(filter(lambda x: x.guid == original_guid, plex_all))[0] added_on = None guids = [] try: (plex_metadata_type, guids, added_on) = (list( filter(lambda x: x[2] == int(item.section().key), config.section_ids))[0][1], item.guids, item.addedAt) except IndexError: (plex_metadata_type, guids, added_on) = (list( filter(lambda x: x[2] == int(item.section().key), config.section_ids))[0][1], original_guid, item.addedAt) except Exception as e: logger.debug('Error on line {} - {} - {}'.format( type(e).__name__, sys.exc_info()[-1].tb_lineno, e)) media_id = guid_to_media_id(metadata_type, guids, original_guid=original_guid, plex=item) logger.debug("Media Id Found: %s" % media_id) if media_id is not None: utils.add_collected(metadata_type, media_id, added_on.strftime('%Y-%m-%d %H:%M:%S')) else: logger.error("Media ID Not Found- (%s,%s,%s,%s,%s)" % (metadata_type, create_plex_title(item), guids, original_guid, added_on)) if mode is None: leftover_ids = list(set(collection_guid).difference(plex_guids)) if len(leftover_ids) > 0: logger.info("Collections IDs in Table but not in Plex - %s" % len(leftover_ids)) if len(leftover_ids) < 100: results = utils.mysql_select( "SELECT * FROM (SELECT c.mediaType, c.mediaId FROM collected c JOIN alternateIds ai ON (c.mediaId=ai.mediaId and c.mediaType=ai.mediaType) WHERE alternateKeyID = 7 and (c.mediaType, alternateId) IN %s) tmp" % str(leftover_ids).replace('[', '(').replace(']', ')')) if len(results) == 1: results = [results[0]] affected_rows = utils.mysql_execute( "UPDATE collected SET removedAt = CURRENT_TIMESTAMP() WHERE (mediaType, media_id) IN %s;" % str(results).replace('[', '(').replace(']', ')')) if affected_rows != len(results): logger.error("Affected %s row count is not correct" % affected_rows) if utils.mysql_execute( "DELETE FROM alternateIds WHERE alternateKeyID = 7 and (mediaType, media_id) IN %s;" % str(results).replace('[', '(').replace( ']', ')')) != len(results): logger.error("Affected %s row count is not correct")