Notes

public IEnumerable<IReceivableInfo> GetReceivableInfo(IContractBilling e, ContractBillingQueryParam.GetReceivableInfoParams p)
{
const decimal zeroAmount = 0.0M;

var receivableIds = p.ACHSchedules.Select(x => x.ReceivableId);

var receivableTaxes = from receivableTax in Repository.ReceivableTaxes
where receivableTax.IsActive
group new { Amount = receivableTax.Amount.Amount, Balance = receivableTax.Balance.Amount }
by receivableTax.ReceivableId into receivableTaxesRS
select new
{
ReceivableId = receivableTaxesRS.Key,
Amount = receivableTaxesRS.Sum(x => x.Amount),
Balance = receivableTaxesRS.Sum(x => x.Balance)
};

var receivableWithHoldingTaxDetails = from whtReceivableId in Repository.ConvertToBigIntTable(receivableIds, "whtReceivableId")
join whtReceivableDetails in Repository.ReceivableWithholdingTaxDetails
on whtReceivableId.Id equals whtReceivableDetails.ReceivableId
select new
{
ReceivableId = whtReceivableDetails.ReceivableId,
TaxAmount = whtReceivableDetails.Tax.Amount,
EffectiveBalance = whtReceivableDetails.EffectiveBalance.Amount,
};

var leasePaymentScheduleDetails = from receivable in Repository.Receivables
join contract in Repository.Contracts on receivable.EntityId equals contract.Id
join leasePaymentSchedule in Repository.LeasePaymentSchedules on receivable.PaymentScheduleId equals leasePaymentSchedule.Id
where receivable.EntityType.Value == ReceivableEntityTypeValues.CT.Value
&& contract.ContractType.Value == ContractTypeValues.Lease.Value
select new
{
ReceivableId = receivable.Id,
PaymentNumber = (int?)leasePaymentSchedule.PaymentNumber
};

var loanPaymentScheduleDetails = from receivable in Repository.Receivables
join contract in Repository.Contracts on receivable.EntityId equals contract.Id
join loanPaymentSchedule in Repository.LoanPaymentSchedules on receivable.PaymentScheduleId equals loanPaymentSchedule.Id
where receivable.EntityType.Value == ReceivableEntityTypeValues.CT.Value
&& contract.ContractType.Value != ContractTypeValues.Lease.Value
select new
{
ReceivableId = receivable.Id,
PaymentNumber = (int?)loanPaymentSchedule.PaymentNumber
};

var recurringSundryPaymentScheduleDetails = from receivable in Repository.Receivables
join recurringSundryPaymentSchedule in Repository.SundryRecurringPaymentSchedules
on receivable.PaymentScheduleId equals recurringSundryPaymentSchedule.Id
where receivable.EntityType.Value == ReceivableEntityTypeValues.CT.Value
select new
{
ReceivableId = receivable.Id,
PaymentNumber = (int?)recurringSundryPaymentSchedule.Number
};

var receivableInfo = from receivable in Repository.Receivables
join receivableCode in Repository.ReceivableCodes on receivable.ReceivableCodeId equals receivableCode.Id
join receivableType in Repository.ReceivableTypes on receivableCode.ReceivableTypeId equals receivableType.Id
join receivableId in Repository.ConvertToBigIntTable(receivableIds, "receivableId") on receivable.Id equals receivableId.Id
join receivableTax in receivableTaxes on receivable.Id equals receivableTax.ReceivableId into receivableTaxesRS
from receivableTax in receivableTaxesRS.DefaultIfEmpty()
join leasePaymentSchedule in leasePaymentScheduleDetails on receivable.Id equals leasePaymentSchedule.ReceivableId
into leasePaymentScheduleDetailsRS
from leasePaymentSchedule in leasePaymentScheduleDetailsRS.DefaultIfEmpty()
join loanPaymentSchedule in loanPaymentScheduleDetails on receivable.Id equals loanPaymentSchedule.ReceivableId
into loanPaymentScheduleDetailsRS
from loanPaymentSchedule in loanPaymentScheduleDetailsRS.DefaultIfEmpty()
join sundryPaymentSchedule in recurringSundryPaymentScheduleDetails
on receivable.Id equals sundryPaymentSchedule.ReceivableId into recurringSundryPaymentScheduleDetailsRS
from sundryPaymentSchedule in recurringSundryPaymentScheduleDetailsRS.DefaultIfEmpty()
join whtReceivable in receivableWithHoldingTaxDetails on receivable.Id equals whtReceivable.ReceivableId
into whtReceivableRS from whtReceivable in whtReceivableRS.DefaultIfEmpty()
select new ReceivableInfo
{
ReceivableId = receivable.Id,
CustomerId = receivable.CustomerId,
ReceivableTypeId = receivableType.Id,
ReceivableType = receivableType.Name,
DueDate = receivable.DueDate,
ReceivableTotalAmount = receivable.TotalAmount.Amount,
ReceivableBalanceAmount = receivable.TotalBalance.Amount,
TaxDue = receivableTax != null ? receivableTax.Amount : default(decimal),
TaxBalance = receivableTax != null ? receivableTax.Balance : default(decimal),
PaymentScheduleNumber = leasePaymentSchedule.PaymentNumber ?? loanPaymentSchedule.PaymentNumber ?? null,
IsWithHoldingTaxAssessed = whtReceivable.ReceivableId != null,
WithHoldingTaxAmountDue = whtReceivable.ReceivableId != null ? whtReceivable.TaxAmount : zeroAmount,
WithHoldingTaxBalance = whtReceivable.ReceivableId != null ? whtReceivable.EffectiveBalance: zeroAmount
};

return p.ACHSchedules.MapTo(receivableInfo, source => source.ReceivableId, target => target.ReceivableId);
}