Esempio n. 1
0
        private void mbntGeo_Click(object sender, EventArgs e)
        {
            txtreslt      = "";
            dtCoordinates = new DataTable();
            validar       = new Classes.validacao();
            format        = new Classes.formatResult();
            DataTable dt = new DataTable();

            geocode = new Classes.Geocode();
            dt      = Classes.excel.import(openFileDialog1.FileName);

            if (validar.existColunm(dt, colY) && validar.existColunm(dt, colX))
            {
                //cria as clunas com uma chave estrageira de fk
                dtCoordinates.Columns.AddRange(new DataColumn[13]
                {
                    new DataColumn("Id", typeof(int)),
                    new DataColumn("BAIRRO", typeof(string)),
                    new DataColumn("LOGRADOURO", typeof(string)),
                    new DataColumn("NUM", typeof(string)),
                    new DataColumn("MUNICIPIO", typeof(string)),
                    new DataColumn("COMPLEMENTO", typeof(string)),
                    new DataColumn("CEP", typeof(string)),
                    new DataColumn("UF", typeof(string)),
                    new DataColumn("PAIS", typeof(string)),
                    new DataColumn("Latitude", typeof(string)),
                    new DataColumn("Longitude", typeof(string)),
                    new DataColumn("Fonte", typeof(string)),
                    new DataColumn("Precisao", typeof(string)),
                });

                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    if (dt.Rows[i][colY].ToString() != "" && dt.Rows[i][colX].ToString() != "")
                    {
                        dt.Rows[i][colY] = dt.Rows[i][colY].ToString().Trim(); //remove espaço do valor da coluna Y na posição i
                        dt.Rows[i][colX] = dt.Rows[i][colX].ToString().Trim(); //remove espaço do valor da coluna X na posição i
                        txtreslt         = geocode.geocodeReverso(mcmbFonte.SelectedIndex, dt.Rows[i][colY].ToString(), dt.Rows[i][colX].ToString(), "*****@*****.**");

                        retorno = format.resultJsonReversoGeo(txtreslt, mcmbFonte.SelectedIndex);

                        if (!retorno[0].Contains("Erro") && !retorno[1].Contains("Erro") && retorno[1] != "NULL" && retorno[2] != "NULL" && retorno[3] != "NULL")
                        {
                            dtCoordinates.Rows.Add(i, validar.formatUTF8(retorno[0]), validar.formatUTF8(retorno[1]), validar.formatUTF8(retorno[2]), validar.formatUTF8(retorno[3]), validar.formatUTF8(retorno[4]), validar.formatUTF8(retorno[5]), validar.formatUTF8(retorno[6]), validar.formatUTF8(retorno[7]), validar.formatUTF8(retorno[8]), validar.formatUTF8(retorno[9]), validar.formatUTF8(retorno[10]), validar.formatUTF8(retorno[11]));
                        }
                        else
                        {
                            dtCoordinates.Rows.Add(i, validar.formatUTF8(retorno[0]), validar.formatUTF8(retorno[1]), validar.formatUTF8(retorno[2]), validar.formatUTF8(retorno[3]));
                        }
                    }
                }
            }
        }
