private void btnExcluir_Click(object sender, EventArgs e)
{
DialogResult iResposta = MessageBox.Show("Deseja excluir este aluno?", "Confirma Exclusao", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (iResposta == DialogResult.Yes)
{
try
{
conexao conexao = new conexao();
conexao.conectar();
MySqlCommand cmd = new MySqlCommand("call deletarAluno(?)", conexao.bdConn);
cmd.Parameters.AddWithValue("@id_aluno", txtId.Text);
cmd.ExecuteReader(CommandBehavior.SingleRow);
conexao.desconectar();
carregaListBox(lstAlunos);
inicial();
}
catch (Exception ex)
{
MessageBox.Show("Ocorreu o seguinte erro: " + ex.Message, "ERRO");
}
}
else
{
carregaListBox(lstAlunos);
inicial();
}
}
private void exibeDadosRepDoAluno(int id)
{
lstVaga.Items.Clear();
try
{
conexao conexao = new conexao();
conexao.conectar();
MySqlCommand cmd = new MySqlCommand("SELECT * FROM REPUBLICA WHERE id_republica in(select republica_id_republica from relacao where status ='ATIVO' and aluno_id_aluno = ?) )", conexao.bdConn);
cmd.Parameters.AddWithValue("@id_republica", id);
MySqlDataReader dr = cmd.ExecuteReader(CommandBehavior.SingleRow);
if (dr.Read())
{
lstVaga.Items.Add(dr["id_republica"].ToString() + "\t" + dr["NOME_REPUBLICA"].ToString());
txtNomeRep.Text = dr["NOME_REPUBLICA"].ToString();
txtAlunoResp.Text = dr["NOME_ALUNO"].ToString();
txtCapacidade.Text = dr["CAPACIDADE_REPUBLICA"].ToString();
txtVagas.Text = dr["VAGAS_REPUBLICA"].ToString();
txtTipo.Text = dr["TIPO_REPUBLICA"].ToString();
txtAluguel.Text = dr["preco_republica"].ToString();
txtDescricao.Text = dr["descricao_republica"].ToString();
txtRepublica.Text = dr["id_republica"].ToString();
}
dr.Close();
conexao.desconectar();
}
catch (Exception ex)
{
MessageBox.Show("Ocorreu o seguinte erro: " + ex.Message, "ERRO");
}
}
public void carregaListBox(ListBox lst)
{
lst.Items.Clear();
idAluno = 0;
conexao conexao = new conexao();
try
{
conexao.conectar();
MySqlCommand cmd = new MySqlCommand("SELECT * FROM ALUNO where status_aluno <> 2", conexao.bdConn);
MySqlDataReader dr = cmd.ExecuteReader(CommandBehavior.Default);
string texto;
while (dr.Read())
{
texto = dr[0].ToString();
texto = texto + "\t" + dr[1].ToString();
lst.Items.Add(texto);
}
}
catch (Exception e)
{
MessageBox.Show(Convert.ToString(e));
}
finally
{
conexao.desconectar();
}
}
public static void repTodas(ListBox lst)
{
lst.Items.Clear();
conexao conexao = new conexao();
MySqlCommand cmd;
try
{
conexao.conectar();
cmd = new MySqlCommand("SELECT * FROM republica WHERE status_republica<>2 ", conexao.bdConn);
MySqlDataReader dr = cmd.ExecuteReader(CommandBehavior.Default);
string texto;
while (dr.Read())
{
texto = dr["id_republica"].ToString();
texto = texto + "\t" + dr["nome_republica"].ToString();
texto = texto + "\t Vagas:" + dr["vagas_republica"].ToString();
lst.Items.Add(texto);
}
dr.Close();
}
catch (Exception e)
{
MessageBox.Show(Convert.ToString(e));
}
finally
{
conexao.desconectar();
}
}
private void exibeDados(int prontuario, string nome)
{
conexao conexao = new conexao();
conexao.conectar();
MySqlCommand cmd = new MySqlCommand("SELECT * FROM ALUNO WHERE NOME_ALUNO=? AND id_aluno=?", conexao.bdConn);
cmd.Parameters.AddWithValue("@NOME_ALUNO", nome);
cmd.Parameters.AddWithValue("@id_aluno", prontuario);
MySqlDataReader dr = cmd.ExecuteReader(CommandBehavior.SingleRow);
if (dr.Read())
{
txtId.Text = Convert.ToString(dr[("ID_ALUNO")]);
txtNome.Text = Convert.ToString(dr[("NOME_ALUNO")]);
txtProntuario.Text = Convert.ToString(dr[("PRONTUARIO_ALUNO")]);
txtCurso.Text = Convert.ToString(dr[("CURSO_ALUNO")]);
txtEmail.Text = Convert.ToString(dr[("EMAIL_ALUNO")]);
txtCelular.Text = Convert.ToString(dr[("CELULAR_ALUNO")]);
txtTelefone.Text = Convert.ToString(dr[("TELEFONE_ALUNO")]);
}
dr.Close();
conexao.desconectar();
}
public void carregaListBox(ListBox lst)
{
lst.Items.Clear();
idRep = 0;
conexao conexao = new conexao();
MySqlCommand cmd;
try
{
conexao.conectar();
cmd = new MySqlCommand("select * from republica where status_republica = 1 ", conexao.bdConn);
MySqlDataReader dr = cmd.ExecuteReader(CommandBehavior.Default);
string texto;
while (dr.Read())
{
texto = dr["id_republica"].ToString();
texto = texto + "\t" + dr["nome_republica"].ToString();
texto = texto + "\t" + dr["alunoResponsavel_republica"].ToString();
lst.Items.Add(texto);
}
}
catch (Exception e)
{
MessageBox.Show(Convert.ToString(e));
}
finally
{
conexao.desconectar();
}
}
public static void AlunosComRep(ListBox lst)
{
lst.Items.Clear();
conexao conexao = new conexao();
try
{
conexao.conectar();
MySqlCommand cmd = new MySqlCommand("SELECT ID_ALUNO, NOME_ALUNO FROM ALUNO WHERE status_aluno = 1 ", conexao.bdConn);
MySqlDataReader dr = cmd.ExecuteReader(CommandBehavior.Default);
string texto;
while (dr.Read())
{
texto = dr[0].ToString();
texto = texto + "\t" + dr[1].ToString();
lst.Items.Add(texto);
}
dr.Close();
}
catch (Exception e)
{
MessageBox.Show(Convert.ToString(e));
}
finally
{
conexao.desconectar();
}
}
private void exibeDados(int id, string nome)
{
conexao conexao = new conexao();
conexao.conectar();
MySqlCommand cmd = new MySqlCommand("SELECT * FROM REPUBLICA WHERE id_republica=? AND nome_republica=?", conexao.bdConn);
cmd.Parameters.AddWithValue("@id_republica", id);
cmd.Parameters.AddWithValue("@nome_republica", nome);
MySqlDataReader dr = cmd.ExecuteReader(CommandBehavior.SingleRow);
if (dr.Read())
{
txtId.Text = dr["id_republica"].ToString();
txtAlunoResponsavel.Text = dr["alunoResponsavel_republica"].ToString();
txtNome.Text = dr["nome_republica"].ToString();
txtUf.Text = dr["uf_republica"].ToString();
txtRua.Text = dr["rua_republica"].ToString();
txtTipo.Text = dr["tipo_republica"].ToString();
txtBairro.Text = dr["bairro_republica"].ToString();
txtCidade.Text = dr["cidade_republica"].ToString();
txtNumero.Text = dr["numero_republica"].ToString();
txtComplemento.Text = dr["complemento_republica"].ToString();
txtCapacidade.Text = dr["capacidade_republica"].ToString();
txtVagas.Text = dr["vagas_republica"].ToString();
txtDescricao.Text = dr["descricao_republica"].ToString();
txtaluguel.Text = dr["preco_republica"].ToString();
txtIdLugar.Text = dr["lugar_id_lugar"].ToString();
}
dr.Close();
conexao.desconectar();
}
public void CarregaPontoOnibus()
{
conexao conexao = new conexao();
try
{
conexao.conectar();
MySqlCommand cmd = new MySqlCommand("select onibus.numero_onibus, onibus.descricao_onibus,lugar.latitude_lugar, lugar.longitude_lugar, lugar_onibus.horario, lugar_onibus.dia from lugar, lugar_onibus, onibus where lugar_onibus.id_lugar = lugar.id_lugar AND lugar_onibus.numero_onibus = onibus.numero_onibus", conexao.bdConn);
MySqlDataReader dr = cmd.ExecuteReader(CommandBehavior.Default);
while (dr.Read())
{
pontos.Add(new Ponto(Convert.ToInt32(dr[0]), dr[1].ToString(), (dr[2]).ToString(), (dr[3]).ToString(), (dr[4]).ToString(), dr[5].ToString()));
}
}
catch (Exception ex)
{
MessageBox.Show("Ocorreu o seguinte erro: " + ex.Message, "ERRO");
}
finally
{
conexao.desconectar();
}
}
public static void buscaListBoxRep(ListBox lst, string busca)
{
lst.Items.Clear();
conexao conexao = new conexao();
try
{
conexao.conectar();
MySqlCommand cmd = new MySqlCommand("SELECT id_republica,nome_republica,alunoResponsavel_republica FROM republica WHERE nome_republica LIKE ? ", conexao.bdConn);
cmd.Parameters.AddWithValue("@nome_republica", busca + "%");
MySqlDataReader dr = cmd.ExecuteReader(CommandBehavior.Default);
string texto;
while (dr.Read())
{
texto = dr[0].ToString();
texto = texto + "\t" + dr[1].ToString();
lst.Items.Add(texto);
}
}
catch (Exception e)
{
MessageBox.Show("Ocorreu o seguinte erro: " + e.Message, "ERRO");
}
finally
{
conexao.desconectar();
}
}
public void carregaPontosRep()
{
conexao conexao = new conexao();
try
{
conexao.conectar();
MySqlCommand cmd = new MySqlCommand("select * from infoRepublica", conexao.bdConn);
MySqlDataReader dr = cmd.ExecuteReader(CommandBehavior.Default);
while (dr.Read())
{
republicas.Add(new Republica(Convert.ToInt32(dr[0]), dr[1].ToString(), (dr[2]).ToString(), (dr[3]).ToString(), Convert.ToInt32(dr[4]), dr[5].ToString(), dr[6].ToString()));
}
}
catch (Exception ex)
{
MessageBox.Show("Ocorreu o seguinte erro: " + ex.Message, "ERRO");
}
finally
{
conexao.desconectar();
}
}
public void carregaComboBox()
{
txtAlunoResponsavel.Items.Clear();
conexao conexao = new conexao();
MySqlCommand cmd;
try
{
conexao.conectar();
cmd = new MySqlCommand("SELECT * FROM aluno WHERE status_aluno=0 ", conexao.bdConn);
MySqlDataReader dr = cmd.ExecuteReader(CommandBehavior.Default);
string texto;
while (dr.Read())
{
texto = dr["nome_aluno"].ToString();
texto = texto + "\t" + dr["id_aluno"].ToString();
txtAlunoResponsavel.Items.Add(texto);
}
}
catch (Exception e)
{
MessageBox.Show(Convert.ToString(e));
}
finally
{
conexao.desconectar();
}
}
private void btnSalvar_Click(object sender, EventArgs e)
{
if (txtAluno.Text == "" && txtRepublica.Text == "")
{
}
else
{
conexao conexao = new conexao();
conexao.conectar();
MySqlCommand cmd;
try
{
cmd = new MySqlCommand("call desfazRelaçao(?,?)", conexao.bdConn);
cmd.Parameters.AddWithValue("@aluno_id_aluno", txtAluno.Text);
cmd.Parameters.AddWithValue("@republica_id_republica", txtRepublica.Text);
cmd.ExecuteReader(CommandBehavior.SingleRow);
}
catch (Exception ex)
{
MessageBox.Show("Ocorreu o seguinte erro: " + ex.Message, "ERRO");
}
finally
{
conexao.desconectar();
inicial();
}
}
}
private void id()
{
conexao conexao = new conexao();
conexao.conectar();
MySqlCommand cmd = new MySqlCommand("SELECT max(id_republica)+1 FROM republica", conexao.bdConn);
MySqlDataReader dr = cmd.ExecuteReader(CommandBehavior.Default);
if (dr.Read())
{
txtId.Text = dr[0].ToString();
}
}
private int nRep()
{
int i = 0;
conexao conexao = new conexao();
conexao.conectar();
MySqlCommand cmd = new MySqlCommand("select count(id_republica) from republica", conexao.bdConn);
MySqlDataReader dr = cmd.ExecuteReader(CommandBehavior.Default);
if (dr.Read())
{
i = Convert.ToInt32(dr[0]);
}
conexao.desconectar();
return(i);
}
public static void carregaListBox(ListBox lst)
{
conexao conexao = new conexao();
try
{
conexao.conectar();
MySqlCommand cmd = new MySqlCommand("SELECT NOME_ALUNO FROM ALUNO", conexao.bdConn);
MySqlDataReader dr = cmd.ExecuteReader(CommandBehavior.Default);
while (dr.Read())
{
lst.Items.Add(dr["NOME_ALUNO"]);
}
}
catch (Exception e)
{
MessageBox.Show(Convert.ToString(e));
}
finally
{
conexao.desconectar();
}
}
private void btnExcluir_Click(object sender, EventArgs e)
{
DialogResult iResposta = MessageBox.Show("Deseja excluir esta Republica?", "Confirma Exclusao", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (iResposta.ToString() == "Yes")
{
conexao conexao = new conexao();
conexao.conectar();
MySqlCommand cmd = new MySqlCommand("call deletarRepublica(?);", conexao.bdConn);
cmd.Parameters.AddWithValue("@id_republica", txtId.Text);
cmd.ExecuteReader(CommandBehavior.SingleRow);
conexao.desconectar();
carregaListBox(lstRepublica);
inicial();
}
else
{
carregaListBox(lstRepublica);
inicial();
}
}
private void exibeDadosRep(int id)
{
conexao conexao = new conexao();
conexao.conectar();
MySqlCommand cmd = new MySqlCommand("SELECT * FROM REPUBLICA WHERE id_republica=?", conexao.bdConn);
cmd.Parameters.AddWithValue("@id_republica", id);
MySqlDataReader dr = cmd.ExecuteReader(CommandBehavior.SingleRow);
if (dr.Read())
{
txtNomeRep.Text = dr["NOME_REPUBLICA"].ToString();
txtAlunoResp.Text = dr["alunoResponsavel_republica"].ToString();
txtCapacidade.Text = dr["CAPACIDADE_REPUBLICA"].ToString();
txtVagas.Text = dr["VAGAS_REPUBLICA"].ToString();
txtTipo.Text = dr["TIPO_REPUBLICA"].ToString();
txtAluguel.Text = dr["preco_republica"].ToString();
txtDescricao.Text = dr["descricao_republica"].ToString();
txtRepublica.Text = dr["id_republica"].ToString();
}
dr.Close();
conexao.desconectar();
}
private void btnSalvar_Click(object sender, EventArgs e)
{
conexao conexao = new conexao();
conexao.conectar();
MySqlCommand cmd;
if (operacao == 1)
{
try
{
cmd = new MySqlCommand("INSERT INTO ALUNO VALUES(?,?,?,?,?,?,?,?)", conexao.bdConn);
cmd.Parameters.AddWithValue("@ID_ALUNO", txtId.Text);
cmd.Parameters.AddWithValue("@NOME_ALUNO", txtNome.Text);
cmd.Parameters.AddWithValue("@PRONTUARIO_ALUNO", txtProntuario.Text);
cmd.Parameters.AddWithValue("@EMAIL_ALUNO", txtEmail.Text);
cmd.Parameters.AddWithValue("@CELULAR_ALUNO", txtCelular.Text);
cmd.Parameters.AddWithValue("@TELEFONE_ALUNO", txtTelefone.Text);
cmd.Parameters.AddWithValue("@CURSO_ALUNO", txtCurso.Text);
cmd.Parameters.AddWithValue("@status_aluno", 0);
cmd.ExecuteReader(CommandBehavior.SingleRow);
DialogResult iResposta;
iResposta = MessageBox.Show("Deseja Continuar a Incluir?", "Confirma Nova Inclusao", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (iResposta.ToString() == "Yes")
{
txtId.Text = "";
txtNome.Text = "";
txtProntuario.Text = "";
txtCurso.Text = "";
txtEmail.Text = "";
txtCelular.Text = "";
txtTelefone.Text = "";
btnAdiconar.Enabled = false;
btnCancelar.Enabled = true;
btnEditar.Enabled = false;
btnExcluir.Enabled = false;
btnSalvar.Enabled = true;
gbxAlunos.Enabled = false;
gbxDados.Enabled = true;
carregaListBox(lstAlunos);
}
else
{
inicial();
carregaListBox(lstAlunos);
}
}
catch (Exception ex)
{
MessageBox.Show("Ocorreu o seguinte erro: " + ex.Message, "ERRO");
}
}
else if (operacao == 2)
{
cmd = new MySqlCommand("UPDATE (ALUNO) SET NOME_ALUNO=?, PRONTUARIO_ALUNO=?, EMAIL_ALUNO=?, CELULAR_ALUNO=?, TELEFONE_ALUNO=?, CURSO_ALUNO=? WHERE ID_ALUNO=?", conexao.bdConn);
cmd.Parameters.AddWithValue("@NOME_ALUNO", txtNome.Text);
cmd.Parameters.AddWithValue("@PRONTUARIO_ALUNO", txtProntuario.Text);
cmd.Parameters.AddWithValue("@EMAIL_ALUNO", txtEmail.Text);
cmd.Parameters.AddWithValue("@CELULAR_ALUNO", txtCelular.Text);
cmd.Parameters.AddWithValue("@TELEFONE_ALUNO", txtTelefone.Text);
cmd.Parameters.AddWithValue("@CURSO_ALUNO", txtCurso.Text);
cmd.Parameters.AddWithValue("@ID_ALUNO", txtId.Text);
cmd.ExecuteReader(CommandBehavior.SingleRow);
inicial();
carregaListBox(lstAlunos);
}
inicial();
}
private void btnSalvar_Click(object sender, EventArgs e)
{
conexao conexao = new conexao();
conexao.conectar();
MySqlCommand cmd;
if (operacao == 1)
{
{
try
{
string[] campo = txtAlunoResponsavel.Text.Split(Convert.ToChar("\t"));
cmd = new MySqlCommand("call criaRep(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)", conexao.bdConn);
cmd.Parameters.AddWithValue("@lat", lat);
cmd.Parameters.AddWithValue("@lon", lng);
if (txtId.Text != "")
{
cmd.Parameters.AddWithValue("@id", txtId.Text);
}
else
{
cmd.Parameters.AddWithValue("@id", 0);
}
cmd.Parameters.AddWithValue("@al", campo[0].ToString());
cmd.Parameters.AddWithValue("@nome", txtNome.Text);
cmd.Parameters.AddWithValue("@uf", txtUf.Text);
cmd.Parameters.AddWithValue("@rua", txtRua.Text);
cmd.Parameters.AddWithValue("@tipo", txtTipo.Text);
cmd.Parameters.AddWithValue("@bairro", txtBairro.Text);
cmd.Parameters.AddWithValue("@cidade", txtCidade.Text);
cmd.Parameters.AddWithValue("@numero", txtNumero.Text);
cmd.Parameters.AddWithValue("@compl", txtComplemento.Text);
cmd.Parameters.AddWithValue("@cap", txtCapacidade.Text);
cmd.Parameters.AddWithValue("@descrb", txtDescricao.Text);
cmd.Parameters.AddWithValue("@preco", txtaluguel.Text);
cmd.Parameters.AddWithValue("@idlu", Convert.ToInt32(campo[1]));
cmd.ExecuteReader(CommandBehavior.SingleRow);
DialogResult iResposta;
iResposta = MessageBox.Show("Deseja Continuar a Incluir?", "Confirma Nova Inclusao", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (iResposta.ToString() == "Yes")
{
txtId.Text = "";
txtNome.Text = "";
txtAlunoResponsavel.Text = "";
txtTipo.Text = "";
txtUf.Text = "";
txtCidade.Text = "";
txtBairro.Text = "";
txtRua.Text = "";
txtNumero.Text = "";
txtComplemento.Text = "";
txtCapacidade.Text = "";
txtVagas.Text = "";
txtDescricao.Text = "";
txtaluguel.Text = "";
operacao = 1;
gbxDados.Enabled = true;
gbxRepublica.Enabled = false;
btnAdicionar.Enabled = false;
btnCancelar.Enabled = true;
btnEditar.Enabled = false;
btnExcluir.Enabled = false;
btnSalvar.Enabled = true;
carregaListBox(lstRepublica);
}
else
{
inicial();
carregaListBox(lstRepublica);
}
}
catch (Exception ex)
{
// MessageBox.Show("Ocorreu o seguinte erro: " + ex.Message, "ERRO");
MessageBox.Show(ex.ToString());
}
}
}
else if (operacao == 2)
{
try
{
cmd = new MySqlCommand("UPDATE (republica) SET alunoResponsavel_republica=?, nome_republica=?, uf_republica=?, rua_republica=?, tipo_republica=?, bairro_republica=?, cidade_republica=?, numero_republica=?, complemento_republica=?, capacidade_republica=?, vagas_republica=?, descricao_republica=?,preco_republica=? WHERE id_republica=?", conexao.bdConn);
cmd.Parameters.AddWithValue("@alunoResponsavel_republica", txtAlunoResponsavel.Text);
cmd.Parameters.AddWithValue("@nome_republica", txtNome.Text);
cmd.Parameters.AddWithValue("@uf_republica", txtUf.Text);
cmd.Parameters.AddWithValue("@rua_republica", txtRua.Text);
cmd.Parameters.AddWithValue("@tipo_republica", txtTipo.Text);
cmd.Parameters.AddWithValue("@bairro_republica", txtBairro.Text);
cmd.Parameters.AddWithValue("@cidade_republica", txtId.Text);
cmd.Parameters.AddWithValue("@numero_republica", txtNome.Text);
cmd.Parameters.AddWithValue("@complemento_republica", txtComplemento.Text);
cmd.Parameters.AddWithValue("@capacidade_republica", txtCapacidade.Text);
cmd.Parameters.AddWithValue("@vagas_republica", txtVagas.Text);
cmd.Parameters.AddWithValue("@descricao_republica", txtDescricao.Text);
cmd.Parameters.AddWithValue("@preco_republica", txtDescricao.Text);
cmd.Parameters.AddWithValue("@id_republica", txtId.Text);
cmd.ExecuteReader(CommandBehavior.SingleRow);
inicial();
carregaListBox(lstRepublica);
}
catch (Exception ex)
{
MessageBox.Show("Ocorreu o seguinte erro: " + ex.Message, "ERRO");
//MessageBox.Show(ex.ToString());
}
}
inicial();
}