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