Notes

public async Task<ProjectionsExcelMasterModels> GetProjectionDetailsList(string startDate, string endDate)
{
ProjectionsExcelMasterModels projectionsExcelMasterModels = new();
try
{
List<ProjectionsFooterModel> projectionsFooterModelList = 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 costosEmployeeDetails = await helperRepository.GetCandidatesRecordModelForCosts(startDate, endDate);
var companyCodesAsync = await helperRepository.GetCompanyCodesAsync();
var projectionIncomeExpensesHelperAsync = await GetProjectionIncomeExpensesHelperAsync(parsedStartDate, parsedEndDate);

var empForms = await helperRepository.GetDivisionCodesAsync();

if (parsedStartDate != DateTime.MinValue && parsedEndDate != DateTime.MinValue)
{
var isMasterProjectionClosed = await IsMasterProjectionClosed(parsedStartDate, parsedEndDate);
if (isMasterProjectionClosed == false)
{
foreach (var item in costosEmployeeDetails.DistinctBy(x => new { x.CandidateId, x.SalaryStartId, x.SalaryRecId, x.EmpSalary }))
{
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)
{
if (item.Division != "485791" && item.Division != "469942" && item.Division != "468692" && item.Division != "492341" && item.Division != "509597")
{
if (item.CompanyId == "5508753")
{
var ecsSalaryDivisionConsultants = costosEmployeeDetails.Where(x => x.CompanyId == "5508753" && x.Division == AppConstants.ECSalaryDivisionCode && x.CandidateId == item.CandidateId).ToList();
var nonEcsSalaryDivisionConsultants = costosEmployeeDetails.Where(x => x.CompanyId == "5508753" && x.Division != AppConstants.ECSalaryDivisionCode && x.CandidateId == item.CandidateId).ToList();

if (ecsSalaryDivisionConsultants.Any())
{
var costosExcelModelECSalary = CreateProjectionExcelModelSalaryDivision(item, parsedStartDate, parsedEndDate, empForms, companyCodesAsync, ecsSalaryDivisionConsultants);
costosExcelModelList.Add(costosExcelModelECSalary);
}

if (nonEcsSalaryDivisionConsultants.Any())
{
var costosExcelModelNonECSalary = CreateProjectionExcelModelNonSalaryDivision(item, parsedStartDate, parsedEndDate, empForms, companyCodesAsync, nonEcsSalaryDivisionConsultants);
costosExcelModelList.Add(costosExcelModelNonECSalary);
}
}
else
{
CostosExcelModel costosExcelModel = CreateProjectionExcelModel(item, parsedStartDate, parsedEndDate, empForms, companyCodesAsync);
costosExcelModelList.Add(costosExcelModel);
}
}
}
}
}
}
}
}

costosMasterModel.CostosModelList = costosExcelModelList;
var projectionData = costosMasterModel;
var distinctCostos = projectionData.CostosModelList.DistinctBy(x => new { x.Company, x.Region }).OrderBy(x => x.Region).ThenBy(x => x.Company).ToList();

