private bool RequireDepartmentSubtotal(CreditAgingListSearch opt,
CreditAgingList detailBuf, CreditAgingList detail)
=> opt.RequireDepartmentTotal &&
detailBuf != null &&
(detail == null ||
(detailBuf.DepartmentId != detail.DepartmentId)
);
private bool RequireCustomerGroupSubtotal(CreditAgingListSearch opt,
CreditAgingList detailBuf, CreditAgingList detail)
=> opt.ConsiderCustomerGroup &&
detailBuf != null &&
detailBuf.ParentCustomerId.HasValue &&
(detail == null ||
(detailBuf.ParentCustomerId != detail.ParentCustomerId ||
opt.RequireDepartmentTotal && detailBuf.DepartmentId != detail.DepartmentId ||
opt.RequireStaffTotal && detailBuf.StaffId != detail.StaffId)
);
private string CreateInitializeTempTable(CreditAgingListSearch option)
{
var builder = new StringBuilder();
builder.Append(@"
IF EXISTS (SELECT * FROM tempdb..sysobjects
WHERE id = object_id(N'tempdb..#WorkCredit'))
DROP TABLE #WorkCredit;
CREATE TABLE #WorkCredit
([CompanyId] INT NOT NULL
,[DepartmentId] INT NOT NULL
,[StaffId] INT NOT NULL
,[ParentCustomerId] INT NOT NULL
,[CustomerId] INT NOT NULL
,[YearMonth] DATETIME2(3) NOT NULL
,[DataType] INT NULL
,[Balance] NUMERIC(18, 5) NOT NULL DEFAULT (0)
,[BillingAmount] NUMERIC(18, 5) NOT NULL DEFAULT (0)
,[ReceiptAmount] NUMERIC(18, 5) NOT NULL DEFAULT (0)
,[MatchingAmount] NUMERIC(18, 5) NOT NULL DEFAULT (0)
,[ReceivableAmount] NUMERIC(18, 5) NOT NULL DEFAULT (0)
);
CREATE INDEX [IdxWorkCredit] ON [#WorkCredit]
([CompanyId] ASC
,[DepartmentId] ASC
,[StaffId] ASC
,[ParentCustomerId] ASC
,[CustomerId] ASC
,[YearMonth] ASC
);
");
if (option.ConsiderReceiptAmount)
{
builder.Append(@"
IF EXISTS (SELECT * FROM tempdb..sysobjects
WHERE id = object_id(N'tempdb..#WorkReceipt'))
DROP TABLE #WorkReceipt;
CREATE TABLE #WorkReceipt
([Id] BIGINT
,[CompanyId] INT
,[DepartmentId] INT
,[StaffId] INT
,[CustomerId] INT
,[Multiple] INT
,[YearMonth] DATETIME2
,[ReceiptAmount] NUMERIC(18, 5)
,[MatchingAmount] NUMERIC(18, 5));
");
}
return(builder.ToString());
}
public async Task <CreditAgingListsResult> GetAsync(string SessionKey, CreditAgingListSearch searchOption, string connectionId)
{
return(await authorizationProcessor.DoAuthorizeAsync(SessionKey, async token =>
{
var notifier = hubContext.CreateNotifier(connectionId);
var result = await creditAgingListProcessor.GetAsync(searchOption, notifier, token);
return new CreditAgingListsResult
{
ProcessResult = new ProcessResult {
Result = true
},
CreditAgingLists = new List <CreditAgingList>(result),
};
}, logger, connectionId));
}
public async Task <byte[]> GetAsync(CreditAgingListSearch option, IProgressNotifier notifier = null, CancellationToken token = default(CancellationToken))
{
var companyTask = companyQueryProcessor.GetAsync(new CompanySearch {
Id = option.CompanyId,
}, token);
var loadTask = creditAgingListProcessor.GetAsync(option, notifier, token);
await Task.WhenAll(companyTask, loadTask);
var company = companyTask.Result.First();
var items = loadTask.Result.ToList();
if (!items.Any())
{
return(null);
}
var title = "債権総額管理表";
var outputName = $"{title}{DateTime.Today:yyyyMMdd}";
var report = new CreditAgingListReport();
report.Name = outputName;
report.SetBasicPageSetting(company.Code, company.Name);
report.DisplayCustomerCode = option.DisplayCustomerCode;
report.ConsiderCustomerGroup = option.ConsiderCustomerGroup;
report.RequireDepartmentTotal = option.RequireDepartmentTotal;
report.RequireStaffTotal = option.RequireStaffTotal;
report.UseMasterStaff = option.UseMasterStaff;
report.lblArrivalDueDate1.Text = option.ArrivalDueDate1;
report.lblArrivalDueDate2.Text = option.ArrivalDueDate2;
report.lblArrivalDueDate3.Text = option.ArrivalDueDate3;
report.lblArrivalDueDate4.Text = option.ArrivalDueDate4;
report.SetPageDataSetting(items);
report.Document.Name = outputName;
report.Run();
return(report.Convert());
}
private void RemovePostiveCustomerGroup(CreditAgingListSearch opt,
List <CreditAgingList> list,
int index,
List <decimal> pcusSub,
List <decimal> deptSub,
List <decimal> stafSub,
List <decimal> grndSub)
{
for (var i = list.Count - 1; i > index; i--)
{
list.RemoveAt(i);
}
if (opt.RequireDepartmentTotal)
{
RemoveSubtotal(deptSub, pcusSub, !opt.UseParentCustomerCredit);
}
if (opt.RequireStaffTotal)
{
RemoveSubtotal(stafSub, pcusSub, !opt.UseParentCustomerCredit);
}
RemoveSubtotal(grndSub, pcusSub, !opt.UseParentCustomerCredit);
list.RemoveAt(index);
}
public Task <IEnumerable <CreditAgingList> > GetAsync(CreditAgingListSearch searchOption,
IProgressNotifier notifier = null,
CancellationToken token = default(CancellationToken))
{
return(dbHelper.ExecuteQueriesAsync(async connection =>
{
await dbHelper.ExecuteAsync(connection, CreateInitializeTempTable(searchOption), searchOption, token);
notifier?.UpdateState();
await dbHelper.ExecuteAsync(connection, CreateInsertReceivable(searchOption), searchOption, token);
notifier?.UpdateState();
await dbHelper.ExecuteAsync(connection, CreateInsertBilling(searchOption), searchOption, token);
notifier?.UpdateState();
if (searchOption.ConsiderReceiptAmount)
{
await dbHelper.ExecuteAsync(connection, CreateInsertReceipt(searchOption), searchOption, token);
notifier?.UpdateState();
}
await dbHelper.ExecuteAsync(connection, CreateInsertMatching(searchOption), searchOption, token);
notifier?.UpdateState();
var items = await dbHelper.QueryAsync <CreditAgingList>(connection, CreateSelectData(searchOption), searchOption, token);
notifier?.UpdateState();
return items;
}));
}
public async Task <ActionResult <IEnumerable <CreditAgingList> > > Get(CreditAgingListSearch option, CancellationToken token) => (await creditAgingListProcessor.GetAsync(option, null, token)).ToArray();
private string CreateInsertBilling(CreditAgingListSearch option)
{
var deptId = option.RequireDepartmentTotal ? "dp.[Id]" : "0";
var stafId = option.RequireStaffTotal ? "st.[Id]" : "0";
var deptAlias = option.UseMasterStaff ? "st" : "b";
var stafAlias = option.UseMasterStaff ? "cs" : "b";
var targetDate = option.UseBilledAt ? "b.[BilledAt]" : "b.[SalesAt]";
var builder = new StringBuilder();
builder.Append($@"
INSERT INTO #WorkCredit
([CompanyId]
,[DepartmentId]
,[StaffId]
,[ParentCustomerId]
,[CustomerId]
,[YearMonth]
,[BillingAmount] )
SELECT b.[CompanyId]
, b.[DepartmentId]
, b.[StaffId]
, COALESCE( csg.[ParentCustomerId], CASE cs.[IsParent] WHEN 1 THEN cs.[Id] ELSE 0 END ) [ParentCustomerId]
, b.[CustomerId]
, b.[YearMonth]
, b.[BillingAmount]
FROM (SELECT b.[CompanyId]
, {deptId} [DepartmentId]
, {stafId} [StaffId]
, b.[CustomerId]
, @ym0t [YearMonth]
, SUM( b.[BillingAmount]
- CASE WHEN b.[DeleteAt] IS NULL THEN 0 ELSE b.[RemainAmount] END ) [BillingAmount]
FROM [dbo].[Billing] b
INNER JOIN [dbo].[Customer] cs ON cs.[Id] = b.[CustomerId]
AND b.[CompanyId] = @CompanyId
AND {targetDate} BETWEEN @ympf AND @ym0t
AND b.[Approved] = 1
AND b.[InputType] <> 3
AND cs.[Code] >= COALESCE(@CustomerCodeFrom, cs.[Code])
AND cs.[Code] <= COALESCE(@CustomerCodeTo , cs.[Code])
AND cs.[ClosingDay] = COALESCE(@ClosingDay, cs.[ClosingDay])
INNER JOIN [dbo].[Staff] st ON st.[Id] = {stafAlias}.[StaffId]
AND st.[Code] >= COALESCE(@StaffCodeFrom, st.[Code])
AND st.[Code] <= COALESCE(@StaffCodeTo , st.[Code])
INNER JOIN [dbo].[Department] dp ON dp.[Id] = {deptAlias}.[DepartmentId]
AND dp.[Code] >= COALESCE(@DepartmentCodeFrom, dp.[Code])
AND dp.[Code] <= COALESCE(@DepartmentCodeTo , dp.[Code])
GROUP BY
b.[CompanyId]");
if (option.RequireDepartmentTotal)
{
builder.Append(@"
, dp.[Id]");
}
if (option.RequireStaffTotal)
{
builder.Append(@"
, st.[Id]");
}
builder.Append($@"
, b.[CustomerId]
) b
INNER JOIN [dbo].[Customer] cs ON cs.[Id] = b.[CustomerId]
LEFT JOIN [dbo].[CustomerGroup] csg ON csg.[ChildCustomerId] = cs.[Id]
");
return(builder.ToString());
}
private string CreateInsertReceipt(CreditAgingListSearch option)
{
var deptId = option.RequireDepartmentTotal ? "dp.[Id]" : "0";
var stafId = option.RequireStaffTotal ? "st.[Id]" : "0";
var deptAlias = option.UseMasterStaff ? "st" : "b";
var stafAlias = option.UseMasterStaff ? "cs" : "b";
var builder = new StringBuilder();
builder.Append($@"
INSERT INTO #WorkReceipt
SELECT r.[Id] [ReceiptId]
, r.[CompanyId]
, {deptId} [DepartmentId]
, {stafId} [StaffId]
, cs.[Id] [CustomerId]
, COUNT(1) [Multiple]
, @ym0t [YearMonth]
, CASE ROW_NUMBER() OVER
(PARTITION BY r.[Id]
ORDER BY");
if (option.RequireDepartmentTotal)
{
builder.Append(" dp.[Id],");
}
if (option.RequireStaffTotal)
{
builder.Append(" st.[Id],");
}
builder.Append($@" cs.[Id] )
WHEN 1 THEN MIN( r.[ReceiptAmount] )
+ SUM(m.[BankTransferFee]
- CASE WHEN m.[TaxDifference] < 0 THEN m.[TaxDifference] ELSE 0 END
+ COALESCE( mbd.[DiscountAmount], 0 ) )
ELSE 0 END [ReceiptAmount]
, SUM( m.[Amount]
+ m.[BankTransferFee]
- CASE WHEN m.[TaxDifference] < 0 THEN m.[TaxDifference] ELSE 0 END
+ COALESCE( mbd.[DiscountAmount], 0 ) ) [MatchingAmount]
FROM [dbo].[Receipt] r
INNER JOIN [dbo].[Matching] m ON r.[Id] = m.[ReceiptId]
AND r.[CompanyId] = @CompanyId
AND r.[RecordedAt] BETWEEN @ympf AND @ym0t
AND r.[Approved] = 1
INNER JOIN [dbo].[Billing] b ON b.[Id] = m.[BillingId]
AND b.[Approved] = 1
AND b.[InputType] <> 3
INNER JOIN [dbo].[Customer] cs ON cs.[Id] = b.[CustomerId]
AND cs.[Code] >= COALESCE(@CustomerCodeFrom, cs.[Code])
AND cs.[Code] <= COALESCE(@CustomerCodeTo , cs.[Code])
AND cs.[ClosingDay] = COALESCE(@ClosingDay, cs.[ClosingDay])
INNER JOIN [dbo].[Staff] st ON st.[Id] = {stafAlias}.[StaffId]
AND st.[Code] >= COALESCE(@StaffCodeFrom, st.[Code])
AND st.[Code] <= COALESCE(@StaffCodeTo , st.[Code])
INNER JOIN [dbo].[Department] dp ON dp.[Id] = {deptAlias}.[DepartmentId]
AND dp.[Code] >= COALESCE(@DepartmentCodeFrom, dp.[Code])
AND dp.[Code] <= COALESCE(@DepartmentCodeTo , dp.[Code])
LEFT JOIN (
SELECT mbd.[MatchingId]
, SUM( mbd.[DiscountAmount] ) [DiscountAmount]
FROM [dbo].[MatchingBillingDiscount] mbd
GROUP BY
mbd.[MatchingId]
) mbd
ON m.[Id] = mbd.[MatchingId]
LEFT JOIN [dbo].[Matching] m2
ON m2.[ReceiptId] = m.[ReceiptId]
AND m2.[MatchingHeaderId] < m.[MatchingHeaderId]
LEFT JOIN [dbo].[Matching] mav
ON mav.[ReceiptId] = r.[OriginalReceiptId]
INNER JOIN [dbo].[Category] rct
ON rct.[Id] = r.[ReceiptCategoryId]
WHERE m2.[Id] IS NULL
AND (
rct.[UseAdvanceReceived] = 0
OR mav.[Id] IS NULL
)
GROUP BY
r.[Id]
, r.[CompanyId]");
if (option.RequireDepartmentTotal)
{
builder.Append(@"
, dp.[Id]");
}
if (option.RequireStaffTotal)
{
builder.Append(@"
, st.[Id]");
}
builder.Append(@"
, cs.[Id];
INSERT INTO #WorkCredit
([CompanyId]
,[DepartmentId]
,[StaffId]
,[ParentCustomerId]
,[CustomerId]
,[YearMonth]
,[ReceiptAmount] )
SELECT wr.[CompanyId]
, wr.[DepartmentId]
, wr.[StaffId]
, COALESCE(csg.[ParentCustomerId], CASE MIN( cs.[IsParent] ) WHEN 1 THEN wr.[CustomerId] ELSE 0 END ) [ParentCustomerId]
, wr.[CustomerId]
, wr.[YearMonth]
, SUM( wr.[ReceiptAmount] ) [ReceiptAmount]
FROM (
SELECT wr.[CompanyId]
, wr.[DepartmentId]
, wr.[StaffId]
, wr.[CustomerId]
, wr.[YearMonth]
, CASE wr.[Multiple] WHEN 1 THEN wr.[ReceiptAmount] ELSE wr.[MatchingAmount] END [ReceiptAmount]
FROM #WorkReceipt wr
UNION ALL
SELECT wr.[CompanyId]
, wr.[DepartmentId]
, wr.[StaffId]
, COALESCE(pcsg.[ParentCustomerId], ccsg.[ParentCustomerId], wr.[CustomerId] ) [CustomerId]
, wr.[YearMonth]
, wr.[ReceiptAmount] - wr.[MatchingAmount]
FROM #WorkReceipt wr
LEFT JOIN (
SELECT DISTINCT csg.[ParentCustomerId]
FROM [dbo].[CustomerGroup] csg
) pcsg
ON pcsg.[ParentCustomerId] = wr.[CustomerId]
LEFT JOIN [dbo].[CustomerGroup] ccsg
ON ccsg.[ChildCustomerId] = wr.[CustomerId]
WHERE wr.[Multiple] > 1
) wr
INNER JOIN [dbo].[Customer] cs ON cs.[Id] = wr.[CustomerId]
LEFT JOIN [dbo].[CustomerGroup] csg ON csg.[ChildCustomerId] = wr.[CustomerId]
GROUP BY
wr.[CompanyId]
, wr.[DepartmentId]
, wr.[StaffId]
, csg.[ParentCustomerId]
, wr.[CustomerId]
, wr.[YearMonth]
");
return(builder.ToString());
}
private bool IsCustomerGroupNegativeCreditBalance(CreditAgingListSearch opt, CreditAgingList detail)
=> (opt.FilterPositiveCreditBalance &&
detail.ParentCustomerId.HasValue &&
detail.IsMinusCreditBalance);
private bool DoSkipWhenSingleCustomerAndPositiveCreditBalance(CreditAgingListSearch opt, CreditAgingList detail)
=> (opt.FilterPositiveCreditBalance &&
!detail.ParentCustomerId.HasValue &&
!detail.IsMinusCreditBalance);
private bool RequireFilterPositiveCreditBalance(CreditAgingListSearch opt,
List <decimal> pcusSub, bool anyBalanceMinus)
=> opt.FilterPositiveCreditBalance &&
(
opt.ConsiderGroupWithCalculate && pcusSub[(int)SubtotalField.CreditBalance] > 0M ||
!opt.ConsiderGroupWithCalculate && !anyBalanceMinus);
public async Task <IEnumerable <CreditAgingList> > GetAsync(CreditAgingListSearch SearchOption,
IProgressNotifier notifier = null, CancellationToken token = default(CancellationToken))
{
var opt = SearchOption;
opt.InitializeYearMonthValue();
var details = await creditAgingListQueryProcessor.GetAsync(opt, notifier, token);
var unit = SearchOption.UnitPrice;
// key break subtotal
const int staffTotal = 1;
const int departmentTotal = 2;
const int grandTotal = 3;
var list = new List <CreditAgingList>();
var pcusIndexBuf = (int?)null;
var stafSub = GetSubtotalList();
var deptSub = GetSubtotalList();
var pcusSub = GetSubtotalList();
var grndSub = GetSubtotalList();
var anyBalanceMinus = false;
CreditAgingList detailBuf = null;
foreach (var detail in details)
{
detail.UnsettledRemain = detail.ReceivableAmount;
detail.BillingRemain = detail.BillingAmount
- (opt.ConsiderReceiptAmount ? detail.ReceiptAmount : detail.MatchingAmount);
detail.CreditAmount = detail.ReceivableAmount + detail.BillingRemain;
detail.ArrivalDueDate1 = detail.ReceivableAmount_1;
detail.ArrivalDueDate2 = detail.ReceivableAmount_2;
detail.ArrivalDueDate3 = detail.ReceivableAmount_3;
detail.ArrivalDueDate4 = detail.ReceivableAmount_4;
var doCalcCreditLimitSingleLine
= !opt.UseParentCustomerCredit ||
!detail.ParentCustomerId.HasValue;
if (!opt.CalculateCreditLimitRegistered || detail.CreditLimit != 0M)
{
detail.CreditBalance
= (doCalcCreditLimitSingleLine ? detail.CreditLimit : 0M)
- detail.CreditAmount;
}
var skip = opt.ConsiderCustomerGroup &&
opt.ConsiderReceiptAmount &&
detail.ParentCustomerId == detail.CustomerId &&
detail.CreditAmount == 0M &&
detail.CreditLimit == 0M;
if (detail.CreditAmount == 0M &&
detail.UnsettledRemain == 0M)
{
continue;
}
if (pcusIndexBuf.HasValue &&
RequireCustomerGroupSubtotal(opt, detailBuf, detail))
{
if (opt.UseParentCustomerCredit)
{
pcusSub[(int)SubtotalField.CreditBalance] += pcusSub[(int)SubtotalField.CreditLimit];
}
if (RequireFilterPositiveCreditBalance(opt, pcusSub, anyBalanceMinus))
{
RemovePostiveCustomerGroup(opt, list, pcusIndexBuf.Value, pcusSub, deptSub, stafSub, grndSub);
}
else
{
SetSubtotal(list[pcusIndexBuf.Value], pcusSub, unit);
if (opt.UseParentCustomerCredit)
{
if (opt.RequireStaffTotal)
{
AddCreditLimitSubtotal(stafSub, list[pcusIndexBuf.Value]);
}
if (opt.RequireDepartmentTotal)
{
AddCreditLimitSubtotal(deptSub, list[pcusIndexBuf.Value]);
}
AddCreditLimitSubtotal(grndSub, list[pcusIndexBuf.Value]);
}
}
ResetSubtotal(pcusSub);
pcusIndexBuf = null;
}
if (RequireStaffSubtotal(opt, detailBuf, detail))
{
if (list.Any())
{
list.Add(GetSubtotal(detailBuf, stafSub, staffTotal, unit));
}
ResetSubtotal(stafSub);
}
if (RequireDepartmentSubtotal(opt, detailBuf, detail))
{
if (list.Any())
{
list.Add(GetSubtotal(detailBuf, deptSub, departmentTotal, unit));
}
ResetSubtotal(deptSub);
}
if (opt.ConsiderCustomerGroup &&
detail.ParentCustomerId.HasValue &&
(
detailBuf?.ParentCustomerId != detail.ParentCustomerId ||
detailBuf?.StaffId != detail.StaffId ||
detailBuf?.DepartmentId != detail.DepartmentId
))
{
anyBalanceMinus = false;
var parent = GetSubtotal(detail, null, 0, unit);
parent.ParentCustomerId = detail.ParentCustomerId;
parent.CustomerId = detail.ParentCustomerId.Value;
parent.CustomerCode = detail.ParentCustomerCode;
parent.CustomerName = detail.ParentCustomerName;
parent.ParentCustomerFlag = 1;
parent.CollectCategory = detail.ParentCollectCategoryName;
list.Add(parent);
pcusIndexBuf = list.IndexOf(parent);
}
if (DoSkipWhenSingleCustomerAndPositiveCreditBalance(opt, detail))
{
continue;
}
if (IsCustomerGroupNegativeCreditBalance(opt, detail))
{
anyBalanceMinus = true;
}
if (opt.ConsiderCustomerGroup && pcusIndexBuf.HasValue)
{
AddSubtotal(pcusSub, detail, !opt.UseParentCustomerCredit);
if (opt.UseParentCustomerCredit)
{
pcusSub[(int)SubtotalField.CreditLimit] = detail.CreditLimit ?? 0M;
pcusSub[(int)SubtotalField.CreditBalance] += detail.CreditBalance ?? 0M;
detail.CreditLimit = null;
detail.CreditBalance = null;
}
}
if (opt.RequireStaffTotal)
{
AddSubtotal(stafSub, detail, doCalcCreditLimitSingleLine);
}
if (opt.RequireDepartmentTotal)
{
AddSubtotal(deptSub, detail, doCalcCreditLimitSingleLine);
}
AddSubtotal(grndSub, detail, doCalcCreditLimitSingleLine);
if (!skip)
{
list.Add(detail);
TruncateValue(detail, unit);
}
detailBuf = detail;
}
if (pcusIndexBuf.HasValue &&
RequireCustomerGroupSubtotal(opt, detailBuf, null))
{
if (opt.UseParentCustomerCredit)
{
pcusSub[(int)SubtotalField.CreditBalance] += pcusSub[(int)SubtotalField.CreditLimit];
}
if (RequireFilterPositiveCreditBalance(opt, pcusSub, anyBalanceMinus))
{
RemovePostiveCustomerGroup(opt, list, pcusIndexBuf.Value, pcusSub, deptSub, stafSub, grndSub);
}
else
{
SetSubtotal(list[pcusIndexBuf.Value], pcusSub, unit);
if (opt.UseParentCustomerCredit)
{
if (opt.RequireStaffTotal)
{
AddCreditLimitSubtotal(stafSub, list[pcusIndexBuf.Value]);
}
if (opt.RequireDepartmentTotal)
{
AddCreditLimitSubtotal(deptSub, list[pcusIndexBuf.Value]);
}
AddCreditLimitSubtotal(grndSub, list[pcusIndexBuf.Value]);
}
}
}
if (RequireStaffSubtotal(opt, detailBuf, null) && list.Any())
{
list.Add(GetSubtotal(detailBuf, stafSub, staffTotal, unit));
}
if (RequireDepartmentSubtotal(opt, detailBuf, null) && list.Any())
{
list.Add(GetSubtotal(detailBuf, deptSub, departmentTotal, unit));
}
if (detailBuf != null && list.Any())
{
list.Add(GetSubtotal(detailBuf, grndSub, grandTotal, unit));
}
notifier?.UpdateState();
return(list);
}
private string CreateInsertMatching(CreditAgingListSearch option)
{
var deptId = option.RequireDepartmentTotal ? "dp.[Id]" : "0";
var stafId = option.RequireStaffTotal ? "st.[Id]" : "0";
var deptAlias = option.UseMasterStaff ? "st" : "b";
var stafAlias = option.UseMasterStaff ? "cs" : "b";
var targetDate = option.UseBilledAt ? "b.[BilledAt]" : "b.[SalesAt]";
var builder = new StringBuilder();
builder.Append($@"
INSERT INTO #WorkCredit
([CompanyId]
,[DepartmentId]
,[StaffId]
,[ParentCustomerId]
,[CustomerId]
,[YearMonth]
,[MatchingAmount] )
SELECT m.[CompanyId]
, m.[DepartmentId]
, m.[StaffId]
, COALESCE(csg.[ParentCustomerId], CASE cs.[IsParent] WHEN 1 THEN cs.[Id] ELSE 0 END ) [ParentCustomerId]
, m.[CustomerId]
, m.[YearMonth]
, m.[MatchingAmount]
FROM (
SELECT b.[CompanyId]
, {deptId} [DepartmentId]
, {stafId} [StaffId]
, b.[CustomerId]
, @ym0t [YearMonth]
, SUM( m.[Amount]
+ m.[BankTransferFee]
- CASE WHEN m.[TaxDifference] < 0 THEN m.[TaxDifference] ELSE 0 END
+ COALESCE( mbd.[DiscountAmount], 0 ) ) [MatchingAmount]
FROM [dbo].[Matching] m
INNER JOIN [dbo].[Receipt] r ON r.[Id] = m.[ReceiptId]
AND r.[CompanyId] = @CompanyId
AND r.[Approved] = 1
INNER JOIN [dbo].[Billing] b ON b.[Id] = m.[BillingId]
AND b.[Approved] = 1
AND b.[InputType] <> 3
AND CASE WHEN r.[RecordedAt] < {targetDate} THEN {targetDate} ELSE r.[RecordedAt] END
BETWEEN @ympf AND @ym0t
INNER JOIN [dbo].[Customer] cs ON cs.[Id] = b.[CustomerId]
AND cs.[Code] >= COALESCE(@CustomerCodeFrom , cs.[Code])
AND cs.[Code] <= COALESCE(@CustomerCodeTo , cs.[Code])
AND cs.[ClosingDay] = COALESCE(@ClosingDay, cs.[ClosingDay])
INNER JOIN [dbo].[Staff] st ON st.[Id] = {stafAlias}.[StaffId]
AND st.[Code] >= COALESCE(@StaffCodeFrom , st.[Code])
AND st.[Code] <= COALESCE(@StaffCodeTo , st.[Code])
INNER JOIN [dbo].[Department] dp ON dp.[Id] = {deptAlias}.[DepartmentId]
AND dp.[Code] >= COALESCE(@DepartmentCodeFrom , dp.[Code])
AND dp.[Code] <= COALESCE(@DepartmentCodeTo , dp.[Code])
LEFT JOIN (
SELECT mbd.[MatchingId]
, SUM( mbd.[DiscountAmount] ) [DiscountAmount]
FROM [dbo].[MatchingBillingDiscount] mbd
GROUP BY
mbd.[MatchingId]
) mbd
ON m.[Id] = mbd.[MatchingId]
GROUP BY
b.[CompanyId]");
if (option.RequireDepartmentTotal)
{
builder.Append(@"
, dp.[Id]");
}
if (option.RequireStaffTotal)
{
builder.Append(@"
, st.[Id]");
}
builder.Append($@"
, b.[CustomerId]
) m
INNER JOIN [dbo].[Customer] cs ON cs.[Id] = m.[CustomerId]
LEFT JOIN [dbo].[CustomerGroup] csg ON cs.[Id] = csg.[ChildCustomerId]
");
return(builder.ToString());
}
public async Task <ActionResult <IEnumerable <CreditAgingList> > > GetR(CreditAgingListSearch option)
=> await hubContext.DoAsync(option.ConnectionId, async (notifier, token)
=> (await creditAgingListProcessor.GetAsync(option, notifier, token)).ToArray());
private string CreateInsertReceivable(CreditAgingListSearch option)
{
var deptId = option.RequireDepartmentTotal ? "dp.[Id]" : "0";
var stafId = option.RequireStaffTotal ? "st.[Id]" : "0";
var deptAlias = option.UseMasterStaff ? "st" : "b";
var stafAlias = option.UseMasterStaff ? "cs" : "b";
var builder = new StringBuilder();
builder.Append($@"
INSERT INTO #WorkCredit
([CompanyId]
,[DepartmentId]
,[StaffId]
,[ParentCustomerId]
,[CustomerId]
,[YearMonth]
,[ReceivableAmount] )
SELECT u.[CompanyId]
, u.[DepartmentId]
, u.[StaffId]
, COALESCE( csg.[ParentCustomerId] , CASE MIN( cs.[IsParent] ) WHEN 1 THEN u.[CustomerId] ELSE 0 END ) [ParentCustomerId]
, u.[CustomerId]
, u.[YearMonth]
, SUM( u.[ReceivableAmount] ) [ReceivableAmount]
FROM (
SELECT b.[CompanyId]
, {deptId} [DepartmentId]
, {stafId} [StaffId]
, b.[CustomerId]
, CASE WHEN r.[DueAt] BETWEEN @ym1f AND @ym1t THEN @ym1t
WHEN r.[DueAt] BETWEEN @ym2f AND @ym2t THEN @ym2t
WHEN r.[DueAt] BETWEEN @ym3f AND @ym3t THEN @ym3t
WHEN @ym4f <= r.[DueAt] THEN @ym4t
END [YearMonth]
, m.[Amount] [ReceivableAmount]
FROM [dbo].[Matching] m
INNER JOIN [dbo].[Receipt] r ON r.[Id] = m.[ReceiptId]
AND r.[Approved] = 1
AND r.[CompanyId] = @CompanyId
AND r.[RecordedAt] <= @ym0t
AND r.[DueAt] >= @ym1f
INNER JOIN [dbo].[Billing] b ON b.[Id] = m.[BillingId]
AND b.[Approved] = 1
AND b.[InputType] <> 3
INNER JOIN [dbo].[Customer] cs ON cs.[Id] = b.[CustomerId]
AND cs.[Code] >= COALESCE(@CustomerCodeFrom, cs.[Code])
AND cs.[Code] <= COALESCE(@CustomerCodeTo , cs.[Code])
AND cs.[ClosingDay] = COALESCE(@ClosingDay, cs.[ClosingDay])
INNER JOIN [dbo].[Staff] st ON st.[Id] = {stafAlias}.[StaffId]
AND st.[Code] >= COALESCE(@StaffCodeFrom, st.[Code])
AND st.[Code] <= COALESCE(@StaffCodeTo , st.[Code])
INNER JOIN [dbo].[Department] dp ON dp.[Id] = {deptAlias}.[DepartmentId]
AND dp.[Code] >= COALESCE(@DepartmentCodeFrom, dp.[Code])
AND dp.[Code] <= COALESCE(@DepartmentCodeTo , dp.[Code])
INNER JOIN [dbo].[Category] rct ON rct.[Id] = r.[ReceiptCategoryId]
AND (
rct.[UseLimitDate] = 1
OR rct.[UseAdvanceReceived] = 1
AND EXISTS (
SELECT 1
FROM [dbo].[Receipt] br
INNER JOIN [dbo].[Category] brct
ON brct.[Id] = br.[ReceiptCategoryId]
AND br.[Id] = r.[OriginalReceiptId]
AND brct.[UseLimitDate] = 1 )
)
UNION ALL
SELECT r.[CompanyId]
, {deptId} [DepartmentId]
, {stafId} [StaffId]
, cs.[Id] [CustomerId]
, CASE WHEN r.[DueAt] BETWEEN @ym1f AND @ym1t THEN @ym1t
WHEN r.[DueAt] BETWEEN @ym2f AND @ym2t THEN @ym2t
WHEN r.[DueAt] BETWEEN @ym3f AND @ym3t THEN @ym3t
WHEN @ym4f <= r.[DueAt] THEN @ym4t
END [YearMonth]
, r.[RemainAmount] [ReceivableAmount]
FROM [dbo].[Receipt] r
INNER JOIN [dbo].[Customer] cs ON cs.[Id] = r.[CustomerId]
AND r.[CompanyId] = @CompanyId
AND r.[RecordedAt] <= @ym0t
AND r.[DueAt] >= @ym1f
AND r.[Approved] = 1
AND r.[DeleteAt] IS NULL
AND cs.[Code] >= @CustomerCodeFrom
AND cs.[Code] <= @CustomerCodeTo
AND cs.[ClosingDay] = COALESCE(@ClosingDay, cs.[ClosingDay])
INNER JOIN [dbo].[Staff] st ON st.[Id] = cs.[StaffId]
AND st.[Code] >= @StaffCodeFrom
AND st.[Code] <= @StaffCodeTo
INNER JOIN [dbo].[Department] dp ON dp.[Id] = st.[DepartmentId]
AND dp.[Code] >= @DepartmentCodeFrom
AND dp.[Code] <= @DepartmentCodeTo
INNER JOIN (
SELECT r.[Id] [ReceiptId]
, MAX(
CASE WHEN r.[CustomerId] = b.[CustomerId]
AND dp.[Id] = b.[DepartmentId]
AND st.[Id] = b.[StaffId]
THEN 0 ELSE 1 END
) [Multiple]
FROM [dbo].[Receipt] r
INNER JOIN [dbo].[Matching] m ON r.[Id] = m.[ReceiptId]
AND r.[CompanyId] = @CompanyId
AND r.[RecordedAt] <= @ym0t
AND r.[DueAt] >= @ym1f
AND r.[Approved] = 1
AND r.[AssignmentFlag] < 2
AND r.[DeleteAt] IS NULL
INNER JOIN [dbo].[Billing] b ON b.[Id] = m.[BillingId]
INNER JOIN [dbo].[Customer] cs ON cs.[Id] = r.[CustomerId]
AND cs.[Code] >= COALESCE(@CustomerCodeFrom , cs.[Code])
AND cs.[Code] <= COALESCE(@CustomerCodeTo , cs.[Code])
AND cs.[ClosingDay] = COALESCE(@ClosingDay, cs.[ClosingDay])
INNER JOIN [dbo].[Staff] st ON st.[Id] = cs.[StaffId]
AND st.[Code] >= COALESCE(@StaffCodeFrom , st.[Code])
AND st.[Code] <= COALESCE(@StaffCodeTo , st.[Code])
INNER JOIN [dbo].[Department] dp ON dp.[Id] = st.[DepartmentId]
AND dp.[Code] >= COALESCE(@DepartmentCodeFrom , dp.[Code])
AND dp.[Code] <= COALESCE(@DepartmentCodeTo , dp.[Code])
GROUP BY
r.[Id]
) m
ON r.[Id] = m.[ReceiptId]
INNER JOIN [dbo].[Category] rct ON rct.[Id] = r.[ReceiptCategoryId]
AND ( rct.[UseLimitDate] = 1
OR rct.[UseAdvanceReceived] = 1
AND EXISTS (
SELECT 1
FROM [dbo].[Receipt] br
INNER JOIN [dbo].[Category] brct
ON brct.[Id] = br.[ReceiptCategoryId]
AND br.[Id] = r.[OriginalReceiptId]
AND brct.[UseLimitDate] = 1 )
)
UNION ALL
SELECT b.[CompanyId]
, {deptId} [DepartmentId]
, {stafId} [StaffId]
, b.[CustomerId]
, @ym0t [YearMonth]
, b.[RemainAmount] [ReceivableAmount]
FROM [dbo].[Billing] b
INNER JOIN [dbo].[Customer] cs ON cs.[Id] = b.[CustomerId]
AND b.[CompanyId] = @CompanyId
AND b.[Approved] = 1
AND b.[InputType] = 3
AND b.[AssignmentFlag] < 2
AND b.[DeleteAt] IS NULL
AND b.[DueAt] <= @ym0t
AND cs.[Code] >= COALESCE(@CustomerCodeFrom, cs.[Code])
AND cs.[Code] <= COALESCE(@CustomerCodeTo , cs.[Code])
AND cs.[ClosingDay] = COALESCE(@ClosingDay, cs.[ClosingDay])
INNER JOIN [dbo].[Staff] st ON st.[Id] = {stafAlias}.[StaffId]
AND st.[Code] >= COALESCE(@StaffCodeFrom, st.[Code])
AND st.[Code] <= COALESCE(@StaffCodeTo , st.[Code])
INNER JOIN [dbo].[Department] dp ON dp.[Id] = {deptAlias}.[DepartmentId]
AND dp.[Code] >= COALESCE(@DepartmentCodeFrom, dp.[Code])
AND dp.[Code] <= COALESCE(@DepartmentCodeTo , dp.[Code])
) u
INNER JOIN [dbo].[Customer] cs ON cs.[Id] = u.[CustomerId]
LEFT JOIN [dbo].[CustomerGroup] csg ON csg.[ChildCustomerId] = cs.[Id]
GROUP BY
u.[CompanyId]
, u.[DepartmentId]
, u.[StaffId]
, u.[CustomerId]
, csg.[ParentCustomerId]
, u.[YearMonth]
");
return(builder.ToString());
}
private string CreateSelectData(CreditAgingListSearch option)
{
var builder = new StringBuilder();
var requireParentLimit = option.ConsiderCustomerGroup && option.UseParentCustomerCredit;
builder.Append($@"
SELECT wc.*
, cs.[Code] [CustomerCode]
, COALESCE(dp.[Code] , N'') [DepartmentCode]
, COALESCE(st.[Code] , N'') [StaffCode]
, COALESCE(pcs.[Code] , N'') [ParentCustomerCode]
, COALESCE(cs.[Name] , N'得意先コード不明') [CustomerName]
, COALESCE(dp.[Name] , N'請求部門コード不明') [DepartmentName]
, COALESCE(st.[Name] , N'担当者コード不明') [StaffName]
, COALESCE(pcs.[Name] , N'') [ParentCustomerName]
, cct.[Name] [CollectCategory]
, COALESCE(pcct.[Name], N'') [ParentCollectCategoryName]
, {(requireParentLimit ? "COALESCE(pcs.[CreditLimit] , cs.[CreditLimit])" : "cs.[CreditLimit]")} * 10000 [CreditLimit]
FROM (
SELECT wc.[CompanyId]
, wc.[DepartmentId]
, wc.[StaffId]
, CASE WHEN wc2.[HasAnyChildren] = 0 OR wc.[ParentCustomerId] = 0 THEN NULL ELSE wc.[ParentCustomerId] END [ParentCustomerId]
, wc.[CustomerId]
, wc.[BillingAmount]
, wc.[ReceiptAmount]
, wc.[MatchingAmount]
, wc.[ReceivableAmount_0]
, wc.[ReceivableAmount_1]
, wc.[ReceivableAmount_2]
, wc.[ReceivableAmount_3]
, wc.[ReceivableAmount_4]
FROM (
SELECT wc.[CompanyId]
, wc.[DepartmentId]
, wc.[StaffId]
, wc.[ParentCustomerId]
, wc.[CustomerId]
, SUM( CASE wc.[YearMonth] WHEN @ym0t THEN wc.[BillingAmount] ELSE 0 END ) [BillingAmount]
, SUM( CASE wc.[YearMonth] WHEN @ym0t THEN wc.[ReceiptAmount] ELSE 0 END ) [ReceiptAmount]
, SUM( CASE wc.[YearMonth] WHEN @ym0t THEN wc.[MatchingAmount] ELSE 0 END ) [MatchingAmount]
, SUM( CASE wc.[YearMonth] WHEN @ym0t THEN wc.[ReceivableAmount] ELSE 0 END ) [ReceivableAmount_0]
, SUM( CASE wc.[YearMonth] WHEN @ym1t THEN wc.[ReceivableAmount] ELSE 0 END ) [ReceivableAmount_1]
, SUM( CASE wc.[YearMonth] WHEN @ym2t THEN wc.[ReceivableAmount] ELSE 0 END ) [ReceivableAmount_2]
, SUM( CASE wc.[YearMonth] WHEN @ym3t THEN wc.[ReceivableAmount] ELSE 0 END ) [ReceivableAmount_3]
, SUM( CASE wc.[YearMonth] WHEN @ym4t THEN wc.[ReceivableAmount] ELSE 0 END ) [ReceivableAmount_4]
FROM #WorkCredit wc
GROUP BY
wc.[CompanyId]
, wc.[DepartmentId]
, wc.[StaffId]
, wc.[ParentCustomerId]
, wc.[CustomerId]
) wc
INNER JOIN (
SELECT wc.[CompanyId]
, wc.[DepartmentId]
, wc.[StaffId]
, wc.[ParentCustomerId]
, SUM( CASE WHEN wc.[ParentCustomerId] = 0 OR wc.[ParentCustomerId] = wc.[CustomerId] THEN 0 ELSE 1 END ) [HasAnyChildren]
FROM #WorkCredit wc
GROUP BY
wc.[CompanyId]
, wc.[DepartmentId]
, wc.[StaffId]
, wc.[ParentCustomerId]
) wc2
ON wc.[CompanyId] = wc2.[CompanyId]
AND wc.[DepartmentId] = wc2.[DepartmentId]
AND wc.[StaffId] = wc2.[StaffId]
AND wc.[ParentCustomerId] = wc2.[ParentCustomerId]
) wc
INNER JOIN [dbo].[Customer] cs ON cs.[Id] = wc.[CustomerId]
INNER JOIN [dbo].[Category] cct ON cct.[Id] = cs.[CollectCategoryId]
LEFT JOIN [dbo].[Department] dp ON dp.[Id] = wc.[DepartmentId]
LEFT JOIN [dbo].[Staff] st ON st.[Id] = wc.[StaffId]
LEFT JOIN [dbo].[Customer] pcs ON pcs.[Id] = wc.[ParentCustomerId]
LEFT JOIN [dbo].[Category] pcct ON pcct.[Id] = pcs.[CollectCategoryId]
ORDER BY
wc.[CompanyId]
, dp.[Code]
, st.[Code]
, pcs.[Code]
, cs.[Code]
");
return(builder.ToString());
}
