public Task <IEnumerable <Billing> > GetItemsForScheduledPaymentImportAsync(ScheduledPaymentImport schedulePayment,
IEnumerable <ImporterSettingDetail> importerSettingDetails, CancellationToken token = default(CancellationToken))
{
var query = new StringBuilder(@"
SELECT b.*, cg.ParentCustomerId
FROM Billing AS b
LEFT JOIN CustomerGroup AS cg ON cg.ChildCustomerId = b.CustomerId
WHERE b.CompanyId = @CompanyId");
CreateSchedulePaymentCondition(schedulePayment, importerSettingDetails, query);
return(dbHelper.GetItemsAsync <Billing>(query.ToString(), schedulePayment, token));
}
public Task <IEnumerable <Billing> > GetDataForScheduledPaymentAsync(ScheduledPaymentImport scheduledPayment,
IEnumerable <ImporterSettingDetail> details, CancellationToken token = default(CancellationToken))
{
var query = new StringBuilder(@"
SELECT b.*, cg.ParentCustomerId
FROM Billing AS b
LEFT JOIN CustomerGroup AS cg ON cg.ChildCustomerId = b.CustomerId
WHERE b.CompanyId = @CompanyId
AND b.DeleteAt IS NULL");
CreateSchedulePaymentCondition(scheduledPayment, details, query);
var order = CreateSchedulePaymentOrder(details);
if (order != "")
{
query.Append($@"
ORDER BY {order}");
}
return(dbHelper.GetItemsAsync <Billing>(query.ToString(), scheduledPayment, token));
}
private void CreateSchedulePaymentCondition(ScheduledPaymentImport billing,
IEnumerable <ImporterSettingDetail> details, StringBuilder builder)
{
foreach (var data in details.Where(d => d.ImportDivision == 1))
{
if (data.Sequence == (int)Fields.ParentCustomerCode)
{
builder.Append(@"
AND COALESCE(cg.ParentCustomerId, b.CustomerId) = @ParentCustomerId");
}
else if (data.Sequence == (int)Fields.CustomerCode)
{
builder.Append(@"
AND b.CustomerId = @CustomerId");
}
else if (data.Sequence == (int)Fields.BilledAt)
{
builder.Append(@"
AND b.BilledAt = @BilledAt");
}
else if (data.Sequence == (int)Fields.BillingAmount)
{
builder.Append(@"
AND b.BillingAmount = @BillingAmount");
}
else if (data.Sequence == (int)Fields.DueAt)
{
builder.Append(@"
AND b.DueAt = @DueAt");
}
else if (data.Sequence == (int)Fields.DepartmentCode)
{
builder.Append(@"
AND b.DepartmentId = @DepartmentId");
}
else if (data.Sequence == (int)Fields.DebitAccountTitleCode)
{
builder.Append(@"
AND b.DebitAccountTitleId = @DebitAccountTitleId");
}
else if (data.Sequence == (int)Fields.SalesAt)
{
builder.Append(@"
AND b.SalesAt = @SalesAt");
}
else if (data.Sequence == (int)Fields.InvoiceCode)
{
builder.Append(@"
AND b.InvoiceCode = @InvoiceCode");
}
else if (data.Sequence == (int)Fields.ClosingAt)
{
builder.Append(@"
AND b.ClosingAt = @ClosingAt");
}
else if (data.Sequence == (int)Fields.Note1)
{
builder.Append(@"
AND b.Note1 = @Note1");
}
else if (data.Sequence == (int)Fields.BillingCategoryCode)
{
builder.Append(@"
AND b.BillingCategoryId = @BillingCategoryId");
}
else if (data.Sequence == (int)Fields.Note2)
{
builder.Append(@"
AND b.Note2 = @Note2");
}
else if (data.Sequence == (int)Fields.Note3)
{
builder.Append(@"
AND b.Note3 = @Note3");
}
else if (data.Sequence == (int)Fields.Note4)
{
builder.Append(@"
AND b.Note4 = @Note4");
}
else if (data.Sequence == (int)Fields.Note5)
{
builder.Append(@"
AND b.Note5 = @Note5");
}
else if (data.Sequence == (int)Fields.Note6)
{
builder.Append(@"
AND b.Note6 = @Note6");
}
else if (data.Sequence == (int)Fields.Note7)
{
builder.Append(@"
AND b.Note7 = @Note7");
}
else if (data.Sequence == (int)Fields.Note8)
{
builder.Append(@"
AND b.Note8 = @Note8");
}
else if (data.Sequence == (int)Fields.CurrencyCode)
{
builder.Append(@"
AND b.CurrencyId = @CurrencyId");
}
}
if (billing.AssignmentFlag == 0)
{
builder.Append(@"
AND b.AssignmentFlag = 0");
}
else
{
builder.Append(@"
AND (b.AssignmentFlag = 0 OR b.AssignmentFlag = 1)");
}
}
