Notes

public async Task<IEnumerable<CandidatesRecordModel>> GetCandidatesRecordModelForMasterData(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 == "1" &&
(string.IsNullOrEmpty(x.EndDate) ||
DateTime.ParseExact(x.EndDate, "yyyy-MM-ddTHH:mm:ss", CultureInfo.InvariantCulture) >= parsedStartDate) &&
x.StartDate <= parsedEndDate)
.ToListAsync();

var salaries = await DbContext.Salaries
.Where(x => x.ApproverId == "1" &&
(string.IsNullOrEmpty(x.EndDate) ||
DateTime.ParseExact(x.EndDate, "dd-MM-yyyy HH:mm:ss", CultureInfo.InvariantCulture) >= parsedStartDate) &&
x.EffectiveDate <= parsedEndDate)
.ToListAsync();

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

// Group by StartId and EmployeeId to ensure correct matching
var billingGroups = billings.GroupBy(b => new { b.StartId, b.EmployeeId }).ToList();
var salaryGroups = salaries.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)
{
foreach (var salary in salaryGroup)
{
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 = 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("yyyy-MM-dd HH:mm:ss"),
});
}
}
}
}

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