
    gA                        d dl Z d dlZd dlmZ d dlmZ d dlmZ d dlmZ d dlmZ d dlmZ d dlmZ d d	lm	Z	  e
        e
        e
       d
a G d de      Zy)    N)common)database)
datatables)helpers)logger)
pmsconnect)session)usersgrandparent_rating_key_idsparent_rating_key_idsrating_key_idsc                       e Zd ZdZd ZddZ	 	 	 ddZg fdZd dZddZ	d!d	Z
d"d
Zd#dZd$dZ	 	 	 d%dZd dZd&dZd'dZd(dZd)dZd$dZd"dZd*dZd+dZd#dZd Zd#dZd Zd,dZd$dZd$dZy)-DataFactoryz=
    Retrieve and process data from the monitor database
    c                      y N )selfs    #/opt/Tautulli/plexpy/datafactory.py__init__zDataFactory.__init__+   s        Nc                 $
   t        j                         }|g }|t        j                  j                  }|t        j                  j
                  }t        j                         rt        t        j                               }d}|D ]a  }d|d   v st        |d   t              r||d   vr|d   j                  |       n%t        |d   t              r|d   |k7  r
|d   |g|d<   d} n |s&|j                  dt        j                         gg       |rdgndg}	g d	}
|r3d
}|D cg c]  }|d   j                  d      d   |d   g  }}dg}g d}nd }g x}x}}	 |j                  d||
||||	|g dg dddgddgddgg|      }|d   }d}| j#                  |      }t        j                  j$                  t        j                  j&                  t        j                  j(                  dt        j                  j&                  d}g }i }|D ]o  }|d   rO|s6t+        j,                         j/                         D ci c]  }|d   |d    }}|j1                  |d         |d<   |t3        j4                  |d         z  }|d    d!k(  r|d"   r|d"   }n|d    d!k(  r|d#   }n|d   }|d$   rd%|d&<   ||d       d'z  }|d$   s(t3        j6                  |d    |d(   |d)   |d*   |d+         rd}n)|d&   |d,z  k\  rd-}n|d&   |d.z  k\  rd/}n|d&   |k\  rd0}nd}t8        j:                  j1                  |d1   |d1         }|d2   r|d2   |d   k7  r|d2   }n|d   r|d   }nt8        j<                  }i d3|d3   d4|d4   d5|d4   d6|d6   d7|d7   d8|d8   d)|d   d|d   d9|d9   d|d   d:|d:   d;|d;   d|d1|d<|d<   d=|d=   d>|d>   i d$|d$   d?|d?   d@|d@   dA|dA   dB|dB   d |d    dC|dC   dD|dD   dE|dE   dF|dF   dG|dG   dH|dH   dI|dI   dJ|dJ   dK|dK   dL|dL   dM|dM   ||dN   |dO   |dP   t?        tA        |d&               ||dQ   |dR   |d   |d   dS
}|j                  |       r |dT   |dU   t        jB                  |      |dV   t3        jD                  |dWX      t3        jD                  |dWX      dY} | S c c}w # t        $ r+}t        j                   d|z         dddg dddcY d }~S d }~ww xY wc c}w )ZNFuser_idr      Tsession_history.user_idsession_history.reference_idsession_history.id)2r   zsession_history.id AS row_idzMAX(started) AS datezMIN(started) AS startedzMAX(stopped) AS stoppedzSUM(CASE WHEN stopped > 0 THEN (stopped - started) ELSE 0 END) -              SUM(CASE WHEN paused_counter IS NULL THEN 0 ELSE paused_counter END) AS play_durationVSUM(CASE WHEN paused_counter IS NULL THEN 0 ELSE paused_counter END) AS paused_counterzsession_history.view_offsetr   zsession_history.userz(CASE WHEN users.friendly_name IS NULL OR TRIM(users.friendly_name) = ''              THEN users.username ELSE users.friendly_name END) AS friendly_namezusers.thumb AS user_thumbz'users.custom_avatar_url AS custom_thumbplatformproductplayer
ip_address
machine_idlocationsecurerelayedzsession_history.media_typez}(CASE WHEN session_history_metadata.live = 1 THEN 'live' ELSE session_history.media_type END)              AS media_type_livez#session_history_metadata.rating_keyz*session_history_metadata.parent_rating_keyz/session_history_metadata.grandparent_rating_keyz#session_history_metadata.full_titlezsession_history_metadata.titlez%session_history_metadata.parent_titlez*session_history_metadata.grandparent_titlez'session_history_metadata.original_titlezsession_history_metadata.yearz$session_history_metadata.media_indexz+session_history_metadata.parent_media_indexzsession_history_metadata.thumbz%session_history_metadata.parent_thumbz*session_history_metadata.grandparent_thumbzsession_history_metadata.livez!session_history_metadata.added_atz0session_history_metadata.originally_available_atzsession_history_metadata.guida0  MAX((CASE WHEN (view_offset IS NULL OR view_offset = '') THEN 0.1 ELSE view_offset * 1.0 END) /              (CASE WHEN (session_history_metadata.duration IS NULL OR session_history_metadata.duration = '')              THEN 1.0 ELSE session_history_metadata.duration * 1.0 END) * 100) AS percent_completez!session_history_metadata.durationz-session_history_metadata.marker_credits_firstz-session_history_metadata.marker_credits_finalz-session_history_media_info.transcode_decisionzCOUNT(*) AS group_countz-GROUP_CONCAT(session_history.id) AS group_idszNULL AS statezNULL AS session_keysessions.session_key)2zNULL AS reference_idzNULL AS row_idzstarted AS datestartedstoppedzSUM(CASE WHEN stopped > 0 THEN (stopped - started) ELSE (strftime('%s', 'now') - started) END) -                  SUM(CASE WHEN paused_counter IS NULL THEN 0 ELSE paused_counter END) AS play_durationr   view_offsetr   userz(CASE WHEN friendly_name IS NULL OR TRIM(friendly_name) = ''                  THEN user ELSE friendly_name END) AS friendly_namezNULL AS user_thumbzNULL AS custom_thumbr   r    r!   r"   r#   r$   r%   r&   
media_typezG(CASE WHEN live = 1 THEN 'live' ELSE media_type END) AS media_type_live
rating_keyparent_rating_keygrandparent_rating_key
full_titletitleparent_titlegrandparent_titleoriginal_titleyearmedia_indexparent_media_indexthumbparent_thumbgrandparent_thumbliveadded_atoriginally_available_atguidzMAX((CASE WHEN (view_offset IS NULL OR view_offset = '') THEN 0.1 ELSE view_offset * 1.0 END) /                  (CASE WHEN (duration IS NULL OR duration = '')                  THEN 1.0 ELSE duration * 1.0 END) * 100) AS percent_completedurationzNULL AS marker_credits_firstzNULL AS marker_credits_finaltranscode_decisionzNULL AS group_countzNULL AS group_idsstater*   session_history)zLEFT OUTER JOINJOINrF   )r
   session_history_metadatasession_history_media_infozusers.user_idzsession_history_metadata.idzsession_history_media_info.id)
table_nametable_name_unioncolumnscolumns_unioncustom_wherecustom_where_uniongroup_bygroup_by_union
join_typesjoin_tables
join_evalskwargszMTautulli DataFactory :: Unable to execute database query for get_history: %s.0)recordsFilteredrecordsTotaldrawdatafilter_durationtotal_durationresultrM   )movieepisodetrackphotocliprD   r;   
user_thumbplay_durationr/   r_   r<   r=   r>   d   percent_completeg      @r-   rB   marker_credits_firstmarker_credits_finalg      @g      ?g       @g      ?g      ?r   custom_thumbreference_idrow_ididdater+   r,   paused_counterr.   friendly_namer    r!   r"   r#   r$   r%   r&   r0   r1   r2   r3   r4   r5   r6   r7   r8   r9   r:   r@   rA   rC   group_count	group_ids)
r;   r@   rA   rC   rf   watched_statusrp   rq   rD   r*   filteredCount
totalCountrX   s)units)rV   rW   rY   rX   rZ   r[   )#r   
DataTablesplexpyCONFIGGROUP_HISTORY_TABLESHISTORY_TABLE_ACTIVITYr	   get_session_user_idstr
isinstancelistappendsplit	ssp_query	Exceptionr   warnget_total_durationMOVIE_WATCHED_PERCENTTV_WATCHED_PERCENTMUSIC_WATCHED_PERCENTr
   Users	get_usersgetr   cast_to_intcheck_watchedr   PLATFORM_NAME_OVERRIDESDEFAULT_USER_THUMBintroundfriendly_name_to_usernamehuman_duration)!r   rT   rM   groupinginclude_activitydata_tablessession_user_idaddedc_whererO   rK   rJ   crN   rP   rL   queryehistoryrZ   r[   watched_percentrowsusers_lookupitemur;   base_watched_valuerr   r   rc   rowdicts!                                    r   get_datatables_historyz"DataFactory.get_datatables_history.   sm    ++-L}}99H#%}}CC&&(!'"="="?@OE' 
*!'!*d3wWXz8Y
))/:#GAJ49V&-aj/%B
 E ##%>A\A\A^@_$`a7?23FZE[8t )GS!T!1Q4::c?2#6!"=!T!T+_N7Mt  $BDDD-	+))5F;K298E7C=O3;9G6>7U 8QRa6b7KMj6k7KMl6m6o 28# * 9E6 /00l0K$*MM$G$G&,mm&F&F$*MM$G$G$%#)==#C#C	  ]	DG}#FKkkmF]F]F_#`AiL!G*$<#`L#`%1%5%5d9o%F\"w2243HIIOL!Y.43G^,l#y001WF|+.'(!0l1C!Ds!JF|w44\"D$7j9I+,d3I.J  "#()-?#-EE!%()-?#-EE!%()-??!%!" 5599$z:JDQ[L\]HN#^(<\@R(R!.1
l#!,/
#66
,>4#7 ,T(^,h, 4<, d9o	,
 d9o, tO4, #D$9, $T*:%;, d9o, 4<, #D$9,  , x, d9o, T(^,   l!3!," 4<#,$  l!3%,& tJ/',( T(^),* d9o+,,  l!3-,.  l!3/,0 '-@(A1,2 ,T2J-K3,4  l!35,6 DM7,8 "4#79,: '-@(A;,< $T*:%;=,> 4<?,@ !$}"5A,B (.B)CC,D ".23L.M<)-.B)C'*56H1I+J'K%3"&}"5 $[ 1 M"&}"5W,C\ KK{]	~ $)#9 %l 399$?f#*#9#9/QT#U")"8"8s"S g "Uf  	+KKgjkkl'($%'*&)+ +	+< $as*   &#S*S <T	T
 T?T
T
c                   0 t        j                         }t        j                  |      }t        j                  |      }t        j                  |      }|r|g}|t        j
                  j                  }|t        j
                  j                  }d}|
r3|d|
z  z  }|s)t        j                  |
      |dz  dz  dz  z
  }|d|z  z  }|r3|d|z  z  }|
