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 projectionData = costosMasterModel;
var distinctCostos = projectionData.CostosModelList.DistinctBy(x => new { x.Company, x.Region }).OrderBy(x => x.Region).ThenBy(x => x.Company).ToList();

// Temporary list to store results with footers
List<ProjectionsExcelModel> tempProjectionsExcelModelList = new List<ProjectionsExcelModel>();

foreach (var regionGroup in distinctCostos.GroupBy(x => x.Region))
{
foreach (var item in regionGroup)
{
ProjectionsExcelModel projectionsExcelModel = new ProjectionsExcelModel();
projectionsExcelModel.Class = item.Region;
projectionsExcelModel.Client = item.Company;
projectionsExcelModel.NumberOfConsultants = projectionData.CostosModelList.Where(x => x.Region == item.Region && x.Company == item.Company).DistinctBy(x => x.StartId).Count().ToString();
projectionsExcelModel.W2 = projectionData.CostosModelList.Where(x => x.Region == item.Region && x.Company == item.Company && (x.Status == AppConstants.W2)).DistinctBy(x => x.StartId).Count().ToString();
projectionsExcelModel.External = projectionData.CostosModelList.Where(x => x.Region == item.Region && x.Company == item.Company && (x.Status == AppConstants.W8ExtConsAdmn || x.Status == AppConstants.W8ExtConsHrs || x.Status == AppConstants.W8ExtConsSalary)).DistinctBy(x => x.StartId).Count().ToString();
projectionsExcelModel.Ten99 = projectionData.CostosModelList.Where(x => x.Region == item.Region && x.Company == item.Company && (x.Status == AppConstants.USA1099Payroll || x.Status == AppConstants.PR480Payroll)).DistinctBy(x => x.StartId).Count().ToString();

var consultatntList = projectionData.CostosModelList.Where(x => x.Region == item.Region && x.Company == item.Company).DistinctBy(x => x.StartId).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));

tempProjectionsExcelModelList.Add(projectionsExcelModel);
}

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

ProjectionsExcelModel footerModel = new ProjectionsExcelModel
{
Class = regionGroup.Key + " Total",
NumberOfConsultants = regionCompanies.DistinctBy(x => x.StartId).Count().ToString(),
W2 = regionCompanies.Where(x => x.Status == AppConstants.W2).DistinctBy(x => x.StartId).Count().ToString(),
External = regionCompanies.Where(x => x.Status == AppConstants.W8ExtConsAdmn || x.Status == AppConstants.W8ExtConsHrs || x.Status == AppConstants.W8ExtConsSalary).DistinctBy(x => x.StartId).Count().ToString(),
Ten99 = regionCompanies.Where(x => x.Status == AppConstants.USA1099Payroll || x.Status == AppConstants.PR480Payroll).DistinctBy(x => x.StartId).Count().ToString(),
Pay160Hras = "$ " + regionCompanies.Sum(x => Convert.ToDecimal(x.Pay160hras?.Replace("$", ""))).ToString(AppConstants.DefaultSixDigits),
Bill160Hras = "$ " + regionCompanies.Sum(x => Convert.ToDecimal(x.Bill160Hras?.Replace("$", ""))).ToString(AppConstants.DefaultSixDigits),
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("$", "")));

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

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

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

tempProjectionsExcelModelList.Add(footerModel);
}

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