Can't import tv show activity from playback Reporting plugin

[Craxter]

Describe the bug
I have a clean install of Jellystat and tried to get data from Playback Reporting, but while Movie data is retrieved perfectly, the tv show activity isn't properly recognized, showing in Activity page as "Show: SnullEnull - Show - sXXeXX - Episode title" and not appearing in any other way.

Environment Details (please complete the following information):

• OS: Linux - Docker
• Browser: N/A
• Jellystat Version: 1.1.0
• Jellyfin Version: 10.9.11

To Reproduce
Steps to reproduce the behavior:

1. Clean install using docker.
2. Go to Settings as admin.
3. Click on Task > Complete Sync with Jellyfin.
4. Click on Task > Import Playback Reporting Plugin Data.
5. Go to activity and see the error.

Expected behavior
It shouldn't show SnullEnull but properly retrieve episode info.

Screenshots

Task Logs

Syncing...

Determining query constraints.

Query built. Executing.

Inserting 1272 Rows.

1272 Rows have been inserted.

Running process to format data to be inserted into the Activity Table

Process complete. Data has been imported.

Any imported data has been processed.

Playback Reporting Plugin Sync Complete

Container Logs
N/A

Additional context
Add any other context about the problem here.

[AIndoria]

Did the same thing today after getting the container(s) stopped for about a month and starting again - first it wouldn't sync at all, last activity it showed was September 2024.

Then when I did a clean docker install, it started adding SnullEnull to my activity. Logs show no errors.

@CyferShepard

[gearoidkeane]

Same issue is occurring for me on a clean docker install performed today

No errors in logs that I am aware of.

[gearoidkeane]

I'm not a coder but I like a dig into databases.

So I found that the "ItemId" in the jf_playback_reporting_plugin_data table relates to the "Id" in jf_library_episodes table.

During the Playback Reporting Data Import, the "Id" field in jf_library_episodes table is concatenated with the "Id" field in jf_library_seasons table and inserted into the jf_playback_activity table as "EpisodeID".

E.G. the field is
a162e2d7af5637709ae7e6f8362717883beba3afbd3690b47468cb1afe83f173
Instead of just
a162e2d7af5637709ae7e6f836271788

Also the the "NowPlayingItemId" in the jf_playback_activity table is populated with the "EpisodeID" but I think this should be the appropriate "Id" from the jf_library_items table.

Anyway, I did a bit of database hacking after running the import from Playback Reporting, to get TV Shows to show in correctly statistics.

-- Use only the first 32 characters for the EpisodeID
UPDATE public.jf_playback_activity
   SET "EpisodeId" = LEFT("EpisodeId", 32)
WHERE "EpisodeId" IS NOT NULL
  AND "imported" = 'True'

-- Temp Table to relate the Library Item Id to the Episode Id
CREATE TEMP TABLE tbl AS (
SELECT pa."NowPlayingItemId",li."Id"
  FROM public.jf_playback_activity pa
     JOIN public.jf_library_episodes le
       ON le."EpisodeId" = pa."EpisodeId"
     JOIN public.jf_library_items li
       ON le."SeriesName" = li."Name"
WHERE li."Type" <> 'Movie'
)

-- Update the Playback Activity Table to replace the NowPlayingItemId with the LibraryItem Id
UPDATE public.jf_playback_activity t1
SET "NowPlayingItemId" = t2."Id"
FROM tbl t2
WHERE t1."NowPlayingItemId" = t2."NowPlayingItemId" 

-- Remove temp table.
DROP TABLE tbl;

Seems to have worked for me but remember I could be all wrong but in the screenshot below there was nothing prior to the Jellystat install for TV Shows (yellow line) and now its going back 2 years using all my Playback Reporting Data.

Thanks @CyferShepard for all your work on this, its amazing!

[Uncle-Tio]

I had the same problem, your solution with the query worked perfectly, thank you!

[itsZeebo]

You're a wizard @gearoidkeane fixed my issue as well.

I can try to open a PR to fix this but I since I don't really maintain this library or know it very well yet (only started to use it yesterday) I think it's best if one of the main guys here decide how they'd like to tackle this.

I don't mind helping with the implementation once a course of action has been decided..

[nickdollimount]

-- Use only the first 32 characters for the EpisodeID
UPDATE public.jf_playback_activity
   SET "EpisodeId" = LEFT("EpisodeId", 32)
WHERE "EpisodeId" IS NOT NULL
  AND "imported" = 'True'

-- Temp Table to relate the Library Item Id to the Episode Id
CREATE TEMP TABLE tbl AS (
SELECT pa."NowPlayingItemId",li."Id"
  FROM public.jf_playback_activity pa
     JOIN public.jf_library_episodes le
       ON le."EpisodeId" = pa."EpisodeId"
     JOIN public.jf_library_items li
       ON le."SeriesName" = li."Name"
WHERE li."Type" <> 'Movie'
)

-- Update the Playback Activity Table to replace the NowPlayingItemId with the LibraryItem Id
UPDATE public.jf_playback_activity t1
SET "NowPlayingItemId" = t2."Id"
FROM tbl t2
WHERE t1."NowPlayingItemId" = t2."NowPlayingItemId" 

-- Remove temp table.
DROP TABLE tbl;

This worked for me! I did have to run each section individually though, adding the semicolon at the end. For some reason running the entire chunk didn't work. Thanks for this!

[dfrazao]

Hey, I also had the same issue, and running the query worked almost perfectly!
For some reason, one user now has a negative watch time, but looking through their activty list doesn't show anything out of the ordinary, at least to me.
Could someone help me out?

---

small edit: there was one entry with no playback time, deleting that fixed the negative watch time!