public static string ImportAgencies(WeddingsPlannerDbContext context, string jsonString)
{
var agenciesDtos = JsonConvert.DeserializeObject <AgencyDto[]>(jsonString);
var sb = new StringBuilder();
var validAgencies = new List <Agency>();
foreach (var agencyDto in agenciesDtos)
{
if (!IsValid(agencyDto))
{
sb.AppendLine(FailureMessage);
continue;
}
var agency = new Agency()
{
Name = agencyDto.Name,
EmployeesCount = agencyDto.EmployeesCount,
Town = agencyDto.Town
};
validAgencies.Add(agency);
sb.AppendLine(String.Format(SuccessMessage, agencyDto.Name));
}
context.Agencies.AddRange(validAgencies);
context.SaveChanges();
var result = sb.ToString();
return(result);
}
private static void ResetDatabase(WeddingsPlannerDbContext context, bool shouldDeleteDatabase = false)
{
if (shouldDeleteDatabase)
{
context.Database.EnsureDeleted();
context.Database.EnsureCreated();
}
context.Database.EnsureCreated();
var disableIntegrityChecksQuery = "EXEC sp_MSforeachtable @command1='ALTER TABLE ? NOCHECK CONSTRAINT ALL'";
context.Database.ExecuteSqlCommand(disableIntegrityChecksQuery);
var deleteRowsQuery = "EXEC sp_MSforeachtable @command1='DELETE FROM ?'";
context.Database.ExecuteSqlCommand(deleteRowsQuery);
var enableIntegrityChecksQuery = "EXEC sp_MSforeachtable @command1='ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL'";
context.Database.ExecuteSqlCommand(enableIntegrityChecksQuery);
var reseedQuery = "EXEC sp_MSforeachtable @command1='DBCC CHECKIDENT(''?'', RESEED, 0)'";
try
{
context.Database.ExecuteSqlCommand(reseedQuery);
}
catch (SqlException) // OrderItems table has no identity column, which isn't a problem
{
}
}
private static void ImportEntities(WeddingsPlannerDbContext context, string baseDir = @"Datasets\")
{
const string exportDir = "./ImportResults/";
var agencies = Deserializer.ImportAgencies(context, File.ReadAllText(baseDir + "agencies.json"));
PrintAndExportEntityToFile(agencies, exportDir + "agencies.txt");
var people = Deserializer.ImportPeople(context, File.ReadAllText(baseDir + "people.json"));
PrintAndExportEntityToFile(people, exportDir + "people.txt");
var weddings = Deserializer.ImportWeddingsAndInvitations(context, File.ReadAllText(baseDir + "weddings.json"));
PrintAndExportEntityToFile(weddings, exportDir + "weddings.txt");
var venues = Deserializer.ImportVenues(context, File.ReadAllText(baseDir + "venues.xml"));
PrintAndExportEntityToFile(venues, exportDir + "venues.txt");
var presents = Deserializer.ImportPresents(context, File.ReadAllText(baseDir + "presents.xml"));
PrintAndExportEntityToFile(presents, exportDir + "presents.txt");
}
public static string ExportGuestsLists(WeddingsPlannerDbContext context)
{
var weddinginfo = context.Weddings
.Include(w => w.Bride)
.Include(w => w.Bridegroom)
.Include(w => w.Agency)
.Include(w => w.Invitations)
.ThenInclude(i => i.Guest)
.Select(w => new ExportWeddingDto
{
Bride = w.Bride.FullName,
Bridegroom = w.Bridegroom.FullName,
Agency = new ExportAgencyDto()
{
Name = w.Agency.Name,
Town = w.Agency.Town
},
InvitedGuests = w.Invitations.Count,
BrideGuests = w.Invitations.Count(i => i.Family == Family.Bride),
BridegroomGuests = w.Invitations.Count(i => i.Family == Family.Bridegroom),
AttendingGuests = w.Invitations.Count(i => i.IsAttending),
Guests = w.Invitations.Where(inv => inv.IsAttending).Select(i => i.Guest.FullName).ToList()
})
.OrderByDescending(wedding => wedding.InvitedGuests)
.ThenBy(wedding => wedding.AttendingGuests).ToArray();
var json = JsonConvert.SerializeObject(weddinginfo, Formatting.Indented);
return(json);
}
public static void Main(string[] args)
{
var context = new WeddingsPlannerDbContext();
ResetDatabase(context, true);
//context.Database.EnsureCreated();
Console.WriteLine("Database Reset.");
Mapper.Initialize(cfg => cfg.AddProfile <MappingProfile>());
ImportEntities(context);
ExportEntities(context);
}
public static string ExportAgenciesByTown(WeddingsPlannerDbContext context)
{
var agenciesByTown = context.Agencies
.Where(agency => agency.Town.Length >= 6)
.GroupBy(agency => agency.Town, agency => agency, (town, agencies) => new
{
Town = town,
Agencies = agencies.Where(agency => agency.Weddings.Count >= 2)
})
.Select(gr => new TownDto()
{
Name = gr.Town,
AgenciesDtos = gr.Agencies.Select(agency => new AgencyInTownDto()
{
Name = agency.Name,
Profit = agency.Weddings
.Sum(wedding => wedding.Invitations
.Where(inv => (inv.Present as Cash) != null)
.Sum(inv => (inv.Present as Cash).Amount)
) * 0.2m,
Weddings = agency.Weddings.Select(wedding => new ExportWeddingInTownDto()
{
Cash = (wedding.Invitations
.Where(inv => (inv.Present as Cash) != null)
.Sum(inv => (decimal?)(inv.Present as Cash).Amount) ?? 0.0m),
Present = wedding.Invitations.Count(inv => (inv.Present as Gift) != null),
Bride = wedding.Bride.FirstName + " " + wedding.Bride.MiddleNameInitial + " " + wedding.Bride.LastName,
Bridegroom = wedding.Bridegroom.FirstName + " " + wedding.Bridegroom.MiddleNameInitial + " " + wedding.Bridegroom.LastName,
Guests = wedding.Invitations.Where(inv => inv.IsAttending).Select(guest => new ExportGuestsDto()
{
Family = guest.Family.ToString(),
Name = guest.Guest.FirstName + " " + guest.Guest.MiddleNameInitial + " " + guest.Guest.LastName,
}).ToList()
}).ToList()
}).ToList()
}).ToArray();
var sb = new StringBuilder();
var serializer = new XmlSerializer(typeof(TownDto[]), new XmlRootAttribute("towns"));
serializer.Serialize(new StringWriter(sb), agenciesByTown, new XmlSerializerNamespaces(new[] { XmlQualifiedName.Empty }));
var result = sb.ToString();
return(result);
}
public static string ImportPeople(WeddingsPlannerDbContext context, string jsonString)
{
var peopleDtos = JsonConvert.DeserializeObject <PersonDto[]>(jsonString);
var sb = new StringBuilder();
var validPeople = new List <Person>();
foreach (var personDto in peopleDtos)
{
if (!IsValid(personDto))
{
sb.AppendLine(FailureMessage);
continue;
}
bool isGenderValid = Enum.TryParse(personDto.Gender.ToString(), out Gender gender);
if (!isGenderValid)
{
gender = Gender.NotSpecified;
}
Person person = new Person()
{
FirstName = personDto.FirstName,
MiddleNameInitial = personDto.MiddleInitial,
LastName = personDto.LastName,
Gender = gender,
Birthdate = personDto.Birthdate,
Phone = personDto.Phone,
Email = personDto.Email
};
validPeople.Add(person);
sb.AppendLine(String.Format(SuccessMessage, person.FullName));
}
context.Persons.AddRange(validPeople);
context.SaveChanges();
var result = sb.ToString();
return(result);
}
public static string ExportOrderedAgencies(WeddingsPlannerDbContext context)
{
var agency = context.Agencies
.Select(a => new
{
name = a.Name,
count = a.EmployeesCount,
town = a.Town
})
.OrderByDescending(a => a.count)
.ThenBy(a => a.name)
.ToArray();
var json = JsonConvert.SerializeObject(agency, Formatting.Indented);
return(json);
}
public static string ExportVenuesInSofia(WeddingsPlannerDbContext context)
{
//Venues which are not in Sofia because there are no venues in Sofia with more than 3 weddings
var venuesInSofia = context.Venues.Where(v => v.Town != "Sofia" && v.Weddings.Count >= 3)
.Select(w => new ExportVenueDto()
{
Name = w.Name,
Capacity = w.Capacity,
WeddingsCount = w.Weddings.Count
})
.OrderBy(v => v.Capacity).ToArray();
var sb = new StringBuilder();
var serializer = new XmlSerializer(typeof(ExportVenueDto[]), new XmlRootAttribute("venues"));
serializer.Serialize(new StringWriter(sb), venuesInSofia, new XmlSerializerNamespaces(new[] { XmlQualifiedName.Empty }));
var result = sb.ToString();
return(result);
}
private static void ExportEntities(WeddingsPlannerDbContext context)
{
const string exportDir = "./ExportResults/";
string jsonOutput = Serializer.ExportOrderedAgencies(context);
Console.WriteLine(jsonOutput);
File.WriteAllText(exportDir + "Agencies-ordered.json", jsonOutput);
string jsonOutput2 = Serializer.ExportGuestsLists(context);
Console.WriteLine(jsonOutput2);
File.WriteAllText(exportDir + "guests.json", jsonOutput2);
string xmlOutput = Serializer.ExportVenuesInSofia(context);
Console.WriteLine(xmlOutput);
File.WriteAllText(exportDir + "sofia-venues.xml", xmlOutput);
string xmlOutput2 = Serializer.ExportAgenciesByTown(context);
Console.WriteLine(xmlOutput2);
File.WriteAllText(exportDir + "agencies-by-town.xml", xmlOutput2);
}
public static string ImportPresents(WeddingsPlannerDbContext context, string xmlString)
{
var serializer = new XmlSerializer(typeof(PresentDto[]), new XmlRootAttribute("presents"));
var presentDtos = (PresentDto[])serializer.Deserialize(new MemoryStream(Encoding.UTF8.GetBytes(xmlString)));
var sb = new StringBuilder();
var validCashPresents = new List <Cash>();
var validGiftPresents = new List <Gift>();
foreach (var presentDto in presentDtos)
{
if (!IsValid(presentDto))
{
sb.AppendLine(FailureMessage);
continue;
}
var invitation = context.Invitations.FirstOrDefault(i => i.Id == presentDto.InvitationId);
if (invitation == null)
{
sb.AppendLine(FailureMessage);
continue;
}
Type presentType = Assembly
.GetAssembly(typeof(Present))
.GetTypes()
.FirstOrDefault(type => type.Name.ToLower() == (presentDto.Type).ToLower());
if (presentDto.Type.ToLower() == "cash")
{
if (presentDto.Amount == null)
{
sb.AppendLine(FailureMessage);
continue;
}
var present = (Cash)Mapper.Map(presentDto, presentDto.GetType(), presentType);
validCashPresents.Add(present);
invitation.Present = present;
}
if (presentDto.Type.ToLower() == "gift")
{
if (presentDto.Name == null)
{
sb.AppendLine(FailureMessage);
continue;
}
var size = presentDto.Size;
Size presentSize = Size.NotSpecified;
if (size != null)
{
bool isPresentSizeValid = Enum.TryParse(size, out presentSize);
if (!isPresentSizeValid)
{
sb.AppendLine(FailureMessage);
continue;
}
}
var present = (Gift)Mapper.Map(presentDto, presentDto.GetType(), presentType);
validGiftPresents.Add(present);
invitation.Present = present;
}
var guestName = context.Invitations
.Include(i => i.Guest)
.FirstOrDefault(i => i.Id == presentDto.InvitationId)
.Guest.FullName;
sb.AppendLine(String.Format(SuccessMessage, $" {presentDto.Type} from {guestName}"));
}
context.CashPresents.AddRange(validCashPresents);
context.GiftPresents.AddRange(validGiftPresents);
context.SaveChanges();
var result = sb.ToString();
return(result);
}
public static string ImportVenues(WeddingsPlannerDbContext context, string xmlString)
{
var serializer = new XmlSerializer(typeof(VenueDto[]), new XmlRootAttribute("venues"));
var venueDtos = (VenueDto[])serializer.Deserialize(new MemoryStream(Encoding.UTF8.GetBytes(xmlString)));
var sb = new StringBuilder();
var validVenues = new List <Venue>();
foreach (var venueDto in venueDtos)
{
if (!IsValid(venueDto))
{
sb.AppendLine(FailureMessage);
continue;
}
var venue = new Venue()
{
Name = venueDto.Name,
Capacity = venueDto.Capacity,
Town = venueDto.Town
};
validVenues.Add(venue);
sb.AppendLine(String.Format(SuccessMessage, venueDto.Name));
}
context.Venues.AddRange(validVenues);
context.SaveChanges();
Random random = new Random();
var weddingsVenues = new List <WeddingsVenue>();
var weddings = context.Weddings.ToList();
foreach (Wedding wedding in weddings)
{
int randomId = random.Next(2, context.Venues.Count() + 1);
Venue venue = context.Venues.Find(randomId);
var weddingVenue = new WeddingsVenue()
{
Venue = venue,
Wedding = wedding
};
venue = context.Venues.Find(randomId - 1);
var weddingVenue2 = new WeddingsVenue()
{
Venue = venue,
Wedding = wedding
};
weddingsVenues.Add(weddingVenue);
weddingsVenues.Add(weddingVenue2);
}
context.WeddingsVenues.AddRange(weddingsVenues);
context.SaveChanges();
var result = sb.ToString();
return(result);
}
public static string ImportWeddingsAndInvitations(WeddingsPlannerDbContext context, string jsonString)
{
var weddingDtos = JsonConvert.DeserializeObject <WeddingDto[]>(jsonString);
var sb = new StringBuilder();
var validWeddings = new List <Wedding>();
foreach (var weddingDto in weddingDtos)
{
if (!IsValid(weddingDto))
{
sb.AppendLine(FailureMessage);
continue;
}
Person bride = context.Persons.FirstOrDefault(person =>
person.FullName == weddingDto.Bride);
Person bridesgroom = context.Persons.FirstOrDefault(person =>
person.FullName == weddingDto.Bridegroom);
Agency agency = context.Agencies.FirstOrDefault(ag => ag.Name == weddingDto.Agency);
DateTime date = DateTime.Parse(weddingDto.Date);
if (bride == null || bridesgroom == null || agency == null)
{
sb.AppendLine(FailureMessage);
continue;
}
var wedding = new Wedding()
{
Bride = bride,
Bridegroom = bridesgroom,
Date = date,
Agency = agency
};
var guests = weddingDto.Guests;
var validGuests = new List <Invitation>();
if (guests?.Count > 0)
{
foreach (var guestDto in guests)
{
if (!IsValid(guestDto))
{
continue;
}
var guest = context.Persons.FirstOrDefault(p => p.FullName == guestDto.Name);
if (guest == null)
{
continue;
}
var invitation = new Invitation()
{
Guest = guest,
IsAttending = guestDto.RSVP,
Family = guestDto.Family
};
validGuests.Add(invitation);
}
wedding.Invitations.AddRange(validGuests);
}
validWeddings.Add(wedding);
sb.AppendLine(String.Format(SuccessMessage, $"wedding of {bride.FirstName} and {bridesgroom.FirstName}"));
}
context.Weddings.AddRange(validWeddings);
context.SaveChanges();
var result = sb.ToString();
return(result);
}
