private static void AddSalesToDb(IExcelDataReader excelReader, DateTime currentDate)
{
using (var db = new ChainOfSupermarketsContext())
{
var salesTable = excelReader.AsDataSet().Tables["Sales"];
var locationName = (string)salesTable.Rows[1].ItemArray[1];
var currentLocation = GetOrCreateLocation(locationName, db);
for (var i = 3; i < salesTable.Rows.Count; i++)
{
if (((string)salesTable.Rows[i].ItemArray[1]).Contains("Total sum"))
{
break;
}
var productName = (string)salesTable.Rows[i].ItemArray[1];
productName = Regex.Replace(productName, @"[^\w'\. ]", string.Empty);
var currentProduct = GetOrCreateProduct(productName, db);
var quantity = (double)salesTable.Rows[i].ItemArray[2];
var pricePerUnit = (double)salesTable.Rows[i].ItemArray[3];
db.Sales.Add(new Sale
{
Location = currentLocation,
DateOfSale = currentDate,
Product = currentProduct,
Quantity = (decimal)quantity,
PricePerUnit = (decimal)pricePerUnit
});
}
db.SaveChanges();
}
}
public static void ExportDbToSqlServer()
{
var connection = new OracleConnection(Settings.Default.OracleConnectionString);
connection.Open();
using (connection)
{
using (var sqlServerDb = new ChainOfSupermarketsContext())
{
ExportMeasures(connection, sqlServerDb);
sqlServerDb.SaveChanges();
ExportVendors(connection, sqlServerDb);
sqlServerDb.SaveChanges();
ExportProducts(connection, sqlServerDb);
sqlServerDb.SaveChanges();
}
}
}
private static Location GetOrCreateLocation(string locationName, ChainOfSupermarketsContext db)
{
var location = db.Locations.FirstOrDefault(l => l.Name == locationName);
if (location == null)
{
location = new Location { Name = locationName };
db.Locations.Add(location);
db.SaveChanges();
}
return location;
}
private static Product GetOrCreateProduct(string productName, ChainOfSupermarketsContext db)
{
var product = db.Products.FirstOrDefault(p => p.ProductName == productName);
if (product == null)
{
product = new Product()
{
ProductName = productName,
Vendors = new Vendor { VendorName = productName.Split(' ').Last() + " Corp." }
};
db.Products.Add(product);
db.SaveChanges();
}
return product;
}