using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Globalization;
using System.Data.Entity;
using System.Data.Entity.Core.Objects;
using Microsoft.AspNet.Identity;
using jQuery.DataTables.Mvc;
using EnVisage.Code.HtmlHelpers;
using EnVisage.Models;
using EnVisage.Code;
using EnVisage.Code.BLL;
using EnVisage.Code.Cache;
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 additionalFilters, PagePreferencesList.PagePreferencesSource src)
		{
		    return PartialView("_forecastDashboard",
		                       new ForecastDashboardOptionsModel {MenuId = menuId, AdditionalFilterParams = additionalFilters, Source = src});
		}

		/// <summary>
		/// Direct POST request to the page - returns main part of dashboard table - active scenarios
		/// </summary>
		[HttpPost]
        public JsonResult Index(JQueryDataTablesModel jQueryDataTablesModel, DateTime? filterStartDate, DateTime? startDate, DateTime? filterEndDate, DateTime? endDate, bool filterGroupByTeam)
		{
			int totalRecordCount;
			int searchRecordCount;

			var scenarios = GetScenarios(jQueryDataTablesModel.iDisplayStart,
				jQueryDataTablesModel.iDisplayLength, jQueryDataTablesModel.GetSortedColumns(),
				out totalRecordCount, out searchRecordCount, jQueryDataTablesModel.sSearch,
                filterStartDate, startDate, filterEndDate, endDate, filterGroupByTeam);

			return this.DataTablesJson(scenarios,
				totalRecordCount,
				searchRecordCount,
				jQueryDataTablesModel.sEcho);
		}

	    /// <summary>
	    /// Gets expanded part of the dashboard table - inactive scenarios.
	    /// </summary>
	    /// <param name="id">Scenario Id.</param>
	    /// <param name="filterStartDate">Start date to filter received from dashboard control.</param>
	    /// <param name="startDate"></param>
        /// <param name="filterEndDate">End date to filter received from dashboard control.</param>
	    /// <param name="endDate"></param>
	    /// <returns></returns>
	    [HttpGet]
		[AreaSecurityAttribute(area = Areas.Scenarios, level = AccessLevel.Read)]
        public ActionResult Inactive(Guid? id, DateTime? filterStartDate, DateTime? startDate, DateTime? filterEndDate, DateTime? endDate)
		{
			var dtStart = (filterStartDate ?? startDate) ?? DateTime.Today;
            var dtEnd = (filterEndDate ?? endDate) ?? DateTime.Today;
			string type = Request.QueryString["type"];
			string status = Request.QueryString["status"];
			string classification = Request.QueryString["classification"];
			string group = Request.QueryString["filterGroup"];
			Guid scenarioGroup = Guid.Empty;
			Guid.TryParse(group, out scenarioGroup);

			Guid shown = new Guid(Request.QueryString["shown"].ToString());
			var scenariotype = ((type == "Portfolio") ? ScenarioType.Portfolio : ScenarioType.Scheduling);
			if (id == null || id == Guid.Empty)
				return new HttpStatusCodeResult(HttpStatusCode.BadRequest);

			if (!HtmlHelpers.CheckProjectPermission(null, id.Value, AccessLevel.Read))
				return new HttpStatusCodeResult(HttpStatusCode.Unauthorized);

			List<ForecastDashboardModel> list = new List<ForecastDashboardModel>();
			try
			{
				if (id == Guid.Empty)
					return HttpNotFound();
				else
				{
					IQueryable<Scenario> scenarios = from s in DbContext.Scenarios
													 where s.ParentId == id && s.Type == (int)scenariotype && s.StartDate <= dtEnd
														 && s.EndDate >= dtStart && s.Status == (int?)ScenarioStatus.Inactive
													 select s;
					if (scenarioGroup != Guid.Empty)
					{
						scenarios = scenarios.Where(s => s.Scenario2Group.Any(g => g.GroupId == scenarioGroup));
					}

					if (shown != null)
					{
						scenarios = scenarios.Where(s => s.Id != shown);
					}

					foreach (Scenario s in scenarios)
					{
						var tempscenario = (ScenarioDetailModel)s;
						list.Add(new ForecastDashboardModel()
							{
								Id = s.Id,
								ProjectId = (Guid)s.ParentId,
								ProjectName = s.Project.Name,
								Name = s.Name,
                                Type = (ScenarioType?)s.Type,
								ProjectedRevenue = s.ProjectedRevenue,
								ExpectedGrossMargin = s.ExpectedGrossMargin,
								ExpectedGrossMargin_LM = s.ExpectedGrossMargin_LM,
								CalculatedGrossMargin = s.CalculatedGrossMargin,
								CalculatedGrossMargin_LM = s.CalculatedGrossMargin_LM,
								UseLMMargin = s.UseLMMargin,
								CGSplit = s.CGSplit,
								EFXSplit = s.EFXSplit,
								StartDate = s.StartDate,
								EndDate = s.EndDate,
								Priority = s.Project.Priority,
								Probability = (decimal)s.Project.Probability,
								TDDirectCosts = s.TDDirectCosts,
								TDDirectCosts_LM = s.TDDirectCosts_LM,
								BUDirectCosts = s.BUDirectCosts,
								BUDirectCosts_LM = s.BUDirectCosts_LM,
								ScenarioStatus = (ScenarioStatus?)s.Status,
								Color = s.Color,
								CostSavings = tempscenario.CostSavings,
								CostSavingsDuration = tempscenario.CostSavingsDuration,
								ROIDate = tempscenario.ROIDate,
								HardSoftSavings = tempscenario.HardSoftSavings,
								Groups = DbContext.Scenario2Group.Where(x => x.ScenarioId == s.Id).Select(x => x.GroupId).ToList(),
								InactiveCount = 0
							});
					}
				}
			}
			catch (BLLException blEx)
			{
				if (blEx.DisplayError)
					SetErrorScript(message: blEx.Message);
				else
				{
					LogException(blEx);
					SetErrorScript();
				}
			}
			catch (Exception exception)
			{
				LogException(exception);
				SetErrorScript();
			}
			return Json(list, JsonRequestBehavior.AllowGet);
		}

		/// <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>
        public JsonResult GetGraphData(DateTime? StartDate, DateTime? EndDate, string type, string status, string classification, string additionalParams, bool? isUOMHours, bool isLaborMode, string filterGroup)
		{
			if (StartDate == null) StartDate = new DateTime(DateTime.Today.Year, 1, 1);
			if (EndDate == null) EndDate = new DateTime(DateTime.Today.Year, 12, 31);
		    
			Dictionary<string, object> additionalParamsDict = null;
			if (!string.IsNullOrEmpty(additionalParams))
			{
				var serializer = new System.Web.Script.Serialization.JavaScriptSerializer();
				try
				{
					additionalParamsDict = (Dictionary<string, object>)serializer.DeserializeObject(additionalParams);
				}
				catch (Exception exception)
				{
					LogException(exception);
					SetErrorScript();
				}
			}
	
			List<Scenario> scenarios = new List<Scenario>();
			var principals = GetUserPrincipals();
			var data = new Dictionary<string, List<long[]>>();
			Guid teamId = Guid.Empty;
			Guid viewId = Guid.Empty;
			ForecastDashboardMode mode = ForecastDashboardMode.MainDashboard;

			if (additionalParamsDict != null && additionalParamsDict.ContainsKey("mode") && (string)additionalParamsDict["mode"] == "team" && additionalParamsDict.ContainsKey("teamId"))
			{
				mode = ForecastDashboardMode.TeamForecast;
				Guid.TryParse((string)additionalParamsDict["teamId"], out teamId);
			}
			if (additionalParamsDict != null && additionalParamsDict.ContainsKey("mode") && (string)additionalParamsDict["mode"] == "view" && additionalParamsDict.ContainsKey("viewId"))
			{
				mode = ForecastDashboardMode.ViewForecast;
				Guid.TryParse((string)additionalParamsDict["viewId"], out viewId);
			}

			data = new DashboardCharthManager().GetData(StartDate.Value, EndDate.Value, principals, Guid.Parse(User.Identity.GetUserId()), type, status, classification, mode, teamId, viewId, isUOMHours, isLaborMode, filterGroup);

			var headers = new List<Object>();

			// Get project statuses
			var statuses = DbContext.Status.Where(x => !x.IsSystem).Select(x => x.Name);
			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);
				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 }
						});
					}
				}
			}

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

            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 = "Total Capacity",
				data = data["resourceCapacity"],
				type = "Cost",
				stack = false,
				lines = new { show = true, fill = false }
			});

			headers.Add(new
			{
				label = "Total 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, JsonRequestBehavior.AllowGet);
		}

        /// <summary>
        /// Returns data for pie chart
        /// </summary>
        
        public JsonResult GetClassPieData(DateTime? StartDate, DateTime? EndDate, string additionalParams, bool isLaborMode)
        {
            if (StartDate == null) StartDate = DateTime.Today.AddMonths(-1);
            if (EndDate == null) EndDate = StartDate.Value.AddYears(1);

            Dictionary<string, object> additionalParamsDict = null;
            if (!string.IsNullOrEmpty(additionalParams))
            {
                var serializer = new System.Web.Script.Serialization.JavaScriptSerializer();
                try
                {
                    additionalParamsDict = (Dictionary<string, object>)serializer.DeserializeObject(additionalParams);
                }
                catch (Exception exception)
                {
                    LogException(exception);
                    SetErrorScript();
                }
            }

            List<Scenario> scenarios = new List<Scenario>();
            var principals = GetUserPrincipals();
            Guid teamId = Guid.Empty;
            Guid viewId = Guid.Empty;
            ForecastDashboardMode dashboardMode = ForecastDashboardMode.MainDashboard;

            if (additionalParamsDict != null && additionalParamsDict.ContainsKey("mode") && (string)additionalParamsDict["mode"] == "team" && additionalParamsDict.ContainsKey("teamId"))
            {
                dashboardMode = ForecastDashboardMode.TeamForecast;
                Guid.TryParse((string)additionalParamsDict["teamId"], out teamId);
            }
            if (additionalParamsDict != null && additionalParamsDict.ContainsKey("mode") && (string)additionalParamsDict["mode"] == "view" && additionalParamsDict.ContainsKey("viewId"))
            {
                dashboardMode = ForecastDashboardMode.ViewForecast;
                Guid.TryParse((string)additionalParamsDict["viewId"], out viewId);
            }

            var data = new DashboardCharthManager().GetPieData(StartDate.Value, EndDate.Value, principals, Guid.Parse(User.Identity.GetUserId()), dashboardMode, teamId, viewId, isLaborMode);
            return Json(data, JsonRequestBehavior.AllowGet);
        }

		[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,
            DateTime? filterStartDate,
            DateTime? startDate,
            DateTime? filterEndDate,
            DateTime? endDate,
            bool filterGroupByTeam)
		{
			var StartDate = ((filterStartDate ?? startDate) ?? DateTime.Today).Date;
            var EndDate = ((filterEndDate ?? endDate) ?? DateTime.Today).Date;
			string type = Request.QueryString["type"];
			string status = Request.QueryString["status"];
			string classification = Request.QueryString["classification"];
			string group = Request.QueryString["filterGroup"];
			string additionalParamsString = Request.QueryString["additionalParams"];
			Dictionary<string, object> additionalParamsDict = null;
			if (!string.IsNullOrEmpty(additionalParamsString))
			{
				var serializer = new System.Web.Script.Serialization.JavaScriptSerializer();
				try
				{
					additionalParamsDict = (Dictionary<string, object>)serializer.DeserializeObject(additionalParamsString);
				}
				catch (Exception exception)
				{
					LogException(exception);
					SetErrorScript();
				}
			}

			var principals = GetUserPrincipals();
			var projectAccesses = new ProjectAccessCache().Value.Where(x => principals.Contains(x.PrincipalId)).Select(x => x.ProjectId);

			ForecastDashboardMode mode = ForecastDashboardMode.MainDashboard;
			Guid teamId = Guid.Empty;
			Guid viewId = Guid.Empty;

			if (additionalParamsDict != null && additionalParamsDict.ContainsKey("mode") && (string)additionalParamsDict["mode"] == "team" && additionalParamsDict.ContainsKey("teamId"))
			{
				mode = ForecastDashboardMode.TeamForecast;
				Guid.TryParse((string)additionalParamsDict["teamId"], out teamId);
			}
			if (additionalParamsDict != null && additionalParamsDict.ContainsKey("mode") && (string)additionalParamsDict["mode"] == "view" && additionalParamsDict.ContainsKey("viewId"))
			{
				mode = ForecastDashboardMode.ViewForecast;
				Guid.TryParse((string)additionalParamsDict["viewId"], out viewId);
			}

			IQueryable<Project> projects = DbContext.Projects.Where(x => projectAccesses.Contains(x.Id) && !x.HasChildren).Distinct();
			if (ForecastDashboardMode.TeamForecast == mode)
			{
				projects = projects.Where(p => p.Team2Project.Any(t2p => t2p.TeamId == teamId));
			}
			if (ForecastDashboardMode.ViewForecast == mode)
			{
                projects = projects.Where(p => p.Team2Project.Any(t2p => t2p.Team.Team2View.Any(tv=>tv.ViewId == viewId)));
			}

			if (classification != "All")
			{
				var classificationGuid = new Guid(classification);
				projects = projects.Where(i => i.TypeId == classificationGuid);
			}
			if (status != "All")
			{
				var statusGuid = new Guid(status);
				projects = projects.Where(i => i.StatusId == statusGuid);
			}

			Guid scenarioGroup = Guid.Empty;
			Guid.TryParse(group, out scenarioGroup);
			var query = new List<ForecastDashboardModel>();
            List<Team> teams = new List<Team>(){new Team(){Id=Guid.Empty}};
            if (filterGroupByTeam)
            {
                if(ForecastDashboardMode.TeamForecast == mode)
                    teams = DbContext.Team2Project.AsNoTracking().Where(t2p => t2p.TeamId == teamId).Select(t2p => t2p.Team).Distinct().ToList();
                else if(ForecastDashboardMode.ViewForecast == mode)
                    teams = DbContext.Team2View.AsNoTracking().Where(t2v => t2v.ViewId == viewId).Select(t2p => t2p.Team).Distinct().ToList();
                else
                    teams = DbContext.Team2Project.AsNoTracking().Where(t2p => projects.Select(p => p.Id).Contains(t2p.ProjectId)).Select(t2p => t2p.Team).Distinct().ToList();
            }

            foreach (var team in teams)
            {
                foreach (var project in projects.Where(p => !filterGroupByTeam  || (filterGroupByTeam && p.Team2Project.Select(t2p => t2p.TeamId).Contains(team.Id))))
                {
                    var scenariotype = (ScenarioType.Portfolio.ToString().Equals(type, StringComparison.InvariantCultureIgnoreCase) ? ScenarioType.Portfolio : ScenarioType.Scheduling);
                    Scenario activescenario = null;
                    if (scenarioGroup != Guid.Empty)
                    {
                        activescenario = (from c in project.Scenarios
                                          where c.Type == (int?)scenariotype && c.StartDate <= EndDate && c.EndDate >= StartDate && c.Status == (int?)ScenarioStatus.Active && c.Scenario2Group.Any(g => g.GroupId == scenarioGroup)
                                          select c).FirstOrDefault();

                        if (activescenario == null)
                            activescenario = (from c in project.Scenarios
                                              where c.Type == (int?)scenariotype && c.StartDate <= EndDate && c.EndDate >= StartDate && c.Scenario2Group.Any(g => g.GroupId == scenarioGroup)
                                              select c).FirstOrDefault();
                    }
                    else
                    {
                        activescenario = (from c in project.Scenarios
                                          where c.Type == (int?)scenariotype && c.StartDate <= EndDate && c.EndDate >= StartDate && c.Status == (int?)ScenarioStatus.Active
                                          select c).FirstOrDefault();

                        if (activescenario == null)
                            activescenario = (from c in project.Scenarios
                                              where c.Type == (int?)scenariotype && c.StartDate <= EndDate && c.EndDate >= StartDate
                                              select c).FirstOrDefault();
                    }
                    if (activescenario != null)
                    {
                        var tempscenario = (ScenarioDetailModel)activescenario;
                        query.Add(new ForecastDashboardModel()
                        {
                            Id = activescenario.Id,
                            ProjectId = (Guid)activescenario.ParentId,
                            ProjectName = (project.ParentProject != null ? project.ParentProject.Name + ": " : "") + project.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 = tempscenario.CostSavings,
                            CostSavingsDuration = tempscenario.CostSavingsDuration,
                            ROIDate = tempscenario.ROIDate,
                            HardSoftSavings = tempscenario.HardSoftSavings,
                            Groups = DbContext.Scenario2Group.Where(x => x.ScenarioId == activescenario.Id).Select(x => x.GroupId).ToList(),
                            InactiveCount = (from c in project.Scenarios
                                             where c.Type == (int)scenariotype && c.StartDate <= EndDate
                                                 && c.EndDate >= StartDate && c.Status == (int?)ScenarioStatus.Inactive && c.Id != activescenario.Id
                                             select c).Count()
                        });
                    }
                    foreach (var sortedColumn in sortedColumns)
                    {
                        var order = query.OrderBy(c => c.Id);
                        if (filterGroupByTeam)
                        {
                            order = query.OrderBy(c => c.TeamName);
                        }

                        switch (sortedColumn.PropertyName)
                        {
                            case "Id":
                                if (sortedColumn.Direction == SortingDirection.Ascending)
                                    query = order.ThenBy(c => c.Id).ToList();
                                else
                                    query = order.ThenByDescending(c => c.Id).ToList();
                                break;
                            case "ProjectName":
                                if (sortedColumn.Direction == SortingDirection.Ascending)
                                    query = order.ThenBy(c => c.ProjectName).ToList();
                                else
                                    query = order.ThenByDescending(c => c.ProjectName).ToList();
                                break;
                            case "ProjectedRevenue":
                                if (sortedColumn.Direction == SortingDirection.Ascending)
                                    query = order.ThenBy(c => c.ProjectedRevenue).ToList();
                                else
                                    query = order.ThenByDescending(c => c.ProjectedRevenue).ToList();
                                break;
                            case "ExpectedGrossMargin":
                                if (sortedColumn.Direction == SortingDirection.Ascending)
                                    query = order.ThenBy(c => c.ExpectedGrossMargin).ToList();
                                else
                                    query = order.ThenByDescending(c => c.ExpectedGrossMargin).ToList();
                                break;
                            case "ExpectedGrossMargin_LM":
                                if (sortedColumn.Direction == SortingDirection.Ascending)
                                    query = order.ThenBy(c => c.ExpectedGrossMargin_LM).ToList();
                                else
                                    query = order.ThenByDescending(c => c.ExpectedGrossMargin_LM).ToList();
                                break;
                            case "CalculatedGrossMargin":
                                if (sortedColumn.Direction == SortingDirection.Ascending)
                                    query = order.ThenBy(c => c.CalculatedGrossMargin).ToList();
                                else
                                    query = order.ThenByDescending(c => c.CalculatedGrossMargin).ToList();
                                break;
                            case "CalculatedGrossMargin_LM":
                                if (sortedColumn.Direction == SortingDirection.Ascending)
                                    query = order.ThenBy(c => c.CalculatedGrossMargin_LM).ToList();
                                else
                                    query = order.ThenByDescending(c => c.CalculatedGrossMargin_LM).ToList();
                                break;
                            case "CGSplit":
                                if (sortedColumn.Direction == SortingDirection.Ascending)
                                    query = order.ThenBy(c => c.CGSplit).ToList();
                                else
                                    query = order.ThenByDescending(c => c.CGSplit).ToList();
                                break;
                            case "StartDate":
                                if (sortedColumn.Direction == SortingDirection.Ascending)
                                    query = order.ThenBy(c => c.StartDate).ToList();
                                else
                                    query = order.ThenByDescending(c => c.StartDate).ToList();
                                break;
                            case "EndDate":
                                if (sortedColumn.Direction == SortingDirection.Ascending)
                                    query = order.ThenBy(c => c.EndDate).ToList();
                                else
                                    query = order.ThenByDescending(c => c.EndDate).ToList();
                                break;
                            case "Priority":
                                if (sortedColumn.Direction == SortingDirection.Ascending)
                                    query = order.ThenBy(c => c.Priority).ToList();
                                else
                                    query = order.ThenByDescending(c => c.Priority).ToList();
                                break;
                            case "TDDirectCosts":
                                if (sortedColumn.Direction == SortingDirection.Ascending)
                                    query = order.ThenBy(c => c.TDDirectCosts).ToList();
                                else
                                    query = order.ThenByDescending(c => c.TDDirectCosts).ToList();
                                break;
                            case "TDDirectCosts_LM":
                                if (sortedColumn.Direction == SortingDirection.Ascending)
                                    query = order.ThenBy(c => c.TDDirectCosts_LM).ToList();
                                else
                                    query = order.ThenByDescending(c => c.TDDirectCosts_LM).ToList();
                                break;
                            case "BUDirectCosts":
                                if (sortedColumn.Direction == SortingDirection.Ascending)
                                    query = order.ThenBy(c => c.BUDirectCosts).ToList();
                                else
                                    query = order.ThenByDescending(c => c.BUDirectCosts).ToList();
                                break;
                            case "BUDirectCosts_LM":
                                if (sortedColumn.Direction == SortingDirection.Ascending)
                                    query = order.ThenBy(c => c.BUDirectCosts_LM).ToList();
                                else
                                    query = order.ThenByDescending(c => c.BUDirectCosts_LM).ToList();
                                break;
                            case "CostSavings":
                                if (sortedColumn.Direction == SortingDirection.Ascending)
                                    query = order.ThenBy(c => c.CostSavings).ToList();
                                else
                                    query = order.ThenByDescending(c => c.CostSavings).ToList();
                                break;
                            case "CostSavingsDuration":
                                if (sortedColumn.Direction == SortingDirection.Ascending)
                                    query = order.ThenBy(c => c.CostSavingsDuration).ToList();
                                else
                                    query = order.ThenByDescending(c => c.CostSavingsDuration).ToList();
                                break;
                            case "ROIDate":
                                if (sortedColumn.Direction == SortingDirection.Ascending)
                                    query = order.ThenBy(c => c.ROIDate).ToList();
                                else
                                    query = order.ThenByDescending(c => c.ROIDate).ToList();
                                break;
                            case "HardSoftSavings":
                                if (sortedColumn.Direction == SortingDirection.Ascending)
                                    query = order.ThenBy(c => c.HardSoftSavings).ToList();
                                else
                                    query = order.ThenByDescending(c => c.HardSoftSavings).ToList();
                                break;
                            case "Status":
                                if (sortedColumn.Direction == SortingDirection.Ascending)
                                    query = order.ThenBy(c => c.ScenarioStatus).ToList();
                                else
                                    query = order.ThenByDescending(c => c.ScenarioStatus).ToList();
                                break;
                            default:
                                if (sortedColumn.Direction == SortingDirection.Ascending)
                                    query = order.ThenBy(c => c.Name).ToList();
                                else
                                    query = order.ThenByDescending(c => c.Name).ToList();
                                break;
                        }
                    }
                }
            }

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

			return query.ToList();
		}

		/// <summary>
		/// Returns a list of all principal GUIDs (user himself and his roles) to be used in direct requests to Security and ProjectAccess tables
		/// </summary>
		private Guid[] GetUserPrincipals()
		{
			var userId = User.Identity.GetUserId();
			AspNetUser user = (from c in DbContext.AspNetUsers where c.Id == userId select c).FirstOrDefault();
			var roleids = (from c in user.AspNetRoles select c.Id).ToList();
			roleids.Add(userId);
			var result = new Guid[roleids.Count() + 1];
			for (int i = 0; i < roleids.Count(); i++)
				result[i] = new Guid(roleids[i]);
			result[roleids.Count()] = new Guid(userId);
			return result;
		}

		/// <summary>
		/// Adds Scenario to the given Scenario Group
		/// </summary>
		private void CopyToGroup(Guid scenarioId, Guid groupId)
		{
			var scenario = DbContext.Scenarios.FirstOrDefault(x => x.Id == scenarioId);
			if (scenario != null)
			{
				var group = DbContext.Scenario2Group.Create();
				group.Id = Guid.NewGuid();
				group.ScenarioId = scenario.Id;
				group.GroupId = groupId;
				DbContext.Entry(group).State = EntityState.Added;

				DbContext.SaveChanges();
			}
		}

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

		#endregion

	}
}