Hospital/hospital/insertPatientCard.cs

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace hospital
{
    public partial class insertPatientCard : Form
    {
        SqlConnection sqlConnection = null;
        SqlCommand a = null;
        SqlDataReader reader = null;

        public insertPatientCard()
        {
            InitializeComponent();
        }
        public static string addPatient(string namePatient, string nameEmployee, string Disease, string Severity)
        {
            try
            {
                SqlConnection sqlConnection = null;
                SqlDataReader reader = null;
                SqlCommand a = null;
                FormCreating.openConnection(ref sqlConnection);
                SqlCommand freeWardsFromDB = new SqlCommand($"select count(*) from wards where isfree like 1", sqlConnection);
                if (Convert.ToInt32(freeWardsFromDB.ExecuteScalar()) != 0)
                {
                    DateTime date1 = DateTime.Today;
                    SqlCommand cardID = new SqlCommand($"select id_card from medical_cards where (surname_patient + ' ' +  name_patient + ' ' + midname_patient) like N'{namePatient}'", sqlConnection);
                    SqlCommand employeeID = new SqlCommand($"select id_employee from employees where (surname_employee + ' ' + name_employee + ' ' + midname_employee) like N'{nameEmployee}'", sqlConnection);
                    SqlCommand diseaseID = new SqlCommand($"select id_disease from diseases where name_disease like N'{Disease}'", sqlConnection);
                    SqlCommand severityID = new SqlCommand($"select id_severity from disease_severity where name_severity like N'{Severity}'", sqlConnection);
                    int[] freeWards = new int[Convert.ToInt32(freeWardsFromDB.ExecuteScalar())];
                    freeWardsFromDB = new SqlCommand($"select number_ward from wards where isfree like 1", sqlConnection);
                    reader = freeWardsFromDB.ExecuteReader();
                    int i = 0;
                    while (reader.Read())
                    {
                        freeWards[i] = Convert.ToInt32(reader.GetString(0));
                        i++;
                    }
                    reader.Close();
                    Random rnd = new Random();
                    int ward = freeWards[rnd.Next(0, freeWards.Length + 1)];
                    a = new SqlCommand($"insert into [patientcards] ([id_card], [id_employee], [id_disease], [id_severity], [id_ward], [date_receipt]) values (@id_card, @id_employee, @id_disease, @id_severity, @id_ward, @date_receipt)", sqlConnection);
                    a.Parameters.AddWithValue("id_card", cardID.ExecuteScalar().ToString());
                    a.Parameters.AddWithValue("id_employee", employeeID.ExecuteScalar().ToString());
                    a.Parameters.AddWithValue("id_disease", diseaseID.ExecuteScalar().ToString());
                    a.Parameters.AddWithValue("id_severity", severityID.ExecuteScalar().ToString());
                    a.Parameters.AddWithValue("id_ward", ward);
                    a.Parameters.AddWithValue("date_receipt", $"{date1.Month}/{date1.Day}/{date1.Year}");

                    Array.Resize(ref auth.data, 6);
                    auth.data[0] = $"Пациент: {namePatient}";
                    auth.data[1] = $"Лечащий врач: {nameEmployee}";
                    auth.data[2] = $"Болезнь: {Disease}";
                    auth.data[3] = $"Тяжесть: {Severity}";
                    auth.data[4] = $"Палата: {ward}";
                    auth.data[5] = $"Дата записи: {date1.Month}/{date1.Day}/{date1.Year}";

                    if (a.ExecuteNonQuery() == 1)
                    {
                        SqlCommand setWardOccupy = new SqlCommand($"update wards set isfree = 0 where number_ward like {ward}", sqlConnection);
                        SqlCommand setIllPatient = new SqlCommand($"update medical_cards set isill = 1 where id_card like {cardID.ExecuteScalar()}", sqlConnection);
                        setIllPatient.ExecuteNonQuery();
                        setWardOccupy.ExecuteNonQuery();
                        return "Добавление данных произошло успешно";
                    }
                    else
                    {
                        return "Произошла ошибка";
                    }
                }
                else
                {
                  return "Пустых палат нет!";
                }
            }
            catch
            {
                return "Произошла ошибка";
            }
        }
        private void add_Click(object sender, EventArgs e)
        {
            try
            {
                SqlCommand freeWardsFromDB = new SqlCommand($"select count(*) from wards where isfree like 1", sqlConnection);
                if (Convert.ToInt32(freeWardsFromDB.ExecuteScalar()) != 0)
                {
                    DateTime date1 = DateTime.Today;
                    SqlCommand cardID = new SqlCommand($"select id_card from medical_cards where (surname_patient + ' ' +  name_patient + ' ' + midname_patient) like N'{patientCB.SelectedItem}'", sqlConnection);
                    SqlCommand employeeID = new SqlCommand($"select id_employee from employees where (surname_employee + ' ' + name_employee + ' ' + midname_employee) like N'{employeeCB.SelectedItem}'", sqlConnection);
                    SqlCommand diseaseID = new SqlCommand($"select id_disease from diseases where name_disease like N'{diseaseCB.SelectedItem}'", sqlConnection);
                    SqlCommand severityID = new SqlCommand($"select id_severity from disease_severity where name_severity like N'{severityCB.SelectedItem}'", sqlConnection);
                    int[] freeWards = new int[Convert.ToInt32(freeWardsFromDB.ExecuteScalar())];
                    freeWardsFromDB = new SqlCommand($"select number_ward from wards where isfree like 1", sqlConnection);
                    reader = freeWardsFromDB.ExecuteReader();
                    int i = 0;
                    while (reader.Read())
                    {
                        freeWards[i] = Convert.ToInt32(reader.GetString(0));
                        i++;
                    }
                    reader.Close();
                    Random rnd = new Random();
                    int ward = freeWards[rnd.Next(0, freeWards.Length + 1)];
                    a = new SqlCommand($"insert into [patientcards] ([id_card], [id_employee], [id_disease], [id_severity], [id_ward], [date_receipt]) values (@id_card, @id_employee, @id_disease, @id_severity, @id_ward, @date_receipt)", sqlConnection);
                    a.Parameters.AddWithValue("id_card", cardID.ExecuteScalar().ToString());
                    a.Parameters.AddWithValue("id_employee", employeeID.ExecuteScalar().ToString());
                    a.Parameters.AddWithValue("id_disease", diseaseID.ExecuteScalar().ToString());
                    a.Parameters.AddWithValue("id_severity", severityID.ExecuteScalar().ToString());
                    a.Parameters.AddWithValue("id_ward", ward);
                    a.Parameters.AddWithValue("date_receipt", $"{date1.Month}/{date1.Day}/{date1.Year}");

                    Array.Resize(ref auth.data, 6);
                    auth.data[0] = $"Пациент: {patientCB.SelectedItem}";
                    auth.data[1] = $"Лечащий врач: {employeeCB.SelectedItem}";
                    auth.data[2] = $"Болезнь: {diseaseCB.SelectedItem}";
                    auth.data[3] = $"Тяжесть: {severityCB.SelectedItem}";
                    auth.data[4] = $"Палата: {ward}";
                    auth.data[5] = $"Дата записи: {date1.Month}/{date1.Day}/{date1.Year}";

                    if (a.ExecuteNonQuery() == 1)
                    {
                        MessageBox.Show("Добавление данных произошло успешно", "Уведомление", MessageBoxButtons.OK, MessageBoxIcon.Information);
                        debugAndTrace.writeInFile("Запись пациента в палату", auth.data);
                        SqlCommand setWardOccupy = new SqlCommand($"update wards set isfree = 0 where number_ward like {ward}", sqlConnection);
                        SqlCommand setIllPatient = new SqlCommand($"update medical_cards set isill = 1 where id_card like {cardID.ExecuteScalar()}", sqlConnection);
                        setIllPatient.ExecuteNonQuery();
                        setWardOccupy.ExecuteNonQuery();
                        this.Close();
                    }
                    else
                    {
                        MessageBox.Show("Произошла ошибка", "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
                        debugAndTrace.writeInFile("Ошибка добавления данных карты пациента", auth.data);
                    }
                }
                else
                {
                    debugAndTrace.writeInFile("Нет пустых палат для записи пациента");
                    MessageBox.Show($"Пустых палат нет!", "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
            }
            catch
            {
                MessageBox.Show("Произошла ошибка", "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
                debugAndTrace.writeInFile("Ошибка добавления данных", auth.data);
            }
        }

        private void insertPatientCard_Load(object sender, EventArgs e)
        {
            try
            {
                FormCreating.openConnection(ref sqlConnection);

                a = new SqlCommand($"select surname_patient + ' ' +  name_patient + ' ' + midname_patient from medical_cards where isill like 0", sqlConnection);
                reader = a.ExecuteReader();
                while (reader.Read())
                {
                    patientCB.Items.Add(reader.GetString(0));
                }
                reader.Close();

                a = new SqlCommand($"select surname_employee + ' ' + name_employee + ' ' + midname_employee from employees", sqlConnection);
                reader = a.ExecuteReader();
                while (reader.Read())
                {
                    employeeCB.Items.Add(reader.GetString(0));
                }
                reader.Close();

                a = new SqlCommand($"select name_disease from diseases", sqlConnection);
                reader = a.ExecuteReader();
                while (reader.Read())
                {
                    diseaseCB.Items.Add(reader.GetString(0));
                }
                reader.Close();

                a = new SqlCommand($"select name_severity from disease_severity", sqlConnection);
                reader = a.ExecuteReader();
                while (reader.Read())
                {
                    severityCB.Items.Add(reader.GetString(0));
                }
                reader.Close();
            }
            catch
            {
                MessageBox.Show("Произошла ошибка", "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
                debugAndTrace.writeInFile("Ошибка добавления данных", auth.data);
            }
        }
    }
}