using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using EnVisage.Models;
using System.Text;
using System.Data.Entity.Infrastructure;

namespace EnVisage.Code.BLL
{
    public class TeamManager : ManagerBase<Team, TeamModel>
    {
        public TeamManager(EnVisageEntities dbContext)
            : base(dbContext)
        {
        }

        protected override Team InitInstance()
        {
            return new Team { Id = Guid.NewGuid() };
        }

        protected override Team RetrieveReadOnlyById(Guid key)
        {
            return DataTable.AsNoTracking().FirstOrDefault(t => t.Id == key);
        }

        public override DbSet<Team> DataTable
        {
            get
            {
                return DbContext.Teams;
            }
        }

        public IQueryable<Team> LoadTeams(IEnumerable<Guid> teams)
        {
            if (teams == null)
                throw new ArgumentNullException("teams");

            var result = DbContext.Teams.Where(x => teams.Contains(x.Id));
            return result;
        }

        public TeamModel LoadTeamModel(Guid teamId)
        {
            if (teamId == Guid.Empty)
                return null;

            return TeamModelBasicQuery.FirstOrDefault(x => x.Id == teamId);
        }
        public void Delete(Guid teamId)
        {
            var nptManager = new NonProjectTimeManager(this.DbContext);
            var dbObj = this.Load(teamId, false);
            var npts = dbObj.NonProjectTime2Team.ToList();
            foreach (var npt in npts)
            {
                nptManager.DeleteNPT(npt.NonProjectTimeId, null);
            }


            var capacityScenarioId = dbObj.PlannedCapacityScenarioId;
            var actualCapacityScenarioId = dbObj.ActualCapacityScenarioId;
            DbContext.User2Team.RemoveRange(DbContext.User2Team.Where(c2s => c2s.TeamId == dbObj.Id));
            DbContext.Team2Project.RemoveRange(DbContext.Team2Project.Where(x => x.TeamId == dbObj.Id));
            DbContext.Team2View.RemoveRange(DbContext.Team2View.Where(tv => tv.TeamId == dbObj.Id));
            DbContext.TeamAllocations.RemoveRange(DbContext.TeamAllocations.Where(x => x.TeamId == dbObj.Id));
            DbContext.Holiday2Team.RemoveRange(dbObj.Holiday2Team);


            DbContext.Teams.Remove(dbObj);
            DbContext.SaveChanges();
            if (capacityScenarioId != null)
                (DbContext as IObjectContextAdapter).ObjectContext.ExecuteStoreCommand(string.Format("exec sp_DeleteScenario '{0}'", capacityScenarioId));
            if (actualCapacityScenarioId != null)
                (DbContext as IObjectContextAdapter).ObjectContext.ExecuteStoreCommand(string.Format("exec sp_DeleteScenario '{0}'", actualCapacityScenarioId));


        }
        public IList<TeamWithResourcesModel> LoadTeamsWithResourcesByUser(Guid? userId, List<Guid> teams = null)
        {
            bool filterByTeams = (teams != null);
            var query = (from ut in DbContext.VW_User2Team
                         join t in DbContext.Teams on ut.TeamId equals t.Id
                         join pr in DbContext.VW_TeamResource on t.Id equals pr.TeamId into prl
                         from p in prl.DefaultIfEmpty()
                         join exp in DbContext.ExpenditureCategory on p.ExpenditureCategoryId equals exp.Id into egr
                         from e in egr.DefaultIfEmpty()
                         where (!userId.HasValue || (ut.UserId == userId.Value))
                         orderby t.Name
                         select new
                         {
                             Team = t,
                             PeopleResource = p,
                             ExpCat = e
                         }).AsNoTracking().Distinct();

            var result = new Dictionary<Guid, TeamWithResourcesModel>();

            foreach (var item in query)
            {
                if (!filterByTeams || teams.Contains(item.Team.Id))
                {
                    if (!result.ContainsKey(item.Team.Id))
                    {
                        result.Add(item.Team.Id, (TeamWithResourcesModel)item.Team);
                    }

                    if (item.PeopleResource != null)
                        result[item.Team.Id].PeopleResources.Add(PeopleResourceModel.GetPeopleResourceModel(item.PeopleResource, item.ExpCat, item.Team));
                }
            }
            var foundTeams = result.Values.ToList();
            foundTeams.ForEach(t => t.PeopleResources = t.PeopleResources.OrderBy(r => r.FirstName + r.LastName).ToList());
            return foundTeams;
        }

        public TeamWithResourcesModel LoadTeamWithResourcesById(Guid teamId)
        {
            if (Guid.Empty.Equals(teamId))
                return null;

            var query = (from t in DbContext.Teams
                         join pr in DbContext.VW_TeamResource on t.Id equals pr.TeamId into prl
                         from p in prl.DefaultIfEmpty()
                         join exp in DbContext.ExpenditureCategory on p.ExpenditureCategoryId equals exp.Id into egr
                         from e in egr.DefaultIfEmpty()
                         where t.Id == teamId
                         orderby t.Name
                         select new
                         {
                             Team = t,
                             PeopleResource = p,
                             ExpCat = e
                         }).AsNoTracking().Distinct();
            TeamWithResourcesModel team = null;
            foreach (var item in query)
            {
                if (team == null)
                {
                    team = (TeamWithResourcesModel)item.Team;
                }
                if (item.PeopleResource != null)
                    team.PeopleResources.Add(PeopleResourceModel.GetPeopleResourceModel(item.PeopleResource, item.ExpCat, item.Team));
            }
            team.PeopleResources.OrderBy(r => r.FirstName + r.LastName);
            return team;
        }
        /// <summary>
        /// Loads all teams for the specified list of actual capacity scenarios. Each Team will be prefilled with resources of specified Expenditure Category.
        /// </summary>
        /// <param name="expCatIds">A list of Expenditure Category Ids.</param>
        /// <param name="actualCapacityScenarioIds">A list of Sceanrio Ids.</param>
        /// <returns></returns>
        public List<TeamWithResourcesModel> LoadTeamsWithResourcesByActualCapacityScenarios(List<Guid> expCatIds, List<Guid> actualCapacityScenarioIds)
        {
            if (actualCapacityScenarioIds == null || actualCapacityScenarioIds.Count == 0)
                return null;

            var query = (from t in DbContext.Teams
                         join pr in DbContext.VW_TeamResource on t.Id equals pr.TeamId into prl
                         from p in prl.DefaultIfEmpty()
                         join exp in DbContext.ExpenditureCategory on p.ExpenditureCategoryId equals exp.Id into egr
                         from e in egr.DefaultIfEmpty()
                         where t.ActualCapacityScenarioId.HasValue && actualCapacityScenarioIds.Contains(t.ActualCapacityScenarioId.Value)
                         orderby t.Name
                         select new
                         {
                             Team = t,
                             PeopleResource = p,
                             ExpCat = e
                         }).AsNoTracking().Distinct();
            var result = new Dictionary<Guid, TeamWithResourcesModel>();
            foreach (var item in query)
            {
                if (!result.ContainsKey(item.Team.Id))
                {
                    result.Add(item.Team.Id, (TeamWithResourcesModel)item.Team);
                }
                if (item.PeopleResource != null)
                    result[item.Team.Id].PeopleResources.Add(PeopleResourceModel.GetPeopleResourceModel(item.PeopleResource, item.ExpCat, item.Team));
            }
            var teams = result.Values.ToList();
            teams.ForEach(t => t.PeopleResources = t.PeopleResources.OrderBy(r => r.FirstName + r.LastName).ToList());
            return teams;
        }


