Imports System
Imports System.Text
Imports System.Linq
Imports System.Web.Mvc
Imports NorthwindVB.BusinessObject
Imports NorthwindVB.Models
Imports NorthwindVB.ViewModels
Imports NorthwindVB.Domain
Imports System.Collections.Generic
Imports System.Configuration
Imports System.Text.RegularExpressions
Imports System.Runtime.InteropServices
 
Namespace 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
         Inherits System.Web.Mvc.Controller
 
        ''' <summary>
        ''' GET: /Products/
        ''' </summary>
        Public Function Index() As ActionResult
            Return View()
        End Function
 
        ''' <summary>
        ''' GET: /Products/Add
        ''' </summary>
        Public Function Add() As ActionResult
            Return GetAddViewModel()
        End Function
 
        ''' <summary>
        ''' POST: /Products/Add
        ''' </summary>
        <HttpPost()> _
        <ValidateAntiForgeryToken> _
        Public Function Add(viewModel As ProductsViewModel, returnUrl As String) As ActionResult
            If ModelState.IsValid Then
                Try
                    ' add new record
                    AddEditProducts(viewModel, CrudOperation.Add)
 
                    If Url.IsLocalUrl(returnUrl) Then
                        Return Redirect(returnUrl)
                    Else
                        Return RedirectToAction("Index")
                    End If
                Catch ex As Exception
                    ModelState.AddModelError("", ex.Message)
                End Try
            End If
 
            ' if we got this far, something failed, redisplay form
            Return GetAddViewModel()
        End Function
 
        Private Function GetAddViewModel() As ActionResult
            Dim viewModel As New ProductsViewModel()
            viewModel.ProductsModel = Nothing
            viewModel.Operation = CrudOperation.Add
            viewModel.ViewControllerName = "Products"
            viewModel.ViewActionName = "Add"
            viewModel.SuppliersDropDownListData = BusinessObject.Suppliers.SelectSuppliersDropDownListData()
            viewModel.CategoriesDropDownListData = BusinessObject.Categories.SelectCategoriesDropDownListData()
 
            If Request.UrlReferrer IsNot Nothing Then
                viewModel.ViewReturnUrl = Request.UrlReferrer.PathAndQuery
            Else
                viewModel.ViewReturnUrl = "Index"
            End If
 
            Return View(viewModel)
        End Function
 
        ''' <summary>
        ''' GET: /Products/Update/5
        ''' </summary>
        Public Function Update(id As Integer) As ActionResult
            Return GetUpdateViewModel(id)
        End Function
 
        ''' <summary>
        ''' POST: /Products/Update/5
        ''' </summary>
        <HttpPost()> _
        <ValidateAntiForgeryToken> _
        Public Function Update(id As Integer, viewModel As ProductsViewModel, returnUrl As String) As ActionResult
            If ModelState.IsValid Then
                Try
                    ' update record
                    AddEditProducts(viewModel, CrudOperation.Update)
 
                    If Url.IsLocalUrl(returnUrl) Then
                        Return Redirect(returnUrl)
                    Else
                        Return RedirectToAction("Index")
                    End If
                Catch ex As Exception
                    ModelState.AddModelError("", ex.Message)
                End Try
            End If
 
            ' if we got this far, something failed, redisplay form
            Return GetUpdateViewModel(id)
        End Function
 
        Public Function GetUpdateViewModel(id As Integer) As ActionResult
            ' select a record by primary key(s)
            Dim objProducts As Products = Products.SelectByPrimaryKey(id)
 
            ' assign values to the model
            Dim model As New Models.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
            model.UnitsOnOrder = objProducts.UnitsOnOrder
            model.ReorderLevel = objProducts.ReorderLevel
            model.Discontinued = objProducts.Discontinued
 
            ' assign values to the view model
            Dim viewModel As New ProductsViewModel()
            viewModel.ProductsModel = model
            viewModel.Operation = CrudOperation.Update
            viewModel.ViewControllerName = "Products"
            viewModel.ViewActionName = "Update"
            viewModel.SuppliersDropDownListData = BusinessObject.Suppliers.SelectSuppliersDropDownListData()
            viewModel.CategoriesDropDownListData = BusinessObject.Categories.SelectCategoriesDropDownListData()
 
            If Request.UrlReferrer IsNot Nothing Then
                viewModel.ViewReturnUrl = Request.UrlReferrer.PathAndQuery
            Else
                viewModel.ViewReturnUrl = "Index"
            End If
 
            Return View(viewModel)
        End Function
 
        ''' <summary>
        ''' POST: /Products/Delete/5
        ''' </summary>
        <HttpPost()> _
        Public Function Delete(id As Integer, viewModel As ProductsViewModel, returnUrl As String) As ActionResult
            Products.Delete(id)
            Return Json(True)
        End Function
 
        ''' <summary>
        ''' GET: /Products/Details/5
        ''' </summary>
        Public Function Details(id As Integer) As ActionResult
            ' select a record by primary key(s)
            Dim objProducts As Products = Products.SelectByPrimaryKey(id)
 
            ' assign values to the model
            Dim model As New Models.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
            model.UnitsOnOrder = objProducts.UnitsOnOrder
            model.ReorderLevel = objProducts.ReorderLevel
            model.Discontinued = objProducts.Discontinued
 
            ' assign values to the view model
            Dim viewModel As New ProductsViewModel()
            viewModel.ProductsModel = model
            viewModel.SuppliersDropDownListData = BusinessObject.Suppliers.SelectSuppliersDropDownListData()
            viewModel.CategoriesDropDownListData = BusinessObject.Categories.SelectCategoriesDropDownListData()
 
            If Request.UrlReferrer IsNot Nothing Then
                viewModel.ViewReturnUrl = Request.UrlReferrer.PathAndQuery
            Else
                viewModel.ViewReturnUrl = "Index"
            End If
 
            Return View(viewModel)
        End Function
 
        ''' <summary>
        ''' GET: /Products/ListCrudRedirect
        ''' </summary>
        Public Function ListCrudRedirect() As ActionResult
            Return View()
        End Function
 
        ''' <summary>
        ''' GET: /Products/ListReadOnly
        ''' </summary>
        Public Function ListReadOnly() As ActionResult
            Return View()
        End Function
 
        ''' <summary>
        ''' GET: /Products/ListCrud
        ''' </summary>
        Public Function ListCrud() As ActionResult
            Return View(GetViewModel())
        End Function
 
        ''' <summary>
        ''' POST: /Products/ListCrud
        ''' </summary>
        <HttpPost()> _
        Public Function ListCrud(inputSubmit As String, viewModel As ProductsViewModel) As ActionResult
            If ModelState.IsValid Then
                Dim operation As CrudOperation = CrudOperation.Add
 
                If inputSubmit = "Update" Then
                    operation = CrudOperation.Update
                End If
 
                Try
                    AddEditProducts(viewModel, operation)
                Catch ex As Exception
                    ModelState.AddModelError("", ex.Message)
                End Try
            End If
 
            Return View(GetViewModel())
        End Function
 
        ''' <summary>
        ''' GET: /Products/ListTotals
        ''' </summary>
        Public Function ListTotals() As ActionResult
            Return View()
        End Function
 
        ''' <summary>
        ''' GET: /Products/ListSearch
        ''' </summary>
        Public Function ListSearch() As ActionResult
            Return View(GetViewModel())
        End Function
 
        ''' <summary>
        ''' GET: /Products/ListScrollLoad
        ''' </summary>
        Public Function ListScrollLoad() As ActionResult
            Return View()
        End Function
 
        ''' <summary>
        ''' GET: /Products/ListInline
        ''' </summary>
        Public Function ListInline() As ActionResult
            Return View(GetViewModel())
        End Function
 
         ''' <summary>
         ''' POST: /Products/ListInlineAdd
         ''' </summary>
         <HttpPost> _
         Public Function ListInlineAdd(model As Models.ProductsModel) As ActionResult
             Dim viewModel As ProductsViewModel = New ProductsViewModel()
             viewModel.ProductsModel = model
 
             AddEditProducts(viewModel, CrudOperation.Add)
             Return Json("", JsonRequestBehavior.AllowGet)
         End Function
 
         ''' <summary>
         ''' POST: /Products/ListInlineUpdate
         ''' </summary>
         <HttpPost> _
         Public Function ListInlineUpdate(model As Models.ProductsModel) As ActionResult
             Dim viewModel As ProductsViewModel = New ProductsViewModel()
             viewModel.ProductsModel = model
 
             AddEditProducts(viewModel, CrudOperation.Update)
             Return Json("", JsonRequestBehavior.AllowGet)
         End Function
 
         ''' <summary>
         ''' GET: /Products/ListForeach
         ''' </summary>
         Public Function ListForeach(sidx As String, sord As String, page As Integer?) As ActionResult
             Dim rows As Integer = Convert.ToInt32(ConfigurationManager.AppSettings("GridNumberOfRows"))
             Dim numberOfPagesToShow As Integer = Convert.ToInt32(ConfigurationManager.AppSettings("GridNumberOfPagesToShow"))
             Dim currentPage As Integer = If(page Is Nothing, 1, Convert.ToInt32(page))
             Dim startRowIndex As Integer = (currentPage * rows) - rows
             Dim totalRecords As Integer = Products.GetRecordCount()
             Dim totalPages As Integer = CInt(Math.Ceiling(CSng(totalRecords) / CSng(rows)))
             Dim objProductsCol As ProductsCollection = Products.SelectSkipAndTake(rows, startRowIndex, sidx & " " & sord)
 
             ' fields and titles
             Dim fieldNames As String(,) = 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
             Dim viewModel As ProductsForeachViewModel = New ProductsForeachViewModel()
             viewModel.ProductsData = objProductsCol
             viewModel.ProductsFieldNames = fieldNames
             viewModel.TotalPages = totalPages
             viewModel.CurrentPage = currentPage
             viewModel.FieldToSort = If([String].IsNullOrEmpty(sidx), "ProductID", sidx)
             viewModel.FieldSortOrder = If([String].IsNullOrEmpty(sord), "asc", sord)
             viewModel.FieldToSortWithOrder = If([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)
         End Function
 
        ''' <summary>
        ''' GET: /Products/ListMasterDetailGridBySupplierID
        ''' </summary>
        Public Function ListMasterDetailGridBySupplierID() As ActionResult
            Return View()
        End Function
 
        ''' <summary>
        ''' GET: /Products/ListMasterDetailGridByCategoryID
        ''' </summary>
        Public Function ListMasterDetailGridByCategoryID() As ActionResult
            Return View()
        End Function
 
        ''' <summary>
        ''' GET: /Products/ListMasterDetailSubGridBySupplierID
        ''' </summary>
        Public Function ListMasterDetailSubGridBySupplierID() As ActionResult
            Return View()
        End Function
 
        ''' <summary>
        ''' GET: /Products/ListMasterDetailSubGridByCategoryID
        ''' </summary>
        Public Function ListMasterDetailSubGridByCategoryID() As ActionResult
            Return View()
        End Function
 
        ''' <summary>
        ''' GET: /Products/Unbound
        ''' </summary>
        Public Function Unbound() As ActionResult
            Return View(GetUnboundViewModel())
        End Function
 
        ''' <summary>
        ''' POST: /Products/Unbound
        ''' </summary>
        <HttpPost()> _
        <ValidateAntiForgeryToken> _
        Public Function Unbound(viewModel As ProductsViewModel, returnUrl As String) As ActionResult
            If ModelState.IsValid Then
                ' do something here before redirecting
 
                If Url.IsLocalUrl(returnUrl) Then
                    Return Redirect(returnUrl)
                Else
                    Return RedirectToAction("/")
                End If
            End If
 
            ' if we got this far, something failed, redisplay form
            Return View(GetUnboundViewModel())
        End Function
 
        Private Function GetUnboundViewModel() As ProductsViewModel
            Dim viewModel As ProductsViewModel = New ProductsViewModel()
            viewModel.ProductsModel = Nothing
            viewModel.ViewControllerName = "Products"
 
            If Request.UrlReferrer IsNot Nothing Then
                viewModel.ViewReturnUrl = Request.UrlReferrer.PathAndQuery
            Else
                viewModel.ViewReturnUrl = "/"
            End If
 
            Return viewModel
        End Function
 
 
        ''' <summary>
        ''' GET: /Products/AddEditProducts
        ''' </summary>
        Private Sub AddEditProducts(viewModel As ProductsViewModel, operation As CrudOperation)
            Dim model As Models.ProductsModel = viewModel.ProductsModel
             Dim objProducts As Products
 
             If operation = CrudOperation.Add Then
                objProducts = New Products()
             Else
                 objProducts = Products.SelectByPrimaryKey(model.ProductID)
             End If
 
             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.UnitsInStock = model.UnitsInStock
             objProducts.UnitsOnOrder = model.UnitsOnOrder
             objProducts.ReorderLevel = model.ReorderLevel
             objProducts.Discontinued = model.Discontinued
 
             If operation = CrudOperation.Add Then
                  objProducts.Insert()
             Else
                  objProducts.Update()
             End If
          End Sub
 
        Private Function GetViewModel() As ProductsViewModel
            Dim viewModel As New ProductsViewModel()
            viewModel.ProductsModel = Nothing
            viewModel.SuppliersDropDownListData = BusinessObject.Suppliers.SelectSuppliersDropDownListData()
            viewModel.CategoriesDropDownListData = BusinessObject.Categories.SelectCategoriesDropDownListData()
 
            Return viewModel
        End Function
 
        Private Function GetFilteredData(sidx As String, sord As String, filters As String, <Out()> ByRef totalRecords As Integer, rows As Integer, startRowIndex As Integer, sortExpression As String) As ProductsCollection
            If Not [String].IsNullOrEmpty(filters) Then
                If (filters.Contains("field") AndAlso  filters.Contains("op") AndAlso  filters.Contains("data")) Then
                    filters = filters.Replace("{""groupOp"":""AND"",""rules"":[{", "")
                    filters = filters.Replace("}]}", "")
 
                    Dim filterArray As String() = Regex.Split(filters, "},{")
                    Dim fieldName As New List(Of String)()
                    Dim data As New List(Of String)()
                    Dim ctr As Integer = 0
                    Dim productID As System.Nullable(Of Integer) = Nothing
                    Dim productName As String = String.Empty
                    Dim supplierID As System.Nullable(Of Integer) = Nothing
                    Dim categoryID As System.Nullable(Of Integer) = Nothing
                    Dim quantityPerUnit As String = String.Empty
                    Dim unitPrice As System.Nullable(Of Decimal) = Nothing
                    Dim unitsInStock As System.Nullable(Of Short) = Nothing
                    Dim unitsOnOrder As System.Nullable(Of Short) = Nothing
                    Dim reorderLevel As System.Nullable(Of Short) = Nothing
                    Dim discontinued As System.Nullable(Of Boolean) = Nothing
 
                    For Each filter As String In filterArray
                        Dim fieldsArray As String() = Regex.Split(filter, ",")
                        fieldName.Add(fieldsArray(0).Replace("""field"":", "").Replace("""", "").ToLower().Trim())
                        data.Add(fieldsArray(2).Replace("""data"":", "").Replace("""", "").ToLower().Trim())
                    Next
 
                    For Each item As String In fieldName
                        if item = "productid" Then
                            productID = Convert.ToInt32(data(ctr))
                        End If
 
                        If item = "productname" Then
                            productName = data(ctr)
                        End If
 
                        if item = "supplierid" Then
                            supplierID = Convert.ToInt32(data(ctr))
                        End If
 
                        if item = "categoryid" Then
                            categoryID = Convert.ToInt32(data(ctr))
                        End If
 
                        If item = "quantityperunit" Then
                            quantityPerUnit = data(ctr)
                        End If
 
                        if item = "unitprice" Then
                            unitPrice = Convert.ToDecimal(data(ctr))
                        End If
 
                        if item = "unitsinstock" Then
                            unitsInStock = Convert.ToInt16(data(ctr))
                        End If
 
                        if item = "unitsonorder" Then
                            unitsOnOrder = Convert.ToInt16(data(ctr))
                        End If
 
                        if item = "reorderlevel" Then
                            reorderLevel = Convert.ToInt16(data(ctr))
                        End If
 
                        if item = "discontinued" Then
                            discontinued = Convert.ToBoolean(data(ctr))
                        End If
 
                        ctr += 1
                    Next
 
                    totalRecords = Products.GetRecordCountDynamicWhere(productID, productName, supplierID, categoryID, quantityPerUnit, unitPrice, unitsInStock, unitsOnOrder, reorderLevel, discontinued)
                    Return Products.SelectSkipAndTakeDynamicWhere(productID, productName, supplierID, categoryID, quantityPerUnit, unitPrice, unitsInStock, unitsOnOrder, reorderLevel, discontinued, rows, startRowIndex, sortExpression)
                End If
            End If
 
            totalRecords = Products.GetRecordCount()
            Return Products.SelectSkipAndTake(rows, startRowIndex, sortExpression)
        End Function
 
        ''' <summary>
        ''' GET: /Products/GridData
        ''' </summary>
        Public Function GridData(sidx As String, sord As String, page As Integer, rows As Integer) As ActionResult
             Dim totalRecords As Integer = 0
             Dim startRowIndex As Integer = (page * rows) - rows
             Dim objProductsCol As ProductsCollection = Products.SelectSkipAndTake(rows, startRowIndex, totalRecords, sidx + " " + sord)
             Dim totalPages As Integer = CInt(Math.Ceiling(CSng(totalRecords) / CSng(rows)))
 
            Dim jsonData = New With { _
                .total = totalPages, _
                .page = page, _
                .records = totalRecords, _
                 .rows = (
                     From objProducts In objProductsCol
                     Select New With { _
                        .id = objProducts.ProductID, _
                        .cell = New String() { _
                             objProducts.ProductID.ToString(), _
                             objProducts.ProductName, _
                             If(objProducts.SupplierID.HasValue, objProducts.SupplierID.Value.ToString(), ""), _
                             If(objProducts.CategoryID.HasValue, objProducts.CategoryID.Value.ToString(), ""), _
                             objProducts.QuantityPerUnit, _
                             If(objProducts.UnitPrice.HasValue, objProducts.UnitPrice.Value.ToString(), ""), _
                             If(objProducts.UnitsInStock.HasValue, objProducts.UnitsInStock.Value.ToString(), ""), _
                             If(objProducts.UnitsOnOrder.HasValue, objProducts.UnitsOnOrder.Value.ToString(), ""), _
                             If(objProducts.ReorderLevel.HasValue, objProducts.ReorderLevel.Value.ToString(), ""), _
                             objProducts.Discontinued.ToString() _
                        }
                    } _
               ).ToArray() _
            }
 
             Return Json(jsonData, JsonRequestBehavior.AllowGet)
        End Function
 
        ''' <summary>
        ''' GET: /Products/GridDataWithFilters
        ''' </summary>
        Public Function GridDataWithFilters(sidx As String, sord As String, page As Integer, rows As Integer, filters As String) As ActionResult
             Dim totalRecords As Integer = 0
             Dim startRowIndex As Integer = (page * rows) - rows
             Dim objProductsCol As ProductsCollection = Me.GetFilteredData(sidx, sord, filters, totalRecords, rows, startRowIndex, sidx + " " + sord)
             Dim totalPages As Integer = CInt(Math.Ceiling(CSng(totalRecords) / CSng(rows)))
 
             If objProductsCol Is Nothing Then
                 Return Json("{ total = 0, page = 0, records = 0, rows = null }", JsonRequestBehavior.AllowGet)
             End If
 
            Dim jsonData = New With { _
                .total = totalPages, _
                .page = page, _
                .records = totalRecords, _
                 .rows = (
                     From objProducts In objProductsCol
                     Select New With { _
                        .id = objProducts.ProductID, _
                        .cell = New String() { _
                             objProducts.ProductID.ToString(), _
                             objProducts.ProductName, _
                             If(objProducts.SupplierID.HasValue, objProducts.SupplierID.Value.ToString(), ""), _
                             If(objProducts.CategoryID.HasValue, objProducts.CategoryID.Value.ToString(), ""), _
                             objProducts.QuantityPerUnit, _
                             If(objProducts.UnitPrice.HasValue, objProducts.UnitPrice.Value.ToString(), ""), _
                             If(objProducts.UnitsInStock.HasValue, objProducts.UnitsInStock.Value.ToString(), ""), _
                             If(objProducts.UnitsOnOrder.HasValue, objProducts.UnitsOnOrder.Value.ToString(), ""), _
                             If(objProducts.ReorderLevel.HasValue, objProducts.ReorderLevel.Value.ToString(), ""), _
                             objProducts.Discontinued.ToString() _
                        }
                    } _
               ).ToArray() _
            }
 
             Return Json(jsonData, JsonRequestBehavior.AllowGet)
        End Function
 
        ''' <summary>
        ''' GET: /Products/GridDataWithTotals
        ''' </summary>
        Public Function GridDataWithTotals(sidx As String, sord As String, page As Integer, rows As Integer) As ActionResult
             Dim totalRecords As Integer = 0
             Dim startRowIndex As Integer = (page * rows) - rows
             Dim objProductsCol As ProductsCollection = Products.SelectSkipAndTake(rows, startRowIndex, totalRecords, sidx + " " + sord)
             Dim totalPages As Integer = CInt(Math.Ceiling(CSng(totalRecords) / CSng(rows)))
 
            Dim jsonData = New With { _
                .total = totalPages, _
                .page = page, _
                .records = totalRecords, _
                .userdata = New With { _
                    .UnitPrice = objProductsCol.Select(Function(p) p.UnitPrice).Sum().ToString()
                },
                 .rows = (
                     From objProducts In objProductsCol
                     Select New With { _
                        .id = objProducts.ProductID, _
                        .cell = New String() { _
                             objProducts.ProductID.ToString(), _
                             objProducts.ProductName, _
                             If(objProducts.SupplierID.HasValue, objProducts.SupplierID.Value.ToString(), ""), _
                             If(objProducts.CategoryID.HasValue, objProducts.CategoryID.Value.ToString(), ""), _
                             objProducts.QuantityPerUnit, _
                             If(objProducts.UnitPrice.HasValue, objProducts.UnitPrice.Value.ToString(), ""), _
                             If(objProducts.UnitsInStock.HasValue, objProducts.UnitsInStock.Value.ToString(), ""), _
                             If(objProducts.UnitsOnOrder.HasValue, objProducts.UnitsOnOrder.Value.ToString(), ""), _
                             If(objProducts.ReorderLevel.HasValue, objProducts.ReorderLevel.Value.ToString(), ""), _
                             objProducts.Discontinued.ToString() _
                        }
                    } _
               ).ToArray() _
            }
 
             Return Json(jsonData, JsonRequestBehavior.AllowGet)
        End Function
 
        ''' <summary>
        ''' GET: /Products/ListGroupedBySupplierID
        ''' </summary>
        Public Function ListGroupedBySupplierID() As ActionResult
            Return View()
        End Function
 
        ''' <summary>
        ''' GET: /Products/GridDataGroupedBySupplierID
        ''' </summary>
        Public Function GridDataGroupedBySupplierID(sidx As String, sord As String, page As Integer, rows As Integer) As ActionResult
             ' using a groupField in the jqgrid passes that field
             ' along with the field to sort, remove the groupField
             Dim groupBy As String = "CompanyName asc, "
             sidx = sidx.Replace(groupBy, "")
 
             Dim totalRecords As Integer = 0
             Dim startRowIndex As Integer = (page * rows) - rows
             Dim objProductsCol As ProductsCollection = Products.SelectSkipAndTake(rows, startRowIndex, totalRecords, sidx + " " + sord)
             Dim totalPages As Integer = CInt(Math.Ceiling(CSng(totalRecords) / CSng(rows)))
 
            Dim jsonData = New With { _
                .total = totalPages, _
                .page = page, _
                .records = totalRecords, _
                 .rows = (
                     From objProducts In objProductsCol
                     Select New With { _
                        .id = objProducts.ProductID, _
                        .cell = New String() { _
                             objProducts.ProductID.ToString(), _
                             objProducts.ProductName, _
                             If(objProducts.SupplierID.HasValue, objProducts.SupplierID.Value.ToString(), ""), _
                             If(objProducts.CategoryID.HasValue, objProducts.CategoryID.Value.ToString(), ""), _
                             objProducts.QuantityPerUnit, _
                             If(objProducts.UnitPrice.HasValue, objProducts.UnitPrice.Value.ToString(), ""), _
                             If(objProducts.UnitsInStock.HasValue, objProducts.UnitsInStock.Value.ToString(), ""), _
                             If(objProducts.UnitsOnOrder.HasValue, objProducts.UnitsOnOrder.Value.ToString(), ""), _
                             If(objProducts.ReorderLevel.HasValue, objProducts.ReorderLevel.Value.ToString(), ""), _
                             objProducts.Discontinued.ToString(), _
                             If(objProducts.SupplierID Is Nothing, "", objProducts.Suppliers.Value.CompanyName) _
                        }
                    } _
               ).ToArray() _
            }
 
             Return Json(jsonData, JsonRequestBehavior.AllowGet)
        End Function
 
        ''' <summary>
        ''' GET: /Products/ListGroupedByCategoryID
        ''' </summary>
        Public Function ListGroupedByCategoryID() As ActionResult
            Return View()
        End Function
 
        ''' <summary>
        ''' GET: /Products/GridDataGroupedByCategoryID
        ''' </summary>
        Public Function GridDataGroupedByCategoryID(sidx As String, sord As String, page As Integer, rows As Integer) As ActionResult
             ' using a groupField in the jqgrid passes that field
             ' along with the field to sort, remove the groupField
             Dim groupBy As String = "CategoryName asc, "
             sidx = sidx.Replace(groupBy, "")
 
             Dim totalRecords As Integer = 0
             Dim startRowIndex As Integer = (page * rows) - rows
             Dim objProductsCol As ProductsCollection = Products.SelectSkipAndTake(rows, startRowIndex, totalRecords, sidx + " " + sord)
             Dim totalPages As Integer = CInt(Math.Ceiling(CSng(totalRecords) / CSng(rows)))
 
            Dim jsonData = New With { _
                .total = totalPages, _
                .page = page, _
                .records = totalRecords, _
                 .rows = (
                     From objProducts In objProductsCol
                     Select New With { _
                        .id = objProducts.ProductID, _
                        .cell = New String() { _
                             objProducts.ProductID.ToString(), _
                             objProducts.ProductName, _
                             If(objProducts.SupplierID.HasValue, objProducts.SupplierID.Value.ToString(), ""), _
                             If(objProducts.CategoryID.HasValue, objProducts.CategoryID.Value.ToString(), ""), _
                             objProducts.QuantityPerUnit, _
                             If(objProducts.UnitPrice.HasValue, objProducts.UnitPrice.Value.ToString(), ""), _
                             If(objProducts.UnitsInStock.HasValue, objProducts.UnitsInStock.Value.ToString(), ""), _
                             If(objProducts.UnitsOnOrder.HasValue, objProducts.UnitsOnOrder.Value.ToString(), ""), _
                             If(objProducts.ReorderLevel.HasValue, objProducts.ReorderLevel.Value.ToString(), ""), _
                             objProducts.Discontinued.ToString(), _
                             If(objProducts.CategoryID Is Nothing, "", objProducts.Categories.Value.CategoryName) _
                        }
                    } _
               ).ToArray() _
            }
 
             Return Json(jsonData, JsonRequestBehavior.AllowGet)
        End Function
 
        ''' <summary>
        ''' GET: /Products/ListTotalsGroupedBySupplierID
        ''' </summary>
        Public Function ListTotalsGroupedBySupplierID() As ActionResult
            Return View()
        End Function
 
        ''' <summary>
        ''' GET: /Products/GridDataTotalsGroupedBySupplierID
        ''' </summary>
        Public Function GridDataTotalsGroupedBySupplierID(sidx As String, sord As String, page As Integer, rows As Integer) As ActionResult
             ' using a groupField in the jqgrid passes that field
             ' along with the field to sort, remove the groupField
             Dim groupBy As String = "CompanyName asc, "
             sidx = sidx.Replace(groupBy, "")
 
             Dim totalRecords As Integer = 0
             Dim startRowIndex As Integer = (page * rows) - rows
             Dim objProductsCol As ProductsCollection = Products.SelectSkipAndTake(rows, startRowIndex, totalRecords, sidx + " " + sord)
             Dim totalPages As Integer = CInt(Math.Ceiling(CSng(totalRecords) / CSng(rows)))
 
            Dim jsonData = New With { _
                .total = totalPages, _
                .page = page, _
                .records = totalRecords, _
                 .rows = (
                     From objProducts In objProductsCol
                     Select New With { _
                        .id = objProducts.ProductID, _
                        .cell = New String() { _
                             objProducts.ProductID.ToString(), _
                             objProducts.ProductName, _
                             If(objProducts.SupplierID.HasValue, objProducts.SupplierID.Value.ToString(), ""), _
                             If(objProducts.CategoryID.HasValue, objProducts.CategoryID.Value.ToString(), ""), _
                             objProducts.QuantityPerUnit, _
                             If(objProducts.UnitPrice.HasValue, objProducts.UnitPrice.Value.ToString(), ""), _
                             If(objProducts.UnitsInStock.HasValue, objProducts.UnitsInStock.Value.ToString(), ""), _
                             If(objProducts.UnitsOnOrder.HasValue, objProducts.UnitsOnOrder.Value.ToString(), ""), _
                             If(objProducts.ReorderLevel.HasValue, objProducts.ReorderLevel.Value.ToString(), ""), _
                             objProducts.Discontinued.ToString(), _
                             If(objProducts.SupplierID Is Nothing, "", objProducts.Suppliers.Value.CompanyName) _
                        }
                    } _
               ).ToArray() _
            }
 
             Return Json(jsonData, JsonRequestBehavior.AllowGet)
        End Function
 
        ''' <summary>
        ''' GET: /Products/ListTotalsGroupedByCategoryID
        ''' </summary>
        Public Function ListTotalsGroupedByCategoryID() As ActionResult
            Return View()
        End Function
 
        ''' <summary>
        ''' GET: /Products/GridDataTotalsGroupedByCategoryID
        ''' </summary>
        Public Function GridDataTotalsGroupedByCategoryID(sidx As String, sord As String, page As Integer, rows As Integer) As ActionResult
             ' using a groupField in the jqgrid passes that field
             ' along with the field to sort, remove the groupField
             Dim groupBy As String = "CategoryName asc, "
             sidx = sidx.Replace(groupBy, "")
 
             Dim totalRecords As Integer = 0
             Dim startRowIndex As Integer = (page * rows) - rows
             Dim objProductsCol As ProductsCollection = Products.SelectSkipAndTake(rows, startRowIndex, totalRecords, sidx + " " + sord)
             Dim totalPages As Integer = CInt(Math.Ceiling(CSng(totalRecords) / CSng(rows)))
 
            Dim jsonData = New With { _
                .total = totalPages, _
                .page = page, _
                .records = totalRecords, _
                 .rows = (
                     From objProducts In objProductsCol
                     Select New With { _
                        .id = objProducts.ProductID, _
                        .cell = New String() { _
                             objProducts.ProductID.ToString(), _
                             objProducts.ProductName, _
                             If(objProducts.SupplierID.HasValue, objProducts.SupplierID.Value.ToString(), ""), _
                             If(objProducts.CategoryID.HasValue, objProducts.CategoryID.Value.ToString(), ""), _
                             objProducts.QuantityPerUnit, _
                             If(objProducts.UnitPrice.HasValue, objProducts.UnitPrice.Value.ToString(), ""), _
                             If(objProducts.UnitsInStock.HasValue, objProducts.UnitsInStock.Value.ToString(), ""), _
                             If(objProducts.UnitsOnOrder.HasValue, objProducts.UnitsOnOrder.Value.ToString(), ""), _
                             If(objProducts.ReorderLevel.HasValue, objProducts.ReorderLevel.Value.ToString(), ""), _
                             objProducts.Discontinued.ToString(), _
                             If(objProducts.CategoryID Is Nothing, "", objProducts.Categories.Value.CategoryName) _
                        }
                    } _
               ).ToArray() _
            }
 
             Return Json(jsonData, JsonRequestBehavior.AllowGet)
        End Function
     End Class
End Namespace