private void GetSales(string directory,
BottleGreenEntities 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)
{
using (var contextBg = new BottleGreenEntities())
{
var salesResult = new Dictionary<string, List<Summary>>();
var sales = contextBg.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 BottleGreenEntities())
{
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 GenerateJsonReports(DateTime start, DateTime end)
{
if (!Directory.Exists(@"../../../Json-Reports/"))
{
Directory.CreateDirectory(@"../../../Json-Reports/");
}
using (var context = new BottleGreenEntities())
{
var products = GetProducts(context, start, end);
var serializer = new JavaScriptSerializer();
foreach (var product in products)
{
if (product.TotalQuantitySold == null)
{
product.TotalQuantitySold = 0;
}
if (product.TotalIncomes == null)
{
product.TotalIncomes = 0;
}
string path = "../../../Json-Reports/";
string dirName = String.Format("{0}.{1}.{2}-{3}.{4}.{5}/",
start.Year, start.Month, start.Day,
end.Year, end.Month, end.Day);
path = path + dirName;
if (!Directory.Exists(@path))
{
Directory.CreateDirectory(@path);
}
var json = JsonConvert.SerializeObject(product, Formatting.Indented);
path = path + string.Format("{0}.json", product.ProductId);
System.IO.File.WriteAllText(path, json);
// opens files in notepad
var xmlReaderStr = "notepad.exe";
Process.Start(xmlReaderStr, path);
}
}
}
public void UploadMongoReports(DateTime start, DateTime end)
{
var client = new MongoClient();
var db = client.GetDatabase("BottleGreen");
var collection = db.GetCollection<Report>("SalesByProductReports");
using (var context = new BottleGreenEntities())
{
var reports = GetProducts(context, start, end);
foreach (var report in reports)
{
if (report.TotalQuantitySold == null)
{
report.TotalQuantitySold = 0;
}
if (report.TotalIncomes == null)
{
report.TotalIncomes = 0;
}
collection.InsertOneAsync(report);
}
}
}
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 BottleGreenEntities())
{
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 = context.Vendors.FirstOrDefault(v => v.Vendor_Name == vendorName).ID;
var expenceEntity = new ExpensesByMonth()
{
ExpenseMonth = expenceDate,
Expenses = expenceSum,
VendorId = vendorId
};
context.ExpensesByMonths.Add(expenceEntity);
}
}
context.SaveChanges();
}
}
private static IQueryable<Report> GetProducts(BottleGreenEntities context, DateTime start, DateTime end)
{
var products = from p in context.Products
where p.SalesReports.Any(r=>r.ReportDate <= end) &&
p.SalesReports.Any(r=>r.ReportDate >= start)
select new Report()
{
ProductId = p.ID,
ProductName = p.Product_Name,
VendorName = p.Vendor.Vendor_Name,
TotalQuantitySold = p.SalesReports.Select(s => s.Quantity).Sum(),
TotalIncomes = p.SalesReports.Select(s => s.Quantity).Sum()*p.Price,
Start = start,
End = end
};
return products;
}
private ICollection<SalesReport> GetSalesFromExcelFiles(string directory,
BottleGreenEntities 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;
}