private void cmb_quarto_TextChanged(object sender, EventArgs e)
{
NpgsqlConnection pgsqlConnection = null;
try
{
Cls_Conexao objconexao = new Cls_Conexao();
pgsqlConnection = objconexao.getConexao();
pgsqlConnection.Open();
string diaria = "select diaria from quartos where quarto = '" + cmb_quarto.Text + "';";
NpgsqlCommand cmd = new NpgsqlCommand(diaria, pgsqlConnection);
NpgsqlDataReader diariashow = cmd.ExecuteReader();
diariashow.Read();
msk_valor.Text = diariashow["diaria"].ToString();
diariashow.Close();
}
finally
{
pgsqlConnection.Close();
}
}
public bool camas()
{
NpgsqlConnection pgsqlConnection = null;
try
{
Cls_Conexao objconexao = new Cls_Conexao();
pgsqlConnection = objconexao.getConexao();
pgsqlConnection.Open();
string pesquisar;
pesquisar = "select camasolteiro, camacasal from quartos where quarto = '" + this.criterio + "';";
NpgsqlCommand cmd = new NpgsqlCommand(pesquisar, pgsqlConnection);
NpgsqlDataReader pesquisa = cmd.ExecuteReader();
if (pesquisa.Read())
{
this.cama_solteiro = pesquisa["camasolteiro"].ToString();
this.cama_casal = pesquisa["camacasal"].ToString();
return(true);
}
else
{
return(false);
}
}
finally
{
pgsqlConnection.Close();
}
}
public Frm_Checkin(string nome)
{
InitializeComponent();
nomefunc = nome;
NpgsqlConnection pgsqlConnection = null;
try
{
Cls_Conexao objconexao = new Cls_Conexao();
pgsqlConnection = objconexao.getConexao();
pgsqlConnection.Open();
string combobox = "select quarto from quartos where status = 'Disponível' order by quarto;";
NpgsqlCommand cmd = new NpgsqlCommand(combobox, pgsqlConnection);
NpgsqlDataReader comboboxshow = cmd.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(comboboxshow);
cmb_quarto.DisplayMember = "quarto";
cmb_quarto.DataSource = dt;
}
finally
{
pgsqlConnection.Close();
}
dgv_pesquisa();
}
public void comboboxAtt()
{
NpgsqlConnection pgsqlConnection = null;
try
{
Cls_Conexao objconexao = new Cls_Conexao();
pgsqlConnection = objconexao.getConexao();
pgsqlConnection.Open();
string combobox = "select usuarioid from usuario where ativo = 'SIM' order by usuarioid;";
NpgsqlCommand cmd = new NpgsqlCommand(combobox, pgsqlConnection);
NpgsqlDataReader comboboxshow = cmd.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(comboboxshow);
cmb_id.DisplayMember = "usuarioid";
cmb_id.DataSource = dt;
}
finally
{
pgsqlConnection.Close();
}
}
public bool cadastroItens()
{
NpgsqlConnection pgsqlConnection = null;
try
{
Cls_Conexao objconexao = new Cls_Conexao();
pgsqlConnection = objconexao.getConexao();
pgsqlConnection.Open();
string querry = "select item from itens where item = '" + this.nome + "';";
NpgsqlCommand cmd = new NpgsqlCommand(querry, pgsqlConnection);
NpgsqlDataReader itemsearch = cmd.ExecuteReader();
if (itemsearch.Read())
{
MessageBox.Show("Já existe um item cadastrado com este nome!", "Aviso", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(false);
}
else
{
itemsearch.Close();
querry = "INSERT INTO itens(item, valor, quantidade, status) VALUES ('" + this.nome + "', '" + this.valor + "', '" + this.quantidade + "', 'Disponivel');";
cmd = new NpgsqlCommand(querry, pgsqlConnection);
NpgsqlDataReader cadItem = cmd.ExecuteReader();
cadItem.Close();
querry = "select itemid from itens where item = '" + this.nome + "';";
cmd = new NpgsqlCommand(querry, pgsqlConnection);
NpgsqlDataReader searchId = cmd.ExecuteReader();
searchId.Read();
string IdSearched = searchId["itemid"].ToString();
searchId.Close();
querry = "INSERT INTO fornecedor(nome_fornecedor, valor_item, itemfk) VALUES ('" + this.nome2 + "', '" + this.valor2 + "', '" + IdSearched + "');";
cmd = new NpgsqlCommand(querry, pgsqlConnection);
NpgsqlDataReader cadFornecedor = cmd.ExecuteReader();
return(true);
}
}
finally
{
pgsqlConnection.Close();
}
}
public bool excluiQuarto()
{
NpgsqlConnection pgsqlConnection = null;
try
{
Cls_Conexao objconexao = new Cls_Conexao();
pgsqlConnection = objconexao.getConexao();
pgsqlConnection.Open();
string querry = "select quarto, tipo, camasolteiro, camacasal, diaria from quartos where quarto = '" + this.criterio + "';";
NpgsqlCommand cmd = new NpgsqlCommand(querry, pgsqlConnection);
NpgsqlDataReader quartoresult = cmd.ExecuteReader();
if (quartoresult.Read())
{
quartoresult.Close();
querry = "select count(status) from quartos where quarto = '" + this.criterio + "' and status = 'Ocupado';";
cmd = new NpgsqlCommand(querry, pgsqlConnection);
NpgsqlDataReader quartostatus = cmd.ExecuteReader();
quartostatus.Read();
int quarto_status = Convert.ToInt16(quartostatus["count"].ToString());
quartostatus.Close();
if (quarto_status == 0)
{
querry = "DELETE FROM quartos WHERE quarto = '" + this.criterio + "';";
cmd = new NpgsqlCommand(querry, pgsqlConnection);
NpgsqlDataReader excluir = cmd.ExecuteReader();
return(true);
}
else
{
MessageBox.Show("Este quarto está ocupado!", "Aviso", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(false);
}
}
else
{
MessageBox.Show("Este quarto não existe!", "Aviso", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(false);
}
}
finally
{
pgsqlConnection.Close();
}
}
public void attComboBox()
{
NpgsqlConnection pgsqlConnection = null;
try
{
Cls_Conexao objconexao = new Cls_Conexao();
pgsqlConnection = objconexao.getConexao();
pgsqlConnection.Open();
string combobox = "select quarto from quartos where status = 'Ocupado'";
NpgsqlCommand cmd = new NpgsqlCommand(combobox, pgsqlConnection);
NpgsqlDataReader comboboxshow = cmd.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(comboboxshow);
cmb_quarto.DisplayMember = "quarto";
cmb_quarto.DataSource = dt;
}
finally
{
pgsqlConnection.Close();
}
}
public bool atualizaCartao()
{
NpgsqlConnection pgsqlConnection = null;
try
{
Cls_Conexao objconexao = new Cls_Conexao();
pgsqlConnection = objconexao.getConexao();
pgsqlConnection.Open();
string atualiza;
atualiza = "UPDATE cartao SET numerocartao= '" + this.numero + "', nome= '" + this.nome + "', validade= '" + this.validade + "', codigo= '" + this.codigo + "', bandeira= '" + this.bandeira + "' where usuariofk = '" + this.criterio + "';";
NpgsqlCommand cmd = new NpgsqlCommand(atualiza, pgsqlConnection);
NpgsqlDataReader atualizar = cmd.ExecuteReader();
return(true);
}
finally
{
pgsqlConnection.Close();
}
}
public bool checkin(string id)
{
NpgsqlConnection pgsqlConnection = null;
try
{
Cls_Conexao objconexao = new Cls_Conexao();
pgsqlConnection = objconexao.getConexao();
pgsqlConnection.Open();
string checkin;
checkin = "INSERT INTO reservas(reservaid, nomefunc, nomecli, entrada, saida, valor, quartofk, status, usuariofk) VALUES((select count(reservaid) + 1 from reservas), '" + this.nomefunc + "', '" + this.nome + "', '" + this.entrada + "', '" + this.saida + "', " + this.valor + ", '" + this.quarto + "','Em andamento', '" + id + "');";
NpgsqlCommand cmd = new NpgsqlCommand(checkin, pgsqlConnection);
NpgsqlDataReader reserva = cmd.ExecuteReader();
reserva.Close();
checkin = "UPDATE quartos SET status='Ocupado', reservafk=(select reservaid from reservas where quartofk = '" + this.quarto + "' and status = 'Em andamento') WHERE quarto = '" + this.quarto + "';";
cmd = new NpgsqlCommand(checkin, pgsqlConnection);
NpgsqlDataReader reservaquarto = cmd.ExecuteReader();
return(true);
}
finally
{
pgsqlConnection.Close();
}
}
public bool pesquisar()
{
NpgsqlConnection pgsqlConnection = null;
try
{
Cls_Conexao objconexao = new Cls_Conexao();
pgsqlConnection = objconexao.getConexao();
pgsqlConnection.Open();
string pesquisar;
pesquisar = "select nome, rg from usuario where usuarioid = '" + this.criterio + "' and ativo = 'SIM' LIMIT 1;";
NpgsqlCommand cmd = new NpgsqlCommand(pesquisar, pgsqlConnection);
NpgsqlDataReader pesquisa = cmd.ExecuteReader();
if (pesquisa.Read())
{
this.nome = pesquisa["nome"].ToString();
this.rg = pesquisa["rg"].ToString();
return(true);
}
else
{
return(false);
}
}
finally
{
pgsqlConnection.Close();
}
}
public bool pesquisaItem()
{
NpgsqlConnection pgsqlConnection = null;
try
{
Cls_Conexao objconexao = new Cls_Conexao();
pgsqlConnection = objconexao.getConexao();
pgsqlConnection.Open();
string querry = "select item, valor, quantidade, nome_fornecedor, valor_item from itens, fornecedor where itemid = itemfk and item = '" + this.criterio + "';";
NpgsqlCommand cmd = new NpgsqlCommand(querry, pgsqlConnection);
NpgsqlDataReader itemresult = cmd.ExecuteReader();
itemresult.Read();
this.nome = itemresult["item"].ToString();
this.nome2 = itemresult["nome_fornecedor"].ToString();
this.quantidade = Convert.ToInt16(itemresult["quantidade"].ToString());
this.valor = itemresult["valor"].ToString();
this.valor2 = itemresult["valor_item"].ToString();
return(true);
}
finally
{
pgsqlConnection.Close();
}
}
public void dgv_pesquisa()
{
try
{
Cls_Conexao objconexao = new Cls_Conexao();
pgsqlConnection = objconexao.getConexao();
pgsqlConnection.Open();
string datagrid = "select reservaid, nomefunc, nomecli, entrada, saida, valor, quartofk, status from reservas where status = 'Em andamento' order by reservaid;";
NpgsqlCommand cmd = new NpgsqlCommand(datagrid, pgsqlConnection);
NpgsqlDataReader dgv = cmd.ExecuteReader();
if (dgv.HasRows)
{
DataTable dt = new DataTable();
dt.Load(dgv);
dgv_reservas.DataSource = dt;
}
else
{
dgv_reservas.DataSource = null;
}
}
finally
{
pgsqlConnection.Close();
}
}
public void atualizadgv()
{
NpgsqlConnection pgsqlconnection = null;
try
{
Cls_Conexao objconexao = new Cls_Conexao();
pgsqlconnection = objconexao.getConexao();
pgsqlconnection.Open();
string querry = "select item, nome_fornecedor, valor, quantidade, status from itens, fornecedor where status = 'Disponivel' and itemfk = itemid order by itemid;";
NpgsqlCommand cmd = new NpgsqlCommand(querry, pgsqlconnection);
NpgsqlDataReader dgv = cmd.ExecuteReader();
if (dgv.HasRows)
{
DataTable dt = new DataTable();
dt.Load(dgv);
dgv_itens.DataSource = dt;
}
}
finally
{
pgsqlconnection.Close();
}
}
private void cmb_quarto_SelectedIndexChanged(object sender, EventArgs e)
{
NpgsqlConnection pgsqlConnection = null;
try
{
Cls_Conexao objconexao = new Cls_Conexao();
pgsqlConnection = objconexao.getConexao();
pgsqlConnection.Open();
string combobox = "select nome from usuario, reservas where usuariofk = usuarioid and status = 'Em andamento' and quartofk = '" + cmb_quarto.Text + "';";
NpgsqlCommand cmd = new NpgsqlCommand(combobox, pgsqlConnection);
NpgsqlDataReader comboboxshow = cmd.ExecuteReader();
comboboxshow.Read();
txt_nome.Text = comboboxshow["nome"].ToString();
}
finally
{
pgsqlConnection.Close();
}
}
public void atualizadtg()
{
NpgsqlConnection pgsqlConnection = null;
try
{
Cls_Conexao objconexao = new Cls_Conexao();
pgsqlConnection = objconexao.getConexao();
pgsqlConnection.Open();
string datagrid = "SELECT usuariofk, numerocartao, nome, validade, codigo, bandeira FROM cartao order by usuariofk;";
NpgsqlCommand cmd = new NpgsqlCommand(datagrid, pgsqlConnection);
NpgsqlDataReader dgv = cmd.ExecuteReader();
if (dgv.HasRows)
{
DataTable dt = new DataTable();
dt.Load(dgv);
dgv_cartoes.DataSource = dt;
}
}
finally
{
pgsqlConnection.Close();
}
}
public void atualizadtg()
{
NpgsqlConnection pgsqlConnection = null;
try
{
Cls_Conexao objconexao = new Cls_Conexao();
pgsqlConnection = objconexao.getConexao();
pgsqlConnection.Open();
string datagrid = "SELECT quarto, tipo, camasolteiro, camacasal, status, diaria from quartos order by quarto;";
NpgsqlCommand cmd = new NpgsqlCommand(datagrid, pgsqlConnection);
NpgsqlDataReader dgv = cmd.ExecuteReader();
if (dgv.HasRows)
{
DataTable dt = new DataTable();
dt.Load(dgv);
dgv_quartos.DataSource = dt;
}
}
finally
{
pgsqlConnection.Close();
}
}
private void cmb_itens_SelectedIndexChanged(object sender, EventArgs e)
{
NpgsqlConnection pgsqlConnection = null;
try
{
Cls_Conexao objconexao = new Cls_Conexao();
pgsqlConnection = objconexao.getConexao();
pgsqlConnection.Open();
string combobox = "select valor from itens where item = '" + cmb_itens.Text + "';";
NpgsqlCommand cmd = new NpgsqlCommand(combobox, pgsqlConnection);
NpgsqlDataReader comboboxshow = cmd.ExecuteReader();
comboboxshow.Read();
txt_valor.Text = comboboxshow["valor"].ToString();
}
finally
{
pgsqlConnection.Close();
}
}
public void attComboBox()
{
NpgsqlConnection pgsqlConnection = null;
try
{
Cls_Conexao objconexao = new Cls_Conexao();
pgsqlConnection = objconexao.getConexao();
pgsqlConnection.Open();
string combobox = "SELECT usuariofk FROM cartao order by usuariofk;";
NpgsqlCommand cmd = new NpgsqlCommand(combobox, pgsqlConnection);
NpgsqlDataReader comboboxshow = cmd.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(comboboxshow);
cmb_pesquisa_cartao.DisplayMember = "usuariofk";
cmb_pesquisa_cartao.DataSource = dt;
}
finally
{
pgsqlConnection.Close();
}
}
private void cmb_itens_SelectedIndexChanged(object sender, EventArgs e)
{
NpgsqlConnection pgsqlConnection = null;
try
{
Cls_Conexao objconexao = new Cls_Conexao();
pgsqlConnection = objconexao.getConexao();
pgsqlConnection.Open();
string combobox = "select nome_fornecedor, quantidade, valor_item from itens, fornecedor where itemfk = itemid and item = '" + cmb_itens.Text + "';";
NpgsqlCommand cmd = new NpgsqlCommand(combobox, pgsqlConnection);
NpgsqlDataReader comboboxshow = cmd.ExecuteReader();
comboboxshow.Read();
txt_fornecedor.Text = comboboxshow["nome_fornecedor"].ToString();
txt_qnt_disponivel.Text = comboboxshow["quantidade"].ToString();
txt_valor.Text = comboboxshow["valor_item"].ToString();
}
finally
{
pgsqlConnection.Close();
}
}
private void atualizadgv()
{
NpgsqlConnection pgsqlConnection = null;
try
{
Cls_Conexao objconexao = new Cls_Conexao();
pgsqlConnection = objconexao.getConexao();
pgsqlConnection.Open();
string datagrid = "select usuarioid, nome, rg, telefone, rua, numero, bairro, cidade, estado, cep, login.ativo, login, senha, tipo from usuario, login where usuarioid = fk_usuario order by usuarioid;";
NpgsqlCommand cmd = new NpgsqlCommand(datagrid, pgsqlConnection);
NpgsqlDataReader dgv = cmd.ExecuteReader();
if (dgv.HasRows)
{
DataTable dt = new DataTable();
dt.Load(dgv);
dgv_usuarios.DataSource = dt;
}
else
{
dgv_usuarios.DataSource = null;
}
}
finally
{
pgsqlConnection.Close();
}
}
public void itens(string criterio)
{
NpgsqlConnection pgsqlConnection = null;
try
{
Cls_Conexao objconexao = new Cls_Conexao();
pgsqlConnection = objconexao.getConexao();
pgsqlConnection.Open();
string pesquisar;
int pesquisa = Convert.ToInt16(criterio);
pesquisar = "select item, valor, quantidade, data from consumos where reservafk = '" + pesquisa + "';";
NpgsqlCommand cmd = new NpgsqlCommand(pesquisar, pgsqlConnection);
NpgsqlDataReader dgv = cmd.ExecuteReader();
if (dgv.HasRows)
{
DataTable dt = new DataTable();
dt.Load(dgv);
dgv_itens.DataSource = dt;
}
}
finally
{
pgsqlConnection.Close();
}
}
public void atualizaComboBox()
{
NpgsqlConnection pgsqlConnection = null;
try
{
Cls_Conexao objconexao = new Cls_Conexao();
pgsqlConnection = objconexao.getConexao();
pgsqlConnection.Open();
string combobox = "select item from itens where status = 'Disponivel' order by item;";
NpgsqlCommand cmd = new NpgsqlCommand(combobox, pgsqlConnection);
NpgsqlDataReader comboboxshow = cmd.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(comboboxshow);
cmb_itens.DisplayMember = "item";
cmb_itens.DataSource = dt;
}
finally
{
pgsqlConnection.Close();
}
}
public Frm_Dashboard(string name, string tipo)
{
InitializeComponent();
string[] nome = name.Split(' ');
lbl_nome.Text = nome[0];
lbl_usuario.Text = "Cargo: " + tipo;
NpgsqlConnection pgsqlConnection = null;
try
{
Cls_Conexao objconexao = new Cls_Conexao();
pgsqlConnection = objconexao.getConexao();
pgsqlConnection.Open();
string querry = "select count (status) from reservas where status = 'Em andamento';";
NpgsqlCommand cmd = new NpgsqlCommand(querry, pgsqlConnection);
NpgsqlDataReader reserva = cmd.ExecuteReader();
reserva.Read();
lbl_reservas.Text = $"Reservas: {reserva["count"].ToString()}";
reserva.Close();
querry = "select count (quarto) from quartos;";
cmd = new NpgsqlCommand(querry, pgsqlConnection);
NpgsqlDataReader total_quartos = cmd.ExecuteReader();
total_quartos.Read();
lbl_quartostotal.Text = $"Total Quartos: {total_quartos["count"].ToString()}";
total_quartos.Close();
querry = "select count (quarto) from quartos where status = 'Disponível';";
cmd = new NpgsqlCommand(querry, pgsqlConnection);
NpgsqlDataReader quartos_disponivel = cmd.ExecuteReader();
quartos_disponivel.Read();
lbl_quartosdisponiveis.Text = $"Quartos disponíveis: {quartos_disponivel["count"].ToString()}";
quartos_disponivel.Close();
}
finally
{
pgsqlConnection.Close();
}
}
public Frm_AtualizaUsuarios(string tipoo)
{
tipo = tipoo;
InitializeComponent();
cmb_tipo.Items.Clear();
cmb_ativo.Items.Clear();
cmb_criterio.Items.Clear();
if (tipoo == "Cargo: Admin")
{
cmb_tipo.Items.Add("Func");
cmb_tipo.Items.Add("Admin");
cmb_tipo.Items.Add("Cliente");
}
else
{
cmb_tipo.Items.Add("Cliente");
cmb_tipo.SelectedItem = "Cliente";
}
cmb_ativo.Items.Add("SIM");
cmb_ativo.Items.Add("NAO");
cmb_criterio.Items.Add("Id");
cmb_criterio.Items.Add("RG");
cmb_criterio.Items.Add("Telefone");
cmb_criterio.SelectedItem = "Id";
NpgsqlConnection pgsqlConnection = null;
try
{
Cls_Conexao objconexao = new Cls_Conexao();
pgsqlConnection = objconexao.getConexao();
pgsqlConnection.Open();
string datagrid = "select usuarioid, nome, rg, telefone, rua, numero, bairro, cidade, estado, cep, login.ativo, login, senha, tipo from usuario, login where usuarioid = fk_usuario order by usuarioid;";
NpgsqlCommand cmd = new NpgsqlCommand(datagrid, pgsqlConnection);
NpgsqlDataReader dgv = cmd.ExecuteReader();
if (dgv.HasRows)
{
DataTable dt = new DataTable();
dt.Load(dgv);
dgv_usuarios.DataSource = dt;
}
}
finally
{
pgsqlConnection.Close();
}
}
public bool checkin(string id)
{
NpgsqlConnection pgsqlConnection = null;
try
{
Cls_Conexao objconexao = new Cls_Conexao();
pgsqlConnection = objconexao.getConexao();
pgsqlConnection.Open();
string querry;
querry = "select ativo from usuario where usuarioid = '" + id + "';";
NpgsqlCommand cmd = new NpgsqlCommand(querry, pgsqlConnection);
NpgsqlDataReader ativo = cmd.ExecuteReader();
ativo.Read();
string verificaAtivo = ativo["ativo"].ToString();
ativo.Close();
if (verificaAtivo == "SIM")
{
string checkin;
checkin = "INSERT INTO reservas(reservaid, nomefunc, nomecli, entrada, saida, valor, quartofk, status, usuariofk) VALUES((select count(reservaid) + 1 from reservas), '" + this.nomefunc + "', '" + this.nome + "', '" + this.entrada + "', '" + this.saida + "', " + this.valor + ", '" + this.quarto + "','Em andamento', '" + id + "');";
cmd = new NpgsqlCommand(checkin, pgsqlConnection);
NpgsqlDataReader reserva = cmd.ExecuteReader();
reserva.Close();
checkin = "UPDATE quartos SET status='Ocupado', reservafk=(select reservaid from reservas where quartofk = '" + this.quarto + "' and status = 'Em andamento') WHERE quarto = '" + this.quarto + "';";
cmd = new NpgsqlCommand(checkin, pgsqlConnection);
NpgsqlDataReader reservaquarto = cmd.ExecuteReader();
return(true);
}
else
{
MessageBox.Show("Usuário desativado!", "Aviso", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(false);
}
}
finally
{
pgsqlConnection.Close();
}
}
private void btn_atualiza_reserva_Click(object sender, EventArgs e)
{
NpgsqlConnection pgsqlConnection = null;
try
{
Cls_Conexao objconexao = new Cls_Conexao();
pgsqlConnection = objconexao.getConexao();
pgsqlConnection.Open();
string datagrid;
NpgsqlCommand cmd;
if (cmb_historico.Text == "Consumo")
{
datagrid = "select item, valor, quantidade, data from consumos where reservafk = '" + cmb_criterio.Text + "';";
cmd = new NpgsqlCommand(datagrid, pgsqlConnection);
NpgsqlDataReader dgv = cmd.ExecuteReader();
if (dgv.HasRows)
{
DataTable dt = new DataTable();
dt.Load(dgv);
dgv_historico.DataSource = dt;
}
else
{
dgv_historico.DataSource = null;
}
}
else
{
datagrid = "select reservaid, nomefunc, nomecli, entrada, saida, valor, quartofk, status from reservas where usuariofk = '" + cmb_criterio.Text + "';";
cmd = new NpgsqlCommand(datagrid, pgsqlConnection);
NpgsqlDataReader dgv = cmd.ExecuteReader();
if (dgv.HasRows)
{
DataTable dt = new DataTable();
dt.Load(dgv);
dgv_historico.DataSource = dt;
}
else
{
dgv_historico.DataSource = null;
}
}
}
finally
{
pgsqlConnection.Close();
}
}
public bool pesquisarCartao()
{
NpgsqlConnection pgsqlConnection = null;
try
{
Cls_Conexao objconexao = new Cls_Conexao();
pgsqlConnection = objconexao.getConexao();
pgsqlConnection.Open();
string pesquisar;
pesquisar = "select numerocartao, nome, validade, codigo, bandeira from cartao where usuariofk = '" + this.criterio + "';";
NpgsqlCommand cmd = new NpgsqlCommand(pesquisar, pgsqlConnection);
NpgsqlDataReader pesquisa = cmd.ExecuteReader();
if (pesquisa.Read())
{
this.numero = pesquisa["numerocartao"].ToString();
this.nome = pesquisa["nome"].ToString();
this.validade = pesquisa["validade"].ToString();
this.codigo = pesquisa["codigo"].ToString();
this.bandeira = pesquisa["bandeira"].ToString();
pesquisa.Close();
pesquisar = "select nome from usuario where usuarioid = '" + this.criterio + "';";
cmd = new NpgsqlCommand(pesquisar, pgsqlConnection);
NpgsqlDataReader pesquisa2 = cmd.ExecuteReader();
pesquisa2.Read();
this.nome2 = pesquisa2["nome"].ToString();
pesquisa2.Close();
return(true);
}
else
{
return(false);
}
}
finally
{
pgsqlConnection.Close();
}
}
public bool valortotal(int totalDias)
{
NpgsqlConnection pgsqlConnection = null;
try
{
Cls_Conexao objconexao = new Cls_Conexao();
pgsqlConnection = objconexao.getConexao();
pgsqlConnection.Open();
string pesquisar;
double valor_reserva = 0;
double valor_itens = 0;
pesquisar = "select diaria from quartos where reservafk = '" + this.id + "';";
NpgsqlCommand cmd = new NpgsqlCommand(pesquisar, pgsqlConnection);
NpgsqlDataReader pesquisa = cmd.ExecuteReader();
if (pesquisa.Read())
{
valor_reserva = Convert.ToDouble(pesquisa["diaria"].ToString());
}
pesquisa.Close();
valor_reserva *= totalDias;
pesquisar = "select sum(valor * quantidade) from consumos where reservafk = '" + this.id + "';";
cmd = new NpgsqlCommand(pesquisar, pgsqlConnection);
pesquisa = cmd.ExecuteReader();
if (pesquisa.Read())
{
if (pesquisa["sum"].ToString() != "")
{
valor_itens = Convert.ToDouble(pesquisa["sum"].ToString());
}
}
this.total = valor_reserva + valor_itens;
return(true);
}
finally
{
pgsqlConnection.Close();
}
}
public bool excluiCartao()
{
NpgsqlConnection pgsqlConnection = null;
try
{
Cls_Conexao objconexao = new Cls_Conexao();
pgsqlConnection = objconexao.getConexao();
pgsqlConnection.Open();
string querry = "SELECT cartaoid, numerocartao, nome, validade, codigo, bandeira, usuariofk FROM cartao where usuariofk = '" + this.criterio + "';;";
NpgsqlCommand cmd = new NpgsqlCommand(querry, pgsqlConnection);
NpgsqlDataReader cartaoresult = cmd.ExecuteReader();
if (cartaoresult.Read())
{
DialogResult Dialogexcluir = new DialogResult();
Dialogexcluir = MessageBox.Show($"Excluir cartão nº: {cartaoresult["numerocartao"].ToString()}?", "Aviso", MessageBoxButtons.YesNo, MessageBoxIcon.Warning);
if (Dialogexcluir == DialogResult.Yes)
{
cartaoresult.Close();
querry = "DELETE FROM cartao WHERE usuariofk = '" + this.criterio + "';";
cmd = new NpgsqlCommand(querry, pgsqlConnection);
NpgsqlDataReader excluir = cmd.ExecuteReader();
return(true);
}
else
{
MessageBox.Show("O cartão não foi excluído", "Aviso", MessageBoxButtons.OK, MessageBoxIcon.Information);
return(false);
}
}
else
{
MessageBox.Show("Não há cartão cadastrado para este usuário!", "Aviso", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(false);
}
}
finally
{
pgsqlConnection.Close();
}
}
private void btn_pesquisa_Click(object sender, EventArgs e)
{
NpgsqlConnection pgsqlConnection = null;
try
{
Cls_Conexao objconexao = new Cls_Conexao();
pgsqlConnection = objconexao.getConexao();
pgsqlConnection.Open();
string querry = "select count (status) from reservas where status = 'Em andamento';";
NpgsqlCommand cmd = new NpgsqlCommand(querry, pgsqlConnection);
NpgsqlDataReader reserva = cmd.ExecuteReader();
reserva.Read();
lbl_reservas.Text = $"Reservas: {reserva["count"].ToString()}";
reserva.Close();
querry = "select count (quarto) from quartos;";
cmd = new NpgsqlCommand(querry, pgsqlConnection);
NpgsqlDataReader total_quartos = cmd.ExecuteReader();
total_quartos.Read();
lbl_quartostotal.Text = $"Total Quartos: {total_quartos["count"].ToString()}";
total_quartos.Close();
querry = "select count (quarto) from quartos where status = 'Disponível';";
cmd = new NpgsqlCommand(querry, pgsqlConnection);
NpgsqlDataReader quartos_disponivel = cmd.ExecuteReader();
quartos_disponivel.Read();
lbl_quartosdisponiveis.Text = $"Quartos disponíveis: {quartos_disponivel["count"].ToString()}";
quartos_disponivel.Close();
}
finally
{
pgsqlConnection.Close();
}
}
