public int ContabilizarCidades()
{
SqlCommand command = new Conexao().ObterConexao();
command.CommandText = @"SELECT COUNT(id) FROM historico_de_viagens WHERE ativo = 1";
return(Convert.ToInt32(command.ExecuteScalar().ToString()));
}
public int ContabilizarPacotes()
{
SqlCommand command = new Conexao().ObterConexao();
command.CommandText = @"SELECT COUNT(id) FROM pacotes WHERE ativo = 1";
return(Convert.ToInt32(command.ExecuteScalar().ToString()));
}
public int Cadastrar(Guia guia)
{
SqlCommand command = new Conexao().ObterConexao();
//
command.CommandText = @"INSERT INTO guias (sexo, nome, sobrenome, numero_carteira_trabalho, categoria_habilitacao, salario, cpf, rg, data_nascimento, rank_, id_endereco)
OUTPUT INSERTED.ID
VALUES (@SEXO, @NOME, @SOBRENOME, @NUMERO_CARTEIRA_TRABALHO, @CATEGORIA_HABILITACAO, @SALARIO, @CPF, @RG, @DATA_NASCIMENTO, @RANK_, @ID_ENDERECO)";
command.Parameters.AddWithValue("@SEXO", guia.Sexo);
command.Parameters.AddWithValue("@NOME", guia.Nome);
command.Parameters.AddWithValue("@SOBRENOME", guia.Sobrenome);
command.Parameters.AddWithValue("@NUMERO_CARTEIRA_TRABALHO", guia.CarteiraTrabalho);
command.Parameters.AddWithValue("@CATEGORIA_HABILITACAO", guia.CategoriaHabilitacao);
command.Parameters.AddWithValue("@SALARIO", guia.Salario);
command.Parameters.AddWithValue("@CPF", guia.Cpf);
command.Parameters.AddWithValue("@RG", guia.Rg);
command.Parameters.AddWithValue("@DATA_NASCIMENTO", guia.DataNascimento);
command.Parameters.AddWithValue("@RANK_", guia.Rank);
command.Parameters.AddWithValue("@ID_ENDERECO", guia.Endereco.Id);
var result = command.ExecuteScalar();
int id = Convert.ToInt32(result.ToString());
return(id);
}
public int ContabilizarEstadosFiltradas(string search)
{
SqlCommand command = new Conexao().ObterConexao();
command.CommandText = @"SELECT COUNT(id) FROM idiomas WHERE ativo = 1 AND ((nome LIKE @SEARCH) OR (id LIKE @SEARCH))";
command.Parameters.AddWithValue("@SEARCH", search);
return(Convert.ToInt32(command.ExecuteScalar().ToString()));
}
public int ContabilizarCidadesFiltradas(string search)
{
SqlCommand command = new Conexao().ObterConexao();
command.CommandText = @"SELECT COUNT(c.id) FROM cidades c
JOIN estados e ON (e.id = c.id_estado)
WHERE c.ativo = 1 AND (c.nome LIKE @SEARCH OR e.nome LIKE @SEARCH)";
command.Parameters.AddWithValue("@SEARCH", search);
return(Convert.ToInt32(command.ExecuteScalar().ToString()));
}
public int Cadastrar(Idioma idioma)
{
SqlCommand command = new Conexao().ObterConexao();
command.CommandText = @"INSERT INTO idiomas (nome) OUTPUT INSERTED.ID VALUES (@NOME)";
command.Parameters.AddWithValue("@NOME", idioma.Nome);
int id = Convert.ToInt32(command.ExecuteScalar().ToString());
return(id);
}
public int ContabilizarEnderecosFiltrados(string search)
{
SqlCommand command = new Conexao().ObterConexao();
command.CommandText = @"SELECT COUNT(e.id) FROM enderecos e
JOIN cidades c ON (c.id = e.id_cidade )
JOIN estados es ON (es.id = c.id_estado )
WHERE e.ativo = 1 AND ((e.cep LIKE @SEARCH) OR (e.logradouro LIKE @SEARCH) OR (c.nome LIKE @SEARCH) )";
command.Parameters.AddWithValue("@SEARCH", search);
return(Convert.ToInt32(command.ExecuteScalar().ToString()));
}
public int ContabilizarCidadesFiltradas(string search)
{
SqlCommand command = new Conexao().ObterConexao();
command.CommandText = @"SELECT COUNT(hv.id)
FROM historico_de_viagens hv
INNER JOIN pacotes p ON (p.id = hv.id_pacote)
WHERE hv.ativo = 1 AND ((hv.id LIKE @SEARCH) OR (p.nome LIKE @SEARCH) OR (hv.data_ LIKE @SEARCH))";
command.Parameters.AddWithValue("@SEARCH", search);
return(Convert.ToInt32(command.ExecuteScalar().ToString()));
}
// public List<Pacote> ObterTodosPacotesPeloIdPacote(int idPacote)
// {
// List<Pacote> pacotes = new List<Pacote>();
// SqlCommand command = new Conexao().ObterConexao();
// command.CommandText = @"SELECT pt.id, p.id, p.nome, p.valor, id_endereco, pt.nome, FROM pontos_turisticos pt JOIN
// pacotes p ON(pt.id = p.id_pacote) WHERE pt.id_pacote = @ID_PACOTE";
// DataTable table = new DataTable();
// table.Load(command.ExecuteReader());
// foreach (DataRow line in table.Rows)
// {
// Pacote pacote = new Pacote()
// {
// Id = Convert.ToInt32(line["p.id"].ToString()),
// Nome = line["p.nome"].ToString()
// };
// pacotes.Add(pacote);
// }
// return pacotes;
// }
public int Cadastrar(PontoTuristico pontosturisticos)
{
SqlCommand command = new Conexao().ObterConexao();
command.CommandText = "INSERT INTO pontos_turisticos(id_endereco,nome)OUTPUT INSERTED.ID VALUES(@ID_ENDERECO,@NOME)";
command.Parameters.AddWithValue("@ID_ENDERECO", pontosturisticos.IdEndereco);
command.Parameters.AddWithValue("@NOME", pontosturisticos.Nome);
int id = Convert.ToInt32(command.ExecuteScalar().ToString());
return(id);
}
public int Cadastro(TuristaPacote turistaPacoteModel)
{
SqlCommand command = new Conexao().ObterConexao();
command.CommandText = @"INSERT INTO turistas_pacotes(id_pacote, id_turista, status_do_pedido, data_requisicao) OUTPUT INSERTED.ID VALUES (@IDPACOTE, @IDTURISTA, @STATUS, GETDATE())";
command.Parameters.AddWithValue("@IDPACOTE", turistaPacoteModel.IdPacote);
command.Parameters.AddWithValue("@IDTURISTA", turistaPacoteModel.IdTurista);
command.Parameters.AddWithValue("@STATUS", turistaPacoteModel.StatusPedido);
return(Convert.ToInt32(command.ExecuteScalar().ToString()));
}
public int Cadastrar(Cidade cidade)
{
SqlCommand command = new Conexao().ObterConexao();
command.CommandText = @"INSERT INTO cidades (nome, id_estado) OUTPUT INSERTED.ID VALUES(@NOME, @ID_ESTADO)";
command.Parameters.AddWithValue("@NOME", cidade.Nome);
command.Parameters.AddWithValue("@ID_ESTADO", cidade.IdEstado);
int id = Convert.ToInt32(command.ExecuteScalar().ToString());
return(id);
}
public int Cadastro(PacotePontoTuristico pacotePontoTuristico)
{
SqlCommand command = new Conexao().ObterConexao();
command.CommandText = @"INSERT INTO pacotes_pontos_turisticos (id_ponto_turistico, id_pacote) OUTPUT INSERTED.ID VALUES (@ID_PONTO_TURISTICO, @ID_PACOTE)";
command.Parameters.AddWithValue("@ID_PONTO_TURISTICO", pacotePontoTuristico.IdPontoTuristico);
command.Parameters.AddWithValue("@ID_PACOTE", pacotePontoTuristico.IdPacote);
int id = Convert.ToInt32(command.ExecuteScalar().ToString());
return(id);
}
public int ContabilizarViagensFiltradas(string search)
{
SqlCommand command = new Conexao().ObterConexao();
command.CommandText = @"SELECT COUNT(v.id)
FROM viagens v
INNER JOIN pacotes p ON (p.id = v.id_pacote)
INNER JOIN guias g ON (g.id = v.id_guia)
WHERE v.ativo = 1 AND ((v.id LIKE @SEARCH) OR (p.nome LIKE @SEARCH) OR (g.nome LIKE @SEARCH) OR (v.data_horario_saida LIKE @SEARCH) OR (v.data_horario_volta LIKE @SEARCH))";
command.Parameters.AddWithValue("@SEARCH", search);
return(Convert.ToInt32(command.ExecuteScalar().ToString()));
}
public int Cadastrar(Pacote pacote)
{
SqlCommand command = new Conexao().ObterConexao();
command.CommandText = "INSERT INTO pacotes(nome, valor, percentual_max_desconto) OUTPUT INSERTED.ID VALUES(@NOME, @VALOR, @PERCENTUAL_MAX_DESCONTO)";
command.Parameters.AddWithValue("@NOME", pacote.Nome);
command.Parameters.AddWithValue("@VALOR", pacote.Valor);
command.Parameters.AddWithValue("@PERCENTUAL_MAX_DESCONTO", pacote.PercentualMaximoDesconto);
int id = Convert.ToInt32(command.ExecuteScalar().ToString());
return(id);
}
public int ContabilizarPontosTuristicosFiltrados(string search)
{
SqlCommand command = new Conexao().ObterConexao();
command.CommandText = @"SELECT COUNT(pt.id) FROM pontos_turisticos pt
INNER JOIN enderecos e ON(e.id = pt.id_endereco)
INNER JOIN cidades c ON (c.id = e.id_cidade)
INNER JOIN estados es ON (es.id = c.id_estado)
WHERE pt.ativo = 1 AND ((pt.id LIKE @SEARCH) OR (pt.nome LIKE @SEARCH) OR (e.logradouro LIKE @SEARCH))";
command.Parameters.AddWithValue("@SEARCH", search);
return(Convert.ToInt32(command.ExecuteScalar().ToString()));
}
public int ContabilizarPacotesAguardandoFiltrado(string search)
{
SqlCommand command = new Conexao().ObterConexao();
command.CommandText = @"SELECT COUNT(tp.id)
FROM turistas_pacotes tp
INNER JOIN turistas t ON (t.id = tp.id_turista)
INNER JOIN pacotes p ON (p.id = tp.id_pacote)
WHERE t.ativo = 1 AND (tp.id LIKE @SEARCH) OR (t.nome LIKE @SEARCH) OR (p.nome LIKE @SEARCH) OR (p.valor LIKE @SEARCH) OR (tp.status_do_pedido LIKE @SEARCH)";
command.Parameters.AddWithValue("@SEARCH", search);
return(Convert.ToInt32(command.ExecuteScalar().ToString()));
}
public int Cadastrar(HistoricoViagem historicoViagem)
{
SqlCommand command = new Conexao().ObterConexao();
command.CommandText = @"INSERT INTO historico_de_viagens (data_, id_pacote)
OUTPUT INSERTED.ID VALUES(@DATA_, @ID_PACOTE)";
command.Parameters.AddWithValue("@DATA_", historicoViagem.Data);
command.Parameters.AddWithValue("@ID_PACOTE", historicoViagem.IdPacote);
int id = Convert.ToInt32(command.ExecuteScalar().ToString());
return(id);
}
public int ContabilizarGuiasFiltrados(string search)
{
SqlCommand command = new Conexao().ObterConexao();
command.CommandText = @"SELECT COUNT(g.id)
FROM guias g
JOIN enderecos e ON (e.id = g.id_endereco)
JOIN cidades c ON (c.id = e.id_cidade)
JOIN estados es ON (es.id = c.id_estado)
WHERE g.ativo = 1 AND ((g.nome LIKE @SEARCH) OR (g.sobrenome LIKE @SEARCH) OR (g.cpf LIKE @SEARCH))";
command.Parameters.AddWithValue("@SEARCH", search);
return(Convert.ToInt32(command.ExecuteScalar().ToString()));
}
public int Cadastro(ViagemTurista viagemTurista)
{
SqlCommand command = new Conexao().ObterConexao();
command.CommandText = @"INSERT INTO viagens_turistas (valor, id_turista, id_viagem) OUTPUT INSERTED.ID VALUES (@VALOR, @ID_TURISTA, @ID_VIAGEM)";
command.Parameters.AddWithValue("@VALOR", viagemTurista.Valor);
command.Parameters.AddWithValue("@ID_TURISTA", viagemTurista.IdTurista);
command.Parameters.AddWithValue("@ID_VIAGEM", viagemTurista.IdViagem);
int id = Convert.ToInt32(command.ExecuteScalar().ToString());
return(id);
}
public int Cadastrar(Viagem viagem)
{
SqlCommand command = new Conexao().ObterConexao();
command.CommandText = @"INSERT INTO viagens (id_pacote, id_guia, data_horario_saida, data_horario_volta)
OUTPUT INSERTED.ID VALUES (@ID_PACOTES, @ID_GUIA, @DATA_HORARIO_SAIDA, @DATA_HORARIO_VOLTA)";
command.Parameters.AddWithValue("@ID_PACOTES", viagem.IdPacote);
command.Parameters.AddWithValue("@ID_GUIA", viagem.IdGuia);
command.Parameters.AddWithValue("@DATA_HORARIO_SAIDA", viagem.DataHorarioSaida);
command.Parameters.AddWithValue("@DATA_HORARIO_VOLTA", viagem.DataHorarioVolta);
int id = Convert.ToInt32(command.ExecuteScalar().ToString());
return(id);
}
public int Cadastrar(Login login)
{
SqlCommand command = new Conexao().ObterConexao();
command.CommandText = @"INSERT INTO logins (email, senha, privilegio)
OUTPUT INSERTED.ID VALUES (@EMAIL, @SENHA, @PRIVILEGIO)";
command.Parameters.AddWithValue("@EMAIL", login.Email);
command.Parameters.AddWithValue("@SENHA", login.Senha);
command.Parameters.AddWithValue("@PRIVILEGIO", "Usuário");
int id = Convert.ToInt32(command.ExecuteScalar().ToString());
return(id);
}
public int Cadastrar(Turista turista)
{
SqlCommand command = new Conexao().ObterConexao();
command.CommandText = @"INSERT INTO turistas (id_login, nome, sobrenome, cpf, rg, data_nascimento, sexo)
OUTPUT INSERTED.ID VALUES (@IDLOGIN, @NOME, @SOBRENOME, @CPF, @RG, @DATA_NASCIMENTO, @SEXO)";
command.Parameters.AddWithValue("@IDLOGIN", turista.IdLogin);
command.Parameters.AddWithValue("@NOME", turista.Nome);
command.Parameters.AddWithValue("@SOBRENOME", turista.Sobrenome);
command.Parameters.AddWithValue("@CPF", turista.Cpf);
command.Parameters.AddWithValue("@RG", turista.Rg);
command.Parameters.AddWithValue("@DATA_NASCIMENTO", turista.DataNascimento);
command.Parameters.AddWithValue("@SEXO", turista.Sexo);
int id = Convert.ToInt32(command.ExecuteScalar().ToString());
return(id);
}
public int Cadastrar(Endereco endereco)
{
SqlCommand command = new Conexao().ObterConexao();
command.CommandText = $@"INSERT INTO enderecos (id_cidade, cep, logradouro, numero, complemento, referencia) OUTPUT INSERTED.ID
VALUES (@ID_CIDADE, @CEP, @LOGRADOURO, @NUMERO, @COMPLEMENTO, @REFERENCIA)";
command.Parameters.AddWithValue("@ID_CIDADE", DBValue(endereco.Cidade.Id));
command.Parameters.AddWithValue("@CEP", DBValue(endereco.Cep));
command.Parameters.AddWithValue("@LOGRADOURO", DBValue(endereco.Logradouro));
command.Parameters.AddWithValue("@NUMERO", DBValue(endereco.Numero));
command.Parameters.AddWithValue("@COMPLEMENTO", DBValue(endereco.Complemento));
command.Parameters.AddWithValue("@REFERENCIA", DBValue(endereco.Referencia));
var newId = command.ExecuteScalar().ToString();
return(Convert.ToInt32(newId));
}