s)t        j                  |      |dz  dz  dz  z   }|d|z  z  }|
r|s(t        j                         |dz  dz  dz  z
  }|d|z  z  }d}|r|d	|z  z  }|	r|d
|	z  z  }|rdnd}|dk(  rdnd}g }|D ]  }|dk(  rg }d|d|d|d|d|d|d}|j                  |      }|D ]  }i d|d   d|d   d|d   d|d   ddd|d   ddd|d   d dd!|d!   d"|d"   d#|d#   d$|d$   d%|d%   d&|d&   r|d&   j                  d'      nd(d)dd*dd|d+   |d,   |d-   d.}|j                  |        |j                  ||d/t!        j"                  |      d0       |d1k(  rg }d2|d|d|d3|d|d|d}|j                  |      }|D ]  }i d|d   d|d   d|d   d|d   ddd|d   d|d   d dd!|d!   d"|d"   d#|d#   d$|d$   d%|d%   d&|d&   r|d&   j                  d'      nd(d)dd*dd5d|d+   |d,   |d-   d6}|j                  |        |j                  |d7t!        j"                  |      d8       |d9k(  rg }d:|d|d|d;|d|d|d}|j                  |      }|D ]  }i d|d=   d|d   d|d   d|d   ddd|d+   r|d   n|d   d|d   d|d   d |d    d!|d    d"|d"   d#|d#   d$|d$   d%|d%   d&|d&   r|d&   j                  d'      nd(d)dd*dd|d+   |d,   |d-   d.}|j                  |        |j                  ||d>t!        j"                  |      d0       |d?k(  rg }d@|d|d|dA|d|d|d}|j                  |      }|D ]  }i d|d=   d|d   d|d   d|d+   r|d   n|d   d|d   d|d   d|d   d |d    d!|d    d"|d"   d#|d#   d$|d$   d%|d%   d&|d&   r|d&   j                  d'      nd(d)dd*dd5d|d+   |d,   |d-   d6}|j                  |        |j                  |dCt!        j"                  |      d8       |dDk(  rg }dE|d|d|dF|d|d|d}|j                  |      }|D ]  }i d|dH   xs |d=   d|d   d|d   d|d   ddd|d   d|d   d|d   d |d    d!|d    d"|d"   d#|d#   d$|d$   d%|d%   d&|d&   r|d&   j                  d'      nd(d)dd*dd|d+   |d,   |d-   d.}|j                  |        |j                  ||dIt!        j"                  |      d0       |dJk(  rg }dK|d|d|dL|d|d|d}|j                  |      }|D ]  }i d|dH   xs |d=   d|d   d|d   d|d   d|d   d|d   d|d   d |d    d!|d    d"|d"   d#|d#   d$|d$   d%|d%   d&|d&   r|d&   j                  d'      nd(d)dd*dd5d|d+   |d,   |d-   d6}|j                  |        |j                  |dNt!        j"                  |      d8       |dOk(  rg }dP|dQd  d|d|dR|d|d|d}|j                  |      }|D ];  }|dT   r|dT   |dU   k7  r|dT   } n|dU   r|dU   } nt$        j&                  } |dV   r|dV   |dW   k7  r|dV   }!n,|dW   t$        j(                  k(  rt$        j*                  }!n|dW   }!|d    r|d    dk(  r|d!   }"n|d    }"i d|d   d|d   dX|dX   dY|dY   d#|d#   d|d   dU| dW|!d!|"d |d    d"|d"   d)dd*dddd5dd|d   d=|d=   |d   |d   |dZ   |d[   |d   |d   |d$   |d%   |d&   r|d&   j                  d'      nd(|d+   |d,   |d-   d\}|j                  |       > |j                  ||d]t!        j"                  |      d0       n|d^k(  rPg }#d_|dQd  d|d|d`|d|d|d}|j                  |      }|D ]  }|dT   r|dT   |db   k7  r|dT   }$n|db   r|db   }$nt$        j,                  }$|d    r|d    dk(  r|d!   }"n|d    }"i d)|d)   dc|dc   d*|d*   d|d   d|d   d|d   db|$d!|"d |d    d"|d"   ddd5dd|d   d=|d=   dd|d   d|d   dZ|dZ   |d[   |d   |d   |d$   |d%   |d&   r|d&   j                  d'      nd(|d+   |d,   |d-   de	}|#j                  |        |j                  ||dft!        j"                  |#      d0       |dgk(  rg }%dh|dQd  d|d|di|d|d|d}|j                  |      }|D ]  }t$        j.                  j1                  |d5   |d5         0t3        0fdkt$        j4                  j7                         D        dl      }&|d   |d   |d   0|&dddddddddddm}|%j                  |        |j                  ||dnt!        j"                  |%dop      d0       	|dqk(  rt        j
                  j8                  }'t        j
                  j:                  }(t        j
                  j<                  drk(  r
ds|'|(fz  })dt}*nYt        j
                  j<                  duk(  r
dv|'|(fz  })dt}*n2t        j
                  j<                  dwk(  rdx|'|(|'|(fz  })dt}*n	dy})dz|'|(fz  }*g }+d{|)d||d|d|d}|*d~|d|}|j                  |      }|D ]  }|d    r|d    dk(  r|d!   }"n|d    }"i d|d-   d)|d)   d*|d*   dc|dc   db|db   d|d   d=|d=   dd|d   d|d   dZ|dZ   d[|d[   d|d   d|d   d!|"d |d    d"|d"   d#|d#   |d$   |d%   |d&   r|d&   j                  d'      nd(|d   |d+   |d,   |d   d}|+j                  |        |j                  |dt!        j"                  |+      d8       |dk(  sd },g }-	 d|dQd  j?                  dd      z  }.d}/|.}|j                  |      }|r|-j                   |,|/|             d}/|.dz   }|j                  |      }|r|-j                   |,|/|             d}/|.dz   }|j                  |      }|r|-j                   |,|/|             d}/|.dz   }|j                  |      }|r|-j                   |,|/|             |j                  |d|-d8        |r|r|d   S |S # t        $ r#}t        j                  d|z         Y d }~ y d }~ww xY w# t        $ r#}t        j                  d4|z         Y d }~ y d }~ww xY w# t        $ r#}t        j                  d<|z         Y d }~ y d }~ww xY w# t        $ r#}t        j                  dB|z         Y d }~ y d }~ww xY w# t        $ r#}t        j                  dG|z         Y d }~ y d }~ww xY w# t        $ r#}t        j                  dM|z         Y d }~ y d }~ww xY w# t        $ r#}t        j                  dS|z         Y d }~ y d }~ww xY w# t        $ r#}t        j                  da|z         Y d }~ y d }~ww xY w# t        $ r#}t        j                  dj|z         Y d }~ y d }~ww xY w# t        $ r#}t        j                  d|z         Y d }~ y d }~ww xY w# t        $ r#}t        j                  d|z         Y d }~ y d }~ww xY w)N zQAND strftime('%%Y-%%m-%%d', datetime(started, 'unixepoch', 'localtime')) <= '%s'    <   z"AND session_history.stopped >= %s zQAND strftime('%%Y-%%m-%%d', datetime(started, 'unixepoch', 'localtime')) >= '%s' z"AND session_history.stopped <= %s z!AND session_history.stopped >= %sz$AND session_history.section_id = %s z!AND session_history.user_id = %s r   r   rB   r[   total_plays
