public ListView loadListViewVisits(string pat_id, ListView lvv)
{
try
{
lvv.Items.Clear();
MySqlComm mysql = new MySqlComm();
MySqlConnectionDatabase conn = new MySqlConnectionDatabase();
mysql = conn.connection();
mysql.open();
string query = "SELECT id, date FROM patient_visits " +
"WHERE patient_id=@pat_id";
cmd = mysql.getConnect(query);
cmd.Parameters.AddWithValue("@pat_id", pat_id);
MySqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
ListViewItem lvi = new ListViewItem();
lvi.Text = dr["id"].ToString();
lvi.SubItems.Add(dr["date"].ToString());
lvv.Items.Add(lvi);
}
dr.Close();
mysql.close();
return(lvv);
}
catch (Exception e)
{
MessageBox.Show("Hiba az adatbázis elérésekor: " + e, "Adatbázis HIBA!", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(lvv);
}
}
public ListView loadListViewPatient(ListView lvp)
{
try
{
lvp.Items.Clear();
MySqlComm mysql = new MySqlComm();
pModel = new PatientModel();
MySqlConnectionDatabase conn = new MySqlConnectionDatabase();
mysql = conn.connection();
mysql.open();
string query = "SELECT * FROM patient;";
cmd = mysql.getConnect(query);
MySqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
ListViewItem lvi = new ListViewItem();
lvi.Text = dr["name"].ToString();
lvi.SubItems.Add(dr["birthdate"].ToString());
lvi.SubItems.Add(dr["tb"].ToString());
lvp.Items.Add(lvi);
}
dr.Close();
mysql.close();
return(lvp);
}
catch (Exception e)
{
MessageBox.Show("Hiba az adatbázis elérésekor: " + e, "Adatbázis HIBA!", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(lvp);
}
}
/// <summary>
/// Létrehozza a patient_sec adattáblát
/// </summary>
/// <param name="tb">TB number</param>
/// <param name="birthPlace">Birth place</param>
/// <param name="addressZip">Address zip code</param>
/// <param name="addressCity">Address city</param>
/// <param name="addressStreet">Address street</param>
/// <param name="addressNumber">Address house number</param>
/// <param name="phone">Phone number</param>
/// <param name="email">Email address</param>
/// <param name="motherName">Mother name</param>
/// <param name="birthName">Birth Name</param>
public void saveNewPatientSecData(string tb, string birthPlace, string addressZip, string addressCity, string addressStreet, string addressNumber, string phone, string email, string motherName, string birthName, string comment)
{
try
{
MySqlComm mysql = new MySqlComm();
MySqlConnectionDatabase conn = new MySqlConnectionDatabase();
mysql = conn.connection();
mysql.open();
string query = "INSERT INTO patient_sec(patient_id,birthplace,address,phone,email,mother_name,comment,birth_name)" +
" VALUES((SELECT patient.id FROM patient WHERE patient.tb=@tb),@birthplace,@address,@phone,@email,@mother_name,@comment,@birth_name);";
cmd = mysql.getConnect(query);
cmd.Parameters.AddWithValue("@tb", trimText(tb));
cmd.Parameters.AddWithValue("@birthplace", birthPlace);
cmd.Parameters.AddWithValue("@address", trimText(addressZip) + " " + addressCity + ", " + addressStreet + " " + addressNumber + ".");
cmd.Parameters.AddWithValue("@phone", phone);
cmd.Parameters.AddWithValue("@email", email);
cmd.Parameters.AddWithValue("@mother_name", motherName);
cmd.Parameters.AddWithValue("@comment", comment);
cmd.Parameters.AddWithValue("@birth_name", birthName);
cmd.ExecuteNonQuery();
mysql.close();
}
catch (MySqlException e)
{
MessageBox.Show("Hiba a feltöltésénél!->" + e, "Adatbázis hiba!", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public MySqlCommand getConnect(string query)
{
MySqlComm db = new MySqlComm();
MySqlCommand cmd = new MySqlCommand(query, conn);
return(cmd);
}
public MySqlComm connection()
{
MySqlComm mysql = new MySqlComm();
mysql.setConnServer("localhost", "forproba", "3306", "none");
mysql.setConnUser("root", "");
mysql.makeConn();
return(mysql);
}
public string setWelcomeLabel(int id)
{
try
{
MySqlComm mysql = new MySqlComm();
MySqlConnectionDatabase conn = new MySqlConnectionDatabase();
mysql = conn.connection();
mysql.open();
string query = "SELECT staff.name FROM staff WHERE staff.id=" + id + ";";
return(mysql.getOneData(query));
}
catch (Exception e)
{
MessageBox.Show("Hiba az adatbázis elérésekor: " + e, "Adatbázis HIBA!", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(null);
}
}
public void loadPatientDetail(string tb)
{
try
{
MySqlComm mysql = new MySqlComm();
MySqlConnectionDatabase conn = new MySqlConnectionDatabase();
mysql = conn.connection();
mysql.open();
string query = "SELECT * FROM patient, patient_sec " +
"WHERE patient.tb=@tb " +
"AND patient_sec.patient_id = patient.id;";
cmd = mysql.getConnect(query);
cmd.Parameters.AddWithValue("@tb", tb);
MySqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
name = dr["name"].ToString();
address = dr["address"].ToString();
birthDate = dr["birthdate"].ToString();
birthPlace = dr["birthplace"].ToString();
mother = dr["mother_name"].ToString();
phone = dr["phone"].ToString();
email = dr["email"].ToString();
id = dr["id"].ToString();
comment = dr["comment"].ToString();
if (dr["birth_name"].ToString().Trim() == "")
{
birthName = name;
}
else
{
birthName = dr["birth_name"].ToString();
}
this.tb = tb;
}
dr.Close();
mysql.close();
}
catch (Exception e)
{
MessageBox.Show("Hiba az adatbázis elérésekor: " + e, "Adatbázis HIBA!", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
}
public string getSelectedVisits(string id)
{
try
{
MySqlComm mysql = new MySqlComm();
MySqlConnectionDatabase conn = new MySqlConnectionDatabase();
mysql = conn.connection();
mysql.open();
string query = "SELECT text FROM patient_visits " +
"WHERE id='" + id + "';";
return(mysql.getOneData(query));
}
catch (Exception e)
{
MessageBox.Show("Hiba az adatbázis elérésekor: " + e, "Adatbázis HIBA!", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(id);
}
}
public void deletePatient(string tb)
{
try
{
MySqlComm mysql = new MySqlComm();
MySqlConnectionDatabase conn = new MySqlConnectionDatabase();
mysql = conn.connection();
mysql.open();
string query = "DELETE FROM patient WHERE tb=@tb;";
cmd = mysql.getConnect(query);
cmd.Parameters.AddWithValue("@tb", tb);
cmd.ExecuteNonQuery();
mysql.close();
}
catch (Exception e)
{
MessageBox.Show("Hiba az adatbázis elérésekor: " + e, "Adatbázis HIBA!", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
/// <summary>
/// Save patient details data to database
/// </summary>
/// <param name="name">Patient name</param>
/// <param name="address">Patient address</param>
/// <param name="birthDate">Patient birth date</param>
/// <param name="birthPlace">Patient birth place</param>
/// <param name="birthName">Patient birth name</param>
/// <param name="mother">Patient mother name</param>
/// <param name="tb">Patient tb number</param>
/// <param name="phone">Patient phone number</param>
/// <param name="email">Patient email address</param>
/// <param name="comment">Patient comment</param>
public void savePatientDetails(string name, string address, string birthDate, string birthPlace, string birthName, string mother, string tb, string phone, string email, string comment)
{
try
{
MySqlComm mysql = new MySqlComm();
MySqlConnectionDatabase conn = new MySqlConnectionDatabase();
mysql = conn.connection();
mysql.open();
string query = "UPDATE patient, patient_sec SET " +
"name=@name, " +
"birthdate=@birthdate, " +
"tb=@tb, " +
"birthplace=@birthplace, " +
"address=@address, " +
"phone=@phone, " +
"email=@email, " +
"mother_name=@mother_name, " +
"comment=@comment," +
"birth_name=@birth_name " +
"WHERE patient.id = @id " +
"AND patient.id = patient_sec.patient_id;";
cmd = mysql.getConnect(query);
cmd.Parameters.AddWithValue("@id", id);
cmd.Parameters.AddWithValue("@name", name);
cmd.Parameters.AddWithValue("@birthdate", birthDate);
cmd.Parameters.AddWithValue("@tb", tb);
cmd.Parameters.AddWithValue("@birthplace", birthPlace);
cmd.Parameters.AddWithValue("@address", address);
cmd.Parameters.AddWithValue("@phone", phone);
cmd.Parameters.AddWithValue("@email", email);
cmd.Parameters.AddWithValue("@mother_name", mother);
cmd.Parameters.AddWithValue("@comment", comment);
cmd.Parameters.AddWithValue("@birth_name", birthName);
cmd.ExecuteNonQuery();
mysql.close();
}
catch (Exception e)
{
MessageBox.Show("Hiba az adatbázis frissítésekor: " + e, "Adatbázis HIBA!", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
}
public void loginModel(string userName, string passWord, Form loginF)
{
try
{
MySqlComm mysql = new MySqlComm();
MySqlConnectionDatabase connDb = new MySqlConnectionDatabase();
mysql = connDb.connection();
mysql.open();
string query = "stafflogin";
cmd = mysql.getConnect(query);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("username", userName);
cmd.Parameters.AddWithValue("password", passWord);
int count = int.Parse(cmd.ExecuteScalar().ToString());
if (count == 1)
{
string getID = "SELECT staff.id FROM staff WHERE staff.username=@username;";
cmd = mysql.getConnect(getID);
cmd.Parameters.AddWithValue("@username", userName);
int id = int.Parse(cmd.ExecuteScalar().ToString());
MainForm mainF = new MainForm(id);
mainF.Show();
loginF.Hide();
//MessageBox.Show("Helyes adatok "+id);
mysql.close();
}
else
{
MessageBox.Show("Hibás felhasználóinév vagy jelszó!");
mysql.close();
}
}
catch (Exception e)
{
MessageBox.Show("Hiba az adatbázis elérésekor: " + e, "Adatbázis HIBA!", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
}
/// <summary>
/// Létrehozza a patient adattáblát
/// </summary>
/// <param name="tb">Tb number</param>
/// <param name="nT">Name title</param>
/// <param name="firstName">First name</param>
/// <param name="lastName">Last name</param>
/// <param name="birthDate">Birth date</param>
public void saveNewPatientData(string tb, string firstName, string lastName, string birthDate)
{
try
{
MySqlComm mysql = new MySqlComm();
MySqlConnectionDatabase conn = new MySqlConnectionDatabase();
mysql = conn.connection();
mysql.open();
string query = "INSERT INTO patient(name,birthdate,tb) VALUES(@name,@birthdate,@tb);";
cmd = mysql.getConnect(query);
cmd.Parameters.AddWithValue("@name", firstName + " " + lastName);
cmd.Parameters.AddWithValue("@birthdate", trimText(birthDate));
cmd.Parameters.AddWithValue("@tb", trimText(tb));
cmd.ExecuteNonQuery();
mysql.close();
}
catch (Exception e)
{
MessageBox.Show("Hiba a feltöltésénél: " + e, "Adatbázis HIBA!", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
}
public void setNewVisits(string pat_id, string text, int staff_id)
{
try
{
MySqlComm mysql = new MySqlComm();
MySqlConnectionDatabase conn = new MySqlConnectionDatabase();
mysql = conn.connection();
mysql.open();
string query = "INSERT INTO patient_visits(patient_id,date,text,staff_id) VALUES(@pat_id,CURRENT_TIMESTAMP,@text,@staff_id);";
cmd = mysql.getConnect(query);
cmd.Parameters.AddWithValue("@pat_id", pat_id);
cmd.Parameters.AddWithValue("@text", text);
cmd.Parameters.AddWithValue("@staff_id", staff_id);
cmd.ExecuteNonQuery();
mysql.close();
}
catch (Exception e)
{
MessageBox.Show("Hiba az adatbázis elérésekor: " + e, "Adatbázis HIBA!", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
}
/// <summary>
/// létrehozza a patientweb adattáblát
/// </summary>
/// <param name="tb">TB number</param>
/// <param name="birthDate">Birth date</param>
public void saveNewPatientWebData(string tb, string birthDate)
{
try
{
MySqlComm mysql = new MySqlComm();
MySqlConnectionDatabase conn = new MySqlConnectionDatabase();
mysql = conn.connection();
mysql.open();
string query = "INSERT INTO patient_web(patient_id,username,password)" +
"VALUES((SELECT patient.id FROM patient WHERE patient.tb=@tb),@username,@password);";
cmd = mysql.getConnect(query);
cmd.Parameters.AddWithValue("@tb", trimText(tb));
cmd.Parameters.AddWithValue("@username", trimText(tb));
cmd.Parameters.AddWithValue("@password", Crypter.Blowfish.Crypt(trimText(birthDate)));
cmd.ExecuteNonQuery();
mysql.close();
}
catch (Exception e)
{
MessageBox.Show("Hiba a feltöltésénél: " + e, "Adatbázis HIBA!", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
}