CCLI Song Search & Reporting Script

[vassbo]

Describe the feature Log the song usage to a data file that can be requested. More details here: https://github.com/ChurchApps/FreeShow/issues/879#issuecomment-2406928189

Additional context Add a more automated way of uploading data to CCLI.

[vassbo]

Format suggestion:

[{
  "name": "Lord for the years",
  "time": 1728637038559,
  "metadata": { "author": "Timothy Dudley Smith", "CCLI":  3274952 },
}, ...]

Do we need more info in the log @yurividal?

[yurividal]

No, that should be it. The CLLI number is indeed all we need.

[vassbo]

Here you can try it out: https://github.com/ChurchApps/FreeShow/releases/tag/v1.2.9 To export, go to settings>"Other">"Export usage log".

Let me know if you have updates on the uploader script.

[yurividal]

Damn @vassbo you are fast!

I'm boarding a flight now, and won't be able to test it for at least another 15 hours. I'll report back to you as soon as I am able to have a look and start working on the uploader script.

[yurividal]

@vassbo I managed to reverse engineer CCLIs API and found a way to programatically report a song. I put together a quick code in python as proof of concept. I tested with a couple of songs, and they all show in my CCLI history as reported. The API is a bit more tricky on the AUTH part, since they include captchas etc...

Perhaps, we can have a webview login page in freeshow, which can then grab the cookies and use them for submittting the reports.

I don't know if can post it here, or if you would rather have this sent by email. just let me know

[vassbo]

Nice! For me it's the same.

I think @gwonamfromkoradai was working on a login system?: https://github.com/ChurchApps/FreeShow/issues/572#issuecomment-2399873916

Let's see if we can merge these in a way, both song importing & usage reporting!

[gwonamfromkoradai]

Yes I am! Working out a few kinks though. The login is fine but I’m just trying to make the search/save function work

[gwonamfromkoradai]

I like the idea of a web view page. I had an annoying time dealing with the two cookie pop ups (who does two??) alone with a popup that says unsupported when I run selenium headless mode.

[yurividal]

Great! @gwonamfromkoradai Are you able to obtain these cookies from your login process?

ARRAffinity
ARRAffinitySameSite
.AspNetCore.Session
.AspNetCore.Antiforgery
CCLI_AUTH
CCLI_JWT_AUTH

and also RequestVerificationToken

If we have these values, my script is already able to report songs. Currently, i open a browser session, login, and get these values from developer options.

[yurividal]

@vassbo @gwonamfromkoradai

Here is my code. Its not pretty, but it works as a proof of concept.

It automatically spawns a chrome page, and tries to log in. Once logged in, it will need to submit a single CCLI song, in order to capture the RequestverificationToken and the Cookies. Once we have that info, it can then use the API to bulk report all the rest of the list.

Works as a proof of concept, and might give us some ideas of how it can be implemented in freeshow.

[gwonamfromkoradai]

Awesome. That’s similar to what I was doing but your APk integration makes it a lot smoother.

When I’m back at my computer, I’ll try integrating the search/add function into this, that way we can have one going (unless that’s not a good idea?). I’m off for a couple days but should be able to write something up Tuesday or Wednesday.

I think that this code has some missing pieces to help simplify what my code was doing. I might have the first version just save them as txt files for now, then once it is working, make it work as a plugin.

Also if anyone else solves the search stuff before me I won’t be offended lol. Otherwise, I’ll work on it in a few days.

As a side note, if anyone wanted a workaround for now on Mac, I built an Automator which imports to freeshow using the free version of ProPresenter lol. Not a permanent solution and the definition of over-engineered but it works. But tbh at that point you might as well use the web interface / copy and paste.

[yurividal]

I found a way to ge tthe verification token without having to manually submit one song. I've updated the script

[vassbo]

Looks good, thank you for all the help! 🎉

[yurividal]

@vassbo i've just submited 101 songs on a single report, and it worked perfectly. I was concerned that the API might have some sort of restriction on how many you can do at a time. Now we know that at least 100 songs can be reported in a single post.

I've also coded a "delete" script, that will delete any reported songs in the last 3 monts. (Needed a way to retract all the 100 test songs i reported. hahaha)

[Marsipulami]

Love it that you all are still working on SongSelect features. How is this proces going and is it correct, that if i am reading this correct, that https://github.com/ChurchApps/FreeShow/issues/776 maybe integrated after all ?