top_moviesa  SELECT sh.id, shm.full_title, shm.year, sh.rating_key, shm.thumb, sh.section_id, shm.art, sh.media_type, shm.content_rating, shm.labels, sh.started, shm.live, shm.guid, MAX(sh.started) AS last_watch, COUNT(sh.id) AS total_plays, SUM(sh.d) AS total_duration FROM (SELECT *, SUM(CASE WHEN stopped > 0 THEN (stopped - started) -        (CASE WHEN paused_counter IS NULL THEN 0 ELSE paused_counter END) ELSE 0 END)        AS d    FROM session_history    WHERE session_history.media_type = 'movie'  z    GROUP BY zj) AS sh JOIN session_history_metadata AS shm ON shm.id = sh.id GROUP BY shm.full_title, shm.year ORDER BY z DESC, sh.started DESC LIMIT z OFFSET z\Tautulli DataFactory :: Unable to execute database query for get_home_stats: top_movies: %s.r4   r3   r8   users_watchedr0   r2   	last_play
last_watchr=   r;   art
section_idr/   content_ratinglabels;r   r.   ro   r>   rA   rl   )r   r>   rA   rk   zMost Watched Movies)stat_id	stat_type
stat_titler   popular_moviesa  SELECT sh.id, shm.full_title, shm.year, sh.rating_key, shm.thumb, sh.section_id, shm.art, sh.media_type, shm.content_rating, shm.labels, sh.started, shm.live, shm.guid, COUNT(DISTINCT sh.user_id) AS users_watched, MAX(sh.started) AS last_watch, COUNT(sh.id) as total_plays, SUM(sh.d) AS total_duration FROM (SELECT *, SUM(CASE WHEN stopped > 0 THEN (stopped - started) -        (CASE WHEN paused_counter IS NULL THEN 0 ELSE paused_counter END) ELSE 0 END)        AS d    FROM session_history    WHERE session_history.media_type = 'movie' z~) AS sh JOIN session_history_metadata AS shm ON shm.id = sh.id GROUP BY shm.full_title, shm.year ORDER BY users_watched DESC, z`Tautulli DataFactory :: Unable to execute database query for get_home_stats: popular_movies: %s.r   )r>   rA   rk   zMost Popular Movies)r   r   r   top_tva  SELECT sh.id, shm.grandparent_title, sh.grandparent_rating_key, shm.grandparent_thumb, sh.section_id, shm.year, sh.rating_key, shm.art, sh.media_type, shm.content_rating, shm.labels, sh.started, shm.live, shm.guid, MAX(sh.started) AS last_watch, COUNT(sh.id) AS total_plays, SUM(sh.d) AS total_duration FROM (SELECT *, SUM(CASE WHEN stopped > 0 THEN (stopped - started) -        (CASE WHEN paused_counter IS NULL THEN 0 ELSE paused_counter END) ELSE 0 END)        AS d    FROM session_history    WHERE session_history.media_type = 'episode' zg) AS sh JOIN session_history_metadata AS shm ON shm.id = sh.id GROUP BY shm.grandparent_title ORDER BY zXTautulli DataFactory :: Unable to execute database query for get_home_stats: top_tv: %s.r6   zMost Watched TV Shows
popular_tvaJ  SELECT sh.id, shm.grandparent_title, sh.grandparent_rating_key, shm.grandparent_thumb, sh.section_id, shm.year, sh.rating_key, shm.art, sh.media_type, shm.content_rating, shm.labels, sh.started, shm.live, shm.guid, COUNT(DISTINCT sh.user_id) AS users_watched, MAX(sh.started) AS last_watch, COUNT(sh.id) as total_plays, SUM(sh.d) AS total_duration FROM (SELECT *, SUM(CASE WHEN stopped > 0 THEN (stopped - started) -        (CASE WHEN paused_counter IS NULL THEN 0 ELSE paused_counter END) ELSE 0 END)        AS d    FROM session_history    WHERE session_history.media_type = 'episode' z{) AS sh JOIN session_history_metadata AS shm ON shm.id = sh.id GROUP BY shm.grandparent_title ORDER BY users_watched DESC, z\Tautulli DataFactory :: Unable to execute database query for get_home_stats: popular_tv: %s.zMost Popular TV Shows	top_musica   SELECT sh.id, shm.grandparent_title, shm.original_title, shm.year, sh.grandparent_rating_key, shm.grandparent_thumb, sh.section_id, shm.art, sh.media_type, shm.content_rating, shm.labels, sh.started, shm.live, shm.guid, MAX(sh.started) AS last_watch, COUNT(sh.id) AS total_plays, SUM(sh.d) AS total_duration FROM (SELECT *, SUM(CASE WHEN stopped > 0 THEN (stopped - started) -        (CASE WHEN paused_counter IS NULL THEN 0 ELSE paused_counter END) ELSE 0 END)        AS d    FROM session_history    WHERE session_history.media_type = 'track' z{) AS sh JOIN session_history_metadata AS shm ON shm.id = sh.id GROUP BY shm.original_title, shm.grandparent_title ORDER BY z[Tautulli DataFactory :: Unable to execute database query for get_home_stats: top_music: %s.r7   zMost Played Artistspopular_musicaM  SELECT sh.id, shm.grandparent_title, shm.original_title, shm.year, sh.grandparent_rating_key, shm.grandparent_thumb, sh.section_id, shm.art, sh.media_type, shm.content_rating, shm.labels, sh.started, shm.live, shm.guid, COUNT(DISTINCT sh.user_id) AS users_watched, MAX(sh.started) AS last_watch, COUNT(sh.id) as total_plays, SUM(sh.d) AS total_duration FROM (SELECT *, SUM(CASE WHEN stopped > 0 THEN (stopped - started) -        (CASE WHEN paused_counter IS NULL THEN 0 ELSE paused_counter END) ELSE 0 END)        AS d    FROM session_history    WHERE session_history.media_type = 'track' z) AS sh JOIN session_history_metadata AS shm ON shm.id = sh.id GROUP BY shm.original_title, shm.grandparent_title ORDER BY users_watched DESC, z_Tautulli DataFactory :: Unable to execute database query for get_home_stats: popular_music: %s.zMost Popular Artiststop_librariesa   SELECT sh.id, shm.title, shm.grandparent_title, shm.full_title, shm.year, shm.media_index, shm.parent_media_index, sh.rating_key, shm.grandparent_rating_key, shm.thumb, shm.grandparent_thumb, sh.user, sh.user_id, sh.player, sh.section_id, shm.art, sh.media_type, shm.content_rating, shm.labels, shm.live, shm.guid, ls.section_name, ls.section_type, ls.thumb AS library_thumb, ls.custom_thumb_url AS custom_thumb, ls.art AS library_art, ls.custom_art_url AS custom_art, sh.started, MAX(sh.started) AS last_watch, COUNT(sh.id) AS total_plays, SUM(sh.d) AS total_duration FROM (SELECT *, SUM(CASE WHEN stopped > 0 THEN (stopped - started) -        (CASE WHEN paused_counter IS NULL THEN 0 ELSE paused_counter END) ELSE 0 END)        AS d    FROM session_history    WHERE    z) AS sh JOIN session_history_metadata AS shm ON shm.id = sh.id LEFT OUTER JOIN (SELECT * FROM library_sections WHERE deleted_section = 0)    AS ls ON sh.section_id = ls.section_id GROUP BY sh.section_id ORDER BY z_Tautulli DataFactory :: Unable to execute database query for get_home_stats: top_libraries: %s.ri   library_thumb
custom_artlibrary_artsection_typesection_namer9   r:   )grandchild_titler8   r9   r:   r0   r2   r/   r   r   r>   rA   rk   zMost Active Libraries	top_usersa$  SELECT sh.id, shm.title, shm.grandparent_title, shm.full_title, shm.year, shm.media_index, shm.parent_media_index, sh.rating_key, shm.grandparent_rating_key, shm.thumb, shm.grandparent_thumb, sh.user, sh.user_id, sh.player, sh.section_id, shm.art, sh.media_type, shm.content_rating, shm.labels, shm.live, shm.guid, u.thumb AS user_thumb, u.custom_avatar_url AS custom_thumb, sh.started, (CASE WHEN u.friendly_name IS NULL OR TRIM(u.friendly_name) = ''   THEN u.username ELSE u.friendly_name END)    AS friendly_name, MAX(sh.started) AS last_watch, COUNT(sh.id) AS total_plays, SUM(sh.d) AS total_duration FROM (SELECT *, SUM(CASE WHEN stopped > 0 THEN (stopped - started) -        (CASE WHEN paused_counter IS NULL THEN 0 ELSE paused_counter END) ELSE 0 END)        AS d    FROM session_history    WHERE z) AS sh JOIN session_history_metadata AS shm ON shm.id = sh.id LEFT OUTER JOIN users AS u ON sh.user_id = u.user_id GROUP BY sh.user_id ORDER BY z[Tautulli DataFactory :: Unable to execute database query for get_home_stats: top_users: %s.rc   r   r   )	r:   r0   r2   r/   r   r   r>   rA   rk   zMost Active Userstop_platformsa?  SELECT sh.platform, sh.started, MAX(sh.started) AS last_watch, COUNT(sh.id) AS total_plays, SUM(sh.d) AS total_duration FROM (SELECT *, SUM(CASE WHEN stopped > 0 THEN (stopped - started) -        (CASE WHEN paused_counter IS NULL THEN 0 ELSE paused_counter END) ELSE 0 END)        AS d    FROM session_history    WHERE z&) AS sh GROUP BY sh.platform ORDER BY z_Tautulli DataFactory :: Unable to execute database query for get_home_stats: top_platforms: %s.c              3   N   K   | ]  \  }}|j                         v s|  y wr   )lower).0kvr   s      r   	<genexpr>z-DataFactory.get_home_stats.<locals>.<genexpr>z  s'     )o1YZ^f^l^l^nYn!)os   %%default)r   r[   r   r   platform_namer4   r;   r=   r   r   r0   r2   r.   ro   rk   zMost Active PlatformsFmask_metadatalast_watchedr   z(CASE WHEN shm.marker_credits_final IS NULL THEN sh._duration * (CASE WHEN sh.media_type = 'movie' THEN %d ELSE %d END) / 100.0 ELSE shm.marker_credits_final END) AS watched_thresholdz!_view_offset >= watched_threshold   z(CASE WHEN shm.marker_credits_first IS NULL THEN sh._duration * (CASE WHEN sh.media_type = 'movie' THEN %d ELSE %d END) / 100.0 ELSE shm.marker_credits_first END) AS watched_threshold   a  MIN((CASE WHEN shm.marker_credits_first IS NULL THEN sh._duration * (CASE WHEN sh.media_type = 'movie' THEN %d ELSE %d END) / 100.0 ELSE shm.marker_credits_first END), sh._duration * (CASE WHEN sh.media_type = 'movie' THEN %d ELSE %d END) / 100.0) AS watched_thresholdzNULL AS watched_thresholdzlsh.media_type == 'movie' AND percent_complete >= %d OR sh.media_type == 'episode' AND percent_complete >= %daZ  SELECT sh.id, shm.title, shm.grandparent_title, shm.full_title, shm.year, shm.media_index, shm.parent_media_index, sh.rating_key, shm.grandparent_rating_key, shm.thumb, shm.grandparent_thumb, sh.user, sh.user_id, u.custom_avatar_url as user_thumb, sh.player, sh.section_id, shm.art, sh.media_type, shm.content_rating, shm.labels, shm.live, shm.guid, (CASE WHEN u.friendly_name IS NULL OR TRIM(u.friendly_name) = ''   THEN u.username ELSE u.friendly_name END)    AS friendly_name, MAX(sh.started) AS last_watch, sh._view_offset, sh._duration, (sh._view_offset / sh._duration * 100) AS percent_complete, a   FROM (SELECT *, MAX(session_history.id),    (CASE WHEN view_offset IS NULL THEN 0.1 ELSE view_offset * 1.0 END) AS _view_offset,    (CASE WHEN duration IS NULL THEN 1.0 ELSE duration * 1.0 END) AS _duration    FROM session_history    JOIN session_history_metadata ON session_history_metadata.id = session_history.id    WHERE (session_history.media_type = 'movie'            OR session_history.media_type = 'episode') zz) AS sh JOIN session_history_metadata AS shm ON shm.id = sh.id LEFT OUTER JOIN users AS u ON sh.user_id = u.user_id WHERE z/ GROUP BY sh.id ORDER BY last_watch DESC LIMIT z^Tautulli DataFactory :: Unable to execute database query for get_home_stats: last_watched: %s.rk   r!   )r/   r   r   r   r>   rA   r!   zRecently Watchedmost_concurrentc                 n   g }|D ]H  }|j                  t        |d         dz   dd       |j                  t        |d         dz   dd       J t        |d 	      }d
}d
}d}| d
ddd}|D ]E  }|d   dk(  r||d   z  }||k\  s|d   }||k\  r|}||d<   |dd |d<   |d   dd |d<   ||d   z  }G |S )z
                    Function to calculate most concurrent streams
                    Input: Stat title, SQLite query result
                    Output: Dict {title, count, started, stopped}
                    r+   Br   )timecountr,   Ar)   c                     | d   S )Nr   r   )r   s    r   <lambda>zJDataFactory.get_home_stats.<locals>.calc_most_concurrent.<locals>.<lambda>  s
    &	 r   )keyr   r   N)r4   r   r+   r,   r   r   )r   r}   sorted)	r4   r\   timesr   r   
last_count
last_start
concurrentds	            r   calc_most_concurrentz8DataFactory.get_home_stats.<locals>.calc_most_concurrent  s    E & Xc$y/.BS.HST%UVc$y/.BS.HSU%VWX #5.ABEE!"J!#J+0+,-1-1"$J # 0W:?!QwZ/E$
2-.vY
$
2-2
6;
7 38B3B
9 589&	#2
9 5!QwZ/E0 &%r   z}SELECT sh.started, sh.stopped FROM session_history AS sh JOIN session_history_media_info AS shmi ON sh.id = shmi.id WHERE %s zsession_history.zsh.zConcurrent StreamszConcurrent Transcodesz*AND shmi.transcode_decision = 'transcode' zConcurrent Direct Streamsz%AND shmi.transcode_decision = 'copy' zConcurrent Direct Playsz,AND shmi.transcode_decision = 'direct play' zaTautulli DataFactory :: Unable to execute database query for get_home_stats: most_concurrent: %s.zMost Concurrent Streamsr   ) r   MonitorDatabaser   r   rx   ry   rz   HOME_STATS_CARDSYMD_to_timestamp	timestampselectr   r   r   r   r   r	   mask_session_infor   DEFAULT_COVER_THUMBDEFAULT_LIVE_TV_ART_FULLDEFAULT_LIVE_TV_ARTr   r   r   nextPLATFORM_NAMESitemsr   r   WATCHED_MARKERreplace)1r   r   
time_range
stats_typestats_startstats_countr   stats_cardsr   r   beforeafter
monitor_dbwhere_timeframer   where_idrO   	sort_type
home_statsstatr   r   r\   r   r   r   r   r   r   r   r   r   r   r   r;   r   rc   top_platformr   movie_watched_percenttv_watched_percentwatched_thresholdwatched_wherer   r   r   
base_queryr4   r   s1                                                   @r   get_home_statszDataFactory.get_home_stats^  s    --/
((4
))+6))+6")K}}99H --88Kru{{{O#44V<zBQS?SVX?XX	#G)#SSruzzzO#44U;j2oPR>RUW>WW	#G)#SS5))+j2o.BR.GGIBYNNO>KKH;gEEH5=1CW(2j(@$m	
 F	=D|#
  6EhPXZcepr}E (..u5F
 # +D7D$6 !4<($}*= ,T2B-C +B	
 (l); 4R '\(: / #DM !$u+ (l); (l); ,T2B-C $$x.T(^%9%9#%>VX "2  +B!" (*#'<#'<%)$Z)C, %%c*/+2 !!d091F+2+D+DZ+P#R S
 ))!#  6EhPXZcepr}E (..u5F
 # /D7D$6 !4<*D,A (l); 4R	
 '\(: )$}*= / #DM !$u+ (l); (l); ,T2B-C $$x.T(^%9%9#%>VX "2 +B  &r!" $(<#'<%)$Z'C* #))#.-/0 !!d1F+2+D+D^+T#V W !  6EhPXZcepr}E (..u5F
 # 'D7D)<$= !4<($}*= ,T2B-C +B	
 (tF|l);QUVnQo 4T:R5S '\(: /5H0I #D)<$= !$u+ (l); (l); ,T2B-C $$x.T(^%9%9#%>VX "2  +B!" (*#'<#'<%)$Z)C, MM#&/'2 !!d091H+2+D+DV+L#N O
 %
   6EhPXZcepr}E  (..u5F
 # +D7D)<$= !4<*D,A (tF|l);QUVnQo 4T:R5S	
 '\(: )$}*= /5H0I #D)<$= !$u+ (l); (l); ,T2B-C $$x.T(^%9%9#%>VX "2 +B  &r!" $(<#'<%)$Z'C* %%c*-+0 !!d1H+2+D+DZ+P#R S $	  6EhPXZcepr}E (..u5F
 # *D7D)9$:$WdCV>W !4<($}*= ,T2B-C +B	
 (.F)G 4T:R5S '\(: /5H0I #D)<$= !$u+ (l); (l); ,T2B-C $$x.T(^%9%9#%>VX "2  +B!" (*#'<#'<%)$Z)C, $$S)/*2 !!d091F+2+D+DY+O#Q R
 ( "  6EhPXZcepr}E (..u5F
 # .D7D)9$:$WdCV>W !4<*D,A (.F)G 4T:R5S	
 '\(: )$}*= /5H0I #D)<$= !$u+ (l); (l); ,T2B-C $$x.T(^%9%9#%>VX "2 +B  &r!" $(<#'<%)$Z'C* "((--.0 !!d1G+2+D+D]+S#U V ( " , 6EQR5H(T\^git  wB+CE, (..u5F
 # 3.DN+^0D_H]0](,^(<o.(,_(=(.(B(BL)d<.@DDW.W&*<&8m,0O0OO&,&@&@&*=&9 34=P8QUW8W $W $%8 9%tM':($/?*@ '^(< '^(<	
 %d<&8 $T,%7 ( &{   ,T2E-F tE{  ( ( #B   l!3!" ,T2E-F#$ -1M $V'+M':.23G.H&*<&8267O2P&*<&8*./?*@?CH~$x."6"6s";SU $V $V"&t*;C> "((-g3.j !!d091H+2+D+D]+S#U V
 $	 , 6EQR5H(T\^git  wB+CE, (..u5F
 # (*DN+^0D\HZ0Z%).%9
l+%),%7
%+%>%>
 34=P8QUW8W $W $%8 964< $d9o*D,A )$}*= ,T2B-C	
 '\(: ( #E /5H0I !$u+ +B &r #D$6 /5H0I .tG} "4<  )$}*=!" 266J1K)-l);59:R5S)-l);-12B-CBFx.T(^%9%9#%>VX#'<#'<%)$Z3C6 $$S)Q(*T !!d091D+2+D+DY+O#Q R
 (!  6EQR5H(T\^git  wB
CE (..u5F
 # -D%==AA$zBRTXYcTdeH$()o8M8M8S8S8U)oqz${M*.}*=-12B-C(,\(:'/,9$&$&02"$,.)+57#%,.%'C  !'',+-. !!d091H+2+D+D\af+g#i j
 '(.(K(K%%+]]%E%E"==//14/ /0BC)D% %HM]]11Q6/ /0BC)D% %HM]]11Q6/ /0BDY[mn)o% %HM(C%S.0BC%DM
  " 2 5F4CXxYf4?5NE6 (..u5F
 # -D 34=P8QUW8W $W $%8 98T$Z !4<*D,A %d9o (l);	
 #D$6 /5H0I .tG} "4< )$}*= 06J1K (l); 4T:R5S #E /5H0I !$u+  (l);!" *.l);-12B-CBFx.T(^%9%9#%>VX)-l);#'<#'<%)(^/C2 !'',?-B !!d1C+2+D+D\+R#T U **"&H #%" "- 0?qr/B/J/JK]_d/e"fJ
 1E&E'..u5F'../CE6/RS3E&HIE'..u5F'../CE6/RS7E&CDE'..u5F'../CE6/RS5E&JKE'..u5F'../CE6/RS
 !!d1J+:#< =IF	=P za= q !  KK ~  BC  !C  D h !  KK  !C  FG  !G  H f !  KK z}~ ~ l !  KK ~  BC  !C  D d !  KK }  AB  !B  C j !  KK  !B  EF  !F  G t !  KK  !B  EF  !F  G p !  KK }  AB  !B  C F !  KK  !B  EF  !F  G H !  KK  !A  DE  !E  F b !  KK  !D  GH  !H  I s	  &w#&x9&y8&y05&z1&{,){=	)|,)}:(~
#C~9#	x,x

x	x>x99x>	y-
y((y-0	z9zz	{({{	{:{55{:=	|)|$$|),	}5}}	~$~~
	~6~11~69	%  %c                    t        j                         }t        j                         rt        j                         }g }	 ddj	                  |      z  }|j                  |      }|D ]  }|d   r|d   |d   k7  r|d   }n|d   r|d   }nt        j                  }|d   r|d   |d   k7  r|d   }	n|d   }	|d   r|d   d	k(  r|d
   }
n|d   }
i d|d   d|d   d|d   d|d|	d|d   d|d   d|d   d
|
xs d	d|d   xs d	d|d   xs d	d|d   d|d   d|d   d|d   d|d   d|d   |d   |d   |d   |d   |d   r|d   j                  d      nd |d!   |d"   |d#   d$}|j                  |        t        j                  |      }t        j                  |d      }|S # t        $ r"}t        j                  d|z         Y d }~y d }~ww xY w)%Nac  SELECT ls.id, ls.section_id, ls.section_name, ls.section_type, ls.thumb AS library_thumb, ls.custom_thumb_url AS custom_thumb, ls.art AS library_art, ls.custom_art_url AS custom_art, ls.count, ls.parent_count, ls.child_count, sh.id, shm.title, shm.grandparent_title, shm.full_title, shm.year, shm.media_index, shm.parent_media_index, sh.rating_key, shm.grandparent_rating_key, shm.thumb, shm.grandparent_thumb, sh.user, sh.user_id, sh.player, shm.art, sh.media_type, shm.content_rating, shm.labels, shm.live, shm.guid, MAX(sh.started) AS last_watch FROM library_sections AS ls LEFT OUTER JOIN session_history AS sh ON ls.section_id = sh.section_id LEFT OUTER JOIN session_history_metadata AS shm ON sh.id = shm.id WHERE ls.section_id IN (%s) AND ls.deleted_section = 0 GROUP BY ls.id ORDER BY ls.section_type, ls.count DESC, ls.parent_count DESC, ls.child_count DESC ,zSTautulli DataFactory :: Unable to execute database query for get_library_stats: %s.ri   r   r   r   r=   r   r;   r   r   r   r   child_countparent_countgrandchild_countr   r4   r3   r6   r   r8   r9   r:   r0   r2   r/   r   r   r   r   r>   rA   rl   )r0   r2   r/   r   r   r>   rA   rk   )r   r   r	   get_session_shared_librariesjoinr   r   r   r   r   r   r   r   r   r   group_by_keys)r   library_cardsr   library_statsr   r\   r   r   r   r   r;   librarys               r   get_library_statszDataFactory.get_library_statsT  s   --/
//1#@@BM	j mpltlt  vC  mDDE  &&u-F
  ,	*DN#^(<_@U(U $^ 4o& $_ 5 & : :L!d<&8D<O&O"<0"=1+,5H0IR0OW01|T,%7 %tN';%tN'; ' %k	
 W %d>&: *4+>  +D1D,E,K d5k/R \ 2 +D1D,E *4= tF| %d=&9  ,T2F-G!" &*,%7156N1O%),%7)-.>)?>B8nh!5!5c!:RT#F|#F|!%d1G4   )Y,	*\  11-@--m^Lk  	KKmpqqr	s    %F4 4	G=GGc           
         ||g S |t         j                  j                  }|r5|3t        t        j
                  t        |      j                  d            }ng d}t	        j                         }t        j                         }g }t               }	|rdnd}
|dv r=t        j                         }|j                  ||      }|d   D cg c]  }|d   	 }}n|g}dj                  d	gt!        |      z        }|D ]  }||d
z  dz  dz  z
  }	 |dkD  rct        |      j#                         r*d|
d|d|d|d	}|j%                  ||g|dz  z         }n}|rd|
z  }|j%                  |||g      }n`g }n]t        |      j#                         r&d|
d|d|d|d	}|j%                  ||dz        }n|rd|
z  }|j%                  ||g      }ng }|D ]A  }|	j-                  |d          |d   r|d   }|d   }nd}d}|||d}|j/                  |       C   t1        d |	D              rg S |S c c}w # t&        $ r$}t)        j*                  d|z         g }Y d }~d }~ww xY w)Nr  )r         r   r   r   
collectionplaylistr0   r/   children_listr0   ?r   r   r   zSELECT (SUM(stopped - started) - SUM(CASE WHEN paused_counter IS NULL THEN 0 ELSE paused_counter END)) AS total_time, COUNT(DISTINCT z) AS total_plays, section_id FROM session_history JOIN session_history_metadata ON session_history_metadata.id = session_history.id WHERE stopped >= ? AND (session_history.grandparent_rating_key IN (+) OR session_history.parent_rating_key IN ($) OR session_history.rating_key IN (z))r   argsaD  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, section_id FROM session_history JOIN session_history_metadata ON session_history_metadata.id = session_history.id WHERE stopped >= ? AND session_history_metadata.guid = ? z) AS total_plays, section_id FROM session_history JOIN session_history_metadata ON session_history_metadata.id = session_history.id WHERE (session_history.grandparent_rating_key IN (a3  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, section_id FROM session_history JOIN session_history_metadata ON session_history_metadata.id = session_history.id WHERE session_history_metadata.guid = ? zTTautulli Libraries :: Unable to execute database query for get_watch_time_stats: %s.r   
total_timer   )
query_daysr   r   c              3   H   K   | ]  }t        j                  |         y wr   r	   allow_session_libraryr   r   s     r   r   z3DataFactory.get_watch_time_stats.<locals>.<genexpr>       [700<<[    ")rx   ry   rz   mapr   r   r}   r   r   r   r   setr   
PmsConnectget_item_childrenr  lenisdigitr   r   r   r   addr   any)r   r0   rA   r/   r   r!  r   r   item_watch_time_statssection_idsrO   pms_connectr\   childrating_keysrating_keys_argdaystimestamp_queryr   r   r   r   r   r   s                           r   get_watch_time_statsz DataFactory.get_watch_time_stats  s   $,I}}99H*0W00#j/2G2G2LMJ&J%%'	--/
 "e5=1CW33$//1K 22jU_2`F<B?<ST55.TKT%,K((C53{+;#;< K	2D'$)b.2*==O7!8:..0 %-oP_
