// Конструктор, вызываемый при нажатии "Добавить"
public add_food_in_menu(db_connect data_module, string serve_time, int menu_id, int day_id)
{
AddDayID = day_id;
id = menu_id;
serve = serve_time;
InitializeComponent();
class_food_in_menu[] food_in_menu = new class_food_in_menu[512];
food_in_menu = Program.add_read_module.get_foodMenu(serve_time,AddDayID);
if (food_in_menu != null)
{
this.lb_food.Items.Clear();
for (int i = 1; i < food_in_menu.Count(); i++)
{
if (food_in_menu[i] != null)
{
this.lb_food.Items.Add(food_in_menu[i].food);
}
else
{
break;
}
}
}
this._data_module = data_module;
this.set_state("NEW");
}
/// <summary>
/// Возвращает дату создания меню-раскладки
/// </summary>
public class_food_in_menu[] menu_in_day()
{
class_food_in_menu[] menu_in_day = new class_food_in_menu[512];
string query = "select * from Menu_in_day where day_id ='" + AddDayID + "'";
try
{
SqlCommand com = Program.data_module._conn.CreateCommand();
com.CommandText = query;
int i = 0;
SqlDataReader rd = com.ExecuteReader();
while (rd.Read())
{
i = i + 1;
menu_in_day[i] = new class_food_in_menu();
menu_in_day[i].result = "OK";
menu_in_day[i].serve_time = rd.GetDateTime(1).ToShortDateString();
}
rd.Close();
rd.Dispose();
com.Dispose();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message + " " + ex.Data);
return null;
}
return menu_in_day;
}
/// <summary>
/// возвращает названия блюд
/// </summary>
/// <param name="food"></param>
/// <returns></returns>
public class_food_in_menu get_food_in_menu(string food)
{
class_food_in_menu food_in_menu = new class_food_in_menu();
string query = "select F.Name_food "
+ "from Food_in_menu FIM "
+ "join Foods F on F.ID_food = FIM.ID_food";
try
{
SqlCommand com = Program.data_module._conn.CreateCommand();
com.CommandText = query;
SqlDataReader rd = com.ExecuteReader();
if (rd.Read())
{
food_in_menu.result = "OK";
if (rd.IsDBNull(0))
{
food_in_menu.food = "";
}
else
{
food_in_menu.food = rd.GetString(0);
}
}
rd.Close();
rd.Dispose();
com.Dispose();
}
catch (Exception ex)
{
food_in_menu.result = "ERROR_" + ex.Data + " " + ex.Message;
}
return food_in_menu;
}
/// <summary>
/// возвращает блюдо которое еще не добавлено в таблицу Food_in_menu
/// </summary>
/// <param name="serve_time"></param>
/// <param name="day"></param>
/// <returns></returns>
public class_food_in_menu[] get_foodMenu(string serve_time, int day)
{
class_food_in_menu[] food_in_menu = new class_food_in_menu[512];
string query = "Select F.ID_food, F.Name_food "
+ "from Foods F "
+ "where F.ID_food not in (select ID_food from Food_in_menu where Serve_time_of_food = '" + serve_time + "'" + "and day_id='" + day + "'" + ")";
try
{
SqlCommand com = Program.data_module._conn.CreateCommand();
com.CommandText = query;
SqlDataReader rd = com.ExecuteReader();
int i = 0;
while (rd.Read())
{
i++;
food_in_menu[i] = new class_food_in_menu();
food_in_menu[i].result = "OK";
food_in_menu[i].food_id = rd.GetInt32(0).ToString();
food_in_menu[i].food = rd.GetString(1);
}
rd.Close();
rd.Dispose();
com.Dispose();
}
catch (Exception ex)
{
food_in_menu[0].result = "ERROR_" + ex.Data + " " + ex.Message;
}
return food_in_menu;
}
/// <summary>
/// Создаёт запись о блюде в меню
/// </summary>
/// <param name="serve_time"></param>
/// <param name="food"></param>
/// <returns></returns>
public string add_food_in_menu(string serve_time, int menu_id, int day_id, string food, string serve)
{
try
{
class_food_in_menu[] menu = new class_food_in_menu[512];
string query = "Select F.ID_food, MID.day_id, F.Name_food "
+ "from Food_in_menu FIM "
+ "join Foods F on F.ID_food = FIM.ID_Food "
+ "join Menu_in_day MID on MID.day_id = FIM.day_id "
+ "where Serve_time_of_food = '" + serve_time + "' and MID.day_id = '" + day_id + "'";
SqlCommand com = Program.data_module._conn.CreateCommand();
com.CommandText = query;
SqlDataReader rd = com.ExecuteReader();
int i = 0;
while (rd.Read())
{
i++;
menu[i] = new class_food_in_menu();
menu[i].result = "OK";
menu[i].food_id = rd.GetInt32(0).ToString();
menu[i].day_id = rd.GetInt32(1).ToString();
menu[i].food = rd.GetString(2);
if ((menu[i].food == food) && (menu[i].day_id == Convert.ToString(day_id)))
{
rd.Close();
rd.Dispose();
com.Dispose();
}
}
rd.Close();
rd.Dispose();
com.Dispose();
}
catch (Exception)
{
MessageBox.Show("Это блюдо уже есть в меню!", "Внимание!", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
return SQL_Exec(string.Format("insert into Food_in_menu (Serve_time_of_food, ID_menu, ID_food, day_id, count_serve) values('{0}',{1},(select ID_food from Foods where Name_food ='{3}'),{2},'{4}')", ((serve_time.Length == 0) ? "NULL" : serve_time), menu_id, day_id, ((food.Length == 0) ? "NULL" : food), ((serve.Length == 0) ? "NULL" : serve)));
}
/// <summary>
/// Метод возвращает id блюда на обед
/// </summary>
public class_food_in_menu[] food_dinner()
{
class_food_in_menu[] foods_list = new class_food_in_menu[512];
string query = "select * from Food_in_menu where Serve_time_of_food ='Обед' and day_id='" + AddDayID +"'";
string query2 = query;
try
{
SqlCommand com = Program.data_module._conn.CreateCommand();
com.CommandText = query2;
SqlDataReader rd = com.ExecuteReader();
int i = 0;
while (rd.Read())
{
i = i + 1;
foods_list[i] = new class_food_in_menu();
foods_list[i].result = "OK";
foods_list[i].food_id = rd.GetInt32(2).ToString();
}
rd.Close();
rd.Dispose();
com.Dispose();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message + " " + ex.Data);
return null;
}
return foods_list;
}
