public IList<Seed> getHomeSearchResult(string city, string catId, string keyword, string zip, string userName) { #region Logic IList<Seed> seedList = new List<Seed>(); Repository repoObj = new Repository(); SeedAction objSeed = new SeedAction(); string searchString = ""; if (catId != null && catId.Length > 0) { searchString = "select distinct seed.* from seed, Seed_has_Category where (status='New' or status='Growing')"; } else { searchString = "select distinct seed.* from seed where (status='New' or status='Growing')"; } bool flag = false; #region Search in location table if ((!string.IsNullOrEmpty(city) && city != "Set Your location") || (!string.IsNullOrEmpty(zip) && zip != "Zipcode")) { searchString += " and locationid in ( select distinct(id) from location where "; if (city.Length > 0 && city != "City") { if (!string.IsNullOrEmpty(zip)) searchString += "cityid in (select id from City where name = '" + city + "' )"; else searchString += "cityid in (select id from City where name = '" + city + "' ))"; flag = true; } if (!string.IsNullOrEmpty(zip)) { if (zip.Length > 0 && zip != "Zipcode") { CommonMethods cmnMethod = new CommonMethods(); string zipResult = cmnMethod.GetZipByRadiusNew("25", zip); if (flag) { searchString += " or "; } if (!string.IsNullOrEmpty(zipResult)) { searchString = searchString.Substring(0, searchString.Length - 1); searchString += " zipcode in (" + zipResult + "))"; } else { searchString += " zipcode in (" + zip + "))"; } flag = true; } } } #endregion #region Search according to categories if (catId != null && catId.Length > 0) { if (!catId.Equals("all")) searchString += " and Seed_has_Category.categoryId in (" + catId + ") and Seed.id=Seed_has_Category.seedId"; } #endregion #region Search in username, firstname, lastname if (!string.IsNullOrEmpty(userName)) { if (userName != "Search by user, category, keywords") { if (userName.Contains('@')) { searchString += " and Seed.ownerId in (Select id from Member where username = '******')"; } else { string fName = string.Empty; string lName = string.Empty; string[] splt = userName.Split(' '); if (splt.Count() > 1) { fName = splt[0].ToString(); lName = splt[1].ToString(); searchString += " and Seed.ownerId in (select id from Member where firstName = '" + fName + "' or lastName='" + lName + "')"; } else { fName = splt[0].ToString(); searchString += " and Seed.ownerId in (select id from Member where firstName = '" + fName + "')"; } } } } #endregion #region Search in title or description if (!string.IsNullOrEmpty(keyword)) { if (keyword.Length > 0 && keyword != "Search by user, category, keywords") { searchString += " or (Seed.title like '%" + keyword + "%' or Seed.description like '%" + keyword + "%')"; } } #endregion seedList = repoObj.ListPPP<Seed>("usp_SearchSeeds", searchString).ToList(); IList<Seed> returnSeedList = (from s in seedList select s).Distinct().ToList(); SessionStore.SetSessionValue(SessionStore.DefaultFeed, searchString); return returnSeedList; #endregion }
public ActionResult AdvanceSearch(string AdvLocation, string currentLocValue, string AnotherLocValue, string LocationAdvSearchRadius, string CoordinatesAdvSearchRadius, string advMedia, string advReplySeeds, string txtAdvSearchIncludeTerms, string txtAdvSearchExcludeTerms) { #region Front Logic Member memberData = (Member)SessionStore.GetSessionValue(SessionStore.Memberobject); SeedAction objSeed = new SeedAction(); string advSearchQuery = "Select Seed.* from Seed where (seed.[status] = 'New' or seed.[status] = 'Growing')"; if (advReplySeeds != "IncludeReplySeeds") advSearchQuery += " and seed.parentSeedID is null"; #region add location in query if (!string.IsNullOrEmpty(AdvLocation)) { if (AdvLocation != "AllLocations") { string radius = string.Empty; string radiusZipList = string.Empty; string ZipCodeAdvSearch = string.Empty; if (AdvLocation == "CurrentLocation") { radius = currentLocValue; CommonMethods objCmnMethods = new CommonMethods(); string strIpAddress = System.Web.HttpContext.Current.Request.UserHostAddress; if (strIpAddress == "127.0.0.1") strIpAddress = "61.246.241.162"; string ipLocation = objCmnMethods.IP2AddressAPI(strIpAddress); string[] currentAddress; if (!string.IsNullOrEmpty(ipLocation)) { //IPaddressAPI currentAddress = ipLocation.Split(','); if (string.IsNullOrEmpty(currentAddress[7].Replace("\"", "").ToString())) ZipCodeAdvSearch = "85027"; else ZipCodeAdvSearch = currentAddress[7].Replace("\"", "").ToString(); } else { //MaxMind ipLocation = objCmnMethods.IP2AddressMaxMind(); currentAddress = ipLocation.Split('\''); if (string.IsNullOrEmpty(currentAddress[15].ToString())) ZipCodeAdvSearch = "85027"; else ZipCodeAdvSearch = currentAddress[15].ToString(); } } if (AdvLocation == "NewLocation") { radius = AnotherLocValue; //Format address string[] splitAddressSearch = LocationAdvSearchRadius.Split(','); if (splitAddressSearch.Length > 4) { string[] splitZipRegion = splitAddressSearch[3].ToString().Trim().Split(' '); ZipCodeAdvSearch = splitZipRegion[1].ToString().Trim(); } else { string[] splitZipRegion = splitAddressSearch[2].ToString().Trim().Split(' '); ZipCodeAdvSearch = splitZipRegion[1].ToString().Trim(); } //End formatting address } //Get zip codes according to radius CommonMethods objCommon = new CommonMethods(); radiusZipList = objCommon.GetZipByRadiusNew(radius, ZipCodeAdvSearch); //End get zip codes according to radius if (string.IsNullOrEmpty(radiusZipList)) radiusZipList = ZipCodeAdvSearch; advSearchQuery += " and locationId in (Select id from Location where zipcode in (" + radiusZipList + "))"; } } #endregion #region include terms in query if (!string.IsNullOrEmpty(txtAdvSearchIncludeTerms)) { string inCondition = string.Empty; string[] includeTerms = txtAdvSearchIncludeTerms.Split(','); for (int i = 0; i < includeTerms.Length; i++) { if (i == 0) inCondition = "'" + includeTerms[i].ToString() + "'"; else inCondition += ",'" + includeTerms[i].ToString() + "'"; } advSearchQuery += " and Seed.id in (select sc.seedId from Category c ,Seed_has_Category sc where c.id=sc.categoryId and c.name in (" + inCondition + ") union select Seed.id from Seed,Member where Member.id = Seed.ownerId and Member.firstName in (" + inCondition + "))"; } #endregion #region exclude terms in query if (!string.IsNullOrEmpty(txtAdvSearchExcludeTerms)) { string exCondition = string.Empty; string[] excludeTerms = txtAdvSearchExcludeTerms.Split(','); for (int i = 0; i < excludeTerms.Length; i++) { if (i == 0) exCondition = "'" + excludeTerms[i].ToString() + "'"; else exCondition += ",'" + excludeTerms[i].ToString() + "'"; } advSearchQuery += " and Seed.id not in (select sc.seedId from Category c ,Seed_has_Category sc where c.id=sc.categoryId and c.name in (" + exCondition + ") union select Seed.id from Seed,Member where Member.id = Seed.ownerId and Member.firstName in (" + exCondition + "))"; } #endregion #region add media in query if (!string.IsNullOrEmpty(advMedia)) { switch (advMedia) { case "All": advSearchQuery += " and Seed.id in (Select m.seedId from Media m)"; break; case "NoMedia": advSearchQuery += " and Seed.id not in (Select m.seedId from Media m)"; break; case "PhotosOnly": advSearchQuery += " and Seed.id in (Select m.seedId from Media m where m.[type]='" + SystemStatements.MEDIA_IMAGE + "')"; break; case "VideosOnly": advSearchQuery += " and Seed.id in (Select m.seedId from Media m where m.[type]='" + SystemStatements.MEDIA_VIDEO + "')"; break; default: break; } } #endregion IList<Seed> lstSeed = null; lstSeed = objSeed.GetSeedListByCriteria(advSearchQuery); lstSeed = lstSeed.Distinct().ToList(); SessionStore.SetSessionValue(SessionStore.SearchSeeds, lstSeed); TempData["DiscoverSeed"] = "AdvanceSearch"; #endregion return RedirectToAction("SearchSeeds", "Home"); }