private void storeacc_up()
{
using (ProductDBContext db = new ProductDBContext())
{
var data = db.StoreAccountings.ToList();
var data2 = db.Products.ToList();
var data3 = db.Stores.ToList();
var data4 = new List <StAccDop>();
foreach (var item in data)
{
var it = new StAccDop
{
Name = item.Product.Name,
Sname = item.Store.Sname,
Quantity = item.Quantity,
Cost = item.Cost
};
data4.Add(it);
}
StoreAccountingReportComboBox.DisplayMemberPath = "Sname";
StoreAccountingReportComboBox.ItemsSource = data3;
comboBoxStPr.ItemsSource = data2;
comboBoxStSt.ItemsSource = data3;
storeaccountingGrid.ItemsSource = data4;
}
}
private void StoreAccountingReportButton_Click(object sender, RoutedEventArgs e)
{
try
{
using (ProductDBContext db = new ProductDBContext())
{
var storev = StoreAccountingReportComboBox.SelectedItem as Store;
var storeRep = db.StoreAccountings.Where(p => p.StoreId == storev.StoreId).Join(db.Products, // второй набор
u => u.ProductId, // свойство-селектор объекта из первого набора
c => c.ProductId, // свойство-селектор объекта из второго набора
(u, c) => new ReportStoreAccounting // результат
{
Name = c.Name,
Quantity = u.Quantity,
Cost = u.Cost
}).OrderBy(p => p.Name);
var reportExcel = ReportGenerator.Generate(storeRep, storev.Sname);
File.WriteAllBytes("../../../../ReportStoreAccounting.xlsx", reportExcel);
}
}
catch (Exception ex)
{
MessageBox.Show("Только что произошло обработанное исключение: " + ex.Message, "Exception Sample", MessageBoxButton.OK, MessageBoxImage.Warning);
}
}
private void whouseacc_up()
{
using (ProductDBContext db = new ProductDBContext())
{
var data = db.WarehouseAccountings.ToList();
var data2 = db.Products.ToList();
var data3 = db.Warehouses.ToList();
var data4 = new List <WhAccDop>();
foreach (var item in data)
{
var it = new WhAccDop
{
Name = item.Product.Name,
Wnumber = item.Whouses.Wnumber,
Quantity = item.Quantity,
Cost = item.Cost
};
data4.Add(it);
}
WhAccountingReportComboBox.DisplayMemberPath = "Wnumber";
WhAccountingReportComboBox.ItemsSource = data3;
comboBoxWhPr.ItemsSource = data2;
comboBoxWhWh.ItemsSource = data3;
warehouseaccountingGrid.ItemsSource = data4;
}
}
private void warehouse_up()
{
using (ProductDBContext db = new ProductDBContext())
{
var data = db.Warehouses.ToList();
warehouseGrid.ItemsSource = data;
}
}
private void store_up()
{
using (ProductDBContext db = new ProductDBContext())
{
var data = db.Stores.ToList();
storeGrid.ItemsSource = data;
}
}
private void prod_up()
{
using (ProductDBContext db = new ProductDBContext())
{
var data = db.Products.ToList();
productGrid.ItemsSource = data;
}
}
private void upinfButtonWhousesAccounting_Click(object sender, RoutedEventArgs e)
{
try {
using (ProductDBContext db = new ProductDBContext())
{
db.Database.ExecuteSqlRaw("EXECUTE up_WarehouseAccounting {0},{1},{2},{3};", getDataFromCellwh(0, warehouseaccountingGrid), getDataFromCellwh(1, warehouseaccountingGrid), getDataFromCell(2, warehouseaccountingGrid), getDataFromCell(3, warehouseaccountingGrid));
}
}
catch (Exception ex) {
MessageBox.Show("Только что произошло обработанное исключение: " + ex.Message, "Exception Sample", MessageBoxButton.OK, MessageBoxImage.Warning);
}
finally {
whouseacc_up();
}
}
private void upinfButtonStoreAccounting_Click(object sender, RoutedEventArgs e)
{
try
{
using (ProductDBContext db = new ProductDBContext())
{
db.Database.ExecuteSqlRaw("EXECUTE up_StoreAccounting {0},{1},{2},{3};", getDataFromCellstore(0, storeaccountingGrid), getDataFromCellstore(1, storeaccountingGrid), getDataFromCell(2, storeaccountingGrid), getDataFromCell(3, storeaccountingGrid));
}
}
catch (Exception ex)
{
MessageBox.Show("A handled exception just occurred: " + ex.Message, "Exception Sample", MessageBoxButton.OK, MessageBoxImage.Warning);
}
finally { storeacc_up(); }
}
private void WhAccountingReportButton_Click(object sender, RoutedEventArgs e)
{
try
{
using (ProductDBContext db = new ProductDBContext())
{
var data3 = db.Warehouses.ToList();
var reportExcel = ReportGeneratorWh.Generate(data3);
File.WriteAllBytes("../../../../ReportWhAccounting.xlsx", reportExcel);
}
}
catch (Exception ex)
{
MessageBox.Show("Только что произошло обработанное исключение: " + ex.Message, "Exception Sample", MessageBoxButton.OK, MessageBoxImage.Warning);
}
}
private void upinfButtonStore_Click(object sender, RoutedEventArgs e)
{
try {
using (ProductDBContext db = new ProductDBContext())
{
db.Database.ExecuteSqlRaw("EXECUTE up_Stores {0},{1},{2},{3},{4};", getDataFromCell(0, storeGrid), getDataFromCell(1, storeGrid), getDataFromCell(2, storeGrid), getDataFromCell(3, storeGrid), getDataFromCell(4, storeGrid));
}
}
catch (Exception ex)
{
MessageBox.Show("Только что произошло обработанное исключение: " + ex.Message, "Exception Sample", MessageBoxButton.OK, MessageBoxImage.Warning);
}
finally
{
store_up();
}
}
private void insertButtonProduct_Click(object sender, RoutedEventArgs e)
{
try {
using (ProductDBContext db = new ProductDBContext())
{
db.Database.ExecuteSqlRaw("EXECUTE ins_Products {0},{1},{2};", getDataFromCell(1, productGrid), getDataFromCell(2, productGrid), getDataFromCell(3, productGrid));
}
}
catch (Exception ex)
{
MessageBox.Show("Только что произошло обработанное исключение: " + ex.Message, "Exception Sample", MessageBoxButton.OK, MessageBoxImage.Warning);
}
finally
{
prod_up();
}
}
private void deleteButtonStoreAccounting_Click(object sender, RoutedEventArgs e)
{
try {
string inf = getDataFromCellstore(0, storeaccountingGrid);
string inf2 = getDataFromCellstore(1, storeaccountingGrid);
if (inf != null)
{
using (ProductDBContext db = new ProductDBContext())
{
db.Database.ExecuteSqlRaw("EXECUTE del_StoreAccounting {0},{1};", inf, inf2);
}
}
}
catch (Exception ex)
{
MessageBox.Show("A handled exception just occurred: " + ex.Message, "Exception Sample", MessageBoxButton.OK, MessageBoxImage.Warning);
}
finally { storeacc_up(); }
}
//Getting data from DataGridComboBoxColumn cell (wareHouseAccounting)
private string getDataFromCellwh(int selectedColumn, DataGrid Grid)
{
DataGridCellInfo cells = Grid.SelectedCells[selectedColumn];
var inf = cells.Item as WhAccDop;
if (selectedColumn == 0)
{
int valueID = 0;
using (ProductDBContext db = new ProductDBContext())
{
var data = db.Products.ToList();
foreach (var item in data)
{
if (item.Name == inf.Name)
{
valueID = item.ProductId;
}
}
}
return(valueID.ToString());
}
else
{
int valueID = 0;
using (ProductDBContext db = new ProductDBContext())
{
var data = db.Warehouses.ToList();
foreach (var item in data)
{
if (item.Wnumber == inf.Wnumber)
{
valueID = item.WhousesId;
}
}
}
return(valueID.ToString());
}
}
private void deleteButtonWarehouse_Click(object sender, RoutedEventArgs e)
{
try {
string inf = getDataFromCell(0, warehouseGrid);
if (inf != null)
{
using (ProductDBContext db = new ProductDBContext())
{
db.Database.ExecuteSqlRaw("EXECUTE del_Stores {0};", inf);
}
}
}
catch (Exception ex)
{
MessageBox.Show("Только что произошло обработанное исключение: " + ex.Message, "Exception Sample", MessageBoxButton.OK, MessageBoxImage.Warning);
}
finally
{
warehouse_up();
}
}
public static byte[] Generate(List <Warehouse> data3)
{
var package = new ExcelPackage();
var sheet = package.Workbook.Worksheets
.Add("Store Accounting Report");
int vall = 0;
using (ProductDBContext db = new ProductDBContext())
{
foreach (var Whv in data3)
{
var whRep = db.WarehouseAccountings.Where(p => p.WhousesId == Whv.WhousesId).Join(db.Products, // второй набор
u => u.ProductId, // свойство-селектор объекта из первого набора
c => c.ProductId, // свойство-селектор объекта из второго набора
(u, c) => new ReportWhAccounting // результат
{
Name = c.Name,
Quantity = u.Quantity,
Cost = u.Cost,
Sum = u.Quantity * u.Cost
}).OrderBy(p => p.Name);
sheet.Cells[2 + (10 * vall), 2].Value = "Warehouse number:";
sheet.Cells[2 + (10 * vall), 3].Value = $"{Whv.Wnumber} ";
sheet.Cells[3 + (10 * vall), 2].Value = "Name";
sheet.Cells[3 + (10 * vall), 3].Value = "Cost";
sheet.Cells[3 + (10 * vall), 4].Value = "Quantity";
sheet.Cells[3 + (10 * vall), 5].Value = "Sum";
sheet.Column(2).Width = 20;
sheet.Column(3).Width = 20;
sheet.Column(4).Width = 20;
sheet.Column(5).Width = 20;
int value = 0;
int count = 0;
var row = 4;
var column = 2;
foreach (var item in whRep)
{
sheet.Cells[row + (10 * vall), column].Value = item.Name;
sheet.Cells[row + (10 * vall), column + 1].Value = item.Cost;
sheet.Cells[row + (10 * vall), column + 2].Value = item.Quantity;
sheet.Cells[row + (10 * vall), column + 3].Value = item.Sum;
if (item.Quantity != null)
{
value += (int)item.Sum;
}
count++;
row++;
}
sheet.Cells[row + (10 * vall), 4].Value = "All:";
sheet.Cells[row + (10 * vall), 5].Value = $"{value} ";
sheet.Cells[row + (10 * vall), 4, row + (10 * vall), 5].Style.Border.BorderAround(ExcelBorderStyle.Double);
sheet.Cells[3 + (10 * vall), 2, 3 + (10 * vall) + count, 5].Style.Border.BorderAround(ExcelBorderStyle.Double);
sheet.Cells[3 + (10 * vall), 2, 3 + (10 * vall), 5].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
vall++;
}
}
sheet.Protection.IsProtected = true;
return(package.GetAsByteArray());
}