        public override Team Save(TeamModel model)
        {
            if (model == null)
                throw new ArgumentNullException("model");

            return Save(model, model.Id == Guid.Empty);
        }

        public Team Save(TeamModel model, bool saveAsNew)
        {
            Team team = null;
            if (saveAsNew && !DbContext.Teams.Any(x => x.Id == model.Id))
            {
                team = new Team()
                {
                    Id = model.Id
                };
                model.CopyTo(team);

                var capScen = DbContext.Scenarios.Create();
                capScen.Type = (int)ScenarioType.TeamPlannedCapacity;
                capScen.Name = model.Name + " Planned Capacity";
                capScen.Id = Guid.NewGuid();
                team.PlannedCapacityScenarioId = capScen.Id;
                var actCapScen = DbContext.Scenarios.Create();
                actCapScen.Type = (int)ScenarioType.TeamActualCapacity;
                actCapScen.Name = model.Name + " Actual Capacity";
                actCapScen.Id = Guid.NewGuid();
                team.ActualCapacityScenarioId = actCapScen.Id;

                if (team.Id == Guid.Empty)
                    team.Id = Guid.NewGuid();

                DbContext.Teams.Add(team);
                DbContext.Scenarios.Add(capScen);
                DbContext.Scenarios.Add(actCapScen);

                if (model.UserId != null)
                {
                    foreach (var userId in model.UserId)
                    {
                        DbContext.User2Team.Add(new User2Team
                        {
                            Id = Guid.NewGuid(),
                            TeamId = team.Id,
                            UserId = userId.ToString()
                        });
                    }
                }
                if (model.WorkFlowContacts != null)
                {
                    (new WorkFlowManager(this.DbContext)).SaveContacts(model.WorkFlowContacts.ToList(), null, team.Id, WorkFlowContactNotificationType.None);
                }
                if (model.NotificationContacts != null && model.NotificationWorkFlowStates != null)
                {
                    (new WorkFlowManager(this.DbContext)).SaveContacts(model.NotificationContacts.ToList(), model.NotificationWorkFlowStates.ToList(), team.Id, WorkFlowContactNotificationType.TeamScenarioAdd);
                }
            }
            else
            {
                team = base.Save(model);
                var projectsWithParents = (from c in DbContext.Team2Project where c.TeamId == model.Id select new { c.ProjectId, c.Project.ParentProjectId }).Distinct().ToList();
                var projects = projectsWithParents.Select(x => x.ProjectId).Distinct().ToList();
                if (projectsWithParents.Any(x => x.ParentProjectId.HasValue))
                    projects.AddRange(projectsWithParents.Where(x => x.ParentProjectId.HasValue).Select(x => x.ParentProjectId.Value).Distinct());
                var parts = DbContext.Projects.Where(x => x.ParentProjectId.HasValue && projects.Contains(x.ParentProjectId.Value)).Select(x => x.Id).ToList();
                projects.AddRange(parts.Where(x => !projects.Contains(x)));

                var users = DbContext.User2Team.Where(x => x.TeamId == model.Id).ToList();
                var users2remove = users.Where(x => model.UserId == null || !model.UserId.Contains(Guid.Parse(x.UserId))).Select(x => x.UserId).ToList();

                users.Where(x => model.UserId == null || !model.UserId.Contains(Guid.Parse(x.UserId))).ToList().
                    ForEach(x => DbContext.Entry(x).State = EntityState.Deleted);
                if (model.UserId != null)
                {
                    foreach (var userId in model.UserId.Where(c => users.All(u => !c.ToString().Equals(u.UserId, StringComparison.InvariantCultureIgnoreCase))))
                    {
                        DbContext.User2Team.Add(new User2Team
                        {
                            Id = Guid.NewGuid(),
                            TeamId = team.Id,
                            UserId = userId.ToString()
                        });
                    }
                }
                if (model.WorkFlowContacts != null)
                {
                    (new WorkFlowManager(this.DbContext)).SaveContacts(model.WorkFlowContacts.ToList(), null, team.Id, WorkFlowContactNotificationType.None);
                }
                if (model.NotificationContacts != null && model.ProjectAddNotification)
                {
                    if (model.NotificationWorkFlowStates == null)
                        model.NotificationWorkFlowStates = new List<string>();
                    (new WorkFlowManager(this.DbContext)).SaveContacts(model.NotificationContacts.ToList(), model.NotificationWorkFlowStates.ToList(), team.Id, WorkFlowContactNotificationType.TeamScenarioAdd);
                }
                foreach (var project in projects.Distinct())
                {
                    var projectUsers = (from c in DbContext.ProjectAccesses where c.ProjectId == project select c.PrincipalId).Distinct().ToList();
                    DbContext.ProjectAccesses.RemoveRange(DbContext.ProjectAccesses.Where(x => x.ProjectId == project && users2remove.Contains(x.PrincipalId.ToString())).ToList());
                    if (model.UserId != null)
                        foreach (var userId in (from c in model.UserId where !projectUsers.Contains(c) select c).Distinct().ToList())
                        {
                            DbContext.ProjectAccesses.Add(new ProjectAccess()
                            {
                                PrincipalId = userId,
                                ProjectId = project,
                                Read = 1,
                                Write = 1
                            });
                        }
                }
            }

            if (IsContextLocal)
                DbContext.SaveChanges();

            return team;
        }

        /// <summary>
        /// Returns team list, which contains specified expenditure category. For Super EC
        /// returns all teams, available to user
        /// </summary>
        /// <param name="userId">Visible for this user</param>
        /// <param name="expCatId">Category to filter teams</param>
        /// <returns>Query to get teams</returns>
        public IQueryable<Team> GetTeamsByExpenditureCategory(Guid expCatId, Guid? userId)
        {
            if (expCatId.Equals(Guid.Empty))
                throw new ArgumentNullException("expCatId");

            // Get info about EC
            var eManager = new ExpenditureCategoryManager(this.DbContext);
            var expCatItem = eManager.Load(expCatId, true);
            IQueryable<Team> teams = null;

            if (expCatItem == null)
                throw new Exception(String.Format("Expenditure Category (Id={0}) not found", expCatId.ToString()));

            if (expCatItem.AllowResourceAssignment)
            {
                // EC is ordinary
                IQueryable<Team> teamsP =
                    from Team t in DbContext.Teams
                    join VW_ExpCategoriesInScenario vv in DbContext.VW_ExpCategoriesInScenario
                        on t.PlannedCapacityScenarioId equals vv.ScenarioID
                    where (vv.Id == expCatId)
                    select t;

                IQueryable<Team> teamsA =
                    from Team t in DbContext.Teams
                    join VW_ExpCategoriesInScenario vv in DbContext.VW_ExpCategoriesInScenario
                        on t.ActualCapacityScenarioId equals vv.ScenarioID
                    where (vv.Id == expCatId)
                    select t;

                teams = teamsP.Union(teamsA);
            }
            else
            {
                // EC is Super EC. Return all teams
                teams = DbContext.Teams;
            }

            if (userId.HasValue)
            {
                string userIdAsText = userId.Value.ToString();

                teams.Join(DbContext.User2Team.Where(x => x.UserId.Equals(userIdAsText)),
                    k => k.Id, l => l.TeamId, (k, l) => k);
            }

            return teams.Distinct();
        }

