by some1 » Tue Nov 06, 2007 1:32 pm
Ive started it, but its giving me a error about 's'
Code: Select all
' Complete Albums
' Version 1.0
' A script to create a "complete album" node below the album node on the tree.
' This script creates a node in the tree, below the album node, called "Albums (Complete)".
' The node displays only albums where the number of songs is > 1 and the total number of songs
' for the album is greater than or equal to the highest existing track number. This does
' create the occasional false positive, and it doesn't include albums with a single track,
' but for the most part, it displays all complete albums in the format:
' Album Artist - [year] Album Name
' and orders them by that caption. It also separates them into subdirectories for each letter
' to reduce clutter.
'
' It also does some stuff that allows you to include a single song on multiple albums. For example,
' let's say you have an EP with the album version of a song you also have on album, you can keep
' a single copy of the song, rather than a copy for each place it shows up. To do this, you add
' a line to the comments with the format:
' LP:Other Album Name:01
' where the track number is a 2 digit number and the note occurs on a single line by itself. This
' tells the script that this song also should show up as the first track on Other Album Name.
'
' You can include multiple notes like this and the track will show up on each album listed. Note that
' the track still appears under the album that is referenced in the album tag, so the notes are
' only for *additional* albums. Also, if two artists have the same album name, I think it will
' fail. I didn't test it, because it doesn't happen often enough for me to care, but if you decide
' to go with a few different "Greatest Hits" albums, you may have problems.
'
' The root caption can be changed by editing the RootNodeCaption constant.
' The album caption can be changed by editing the albumCaption function.
'
' This script does not update the DB or files. Use at your own risk. Do not taunt this script.
'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
' Global Variables and Declarations
'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
Option Explicit
' %%% The caption for the root node.
Const RootNodeCaption = "Albums (Complete)"
' %%% The function that builds the caption for the album node.
Function albumCaption(artist, year, album)
Dim caption
If Len(year) < 4 Then
year = year & "-"
End If
If Len(year) < 4 Then
year = ""
Else
year = "["&year&"] "
End If
caption = artist&" - "&year&""&album
albumCaption = caption
End Function
' %%% The code the script uses to check in comments for tracks that appear on more than one album.
Const LPCode = "LP"
'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
' The Meat. Don't change anything under here.
'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
Sub SortArrayStr(aTempArray)
Dim iTemp, jTemp, strTemp
For iTemp = 0 To UBound(aTempArray)
For jTemp = 0 To iTemp
If strComp(aTempArray(jTemp), aTempArray(iTemp)) > 0 Then
'Swap the array positions
strTemp = aTempArray(jTemp)
aTempArray(jTemp) = aTempArray(iTemp)
aTempArray(iTemp) = strTemp
End If
Next
Next
End Sub
Sub SortArray(aTempArray)
Dim iTemp, jTemp, strTemp
For iTemp = 0 To UBound(aTempArray)
For jTemp = 0 To iTemp
If aTempArray(jTemp) > aTempArray(iTemp) Then
'Swap the array positions
strTemp = aTempArray(jTemp)
aTempArray(jTemp) = aTempArray(iTemp)
aTempArray(iTemp) = strTemp
End If
Next
Next
End Sub
Sub BuildArray(CompList, aTempArray)
Dim nCount, strKey
nCount = 0
'-- Redim the array to the number of keys we need
Redim aTempArray(CompList.Count - 1)
'-- Load the array
For Each strKey In CompList.Keys
'-- Set the array element to the key
aTempArray(nCount) = strKey
'-- Increment the count
nCount = nCount + 1
Next
End Sub
Sub FillAlbumLeaf(Node)
Dim sql, songlist, SplitCustomData, name, albumId
SplitCustomData = Split(Node.CustomData,"@@")
name = SplitCustomData(0)
albumId = SplitCustomData(1)
sql = "select Songs.Id, Songs.SongOrder from Songs where songs.idAlbum = "&albumId
Dim Iter, id, track, song, songTable
Set songTable = CreateObject("Scripting.Dictionary")
Set Iter = SDB.Database.OpenSQL(sql)
Do Until Iter.EOF
id = Iter.StringByIndex(0)
track = Iter.StringByIndex(1) + 1
If Not songTable.Exists(track) Then
songTable.add track, id
Else
SDB.MessageBox "Duplicate track numbers exist for this album. Will not show duplciate tracks.", mtWarning, Array(mbOk)
End If
Iter.Next
Loop
Dim lengthSql, posSql, trackNumSql, sql2
name = Replace(name, "'", "''")
lengthSql = "Len('"&name&"')"
posSql = "InStr(Memos.MemoText,'"&name&"')"
trackNumSql = "Mid(Memos.MemoText, "&posSql&"+"&lengthSql&"+1,2)"
sql2 = "select Songs.Id, "&trackNumSql&" from Songs, Memos " &_
"where Songs.Id = Memos.IdSong and MemoText like '%"&LPCode&":"&name&":%' "
Set Iter = SDB.Database.OpenSQL(sql2)
Do Until Iter.EOF
id = Iter.StringByIndex(0)
track = Iter.StringByIndex(1) + 0
If Not songTable.Exists(track) Then
songTable.add track, id
Else
SDB.MessageBox "Duplicate track numbers exist for this album. Will not show duplciate tracks.", mtWarning, Array(mbOk)
End If
Iter.Next
Loop
Dim aTemp, iTemp, trackNum, songId
Call BuildArray(songTable, aTemp)
Call SortArray(aTemp)
Dim Tracks
Set Tracks = SDB.MainTracksWindow
For iTemp = 0 To UBound(aTemp)
songId = songTable.Item(aTemp(iTemp))
Tracks.AddTracksFromQuery("AND Songs.ID = "&songId)
Iter.Next
Next
Tracks.FinishAdding
End Sub
Sub FillRootCustomNode(Node)
Node.hasChildren = False
Dim sql, fatsql, Iter, subroot, albumId, year, id
fatsql = "select artists.id from songs, albums, artists where songs.IDAlbum = albums.id and artists.id = albums.artist group by artists.id, songs.year, albums.album, albums.id having count(*) >= (max(songs.tracknumber)+1) and count(*) > 1 and albums.album <> '' and max(songs.tracknumber) > -1"
Set Iter = SDB.Database.OpenSQL(fatsql)
Dim idTable
Set idTable= CreateObject("Scripting.Dictionary")
Do Until Iter.EOF
id = Iter.StringByIndex(0)
If Not idTable.Exists(id) Then
idTable.add id, id
End If
Iter.Next
Loop
Dim s, strKey
For Each strKey In idTable.Keys
s = s &"," & strKey
Next
s = Mid(s,2)
sql = "select left(artist,1) from artists where artists.id in ("&s&") group by left(artist,1)"
Set Iter = SDB.Database.OpenSQL(sql)
Do Until Iter.EOF
Set subRoot = SDB.MainTree.createNode
subRoot.Caption = Iter.StringByIndex(0)
subRoot.IconIndex = 16
subRoot.UseScript = Script.ScriptPath
subRoot.onFillChildren = "FillSubRootLetterNode"
subRoot.customData = Iter.StringByIndex(0)
subRoot.sortCriteria = 2
subRoot.sortGroup = 2
SDB.MainTree.AddNode Node, subRoot, 3
subRoot.hasChildren = True
Iter.Next
Loop
End Sub
Sub FillSubRootLetterNode(Node)
Node.hasChildren = False
Dim sql, Iter, subroot, albumId, year, letter
letter = Left(Node.customData,1)
sql = "select album, albums.id, artist, max(songs.year) from songs, albums, artists where left(artists.artist,1) = '"&letter&"' and songs.IDAlbum = albums.id and artists.id = albums.idartist group by artist, songs.year, album, albums.id having count(*) >= (max(songs.songorder)+1) and count(*) > 1 and album <> '' and max(songs.songorder) > -1"
Dim captionTable, caption
Set captionTable= CreateObject("Scripting.Dictionary")
Set Iter = SDB.Database.OpenSQL(sql)
Do Until Iter.EOF
caption = albumCaption(Iter.StringByIndex(2),Iter.StringByIndex(3),Iter.StringByIndex(0))
If Not captionTable.Exists(caption) Then
captionTable.add caption, Iter.StringByIndex(0)&"@@"&Iter.StringByIndex(1)
End If
Iter.Next
Loop
sql = "select Memos.MemoText from Memos where MemoText like '%"&LPCode&":%'"
Dim lpTable, bpos, s, epos, name
Set lpTable= CreateObject("Scripting.Dictionary")
Set Iter = SDB.Database.OpenSQL(sql)
Do Until Iter.EOF
s = Iter.StringByIndex(0)
Do Until Len(s) = 0 Or InStr(s, LPCode&":") = 0
bpos = InStr(s, LPCode&":")
If bpos <> 1 Then
s = Mid(s, bpos)
End If
s = Mid(s, Len(LPCode)+2)
epos = InStr(s, vbcrlf)
If epos = 0 Then
epos = Len(s) + 1
End If
name = Left(s, epos-1)
If Left(Right(name,3),1) = ":" Or Left(Right(name,3),1) = "." Then
name = Mid(name,1,Len(name)-3)
End If
If Not lpTable.Exists(name) Then
lpTable.add name, name
End If
s = Mid(s,epos)
Loop
Iter.Next
Loop
For Each name In lpTable.Keys
sql = "select album, albums.id, artist, max(songs.year) from songs, albums, artists where left(artists.artist,1) = '"&letter&"' and songs.IDAlbum = albums.id and artists.id = albums.idartist and albums.album='"&name&"' group by artist, songs.year, album, albums.id "
Set Iter = SDB.Database.OpenSQL(sql)
Do Until Iter.EOF
caption = albumCaption(Iter.StringByIndex(2),Iter.StringByIndex(3),Iter.StringByIndex(0))
If Not captionTable.Exists(caption) Then
captionTable.add caption, name&"@@"&Iter.StringByIndex(1)
End If
Iter.Next
Loop
Next
Dim aTemp, iTemp
Call BuildArray(captionTable, aTemp)
Call SortArrayStr(aTemp)
For iTemp = 0 To UBound(aTemp)
Set subRoot = SDB.MainTree.createNode
caption = aTemp(iTemp)
albumId = captionTable.Item(aTemp(iTemp))
subRoot.Caption = caption
subRoot.IconIndex = 16
subRoot.UseScript = Script.ScriptPath
subRoot.OnFillTracksFunct = "FillAlbumLeaf"
subRoot.customData = albumId
subRoot.sortCriteria = 0
subRoot.sortGroup = 0
subRoot.hasChildren = False
SDB.MainTree.AddNode Node, subRoot, 3
Next
End Sub
'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
' Startup Function
'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
Sub onStartUp
Dim Tree, Iter
Dim sql
Dim CompRoot
Set CompRoot = SDB.MainTree.createNode
CompRoot.Caption = RootNodeCaption
CompRoot.IconIndex = 16
CompRoot.UseScript = Script.ScriptPath
CompRoot.onFillChildren = "FillRootCustomNode"
SDB.MainTree.AddNode SDB.MainTree.Node_Album, CompRoot, 1
CompRoot.hasChildren = True
End Sub
Ive started it, but its giving me a error about 's'
[code]' Complete Albums
' Version 1.0
' A script to create a "complete album" node below the album node on the tree.
' This script creates a node in the tree, below the album node, called "Albums (Complete)".
' The node displays only albums where the number of songs is > 1 and the total number of songs
' for the album is greater than or equal to the highest existing track number. This does
' create the occasional false positive, and it doesn't include albums with a single track,
' but for the most part, it displays all complete albums in the format:
' Album Artist - [year] Album Name
' and orders them by that caption. It also separates them into subdirectories for each letter
' to reduce clutter.
'
' It also does some stuff that allows you to include a single song on multiple albums. For example,
' let's say you have an EP with the album version of a song you also have on album, you can keep
' a single copy of the song, rather than a copy for each place it shows up. To do this, you add
' a line to the comments with the format:
' LP:Other Album Name:01
' where the track number is a 2 digit number and the note occurs on a single line by itself. This
' tells the script that this song also should show up as the first track on Other Album Name.
'
' You can include multiple notes like this and the track will show up on each album listed. Note that
' the track still appears under the album that is referenced in the album tag, so the notes are
' only for *additional* albums. Also, if two artists have the same album name, I think it will
' fail. I didn't test it, because it doesn't happen often enough for me to care, but if you decide
' to go with a few different "Greatest Hits" albums, you may have problems.
'
' The root caption can be changed by editing the RootNodeCaption constant.
' The album caption can be changed by editing the albumCaption function.
'
' This script does not update the DB or files. Use at your own risk. Do not taunt this script.
'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
' Global Variables and Declarations
'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
Option Explicit
' %%% The caption for the root node.
Const RootNodeCaption = "Albums (Complete)"
' %%% The function that builds the caption for the album node.
Function albumCaption(artist, year, album)
Dim caption
If Len(year) < 4 Then
year = year & "-"
End If
If Len(year) < 4 Then
year = ""
Else
year = "["&year&"] "
End If
caption = artist&" - "&year&""&album
albumCaption = caption
End Function
' %%% The code the script uses to check in comments for tracks that appear on more than one album.
Const LPCode = "LP"
'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
' The Meat. Don't change anything under here.
'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
Sub SortArrayStr(aTempArray)
Dim iTemp, jTemp, strTemp
For iTemp = 0 To UBound(aTempArray)
For jTemp = 0 To iTemp
If strComp(aTempArray(jTemp), aTempArray(iTemp)) > 0 Then
'Swap the array positions
strTemp = aTempArray(jTemp)
aTempArray(jTemp) = aTempArray(iTemp)
aTempArray(iTemp) = strTemp
End If
Next
Next
End Sub
Sub SortArray(aTempArray)
Dim iTemp, jTemp, strTemp
For iTemp = 0 To UBound(aTempArray)
For jTemp = 0 To iTemp
If aTempArray(jTemp) > aTempArray(iTemp) Then
'Swap the array positions
strTemp = aTempArray(jTemp)
aTempArray(jTemp) = aTempArray(iTemp)
aTempArray(iTemp) = strTemp
End If
Next
Next
End Sub
Sub BuildArray(CompList, aTempArray)
Dim nCount, strKey
nCount = 0
'-- Redim the array to the number of keys we need
Redim aTempArray(CompList.Count - 1)
'-- Load the array
For Each strKey In CompList.Keys
'-- Set the array element to the key
aTempArray(nCount) = strKey
'-- Increment the count
nCount = nCount + 1
Next
End Sub
Sub FillAlbumLeaf(Node)
Dim sql, songlist, SplitCustomData, name, albumId
SplitCustomData = Split(Node.CustomData,"@@")
name = SplitCustomData(0)
albumId = SplitCustomData(1)
sql = "select Songs.Id, Songs.SongOrder from Songs where songs.idAlbum = "&albumId
Dim Iter, id, track, song, songTable
Set songTable = CreateObject("Scripting.Dictionary")
Set Iter = SDB.Database.OpenSQL(sql)
Do Until Iter.EOF
id = Iter.StringByIndex(0)
track = Iter.StringByIndex(1) + 1
If Not songTable.Exists(track) Then
songTable.add track, id
Else
SDB.MessageBox "Duplicate track numbers exist for this album. Will not show duplciate tracks.", mtWarning, Array(mbOk)
End If
Iter.Next
Loop
Dim lengthSql, posSql, trackNumSql, sql2
name = Replace(name, "'", "''")
lengthSql = "Len('"&name&"')"
posSql = "InStr(Memos.MemoText,'"&name&"')"
trackNumSql = "Mid(Memos.MemoText, "&posSql&"+"&lengthSql&"+1,2)"
sql2 = "select Songs.Id, "&trackNumSql&" from Songs, Memos " &_
"where Songs.Id = Memos.IdSong and MemoText like '%"&LPCode&":"&name&":%' "
Set Iter = SDB.Database.OpenSQL(sql2)
Do Until Iter.EOF
id = Iter.StringByIndex(0)
track = Iter.StringByIndex(1) + 0
If Not songTable.Exists(track) Then
songTable.add track, id
Else
SDB.MessageBox "Duplicate track numbers exist for this album. Will not show duplciate tracks.", mtWarning, Array(mbOk)
End If
Iter.Next
Loop
Dim aTemp, iTemp, trackNum, songId
Call BuildArray(songTable, aTemp)
Call SortArray(aTemp)
Dim Tracks
Set Tracks = SDB.MainTracksWindow
For iTemp = 0 To UBound(aTemp)
songId = songTable.Item(aTemp(iTemp))
Tracks.AddTracksFromQuery("AND Songs.ID = "&songId)
Iter.Next
Next
Tracks.FinishAdding
End Sub
Sub FillRootCustomNode(Node)
Node.hasChildren = False
Dim sql, fatsql, Iter, subroot, albumId, year, id
fatsql = "select artists.id from songs, albums, artists where songs.IDAlbum = albums.id and artists.id = albums.artist group by artists.id, songs.year, albums.album, albums.id having count(*) >= (max(songs.tracknumber)+1) and count(*) > 1 and albums.album <> '' and max(songs.tracknumber) > -1"
Set Iter = SDB.Database.OpenSQL(fatsql)
Dim idTable
Set idTable= CreateObject("Scripting.Dictionary")
Do Until Iter.EOF
id = Iter.StringByIndex(0)
If Not idTable.Exists(id) Then
idTable.add id, id
End If
Iter.Next
Loop
Dim s, strKey
For Each strKey In idTable.Keys
s = s &"," & strKey
Next
s = Mid(s,2)
sql = "select left(artist,1) from artists where artists.id in ("&s&") group by left(artist,1)"
Set Iter = SDB.Database.OpenSQL(sql)
Do Until Iter.EOF
Set subRoot = SDB.MainTree.createNode
subRoot.Caption = Iter.StringByIndex(0)
subRoot.IconIndex = 16
subRoot.UseScript = Script.ScriptPath
subRoot.onFillChildren = "FillSubRootLetterNode"
subRoot.customData = Iter.StringByIndex(0)
subRoot.sortCriteria = 2
subRoot.sortGroup = 2
SDB.MainTree.AddNode Node, subRoot, 3
subRoot.hasChildren = True
Iter.Next
Loop
End Sub
Sub FillSubRootLetterNode(Node)
Node.hasChildren = False
Dim sql, Iter, subroot, albumId, year, letter
letter = Left(Node.customData,1)
sql = "select album, albums.id, artist, max(songs.year) from songs, albums, artists where left(artists.artist,1) = '"&letter&"' and songs.IDAlbum = albums.id and artists.id = albums.idartist group by artist, songs.year, album, albums.id having count(*) >= (max(songs.songorder)+1) and count(*) > 1 and album <> '' and max(songs.songorder) > -1"
Dim captionTable, caption
Set captionTable= CreateObject("Scripting.Dictionary")
Set Iter = SDB.Database.OpenSQL(sql)
Do Until Iter.EOF
caption = albumCaption(Iter.StringByIndex(2),Iter.StringByIndex(3),Iter.StringByIndex(0))
If Not captionTable.Exists(caption) Then
captionTable.add caption, Iter.StringByIndex(0)&"@@"&Iter.StringByIndex(1)
End If
Iter.Next
Loop
sql = "select Memos.MemoText from Memos where MemoText like '%"&LPCode&":%'"
Dim lpTable, bpos, s, epos, name
Set lpTable= CreateObject("Scripting.Dictionary")
Set Iter = SDB.Database.OpenSQL(sql)
Do Until Iter.EOF
s = Iter.StringByIndex(0)
Do Until Len(s) = 0 Or InStr(s, LPCode&":") = 0
bpos = InStr(s, LPCode&":")
If bpos <> 1 Then
s = Mid(s, bpos)
End If
s = Mid(s, Len(LPCode)+2)
epos = InStr(s, vbcrlf)
If epos = 0 Then
epos = Len(s) + 1
End If
name = Left(s, epos-1)
If Left(Right(name,3),1) = ":" Or Left(Right(name,3),1) = "." Then
name = Mid(name,1,Len(name)-3)
End If
If Not lpTable.Exists(name) Then
lpTable.add name, name
End If
s = Mid(s,epos)
Loop
Iter.Next
Loop
For Each name In lpTable.Keys
sql = "select album, albums.id, artist, max(songs.year) from songs, albums, artists where left(artists.artist,1) = '"&letter&"' and songs.IDAlbum = albums.id and artists.id = albums.idartist and albums.album='"&name&"' group by artist, songs.year, album, albums.id "
Set Iter = SDB.Database.OpenSQL(sql)
Do Until Iter.EOF
caption = albumCaption(Iter.StringByIndex(2),Iter.StringByIndex(3),Iter.StringByIndex(0))
If Not captionTable.Exists(caption) Then
captionTable.add caption, name&"@@"&Iter.StringByIndex(1)
End If
Iter.Next
Loop
Next
Dim aTemp, iTemp
Call BuildArray(captionTable, aTemp)
Call SortArrayStr(aTemp)
For iTemp = 0 To UBound(aTemp)
Set subRoot = SDB.MainTree.createNode
caption = aTemp(iTemp)
albumId = captionTable.Item(aTemp(iTemp))
subRoot.Caption = caption
subRoot.IconIndex = 16
subRoot.UseScript = Script.ScriptPath
subRoot.OnFillTracksFunct = "FillAlbumLeaf"
subRoot.customData = albumId
subRoot.sortCriteria = 0
subRoot.sortGroup = 0
subRoot.hasChildren = False
SDB.MainTree.AddNode Node, subRoot, 3
Next
End Sub
'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
' Startup Function
'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
Sub onStartUp
Dim Tree, Iter
Dim sql
Dim CompRoot
Set CompRoot = SDB.MainTree.createNode
CompRoot.Caption = RootNodeCaption
CompRoot.IconIndex = 16
CompRoot.UseScript = Script.ScriptPath
CompRoot.onFillChildren = "FillRootCustomNode"
SDB.MainTree.AddNode SDB.MainTree.Node_Album, CompRoot, 1
CompRoot.hasChildren = True
End Sub[/code]