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;
}
}
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 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 void atualizadgv()
{
NpgsqlConnection pgsqlconnection = null;
try
{
Cls_Conexao objconexao = new Cls_Conexao();
pgsqlconnection = objconexao.getConexao();
pgsqlconnection.Open();
string querry = "select item, nome_fornecedor, valor_item, quantidade, status from itens, fornecedor where 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_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 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 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 void ComboBoxQuarto()
{
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();
}
}
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();
}
}
public void atualizaComboBox()
{
NpgsqlConnection pgsqlConnection = null;
try
{
Cls_Conexao objconexao = new Cls_Conexao();
pgsqlConnection = objconexao.getConexao();
pgsqlConnection.Open();
string combobox = "select item from itens 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 void attComboBox()
{
NpgsqlConnection pgsqlConnection = null;
try
{
Cls_Conexao objconexao = new Cls_Conexao();
pgsqlConnection = objconexao.getConexao();
pgsqlConnection.Open();
string combobox = "SELECT usuarioid FROM usuario order by usuarioid;";
NpgsqlCommand cmd = new NpgsqlCommand(combobox, pgsqlConnection);
NpgsqlDataReader comboboxshow = cmd.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(comboboxshow);
cmb_pesquisa_cartao.DisplayMember = "usuarioid";
cmb_pesquisa_cartao.DataSource = dt;
}
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 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 bool pesquisaQuarto()
{
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())
{
this.nome = quartoresult["quarto"].ToString();
this.tipo = quartoresult["tipo"].ToString();
this.camasolteiro = Convert.ToInt16(quartoresult["camasolteiro"].ToString());
this.camacasal = Convert.ToInt16(quartoresult["camacasal"].ToString());
this.valor = quartoresult["diaria"].ToString();
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());
if (quarto_status == 0)
{
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 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();
}
}
public void attComboBox()
{
NpgsqlConnection pgsqlConnection = null;
try
{
Cls_Conexao objconexao = new Cls_Conexao();
pgsqlConnection = objconexao.getConexao();
pgsqlConnection.Open();
string combobox;
NpgsqlCommand cmd;
if (tipo == "Cargo: Admin")
{
combobox = "select usuarioid from usuario order by usuarioid;";
cmd = new NpgsqlCommand(combobox, pgsqlConnection);
NpgsqlDataReader comboboxshow = cmd.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(comboboxshow);
cmb_pesquisacad.DisplayMember = "usuarioid";
cmb_pesquisacad.DataSource = dt;
}
else
{
combobox = "select usuarioid from usuario, login where usuarioid = fk_usuario and tipo = 'Cliente' order by usuarioid;";
cmd = new NpgsqlCommand(combobox, pgsqlConnection);
NpgsqlDataReader comboboxshow = cmd.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(comboboxshow);
cmb_pesquisacad.DisplayMember = "usuarioid";
cmb_pesquisacad.DataSource = dt;
}
}
finally
{
pgsqlConnection.Close();
}
}
public bool atualiza()
{
NpgsqlConnection pgsqlConnection = null;
try
{
Cls_Conexao objconexao = new Cls_Conexao();
pgsqlConnection = objconexao.getConexao();
pgsqlConnection.Open();
string atualizausuario;
string atualizalogin;
if (this.criterio == "Id")
{
this.pesquisaid = Convert.ToInt32(this.pesquisa);
atualizausuario = "UPDATE usuario SET nome='" + this.nome + "', rg='" + this.rg + "', telefone='" + this.telefone + "', rua='" + this.rua + "', numero='" + this.numero + "', bairro='" + this.bairro + "', cidade='" + this.cidade + "', estado='" + this.estado + "', cep='" + this.cep + "', ativo='" + this.ativo + "' where usuarioid = " + this.pesquisaid + ";";
atualizalogin = "******" + this.ativo + "', login='******', senha='" + this.senha + "', tipo='" + this.tipo + "' where fk_usuario = " + this.pesquisaid + ";";
}
else if (this.criterio == "RG")
{
atualizausuario = "UPDATE usuario SET nome='" + this.nome + "', rg='" + this.rg + "', telefone='" + this.telefone + "', rua='" + this.rua + "', numero='" + this.numero + "', bairro='" + this.bairro + "', cidade='" + this.cidade + "', estado='" + this.estado + "', cep='" + this.cep + "', ativo='" + this.ativo + "' where rg = '" + this.rg + "';";
atualizalogin = "******" + this.ativo + "', login='******', senha='" + this.senha + "', tipo='" + this.tipo + "' where fk_usuario = (select usuarioid from usuario where rg = '" + this.rg + "');";
}
else
{
atualizausuario = "UPDATE usuario SET nome='" + this.nome + "', rg='" + this.rg + "', telefone='" + this.telefone + "', rua='" + this.rua + "', numero='" + this.numero + "', bairro='" + this.bairro + "', cidade='" + this.cidade + "', estado='" + this.estado + "', cep='" + this.cep + "', ativo='" + this.ativo + "' where telefone = '" + this.telefone + "';";
atualizalogin = "******" + this.ativo + "', login='******', senha='" + this.senha + "', tipo='" + this.tipo + "' where fk_usuario = (select usuarioid from usuario where telefone = '" + this.telefone + "');";
}
NpgsqlCommand cmd = new NpgsqlCommand(atualizausuario, pgsqlConnection);
NpgsqlDataReader atualizauser = cmd.ExecuteReader();
atualizauser.Close();
cmd = new NpgsqlCommand(atualizalogin, pgsqlConnection);
NpgsqlDataReader atualizarlogin = cmd.ExecuteReader();
atualizarlogin.Close();
return(true);
}
finally
{
pgsqlConnection.Close();
}
}
public void attDashboard()
{
NpgsqlConnection pgsqlConnection = null;
try
{
Cls_Conexao objconexao = new Cls_Conexao();
pgsqlConnection = objconexao.getConexao();
pgsqlConnection.Open();
string querry = "select fk_usuario from login where login = '******';";
NpgsqlCommand cmd = new NpgsqlCommand(querry, pgsqlConnection);
NpgsqlDataReader Searchid = cmd.ExecuteReader();
Searchid.Read();
string id = Searchid["fk_usuario"].ToString();
Searchid.Close();
querry = "select count(quartofk) from reservas where status = 'Encerrado' and usuariofk = '" + id + "';";
cmd = new NpgsqlCommand(querry, pgsqlConnection);
NpgsqlDataReader ReservaEnc = cmd.ExecuteReader();
ReservaEnc.Read();
lbl_reservas_encerradas.Text = $"Reservas encerradas: {ReservaEnc["count"].ToString()}";
ReservaEnc.Close();
querry = "select count(quartofk) from reservas where status = 'Em andamento' and usuariofk = '" + id + "';";
cmd = new NpgsqlCommand(querry, pgsqlConnection);
NpgsqlDataReader ReservaAnd = cmd.ExecuteReader();
ReservaAnd.Read();
lbl_reservas_andamento.Text = $"Reservas encerradas: {ReservaAnd["count"].ToString()}";
}
finally
{
pgsqlConnection.Close();
}
}
public bool excluiItem()
{
NpgsqlConnection pgsqlConnection = null;
try
{
Cls_Conexao objconexao = new Cls_Conexao();
pgsqlConnection = objconexao.getConexao();
pgsqlConnection.Open();
string querry = "select itemid from itens where item = '" + this.criterio + "';";
NpgsqlCommand cmd = new NpgsqlCommand(querry, pgsqlConnection);
NpgsqlDataReader searchId = cmd.ExecuteReader();
searchId.Read();
string IdSearched = searchId["itemid"].ToString();
searchId.Close();
querry = "DELETE FROM fornecedor WHERE itemfk = '" + IdSearched + "';";
cmd = new NpgsqlCommand(querry, pgsqlConnection);
NpgsqlDataReader excluir = cmd.ExecuteReader();
excluir.Close();
querry = "DELETE FROM itens WHERE item = '" + this.criterio + "';";
cmd = new NpgsqlCommand(querry, pgsqlConnection);
NpgsqlDataReader excluir2 = cmd.ExecuteReader();
excluir.Close();
return(true);
}
finally
{
pgsqlConnection.Close();
}
}
public bool cadastroQuarto()
{
NpgsqlConnection pgsqlConnection = null;
try
{
Cls_Conexao objconexao = new Cls_Conexao();
pgsqlConnection = objconexao.getConexao();
pgsqlConnection.Open();
string querry = "select count(quarto) from quartos where quarto = '" + this.nome + "';";
NpgsqlCommand cmd = new NpgsqlCommand(querry, pgsqlConnection);
NpgsqlDataReader quartoresult = cmd.ExecuteReader();
quartoresult.Read();
int qntquarto = Convert.ToInt16(quartoresult["count"].ToString());
quartoresult.Close();
if (qntquarto == 0)
{
querry = "INSERT INTO quartos(quarto, tipo, camasolteiro, camacasal, status, diaria) VALUES ('" + this.nome + "', '" + this.tipo + "', '" + this.camasolteiro + "', '" + this.camacasal + "', 'Disponível', '" + this.valor + "');";
cmd = new NpgsqlCommand(querry, pgsqlConnection);
NpgsqlDataReader quarto = cmd.ExecuteReader();
quarto.Close();
return(true);
}
else
{
MessageBox.Show("Este quarto já existe, por favor insira outro", "Aviso", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(false);
}
}
finally
{
pgsqlConnection.Close();
}
}
public void ReservasEncerradas()
{
NpgsqlConnection pgsqlConnection = null;
try
{
Cls_Conexao objconexao = new Cls_Conexao();
pgsqlConnection = objconexao.getConexao();
pgsqlConnection.Open();
string querry = "select fk_usuario from login where login = '******';";
NpgsqlCommand cmd = new NpgsqlCommand(querry, pgsqlConnection);
NpgsqlDataReader Searchid = cmd.ExecuteReader();
Searchid.Read();
string id = Searchid["fk_usuario"].ToString();
Searchid.Close();
querry = "select nomefunc, nomecli, entrada, saida, valor, quartofk from reservas where status = 'Encerrado' and usuariofk = '" + id + "';";
cmd = new NpgsqlCommand(querry, pgsqlConnection);
NpgsqlDataReader dgv = cmd.ExecuteReader();
if (dgv.HasRows)
{
DataTable dt = new DataTable();
dt.Load(dgv);
dgv_reservas.DataSource = dt;
}
else
{
MessageBox.Show("Você não possui reservas encerradas", "Aviso", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
finally
{
pgsqlConnection.Close();
}
}
public bool cadastroCartao(string id)
{
NpgsqlConnection pgsqlConnection = null;
try
{
Cls_Conexao objconexao = new Cls_Conexao();
pgsqlConnection = objconexao.getConexao();
pgsqlConnection.Open();
string querry = "select count(usuariofk) from cartao where usuariofk = '" + id + "';";
NpgsqlCommand cmd = new NpgsqlCommand(querry, pgsqlConnection);
NpgsqlDataReader cartoes = cmd.ExecuteReader();
cartoes.Read();
int qntcartoes = Convert.ToInt16(cartoes["count"].ToString());
cartoes.Close();
if (qntcartoes == 0)
{
querry = "INSERT INTO cartao(numerocartao, nome, validade, codigo, bandeira, usuariofk) VALUES ('" + this.numero + "', '" + this.nome + "', '" + this.validade + "', '" + this.codigo + "', '" + this.bandeira + "', '" + id + "');";
cmd = new NpgsqlCommand(querry, pgsqlConnection);
NpgsqlDataReader cadastraCartao = cmd.ExecuteReader();
cadastraCartao.Close();
return(true);
}
else
{
return(false);
}
}
finally
{
pgsqlConnection.Close();
}
}
public bool atualizaItens()
{
NpgsqlConnection pgsqlConnection = null;
try
{
Cls_Conexao objconexao = new Cls_Conexao();
pgsqlConnection = objconexao.getConexao();
pgsqlConnection.Open();
string querry = "UPDATE itens SET item='" + this.nome + "', valor='" + this.valor + "' WHERE item = '" + this.criterio + "';";
NpgsqlCommand cmd = new NpgsqlCommand(querry, pgsqlConnection);
NpgsqlDataReader itemAtt = cmd.ExecuteReader();
itemAtt.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 = "UPDATE fornecedor SET nome_fornecedor='" + this.nome2 + "', valor_item='" + this.valor2 + "' WHERE itemfk='" + IdSearched + "';";
cmd = new NpgsqlCommand(querry, pgsqlConnection);
NpgsqlDataReader fornecedorAtt = cmd.ExecuteReader();
return(true);
}
finally
{
pgsqlConnection.Close();
}
}
public void atualizaComboBox2()
{
NpgsqlConnection pgsqlConnection = null;
try
{
Cls_Conexao objconexao = new Cls_Conexao();
pgsqlConnection = objconexao.getConexao();
pgsqlConnection.Open();
string querry = "select fk_usuario from login where login = '******';";
NpgsqlCommand cmd = new NpgsqlCommand(querry, pgsqlConnection);
NpgsqlDataReader Searchid = cmd.ExecuteReader();
Searchid.Read();
string id = Searchid["fk_usuario"].ToString();
Searchid.Close();
querry = "select quartofk from reservas where status = 'Em andamento' and usuariofk = '" + id + "';";
cmd = new NpgsqlCommand(querry, pgsqlConnection);
NpgsqlDataReader comboboxshow = cmd.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(comboboxshow);
cmb_quarto.DisplayMember = "quartofk";
cmb_quarto.DataSource = dt;
}
finally
{
pgsqlConnection.Close();
}
}