private void HospBeatriz_Load(object sender, EventArgs e)
{
MDB mdb = new MDB();
{
string sql = $@"select e.nome_especialidade
from hospital_tem_especialidade x
inner join especialidade e on x.Especialidade_idEspecialidade = e.idEspecialidade
where x.Hospital_idHospita = 1 order by e.nome_especialidade";
MySqlCommand command = new MySqlCommand(sql, mdb.getConnection());
{
MySqlDataReader myReader;
try
{
mdb.openConnection();
myReader = command.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(myReader);
cmbhospitalBA.DisplayMember = "nome_especialidade";
cmbhospitalBA.ValueMember = "Especialidade_idEspecialidade";
cmbhospitalBA.DataSource = dt;
}
catch (Exception erro)
{
MessageBox.Show("Erro:" + erro.Message);
}
finally
{
mdb.closeConnection();
}
}
}
}
private void btnDelete_Click(object sender, EventArgs e)
{
MDB mdb = new MDB();
{
string sql = $@"DELETE FROM medico WHERE idMedico = @im ";
MySqlCommand command = new MySqlCommand(sql, mdb.getConnection());
command.Parameters.AddWithValue("@im", txtIdmedico.Text);
try
{
mdb.openConnection();
if (command.ExecuteNonQuery() == 1)
{
MessageBox.Show("Médico eliminado com sucesso", "Eliminar", MessageBoxButtons.OK, MessageBoxIcon.Information);
panel3.Visible = false;
}
else
{
MessageBox.Show("ERRO");
}
}
catch (Exception erro)
{
throw erro;
}
finally
{
mdb.closeConnection();
}
}
}
private void contConsultas()
{
#region COUNT total de consultas realizadas
MDB mdb = new MDB();
{
try
{
mdb.openConnection();
DataTable table = new DataTable();
MySqlCommand command = new MySqlCommand("SELECT count(distinct idMarcacao ) FROM consulta WHERE Data_consulta " +
"BETWEEN DATE_ADD(CURRENT_DATE(), INTERVAL -100 year ) AND CURRENT_DATE()", mdb.getConnection());
command.Parameters.Clear();
command.CommandType = CommandType.Text;
MySqlDataReader dr;
dr = command.ExecuteReader();
dr.Read();
label1.Text = dr.GetString(0);
}
catch (Exception erro)
{
throw erro;
}
finally
{
mdb.closeConnection();
}
}
#endregion
}
private void contConsultas()
{
#region COUNT total de consultas realizadas
MDB mdb = new MDB();
{
try
{
mdb.openConnection();
DataTable table = new DataTable();
MySqlCommand command = new MySqlCommand("SELECT count(distinct id_user ) FROM user", mdb.getConnection());
command.Parameters.Clear();
command.CommandType = CommandType.Text;
MySqlDataReader dr;
dr = command.ExecuteReader();
dr.Read();
label1.Text = dr.GetString(0);
}
catch (Exception erro)
{
throw erro;
}
finally
{
mdb.closeConnection();
}
}
#endregion
}
public void CarregarHospitais()
{
MDB mdb = new MDB();
{
MySqlCommand command = new MySqlCommand("SELECT * FROM hospital ORDER BY nome_hospital ASC;", mdb.getConnection());
{
MySqlDataReader myReader;
try
{
mdb.openConnection();
myReader = command.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(myReader);
cmbhospital.DisplayMember = "nome_hospital";
cmbhospital.ValueMember = "idHospita";
cmbhospital.DataSource = dt;
}
catch (Exception erro)
{
MessageBox.Show("Erro:" + erro.Message);
}
finally
{
mdb.closeConnection();
}
}
}
}
private void contMedico()
{
#region COUNT quantidade de Medicos
MDB mdb = new MDB();
{
try
{
mdb.openConnection();
DataTable table = new DataTable();
MySqlCommand command = new MySqlCommand("SELECT count(distinct idMedico ) FROM medico ", mdb.getConnection());
command.Parameters.Clear();
command.CommandType = CommandType.Text;
MySqlDataReader dr;
dr = command.ExecuteReader();
dr.Read();
label19.Text = dr.GetString(0);
}
catch (Exception erro)
{
throw erro;
}
finally
{
mdb.closeConnection();
}
}
#endregion
}
private void btnEliminar_Click(object sender, EventArgs e)
{
#region Botão eliminar
MDB mdb = new MDB();
{
string sql = $@"DELETE FROM `consulta` WHERE idMarcacao= @im ";
MySqlCommand command = new MySqlCommand(sql, mdb.getConnection());
command.Parameters.AddWithValue("@im", label15.Text);
try
{
mdb.openConnection();
if (command.ExecuteNonQuery() == 1)
{
MessageBox.Show("Consulta eliminada com sucesso", "Eliminar", MessageBoxButtons.OK, MessageBoxIcon.Information);
panel6.Visible = false;
}
else
{
MessageBox.Show("ERRO");
}
}
catch (Exception erro)
{
throw erro;
}
finally
{
mdb.closeConnection();
}
}
#endregion
}
private void bunifuCustomDataGrid1_MouseDoubleClick(object sender, MouseEventArgs e)
{
panel3.Visible = false;
//panel
if (panel3.Visible == true)
{
panel3.Visible = false;
}
else
{
panel3.Visible = true;
}
//mostrar botões
if (btnDelete.Visible == false || btnEditar.Visible == false)
{
btnDelete.Visible = true;
btnEditar.Visible = true;
}
txtIdmedico.Text = bunifuCustomDataGrid1.CurrentRow.Cells[0].Value.ToString();
txtNomeMedico.Text = bunifuCustomDataGrid1.CurrentRow.Cells[1].Value.ToString();
txtIdhospital.Text = bunifuCustomDataGrid1.CurrentRow.Cells[5].Value.ToString();
txtIdespecialdade.Text = bunifuCustomDataGrid1.CurrentRow.Cells[6].Value.ToString();
txtMorada.Text = bunifuCustomDataGrid1.CurrentRow.Cells[2].Value.ToString();
txtEmail.Text = bunifuCustomDataGrid1.CurrentRow.Cells[3].Value.ToString();
txtContacto.Text = bunifuCustomDataGrid1.CurrentRow.Cells[4].Value.ToString();
//codico para quando o user der duble click apareca o id do hospital e especialidade em vez do nome
MDB mdb = new MDB();
{
try
{
mdb.openConnection();
//DataTable table = new DataTable();
MySqlCommand command = new MySqlCommand("select Hospital_idHospita, Especialidade_idEspecialidade from medico where idMedico = @im", mdb.getConnection());
command.Parameters.Clear();
command.CommandType = CommandType.Text;
command.Parameters.Add("@im", MySqlDbType.VarChar).Value = txtIdmedico.Text;
MySqlDataReader dr;
dr = command.ExecuteReader();
dr.Read();
txtIdhospital.Text = dr.GetString(0);
txtIdespecialdade.Text = dr.GetString(1);
}
catch (Exception erro)
{
throw erro;
}
finally
{
mdb.closeConnection();
}
}
}
private void MinhaConta_Load(object sender, EventArgs e)
{
panel1.Visible = false;
label5.Text = Entrar.passingText2;
MDB mdb = new MDB();
{
try
{
mdb.openConnection();
MySqlCommand command = new MySqlCommand("select id_user, nome_completo, email from `user` where `user` = @im ", mdb.getConnection());
command.Parameters.Clear();
command.CommandType = CommandType.Text;
command.Parameters.Add("@im", MySqlDbType.VarChar).Value = label5.Text;
MySqlDataReader dr = command.ExecuteReader();
while (dr.Read())
{
label8.Text = dr.GetString(0);
label7.Text = dr.GetString(1);
label6.Text = dr.GetString(2);
}
dr.Close();
}
catch (Exception erro)
{
throw erro;
}
finally
{
mdb.closeConnection();
}
DataTable tablex = new DataTable();
string sql = $@"SELECT idMarcacao as 'ID Marcação' ,nome_completo as 'Nome do Untente', num_saude as 'Nº de Saúde',
Data_nascimento as 'Data de Nascimento', c.contacto as 'contacto', c.morada as 'morada',
nome_hospital as 'Hospital', nome_especialidade as 'Especialidade', data_consulta as 'Data da Consulta',
nome_medico as 'Médico Designado',
IF(data_consulta > curdate(), 'confirmado', 'concluido')
AS Estado FROM consulta c
inner join medico m on c.Medico_idMedico = m.idMedico
where User_id_user = '******' ";
MySqlDataAdapter adapter = new MySqlDataAdapter(sql, mdb.getConnection());
adapter.Fill(tablex);
bunifuCustomDataGrid1.DataSource = tablex;
}
}
private void UserControl3_Load(object sender, EventArgs e)
{
MaxContacto();
MaxNumSaude();
dateTimePicker1.MaxDate = DateTime.Now;
cmbhospital.DropDownStyle = ComboBoxStyle.DropDownList;
cmbespecialidade.DropDownStyle = ComboBoxStyle.DropDownList;
CarregarHospitais();
MDB mdb = new MDB();
{
try
{
mdb.openConnection();
DataTable table = new DataTable();
MySqlCommand command = new MySqlCommand("select id_user from `user` where `user` = @im ", mdb.getConnection());
command.Parameters.Clear();
command.CommandType = CommandType.Text;
command.Parameters.Add("@im", MySqlDbType.VarChar).Value = Entrar.passingText2;
MySqlDataReader dr;
dr = command.ExecuteReader();
dr.Read();
passingText10 = dr.GetString(0);
// label5.Text = dr.GetString(0);
}
catch (Exception erro)
{
throw erro;
}
finally
{
mdb.closeConnection();
}
}
cmbhospital.Text = UserControl1.passingText;
cmbespecialidade.Text = UserControl1.passingText1;
txtNomeCompleto.Text = UserControl2.passingText2;
txtNSaude.Text = UserControl2.passingText3;
txtContacto.Text = UserControl2.passingText4;
dateTimePicker1.Text = UserControl2.passingText5;
txtMorada.Text = UserControl2.passingText6;
}
private void btnAlterar_Click(object sender, EventArgs e)
{
#region update na tabela consulta
MDB mdb = new MDB();
{
string sql = $@"UPDATE `consulta` SET `data_consulta`= @dc, `Medico_idMedico`= @med WHERE `idMarcacao` = @im ";
MySqlCommand command = new MySqlCommand(sql, mdb.getConnection());
command.Parameters.Add("@dc", MySqlDbType.Date).Value = dateTimePicker2.Value;
command.Parameters.AddWithValue("@med", textBox1.Text);
command.Parameters.AddWithValue("@im", txtIdmarcacao.Text);
try
{
mdb.openConnection();
if (!VerificarValoresTextBoxes())
{
if (command.ExecuteNonQuery() == 1)
{
MessageBox.Show("Consulta Alterada", "Editado", MessageBoxButtons.OK, MessageBoxIcon.Information);
panel3.Visible = false;
panel5.Visible = false;
textBox1.Text = "";
}
else
{
MessageBox.Show("ERRO");
}
}
else
{
MessageBox.Show("Insira o ID do Médico", "ERRO", MessageBoxButtons.OKCancel, MessageBoxIcon.Error);
}
}
catch (Exception erro)
{
throw erro;
}
finally
{
mdb.closeConnection();
}
}
#endregion
}
private void btnEditar_Click(object sender, EventArgs e)
{
#region UPDATE na tabela user
MDB mdb = new MDB();
{
string sql = $@"UPDATE `user` SET `nome_completo`= @nm , `user` = @us, `email`= @email, `passe` = @ps WHERE `id_user` = @iu ";
MySqlCommand command = new MySqlCommand(sql, mdb.getConnection());
command.Parameters.AddWithValue("@nm", txtNomecompleto.Text);
command.Parameters.AddWithValue("@us", txtNomeUser.Text);
command.Parameters.AddWithValue("@email", txtEmail.Text);
command.Parameters.AddWithValue("@ps", txtPasse.Text);
command.Parameters.AddWithValue("@iu", txtIduser.Text);
try
{
mdb.openConnection();
if (command.ExecuteNonQuery() == 1)
{
MessageBox.Show("Registo atualizado", "Atualizado", MessageBoxButtons.OK, MessageBoxIcon.Information);
txtNomecompleto.Text = "";
txtNomeUser.Text = "";
txtEmail.Text = "";
txtPasse.Text = "";
txtIduser.Text = "";
}
else
{
MessageBox.Show("ERRO");
}
}
catch (Exception erro)
{
throw erro;
}
finally
{
mdb.closeConnection();
}
}
#endregion
}
private void Btn5_Click(object sender, EventArgs e)
{
MDB mdb = new MDB();
{
string sql = $@"INSERT INTO `consulta`( `nome_completo`, `num_saude`, `contacto`, `Data_nascimento`, `morada`, `nome_especialidade`, `nome_hospital`)VALUES (@nc, @ns,@cont, @dn, @mor, @ne, @nh )";
MySqlCommand command = new MySqlCommand(sql, mdb.getConnection());
command.Parameters.Add("@nc", MySqlDbType.VarChar).Value = txtNomeCompleto.Text;
command.Parameters.Add("@ns", MySqlDbType.VarChar).Value = txtNSaude.Text;
command.Parameters.Add("@cont", MySqlDbType.VarChar).Value = txtContacto.Text;
command.Parameters.Add("@dn", MySqlDbType.Date).Value = dateTimePicker1.Value;
command.Parameters.Add("@mor", MySqlDbType.VarChar).Value = txtMorada.Text;
command.Parameters.Add("@ne", MySqlDbType.VarChar).Value = cmbespecialidade.Text;
command.Parameters.Add("@nh", MySqlDbType.VarChar).Value = cmbhospital.Text;
try
{
mdb.openConnection();
if (command.ExecuteNonQuery() == 1)
{
MessageBox.Show("Pedido de consulta realizado com sucesso", "Mrcação de consulta", MessageBoxButtons.OK, MessageBoxIcon.Information);
txtNomeCompleto.Text = "Nome Completo";
txtNSaude.Text = "Nº de Saude";
txtContacto.Text = "Telemovel / Telefone";
// dateTimePicker1.Value = DateTime.Now;
txtMorada.Text = "Morada";
//this.Parent.Controls.Clear(); ;
}
else
{
MessageBox.Show("ERRO");
}
}
catch (Exception erro)
{
throw erro;
}
finally
{
mdb.closeConnection();
}
}
}
private void btnGuardar_Click(object sender, EventArgs e)
{
MDB mdb = new MDB();
{
string sql = $@"INSERT INTO `medico`(`nome_medico`, `email`, `morada`, `contacto`, `Especialidade_idEspecialidade`, `Hospital_idHospita`)
VALUES (@nm, @email, @mor, @cont, @ie, @ih)";
MySqlCommand command = new MySqlCommand(sql, mdb.getConnection());
command.Parameters.Add("@nm", MySqlDbType.VarChar).Value = txtNomeMedico.Text;
command.Parameters.Add("@mor", MySqlDbType.VarChar).Value = txtMorada.Text;
command.Parameters.Add("@email", MySqlDbType.VarChar).Value = txtEmail.Text;
command.Parameters.Add("@cont", MySqlDbType.VarChar).Value = txtContacto.Text;
command.Parameters.Add("@ie", MySqlDbType.VarChar).Value = txtIdespecialdade.Text;
command.Parameters.Add("@ih", MySqlDbType.VarChar).Value = txtIdhospital.Text;
try
{
mdb.openConnection();
if (command.ExecuteNonQuery() == 1)
{
MessageBox.Show("Medico adicionado com sucesso", "Medico Adicionado", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
MessageBox.Show("ERRO");
}
}
catch (Exception erro)
{
throw erro;
}
finally
{
mdb.closeConnection();
}
}
panel3.Visible = false;
}
private void btnEditar_Click(object sender, EventArgs e)
{
MDB mdb = new MDB();
{
string sql = $@"UPDATE `medico` SET `nome_medico`= @nm , `email`= @email, `morada` = @mor, `contacto` = @cont, `Especialidade_idEspecialidade` = @ie, `Hospital_idHospita` =@ih WHERE `idMedico` = @im ";
MySqlCommand command = new MySqlCommand(sql, mdb.getConnection());
command.Parameters.AddWithValue("@nm", txtNomeMedico.Text);
command.Parameters.AddWithValue("@mor", txtMorada.Text);
command.Parameters.AddWithValue("@email", txtEmail.Text);
command.Parameters.AddWithValue("@cont", txtContacto.Text);
command.Parameters.AddWithValue("@ie", txtIdespecialdade.Text);
command.Parameters.AddWithValue("@ih", txtIdhospital.Text);
command.Parameters.AddWithValue("@im", txtIdmedico.Text);
try
{
mdb.openConnection();
if (command.ExecuteNonQuery() == 1)
{
MessageBox.Show("Registo editado com sucesso", "Editado", MessageBoxButtons.OK, MessageBoxIcon.Information);
panel3.Visible = false;
}
else
{
MessageBox.Show("ERRO");
}
}
catch (Exception erro)
{
throw erro;
}
finally
{
mdb.closeConnection();
}
}
}
private void Btn5_Click(object sender, EventArgs e)
{
MDB mdb = new MDB();
{
string sql = $@"INSERT INTO `consulta`(`User_id_user`, `nome_completo`, `num_saude`, `contacto`, `Data_nascimento`, `morada`, `nome_especialidade`, `nome_hospital`)VALUES (@iu, @nc, @ns,@cont, @dn, @mor, @ne, @nh )";
MySqlCommand command = new MySqlCommand(sql, mdb.getConnection());
command.Parameters.Add("@nc", MySqlDbType.VarChar).Value = txtNomeCompleto.Text;
command.Parameters.Add("@ns", MySqlDbType.VarChar).Value = txtNSaude.Text;
command.Parameters.Add("@cont", MySqlDbType.VarChar).Value = txtContacto.Text;
command.Parameters.Add("@dn", MySqlDbType.Date).Value = dateTimePicker1.Value;
command.Parameters.Add("@mor", MySqlDbType.VarChar).Value = txtMorada.Text;
command.Parameters.Add("@ne", MySqlDbType.VarChar).Value = cmbespecialidade.Text;
command.Parameters.Add("@nh", MySqlDbType.VarChar).Value = cmbhospital.Text;
command.Parameters.Add("@iu", MySqlDbType.VarChar).Value = UserControl3.passingText10;
try
{
mdb.openConnection();
if (command.ExecuteNonQuery() == 1)
{
MessageBox.Show("Pedido de consulta realizado com sucesso! Abra o seu perfil para ver o estado da sua consulta.", "Mrcação de consulta", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
MessageBox.Show("ERRO");
}
}
catch (Exception erro)
{
throw erro;
}
finally
{
mdb.closeConnection();
}
}
}
private void btnDelete_Click(object sender, EventArgs e)
{
#region DELETE na tabela user
MDB mdb = new MDB();
{
string sql = $@"DELETE FROM user WHERE id_user = @iu ";
MySqlCommand command = new MySqlCommand(sql, mdb.getConnection());
command.Parameters.AddWithValue("@iu", txtIduser.Text);
try
{
mdb.openConnection();
if (command.ExecuteNonQuery() == 1)
{
MessageBox.Show("Utilizador eliminado com sucesso", "Eliminar", MessageBoxButtons.OK, MessageBoxIcon.Information);
// panel3.Visible = false;
txtIduser.Text = "";
txtNomecompleto.Text = "";
txtNomeUser.Text = "";
txtEmail.Text = "";
txtPasse.Text = "";
}
else
{
MessageBox.Show("ERRO");
}
}
catch (Exception erro)
{
throw erro;
}
finally
{
mdb.closeConnection();
}
}
#endregion
}
private void btnRegister_Click_1(object sender, EventArgs e)
{
//adicionar novo user
MDB mdb = new MDB();
{
string sql = $@"INSERT INTO `user`(`nome_completo`, `email`, `user`, `passe`) VALUES (@nc, @email, @usn, @pass)";
MySqlCommand command = new MySqlCommand(sql, mdb.getConnection());
command.Parameters.Add("@nc", MySqlDbType.VarChar).Value = TextboxCompleteName.Text;
command.Parameters.Add("@email", MySqlDbType.VarChar).Value = TextboxEmail.Text;
command.Parameters.Add("@usn", MySqlDbType.VarChar).Value = TextboxUserName.Text;
command.Parameters.Add("@pass", MySqlDbType.VarChar).Value = TextboxPassword.Text;
try
{
//abrir conecção
mdb.openConnection();
//verificar se as textbox teem valores padrao
if (!VerificarValoresTextBoxes())
{
//verificar se as passwords são iguais
if (TextboxPassword.Text.Equals(TextboxPasswordConfirm.Text))
{
//verificar email
if (ValidarEmail(TextboxEmail.Text))
{
// verificar se o username ja existe
if (verificarUserName())
{
MessageBox.Show("Esse nome de utilizador já existe. Experimente outro", " Username Duplicado ", MessageBoxButtons.OKCancel, MessageBoxIcon.Error);
}
else
{
//consultar os dados
if (command.ExecuteNonQuery() == 1)
{
MessageBox.Show("A sua conta foi criada com sucesso", "Conta criada", MessageBoxButtons.OK, MessageBoxIcon.Information);
Entrar entrarform = new Entrar();
this.Hide();
entrarform.Show();
}
else
{
MessageBox.Show("ERRO");
}
}
}
else
{
MessageBox.Show("Email com formato incorreto!", "ef3.Cinco");
}
}
else
{
MessageBox.Show("As palavras-passe não correspondiam. Tente novamente", "Erro Passe", MessageBoxButtons.OKCancel, MessageBoxIcon.Error);
}
}
else
{
MessageBox.Show("Preencha os campos com informação valida", "Empty Data", MessageBoxButtons.OKCancel, MessageBoxIcon.Error);
}
}
catch (Exception erro)
{
throw erro;
}
finally
{
mdb.closeConnection();
}
}
//VERIFICAR SE O USERNAME JA EXISTE
Boolean verificarUserName()
{
String username = TextboxUserName.Text;
DataTable table = new DataTable();
MySqlDataAdapter adapter = new MySqlDataAdapter();
MySqlCommand command = new MySqlCommand("SELECT * FROM `user` WHERE `user`= @usn ", mdb.getConnection());
command.Parameters.Add("@usn", MySqlDbType.VarChar).Value = username;
adapter.SelectCommand = command;
adapter.Fill(table);
//ver se o username existe na base de dados
if (table.Rows.Count > 0)
{
return(true);
}
else
{
return(false);
}
}
//verificar se textbox contém os valores padrão
Boolean VerificarValoresTextBoxes()
{
String completename = TextboxCompleteName.Text;
String email = TextboxEmail.Text;
String uname = TextboxUserName.Text;
String pass = TextboxPassword.Text;
if (completename.Equals("nome completo") || email.Equals("email") || uname.Equals("nome utilizador") || pass.Equals("palavra-passe"))
{
return(true);
}
else
{
return(false);
}
}
}
