AbleCommerce Shopping Cart Software

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

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)
....
....

Code: Select all

AND PC.CatalogNodeTypeId=1

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)";
                }
            }
        }