        public Guid?[] GetPlannedCapacityCategoriesIds(Guid teamId)
        {
            return (from sd in DbContext.ScenarioDetail
                    join s in DbContext.Scenarios on sd.ParentID equals s.Id
                    join t in DbContext.Teams on s.Id equals t.PlannedCapacityScenarioId
                    where t.Id == teamId
                    select sd.ExpenditureCategoryId).Distinct().ToArray();
        }

        public List<TeamAllocation> GetTeamsAllocation(IEnumerable<Guid> teams, IEnumerable<Guid> scenarios, IEnumerable<Guid> expenditureCategories, DateTime? startDate, DateTime? endDate)
        {
            if (teams == null || !teams.Any())
                return new List<TeamAllocation>();

            // TODO: add ability to filter with partial week (need to join to Fiscal Calendar and filter data by Start/End date of fiscal week instead of allocation.WeekEnding, see FiscalCalendarManager.FilterWeeks method)
            var teamsAllocation = DbContext.TeamAllocations.Where(x => teams.Contains(x.TeamId));
            if (scenarios != null && scenarios.Any())
                teamsAllocation = teamsAllocation.Where(x => scenarios.Contains(x.ScenarioId));

            if (expenditureCategories != null && expenditureCategories.Any())
                teamsAllocation = teamsAllocation.Where(x => expenditureCategories.Contains(x.ExpenditureCategoryId));

            if (startDate.HasValue)
                teamsAllocation = teamsAllocation.Where(x => x.WeekEndingDate >= startDate.Value);

            if (endDate.HasValue)
                teamsAllocation = teamsAllocation.Where(x => x.WeekEndingDate <= endDate.Value);

            return teamsAllocation.ToList();
        }
        public List<TeamAllocation> GetTeamsAllocation(Guid scenarioId, DateTime? startDate = null, DateTime? endDate = null, bool readOnly = false)
        {
            if (scenarioId == Guid.Empty)
                return new List<TeamAllocation>();

            return GetTeamsAllocation(new List<Guid> { scenarioId }, startDate, endDate, readOnly);
        }
        public List<TeamAllocation> GetTeamsAllocation(IEnumerable<Guid> scenarios, DateTime? startDate = null, DateTime? endDate = null, bool readOnly = false)
        {
            // TODO: add ability to filter with partial week (need to join to Fiscal Calendar and filter data by Start/End date of fiscal week instead of allocation.WeekEnding, see FiscalCalendarManager.FilterWeeks method)
            var taQuery = DbContext.TeamAllocations.AsQueryable();
            if (readOnly)
                taQuery = taQuery.AsNoTracking();

            if (scenarios != null && scenarios.Any())
                taQuery = taQuery.Where(x => scenarios.Contains(x.ScenarioId));

            if (startDate.HasValue)
                taQuery = taQuery.Where(x => x.WeekEndingDate >= startDate.Value);

            if (endDate.HasValue)
                taQuery = taQuery.Where(x => x.WeekEndingDate <= endDate.Value);

            return taQuery.ToList();
        }

        /// <summary>Returns tree-structure of teams allocation: Teams[TeamId, Scenarios[ScenarioId, ExpCats[ExpCatId, [{WeekEnding: TeamAllocation}]]]</summary>
        public Dictionary<Guid, Dictionary<Guid, Dictionary<Guid, Dictionary<DateTime, TeamAllocation>>>> GetTeamsAllocationTree(List<Guid> teams, List<Guid> scenarios, List<Guid> expenditureCategories, DateTime? startDate, DateTime? endDate)
        {
            return GetTeamsAllocation(teams, scenarios, expenditureCategories, startDate, endDate)
                        .GroupBy(x => x.TeamId)
                        .ToDictionary(tms => tms.Key, sc => sc.GroupBy(scenario => scenario.ScenarioId)
                                                              .ToDictionary(scenario => scenario.Key, expCats => expCats.GroupBy(expCat => expCat.ExpenditureCategoryId)
                                                                                                                        .ToDictionary(expCat => expCat.Key, allocations => allocations.ToDictionary(allocation => allocation.WeekEndingDate))));
        }

        /// <summary>
        /// Returns tree-structure of teams to scenario relations: Teams[TeamId, Team2ScenarioList]
        /// </summary>
        /// <param name="teams"></param>
        /// <param name="scenarioType"></param>
        /// <param name="scenarioStatus"></param>
        /// <returns></returns>
        public Dictionary<Guid, List<Guid>> GetRelationsWithScenariosTree(List<Guid> teams, ScenarioType? scenarioType, ScenarioStatus? scenarioStatus)
        {
            var team2ScenarioQuery =
                from t2pr in DbContext.Team2Project
                join proj in DbContext.Projects on t2pr.ProjectId equals proj.Id
                join scen in DbContext.Scenarios on proj.Id equals scen.ParentId
                select new
                {
                    ScenarioId = scen.Id,
                    ScenarioType = scen.Type,
                    ScenarioStatus = scen.Status,
                    TeamId = t2pr.TeamId
                };

            if ((teams != null) && (teams.Count > 0))
                team2ScenarioQuery = team2ScenarioQuery.Where(x => teams.Contains(x.TeamId));

            if (scenarioType.HasValue)
                team2ScenarioQuery = team2ScenarioQuery.Where(x => x.ScenarioType == (int)scenarioType.Value);

            if (scenarioStatus.HasValue)
                team2ScenarioQuery = team2ScenarioQuery.Where(x => x.ScenarioStatus == (int)scenarioStatus.Value);

            var result =
                team2ScenarioQuery.Select(x => new
                {
                    TeamId = x.TeamId,
                    ScenarioId = x.ScenarioId
                })
                .Distinct().ToList().GroupBy(x => x.TeamId)
                .ToDictionary(x => x.Key, g => g.Select(k => k.ScenarioId).ToList());

            return result;
        }
        public List<Team> GetTeamsByCompanyName(string CompanyName)
        {
            return DbContext.Teams.Where(x => x.Company.Name == CompanyName).ToList();
        }

