using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Linq;
using System.Web.Mvc;
using System.Data.Entity;
using jQuery.DataTables.Mvc;
using EnVisage.Models;
using EnVisage.Code;
using EnVisage.Code.BLL;
using System.Net;
using EnVisage.Code.Charts;

namespace EnVisage.Controllers
{
	[Authorize]
	public class ForecastDashboardController : BaseController
    {

        /// <summary>
        /// Direct GET to the page - returns main view
        /// </summary>
        public ActionResult Index(string menuId, string pageModel, string additionalFilters)
        {
            return PartialView("_forecastDashboard",      
                new ForecastDashboardOptionsModel 
				{ 
					MenuId = menuId,
					// SA. ENV-492. Added to trace the source page, where forecast widget to be shown
					SourcePageModel = !String.IsNullOrEmpty(pageModel) ? pageModel : String.Empty,
					AdditionalFilterParams = additionalFilters 
				});
        }
        
        /// <summary>
		/// Direct POST request to the page - returns main part of dashboard table - active scenarios
		/// </summary>
		[HttpPost]
        public JsonResult Index(JQueryDataTablesModel jQueryDataTablesModel, ForecastDashboardFilterModel filter)
		{
			int totalRecordCount;
			int searchRecordCount;

            var scenarios = GetScenarios(jQueryDataTablesModel.iDisplayStart,
            jQueryDataTablesModel.iDisplayLength, jQueryDataTablesModel.GetSortedColumns(),
            out totalRecordCount, out searchRecordCount, jQueryDataTablesModel.sSearch, filter);

			return this.DataTablesJson(scenarios, //Json("",JsonRequestBehavior.AllowGet);//
                totalRecordCount,
                searchRecordCount,
                jQueryDataTablesModel.sEcho);
		}

		/// <summary>
		/// Adds Scenario to the given Scenario Group
		/// </summary>
		[HttpPost]
		public JsonResult CopyScenarioToGroup(Guid? scenarioId, Guid? groupId)
		{
			try
			{
				if (!scenarioId.HasValue || !groupId.HasValue)
					throw new Exception("ScenarioId and GroupId can't be null.");
				if (scenarioId == Guid.Empty || groupId == Guid.Empty)
					throw new Exception("ScenarioId and GroupId can't be empty.");

				CopyToGroup(scenarioId.Value, groupId.Value);
				return Json(new { Status = "Ok" });

			}
			catch (Exception ex)
			{
				return Json(new { Status = "Error", ErrorMsg = ex.Message, ErrorDetails = ex.ToString() });
			}
		}

		/// <summary>
		/// Removes Scenario from the given Scenario Group
		/// </summary>
		[HttpPost]
		public JsonResult ExtractFromGroup(Guid? scenarioId, Guid? groupId)
		{
			try
			{
				if (!scenarioId.HasValue || !groupId.HasValue)
					throw new Exception("ScenarioId and GroupId can't be null.");
				if (scenarioId == Guid.Empty || groupId == Guid.Empty)
					throw new Exception("ScenarioId and GroupId can't be empty.");

				DeleteFromGroup(scenarioId.Value, groupId.Value);
				return Json(new { Status = "Ok" });

			}
			catch (Exception ex)
			{
				return Json(new { Status = "Error", ErrorMsg = ex.Message, ErrorDetails = ex.ToString() });
			}
		}

