// GET: /<controller>/
        public IActionResult Index(List <string> filterBrand = null, List <string> filterDepartment = null,
                                   int pageItems             = 20, int pageNumber   = 1,
                                   string search             = null, string orderBy = "createdate", string orderByModifier = "desc",
                                   string statusMessage      = null, string successMessage = null,
                                   string failureMessage     = null)
        {
            var products     = new List <Product>();
            var pageProducts = new List <Product>();

            var where = "";
            string searchTerm = null;
            var    maxPages   = 1;
            var    pageStart  = 1;
            var    pageEnd    = 1;

            //checked filter for brand and ensure no SQL injection
            if (filterBrand != null && filterBrand.Count > 0)
            {
                if (searchTerm == null)
                {
                    searchTerm = "select * from products where ";
                }

                foreach (var brand in filterBrand)
                {
                    if (!ProductHelper.CheckSQL(brand))
                    {
                        return(ControllerHelper.RedirectToLocal(this, "/Products?failureMessage=Filter not available."));
                    }
                }
                where       = ProductHelper.CreateFilterSQL("productbrandid", filterBrand, _context);
                searchTerm += where;
            }

            //if both brand and department filtered then add 'and' to where
            if (where.Length > 0 && filterDepartment != null && filterDepartment.Count > 0)
            {
                searchTerm += " and ";
            }

            //checked department filter and ensure no SQL injection
            if (filterDepartment != null && filterDepartment.Count > 0)
            {
                if (searchTerm == null)
                {
                    searchTerm = "select * from products where ";
                }

                foreach (var dept in filterDepartment)
                {
                    if (!ProductHelper.CheckSQL(dept))
                    {
                        return(ControllerHelper.RedirectToLocal(this, "/Products?failureMessage=Filter not available."));
                    }
                }
                where       = ProductHelper.CreateFilterSQL("departmentid", filterDepartment, _context);
                searchTerm += where;
            }

            if (!string.IsNullOrWhiteSpace(search))
            {
                if (where.Length > 0 && (filterDepartment != null || filterDepartment.Count > 0))
                {
                    searchTerm += " and ";
                }
                else
                {
                    searchTerm = "select * from products where ";
                }

                searchTerm += ProductHelper.CreateSearchSQL(search);
            }

            products = ProductHelper.GetProducts(_context, searchTerm, orderBy, orderByModifier);

            //select just the products relevant to selected page
            if (pageItems == 1)
            {
                maxPages = 1;
            }
            else
            {
                double pages = Convert.ToDouble(products.Count) / Convert.ToDouble(pageItems);
                maxPages = Convert.ToInt32(Math.Ceiling(pages));
            }

            if (pageNumber > 1)
            {
                pageStart = ((pageNumber - 1) * pageItems) + 1;
            }

            if (pageItems == 1 || products.Count < pageItems)
            {
                pageEnd = products.Count;
            }
            else
            {
                pageEnd = pageNumber * pageItems;
            }

            if (pageItems == 1)
            {
                pageProducts = products;
            }
            else
            {
                var i = 0;
                foreach (var product in products)
                {
                    i++;
                    if (i >= pageStart && i <= pageEnd)
                    {
                        pageProducts.Add(product);
                    }
                }
            }

            var previousUrl = string.Format("/Products?pageItems={0}&&pageNumber={1}&&orderBy={2}&&orderByModifier={3}&&search={4}",
                                            pageItems, pageNumber - 1, orderBy, orderByModifier, search);
            var nextUrl = string.Format("/Products?pageItems={0}&&pageNumber={1}&&orderBy={2}&&orderByModifier={3}&&search={4}",
                                        pageItems, pageNumber + 1, orderBy, orderByModifier, search);
            var page1Url = "";
            var page2Url = "";
            var page3Url = "";
            var page4Url = "";
            var page5Url = "";

            if (pageNumber == 1)
            {
                page1Url = string.Format("/Products?pageItems={0}&&pageNumber={1}&&orderBy={2}&&orderByModifier={3}&&search={4}",
                                         pageItems, pageNumber, orderBy, orderByModifier, search);
                page2Url = string.Format("/Products?pageItems={0}&&pageNumber={1}&&orderBy={2}&&orderByModifier={3}&&search={4}",
                                         pageItems, pageNumber + 1, orderBy, orderByModifier, search);
                page3Url = string.Format("/Products?pageItems={0}&&pageNumber={1}&&orderBy={2}&&orderByModifier={3}&&search={4}",
                                         pageItems, pageNumber + 2, orderBy, orderByModifier, search);
                page4Url = string.Format("/Products?pageItems={0}&&pageNumber={1}&&orderBy={2}&&orderByModifier={3}&&search={4}",
                                         pageItems, pageNumber + 3, orderBy, orderByModifier, search);
                page5Url = string.Format("/Products?pageItems={0}&&pageNumber={1}&&orderBy={2}&&orderByModifier={3}&&search={4}",
                                         pageItems, pageNumber + 4, orderBy, orderByModifier, search);
            }
            else if (pageNumber == 2)
            {
                page1Url = string.Format("/Products?pageItems={0}&&pageNumber={1}&&orderBy={2}&&orderByModifier={3}&&search={4}",
                                         pageItems, pageNumber - 1, orderBy, orderByModifier, search);
                page2Url = string.Format("/Products?pageItems={0}&&pageNumber={1}&&orderBy={2}&&orderByModifier={3}&&search={4}",
                                         pageItems, pageNumber, orderBy, orderByModifier, search);
                page3Url = string.Format("/Products?pageItems={0}&&pageNumber={1}&&orderBy={2}&&orderByModifier={3}&&search={4}",
                                         pageItems, pageNumber + 1, orderBy, orderByModifier, search);
                page4Url = string.Format("/Products?pageItems={0}&&pageNumber={1}&&orderBy={2}&&orderByModifier={3}&&search={4}",
                                         pageItems, pageNumber + 2, orderBy, orderByModifier, search);
                page5Url = string.Format("/Products?pageItems={0}&&pageNumber={1}&&orderBy={2}&&orderByModifier={3}&&search={4}",
                                         pageItems, pageNumber + 3, orderBy, orderByModifier, search);
            }
            else
            {
                page1Url = string.Format("/Products?pageItems={0}&&pageNumber={1}&&orderBy={2}&&orderByModifier={3}&&search={4}",
                                         pageItems, pageNumber - 2, orderBy, orderByModifier, search);
                page2Url = string.Format("/Products?pageItems={0}&&pageNumber={1}&&orderBy={2}&&orderByModifier={3}&&search={4}",
                                         pageItems, pageNumber - 1, orderBy, orderByModifier, search);
                page3Url = string.Format("/Products?pageItems={0}&&pageNumber={1}&&orderBy={2}&&orderByModifier={3}&&search={4}",
                                         pageItems, pageNumber, orderBy, orderByModifier, search);
                page4Url = string.Format("/Products?pageItems={0}&&pageNumber={1}&&orderBy={2}&&orderByModifier={3}&&search={4}",
                                         pageItems, pageNumber + 1, orderBy, orderByModifier, search);
                page5Url = string.Format("/Products?pageItems={0}&&pageNumber={1}&&orderBy={2}&&orderByModifier={3}&&search={4}",
                                         pageItems, pageNumber + 2, orderBy, orderByModifier, search);
            }

            var viewModel = new ProductListViewModel
            {
                Products    = pageProducts,
                Brands      = _context.ProductBrands.OrderBy(x => x.Brand).ToList(),
                Departments = _context.Departments.OrderBy(x => x.DepartmentName).ToList(),
                PageItems   = pageItems,
                PageNumber  = pageNumber,
                MaxPages    = maxPages,
                OrderBy     = orderBy,
                PreviousUrl = previousUrl,
                NextUrl     = nextUrl,
                PageURLs    = new List <string> {
                    page1Url, page2Url, page3Url, page4Url, page5Url
                },
                ItemCountUrl1 = string.Format("/Products?pageItems={0}&&pageNumber={1}&&orderBy={2}&&orderByModifier={3}&&search={4}",
                                              20, 1, orderBy, orderByModifier, search),
                ItemCountUrl2 = string.Format("/Products?pageItems={0}&&pageNumber={1}&&orderBy={2}&&orderByModifier={3}&&search={4}",
                                              40, 1, orderBy, orderByModifier, search),
                ItemCountUrl3 = string.Format("/Products?pageItems={0}&&pageNumber={1}&&orderBy={2}&&orderByModifier={3}&&search={4}",
                                              1, 1, orderBy, orderByModifier, search),
                CreateDateUrl = string.Format("/Products?pageItems={0}&&pageNumber={1}&&orderBy={2}&&orderByModifier={3}&&search={4}",
                                              pageItems, 1, "createDate", "desc", search),
                BrandUrl = string.Format("/Products?pageItems={0}&&pageNumber={1}&&orderBy={2}&&orderByModifier={3}&&search={4}",
                                         pageItems, 1, "brand", "asc", search),
                DepartmentUrl = string.Format("/Products?pageItems={0}&&pageNumber={1}&&orderBy={2}&&orderByModifier={3}&&search={4}",
                                              pageItems, 1, "department", "asc", search),
                PriceUrl = string.Format("/Products?pageItems={0}&&pageNumber={1}&&orderBy={2}&&orderByModifier={3}&&search={4}",
                                         pageItems, 1, "price", "asc", search),
                TitleUrl = string.Format("/Products?pageItems={0}&&pageNumber={1}&&orderBy={2}&&orderByModifier={3}&&search={4}",
                                         pageItems, 1, "title", "asc", search),
                ReturnUrl = string.Format("/Products?pageItems={0}&&pageNumber={1}&&orderBy={2}&&orderByModifier={3}&&search={4}",
                                          pageItems, pageNumber, orderBy, orderByModifier, search),
                OrderByModifier = orderByModifier,
                SuccessMessage  = successMessage,
                FailureMessage  = failureMessage,
                StatusMessage   = statusMessage
            };

            if (filterBrand != null)
            {
                viewModel.FilteredBrands = filterBrand;
            }
            else
            {
                viewModel.FilteredBrands = new List <string>();
            }

            if (filterDepartment != null)
            {
                viewModel.FilteredDepartments = filterDepartment;
            }
            else
            {
                viewModel.FilteredDepartments = new List <string>();
            }

            return(View(viewModel));
            //return ControllerHelper.RedirectToLocal(this,"/Home/ComingSoon");
        }