Notes

public async Task<ProjectionsExcelMasterModels> GetProjectionDetailsList(string startDate, string endDate)
{
try
{
ProjectionsExcelMasterModels projectionsExcelMasterModels = new ProjectionsExcelMasterModels();
List<ProjectionsFooterModel> projectionsFooterModelList = new List<ProjectionsFooterModel>();
CostosMasterModel costosMasterModel = new CostosMasterModel();
List<CostosExcelModel> costosExcelModelList = new List<CostosExcelModel>();

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();

foreach (var regionGroup in distinctCostos.GroupBy(x => x.Region))
{
ProjectionsFooterModel projectionsFooterModel = new ProjectionsFooterModel
{
Region = regionGroup.Key,
ProjectionsModelList = new List<ProjectionsExcelModel>()
};

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).DistinctBy(x => x.StartId).Count().ToString(),
W2 = projectionData.CostosModelList.Where(x => x.Region == item.Region && x.Company == item.Company && (x.Status == AppConstants.W2)).DistinctBy(x => x.StartId).Count().ToString(),
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(),
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));

projectionsFooterModel.ProjectionsModelList.Add(projectionsExcelModel);
}

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

projectionsFooterModel.NumberOfConsultants = regionCompanies.DistinctBy(x => x.StartId).Count();
projectionsFooterModel.W2 = regionCompanies.Where(x => x.Status == AppConstants.W2).DistinctBy(x => x.StartId).Count();
projectionsFooterModel.External = regionCompanies.Where(x => x.Status == AppConstants.W8ExtConsAdmn || x.Status == AppConstants.W8ExtConsHrs || x.Status == AppConstants.W8ExtConsSalary).DistinctBy(x => x.StartId).Count();
projectionsFooterModel.Ten99 = regionCompanies.Where(x => x.Status == AppConstants.USA1099Payroll || x.Status == AppConstants.PR480Payroll).DistinctBy(x => x.StartId).Count();
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);

projectionsFooterModelList.Add(projectionsFooterModel);
}

projectionsExcelMasterModels.ProjectionsFooterModelList = projectionsFooterModelList;
return projectionsExcelMasterModels;
}
catch (Exception exception)
{
Log.Error($"Error in GetProjectionDetailsList: {exception.Message}");
throw;
}
}


@model ProjectionsExcelMasterModels

<table>
<thead>
<tr>
<th>Region</th>
<th>Company</th>
<th>Number of Consultants</th>
<th>W2</th>
<th>External</th>
<th>1099</th>
<th>Pay 160 Hours</th>
<th>Bill 160 Hours</th>
<th>Total Cost</th>
<th>Bill - Total Cost</th>
<th>Second %</th>
<th>Mark Up</th>
</tr>
</thead>
<tbody>
@foreach (var footerModel in Model.ProjectionsFooterModelList)
{
foreach (var projection in footerModel.ProjectionsModelList)
{
<tr>
<td>@projection.Class</td>
<td>@projection.Client</td>
<td>@projection.NumberOfConsultants</td>
<td>@projection.W2</td>
<td>@projection.External</td>
<td>@projection.Ten99</td>
<td>@projection.Pay160Hras</td>
<td>@projection.Bill160Hras</td>
<td>@projection.TotalCost</td>
<td>@projection.BillTotalCost</td>
<td>@projection.SecondPercent</td>
<td>@projection.MarkUp</td>
</tr>
}

<tr class="footer-row">
<td colspan="2">@footerModel.Region Total</td>
<td>@footerModel.NumberOfConsultants</td>
<td>@footerModel.W2</td>
<td>@footerModel.External</td>
<td>@footerModel.Ten99</td>
<td>@footerModel.Pay160Hras</td>
<td>@footerModel.Bill160Hras</td>
<td>@footerModel.TotalCost</td>
<td>@footerModel.BillTotalCost</td>
<td>@footerModel.SecondPercent</td>
<td>@footerModel.MarkUp</td>
</tr>
}
</tbody>
</table>