public void AddElement(OrderListBindingModel model)
{
using (var transaction = context.Database.BeginTransaction())
{
try
{
OrderList element = context.OrderLists.FirstOrDefault(rec =>
rec.OrderListName == model.OrderListName);
if (element != null)
{
throw new Exception("Уже есть список с таким названием");
}
element = new OrderList
{
OrderListName = model.OrderListName,
Sum = model.Sum,
};
context.OrderLists.Add(element);
context.SaveChanges();
// убираем дубли по компонентам
var groupProducts = model.OrderListProducts
.GroupBy(rec => rec.ProductId)
.Select(rec => new
{
ProductId = rec.Key,
Count = rec.Sum(r => r.Count)
});
// добавляем компоненты
foreach (var groupProduct in groupProducts)
{
context.OrderListProducts.Add(new OrderListProduct
{
OrderListId = element.Id,
ProductId = groupProduct.ProductId,
Count = groupProduct.Count
});
context.SaveChanges();
}
transaction.Commit();
}
catch (Exception)
{
transaction.Rollback();
throw;
}
}
}
public List <OrderListProductBindingModel> ReadExcel(string FileName)
{
int rows = 0;
OrderListBindingModel OL = new OrderListBindingModel();
var ObjWorkExcel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook ObjWorkBook = ObjWorkExcel.Workbooks.Open(FileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
Microsoft.Office.Interop.Excel.Worksheet ObjWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)ObjWorkBook.Sheets[1];
var lastCell = ObjWorkSheet.Cells.SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeLastCell);
string[,] list = new string[lastCell.Column, lastCell.Row];
for (int i = 0; i < lastCell.Column; i++)
{
for (int j = 0; j < lastCell.Row; j++)
{
list[i, j] = ObjWorkSheet.Cells[j + 1, i + 1].Text.ToString();
rows = j + 1;
}
}
ObjWorkBook.Close(false, Type.Missing, Type.Missing);
ObjWorkExcel.Quit();
GC.Collect();
List <OrderListProductBindingModel> orderlistProductBM = new
List <OrderListProductBindingModel>();
for (int i = 0; i < rows; i++)
{
orderlistProductBM.Add(new OrderListProductBindingModel
{
Id = Convert.ToInt32(list[0, i]),
OrderListId = Convert.ToInt32(list[1, i]),
Price = Convert.ToDecimal(list[2, i]),
ProductName = list[3, i].ToString(),
ProductId = Convert.ToInt32(list[4, i]),
Count = Convert.ToInt32(list[5, i]),
Sum = Convert.ToDecimal(list[6, i])
});
}
OL.OrderListProducts = orderlistProductBM;
return(orderlistProductBM);
}
public void AddElement(OrderListBindingModel model)
{
OrderList element = source.OrderLists.FirstOrDefault(rec => rec.OrderListName == model.OrderListName);
if (element != null)
{
throw new Exception("Уже есть изделие с таким названием");
}
int maxId = source.OrderLists.Count > 0 ? source.OrderLists.Max(rec => rec.Id) :
0;
source.OrderLists.Add(new OrderList
{
Id = maxId + 1,
OrderListName = model.OrderListName,
Sum = model.Sum
});
// компоненты для изделия
int maxPCId = source.OrderListProducts.Count > 0 ?
source.OrderListProducts.Max(rec => rec.Id) : 0;
// убираем дубли по компонентам
var groupComponents = model.OrderListProducts.GroupBy(rec => rec.ProductId).Select(rec => new
{
ProductId = rec.Key,
Count = rec.Sum(r => r.Count)
});
// добавляем компоненты
foreach (var groupMaterial in groupComponents)
{
source.OrderListProducts.Add(new OrderListProduct
{
Id = ++maxPCId,
OrderListId = maxId + 1,
ProductId = groupMaterial.ProductId,
Count = groupMaterial.Count
});
}
}
public void UpdElement(OrderListBindingModel model)
{
using (var transaction = context.Database.BeginTransaction())
{
try
{
OrderList element = context.OrderLists.FirstOrDefault(rec =>
rec.OrderListName == model.OrderListName && rec.Id != model.Id);
if (element != null)
{
throw new Exception("Уже есть изделие с таким названием");
}
element = context.OrderLists.FirstOrDefault(rec => rec.Id == model.Id);
if (element == null)
{
throw new Exception("Элемент не найден");
}
element.OrderListName = model.OrderListName;
element.Sum = model.Sum;
context.SaveChanges();
// обновляем существуюущие компоненты
var compIds = model.OrderListProducts.Select(rec =>
rec.ProductId).Distinct();
var updateProducts = context.OrderListProducts.Where(rec =>
rec.ProductId == model.Id && compIds.Contains(rec.ProductId));
foreach (var updateProduct in updateProducts)
{
updateProduct.Count =
model.OrderListProducts.FirstOrDefault(rec => rec.Id == updateProduct.Id).Count;
}
context.SaveChanges();
context.OrderListProducts.RemoveRange(context.OrderListProducts.Where(rec =>
rec.OrderListId == model.Id && !compIds.Contains(rec.ProductId)));
context.SaveChanges();
// новые записи
var groupProducts = model.OrderListProducts
.Where(rec => rec.Id == 0)
.GroupBy(rec => rec.ProductId)
.Select(rec => new
{
ProductId = rec.Key,
Count = rec.Sum(r => r.Count)
});
foreach (var groupProduct in groupProducts)
{
OrderListProduct elementPC =
context.OrderListProducts.FirstOrDefault(rec => rec.OrderListId == model.Id &&
rec.ProductId == groupProduct.ProductId);
if (elementPC != null)
{
elementPC.Count += groupProduct.Count;
context.SaveChanges();
}
else
{
context.OrderListProducts.Add(new OrderListProduct
{
OrderListId = model.Id,
ProductId = groupProduct.ProductId,
Count = groupProduct.Count
});
context.SaveChanges();
}
}
transaction.Commit();
}
catch (Exception)
{
transaction.Rollback();
throw;
}
}
}
public void UpdElement(OrderListBindingModel model)
{
OrderList element = source.OrderLists.FirstOrDefault(rec => rec.OrderListName ==
model.OrderListName && rec.Id != model.Id);
if (element != null)
{
throw new Exception("Уже есть изделие с таким названием");
}
element = source.OrderLists.FirstOrDefault(rec => rec.Id == model.Id);
if (element == null)
{
throw new Exception("Элемент не найден");
}
element.OrderListName = model.OrderListName;
element.Sum = model.Sum;
int maxPCId = source.OrderListProducts.Count > 0 ?
source.OrderListProducts.Max(rec => rec.Id) : 0;
// обновляем существуюущие компоненты
var compIds = model.OrderListProducts.Select(rec =>
rec.ProductId).Distinct();
var updateMaterials = source.OrderListProducts.Where(rec => rec.OrderListId ==
model.Id && compIds.Contains(rec.ProductId));
foreach (var updateMaterial in updateMaterials)
{
updateMaterial.Count = model.OrderListProducts.FirstOrDefault(rec =>
rec.Id == updateMaterial.Id).Count;
}
source.OrderListProducts.RemoveAll(rec => rec.OrderListId == model.Id &&
!compIds.Contains(rec.ProductId));
// новые записи
var groupMaterials = model.OrderListProducts.Where(rec => rec.Id == 0).GroupBy(rec => rec.ProductId)
.Select(rec => new
{
ProductId = rec.Key,
Count = rec.Sum(r => r.Count)
});
foreach (var groupMaterial in groupMaterials)
{
OrderListProduct elementPC = source.OrderListProducts.FirstOrDefault(rec
=> rec.OrderListId == model.Id && rec.ProductId == groupMaterial.ProductId);
if (elementPC != null)
{
elementPC.Count += groupMaterial.Count;
}
else
{
source.OrderListProducts.Add(new OrderListProduct
{
Id = ++maxPCId,
OrderListId = model.Id,
ProductId = groupMaterial.ProductId,
Count = groupMaterial.Count
});
}
}
}
