private void GetSales(string directory,
SagittaDBEntities supermarketContext, IList <SalesReport> allSales)
{
foreach (var sale in GetSalesFromExcelFiles(directory, supermarketContext))
{
allSales.Add(sale);
}
string[] subDirectories = Directory.GetDirectories(directory);
if (subDirectories.Length > 0)
{
for (int i = 0; i < subDirectories.Length; i++)
{
GetSales(subDirectories[i], supermarketContext, allSales);
}
}
}
public Dictionary <string, List <Summary> > ReadData(DateTime startDate, DateTime endDate)
{
//string outputData = string.Empty;
using (SagittaDBEntities msSqlDB = new SagittaDBEntities())
{
//var dbVendors = msSqlDB.SalesReports;
var salesResult = new Dictionary <string, List <Summary> >();
var sales = msSqlDB.SalesReports.Select(s => new
{
s.Product,
s.Product.Vendor,
s.ReportDate
});
foreach (var sale in sales)
{
if (startDate <= sale.ReportDate && sale.ReportDate <= endDate)
{
var vendorName = sale.Product.Vendor.Vendor_Name;
if (!salesResult.ContainsKey(vendorName))
{
salesResult[vendorName] = new List <Summary>();
}
var summary = new Summary(sale.ReportDate, sale.Product.Price);
var summariesWithEqualDates = salesResult[vendorName].Where(x => x.DateOfSale == summary.DateOfSale);
if (summariesWithEqualDates.Count() == 0)
{
salesResult[vendorName].Add(summary);
}
else
{
summariesWithEqualDates.FirstOrDefault().TotalSum += summary.TotalSum;
}
}
}
return(salesResult);
}
}
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 void MigrateDataSqlServerToFromMySql()
{
using (var contextSqlServer = new SagittaDBEntities())
{
using (var contextMySqlServer = new sagittadb_mysqlEntities())
{
foreach (var vendorSqlServer in contextSqlServer.Vendors)
{
if (!contextMySqlServer.vendors
.Any(v => v.Name == vendorSqlServer.Vendor_Name))
{
contextMySqlServer.vendors.Add(new vendor()
{
Name = vendorSqlServer.Vendor_Name
});
}
}
}
}
}
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);
}