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 ExportSingleExcelFile(FileInfo file, int dateId,
OleDbConnection excelConnection, SupermarketContext sqlServerDb)
{
excelConnection.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;" +
string.Format("Data Source={0};", file.FullName) +
@"Extended Properties=""Excel 12.0;HDR=YES""";
excelConnection.Open();
int supermarketId = -1;
string supermarketName = file.Name.Substring(0, file.Name.IndexOf("-Sales-"));
if (sqlServerDb.Supermarkets.
Where(x => x.Name == supermarketName).Count() == 0)
{
sqlServerDb.Supermarkets.Add(new Supermarket()
{
Name = supermarketName
});
sqlServerDb.SaveChanges();
}
supermarketId = sqlServerDb.Supermarkets.
Where(x => x.Name == supermarketName).
Select(x => x.SupermarketId).First();
DataTable table = new DataTable();
using (excelConnection)
{
OleDbDataAdapter adapter = new OleDbDataAdapter(@"SELECT * FROM [Sales$]", excelConnection);
adapter.Fill(table);
for (int i = 0; i < table.Rows.Count - 1; i++)
{
int productId = -1, quantity = -1;
decimal unitPrice = -1, sum = -1;
IEnumerable<object> data = table.Rows[i].ItemArray.SkipWhile(x => x.GetType().Equals(typeof(System.DBNull)));
int currentColumnIndex = 0;
foreach (var item in data)
{
if (item.GetType() == typeof(System.DBNull))
{
//Console.WriteLine(item.GetType());
continue;
}
else
{
switch (currentColumnIndex)
{
case 0:
{
productId = (int)((double) item);
break;
}
case 1:
{
quantity = (int)((double) item);
break;
}
case 2:
{
unitPrice = (decimal)((double) item);
break;
}
case 3:
{
sum = (decimal)((double) item);
break;
}
}
}
currentColumnIndex++;
}
if (productId != -1)
{
//Console.WriteLine("{0}. {1} -> {2}", i, currentColumnIndex, item);
AddSale(productId, quantity, unitPrice, sum,
sqlServerDb, dateId, supermarketId);
}
}
}
}
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);
}
}
}
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();
}
