製品版のみの機能

グリッド - 集計 (リモート計算)

このサンプルでは、igGrid コントロールのリモート集計機能を紹介します。このモードでは、集計が Ignite UI for jQuery グリッドの MVC ヘルパーラッパーによってサーバーで計算され、応答としてクライアントに送信されます。

このサンプルは、より大きい画面サイズのためにデザインされました。

モバイルデバイスで画面を回転、フルサイズ表示、またはその他のデバイスにメールで送信します。

コードビュー

クリップボードへコピー

                @using Infragistics.Web.Mvc
@using IgniteUI.SamplesBrowser.Models
@model IQueryable<IgniteUI.SamplesBrowser.Models.Northwind.Product>
<!DOCTYPE html>

<html>
<head>
    <title></title>

    <!-- Ignite UI for jQuery Required Combined CSS Files -->
    <link href="http://cdn-na.infragistics.com/igniteui/2024.1/latest/css/themes/infragistics/infragistics.theme.css" rel="stylesheet" />
    <link href="http://cdn-na.infragistics.com/igniteui/2024.1/latest/css/structure/infragistics.css" rel="stylesheet" />

    <script src="http://ajax.aspnetcdn.com/ajax/modernizr/modernizr-2.8.3.js"></script>
    <script src="http://code.jquery.com/jquery-1.11.3.min.js"></script>
    <script src="http://code.jquery.com/ui/1.11.1/jquery-ui.min.js"></script>

    <!-- Ignite UI for jQuery Required Combined JavaScript Files -->
    <script src="http://cdn-na.infragistics.com/igniteui/2024.1/latest/js/infragistics.core.js"></script>
    <script src="http://cdn-na.infragistics.com/igniteui/2024.1/latest/js/infragistics.lob.js"></script>

</head>
<body>

    @(Html.Infragistics()
        .Grid(Model)
        .ID("products")
        .Width("100%")
        .Height("500px")
        .PrimaryKey("ID")
        .AutoGenerateColumns(false)
        .AutoGenerateLayouts(false)
        .AutoFormat(GridAutoFormat.DateAndNumber)
        .Columns(column =>
        {
            column.For(x => x.ProductName).HeaderText("製品名").Width("40%");
            column.For(x => x.UnitsInStock).HeaderText("在庫数").Width("20%");
            column.For(x => x.UnitPrice).HeaderText("単価").Width("20%");
            column.For(x => x.UnitsOnOrder).HeaderText("入荷待ち").Width("20%");
        })
        .Features(features =>
        {
            features.Summaries().Type(OpType.Remote);
        })
        .DataSourceUrl(Url.Action("GetProducts"))
        .Render()
    )

</body>
</html>

                 using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web.Mvc;
using System.Linq;
using Infragistics.Web.Mvc;
using IgniteUI.SamplesBrowser.Models.Repositories;
using IgniteUI.SamplesBrowser.Application.Data;
using IgniteUI.SamplesBrowser.Models.Northwind;
using System.Collections.Generic;
using System;
using System.Collections.Specialized;
using System.Text.RegularExpressions;
using System.Web;
using LinqToTwitter;
using System.Reflection;
using System.Linq.Expressions;
using System.Text;


namespace IgniteUI.SamplesBrowser.Controllers
{

	public class GridController : Controller
	{
		private DataTable GetCustomers
		{
			get
			{
				if (Session["Customers"] == null)
				{
					Session["Customers"] = GetCustomerDataTable();
				}
				return (DataTable)Session["Customers"];
			}
		}

		[GridDataSourceAction]
		[ActionName("datatable-binding")]
		public ActionResult BasicMvcHelper()
		{
			DataTable customers = this.GetCustomers;
			NameValueCollection queryString = HttpUtility.ParseQueryString(Request.QueryString.ToString());
			// check the query string for sorting expressions
			List<SortExpression> sortExpressions = BuildSortExpressions(queryString, "sort", true);
			DataView dv = customers.DefaultView;
			if (sortExpressions.Count > 0)
			{
				String sortExpression = "";
				foreach (SortExpression expr in sortExpressions)
				{
					sortExpression += expr.Key + " " + (expr.Mode == SortMode.Ascending ? "asc" : "desc") + ",";
				}
				dv.Sort = sortExpression.Substring(0, sortExpression.Length - 1);
			}
			return View("datatable-binding", dv.ToTable());
		}

