public static string stdGetSQL(SQLCondition conditions, bool selectFull, bool includeStdCond)
{
string field;
if (selectFull)
{
SQLWhat what = new SQLWhat(new DBOnlineSeries());
what.AddWhat(new DBSeries());
field = what;
}
else
{
field = DBOnlineSeries.Q(DBOnlineSeries.cID) + " from " + DBOnlineSeries.cTableName;
}
if (includeStdCond)
{
conditions.AddCustom(stdConditions.ConditionsSQLString);
}
string conds = conditions;
string orderBy = string.Empty;
if (selectFull)
{
bool bUseSortName = DBOption.GetOptions(DBOption.cUseSortName);
orderBy = conditions.customOrderStringIsSet
? conditions.orderString
: " order by " + (bUseSortName?"upper(" + DBOnlineSeries.Q(DBOnlineSeries.cSortName) + "),":"") + "upper(" + DBOnlineSeries.Q(DBOnlineSeries.cPrettyName) + ")";
}
return("select " + field + " left join " + cTableName + " on " + DBSeries.Q(cID) + "==" + DBOnlineSeries.Q(cID)
+ conds
+ orderBy
+ conditions.limitString);
}
public static string stdGetSQL(SQLCondition condition, bool selectFull, bool includeStdCond)
{
string orderBy = !condition.customOrderStringIsSet
? " order by " + Q(cIndex)
: condition.orderString;
if (includeStdCond)
{
condition.AddCustom(stdConditions.ConditionsSQLString);
if (!Settings.isConfig)
{
SQLCondition fullSubCond = new SQLCondition();
//fullSubCond.AddCustom(DBOnlineEpisode.Q(DBOnlineEpisode.cSeriesID), DBSeason.Q(DBSeason.cSeriesID), SQLConditionType.Equal);
//fullSubCond.AddCustom(DBOnlineEpisode.Q(DBOnlineEpisode.cSeasonIndex), DBSeason.Q(DBSeason.cIndex), SQLConditionType.Equal);
//condition.AddCustom(" season.seasonindex in ( " + DBEpisode.stdGetSQL(fullSubCond, false, true, DBOnlineEpisode.Q(DBOnlineEpisode.cSeriesID)) + " )");
string join = null;
if (DBOption.GetOptions(DBOption.cOnlyShowLocalFiles))
{
fullSubCond = DBEpisode.stdConditions;
condition.AddCustom(fullSubCond.ConditionsSQLString.Replace("where", "and"));
join = " left join local_episodes on season.seriesid = local_episodes.seriesid " +
" and season.seasonindex = local_episodes.seasonindex left join online_episodes on local_episodes.compositeid = online_episodes.compositeid ";
}
else
{
join = " left join online_episodes on season.seriesid = online_episodes.seriesid " +
" and season.seasonindex = online_episodes.seasonindex";
}
return("select " + new SQLWhat(new DBSeason()) +
join +
condition + " group by season.id " + orderBy + condition.limitString);
}
}
if (selectFull)
{
return("select " + new SQLWhat(new DBSeason()) + condition + orderBy + condition.limitString);
}
else
{
return("select " + DBSeason.cID + " from " + DBSeason.cTableName + " " + condition + orderBy + condition.limitString);
}
}
public static List <DBSeries> Get(SQLCondition conditions, bool onlyWithUnwatchedEpisodes, bool includeStdCond)
{
if (onlyWithUnwatchedEpisodes)
{
conditions.AddCustom(@"(
select count(*) from online_episodes
where
seriesID = local_series.ID
and watched = '0'
) > 0");
}
String sqlQuery = stdGetSQL(conditions, true, includeStdCond);
return(Get(sqlQuery));
}
public void addSQLCondition(string what, string type, string condition)
{
if ((!what.Contains("<") || !what.Contains(".")) && !what.Contains("custom:"))
{
return;
}
SQLConditionType condtype;
switch (type)
{
case "=":
condtype = SQLConditionType.Equal;
break;
case ">":
condtype = SQLConditionType.GreaterThan;
break;
case ">=":
condtype = SQLConditionType.GreaterEqualThan;
break;
case "<":
condtype = SQLConditionType.LessThan;
break;
case "<=":
condtype = SQLConditionType.LessEqualThan;
break;
case "!=":
condtype = SQLConditionType.NotEqual;
break;
case "like":
condtype = SQLConditionType.Like;
break;
default:
condtype = SQLConditionType.Equal;
break;
}
DBTable table = null;
string tableField = string.Empty;
getTableFieldname(what, out table, out tableField);
Type lType = table.GetType();
SQLCondition fullSubCond = new SQLCondition();
if (logicalViewStep.type.series == Type && (lType != typeof(DBSeries) && lType != typeof(DBOnlineSeries)))
{
if (lType == typeof(DBSeason))
{
fullSubCond.AddCustom(DBSeason.Q(DBSeason.cSeriesID), DBOnlineSeries.Q(DBOnlineSeries.cID), SQLConditionType.Equal);
fullSubCond.AddCustom(DBSeason.Q(tableField), condition, condtype, true);
conds.AddCustom(" exists( " + DBSeason.stdGetSQL(fullSubCond, false) + " )");
}
else if (lType == typeof(DBOnlineEpisode))
{
fullSubCond.AddCustom(DBOnlineEpisode.Q(tableField), condition, condtype, true);
conds.AddCustom(" online_series.id in ( " + DBEpisode.stdGetSQL(fullSubCond, false, true, DBOnlineEpisode.Q(DBOnlineEpisode.cSeriesID)) + " )");
}
else if (lType == typeof(DBEpisode))
{
fullSubCond.AddCustom(DBEpisode.Q(tableField), condition, condtype, true);
conds.AddCustom(" online_series.id in ( " + DBEpisode.stdGetSQL(fullSubCond, false, true, DBOnlineEpisode.Q(DBOnlineEpisode.cSeriesID)) + " )");
}
}
else if (logicalViewStep.type.season == Type && lType != typeof(DBSeason))
{
if (lType == typeof(DBOnlineSeries) || lType == typeof(DBSeries))
{
fullSubCond.AddCustom(DBOnlineSeries.Q(DBOnlineSeries.cID), DBSeason.Q(DBSeason.cSeriesID), SQLConditionType.Equal);
fullSubCond.AddCustom(DBOnlineSeries.Q(tableField), condition, condtype, true);
conds.AddCustom(" exists( " + DBSeries.stdGetSQL(fullSubCond, false) + " )");
}
else if (lType == typeof(DBOnlineEpisode))
{
// we rely on the join in dbseason for this (much, much faster)
conds.AddCustom(DBOnlineEpisode.Q(tableField), condition, condtype, true);
}
else if (lType == typeof(DBEpisode))
{
fullSubCond.AddCustom(DBOnlineEpisode.Q(DBOnlineEpisode.cSeriesID), DBSeason.Q(DBSeason.cSeriesID), SQLConditionType.Equal);
fullSubCond.AddCustom(DBOnlineEpisode.Q(DBOnlineEpisode.cSeasonIndex), DBSeason.Q(DBSeason.cIndex), SQLConditionType.Equal);
fullSubCond.AddCustom(DBEpisode.Q(tableField), condition, condtype);
conds.AddCustom(" exists( " + DBEpisode.stdGetSQL(fullSubCond, false) + " )");
}
}
else if (logicalViewStep.type.episode == Type && (lType != typeof(DBEpisode) && lType != typeof(DBOnlineEpisode)))
{
if (lType == typeof(DBOnlineSeries) || lType == typeof(DBSeries))
{
fullSubCond.AddCustom(DBOnlineSeries.Q(DBOnlineSeries.cID), DBOnlineEpisode.Q(DBOnlineEpisode.cSeriesID), SQLConditionType.Equal);
fullSubCond.AddCustom(DBOnlineSeries.Q(tableField), condition, condtype, true);
conds.AddCustom(" exists( " + DBSeries.stdGetSQL(fullSubCond, false) + " )");
}
if (lType == typeof(DBSeason))
{
fullSubCond.AddCustom(DBSeason.Q(DBSeason.cSeriesID), DBOnlineEpisode.Q(DBOnlineEpisode.cSeriesID), SQLConditionType.Equal);
fullSubCond.AddCustom(DBSeason.Q(DBSeason.cIndex), DBOnlineEpisode.Q(DBOnlineEpisode.cSeasonIndex), SQLConditionType.Equal);
fullSubCond.AddCustom(DBSeason.Q(tableField), condition, condtype, true);
conds.AddCustom(" exists( " + DBSeason.stdGetSQL(fullSubCond, false) + " )");
}
}
else
{
// condition is on current table itself
conds.Add(table, tableField, condition.Trim(), condtype);
}
}
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 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 addSQLCondition(string what, string type, string condition)
{
if ((!what.Contains("<") || !what.Contains(".")) && !what.Contains("custom:")) return;
SQLConditionType condtype;
switch (type)
{
case "=":
condtype = SQLConditionType.Equal;
break;
case ">":
condtype = SQLConditionType.GreaterThan;
break;
case ">=":
condtype = SQLConditionType.GreaterEqualThan;
break;
case "<":
condtype = SQLConditionType.LessThan;
break;
case "<=":
condtype = SQLConditionType.LessEqualThan;
break;
case "!=":
condtype = SQLConditionType.NotEqual;
break;
case "like":
condtype = SQLConditionType.Like;
break;
default:
condtype = SQLConditionType.Equal;
break;
}
DBTable table = null;
string tableField = string.Empty;
getTableFieldname(what, out table, out tableField);
Type lType = table.GetType();
SQLCondition fullSubCond = new SQLCondition();
if (logicalViewStep.type.series == Type && (lType != typeof(DBSeries) && lType != typeof(DBOnlineSeries)))
{
if (lType == typeof(DBSeason))
{
fullSubCond.AddCustom(DBSeason.Q(DBSeason.cSeriesID), DBOnlineSeries.Q(DBOnlineSeries.cID), SQLConditionType.Equal);
fullSubCond.AddCustom(DBSeason.Q(tableField), condition, condtype, true);
conds.AddCustom(" exists( " + DBSeason.stdGetSQL(fullSubCond, false) + " )");
}
else if (lType == typeof(DBOnlineEpisode))
{
fullSubCond.AddCustom(DBOnlineEpisode.Q(tableField), condition, condtype, true);
conds.AddCustom(" online_series.id in ( " + DBEpisode.stdGetSQL(fullSubCond, false, true, DBOnlineEpisode.Q(DBOnlineEpisode.cSeriesID)) + " )");
}
else if (lType == typeof(DBEpisode)) {
fullSubCond.AddCustom(DBEpisode.Q(tableField), condition, condtype, true);
conds.AddCustom(" online_series.id in ( " + DBEpisode.stdGetSQL(fullSubCond, false, true, DBOnlineEpisode.Q(DBOnlineEpisode.cSeriesID)) + " )");
}
}
else if (logicalViewStep.type.season == Type && lType != typeof(DBSeason))
{
if (lType == typeof(DBOnlineSeries) || lType == typeof(DBSeries))
{
fullSubCond.AddCustom(DBOnlineSeries.Q(DBOnlineSeries.cID), DBSeason.Q(DBSeason.cSeriesID), SQLConditionType.Equal);
fullSubCond.AddCustom(DBOnlineSeries.Q(tableField), condition, condtype, true);
conds.AddCustom(" exists( " + DBSeries.stdGetSQL(fullSubCond, false) + " )");
}
else if (lType == typeof(DBOnlineEpisode))
{
// we rely on the join in dbseason for this (much, much faster)
conds.AddCustom(DBOnlineEpisode.Q(tableField), condition, condtype, true);
}
else if (lType == typeof(DBEpisode)) {
fullSubCond.AddCustom(DBOnlineEpisode.Q(DBOnlineEpisode.cSeriesID), DBSeason.Q(DBSeason.cSeriesID), SQLConditionType.Equal);
fullSubCond.AddCustom(DBOnlineEpisode.Q(DBOnlineEpisode.cSeasonIndex), DBSeason.Q(DBSeason.cIndex), SQLConditionType.Equal);
fullSubCond.AddCustom(DBEpisode.Q(tableField), condition, condtype);
conds.AddCustom(" exists( " + DBEpisode.stdGetSQL(fullSubCond, false) + " )");
}
}
else if (logicalViewStep.type.episode == Type && (lType != typeof(DBEpisode) && lType != typeof(DBOnlineEpisode)))
{
if (lType == typeof(DBOnlineSeries) || lType == typeof(DBSeries))
{
fullSubCond.AddCustom(DBOnlineSeries.Q(DBOnlineSeries.cID), DBOnlineEpisode.Q(DBOnlineEpisode.cSeriesID), SQLConditionType.Equal);
fullSubCond.AddCustom(DBOnlineSeries.Q(tableField), condition, condtype, true);
conds.AddCustom(" exists( " + DBSeries.stdGetSQL(fullSubCond, false) + " )");
}
if (lType == typeof(DBSeason))
{
fullSubCond.AddCustom(DBSeason.Q(DBSeason.cSeriesID), DBOnlineEpisode.Q(DBOnlineEpisode.cSeriesID), SQLConditionType.Equal);
fullSubCond.AddCustom(DBSeason.Q(DBSeason.cIndex), DBOnlineEpisode.Q(DBOnlineEpisode.cSeasonIndex), SQLConditionType.Equal);
fullSubCond.AddCustom(DBSeason.Q(tableField), condition, condtype, true);
conds.AddCustom(" exists( " + DBSeason.stdGetSQL(fullSubCond, false) + " )");
}
}
else
{
// condition is on current table itself
conds.Add(table, tableField, condition.Trim(), condtype);
}
}
// in nested groups, eg. Networks-Genres-.. we also need selections
public List<string> getGroupItems(int stepIndex, string[] currentStepSelection)
{
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.cView_Episode_OnlyShowLocalFiles) && (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.cView_Episode_OnlyShowLocalFiles))
{
// 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;
}
}
}
private void Group_OnItemSelected(GUIListItem item)
{
m_SelectedSeries = null;
m_SelectedSeason = null;
m_SelectedEpisode = null;
if (item == null) return;
setNewListLevelOfCurrView(m_CurrViewStep);
// let's try to give the user a bit more information
string groupedBy = m_CurrLView.groupedInfo(m_CurrViewStep);
if (groupedBy.Contains("<Ser"))
{
int count = 0;
string seriesNames = string.Empty;
SQLCondition cond = new SQLCondition();
cond.AddOrderItem(DBOnlineSeries.Q(DBOnlineSeries.cPrettyName), SQLCondition.orderType.Ascending);
cond.SetLimit(20);
bool requiresSplit = false; // use sql 'like' for split fields
// selected group label
string selectedItem = this.m_Facade.SelectedListItem.Label.ToString();
// unknown really is "" so get all with null values here
if (selectedItem == Translation.Unknown)
{
selectedItem = string.Empty;
}
else
if (m_CurrLView.m_steps[m_CurrViewStep].groupedBy.attempSplit) requiresSplit = true;
string field = groupedBy.Substring(groupedBy.IndexOf('.') + 1).Replace(">", "");
string tableName = "online_series";
string tableField = tableName + "." + field;
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, field + DBTable.cUserEditPostFix))
{
sql = "(case when (" + userEditField + " is null or " + userEditField + " = " + "'" + "'" + ") " +
"then " + tableField + " else " + userEditField + " " +
"end) " + value;
}
else
{
sql = tableField + " " + value;
}
cond.AddCustom(sql);
if (DBOption.GetOptions(DBOption.cView_Episode_OnlyShowLocalFiles))
{
// not generic
SQLCondition fullSubCond = new SQLCondition();
fullSubCond.AddCustom(DBOnlineEpisode.Q(DBOnlineEpisode.cSeriesID), DBOnlineSeries.Q(DBOnlineSeries.cID), SQLConditionType.Equal);
cond.AddCustom(" exists( " + DBEpisode.stdGetSQL(fullSubCond, false) + " )");
}
if (!DBOption.GetOptions(DBOption.cShowHiddenItems))
cond.AddCustom("exists ( select id from local_series where id = online_series.id and hidden = 0)");
foreach (string series in DBOnlineSeries.GetSingleField(DBOnlineSeries.cPrettyName, cond, new DBOnlineSeries()))
{
seriesNames += series + Environment.NewLine;
count++;
}
setGUIProperty(guiProperty.SeriesCount, count.ToString());
setGUIProperty(guiProperty.Subtitle, count.ToString() + " " + (count == 1 ? Translation.Series : Translation.Series_Plural));
setGUIProperty(guiProperty.Description, seriesNames);
}
else
{
clearGUIProperty(guiProperty.Description);
clearGUIProperty(guiProperty.Subtitle);
}
setGUIProperty(guiProperty.Title, item.Label.ToString());
setGUIProperty(guiProperty.Logos, localLogos.getLogos(m_CurrLView.groupedInfo(m_CurrViewStep), this.m_Facade.SelectedListItem.Label, logosHeight, logosWidth));
clearGUIProperty(guiProperty.EpisodeImage);
DisableFanart();
}
public static string stdGetSQL(SQLCondition conditions, bool selectFull, bool includeStdCond)
{
string field;
if (selectFull)
{
SQLWhat what = new SQLWhat(new DBOnlineSeries());
what.AddWhat(new DBSeries());
field = what;
}
else field = DBOnlineSeries.Q(DBOnlineSeries.cID) + " from " + DBOnlineSeries.cTableName;
if (includeStdCond)
{
conditions.AddCustom(stdConditions.ConditionsSQLString);
}
string conds = conditions;
string orderBy = string.Empty;
if (selectFull)
{
bool bUseSortName = DBOption.GetOptions(DBOption.cUseSortName);
orderBy = conditions.customOrderStringIsSet
? conditions.orderString
: " order by " + (bUseSortName?"upper(" + DBOnlineSeries.Q(DBOnlineSeries.cSortName) + "),":"") + "upper(" + DBOnlineSeries.Q(DBOnlineSeries.cPrettyName) + ")";
}
return "select " + field + " left join " + cTableName + " on " + DBSeries.Q(cID) + "==" + DBOnlineSeries.Q(cID)
+ conds
+ orderBy
+ conditions.limitString;
}
public static List<DBSeries> Get(SQLCondition conditions, bool onlyWithUnwatchedEpisodes, bool includeStdCond)
{
if (onlyWithUnwatchedEpisodes)
{
conditions.AddCustom(@"(
select count(*) from online_episodes
where
seriesID = local_series.ID
and watched = '0'
) > 0");
}
String sqlQuery = stdGetSQL(conditions, true, includeStdCond);
return Get(sqlQuery);
}
