public ActionResult GetDistributorResults(DistributorParameters dp, FormCollection form) { int stateZoom = Convert.ToInt32(CurrentPage.GetProperty("stateSearchMapZoom").ToString()); int zipZoom = Convert.ToInt32(CurrentPage.GetProperty("zipSearchMapZoom").ToString()); Session["distributors"] = null; string whereClause = "WHERE"; string resultsFor = ""; bool isZipSearch = false; if (string.IsNullOrWhiteSpace(dp.City) && string.IsNullOrWhiteSpace(dp.State) && string.IsNullOrWhiteSpace(dp.ZipCode)) { //Display Error when no selections have been made TempData["error"] = CurrentPage.GetProperty("requiredValidationText") != null?CurrentPage.GetProperty("requiredValidationText").ToString() : "Please indicate State or Zip."; } else if (!string.IsNullOrWhiteSpace(dp.ZipCode)) { //Zip takes priority over City/State isZipSearch = true; string selectedZip = dp.ZipCode; if (ModelState.IsValid) { whereClause = whereClause + " a.postal_code = '" + selectedZip + "'"; resultsFor = "'" + selectedZip + "'"; } } else { if (!string.IsNullOrWhiteSpace(dp.City) && string.IsNullOrWhiteSpace(dp.State)) { //search by City only if no State is selected string selectedCity = dp.City; whereClause = whereClause + " a.city LIKE '%" + selectedCity + "%'"; resultsFor = "'" + selectedCity.ToUpper() + "'"; } else if (string.IsNullOrWhiteSpace(dp.City) && !string.IsNullOrWhiteSpace(dp.State)) { //search by State only if no City is selected string selectedState = dp.State; whereClause = whereClause + " a.state = '" + selectedState + "'"; resultsFor = "'" + selectedState.ToUpper() + "'"; } else { //search by State and City string selectedCity = dp.City; string selectedState = dp.State; whereClause = whereClause + " a.city LIKE '%" + selectedCity + "%' AND a.state = '" + selectedState + "'"; resultsFor = "'" + selectedCity.ToUpper() + ", " + selectedState.ToUpper() + "'"; } } if (whereClause != "WHERE") { TempData["resultsFor"] = (CurrentPage.GetProperty("resultsForTextOverride") != null ? CurrentPage.GetProperty("resultsForTextOverride").ToString() : "RESULTS FOR") + " " + resultsFor; TempData["resultsFlag"] = true; DistributorResultSet drs = new DistributorResultSet(); if (!isZipSearch) { TempData["mapZoom"] = stateZoom; DataSet ds = new DataSet(); List <Distributor> distributors = new List <Distributor>(); using (SqlConnection con = new SqlConnection(connStr)) { string sql = "SELECT a.[name],a.[address1],a.[city],a.[state],a.[postal_code],a.[phone],a.[fax],a.[website], b.lat, b.long FROM [dbo].[distributor] a LEFT JOIN [dbo].[zips] b on a.postal_code = b.zip " + whereClause + " AND[type] = 1 ORDER BY a.[name]"; using (SqlCommand sqlcmd = new SqlCommand(sql)) { sqlcmd.Connection = con; using (SqlDataAdapter sda = new SqlDataAdapter(sqlcmd)) { sda.Fill(ds); } } } int markerId = 1; foreach (DataRow row in ds.Tables[0].Rows) { Distributor distributor = new Distributor(); distributor.Name = Convert.ToString(row["name"]); distributor.Address = Convert.ToString(row["address1"]); distributor.City = Convert.ToString(row["city"]).Replace(' ', '-'); distributor.StateValue = Convert.ToString(row["state"]).Replace(' ', '-'); distributor.State = Convert.ToString(row["state"]); distributor.Zip = Convert.ToString(row["postal_code"]); distributor.Phone = Convert.ToString(row["phone"]); distributor.Fax = Convert.ToString(row["fax"]); distributor.Website = Convert.ToString(row["website"]); if (row["lat"] != DBNull.Value && row["long"] != DBNull.Value) { distributor.Lat = Convert.ToString(row["lat"]); distributor.Long = "-" + Convert.ToString(row["long"]); distributor.MarkerId = markerId; markerId++; } else { distributor.Lat = ""; distributor.Long = ""; distributor.MarkerId = 0; } distributor.Distance = "undefined"; distributors.Add(distributor); } foreach (Distributor dis in distributors) { if (dis.Website.Length > 4 && !(dis.Website.Substring(0, 4) == "http")) { dis.Website = "http://" + dis.Website; } } //foreach (Distributor dis in distributors) //{ // using (WebClient wc = new WebClient()) // { // var json = wc.DownloadString("https://maps.googleapis.com/maps/api/geocode/json?address=" + dis.Address + " " + dis.City + ", " + dis.State + " " + dis.Zip + "&key=AIzaSyCShc6ISfXBfzjitikfWY6pJIaQpw4dpxs"); // GoogleMapJSON googleMapJSON = JsonConvert.DeserializeObject<GoogleMapJSON>(json); // dis.Lat = googleMapJSON.results[0].geometry.location.lat; // dis.Long = googleMapJSON.results[0].geometry.location.lng; // } //} drs.Distributors = distributors; } else { TempData["mapZoom"] = zipZoom; TempData["resultsFor"] = (CurrentPage.GetProperty("resultsForTextOverride") != null ? CurrentPage.GetProperty("resultsForTextOverride").ToString() : "RESULTS FOR") + " " + resultsFor; TempData["resultsFlag"] = true; drs.Distributors = GetDistributorsByZip(dp.ZipCode, 50); } if (drs.Distributors.Count > 0) { Session["distributors"] = drs; } else { TempData["resultsFor"] = (CurrentPage.GetProperty("noResultsForTextOverride") != null ? CurrentPage.GetProperty("noResultsForTextOverride").ToString() : "NO RESULTS FOR") + " " + resultsFor; } } return(CurrentUmbracoPage()); }
public ActionResult GetSalesRepResults(DistributorParameters dp, FormCollection form) { Session["distributors"] = null; string whereClause = "WHERE"; string resultsFor = ""; bool isZipSearch = false; if (string.IsNullOrWhiteSpace(dp.City) && string.IsNullOrWhiteSpace(dp.State) && string.IsNullOrWhiteSpace(dp.ZipCode)) { //Display Error when no selections have been made TempData["error"] = CurrentPage.GetProperty("requiredValidationText") != null?CurrentPage.GetProperty("requiredValidationText").ToString() : "Please indicate State"; } else if (!string.IsNullOrWhiteSpace(dp.ZipCode)) { //Zip takes priority over City/State isZipSearch = true; string selectedZip = dp.ZipCode; if (ModelState.IsValid) { whereClause = whereClause + " a.postal_code = '" + selectedZip + "'"; resultsFor = "'" + selectedZip + "'"; } } else { if (!string.IsNullOrWhiteSpace(dp.City) && string.IsNullOrWhiteSpace(dp.State)) { //search by City only if no State is selected string selectedCity = dp.City; whereClause = whereClause + " a.city LIKE '%" + selectedCity + "%'"; resultsFor = "'" + selectedCity.ToUpper() + "'"; } else if (string.IsNullOrWhiteSpace(dp.City) && !string.IsNullOrWhiteSpace(dp.State)) { //search by State only if no City is selected string selectedState = dp.State; whereClause = whereClause + " a.state = '" + selectedState + "'"; resultsFor = "'" + selectedState.ToUpper() + "'"; } else { //search by State and City string selectedCity = dp.City; string selectedState = dp.State; whereClause = whereClause + " a.city LIKE '%" + selectedCity + "%' AND a.state = '" + selectedState + "'"; resultsFor = "'" + selectedCity.ToUpper() + ", " + selectedState.ToUpper() + "'"; } } if (whereClause != "WHERE") { TempData["resultsFor"] = (CurrentPage.GetProperty("resultsForTextOverride") != null ? CurrentPage.GetProperty("resultsForTextOverride").ToString() : "RESULTS FOR") + " " + resultsFor; TempData["resultsFlag"] = true; DistributorResultSet drs = new DistributorResultSet(); if (!isZipSearch) { DataSet ds = new DataSet(); List <Distributor> distributors = new List <Distributor>(); using (SqlConnection con = new SqlConnection(connStr)) { string sql = "SELECT a.[name],a.[address1],a.[city],a.[state],a.[postal_code],a.[email],a.[phone],a.[fax],a.[website] FROM[euclidOAS].[dbo].[distributor] a " + whereClause + "AND [type] = 0"; using (SqlCommand sqlcmd = new SqlCommand(sql)) { sqlcmd.Connection = con; using (SqlDataAdapter sda = new SqlDataAdapter(sqlcmd)) { sda.Fill(ds); } } } foreach (DataRow row in ds.Tables[0].Rows) { Distributor distributor = new Distributor(); distributor.Name = Convert.ToString(row["name"]); distributor.Address = Convert.ToString(row["address1"]); distributor.City = Convert.ToString(row["city"]).Replace(' ', '-'); distributor.StateValue = Convert.ToString(row["state"]).Replace(' ', '-'); distributor.State = Convert.ToString(row["state"]); distributor.Zip = Convert.ToString(row["postal_code"]); distributor.Email = Convert.ToString(row["email"]); distributor.Phone = Convert.ToString(row["phone"]); distributor.Fax = Convert.ToString(row["fax"]); distributor.Website = Convert.ToString(row["website"]); distributor.Distance = "undefined"; distributors.Add(distributor); } drs.Distributors = distributors; } else { TempData["resultsFor"] = (CurrentPage.GetProperty("resultsForTextOverride") != null ? CurrentPage.GetProperty("resultsForTextOverride").ToString() : "RESULTS FOR") + " " + resultsFor; TempData["resultsFlag"] = true; drs.Distributors = GetDistributorsByZip(dp.ZipCode, 25); } if (drs.Distributors.Count > 0) { Session["distributors"] = drs; } else { TempData["resultsFor"] = (CurrentPage.GetProperty("noResultsForTextOverride") != null ? CurrentPage.GetProperty("noResultsForTextOverride").ToString() : "NO RESULTS FOR") + " " + resultsFor; } } return(CurrentUmbracoPage()); }