/// <summary> /// Gets only the ids of the properties we've searched for /// </summary> public static IEnumerable <int> Search(REIQ.Enum.SearchType searchType, IEnumerable <string> propertyTypes, bool excludeUnderOffer, bool includeSurroundingSuburbs, bool onlyIfOpenHome, string keyword, int minPrice, int maxPrice, int numBedrooms, int numBathrooms, REIQ.Enum.SortOptions sortOption, int acId = 0) { // computed values from keyword Entities.Suburb suburb = null; // suburbing matching search keyword List <Entities.Suburb> suburbsList = new List <Entities.Suburb>(); // list of suburbs when search string contains several suburbs int?postcode = null; // exact postcode Entities.Property property = null; // exact property id entered into search string region = null; // keyword corresponds to a region List <string> regionsList = new List <string>(); //list of regions when query string contains several regions // several regions are selected if (keyword.Contains("::")) { foreach (var rg in keyword.Split(new string[] { "::" }, StringSplitOptions.None)) { regionsList.Add(rg); } } // several suburbs are selected else if (keyword.Contains("!!")) { foreach (var sb in keyword.Split(new string[] { "!!" }, StringSplitOptions.None)) { var match = Regex.Match(sb, "(.*), (4\\d{3})"); // in format "Suburb Name, 4XXX" if (match.Success) { var su = match.Groups[1].Value; var pc = Convert.ToInt32(match.Groups[2].Value); suburb = REIQ.Access.Suburb.Get(su, pc); //save search query into DB for stats if (suburb != null && !String.IsNullOrEmpty(suburb.name)) { Suburb.LogSuburbSearch(suburb); //remember found suburb suburbsList.Add(suburb); } } } } else { var match = Regex.Match(keyword, "(.*), (4\\d{3})"); // in format "Suburb Name, 4XXX" if (match.Success) { var su = match.Groups[1].Value; var pc = Convert.ToInt32(match.Groups[2].Value); suburb = REIQ.Access.Suburb.Get(su, pc); //save search query into DB for stats if (suburb != null && !String.IsNullOrEmpty(suburb.name)) { Suburb.LogSuburbSearch(suburb); } } else if (Regex.IsMatch(keyword, "^4[0-9]{3}$")) // postcode only { postcode = Convert.ToInt32(keyword); } else if (Regex.IsMatch(keyword, "^\\d+$")) // is purely numeric, so check if this is a property id { Int32 potentialPropertyID = 0; if (Int32.TryParse(keyword, out potentialPropertyID))//To avoid throwing of errors if the number is very big like "12234456677" { //get any property using (var conn = Data.CreateConnection()) { property = conn.Query <Entities.Property>("SELECT * FROM tblProperty WITH (NOLOCK) WHERE pId = @pID", new { pID = potentialPropertyID }).FirstOrDefault(); } if (property == null) { keyword = String.Empty; } //checking if property is hidden for some reason and make redirection in that case if (property != null) { REIQ.Helpers.PropertyHelper.CheckIfHidden(property); } } } else // check to see if this is the name of a suburb or region { var matchingItems = REIQ.Access.Suburb.ListMatching(keyword); if (matchingItems != null && matchingItems.Regions != null && matchingItems.Regions.Count() > 0) { region = matchingItems.Regions[0]; } else if (matchingItems != null && matchingItems.Suburbs != null && matchingItems.Suburbs.Count() > 0) { if (matchingItems.Suburbs.Count() == 1) { suburb = matchingItems.Suburbs[0]; Suburb.LogSuburbSearch(suburb); } else { foreach (var sb in matchingItems.Suburbs) { suburbsList.Add(sb); Suburb.LogSuburbSearch(sb); } } } } } if (property != null) // exact property match { return(Enumerable.Repeat <int>(property.pID, 1)); } if (regionsList.Count == 0 && suburbsList.Count == 0 && suburb == null && !postcode.HasValue && String.IsNullOrEmpty(region) && property == null && !String.IsNullOrEmpty(keyword)) // found nothing! { //if (HttpContext.Current != null) // HttpContext.Current.Response.Redirect("~/?nomatches=1"); //return Enumerable.Empty<int>(); //Street Search IEnumerable <int> streetSearchList = TryStreetSearch(searchType, propertyTypes, excludeUnderOffer, includeSurroundingSuburbs, onlyIfOpenHome, keyword.Replace(",", " "), minPrice, maxPrice, numBedrooms, numBathrooms, sortOption); if (streetSearchList.Count() > 0) { return(streetSearchList); } } var query = new System.Text.StringBuilder(@"SELECT TOP 5000 p.pId FROM tblProperty p LEFT JOIN tblSuburb s ON s.name = p.Suburb AND s.postcode = p.postcode WHERE p.isWebDisplay = 1 AND p.goLive = 0 AND p.hideAHC = 0 AND p.status IN @Status "); query.AppendLine(); // agencyId if (acId > 0) { query.AppendLine("AND p.acId = @AgencyId"); } //get exact suburbs to search List <int> suburbIds = new List <int>(); if (regionsList != null && regionsList.Count > 0) { foreach (var rg in regionsList) { suburbIds = suburbIds.Concat(Access.Suburb.ListIdsOfSuburbsByRegion(rg)).ToList(); //ugly hack for SQL server not accepting more than 200 parameters if (suburbIds.Count > 200) { break; } } } else if (!String.IsNullOrEmpty(region)) { suburbIds = Access.Suburb.ListIdsOfSuburbsByRegion(region); } else if (suburbsList != null && suburbsList.Count > 0) { foreach (var sb in suburbsList) { if (includeSurroundingSuburbs) { if (suburbIds.Count == 0) { suburbIds = Access.Suburb.ListIdsOfSurroundingSuburbs(sb.name, sb.postcode.Value).ToList(); } else { List <int> tempSbs = Access.Suburb.ListIdsOfSurroundingSuburbs(sb.name, sb.postcode.Value).ToList(); suburbIds = suburbIds.Concat(tempSbs).ToList(); } } else { suburbIds.Add(sb.sID); } } } else if (suburb != null) { if (includeSurroundingSuburbs) { suburbIds = Access.Suburb.ListIdsOfSurroundingSuburbs(suburb.name, suburb.postcode.Value).ToList(); } else { suburbIds = new List <int> { suburb.sID }; } } else if (postcode.HasValue) { suburbIds = Access.Suburb.ListIdsOfSuburbsByPostCode(postcode.Value); } // set property type filter switch (searchType) { case Enum.SearchType.bu: query.AppendLine("AND p.type = 'business'"); break; case Enum.SearchType.co: case Enum.SearchType.cr: query.AppendLine("AND p.type = 'commercial'"); break; case Enum.SearchType.ru: query.AppendLine("AND p.type = 'Acreage/Rural'"); break; case Enum.SearchType.ra: case Enum.SearchType.sa: default: query.AppendLine("AND p.type NOT IN ('commercial', 'business')"); if (propertyTypes.Count() > 0) { query.AppendLine("AND p.type IN @PropertyTypes"); } break; } // set status filter var status = new List <string>(); switch (searchType) { case Enum.SearchType.bu: status.Add("For Sale"); status.Add("For Rent"); break; case Enum.SearchType.cr: case Enum.SearchType.ra: status.Add("For Rent"); break; case Enum.SearchType.sa: case Enum.SearchType.ru: case Enum.SearchType.co: default: status.Add("For Sale"); break; } if (!excludeUnderOffer && !status.Contains("For Rent")) { status.Add("Under offer"); } if (onlyIfOpenHome) { query.AppendLine("AND COALESCE(p.homeopen1From, p.homeopen2From) IS NOT NULL"); } if (numBedrooms > 0) { query.AppendLine("AND COALESCE(p.numBedrooms, 0) >= @NumBedrooms"); } if (numBathrooms > 0) { query.AppendLine("AND COALESCE(p.numBathrooms, 0) >= @NumBathrooms"); } // price filter if (minPrice > 0) { query.AppendLine("AND COALESCE(p.commercialPriceYearly, ISNULL(p.priceLow, 0)) >= @MinPrice"); } if (maxPrice > 0) { query.AppendLine("AND COALESCE(p.commercialPriceYearly, p.priceHigh, ISNULL(p.priceLow, 0)) <= @MaxPrice"); } if (suburbIds.Count() > 0) { query.AppendLine("AND s.SID IN @SuburbIds"); } //sort on the most appropriate price field for the property type switch (sortOption) { default: case Enum.SortOptions.Most_recent: query.AppendLine("ORDER BY p.datelisted DESC"); break; case Enum.SortOptions.By_suburb: query.AppendLine("ORDER BY p.suburb, COALESCE(p.commercialPriceYearly, p.priceLow) DESC, p.datelisted DESC"); break; case Enum.SortOptions.Highest_price: query.AppendLine("ORDER BY COALESCE(p.commercialPriceYearly, p.priceLow) DESC, p.datelisted DESC"); break; case Enum.SortOptions.Lowest_price: query.AppendLine("ORDER BY COALESCE(p.commercialPriceYearly, p.priceLow) ASC, p.datelisted DESC"); break; case Enum.SortOptions.Number_bedrooms: query.AppendLine("ORDER BY p.numBedrooms ASC, p.datelisted DESC"); break; } REIQ.Access.Data.TraceWrite("Search query", query.ToString()); //foreach (int id in suburbIds) //{ // REIQ.Access.Data.TraceWrite("SuburbIDS", id.ToString()); //} //REIQ.Access.Data.TraceWrite("Region", region); REIQ.Access.Data.TraceWrite("Property types", string.Join(", ", propertyTypes.ToArray())); REIQ.Access.Data.TraceWrite("Status", string.Join(", ", status.ToArray())); using (var conn = Data.CreateConnection()) { return(from p in conn.Query <Entities.Property>(query.ToString(), new { AgencyId = acId, MinPrice = minPrice, MaxPrice = maxPrice, NumBedrooms = numBedrooms, NumBathrooms = numBathrooms, PropertyTypes = propertyTypes, Status = status, SuburbIds = suburbIds }) select p.pID); } }
public static IEnumerable <int> TryStreetSearch(REIQ.Enum.SearchType searchType, IEnumerable <string> propertyTypes, bool excludeUnderOffer, bool includeSurroundingSuburbs, bool onlyIfOpenHome, string keyword, int minPrice, int maxPrice, int numBedrooms, int numBathrooms, REIQ.Enum.SortOptions sortOption) { //Try search by address String rdNum = String.Empty; String rdType = String.Empty; String streetName = String.Empty; String subName = String.Empty; String keyWordsForSuburb = String.Empty; String keyWordsForStreet = String.Empty; String[] parts = keyword.Split(' '); //String rdTypeConstant = "(street|st|road|rd|close|cl|avenue|ave|av|path|ph|drive|drv|loop|court|ct|circle|lane|ln|way)"; IEnumerable <int> list = Enumerable.Empty <int>(); String region = String.Empty; List <string> allSuburbs = null; List <Suburb.StreetNames> allStreets = null; //get all suburbs from DB allSuburbs = Suburb.GetAllSuburbs(); //Try determine suburb String foundSuburb = String.Empty; foreach (String suburb in allSuburbs) { if (String.IsNullOrEmpty(suburb)) { continue; } if (keyword.ToLower().Contains(suburb.ToLower())) { foundSuburb = suburb; //Log this suburb search REIQ.Entities.Suburb s = Suburb.GetFromName(suburb); if (s != null) { Suburb.LogSuburbSearch(s); } break; } } //get all possible combinations of Road Name and Road Type if (!String.IsNullOrEmpty(foundSuburb)) { allStreets = Suburb.GetAllStreetsBySuburb(foundSuburb); } else { allStreets = Suburb.GetAllStreets(); } //Trying to determine roadNumber and roadType foreach (String part in parts) { var matchNumber = Regex.Match(part, @"\d"); if (matchNumber.Captures.Count > 0) { rdNum = part; } //if (rdTypeConstant.Contains(part.Trim().ToLower())) // rdType = part; } String foundStreetName = String.Empty; String foundStreetType = String.Empty; //List<Suburb.StreetNames> res = allStreets.Where(s => keyword.ToLower().Contains((String.IsNullOrEmpty(s.StreetName) ? "" : s.StreetName.ToLower()) + " " + (String.IsNullOrEmpty(s.StreetType) ? "" : s.StreetType.ToLower()))).ToList(); foreach (Suburb.StreetNames street in allStreets) { if (String.IsNullOrEmpty(street.StreetName)) { continue; } if (Regex.IsMatch(street.StreetName, "^\\d+$")) { continue; } if (keyword.ToLower().Contains((String.IsNullOrEmpty(street.StreetName) ? "" : street.StreetName.ToLower()) + " " + (String.IsNullOrEmpty(street.StreetType) ? "" : street.StreetType.ToLower()))) { foundStreetName = street.StreetName; foundStreetType = street.StreetType; break; } } if (String.IsNullOrEmpty(foundStreetName)) { foreach (Suburb.StreetNames street in allStreets) { if (String.IsNullOrEmpty(street.StreetName)) { continue; } if (Regex.IsMatch(street.StreetName, "^\\d+$")) { continue; } if (keyword.ToLower().Contains(street.StreetName.ToLower())) { foundStreetName = street.StreetName; break; } } } #region setUpOptions //Set up filter StringBuilder queryFilterString = new System.Text.StringBuilder(); // set property type filter switch (searchType) { case Enum.SearchType.bu: queryFilterString.AppendLine("AND p.type = 'business'"); break; case Enum.SearchType.co: case Enum.SearchType.cr: queryFilterString.AppendLine("AND p.type = 'commercial'"); break; case Enum.SearchType.ru: queryFilterString.AppendLine("AND p.type = 'Acreage/Rural'"); break; case Enum.SearchType.ra: case Enum.SearchType.sa: default: queryFilterString.AppendLine("AND p.type NOT IN ('commercial', 'business')"); if (propertyTypes.Count() > 0) { queryFilterString.AppendLine("AND p.type IN @PropertyTypes"); } break; } // set status filter var status = new List <string>(); switch (searchType) { case Enum.SearchType.bu: status.Add("For Sale"); status.Add("For Rent"); break; case Enum.SearchType.cr: case Enum.SearchType.ra: status.Add("For Rent"); break; case Enum.SearchType.sa: case Enum.SearchType.ru: case Enum.SearchType.co: default: status.Add("For Sale"); break; } if (!excludeUnderOffer && !status.Contains("For Rent")) { status.Add("Under offer"); } if (onlyIfOpenHome) { queryFilterString.AppendLine("AND COALESCE(p.homeopen1From, p.homeopen2From) IS NOT NULL"); } if (numBedrooms > 0) { queryFilterString.AppendLine("AND COALESCE(p.numBedrooms, 0) >= @NumBedrooms"); } if (numBathrooms > 0) { queryFilterString.AppendLine("AND COALESCE(p.numBathrooms, 0) >= @NumBathrooms"); } // price filter if (minPrice > 0) { queryFilterString.AppendLine("AND COALESCE(p.commercialPriceYearly, p.priceLow) >= @MinPrice"); } if (maxPrice > 0) { queryFilterString.AppendLine("AND COALESCE(p.commercialPriceYearly, p.priceHigh, p.priceLow) <= @MaxPrice"); } // sort on the most appropriate price field for the property type switch (sortOption) { default: case Enum.SortOptions.Most_recent: queryFilterString.AppendLine("ORDER BY p.datelisted DESC"); break; case Enum.SortOptions.By_suburb: queryFilterString.AppendLine("ORDER BY p.suburb, COALESCE(p.commercialPriceYearly, p.priceLow) DESC, p.datelisted DESC"); break; case Enum.SortOptions.Highest_price: queryFilterString.AppendLine("ORDER BY COALESCE(p.commercialPriceYearly, p.priceLow) DESC, p.datelisted DESC"); break; case Enum.SortOptions.Lowest_price: queryFilterString.AppendLine("ORDER BY COALESCE(p.commercialPriceYearly, p.priceLow) ASC, p.datelisted DESC"); break; case Enum.SortOptions.Number_bedrooms: queryFilterString.AppendLine("ORDER BY p.numBedrooms ASC, p.datelisted DESC"); break; } #endregion setUpOptions #region DBSearch if (!String.IsNullOrEmpty(foundStreetName) && !String.IsNullOrEmpty(foundSuburb)) { //Trying to get poperty via it certain address if (!String.IsNullOrEmpty(foundStreetName) && !String.IsNullOrEmpty(foundSuburb)) { if (!String.IsNullOrEmpty(foundStreetType) && !String.IsNullOrEmpty(rdNum)) { list = DoStreetSearch_By_Suburb_rdNum_strName_strType(queryFilterString.ToString(), foundSuburb, rdNum, foundStreetName, foundStreetType, status, minPrice, maxPrice, numBedrooms, numBathrooms, propertyTypes); } if (!(list.Count() > 0)) { if (!String.IsNullOrEmpty(foundStreetType)) { list = DoStreetSearch_By_Suburb_strName_strType(queryFilterString.ToString(), foundSuburb, foundStreetName, foundStreetType, status, minPrice, maxPrice, numBedrooms, numBathrooms, propertyTypes); } } if (!(list.Count() > 0)) { list = DoStreetSearch_By_Suburb_strName(queryFilterString.ToString(), foundSuburb, foundStreetName, status, minPrice, maxPrice, numBedrooms, numBathrooms, propertyTypes); } if (!(list.Count() > 0)) { list = DoStreetSearch_By_Suburb(queryFilterString.ToString(), foundSuburb, status, minPrice, maxPrice, numBedrooms, numBathrooms, propertyTypes); } } } //Get Property via Suburb if Street name was not found if (String.IsNullOrEmpty(foundStreetName)) { list = DoStreetSearch_By_Suburb(queryFilterString.ToString(), foundSuburb, status, minPrice, maxPrice, numBedrooms, numBathrooms, propertyTypes); } //Get Property via Street name if suburb was not found if (String.IsNullOrEmpty(foundSuburb)) { //get property via Street name and Street num and Street type if (!String.IsNullOrEmpty(foundStreetType) && !String.IsNullOrEmpty(rdNum)) { list = DoStreetSearch_By_rdNum_strName_strType(queryFilterString.ToString(), rdNum, foundStreetName, foundStreetType, status, minPrice, maxPrice, numBedrooms, numBathrooms, propertyTypes); } //get property via Street name and Street type if (!String.IsNullOrEmpty(foundStreetType)) { list = DoStreetSearch_By_strName_strType(queryFilterString.ToString(), foundStreetName, foundStreetType, status, minPrice, maxPrice, numBedrooms, numBathrooms, propertyTypes); } //get property via Street name if nothing found before if (!(list.Count() > 0)) { list = DoStreetSearch_By_strName(queryFilterString.ToString(), foundStreetName, status, minPrice, maxPrice, numBedrooms, numBathrooms, propertyTypes); } } return(list); #endregion }