foreach (var regionGroup in distinctCostos.GroupBy(x => x.Region))
{
ProjectionsFooterModel projectionsFooterModel = new ProjectionsFooterModel
{
Region = regionGroup.Key,
ProjectionsModelList = new List<ProjectionsExcelModel>()
};
string? firstPercent = string.Empty;
foreach (var item in regionGroup)
{
ProjectionsExcelModel projectionsExcelModel = new ProjectionsExcelModel
{
Class = item.Region,
Client = item.Company,
NumberOfConsultants = projectionData.CostosModelList.Where(x => x.Region == item.Region && x.Company == item.Company).Count().ToString(),
W2 = projectionData.CostosModelList.Where(x => x.Region == item.Region && x.Company == item.Company && (x.Status == AppConstants.W2HoursDivisionCode || x.Status == AppConstants.W2SalaryDivisionCode)).Count().ToString(),
External = projectionData.CostosModelList.Where(x => x.Region == item.Region && x.Company == item.Company && (x.Status == AppConstants.ECAdminDivisionCode || x.Status == AppConstants.ECHoursDivisionCode || x.Status == AppConstants.ECSalaryDivisionCode)).Count().ToString(),
Ten99 = projectionData.CostosModelList.Where(x => x.Region == item.Region && x.Company == item.Company && (x.Status == AppConstants.USA1099DivisionCode || x.Status == AppConstants.PR480DivisionCode)).Count().ToString(),
};

var consultatntList = projectionData.CostosModelList.Where(x => x.Region == item.Region && x.Company == item.Company).ToList();

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

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

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

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

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

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

firstPercent = ((Convert.ToDecimal(consultatntList.Count()) / Convert.ToDecimal(projectionData.CostosModelList.Count())) * 100).ToString(AppConstants.DefaultTwoDigits);
projectionsExcelModel.FirstPercent = $"{firstPercent}%";

projectionsFooterModel.ProjectionsModelList.Add(projectionsExcelModel);
}

// Calculate footer for each region
var regionCompanies = projectionData.CostosModelList.Where(x => x.Region == regionGroup.Key).ToList();

projectionsFooterModel.NumberOfConsultants = regionCompanies.Count();
projectionsFooterModel.W2 = regionCompanies.Where(x => x.Status == AppConstants.W2HoursDivisionCode || x.Status == AppConstants.W2SalaryDivisionCode).Count();
projectionsFooterModel.External = regionCompanies.Where(x => x.Status == AppConstants.ECAdminDivisionCode || x.Status == AppConstants.ECHoursDivisionCode || x.Status == AppConstants.ECSalaryDivisionCode).Count();
projectionsFooterModel.Ten99 = regionCompanies.Where(x => x.Status == AppConstants.USA1099DivisionCode || x.Status == AppConstants.PR480DivisionCode).Count();

decimal totalFirstPercent = (Convert.ToDecimal(regionCompanies.Count()) / Convert.ToDecimal(projectionData.CostosModelList.Count())) * 100;
projectionsFooterModel.TotalFirstPercent = $"{totalFirstPercent.ToString(AppConstants.DefaultTwoDigits)}%";

projectionsFooterModel.Pay160Hras = "$" + regionCompanies.Sum(x => Convert.ToDecimal(x.Pay160hras?.Replace("$", ""))).ToString(AppConstants.DefaultSixDigits);
projectionsFooterModel.Bill160Hras = "$" + regionCompanies.Sum(x => Convert.ToDecimal(x.Bill160Hras?.Replace("$", ""))).ToString(AppConstants.DefaultSixDigits);
projectionsFooterModel.TotalCost = "$" + regionCompanies.Sum(x => Convert.ToDecimal(x.TotalDeCosto?.Replace("$", ""))).ToString(AppConstants.DefaultSixDigits);

decimal totalBill160HorasRegion = regionCompanies.Sum(x => Convert.ToDecimal(x.Bill160Hras?.Replace("$", "")));
decimal totalCostRegion = regionCompanies.Sum(x => Convert.ToDecimal(x.TotalDeCosto?.Replace("$", "")));

projectionsFooterModel.BillTotalCost = "$" + (totalBill160HorasRegion - totalCostRegion).ToString(AppConstants.DefaultSixDigits);

if (totalBill160HorasRegion != 0)
{
decimal secondPercent = (totalCostRegion / totalBill160HorasRegion) * 100;
projectionsFooterModel.SecondPercent = $"{secondPercent.ToString(AppConstants.DefaultTwoDigits)}%";
}

projectionsFooterModel.MarkUp = (totalBill160HorasRegion / totalCostRegion).ToString(AppConstants.DefaultTwoDigits);

projectionsFooterModel.TotalW2Calculation = projectionsFooterModel.W2 * 4 * 40;
projectionsFooterModel.TotalTen99Calculation = projectionsFooterModel.Ten99 * 4 * 40;
projectionsFooterModel.TotalExternalCalculation = projectionsFooterModel.External * 4 * 40;
projectionsFooterModel.TotalConsultantsCalculation = projectionsFooterModel.NumberOfConsultants * 4 * 40;

projectionsFooterModelList.Add(projectionsFooterModel);
}

