Page 1 of 1
Easy way to populate cat/product grid based on sales
Posted: Wed May 13, 2009 8:42 am
by heinscott
Hello... Wondering if anyone might have an easy way to accomplish this...
I would like to show the products in my category grid in order of what product has sold the most (all time).
Can anyone (Mazhar) think of an easy way to accomplish this? We were playing around with doing some custom sorts, but, this method would keep us from having to re-order things whenever new products are added.
Thanks in advance for the help.
Scott
Re: Easy way to populate cat/product grid based on sales
Posted: Wed May 13, 2009 9:25 am
by nickc
The Product class exposes that value as Product.OrderItems.ProductCount. I don't have an unmodified version of AbleCommerce handy, but I believe you could pass that as a sort expression.
Re: Easy way to populate cat/product grid based on sales
Posted: Wed May 13, 2009 9:57 am
by mazhar
The easiest way I can think is to query again. In your category grid page add a new function and change BindProductList function as below.
Code: Select all
protected void BindProductList()
{
List<Product> products = ProductDataSource.NarrowSearch(_Keywords, this.CategoryId, _ManufacturerId, 0, 0, _PageSize, (_HiddenPageIndex * _PageSize), SortResults.SelectedValue);
ProductList.DataSource = GetSortedProducts(products);
ProductList.DataBind();
NoSearchResults.Visible = (_SearchResultCount == 0);
SearchResultsAjaxPanel.Update();
}
protected List<Product> GetSortedProducts(List<Product> products)
{
List<Product> sortedProducts = new List<Product>();
string productIds = "";
foreach (Product product in products)
productIds += product.ProductId.ToString() + ",";
if (!string.IsNullOrEmpty(productIds))
{
productIds = productIds.Remove(productIds.Length - 1, 1);
string selectCommand = string.Format("SELECT P.ProductId AS ProductId, COUNT(OI.ProductId) AS OrderCount FROM ac_OrderItems AS OI RIGHT OUTER JOIN ac_Products AS P ON OI.ProductId = P.ProductId WHERE P.ProductId IN ({0}) GROUP BY OI.ProductId, P.ProductId ORDER BY OrderCount DESC",productIds);
CommerceBuilder.Data.Database database = Token.Instance.Database;
System.Data.Common.DbCommand command = database.GetSqlStringCommand(selectCommand);
IDataReader dr = database.ExecuteReader(command);
while (dr.Read())
{
Product product = ProductDataSource.Load(AlwaysConvert.ToInt(dr["ProductId"]));
sortedProducts.Add(product);
}
}
return sortedProducts;
}
Re: Easy way to populate cat/product grid based on sales
Posted: Wed May 13, 2009 12:08 pm
by heinscott
Thanks Nick and Mazhar...
I ended up doing something different, because it looks like the code you sent would only sort the items on the current page according to popularity, rather than sorting all the products, and then returning the proper amount per page.
I ended up using this method which uses results from the ReportDataSource.GetSalesByProduct method...
Code: Select all
else if (SortResults.SelectedValue.ToString().Equals("Popular"))
{
DateTime localNow = LocaleHelper.LocalNow;
DateTime lastYear = (new DateTime(localNow.Year, localNow.Month, localNow.Day, 0, 0, 0)).AddDays(-360);
List<ProductSummary> productSales = ReportDataSource.GetSalesByProduct(lastYear, DateTime.MaxValue, "TotalQuantity DESC");
List<int> sortedList = new List<int>();
List<Product> finalList = new List<Product>();
List<int> originalListInts = new List<int>();
foreach (Product p in originalList)
originalListInts.Add(p.ProductId);
foreach (ProductSummary ps in productSales)
{
if (originalListInts.Contains(ps.ProductId))
sortedList.Add(ps.ProductId);
}
foreach (int i in originalListInts)
{
if (!sortedList.Contains(i))
sortedList.Add(i);
}
int productcount = 0;
foreach (int p2 in sortedList)
{
if (productcount >= (_HiddenPageIndex * _PageSize) && productcount < ((_HiddenPageIndex + 1) * _PageSize))
finalList.Add(ProductDataSource.Load(p2));
productcount++;
}
ProductList.DataSource = finalList;
}
I know the code seems like it probably has a bit of overhead (ie, first saving out the productids to a list, regenerating a new list), but, it does work (without noticeable delay). If anyone had any ideas on how to refactor, I would appreciate it.
Thanks again for the help.
Scott
Re: Easy way to populate cat/product grid based on sales
Posted: Tue Jun 02, 2009 2:26 pm
by flattmatt
Hello
This is exactly a feature I need - but I would like to use it in Category Grid 4. I have done modifications on this Grid to display products a little differently, and would rather just plug in this sort of code that does the same thing as the ConLib Popular Products does, but just have a duplicate Category Grid 4 - this way I maintain look and feel, and in the future it will be easier to modify.
I am not good with ASP code, but manage to get most of what I want done...
Any help on this would be greatly appreciated.