        public List<TeamWithPermissionsModel> GetTeamsByCompanyId(Guid companyId)
        {
            var userId = SecurityManager.GetUserPrincipal();
            var teams = GetTeamsByCompanies(userId, new List<Guid> { companyId });
            return teams;
        }
        public TeamModel GetTeamByName(string name)
        {
            return (TeamModel)this.TeamModelBasicQuery.Where(x => x.Name == name).FirstOrDefault();
        }
        public List<TeamWithPermissionsModel> GetTeamsByCompanies(Guid userId, List<Guid> companies)
        {
            if (Guid.Empty.Equals(userId))
                throw new ArgumentNullException("userId");

            if (companies == null)
                throw new ArgumentNullException("companies");

            if (companies.Count < 1)
                return new List<TeamWithPermissionsModel>();

            var result =
                (from ut in DbContext.VW_User2Team
                 join c in DbContext.Companies on ut.CompanyId equals c.Id
                 join t in DbContext.Teams on ut.TeamId equals t.Id
                 where ut.CompanyId.HasValue && (companies.Contains(c.Id) || (c.ParentCompanyId.HasValue && companies.Contains(c.ParentCompanyId.Value)))
                    && ut.UserId.Equals(userId) && (ut.Read == 1 || ut.Write == 1)
                 select new TeamWithPermissionsModel()
                 {
                     Name = ut.TeamName,
                     UserId = ut.UserId.ToString(),
                     TeamId = ut.TeamId,
                     Read = ut.Read == 1,
                     Write = ut.Write == 1
                 }).Distinct().ToList();

            return result;
        }

        public List<TeamWithPermissionsModel> GetTeamsByViewsByUser(List<Guid> views, Guid userId)
        {
            if (views == null)
                throw new ArgumentNullException("views");

            if (userId.Equals(Guid.Empty))
                throw new ArgumentNullException("userId");

            if (views.Count < 1)
                return new List<TeamWithPermissionsModel>();

            var result =
                (from t2v in DbContext.VW_Team2View
                 join userTeams in DbContext.VW_User2Team on t2v.TeamId equals userTeams.TeamId
                 join t in DbContext.Teams on userTeams.TeamId equals t.Id
                 where userTeams.UserId == userId && views.Contains(t2v.ViewId) && ((userTeams.Read == 1) || (userTeams.Write == 1))
                 select new
                 {
                     Name = t.Name,
                     UserId = userTeams.UserId,
                     TeamId = userTeams.TeamId,
                     Read = userTeams.Read == 1,
                     Write = userTeams.Write == 1
                 }).Distinct().ToArray().Select(t => new TeamWithPermissionsModel
                 {
                     Name = t.Name,
                     UserId = t.UserId.ToString(),
                     TeamId = t.TeamId,
                     Read = t.Read,
                     Write = t.Write
                 }).ToList();

            return result;
        }
        [Obsolete("Seems like it's old, use GetTeamsByViewsByUser instead")]
        public List<Guid> GetTeamsByViews(List<Guid> views)
        {
            if (views == null)
                throw new ArgumentNullException("views");

            if (views.Count < 1)
                return new List<Guid>();

            // Via direct links views to teams
            var teamsDirect = DbContext.Team2View.Where(x => views.Contains(x.ViewId))
                .Select(x => x.TeamId).ToList();

            // Via links views to companies
            var teamsThroughCompanies =
                (from t2v in DbContext.VW_Company2View
                 join team in DbContext.Teams on t2v.CompanyId equals team.CompanyId
                 where views.Contains(t2v.ViewId)
                 select team.Id).ToList();

            var result = teamsDirect.Union(teamsThroughCompanies).Distinct().ToList();
            return result;
        }

        public List<TeamWithPermissionsModel> GetTeamsByUser(Guid userId)
        {
            if (userId.Equals(Guid.Empty))
                throw new ArgumentNullException(nameof(userId));

            return GetTeamWithPermissionsBasicQuery(userId).Distinct().ToList();
        }

        public List<TeamWithPermissionsModel> GetTeamsByUserFiltered(string userId, List<Guid> teams, List<Guid> views, List<Guid> companies)
        {
            if (String.IsNullOrEmpty(userId))
                throw new ArgumentNullException(nameof(userId));

            Guid userIdAsGuid = new Guid(userId);
            List<TeamWithPermissionsModel> result;

            bool filteredByTeams = teams != null && teams.Count > 0;
            bool filteredByViews = views != null && views.Count > 0;
            bool filteredByCompanies = companies != null && companies.Count > 0;
            bool filtered = filteredByTeams || filteredByViews || filteredByCompanies;

            if (filtered)
            {
                List<TeamWithPermissionsModel> teamsByTeams = new List<TeamWithPermissionsModel>();
                List<TeamWithPermissionsModel> teamsByViews = new List<TeamWithPermissionsModel>();
                List<TeamWithPermissionsModel> teamsByCompanies = new List<TeamWithPermissionsModel>();

                if (filteredByTeams)
                {
                    teamsByTeams = this.GetTeamsByUser(userIdAsGuid);
                    teamsByTeams = teamsByTeams.Where(t => teams.Contains(t.TeamId)).ToList();
                }

                if (filteredByViews)
                    teamsByViews = this.GetTeamsByViewsByUser(views, userIdAsGuid);

                if (filteredByCompanies)
                    teamsByCompanies = this.GetTeamsByCompanies(userIdAsGuid, companies);

                result = teamsByTeams.Union(teamsByViews).Union(teamsByCompanies).Distinct().ToList();
            }
            else
            {
                result = this.GetTeamsByUser(userIdAsGuid);
            }

            return result;
        }

        public List<TeamWithPermissionsModel> GetTeamsByResources(string userId, List<Guid> resources)
        {
            if (string.IsNullOrWhiteSpace(userId) || resources == null || resources.Count <= 0)
                return new List<TeamWithPermissionsModel>();

            var userIdAsGuid = Guid.Parse(userId);
            var userAvailableTeams = this.GetTeamsByUser(userIdAsGuid);

            var resourceTeams = DbContext.VW_TeamResource.Where(x =>
                resources.Contains(x.Id)).Select(x => x.TeamId).Distinct().ToList();
            return userAvailableTeams.Where(t => resourceTeams.Contains(t.TeamId)).ToList();
        }

        public List<TeamWithPermissionsModel> GetTeamsByProjects(Guid userId, IEnumerable<Guid> projects, IEnumerable<Guid> teams)
        {
            if (userId == Guid.Empty)
                throw new ArgumentException("Argument cannot be empty", nameof(userId));

            if (projects == null || !projects.Any())
                return new List<TeamWithPermissionsModel>();

            var query = (from team in GetTeamWithPermissionsBasicQuery(userId)
                         join project in DbContext.Team2Project on team.TeamId equals project.TeamId
                         where projects.Contains(project.ProjectId)
                         select team).Distinct();

            if (teams != null && teams.Any())
                query = query.Where(x => teams.Contains(x.TeamId));

            var result = query.ToList();

            return result;
        }

