using System; using System.Collections.Generic; using System.Data.Entity; using System.Linq; using System.Web; using EnVisage.Models; using System.Data.Entity.Infrastructure; using jQuery.DataTables.Mvc; using System.Data.SqlClient; using System.Data.Entity.Core.Objects; using System.Data; namespace EnVisage.Code.BLL { public class ProjectManager : ManagerBase { public ProjectManager(EnVisageEntities dbContext) : base(dbContext) { } protected override Project InitInstance() { return new Project { Id = Guid.NewGuid() }; } protected override Project RetrieveReadOnlyById(Guid key) { return DataTable.AsNoTracking().FirstOrDefault(t => t.Id == key); } public override DbSet DataTable { get { return DbContext.Projects; } } public override Project Save(ProjectModel model) { if (model == null) throw new ArgumentNullException("model"); var partManager = new ProjectPartManager(DbContext); if (!model.HasChildren && model.Parts.Count == 1) { var part = model.Parts[0]; model.StatusId = part.StatusId; model.TypeId = part.TypeId; model.ClientId = part.ClientId; model.Details = part.Details; model.Priority = part.Priority; model.IsRevenueGenerating = part.IsRevenueGenerating; model.Deadline = part.Deadline; model.Probability = part.Probability; model.ParentProjectId = null; model.ExternalContacts = part.ExternalContacts; model.InternalContacts = part.InternalContacts; model.AssignedTeams = part.AssignedTeams; model.StrategicGoals = part.StrategicGoals; model.PerformanceYellowThreshold = part.UseThreshold ? part.PerformanceYellowThreshold : null; model.PerformanceRedThreshold = part.UseThreshold ? part.PerformanceRedThreshold : null; } else if (model.HasChildren && model.Parts.Count > 0) { model.StatusId = model.Parts[0].StatusId; model.TypeId = model.Parts[0].TypeId; model.ClientId = model.Parts[0].ClientId; } var obj = base.Save(model); if (model.HasChildren && model.Parts.Count > 0) { var currentParts = model.Id == Guid.Empty ? new List() : DbContext.Projects.Where(t => t.ParentProjectId == model.Id).ToList(); var partNum = 1; foreach (var currentPart in currentParts) { if (model.Parts.FirstOrDefault(x => x.Id == currentPart.Id) == null) { // SA. ENV-502. Attachments. Get project files to delete IList holders = this.GetSubprojectsAndParts(currentPart); FileManager fileMngr = new FileManager(DbContext); fileMngr.QueuePermanentFilesToDelete(holders); //DbContext.Entry(currentPart).State = EntityState.Deleted; (DbContext as IObjectContextAdapter).ObjectContext.ExecuteStoreCommand(string.Format("exec sp_DeleteProject '{0}'", currentPart.Id)); // SA. ENV-502. Delete attachments fileMngr.DeleteQueuedFiles(true); } } for (var i = 0; i < model.Parts.Count; i++) { var projectModel = model.Parts[i]; if (projectModel.DeletedPart && projectModel.Id != Guid.Empty) { var item2Delete = currentParts.FirstOrDefault(t => t.Id == projectModel.Id); if (item2Delete != null) { // SA. ENV-502. Attachments. Get project files to delete Project deletableProject = DbContext.Projects.SingleOrDefault(x => x.Id.Equals(projectModel.Id)); if ((deletableProject != null) && !deletableProject.Id.Equals(Guid.Empty)) { IList holders = this.GetSubprojectsAndParts(deletableProject); FileManager fileMngr = new FileManager(DbContext); fileMngr.QueuePermanentFilesToDelete(holders); //DbContext.Entry(item2Delete).State = EntityState.Deleted; (DbContext as IObjectContextAdapter).ObjectContext.ExecuteStoreCommand(string.Format("exec sp_DeleteProject '{0}'", projectModel.Id)); // SA. ENV-502. Delete attachments fileMngr.DeleteQueuedFiles(true); } } } else if (currentParts.All(t => t.Id != projectModel.Id)) { projectModel.ParentProjectId = obj.Id; projectModel.SaveAsCopy = model.SaveAsCopy; projectModel.PartNum = partNum++; var oldId = model.Parts[i].OldId; model.Parts[i] = (ProjectPartModel)partManager.Save(projectModel); model.Parts[i].OldId = oldId; model.Parts[i].Attachments = projectModel.Attachments; } else { var oldId = model.Parts[i].OldId; projectModel.SaveAsCopy = model.SaveAsCopy; projectModel.PartNum = partNum++; model.Parts[i] = (ProjectPartModel)partManager.Save(projectModel); model.Parts[i].OldId = oldId; model.Parts[i].Attachments = projectModel.Attachments; } } } if (!model.HasChildren && model.Parts.Count == 1) { SaveContacts(obj, model.InternalContacts, model.ExternalContacts); SaveTeams(obj, model.AssignedTeams); SaveStrategicGoals(obj, model.StrategicGoals); } return obj; } /// /// Adds new teams to the project and all (except actuals) its scenarios, except the given one /// /// Id of the updatable project /// Teams to add /// Scenario to exclude from processing /// SA. ENV-754 public void UpdateTeamsOfProjectAndScenarios(Project project, List teamsToAdd, Guid excludeScenarioId, bool appendProjectTeamsToScenario) { if (project == null) return; // The result team set for project var resultProjectTeams = teamsToAdd; if (appendProjectTeamsToScenario) { var currentProjectTeams = DbContext.Team2Project.Where(x => x.ProjectId == project.Id).Select(y => y.TeamId).ToList(); var newTeams = teamsToAdd.Except(currentProjectTeams); resultProjectTeams = currentProjectTeams.Union(newTeams).ToList(); } SaveTeams(project, resultProjectTeams); // Get scenarios to update List scenariosToUpdate = DbContext.Scenarios.Where(x => x.ParentId.HasValue && x.ParentId.Value.Equals(project.Id) && x.Type != (int)ScenarioType.Actuals) .Select(x => x.Id).ToList(); scenariosToUpdate.Remove(excludeScenarioId); // Perform update ScenarioManager scMngr = new ScenarioManager(DbContext); scMngr.UpdateScenarioTeams(scenariosToUpdate, resultProjectTeams); } public void SaveContacts(Project project, List internalContacts, List externalContacts) { if (externalContacts == null) externalContacts = new List(); if (internalContacts == null) internalContacts = new List(); var allCurrentContacts = externalContacts.Concat(internalContacts).ToList(); var oldContacts = DbContext.Contact2Project.Where(c2S => c2S.ShowId == project.Id).ToList(); foreach (var c in oldContacts) { if (allCurrentContacts.Contains(c.ContactId)) allCurrentContacts.Remove(c.ContactId); else DbContext.Contact2Project.Remove(c); } foreach (var cId in allCurrentContacts) { DbContext.Contact2Project.Add(new Contact2Project() { Id = Guid.NewGuid(), ContactId = cId, ShowId = project.Id }); } } public void SaveTeams(Project project, List assignedTeams) { if (project == null || project.Id == Guid.Empty) return; if (assignedTeams == null) assignedTeams = new List(); var projectsIds = new List { project.Id }; if (project.ParentProjectId.HasValue) projectsIds.Add(project.ParentProjectId.Value); var partsIds = DbContext.Projects.AsNoTracking() .Where(x => x.ParentProjectId.HasValue && projectsIds.Contains(x.ParentProjectId.Value)) .Select(x => x.Id).ToList(); projectsIds.AddRange(partsIds.Where(x => !projectsIds.Contains(x))); #region Saving permissions var oldTeamsOfCurrentProject = DbContext.Team2Project.Where(x => x.ProjectId == project.Id).ToList(); var oldTeamIds = oldTeamsOfCurrentProject.Select(x => x.TeamId).ToList(); var oldTeamsUsers = DbContext.User2Team.Where(x => oldTeamIds.Contains(x.TeamId)) .Select(x => x.UserId.ToLower()).Distinct().ToList(); var newTeamsUsers = DbContext.User2Team.Where(x => assignedTeams.Contains(x.TeamId)) .Select(x => x.UserId.ToLower()).Distinct().ToList(); // TODO: discuss, is it correct removing? in this case we can remove rights from all users for current project #region Removing permissions from users which teams has no allocation in scenarios of current project or current project parts var removedFromCurrentProjectUsers = oldTeamsUsers.Except(newTeamsUsers).ToList(); if (removedFromCurrentProjectUsers.Count > 0) { var contributorsInOtherParts = DbContext.Team2Scenario .Where(x => x.Allocation > 0 && x.Scenario.ParentId.HasValue && projectsIds.Contains(x.Scenario.ParentId.Value) && x.Scenario.ParentId != project.Id) .SelectMany(x => x.Team.User2Team.Select(u => u.UserId.ToLower())) .Distinct().ToList(); var removedUsers = removedFromCurrentProjectUsers.Except(contributorsInOtherParts).ToList(); if (removedUsers.Count > 0) DbContext.ProjectAccesses.RemoveRange(DbContext.ProjectAccesses.Where(x => projectsIds.Contains(x.ProjectId) && removedUsers.Contains(x.PrincipalId.ToString()))); } #endregion var currentPermissions = DbContext.ProjectAccesses.AsNoTracking().Where(x => projectsIds.Contains(x.ProjectId)).ToList(); //previous collection DOES NOT contain records that are not saved yet into the DB via context.SaveChanges, so we need to check the "Local" collection as well var localPermissions = DbContext.ProjectAccesses.Local.Where(x => projectsIds.Contains(x.ProjectId)).ToList(); // need to grant access for all new users to project and all it parts foreach (var projectId in projectsIds) { foreach (var user in newTeamsUsers) { var userId = new Guid(user); if (currentPermissions.Any(x => x.ProjectId == projectId && x.PrincipalId == userId)) continue; if (localPermissions.Any(x => x.ProjectId == projectId && x.PrincipalId == userId)) continue; DbContext.ProjectAccesses.Add(new ProjectAccess() { PrincipalId = userId, ProjectId = projectId, Read = 1, Write = 1 }); } } #endregion #region Saving teams foreach (var t in oldTeamsOfCurrentProject) { if (!assignedTeams.Contains(t.TeamId)) DbContext.Team2Project.Remove(t); } foreach (var tId in assignedTeams.Except(oldTeamIds)) { DbContext.Team2Project.Add(new Team2Project() { Id = Guid.NewGuid(), ProjectId = project.Id, TeamId = tId }); } #endregion } public void SaveStrategicGoals(Project project, List strategicGoals) { if (project == null || project.Id == Guid.Empty) return; if (strategicGoals == null) strategicGoals = new List(); #region Saving var oldGoalsOfCurrentProject = DbContext.StrategicGoal2Project.Where(x => x.ProjectId == project.Id).ToList(); var oldGoalsIds = oldGoalsOfCurrentProject.Select(x => x.StrategicGoalId).ToList(); foreach (var t in oldGoalsOfCurrentProject) { if (!strategicGoals.Contains(t.StrategicGoalId)) DbContext.StrategicGoal2Project.Remove(t); } foreach (var tId in strategicGoals.Except(oldGoalsIds)) { DbContext.StrategicGoal2Project.Add(new StrategicGoal2Project() { Id = Guid.NewGuid(), ProjectId = project.Id, StrategicGoalId = tId }); } #endregion } /// /// Returns list of IDs for all child projects and parts for given project via recursive search /// (including given project ID) /// /// Root project for the search /// public IList GetSubprojectsAndParts(Project project) { List result = new List(); if (project == null) return result; if (project.ChildProjects != null) { foreach (Project child in project.ChildProjects) { IList subProjects = GetSubprojectsAndParts(child); result.AddRange(subProjects); } } result.Add(project.Id); return result; } public List FindProjects(IEnumerable projectIds) { if (projectIds == null || projectIds.Count() <= 0) return new List(); return DbContext.Projects.Where(x => projectIds.Contains(x.Id)).ToList(); } public List GetProjects4User(Guid userId, bool groupByTeam, SortedColumn sortedColumn, int startIndex, int pageSize, string searchString, out int totalRecordCount, out int searchRecordCount) { if (groupByTeam) { return GetGrouped(userId, sortedColumn, startIndex, pageSize, searchString, out totalRecordCount, out searchRecordCount); } else { return GetUnGrouped(userId, sortedColumn, startIndex, pageSize, searchString, out totalRecordCount, out searchRecordCount); } } private List GetGrouped(Guid userId, SortedColumn sortedColumn, int startIndex, int pageSize, string searchString, out int totalRecordCount, out int searchRecordCount) { totalRecordCount = searchRecordCount = 0; if (userId == Guid.Empty) return new List(); if (sortedColumn == null) sortedColumn = new SortedColumn("Name", "asc"); //if (true || sortedColumn.PropertyName == "Deadline" || // sortedColumn.PropertyName == "ProjectNumber" || // sortedColumn.PropertyName == "ProjectName" || // sortedColumn.PropertyName == "Priority" || // sortedColumn.PropertyName == "Classification" || // sortedColumn.PropertyName == "Status") { var columnName = "Name"; var direction = SortingDirection.Ascending; if (sortedColumn.PropertyName == "Priority") { columnName = "Priority"; direction = sortedColumn.Direction; } if (sortedColumn.PropertyName == "Classification") { columnName = "TypeName"; direction = sortedColumn.Direction; } if (sortedColumn.PropertyName == "Status") { columnName = "StatusName"; direction = sortedColumn.Direction; } if (sortedColumn.PropertyName.Equals("ProjectName", StringComparison.InvariantCultureIgnoreCase)) { columnName = "Name"; direction = sortedColumn.Direction; } if (sortedColumn.PropertyName.Equals("ProjectNumber", StringComparison.InvariantCultureIgnoreCase)) { columnName = "ProjectNumber"; direction = sortedColumn.Direction; } if (sortedColumn.PropertyName.Equals("Deadline", StringComparison.InvariantCultureIgnoreCase)) { columnName = "Deadline"; direction = sortedColumn.Direction; } if (sortedColumn.PropertyName.Equals("ActiveScenario", StringComparison.InvariantCultureIgnoreCase)) { columnName = "ActiveScenarioName"; direction = sortedColumn.Direction; } var projectsWithParts = GetProjectsWithPassThroughSortingGroupped(userId, columnName, direction == SortingDirection.Ascending ? "asc" : "desc", searchString, startIndex, pageSize, out totalRecordCount, out searchRecordCount); var parentsIds = projectsWithParts.Select(x => x.ProjectId).Distinct().ToList(); var partsIds = projectsWithParts.Where(x => x.PartId.HasValue).Select(x => x.PartId.Value).ToList(); var projectsIds = parentsIds.Union(partsIds); var projects = DbContext.VW_ProjectAccessByUser.Where(x => x.UserId == userId && projectsIds.Contains(x.Id)).ToList(); var projectsList = new List(); switch (sortedColumn.PropertyName) { case "Teams": if (sortedColumn.Direction == SortingDirection.Ascending) projectsWithParts = projectsWithParts.OrderBy(x => x.TeamName).ToList(); else projectsWithParts = projectsWithParts.OrderByDescending(x => x.TeamName).ToList(); break; } foreach (var item1 in projectsWithParts.GroupBy(x => x.TeamName)) { //projectsList.Add(new ProjectListModel() //{ // ProjectName = item1.Key //}); foreach (var item in item1) { var project = projects.FirstOrDefault(x => x.Id == item.ProjectId); if (project == null) continue; if (!projectsList.Any(x => x.Id == item.ProjectId && x.Rank == item.Rank && ((string.IsNullOrEmpty(x.Team) && string.IsNullOrEmpty(item.TeamName)) || x.Team.Equals(item.TeamName, StringComparison.InvariantCultureIgnoreCase)))) { #region Add Project projectsList.Add(new ProjectListModel() { Id = project.Id, ProjectId = project.Id, ProjectName = project.Name ?? string.Empty, ProjectNumber = project.ProjectNumber ?? string.Empty, Status = project.StatusName, Classification = project.TypeName, Client = project.ClientName, Company = project.CompanyName, Priority = project.Priority, WritePermissionEnabledForCurrentUser = (project.Write > 0), ActiveScenario = project.ActiveScenarioId.HasValue ? new ScenarioInProjectModel() { Id = project.ActiveScenarioId.Value, Name = project.ActiveScenarioName } : null, HasChildren = project.HasChildren, Deadline = project.Deadline, Teams = project.Teams, Team = item.TeamName ?? string.Empty, TeamId = item.TeamId ?? Guid.Empty, Rank = item.Rank, ProjectParts = new List() }); #endregion } if (!item.PartId.HasValue) continue; var partProject = projects.FirstOrDefault(x => x.Id == item.PartId.Value); if (partProject == null) continue; var parentProject = projectsList.FirstOrDefault(x => x.Id == item.ProjectId && x.Rank == item.Rank && ((string.IsNullOrEmpty(x.Team) && string.IsNullOrEmpty(item.TeamName)) || x.Team.Equals(item.TeamName, StringComparison.InvariantCultureIgnoreCase))); parentProject.ProjectParts.Add(new ProjectPartListModel() { #region Add Project Part Id = partProject.Id, ProjectId = project.Id, ProjectName = partProject.Name ?? string.Empty, ProjectNumber = partProject.ProjectNumber ?? string.Empty, Status = partProject.StatusName, Classification = partProject.TypeName, Client = partProject.ClientName, Company = partProject.CompanyName, Priority = partProject.Priority, WritePermissionEnabledForCurrentUser = (partProject.Write > 0), ActiveScenario = partProject.ActiveScenarioId.HasValue ? new ScenarioInProjectModel() { Id = partProject.ActiveScenarioId.Value, Name = partProject.ActiveScenarioName } : null, PartNum = partProject.PartNum, Deadline=partProject.Deadline, Teams = partProject.Teams, Team = item.TeamName ?? string.Empty, TeamId = item.TeamId ?? Guid.Empty, #endregion }); } } return projectsList; } } private List GetUnGrouped(Guid userId, SortedColumn sortedColumn, int startIndex, int pageSize, string searchString, out int totalRecordCount, out int searchRecordCount) { totalRecordCount = searchRecordCount = 0; if (userId == Guid.Empty) return new List(); if (sortedColumn == null) sortedColumn = new SortedColumn("ProjectName", "asc"); if (sortedColumn.PropertyName == "Priority" || sortedColumn.PropertyName == "Classification" || sortedColumn.PropertyName == "Status" || sortedColumn.PropertyName == "ActiveScenario") { var columnName = string.Empty; if (sortedColumn.PropertyName == "Priority") { columnName = "Priority"; } if (sortedColumn.PropertyName == "Classification") { columnName = "TypeName"; } if (sortedColumn.PropertyName == "Status") { columnName = "StatusName"; } if (sortedColumn.PropertyName == "ActiveScenario") { columnName = "ActiveScenarioName"; } var projectsWithParts = GetProjectsWithPassThroughSorting(userId, columnName, sortedColumn.Direction == SortingDirection.Ascending ? "asc" : "desc", searchString, startIndex, pageSize, out totalRecordCount, out searchRecordCount); var parentsIds = projectsWithParts.Select(x => x.ProjectId).Distinct().ToList(); var partsIds = projectsWithParts.Where(x => x.PartId.HasValue).Select(x => x.PartId.Value).ToList(); var projectsIds = parentsIds.Union(partsIds); var projects = DbContext.VW_ProjectAccessByUser.Where(x => x.UserId == userId && projectsIds.Contains(x.Id)).ToList(); var projectsList = new List(); foreach (var item in projectsWithParts.Where(x => x.ProjectId != null)) { var project = projects.FirstOrDefault(x => x.Id == item.ProjectId); if (project == null) continue; if (!projectsList.Any(x => x.Id == item.ProjectId && x.Rank == item.Rank)) { projectsList.Add(new ProjectListModel() { Id = project.Id, ProjectId = project.Id, ProjectName = project.Name ?? string.Empty, ProjectNumber = project.ProjectNumber ?? string.Empty, Status = project.StatusName, Classification = project.TypeName, Client = project.ClientName, Company = project.CompanyName, Priority = project.Priority, WritePermissionEnabledForCurrentUser = (project.Write > 0), ActiveScenario = project.ActiveScenarioId.HasValue ? new ScenarioInProjectModel() { Id = project.ActiveScenarioId.Value, Name = project.ActiveScenarioName } : null, HasChildren = project.HasChildren, Deadline = project.Deadline, Teams = project.Teams, Team = item.TeamName, Rank = item.Rank, ProjectParts = new List() }); } if (!item.PartId.HasValue) continue; var partProject = projects.FirstOrDefault(x => x.Id == item.PartId.Value); if (partProject == null) continue; var parentProject = projectsList.FirstOrDefault(x => x.Id == item.ProjectId && x.Rank == item.Rank); parentProject.ProjectParts.Add(new ProjectPartListModel() { Id = partProject.Id, ProjectId = project.Id, ProjectName = partProject.Name ?? string.Empty, ProjectNumber = partProject.ProjectNumber ?? string.Empty, Status = partProject.StatusName, Classification = partProject.TypeName, Client = partProject.ClientName, Company = partProject.CompanyName, Priority = partProject.Priority, WritePermissionEnabledForCurrentUser = (partProject.Write > 0), ActiveScenario = partProject.ActiveScenarioId.HasValue ? new ScenarioInProjectModel() { Id = partProject.ActiveScenarioId.Value, Name = partProject.ActiveScenarioName } : null, PartNum = partProject.PartNum, Deadline = partProject.Deadline, Teams = partProject.Teams, Team = item.TeamName }); } return projectsList; } else { var query = from p in DbContext.VW_ProjectAccessByUser join parts in DbContext.VW_ProjectAccessByUser on p.Id equals parts.ParentProjectId into projectParts where (p.ParentProjectId == null) && (p.UserId.Equals(userId)) select new ProjectListModel() { Id = p.Id, ProjectId = p.Id, ProjectName = p.Name ?? string.Empty, ProjectNumber = p.ProjectNumber ?? string.Empty, Status = p.StatusName, Classification = p.TypeName, Client = p.ClientName, Company = p.CompanyName, Priority = p.Priority, WritePermissionEnabledForCurrentUser = (p.Write > 0), ActiveScenario = p.ActiveScenarioId.HasValue ? new ScenarioInProjectModel() { Id = p.ActiveScenarioId.Value, Name = p.ActiveScenarioName } : null, HasChildren = p.HasChildren, Deadline = p.Deadline, Teams = p.Teams, ProjectParts = projectParts.Where(pp => pp.UserId.Equals(userId) && (searchString == null || searchString == "" || p.Name.Contains(searchString) || p.ProjectNumber.Contains(searchString) || p.ActiveScenarioName.Contains(searchString) || pp.Name.Contains(searchString) || pp.ActiveScenarioName.Contains(searchString) || pp.Teams.Contains(searchString))) .Select(pp => new ProjectPartListModel() { Id = pp.Id, ProjectId = p.Id, ProjectName = pp.Name ?? string.Empty, ProjectNumber = pp.ProjectNumber ?? string.Empty, Status = pp.StatusName, Classification = pp.TypeName, Client = pp.ClientName, Company = pp.CompanyName, Priority = pp.Priority, Deadline=pp.Deadline, WritePermissionEnabledForCurrentUser = (pp.Write > 0), ActiveScenario = pp.ActiveScenarioId.HasValue ? new ScenarioInProjectModel() { Id = pp.ActiveScenarioId.Value, Name = pp.ActiveScenarioName } : null, PartNum = pp.PartNum, Teams = pp.Teams } ).ToList() }; //filter if (!string.IsNullOrWhiteSpace(searchString)) { string searchStringLow = searchString.ToLower(); query = query.Where(c => c.ProjectName.ToLower().Contains(searchStringLow) || c.ProjectNumber.ToLower().Contains(searchStringLow) || c.ActiveScenario.Name.ToLower().Contains(searchStringLow) || c.Teams.Contains(searchStringLow) || c.ProjectParts.Any(pp => pp.ProjectName.Contains(searchStringLow) || pp.ActiveScenario.Name.Contains(searchStringLow) || pp.Teams.Contains(searchStringLow))); } //sort switch (sortedColumn.PropertyName) { case "ProjectNumber": if (sortedColumn.Direction == SortingDirection.Ascending) query = query.OrderBy(p => p.ProjectNumber); else query = query.OrderByDescending(p => p.ProjectNumber); break; case "Deadline": if (sortedColumn.Direction == SortingDirection.Ascending) query = query.OrderBy(p => p.Deadline); else query = query.OrderByDescending(p => p.Deadline); break; case "Teams": if (sortedColumn.Direction == SortingDirection.Ascending) query = query.OrderBy(x => x.Teams); else query = query.OrderByDescending(x => x.Teams); break; default: if (sortedColumn.Direction == SortingDirection.Ascending) query = query.OrderBy(p => p.ProjectName); else query = query.OrderByDescending(p => p.ProjectName); break; } totalRecordCount = DbContext.VW_ProjectAccessByUser .Where(x => x.UserId.Equals(userId) && !x.ParentProjectId.HasValue).Count(); searchRecordCount = query.Count(); var projectsToDisplay = query.Skip(startIndex).Take(pageSize).ToList(); switch (sortedColumn.PropertyName) { case "ProjectNumber": if (sortedColumn.Direction == SortingDirection.Ascending) projectsToDisplay.ForEach(p => p.ProjectParts = p.ProjectParts.OrderBy(pp => pp.ProjectNumber).ToList()); else projectsToDisplay.ForEach(p => p.ProjectParts = p.ProjectParts.OrderByDescending(pp => pp.ProjectNumber).ToList()); break; case "Teams": if (sortedColumn.Direction == SortingDirection.Ascending) projectsToDisplay.ForEach(p => p.ProjectParts = p.ProjectParts.OrderBy(pp => pp.Teams).ToList()); else projectsToDisplay.ForEach(p => p.ProjectParts = p.ProjectParts.OrderByDescending(pp => pp.Teams).ToList()); break; default: if (sortedColumn.Direction == SortingDirection.Ascending) projectsToDisplay.ForEach(p => p.ProjectParts = p.ProjectParts.OrderBy(pp => pp.ProjectName).ToList()); else projectsToDisplay.ForEach(p => p.ProjectParts = p.ProjectParts.OrderByDescending(pp => pp.ProjectName).ToList()); break; } return projectsToDisplay; } } private List GetProjectsWithPassThroughSorting(Guid userId, string columnName, string sort, string searchString, int startIndex, int pageSize, out int totalRecordCount, out int searchRecordCount) { var query = string.Format( @"; DECLARE @T TABLE ( ProjectId uniqueidentifier NOT NULL , PartId uniqueidentifier NULL , Part{0} {2} NOT NULL , [Row] int NOT NULL ) DECLARE @Result TABLE ( ProjectId uniqueidentifier NOT NULL , PartId uniqueidentifier NULL , [Rank] int NOT NULL ) INSERT INTO @T (ProjectId, PartId, Part{0}, [Row]) SELECT p.Id AS ProjectId , c.Id AS PartId , ISNULL(c.{0}, p.{0}) AS Part{0} , ROW_NUMBER() OVER ( ORDER BY ISNULL(c.{0}, p.{0}) {1}, p.Id ASC ) AS [Row] FROM dbo.VW_ProjectAccessByUser p LEFT JOIN dbo.VW_ProjectAccessByUser c ON c.ParentProjectId = p.Id AND c.UserId = @UserId WHERE p.ParentProjectId IS NULL AND p.UserId = @UserId AND ( @SearchString IS NULL OR p.Name LIKE '%' + @SearchString + '%' OR p.ProjectNumber LIKE '%' + @SearchString + '%' OR p.ActiveScenarioName LIKE '%' + @SearchString + '%' OR p.Teams LIKE '%' + @SearchString + '%' OR c.Name LIKE '%' + @SearchString + '%' OR c.ActiveScenarioName LIKE '%' + @SearchString + '%' OR c.Teams LIKE '%' + @SearchString + '%' ) INSERT INTO @Result (ProjectId, PartId, [Rank]) SELECT Result.ProjectId , Result.PartId , Result.[Rank] FROM ( SELECT Result.ProjectId , Result.PartId , DENSE_RANK() OVER ( ORDER BY Result.[Rank] ) AS [Rank] FROM ( SELECT T.ProjectId , T.PartId , T.Part{0} , ( SELECT TOP 1 ISNULL(InnerT.[Row], 0) + 1 FROM @T AS InnerT WHERE InnerT.[Row] < T.[Row] AND InnerT.[ProjectId] <> T.ProjectId ORDER BY InnerT.[Row] DESC ) AS [Rank] FROM @T T ) AS Result ) AS Result SELECT r.ProjectId , r.PartId , r.[Rank] FROM @Result r WHERE r.[Rank] BETWEEN @StartIndex AND @EndIndex SELECT @TotalRecordCount = ISNULL(MAX([Rank]), 0) from @Result SELECT @SearchRecordCount = ISNULL(MAX([Rank]), 0) from @Result", columnName, sort, columnName == "Priority" ? "int" : "nvarchar(1600)"); var totalRecordCountParam = new SqlParameter("@TotalRecordCount", SqlDbType.Int); totalRecordCountParam.Direction = System.Data.ParameterDirection.Output; var searchRecordCountParam = new SqlParameter("@SearchRecordCount", SqlDbType.Int); searchRecordCountParam.Direction = System.Data.ParameterDirection.Output; var projects = DbContext.Database.SqlQuery(query, new SqlParameter("@UserId", userId), new SqlParameter("@StartIndex", startIndex + 1), new SqlParameter("@EndIndex", startIndex + pageSize), new SqlParameter("@SearchString", searchString), totalRecordCountParam, searchRecordCountParam) .ToList(); totalRecordCount = (int)totalRecordCountParam.Value; searchRecordCount = (int)searchRecordCountParam.Value; return projects; } private List GetProjectsWithPassThroughSortingGroupped(Guid userId, string columnName, string sort, string searchString, int startIndex, int pageSize, out int totalRecordCount, out int searchRecordCount) { var query = string.Format( @"; DECLARE @T TABLE ( ProjectId uniqueidentifier NOT NULL , PartId uniqueidentifier NULL , Part{0} {2} NULL , TeamName nvarchar(255) NULL , TeamId uniqueidentifier NULL , [Row] int NOT NULL ) DECLARE @Result TABLE ( ProjectId uniqueidentifier NOT NULL , PartId uniqueidentifier NULL , TeamName nvarchar(255) NULL , TeamId uniqueidentifier NULL , [Rank] int NOT NULL ) INSERT INTO @T (ProjectId, PartId, Part{0}, TeamName,TeamId, [Row]) SELECT p.Id AS ProjectId , c.Id AS PartId , ISNULL(c.{0}, p.{0}) AS Part{0} , ISNULL(t_part.Name, t.Name) AS TeamName, ISNULL(t_part.Id, t.Id) AS TeamId, ROW_NUMBER() OVER ( ORDER BY ISNULL(c.{0}, p.{0}) {1}, p.Id ASC ) AS [Row] FROM dbo.VW_ProjectAccessByUser p LEFT JOIN dbo.Team2Project t2p ON t2p.ProjectId = p.Id LEFT JOIN dbo.Team t ON t2p.TeamId = t.Id LEFT JOIN dbo.VW_ProjectAccessByUser c ON c.ParentProjectId = p.Id AND c.UserId = @UserId LEFT JOIN dbo.Team2Project t2p_part ON t2p_part.ProjectId = c.Id LEFT JOIN dbo.Team t_part ON t2p_part.TeamId = t_part.Id WHERE p.ParentProjectId IS NULL AND p.UserId = @UserId AND ( @SearchString IS NULL OR p.Name LIKE '%' + @SearchString + '%' OR p.ProjectNumber LIKE '%' + @SearchString + '%' OR p.ActiveScenarioName LIKE '%' + @SearchString + '%' OR p.Teams LIKE '%' + @SearchString + '%' OR c.Name LIKE '%' + @SearchString + '%' OR c.ActiveScenarioName LIKE '%' + @SearchString + '%' OR t_part.Name LIKE '%' + @SearchString + '%' OR t.Name LIKE '%' + @SearchString + '%' ) group by ISNULL(t_part.Name, t.Name), p.Id, c.Id, isnull(c.{0}, p.{0}), ISNULL(t_part.Id, t.Id) INSERT INTO @Result (ProjectId, PartId, TeamName, TeamId, [Rank]) SELECT Result.ProjectId , Result.PartId , Result.TeamName , Result.TeamId , Result.[Rank] FROM ( SELECT Result.ProjectId , Result.PartId , Result.TeamName , Result.TeamId , DENSE_RANK() OVER ( ORDER BY Result.[Rank] ) AS [Rank] FROM ( SELECT T.ProjectId , T.PartId , T.TeamName , T.TeamId , T.Part{0} , ( SELECT TOP 1 ISNULL(InnerT.[Row], 0) + 1 FROM @T AS InnerT WHERE InnerT.[Row] < T.[Row] AND InnerT.[ProjectId] <> T.ProjectId ORDER BY InnerT.[Row] DESC ) AS [Rank] FROM @T T ) AS Result ) AS Result SELECT r.ProjectId , r.PartId , r.TeamName , r.TeamId , r.[Rank] FROM @Result r WHERE r.[Rank] BETWEEN @StartIndex AND @EndIndex SELECT @TotalRecordCount = ISNULL(MAX([Rank]), 0) from @Result SELECT @SearchRecordCount = ISNULL(MAX([Rank]), 0) from @Result", columnName, sort, columnName == "Priority" ? "int" : "nvarchar(1600)"); var totalRecordCountParam = new SqlParameter("@TotalRecordCount", SqlDbType.Int); totalRecordCountParam.Direction = System.Data.ParameterDirection.Output; var searchRecordCountParam = new SqlParameter("@SearchRecordCount", SqlDbType.Int); searchRecordCountParam.Direction = System.Data.ParameterDirection.Output; var projects = DbContext.Database.SqlQuery(query, new SqlParameter("@UserId", userId), new SqlParameter("@StartIndex", startIndex + 1), new SqlParameter("@EndIndex", startIndex + pageSize), new SqlParameter("@SearchString", searchString), totalRecordCountParam, searchRecordCountParam) .ToList(); totalRecordCount = (int)totalRecordCountParam.Value; searchRecordCount = (int)searchRecordCountParam.Value; return projects; } private class Project2PartModel { public Guid ProjectId { get; set; } public Guid? PartId { get; set; } public string TeamName { get; set; } public Guid? TeamId { get; set; } public int Rank { get; set; } } } public class ProjectPartManager : ManagerBase { public ProjectPartManager(EnVisageEntities dbContext) : base(dbContext) { } protected override Project InitInstance() { return new Project { Id = Guid.NewGuid() }; } protected override Project RetrieveReadOnlyById(Guid key) { return DataTable.AsNoTracking().FirstOrDefault(t => t.Id == key); } public override DbSet DataTable { get { return DbContext.Projects; } } public override Project Save(ProjectPartModel model) { var obj = base.Save(model); (new ProjectManager(DbContext)).SaveContacts(obj, model.InternalContacts, model.ExternalContacts); (new ProjectManager(DbContext)).SaveTeams(obj, model.AssignedTeams); (new ProjectManager(DbContext)).SaveStrategicGoals(obj, model.StrategicGoals); if (model.Id == Guid.Empty) { #region Create Actuals scenario if (!model.SaveAsCopy) { var scenario = new Scenario { Id = Guid.NewGuid(), Name = "ACTUALS", ParentId = obj.Id, Type = ScenarioType.Actuals.GetHashCode(), StartDate = DateTime.Now, Color = "", ProjectedRevenue = 0 }; DbContext.Scenarios.Add(scenario); } #endregion #region Copy scenarios and referenced scenario details if (model.SaveAsCopy) { var oldId = Guid.Empty; var scenarios = DbContext.Scenarios.Where(t => t.ParentId == oldId).ToList(); foreach (var currScenario in scenarios) { var currDetails = DbContext.ScenarioDetail.Where( t => t.ParentID == currScenario.Id).AsNoTracking().ToList().OrderBy(t => t.ExpenditureCategoryId).ThenBy(t => t.WeekOrdinal); var newScenario = new Scenario { Id = Guid.NewGuid(), ParentId = obj.Id, Type = currScenario.Type, Name = currScenario.Name, ProjectedRevenue = currScenario.ProjectedRevenue, ExpectedGrossMargin = currScenario.ExpectedGrossMargin, CalculatedGrossMargin = currScenario.CalculatedGrossMargin, CGSplit = currScenario.CGSplit, EFXSplit = currScenario.EFXSplit, Duration = currScenario.Duration, TDDirectCosts = currScenario.TDDirectCosts, BUDirectCosts = currScenario.BUDirectCosts, Shots = currScenario.Shots, TDRevenueShot = currScenario.TDRevenueShot, BURevenueShot = currScenario.BURevenueShot, LastUpdate = DateTime.Now, Status = currScenario.Status, UseLMMargin = currScenario.UseLMMargin, ExpectedGrossMargin_LM = currScenario.ExpectedGrossMargin_LM, CalculatedGrossMargin_LM = currScenario.CalculatedGrossMargin_LM, TDDirectCosts_LM = currScenario.TDDirectCosts_LM, BUDirectCosts_LM = currScenario.BUDirectCosts_LM, BURevenueShot_LM = currScenario.BURevenueShot_LM, EntryTimeStamp = DateTime.Now, Actuals_BUDirectCosts = currScenario.Actuals_BUDirectCosts, Actuals_BUDirectCosts_LM = currScenario.Actuals_BUDirectCosts_LM, FreezeRevenue = currScenario.FreezeRevenue, GrowthScenario = currScenario.GrowthScenario, TemplateId = currScenario.TemplateId, Color = currScenario.Color, ProjectedExpense = currScenario.ProjectedExpense, StartDate = currScenario.StartDate, EndDate = currScenario.EndDate, ShotStartDate = currScenario.ShotStartDate, SystemAttributeObjectID = currScenario.SystemAttributeObjectID }; DbContext.Scenarios.Add(newScenario); foreach (var detail in currDetails) { var newDetailItem = new ScenarioDetail { Id = Guid.NewGuid(), ExpenditureCategoryId = detail.ExpenditureCategoryId, ParentID = newScenario.Id, Quantity = detail.Quantity, Cost = detail.Cost, WeekOrdinal = detail.WeekOrdinal, WeekEndingDate = detail.WeekEndingDate, LastUpdate = DateTime.Now }; DbContext.ScenarioDetail.Add(newDetailItem); } } } #endregion } return obj; } } }