private static void GenerateJsonFlightsReportsAndLoadToMySql(IAirportsDataSqlServer airportsData)
{
var jsonExporter = new JsonExporter();
jsonExporter.GenerateReportsForGivenPeriod(airportsData, JsonReportsDestionationPath);
var mySqlImporter = new JsonDataImporter();
mySqlImporter.ImportAirlineReportsFromDirectory(JsonReportsDestionationPath);
}
public void GenerateReportsForGivenPeriod(
IAirportsDataSqlServer context,
string reportsFolderPath,
DateTime? startDate = null,
DateTime? endDate = null)
{
if (!Directory.Exists(reportsFolderPath))
{
Directory.CreateDirectory(reportsFolderPath);
}
startDate = startDate ?? new DateTime(1900, 1, 1);
endDate = endDate ?? DateTime.Now;
var aggregatedAirlineReports = context.Airlines.GetAll()
.Include(a => a.Flights)
.Select(a =>
new
{
a.AirlineId,
a.Name,
Flights = a.Flights.Where(f => startDate < f.FlightDate && f.FlightDate < endDate)
})
.Select(a =>
new
{
AirlineId = a.AirlineId,
AirlineName = a.Name,
TotalFlightsCount = a.Flights.Count(),
AverageFlightsCount = a.Flights.Average(f => f.DurationHours),
TotalFlightsDuration = a.Flights.Sum(f => f.DurationHours),
StartDate = startDate,
EndDate = endDate
})
.ToList();
foreach (var report in aggregatedAirlineReports)
{
string json = JsonConvert.SerializeObject(report, Formatting.Indented);
using (var writer = new StreamWriter(reportsFolderPath + report.AirlineId + FileExtension))
{
writer.Write(json);
}
}
}
public static void GenerateAirlinesReport(string filePath, IAirportsDataSqlServer database)
{
if (!Directory.Exists(filePath))
{
Directory.CreateDirectory(filePath);
}
var airlines = database.Airlines.GetAll()
.Include("Flights")
.Select(airline => new
{
Name = airline.Name,
Flights = airline.Flights.Select(flight =>
new
{
FlightCode = flight.FlightCode,
DepartureAirport = flight.DepartureAirport,
ArrivalAirport = flight.ArrivalAirport,
DurationHours = flight.DurationHours
})
})
.ToList();
XDocument xdoc = new XDocument(new XElement("airlines",
from airline in airlines
select new XElement("airline",
new XAttribute("name", airline.Name),
new XElement("flights",
from flight in airline.Flights
select new XElement("flight",
new XAttribute("code", flight.FlightCode),
new XElement("departure-airport", flight.DepartureAirport.Name),
new XElement("arival-airport", flight.ArrivalAirport.Name),
new XElement("duration", flight.DurationHours + " hours"))))));
DateTime now = DateTime.Now;
string uniqueComponent = string.Format("{0}.{1}.{2}-{3}.{4}.{5}-{6}",
now.Day, now.Month, now.Year, now.Hour, now.Minute, now.Second, now.Millisecond);
string fileName = string.Format(@"{0}airlines-{1}.xml", filePath, uniqueComponent);
xdoc.Save(fileName);
}
public void GenerateAggregatedAirlineReports(string filePath, string fileName, IAirportsDataSqlServer airportsData)
{
fileName = AddUniqueFilenameSuffix(fileName);
if (!Directory.Exists(filePath))
{
Directory.CreateDirectory(filePath);
}
var document = new Document(PageSize.A4, 50, 50, 25, 25);
var output = new FileStream(filePath + fileName, FileMode.Create, FileAccess.Write);
var writer = PdfWriter.GetInstance(document, output);
PdfPTable table = this.CreateAirlineReportsTable();
this.AddAirlineReportsTableHeader(table);
this.AddAirlineReportsTableColumns(table);
this.FillAirlineReportsTableData(table, airportsData);
document.Open();
document.Add(table);
document.Close();
}
private static void ExtractZipAndImportDataFromExcelAndMongoDb(
IAirportsDataSqlServer airportsData,
IAirportsDataMongoDb mongoData)
{
Console.WriteLine("Unpacking zip archive...");
var zipExtractor = new ZipExtractor();
zipExtractor.Extract(SampleFlightsArchivedFilePath, SampleFlightsUnpackedDestinationPath);
Console.WriteLine("Importing flights data from directory with xls/xlsx files...");
var excelDataImporter = new ExcelDataImporter();
ICollection<Flight> importedFlightsFromExcel = excelDataImporter
.ImportFlightsDataFromDirectory(SampleFlightsUnpackedDestinationPath);
Console.WriteLine("Loading imported flights from Excel to SQL Server...");
foreach (var flight in importedFlightsFromExcel)
{
airportsData.Flights.Add(flight);
}
airportsData.SaveChanges();
var countriesFromMongo = mongoData.Countries.ToList();
foreach (var country in countriesFromMongo)
{
airportsData.Countries.Add(country);
}
var citiesFromMongo = mongoData.Cities.ToList();
foreach (var city in citiesFromMongo)
{
airportsData.Cities.Add(city);
}
airportsData.SaveChanges();
}
private void FillAirlineReportsTableData(PdfPTable table, IAirportsDataSqlServer airportsData)
{
var aggregatedAirlineReports = airportsData.Airlines.GetAll()
.Include("Flights")
.Select(a =>
new
{
a.Name,
TotalFlightsCount = a.Flights.Count,
AverageFlightDuration = a.Flights.Average(f => f.DurationHours),
TotalFlightDuration = a.Flights.Sum(f => f.DurationHours)
})
.ToList();
foreach (var airlineReport in aggregatedAirlineReports)
{
table.AddCell(airlineReport.Name);
table.AddCell(airlineReport.TotalFlightsCount.ToString());
table.AddCell(airlineReport.AverageFlightDuration.ToString() + " hours");
table.AddCell(airlineReport.TotalFlightDuration.ToString());
}
}
private static void GenerateXmlFlightsReport(IAirportsDataSqlServer airportsData)
{
Console.WriteLine("Exporting XML airlines report...");
XmlFileExporter.GenerateAirlinesReport(XmlReportsFolderPath, airportsData);
}
private static void GeneratePdfFlightsReport(IAirportsDataSqlServer airportsData)
{
Console.WriteLine("Exporting PDF flight report...");
var pdfExporter = new PdfFileExporter();
pdfExporter.GenerateAggregatedAirlineReports(PdfReportsFolderPath, PdfReportsFileName, airportsData);
}
private static void ImportFlightsDataFromXmlAndLoadToMongoDb(IAirportsDataSqlServer airportsData, IAirportsDataMongoDb mongoData)
{
Console.WriteLine("Importing flights data from xml file...");
XmlDataImporter xmlDataImporter = new XmlDataImporter();
ICollection<Flight> importedFlightsFromXml =
xmlDataImporter.ImportFlightsDataFromFile(SampleFlightsDataXmlFilePath);
Console.WriteLine("Loading imported flights to SQL Server and MongoDb...");
foreach (var flight in importedFlightsFromXml)
{
airportsData.Flights.Add(flight);
mongoData.Save(flight);
}
airportsData.SaveChanges();
}