		/// <summary>
		/// Returns data for main chart
		/// </summary>
        [HttpPost]
        public JsonResult GetGraphData(ForecastDashboardChartFilterModel filter)
		{
            if (!filter.StartDate.HasValue)
                filter.StartDate = new DateTime(DateTime.Today.Year, 1, 1);
		    
            if (!filter.EndDate.HasValue)
                filter.EndDate = new DateTime(DateTime.Today.Year, 12, 31);
	
            var scenarios = new List<Scenario>();
			var data = new Dictionary<string, List<long[]>>();
            var mode = ForecastDashboardMode.MainDashboard;

            if (filter.AdditionalParams != null && filter.AdditionalParams.ContainsKey("mode"))
			{
                filter.Teams = new List<Guid>();
                filter.Views = new List<Guid>();

                if ((string)filter.AdditionalParams["mode"] == "team" && filter.AdditionalParams.ContainsKey("teamId"))
                {
                        mode = ForecastDashboardMode.TeamForecast;
                    var teamId = Guid.Empty;
                    if (Guid.TryParse((string)filter.AdditionalParams["teamId"], out teamId))
                        filter.Teams.Add(teamId);
                    }
                if ((string)filter.AdditionalParams["mode"] == "view" && filter.AdditionalParams.ContainsKey("viewId"))
                    {
                        mode = ForecastDashboardMode.ViewForecast;
                    var viewId = Guid.Empty;
                    if (Guid.TryParse((string)filter.AdditionalParams["viewId"], out viewId))
                        filter.Views.Add(viewId);
                    }    
                }

            data = new DashboardCharthManager().GetData(filter.StartDate.Value, filter.EndDate.Value, Guid.Parse(User.Identity.GetID()), filter.Type, filter.ProjectStatuses, filter.ProjectTypes, mode, filter.Teams, filter.Views, filter.IsUOMHours, filter.IsLaborMode, filter.FilterGroups, filter.StrategicGoals);

			var headers = new List<Object>();

			// Get project statuses
			//var statuses = DbContext.Status.Where(x => !x.IsSystem).Select(x => x.Name ).ToList();
            var statuses = DbContext.Status.Where(x => !x.IsSystem).OrderByDescending(x => x.Probability100 ).Select(x => x.Name).ToList();

            foreach (var statusName in statuses)
			{
				if (data.ContainsKey(statusName))
				{
					headers.Add(new
					{
						label = statusName,
						data = data[statusName],
						type = "Cost",
						stack = true,
						lines = new { show = true, fill = true }
					});

					headers.Add(new
					{
						label = statusName,
						data = data[statusName + "Q"],
						type = "Quantity",
						stack = true,
						lines = new { show = true, fill = true }
					});
				}
			}

			if (mode == ForecastDashboardMode.MainDashboard)
			{
                //headers.Add(new
                //{
                //    label = "Training",
                //    data = data["training"],
                //    type = "Cost",
                //    stack = true,
                //    lines = new { show = true, fill = true }
                //});

                //headers.Add(new
                //{
                //    label = "Training",
                //    data = data["trainingQ"],
                //    type = "Quantity",
                //    stack = true,
                //    lines = new { show = true, fill = true }
                //});

                var capacities = DbContext.Scenarios.Where(x => x.Type == (int)ScenarioType.Capacity && x.Status == (int)ScenarioStatus.Active).Select(x => x.Name).ToList();
				foreach (var capacity in capacities)
				{
					if (data.ContainsKey(capacity))
					{
						headers.Add(new
						{
							label = capacity,
							data = data[capacity],
							type = "Cost",
							stack = false,
							lines = new { show = true, fill = false }
						});

						headers.Add(new
						{
							label = capacity,
							data = data[capacity + "Q"],
							type = "Quantity",
							stack = false,
							lines = new { show = true, fill = false }
						});
					}
				}
			}

			if (data["trainingQ"] != null && data["trainingQ"].Any(i => i.Length > 1 && i[1] > 0))
			{
				headers.Add(new
				{
					label = "Training",
					data = data["training"],
					type = "Cost",
					stack = true,
					lines = new { show = true, fill = true }
				});

				headers.Add(new
				{
					label = "Training",
					data = data["trainingQ"],
					type = "Quantity",
					stack = true,
					lines = new { show = true, fill = true }
				});
			}

			if (data["vacationQ"] != null && data["vacationQ"].Any(i => i.Length > 1 && i[1] > 0))
			{
				headers.Add(new
				{
					label = "Vacation",
					data = data["vacation"],
					type = "Cost",
					stack = true,
					lines = new { show = true, fill = true }
				});

				headers.Add(new
				{
					label = "Vacation",
					data = data["vacationQ"],
					type = "Quantity",
					stack = true,
					lines = new { show = true, fill = true }
				});
			}

			headers.Add(new
			{
				label = "Actual Capacity",
				data = data["resourceCapacity"],
				type = "Cost",
				stack = false,
				lines = new { show = true, fill = false }
			});

			headers.Add(new
			{
				label = "Actual Capacity",
				data = data["resourceCapacityQ"],
				type = "Quantity",
				stack = false,
				lines = new { show = true, fill = false }
			});
			
            headers.Add(new
            {
                label = "Planned Capacity",
                data = data["totalPlannedCapacity"],
                type = "Cost",
                stack = false,
                lines = new { show = true, fill = false }
            });

            headers.Add(new
            {
                label = "Planned Capacity",
                data = data["totalPlannedCapacityQ"],
                type = "Quantity",
                stack = false,
                lines = new { show = true, fill = false }
            });
            

            return Json(headers);
		}

        /// <summary>
        /// Returns data for pie chart
        /// </summary>
        [HttpPost]
        public JsonResult GetClassPieData(ForecastDashboardPieChartFilterModel filter)
        {
            if (!filter.StartDate.HasValue)
                filter.StartDate = DateTime.Today.AddMonths(-1);

            if (!filter.EndDate.HasValue)
                filter.EndDate = filter.StartDate.Value.AddYears(1);

            List<Scenario> scenarios = new List<Scenario>();

            ForecastDashboardMode dashboardMode = ForecastDashboardMode.MainDashboard;

            if (filter.AdditionalParams != null && filter.AdditionalParams.ContainsKey("mode"))
            {
                filter.Teams = new List<Guid>();
                filter.Views = new List<Guid>();

                if ((string)filter.AdditionalParams["mode"] == "team" && filter.AdditionalParams.ContainsKey("teamId"))
            {
                dashboardMode = ForecastDashboardMode.TeamForecast;
                    var teamId = Guid.Empty;
                    if (Guid.TryParse((string)filter.AdditionalParams["teamId"], out teamId))
                        filter.Teams.Add(teamId);
            }
                else if ((string)filter.AdditionalParams["mode"] == "view" && filter.AdditionalParams.ContainsKey("viewId"))
            {
                dashboardMode = ForecastDashboardMode.ViewForecast;
                    var viewId = Guid.Empty;
                    if (Guid.TryParse((string)filter.AdditionalParams["viewId"], out viewId))
                        filter.Views.Add(viewId);
            }
            }

            var data = new DashboardCharthManager().GetPieData(filter.StartDate.Value, filter.EndDate.Value, Guid.Parse(User.Identity.GetID()), dashboardMode, filter.Teams, filter.Views, filter.IsLaborMode, filter.FilterGroups, filter.ProjectTypes, filter.ProjectStatuses, filter.StrategicGoals, filter.PresetColor, filter.PieForecastCost);
            return Json(data);
        }

