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 billingsQuery = DbContext.Billings
.Where(x => (x.ApproverId == "1") && (x.EndDate == "" || x.EndDate == null || x.EndDate.CompareTo(parsedEndDate.ToString("yyyy-MM-dd")) >= 0)
&& (x.StartDate <= parsedStartDate));

var salariesQuery = DbContext.Salaries
.Where(x => (x.ApproverId == "1") && (x.EndDate == "" || x.EndDate == null || x.EndDate.CompareTo(parsedEndDate.ToString("dd-MM-yyyy")) >= 0)
&& (x.EffectiveDate <= parsedStartDate));

return await (
from billing in billingsQuery
join salary in salariesQuery on new { billing.StartId } equals new { 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 = Convert.ToDateTime(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 = Convert.ToString(billing.StartDate),
}).OrderBy(x => x.CompanyName).ToListAsync();
}
return candidatesRecordModel;
}
catch (Exception exception)
{
Log.Error($"Error in GetCandidatesRecordModel: {exception.Message}");
throw;
}
}


public async Task<DataEntryExcelMasterModel> GetDataEntryEmployeesLists(string startDate, string endDate)
{
try
{
var currentDate = DateTime.Now;

DataEntryExcelMasterModel dataEntryExcelMasterModel = new DataEntryExcelMasterModel();
List<DataEntryExcelModel> dataEntryExcelModelList = new List<DataEntryExcelModel>();

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 deductions = await helperRepository.GetDeductionsAsync(parsedStartDate, parsedEndDate);
var reimbursements = await helperRepository.GetReimbursementsAsync(parsedStartDate, parsedEndDate);
var employeeTimesheets = await helperRepository.GetApprovedEmployeeTimesheetsAsync(parsedStartDate, parsedEndDate);
var empForms = await helperRepository.GetDivisionCodesAsync();

var externalConsultingAdminHelper = await helperRepository.GetExternalConsultingAdminHelperAsync();
var externalConsultingSalaryHelper = await helperRepository.GetExternalConsultingSalaryHelperAsync();
var externalConsultingHoursHelper = await helperRepository.GetExternalConsultingHoursHelperAsync();
var w2SalaryHelperAsync = await helperRepository.GetW2SalaryHelperAsync();
var w2HoursHelperAsync = await helperRepository.GetW2HoursHelperAsync();
var usa1099SalaryHelperAsync = await helperRepository.Get1099USASalaryHelperAsync();
var usa1099HoursHelperAsync = await helperRepository.Get1099USAHoursHelperAsync();
var pr480PayrollHelperAsync = await helperRepository.Get480PRPayrollHelperAsync();

var dataEntry = await helperRepository.GetCandidatesRecordModelForMasterData(startDate, endDate);
var getRegularHoursDate = helperRepository.GetRegularHoursDate(startDate, endDate);
foreach (var item in dataEntry.DistinctBy(x => new { x.CandidateId, x.BillingStartId, x.BillingRecId }))
{
if (string.IsNullOrEmpty(item.SalaryEndDate) || item.SalaryEndDate.CompareTo(parsedEndDate.ToString("dd-MM-yyyy")) >= 0)
{
if (item.EffectiveDate <= parsedStartDate)
{
if (item.SalaryApproved == AppConstants.One)
{
DataEntryExcelModel dataEntryExcelModel = CreateDataEntryExcelModel(item, startDate, endDate, parsedStartDate, parsedEndDate, deductions, reimbursements,
employeeTimesheets, empForms, getRegularHoursDate, externalConsultingAdminHelper, externalConsultingSalaryHelper, externalConsultingHoursHelper, w2SalaryHelperAsync,
w2HoursHelperAsync, usa1099SalaryHelperAsync, usa1099HoursHelperAsync, pr480PayrollHelperAsync);
dataEntryExcelModelList.Add(dataEntryExcelModel);
}
}
}
}
dataEntryExcelMasterModel.DataEntryExcelModelList = dataEntryExcelModelList;
return dataEntryExcelMasterModel;
}
catch (Exception ex)
{
Log.Error($"Error in GetDataEntryEmployeesLists: {ex.Message}");
throw;
}
}

here my logic every this runnng fine and I want data on the conditions i.e. if StartDate and EffectiveDate is less than or equal to parsedStartDate and EndDate is Null or Empty or greater than equal to parsedEndDate and ApproverId is 1
please modify this code because this code missing some data.