!" ",!2!25?PS^abSb?b!2!c!I LT!T ",!2!25QU?V!2!W!#:..0 %-oP_	!" ",!2!25{Q!2!O!K
 NV!V ",!2!25v!2!F!#
  2\ 23%!%l!3J"&}"5K!"J"#K%)%/&1
 &,,S12yK	2Z [{[[I$$m U|  ruvvws   
H3CH88	I%I  I%c           	         |t         j                  j                  }t        j                         }g }t               }|rdnd}|dv r=t        j                         }	|	j                  ||      }
|
d   D cg c]  }|d   	 }}n|g}dj                  dgt        |      z        }	 t        |      j                         r&d	|d
|d|d|d	}|j                  ||dz        }
n|rd|z  }|j                  ||g      }
ng }
|
D ]p  }|j#                  |d          |d   r|d   |d   k7  r|d   }n|d   r|d   }nt$        j&                  }|d   |d   ||d   |d   |d   d}|j)                  |       r t+        d |D              rg S t-        j.                  |d      S c c}w # t        $ r$}t        j                   d|z         g }
Y d }~d }~ww xY w)Nr   r   r  r  r  r0   r  r  zSELECT (CASE WHEN users.friendly_name IS NULL OR TRIM(users.friendly_name) = '' THEN users.username ELSE users.friendly_name END) AS friendly_name, users.user_id, users.username, users.thumb, users.custom_avatar_url AS custom_thumb, COUNT(DISTINCT a[  ) AS total_plays, (SUM(stopped - started) - SUM(CASE WHEN paused_counter IS NULL THEN 0 ELSE paused_counter END)) AS total_time, section_id 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 (session_history.grandparent_rating_key IN (r  r  zD)) GROUP BY users.user_id ORDER BY total_plays DESC, total_time DESCr   r  a  SELECT (CASE WHEN users.friendly_name IS NULL OR TRIM(users.friendly_name) = '' THEN users.username ELSE users.friendly_name END) AS friendly_name, users.user_id, users.username, users.thumb, users.custom_avatar_url AS custom_thumb, COUNT(DISTINCT %s) AS total_plays, (SUM(stopped - started) - SUM(CASE WHEN paused_counter IS NULL THEN 0 ELSE paused_counter END)) AS total_time, section_id 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 session_history_metadata.guid = ? GROUP BY users.user_id ORDER BY total_plays DESC, total_time DESCzNTautulli Libraries :: Unable to execute database query for get_user_stats: %s.r   ri   r;   ro   r   usernamer   r   )ro   r   rc   r:  r   r   c              3   H   K   | ]  }t        j                  |         y wr   r#  r%  s     r   r   z-DataFactory.get_user_stats.<locals>.<genexpr>f  r&  r'  Fr   )rx   ry   rz   r   r   r)  r   r*  r+  r  r,  r}   r-  r   r   r   r   r.  r   r   r   r/  r	   r   )r   r0   rA   r/   r   r   
user_statsr1  rO   r2  r\   r3  r4  r5  r   r   r   rc   r   s                      r   get_user_statszDataFactory.get_user_stats  s   }}99H--/

