public Task <IEnumerable <CustomerGroup> > GetAsync(CustomerGroupSearch option, CancellationToken token = default(CancellationToken))
{
if (option.RequireSingleCusotmerRelation)
{
return(GetCustomerForCustomerGroupAsync(option, token));
}
var query = @"
SELECT cg.*
, pcs.[Code] [ParentCustomerCode]
, pcs.[Name] [ParentCustomerName]
, pcs.[Kana] [ParentCustomerKana]
, ccs.[Code] [ChildCustomerCode]
, ccs.[Name] [ChildCustomerName]
FROM CustomerGroup cg
INNER JOIN Customer pcs ON pcs.Id = cg.ParentCustomerId
INNER JOIN Customer ccs ON ccs.Id = cg.ChildCustomerId
WHERE cg.ParentCustomerId = cg.ParentCustomerId";
if (option.CompanyId.HasValue)
{
query += @"
AND pcs.CompanyId = @CompanyId";
}
if (option.ParentIds?.Any() ?? false)
{
query += @"
AND cg.ParentCustomerId IN (SELECT Id FROM @ParentIds)";
}
if (option.ChildIds?.Any() ?? false)
{
query += @"
AND cg.ChildCustomerId IN (SELECT Id FROM @ChildIds)
UNION
SELECT cs.Id [ParentCustomerId]
, cs.Id [ChildCustomerId]
, cs.[Code] [ParentCustomerCode]
, cs.[Name] [ParentCustomerName]
, cs.[Kana] [ParentCustomerKana]
, cs.[Code] [ChildCustomerCode]
, cs.[Name] [ChildCustomerName]
FROM Customer cs
WHERE cs.Id IN (SELECT Id FROM @ChildIds)
AND cs.IsParent = 1";
}
return(dbHelper.GetItemsAsync <CustomerGroup>(query, new {
option.CompanyId,
ParentIds = option.ParentIds.GetTableParameter(),
ChildIds = option.ChildIds.GetTableParameter(),
}, token));
}
private Task <IEnumerable <CustomerGroup> > GetCustomerForCustomerGroupAsync(CustomerGroupSearch option, CancellationToken token = default(CancellationToken))
{
var query = @"
SELECT cs.Id [ChildCustomerId]
, cs.Code [ChildCustomerCode]
, cs.Name [ChildCustomerName]
, COALESCE(pcs.Code, cs.Code) [ParentCustomerCode]
, COALESCE(pcs.Name, cs.Name) [ParentCustomerName]
, COALESCE(pcs.Id,
CASE cs.IsParent WHEN 1 THEN cs.Id ELSE 0 END)
[ParentCustomerId]
FROM Customer cs
LEFT JOIN CustomerGroup cg ON cg.ChildCustomerId = cs.Id
LEFT JOIN Customer pcs ON cg.ParentCustomerId = pcs.Id
WHERE cs.CompanyId = @CompanyId
AND cs.Code = @Code
";
return(dbHelper.GetItemsAsync <CustomerGroup>(query, option, token));
}
public async Task <ActionResult <IEnumerable <CustomerGroup> > > GetItems(CustomerGroupSearch option, CancellationToken token) => (await customerGroupProcessor.GetAsync(option, token)).ToArray();
public async Task <IEnumerable <CustomerGroup> > GetAsync(CustomerGroupSearch option, CancellationToken token = default(CancellationToken)) => await customerGroupByIdQueryProcessor.GetAsync(option, token);
