public int Import()
{
using (var context = new SupermarketsContext())
using (var transaction = context.Database.BeginTransaction())
{
try
{
var productsNotInDB = this.extractor.Products.
Except(context.Products.Select(p => p.Name));
if (productsNotInDB.Count() > 0)
{
throw new Exception(string.Format(Messages.NonExistingProduct,
string.Join(", ", productsNotInDB)));
}
var shopsToInsert = this.extractor.Shops.
Except(context.Shops.Select(s => s.Name)).
Select(s => new Shop() { Name = s });
context.Shops.AddRange(shopsToInsert);
context.SaveChanges();
var salesToInsert = this.extractor.Sales
.Select(s => new Sale
{
ProductId = context.Products.Where(p => p.Name == s.Product.Name).Select(p => p.Id).FirstOrDefault(),
ShopId = context.Shops.Where(shop => shop.Name == s.Shop.Name).Select(shop => shop.Id).FirstOrDefault(),
Quantity = s.Quantity,
UnitPrice = s.UnitPrice,
Date = s.Date
})
.Where(s => !context.Sales.Any(es => es.ProductId == s.ProductId && es.Date == s.Date && es.ShopId == s.ShopId));
context.Sales.AddRange(salesToInsert);
context.SaveChanges();
transaction.Commit();
return salesToInsert.Count();
}
// TODO: Change to more concrete exceptions
catch (Exception innerEx)
{
try
{
transaction.Rollback();
}
catch (Exception rollBackEx)
{
throw new Exception(innerEx.Message + Environment.NewLine + Messages.ErrorDuringRollback, rollBackEx);
}
throw new Exception(innerEx.Message + Environment.NewLine + Messages.ErrorUpdatingSQLDatabase, innerEx);
}
}
}
public static IEnumerable<MySqlVendor> GetVendorExpenses()
{
var supermarketsContext = new SupermarketsContext();
var vendorExpenses = supermarketsContext.Vendors
.Select(v => new MySqlVendor()
{
Name = v.Name,
Expenses = v.Expenses.Sum(e => e.Ammount) == null ? 0m : v.Expenses.Sum(e => e.Ammount)
});
return vendorExpenses;
}
public static IEnumerable<MySqlProduct> GetProductIncomes()
{
var supermarketsContext = new SupermarketsContext();
var productIncomes = supermarketsContext.Products
.Where(p => p.Sales.Count > 0)
.Select(p => new MySqlProduct()
{
Name = p.Name,
Incomes = p.Sales.Sum(s => s.Quantity * s.UnitPrice),
Vendor = new MySqlVendor() { Name = p.Vendor.Name}
});
return productIncomes;
}
public VendorProfitReport()
{
this.supermarketsContext = new SupermarketsContext();
this.taxesContext = new TaxesContext();
}
public SalesReportForPeriod()
{
this.context = new SupermarketsContext();
}
public OracleImporter()
{
this.context = new SupermarketsContext();
}
