Notes

public async Task<IEnumerable<CandidatesRecordModel>> GetCandidatesRecordModelForPayroll(string startDate, string endDate)
{
try
{
List<CandidatesRecordModel> candidatesRecordModel = 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);

if (parsedStartDate != DateTime.MinValue && parsedEndDate != DateTime.MinValue)
{
var billings = await DbContext.Billings
.Where(x => x.ApproverId == AppConstants.One)
.ToListAsync();

var salaries = await DbContext.Salaries
.Where(x => x.ApproverId == AppConstants.One)
.ToListAsync();

var billingsQuery = billings
.Where(x => (string.IsNullOrEmpty(x.EndDate) || (DateTime.TryParseExact(x.EndDate, AppConstants.HelperTDateFormate, CultureInfo.InvariantCulture, DateTimeStyles.None, out DateTime billingEndDate) && billingEndDate >= parsedStartDate))
&& x.StartDate <= parsedEndDate);

var salariesQuery = salaries
.Where(x => (string.IsNullOrEmpty(x.EndDate) || (DateTime.TryParseExact(x.EndDate, AppConstants.HelperTDateFormate, CultureInfo.InvariantCulture, DateTimeStyles.None, out DateTime salaryEndDate) && salaryEndDate >= parsedStartDate))
&& x.EffectiveDate <= parsedEndDate);

var candidates = await DbContext.Candidate.ToListAsync();
var jobs = await DbContext.Job.ToListAsync();
var deductions = await DbContext.Deductions.ToListAsync();
var reimbursements = await DbContext.Reimbursement.ToListAsync();

var billingGroups = billingsQuery.GroupBy(b => new { b.StartId, b.EmployeeId }).ToList();
var salaryGroups = salariesQuery.GroupBy(s => new { s.StartId, s.EmployeeId }).ToList();

foreach (var billingGroup in billingGroups)
{
var salaryGroup = salaryGroups.FirstOrDefault(sg => sg.Key.StartId == billingGroup.Key.StartId && sg.Key.EmployeeId == billingGroup.Key.EmployeeId);

if (salaryGroup != null)
{
foreach (var billing in billingGroup)
{
var matchedSalaries = salaryGroup.Where(s => s.RecId == billing.RecId).ToList();

if (!matchedSalaries.Any())
{
matchedSalaries = salaryGroup.ToList();
}

foreach (var salary in matchedSalaries)
{
var candidate = candidates.FirstOrDefault(c => c.id == billing.EmployeeId);
var job = jobs.FirstOrDefault(j => j.EmployeeId == billing.EmployeeId);


var deduction = deductions.FirstOrDefault(d => d.EmpStartId == billing.StartId);
var reimbursement = reimbursements.FirstOrDefault(r => r.EmployeeStartId == billing.StartId);

candidatesRecordModel.Add(new CandidatesRecordModel()
{
FirstName = candidate?.firstname,
LastName = candidate?.lastname,
Email = candidate?.Email,
CandidateId = candidate?.id,
TypeofConsultant = candidate?.typeofConsultant,
Country = candidate?.country,
EmploymentCategory = salary.EmploymentCategory,
SalaryPer = salary.SalaryPer,
EmpSalary = salary.EmpSalary,
SalaryApproved = salary.ApproverId,
SalaryEndDate = salary.EndDate,
SalaryStartId = salary.StartId,
SalaryJobId = salary.JobId,
SalaryRecId = salary.RecId,
PaymentFrequency = salary.PaymentFrequency,
ADPFileNo = salary.ADPFileNo,
EffectiveDate = Convert.ToDateTime(salary.EffectiveDate),
BillingApproved = billing.ApproverId,
BillingEndDate = billing.EndDate,
BillingStartId = billing.StartId,
BillingJobId = billing.JobId,
BillingRecId = billing.RecId,
CompanyName = billing.CompanyName,
BillRate = billing.BillRate,
BillRatePer = billing.BillRatePer,
Division = billing.Division,
CompanyId = billing.CompanyId,
BankAccountACH = billing.BankAccountACH,
AccountNumberWire = billing.AccountNumberWire,
BankTypeACH = billing.BankTypeACH,
Region = billing.RegionClass,
ClientContact = billing.ClientContact,
JobTitle = job?.JobTitle,
DeductionCode = deduction?.DeductionCode,
DeductionAmount = deduction?.DeductionAmount,
ReimbursementCode = reimbursement?.ReimbursementCode,
ReimbursementAmount = reimbursement?.ReimbursementAmount,
BillingStartDate = billing.StartDate.ToString(AppConstants.HelperDateFormate),
});
}
}
}
}

return candidatesRecordModel.OrderBy(x => x.CompanyName).ToList();
}
return candidatesRecordModel;
}
catch (Exception exception)
{
Log.Error($"Error in GetCandidatesRecordModel: {exception.Message}");
throw;
}
}