using Microsoft.AspNetCore.Mvc;
using System;
using System.Linq;
using NorthwindAPI.BusinessObject;
using NorthwindAPI.Models;
using NorthwindAPI.ViewModels;
using NorthwindAPI.Domain;
using System.Collections.Generic;
using Microsoft.Extensions.Options;
using System.Threading.Tasks;
using Newtonsoft.Json;
using System.Text;
 
namespace Northwind.Controllers.Base
{
     /// <summary>
     /// Base class for ProductsController.  Do not make changes to this class,
     /// instead, put additional code in the ProductsController class 
     /// </summary>
     public class ProductsControllerBase : Controller
     {
 
         /// <summary>
         /// GET: /Products/
         /// </summary>
         public IActionResult Index()
         {
             return View();
         }
 
         /// <summary>
         /// GET: /Products/Add
         /// </summary>
         public IActionResult Add()
         {
             return GetAddViewModel();
         }
 
         /// <summary>
         /// POST: /Products/Add
         /// </summary>
         [HttpPost]
         [ValidateAntiForgeryToken]
         public IActionResult Add(ProductsViewModel viewModel, string returnUrl)
         {
             if (ModelState.IsValid)
             {
                 try
                 {
                     // add new record
                     AddEditProducts(viewModel, CrudOperation.Add);
 
                     if (Url.IsLocalUrl(returnUrl))
                         return Redirect(returnUrl);
                     else
                         return RedirectToAction("ListCrudRedirect", "Products");
                 }
                 catch(Exception ex)
                 {
                     if (ex.InnerException != null)
                         ModelState.AddModelError("", ex.InnerException.Message);
                     else
                         ModelState.AddModelError("", ex.Message);
                 }
             }
 
             // if we got this far, something failed, redisplay form
             return GetAddViewModel();
         }
 
         private IActionResult GetAddViewModel()
         {
             ProductsViewModel viewModel = new ProductsViewModel();
             viewModel.ProductsModel = null;
             viewModel.Operation = CrudOperation.Add;
             viewModel.ViewControllerName = "Products";
             viewModel.ViewActionName = "Add";
             viewModel.SuppliersDropDownListData = Suppliers.SelectSuppliersDropDownListData();
             viewModel.CategoriesDropDownListData = Categories.SelectCategoriesDropDownListData();
             viewModel.ViewReturnUrl = "/Products/ListCrudRedirect";
 
             return View(viewModel);
         }
 
         /// <summary>
         /// GET: /Products/Update/5
         /// </summary>
         public IActionResult Update(int id)
         {
             return GetUpdateViewModel(id);
         }
 
         /// <summary>
         /// POST: /Products/Update/5
         /// </summary>
         [HttpPost]
         [ValidateAntiForgeryToken]
         public IActionResult Update(int id, ProductsViewModel viewModel, string returnUrl)
         {
             if (ModelState.IsValid)
             {
                 try
                 {
                     // update record
                     AddEditProducts(viewModel, CrudOperation.Update);
 
                     if (Url.IsLocalUrl(returnUrl))
                         return Redirect(returnUrl);
                     else
                         return RedirectToAction("ListCrudRedirect", "Products");
                 }
                 catch(Exception ex)
                 {
                     if (ex.InnerException != null)
                         ModelState.AddModelError("", ex.InnerException.Message);
                     else
                         ModelState.AddModelError("", ex.Message);
                 }
             }
 
             // if we got this far, something failed, redisplay form
             return GetUpdateViewModel(id);
         }
 
