public JsonResult InvoiceGetInventoryItem(DatableOption option) { var jsonReturn = new JsonDataTable(); try { using (var db = new FTTxEntities()) { var query = (from t1 in db.OITM join t2 in db.OITB on t1.ItmsGrpCod equals t2.ItmsGrpCod join t3 in db.ITM1.Where(i => i.PriceList == 1) on t1.ItemCode equals t3.ItemCode where (string.IsNullOrEmpty(option.Query) || (t1.ItemCode.Contains(option.Query)) || (t1.ItemName.Contains(option.Query)) ) where t1.frozenFor == "N" where t2.ItmsGrpNam == "Project" select new { RowsNumber = 0, ItemCode = t1.ItemCode, ItemName = t1.ItemName ?? "", UnitPrice = t1.PricingPrc, }); if (option.order.Count > 0) { var sorting = int.Parse(option.order[0].FirstOrDefault(r => r.Key == "column").Value); var dir = option.order[0].FirstOrDefault(r => r.Key == "dir").Value; switch (sorting) { case 1: query = (dir.ToLower() == "asc" ? query.OrderBy(r => r.ItemCode) : query.OrderByDescending(r => r.ItemCode)); break; case 2: query = (dir.ToLower() == "asc" ? query.OrderBy(r => r.ItemName) : query.OrderByDescending(r => r.ItemName)); break; case 3: query = (dir.ToLower() == "asc" ? query.OrderBy(r => r.UnitPrice) : query.OrderByDescending(r => r.UnitPrice)); break; default: query = (dir.ToLower() == "asc" ? query.OrderBy(r => r.ItemCode) : query.OrderByDescending(r => r.ItemCode)); break; } } var data = query.Skip(option.start).Take(option.length).ToList(); var count = query.Count(); jsonReturn = new JsonDataTable { status = true, message = "Ok", data = data, draw = option.draw, recordsTotal = count, recordsFiltered = count }; } } catch (Exception ex) { jsonReturn = new JsonDataTable { status = false, message = ex.Message, data = new string[0], draw = option.draw, recordsTotal = 0 }; Log.Error(this.GetType().FullName + "." + System.Reflection.MethodBase.GetCurrentMethod().Name + " Error -> " + ex.Message); } return(Json(jsonReturn)); }
void GenerateCreateTableScript(JsonDataTable dt, StringBuilder sbScript) { sbScript.AppendFormat("if not exists(select * from sysobjects where name = '{0}' and xtype = 'U')", dt.TableName); sbScript.AppendLine(); sbScript.AppendFormat("\tcreate table [{0}](", dt.TableName); sbScript.AppendLine(); foreach (var fieldData in dt.FieldMetaData) { sbScript.AppendFormat("\t\t[{0}] {1} NULL,", fieldData.FieldName, SqlDataType(fieldData.DataType)); sbScript.AppendLine(); } sbScript.Length -= 3; //remove last comma and \r\n sbScript.AppendLine(); sbScript.Append("\t)"); if (dt.Tables.Count > 0) { foreach (var table in dt.Tables) { sbScript.AppendLine(); sbScript.AppendFormat("------------ Table : \"{0}\" ------------", table.Key); sbScript.AppendLine(); GenerateCreateTableScript(table.Value, sbScript); } } sbScript.AppendLine(); }
void GenerateInsertScript(JsonDataTable dt, StringBuilder sbScript) { sbScript.AppendFormat("------------ Begin Table \"{0}\" Rows ({1}) ------------", dt.TableName, dt.Rows.Count); sbScript.AppendLine(); foreach (var row in dt.Rows.DataRows) { sbScript.AppendFormat("insert into [{0}](", dt.TableName); foreach (var fieldData in dt.FieldMetaData) { sbScript.AppendFormat("[{0}],", fieldData.FieldName); } sbScript.Length -= 1; sbScript.Append(")").AppendLine(); sbScript.Append("\tvalues("); sbScript.AppendFormat("{0},", row._Id); sbScript.AppendFormat("{0},", row._ParentId); //left join with matadata to make all fields available for insert var flds = from m in dt.FieldMetaData join r in row.Fields on m.FieldName equals r.FieldName into ps from r in ps.DefaultIfEmpty() where m.IsIdentityField == false select new { FieldName = m.FieldName, DataType = m.DataType, DateCulture = m.DateCulture, FieldValue = r?.FieldValue //use null propogation }; foreach (var field in flds) { var val = DecideFieldValue( new Field() { FieldName = field.FieldName, FieldValue = field.FieldValue }, dt.FieldMetaData); sbScript.AppendFormat("{1}{0}{1},", val, GetFieldDataTypeDelimit( new Field() { FieldName = field.FieldName, FieldValue = field.FieldValue }, dt.FieldMetaData)); } sbScript.Length -= 1; //remove last comma sbScript.Append(")").AppendLine(); } sbScript.AppendFormat("------------ End Table \"{0}\" Rows ({1}) ------------", dt.TableName, dt.Rows.Count); sbScript.AppendLine().AppendLine(); if (dt.Tables.Count > 0) //Iterate into child tables { foreach (var table in dt.Tables) { GenerateInsertScript(table.Value, sbScript); } } }
public override StringBuilder ToSqlScript(JsonDataTable jsonDataTable) { this.jsonDataTable = jsonDataTable; StringBuilder stringBuilder = new StringBuilder(); GenerateCreateTableScript(jsonDataTable, stringBuilder); GenerateInsertScript(jsonDataTable, stringBuilder); return(stringBuilder); }
public ActionResult WvJRpzl5(int draw, int start, int length) { JsonDataTable dataTableData = new JsonDataTable(); string search = string.Empty; int recordsFiltered = 0; int recordsTotal = 0; if (User != null) { int sortColumn = -1; string sortDirection = "asc"; if (Request.QueryString["order[0][column]"] != null) { sortColumn = int.Parse(Request.QueryString["order[0][column]"]); } if (Request.QueryString["order[0][dir]"] != null) { sortDirection = Request.QueryString["order[0][dir]"]; } var start_offset = start; if (start_offset != 0) { start_offset = start / length; } if (Request.QueryString["search[value]"] != null) { search = Request.QueryString["search[value]"].Trim(); } int flt = 0; if (Request.QueryString["mose"] != null) { var s_mose = Peach.DecriptFromBase64(Request.QueryString["mose"].Trim()); if (s_mose != string.Empty) { flt = Convert.ToInt16((s_mose)); } } dataTableData.draw = draw; dataTableData.data = new BLCuentaCorriente().Listar_toDataTables(start_offset, length, search, sortColumn, sortDirection, flt, ref recordsTotal); recordsFiltered = dataTableData.data.Count(); dataTableData.recordsTotal = recordsTotal; dataTableData.recordsFiltered = (search == string.Empty ? recordsTotal : recordsFiltered); return(Json(dataTableData, JsonRequestBehavior.AllowGet)); } else { return(RedirectToAction("Index", "Account", new { act = "timeout" })); } }
public ActionResult TXpDQ0V1(int draw, int start, int length) { JsonDataTable dataTableData = new JsonDataTable(); string search = string.Empty; int recordsFiltered = 0; int recordsTotal = 0; if (User != null) { int sortColumn = -1; string sortDirection = "asc"; if (Request.QueryString["order[0][column]"] != null) { sortColumn = int.Parse(Request.QueryString["order[0][column]"]); } if (Request.QueryString["order[0][dir]"] != null) { sortDirection = Request.QueryString["order[0][dir]"]; } var start_offset = start; if (start_offset != 0) { start_offset = start / length; } if (Request.QueryString["search[value]"] != null) { search = Request.QueryString["search[value]"].Trim(); } dataTableData.draw = draw; dataTableData.data = new BLMoneda().Listar_toDataTables(start_offset, length, search, sortColumn, sortDirection, ref recordsTotal); recordsFiltered = dataTableData.data.Count(); dataTableData.recordsTotal = recordsTotal; dataTableData.recordsFiltered = (search == string.Empty ? recordsTotal : recordsFiltered); return(Json(dataTableData, JsonRequestBehavior.AllowGet)); } else { return(RedirectToAction("Index", "Account", new { act = "timeout" })); } }
private void GenerateCreateTableScript(JsonDataTable dt, StringBuilder sbScript) { sbScript.AppendFormat("CREATE TABLE {0}(", dt.TableName.Trim().Replace(' ', '_')); sbScript.AppendLine(); foreach (var fieldData in dt.FieldMetaData) { sbScript.AppendFormat("\t{0} {1} NULL,", DecideFieldName(fieldData), OracleDataType(fieldData.DataType)); sbScript.AppendLine(); } sbScript.Length -= 3; //remove last comma and \r\n sbScript.AppendLine(); sbScript.Append(")"); if (dt.Tables.Count > 0) { foreach (var table in dt.Tables) { sbScript.AppendLine(); sbScript.AppendFormat("------------ Table : \"{0}\" ------------", table.Key); sbScript.AppendLine(); GenerateCreateTableScript(table.Value, sbScript); } } sbScript.AppendLine(); }
void GenerateCreateTableScript(JsonDataTable dt, StringBuilder sbScript) { sbScript.AppendFormat("create table if not exists `{0}`(", dt.TableName); sbScript.AppendLine(); foreach (var fieldData in dt.FieldMetaData) { sbScript.AppendFormat("\t`{0}` {1} NULL,", fieldData.FieldName, SqlDataType(fieldData.DataType)); sbScript.AppendLine(); } sbScript.Length -= 3; //remove last comma and \r\n sbScript.AppendLine(); sbScript.Append(");"); if (dt.Tables.Count > 0) { foreach (var table in dt.Tables) { sbScript.AppendLine(); sbScript.AppendFormat("-- ---------- Table : \"{0}\" ------------", table.Key); sbScript.AppendLine(); GenerateCreateTableScript(table.Value, sbScript); } } sbScript.AppendLine(); }
public abstract string ToSqlScript(JsonDataTable jsonDataTable, FilePath filePath);
public abstract StringBuilder ToSqlScript(JsonDataTable jsonDataTable);
/// <summary> /// Send back a JsonDataTableObject as json with all the information that we need to populate datatable /// </summary> /// <param name="draw">Draw order. Client send a draw id in request to keep track of asyncron response</param> /// <param name="start">Start from this item</param> /// <param name="length">Take a list with "lenght" (if exists) objects inside.</param> /// <returns>JsonDataTableObject</returns> public JsonResult JsonTableFill(int draw, int start, int length) { try { const int TOTAL_ROWS = 999; string category = category = Request.HttpContext.Request.Query["category"]; string search = search = Request.Query["search[value]"]; if (String.IsNullOrEmpty(category)) { category = ""; } if (String.IsNullOrEmpty(search)) { search = ""; } int sortColumn = -1; string sortDirection = "asc"; if (length == -1) { length = TOTAL_ROWS; } // note: we only sort one column at a time if (!String.IsNullOrEmpty(Request.Query["order[0][column]"])) { sortColumn = int.Parse(Request.Query["order[0][column]"]); } if (!String.IsNullOrEmpty(Request.Query["order[0][dir]"])) { sortDirection = Request.Query["order[0][dir]"]; } IQueryable <Products> products; //try to parse search to int int searchInteger; if (int.TryParse(search, out searchInteger)) { //list of product that contain "search" products = db.Products.Include(p => p.Category).Include(p => p.Supplier) .Where(p => (p.ProductName.Contains(search) || p.UnitPrice == searchInteger || p.UnitsInStock == searchInteger || p.UnitsOnOrder == searchInteger || p.ReorderLevel == searchInteger || p.Discontinued.ToString().Contains(search)) && p.Category.CategoryName.Contains(category)); } else { //if int cannot be taken products = db.Products.Include(p => p.Category).Include(p => p.Supplier) .Where(p => (p.ProductName.Contains(search) || p.Discontinued.ToString().Contains(search)) && p.Category.CategoryName.Contains(category)); } //order list switch (sortColumn) { case -1: //sort by first column goto FirstColumn; case 0: //first column FirstColumn: if (sortDirection == "asc") { products = products.OrderBy(x => x.ProductName); } else { products = products.OrderByDescending(x => x.ProductName); } break; case 1: //second column if (sortDirection == "asc") { products = products.OrderBy(x => x.UnitPrice); } else { products = products.OrderByDescending(x => x.UnitPrice); } break; case 2: // and so on if (sortDirection == "asc") { products = products.OrderBy(x => x.UnitsInStock); } else { products = products.OrderByDescending(x => x.UnitsInStock); } break; case 3: if (sortDirection == "asc") { products = products.OrderBy(x => x.UnitsOnOrder); } else { products = products.OrderByDescending(x => x.UnitsOnOrder); } break; case 4: if (sortDirection == "asc") { products = products.OrderBy(x => x.ReorderLevel); } else { products = products.OrderByDescending(x => x.ReorderLevel); } break; case 5: if (sortDirection == "asc") { products = products.OrderBy(x => x.Discontinued); } else { products = products.OrderByDescending(x => x.Discontinued); } break; } //objet that whill be sent to client JsonDataTable dataTableData = new JsonDataTable() { draw = draw, recordsTotal = db.Products.Count(), data = products.Skip(start).Take(length).Select(x => new { ID = x.ProductID, ProductName = x.ProductName, Price = x.UnitPrice, InStock = x.UnitsInStock, OnOrders = x.UnitsOnOrder, ReorderLevel = x.ReorderLevel, Discontinued = x.Discontinued }), recordsFiltered = products.Count(), //need to be below data(ref recordsFiltered) }; return(Json(dataTableData)); } catch (Exception e) { //todo //logger.Error(e.ToString()); return(Json(new JsonDataTable() { draw = draw, recordsTotal = db.Products.Count(), error = "Ceva nu a mers bine", recordsFiltered = 0 })); } }
public JsonResult InvoiceListLoad(DatableOption option, FilterCustomer model) { var jsonReturn = new JsonDataTable(); try { using (var db = new FTTxEntities()) { var endDate = DateTime.Now.Date; if (model.EndDate != null) { endDate = model.EndDate.Value.AddDays(1); } var query = (from t1 in db.OINV join t2 in db.OCRD on t1.CardCode equals t2.CardCode where (model.ProvinceCode.Count == 0 || model.ProvinceCode.Contains(t1.CardCode.Substring(1, 2))) where (model.StartDate == null || t1.CreateDate >= model.StartDate) where (model.EndDate == null || t1.CreateDate < endDate) where (model.Status.Count == 0 || model.Status.Contains(t1.DocStatus)) where t2.GroupCode == 103 where (string.IsNullOrEmpty(model.Query) || (t1.CardCode.Contains(model.Query)) || (t1.CardName.Contains(model.Query)) || (t1.DocNum.ToString().Contains(model.Query)) ) select new { DocNum = t1.DocNum, CardCode = t1.CardCode ?? "", CardName = t1.CardName ?? "", DocTotal = t1.DocTotal, CreateDate = t1.CreateDate, PaidToDate = t1.PaidToDate, CancelDate = t1.CancelDate, DocDueDate = t1.DocDueDate, VatSum = t1.VatSum, BeforeVat = t1.DocTotal - t1.VatSum, DocStatus = t1.DocStatus ?? "" }); if (option.order.Count > 0) { var sorting = int.Parse(option.order[0].FirstOrDefault(r => r.Key == "column").Value); var dir = option.order[0].FirstOrDefault(r => r.Key == "dir").Value; switch (sorting) { case 1: query = (dir.ToLower() == "asc" ? query.OrderBy(r => r.DocNum) : query.OrderByDescending(r => r.DocNum)); break; case 2: query = (dir.ToLower() == "asc" ? query.OrderBy(r => r.CardCode) : query.OrderByDescending(r => r.CardCode)); break; case 3: query = (dir.ToLower() == "asc" ? query.OrderBy(r => r.CardName) : query.OrderByDescending(r => r.CardName)); break; case 4: query = (dir.ToLower() == "asc" ? query.OrderBy(r => r.DocTotal) : query.OrderByDescending(r => r.DocTotal)); break; case 5: query = (dir.ToLower() == "asc" ? query.OrderBy(r => r.CreateDate) : query.OrderByDescending(r => r.CreateDate)); break; case 6: query = (dir.ToLower() == "asc" ? query.OrderBy(r => r.DocDueDate) : query.OrderByDescending(r => r.DocDueDate)); break; default: query = (dir.ToLower() == "asc" ? query.OrderBy(r => r.DocNum) : query.OrderByDescending(r => r.DocNum)); break; } } var data = query.Skip(option.start).Take(option.length).ToList(); var count = query.Count(); jsonReturn = new JsonDataTable { status = true, message = "Ok", data = data, draw = option.draw, recordsTotal = count, recordsFiltered = count }; } } catch (Exception ex) { jsonReturn = new JsonDataTable { status = false, message = ex.Message, data = new string[0], draw = option.draw, recordsTotal = 0 }; Log.Error(this.GetType().FullName + "." + System.Reflection.MethodBase.GetCurrentMethod().Name + " Error -> " + ex.Message); } return(Json(jsonReturn)); }
public override string ToSqlScript(JsonDataTable jsonDataTable, FilePath filePath) { throw new NotImplementedException(); }
public JsonResult CustomerListLoad(DatableOption option, FilterCustomer model) { var jsonReturn = new JsonDataTable(); try { using (var db = new FTTxEntities()) { var endDate = DateTime.Now.Date; if (model.EndDate != null) { endDate = model.EndDate.Value.AddDays(1); } var query = (from t1 in db.OCRD where (model.ProvinceCode.Count == 0 || model.ProvinceCode.Contains(t1.CardCode.Substring(1, 2))) where (model.StartDate == null || t1.CreateDate >= model.StartDate) where (model.EndDate == null || t1.CreateDate < endDate) where (model.Status.Count == 0 || model.Status.Contains(t1.frozenFor)) where t1.GroupCode == 103 where (string.IsNullOrEmpty(model.Query) || (t1.CardCode.Contains(model.Query)) || (t1.CardName.Contains(model.Query)) || (t1.Cellular.Contains(model.Query)) || (t1.E_Mail.Contains(model.Query)) ) select new { CardCode = t1.CardCode ?? "", CardName = t1.CardName ?? "", CardNo = t1.LicTradNum ?? "", Cellular = t1.Cellular ?? "", CreateDate = t1.CreateDate, EMail = t1.E_Mail ?? "", Address = t1.Address ?? "", District = t1.Block ?? "", Amphur = t1.County ?? "", Province = t1.City ?? "", ZipCode = t1.ZipCode ?? "", Status = t1.frozenFor ?? "" }); if (option.order.Count > 0) { var sorting = int.Parse(option.order[0].FirstOrDefault(r => r.Key == "column").Value); var dir = option.order[0].FirstOrDefault(r => r.Key == "dir").Value; switch (sorting) { case 1: query = (dir.ToLower() == "asc" ? query.OrderBy(r => r.CardName) : query.OrderByDescending(r => r.CardName)); break; case 2: query = (dir.ToLower() == "asc" ? query.OrderBy(r => r.Cellular) : query.OrderByDescending(r => r.Cellular)); break; case 3: query = (dir.ToLower() == "asc" ? query.OrderBy(r => r.EMail) : query.OrderByDescending(r => r.EMail)); break; case 5: query = (dir.ToLower() == "asc" ? query.OrderBy(r => r.CreateDate) : query.OrderByDescending(r => r.CreateDate)); break; case 6: query = (dir.ToLower() == "asc" ? query.OrderBy(r => r.Status) : query.OrderByDescending(r => r.Status)); break; default: query = (dir.ToLower() == "asc" ? query.OrderBy(r => r.CardCode) : query.OrderByDescending(r => r.CardCode)); break; } } var data = query.Skip(option.start).Take(option.length).ToList(); var count = query.Count(); jsonReturn = new JsonDataTable { status = true, message = "Ok", data = data, draw = option.draw, recordsTotal = count, recordsFiltered = count }; } } catch (Exception ex) { jsonReturn = new JsonDataTable { status = false, message = ex.Message, data = new string[0], draw = option.draw, recordsTotal = 0 }; Log.Error(this.GetType().FullName + "." + System.Reflection.MethodBase.GetCurrentMethod().Name + " Error -> " + ex.Message); } return(Json(jsonReturn)); }
/// <summary> /// Function used to control the dashboard datatables from the server /// </summary> /// <param name="draw"></param> /// <param name="start"></param> /// <param name="length"></param> /// <returns>A JSON filtered employees list.</returns> // GET: Employees by Json public JsonResult JsonTableFill(int draw, int start, int length) { const int TOTAL_ROWS = 999; string search = Request.QueryString["search[value]"] ?? ""; int sortColumn = -1; string sortDirection = "asc"; if (length == -1) { length = TOTAL_ROWS; } // note: we only sort one column at a time if (Request.QueryString["order[0][column]"] != null) { sortColumn = int.Parse(Request.QueryString["order[0][column]"]); } if (Request.QueryString["order[0][dir]"] != null) { sortDirection = Request.QueryString["order[0][dir]"]; } //list of product that contain "search" var list = db.Employees. Where (p => p.LastName.Contains(search) || p.FirstName.Contains(search) || p.City.Contains(search) || p.Title.Contains(search) || p.Country.Contains(search) || p.HomePhone.Contains(search) ); //order list switch (sortColumn) { case -1: //sort by first column goto FirstColumn; case 0: //first column FirstColumn: if (sortDirection == "asc") { list = list.OrderBy(x => x.LastName); } else { list = list.OrderByDescending(x => x.LastName); } break; case 1: //second column if (sortDirection == "asc") { list = list.OrderBy(x => x.FirstName); } else { list = list.OrderByDescending(x => x.FirstName); } break; case 3: // and so on if (sortDirection == "asc") { list = list.OrderBy(x => x.City); } else { list = list.OrderByDescending(x => x.City); } break; case 2: if (sortDirection == "asc") { list = list.OrderBy(x => x.Title); } else { list = list.OrderByDescending(x => x.Title); } break; case 5: if (sortDirection == "asc") { list = list.OrderBy(x => x.HomePhone); } else { list = list.OrderByDescending(x => x.HomePhone); } break; case 4: if (sortDirection == "asc") { list = list.OrderBy(x => x.Country); } else { list = list.OrderByDescending(x => x.Country); } break; } //object that whill be sent to client JsonDataTable dataTableData = new JsonDataTable() { draw = draw, recordsTotal = db.Employees.Count(), data = list.Skip(start).Take(length).Select(x => new { ID = x.EmployeeID, LastName = x.LastName, FirstName = x.FirstName, Title = x.Title, City = x.City, Country = x.Country, HomePhone = x.HomePhone }), recordsFiltered = list.Count(), //need to be below data(ref recordsFiltered) }; return(Json(dataTableData, JsonRequestBehavior.AllowGet)); }
/// <summary> /// Send back a JsonDataTableObject as json with all the information that we need to populate datatable /// </summary> /// <param name="draw">Draw order. Client send a draw id in request to keep track of asyncron response</param> /// <param name="start">Start from this item</param> /// <param name="length">Take a list with "lenght" (if exists) objects inside.</param> /// <returns>JsonDataTableObject</returns> public JsonResult JsonTableFill(int draw, int start, int length, [FromQuery] string json = "") { const int TOTAL_ROWS = 999; //init list of products in shopcart IQueryable <ProductShopCartDetailed> list; if (User.Identity.IsAuthenticated) { list = from s in db.ShopCart join p in db.Products on s.ProductID equals p.ProductID join c in db.Categories on p.CategoryID equals c.CategoryID where s.UserName == User.Identity.Name select new ProductShopCartDetailed { Category = c.CategoryName, ID = s.ProductID, ProductName = p.ProductName, Quantity = s.Quantity, UnitPrice = p.UnitPrice ?? 999999 }; } else { if (json != "") { list = JsonConvert.DeserializeObject <List <ProductShopCartDetailed> >(json).AsQueryable(); } else { list = new List <ProductShopCartDetailed>().AsQueryable(); } } int sortColumn = -1; string sortDirection = "asc"; if (length == -1) { length = TOTAL_ROWS; } // note: we only sort one column at a time #warning "This is unnecessarily complicated. Need to figure out datatable sorting." //iSortCol_0 //if (Request.QueryString["order[0][column]"] != null) //{ // sortColumn = int.Parse(Request.QueryString["order[0][column]"]); //} //if (Request.QueryString["order[0][dir]"] != null) //{ // sortDirection = Request.QueryString["order[0][dir]"]; //} //list of product that contain "search" //order list switch (sortColumn) { case -1: //sort by first column goto FirstColumn; case 0: //first column FirstColumn: if (sortDirection == "asc") { list = list.OrderBy(x => x.ProductName); } else { list = list.OrderByDescending(x => x.ProductName); } break; case 1: //second column if (sortDirection == "asc") { list = list.OrderBy(x => x.Quantity); } else { list = list.OrderByDescending(x => x.Quantity); } break; case 2: // and so on if (sortDirection == "asc") { list = list.OrderBy(x => x.UnitPrice); } else { list = list.OrderByDescending(x => x.UnitPrice); } break; case 3: if (sortDirection == "asc") { list = list.OrderBy(x => (x.UnitPrice * x.Quantity)); } else { list = list.OrderByDescending(x => (x.UnitPrice * x.Quantity)); } break; } //object that whill be sent to client JsonDataTable dataTableData = new JsonDataTable() { draw = draw, recordsTotal = db.Products.Count(), data = list.Skip(start).Take(length).Select(p => new { Category = p.Category, ID = p.ID, ProductName = p.ProductName, Quantity = p.Quantity, UnitPrice = (int)p.UnitPrice, }).AsQueryable(), recordsFiltered = list.Count(), //need to be below data(ref recordsFiltered) }; //return Json(dataTableData, JsonRequestBehavior.AllowGet); return(Json(dataTableData)); }
/// <summary> /// Function used to control the dashboard datatables from the server /// </summary> /// <param name="draw"></param> /// <param name="start"></param> /// <param name="length"></param> /// <returns>A JSON filtered customer list.</returns> public JsonResult JsonTableFill(int draw, int start, int length) { try { const int totalRows = 999; string search = ""; search = Request.QueryString["search[value]"] ?? ""; int sortColumn = -1; string sortDirection = "asc"; if (length == -1) { length = totalRows; } // note: we only sort one column at a time if (Request.QueryString["order[0][column]"] != null) { sortColumn = int.Parse(Request.QueryString["order[0][column]"]); } if (Request.QueryString["order[0][dir]"] != null) { sortDirection = Request.QueryString["order[0][dir]"]; } //list of customers that contain "search" var list = db.Customers.Where(x => x.CompanyName.Contains(search) || x.ContactName.Contains(search) || x.ContactTitle.Contains(search) || x.City.Contains(search) || x.Country.Contains(search) || x.Phone.Contains(search)); //order list switch (sortColumn) { case -1: //sort by first column goto FirstColumn; case 0: //first column FirstColumn: if (sortDirection == "asc") { list = list.OrderBy(x => x.CompanyName); } else { list = list.OrderByDescending(x => x.CompanyName); } break; case 1: //second column if (sortDirection == "asc") { list = list.OrderBy(x => x.ContactName); } else { list = list.OrderByDescending(x => x.ContactName); } break; case 2: // and so on if (sortDirection == "asc") { list = list.OrderBy(x => x.ContactTitle); } else { list = list.OrderByDescending(x => x.ContactTitle); } break; case 3: if (sortDirection == "asc") { list = list.OrderBy(x => x.City); } else { list = list.OrderByDescending(x => x.City); } break; case 4: if (sortDirection == "asc") { list = list.OrderBy(x => x.Country); } else { list = list.OrderByDescending(x => x.Country); } break; case 5: if (sortDirection == "asc") { list = list.OrderBy(x => x.Phone); } else { list = list.OrderByDescending(x => x.Phone); } break; } //objet that whill be sent to client JsonDataTable dataTableData = new JsonDataTable() { draw = draw, recordsTotal = db.Customers.Count(), data = list.Skip(start).Take(length).Select(x => new { ID = x.CustomerID, CompanyName = x.CompanyName, ContactName = x.ContactName, ContactTitle = x.ContactTitle, City = x.City, Country = x.Country, Phone = x.Phone }), recordsFiltered = list.Count(), //need to be below data(ref recordsFiltered) }; return(Json(dataTableData, JsonRequestBehavior.AllowGet)); } catch (Exception e) { logger.Error(e.ToString()); return(Json(new JsonDataTable() { draw = draw, recordsTotal = db.Customers.Count(), error = "Ceva nu a mers bine", recordsFiltered = 0 }, JsonRequestBehavior.AllowGet)); } }
/// <summary> /// Send back a JsonDataTableObject as json with all the information that we need to populate datatable /// </summary> /// <param name="draw">Draw order. Client send a draw id in request to keep track of asyncron response</param> /// <param name="start">Start from this item</param> /// <param name="length">Take a list with "lenght" (if exists) objects inside.</param> /// <returns>JsonDataTableObject</returns> public JsonResult JsonTableFill(int draw, int start, int length) { const int TOTAL_ROWS = 999; string search = ""; try { search = Request.QueryString["search[value]"] ?? ""; } catch (Exception exception) { logger.Error(exception.ToString()); } int sortColumn = -1; string sortDirection = "asc"; if (length == -1) { length = TOTAL_ROWS; } // note: we only sort one column at a time try { if (Request.QueryString["order[0][column]"] != null) { sortColumn = int.Parse(Request.QueryString["order[0][column]"]); } } catch (Exception exception) { logger.Error(exception.ToString()); } try { if (Request.QueryString["order[0][dir]"] != null) { sortDirection = Request.QueryString["order[0][dir]"]; } } catch (Exception exception) { logger.Error(exception.ToString()); } //list of product that contain "search" var suppliersInfo = db.Suppliers.OrderBy(x => x.SupplierID).Where(s => s.CompanyName.Contains(search) || s.ContactName.Contains(search) || s.ContactTitle.Contains(search) || s.Address.Contains(search) || s.City.Contains(search) || s.Country.Contains(search) || s.Phone.Contains(search)); //order list switch (sortColumn) { case -1: //sort by first column goto FirstColumn; case 0: //first column FirstColumn: if (sortDirection == "asc") { suppliersInfo = suppliersInfo.OrderBy(x => x.CompanyName); } else { suppliersInfo = suppliersInfo.OrderByDescending(x => x.CompanyName); } break; case 1: //second column if (sortDirection == "asc") { suppliersInfo = suppliersInfo.OrderBy(x => x.ContactName); } else { suppliersInfo = suppliersInfo.OrderByDescending(x => x.ContactName); } break; case 2: // and so on if (sortDirection == "asc") { suppliersInfo = suppliersInfo.OrderBy(x => x.ContactTitle); } else { suppliersInfo = suppliersInfo.OrderByDescending(x => x.ContactTitle); } break; case 3: if (sortDirection == "asc") { suppliersInfo = suppliersInfo.OrderBy(x => x.Address); } else { suppliersInfo = suppliersInfo.OrderByDescending(x => x.Address); } break; case 4: if (sortDirection == "asc") { suppliersInfo = suppliersInfo.OrderBy(x => x.City); } else { suppliersInfo = suppliersInfo.OrderByDescending(x => x.City); } break; case 5: if (sortDirection == "asc") { suppliersInfo = suppliersInfo.OrderBy(x => x.Country); } else { suppliersInfo = suppliersInfo.OrderByDescending(x => x.Country); } break; case 6: if (sortDirection == "asc") { suppliersInfo = suppliersInfo.OrderBy(x => x.Phone); } else { suppliersInfo = suppliersInfo.OrderByDescending(x => x.Phone); } break; } //objet that whill be sent to client JsonDataTable dataTableData = new JsonDataTable() { draw = draw, recordsTotal = db.Suppliers.Count(), data = suppliersInfo.Skip(start).Take(length).Select(x => new { SupplierID = x.SupplierID, CompanyName = x.CompanyName, ContactName = x.ContactName, ContactTitle = x.ContactTitle, Address = x.Address, City = x.City, Country = x.Country, Phone = x.Phone }), recordsFiltered = suppliersInfo.Count(), //need to be below data(ref recordsFiltered) }; return(Json(dataTableData, JsonRequestBehavior.AllowGet)); }