        /// <summary>
        /// Returns data for main chart
        /// </summary>
        /// <remarks>SA. ENV-608</remarks>
        [HttpPost]
        public JsonResult GetPerformanceGraphData(ForecastDashboardPerformanceGraphFilterModel filter)
        {
            var userId = Guid.Parse(User.Identity.GetID());
            var mode = ForecastDashboardMode.MainDashboard;

            Dictionary<string, StackGraphDataBlock> graphData;
            DashboardCharthManager mngr = new DashboardCharthManager();

            switch (filter.GraphMode)
            {
                case 1:
                    // Top 5 cost projects performance
                    graphData = mngr.GetPerformanceGraphDataByProjects(mode, userId, DateTime.Today, filter.FilterGroups, filter.ProjectTypes, filter.Teams, filter.Views);
                    break;

                case 2:
                    // Top 5 project types performance
                    graphData = mngr.GetPerformanceGraphDataByProjectClass(mode, userId, DateTime.Today, filter.FilterGroups, filter.ProjectTypes, filter.Teams, filter.Views);
                    break;
                
                default:
                    // Invalid data type requested
                    graphData = new Dictionary<string, StackGraphDataBlock>();
                    break;
            }

            return Json(graphData);
        }

		[HttpGet]
		public string CheckIfActive()
		{
			string result = "";
			string scenarioId = Request.QueryString["scenarioId"];
			var guidId = new Guid(scenarioId);
			var context = new EnVisageEntities();
			var scenario = (from c in context.Scenarios where c.Id == guidId select c).FirstOrDefault();
			if (scenario != null && scenario.Status != (int)ScenarioStatus.Active)
			{
				var activeScenario = (from c in context.Scenarios where c.ParentId == scenario.ParentId && scenario.Type == c.Type && c.Status == (int?)ScenarioStatus.Active select c).FirstOrDefault();
				if (activeScenario != null)
					result = activeScenario.Id.ToString();
			}

			return result;
		}

		[HttpGet]
		public void ToggleStatus()
		{
			string scenarioId = Request.QueryString["scenarioId"];
			var guidId = new Guid(scenarioId);
			var context = new EnVisageEntities();
			var scenario = (from c in context.Scenarios where c.Id == guidId select c).FirstOrDefault();
			if (scenario != null && scenario.Status != (int)ScenarioStatus.Active)
			{
				var activeScenarios = (from c in context.Scenarios where c.ParentId == scenario.ParentId && scenario.Type == c.Type && c.Status == (int?)ScenarioStatus.Active select c).ToList();
				foreach (var scen in activeScenarios)
				{
					scen.Status = (int)ScenarioStatus.Inactive;
				}
				scenario.Status = (int?)ScenarioStatus.Active;
			}
			else if (scenario != null)
                scenario.Status = (int)ScenarioStatus.Inactive;
			context.SaveChanges();
		}