projectionsExcelMasterModels.ProjectionsFooterModelList = projectionsFooterModelList;

// Compute main footer totals
projectionsExcelMasterModels.TotalNumberOfConsultants = projectionsFooterModelList.Sum(x => x.NumberOfConsultants);
projectionsExcelMasterModels.TotalW2 = projectionsFooterModelList.Sum(x => x.W2);
projectionsExcelMasterModels.TotalExternal = projectionsFooterModelList.Sum(x => x.External);
projectionsExcelMasterModels.TotalTen99 = projectionsFooterModelList.Sum(x => x.Ten99);
projectionsExcelMasterModels.TotalPay160Hras = "$" + projectionsFooterModelList.Sum(x => Convert.ToDecimal(x.Pay160Hras?.Replace("$", ""))).ToString(AppConstants.DefaultSixDigits);
projectionsExcelMasterModels.TotalBill160Hras = "$" + projectionsFooterModelList.Sum(x => Convert.ToDecimal(x.Bill160Hras?.Replace("$", ""))).ToString(AppConstants.DefaultSixDigits);
projectionsExcelMasterModels.TotalTotalCost = "$" + projectionsFooterModelList.Sum(x => Convert.ToDecimal(x.TotalCost?.Replace("$", ""))).ToString(AppConstants.DefaultSixDigits);
projectionsExcelMasterModels.TotalBillTotalCost = "$" + projectionsFooterModelList.Sum(x => Convert.ToDecimal(x.BillTotalCost?.Replace("$", ""))).ToString(AppConstants.DefaultSixDigits);
projectionsExcelMasterModels.TotalSecondPercent = $"{(projectionsFooterModelList.Sum(x => Convert.ToDecimal(x.SecondPercent?.Replace("%", ""))) / projectionsFooterModelList.Count).ToString(AppConstants.DefaultTwoDigits)}%";
projectionsExcelMasterModels.TotalMarkUp = $"{(projectionsFooterModelList.Sum(x => Convert.ToDecimal(x.MarkUp)) / projectionsFooterModelList.Count).ToString(AppConstants.DefaultTwoDigits)}";

projectionsExcelMasterModels.GrandTotalW2Calculation = projectionsFooterModelList.Sum(x => x.TotalW2Calculation);
projectionsExcelMasterModels.GrandTotalTen99Calculation = projectionsFooterModelList.Sum(x => x.TotalTen99Calculation);
projectionsExcelMasterModels.GrandTotalExternalCalculation = projectionsFooterModelList.Sum(x => x.TotalExternalCalculation);
projectionsExcelMasterModels.GrandTotalConsultantsCalculation = projectionsFooterModelList.Sum(x => x.TotalConsultantsCalculation);

decimal grandTotalPay160HrasCalculation = Convert.ToDecimal(projectionsExcelMasterModels.TotalPay160Hras?.Replace("$", "")) / Convert.ToDecimal(projectionsExcelMasterModels.GrandTotalConsultantsCalculation);
projectionsExcelMasterModels.GrandTotalPay160HrasCalculation = $"${grandTotalPay160HrasCalculation.ToString(AppConstants.DefaultTwoDigits)}";

decimal grandTotalBill160HrasCalculation = Convert.ToDecimal(projectionsExcelMasterModels.TotalBill160Hras?.Replace("$", "")) / Convert.ToDecimal(projectionsExcelMasterModels.GrandTotalConsultantsCalculation);
projectionsExcelMasterModels.GrandTotalBill160HrasCalculation = $"${grandTotalBill160HrasCalculation.ToString(AppConstants.DefaultTwoDigits)}";

decimal grandTotalCostCalculation = Convert.ToDecimal(projectionsExcelMasterModels.TotalTotalCost?.Replace("$", "")) / Convert.ToDecimal(projectionsExcelMasterModels.GrandTotalConsultantsCalculation);
projectionsExcelMasterModels.GrandTotalCostCalculation = $"${grandTotalCostCalculation.ToString(AppConstants.DefaultTwoDigits)}";