private static void GenerateXmlReport(ArtGalleryDbContext db, string xmlReportFilePath) { var xmlDocument = new XmlDocument(); XmlElement rootElement = xmlDocument.CreateElement("sales"); string nativeSqlQuery = "SELECT a.FirstName + ' ' + a.LastName AS [FullName], aw.DateSold, SUM(aw.Value) AS [Price]" + "FROM ArtWorks aw " + "INNER JOIN Artists a on aw.ArtistId=a.Id AND aw.DateSold IS NOT NULL " + "GROUP BY a.FirstName, a.LastName, aw.DateSold"; DbRawSqlQuery<ArtistSaleReport> artistSaleReports = db.Database.SqlQuery<ArtistSaleReport>(nativeSqlQuery); foreach (var artistSaleReport in artistSaleReports) { XmlElement saleElement = xmlDocument.CreateElement("sale"); saleElement.SetAttribute("artist", artistSaleReport.FullName); XmlElement summaryElement = xmlDocument.CreateElement("summary"); summaryElement.SetAttribute("date", artistSaleReport.DateSold.ToString("dd-MMM-yyyy", CultureInfo.GetCultureInfo("en-US"))); summaryElement.SetAttribute("total-sum", artistSaleReport.Price.ToString()); saleElement.AppendChild(summaryElement); rootElement.AppendChild(saleElement); } xmlDocument.AppendChild(rootElement); XmlWriter xmlReportFile = GenerateXmlFile(xmlReportFilePath); xmlReportFile.WriteStartDocument(); using (xmlReportFile) { xmlDocument.WriteTo(xmlReportFile); } }
public void WriteData() { this.ChangeState(new Notification { Message = "Generating JSON reports and Importing them into MySql..." }); using (var db = new ArtGalleryDbContext()) { var artWorkReports = db.ArtWorks.Select(a => new ArtWorkJsonReport { Id = a.Id, Name = a.Title, Type = a.Type.ToString(), Status = a.Status.ToString(), Income = a.DateSold.HasValue ? a.Value : 0 }) .AsEnumerable(); var counter = 0; var mySql = new ArtGalleryMySqlDbContext(); foreach (var report in artWorkReports) { this.SaveAndWriteReport(report, report.Id); mySql.Reports.Add(report); mySql.SaveChanges(); counter += 1; if (counter == 20) { mySql.Dispose(); mySql = new ArtGalleryMySqlDbContext(); } } } this.ChangeState(new Notification { Message = "Done." }); }
public void Run() { /// string PathToReportsArchive = @"../../Data/SalesReports.zip"; /// string PathToReports = @"../../Data/SalesReports"; var data = new ArtGalleryDbContext(); var dataImporter = new MongoDb(); var consoleWriter = new TextWriter(Console.Out); var sqlDbDataImporter = new MsSqlDbDataImporter(dataImporter, data); var archiver = new ArchiveHandler(); sqlDbDataImporter.Subscribe(consoleWriter); sqlDbDataImporter.ImportData(); archiver.Subscribe(consoleWriter); /// archiver.UnzipToFolder(PathToReportsArchive, PathToReports); var transfer = new TransferDataFromExcelToDB(data); transfer.GetFile(); }
public void ImportData() { this.ChangeState(new Notification { Message = "Importing XML data into SQL Server.." }); using (var db = new ArtGalleryDbContext()) { this .dataLoader .GetArtWorksInformation() .Select(x => (ArtWorkDescription)new ArtWorkDescription().InjectFrom(x)) .ToList() .ForEach(x => db.ArtWorksDescriptions.Add(x)); db.SaveChanges(); } this.ChangeState(new Notification { Message = "Done." }); }
public void Run() { var db = new ArtGalleryDbContext(); GenerateXmlReport(db, XmlReportFilePath); }
public TransferDataFromExcelToDB(ArtGalleryDbContext data) { this.data = data; }
public void Run() { // Needs refactoring Console.WriteLine("Generating PDF reports..."); var db = new ArtGalleryDbContext(); List<int?> yearsOfSales = db .ArtWorks .Select(a => a.DateSold) .Distinct() .AsEnumerable() .Select<DateTime?, int?>(d => { if (d.HasValue) { return d.GetValueOrDefault().Year; } return null; }) .Distinct() .ToList(); var doc = new Document(iTextSharp.text.PageSize.LETTER, 10, 10, 42, 35); PdfWriter.GetInstance(doc, new FileStream(PathToPdfReports + "/Yearly-Artworks-Sales-Report.pdf", FileMode.Create)); doc.Open(); PdfPTable titleHeader = new PdfPTable(1); PdfPCell cellHeader = new PdfPCell(new Phrase("Aggregated Yearly Artworks Sales Report")); cellHeader.HorizontalAlignment = 1; titleHeader.AddCell(cellHeader); doc.Add(titleHeader); decimal grandTotalSum = 0; decimal totalWorthOfSales = 0; decimal totalWorthLeft = 0; foreach (var date in yearsOfSales) { string dateGroup = string.Empty; if (!date.HasValue) { dateGroup = "N/A"; // Not available, i.e not sold yet } else { dateGroup = date.GetValueOrDefault().ToString(); } PdfPTable tableHeader = new PdfPTable(5); { PdfPCell cellHeaderDate = new PdfPCell(new Phrase("Year: " + dateGroup)); cellHeaderDate.Colspan = 5; cellHeaderDate.BackgroundColor = new BaseColor(217, 217, 217); PdfPCell artist = new PdfPCell(new Phrase("Artist")); artist.BackgroundColor = new BaseColor(217, 217, 217); PdfPCell title = new PdfPCell(new Phrase("Title")); title.BackgroundColor = new BaseColor(217, 217, 217); PdfPCell type = new PdfPCell(new Phrase("Artwork type")); type.BackgroundColor = new BaseColor(217, 217, 217); PdfPCell status = new PdfPCell(new Phrase("Status")); status.BackgroundColor = new BaseColor(217, 217, 217); PdfPCell price = new PdfPCell(new Phrase("Price")); price.BackgroundColor = new BaseColor(217, 217, 217); tableHeader.AddCell(cellHeaderDate); tableHeader.AddCell(artist); tableHeader.AddCell(title); tableHeader.AddCell(type); tableHeader.AddCell(status); tableHeader.AddCell(price); } decimal totalSum = 0; PdfPTable tableBody = new PdfPTable(5); { var artworks = db .ArtWorks .AsEnumerable() .Where(a => { if (date == null) { return !a.DateSold.HasValue; } return a.DateSold.GetValueOrDefault().Year == date; }) .ToList(); tableBody.DefaultCell.HorizontalAlignment = 1; foreach (var artwork in artworks) { tableBody.AddCell(db.ArtWorks.Find(artwork.ArtistId).Artist.FirstName + " " + db.ArtWorks.Find(artwork.ArtistId).Artist.LastName); tableBody.AddCell(artwork.Title); tableBody.AddCell(artwork.Type.ToString()); tableBody.AddCell(artwork.Status.ToString()); tableBody.AddCell(artwork.Value.ToString()); totalSum += artwork.Value; } grandTotalSum += totalSum; if (date.HasValue) { totalWorthOfSales += totalSum; } } PdfPTable tableFooter = new PdfPTable(5); { PdfPCell totalSumTextCell = new PdfPCell(new Phrase("Total sum for " + dateGroup + ": ")); totalSumTextCell.Colspan = 4; totalSumTextCell.HorizontalAlignment = 2; tableFooter.AddCell(totalSumTextCell); PdfPCell totalSumCell = new PdfPCell(new Phrase(totalSum.ToString())); totalSumCell.HorizontalAlignment = 2; tableFooter.AddCell(totalSumCell); } doc.Add(tableHeader); doc.Add(tableBody); doc.Add(tableFooter); } totalWorthLeft = grandTotalSum - totalWorthOfSales; var footer = new PdfPTable(5); var grandSumText = new PdfPCell(new Phrase("Grand total: ")); grandSumText.Colspan = 4; grandSumText.HorizontalAlignment = 2; var grandSum = new PdfPCell(new Phrase(grandTotalSum.ToString())); grandSum.HorizontalAlignment = 2; var totalSoldText = new PdfPCell(new Phrase("Total worth of sales: ")); totalSoldText.Colspan = 4; totalSoldText.HorizontalAlignment = 2; var totalSoldValue = new PdfPCell(new Phrase(totalWorthOfSales.ToString())); totalSoldValue.HorizontalAlignment = 2; var totalRemainingText = new PdfPCell(new Phrase("Total worth remaining: ")); totalRemainingText.Colspan = 4; totalRemainingText.HorizontalAlignment = 2; var totalRemainingValue = new PdfPCell(new Phrase(totalWorthLeft.ToString())); totalRemainingValue.HorizontalAlignment = 2; footer.AddCell(totalSoldText); footer.AddCell(totalSoldValue); footer.AddCell(totalRemainingText); footer.AddCell(totalRemainingValue); footer.AddCell(grandSumText); footer.AddCell(grandSum); doc.Add(footer); doc.Close(); Console.WriteLine("Done."); }
public MsSqlDbDataImporter(IDataProvider dataProvider, ArtGalleryDbContext data) { this.dataProvider = dataProvider; this.subscribers = new List<IObserver>(); this.data = data; }