using Microsoft.AspNetCore.Mvc;
using System;
using System.Linq;
using NorthwindAPI.BusinessObject;
using NorthwindAPI.Models;
using NorthwindAPI.Domain;
using System.Collections.Generic;
using Newtonsoft.Json;
using System.Net;
using System.Net.Http;
using System.Threading.Tasks;
 
namespace NorthwindWebAPI.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) + 1;
             List<Products> objProductsCol = Products.SelectSkipAndTake(rows, startRowIndex, sidx + " " + sord);
 
             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) + 1;
             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) + 1;
 
             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) + 1;
 
             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.Suppliers.Value.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) + 1;
 
             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.Categories.Value.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) + 1;
 
             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.Suppliers.Value.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) + 1;
 
             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.Categories.Value.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);
         }
 
         /// <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;
         }
     }
}