private async Task <int> SearchReminderOutputedAsync(ReminderOutputedSearch search)
{
var result = new ReminderOutputedResult();
decimal totalAmount = 0m;
int count = 0;
await ServiceProxyFactory.DoAsync <ReminderServiceClient>(async client =>
{
result = await client.GetOutputedItemsAsync(SessionKey, SearchCondition);
grdReminder.DataSource = new BindingSource(result.ReminderOutputed, null);
if (result.ProcessResult.Result)
{
count = result.ReminderOutputed.Count;
}
if (count > 0)
{
totalAmount = result.ReminderOutputed.Sum(x => x.RemainAmount);
tbcReminder.SelectedTab = tabReminderResult;
BaseContext.SetFunction03Enabled(true);
BaseContext.SetFunction08Enabled(true);
BaseContext.SetFunction09Enabled(true);
}
else
{
tbcReminder.SelectedTab = tabReminderSearch;
grdReminder.DataSource = null;
BaseContext.SetFunction03Enabled(false);
BaseContext.SetFunction08Enabled(false);
BaseContext.SetFunction09Enabled(false);
}
});
return(count);
}
/// <summary>検索データ</summary>
/// <returns>ArrearagesListSearch</returns>
private ReminderOutputedSearch GetSearchDataCondition()
{
var reminderSearch = new ReminderOutputedSearch();
reminderSearch.CompanyId = CompanyId;
reminderSearch.OutputAtFrom = datOutputAtFrom.Value;
reminderSearch.OutputAtTo = datOutputAtTo.Value;
reminderSearch.OutputNoFrom = (int?)nmbOutputNoFrom.Value;
reminderSearch.OutputNoTo = (int?)nmbOutputNoTo.Value;
reminderSearch.CurrencyCode = UseForeignCurrency ? reminderSearch.CurrencyCode = txtCurrencyCode.Text : Constants.DefaultCurrencyCode;
if (!string.IsNullOrWhiteSpace(txtFromCustomerCode.Text))
{
reminderSearch.CustomerCodeFrom = txtFromCustomerCode.Text;
}
if (!string.IsNullOrWhiteSpace(txtToCustomerCode.Text))
{
reminderSearch.CustomerCodeTo = txtToCustomerCode.Text;
}
reminderSearch.UseDestinationSummarized = UseDestinationSummarized;
return(reminderSearch);
}
public async Task <IEnumerable <ReminderOutputed> > GetOutputedItems(ReminderOutputedSearch option, CancellationToken token) => (await reminderProcessor.GetOutputedItemsAsync(option, token)).ToArray();
public Task <IEnumerable <ReminderOutputed> > GetItemsAsync(ReminderOutputedSearch search, CancellationToken token = default(CancellationToken))
{
var condition = new StringBuilder();
if (search.OutputAtFrom != null)
{
condition.AppendLine("AND o.OutputAt >= @OutputAtFrom");
}
if (search.OutputAtTo != null)
{
search.OutputAtTo = search.OutputAtTo.Value.AddDays(1);
condition.AppendLine("AND o.OutputAt < @OutputAtTo");
}
if (search.OutputNoFrom != null)
{
condition.AppendLine("AND o.OutputNo >= @OutputNoFrom");
}
if (search.OutputNoTo != null)
{
condition.AppendLine("AND o.OutputNo <= @OutputNoTo");
}
if (!string.IsNullOrEmpty(search.CustomerCodeFrom))
{
condition.AppendLine("AND cs.Code >= @CustomerCodeFrom");
}
if (!string.IsNullOrEmpty(search.CustomerCodeTo))
{
condition.AppendLine("AND cs.Code <= @CustomerCodeTo");
}
var destinationSelect = new StringBuilder();
var destinationJoin = new StringBuilder();
var destinationGroup = new StringBuilder();
if (search.UseDestinationSummarized)
{
destinationSelect.AppendLine(", d.Id [DestinationId]");
destinationSelect.AppendLine(", COALESCE(d.Code, '') [DestinationCode]");
destinationSelect.AppendLine(", CASE WHEN d.Id IS NULL THEN");
destinationSelect.AppendLine(" IIF (COALESCE(cs.PostalCode,'') <> '','〒' + cs.PostalCode, '') + ' ' + cs.Name + cs.Address1 + cs.Address2 + ' ' + cs.DestinationDepartmentName + ' ' + cs.CustomerStaffName + cs.Honorific");
destinationSelect.AppendLine(" ELSE");
destinationSelect.AppendLine(" IIF (COALESCE(d.PostalCode,'') <> '','〒' + d.PostalCode, '') + ' ' + d.Name + d.Address1 + d.Address2 + ' ' + d.DepartmentName + ' ' + d.Addressee + d.Honorific");
destinationSelect.AppendLine(" END [DestinationDisplay]");
destinationJoin.AppendLine("LEFT JOIN Destination d");
destinationJoin.AppendLine("ON d.CompanyId = @CompanyId");
destinationJoin.AppendLine("AND d.Id = o.DestinationId");
destinationGroup.AppendLine(", cs.PostalCode, cs.Address1, cs.Address2, cs.DestinationDepartmentName, cs.CustomerStaffName, cs.Honorific");
destinationGroup.AppendLine(", d.Id, d.Code, d.Name, d.PostalCode, d.Address1, d.Address2, d.DepartmentName, d.Addressee, d.Honorific");
}
var query = $@"
SELECT
o.OutputAt
, o.OutputNo
, cs.Id [CustomerId]
, cs.Code [CustomerCode]
, cs.Name [CustomerName]
, COUNT(*) BillingCount
, SUM(b.BillingAmount) BillingAmount
, SUM(o.RemainAmount) RemainAmount
, o.ReminderTemplateId
{destinationSelect.ToString()}
FROM ReminderOutputed o
INNER JOIN Billing b
ON o.BillingId = b.Id
AND b.CompanyId = @CompanyId
INNER JOIN Currency ccy
ON b.CurrencyId = ccy.Id
AND ccy.Code = @CurrencyCode
INNER JOIN Customer cs
ON b.CustomerId = cs.Id
{condition.ToString()}
{destinationJoin.ToString()}
GROUP BY o.OutputAt, o.OutputNo, cs.Id, cs.Code, cs.Name, o.ReminderTemplateId
{destinationGroup.ToString()}
ORDER BY o.OutputAt, o.OutputNo
";
return(dbHelper.GetItemsAsync <ReminderOutputed>(query, search, token));
}
public async Task <IEnumerable <ReminderOutputed> > GetOutputedItemsAsync(ReminderOutputedSearch search, CancellationToken token = default(CancellationToken)) => await reminderOutputedQueryProcessor.GetItemsAsync(search, token);
public async Task <ReminderOutputedResult> GetOutputedItemsAsync(string SessionKey, ReminderOutputedSearch search)
{
return(await authorizationProcessor.DoAuthorizeAsync(SessionKey, async token =>
{
var processResult = new ProcessResult();
var result = (await reminderProcessor.GetOutputedItemsAsync(search, token)).ToList();
if (result != null)
{
processResult.Result = true;
}
return new ReminderOutputedResult
{
ProcessResult = processResult,
ReminderOutputed = result,
};
}, logger));
}