private static List<ProductReport> getAllReports(SupermarketContext db) { var reports = new List<ProductReport>(); foreach (var product in db.Products) { int totalQuantitySold = 0; var foundProduct = db.SalesReports.FirstOrDefault(x => x.ProductID == product.ProductID); if(foundProduct!=null) { totalQuantitySold = db.SalesReports.Where(x => x.ProductID == product.ProductID) .Sum(x => x.Quantity); } ProductReport report = new ProductReport() { productId = product.ProductID, productName = product.ProductName, vendorName = product.Vendor.VendorName, totalQuantitySold = totalQuantitySold, totalIncomes = product.BasePrice * totalQuantitySold }; reports.Add(report); } return reports; }
static void Main() { Database.SetInitializer(new MigrateDatabaseToLatestVersion<SupermarketContext, Configuration>()); var db = new SupermarketContext(); string conStr = "Server=localhost; Port=3306; Database=supermarketdb; Uid=root; Pwd=6okolad; pooling=true"; SupermarketModel mySqlDB = new SupermarketModel(conStr); db.Database.Delete(); TransferData(mySqlDB, db); TransferDataConnTable(mySqlDB, db); db.Database.Connection.Close(); //Unpack data from zip file ZipDataReader.ExtractData(@"../../../Sample-Sales-Reports.zip", @"../../../Extracted Files"); ExcelDataReader.TransferDataFromExcelToDB(db, "../../../Extracted Files"); Directory.Delete(@"../../../Extracted Files", true); //Create Pdf File CreatePdf.AddDataToPdf(db); //Create XML File CreateXMLDocument.AddDataToXML(db); //Save Json files in the file system CreateJsonDocument.saveJsonFiles(@"../../../Product-Reports", db); CreateJsonDocument.saveReportsToMongoDB(db); }
// Copy data from auto-generates MySql classes to code first made classes for MS SQL Database public static void TransferData(SupermarketModel mySQL, SupermarketContext msSQL) { foreach (var measure in mySQL.Measures) { var newMeasure = new SupermarketDB.Models.Measure { MeasureID = measure.MeasureID, MeasureName = measure.MeasureName }; msSQL.Measures.Add(newMeasure); msSQL.SaveChanges(); } foreach (var vendor in mySQL.Vendors) { var newVendor = new SupermarketDB.Models.Vendor { VendorID = vendor.VendorID, VendorName = vendor.VendorName }; msSQL.Vendors.Add(newVendor); msSQL.SaveChanges(); } }
public static void saveReportsToMongoDB(SupermarketContext db) { var mongoClient = new MongoClient("mongodb://*****:*****@dharma.mongohq.com:10068/Supermarket"); var mongoServer = mongoClient.GetServer(); var supermarketDB = mongoServer.GetDatabase("Supermarket"); var reportsCollection = supermarketDB.GetCollection("Reports"); var reports = getAllReports(db); foreach (ProductReport report in reports) { reportsCollection.Insert(report); } }
public static void saveJsonFiles(string path, SupermarketContext db) { var directory = Directory.CreateDirectory(path); var reports = getAllReports(db); foreach (ProductReport report in reports) { string currentPath = path + "/" + report.productId.ToString() + ".json"; using (StreamWriter writer = new StreamWriter(currentPath)) { writer.Write(JsonConvert.SerializeObject(report)); } } }
public static void TransferDataConnTable(SupermarketModel mySQL, SupermarketContext msSQL) { foreach (var product in mySQL.Products) { var newProduct = new SupermarketDB.Models.Product { ProductID = product.ProductID, VendorID = product.VendorID, MeasureID = product.MeasureID, ProductName = product.ProductName, BasePrice = product.BasePrice }; msSQL.Products.Add(newProduct); msSQL.SaveChanges(); } }
public static void AddDataToXML(SupermarketContext db) { XElement saleXML = new XElement("sales"); foreach (var vendor in db.Vendors) { XElement sale = new XElement("sale"); sale.SetAttributeValue("vendor", vendor.VendorName); saleXML.Add(sale); foreach (var product in vendor.Products) { foreach (var report in product.Reports) { XElement summary = new XElement("summary"); summary.Add(new XAttribute("date", report.ReportDate)); summary.Add(new XAttribute("total-sum", report.Sum)); sale.Add(summary); } } } saleXML.Save("../../../Sales-by-Vendors-report.xml"); }
public static void AddDataToPdf(SupermarketContext db) { Document doc = new Document(); PdfAWriter.GetInstance(doc, new FileStream(@"../../.." + "/Aggregated-Sales-Report.pdf", FileMode.Create)); doc.Open(); PdfPTable table = new PdfPTable(5); Font fontBold = new Font(Font.FontFamily.HELVETICA, 14f); fontBold.SetStyle("bold"); Font fontNormal = new Font(Font.FontFamily.HELVETICA, 12f); PdfPCell header = new PdfPCell(new Phrase("Aggregated Sales Report", fontBold)); header.Colspan = 5; header.HorizontalAlignment = 1; table.AddCell(header); using (db) { var dates = (from d in db.SalesReports select d.ReportDate).Distinct(); decimal grandTotal = 0; foreach (var date in dates) { PdfPCell cellDate = new PdfPCell(new Phrase("Date : " + date.ToShortDateString())); cellDate.Colspan = 5; table.AddCell(cellDate); PdfPCell cellProduct = new PdfPCell(new Phrase("Products", fontBold)); table.AddCell(cellProduct); PdfPCell cellQuantity = new PdfPCell(new Phrase("Quantity", fontBold)); table.AddCell(cellQuantity); PdfPCell cellUnitPrice = new PdfPCell(new Phrase("Unit Price", fontBold)); table.AddCell(cellUnitPrice); PdfPCell cellLocation = new PdfPCell(new Phrase("Location", fontBold)); table.AddCell(cellLocation); PdfPCell cellSum = new PdfPCell(new Phrase("Sum", fontBold)); table.AddCell(cellSum); var sales = (from s in db.SalesReports where s.ReportDate == date select s); decimal totalSum = 0; foreach (var sale in sales) { if (sale.Product.ProductName != null) { table.AddCell(sale.Product.ProductName); table.AddCell(sale.Quantity.ToString() + " " + db.Measures .Where(x => x.MeasureID == sale.Product.MeasureID) .Select(x => x.MeasureName).First().ToString()); table.AddCell(sale.UnitPrice.ToString()); table.AddCell(sale.SupermarketName); table.AddCell(sale.Sum.ToString()); totalSum += sale.Sum; } } string text = string.Format("Total sum for {0} :", date.ToShortDateString()); PdfPCell cellTotal = new PdfPCell(new Phrase(text, fontNormal)); cellTotal.Colspan = 4; cellTotal.HorizontalAlignment = 2; table.AddCell(cellTotal); table.AddCell(totalSum.ToString()); grandTotal += totalSum; } string grand = string.Format("Grand Total: "); PdfPCell cellGrand = new PdfPCell(new Phrase(grand)); cellGrand.Colspan = 4; cellGrand.HorizontalAlignment = 2; table.AddCell(cellGrand); table.AddCell(grandTotal.ToString()); doc.Add(table); } doc.Close(); }
public static void TransferDataFromExcelToDB(SupermarketContext DBconn, string directory) { DirectoryInfo dir = new DirectoryInfo(directory); var directories = dir.GetDirectories(); foreach (var d in directories) { var date = DateTime.Parse(d.Name); var files = d.GetFiles(); foreach (var f in files) { string excelFile = f.FullName; string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;" + @"Data Source=" + excelFile + ";" + @"Extended Properties='Excel 12.0 Xml;HDR=YES'"; OleDbConnection conn = new OleDbConnection(connectionString); conn.Open(); DataSet newDataSet = new DataSet(); using (conn) { OleDbCommand cmd = new OleDbCommand("SELECT * from [Sales$]", conn); OleDbDataAdapter newDataAdapter = new OleDbDataAdapter(cmd); newDataAdapter.Fill(newDataSet, "Sales"); } DataTable table = newDataSet.Tables[0]; // Get supermarket name DataRow row = table.Rows[0]; DataColumn col = table.Columns[0]; string line = row[col].ToString(); int spaceIndex = line.IndexOf(' '); string supermarketName = line.Substring(spaceIndex + 1); decimal sum = decimal.Parse(table.Rows[table.Rows.Count - 1][table.Columns.Count - 1].ToString()); List<decimal[]> allData = new List<decimal[]>(); for (int r = 2; r < table.Rows.Count - 1; r++) { DataRow dataRow = table.Rows[r]; var productData = new decimal[4]; decimal result = 0; for (int c = 0; c < 4; c++) { DataColumn dataCol = table.Columns[c]; decimal.TryParse(dataRow[dataCol].ToString(), out result); productData[c] = result; } if (result != 0) { allData.Add(productData); } } foreach (var report in allData) { var productId = (int)report[0]; var quantity = (int)report[1]; var unitPrice = report[2]; var reportSum = report[3]; var saleReport = new SaleReport { ProductID = productId, Quantity = quantity, UnitPrice = unitPrice, Sum = reportSum, SupermarketName = supermarketName, ReportDate = date }; DBconn.SalesReports.Add(saleReport); DBconn.SaveChanges(); } } } }