Question for a developer

[heinscott]

I have found, what I believe to be a bug. Maybe you can let me know if perhaps I've done something with Able that was not intended. If so, that would explain my situation.
For starters, I am moving an online store of about 2,000 skus (1,000 products setup in kits, options) that reside in appoximately 200-300 categories. Because I needed to keep my structure in tact (many of the products are in several categories), I chose to import products and categories into the AbleCommerce database using the original ProductIds and CategoryIds. It took a bit of work to get the associations working, but, in the end I did get everything to appear in the right categories, with the right parents, etc, etc...
Now, the problem I am running into is this:
I am using the CategorySideSearch feature of Able to narrow results. It seems to work great for the most part. However, I noticed that some of my categories, in the expand by manufacturer section, contained manufacturers that had NO products in that category. I thought maybe I had somehow screwed up the product/category structure. However, after much research, I realized that the ProductDataSource.ManufacturerProductCount was returning a product count for Manufacturers that did not exist in the category in question. After even further head-scratching, and poking around, I realized this... The ProductDataSource.ManufacturerProductCount method is not checking what the NodeType is when it is doing it's search (apparently)... Because I have CategoryIds that are the same as ProductIds (in a few instances), this lack of testing is causing the error.
I don't know if when you add products and categories through the Able interface, or, if you use the dataport to import them that Able won't allow for a ProductID that is the same as a CategoryId, and vice-versa. If so, I guess that I've brought this on myself.
Please let me know how I should procede. Of course I can either re-create the method to get my counts, or, simply change the ids on the product/categories in question.
Thank you for any help you can give me!

