private static void GenerateAggregatedXMLSalesReport() { ProductsDBContext sqlContext = new ProductsDBContext(); XElement xmlDocument = new XElement("sales"); var vendors = sqlContext.Vendors.ToList(); foreach (var vendor in vendors) { XElement vendorName = new XElement("sale", new XAttribute("vendor", vendor.VendorName)); var salesReports = sqlContext.SalesReports.Where(salesReport => salesReport.Product.Vendor.VendorId == vendor.VendorId) .OrderBy(salesReport => salesReport.Date).ToList(); DateTime previousDate = new DateTime(); decimal expenses = 0; foreach (var salesReport in salesReports) { DateTime currentDate = salesReport.Date; if (currentDate != previousDate) { if (previousDate != new DateTime()) { XElement xmlElement = new XElement("summary", new XAttribute("date", string.Format("{0:dd-MMM-yyyy}", currentDate)), new XAttribute("total-sum", expenses) ); vendorName.Add(xmlElement); expenses = 0; } previousDate = currentDate; } expenses += salesReport.Quantity * salesReport.UnitPrice; } xmlDocument.Add(vendorName); } xmlDocument.Save(@"..\..\Data\Aggregated-SalesReport-ByDates.xml"); }
private static void UpdateDataBasesFromXML() { ProductsDBContext sqlContext = new ProductsDBContext(); MongoClient client = new MongoClient("mongodb://localhost/"); MongoServer server = client.GetServer(); var productsDB = server.GetDatabase("ProductsDB"); MongoCollection<Expense> expensesMongo = productsDB.GetCollection<Expense>("Expenses"); using (XmlReader reader = XmlReader.Create(@"..\..\Data\VendorExpenses.xml")) { DateTime month = new DateTime(); decimal amount = 0; string vendorName = string.Empty; while (reader.Read()) { if (reader.NodeType == XmlNodeType.Element) { if (reader.Name == "sale") { vendorName = reader.GetAttribute("vendor"); } else if (reader.Name == "expenses") { month = DateTime.Parse(string.Format("{0:MMM-YYYY}", reader.GetAttribute("month"))); } } else if (reader.NodeType == XmlNodeType.Text) { amount = decimal.Parse(reader.Value); ProductsDB.Model.Vendor vendor = sqlContext.Vendors.Where(v => v.VendorName == vendorName) .FirstOrDefault(); Expense expenseSQL = new Expense() { Amount = amount, Vendor = vendor, Date = month }; Expense expenseMongo = new Expense() { Amount = amount, VendorId = vendor.VendorId, Date = month }; sqlContext.Expenses.Add(expenseSQL); expensesMongo.Insert(expenseMongo); } } } sqlContext.SaveChanges(); }
private static void SaveJSONReports() { List<JSONReport> JSONReports = new List<JSONReport>(); using (ProductsDBContext sqlContext = new ProductsDBContext()) { var products = sqlContext.Products.ToList(); foreach (var prod in products) { JSONReport report = new JSONReport() { ProductId = prod.ProductId, ProductName = prod.ProductName, VendorName = prod.Vendor.VendorName, }; foreach (var salesReport in sqlContext.SalesReports) { if (salesReport.ProductId == prod.ProductId) { report.TotalQuantitySold += salesReport.Quantity; report.TotalIncome += report.TotalQuantitySold * salesReport.UnitPrice; } } JSONReports.Add(report); } } StreamWriter writer = new StreamWriter(@"..\..\Data\JSONReports.json"); StringBuilder sb = new StringBuilder(); foreach (var report in JSONReports) { var json = new JavaScriptSerializer().Serialize(report); sb.AppendLine(json); } using (writer) { writer.Write(sb.ToString()); } }
private static void LoadSalesReports() { List<SalesReport> salesReports = ProcessSalesReports(); using (ProductsDBContext sqlContext = new ProductsDBContext()) { foreach (var salesReport in salesReports) { sqlContext.SalesReports.Add(salesReport); } sqlContext.SaveChanges(); } }
private static void LoadMySQLEntries() { using (ProductsDBContext sqlContext = new ProductsDBContext()) { ProductsDBModel mySQLContext = new ProductsDBModel(@"Server=localhost;Database=products_db;Uid=Rami;Pwd=killer91;"); foreach (var measure in mySQLContext.Measures) { ProductsDB.Model.Measure newMeasure = new Model.Measure() { MeasureId = measure.Measure_ID, MeasureName = measure.Measure_Name }; sqlContext.Measures.Add(newMeasure); } sqlContext.SaveChanges(); foreach (var vendor in mySQLContext.Vendors) { ProductsDB.Model.Vendor newVendor = new Model.Vendor() { VendorId = vendor.Vendor_ID, VendorName = vendor.Vendor_Name }; sqlContext.Vendors.Add(newVendor); } sqlContext.SaveChanges(); foreach (var prod in mySQLContext.Products) { ProductsDB.Model.Vendor vendor = sqlContext.Vendors.Where (v => v.VendorId == prod.Vendors_Vendor_ID).First(); ProductsDB.Model.Measure measure = sqlContext.Measures.Where (m => m.MeasureId == prod.Measures_Measure_ID).First(); ProductsDB.Model.Product newProd = new Model.Product() { ProductId = prod.Products_ID, ProductName = prod.Product_Name, VendorId = prod.Vendors_Vendor_ID, Vendor = vendor, MeasureId = prod.Measures_Measure_ID, Measure = measure, BasePrice = prod.BasePrice, }; sqlContext.Products.Add(newProd); } sqlContext.SaveChanges(); } }
private static void GeneratePDFReports() { ProductsDBContext sqlContext = new ProductsDBContext(); string path = @"..\..\Data" + @"\SalesReports.pdf"; Document document = new Document(); FileStream fileStream = File.Create(path); PdfWriter.GetInstance(document, fileStream); document.Open(); PdfPTable mainTable = new PdfPTable(10); DateTime currentDate = new DateTime(); decimal currentTotalSum = 0; bool isFirst = true; var sortedSalesReports = (from salesReport in sqlContext.SalesReports orderby salesReport.Date select salesReport).ToList(); foreach (var salesReport in sortedSalesReports) { if (currentDate != salesReport.Date) { if (!isFirst) { var totalSumCell = new PdfPCell(new Phrase("Total Sum : " + currentTotalSum.ToString())); totalSumCell.BackgroundColor = BaseColor.GREEN; totalSumCell.Colspan = 10; totalSumCell.HorizontalAlignment = iTextSharp.text.Element.ALIGN_RIGHT; mainTable.AddCell(totalSumCell); currentTotalSum = 0; } isFirst = false; currentDate = salesReport.Date; var dateCell = new PdfPCell(new Phrase(string.Format("Date: {0:dd-MMM-yyyy} ", currentDate))); dateCell.BackgroundColor = BaseColor.LIGHT_GRAY; dateCell.Colspan = 10; dateCell.HorizontalAlignment = iTextSharp.text.Element.ALIGN_LEFT; mainTable.AddCell(dateCell); var productCell = new PdfPCell(new Phrase("Product")); productCell.BackgroundColor = BaseColor.LIGHT_GRAY; productCell.Colspan = 2; productCell.HorizontalAlignment = 1; mainTable.AddCell(productCell); var quantityCell = new PdfPCell(new Phrase("Quantity")); quantityCell.BackgroundColor = BaseColor.LIGHT_GRAY; quantityCell.Colspan = 2; quantityCell.HorizontalAlignment = 1; mainTable.AddCell(quantityCell); var unitPriceCell = new PdfPCell(new Phrase("UnitPrice")); unitPriceCell.BackgroundColor = BaseColor.LIGHT_GRAY; unitPriceCell.Colspan = 2; unitPriceCell.HorizontalAlignment = 1; mainTable.AddCell(unitPriceCell); var locationCell = new PdfPCell(new Phrase("Location")); locationCell.BackgroundColor = BaseColor.LIGHT_GRAY; locationCell.Colspan = 3; locationCell.HorizontalAlignment = 1; mainTable.AddCell(locationCell); var sumCell = new PdfPCell(new Phrase("Sum")); sumCell.BackgroundColor = BaseColor.LIGHT_GRAY; sumCell.Colspan = 1; sumCell.HorizontalAlignment = 1; mainTable.AddCell(sumCell); } else { var productName = new PdfPCell(new Phrase(salesReport.Product.ProductName)); productName.Colspan = 2; productName.HorizontalAlignment = 1; mainTable.AddCell(productName); var quantity = new PdfPCell(new Phrase(salesReport.Quantity + " " + salesReport.Product.Measure.MeasureName)); quantity.Colspan = 2; quantity.HorizontalAlignment = 1; mainTable.AddCell(quantity); var unitPrice = new PdfPCell(new Phrase(salesReport.UnitPrice.ToString())); unitPrice.Colspan = 2; unitPrice.HorizontalAlignment = 1; mainTable.AddCell(unitPrice); var supermarketName = new PdfPCell(new Phrase(salesReport.Location.ToString())); supermarketName.Colspan = 3; supermarketName.HorizontalAlignment = 1; mainTable.AddCell(supermarketName); var sum = new PdfPCell(new Phrase((salesReport.Quantity * salesReport.UnitPrice).ToString())); sum.Colspan = 1; sum.HorizontalAlignment = 1; mainTable.AddCell(sum); currentTotalSum += salesReport.Quantity * salesReport.UnitPrice; } } var totalSumCellF = new PdfPCell(new Phrase("Total Sum : " + currentTotalSum.ToString())); totalSumCellF.BackgroundColor = BaseColor.GREEN; totalSumCellF.Colspan = 10; totalSumCellF.HorizontalAlignment = iTextSharp.text.Element.ALIGN_RIGHT; mainTable.AddCell(totalSumCellF); currentTotalSum = 0; mainTable.WidthPercentage = 100; document.Add(mainTable); document.Close(); }
private static void GenerateJSONReports() { using (ProductsDBContext sqlContext = new ProductsDBContext()) { string cString = "mongodb://localhost/"; MongoClient client = new MongoClient(cString); MongoServer server = client.GetServer(); var productsDB = server.GetDatabase("ProductsDB"); MongoCollection<JSONReport> reportsMongo = productsDB.GetCollection<JSONReport>("JSONReports"); var products = sqlContext.Products.ToList(); foreach (var prod in products) { JSONReport report = new JSONReport() { ProductId = prod.ProductId, ProductName = prod.ProductName, VendorName = prod.Vendor.VendorName, }; foreach (var salesReport in sqlContext.SalesReports) { if (salesReport.ProductId == prod.ProductId) { report.TotalQuantitySold += salesReport.Quantity; report.TotalIncome += report.TotalQuantitySold * salesReport.UnitPrice; } } reportsMongo.Insert(report); } } }