		#region private methods
		/// <summary>
		/// Returns data for main dashboard table
		/// </summary>
		private IList<ForecastDashboardModel> GetScenarios(int startIndex,
			int pageSize,
			ReadOnlyCollection<SortedColumn> sortedColumns,
			out int totalRecordCount,
			out int searchRecordCount,
			string searchString,
            ForecastDashboardFilterModel filter)
		{
            var startDate = (filter.StartDate ?? DateTime.Today).Date;
            var endDate = (filter.EndDate ?? DateTime.Today).Date;
            var userId = SecurityManager.GetUserPrincipal();

            if (filter.AdditionalParams != null && filter.AdditionalParams.ContainsKey("mode"))
				{
                filter.Teams = new List<Guid>();
                filter.Views = new List<Guid>();

                if ((string)filter.AdditionalParams["mode"] == "team" && filter.AdditionalParams.ContainsKey("teamId"))
            {
                    var teamId = Guid.Empty;
                    if (Guid.TryParse((string)filter.AdditionalParams["teamId"], out teamId))
                        filter.Teams.Add(teamId);
            }
                else if ((string)filter.AdditionalParams["mode"] == "view" && filter.AdditionalParams.ContainsKey("viewId"))
			{
                    var viewId = Guid.Empty;
                    if (Guid.TryParse((string)filter.AdditionalParams["viewId"], out viewId))
                        filter.Views.Add(viewId);
			}
			}

            var filteredTeams = (new TeamManager(DbContext)).GetTeamsByUser(userId.ToString("D"), filter.Teams, filter.Views);
            var filteredTeamsIds = filteredTeams.Select(x => x.Id).ToList();

            var projects =
                (from proj in DbContext.Projects
                join sc in DbContext.VW_ProjectAccessByUser on proj.Id equals sc.Id
                 where sc.UserId.Equals(userId) && !proj.HasChildren && proj.Team2Project.Any(p => filteredTeamsIds.Contains(p.TeamId))
                select proj).Distinct();

            if (filter.ProjectTypes != null && filter.ProjectTypes.Count > 0)
                projects = projects.Where(i => filter.ProjectTypes.Contains(i.TypeId));

            if (filter.ProjectStatuses != null && filter.ProjectStatuses.Count > 0)
                projects = projects.Where(i => filter.ProjectStatuses.Contains(i.StatusId));

            if (filter.StrategicGoals != null && filter.StrategicGoals.Count > 0)
            {
                var sgs = DbContext.StrategicGoal2Project.Where(x=> filter.StrategicGoals.Contains(x.StrategicGoalId)).Select(x => x.ProjectId).ToList();
                projects = projects.Where(x => sgs.Contains(x.Id));
            }

            // SA. ENV-607
            Dictionary<Guid, Type> projectTypes = DbContext.Types.AsNoTracking().ToDictionary(x => x.Id);

			var query = new List<ForecastDashboardModel>();
            var teams = new List<Team>() { new Team() { Id = Guid.Empty } };

            if (filter.FilterGroupByTeam)
                teams = filteredTeams;

            foreach (var team in teams)
            {
                var projectsToEnumerate = projects.Include("Status").Where(p => !filter.FilterGroupByTeam || (filter.FilterGroupByTeam && p.Team2Project.Select(t2p => t2p.TeamId).Contains(team.Id))).ToArray();
                var projectsToEnumerateIds = projectsToEnumerate.Select(x => x.Id).ToArray();
                var projectsScenarios = DbContext.Scenarios.Include("CostSavings1").Where(x => projectsToEnumerateIds.Contains(x.ParentId.Value)).ToArray();
                var projectsScenariosIds = projectsScenarios.Select(x => x.Id).ToArray();
                var scGroups = DbContext.Scenario2Group.Where(x => projectsScenariosIds.Contains(x.ScenarioId)).ToArray();
                foreach (var project in projectsToEnumerate)
                {
                    Scenario activescenario = null;
                    var goals = (from s in project.StrategicGoal2Project select s.StrategicGoal.Name).ToArray();
                    var goalsStr = string.Join(", ", goals);
                    if (filter.FilterGroups != null && filter.FilterGroups.Count > 0)
                    {
                        activescenario = (from c in projectsScenarios
                                          where c.ParentId == project.Id && c.StartDate <= endDate && c.EndDate >= startDate && c.Status == (int?)ScenarioStatus.Active && c.Scenario2Group.Any(g => filter.FilterGroups.Contains(g.GroupId))
                                          select c).FirstOrDefault();

                        if (activescenario == null)
                            activescenario = (from c in projectsScenarios
                                              where c.ParentId == project.Id && c.StartDate <= endDate && c.EndDate >= startDate && c.Scenario2Group.Any(g => filter.FilterGroups.Contains(g.GroupId))
                                              select c).FirstOrDefault();
                    }
                    else
                    {
                        activescenario = (from c in projectsScenarios
                                          where c.ParentId == project.Id && c.StartDate <= endDate && c.EndDate >= startDate && c.Status == (int?)ScenarioStatus.Active
                                          select c).FirstOrDefault();

                        if (activescenario == null)
                            activescenario = (from c in projectsScenarios
                                              where c.ParentId == project.Id && c.StartDate <= endDate && c.EndDate >= startDate
                                              select c).FirstOrDefault();
                    }
                    if (activescenario != null)
                    {
                        
                        ForecastDashboardModel modelItem = new ForecastDashboardModel()
                        {                                             
                            Id = activescenario.Id,
                            ProjectId = (Guid)activescenario.ParentId,
                            ProjectName = project.Name + (project.ParentProject != null ? ": " + project.ParentProject.Name : ""),
                            TeamName = team.Name,
                            TeamId = team.Id,
                            Name = activescenario.Name,
                            Type = (ScenarioType?)activescenario.Type,
                            ProjectedRevenue = activescenario.ProjectedRevenue,
                            ExpectedGrossMargin = activescenario.ExpectedGrossMargin,
                            ExpectedGrossMargin_LM = activescenario.ExpectedGrossMargin_LM,
                            CalculatedGrossMargin = activescenario.CalculatedGrossMargin,
                            CalculatedGrossMargin_LM = activescenario.CalculatedGrossMargin_LM,
                            UseLMMargin = activescenario.UseLMMargin,
                            CGSplit = activescenario.CGSplit,
                            EFXSplit = activescenario.EFXSplit,
                            StartDate = activescenario.StartDate,
                            EndDate = activescenario.EndDate,
                            Priority = project.Priority,
                            Probability = (decimal)project.Probability,
                            TDDirectCosts = activescenario.TDDirectCosts,
                            TDDirectCosts_LM = activescenario.TDDirectCosts_LM,
                            BUDirectCosts = activescenario.BUDirectCosts,
                            BUDirectCosts_LM = activescenario.BUDirectCosts_LM,
                            ScenarioStatus = (ScenarioStatus?)activescenario.Status,
                            Color = activescenario.Color,
                            CostSavings = activescenario.CostSavings,
                            CostSavingsDuration = (activescenario.CostSavingsEndDate.HasValue && activescenario.CostSavingsStartDate.HasValue) ? (activescenario.CostSavingsEndDate.Value.Month - activescenario.CostSavingsStartDate.Value.Month) + 12 * (activescenario.CostSavingsEndDate.Value.Year - activescenario.CostSavingsStartDate.Value.Year) + 1 : (int?)null,
                            ROIDate = activescenario.ROIDate, // GetROIDate(activescenario),
                            HardSoftSavings = (activescenario.CostSavings1.Any()) ? (activescenario.CostSavingsType.Value == 1 ? "Hard" : "Soft") : string.Empty,
                            ProjectDeadline = project.Deadline,
							ProjectStatus = project.Status != null ? project.Status.Name : string.Empty,
                            Groups = scGroups.Where(x => x.ScenarioId == activescenario.Id).Select(x => x.GroupId).ToList(),
                            InactiveCount = (from c in projectsScenarios
                                             where c.ParentId == project.Id && c.StartDate <= endDate
                                                 && c.EndDate >= startDate && c.Status == (int?)ScenarioStatus.Inactive && c.Id != activescenario.Id
                                             select c).Count(),
                            InactiveScenarios = (from c in projectsScenarios
                                             where c.ParentId == project.Id && c.StartDate <= endDate
                                                 && c.EndDate >= startDate && c.Status == (int?)ScenarioStatus.Inactive && c.Id != activescenario.Id
                                             select c).OrderBy(z=>z.Name).ToDictionary(x=> x.Id.ToString(), x=> x.Name),
                            StrategicGoals = goalsStr
                        };

                        // SA. ENV-607. Begin
                        Type currentProjectType = null;

                        if (projectTypes.ContainsKey(project.TypeId))
                            currentProjectType = projectTypes[project.TypeId];

                        if (project.PerformanceYellowThreshold.HasValue)
                            modelItem.PerformanceThresholdYellow = project.PerformanceYellowThreshold;
                        else
                            if ((currentProjectType != null) && (currentProjectType.PerformanceYellowThreshold.HasValue))
                                modelItem.PerformanceThresholdYellow = currentProjectType.PerformanceYellowThreshold;

                        if (project.PerformanceRedThreshold.HasValue)
                            modelItem.PerformanceThresholdRed = project.PerformanceRedThreshold;
                        else
                            if ((currentProjectType != null) && (currentProjectType.PerformanceRedThreshold.HasValue))
                                modelItem.PerformanceThresholdRed = currentProjectType.PerformanceRedThreshold;
                        // SA. ENV-607. End

                        query.Add(modelItem);

                    }
                    foreach (var sortedColumn in sortedColumns)
                    {
                        switch (sortedColumn.PropertyName)
                        {
                            case "Id": 
                                if (sortedColumn.Direction == SortingDirection.Ascending)
                                    query = filter.FilterGroupByTeam ? query.OrderBy(c => c.TeamName).ThenBy(c => c.Id).ToList() : query.OrderBy(c => c.Id).ToList();
                                else
                                    query = filter.FilterGroupByTeam ? query.OrderBy(c => c.TeamName).ThenByDescending(c => c.Id).ToList() : query.OrderByDescending(c => c.Id).ToList();
                                break;
                            case "ProjectName":
                                if (sortedColumn.Direction == SortingDirection.Ascending)
                                    query = filter.FilterGroupByTeam ? query.OrderBy(c => c.TeamName).ThenBy(c => c.ProjectName).ToList() : query.OrderBy(c => c.ProjectName).ToList();
                                else
                                    query = filter.FilterGroupByTeam ? query.OrderBy(c => c.TeamName).ThenByDescending(c => c.ProjectName).ToList() : query.OrderByDescending(c => c.ProjectName).ToList();
                                break;
                            case "ProjectedRevenue":
                                if (sortedColumn.Direction == SortingDirection.Ascending)
                                    query = filter.FilterGroupByTeam ? query.OrderBy(c => c.TeamName).ThenBy(c => c.ProjectedRevenue).ToList() : query.OrderBy(c => c.ProjectedRevenue).ToList();
                                else
                                    query = filter.FilterGroupByTeam ? query.OrderBy(c => c.TeamName).ThenByDescending(c => c.ProjectedRevenue).ToList() : query.OrderByDescending(c => c.ProjectedRevenue).ToList();
                                break;
                            case "ExpectedGrossMargin":
                                if (sortedColumn.Direction == SortingDirection.Ascending)
                                    query = filter.FilterGroupByTeam ? query.OrderBy(c => c.TeamName).ThenBy(c => c.ExpectedGrossMargin).ToList() : query.OrderBy(c => c.ExpectedGrossMargin).ToList();
                                else
                                    query = filter.FilterGroupByTeam ? query.OrderBy(c => c.TeamName).ThenByDescending(c => c.ExpectedGrossMargin).ToList() : query.OrderByDescending(c => c.ExpectedGrossMargin).ToList();
                                break;
                            case "ExpectedGrossMargin_LM":
                                if (sortedColumn.Direction == SortingDirection.Ascending)
                                    query = filter.FilterGroupByTeam ? query.OrderBy(c => c.TeamName).ThenBy(c => c.ExpectedGrossMargin_LM).ToList() : query.OrderBy(c => c.ExpectedGrossMargin_LM).ToList();
                                else
                                    query = filter.FilterGroupByTeam ? query.OrderBy(c => c.TeamName).ThenByDescending(c => c.ExpectedGrossMargin_LM).ToList() : query.OrderByDescending(c => c.ExpectedGrossMargin_LM).ToList();
                                break;
                            case "CalculatedGrossMargin":
                                if (sortedColumn.Direction == SortingDirection.Ascending)
                                    query = filter.FilterGroupByTeam ? query.OrderBy(c => c.TeamName).ThenBy(c => c.CalculatedGrossMargin).ToList() : query.OrderBy(c => c.CalculatedGrossMargin).ToList();
                                else
                                    query = filter.FilterGroupByTeam ? query.OrderBy(c => c.TeamName).ThenByDescending(c => c.CalculatedGrossMargin).ToList() : query.OrderByDescending(c => c.CalculatedGrossMargin).ToList();
                                break;
                            case "CalculatedGrossMargin_LM":
                                if (sortedColumn.Direction == SortingDirection.Ascending)
                                    query = filter.FilterGroupByTeam ? query.OrderBy(c => c.TeamName).ThenBy(c => c.CalculatedGrossMargin_LM).ToList() : query.OrderBy(c => c.CalculatedGrossMargin_LM).ToList();
                                else
                                    query = filter.FilterGroupByTeam ? query.OrderBy(c => c.TeamName).ThenByDescending(c => c.CalculatedGrossMargin_LM).ToList() : query.OrderByDescending(c => c.CalculatedGrossMargin_LM).ToList();
                                break;
                            case "CGSplit":
                                if (sortedColumn.Direction == SortingDirection.Ascending)
                                    query = filter.FilterGroupByTeam ? query.OrderBy(c => c.TeamName).ThenBy(c => c.CGSplit).ToList() : query.OrderBy(c => c.CGSplit).ToList();
                                else
                                    query = filter.FilterGroupByTeam ? query.OrderBy(c => c.TeamName).ThenByDescending(c => c.CGSplit).ToList() : query.OrderByDescending(c => c.CGSplit).ToList();
                                break;
                            case "StartDate":
                                if (sortedColumn.Direction == SortingDirection.Ascending)
                                    query = filter.FilterGroupByTeam ? query.OrderBy(c => c.TeamName).ThenBy(c => c.StartDate).ToList() : query.OrderBy(c => c.StartDate).ToList();
                                else
                                    query = filter.FilterGroupByTeam ? query.OrderBy(c => c.TeamName).ThenByDescending(c => c.StartDate).ToList() : query.OrderByDescending(c => c.StartDate).ToList();
                                break;
                            case "EndDate":
                                if (sortedColumn.Direction == SortingDirection.Ascending)
                                    query = filter.FilterGroupByTeam ? query.OrderBy(c => c.TeamName).ThenBy(c => c.EndDate).ToList() : query.OrderBy(c => c.EndDate).ToList();
                                else
                                    query = filter.FilterGroupByTeam ? query.OrderBy(c => c.TeamName).ThenByDescending(c => c.EndDate).ToList() : query.OrderByDescending(c => c.EndDate).ToList();
                                break;
                            case "ProjectDeadline":
                                if (sortedColumn.Direction == SortingDirection.Ascending)
                                    query = filter.FilterGroupByTeam ? query.OrderBy(c => c.TeamName).ThenBy(c => c.ProjectDeadline).ToList() : query.OrderBy(c => c.ProjectDeadline).ToList();
                                else
                                    query = filter.FilterGroupByTeam ? query.OrderBy(c => c.TeamName).ThenByDescending(c => c.ProjectDeadline).ToList() : query.OrderByDescending(c => c.ProjectDeadline).ToList();
                                break;
							case "ProjectStatus":
								if (sortedColumn.Direction == SortingDirection.Ascending)
                                    query = filter.FilterGroupByTeam ? query.OrderBy(c => c.TeamName).ThenBy(c => c.ProjectStatus).ToList() : query.OrderBy(c => c.ProjectStatus).ToList();
								else
                                    query = filter.FilterGroupByTeam ? query.OrderBy(c => c.TeamName).ThenByDescending(c => c.ProjectStatus).ToList() : query.OrderByDescending(c => c.ProjectStatus).ToList();
								break;
							case "Priority":
                                if (sortedColumn.Direction == SortingDirection.Ascending)
                                    query = filter.FilterGroupByTeam ? query.OrderBy(c => c.TeamName).ThenBy(c => c.Priority).ToList() : query.OrderBy(c => c.Priority).ToList();
                                else
                                    query = filter.FilterGroupByTeam ? query.OrderBy(c => c.TeamName).ThenByDescending(c => c.Priority).ToList() : query.OrderByDescending(c => c.Priority).ToList();
                                break;
							case "Probability":
								if (sortedColumn.Direction == SortingDirection.Ascending)
                                    query = filter.FilterGroupByTeam ? query.OrderBy(c => c.TeamName).ThenBy(c => c.Probability).ToList() : query.OrderBy(c => c.Probability).ToList();
								else
                                    query = filter.FilterGroupByTeam ? query.OrderBy(c => c.TeamName).ThenByDescending(c => c.Probability).ToList() : query.OrderByDescending(c => c.Probability).ToList();
								break;
							case "TDDirectCosts":
                                if (sortedColumn.Direction == SortingDirection.Ascending)
                                    query = filter.FilterGroupByTeam ? query.OrderBy(c => c.TeamName).ThenBy(c => c.TDDirectCosts).ToList() : query.OrderBy(c => c.TDDirectCosts).ToList();
                                else
                                    query = filter.FilterGroupByTeam ? query.OrderBy(c => c.TeamName).ThenByDescending(c => c.TDDirectCosts).ToList() : query.OrderByDescending(c => c.TDDirectCosts).ToList();
                                break;
                            case "TDDirectCosts_LM":
                                if (sortedColumn.Direction == SortingDirection.Ascending)
                                    query = filter.FilterGroupByTeam ? query.OrderBy(c => c.TeamName).ThenBy(c => c.TDDirectCosts_LM).ToList() : query.OrderBy(c => c.TDDirectCosts_LM).ToList();
                                else
                                    query = filter.FilterGroupByTeam ? query.OrderBy(c => c.TeamName).ThenByDescending(c => c.TDDirectCosts_LM).ToList() : query.OrderByDescending(c => c.TDDirectCosts_LM).ToList();
                                break;
                            case "BUDirectCosts":
                                if (sortedColumn.Direction == SortingDirection.Ascending)
                                    query = filter.FilterGroupByTeam ? query.OrderBy(c => c.TeamName).ThenBy(c => c.BUDirectCosts).ToList() : query.OrderBy(c => c.BUDirectCosts).ToList();
                                else
                                    query = filter.FilterGroupByTeam ? query.OrderBy(c => c.TeamName).ThenByDescending(c => c.BUDirectCosts).ToList() : query.OrderByDescending(c => c.BUDirectCosts).ToList();
                                break;
                            case "BUDirectCosts_LM":
                                if (sortedColumn.Direction == SortingDirection.Ascending)
                                    query = filter.FilterGroupByTeam ? query.OrderBy(c => c.TeamName).ThenBy(c => c.BUDirectCosts_LM).ToList() : query.OrderBy(c => c.BUDirectCosts_LM).ToList();
                                else
                                    query = filter.FilterGroupByTeam ? query.OrderBy(c => c.TeamName).ThenByDescending(c => c.BUDirectCosts_LM).ToList() : query.OrderByDescending(c => c.BUDirectCosts_LM).ToList();
                                break;
                            case "CostSavings":
                                if (sortedColumn.Direction == SortingDirection.Ascending)
                                    query = filter.FilterGroupByTeam ? query.OrderBy(c => c.TeamName).ThenBy(c => c.CostSavings).ToList() : query.OrderBy(c => c.CostSavings).ToList();
                                else
                                    query = filter.FilterGroupByTeam ? query.OrderBy(c => c.TeamName).ThenByDescending(c => c.CostSavings).ToList() : query.OrderByDescending(c => c.CostSavings).ToList();
                                break;
                            case "CostSavingsDuration":
                                if (sortedColumn.Direction == SortingDirection.Ascending)
                                    query = filter.FilterGroupByTeam ? query.OrderBy(c => c.TeamName).ThenBy(c => c.CostSavingsDuration).ToList() : query.OrderBy(c => c.CostSavingsDuration).ToList();
                                else
                                    query = filter.FilterGroupByTeam ? query.OrderBy(c => c.TeamName).ThenByDescending(c => c.CostSavingsDuration).ToList() : query.OrderByDescending(c => c.CostSavingsDuration).ToList();
                                break;
                            case "ROIDate":
                                if (sortedColumn.Direction == SortingDirection.Ascending)
                                    query = filter.FilterGroupByTeam ? query.OrderBy(c => c.TeamName).ThenBy(c => c.ROIDate).ToList() : query.OrderBy(c => c.ROIDate).ToList();
                                else
                                    query = filter.FilterGroupByTeam ? query.OrderBy(c => c.TeamName).ThenByDescending(c => c.ROIDate).ToList() : query.OrderByDescending(c => c.ROIDate).ToList();
                                break;
                            case "HardSoftSavings":
                                if (sortedColumn.Direction == SortingDirection.Ascending)
                                    query = filter.FilterGroupByTeam ? query.OrderBy(c => c.TeamName).ThenBy(c => c.HardSoftSavings).ToList() : query.OrderBy(c => c.HardSoftSavings).ToList();
                                else
                                    query = filter.FilterGroupByTeam ? query.OrderBy(c => c.TeamName).ThenByDescending(c => c.HardSoftSavings).ToList() : query.OrderByDescending(c => c.HardSoftSavings).ToList();
                                break;
                            case "Status":
                                if (sortedColumn.Direction == SortingDirection.Ascending)
                                    query = filter.FilterGroupByTeam ? query.OrderBy(c => c.TeamName).ThenBy(c => c.ScenarioStatus).ToList() : query.OrderBy(c => c.ScenarioStatus).ToList();
                                else
                                    query = filter.FilterGroupByTeam ? query.OrderBy(c => c.TeamName).ThenByDescending(c => c.ScenarioStatus).ToList() : query.OrderByDescending(c => c.ScenarioStatus).ToList();
                                break;
                            default:
                                if (sortedColumn.Direction == SortingDirection.Ascending)
                                    query = filter.FilterGroupByTeam ? query.OrderBy(c => c.TeamName).ThenBy(c => c.Name).ToList() : query.OrderBy(c => c.Name).ToList();
                                else
                                    query = filter.FilterGroupByTeam ? query.OrderBy(c => c.TeamName).ThenByDescending(c => c.Name).ToList() : query.OrderByDescending(c => c.Name).ToList();
                                break;
                        }
                    }
                }
            }

			totalRecordCount = query.Count();
			searchRecordCount = query.Count();

            // SA. ENV-607. Performance Indication. Begin
            List<ForecastDashboardModel> rowsToDisplay = query.Skip(startIndex).Take(pageSize).ToList();
            var scenarioIds = rowsToDisplay.Select(x => x.Id);

            Dictionary<Guid, VW_ScenarioPerformance> performanceData =
                DbContext.VW_ScenarioPerformance.Where(x => scenarioIds.Contains(x.ForecastScenarioId))
                    .AsNoTracking().ToDictionary(x => x.ForecastScenarioId);

            foreach (ForecastDashboardModel scenarioRow in rowsToDisplay)
            {
                if (performanceData.ContainsKey(scenarioRow.Id) && 
                    scenarioRow.PerformanceThresholdYellow.HasValue &&
                    scenarioRow.PerformanceThresholdRed.HasValue)
                {
                    decimal? variationPercent = performanceData[scenarioRow.Id].VariationPercent;

                    if (variationPercent.HasValue)
                    {
                        if (variationPercent >= scenarioRow.PerformanceThresholdRed.Value)
                            scenarioRow.PerformanceColor = "perf-red";

                        else if ((variationPercent < scenarioRow.PerformanceThresholdRed.Value) &&
                            (variationPercent >= scenarioRow.PerformanceThresholdYellow.Value))
                            scenarioRow.PerformanceColor = "perf-yellow";

                        //if ((variationPercent < scenarioRow.PerformanceThresholdYellow.Value) &&
                        //    (variationPercent >= scenarioRow.PerformanceThresholdGreen.Value))
                        else
                            scenarioRow.PerformanceColor = "perf-green";
                    }
                }
            }
            // SA. ENV-607. End;
            
            return rowsToDisplay;
        }

