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);
}
public bool GetDetails(DBBasicInfo mv)
{
logger.Debug("In Method: GetDetails(DBBasicInfo mv)");
MusicDatabase m_db = null;
string inLang = mvCentralCore.Settings.DataProviderAutoLanguage;
ReportProgress(string.Empty);
try
{
m_db = MusicDatabase.Instance;
}
catch (Exception e)
{
logger.Error("GetDetails: Music database init failed " + e.ToString());
return(false);
}
// ---------------- Get Artist Info ---------------------
if (mv.GetType() == typeof(DBArtistInfo))
{
var artist = ((DBArtistInfo)mv).Artist;
var releases = new List <Release>();
var artists = new ArrayList();
// Grab the Artist Info
if (artist != null)
{
ReportProgress("Getting Artists...");
logger.Debug("GetDetails: Getting Artists: " + artist);
artists.Clear();
string strArtist = NormalizeArtist(artist);
string strSQL = String.Format("SELECT a.strArtist FROM artist a, artistinfo i WHERE LOWER(a.strArtist) = LOWER(i.strArtist) AND i.strAMGBio IS NOT NULL AND TRIM(i.strAMGBio) <> '' AND LOWER(i.strArtist) LIKE '%{0}%';", strArtist);
List <Song> songInfo = new List <Song>();
m_db.GetSongsByFilter(strSQL, out songInfo, "artist");
foreach (Song mySong in songInfo)
{
if (!string.IsNullOrEmpty(mySong.Artist))
{
artists.Add(mySong.Artist);
}
}
}
else
{
return(false);
}
if (artists == null || artists.Count <= 0)
{
return(false);
}
foreach (string _artist in artists)
{
Release r2 = new Release(_artist, string.Empty);
releases.Add(r2);
}
ReportProgress("Done!");
// Now sort and Display the retrived matches
releases.Sort(Release.TitleComparison);
var resultsDialog = new DetailsPopup(releases);
// Get the full info for the selection
if (resultsDialog.ShowDialog() == DialogResult.OK)
{
var mv1 = (DBArtistInfo)mv;
mv.ArtUrls.Clear();
string title = resultsDialog.selectedItem.Text;
string mbid = resultsDialog.label8.Text;
if (title.Trim().Length == 0)
{
title = null;
}
if (mbid.Trim().Length == 0)
{
mbid = null;
}
setMusicVideoArtist(ref mv1, title, mbid);
GetArtistArt((DBArtistInfo)mv);
}
}
// -------------- Get Album Info --------------
if (mv.GetType() == typeof(DBAlbumInfo))
{
List <DBTrackInfo> a1 = DBTrackInfo.GetEntriesByAlbum((DBAlbumInfo)mv);
if (a1.Count > 0)
{
string artist = a1[0].ArtistInfo[0].Artist;
var albums = new ArrayList();;
if (artist != null)
{
ReportProgress("Getting Albums...");
logger.Debug("GetDetails: Getting Albums: " + artist);
albums.Clear();
string strArtist = NormalizeArtist(artist);
string strSQL = String.Format("SELECT DISTINCT T.strAlbum, A.strReview FROM albuminfo A, tracks T WHERE A.strReview IS NOT NULL AND TRIM(A.strReview) <> '' AND (A.strArtist LIKE '%{0}%' OR A.strAlbumArtist LIKE '%{1}%') AND T.strAlbum = A.strAlbum COLLATE NOCASE;", strArtist, strArtist);
List <Song> songInfo = new List <Song>();
m_db.GetSongsByFilter(strSQL, out songInfo, "album");
logger.Debug("GetDetails: Getting Albums: " + artist + " - " + songInfo.Count);
foreach (Song mySong in songInfo)
{
if (!string.IsNullOrEmpty(mySong.Album))
{
albums.Add(mySong.Album);
}
}
}
else
{
return(false);
}
if (albums == null || albums.Count <= 0)
{
return(false);
}
List <Release> artistTopAlbumns = new List <Release>();
foreach (string _album in albums)
{
logger.Debug("GetDetails: Getting Albums: " + artist + " - " + _album);
Release r2 = new Release(_album, string.Empty);
artistTopAlbumns.Add(r2);
}
ReportProgress("Done!");
artistTopAlbumns.Sort(Release.TitleComparison);
DetailsPopup d1 = new DetailsPopup(artistTopAlbumns);
if (d1.ShowDialog() == DialogResult.OK)
{
DBAlbumInfo mv1 = (DBAlbumInfo)mv;
mv.ArtUrls.Clear();
string title = d1.selectedItem.Text;
string mbid = d1.label8.Text;
if (title.Trim().Length == 0)
{
title = null;
}
if (mbid.Trim().Length == 0)
{
mbid = null;
}
setMusicVideoAlbum(ref mv1, artist, title, mbid);
GetAlbumArt((DBAlbumInfo)mv);
}
}
}
// -------------- Get Track Info --------------
if (mv.GetType() == typeof(DBTrackInfo))
{
string artist = ((DBTrackInfo)mv).ArtistInfo[0].Artist;
var tracks = new ArrayList();;
if (artist != null)
{
ReportProgress("Getting Tracks...");
logger.Debug("GetDetails: Getting Tracks: " + artist);
tracks.Clear();
string strArtist = NormalizeArtist(artist);
string strSQL = String.Format("SELECT strTitle FROM tracks WHERE strArtist LIKE '%| {0} |%' OR strAlbumArtist LIKE '%| {1} |%';');", strArtist, strArtist);
List <Song> songInfo = new List <Song>();
m_db.GetSongsByFilter(strSQL, out songInfo, "tracks");
logger.Debug("GetDetails: Getting Tracks: " + artist + " - " + songInfo.Count);
foreach (Song mySong in songInfo)
{
if (!string.IsNullOrEmpty(mySong.Title))
{
tracks.Add(mySong.Title);
}
}
}
else
{
return(false);
}
if (tracks == null || tracks.Count <= 0)
{
return(false);
}
List <Release> artistTopTracks = new List <Release>();
foreach (string _track in tracks)
{
logger.Debug("GetDetails: Getting Track: " + artist + " - " + _track);
Release r2 = new Release(_track, string.Empty);
artistTopTracks.Add(r2);
}
ReportProgress("Done!");
artistTopTracks.Sort(Release.TitleComparison);
DetailsPopup d1 = new DetailsPopup(artistTopTracks);
if (d1.ShowDialog() == DialogResult.OK)
{
DBTrackInfo mv1 = (DBTrackInfo)mv;
mv.ArtUrls.Clear();
if (artist.Trim().Length == 0)
{
artist = null;
}
string title = d1.selectedItem.Text;
string mbid = d1.label8.Text;
if (title.Trim().Length == 0)
{
title = null;
}
if (mbid.Trim().Length == 0)
{
mbid = null;
}
setMusicVideoTrack(ref mv1, artist, title, mbid);
GetTrackArt((DBTrackInfo)mv);
}
}
return(true);
}
