Boolean getAddresses()
{
try
{
toProcess = false;
records = new List <GeoCodeRecord>();
String sql = "exec stp_LoadAddressQueue '" + utype + "'";
SqlDataReader dr = SqlHelper.ExecuteReader(connstring, CommandType.Text, sql);
do
{
while (dr.Read())
{
GeoCodeRecord rec = new GeoCodeRecord();
rec.location = ParseStreet(Convert.ToString(dr["Address"]));
rec.street1 = ParseStreet(Convert.ToString(dr["Street1"]));
rec.street2 = ParseStreet(Convert.ToString(dr["Street1"]));
rec.city = ParseStreet(Convert.ToString(dr["City"]));
rec.state = ParseStreet(Convert.ToString(dr["State"]));
rec.zip = ParseStreet(Convert.ToString(dr["Zip"]));
rec.address_id = Convert.ToInt32(dr["Address_Id"]);
records.Add(rec);
toProcess = true;
}
}while (dr.NextResult());
dr.Close();
return(true);
}
catch (Exception e)
{
Console.Write("Err:" + e.Message);
return(false);
}
finally{}
}
Boolean updateAddress(GeoCodeRecord geo)
{
String sql = "";
try
{
String table = "";
String id = "";
switch (utype)
{
case "Emp":
{
table = "tblEmployee_Address"; id = "Address_Id";
break;
}
case "EmpInt":
{
table = "tblEmployee_Address"; id = "Address_Id";
break;
}
case "Client":
{
table = "tblCOMPANY_CONTACT_ADDRESS"; id = "CompContAddress_Id";
break;
}
case "Order":
{
table = "PSGBoston.dbo.OrderDetails"; id = "OrderId";
break;
}
case "Zip":
{
table = "tblxZIPCODE"; id = "Pk_Id";
break;
}
}
if (utype != "Order")
{
sql = "UPDATE " + table + " SET Geo_Lat='" + geo.latitude + "', Geo_Long='" + geo.longitude + "'," +
"GeoAddressUsed='" + geo.formattedLocation.Replace("'", "''") + "', ModifiedDate=GetDate() WHERE " + id + "=" + geo.address_id;
}
else
{
sql = "UPDATE " + table + " SET GeoLatitude='" + geo.latitude + "', GeoLongitude='" + geo.longitude + "'" +
"WHERE " + id + "=" + geo.address_id;
}
psgSQLHelper.SqlHelper.ExecuteScalar(connstring, CommandType.Text, sql);
return(true);
}
catch (Exception e)
{
Console.Write("Err:" + sql + " " + e.Message);
return(false);
}
finally { }
}
Boolean processGeoAddresses(GeoCodeRecord rec)
{
XElement x;
Boolean result = true;
try
{
// String geocodeURL = "http://maps.google.com/maps/geo?key=" + APIKey + "&q=" + rec.location + "&sensor=false&output=xml";
String geocodeURL = "https://maps.googleapis.com/maps/api/geocode/xml?address=" + rec.location + "&key=" + APIKey;// "&sensor=false";
rec.Document = System.Xml.Linq.XDocument.Load(geocodeURL);
rec.status = rec.Document.Descendants().First(p => p.Name.LocalName == "status").Value;
if (rec.status == "OK")
{
x = rec.Document.Descendants().SingleOrDefault(p => p.Name.LocalName == "location");
// XElement x = rec.Document.Descendants().First(p => p.Name.LocalName == "location");
rec.latitude = x.Descendants().SingleOrDefault(p => p.Name.LocalName == "lat").Value;
rec.longitude = x.Descendants().SingleOrDefault(p => p.Name.LocalName == "lng").Value;
rec.formattedLocation = rec.Document.Descendants().SingleOrDefault(p => p.Name.LocalName == "formatted_address").Value;
rec.status = rec.Document.Descendants().SingleOrDefault(p => p.Name.LocalName == "formatted_address").Value;
Console.WriteLine(rec.location + " " + rec.latitude + " " + rec.longitude + " processed");
updateAddress(rec);
count += 1;
result = true;
}
else if (rec.status == "ZERO_RESULTS")
{
rec.latitude = "--";
rec.longitude = "--";
rec.formattedLocation = rec.status;
Console.WriteLine(rec.location + " " + rec.latitude + " " + rec.longitude + " zero results");
updateAddress(rec);
count += 1;
result = true;
}
else if (rec.status == "OVER_QUERY_LIMIT")
{
Console.WriteLine(rec.location + " " + " err" + rec.status);
//Console.Read();
result = false;
}
else
{
Console.WriteLine(utype + ":" + rec.address_id + " " + rec.location + " " + " err" + rec.Document.ToString());
Console.Read();
result = false;
}
return(result);
}
catch (Exception ex)
{
Console.WriteLine(utype + ":" + +rec.address_id + " " + rec.location + " " + ex.Message);
try
{
if (rec.status == "OK")
{
x = rec.Document.Descendants().First(p => p.Name.LocalName == "location");
// XElement x = rec.Document.Descendants().First(p => p.Name.LocalName == "location");
rec.latitude = x.Descendants().First(p => p.Name.LocalName == "lat").Value;
rec.longitude = x.Descendants().First(p => p.Name.LocalName == "lng").Value;
rec.formattedLocation = rec.Document.Descendants().First(p => p.Name.LocalName == "formatted_address").Value;
Console.WriteLine(rec.location + " " + rec.latitude + " " + rec.longitude + " first rec processed");
updateAddress(rec);
count += 1;
result = true;
}
else
{
Console.WriteLine(utype + ":" + rec.address_id + " " + rec.location + " " + " err" + ex.Message);
// Console.Read();
result = false;
}
}
catch (Exception ex1)
{
Console.WriteLine(utype + ":" + rec.address_id + " " + rec.location + " " + ex1.Message + " err");
String status = rec.Document.Descendants().First(p => p.Name.LocalName == "status").Value;
rec.latitude = "--";
rec.longitude = "--";
rec.formattedLocation = status + " " + ex1.Message;
updateAddress(rec);
count += 1;
result = true;
}
return(result);
}
finally { }
}
