Web View Index Data List

/// <summary>
/// Overrides the base SOWIWeb.Helper.ControllerStandard method IndexGetView with out parameter.
/// Tihs method calls the method GetViewSimple from App
/// </summary>
/// <param name="pViewConfig">List setting</param>
/// <param name="pViewName">View name e.g. Index</param>
/// <param name="pDictionaryUI">UI dictionary</param>
/// <returns>A simple data list</returns>
/// <seealso cref="SOWIWeb.Helper.ControllerStandard{DataItemClass}.IndexGetView(out ListViewConfig, string, Dictionary{string, string})"/>
public override List<object> IndexGetView(out ListViewConfig pViewConfig, string pViewName, Dictionary<string, string> pDictionaryUI = null)
{
    return this.AppMuster.GetViewSimple(out pViewConfig, pViewName, pDictionaryUI);
}

/// <summary>
/// Overrides the base SOWIWeb.Helper.ControllerStandard method IndexGetView with ref parameter.
/// This method calls the method GetViewSimple from App.
/// </summary>
/// <param name="pViewConfig">List setting</param>
/// <param name="pDictionaryUI">UI dictionary</param>
/// <returns>A simple data list</returns>
/// <seealso cref="SOWIWeb.Helper.ControllerStandard{DataItemClass}.IndexGetView(ref ListViewConfig, Dictionary{string, string})"/>
public override List<object> IndexGetView(ref ListViewConfig pViewConfig, Dictionary<string, string> pDictionaryUI = null)
{
    return this.AppMuster.GetViewSimple(ref pViewConfig, pDictionaryUI);
}

/// <summary>
/// Data list configured by List View Config
/// </summary>
/// <param name="pViewConfig">Return the ViewConfig object</param>
/// <param name="pViewName">View name for list view setting e.g. Index</param>
/// <param name="pDictionaryUI">UI dictionary. If object equal null then use internal DictionaryUI object</param>
/// <returns>A list of data item objects</returns>
/// <remarks>
/// This method calls method GetViewSimple with ref parameter.
/// See this for more information.
/// </remarks>
public List<object> GetViewSimple(out SOWIData.Management.ListViewConfig pViewConfig, string pViewName, Dictionary<string, string> pDictionaryUI = null)
{
    try
    {
        if (pDictionaryUI == null)
        {
            pDictionaryUI = this.DictionaryUI;
        }
        pViewConfig = SOWIApp.Management.ListViewConfig.GetByConfig(this.AppName, this.UserName, pViewName);
        return this.GetViewSimple(ref pViewConfig, pDictionaryUI);
    }
    catch (Exception ex)
    {
        SIC.Protocol.Write(ref ex);
        throw;
    }
}

