Notes

public async Task<IEnumerable<CandidatesRecordModel>> GetCandidatesRecordModelForMasterData(string startDate, string endDate)
{
try
{
List<CandidatesRecordModel> candidatesRecordModel = new();
DateTime.TryParseExact(startDate, "yyyy-MM-dd", CultureInfo.InvariantCulture, DateTimeStyles.None, out DateTime parsedStartDate);
DateTime.TryParseExact(endDate, "yyyy-MM-dd", CultureInfo.InvariantCulture, DateTimeStyles.None, out DateTime parsedEndDate);

if (parsedStartDate != DateTime.MinValue && parsedEndDate != DateTime.MinValue)
{
var billingsQuery = DbContext.Billings
.Where(x => x.ApproverId == "1" &&
(x.EndDate == null || x.EndDate >= parsedEndDate) &&
x.StartDate <= parsedStartDate);

var salariesQuery = DbContext.Salaries
.Where(x => x.ApproverId == "1" &&
(x.EndDate == null || x.EndDate >= parsedEndDate) &&
x.EffectiveDate <= parsedStartDate);

return await (
from billing in billingsQuery
join salary in salariesQuery on billing.StartId equals salary.StartId into bs
from billingSalary in bs.DefaultIfEmpty()
join candidate in DbContext.Candidate on billingSalary.EmployeeId equals candidate.id into cb
from candidateBilling in cb.DefaultIfEmpty()
join job in DbContext.Job on billingSalary.EmployeeId equals job.EmployeeId into jb
from candidateJob in jb.DefaultIfEmpty()
join deduction in DbContext.Deductions on billingSalary.StartId equals deduction.EmpStartId into djb
from deductionBilling in djb.DefaultIfEmpty()
join reimbursement in DbContext.Reimbursement on billingSalary.StartId equals reimbursement.EmployeeStartId into rjb
from reimbursementBilling in rjb.DefaultIfEmpty()
select new CandidatesRecordModel()
{
FirstName = candidateBilling.firstname,
LastName = candidateBilling.lastname,
Email = candidateBilling.Email,
CandidateId = candidateBilling.id,
TypeofConsultant = candidateBilling.typeofConsultant,
Country = candidateBilling.country,
EmploymentCategory = billingSalary.EmploymentCategory,
SalaryPer = billingSalary.SalaryPer,
EmpSalary = billingSalary.EmpSalary,
SalaryApproved = billingSalary.ApproverId,
SalaryEndDate = billingSalary.EndDate,
SalaryStartId = billingSalary.StartId,
SalaryJobId = billingSalary.JobId,
SalaryRecId = billingSalary.RecId,
PaymentFrequency = billingSalary.PaymentFrequency,
ADPFileNo = billingSalary.ADPFileNo,
EffectiveDate = billingSalary.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 = candidateJob.JobTitle,
DeductionCode = deductionBilling.DeductionCode,
DeductionAmount = deductionBilling.DeductionAmount,
ReimbursementCode = reimbursementBilling.ReimbursementCode,
ReimbursementAmount = reimbursementBilling.ReimbursementAmount,
BillingStartDate = billing.StartDate.ToString(),
}).OrderBy(x => x.CompanyName).ToListAsync();
}
return candidatesRecordModel;
}
catch (Exception exception)
{
Log.Error($"Error in GetCandidatesRecordModel: {exception.Message}");
throw;
}
}