        //private DateTime? GetROIDate(Scenario scenario)
        //{
        //    if (!scenario.CostSavings.HasValue)
        //        return null;
        //    if (scenario.BUDirectCosts > scenario.CostSavings)
        //        return null;
        //    if (scenario.CostSavings1 == null || scenario.CostSavings1.Count == 0)
        //        return null;
        //    decimal? costSavingsSum = null;
        //    foreach (var costSaving in scenario.CostSavings1.OrderBy(t => t.Year).ThenBy(t => t.Month))
        //    {
        //        if (costSaving.Cost.HasValue)
        //        costSavingsSum += costSaving.Cost;
        //        if (costSavingsSum.HasValue  && costSavingsSum >= scenario.BUDirectCosts)
        //        {
        //            return new DateTime(costSaving.Year, costSaving.Month, 1);
        //        }
        //    }
        //    return null;
        //}

		/// <summary>
		/// Adds Scenario to the given Scenario Group
		/// </summary>
		private void CopyToGroup(Guid scenarioId, Guid groupId)
		{
			var scenarioIsExists = DbContext.Scenarios.Any(x => x.Id == scenarioId);
            if (scenarioIsExists)
			{
                DbContext.Scenario2Group.Add(new Scenario2Group() 
                {
                    Id = Guid.NewGuid(),
                    ScenarioId = scenarioId,
                    GroupId = groupId
                });

				DbContext.SaveChanges();
			}
		}

		/// <summary>
		/// Removes Scenario from the given Scenario Group
		/// </summary>
		private void DeleteFromGroup(Guid scenarioId, Guid groupId)
		{
			var entity = DbContext.Scenario2Group.FirstOrDefault(x => x.ScenarioId == scenarioId && x.GroupId == groupId);
			if (entity != null)
			{
				DbContext.Scenario2Group.Remove(entity);
				DbContext.SaveChanges();
			}
		}

		#endregion

	}
}