using Microsoft.AspNetCore.Mvc; using System; using System.Linq; using RazorEfWaAPI.BusinessObject; using RazorEfWaAPI.Models; using RazorEfWaAPI.Domain; using System.Collections.Generic; using Newtonsoft.Json; using System.Net; using System.Net.Http; using System.Threading.Tasks; namespace RazorEfWaWebAPI.ApiControllers.Base { /// <summary> /// Base class for ProductsApiController. Do not make changes to this class, /// instead, put additional code in the ProductsApiController class /// </summary> public class ProductsApiControllerBase : Controller { /// <summary> /// Inserts/Adds/Creates a new record in the database /// </summary> /// <param name="model">Pass the ProductsModel here. Arrives as ProductsFields which automatically strips the data annotations from the ProductsModel.</param> /// <returns>IActionResult</returns> [HttpPost] public IActionResult Insert([FromBody]ProductsModel model, bool isForListInline = false) { return AddEditProducts(model, CrudOperation.Add, isForListInline); } /// <summary> /// Updates an existing record in the database by primary key. Pass the primary key in the ProductsModel /// </summary> /// <param name="model">Pass the ProductsModel here. Arrives as ProductsFields which automatically strips the data annotations from the ProductsModel.</param> /// <returns>IActionResult</returns> [HttpPost] public IActionResult Update([FromBody]ProductsModel model, bool isForListInline = false) { return AddEditProducts(model, CrudOperation.Update, isForListInline); } /// <summary> /// Deletes an existing record by primary key /// </summary> /// <param name="id">ProductID</param> /// <returns>IActionResult</returns> [HttpDelete] public IActionResult Delete(int id) { try { Products.Delete(id); return Ok(); } catch (Exception ex) { return BadRequest("Error Message: " + ex.Message + " Stack Trace: " + ex.StackTrace); } } private IActionResult AddEditProducts(ProductsModel model, CrudOperation operation, bool isForListInline = false) { try { Products objProducts; if (operation == CrudOperation.Add) objProducts = new Products(); else objProducts = Products.SelectByPrimaryKey(model.ProductID); 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 (isForListInline) { 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; } if (operation == CrudOperation.Add) objProducts.Insert(); else objProducts.Update(); return Ok(); } catch (Exception ex) { return BadRequest("Error Message: " + ex.Message + " Stack Trace: " + ex.StackTrace); } } private List<Products> GetFilteredData(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 Products.SelectSkipAndTakeDynamicWhere(productID, productName, supplierID, categoryID, quantityPerUnit, unitPrice, unitsInStock, unitsOnOrder, reorderLevel, discontinued, rows, startRowIndex, sortExpression); return Products.SelectSkipAndTake(rows, startRowIndex, sortExpression); } /// <summary> /// Use in a JQGrid plugin. Selects records as a collection (List) of Products sorted by the sortByExpression. /// Also returns total pages, current page, and total records. /// </summary> /// <param name="sidx">Field to sort. Can be an empty string.</param> /// <param name="sord">asc or an empty string = ascending. desc = descending</param> /// <param name="page">Current page</param> /// <param name="rows">Number of rows to retrieve</param> /// <param name="isforJqGrid">Optional isforJqGrid. Default is true, returns json formatted string, otherwise, returns serialized List of Products</param> /// <returns>Serialized Products collection in json format for use in a JQGrid plugin</returns> [HttpGet] public object SelectSkipAndTake(string sidx, string sord, int _page, int rows, bool isforJqGrid = true) { int totalRecords = Products.GetRecordCount(); int startRowIndex = ((_page * rows) - rows); bool isIncludeRelatedProperties = true; if(!isforJqGrid) isIncludeRelatedProperties = false; List<Products> objProductsCol = Products.SelectSkipAndTake(rows, startRowIndex, sidx + " " + sord, isIncludeRelatedProperties); if (!isforJqGrid) { if (objProductsCol is null) return ""; return objProductsCol; } int totalPages = (int)Math.Ceiling((float)totalRecords / (float)rows); if (objProductsCol is null) return Json("{ total = 0, page = 0, records = 0, rows = null }"); 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 jsonData; } /// <summary> /// Use in a JQGrid plugin. Selects records as a collection (List) of Products sorted by the sortByExpression. /// Also returns total pages, current page, and total records based on the search filters. /// </summary> /// <param name="_search">true or false</param> /// <param name="nd">nd</param> /// <param name="rows">Number of rows to retrieve</param> /// <param name="page">Current page</param> /// <param name="sidx">Field to sort. Can be an empty string.</param> /// <param name="sord">asc or an empty string = ascending. desc = descending</param> /// <param name="filters">Optional. Filters used in search</param> /// <returns>Serialized Products collection in json format for use in a JQGrid plugin</returns> [HttpGet] public object SelectSkipAndTakeWithFilters(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 json and get values being searched var jsonResult = 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; } int totalRecords = Products.GetRecordCountDynamicWhere(productID, productName, supplierID, categoryID, quantityPerUnit, unitPrice, unitsInStock, unitsOnOrder, reorderLevel, discontinued); int startRowIndex = ((_page * rows) - rows); List<Products> objProductsCol = Products.SelectSkipAndTakeDynamicWhere(productID, productName, supplierID, categoryID, quantityPerUnit, unitPrice, unitsInStock, unitsOnOrder, reorderLevel, discontinued, rows, startRowIndex, sidx + " " + sord); int totalPages = (int)Math.Ceiling((float)totalRecords / (float)rows); if (objProductsCol is null) return Json("{ total = 0, page = 0, records = 0, rows = null }"); 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 jsonData; } /// <summary> /// Use in a JQGrid plugin. Selects records as a collection (List) of Products sorted by the sortByExpression. /// Also returns total pages, current page, and total records. /// </summary> /// <param name="sidx">Field to sort. Can be an empty string.</param> /// <param name="sord">asc or an empty string = ascending. desc = descending</param> /// <param name="page">Current page</param> /// <param name="rows">Number of rows to retrieve</param> /// <returns>Serialized Products collection in json format for use in a JQGrid plugin</returns> [HttpGet] public object SelectSkipAndTakeWithTotals(string sidx, string sord, int _page, int rows) { int totalRecords = Products.GetRecordCount(); int startRowIndex = ((_page * rows) - rows); List<Products> objProductsCol = Products.SelectSkipAndTake(rows, startRowIndex, sidx + " " + sord); int totalPages = (int)Math.Ceiling((float)totalRecords / (float)rows); if (objProductsCol is null) return Json("{ total = 0, page = 0, records = 0, rows = null }"); 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 jsonData; } /// <summary> /// Use in a JQGrid plugin. Selects records as a collection (List) of Products sorted by the sortByExpression. /// Also returns total pages, current page, and total records. /// </summary> /// <param name="sidx">Field to sort. Can be an empty string.</param> /// <param name="sord">asc or an empty string = ascending. desc = descending</param> /// <param name="page">Current page</param> /// <param name="rows">Number of rows to retrieve</param> /// <returns>Serialized Products collection in json format for use in a JQGrid plugin</returns> [HttpGet] public object SelectSkipAndTakeGroupedBySupplierID(string sidx, string sord, int _page, int rows) { // using a groupField in the jqgrid passes that field // along with the field to sort, remove the groupField string groupBy = "CompanyName asc, "; sidx = sidx.Replace(groupBy, ""); int totalRecords = Products.GetRecordCount(); int startRowIndex = ((_page * rows) - rows); List<Products> objProductsCol = Products.SelectSkipAndTake(rows, startRowIndex, sidx + " " + sord); int totalPages = (int)Math.Ceiling((float)totalRecords / (float)rows); if (objProductsCol is null) return Json("{ total = 0, page = 0, records = 0, rows = null }"); 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.Supplier.CompanyName } }).ToArray() }; return jsonData; } /// <summary> /// Use in a JQGrid plugin. Selects records as a collection (List) of Products sorted by the sortByExpression. /// Also returns total pages, current page, and total records. /// </summary> /// <param name="sidx">Field to sort. Can be an empty string.</param> /// <param name="sord">asc or an empty string = ascending. desc = descending</param> /// <param name="page">Current page</param> /// <param name="rows">Number of rows to retrieve</param> /// <returns>Serialized Products collection in json format for use in a JQGrid plugin</returns> [HttpGet] public object SelectSkipAndTakeGroupedByCategoryID(string sidx, string sord, int _page, int rows) { // using a groupField in the jqgrid passes that field // along with the field to sort, remove the groupField string groupBy = "CategoryName asc, "; sidx = sidx.Replace(groupBy, ""); int totalRecords = Products.GetRecordCount(); int startRowIndex = ((_page * rows) - rows); List<Products> objProductsCol = Products.SelectSkipAndTake(rows, startRowIndex, sidx + " " + sord); int totalPages = (int)Math.Ceiling((float)totalRecords / (float)rows); if (objProductsCol is null) return Json("{ total = 0, page = 0, records = 0, rows = null }"); 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.Category.CategoryName } }).ToArray() }; return jsonData; } /// <summary> /// Use in a JQGrid plugin. Selects records as a collection (List) of Products sorted by the sortByExpression. /// Also returns total pages, current page, and total records. /// </summary> /// <param name="sidx">Field to sort. Can be an empty string.</param> /// <param name="sord">asc or an empty string = ascending. desc = descending</param> /// <param name="page">Current page</param> /// <param name="rows">Number of rows to retrieve</param> /// <returns>Serialized Products collection in json format for use in a JQGrid plugin</returns> [HttpGet] public object SelectSkipAndTakeTotalsGroupedBySupplierID(string sidx, string sord, int _page, int rows) { // using a groupField in the jqgrid passes that field // along with the field to sort, remove the groupField string groupBy = "CompanyName asc, "; sidx = sidx.Replace(groupBy, ""); int totalRecords = Products.GetRecordCount(); int startRowIndex = ((_page * rows) - rows); List<Products> objProductsCol = Products.SelectSkipAndTake(rows, startRowIndex, sidx + " " + sord); int totalPages = (int)Math.Ceiling((float)totalRecords / (float)rows); if (objProductsCol is null) return Json("{ total = 0, page = 0, records = 0, rows = null }"); 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.Supplier.CompanyName } }).ToArray() }; return jsonData; } /// <summary> /// Use in a JQGrid plugin. Selects records as a collection (List) of Products sorted by the sortByExpression. /// Also returns total pages, current page, and total records. /// </summary> /// <param name="sidx">Field to sort. Can be an empty string.</param> /// <param name="sord">asc or an empty string = ascending. desc = descending</param> /// <param name="page">Current page</param> /// <param name="rows">Number of rows to retrieve</param> /// <returns>Serialized Products collection in json format for use in a JQGrid plugin</returns> [HttpGet] public object SelectSkipAndTakeTotalsGroupedByCategoryID(string sidx, string sord, int _page, int rows) { // using a groupField in the jqgrid passes that field // along with the field to sort, remove the groupField string groupBy = "CategoryName asc, "; sidx = sidx.Replace(groupBy, ""); int totalRecords = Products.GetRecordCount(); int startRowIndex = ((_page * rows) - rows); List<Products> objProductsCol = Products.SelectSkipAndTake(rows, startRowIndex, sidx + " " + sord); int totalPages = (int)Math.Ceiling((float)totalRecords / (float)rows); if (objProductsCol is null) return Json("{ total = 0, page = 0, records = 0, rows = null }"); 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.Category.CategoryName } }).ToArray() }; return jsonData; } /// <summary> /// Selects a record by primary key(s) /// </summary> /// <param name="id">ProductID</param> /// <returns>One serialized Products record in json format</returns> [HttpGet] public object SelectByPrimaryKey(int id) { Products objProducts = Products.SelectByPrimaryKey(id); var jsonData = new { ProductID = objProducts.ProductID, ProductName = objProducts.ProductName, SupplierID = objProducts.SupplierID, CategoryID = objProducts.CategoryID, QuantityPerUnit = objProducts.QuantityPerUnit, UnitPrice = objProducts.UnitPrice, UnitsInStock = objProducts.UnitsInStock, UnitsOnOrder = objProducts.UnitsOnOrder, ReorderLevel = objProducts.ReorderLevel, Discontinued = objProducts.Discontinued }; return jsonData; } /// <summary> /// Gets the total number of records in the Products table /// </summary> /// <returns>Total number of records in the Products table</returns> [HttpGet] public int GetRecordCount() { return Products.GetRecordCount(); } /// <summary> /// Gets the total number of records in the Products table by SupplierID /// </summary> /// <param name="id">supplierID</param> /// <returns>Total number of records in the Products table by supplierID</returns> [HttpGet] public int GetRecordCountBySupplierID(int id) { return Products.GetRecordCountBySupplierID(id); } /// <summary> /// Gets the total number of records in the Products table by CategoryID /// </summary> /// <param name="id">categoryID</param> /// <returns>Total number of records in the Products table by categoryID</returns> [HttpGet] public int GetRecordCountByCategoryID(int id) { return Products.GetRecordCountByCategoryID(id); } /// <summary> /// Gets the total number of records in the Products table based on search parameters /// </summary> /// <param name="productID">ProductID</param> /// <param name="productName">ProductName</param> /// <param name="supplierID">SupplierID</param> /// <param name="categoryID">CategoryID</param> /// <param name="quantityPerUnit">QuantityPerUnit</param> /// <param name="unitPrice">UnitPrice</param> /// <param name="unitsInStock">UnitsInStock</param> /// <param name="unitsOnOrder">UnitsOnOrder</param> /// <param name="reorderLevel">ReorderLevel</param> /// <param name="discontinued">Discontinued</param> /// <returns>Total number of records in the Products table based on the search parameters</returns> [HttpGet] public int GetRecordCountDynamicWhere(int? productID, string productName, int? supplierID, int? categoryID, string quantityPerUnit, decimal? unitPrice, Int16? unitsInStock, Int16? unitsOnOrder, Int16? reorderLevel, bool? discontinued) { return Products.GetRecordCountDynamicWhere(productID, productName, supplierID, categoryID, quantityPerUnit, unitPrice, unitsInStock, unitsOnOrder, reorderLevel, discontinued); } /// <summary> /// Selects records as a collection (List) of Products sorted by the sortByExpression and returns the rows (# of records) starting from the startRowIndex /// </summary> public List<Products> SelectSkipAndTake(int rows, int startRowIndex, string sortByExpression, bool isIncludeRelatedProperties = true) { sortByExpression = GetSortExpression(sortByExpression); return Products.SelectSkipAndTake(rows, startRowIndex, sortByExpression, isIncludeRelatedProperties); } /// <summary> /// Selects records by SupplierID as a collection (List) of Products sorted by the sortByExpression starting from the startRowIndex /// </summary> /// <param name="id">Supplier ID</param> /// <param name="sidx">Column to sort</param> /// <param name="sord">Sort direction</param> /// <param name="page">Page of the grid to show</param> /// <param name="rows">Number of rows to retrieve</param> /// <returns>Serialized Products collection in json format</returns> [HttpGet] public object SelectSkipAndTakeBySupplierID(int id, string sidx, string sord, int _page, int rows) { string sortByExpression = GetSortExpression(sidx + " " + sord); int startRowIndex = _page - 1; List<Products> objProductsCol = Products.SelectSkipAndTakeBySupplierID(rows, startRowIndex, sortByExpression, id); int totalRecords = Products.GetRecordCountBySupplierID(id); return GetJsonCollection(objProductsCol, totalRecords, _page, rows); } /// <summary> /// Selects records by CategoryID as a collection (List) of Products sorted by the sortByExpression starting from the startRowIndex /// </summary> /// <param name="id">Category ID</param> /// <param name="sidx">Column to sort</param> /// <param name="sord">Sort direction</param> /// <param name="page">Page of the grid to show</param> /// <param name="rows">Number of rows to retrieve</param> /// <returns>Serialized Products collection in json format</returns> [HttpGet] public object SelectSkipAndTakeByCategoryID(int id, string sidx, string sord, int _page, int rows) { string sortByExpression = GetSortExpression(sidx + " " + sord); int startRowIndex = _page - 1; List<Products> objProductsCol = Products.SelectSkipAndTakeByCategoryID(rows, startRowIndex, sortByExpression, id); int totalRecords = Products.GetRecordCountByCategoryID(id); return GetJsonCollection(objProductsCol, totalRecords, _page, rows); } /// <summary> /// Selects records as a collection (List) of Products sorted by the sortByExpression starting from the startRowIndex, based on the search parameters /// </summary> /// <param name="productID">ProductID</param> /// <param name="productName">ProductName</param> /// <param name="supplierID">SupplierID</param> /// <param name="categoryID">CategoryID</param> /// <param name="quantityPerUnit">QuantityPerUnit</param> /// <param name="unitPrice">UnitPrice</param> /// <param name="unitsInStock">UnitsInStock</param> /// <param name="unitsOnOrder">UnitsOnOrder</param> /// <param name="reorderLevel">ReorderLevel</param> /// <param name="discontinued">Discontinued</param> /// <param name="rows">Number of rows to retrieve</param> /// <param name="startRowIndex">Zero-based. Row index where to start taking rows from</param> /// <param name="sortByExpression">Field to sort and sort direction. E.g. "FieldName asc" or "FieldName desc"</param> /// <param name="page">Page of the grid to show</param> /// <returns>Serialized Products collection in json format</returns> [HttpGet] public object SelectSkipAndTakeDynamicWhere(int? productID, string productName, int? supplierID, int? categoryID, string quantityPerUnit, decimal? unitPrice, Int16? unitsInStock, Int16? unitsOnOrder, Int16? reorderLevel, bool? discontinued, int rows, int startRowIndex, string sortByExpression, int _page) { sortByExpression = GetSortExpression(sortByExpression); List<Products> objProductsCol = Products.SelectSkipAndTakeDynamicWhere(productID, productName, supplierID, categoryID, quantityPerUnit, unitPrice, unitsInStock, unitsOnOrder, reorderLevel, discontinued, rows, startRowIndex, sortByExpression); int totalRecords = Products.GetRecordCountDynamicWhere(productID, productName, supplierID, categoryID, quantityPerUnit, unitPrice, unitsInStock, unitsOnOrder, reorderLevel, discontinued); return GetJsonCollection(objProductsCol, totalRecords, _page, rows); } /// <summary> /// Selects all records as a collection (List) of Products /// </summary> /// <returns>Serialized Products collection in json format</returns> [HttpGet] public object SelectAll() { List<Products> objProductsCol = Products.SelectAll(); return GetJsonCollection(objProductsCol, objProductsCol.Count, 1, objProductsCol.Count); } /// <summary> /// Selects all records as a collection (List) of Products sorted by the sort expression /// </summary> /// <param name="sortByExpression">Field to sort and sort direction. E.g. "FieldName asc" or "FieldName desc"</param> /// <returns>Serialized Products collection in json format</returns> [HttpGet] public object SelectAll(string sortByExpression) { sortByExpression = GetSortExpression(sortByExpression); List<Products> objProductsCol = Products.SelectAll(sortByExpression); return GetJsonCollection(objProductsCol, objProductsCol.Count, 1, objProductsCol.Count); } /// <summary> /// Selects records based on the passed filters as a collection (List) of Products. /// </summary> /// <param name="productID">ProductID</param> /// <param name="productName">ProductName</param> /// <param name="supplierID">SupplierID</param> /// <param name="categoryID">CategoryID</param> /// <param name="quantityPerUnit">QuantityPerUnit</param> /// <param name="unitPrice">UnitPrice</param> /// <param name="unitsInStock">UnitsInStock</param> /// <param name="unitsOnOrder">UnitsOnOrder</param> /// <param name="reorderLevel">ReorderLevel</param> /// <param name="discontinued">Discontinued</param> /// <returns>Serialized Products collection in json format</returns> [HttpGet] public object SelectAllDynamicWhere(int? productID, string productName, int? supplierID, int? categoryID, string quantityPerUnit, decimal? unitPrice, Int16? unitsInStock, Int16? unitsOnOrder, Int16? reorderLevel, bool? discontinued) { List<Products> objProductsCol = Products.SelectAllDynamicWhere(productID, productName, supplierID, categoryID, quantityPerUnit, unitPrice, unitsInStock, unitsOnOrder, reorderLevel, discontinued); return GetJsonCollection(objProductsCol, objProductsCol.Count, 1, objProductsCol.Count); } /// <summary> /// Selects all Products by Suppliers, related to column SupplierID /// </summary> /// <param name="id">supplierID</param> /// <param name="sidx">Column to sort</param> /// <param name="sord">Sort direction</param> /// <param name="page">Page of the grid to show</param> /// <param name="rows">Number of rows to retrieve</param> /// <returns>Total number of records in the Products table by supplierID</returns> [HttpGet] public object SelectProductsCollectionBySupplierID(int id, string sidx, string sord, int _page, int rows) { string sortByExpression = GetSortExpression(sidx + " " + sord); int startRowIndex = _page; List<Products> objProductsCol = Products.SelectSkipAndTakeBySupplierID(rows, startRowIndex, sortByExpression, id); int totalRecords = Products.GetRecordCountBySupplierID(id); return GetJsonCollection(objProductsCol, totalRecords, _page, rows); } /// <summary> /// Selects all Products by Categories, related to column CategoryID /// </summary> /// <param name="id">categoryID</param> /// <param name="sidx">Column to sort</param> /// <param name="sord">Sort direction</param> /// <param name="page">Page of the grid to show</param> /// <param name="rows">Number of rows to retrieve</param> /// <returns>Total number of records in the Products table by categoryID</returns> [HttpGet] public object SelectProductsCollectionByCategoryID(int id, string sidx, string sord, int _page, int rows) { string sortByExpression = GetSortExpression(sidx + " " + sord); int startRowIndex = _page; List<Products> objProductsCol = Products.SelectSkipAndTakeByCategoryID(rows, startRowIndex, sortByExpression, id); int totalRecords = Products.GetRecordCountByCategoryID(id); return GetJsonCollection(objProductsCol, totalRecords, _page, rows); } /// <summary> /// Selects ProductID and ProductName columns for use with a DropDownList web control, ComboBox, CheckedBoxList, ListView, ListBox, etc /// </summary> /// <returns>Serialized Products collection in json format</returns> [HttpGet] public object SelectProductsDropDownListData() { List<Products> objProductsCol = Products.SelectProductsDropDownListData(); if(objProductsCol != null) { var jsonData = (from objProducts in objProductsCol select new { ProductID = objProducts.ProductID, ProductName = objProducts.ProductName }).ToArray(); return jsonData; } return null; } private object GetJsonCollection(List<Products> objProductsCol, int totalRecords, int _page, int rows) { if (objProductsCol is null) return null; int totalPages = (int)Math.Ceiling((float)totalRecords / (float)rows); 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 jsonData; } private string GetSortExpression(string sortByExpression) { if (String.IsNullOrEmpty(sortByExpression) || sortByExpression == " asc") sortByExpression = "ProductID"; else if (sortByExpression.Contains(" asc")) sortByExpression = sortByExpression.Replace(" asc", ""); return sortByExpression; } } }