CCLI Song Search & Reporting Script

[vassbo]

Describe the feature
Log the song usage to a data file that can be requested.
More details here: #879 (comment)

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?: #572 (comment)

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 #776 maybe integrated after all ?

[vassbo]

@gwonamfromkoradai Did you get a chance to work on the song search & importing?

[gwonamfromkoradai]

Hey, I was working on it but have been out of commission for a bit. I'm going to look at the code I have and see what might work.

[vassbo]

Okay no worries, take your time!

[gwonamfromkoradai]

FWIW if anyone gets to this before me, there will be no offence taken at all lol.

I think I will upload my garbage code that I have and then keep working on it, but that way if other people can integrate it better they can.

[gwonamfromkoradai]

@yurividal I've been working on my old code but I think your code is maybe a better starting place. I was using selenium and navigating the site, but I wonder if utilizing your program's ability to grab the cookie and request verification token may be a better avenue to go down in order to make it cleaner.

Time for some dumb questions from me. Does the verification token = access token? Does your code extract the subscription key?

If that wouldn't work here, another option is for me to adapt my old garbage code into a chrome extension or something, basically use the CCLI website and have ability to import directly from that. Not as clean as what you've made for reporting, however.

[yurividal]

I dont think i grab the subscription key. What is that used for? I don't think i saw any refference to subscription keys when i was inspecting the traffic

[yurividal]

Ok, from the documentation here the subscription key is a key provided by CCLI when you get your CCLI Partner account. I don't think we have that, and the whole point of this discussion is to be able to use their public API without this partner account, which aparently costs over $1K/year

If we want to implement songselect search, we will have to utilize the auth and api calls implemented on https://songselect.ccli.com/

[klecu]

Could you do like OpenSongApp and add a webview that intercepts .chopro and .txt downloads?

[yurividal]

The more i look into the code on their website, the more complicated and protected it looks.
I think the easiest wya to implement the songselect import would be to:

• Open a browser window when user clicks import SongSelect
• Let the user login, and search for the song
• User clicks the download button
• Freeshow waits and expects a txt file
• Once txt file is found, freeshow parses it

[gwonamfromkoradai]

Yeah that’s exactly what I was thinking. That’s why I’m wondering if a chrome extension might work.

I’m looking through the openlp code as well to see how they handle it.

I didn’t realize there was a price to access the API, I thought they were just not given it out except to the elect few. That makes even less sense. I get why pro presenter doesn’t want apps like this to have import features, but I honestly don’t get why CCLI would want to prevent it. Seems shady.

[yurividal]

Everything is shady about CCLI... Unfortunately

[gwonamfromkoradai]

I may have something, got some inspiration from open lp’s design. I did a rough program, I want to try to combine it with the importer

[gwonamfromkoradai]

Ok, here is what I've got. Not much but maybe a start?

https://github.com/gwonamfromkoradai/SongSelectSave/tree/main

It's a simple python script that will open a browser window to CCLI, allow you to log in, then when you search, you can select copy and you can save it as a text file.

Technically speaking., you can use this to import from CCLI to FreeShow by then using the text import feature and pointing to your "song" directory. However, I see this more as a first step to adapt a converter - I don't think that it would take much to adapt this directly within the existing import function. I was working on adapting this to a .ts that could be integrated but I didn't get too far yet.

I also would guess that this could be easily integrated with what @yurividal developed for the reporting software so that users will only need to sign in once. In fact, it probably would make this code more easily integrated into the program.

Let me know thoughts. I am happy to keep chipping away but wanted to share what I got so far. Sorry it's taken so long for not much lol, I've tried to integrate other features but ended up stripping it down to the bare features to (I think) provide a framework to integrate Song Select search into FreeShow.

Lastly, I tried but couldn't for the life of me accomplish this without a web interface pop-up. Perhaps smarter people than me might be able, but I did notice that OpenLP has a similar set-up to this (They utilize the download function, but in my humble opinion the clipboard grabber is cleaner because of the way Song Select is laid out with the copy function). The main thing I couldn't get working was the search function, but again, maybe with @yurividal's success utilizing the reporting search, a similar thing might be adapted.

[vassbo]

Nice! Looks like the repo is private.

The best would be to make this into a node module/npm package, to be able to easily integrate it into the program.

[gwonamfromkoradai]

Nice! Looks like the repo is private.

The best would be to make this into a node module/npm package, to be able to easily integrate it into the program.

Whoops! Switched to public

[gwonamfromkoradai]

Ok, here is a node module version:

https://github.com/gwonamfromkoradai/SSSave/releases/tag/SSSave

I think my next step will be adding file conversion to make the files save as .show

After that, I would like to try to clean up the keys. Then at the end, I will tinker with the UI.

[gwonamfromkoradai]

Hello all!

Sorry for the very long delay. I think I finally have a beta program that could effectively work as a baseline to integrate SongSelect search into Freeshow!

Here is the code. I am going to work on making a node module version which would (hopefully) make it more easily integrated into Freeshow.

Basically, the first time you use it, it will ask for your CCLI login. Then it will launch a GUI search function, where you can search, select a song, and save it as a txt file.

One thing that I am happy about is that it is able to function entirely within the GUI on the user-level, using a web backend. The downside of this approach, of course, is that it will break if CCLI changes their site element labels. But it wouldn't be a huge deal to update them in that case.

Thanks to the work of Yurividal, I was also able to integrate what he developed into this allowing the user to be able to remain logged in using a saved cookie/token. You only will need to login once.

The code is clunky and I know it will need tweaking. For example, I don't know if changing one's password changes their access token? Also, I am wondering about changing it so that the username/password isn't saved in a file, since they will have the cookie/token saved anyways. It's also not the speediest thing ever made, I would imagine there are ways that it could be improved. Nonetheless, I have to admit I am happy to see a proof of concept of a fully GUI song search/save with songselect. I am also guessing that there will be ways that integration will make it work smoother.

This lacks the converter function. I was working on it, but I gave up because I was thinking that something could probably be made using the existing text file import function? IE on the front end it is just importing song select, but on the back end it is running this type of program, saving as texts, then running the text to .show converter? Or maybe there was an easier way.