public static List <Patient> GetByUserID(int?userID)
{
List <Patient> pl = new();
MySqlCommand command;
command = new("SELECT * FROM patients WHERE doctor_id = @doctor_id", DataBaseHandler.Connection);
command.Parameters.Add("@doctor_id", MySqlDbType.Int32).Value = userID;
DataBaseHandler.Open();
MySqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
pl.Add(new Patient
(
(reader.IsDBNull(0)) ? null : reader.GetFieldValue <int>(0),
(reader.IsDBNull(1)) ? null : reader.GetFieldValue <int>(1),
(reader.IsDBNull(2)) ? null : reader.GetFieldValue <string>(2),
(reader.IsDBNull(3)) ? null : reader.GetFieldValue <string>(3),
(reader.IsDBNull(4)) ? null : reader.GetFieldValue <string>(4),
(reader.IsDBNull(5)) ? null : reader.GetFieldValue <string>(5),
(reader.IsDBNull(5)) ? null : reader.GetFieldValue <string>(6),
(reader.IsDBNull(6)) ? null : JsonSerializer.Deserialize <List <PatientData> >(reader.GetFieldValue <string>(7)),
(reader.IsDBNull(7)) ? null : reader.GetFieldValue <int>(8)
));
}
reader.Close();
DataBaseHandler.Close();
return(pl);
}
public static void AddValues(Patient patient,
double pressureEvening,
double pressureMorning,
double sugarEvening,
double sugarMorning,
double weightEvening,
double weightMorning,
double height,
string notesEvening,
string notesMorning
)
{
patient.PatientDataList.Add(new PatientData(
pressureEvening,
pressureMorning,
sugarEvening,
sugarMorning,
weightEvening,
weightMorning,
height,
notesEvening,
notesMorning
));
MySqlCommand command;
DataBaseHandler.Open();
command = new("UPDATE patients SET attached_data = @attached_data WHERE id = @id;", DataBaseHandler.Connection);
command.Parameters.Add("@attached_data", MySqlDbType.JSON).Value = JsonSerializer.Serialize <List <PatientData> >(patient.PatientDataList);
command.Parameters.Add("@id", MySqlDbType.Int32).Value = patient.ID;
command.ExecuteNonQuery();
DataBaseHandler.Close();
}
public static List <Patient> FindBySomeData(string request)
{
List <Patient> pl = new();
MySqlCommand command;
command = new("SELECT * FROM patients WHERE last_name LIKE @last_name;", DataBaseHandler.Connection);
command.Parameters.Add("@last_name", MySqlDbType.VarChar).Value = "%" + request + "%";
DataBaseHandler.Open();
MySqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
pl.Add(new Patient
(
(reader.IsDBNull(0)) ? null : reader.GetFieldValue <int>(0),
(reader.IsDBNull(1)) ? null : reader.GetFieldValue <int>(1),
(reader.IsDBNull(2)) ? null : reader.GetFieldValue <string>(2),
(reader.IsDBNull(3)) ? null : reader.GetFieldValue <string>(3),
(reader.IsDBNull(4)) ? null : reader.GetFieldValue <string>(4),
(reader.IsDBNull(5)) ? null : reader.GetFieldValue <string>(5),
(reader.IsDBNull(5)) ? null : reader.GetFieldValue <string>(6),
(reader.IsDBNull(6)) ? null : JsonSerializer.Deserialize <List <PatientData> >(reader.GetFieldValue <string>(7)),
(reader.IsDBNull(7)) ? null : reader.GetFieldValue <int>(8)
));
}
reader.Close();
DataBaseHandler.Close();
return(pl);
}
public static List <Patient> GetAll()
{
List <Patient> pl = new();
MySqlCommand command;
command = new("SELECT * FROM patients Limit 50;", DataBaseHandler.Connection);
DataBaseHandler.Open();
MySqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
pl.Add(new Patient
(
(reader.IsDBNull(0)) ? null : reader.GetFieldValue <int>(0),
(reader.IsDBNull(1)) ? null : reader.GetFieldValue <int>(1),
(reader.IsDBNull(2)) ? null : reader.GetFieldValue <string>(2),
(reader.IsDBNull(3)) ? null : reader.GetFieldValue <string>(3),
(reader.IsDBNull(4)) ? null : reader.GetFieldValue <string>(4),
(reader.IsDBNull(5)) ? null : reader.GetFieldValue <string>(5),
(reader.IsDBNull(5)) ? null : reader.GetFieldValue <string>(6),
(reader.IsDBNull(6)) ? null : JsonSerializer.Deserialize <List <PatientData> >(reader.GetFieldValue <string>(7)),
(reader.IsDBNull(7)) ? null : reader.GetFieldValue <int>(8)
));
}
reader.Close();
DataBaseHandler.Close();
return(pl);
}
public static void Remove(Patient patient)
{
MySqlCommand command;
DataBaseHandler.Open();
command = new("DELETE FROM patients WHERE id = @id;", DataBaseHandler.Connection);
command.Parameters.Add("@id", MySqlDbType.Int32).Value = patient.ID;
command.ExecuteNonQuery();
DataBaseHandler.Close();
}
public static void BackUp(string path)
{
List <EmployeeRecovery> employeeRecoveries = new();
List <PatientRecovery> patientRecoveries = new();
MySqlCommand command;
MySqlDataReader reader;
DataBaseHandler.Open();
command = new("SELECT * FROM employees;", DataBaseHandler.Connection);
reader = command.ExecuteReader();
while (reader.Read())
{
employeeRecoveries.Add(new(
reader.GetFieldValue <int>(0), //id
(reader.IsDBNull(1)) ? null : reader.GetFieldValue <string>(1), //cardID
(reader.IsDBNull(2)) ? null : reader.GetFieldValue <string>(2), //pass
(reader.IsDBNull(3)) ? null : reader.GetFieldValue <string>(3), //lastName
(reader.IsDBNull(4)) ? null : reader.GetFieldValue <string>(4), //firstName
(reader.IsDBNull(5)) ? null : reader.GetFieldValue <string>(5), //patronymic
reader.GetFieldValue <int>(6) //role
));
}
reader.Close();
command = new("SELECT * FROM patients;", DataBaseHandler.Connection);
reader = command.ExecuteReader();
while (reader.Read())
{
patientRecoveries.Add(new(
reader.GetFieldValue <int>(0), //id
reader.GetFieldValue <int>(1), //chamber
(reader.IsDBNull(2)) ? null : reader.GetFieldValue <string>(2), //lastName
(reader.IsDBNull(3)) ? null : reader.GetFieldValue <string>(3), //firstName
(reader.IsDBNull(4)) ? null : reader.GetFieldValue <string>(4), //patronymic
(reader.IsDBNull(5)) ? null : reader.GetFieldValue <string>(5), //dateOfBirth
(reader.IsDBNull(6)) ? null : reader.GetFieldValue <string>(6), //sickLeave
(reader.IsDBNull(6)) ? null : JsonSerializer.Deserialize <List <PatientRecoveryData> >(reader.GetFieldValue <string>(7)), //patientDataList
reader.GetFieldValue <int>(8) //role
));
}
reader.Close();
DataBaseHandler.Close();
DataRecovery recovery = new(employeeRecoveries, patientRecoveries);
string str = JsonSerializer.Serialize <DataRecovery>(recovery);
File.WriteAllText(path, str);
}
public static void Drop()
{
MySqlCommand command;
DataBaseHandler.Open();
try
{
command = new("DROP TABLE patients;", DataBaseHandler.Connection);
command.ExecuteNonQuery();
}
catch { }
try
{
command = new("DROP TABLE employees;", DataBaseHandler.Connection);
command.ExecuteNonQuery();
}
catch { }
DataBaseHandler.Close();
}
public static void Modify(Patient patient, int chamber, string lastName, string firstName, string patronymic, string dateOfBirth, string SickLeave)
{
patient.Chamber = chamber;
patient.LastName = lastName;
patient.FirstName = firstName;
patient.Patronymic = patronymic;
patient.DateOfBirth = dateOfBirth;
patient.SickLeave = SickLeave;
MySqlCommand command;
DataBaseHandler.Open();
command = new("UPDATE patients SET chamber = @chamber, last_name = @last_name, first_name = @first_name, patronymic = @patronymic, date_of_birth = @date_of_birth, sick_leave_number = @sick_leave_number;", DataBaseHandler.Connection);
command.Parameters.Add("@chamber", MySqlDbType.Int32).Value = patient.Chamber;
command.Parameters.Add("@last_name", MySqlDbType.VarChar).Value = patient.LastName;
command.Parameters.Add("@first_name", MySqlDbType.VarChar).Value = patient.FirstName;
command.Parameters.Add("@patronymic", MySqlDbType.VarChar).Value = patient.Patronymic;
command.Parameters.Add("@date_of_birth", MySqlDbType.VarChar).Value = patient.DateOfBirth;
command.Parameters.Add("@sick_leave_number", MySqlDbType.VarChar).Value = patient.SickLeave;
command.ExecuteNonQuery();
DataBaseHandler.Close();
}
public static Patient Add(int?chamber, string lastName, string firstName, string patronymic, string dateOfBirth, string sickLeave)
{
Patient p = new(chamber, lastName, firstName, patronymic, dateOfBirth, sickLeave);
MySqlCommand command;
MySqlDataReader reader;
DataBaseHandler.Open();
command = new("SELECT id FROM patients WHERE sick_leave_number = @sick_leave_number;", DataBaseHandler.Connection);
command.Parameters.Add("@sick_leave_number", MySqlDbType.VarChar).Value = p.SickLeave;
reader = command.ExecuteReader();
if (reader.HasRows)
{
reader.Close();
DataBaseHandler.Close();
return(null);
}
DataBaseHandler.Close();
DataBaseHandler.Open();
command = new("INSERT INTO patients(chamber, last_name, first_name, patronymic, date_of_birth, sick_leave_number, attached_data, doctor_id)" +
"VALUES(@chamber, @last_name, @first_name, @patronymic, @date_of_birth, @sick_leave_number, @attached_data, @doctor_id);", DataBaseHandler.Connection);
command.Parameters.Add("@chamber", MySqlDbType.Int32).Value = p.Chamber;
command.Parameters.Add("@last_name", MySqlDbType.VarChar).Value = p.LastName;
command.Parameters.Add("@first_name", MySqlDbType.VarChar).Value = p.FirstName;
command.Parameters.Add("@patronymic", MySqlDbType.VarChar).Value = p.Patronymic;
command.Parameters.Add("@date_of_birth", MySqlDbType.VarChar).Value = p.DateOfBirth;
command.Parameters.Add("@sick_leave_number", MySqlDbType.VarChar).Value = p.SickLeave;
command.Parameters.Add("@attached_data", MySqlDbType.JSON).Value = JsonSerializer.Serialize <List <PatientData> >(p.PatientDataList);
command.Parameters.Add("@doctor_id", MySqlDbType.Int32).Value = p.DoctorID;
command.ExecuteNonQuery();
p.ID = (int)command.LastInsertedId;
DataBaseHandler.Close();
return(p);
}
public static void Restore(string path)
{
string str;
try
{
str = File.ReadAllText(path);
}
catch (Exception)
{
return;
}
DataRecovery recovery = JsonSerializer.Deserialize <DataRecovery>(str);
MySqlCommand command;
DataBaseHandler.Open();
try
{
command = new("DROP TABLE patients;", DataBaseHandler.Connection);
command.ExecuteNonQuery();
}
catch { }
try
{
command = new("DROP TABLE employees;", DataBaseHandler.Connection);
command.ExecuteNonQuery();
}
catch { }
command = new("CREATE TABLE employees(" +
"id INT PRIMARY KEY AUTO_INCREMENT," +
"card_id VARCHAR(10) UNIQUE not null," +
"pass VARCHAR(20) not null," +
"last_name VARCHAR(20) not null," +
"first_name VARCHAR(20) not null," +
"patronymic VARCHAR(20)," +
"role INT not null DEFAULT(0)" +
"); ", DataBaseHandler.Connection);
command.ExecuteNonQuery();
foreach (EmployeeRecovery item in recovery.EmployeeRecoveries)
{
command = new("INSERT INTO employees(id, card_id,pass,last_name,first_name, patronymic) VALUES(" +
"@id, @card_id, @pass, @last_name, @first_name, @patronymic);", DataBaseHandler.Connection);
command.Parameters.Add("@id", MySqlDbType.Int32).Value = item.ID;
command.Parameters.Add("@card_id", MySqlDbType.VarChar).Value = item.CardID;
command.Parameters.Add("@pass", MySqlDbType.VarChar).Value = item.Pass;
command.Parameters.Add("@last_name", MySqlDbType.VarChar).Value = item.LastName;
command.Parameters.Add("@first_name", MySqlDbType.VarChar).Value = item.FirstName;
command.Parameters.Add("@patronymic", MySqlDbType.VarChar).Value = item.Patronymic;
command.ExecuteNonQuery();
}
command = new("CREATE TABLE patients(" +
"id INT AUTO_INCREMENT," +
"chamber INT," +
"last_name VARCHAR(20) not null," +
"first_name VARCHAR(20) not null," +
"patronymic VARCHAR(20)," +
"date_of_birth VARCHAR(20)," +
"sick_leave_number VARCHAR(30) not null," +
"attached_data JSON," +
"doctor_id INT," +
"PRIMARY KEY(id)," +
"FOREIGN KEY(doctor_id) REFERENCES employees(id) ON DELETE SET null" +
"); ", DataBaseHandler.Connection);
command.ExecuteNonQuery();
foreach (PatientRecovery item in recovery.PatientRecoveries)
{
command = new("INSERT INTO patients(chamber, last_name, first_name, patronymic, date_of_birth, sick_leave_number, attached_data, doctor_id)" +
"VALUES(@chamber, @last_name, @first_name, @patronymic, @date_of_birth, @sick_leave_number, @attached_data, @doctor_id);", DataBaseHandler.Connection);
command.Parameters.Add("@chamber", MySqlDbType.Int32).Value = item.Chamber;
command.Parameters.Add("@last_name", MySqlDbType.VarChar).Value = item.LastName;
command.Parameters.Add("@first_name", MySqlDbType.VarChar).Value = item.FirstName;
command.Parameters.Add("@patronymic", MySqlDbType.VarChar).Value = item.Patronymic;
command.Parameters.Add("@date_of_birth", MySqlDbType.VarChar).Value = item.DateOfBirth;
command.Parameters.Add("@sick_leave_number", MySqlDbType.VarChar).Value = item.SickLeave;
command.Parameters.Add("@attached_data", MySqlDbType.JSON).Value = JsonSerializer.Serialize <List <PatientRecoveryData> >(item.PatientDataList);
command.Parameters.Add("@doctor_id", MySqlDbType.Int32).Value = item.DoctorID;
command.ExecuteNonQuery();
}
DataBaseHandler.Close();
}