		public List<SortExpression> BuildSortExpressions(NameValueCollection queryString, string sortKey, bool isTable)
		{
			List<SortExpression> expressions = new List<SortExpression>();
			List<string> sortKeys = new List<string>();
			foreach (string key in queryString.Keys)
			{
				if (!string.IsNullOrEmpty(key) && key.StartsWith(sortKey))
				{
					SortExpression e = new SortExpression();
					e.Key = key.Substring(key.IndexOf("(")).Replace("(", "").Replace(")", "").Split(':')[0];
					e.Logic = "AND";
					e.Mode = queryString[key].ToLower().StartsWith("asc") ? SortMode.Ascending : SortMode.Descending;
					expressions.Add(e);
					sortKeys.Add(key);
				}
			}
			if (sortKeys.Count > 0 && isTable)
			{
				foreach (string sortedKey in sortKeys)
				{
					queryString.Remove(sortedKey);
				}
				string url = Request.Url.AbsolutePath;
				string updatedQueryString = "?" + queryString.ToString();
				Response.Redirect(url + updatedQueryString);
			}
			return expressions;
		}

		[ActionName("aspnet-mvc-helper")]
		public ActionResult AspnetMvcHelper()
		{
			return View("aspnet-mvc-helper");
		}

        [ActionName("sorting-remote")]
        public ActionResult SortingRemote()
        {
            return View("sorting-remote");
        }

        [ActionName("summaries-remote")]
		public ActionResult SummariesRemote()
		{
			return View("summaries-remote");
		}

		[ActionName("bind-web-api")]
		public ActionResult BindToWebAPI()
		{
			return View("bind-web-api");
		}

		[ActionName("basic-editing")]
		public ActionResult BasicEditing()
		{
			ViewBag.Customers = RepositoryFactory.GetCustomerRepository().Get().AsQueryable();
			return View("basic-editing");
		}

        [ActionName("manual-remote-features-handling")]
        public ActionResult GridRemoteFeaturesHandling()
        {
            return View("manual-remote-features-handling");
        }

        public JsonResult GridRemoteData()
        {
            IEnumerable<Order> customers = RepositoryFactory.GetOrderRepository().Get().Take(200);

            IQueryable data = customers.AsQueryable();

            //apply filtering
            var filterExprs = Request.QueryString.AllKeys.Where(x => x.Contains("filter"));
            if (filterExprs.Count() != 0)
            {
                data = ApplyFilterExpr(Request.QueryString, customers.AsQueryable());
            }

            //apply sorting
            var sortExprs = Request.QueryString.AllKeys.Where(x => x.Contains("sort"));
            if (sortExprs.Count() != 0)
            {
                data = ApplySorting(Request.QueryString, data);
            }


            int totalCount = data.Count();

            //apply paging 
            if (Request.QueryString["$top"] != null && Request.QueryString["$skip"] != null)
            {
                data = ApplyPaging(Request.QueryString, data);
            }

            JsonResult result = new JsonResult();
            result.JsonRequestBehavior = JsonRequestBehavior.AllowGet;
            result.Data = new { Records = data, TotalRecordsCount = totalCount };
            return result;
        }

        [ActionName("rest-editing")]
        public ActionResult RestEditing()
        {
            return View("rest-editing");
        }

        //[ActionName("grid-performance")]
        //public ActionResult GridPerformance()
        //{
        //    return View("grid-performance");
        //}


