public List <FilmViewModel> GetFilmsByFilter(FilmsFilter filter)
{
if (filter == null)
{
throw new NullReferenceException("filter");
}
var result = new List <FilmViewModel>();
var films = new List <Film>();
using (var ctx = new FilmsDbContext())
{
if (Properties.Settings.Default.UsStoredProcedure)
{
var maxScoreParam = new System.Data.SqlClient.SqlParameter("@maxScore", filter.MaxScore);
var minScoreParam = new System.Data.SqlClient.SqlParameter("@minScore", filter.MinScore);
films = ctx.Database.SqlQuery <Film>("GetFilmList @maxScore, @minScore", new[] { maxScoreParam, minScoreParam })
.ToList();
films.ForEach(f =>
{
var idParam = new System.Data.SqlClient.SqlParameter("@filmId", f.Id);
f.Actors = ctx.Database.SqlQuery <Actor>("GetFilmActors @filmId", idParam).ToList();
idParam = new System.Data.SqlClient.SqlParameter("@filmId", f.Id);
f.Ganre = ctx.Database.SqlQuery <Ganre>("GetFilmGanre @filmId", idParam).FirstOrDefault();
});
}
else
{
films = ctx.Films
.Include("Actors")
.Include("Ganre")
.Where(f => f.Score >= filter.MinScore && f.Score <= filter.MaxScore).ToList();
}
if (!string.IsNullOrEmpty(filter.OrderColumn))
{
FilmsOrderBy(ref films, filter.OrderColumn, filter.SortDirection);
}
result = films.Select(f => new FilmViewModel
{
Actors = f.Actors.Select(a => a.Name).ToList(),
GanreId = f.GanreId,
Ganre = f.Ganre.Name,
Budget = f.Budget,
Description = f.Description,
Name = f.Name,
Id = f.Id,
Producer = f.Producer,
Score = f.Score
}).ToList();
}
return(result);
}
public ActionResult <FilmsFullReport> GetFullReport(FilmsFilter filter)
{
if (!Request.IsAuthorized())
{
return(Unauthorized());
}
if (filter.FromYear.HasValue && filter.ToYear.HasValue && filter.FromYear > filter.ToYear)
{
return(BadRequest("FromYear is greater than ToYear"));
}
var films = Films.Items
.Where(film => film.Title.ContainsIgnoreCase(filter.Title))
.Where(film => film.Year > (filter.FromYear ?? 0))
.Where(film => film.Year < (filter.ToYear ?? 3000))
.Select(film => new FilmFullData
{
ID = film.ID,
Title = film.Title,
Year = film.Year,
Budget = Assignments.ByFilm(film.ID)
.Select(x => Employees.Get(x.EmployeeId))
.Sum(x => x.Salary),
Stuff = Assignments.ByFilm(film.ID)
.Select(x => Employees.Get(x.EmployeeId))
.Select(x => new Stuff {
Role = x.Role.ToString(), Name = x.Name, Costs = x.Salary
}).ToArray()
}).ToArray();
var result = new FilmsFullReport
{
Count = films.Length,
Budget = films.Sum(film => film.Budget),
Data = films
};
return(Ok(result));
}