        public List<Guid> GetProjectsIds(IEnumerable<Guid> teams)
        {
            if (teams == null || teams.Count() <= 0)
                return new List<Guid>();

            var projects = DbContext.Team2Project.AsNoTracking()
                                    .Where(x => teams.Contains(x.TeamId))
                                    .Select(x => x.ProjectId)
                                    .Distinct()
                                    .ToList();

            return projects;
        }

        /// <summary>Returns teams by passed ids and views with checking of user rights for teams</summary>
        public List<TeamSummaryInfoModel> GetTeamsInfoByUser(string userId, List<Guid> teamIds, List<Guid> viewIds, List<FiscalCalendar> fiscalCalendar)
        {
            if ((teamIds == null || teamIds.Count <= 0) && (viewIds == null || viewIds.Count <= 0))
                return new List<TeamSummaryInfoModel>();

            var teams = GetTeamsByUserFiltered(userId, teamIds, viewIds, null);
            var teamsData = LoadTeams(teams.Select(t => t.TeamId)).ToList();

            if (teamsData.Count < 1)
                return new List<TeamSummaryInfoModel>();

            return ConvertTeamsToSummaryInfoModel(teamsData, teams, fiscalCalendar);
        }

        /// <summary>Returns teams by passed ids w/o checking of user rights for teams</summary>
        public List<TeamSummaryInfoModel> GetTeamsInfo(List<TeamWithPermissionsModel> teams, List<FiscalCalendar> fiscalCalendar, bool ignoreScenarioStatus = false)
        {
            if (teams == null || teams.Count <= 0)
                return new List<TeamSummaryInfoModel>();
            var teamIds = teams.Select(t => t.TeamId);
            var teamInfos = DbContext.Teams.Where(x => teamIds.Contains(x.Id))
                                       .AsNoTracking()
                                       .ToList();

            return ConvertTeamsToSummaryInfoModel(teamInfos, teams, fiscalCalendar, ignoreScenarioStatus);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="teams"></param>
        /// <param name="teamPermissions"></param>
        /// <param name="fiscalCalendar"></param>
        /// <returns></returns>
        /// <remarks>
        /// ToDo: Using of two team parameters is a quick temp solution they should be joined.
        /// </remarks>
        private List<TeamSummaryInfoModel> ConvertTeamsToSummaryInfoModel(List<Team> teams, IEnumerable<TeamWithPermissionsModel> teamPermissions, List<FiscalCalendar> fiscalCalendar, bool ignoreScenarioStatus = false)
        {
            var result = new List<TeamSummaryInfoModel>();
            if (teams == null || teams.Count <= 0)
                return result;

            if (fiscalCalendar == null)
                fiscalCalendar = new List<FiscalCalendar>();
            var startDate = fiscalCalendar.Count > 0 ? fiscalCalendar.FirstOrDefault().StartDate : (DateTime?)null;
            var endDate = fiscalCalendar.Count > 0 ? fiscalCalendar.LastOrDefault().EndDate : (DateTime?)null;

            var scenarioManager = (new ScenarioManager(DbContext));
            var prManager = (new PeopleResourcesManager(DbContext));
            var nptManager = new NonProjectTimeManager(DbContext);

            var uoms = (new UOMManager(DbContext)).GetAsDictionary();
            var teamIds = teams.Select(x => x.Id).ToList();
            var teamPermissionsDict = teamPermissions.GroupBy(t => t.TeamId).ToDictionary(gr => gr.Key, el => el.FirstOrDefault().AccessLevel);
            var teams2Scenarios = GetRelationsWithScenariosTree(teamIds, ScenarioType.Portfolio, ignoreScenarioStatus ? (ScenarioStatus?)null : ScenarioStatus.Active);
            var activeScenarios = teams2Scenarios.SelectMany(x => x.Value).Distinct().ToList();
            var plannedCapacityScenariosId =
                teams.Where(x => x.PlannedCapacityScenarioId.HasValue).Select(x => x.PlannedCapacityScenarioId.Value).ToList();
            var actualCapacityScenariosId =
                teams.Where(x => x.ActualCapacityScenarioId.HasValue).Select(x => x.ActualCapacityScenarioId.Value).ToList();
            var capacityScenarios = plannedCapacityScenariosId.Union(actualCapacityScenariosId).ToList();

            // Get ExpCats from capacity scenarios
            var expCatsFromCapacityScenarios = scenarioManager.GetCategoriesInScenarios(capacityScenarios, null);
            var expCatsFromResources = GetECsByTeamResources(teamIds);
            var expCatsInTeams = expCatsFromCapacityScenarios
                                                   .SelectMany(x => x.Value.Values).Union(expCatsFromResources.SelectMany(x => x.Value))
                                                   .Select(x => new
                                                   {
                                                       x.Id,
                                                       x.UOMId,
                                                       x.ExpCategoryWithCcName,
                                                       x.AllowResourceAssignment
                                                   }).Distinct().ToDictionary(x => x.Id);

            // Get ECs from active scenarios, the incoming teams are involved to (it is necessary to extract SuperECs)
            var expCatsFromActiveScenarios = scenarioManager.GetCategoriesInScenarios(activeScenarios, expCatsInTeams.Keys.ToList());
            // Add superECs to the main ECs list
            var superExpCatsInTeams = expCatsFromActiveScenarios.SelectMany(x => x.Value.Values)
                                                   .Select(x => new
                                                   {
                                                       x.Id,
                                                       x.UOMId,
                                                       x.ExpCategoryWithCcName,
                                                       x.AllowResourceAssignment
                                                   }).Distinct().ToDictionary(x => x.Id);
            superExpCatsInTeams.Keys.ToList().ForEach(expCatId => expCatsInTeams.Add(expCatId, superExpCatsInTeams[expCatId]));

            // for reducing number of retrieving scenario details rows we should retrieve only by ECs which exists in teams
            var scenarioDetails = scenarioManager.GetScenarioDetailsTree(activeScenarios.ToList(), expCatsInTeams.Keys.ToList());
            var plannedCapacityScenarios = scenarioManager.GetPlanningCapacityAdjusted(plannedCapacityScenariosId, expCatsInTeams.Keys.ToList(), null, null);

            // for reducing number of retrieving teams allocations rows we should retrieve only by active scenarios and ECs which exists in teams
            var teamsAllocations = GetTeamsAllocationTree(teams.Select(x => x.Id).ToList(), activeScenarios, expCatsInTeams.Keys.ToList(), null, null);

            var resources = prManager.GetResourcesWithAllocationsByTeams(teams.Select(x => x.Id));
            prManager.ReplaceOwnResourceExpendituresWithExpendituresInAllocations(resources);
            var resourcesTree = prManager.CastPeopleResourceWithAllocationToTree(resources);
            var resourcesIds = resourcesTree.SelectMany(x => x.Value.SelectMany(r => r.Value.Select(rv => rv.Id)));

            var npTimes = nptManager.GetNonProjectTimes4Teams(teamIds, startDate, endDate);
            var allResHolidays = (new FiscalCalendarManager(DbContext)).GetHolidayAllocationsByResource(null, null, resourceIds: resourcesIds);

            foreach (var team in teams)
            {
                var teamAllocations = teamsAllocations.ContainsKey(team.Id) ? teamsAllocations[team.Id] : null;
                var teamResources = resourcesTree.ContainsKey(team.Id) ? resourcesTree[team.Id] : null;

                var teamSummaryInfoModel = new TeamSummaryInfoModel()
                {
                    Id = team.Id.ToString(),
                    Name = team.Name,
                    CompanyId = team.CompanyId,
                    AccessLevel = teamPermissionsDict.ContainsKey(team.Id) ? teamPermissionsDict[team.Id] : AccessLevel.None
                };

                // Get Expenditures from team Planned and Actual capacity scenarios
                var plannedCapacityScenario = (team.PlannedCapacityScenarioId.HasValue &&
                                               plannedCapacityScenarios.ContainsKey(team.PlannedCapacityScenarioId.Value))
                                                  ? plannedCapacityScenarios[team.PlannedCapacityScenarioId.Value]
                                                  : null;

                var actualCapacityExpenditures = (team.ActualCapacityScenarioId.HasValue && expCatsFromCapacityScenarios.ContainsKey(team.ActualCapacityScenarioId.Value))
                                                 ? expCatsFromCapacityScenarios[team.ActualCapacityScenarioId.Value]
                                                 : null;

                var expCatsInTeam = new List<Guid>();
                if (plannedCapacityScenario != null && plannedCapacityScenario.Keys.Count > 0)
                    expCatsInTeam.AddRange(plannedCapacityScenario.Keys);

                if (actualCapacityExpenditures != null && actualCapacityExpenditures.Keys.Count > 0)
                    expCatsInTeam.AddRange(actualCapacityExpenditures.Keys);

                if (expCatsFromResources.ContainsKey(team.Id))
                    expCatsInTeam.AddRange(expCatsFromResources[team.Id].Select(x => x.Id));

                if (teamAllocations != null)
                {
                    // Append Expenditures from team allocations records for this team (necessary to get SuperECs)
                    teamAllocations.Keys.ToList().ForEach(scenarioId =>
                    {
                        var scenarioECs = teamAllocations[scenarioId];
                        if (scenarioECs != null)
                        {
                            var superExpCats = scenarioECs.Keys.Except(expCatsInTeam);
                            expCatsInTeam.AddRange(superExpCats);
                        }
                    });
                }

                expCatsInTeam = expCatsInTeam.Distinct().ToList();
                foreach (var expCatId in expCatsInTeam)
                {
                    var uomId = expCatsInTeams[expCatId].UOMId;
                    var activityCategory = new ExpCategorySummaryInfoModel()
                    {
                        Id = expCatId,
                        Name = expCatsInTeams[expCatId].ExpCategoryWithCcName,
                        UomValue = uoms[uomId].UOMValue,
                        ECScenario = ((plannedCapacityScenario != null && plannedCapacityScenario.Keys.Contains(expCatId) ? ExpCategorySummaryInfoModel.EC_Scenario.PLANNED : 0)
                        | (actualCapacityExpenditures != null && actualCapacityExpenditures.Keys.Contains(expCatId) ? ExpCategorySummaryInfoModel.EC_Scenario.ACTUALS : 0)
                        | (teamResources != null && teamResources.SelectMany(r => r.Value.Select(r1 => r1.ExpenditureCategoryId)).Contains(expCatId) ? ExpCategorySummaryInfoModel.EC_Scenario.TEAM : 0)),
                        AllowResourceAssignment = expCatsInTeams[expCatId].AllowResourceAssignment
                    };

                    if (plannedCapacityScenario != null && plannedCapacityScenario.ContainsKey(expCatId))
                    {
                        activityCategory.PlannedCapacityValues = plannedCapacityScenario[expCatId]
                                                                    .Where(x => x.WeekEndingDate.HasValue)
                                                                    .OrderBy(x => x.WeekEndingDateMs)
                                                                    .ToDictionary(x => x.WeekEndingDateMs.ToString(), g => g.Quantity);
                    }

                    if (teams2Scenarios.ContainsKey(team.Id))
                    {
                        foreach (var scenarioId in teams2Scenarios[team.Id])
                        {
                            var teamAllocationsInScenarioAndEC = (teamAllocations != null &&
                                                                  teamAllocations.ContainsKey(scenarioId) &&
                                                                  teamAllocations[scenarioId].ContainsKey(expCatId))
                                                                     ? teamAllocations[scenarioId][expCatId]
                                                                     : new Dictionary<DateTime, TeamAllocation>();

                            if (!scenarioDetails.ContainsKey(scenarioId))
                                continue;

                            if (!scenarioDetails[scenarioId].ContainsKey(expCatId))
                                continue;

                            foreach (var detail in scenarioDetails[scenarioId][expCatId])
                            {
                                decimal quantity = 0;
                                if (teamAllocationsInScenarioAndEC.ContainsKey(detail.WeekEndingDate.Value))
                                    quantity = teamAllocationsInScenarioAndEC[detail.WeekEndingDate.Value].Quantity;
                                else
                                    //quantity = (team2scenario.Allocation * (detail.Quantity ?? 0)) / (decimal)100.0;
                                    // SA: Since all team allocations are saved, when scenario created, and there
                                    // is no any virtual team allocations (calculated on the fly), we consider
                                    // quantity is zero for records, that have no allocations in db table
                                    quantity = 0;

                                var sdKey = Utils.ConvertToUnixDate(detail.WeekEndingDate.Value).ToString();
                                if (!activityCategory.NeedCapacity.ContainsKey(sdKey))
                                    activityCategory.NeedCapacity.Add(sdKey, 0);

                                activityCategory.NeedCapacity[sdKey] += quantity;
                            }
                        }
                    }

                    if (teamResources != null && teamResources.ContainsKey(expCatId))
                    {
                        foreach (var resource in teamResources[expCatId])
                        {
                            decimal resourceUomValue = activityCategory.UomValue;
                            ResourceSummaryInfoModel resourceModel = null;

                            if (activityCategory.Resources.ContainsKey(resource.Id.ToString()))
                            {
                                // Get existing resource model (the resource seems to be the team member twice or more times)
                                resourceModel = activityCategory.Resources[resource.Id.ToString()];
                            }
                            else
                            {
                                resourceModel = new ResourceSummaryInfoModel()
                                {
                                    Id = resource.Id,
                                };
                                activityCategory.Resources.Add(resourceModel.Id.ToString(), resourceModel);
                            }

                            // Store the resource's team membership info
                            var teamMembershipInfo = new ResourceSummaryInfoModel.TeamMembershipModel()
                            {
                                TeamId = team.Id,
                                StartDate = Utils.ConvertToUnixDate(resource.StartDate),
                                EndDate = resource.EndDate.HasValue ? Utils.ConvertToUnixDate(resource.EndDate.Value) : (long?)null
                            };
                            resourceModel.MergeTeam(teamMembershipInfo);

                            if (resource.OwnExpenditureCategoryId != resource.ExpenditureCategoryId)
                            {
                                // Get the resource's UOM, if it is assigned to Super EC. UOM should be taken from Super EC
                                if (!expCatsInTeams.ContainsKey(resource.OwnExpenditureCategoryId))
                                    throw new Exception("Own expenditure category for resource not found in the loaded category list");

                                var resourceOwnExpCat = expCatsInTeams[resource.OwnExpenditureCategoryId];
                                if (!uoms.ContainsKey(resourceOwnExpCat.UOMId))
                                    throw new Exception("UOM not found in the loaded list");

                                resourceUomValue = uoms[resourceOwnExpCat.UOMId].UOMValue;
                            }

                            var fiscalCalendarWeeks4Resource = fiscalCalendar.Where(x => x.StartDate >= resource.StartDate && (!resource.EndDate.HasValue || (x.EndDate <= resource.EndDate))).Select(x => x.EndDate).Distinct().ToDictionary(key => key);

                            if (activityCategory.AllowResourceAssignment)
                            {
                                // Attach NPT items to the only resources in ordinary ECs
                                var teamResourceNptAllocations = (npTimes.ContainsKey(resource.Id) ? npTimes[resource.Id] : new Dictionary<string, ResourceNptModel>());
                                // get only npt allocations related to the current team
                                foreach (var item in teamResourceNptAllocations)
                                {
                                    ResourceNptModel nptItem = null;
                                    if (resourceModel.NonProjectTime.ContainsKey(item.Key))
                                        nptItem = resourceModel.NonProjectTime[item.Key];
                                    else
                                        nptItem = new ResourceNptModel
                                        {
                                            Id = item.Value.Id,
                                            Name = item.Value.Name,
                                            CategoryId = item.Value.CategoryId,
                                            isTeamWide = item.Value.isTeamWide,
                                            Allocations = new Dictionary<string, decimal>()
                                        };
                                    foreach (var alloc in item.Value.Allocations)
                                    {
                                        if (teamMembershipInfo.StartDate < Convert.ToInt64(alloc.Key) &&
                                            (!teamMembershipInfo.EndDate.HasValue || teamMembershipInfo.EndDate >= Convert.ToInt64(alloc.Key)))
                                            nptItem.Allocations.Add(alloc.Key, alloc.Value);
                                    }
                                    // do not add NPT items for which we do not have any allocations
                                    // e.g. NPT record for resource of unaccessible team 
                                    if (nptItem.Allocations.Count > 0 && !resourceModel.NonProjectTime.ContainsKey(item.Key))
                                        resourceModel.NonProjectTime.Add(item.Key, nptItem);
                                }
                            }


                            foreach (var weekEnding in fiscalCalendarWeeks4Resource.Keys)
                            {
                                var rKey = Utils.ConvertToUnixDate(weekEnding).ToString();
                                var holidayKoeff = allResHolidays.ContainsKey(resource.Id) && allResHolidays[resource.Id].ContainsKey(weekEnding) ? allResHolidays[resource.Id][weekEnding] : 1;
                                var resourceCapacity = resourceUomValue * holidayKoeff;

                                if (!resourceModel.TotalCapacity.ContainsKey(rKey))
                                    resourceModel.TotalCapacity.Add(rKey, resourceCapacity);

                                if (activityCategory.AllowResourceAssignment)
                                {
                                    // Calculate EC's Actual capacity values for normal ECs only,
                                    // and skip this for SuperECs as they have no own capacity
                                    if (!activityCategory.ActualCapacityValues.ContainsKey(rKey))
                                        activityCategory.ActualCapacityValues.Add(rKey, 0);

                                    activityCategory.ActualCapacityValues[rKey] += resourceCapacity;
                                }
                            }

                            foreach (var rAllocation in resource.Allocations)
                            {
                                if (!fiscalCalendarWeeks4Resource.ContainsKey(rAllocation.WeekEndingDate))
                                    continue;

                                var rKey = Utils.ConvertToUnixDate(rAllocation.WeekEndingDate).ToString();
                                if (!resourceModel.AllocatedCapacity.ContainsKey(rKey))
                                    resourceModel.AllocatedCapacity.Add(rKey, 0);

                                resourceModel.AllocatedCapacity[rKey] += rAllocation.Quantity;

                                if (activityCategory.AllowResourceAssignment)
                                {
                                    if (!activityCategory.AllocatedCapacity.ContainsKey(rKey))
                                        activityCategory.AllocatedCapacity.Add(rKey, 0);

                                    // Calculate EC's Actual capacity values for normal ECs only,
                                    // and skip this for SuperECs as they have no own capacity
                                    activityCategory.AllocatedCapacity[rKey] += rAllocation.Quantity;
                                }
                            }
                        }
                    }

                    teamSummaryInfoModel.ExpCategories.Add(activityCategory.Id.ToString(), activityCategory);
                }

                result.Add(teamSummaryInfoModel);
            }

            return result;
        }

        public void AddCapacityToTeam(Guid expCatId, Guid teamId, IEnumerable<PeopleResourceModel.CapacityValues> values, bool isPlanned)
        {
            if (expCatId.Equals(Guid.Empty))
                throw new ArgumentException(nameof(expCatId));

            if (teamId.Equals(Guid.Empty))
                throw new ArgumentException(nameof(teamId));

            if (values == null || !values.Any())
                return; // Nothing to do

            // Set incoming values indexed
            var valuesIndexed = values.ToDictionary(k => k.WeekEnding, v => v);
            var incomingWeekendings = valuesIndexed.Keys;

            var scenarioManager = new ScenarioManager(DbContext);
            var teamManager = new TeamManager(DbContext);
            var team = teamManager.Load(teamId);

            if (team == null)
                throw new Exception($"Team '{teamId}' not found");

            var parentId = isPlanned ? team.PlannedCapacityScenarioId : team.ActualCapacityScenarioId;

            if (!parentId.HasValue)
            {
                string scenarioTypeAsText = isPlanned ? "planned" : "actual";
                throw new Exception($"Team '{team.Name}' doesn't has {scenarioTypeAsText} capacity scenario");
            }

            // Existing capacity values in DB
            var existingCapacityDetailsIndexed = scenarioManager.GetScenarioDetails(parentId.Value, new List<Guid> { expCatId }, incomingWeekendings.Min(), incomingWeekendings.Max(), false)
                                                                .ToDictionary(k => k.WeekEndingDate.Value, v => v);

            // Weekending, for which capacity values must be created
            var newCapacityDates = incomingWeekendings.Except(existingCapacityDetailsIndexed.Keys).ToList();

            #region Debug Info

            var sb = new StringBuilder();
            sb.AppendLine("TeamManager.AddCapacityToTeam method. existingCapacityDetailsIndexed.Values:");
            existingCapacityDetailsIndexed.Values.DebugObjectProperties(sb);
            sb.AppendLine("newCapacityDates:");
            newCapacityDates.DebugObjectProperties(sb);
            Logger.Debug(sb);

            #endregion

            // Process existing capacity values
            foreach (var detailItem in existingCapacityDetailsIndexed.Values)
            {
                var vc = valuesIndexed[detailItem.WeekEndingDate.Value];
                detailItem.Quantity = detailItem.Quantity + vc.Quantity;
                detailItem.Cost = (detailItem.Cost + vc.Cost) > 0 ? detailItem.Cost + vc.Cost : 0;
            };

            var addedValues = new List<ScenarioDetail>(newCapacityDates.Count);
            var removedValues = existingCapacityDetailsIndexed.Values.Where(x => x.Quantity <= 0);

            foreach (var weekEnding in newCapacityDates)
            {
                var vc = valuesIndexed[weekEnding];
                if (vc.Quantity <= 0)
                    continue;

                var capacityValue = new ScenarioDetail
                {
                    Id = Guid.NewGuid(),
                    ParentID = parentId.Value,
                    Quantity = vc.Quantity,
                    Cost = vc.Cost,
                    ExpenditureCategoryId = expCatId,
                    WeekEndingDate = weekEnding,
                    WeekOrdinal = 0
                };
                addedValues.Add(capacityValue);
            }

            DbContext.ScenarioDetail.RemoveRange(removedValues);
            DbContext.ScenarioDetail.AddRange(addedValues);
        }

        public void RemoveCapacityFromTeam(Guid expCatId, Guid teamId, List<PeopleResourceModel.CapacityValues> values, bool isPlanned)
        {
            if ((values == null) || (values.Count < 1))
                // Nothing to do
                return;

            // Make list of negative values
            var negativeValues = values.Select(x => new PeopleResourceModel.CapacityValues()
            {
                WeekEnding = x.WeekEnding,
                Cost = -x.Cost,
                Quantity = -x.Quantity
            }).ToList();

            AddCapacityToTeam(expCatId, teamId, negativeValues, isPlanned);
        }

        public List<Team> GetTeamsByProject(Guid projectId)
        {

            List<Team> results = new List<Team>();
            if (projectId == null)
                return results;

            var t2p = this.DbContext.Team2Project.Where(x => x.ProjectId == projectId).ToList<Team2Project>();
            foreach (Team2Project t in t2p)
            {
                results.Add(t.Team);
            }
            return results;
        }

        public Dictionary<Guid, string> GetTeamNames(IEnumerable<Guid> teams)
        {
            Dictionary<Guid, string> result = new Dictionary<Guid, string>();

            if ((teams == null) || !teams.Any())
                return result;

            result = DbContext.Teams.AsNoTracking().Where(x => teams.Contains(x.Id))
                .OrderBy(x => x.Name).ToDictionary(k => k.Id, v => v.Name);
            return result;
        }

        /// <summary>
        /// Returns UOMs for each team in the specified list. UOM for team is the Max UOM of all it's resources
        /// </summary>
        public Dictionary<Guid, decimal> GetTeamsUOM(List<Guid> teams, DateTime datePoint)
        {
            if (teams == null || teams.Count < 1)
                return new Dictionary<Guid, decimal>();

            var dp = datePoint.ToUniversalTime().Date;
            var uoms = (from p in DbContext.PeopleResources.AsNoTracking()
                        join p2t in DbContext.PeopleResource2Team.AsNoTracking() on p.Id equals p2t.PeopleResourceId
                        join ec in DbContext.ExpenditureCategory.AsNoTracking() on p.ExpenditureCategoryId equals ec.Id
                        join uom in DbContext.UOMs.AsNoTracking() on ec.UOMId equals uom.Id
                        where teams.Contains(p2t.TeamId) &&
                        (p2t.StartDate <= dp) && (!p2t.EndDate.HasValue || (p2t.EndDate.Value >= dp))
                        select new
                        {
                            TeamId = p2t.TeamId,
                            UOMValue = uom.UOMValue
                        })
                        .GroupBy(x => x.TeamId, (key, grp) => new
                        {
                            TeamId = key,
                            UOMValue = grp.Select(j => j.UOMValue).Max()
                        })
                        .ToDictionary(x => x.TeamId, x => x.UOMValue);

            return uoms;
        }

        public Dictionary<Guid, List<VW_ExpenditureCategory>> GetECsByTeamResources(List<Guid> teamIds)
        {
            if (teamIds == null || !teamIds.Any())
                return new Dictionary<Guid, List<VW_ExpenditureCategory>>();

            var query = (from resource in DbContext.VW_TeamResource
                         join category in DbContext.VW_ExpenditureCategory on resource.ExpenditureCategoryId equals category.Id
                         where teamIds.Contains(resource.TeamId)
                         select new
                         {
                             TeamId = resource.TeamId,
                             ExpenditureCategory = category
                         });

            var result = query.ToList()
                              .GroupBy(x => x.TeamId)
                              .ToDictionary(x => x.Key, g => g.Select(x => x.ExpenditureCategory)
                                                              .ToList());

            return result;
        }
        public List<Guid> GetProjectAddNotificationList(Guid teamId, string state)
        {
            var contacts = (from c in this.DbContext.WorkFlowContacts
                            join st in this.DbContext.NotificationToWorkFlowStates on c.ContactId equals st.WorkFlowContactId
                            where c.PartentId == teamId && c.NotificationType == (int)WorkFlowContactNotificationType.TeamScenarioAdd && st.State == state
                            select c.Id).ToList();
            return contacts;
        }
        public List<Guid> GetProjectAddNotificationList(Guid teamId)
        {
            var contacts = (from c in this.DbContext.WorkFlowContacts

                            where c.PartentId == teamId && c.NotificationType == (int)WorkFlowContactNotificationType.TeamScenarioAdd
                            select c.Id).ToList();
            return contacts;
        }
        #region Private Members

        private IQueryable<TeamModel> TeamModelBasicQuery
        {
            get
            {
                var query = DataTable.AsNoTracking()
                                     .Select(x =>
                new TeamModel()
                {
                    Id = x.Id,
                    Name = x.Name,
                    CompanyId = x.CompanyId,
                    CostCenterId = x.CostCenterId,
                    ReportToId = x.ReportsTo,
                    PlannedCapacityScenarioId = x.PlannedCapacityScenarioId,
                    ActualCapacityScenarioId = x.ActualCapacityScenarioId,
                    ProjectAddNotification = x.ProjectAddNotification.HasValue ? x.ProjectAddNotification.Value : false
                });

                return query;
            }
        }

        private IQueryable<TeamWithPermissionsModel> GetTeamWithPermissionsBasicQuery(Guid userId)
        {
            var query = DbContext.VW_User2Team.Where(x => x.UserId.Equals(userId) && (x.Read == 1 || x.Write == 1))
                                 .Select(x => new TeamWithPermissionsModel
                                 {
                                     Name = x.TeamName,
                                     UserId = x.UserId.ToString(),
                                     TeamId = x.TeamId,
                                     Read = (x.Read == 1),
                                     Write = (x.Write == 1)
                                 });

            return query;
        }

        #endregion
    }
}