示例#1
0
        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);
        }
示例#2
0
        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);
        }
示例#3
0
        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;
                }
            }
        }
示例#4
0
        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;
        }