}//скачать отчет склада
public void DownloadMO()
{
restEntities db = new restEntities();
SaveFileDialog dialog = new SaveFileDialog();
dialog.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
dialog.DefaultExt = ".xls";
dialog.Filter = "Таблицы Excel (*.xls)|*.xls|Все файлы (*.*)|*.*";
dialog.FilterIndex = 1;
dialog.FileName = "Отчет по выручке";
if (dialog.ShowDialog() == DialogResult.OK)
{
var file = new FileStream(dialog.FileName, FileMode.Create, FileAccess.ReadWrite);
var query = (from d in db.dishes
orderby d.date_dish
select new { d.date_dish, d.count_dish }).ToList();
var temp = new MemoryStream(Properties.Resources.temp1, true);
var workbook = new XSSFWorkbook(temp);
var sheet1 = workbook.GetSheet("Лист1");
int row = 2;
foreach (var item in query.OrderBy(o => o.date_dish))
{
var rowInsert = sheet1.CreateRow(row);
rowInsert.CreateCell(1).SetCellValue(Convert.ToString(item.date_dish));
rowInsert.CreateCell(2).SetCellValue(Convert.ToDouble(item.count_dish));
row++;
}
workbook.Write(file);
}
}//скачать отчет по выручке
}//заполнение формы для редачки
public void EditFinal(employee emp, ComboBox comboBox, TextBox t1, TextBox t2, TextBox t3, MaskedTextBox m, TextBox t4, TextBox t5)
{
restEntities db = new restEntities();
var query = (from i in db.position
where i.id_post == comboBox.SelectedIndex + 1
select i.id_post).ToList();
var log = db.users.SingleOrDefault(r => r.id_user == emp.id_user);
var result = db.employee.SingleOrDefault(n => n.id_empl == emp.id_empl);
result.surname_emp = ClassReports.NameNew(t1.Text.ToString());
result.name_emp = ClassReports.NameNew(t2.Text.ToString());
if (t3.Text != "")
{
result.lastname_emp = ClassReports.NameNew(t3.Text.ToString());
} //отчество
else
{
result.lastname_emp = t3.Text.ToString();
}
result.id_post = Convert.ToInt32(query[0]);
result.date_emp = Convert.ToDateTime(m.Text);
log.login_user = t4.Text.ToString();
log.pass_user = t5.Text.ToString();
db.SaveChanges();
}//сохранение обновленных значений
}//видимость кнопок для меню
public void Test(int i, DataGridView dataGridView, int n)
{
restEntities db = new restEntities();
List <order11> order1;
order1 = (from or in db.order11Set
select or).ToList();
order11 o1 = new order11 {
id_position = 0, id_menu = i, data_order1 = DateTime.Now, table_n = n
};
db.order11Set.Add(o1);
db.SaveChanges();
var query = (from t in order1
join g in db.menu on t.id_menu equals g.id_menu
where t.table_n == n
select new { g.name_item }).ToList();
dataGridView.DataSource = query;
dataGridView.Columns[0].HeaderText = "Название";
Update(n, dataGridView);
}
public void Conc(DataGridView dataGridView)
{
restEntities db = new restEntities();
var query = (from e in db.employee
join p in db.position on e.id_post equals p.id_post
join u in db.users on e.id_user equals u.id_user
select new
{
e.id_empl,
e.surname_emp,
e.name_emp,
e.lastname_emp,
e.date_emp,
p.name_post,
p.salary,
u.login_user,
u.pass_user
}).ToList();
dataGridView.DataSource = query;
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.Columns[8].HeaderText = "Пароль";
}//вывод списка сотрудников
public void InfOrd(DataGridView dataGridView, int n)
{
restEntities db = new restEntities();
List <menu> menulist;
menulist = (from men in db.menu
select men).ToList();
//var query = (from t in order1
// join g in db.menu on t.id_menu equals g.id_menu
// where t.table_n == n
// select new { g.name_item, g.calories }).ToList();
//dataGridView.DataSource = query;
//dataGridView.Columns[0].HeaderText = "Название";
var query = (from g in menulist
join men in db.order11Set on g.id_menu equals men.id_menu
where men.table_n == n
select new { g.name_item, g.active, g.calories, g.composition, g.gm, g.price }).ToList();
dataGridView.DataSource = query;
dataGridView.Columns[0].HeaderText = "Название";
dataGridView.Columns[1].HeaderText = "Наличие";
dataGridView.Columns[2].HeaderText = "Калории";
dataGridView.Columns[3].HeaderText = "Состав";
dataGridView.Columns[4].HeaderText = "Вес";
dataGridView.Columns[5].HeaderText = "Цена";
}
public void Ent(string a, string b)
{
restEntities db = new restEntities();
var query = (from q in db.users
where (q.login_user == a && q.pass_user == b)
select new { q.role_user }).ToList();
var f = db.users.SingleOrDefault(p => p.login_user == a && p.pass_user == b);
if (query.Count == 1)
{
if (f.role_user == "ofic")
{
ofic of = new ofic();
of.Show();
}
else
{
if (f.role_user == "cook")
{
cook c = new cook();
c.Show();
}
else
{
if (f.role_user == "bar")
{
bar c = new bar();
c.Show();
}
else
{
if (f.role_user == "admin")
{
admin ad = new admin();
ad.Show();
}
else
{
if (f.role_user == "manager")
{
manager c = new manager();
c.Show();
}
}
}
}
}
}
else
{
MessageBox.Show("Неверный логин или пароль!");
Form1 fofm = new Form1();
fofm.Show();
}
}
}//вывод активных заказов для кухни
public void ShowOrdersB(DataGridView dataGridView)
{
restEntities db = new restEntities();
var query = (from a in db.order11Set
join f in db.menu on a.id_menu equals f.id_menu
where f.bar_kitchen == "bar"
select new { f.name_item }).ToList();
dataGridView.DataSource = query;
dataGridView.Columns[0].HeaderText = "Название";
}//вывод активных заказов для бара
}//скачать отчет по выручке
public void MoneyEnter(DataGridView dataGridView)
{
restEntities db = new restEntities();
var query = (from e in db.dishes
select new { e.count_dish, e.date_dish }).ToList();
dataGridView.DataSource = query;
dataGridView.Columns[0].HeaderText = "Суммма выручки";
dataGridView.Columns[1].HeaderText = "Дата";
}//вывод отчета по выручке
}//вывод отчета по выручке
public void Sort2(DataGridView dataGridView, DateTime a, DateTime b)
{
restEntities db = new restEntities();
var query = (from e in db.dishes
where e.date_dish >= a && e.date_dish <= b
select new { e.count_dish, e.date_dish }).ToList();
dataGridView.DataSource = query;
dataGridView.Columns[0].HeaderText = "Сумма выручки";
dataGridView.Columns[1].HeaderText = "Дата";
}//сортировка отчета по выручке
//public void Test(DataGridView dataGridView)
//{
// restEntities db = new restEntities();
// List<order11> order1;
// order1 = (from or in db.order11Set
// select or).ToList();
// var query = (from t in order1
// join g in db.menu on t.id_menu equals g.id_menu
// select new { g.name_item }).ToList();
// dataGridView.DataSource = query;
// dataGridView.Columns[1].HeaderText = "Название";
//}
public void Drop(int n, DataGridView dataGridView)
{
restEntities db = new restEntities();
List <order11> order1;
order1 = (from or in db.order11Set
select or).ToList();
db.order11Set.RemoveRange(db.order11Set.Where(w => w.table_n == n));
db.SaveChanges();
Update(n, dataGridView);
}
} //пополнение склада
public void StockEnter(DataGridView dataGridView)
{
restEntities db = new restEntities();
var query = (from e in db.stock
select new { e.product, e.quantity, e.unit, e.summ, e.date_sup }).ToList();
dataGridView.DataSource = query;
dataGridView.Columns[0].HeaderText = "Товар";
dataGridView.Columns[1].HeaderText = "Количество";
dataGridView.Columns[2].HeaderText = "Еденица измерения";
dataGridView.Columns[3].HeaderText = "Итоговая сумма";
dataGridView.Columns[4].HeaderText = "Дата";
}//вывод отчета со склада
public string Itog(int m)
{
restEntities db = new restEntities();
List <order11> summ;
summ = (from s in db.order11Set
select s).ToList();
var query = (from g in summ
join l in db.menu on g.id_menu equals l.id_menu
where g.table_n == m
select new { l.price }).ToList();
return(query.Sum(n => n.price).ToString());
}
}//вывод отчета со склада
public void Sort1(DataGridView dataGridView, DateTime a, DateTime b)
{
restEntities db = new restEntities();
var query = (from e in db.stock
where e.date_sup >= a && e.date_sup <= b
select new { e.product, e.quantity, e.unit, e.summ, e.date_sup }).ToList();
dataGridView.DataSource = query;
dataGridView.Columns[0].HeaderText = "Товар";
dataGridView.Columns[1].HeaderText = "Количество";
dataGridView.Columns[2].HeaderText = "Еденица измерения";
dataGridView.Columns[3].HeaderText = "Итоговая сумма";
dataGridView.Columns[4].HeaderText = "Дата";
}//сортировка отчета на складе
}//сохранение обновленных значений
public void Stock(string t1, int t2, DateTime m, string c, double t3)
{
restEntities db = new restEntities();
stock st = new stock
{
product = t1,
quantity = t2,
unit = c,
summ = t3,
date_sup = m,
id_empl = 2
};
db.stock.Add(st);
db.SaveChanges();
} //пополнение склада
public void Buton(Button button)
{
restEntities db = new restEntities();
List <menu> mn;
mn = (from m in db.menu
select m).ToList();
var query = (from t in mn
where (t.active == false && t.name_item == button.Text)
select new { t.name_item }).ToList();
if (query.Count == 1)
{
button.Enabled = false;
}
}//видимость кнопок для меню
public void Update(int n, DataGridView dataGridView)
{
restEntities db = new restEntities();
List <order11> order1;
order1 = (from or in db.order11Set
select or).ToList();
var query = (from t in order1
join g in db.menu on t.id_menu equals g.id_menu
where t.table_n == n
select new { g.name_item }).ToList();
dataGridView.DataSource = query;
dataGridView.Columns[0].HeaderText = "Название";
}
public void Inf(DataGridView dataGridView)
{
restEntities db = new restEntities();
List <menu> menulist;
menulist = (from men in db.menu
select men).ToList();
var query = (from men in menulist
select new { men.name_item, men.active, men.calories, men.composition, men.gm, men.price }).ToList();
dataGridView.DataSource = query;
dataGridView.Columns[0].HeaderText = "Название";
dataGridView.Columns[1].HeaderText = "Наличие";
dataGridView.Columns[2].HeaderText = "Калории";
dataGridView.Columns[3].HeaderText = "Состав";
dataGridView.Columns[4].HeaderText = "Вес";
dataGridView.Columns[5].HeaderText = "Цена";
}
}//вывод списка сотрудников
public void Drop(string s, string n, string l)
{
restEntities db = new restEntities();
var query = (from a in db.employee
where a.surname_emp == s && a.name_emp == n && a.lastname_emp == l
select a).ToList();
if (query.Count == 1)
{
db.employee.RemoveRange(db.employee.Where(w => w.surname_emp == s && w.name_emp == n && w.lastname_emp == l));
db.SaveChanges();
MessageBox.Show("Сотрудник удален");
}
else
{
MessageBox.Show("Такого сотрудника нет в базе");
}
}//удалить сотрудника
} //менять наличие еды для true
public void Report(int n, double SUM)
{
restEntities db = new restEntities();
List <dishes> dish;
dish = (from or in db.dishes
select or).ToList();
dishes d1 = new dishes
{
id_dishes = 0,
date_dish = DateTime.Now,
count_dish = Convert.ToInt32(SUM),
id_empl = 5,
};
db.dishes.Add(d1);
db.SaveChanges();
} //добавление заказа для отчета в dishes
}//добавить нового сотрудника
public void Edit(employee emp, ComboBox comboBox, TextBox t1, TextBox t2, TextBox t3, MaskedTextBox m, TextBox t4, TextBox t5)
{
restEntities db = new restEntities();
var posit_list = (from i in db.position
select i.name_post).Distinct();
var query = (from i in db.position
where i.id_post == emp.id_post
select i.name_post).ToList();
var log = db.users.SingleOrDefault(r => r.id_user == emp.id_user);
comboBox.SelectedItem = query[0];
t1.Text = emp.surname_emp.ToString();
t2.Text = emp.name_emp.ToString();
t3.Text = emp.lastname_emp.ToString();
m.Text = emp.date_emp.ToString();
t4.Text = log.login_user.ToString();
t5.Text = log.pass_user.ToString();
}//заполнение формы для редачки
} //менять наличие еды для false
public void UpdateMenu2(int men)
{
restEntities db = new restEntities();
List <menu> mn;
mn = (from m in db.menu
select m).ToList();
var result = db.menu.SingleOrDefault(x => x.id_menu == men);
if (result.active == true)
{
result.active = false;
db.SaveChanges();
MessageBox.Show("Продукт исключен из меню");
}
else
{
MessageBox.Show("Ошибка! Этого блюда уже нет в наличии");
}
} //менять наличие еды для true
private void редактироватьToolStripMenuItem_Click(object sender, EventArgs e)
{
restEntities db = new restEntities();
List <employee> query = (from j in db.employee
select j).ToList();
try
{
if (dataGridView1.SelectedCells.Count == 1)
{
employee item = query.First(w => w.id_empl.ToString() == dataGridView1.SelectedCells[0]
.OwningRow.Cells[0].Value.ToString());
EditJob edit = new EditJob(item);
edit.Owner = this;
edit.Show();
}
}
catch {
MessageBox.Show("Обновите список сотрудников");
}
}
}//сортировка отчета на складе
public void DownloadST()
{
restEntities db = new restEntities();
SaveFileDialog dialog = new SaveFileDialog();
dialog.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
dialog.DefaultExt = ".xls";
dialog.Filter = "Таблицы Excel (*.xls)|*.xls|Все файлы (*.*)|*.*";
dialog.FilterIndex = 1;
dialog.FileName = "Отчет по закупкам";
if (dialog.ShowDialog() == DialogResult.OK)
{
var file = new FileStream(dialog.FileName, FileMode.Create, FileAccess.ReadWrite);
var query = (from d in db.stock
orderby d.date_sup
select new { d.product, d.quantity, d.unit, d.summ, d.date_sup }).ToList();
var stosk = new MemoryStream(Properties.Resources.stock, true);
var workbook = new XSSFWorkbook(stosk);
var sheet1 = workbook.GetSheet("Лист1");
int row = 2;
foreach (var item in query.OrderBy(o => o.date_sup))
{
var rowInsert = sheet1.CreateRow(row);
rowInsert.CreateCell(0).SetCellValue(Convert.ToString(item.product));
rowInsert.CreateCell(1).SetCellValue(Convert.ToDouble(item.quantity));
rowInsert.CreateCell(2).SetCellValue(Convert.ToString(item.unit));
rowInsert.CreateCell(3).SetCellValue(Convert.ToDouble(item.summ));
rowInsert.CreateCell(4).SetCellValue(Convert.ToString(item.date_sup));
row++;
}
workbook.Write(file);
}
}//скачать отчет склада
}//удалить сотрудника
public void AddJ(string f, string n, string l, DateTime date, int i, string log, string pass)
{
restEntities db = new restEntities();
var query = (from e in db.employee
join p in db.position on e.id_post equals p.id_post
join u in db.users on e.id_user equals u.id_user
select new
{
e.surname_emp,
e.name_emp,
e.lastname_emp,
e.date_emp,
p.name_post,
p.salary,
u.login_user,
u.pass_user
}).ToList();
int user = db.users.Max(us => us.id_user);
employee e1 = new employee
{
surname_emp = f,
name_emp = n,
lastname_emp = l,
date_emp = date,
id_user = user + 1,
id_post = i + 1
};
string role = "";
if (i == 0)
{
role = "manager";
}
else if (i == 1)
{
role = "admin";
}
else if (i == 2)
{
role = "ofic";
}
else if (i == 3)
{
role = "cook";
}
else if (i == 4)
{
role = "bar";
}
users u1 = new users
{
id_user = user + 1,
role_user = role,
login_user = log,
pass_user = pass
};
db.employee.Add(e1);
db.users.Add(u1);
db.SaveChanges();
MessageBox.Show("Сотрудник добавлен");
}//добавить нового сотрудника