using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.IO;
using System.Text;
using NLog;
using FileHelpers;
using System.Data.Entity;
using EnVisage.Code.BLL;
using EnVisage.App_Start;
using EnVisage.Models;

namespace EnVisage.Code
{
    [DelimitedRecord(",")]
    public class ActualsImportRow
    {
        public String ProjectNumber;
        public String WeekEndingDate;
        public String CostCenter;
        public String ExpenditureCategoryName;
        public String Quantity;
        public String Cost;
    }

    public class ImportActuals
    {
		EnVisageEntities _dbContext = new EnVisageEntities();
		private Dictionary<DateTime, FiscalCalendar> moDateColl;
		private	Guid msScenarioOID;
        private Dictionary<Guid, Dictionary<string, ScenarioDetail>> maScenarioDetails = new Dictionary<Guid, Dictionary<string, ScenarioDetail>>(); //level 0 key=scenario.id. level 1 key = ExpenditureCategoryId + WeekEndingDate
		private long mlRecsProcessed;
		private long mlNumRecs;
		private long mlRecsBypassed;
		private byte mbPercentComplete;
        private List<Guid> UpdatedSDs;

        public ImportActualsModel ProcessImport(ActualsImportRow[] ludtInFile, bool firstRowContainsHeaders, bool resetActuals, bool UOMhours, string userName, out string log)
        {
            var model = new ImportActualsModel();
            UpdatedSDs = new List<Guid>();
            model.ResetActuals = resetActuals;
            model.Id = Guid.NewGuid();
            var SDList = new List<ImportActualsScenarioDetails>();
			StringBuilder sbLog = new StringBuilder();
            List<Rate> gRates = new List<Rate>();
            Dictionary<string, ExpenditureCategory> expenditureCategories = new Dictionary<string, ExpenditureCategory>();
            Dictionary<string, CreditDepartment> costCenters = new Dictionary<string, CreditDepartment>();
            Dictionary<Guid, Guid> actualsScenarioIds = new Dictionary<Guid, Guid>();
            Dictionary<Guid, decimal> UOMValues = new Dictionary<Guid, decimal>();
			string lvHoldMissingPrjID = string.Empty;
			Guid lvHoldShowNoActuals = Guid.Empty;
			mlNumRecs = ludtInFile.LongLength;

			if (ludtInFile.Length < 1 || (firstRowContainsHeaders && ludtInFile.Length < 2))
			{
				sbLog.AppendLine("- Import file does not seem to have enough records for import -- ending data import process.");
				log = sbLog.ToString();
                model.Message = sbLog.ToString();
				return model;
			}
            UOMValues = _dbContext.UOMs.ToDictionary(x => x.Id, x => x.UOMValue);
			//sort input array for better performance with current algorithm: ProjectNumber ASC, ExpCategory ASC, WKEndingDate ASC
			sbLog.AppendLine("- Sorting input data for better performance");
			try
			{

				if (firstRowContainsHeaders)
				{
					List<ActualsImportRow> tmpFirstRow = ludtInFile.Take(1).ToList();
					IOrderedEnumerable<ActualsImportRow> sortedColl = ludtInFile.Skip(1).OrderBy(r => r.ProjectNumber).ThenBy(r => r.ExpenditureCategoryName).ThenBy(r => DateTime.Parse(r.WeekEndingDate));
					tmpFirstRow.AddRange(sortedColl);
					ludtInFile = tmpFirstRow.ToArray();
				}
				else
				{
					ludtInFile = ludtInFile.OrderBy(r => r.ProjectNumber).ThenBy(r => r.ExpenditureCategoryName).ThenBy(r => DateTime.Parse(r.WeekEndingDate)).ToArray();
				}
			}
			catch (Exception ex)
			{
				sbLog.AppendLine("- An error occurred while sorting data: " + ex.Message + " -- ending data import process.");
				log = sbLog.ToString();
                model.Message = sbLog.ToString();
				return model;
			}

			sbLog.AppendLine("- Retrieving all AWARDED Projects with non-empty unique Project Numbers from the database.");
            var allProjects = (from p in _dbContext.Projects
                        join parentP in _dbContext.Projects on p.ParentProjectId equals parentP.Id into grProject
                        from parentP in grProject.DefaultIfEmpty()
                        where
                            !p.HasChildren &&
                            ((!p.ParentProjectId.HasValue && !string.IsNullOrEmpty(p.ProjectNumber)) ||
                             (p.ParentProjectId.HasValue && !string.IsNullOrEmpty(parentP.ProjectNumber)))
                        select new
                            {
                                Project = p,
                                ParentProject = parentP
                            }).ToList();
            var projects = allProjects.Select(t => t.ParentProject ?? t.Project).ToList();
            //var projectQuery = _dbContext.Projects.Where(p => p.Status.Name == "AWARDED" && p.ProjectNumber != "");
            //foreach (Project p in projectQuery)
            //{
            //    if (projects.ContainsKey(p.ProjectNumber))
            //    {
            //        sbLog.AppendLine("- There is more than one project with Project Number " + p.ProjectNumber + " in the system -- ending data import process.");
            //        log = sbLog.ToString();
            //        return false;
            //    }
            //    projects.Add(p);
            //}

			if (projects.Count == 0)
			{
				sbLog.AppendLine("- 0 Projects retrieved -- ending data import process.");
				log = sbLog.ToString();
                model.Message = sbLog.ToString();
				return model;
			}
			else
			{
				sbLog.AppendLine("- " + projects.Count() + " Projects retrieved from the database.");
			}

			sbLog.AppendLine("- Retrieving all Expenditure Category data from the database.");
            var _expenditureCategories = _dbContext.ExpenditureCategory.AsNoTracking().ToList();
            var _costCenters = _dbContext.CreditDepartments.AsNoTracking().ToList();
            foreach (var exCat in _expenditureCategories)
                if (!expenditureCategories.Keys.Contains(exCat.Name)) expenditureCategories.Add(exCat.Name, exCat); // SA. ENV-756. ExpName -> ExpCatName

            foreach (var coCen in _costCenters)
                if (!costCenters.Keys.Contains(coCen.CreditNumber)) costCenters.Add(coCen.CreditNumber, coCen);

			if (expenditureCategories.Count() == 0)
            {
                sbLog.AppendLine("- 0 Expenditure Categories retrieved -- ending data import process.");
				log = sbLog.ToString();
                model.Message = sbLog.ToString();
				return model;
			}
            else
            {
				sbLog.AppendLine("- " + expenditureCategories.Count() + " Expenditure Categories retrieved from the database.");
            }

            var lsExpCatsForLRates = expenditureCategories.Values.Select(x=>x.Id).ToArray();
            gRates = lsExpCatsForLRates.Length > 0
                         ? _dbContext.Rates.AsNoTracking().Where(t => t.Type == (short)RateModel.RateType.Global &&
                             lsExpCatsForLRates.Contains(t.ExpenditureCategoryId))
                                    .OrderBy(t => t.ExpenditureCategoryId)
                                    .ThenBy(t => t.EndDate)
                                    .ToList()
                         : _dbContext.Rates.AsNoTracking().Where(t => t.Type == (short)RateModel.RateType.Global)
                                    .OrderBy(t => t.ExpenditureCategoryId)
                                    .ThenBy(t => t.EndDate)
                                    .ToList();


			for (int i = 0; i < ludtInFile.Length; i++ )
			{
				if (i == 0 && firstRowContainsHeaders)
					continue;

				if (!expenditureCategories.ContainsKey(ludtInFile[i].ExpenditureCategoryName))
				{
                    //var Exp = new Expenditure();
                    //Exp.Id = Guid.NewGuid();
                    //Exp.Name = ludtInFile[i].ExpenditureCategoryName;
                    //_dbContext.Expenditures.Add(Exp);
                    //_dbContext.SaveChanges();
                    //var ExpCat = new ExpenditureCategory();
                    //ExpCat.Id = Guid.NewGuid();
                    //ExpCat.ExpenditureId = Exp.Id;
                    //ExpCat.UOMId = null;
					sbLog.AppendLine("- Checking expenditure category existence failed - " + ludtInFile[i].ExpenditureCategoryName + " does not exist in the database -- ending data import process.");
					log = sbLog.ToString();
                    model.Message = sbLog.ToString();
					return model;
				}
			}
			
			moDateColl = new Dictionary<DateTime, FiscalCalendar>();
            //bool firstWrite = true;                 // SA. Commented, because is unused
			Guid bucketScenarioId = Guid.Empty;
			Guid bucketExpenditureCatId = Guid.Empty;
			DateTime bucketWeekEndingDate = DateTime.MinValue;
            //decimal bucketQuantity = 0.0M; // SA. Commented, because is unused
            //decimal bucketCost = 0.0M; // SA. Commented, because is unused
			long arrayPointer = 0;
            Guid? currPrId = null;
			if (firstRowContainsHeaders)
			{
				mlRecsProcessed++;
				mbPercentComplete = Convert.ToByte(((decimal)mlRecsProcessed / (decimal)mlNumRecs) * (decimal)100);
				arrayPointer++;
			}

			sbLog.AppendLine("- Reading records from input data...");
			msScenarioOID = Guid.Empty;
			for (long i = arrayPointer; i < mlNumRecs; i++)
			{
				ActualsImportRow row = ludtInFile[i];
				DateTime rowWeekEndingDate = DateTime.MinValue;
				Guid rowProjectId = Guid.Empty;
                Guid[] rowProjectIds;
				Guid rowScenarioId = Guid.Empty;
				Guid rowExpenditureCatId = Guid.Empty;
				if (!DateTime.TryParse(row.WeekEndingDate, out rowWeekEndingDate))
				{
					sbLog.AppendLine(" - ERROR: Line " + i + " has incorrectly formatted Week Ending Date. The import will be aborted");
					log = sbLog.ToString();
                    model.Message = sbLog.ToString();
					return model;
				}
				if (!CheckWeekForWorkStatus(rowWeekEndingDate)) //!tochange
				{
					sbLog.AppendLine(" - ERROR: Line " + i + " contains data for a non-work week period. The import will be aborted");
					log = sbLog.ToString();
                    model.Message = sbLog.ToString();
					return model;
				}

				mlRecsProcessed++;
				mbPercentComplete = Convert.ToByte(((decimal)mlRecsProcessed / (decimal)mlNumRecs) * (decimal)100);
                var currentProjects = projects.Where(x => x.ProjectNumber == row.ProjectNumber).ToArray();
				if (projects.Select(x=>x.ProjectNumber).ToArray().Contains(row.ProjectNumber))
				{
					rowProjectIds = projects.Where(x=>x.ProjectNumber == row.ProjectNumber).Select(x=>x.Id).Distinct().ToArray();
				}
				else
				{
					if (lvHoldMissingPrjID != row.ProjectNumber)
					{
						lvHoldMissingPrjID = row.ProjectNumber;

						sbLog.AppendLine("- WARNING: Project with Project Number '" + lvHoldMissingPrjID + "' does not have a match in the database.");
                        sbLog.AppendLine("  All subsequent import records with this Project Number will be bypassed.");
						sbLog.AppendLine("  This Project should be defined in Prevu.  If it is defined, ensure the Project Number is correct.");
					}
					mlRecsBypassed++;
					continue;
				}

                foreach (var currProjectId in rowProjectIds)
                {
                    bool first = true;
                    currPrId = currProjectId;
                    var currpr = projects.Where(x => x.Id == currProjectId).FirstOrDefault();
                    if (model.Projects.Where(x => x.ProjectId == currProjectId).Count() == 0)
                    {
                        var IAProject = new ImportActualsProject();
                        IAProject.ProjectId = currProjectId;
                        IAProject.Checked = true;
                        IAProject.ProjectName = currpr.Name;
                        if (currpr.HasChildren)
                        {
                            first = true;
                            var parts = _dbContext.Projects.Where(x => x.ParentProjectId == currProjectId).ToList();
                            foreach (var part in parts)
                            {
                                var currpart = new ImportActualsProjectPart();
                                currpart.PartName = part.Name;
                                currpart.PartId = part.Id;
                                currpart.Checked = true;
                                if (first)
                                {
                                    currpart.Distribution = 100;
                                    first = false;
                                }
                                else currpart.Distribution = 0;
                                var scenario = _dbContext.Scenarios.Where(s => s.ParentId == part.Id && s.Type == (int)ScenarioType.Actuals).FirstOrDefault();
                                if (scenario == null || scenario.Id == Guid.Empty)
                                {
                                    if (lvHoldShowNoActuals != part.Id)
                                    {
                                        lvHoldShowNoActuals = part.Id;
                                        sbLog.AppendLine("- No ACTUALS Scenario data located for Project part with Project Number '" + row.ProjectNumber + "' ...bypassing Project Part...");
                                    }
                                    mlRecsBypassed++;
                                    continue;
                                }
                                currpart.ActualsScenarioId = scenario.Id;
                                actualsScenarioIds.Add(part.Id, scenario.Id);
                                IAProject.ProjectParts.Add(currpart);
                            }
                        }
                        else {
                            var scenario = _dbContext.Scenarios.Where(s => s.ParentId == currProjectId && s.Type == (int)ScenarioType.Actuals).FirstOrDefault();
                            if (scenario == null || scenario.Id == Guid.Empty)
                            {
                                if (lvHoldShowNoActuals != currProjectId)
                                {
                                    lvHoldShowNoActuals = currProjectId;
                                    sbLog.AppendLine("- No ACTUALS Scenario data located for Project with Project Number '" + row.ProjectNumber + "' ...bypassing Project.");
                                }
                                mlRecsBypassed++;
                                continue;
                            }
                            else {
                                IAProject.ActualsScenarioId = scenario.Id;
                                actualsScenarioIds.Add(currProjectId, scenario.Id);
                            }
                        }
                        model.Projects.Add(IAProject);
                    }

                    //if (actualsScenarioIds.ContainsKey(currProjectId))
                    //{
                    //    rowScenarioId = actualsScenarioIds[currProjectId];
                    //}
                    //else
                    //{
                    //    var scenario = _dbContext.Scenarios.Where(s => s.ParentId == currProjectId && s.Type == (int)ScenarioType.Actuals).FirstOrDefault();
                    //    if (scenario == null || scenario.Id == Guid.Empty)
                    //    {
                    //        if (lvHoldShowNoActuals != currProjectId)
                    //        {
                    //            lvHoldShowNoActuals = currProjectId;
                    //            sbLog.AppendLine("- No ACTUALS Scenario data located for Project with Project Number '" + row.ProjectNumber + "' ...bypassing Project.");
                    //        }
                    //        mlRecsBypassed++;
                    //        continue;
                    //    }
                    //    else { 

                    //    }
                    //    rowScenarioId = scenario.Id;
                    //    actualsScenarioIds.Add(currProjectId, scenario.Id);
                    //}
                    if (string.IsNullOrEmpty(row.CostCenter) || !costCenters.ContainsKey(ludtInFile[i].CostCenter))
                    {
                        rowExpenditureCatId = expenditureCategories[row.ExpenditureCategoryName].Id;
                    }
                    else
                    {
                        var currCC = costCenters[row.CostCenter];
                        var EC = currCC.ExpenditureCategory.Where(x => x.Name == ludtInFile[i].ExpenditureCategoryName).FirstOrDefault();
                        if (EC != null)
                            rowExpenditureCatId = EC.Id;
                        else rowExpenditureCatId = expenditureCategories[row.ExpenditureCategoryName].Id;
                    }
                    //uom, quantity and cost calculation
                    decimal currCost = 0.0M;
                    decimal currQuantity = 0.0M;
                    if (!decimal.TryParse(row.Cost, out currCost) & !decimal.TryParse(row.Quantity, out currQuantity))
                    {
                        mlRecsBypassed++;
                        continue;
                    }
                    if (!decimal.TryParse(row.Cost, out currCost))
                    {
                        if (!UOMhours)
                        {
                            currQuantity = currQuantity * UOMValues[_expenditureCategories.Where(x => x.Id == rowExpenditureCatId).FirstOrDefault().UOMId];
                        }
                        var rate = gRates.Where(x => x.ExpenditureCategoryId == expenditureCategories[row.ExpenditureCategoryName].Id && x.StartDate <= rowWeekEndingDate && x.EndDate >= rowWeekEndingDate).FirstOrDefault();
                        if (rate != null)
                            currCost = currQuantity * rate.Rate1; 
                    }
                    else if (!decimal.TryParse(row.Quantity, out currQuantity))
                    {
                        var rate = gRates.Where(x => x.ExpenditureCategoryId == expenditureCategories[row.ExpenditureCategoryName].Id && x.StartDate <= rowWeekEndingDate && x.EndDate >= rowWeekEndingDate).FirstOrDefault();
                        if (rate != null && rate.Rate1 > 0)
                            currQuantity = currCost / rate.Rate1;
                    }
                    else {
                        if (!UOMhours)
                        {
                            currQuantity = currQuantity * UOMValues[_expenditureCategories.Where(x => x.Id == rowExpenditureCatId).FirstOrDefault().UOMId];
                        }
                    }
                    if (currpr.HasChildren) {
                        foreach (var part in model.Projects.Where(x => x.ProjectId == currProjectId).FirstOrDefault().ProjectParts)
                        {
                            StoreScDetailToCash(rowExpenditureCatId, rowWeekEndingDate, part.ActualsScenarioId, part.PartId, currQuantity, currCost, resetActuals, SDList);
                        }
                    }
                    else {
                        StoreScDetailToCash(rowExpenditureCatId, rowWeekEndingDate, actualsScenarioIds[currProjectId], currProjectId, currQuantity, currCost, resetActuals, SDList);
                    }
                    //if (rowScenarioId == bucketScenarioId &&
                    //    rowExpenditureCatId == bucketExpenditureCatId &&
                    //    rowWeekEndingDate == bucketWeekEndingDate)
                    //{
                    //    bucketQuantity += currQuantity;
                    //    bucketCost += currCost;
                    //}
                    //else
                    //{
                    //    if (firstWrite)
                    //        firstWrite = false;
                    //    else
                    //    {
                    //        StoreScDetailToCash(rowExpenditureCatId, bucketWeekEndingDate, bucketScenarioId, currProjectId, bucketQuantity, bucketCost, resetActuals, SDList);
                    //    }

                    //    bucketScenarioId = rowScenarioId;
                    //    bucketExpenditureCatId = rowExpenditureCatId;
                    //    bucketWeekEndingDate = rowWeekEndingDate;
                    //    bucketQuantity = currQuantity;
                    //    bucketCost = currCost;
                    //}
                }
			}

            //if ((mlRecsProcessed - mlRecsBypassed > 0) && bucketScenarioId != Guid.Empty && bucketExpenditureCatId != Guid.Empty && currPrId.HasValue)
            //{
            //    StoreScDetailToCash(bucketExpenditureCatId, bucketWeekEndingDate, bucketScenarioId, currPrId.Value, bucketQuantity, bucketCost, resetActuals, SDList);
            //}

            var SessionId = Guid.NewGuid();
            model.SessionId = SessionId;
            HttpContext.Current.Session[SessionId.ToString()] = SDList;

			sbLog.AppendLine();
			sbLog.AppendLine("- Total import records - " + mlRecsProcessed);
            sbLog.AppendLine("- Total import records bypassed - " + mlRecsBypassed);
            sbLog.AppendLine("- Total import records processed - " + (mlRecsProcessed - mlRecsBypassed));
            sbLog.AppendLine();

			_dbContext.SaveChanges();


			log = sbLog.ToString();
            model.Message = sbLog.ToString();
            model.ImportSuccessful = true;
            foreach (var pr in model.Projects)
            {
                if (!pr.ProjectParts.Any() && !actualsScenarioIds.ContainsKey(pr.ProjectId))
                    continue;
                //if (!pr.ProjectParts.Any()) {pr.ActualsScenarioId = actualsScenarioIds[pr.ProjectId];
                if (SDList.Any(x => x.ParentId == pr.ActualsScenarioId))
                {
                    var startDate = SDList.Where(x => x.ParentId == pr.ActualsScenarioId).OrderBy(x => x.WeekEndingDate).FirstOrDefault().WeekEndingDate;
                    var endDate = SDList.Where(x => x.ParentId == pr.ActualsScenarioId).OrderByDescending(x => x.WeekEndingDate).FirstOrDefault().WeekEndingDate;
                    pr.StartDate = startDate.ToShortDateString();
                    pr.EndDate = endDate.ToShortDateString();
                }
                else {
                    foreach (var part in pr.ProjectParts)
                    {
                        var startDate = SDList.Where(x => x.ParentId == part.ActualsScenarioId).OrderBy(x => x.WeekEndingDate).FirstOrDefault();
                        if (startDate != null) {
                            pr.StartDate = startDate.WeekEndingDate.ToShortDateString();
                            part.StartDate = startDate.WeekEndingDate.ToShortDateString(); }
                        var endDate = SDList.Where(x => x.ParentId == part.ActualsScenarioId).OrderByDescending(x => x.WeekEndingDate).FirstOrDefault();
                        if (endDate != null) {
                            pr.EndDate = endDate.WeekEndingDate.ToShortDateString();
                            part.EndDate = endDate.WeekEndingDate.ToShortDateString(); }
                    }
                }
            }
			return model;
        }

