Unilevel Organization

• C# Sample Code /
• Reports /
• Unilevel Organization

Overview

Namespaces

This sample requires the following namespaces:

using ExigoOData;
using System.Data.Services.Client;
using System.Text;
using System.Linq.Expressions;
using System.Reflection;
using System.ComponentModel;

Exigo API Authentication

This sample accesses OData using the ExigoContext object:

    public ExigoContext ExigoOData
    {
        get
        {
            var context = new ExigoOData.ExigoContext(new Uri("http://api.exigo.com/4.0/" + exigoAPICompany + "/model"));
            context.IgnoreMissingProperties = true;
            var credentials = Convert.ToBase64String(Encoding.ASCII.GetBytes(exigoAPILoginName + ":" + exigoAPIPassword));
            context.SendingRequest +=
                (object s, SendingRequestEventArgs e) =>
                    e.RequestHeaders.Add("Authorization", "Basic " + credentials);
            return context;
        }
    }
    

jQuery

We use jQuery for easier Ajax calls and theming.

<link href="<%=this.ResolveUrl("../../Themes/start/jquery-ui.custom.css") %>" rel="stylesheet" type="text/css" />
<script src="<%=this.ResolveUrl("../../Scripts/jquery.min.js") %>" type="text/javascript"></script>
<script src="<%=this.ResolveUrl("../../Scripts/jquery-ui.min.js") %>" type="text/javascript"></script>

Standard GridView

For this sample, we utilized a standard ASP.Net GridView object to display our data.

    <div class="ReportGrid ui-widget ui-corner-all">
        <asp:GridView ID="ReportGrid" Width="100%" runat="server" AutoGenerateColumns="false"
            AllowSorting="true" OnSorting="ReportGrid_Sorting">
            <Columns>
                <asp:BoundField DataField="CustomerID" HeaderText="ID" SortExpression="CustomerID" HeaderStyle-CssClass="ui-widget-header ui-corner-tl" />
                <asp:BoundField DataField="FirstName" HeaderText="First Name" SortExpression="Customer.FirstName" HeaderStyle-CssClass="ui-widget-header" />
                <asp:BoundField DataField="LastName" HeaderText="Last Name" SortExpression="Customer.LastName" HeaderStyle-CssClass="ui-widget-header" />
                <asp:BoundField DataField="Level" HeaderText="Level" SortExpression="Level" HeaderStyle-CssClass="ui-widget-header" />
                <asp:BoundField DataField="Email" HeaderText="Email" SortExpression="Customer.Email" HeaderStyle-CssClass="ui-widget-header" />
                <asp:BoundField DataField="Phone" HeaderText="Phone" SortExpression="Customer.Phone" HeaderStyle-CssClass="ui-widget-header" />
                <asp:BoundField DataField="CreatedDate" HeaderText="Joined Date" SortExpression="Customer.CreatedDate"
                    DataFormatString="{0:MMMM d, yyyy}" HeaderStyle-CssClass="ui-widget-header" />
                <asp:BoundField DataField="HighestRankAchieved" HeaderText="Highest Rank Achieved"
                    SortExpression="PeriodVolume.Rank.RankDescription" HeaderStyle-CssClass="ui-widget-header ui-corner-tr" />
            </Columns>
            <EmptyDataTemplate>
                <p>
                    No records to display.
                </p>
            </EmptyDataTemplate>
        </asp:GridView>
    </div>
    

Data Binding

We assign the return value of the OData to the DataSource property of the grid.

    private void BindData()
    {
        ReportGrid.DataSource = FetchReportData(CurrentPage);
        ReportGrid.DataBind();
    }
    

By breaking this up into the following two functions, we can add in the paging, sorting and filtering.

    private IQueryable<UniLevelNodePeriodVolume> ReportDataQuery()
    {
        // Establish the base query
        var query = ExigoOData.UniLevelTreePeriodVolumes
            .Where(u => u.TopCustomerID == customerID)
            .Where(u => u.PeriodTypeID == periodTypeID)
            .Where(u => u.Period.IsCurrentPeriod == true);

        // Apply the sorting, if applicable
        if (!string.IsNullOrEmpty(GridSortExpression))
        {
            query = query.OrderBy(GridSortExpression, GridSortDirection);
        }

        // Apply the searching, if applicable
        if (!string.IsNullOrEmpty(GridSearchValue))
        {
            query = query.Where(GridSearchExpression, GridSearchValue);
        }

        return query;
    }

    private IQueryable FetchReportData(int page)
    {
        // Return the desired fields from the base query
        return ReportDataQuery().Select(u => new
        {
            u.CustomerID,
            u.Customer.FirstName,
            u.Customer.LastName,
            u.Level,
            u.Customer.Email,
            u.Customer.Phone,
            u.Customer.CreatedDate,
            HighestRankAchieved = u.PeriodVolume.Rank.RankDescription
        }).Skip((PageSize * page) - PageSize).Take(PageSize);
    }
    

Custom LINQ Extensions

We created some custom LINQ extensions to help us sort and filter our OData queries.The following custom extensions help us order our OData queries using strings rather than explicitly-casted objects:

    public static IOrderedQueryable<T> OrderBy<T>(this IQueryable<T> source, string property, string method)
    {
        string methodName = string.Empty;
        switch (method)
        {
            case "asc": methodName = "OrderBy"; break;
            case "desc": methodName = "OrderByDescending"; break;
        }
        return ApplyOrder<T>(source, property, methodName);
    }
    private static IOrderedQueryable<T> ApplyOrder<T>(IQueryable<T> source, string property, string methodType)
    {
        string[] props = property.Split('.');
        Type type = typeof(T);
        ParameterExpression arg = Expression.Parameter(type, "x");
        Expression expr = arg;
        foreach (string prop in props)
        {
            // use reflection (not ComponentModel) to mirror LINQ
            PropertyInfo pi = type.GetProperty(prop);
            expr = Expression.Property(expr, pi);
            type = pi.PropertyType;
        }
        Type delegateType = typeof(Func<,>).MakeGenericType(typeof(T), type);
        LambdaExpression lambda = Expression.Lambda(delegateType, expr, arg);

        object result = typeof(Queryable).GetMethods().Single(
                method => method.Name == methodType
                        && method.IsGenericMethodDefinition
                        && method.GetGenericArguments().Length == 2
                        && method.GetParameters().Length == 2)
                .MakeGenericMethod(typeof(T), type)
                .Invoke(null, new object[] { source, lambda });

        return (IOrderedQueryable<T>)result;
    }
    

The following custom extension allows us to filter our OData queries using strings rather than explicitly-casted objects:

    public static IQueryable<T> Where<T>(this IQueryable<T> source, string property, object value)
    {
        var param = Expression.Parameter(typeof(T));

        string[] props = property.Split('.');
        Type type = typeof(T);
        ParameterExpression arg = Expression.Parameter(type, "x");
        Expression leftExpression = arg;
        foreach (string prop in props)
        {
            // use reflection (not ComponentModel) to mirror LINQ
            PropertyInfo pi = type.GetProperty(prop);
            leftExpression = Expression.Property(leftExpression, pi);
            type = pi.PropertyType;
        }

        dynamic typedValue = Convert.ChangeType(value, type);

        BinaryExpression condition = Expression.Equal(leftExpression, Expression.Constant(typedValue));

        return source.Where(Expression.Lambda<Func<T, bool>>(condition, param));
    }