public Task <IEnumerable <BillingInvoiceDetailForPrint> > GetDetailsForPrintAsync(
BillingInvoiceDetailSearch option,
InvoiceCommonSetting invoiceCommonSetting, CancellationToken token = default(CancellationToken))
{
return(dbHelper.GetItemsAsync <BillingInvoiceDetailForPrint>(
GetQuerySelectBillingInvoiceDetailsForPrint(option, invoiceCommonSetting),
new
{
option.ClientKey,
BillingInputIds = option.BillingInputIds.GetTableParameter(),
TemporaryBillingInputIds = option.TemporaryBillingInputIds.GetTableParameter(),
option.InvoiceTemplateId
}, token));
}
public async Task <BillingInvoiceDetailResult> GetDetailsForPrintAsync(string SessionKey,
BillingInvoiceDetailSearch billingInvoiceDetailSearch)
{
return(await authorizationProcessor.DoAuthorizeAsync(SessionKey, async token =>
{
var result = (await billingInvoiceProcessor.GetDetailsForPrintAsync(billingInvoiceDetailSearch, token)).ToList();
return new BillingInvoiceDetailResult
{
ProcessResult = new ProcessResult {
Result = true
},
BillingInvoicesDetails = result,
};
}, logger));
}
public async Task <IEnumerable <BillingInvoiceDetailForPrint> > GetDetailsForPrint(
BillingInvoiceDetailSearch option, CancellationToken token)
=> (await billingInvoiceProcessor.GetDetailsForPrintAsync(option, token)).ToArray();
private string GetQuerySelectBillingInvoiceDetailsForPrint(
BillingInvoiceDetailSearch billingInvoiceDetailSearch,
InvoiceCommonSetting invoiceCommonSetting)
{
var templateId = billingInvoiceDetailSearch.TemporaryBillingInputIds != null
? "@InvoiceTemplateId"
: "bi.InvoiceTemplateId";
var builder = new StringBuilder(@"
SELECT b.BillingInputId
, b.InvoiceCode
, b.BilledAt
, b.ClosingAt
, b.SalesAt
, b.DueAt
, b.BillingAmount
, CASE b.TaxClassId WHEN 0 THEN b.TaxAmount
ELSE 0
END AS TaxAmount
, CASE b.TaxClassId WHEN 0 THEN (b.BillingAmount - b.TaxAmount)
ELSE b.BillingAmount
END AS TaxExcludedPrice
, b.RemainAmount
, CASE b.TaxClassId WHEN 1 THEN '税込'
WHEN 0 THEN '税抜'
ELSE tc.Name
END AS TaxClassName
, b.Note1
, b.Note2
, CASE b.Quantity WHEN 0 THEN NULL
ELSE b.Quantity
END AS Quantity
, CASE b.UnitPrice WHEN 0 THEN NULL
ELSE b.UnitPrice
END AS UnitPrice
, b.UnitSymbol
, s.Name AS StaffName
, it.DisplayStaff
, '' AS Tel
, '' AS Fax
, c.PostalCode AS CustomerPostalCode
, c.Address1 AS CustomerAddress1
, c.Address2 AS CustomerAddress2
, c.Code AS CustomerCode
, c.Name AS CustomerName
, c.DestinationDepartmentName AS CustomerDestinationDepartmentName
, c.CustomerStaffName AS CustomerStaffName
, c.Honorific AS CustomerHonorific
, c.ShareTransferFee
");
if (billingInvoiceDetailSearch.DestinationId.HasValue)
{
builder.AppendFormat(@" , {0} AS DestinationId
", billingInvoiceDetailSearch.DestinationId.Value);
}
else
{
builder.AppendLine(" , b.DestinationId");
}
builder.AppendLine(@"
, des.Name AS DestinationName
, des.PostalCode AS DestinationPostalCode
, des.Address1 AS DestinationAddress1
, des.Address2 AS DestinationAddress2
, des.DepartmentName AS DestinationDepartmentName
, des.Addressee AS DestinationAddressee
, des.Honorific AS DestinationHonorific
, it.Title
, it.Greeting
, it.DueDateComment
, it.DueDateFormat
, it.TransferFeeComment
, c.ReceiveAccountId1
, c.ReceiveAccountId2
, c.ReceiveAccountId3
, ('振込銀行：'
+ c.ExclusiveBankName
+ ' '
+ c.ExclusiveBranchName
+ ' '
+ at.Name
+ ' '
+ RIGHT(c.ExclusiveAccountNumber, 7)
) AS ExclusiveAccount
");
if (billingInvoiceDetailSearch.TemporaryBillingInputIds != null)
{
builder.Append(@"
, (SELECT COUNT(*)
FROM WorkBillingInvoice wbi2
WHERE wbi2.ClientKey = @ClientKey
AND wbi2.TemporaryBillingInputId = wbi.TemporaryBillingInputId
) AS DetailCount
FROM Billing b
");
}
else
{
builder.Append(@"
, (SELECT COUNT(*)
FROM Billing b2
WHERE b2.BillingInputId = b.BillingInputId
) AS DetailCount
FROM Billing b
");
}
if (billingInvoiceDetailSearch.TemporaryBillingInputIds != null)
{
builder.Append(@"
INNER JOIN WorkBillingInvoice wbi
ON wbi.ClientKey = @ClientKey
AND wbi.BillingId = b.Id
");
}
builder.Append($@"
LEFT join Customer c
ON c.Id = b.CustomerId
LEFT JOIN TaxClass tc
ON tc.Id = b.TaxClassId
LEFT JOIN Staff s
ON s.Id = b.StaffId
LEFT JOIN BillingInput bi
ON bi.Id = b.BillingInputId
LEFT JOIN InvoiceTemplateSetting it
ON it.Id = { templateId }
LEFT JOIN BankAccountType at
ON at.Id = c.ExclusiveAccountTypeId
LEFT JOIN Destination des
");
if (billingInvoiceDetailSearch.DestinationId.HasValue)
{
builder.AppendFormat(@" ON des.Id = {0}
", billingInvoiceDetailSearch.DestinationId.Value);
}
else
{
builder.AppendLine(" ON des.Id = b.DestinationId");
}
if (billingInvoiceDetailSearch.TemporaryBillingInputIds != null)
{
builder.AppendLine("WHERE wbi.TemporaryBillingInputId IN (SELECT Id FROM @TemporaryBillingInputIds)");
}
else
{
builder.AppendLine("WHERE b.BillingInputId IN (SELECT Id FROM @BillingInputIds)");
}
if (invoiceCommonSetting.ExcludeMatchedData != 0)
{
builder.AppendLine(" AND b.AssignmentFlag <> 2");
}
builder.AppendLine(" ORDER BY b.BilledAt asc, c.Code asc, des.Code asc, b.BillingInputId asc");
return(builder.ToString());
}
public async Task <IEnumerable <BillingInvoiceDetailForPrint> > GetDetailsForPrintAsync(BillingInvoiceDetailSearch option, CancellationToken token = default(CancellationToken))
{
var invoiceCommonSetting = await invoiceCommonSettingProcessor.GetAsync(option.CompanyId, token);
return(await billingInvoiceQueryProcessor.GetDetailsForPrintAsync(option, invoiceCommonSetting, token));
}