        public LargeJsonResult GridPerformance()
        {
            bool dataBind = bool.Parse(Request.QueryString["databind"]);
            int rowsCount = int.Parse(Request.QueryString["rowsCount"]);
            int colsCount = int.Parse(Request.QueryString["colsCount"]);
            int pageSize, pageIndex;
            var persons = RepositoryFactory.GetPersonsRepository(dataBind, rowsCount, colsCount).Get();
            bool res;

            res = int.TryParse(Request.QueryString["pageSize"], out pageSize);
            if (!res)
            {
                pageSize = -1;
                res = false;
            }

            res = int.TryParse(Request.QueryString["pageIndex"], out pageIndex);
            if (!res)
            {
                pageIndex = -1;
                res = false;
            }

            IQueryable data = persons.AsQueryable();

            //apply filtering
            var filterExprs = Request.QueryString.AllKeys.Where(x => x.Contains("filter"));
            if (filterExprs.Count() != 0)
            {
                data = ApplyFilterExpr(Request.QueryString, persons.AsQueryable());
            }

            int totalCount = data.Count();

            //apply paging 
            if (pageSize > -1 && pageIndex > -1)
            {
                data = data.Skip(pageIndex * pageSize).Take(pageSize); ;
            }

            LargeJsonResult result = new LargeJsonResult();
            result.JsonRequestBehavior = JsonRequestBehavior.AllowGet;
            result.Data = new { Records = data, TotalRecordsCount = totalCount };
            return result;
        }

		private IQueryable ApplyPaging(NameValueCollection queryString, IQueryable data)
		{
			int recCount = Convert.ToInt32(queryString["$top"]);
			int startIndex = Convert.ToInt32(queryString["$skip"]);

			data = data.Skip(startIndex).Take(recCount);
			return data;
		}

		private IQueryable ApplySorting(NameValueCollection queryString, IQueryable data)
		{
			List<SortExpression> sortExpressions = BuildSortExpressions(queryString, "sort", false);

			string orderBy = "OrderBy";
			string orderByDescending = "OrderByDescending";
			foreach (SortExpression expr in sortExpressions)
			{

				data = ApplyOrder(data, expr.Key, expr.Mode == SortMode.Ascending ? orderBy : orderByDescending);
				orderBy = "ThenBy";
				orderByDescending = "ThenByDescending";
			}
			return data;
			
		}

