private void btnAddLocation_Click(object sender, RoutedEventArgs e) { lvShowData.Items.Clear(); ZipCodes z = new ZipCodes(); Cities c = new Cities(); States s = new States(); z.Number = int.Parse(tbZipCode.Text); c.Name = tbCityName.Text; s.Name = tbStateName.Text; s.Abbreviation = tbStateAbbreviation.Text; int locationAdded = DAL.AddLocation(c, z, s); if (locationAdded > 0) { lvShowData.Items.Add($"You have added a location."); } else { lvShowData.Items.Add("Failure! The Location was not added to the database."); } }
public IEnumerable <ZipCodes> GetZipcodesByZone(int zone_id) { //This is a fairly generic db query with no parameters and pulling all values from reader and tossing data into User IList <ZipCodes> allZips = new List <ZipCodes>(); using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(Infrastructure.ConfigReader.ConnectionString.ToString())) { conn.Open(); using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(ZoneSelectonZipCodeQuery, conn)) { command.Parameters.Add(new Npgsql.NpgsqlParameter(":zone_id", NpgsqlTypes.NpgsqlDbType.Integer)); command.Prepare(); command.Parameters[0].Value = zone_id; using (Npgsql.NpgsqlDataReader dr = command.ExecuteReader()) { while (dr.Read()) { ZipCodes newZipcode = populateZipcodesFromDB(dr); allZips.Add(newZipcode); } } } } return(allZips); }
public ActionResult Index(FormCollection form) { List <ZipCodes> ziplist = new List <ZipCodes>(); ViewBag.Message = "Address: " + form["txtAddress"]; ViewBag.Message += "\\nLatitude: " + form["txtLatitude"]; ViewBag.Message += "\\nLongitude: " + form["txtLongitude"]; string cs = @"server=35.187.52.149;userid=root;password=hUdj53D45E6;database=tustus_db"; var con = new MySqlConnection(cs); con.Open(); string sql = "SELECT * FROM tustus_db.WCMS_ZipCodes limit 10;"; var cmd = new MySqlCommand(sql, con); MySqlDataReader rdr = cmd.ExecuteReader(); while (rdr.Read()) { ZipCodes objZip = new ZipCodes(); objZip.ID = rdr.GetInt32(0); objZip.zip = rdr.GetString(2); ziplist.Add(objZip); } return(View(ziplist)); }
public void AddZipCodes() { var zip2list = _zipRepository2.GetAll(); foreach (var code2 in zip2list) { try { var city = _cityRepository.GetSingle(x => x.Name == code2.City); if (city != null) { ZipCodes code = new ZipCodes(); code.CityId = city.CityId; code.Number = Convert.ToInt32(code2.ZipCode); code.IsActive = true; code.IsDeleted = false; code.CreatedDate = DateTime.Now; _zipRepository.Add(code); } else { } } catch { continue; } } }
//Get data private void GetData() { try { var doc = new HtmlDocument(); this.URL = ConfigurationManager.AppSettings["FindClubState"] + this.State + ConfigurationManager.AppSettings["FindClubZip"] + this.Zip; doc.LoadHtml(ReadData(URL)); var allClubs = doc.DocumentNode.SelectNodes("//*[contains(@class,'TextDataColumn')]"); int ClubNumber = 1; foreach (var td in allClubs) { string ClubURI = string.Empty; string ClubName = string.Empty; string ClubAddress = string.Empty; string ClubDescription = string.Empty; string ClubID = string.Empty; string Zip = string.Empty; string State = string.Empty; if (td.InnerText.Trim() == "Club") { //Ignore } else { ClubURI = td.SelectSingleNode(".//a").GetAttributeValue("href", "Club Lunk not found"); ClubName = td.SelectSingleNode(string.Format(".//span[@id='ctl00_MainContent_repClubInfo_ctl0{0}_lblClubDisplayName']", ClubNumber)).InnerText.Trim(); ClubAddress = td.SelectSingleNode(string.Format("//span[@id='ctl00_MainContent_repClubInfo_ctl0{0}_lblAddress']", ClubNumber)).InnerHtml.Replace("<br>", ", ").Replace(" ", " ").Replace(" ", " "); ClubDescription = td.InnerText.Trim().Replace(" ", " "); ClubDescription = Regex.Replace(ClubDescription, @"\s+", " "); ClubID = ClubURI.Split('=')[1]; Zip = ClubAddress.Substring(ClubAddress.Length - 5, 5); State = ClubAddress.Substring(ClubAddress.Length - 8, 2); ClubNumber += 1; //Update values ClubsURIs.Add(ClubURI); ClubsNames.Add(ClubName); ClubsAddresses.Add(ClubAddress); ClubsDescriptions.Add(ClubDescription); ClubsIDs.Add(ClubID); if (!ZipCodes.Contains(Zip)) { ZipCodes.Add(Zip); } if (!States.Contains(State)) { States.Add(State); } //Add to the list Classes Classes = new Classes(ClubID); ClubsList.Add(new Club(State, Zip, ClubURI, ClubName, ClubAddress, ClubDescription, ClubID, Classes, Classes.GetClassesList())); } } } catch (Exception e) { throw new Exception(e.Message); } }
public ActionResult DeleteConfirmed(int id) { ZipCodes zipCodes = db.ZipCodes.Find(id); db.ZipCodes.Remove(zipCodes); db.SaveChanges(); return(RedirectToAction("Index")); }
private static ZipCodes populateZipcodesFromDB(Npgsql.NpgsqlDataReader dr) { ZipCodes newZip = new ZipCodes(); newZip.ZipCodeID = Helper.ConvertFromDBVal <int>(dr[0]); newZip.ZipCode = Helper.ConvertFromDBVal <int>(dr[1]); newZip.ZoneID = Helper.ConvertFromDBVal <int>(dr[2]); return(newZip); }
public ActionResult Edit([Bind(Include = "ZipCodeId,ZipCodeNumber")] ZipCodes zipCodes) { if (ModelState.IsValid) { db.Entry(zipCodes).State = EntityState.Modified; db.SaveChanges(); return(RedirectToAction("Index")); } return(View(zipCodes)); }
public ActionResult Create([Bind(Include = "ZipCodeId,ZipCodeNumber")] ZipCodes zipCodes) { if (ModelState.IsValid) { db.ZipCodes.Add(zipCodes); db.SaveChanges(); return(RedirectToAction("Index")); } return(View(zipCodes)); }
// GET: ZipCodes/Delete/5 public ActionResult Delete(int?id) { if (id == null) { return(new HttpStatusCodeResult(HttpStatusCode.BadRequest)); } ZipCodes zipCodes = db.ZipCodes.Find(id); if (zipCodes == null) { return(HttpNotFound()); } return(View(zipCodes)); }
private string GetAreaProvidersByRelId(int r) { string retval = ""; if (proband != null) { string sqlStr = "SELECT zipcode, city, state FROM tblRelativeDetails WHERE relativeID=@relativeID AND apptID=@apptID"; ParameterCollection pc = new ParameterCollection(); pc.Add("relativeID", r); pc.Add("apptID", proband.apptid); DbDataReader reader = BCDB2.Instance.ExecuteReaderWithParams(sqlStr, pc); bool hasZipCode = false; String zipcode = ""; String city = ""; String state = ""; if (reader.Read()) { zipcode = reader.GetValue(0).ToString(); city = reader.GetValue(1).ToString(); state = reader.GetValue(2).ToString(); } reader.Close(); if (zipcode != "") { hasZipCode = ZipCodes.isValidZip(zipcode); } else { zipcode = ZipCodes.getZipCode(city, state); if (zipcode != "") { hasZipCode = ZipCodes.isValidZip(zipcode); } } if (hasZipCode) { retval = GetAreaProvidersByZipCode(zipcode); } } return(retval); }
//private Location SaveCooridnate(string postalCode ) //{ // var coordinate = GetCoordinate(postalCode); // if (coordinate != null && coordinate.Latitude != null && coordinate.Longitude != null) // return coordinate; // var locationLatLong = this.GetAll()?.FirstOrDefault(x => x.Name.EqualsIgnoreCase(postalCode) && x.Latitude != null && x.Longitude != null); // if (locationLatLong != null) // { // //todo add as coordinate // return locationLatLong; // } // //todo Get lat and long // //then update or insert // var location = this.GetAll()?.FirstOrDefault(x => x.Name.EqualsIgnoreCase(postalCode)); // if (location == null) // { //add as coordinate // } // if (ConfigurationManager.AppSettings["google.maps.requestperday"] != null) // _apiRequestsPerDay = ConfigurationManager.AppSettings["google.maps.requestperday"].ToString().ConvertTo<int>(); // if (ConfigurationManager.AppSettings["google.maps.requestpersecond"] != null) // _apiRequestsPerSecond = ConfigurationManager.AppSettings["google.maps.requestpersecond"].ToString().ConvertTo<int>(); // // if (geos.Count > _apiRequestsPerDay) // // geos = geos.Take(_apiRequestsPerDay).ToList(); // long totalElapsedTime = 0; // int millisecondCap = 1000; //or 1 second. // //If we go below this time on all the requests then we'll go over the throttle limit. // int minRequesTimeThreshold = millisecondCap / _apiRequestsPerSecond; // Stopwatch stopwatch = new Stopwatch(); // int index = 1; // var address = this.GetFullAddress(location); //location.Address1 + " " + location.City + " " + location.State + " " + location.Postal; // //location.Name = address; // //var requestUri = string.Format("https://maps.googleapis.com/maps/api/geocode/xml?key=YOURGOOGLEAPIKEY&address={0}&sensor=false", Uri.EscapeDataString(address)); // var requestUri = string.Format("https://maps.googleapis.com/maps/api/geocode/xml?key=YOURGOOGLEAPIKEY&address={0}&sensor=false", Uri.EscapeDataString(address)); // stopwatch.Restart(); // Begin timing. // var request = WebRequest.Create(requestUri); // var response = request.GetResponse(); // var xdoc = XDocument.Load(response.GetResponseStream()); // // var xdoc = XDocument.Parse(_googleGeoXml); //test parse // var status = xdoc.Element("GeocodeResponse").Element("status"); // switch (status.Value) // { // case GoogleGeo.ResponseStatus.OK: // // location.DescriptionEx = xdoc.ToString(); // var result = xdoc.Element("GeocodeResponse").Element("result"); // var locationElement = result.Element("geometry").Element("location"); // location.Latitude = locationElement.Element("lat").Value.ConvertTo<float>(); // location.Longitude = locationElement.Element("lng").Value.ConvertTo<float>(); // //DataQuery dqFC = new DataQuery(); // // dqFC.SQL = string.Format("UPDATE FireDeptIncidents SET Latitude={0}, Longitude={1} WHERE inci_id='{2}'", location.Latitude, location.Longitude, location.inci_id); // // int res = fim.Update(dqFC); // // SetProgressBar(index, "updated:" + address); // break; // case GoogleGeo.ResponseStatus.OverLimit: // //SetProgressBar(-1, "Status: OverLimit"); // //todo log this // return null; // case GoogleGeo.ResponseStatus.Denied: // //todo log this // // SetProgressBar(-1, "Status: Denied"); // //SetProgressBar(0, xdoc.ToString()); // return null; // } // // Stop timing. // stopwatch.Stop(); // long elapsedTime = stopwatch.ElapsedMilliseconds;//How long it took to get and process the response. // if (elapsedTime < minRequesTimeThreshold) // { // //SetProgressBar(-1, "suspending for:" + (minRequesTimeThreshold - (int)elapsedTime).ToString()); // Thread.Sleep(minRequesTimeThreshold - (int)elapsedTime);//sleep is in milliseconds // totalElapsedTime += elapsedTime; // // millisecond = .001 or 10−3 or 1 / 1000 // //so 1 request every 100 milliseconds // } //} //public void ImportZipCodes(string pathToFile) { // ZipCodes codes = LoadZipCodeCoordinates(pathToFile); // foreach (int zipCode in codes.Keys) // { // ZipCode loc = codes[zipCode]; // SaveCooridnate(loc.Code.ToString(), loc.State, loc.Latitude, loc.Longitude); // } // return; //} // Columns 1-2: United States Postal Service State Abbreviation // Columns 3-66: Name (e.g. 35004 5-Digit ZCTA - there are no post office names) // Columns 67-75: Total Population (2000) // Columns 76-84: Total Housing Units (2000) // Columns 85-98: Land Area (square meters) - Created for statistical purposes only. // Columns 99-112: Water Area (square meters) - Created for statistical purposes only. // Columns 113-124: Land Area (square miles) - Created for statistical purposes only. // Columns 125-136: Water Area (square miles) - Created for statistical purposes only. // Columns 137-146: Latitude (decimal degrees) First character is blank or "-" denoting North or South latitude respectively // Columns 147-157: Longitude (decimal degrees) First character is blank or "-" denoting East or West longitude respectively private ZipCodes LoadZipCodeCoordinates(string pathToFile) { ZipCodes codes = new ZipCodes(); string[] fileLines = File.ReadAllLines(pathToFile); string sep = "\t"; foreach (string line in fileLines) { if (string.IsNullOrWhiteSpace(line)) { continue; } string code = ""; //int double lat = 0; double lon = 0; string[] tokens = line.Split(sep.ToCharArray()); //if (!Int32.TryParse(line.Substring(2, 5), out code) || // !double.TryParse(line.Substring(136, 10), out lat) || // !double.TryParse(line.Substring(146, 10), out lon)) // continue;// skip lines that aren't valid if ( //!Int32.TryParse(tokens[0], out code) || !double.TryParse(tokens[5], out lat) || !double.TryParse(tokens[6], out lon)) { continue;// skip lines that aren't valid } if (codes.ContainsKey(code)) { continue; // there are a few duplicates due to state boundaries, ignore them } codes.Add(code, new ZipCode() { // State = line.Substring(0, 2), Code = code, Latitude = ZipCode.ToRadians(lat), Longitude = ZipCode.ToRadians(lon), }); } return(codes); }
public ZipCodes GetZipCode(int zipcodeid) { ZipCodes zip = new ZipCodes(); using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(Infrastructure.ConfigReader.ConnectionString.ToString())) { conn.Open(); using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(ZipSelectQuery, conn)) { using (Npgsql.NpgsqlDataReader dr = command.ExecuteReader()) { while (dr.Read()) { zip = populateZipcodesFromDB(dr); } } } } return(zip); }
/// <summary> /// Looks us a zipcode for authorized users /// </summary> /// <param name="caller">The user; will verify caller is autorized</param> /// <param name="zipcode">The zipcode to lookup</param> /// <returns>City, state for specified zipcode or error description.</returns> public string Lookup(string caller, string zipcode) { string result; // check that caller is authorized if (IsAuthorized(caller)) { // do lookup result = ZipCodes.ContainsKey(zipcode) ? ZipCodes[zipcode] : Resources.UNKOWN_ZIPCCODE_MSG; } else { // unauthorized user message result = Resources.UNAUTHORIZED_USER; } // log call LogResults(caller, zipcode, result); return(result); }
// Method to populate the property of the ZipCode model. This method is a helper to the PopulateAndDisplay // methods of the logic classes to get a ZIP code from zipcodeapi.com for a given locale. // This allows the rate information to be searched in the PowerRate table since a user may not know a zipcode for a locale. public async Task<string> GetZipCode(string city, string stateAbbreviation) { using (var httpClient = new HttpClient()) { string apiKey = "vMGbJYN2IJf2EbjVN2h8bERvkD55ZwNuFcfSWzMtxwPKdi6t0dCV0k6LgZp127rG"; var responseMessage = await httpClient.GetAsync("https://www.zipcodeapi.com/rest/" + apiKey + "/city-zips.json/" + city + "/" + stateAbbreviation); if (!responseMessage.IsSuccessStatusCode) throw new System.Exception("Unable to obtain a ZIP code from ZipCodeApi and proceed with your request. \r\n" + "Only 10 requests are allowed per hour. Please wait and try later."); string responseString = await responseMessage.Content.ReadAsStringAsync(); ZipCodes zipCodesResult = JsonConvert.DeserializeObject<ZipCodes>(responseString); if (zipCodesResult.ZipCodesArr.Length < 1) return null; else return zipCodesResult.ZipCodesArr[0]; } }
private ZipCodes CalculateLatLngForZipCode(string zipcode) { var zipCodeLatLng = GetGeoCoordinate(zipcode); if (zipCodeLatLng.Count == 2) { //insert zipcode var zipCodeLocation = new ZipCodes { ZipCode = zipcode, Latitude = zipCodeLatLng["lat"], Longitude = zipCodeLatLng["lng"] }; _addressService .InsertZipCode(zipCodeLocation); return(zipCodeLocation); } return(null); }
public static int AddLocation(Cities c, ZipCodes z, States s) { SqlConnection conn = new SqlConnection(); conn.ConnectionString = _EditConnectionString; int recordsAdded = -1; try { conn.Open(); SqlCommand command = new SqlCommand("sproc_LocationAdd", conn); command.CommandType = System.Data.CommandType.StoredProcedure; command.Parameters.AddWithValue($"@{Cities.db_Name}", c.Name); command.Parameters.AddWithValue($"@{States.db_Name}", s.Name); command.Parameters.AddWithValue($"@{States.db_Abbreviation}", s.Abbreviation); command.Parameters.AddWithValue($"@{ZipCodes.db_Number}", z.Number); recordsAdded = command.ExecuteNonQuery(); } catch (Exception e) { System.Diagnostics.Debug.WriteLine(e.Message); } finally { conn.Close(); } return(recordsAdded); }
public void InsertZipCode(ZipCodes zipcode) { _zipCodeRepository.Insert(zipcode); }
private string GetAreaProvidersByZipCode(string zipcode) { string retval = ""; zipcode = zipcode.Trim(); if (zipcode.Length > 5) { zipcode = zipcode.Substring(0, 5); } String sqlStr = "SELECT providerID, zipcode FROM lkpProviders WHERE riskClinic=1 AND zipcode IS NOT NULL AND LEN(zipCode)>0;"; DbDataReader reader = BCDB2.Instance.ExecuteReader(sqlStr); var providerHash = new Hashtable(); while (reader.Read()) { String providerID = reader.GetValue(0).ToString(); String providerZipcode = reader.GetValue(1).ToString(); providerZipcode = providerZipcode.Trim(); if (providerZipcode.Length > 5) { providerZipcode = providerZipcode.Substring(0, 5); } providerHash.Add(providerID, providerZipcode); } reader.Close(); IDictionaryEnumerator en = providerHash.GetEnumerator(); var providerDistanceHash = new Hashtable(); while (en.MoveNext()) { String providerID = en.Key.ToString(); String providerZipcode = en.Value.ToString(); if (ZipCodes.isValidZip(providerZipcode)) { double distance = ZipCodes.getDistance(zipcode, providerZipcode); if (distance < 100.0) { providerDistanceHash.Add(providerID, distance); } } } // sort by reverse distance var keys = new string[providerDistanceHash.Count]; providerDistanceHash.Keys.CopyTo(keys, 0); var values = new double[providerDistanceHash.Count]; providerDistanceHash.Values.CopyTo(values, 0); Array.Sort(values, keys); int maxIndex = 10; if (keys.Length < maxIndex) { maxIndex = keys.Length; } for (int i = 0; i < maxIndex; i++) //limit to 10 closest { retval += GetProviderInfo(keys[i]); } return(retval); }
public async Task <bool> ImportZips(IEnumerable <ZipCodeModel> zips) { var zipCodes = await _context.ZipCodes.ToListAsync(); var countries = await _context.Countries.Include(x => x.States).ThenInclude(x => x.Counties) .ThenInclude(x => x.City).ThenInclude(x => x.ZipCodes).ToListAsync(); var countryId = _context.Countries.Max(x => x.Id); var stateId = _context.States.Max(x => x.Id); var countyId = _context.Counties.Max(x => x.Id); var cityId = _context.City.Max(x => x.Id); var zipId = _context.ZipCodes.Max(x => x.Id); foreach (var zipCodeModel in zips) { var country = countries.SingleOrDefault(x => x.Name.Equals(zipCodeModel.Country)); if (country == null) { country = new Countries { Id = ++countryId, Name = zipCodeModel.Country }; countries.Add(country); _context.Countries.Add(country); } var state = country.States.SingleOrDefault(x => x.Name.Equals(zipCodeModel.State)); if (state == null) { state = new States { Id = ++stateId, Name = zipCodeModel.State, CountryId = country.Id }; country.States.Add(state); _context.States.Add(state); } var county = state.Counties.SingleOrDefault(x => x.Name.Equals(zipCodeModel.County)); if (county == null) { county = new Counties() { Id = ++countyId, Name = zipCodeModel.County, StateId = state.Id }; state.Counties.Add(county); _context.Counties.Add(county); } var city = county.City.SingleOrDefault(x => x.Name.Equals(zipCodeModel.City)); if (city == null) { city = new City() { Id = ++cityId, Name = zipCodeModel.City, CountyId = county.Id }; county.City.Add(city); _context.City.Add(city); } var zip = zipCodes.SingleOrDefault(x => x.Zip.Equals(zipCodeModel.Zip)); if (zip == null) { zip = new ZipCodes() { Id = ++zipId, Zip = zipCodeModel.Zip, CityId = city.Id }; zipCodes.Add(zip); _context.ZipCodes.Add(zip); } } _context.SaveChanges(); return(true); }