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