		public static IQueryable ApplyOrder(IQueryable source, string property, string methodName)
		{
			string[] props = property.Split('.');
			Type type = source.ElementType;
			ParameterExpression arg = Expression.Parameter(type, "x");
			Expression expr = arg;
			foreach (string prop in props)
			{
                var propName = prop.Split(':')[0];
				PropertyInfo pi = type.GetProperty(propName);
				expr = Expression.Property(expr, pi);
				type = pi.PropertyType;
			}
			Type delegateType = typeof(Func<,>).MakeGenericType(source.ElementType, type);
			LambdaExpression lambda = Expression.Lambda(delegateType, expr, arg);

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

		private IQueryable ApplyFilterExpr(NameValueCollection queryString, IQueryable customers)
		{
			List<FilterExpression> exprs = GetFilterExpressions(queryString);
			StringBuilder builder = new StringBuilder();
			int count = 0;

			for (int i = 0; i < exprs.Count; i++)
			{
				if (count != 0 && count <= exprs.Count - 1)
				{
					builder.Append(exprs[i].Logic.ToLower() == "AND".ToLower() ? " AND " : " OR ");
				}
				count++;

				string condition = exprs[i].Condition;
				string expr = exprs[i].Expr;
				string colKey = exprs[i].Key;
				var dt = DateTime.Now;
				
				switch (condition.ToLower())
				{
					case "startswith":
						 builder.Append(colKey + ".StartsWith(\"" + expr + "\")");
						break;
					case "contains":
						 builder.Append(colKey + ".Contains(\"" + expr + "\")");
						break;
					case "endswith":
						 builder.Append(colKey + ".EndsWith(\"" + expr + "\")");
						break;
					case "equals":
						if (colKey == "ShipName") {
							//col type is string
							builder.Append(colKey + " == \"" + expr + "\"");
						}
						else
						{
							//col type is number
							builder.Append(colKey + " == " + expr);
						}
						
						break;
					case "doesnotequal":
						if (colKey == "ShipName")
						{
							//col type is string
							builder.Append(colKey + " != \"" + expr + "\"");
						}
						else
						{
							//col type is number
							builder.Append(colKey + " != " + expr);
						}
						break;
					case "doesnotcontain":
						 builder.Append("! " + colKey + ".Contains(\"" + expr + "\")");
						break;
					case "lessthan":
						 builder.Append(colKey + " < " + expr);
						break;
					case "greaterthan":
						 builder.Append(colKey + " > " + expr);
						break;
					case "lessthanorequalto":
						 builder.Append(colKey + " <= " + expr);
						break;
					case "greaterthanorequalto":
						 builder.Append(colKey + " >= " + expr);
						break;
					case "on":
						dt = new DateTime(1970, 1, 1, 0, 0, 0, DateTimeKind.Utc).AddMilliseconds(double.Parse(expr)).ToUniversalTime();
						 builder.Append("(" + colKey + ".Value.Day == " + dt.Day + " AND " + colKey +
							".Value.Year == " + dt.Year + " AND " +colKey + ".Value.Month == " + dt.Month + ")");
						break;
					case "noton":
						dt = new DateTime(1970, 1, 1, 0, 0, 0, DateTimeKind.Utc).AddMilliseconds(double.Parse(expr)).ToUniversalTime();
						 builder.Append("!("+colKey + ".Value.Day == " + dt.Day + " AND " + colKey +
							".Value.Year == " + dt.Year + " AND " + colKey + ".Value.Month == " + dt.Month + ")");
						break;
					case "after":
						dt = new DateTime(1970, 1, 1, 0, 0, 0, DateTimeKind.Utc).AddMilliseconds(double.Parse(expr)).ToUniversalTime();
						 builder.Append("((" + colKey + ".Value.Year > " + dt.Year + " OR (" +
							colKey + ".Value.Month > " + dt.Month + " AND " + colKey + ".Value.Year == " + dt.Year + ") OR (" +
							colKey + ".Value.Day > " + dt.Day + " AND " + colKey + ".Value.Year == " + dt.Year + " AND " +
							colKey + ".Value.Month == " + dt.Month + ")))");
						break;
					case "before":
						dt = new DateTime(1970, 1, 1, 0, 0, 0, DateTimeKind.Utc).AddMilliseconds(double.Parse(expr)).ToUniversalTime();
						builder.Append("((" + colKey + ".Value.Year < " + dt.Year + " OR (" +
									   colKey + ".Value.Month < " + dt.Month + " AND " + colKey + ".Value.Year == " + dt.Year + ") OR (" +
									   colKey + ".Value.Day < " + dt.Day + " AND " + colKey + ".Value.Year == " + dt.Year + " AND " +
									   colKey + ".Value.Month == " + dt.Month + ")))");
						break;
					case "today":
						builder.Append("(" + colKey + ".Value.Day == " + DateTime.Now.Day + " AND " + colKey +
						 ".Value.Year == " + DateTime.Now.Year + " AND " + colKey + ".Value.Month == " + DateTime.Now.Month + ")");
						break;
					case "yesterday":
						DateTime yesterday = DateTime.Now.AddDays(-1);
						builder.Append("(" + colKey + ".Value.Day == " + yesterday.Day + " AND " + colKey +
						 ".Value.Year == " + yesterday.Year + " AND " + colKey + ".Value.Month == " + yesterday.Month + ")");
						break;
					case "thismonth":
						builder.Append("(" + colKey + ".Value.Year == " + DateTime.Now.Year + " AND " + colKey + ".Value.Month == " + DateTime.Now.Month + ")");
						break;
					case "lastmonth":
						builder.Append("(" + colKey + ".Value.Year == " + (DateTime.Now.Year - 1) + " AND " + colKey + ".Value.Month == " + (DateTime.Now.Month - 1) + ")");
						break;
					case "nextmonth":
						builder.Append("(" + colKey + ".Value.Year == " + (DateTime.Now.Year - 1) + " AND " + colKey + ".Value.Month == " + (DateTime.Now.Month + 1) + ")");
						break;
					case "thisyear":
						builder.Append(colKey + ".Value.Year == " + DateTime.Now.Year);
						break;
					case "lastyear":
						builder.Append(colKey + ".Value.Year == " + (DateTime.Now.Year - 1));
						break;
					case "nextyear":
						builder.Append(colKey + ".Value.Year == " + (DateTime.Now.Year + 1));
						break;
					default:
						break;
				}
			}
			if (builder.Length > 0) {
				customers = customers.Where(builder.ToString(), new object[0]);
			}
		
			return customers;
		}

		internal List<FilterExpression> GetFilterExpressions(NameValueCollection queryString)
		{
			List<FilterExpression> expressions = new List<FilterExpression>();
			// check the query string for sorting expressions
			foreach (string key in queryString.Keys)
			{
				if (!string.IsNullOrEmpty(key) && key.StartsWith("filter("))
				{
					string val = key.Substring(key.IndexOf("(")).Replace("(", "").Replace(")", "");
                    string columnKey = val.Substring(0, val.IndexOf(":"));
					string logic = "AND";

					if (queryString["filter"] != null && (queryString["filter"].ToLower() == "and" || queryString["filter"].ToLower() == "or"))
					{
						logic = queryString["filter"];
					}

					Regex filtersRegex = new Regex(@"[a-z]+\(.*?\)", RegexOptions.IgnoreCase);
					MatchCollection m = filtersRegex.Matches(queryString[key]);
					string[] filters = new string[m.Count];
					int i = 0;
					foreach (Match capture in m)
					{
						filters[i] = capture.Value;
						i++;
					}

					// Handle multiple filters for the same column 
					for (i = 0; i < filters.Length; i++)
					{
						FilterExpression e = new FilterExpression();
						e.Logic = logic;
						e.Key = columnKey;
						e.Condition = filters[i].Substring(0, filters[i].IndexOf("("));
						if ((filters[i].StartsWith("contains()") || filters[i].StartsWith("equals()") || filters[i].StartsWith("startsWith()")
								|| filters[i].StartsWith("endsWith()") || filters[i].StartsWith("doesNotContain()") || filters[i].StartsWith("doesNotEqual()")
							)
							&& (e.Condition != "null" && e.Condition != "notNull" && e.Condition != "empty" && e.Condition != "notEmpty")
							&& (e.Condition != "today" && e.Condition != "yesterday" && e.Condition != "thisMonth" && e.Condition != "nextMonth"
							&& e.Condition != "lastMonth" && e.Condition != "thisYear" && e.Condition != "nextYear" && e.Condition != "lastYear")
							)
						{
							continue;
						}
						else
						{
							string tmp = filters[i].Substring(filters[i].IndexOf("("));
							e.Expr = filters[i].Substring(filters[i].IndexOf("(")).Replace("(", "").Replace(")", "");
						}
						expressions.Add(e);
					}
				}
			}

			return expressions;
		}




		

		public ActionResult OrdersSaveData()
		{
			GridModel gridModel = new GridModel();
			List<Transaction<Order>> transactions = gridModel.LoadTransactions<Order>(HttpContext.Request.Form["ig_transactions"]);
			var orders = RepositoryFactory.GetOrderRepository();
			foreach (Transaction<Order> t in transactions)
			{
				if (t.type == "newrow")
				{
					orders.Add(t.row);
				}
				else if (t.type == "deleterow")
				{
					orders.Delete(o => o.OrderID == Int32.Parse(t.rowId));
				}
				else if (t.type == "row")
				{
					var order = (from o in orders.Get()
								 where o.OrderID == Int32.Parse(t.rowId)
								 select o).Single();
					if (t.row.OrderDate != null)
					{
						order.OrderDate = t.row.OrderDate;
					}
					order.TotalPrice = t.row.TotalPrice;
					order.TotalItems = t.row.TotalItems;
					if (t.row.CustomerID != null)
					{
						order.CustomerID = t.row.CustomerID;
					}
					if (t.row.ShipAddress != null)
					{
						order.ShipAddress = t.row.ShipAddress;
					}
					orders.Update(order, o => o.OrderID == Int32.Parse(t.rowId));
				}
			}
			orders.Save();
			JsonResult result = new JsonResult();
			Dictionary<string, bool> response = new Dictionary<string, bool>();
			response.Add("Success", true);
			result.Data = response;
			return result;
		}

		[GridDataSourceAction]
		public ActionResult ChainingGetData()
		{
			var employees = RepositoryFactory.GetEmployeeRepository().Get();
			return View(employees);
		}

		[GridDataSourceAction]
		public ActionResult GetTweets()
		{
			var auth = new SingleUserAuthorizer
			{
				Credentials = new SingleUserInMemoryCredentials
				{
					ConsumerKey = ConfigurationManager.AppSettings["consumerKey"],
					ConsumerSecret = ConfigurationManager.AppSettings["consumerSecret"],
					TwitterAccessToken = ConfigurationManager.AppSettings["accessToken"],
					TwitterAccessTokenSecret = ConfigurationManager.AppSettings["accessTokenSecret"]
				}
			};
			var twitterCtx = new TwitterContext(auth);

			var userStatusResponse =
			   (from tweet in twitterCtx.Status
				where tweet.Type == StatusType.User &&
					  tweet.ScreenName == "Infragistics" &&
					  tweet.Count == 100
				select tweet);

			var data = userStatusResponse.ToArray();

			return View(data.AsQueryable()); ;
		}


		[GridDataSourceAction]
		public ActionResult GetEmployees()
		{
			var employees = RepositoryFactory.GetEmployeeRepository().Get();
			return View(employees);
		}

		[GridDataSourceAction]
		public ActionResult GetProducts()
		{
			var products = RepositoryFactory.GetProductRepository().Get();
			return View(products);
		}



		[GridDataSourceAction]
		public ActionResult GetCategories()
		{
			var categories = RepositoryFactory.GetCategoryRepository().Get();
			return View(categories);
		}

		[GridDataSourceAction]
		public ActionResult GetOrders()
		{
			var orders = RepositoryFactory.GetOrderRepository().Get().OrderBy(o => o.OrderID).Take(50).AsQueryable();
			return View(orders);
		}

		private DataTable GetCustomerDataTable()
		{
			NorthwindContext ctx = new NorthwindContext();
			SqlConnection conn = (SqlConnection)ctx.Database.Connection;
			DataTable dt = new DataTable();
			using (SqlConnection con = conn)
			{
				using (SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM CUSTOMERS", con))
				{
					adapter.Fill(dt);
				}
			}
			return dt;
		}
	}
}

             using IgniteUI.SamplesBrowser.Models.Repositories;
using System;

namespace IgniteUI.SamplesBrowser.Models.Northwind
{
    public class Product
    {
        public int ID { get; set; }
        public string ProductName { get; set; }
        public Nullable<int> SupplierID { get; set; }
        public Nullable<int> CategoryID { get; set; }
        public string QuantityPerUnit { get; set; }
        public Nullable<decimal> UnitPrice { get; set; }
        public Nullable<short> UnitsInStock { get; set; }
        public Nullable<short> UnitsOnOrder { get; set; }
        public Nullable<short> ReorderLevel { get; set; }
        public string SupplierName { get; set; }
        public string CategoryName { get; set; }
        public int Rating { get; set; }
        public bool Discontinued { get; set; }
        public string CategoryImageUrl { get; set; }
    }
}

Ignite UI for jQuery | サンプル

検索結果

グリッド - 集計 (リモート計算)

コードビュー

Ignite UI for jQuery | サンプル

検索結果

グリッド - 集計 (リモート計算)

コード ビュー

コードビュー