e5=1CW33$//1K 22jU_2`F<B?<ST55.TKT%,K((C53{+;#;<'	:&&( %o" $**5{Q*GE HPP $**5v*>
  	#DOOD./N#^(<W(M!.1
g!']
#66
$($9"9o!+#J/"&}"5!%l!3C c"#	#& [{[[I((5IIK UV  	KKhkllmF	s   5F#&AF( (	G1GGc                 .   t        j                         }d}|rdnd}t        j                         rd|dt        j                         d}|rd|z  }|j	                  ||g      }n|rd	|z  }|j	                  ||g      }ny i }|D ]  }	d
}
|	d   s|	d   |	d<   |	d   xs |	d   |	d<   |	d   |	d<   |	d   xs |	d   |	d<   |	d   xs |	d   |	d<   |	d   xs |	d   |	d<   |	d   |	d<   |	d   xs |	d   |	d<   |	d    xs |	d!   |	d"<   |	d   |	d#<   |	d   |	d$<   d%}
i d&|	d&   d|	d   d'|	d'   d(|	d(   d)|	d)   d*|	d*   d+|	d+   d|	d   d|	d   d,|	d,   d#|	d#   d$|	d$   d-|	d-   d.|	d.   d/|	d/   d|	d   d0|	d0   i d!|	d!   d1|	d1   d2|	d2   d3|	d3   d4|	d4   d5|	d5   d6|	d6   d|	d   d7|	d7   d8|	d8   d|	d   d|	d   d|	d   d9|	d9   d|	d   d|	d   d:|	d:   i d;|	d;   d|	d   d|	d   d<|	d<   d"|	d"   d=|	d=   d>|	d>   d?|	d?   d@|	d@   dA|	dA   dB|	dB   dC|	dC   dD|	dD   dE|	dE   d|	d   d|	d   dF|	dF   |	dG   |	dH   |	dI   |rd%nd
|
dJ} |j                         D ci c]  \  }}||xs d }}}|S c c}}w )KNr   rE   r'   zAND z.user_id = r   a  SELECT bitrate, video_full_resolution, optimized_version, optimized_version_profile, optimized_version_title, synced_version, synced_version_profile, container, video_codec, video_bitrate, video_width, video_height, video_framerate, video_dynamic_range, aspect_ratio, audio_codec, audio_bitrate, audio_channels, audio_language, audio_language_code, subtitle_codec, subtitle_forced, subtitle_language, stream_bitrate, stream_video_full_resolution, quality_profile, stream_container_decision, stream_container, stream_video_decision, stream_video_codec, stream_video_bitrate, stream_video_width, stream_video_height, stream_video_framerate, stream_video_dynamic_range, stream_audio_decision, stream_audio_codec, stream_audio_bitrate, stream_audio_channels, stream_audio_language, stream_audio_language_code, subtitles, stream_subtitle_decision, stream_subtitle_codec, stream_subtitle_forced, stream_subtitle_language, transcode_hw_decoding, transcode_hw_encoding, video_decision, audio_decision, transcode_decision, width, height, container, transcode_container, transcode_video_codec, transcode_audio_codec, transcode_audio_channels, transcode_width, transcode_height, session_history_metadata.media_type, title, grandparent_title, original_title FROM session_history_media_info JOIN session_history ON session_history_media_info.id = session_history.id JOIN session_history_metadata ON session_history_media_info.id = session_history_metadata.id WHERE session_history_media_info.id = ? %sr  a  SELECT bitrate, video_full_resolution, optimized_version, optimized_version_profile, optimized_version_title, synced_version, synced_version_profile, container, video_codec, video_bitrate, video_width, video_height, video_framerate, video_dynamic_range, aspect_ratio, audio_codec, audio_bitrate, audio_channels, audio_language, audio_language_code, subtitle_codec, subtitle_forced, subtitle_language, stream_bitrate, stream_video_full_resolution, quality_profile, stream_container_decision, stream_container, stream_video_decision, stream_video_codec, stream_video_bitrate, stream_video_width, stream_video_height, stream_video_framerate, stream_video_dynamic_range, stream_audio_decision, stream_audio_codec, stream_audio_bitrate, stream_audio_channels, stream_audio_language, stream_audio_language_code, subtitles, stream_subtitle_decision, stream_subtitle_codec, stream_subtitle_forced, stream_subtitle_language, transcode_hw_decoding, transcode_hw_encoding, video_decision, audio_decision, transcode_decision, width, height, container, transcode_container, transcode_video_codec, transcode_audio_codec, transcode_audio_channels, transcode_width, transcode_height, media_type, title, grandparent_title, original_title FROM sessions WHERE session_key = ? %sr   stream_containervideo_full_resolutionstream_video_full_resolutiontranscode_container	containervideo_decisionstream_video_decisiontranscode_video_codecvideo_codecstream_video_codectranscode_widthwidthstream_video_widthtranscode_heightheightstream_video_heightaudio_decisionstream_audio_decisiontranscode_audio_codecaudio_codecstream_audio_codectranscode_audio_channelsaudio_channelsstream_audio_channelsvideo_widthvideo_heightr   bitrateoptimized_versionoptimized_version_profileoptimized_version_titlesynced_versionsynced_version_profilevideo_bitratevideo_frameratevideo_dynamic_rangeaspect_ratioaudio_bitrateaudio_languageaudio_language_codesubtitle_codecsubtitle_forcedsubtitle_languagestream_bitratequality_profilestream_container_decisionstream_video_bitratestream_video_frameratestream_video_dynamic_rangestream_audio_bitratestream_audio_languagestream_audio_language_code	subtitlesstream_subtitle_decisionstream_subtitle_codecstream_subtitle_forcedstream_subtitle_languagetranscode_hw_decodingtranscode_hw_encodingr/   r4   r6   r7   )r4   r6   r7   current_sessionpre_tautulli)r   r   r	   r|   r   r   )r   rk   r*   r   	user_condtabler   r\   stream_outputr   rz  r   r   s                r   get_stream_detailszDataFactory.get_stream_detailsk  s   --/
	%+!&&(279T9T9VWIA* DM+ME,  &&uF8&<F/& 2;';E(  &&uK=&AF J	DL *+7;<S7T34+/0E+F+[${J['(045E0F,--12I-J-adS`Na)*-12C-D-UW)*.23E.F.X$x.*+045E0F,--12I-J-adS`Na)*045O0P0jTXYiTj,-&*7m]#'+H~^$ 8YY 84d;R6S80$7J2K8 9$?Z:[8 7=V8W	8
 .t4D/E8 6t<T7U8 )${*;8 +D,?8 -d?.C8 +D,?8 ,T.-A8 /5F0G8 3D9N4O8 ,T.-A8 +D,?8  -d?.C!8" .t4D/E#8$ .t4D/E%8& 3D9N4O'8( .t4D/E)8* /5F0G+8, 1$7J2K-8. .t4D/E/80 <TB`=a182 /5F0G384 9$?Z:[586 06H1I788 5d;R6S98: 248L3M;8< 4T:P5Q=8> 248L3M?8@ 3D9N4OA8B 6t<T7UC8D :4@\;]E8F 5d;R6SG8H 248L3MI8J 4T:P5QK8L 5d;R6SM8N 5d;R6SO8P :4@\;]Q8R )${*;S8T 8>X9YU8V 5d;R6SW8X 6t<T7UY8Z 8>X9Y[8\ 5d;R6S]8^ 5d;R6S_8` .t4D/Ea8b .t4D/Ec8d *4+=e8f '+7m267J2K/34D/E5@a-9o8M%J	X 1>0C0C0EF1AGFF Gs   ;Jc                 <   t        j                         }|s|r;|rd}|j                  d      d   dz   g}nd}|g}d|z  }|j                  ||      }ng }g }|D ]  }	|	d   r|	d   j                  d	      ng }
|	d
   r|	d
   j                  d	      ng }|	d   r|	d   j                  d	      ng }|	d   r|	d   j                  d	      ng }|	d   r|	d   j                  d	      ng }|	d   |	d   |	d   |	d   |	d   |	d   |	d   |	d   |	d   |	d   |	d   |	d   |	d   |	d   dg}i d|	d   d|	d   d|	d   d |	d    d!|	d!   d"|	d"   d#|	d#   d$|	d$   d%|	d%   d&|	d&   d'|	d'   d(|	d(   d)|	d)   d*|	d*   d+|	d+   d,|	d,   d-|	d-   i d.|	d.   d/|	d/   d0|	d0   d1|	d1   d2|	d2   d3|	d3   d4|	d4   d5|	d5   d6|	d6   d7|	d7   d|
