using Microsoft.AspNetCore.Mvc; using System; using System.Linq; using NorthwindAPI.BusinessObject; using NorthwindAPI.Models; using NorthwindAPI.ViewModels; using NorthwindAPI.Domain; using System.Collections.Generic; using Microsoft.Extensions.Options; using System.Threading.Tasks; using System.Text; using System.Text.Json; namespace Northwind.Controllers.Base { /// <summary> /// Base class for ProductsController. Do not make changes to this class, /// instead, put additional code in the ProductsController class /// </summary> public class ProductsControllerBase : Controller { #region actions used by their respective views /// <summary> /// GET: /Products/ /// Gets the view used by the Index razor view /// </summary> public async Task<IActionResult> Index() { // return the View return await Task.Run(() => View()); } /// <summary> /// GET: /Products/Add /// Gets the view model used by the Add razor view /// </summary> public async Task<IActionResult> Add() { // return the view model return await GetAddViewModelAsync(); } /// <summary> /// POST: /Products/Add /// Posts values from the Add razor view /// </summary> [HttpPost] [ValidateAntiForgeryToken] public async Task<IActionResult> Add(ProductsViewModel viewModel, string returnUrl) { if (ModelState.IsValid) { try { // add new record await AddEditProductsAsync(viewModel, CrudOperation.Add); if (Url.IsLocalUrl(returnUrl)) return Redirect(returnUrl); else return RedirectToAction("ListCrudRedirect", "Products"); } catch(Exception ex) { if (ex.InnerException != null) ModelState.AddModelError("", ex.InnerException.Message); else ModelState.AddModelError("", ex.Message); } } // if we got this far, something failed, redisplay form return await GetAddViewModelAsync(); } /// <summary> /// GET: /Products/Update/* /// Gets the view model used by the Update razor view /// </summary> public async Task<IActionResult> Update(int id) { // return view model return await GetUpdateViewModelAsync(id); } /// <summary> /// POST: /Products/Update/# /// Posts values from the Update razor view /// </summary> [HttpPost] [ValidateAntiForgeryToken] public async Task<IActionResult> Update(int id, ProductsViewModel viewModel, string returnUrl) { if (ModelState.IsValid) { try { // update record await AddEditProductsAsync(viewModel, CrudOperation.Update); if (Url.IsLocalUrl(returnUrl)) return Redirect(returnUrl); else return RedirectToAction("ListCrudRedirect", "Products"); } catch(Exception ex) { if (ex.InnerException != null) ModelState.AddModelError("", ex.InnerException.Message); else ModelState.AddModelError("", ex.Message); } } // if we got this far, something failed, redisplay form return await GetUpdateViewModelAsync(id); } /// <summary> /// GET: /Details/Details/# /// Gets the view model used by the Details razor view /// </summary> public async Task<IActionResult> Details(int id) { // select a record by primary key(s) Products objProducts = await Products.SelectByPrimaryKeyAsync(id); // assign values to the model ProductsModel model = new ProductsModel(); model.ProductID = objProducts.ProductID; model.ProductName = objProducts.ProductName; model.SupplierID = objProducts.SupplierID; model.CategoryID = objProducts.CategoryID; model.QuantityPerUnit = objProducts.QuantityPerUnit; model.UnitPrice = objProducts.UnitPrice; model.UnitsInStock = objProducts.UnitsInStock; if (objProducts.UnitsInStock.HasValue) model.UnitsInStockHidden = objProducts.UnitsInStock.Value.ToString(); else model.UnitsInStockHidden = null; model.UnitsOnOrder = objProducts.UnitsOnOrder; if (objProducts.UnitsOnOrder.HasValue) model.UnitsOnOrderHidden = objProducts.UnitsOnOrder.Value.ToString(); else model.UnitsOnOrderHidden = null; model.ReorderLevel = objProducts.ReorderLevel; if (objProducts.ReorderLevel.HasValue) model.ReorderLevelHidden = objProducts.ReorderLevel.Value.ToString(); else model.ReorderLevelHidden = null; model.Discontinued = objProducts.Discontinued; // assign values to the view model ProductsViewModel viewModel = new ProductsViewModel(); viewModel.ProductsModel = model; viewModel.SuppliersDropDownListData = await Suppliers.SelectSuppliersDropDownListDataAsync(); viewModel.CategoriesDropDownListData = await Categories.SelectCategoriesDropDownListDataAsync(); viewModel.ViewReturnUrl = "/Products/ListCrudRedirect"; viewModel.URLReferrer = Request.Headers["Referer"].ToString(); // return the view model return View(viewModel); } /// <summary> /// GET: /Products/ListCrudRedirect /// Gets the view used by the ListCrudRedirect razor view /// </summary> public async Task<IActionResult> ListCrudRedirect() { // return the View return await Task.Run(() => View()); } /// <summary> /// GET: /Products/ListReadOnly /// Gets the view used by the ListReadOnly razor view /// </summary> public async Task<IActionResult> ListReadOnly() { // return the View return await Task.Run(() => View()); } /// <summary> /// GET: /Products/ListCrud /// Gets the view model used by the ListCrud razor view /// </summary> public async Task<IActionResult> ListCrud() { // return view model ProductsViewModel viewModel = await GetViewModelAsync("ListCrud"); return View(viewModel); } /// <summary> /// POST: /Products/ListCrud /// Posts values from the ListCrud /// </summary> [HttpPost] public async Task<IActionResult> ListCrud(string inputSubmit, string serializedData) { // deserialize serializedData Products objProducts = Newtonsoft.Json.JsonConvert.DeserializeObject<Products>(serializedData); // assign a value to the view model's Model property ProductsViewModel viewModel = new ProductsViewModel(); viewModel.ProductsModel = objProducts; if (ModelState.IsValid) { CrudOperation operation = CrudOperation.Add; if (inputSubmit == "Update") operation = CrudOperation.Update; try { // add a new record or update an existing record await AddEditProductsAsync(viewModel, operation, true); } catch(Exception ex) { ModelState.AddModelError("", ex.Message); } } return Json(true); } /// <summary> /// GET: /Products/ListTotals /// Gets the view used by the ListTotals razor view /// </summary> public async Task<IActionResult> ListTotals() { // return the View return await Task.Run(() => View()); } /// <summary> /// GET: /Products/ListSearch /// Gets the view model used by the ListSearch razor view /// </summary> public async Task<IActionResult> ListSearch() { // return view model ProductsViewModel viewModel = await GetViewModelAsync("ListSearch"); return View(viewModel); } /// <summary> /// GET: /Products/ListScrollLoad /// Gets the view used by the ListScrollLoad razor view /// </summary> public async Task<IActionResult> ListScrollLoad() { // return the View return await Task.Run(() => View()); } /// <summary> /// GET: /Products/ListInline /// Gets the view model used by the ListInline razor view /// </summary> public async Task<IActionResult> ListInline() { // return view model ProductsViewModel viewModel = await GetViewModelAsync("ListInline"); return View(viewModel); } /// <summary> /// POST: /Products/ListInlineAdd /// Posts the values from the ListInlineAdd /// </summary> [HttpPost] public async Task<IActionResult> ListInlineAdd([FromBody]string modelString) { // deserialize modelString ProductsViewModel viewModel = new ProductsViewModel(); viewModel.ProductsModel = Newtonsoft.Json.JsonConvert.DeserializeObject<ProductsModel>("{" + modelString + "}"); // add new record await AddEditProductsAsync(viewModel, CrudOperation.Add, true); return Json(true); } /// <summary> /// POST: /Products/ListInlineUpdate /// Posts the values from the ListInlineUpdate /// </summary> [HttpPost] public async Task<IActionResult> ListInlineUpdate([FromBody]string modelString) { // deserialize modelString ProductsViewModel viewModel = new ProductsViewModel(); viewModel.ProductsModel = Newtonsoft.Json.JsonConvert.DeserializeObject<ProductsModel>("{" + modelString + "}"); // update record await AddEditProductsAsync(viewModel, CrudOperation.Update, true); return Json(true); } /// <summary> /// GET: /Products/ListForeach /// Gets the view model used by the ListForeach razor view /// </summary> public async Task<IActionResult> ListForeach(string sidx, string sord, int? page) { // get the default number of rows to show int rows = Functions.GetGridNumberOfRows(); // the numberOfPagesToShow is used to show the "< First..." link in the razor view int numberOfPagesToShow = Functions.GetGridNumberOfPagesToShow(); // when page is null, set it to 1 int currentPage = page is null ? 1 : Convert.ToInt32(page); // get the index where to start retrieving records from // 1 = starts from the beggining, 11 means skip the first 10 records and start from record 11 int startRowIndex = ((currentPage * rows) - rows) + 1; // get the total number of records int totalRecords = await Products.GetRecordCountAsync(); // calculate the total number of pages int totalPages = (int)Math.Ceiling((float)totalRecords / (float)rows); // get records based on filters List<Products> objProductsCol = await Products.SelectSkipAndTakeAsync(rows, startRowIndex, sidx + " " + sord); // fields and titles string[,] fieldNames = new string[,] { {"ProductID", "Product ID"}, {"ProductName", "Product Name"}, {"SupplierID", "Supplier ID"}, {"CategoryID", "Category ID"}, {"QuantityPerUnit", "Quantity Per Unit"}, {"UnitPrice", "Unit Price"}, {"UnitsInStock", "Units In Stock"}, {"UnitsOnOrder", "Units On Order"}, {"ReorderLevel", "Reorder Level"}, {"Discontinued", "Discontinued"} }; // view model ProductsForeachViewModel viewModel = new ProductsForeachViewModel(); viewModel.ProductsData = objProductsCol; viewModel.ProductsFieldNames = fieldNames; viewModel.TotalPages = totalPages; viewModel.CurrentPage = currentPage; viewModel.FieldToSort = String.IsNullOrEmpty(sidx) ? "ProductID" : sidx; viewModel.FieldSortOrder = String.IsNullOrEmpty(sord) ? "asc" : sord; viewModel.FieldToSortWithOrder = String.IsNullOrEmpty(sidx) ? "ProductID" : (sidx + " " + sord).Trim(); viewModel.NumberOfPagesToShow = numberOfPagesToShow; viewModel.StartPage = Functions.GetPagerStartPage(currentPage, numberOfPagesToShow); viewModel.EndPage = Functions.GetPagerEndPage(viewModel.StartPage, numberOfPagesToShow, totalPages); // return the view model return View(viewModel); } /// <summary> /// GET: /Products/ListMasterDetailGridBySupplierID /// Gets the view used by the ListMasterDetailGridBySupplierID razor view /// </summary> public async Task<IActionResult> ListMasterDetailGridBySupplierID() { // return the View return await Task.Run(() => View()); } /// <summary> /// GET: /Products/ListMasterDetailGridByCategoryID /// Gets the view used by the ListMasterDetailGridByCategoryID razor view /// </summary> public async Task<IActionResult> ListMasterDetailGridByCategoryID() { // return the View return await Task.Run(() => View()); } /// <summary> /// GET: /Products/ListMasterDetailSubGridBySupplierID /// Gets the view used by the ListMasterDetailSubGridBySupplierID razor view /// </summary> public async Task<IActionResult> ListMasterDetailSubGridBySupplierID() { // return the View return await Task.Run(() => View()); } /// <summary> /// GET: /Products/ListMasterDetailSubGridByCategoryID /// Gets the view used by the ListMasterDetailSubGridByCategoryID razor view /// </summary> public async Task<IActionResult> ListMasterDetailSubGridByCategoryID() { // return the View return await Task.Run(() => View()); } /// <summary> /// GET: /Products/ListMultipleDelete /// Gets the view used by the ListMultipleDelete razor view /// </summary> public async Task<IActionResult> ListMultipleDelete() { // return the View return await Task.Run(() => View()); } /// <summary> /// GET: /Products/ListGroupedBySupplierID /// Gets the view used by the ListGroupedBySupplierID razor view /// </summary> public async Task<IActionResult> ListGroupedBySupplierID() { // return the View return await Task.Run(() => View()); } /// <summary> /// GET: /Products/ListGroupedByCategoryID /// Gets the view used by the ListGroupedByCategoryID razor view /// </summary> public async Task<IActionResult> ListGroupedByCategoryID() { // return the View return await Task.Run(() => View()); } /// <summary> /// GET: /Products/ListTotalsGroupedBySupplierID /// Gets the view used by the ListTotalsGroupedBySupplierID razor view /// </summary> public async Task<IActionResult> ListTotalsGroupedBySupplierID() { // return the View return await Task.Run(() => View()); } /// <summary> /// GET: /Products/ListTotalsGroupedByCategoryID /// Gets the view used by the ListTotalsGroupedByCategoryID razor view /// </summary> public async Task<IActionResult> ListTotalsGroupedByCategoryID() { // return the View return await Task.Run(() => View()); } /// <summary> /// Gets the view used by the ListBySupplierID razor view /// </summary> public async Task<IActionResult> ListBySupplierID() { // instantiate a new ProductsViewModel ProductsViewModel viewModel = new ProductsViewModel(); // assign values to the view model viewModel.SuppliersDropDownListData = await Suppliers.SelectSuppliersDropDownListDataAsync(); // return the view model return await Task.Run(() => View(viewModel)); } /// <summary> /// Gets the view used by the ListByCategoryID razor view /// </summary> public async Task<IActionResult> ListByCategoryID() { // instantiate a new ProductsViewModel ProductsViewModel viewModel = new ProductsViewModel(); // assign values to the view model viewModel.CategoriesDropDownListData = await Categories.SelectCategoriesDropDownListDataAsync(); // return the view model return await Task.Run(() => View(viewModel)); } /// <summary> /// GET: /Products/Unbound /// Gets the view model used by the Unbound razor view /// </summary> public IActionResult Unbound() { // return view model return View(GetUnboundViewModel()); } /// <summary> /// POST: /Products/Unbound /// Post values fromy the Unbound razor view /// </summary> [HttpPost] [ValidateAntiForgeryToken] public IActionResult Unbound(ProductsViewModel viewModel, string returnUrl) { if (ModelState.IsValid) { // do something here before redirecting if (Url.IsLocalUrl(returnUrl)) return Redirect(returnUrl); else return RedirectToAction("Index", "Home"); } // if we got this far, something failed, redisplay form return View(GetUnboundViewModel()); } #endregion #region public methods /// <summary> /// POST: /Products/Delete/# /// Deletes a record based on the id(s) /// </summary> [HttpPost] public async Task<IActionResult> Delete(int id) { // delete record await Products.DeleteAsync(id); return Json(true); } /// <summary> /// POST: /Products/DeleteMultiple/ids /// Deletes multiple records based on the comma-delimited ids /// </summary> [HttpPost] public async Task<IActionResult> DeleteMultiple(string ids) { // split ids into a List List<Int32> productIDList = ids.Split(",").Select(Int32.Parse).ToList(); // delete multiple records await Products.DeleteMultipleAsync(productIDList); return Json(true); } #endregion #region private methods /// <summary> /// Gets the view model used by the Add razor view /// </summary> private async Task<IActionResult> GetAddViewModelAsync() { // instantiate a new ProductsViewModel ProductsViewModel viewModel = new ProductsViewModel(); // assign values to the view model viewModel.ProductsModel = null; viewModel.Operation = CrudOperation.Add; viewModel.ViewControllerName = "Products"; viewModel.ViewActionName = "Add"; viewModel.SuppliersDropDownListData = await Suppliers.SelectSuppliersDropDownListDataAsync(); viewModel.CategoriesDropDownListData = await Categories.SelectCategoriesDropDownListDataAsync(); viewModel.ViewReturnUrl = "/Products/ListCrudRedirect"; // return the view model return await Task.Run(() => View(viewModel)); } /// <summary> /// Gets the view model used by the Update razor view /// </summary> private async Task<IActionResult> GetUpdateViewModelAsync(int id) { // select a record by primary key(s) Products objProducts = await Products.SelectByPrimaryKeyAsync(id); // assign values to the model ProductsModel model = new ProductsModel(); model.ProductID = objProducts.ProductID; model.ProductName = objProducts.ProductName; model.SupplierID = objProducts.SupplierID; model.CategoryID = objProducts.CategoryID; model.QuantityPerUnit = objProducts.QuantityPerUnit; model.UnitPrice = objProducts.UnitPrice; model.UnitsInStock = objProducts.UnitsInStock; if (objProducts.UnitsInStock.HasValue) model.UnitsInStockHidden = objProducts.UnitsInStock.Value.ToString(); else model.UnitsInStockHidden = null; model.UnitsOnOrder = objProducts.UnitsOnOrder; if (objProducts.UnitsOnOrder.HasValue) model.UnitsOnOrderHidden = objProducts.UnitsOnOrder.Value.ToString(); else model.UnitsOnOrderHidden = null; model.ReorderLevel = objProducts.ReorderLevel; if (objProducts.ReorderLevel.HasValue) model.ReorderLevelHidden = objProducts.ReorderLevel.Value.ToString(); else model.ReorderLevelHidden = null; model.Discontinued = objProducts.Discontinued; // assign values to the view model ProductsViewModel viewModel = new ProductsViewModel(); viewModel.ProductsModel = model; viewModel.Operation = CrudOperation.Update; viewModel.ViewControllerName = "Products"; viewModel.ViewActionName = "Update"; viewModel.SuppliersDropDownListData = await Suppliers.SelectSuppliersDropDownListDataAsync(); viewModel.CategoriesDropDownListData = await Categories.SelectCategoriesDropDownListDataAsync(); viewModel.ViewReturnUrl = "/Products/ListCrudRedirect"; viewModel.URLReferrer = Request.Headers["Referer"].ToString(); // return the view model return View(viewModel); } /// <summary> /// Gets the view model used by the Unbound razor view /// </summary> private ProductsViewModel GetUnboundViewModel() { // instantiate a new ProductsViewModel ProductsViewModel viewModel = new ProductsViewModel(); // assign values to the view model viewModel.ProductsModel = null; viewModel.ViewControllerName = "Products"; viewModel.ViewActionName = "Unbound"; viewModel.ViewReturnUrl = "/Home"; // return the view model return viewModel; } /// <summary> /// Used when adding a new record or updating an existing record /// </summary> /// <param name="viewModel">ProductsViewModel</param> /// <param name="operation">Operation to Add a new record or Update an existing record</param> /// <param name="isForListInlineOrListCrud">Used by the razor Views with ListInline or ListCrud when true</param> /// <returns>Task of IActionResult</returns> private async Task<IActionResult> AddEditProductsAsync(ProductsViewModel viewModel, CrudOperation operation, bool isForListInlineOrListCrud = false) { ProductsModel model = viewModel.ProductsModel; try { Products objProducts; // create a new instance of the Products when adding a new record // or, retrieve the record that needs to be updated if (operation == CrudOperation.Add) objProducts = new Products(); else objProducts = await Products.SelectByPrimaryKeyAsync(model.ProductID); // assign values to the Products instance objProducts.ProductID = model.ProductID; objProducts.ProductName = model.ProductName; objProducts.SupplierID = model.SupplierID; objProducts.CategoryID = model.CategoryID; objProducts.QuantityPerUnit = model.QuantityPerUnit; objProducts.UnitPrice = model.UnitPrice; objProducts.Discontinued = model.Discontinued; if (isForListInlineOrListCrud) { objProducts.UnitsInStock = model.UnitsInStock; objProducts.UnitsOnOrder = model.UnitsOnOrder; objProducts.ReorderLevel = model.ReorderLevel; } else { if(!String.IsNullOrEmpty(model.UnitsInStockHidden)) objProducts.UnitsInStock = Convert.ToInt16(model.UnitsInStockHidden); else objProducts.UnitsInStock = null; if(!String.IsNullOrEmpty(model.UnitsOnOrderHidden)) objProducts.UnitsOnOrder = Convert.ToInt16(model.UnitsOnOrderHidden); else objProducts.UnitsOnOrder = null; if(!String.IsNullOrEmpty(model.ReorderLevelHidden)) objProducts.ReorderLevel = Convert.ToInt16(model.ReorderLevelHidden); else objProducts.ReorderLevel = null; } // save the new record, or the updated values of the current record if (operation == CrudOperation.Add) await objProducts.InsertAsync(); else await objProducts.UpdateAsync(); // everthing went well return Ok(); } catch (Exception ex) { // something went wrong return BadRequest("Error Message: " + ex.Message + " Stack Trace: " + ex.StackTrace); } } /// <summary> /// Gets the view model based on an actionName /// </summary> private async Task<ProductsViewModel> GetViewModelAsync(string actionName) { // instantiate a new ProductsViewModel ProductsViewModel viewModel = new ProductsViewModel(); // assign values to the view model viewModel.ProductsModel = null; viewModel.ViewControllerName = "Products"; viewModel.ViewActionName = actionName; viewModel.SuppliersDropDownListData = await Suppliers.SelectSuppliersDropDownListDataAsync(); viewModel.CategoriesDropDownListData = await Categories.SelectCategoriesDropDownListDataAsync(); // return the view model return viewModel; } /// <summary> /// Selects records as a collection (List) of Products sorted by the sortByExpression filtered by the passed parameters /// </summary> private async Task<List<Products>> GetFilteredDataAsync(int? productID, string productName, int? supplierID, int? categoryID, string quantityPerUnit, decimal? unitPrice, Int16? unitsInStock, Int16? unitsOnOrder, Int16? reorderLevel, bool? discontinued, string sidx, string sord, int rows, int startRowIndex, string sortExpression) { if (productID != null || !String.IsNullOrEmpty(productName) || supplierID != null || categoryID != null || !String.IsNullOrEmpty(quantityPerUnit) || unitPrice != null || unitsInStock != null || unitsOnOrder != null || reorderLevel != null || discontinued != null) return await Products.SelectSkipAndTakeDynamicWhereAsync(productID, productName, supplierID, categoryID, quantityPerUnit, unitPrice, unitsInStock, unitsOnOrder, reorderLevel, discontinued, rows, startRowIndex, sortExpression); return await Products.SelectSkipAndTakeAsync(rows, startRowIndex, sortExpression); } #endregion #region methods that return data in json format used by the jqgrid /// <summary> /// GET: /Products/GridData /// Gets the json needed by the jqgrid for use by the GridData /// </summary> public async Task<IActionResult> GridData(string sidx, string sord, int page, int rows) { // get the index where to start retrieving records from // 0 = starts from the beggining, 10 means skip the first 10 records and start from record 11 int startRowIndex = ((page * rows) - rows); // get the total number of records int totalRecords = await Products.GetRecordCountAsync(); // get records List<Products> objProductsCol = await Products.SelectSkipAndTakeAsync(rows, startRowIndex, sidx + " " + sord); // calculate the total number of pages int totalPages = (int)Math.Ceiling((float)totalRecords / (float)rows); // return a null in json for use by the jqgrid if (objProductsCol is null) return Json("{ total = 0, page = 0, records = 0, rows = null }"); // create a serialized json object for use by the jqgrid var jsonData = new { total = totalPages, page, records = totalRecords, rows = ( from objProducts in objProductsCol select new { id = objProducts.ProductID, cell = new string[] { objProducts.ProductID.ToString(), objProducts.ProductName, objProducts.SupplierID.HasValue ? objProducts.SupplierID.Value.ToString() : "", objProducts.CategoryID.HasValue ? objProducts.CategoryID.Value.ToString() : "", objProducts.QuantityPerUnit, objProducts.UnitPrice.HasValue ? objProducts.UnitPrice.Value.ToString() : "", objProducts.UnitsInStock.HasValue ? objProducts.UnitsInStock.Value.ToString() : "", objProducts.UnitsOnOrder.HasValue ? objProducts.UnitsOnOrder.Value.ToString() : "", objProducts.ReorderLevel.HasValue ? objProducts.ReorderLevel.Value.ToString() : "", objProducts.Discontinued.ToString() } }).ToArray() }; return Json(jsonData); } /// <summary> /// GET: /Products/GridDataWithFilters /// Gets the json needed by the jqgrid for use by the GridDataWithFilters /// </summary> public async Task<IActionResult> GridDataWithFilters(string _search, string nd, int rows, int page, string sidx, string sord, string filters = "") { int? productID = null; string productName = String.Empty; int? supplierID = null; int? categoryID = null; string quantityPerUnit = String.Empty; decimal? unitPrice = null; Int16? unitsInStock = null; Int16? unitsOnOrder = null; Int16? reorderLevel = null; bool? discontinued = null; if (!String.IsNullOrEmpty(filters)) { // deserialize filters and get values being searched var jsonResult = Newtonsoft.Json.JsonConvert.DeserializeObject<Dictionary<string, dynamic>>(filters); foreach (var rule in jsonResult["rules"]) { if (rule["field"].Value.ToLower() == "productid") productID = Convert.ToInt32(rule["data"].Value); if (rule["field"].Value.ToLower() == "productname") productName = rule["data"].Value; if (rule["field"].Value.ToLower() == "supplierid") supplierID = Convert.ToInt32(rule["data"].Value); if (rule["field"].Value.ToLower() == "categoryid") categoryID = Convert.ToInt32(rule["data"].Value); if (rule["field"].Value.ToLower() == "quantityperunit") quantityPerUnit = rule["data"].Value; if (rule["field"].Value.ToLower() == "unitprice") unitPrice = Convert.ToDecimal(rule["data"].Value); if (rule["field"].Value.ToLower() == "unitsinstock") unitsInStock = Convert.ToInt16(rule["data"].Value); if (rule["field"].Value.ToLower() == "unitsonorder") unitsOnOrder = Convert.ToInt16(rule["data"].Value); if (rule["field"].Value.ToLower() == "reorderlevel") reorderLevel = Convert.ToInt16(rule["data"].Value); if (rule["field"].Value.ToLower() == "discontinued") discontinued = Convert.ToBoolean(rule["data"].Value); } // sometimes jqgrid assigns a -1 to numeric fields when no value is assigned // instead of assigning a null, we'll correct this here if (productID == -1) productID = null; if (supplierID == -1) supplierID = null; if (categoryID == -1) categoryID = null; if (unitPrice == -1) unitPrice = null; if (unitsInStock == -1) unitsInStock = null; if (unitsOnOrder == -1) unitsOnOrder = null; if (reorderLevel == -1) reorderLevel = null; } // get the index where to start retrieving records from // 0 = starts from the beggining, 10 means skip the first 10 records and start from record 11 int startRowIndex = ((page * rows) - rows); // get records based on filters int totalRecords = await Products.GetRecordCountDynamicWhereAsync(productID, productName, supplierID, categoryID, quantityPerUnit, unitPrice, unitsInStock, unitsOnOrder, reorderLevel, discontinued); // get records based on filters List<Products> objProductsCol = await Products.SelectSkipAndTakeDynamicWhereAsync(productID, productName, supplierID, categoryID, quantityPerUnit, unitPrice, unitsInStock, unitsOnOrder, reorderLevel, discontinued, rows, startRowIndex, sidx + " " + sord); // calculate the total number of pages int totalPages = (int)Math.Ceiling((float)totalRecords / (float)rows); // return a null in json for use by the jqgrid if (objProductsCol is null) return Json("{ total = 0, page = 0, records = 0, rows = null }"); // create a serialized json object for use by the jqgrid var jsonData = new { total = totalPages, page, records = totalRecords, rows = ( from objProducts in objProductsCol select new { id = objProducts.ProductID, cell = new string[] { objProducts.ProductID.ToString(), objProducts.ProductName, objProducts.SupplierID.HasValue ? objProducts.SupplierID.Value.ToString() : "", objProducts.CategoryID.HasValue ? objProducts.CategoryID.Value.ToString() : "", objProducts.QuantityPerUnit, objProducts.UnitPrice.HasValue ? objProducts.UnitPrice.Value.ToString() : "", objProducts.UnitsInStock.HasValue ? objProducts.UnitsInStock.Value.ToString() : "", objProducts.UnitsOnOrder.HasValue ? objProducts.UnitsOnOrder.Value.ToString() : "", objProducts.ReorderLevel.HasValue ? objProducts.ReorderLevel.Value.ToString() : "", objProducts.Discontinued.ToString() } }).ToArray() }; return Json(jsonData); } /// <summary> /// GET: /Products/GridDataWithTotals /// Gets the json needed by the jqgrid for use by the GridDataWithTotals /// </summary> public async Task<IActionResult> GridDataWithTotals(string sidx, string sord, int page, int rows) { // get the index where to start retrieving records from // 0 = starts from the beggining, 10 means skip the first 10 records and start from record 11 int startRowIndex = ((page * rows) - rows); // get the total number of records int totalRecords = await Products.GetRecordCountAsync(); // get records List<Products> objProductsCol = await Products.SelectSkipAndTakeAsync(rows, startRowIndex, sidx + " " + sord); // calculate the total number of pages int totalPages = (int)Math.Ceiling((float)totalRecords / (float)rows); // return a null in json for use by the jqgrid if (objProductsCol is null) return Json("{ total = 0, page = 0, records = 0, rows = null }"); // create a serialized json object for use by the jqgrid var jsonData = new { total = totalPages, page, records = totalRecords, rows = ( from objProducts in objProductsCol select new { id = objProducts.ProductID, cell = new string[] { objProducts.ProductID.ToString(), objProducts.ProductName, objProducts.SupplierID.HasValue ? objProducts.SupplierID.Value.ToString() : "", objProducts.CategoryID.HasValue ? objProducts.CategoryID.Value.ToString() : "", objProducts.QuantityPerUnit, objProducts.UnitPrice.HasValue ? objProducts.UnitPrice.Value.ToString() : "", objProducts.UnitsInStock.HasValue ? objProducts.UnitsInStock.Value.ToString() : "", objProducts.UnitsOnOrder.HasValue ? objProducts.UnitsOnOrder.Value.ToString() : "", objProducts.ReorderLevel.HasValue ? objProducts.ReorderLevel.Value.ToString() : "", objProducts.Discontinued.ToString() } }).ToArray() }; return Json(jsonData); } /// <summary> /// GET: /Products/GridDataGroupedBySupplierID /// Gets the json needed by the jqgrid for use by the GridDataGroupedBySupplierID /// </summary> public async Task<IActionResult> GridDataGroupedBySupplierID(string sidx, string sord, int page, int rows) { // using a groupBy field in the jqgrid passes that field // along with the field to sort, remove the groupBy field string groupBy = "CompanyName asc, "; sidx = sidx.Replace(groupBy, ""); // get the index where to start retrieving records from // 0 = starts from the beggining, 10 means skip the first 10 records and start from record 11 int startRowIndex = ((page * rows) - rows); // get the total number of records int totalRecords = await Products.GetRecordCountAsync(); // get records List<Products> objProductsCol = await Products.SelectSkipAndTakeAsync(rows, startRowIndex, sidx + " " + sord); // calculate the total number of pages int totalPages = (int)Math.Ceiling((float)totalRecords / (float)rows); // return a null in json for use by the jqgrid if (objProductsCol is null) return Json("{ total = 0, page = 0, records = 0, rows = null }"); // create a serialized json object for use by the jqgrid var jsonData = new { total = totalPages, page, records = totalRecords, rows = ( from objProducts in objProductsCol select new { id = objProducts.ProductID, cell = new string[] { objProducts.ProductID.ToString(), objProducts.ProductName, objProducts.SupplierID.HasValue ? objProducts.SupplierID.Value.ToString() : "", objProducts.CategoryID.HasValue ? objProducts.CategoryID.Value.ToString() : "", objProducts.QuantityPerUnit, objProducts.UnitPrice.HasValue ? objProducts.UnitPrice.Value.ToString() : "", objProducts.UnitsInStock.HasValue ? objProducts.UnitsInStock.Value.ToString() : "", objProducts.UnitsOnOrder.HasValue ? objProducts.UnitsOnOrder.Value.ToString() : "", objProducts.ReorderLevel.HasValue ? objProducts.ReorderLevel.Value.ToString() : "", objProducts.Discontinued.ToString(), objProducts.SupplierID is null ? "" : objProducts.Suppliers.Value.Result.CompanyName } }).ToArray() }; return Json(jsonData); } /// <summary> /// GET: /Products/GridDataGroupedByCategoryID /// Gets the json needed by the jqgrid for use by the GridDataGroupedByCategoryID /// </summary> public async Task<IActionResult> GridDataGroupedByCategoryID(string sidx, string sord, int page, int rows) { // using a groupBy field in the jqgrid passes that field // along with the field to sort, remove the groupBy field string groupBy = "CategoryName asc, "; sidx = sidx.Replace(groupBy, ""); // get the index where to start retrieving records from // 0 = starts from the beggining, 10 means skip the first 10 records and start from record 11 int startRowIndex = ((page * rows) - rows); // get the total number of records int totalRecords = await Products.GetRecordCountAsync(); // get records List<Products> objProductsCol = await Products.SelectSkipAndTakeAsync(rows, startRowIndex, sidx + " " + sord); // calculate the total number of pages int totalPages = (int)Math.Ceiling((float)totalRecords / (float)rows); // return a null in json for use by the jqgrid if (objProductsCol is null) return Json("{ total = 0, page = 0, records = 0, rows = null }"); // create a serialized json object for use by the jqgrid var jsonData = new { total = totalPages, page, records = totalRecords, rows = ( from objProducts in objProductsCol select new { id = objProducts.ProductID, cell = new string[] { objProducts.ProductID.ToString(), objProducts.ProductName, objProducts.SupplierID.HasValue ? objProducts.SupplierID.Value.ToString() : "", objProducts.CategoryID.HasValue ? objProducts.CategoryID.Value.ToString() : "", objProducts.QuantityPerUnit, objProducts.UnitPrice.HasValue ? objProducts.UnitPrice.Value.ToString() : "", objProducts.UnitsInStock.HasValue ? objProducts.UnitsInStock.Value.ToString() : "", objProducts.UnitsOnOrder.HasValue ? objProducts.UnitsOnOrder.Value.ToString() : "", objProducts.ReorderLevel.HasValue ? objProducts.ReorderLevel.Value.ToString() : "", objProducts.Discontinued.ToString(), objProducts.CategoryID is null ? "" : objProducts.Categories.Value.Result.CategoryName } }).ToArray() }; return Json(jsonData); } /// <summary> /// GET: /Products/GridDataTotalsGroupedBySupplierID /// Gets the json needed by the jqgrid for use by the GridDataTotalsGroupedBySupplierID /// </summary> public async Task<IActionResult> GridDataTotalsGroupedBySupplierID(string sidx, string sord, int page, int rows) { // using a groupBy field in the jqgrid passes that field // along with the field to sort, remove the groupBy field string groupBy = "CompanyName asc, "; sidx = sidx.Replace(groupBy, ""); // get the index where to start retrieving records from // 0 = starts from the beggining, 10 means skip the first 10 records and start from record 11 int startRowIndex = ((page * rows) - rows); // get the total number of records int totalRecords = await Products.GetRecordCountAsync(); // get records List<Products> objProductsCol = await Products.SelectSkipAndTakeAsync(rows, startRowIndex, sidx + " " + sord); // calculate the total number of pages int totalPages = (int)Math.Ceiling((float)totalRecords / (float)rows); // return a null in json for use by the jqgrid if (objProductsCol is null) return Json("{ total = 0, page = 0, records = 0, rows = null }"); // create a serialized json object for use by the jqgrid var jsonData = new { total = totalPages, page, records = totalRecords, rows = ( from objProducts in objProductsCol select new { id = objProducts.ProductID, cell = new string[] { objProducts.ProductID.ToString(), objProducts.ProductName, objProducts.SupplierID.HasValue ? objProducts.SupplierID.Value.ToString() : "", objProducts.CategoryID.HasValue ? objProducts.CategoryID.Value.ToString() : "", objProducts.QuantityPerUnit, objProducts.UnitPrice.HasValue ? objProducts.UnitPrice.Value.ToString() : "", objProducts.UnitsInStock.HasValue ? objProducts.UnitsInStock.Value.ToString() : "", objProducts.UnitsOnOrder.HasValue ? objProducts.UnitsOnOrder.Value.ToString() : "", objProducts.ReorderLevel.HasValue ? objProducts.ReorderLevel.Value.ToString() : "", objProducts.Discontinued.ToString(), objProducts.SupplierID is null ? "" : objProducts.Suppliers.Value.Result.CompanyName } }).ToArray() }; return Json(jsonData); } /// <summary> /// GET: /Products/GridDataTotalsGroupedByCategoryID /// Gets the json needed by the jqgrid for use by the GridDataTotalsGroupedByCategoryID /// </summary> public async Task<IActionResult> GridDataTotalsGroupedByCategoryID(string sidx, string sord, int page, int rows) { // using a groupBy field in the jqgrid passes that field // along with the field to sort, remove the groupBy field string groupBy = "CategoryName asc, "; sidx = sidx.Replace(groupBy, ""); // get the index where to start retrieving records from // 0 = starts from the beggining, 10 means skip the first 10 records and start from record 11 int startRowIndex = ((page * rows) - rows); // get the total number of records int totalRecords = await Products.GetRecordCountAsync(); // get records List<Products> objProductsCol = await Products.SelectSkipAndTakeAsync(rows, startRowIndex, sidx + " " + sord); // calculate the total number of pages int totalPages = (int)Math.Ceiling((float)totalRecords / (float)rows); // return a null in json for use by the jqgrid if (objProductsCol is null) return Json("{ total = 0, page = 0, records = 0, rows = null }"); // create a serialized json object for use by the jqgrid var jsonData = new { total = totalPages, page, records = totalRecords, rows = ( from objProducts in objProductsCol select new { id = objProducts.ProductID, cell = new string[] { objProducts.ProductID.ToString(), objProducts.ProductName, objProducts.SupplierID.HasValue ? objProducts.SupplierID.Value.ToString() : "", objProducts.CategoryID.HasValue ? objProducts.CategoryID.Value.ToString() : "", objProducts.QuantityPerUnit, objProducts.UnitPrice.HasValue ? objProducts.UnitPrice.Value.ToString() : "", objProducts.UnitsInStock.HasValue ? objProducts.UnitsInStock.Value.ToString() : "", objProducts.UnitsOnOrder.HasValue ? objProducts.UnitsOnOrder.Value.ToString() : "", objProducts.ReorderLevel.HasValue ? objProducts.ReorderLevel.Value.ToString() : "", objProducts.Discontinued.ToString(), objProducts.CategoryID is null ? "" : objProducts.Categories.Value.Result.CategoryName } }).ToArray() }; return Json(jsonData); } /// <summary> /// GET: /Products/GridDataBySupplierID /// Gets the json needed by the jqgrid for use by the GridDataBySupplierID /// </summary> public async Task<IActionResult> GridDataBySupplierID(string sidx, string sord, int page, int rows, int? supplierID) { // get the index where to start retrieving records from // 0 = starts from the beggining, 10 means skip the first 10 records and start from record 11 int startRowIndex = ((page * rows) - rows); // get the total number of records int totalRecords = await Products.GetRecordCountBySupplierIDAsync(supplierID); // get records List<Products> objProductsCol = await Products.SelectSkipAndTakeBySupplierIDAsync(rows, startRowIndex, sidx + " " + sord, supplierID); // calculate the total number of pages int totalPages = (int)Math.Ceiling((float)totalRecords / (float)rows); // return a null in json for use by the jqgrid if (objProductsCol is null) return Json("{ total = 0, page = 0, records = 0, rows = null }"); // create a serialized json object for use by the jqgrid var jsonData = new { total = totalPages, page, records = totalRecords, rows = ( from objProducts in objProductsCol select new { id = objProducts.ProductID, cell = new string[] { objProducts.ProductID.ToString(), objProducts.ProductName, objProducts.SupplierID.HasValue ? objProducts.SupplierID.Value.ToString() : "", objProducts.CategoryID.HasValue ? objProducts.CategoryID.Value.ToString() : "", objProducts.QuantityPerUnit, objProducts.UnitPrice.HasValue ? objProducts.UnitPrice.Value.ToString() : "", objProducts.UnitsInStock.HasValue ? objProducts.UnitsInStock.Value.ToString() : "", objProducts.UnitsOnOrder.HasValue ? objProducts.UnitsOnOrder.Value.ToString() : "", objProducts.ReorderLevel.HasValue ? objProducts.ReorderLevel.Value.ToString() : "", objProducts.Discontinued.ToString() } }).ToArray() }; return Json(jsonData); } /// <summary> /// GET: /Products/GridDataByCategoryID /// Gets the json needed by the jqgrid for use by the GridDataByCategoryID /// </summary> public async Task<IActionResult> GridDataByCategoryID(string sidx, string sord, int page, int rows, int? categoryID) { // get the index where to start retrieving records from // 0 = starts from the beggining, 10 means skip the first 10 records and start from record 11 int startRowIndex = ((page * rows) - rows); // get the total number of records int totalRecords = await Products.GetRecordCountByCategoryIDAsync(categoryID); // get records List<Products> objProductsCol = await Products.SelectSkipAndTakeByCategoryIDAsync(rows, startRowIndex, sidx + " " + sord, categoryID); // calculate the total number of pages int totalPages = (int)Math.Ceiling((float)totalRecords / (float)rows); // return a null in json for use by the jqgrid if (objProductsCol is null) return Json("{ total = 0, page = 0, records = 0, rows = null }"); // create a serialized json object for use by the jqgrid var jsonData = new { total = totalPages, page, records = totalRecords, rows = ( from objProducts in objProductsCol select new { id = objProducts.ProductID, cell = new string[] { objProducts.ProductID.ToString(), objProducts.ProductName, objProducts.SupplierID.HasValue ? objProducts.SupplierID.Value.ToString() : "", objProducts.CategoryID.HasValue ? objProducts.CategoryID.Value.ToString() : "", objProducts.QuantityPerUnit, objProducts.UnitPrice.HasValue ? objProducts.UnitPrice.Value.ToString() : "", objProducts.UnitsInStock.HasValue ? objProducts.UnitsInStock.Value.ToString() : "", objProducts.UnitsOnOrder.HasValue ? objProducts.UnitsOnOrder.Value.ToString() : "", objProducts.ReorderLevel.HasValue ? objProducts.ReorderLevel.Value.ToString() : "", objProducts.Discontinued.ToString() } }).ToArray() }; return Json(jsonData); } #endregion } }