Notes

public async Task<CostosMasterModel> GetCostosDetailsList(string startDate, string endDate)
{
try
{
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 = CreateCostosExcelModel(item, employeeTimesheets, empForms);
costosExcelModelList.Add(costosExcelModel);
}
}
}
}
}
}
costosMasterModel.CostosModelList = costosExcelModelList;
return costosMasterModel;
}
catch (Exception exception)
{
Log.Error($"Error in GetCostosDetailsList: {exception.Message}");
throw;
}
}

/// <summary>
/// Create Costos Excel Model
/// </summary>
/// <param name="item"></param>
/// <param name="employeeTimesheets"></param>
/// <returns></returns>
private CostosExcelModel CreateCostosExcelModel(CandidatesRecordModel item, List<EmployeeTimesheet> employeeTimesheets, List<DivisionCode> empForms)
{
try
{
CostosExcelModel costosExcelModel = new();

costosExcelModel.Region = item.Region;
costosExcelModel.LastName = item.LastName;
costosExcelModel.FirstName = item.FirstName;
costosExcelModel.FullName = $"{item.FirstName} {item.LastName}";
costosExcelModel.Email = item.Email;
costosExcelModel.Company = item.CompanyName;
costosExcelModel.Position = item.JobTitle;
var billingStartDate = DateTime.Parse(item.BillingStartDate);
costosExcelModel.StartDate = billingStartDate.ToString(AppConstants.DateFormatMDY);
if (item.Division != null)
{
var empForm = empForms.Where(d => d.DivisionCodes == item.Division).Select(s => s.DivisionName).FirstOrDefault();
if (empForm != null)
{
costosExcelModel.Status = empForm;
}
}
if (item.SalaryPer != null && item.EmpSalary != null)
{
decimal payRate = Convert.ToDecimal(objectCalculation.GetCostosPayRate(item.SalaryPer, item.EmpSalary));
if (payRate != 0 && payRate != 1)
{
costosExcelModel.PayRate = $"${payRate.ToString(AppConstants.DefaultFiveDigits)}";
}
}
if (item.BillRatePer != null && item.BillRate != null)
{
decimal billRates = Convert.ToDecimal(objectCalculation.GetCostosBillRate(item.BillRatePer, item.BillRate));
if (billRates != 0)
{
costosExcelModel.BillRate = $"${billRates.ToString(AppConstants.DefaultFiveDigits)}";
costosExcelModel.getBillRate = billRates.ToString();
}
}
var employeeTimesheet = employeeTimesheets.Where(x => x.StartId == item.BillingStartId && x.BillRate == costosExcelModel.getBillRate).ToList();
decimal actualHours = employeeTimesheet.Sum(x => Convert.ToDecimal(x.RegHours) + Convert.ToDecimal(x.OtHours));

if (item.SalaryPer == "H")
{
costosExcelModel.ActualHours = actualHours.ToString("0.00");
decimal pay160hras = actualHours * Convert.ToDecimal(costosExcelModel.PayRate?.Replace(@"$", string.Empty));
costosExcelModel.Pay160hras = $"${pay160hras.ToString(AppConstants.DefaultTwoDigits)}";

decimal bill160hras = actualHours * Convert.ToDecimal(costosExcelModel.BillRate?.Replace(@"$", string.Empty));
costosExcelModel.Bill160Hras = $"${bill160hras.ToString(AppConstants.DefaultTwoDigits)}";
}
else
{
costosExcelModel.ActualHours = "160";
if (item.SalaryPer != null && item.EmpSalary != null)
{
if (item.Division == AppConstants.ECSalaryDivisionCode || item.Division == AppConstants.ECAdminDivisionCode)
{
var salaryEmp = objectCalculation.GetExternalConsultingSalary(item.SalaryPer, item.EmpSalary);
costosExcelModel.Pay160hras = $"${salaryEmp}";
}
else
{
var pay160hras = objectCalculation.GetCostosSalary(item.SalaryPer, item.EmpSalary);
if (pay160hras != AppConstants.DefaultTwoDigits)
{
costosExcelModel.Pay160hras = $"${pay160hras}";
}
}
}

if (item.BillRatePer != null && item.BillRate != null)
{
if (item.Division == AppConstants.ECSalaryDivisionCode || item.Division == AppConstants.ECAdminDivisionCode)
{
var salaryEmp = objectCalculation.GetExternalConsultingBilling(item.BillRatePer, item.BillRate);
costosExcelModel.Bill160Hras = $"${salaryEmp}";
}
else
{
var billRate160Horas = objectCalculation.GetCostosBill160Hora(item.BillRatePer, item.BillRate);
if (billRate160Horas != AppConstants.Zeros)
{
costosExcelModel.Bill160Hras = $"${billRate160Horas}";
}
}
}
}

string impuestos175 = string.Empty;
string impuestos250 = string.Empty;
string impuestos8 = string.Empty;

if (!String.IsNullOrEmpty(item.Division) && !String.IsNullOrEmpty(item.SalaryPer) && !String.IsNullOrEmpty(item.EmpSalary))
{
impuestos175 = objectCalculation.GetImpuestos17PercentForCost(item.Division, item.SalaryPer, item.EmpSalary, costosExcelModel.ActualHours);
impuestos250 = objectCalculation.GetImpuestos2PercentForCost(item.Division, item.SalaryPer, item.EmpSalary, costosExcelModel.ActualHours);
impuestos8 = objectCalculation.GetImpuestos8PercentForCost(item.Division, item.SalaryPer, item.EmpSalary, costosExcelModel.ActualHours);
}

if (item.Division != null)
{
switch (item.Division)
{
case AppConstants.W2HoursDivisionCode:
costosExcelModel.Impuestos175 = $"${impuestos175}";
break;
case AppConstants.W2SalaryDivisionCode:
costosExcelModel.Impuestos175 = $"${impuestos175}";
break;
case AppConstants.USA1099DivisionCode:
costosExcelModel.Impuestos250 = $"${impuestos250}";
break;
case AppConstants.PR480DivisionCode:
costosExcelModel.Impuestos250 = $"${impuestos250}";
break;
case AppConstants.ECHoursDivisionCode:
costosExcelModel.Impuestos8 = $"${impuestos8}";
break;
case AppConstants.ECAdminDivisionCode:
costosExcelModel.Impuestos8 = $"${impuestos8}";
break;
case AppConstants.ECSalaryDivisionCode:
costosExcelModel.Impuestos8 = $"${impuestos8}";
break;
default:
break;
}
}

var costosCode = item.CostosCode;
var costosAmount = item.CostosAmount;
if (costosCode != null && costosAmount != null)
{
switch (costosCode)
{
case AppConstants.One:
costosExcelModel.Auto = $"${costosAmount}";
break;
case AppConstants.Twos:
costosExcelModel.Apt = $"${costosAmount}";
break;
case AppConstants.Three:
costosExcelModel.Visa = $"${costosAmount}";
break;
case AppConstants.Four:
costosExcelModel.TicketOther = $"${costosAmount}";
break;
case AppConstants.Five:
costosExcelModel.Laptop = $"${costosAmount}";
break;
case AppConstants.Six:
costosExcelModel.Insurance = $"${costosAmount}";
break;
case AppConstants.Seven:
costosExcelModel.Comm3rdParty = $"${costosAmount}";
break;
default:
break;
}
}

decimal Pay_160hras = Convert.ToDecimal(costosExcelModel.Pay160hras?.Replace(@"$", string.Empty));
decimal Impuestos_175 = Convert.ToDecimal(costosExcelModel.Impuestos175?.Replace(@"$", string.Empty));
decimal Impuestos_250 = Convert.ToDecimal(costosExcelModel.Impuestos250?.Replace(@"$", string.Empty));
decimal Impuestos_8 = Convert.ToDecimal(costosExcelModel.Impuestos8?.Replace(@"$", string.Empty));
decimal Auto_Doller = Convert.ToDecimal(costosExcelModel.Auto?.Replace(@"$", string.Empty));
decimal Apt_Doller = Convert.ToDecimal(costosExcelModel.Apt?.Replace(@"$", string.Empty));
decimal Visa_Doller = Convert.ToDecimal(costosExcelModel.Visa?.Replace(@"$", string.Empty));
decimal Ticket_Other = Convert.ToDecimal(costosExcelModel.TicketOther?.Replace(@"$", string.Empty));
decimal Laptop_Doller = Convert.ToDecimal(costosExcelModel.Laptop?.Replace(@"$", string.Empty));
decimal Insurance_Doller = Convert.ToDecimal(costosExcelModel.Insurance?.Replace(@"$", string.Empty));
decimal Comm_3rdParty = Convert.ToDecimal(costosExcelModel.Comm3rdParty?.Replace(@"$", string.Empty));
var totalDeCost = Pay_160hras + Impuestos_175 + Impuestos_250 + Impuestos_8 + Auto_Doller + Apt_Doller + Visa_Doller + Ticket_Other + Laptop_Doller + Insurance_Doller + Comm_3rdParty;
if (totalDeCost != 0)
{
costosExcelModel.TotalDeCosto = $"${totalDeCost.ToString(AppConstants.DefaultTwoDigits)}";
}

decimal Total_Cost = Convert.ToDecimal(costosExcelModel.TotalDeCosto?.Replace(@"$", string.Empty));
decimal costHoras = 0;

if (item.SalaryPer != "H")
{
costHoras = Total_Cost / 160;
costosExcelModel.CostoHora = $"${costHoras.ToString(AppConstants.DefaultTwoDigits)}";
}
else
{
if (actualHours != 0)
{
costHoras = Total_Cost / actualHours;
costosExcelModel.CostoHora = $"${costHoras.ToString(AppConstants.DefaultTwoDigits)}";
}
}

decimal profit = Convert.ToDecimal(costosExcelModel.Bill160Hras?.Replace(@"$", string.Empty)) - Total_Cost;
if (profit != 0)
{
costosExcelModel.Profit = $"${profit.ToString(AppConstants.DefaultOneDigits)}";
}

decimal billRate = Convert.ToDecimal(costosExcelModel.BillRate?.Replace(@"$", string.Empty));
if (billRate != 0)
{
decimal relacion7030 = (costHoras / billRate) * 100;
costosExcelModel.Relacion7030 = $"{relacion7030.ToString(AppConstants.DefaultOneDigits)}%";
}

if (billRate != 0 && costHoras != 0)
{
var coefficient = ((billRate - costHoras) * 5) / 100;
if (coefficient != 0)
{
costosExcelModel.Coeficiente = $"{coefficient.ToString(AppConstants.DefaultOneDigits)}%";
}
}
return costosExcelModel;
}
catch (Exception exception)
{
Log.Error($"Error in CreateCostosExcelModel: {exception.Message}");
throw;
}
}