d
|d|d|d|d8|	d9   d:|	d:   |	d;   |d<}|j	                  |        t        j                  |d:=      }|r|d   S g S )>Nz$session_history_metadata.guid LIKE ?r  r   %z'session_history_metadata.rating_key = ?a  SELECT session_history.section_id, session_history_metadata.id, session_history_metadata.rating_key, session_history_metadata.parent_rating_key, session_history_metadata.grandparent_rating_key, session_history_metadata.title, session_history_metadata.parent_title, session_history_metadata.grandparent_title, session_history_metadata.original_title, session_history_metadata.full_title, library_sections.section_name, session_history_metadata.media_index, session_history_metadata.parent_media_index, session_history_metadata.thumb, session_history_metadata.parent_thumb, session_history_metadata.grandparent_thumb, session_history_metadata.art, session_history_metadata.media_type, session_history_metadata.year, session_history_metadata.originally_available_at, session_history_metadata.added_at, session_history_metadata.updated_at, session_history_metadata.last_viewed_at, session_history_metadata.content_rating, session_history_metadata.summary, session_history_metadata.tagline, session_history_metadata.rating, session_history_metadata.duration, session_history_metadata.guid, session_history_metadata.directors, session_history_metadata.writers, session_history_metadata.actors, session_history_metadata.genres, session_history_metadata.studio, session_history_metadata.labels, session_history_media_info.container, session_history_media_info.bitrate, session_history_media_info.video_codec, session_history_media_info.video_resolution, session_history_media_info.video_full_resolution, session_history_media_info.video_framerate, session_history_media_info.audio_codec, session_history_media_info.audio_channels, session_history_metadata.live, session_history_metadata.channel_call_sign, session_history_metadata.channel_id, session_history_metadata.channel_identifier, session_history_metadata.channel_title, session_history_metadata.channel_thumb, session_history_metadata.channel_vcn FROM session_history_metadata JOIN library_sections ON session_history.section_id = library_sections.section_id JOIN session_history ON session_history_metadata.id = session_history.id JOIN session_history_media_info ON session_history_metadata.id = session_history_media_info.id WHERE %s ORDER BY session_history_metadata.id DESC LIMIT 1r   r  	directorsr   writersactorsgenresr   rC  rY  rG  video_resolutionr@  r`  rR  rU  channel_call_sign
channel_idchannel_identifierchannel_titlechannel_thumbchannel_vcn)rC  rY  rG  r  r@  r`  rR  rU  r  r  r  r  r  r  r/   r0   r1   r2   r6   r7   r:   r5   r9   studior4   r3   r   summarytaglineratingrB   r8   r;   r<   r=   r   r@   r?   
updated_atlast_viewed_atrA   library_namer   r   r>   )r>   
media_info)
filter_key)r   r   r   r   r   r	   filter_session_info)r   r0   rA   r   wherer  r   r\   metadata_listr   r  r  r  r  r   r  metadatafiltered_metadata_lists                     r   get_metadata_detailsz DataFactory.get_metadata_details  s   --/
>

3*S01A"|> !&?&E@  &&U&>FF <	+D8<[8I[)//4rI48Od9o++C0G26x.T(^))#.bF26x.T(^))#.bF26x.T(^))#.bF(,[(9&*9o*.}*=/34F/G489P4Q.23D.E*.}*=-12B-C045H0I)-l);156J1K,0,A,0,A*.}*= J $d<&8 $$d<&8$+T2E-F$ 1$7O2P$ ,T2E-F	$
 )$/?*@$ -d3G.H$ '^(<$ &tM':$ !$x.$  g$ %d<&8$ )$/?*@$ "4	?$ "4	?$ !$x.$  #D$4!$" V#$$  g%$& '^(<'$( ,T2E-F)$* tE{+$, 248Q3R-$. #D$4/$0 %d<&81$2 )$/?*@3$4 V5$6 $Y7$8 "79$: !&;$< !&=$> !&?$@ '^(<A$B %d<&8C$D !%V&0G$HJ   *y<	+| ")!<!<]Wc!d!)!,,Ir   c                 
   t        j                         }t        j                  |      \  }}	 d|z  }|j	                  ||      }d}|D ]  }	|	d   }	 |S # t
        $ r"}t        j                  d|z         Y d }~y d }~ww xY w)Nr]   a  SELECT SUM(CASE WHEN stopped > 0 THEN (stopped - started) ELSE 0 END) - SUM(CASE WHEN paused_counter IS NULL THEN 0 ELSE paused_counter END) AS total_duration, (CASE WHEN session_history_metadata.live = 1 THEN 'live' ELSE session_history.media_type END) AS media_type_live FROM session_history JOIN session_history_metadata ON session_history_metadata.id = session_history.id JOIN session_history_media_info ON session_history_media_info.id = session_history.id %s r  zTTautulli DataFactory :: Unable to execute database query for get_total_duration: %s.r   r[   )r   r   r   build_custom_wherer   r   r   r   )
r   rM   r   r  r  r   r\   r   r[   r   s
             r   r   zDataFactory.get_total_durationk  s    --/
 33Nt	 ""E  &&u4&8F
  	4D!"23N	4   	KKnqrrs	s   A 	B A==Bc                 R   t        j                         }d}d}t        j                         rdt        j                         z  }|r#	 dt	        |      |fz  }|j                  |      }n|S |D ]  }|d   }	 |S # t        $ r$}t        j                  d|z         |cY d }~S d }~ww xY w)NzN/Ar   zAND user_id = %s z9SELECT ip_address FROM sessions WHERE session_key = %d %szPTautulli DataFactory :: Unable to execute database query for get_session_ip: %s.r"   )	r   r   r	   r|   r   r   r   r   r   )	r   r*   r   r"   r{  r   r\   r   r   s	            r   get_session_ipzDataFactory.get_session_ip  s    --/

	&&(+g.I.I.KKI"SWZ[fWgirVss#**51
  	,Dl+J	,   "nqrrs!!"s   !A9 9	B&B!B&!B&c                    t        j                         }g }g }g }|"|j                  d       |j                  |       |"|j                  d       |j                  |       |"|j                  d       |j                  |       |"|j                  d       |j                  |       |"|j                  d       |j                  |       |"|j                  d       |j                  |       |"|j                  d       |j                  |       |"|j                  d       |j                  |       d	}|r%d
dj                  |D cg c]  }|dz   	 c}      z   }|	rd|	z   dz   }	|
dk(  r	d|d|	}n%|
dk(  r	d|d|	}nt	        j
                  d       |S 	 |j                  ||      }|S c c}w # t        $ r#}t	        j
                  d|z         Y d }~|S d }~ww xY w)Nimgr0   rJ  rM  opacity
backgroundblurfallbackr   zWHERE z AND z = ?z	ORDER BY z DESCimgurzSELECT imgur_title AS img_title, imgur_url AS img_url FROM imgur_lookup JOIN image_hash_lookup ON imgur_lookup.img_hash = image_hash_lookup.img_hash r   
cloudinaryzSELECT cloudinary_title AS img_title, cloudinary_url AS img_url FROM cloudinary_lookup JOIN image_hash_lookup ON cloudinary_lookup.img_hash = image_hash_lookup.img_hash z`Tautulli DataFactory :: Unable to execute database query for get_img_info: service not provided.r  zNTautulli DataFactory :: Unable to execute database query for get_img_info: %s.)r   r   r   r  r   r   r   r   )r   r  r0   rJ  rM  r  r  r  r  order_byservicer   img_infowhere_paramsr  r  wr   r   s                      r   get_img_infozDataFactory.get_img_info  s    --/
?&KK!-KK
#(KK)KK	*KK !-KK
#'KK
+KK!w||,NAQZ,NOOE"X-7Hg  %h0E $  %h0E KK 0 1O	n!((T(:H / -O(  	nKKhkllmm	ns   F7
"F< <	G(G##G(c                     t        j                         }d|i}|dk(  r	d}|||d}	n#|dk(  rd}||d}	nt        j                  d       y |j	                  |||		       y )
Nimg_hashr  imgur_lookup)imgur_title	imgur_urldelete_hashr  cloudinary_lookup)cloudinary_titlecloudinary_urlz`Tautulli DataFactory :: Unable to execute database query for set_img_info: service not provided.)key_dict
value_dict)r   r   r   r   upsert)
r   r  	img_titleimg_urlr  r  r   keysr|  valuess
             r   set_img_infozDataFactory.set_img_info  s~    --/
H%g"E%.#*%02F $'E*3(/1F KK 0 1%$6Br   c                    t        j                         }|st        j                         }|s|st	        j
                  d       yd}g }d}|r
d}|g}d|z  }|j                         dk(  rud|z  }|j                  ||      }	|	D ])  }
|
d	   s	t        j                  |
d	   |
d
   |
d          + t	        j                  d|z         |j                  d|z  |      }|S |j                         dk(  rd|z  }|j                  ||      }	|rt        j                  |       n |	D ]  }t        j                  |d           t	        j                  d|z         |j                  d|z  |      }|S t	        j
                  d|z         |S )NzPTautulli DataFactory :: Unable to delete hosted images: rating_key not provided.Fr   zWHERE rating_key = ?z for rating_key %sr  zSELECT imgur_title, delete_hash, fallback FROM imgur_lookup JOIN image_hash_lookup ON imgur_lookup.img_hash = image_hash_lookup.img_hash %sr  r  r  r  )r  r  r  z@Tautulli DataFactory :: Deleting Imgur info%s from the database.zVDELETE FROM imgur_lookup WHERE img_hash IN (SELECT img_hash FROM image_hash_lookup %s)r  zSELECT cloudinary_title, rating_key, fallback FROM cloudinary_lookup JOIN image_hash_lookup ON cloudinary_lookup.img_hash = image_hash_lookup.img_hash %s GROUP BY rating_key)
delete_allr0   )r0   zETautulli DataFactory :: Deleting Cloudinary info%s from the database.z[DELETE FROM cloudinary_lookup WHERE img_hash IN (SELECT img_hash FROM image_hash_lookup %s)zVTautulli DataFactory :: Unable to delete hosted images: invalid service '%s' provided.)r   r   r   get_img_servicer   errorr   r   delete_from_imgurinfoactiondelete_from_cloudinary)r   r0   r  r  r   r  r  log_msgr   results
imgur_infor\   cloudinary_infos                r   delete_img_infozDataFactory.delete_img_info  s   --/
--/G*LLkl*E<D*Z7G==?g%fhmnE ''D'9G% O
m,--*]:S8B=8Q7A*7MOO KKZ!" #&& (XZ_(`'+-F6 / ]]_,*,12E !''D'9G..*E'. ]O22ol>[\] KK_!" #&& (XZ_(`'+-F  LLq"# $ r   c                 <   d}t        |      j                         r|}n(|r&|d   dv r|d   }n|d   dv r|d   }n|d   dv r|d   }i }|rQ|xs t        j                         }|r7| j	                  |dd	|
      }|r |d   d   |d   d   |j                         d}|S )Nr   r/   )r^   showartistr  r0   )seasonalbum)r_   r`   r1   rM  poster)r0   r  r  r  r   r  r  )poster_title
poster_urlimg_service)r}   r-  r   r  r  
capitalize)r   r0   r  r  
poster_keyposter_infor  s          r   get_poster_infozDataFactory.get_poster_info,  s    
z?""$#J%)RR%l3
,'+>>%l3
,'+??%&9:
:!8!8!:G,,
6>6>5< - > 3;A;{3K19!Y1G292D2D2F#HK r   c                    t        j                         }d}t        |      j                         r|}n*|r(|d   dv r|d   }n|d   dk(  r|d   }n|d   dk(  r|d   }dddd	}|rc	 d
}|j	                  ||g      }|r|d   |d<   d}|j	                  ||g      }|r|d   |d<   d}|j	                  ||g      }	|	r|	d   |d<   |S |S # t
        $ r#}
t        j                  d|
z         Y d }
~
|S d }
~
ww xY w)Nr   r/   )r^   r  r  r  r`   r0   r  r1   r_   r2   )	tvmaze_idthemoviedb_idmusizbrainz_idz8SELECT tvmaze_id FROM tvmaze_lookup WHERE rating_key = ?r  r  z@SELECT themoviedb_id FROM themoviedb_lookup WHERE rating_key = ?r  zBSELECT musicbrainz_id FROM musicbrainz_lookup WHERE rating_key = ?musicbrainz_idzQTautulli DataFactory :: Unable to execute database query for get_lookup_info: %s.)r   r   r}   r-  select_singler   r   r   )r   r0   r  r   
lookup_keylookup_infor   tvmaze_infothemoviedb_infomusicbrainz_infor   s              r   get_lookup_infozDataFactory.get_lookup_infoI  s`   --/

z?""$#J%)VV%l3
,'83%&9:
,'94%&>?
$&(*)+- u/(66uJ<6P/:;/GK,/",":":5
|":"T"3B?3SK0/#-#;#;E#;#U #4DEU4VK 01
 {  uorssttus   &A C
 
	C6C11C6c                    |s|st        j                  d       yt        j                         }|rdt        j                  d|z         |j                  d|g      }|j                  d|g      }|j                  d|g      }t        |xs |xs |      S |rs|rp|j                         dv rEt        j                  d|z         |j                  d	|j                         z        }t        |      S t        j                  d
|z         y y y )NzNTautulli DataFactory :: Unable to delete lookup info: rating_key not provided.FzQTautulli DataFactory :: Deleting lookup info for rating_key %s from the database.z2DELETE FROM themoviedb_lookup WHERE rating_key = ?z.DELETE FROM tvmaze_lookup WHERE rating_key = ?z3DELETE FROM musicbrainz_lookup WHERE rating_key = ?)
themoviedbtvmazemusicbrainzzLTautulli DataFactory :: Deleting all lookup info for '%s' from the database.zDELETE FROM %s_lookupzTTautulli DataFactory :: Unable to delete lookup info: invalid service '%s' provided.)r   r  r   r   r  r  boolr   )	r   r0   r  r  r   result_themoviedbresult_tvmazeresult_musicbrainzr\   s	            r   delete_lookup_infozDataFactory.delete_lookup_infot  s   *LLij--/
KKk$% & * 1 12fisht u&--.^ak`lmM!+!2!23hkujv!w)P]P>PQQ}}"IIj%& '#**+BW]]_+TUF|#s&' ( $Wr   c           
         t        j                         }|rd}|j                  ||||g      }ng }i }d }d }|D ]  }|d   }|d   }	|d   }