/// <summary>
/// Gives a data list by list view config setting.
/// </summary>
/// <remarks>
/// Requirement: SQL view called View_Ticket
/// </remarks>
/// <param name="pViewConfig">Setting for data list. This parameter is by reference (C# with ref prefix)</param>
/// <param name="pDictionaryUI">Dictionary for UI translation. If null then the internal DictionaryUI is used</param>
/// <returns>List of data records</returns>
/// <seealso cref="SOWIData.Management.ListViewConfig"/>
/// <seealso cref="SOWIApp.Foundation.AppStandard{DataItemClass}.GetView(ref SOWIData.Management.ListViewConfig, Dictionary{string, string})"/>
public List<object> GetViewSimple(ref SOWIData.Management.ListViewConfig pViewConfig, Dictionary<string, string> pDictionaryUI = null)
{
    try
    {
        #region --- declaration SQL variable ---
        string lSQL;
        string lSQLSelect = @"SELECT * FROM [View_Ticket] ";
        string lSQLWhere = "";
        string lSQLOrderBy = "";
        #endregion

        #region --- puts DictionaryUI object ---
        if (pDictionaryUI == null)
        {
            pDictionaryUI = this.DictionaryUI;
        }
        if (pViewConfig == null)
        {
            throw new Exception(SIC.Messages.DataNotFound.ToString());
        }
        #endregion

        #region --- search field exist? ---
        if (pViewConfig.SearchField == null)
        {
            pViewConfig.SearchField = "";
        }
        #endregion

        #region --- set SearchValues ---
        pViewConfig.SearchValues = new List<string>();
        string lSearchField = pViewConfig.SearchField;
        if (lSearchField != "")
        {
            try
            {
                lSQL = "SELECT [" + lSearchField + "] " +
                       "FROM [View_Ticket] " +
                       "WHERE [ClientID] = " + this.ClientID + " AND [StatusText] <> 'Delete' AND [" + lSearchField + "] IS NOT NULL " +
                       "GROUP BY [" + lSearchField + "]";
                pViewConfig.SearchValues = this.DB.Database.SqlQuery<string>(lSQL).ToList();

                //var lSearchValues = this.DB.Database.SqlQuery<object>(lSQL).ToList();
                //foreach (var item in lSearchValues)
                //{
                //    pViewConfig.SearchValues.Add(item.ToString());
                //}
            }
            catch (Exception ex)
            {
                // BUG: exception message: Die angegebene Umwandlung aus einem materialisierten 'System.DateTime'-Typ in den 'System.String'-Typ ist ungültig. [12.01.2018]

                pViewConfig.SearchValues.Add("");
                pViewConfig.SearchValues.Add("Daten können nicht gelesen werden!");
                pViewConfig.SearchValues.Add("Exception: " + ex.Message.ToString());
            }
        }
        #endregion

        #region --- sort field and direction exist? ---
        if (pViewConfig.SortField == null)
        {
            pViewConfig.SortField = this.SortFieldStandard;
            pViewConfig.SortDirection = this.SortDirectionStandard;
        }
        #endregion

        #region --- puts sort SQL statement ---
        lSQLOrderBy = "ORDER BY [" + pViewConfig.SortField + "] ";
        if (pViewConfig.SortDirection == SortDirections.descending)
        {
            lSQLOrderBy += "DESC ";
        }
        #endregion

        #region --- search and sort field list exist? ---
        if (pViewConfig.Fields == null)
        {
            var lFields = DataGridView;
            lFields = (from p in lFields
                       where p.InFieldList == true
                       select p).ToList();
            lFields.Insert(0, new SOWIData.Management.DataGrid());
            if (pDictionaryUI != null)
            {
                SOWIApp.Management.DataGrid.Translate(ref lFields, pDictionaryUI);
            }
            else
            {
                foreach (var item in lFields)
                {
                    if (item.ColumnLabel.Length == 0)
                    {
                        item.ColumnLabel = item.DataFieldName;
                    }
                }
            }
            pViewConfig.Fields = lFields; ;
        }
        #endregion

        #region --- puts search filter SQL statement ---

        //* analyse customer filter *
        string lValue = (string.IsNullOrEmpty(pViewConfig.SearchValue) ? "" : pViewConfig.SearchValue);
        if (lValue == "")
        {
            lValue = (string.IsNullOrEmpty(pViewConfig.SearchValue2) ? "" : pViewConfig.SearchValue2);
            if (lValue != "")
            {   // ** Check exists Value in Search Values **
                var lFind = pViewConfig.SearchValues.Find(p => p == lValue);
                if (lFind == null)
                {
                    lValue = "";
                    pViewConfig.SearchValue2 = "";
                }
                else
                {
                    lValue = pViewConfig.SearchValue2;
                }
            }
        }
        else
        {
            pViewConfig.SearchValue2 = "";
        }

        //* puts default filter *
        lSQLWhere = @"WHERE [ClientID] = " + this.ClientID.ToString() + " " +
                      @"AND [StatusText] <> 'Delete' ";

        //* puts custom filter *
        if (lValue.Length > 0)
        {
            lSQLWhere += @" AND ";
            switch (pViewConfig.SearchOperator)
            {
                case Operators.Equal:
                    lSQLWhere += @"[" + pViewConfig.SearchField.ToString() + @"] = '" + lValue.ToString() + @"' ";
                    break;
                case Operators.LessThan:
                    lSQLWhere += @"[" + pViewConfig.SearchField.ToString() + @"] < " + lValue.ToString() + @" ";
                    break;
                case Operators.LessThanOrEqual:
                    lSQLWhere += @"[" + pViewConfig.SearchField.ToString() + @"] <= " + lValue.ToString() + @" ";
                    break;
                case Operators.GreaterThan:
                    lSQLWhere += @"[" + pViewConfig.SearchField.ToString() + @"] > " + lValue.ToString() + @" ";
                    break;
                case Operators.GreaterThanOrEqual:
                    lSQLWhere += @"[" + pViewConfig.SearchField.ToString() + @"] >= " + lValue.ToString() + @" ";
                    break;
                case Operators.Contains:        //* example: WHERE [Name] LIKE '%Beamer%' *
                    lSQLWhere += @"[" + pViewConfig.SearchField.ToString() + @"] LIKE '%" + lValue.ToString() + @"%' ";
                    break;
                case Operators.Flag:
                    break;
                default:
                    break;
            }
        }
        #endregion

        #region --- puts data rows active, archived or all ---
        switch (pViewConfig.Record)
        {
            case Records.All:
                break;
            case Records.Active:
                lSQLWhere += " AND [Archive] = 0 ";
                break;
            case Records.Archived:
                lSQLWhere += " AND [Archive] = 1 ";
                break;
            default:
                break;
        }
        #endregion

        #region --- paging ---

        //* count of rows *
        lSQL = @"SELECT COUNT([ID]) FROM [View_Ticket] ";
        lSQL += lSQLWhere;   //* example: WHERE [ClientID] = 4 AND [StatusText] <> 'Delete' *
        var lCount = this.DB.Database.SqlQuery<int>(lSQL).First();

        pViewConfig.RecordCount = lCount;
        pViewConfig.PageCount = pViewConfig.RecordCount / pViewConfig.PageSize;
        int lRecordCountByPage = pViewConfig.PageSize * pViewConfig.PageCount;
        if (lRecordCountByPage < pViewConfig.RecordCount)
        {
            pViewConfig.PageCount += 1;
        }
        if (pViewConfig.CurrentPageIndex > pViewConfig.PageCount - 1)        // CurrentPageIndex null based
        {
            pViewConfig.CurrentPageIndex = pViewConfig.PageCount - 1;
        }
        #endregion

        #region --- set data list ---
        lSQL = lSQLSelect;   //* SELECT * FROM View_Ticket *
        lSQL += lSQLWhere;   //* example: WHERE [ClientID] = 4 AND [StatusText] <> 'Delete' *
        lSQL += lSQLOrderBy; //* example: ORDER BY [Name] *

        //* paging *
        #region --- test example ---
        //lSQL += @"OFFSET 0 ROWS " +
        //        @"FETCH NEXT 20 ROWS ONLY";
        #endregion

        int lOffset = (pViewConfig.CurrentPageIndex * pViewConfig.PageSize);
        if (lOffset >= 0)
        {
            lSQL += @"OFFSET " + lOffset + " ROWS " +
                    @"FETCH NEXT " + pViewConfig.PageSize + " ROWS ONLY";
        }
        List<ProTicData.Models.Ticket> lQuery = this.DB.Database.SqlQuery<ProTicData.Models.Ticket>(lSQL).ToList();
        #endregion

        #region --- saves ListViewConfig object ---
        SOWIApp.Management.ListViewConfig.SetListViewConfig(pViewConfig);
        #endregion

        return (lQuery as IEnumerable<object>).Cast<object>().ToList();

    }
    catch (Exception ex)
    {
        SIC.Protocol.Write(ref ex);
        throw;
    }
}