using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using EnVisage.Models;
using EnVisage.Models.ProjectDependencies;
using System.Data.Entity.Infrastructure;
using jQuery.DataTables.Mvc;
using System.Data.SqlClient;
using System.Data.Entity.Core.Objects;
using System.Data;
using Prevu.Core.Audit.Model;
using System.Threading.Tasks;
using static EnVisage.Code.AuditProxy;
using Prevu.Core.Workflow;
using Prevu.DAL.Main.Accessors;
using Prevu.Core.Audit.Model.ResponseModels;
using Resources;
using System.Runtime.CompilerServices;
using EnVisage.Code.Cache;

namespace EnVisage.Code.BLL
{
    public class ProjectManager : ManagerBase<Project, ProjectModel>
    {
        #region Properties
        protected ScenarioManager ScenarioManager { get; set; }
        protected IWorkflowManager WorkflowManager { get; set; }
        protected UsersCache UsersCache { get; set; }
        public const string EDIT_DEPENDENCY_KEY = "EDIT_DEPENDENCY_KEY";
        #endregion
        #region Local Enums

        [Flags]
        public enum LoadProjectItems
        {
            None = 0x0,
            Scenarios = 0x1,
            Teams = 0x2,
            Goals = 0x4,
            PortfolioLabels = 0x8,
            Tags = 0x16,

            All = Scenarios | Teams | Goals
        }

        #endregion
        #region Constructors

        public ProjectManager(EnVisageEntities dbContext)
            : base(dbContext)
        {
            ScenarioManager = new ScenarioManager(dbContext);
        }
        public ProjectManager(EnVisageEntities dbContext, ScenarioManager scenarioManager, IWorkflowManager wfManager, UsersCache usersCache)
            : base(dbContext)
        {
            ScenarioManager = scenarioManager;
            WorkflowManager = wfManager;
            UsersCache = usersCache;
        }

        #endregion

        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<Project> DataTable => DbContext.Projects;

        public Dictionary<Guid, ProjectTeamUsage> GetProjectTeamsUsageInfo(Guid projectId)
        {
            if (Guid.Empty.Equals(projectId))
                return new Dictionary<Guid, ProjectTeamUsage>();

            var projectScenarios = ScenarioManager.GetScenariosByParentProject(projectId, ScenarioType.Portfolio);
            var projectActualScenario = ScenarioManager.GetActualScenario(projectId);

            // return empty data if there are scenarios for the specified project (e.g. new project, not commited yet)
            if (null == projectScenarios && null == projectActualScenario)
                return new Dictionary<Guid, ProjectTeamUsage>();

            var allProjectScenarios = new List<Guid>();
            if (null != projectScenarios)
                allProjectScenarios = projectScenarios.Clone();
            if (null != projectActualScenario)
                allProjectScenarios.Add(projectActualScenario.Id);
            if (null == allProjectScenarios || !allProjectScenarios.Any())
                return new Dictionary<Guid, ProjectTeamUsage>();

            var teamsWithResourceAllocations = ScenarioManager.GetResourceAllocatedScenarioTeams(allProjectScenarios);
            if (null == teamsWithResourceAllocations || !teamsWithResourceAllocations.Any())
                return new Dictionary<Guid, ProjectTeamUsage>();
            var allTeamsInAllProjectScenarios = teamsWithResourceAllocations.Values.SelectMany(x => x).Distinct().ToList();

            var portfolioScenarioTeams = teamsWithResourceAllocations.Keys.Except(new List<Guid> { projectActualScenario.Id })
                .SelectMany(x => teamsWithResourceAllocations[x]).Distinct().ToList();
            var actualScenarioTeams = new List<Guid>();

            if (teamsWithResourceAllocations.ContainsKey(projectActualScenario.Id))
            {
                actualScenarioTeams = teamsWithResourceAllocations[projectActualScenario.Id];
            }

            var result = allTeamsInAllProjectScenarios.ToDictionary(k => k, v =>
                new ProjectTeamUsage
                {
                    HasProjectionsResourceAllocations = portfolioScenarioTeams.Contains(v),
                    HasActualsResourceAllocations = actualScenarioTeams.Contains(v)
                });

            return result;
        }
        public List<Guid> getTeamsAssingedToProject(Guid projectId)
        {
            return projectId == Guid.Empty ? new List<Guid>() : DbContext.Team2Project.Where(x => x.ProjectId == projectId).Select(x => x.TeamId).ToList();
        }
        public override Project Save(ProjectModel model)
        {
            if (model == null)
                throw new ArgumentNullException(nameof(model));

            var currentProjectId = model.Id;
            var scenarios4Copy = new Dictionary<Guid, List<Scenario>>();
            var localRates4Copy = new Dictionary<Guid, List<Rate>>();
            var scenarioDetails4Copy = new Dictionary<Guid, List<ScenarioDetail>>();

            if (model.SaveAsCopy)
            {
                model.Id = Guid.Empty;
                model.Name += " - Copy";

                var projects = new List<Guid>() { currentProjectId };
                if (model.HasChildren && model.Parts != null && model.Parts.Count > 0)
                    projects.AddRange(model.Parts.Select(x => x.Id));

                var scenarios = DbContext.Scenarios.Where(x => x.ParentId.HasValue &&
                                                               x.Type == (int)ScenarioType.Portfolio && projects.Contains(x.ParentId.Value))
                                                   .Include(x => x.CostSavings1)
                                                   .Include(x => x.PeopleResourceAllocations)
                                                   .Include(x => x.Scenario2Group)
                                                   .Include(x => x.TeamAllocations)
                                                   .AsNoTracking()
                                                   .ToList();

                var scenarioIds = scenarios.Select(x => x.Id).ToList();

                if (scenarioIds.Count > 0)
                {
                    scenarios4Copy = scenarios.GroupBy(x => x.ParentId.Value)
                                      .ToDictionary(x => x.Key, g => g.ToList());

                    scenarioDetails4Copy = DbContext.ScenarioDetail
                                                    .AsNoTracking()
                                                    .Where(x => x.ParentID.HasValue && scenarioIds.Contains(x.ParentID.Value))
                                                    .ToList()
                                                    .GroupBy(x => x.ParentID.Value)
                                                    .ToDictionary(x => x.Key, g => g.ToList());

                    localRates4Copy = (new RateManager(DbContext)).Get4Parents(scenarioIds, RateModel.RateType.Derived);
                }
            }

            var partManager = new ProjectPartManager(DbContext);
            if (model.Parts != null && !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;
                model.WorkFlowContacts = part.WorkFlowContacts;

            }
            else if (model.Parts != null && 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 project = base.Save(model);

            #region Save Project Parts if exist

            if (model.Parts != null && model.HasChildren && model.Parts.Count > 0)
            {
                var currentParts = model.Id == Guid.Empty
                                       ? new List<Project>()
                                       : 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)
                    {
                        IList<Guid> holders = this.GetSubprojectsAndParts(currentPart);
                        FileManager fileMngr = new FileManager(DbContext);
                        fileMngr.QueuePermanentFilesToDelete(holders);

                        (DbContext as IObjectContextAdapter).ObjectContext.ExecuteStoreCommand(
                            $"exec sp_DeleteProject '{currentPart.Id}'");

                        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)
                        {
                            Project deletableProject = DbContext.Projects.SingleOrDefault(x => x.Id.Equals(projectModel.Id));

                            if ((deletableProject != null) && !deletableProject.Id.Equals(Guid.Empty))
                            {
                                IList<Guid> holders = this.GetSubprojectsAndParts(deletableProject);
                                FileManager fileMngr = new FileManager(DbContext);
                                fileMngr.QueuePermanentFilesToDelete(holders);

                                (DbContext as IObjectContextAdapter).ObjectContext.ExecuteStoreCommand(string.Format("exec sp_DeleteProject '{0}'", projectModel.Id));

                                fileMngr.DeleteQueuedFiles(true);
                            }
                        }
                    }
                    else
                    {
                        projectModel.ParentProjectId = project.Id;
                        projectModel.PartNum = partNum++;

                        if (currentParts.Count > 0 && currentParts.All(t => t.Id != projectModel.Id) || model.SaveAsCopy)
                        {
                            var oldId = model.Parts[i].Id;
                            model.Parts[i].Id = Guid.Empty;

                            var savedPartModel = partManager.Save(projectModel);
                            var savedPartModelConverted = (ProjectPartModel)savedPartModel;

                            if (projectModel.ExternalContacts != null)
                            {
                                savedPartModelConverted.ExternalContacts = new List<Guid>();
                                savedPartModelConverted.ExternalContacts.AddRange(projectModel.ExternalContacts);
                            }

                            if (projectModel.InternalContacts != null)
                            {
                                savedPartModelConverted.InternalContacts = new List<Guid>();
                                savedPartModelConverted.InternalContacts.AddRange(projectModel.InternalContacts);
                            }
                            if (projectModel.WorkFlowContacts != null)
                            {
                                savedPartModelConverted.WorkFlowContacts = new List<Guid>();
                                savedPartModelConverted.WorkFlowContacts.AddRange(projectModel.WorkFlowContacts);
                            }
                            model.Parts[i] = savedPartModelConverted;
                            model.Parts[i].Attachments = projectModel.Attachments;
                            model.Parts[i].Links = projectModel.Links;
                            model.Parts[i].ProjectTags = projectModel.ProjectTags;
                            model.Parts[i].PortfolioTags = projectModel.PortfolioTags;
                            if (model.SaveAsCopy)
                            {
                                if (scenarios4Copy.ContainsKey(oldId))
                                {
                                    CopyScenariosToProject(model.Parts[i].Id, scenarios4Copy[oldId], scenarioDetails4Copy, localRates4Copy, projectModel.AssignedTeams);
                                }
                            }
                        }
                        else
                        {
                            var savedPartModel = partManager.Save(projectModel);
                            var savedPartModelConverted = (ProjectPartModel)savedPartModel;

                            if (projectModel.ExternalContacts != null)
                            {
                                savedPartModelConverted.ExternalContacts = new List<Guid>();
                                savedPartModelConverted.ExternalContacts.AddRange(projectModel.ExternalContacts);
                            }

                            if (projectModel.InternalContacts != null)
                            {
                                savedPartModelConverted.InternalContacts = new List<Guid>();
                                savedPartModelConverted.InternalContacts.AddRange(projectModel.InternalContacts);
                            }
                            if (projectModel.WorkFlowContacts != null)
                            {
                                savedPartModelConverted.WorkFlowContacts = new List<Guid>();
                                savedPartModelConverted.WorkFlowContacts.AddRange(projectModel.WorkFlowContacts);
                            }
                            model.Parts[i] = savedPartModelConverted;
                            model.Parts[i].Attachments = projectModel.Attachments;
                            model.Parts[i].Links = projectModel.Links;
                            model.Parts[i].ProjectTags = projectModel.ProjectTags;
                            model.Parts[i].PortfolioTags = projectModel.PortfolioTags;
                        }
                    }
                }
            }

            #endregion

            if (model.Id == Guid.Empty)
            {
                #region Create Actuals scenario

                // we need to create empty actual scenario for all new projects (just created or copied)
                var scenario = new Scenario
                {
                    Id = Guid.NewGuid(),
                    Name = "ACTUALS",
                    ParentId = project.Id,
                    Type = ScenarioType.Actuals.GetHashCode(),
                    StartDate = DateTime.UtcNow.Date,
                    Color = "",
                    ProjectedRevenue = 0
                };
                DbContext.Scenarios.Add(scenario);

                #endregion
            }

            if (!model.HasChildren && model.Parts.Count == 1)
            {
                SaveContacts(project, model.InternalContacts, model.ExternalContacts);

                model.AssignedTeams = model.Parts[0].AssignedTeams = UpdateProjectTeams(project, model.AssignedTeams, null, false, true);

                SaveStrategicGoals(project, model.StrategicGoals);
                (new WorkFlowManager(DbContext)).SaveContacts(model.WorkFlowContacts, null, project.Id, WorkFlowContactNotificationType.None);
                if (model.SaveAsCopy)
                {
                    if (scenarios4Copy.ContainsKey(currentProjectId))
                    {
                        CopyScenariosToProject(project.Id, scenarios4Copy[currentProjectId], scenarioDetails4Copy, localRates4Copy, model.AssignedTeams);
                    }
                }
            }