|d   }|d   }|d   }t        |d	         |k(  r|d   }|d
   }=t        |d         |k(  r!|d
   dk(  r|d   }d}^|d
   dk(  sg|d   }d}ot        |d         |k(  s|d
   dk(  r|d   }d}|d
   dk(  s|d   }d} |r|r|	
||d	}|S y )Na	  SELECT rating_key, parent_rating_key, grandparent_rating_key, title, parent_title, grandparent_title, media_index, parent_media_index, year, media_type FROM session_history_metadata WHERE rating_key = ? OR parent_rating_key = ? OR grandparent_rating_key = ? LIMIT 1r  r4   r5   r6   r9   r:   r8   r0   r/   r1   r_   r  r`   r  r2   r  r  )	query_stringr4   r5   r6   r9   r:   r8   r/   r0   )r   r   r   r}   )r   r0   r   r   r\   r  r/   r   r4   r5   r6   r9   r:   r8   s                 r   get_search_queryzDataFactory.get_search_query  s   --/
E  &&U*jR\9]&^FF
 	*DME/L $%8 9}-K!%&:!;<D4%&*4#G}!,/
T-./:=%2#'(;#<L!)J,'72#'#7L!(JT234
B%2#'(;#<L!'J,'72#'(;#<L!)J5	*8 J%1#%1*;$/+=!#-#-	E  r   c           	         t        j                         }|dk(  rddt        |      ii}|S |dk(  s
|dk(  s|dk(  rd}nd}	 d	}|j                  ||||g
      }|d   d   }d}i }
|j                  |j                  dd      |g
      }|D ]  }i }|j                  |j                  dd      |d   g
      }|D ]  }i }|j                  |j                  dd      |d   g
      }|D ]@  }|d   r|d   nt        |d         j                         }|j                  |d|d   ii       B |dk(  r|d   nt        |d         j                         }|j                  ||d   |di        |dk(  rdnt        |d         j                         }|
j                  ||d   |di        |
}|S # t        $ r$}	t        j                  d|	z         i cY d }	~	S d }	~	ww xY w)Nr^   r   r0   r  r  r`   r4   indexzSELECT rating_key, parent_rating_key, grandparent_rating_key FROM session_history_metadata WHERE rating_key = ? OR parent_rating_key = ? OR grandparent_rating_key = ? LIMIT 1r  r2   zVTautulli DataFactory :: Unable to execute database query for get_rating_keys_list: %s.zSELECT rating_key, parent_rating_key, grandparent_rating_key, title, parent_title, grandparent_title, media_index, parent_media_index FROM session_history_metadata WHERE {0} = ? GROUP BY {1} ORDER BY {1} DESC r1   r9   r:   r5   )r0   childrenr6   )r   r   r   r   r   r   r   formatr}   r   update)r   r0   r/   r   key_list
match_typer   r\   r2   r   grandparentsr   parentsr  r   s                  r   get_rating_keys_listz DataFactory.get_rating_keys_list  sp   --/
 L#j/:;HO!Z7%:jG>S J J	E  &&U*jR\9]&^F%+AY/G%H"% ""6NPh)i)?(@ # B 	$DG&&U\\:RTg-h-12J-K,L ' NF ##**>QS_1`156I1J0K + M" OD15m1D$}-#dSZmJ\JbJbJdCOOS<l9K*L$MNO 5?'4Id/0sSWXfSgOhOnOnOp/34G/H-5!7 " ## "W,!#d;N6O2P2V2V2XC045M0N.5"7!# $+	$4  W  	KKpsttuI	s     F. .	G7GGGc                    t        j                         }t        j                         }fdi }|r|r	 ||      }|rt	        j
                  d       |rt               t               t               da|j                         D ]  \  }}	|j                  |	      }
|
st	        j                  d||	|
d          |
d   dk(  s|
d   dk(  rd}|g}t        d	   r|d
dj                  t        d	         z  z  }|j                  ||      D cg c]  }t        |d          }}|rt        d	   j                  |       n|j                  ddj                  |      z  |	g       |j                  ddj                  |      z  |	g       |
d   dk(  s|
d   dk(  rd}|g}t        d   r|d
dj                  t        d         z  z  }|j                  ||      D cg c]  }t        |d          }}|rt        d   j                  |       n|j                  ddj                  |      z  |	g       |j                  ddj                  |      z  |	g       d}|g}t        d   r|d
dj                  t        d         z  z  }|j                  ||      D cg c]  }t        |d          }}|rt        d   j                  |       nI|j                  ddj                  |      z  |	g       |j                  ddj                  |      z  |	g       | j!                  ||	|
|        yyc c}w c c}w c c}w )Nc                     i }| j                         D ]R  \  }}||v s|j                  |d   ||   d   i       d| |   v s/|j                   | |   d   ||   d                T |S )Nr0   r  )r   r  )oldnewpairsr   r   	get_pairss        r   r   z.DataFactory.update_metadata.<locals>.get_pairs  s    E		 X18LL!L/3q6,3G!HI!SV+Ys1vj/A3q6*CU%VW	X Lr   z:Tautulli DataFactory :: Updating metadata in the database.r   z<Tautulli DataFactory :: Mapping for rating_key %s -> %s (%s)r/   r  r  z@SELECT id FROM session_history WHERE grandparent_rating_key = ? r   zAND id NOT IN (%s)r  rl   zFUPDATE session_history SET grandparent_rating_key = ? WHERE id IN (%s)zOUPDATE session_history_metadata SET grandparent_rating_key = ? WHERE id IN (%s)r  r  z;SELECT id FROM session_history WHERE parent_rating_key = ? r   zAUPDATE session_history SET parent_rating_key = ? WHERE id IN (%s)zJUPDATE session_history_metadata SET parent_rating_key = ? WHERE id IN (%s)z4SELECT id FROM session_history WHERE rating_key = ? r   z:UPDATE session_history SET rating_key = ? WHERE id IN (%s)zEUPDATE session_history_media_info SET rating_key = ? WHERE id IN (%s)zUpdated metadata in database.z<Unable to update metadata in database. No changes were made.)r   r*  r   r   r   r  r)  _UPDATE_METADATA_IDSr   r  debugr  r   r}   r  r  update_metadata_details)r   old_key_listnew_key_listr/   single_updater2  r   mappingold_keynew_keyr  r   r  r   idsr   s                  @r   update_metadatazDataFactory.update_metadata  s    ++---/
	 Ll;GKKTU 25%-0U&)e($ %,MMO \V &;;GDLL!_!('8L3IK  -78L;QU];]@  !(y/0LM!%9CHHEYZvEw<x%xxE9C9J9J5RV9WX#s3t9~XX01MNUUVYZ$"))/14#?$I
 #))/14#?$I ",/8;x?UY`?`;  !(y/0GH!%9CHHEYZqEr<s%ssE9C9J9J5RV9WX#s3t9~XX01HIPPQTU$"))/14#?$I
 #))/14#?$I4  !(y/0@A!%9CHHEYZjEk<l%llE9C9J9J5RV9WX#s3t9~XX01ABII#N$"))/14#?$I
 #))/14#?$I 44WgxQTUy\V| 3Q] Y8 Y8 Ys   L2L7L<c                 b   |r|d   dk(  r|d   d|d   }n"|d   dk(  r|d   d|d   xs |d   }n|d   }dj                  |d	         }dj                  |d
         }dj                  |d         }dj                  |d         }	dj                  |d         }
