private void buttonSearch_Click(object sender, EventArgs e)
{
try
{
if (textBoxIDDep.Text != "")
{
dataGridView.DataSource = null;
dataGridView.DataSource = DBConnect.ShowDB(String.Format("SELECT Doctors.Surname, Doctors.Name, Doctors.Patronymic, Doctors.[Position], Department.Name FROM Doctors, Department WHERE Doctors.ID_Dep = Department.ID_Dep AND Department.ID_Dep = {0}", Convert.ToInt32(textBoxIDDep.Text)));
dataGridView.Columns[0].HeaderText = "Фамилия";
dataGridView.Columns[1].HeaderText = "Имя";
dataGridView.Columns[2].HeaderText = "Отчество";
dataGridView.Columns[3].HeaderText = "Должность";
dataGridView.Columns[4].HeaderText = "Наименование отделения";
dataGridView.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
buttonDelete.Visible = false;
buttonRecord.Visible = false;
buttonUpdate.Visible = false;
}
else
{
UpdateI();
MessageBox.Show("Заполните поле \"Код отделения\"");
}
}
catch (Exception ex) { MessageBox.Show(ex.Message); }
}
public void setValue()
{
DataTable table1 = DBConnect.ShowDB(String.Format("SELECT ID_Dep FROM Doctors WHERE ID_Doc = {0}", ID_Doctors));
ID_Department = Convert.ToInt32(table1.Rows[0]["ID_Dep"]);
DataTable tableD = DBConnect.ShowDB(String.Format("SELECT Name FROM Department WHERE ID_Dep = {0}", ID_Department));
comboBoxDep.Text = tableD.Rows[0]["Name"].ToString();
DataTable tableP = DBConnect.ShowDB(String.Format("SELECT Name FROM [Procedure] WHERE ID_Procedure = {0}", ID_Procedure));
comboBoxProcedure.Text = tableP.Rows[0]["Name"].ToString();
DataTable tableDoc = DBConnect.ShowDB(String.Format("SELECT Surname, Name, Patronymic FROM Doctors WHERE ID_Doc = {0}", ID_Doctors));
comboBoxDoc.Text = String.Format(tableDoc.Rows[0]["Surname"].ToString() + " " + tableDoc.Rows[0]["Name"].ToString() + " " + tableDoc.Rows[0]["Patronymic"].ToString());
DataTable tablePat = DBConnect.ShowDB(String.Format("SELECT Surname, Name, Patronymic FROM Patients WHERE ID_Pat = {0}", ID_Patients));
comboBoxPat.Text = String.Format(tablePat.Rows[0]["Surname"].ToString() + " " + tablePat.Rows[0]["Name"].ToString() + " " + tablePat.Rows[0]["Patronymic"].ToString());
DataTable tableT = DBConnect.ShowDB(String.Format("SELECT [Time], Data, Status FROM Records WHERE ID_Record = {0}", ID_Record));
time = Convert.ToDateTime(tableT.Rows[0]["Time"]).ToShortTimeString();
comboBoxTime.Text = time;
comboBoxStatus.Text = tableT.Rows[0]["Status"].ToString();
dateTimePicker.Value = Convert.ToDateTime(tableT.Rows[0]["Data"]);
}
private void buttonUpdate_Click(object sender, EventArgs e)
{
if (dateTimePicker.Value.ToString() != null && comboBoxTime.SelectedItem != null && comboBoxPat.SelectedItem != null && comboBoxDoc.SelectedItem != null && comboBoxProcedure.SelectedItem != null && comboBoxDep.SelectedItem != null && comboBoxStatus.SelectedItem != null)
{
try
{
DataTable table1 = DBConnect.ShowDB(String.Format("SELECT ID_Procedure FROM [Procedure] WHERE Name = '{0}'", comboBoxProcedure.SelectedItem.ToString()));
int ID_procedure = Convert.ToInt32(table1.Rows[0]["ID_Procedure"]);
DataTable table2 = DBConnect.ShowDB(String.Format("SELECT ID_Pat FROM Patients"));
int ID_patient = Convert.ToInt32(table2.Rows[comboBoxPat.SelectedIndex]["ID_Pat"]);
DataTable tableD = DBConnect.ShowDB(String.Format("SELECT ID_Dep FROM Department"));
int ID_dep = Convert.ToInt32(tableD.Rows[comboBoxDep.SelectedIndex]["ID_Dep"]);
DataTable table3 = DBConnect.ShowDB(String.Format("SELECT ID_Doc FROM Doctors WHERE ID_Dep = {0}", ID_dep));
int ID_doc = Convert.ToInt32(table3.Rows[comboBoxDoc.SelectedIndex]["ID_Doc"]);
DBConnect.InsertDeleteDB(String.Format("UPDATE Records SET ID_Procedure = {0}, [Time] = '{1}', Data = '{2}', Status = '{3}',ID_Doc ={4}, ID_Pat = {5} WHERE ID_Record = {6} ", ID_procedure, comboBoxTime.SelectedItem.ToString(), dateTimePicker.Value.ToShortDateString(), comboBoxStatus.SelectedItem.ToString(), ID_doc, ID_patient, ID_Record));
MessageBox.Show("Запись успешно изменена!");
this.Close();
}
catch (Exception ex) { MessageBox.Show(ex.Message); }
}
else
{
MessageBox.Show("Заполнены не все поля.");
}
}
void UpdateI()
{
dataGridView.DataSource = DBConnect.ShowDB("SELECT * FROM Department");
dataGridView.Columns[0].HeaderText = "Код отделения";
dataGridView.Columns[1].HeaderText = "Наименование";
dataGridView.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
}
private void Record_Load(object sender, EventArgs e)
{
Ran = new Random((int)DateTime.Now.Ticks);
comboBoxDep.DropDownStyle = System.Windows.Forms.ComboBoxStyle.DropDownList;
comboBoxProcedure.DropDownStyle = System.Windows.Forms.ComboBoxStyle.DropDownList;
comboBoxDoc.DropDownStyle = System.Windows.Forms.ComboBoxStyle.DropDownList;
comboBoxTime.DropDownStyle = System.Windows.Forms.ComboBoxStyle.DropDownList;
comboBoxPat.DropDownStyle = System.Windows.Forms.ComboBoxStyle.DropDownList;
DataTable table = DBConnect.ShowDB("SELECT Name FROM Department");
List <string> sList = new List <string>();
for (int i = 0; i < table.Rows.Count; i++)
{
sList.Add((table.Rows[i]["Name"]).ToString());
}
comboBoxDep.DataSource = sList;
DataTable tableP = DBConnect.ShowDB("SELECT Surname, Name, Patronymic, ID_Pat FROM Patients");
List <string> sListP = new List <string>();
for (int i = 0; i < tableP.Rows.Count; i++)
{
sListP.Add(String.Format((tableP.Rows[i]["Surname"]).ToString() + " " + (tableP.Rows[i]["Name"]).ToString() + " " + (tableP.Rows[i]["Patronymic"]).ToString() + " " + (tableP.Rows[i]["ID_Pat"]).ToString()));
}
comboBoxPat.DataSource = sListP;
}
private void comboBoxDep_SelectedIndexChanged(object sender, EventArgs e)
{
comboBoxProcedure.DataSource = null;
comboBoxDoc.DataSource = null;
DataTable table1 = DBConnect.ShowDB(String.Format("SELECT ID_Dep FROM Department WHERE Name = '{0}'", comboBoxDep.SelectedItem.ToString()));
int ID_D = Convert.ToInt32(table1.Rows[0]["ID_Dep"]);
DataTable table2 = DBConnect.ShowDB(String.Format("SELECT Name FROM [Procedure] WHERE ID_Dep = {0}", ID_D));
List <string> sList = new List <string>();
for (int i = 0; i < table2.Rows.Count; i++)
{
sList.Add((table2.Rows[i]["Name"]).ToString());
}
comboBoxProcedure.DataSource = sList;
DataTable table3 = DBConnect.ShowDB(String.Format("SELECT Surname, Name, Patronymic, [Position] FROM Doctors WHERE ID_Dep = {0}", ID_D));
List <string> sList2 = new List <string>();
for (int i = 0; i < table3.Rows.Count; i++)
{
sList2.Add(String.Format((table3.Rows[i]["Surname"]).ToString() + " " + (table3.Rows[i]["Name"]).ToString() + " " + (table3.Rows[i]["Patronymic"]).ToString() + " " + (table3.Rows[i]["Position"]).ToString()));
}
comboBoxDoc.DataSource = sList2;
}
void UpdateI()
{
dataGridView.DataSource = DBConnect.ShowDB("SELECT * FROM Report");
dataGridView.Columns[0].HeaderText = "Код записи";
dataGridView.Columns[1].HeaderText = "Оплата";
dataGridView.Columns[2].HeaderText = "Комментарий";
dataGridView.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
}
private void buttonNoPay_Click(object sender, EventArgs e)
{
try
{
dataGridView.DataSource = DBConnect.ShowDB(String.Format("SELECT * FROM Report WHERE Payment = '{0}'", "Не оплачено"));
}
catch (Exception ex) { MessageBox.Show(ex.Message); }
}
private void Procedure_Load(object sender, EventArgs e)
{
UpdateI();
Ran = new Random((int)DateTime.Now.Ticks);
DataTable table = DBConnect.ShowDB("SELECT ID_Dep FROM Department");
List <string> sList2 = new List <string>();
for (int i = 0; i < table.Rows.Count; i++)
{
sList2.Add(String.Format((table.Rows[i]["ID_Dep"]).ToString()));
}
comboBoxIDDep.DataSource = sList2;
comboBoxIDDep.DropDownStyle = System.Windows.Forms.ComboBoxStyle.DropDownList;
}
void UpdateI()
{
dataGridView.DataSource = DBConnect.ShowDB("SELECT * FROM [Procedure]");
dataGridView.Columns[0].HeaderText = "Код услуги";
dataGridView.Columns[1].HeaderText = "Наименование";
dataGridView.Columns[2].HeaderText = "Стоимость";
dataGridView.Columns[3].HeaderText = "Код отделения";
dataGridView.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
comboBoxIDDep.Text = "";
textBoxName.Text = "";
textBoxID_Procedure.Text = "";
textBoxCost.Text = "";
}
private void MainForm_Load(object sender, EventArgs e)
{
UpdateI();
comboBoxDep.DropDownStyle = System.Windows.Forms.ComboBoxStyle.DropDownList;
DataTable table = DBConnect.ShowDB("SELECT ID_Dep, Name FROM Department");
List <string> sList = new List <string>();
for (int i = 0; i < table.Rows.Count; i++)
{
sList.Add((table.Rows[i]["ID_Dep"]).ToString() + " " + (table.Rows[i]["Name"]).ToString());
}
comboBoxDep.DataSource = sList;
}
private void buttonShow_Click(object sender, EventArgs e)
{
try
{
string text = comboBoxDep.SelectedItem.ToString();
string[] str = text.Split((string[])null, StringSplitOptions.RemoveEmptyEntries);
dataGridView.DataSource = DBConnect.ShowDB(String.Format("SELECT Procedure.Name, Procedure.Cost, Department.Name FROM Department INNER JOIN[Procedure] ON Department.ID_Dep = Procedure.ID_Dep WHERE(((Procedure.ID_Dep) = {0}))", Convert.ToInt32(str[0])));
dataGridView.Columns[0].HeaderText = "Наименование услуги";
dataGridView.Columns[1].HeaderText = "Стоимость";
dataGridView.Columns[2].HeaderText = "Наименование отделения";
dataGridView.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
buttonDelete.Visible = false;
buttonRecord.Visible = false;
buttonUpdate.Visible = false;
}
catch (Exception ex) { MessageBox.Show(ex.Message); }
}
void UpdateI()
{
dataGridView.DataSource = DBConnect.ShowDB("SELECT * FROM Patients");
dataGridView.Columns[0].HeaderText = "Код клиента";
dataGridView.Columns[1].HeaderText = "Фамилия";
dataGridView.Columns[2].HeaderText = "Имя";
dataGridView.Columns[3].HeaderText = "Отчество";
dataGridView.Columns[4].HeaderText = "Телефон";
dataGridView.Columns[5].HeaderText = "Дата рождения";
textBoxID.Text = "";
maskedTextBoxP.Text = "";
maskedTextBoxData.Text = "";
textBoxF.Text = "";
textBoxN.Text = "";
textBoxP.Text = "";
}
void UpdateI()
{
dataGridView.DataSource = DBConnect.ShowDB("SELECT * FROM Records");
dataGridView.Columns[0].HeaderText = "Код записи";
dataGridView.Columns[1].HeaderText = "Код услуги";
dataGridView.Columns[2].HeaderText = "Время";
dataGridView.Columns[3].HeaderText = "Дата";
dataGridView.Columns[4].HeaderText = "Статус";
dataGridView.Columns[5].HeaderText = "Код врача";
dataGridView.Columns[6].HeaderText = "Код клиента";
dataGridView.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
this.dataGridView.Columns["Time"].DefaultCellStyle.Format = "t";
buttonDelete.Visible = true;
buttonRecord.Visible = true;
buttonUpdate.Visible = true;
}
public static bool Contains(string commC)
{
try
{
DataTable table = DBConnect.ShowDB(commC);
if (table.Rows.Count == 0)
{
return(false);
}
else
{
return(true);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return(true);
}
}
private void buttonRecordPat_Click(object sender, EventArgs e)
{
if (textBoxID_Pat.Text != "")
{
dataGridView.DataSource = null;
dataGridView.DataSource = DBConnect.ShowDB(String.Format("SELECT Patients.Surname, Patients.Name, Patients.Patronymic, Records.Time, Records.Data, Procedure.Name, Records.ID_Record FROM [Procedure] INNER JOIN(Patients INNER JOIN Records ON Patients.ID_Pat = Records.ID_Pat) ON Procedure.ID_Procedure = Records.ID_Procedure WHERE Patients.ID_Pat = {0}", Convert.ToInt32(textBoxID_Pat.Text)));
dataGridView.Columns[0].HeaderText = "Фамилия";
dataGridView.Columns[1].HeaderText = "Имя";
dataGridView.Columns[2].HeaderText = "Отчество";
dataGridView.Columns[3].HeaderText = "Время";
dataGridView.Columns[4].HeaderText = "Дата";
dataGridView.Columns[5].HeaderText = "Наименование процедуры";
dataGridView.Columns[6].HeaderText = "Код записи";
dataGridView.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
this.dataGridView.Columns["Time"].DefaultCellStyle.Format = "t";
}
else
{
UpdateI();
MessageBox.Show("Заполните поле \"Код клиента\"");
}
}
private void buttonAdd_Click(object sender, EventArgs e)
{
if (dateTimePicker.Value.ToString() != null && comboBoxTime.SelectedItem != null && comboBoxPat.SelectedItem != null && comboBoxDoc.SelectedItem != null && comboBoxProcedure.SelectedItem != null && comboBoxDep.SelectedItem != null)
{
int ran = Ran.Next(1, 10000);
//MessageBox.Show(ran.ToString());
if (DBConnect.Contains(String.Format("SELECT Data FROM Records WHERE ID_Record = {0}", ran)) == true)
{
while (DBConnect.Contains(String.Format("SELECT Data FROM Records WHERE ID_Record = {0}", ran)) == true)
{
ran = Ran.Next(1, 10000);
}
}
try
{
DataTable table1 = DBConnect.ShowDB(String.Format("SELECT ID_Procedure FROM [Procedure] WHERE Name = '{0}'", comboBoxProcedure.SelectedItem.ToString()));
int ID_Procedure = Convert.ToInt32(table1.Rows[0]["ID_Procedure"]);
DataTable table2 = DBConnect.ShowDB(String.Format("SELECT ID_Pat FROM Patients"));
int ID_Patient = Convert.ToInt32(table2.Rows[comboBoxPat.SelectedIndex]["ID_Pat"]);
DataTable tableD = DBConnect.ShowDB(String.Format("SELECT ID_Dep FROM Department"));
int ID_Dep = Convert.ToInt32(tableD.Rows[comboBoxDep.SelectedIndex]["ID_Dep"]);
DataTable table3 = DBConnect.ShowDB(String.Format("SELECT ID_Doc FROM Doctors WHERE ID_Dep = {0}", ID_Dep));
int ID_Doc = Convert.ToInt32(table3.Rows[comboBoxDoc.SelectedIndex]["ID_Doc"]);
DBConnect.InsertDeleteDB(String.Format("INSERT INTO Records (ID_Record, ID_Procedure, [Time], Data, Status,ID_Doc, ID_Pat) VALUES ({0},{1},'{2}','{3}','{4}',{5},{6})", ran, ID_Procedure, comboBoxTime.SelectedItem.ToString(), dateTimePicker.Value.ToShortDateString(), "Ожидание", ID_Doc, ID_Patient));
DBConnect.InsertDeleteDB(String.Format("INSERT INTO Report (ID_Record, Payment, Comment) VALUES ({0},'{1}','{2}')", ran, "Не оплачено", ""));
this.Close();
}
catch (Exception ex) { MessageBox.Show(ex.Message); }
}
else
{
MessageBox.Show("Заполнены не все поля.");
}
}
private void buttonRecordDoc_Click(object sender, EventArgs e)
{
if (textBoxID_Doc.Text != "")
{
dataGridView.DataSource = null;
dataGridView.DataSource = DBConnect.ShowDB(String.Format("SELECT Doctors.Surname, Doctors.Name, Doctors.Patronymic, Doctors.[Position], Records.[Time], Records.Data, Records.Status, Records.ID_Record FROM Records, Doctors WHERE Records.Status = '{0}' AND Records.ID_Doc = Doctors.ID_Doc AND Doctors.ID_Doc = {1}", "Ожидание", Convert.ToInt32(textBoxID_Doc.Text)));
dataGridView.Columns[0].HeaderText = "Фамилия";
dataGridView.Columns[1].HeaderText = "Имя";
dataGridView.Columns[2].HeaderText = "Отчество";
dataGridView.Columns[3].HeaderText = "Должность";
dataGridView.Columns[4].HeaderText = "Время";
dataGridView.Columns[5].HeaderText = "Дата";
dataGridView.Columns[6].HeaderText = "Статус";
dataGridView.Columns[7].HeaderText = "Код записи";
dataGridView.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
this.dataGridView.Columns["Time"].DefaultCellStyle.Format = "t";
}
else
{
UpdateI();
MessageBox.Show("Заполните поле \"Код врача\"");
}
}
void UpdateI()
{
dataGridView.DataSource = DBConnect.ShowDB("SELECT * FROM Doctors");
dataGridView.Columns[0].HeaderText = "Код врача";
dataGridView.Columns[1].HeaderText = "Фамилия";
dataGridView.Columns[2].HeaderText = "Имя";
dataGridView.Columns[3].HeaderText = "Отчество";
dataGridView.Columns[4].HeaderText = "Должность";
dataGridView.Columns[5].HeaderText = "Телефон";
dataGridView.Columns[6].HeaderText = "E-mail";
dataGridView.Columns[7].HeaderText = "Код отделения";
dataGridView.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
textBoxID.Text = "";
maskedTextBoxP.Text = "";
textBoxPosition.Text = "";
textBoxF.Text = "";
textBoxN.Text = "";
textBoxP.Text = "";
textBoxE_mail.Text = "";
comboBoxIDDep.Text = "";
}
private void dateTimePicker_ValueChanged(object sender, EventArgs e)
{
if (Data.Count != 0)
{
Data.Clear();
comboBoxTime.DataSource = null;
sListTime.Clear();
}
string text = comboBoxDoc.SelectedItem.ToString();
string[] str = text.Split((string[])null, StringSplitOptions.RemoveEmptyEntries);
string pos = "";
for (int i = 3; i < str.Count(); i++)
{
pos += str[i];
if (i < str.Count() - 1)
{
pos += " ";
}
}
DataTable tableDoc = DBConnect.ShowDB(String.Format("SELECT ID_Doc FROM Doctors WHERE (Surname = '{0}')AND(Name = '{1}')AND(Patronymic = '{2}')AND([Position] LIKE '%{3}%')", str[0], str[1], str[2], pos));
//MessageBox.Show(tableDoc.Rows.Count.ToString());
if (tableDoc.Rows.Count != 0)
{
int ID_Doc = Convert.ToInt32(tableDoc.Rows[0]["ID_Doc"]);
DataTable tableTime = DBConnect.ShowDB(String.Format("SELECT [Time] FROM Records WHERE (ID_Doc = {0}) AND (Data = DateValue('{1}')) ", ID_Doc, dateTimePicker.Value.ToShortDateString()));
// MessageBox.Show("dsfsdfs");
//DataTable tableTime = DBConnect.ShowDB(String.Format("SELECT [Time] FROM Records WHERE (ID_Doc = 8721) AND (Data = DateValue('08.12.2017')) "/*, ID_Doc, dateTimePicker.Value.ToShortDateString()*/));
// MessageBox.Show(tableTime.Rows.Count.ToString());
for (int i = 0; i < tableTime.Rows.Count; i++)
{
sListTime.Add(Convert.ToDateTime(tableTime.Rows[i]["Time"]).ToShortTimeString());
}
}
// comboBoxTime.DataSource = sListTime;
if ((dateTimePicker.Value.ToShortDateString() == DateTime.Now.ToShortDateString()) && (DateTime.Now.Hour > Convert.ToDateTime("8:00").Hour))
{
DateTime dateTime = Convert.ToDateTime(DateTime.Today.ToShortDateString() + "," + DateTime.Now.ToShortTimeString());
var interval = new TimeSpan(0, 30, 0);
if ((dateTime.Minute / interval.Minutes) == 0)
{
string newTime = (dateTime.Hour.ToString() + ":" + interval.Minutes.ToString());
dateTime = Convert.ToDateTime(DateTime.Today.ToShortDateString() + "," + newTime);
}
else
{
dateTime = dateTime.AddHours(1);
dateTime = dateTime.AddMinutes(-dateTime.Minute);
dateTime = Convert.ToDateTime(DateTime.Today.ToShortDateString() + "," + dateTime.ToShortTimeString());
}
while (dateTime != Convert.ToDateTime(dateTimePicker.Value.ToShortDateString() + "," + "20:00"))
{
if (sListTime.Contains(dateTime.ToShortTimeString()) == false)
{
Data.Add(dateTime.ToShortTimeString());
}
dateTime = dateTime + interval;
}
comboBoxTime.DataSource = Data;
}
else
{
DateTime dateTime = Convert.ToDateTime(dateTimePicker.Value.ToShortDateString() + "," + "8:00");
var interval = new TimeSpan(0, 30, 0);
while (dateTime != Convert.ToDateTime(dateTimePicker.Value.ToShortDateString() + "," + "20:00"))
{
if (sListTime.Contains(dateTime.ToShortTimeString()) == false)
{
Data.Add(dateTime.ToShortTimeString());
}
dateTime = dateTime + interval;
}
comboBoxTime.DataSource = Data;
}
}