New *basic* Script to insert fake plays from iPod

[Christoph]

Ok, I changed some stuff:
1. Extended the progress bar. Now you get the current song (e.g. 1/20, 2/20)
2. Changed the play date creation logic. Works now with seconds and is also shortener.

Please make a dump of your database before testing.

Christoph

Edit: Old code removed.

[brianon]

Thanks for the update Christoph.

Doesn't seem to work for me though. Complains about Line 57...

Code: Select all

formattedSyncDate = FormatDateTime(lastSyncDate,2)&" "&FormatDateTime(lastSyncDate,3)

That doesn't look right to me.

[Christoph]

Hey,
I think that's also related to our different date formats.
Maybe we have to include another replace function.
On my computer it works, but the database update doesn't work right. All changes were gone when pressing F5. I'm working on it.

Christoph

[brianon]

good man.

You can carry the torch now

[Christoph]

Ok, can reproduce the error. Now, it's time to fix it.

[Christoph]

Ok, seems to work for me.
Could you please test it?

Christoph

Edit: Old code removed.

[brianon]

Real good now...almost

All seems to be workign except if you try to add a Fake Play for a Track with no Last Played date and no play count. Try that.

[Christoph]

Ok, should be fixed now.
Sometimes I get an transaction error. I have to look into this.

Edit: Removed old code.

[nynaevelan]

This script looks like it has the potential to fix some discrepancies in my played table but before I give it a go, can you tell me if it is possible to find out which tracks are missing played info?? Is there some way to get this in a report??

Nyn

[Christoph]

Hey,
ok, here's the next version that can also run in simulation mode. Then it creates a report in the script folder.

Do (de)activate it just (un)comment line 30 in the script.

Christoph

Edit: Removed old code.

[brianon]

Nice

[Big_Berny]

If you get transaction errors this is probably caused by the SQL-query. You have to close them at the end of the script by adding "Set sqlQry = Nothing" before "SDB.Database.Commit".

And strSQL can also be included directly into "Set sqlQry = ...". Makes the script a bit smaller.

[Christoph]

Hey,
thanks for the hint. Added this.
Yes, in other languages, I would also include the queries right into the function. Didn't started optimizing the complete script yet.
I hope, the annoying sql errors are gone now.

Christoph

Code: Select all

' MediaMonkey Script
'
' NAME: Fake Plays
'
' AUTHOR: Brianon & Christoph
' Start Date : 29-03-2008
'
'---------------------------------------------------------------------------------------------------------
' Ver 1.3 (13 Mar 2008)
'---------------------------------------------------------------------------------------------------------