         public IActionResult GetUpdateViewModel(int id)
         {
             // select a record by primary key(s)
             Products objProducts = Products.SelectByPrimaryKey(id);
 
             // assign values to the model
             ProductsModel model = new ProductsModel();
             model.ProductID = objProducts.ProductID;
             model.ProductName = objProducts.ProductName;
             model.SupplierID = objProducts.SupplierID;
             model.CategoryID = objProducts.CategoryID;
             model.QuantityPerUnit = objProducts.QuantityPerUnit;
             model.UnitPrice = objProducts.UnitPrice;
             model.UnitsInStock = objProducts.UnitsInStock;
 
             if (objProducts.UnitsInStock.HasValue)
                 model.UnitsInStockHidden = objProducts.UnitsInStock.Value.ToString();
             else
                 model.UnitsInStockHidden = null;
 
             model.UnitsOnOrder = objProducts.UnitsOnOrder;
 
             if (objProducts.UnitsOnOrder.HasValue)
                 model.UnitsOnOrderHidden = objProducts.UnitsOnOrder.Value.ToString();
             else
                 model.UnitsOnOrderHidden = null;
 
             model.ReorderLevel = objProducts.ReorderLevel;
 
             if (objProducts.ReorderLevel.HasValue)
                 model.ReorderLevelHidden = objProducts.ReorderLevel.Value.ToString();
             else
                 model.ReorderLevelHidden = null;
 
             model.Discontinued = objProducts.Discontinued;
 
             // assign values to the view model
             ProductsViewModel viewModel = new ProductsViewModel();
             viewModel.ProductsModel = model;
             viewModel.Operation = CrudOperation.Update;
             viewModel.ViewControllerName = "Products";
             viewModel.ViewActionName = "Update";
             viewModel.SuppliersDropDownListData = Suppliers.SelectSuppliersDropDownListData();
             viewModel.CategoriesDropDownListData = Categories.SelectCategoriesDropDownListData();
 
             viewModel.ViewReturnUrl = "/Products/ListCrudRedirect";
             viewModel.URLReferrer = Request.Headers["Referer"].ToString();
 
             return View(viewModel);
         }
 
         /// <summary>
         /// POST: /Products/Delete/5
         /// </summary>
         [HttpPost]
         public IActionResult Delete(int id, ProductsViewModel viewModel, string returnUrl)
         {
             Products.Delete(id);
             return Json(true);
         }
 
         /// <summary>
         /// GET: /Products/Details/5
         /// </summary>
         public IActionResult Details(int id)
         {
             // select a record by primary key(s)
             Products objProducts = Products.SelectByPrimaryKey(id);
 
             // assign values to the model
             ProductsModel model = new ProductsModel();
             model.ProductID = objProducts.ProductID;
             model.ProductName = objProducts.ProductName;
             model.SupplierID = objProducts.SupplierID;
             model.CategoryID = objProducts.CategoryID;
             model.QuantityPerUnit = objProducts.QuantityPerUnit;
             model.UnitPrice = objProducts.UnitPrice;
             model.UnitsInStock = objProducts.UnitsInStock;
 
             if (objProducts.UnitsInStock.HasValue)
                 model.UnitsInStockHidden = objProducts.UnitsInStock.Value.ToString();
             else
                 model.UnitsInStockHidden = null;
 
             model.UnitsOnOrder = objProducts.UnitsOnOrder;
 
             if (objProducts.UnitsOnOrder.HasValue)
                 model.UnitsOnOrderHidden = objProducts.UnitsOnOrder.Value.ToString();
             else
                 model.UnitsOnOrderHidden = null;
 
             model.ReorderLevel = objProducts.ReorderLevel;
 
             if (objProducts.ReorderLevel.HasValue)
                 model.ReorderLevelHidden = objProducts.ReorderLevel.Value.ToString();
             else
                 model.ReorderLevelHidden = null;
 
             model.Discontinued = objProducts.Discontinued;
 
             // assign values to the view model
             ProductsViewModel viewModel = new ProductsViewModel();
             viewModel.ProductsModel = model;
             viewModel.SuppliersDropDownListData = Suppliers.SelectSuppliersDropDownListData();
             viewModel.CategoriesDropDownListData = Categories.SelectCategoriesDropDownListData();
 
             viewModel.ViewReturnUrl = "/Products/ListCrudRedirect";
             viewModel.URLReferrer = Request.Headers["Referer"].ToString();
 
             return View(viewModel);
         }
 
         /// <summary>
         /// GET: /Products/ListCrudRedirect
         /// </summary>
         public IActionResult ListCrudRedirect()
         {
             return View();
         }
 
         /// <summary>
         /// GET: /Products/ListReadOnly
         /// </summary>
         public IActionResult ListReadOnly()
         {
             return View();
         }
 
         /// <summary>
         /// GET: /Products/ListCrud
         /// </summary>
         public IActionResult ListCrud()
         {
             return View(GetViewModel("ListCrud"));
         }
 
         /// <summary>
         /// POST: /Products/ListCrud
         /// </summary>
         [HttpPost]
         [ValidateAntiForgeryToken]
         public IActionResult ListCrud(string inputSubmit, ProductsViewModel viewModel)
         {
             if (ModelState.IsValid)
             {
                 CrudOperation operation = CrudOperation.Add;
 
                 if (inputSubmit == "Update")
                     operation = CrudOperation.Update;
 
                 try
                 {
                     AddEditProducts(viewModel, operation);
                 }
                 catch(Exception ex)
                 {
                     ModelState.AddModelError("", ex.Message);
                 }
             }
 
             return View(GetViewModel("ListCrud"));
         }
 
