public static void ImportExpensesInDatabase(string fileName)
{
var context = new MsSqlEntities();
XDocument doc = XDocument.Load(fileName);
var vendorsXml = doc.XPathSelectElements("expenses-by-month/vendor");
foreach (var vendorXml in vendorsXml)
{
string vendorName = vendorXml.Attribute("name").Value;
var expensesXml = vendorXml.XPathSelectElements("expenses");
foreach (var expsenseXml in expensesXml)
{
DateTime month = DateTime.Parse(expsenseXml.Attribute("month").Value);
decimal sum = decimal.Parse(expsenseXml.Value);
Vendor vendor = context.Vendors
.FirstOrDefault(v => v.Name == vendorName);
if (vendor == null)
{
Vendor newVendor = new Vendor();
newVendor.Name = vendorName;
context.Vendors.Add(newVendor);
context.SaveChanges();
vendor = newVendor;
}
int vendorId = vendor.Id;
Expense newExpense = new Expense();
newExpense.Date = month;
newExpense.Sum = sum;
newExpense.VendorId = vendorId;
context.Expenses.Add(newExpense);
}
}
context.SaveChanges();
}
public static void DisplayMsSqlMenu()
{
bool sqlExit = false;
while (true)
{
Console.WriteLine("\nMS SQL Options:");
Console.WriteLine("1) Display Aggregated Data:");
Console.WriteLine("2) Load Data from Oracle Database.");
Console.WriteLine("3) Load Excel Reports from ZIP File");
Console.WriteLine("0) Exit.");
Console.Write("\nPlease, select option: ");
string sqlChoice = Console.ReadLine();
switch (sqlChoice)
{
case "0":
sqlExit = true;
break;
case "1":
Console.WriteLine("Selected option: " + sqlChoice);
break;
case "2":
Console.WriteLine("Selected option: " + sqlChoice);
CloneOracleDbToSql.Run();
break;
case "3":
Console.WriteLine("Selected option: " + sqlChoice);
var context = new MsSqlEntities();
var selectedFile = OpenFile();
var fileName = ExtractFileName(selectedFile);
var path = Path.GetDirectoryName(selectedFile);
var data = new ExcelImport(path, fileName).GetSales(context);
context.Sales.AddRange(data);
context.SaveChanges();
break;
default:
Console.WriteLine("Invalid selection!");
break;
}
if (sqlExit)
{
break;
}
}
}
private Supermarket CheckSupermarketExist(string supermarketName, MsSqlEntities context)
{
var supermarket = context.Supermarkets.FirstOrDefault(s => s.Name == supermarketName);
if (supermarket == null)
{
// TODO: Add new supermarket from report.
supermarket = new Supermarket { Name = supermarketName };
context.Supermarkets.Add(supermarket);
context.SaveChanges();
}
return supermarket;
}
private Product CheckValidProduct(string productName, MsSqlEntities context)
{
var product = context.Products.FirstOrDefault(p => p.Name == productName);
if (product == null)
{
// TODO: Add new products from report fuctionality.
product = new Product
{
Name = productName,
CategoryId = 2,
MeasureId = 2,
Vendor = context.Vendors.Find(9),
Price = 31.2m
};
context.Products.Add(product);
context.SaveChanges();
}
return product;
}