CStdString CDatabaseQueryRule::FormatParameter(const CStdString &operatorString, const CStdString ¶m, const CDatabase &db, const CStdString &strType) const
{
CStdString parameter;
if (GetFieldType(m_field) == TEXTIN_FIELD)
{
vector<string> split = StringUtils::Split(param, ",");
for (vector<string>::iterator itIn = split.begin(); itIn != split.end(); ++itIn)
{
if (!parameter.empty())
parameter += ",";
parameter += db.PrepareSQL("'%s'", StringUtils::Trim(*itIn).c_str());
}
parameter = " IN (" + parameter + ")";
}
else
parameter = db.PrepareSQL(operatorString.c_str(), ValidateParameter(param).c_str());
if (GetFieldType(m_field) == DATE_FIELD)
{
if (m_operator == OPERATOR_IN_THE_LAST || m_operator == OPERATOR_NOT_IN_THE_LAST)
{ // translate time period
CDateTime date=CDateTime::GetCurrentDateTime();
CDateTimeSpan span;
span.SetFromPeriod(param);
date-=span;
parameter = db.PrepareSQL(operatorString.c_str(), date.GetAsDBDate().c_str());
}
}
return parameter;
}
std::string CDatabaseQueryRule::GetWhereClause(const CDatabase &db, const std::string& strType) const
{
SEARCH_OPERATOR op = GetOperator(strType);
std::string operatorString = GetOperatorString(op);
std::string negate;
if (op == OPERATOR_DOES_NOT_CONTAIN || op == OPERATOR_FALSE ||
(op == OPERATOR_DOES_NOT_EQUAL && GetFieldType(m_field) != REAL_FIELD && GetFieldType(m_field) != NUMERIC_FIELD &&
GetFieldType(m_field) != SECONDS_FIELD))
negate = " NOT ";
// boolean operators don't have any values in m_parameter, they work on the operator
if (m_operator == OPERATOR_FALSE || m_operator == OPERATOR_TRUE)
return GetBooleanQuery(negate, strType);
// The BETWEEN operator is handled special
if (op == OPERATOR_BETWEEN)
{
if (m_parameter.size() != 2)
return "";
FIELD_TYPE fieldType = GetFieldType(m_field);
if (fieldType == REAL_FIELD)
return db.PrepareSQL("%s BETWEEN %s AND %s", GetField(m_field, strType).c_str(), m_parameter[0].c_str(), m_parameter[1].c_str());
else if (fieldType == NUMERIC_FIELD)
return db.PrepareSQL("CAST(%s as DECIMAL(5,1)) BETWEEN %s AND %s", GetField(m_field, strType).c_str(), m_parameter[0].c_str(), m_parameter[1].c_str());
else if (fieldType == SECONDS_FIELD)
return db.PrepareSQL("CAST(%s as INTEGER) BETWEEN %s AND %s", GetField(m_field, strType).c_str(), m_parameter[0].c_str(), m_parameter[1].c_str());
else
return db.PrepareSQL("%s BETWEEN '%s' AND '%s'", GetField(m_field, strType).c_str(), m_parameter[0].c_str(), m_parameter[1].c_str());
}
// now the query parameter
std::string wholeQuery;
for (std::vector<std::string>::const_iterator it = m_parameter.begin(); it != m_parameter.end(); ++it)
{
std::string query = '(' + FormatWhereClause(negate, operatorString, *it, db, strType) + ')';
if (it + 1 != m_parameter.end())
{
if (negate.empty())
query += " OR ";
else
query += " AND ";
}
wholeQuery += query;
}
return wholeQuery;
}
bool CGUIDialogMediaFilter::GetMinMax(const std::string &table, const std::string &field, int &min, int &max, const CDatabase::Filter &filter /* = CDatabase::Filter() */)
{
if (table.empty() || field.empty())
return false;
CDatabase *db = NULL;
CDbUrl *dbUrl = NULL;
if (m_mediaType == "movies" || m_mediaType == "tvshows" || m_mediaType == "episodes" || m_mediaType == "musicvideos")
{
CVideoDatabase *videodb = new CVideoDatabase();
if (!videodb->Open())
{
delete videodb;
return false;
}
db = videodb;
dbUrl = new CVideoDbUrl();
}
else if (m_mediaType == "artists" || m_mediaType == "albums" || m_mediaType == "songs")
{
CMusicDatabase *musicdb = new CMusicDatabase();
if (!musicdb->Open())
{
delete musicdb;
return false;
}
db = musicdb;
dbUrl = new CMusicDbUrl();
}
if (db == NULL || !db->IsOpen() || dbUrl == NULL)
{
delete db;
delete dbUrl;
return false;
}
CDatabase::Filter extFilter = filter;
std::string strSQLExtra;
if (!db->BuildSQL(m_dbUrl->ToString(), strSQLExtra, extFilter, strSQLExtra, *dbUrl))
{
delete db;
delete dbUrl;
return false;
}
std::string strSQL = "SELECT %s FROM %s ";
min = static_cast<int>(strtol(db->GetSingleValue(db->PrepareSQL(strSQL, std::string("MIN(" + field + ")").c_str(), table.c_str()) + strSQLExtra).c_str(), NULL, 0));
max = static_cast<int>(strtol(db->GetSingleValue(db->PrepareSQL(strSQL, std::string("MAX(" + field + ")").c_str(), table.c_str()) + strSQLExtra).c_str(), NULL, 0));
db->Close();
delete db;
delete dbUrl;
return true;
}
CStdString CSmartPlaylist::GetOrderClause(CDatabase &db)
{
CStdString order;
if (m_orderField != CSmartPlaylistRule::FIELD_NONE)
{
if (CSmartPlaylistRule::GetFieldType(m_orderField) == CSmartPlaylistRule::NUMERIC_FIELD)
order.Format("ORDER BY 1*%s", CSmartPlaylistRule::GetDatabaseField(m_orderField,GetType()));
else
order = db.PrepareSQL("ORDER BY %s", CSmartPlaylistRule::GetDatabaseField(m_orderField,GetType()).c_str());
if (!m_orderAscending)
order += " DESC";
}
if (m_limit)
{
CStdString limit;
limit.Format(" LIMIT %i", m_limit);
order += limit;
}
return order;
}
CStdString CSmartPlaylistRule::GetWhereClause(CDatabase &db, const CStdString& strType)
{
SEARCH_OPERATOR op = m_operator;
if ((strType == "tvshows" || strType == "episodes") && m_field == FIELD_YEAR)
{ // special case for premiered which is a date rather than a year
// TODO: SMARTPLAYLISTS do we really need this, or should we just make this field the premiered date and request a date?
if (op == OPERATOR_EQUALS)
op = OPERATOR_CONTAINS;
else if (op == OPERATOR_DOES_NOT_EQUAL)
op = OPERATOR_DOES_NOT_CONTAIN;
}
CStdString operatorString, negate;
CStdString parameter;
if (GetFieldType(m_field) == TEXTIN_FIELD)
{
CStdStringArray split;
StringUtils::SplitString(m_parameter, ",", split);
for (CStdStringArray::iterator it=split.begin(); it!=split.end(); ++it)
{
if (!parameter.IsEmpty())
parameter += ",";
parameter += db.PrepareSQL("'%s'", (*it).Trim().c_str());
}
parameter = " IN (" + parameter + ")";
if (op == OPERATOR_DOES_NOT_EQUAL)
negate = " NOT";
}
else
{
// the comparison piece
switch (op)
{
case OPERATOR_CONTAINS:
operatorString = " LIKE '%%%s%%'"; break;
case OPERATOR_DOES_NOT_CONTAIN:
negate = " NOT"; operatorString = " LIKE '%%%s%%'"; break;
case OPERATOR_EQUALS:
operatorString = " LIKE '%s'"; break;
case OPERATOR_DOES_NOT_EQUAL:
negate = " NOT"; operatorString = " LIKE '%s'"; break;
case OPERATOR_STARTS_WITH:
operatorString = " LIKE '%s%%'"; break;
case OPERATOR_ENDS_WITH:
operatorString = " LIKE '%%%s'"; break;
case OPERATOR_AFTER:
case OPERATOR_GREATER_THAN:
case OPERATOR_IN_THE_LAST:
operatorString = " > '%s'"; break;
case OPERATOR_BEFORE:
case OPERATOR_LESS_THAN:
case OPERATOR_NOT_IN_THE_LAST:
operatorString = " < '%s'"; break;
case OPERATOR_TRUE:
operatorString = " = 1"; break;
case OPERATOR_FALSE:
negate = " NOT "; operatorString = " = 0"; break;
default:
break;
}
parameter = db.PrepareSQL(operatorString.c_str(), m_parameter.c_str());
}
if (m_field == FIELD_LASTPLAYED || m_field == FIELD_AIRDATE)
{
if (m_operator == OPERATOR_IN_THE_LAST || m_operator == OPERATOR_NOT_IN_THE_LAST)
{ // translate time period
CDateTime date=CDateTime::GetCurrentDateTime();
CDateTimeSpan span;
span.SetFromPeriod(m_parameter);
date-=span;
parameter = db.PrepareSQL(operatorString.c_str(), date.GetAsDBDate().c_str());
}
}
else if (m_field == FIELD_TIME)
{ // translate time to seconds
CStdString seconds; seconds.Format("%i", StringUtils::TimeStringToSeconds(m_parameter));
parameter = db.PrepareSQL(operatorString.c_str(), seconds.c_str());
}
// now the query parameter
CStdString query;
if (strType == "songs")
{
if (m_field == FIELD_GENRE)
query = negate + " ((strGenre" + parameter + ") or idSong IN (select idSong from genre,exgenresong where exgenresong.idGenre = genre.idGenre and genre.strGenre" + parameter + "))";
else if (m_field == FIELD_ARTIST)
query = negate + " ((strArtist" + parameter + ") or idSong IN (select idSong from artist,exartistsong where exartistsong.idArtist = artist.idArtist and artist.strArtist" + parameter + "))";
else if (m_field == FIELD_ALBUMARTIST)
query = negate + " (idalbum in (select idalbum from artist,album where album.idArtist=artist.idArtist and artist.strArtist" + parameter + ") or idalbum in (select idalbum from artist,exartistalbum where exartistalbum.idArtist = artist.idArtist and artist.strArtist" + parameter + "))";
else if (m_field == FIELD_LASTPLAYED && (m_operator == OPERATOR_LESS_THAN || m_operator == OPERATOR_BEFORE || m_operator == OPERATOR_NOT_IN_THE_LAST))
query = "lastPlayed is NULL or lastPlayed" + parameter;
}
else if (strType == "albums")
{
if (m_field == FIELD_GENRE)
query = negate + " (idAlbum in (select song.idAlbum from song join genre on song.idGenre=genre.idGenre where genre.strGenre" + parameter + ") or "
"idAlbum in (select song.idAlbum from song join exgenresong on song.idSong=exgenresong.idSong join genre on exgenresong.idGenre=genre.idGenre where genre.strGenre" + parameter + "))";
else if (m_field == FIELD_ARTIST)
query = negate + " (idAlbum in (select song.idAlbum from song join artist on song.idArtist=artist.idArtist where artist.strArtist" + parameter + ") or "
"idAlbum in (select song.idAlbum from song join exartistsong on song.idSong=exartistsong.idSong join artist on exartistsong.idArtist=artist.idArtist where artist.strArtist" + parameter + "))";
else if (m_field == FIELD_ALBUMARTIST)
query = negate + " (idalbum in (select idalbum from artist,album where album.idArtist=artist.idArtist and artist.strArtist" + parameter + ") or idalbum in (select idalbum from artist,exartistalbum where exartistalbum.idArtist = artist.idArtist and artist.strArtist" + parameter + "))";
}
else if (strType == "movies")
{
if (m_field == FIELD_GENRE)
query = "idMovie" + negate + " in (select idMovie from genrelinkmovie join genre on genre.idGenre=genrelinkmovie.idGenre where genre.strGenre" + parameter + ")";
else if (m_field == FIELD_DIRECTOR)
query = "idMovie" + negate + " in (select idMovie from directorlinkmovie join actors on actors.idActor=directorlinkmovie.idDirector where actors.strActor" + parameter + ")";
else if (m_field == FIELD_ACTOR)
query = "idMovie" + negate + " in (select idMovie from actorlinkmovie join actors on actors.idActor=actorlinkmovie.idActor where actors.strActor" + parameter + ")";
else if (m_field == FIELD_WRITER)
query = "idMovie" + negate + " in (select idMovie from writerlinkmovie join actors on actors.idActor=writerlinkmovie.idWriter where actors.strActor" + parameter + ")";
else if (m_field == FIELD_STUDIO)
query = "idMovie" + negate + " in (select idMovie from studiolinkmovie join studio on studio.idStudio=studiolinkmovie.idStudio where studio.strStudio" + parameter + ")";
else if (m_field == FIELD_COUNTRY)
query = "idMovie" + negate + " in (select idMovie from countrylinkmovie join country on country.idCountry=countrylinkmovie.idCountry where country.strCountry" + parameter + ")";
else if (m_field == FIELD_HASTRAILER)
query = negate + GetDatabaseField(m_field, strType) + "!= ''";
else if (m_field == FIELD_LASTPLAYED && (m_operator == OPERATOR_LESS_THAN || m_operator == OPERATOR_BEFORE || m_operator == OPERATOR_NOT_IN_THE_LAST))
query = "lastPlayed is NULL or lastPlayed" + parameter;
else if (m_field == FIELD_INPROGRESS)
query = "idFile " + negate + " in (select idFile from bookmark where type = 1)";
else if (m_field == FIELD_SET)
query = "idMovie" + negate + " in (select idMovie from setlinkmovie join sets on sets.idSet=setlinkmovie.idSet where sets.strSet" + parameter + ")";
}
else if (strType == "musicvideos")
{
if (m_field == FIELD_GENRE)
query = "idMVideo" + negate + " in (select idMVideo from genrelinkmusicvideo join genre on genre.idGenre=genrelinkmusicvideo.idGenre where genre.strGenre" + parameter + ")";
else if (m_field == FIELD_ARTIST)
query = "idMVideo" + negate + " in (select idMVideo from artistlinkmusicvideo join actors on actors.idActor=artistlinkmusicvideo.idArtist where actors.strActor" + parameter + ")";
else if (m_field == FIELD_STUDIO)
query = "idMVideo" + negate + " in (select idMVideo from studiolinkmusicvideo join studio on studio.idStudio=studiolinkmusicvideo.idStudio where studio.strStudio" + parameter + ")";
else if (m_field == FIELD_DIRECTOR)
query = "idMVideo" + negate + " in (select idMVideo from directorlinkmusicvideo join actors on actors.idActor=directorlinkmusicvideo.idDirector where actors.strActor" + parameter + ")";
else if (m_field == FIELD_LASTPLAYED && (m_operator == OPERATOR_LESS_THAN || m_operator == OPERATOR_BEFORE || m_operator == OPERATOR_NOT_IN_THE_LAST))
query = "lastPlayed is NULL or lastPlayed" + parameter;
}
else if (strType == "tvshows")
{
if (m_field == FIELD_GENRE)
query = "idShow" + negate + " in (select idShow from genrelinktvshow join genre on genre.idGenre=genrelinktvshow.idGenre where genre.strGenre" + parameter + ")";
else if (m_field == FIELD_DIRECTOR)
query = "idShow" + negate + " in (select idShow from directorlinktvshow join actors on actors.idActor=directorlinktvshow.idDirector where actors.strActor" + parameter + ")";
else if (m_field == FIELD_ACTOR)
query = "idShow" + negate + " in (select idShow from actorlinktvshow join actors on actors.idActor=actorlinktvshow.idActor where actors.strActor" + parameter + ")";
else if (m_field == FIELD_STUDIO)
query = "idShow" + negate + " IN (SELECT idShow FROM tvshowview WHERE " + GetDatabaseField(m_field, strType) + parameter + ")";
else if (m_field == FIELD_MPAA)
query = "idShow" + negate + " IN (SELECT idShow FROM tvshowview WHERE " + GetDatabaseField(m_field, strType) + parameter + ")";
}
else if (strType == "episodes")
{
if (m_field == FIELD_GENRE)
query = "idShow" + negate + " in (select idShow from genrelinktvshow join genre on genre.idGenre=genrelinktvshow.idGenre where genre.strGenre" + parameter + ")";
else if (m_field == FIELD_DIRECTOR)
query = "idEpisode" + negate + " in (select idEpisode from directorlinkepisode join actors on actors.idActor=directorlinkepisode.idDirector where actors.strActor" + parameter + ")";
else if (m_field == FIELD_ACTOR)
query = "idEpisode" + negate + " in (select idEpisode from actorlinkepisode join actors on actors.idActor=actorlinkepisode.idActor where actors.strActor" + parameter + ")";
else if (m_field == FIELD_WRITER)
query = "idEpisode" + negate + " in (select idEpisode from writerlinkepisode join actors on actors.idActor=writerlinkepisode.idWriter where actors.strActor" + parameter + ")";
else if (m_field == FIELD_LASTPLAYED && (m_operator == OPERATOR_LESS_THAN || m_operator == OPERATOR_BEFORE || m_operator == OPERATOR_NOT_IN_THE_LAST))
query = "lastPlayed is NULL or lastPlayed" + parameter;
else if (m_field == FIELD_INPROGRESS)
query = "idFile " + negate + " in (select idFile from bookmark where type = 1)";
else if (m_field == FIELD_STUDIO)
query = "idEpisode" + negate + " IN (SELECT idEpisode FROM episodeview WHERE strStudio" + parameter + ")";
else if (m_field == FIELD_MPAA)
query = "idEpisode" + negate + " IN (SELECT idEpisode FROM episodeview WHERE mpaa" + parameter + ")";
}
if (m_field == FIELD_VIDEORESOLUTION)
query = "idFile" + negate + GetVideoResolutionQuery();
else if (m_field == FIELD_AUDIOCHANNELS)
query = "idFile" + negate + " in (select distinct idFile from streamdetails where iAudioChannels " + parameter + ")";
else if (m_field == FIELD_VIDEOCODEC)
query = "idFile" + negate + " in (select distinct idFile from streamdetails where strVideoCodec " + parameter + ")";
else if (m_field == FIELD_AUDIOCODEC)
query = "idFile" + negate + " in (select distinct idFile from streamdetails where strAudioCodec " + parameter + ")";
else if (m_field == FIELD_AUDIOLANGUAGE)
query = "idFile" + negate + " in (select distinct idFile from streamdetails where strAudioLanguage " + parameter + ")";
else if (m_field == FIELD_SUBTITLELANGUAGE)
query = "idFile" + negate + " in (select distinct idFile from streamdetails where strSubtitleLanguage " + parameter + ")";
else if (m_field == FIELD_VIDEOASPECT)
query = "idFile" + negate + " in (select distinct idFile from streamdetails where fVideoAspect " + parameter + ")";
else if (m_field == FIELD_PLAYLIST)
{ // playlist field - grab our playlist and add to our where clause
CStdString playlistFile = CSmartPlaylistDirectory::GetPlaylistByName(m_parameter, strType);
if (!playlistFile.IsEmpty())
{
CSmartPlaylist playlist;
playlist.Load(playlistFile);
CStdString playlistQuery;
// only playlists of same type will be part of the query
if (playlist.GetType().Equals(strType) || (playlist.GetType().Equals("mixed") && (strType == "songs" || strType == "musicvideos")) || playlist.GetType().IsEmpty())
{
playlist.SetType(strType);
playlistQuery = playlist.GetWhereClause(db, false);
}
if (m_operator == OPERATOR_DOES_NOT_EQUAL && playlist.GetType().Equals(strType))
query.Format("NOT (%s)", playlistQuery.c_str());
else if (m_operator == OPERATOR_EQUALS && playlist.GetType().Equals(strType))
query = playlistQuery;
}
}
if (m_field == FIELD_PLAYCOUNT && strType != "songs" && strType != "albums")
{ // playcount is stored as NULL or number in video db
if ((m_operator == OPERATOR_EQUALS && m_parameter == "0") ||
(m_operator == OPERATOR_DOES_NOT_EQUAL && m_parameter != "0") ||
(m_operator == OPERATOR_LESS_THAN))
{
CStdString field = GetDatabaseField(FIELD_PLAYCOUNT, strType);
query = field + " is NULL or " + field + parameter;
}
}
if (query.IsEmpty() && m_field != FIELD_NONE)
query = GetDatabaseField(m_field,strType) + negate + parameter;
// if we fail to get a dbfield, we empty query so it doesn't fail
if (query.Equals(negate + parameter))
query = "";
return query;
}
CStdString CSmartPlaylistRule::GetWhereClause(CDatabase &db, const CStdString& strType) const
{
SEARCH_OPERATOR op = m_operator;
if ((strType == "tvshows" || strType == "episodes") && m_field == FieldYear)
{ // special case for premiered which is a date rather than a year
// TODO: SMARTPLAYLISTS do we really need this, or should we just make this field the premiered date and request a date?
if (op == OPERATOR_EQUALS)
op = OPERATOR_CONTAINS;
else if (op == OPERATOR_DOES_NOT_EQUAL)
op = OPERATOR_DOES_NOT_CONTAIN;
}
CStdString operatorString, negate;
if (GetFieldType(m_field) == TEXTIN_FIELD)
{
if (op == OPERATOR_DOES_NOT_EQUAL)
negate = " NOT";
}
else
{
// the comparison piece
switch (op)
{
case OPERATOR_CONTAINS:
operatorString = " LIKE '%%%s%%'"; break;
case OPERATOR_DOES_NOT_CONTAIN:
negate = " NOT"; operatorString = " LIKE '%%%s%%'"; break;
case OPERATOR_EQUALS:
operatorString = " LIKE '%s'"; break;
case OPERATOR_DOES_NOT_EQUAL:
negate = " NOT"; operatorString = " LIKE '%s'"; break;
case OPERATOR_STARTS_WITH:
operatorString = " LIKE '%s%%'"; break;
case OPERATOR_ENDS_WITH:
operatorString = " LIKE '%%%s'"; break;
case OPERATOR_AFTER:
case OPERATOR_GREATER_THAN:
case OPERATOR_IN_THE_LAST:
operatorString = " > '%s'"; break;
case OPERATOR_BEFORE:
case OPERATOR_LESS_THAN:
case OPERATOR_NOT_IN_THE_LAST:
operatorString = " < '%s'"; break;
case OPERATOR_TRUE:
operatorString = " = 1"; break;
case OPERATOR_FALSE:
negate = " NOT "; operatorString = " = 0"; break;
default:
break;
}
}
// FieldInProgress does not have any values in m_parameter, it works on the operator
if (m_field == FieldInProgress && (strType == "movies" || strType == "episodes"))
return "idFile " + negate + " in (select idFile from bookmark where type = 1)";
// now the query parameter
CStdString wholeQuery;
for (vector<CStdString>::const_iterator it = m_parameter.begin(); it != m_parameter.end(); /* it++ is done further down */)
{
CStdString parameter;
if (GetFieldType(m_field) == TEXTIN_FIELD)
{
CStdStringArray split;
StringUtils::SplitString(*it, ",", split);
for (CStdStringArray::iterator itIn = split.begin(); itIn != split.end(); ++itIn)
{
if (!parameter.IsEmpty())
parameter += ",";
parameter += db.PrepareSQL("'%s'", (*itIn).Trim().c_str());
}
parameter = " IN (" + parameter + ")";
}
else
parameter = db.PrepareSQL(operatorString.c_str(), it->c_str());
if (GetFieldType(m_field) == DATE_FIELD)
{
if (m_operator == OPERATOR_IN_THE_LAST || m_operator == OPERATOR_NOT_IN_THE_LAST)
{ // translate time period
CDateTime date=CDateTime::GetCurrentDateTime();
CDateTimeSpan span;
span.SetFromPeriod(*it);
date-=span;
parameter = db.PrepareSQL(operatorString.c_str(), date.GetAsDBDate().c_str());
}
}
else if (m_field == FieldTime)
{ // translate time to seconds
CStdString seconds; seconds.Format("%i", StringUtils::TimeStringToSeconds(*it));
parameter = db.PrepareSQL(operatorString.c_str(), seconds.c_str());
}
CStdString query;
CStdString table;
if (strType == "songs")
{
table = "songview";
if (m_field == FieldGenre)
query = negate + " (" + GetField(FieldId, strType) + " IN (SELECT idSong FROM song_genre, genre WHERE song_genre.idGenre = genre.idGenre AND genre.strGenre" + parameter + ")";
else if (m_field == FieldArtist)
query = negate + " (" + GetField(FieldId, strType) + " IN (SELECT idSong FROM song_artist, artist WHERE song_artist.idArtist = artist.idArtist AND artist.strArtist" + parameter + ")";
else if (m_field == FieldAlbumArtist)
query = negate + " (" + table + ".idAlbum IN (SELECT idAlbum FROM album_artist, artist WHERE album_artist.idArtist = artist.idArtist AND artist.strArtist" + parameter + ")";
else if (m_field == FieldLastPlayed && (m_operator == OPERATOR_LESS_THAN || m_operator == OPERATOR_BEFORE || m_operator == OPERATOR_NOT_IN_THE_LAST))
query = "lastPlayed is NULL or lastPlayed" + parameter;
}
else if (strType == "albums")
{
table = "albumview";
if (m_field == FieldGenre)
query = negate + " (" + GetField(FieldId, strType) + " IN (SELECT song.idAlbum FROM song, song_genre, genre WHERE song.idSong = song_genre.idGenre AND song_genre.idGenre = genre.idGenre AND genre.strGenre" + parameter + ")";
else if (m_field == FieldArtist)
query = negate + " (" + GetField(FieldId, strType) + " IN (SELECT song.idAlbum FROM song, song_artist, artists WHERE song.idSong = song_artist.idSong AND song_artist.idArtist = artist.idArtist AND artist.strArtist" + parameter + ")";
else if (m_field == FieldAlbumArtist)
query = negate + " (" + GetField(FieldId, strType) + " IN (SELECT album_artist.idAlbum FROM album_artist, artist WHERE album_artist.idArtist = artist.idArtist AND artist.strArtist" + parameter + ")";
}
else if (strType == "movies")
{
table = "movieview";
if (m_field == FieldGenre)
query = GetField(FieldId, strType) + negate + " IN (SELECT idMovie FROM genrelinkmovie JOIN genre ON genre.idGenre=genrelinkmovie.idGenre WHERE genre.strGenre" + parameter + ")";
else if (m_field == FieldDirector)
query = GetField(FieldId, strType) + negate + " IN (SELECT idMovie FROM directorlinkmovie JOIN actors ON actors.idActor=directorlinkmovie.idDirector WHERE actors.strActor" + parameter + ")";
else if (m_field == FieldActor)
query = GetField(FieldId, strType) + negate + " IN (SELECT idMovie FROM actorlinkmovie JOIN actors ON actors.idActor=actorlinkmovie.idActor WHERE actors.strActor" + parameter + ")";
else if (m_field == FieldWriter)
query = GetField(FieldId, strType) + negate + " IN (SELECT idMovie FROM writerlinkmovie JOIN actors ON actors.idActor=writerlinkmovie.idWriter WHERE actors.strActor" + parameter + ")";
else if (m_field == FieldStudio)
query = GetField(FieldId, strType) + negate + " IN (SELECT idMovie FROM studiolinkmovie JOIN studio ON studio.idStudio=studiolinkmovie.idStudio WHERE studio.strStudio" + parameter + ")";
else if (m_field == FieldCountry)
query = GetField(FieldId, strType) + negate + " IN (SELECT idMovie FROM countrylinkmovie JOIN country ON country.idCountry=countrylinkmovie.idCountry WHERE country.strCountry" + parameter + ")";
else if (m_field == FieldTrailer)
query = negate + GetField(m_field, strType) + "!= ''";
else if ((m_field == FieldLastPlayed || m_field == FieldDateAdded) && (m_operator == OPERATOR_LESS_THAN || m_operator == OPERATOR_BEFORE || m_operator == OPERATOR_NOT_IN_THE_LAST))
query = GetField(m_field, strType) + " IS NULL OR " + GetField(m_field, strType) + parameter;
else if (m_field == FieldInProgress)
query = table + ".idFile " + negate + " IN (SELECT idFile FROM bookmark WHERE type = 1)";
else if (m_field == FieldSet)
query = GetField(FieldId, strType) + negate + " IN (SELECT idMovie FROM setlinkmovie JOIN sets ON sets.idSet=setlinkmovie.idSet WHERE sets.strSet" + parameter + ")";
else if (m_field == FieldTag)
query = GetField(FieldId, strType) + negate + " IN (SELECT idMedia FROM taglinks JOIN tag ON tag.idTag = taglinks.idTag WHERE tag.strTag" + parameter + " AND taglinks.media_type = 'movie')";
}
else if (strType == "musicvideos")
{
table = "musicvideoview";
if (m_field == FieldGenre)
query = GetField(FieldId, strType) + negate + " IN (SELECT idMVideo FROM genrelinkmusicvideo JOIN genre ON genre.idGenre=genrelinkmusicvideo.idGenre WHERE genre.strGenre" + parameter + ")";
else if (m_field == FieldArtist)
query = GetField(FieldId, strType) + negate + " IN (SELECT idMVideo FROM artistlinkmusicvideo JOIN actors ON actors.idActor=artistlinkmusicvideo.idArtist WHERE actors.strActor" + parameter + ")";
else if (m_field == FieldStudio)
query = GetField(FieldId, strType) + negate + " IN (SELECT idMVideo FROM studiolinkmusicvideo JOIN studio ON studio.idStudio=studiolinkmusicvideo.idStudio WHERE studio.strStudio" + parameter + ")";
else if (m_field == FieldDirector)
query = GetField(FieldId, strType) + negate + " IN (SELECT idMVideo FROM directorlinkmusicvideo JOIN actors ON actors.idActor=directorlinkmusicvideo.idDirector WHERE actors.strActor" + parameter + ")";
else if ((m_field == FieldLastPlayed || m_field == FieldDateAdded) && (m_operator == OPERATOR_LESS_THAN || m_operator == OPERATOR_BEFORE || m_operator == OPERATOR_NOT_IN_THE_LAST))
query = GetField(m_field, strType) + " IS NULL OR " + GetField(m_field, strType) + parameter;
}
else if (strType == "tvshows")
{
table = "tvshowview";
if (m_field == FieldGenre)
query = GetField(FieldId, strType) + negate + " IN (SELECT idShow FROM genrelinktvshow JOIN genre ON genre.idGenre=genrelinktvshow.idGenre WHERE genre.strGenre" + parameter + ")";
else if (m_field == FieldDirector)
query = GetField(FieldId, strType) + negate + " IN (SELECT idShow FROM directorlinktvshow JOIN actors ON actors.idActor=directorlinktvshow.idDirector WHERE actors.strActor" + parameter + ")";
else if (m_field == FieldActor)
query = GetField(FieldId, strType) + negate + " IN (SELECT idShow FROM actorlinktvshow JOIN actors ON actors.idActor=actorlinktvshow.idActor WHERE actors.strActor" + parameter + ")";
else if (m_field == FieldStudio)
query = GetField(FieldId, strType) + negate + " IN (SELECT idShow FROM tvshowview WHERE " + GetField(m_field, strType) + parameter + ")";
else if (m_field == FieldMPAA)
query = GetField(FieldId, strType) + negate + " IN (SELECT idShow FROM tvshowview WHERE " + GetField(m_field, strType) + parameter + ")";
else if (m_field == FieldDateAdded && (m_operator == OPERATOR_LESS_THAN || m_operator == OPERATOR_BEFORE || m_operator == OPERATOR_NOT_IN_THE_LAST))
query = GetField(FieldDateAdded, strType) + " IS NULL OR " + GetField(FieldDateAdded, strType) + parameter;
}
else if (strType == "episodes")
{
table = "episodeview";
if (m_field == FieldGenre)
query = table + ".idShow" + negate + " IN (SELECT idShow FROM genrelinktvshow JOIN genre ON genre.idGenre=genrelinktvshow.idGenre WHERE genre.strGenre" + parameter + ")";
else if (m_field == FieldDirector)
query = GetField(FieldId, strType) + negate + " IN (SELECT idEpisode FROM directorlinkepisode JOIN actors ON actors.idActor=directorlinkepisode.idDirector WHERE actors.strActor" + parameter + ")";
else if (m_field == FieldActor)
query = GetField(FieldId, strType) + negate + " IN (SELECT idEpisode FROM actorlinkepisode JOIN actors ON actors.idActor=actorlinkepisode.idActor WHERE actors.strActor" + parameter + ")";
else if (m_field == FieldWriter)
query = GetField(FieldId, strType) + negate + " IN (SELECT idEpisode FROM writerlinkepisode JOIN actors ON actors.idActor=writerlinkepisode.idWriter WHERE actors.strActor" + parameter + ")";
else if ((m_field == FieldLastPlayed || m_field == FieldDateAdded) && (m_operator == OPERATOR_LESS_THAN || m_operator == OPERATOR_BEFORE || m_operator == OPERATOR_NOT_IN_THE_LAST))
query = GetField(m_field, strType) + " IS NULL OR " + GetField(m_field, strType) + parameter;
else if (m_field == FieldInProgress)
query = table + ".idFile " + negate + " IN (SELECT idFile FROM bookmark WHERE type = 1)";
else if (m_field == FieldStudio)
query = GetField(FieldId, strType) + negate + " IN (SELECT idEpisode FROM episodeview WHERE strStudio" + parameter + ")";
else if (m_field == FieldMPAA)
query = GetField(FieldId, strType) + negate + " IN (SELECT idEpisode FROM episodeview WHERE mpaa" + parameter + ")";
}
if (m_field == FieldVideoResolution)
query = table + ".idFile" + negate + GetVideoResolutionQuery(*it);
else if (m_field == FieldAudioChannels)
query = table + ".idFile" + negate + " IN (SELECT DISTINCT idFile FROM streamdetails WHERE iAudioChannels " + parameter + ")";
else if (m_field == FieldVideoCodec)
query = table + ".idFile" + negate + " IN (SELECT DISTINCT idFile FROM streamdetails WHERE strVideoCodec " + parameter + ")";
else if (m_field == FieldAudioCodec)
query = table + ".idFile" + negate + " IN (SELECT DISTINCT idFile FROM streamdetails WHERE strAudioCodec " + parameter + ")";
else if (m_field == FieldAudioLanguage)
query = table + ".idFile" + negate + " IN (SELECT DISTINCT idFile FROM streamdetails WHERE strAudioLanguage " + parameter + ")";
else if (m_field == FieldSubtitleLanguage)
query = table + ".idFile" + negate + " IN (SELECT DISTINCT idFile FROM streamdetails WHERE strSubtitleLanguage " + parameter + ")";
else if (m_field == FieldVideoAspectRatio)
query = table + ".idFile" + negate + " IN (SELECT DISTINCT idFile FROM streamdetails WHERE fVideoAspect " + parameter + ")";
if (m_field == FieldPlaycount && strType != "songs" && strType != "albums")
{ // playcount IS stored as NULL OR number IN video db
if ((m_operator == OPERATOR_EQUALS && it->Equals("0")) ||
(m_operator == OPERATOR_DOES_NOT_EQUAL && !it->Equals("0")) ||
(m_operator == OPERATOR_LESS_THAN))
{
CStdString field = GetField(FieldPlaycount, strType);
query = field + " IS NULL OR " + field + parameter;
}
}
if (query.IsEmpty() && m_field != FieldNone)
query = GetField(m_field,strType) + negate + parameter;
it++;
if (query.Equals(negate + parameter))
query = "1";
query = "(" + query + ")";
if (it != m_parameter.end())
query += " OR ";
wholeQuery += query;
}
return wholeQuery;
}