public TourViewModel GetElement(TourBindingModel model)
{
if (model == null)
{
return(null);
}
using (var context = new TourFirmDatabase())
{
var tour = context.Tours
.Include(rec => rec.TourGuides)
.ThenInclude(rec => rec.Guide)
.FirstOrDefault(rec => rec.ID == model.ID);
return(tour != null ? new TourViewModel
{
ID = tour.ID,
Name = tour.Name,
Country = tour.Country,
Price = tour.Price,
OperatorID = tour.OperatorID,
HaltID = tour.HaltID,
TourGuides = tour.TourGuides
.ToDictionary(recTG => recTG.GuideID, recTG => (recTG.Guide?.Surname))
} :
null);
}
}
public List <ExcursionViewModel> GetAllExcursionStatistics(ReportBindingModel model)
{
using (var context = new TourFirmDatabase())
{
var excursions = from tour in context.Tours
join travelTours in context.TravelTours
on tour.ID equals travelTours.TourID
join travel in context.Travels
on travelTours.TravelID equals travel.ID
where travel.DateStart >= model.DateFrom
where travel.DateEnd <= model.DateTo
join tourguide in context.TourGuides
on tour.ID equals tourguide.TourID
join guide in context.Guides
on tourguide.GuideID equals guide.ID
join guideExcursion in context.GuideExcursions
on guide.ID equals guideExcursion.GuideID
join excursion in context.Excursions
on guideExcursion.ExcursionID equals excursion.ID
select new ExcursionViewModel
{
Price = excursion.Price,
Duration = excursion.Duration
};
return(excursions.ToList());
}
}
private Guide CreateModel(GuideBindingModel model, Guide guide, TourFirmDatabase context)
{
guide.Name = model.Name;
guide.Surname = model.Surname;
guide.PhoneNumber = model.PhoneNumber;
guide.WorkPlace = model.WorkPlace;
guide.MainLanguage = model.MainLanguage;
guide.AdditionalLanguage = model.AdditionalLanguage;
guide.OperatorID = model.OperatorID;
if (model.GuideExcursions != null)
{
if (model.ID.HasValue)
{
var excursionGuides = context.GuideExcursions.Where(rec =>
rec.GuideID == model.ID.Value).ToList();
// удалили те, которых нет в модели
context.GuideExcursions.RemoveRange(excursionGuides.Where(rec =>
!model.GuideExcursions.ContainsKey(rec.ExcursionID)).ToList());
context.SaveChanges();
}
// добавили новые
foreach (var eg in model.GuideExcursions)
{
context.GuideExcursions.Add(new GuideExcursion
{
GuideID = guide.ID,
ExcursionID = eg.Key,
});
context.SaveChanges();
}
}
return(guide);
}
public void Update(TravelBindingModel model)
{
using (var context = new TourFirmDatabase())
{
using (var transaction = context.Database.BeginTransaction())
{
try
{
Travel element = context.Travels.FirstOrDefault(rec => rec.ID == model.ID);
if (element == null)
{
throw new Exception("Элемент не найден");
}
CreateModel(model, element, context);
transaction.Commit();
}
catch
{
transaction.Rollback();
throw;
}
}
}
}
public TravelViewModel GetElement(TravelBindingModel model)
{
if (model == null)
{
return(null);
}
using (var context = new TourFirmDatabase())
{
Travel travel = context.Travels
.Include(rec => rec.TravelTours)
.ThenInclude(rec => rec.Tour)
.Include(rec => rec.TravelExcursions)
.ThenInclude(rec => rec.Excursion)
.FirstOrDefault(rec => rec.Name == model.Name || rec.ID == model.ID);
return(travel != null ? new TravelViewModel
{
ID = travel.ID,
Name = travel.Name,
DateStart = travel.DateStart,
DateEnd = travel.DateEnd,
TouristID = travel.TouristID,
TravelTours = travel.TravelTours.ToDictionary(recTT => recTT.TourID, recTT => (recTT.Tour?.Name)),
TravelExcursions = travel.TravelExcursions.ToDictionary(recTE => recTE.ExcursionID, recTE => (recTE.Excursion?.Name))
} : null);
}
}
public List <GuideViewModel> GetFilteredList(GuideBindingModel model)
{
if (model == null)
{
return(null);
}
using (var context = new TourFirmDatabase())
{
return(context.Guides
.Include(rec => rec.GuideExcursions)
.ThenInclude(rec => rec.Excursion)
.Where(rec => (rec.OperatorID == model.OperatorID))
.ToList().
Select(rec => new GuideViewModel
{
ID = rec.ID,
Name = rec.Name,
Surname = rec.Surname,
PhoneNumber = rec.PhoneNumber,
WorkPlace = rec.WorkPlace,
MainLanguage = rec.MainLanguage,
AdditionalLanguage = rec.AdditionalLanguage,
OperatorID = rec.OperatorID,
GuideExcursions = rec.GuideExcursions
.ToDictionary(recEX => recEX.ExcursionID, recEX => (recEX.Excursion?.Name))
}).ToList());
}
}
private Tour CreateModel(TourBindingModel model, Tour tour, TourFirmDatabase context)
{
tour.Name = model.Name;
tour.Country = model.Country;
tour.Price = model.Price;
tour.HaltID = model.HaltID;
tour.OperatorID = model.OperatorID;
if (model.ID.HasValue)
{
var tourGuides = context.TourGuides.Where(rec =>
rec.TourID == model.ID.Value).ToList();
// удалили те, которых нет в модели
context.TourGuides.RemoveRange(tourGuides.ToList());
context.SaveChanges();
}
// добавили новые
foreach (var tg in model.TourGuides)
{
context.TourGuides.Add(new TourGuide
{
TourID = tour.ID,
GuideID = tg.Key,
});
context.SaveChanges();
}
return(tour);
}
public ReportTourExcursionsViewModel GetTourExcursion(TourBindingModel model)
{
using (var context = new TourFirmDatabase())
{
var excusrions = from tour in context.Tours
where tour.ID == model.ID
join tourguide in context.TourGuides
on tour.ID equals tourguide.TourID
join guide in context.Guides
on tourguide.GuideID equals guide.ID
join guideexcurion in context.GuideExcursions
on guide.ID equals guideexcurion.GuideID
join excursion in context.Excursions
on guideexcurion.ExcursionID equals excursion.ID
select new ExcursionViewModel
{
Name = excursion.Name,
Price = excursion.Price,
Duration = excursion.Duration
};
return(new ReportTourExcursionsViewModel
{
TourName = model.Name,
Excursions = excusrions.ToList()
});
}
}
public ReportTravelGuidesViewModel GetTravelGuides(TravelBindingModel model)
{
using (var context = new TourFirmDatabase())
{
var guides = from travel in context.Travels
where travel.ID == model.ID
join travelExcursion in context.TravelExcursions
on travel.ID equals travelExcursion.TravelID
join guideExcursion in context.GuideExcursions
on travelExcursion.ExcursionID equals guideExcursion.ExcursionID
join guide in context.Guides
on guideExcursion.GuideID equals guide.ID
select new GuideViewModel
{
Name = guide.Name,
Surname = guide.Surname,
PhoneNumber = guide.PhoneNumber,
WorkPlace = guide.WorkPlace,
MainLanguage = guide.MainLanguage,
AdditionalLanguage = guide.AdditionalLanguage
};
return(new ReportTravelGuidesViewModel
{
TravelName = model.Name,
Guides = guides.ToList()
});
}
}
public List <ReportGuidesViewModel> GetFullListGuides(ReportTourBindingModel model, int _OperatorID)
{
using (var context = new TourFirmDatabase())
{
var guides = from tour in context.Tours
join travelTours in context.TravelTours
on tour.ID equals travelTours.TourID
where tour.OperatorID == _OperatorID
join travel in context.Travels
on travelTours.TravelID equals travel.ID
where travel.DateStart >= model.DateFrom
where travel.DateEnd <= model.DateTo
join tourguide in context.TourGuides
on tour.ID equals tourguide.TourID
join guide in context.Guides
on tourguide.GuideID equals guide.ID
where guide.OperatorID == _OperatorID
join guideExcursion in context.GuideExcursions
on guide.ID equals guideExcursion.GuideID
join excursion in context.Excursions
on guideExcursion.ExcursionID equals excursion.ID
select new ReportGuidesViewModel
{
DateStartTravel = travel.DateStart,
GuideSurname = guide.Surname,
GuideName = guide.Name,
GuideWorkPlace = guide.WorkPlace,
ExcursionName = excursion.Name,
TourName = tour.Name
};
return(guides.ToList());
}
}
public List <ReportTravelsViewModel> GetFullListTravels(ReportTravelBindingModel model, int _TouristID)
{
using (var context = new TourFirmDatabase())
{
var travels = from travel in context.Travels
join travelExcursion in context.TravelExcursions
on travel.ID equals travelExcursion.TravelID
where travel.TouristID == _TouristID
where travel.DateStart >= model.DateFrom
where travel.DateEnd <= model.DateTo
join guideExcursion in context.GuideExcursions
on travelExcursion.ExcursionID equals guideExcursion.ExcursionID
join guide in context.Guides
on guideExcursion.GuideID equals guide.ID
join excursion in context.Excursions
on travelExcursion.ExcursionID equals excursion.ID
select new ReportTravelsViewModel
{
TravelName = travel.Name,
DateStart = travel.DateStart,
DateEnd = travel.DateEnd,
ExcursionName = excursion.Name,
GuideSurname = guide.Surname,
GuideName = guide.Name
};
return(travels.ToList());
}
}
public void Insert(GuideBindingModel model)
{
using (var context = new TourFirmDatabase())
{
using (var transaction = context.Database.BeginTransaction())
{
try
{
Guide guide = new Guide
{
Name = model.Name,
Surname = model.Surname,
PhoneNumber = model.PhoneNumber,
WorkPlace = model.WorkPlace,
MainLanguage = model.MainLanguage,
AdditionalLanguage = model.AdditionalLanguage,
OperatorID = model.OperatorID
};
context.Guides.Add(guide);
context.SaveChanges();
CreateModel(model, guide, context);
context.SaveChanges();
transaction.Commit();
}
catch
{
transaction.Rollback();
throw;
}
}
}
}
public void Insert(TourBindingModel model)
{
using (var context = new TourFirmDatabase())
{
using (var transaction = context.Database.BeginTransaction())
{
try
{
Tour tour = new Tour
{
Name = model.Name,
Price = model.Price,
Country = model.Country,
HaltID = model.HaltID,
OperatorID = model.OperatorID
};
context.Tours.Add(tour);
context.SaveChanges();
CreateModel(model, tour, context);
context.SaveChanges();
transaction.Commit();
}
catch
{
transaction.Rollback();
throw;
}
}
}
}
public List <GuideViewModel> GetAllGuidesStatistics(ReportBindingModel model, int _OperatorID)
{
using (var context = new TourFirmDatabase())
{
var guides = from tour in context.Tours
where tour.OperatorID != _OperatorID
join travelTours in context.TravelTours
on tour.ID equals travelTours.TourID
join travel in context.Travels
on travelTours.TravelID equals travel.ID
where travel.DateStart >= model.DateFrom
where travel.DateEnd <= model.DateTo
join tourguide in context.TourGuides
on tour.ID equals tourguide.TourID
join guide in context.Guides
on tourguide.GuideID equals guide.ID
join guideExcursion in context.GuideExcursions
on guide.ID equals guideExcursion.GuideID
join excursion in context.Excursions
on guideExcursion.ExcursionID equals excursion.ID
select new GuideViewModel
{
Surname = guide.Surname,
Name = guide.Name,
PhoneNumber = guide.PhoneNumber
};
return(guides.ToList());
}
}
public List <TravelViewModel> GetFilteredList(TravelBindingModel model)
{
if (model == null)
{
return(null);
}
using (var context = new TourFirmDatabase())
{
return(context.Travels
.Include(rec => rec.TravelTours)
.ThenInclude(rec => rec.Tour)
.Include(rec => rec.TravelExcursions)
.ThenInclude(rec => rec.Excursion)
.Where(rec => (model.DateFrom.HasValue && model.DateTo.HasValue && rec.DateStart.Date >= model.DateFrom.Value.Date &&
rec.DateEnd.Date <= model.DateTo.Value.Date && rec.TouristID == model.TouristID) ||
(!model.DateFrom.HasValue && !model.DateTo.HasValue && rec.TouristID == model.TouristID))
.ToList().
Select(rec => new TravelViewModel
{
ID = rec.ID,
Name = rec.Name,
DateStart = rec.DateStart,
DateEnd = rec.DateEnd,
TouristID = rec.TouristID,
TravelTours = rec.TravelTours.ToDictionary(recTT => recTT.TourID, recTT => (recTT.Tour?.Name)),
TravelExcursions = rec.TravelExcursions.ToDictionary(recTE => recTE.ExcursionID, recTE => (recTE.Excursion?.Name))
}).ToList());
}
}
public GuideViewModel GetElement(GuideBindingModel model)
{
if (model == null)
{
return(null);
}
using (var context = new TourFirmDatabase())
{
var guide = context.Guides
.Include(rec => rec.GuideExcursions)
.ThenInclude(rec => rec.Excursion)
.FirstOrDefault(rec => rec.ID == model.ID || rec.Surname == model.Surname);
return(guide != null ? new GuideViewModel
{
ID = guide.ID,
Name = guide.Name,
Surname = guide.Surname,
PhoneNumber = guide.PhoneNumber,
WorkPlace = guide.WorkPlace,
MainLanguage = guide.MainLanguage,
AdditionalLanguage = guide.AdditionalLanguage,
OperatorID = guide.OperatorID,
GuideExcursions = guide.GuideExcursions
.ToDictionary(recEX => recEX.ExcursionID, recEX => (recEX.Excursion?.Name))
} :
null);
}
}
public void Insert(OperatorBindingModel model)
{
using (var context = new TourFirmDatabase())
{
context.Operators.Add(CreateModel(model, new Operator()));
context.SaveChanges();
}
}
public void Insert(TouristBindingModel model)
{
using (var context = new TourFirmDatabase())
{
context.Tourists.Add(CreateModel(model, new Tourist()));
context.SaveChanges();
}
}
public void Insert(ExcursionBindingModel model)
{
using (var context = new TourFirmDatabase())
{
context.Excursions.Add(CreateModel(model, new Excursion()));
context.SaveChanges();
}
}
public List<HaltViewModel> GetFullList()
{
using (var context = new TourFirmDatabase())
{
return context.Halts.Select(rec => new HaltViewModel
{
ID = rec.ID,
Name = rec.Name,
Address = rec.Address,
OperatorID = rec.OperatorID
}).ToList();
}
}
public void Update(OperatorBindingModel model)
{
using (var context = new TourFirmDatabase())
{
var element = context.Operators.FirstOrDefault(rec => rec.ID == model.ID);
if (element == null)
{
throw new Exception("Элемент не найден");
}
CreateModel(model, element);
context.SaveChanges();
}
}
public List <PlaceViewModel> GetFullList()
{
using (var context = new TourFirmDatabase())
{
return(context.Places
.ToList()
.Select(rec => new PlaceViewModel
{
ID = rec.ID,
Name = rec.Name,
Type = rec.Type,
TouristID = rec.TouristID
}).ToList());
}
}
public List <ExcursionViewModel> GetFullList()
{
using (var context = new TourFirmDatabase())
{
return(context.Excursions
.Select(rec => new ExcursionViewModel
{
ID = rec.ID,
Name = rec.Name,
Price = rec.Price,
Duration = rec.Duration,
PlaceID = rec.PlaceID,
TouristID = rec.TouristID,
})
.ToList());
}
}
public void Delete(OperatorBindingModel model)
{
using (var context = new TourFirmDatabase())
{
var element = context.Operators.FirstOrDefault(rec => rec.ID ==
model.ID);
if (element != null)
{
context.Operators.Remove(element);
context.SaveChanges();
}
else
{
throw new Exception("Элемент не найден");
}
}
}
public List <TourViewModel> GetAllTourByMonthStatistic(StatisticBindingModel model)
{
using (var context = new TourFirmDatabase())
{
var tours = from tour in context.Tours
join travelTours in context.TravelTours
on tour.ID equals travelTours.TourID
join travel in context.Travels
on travelTours.TravelID equals travel.ID
where travel.DateStart.Month == model.month
select new TourViewModel
{
Country = tour.Country,
Price = tour.Price
};
return(tours.ToList());
}
}
public List <OperatorViewModel> GetFullList()
{
using (var context = new TourFirmDatabase())
{
return(context.Operators
.Select(rec => new OperatorViewModel
{
ID = rec.ID,
Name = rec.Name,
Surname = rec.Surname,
PhoneNumber = rec.PhoneNumber,
Login = rec.Login,
Mail = rec.Mail,
Password = rec.Password
})
.ToList());
}
}
public HaltViewModel GetElement(HaltBindingModel model)
{
if (model == null)
{
return null;
}
using (var context = new TourFirmDatabase())
{
var halt = context.Halts.FirstOrDefault(rec => rec.Name == model.Name || rec.ID == model.ID);
return halt != null ? new HaltViewModel
{
ID = halt.ID,
Name = halt.Name,
Address = halt.Address,
OperatorID = halt.OperatorID
} :
null;
}
}
public void Insert(TravelBindingModel model)
{
using (var context = new TourFirmDatabase())
{
using (var transaction = context.Database.BeginTransaction())
{
try
{
CreateModel(model, new Travel(), context);
transaction.Commit();
}
catch
{
transaction.Rollback();
throw;
}
}
}
}
public PlaceViewModel GetElement(PlaceBindingModel model)
{
if (model == null)
{
return(null);
}
using (var context = new TourFirmDatabase())
{
Place place = context.Places
.FirstOrDefault(rec => rec.Name == model.Name || rec.ID == model.ID);
return(place != null ? new PlaceViewModel
{
ID = place.ID,
Name = place.Name,
Type = place.Type,
TouristID = place.TouristID
} : null);
}
}
public List<HaltViewModel> GetFilteredList(HaltBindingModel model)
{
if (model == null)
{
return null;
}
using (var context = new TourFirmDatabase())
{
return context.Halts
.Where(rec => (rec.OperatorID == model.OperatorID))
.ToList().
Select(rec => new HaltViewModel
{
ID = rec.ID,
Name = rec.Name,
Address = rec.Address,
OperatorID = rec.OperatorID
}).ToList();
}
}
