public string importKML(string path, int codUser, string filename)
{
try
{
valida = new Classes.validacao();
fileStream = File.Open(path, FileMode.Open);
var mapper = new Kml2SqlMapper(fileStream);
var sb = new StringBuilder();
sb.Clear();
var lista = mapper.GetMapFeatures().ToList();
int arquafet = 0;
sb.Append(" Declare @count int ");
for (int i = 0; i < lista.Count(); i++)
{
if (lista[i].ShapeType.ToString() == "Point" && lista[i].Name.ToString() != "Untitled Placemark" && lista[i].Name.ToString() != "")
{
arquafet++;
string lat = lista[i].Coordinates[0].Latitude.ToString().Replace(",", ".");
string lng = lista[i].Coordinates[0].Longitude.ToString().Replace(",", ".");
if (lista[i].Name.Contains('-') && lista[i].Name.Contains('.'))
{
serie = lista[i].Name.Split('-')[0];
if (lista[i].Name.Split('-')[1].Contains("."))
{
ini = lista[i].Name.Split('-')[1].Split('.')[0];
fim = lista[i].Name.Split('-')[1].Split('.')[1];
}
else
{
ini = lista[i].Name.Split('-')[1];
fim = lista[i].Name.Split('-')[1];
}
}
else if (lista[i].Name.Contains('.'))
{
ini = lista[i].Name.Split('.')[0];
fim = lista[i].Name.Split('.')[1];
}
else if (lista[i].Name.Contains('-'))
{
serie = lista[i].Name.Split('-')[0];
ini = lista[i].Name.Split('-')[1];
fim = lista[i].Name.Split('-')[1];
}
else
{
ini = lista[i].Name;
fim = lista[i].Name;
}
//ini = lista[i].Name.Split('-')[0];
//fim = lista[i].Name.Split('-')[1];
string shape = "geometry::STGeomFromText('POINT(" + lng + " " + lat + ")',4326)";
sb.Append(" SELECT @count = COUNT(*) FROM " + tabela +
" IF(@count > 0) " +
" BEGIN " +
" INSERT INTO " + tabela + " " + colunas1 + " VALUES(((SELECT TOP 1 " + pk + " FROM " + tabela + " ORDER BY " + pk + " DESC) + 1),'" + ini + "'," + valida.prepDB(lista[i].Id.ToString()) + "," + shape + "," + valida.prepDB(filename) + ",'" + datanow + "'," + valida.prepDB(codUser.ToString()) + "," + valida.prepDB(ddlEquipe.Text) + "," + valida.validaData(ddllocal.Text) + ",'" + fim + "','" + serie + "') " +
" END " +
" ELSE " +
" BEGIN " +
" INSERT INTO " + tabela + " " + colunas1 + " VALUES(1,'" + ini + "'," + valida.prepDB(lista[i].Id.ToString()) + "," + shape + "," + valida.prepDB(filename) + ",'" + datanow + "'," + valida.prepDB(codUser.ToString()) + "," + valida.prepDB(ddlEquipe.Text) + "," + valida.validaData(ddllocal.Text) + ",'" + fim + "','" + serie + "')" +
" END ");
}
}
conexao.commandExec(sb.ToString());
fileStream.Close();
fileStream.Dispose();
return("" + lista.Count() + "," + arquafet);
}catch (Exception ex)
{
fileStream.Close();
fileStream.Dispose();
return("Erro : " + ex.Message);
}
}
public string[] resultJsonReversoGeo(string txtreturn, int fonte)
{
string numero = "NULL";
string logradouro = "NULL";
string bairro = "NULL";
string cidade = "NULL";
string estado = "NULL";
string pais = "NULL";
string cep = "NULL";
string lat = "NULL";
string lng = "NULL";
string precisao = "NULL";
string complemento = "NULL";
string[] row = new string[12];
dynamic json;
string type = "";
validar = new validacao();
try
{
switch (fonte)
{
case 1: //GOOGLE
json = JValue.Parse(txtreturn);
if (json.status == "OK")
{
precisao = json.results[0].geometry.location_type;
lat = json.results[0].geometry.location.lat;
lng = json.results[0].geometry.location.lng;
for (int i = 0; i < json.results.Count; i++)
{
for (int j = 0; j < json.results[i].address_components.Count; j++)
{
type = json.results[i].address_components[j].types[0];
if (type == "premise")
{
complemento = json.results[i].address_components[j].long_name;
complemento = "NULL";
}
else if (type == "street_number")
{
numero = json.results[i].address_components[j].long_name;
if (numero.Contains("-"))
{
numero = validar.calcMedia(numero.Split('-')).ToString();
}
numero = validar.removeCaracter(numero);
}
else if (type == "route")
{
logradouro = json.results[i].address_components[j].long_name;
}
else if (type == "sublocality_level_1")
{
bairro = json.results[i].address_components[j].long_name;
}
else if (type == "locality")
{
cidade = json.results[i].address_components[j].long_name;
}
else if (type == "administrative_area_level_1")
{
estado = json.results[i].address_components[j].long_name;
}
else if (type == "country")
{
pais = json.results[i].address_components[j].long_name;
}
else if (type == "postal_code")
{
cep = json.results[i].address_components[j].long_name;
}
}
if (numero != "NULL" && logradouro != "NULL" && bairro != "NULL" && cidade != "NULL" && estado != "NULL" && pais != "NULL" && cep != "NULL") //se tudo completado break
{
break;
}
break;
}
row = new string[] { validar.formatUTF8(bairro), validar.formatUTF8(logradouro), validar.formatUTF8(numero), validar.formatUTF8(cidade), validar.formatUTF8(complemento), validar.formatUTF8(cep), validar.formatUTF8(estado), validar.formatUTF8(pais), lat, lng, "Google", precisao };
}
else //fim status
{
row = new string[] { "Erro(0x00101):", "Erro de requisição", "Messagem original:" + json.status, "", "", "", "", "", "", "", "", "" };
}
break; //fim case 1
case 2: //nominatim
break;
case 3: //bing
json = JValue.Parse(txtreturn);
//DataSet ds = new DataSet();
// XmlDocument docxml = new XmlDocument();
// docxml.LoadXml(txtreturn);
// ds.ReadXml(new XmlNodeReader(docxml));
//if (ds.Tables["Response"].Rows[0]["StatusDescription"].ToString() == "OK")
//{
// if (ds.Tables["Address"].Rows[0]["AddressLine"].ToString().Contains(","))
// {
// logradouro = ds.Tables["Address"].Rows[0]["AddressLine"].ToString().Split(',')[0];
// numero = ds.Tables["Address"].Rows[0]["AddressLine"].ToString().Split(',')[1].Trim();
// }
// else
// {
// logradouro = ds.Tables["Address"].Rows[0]["AddressLine"].ToString();
// }
// row = new string[] { "NULL", logradouro, numero, ds.Tables["Address"].Rows[0]["Locality"].ToString(), "NULL", ds.Tables["Address"].Rows[0]["PostalCode"].ToString(), ds.Tables["Address"].Rows[0]["AdminDistrict"].ToString(),"Brasil",ds.Tables["Point"].Rows[0]["Latitude"].ToString(), ds.Tables["Point"].Rows[0]["Longitude"].ToString(), "Bing", ds.Tables["GeocodePoint"].Rows[0]["CalculationMethod"].ToString(), pk };
//}
//else
//{
// row = new string[] { "Erro", "Não possui resultados", "", "", "", "", "", "", "", "", "", pk };
//}
if (json.statusDescription == "OK")
{
//precisao = json.results[0].geometry.location_type;
//lat = json.results[0].geometry.location.lat;
//lng = json.results[0].geometry.location.lng;
for (int i = 0; i < json.resourceSets.Count; i++)
{
for (int j = 0; j < json.resourceSets[i].resources.Count; j++)
{
dynamic teste1 = json.resourceSets[i].resources[j].o.SelectToken("$.Manufacturers[?(@.Name == 'address')]");
dynamic teste2 = json.resourceSets[i].resources.Children()["address"];
dynamic teste3 = json.resourceSets[i].resources.SelectToken("address");
dynamic teste = json.resourceSets[i].resources.Contains("address");
type = json.resourceSets[i].resources[j].address.addressLine; //////address line
if (type != "null" && type != "NULL" || type != "")
{
if (logradouro == "NULL" || numero == "NULL")
{
if (type.Contains(","))
{
logradouro = type.Split(',')[0];
numero = type.Split(',')[1];
}
else if (type.Contains('-'))
{
}
else
{
logradouro = type;
}
}
}
//////////////////////////fim addres line
estado = json.resourceSets[i].resources[j].address.adminDistrict; //estado
pais = json.resourceSets[i].resources[j].address.countryRegion; //pais
cep = json.resourceSets[i].resources[j].address.postalCode; //cep
cidade = json.resourceSets[i].resources[j].address.locality; //cidade
type = json.resourceSets[i].resources[j].geocodePoints[0].type;
if (type == "Point")
{
lat = json.resourceSets[i].resources[j].geocodePoints[0].coordinates[0];
lng = json.resourceSets[i].resources[j].geocodePoints[0].coordinates[1];
precisao = json.resourceSets[i].resources[j].geocodePoints[0].calculationMethod;
}
}
if (numero != "NULL" && logradouro != "NULL" && bairro != "NULL" && cidade != "NULL" && estado != "NULL" && pais != "NULL" && cep != "NULL" && complemento != "NULL") //se tudo completado break
{
break;
}
}
row = new string[] { validar.formatUTF8(bairro), validar.formatUTF8(logradouro), validar.formatUTF8(numero), validar.formatUTF8(cidade), validar.formatUTF8(complemento), validar.formatUTF8(cep), validar.formatUTF8(estado), validar.formatUTF8(pais), lat, lng, "Bing", precisao };
}
else //fim status
{
row = new string[] { "Erro(0x00101):", "Erro de requisição", "Messagem original:" + json.status, "", "", "", "", "", "", "", "", "" };
}
break;
default:
row = new string[] { "Erro", "Fonte não encontrada", "", "", "", "", "", "", "", "", "", };
break;
}
return(row);
}
catch (Exception ex)
{
string erro = ex.Message;
return(row = new string[] { "Erro(0x00103):", "Erro de formatação", "Messagem original:" + erro, "", "", "", "", "", "", "", "", "" });
}
}