/// <summary> /// Конструктор, вызываемый при нажатии "Добавить" /// </summary> /// <param name="data_module"></param> public add_diet_in_food(db_connect data_module) { InitializeComponent(); //если текст бокс с именем блюда пустой, то блокируем доступ к текст боксам с номером карты и номерами диет if (lb_food_name.Text == "") { tb_card_numb.Enabled = false; lb_diet_numb.Enabled = false; } //получаем список блюд class_diet_in_food[] food = new class_diet_in_food[512]; food = Program.add_read_module.get_list_food_name(); if (food != null) { this.lb_food_name.Items.Clear(); for (int i = 1; i < food.Count(); i++) { if (food[i] != null)//если запрос не пустой, то заполняем лист бокс списком блюд { this.lb_food_name.Items.Add(food[i].food_name); } else { break; } } } this._data_module = data_module; this.set_state("NEW"); }
/// <summary> /// Возвращает номер диеты и id для редактировани диеты в блюде /// </summary> /// <returns></returns> public class_diet_in_food[] get_list_diet(int _id_card) { class_diet_in_food[] diet = new class_diet_in_food[512]; string query = "select NumOfDiet, ID_Diets from Diets " + "where ID_Diets not in " + "(select ID_Diets from Food_In_Diets FID " + "join Cards C on C.Id_Cards = FID.Id_Cards " + "where C.Id_Cards = '" + _id_card + "')"; try { SqlCommand com = Program.data_module._conn.CreateCommand(); com.CommandText = query; SqlDataReader rd = com.ExecuteReader(); int i = 0; while (rd.Read()) { i++; diet[i] = new class_diet_in_food(); diet[i].result = "OK"; diet[i].diet_id = rd.GetInt32(1).ToString(); diet[i].diet_numb = rd.GetString(0); } rd.Close(); rd.Dispose(); com.Dispose(); } catch (Exception ex) { diet[1].result = "ERROR_" + ex.Data + " " + ex.Message; } return diet; }
/// <summary> /// возвращает карту по указанному в параметрах идентификатору (коду) /// </summary> /// <returns></returns> public class_diet_in_food get_list_card_id(int food_id) { class_diet_in_food card = new class_diet_in_food(); string query = "select Number_Card, Id_Cards from Cards C " + "join Foods F on F.ID_food = C.ID_food " + "where F.ID_food = '" + food_id + "'"; try { SqlCommand com = Program.data_module._conn.CreateCommand(); com.CommandText = query; SqlDataReader rd = com.ExecuteReader(); if (rd.Read()) { card = new class_diet_in_food(); card.result = "OK"; card.card_id = rd.GetInt32(1).ToString(); card.card_numb = rd.GetString(0); } rd.Close(); rd.Dispose(); com.Dispose(); } catch (Exception ex) { card.result = "ERROR_" + ex.Data + " " + ex.Message; } return card; }
/// <summary> /// возвращает блюдо по указанному в параметрах идентификатору (коду) /// </summary> /// <returns></returns> public class_diet_in_food[] get_list_food_name() { class_diet_in_food[] food = new class_diet_in_food[512]; string query = "select distinct F.Name_food, F.ID_food from Foods F " + "where F.ID_food in (select ID_food from Cards) " + "and (select COUNT(ID_Diets) from Food_In_Diets FID where FID.ID_food = F.ID_food) < (select COUNT(ID_Diets) from Diets) " + "group by F.Name_food, F.ID_food"; try { SqlCommand com = Program.data_module._conn.CreateCommand(); com.CommandText = query; SqlDataReader rd = com.ExecuteReader(); int i = 0; while (rd.Read()) { i++; food[i] = new class_diet_in_food(); food[i].result = "OK"; food[i].food_id = rd.GetInt32(1).ToString(); food[i].food_name = rd.GetString(0); } rd.Close(); rd.Dispose(); com.Dispose(); } catch (Exception ex) { food[1].result = "ERROR_" + ex.Data + " " + ex.Message; } return food; }
/// <summary> /// Заполняем лист бокс номерами диет при выборе блюда /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void lb_food_name_SelectedValueChanged(object sender, EventArgs e) { //получаем ID выбранного блюда class_food diet = new class_food(); string query = "select ID_food from Foods " + "where Name_food = '" + lb_food_name.Text + "'"; try { SqlCommand com = Program.data_module._conn.CreateCommand(); com.CommandText = query; SqlDataReader rd = com.ExecuteReader(); if (rd.Read()) { diet = new class_food(); diet.food_id = rd.GetInt32(0).ToString(); } rd.Close(); rd.Dispose(); com.Dispose(); } catch (Exception ex) { diet.result = "ERROR_" + ex.Data + " " + ex.Message; } //поолучаем список диет class_diet_in_food[] diet_in_food = new class_diet_in_food[512]; diet_in_food = Program.add_read_module.get_list_diet_id(Convert.ToInt32(diet.food_id)); if (diet_in_food != null) { this.lb_diet_numb.Items.Clear(); for (int i = 1; i < diet_in_food.Count(); i++) { if (diet_in_food[i] != null) { this.lb_diet_numb.Items.Add(diet_in_food[i].diet_numb); } else { break; } } } //Записываем номер карты выбранного блюда в текст бокс class_diet_in_food card = new class_diet_in_food(); card = Program.add_read_module.get_list_card_id(Convert.ToInt32(diet.food_id)); if (card != null) { tb_card_numb.Text = card.card_numb; } }