public void MigrateDataFromExcelFiles()
{
ExtractZipFile(ReportsFilePath);
using (var supermarketContext = new SagittaDBEntities())
{
IList <SalesReport> allSales = new List <SalesReport>();
GetSales(TempFolderForExtract, supermarketContext, allSales);
foreach (var sale in allSales)
{
supermarketContext.SalesReports.Add(sale);
}
supermarketContext.SaveChanges();
}
Directory.Delete(TempFolderForExtract, true);
}
public static void PolulateSqlTables(string filePath)
{
XmlDocument xmlDoc = new XmlDocument();
xmlDoc.Load(filePath);
XmlElement root = xmlDoc.DocumentElement;
XmlNodeList xmlVendors = root.SelectNodes("/expenses-by-month/vendor");
using (var context = new SagittaDBEntities())
{
var dbVendors = context.Vendors;
foreach (XmlNode vendor in xmlVendors)
{
var vendorName = vendor.Attributes["name"].Value;
XmlNodeList expenses = vendor.SelectNodes("expenses");
for (int i = 0; i < expenses.Count; i++)
{
var expense = expenses[i];
var expenceMonth = expense.Attributes["month"].Value;
var expenceDate = DateTime.Parse(expenceMonth);
var expenceSum = decimal.Parse(expenses[i].InnerText);
var vendorId = dbVendors
.Where(v => v.Vendor_Name == vendorName)
.Select(v => v.ID).FirstOrDefault();
var expenseEntity = new ExpensesByMonth()
{
ExpenseMonth = expenceDate,
Expenses = expenceSum,
VendorId = vendorId
};
context.ExpensesByMonths.Add(expenseEntity);
context.SaveChanges();
}
}
}
}
private ICollection <SalesReport> GetSalesFromExcelFiles(string directory,
SagittaDBEntities supermarketContext)
{
IList <SalesReport> sales = new List <SalesReport>();
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, "SalesReports");
}
DataRowCollection excelRows = dataSet.Tables["SalesReports"].Rows;
string supermarketName = excelRows[0][0].ToString();
if (!supermarketContext.Supermarkets.Any(s => s.Name == supermarketName))
{
supermarketContext.Supermarkets.Add(new Supermarket()
{
Name = supermarketName
});
supermarketContext.SaveChanges();
}
for (int i = 2; i < excelRows.Count - 1; i++)
{
string productName = excelRows[i][0].ToString();
var product = supermarketContext.Products.FirstOrDefault(p => p.Product_Name == productName);
if (product != null)
{
int productID = product.ID;
int quantity = 0;
int.TryParse(excelRows[i][1].ToString(), out quantity);
decimal unitPrice = 0;
decimal.TryParse(excelRows[i][2].ToString(), out unitPrice);
int supermarketID =
supermarketContext.Supermarkets.First(s => s.Name == supermarketName).Id;
string saleDateString = Path.GetFileName(Path.GetDirectoryName(excelFilePath));
sales.Add(new SalesReport()
{
ReportDate = DateTime.Parse(saleDateString),
Product = supermarketContext.Products.Find(productID),
Quantity = quantity,
Supermarket = supermarketContext.Supermarkets.Find(supermarketID),
ActualPrice = unitPrice
});
}
else
{
// todo add new product
//supermarketContext.Products.Add(new Product()
//{
// Product_Name = productName,
// VendorID = 1,
// Measure = new Measure(),
// Price = 1
//});
}
}
}
return(sales);
}