protected void Page_Load(object sender, EventArgs e) { if (string.IsNullOrEmpty(Request["categoryid"]) || !Int32.TryParse(Request["categoryid"], out _categoryId)) { Error404(); } Category = CategoryService.GetCategory(_categoryId); if (Category == null || Category.Enabled == false || Category.ParentsEnabled == false) { Error404(); return; } Indepth = Request["indepth"] == "1" || Category.DisplayChildProducts; _priceField = CustomerSession.CurrentCustomer.PriceType.ToString(); _paging = new SqlPaging { TableName = "[Catalog].[Product] " + "LEFT JOIN [Catalog].[Offer] ON [Product].[ProductID] = [Offer].[ProductID] " + "inner join Catalog.ProductCategories on ProductCategories.ProductId = [Product].[ProductID] " + "Left JOIN [Catalog].[ProductPropertyValue] ON [Product].[ProductID] = [ProductPropertyValue].[ProductID] " + "LEFT JOIN [Catalog].[ShoppingCart] ON [Catalog].[ShoppingCart].[OfferID] = [Catalog].[Offer].[OfferID] AND [Catalog].[ShoppingCart].[ShoppingCartType] = 3 AND [ShoppingCart].[CustomerID] = @CustomerId " + "Left JOIN [Catalog].[Ratio] on Product.ProductId= Ratio.ProductID and Ratio.CustomerId=@CustomerId " + "LEFT JOIN [Customers].[LastPrice] ON [LastPrice].[ProductId] = [Product].[ProductId] AND [LastPrice].[CustomerId] = @CustomerId" }; _paging.AddFieldsRange( new List<Field> { new Field {Name = "[Product].[ProductID]", IsDistinct = true}, new Field {Name = "(CASE WHEN Offer.ColorID is not null THEN (Select Count(PhotoName) From [Catalog].[Photo] WHERE ([Photo].ColorID = Offer.ColorID or [Photo].ColorID is null) and [Product].[ProductID] = [Photo].[ObjId] and Type=@Type) ELSE (Select Count(PhotoName) From [Catalog].[Photo] WHERE [Product].[ProductID] = [Photo].[ObjId] and Type=@Type) END) AS CountPhoto"}, new Field {Name = "(CASE WHEN Offer.ColorID is not null THEN (Select TOP(1) PhotoName From [Catalog].[Photo] WHERE ([Photo].ColorID = Offer.ColorID or [Photo].ColorID is null) and [Product].[ProductID] = [Photo].[ObjId] and Type=@Type Order By main desc, [Photo].[PhotoSortOrder]) ELSE (Select TOP(1) PhotoName From [Catalog].[Photo] WHERE [Product].[ProductID] = [Photo].[ObjId] and Type=@Type AND [Photo].[Main] = 1) END) AS Photo"}, new Field {Name = "(CASE WHEN Offer.ColorID is not null THEN (Select TOP(1) [Photo].[Description] From [Catalog].[Photo] WHERE ([Photo].ColorID = Offer.ColorID or [Photo].ColorID is null) and [Product].[ProductID] = [Photo].[ObjId] and Type=@Type Order By main desc, [Photo].[PhotoSortOrder]) ELSE (Select TOP(1) [Photo].[Description] From [Catalog].[Photo] WHERE [Product].[ProductID] = [Photo].[ObjId] and Type=@Type AND [Photo].[Main] = 1) END) AS PhotoDesc"}, new Field {Name = "[ProductCategories].[CategoryID]", NotInQuery=true}, new Field {Name = "BriefDescription"}, new Field {Name = "Product.ArtNo"}, new Field {Name = "Name"}, new Field {Name = string.Format("(CASE WHEN {0}=0 THEN 0 ELSE 1 END) as TempSort", _priceField), Sorting=SortDirection.Descending}, new Field {Name = "Recomended"}, new Field {Name = "Bestseller"}, new Field {Name = "New"}, new Field {Name = "OnSale"}, new Field {Name = "Discount"}, new Field {Name = "Offer.Main", NotInQuery=true}, new Field {Name = "Offer.OfferID"}, new Field {Name = "(Select Max(Offer.Amount) from catalog.Offer Where ProductId=[Product].[ProductID]) as Amount"}, new Field {Name = "(CASE WHEN (Select Max(Offer.Amount) from catalog.Offer Where ProductId=[Product].[ProductID]) <= 0 OR (Select Max(Offer.Amount) from catalog.Offer Where ProductId=[Product].[ProductID]) < IsNull(MinAmount,0) THEN 0 ELSE 1 END) as TempAmountSort", Sorting=SortDirection.Descending}, new Field {Name = "MinAmount"}, new Field {Name = "MaxAmount"}, new Field {Name = "Multiplicity"}, new Field {Name = "Enabled"}, new Field {Name = "AllowPreOrder"}, new Field {Name = "Ratio"}, new Field {Name = "RatioID"}, new Field {Name = "DateModified"}, new Field {Name = "ShoppingCartItemId"}, new Field {Name = "UrlPath"}, new Field {Name = !Indepth ? "[ProductCategories].[SortOrder]" : "0 as SortOrder"}, new Field {Name = "[ShoppingCart].[CustomerID]", NotInQuery=true}, new Field {Name = "BrandID", NotInQuery=true}, new Field {Name = "Offer.ProductID as Size_ProductID", NotInQuery=true}, new Field {Name = "Offer.ProductID as Color_ProductID", NotInQuery=true}, new Field {Name = "Offer.ProductID as Price_ProductID", NotInQuery=true}, new Field {Name = "Offer.ColorID"}, new Field {Name = "CategoryEnabled", NotInQuery=true}, }); if (SettingsCatalog.ComplexFilter) { _paging.AddFieldsRange(new List<Field> { new Field {Name = "(select [Settings].[ProductColorsToString]([Product].[ProductID])) as Colors"}, new Field {Name = string.Format("(select max ({0}) - min ({0}) from catalog.offer where offer.productid=product.productid) as MultiPrices", _priceField)}, new Field {Name = string.Format("(select min ({0}) from catalog.offer where offer.productid=product.productid) as Price", _priceField)}, }); } else { _paging.AddFieldsRange(new List<Field> { new Field {Name = "null as Colors"}, new Field {Name = "0 as MultiPrices"}, new Field { Name = SettingsCatalog.UseLastPrice ? string.Format("(CASE WHEN ProductPrice IS NULL THEN {0} ELSE ProductPrice END) as Price", _priceField) : string.Format("{0} as Price", _priceField) }, }); } _paging.AddParam(new SqlParam { ParameterName = "@CustomerId", Value = CustomerSession.CustomerId.ToString() }); _paging.AddParam(new SqlParam { ParameterName = "@Type", Value = PhotoType.Product.ToString() }); ProductsCount = Indepth ? Category.TotalProductsCount : Category.GetProductCount(); categoryView.CategoryID = _categoryId; categoryView.Visible = Category.DisplayStyle == "True" || ProductsCount == 0; pnlSort.Visible = ProductsCount > 0; productView.Visible = ProductsCount > 0; catalogView.CategoryID = _categoryId; filterProperty.CategoryId = _categoryId; filterBrand.CategoryId = _categoryId; filterBrand.InDepth = Indepth; filterSize.CategoryId = _categoryId; filterSize.InDepth = Indepth; filterColor.CategoryId = _categoryId; filterColor.InDepth = Indepth; filterPrice.CategoryId = _categoryId; filterPrice.InDepth = Indepth; filterPrice.PriceType = CustomerSession.CurrentCustomer.PriceType; lblCategoryName.Text = _categoryId != 0 ? Category.Name : Resource.Client_MasterPage_Catalog; breadCrumbs.Items = CategoryService.GetParentCategories(_categoryId).Select(parent => new BreadCrumbs { Name = parent.Name, Url = UrlService.GetLink(ParamType.Category, parent.UrlPath, parent.CategoryId) }).Reverse().ToList(); breadCrumbs.Items.Insert(0, new BreadCrumbs { Name = Resource.Client_MasterPage_MainPage, Url = UrlService.GetAbsoluteLink("/") }); var categoryMeta = SetMeta(Category.Meta, Category.Name, page: paging.CurrentPage); lblCategoryName.Text = categoryMeta.H1; if (Category.DisplayChildProducts || Indepth) { var cfilter = new InChildCategoriesFieldFilter { CategoryId = _categoryId.ToString(), ParamName = "@CategoryID" }; _paging.Fields["[ProductCategories].[CategoryID]"].Filter = cfilter; } else { var cfilter = new EqualFieldFilter { Value = _categoryId.ToString(), ParamName = "@catalog" }; _paging.Fields["[ProductCategories].[CategoryID]"].Filter = cfilter; } _paging.Fields["Enabled"].Filter = new EqualFieldFilter { Value = "1", ParamName = "@enabled" }; _paging.Fields["CategoryEnabled"].Filter = new EqualFieldFilter { Value = "1", ParamName = "@CategoryEnabled" }; var logicalFilter = new LogicalFilter{ ParamName = "@Main", HideInCustomData = true }; logicalFilter.AddFilter(new EqualFieldFilter {Value = "1", ParamName = "@Main1", HideInCustomData = true}); logicalFilter.AddLogicalOperation("OR"); logicalFilter.AddFilter(new NullFieldFilter { Null = true, ParamName = "@Main2", HideInCustomData = true }); _paging.Fields["Offer.Main"].Filter = logicalFilter; BuildSorting(); BuildFilter(); }
protected void Page_Load(object sender, EventArgs e) { _paging = new SqlPaging { TableName = "[Catalog].[Product] LEFT JOIN [Catalog].[Offer] ON [Product].[ProductID] = [Offer].[ProductID] inner join Catalog.ProductCategories on ProductCategories.ProductId = [Product].[ProductID] Left JOIN [Catalog].[ProductPropertyValue] ON [Product].[ProductID] = [ProductPropertyValue].[ProductID] LEFT JOIN [Catalog].[ShoppingCart] ON [Catalog].[ShoppingCart].[OfferID] = [Catalog].[Offer].[OfferID] AND [Catalog].[ShoppingCart].[ShoppingCartType] = 3 AND [ShoppingCart].[CustomerID] = @CustomerId Left JOIN [Catalog].[Ratio] on Product.ProductId= Ratio.ProductID and Ratio.CustomerId=@CustomerId" }; _paging.AddFieldsRange( new List<Field> { new Field {Name = "[Product].[ProductID]", IsDistinct = true}, //new Field {Name = "PhotoName AS Photo"}, new Field {Name = "(CASE WHEN Offer.ColorID is not null THEN (Select Count(PhotoName) From [Catalog].[Photo] WHERE ([Photo].ColorID = Offer.ColorID or [Photo].ColorID is null) and [Product].[ProductID] = [Photo].[ObjId] and Type=@Type) ELSE (Select Count(PhotoName) From [Catalog].[Photo] WHERE [Product].[ProductID] = [Photo].[ObjId] and Type=@Type) END) AS CountPhoto"}, new Field {Name = "(CASE WHEN Offer.ColorID is not null THEN (Select TOP(1) PhotoName From [Catalog].[Photo] WHERE ([Photo].ColorID = Offer.ColorID or [Photo].ColorID is null) and [Product].[ProductID] = [Photo].[ObjId] and Type=@Type Order By main desc, [Photo].[PhotoSortOrder]) ELSE (Select TOP(1) PhotoName From [Catalog].[Photo] WHERE [Product].[ProductID] = [Photo].[ObjId] and Type=@Type AND [Photo].[Main] = 1) END) AS Photo"}, new Field {Name = "(CASE WHEN Offer.ColorID is not null THEN (Select TOP(1) [Photo].[Description] From [Catalog].[Photo] WHERE ([Photo].ColorID = Offer.ColorID or [Photo].ColorID is null) and [Product].[ProductID] = [Photo].[ObjId] and Type=@Type Order By main desc, [Photo].[PhotoSortOrder]) ELSE (Select TOP(1) [Photo].[Description] From [Catalog].[Photo] WHERE [Product].[ProductID] = [Photo].[ObjId] and Type=@Type AND [Photo].[Main] = 1) END) AS PhotoDesc"}, new Field {Name = "(select [Settings].[ProductColorsToString]([Product].[ProductID])) as Colors"}, //new Field {Name = "[Photo].[Description] AS PhotoDesc"}, new Field {Name = "[ProductCategories].[CategoryID]", NotInQuery=true}, new Field {Name = "BriefDescription"}, new Field {Name = "Product.ArtNo"}, new Field {Name = "Name"}, new Field {Name = "(CASE WHEN Price=0 THEN 0 ELSE 1 END) as TempSort", Sorting=SortDirection.Descending}, new Field {Name = "Recomended"}, new Field {Name = "Bestseller"}, new Field {Name = "New"}, new Field {Name = "OnSale"}, new Field {Name = "Discount"}, new Field {Name = "Offer.Main", NotInQuery=true}, new Field {Name = "Offer.OfferID"}, new Field {Name = "Offer.Amount"}, new Field {Name = "(CASE WHEN Offer.Amount=0 OR Offer.Amount < IsNull(MinAmount,0) THEN 0 ELSE 1 END) as TempAmountSort", Sorting=SortDirection.Descending}, new Field {Name = "MinAmount"}, new Field {Name = "MaxAmount"}, new Field {Name = "Enabled"}, new Field {Name = "AllowPreOrder"}, new Field {Name = "Ratio"}, new Field {Name = "RatioID"}, new Field {Name = "DateModified"}, new Field {Name = "ShoppingCartItemId"}, new Field {Name = "UrlPath"}, new Field {Name = "[ProductCategories].[SortOrder]"}, new Field {Name = "[ShoppingCart].[CustomerID]", NotInQuery=true}, new Field {Name = "BrandID", NotInQuery=true}, new Field {Name = "Offer.ProductID as Size_ProductID", NotInQuery=true}, new Field {Name = "Offer.ProductID as Color_ProductID", NotInQuery=true}, new Field {Name = "Offer.ProductID as Price_ProductID", NotInQuery=true}, new Field {Name = "Offer.ColorID"}, new Field {Name = "CategoryEnabled", NotInQuery=true}, }); if (SettingsCatalog.ComplexFilter) { _paging.AddFieldsRange(new List<Field> { new Field {Name = "(select max (price) - min (price) from catalog.offer where offer.productid=product.productid) as MultiPrices"}, new Field {Name = "(select min (price) from catalog.offer where offer.productid=product.productid) as Price"}, }); } else { _paging.AddFieldsRange(new List<Field> { new Field {Name = "0 as MultiPrices"}, new Field {Name = "Price"}, }); } _paging.AddParam(new SqlParam { ParameterName = "@CustomerId", Value = CustomerSession.CustomerId.ToString() }); _paging.AddParam(new SqlParam { ParameterName = "@Type", Value = PhotoType.Product.ToString() }); if (string.IsNullOrEmpty(Request["categoryid"]) || !Int32.TryParse(Request["categoryid"], out _categoryId)) { _categoryId = 0; var sbMainPage = StaticBlockService.GetPagePartByKeyWithCache("MainPageSocial"); if (sbMainPage != null && sbMainPage.Enabled) MainPageText = sbMainPage.Content; } if (!string.IsNullOrEmpty(MainPageText)) { SetMeta(null, string.Empty); return; } category = CategoryService.GetCategory(_categoryId); if (category == null || category.Enabled == false || category.ParentsEnabled == false) { Error404(); return; } ProductsCount = category.GetProductCount(); categoryView.CategoryID = _categoryId; categoryView.Visible = true; pnlSort.Visible = ProductsCount > 0; productView.Visible = ProductsCount > 0; lblCategoryName.Text = _categoryId != 0 ? category.Name : Resource.Client_MasterPage_Catalog; //lblCategoryDescription.Text = category.Description; //imgCategoryImage.ImageUrl = string.IsNullOrEmpty(category.Picture) ? "" : string.Format("{0}", ImageFolders.GetImageCategoryPath(false, category.Picture)); breadCrumbs.Items = CategoryService.GetParentCategories(_categoryId).Select(parent => new BreadCrumbs { Name = parent.Name, Url = "social/catalogsocial.aspx?categoryid=" + parent.CategoryId }).Reverse().ToList(); breadCrumbs.Items.Insert(0, new BreadCrumbs { Name = Resource.Client_MasterPage_MainPage, Url = UrlService.GetAbsoluteLink("social/catalogsocial.aspx") }); SetMeta(category.Meta, category.Name); if (category.DisplayChildProducts) { var cfilter = new InChildCategoriesFieldFilter { CategoryId = _categoryId.ToString(), ParamName = "@CategoryID" }; _paging.Fields["[ProductCategories].[CategoryID]"].Filter = cfilter; } else { var cfilter = new EqualFieldFilter { Value = _categoryId.ToString(), ParamName = "@catalog" }; _paging.Fields["[ProductCategories].[CategoryID]"].Filter = cfilter; } _paging.Fields["Enabled"].Filter = new EqualFieldFilter { Value = "1", ParamName = "@enabled" }; ; _paging.Fields["CategoryEnabled"].Filter = new EqualFieldFilter { Value = "1", ParamName = "@CategoryEnabled" }; var logicalFilter = new LogicalFilter { ParamName = "@Main", HideInCustomData = true }; logicalFilter.AddFilter(new EqualFieldFilter { Value = "1", ParamName = "@Main1", HideInCustomData = true }); logicalFilter.AddLogicalOperation("OR"); logicalFilter.AddFilter(new NullFieldFilter { Null = true, ParamName = "@Main2", HideInCustomData = true }); _paging.Fields["Offer.Main"].Filter = logicalFilter; BuildSorting(); }