Esempio n. 2
0
        private void bgWork_DoWork(object sender, DoWorkEventArgs e)
        {
            try
            {
                DataSet dsResult = new DataSet();     //Data set para guarda as tabelas do request url
                geocode = new Classes.Geocode();      //instaciando objeto da classe geocode
                format  = new Classes.formatResult(); // instaciando objeto da classe formatResult

                string   docxml = "";
                string   erro   = "0";              //string para reporta erros
                int      busca  = -1;               //instancia da variavel de busca;
                bool     validabusca;               //variavel para verificar se  vai alterar os dados no banco ou permancer se houver algum
                string   url        = string.Empty; //inicializar url vazia para fazer request
                string[] retorno    = new string[12];
                string   numero     = string.Empty;
                string   logradouro = string.Empty;
                string   bairro     = string.Empty;
                string   cidade     = string.Empty;
                string   estado     = string.Empty;
                string   pais       = string.Empty;
                string   cep        = string.Empty;
                string   lat        = string.Empty;
                string   lng        = string.Empty;
                string   precisao   = string.Empty;



                //cria datatable na memoria
                DataTable dt = new DataTable("Dados");
                dt = Dados();//carrega o datatable com os dados do grid

                //verifica se tem registro e se tem uma fonte selecionada
                if (dt.Rows.Count > 0 && cmb != "Selecione uma tabela" && fonte != 0)
                {
                    existcolunm = validar.existColunm(dt, pk);//verifica se a coluna existe no DataTable


                    //instancia tabela na memoria RAM
                    DataTable datageo = new DataTable();
                    // Busca dados da tabela ENDERECO_LOTE cadastrados e ordenados pelo codigo
                    datageo = conexao.SELECT("SELECT * FROM " + tblEnder + " ORDER BY " + fk + " ASC");// seleciona dados geocodificados do banco de dados

                    //verifica se tem a coluna
                    bool contemXY = validar.existColunm(dt, colX, colY);



                    //limpa coluna da tabela na memoria RAM
                    dtCoordinates = new DataTable();


                    //verifica se possui coluna
                    if (dtCoordinates.Columns.Count <= 0)
                    {
                        //verifica se existe a coluna "Id"
                        if (!validar.existColunm(dtCoordinates, "Id"))
                        {
                            //cria as clunas com uma chave estrageira de fk
                            dtCoordinates.Columns.AddRange(new DataColumn[15]
                            {
                                new DataColumn("Id", typeof(int)),
                                new DataColumn("BAIRRO", typeof(string)),
                                new DataColumn("LOGRADOURO", typeof(string)),
                                new DataColumn("NUM", typeof(string)),
                                new DataColumn("MUNICIPIO", typeof(string)),
                                new DataColumn("COMPLEMENTO", typeof(string)),
                                new DataColumn("CEP", typeof(string)),
                                new DataColumn("UF", typeof(string)),
                                new DataColumn("PAIS", typeof(string)),
                                new DataColumn("Latitude", typeof(string)),
                                new DataColumn("Longitude", typeof(string)),
                                new DataColumn("Fonte", typeof(string)),
                                new DataColumn("Precisao", typeof(string)),
                                new DataColumn("validacao", typeof(bool)),
                                new DataColumn(fk, typeof(string))
                            });
                        }
                    }


                    //verifica se a variavel é verdadeira se tem a coluna X (longitude) e Y(Latitude)
                    if (contemXY)
                    {
                        //loop  numero de linhas
                        for (int i = 0; i < dt.Rows.Count; i++)
                        {
                            try
                            {
                                //operação para canlar o loop
                                if (bgWork.CancellationPending)
                                {
                                    e.Cancel = true;
                                    break;
                                }
                                else
                                {
                                    validabusca = false;
                                    busca       = -1;                                 //inicializa a variavel todas as vezes que repetir
                                    tarefa();                                         //sleep  intervalo de request
                                    bgWork.ReportProgress((100 * i) / dt.Rows.Count); //acrescenta na barra de progresso


                                    dt.Rows[i][colY] = dt.Rows[i][colY].ToString().Trim();                      //remove espaço do valor da coluna Y na posição i
                                    dt.Rows[i][colX] = dt.Rows[i][colX].ToString().Trim();                      //remove espaço do valor da coluna Y na posição i

                                    busca = validar.BuscaBinaria(datageo, Convert.ToInt32(dt.Rows[i][pk]), fk); //busca no data table se existe o registro
                                    if (busca != -1)                                                            //veririca se acho o registro
                                    {
                                        if (datageo.Rows[busca]["FONTE"].ToString() != geocode.getFonte(fonte) && datageo.Rows[busca]["LAT"].ToString() != dt.Rows[i][colY].ToString() && datageo.Rows[busca]["LNG"].ToString() != dt.Rows[i][colX].ToString())
                                        {
                                            if (datageo.Rows[busca]["NUM"].ToString() == "NULL" || datageo.Rows[busca]["NUM"].ToString() == "")               //verifica a precisao do registro encontrado é diferente de ROOFTOP
                                            {
                                                docxml      = geocode.geocodeReverso(fonte, dt.Rows[i][colY].ToString(), dt.Rows[i][colX].ToString(), email); //faz o geocodereverso e retorna o dataset
                                                fontegeo    = geocode.getFonte();                                                                             //pega a fonte em texto
                                                validabusca = true;
                                            }
                                            else if (datageo.Rows[busca]["LOGRADOURO"].ToString() == "NULL" || datageo.Rows[busca]["LOGRADOURO"].ToString() == "")
                                            {
                                                docxml      = geocode.geocodeReverso(fonte, dt.Rows[i][colY].ToString(), dt.Rows[i][colX].ToString(), email); //faz o geocodereverso e retorna o dataset
                                                fontegeo    = geocode.getFonte();                                                                             //pega a fonte em texto
                                                validabusca = true;
                                            }

                                            else
                                            {
                                                continue;//passa para o proximo posição não é necessario geocodificar
                                            }
                                        }
                                        else
                                        {
                                            continue;
                                        }
                                    }
                                    else
                                    {
                                        docxml      = geocode.geocodeReverso(fonte, dt.Rows[i][colY].ToString(), dt.Rows[i][colX].ToString(), email); //faz o geocodereverso e retorna o dataset
                                        fontegeo    = geocode.getFonte();                                                                             //pega a fonte em texto
                                        validabusca = false;                                                                                          //
                                    }



                                    retorno = format.resultJsonReversoGeo(docxml, fonte, dt.Rows[i][pk].ToString(), fk);

                                    if (validabusca)
                                    {
                                        //   new DataColumn("BAIRRO", typeof(string)),
                                        //new DataColumn("LOGRADOURO", typeof(string)),
                                        //new DataColumn("NUM", typeof(string)),
                                        //new DataColumn("MUNICIPIO", typeof(string)),
                                        //new DataColumn("COMPLEMENTO", typeof(string)),
                                        //new DataColumn("CEP", typeof(string)),
                                        //new DataColumn("UF", typeof(string)),
                                        //new DataColumn("PAIS", typeof(string)),
                                        //new DataColumn("Latitude",typeof(string)),
                                        //new DataColumn("Longitude",typeof(string)),
                                        //new DataColumn("Fonte",typeof(string)),
                                        //new DataColumn("Precisao",typeof(string)),
                                        //new DataColumn("validacao",typeof(bool)),
                                        //new DataColumn(fk,typeof(string))
                                        if (retorno[1] != "NULL" && retorno[2] != "NULL" && retorno[3] != "NULL")
                                        {
                                            dtCoordinates.Rows.Add(i, validar.formatUTF8(retorno[0]), validar.formatUTF8(retorno[1]), validar.formatUTF8(retorno[2]), validar.formatUTF8(retorno[3]), validar.formatUTF8(retorno[4]), validar.formatUTF8(retorno[5]), validar.formatUTF8(retorno[6]), validar.formatUTF8(retorno[7]), validar.formatUTF8(retorno[8]), validar.formatUTF8(retorno[9]), validar.formatUTF8(retorno[10]), validar.formatUTF8(retorno[11]), validabusca, validar.formatUTF8(retorno[12]));
                                        }
                                        else
                                        {
                                        }
                                    }
                                    else
                                    {
                                        dtCoordinates.Rows.Add(i, validar.formatUTF8(retorno[0]), validar.formatUTF8(retorno[1]), validar.formatUTF8(retorno[2]), validar.formatUTF8(retorno[3]), validar.formatUTF8(retorno[4]), validar.formatUTF8(retorno[5]), validar.formatUTF8(retorno[6]), validar.formatUTF8(retorno[7]), validar.formatUTF8(retorno[8]), validar.formatUTF8(retorno[9]), validar.formatUTF8(retorno[10]), validar.formatUTF8(retorno[11]), validabusca, validar.formatUTF8(retorno[12]));
                                    }



                                    //dtCoordinates.Rows.Add(i,format.resultReversoGeo(dsResult, fonte, dt.Rows[i][pk].ToString(), fk).ToString());

                                    if (retorno[2].Contains("OVER_QUERY_LIMIT"))
                                    {
                                        MessageBox.Show("Limite diario ultrapassado");
                                        break;
                                    }


                                    //if (fonte == 1)//fonte 1 google
                                    //{

                                    //    if (dsResult.Tables["GeocodeResponse"].Rows[0]["status"].ToString() == "OK")//verifica se retorno algo
                                    //    {
                                    //        int media = 0;

                                    //        if (dsResult.Tables["address_component"].Rows[0][1].ToString().Contains("-"))
                                    //        {
                                    //            media = validar.calcMedia(dsResult.Tables["address_component"].Rows[0][1].ToString().Split('-'));
                                    //            //////////////////////id///////////////////////////bairro/////////////////////////////////////logradouro////////////////////////////////////////////numero//////////////////////////////////////////////////////////////////municipio///////////////////////////////////comlemento//////////////cep//////////////////////////////////////////////////////////////////Estado////////////////////////////////////lat///////////////////////////////////////////long////////////////////////////////////////fonte////////////cod_lote/////////////
                                    //            ///adiciona os dados no datatable
                                    //            dtCoordinates.Rows.Add(i, dsResult.Tables["address_component"].Rows[2][0].ToString(), dsResult.Tables["address_component"].Rows[1][0].ToString(), media, dsResult.Tables["address_component"].Rows[3][0].ToString(), "NULL", dsResult.Tables["address_component"].Rows[7][0].ToString(), dsResult.Tables["address_component"].Rows[5][0].ToString(), dsResult.Tables["location"].Rows[0][1].ToString(), dsResult.Tables["location"].Rows[0][0].ToString(), "Google", dsResult.Tables["geometry"].Rows[0][1].ToString(), validabusca, dt.Rows[i][pk].ToString());

                                    //        }
                                    //        else
                                    //        {
                                    //            //////////////////////id///////////////////////////bairro/////////////////////////////////////logradouro////////////////////////////////////////////numero//////////////////////////////////////////////////////////////////municipio///////////////////////////////////comlemento//////////////cep//////////////////////////////////////////////////////////////////Estado////////////////////////////////////lat///////////////////////////////////////////long////////////////////////////////////////fonte////////////cod_lote/////////////
                                    //            ///adiciona os dados no datatable
                                    //            dtCoordinates.Rows.Add(i, dsResult.Tables["address_component"].Rows[2][0].ToString(), dsResult.Tables["address_component"].Rows[1][0].ToString(), dsResult.Tables["address_component"].Rows[0][1].ToString(), dsResult.Tables["address_component"].Rows[3][0].ToString(), "NULL", dsResult.Tables["address_component"].Rows[7][0].ToString(), dsResult.Tables["address_component"].Rows[5][0].ToString(), dsResult.Tables["location"].Rows[0][0].ToString(), dsResult.Tables["location"].Rows[0][1].ToString(), "Google", dsResult.Tables["geometry"].Rows[0][1].ToString(), validabusca, dt.Rows[i][pk].ToString());

                                    //        }

                                    //    }
                                    //    else if (dsResult.Tables["GeocodeResponse"].Rows[0]["status"].ToString() == "OVER_QUERY_LIMIT")//verifica se ultrapasso o limite diario de request
                                    //    {
                                    //        MessageBox.Show("limite excedido");
                                    //        break;
                                    //    }
                                    //    else
                                    //    {
                                    //        //adiciona null se algo nao achar
                                    //        dtCoordinates.Rows.Add(i, "NULL", "NULL", "NULL", "NULL", "NULL", "NULL", "NULL", "NULL", "NULL", "NULL", "NULL", validabusca, dt.Rows[i][pk].ToString());
                                    //    }

                                    //}
                                    //else if (fonte == 2)// 2 fonte Nominatim
                                    //{
                                    //    if (dsResult.Tables["reversegeocode"].Rows[0][0].ToString() == "0")
                                    //    {

                                    //            for (int j = 0; j < dsResult.Tables["result"].Rows.Count; j++)
                                    //            {

                                    //                //dsResult.Tables["addressparts"].Rows[0]["house_number"].ToString();//numero
                                    //                dsResult.Tables["addressparts"].Rows[0]["road"].ToString();//rua
                                    //                    dsResult.Tables["addressparts"].Rows[0]["town"].ToString();//cidade
                                    //                        dsResult.Tables["addressparts"].Rows[0]["county"].ToString();//bairro
                                    //                            dsResult.Tables["addressparts"].Rows[0]["state"].ToString();//estado
                                    //                            dsResult.Tables["addressparts"].Rows[0]["country"].ToString();//pais


                                    //                            dtCoordinates.Rows.Add(i, dsResult.Tables["addressparts"].Rows[0]["county"].ToString(), dsResult.Tables["addressparts"].Rows[0]["road"].ToString(),"NUMBER", dsResult.Tables["addressparts"].Rows[0]["town"].ToString(), "NULL", "NULL", dsResult.Tables["addressparts"].Rows[0]["state"].ToString(), dsResult.Tables["result"].Rows[j][4].ToString(), dsResult.Tables["result"].Rows[j][5].ToString(), "Nominatim", "", validabusca, dt.Rows[i][pk].ToString());


                                    //            }

                                    //    }
                                    //    else
                                    //    {


                                    //        dtCoordinates.Rows.Add(i, "NULL", "NULL", "NULL", "NULL", "NULL", "NULL", "NULL", "NULL", "NULL", "NULL", "NULL", "NULL" ,"NULL");


                                    //    }

                                    //}
                                    //else if(fonte == 3)
                                    //{
                                    //    if (dsResult.Tables["Response"].Rows[0]["StatusDescription"].ToString() == "OK")
                                    //    {


                                    //        dsResult.Tables["GeocodePoint"].Rows[0]["CalculationMethod"].ToString();//precisao
                                    //        dsResult.Tables["Point"].Rows[0]["Latitude"].ToString();//lat
                                    //        dsResult.Tables["Point"].Rows[0]["Longitude"].ToString();//lomg
                                    //        dsResult.Tables["Address"].Rows[0]["AddressLine"].ToString();//rua,numero
                                    //        dsResult.Tables["Address"].Rows[0]["AdminDistrict"].ToString();// sigla estado
                                    //        //dsResult.Tables["Address"].Rows[0]["AdminDistrict2"].ToString();
                                    //        dsResult.Tables["Address"].Rows[0]["CountryRegion"].ToString();//pais
                                    //        dsResult.Tables["Address"].Rows[0]["FormattedAddress"].ToString();//ender completo
                                    //        dsResult.Tables["Address"].Rows[0]["Locality"].ToString();//cidade
                                    //        dsResult.Tables["Address"].Rows[0]["PostalCode"].ToString();//cep

                                    //        dtCoordinates.Rows.Add(i, dsResult.Tables["addressparts"].Rows[0]["county"].ToString(), dsResult.Tables["addressparts"].Rows[0]["road"].ToString(),"NUMBER", dsResult.Tables["addressparts"].Rows[0]["town"].ToString(), "NULL", "NULL", dsResult.Tables["addressparts"].Rows[0]["state"].ToString(), dsResult.Tables["result"].Rows[0][4].ToString(), dsResult.Tables["result"].Rows[0][5].ToString(), "Nominatim", "", validabusca, dt.Rows[i][pk].ToString());

                                    //    }



                                    //}
                                }
                            }
                            catch (Exception exc)
                            {
                                erro = erro + "" + exc.Message;
                            }
                        }


                        if (erro != "0")
                        {
                            MessageBox.Show("Erro:" + erro);
                        }
                    }
                    else
                    {
                        MessageBox.Show("Tabela não possui coluna (X,Y). Selecione outra tabela porfavor");
                    }
                }



                //limpar linhas da tabela
                dt.Clear();
                //função para limpar colunas

                dt.Dispose();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Erro:" + ex.Message);
            }
        }