        public bool CommitImport(ImportActualsModel model, string userName, out string log)
        {
            StringBuilder sbLog = new StringBuilder();
            var ScenariosToModify = new List<Guid>();
            ScenariosToModify = model.Projects.Select(x => x.ActualsScenarioId).Distinct().ToList();
            foreach (var proj in model.Projects)
                if (proj.ProjectParts.Count()>0)
                    ScenariosToModify.AddRange(proj.ProjectParts.Select(x => x.ActualsScenarioId).Distinct().ToList());
            //if (model.ResetActuals)
            //    _dbContext.ScenarioDetail.RemoveRange(_dbContext.ScenarioDetail.Where(x => ScenariosToModify.Contains(x.ParentID.Value)).ToArray());

            var SDList = HttpContext.Current.Session[model.SessionId.ToString()] as List<ImportActualsScenarioDetails> ?? new List<ImportActualsScenarioDetails>();
            if (SDList.Count() == 0)
            {
                sbLog.AppendLine("Error loading imported data from session. Please retry importing actuals.");
                log = sbLog.ToString();
                return false;
            }
            var allExpCats = _dbContext.ExpenditureCategory.AsNoTracking().ToDictionary(key => key.Id);
            foreach (var project in model.Projects)
            {
                if (project.Checked)
                {
                    if (project.ProjectParts.Any())// && project.ProjectParts.Where(x => x.Checked == true).Count() > 0)
                    {
                        var parts = project.ProjectParts.Where(x => x.Checked == true).ToList();
                        foreach (var part in parts)
                        {
                            var details = SDList.Where(x => x.ParentId == part.ActualsScenarioId).ToList();
                            var partdetails = details.Where(x =>
                                    x.WeekEndingDate >= DateTime.Parse(part.StartDate) &&
                                    x.WeekEndingDate <= DateTime.Parse(part.EndDate)).ToList();
                            foreach (var pd in partdetails)
                            {
                                bool overlaps = parts.Count(x => DateTime.Parse(x.StartDate) <= pd.WeekEndingDate &&
                                                                 DateTime.Parse(x.EndDate) >= pd.WeekEndingDate) > 1;
                                if (overlaps)
                                    DoScDetailFlushToDB(pd.ExpenditureCategoryId, pd.WeekEndingDate, pd.ParentId,
                                                        pd.Quantity*part.Distribution/100, pd.Cost*part.Distribution/100,
                                                        model.ResetActuals);
                                else
                                    DoScDetailFlushToDB(pd.ExpenditureCategoryId, pd.WeekEndingDate, pd.ParentId,
                                                        pd.Quantity, pd.Cost, model.ResetActuals);
                            }
                            sbLog.AppendLine("- Updated Scenario with Id " +
                                             partdetails.FirstOrDefault().ParentId.ToString() +
                                             " to reflect accurate Start and End Dates.");
                            var actualsScenarioId = part.ActualsScenarioId;
                            Scenario scenario = _dbContext.Scenarios.FirstOrDefault(s => s.Id == actualsScenarioId);
                            if (null != scenario)
                            {
                                var oldActualsDate = _dbContext.ScenarioDetail.Where(sd => sd.ParentID == scenario.Id).Min(sd => sd.WeekEndingDate);
                                var newActualsDate = SDList.Where(sd => sd.ParentId == scenario.Id).Min(sd => sd.WeekEndingDate);
                                if (oldActualsDate.HasValue && oldActualsDate < newActualsDate)
                                    scenario.StartDate = oldActualsDate.Value;
                                else 
                                    scenario.StartDate = newActualsDate;

                                oldActualsDate = _dbContext.ScenarioDetail.Where(sd => sd.ParentID == scenario.Id).Max(sd => sd.WeekEndingDate);
                                newActualsDate = SDList.Where(sd => sd.ParentId == scenario.Id).Max(sd => sd.WeekEndingDate);
                                if (oldActualsDate.HasValue && oldActualsDate > newActualsDate)
                                    scenario.StartDate = oldActualsDate.Value;
                                else
                                    scenario.EndDate = newActualsDate;

                                var allActualRecords = maScenarioDetails[scenario.Id].Select(t => new ScenarioManager.ScenarioDetailsListItem
                                {
                                    DetailCost = t.Value.Cost ?? 0,
                                    CategoryType = allExpCats.ContainsKey(t.Value.ExpenditureCategoryId ?? Guid.Empty)
                                            ? allExpCats[t.Value.ExpenditureCategoryId ?? Guid.Empty].Type.HasValue
                                                    ? (ExpenditureCategoryModel.CategoryTypes)allExpCats[t.Value.ExpenditureCategoryId ?? Guid.Empty].Type.Value
                                                    : ExpenditureCategoryModel.CategoryTypes.Undefined
                                            : ExpenditureCategoryModel.CategoryTypes.Undefined
                                }).ToList();
                                var sm = new ScenarioManager(_dbContext);
                                sm.SetBottomUpCosts(scenario, true, allActualRecords);
                                scenario.LastUpdate = DateTime.Now;
                                _dbContext.SaveChanges();
                            }
                        }
                    }
                    else
                    {
                        //ContentLocker.AddLock("Scenario", scenario.Id.ToString(), userName);
                        var details = SDList.Where(x => x.ParentId == project.ActualsScenarioId).ToList();
                        foreach (var ds in details)
                        {
                            DoScDetailFlushToDB(ds.ExpenditureCategoryId, ds.WeekEndingDate, ds.ParentId, ds.Quantity, ds.Cost, model.ResetActuals);
                        }
                        var scenarioId = project.ActualsScenarioId;
                        Scenario scenario = _dbContext.Scenarios.FirstOrDefault(s => s.Id == scenarioId);
                        if (null != scenario)
                        {
                            var oldActualsDate = _dbContext.ScenarioDetail.Where(sd => sd.ParentID == scenario.Id).Min(sd => sd.WeekEndingDate);
                            var newActualsDate = SDList.Where(sd => sd.ParentId == scenario.Id).Min(sd => sd.WeekEndingDate);
                            //TODO: check that we need to set start date as min end date. Maybe it should be equal to min(end date) - 6 days
                            if (oldActualsDate.HasValue && oldActualsDate < newActualsDate)
                                scenario.StartDate = oldActualsDate.Value;
                            else 
                                scenario.StartDate = newActualsDate;

                            oldActualsDate = _dbContext.ScenarioDetail.Where(sd => sd.ParentID == scenario.Id).Max(sd => sd.WeekEndingDate);
                            newActualsDate = SDList.Where(sd => sd.ParentId == scenario.Id).Max(sd => sd.WeekEndingDate);
                            if (oldActualsDate.HasValue && oldActualsDate > newActualsDate)
                                scenario.EndDate = oldActualsDate.Value;
                            else 
                                scenario.EndDate = newActualsDate;

                            var allActualRecords = maScenarioDetails[scenario.Id].Select(t => new ScenarioManager.ScenarioDetailsListItem
                                    {
                                        DetailCost = t.Value.Cost ?? 0,
                                        CategoryType = allExpCats.ContainsKey(t.Value.ExpenditureCategoryId ?? Guid.Empty)
                                            ? allExpCats[t.Value.ExpenditureCategoryId ?? Guid.Empty].Type.HasValue
                                                    ? (ExpenditureCategoryModel.CategoryTypes)allExpCats[t.Value.ExpenditureCategoryId ?? Guid.Empty].Type.Value
                                                    : ExpenditureCategoryModel.CategoryTypes.Undefined
                                            : ExpenditureCategoryModel.CategoryTypes.Undefined
                                    }).ToList();

                            var sm = new ScenarioManager(_dbContext);
                            sm.SetBottomUpCosts(scenario, true, allActualRecords);
                            scenario.LastUpdate = DateTime.Now;
                            _dbContext.SaveChanges();
                        }
                        //ContentLocker.RemoveLock("Scenario", actualsScenarioIds[projectId].ToString(), userName);
                        sbLog.AppendLine("- Updated Scenario with Id " + project.ActualsScenarioId.ToString() + " to reflect accurate Start and End Dates.");
                    }
                }
            }

            sbLog.AppendLine("Updating scenario headers and actual buttom up direct costs.");

            sbLog.AppendLine();
            sbLog.AppendLine("Data import is complete.");
            log = sbLog.ToString();
            return true;
        }

