public ActionResult AddStreamFeed(string gFeedTitle, string gFeedDesc, string gIsPublic, string seedLocationStreamFeed, string seedCoordinatesStreamFeed, string txtCategoryStreamFeed, string feedLocation, string profileLocValue, string NewLocValue, string LocationRadius, string CoordinatesRadius, string gIsMedia, string txtIncludeTerms, string txtExcludeTerms, string isEdit, string StreamId) { #region Front Logic try { Member memberData = (Member)SessionStore.GetSessionValue(SessionStore.Memberobject); StreamAction objStream = new StreamAction(); ssStream stream = new ssStream(); stream.title = gFeedTitle; stream.description = gFeedDesc; string feedQuery = "Select Seed.* from Seed where (seed.[status] = 'New' or seed.[status] = 'Growing')"; #region add location in query if (!string.IsNullOrEmpty(feedLocation)) { if (feedLocation != "AllLocations") { string radius = string.Empty; string radiusZipList = string.Empty; string ZipCodeFeed = string.Empty; if (feedLocation == "ProfileLocation") { radius = profileLocValue; if (memberData.MemberProfiles != null) { if (memberData.MemberProfiles.FirstOrDefault().Location.zipcode != null) ZipCodeFeed = Convert.ToString(memberData.MemberProfiles.FirstOrDefault().Location.zipcode); } else ZipCodeFeed = "85027"; } if (feedLocation == "NewLocation") { radius = NewLocValue; //Format address string[] splitAddressFeed = LocationRadius.Split(','); if (splitAddressFeed.Length > 4) { string[] splitZipRegion = splitAddressFeed[3].ToString().Trim().Split(' '); ZipCodeFeed = splitZipRegion[1].ToString().Trim(); } else { string[] splitZipRegion = splitAddressFeed[2].ToString().Trim().Split(' '); ZipCodeFeed = splitZipRegion[1].ToString().Trim(); } //End formatting address } //Get zip codes according to radius CommonMethods objCommon = new CommonMethods(); radiusZipList = objCommon.GetZipByRadius(radius, ZipCodeFeed); //End get zip codes according to radius if (string.IsNullOrEmpty(radiusZipList)) radiusZipList = ZipCodeFeed; feedQuery += " and locationId in (Select id from Location where zipcode in (" + radiusZipList + "))"; } } #endregion #region include terms in query if (!string.IsNullOrEmpty(txtIncludeTerms)) { string inCondition = string.Empty; string[] includeTerms = txtIncludeTerms.Split(','); for (int i = 0; i < includeTerms.Length; i++) { if (i == 0) inCondition = "'" + includeTerms[i].ToString() + "'"; else inCondition += ",'" + includeTerms[i].ToString() + "'"; } feedQuery += " 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(txtExcludeTerms)) { string exCondition = string.Empty; string[] excludeTerms = txtExcludeTerms.Split(','); for (int i = 0; i < excludeTerms.Length; i++) { if (i == 0) exCondition = "'" + excludeTerms[i].ToString() + "'"; else exCondition += ",'" + excludeTerms[i].ToString() + "'"; } feedQuery += " 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(gIsMedia)) { switch (gIsMedia) { case "All": feedQuery += " and Seed.id in (Select m.seedId from Media m)"; break; case "NoMedia": feedQuery += " and Seed.id not in (Select m.seedId from Media m)"; break; case "PhotosOnly": feedQuery += " and Seed.id in (Select m.seedId from Media m where m.[type]='" + SystemStatements.MEDIA_IMAGE + "')"; break; case "VideosOnly": feedQuery += " and Seed.id in (Select m.seedId from Media m where m.[type]='" + SystemStatements.MEDIA_VIDEO + "')"; break; default: break; } } #endregion stream.criteria = feedQuery; stream.streamType = SystemStatements.STREAM_FEED; stream.ownerId = memberData.id; if (isEdit == "Yes" && !string.IsNullOrEmpty(StreamId)) { ssStream ss = objStream.GetStreamById(StreamId); ss.title = gFeedTitle; ss.description = gFeedDesc; ss.criteria = feedQuery; ss.isPublic = Convert.ToBoolean(gIsPublic); if (!string.IsNullOrEmpty(seedLocationStreamFeed)) { //Format address string StreetAddress = string.Empty; string City = string.Empty; string ZipCode = string.Empty; string StateCode = string.Empty; string[] splitAddress = seedLocationStreamFeed.Split(','); string feedCountry = string.Empty; if (splitAddress.Length > 4) { StreetAddress = splitAddress[0].ToString() + ", " + splitAddress[1].ToString(); City = splitAddress[2].ToString().Trim(); string[] splitZipRegion = splitAddress[3].ToString().Trim().Split(' '); ZipCode = splitZipRegion[1].ToString().Trim(); StateCode = splitZipRegion[0].ToString().Trim(); feedCountry = splitAddress[4].ToString(); } else { StreetAddress = splitAddress[0].ToString(); City = splitAddress[1].ToString().Trim(); string[] splitZipRegion = splitAddress[2].ToString().Trim().Split(' '); ZipCode = splitZipRegion[1].ToString().Trim(); StateCode = splitZipRegion[0].ToString().Trim(); feedCountry = splitAddress[3].ToString(); } //End formatting address if (feedCountry.Trim().Equals("USA") || feedCountry.Trim().Equals("US")) feedCountry = feedCountry.Trim(); else throw new Exception("Feeds can not be created outside region of United States"); LocationAction objLocation = new LocationAction(); SeedAction objSeed = new SeedAction(); string latLong = seedCoordinatesStreamFeed; string[] strSplitLatLong = latLong.Split(','); string lat = strSplitLatLong[0].ToString(); string longt = strSplitLatLong[1].ToString(); string crossStreet = StreetAddress.Trim(); string cityId = objLocation.GetCityIdByCityName(City, StateCode); if (string.IsNullOrEmpty(cityId)) cityId = objSeed.AddCity(City, StateCode); Location loc = objLocation.CreateLocation(cityId, ZipCode, Convert.ToDouble(lat), Convert.ToDouble(longt), crossStreet); ss.locationId = loc.id; } ss = objStream.UpdateStream1(ss); if (txtCategoryStreamFeed != null) { string catIds = string.Empty; string[] splitCategories = txtCategoryStreamFeed.Split(','); for (int i = 0; i < splitCategories.Length; i++) { CategoryAction objCatg = new CategoryAction(); string idCatg = objCatg.GetCategoryIdByCategoryName(splitCategories[i].ToString()); if (!string.IsNullOrEmpty(idCatg)) { if (string.IsNullOrEmpty(catIds)) catIds = idCatg; else catIds = catIds + "," + idCatg; } } if (!string.IsNullOrEmpty(catIds)) { string[] arrCatIds = catIds.Split(','); objStream.AddFeedCategories(Convert.ToString(ss.id), arrCatIds); } } if (!string.IsNullOrEmpty(Convert.ToString(ss.id))) { return RedirectToAction("ManageMyFeeds", "SeedStream"); } return RedirectToAction("UnexpectedError", "Error"); } if (!string.IsNullOrEmpty(seedLocationStreamFeed)) { //Format address string StreetAddress = string.Empty; string City = string.Empty; string ZipCode = string.Empty; string StateCode = string.Empty; string[] splitAddress = seedLocationStreamFeed.Split(','); string feedCountry = string.Empty; if (splitAddress.Length > 4) { StreetAddress = splitAddress[0].ToString() + ", " + splitAddress[1].ToString(); City = splitAddress[2].ToString().Trim(); string[] splitZipRegion = splitAddress[3].ToString().Trim().Split(' '); ZipCode = splitZipRegion[1].ToString().Trim(); StateCode = splitZipRegion[0].ToString().Trim(); feedCountry = splitAddress[4].ToString(); } else { StreetAddress = splitAddress[0].ToString(); City = splitAddress[1].ToString().Trim(); string[] splitZipRegion = splitAddress[2].ToString().Trim().Split(' '); ZipCode = splitZipRegion[1].ToString().Trim(); StateCode = splitZipRegion[0].ToString().Trim(); feedCountry = splitAddress[3].ToString(); } //End formatting address if (feedCountry.Trim().Equals("USA") || feedCountry.Trim().Equals("US")) feedCountry = feedCountry.Trim(); else throw new Exception("Feeds can not be created outside region of United States"); LocationAction objLocation = new LocationAction(); SeedAction objSeed = new SeedAction(); string latLong = seedCoordinatesStreamFeed; string[] strSplitLatLong = latLong.Split(','); string lat = strSplitLatLong[0].ToString(); string longt = strSplitLatLong[1].ToString(); string crossStreet = StreetAddress.Trim(); string cityId = objLocation.GetCityIdByCityName(City, StateCode); if (string.IsNullOrEmpty(cityId)) cityId = objSeed.AddCity(City, StateCode); Location loc = objLocation.CreateLocation(cityId, ZipCode, Convert.ToDouble(lat), Convert.ToDouble(longt), crossStreet); stream.locationId = loc.id; } stream.isPublic = Convert.ToBoolean(gIsPublic); stream = objStream.CreateStream1(stream); if (txtCategoryStreamFeed != null) { string catIds = string.Empty; string[] splitCategories = txtCategoryStreamFeed.Split(','); for (int i = 0; i < splitCategories.Length; i++) { CategoryAction objCatg = new CategoryAction(); string idCatg = objCatg.GetCategoryIdByCategoryName(splitCategories[i].ToString()); if (!string.IsNullOrEmpty(idCatg)) { if (string.IsNullOrEmpty(catIds)) catIds = idCatg; else catIds = catIds + "," + idCatg; } } if (!string.IsNullOrEmpty(catIds)) { string[] arrCatIds = catIds.Split(','); objStream.AddFeedCategories(Convert.ToString(stream.id), arrCatIds); } } if (!string.IsNullOrEmpty(Convert.ToString(stream.id))) { return RedirectToAction("ManageMyFeeds", "SeedStream"); } return RedirectToAction("UnexpectedError", "Error"); } catch (Exception ex) { SessionStore.SetSessionValue("PlantError", "Error occurred while creating feed#" + ex.Message.ToString()); return RedirectToAction("PlantError", "Seed"); } #endregion }
public IList<Seed> getSearchResult(string cstreet, string city, string catId, string keyword, string zip, string userName, string rating) { #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 seed.* from seed, Seed_has_Category, Tag where (status='New' or status='Growing')"; } else { searchString = "select seed.* from seed, Tag where (status='New' or status='Growing')"; } bool flag = false; #region Search in location table if ((!string.IsNullOrEmpty(city) && city != "City") || (!string.IsNullOrEmpty(zip) && zip != "Zipcode") || (!string.IsNullOrEmpty(cstreet) && cstreet != "Cross Street")) { searchString += " and locationid in ( select id from location where "; if (city.Length > 0 && city != "City") { 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.GetZipByRadius("2", zip); if (flag) { searchString = searchString.Substring(0, searchString.Length - 1); searchString += " and zipcode in (" + zipResult + "))"; } else { searchString += "zipcode in (" + zipResult + "))"; } flag = true; } } if (!string.IsNullOrEmpty(cstreet)) { if (cstreet.Length > 0 && cstreet != "Cross Street") { if (flag) { searchString = searchString.Substring(0, searchString.Length - 1); searchString += " and crossStreet like '%" + cstreet + "%')"; } else { searchString += "crossStreet like '%" + cstreet + "%')"; } flag = true; } } } #endregion #region Search according to categories if (catId != null && catId.Length > 0) { searchString += " and Seed_has_Category.categoryId = '" + catId + "' and Seed.id=Seed_has_Category.seedId"; } #endregion #region Search in username, firstname, lastname if (!string.IsNullOrEmpty(userName)) { if (userName != "User Name") { 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 by rating if (!string.IsNullOrEmpty(rating)) { if (rating != "Select") { searchString += " and Seed.id in (select seedId from Rating where likes = '" + rating + "')"; } } #endregion #region Search in title or tag or description if (!string.IsNullOrEmpty(keyword)) { if (keyword.Length > 0 && keyword != "Keywords") { searchString += "and (Seed.title like '%" + keyword + "%' or Tag.name like '%" + keyword + "%' or Seed.description like '%" + keyword + "%') and Seed.id=Tag.seedId"; } } #endregion seedList = repoObj.ListPPP<Seed>("usp_SearchSeeds", searchString).ToList(); IList<Seed> returnSeedList = (from s in seedList select s).Distinct().ToList(); return returnSeedList; #endregion }