public void MigrateDataFromMySqlToSqlServer()
{
using (var supermarketContextSqlServer = new SupermarketContext())
{
using (var supermarketContextMySql = new SupermarketModel())
{
foreach (var measureMySql in supermarketContextMySql.Measures)
{
if (!supermarketContextSqlServer.Measures
.Any(m => m.MeasureName == measureMySql.MeasureName))
{
supermarketContextSqlServer.Measures.Add(new Models.Measure()
{
MeasureName = measureMySql.MeasureName
});
}
}
foreach (var vendorMySql in supermarketContextMySql.Vendors)
{
if (!supermarketContextSqlServer.Vendors
.Any(v => v.VendorName == vendorMySql.VendorName))
{
supermarketContextSqlServer.Vendors.Add(new Models.Vendor()
{
VendorName = vendorMySql.VendorName
});
}
}
supermarketContextSqlServer.SaveChanges();
foreach (var productMySql in supermarketContextMySql.Products)
{
if (!supermarketContextSqlServer.Products
.Any(p => p.Name == productMySql.ProductName))
{
var vendorSqlServer = supermarketContextSqlServer.Vendors
.First(v => v.VendorName == productMySql.Vendor.VendorName);
var measureSqlServer = supermarketContextSqlServer.Measures
.First(m => m.MeasureName == productMySql.Measure.MeasureName);
supermarketContextSqlServer.Products.Add(new Models.Product()
{
BasePrice = productMySql.BasePrice,
Name = productMySql.ProductName,
Measure = measureSqlServer,
Vendor = vendorSqlServer,
});
}
}
}
supermarketContextSqlServer.SaveChanges();
}
}
public ActionResult Create([Bind(Include = "ID,FirstName,LastName,Age,PhoneNum")] Client client)
{
if (ModelState.IsValid)
{
db.clients.Add(client);
db.SaveChanges();
return(RedirectToAction("Index"));
}
return(View(client));
}
public ActionResult Create([Bind(Include = "SuplierID,SuplierName,Adress,Phone")] Suplier suplier)
{
if (ModelState.IsValid)
{
db.supliers.Add(suplier);
db.SaveChanges();
return(RedirectToAction("Index"));
}
return(View(suplier));
}
public ActionResult Create([Bind(Include = "ID_чека,Время_открытия_чека,Время_закрытия_чека,Номер_карты,ID_кассира,ID_смены,Наличные")] Чеки чеки)
{
if (ModelState.IsValid)
{
чеки.ID_чека = Guid.NewGuid();
db.Чеки.Add(чеки);
db.SaveChanges();
return(RedirectToAction("Index"));
}
ViewBag.Номер_карты = new SelectList(db.Дисконтные_карты, "Номер_карты", "Номер_карты", чеки.Номер_карты);
return(View(чеки));
}
public void Export(string zipFileName, string extractionFolder,
OleDbConnection excelConnection, SupermarketContext sqlServerDb)
{
ExtractTo(zipFileName, extractionFolder);
DirectoryInfo directory = new DirectoryInfo(extractionFolder);
foreach (var dir in directory.GetDirectories())
{
int dateId = -1;
//Console.WriteLine("{0}", dir.Name);
DateTime date = DateTime.Parse(dir.Name);
if (sqlServerDb.SoldDates.Where(x => x.Date == date).Count() == 0)
{
sqlServerDb.SoldDates.Add(new SoldDate()
{
Date = date
});
sqlServerDb.SaveChanges();
}
dateId = sqlServerDb.SoldDates.Where(x => x.Date.Equals(date)).Select(x => x.SoldDateId).First();
foreach (var file in dir.GetFiles())
{
ExportSingleExcelFile(file, dateId, excelConnection, sqlServerDb);
}
}
}
static void Main()
{
XmlDocument xmlDoc = new XmlDocument();
xmlDoc.Load("../../ExpensesByVendorAndByMonth.xml");
var context = new SupermarketContext();
XmlNodeList Vendors = xmlDoc.DocumentElement.SelectNodes("/expenses-by-month/vendor");
foreach (XmlElement item in Vendors)
{
string vendorName = item.Attributes["name"].Value;
foreach (XmlNode expense in item.ChildNodes)
{
DateTime expenseMonth = DateTime.Parse(expense.Attributes["month"].Value);
decimal expensePrice = Convert.ToDecimal(expense.InnerText);
try
{
var vendorId = context.Suppliers.First(v => v.Name == vendorName).Id;
context.Expenses.Add(new Expense
{
Date = expenseMonth,
Amount = expensePrice,
SupplierId = vendorId
});
context.SaveChanges();
}
catch
{
throw new ArgumentNullException("Such vendor is not found in the database", vendorName);
}
}
}
}
private static void MoveProducts()
{
var client = new SupermarketModel();
using (client)
{
var products = client.Products;
using (SupermarketContext context = new SupermarketContext())
{
foreach (var item in products)
{
var product = new SupermarketSQL.Models.Product
{
ProductName = item.ProductName,
BasePrice = item.BasePrice,
MeasureId = item.MeasureId,
VendorId = item.VendorId
};
context.Products.Add(product);
}
context.SaveChanges();
}
}
}
public void FillTable()
{
SupermarketContext context = new SupermarketContext();
using (context)
{
foreach (var report in this.reports)
{
var supermarket = context.Supermarkets.Where(x => x.Name == report.Name).FirstOrDefault();
if (supermarket == null)
{
var newSupermarket = new Supermarket.Model.SalesReports.Supermarket()
{
Name = report.Name
};
context.Supermarkets.Add(newSupermarket);
supermarket = newSupermarket;
context.SaveChanges();
}
for (int i = 0; i < report.ProductID.Count; i++)
{
var newSalesReport = new SalesReport()
{
SupermarketId = supermarket.Id,
ProductId = report.ProductID[i],
Quantity = report.Quantity[i],
UnitPrice = report.UnitPrice[i],
Sum = report.Sum[i],
Date = report.Date
};
context.SalesReports.Add(newSalesReport);
}
}
context.SaveChanges();
}
}
private static void FromMySqlNeverAgain()
{
var dbcontext = new SupermarketContext();
var mySqlContx = new SupermarketModel();
using (mySqlContx)
{
var products = mySqlContx.Products.OrderBy(e => e.ID).ToList();
var vendors = mySqlContx.Vendors.ToList();
var mesuares = mySqlContx.Measures.ToList();
using (dbcontext)
{
foreach (var mesuare in mesuares)
{
var newMeasure = new Measure()
{
ID = mesuare.ID,
Name = mesuare.Name
};
dbcontext.Measures.Add(newMeasure);
}
foreach (var vendor in vendors)
{
var newVendor = new Vendor()
{
ID = vendor.ID,
Name = vendor.Name
};
dbcontext.Vendors.Add(newVendor);
}
foreach (var product in products)
{
var some = new Product
{
BasePrice = product.BasePrice,
Measure_ID = product.Measure_ID,
Name = product.Name,
Vendor_ID = product.Vendor_ID,
};
dbcontext.Products.Add(some);
}
dbcontext.SaveChanges();
}
}
}
///<summary>
///This is summary for method that transfer records from Oracle model into MQ SQL Server model.
///The method first select records from Oracle model which have not yet been copied or deleted.
///Second it inserts record into MS SQL Method and mark them as copied into Oracle model
///</summary>
public static void UpdateVendorsFromOracle()
{
var oracleContext = new OracleEntities();
var msSqLcontext = new SupermarketContext();
var vendors = oracleContext.VENDORS
.Where(v => v.ISCOPIED == false && v.ISDELETED == false)
.Select(v => new
{
v.VENDORNAME
})
.ToList();
if (vendors.Count > 0)
{
var addedVendorsList = new List <string>();
foreach (var vendor in vendors)
{
var vendorName = vendor.VENDORNAME;
try
{
msSqLcontext.Vendors.AddOrUpdate(
v => v.VendorName,
new Vendor()
{
VendorName = vendorName
});
msSqLcontext.SaveChanges();
addedVendorsList.Add(vendorName);
}
catch (Exception ex)
{
throw new ArgumentException();
}
}
var vendorsToChange = oracleContext.VENDORS.Where(v => addedVendorsList.Contains(v.VENDORNAME)).ToList();
vendorsToChange.ForEach(v => v.ISCOPIED = true);
oracleContext.SaveChanges();
Console.WriteLine("\nAdded new Vendors from OracleBD into MS SQL Server:");
vendorsToChange.ForEach(v => Console.WriteLine("Added vendor name: {0}", v.VENDORNAME));
}
else
{
Console.WriteLine("\nThere is no new records to import into VENDORS table!");
}
}
///<summary>
///This is summary for method that transfer records from Oracle model into MQ SQL Server model.
///The method first select records from Oracle model which have not yet been copied or deleted.
///Second it inserts record into MS SQL Method and mark them as copied into Oracle model
///</summary>
public static void UpdateProductsTypesFromOracle()
{
var oracleContext = new OracleEntities();
var msSqLcontext = new SupermarketContext();
var productstypes = oracleContext.PRODUCTSTYPES
.Where(pt => pt.ISCOPIED == false && pt.ISDELETED == false)
.Select(pt => new
{
pt.TYPENAME
}).ToList();
if (productstypes.Count > 0)
{
var addedProductsTypesList = new List <string>();
foreach (var type in productstypes)
{
var typeName = type.TYPENAME;
try
{
msSqLcontext.ProductTypes.AddOrUpdate(
pt => pt.TypeName,
new ProductType()
{
TypeName = typeName
});
msSqLcontext.SaveChanges();
addedProductsTypesList.Add(typeName);
}
catch (Exception ex)
{
throw new ArgumentException();
}
}
var typesToChange =
oracleContext.PRODUCTSTYPES.Where(pt => addedProductsTypesList.Contains(pt.TYPENAME)).ToList();
typesToChange.ForEach(pt => pt.ISCOPIED = true);
oracleContext.SaveChanges();
Console.WriteLine("\nAdded new Types from OracleBD into MS SQL Server:");
typesToChange.ForEach(tp => Console.WriteLine("Added types name: {0}", tp.TYPENAME));
}
else
{
Console.WriteLine("\nThere is no new records to import into PRODUCTSTYPES table!");
}
}
public static void MigrateFromMySqlToMSSql()
{
using (var productContext = new ProductContext())
{
using (var supermarketContext = new SupermarketContext())
{
MigrateVendorsTable(productContext, supermarketContext);
MigrateMeasuresTable(productContext, supermarketContext);
MigrateProductsTable(productContext, supermarketContext);
supermarketContext.SaveChanges();
}
}
}
///<summary>
///This is summary for method that transfer records from Oracle model into MQ SQL Server model.
///The method first select records from Oracle model which have not yet been copied or deleted.
///Second it inserts record into MS SQL Method and mark them as copied into Oracle model
///</summary>
public static void UpdateMeasuresFromOracle()
{
var oracleContext = new OracleEntities();
var msSqLcontext = new SupermarketContext();
var measures = oracleContext.MEASURES
.Where(m => m.ISCOPIED == false && m.ISDELETED == false)
.Select(m => new
{
m.MEASURENAME
}).ToList();
if (measures.Count > 0)
{
var addedMeasuresList = new List <string>();
foreach (var measure in measures)
{
var measureName = measure.MEASURENAME;
try
{
msSqLcontext.Measures.AddOrUpdate(
m => m.MeasureName,
new Measure()
{
MeasureName = measureName
});
msSqLcontext.SaveChanges();
addedMeasuresList.Add(measureName);
}
catch (Exception ex)
{
throw new ArgumentException();
}
}
var measuresToChange = oracleContext.MEASURES.Where(m => addedMeasuresList.Contains(m.MEASURENAME)).ToList();
measuresToChange.ForEach(m => m.ISCOPIED = true);
oracleContext.SaveChanges();
Console.WriteLine("\nAdded new Measures from OracleBD into MS SQL Server:");
measuresToChange.ForEach(m => Console.WriteLine("Added measure name: {0}", m.MEASURENAME));
}
else
{
Console.WriteLine("\nThere is no new records to import into MEASURES table!");
}
}
private static void FillExpensesInSql(ICollection <Expense> expenses)
{
SupermarketContext context = new SupermarketContext();
using (context)
{
foreach (var expense in expenses)
{
context.Expenses.Add(expense);
}
context.SaveChanges();
}
Console.WriteLine("5. Expenses saved in SQL Server!");
}
private void AddSale(int productId, int quantity, decimal unitPrice, decimal sum,
SupermarketContext sqlServerDb, int dateId, int supermarketId)
{
Sale sale = new Sale()
{
ProductId = productId,
Quantity = quantity,
UnitPrice = unitPrice,
Sum = sum,
SoldDateId = dateId,
SupermarketId = supermarketId
};
sqlServerDb.Sales.Add(sale);
sqlServerDb.SaveChanges();
}
/// <summary>
/// Check for existing product type, because we want to have distinct data in the database and if the product type do not exists the method add it to the database.
/// </summary>
/// <param name="prodTypeName">the type to be checked</param>
/// <param name="context">the Entity Framework connection to the database</param>
private void CheckProductType(string prodTypeName, SupermarketContext context)
{
var existPodType = context.ProductTypes
.Where(t => t.TypeName == prodTypeName)
.Select(t => t.TypeName)
.FirstOrDefault();
if (existPodType == null)
{
context.ProductTypes.Add(new ProductType
{
TypeName = prodTypeName
});
context.SaveChanges();
}
}
private static void ReplicateMeasures(SupermarketContext context)
{
var oracleDb = new OracleEntities();
var measures = oracleDb.MEASURES.ToList();
foreach (var measure in measures)
{
if (context.Measures.Any(m => m.Name == measure.NAME) == false)
{
context.Measures.Add(new Measure
{
Name = measure.NAME
});
}
}
context.SaveChanges();
}
private static void ReplicateCategories(SupermarketContext context)
{
var oracleDb = new OracleEntities();
var categories = oracleDb.CATEGORIES.ToList();
foreach (var category in categories)
{
if (context.Categories.Any(c => c.Name == category.NAME) == false)
{
context.Categories.Add(new Category
{
Name = category.NAME,
Description = category.DESCRIPTION
});
}
}
context.SaveChanges();
}
private static void InitializeDataFromExcel()
{
var db = new SupermarketContext();
var generator = new Generator(@"..\..\Sample-Sales-Reports.zip", @"..\..\");
var reportList = generator.Generate();
using (db)
{
foreach (var item in reportList)
{
db.DailyReports.Add(item);
}
db.SaveChanges();
Console.WriteLine("1. Data from Excel saved in SQL Server!");
}
}
private static void ReplicateCustomers(SupermarketContext context)
{
var oracleDb = new OracleEntities();
var customers = oracleDb.CUSTOMERS.ToList();
foreach (var customer in customers)
{
if (context.Customers.Any(c => c.Name == customer.NAME) == false)
{
context.Customers.Add(new Customer
{
Name = customer.NAME,
Address = customer.ADDRESS,
Phone = customer.PHONE
});
}
}
context.SaveChanges();
}
public void MigrateDataFromExcelFiles(string zipFilePath)
{
ExtractZipFile(zipFilePath);
using (var supermarketContext = new SupermarketContext())
{
IList <Models.Sale> allSales = new List <Models.Sale>();
GetSales(TempFolderForExtract, supermarketContext, allSales);
foreach (var sale in allSales)
{
supermarketContext.Sales.Add(sale);
}
supermarketContext.SaveChanges();
}
Directory.Delete(TempFolderForExtract, true);
}
/// <summary>
///Check for existing supermarket, because we want to have distinct data in the database and if the supermarket do not exists the method add it to the database.
/// </summary>
/// <param name="supName">the array containig the supermarket name</param>
/// <param name="context">the Entity Framework connection to the database</param>
private void CheckForExistingSupermarket(object[] supName, SupermarketContext context)
{
string marketName = this.SupermarketName(supName);
var existSupName = context.Supermarkets.Where(s => s.Name == marketName).Select(s => s.Name).FirstOrDefault();
if (existSupName == null)
{
context.Supermarkets.Add(new MS_SQL_Server.Supermarket
{
Name = marketName,
IsDeleted = false
});
context.SaveChanges();
Console.WriteLine("Supermarket: {0} added!", marketName);
}
else
{
Console.WriteLine("Supermarket: {0} existed!", marketName);
}
}
public static void ParseXML(string filePath)
{
StringBuilder result = new StringBuilder();
using (SupermarketContext context = new SupermarketContext())
{
string vendor = string.Empty;
string expence = string.Empty;
string date = string.Empty;
using (XmlReader reader = XmlReader.Create(filePath))
{
while (reader.Read())
{
if ((reader.NodeType == XmlNodeType.Element) &&
(reader.Name == "sale"))
{
vendor = (reader.GetAttribute(0)).ToString();
}
if ((reader.NodeType == XmlNodeType.Element) &&
(reader.Name == "expenses"))
{
date = (reader.GetAttribute(0)).ToString();
expence = (reader.ReadElementString());
}
Expense expenceObj = new Expense()
{
Date = date,
Expenses = expence,
VendorName = vendor
};
context.Expenses.Add(expenceObj);
context.SaveChanges();
}
}
}
}
private static void InitializeDataFromMySQL()
{
var db = new SupermarketContext();
SupermarketModel model = new SupermarketModel();
using (db)
{
foreach (var measure in model.Measures)
{
db.Measurments.Add(new Measure()
{
Id = measure.Id,
MeasureName = measure.MeasureName
});
}
foreach (var vendor in model.Vendors)
{
db.Vendors.Add(new Vendor()
{
Id = vendor.Id,
VendorName = vendor.VendorName
});
}
foreach (var product in model.Products)
{
db.Products.Add(new Product()
{
Id = product.Id,
BasePrice = product.BasePrice,
MeasureId = product.MeasureId,
ProductName = product.ProductName,
VendorId = product.VendorId
});
}
db.SaveChanges();
Console.WriteLine("1. Data from MySQL saved in SQL Server!");
}
}
private static void MoveMeasures()
{
var client = new SupermarketModel();
using (client)
{
var measures = client.Measures;
using (SupermarketContext context = new SupermarketContext())
{
foreach (var item in measures)
{
var measure = new SupermarketSQL.Models.Measure {
MeasureName = item.MeasureName
};
context.Measures.Add(measure);
}
context.SaveChanges();
}
}
}
/// <summary>
/// Insert the content of the excel files into the database.
/// </summary>
/// <param name="rowData">array of data containing name, type, price and quantity of the product</param>
/// <param name="context">the Entity Framework connection to the database</param>
/// <param name="reportDate">the date taken from the folder with the reports</param>
/// <param name="supmarketName">the name of the supermarket</param>
private void InsertIntoDataBase(object[] rowData, SupermarketContext context, string reportDate, string supmarketName)
{
// string[] inputNameType = rowData[0].ToString().Split();
// string prodType = inputNameType[0];
// string prodName = inputNameType[1];
string prodName = rowData[0].ToString();
int quantity = int.Parse(rowData[1].ToString());
float price = float.Parse(rowData[2].ToString());
DateTime dateReport = DateTime.ParseExact(reportDate, "dd-MMM-yyyy", CultureInfo.InvariantCulture);
//this.CheckProductType(prodType, context);
this.CheckProduct(prodName, price, context);
var productId = context.Products.Where(p => p.ProductName == prodName).Select(p => p.Id).FirstOrDefault();
var marketId = context.Supermarkets.Where(s => s.Name == supmarketName).Select(s => s.SupermarketId).FirstOrDefault();
var existMarketSalesProduct = context.SupermarketSalesProducts
.Where(s => s.SupermarketId == marketId &&
s.ProductId == productId &&
s.SalesDate == dateReport)
.Select(s => new
{
s.ProductId,
s.SupermarketId,
s.SalesDate
}).FirstOrDefault();
if (existMarketSalesProduct == null)
{
context.SupermarketSalesProducts.Add(new SupermarketSalesProduct
{
SupermarketId = marketId,
ProductId = productId,
Quantity = quantity,
Price = (decimal)price,
SalesDate = dateReport
});
context.SaveChanges();
}
}
/// <summary>
/// Check for existing product type, because we want to have distinct data in the database and if the product do not exists the method add it to the database.
/// </summary>
/// <param name="productName">the name to be checked</param>
/// <param name="price">the price needed to be eventually created new product</param>
/// <param name="context">the Entity Framework connection to the database</param>
private void CheckProduct(string productName, float price, SupermarketContext context)
{
var existProd = context.Products
.Where(p => p.ProductName == productName)
.Select(p => p.ProductName)
.FirstOrDefault();
if (existProd == null)
{
context.Products.Add(new Product
{
ProductName = productName,
Price = price
});
context.SaveChanges();
Console.WriteLine("Product: {0} added!", productName);
}
else
{
Console.WriteLine("Product: {0} existed!", productName);
}
}
public void Export(string xmlFullFileName, SupermarketContext sqlServerContext)
{
XDocument xmlDoc = XDocument.Load(xmlFullFileName);
var sales = xmlDoc.Descendants("sale").ToList();
int vendorId = -1;
string vendorName = string.Empty;
foreach (var sale in sales)
{
vendorName = sale.Attribute("vendor").Value;
vendorId = sqlServerContext.Vendors.Where(x => x.VendorName == vendorName).Select(x => x.Id).FirstOrDefault();
var expenses = sale.Descendants("expenses");
foreach (var expense in expenses)
{
MongoDbExpenseFormat expenseObject = new MongoDbExpenseFormat()
{
VendorName = vendorName,
VendorId = vendorId,
Month = DateTime.Parse(expense.Attribute("month").Value),
Expense = decimal.Parse(expense.Value),
};
MongoDbProvider.db.SaveData <MongoDbExpenseFormat>(expenseObject);
Expense sqlExpense = new Expense()
{
VendorId = expenseObject.VendorId,
Month = expenseObject.Month,
ExpenseValue = expenseObject.Expense
};
sqlServerContext.Expenses.Add(sqlExpense);
sqlServerContext.SaveChanges();
}
}
}
private void LoadVendorExpensesToSqlServer(IList <string[]> expenses)
{
using (var supermarketContext = new SupermarketContext())
{
foreach (var expense in expenses)
{
string vendorName = expense[0];
int vendorID = supermarketContext.Vendors.First(v => v.VendorName == vendorName).ID;
decimal value = decimal.Parse(expense[2]);
Models.Expense newExpense = new Models.Expense()
{
VendorID = vendorID,
Month = DateTime.Parse(expense[1]),
Value = value
};
supermarketContext.Expenses.Add(newExpense);
}
supermarketContext.SaveChanges();
}
}
private static void MoveVendors()
{
var client = new SupermarketModel();
using (client)
{
var vendors = client.Vendors;
using (SupermarketContext context = new SupermarketContext())
{
foreach (var item in vendors)
{
var vendor = new SupermarketSQL.Models.Vendor
{
VendorName = item.VendorName
};
context.Vendors.Add(vendor);
}
context.SaveChanges();
}
}
}
public static void MoveData()
{
var sqliteData = new SupermarketSQLite();
using (sqliteData)
{
var taxes = sqliteData.Taxes;
using (SupermarketContext context = new SupermarketContext())
{
foreach (var item in taxes)
{
var tax = new SupermarketSQL.Models.TaxTable
{
ProductId = item.ProductId,
Tax = item.Tax
};
context.Taxes.Add(tax);
sqliteData.SaveChanges();
}
context.SaveChanges();
}
}
}
private static void ReplicateProducts(SupermarketContext context)
{
var oracleDb = new OracleEntities();
var products = oracleDb.PRODUCTS.ToList();
foreach (var product in products)
{
if (context.Products.Any(p => p.Name == product.NAME) == false)
{
context.Products.Add(new Product
{
Name = product.NAME,
Price = product.PRICE,
SupplierId = (int)product.SUPPLIER_ID,
CategoryId = (int)product.CATEGORY_ID,
MeasureId = (int)product.MEASURE_ID
});
}
}
context.SaveChanges();
}
public bool Save()
{
return(_context.SaveChanges() >= 0);
}
public static void ReadExcelData()
{
var dbContext = new SupermarketContext();
using (dbContext)
{
string[] subDirs;
subDirs = Directory.GetDirectories(@"../../../../Reports/Sample-Sales-Extracted Files");
foreach (var dir in subDirs)
{
string[] files = Directory.GetFiles(dir);
//Console.WriteLine("Directory: " + dir);
//Console.WriteLine("Files:");
for (int i = 0; i < files.Length; i++)
{
string fileName = new FileInfo(files[i]).Name;
int length = fileName.Length;
DateTime date = Convert.ToDateTime(fileName.Substring(0 + length - 15, 11));
//Console.WriteLine("{0:dd-MM-yyyy}",date);
string currentFilePath = (dir + @"\" + new FileInfo(files[i]).Name);
string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;" +
"Data Source=" + currentFilePath + ";Persist Security Info=False";
OleDbConnectionStringBuilder conString = new OleDbConnectionStringBuilder(connectionString);
conString.Add("Extended Properties", "Excel 12.0 Xml;HDR=YES");
OleDbConnection dbConn = new OleDbConnection(conString.ConnectionString);
dbConn.Open();
using (dbConn)
{
DataTable dataSet = new DataTable();
string selectSql = @"SELECT * FROM [Sales$]";
OleDbDataAdapter adapter = new OleDbDataAdapter(selectSql, dbConn);
adapter.Fill(dataSet);
int rows = dataSet.Rows.Count;
string location = dataSet.Rows[0].ItemArray[0].ToString();
// Console.WriteLine("Company name: " + location);
for (int row = 2; row < rows - 2; row++)
{
Sale currentSale = new Sale();
Location currentLocation = new Location();
// currentLocation.Name = location;
dbContext.Locations.Add(currentLocation);
currentSale.Location = currentLocation;
int productID = Convert.ToInt32(dataSet.Rows[row].ItemArray[0]);
var product = from p in dbContext.Products
where p.ID == productID
select p;
Product currentProduct = product.First();
// Console.WriteLine("productID = "+productID);
currentSale.Quantity = Convert.ToInt32(dataSet.Rows[row].ItemArray[1]);
currentSale.UnitPrice = Convert.ToDecimal(dataSet.Rows[row].ItemArray[2]);
currentSale.Sum = Convert.ToDecimal(dataSet.Rows[row].ItemArray[3]);
currentSale.Date = date;
currentSale.Product = currentProduct;
//Console.WriteLine(currentSale.Location.Name + " " + currentSale.Sum);
dbContext.Sales.Add(currentSale);
// dbContext.Products.Add(currentProduct);
dbContext.SaveChanges();
}
}
}
}
}
}
private static void ReplicateSuppliers(SupermarketContext context)
{
var oracleDb = new OracleEntities();
var suppliers = oracleDb.SUPPLIERS.ToList();
foreach (var supplier in suppliers)
{
if (context.Suppliers.Any(s => s.Name == supplier.NAME) == false)
{
context.Suppliers.Add(new Supplier
{
Name = supplier.NAME,
Address = supplier.ADDRESS,
Phone = supplier.PHONE
});
}
}
context.SaveChanges();
}
private ICollection <Models.Sale> GetSalesFromExcelFiles(
string directory, SupermarketContext supermarketContext)
{
IList <Models.Sale> sales = new List <Models.Sale>();
string[] excelFilesPaths = Directory.GetFiles(directory, "*.xls");
foreach (var excelFilePath in excelFilesPaths)
{
string excelConnectionString = string.Format(
Settings.Default.ExcelReadConnectionString, excelFilePath);
OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
excelConnection.Open();
DataSet dataSet = new DataSet();
using (excelConnection)
{
string selectAllRowsCommandString = "SELECT * FROM [Sales$]";
OleDbCommand selectAllRowsCommand = new OleDbCommand(selectAllRowsCommandString, excelConnection);
OleDbDataAdapter excelAdapter = new OleDbDataAdapter(selectAllRowsCommand);
excelAdapter.Fill(dataSet, "Sales");
}
DataRowCollection excelRows = dataSet.Tables["Sales"].Rows;
string supermarketName = excelRows[0][0].ToString();
if (!supermarketContext.Supermarkets.Any(s => s.Name == supermarketName))
{
supermarketContext.Supermarkets.Add(new Models.SupermarketBranch()
{
Name = supermarketName
});
supermarketContext.SaveChanges();
}
for (int i = 2; i < excelRows.Count - 2; i++)
{
int productID = 0;
int.TryParse(excelRows[i][0].ToString(), out productID);
if (productID != 0)
{
int quantity = 0;
int.TryParse(excelRows[i][1].ToString(), out quantity);
decimal unitPrice = 0;
decimal.TryParse(excelRows[i][2].ToString(), out unitPrice);
decimal sum = 0;
decimal.TryParse(excelRows[i][3].ToString(), out sum);
int supermarketID =
supermarketContext.Supermarkets.First(s => s.Name == supermarketName).ID;
string saleDateString = Path.GetFileName(Path.GetDirectoryName(excelFilePath));
sales.Add(new Models.Sale()
{
Date = DateTime.Parse(saleDateString),
Product = supermarketContext.Products.Find(productID),
Quantity = quantity,
Sum = sum,
Supermarket = supermarketContext.Supermarkets.Find(supermarketID),
UnitPrice = unitPrice
});
}
}
}
return(sales);
}
private static void ReplicateOrders(SupermarketContext context)
{
var oracleDb = new OracleEntities();
var orders = oracleDb.ORDERS.ToList();
foreach (var order in orders)
{
if (context.Orders.Any(o => o.Id == order.ID) == false)
{
context.Orders.Add(new Order
{
Quantity = order.QUANTITY,
ProductId = (int)order.PRODUCT_ID,
Discount = order.DISCOUNT,
Date = order.ORDER_DATE,
CustomerId = (int)order.CUSTOMER_ID
});
}
}
context.SaveChanges();
}