public ProductsRow AddProductsRow()
{
ProductsRow rowProductsRow = ((ProductsRow)(this.NewRow()));
rowProductsRow.ItemArray = new object[] {
null
};
this.Rows.Add(rowProductsRow);
return(rowProductsRow);
}
public ProductsRow AddProductsRow(string ProductName, System.Decimal UnitPrice)
{
ProductsRow rowProductsRow = ((ProductsRow)(this.NewRow()));
rowProductsRow.ItemArray = new object[] {
null,
ProductName,
UnitPrice
};
this.Rows.Add(rowProductsRow);
return(rowProductsRow);
}
public ProductsRow AddProductsRow(int ProductID, string ProductName, string QuantityPerUnit, System.Decimal UnitPrice)
{
ProductsRow rowProductsRow = ((ProductsRow)(this.NewRow()));
rowProductsRow.ItemArray = new object[] {
ProductID,
ProductName,
QuantityPerUnit,
UnitPrice
};
this.Rows.Add(rowProductsRow);
return(rowProductsRow);
}
public ProductsRow AddProductsRow(string ProductName, int CategoryID)
{
ProductsRow rowProductsRow = ((ProductsRow)(this.NewRow()));
object[] columnValuesArray = new object[] {
null,
ProductName,
CategoryID
};
rowProductsRow.ItemArray = columnValuesArray;
this.Rows.Add(rowProductsRow);
return(rowProductsRow);
}
public ProductsRow AddProductsRow(int Product_ID, string Product_Name, string Product_Description, byte[] BarCode)
{
ProductsRow rowProductsRow = ((ProductsRow)(this.NewRow()));
object[] columnValuesArray = new object[] {
Product_ID,
Product_Name,
Product_Description,
BarCode
};
rowProductsRow.ItemArray = columnValuesArray;
this.Rows.Add(rowProductsRow);
return(rowProductsRow);
}
public ProductsRow AddProductsRow(string ProductName, decimal UnitPrice, bool Discontinued)
{
ProductsRow rowProductsRow = ((ProductsRow)(this.NewRow()));
object[] columnValuesArray = new object[] {
null,
ProductName,
UnitPrice,
Discontinued
};
rowProductsRow.ItemArray = columnValuesArray;
this.Rows.Add(rowProductsRow);
return(rowProductsRow);
}
public ProductsRow AddProductsRow(int CategoryID, string ProductName, System.Decimal UnitPrice, short UnitsInStock, short ReorderLevel)
{
ProductsRow rowProductsRow = ((ProductsRow)(this.NewRow()));
rowProductsRow.ItemArray = new object[] {
CategoryID,
ProductName,
UnitPrice,
UnitsInStock,
ReorderLevel
};
this.Rows.Add(rowProductsRow);
return(rowProductsRow);
}
public ProductsRow AddProductsRow(string ProductName, string QuantityPerUnit, System.Decimal UnitPrice, short UnitsInStock, short UnitsOnOrder)
{
ProductsRow rowProductsRow = ((ProductsRow)(this.NewRow()));
rowProductsRow.ItemArray = new object[] {
null,
ProductName,
QuantityPerUnit,
UnitPrice,
UnitsInStock,
UnitsOnOrder
};
this.Rows.Add(rowProductsRow);
return(rowProductsRow);
}
public ProductsRow AddProductsRow(string ProductName, int CategoryID, decimal UnitPrice, System.DateTime LastUpdate)
{
ProductsRow rowProductsRow = ((ProductsRow)(this.NewRow()));
object[] columnValuesArray = new object[] {
null,
ProductName,
CategoryID,
UnitPrice,
LastUpdate
};
rowProductsRow.ItemArray = columnValuesArray;
this.Rows.Add(rowProductsRow);
return(rowProductsRow);
}
public ProductsRow AddProductsRow(string ProductName, int SupplierID, int CategoryID, string QuantityPerUnit, System.Decimal UnitPrice, short UnitsInStock)
{
ProductsRow rowProductsRow = ((ProductsRow)(this.NewRow()));
rowProductsRow.ItemArray = new object[] {
null,
ProductName,
SupplierID,
CategoryID,
QuantityPerUnit,
UnitPrice,
UnitsInStock
};
this.Rows.Add(rowProductsRow);
return(rowProductsRow);
}
public ProductsRow AddProductsRow(string ProductName, int SupplierID, CategoriesRow parentCategoriesRowByCategoriesProducts, string QuantityPerUnit, System.Decimal UnitPrice, short UnitsInStock, short UnitsOnOrder, short ReorderLevel, bool Discontinued)
{
ProductsRow rowProductsRow = ((ProductsRow)(this.NewRow()));
rowProductsRow.ItemArray = new object[] {
null,
ProductName,
SupplierID,
parentCategoriesRowByCategoriesProducts[0],
QuantityPerUnit,
UnitPrice,
UnitsInStock,
UnitsOnOrder,
ReorderLevel,
Discontinued
};
this.Rows.Add(rowProductsRow);
return(rowProductsRow);
}
public Order_DetailsRow AddOrder_DetailsRow(OrdersRow parentOrdersRowByOrdersOrder_Details, ProductsRow parentProductsRowByProductsOrder_Details, decimal UnitPrice, short Quantity, float Discount) {
Order_DetailsRow rowOrder_DetailsRow = ((Order_DetailsRow)(this.NewRow()));
rowOrder_DetailsRow.ItemArray = new object[] {
parentOrdersRowByOrdersOrder_Details[0],
parentProductsRowByProductsOrder_Details[0],
UnitPrice,
Quantity,
Discount};
this.Rows.Add(rowOrder_DetailsRow);
return rowOrder_DetailsRow;
}
public ProductsRowChangeEvent(ProductsRow row, global::System.Data.DataRowAction action) {
this.eventRow = row;
this.eventAction = action;
}
public ExcelImportResponse ExcelImport(IUnitOfWork uow, ExcelImportRequest request)
{
request.CheckNotNull();
Check.NotNullOrWhiteSpace(request.FileName, "filename");
UploadHelper.CheckFileNameSecurity(request.FileName);
if (!request.FileName.StartsWith("temporary/"))
{
throw new ArgumentOutOfRangeException("filename");
}
ExcelPackage ep = new ExcelPackage();
using (var fs = new FileStream(UploadHelper.DbFilePath(request.FileName), FileMode.Open, FileAccess.Read))
ep.Load(fs);
var p = ProductsRow.Fields;
var c = CategoriesRow.Fields;
var m = MeasuresRow.Fields;
var cp = CounterpartiesRow.Fields;
var response = new ExcelImportResponse();
response.ErrorList = new List <string>();
var worksheet = ep.Workbook.Worksheets[1];
for (var row = 2; row <= worksheet.Dimension.End.Row; row++)
{
try
{
var productName = Convert.ToString(worksheet.Cells[row, 4].Value ?? "");
if (productName.IsTrimmedEmpty())
{
continue;
}
var product = uow.Connection.TryFirst <ProductsRow>(q => q
.Select(p.ProductId)
.Where(p.ProductName == productName));
if (product == null)
{
product = new ProductsRow
{
ProductName = productName
}
}
;
else
{
product.TrackWithChecks = false;
}
#region Category
var categoryName = Convert.ToString(worksheet.Cells[row, 6].Value ?? "");
if (!string.IsNullOrWhiteSpace(categoryName))
{
var category = uow.Connection.TryFirst <CategoriesRow>(q => q
.Select(c.CategoryId)
.Where(c.CategoryName == categoryName));
if (category == null)
{
response.ErrorList.Add("Error On Row" + row + ": Category with name '" +
categoryName + "' is not found!");
continue;
}
product.CategoryId = category.CategoryId.Value;
}
else
{
product.CategoryId = null;
}
#endregion Category
#region Measure
var measureName = Convert.ToString(worksheet.Cells[row, 7].Value ?? "");
if (!string.IsNullOrWhiteSpace(measureName))
{
var measure = uow.Connection.TryFirst <MeasuresRow>(q => q
.Select(m.MeasureId)
.Where(m.MeasureName == measureName));
if (measure == null)
{
response.ErrorList.Add("Error On Row" + row + ": Measure with name '" +
measureName + "' is not found!");
continue;
}
product.MeasureId = measure.MeasureId.Value;
}
else
{
product.MeasureId = null;
}
#endregion Measure
#region Counterparty
//var counterpartyName = Convert.ToString(worksheet.Cells[row, 13].Value ?? 0);
//if (!string.IsNullOrWhiteSpace(counterpartyName))
//{
// var counterparty = uow.Connection.TryFirst<CounterpartyRow>(q => q
// .Select(cp.CounterpartyID)
// .Where(cp.CompanyName == counterpartyName));
// if(counterparty == null)
// {
// response.ErrorList.Add("Error On Row" + row + ": Counterparty with name '" +
// counterpartyName + "' is not found!");
// continue;
// }
// product.CounterpartyID = counterparty.CounterpartyID.ToString();
//}
//else
//{
// product.CounterpartyID = null;
//}
#endregion Counterparty
product.ProductCode = Convert.ToString(worksheet.Cells[row, 1].Value ?? "");
product.ProductBarcode = Convert.ToString(worksheet.Cells[row, 2].Value ?? "");
product.ProductLabel = Convert.ToString(worksheet.Cells[row, 3].Value ?? "");
product.Discontinued = Convert.ToBoolean(worksheet.Cells[row, 5].Value ?? "");
product.QuantityPerUnit = Convert.ToString(worksheet.Cells[row, 8].Value ?? "");
product.UnitPrice = Convert.ToDecimal(worksheet.Cells[row, 9].Value ?? 0);
product.UnitsInStock = Convert.ToInt16(worksheet.Cells[row, 10].Value ?? 0);
product.UnitsOnOrder = Convert.ToInt16(worksheet.Cells[row, 11].Value ?? 0);
product.CounterpartyId = Convert.ToString(worksheet.Cells[row, 13].Value ?? 0);
if (product.ProductId == null)
{
new ProductsRepository().Create(uow, new SaveWithLocalizationRequest <MyRow>
{
Entity = product
});
response.Inserted = response.Inserted + 1;
}
else
{
new ProductsRepository().Update(uow, new SaveWithLocalizationRequest <MyRow>
{
Entity = product,
EntityId = product.ProductId.Value
});
response.Updated = response.Updated + 1;
}
}
catch (Exception ex)
{
response.ErrorList.Add("Exception on Row " + row + ": " + ex.Message);
}
}
return(response);
}
public void RemoveProductsRow(ProductsRow row) {
this.Rows.Remove(row);
}
public void AddProductsRow(ProductsRow row) {
this.Rows.Add(row);
}
public Order_DetailsRow AddOrder_DetailsRow(OrdersRow parentOrdersRowByFK_Order_Details_Orders, ProductsRow parentProductsRowByFK_Order_Details_Products, decimal UnitPrice, short Quantity, float Discount) {
Order_DetailsRow rowOrder_DetailsRow = ((Order_DetailsRow)(this.NewRow()));
object[] columnValuesArray = new object[] {
null,
null,
UnitPrice,
Quantity,
Discount};
if ((parentOrdersRowByFK_Order_Details_Orders != null)) {
columnValuesArray[0] = parentOrdersRowByFK_Order_Details_Orders[0];
}
if ((parentProductsRowByFK_Order_Details_Products != null)) {
columnValuesArray[1] = parentProductsRowByFK_Order_Details_Products[0];
}
rowOrder_DetailsRow.ItemArray = columnValuesArray;
this.Rows.Add(rowOrder_DetailsRow);
return rowOrder_DetailsRow;
}
public ProductsRowChangeEvent(ProductsRow row, global::System.Data.DataRowAction action)
{
this.eventRow = row;
this.eventAction = action;
}
public void RemoveProductsRow(ProductsRow row)
{
this.Rows.Remove(row);
}
public void AddProductsRow(ProductsRow row)
{
this.Rows.Add(row);
}
public int GetProductsRows()
{
return(ProductsRow.ToArray().Length);
}
public ProductsRowChangeEvent(ProductsRow row, DataRowAction action)
{
this.eventRow = row;
this.eventAction = action;
}
public ProductsRowChangedEventArgs(ProductsRow r, System.Data.DataRowAction a) {
this.eventRow = r;
this.eventAction = a;
}