Dim region
Dim simu
Sub FakePlays
	Dim DB : Set DB = SDB.Database
	Dim list, itm, i
	Dim trackPlayCount, trackPlayDates, fakePlaysToCreate
	Dim strSQL, sqlQry
	Dim formattedAddedDate, dateToInsert
	Dim secondsDiff
	Dim lastSyncDate
	Dim bHasSyncDate
	
	If CDbl("1,0")=1.0 Then
		region=true
	Else
		region=false
	End If

	simu=True 'Activate this for report generation
	
	'Create any temp tables we want and remove any from previous release if required.
	DB.ExecSQL("CREATE TABLE IF NOT EXISTS tmpLastFakeSync (SongId INTEGER PRIMARY KEY, SyncDate VbDateTime)")
	'DB.ExecSQL("DROP TABLE tmpLastFakeSync")

	'Set up progress
	Set Progress = SDB.Progress

	'Get list of selected tracksfrom MediaMonkey
	Set list = SDB.CurrentSongList
	If list.Count=0 Then
	Exit Sub
	End If

	Progress.Text = "Processing tracks..."

	'Create the Report File
	If Simu=True Then
		Set f=CreateObject("Scripting.FileSystemObject")
		Set file=f.OpenTextFile(Script.ScriptPath&".log",2,true)
		file.WriteLine "FakePlay Log"
		file.WriteLine "------------"
	End If

	'Process all selected tracks
	SDB.Database.BeginTransaction
	For i=0 To list.count-1
		Progress.Text = "Processing track "&(i+1)&"/"&list.count&"..."
		Set itm = list.Item(i)

		'Get the start (BEGIN) date
		Set sqlQry = SDB.Database.OpenSQL("SELECT tmpLastFakeSync.SyncDate AS LastSyncDate FROM tmpLastFakeSync WHERE tmpLastFakeSync.SongId="&itm.ID)
		lastSyncDate = prepareDouble(sqlQry.StringByName("LastSyncDate"))


		If lastSyncDate = "" Then
			'mb1 = MsgBox("Track has no previous entry so we will use the Added date...", 0, "Error")
			bHasSyncDate = false

			'here we get the date this track was added to the library
			Set sqlQry = SDB.Database.OpenSQL("SELECT Songs.DateAdded AS TrackAddedDate FROM Songs WHERE Songs.ID="&itm.ID)
			lastSyncDate = prepareDouble(sqlQry.StringByName("TrackAddedDate"))
			formattedSyncDate = FormatDateTime(lastSyncDate,2)&" "&FormatDateTime(lastSyncDate,3)
		Else
			'mb2 = MsgBox("Last sync for this track was..." &lastSyncDate, 0, "Error")
			bHasSyncDate = True
			formattedSyncDate = FormatDateTime(lastSyncDate,2)&" "&FormatDateTime(lastSyncDate,3)
		End If

		'here we get the playcount of the current track
		trackPlayCount = itm.PlayCounter

		'here we get the number of time/date entries for this track from SQL query
		Set sqlQry = SDB.Database.OpenSQL("SELECT Count(Played.IdSong) AS CountOfPlays FROM Played WHERE Played.IdSong="&itm.ID)
		trackPlayDates = sqlQry.StringByName("CountOfPlays")

		'get the difference
		fakePlaysToCreate = trackPlayCount - trackPlayDates
		If Simu=True Then
		End If

		' only continue if we have fake plays to create
		' **TODO: could increment/decrement playcounter to match actual plays ?
		If fakePlaysToCreate > 0 Then

			'now we need to determine how many seconds we should add to our starting date each time
			'we add a fake entry
			secondsToAdd = calcSecondsToAdd(formattedSyncDate, fakePlaysToCreate)
			'MsgBox("secondstoadd:"&secondsToAdd)

			If Simu=True Then
				file.WriteLine "Insert "&fakePlaysToCreate&" fake plays: "&itm.Path&"("&itm.ID&")"
			Else

				dateToInsert = formattedSyncDate
				'now create 'X' fake plays. For each play missing, add a 'dummy'
				For x=0 To fakePlaysToCreate-1

					dateToInsert = DateAdd("s",secondsToAdd, dateToInsert)
					'mb5 = MsgBox("dateToInsert: " &dateToInsert, 0, "Error")
					DB.ExecSQL("INSERT INTO Played (IdSong, PlayDate) VALUES ("&itm.ID&",'"&Cdbl(dateToInsert)&"')")
				Next
				' update the last played date
				If itm.lastPlayed<CDbl(dateToInsert) Then
					itm.lastPlayed=CDbl(dateToInsert)
				End If

				' now update our FakeSynList or add a new entry
				If bHasSyncDate = true Then
					DB.ExecSQL("UPDATE tmpLastFakeSync SET SyncDate = '" & Now() &"' WHERE SongId = " &itm.ID)
					'mb6 = MsgBox("Updating SyncDate to " &Now(), 0, "Error")
				Else
					DB.ExecSQL("INSERT INTO tmpLastFakeSync (SongId, SyncDate) values ("& itm.ID &", '" & Now() &"')")
					'mb7 = MsgBox("Inserting SyncDate " &Now(), 0, "Error")
				End If
			End If


		ElseIf trackPlayCount>0 Then
			Set sqlQry = SDB.Database.OpenSQL("SELECT PlayDate FROM Played WHERE IDSong="&itm.ID&" ORDER BY PlayDate DESC LIMIT 0,1")
			dateToInsert = prepareDouble(sqlQry.StringByName("PlayDate"))
			formattedSyncDate = FormatDateTime(dateToInsert,2)&" "&FormatDateTime(dateToInsert,3)
			If DateDiff("s",itm.lastPlayed,formattedSyncDate)>0 Then
				If simu=True Then
					file.WriteLine "Update PlayDate: "&itm.Path
				Else
					itm.lastPlayed=formattedSyncDate
				End If
			End If
		End If
	Next
	Set Progress = Nothing
	Set sqlQry=Nothing
	SDB.Database.Commit
	If Simu=True Then
		file.Close
	Else
		list.UpdateAll
	End If
End Sub

Function calcSecondsToAdd(addedDate, playsToCreate)
	calcSecondsToAdd = DateDiff("s",addedDate, Now)/(playsToCreate+1)
End Function

Function prepareDouble(number)
	If region=True Then
		prepareDouble=Replace(number,".",",")
	Else
		prepareDouble=number
	End If
End Function

[nynaevelan]

I don't know what I am doing wrong, but nothing seems to be happening.

Nyn

[Christoph]

In the simu mode, the script creates a log file in the scripts folder.
Have you installed it correctly?
@brianon: We should think about a little install package for our script (a mmip file).

Christoph