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