Here's what I came up with. Shamelessly cribbed from Pablo's Magic Nodes script.
Code: Select all
' AlbumRating Script
' Version 1.0
' Inspired by and Shameless ripped off from Pablo's Magic Nodes script
' This script creates a root node in the tree, under the library node, called "Album Rating"
' (or whatever you change that too). Beneath this node, it lists all albums that contain rated
' songs, based on the options chosen below. Albums with no rated songs at all are left out.
'
' This script can be freely used and modified.
' This is an early release and there may be bugs. If it's causing you problems, simply delete
' it or move it out of the scripts\auto folder.
'
' The script does not modify the database, registry or INI file.
'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
' Global Variables and Declarations
'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
Option Explicit
' %%% If Unrated songs are to be included in the rating, they should receive this value as a default
Const UnratedValue = 3
' %%% If this is true, ignore any unrated songs when calculating the average.
' If false, use the 'unrated value' above.
Const ignoreUnrated = True
' %%% Only display albums that have all the tracks. This compares the highest #'d track to the total
' number of tracks. If they are even, it assumes the album is complete. However, if you only have
' track #1 of an album, or are missing the last track(s), it will still count the album as complete.
' Oh well.
Const onlyWholeAlbums = False
' %%% This weights the ratings by the length of the song. So, if you have a 30 minute 4-star song and a
' one minute one-star song, the 4 star song is weighted 30 times as strongly. I think this reflects
' a more true view of the quality of the album.
Const weightByTime = True
' %%% If true, it rounds the rating and turns it into stars: ***½.
' If false, it just displays the average rating, rounded to 2 places.
Const showStars = False
' %%% The caption for the root node.
Const RootNodeCaption = "Album Rating"
' %%% The display format of the album node. The artist is actually "Album Artist", so 'various' will display, etc.
Function formatCaption(score,artist,album)
formatCaption = "[" & score & "] " & artist & ": " & album
End Function
'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
' The Meat. Don't change anything under here.
'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
'A procedure that does nothing
Sub DummyFunct(Argument)
End Sub
Function starString(score)
score = FormatNumber(score * 2, 0)
If score = "0" Then
starString = "--"
ElseIf score = "1" Then
starString = "½"
ElseIf score = "2" Then
starString = "*"
ElseIf score = "3" Then
starString = "*½"
ElseIf score = "4" Then
starString = "**"
ElseIf score = "5" Then
starString = "**½"
ElseIf score = "6" Then
starString = "***"
ElseIf score = "7" Then
starString = "***½"
ElseIf score = "8" Then
starString = "****"
ElseIf score = "9" Then
starString = "****½"
ElseIf score = "10" Then
starString = "*****"
End If
End Function
'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
' Creation of Node Hierarchy
'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
Sub FillStandardProperties(parentNode, childNode)
With childNode
.CustomNodeId = parentNode.CustomNodeId
.CustomDataId = parentNode.CustomDataId + 1
.UseScript = Script.ScriptPath
End With
End Sub
Sub FillCustomNode(Node)
Dim SQLLinking ' Piece of SQL Code (WHERE statement) defining the relations between tables
Dim Tree, newNode, nextIsLeaf
Dim scoreField
Dim idIndex
Dim albumIndex
Dim artistIndex
Dim scoreIndex
Dim albumName, artistName, avgScore, formattedScore
Dim SELECT_Clause, FROM_Clause, WHERE_Clause, GROUP_BY_Clause, ORDER_BY_Clause, HAVING_Clause
Dim SQLStatement ' SQL query to the database
Dim EscapedId ' Used to escape a text value in the database to be used as a test
Dim Iter ' SDBD Iterator obtained by running the SQL query to get the nodes
Dim SQLKnown
SQLLinking = " Songs.IdAlbum = Albums.ID and Artists.Id = Albums.IdArtist "
SQLKnown = "Songs.IdAlbum > 0 "
nextIsLeaf = True
idIndex = 0
albumIndex = 1
artistIndex = 2
scoreIndex = 3
If weightByTime Then
scoreField = "SUM(Songs.SongLength * iif(Songs.Rating > -1,Songs.Rating/20,"& UnratedValue &"))/SUM(Songs.SongLength)"
Else
scoreField = "sum(iif(Songs.Rating > -1,Songs.Rating/20,"& UnratedValue &"))/count(*)"
End If
Set Tree = SDB.MainTree
Node.HasChildren = false
SELECT_Clause = " SELECT DISTINCT Songs.IdAlbum, Albums.Album, Artists.Artist, " & scoreField
FROM_Clause = " FROM Songs, Albums, Artists "
WHERE_Clause = "WHERE " & SQLLinking & " AND " & SQLKnown
If ignoreUnrated = True Then
WHERE_Clause = WHERE_Clause & " AND Songs.Rating >= 0"
End If
GROUP_BY_Clause = " GROUP BY Songs.IdAlbum, Albums.Album, Artists.Artist"
HAVING_Clause = " HAVING avg(Songs.Rating) > -1 "
If onlyWholeAlbums = True Then
HAVING_Clause = HAVING_Clause & " AND (MAX(Songs.SongOrder)+1) <= Count(*)"
End If
ORDER_BY_Clause = " ORDER BY " & scoreField & " DESC, Albums.Album, Artists.Artist"
SQLStatement = SELECT_Clause & FROM_Clause & WHERE_Clause & GROUP_BY_Clause & HAVING_Clause & ORDER_BY_Clause
' Fill regular subnodes
Set Iter = SDB.Database.OpenSQL(SQLStatement)
While Not Iter.EOF
Set NewNode = Tree.CreateNode
NewNode.iconIndex = 16
albumName = Iter.StringByIndex(albumIndex)
artistName = Iter.StringByIndex(artistIndex)
avgScore = Iter.StringByIndex(scoreIndex)
If showStars Then
formattedScore = starString(avgScore)
Else
formattedScore = FormatNumber(avgScore, 2)
End If
NewNode.Caption = formatCaption(formattedScore, artistName, albumName)
escapedId = Iter.StringByIndex(0)
newNode.CustomData = SQLKnown & "AND Songs.IdAlbum=" & escapedId & "@@" & SQLLinking & "@@Songs, Albums, Artists"
FillStandardProperties node,newNode
newNode.onFillTracksFunct = "FillCustomLeaf"
Tree.AddNode Node, NewNode, 3
newNode.hasChildren = Not nextIsLeaf
Iter.Next
Wend
End Sub
Sub FillCustomLeaf(Node)
Dim SplitCustomData, Tracks
Dim SQLCondition, SQLLinking
Dim SELECT_Clause, FROM_Clause, WHERE_Clause
SplitCustomData = Split(Node.CustomData,"@@")
' condition includes album ID
SQLCondition = SplitCustomData(0)
SQLLinking = " Songs.IdAlbum = Albums.ID and Artists.Id = Albums.IdArtist "
SELECT_Clause = " SELECT Songs.Id "
FROM_Clause = " FROM Songs, Albums, Artists "
WHERE_Clause = " WHERE " & SQLLinking & " AND " & SQLCondition
Set Tracks = SDB.MainTracksWindow
Tracks.AddTracksFromQuery("AND Songs.ID IN (" & SELECT_Clause & FROM_Clause & WHERE_Clause & ")")
Tracks.FinishAdding
End Sub
'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
' Startup Function
'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
Sub onStartUp
Dim Tree, AlbumRatingNodeRoot
Dim SQLFilter
Dim SQLLinking, SQLTables
Set Tree = Sdb.MainTree
Set AlbumRatingNodeRoot = Tree.createNode
SQLFilter = ""
SQLLinking = " Songs.IdAlbum = Albums.ID and Artists.Id = Albums.IdArtist "
SQLTables = " Songs, Albums, Artists "
AlbumRatingNodeRoot.Caption = RootNodeCaption
AlbumRatingNodeRoot.IconIndex = 33
AlbumRatingNodeRoot.UseScript = Script.ScriptPath
AlbumRatingNodeRoot.onFillChildren = "FillCustomNode"
AlbumRatingNodeRoot.customData = SQLFilter & " @@" & SQLLinking & "@@" & SQLTables
Tree.AddNode Tree.Node_Library, AlbumRatingNodeRoot, 1
AlbumRatingNodeRoot.hasChildren = True
SDB.Objects("AlbumRatingNodeRoot") = AlbumRatingNodeRoot
End Sub