        private void StoreScDetailToCash(Guid expenditureCategoryId, DateTime weekEndingDate, Guid scenarioId, Guid projectId, decimal quantity, decimal cost, bool resetActuals, List<ImportActualsScenarioDetails> SDList)
        {
            var SDitem = new ImportActualsScenarioDetails();
            var ExistingSD = SDList.Where(x => x.ExpenditureCategoryId == expenditureCategoryId && x.ParentId == scenarioId && x.WeekEndingDate == weekEndingDate).FirstOrDefault();
            if (ExistingSD == null)
            {
                SDitem.Id = Guid.NewGuid();
                SDitem.ParentId = scenarioId;
                SDitem.ProjectId = projectId;
                SDitem.ExpenditureCategoryId = expenditureCategoryId;
                SDitem.WeekEndingDate = weekEndingDate;
                SDitem.Cost = cost;
                SDitem.Quantity = quantity;
                SDList.Add(SDitem);
                UpdatedSDs.Add(SDitem.Id);
                return;
            }
            else {
                ExistingSD.Cost += cost;
                ExistingSD.Quantity += quantity;
                return;
            }
        }

		private void DoScDetailFlushToDB(Guid expenditureCategoryId, DateTime weekEndingDate, Guid scenarioId, decimal quantity, decimal cost, bool resetActuals)
		{
			ScenarioDetail detail = DetailUpdate(expenditureCategoryId, weekEndingDate, scenarioId);
			if (detail == null)
			{
				detail = new ScenarioDetail()
				{
					Id = Guid.NewGuid(),
					ParentID = scenarioId,
					ExpenditureCategoryId = expenditureCategoryId,
					WeekEndingDate = weekEndingDate,
					WeekOrdinal = 0,
					LastUpdate = DateTime.Now,
					Cost = cost,
					Quantity = quantity
				};
                maScenarioDetails[scenarioId].Add(expenditureCategoryId.ToString() + weekEndingDate.ToString(), detail);
				_dbContext.ScenarioDetail.Add(detail);
			}
			else
			{
				if (!detail.Quantity.HasValue)
					detail.Quantity = 0;
				if (!detail.Cost.HasValue)
					detail.Cost = 0;
				
				if (resetActuals)
				{
					detail.Quantity = quantity;
					detail.Cost = cost;
				}
				else
				{
					detail.Quantity += quantity;
					detail.Cost += cost;
				}
				_dbContext.Entry(detail).State = EntityState.Modified;
			}
		}

