public Boolean Keywords(string searchtext, string fieldname, string mode) { //TODO: DEBUG_DAO create a function to build these filters. using brute force right now // need to add quoted phrases, exclude operators, whole or partial wor support // algorithm not always returning values, or sometimes rturns multiple when there should be one PortalCatalogDataContext dbPortalCatalog = new PortalCatalogDataContext(); PortalDataContext dbPortal = new PortalDataContext(); CategoryNodeDataContext dbCategoryNode = new CategoryNodeDataContext(); ProductCategoryDataContext dbProductCategory = new ProductCategoryDataContext(); ProductDataContext dbProduct = new ProductDataContext(); CategoryDataContext dbCategories = new CategoryDataContext(); PortalCatalog portal = new PortalCatalog(); Boolean fStatus = false; // Work through the database to find all products associated with a portal ID. // Znode has a few tables that simply act as relationship tables, hence the complexity of this query. var listPortalCatalog = portal.ReadDB(PortalID); var _productlist = listPortalCatalog.AsEnumerable() // staring with the portal ID, grab all catalogs for that portal // get mapping of catalogs to various categories .Join (dbCategoryNode.ZNodeCategoryNodes, joinedPortalCatalogs => joinedPortalCatalogs.catalogID, tempCategoryNode => tempCategoryNode.CatalogID, (joinedPortalCatalogs, tempCategoryNode) => new { joinedPortalCatalogs = joinedPortalCatalogs, tempCategoryNode = tempCategoryNode } ) // produces joinedCategoryNode // get mapping of all product IDs within categories .Join (dbProductCategory.ZNodeProductCategories, joinedCategoryNode => joinedCategoryNode.tempCategoryNode.CategoryID, tempProductCategories => tempProductCategories.CategoryID, (joinedCategoryNode, tempProductCategories) => new { joinedCategoryNode = joinedCategoryNode, tempProductCategories = tempProductCategories } ) // produces joinedProductCategories // get mapping of all products to categories and get product details .Join (dbProduct.ZNodeProducts, joinedProductCategories => joinedProductCategories.tempProductCategories.ProductID, tempProducts => tempProducts.ProductID, (joinedProductCategories, tempProducts) => new { joinedProductCategories = joinedProductCategories, tempProducts = tempProducts } ) // produces final product list (tempProducts) .Distinct() .Select (row => new Product { productID = row.tempProducts.ProductID, parentCategoryID = row.joinedProductCategories.tempProductCategories.CategoryID, categoryID = row.joinedProductCategories.joinedCategoryNode.tempCategoryNode.CategoryID, shortDescription = row.tempProducts.ShortDescription, description = row.tempProducts.Description, productNumber = row.tempProducts.ProductNum, imageFile = row.tempProducts.ImageFile, displayOrder = (!row.tempProducts.DisplayOrder.HasValue) ? 0 : (int)row.tempProducts.DisplayOrder, imageAltTag = row.tempProducts.ImageAltTag, title = row.tempProducts.Name, salePrice = row.tempProducts.SalePrice, retailPrice = row.tempProducts.RetailPrice, isOnSale = (row.tempProducts.SalePrice.HasValue) ? true : false, portalID = (!row.tempProducts.PortalID.HasValue) ? 0 : (int)row.tempProducts.PortalID } ); // set operational modes //fieldName = all | title | description mode = all | exact | any if ((fieldname != null) && (fieldname.Length > 0)) { fieldname = fieldname.ToLower(); } else { fieldname = null; } if ((mode != null) && (mode.Length > 0)) { mode = mode.ToLower(); } else { mode = null; } // get any search terms String[] searchTerms = new String[] { }; string[] separators = { ",", ".", "!", "?", ";", ":", " " }; if (mode != null) { mode = mode.ToLower(); } if (mode == "all" || mode == "any" || mode==null) { searchTerms = searchtext.Split(separators, StringSplitOptions.RemoveEmptyEntries); } else { searchTerms = null; // use searctext for an exact phrase matching } if (fieldname == "all" || fieldname == null) { if (mode == "exact") { _productlist = _productlist .Where ( row => row.title.ToLower().Contains(searchtext) || row.description.ToLower().Contains(searchtext) || row.keywords.ToLower().Contains(searchtext) || row.shortDescription.ToLower().Contains(searchtext) || row.SEOKeywords.ToLower().Contains(searchtext) || row.SEOTitle.ToLower().Contains(searchtext) || row.SEODescription.ToLower().Contains(searchtext) ); } else if ((mode == "any") || (mode == null)) { var p1 = _productlist .Where ( row => searchTerms.Any ( term => row.title.ToLower().Contains(term) ) ); var p2 = _productlist .Where ( row => searchTerms.Any ( term => row.description.ToLower().Contains(term) ) ); var p3 = _productlist .Where ( row => searchTerms.Any ( term => row.keywords.ToLower().Contains(term) ) ); _productlist = p1.Union(p2).ToList(); } else if (mode == "all") { var p1 = _productlist .Where ( row => searchTerms.All ( term => row.title.ToLower().Contains(term) ) ); var p2 = _productlist .Where ( row => searchTerms.All ( term => row.description.ToLower().Contains(term) ) ); var p3 = _productlist .Where ( row => searchTerms.All ( term => row.keywords.ToLower().Contains(term) ) ); _productlist = p1.Union(p2).ToList(); //_productlist.Union(p3); } } else if (fieldname == "title") { if (mode == "exact") { _productlist = _productlist .Where ( row => row.title.ToLower().Contains(searchtext) ); } else if ((mode == "any") || (mode == null)) { _productlist = _productlist .Where ( row => searchTerms.Any ( term => row.title.ToLower().Contains(term) ) ); } else if (mode == "all") { _productlist = _productlist .Where ( row => searchTerms.All ( term => row.title.ToLower().Contains(term) ) ); } } else if (fieldname == "description") { if (mode == "exact") { _productlist = _productlist .Where ( row => row.description.ToLower().Contains(searchtext) ); } else if ((mode == "any") || (mode == null)) { _productlist = _productlist .Where ( row => searchTerms.Any ( term => row.description.ToLower().Contains(term) ) ); } else if (mode == "all") { _productlist = _productlist .Where ( row => searchTerms.All ( term => row.description.ToLower().Contains(term) ) ); } } else if (fieldname == "keywords") { if (mode == "exact") { _productlist = _productlist .Where ( row => row.keywords.ToLower().Contains(searchtext) ); } else if ((mode == "any") || (mode == null)) { _productlist = _productlist .Where ( row => searchTerms.Any ( term => row.keywords.ToLower().Contains(term) ) ); } else if (mode == "all") { _productlist = _productlist .Where ( row => searchTerms.All ( term => row.keywords.ToLower().Contains(term) ) ); } } _productlist = _productlist .OrderBy(row => row.productCategoryID); if (_productlist != null) { try { fStatus = true; Count = _productlist.Count(); ProductList = _productlist; } catch (Exception e) { Count = 0; ProductList = null; } } return fStatus; }
public IEnumerable<CategoryNode> ReadDB(int? catalogID, int? parentID) { IEnumerable<CategoryNode> iCatNode; CategoryNodeDataContext db = new CategoryNodeDataContext(); CategoryDataContext dbCategory = new CategoryDataContext(); iCatNode = db.ZNodeCategoryNodes.AsEnumerable() .Join (dbCategory.ZNodeCategories, _CatNode => _CatNode.CategoryID, _Category => _Category.CategoryID, (_CatNode, _Category) => new { _Category.Name, _Category.PortalID, _Category.ShortDescription, _Category.Title, _Category.ImageFile, _Category.Description, _CatNode.ActiveInd, _CatNode.CatalogID, _CatNode.CategoryID, _CatNode.CategoryNodeID, _CatNode.DisplayOrder, _CatNode.BeginDate, _CatNode.EndDate, _CatNode.MasterPage, _CatNode.Theme, _CatNode.CSS, _CatNode.ParentCategoryNodeID } ) .Select (row => new CategoryNode { catalogID = (!row.CatalogID.HasValue) ? 0 : (int)row.CatalogID, isActive = row.ActiveInd, theme = row.Theme, masterPage = row.MasterPage, dateStart = row.BeginDate, dateEnd = row.EndDate, CSS = row.CSS, title = row.Title, categoryNodeID = row.CategoryNodeID, categoryID = row.CategoryID, parentCategoryNodeID = row.ParentCategoryNodeID, displayOrder = (!row.DisplayOrder.HasValue) ? 0 : (int)row.DisplayOrder } ); if (catalogID.HasValue) { iCatNode = iCatNode .OrderBy ( x => x.displayOrder) .ThenBy ( x => x.title) .Where ( row => row.catalogID == catalogID ); } if (parentID.HasValue) { iCatNode = iCatNode .OrderBy(x => x.displayOrder) .ThenBy(x => x.title) .Where(row => row.parentCategoryNodeID == parentID); } return iCatNode; }
public IEnumerable<Category> ReadDB(int? cID) { IEnumerable<Category> iCategory; CategoryDataContext db = new CategoryDataContext(); iCategory = db.ZNodeCategories.AsEnumerable() .Select(row => new Category { categoryID = row.CategoryID, shortDescription = row.ShortDescription, description = row.Description, title = row.Title, imageFile = row.ImageFile, displayOrder = (!row.DisplayOrder.HasValue) ? 0 : (int)row.DisplayOrder, imageAltTag = row.ImageAltTag, name = row.Name, portalID = (!row.PortalID.HasValue) ? 0 : (int)row.PortalID }); if (cID.HasValue) { iCategory = iCategory.Where(row => row.categoryID == cID); } return iCategory; }