public bool Execute(out List <Song> songs)
{
if (currentLevel < 0)
{
previousLevel = -1;
songs = new List <Song>();
return(false);
}
string whereClause = string.Empty;
string orderClause = string.Empty;
FilterDefinition definition = (FilterDefinition)currentView.Filters[CurrentLevel];
restrictionLength = 0;
for (int i = 0; i < CurrentLevel; ++i)
{
BuildSelect((FilterDefinition)currentView.Filters[i], ref whereClause, i);
}
BuildWhere((FilterDefinition)currentView.Filters[CurrentLevel], ref whereClause);
BuildRestriction((FilterDefinition)currentView.Filters[CurrentLevel], ref whereClause);
BuildOrder((FilterDefinition)currentView.Filters[CurrentLevel], ref orderClause);
if (CurrentLevel > 0)
{
// When grouping is active, we need to omit the "where ";
if (!whereClause.Trim().StartsWith("group ") && whereClause.Trim() != "")
{
whereClause = "where " + whereClause;
}
}
//execute the query
string sql = "";
if (CurrentLevel == 0)
{
FilterDefinition defRoot = (FilterDefinition)currentView.Filters[0];
string table = GetTable(defRoot.Where);
string searchField = GetField(defRoot.Where);
// Handle the grouping of songs
if (definition.SqlOperator == "group")
{
string searchTable = table;
string countField = searchField; // when grouping on Albums, we need to count the artists
// We don't have an album table anymore, so change the table to search for to tracks here.
if (table == "album")
{
searchTable = "tracks";
countField = "strAlbumArtist";
}
sql = String.Format("Select UPPER(SUBSTR({0},1,{1})) as IX, Count(distinct {2}) from {3} GROUP BY IX",
searchField, definition.Restriction, countField, searchTable);
// only group special characters into a "#" entry is field is text based
if (defRoot.Where == "rating" || defRoot.Where == "year" || defRoot.Where == "track" || defRoot.Where == "disc#" ||
defRoot.Where == "timesplayed" || defRoot.Where == "favourites" || defRoot.Where == "date")
{
database.GetSongsByFilter(sql, out songs, table);
}
else
{
database.GetSongsByIndex(sql, out songs, CurrentLevel, table);
}
previousLevel = currentLevel;
return(true);
}
switch (table)
{
case "artist":
case "albumartist":
case "genre":
case "composer":
sql = String.Format("select * from {0} ", table);
if (whereClause != string.Empty)
{
sql += "where " + whereClause;
}
if (orderClause != string.Empty)
{
sql += orderClause;
}
break;
case "album":
sql = String.Format("select * from tracks ");
if (whereClause != string.Empty)
{
sql += "where " + whereClause;
}
sql += " group by strAlbum, strAlbumArtist ";
// We need to group on AlbumArtist, to show Albums with same name for different artists
if (orderClause != string.Empty)
{
sql += orderClause;
}
break;
case "tracks":
if (defRoot.Where == "year")
{
songs = new List <Song>();
sql = String.Format("select distinct iYear from tracks ");
SQLiteResultSet results = MusicDatabase.DirectExecute(sql);
for (int i = 0; i < results.Rows.Count; i++)
{
Song song = new Song();
try
{
song.Year = (int)Math.Floor(0.5d + Double.Parse(DatabaseUtility.Get(results, i, "iYear")));
}
catch (Exception)
{
song.Year = 0;
}
if (song.Year > 1000)
{
songs.Add(song);
}
}
previousLevel = currentLevel;
return(true);
}
else if (defRoot.Where == "recently added")
{
try
{
whereClause = "";
TimeSpan ts = new TimeSpan(Convert.ToInt32(defRoot.Restriction), 0, 0, 0);
DateTime searchDate = DateTime.Today - ts;
whereClause = String.Format("where {0} > '{1}'", searchField, searchDate.ToString("yyyy-MM-dd hh:mm:ss"));
sql = String.Format("select * from tracks {0} {1}", whereClause, orderClause);
}
catch (Exception) {}
}
else if (defRoot.Where == "conductor")
{
whereClause = "";
BuildRestriction(defRoot, ref whereClause);
if (whereClause != string.Empty)
{
whereClause = String.Format("where {0}", whereClause);
}
sql = String.Format("select distinct strConductor from tracks {0} {1}", whereClause, orderClause);
}
else
{
whereClause = "";
BuildRestriction(defRoot, ref whereClause);
if (whereClause != string.Empty)
{
whereClause = String.Format("where {0}", whereClause);
}
sql = String.Format("select * from tracks {0} {1}", whereClause, orderClause);
}
break;
}
database.GetSongsByFilter(sql, out songs, table);
}
else if (CurrentLevel < MaxLevels - 1)
{
FilterDefinition defCurrent = (FilterDefinition)currentView.Filters[CurrentLevel];
string table = GetTable(defCurrent.Where);
// Now we need to check the previous filters, if we were already on the tracks table previously
// In this case the from clause must contain the tracks table only
bool isUsingTrackTable = false;
string allPrevColumns = string.Empty;
for (int i = CurrentLevel; i > -1; i--)
{
FilterDefinition filter = (FilterDefinition)currentView.Filters[i];
allPrevColumns += " " + GetField(filter.Where) + " ,";
if (GetTable(filter.Where) != table)
{
isUsingTrackTable = true;
}
}
allPrevColumns = allPrevColumns.Remove(allPrevColumns.Length - 1, 1); // remove extra trailing comma
if (defCurrent.SqlOperator == "group")
{
// in an odd scenario here as we have a group operator
// but not at the first level of view
// Build correct table for search
string searchTable = GetTable(defCurrent.Where);
string searchField = GetField(defCurrent.Where);
string countField = searchField;
// We don't have an album table anymore, so change the table to search for to tracks here.
if (table == "album")
{
searchTable = "tracks";
countField = "strAlbumArtist";
}
if (isUsingTrackTable && searchTable != "tracks")
{
// have the messy case where previous filters in view
// do not use the same table as the current level
// which means we can not just lookup values in search table
string joinSQL;
if (IsMultipleValueField(searchField))
{
joinSQL = string.Format("and tracks.{1} like '%| '||{0}.{1}||' |%' ",
searchTable, searchField);
}
else
{
joinSQL = string.Format("and tracks.{1} = {0}.{1} ",
searchTable, searchField);
}
whereClause = whereClause.Replace("group by ix", "");
whereClause = string.Format(" where exists ( " +
" select 0 " +
" from tracks " +
" {0} " +
" {1} " +
") " +
"group by ix "
, whereClause, joinSQL);
}
sql = String.Format("select UPPER(SUBSTR({0},1,{1})) IX, Count(distinct {2}) from {3} {4} {5}",
searchField, Convert.ToInt16(defCurrent.Restriction), countField,
searchTable, whereClause, orderClause);
database.GetSongsByIndex(sql, out songs, CurrentLevel, table);
}
else
{
string from = String.Format("{1} from {0}", table, GetField(defCurrent.Where));
if (isUsingTrackTable && table != "album" && defCurrent.Where != "Disc#")
{
from = String.Format("{0} from tracks", allPrevColumns);
table = "tracks";
}
// When searching for an album, we need to retrieve the AlbumArtist as well, because we could have same album names for different artists
// We need also the Path to retrieve the coverart
// We don't have an album table anymore, so change the table to search for to tracks here.
for (int i = 0; i < currentLevel; i++)
{
// get previous filter to see, if we had an album that was not a group level
FilterDefinition defPrevious = (FilterDefinition)currentView.Filters[i];
if (defPrevious.Where == "album" && defPrevious.SqlOperator != "group")
{
if (whereClause != "")
{
whereClause += " and ";
}
string selectedArtist = currentSong.AlbumArtist;
DatabaseUtility.RemoveInvalidChars(ref selectedArtist);
// we don't store "unknown" into the datbase, so let's correct empty values
if (selectedArtist == "unknown")
{
selectedArtist = string.Empty;
}
whereClause += String.Format("strAlbumArtist like '%| {0} |%'", selectedArtist);
break;
}
}
if (table == "album")
{
from = String.Format("* from tracks", GetField(defCurrent.Where));
whereClause += " group by strAlbum, strAlbumArtist ";
}
if (defCurrent.Where == "disc#")
{
from = String.Format("* from tracks", GetField(defCurrent.Where));
whereClause += " group by strAlbum, strAlbumArtist, iDisc ";
}
sql = String.Format("select distinct {0} {1} {2}", from, whereClause, orderClause);
database.GetSongsByFilter(sql, out songs, table);
}
}
else
{
for (int i = 0; i < currentLevel; i++)
{
// get previous filter to see, if we had an album that was not a group level
FilterDefinition defPrevious = (FilterDefinition)currentView.Filters[i];
if (defPrevious.Where == "album" && defPrevious.SqlOperator != "group")
{
if (whereClause != "")
{
whereClause += " and ";
}
string selectedArtist = currentSong.AlbumArtist;
DatabaseUtility.RemoveInvalidChars(ref selectedArtist);
// we don't store "unknown" into the datbase, so let's correct empty values
if (selectedArtist == "unknown")
{
selectedArtist = string.Empty;
}
whereClause += String.Format("strAlbumArtist like '%| {0} |%'", selectedArtist);
break;
}
}
sql = String.Format("select * from tracks {0} {1}", whereClause, orderClause);
database.GetSongsByFilter(sql, out songs, "tracks");
}
if (songs.Count == 1 && definition.SkipLevel)
{
if (currentLevel < MaxLevels - 1)
{
if (previousLevel < currentLevel)
{
FilterDefinition fd = (FilterDefinition)currentView.Filters[currentLevel];
fd.SelectedValue = GetFieldValue(songs[0], fd.Where);
currentLevel = currentLevel + 1;
}
else
{
currentLevel = currentLevel - 1;
}
if (!Execute(out songs))
{
return(false);
}
}
}
previousLevel = currentLevel;
return(true);
}