private List <DTOProjectDataTable> getStatusInListDTOProjectDataTable(IDbContext context, IQueryable <DTOProjectDataTable> source) { // Get total number of tasks for each requirement var totals = (from project in source // left join Requirement join requirementRecord in getAll <Requirement>(context) on project.ProjectId equals requirementRecord.ProjectId into reqOuterJoin from requirement in reqOuterJoin.DefaultIfEmpty() // left join Task join taskRecord in getAll <Task>(context) on requirement.Id equals taskRecord.RequirementId into taskOuterJoin from task in taskOuterJoin.DefaultIfEmpty() let projectCanBeDeleted = (requirement != null) group project by new { ProjectId = project.ProjectId, project.Name, project.CreationDate, project.Description, project.NumberOfTasks, ProjectIdFromRequirement = requirement.ProjectId } into g select new DTOProjectDataTable { Name = g.Key.Name, CreationDate = g.Key.CreationDate, Description = g.Key.Description, ProjectId = g.Key.ProjectId, NumberOfTasks = g.Count(), Status = 0, ThereAreRequirements = g.Key.ProjectId == g.Key.ProjectIdFromRequirement }).AsQueryable(); // Get total number of complete tasks for each requirement var completes = (from project in source // left join Requirement join requirementRecord in getAll <Requirement>(context) on project.ProjectId equals requirementRecord.ProjectId into reqOuterJoin from requirement in reqOuterJoin.DefaultIfEmpty() // left join Task join taskRecord in getAll <Task>(context) on requirement.Id equals taskRecord.RequirementId into taskOuterJoin from task in taskOuterJoin.DefaultIfEmpty() // left join complete join completeRecord in getAll <Complete>(context) on task.Id equals completeRecord.TaskId into completeOuterJoin from complete in completeOuterJoin.DefaultIfEmpty() where complete.IsComplete group project by project.ProjectId into g select new { ProjectId = g.Key, ProjectCount = g.Count(), }).AsQueryable(); // Keep same ordering totals = dataTableService.CustomOrderBy <DTOProjectDataTable>(totals); // Get lists var totalList = totals.ToList(); var completeList = completes.ToList(); // Ge the Status for each requirement in a DTORequirementDataTable var union = (from totalRecord in totalList join completeRecord in completeList on totalRecord.ProjectId equals completeRecord.ProjectId into completeOuterJoin from complete in completeOuterJoin.DefaultIfEmpty() select new DTOProjectDataTable { Name = totalRecord.Name, CreationDate = totalRecord.CreationDate, Description = totalRecord.Description, ProjectId = totalRecord.ProjectId, NumberOfTasks = totalRecord.NumberOfTasks, ThereAreRequirements = totalRecord.ThereAreRequirements, Status = complete == null ? 0 : Math.Round((double)complete.ProjectCount / (double)totalRecord.NumberOfTasks * 100) }).ToList(); return(union); }
/// <summary> /// Get the percentage of complete task for each requirement. /// </summary> /// <param name="context">Context of the database.</param> /// <param name="requirement">Requirement DTO that contains the requirement id.</param> /// <returns>The percentage of the complete task.</returns> private List <DTORequirementDataTable> getStatusInListDTORequirementDataTable(IDbContext context, IQueryable <DTORequirementDataTable> source) { // Get total number of tasks for each requirement var totals = (from req in source // left join Task join taskRecord in getAll <Task>(context) on req.RequirementId equals taskRecord.RequirementId into taskOuterJoin from task in taskOuterJoin.DefaultIfEmpty() group req by new { req.RequirementId, req.ProjectId, req.CreationDate, req.Description, req.Name, req.NumberOfTasks, RequirementIdInTask = task.RequirementId } into g select new DTORequirementDataTable { RequirementId = g.Key.RequirementId, ProjectId = g.Key.ProjectId, CreationDate = g.Key.CreationDate, Description = g.Key.Description, Name = g.Key.Name, NumberOfTasks = g.Count(), ThereAreTasks = g.Key.RequirementIdInTask == g.Key.RequirementId }).AsQueryable(); // Get total number of complete tasks for each requirement var completes = (from req in source // left join Task join taskRecord in getAll <Task>(context) on req.RequirementId equals taskRecord.RequirementId into taskOuterJoin from task in taskOuterJoin.DefaultIfEmpty() // left join complete join completeRecord in getAll <Complete>(context) on task.Id equals completeRecord.TaskId into completeOuterJoin from complete in completeOuterJoin.DefaultIfEmpty() where complete.IsComplete group req by req.RequirementId into g select new { ReqId = g.Key, ReqIdCount = g.Count(), }).AsQueryable(); // Set inital ordering totals = dataTableService.CustomOrderBy <DTORequirementDataTable>(totals); // Get total and complete collection as enumerables var totalsEnum = totals.ToList(); var completesEnum = completes.ToList(); // Ge the Status for each requirement in a DTORequirementDataTable var union = (from totalRecord in totalsEnum join completeRecord in completesEnum on totalRecord.RequirementId equals completeRecord.ReqId into completeOuterJoin from complete in completeOuterJoin.DefaultIfEmpty() select new DTORequirementDataTable { RequirementId = totalRecord.RequirementId, ProjectId = totalRecord.ProjectId, CreationDate = totalRecord.CreationDate, Description = totalRecord.Description, Name = totalRecord.Name, NumberOfTasks = totalRecord.NumberOfTasks, ThereAreTasks = totalRecord.ThereAreTasks, Status = (complete == null) ? 0 : Math.Round((double)complete.ReqIdCount / (double)totalRecord.NumberOfTasks * 100) }).ToList(); return(union); }