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;
}