         /// <summary>
         /// GET: /Products/ListTotals
         /// </summary>
         public IActionResult ListTotals()
         {
             return View();
         }
 
         /// <summary>
         /// GET: /Products/ListSearch
         /// </summary>
         public IActionResult ListSearch()
         {
             return View(GetViewModel("ListSearch"));
         }
 
         /// <summary>
         /// GET: /Products/ListScrollLoad
         /// </summary>
         public IActionResult ListScrollLoad()
         {
             return View();
         }
 
         /// <summary>
         /// GET: /Products/ListInline
         /// </summary>
         public IActionResult ListInline()
         {
             return View(GetViewModel("ListInline"));
         }
 
         /// <summary>
         /// POST: /Products/ListInlineAdd
         /// </summary>
         [HttpPost]
         public IActionResult ListInlineAdd([FromBody]ProductsModel model)
         {
             ProductsViewModel viewModel = new ProductsViewModel();
             viewModel.ProductsModel = model;
 
             AddEditProducts(viewModel, CrudOperation.Add, true);
             return Json("");
         }
 
         /// <summary>
         /// POST: /Products/ListInlineUpdate
         /// </summary>
         [HttpPost]
         public IActionResult ListInlineUpdate([FromBody]ProductsModel model)
         {
             ProductsViewModel viewModel = new ProductsViewModel();
             viewModel.ProductsModel = model;
 
             AddEditProducts(viewModel, CrudOperation.Update, true);
             return Json("");
         }
 
         /// <summary>
         /// GET: /Products/ListForeach
         /// </summary>
         public IActionResult ListForeach(string sidx, string sord, int? page)
         {
             int rows = Functions.GetGridNumberOfRows();
             int numberOfPagesToShow = Functions.GetGridNumberOfPagesToShow();
             int currentPage = page is null ? 1 : Convert.ToInt32(page);
             int startRowIndex = ((currentPage * rows) - rows) + 1;
             int totalRecords = Products.GetRecordCount();
             int totalPages = (int)Math.Ceiling((float)totalRecords / (float)rows);
             List<Products> objProductsCol = Products.SelectSkipAndTake(rows, startRowIndex, sidx + " " + sord);
 
             // fields and titles
             string[,] fieldNames = new string[,] {
                 {"ProductID", "Product ID"},
                 {"ProductName", "Product Name"},
                 {"SupplierID", "Supplier ID"},
                 {"CategoryID", "Category ID"},
                 {"QuantityPerUnit", "Quantity Per Unit"},
                 {"UnitPrice", "Unit Price"},
                 {"UnitsInStock", "Units In Stock"},
                 {"UnitsOnOrder", "Units On Order"},
                 {"ReorderLevel", "Reorder Level"},
                 {"Discontinued", "Discontinued"}
             };
 
             // view model
             ProductsForeachViewModel viewModel = new ProductsForeachViewModel();
             viewModel.ProductsData = objProductsCol;
             viewModel.ProductsFieldNames = fieldNames;
             viewModel.TotalPages = totalPages;
             viewModel.CurrentPage = currentPage;
             viewModel.FieldToSort = String.IsNullOrEmpty(sidx) ? "ProductID" : sidx;
             viewModel.FieldSortOrder = String.IsNullOrEmpty(sord) ? "asc" : sord;
             viewModel.FieldToSortWithOrder = String.IsNullOrEmpty(sidx) ? "ProductID" : (sidx + " " + sord).Trim();
             viewModel.NumberOfPagesToShow = numberOfPagesToShow;
             viewModel.StartPage = Functions.GetPagerStartPage(currentPage, numberOfPagesToShow, totalPages);
             viewModel.EndPage = Functions.GetPagerEndPage(viewModel.StartPage, currentPage, numberOfPagesToShow, totalPages);
 
             return View(viewModel);
         }
 
         /// <summary>
         /// GET: /Products/ListMasterDetailGridBySupplierID
         /// </summary>
         public IActionResult ListMasterDetailGridBySupplierID()
         {
             return View();
         }
 
         /// <summary>
         /// GET: /Products/ListMasterDetailGridByCategoryID
         /// </summary>
         public IActionResult ListMasterDetailGridByCategoryID()
         {
             return View();
         }
 
