Help with SQL string

[Skydragon980]

Well now that I look at it again I noticed it actually can't be done.

Unless someone knows a way to pull single tracks to the playlist using SQL.
Using rotations for this task is not appropriate. but you can still load a set number of commercials to a block (using "load track from category"). You can even use repeat rules. Unfortunately there is no way to make sure a commercial won't play for more than "set" times. But it's the closest you can get.

This would be a great addition to RDJ tho. Hope Marius sees this and adds this function in the future.
The ability to add tracks from SQL using events. That would be awesome.

well, so i'was doing all right and the problem isn't in my commands but in a limit of the program, BTW using rotations i can maybe put a play x times then deactvate the track, but do you know if there's a way or a script to clear the count on files? i write an example to be clear, i'm fucking tired and my english semms pulled out of a freaking nightmare

example:
commercial 1 has a count of 10 times before deactivation, how can i reset the playcount only on those files?

thanks for your precious help

[Filip83]

You can do that with an event. You can repeat the event as often as you want. It will reset the play count. But I am not sure you can actually limit the reset to a specific category. By default it will reset play count for all entries including songs!

PluginAction|Utilities|ResetPlaycounts

I think it may be possible to limit the reset to a specific category but have no idea how to do it.

[Marius]

For future reference, here are the internal track types:

Code: [Select]

        Music = 0
        Jingle = 1
        Sweeper = 2
        Voiceover = 3
        Commercial = 4
        InternetStream = 5
        Other = 6
        VDF = 7
        Podcast = 8
        Request = 9
        News = 10
        PlaylistEvent = 11
        FileByDate = 12
        NewestFromFolder = 13
        Teaser = 14
Now regarding the query, you've said that you need to play the commercials from "Paid Commercial" subcategory. Do you have tracks set as commercials in other subcategories too? I ask this because in your query you are simply querying about track type 4 without selecting a subcategory.

Ok the script finally work but now i have another problem i just tried creating an event that do those things in order:

1. Clear Playlist (hoping it clear everything in queue to be played)
2. Load Jingle Start Cluster
3. Load Rotation Commercial Cluster (the one with SQl string)
4. Load Jingle End Cluster
5. Load Normal Rotation
but it load only the 2 jingles and no commercials... where is the error?

The rotation won't add any tracks in the playlist because you already have something in it (the jingle). You could try something like this:

1. Clear playlist
2. Load sql query rotation
3. Load jingle start ON TOP
4. Load jingle end ON BOTTOM
5. Load normal rotation

The real problem in this approach is that there isn't a way to reset manually just a subcategory. The best fix for this would be to implement an event action to run sql queries, which i will do right now.
What i can do right now to help you is to write a small application which would allow you to run UPDATE sql queries, (like set play count=0) and then you can start it using events (Run application action). Let me know if this can help you.

[Marius]

I completed the program: SQLRun 1.0 (Schedule and run SQL queries in RadioDJ).

This utility allows you tu run MySQL queries on RadioDJ database using events. In order to do this, you need to place "SQLRun.exe" in the same folder as RadioDJ.exe.
To run it, you must create an event and add the "Run Application" action, select "SQLRun.exe" application and as argument enter the path to the .sql file to run.

Note: the utility can only INSERT/DELETE/UPDATE records on the database, SELECT will not work.

Now to reset the play count for a specific subcategory, you must create a sql file, let's name it "test.sql".

In that file we will add:

Code: [Select]

UPDATE songs SET count_played=0 WHERE id_subcat=X;
X=the ID of the subcategory you need to reset.

In the next version this feature is built in, but for now i can't publish it, because it needs more testing.

[attachment deleted by admin]

[Filip83]

Wow this is awesome! I will have a look at this later on.

[Skydragon980]

I completed the program: SQLRun 1.0 (Schedule and run SQL queries in RadioDJ).

This utility allows you tu run MySQL queries on RadioDJ database using events. In order to do this, you need to place "SQLRun.exe" in the same folder as RadioDJ.exe.
To run it, you must create an event and add the "Run Application" action, select "SQLRun.exe" application and as argument enter the path to the .sql file to run.

Note: the utility can only INSERT/DELETE/UPDATE records on the database, SELECT will not work.

Now to reset the play count for a specific subcategory, you must create a sql file, let's name it "test.sql".

In that file we will add:

Code: [Select]

UPDATE songs SET count_played=0 WHERE id_subcat=X;
X=the ID of the subcategory you need to reset.

In the next version this feature is built in, but for now i can't publish it, because it needs more testing.

thank you marius you are great, and thanks to the other friends too

[Skydragon980]

For future reference, here are the internal track types:

Code: [Select]

        Music = 0
        Jingle = 1
        Sweeper = 2
        Voiceover = 3
        Commercial = 4
        InternetStream = 5
        Other = 6
        VDF = 7
        Podcast = 8
        Request = 9
        News = 10
        PlaylistEvent = 11
        FileByDate = 12
        NewestFromFolder = 13
        Teaser = 14
Now regarding the query, you've said that you need to play the commercials from "Paid Commercial" subcategory. Do you have tracks set as commercials in other subcategories too? I ask this because in your query you are simply querying about track type 4 without selecting a subcategory.

The rotation won't add any tracks in the playlist because you already have something in it (the jingle). You could try something like this:

1. Clear playlist
2. Load sql query rotation
3. Load jingle start ON TOP
4. Load jingle end ON BOTTOM
5. Load normal rotation

The real problem in this approach is that there isn't a way to reset manually just a subcategory. The best fix for this would be to implement an event action to run sql queries, which i will do right now.
What i can do right now to help you is to write a small application which would allow you to run UPDATE sql queries, (like set play count=0) and then you can start it using events (Run application action). Let me know if this can help you.

i use the commercial category only for paid commercial, free commercials aren't "good looked" for SIAE (the italian organization that grant me the streaming license)

[Skydragon980]

EDIT: FINALLY IT WORK! you need to add a pause between the commercial rotation and the top and bottom jingles

Code: [Select]

1. Clear playlist
2. Load sql query rotation
3. Pause for 1 second
4. Load jingle start ON TOP
5. Load jingle end ON BOTTOM
6. Load normal rotation


[Filip83]

I am glad you were able to make it all work! Could you do a recap of all the steps you used to made it work for other users?

[Skydragon980]

Hello, here are the solution for my problem, thanks to everyone of you that helped me 



create a new SQL rotation with this SQL string:

Code: [Select]

#INSERT TITLE HERE
SELECT `songs`.`ID`, `songs`.`artist` FROM `songs`
LEFT JOIN `queuelist` ON ( `songs`.`artist` = `queuelist`.`artist`)
WHERE `song_type`=4
AND `enabled`=1
AND `count_played`<10
AND  `queuelist`.`artist` IS NULL
ORDER BY `date_played` ASC
LIMIT 5;

song type reference:

Code: [Select]

        Music = 0
        Jingle = 1
        Sweeper = 2
        Voiceover = 3
        Commercial = 4
        InternetStream = 5
        Other = 6
        VDF = 7
        Podcast = 8
        Request = 9
        News = 10
        PlaylistEvent = 11
        FileByDate = 12
        NewestFromFolder = 13
        Teaser = 14

then create an event :

Code: [Select]

1. Clear Playlist!
2. Load Rotation "SQL ROTATION"
3. Seconds to wait 1
4. Load Jingle on Top
5. Load Jingle on Bottom
6. Load standard Rotation

it work for me