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