         /// <summary>
         /// GET: /Products/ListMasterDetailSubGridBySupplierID
         /// </summary>
         public IActionResult ListMasterDetailSubGridBySupplierID()
         {
             return View();
         }
 
         /// <summary>
         /// GET: /Products/ListMasterDetailSubGridByCategoryID
         /// </summary>
         public IActionResult ListMasterDetailSubGridByCategoryID()
         {
             return View();
         }
 
         /// <summary>
         /// GET: /Products/Unbound
         /// </summary>
         public IActionResult Unbound()
         {
             return View(GetUnboundViewModel());
         }
 
         /// <summary>
         /// POST: /Products/Unbound
         /// </summary>
         [HttpPost]
         [ValidateAntiForgeryToken]
         public IActionResult Unbound(ProductsViewModel viewModel, string returnUrl)
         {
             if (ModelState.IsValid)
             {
                 // do something here before redirecting
 
                 if (Url.IsLocalUrl(returnUrl))
                     return Redirect(returnUrl);
                 else
                     return RedirectToAction("Index", "Home");
             }
 
             // if we got this far, something failed, redisplay form
             return View(GetUnboundViewModel());
         }
 
         private ProductsViewModel GetUnboundViewModel()
         {
             ProductsViewModel viewModel = new ProductsViewModel();
             viewModel.ProductsModel = null;
             viewModel.ViewControllerName = "Products";
             viewModel.ViewActionName = "Unbound";
             viewModel.ViewReturnUrl = "/Home";
 
             return viewModel;
         }
 
         /// <summary>
         /// GET: /Products/AddEditProducts
         /// </summary>
         private void AddEditProducts(ProductsViewModel viewModel, CrudOperation operation, bool isForListInline = false)
         {
             ProductsModel model = viewModel.ProductsModel;
             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();
         }
 
         private ProductsViewModel GetViewModel(string actionName)
         {
             ProductsViewModel viewModel = new ProductsViewModel();
             viewModel.ProductsModel = null;
             viewModel.ViewControllerName = "Products";
             viewModel.ViewActionName = actionName;
             viewModel.SuppliersDropDownListData = Suppliers.SelectSuppliersDropDownListData();
             viewModel.CategoriesDropDownListData = Categories.SelectCategoriesDropDownListData();
 
             return viewModel;
         }
 
         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>
         /// GET: /Products/GridData
         /// </summary>
         public IActionResult GridData(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);
             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 Json(jsonData);
         }
 
         /// <summary>
         /// GET: /Products/GridDataWithFilters
         /// </summary>
         public IActionResult GridDataWithFilters(string sidx, string sord, int page, int rows, 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 Json(jsonData);
         }
 
         /// <summary>
         /// GET: /Products/GridDataWithTotals
         /// </summary>
         public IActionResult GridDataWithTotals(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 Json(jsonData);
         }
 
         /// <summary>
         /// GET: /Products/ListGroupedBySupplierID
         /// </summary>
         public IActionResult ListGroupedBySupplierID()
         {
             return View();
         }
 
         /// <summary>
         /// GET: /Products/GridDataGroupedBySupplierID
         /// </summary>
         public IActionResult GridDataGroupedBySupplierID(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 Json(jsonData);
         }
 
         /// <summary>
         /// GET: /Products/ListGroupedByCategoryID
         /// </summary>
         public IActionResult ListGroupedByCategoryID()
         {
             return View();
         }
 
         /// <summary>
         /// GET: /Products/GridDataGroupedByCategoryID
         /// </summary>
         public IActionResult GridDataGroupedByCategoryID(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 Json(jsonData);
         }
 
         /// <summary>
         /// GET: /Products/ListTotalsGroupedBySupplierID
         /// </summary>
         public IActionResult ListTotalsGroupedBySupplierID()
         {
             return View();
         }
 
         /// <summary>
         /// GET: /Products/GridDataTotalsGroupedBySupplierID
         /// </summary>
         public IActionResult GridDataTotalsGroupedBySupplierID(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 Json(jsonData);
         }
 
         /// <summary>
         /// GET: /Products/ListTotalsGroupedByCategoryID
         /// </summary>
         public IActionResult ListTotalsGroupedByCategoryID()
         {
             return View();
         }
 
         /// <summary>
         /// GET: /Products/GridDataTotalsGroupedByCategoryID
         /// </summary>
         public IActionResult GridDataTotalsGroupedByCategoryID(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 Json(jsonData);
         }
     }
}