Пример #1
0
        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);
        }
Пример #2
0
        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();
        }
Пример #3
0
        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);
        }
Пример #4
0
        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);
        }
Пример #5
0
        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();
        }
Пример #6
0
        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);
        }
Пример #7
0
        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();
        }
Пример #8
0
        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();
        }
Пример #9
0
        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);
        }
Пример #10
0
        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();
        }