Notes

public async Task<ProjectionsExcelMasterModel> GetProjectionDetailsList(string startDate, string endDate)
{
try
{
ProjectionsExcelMasterModel projectionsExcelMasterModel = new();
List<ProjectionsExcelModel> projectionsExcelModelList = new();
CostosMasterModel costosMasterModel = new();
List<CostosExcelModel> costosExcelModelList = new();

DateTime.TryParseExact(startDate, AppConstants.DateFormat, CultureInfo.InvariantCulture, DateTimeStyles.None, out DateTime parsedStartDate);
DateTime.TryParseExact(endDate, AppConstants.DateFormat, CultureInfo.InvariantCulture, DateTimeStyles.None, out DateTime parsedEndDate);

var employeeTimesheets = await helperRepository.GetApprovedEmployeeTimesheetsAsync(parsedStartDate, parsedEndDate);
var costosEmployeeDetails = await helperRepository.GetCandidatesRecordModelForCosts(startDate, endDate);
var empForms = await helperRepository.GetDivisionCodesAsync();

if (parsedStartDate != DateTime.MinValue && parsedEndDate != DateTime.MinValue)
{
foreach (var item in costosEmployeeDetails.DistinctBy(x => new { x.CandidateId, x.BillingStartId, x.BillingRecId }))
{
if (string.IsNullOrEmpty(item.SalaryEndDate) || (DateTime.TryParseExact(item.SalaryEndDate, AppConstants.SalaryEndDateFormat, CultureInfo.InvariantCulture, DateTimeStyles.None, out DateTime salaryEndDate) && salaryEndDate >= parsedStartDate))
{
if (item.EffectiveDate <= parsedEndDate)
{
if (item.SalaryApproved == AppConstants.One)
{
if (item.CompanyId != InvoiceConstants.SMX && item.CompanyId != InvoiceConstants.SMXServicesConsultingInc && item.CompanyId != InvoiceConstants.SMXUSA && item.CompanyId != InvoiceConstants.SMXUSAFL && item.CompanyId != InvoiceConstants.InfolasticSMXSalesforce)
{
CostosExcelModel costosExcelModel = CreateProjectionExcelModel(item, employeeTimesheets, empForms);
costosExcelModelList.Add(costosExcelModel);
}
}
}
}
}
}

costosMasterModel.CostosModelList = costosExcelModelList;
var groupedCostos = costosMasterModel.CostosModelList.GroupBy(x => x.Region)
.Select(regionGroup => new
{
Region = regionGroup.Key,
Companies = regionGroup.GroupBy(x => x.Company)
});

foreach (var regionGroup in groupedCostos)
{
foreach (var companyGroup in regionGroup.Companies)
{
ProjectionsExcelModel projectionsExcelModel = new ProjectionsExcelModel();
projectionsExcelModel.Class = regionGroup.Region;
projectionsExcelModel.Client = companyGroup.Key;
projectionsExcelModel.NumberOfConsultants = companyGroup.DistinctBy(x => x.StartId).Count().ToString();
projectionsExcelModel.W2 = companyGroup.Where(x => x.Status == AppConstants.W2).DistinctBy(x => x.StartId).Count().ToString();
projectionsExcelModel.External = companyGroup.Where(x => x.Status == AppConstants.W8ExtConsAdmn || x.Status == AppConstants.W8ExtConsHrs || x.Status == AppConstants.W8ExtConsSalary).DistinctBy(x => x.StartId).Count().ToString();
projectionsExcelModel.Ten99 = companyGroup.Where(x => x.Status == AppConstants.USA1099Payroll || x.Status == AppConstants.PR480Payroll).DistinctBy(x => x.StartId).Count().ToString();

var consultantList = companyGroup.DistinctBy(x => x.StartId).ToList();

decimal totalPay160Horas = consultantList.Sum(x => Convert.ToDecimal(x.Pay160hras?.Replace("$", "")));
projectionsExcelModel.Pay160Hras = "$ " + totalPay160Horas.ToString(AppConstants.DefaultSixDigits);

decimal totalBill160Horas = consultantList.Sum(x => Convert.ToDecimal(x.Bill160Hras?.Replace("$", "")));
projectionsExcelModel.Bill160Hras = "$ " + totalBill160Horas.ToString(AppConstants.DefaultSixDigits);

decimal totalCost = consultantList.Sum(x => Convert.ToDecimal(x.TotalDeCosto?.Replace("$", "")));
projectionsExcelModel.TotalCost = "$ " + totalCost.ToString(AppConstants.DefaultSixDigits);

decimal billTotalCost = totalBill160Horas - totalCost;
projectionsExcelModel.BillTotalCost = "$ " + billTotalCost.ToString(AppConstants.DefaultSixDigits);

if (totalBill160Horas != 0)
{
decimal secondPercent = (totalCost / totalBill160Horas) * 100;
projectionsExcelModel.SecondPercent = $"{secondPercent.ToString(AppConstants.DefaultTwoDigits)}%";
}

decimal markUp = totalBill160Horas / totalCost;
projectionsExcelModel.MarkUp = markUp.ToString(AppConstants.DefaultTwoDigits);

projectionsExcelModelList.Add(projectionsExcelModel);
}
}

projectionsExcelMasterModel.ProjectionsExcelModelList = projectionsExcelModelList;
return projectionsExcelMasterModel;
}
catch (Exception exception)
{
Log.Error($"Error in GetProjectionDetailsList: {exception.Message}");
throw;
}
}