using Microsoft.AspNetCore.Mvc; using ACG6MvcEFWebAPIApi.BusinessLayer; using ACG6MvcEFWebAPIApi.Models; using ACG6MvcEFWebAPIApi.Domain; using Newtonsoft.Json; namespace ACG6MvcEFWebAPISrvcs.ApiControllers.Base { /// <summary> /// Base class for ProductApiController. /// ************************************* Do not make changes to this class ************************************* /// ** Put your additional code in the ProductApiController class. ** /// ************************************************************************************************************* /// </summary> [ApiController] public partial class ProductApiController : ControllerBase { private Product _product; private readonly IProductBusinessLayer _productBusinessLayer; private readonly ISupplierBusinessLayer _supplierBusinessLayer; private readonly ICategoryBusinessLayer _categoryBusinessLayer; // constructor public ProductApiController(Product product, IProductBusinessLayer productBusinessLayer, ISupplierBusinessLayer supplierBusinessLayer, ICategoryBusinessLayer categoryBusinessLayer) { _product = product; _productBusinessLayer = productBusinessLayer; _supplierBusinessLayer = supplierBusinessLayer; _categoryBusinessLayer = categoryBusinessLayer; } /// <summary> /// Inserts/Adds/Creates a new record in the database /// </summary> /// <param name="model">Pass the Product here. Arrives as ProductFields which automatically strips the data annotations from the Product.</param> /// <returns>Task of IActionResult</returns> [Route("[controller]/insert")] [HttpPost] public async Task<IActionResult> Insert([FromBody]Product model, bool isForListInlineOrListCrud = false) { // add a new record return await AddEditProductAsync(model, CrudOperation.Add, isForListInlineOrListCrud); } /// <summary> /// Updates an existing record in the database by primary key. Pass the primary key in the Product /// </summary> /// <param name="model">Pass the Product here. Arrives as ProductFields which automatically strips the data annotations from the Product.</param> /// <returns>Task of IActionResult</returns> [Route("[controller]/update")] [HttpPost] public async Task<IActionResult> Update([FromBody]Product model, bool isForListInlineOrListCrud = false) { // update existing record return await this.AddEditProductAsync(model, CrudOperation.Update, isForListInlineOrListCrud); } /// <summary> /// Deletes an existing record by primary key /// </summary> /// <param name="id">ProductID</param> /// <returns>Task of IActionResult</returns> [Route("[controller]/delete")] [HttpDelete] public async Task<IActionResult> Delete(int id) { try { // delete a record based on id(s) await _productBusinessLayer.DeleteAsync(id); // everthing went well return Ok(); } catch (Exception ex) { // something went wrong return BadRequest("Error Message: " + ex.Message); } } /// <summary> /// Deletes multiple records based on the comma-delimited ids (primary keys) /// </summary> /// <param name="ids">List of ProductIDs</param> /// <returns>Task of IActionResult</returns> [Route("[controller]/deletemultiple")] [HttpDelete] public async Task<IActionResult> DeleteMultiple(string ids) { try { // split ids into a List List<Int32> productIDList = ids.Split(",").Select(Int32.Parse).ToList(); // delete multiple records based on a list of ids (primary keys) await _productBusinessLayer.DeleteMultipleAsync(productIDList); // everthing went well return Ok(); } catch (Exception ex) { // something went wrong return BadRequest("Error Message: " + ex.Message); } } /// <summary> /// Selects records as a collection (List) of Products sorted by the sord, starting in page, get the number of rows. /// </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>Returns a collection (List) of Products</returns> [Route("[controller]/selectskipandtake")] [HttpGet] public async Task<List<Product>> SelectSkipAndTake(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 records List<Product> objProductsList = await _productBusinessLayer.SelectSkipAndTakeAsync(rows, startRowIndex, sidx + " " + sord); return objProductsList; } /// <summary> /// Selects records as a collection (List) of Products sorted by the sord, starting in page, get the number of rows. /// </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>Returns a collection (List) of Products</returns> [Route("[controller]/selectskipandtakewithfilters")] [HttpGet] public async Task<List<Product>> 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 filters 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; } // 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 List<Product> objProductsList = await _productBusinessLayer.SelectSkipAndTakeDynamicWhereAsync(productID, productName, supplierID, categoryID, quantityPerUnit, unitPrice, unitsInStock, unitsOnOrder, reorderLevel, discontinued, rows, startRowIndex, sidx + " " + sord); return objProductsList; } /// <summary> /// Selects records as a collection (List) of Products sorted by the sord, starting in page, get the number of rows. /// </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>Returns a collection (List) of Products</returns> [Route("[controller]/selectskipandtakewithtotals")] [HttpGet] public async Task<List<Product>> SelectSkipAndTakeWithTotals(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 records List<Product> objProductsList = await _productBusinessLayer.SelectSkipAndTakeAsync(rows, startRowIndex, sidx + " " + sord); return objProductsList; } /// <summary> /// Selects records as a collection (List) of Products sorted by the sord, starting in page, get the number of rows. /// </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>Returns a collection (List) of Products</returns> [Route("[controller]/selectskipandtakegroupedbysupplierid")] [HttpGet] public async Task<List<Product>> SelectSkipAndTakeGroupedBySupplierID(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 records List<Product> objProductsList = await _productBusinessLayer.SelectSkipAndTakeAsync(rows, startRowIndex, sidx + " " + sord); return objProductsList; } /// <summary> /// Selects records as a collection (List) of Products sorted by the sord, starting in page, get the number of rows. /// </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>Returns a collection (List) of Products</returns> [Route("[controller]/selectskipandtakegroupedbycategoryid")] [HttpGet] public async Task<List<Product>> SelectSkipAndTakeGroupedByCategoryID(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 records List<Product> objProductsList = await _productBusinessLayer.SelectSkipAndTakeAsync(rows, startRowIndex, sidx + " " + sord); return objProductsList; } /// <summary> /// Selects records as a collection (List) of Products sorted by the sord, starting in page, get the number of rows. /// </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>Returns a collection (List) of Products</returns> [Route("[controller]/selectskipandtaketotalsgroupedbysupplierid")] [HttpGet] public async Task<List<Product>> SelectSkipAndTakeTotalsGroupedBySupplierID(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 records List<Product> objProductsList = await _productBusinessLayer.SelectSkipAndTakeAsync(rows, startRowIndex, sidx + " " + sord); return objProductsList; } /// <summary> /// Selects records as a collection (List) of Products sorted by the sord, starting in page, get the number of rows. /// </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>Returns a collection (List) of Products</returns> [Route("[controller]/selectskipandtaketotalsgroupedbycategoryid")] [HttpGet] public async Task<List<Product>> SelectSkipAndTakeTotalsGroupedByCategoryID(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 records List<Product> objProductsList = await _productBusinessLayer.SelectSkipAndTakeAsync(rows, startRowIndex, sidx + " " + sord); return objProductsList; } /// <summary> /// Selects a record by primary key(s) /// </summary> /// <param name="id">ProductID</param> /// <returns>Returns one Product record</returns> [Route("[controller]/selectbyprimarykey")] [HttpGet] public async Task<Product> SelectByPrimaryKey(int id) { Product objProduct = await _productBusinessLayer.SelectByPrimaryKeyAsync(id); return objProduct; } /// <summary> /// Gets the total number of records in the Products table /// </summary> /// <returns>Returns the Total number of records in the Product table</returns> [Route("[controller]/getrecordcount")] [HttpGet] public async Task<int> GetRecordCount() { return await _productBusinessLayer.GetRecordCountAsync(); } /// <summary> /// Gets the total number of records in the Products table by SupplierID /// </summary> /// <param name="id">supplierID</param> /// <returns>Returns the Total number of records in the Products table by supplierID</returns> [Route("[controller]/getrecordcountbysupplierid")] [HttpGet] public async Task<int> GetRecordCountBySupplierID(int? id) { return await _productBusinessLayer.GetRecordCountBySupplierIDAsync(id); } /// <summary> /// Gets the total number of records in the Products table by CategoryID /// </summary> /// <param name="id">categoryID</param> /// <returns>Returns the Total number of records in the Products table by categoryID</returns> [Route("[controller]/getrecordcountbycategoryid")] [HttpGet] public async Task<int> GetRecordCountByCategoryID(int? id) { return await _productBusinessLayer.GetRecordCountByCategoryIDAsync(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>Returns the Total number of records in the Products table based on the search parameters</returns> [Route("[controller]/getrecordcountdynamicwhere")] [HttpGet] public async Task<int> GetRecordCountDynamicWhere(int? productID, string productName, int? supplierID, int? categoryID, string quantityPerUnit, decimal? unitPrice, Int16? unitsInStock, Int16? unitsOnOrder, Int16? reorderLevel, bool? discontinued) { return await _productBusinessLayer.GetRecordCountDynamicWhereAsync(productID, productName, supplierID, categoryID, quantityPerUnit, unitPrice, unitsInStock, unitsOnOrder, reorderLevel, discontinued); } /// <summary> /// Selects records as a collection (List) of Product sorted by the sortByExpression. /// </summary> /// <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> /// <returns>Returns Product (List of) collection.</returns> [Route("[controller]/selectskipandtakebysortexprsn")] [HttpGet] public async Task<List<Product>> SelectSkipAndTakeBySortExprsn(int rows, int startRowIndex, string sortByExpression) { sortByExpression = this.GetSortExpression(sortByExpression); return await _productBusinessLayer.SelectSkipAndTakeAsync(rows, startRowIndex, sortByExpression); } /// <summary> /// Selects records by SupplierID as a collection (List) of Product sorted by the sord starting from the page /// </summary> /// <param name="supplierID">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>Returns a collection (List) of Product</returns> [Route("[controller]/selectskipandtakebysupplierid")] [HttpGet] public async Task<List<Product>> SelectSkipAndTakeBySupplierID(int? supplierID, string sidx, string sord, int page, int rows) { string sortByExpression = this.GetSortExpression(sidx + " " + sord); int startRowIndex = ((page * rows) - rows); List<Product> objProductsList = await _productBusinessLayer.SelectSkipAndTakeBySupplierIDAsync(rows, startRowIndex, sortByExpression, supplierID); return objProductsList; } /// <summary> /// Selects records by CategoryID as a collection (List) of Product sorted by the sord starting from the page /// </summary> /// <param name="categoryID">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>Returns a collection (List) of Product</returns> [Route("[controller]/selectskipandtakebycategoryid")] [HttpGet] public async Task<List<Product>> SelectSkipAndTakeByCategoryID(int? categoryID, string sidx, string sord, int page, int rows) { string sortByExpression = this.GetSortExpression(sidx + " " + sord); int startRowIndex = ((page * rows) - rows); List<Product> objProductsList = await _productBusinessLayer.SelectSkipAndTakeByCategoryIDAsync(rows, startRowIndex, sortByExpression, categoryID); return objProductsList; } /// <summary> /// Selects records as a collection (List) of Product 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>Returns a collection (List) of Product</returns> [Route("[controller]/selectskipandtakedynamicwhere")] [HttpGet] public async Task<List<Product>> 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) { sortByExpression = this.GetSortExpression(sortByExpression); List<Product> objProductsList = await _productBusinessLayer.SelectSkipAndTakeDynamicWhereAsync(productID, productName, supplierID, categoryID, quantityPerUnit, unitPrice, unitsInStock, unitsOnOrder, reorderLevel, discontinued, rows, startRowIndex, sortByExpression); return objProductsList; } /// <summary> /// Selects all records as a collection (List) of Product sorted by the sort expression. Data returned is not sorted when sortByExpression is not passed, null, or empty. /// </summary> /// <param name="sortByExpression">Field to sort and sort direction. E.g. "FieldName asc" or "FieldName desc". sortByExpression is not required.</param> /// <returns>Returns a collection (List) of Product</returns> [Route("[controller]/selectall")] [HttpGet] public async Task<List<Product>> SelectAll(string sortByExpression = null) { List<Product> objProductsList; if (String.IsNullOrEmpty(sortByExpression)) { objProductsList = await _productBusinessLayer.SelectAllAsync(); } else { sortByExpression = this.GetSortExpression(sortByExpression); objProductsList = await _productBusinessLayer.SelectAllAsync(sortByExpression); } return objProductsList; } /// <summary> /// Selects records based on the passed filters as a collection (List) of Product. /// </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>Returns a collection (List) of Product</returns> [Route("[controller]/selectalldynamicwhere")] [HttpGet] public async Task<List<Product>> SelectAllDynamicWhere(int? productID, string productName, int? supplierID, int? categoryID, string quantityPerUnit, decimal? unitPrice, Int16? unitsInStock, Int16? unitsOnOrder, Int16? reorderLevel, bool? discontinued) { List<Product> objProductsList = await _productBusinessLayer.SelectAllDynamicWhereAsync(productID, productName, supplierID, categoryID, quantityPerUnit, unitPrice, unitsInStock, unitsOnOrder, reorderLevel, discontinued); return objProductsList; } /// <summary> /// Selects ProductID and ProductName columns for use with a DropDownList web control, ComboBox, CheckedBoxList, ListView, ListBox, etc /// </summary> /// <returns>Returns a collection (List) of Product</returns> [Route("[controller]/selectproductdropdownlistdata")] [HttpGet] public async Task<List<Product>> SelectProductDropDownListData() { List<Product> objProductsList = await _productBusinessLayer.SelectProductDropDownListDataAsync(); return objProductsList; } /// <summary> /// Gets the default sort expression when no sort expression is provided /// </summary> private string GetSortExpression(string sortByExpression) { // when no sort expression is provided, ProductID is set as the default in ascending order // for ascending order, "asc" is not needed, so it is removed if (String.IsNullOrEmpty(sortByExpression) || sortByExpression == " asc") sortByExpression = "ProductID"; else if (sortByExpression.Contains(" asc")) sortByExpression = sortByExpression.Replace(" asc", ""); return sortByExpression; } /// <summary> /// Used when adding a new record or updating an existing record /// </summary> /// <param name="model">Product</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> AddEditProductAsync(Product model, CrudOperation operation, bool isForListInlineOrListCrud = false) { try { // create a new instance of the Product when adding a new record // or, retrieve the record that needs to be updated if (operation == CrudOperation.Add) _product = new(); else _product = await _productBusinessLayer.SelectByPrimaryKeyAsync(model.ProductID); // assign values to the Product instance _product.ProductID = model.ProductID; _product.ProductName = model.ProductName; _product.SupplierID = model.SupplierID; _product.CategoryID = model.CategoryID; _product.QuantityPerUnit = model.QuantityPerUnit; _product.UnitPrice = model.UnitPrice; _product.Discontinued = model.Discontinued; if (isForListInlineOrListCrud) { _product.UnitsInStock = model.UnitsInStock; _product.UnitsOnOrder = model.UnitsOnOrder; _product.ReorderLevel = model.ReorderLevel; } else { if(!String.IsNullOrEmpty(model.UnitsInStockHidden)) _product.UnitsInStock = Convert.ToInt16(model.UnitsInStockHidden); else _product.UnitsInStock = null; if(!String.IsNullOrEmpty(model.UnitsOnOrderHidden)) _product.UnitsOnOrder = Convert.ToInt16(model.UnitsOnOrderHidden); else _product.UnitsOnOrder = null; if(!String.IsNullOrEmpty(model.ReorderLevelHidden)) _product.ReorderLevel = Convert.ToInt16(model.ReorderLevelHidden); else _product.ReorderLevel = null; } // save the new record, or the updated values of the current record if (operation == CrudOperation.Add) await _productBusinessLayer.InsertAsync(_product); else await _productBusinessLayer.UpdateAsync(_product); // everthing went well return Ok(); } catch (Exception ex) { // something went wrong return BadRequest("Error Message: " + ex.Message); } } } }