public static void GenerateProductReports(string folderName)
{
DirectoryInfo directory = Directory.CreateDirectory(folderName);
List<MongoProductReport> mongoProductsReports = new List<MongoProductReport>();
MongoDBManager<MongoProductReport> mongo = new MongoDBManager<MongoProductReport>();
using (var msSQLServerContext = new SupermarketEntities())
{
var sales =
from product in msSQLServerContext.Products
join vendor in msSQLServerContext.Vendors
on product.VendorId equals vendor.VendorId
join sale in msSQLServerContext.Sales
on product.ProductId equals sale.ProductId
group sale by sale.ProductId into productsById
select new
{
ProductId = productsById.Key,
SalesTotalQuantity = productsById.Sum(s => s.ProductQuantity),
SalesTotalSum = productsById.Sum(s => s.ProductTotalSum)
};
var productReports =
from sale in sales
join product in msSQLServerContext.Products
on sale.ProductId equals product.ProductId
join vendor in msSQLServerContext.Vendors
on product.VendorId equals vendor.VendorId
select new
{
ProductId = sale.ProductId,
ProductName = product.ProductName,
VendorName = vendor.VendorName,
SalesTotalQuantity = sale.SalesTotalQuantity,
SalesTotalSum = sale.SalesTotalSum
};
foreach (var productReport in productReports)
{
JObject jsonObject = new JObject(
new JProperty("product-id", productReport.ProductId),
new JProperty("product-name", productReport.ProductName),
new JProperty("vendor-name", productReport.VendorName),
new JProperty("total-quantity-sold", productReport.SalesTotalQuantity),
new JProperty("total-incomes", productReport.SalesTotalSum));
string filePath = Path.Combine(folderName, string.Format("{0}.json", productReport.ProductId));
using (FileStream file = File.Create(filePath))
{
using (StreamWriter writer = new StreamWriter(file))
{
writer.Write(jsonObject.ToString());
}
}
MongoProductReport mongoProductReport = new MongoProductReport
{
ProductId = productReport.ProductId,
ProductName = productReport.ProductName,
VendorName = productReport.VendorName,
TotalQuantitySold = productReport.SalesTotalQuantity,
TotalIncomes = productReport.SalesTotalSum
};
mongoProductsReports.Add(mongoProductReport);
}
}
mongo.InsertInMongoDB(mongoProductsReports, "ProductsReports");
}
/// <summary>
/// Method for extracting expenses reports in XML format
/// </summary>
/// <param name="filePath">Filepath for the .xml file</param>
public static void ReadVendorMonthlyExpenses(string filePath)
{
XmlReader reader = XmlReader.Create(filePath);
List<MongoVendorExpense> mongoVendorExpensesList = new List<MongoVendorExpense>();
using (var msSQLServerContext = new SupermarketEntities())
{
using (reader)
{
int vendorId = 0;
while (reader.Read())
{
//Insert a new vendor
if (reader.NodeType == XmlNodeType.Element && reader.Name == "sale")
{
string vendorName = reader.GetAttribute("vendor");
var vendor = msSQLServerContext.Vendors.FirstOrDefault(v => v.VendorName == vendorName);
if (vendor == null)
{
// the vendor doesn't exist
vendor = new Vendor
{
VendorName = vendorName
};
msSQLServerContext.Vendors.Add(vendor);
msSQLServerContext.SaveChanges();
}
vendorId = vendor.VendorId;
}
//Insert Expenses
if (reader.NodeType == XmlNodeType.Element && reader.Name == "expenses")
{
string month = reader.GetAttribute("month");
DateTime monthAsDate = DateTime.ParseExact(month, "MMM-yyyy", CultureInfo.InvariantCulture);
decimal amount = reader.ReadElementContentAsDecimal();
var vendorMonthlyExpense = new VendorExpense
{
VendorId = vendorId,
Month = monthAsDate.Month,
Year = monthAsDate.Year,
Amount = amount
};
var mongoVendorExpense = new MongoVendorExpense
{
VendorId = vendorId,
Month = monthAsDate.Month,
Year = monthAsDate.Year,
Amount = amount
};
mongoVendorExpensesList.Add(mongoVendorExpense);
msSQLServerContext.VendorExpenses.Add(vendorMonthlyExpense);
}
}
}
MongoDBManager<MongoVendorExpense> mongoDBInserter = new MongoDBManager<MongoVendorExpense>();
mongoDBInserter.InsertInMongoDB(mongoVendorExpensesList, "VendorExpenses");
msSQLServerContext.SaveChanges();
}
}
