private void DisplayBtn_Click(object sender, EventArgs e)
{
//https://devlights.hatenablog.com/entry/20070302/p2
this.MntGritView.DataSource = new BindingSource();
var dbConnect = new DbConect();
//設定
//奇数行を青色にする
}
/**
* Method: SalvarImagensURLs
* Description: Salva no banco de dados as URLs de imagens/fotos dos locais
* Author: Luiz F. Amaral
* Data: 02/12/2020
* Website: https://luizamaral.dev.br
* Contact: [email protected]
*/
public void SalvarImagensURLs(List <String> imagens, Int32 idLocal)
{
if (imagens == null)
{
return;
}
NpgsqlConnection pgsqlConnection = DbConect.GetConection();
//Abra a conexão com o PgSQL
pgsqlConnection.Open();
try
{
foreach (var url in imagens)
{
String strSQL = " Insert into ImagemURL " +
" ( idLocal, " +
" url ) "+
" Values(@param1, @param2) ";
NpgsqlCommand cmd = new NpgsqlCommand(strSQL, pgsqlConnection);
cmd.Parameters.AddWithValue("@param1", Utils.Utils.SafeDbObject(idLocal));
cmd.Parameters.AddWithValue("@param2", Utils.Utils.SafeDbObject(url));
cmd.ExecuteNonQuery();
}
}
catch (NpgsqlException ex)
{
throw ex;
}
catch (Exception ex)
{
throw ex;
}
finally
{
pgsqlConnection.Close();
}
}
/**
* Method: SalvarHorariosHistogramItem
* Description: Salva no banco de dados um horario de funcionamento do local
* Author: Luiz F. Amaral
* Data: 02/12/2020
* Website: https://luizamaral.dev.br
* Contact: [email protected]
*/
public void SalvarHorariosHistogramItem(Int32 idLocal, int diaSemana, int hora, int taxa)
{
NpgsqlConnection pgsqlConnection = DbConect.GetConection();
//Abra a conexão com o PgSQL
pgsqlConnection.Open();
try
{
String strSQL = " Insert into HorariosHistoricoHistogram " +
" ( idLocal, " +
" idDiaSemana, "+
" hora, "+
" taxaOcupacao) "+
" Values(@param1, @param2, @param3, @param4) ";
NpgsqlCommand cmd = new NpgsqlCommand(strSQL, pgsqlConnection);
cmd.Parameters.AddWithValue("@param1", Utils.Utils.SafeDbObject(idLocal));
cmd.Parameters.AddWithValue("@param2", Utils.Utils.SafeDbObject(diaSemana));
cmd.Parameters.AddWithValue("@param3", Utils.Utils.SafeDbObject(hora));
cmd.Parameters.AddWithValue("@param4", Utils.Utils.SafeDbObject(taxa));
cmd.ExecuteNonQuery();
}
catch (NpgsqlException ex)
{
throw ex;
}
catch (Exception ex)
{
throw ex;
}
finally
{
pgsqlConnection.Close();
}
}
/**
* Method: SalvarReviewsUsuarios
* Description: Salva no banco de dados as revisões/comentarios dos usuários sobre o local
* Author: Luiz F. Amaral
* Data: 02/12/2020
* Website: https://luizamaral.dev.br
* Contact: [email protected]
*/
public void SalvarReviewsUsuarios(List <Review> listaReview, Int32 idLocal)
{
if (listaReview == null)
{
return;
}
NpgsqlConnection pgsqlConnection = DbConect.GetConection();
//Abra a conexão com o PgSQL
pgsqlConnection.Open();
try
{
foreach (var item in listaReview)
{
String strSQL = " Insert into reviewsusuarios " +
" ( " +
" idlocal, " +
" idreview, " +
" tempopublicacaodataimport, " +
" dataimport, " +
" url, " +
" likes, " +
" pontuacao, " +
" nome, " +
" mensagem, " +
" reviewerid, " +
" reviewerurl, " +
" reviewernumberofreviews, " +
" islocalguide " +
" ) " +
" Values(@param1, @param2, @param3, @param4, @param5, @param6, @param7, @param8, " +
" @param9, @param10, @param11, @param12, @param13) ";
NpgsqlCommand cmd = new NpgsqlCommand(strSQL, pgsqlConnection);
cmd.Parameters.AddWithValue("@param1", Utils.Utils.SafeDbObject(idLocal));
cmd.Parameters.AddWithValue("@param2", Utils.Utils.SafeDbObject(item.reviewId));
cmd.Parameters.AddWithValue("@param3", Utils.Utils.SafeDbObject(item.publishAt));
cmd.Parameters.AddWithValue("@param4", DateTime.Now);
cmd.Parameters.AddWithValue("@param5", Utils.Utils.SafeDbObject(item.reviewUrl));
cmd.Parameters.AddWithValue("@param6", Utils.Utils.SafeDbObject(item.likesCount));
cmd.Parameters.AddWithValue("@param7", Utils.Utils.SafeDbObject(item.stars));
cmd.Parameters.AddWithValue("@param8", Utils.Utils.SafeDbObject(item.name));
cmd.Parameters.AddWithValue("@param9", Utils.Utils.SafeDbObject(item.text));
cmd.Parameters.AddWithValue("@param10", Utils.Utils.SafeDbObject(item.reviewerId));
cmd.Parameters.AddWithValue("@param11", Utils.Utils.SafeDbObject(item.reviewerUrl));
cmd.Parameters.AddWithValue("@param12", Utils.Utils.SafeDbObject(item.reviewerNumberOfReviews));
cmd.Parameters.AddWithValue("@param13", Utils.Utils.SafeDbObject(item.isLocalGuide));
cmd.ExecuteNonQuery();
}
}
catch (NpgsqlException ex)
{
throw ex;
}
catch (Exception ex)
{
throw ex;
}
finally
{
pgsqlConnection.Close();
}
}
/**
* Method: SalvarHorariosSemanais
* Description: Salva no banco de dados as informações dos horarios semanais
* Author: Luiz F. Amaral
* Data: 02/12/2020
* Website: https://luizamaral.dev.br
* Contact: [email protected]
*/
public void SalvarHorariosSemanais(List <OpeningHour> horarioFuncionamento, Int32 idLocal)
{
if (horarioFuncionamento == null)
{
return;
}
NpgsqlConnection pgsqlConnection = DbConect.GetConection();
//Abra a conexão com o PgSQL
pgsqlConnection.Open();
try
{
foreach (var item in horarioFuncionamento)
{
int idDia = 0;
if (item.day.Equals("Sunday,"))
{
idDia = ID_DOMINGO;
}
else if (item.day.Equals("Monday,"))
{
idDia = ID_SEGUNDA;
}
else if (item.day.Equals("Tuesday,"))
{
idDia = ID_TERCA;
}
else if (item.day.Equals("Wednesday,"))
{
idDia = ID_QUARTA;
}
else if (item.day.Equals("Thursday,"))
{
idDia = ID_QUINTA;
}
else if (item.day.Equals("Friday,"))
{
idDia = ID_SEXTA;
}
else if (item.day.Equals("Saturday,"))
{
idDia = ID_SABADO;
}
if (idDia == 0)
{
Console.WriteLine("Erro ao importar horários de funcionamento");
continue;
}
String strSQL = " Insert into HorariosFuncionamento " +
" ( idLocal, " +
" idDiaSemana, "+
" intervaloDesc ) "+
" Values(@param1, @param2, @param3) ";
NpgsqlCommand cmd = new NpgsqlCommand(strSQL, pgsqlConnection);
cmd.Parameters.AddWithValue("@param1", idLocal);
cmd.Parameters.AddWithValue("@param2", idDia);
cmd.Parameters.AddWithValue("@param3", Utils.Utils.SafeDbObject(item.hours));
cmd.ExecuteNonQuery();
}
}
catch (NpgsqlException ex)
{
throw ex;
}
catch (Exception ex)
{
throw ex;
}
finally
{
pgsqlConnection.Close();
}
}
/**
* Method: SalvarLocal
* Description: Salva no banco de dados as informações dos Locais da pesquisa(Places)
* Author: Luiz F. Amaral
* Data: 02/12/2020
* Website: https://luizamaral.dev.br
* Contact: [email protected]
*/
public void SalvarLocal(Place local, int count)
{
NpgsqlConnection pgsqlConnection = DbConect.GetConection();
//Abra a conexão com o PgSQL
pgsqlConnection.Open();
String strSQL = "";
NpgsqlCommand cmd = null;
try
{
strSQL = " Insert into Local " +
" ( titulo, "+
" pontuacaoTotal, " +
" categoriaNome, " +
" endereco, " +
" plusCode, " +
" website, " +
" telefone, " +
" temporarioFechado, " +
" fechado, " +
" ranking, " +
" placeId, " +
" url, " +
" latitude, " +
" longitude, " +
" searchString, " +
" HorarioPopularesTxt, " +
" contagemReviews) " +
" Values(@param2, @param3, @param4, @param5, @param6, " +
" @param7, @param8, @param9, @param10, @param11, @param12, " +
" @param13, @param14, @param15, @param16, @param17, @param18) " +
" RETURNING idLocal ";
cmd = new NpgsqlCommand(strSQL, pgsqlConnection);
cmd.Parameters.AddWithValue("@param2", Utils.Utils.SafeDbObject(local.title));
cmd.Parameters.AddWithValue("@param3", Utils.Utils.SafeDbObject(local.totalScore));
cmd.Parameters.AddWithValue("@param4", Utils.Utils.SafeDbObject(local.categoryName));
cmd.Parameters.AddWithValue("@param5", Utils.Utils.SafeDbObject(local.address));
cmd.Parameters.AddWithValue("@param6", Utils.Utils.SafeDbObject(local.plusCode));
cmd.Parameters.AddWithValue("@param7", Utils.Utils.SafeDbObject(local.website));
cmd.Parameters.AddWithValue("@param8", Utils.Utils.SafeDbObject(local.phone));
cmd.Parameters.AddWithValue("@param9", Utils.Utils.SafeDbObject(local.temporarilyClosed));
cmd.Parameters.AddWithValue("@param10", Utils.Utils.SafeDbObject(local.permanentlyClosed));
cmd.Parameters.AddWithValue("@param11", Utils.Utils.SafeDbObject(local.rank));
cmd.Parameters.AddWithValue("@param12", Utils.Utils.SafeDbObject(local.placeId));
cmd.Parameters.AddWithValue("@param13", Utils.Utils.SafeDbObject(local.url));
cmd.Parameters.AddWithValue("@param14", Utils.Utils.SafeDbObject(local.location.lat));
cmd.Parameters.AddWithValue("@param15", Utils.Utils.SafeDbObject(local.location.lng));
cmd.Parameters.AddWithValue("@param16", Utils.Utils.SafeDbObject(local.searchString));
cmd.Parameters.AddWithValue("@param17", Utils.Utils.SafeDbObject(local.popularTimesLivePercent));
cmd.Parameters.AddWithValue("@param18", Utils.Utils.SafeDbObject(local.reviewsCount));
Int32 idLocal = Convert.ToInt32(cmd.ExecuteScalar());
pgsqlConnection.Close();
SalvarHorariosSemanais(local.openingHours, idLocal);
SalvarHorariosHistogram(local.popularTimesHistogram, idLocal);
SalvarReviewsUsuarios(local.reviews, idLocal);
SalvarImagensURLs(local.imageUrls, idLocal);
}
catch (NpgsqlException ex)
{
Console.WriteLine(count);
throw ex;
}
catch (Exception ex)
{
throw ex;
}
}
