private void btnAddOrder_Click(object sender, EventArgs e) // Добавление заказа
{
if (cmbxClient.Text == "" || lblRent.Text == "" || !dobavlen)
{
lblbl.Text = '\n' + "Все поля обязательны к заполнению";
}
else
{
using (pp_dbEntities db = new pp_dbEntities())
{
orders ord = new orders
{
client = cmbxClient.Text,
status = "1",
date = DateTime.Now,
rent = DateTime.Now.AddDays(Convert.ToInt32(txtboxRent.Text)),
disklist = SpisokDiskov,
deposit = Convert.ToString(price),
};
try
{
db.orders.Add(ord);
db.SaveChanges();
}
catch (Exception ex)
{
return;
}
}
orderadded = true;
this.Close();
}
}
private void AddOrderForm_FormClosed(object sender, FormClosedEventArgs e)
{
if (SpisokDiskov != null && !orderadded)
{
pp_dbEntities db = new pp_dbEntities();
string s = SpisokDiskov;
int id1 = 0;
int id2 = 0;
String[] words = s.Split(new char[] { ' ' }, StringSplitOptions.RemoveEmptyEntries);
for (int i = 0; i < words.Length - 1; i += 2)
{
int[] array = words[i].Where(x => char.IsNumber(x)).Select(x => x - 48).ToArray();
int[] array2 = words[i + 1].Where(x => char.IsNumber(x)).Select(x => x - 48).ToArray();
for (int j = 0; j < array.Length; j++)
{
id1 = id1 * 10 + array[j];
}
for (int j = 0; j < array2.Length; j++)
{
id2 = id2 * 10 + array2[j];
}
movies mv = db.movies.Where(c => c.Id == id1).FirstOrDefault();
mv.count += id2;
db.Entry(mv).State = System.Data.Entity.EntityState.Modified;
db.SaveChanges();
}
}
}
private void btnRemoveData_Click(object sender, EventArgs e)
{
pp_dbEntities db = new pp_dbEntities();
if (lblData.Text == "Жанр")
{
int id = Convert.ToInt32(dataGridView1.SelectedRows[0].Cells[0].Value.ToString());
genres delete_item = db.genres.Find(id);
db.genres.Remove(delete_item);
db.SaveChanges();
}
else if (lblData.Text == "Страна")
{
int id = Convert.ToInt32(dataGridView1.SelectedRows[0].Cells[0].Value.ToString());
country delete_item = db.country.Find(id);
db.country.Remove(delete_item);
db.SaveChanges();
}
else
{
int id = Convert.ToInt32(dataGridView1.SelectedRows[0].Cells[0].Value.ToString());
agerating delete_item = db.agerating.Find(id);
db.agerating.Remove(delete_item);
db.SaveChanges();
}
zapis();
}
private void cmbxDisk_SelectedIndexChanged(object sender, EventArgs e) //Показывает кол-во доступных дисков в label'ах, при выборе из combobox'а.
{
pp_dbEntities db = new pp_dbEntities();
int ID = Convert.ToInt32(cmbxDisk.SelectedValue.ToString());
movies dsk = db.movies.Where(c => c.Id == ID).FirstOrDefault();
lbldskcount.Text = dsk.count.ToString();
lbldskcount.Visible = true;
label8.Text = "Дисков в наличии:";
}
public void zapis()
{
pp_dbEntities db = new pp_dbEntities();
dataGridView1.DataSource = db.clients.OrderBy(c => c.fullname).ToList <clients>();
dataGridView1.Columns[0].HeaderText = "ФИО";
dataGridView1.Columns[1].HeaderText = "Адрес";
dataGridView1.Columns[2].HeaderText = "Контактный телефон";
dataGridView1.Columns[3].HeaderText = "ЧС";
dataGridView1.Columns[4].Visible = false;
}
private void btnAdd_Click(object sender, EventArgs e)
{
if (txtboxName.Text == "" || txtboxPrice.Text == "" || txtboxCount.Text == "")
MessageBox.Show("Название диска, его цена и количество обязательны к заполнению");
else
{
using (pp_dbEntities db = new pp_dbEntities())
{
movies mv = new movies
{
name = txtboxName.Text,
synopsis = rTxtBoxSynopsis.Text,
genres = cmbxGenre.Text,
director = txtboxDirector.Text,
year = txtboxYear.Text,
agerating = cmbxAgerating.Text,
country = cmbxCounrty.Text,
price = Convert.ToInt32(txtboxPrice.Text),
count = Convert.ToInt32(txtboxCount.Text),
};
try
{
if (!add_or_edit)
{
db.movies.Add(mv);
db.SaveChanges();
}
else
{
movies mv2 = db.movies.Where(c => c.name == name2).FirstOrDefault();
mv2.name = txtboxName.Text;
mv2.synopsis = rTxtBoxSynopsis.Text;
mv2.genres = cmbxGenre.Text;
mv2.director = txtboxDirector.Text;
mv2.year = txtboxYear.Text;
mv2.agerating = cmbxAgerating.Text;
mv2.country = cmbxCounrty.Text;
mv2.price = Convert.ToInt32(txtboxPrice.Text);
mv2.count = Convert.ToInt32(txtboxCount.Text);
db.Entry(mv2).State = System.Data.Entity.EntityState.Modified;
db.SaveChanges();
}
}
catch (Exception ex)
{
return;
}
}
this.Close();
}
}
private void dataGridView1_CellDoubleClick(object sender, DataGridViewCellEventArgs e)
{
pp_dbEntities db = new pp_dbEntities();
int ID = Convert.ToInt32(dataGridView1.SelectedRows[0].Cells[6].Value.ToString());
int CLID = Convert.ToInt32(dataGridView1.SelectedRows[0].Cells[8].Value.ToString());
orders or = db.orders.Find(ID);
clients cl = db.clients.Where(c => c.id == CLID).FirstOrDefault();
string dl = "";
string s = or.disklist.ToString();
bool a = true;
int id3 = 0;
String[] words = s.Split(new char[] { ' ' }, StringSplitOptions.RemoveEmptyEntries);
for (int i = 0; i < words.Length; i++)
{
int[] array = words[i].Where(x => char.IsNumber(x)).Select(x => x - 48).ToArray();
for (int j = 0; j < array.Length; j++)
{
id3 = id3 * 10 + array[j];
}
if (a)
{
movies mv = db.movies.Where(c => c.Id == id3).FirstOrDefault();
dl = dl + mv.name.ToString() + " ";
id3 = 0;
a = false;
}
else
{
dl = dl + id3.ToString() + " шт ";
id3 = 0;
a = true;
}
}
OrderCloseForm close_form = new OrderCloseForm();
close_form.lblcl.Text = or.client.ToString();
close_form.lblContact.Text = cl.contact.ToString();
close_form.lblAddress.Text = cl.address.ToString();
close_form.lbldsk.Text = dl.ToString();
close_form.lbldeposit.Text = or.deposit.ToString();
close_form.lblrent.Text = or.rent.ToString();
close_form.lblid.Text = or.id.ToString();
close_form.ShowDialog();
zapis();
}
public void clientcheck() // Проверка клиента на наличие в черном списке, вывод его контактных данных рядом с его именем.
{
pp_dbEntities db = new pp_dbEntities();
int ID = Convert.ToInt32(cmbxClient.SelectedValue.ToString());
clients cl = db.clients.Where(c => c.id == ID).FirstOrDefault();
lblcontact.Text = cl.contact.ToString();
lblAddress.Text = cl.address.ToString();
if (cl.blackliststatus == 1)
{
if (txtboxRent.Text != "")
{
if (Convert.ToInt32(txtboxRent.Text) > 14)
{
lblbl.Text = "Клиент находится в черном списке" + '\n' + "Максимальный срок заказа 14 дней";
}
else
{
lblbl.Text = "Клиент находится в черном списке";
}
}
else
{
lblbl.Text = "Клиент находится в черном списке";
}
btnAddOrder.Visible = false;
btnAddDisk.Visible = false;
}
else
{
if (txtboxRent.Text != "")
{
if (Convert.ToInt32(txtboxRent.Text) > 14)
{
btnAddOrder.Visible = false;
lblbl.Text = "Максимальный срок заказа 14 дней";
}
else
{
lblbl.Text = "";
btnAddOrder.Visible = true;
btnAddDisk.Visible = true;
}
}
else
{
lblbl.Text = "";
btnAddOrder.Visible = true;
btnAddDisk.Visible = true;
}
}
}
public void zapis()
{
pp_dbEntities db = new pp_dbEntities();
var Movies = db.movies.OrderBy(c => c.name);
dataGridView1.DataSource = Movies.ToList <movies>();
dataGridView1.Columns[0].HeaderText = "Название";
dataGridView1.Columns[1].HeaderText = "Количество";
dataGridView1.Columns[2].HeaderText = "Цена";
for (int i = 3; i <= 9; i++)
{
dataGridView1.Columns[i].Visible = false;
}
}
private void zapis2(List <movies> mv)
{
pp_dbEntities db = new pp_dbEntities();
var Movies = db.movies.OrderBy(c => c.name);
dataGridView1.DataSource = mv;
dataGridView1.Columns[0].HeaderText = "Название";
dataGridView1.Columns[1].HeaderText = "Количество";
dataGridView1.Columns[2].HeaderText = "Цена";
for (int i = 3; i <= 9; i++)
{
dataGridView1.Columns[i].Visible = false;
}
this.Refresh();
}
public void fillchkbox() // Заполнение комбобокса данными из базы данных
{
pp_dbEntities db = new pp_dbEntities();
cmbxGenre.DataSource = db.genres.ToList<genres>();
cmbxGenre.ValueMember = "Id";
cmbxGenre.DisplayMember = "name";
cmbxAgerating.DataSource = db.agerating.ToList<agerating>();
cmbxAgerating.ValueMember = "Id";
cmbxAgerating.DisplayMember = "name";
cmbxCounrty.DataSource = db.country.ToList<country>();
cmbxCounrty.ValueMember = "Id";
cmbxCounrty.DisplayMember = "name";
}
private void txtboxRent_TextChanged(object sender, EventArgs e) // Вывод даты ренты при вводе количества дней и стоимости ренты.
{
DateTime date1 = new DateTime();
if (txtboxRent.Text != "")
{
date1 = DateTime.Now.AddDays(Convert.ToInt32(txtboxRent.Text));
lblRent.Text = date1.ToString("ddd, dd MMM yyy HH’:’mm’:’ss ‘GMT’");
lblPrice.Text = (price / 20 * Convert.ToDouble(txtboxRent.Text)).ToString();
pp_dbEntities db = new pp_dbEntities();
int ID = Convert.ToInt32(cmbxClient.SelectedValue.ToString());
clients cl = db.clients.Where(c => c.id == ID).FirstOrDefault();
if (Convert.ToInt32(txtboxRent.Text) > 14)
{
btnAddOrder.Visible = false;
if (cl.blackliststatus == 0)
{
lblbl.Text = "Максимальный срок заказа 14 дней";
btnAddDisk.Visible = true;
}
else
{
lblbl.Text = "Клиент находится в черном списке" + '\n' + "Максимальный срок заказа 14 дней";
btnAddDisk.Visible = false;
}
}
else
{
if (cl.blackliststatus == 1)
{
lblbl.Text = "Клиент находится в черном списке";
btnAddDisk.Visible = false;
}
else
{
btnAddDisk.Visible = true;
btnAddOrder.Visible = true;
lblbl.Text = "";
}
}
}
else
{
lblPrice.Text = (price / 20).ToString();
}
}
private void btnRemoveClient_Click(object sender, EventArgs e)
{
pp_dbEntities db = new pp_dbEntities();
try
{
int id = Convert.ToInt32(dataGridView1.SelectedRows[0].Cells[4].Value.ToString());
clients delete_item = db.clients.Find(id);
db.clients.Remove(delete_item);
db.SaveChanges();
zapis();
}
catch (Exception ex)
{
return;
}
}
private void AddOrderForm_Activated(object sender, EventArgs e) //Обновление combobox'ов при добавлении клиента
{
fillchkbox();
chkNewClient.Checked = false;
pp_dbEntities db = new pp_dbEntities();
try
{
int intIdt = db.clients.Max(u => u.id); //Поиск клиента с наибольшим ID
clients cl = db.clients.Where(c => c.id == intIdt).FirstOrDefault();
cmbxClient.Text = cl.fullname.ToString();
}
catch (Exception ex)
{
return;
}
}
public void fillchkbox() // Заполнение комбобоксов данными из бд и время.
{
pp_dbEntities db = new pp_dbEntities();
cmbxClient.DataSource = db.clients.OrderBy(c => c.fullname).ToList <clients>();
cmbxClient.ValueMember = "Id";
cmbxClient.DisplayMember = "fullname";
if (cmbxClient.Items == null)
{
clientcheck();
}
cmbxDisk.DataSource = db.movies.OrderBy(c => c.name).ToList <movies>();
cmbxDisk.ValueMember = "Id";
cmbxDisk.DisplayMember = "name";
lblTime.Text = DateTime.Now.ToString("ddd, dd MMM yyy HH’:’mm’:’ss ‘GMT’");
}
private void btnclose_Click(object sender, EventArgs e)
{
int id2 = Convert.ToInt32(lblid.Text.ToString());
pp_dbEntities db = new pp_dbEntities();
orders or = db.orders.Where(c => c.id == id2).FirstOrDefault();
or.closed_date = DateTime.Now;
if (or.status == "1" || or.status == "2")
{
or.status = 0.ToString();
db.Entry(or).State = System.Data.Entity.EntityState.Modified;
db.SaveChanges();
this.Close();
}
else
{
MessageBox.Show("Заказ уже закрыт");
}
}
private void btnAddData_Click(object sender, EventArgs e)
{
pp_dbEntities db = new pp_dbEntities();
if (txtboxData.Text != "")
{
if (lblData.Text == "Жанр")
{
genres gr = new genres
{
name = txtboxData.Text
};
db.genres.Add(gr);
db.SaveChanges();
this.Close();
}
else if (lblData.Text == "Страна")
{
country cr = new country
{
name = txtboxData.Text
};
db.country.Add(cr);
db.SaveChanges();
this.Close();
}
else
{
agerating ar = new agerating
{
name = txtboxData.Text
};
db.agerating.Add(ar);
db.SaveChanges();
this.Close();
}
}
else
{
MessageBox.Show("Поле пустое");
}
}
public void fillchkbox() // Заполнение комбобоксов данными из бд и время.
{
pp_dbEntities db = new pp_dbEntities();
cmbxCounrty.DataSource = db.country.OrderBy(c => c.name).ToList <country>();
cmbxCounrty.ValueMember = "Id";
cmbxCounrty.DisplayMember = "name";
cmbxGenre.DataSource = db.genres.OrderBy(c => c.name).ToList <genres>();
cmbxGenre.ValueMember = "Id";
cmbxGenre.DisplayMember = "name";
cmbxPageRating.DataSource = db.agerating.OrderBy(c => c.name).ToList <agerating>();
cmbxPageRating.ValueMember = "Id";
cmbxPageRating.DisplayMember = "name";
cmbxCounrty.SelectedValue = "";
cmbxPageRating.SelectedValue = "";
cmbxGenre.SelectedValue = "";
}
private void dataGridView1_CellDoubleClick(object sender, DataGridViewCellEventArgs e)
{
pp_dbEntities db = new pp_dbEntities();
int id = Convert.ToInt32(dataGridView1.SelectedRows[0].Cells[9].Value.ToString());
movies mv = db.movies.Find(id);
AddDiskForm edit_form = new AddDiskForm();
edit_form.txtboxName.Text = mv.name;
edit_form.rTxtBoxSynopsis.Text = mv.synopsis;
edit_form.cmbxGenre.Text = mv.genres;
edit_form.txtboxDirector.Text = mv.director;
edit_form.txtboxYear.Text = mv.year;
edit_form.cmbxAgerating.Text = mv.agerating;
edit_form.cmbxCounrty.Text = mv.country;
edit_form.txtboxPrice.Text = Convert.ToString(mv.price);
edit_form.txtboxCount.Text = Convert.ToString(mv.count);
edit_form.ShowDialog();
zapis();
}
public void proverka()
{
pp_dbEntities db = new pp_dbEntities(); // Добавление в список просроченных
var Orders = db.orders.Where(c => c.status == "1"); // Ищем только среди действующих заказов.
orders or = Orders.Where(c => c.rent < DateTime.Now).FirstOrDefault();
if (or != null) // Если есть просроченный заказ то меняем статус на 2
{
or.status = 2.ToString();
db.Entry(or).State = System.Data.Entity.EntityState.Modified;
db.SaveChanges();
}
if (Orders.Where(c => c.rent < DateTime.Now).FirstOrDefault() != null) // Если есть еще просроченные заказы, то вызываем функцию снова.
{
proverka(); // Рекурсия о_О
}
DateTime D = DateTime.Now; // Добавление в список проваленных
D = D.AddDays(-14);
var Orders2 = db.orders.Where(c => c.status == "2"); // Ищем только среди просроченных заказов.
orders or2 = Orders2.Where(c => c.rent < D).FirstOrDefault();
if (or2 != null) // Если есть проваленный заказ то меняем статус на 3
{
or2.status = 3.ToString();
or2.closed_date = DateTime.Now;
clients cl = db.clients.Where(c => c.id == or2.clid).FirstOrDefault();
cl.blackliststatus = 1;
db.Entry(cl).State = System.Data.Entity.EntityState.Modified;
db.SaveChanges();
db.Entry(or2).State = System.Data.Entity.EntityState.Modified;
db.SaveChanges();
}
if (Orders2.Where(c => c.rent < D).FirstOrDefault() != null) // Если есть еще проваленные заказы, то вызываем функцию снова.
{
proverka(); // Рекурсия о_О
}
}
private void dataGridView1_CellDoubleClick(object sender, DataGridViewCellEventArgs e)
{
pp_dbEntities db = new pp_dbEntities();
int id = Convert.ToInt32(dataGridView1.SelectedRows[0].Cells[4].Value.ToString());
clients cl = db.clients.Find(id);
AddClientForm edit_form = new AddClientForm();
edit_form.txtBoxname.Text = cl.fullname;
edit_form.txtboxAddress.Text = cl.address;
edit_form.txtboxphone.Text = cl.contact;
if (cl.blackliststatus == 1)
{
edit_form.chkbxBL.Checked = true;
}
else
{
edit_form.chkbxBL.Checked = false;
}
edit_form.ShowDialog();
zapis();
}
private void zapis()
{
pp_dbEntities db = new pp_dbEntities();
if (lblData.Text == "Жанр")
{
dataGridView1.DataSource = db.genres.ToList <genres>();
dataGridView1.Columns[0].Visible = false;
dataGridView1.Columns[1].HeaderText = "Жанры";
}
else if (lblData.Text == "Страна")
{
dataGridView1.DataSource = db.country.ToList <country>();
dataGridView1.Columns[0].Visible = false;
dataGridView1.Columns[1].HeaderText = "Страны";
}
else
{
dataGridView1.DataSource = db.agerating.ToList <agerating>();
dataGridView1.Columns[0].Visible = false;
dataGridView1.Columns[1].HeaderText = "Возрастные рейтинги";
}
}
public void zapis()
{
pp_dbEntities or = new pp_dbEntities();
dataGridView1.DataSource = or.orders.ToList <orders>();
dataGridView1.Columns[0].HeaderText = "ФИО клиента";
dataGridView1.Columns[1].HeaderText = "Статус";
dataGridView1.Columns[2].HeaderText = "Дата заказа";
dataGridView1.Columns[3].HeaderText = "Срок";
for (int i = 4; i <= 8; i++)
{
dataGridView1.Columns[i].Visible = false;
}
if (chkboxClosedOrder.Checked == true)
{
for (int i = 0; i < dataGridView1.Rows.Count; i++)
{
if (dataGridView1.Rows[i].Cells[1].Value.ToString() == "0" || dataGridView1.Rows[i].Cells[1].Value.ToString() == "3")
{
dataGridView1.Rows[i].Visible = false;
}
}
}
else
{
for (int i = 0; i < dataGridView1.Rows.Count; i++)
{
if (dataGridView1.Rows[i].Cells[1].Value.ToString() == "0" || dataGridView1.Rows[i].Cells[1].Value.ToString() == "3")
{
dataGridView1.Rows[i].Visible = true;
}
}
}
}
private void btnCreateReport_Click(object sender, EventArgs e)
{
pp_dbEntities db = new pp_dbEntities();
var query_closed = db.orders.Where <orders>(f => f.closed_date >= dateTimePickerFrom.Value).Where <orders>(t => t.closed_date <= dateTimePickerTo.Value).Where <orders>(s => s.status == "0");
var query_active = db.orders.Where <orders>(t => t.date <= dateTimePickerTo.Value).Where <orders>(s => s.status == "1");
var query_expired = db.orders.Where <orders>(s => s.status == "2");
var query_failed = db.orders.Where <orders>(s => s.status == "3");
SaveFileDialog saveFileDialog = new SaveFileDialog();
saveFileDialog.CreatePrompt = false;
saveFileDialog.OverwritePrompt = false;
saveFileDialog.FileName = "Отчет от " + DateTime.Now.ToShortDateString();
saveFileDialog.DefaultExt = "xlsx";
saveFileDialog.Filter = "xlsx files (*.xlsx)|*.xlsx|All files (*.*)|*.*";
if (saveFileDialog.ShowDialog() == DialogResult.OK)
{
Excel.Application app = new Excel.Application();
Excel.Workbook workbook = app.Workbooks.Add(Type.Missing);
Excel.Worksheet closed_sheet = null;
app.Visible = false;
//Лист закрытых заказов
closed_sheet = workbook.ActiveSheet;
closed_sheet.Name = "Закрытые заказы";
closed_sheet.Cells[1, 1] = "Выполненные заказы";
closed_sheet.Range[closed_sheet.Cells[1, 1], closed_sheet.Cells[1, 2]].Merge();
closed_sheet.Cells[2, 1] = "ID заказа";
closed_sheet.Cells[2, 2] = "Клиент";
closed_sheet.Cells[2, 3] = "Дата открытия";
closed_sheet.Cells[2, 4] = "Дата закрытия";
Excel.Range cellRange = (Excel.Range)closed_sheet.Cells[1, 1];
Excel.Range rowRange = cellRange.EntireRow;
Excel.Range colRange = cellRange.EntireColumn;
int cellRow = 3;
double cash = 0;
foreach (var orders in query_closed)
{
closed_sheet.Cells[cellRow, 1] = orders.id.ToString();
closed_sheet.Cells[cellRow, 2] = orders.client.ToString();
closed_sheet.Cells[cellRow, 3] = orders.date.ToString();
closed_sheet.Cells[cellRow, 4] = orders.closed_date.ToString();
DateTime a = orders.closed_date.Value;
DateTime b = orders.date.Value;
double ddiff = (a - b).TotalHours / 24;
cash += Convert.ToDouble(Convert.ToDouble(orders.deposit) / 20 * ddiff);
cellRow++;
}
closed_sheet.Cells[cellRow + 1, 1] = "Итого выполнено";
closed_sheet.Cells[cellRow + 1, 2] = "Заработано";
closed_sheet.Cells[cellRow + 2, 1] = cellRow - 3;
closed_sheet.Cells[cellRow + 2, 2] = cash;
Excel.Range fit_cells = closed_sheet.UsedRange;
fit_cells.EntireColumn.AutoFit();
fit_cells.EntireRow.AutoFit();
fit_cells.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
fit_cells.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
//Лист действующих заказов
Excel.Worksheet active_sheet = null;
active_sheet = (Excel.Worksheet)workbook.Worksheets.Add();
active_sheet = workbook.ActiveSheet;
active_sheet.Name = "Действующие заказы";
active_sheet.Cells[1, 1] = "Действующие заказы";
active_sheet.Range[active_sheet.Cells[1, 1], active_sheet.Cells[1, 4]].Merge();
active_sheet.Cells[2, 1] = "ID заказа";
active_sheet.Cells[2, 2] = "Клиент";
active_sheet.Cells[2, 3] = "Дата открытия";
active_sheet.Cells[2, 4] = "Cрок заказа";
cellRange = (Excel.Range)active_sheet.Cells[1, 1];
rowRange = cellRange.EntireRow;
colRange = cellRange.EntireColumn;
cellRow = 3;
foreach (var orders in query_active)
{
active_sheet.Cells[cellRow, 1] = orders.id.ToString();
active_sheet.Cells[cellRow, 2] = orders.client.ToString();
active_sheet.Cells[cellRow, 3] = orders.date.ToString();
active_sheet.Cells[cellRow, 4] = orders.rent.ToString();
cellRow++;
}
active_sheet.Cells[cellRow + 1, 1] = "Итого действует";
active_sheet.Cells[cellRow + 2, 1] = cellRow - 3;
fit_cells = active_sheet.UsedRange;
fit_cells.EntireColumn.AutoFit();
fit_cells.EntireRow.AutoFit();
fit_cells.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
fit_cells.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
//Лист просроченных заказов
Excel.Worksheet expired_sheet = null;
expired_sheet = (Excel.Worksheet)workbook.Worksheets.Add();
expired_sheet = workbook.ActiveSheet;
expired_sheet.Name = "Просроченные заказы";
expired_sheet.Cells[1, 1] = "Просроченные заказы";
expired_sheet.Range[expired_sheet.Cells[1, 1], expired_sheet.Cells[1, 4]].Merge();
expired_sheet.Cells[2, 1] = "ID заказа";
expired_sheet.Cells[2, 2] = "Клиент";
expired_sheet.Cells[2, 3] = "Дата открытия";
expired_sheet.Cells[2, 4] = "Cрок заказа";
cellRange = (Excel.Range)expired_sheet.Cells[1, 1];
rowRange = cellRange.EntireRow;
colRange = cellRange.EntireColumn;
cellRow = 3;
foreach (var orders in query_expired)
{
expired_sheet.Cells[cellRow, 1] = orders.id.ToString();
expired_sheet.Cells[cellRow, 2] = orders.client.ToString();
expired_sheet.Cells[cellRow, 3] = orders.date.ToString();
expired_sheet.Cells[cellRow, 4] = orders.rent.ToString();
cellRow++;
}
expired_sheet.Cells[cellRow + 1, 1] = "Итого просрочено";
expired_sheet.Cells[cellRow + 2, 1] = cellRow - 3;
fit_cells = expired_sheet.UsedRange;
fit_cells.EntireColumn.AutoFit();
fit_cells.EntireRow.AutoFit();
fit_cells.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
fit_cells.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
//Лист проваленных заказов
Excel.Worksheet failed_sheet = null;
failed_sheet = (Excel.Worksheet)workbook.Worksheets.Add();
failed_sheet = workbook.ActiveSheet;
failed_sheet.Name = "Проваленные заказы";
failed_sheet.Cells[1, 1] = "Проваленные заказы";
failed_sheet.Range[failed_sheet.Cells[1, 1], failed_sheet.Cells[1, 4]].Merge();
failed_sheet.Cells[2, 1] = "ID заказа";
failed_sheet.Cells[2, 2] = "Клиент";
failed_sheet.Cells[2, 3] = "Дата открытия";
failed_sheet.Cells[2, 4] = "Дата провала";
cellRange = (Excel.Range)failed_sheet.Cells[1, 1];
rowRange = cellRange.EntireRow;
colRange = cellRange.EntireColumn;
cellRow = 3;
foreach (var orders in query_failed)
{
failed_sheet.Cells[cellRow, 1] = orders.id.ToString();
failed_sheet.Cells[cellRow, 2] = orders.client.ToString();
failed_sheet.Cells[cellRow, 3] = orders.date.ToString();
failed_sheet.Cells[cellRow, 4] = orders.closed_date.ToString();
cellRow++;
}
failed_sheet.Cells[cellRow + 1, 1] = "Итого провалено";
failed_sheet.Cells[cellRow + 2, 1] = cellRow - 3;
fit_cells = failed_sheet.UsedRange;
fit_cells.EntireColumn.AutoFit();
fit_cells.EntireRow.AutoFit();
fit_cells.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
fit_cells.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
workbook.SaveAs(saveFileDialog.FileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
app.Quit();
this.Close();
}
}
private void btnAdd_Click(object sender, EventArgs e)
{
if (txtboxAddress.Text == "" || txtBoxname.Text == "" || txtboxphone.Text == "")
{
MessageBox.Show("Все поля обязательны к заполнению");
}
else
{
using (pp_dbEntities db = new pp_dbEntities())
{
SByte chk = 0;
if (chkbxBL.Checked)
{
chk = 1;
}
clients cl = new clients
{
fullname = txtBoxname.Text,
address = txtboxAddress.Text,
contact = txtboxphone.Text,
blackliststatus = chk,
};
try
{
if (!add_or_edit)
{
db.clients.Add(cl);
db.SaveChanges();
}
else
{
clients cl2 = db.clients.Where(c => c.fullname == name2).FirstOrDefault();
cl2.fullname = txtBoxname.Text;
cl2.address = txtboxAddress.Text;
cl2.contact = txtboxphone.Text;
if (chkbxBL.Checked)
{
cl2.blackliststatus = 1;
}
else
{
cl2.blackliststatus = 0;
}
db.Entry(cl2).State = System.Data.Entity.EntityState.Modified;
db.SaveChanges();
}
}
catch (Exception ex)
{
return;
}
}
if (!chkModal)
{
this.Close();
}
else
{
txtboxAddress.Text = null;
txtBoxname.Text = null;
txtboxphone.Text = null;
chkbxBL.Checked = false;
MessageBox.Show("Клиент добавлен");
}
}
}
private void btnSearch_Click(object sender, EventArgs e)
{
pp_dbEntities db = new pp_dbEntities();
if (cmbxPageRating.Text != "" && cmbxPageRating.Text != null)
{
if (tbSearchYear.Text != "" && tbSearchYear.Text != null)
{
mv = db.movies.
Where <movies>(x => x.name.Contains(tbSearchName.Text)).
Where <movies>(x => x.agerating == cmbxPageRating.Text).
Where <movies>(x => x.genres.Contains(cmbxGenre.Text)).
Where <movies>(x => x.director.Contains(tbSearchDirector.Text)).
Where <movies>(x => x.year.Contains(tbSearchYear.Text)).
Where <movies>(x => x.country.Contains(cmbxCounrty.Text)).ToList();
zapis2(mv);
}
else
{
mv = db.movies.
Where <movies>(x => x.name.Contains(tbSearchName.Text)).
Where <movies>(x => x.agerating == cmbxPageRating.Text).
Where <movies>(x => x.genres.Contains(cmbxGenre.Text)).
Where <movies>(x => x.director.Contains(tbSearchDirector.Text)).
Where <movies>(x => x.country.Contains(cmbxCounrty.Text)).ToList();
zapis2(mv);
}
}
else
{
if (tbSearchYear.Text != "" && tbSearchYear.Text != null)
{
mv = db.movies.
Where <movies>(x => x.name.Contains(tbSearchName.Text)).
Where <movies>(x => x.genres.Contains(cmbxGenre.Text)).
Where <movies>(x => x.year.Contains(tbSearchYear.Text)).
Where <movies>(x => x.director.Contains(tbSearchDirector.Text)).
Where <movies>(x => x.country.Contains(cmbxCounrty.Text)).ToList();
zapis2(mv);
}
else
{
mv = db.movies.
Where <movies>(x => x.name.Contains(tbSearchName.Text)).
Where <movies>(x => x.genres.Contains(cmbxGenre.Text)).
Where <movies>(x => x.director.Contains(tbSearchDirector.Text)).
Where <movies>(x => x.country.Contains(cmbxCounrty.Text)).ToList();
zapis2(mv);
}
}
label1.Visible = false;
label2.Visible = false;
label3.Visible = false;
label4.Visible = false;
label5.Visible = false;
label6.Visible = false;
btnSearch.Visible = false;
cmbxCounrty.Visible = false;
cmbxPageRating.Visible = false;
cmbxGenre.Visible = false;
tbSearchDirector.Visible = false;
tbSearchName.Visible = false;
tbSearchYear.Visible = false;
cmbxCounrty.SelectedValue = "";
cmbxPageRating.SelectedValue = "";
cmbxGenre.SelectedValue = "";
tbSearchDirector.Text = "";
tbSearchName.Text = "";
tbSearchYear.Text = "";
}
public void btnAddDisk_Click(object sender, EventArgs e) // Добавление дисков в заказ
{
if (txtboxDiskCount.Text == "" || Convert.ToInt32(txtboxDiskCount.Text) == 0)
{
lbldsk.Visible = true;
}
else
{
lbldsklst.Text = "";
dobavlen = true;
lbldsk.Visible = false;
pp_dbEntities db = new pp_dbEntities();
movies mv = db.movies.Where(c => c.name == cmbxDisk.Text).FirstOrDefault();
int cnt = Convert.ToInt32((txtboxDiskCount.Text));
if (mv.count < cnt)
{
lbldskcount.Visible = false;
label8.Text = "Закажите не более " + lbldskcount.Text + " дисков";
}
else
{
mv.count = mv.count - cnt;
db.Entry(mv).State = System.Data.Entity.EntityState.Modified;
db.SaveChanges();
int ID = Convert.ToInt32(cmbxDisk.SelectedValue.ToString());
movies dsk = db.movies.Where(c => c.Id == ID).FirstOrDefault();
lbldskcount.Text = dsk.count.ToString();
lbldskcount.Visible = true;
label8.Text = "Дисков в наличии:";
bool UsheEst = false;
for (int j = 0; j < i; j++)
{
if (DiskList[j] == dsk.Id)
{
DiskCount[j] = DiskCount[j] + cnt;
UsheEst = true;
price = price + dsk.price * cnt;
break;
}
}
if (!UsheEst)
{
DiskList[i] = dsk.Id;
DiskCount[i] = cnt;
price = price + dsk.price * cnt;
i++;
}
}
if (txtboxRent.Text != "")
{
lblPrice.Text = (price / 20 * Convert.ToDouble(txtboxRent.Text)).ToString();
}
else
{
lblPrice.Text = (price / 20).ToString();
}
lblDeposit.Text = price.ToString();
SpisokDiskov = "";
for (int j = 0; j < i; j++)
{
SpisokDiskov = SpisokDiskov + DiskList[j].ToString() + " " + DiskCount[j].ToString() + " ";
}
string dl = "";
string s = SpisokDiskov;
bool a = true;
int id3 = 0;
String[] words = s.Split(new char[] { ' ' }, StringSplitOptions.RemoveEmptyEntries);
for (int i = 0; i < words.Length; i++)
{
int[] array = words[i].Where(x => char.IsNumber(x)).Select(x => x - 48).ToArray();
for (int j = 0; j < array.Length; j++)
{
id3 = id3 * 10 + array[j];
}
if (a)
{
dl = "";
movies mv3 = db.movies.Where(c => c.Id == id3).FirstOrDefault();
dl = dl + mv3.name.ToString() + " ";
id3 = 0;
a = false;
}
else
{
dl = dl + id3.ToString() + " шт ";
id3 = 0;
a = true;
lbldsklst.Text = lbldsklst.Text + '\n' + dl;
}
}
}
}