The problem:
You have a track that has a Play Count of 10. However in the Played table it shows only 5 entries. So you played the track 5 times in MM and 5 times on your iPod/MP3 player.
What happens is that this can screw up your charts. especially noticed in the great PlayHistory/Stat node script.
Solution: To add fake plays for missing Played entries.
The script:
Determines how many plays are missing. Adds entries into the Played table to make up the difference. Has super simple/basic logic for the dates to give to each entry.
Example: Track A has a PlayCounter of 2 and only 1 entry in the Played table. The track was added on the 1st of March. Todays date is 10th March. It will add a fake play for the 5th of March.
So while the script can NEVER be accurate (no way of actually telling when a track was played on your iPod) it trys to guess.
Its a very quick and dirty and nasty.
One thing it does do if that it creates its own little table and once you add a fake plays for a track it stores that date. So the next time you try to add fake plays for a track it will use THAT date and not the Track Added date. Should keep things more accurate.
Here is the script. Would be great if someone could make it better/more accurate/faster etc...
Code: Select all
' MediaMonkey Script
'
' NAME: Fake Plays
'
' AUTHOR: Brianon
' Start Date : 29-03-2008
'
' ------------------------------------------------------------------------------------------------------------
' Ver 1.10 (11 Mar 2008)
'-------------------------------------------------------------------------------------------------------------
Sub FakePlays
Dim DB : Set DB = SDB.Database
Dim list, itm, i
Dim trackPlayCount, trackPlayDates, fakePlaysToCreate
Dim strSQL, sqlQry
Dim formattedAddedDate, dateToInsert
Dim daysDiff
Dim lastSyncDate
Dim bHasSyncDate
'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 tracks from MediaMonkey
Set list = SDB.CurrentSongList
If list.Count=0 Then
Exit Sub
End If
Progress.Text = "Processing tracks..."
' Process all selected tracks
For i=0 To list.count-1
Set itm = list.Item(i)
' Get the start (BEGIN) date
strSQL = "select tmpLastFakeSync.SyncDate AS LastSyncDate from tmpLastFakeSync where tmpLastFakeSync.SongId = " & itm.ID
Set sqlQry = SDB.Database.OpenSQL(strSQL)
lastSyncDate = 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
strSQL = "select Songs.DateAdded AS TrackAddedDate from Songs where Songs.ID = " & itm.ID
Set sqlQry = SDB.Database.OpenSQL(strSQL)
lastSyncDate = sqlQry.StringByName("TrackAddedDate")
formattedSyncDate = FormatDateTime(lastSyncDate,2)
Else
'mb2 = MsgBox("Last sync for this track was..." &lastSyncDate, 0, "Error")
bHasSyncDate = true
formattedSyncDate = FormatDateTime(lastSyncDate,2)
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
strSQL = "select Count(Played.IdSong) AS CountOfPlays from Played where Played.IdSong = " & itm.ID
Set sqlQry = SDB.Database.OpenSQL(strSQL)
trackPlayDates = sqlQry.StringByName("CountOfPlays")
' get the difference
fakePlaysToCreate = trackPlayCount - trackPlayDates
' 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 days we should add to our starting date each time
' we add a fake entry
daysToAdd = calcDaysToSub(formattedSyncDate, fakePlaysToCreate)
'mb3 = MsgBox("Fake plays required for this track are..." &fakePlaysToCreate, 0, "Error")
'mb4 = MsgBox("Todays Date is " &Now(), 0, "Error")
dateToInsert = Now()
' now create 'X' fake plays. For each play missing, add a 'dummy'
For x=0 To fakePlaysToCreate-1
dateToInsert = DateAdd("d",daysToAdd, dateToInsert)
'mb5 = MsgBox("dateToInsert: " &dateToInsert, 0, "Error")
DB.ExecSQL("insert into Played (IdSong, PlayDate) values (" &itm.ID & ",'" &Cdbl(dateToInsert) & "')")
Next
' 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
Next
Set Progress = nothing
End Sub
Function calcDaysToSub(addedDate, playsToCreate)
Dim modDiff
Dim tmp
' determine how many days since this track was added
daysDiff = DateDiff("d",addedDate, Date)
'mb8 = MsgBox("calDaysToAdd() --> Days Difference is : " &daysDiff, 0, "Error")
' now get the days we must add for each fake play
If daysDiff = 0 Then
' added today so just add all fake plays for today
daysToAdd = 0
'mb9 = MsgBox("1: calDaysToAdd() --> daysToAdds : " &daysToAdd, 0, "Error")
ElseIf daysDiff = playsToCreate Then
daysToAdd = 1
'mb10 = MsgBox("2: calDaysToAdd() --> daysToAdds : " &daysToAdd, 0, "Error")
ElseIf daysDiff > playsToCreate Then
daysToAdd = daysDiff / (playsToCreate+1)
'mb11 = MsgBox("3.1: calDaysToAdd() --> daysToAdds : " &daysToAdd, 0, "Error")
ElseIf daysDiff < daysToAdd Then
' ok this isn't very accurate but for most cases it won't be too far off the mark
' and should suffice for what I want.
daysToAdd = 1
'mb12 = MsgBox("4: calDaysToAdd() --> daysToAdds : " &daysToAdd, 0, "Error")
Else
daysToAdd = 1
mb13 = MsgBox("5: We missed some scenario. Defaulting to 1", 0, "Error")
End If
' make the number a negative
daysToAdd = daysToAdd - (DaysToAdd*2)
calcDaysToSub = daysToAdd
'mb114 = MsgBox("X: calDaysToAdd() --> daysToAdds : " &daysToAdd, 0, "Error")
End Function