Scott
(Here is an example of the problem... http://76.12.100.221/Hot-Tub-Filters-C350.aspx Click on 'BioGuard' under the Narrow by Manufacturer )

[sohaib]

If you are assuming that under the Narrow by Manufacturer section you are supposed to see only the manufacturers that have products for that category then that is not the case. This section is displaying all the manufacturers. It does not distinguish between manufacturers that have products for current category and manufacturers that do not have. This however should be easy to achieve. You can update Page_PreRender method in CategorySearchSideBar.ascx.cs in ConLib to remove manufacturers that do not have any products for the current category.

Try this

Code: Select all

_Manufacturers = ProductDataSource.NarrowSearchCountByManufacturer(this.Keyword, this.CategoryId, 0, 0, string.Empty);
//Code Added : Begin
for(int i= _Manufacturers.Count-1; i>=0; i--)
{
     ProductDataSource.ManufacturerProductCount mpc = _Manufacturers[i];
     if (mpc.ProductCount < 1) _Manufacturers.RemoveAt(i);            
}
//Code Added : End

[heinscott]

Okay... I guess I'm confused then...
I ran this code, as a test...

List<ProductDataSource.ManufacturerProductCount> _ManCount;
_ManCount = ProductDataSource.NarrowSearchCountByManufacturer("",338,0,0,"");
foreach (ProductDataSource.ManufacturerProductCount m in _ManCount)
{
Response.Write(m.Name + " " + m.ProductCount+" "+m.ManufacturerId+"<BR>");
}

...and I only came up with 13 manufacturers... In fact, here is my output...

Polaris 192 61
Dolphin 11 32
Pentair 8 48
Aqua Products 7 24
Zodiac 6 15
Hayward 5 25
Aqua Vac 3 20
BioGuard 3 11
Sta-rite 3 28
Dirt Devil 2 41
Spa Essentials 1 26
Water Tech 1 49
Leisure Concepts 1 53

I have many more Manufacturers in my database than this. As you can see, the lowest count is '1'. I Checked on BioGuard as a test. There just happen to be 3 BioGuard Products that have the same id's as 3 categories that are children of Cat 338. If that method returns all Manufacturers, wouldn't I recieve all the ones that have 0 products with this code, then??

Thanks for the help.

Scott

[jmestep]

Scott, you're not alone. I never have been able to figure out what that control is doing. I put the normal category control above it because I was afraid customers wouldn't be able to figure it out either. It's probably very powerful, but it's over my head.

[heinscott]

...If this helps at all, I just posted the code change, but, I am seeing the exact same results. It's still showing the Manufacturers in that list, because it thinks there are products there when there are not...

[sohaib]

hmmm ... you are right.
You were right the first time as well but you did not mention your test

I have reviewed the code and found something like this in query

Code: Select all

SELECT M.ManufacturerId, M.Name, COUNT(DISTINCT P.ProductId) As ProductCount FROM 
 ((ac_Products P INNER JOIN ac_CatalogNodes PC ON P.ProductId = PC.CatalogNodeId) LEFT JOIN ac_Manufacturers M ON P.ManufacturerId = M.ManufacturerId)
WHERE P.StoreId = @storeId
AND (P.Name LIKE @keyword OR P.SearchKeywords LIKE @keyword)
AND PC.CategoryId IN (SELECT CategoryId FROM ac_CategoryParents WHERE ParentId = @categoryId)
....
....

There should have been an additional check

Code: Select all

AND PC.CatalogNodeTypeId=1

[sohaib]

This is a bug that you can report. Not a very serious bug ... so there will probably be no immediate patch available. But in any case do take the honors of reporting this bug .

The workaround for now is to rely on writing you own NarrowSearchCountByManufacturer method.

You can modify this one

Code: Select all

        public static List<ManufacturerProductCount> NarrowSearchCountByManufacturer(string keyword, int categoryId, LSDecimal lowPrice, LSDecimal highPrice, string sortExpression)
        {
            StringBuilder selectQuery = new StringBuilder();
            selectQuery.Append("SELECT M.ManufacturerId, M.Name, COUNT(DISTINCT P.ProductId) As ProductCount FROM ");
            selectQuery.Append(GetNarrowSearchTables(categoryId, true));
            selectQuery.Append(" WHERE P.StoreId = @storeId");
            if (!string.IsNullOrEmpty(keyword))
            {
                keyword = StringHelper.FixSearchPattern(keyword);
                selectQuery.Append(" AND (P.Name LIKE @keyword OR P.SearchKeywords LIKE @keyword)");
            }
            if (categoryId != 0) selectQuery.Append(" AND PC.CategoryId IN (SELECT CategoryId FROM ac_CategoryParents WHERE ParentId = @categoryId)");
            if (lowPrice > 0) selectQuery.Append(" AND P.Price >= @lowPrice");
            if (highPrice > 0) selectQuery.Append(" AND P.Price <= @highPrice");
            selectQuery.Append(" AND P.VisibilityId = 0");
            selectQuery.Append(" GROUP BY M.ManufacturerId, M.Name");
            if (!string.IsNullOrEmpty(sortExpression))
            {
                if (sortExpression.StartsWith("Name")) sortExpression = "M." + sortExpression;
                selectQuery.Append(" ORDER BY " + sortExpression);
            }
            else
            {
                selectQuery.Append(" ORDER BY ProductCount DESC");
            }
            Database database = Token.Instance.Database;
            DbCommand selectCommand = database.GetSqlStringCommand(selectQuery.ToString());
            database.AddInParameter(selectCommand, "storeId", DbType.Int32, Token.Instance.StoreId);
            if (!string.IsNullOrEmpty(keyword)) database.AddInParameter(selectCommand, "keyword", DbType.String, keyword);
            if (categoryId != 0) database.AddInParameter(selectCommand, "categoryId", DbType.Int32, categoryId);
            if (lowPrice > 0) database.AddInParameter(selectCommand, "lowPrice", DbType.Decimal, lowPrice);
            if (highPrice > 0) database.AddInParameter(selectCommand, "highPrice", DbType.Decimal, highPrice);
            List<ManufacturerProductCount> results = new List<ManufacturerProductCount>();
            using (IDataReader dr = database.ExecuteReader(selectCommand))
            {
                while (dr.Read())
                {
                    int manufacturerId = NullableData.GetInt32(dr, 0);
                    if (manufacturerId != 0)
                    {
                        ManufacturerProductCount mpc = new ManufacturerProductCount(manufacturerId, dr.GetString(1), dr.GetInt32(2));
                        results.Add(mpc);
                    }
                }
                dr.Close();
            }
            return results;
        }


        private static string GetNarrowSearchTables(int categoryId, bool includeManufacturer)
        {
            bool filterCategory = (categoryId != 0);
            if (filterCategory)
            {
                if (!includeManufacturer)
                {
                    return "(ac_Products P INNER JOIN ac_CatalogNodes PC ON P.ProductId = PC.CatalogNodeId)";
                }
                else
                {
                    return "((ac_Products P INNER JOIN ac_CatalogNodes PC ON P.ProductId = PC.CatalogNodeId) LEFT JOIN ac_Manufacturers M ON P.ManufacturerId = M.ManufacturerId)";
                }
            }
            else
            {
                if (!includeManufacturer)
                {
                    return "ac_Products P";
                }
                else
                {
                    return "(ac_Products P LEFT JOIN ac_Manufacturers M ON P.ManufacturerId = M.ManufacturerId)";
                }
            }
        }

[heinscott]

Thanks Sohaib. I appreciate your help.
Thanks for the code, too. I was just going to ask for that, but you beat me to it!
Scott

[heinscott]

One last question (I hope)...
I've made a customTools class, and added the NarrowSearchCountByManufacturer method. The problem I am having though, is that I can't seem to find the namespace for certain objects. Could you possibly send me the using statement I would need for this? The only one (I think) I can't seem to find is for the Database object.
Thanks a lot.

Scott

[nickc]

Think that might be here: Microsoft.Practices.EnterpriseLibrary.Data.Database

[heinscott]

That's the one.
Thank you much!

Scott