t        j                  d||       t        j                         }ddj                  |      z  }|d   g}|j                  ||       ddj                  |      z  }g |d   |d   |d   |d   |d   |d   |d   ||d   |d   |d   |d   |d   |d   |d   |d   |d   |d    |d!   |d"   |d#   |d$   |d%   |d&   |d'   |d(   ||||	|d)   |
}|j                  ||       y y )*Nr/   r_   r6   z - r4   r`   r7   r   r  r  r  r  r   zRTautulli DataFactory :: Updating metadata in the database for rating_key %s -> %s.z:UPDATE session_history SET section_id = ? WHERE id IN (%s)r  r   r  a;  UPDATE session_history_metadata SET rating_key = ?, parent_rating_key = ?, grandparent_rating_key = ?, title = ?, parent_title = ?, grandparent_title = ?, original_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 = ?, tagline = ?, rating = ?, duration = ?, guid = ?, directors = ?, writers = ?, actors = ?, genres = ?, studio = ?, labels = ? WHERE id IN (%s)r0   r1   r2   r5   r9   r:   r;   r<   r=   r   r8   r@   r?   r  r  r   r  r  r  rB   rA   r  )r  r   r  r   r   r  )r   old_rating_keynew_rating_keyr  r
  r3   r  r  r  r  r   r   r   r  s                 r   r  z#DataFactory.update_metadata_details  s2   %2*23F*GRYIZ[
,'72*27*;*23C*D*eQdHe*eg
 &g.
+!67Ihhx	23GXXhx01FXXhx01FXXhx01FLLm'9 "113J'),#7E\*+DE5' *-#7E0H\* 0H5H,I 08TlKm 0W%0'/'?0AIJ]A^0-.00:0 ]+0 .66J-K0 NVV]M^0 ^,	0 /77J.K	0 NVV[_	0 _ggs^t	0
 V$0
 '//H&I0
 LTT^K_0
 bjjvaw0 -.0 199I0J0 MUU^L_0 bjjsat0 X&0 )1(<0 ?Gv>N0 QZ0 \c0 ek0 ms0 X&0 )/0D E5_ r   c                    t        j                         }g d}	 |j                  d|g g g g g |      }|d   }g }|D ]t  }|d   r&|d   j                  d	d
      j                  dd
      }	nd}	|d   |d   |d   |d   |d   |d   |d   |d   |d   |d   |d   |	|d   d}
|j                  |
       v |d   |d   ||d   d}|S # t        $ r)}t	        j
                  d|z         dddg dcY d }~S d }~ww xY w)N)znotify_log.idznotify_log.timestampznotify_log.session_keyznotify_log.rating_keyznotify_log.user_idznotify_log.userznotify_log.notifier_idznotify_log.agent_idznotify_log.agent_nameznotify_log.notify_actionznotify_log.subject_textznotify_log.body_textznotify_log.success
notify_logrI   rK   rM   rO   rQ   rR   rS   rT   zVTautulli DataFactory :: Unable to execute database query for get_notification_log: %s.r   rV   rW   rX   rY   r\   	body_textz
z<br />
r   rl   r   r*   r0   r   r.   notifier_idagent_id
agent_namenotify_actionsubject_textsuccess)rl   r   r*   r0   r   r.   r  r  r  r  r  r  r  rs   rt   rX   rV   rW   rY   rX   )r   rw   r   r   r   r   r   r   )r   rT   r   rK   r   r   notificationsr   r   r  r   r   s               r   get_notification_logz DataFactory.get_notification_log  st    ++-	 ))\29793557685717 * 9E h! 	DK  -55fhGOOPTV^_		d $[ 1"&}"5!%l!3"9o<"&}"5#J/!%l!3$($9#'#7 )"9oC KK+	. $)#9 %l 3f O  	 KKpsttu'($%   	 s   C 	C7C2,C72C7c                     t        j                         }	 t        j                  d       |j	                  d       |j	                  d       y# t
        $ r"}t        j                  d|z         Y d }~yd }~ww xY w)NzATautulli DataFactory :: Clearing notification logs from database.zDELETE FROM notify_logVACUUMTzYTautulli DataFactory :: Unable to execute database query for delete_notification_log: %s.Fr   r   r   r  r  r   r   r   r   r   s      r   delete_notification_logz#DataFactory.delete_notification_log	  sh    --/
	KK[\67h' 	KKsvwwx	   7A 	A9A44A9c                    t        j                         }g d}	 |j                  d|g g g g g |      }|d   }g }|D ]F  }|d   |d	   |d
   |d   |d   |d   |d   |d   |d   |d   |d   |d   d}	|j                  |	       H |d   |d   ||d   d}
|
S # t        $ r)}t	        j
                  d|z         dddg dcY d }~S d }~ww xY w)N)znewsletter_log.idznewsletter_log.timestampznewsletter_log.newsletter_idznewsletter_log.agent_idznewsletter_log.agent_nameznewsletter_log.notify_actionznewsletter_log.subject_textznewsletter_log.body_textznewsletter_log.start_dateznewsletter_log.end_dateznewsletter_log.uuidznewsletter_log.successnewsletter_logr  zTTautulli DataFactory :: Unable to execute database query for get_newsletter_log: %s.r   r  r\   rl   r   newsletter_idr  r  r  r  r  
start_dateend_dateuuidr  )rl   r   r&  r  r  r  r  r  r'  r(  r)  r  rs   rt   rX   r  )r   rw   r   r   r   r   r   )r   rT   r   rK   r   r   newslettersr   r   r   r   s              r   get_newsletter_logzDataFactory.get_newsletter_log	  s@    ++-	 ))5E29793557685717 * 9E Ho 	Dd $[ 1$($9#J/!%l!3$($9#'#7 $[ 1!%l!3#J/<"9oC KK	" $)#9 %l 3f C  	 KKnqrrs'($%   	 s   B 	C	 C>C	C	c                     t        j                         }	 t        j                  d       |j	                  d       |j	                  d       y# t
        $ r"}t        j                  d|z         Y d }~yd }~ww xY w)Nz?Tautulli DataFactory :: Clearing newsletter logs from database.zDELETE FROM newsletter_logr  TzWTautulli DataFactory :: Unable to execute database query for delete_newsletter_log: %s.Fr   r!  s      r   delete_newsletter_logz!DataFactory.delete_newsletter_logO	  sh    --/
	KKYZ:;h' 	KKqtuuv	r#  c                     t        j                         }|r|rd}nd}	 |j                  ||g      }ng S |D cg c]  }|d   	 c}S # t        $ r$}t	        j
                  d|z         g cY d }~S d }~ww xY wc c}w )NzLSELECT machine_id FROM session_history WHERE user_id = ? GROUP BY machine_idzSELECT * FROM (SELECT user_id, machine_id FROM session_history UNION SELECT user_id, machine_id from sessions_continued) WHERE user_id = ? GROUP BY machine_idr  zRTautulli DataFactory :: Unable to execute database query for get_user_devices: %s.r#   r   r   r   r   r   r   )r   r   history_onlyr   r   r\   r   r   s           r   get_user_deviceszDataFactory.get_user_devices[	  s    --/
..#**gY*G
 I)/0A,00  psttu	 1s"   A	 A9		A6A1+A61A6c                     t        j                         }|r	 d}|j                  ||g      }|S g S # t        $ r$}t	        j
                  d|z         g cY d }~S d }~ww xY w)Nz1SELECT * FROM recently_added WHERE rating_key = ?r  zYTautulli DataFactory :: Unable to execute database query for get_recently_added_item: %s.r/  )r   r0   r   r   r\   r   s         r   get_recently_added_itemz#DataFactory.get_recently_added_itemt	  sl    --/
K#**j\*J  I	  wz{{|	s   2 	AAAAc           	      f   t        j                         }t        j                         }|j	                  |      }d|d   i}|d   |d   |d   |d   |d   t        j                  |d         d}	 |j                  d	||
       y# t        $ r"}t        j                  d|z         Y d }~yd }~ww xY w)Nr0   r?   r   r1   r2   r/   r  )r?   r   r1   r2   r/   r  recently_added)rI   r  r  zYTautulli DataFactory :: Unable to execute database query for set_recently_added_item: %s.FT)r   r   r   r*  r  jsondumpsr  r   r   r   )r   r0   r   r2  r  r  r  r   s           r   set_recently_added_itemz#DataFactory.set_recently_added_item	  s    --/
 ++-33J?h|45&z2 ( 6'/0C'D,45M,N ( 6 $

8L+A B	)9DU[\
 	  	KKsvwwx	s   0B 	B0B++B0)NNNN)Nr  playsr   
   r   NNNNN)NNNNN)NN)r   r   r   )r   )
NNNNNNNNr   N)Nr   F)r   NN)r   N)r   r   F)r   r   r   F)r   r   NN)r   T)__name__
__module____qualname____doc__r   r   r  r  r8  r=  r~  r  r   r  r  r  r  r  r  r  r  r  r  r  r  r"  r+  r-  r1  r3  r8  r   r   r   r   r   &   s    n`	 GNNRIMtl /1 N`o%bTJlHTtl20 JNHL*.<|C*;z:)V(0=~CJRB16fAF
:x
12r   r   )r6  rx   r   r   r   r   r   r   r	   r
   r)  r  objectr   r   r   r   <module>r@     sL   $           #&% Ue s%& s%r   