		private ScenarioDetail DetailUpdate(Guid expenditureCategoryId, DateTime weekEndingDate, Guid scenarioId)
		{
			if(scenarioId == Guid.Empty)
				throw new ArgumentException("scenarioId");

			if(scenarioId != msScenarioOID)
			{
				msScenarioOID = scenarioId;
                if (!maScenarioDetails.ContainsKey(msScenarioOID))
				    maScenarioDetails.Add(msScenarioOID, _dbContext.ScenarioDetail.Where(sd => sd.ParentID == msScenarioOID).OrderBy(sd => sd.ExpenditureCategoryId).ThenBy(sd => sd.WeekEndingDate).
					    ToDictionary(sd => sd.ExpenditureCategoryId.ToString() + sd.WeekEndingDate.ToString()));
			}

            return maScenarioDetails[msScenarioOID].ContainsKey(expenditureCategoryId.ToString() + weekEndingDate.ToString()) ?
                maScenarioDetails[msScenarioOID][expenditureCategoryId.ToString() + weekEndingDate.ToString()] : null;
		}

        private bool CheckWeekForWorkStatus(DateTime psWeekEndDt)
        {
			if (moDateColl.Count == 0)
			{
				moDateColl = (from c in _dbContext.FiscalCalendars where c.Type == 0 && c.NonWorking == 1 select c).ToDictionary(c => c.EndDate);
			}

			return !moDateColl.ContainsKey(psWeekEndDt);
        }
    }
}