private void BuildWhere(FilterDefinition filter, ref string whereClause)
{
if (filter.WhereValue != "*")
{
if (whereClause != "")
{
whereClause += " and ";
}
whereClause += String.Format(" {0}='{1}'", GetField(filter.Where), filter.WhereValue);
}
}
private void BuildOrder(FilterDefinition filter, ref string orderClause)
{
orderClause = " order by " + GetField(filter.Where) + " ";
if (!filter.SortAscending)
{
orderClause += "desc";
}
else
{
orderClause += "asc";
}
if (filter.Limit > 0)
{
orderClause += String.Format(" Limit {0}", filter.Limit);
}
}
private void BuildSelect(FilterDefinition filter, ref string whereClause, ref string fromClause)
{
if (whereClause != "")
{
whereClause += " and ";
}
whereClause += String.Format(" {0}='{1}'", GetFieldId(filter.Where), filter.SelectedValue);
bool useMovieInfoTable = false;
bool useAlbumTable = false;
bool useActorsTable = false;
bool useGenreTable = false;
string table = GetTable(filter.Where, ref useMovieInfoTable, ref useAlbumTable, ref useActorsTable,
ref useGenreTable);
if (useGenreTable)
{
fromClause += String.Format(",genre,genrelinkmovie");
whereClause += " and genre.idGenre=genrelinkMovie.idGenre and genrelinkMovie.idMovie=movieinfo.idMovie";
}
if (useActorsTable)
{
fromClause += String.Format(",actors castactors,actorlinkmovie");
whereClause += " and castactors.idActor=actorlinkmovie.idActor and actorlinkmovie.idMovie=movieinfo.idMovie";
}
}
private void BuildRestriction(FilterDefinition filter, ref string whereClause)
{
if (filter.SqlOperator != string.Empty && filter.Restriction != string.Empty)
{
if (whereClause != "")
{
whereClause += " and ";
}
string restriction = filter.Restriction;
restriction = restriction.Replace("*", "%");
DatabaseUtility.RemoveInvalidChars(ref restriction);
if (filter.SqlOperator == "=")
{
bool isascii = false;
for (int x = 0; x < restriction.Length; ++x)
{
if (!Char.IsDigit(restriction[x]))
{
isascii = true;
break;
}
}
if (isascii)
{
filter.SqlOperator = "like";
}
}
whereClause += String.Format(" {0} {1} '{2}'", GetFieldName(filter.Where), filter.SqlOperator, restriction);
}
}
private void BuildOrder(FilterDefinition filter, ref string orderClause)
{
string[] sortFields = GetSortField(filter).Split('|');
orderClause = " order by ";
for (int i = 0; i < sortFields.Length; i++)
{
if (i > 0)
{
orderClause += ", ";
}
orderClause += sortFields[i];
if (!filter.SortAscending)
{
orderClause += " desc";
}
else
{
orderClause += " asc";
}
}
if (filter.Limit > 0)
{
orderClause += String.Format(" Limit {0}", filter.Limit);
}
}
private static string GetSortField(FilterDefinition filter)
{
// Don't allow anything else but the fieldnames itself on Multiple Fields
if (filter.Where == "artist" || filter.Where == "albumartist" || filter.Where == "genre" ||
filter.Where == "composer")
{
return GetField(filter.Where);
}
if (filter.DefaultSort == "Date")
{
return GetField("date");
}
if (filter.DefaultSort == "Year")
{
return GetField("year");
}
if (filter.DefaultSort == "Name")
{
return GetField("title");
}
if (filter.DefaultSort == "Duration")
{
return "iDuration";
}
if (filter.DefaultSort == "disc#")
{
return "iDisc";
}
if (filter.DefaultSort == "Track")
{
return "iDisc|iTrack"; // We need to sort on Discid + Track
}
return GetField(filter.Where);
}
private void BuildWhere(FilterDefinition filter, ref string whereClause)
{
if (filter.WhereValue != "*")
{
if (whereClause != "")
{
whereClause += " and ";
}
string selectedValue = filter.WhereValue;
DatabaseUtility.RemoveInvalidChars(ref selectedValue);
// Do we have a Multiplevalues field, then we need compare with like
if (IsMultipleValueField(GetField(filter.Where)))
{
whereClause += String.Format(" {0} like '%| {1} |%'", GetField(filter.Where), selectedValue);
}
else
{
// use like for case insensitivity
whereClause += String.Format(" {0} like '{1}'", GetField(filter.Where), selectedValue);
}
}
}
private void BuildSelect(FilterDefinition filter, ref string whereClause, int filterLevel)
{
if (filter.SqlOperator == "group")
{
// Don't need to include the grouping value, when it was on the first level
if (CurrentLevel > 1 && filterLevel == 0)
{
return;
}
if (whereClause != "")
{
whereClause += " and ";
}
restrictionLength += Convert.ToInt16(filter.Restriction);
// muliple value fields are stored in one database field in tracks
// table but on different rows in other tables
if (IsMultipleValueField(GetField(filter.Where)))
{
bool usingTracksTable = true;
if (GetTable(CurrentLevelWhere) != "tracks")
{
usingTracksTable = false;
}
if (!usingTracksTable)
{
// current level is not using tracks table so check whether
// any filters above this one are using a different table
// and if so data will be taken from tracks table
FilterDefinition defRoot = (FilterDefinition)currentView.Filters[0];
string table = GetTable(defRoot.Where);
for (int i = CurrentLevel; i > -1; i--)
{
FilterDefinition prevFilter = (FilterDefinition)currentView.Filters[i];
if (GetTable(prevFilter.Where) != table)
{
usingTracksTable = true;
break;
}
}
}
// now we know if we are using the tracks table or not we can
// figure out how to format multiple value fields
if (usingTracksTable)
{
if (filter.SelectedValue == "#")
{
// need a special case here were user selects # from grouped menu
// as multiple values can be stored in the same field
whereClause += String.Format(" exists ( " +
" select 0 from {0} " +
" where {1} < 'A' " +
" and tracks.{1} like '%| '||{0}.{1}||' |%' " +
" ) ", GetTable(filter.Where), GetField(filter.Where));
}
else
{
whereClause += String.Format(" ({0} like '| {1}%' or '| {2}%')", GetField(filter.Where),
filter.SelectedValue.PadRight(restrictionLength), filter.SelectedValue);
}
}
else
{
if (filter.SelectedValue == "#")
{
whereClause += String.Format(" {0} < 'A'", GetField(filter.Where));
}
else
{
whereClause += String.Format(" ({0} like '{1}%' or '{2}%')", GetField(filter.Where),
filter.SelectedValue.PadRight(restrictionLength), filter.SelectedValue);
}
}
}
else
{
// we are looking for fields which do not contain multiple values
if (filter.SelectedValue == "#")
{
// deal with non standard characters
whereClause += String.Format(" {0} < 'A'", GetField(filter.Where));
}
else
{
whereClause += String.Format(" ({0} like '{1}%' or '{2}%')", GetField(filter.Where),
filter.SelectedValue.PadRight(restrictionLength), filter.SelectedValue);
}
}
}
else
{
if (whereClause != "")
{
whereClause += " and ";
}
string selectedValue = filter.SelectedValue;
DatabaseUtility.RemoveInvalidChars(ref selectedValue);
// we don't store "unknown" into the datbase, so let's correct empty values
if (selectedValue == "unknown")
{
selectedValue = "";
}
// If we have a multiple values field then we need to compare with like
if (IsMultipleValueField(GetField(filter.Where)))
{
whereClause += String.Format("{0} like '%| {1} |%'", GetField(filter.Where), selectedValue);
}
else
{
// use like for case insensitivity
whereClause += String.Format("{0} like '{1}'", GetField(filter.Where), selectedValue);
}
}
}
private void BuildOrder(FilterDefinition filter, ref string orderClause)
{
orderClause = " ORDER BY " + GetField(filter.Where) + " ";
if (!filter.SortAscending)
{
orderClause += "DESC";
}
else
{
orderClause += "ASC";
}
if (filter.Limit > 0)
{
orderClause += String.Format(" LIMIT {0}", filter.Limit);
}
}
private void BuildSelect(FilterDefinition filter, ref string whereClause, ref string fromClause)
{
if (whereClause != "")
{
whereClause += " AND ";
}
string cleanValue = DatabaseUtility.RemoveInvalidChars(filter.SelectedValue);
if (filter.Where == "actorindex" || filter.Where == "directorindex" || filter.Where == "titleindex")
{
if (cleanValue == "#")
{
string nWordChar = VideoDatabase.NonwordCharacters();
if (filter.Where == "actorindex" || filter.Where == "directorindex")
{
whereClause += @" SUBSTR(strActor,1,1) IN (" + nWordChar +")";
}
else
{
whereClause += @" SUBSTR(strTitle,1,1) IN (" + nWordChar +")";
}
}
else
{
whereClause += String.Format(" {0}='{1}'", GetFieldId(filter.Where), cleanValue);
}
}
else
{
whereClause += String.Format(" {0}='{1}'", GetFieldId(filter.Where), cleanValue);
}
bool useMovieInfoTable = false;
bool useAlbumTable = false;
bool useActorsTable = false;
bool useGenreTable = false;
bool useUserGroupsTable = false;
bool useMovieCollectionTable = false;
string table = GetTable(filter.Where, ref useMovieInfoTable, ref useAlbumTable, ref useActorsTable,
ref useGenreTable, ref useUserGroupsTable, ref useMovieCollectionTable);
if (useGenreTable)
{
fromClause += String.Format(",genre,genrelinkmovie");
whereClause += " AND genre.idGenre=genrelinkMovie.idGenre AND genrelinkMovie.idMovie=movieinfo.idMovie";
return;
}
if (useUserGroupsTable)
{
fromClause += String.Format(",usergroup,usergrouplinkmovie");
whereClause += " AND usergroup.idGroup=usergrouplinkmovie.idGroup AND usergrouplinkMovie.idMovie=movieinfo.idMovie";
return;
}
if (useMovieCollectionTable)
{
fromClause += String.Format(",moviecollection,moviecollectionlinkmovie");
whereClause += " AND moviecollection.idCollection=moviecollectionlinkmovie.idCollection AND moviecollectionlinkmovie.idMovie=movieinfo.idMovie";
return;
}
if (useActorsTable)
{
if (CurrentLevel == MaxLevels - 1 && filter.Where == "actor")
{
fromClause += String.Format(",actors ,actorlinkmovie");
whereClause += " AND actors.idActor=actorlinkmovie.idActor AND actorlinkmovie.idMovie=movieinfo.idMovie";
}
return;
}
}
/// <summary>
/// Store the Grid Values in the View
/// </summary>
private void StoreGridInView()
{
if (updating)
{
return;
}
if (dataGrid.DataSource == null)
{
return;
}
if (currentView == null)
{
return;
}
settingsChanged = true;
ViewDefinition view = currentView;
DataTable dt = dataGrid.DataSource as DataTable;
view.Filters.Clear();
foreach (DataRow row in dt.Rows)
{
FilterDefinition def = new FilterDefinition();
def.Where = row[0] as string;
if (def.Where == string.Empty)
{
continue;
}
def.SqlOperator = row[1].ToString();
def.Restriction = row[2].ToString();
try
{
def.Limit = Int32.Parse(row[3].ToString());
}
catch (Exception)
{
def.Limit = -1;
}
def.DefaultView = row[4].ToString();
def.DefaultSort = row[5].ToString();
def.SortAscending = (bool)row[6];
def.SkipLevel = (bool)row[7];
view.Filters.Add(def);
}
}