            return project;
        }

        #region Events & History Logging

        public void LogProjectCreateEvent(Project newProject, string userId, string transactionId = null)
        {
            transactionId = Utils.GetOrCreateTransactionId(DbContext, transactionId);

            #region Project Created

            LogEvent(transactionId, new EventRequestBaseModel
            {
                EntityId = newProject.Id,
                ProjectId = newProject.Id,
                ClassificationKey = "ProjectCreated",
                NewValue = newProject.Name,
                UserId = userId
            });

            #endregion
        }

        public void LogProjectDeleteEvent(ProjectModel oldProject, string userId, string transactionId = null)
        {
            transactionId = Utils.GetOrCreateTransactionId(DbContext, transactionId);

            #region Project Delete

            LogEvent(transactionId, new EventRequestBaseModel
            {
                EntityId = oldProject.Id,
                ProjectId = oldProject.Id,
                ClassificationKey = "ProjectDelete",
                UserId = userId,
                OldValue = oldProject.Number
            });

            #endregion
        }

        public void LogProjectEvents(string userId, ProjectModel newProjectModel, string transactionId = null, bool convertedToProjectWithParts = false)
        {
            transactionId = Utils.GetOrCreateTransactionId(DbContext, transactionId);

            Project oldProject = null;
            Guid newProjectModelId = Guid.Empty;
            if (convertedToProjectWithParts)
            {
                //when we convert project to project with parts, first "virtual" part becomes real first part, old project id becomes first part's id
                //so, to get old project we need to load by id taken from the first part because newProjectModel.Id is Guid.Empty until we save
                if (newProjectModel.Parts.Count < 1 || newProjectModel.Parts.First() == null)
                    throw new ArgumentException("ProjectModel does not contain parts");
                oldProject = DataTable.Find(newProjectModel.Parts.First().Id);
                newProjectModelId = newProjectModel.Parts.First().Id;
            }
            else
            {
                oldProject = DataTable.Find(newProjectModel.Id);
                newProjectModelId = newProjectModel.Id;
            }

            if (oldProject != null)
            {
                var oldProjectModel = ProjectModel.GetProjectModel(oldProject, DbContext);

                #region Project Name

                if (newProjectModel.Name != oldProjectModel.Name)
                {
                    LogEvent(transactionId, new EventRequestBaseModel
                    {
                        EntityId = newProjectModelId,
                        ProjectId = newProjectModelId,
                        ClassificationKey = "ProjectNameChange",
                        UserId = userId,
                        NewValue = newProjectModel.Name,
                        OldValue = oldProjectModel.Name
                    });
                }

                #endregion

                #region Project Number

                if (!string.IsNullOrEmpty(newProjectModel.Number))
                {
                    if (string.IsNullOrEmpty(oldProjectModel.Number))
                    {
                        LogEvent(transactionId, new EventRequestBaseModel
                        {
                            EntityId = newProjectModelId,
                            ProjectId = newProjectModelId,
                            ClassificationKey = "ProjectNumberAdd",
                            UserId = userId,
                            NewValue = newProjectModel.Number,
                            OldValue = oldProjectModel.Number
                        });
                    }
                    else
                    {
                        if (oldProjectModel.Number != newProjectModel.Number)
                        {
                            LogEvent(transactionId, new EventRequestBaseModel
                            {
                                EntityId = newProjectModelId,
                                ProjectId = newProjectModelId,
                                ClassificationKey = "ProjectNumberChange",
                                UserId = userId,
                                NewValue = newProjectModel.Number,
                                OldValue = oldProjectModel.Number
                            });
                        }
                    }
                }
                else
                {
                    if (!string.IsNullOrEmpty(oldProjectModel.Number))
                    {
                        LogEvent(transactionId, new EventRequestBaseModel
                        {
                            EntityId = newProjectModelId,
                            ProjectId = newProjectModelId,
                            ClassificationKey = "ProjectNumberDelete",
                            UserId = userId,
                            NewValue = newProjectModel.Number,
                            OldValue = oldProjectModel.Number
                        });
                    }
                }

                #endregion

                #region Project Color

                if (!string.IsNullOrEmpty(newProjectModel.Color))
                {
                    if (string.IsNullOrEmpty(oldProjectModel.Color))
                    {
                        LogEvent(transactionId, new EventRequestBaseModel
                        {
                            EntityId = newProjectModelId,
                            ProjectId = newProjectModelId,
                            ClassificationKey = "ProjectColorAdd",
                            UserId = userId,
                            NewValue = newProjectModel.Color.Trim('#'),
                            OldValue = string.Empty
                        });
                    }
                    else
                    {
                        if (oldProjectModel.Color.Trim('#') != newProjectModel.Color.Trim('#'))
                        {
                            LogEvent(transactionId, new EventRequestBaseModel
                            {
                                EntityId = newProjectModelId,
                                ProjectId = newProjectModelId,
                                ClassificationKey = "ProjectColorChange",
                                UserId = userId,
                                NewValue = newProjectModel.Color.Trim('#'),
                                OldValue = oldProjectModel.Color.Trim('#')
                            });
                        }
                    }
                }
                else
                {
                    if (!string.IsNullOrEmpty(oldProjectModel.Color))
                    {
                        LogEvent(transactionId, new EventRequestBaseModel
                        {
                            EntityId = newProjectModelId,
                            ProjectId = newProjectModelId,
                            ClassificationKey = "ProjectColorDelete",
                            UserId = userId,
                            NewValue = string.Empty,
                            OldValue = oldProjectModel.Color.Trim('#')
                        });
                    }
                }

                #endregion

                #region Business Unit

                if (newProjectModel.CompanyId != oldProjectModel.CompanyId)
                {
                    var compayManager = new CompanyManager(DbContext);
                    var newCompanyName = string.Empty;
                    if (newProjectModel.CompanyId != null)
                    {
                        var newCompany = compayManager.GetCompanyById(newProjectModel.CompanyId.Value);
                        if (newCompany != null)
                        {
                            newCompanyName = newCompany.Name;
                        }
                    }
                    LogEvent(transactionId, new EventRequestBaseModel
                    {
                        EntityId = newProjectModelId,
                        ProjectId = newProjectModelId,
                        ClassificationKey = "ProjectBusinessUnitChange",
                        UserId = userId,
                        NewValue = newCompanyName,
                        OldValue = oldProjectModel.CompanyName
                    });
                }

                #endregion

                #region Project UserDefinedFields

                if (newProjectModel.UserDefinedFields.Count > 0 && !string.IsNullOrEmpty(newProjectModel.UserDefinedFields.First().Values.Value))
                {
                    if (oldProjectModel.UserDefinedFields.Count == 0 || string.IsNullOrEmpty(oldProjectModel.UserDefinedFields.First().Values.Value))
                    {
                        LogEvent(transactionId, new EventRequestBaseModel
                        {
                            EntityId = newProjectModelId,
                            ProjectId = newProjectModelId,
                            ClassificationKey = "ProjectUserDefinedFieldsAdd",
                            UserId = userId,
                            NewValue = newProjectModel.UserDefinedFields.First().Values.Value,
                            OldValue = string.Empty
                        });
                    }
                    else
                    {
                        if (oldProjectModel.UserDefinedFields.Count > 0 && newProjectModel.UserDefinedFields.First().Values.Value != oldProjectModel.UserDefinedFields.First().Values.Value)
                        {
                            LogEvent(transactionId, new EventRequestBaseModel
                            {
                                EntityId = newProjectModelId,
                                ProjectId = newProjectModelId,
                                ClassificationKey = "ProjectUserDefinedFieldsChange",
                                UserId = userId,
                                NewValue = newProjectModel.UserDefinedFields.First().Values.Value,
                                OldValue = oldProjectModel.UserDefinedFields.First().Values.Value
                            });
                        }
                    }
                }
                else
                {
                    if (oldProjectModel.UserDefinedFields.Count > 0 && !string.IsNullOrEmpty(oldProjectModel.UserDefinedFields.First().Values.Value))
                    {
                        LogEvent(transactionId, new EventRequestBaseModel
                        {
                            EntityId = newProjectModelId,
                            ProjectId = newProjectModelId,
                            ClassificationKey = "ProjectUserDefinedFieldsDelete",
                            UserId = userId,
                            NewValue = string.Empty,
                            OldValue = oldProjectModel.UserDefinedFields.First().Values.Value
                        });
                    }
                }

                #endregion

                #region Parts

                if (!newProjectModel.HasChildren || convertedToProjectWithParts)
                {
                    LogProjectPartsEvents(false, oldProjectModel.Parts.First(), newProjectModel.Parts.First(), userId, transactionId, convertedToProjectWithParts);
                }

                #endregion
            }
        }

        [MethodImpl(MethodImplOptions.AggressiveInlining)]
        private string FormatTagNameForEventsLog(Guid tagId, Dictionary<Guid, string> tagNames)
        {
            var tagName = (tagNames.ContainsKey(tagId)) ? tagNames[tagId] : String.Format(AuditStrings.Tag_UnknownNameTemplate, tagId.ToString());
            return tagName;
        }

        public void LogProjectPartsEvents(bool isPart, ProjectPartModel oldProject, ProjectPartModel newProjectModel, string userId, string transactionId = null, bool convertedToProjectWithParts = false)
        {
            transactionId = Utils.GetOrCreateTransactionId(DbContext, transactionId);
            string standardCommentForProjectPart = isPart ? String.Format(AuditStrings.ProjectPart_StandardEventComment, newProjectModel.Name) : (string)null;
            Guid? projectId = null;
            if (isPart || convertedToProjectWithParts)
            {
                projectId = newProjectModel.Id;
            }
            else
            {
                projectId = newProjectModel.ParentProjectId;
            }

            EventRequestBaseModel eventRecord;

            // First load additional necessary data
            var tagsToLoad = new List<Guid>();
            Task<Dictionary<Guid, string>> tagsLoadingTask = null;

            if (oldProject.PortfolioTags != null)
                tagsToLoad.AddRange(oldProject.PortfolioTags);

            if (newProjectModel.PortfolioTags != null)
                tagsToLoad.AddRange(newProjectModel.PortfolioTags);

            if (oldProject.ProjectTags != null)
                tagsToLoad.AddRange(oldProject.ProjectTags);

            if (newProjectModel.ProjectTags != null)
                tagsToLoad.AddRange(newProjectModel.ProjectTags);

            if (tagsToLoad.Count > 0)
            {
                var tagManager = new TagManager(DbContext);
                tagsLoadingTask = tagManager.LoadTagNamesAsync(tagsToLoad.Distinct());
            }

            #region Project Type

            if (newProjectModel.TypeId != oldProject.TypeId)
            {
                var newValue = DbContext.Types.FirstOrDefault(p => p.Id == newProjectModel.TypeId);
                LogEvent(transactionId, new EventRequestBaseModel
                {
                    EntityId = projectId,
                    ProjectId = projectId,
                    ClassificationKey = "ProjectTypeChange",
                    UserId = userId,
                    NewValue = newValue?.Name ?? newProjectModel.TypeName,
                    OldValue = oldProject.TypeName,
                    Comment = standardCommentForProjectPart
                });
            }


            #endregion
            #region Client

            if (newProjectModel.ClientId != oldProject.ClientId)
            {
                var clientManager = new ClientManager(DbContext);
                var newClientName = string.Empty;
                if (newProjectModel.ClientId != null)
                {
                    var newClient = clientManager.LoadClientModel(newProjectModel.ClientId.Value);
                    if (newClient != null)
                    {
                        newClientName = newClient.Name;
                    }
                }
                LogEvent(transactionId, new EventRequestBaseModel
                {
                    EntityId = projectId,
                    ProjectId = projectId,
                    ClassificationKey = "ProjectClientChange",
                    UserId = userId,
                    NewValue = newClientName,
                    OldValue = oldProject.ClientName,
                    Comment = standardCommentForProjectPart
                });
            }

            #endregion
            #region Project Deadline

            if (newProjectModel.Deadline != null)
            {
                if (oldProject.Deadline == null)
                {
                    LogEvent(transactionId, new EventRequestBaseModel
                    {
                        EntityId = projectId,
                        ProjectId = projectId,
                        ClassificationKey = "ProjectDeadlineAdd",
                        UserId = userId,
                        NewValue = newProjectModel.Deadline.ToString(),
                        OldValue = oldProject.Deadline.ToString(),
                        Comment = standardCommentForProjectPart
                    });
                }
                else
                {
                    if (oldProject.Deadline != newProjectModel.Deadline)
                    {
                        LogEvent(transactionId, new EventRequestBaseModel
                        {
                            EntityId = projectId,
                            ProjectId = projectId,
                            ClassificationKey = "ProjectDeadlineChange",
                            UserId = userId,
                            NewValue = newProjectModel.Deadline.ToString(),
                            OldValue = oldProject.Deadline.ToString(),
                            Comment = standardCommentForProjectPart
                        });
                    }
                }
            }
            else
            {
                if (oldProject.Deadline != null)
                {
                    LogEvent(transactionId, new EventRequestBaseModel
                    {
                        EntityId = projectId,
                        ProjectId = projectId,
                        ClassificationKey = "ProjectDeadlineDelete",
                        UserId = userId,
                        NewValue = newProjectModel.Deadline.ToString(),
                        OldValue = oldProject.Deadline.ToString(),
                        Comment = standardCommentForProjectPart
                    });
                }
            }

            #endregion
            #region Status

            if (newProjectModel.StatusId != oldProject.StatusId)
            {
                var statusManager = new StatusManager(DbContext);
                var newName = string.Empty;
                var newItem = statusManager.GetTypeById(newProjectModel.StatusId);
                if (newItem != null)
                {
                    newName = newItem.Name;
                }
                LogEvent(transactionId, new EventRequestBaseModel
                {
                    EntityId = projectId,
                    ProjectId = projectId,
                    ClassificationKey = "ProjectStatusChange",
                    UserId = userId,
                    NewValue = newName,
                    OldValue = oldProject.StatusName,
                    Comment = standardCommentForProjectPart
                });
            }

            #endregion
            #region Project Probability

            if (newProjectModel.Probability != 0)
            {
                if (oldProject.Probability == 0)
                {
                    LogEvent(transactionId, new EventRequestBaseModel
                    {
                        EntityId = projectId,
                        ProjectId = projectId,
                        ClassificationKey = "ProjectProbabilityAdd",
                        UserId = userId,
                        NewValue = newProjectModel.Probability.ToString(),
                        OldValue = oldProject.Probability.ToString(),
                        Comment = standardCommentForProjectPart
                    });
                }
                else
                {
                    if (oldProject.Probability != newProjectModel.Probability)
                    {
                        LogEvent(transactionId, new EventRequestBaseModel
                        {
                            EntityId = projectId,
                            ProjectId = projectId,
                            ClassificationKey = "ProjectProbabilityChange",
                            UserId = userId,
                            NewValue = newProjectModel.Probability.ToString(),
                            OldValue = oldProject.Probability.ToString(),
                            Comment = standardCommentForProjectPart
                        });
                    }
                }
            }
            else
            {
                if (oldProject.Probability != 0)
                {
                    LogEvent(transactionId, new EventRequestBaseModel
                    {
                        EntityId = projectId,
                        ProjectId = projectId,
                        ClassificationKey = "ProjectProbabilityDelete",
                        UserId = userId,
                        NewValue = newProjectModel.Probability.ToString(),
                        OldValue = oldProject.Probability.ToString(),
                        Comment = standardCommentForProjectPart
                    });
                }
            }

            #endregion
            #region Revenue Generating

            if (newProjectModel.IsRevenueGenerating != oldProject.IsRevenueGenerating)
            {
                LogEvent(transactionId, new EventRequestBaseModel
                {
                    EntityId = projectId,
                    ProjectId = projectId,
                    ClassificationKey = "ProjectRevenueGeneratingChange",
                    UserId = userId,
                    NewValue = newProjectModel.IsRevenueGenerating ? "Yes" : "No",
                    OldValue = oldProject.IsRevenueGenerating ? "Yes" : "No",
                    Comment = standardCommentForProjectPart
                });
            }

            #endregion
            #region Revenue Priority

            if (newProjectModel.Priority != oldProject.Priority)
            {
                LogEvent(transactionId, new EventRequestBaseModel
                {
                    EntityId = projectId,
                    ProjectId = projectId,
                    ClassificationKey = "ProjectPriorityChange",
                    UserId = userId,
                    NewValue = newProjectModel.Priority.ToString(),
                    OldValue = oldProject.Priority.ToString(),
                    Comment = standardCommentForProjectPart
                });
            }

            #endregion
            #region Revenue Details

            if (newProjectModel.Details != oldProject.Details)
            {
                LogEvent(transactionId, new EventRequestBaseModel
                {
                    EntityId = projectId,
                    ProjectId = projectId,
                    ClassificationKey = "ProjectDetailsEdit",
                    UserId = userId,
                    NewValue = newProjectModel.Details,
                    OldValue = oldProject.Details,
                    Comment = standardCommentForProjectPart
                });
            }

            #endregion
            #region Portfolio/Product Labels & Project Tags 

            if (tagsLoadingTask != null)
            {
                // tagsLoadingTask is NULL, if there is nothing to load. Means, no Porflolio and Project tags exist for current Project part
                // Get added an removed Portfolio Tags
                var oldPortfolioTags = (oldProject.PortfolioTags != null) ? oldProject.PortfolioTags : new List<Guid>();
                var newPortfolioTags = (newProjectModel.PortfolioTags != null) ? newProjectModel.PortfolioTags : new List<Guid>();
                var addedPortfolioTags = newPortfolioTags.Except(oldPortfolioTags).ToList();
                var removedPortfolioTags = oldPortfolioTags.Except(newPortfolioTags).ToList();

                // Get added an removed Project Tags
                var oldProjectTags = (oldProject.ProjectTags != null) ? oldProject.ProjectTags : new List<Guid>();
                var newProjectTags = (newProjectModel.ProjectTags != null) ? newProjectModel.ProjectTags : new List<Guid>();
                var addedProjectTags = newProjectTags.Except(oldProjectTags).ToList();
                var removedProjectTags = oldProjectTags.Except(newProjectTags).ToList();

                var createdEventRecords = new List<EventRequestBaseModel>();
                tagsLoadingTask.Wait();

                // Events for removed Portfolio Tags / Labels
                foreach (var tagId in removedPortfolioTags)
                {
                    var tagName = FormatTagNameForEventsLog(tagId, tagsLoadingTask.Result);
                    eventRecord = new EventRequestBaseModel(projectId, userId, "ProjectPortfolioProductLabelsDelete",
                        oldValue: tagName, projectId: projectId, comment: standardCommentForProjectPart);
                    createdEventRecords.Add(eventRecord);
                }

                // Events for added Portfolio Tags / Labels
                foreach (var tagId in addedPortfolioTags)
                {
                    var tagName = FormatTagNameForEventsLog(tagId, tagsLoadingTask.Result);
                    eventRecord = new EventRequestBaseModel(projectId, userId, "ProjectPortfolioProductLabelsAdd",
                        newValue: tagName, projectId: projectId, comment: standardCommentForProjectPart);
                    createdEventRecords.Add(eventRecord);
                }

                // Events for removed Project Tags
                foreach (var tagId in removedProjectTags)
                {
                    var tagName = FormatTagNameForEventsLog(tagId, tagsLoadingTask.Result);
                    eventRecord = new EventRequestBaseModel(projectId, userId, "ProjectProjectTagsDelete",
                        oldValue: tagName, projectId: projectId, comment: standardCommentForProjectPart);
                    createdEventRecords.Add(eventRecord);
                }

                // Events for added Project Tags
                foreach (var tagId in addedProjectTags)
                {
                    var tagName = FormatTagNameForEventsLog(tagId, tagsLoadingTask.Result);
                    eventRecord = new EventRequestBaseModel(projectId, userId, "ProjectProjectTagsAdd",
                        newValue: tagName, projectId: projectId, comment: standardCommentForProjectPart);
                    createdEventRecords.Add(eventRecord);
                }

                if (createdEventRecords.Count > 0)
                    AuditProxy.LogEvent(transactionId, createdEventRecords);
            }

            #endregion
            #region Project Dependency

            if (oldProject.Dependencies != null && newProjectModel.Dependencies != null)
            {
                foreach (var oldDependencies in oldProject.Dependencies)
                {
                    if (!newProjectModel.Dependencies.Contains(oldDependencies))
                    {
                        LogEvent(transactionId,
                            new EventRequestBaseModel(projectId, userId, "ProjectDependencyDelete",
                                oldValue: oldDependencies.DependencyTypeDisplayName, projectId: projectId, comment: standardCommentForProjectPart));
                    }
                }
                foreach (var newDependencies in newProjectModel.Dependencies)
                {
                    if (!oldProject.Dependencies.Contains(newDependencies))
                    {

                        LogEvent(transactionId,
                            new EventRequestBaseModel(projectId, userId, "ProjectDependencyAdd",
                                newValue: newDependencies.DependencyTypeDisplayName, projectId: projectId, comment: standardCommentForProjectPart));
                    }
                }
            }

            #endregion
            #region Performance Indicators 

            if (newProjectModel.UseThreshold != oldProject.UseThreshold)
            {
                LogEvent(transactionId, new EventRequestBaseModel
                {
                    EntityId = projectId,
                    ProjectId = projectId,
                    ClassificationKey = "PerformanceIndicatorsChange",
                    UserId = userId,
                    NewValue = newProjectModel.UseThreshold ? "Yes" : "No",
                    OldValue = oldProject.UseThreshold ? "Yes" : "No",
                    Comment = standardCommentForProjectPart
                });
            }

            #endregion
            #region Teams

            if (oldProject.AssignedTeams != null && newProjectModel.AssignedTeams != null)
            {
                var tagManager = new TeamManager(DbContext);
                foreach (var oldAssignedTeams in oldProject.AssignedTeams)
                {
                    if (!newProjectModel.AssignedTeams.Contains(oldAssignedTeams))
                    {
                        var name = string.Empty;
                        var item = tagManager.LoadTeamModel(oldAssignedTeams);
                        if (item != null)
                        {
                            name = item.Name;
                        }
                        LogEvent(transactionId,
                            new EventRequestBaseModel(projectId, userId, "ProjectTeamsDelete",
                                oldValue: name, projectId: projectId, comment: standardCommentForProjectPart));
                    }
                }
                foreach (var newAssignedTeams in newProjectModel.AssignedTeams)
                {
                    if (!oldProject.AssignedTeams.Contains(newAssignedTeams))
                    {
                        var name = string.Empty;
                        var item = tagManager.LoadTeamModel(newAssignedTeams);
                        if (item != null)
                        {
                            name = item.Name;
                        }
                        LogEvent(transactionId,
                            new EventRequestBaseModel(projectId, userId, "ProjectTeamsAdd",
                                newValue: name, projectId: projectId, comment: standardCommentForProjectPart));
                    }
                }
            }

            #endregion
            #region Strategic Goals

            if (oldProject.StrategicGoals != null && newProjectModel.StrategicGoals != null)
            {
                var manager = new StrategicGoalManager(DbContext);

                var deletedGoals = oldProject.StrategicGoals.Except(newProjectModel.StrategicGoals).ToList();
                var newGoals = newProjectModel.StrategicGoals.Except(oldProject.StrategicGoals).ToList();

                foreach (var deletedGoal in deletedGoals)
                {
                    var name = string.Empty;
                    var item = manager.GetStrategicGoalById(deletedGoal);
                    if (item != null)
                    {
                        name = item.Name;
                    }
                    LogEvent(transactionId,
                        new EventRequestBaseModel(projectId, userId, "ProjectStrategicGoalsDelete",
                            oldValue: name, projectId: projectId, comment: standardCommentForProjectPart));
                }

                foreach (var newGoal in newGoals)
                {
                    var name = string.Empty;
                    var item = manager.GetStrategicGoalById(newGoal);
                    if (item != null)
                    {
                        name = item.Name;
                    }
                    LogEvent(transactionId,
                        new EventRequestBaseModel(projectId, userId, "ProjectStrategicGoalsAdd",
                            newValue: name, projectId: projectId, comment: standardCommentForProjectPart));
                }

                //foreach (var oldStrategicGoals in oldProject.StrategicGoals)
                //{
                //    if (!newProjectModel.StrategicGoals.Contains(oldStrategicGoals))
                //    {
                //        var name = string.Empty;
                //        var item = manager.GetStrategicGoalById(oldStrategicGoals);
                //        if (item != null)
                //        {
                //            name = item.Name;
                //        }
                //        LogEvent(transactionId,
                //            new EventRequestBaseModel(projectId, userId, "ProjectStrategicGoalsDelete",
                //                oldValue: name, projectId: projectId));
                //    }
                //}
                //foreach (var newStrategicGoals in newProjectModel.StrategicGoals)
                //{
                //    if (!oldProject.StrategicGoals.Contains(newStrategicGoals))
                //    {
                //        var name = string.Empty;
                //        var item = manager.GetStrategicGoalById(newStrategicGoals);
                //        if (item != null)
                //        {
                //            name = item.Name;
                //        }
                //        LogEvent(transactionId,
                //            new EventRequestBaseModel(projectId, userId, "ProjectStrategicGoalsAdd",
                //                newValue: name, projectId: projectId));
                //    }
                //}
            }

            #endregion
            #region Internal Contacts

            if (oldProject.InternalContacts != null && newProjectModel.InternalContacts != null)
            {
                var manager = new ContactManager(DbContext);
                foreach (var oldItem in oldProject.InternalContacts)
                {
                    if (!newProjectModel.InternalContacts.Contains(oldItem))
                    {
                        var name = string.Empty;
                        var item = manager.LoadContactModel(oldItem);
                        if (item != null)
                        {
                            name = item.FirstName + item.LastName;
                        }
                        LogEvent(transactionId,
                            new EventRequestBaseModel(projectId, userId, "ProjectInternalContactsDelete",
                                oldValue: name, projectId: projectId, comment: standardCommentForProjectPart));
                    }
                }
                foreach (var newItem in newProjectModel.InternalContacts)
                {
                    if (!oldProject.InternalContacts.Contains(newItem))
                    {
                        var name = string.Empty;
                        var item = manager.LoadContactModel(newItem);
                        if (item != null)
                        {
                            name = item.FirstName + item.LastName;
                        }
                        LogEvent(transactionId,
                            new EventRequestBaseModel(projectId, userId, "ProjectInternalContactsAdd",
                                newValue: name, projectId: projectId, comment: standardCommentForProjectPart));
                    }
                }
            }

            #endregion
            #region External Contacts

            if (oldProject.ExternalContacts != null && newProjectModel.ExternalContacts != null)
            {
                var manager = new ContactManager(DbContext);
                foreach (var oldItem in oldProject.ExternalContacts)
                {
                    if (!newProjectModel.ExternalContacts.Contains(oldItem))
                    {
                        var name = string.Empty;
                        var item = manager.LoadContactModel(oldItem);
                        if (item != null)
                        {
                            name = item.FirstName + item.LastName;
                        }
                        LogEvent(transactionId,
                            new EventRequestBaseModel(projectId, userId, "ProjectExternalContactsDelete",
                                oldValue: name, projectId: projectId, comment: standardCommentForProjectPart));
                    }
                }
                foreach (var newItem in newProjectModel.ExternalContacts)
                {
                    if (!oldProject.ExternalContacts.Contains(newItem))
                    {
                        var name = string.Empty;
                        var item = manager.LoadContactModel(newItem);
                        if (item != null)
                        {
                            name = item.FirstName + item.LastName;
                        }
                        LogEvent(transactionId,
                            new EventRequestBaseModel(projectId, userId, "ProjectExternalContactsAdd",
                                newValue: name, projectId: projectId, comment: standardCommentForProjectPart));
                    }
                }
            }

            #endregion
            #region Workflow Contacts

            if (oldProject.WorkFlowContacts != null && newProjectModel.WorkFlowContacts != null)
            {
                var manager = new ContactManager(DbContext);
                foreach (var oldItem in oldProject.WorkFlowContacts)
                {
                    if (!newProjectModel.WorkFlowContacts.Contains(oldItem))
                    {
                        var name = string.Empty;
                        var item = manager.LoadContactModel(oldItem);
                        if (item != null)
                        {
                            name = item.FirstName + item.LastName;
                        }
                        LogEvent(transactionId,
                            new EventRequestBaseModel(projectId, userId, "ProjectWorkflowContactsDelete",
                                oldValue: name, projectId: projectId, comment: standardCommentForProjectPart));
                    }
                }
                foreach (var newItem in newProjectModel.WorkFlowContacts)
                {
                    if (!oldProject.WorkFlowContacts.Contains(newItem))
                    {
                        var name = string.Empty;
                        var item = manager.LoadContactModel(newItem);
                        if (item != null)
                        {
                            name = item.FirstName + item.LastName;
                        }
                        LogEvent(transactionId,
                            new EventRequestBaseModel(projectId, userId, "ProjectWorkflowContactsAdd",
                                newValue: name, projectId: projectId, comment: standardCommentForProjectPart));
                    }
                }
            }

            #endregion
            #region Attachments

            if (oldProject.Attachments != null && newProjectModel.Attachments != null)
            {
                foreach (var oldItem in oldProject.Attachments.ToDictionary(q => q.Id, q => q.Name))
                {
                    if (!newProjectModel.Attachments.Select(q => q.Id).Contains(oldItem.Key))
                    {
                        LogEvent(transactionId,
                            new EventRequestBaseModel(projectId, userId, "ProjectAttachmentsDelete",
                                oldValue: oldItem.Value, projectId: projectId, comment: standardCommentForProjectPart));
                    }
                }
                foreach (var newItem in newProjectModel.Attachments.ToDictionary(q => q.Id, q => q.Name))
                {
                    if (!oldProject.Attachments.Select(q => q.Id).Contains(newItem.Key))
                    {
                        LogEvent(transactionId,
                            new EventRequestBaseModel(projectId, userId, "ProjectAttachmentsAdd",
                                newValue: newItem.Value, projectId: projectId, comment: standardCommentForProjectPart));
                    }
                }
            }

            #endregion
            #region Links

            if (oldProject.Links != null && newProjectModel.Links != null)
            {
                foreach (var oldItem in oldProject.Links.ToDictionary(q => q.Id, q => q.Name))
                {
                    if (!newProjectModel.Links.Select(q => q.Id).Contains(oldItem.Key))
                    {
                        LogEvent(transactionId,
                            new EventRequestBaseModel(projectId, userId, "ProjectLinksDelete",
                                oldValue: oldItem.Value, projectId: projectId, comment: standardCommentForProjectPart));
                    }
                }
                foreach (var newItem in newProjectModel.Links.ToDictionary(q => q.Id, q => q.Name))
                {
                    if (!oldProject.Links.Select(q => q.Id).Contains(newItem.Key))
                    {
                        LogEvent(transactionId,
                            new EventRequestBaseModel(projectId, userId, "ProjectLinksAdd",
                                newValue: newItem.Value, projectId: projectId, comment: standardCommentForProjectPart));
                    }
                }
            }

            #endregion
        }

        public async Task<HistoryDisplayModelTotal> GetProjectEvents(Guid entityId, int take = 25, int skip = 0, string orderBy = "DateCreated", bool isOrderAsc = false)
        {
            // Get project parts, if exist
            IList<Guid> projectAndPartsIds = new List<Guid>() { entityId };

			var masterProject = this.Load(entityId, true);
			if ((masterProject != null) && masterProject.HasChildren)
			{
				projectAndPartsIds = this.GetSubprojectsAndParts(masterProject);
			}
            // as we get history events and workflow events from different data source
            // so we should get take + skip elements from both sources and then 
            // merge them, sort and only after that apply skip and take operators, otherwise paging wont work correctly
			var auditEvents = await GetEvents(projectAndPartsIds, take + skip, 0, orderBy, isOrderAsc);
            var wfEvents = await WorkflowManager.GetWorkflowHistory4Project(string.Empty, entityId, take + skip, 0, orderBy, isOrderAsc);
            var events = MergeEventsWithWorkflowHistory(auditEvents, wfEvents, take, skip, orderBy, isOrderAsc);
            if (events == null)
                return new HistoryDisplayModelTotal();
            var eventUserIds = events.Data?.Select(w => w.UserId).Distinct();
            if (eventUserIds == null)
                return new HistoryDisplayModelTotal();

            var users = UsersCache.Value.Where(q => eventUserIds.Contains(q.Id.ToString().ToLower()))
                .ToDictionary(q => q.Id.ToString().ToLower(), q => $"{q.FirstName} {q.LastName}");
	        var eventsModel = new List<HistoryDisplayModel>();
            foreach (var eventItem in events.Data)
            {
                string userName = string.Empty;
                if (!string.IsNullOrEmpty(eventItem.UserId?.ToLower()))
                    users.TryGetValue(eventItem.UserId?.ToLower(), out userName);
	            eventsModel.Add("Workflow".Equals(eventItem.ClassificationTitle)
		            ? new HistoryDisplayModel(eventItem, userName?.Trim(), "Workflow")
		            : new HistoryDisplayModel(eventItem, userName?.Trim()));
            }
            return new HistoryDisplayModelTotal
            {
                Total = events.Total,
                Data = eventsModel
            };
        }

        #endregion

        /// <summary>
        /// 
        /// </summary>
        /// <param name="projectId">A <see cref="Project"/> Id which Team2Project records we're going to update.</param>
        /// <param name="teams">new project teams to save or just new teams to add if <paramref name="appendProjectTeamsToScenario"/> is true.</param>
        /// <param name="excludeScenarioId">Scenario to exclude from processing</param>
        /// <param name="appendProjectTeamsToScenario"><b>true</b> - append teams from <paramref name="teams"/> to current project teams. 
        /// <b>false</b> - replace current <see cref="Team2Project"/> records with teams specified in <paramref name="teams"/>.</param>
        /// <param name="recalculateScenarioAllocations">Indicates whether to delete redundant team and resource allocations (and recalculate BU scenario details accordingly) or not.</param>
        /// <returns>An updated list of proejct teams. It could contain some teams not exist in <paramref name="teams"/> which system cannot delete due to any restrictions.</returns>
        public List<Guid> UpdateProjectTeams(Guid projectId, List<Guid> teamsToAdd, Guid? excludeScenarioId, bool appendProjectTeamsToScenario, bool recalculateScenarioAllocations = false)
        {
            var project = DbContext.Projects.FirstOrDefault(x => x.Id == projectId);
            if (project == null)
                return teamsToAdd;

            return UpdateProjectTeams(project, teamsToAdd, excludeScenarioId, appendProjectTeamsToScenario, recalculateScenarioAllocations);
        }

        /// <summary>
        /// Adds new teams to the project and all (except actuals) its scenarios, except the given one. Transaction usage REQUIRED if <paramref name="recalculateScenarioAllocations"/> is true.
        /// </summary>
        /// <param name="project">A <see cref="Project"/> record which Team2Project records we're going to update.</param>
        /// <param name="teams">new project teams to save or just new teams to add if <paramref name="appendProjectTeamsToScenario"/> is true.</param>
        /// <param name="excludeScenarioId">Scenario to exclude from processing</param>
        /// <param name="appendProjectTeamsToScenario"><b>true</b> - append teams from <paramref name="teams"/> to current project teams. 
        /// <b>false</b> - replace current <see cref="Team2Project"/> records with teams specified in <paramref name="teams"/>.</param>
        /// <param name="recalculateScenarioAllocations">Indicates whether to delete redundant team and resource allocations (and recalculate BU scenario details accordingly) or not.</param>
        /// <returns>An updated list of proejct teams. It could contain some teams not exist in <paramref name="teams"/> which system cannot delete due to any restrictions.</returns>
        public List<Guid> UpdateProjectTeams(Project project, List<Guid> teams, Guid? excludeScenarioId, bool appendProjectTeamsToScenario, bool recalculateScenarioAllocations = false, Dictionary<Guid, Dictionary<Guid, IEnumerable<RateModel>>> rates = null)
        {
            if (project == null)
                return teams;

            // The result team set for project
            var resultProjectTeams = teams;
            if (appendProjectTeamsToScenario)
            {
                var currentProjectTeams = DbContext.Team2Project.Where(x => x.ProjectId == project.Id).Select(y => y.TeamId).ToList();
                var newTeams = teams.Except(currentProjectTeams);
                resultProjectTeams = currentProjectTeams.Union(newTeams).ToList();
            }

            resultProjectTeams = SaveTeam2Projects(project, resultProjectTeams);

            // Get scenarios to update
            List<Guid> scenariosToUpdate = ScenarioManager.GetProjectNonActualsScenarios(project.Id);

            if (excludeScenarioId.HasValue && !excludeScenarioId.Value.Equals(Guid.Empty))
                scenariosToUpdate.Remove(excludeScenarioId.Value);

            // Perform update of scenario allocations (SD, TA and RA)
            if (recalculateScenarioAllocations)
            {
                if (rates == null)
                {
                    var rateManager = new RateManager(DbContext);
                    rates = rateManager.GetRatesDict(scenariosToUpdate, rateManager.LoadRatesByScenarios(scenariosToUpdate));
                }
                ScenarioManager.RecalculateScenarioAllocationsForRemovedTeams(scenariosToUpdate, resultProjectTeams, rates);
            }

            return resultProjectTeams;
        }

        public void SaveContacts(Project project, List<Guid> internalContacts, List<Guid> externalContacts)
        {
            if (externalContacts == null)
                externalContacts = new List<Guid>();

            if (internalContacts == null)
                internalContacts = new List<Guid>();

            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
                });
            }
        }

        /// <summary>												
        /// Deletes and adds tems to project according to incoming team list.
        /// </summary>
        /// <param name="project"></param>
        /// <param name="allProjectTeamsToSave"></param>
        /// <returns>Result teams list (some teams may be not deleted because of actuals existing)</returns>
        /// <remarks>This method is just a subtask of updating project teams BLL task (see UpdateProjectTeams method). Don't use it separately to avoid data inconsistency.</remarks>
        private List<Guid> SaveTeam2Projects(Project project, List<Guid> allProjectTeamsToSave)
        {
            if (project == null || project.Id == Guid.Empty)
                return allProjectTeamsToSave;

            if (allProjectTeamsToSave == null)
                allProjectTeamsToSave = new List<Guid>();

            var result = allProjectTeamsToSave.Clone();
            var projectsIds = new List<Guid> { 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 => allProjectTeamsToSave.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 =
                    (from ta in DbContext.TeamAllocations
                     join s in DbContext.Scenarios on ta.ScenarioId equals s.Id
                     join t in DbContext.Teams on ta.TeamId equals t.Id
                     join u2t in DbContext.User2Team on t.Id equals u2t.TeamId
                     where (ta.Quantity > 0) && s.ParentId.HasValue && projectsIds.Contains(s.ParentId.Value) &&
                        (s.ParentId != project.Id)
                     select u2t.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

            // Get teams usage info
            var teamsUsageInfo = this.GetProjectTeamsUsageInfo(project.Id);

            foreach (var t in oldTeamsOfCurrentProject)
            {
                if (!allProjectTeamsToSave.Contains(t.TeamId))
                {
                    if (!teamsUsageInfo.ContainsKey(t.TeamId) || !teamsUsageInfo[t.TeamId].HasActualsResourceAllocations)
                    {
                        // Team has no actuals assigned, and may be removed
                        DbContext.Team2Project.Remove(t);
                    }
                    else
                    {
                        // Team can not be deleted because of existing actuals for it. Add it back to result list
                        if (!result.Contains(t.TeamId))
                            result.Add(t.TeamId);
                    }
                }
            }
            foreach (var tId in allProjectTeamsToSave.Except(oldTeamIds))
            {
                DbContext.Team2Project.Add(new Team2Project()
                {
                    Id = Guid.NewGuid(),
                    ProjectId = project.Id,
                    TeamId = tId
                });
                var wfMan = new WorkFlowManager(this.DbContext);
                //do notifications for new teams on project/scenario
                foreach (var s in project.Scenarios)
                {
                    var state = wfMan.GetCurrentState(s.Id);

                }
            }

            #endregion

            return result;
        }

        public void SaveStrategicGoals(Project project, List<Guid> strategicGoals)
        {
            if (project == null || project.Id == Guid.Empty)
                return;

            if (strategicGoals == null)
                strategicGoals = new List<Guid>();

            #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
        }

        /// <summary>
        /// Returns list of IDs for all child projects and parts for given project via recursive search 
        /// (including given project ID)
        /// </summary>
        /// <param name="project">Root project for the search</param>
        /// <returns></returns>
        public IList<Guid> GetSubprojectsAndParts(Project project)
        {
            List<Guid> result = new List<Guid>();

            if (project == null)
                return result;

            if (project.ChildProjects != null)
            {
                foreach (Project child in project.ChildProjects)
                {
                    IList<Guid> subProjects = GetSubprojectsAndParts(child);
                    result.AddRange(subProjects);
                }
            }

            result.Add(project.Id);
            return result;
        }

        public List<Project> FindProjects(IEnumerable<Guid> projectIds)
        {
            if (projectIds == null || !projectIds.Any())
                return new List<Project>();

            return DbContext.Projects.Where(x => projectIds.Contains(x.Id)).ToList();
        }

        /// <summary>
        /// Returns projects, that has scenarios with resource allocations for resources, linked
        /// to specified userId by e-mail field
        /// </summary>
        /// <param name="userId">User Id to get projects for</param>
        public List<Guid> GetAssignedProjectsByUserId(Guid userId)
        {
            List<Guid> result = null;

            var projectsAndParts =
                (from usr in DbContext.AspNetUsers
                 join pr in DbContext.PeopleResources on usr.Email equals pr.Email
                 join prAlloc in DbContext.PeopleResourceAllocations on pr.Id equals prAlloc.PeopleResourceId
                 join scen in DbContext.Scenarios on prAlloc.ScenarioId equals scen.Id
                 join prjPrt in DbContext.Projects on scen.ParentId equals prjPrt.Id
                 where scen.Status == (int)ScenarioStatus.Active
                 select new
                 {
                     Id = prjPrt.Id,
                     ParentProjectId = prjPrt.ParentProjectId
                 }).Distinct().AsNoTracking().ToList();

            result =
                projectsAndParts.Select(x => x.ParentProjectId ?? x.Id).Distinct().ToList();

            return result;
        }

        public List<ProjectListModel> 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);
            }
        }

        public List<ProjectWithChildrenView> GetProjectsWithChildren(Guid userId, LoadProjectItems includedItems = LoadProjectItems.None)
        {
            var baseQuery = GetProjectWithChildrenViewQuery(userId);
            var projectsDict = baseQuery.Where(t => !t.HasChildren).GroupBy(x => x.Id).ToDictionary(t => t.Key, el => el.FirstOrDefault());
            var projects = projectsDict.Values.ToList();
            FillProjectsWithChildren(projectsDict, includedItems);
            return projects;
        }

        public List<ProjectWithChildrenView> GetProjectsWithChildrenByCompany(Guid companyId, LoadProjectItems includedItems = LoadProjectItems.All)
        {
            var result = this.GetProjectsWithChildrenByCompanies(new List<Guid>() { companyId }, includedItems);
            return result;
        }

        public List<ProjectWithChildrenView> GetProjectsWithChildrenByCompanies(List<Guid> companies, LoadProjectItems includedItems = LoadProjectItems.All)
        {
            var userId = SecurityManager.GetUserPrincipal();
            var userCompanies =
                (new CompanyManager(DbContext)).GetCompaniesByUser(null)
                .Where(x => companies.Contains(x.Id) || (x.ParentCompanyId.HasValue && companies.Contains(x.ParentCompanyId.Value)))
                .ToList();

            var companyIds = userCompanies.Select(x => x.Id);
            var baseQuery = GetProjectWithChildrenViewQuery(userId);

            var projectsDict = (from project in baseQuery
                                where !project.HasChildren &&
                                      ((project.CompanyId.HasValue && companyIds.Contains(project.CompanyId.Value)) ||
                                       (project.ParentProject != null && project.ParentProject.CompanyId.HasValue && companyIds.Contains(project.ParentProject.CompanyId.Value)))
                                select project).GroupBy(x => x.Id).ToDictionary(t => t.Key, el => el.FirstOrDefault());
            var projects = projectsDict.Values.ToList();
            FillProjectsWithChildren(projectsDict, includedItems);

            return projects;
        }

        /// <summary>
        /// Get projects, filtered by user access permissions and given teams
        /// </summary>
        /// <param name="teams">A collection of team Ids.</param>
        /// <param name="tags">A collection of tag Ids.</param>
        /// <returns>A list of <see cref="Project"/> objects.</returns>
        public List<ProjectWithChildrenView> GetProjectsWithChildrenByTeams(IEnumerable<Guid> teams, IEnumerable<Guid> tags = null, LoadProjectItems includedItems = LoadProjectItems.All)
        {
            var userId = SecurityManager.GetUserPrincipal();

            if (teams == null || !teams.Any())
                return new List<ProjectWithChildrenView>();

            var baseQuery = GetProjectWithChildrenViewQuery(userId);
            var query = (from project in baseQuery
                         join t2p in DbContext.Team2Project.AsNoTracking() on project.Id equals t2p.ProjectId
                         where teams.Contains(t2p.TeamId) && !project.HasChildren
                         select project);
            if (tags != null && tags.Any())
                query = query.Where(x => DbContext.TagLinks.Any(tl => tl.ParentID == x.Id && tags.Contains(tl.TagID)));

            var projectsDict = query.AsParallel().GroupBy(x => x.Id).ToDictionary(t => t.Key, el => el.FirstOrDefault());
            var projects = projectsDict.Values.ToList();
            FillProjectsWithChildren(projectsDict, includedItems);
            return projects;
        }

        public List<ProjectWithChildrenView> GetProjectsWithChildrenByView(Guid viewId, LoadProjectItems includedItems = LoadProjectItems.All)
        {
            if (viewId.Equals(Guid.Empty))
                throw new ArgumentNullException("viewId");

            var userId = SecurityManager.GetUserPrincipal();
            var projects = new List<ProjectWithChildrenView>();
            var companyMngr = new CompanyManager(DbContext);
            var teamsMngr = new TeamManager(DbContext);
            var viewsMngr = new ViewManager(DbContext);

            // Get companies from view
            var viewCompanies = new List<Guid>();
            var viewData = viewsMngr.LoadWithChildCollections(viewId, true);
            if (viewData == null)
                return projects;

            if (viewData.Companies != null)
            {
                var companies = companyMngr.GetCompaniesByUserFiltered(viewData.Companies.ToList()).ToList();
                viewCompanies = companies.Select(x => x.Id).ToList();
            }

            // Get teams from view
            var viewTeams = teamsMngr.GetTeamsByViewsByUser(new List<Guid>() { viewId }, userId).Select(t => t.TeamId);

            var projectsViaCompanies = this.GetProjectsWithChildrenByCompanies(viewCompanies, includedItems: LoadProjectItems.None);
            var projectsViaTeams = this.GetProjectsWithChildrenByTeams(viewTeams, includedItems: LoadProjectItems.None);

            // Get projects from companies, which where not got from teams
            var projectsViaTeamsIds = projectsViaTeams.Select(x => x.Id).ToList();
            var newProjectsViaCompanies = projectsViaCompanies.Where(x => !projectsViaTeamsIds.Contains(x.Id)).ToList();

            projects.AddRange(projectsViaTeams);
            projects.AddRange(newProjectsViaCompanies);

            var projectsDict = projects.GroupBy(t => t.Id).ToDictionary(k => k.Key, el => el.FirstOrDefault());
            FillProjectsWithChildren(projectsDict, includedItems);
            return projects;
        }

        public List<ProjectWithChildrenView> GetProjectsWithChildrenByStatusAndClient(IEnumerable<Guid> statuses, IEnumerable<Guid> clients, IEnumerable<Guid> projects, LoadProjectItems includedItems = LoadProjectItems.All)
        {
            var userId = SecurityManager.GetUserPrincipal();
            var query = GetProjectWithChildrenViewQuery(userId).Where(x => !x.HasChildren);

            if (statuses != null && statuses.Any())
                query = query.Where(x => statuses.Contains(x.StatusId));
            if (clients != null && clients.Any())
                query = query.Where(x => x.ClientId.HasValue && clients.Contains(x.ClientId.Value));
            if (projects != null && projects.Any())
                query = query.Where(x => projects.Contains(x.Id));

            var projectsDict = query.AsParallel().GroupBy(x => x.Id).ToDictionary(t => t.Key, el => el.FirstOrDefault());
            FillProjectsWithChildren(projectsDict, includedItems);

            return projectsDict.Values.ToList();
        }

        public List<ProjectWithChildrenView> GetProjectsWithChildrenByResource(Guid resourceId, LoadProjectItems includedItems = LoadProjectItems.All)
        {
            if (resourceId == Guid.Empty)
                return new List<ProjectWithChildrenView>();

            var userId = SecurityManager.GetUserPrincipal();
            var baseQuery = GetProjectWithChildrenViewQuery(userId);
            var projectsDict = (from resAllocation in DbContext.PeopleResourceAllocations
                                join scen in DbContext.Scenarios on resAllocation.ScenarioId equals scen.Id
                                join proj in baseQuery on scen.ParentId equals proj.Id
                                where resAllocation.PeopleResourceId.Equals(resourceId) && !proj.HasChildren
                                select proj).GroupBy(x => x.Id).ToDictionary(t => t.Key, el => el.FirstOrDefault());
            var projects = projectsDict.Values.ToList();
            FillProjectsWithChildren(projectsDict, includedItems);

            return projects;
        }

        /// <summary>
        /// Method used to load scenarios for already loaded projects. Intended to be used in GetProjectsWithChildrenBy... methods 
        /// for on-demand loading of only requried objects the same way in all methods.
        /// </summary>
        /// <param name="projectIds">A collection of project Ids.</param>
        /// <returns>Dictionary where key=project.Id and value=collection of scenarios for the specified project.</returns>
        /// <remarks>
        /// Do not intended to be used as standalone public method. For general purpose of loading project scenarios use 
        /// ScenarioManager.GetScenarios4Projects method directly.
        /// </remarks>
        private Dictionary<Guid, IEnumerable<Scenario>> GetProjectScenarios(IEnumerable<Guid> projectIds)
        {
            var scenariomanager = new ScenarioManager(DbContext);
            return scenariomanager.GetScenarios4Projects(projectIds.ToList(), null, null, noTracking: true)
                .GroupBy(t => t.ParentId.Value).ToDictionary(t => t.Key, el => el.AsEnumerable());
        }
        /// <summary>
        /// Method used to load teams for already loaded projects. Intended to be used in GetProjectsWithChildrenBy... methods 
        /// for on-demand loading of only requried objects the same way in all methods.
        /// </summary>
        /// <param name="projectIds">A collection of project Ids.</param>
        /// <returns>Dictionary where key=project.Id and value=collection of team.Id for the specified project.</returns>
        /// <remarks>
        /// Do not intended to be used as standalone public method. For general purpose of loading project teams use 
        /// public methods.
        /// </remarks>
        private Dictionary<Guid, IEnumerable<Guid>> GetProjectTeams(IEnumerable<Guid> projectIds)
        {
            return DbContext.Team2Project.AsNoTracking().Where(t => projectIds.Contains(t.ProjectId))
                .GroupBy(t => t.ProjectId).ToDictionary(t => t.Key, el => el.Select(t => t.TeamId));
        }
        /// <summary>
        /// Method used to load strategic goals for already loaded projects. Intended to be used in GetProjectsWithChildrenBy... methods 
        /// for on-demand loading of only requried objects the same way in all methods.
        /// </summary>
        /// <param name="projectIds">A collection of project Ids.</param>
        /// <returns>Dictionary where key=project.Id and value=collection of StrategicGoal.Id for the specified project.</returns>
        /// <remarks>
        /// Do not intended to be used as standalone public method. For general purpose of loading project strategic goals use 
        /// public methods.
        /// </remarks>
        private Dictionary<Guid, IEnumerable<Guid>> GetProjectGoals(IEnumerable<Guid> projectIds)
        {
            return DbContext.StrategicGoal2Project.AsNoTracking().Where(t => projectIds.Contains(t.ProjectId))
                .GroupBy(t => t.ProjectId).ToDictionary(t => t.Key, el => el.Select(t => t.StrategicGoalId));
        }
        private void FillProjectsWithChildren(Dictionary<Guid, ProjectWithChildrenView> projectsDict, LoadProjectItems includedItems = LoadProjectItems.None)
        {
            if (includedItems == LoadProjectItems.None)
                return;

            Dictionary<Guid, IEnumerable<Scenario>> scenarios = new Dictionary<Guid, IEnumerable<Scenario>>();
            Dictionary<Guid, IEnumerable<Guid>> projectTeamsDict = new Dictionary<Guid, IEnumerable<Guid>>();
            Dictionary<Guid, IEnumerable<Guid>> projectGoalsDict = new Dictionary<Guid, IEnumerable<Guid>>();
            // load scenarios for all retrieved projects
            if (includedItems.HasFlag(LoadProjectItems.Scenarios))
            {
                scenarios = GetProjectScenarios(projectsDict.Keys.ToList());
            }
            // load Team2Project relations for all retrieved projects
            if (includedItems.HasFlag(LoadProjectItems.Teams))
            {
                projectTeamsDict = GetProjectTeams(projectsDict.Keys.ToList());
            }
            // load StrategicGoals2Project relations for all retrieved projects
            if (includedItems.HasFlag(LoadProjectItems.Goals))
            {
                projectGoalsDict = GetProjectGoals(projectsDict.Keys.ToList());
            }
            foreach (var project in projectsDict.Values)
            {
                if (scenarios.ContainsKey(project.Id))
                {
                    project.Scenarios = scenarios[project.Id].Select(x => new ProjectWithChildrenView.ScenarioView()
                    {
                        Id = x.Id,
                        ParentId = x.ParentId,
                        Name = x.Name,
                        IsBottomUp = x.IsBottomUp,
                        Status = (ScenarioStatus?)x.Status,
                        Type = (ScenarioType?)x.Type,
                        StartDate = x.StartDate,
                        EndDate = x.EndDate,
                        DateCreated = x.DateCreated,
                    }).ToList();
                }
                if (projectTeamsDict.ContainsKey(project.Id))
                    project.Teams = projectTeamsDict[project.Id].ToList();
                if (projectGoalsDict.ContainsKey(project.Id))
                    project.StrategicGoals = projectGoalsDict[project.Id].ToList();
            }
        }

        /// <summary>
        /// Return project teams. If filter specified, the result is filtered by existance in the filter
        /// </summary>
        /// <param name="projectId">Project Id to get teams of</param>
        /// <param name="filter">Filter for result or NULL</param>
        /// <returns></returns>
        private List<ProjectListModel> 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<ProjectListModel>();

            if (sortedColumn == null)
                sortedColumn = new SortedColumn("Name", "asc");

            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 == "Client")
            {
                columnName = "ClientName";
                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 = GetProjectsWithPartsThroughSortingGrouped(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_ProjectAccessByUserExtended.Where(x => x.UserId == userId && projectsIds.Contains(x.Id)).ToList();
            var projectsList = new List<ProjectListModel>();
            //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<ProjectPartListModel>(),
                            ReadOnly = (!project.Write.HasValue || (project.Write.Value < 1))
                        });
                        #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
                    });
                }
            }


            // Fill project models with comma-separated team names
            if (projectsList.Any())
            {
                var projectTeamsDict = GetProjectTeams(projectsIds);
                var teamManager = new TeamManager(DbContext);
                var teamsDict = teamManager.GetTeamsByUserFiltered(userId.ToString(), projectTeamsDict.SelectMany(t => t.Value).Distinct().ToList(), null, null)
                    .GroupBy(t => t.TeamId).ToDictionary(t => t.Key, el => el.FirstOrDefault());
                foreach (var project in projectsList)
                {
                    if (projectTeamsDict.ContainsKey(project.Id))
                        project.Teams = string.Join(", ",
                            teamsDict.Where(t => projectTeamsDict[project.Id].Any(x => t.Key == x)).Select(t => t.Value.Name)) ?? string.Empty;
                    if (project.ProjectParts != null && project.ProjectParts.Any())
                        foreach (var part in project.ProjectParts)
                        {
                            if (projectTeamsDict.ContainsKey(part.Id))
                                part.Teams = string.Join(", ",
                                    teamsDict.Where(t => projectTeamsDict[part.Id].Any(x => t.Key == x)).Select(t => t.Value.Name)) ?? string.Empty;
                        }
                }
            }
            return projectsList;
        }
        private List<ProjectListModel> 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<ProjectListModel>();

            if (sortedColumn == null)
                sortedColumn = new SortedColumn("ProjectName", "asc");

            if (sortedColumn.PropertyName == "Priority" ||
                sortedColumn.PropertyName == "Classification" ||
                sortedColumn.PropertyName == "Status" ||
                sortedColumn.PropertyName == "ActiveScenario" ||
                sortedColumn.PropertyName == "Client")
            {
                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 == "Client")
                {
                    columnName = "ClientName";
                }
                if (sortedColumn.PropertyName == "ActiveScenario")
                {
                    columnName = "ActiveScenarioName";
                }

                var projectsWithParts = GetProjectsWithPartsThroughSorting(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_ProjectAccessByUserExtended.Where(x => x.UserId == userId && projectsIds.Contains(x.Id)).ToList();
                var projectsList = new List<ProjectListModel>();

                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<ProjectPartListModel>(),
                            ReadOnly = (!project.Write.HasValue || (project.Write.Value < 1))
                        });
                    }

                    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
                    });
                }

                // Fill project models with comma-separated team names
                if (null != projectsList && projectsList.Any())
                {
                    var projectIds = projectsList.Select(t => t.Id).Union(projectsList.Where(t => t.ProjectParts != null && t.ProjectParts.Any())
                        .SelectMany(t => t.ProjectParts).Select(t => t.Id));
                    var projectTeamsDict = GetProjectTeams(projectIds);
                    var teamManager = new TeamManager(DbContext);
                    var teamsDict = teamManager.GetTeamsByUserFiltered(userId.ToString(), projectTeamsDict.SelectMany(t => t.Value).Distinct().ToList(), null, null)
                        .GroupBy(t => t.TeamId).ToDictionary(t => t.Key, el => el.FirstOrDefault());
                    foreach (var project in projectsList)
                    {
                        if (projectTeamsDict.ContainsKey(project.Id))
                            project.Teams = string.Join(", ",
                                teamsDict.Where(t => projectTeamsDict[project.Id].Any(x => t.Key == x)).Select(t => t.Value.Name)) ?? string.Empty;
                        if (project.ProjectParts != null && project.ProjectParts.Any())
                            foreach (var part in project.ProjectParts)
                            {
                                if (projectTeamsDict.ContainsKey(part.Id))
                                    part.Teams = string.Join(", ",
                                        teamsDict.Where(t => projectTeamsDict[part.Id].Any(x => t.Key == x)).Select(t => t.Value.Name)) ?? string.Empty;
                            }
                    }
                }
                return projectsList;
            }
            else
            {
                //sort
                var sortField = string.Empty;
                switch (sortedColumn.PropertyName)
                {
                    case "ProjectName":
                        sortField = "Name";
                        break;
                    //case "Teams":
                    //    if (sortedColumn.Direction == SortingDirection.Ascending)
                    //        query = query.OrderBy(x => x.Teams);
                    //    else
                    //        query = query.OrderByDescending(x => x.Teams);
                    //    break;
                    default:
                        sortField = sortedColumn.PropertyName;
                        break;
                }

                var totalCount = new ObjectParameter("totalCount", typeof(int));
                var mainProjects = DbContext.sp_ProjectSearch(userId, "%" + searchString + "%", startIndex, pageSize, sortField,
                    sortedColumn.Direction == SortingDirection.Ascending, totalCount).ToList();
                var projectIds = mainProjects.Select(t => t.Id);
                var projectParts = DbContext.VW_ProjectAccessByUserExtended.Where(t => t.UserId.Equals(userId) && t.ParentProjectId.HasValue && projectIds.Contains(t.ParentProjectId.Value))
                    .GroupBy(gr => gr.ParentProjectId.Value).ToDictionary(k => k.Key, el => el.ToList());
                var projectsToDisplay = mainProjects.Select(p => 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,
                    ReadOnly = (!p.Write.HasValue || (p.Write.Value < 1)),
                    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.ContainsKey(p.Id) ? projectParts[p.Id].Where(pp => string.IsNullOrEmpty(searchString) ||
                                                                                                   (p.Name ?? string.Empty).Contains(searchString) || (p.ProjectNumber ?? string.Empty).Contains(searchString) ||
                                                                                                   (p.ActiveScenarioName ?? string.Empty).Contains(searchString) || (pp.Name ?? string.Empty).Contains(searchString) ||
                                                                                                   (pp.ActiveScenarioName ?? string.Empty).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() : new List<ProjectPartListModel>()
                }).ToList();

                totalRecordCount = DbContext.VW_ProjectAccessByUserExtended.Count(x => x.UserId.Equals(userId) && !x.ParentProjectId.HasValue);

                searchRecordCount = Convert.ToInt32(totalCount.Value);

                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;
                }
                // Fill project models with comma-separated team names
                if (null != projectsToDisplay && projectsToDisplay.Any())
                {
                    var allProjectIds = projectsToDisplay.Select(t => t.Id).Union(projectsToDisplay.Where(t => t.ProjectParts != null && t.ProjectParts.Any())
                        .SelectMany(t => t.ProjectParts).Select(t => t.Id));
                    var projectTeamsDict = GetProjectTeams(allProjectIds);
                    var teamManager = new TeamManager(DbContext);
                    var teamsDict = teamManager.GetTeamsByUserFiltered(userId.ToString(), projectTeamsDict.SelectMany(t => t.Value).Distinct().ToList(), null, null)
                        .GroupBy(t => t.TeamId).ToDictionary(t => t.Key, el => el.FirstOrDefault());
                    foreach (var project in projectsToDisplay)
                    {
                        if (projectTeamsDict.ContainsKey(project.Id))
                            project.Teams = string.Join(", ",
                                teamsDict.Where(t => projectTeamsDict[project.Id].Any(x => t.Key == x)).Select(t => t.Value.Name)) ?? string.Empty;
                        if (project.ProjectParts != null && project.ProjectParts.Any())
                            foreach (var part in project.ProjectParts)
                            {
                                if (projectTeamsDict.ContainsKey(part.Id))
                                    part.Teams = string.Join(", ",
                                        teamsDict.Where(t => projectTeamsDict[part.Id].Any(x => t.Key == x)).Select(t => t.Value.Name)) ?? string.Empty;
                            }
                    }
                }

                return projectsToDisplay;
            }
        }
        /// <summary>
        /// Loads a collection of simple project models for the specified project Ids, INCLUDING not accessible to current user (if userId is not set).
        /// </summary>
        /// <param name="projectIds">A lsit of project.Id values to load.</param>
        /// <param name="userId">A user.Id of the current user.</param>
        /// <returns>A collection of project models with the specified <paramref name="projectIds"/>. If <paramref name="userId"/> is set 
        /// then returned collection contains only project accessible by current user.</returns>
        /// <remarks>
        /// This method loads all projects for Ids specified in argument, INCLUDING not accessible to current user (if userId is not set).
        /// </remarks>
        public IEnumerable<ProjectListModel> LoadProjects(IEnumerable<Guid> projectIds)
        {
            return DbContext.Projects.AsNoTracking().Include(t => t.ParentProject).Where(t => projectIds.Contains(t.Id)).Select(t => new ProjectListModel
            {
                Id = t.Id,
                Deadline = t.Deadline,
                HasChildren = t.HasChildren,
                PartNum = t.PartNum,
                Priority = t.Priority,
                Probability = (short)(t.Probability * 100),
                ProjectId = t.ParentProjectId ?? t.Id,
                ProjectName = t.ParentProject != null ? t.Name + ": " + t.ParentProject.Name : t.Name,
                ProjectNumber = t.ProjectNumber
            });
        }

        public List<Project> GetProjects(IEnumerable<Guid> projects, bool readOnly = false)
        {
            if (projects == null || !projects.Any())
                return new List<Project>();

            var projectsQuery = DbContext.Projects.Where(x => projects.Contains(x.Id));
            if (readOnly)
                projectsQuery = projectsQuery.AsNoTracking();

            return projectsQuery.ToList();
        }

        private List<Project2PartModel> GetProjectsWithPartsThroughSorting(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_ProjectAccessByUserExtended	p
LEFT
JOIN	dbo.VW_ProjectAccessByUserExtended	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
    p.ClientName    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" ? "decimal(5,2)" : "nvarchar(1600)");

            var totalRecordCountParam =
                new SqlParameter("@TotalRecordCount", SqlDbType.Int) { Direction = ParameterDirection.Output };

            var searchRecordCountParam =
                new SqlParameter("@SearchRecordCount", SqlDbType.Int) { Direction = ParameterDirection.Output };

            var projects = DbContext.Database.SqlQuery<Project2PartModel>(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<Project2PartModel> GetProjectsWithPartsThroughSortingGrouped(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_ProjectAccessByUserExtended	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_ProjectAccessByUserExtended	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" ? "decimal(5,2)" : "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<Project2PartModel>(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 void CopyScenariosToProject(Guid targetProjectId, List<Scenario> scenarios, Dictionary<Guid, List<ScenarioDetail>> details, Dictionary<Guid, List<Rate>> rates, List<Guid> assignedTeams)
        {
            if (targetProjectId == Guid.Empty)
                throw new InvalidOperationException("targetProjectId is empty");

            if (scenarios == null || scenarios.Count <= 0)
                throw new ArgumentNullException(nameof(scenarios));

            foreach (var scenario in scenarios)
            {
                if (scenario == null)
                    continue;

                var scenarioClone = new Scenario()
                {
                    Id = Guid.NewGuid(),
                    ParentId = targetProjectId,
                    TemplateId = scenario.TemplateId,
                    Type = scenario.Type,
                    Name = scenario.Name,
                    ProjectedRevenue = scenario.ProjectedRevenue,
                    ExpectedGrossMargin = scenario.ExpectedGrossMargin,
                    CalculatedGrossMargin = scenario.CalculatedGrossMargin,
                    CGSplit = scenario.CGSplit,
                    EFXSplit = scenario.EFXSplit,
                    StartDate = scenario.StartDate,
                    EndDate = scenario.EndDate,
                    Duration = scenario.Duration,
                    TDDirectCosts = scenario.TDDirectCosts,
                    BUDirectCosts = scenario.BUDirectCosts,
                    Shots = scenario.Shots,
                    TDRevenueShot = scenario.TDRevenueShot,
                    BURevenueShot = scenario.BURevenueShot,
                    FreezeRevenue = scenario.FreezeRevenue,
                    LastUpdate = DateTime.UtcNow,
                    Color = scenario.Color,
                    Status = scenario.Status,
                    UseLMMargin = scenario.UseLMMargin,
                    ExpectedGrossMargin_LM = scenario.ExpectedGrossMargin_LM,
                    CalculatedGrossMargin_LM = scenario.CalculatedGrossMargin_LM,
                    TDDirectCosts_LM = scenario.TDDirectCosts_LM,
                    BUDirectCosts_LM = scenario.BUDirectCosts_LM,
                    BURevenueShot_LM = scenario.BURevenueShot_LM,
                    ShotStartDate = scenario.ShotStartDate,
                    GrowthScenario = scenario.GrowthScenario,
                    Actuals_BUDirectCosts = scenario.Actuals_BUDirectCosts,
                    Actuals_BUDirectCosts_LM = scenario.Actuals_BUDirectCosts_LM,
                    SystemAttributeObjectID = scenario.SystemAttributeObjectID,
                    ProjectedExpense = scenario.ProjectedExpense,
                    CostSavings = scenario.CostSavings,
                    CostSavingsStartDate = scenario.CostSavingsStartDate,
                    CostSavingsEndDate = scenario.CostSavingsEndDate,
                    CostSavingsType = scenario.CostSavingsType,
                    CostSavingsDescription = scenario.CostSavingsDescription,
                    ROIDate = scenario.ROIDate,
                    DateCreated = DateTime.UtcNow,
                    IsBottomUp = scenario.IsBottomUp
                };

                scenarioClone.CostSavings1 = scenario.CostSavings1.Select(x => new CostSaving()
                {
                    Id = Guid.NewGuid(),
                    ScenarioId = scenarioClone.Id,
                    Year = x.Year,
                    Month = x.Month,
                    Cost = x.Cost
                }).ToList();

                if (assignedTeams != null && assignedTeams.Count > 0)
                {
                    scenarioClone.PeopleResourceAllocations = scenario.PeopleResourceAllocations
                                                                      .Where(x => assignedTeams.Contains(x.TeamId))
                                                                      .Select(x =>
                    new PeopleResourceAllocation()
                    {
                        Id = Guid.NewGuid(),
                        ScenarioId = scenarioClone.Id,
                        TeamId = x.TeamId,
                        PeopleResourceId = x.PeopleResourceId,
                        ExpenditureCategoryId = x.ExpenditureCategoryId,
                        WeekEndingDate = x.WeekEndingDate,
                        Quantity = x.Quantity
                    }).ToList();

                    scenarioClone.TeamAllocations = scenario.TeamAllocations
                                                            .Where(x => assignedTeams.Contains(x.TeamId))
                                                            .Select(x =>
                    new TeamAllocation()
                    {
                        Id = Guid.NewGuid(),
                        ScenarioId = scenarioClone.Id,
                        TeamId = x.TeamId,
                        ExpenditureCategoryId = x.ExpenditureCategoryId,
                        WeekEndingDate = x.WeekEndingDate,
                        Quantity = x.Quantity
                    }).ToList();
                }

                scenarioClone.Scenario2Group = scenario.Scenario2Group.Select(x => new Scenario2Group()
                {
                    Id = Guid.NewGuid(),
                    ScenarioId = scenarioClone.Id,
                    GroupId = x.GroupId
                }).ToList();

                // copy scenario local rates
                if (rates != null && rates.ContainsKey(scenario.Id))
                {
                    foreach (var rate in rates[scenario.Id])
                    {
                        DbContext.Rates.Add(new Rate()
                        {
                            Id = Guid.NewGuid(),
                            ParentId = scenarioClone.Id,
                            ExpenditureCategoryId = rate.ExpenditureCategoryId,
                            Rate1 = rate.Rate1,
                            StartDate = rate.StartDate,
                            EndDate = rate.EndDate,
                            FreezeRate = rate.FreezeRate,
                            Type = rate.Type,
                            DerivedId = rate.DerivedId
                        });
                    }
                }

                if (details != null && details.ContainsKey(scenario.Id))
                {
                    foreach (var detail in details[scenario.Id])
                    {
                        DbContext.ScenarioDetail.Add(new ScenarioDetail()
                        {
                            Id = Guid.NewGuid(),
                            ParentID = scenarioClone.Id,
                            ExpenditureCategoryId = detail.ExpenditureCategoryId,
                            WeekEndingDate = detail.WeekEndingDate,
                            Quantity = detail.Quantity,
                            WeekOrdinal = detail.WeekOrdinal,
                            Cost = detail.Cost,
                        });
                    }
                }

                DbContext.Scenarios.Add(scenarioClone);
            }
        }

        public VW_ProjectAccessByUserExtended GetProjectByUser(Guid userId, Guid projectId)
        {
            return DbContext.VW_ProjectAccessByUserExtended.FirstOrDefault(x => x.UserId == userId && x.Id == projectId);
        }

        private EventGetResponseModel MergeEventsWithWorkflowHistory(EventGetResponseModel events,
            ResponseModel<EventGetResponseModel> wfEvents, int take, int skip, string orderBy, bool isOrderAsc)
        {
            var data = new List<EventGetResponse>();
            if (events?.Data != null)
                data.AddRange(events.Data);
            if (wfEvents?.Result?.Data != null)
                data.AddRange(wfEvents.Result.Data);
            if (string.IsNullOrWhiteSpace(orderBy))
                orderBy = string.Empty;
            switch (orderBy.ToLower())
            {
                case "newvalue":
                    data = isOrderAsc ? data.OrderBy(t => t.NewValue).Skip(skip).Take(take).ToList() : data.OrderByDescending(t => t.NewValue).Skip(skip).Take(take).ToList();
                    break;
                case "oldvalue":
                    data = isOrderAsc ? data.OrderBy(t => t.OldValue).Skip(skip).Take(take).ToList() : data.OrderByDescending(t => t.OldValue).Skip(skip).Take(take).ToList();
                    break;
                default:
                    data = isOrderAsc ? data.OrderBy(t => t.DateCreated).Skip(skip).Take(take).ToList() : data.OrderByDescending(t => t.DateCreated).Skip(skip).Take(take).ToList();
                    break;
            }

            var total = events?.Total ?? 0;
            if (wfEvents?.Result != null)
                total += wfEvents.Result.Total;
            return new EventGetResponseModel
            {
                Data = data,
                Total = total
            };
        }

        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; }
        }

        #region Queries

        private IQueryable<ProjectWithChildrenView> GetProjectWithChildrenViewQuery(Guid userId)
        {
            var query = (from project in DbContext.VW_ProjectAccessByUserExtended
                         where project.UserId == userId
                         select new ProjectWithChildrenView()
                         {
                             Id = project.Id,
                             CompanyId = project.CompanyId,
                             CompanyName = project.CompanyName,
                             ClientId = project.ClientId,
                             ClientName = project.ClientName,
                             Name = project.Name,
                             TypeName = project.TypeName,
                             Deadline = project.Deadline,
                             Priority = project.Priority,
                             HasChildren = project.HasChildren,
                             Color = project.Color,
                             ReadOnly = (!project.Write.HasValue || project.Write.Value < 1),
                             StatusId = project.StatusId,
                             TypeId = project.TypeId,
                             Probability = project.Probability,
                             StatusName = project.StatusName,
                             PerformanceYellowThreshold = project.PerformanceYellowThreshold,
                             PerformanceRedThreshold = project.PerformanceRedThreshold,
                             ParentProjectId = project.ParentProjectId,
                             ParentProject = !project.ParentProjectId.HasValue ? null : new ProjectWithChildrenView.ParentProjectView()
                             {
                                 Id = project.ParentProjectId.Value,
                                 CompanyId = project.CompanyId,
                                 Name = project.ParentProjectName,
                                 Color = project.ParentProjectColor,
                             },
                         });
            return query;
        }

        #endregion
    }


    public class ProjectPartManager : ManagerBase<Project, ProjectPartModel>
    {
        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<Project> DataTable => DbContext.Projects;

        public override Project Save(ProjectPartModel model)
        {
            var project = base.Save(model);
            ProjectManager mngr = new ProjectManager(DbContext);

            mngr.SaveContacts(project, model.InternalContacts, model.ExternalContacts);

            model.AssignedTeams = mngr.UpdateProjectTeams(project, model.AssignedTeams, null, false, true);

            mngr.SaveStrategicGoals(project, model.StrategicGoals);
            (new WorkFlowManager(DbContext)).SaveContacts(model.WorkFlowContacts, null, project.Id, WorkFlowContactNotificationType.None);
            // if it is new project part
            if (model.Id == Guid.Empty)
            {
                #region Create Actuals scenario

                // we need to create empty actual scenario for all new projects (just created or copied)
                var scenario = new Scenario
                {
                    Id = Guid.NewGuid(),
                    Name = "ACTUALS",
                    ParentId = project.Id,
                    Type = ScenarioType.Actuals.GetHashCode(),
                    StartDate = DateTime.UtcNow.Date,
                    Color = "",
                    ProjectedRevenue = 0
                };
                DbContext.Scenarios.Add(scenario);

                #endregion
            }

            return project;
        }

        public Dictionary<Guid, ProjectPartModel> GetItemsAsModelWithChildren(Guid parentProjectId,
            ProjectManager.LoadProjectItems includedItems = ProjectManager.LoadProjectItems.None)
        {
            if (parentProjectId.Equals(Guid.Empty))
                throw new ArgumentNullException(nameof(parentProjectId));

            var partsAsRaw = DbContext.Projects.AsNoTracking().Where(t => t.ParentProjectId.HasValue && t.ParentProjectId.Value == parentProjectId).ToList();
            var partModels = partsAsRaw.ToDictionary(k => k.Id, v => (ProjectPartModel)v);

            EnVisageEntities tagsContext = null;
            try
            {
                var tasksToWait = new List<Task>();

                if (includedItems.HasFlag(ProjectManager.LoadProjectItems.PortfolioLabels) ||
                    includedItems.HasFlag(ProjectManager.LoadProjectItems.Tags))
                {
                    // Loading project tags and labels
                    tagsContext = new EnVisageEntities();
                    var tagsTask = tagsContext.TagLinks.AsNoTracking().Where(x => partModels.Keys.Contains(x.ParentID))
                        .GroupBy(x => x.ParentID).ToDictionaryAsync(k => k.Key, v => v.ToList());
                    tasksToWait.Add(tagsTask);
                }

                Task.WaitAll(tasksToWait.ToArray());

                if (includedItems.HasFlag(ProjectManager.LoadProjectItems.PortfolioLabels) ||
                    includedItems.HasFlag(ProjectManager.LoadProjectItems.Tags))
                {
                    // Find appropriate task in the awaitable list
                    var tagsLoadingTask = tasksToWait.FirstOrDefault(x =>
                        x is Task<Dictionary<Guid, List<TagLink>>>) as Task<Dictionary<Guid, List<TagLink>>>;

                    if (tagsLoadingTask != null)
                    {
                        var loadedLabelsAndTags = tagsLoadingTask.Result;

                        if (includedItems.HasFlag(ProjectManager.LoadProjectItems.PortfolioLabels))
                        {
                            // Get Project Portfolio Tags / Labels
                            foreach (var partId in partModels.Keys)
                            {
                                partModels[partId].PortfolioTags = loadedLabelsAndTags.ContainsKey(partId) ?
                                    loadedLabelsAndTags[partId].Where(x => x.TagLinkType == (int)TagType.Portfolio).Select(x => x.TagID).ToList() :
                                    new List<Guid>();
                            }
                        }

                        if (includedItems.HasFlag(ProjectManager.LoadProjectItems.Tags))
                        {
                            // Get Project Tags
                            foreach (var partId in partModels.Keys)
                            {
                                partModels[partId].ProjectTags = loadedLabelsAndTags.ContainsKey(partId) ?
                                    loadedLabelsAndTags[partId].Where(x => x.TagLinkType == (int)TagType.Project).Select(x => x.TagID).ToList() :
                                    new List<Guid>();
                            }
                        }
                    }
                }
            }
            finally
            {
                if (tagsContext != null)
                    tagsContext.Dispose();
            }

            return partModels;
        }
    }

    public class ProjectDependencyManager : ManagerBase<ProjectDependency, ProjectDependencyModel>
    {
        protected ScenarioManager ScenarioManager { get; set; }
        protected ProjectManager ProjectManager { get; set; }
        protected TeamManager TeamManager { get; set; }

        public ProjectDependencyManager(EnVisageEntities dbContext) : base(dbContext)
        {
            ProjectManager = new ProjectManager(DbContext);
            ScenarioManager = new ScenarioManager(DbContext);
            TeamManager = new TeamManager(DbContext);
        }
        public ProjectDependencyManager(EnVisageEntities dbContext, ProjectManager projectManager, ScenarioManager scenarioManager, TeamManager teamManager) : base(dbContext)
        {
            ProjectManager = projectManager;
            ScenarioManager = scenarioManager;
            TeamManager = teamManager;
        }
        protected override ProjectDependency InitInstance()
        {
            return new ProjectDependency { Id = Guid.NewGuid() };
        }

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

        public override DbSet<ProjectDependency> DataTable => DbContext.ProjectDependencies;

        public override ProjectDependency Save(ProjectDependencyModel model)
        {
            var oldObj = Load(model.Id);
            var obj = base.Save(model);
            // we need to update project timestamp of affected projects
            var projectmanager = new ProjectManager(DbContext);
            var sourceProjectModel = (ProjectModel)projectmanager.Load(obj.SourceProjectId);
            if (sourceProjectModel != null)
                projectmanager.Save(sourceProjectModel);
            var targetProjectModel = (ProjectModel)projectmanager.Load(obj.TargetProjectId);
            if (targetProjectModel != null)
                projectmanager.Save(targetProjectModel);

            #region Log Event

            var isAdd = oldObj.Id == Guid.Empty;
            var dependencyStrModelOld = new LogProjectDependencyStrModel();
            var dependencyStrModelNew = new LogProjectDependencyStrModel();
            if (!isAdd)
            {
                dependencyStrModelOld = GetLogProjectDependencyStr(oldObj, obj.Project1.Id, sourceProjectModel.Name, targetProjectModel.Name);
            }
            dependencyStrModelNew = GetLogProjectDependencyStr(obj, obj.Project.Id, sourceProjectModel.Name, targetProjectModel.Name);

            var transactionId = Utils.GetOrCreateTransactionId(DbContext, null);
            var userId = SecurityManager.GetUserPrincipal().ToString();

            LogEvent(transactionId, new EventRequestBaseModel
            {
                EntityId = obj.Id,
                ProjectId = obj.SourceProjectId,
                ClassificationKey = isAdd ? "ProjectDependencyAdd" : "ProjectDependencyEdit",
                OldValue = dependencyStrModelOld.ValueSourceStr,
                NewValue = dependencyStrModelNew.ValueSourceStr,
                UserId = userId
            });
            LogEvent(transactionId, new EventRequestBaseModel
            {
                EntityId = obj.Id,
                ProjectId = obj.TargetProjectId,
                ClassificationKey = isAdd ? "ProjectDependencyAdd" : "ProjectDependencyEdit",
                OldValue = dependencyStrModelOld.ValueTargetStr,
                NewValue = dependencyStrModelNew.ValueTargetStr,
                UserId = userId
            });
            Task.Run(() => AuditProxy.CommitEventChanges(transactionId));

            #endregion

            if (IsContextLocal)
                DbContext.SaveChanges();
            return obj;
        }
        public void Delete(Guid id, string userId = null)
        {
            var obj = DataTable.FirstOrDefault(t => t.Id == id);
            if (obj != null)
            {
                // we need to update project timestamp of affected projects
                var projectmanager = new ProjectManager(DbContext);
                var sourceProjectModel = (ProjectModel)projectmanager.Load(obj.SourceProjectId);
                if (sourceProjectModel != null)
                    projectmanager.Save(sourceProjectModel);
                var targetProjectModel = (ProjectModel)projectmanager.Load(obj.TargetProjectId);
                if (targetProjectModel != null)
                    projectmanager.Save(targetProjectModel);
                var projectId = obj.Project.Id;
                DataTable.Remove(obj);

                #region Log Event

                var dependencyStrModel = GetLogProjectDependencyStr(obj, projectId, sourceProjectModel.Name, targetProjectModel.Name);

                var transactionId = Utils.GetOrCreateTransactionId(DbContext, null);
                if (string.IsNullOrEmpty(userId))
                    userId = SecurityManager.GetUserPrincipal().ToString();

                LogEvent(transactionId, new EventRequestBaseModel
                {
                    EntityId = obj.Id,
                    ProjectId = obj.SourceProjectId,
                    ClassificationKey = "ProjectDependencyDelete",
                    OldValue = dependencyStrModel.ValueSourceStr,
                    UserId = userId
                });
                LogEvent(transactionId, new EventRequestBaseModel
                {
                    EntityId = obj.Id,
                    ProjectId = obj.TargetProjectId,
                    ClassificationKey = "ProjectDependencyDelete",
                    OldValue = dependencyStrModel.ValueTargetStr,
                    UserId = userId
                });
                Task.Run(() => AuditProxy.CommitEventChanges(transactionId));

                #endregion
            }
            if (IsContextLocal)
                DbContext.SaveChanges();
        }

        private LogProjectDependencyStrModel GetLogProjectDependencyStr(ProjectDependency obj, Guid projectId, string sourceProjectName, string targetProjectName)
        {
            var oldValueTargetStr = string.Empty;
            var oldValueSourceStr = string.Empty;
            if (obj.Type == (int)ProjectDependencyDisplayType.Link)
            {
                oldValueTargetStr = ProjectDependencyDisplayType.Link.ToDisplayValue();
                oldValueSourceStr = ProjectDependencyDisplayType.Link.ToDisplayValue();
            }
            else
            {
                if (projectId == obj.SourceProjectId)
                {
                    oldValueTargetStr = ProjectDependencyDisplayType.StartsAfter.ToDisplayValue();
                    oldValueSourceStr = ProjectDependencyDisplayType.StartsBefore.ToDisplayValue();
                }
                else
                {
                    oldValueTargetStr = ProjectDependencyDisplayType.StartsBefore.ToDisplayValue();
                    oldValueSourceStr = ProjectDependencyDisplayType.StartsAfter.ToDisplayValue();
                }
            }

            oldValueTargetStr = oldValueTargetStr + " " + sourceProjectName;
            oldValueSourceStr = oldValueSourceStr + " " + targetProjectName;
            return new LogProjectDependencyStrModel
            {
                ValueTargetStr = oldValueTargetStr,
                ValueSourceStr = oldValueSourceStr
            };
        }

        private class LogProjectDependencyStrModel
        {
            public string ValueTargetStr { get; set; }
        public string ValueSourceStr { get; set; }
    }

        public IEnumerable<DependencyProjectItem> GetAvailableProjects(Guid projectId, Guid sessionKey, Guid userId)
        {
            var chain = LoadChain(new List<Guid> { projectId });
            var excludeProjects = chain.Values.Select(t => t.SourceProjectId).Union(chain.Values.Select(x => x.TargetProjectId)).Union(new List<Guid> { projectId }).Distinct();

            var projects = GetDependencyProjects(userId, excludeProjects);
            return projects;
        }
        private static IEnumerable<DependencyProjectItem> GetDependencyProjects(Guid userId, IEnumerable<Guid> excludeProjects)
        {
            using (var dbContext = new EnVisageEntities())
            {
                var projectmanager = new ProjectManager(dbContext);
                var projects = projectmanager.GetProjectsWithChildren(userId, ProjectManager.LoadProjectItems.Scenarios)
                    .Where(t => !excludeProjects.Contains(t.Id))
                    .Select(t => new DependencyProjectItem
                    {
                        ProjectId = t.Id,
                        ProjectName = t.ParentProject != null ? t.Name + ": " + t.ParentProject.Name : t.Name,
                        ScenarioStartDate = t.Scenarios.Any(sc => sc.Status == ScenarioStatus.Active && sc.Type == ScenarioType.Portfolio) ?
                            Utils.ConvertToUnixDate(t.Scenarios.FirstOrDefault(sc => sc.Status == ScenarioStatus.Active && sc.Type == ScenarioType.Portfolio).StartDate.Value)
                            : (long?)null,
                        ScenarioEndDate = t.Scenarios.Any(sc => sc.Status == ScenarioStatus.Active && sc.Type == ScenarioType.Portfolio) ?
                            Utils.ConvertToUnixDate(t.Scenarios.FirstOrDefault(sc => sc.Status == ScenarioStatus.Active && sc.Type == ScenarioType.Portfolio).EndDate.Value)
                            : (long?)null
                    });
                return projects;
            }
        }


        public DependencyChain<DependencyChainItem> LoadChain(IEnumerable<Guid> projectIds)
        {
            var result = new DependencyChain<DependencyChainItem>();
            if (projectIds == null || !projectIds.Any())
                return result;
            result = LoadProjectDependencies(projectIds);
            return result;
        }

        public DependencyChain<DependencyChainItem> LoadProjectDependencies(IEnumerable<Guid> projectIds)
        {
            var visitedProjects = new List<Guid>().Union(projectIds).ToList();

            var ancestors = LoadDependenciesRecursively(projectIds, 0, true, visitedProjects);
            var descendants = LoadDependenciesRecursively(projectIds, 0, false, visitedProjects);

            var result = ancestors;
            descendants.Keys.ToList().ForEach(k =>
            {
                if (!result.ContainsKey(k))
                {
                    result.Add(k, descendants[k]);
                }
            });

            return result;
        }

        public DependencyChain<DependencyChainItem> LoadDependenciesRecursively(IEnumerable<Guid> projectIds, int level, bool getAncestors, List<Guid> visited)
        {
            var query1 = DbContext.VW_ProjectDependencies.AsQueryable();
            var query2 = DbContext.VW_ProjectDependencies.AsQueryable();
            IEnumerable<DependencyChainItem> extractedDependencies;

            if (getAncestors)
            {
                var commonQuery = query1.Where(t => projectIds.Contains(t.TargetProjectId) && !visited.Contains(t.SourceProjectId))
                    .Select(x => new
                    {
                        Item = x,
                        IsReversed = false
                    });
                var reversedLinksQuery = query2.Where(t => (projectIds.Contains(t.SourceProjectId) &&
                    (t.Type == (short)ProjectDependencyDisplayType.Link) && !visited.Contains(t.TargetProjectId)))
                    .Select(x => new
                    {
                        Item = x,
                        IsReversed = true
                    });

                extractedDependencies = commonQuery.Union(reversedLinksQuery).ToList().Select(x =>
                    DependencyChainItem.CreateFrom(x.Item, level, x.IsReversed));
            }
            else
            {
                var commonQuery = query1.Where(t => projectIds.Contains(t.SourceProjectId) && !visited.Contains(t.TargetProjectId))
                    .Select(x => new
                    {
                        Item = x,
                        IsReversed = false
                    });
                var reversedLinksQuery = query2.Where(t => (projectIds.Contains(t.TargetProjectId) &&
                    (t.Type == (short)ProjectDependencyDisplayType.Link) && !visited.Contains(t.SourceProjectId)))
                    .Select(x => new
                    {
                        Item = x,
                        IsReversed = true
                    });

                extractedDependencies = commonQuery.Union(reversedLinksQuery).ToList().Select(x =>
                    DependencyChainItem.CreateFrom(x.Item, level, x.IsReversed));
            }

            var result = extractedDependencies.GroupBy(t => t.Id).ToDictionary(key => key.Key, el => el.FirstOrDefault());

            if (result.Any())
            {
                var itemsToGoFrom =
                    result.Values.Where(x => (getAncestors && !x.IsReversed) || (!getAncestors && x.IsReversed)).Select(x => x.SourceProjectId).Union(
                    result.Values.Where(x => (getAncestors && x.IsReversed) || (!getAncestors && !x.IsReversed)).Select(x => x.TargetProjectId));

                visited.AddRange(itemsToGoFrom);
                var nextLevel = getAncestors ? level - 1 : level + 1;
                var nextLevelResult = LoadDependenciesRecursively(itemsToGoFrom, nextLevel, getAncestors, visited);

                foreach (var item in nextLevelResult)
                {
                    if (!result.ContainsKey(item.Key))
                        result.Add(item.Key, item.Value);
                }
            }

            return DependencyChain<DependencyChainItem>.CreateFrom(result);
        }
        public List<ProjectDependency> LoadDependencies(IEnumerable<Guid> ids, bool trackChanges = false)
        {
            if (ids == null || !ids.Any())
                return new List<ProjectDependency>();
            var query = DbContext.ProjectDependencies.AsQueryable();
            if (!trackChanges)
                query = query.AsNoTracking();
            return query.Where(t => ids.Contains(t.Id)).ToList();
        }
        /// <summary>
        /// Loads dependencies for the specified project Id from DB and convert them into <see cref="ProjectDependencyListModel"/> object.
        /// </summary>
        /// <param name="id">Single project or project part Id.</param>
        /// <returns>An instance of <see cref="ProjectDependencyListModel"/> class with info about all (explicit and implicit) 
        /// dependencies of the project with the specified Id.</returns>
        public ProjectDependencyListModel GetDependencies(Guid id, Guid? userId = null)
        {
            return GetDependencies(id, null, userId);
            //var dbRecords = LoadProjectDependencies(new List<Guid> { id });
            //return BuildDependencyListModel(id, dbRecords.Values, userId);
        }
        public ProjectDependencyListModel GetDependencies(Guid id, List<MixScenarioDates> MongoScenarioData, Guid? userId = null)
        {
            //var dbRecords = LoadProjectDependencies(new List<Guid> { id });
            var dbRecords = LoadProjectDependencies(new List<Guid> { id });
            return BuildDependencyListModel(id, dbRecords.Values, MongoScenarioData, userId);
        }
        /// <summary>
        /// Converts the specified collection of <see cref="DependencyChain.DependencyChainItem"/> items into <see cref="ProjectDependencyListModel"/> model
        /// and loads requried project and scenario info from database.
        /// </summary>
        /// <param name="id">Single project or project part Id.</param>
        /// <returns>An instance of <see cref="ProjectDependencyListModel"/> class with info about all (explicit and implicit) 
        /// dependencies of the project with the specified Id.</returns>
        public ProjectDependencyListModel BuildDependencyListModel(Guid id, IEnumerable<DependencyChainItem> data, Guid? userId = null)
        {
            return BuildDependencyListModel(id, data, null, userId);
        }
        public ProjectDependencyListModel BuildDependencyListModel(Guid id, IEnumerable<DependencyChainItem> data, List<MixScenarioDates> MongoScenarioData, Guid? userId = null)
        {
            //Logger.Debug("BuildDependencyListModel started");
            var dict = new Dictionary<ProjectDependencyDisplayType, ProjectDependencyDisplayModel>();
            // gather all project ids i n chain in both directions
            var allProjectIds = data.Select(t => t.SourceProjectId).Union(data.Select(t => t.TargetProjectId));
            // load all referenced projects
            var projects = ProjectManager.LoadProjects(allProjectIds).GroupBy(t => t.Id).ToDictionary(t => t.Key, el => el.FirstOrDefault());
            //Logger.Debug("BuildDependencyListModel projects loaded");
            // load all active scenarios for previously loaded projects
            var scenarios = ScenarioManager.GetScenarios4Projects(allProjectIds.ToList(), null, ScenarioStatus.Active, false, true).
                GroupBy(t => t.ParentId).ToDictionary(t => t.Key, el => el.FirstOrDefault());
            if (MongoScenarioData != null)
            {
                if (MongoScenarioData.Count > 0)
                {
                    foreach (var s in MongoScenarioData)
                    {
                        if (scenarios.ContainsKey(s.ProjectId) && s.EndDate.HasValue && s.StartDate.HasValue)
                        {
                            scenarios[s.ProjectId].StartDate = Utils.ConvertFromUnixDate(s.StartDate.Value);
                            scenarios[s.ProjectId].EndDate = Utils.ConvertFromUnixDate(s.EndDate.Value);
                        }
                    }
                }
            }

            //Logger.Debug("BuildDependencyListModel scenarios loaded");
            var dataOrdered = data.OrderBy(x => Math.Abs(x.Level));
            foreach (var item in dataOrdered)
            {
                ProjectDependencyDisplayType virtualType = (ProjectDependencyDisplayType)item.Type;
                // calculate type to display
                if (item.Level == 0) // if this is direct dependency of the project with Id equals to id argument
                {
                    // if other task depends on current task then
                    // replace type with virtual because we need to take a look at dependency from opposite side (target project)
                    if (ProjectDependencyDisplayType.StartsBefore.Equals(virtualType) && item.TargetProjectId == id)
                        virtualType = ProjectDependencyDisplayType.StartsAfter;
                }
                else if (item.Level > 0 && ProjectDependencyDisplayType.StartsAfter.Equals(virtualType)) // if this is a descendant dependency item
                {
                    virtualType = ProjectDependencyDisplayType.StartsBefore;
                }
                else if (item.Level < 0 && ProjectDependencyDisplayType.StartsBefore.Equals(virtualType))
                {
                    virtualType = ProjectDependencyDisplayType.StartsAfter;
                }
                // create new item of the specified type or reuse one of already created
                ProjectDependencyDisplayModel listItem = null;
                if (dict.ContainsKey(virtualType))
                    listItem = dict[virtualType];
                else
                {
                    listItem = new ProjectDependencyDisplayModel()
                    {
                        Type = virtualType
                    };
                    dict.Add(virtualType, listItem);
                }
                // get referenced project
                ProjectListModel itemProject = null;
                Scenario itemScenario = null;
                Guid displayedProjectId = Guid.Empty;

                if (ProjectDependencyDisplayType.StartsBefore.Equals(virtualType)) // if current task depends on other task
                {
                    itemProject = projects.ContainsKey(item.TargetProjectId) ? projects[item.TargetProjectId] : null;
                    displayedProjectId = item.TargetProjectId;
                }
                else if (ProjectDependencyDisplayType.StartsAfter.Equals(virtualType)) // if other task depends on current task
                {
                    itemProject = projects.ContainsKey(item.SourceProjectId) ? projects[item.SourceProjectId] : null;
                    displayedProjectId = item.SourceProjectId;
                }
                else if (ProjectDependencyDisplayType.Link.Equals(virtualType))
                {
                    if (item.Level == 0)
                    {
                        if (item.SourceProjectId == id)
                        {
                            if (projects.ContainsKey(item.TargetProjectId))
                            {
                                itemProject = projects[item.TargetProjectId];
                                displayedProjectId = item.TargetProjectId;
                            }
                        }
                        else
                        {
                            if (projects.ContainsKey(item.SourceProjectId))
                            {
                                itemProject = projects[item.SourceProjectId];
                                displayedProjectId = item.SourceProjectId;
                            }
                        }
                    }
                    else
                    {

                        if (((item.Level < 0) && !item.IsReversed) || ((item.Level > 0) && item.IsReversed))
                        {
                            itemProject = projects.ContainsKey(item.SourceProjectId) ? projects[item.SourceProjectId] : null;
                            displayedProjectId = item.SourceProjectId;
                        }
                        else
                        {
                            itemProject = projects.ContainsKey(item.TargetProjectId) ? projects[item.TargetProjectId] : null;
                            displayedProjectId = item.TargetProjectId;
                        }
                    }
                }
                // set start/end date properties for the specified project and push it to projects property
                if (itemProject != null)
                {
                    if (scenarios.ContainsKey(itemProject.Id))
                    {
                        itemScenario = scenarios[itemProject.Id];
                        if (!listItem.dtStartDate.HasValue || Utils.ConvertFromUnixDate(listItem.dtStartDate.Value) > itemScenario.StartDate)
                            listItem.dtStartDate = Utils.ConvertToUnixDate(itemScenario.StartDate.Value);
                        if (!listItem.dtEndDate.HasValue || Utils.ConvertFromUnixDate(listItem.dtEndDate.Value) < itemScenario.EndDate)
                            listItem.dtEndDate = Utils.ConvertToUnixDate(itemScenario.EndDate.Value);
                    }
                }

                // fill Items collection with raw data, required for saving
                bool performItemAdding = true;
                var itemModel = new ProjectDependencyModel
                {
                    Id = item.Id,
                    SourceProjectId = item.SourceProjectId,
                    TargetProjectId = item.TargetProjectId,
                    Level = item.Level,
                    IsReversed = item.IsReversed,
                    DisplayedProjectId = displayedProjectId,
                    Type = (short)virtualType,
                    Name = itemProject != null ? itemProject.ProjectName : string.Empty,
                    StartDate = itemScenario?.StartDate != null ? Utils.ConvertToUnixDate(itemScenario.StartDate.Value) : (long?)null,
                    EndDate = itemScenario?.EndDate != null ? Utils.ConvertToUnixDate(itemScenario.EndDate.Value) : (long?)null
                };

                // Apply filtering of items, we going to add to result collection.
                // Use no filtering for 0 level of related items.
                // Do it to avoid displaying twice the same project name, if we reached it by different ways.
                if (listItem.Items.Count > 0 && itemModel.Level != 0)
                {
                    // Looking for duplacated records in current items set
                    bool relationToProjectExists = listItem.Items.Any(x => x.Type == itemModel.Type && x.DisplayedProjectId.Equals(itemModel.DisplayedProjectId));
                    performItemAdding = !relationToProjectExists;
                }

                if (performItemAdding)
                {
                    listItem.Items.Add(itemModel);
                }
            }
            //Logger.Debug("BuildDependencyListModel model built");

            return new ProjectDependencyListModel(dict.Values);
        }

        public bool ValidateProjects(DependencyResolveModel model, out DependencyResolveModel validationResult)
        {
            validationResult = model.Clone();

            TimeSpan startDateOffset = TimeSpan.Zero;
            TimeSpan endDateOffset = TimeSpan.Zero;
            // calculate offsets
            var scenarios = ScenarioManager.GetScenarios4Projects(new List<Guid> { model.ProjectId }, ScenarioType.Portfolio, ScenarioStatus.Active, false, true);
            if (scenarios != null && scenarios.Any())
            {
                var scenario = scenarios.FirstOrDefault();
                if (scenario?.StartDate != null && scenario.EndDate.HasValue)
                {
                    if (model.StartDate.HasValue)
                        startDateOffset = model.StartDate.Value - scenario.StartDate.Value;
                    if (model.EndDate.HasValue)
                        endDateOffset = model.EndDate.Value - scenario.EndDate.Value;
                }
            }
            // validate dependencies
            var referencedProjects = LoadAllReferencedProjects(model.ProjectId, model.ResolvePlan, model.StartDate, startDateOffset, model.EndDate, endDateOffset);
            validationResult.ResolvePlan = referencedProjects.Values.ToList();
            return false;
        }
        private Dictionary<Guid, DependencyResolveModel.ProjectItem> InjectChanges(IEnumerable<DependencyResolveModel.ProjectItem> items,
            Dictionary<Guid, DependencyResolveModel.ProjectItem> changedItemsDict, Dictionary<Guid, Tuple<DateTime, DateTime>> changedProjects,
            TimeSpan? startDateOffset = null, TimeSpan? endDateOffset = null)
        {
            startDateOffset = startDateOffset ?? TimeSpan.Zero;
            endDateOffset = endDateOffset ?? TimeSpan.Zero;
            var result = new Dictionary<Guid, DependencyResolveModel.ProjectItem>();
            foreach (var dbItem in items)
            {
                #region replace dates for other project if it has been modified earlier
                if (dbItem.ProjectId == dbItem.SourceProjectId)
                {
                    if (changedProjects.ContainsKey(dbItem.TargetProjectId) && dbItem.Level != 0)
                    {
                        if (dbItem.TargetStartDate.HasValue)
                            startDateOffset = changedProjects[dbItem.TargetProjectId].Item1 - dbItem.TargetStartDate.Value;
                        if (dbItem.TargetEndDate.HasValue)
                            endDateOffset = changedProjects[dbItem.TargetProjectId].Item2 - dbItem.TargetEndDate.Value;
                        dbItem.TargetStartDate = changedProjects[dbItem.TargetProjectId].Item1;
                        dbItem.TargetEndDate = changedProjects[dbItem.TargetProjectId].Item2;
                    }
                }
                else if (dbItem.ProjectId == dbItem.TargetProjectId)
                {
                    if (changedProjects.ContainsKey(dbItem.SourceProjectId) && dbItem.Level != 0)
                    {
                        if (dbItem.SourceStartDate.HasValue)
                            startDateOffset = changedProjects[dbItem.SourceProjectId].Item1 - dbItem.SourceStartDate.Value;
                        if (dbItem.SourceEndDate.HasValue)
                            endDateOffset = changedProjects[dbItem.SourceProjectId].Item2 - dbItem.SourceEndDate.Value;
                        dbItem.SourceStartDate = changedProjects[dbItem.SourceProjectId].Item1;
                        dbItem.SourceEndDate = changedProjects[dbItem.SourceProjectId].Item2;
                    }
                }
                #endregion

                #region apply changes entered by user on the form
                if (changedItemsDict.ContainsKey(dbItem.Id))
                {
                    var changedItem = changedItemsDict[dbItem.Id];
                    dbItem.MoveStartDate = changedItem.MoveStartDate;
                    dbItem.Action = changedItem.Action;
                    if (dbItem.Action.HasValue && !dbItem.AvailableMoveTypes.Contains(dbItem.Action.Value))
                        dbItem.Action = dbItem.AvailableMoveTypes.Any() ? dbItem.AvailableMoveTypes.First() : (DependencyResolveModel.ActionType?)null;
                }
                #endregion

                #region remove DontMove option if necessary
                if (dbItem.SourceEndDate.HasValue && dbItem.TargetStartDate.HasValue &&
                    dbItem.SourceEndDate.Value >= dbItem.TargetStartDate.Value && // validation rule 1
                    dbItem.Type == (short)ProjectDependencyModel.ProjectDependencyType.StartsBefore)
                {
                    // we should remove don't move option as it will lead to overlap of projects
                    if (dbItem.AvailableMoveTypes.Contains(DependencyResolveModel.ActionType.DontMove))
                    {
                        dbItem.AvailableMoveTypes.Remove(DependencyResolveModel.ActionType.DontMove);
                        if (DependencyResolveModel.ActionType.DontMove.Equals(dbItem.Action))
                            dbItem.Action = dbItem.AvailableMoveTypes.Any() ? dbItem.AvailableMoveTypes.First() : (DependencyResolveModel.ActionType?)null;
                    }
                }
                #endregion

                #region remove movewith option if necessary
                if (dbItem.AvailableMoveTypes.Contains(DependencyResolveModel.ActionType.MoveWith))
                {
                    if (dbItem.ProjectId == dbItem.SourceProjectId)
                    {
                        if (TimeSpan.Zero.Equals(startDateOffset) || dbItem.SourceHasActuals)
                        {
                            dbItem.AvailableMoveTypes.Remove(DependencyResolveModel.ActionType.MoveWith);
                            if (DependencyResolveModel.ActionType.MoveWith.Equals(dbItem.Action))
                                dbItem.Action = dbItem.AvailableMoveTypes.Any() ? dbItem.AvailableMoveTypes.First() : (DependencyResolveModel.ActionType?)null;
                        }
                    }
                    else if (dbItem.ProjectId == dbItem.TargetProjectId)
                    {
                        if (TimeSpan.Zero.Equals(endDateOffset) || dbItem.TargetHasActuals)
                        {
                            dbItem.AvailableMoveTypes.Remove(DependencyResolveModel.ActionType.MoveWith);
                            if (DependencyResolveModel.ActionType.MoveWith.Equals(dbItem.Action))
                                dbItem.Action = dbItem.AvailableMoveTypes.Any() ? dbItem.AvailableMoveTypes.First() : (DependencyResolveModel.ActionType?)null;
                        }
                    }
                }
                #endregion

                #region remove link option if necessary
                if (dbItem.AvailableMoveTypes.Contains(DependencyResolveModel.ActionType.ConvertToLink))
                {
                    if (dbItem.IsNew)
                    {
                        dbItem.AvailableMoveTypes.Remove(DependencyResolveModel.ActionType.ConvertToLink);
                        if (DependencyResolveModel.ActionType.ConvertToLink.Equals(dbItem.Action))
                            dbItem.Action = dbItem.AvailableMoveTypes.Any() ? dbItem.AvailableMoveTypes.First() : (DependencyResolveModel.ActionType?)null;
                    }
                    else if (dbItem.IsDeleted)
                    {
                        dbItem.AvailableMoveTypes.Remove(DependencyResolveModel.ActionType.ConvertToLink);
                        if (DependencyResolveModel.ActionType.ConvertToLink.Equals(dbItem.Action))
                            dbItem.Action = dbItem.AvailableMoveTypes.Any() ? dbItem.AvailableMoveTypes.First() : (DependencyResolveModel.ActionType?)null;
                    }
                }
                #endregion

                #region remove RemoveDependency option if necessary
                if (dbItem.AvailableMoveTypes.Contains(DependencyResolveModel.ActionType.RemoveDependency))
                {
                    if (dbItem.IsNew)
                    {
                        dbItem.AvailableMoveTypes.Remove(DependencyResolveModel.ActionType.RemoveDependency);
                        if (DependencyResolveModel.ActionType.RemoveDependency.Equals(dbItem.Action))
                            dbItem.Action = dbItem.AvailableMoveTypes.Any() ? dbItem.AvailableMoveTypes.First() : (DependencyResolveModel.ActionType?)null;
                    }
                    else if (dbItem.IsDeleted)
                    {
                        dbItem.AvailableMoveTypes.Remove(DependencyResolveModel.ActionType.RemoveDependency);
                        if (DependencyResolveModel.ActionType.RemoveDependency.Equals(dbItem.Action))
                            dbItem.Action = dbItem.AvailableMoveTypes.Any() ? dbItem.AvailableMoveTypes.First() : (DependencyResolveModel.ActionType?)null;
                    }
                }
                #endregion

                #region Set MoveDate to current scenario start date if project is not going to be moved
                if (DependencyResolveModel.ActionType.DontMove.Equals(dbItem.Action) ||
                    (!dbItem.Action.HasValue && dbItem.AvailableMoveTypes.FirstOrDefault() == DependencyResolveModel.ActionType.DontMove))
                {
                    if (dbItem.ProjectId == dbItem.TargetProjectId)
                        dbItem.MoveStartDate = dbItem.TargetStartDate;
                    else
                        dbItem.MoveStartDate = dbItem.SourceStartDate;
                }
                else if (DependencyResolveModel.ActionType.ConvertToLink.Equals(dbItem.Action) ||
                    (!dbItem.Action.HasValue && dbItem.AvailableMoveTypes.FirstOrDefault() == DependencyResolveModel.ActionType.ConvertToLink))
                {
                    if (dbItem.ProjectId == dbItem.TargetProjectId)
                        dbItem.MoveStartDate = dbItem.TargetStartDate;
                    else
                        dbItem.MoveStartDate = dbItem.SourceStartDate;
                }
                else if (DependencyResolveModel.ActionType.RemoveDependency.Equals(dbItem.Action) ||
                    (!dbItem.Action.HasValue && dbItem.AvailableMoveTypes.FirstOrDefault() == DependencyResolveModel.ActionType.RemoveDependency))
                {
                    if (dbItem.ProjectId == dbItem.TargetProjectId)
                        dbItem.MoveStartDate = dbItem.TargetStartDate;
                    else
                        dbItem.MoveStartDate = dbItem.SourceStartDate;
                }
                #endregion

                #region Apply MoveWith option and validate that new dates are acceptable
                if (dbItem.Action == DependencyResolveModel.ActionType.MoveWith ||
                    (!dbItem.Action.HasValue && dbItem.AvailableMoveTypes.FirstOrDefault() == DependencyResolveModel.ActionType.MoveWith))
                {
                    if (dbItem.ProjectId == dbItem.SourceProjectId)
                    {
                        var removeAction = false;
                        if (dbItem.SourceHasActuals)
                            removeAction = true;
                        else
                        {
                            // move source project to the same offset as changed project
                            if (dbItem.SourceStartDate.HasValue)
                            {
                                dbItem.MoveStartDate = dbItem.SourceStartDate.Value.Add(startDateOffset.Value);
                                // if source project ends after target starts even after move then
                                // we need to move it additionally so it ends before target starts 
                                if (ProjectDependencyModel.ProjectDependencyType.StartsBefore.Equals(dbItem.Type) &&
                                    dbItem.MoveEndDate.HasValue && dbItem.TargetStartDate.HasValue && dbItem.MoveEndDate >= dbItem.TargetStartDate)
                                {
                                    var newOffset = dbItem.TargetStartDate.Value.AddDays(-1) - dbItem.MoveEndDate.Value;
                                    dbItem.MoveStartDate = dbItem.MoveStartDate.Value.Add(newOffset);
                                }
                            }
                            // if projects intersect even after move then we should remove MoveWith option and reset move date
                            if (dbItem.MoveEndDate.HasValue && dbItem.TargetStartDate.HasValue && dbItem.MoveStartDate.HasValue &&
                                ((ProjectDependencyModel.ProjectDependencyType.StartsBefore.Equals(dbItem.Type) && dbItem.MoveEndDate >= dbItem.TargetStartDate)
                                    || dbItem.MoveStartDate.Value < DateTime.UtcNow.Date))
                                removeAction = true;
                        }
                        // if projects intersect even after move then we should remove MoveWith option and reset move date
                        if (removeAction)
                        {
                            dbItem.AvailableMoveTypes.Remove(DependencyResolveModel.ActionType.MoveWith);
                            dbItem.Action = dbItem.AvailableMoveTypes.Any() ? dbItem.AvailableMoveTypes.First() : (DependencyResolveModel.ActionType?)null;
                            dbItem.MoveStartDate = dbItem.SourceStartDate;
                        }
                    }
                    else if (dbItem.ProjectId == dbItem.TargetProjectId)
                    {
                        var removeAction = false;
                        if (dbItem.TargetHasActuals)
                            removeAction = true;
                        else
                        {
                            if (dbItem.TargetStartDate.HasValue)
                            {
                                // move target project to the same offset as changed project
                                dbItem.MoveStartDate = dbItem.TargetStartDate.Value.Add(endDateOffset.Value);
                                // if target project starts before source ends even after move then
                                // we need to move it additionally so it starts after target ends 
                                if (ProjectDependencyModel.ProjectDependencyType.StartsBefore.Equals(dbItem.Type) &&
                                    dbItem.MoveStartDate.HasValue && dbItem.SourceEndDate.HasValue && dbItem.MoveStartDate <= dbItem.SourceEndDate)
                                {
                                    var newOffset = dbItem.SourceEndDate.Value.AddDays(1) - dbItem.MoveStartDate.Value;
                                    dbItem.MoveStartDate = dbItem.MoveStartDate.Value.Add(newOffset);
                                }
                            }
                            // if projects intersect even after move then we should remove MoveWith option and reset move date
                            if (dbItem.MoveStartDate.HasValue && dbItem.SourceEndDate.HasValue &&
                                ((ProjectDependencyModel.ProjectDependencyType.StartsBefore.Equals(dbItem.Type) && dbItem.MoveStartDate <= dbItem.SourceEndDate)
                                    || dbItem.MoveStartDate.Value < DateTime.UtcNow.Date))
                                removeAction = true;
                        }
                        if (removeAction)
                        {
                            dbItem.AvailableMoveTypes.Remove(DependencyResolveModel.ActionType.MoveWith);
                            dbItem.Action = dbItem.AvailableMoveTypes.Any() ? dbItem.AvailableMoveTypes.First() : (DependencyResolveModel.ActionType?)null;
                            dbItem.MoveStartDate = dbItem.TargetStartDate;
                        }
                    }
                }
                #endregion

                #region set move start date as original start date if other value was not set
                if (dbItem.ProjectId == dbItem.SourceProjectId)
                {
                    dbItem.MoveStartDate = dbItem.MoveStartDate ?? dbItem.SourceStartDate;
                }
                else if (dbItem.ProjectId == dbItem.TargetProjectId)
                {
                    dbItem.MoveStartDate = dbItem.MoveStartDate ?? dbItem.TargetStartDate;
                }
                #endregion

                #region Determine min and max available dates

                dbItem.MinAvailableScenarioStartDate = Constants.FISCAL_CALENDAR_MIN_DATE > DateTime.UtcNow.Date ? Constants.FISCAL_CALENDAR_MIN_DATE : DateTime.UtcNow.Date;
                dbItem.MaxAvailableScenarioStartDate = dbItem.MinAvailableScenarioStartDate;

                if (dbItem.ProjectId == dbItem.SourceProjectId)
                {
                    if (dbItem.SourceStartDate.HasValue && dbItem.SourceEndDate.HasValue)
                    {
                        var sourceDuration = dbItem.SourceEndDate.Value - dbItem.SourceStartDate.Value;
                        var sourceMaxEndDate = (dbItem.SourceDeadline.HasValue && dbItem.SourceDeadline.Value < Constants.FISCAL_CALENDAR_MAX_DATE) ? dbItem.SourceDeadline.Value : Constants.FISCAL_CALENDAR_MAX_DATE;

                        if (dbItem.TargetStartDate.HasValue &&
                            dbItem.TargetStartDate.Value.AddDays(-1) < sourceMaxEndDate)
                        {
                            sourceMaxEndDate = dbItem.TargetStartDate.Value.AddDays(-1);
                        }
                        dbItem.MaxAvailableScenarioStartDate = sourceMaxEndDate.Subtract(sourceDuration);
                    }
                }
                else if (dbItem.ProjectId == dbItem.TargetProjectId)
                {
                    if (dbItem.TargetStartDate.HasValue && dbItem.TargetEndDate.HasValue)
                    {
                        var targetDuration = dbItem.TargetEndDate.Value - dbItem.TargetStartDate.Value;
                        var targetMaxEndDate = (dbItem.TargetDeadline.HasValue && dbItem.TargetDeadline.Value < Constants.FISCAL_CALENDAR_MAX_DATE) ? dbItem.TargetDeadline.Value : Constants.FISCAL_CALENDAR_MAX_DATE;

                        dbItem.MaxAvailableScenarioStartDate = targetMaxEndDate.Subtract(targetDuration);
                        if (dbItem.SourceEndDate.HasValue &&
                            dbItem.SourceEndDate.Value.AddDays(1) > dbItem.MinAvailableScenarioStartDate)
                        {
                            dbItem.MinAvailableScenarioStartDate = dbItem.SourceEndDate.Value.AddDays(1);
                        }
                    }
                }

                #endregion

                #region remove Custom option if there are no available move dates
                if (dbItem.AvailableMoveTypes.Contains(DependencyResolveModel.ActionType.CustomMove))
                {
                    var availableRangeDuration = dbItem.MaxAvailableScenarioStartDate.Subtract(dbItem.MinAvailableScenarioStartDate);
                    var isRemove = availableRangeDuration.TotalDays <= 0;
                    if (!isRemove)
                    {
                        if (dbItem.ProjectId == dbItem.SourceProjectId && dbItem.SourceHasActuals)
                            isRemove = true;
                        else if (dbItem.ProjectId == dbItem.TargetProjectId && dbItem.TargetHasActuals)
                            isRemove = true;
                    }
                    if (isRemove)
                    {
                        dbItem.AvailableMoveTypes.Remove(DependencyResolveModel.ActionType.CustomMove);
                        if (DependencyResolveModel.ActionType.CustomMove.Equals(dbItem.Action))
                            dbItem.Action = dbItem.AvailableMoveTypes.Any() ? dbItem.AvailableMoveTypes.First() : (DependencyResolveModel.ActionType?)null;
                    }
                    else
                    {
                        if (dbItem.MoveStartDate.HasValue &&
                           (dbItem.MoveStartDate.Value < dbItem.MinAvailableScenarioStartDate ||
                            dbItem.MoveStartDate.Value > dbItem.MaxAvailableScenarioStartDate))
                        {
                            dbItem.MoveStartDate = dbItem.MinAvailableScenarioStartDate;
                        }
                    }
                }
                #endregion

                // add project to disctionary if it's dates were changed
                if (dbItem.MoveStartDate.HasValue && dbItem.MoveEndDate.HasValue
                    && dbItem.MoveStartDate != (dbItem.ProjectId == dbItem.SourceProjectId ? dbItem.SourceStartDate : dbItem.TargetStartDate)
                    && !changedProjects.ContainsKey(dbItem.ProjectId))
                    changedProjects.Add(dbItem.ProjectId, new Tuple<DateTime, DateTime>(dbItem.MoveStartDate.Value, dbItem.MoveEndDate.Value));

                result.Add(dbItem.Id, dbItem);
            }
            return result;
        }
        public DependencyChain<DependencyResolveModel.ProjectItem> LoadAllReferencedProjects(Guid projectId,
            IEnumerable<DependencyResolveModel.ProjectItem> changedItems, DateTime? newStartDate, TimeSpan startDateOffset,
            DateTime? newEndDate, TimeSpan endDateOffset)
        {
            // gather direct relations
            var dbItems = DbContext.VW_ProjectDependencies.Where(t => projectId == t.SourceProjectId || projectId == t.TargetProjectId)
                .AsEnumerable().Select(t => (DependencyResolveModel.ProjectItem)t).GroupBy(t => t.Id).ToDictionary(key => key.Key, el => el.FirstOrDefault());
            var changedProjects = new Dictionary<Guid, Tuple<DateTime, DateTime>>();
            if (DateTime.MinValue.Equals(newStartDate))
            {
                newStartDate = null;
                startDateOffset = TimeSpan.Zero;
            }
            if (DateTime.MinValue.Equals(newEndDate))
            {
                newEndDate = null;
                endDateOffset = TimeSpan.Zero;
            }
            dbItems = RemoveDeletedItems(dbItems, changedItems.Where(t => t.IsDeleted));
            dbItems = PrependPendingItems(dbItems, changedItems.Where(t => t.IsNew));
            // inject changes from client model to direct relations
            foreach (var item in dbItems.Values)
            {
                if (projectId == item.SourceProjectId)
                {
                    // replace dates of changed scenario/project
                    item.SourceStartDate = item.SourceStartDate?.Add(startDateOffset) ?? newStartDate;
                    item.SourceEndDate = item.SourceEndDate?.Add(endDateOffset) ?? newEndDate;
                    if (!changedProjects.ContainsKey(projectId) && item.SourceStartDate.HasValue && item.SourceEndDate.HasValue)
                        changedProjects.Add(projectId, new Tuple<DateTime, DateTime>(item.SourceStartDate.Value, item.SourceEndDate.Value));
                    // fill source/target properties of the item
                    item.FillDescendant(endDateOffset);
                }
                if (projectId == item.TargetProjectId)
                {
                    // replace dates of changed scenario/project
                    item.TargetStartDate = item.TargetStartDate?.Add(startDateOffset) ?? newStartDate;
                    item.TargetEndDate = item.TargetEndDate?.Add(endDateOffset) ?? newEndDate;
                    if (!changedProjects.ContainsKey(projectId) && item.TargetStartDate.HasValue && item.TargetEndDate.HasValue)
                        changedProjects.Add(projectId, new Tuple<DateTime, DateTime>(item.TargetStartDate.Value, item.TargetEndDate.Value));
                    // fill source/target properties of the item
                    item.FillAncestor(startDateOffset);
                }
            }

            var changedItemsDict = changedItems.GroupBy(t => t.Id).ToDictionary(t => t.Key, el => el.FirstOrDefault());
            var result = InjectChanges(dbItems.Values, changedItemsDict, changedProjects, startDateOffset, endDateOffset);

            if (result.Any())
            {
                var projectsToLookAncestorsFor =
                    result.Values.Where(t => t.Action != DependencyResolveModel.ActionType.RemoveDependency).Select(t => t.SourceProjectId).Distinct();
                var projectsToLookDescendantsOf =
                    result.Values.Where(t => t.Action != DependencyResolveModel.ActionType.RemoveDependency).Select(t => t.TargetProjectId).Distinct();
                var visitedProjects = projectsToLookAncestorsFor.Union(projectsToLookDescendantsOf)
                    .Union(new List<Guid>() { projectId }).Distinct().ToList();

                var ancestors = LoadResolveDependenciesRecursively(projectsToLookAncestorsFor, -1, true, changedItemsDict, startDateOffset, endDateOffset, changedProjects, visitedProjects);
                var descendants = LoadResolveDependenciesRecursively(projectsToLookDescendantsOf, 1, false, changedItemsDict, startDateOffset, endDateOffset, changedProjects, visitedProjects);

                foreach (var item in ancestors)
                    if (!result.ContainsKey(item.Key))
                    {
                        result.Add(item.Key, item.Value);
                    }
                foreach (var item in descendants)
                    if (!result.ContainsKey(item.Key))
                    {
                        result.Add(item.Key, item.Value);
                    }
            }
            return DependencyChain<DependencyResolveModel.ProjectItem>.CreateFrom(result);
        }
        private Dictionary<Guid, DependencyResolveModel.ProjectItem> RemoveDeletedItems(Dictionary<Guid, DependencyResolveModel.ProjectItem> dbItems, IEnumerable<DependencyResolveModel.ProjectItem> deletedItems)
        {
            var result = new Dictionary<Guid, DependencyResolveModel.ProjectItem>();
            foreach (var item in dbItems)
            {
                if (deletedItems.Any(del => (del.SourceProjectId == item.Value.SourceProjectId && del.TargetProjectId == item.Value.TargetProjectId) || // exact match
                                            (del.TargetProjectId == item.Value.SourceProjectId && del.SourceProjectId == item.Value.TargetProjectId))) // or backward match
                    continue;
                if (!result.ContainsKey(item.Key))
                    result.Add(item.Key, item.Value);
            }
            return result;
        }
        /// <summary>
        /// Loads information about projects and scenarios from <paramref name="newItems"/> collection and then append them to <paramref name="dbItems"/> collection.
        /// </summary>
        /// <param name="dbItems">A collection of direct dependencies.</param>
        /// <param name="newItems">A collection of items system is going to create but not saved to DB yet.</param>
        private Dictionary<Guid, DependencyResolveModel.ProjectItem> PrependPendingItems(Dictionary<Guid, DependencyResolveModel.ProjectItem> dbItems, IEnumerable<DependencyResolveModel.ProjectItem> newItems)
        {
            var projectIds = newItems.SelectMany(t => new Guid[] { t.SourceProjectId, t.TargetProjectId }).Distinct();
            var projects = ProjectManager.LoadProjects(projectIds)
                .GroupBy(t => t.Id).ToDictionary(t => t.Key, el => el.FirstOrDefault());
            var scenarios = ScenarioManager.GetScenarios4Projects(projectIds.ToList(), ScenarioType.Portfolio, ScenarioStatus.Active, false, true)
                .GroupBy(k => k.ParentId).ToDictionary(k => k.Key, el => el.FirstOrDefault());
            var result = new Dictionary<Guid, DependencyResolveModel.ProjectItem>();
            foreach (var item in newItems)
            {
                // if result collection already contains an item with the same project.Id tuple then skip this item
                if (result.ContainsKey(item.Id) ||
                    result.Values.Any(t => t.SourceProjectId == item.SourceProjectId && t.TargetProjectId == item.TargetProjectId))
                    continue;
                var dbItem = new DependencyResolveModel.ProjectItem
                {
                    Id = item.Id,
                    IsNew = true,
                    Level = 0,
                    ProjectId = item.ProjectId,
                    SourceProjectId = item.SourceProjectId,
                    TargetProjectId = item.TargetProjectId,
                    SourceStartDate = item.SourceStartDate,
                    SourceEndDate = item.SourceEndDate,
                    TargetStartDate = item.TargetStartDate,
                    TargetEndDate = item.TargetEndDate,
                    Type = item.Type
                };
                if (projects.ContainsKey(dbItem.SourceProjectId))
                {
                    dbItem.SourceProjectName = projects[dbItem.SourceProjectId].ProjectName;
                    if (scenarios.ContainsKey(projects[dbItem.SourceProjectId].Id))
                    {
                        var scenario = scenarios[projects[dbItem.SourceProjectId].Id];
                        dbItem.SourceStartDate = dbItem.SourceStartDate ?? scenario.StartDate;
                        dbItem.SourceEndDate = dbItem.SourceEndDate ?? scenario.EndDate;
                    }
                }
                if (projects.ContainsKey(dbItem.TargetProjectId))
                {
                    dbItem.TargetProjectName = projects[dbItem.TargetProjectId].ProjectName;
                    if (scenarios.ContainsKey(projects[dbItem.TargetProjectId].Id))
                    {
                        var scenario = scenarios[projects[dbItem.TargetProjectId].Id];
                        dbItem.TargetStartDate = dbItem.TargetStartDate ?? scenario.StartDate;
                        dbItem.TargetEndDate = dbItem.TargetEndDate ?? scenario.EndDate;
                    }
                }
                result.Add(dbItem.Id, dbItem);
            }
            foreach (var item in dbItems)
            {
                if (!result.ContainsKey(item.Key))
                    result.Add(item.Key, item.Value);
            }
            return result;
        }

        public DependencyChain<DependencyResolveModel.ProjectItem> LoadResolveDependenciesRecursively(IEnumerable<Guid> projectIds, int level, bool getAncestors,
            Dictionary<Guid, DependencyResolveModel.ProjectItem> changedItemsDict, TimeSpan startDateOffset, TimeSpan endDateOffset,
            Dictionary<Guid, Tuple<DateTime, DateTime>> changedProjects, List<Guid> visited)
        {
            var query1 = DbContext.VW_ProjectDependencies.AsQueryable();
            var query2 = DbContext.VW_ProjectDependencies.AsQueryable();
            IEnumerable<DependencyResolveModel.ProjectItem> extractedDependencies;

            if (getAncestors)
            {
                var commonQuery = query1.Where(t => projectIds.Contains(t.TargetProjectId) && !visited.Contains(t.SourceProjectId))
                    .Select(x => new
                    {
                        Item = x,
                        IsReversed = false
                    });
                var reversedLinksQuery = query2.Where(t => (projectIds.Contains(t.SourceProjectId) &&
                    (t.Type == (short)ProjectDependencyDisplayType.Link) && !visited.Contains(t.TargetProjectId)))
                    .Select(x => new
                    {
                        Item = x,
                        IsReversed = true
                    });

                extractedDependencies = commonQuery.Union(reversedLinksQuery).ToList().Select(x =>
                    DependencyResolveModel.ProjectItem.CreateFrom(x.Item, level, x.IsReversed));
            }
            else
            {
                var commonQuery = query1.Where(t => projectIds.Contains(t.SourceProjectId) && !visited.Contains(t.TargetProjectId))
                    .Select(x => new
                    {
                        Item = x,
                        IsReversed = false
                    });
                var reversedLinksQuery = query2.Where(t => (projectIds.Contains(t.TargetProjectId) &&
                    (t.Type == (short)ProjectDependencyDisplayType.Link) && !visited.Contains(t.SourceProjectId)))
                    .Select(x => new
                    {
                        Item = x,
                        IsReversed = true
                    });

                extractedDependencies = commonQuery.Union(reversedLinksQuery).ToList().Select(x =>
                    DependencyResolveModel.ProjectItem.CreateFrom(x.Item, level, x.IsReversed));
            }

            var dbItems = extractedDependencies.GroupBy(t => t.Id).ToDictionary(key => key.Key, el => el.FirstOrDefault());

            foreach (var item in dbItems.Values)
            {
                // Source code:
                //if (getAncestors)
                //	item.FillAncestor(startDateOffset);
                //else
                //	item.FillDescendant(endDateOffset);

                if ((getAncestors && (item.Type != (short)ProjectDependencyDisplayType.Link)) ||
                    (getAncestors && !item.IsReversed) || (!getAncestors && item.IsReversed))
                    item.FillAncestor(startDateOffset);
                else
                    item.FillDescendant(endDateOffset);
            }

            // inject changes from client model to direct relations
            var result = InjectChanges(dbItems.Values, changedItemsDict, changedProjects);
            if (result.Any())
            {
                var itemsToGoFrom =
                    result.Values.Where(x => (x.Action != DependencyResolveModel.ActionType.RemoveDependency) &&
                    (getAncestors && !x.IsReversed) || (!getAncestors && x.IsReversed)).Select(x => x.SourceProjectId)
                    .Union(
                        result.Values.Where(x => (x.Action != DependencyResolveModel.ActionType.RemoveDependency) &&
                        (getAncestors && x.IsReversed) || (!getAncestors && !x.IsReversed)).Select(x => x.TargetProjectId)
                    );

                visited.AddRange(itemsToGoFrom);
                var nextLevel = getAncestors ? level - 1 : level + 1;

                var nextLevelResult = LoadResolveDependenciesRecursively(itemsToGoFrom, nextLevel, getAncestors,
                    changedItemsDict, startDateOffset, endDateOffset, changedProjects, visited);

                foreach (var item in nextLevelResult)
                {
                    if (!result.ContainsKey(item.Key))
                        result.Add(item.Key, item.Value);
                }
            }
            return DependencyChain<DependencyResolveModel.ProjectItem>.CreateFrom(result);
        }
        /// <summary>
        /// Applies changes to projects/scenarios entered by user in resolve dependency conflicts modal form.
        /// </summary>
        /// <param name="model">A collection of resolve conflict items, one per project.</param>
        public void ResolveConflicts(DependencyResolveModel model)
        {
            if (model == null || Guid.Empty.Equals(model.ProjectId) || model.ResolvePlan == null || !model.ResolvePlan.Any())
                return;

            var changedItems = model.ResolvePlan.GroupBy(t => t.Id).ToDictionary(gr => gr.Key, grel => grel.FirstOrDefault());
            var dbItems = LoadDependencies(changedItems.Keys).GroupBy(t => t.Id).ToDictionary(gr => gr.Key, grel => grel.FirstOrDefault());

            if (changedItems == null || !changedItems.Any())
                return;
            var updatedProjects = new Dictionary<Guid, double>();
            foreach (var group in changedItems)
            {
                var item = group.Value;
                var id = group.Key;

                switch (item.Action)
                {
                    case DependencyResolveModel.ActionType.ConvertToLink:
                        if (dbItems.ContainsKey(id))
                        {
                            // update dependency item itself
                            var dbItem = dbItems[id];
                            dbItem.Type = (short)ProjectDependencyModel.ProjectDependencyType.Link;
                            DbContext.Entry(dbItem).State = EntityState.Modified;
                            // gather projects which timestamps should be updated
                            if (!updatedProjects.ContainsKey(dbItem.SourceProjectId))
                                updatedProjects.Add(dbItem.SourceProjectId, 0);
                            if (!updatedProjects.ContainsKey(dbItem.TargetProjectId))
                                updatedProjects.Add(dbItem.TargetProjectId, 0);
                        }
                        break;
                    case DependencyResolveModel.ActionType.RemoveDependency:
                        if (dbItems.ContainsKey(id))
                        {
                            var dbItem = dbItems[id];
                            // gather projects which timestamps should be updated
                            if (!updatedProjects.ContainsKey(dbItem.SourceProjectId))
                                updatedProjects.Add(dbItem.SourceProjectId, 0);
                            if (!updatedProjects.ContainsKey(dbItem.TargetProjectId))
                                updatedProjects.Add(dbItem.TargetProjectId, 0);
                            // update dependency item itself
                            DbContext.Entry(dbItem).State = EntityState.Deleted;
                        }
                        break;
                    default:
                        if (DependencyResolveModel.ActionType.DontMove.Equals(item.Action) ||
                            !item.MoveStartDate.HasValue || !item.MoveEndDate.HasValue ||
                            (item.ProjectId == item.SourceProjectId && !item.SourceStartDate.HasValue) ||
                            (item.ProjectId == item.TargetProjectId && !item.TargetStartDate.HasValue))
                            break;
                        var timespan = 0D;
                        if (item.ProjectId == item.SourceProjectId)
                            timespan = item.MoveStartDate.Value.Subtract(item.SourceStartDate.Value).TotalMilliseconds;
                        else if (item.ProjectId == item.TargetProjectId)
                            timespan = item.MoveStartDate.Value.Subtract(item.TargetStartDate.Value).TotalMilliseconds;
                        // gather projects which active scenarios should be updated
                        if (!updatedProjects.ContainsKey(item.ProjectId))
                            updatedProjects.Add(item.ProjectId, timespan);
                        break;
                }
            }
            if (!updatedProjects.Any())
                return;
            // update all changed project's timestamps
            var projects = ProjectManager.FindProjects(updatedProjects.Keys);
            var projectIds2Update = updatedProjects.Where(t => t.Value != 0).Select(t => t.Key).ToList();
            var scenariosDict = ScenarioManager.GetScenarios4Projects(projectIds2Update, ScenarioType.Portfolio, ScenarioStatus.Active)
                .GroupBy(t => t.ParentId.Value).ToDictionary(t => t.Key, x => x.FirstOrDefault());
            var scenarioIds2Update = scenariosDict.Values.Select(t => t.Id).ToList();
            var scenarioDetailsDict = ScenarioManager.GetScenarioDetails(scenarioIds2Update)
                .GroupBy(t => t.ParentID).ToDictionary(t => t.Key, x => x.ToList()); ;
            var teamAllocationsDict = TeamManager.GetTeamsAllocation(scenarioIds2Update)
                .GroupBy(t => t.ScenarioId).ToDictionary(gr => gr.Key, grel => grel.ToList());
            var resourceAllocationsDict = DbContext.PeopleResourceAllocations.Where(t => scenarioIds2Update.Contains(t.ScenarioId))
                .GroupBy(t => t.ScenarioId).ToDictionary(gr => gr.Key, grel => grel.ToList());
            var resourceIds = resourceAllocationsDict.Values.SelectMany(t => t.Select(x => x.PeopleResourceId)).Distinct();
            var resources = (new PeopleResourcesManager(DbContext)).GetPeopleResourceWithTeams4Resources(resourceIds)
                .GroupBy(t => t.Id).ToDictionary(gr => gr.Key, grel => grel.FirstOrDefault());
            foreach (var item in projects)
            {
                // update project timestamp
                ProjectManager.Save((ProjectModel)item);
                // shift scenarios and all related allocations
                var offsetMs = updatedProjects.ContainsKey(item.Id) ? updatedProjects[item.Id] : 0;
                if (offsetMs != 0)
                {
                    if (scenariosDict.ContainsKey(item.Id))
                    {
                        // shift scenario
                        var scenario = scenariosDict[item.Id];
                        var replWeeks = BuildReplacementWeeksDict(scenario.StartDate.Value, scenario.EndDate.Value, offsetMs);
                        //var offsetWeeks = 
                        scenario.StartDate = scenario.StartDate.Value.AddMilliseconds(offsetMs);
                        scenario.EndDate = scenario.EndDate.Value.AddMilliseconds(offsetMs);
                        if (scenarioDetailsDict.ContainsKey(scenario.Id))
                        {
                            foreach (var sd in scenarioDetailsDict[scenario.Id])
                            {
                                var clone = new ScenarioDetail
                                {
                                    Id = Guid.NewGuid(),
                                    Cost = sd.Cost,
                                    ExpenditureCategoryId = sd.ExpenditureCategoryId,
                                    ParentID = sd.ParentID,
                                    Quantity = sd.Quantity,
                                    WeekOrdinal = sd.WeekOrdinal,
                                    WeekEndingDate = replWeeks.ContainsKey(sd.WeekEndingDate.Value) ? replWeeks[sd.WeekEndingDate.Value] : sd.WeekEndingDate
                                };
                                DbContext.Entry(sd).State = EntityState.Deleted;
                                if (replWeeks.ContainsKey(sd.WeekEndingDate.Value))
                                    DbContext.Entry(clone).State = EntityState.Added;
                            };
                        }
                        if (teamAllocationsDict.ContainsKey(scenario.Id))
                        {
                            foreach (var ta in teamAllocationsDict[scenario.Id])
                            {
                                var clone = new TeamAllocation
                                {
                                    Id = Guid.NewGuid(),
                                    ExpenditureCategoryId = ta.ExpenditureCategoryId,
                                    Quantity = ta.Quantity,
                                    ScenarioId = ta.ScenarioId,
                                    TeamId = ta.TeamId,
                                    WeekEndingDate = replWeeks.ContainsKey(ta.WeekEndingDate) ? replWeeks[ta.WeekEndingDate] : ta.WeekEndingDate
                                };
                                DbContext.Entry(ta).State = EntityState.Deleted;
                                if (replWeeks.ContainsKey(ta.WeekEndingDate))
                                    DbContext.Entry(clone).State = EntityState.Added;
                            };
                        }
                        if (resourceAllocationsDict.ContainsKey(scenario.Id))
                        {
                            foreach (var ra in resourceAllocationsDict[scenario.Id])
                            {
                                var newWeek = ra.WeekEndingDate;
                                if (resources.ContainsKey(ra.PeopleResourceId))
                                {
                                    var res = resources[ra.PeopleResourceId];
                                    if (replWeeks.ContainsKey(ra.WeekEndingDate))
                                    {
                                        newWeek = replWeeks[ra.WeekEndingDate];

                                        if (res.Teams.Any(team => (team.StartDate <= newWeek) && (!team.EndDate.HasValue || team.EndDate >= newWeek)))
                                        {
                                            var clone = new PeopleResourceAllocation
                                            {
                                                Id = Guid.NewGuid(),
                                                ExpenditureCategoryId = ra.ExpenditureCategoryId,
                                                PeopleResourceId = ra.PeopleResourceId,
                                                Quantity = ra.Quantity,
                                                ScenarioId = ra.ScenarioId,
                                                TeamId = ra.TeamId,
                                                WeekEndingDate = newWeek
                                            };
                                            DbContext.Entry(ra).State = EntityState.Deleted;
                                            DbContext.Entry(clone).State = EntityState.Added;
                                        }
                                        else
                                        {
                                            DbContext.Entry(ra).State = EntityState.Deleted;
                                        }
                                    }
                                    else
                                    {
                                        DbContext.Entry(ra).State = EntityState.Deleted;
                                    }
                                }
                            }
                        }
                    }
                }
            }
        }

        private Dictionary<DateTime, DateTime> BuildReplacementWeeksDict(DateTime startDate, DateTime endDate, double offsetMs)
        {
            var result = new Dictionary<DateTime, DateTime>();
            var scenarioWeeks = FiscalCalendarManager.GetWeekendingsByRange(startDate, endDate, DbContext);
            var modifiedScenarioWeeks = new List<DateTime>();
            modifiedScenarioWeeks = offsetMs > 0 ? FiscalCalendarManager.GetWeekendingsByRange(startDate, endDate.AddMilliseconds(offsetMs), DbContext) : FiscalCalendarManager.GetWeekendingsByRange(startDate.AddMilliseconds(offsetMs), endDate, DbContext);
            var offsetWeeks = (modifiedScenarioWeeks.Count - scenarioWeeks.Count) * (offsetMs > 0 ? 1 : -1);
            var indexOffset = offsetWeeks < 0 ? 0 : offsetWeeks;
            for (var i = 0; i < scenarioWeeks.Count; i++)
            {
                result.Add(scenarioWeeks[i], modifiedScenarioWeeks[i + indexOffset]);
            }
            return result;
        }

    }
}