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")