public static logicalViewStep parseFromDB(string viewStep, bool hasSeriesBeforeIt)
{
logicalViewStep thisView = new logicalViewStep();
thisView.hasSeriesBeforeIt = hasSeriesBeforeIt;
string[] viewSteps = System.Text.RegularExpressions.Regex.Split(viewStep, s_intSeperator);
thisView.setType(viewSteps[0]);
thisView.addConditionsFromString(viewSteps[1]);
if (viewSteps[2].Length > 0)
{
string[] orderFields = System.Text.RegularExpressions.Regex.Split(viewSteps[2], ";");
thisView.inLineSpecials = orderFields[0] == "<Episode.EpisodeIndex>";
thisView.inLineSpecialsAsc = orderFields[0] != "desc";
for (int i = 0; i < orderFields.Length; i += 2)
{
if (thisView.Type != type.group)
{
DBTable table = null;
string tableField = string.Empty;
getTableFieldname(orderFields[i], out table, out tableField);
tableField = getQTableNameFromUnknownType(table, tableField);
// example of how the user can order by a different table
// needs to be enabled once definable views are ready
/*
* if (thisView.Type == type.season && table.GetType() != typeof(DBSeason))
* {
* Type lType = table.GetType();
* if (lType == typeof(DBOnlineSeries))
* tableField = "( select " + tableField + " from " + DBOnlineSeries.cTableName
+ " where " + DBOnlineSeries.Q(DBOnlineSeries.cID) + " = " + DBSeason.Q(DBSeason.cSeriesID) + ")";
+ }*/
//if (thisView.Type == type.episode && ( table.GetType() == typeof(DBEpisode) || table.GetType() == typeof(DBOnlineEpisode)))
//{
// // for perf reason a subquery is build, otherwise custom orders and the nessesary join really slow down sqllite!
// SQLCondition subQueryConditions = thisView.conds.Copy(); // have to have all conds too
// subQueryConditions.AddOrderItem(tableField, (orderFields[i + 1] == "asc" ? SQLCondition.orderType.Ascending : SQLCondition.orderType.Descending));
// if (viewSteps[3].Length > 0) // set the limit too
// {
// try
// {
// subQueryConditions.SetLimit(System.Convert.ToInt32(viewSteps[3]));
// }
// catch (Exception)
// {
// }
// }
// thisView.conds.AddSubQuery("compositeid", table, subQueryConditions, table.m_tableName + "." + DBEpisode.cCompositeID, SQLConditionType.In);
//}
thisView.conds.AddOrderItem(tableField, (orderFields[i + 1] == "asc" ? SQLCondition.orderType.Ascending : SQLCondition.orderType.Descending));
}
}
}
if (thisView.Type == type.group && thisView.groupedBy != null) // for groups always by their values (ignore user setting!)
{
if (thisView.groupedBy.table.GetType() == typeof(DBSeries))
{
if (!DBOption.GetOptions(DBOption.cShowHiddenItems))
{
thisView.conds.Add(new DBSeries(), DBSeries.cHidden, 1, SQLConditionType.NotEqual);
}
}
else if (thisView.groupedBy.table.GetType() == typeof(DBOnlineSeries))
{
if (!DBOption.GetOptions(DBOption.cShowHiddenItems))
{
thisView.conds.AddCustom(" not exists ( select * from " + DBSeries.cTableName + " where id = " + DBOnlineSeries.Q(DBOnlineSeries.cID) + " and " + DBSeries.Q(DBSeries.cHidden) + " = 1)");
}
}
else if (thisView.groupedBy.table.GetType() == typeof(DBSeason))
{
if (!DBOption.GetOptions(DBOption.cShowHiddenItems))
{
thisView.conds.Add(new DBSeason(), DBSeason.cHidden, 1, SQLConditionType.NotEqual);
}
}
thisView.conds.AddOrderItem(thisView.groupedBy.tableField, SQLCondition.orderType.Descending); // tablefield includes tablename itself!
}
try
{
if (viewSteps[3].Length > 0)
{
thisView.limitItems = System.Convert.ToInt32(viewSteps[3]);
thisView.conds.SetLimit(thisView.limitItems);
}
}
catch (Exception)
{
MPTVSeriesLog.Write("Cannot interpret limit in logicalview, limit was: " + viewSteps[3]);
}
return(thisView);
}
public List <string> getGroupItems(int stepIndex, string[] currentStepSelection) // in nested groups, eg. Networks-Genres-.. we also need selections
{
SQLCondition conditions = null;
MPTVSeriesLog.Write("View: GetGroupItems: Begin", MPTVSeriesLog.LogLevel.Debug);
if (stepIndex >= m_steps.Count)
{
return(null); // wrong index specified!!
}
addHierarchyConditions(ref stepIndex, ref currentStepSelection, ref conditions);
logicalViewStep step = m_steps[stepIndex];
List <string> items = new List <string>();
// to ensure we respect on the fly filter settings
if (DBOption.GetOptions(DBOption.cOnlyShowLocalFiles) && (typeof(DBOnlineEpisode) != step.groupedBy.table.GetType() && typeof(DBEpisode) != step.groupedBy.table.GetType()))
{
// not generic
SQLCondition fullSubCond = new SQLCondition();
fullSubCond.AddCustom(DBOnlineEpisode.Q(DBOnlineEpisode.cSeriesID), DBOnlineSeries.Q(DBOnlineSeries.cID), SQLConditionType.Equal);
conditions.AddCustom(" exists( " + DBEpisode.stdGetSQL(fullSubCond, false) + " )");
}
else if (DBOption.GetOptions(DBOption.cOnlyShowLocalFiles))
{
// has to be grouped by something episode
conditions.Add(new DBEpisode(), DBEpisode.cFilename, "", SQLConditionType.NotEqual);
}
string fieldName = step.groupedBy.rawFieldname;
string tableName = step.groupedBy.table.m_tableName;
string tableField = step.groupedBy.tableField;
string userEditField = tableField + DBTable.cUserEditPostFix;
string sql = string.Empty;
// check if the useredit column exists
if (DBTable.ColumnExists(tableName, fieldName + DBTable.cUserEditPostFix))
{
sql = "select distinct(" +
"case when (" + userEditField + " is null or " + userEditField + " = " + "'" + "'" + ") " +
"then " + tableField + " else " + userEditField + " " +
"end) as gnr, " +
"count(*) from " + tableName + conditions + " group by gnr" + step.conds.orderString;
}
else
{
sql = "select distinct " + tableField +
" , count(*) " +
" from " + tableName + conditions +
" group by " + tableField +
step.conds.orderString;
}
SQLite.NET.SQLiteResultSet results = DBTVSeries.Execute(sql);
MPTVSeriesLog.Write("View: GetGroupItems: SQL complete", MPTVSeriesLog.LogLevel.Debug);
if (results.Rows.Count > 0)
{
for (int index = 0; index < results.Rows.Count; index++)
{
string tmpItem = results.Rows[index].fields[0];
// assume we now have a list of all distinct ones
if (step.groupedBy.attempSplit)
{
// we want to try to split by "|" eg. for actors/genres
string[] split = DBOnlineEpisode.splitField(tmpItem);
foreach (string item in split)
{
if (item.Trim().Length == 0)
{
// display "Unknown" if field is empty"
items.Add(Translation.Unknown);
}
else
{
items.Add(item.Trim());
}
}
}
else
{
if (tmpItem.Trim().Length == 0)
{
items.Add(Translation.Unknown);
}
else
{
items.Add(tmpItem.Trim());
}
}
}
if (step.groupedBy.attempSplit)
{
// have to check for dups (because we split eg. Drama|Action so "Action" might be in twice
items = Helper.RemoveDuplicates(items);
}
// now we have to sort them again (Unknown/splitting above)
items.Sort();
if (step.groupedBy.attempSplit)
{
// and limit in memory here (again because those splits are hard to deal with)
if (step.limitItems > 0)
{
Helper.LimitList(ref items, step.limitItems);
}
}
}
MPTVSeriesLog.Write("View: GetGroupItems: Complete", MPTVSeriesLog.LogLevel.Debug);
return(items);
}
public void addHierarchyConditions(ref int stepIndex, ref string[] currentStepSelection, ref SQLCondition conditions)
{
logicalViewStep step = m_steps[stepIndex];
conditions = step.conds.Copy(); // important, don't change the steps themselves
// we need to add one additional condition to reflect the selection one hierarchy up
if (currentStepSelection != null && currentStepSelection.Length > 0 && stepIndex > 0)
{
switch (m_steps[stepIndex - 1].Type)
{
case logicalViewStep.type.group:
bool requiresSplit = false; // use sql 'like' for split fields
// selected group label
string selectedItem = currentStepSelection[0];
// we expect to get the selected group's label
// unknown really is "" so get all with null values here
if (selectedItem == Translation.Unknown)
{
selectedItem = string.Empty;
}
else
if (m_steps[stepIndex - 1].groupedBy.attempSplit)
{
requiresSplit = true;
}
string fieldName = m_steps[stepIndex - 1].groupedBy.rawFieldname;
string tableName = m_steps[stepIndex - 1].groupedBy.table.m_tableName;
string tableField = tableName + "." + fieldName;
string userEditField = tableField + DBTable.cUserEditPostFix;
string value = requiresSplit ? "like " + "'%" + selectedItem + "%'" : "= " + "'" + selectedItem + "'";
string sql = string.Empty;
// check if the useredit column exists
if (DBTable.ColumnExists(tableName, fieldName + DBTable.cUserEditPostFix))
{
sql = "(case when (" + userEditField + " is null or " + userEditField + " = " + "'" + "'" + ") " +
"then " + tableField + " else " + userEditField + " " +
"end) " + value;
}
else
{
sql = tableField + " " + value;
}
conditions.AddCustom(sql);
break;
case logicalViewStep.type.series:
// we expect to get the seriesID as stepSel
conditions.Add(new DBSeason(), DBSeason.cSeriesID, currentStepSelection[0], SQLConditionType.Equal);
if (DBOption.GetOptions(DBOption.cSortSpecialSeasonLast))
{
conditions.InsertOrderItem(DBSeason.cTableName + "." + DBSeason.cIndex + " = 0", SQLCondition.orderType.Ascending);
}
break;
case logicalViewStep.type.season:
// we expect to get the seriesID/seasonIndex as stepSel
// we want to query episodes using the CombinedSeason if Sort Order is "DVD"
// CombinedSeason gives us the DVD Season and if empty will give us the Aired Season
DBSeries series = Helper.getCorrespondingSeries(int.Parse(currentStepSelection[0]));
bool SortByDVD = series[DBOnlineSeries.cEpisodeSortOrder] == "DVD";
string seasonIndex = SortByDVD ? DBOnlineEpisode.cCombinedSeason : DBOnlineEpisode.cSeasonIndex;
conditions.Add(new DBOnlineEpisode(), DBOnlineEpisode.cSeriesID, currentStepSelection[0], SQLConditionType.Equal);
conditions.beginGroup();
conditions.Add(new DBOnlineEpisode(), seasonIndex, currentStepSelection[1], SQLConditionType.Equal);
if (DBOption.GetOptions(DBOption.cSortSpecials) && !SortByDVD)
{
conditions.nextIsOr = true;
conditions.Add(new DBOnlineEpisode(), DBOnlineEpisode.cAirsBeforeSeason, currentStepSelection[1], SQLConditionType.Equal);
conditions.Add(new DBOnlineEpisode(), DBOnlineEpisode.cAirsAfterSeason, currentStepSelection[1], SQLConditionType.Equal);
conditions.nextIsOr = false;
}
conditions.endGroup();
break;
}
}
}
public static logicalViewStep parseFromDB(string viewStep, bool hasSeriesBeforeIt)
{
logicalViewStep thisView = new logicalViewStep();
thisView.hasSeriesBeforeIt = hasSeriesBeforeIt;
string[] viewSteps = System.Text.RegularExpressions.Regex.Split(viewStep, s_intSeperator);
thisView.setType(viewSteps[0]);
thisView.addConditionsFromString(viewSteps[1]);
if (viewSteps[2].Length > 0)
{
string[] orderFields = System.Text.RegularExpressions.Regex.Split(viewSteps[2], ";");
thisView.inLineSpecials = orderFields[0] == "<Episode.EpisodeIndex>";
thisView.inLineSpecialsAsc = orderFields[0] != "desc";
for (int i = 0; i < orderFields.Length; i += 2)
{
if(thisView.Type != type.group)
{
DBTable table = null;
string tableField = string.Empty;
getTableFieldname(orderFields[i], out table, out tableField);
tableField = getQTableNameFromUnknownType(table, tableField);
// example of how the user can order by a different table
// needs to be enabled once definable views are ready
/*
if (thisView.Type == type.season && table.GetType() != typeof(DBSeason))
{
Type lType = table.GetType();
if (lType == typeof(DBOnlineSeries))
tableField = "( select " + tableField + " from " + DBOnlineSeries.cTableName
+ " where " + DBOnlineSeries.Q(DBOnlineSeries.cID) + " = " + DBSeason.Q(DBSeason.cSeriesID) + ")";
}*/
//if (thisView.Type == type.episode && ( table.GetType() == typeof(DBEpisode) || table.GetType() == typeof(DBOnlineEpisode)))
//{
// // for perf reason a subquery is build, otherwise custom orders and the nessesary join really slow down sqllite!
// SQLCondition subQueryConditions = thisView.conds.Copy(); // have to have all conds too
// subQueryConditions.AddOrderItem(tableField, (orderFields[i + 1] == "asc" ? SQLCondition.orderType.Ascending : SQLCondition.orderType.Descending));
// if (viewSteps[3].Length > 0) // set the limit too
// {
// try
// {
// subQueryConditions.SetLimit(System.Convert.ToInt32(viewSteps[3]));
// }
// catch (Exception)
// {
// }
// }
// thisView.conds.AddSubQuery("compositeid", table, subQueryConditions, table.m_tableName + "." + DBEpisode.cCompositeID, SQLConditionType.In);
//}
thisView.conds.AddOrderItem(tableField, (orderFields[i + 1] == "asc" ? SQLCondition.orderType.Ascending : SQLCondition.orderType.Descending));
}
}
}
if (thisView.Type == type.group && thisView.groupedBy != null) // for groups always by their values (ignore user setting!)
{
if (thisView.groupedBy.table.GetType() == typeof(DBSeries))
{
if (!DBOption.GetOptions(DBOption.cShowHiddenItems))
thisView.conds.Add(new DBSeries(), DBSeries.cHidden, 1, SQLConditionType.NotEqual);
}
else if (thisView.groupedBy.table.GetType() == typeof(DBOnlineSeries))
{
if (!DBOption.GetOptions(DBOption.cShowHiddenItems))
thisView.conds.AddCustom(" not exists ( select * from " + DBSeries.cTableName + " where id = " + DBOnlineSeries.Q(DBOnlineSeries.cID) + " and " + DBSeries.Q(DBSeries.cHidden) + " = 1)");
}
else if (thisView.groupedBy.table.GetType() == typeof(DBSeason))
{
if (!DBOption.GetOptions(DBOption.cShowHiddenItems))
thisView.conds.Add(new DBSeason(), DBSeason.cHidden, 1, SQLConditionType.NotEqual);
}
thisView.conds.AddOrderItem(thisView.groupedBy.tableField, SQLCondition.orderType.Descending); // tablefield includes tablename itself!
}
try
{
if (viewSteps[3].Length > 0)
{
thisView.limitItems = System.Convert.ToInt32(viewSteps[3]);
thisView.conds.SetLimit(thisView.limitItems);
}
}
catch (Exception)
{
MPTVSeriesLog.Write("Cannot interpret limit in logicalview, limit was: " + viewSteps[3]);
}
return thisView;
}