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); }