public async Task <IEnumerable <BillingInvoice> > GetAsync(BillingInvoiceSearch searchOption,
CancellationToken token = default(CancellationToken))
{
var setting = await invoiceCommonSettingProcessor.GetAsync(searchOption.CompanyId, token);
return(await billingInvoiceQueryProcessor.GetAsync(searchOption, setting, token));
}
public async Task <IEnumerable <BillingInvoice> > GetR(BillingInvoiceSearch option)
{
return(await hubContext.DoAsync(option.ConnectionId, async (notifier, token) => {
var result = (await billingInvoiceProcessor.GetAsync(option, token)).ToArray();
notifier?.UpdateState();
return result;
}));
}
public async Task <ActionResult <int> > GetCountR(BillingInvoiceSearch option)
{
return(await hubContext.DoAsync(option.ConnectionId, async (notifier, token) =>
{
var result = (await billingInvoiceProcessor.GetAsync(option, token)).Count();
notifier?.UpdateState();
return result;
}));
}
public Task <IEnumerable <BillingInvoice> > GetAsync(BillingInvoiceSearch searchOption,
InvoiceCommonSetting invoiceCommonSetting,
CancellationToken token = default(CancellationToken))
{
return(dbHelper.ExecuteQueriesAsync(async connection =>
{
await dbHelper.ExecuteAsync(connection,
GetQueryInsertWorkBillingInvoices(searchOption),
searchOption, token);
return await dbHelper.QueryAsync <BillingInvoice>(connection,
GetQuerySelectBillingInvoices(searchOption, invoiceCommonSetting),
searchOption,
token);
}));
}
public async Task <CountResult> GetCountAsync(string SessionKey,
BillingInvoiceSearch searchOption,
string connectionId)
{
return(await authorizationProcessor.DoAuthorizeAsync(SessionKey, async token =>
{
var notifier = hubContext.CreateNotifier(connectionId);
var result = (await billingInvoiceProcessor.GetAsync(searchOption, token)).Count();
notifier?.UpdateState();
return new CountResult()
{
ProcessResult = new ProcessResult {
Result = true
},
Count = result
};
}, logger, connectionId));
}
public async Task <IEnumerable <BillingInvoice> > Get(BillingInvoiceSearch option, CancellationToken token) => (await billingInvoiceProcessor.GetAsync(option, token)).ToArray();
public async Task <int> UpdatePublishAt(BillingInvoiceSearch option, CancellationToken token) => (await billingInvoiceProcessor.UpdatePublishAtAsync(option.BillingInputIds, token)).Count;
public async Task <int> UpdatePublishAtR(BillingInvoiceSearch option)
=> await hubContext.DoAsync(option.ConnectionId, async (notifier, token)
=> (await billingInvoiceProcessor.UpdatePublishAtAsync(option.BillingInputIds, token)).Count);
public async Task <IEnumerable <BillingInvoiceDetailForExport> > GetDetailsForExport(BillingInvoiceSearch option, CancellationToken token) => (await billingInvoiceProcessor.GetDetailsForExportAsync(option.BillingInputIds, option.CompanyId, token)).ToList();
public async Task <IEnumerable <BillingInvoiceDetailForExport> > GetDetailsForExportR(BillingInvoiceSearch option)
{
return(await hubContext.DoAsync(option.ConnectionId, async (notifier, token) =>
{
var result = (await billingInvoiceProcessor.GetDetailsForExportAsync(option.BillingInputIds, option.CompanyId, token)).ToList();
notifier?.UpdateState();
return result;
}));
}
public async Task <int> CancelPublish(BillingInvoiceSearch option, CancellationToken token) => (await billingInvoiceProcessor.CancelPublishAsync(option.BillingIds, token)).Count;
public async Task <int> CancelPublishR(BillingInvoiceSearch option)
=> await hubContext.DoAsync(option.ConnectionId, async (notifier, token)
=> (await billingInvoiceProcessor.CancelPublishAsync(option.BillingIds, token)).Count);
public async Task <int> DeleteWorkTable(BillingInvoiceSearch option, CancellationToken token) => await billingInvoiceProcessor.DeleteWorkTableAsync(option.ClientKey, token);
public async Task <int> GetCount(BillingInvoiceSearch option, CancellationToken token) => (await billingInvoiceProcessor.GetAsync(option, token)).Count();
private string GetQueryInsertWorkBillingInvoices(BillingInvoiceSearch option)
{
var sql = @"
DELETE FROM WorkBillingInvoice
WHERE ClientKey = @ClientKey
INSERT INTO WorkBillingInvoice
( [ClientKey]
,[BillingId]
,[TemporaryBillingInputId]
,[BillingInputId] )
SELECT @ClientKey
, b.BillingId
, DENSE_RANK() OVER(ORDER BY b.BillingInputId, b.BillingInputId2) AS TemporaryBillingInputId
, b.BillingInputId
FROM (
---BillingInputId 有り
SELECT b.Id AS BillingId
, b.BillingInputId
, 0 AS BillingInputId2
FROM Billing b
LEFT JOIN Department d
ON d.Id = b.DepartmentId
LEFT JOIN Customer c
ON c.Id = b.CustomerId
LEFT JOIN BillingInput bi
ON bi.Id = b.BillingInputId
LEFT JOIN Staff s
ON s.Id = b.StaffId
LEFT JOIN Category cat
ON cat.id = b.CollectCategoryId
WHERE b.CompanyId = @CompanyId
AND b.BillingInputId IS NOT NULL
AND b.DeleteAt IS NULL
{0}
UNION ALL
---BillingInputId 無し
SELECT b.Id AS BillingId
, b.BillingInputId
, DENSE_RANK()
OVER(ORDER BY b.CurrencyId
, b.CustomerId
, b.DepartmentId
, b.StaffId
, b.BilledAt
, b.ClosingAt
, b.DueAt
, b.CollectCategoryId
, b.InvoiceCode
, b.DestinationId
) AS BillingInputId2
FROM Billing b
LEFT JOIN Department d
ON d.Id = b.DepartmentId
LEFT JOIN Customer c
ON c.Id = b.CustomerId
LEFT JOIN BillingInput bi
ON bi.Id = b.BillingInputId
LEFT JOIN Staff s
ON s.Id = b.StaffId
LEFT JOIN Category cat
ON cat.id = b.CollectCategoryId
WHERE b.CompanyId = @CompanyId
AND b.BillingInputId IS NULL
AND b.DeleteAt IS NULL
{0}
) AS b
";
//検索条件
var condition = new StringBuilder();
if (option.ClosingAt.HasValue)
{
condition.AppendLine(" AND b.ClosingAt = @ClosingAt");
}
if (option.BilledAtFrom.HasValue)
{
condition.AppendLine(" AND b.BilledAt >= @BilledAtFrom");
}
if (option.BilledAtTo.HasValue)
{
condition.AppendLine(" AND b.BilledAt <= @BilledAtTo");
}
if (option.CollectCategoryId != 0)
{
condition.AppendLine(" AND b.CollectCategoryId = @CollectCategoryId");
}
if (!string.IsNullOrEmpty(option.DepartmentCodeFrom))
{
condition.AppendLine(" AND d.Code >= @DepartmentCodeFrom");
}
if (!string.IsNullOrEmpty(option.DepartmentCodeTo))
{
condition.AppendLine(" AND d.Code <= @DepartmentCodeTo");
}
if (!string.IsNullOrEmpty(option.CustomerCodeFrom))
{
condition.AppendLine(" AND c.Code >= @CustomerCodeFrom");
}
if (!string.IsNullOrEmpty(option.CustomerCodeTo))
{
condition.AppendLine(" AND c.Code <= @CustomerCodeTo");
}
if (option.IsPublished)
{
condition.AppendLine(" AND bi.PublishAt IS NOT NULL");
}
else
{
condition.AppendLine(" AND bi.PublishAt IS NULL");
}
if (!string.IsNullOrEmpty(option.StaffCodeFrom))
{
condition.AppendLine(" AND s.Code >= @StaffCodeFrom");
}
if (!string.IsNullOrEmpty(option.StaffCodeTo))
{
condition.AppendLine(" AND s.Code <= @StaffCodeTo");
}
if (!string.IsNullOrEmpty(option.InvoiceCodeFrom))
{
condition.AppendLine(" AND b.InvoiceCode >= @InvoiceCodeFrom");
}
if (!string.IsNullOrEmpty(option.InvoiceCodeTo))
{
condition.AppendLine(" AND b.InvoiceCode <= @InvoiceCodeTo");
}
if (!string.IsNullOrEmpty(option.InvoiceCode))
{
option.InvoiceCode = Sql.GetWrappedValue(option.InvoiceCode);
condition.AppendLine(" AND b.InvoiceCode like @InvoiceCode");
}
//除外設定
if (!option.IsPublished)
{
condition.AppendLine(@"
AND cat.ExcludeInvoicePublish = 0
AND c.ExcludeInvoicePublish = 0
");
}
return(string.Format(sql, condition));
}
private string GetQuerySelectBillingInvoices(BillingInvoiceSearch option,
InvoiceCommonSetting invoiceCommonSetting)
{
var select1 = option.IsPublished ?
"0"
: "1";
var select2 = option.IsPublished ?
"bi.InvoiceTemplateId AS InvoiceTemplateId"
: "COALESCE(its.Id, 0) AS InvoiceTemplateId";
var builder = new StringBuilder();
builder.Append($@"
declare @CompanyCode nvarchar(20) = (SELECT Code From Company WHERE Id = @CompanyId)
SELECT inv.TemporaryBillingInputId
, @CompanyCode AS CompanyCode
, inv.BillingInputId
, {select1} AS Checked
, its.Code AS InvoiceTemplateCode
, {select2}
, inv.InvoiceCode
, inv.DetailsCount
, inv.CustomerId
, cus.Code AS CustomerCode
, cus.Name AS CustomerName
, cus.PostalCode AS CustomerPostalCode
, cus.Address1 AS CustomerAddress1
, cus.Address2 AS CustomerAddress2
, cus.DestinationDepartmentName AS CustomerDestinationDepartmentName
, cus.CustomerStaffName AS CustomerStaffName
, cus.Honorific AS CustomerHonorific
, inv.AmountSum
, inv.RemainAmountSum
, (cat.code + ':' + cat.name) AS CollectCategoryCodeAndNeme
, inv.ClosingAt
, inv.BilledAt
, dep.Code AS DepartmentCode
, dep.Name AS DepartmentName
, sta.code AS StaffCode
, sta.name AS StaffName
, inv.DestinationId
, dest.code AS DestnationCode
, dest.Name AS DestinationName
, dest.PostalCode AS DestinationPostalCode
, dest.Address1 AS DestinationAddress1
, dest.Address2 AS DestinationAddress2
, dest.DepartmentName AS DestinationDepartmentName
, dest.Addressee AS DestinationAddressee
, dest.Honorific AS DestinationHonorific
, bi.PublishAt
, bi.PublishAt1st
, inv.UpdateAt
FROM (
SELECT wbi.TemporaryBillingInputId
, wbi.BillingInputId
, b.CompanyId
, b.CurrencyId
, b.CustomerId
, b.DepartmentId
, b.StaffId
, b.DestinationId
, b.BilledAt
, b.ClosingAt
, b.DueAt
, b.InvoiceCode
, SUM(b.BillingAmount) AS AmountSum
, SUM(b.RemainAmount) AS RemainAmountSum
, b.CollectCategoryId
, COUNT(*) AS DetailsCount
, CASE WHEN (MAX(b.AssignmentFlag) = MIN(b.AssignmentFlag) AND MAX(b.AssignmentFlag) = 0) THEN 0
WHEN (MAX(b.AssignmentFlag) = MIN(b.AssignmentFlag) AND MAX(b.AssignmentFlag) = 2) THEN 2
ELSE 1
END AS AssignmentFlagForInvoice
, MAX(b.UpdateAt) AS UpdateAt
FROM WorkBillingInvoice wbi
INNER JOIN Billing b
ON b.Id = wbi.BillingId
WHERE wbi.ClientKey = @ClientKey
GROUP BY wbi.TemporaryBillingInputId
, wbi.BillingInputId
, b.CompanyId
, b.CurrencyId
, b.CustomerId
, b.DepartmentId
, b.StaffId
, b.DestinationId
, b.BilledAt
, b.ClosingAt
, b.DueAt
, b.InvoiceCode
, b.CollectCategoryId
) AS inv
LEFT JOIN BillingInput bi
ON bi.Id = inv.BillingInputId
LEFT JOIN Customer cus
ON cus.Id = inv.CustomerId
LEFT JOIN Category cat
ON cat.id = inv.CollectCategoryId
LEFT JOIN Department dep
ON dep.id = inv.departmentId
LEFT JOIN Staff sta
ON sta.Id = inv.StaffId
LEFT JOIN Destination dest
ON dest.id = inv.DestinationId
LEFT JOIN InvoiceTemplateSetting its
ON its.CollectCategoryId = inv.CollectCategoryId
WHERE 1 = 1
");
//検索条件
if (option.IsPublished)
{
var flags = (AssignmentFlagChecked)option.AssignmentFlg;
var selectedFlags = new List <int>();
if (flags.HasFlag(AssignmentFlagChecked.NoAssignment))
{
selectedFlags.Add(0);
}
if (flags.HasFlag(AssignmentFlagChecked.PartAssignment))
{
selectedFlags.Add(1);
}
if (flags.HasFlag(AssignmentFlagChecked.FullAssignment))
{
selectedFlags.Add(2);
}
if (selectedFlags.Any() && !flags.HasFlag(AssignmentFlagChecked.All))
{
builder.AppendLine($" AND inv.AssignmentFlagForInvoice IN ({(string.Join(", ", selectedFlags))})");
}
if (option.PublishAtFrom.HasValue)
{
builder.AppendLine(" AND bi.PublishAt >= @PublishAtFrom");
}
if (option.PublishAtTo.HasValue)
{
builder.AppendLine(" AND bi.PublishAt <= @PublishAtTo");
}
if (option.PublishAtFirstFrom.HasValue)
{
builder.AppendLine(" AND bi.PublishAt1st >= @PublishAtFirstFrom");
}
if (option.PublishAtFirstTo.HasValue)
{
builder.AppendLine(" AND bi.PublishAt1st <= @PublishAtFirstTo");
}
}
//除外設定
if (invoiceCommonSetting.ExcludeAmountZero == 1)
{
if (option.ReportInvoiceAmount == 0)
{
builder.AppendLine(" AND inv.AmountSum <> 0");
}
else
{
builder.AppendLine(" AND inv.RemainAmountSum <> 0");
}
}
if (invoiceCommonSetting.ExcludeMinusAmount == 1)
{
if (option.ReportInvoiceAmount == 0)
{
builder.AppendLine(" AND inv.AmountSum >= 0");
}
else
{
builder.AppendLine(" AND inv.RemainAmountSum >= 0");
}
}
if (invoiceCommonSetting.ExcludeMatchedData == 1)
{
builder.AppendLine(" AND inv.AssignmentFlagForInvoice <> 2");
}
builder.AppendLine(" ORDER BY inv.BilledAt asc, cus.Code asc, dest.code asc, inv.BillingInputId asc, inv.TemporaryBillingInputId asc");
return(builder.ToString());
}
