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