private void button15_Click_1(object sender, EventArgs e)
{
MySqlConnection conn = DBUtils.GetDBConnection();
MySqlCommand cmd = conn.CreateCommand();
string drop1 = "TRUNCATE `averages`;";
cmd.Connection.Open();
cmd.CommandText = drop1;
MySqlDataReader reader;
reader = cmd.ExecuteReader();
reader.Read();
MessageBox.Show("Tables Dropped!", "Success", MessageBoxButtons.OK);
}
private void fillInfo(int user_id)
{
SqlConnection conn = DBUtils.GetDBConnection();
string sqlExpression = "SELECT doctors.doc_id, first_name, last_name, mid_name, birth_date, gender, work_begin, work_end FROM users, doctors WHERE user_id=@user_id AND users.doc_id=doctors.doc_id";
try
{
conn.Open();
SqlCommand command = new SqlCommand(sqlExpression, conn);
command.Parameters.AddWithValue("@user_id", user_id);
SqlDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
labelFirstName.Text = reader["first_name"].ToString();
labelLastName.Text = reader["last_name"].ToString();
if (reader["mid_name"] != null)
{
labelMidName.Text = reader["mid_name"].ToString();
}
else
{
labelMidName.Text = "<Нет>";
}
labelBirthDate.Text = DateTime.Parse($"{reader["birth_date"]}").ToString("D");
labelGender.Text = $"{reader["gender"]}";
string workBegin = DateTime.Parse($"{reader["work_begin"]}").ToString("t");
string workEnd = DateTime.Parse($"{reader["work_end"]}").ToString("t");
labelTime.Text = $"{workBegin}-{workEnd}";
labelDocId.Text = reader["doc_id"].ToString();
getAreaNum(Convert.ToInt32(reader["doc_id"]));
}
}
}
catch (Exception ex)
{
MessageBox.Show("Произошла ошибка при заполнении", "Ошибка", MessageBoxButtons.OK);
}
finally
{
conn.Close();
}
}
private void button3_Click_1(object sender, EventArgs e) //вывод всех скринов с таймлайна
{
MySqlConnection conn = DBUtils.GetDBConnection();
MySqlCommand cmd = conn.CreateCommand();
string sql = "SELECT link, vid_id FROM our_screens WHERE vid_id = '" + comboBox1.Text + "'";
dataGridView9.Rows.Clear();
cmd.CommandText = sql;
MySqlDataReader reader;
cmd.Connection.Open();
reader = cmd.ExecuteReader();
string[] linkse = new string[12];
int i = 0;
while ((reader.Read() && (i < 12)))
{
linkse[i] = reader["link"].ToString();
i++;
}
int k = 0;
for (i = 0; i < 4; i++)
{
for (int j = 0; j < 3; j++)
{
dataGridView9.Rows.Add();
dataGridView9[i, j].Value = GetImageFromUrl(linkse[k]);
if (k < 12)
{
k++;
}
else
{
k = 0;
}
}
}
}
private void button3_Click(object sender, EventArgs e)
{
MySqlConnection conn = DBUtils.GetDBConnection();
MySqlCommand cmd1 = conn.CreateCommand();
string sql = "SELECT vid_id, link FROM our_screens GROUP BY vid_id";
cmd1.CommandText = sql;
MySqlDataReader reader;
int rzm2 = dataGridView1.ColumnCount;
cmd1.Connection.Open();
try
{
reader = cmd1.ExecuteReader();
int im = 101;
while (reader.Read())
{
if (im == Convert.ToInt32(reader["vid_id"].ToString()))
{
for (int i = 0; i < dataGridView1.RowCount; i++)
{
dataGridView1.Rows.Add();
dataGridView1[0, i].Value = im;
for (int j = 2; j < dataGridView1.ColumnCount; j++)
{
dataGridView1.Rows[i].Cells[j].Value = reader["link"];
}
}
}
else
{
im = Convert.ToInt32(reader["vid_id"].ToString());
}
}
reader.Close();
}
catch (MySqlException ex)
{
MessageBox.Show("Error: \r\n{ 0}" + ex.ToString(), "Error", MessageBoxButtons.OK);
}
}
private void fillInfo(int user_id)
{
SqlConnection conn = DBUtils.GetDBConnection();
string sqlExpression = "SELECT patients.pat_id, first_name, last_name, mid_name, birth_date, gender, area_num FROM users, patients WHERE user_id=@user_id AND users.pat_id=patients.pat_id";
try
{
conn.Open();
SqlCommand command = new SqlCommand(sqlExpression, conn);
command.Parameters.AddWithValue("@user_id", user_id);
SqlDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
labelCardNum.Text = $"№{reader.GetValue(0)}";
labelFirstName.Text = $"{reader.GetValue(1)}";
labelLastName.Text = $"{reader.GetValue(2)}";
if (reader.GetValue(3) != null)
{
labelMidName.Text = $"{reader.GetValue(3)}";
}
else
{
labelMidName.Text = "<Нет>";
}
labelBirthDate.Text = DateTime.Parse($"{reader.GetValue(4)}").ToString("D");
labelGender.Text = $"{reader.GetValue(5)}";
labelAreaNum.Text = $"№{reader.GetValue(6)}";
}
}
}
catch (Exception ex)
{
MessageBox.Show("Произошла ошибка при заполнении", "Ошибка", MessageBoxButtons.OK);
}
finally
{
conn.Close();
}
}
private void btnSearch_Click(object sender, EventArgs e)
{
if (searchCabinets.Text != "")
{
SqlConnection conn = DBUtils.GetDBConnection();
string sqlExpression = "SELECT cab_num, work_begin, work_end, doctor FROM cabinets WHERE cab_num=@cab_num";
try
{
conn.Open();
SqlCommand command = new SqlCommand(sqlExpression, conn);
command.Parameters.AddWithValue("@cab_num", Convert.ToInt32(searchCabinets.Text));
SqlDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
labelCabNum.Text = $"{reader.GetValue(0)}";
string workBegin = DateTime.Parse($"{reader.GetValue(1)}").ToString("t");
string workEnd = DateTime.Parse($"{reader.GetValue(2)}").ToString("t");
labelTime.Text = $"{workBegin}-{workEnd}";
getDoctor(Convert.ToInt32(reader.GetValue(3)));
}
}
}
catch (Exception ex)
{
MessageBox.Show("Произошла ошибка при заполнении", "Ошибка", MessageBoxButtons.OK);
}
finally
{
conn.Close();
}
}
else
{
MessageBox.Show("Выберите врача!", "Ошибка", MessageBoxButtons.OK);
}
}
private void getAreaNum(int doc_id)
{
SqlConnection conn = DBUtils.GetDBConnection();
string sqlExpression = "SELECT area_num FROM areas WHERE doctor=@doc_id";
try
{
conn.Open();
SqlCommand command = new SqlCommand(sqlExpression, conn);
command.Parameters.AddWithValue("@doc_id", doc_id);
SqlDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
panelAreaNum.Visible = true;
labelArea.Visible = true;
labelAreaNum.Visible = true;
labelAreaNum.Text = $"№{reader.GetInt32(0)}";
}
}
else
{
panelAreaNum.Visible = false;
labelArea.Visible = false;
labelAreaNum.Visible = false;
}
}
catch (Exception ex)
{
MessageBox.Show("Произошла ошибка при заполнении", "Ошибка", MessageBoxButtons.OK);
}
finally
{
conn.Close();
}
}
private bool doesUserExist(int id, bool isDoctor)
{
SqlConnection conn = DBUtils.GetDBConnection();
string sqlExpression = "SELECT doc_id, pat_id FROM users WHERE pat_id=@id";
if (isDoctor)
{
sqlExpression = "SELECT doc_id, pat_id FROM users WHERE doc_id=@id";
}
try
{
conn.Open();
SqlCommand command = new SqlCommand(sqlExpression, conn);
if (isDoctor)
{
command.Parameters.AddWithValue("@id", id);
}
else
{
command.Parameters.AddWithValue("@id", id);
}
SqlDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
return(true);
}
}
catch (Exception ex)
{
MessageBox.Show("Ошибка при загрузке данных", "Ошибка", MessageBoxButtons.OK);
}
finally
{
conn.Close();
}
return(false);
}
//get student information: MASV, HO, TEN, NGAYSINH, NAM, MAKH
public static DataSet GetStudentInformation()
{
SqlConnection scon = DBUtils.GetDBConnection();
String sqlStr = "SELECT MASV, HO, TEN, NGAYSINH, GIOITINH, MAKH FROM SVIEN";
try
{
scon.Open();
SqlDataAdapter daStudent = new SqlDataAdapter(sqlStr, scon);
DataSet dsStudent = new DataSet();
daStudent.Fill(dsStudent);
return(dsStudent);
}
catch (Exception)
{
MessageBox.Show("Open Connection error");
}
finally
{
scon.Close();
}
return(null);
}
private void fillInputs()
{
SqlConnection conn = DBUtils.GetDBConnection();
string sqlExpression = "SELECT doctor, vistime_begin, vistime_end, visit_date, diagnosis, service FROM appointments WHERE diagnosis=@diagnosis";
try
{
conn.Open();
SqlCommand command = new SqlCommand(sqlExpression, conn);
command.Parameters.AddWithValue("@diagnosis", searchDiagnosis.Text);
SqlDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
int doc_id = Convert.ToInt32(reader.GetValue(0));
getDoctor(doc_id);
string workBegin = DateTime.Parse($"{reader.GetValue(1)}").ToString("t");
string workEnd = DateTime.Parse($"{reader.GetValue(2)}").ToString("t");
labelTime.Text = $"{workBegin}-{workEnd}";
labelDate.Text = DateTime.Parse($"{reader.GetValue(3)}").ToString("D");
labelDiagnosis.Text = $"{reader.GetValue(4)}";
int serv = reader.GetInt32(5);
getService(serv);
}
}
}
catch (Exception ex)
{
MessageBox.Show("Произошла ошибка при заполнении", "Ошибка", MessageBoxButtons.OK);
}
finally
{
conn.Close();
}
}
private void button10_Click(object sender, EventArgs e)
{
MySqlConnection conn = DBUtils.GetDBConnection();
MySqlCommand cmd = conn.CreateCommand();
int rows_d = dataGridView7.RowCount;
double buf = 0;
cmd.Connection.Open();
string sql2 = "INSERT INTO `videos`.`compare_buf` (`name`, `value`, `link`, `vid_id`) VALUES (@name, @value, @link, @vid_id );";
for (int i = 0; i < (rows_d - 1); i++)
{
using (MySqlCommand cmd1 = new MySqlCommand(sql2, conn))
{
cmd1.Parameters.AddWithValue("@name", dataGridView7[0, i].Value.ToString());
cmd1.Parameters.AddWithValue("@value", Convert.ToDouble(dataGridView7[1, i].Value.ToString()));
cmd1.Parameters.AddWithValue("@link", dataGridView7[2, i].Value.ToString());
cmd1.Parameters.AddWithValue("@vid_id", dataGridView7[3, i].Value.ToString());
cmd1.ExecuteNonQuery();
}
}
//Подсчет совпадений для каждого скриншота [START]
string sql3 = "SELECT link, value, vid_id, COUNT(link) as comparsions, SUM(value)/COUNT(link) as aver FROM compare_buf GROUP BY link;";
cmd.CommandText = sql3;
MySqlDataReader reader;
int rows_ds = dataGridView8.RowCount;
try
{
reader = cmd.ExecuteReader();
while (reader.Read())
{
if ((Convert.ToInt32(reader["comparsions"].ToString()) > Convert.ToInt32(textBox2.Text)))
{
dataGridView8.Rows.Add(reader["aver"], reader["comparsions"], reader["link"], reader["vid_id"]);
}
}
reader.Close();
dataGridView8.Sort(dataGridView8.Columns[0], ListSortDirection.Descending);
}
catch (MySqlException ex)
{
MessageBox.Show("Error: \r\n{ 0}" + ex.ToString(), "Error", MessageBoxButtons.OK);
}
//Подсчет совпадений для каждого скриншота [END]
int rows_df = dataGridView8.Rows.Count;
string sql33 = "INSERT INTO `videos`.`result` (`ave`, `compare`, `link`, `vid_id`) VALUES (@ave, @compare, @link, @vid_id);";
for (int i = 0; i < rows_df; i++)
{
using (MySqlCommand cmd1 = new MySqlCommand(sql33, conn))
{
cmd1.Parameters.AddWithValue("@ave", Convert.ToDouble(dataGridView8[0, i].Value.ToString()));
cmd1.Parameters.AddWithValue("@compare", Convert.ToInt32(dataGridView8[1, i].Value.ToString()));
cmd1.Parameters.AddWithValue("@link", dataGridView8[2, i].Value.ToString());
cmd1.Parameters.AddWithValue("@vid_id", Convert.ToInt32(dataGridView8[3, i].Value.ToString()));
cmd1.ExecuteNonQuery();
}
}
}
private void button6_Click(object sender, EventArgs e)
{
dataGridView6.Rows.Clear();
MySqlConnection conn = DBUtils.GetDBConnection();
MySqlCommand cmd = conn.CreateCommand();
//Подсчет из обучалки количества совпадений во всей выборочной базе и вывод среднего значения, выполняется запросом ниже
string sql_count = "SELECT name, value, COUNT(name) as count, SUM(value) as suma, SUM(value) / COUNT(name) as aver FROM an_base GROUP BY name";
cmd.Connection = conn;
cmd.CommandText = sql_count;
MySqlDataReader reader;
try
{
cmd.Connection.Open();
reader = cmd.ExecuteReader();
while (reader.Read())
{
string buf = reader["count"].ToString();
int count = Convert.ToInt32(buf);
if (count > Convert.ToInt32(textBox3.Text.ToString()))
{
dataGridView6.Rows.Add(reader["name"], reader["count"], reader["suma"], reader["aver"]);
}
}
reader.Close();
}
catch (MySqlException ex)
{
MessageBox.Show("Error: \r\n{ 0}" + ex.ToString(), "Error", MessageBoxButtons.OK);
}
string sql_our_screens = "SELECT * FROM an_base_our_screens"; // выбираем нашу БД и выводим с нее все
cmd.Connection = conn; //открываем соединение
cmd.CommandText = sql_our_screens; //вбиваем запрос
int rows_c = dataGridView6.RowCount;
//1) Выбираем из базы данных данные и построчно сравниваем с нужными нам значениями которые находятся в таблице - averages
//2) Лучшие результаты вбиваем в dataGridView7
//3) Выбираем лучшие результаты из dataGridView7 (предположительно складываем все основные признаки и выбираем лучшее по среднему)
//4) Выводим в dataGridView8 результат (должно быть только 1-но изображение)
try
{
reader = cmd.ExecuteReader();
while (reader.Read())
{
for (int i = 0; i < (rows_c - 1); i++)
{
if ((reader["name"].ToString() == dataGridView6[0, i].Value.ToString()) && (Convert.ToDecimal(reader["value"].ToString()) > Convert.ToDecimal(dataGridView6[3, i].Value.ToString()))) //поиск совпадающих имен и сравнение value
{
dataGridView7.Rows.Add(reader["name"], reader["value"], reader["img_link"], reader["vid_id"]);
}
}
}
reader.Close();
}
catch (MySqlException ex)
{
MessageBox.Show("Error: \r\n{ 0}" + ex.ToString(), "Error", MessageBoxButtons.OK);
}
}
private async void button9_Click(object sender, EventArgs e)
{
var client = new ClarifaiClient("f5d04074f20343b787952a2605d34b3a");
MySqlConnection conn = DBUtils.GetDBConnection();
MySqlCommand cmd = conn.CreateCommand();
int rown = dataGridView3.RowCount;
progressBar2.Maximum = 1365;
int coun1t = 0;
cmd.Connection.Open();
for (int i = 0; i < (rown - 1); i++)
{
string linked = dataGridView3[1, i].Value.ToString();
//MessageBox.Show(linked.ToString());
var res = await client.PublicModels.GeneralModel.Predict(new ClarifaiURLImage(linked)).ExecuteAsync();
foreach (var concept in res.Get().Data)
{
dataGridView4.Rows.Add(linked, $"{concept.Name}", $"{concept.Value}");
string sql = "INSERT INTO an_base (name,value,img_link) VALUES (@name,@val,@link)";
using (MySqlCommand cmd1 = new MySqlCommand(sql, conn))
{
cmd1.Parameters.AddWithValue("@name", concept.Name);
cmd1.Parameters.AddWithValue("@val", concept.Value);
cmd1.Parameters.AddWithValue("@link", linked);
cmd1.ExecuteNonQuery();
}
}
if (coun1t <= progressBar2.Maximum)
{
progressBar2.Value = coun1t++;
}
}
//MessageBox.Show("sdjs;fk");
string sql_count = "SELECT name, value, COUNT(name) as count, SUM(value) as suma, SUM(value) / COUNT(name) as aver FROM an_base GROUP BY name";
cmd.Connection = conn;
cmd.CommandText = sql_count;
MySqlDataReader reader;
try
{
//cmd.Connection.Open();
reader = cmd.ExecuteReader();
while (reader.Read())
{
string buf = reader["count"].ToString();
int count = Convert.ToInt32(buf);
if (count > 1)
{
dataGridView6.Rows.Add(reader["name"], reader["count"], reader["suma"], reader["aver"]);
}
}
reader.Close();
}
catch (MySqlException ex)
{
MessageBox.Show("Error: \r\n{ 0}" + ex.ToString(), "Error", MessageBoxButtons.OK);
}
}
private void enterButton_Click(object sender, EventArgs e) // при нажатии на кнопку ВОЙТИ
{
if (loginTextBox.Text == "" || passTextBox.Text == "") // заполнение строк логина и пароля обязательно
{
MessageBox.Show("Заполните пустые строки!", "Ошибка", MessageBoxButtons.OK);
}
else
{
SqlConnection conn = DBUtils.GetDBConnection();
string sqlExpression = $"SELECT * FROM users WHERE login = '******' AND password = '******'";
int user_id = 0;
try
{
conn.Open();
SqlCommand command = new SqlCommand(sqlExpression, conn);
SqlDataReader reader = command.ExecuteReader();
if (reader.HasRows) // если пользователь найден
{
int userRole = getUserData(reader, ref user_id); // проверяем его роль и доступ
if (userRole == Constants.ACCESS_ERROR)
{
MessageBox.Show("Доступ запрещен.", "Ошибка", MessageBoxButtons.OK);
}
else if (userRole == Constants.ADMIN_ROLE)
{
clearInputs();
AdminForm adminForm = new AdminForm();
adminForm.Show();
this.Hide();
}
else if (userRole == Constants.USER_ROLE)
{
clearInputs();
CurrentUser.Id = user_id; //сохраняем id текущего пользователя
UserForm userForm = new UserForm();
userForm.Show();
this.Hide();
}
else
{
clearInputs();
CurrentUser.Id = user_id; //сохраняем id текущего пользователя
DocForm docForm = new DocForm();
docForm.Show();
this.Hide();
}
}
else
{
MessageBox.Show("Такого пользователя не существует. Введите данные снова.", "Ошибка", MessageBoxButtons.OK);
}
}
catch (Exception ex)
{
MessageBox.Show($"Error: {ex.Message}", "Error", MessageBoxButtons.OK);
}
finally
{
// Гарантировать освобождение подключения
conn.Close();
}
}
}