337 lines
12 KiB
C#
337 lines
12 KiB
C#
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;
|
|
|
|
namespace EnVisage.Code
|
|
{
|
|
[DelimitedRecord(",")]
|
|
public class ActualsImportRow
|
|
{
|
|
public String ProjectNumber;
|
|
public String WeekEndingDate;
|
|
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<string, ScenarioDetail> maScenarioDetails;//key = ExpenditureCategoryId + WeekEndingDate
|
|
private long mlRecsProcessed;
|
|
private long mlNumRecs;
|
|
private long mlRecsBypassed;
|
|
private byte mbPercentComplete;
|
|
|
|
public bool ProcessImport(ActualsImportRow[] ludtInFile, bool firstRowContainsHeaders, bool resetActuals, string userName, out string log)
|
|
{
|
|
StringBuilder sbLog = new StringBuilder();
|
|
Dictionary<string, Project> projects = new Dictionary<string,Project>();
|
|
Dictionary<string, ExpenditureCategory> expenditureCategories = new Dictionary<string, ExpenditureCategory>(); ;
|
|
Dictionary<Guid, Guid> actualsScenarioIds = new Dictionary<Guid, Guid>();
|
|
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();
|
|
return false;
|
|
}
|
|
|
|
//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();
|
|
return false;
|
|
}
|
|
|
|
sbLog.AppendLine("- Retrieving all AWARDED Projects with non-empty unique Project Numbers from the database.");
|
|
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.ProjectNumber, p);
|
|
}
|
|
|
|
if (projects.Count == 0)
|
|
{
|
|
sbLog.AppendLine("- 0 Projects retrieved -- ending data import process.");
|
|
log = sbLog.ToString();
|
|
return false;
|
|
}
|
|
else
|
|
{
|
|
sbLog.AppendLine("- " + projects.Count() + " Projects retrieved from the database.");
|
|
}
|
|
|
|
sbLog.AppendLine("- Retrieving all Expenditure Category data from the database.");
|
|
var _expenditureCategories = _dbContext.ExpenditureCategory.ToList();
|
|
foreach (var exCat in _expenditureCategories)
|
|
if (!expenditureCategories.Keys.Contains(exCat.Expenditure.Name)) expenditureCategories.Add(exCat.Expenditure.Name, exCat);
|
|
if (expenditureCategories.Count() == 0)
|
|
{
|
|
sbLog.AppendLine("- 0 Expenditure Categories retrieved -- ending data import process.");
|
|
log = sbLog.ToString();
|
|
return false;
|
|
}
|
|
else
|
|
{
|
|
sbLog.AppendLine("- " + expenditureCategories.Count() + " Expenditure Categories retrieved from the database.");
|
|
}
|
|
|
|
for (int i = 0; i < ludtInFile.Length; i++ )
|
|
{
|
|
if (i == 0 && firstRowContainsHeaders)
|
|
continue;
|
|
|
|
if (!expenditureCategories.ContainsKey(ludtInFile[i].ExpenditureCategoryName))
|
|
{
|
|
sbLog.AppendLine("- Checking expenditure category existence failed - " + ludtInFile[i].ExpenditureCategoryName + " does not exist in the database -- ending data import process.");
|
|
log = sbLog.ToString();
|
|
return false;
|
|
}
|
|
}
|
|
|
|
moDateColl = new Dictionary<DateTime, FiscalCalendar>();
|
|
bool firstWrite = true;
|
|
Guid bucketScenarioId = Guid.Empty;
|
|
Guid bucketExpenditureCatId = Guid.Empty;
|
|
DateTime bucketWeekEndingDate = DateTime.MinValue;
|
|
decimal bucketQuantity = 0.0M;
|
|
decimal bucketCost = 0.0M;
|
|
long arrayPointer = 0;
|
|
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 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();
|
|
return false;
|
|
}
|
|
if (!CheckWeekForWorkStatus(rowWeekEndingDate))
|
|
{
|
|
sbLog.AppendLine(" - ERROR: Line " + i + " contains data for a non-work week period. The import will be aborted");
|
|
log = sbLog.ToString();
|
|
return false;
|
|
}
|
|
|
|
mlRecsProcessed++;
|
|
mbPercentComplete = Convert.ToByte(((decimal)mlRecsProcessed / (decimal)mlNumRecs) * (decimal)100);
|
|
|
|
if (projects.ContainsKey(row.ProjectNumber))
|
|
{
|
|
rowProjectId = projects[row.ProjectNumber].Id;
|
|
}
|
|
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 PlanIT. If it is defined, ensure the Project Number is correct.");
|
|
}
|
|
mlRecsBypassed++;
|
|
continue;
|
|
}
|
|
|
|
if (actualsScenarioIds.ContainsKey(rowProjectId))
|
|
{
|
|
rowScenarioId = actualsScenarioIds[rowProjectId];
|
|
}
|
|
else
|
|
{
|
|
var scenario = _dbContext.Scenarios.Where(s => s.ParentId == rowProjectId && s.Type == (int)ScenarioType.Actuals).FirstOrDefault();
|
|
if (scenario == null || scenario.Id == Guid.Empty)
|
|
{
|
|
if (lvHoldShowNoActuals != rowProjectId)
|
|
{
|
|
lvHoldShowNoActuals = rowProjectId;
|
|
sbLog.AppendLine("- No ACTUALS Scenario data located for Project with Project Number '" + row.ProjectNumber + "' ...bypassing Project.");
|
|
}
|
|
mlRecsBypassed++;
|
|
continue;
|
|
}
|
|
rowScenarioId = scenario.Id;
|
|
actualsScenarioIds.Add(rowProjectId, scenario.Id);
|
|
ContentLocker.AddLock("Scenario", scenario.Id.ToString(), userName);
|
|
}
|
|
|
|
rowExpenditureCatId = expenditureCategories[row.ExpenditureCategoryName].Id;
|
|
|
|
if (rowScenarioId == bucketScenarioId &&
|
|
rowExpenditureCatId == bucketExpenditureCatId &&
|
|
rowWeekEndingDate == bucketWeekEndingDate)
|
|
{
|
|
bucketQuantity += Decimal.Parse(row.Quantity);
|
|
bucketCost += Decimal.Parse(row.Cost);
|
|
}
|
|
else
|
|
{
|
|
if (firstWrite)
|
|
firstWrite = false;
|
|
else
|
|
{
|
|
DoScDetailFlushToDB(bucketExpenditureCatId, bucketWeekEndingDate, bucketScenarioId, bucketQuantity, bucketCost, resetActuals);
|
|
}
|
|
|
|
bucketScenarioId = rowScenarioId;
|
|
bucketExpenditureCatId = rowExpenditureCatId;
|
|
bucketWeekEndingDate = rowWeekEndingDate;
|
|
bucketQuantity = Decimal.Parse(row.Quantity);
|
|
bucketCost = Decimal.Parse(row.Cost);
|
|
}
|
|
}
|
|
|
|
if ((mlRecsProcessed - mlRecsBypassed > 0) && bucketScenarioId != Guid.Empty && bucketExpenditureCatId != Guid.Empty)
|
|
{
|
|
DoScDetailFlushToDB(bucketExpenditureCatId, bucketWeekEndingDate, bucketScenarioId, bucketQuantity, bucketCost, resetActuals);
|
|
}
|
|
|
|
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();
|
|
|
|
sbLog.AppendLine("Updating scenario headers and actual buttom up direct costs.");
|
|
foreach (Guid projectId in actualsScenarioIds.Keys)
|
|
{
|
|
Guid scenarioId = actualsScenarioIds[projectId];
|
|
Scenario scenario = _dbContext.Scenarios.Where(s => s.Id == scenarioId).FirstOrDefault();
|
|
scenario.StartDate = _dbContext.ScenarioDetail.Where(sd => sd.ParentID == scenario.Id).Min(sd => sd.WeekEndingDate).Value;
|
|
scenario.EndDate = _dbContext.ScenarioDetail.Where(sd => sd.ParentID == scenario.Id).Max(sd => sd.WeekEndingDate).Value;
|
|
ScenarioManager sm = new ScenarioManager(_dbContext);
|
|
sm.SetBottomUpCosts(scenario, true);
|
|
scenario.LastUpdate = DateTime.Now;
|
|
_dbContext.SaveChanges();
|
|
ContentLocker.RemoveLock("Scenario", actualsScenarioIds[projectId].ToString(), userName);
|
|
sbLog.AppendLine("- Updated Scenario with Id " + actualsScenarioIds[projectId] + " to reflect accurate Start and End Dates.");
|
|
}
|
|
|
|
sbLog.AppendLine();
|
|
sbLog.AppendLine("Data import is complete.");
|
|
|
|
log = sbLog.ToString();
|
|
return true;
|
|
}
|
|
|
|
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
|
|
};
|
|
_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;
|
|
maScenarioDetails = _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.ContainsKey(expenditureCategoryId.ToString() + weekEndingDate.ToString()) ?
|
|
maScenarioDetails[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);
|
|
}
|
|
}
|
|
} |