/// <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;
}
}
