public static void CreateXml(string filePath) { using (SupermarketDBContext sqldb = new SupermarketDBContext()) { string format = "dd-MMM-yyyy"; XElement sales = new XElement("sales"); foreach (var vendor in sqldb.Vendors.ToList().Where( x=> x.Products.Any(y => y.SalesReports.Count > 0))) { XElement elSale = new XElement("sale"); elSale.SetAttributeValue("vendor", vendor.VendorName.ToString()); foreach (var product in vendor.Products.ToList()) { foreach (var report in product.SalesReports.ToList()) { XElement summary = new XElement("summary"); summary.SetAttributeValue("date", report.ReportDate.ToString(format)); summary.SetAttributeValue("total-sum", report.Sum.ToString()); elSale.Add(summary); } } sales.Add(elSale); } sales.Save(filePath); } }
public static void SeedDatabase() { using (SupermarketDbModel mysql = new SupermarketDbModel()) using (SupermarketDBContext sql = new SupermarketDBContext()) { //sql.Database.Delete(); foreach (var measure in mysql.Measures) { if (!sql.Measures.Any(x=> x.MeasureId == measure.MeasureId)) { SupermarketEF.Model.Measure measureToAdd = new SupermarketEF.Model.Measure { MeasureId = measure.MeasureId, MeasureName = measure.MeasureName }; sql.Measures.Add(measureToAdd); } } sql.SaveChanges(); foreach (var vendor in mysql.Vendors) { if (!sql.Vendors.Any(x => x.VendorId == vendor.VendorId)) { SupermarketEF.Model.Vendor vendorToAdd = new SupermarketEF.Model.Vendor { //Products = new HashSet<SupermarketEF.Model.Product>(), VendorId = vendor.VendorId, VendorName = vendor.VendorName }; sql.Vendors.Add(vendorToAdd); } sql.SaveChanges(); } foreach (var product in mysql.Products) { if (!sql.Products.Any(x => x.ProductId == product.ProductId)) { SupermarketEF.Model.Product productToAdd = new SupermarketEF.Model.Product { VendorId = product.VendorId, ProductName = product.ProductName, MeasureId = product.MeasureId, BasePrice = product.BasePrice }; sql.Products.Add(productToAdd); } sql.SaveChanges(); } } }
public static void ParseExcelFile(SupermarketDBContext sqlDb, string folderDate, string file) { string connectStr = @"Provider=Microsoft.ACE.OLEDB.12.0; " + @"Data Source=" + file + "; " + @"Extended Properties='Excel 12.0 Xml; HDR=YES'"; OleDbConnection dbCon = new OleDbConnection(connectStr); string sqlQuery = "select * from [Sales$]"; OleDbDataAdapter myDataAdapter = new OleDbDataAdapter(sqlQuery, dbCon); DataSet currentData = new DataSet(); myDataAdapter.Fill(currentData, "Sales"); DataRowCollection excelRows = currentData.Tables["Sales"].Rows; string location = string.Empty; DateTime saleDate = DateTime.Parse(folderDate); location = excelRows[0][0].ToString(); for (int row = 2; row < excelRows.Count - 1; row++) { int productId = 0; int quantity = 0; decimal unitPrice = 0; decimal sum = 0; int.TryParse(excelRows[row][0].ToString(), out productId); int.TryParse(excelRows[row][1].ToString(), out quantity); decimal.TryParse(excelRows[row][2].ToString(), out unitPrice); decimal.TryParse(excelRows[row][3].ToString(), out sum); if (productId != 0) { SalesReport currentReport = new SalesReport { Location = location, ProductId = productId, Quantity = quantity, Sum = sum, UnitPrice = unitPrice, ReportDate = saleDate }; sqlDb.SalesReports.Add(currentReport); sqlDb.SaveChanges(); } } }
public static void ParseExcelDirectory(SupermarketDBContext sqldb, string directoryPath) { DirectoryInfo dir = new DirectoryInfo(directoryPath); foreach (var subDirectory in dir.GetDirectories()) { FileInfo[] files = subDirectory.GetFiles(); //DateTime folderDate = DateTime.Parse(subDirectory.Name); foreach (var item in files) { Console.WriteLine(item.FullName); ParseExcelFile(sqldb, subDirectory.Name, item.FullName); sqldb.SaveChanges(); Console.WriteLine(); } } }
public static void TestReportReader() { Thread.CurrentThread.CurrentCulture = CultureInfo.InvariantCulture; SupermarketDBContext sqldb = new SupermarketDBContext(); ExtractZipFIle(@"../Sample-Sales-Reports.zip", @"../Reports"); ParseExcelDirectory(sqldb, @"../Reports/"); }
public static void CreatePdf(string path) { Document myDoc = new Document(PageSize.A4.Rotate()); iTextSharp.text.pdf.PdfWriter.GetInstance(myDoc, new FileStream(path, FileMode.Create)); myDoc.Open(); PdfPTable table = new PdfPTable(5); table.TotalWidth = 800f; table.LockedWidth = true; Font font = new Font(Font.FontFamily.COURIER, 20f, 1); font.SetStyle("bold"); PdfPCell cell = new PdfPCell(new Phrase("Aggregated Sales Report", font)); cell.Colspan = 5; cell.Indent = 50; cell.HorizontalAlignment = Element.ALIGN_CENTER; cell.Padding = 5; cell.BackgroundColor = new BaseColor(229, 228, 226); table.AddCell(cell); using (SupermarketDBContext sqlContext = new SupermarketDBContext()) { //MAGIC QUERRY //var salesRep = ( from sr in sqlContext.SalesReports // join p in sqlContext.Products // on sr.ProductId equals p.ProductId // select new { sr, p.ProductName }).Distinct().GroupBy(x => x.sr.ReportDate); var salesReports = sqlContext.SalesReports.GroupBy(x => x.ReportDate).ToList(); decimal grandTotal = 0m; foreach (var reports in salesReports) { string format = "dd-MMM-yyyy"; var date = reports.First().ReportDate.ToString(format); PdfPCell dateCell = new PdfPCell( new Phrase(date, font)); dateCell.Colspan = 5; dateCell.Indent = 50; dateCell.HorizontalAlignment = Element.ALIGN_CENTER; dateCell.Padding = 5; dateCell.BackgroundColor = new BaseColor(229, 228, 226); table.AddCell(dateCell); foreach (var report in reports) { string productId = report.Product.ProductName.ToString(); string quantity = report.Quantity.ToString(); string unitPrice = report.UnitPrice.ToString(); string location = report.Location.ToString(); string sum = report.Sum.ToString(); table.AddCell(productId); table.AddCell(quantity); table.AddCell(unitPrice); table.AddCell(location); table.AddCell(sum); } PdfPCell footrCell = new PdfPCell(new Phrase("Total sum for " + date)); footrCell.Colspan = 4; footrCell.Indent = 50; footrCell.HorizontalAlignment = Element.ALIGN_RIGHT; footrCell.Padding = 5; table.AddCell(footrCell); //PdfPCell totalCell = new PdfPCell(); decimal reportSum = reports.Sum(x => x.Sum); grandTotal += reportSum; table.AddCell(reportSum.ToString()); } PdfPCell grandFootrCell = new PdfPCell(new Phrase("Grand total:")); grandFootrCell.Colspan = 4; grandFootrCell.Indent = 50; grandFootrCell.HorizontalAlignment = Element.ALIGN_RIGHT; grandFootrCell.Padding = 5; table.AddCell(grandFootrCell); table.AddCell(new PdfPCell(new Phrase(grandTotal.ToString(),font))); myDoc.Add(table); } myDoc.Close(); }