private async Task <PeriodicBillingSettingsResult> GetSettingsAsync(PeriodicBillingSettingSearch option)
=> await ServiceProxyFactory.DoAsync(async (PeriodicBillingSettingMasterService.PeriodicBillingSettingMasterClient client) =>
{
var result = await client.GetItemsAsync(SessionKey, option);
if (result.ProcessResult.Result && result.PeriodicBillingSettings.Any())
{
foreach (var setting in result.PeriodicBillingSettings)
{
setting.InitializeForGetBilling(BaseDate);
}
}
return(result);
});
public Task <IEnumerable <PeriodicBillingSettingDetail> > GetAsync(PeriodicBillingSettingSearch option, CancellationToken token = default(CancellationToken))
{
var query = @"
SELECT d.*
FROM PeriodicBillingSettingDetail d
INNER JOIN PeriodicBillingSetting s ON s.Id = d.PeriodicBillingSettingId
WHERE s.CompanyId = @CompanyId
AND s.Id IN (SELECT id FROM @Ids)";
return(dbHelper.GetItemsAsync <PeriodicBillingSettingDetail>(query, new {
option.CompanyId,
Ids = option.Ids?.GetTableParameter()
}, token));
}
public async Task <IEnumerable <PeriodicBillingSetting> > GetAsync(PeriodicBillingSettingSearch option, CancellationToken token = default(CancellationToken))
{
// データ量的に perfomance で問題になる場合は処理を分ける
var settings = (await periodicBillingSettingQueryProcessor.GetAsync(option, token)).ToArray();
option.Ids = settings.Select(x => x.Id).Distinct().ToArray();
var details = (await periodicBillingSettingDetailQueryProcessor.GetAsync(option, token))
.GroupBy(x => x.PeriodicBillingSettingId)
.ToDictionary(x => x.Key, x => x.ToList());
foreach (var setting in settings)
{
setting.Details = details[setting.Id];
}
return(settings);
}
public async Task <IEnumerable <Header> > SearchByKey(params string[] keys)
{
var option = new PeriodicBillingSettingSearch {
CompanyId = Application.Login.CompanyId,
};
if ((keys?.Any() ?? false) && !string.IsNullOrWhiteSpace(keys.First()))
{
option.Name = keys.First();
}
else
{
option.Name = string.Empty;
}
var result = await ServiceProxyFactory.DoAsync(async (PeriodicBillingSettingMasterService.PeriodicBillingSettingMasterClient client)
=> await client.GetItemsAsync(Application.Login.SessionKey, option));
if (result.ProcessResult.Result)
{
return(result.PeriodicBillingSettings);
}
return(new List <Header>());
}
public async Task <ActionResult <IEnumerable <PeriodicBillingSetting> > > GetItems(PeriodicBillingSettingSearch option, CancellationToken token) => (await periodicBillingSettingProcessor.GetAsync(option, token)).ToArray();
public Task <IEnumerable <PeriodicBillingSetting> > GetAsync(PeriodicBillingSettingSearch option, CancellationToken token = default(CancellationToken))
{
#region query
var query = new StringBuilder(@"
SELECT s.*
, pc.[UpdateAt] [LastUpdateAt]
, lu.[Name] [LastUpdatedBy]
, cc.[Name] [CollectCategoryName]
, dp.[Code] [DepartmentCode]
, dp.[Name] [DepartmentName]
, st.[Code] [StaffCode]
, st.[Name] [StaffName]
, ds.[Code] [DestinationCode]
, cs.[Code] [CustomerCode]
, cs.[Name] [CustomerName]
, CASE ds.[Addressee] WHEN N'' THEN ds.[DepartmentName] ELSE ds.[Addressee] END
+ CASE ds.[Honorific] WHEN N'' THEN N'' ELSE N' ' + ds.[Honorific] END
[DestinationName]
, ds.[Addressee]
, pbc.[LastCreateYearMonth]
FROM PeriodicBillingSetting s
INNER JOIN Customer cs ON cs.Id = s.CustomerId
LEFT JOIN Destination ds ON ds.Id = s.DestinationId
LEFT JOIN Category cc ON cc.Id = s.CollectCategoryId
LEFT JOIN Staff st ON st.Id = s.StaffId
LEFT JOIN Department dp ON dp.Id = s.DepartmentId
LEFT JOIN PeriodicBillingCreated pc ON s.Id = pc.PeriodicBillingSettingId AND pc.CreateYearMonth = @BaseDate
LEFT JOIN LoginUser lu ON lu.Id = pc.UpdateBy
LEFT JOIN (
SELECT pbc.PeriodicBillingSettingId
, MAX(CreateYearMonth) [LastCreateYearMonth]
FROM PeriodicBillingCreated pbc
GROUP BY
pbc.PeriodicBillingSettingId
) pbc ON s.Id = pbc.PeriodicBillingSettingId
WHERE s.CompanyId = @CompanyId
");
if (option.Ids?.Any() ?? false)
{
query.Append(@"
AND s.Id IN (SELECT Id FROM @Ids)");
}
if (!string.IsNullOrEmpty(option.Name))
{
option.Name = Sql.GetWrappedValue(option.Name);
query.Append(@"
AND (s.[Name] LIKE @Name
OR cs.[Name] LIKE @Name
OR ds.[Addressee] LIKE @Name
)");
}
if (option.BaseDate.HasValue)
{
query.Append(@"
AND s.StartMonth <= @BaseDate
AND @BaseDate <= COALESCE( s.EndMonth, CAST('9999-12-01' AS DATE) )
AND (s.BilledCycle = 1
OR s.StartMonth = @BaseDate
OR DateAdd(Month, s.BilledCycle * (DATEDIFF(Month, s.StartMonth, @BaseDate) / s.BilledCycle), s.StartMonth ) = @BaseDate )");
if (option.ReCreate)
{
query.Append(@"
AND EXISTS (
SELECT 1
FROM PeriodicBillingCreated pbc
WHERE pbc.PeriodicBillingSettingId = s.Id
AND pbc.CreateYearMonth = @BaseDate )");
}
else
{
query.Append(@"
AND NOT EXISTS (
SELECT 1
FROM PeriodicBillingCreated pbc
WHERE pbc.PeriodicBillingSettingId = s.Id
AND pbc.CreateYearMonth = @BaseDate )");
}
}
query.Append(@"
ORDER BY cs.[Code], s.[StartMonth]");
#endregion
return(dbHelper.GetItemsAsync <PeriodicBillingSetting>(query.ToString(), new {
option.CompanyId,
option.Name,
option.BaseDate,
Ids = option.Ids?.GetTableParameter(),
}, token));
}
public async Task <PeriodicBillingSettingsResult> GetItemsAsync(string sessionKey, PeriodicBillingSettingSearch option)
=> await authorizationProcessor.DoAuthorizeAsync(sessionKey, async token => {
var result = (await periodicBillingSettingProcessor.GetAsync(option, token)).ToList();
return(new PeriodicBillingSettingsResult {
ProcessResult = new ProcessResult {
Result = true
},
PeriodicBillingSettings = result,
});
}, logger);
