public async Task <byte[]> GetAsync(BillingAgingListSearch option, CancellationToken token = default(CancellationToken))
{
var companyTask = companyQueryProcessor.GetAsync(new CompanySearch {
Id = option.CompanyId,
}, token);
var columnTask = columnNameSettingQueryProcessor.GetAsync(new ColumnNameSetting {
CompanyId = option.CompanyId,
TableName = nameof(Billing),
ColumnName = nameof(Billing.Note1),
}, token);
var loadTask = billingAgingListQueryProcessor.GetDetailsAsync(option, token);
await Task.WhenAll(companyTask, columnTask, loadTask);
var items = loadTask.Result.ToList();
if (!items.Any())
{
return(null);
}
var company = companyTask.Result.First();
var column = columnTask.Result.First();
var title = $"請求残高年齢表（明細）{ DateTime.Today:yyyyMMdd}";
var report = new BillingAgingListDetailSectionReport();
report.SetBasicPageSetting(company.Code, company.Name);
report.Name = title;
report.SetData(items, option.Precision, column);
report.Run();
return(report.Convert());
}
private void SetDetail(SheetData data, BillingAgingListSearch option, BillingAgingList item, ref uint rowIndex)
{
rowIndex++;
var isChildCustomer = item.ParentCustomerFlag != 1 && item.ParentCustomerId.HasValue && option.ConsiderCustomerGroup;
var isParentCustomer = item.ParentCustomerFlag == 1 && option.ConsiderCustomerGroup;
var customerInfo = string.Concat(
isChildCustomer ? " " : "",
DisplayCustomerCode ? $"{item.CustomerCode} " : "",
item.CustomerName);
data.SetText(rowIndex, "A", customerInfo, cfidxDA);
var caption = isParentCustomer ? "小計" : "";
data.SetText(rowIndex, "B", caption, cfidxDB);
data.SetNumber(rowIndex, "C", item.LastMonthRemain, cfidxDCJ);
data.SetNumber(rowIndex, "D", item.CurrentMonthSales, cfidxDCJ);
data.SetNumber(rowIndex, "E", item.CurrentMonthReceipt, cfidxDCJ);
data.SetNumber(rowIndex, "F", item.CurrentMonthMatching, cfidxDCJ);
data.SetNumber(rowIndex, "G", item.CurrentMonthRemain, cfidxDCJ);
data.SetNumber(rowIndex, "H", item.MonthlyRemain0, cfidxDCJ);
data.SetNumber(rowIndex, "I", item.MonthlyRemain1, cfidxDCJ);
data.SetNumber(rowIndex, "J", item.MonthlyRemain2, cfidxDCJ);
data.SetNumber(rowIndex, "K", item.MonthlyRemain3, cfidxDK);
}
private void SetTitle(SheetData data, BillingAgingListSearch option, BillingAgingList item, ref uint rowIndex)
{
rowIndex++;
data.SetText(rowIndex, "A", "請求残高年齢表");
data.SetStyle(GetCellReference(rowIndex, "A", columnNameEnd: "I"), cfidxTitle);
if (option.RequireDepartmentSubtotal)
{
rowIndex++;
data.SetText(rowIndex, "A", $"請求部門コード：{item.DepartmentCode} {item.DepartmentName}");
}
if (option.RequireStaffSubtotal)
{
rowIndex++;
data.SetText(rowIndex, "A", $"担当者コード ：{item.StaffCode} {item.StaffName}");
}
rowIndex++;
rowIndex++;
data.SetText(rowIndex, "A", "得意先", cfidxHA);
data.SetStyle(rowIndex, "B", cfidxHB);
data.SetText(rowIndex, "C", "前月請求残", cfidxHCI);
data.SetText(rowIndex, "D", "当月売上高", cfidxHCI);
data.SetText(rowIndex, "E", "当月消込", cfidxHCI);
data.SetText(rowIndex, "F", "当月請求残", cfidxHCI);
data.SetText(rowIndex, "G", option.MonthlyRemain0, cfidxHCI);
data.SetText(rowIndex, "H", option.MonthlyRemain1, cfidxHCI);
data.SetText(rowIndex, "I", option.MonthlyRemain2, cfidxHCI);
data.SetText(rowIndex, "J", option.MonthlyRemain3, cfidxHJ);
}
private bool RequireDepartmentSubtotal(BillingAgingListSearch opt, BillingAgingList detailBuf, BillingAgingList detail)
{
return(opt.RequireDepartmentSubtotal &&
detailBuf != null &&
(detail == null ||
(opt.RequireDepartmentSubtotal && detailBuf.DepartmentId != detail.DepartmentId)
));
}
private bool RequireBreak(BillingAgingListSearch opt, BillingAgingList item, BillingAgingList itemNext, uint rowIndex, uint lastBraekRowIndex)
{
const uint MaxRowCount = 44u;
return(itemNext != null &&
(
opt.RequireStaffSubtotal && item.RecordType == 1 && itemNext.RecordType == 0 ||
opt.RequireDepartmentSubtotal && item.RecordType == 2 && itemNext.RecordType == 0 ||
(rowIndex - lastBraekRowIndex) >= MaxRowCount
));
}
private bool RequireCustomerGroupSubtotal(BillingAgingListSearch opt, BillingAgingList detailBuf, BillingAgingList detail)
{
return(opt.ConsiderCustomerGroup &&
detailBuf != null &&
detailBuf.ParentCustomerId.HasValue &&
(detail == null ||
(detailBuf.ParentCustomerId != detail.ParentCustomerId ||
opt.RequireStaffSubtotal && detailBuf.StaffId != detail.StaffId ||
opt.RequireDepartmentSubtotal && detailBuf.DepartmentId != detail.DepartmentId)
));
}
public async Task <BillingAgingListsResult> GetAsync(string SessionKey, BillingAgingListSearch searchOption, string connectionId)
{
return(await authorizationProcessor.DoAuthorizeAsync(SessionKey, async token =>
{
var notifier = hubContext.CreateNotifier(connectionId);
var result = await billingAgingListProcessor.GetAsync(searchOption, notifier, token);
return new BillingAgingListsResult
{
ProcessResult = new ProcessResult {
Result = true
},
BillingAgingLists = new List <BillingAgingList>(result),
};
}, logger, connectionId));
}
public Task <IEnumerable <BillingAgingList> > GetAsync(BillingAgingListSearch searchOption,
IProgressNotifier notifier = null, CancellationToken token = default(CancellationToken))
{
return(dbHelper.ExecuteQueriesAsync(async connection =>
{
await dbHelper.ExecuteAsync(connection, GetQueryInitializeTempTable(searchOption), searchOption, token);
notifier?.UpdateState();
await dbHelper.ExecuteAsync(connection, GetQueryInsertBillingReamin(searchOption), searchOption, token);
notifier?.UpdateState();
if (searchOption.BillingRemainType > 0)
{
await dbHelper.ExecuteAsync(connection, GetQueryInsertReceiptRemain(searchOption), searchOption, token);
notifier?.UpdateState();
}
await dbHelper.ExecuteAsync(connection, GetQueryInsertMatchingReamin(searchOption), searchOption, token);
notifier?.UpdateState();
var items = await dbHelper.QueryAsync <BillingAgingList>(connection, GetQuerySelectBillingAgingList(searchOption), searchOption, token);
notifier?.UpdateState();
return items;
}));
}
private void SetSubtotal(SheetData data, BillingAgingListSearch opt, BillingAgingList item, ref uint rowIndex)
{
rowIndex++;
// 合計行
var caption
= item.RecordType == 1 ? $"{item.StaffCode} {item.StaffName} 計"
: item.RecordType == 2 ? $"{item.DepartmentCode} {item.DepartmentName} 計"
: item.RecordType == 3 ? (UseForeignCurrency ? "通貨計" : "総合計")
: string.Empty;
data.SetText(rowIndex, "A", caption, cfidxSA);
data.SetStyle(rowIndex, "B", cfidxSB);
data.SetNumber(rowIndex, "C", item.LastMonthRemain, cfidxSCI);
data.SetNumber(rowIndex, "D", item.CurrentMonthSales, cfidxSCI);
data.SetNumber(rowIndex, "E", item.CurrentMonthMatching, cfidxSCI);
data.SetNumber(rowIndex, "F", item.CurrentMonthRemain, cfidxSCI);
data.SetNumber(rowIndex, "G", item.MonthlyRemain0, cfidxSCI);
data.SetNumber(rowIndex, "H", item.MonthlyRemain1, cfidxSCI);
data.SetNumber(rowIndex, "I", item.MonthlyRemain2, cfidxSCI);
data.SetNumber(rowIndex, "J", item.MonthlyRemain3, cfidxSJ);
}
public async Task <IEnumerable <BillingAgingListDetail> > GetDetailsAsync(BillingAgingListSearch option, CancellationToken token = default(CancellationToken))
{
option.InitializeYearMonthConditions();
return(await billingAgingListQueryProcessor.GetDetailsAsync(option, token));
}
public async Task <IActionResult> GetSpreadsheet(BillingAgingListSearch option, CancellationToken token)
{
var content = await billingAgingListSpreadsheetProcessor.GetAsync(option, null, token);
return(File(content, SpreadsheetContentType, "billing-aging-list.xlsx"));
}
public async Task <byte[]> GetAsync(BillingAgingListSearch option, IProgressNotifier notifier = null, CancellationToken token = default(CancellationToken))
{
var companyTask = companyQueryProcessor.GetAsync(new CompanySearch {
Id = option.CompanyId,
}, token);
var appConTask = applicationControlGetByCompanyQueryProcessor.GetAsync(option.CompanyId, token);
var settingTask = reportSettingQueryProcessor.GetAsync(option.CompanyId, ReportId, token);
var loadTask = billingAgingListProcessor.GetAsync(option, notifier, token);
await Task.WhenAll(companyTask, appConTask, settingTask, loadTask);
var company = companyTask.Result.First();
var appCon = appConTask.Result;
var settings = settingTask.Result.ToList();
var items = loadTask.Result.ToList();
if (!items.Any())
{
return(null);
}
var remianType = settings.GetReportSetting <ReportAdvanceReceivedType>(BillingRemainType);
var displayCode = settings.GetReportSetting <ReportDoOrNot>(DisplayCustomerCode) == ReportDoOrNot.Do;
var title = "請求残高年齢表";
var outputName = $"{title}{DateTime.Today:yyyyMMdd}";
GrapeCity.ActiveReports.SectionReport report;
if (remianType == ReportAdvanceReceivedType.UseMatchingAmount)
{
var reportTemp = new BillingAgingListSectionReport1();
reportTemp.ConsiderCustomerGroup = option.ConsiderCustomerGroup;
reportTemp.DisplayCustomerCode = displayCode;
reportTemp.RequireStaffSubtotal = option.RequireStaffSubtotal;
reportTemp.RequireDepartmentSubtotal = option.RequireDepartmentSubtotal;
reportTemp.SetBasicPageSetting(company.Code, company.Name);
reportTemp.lblMonthlyRemain0.Text = option.MonthlyRemain0;
reportTemp.lblMonthlyRemain1.Text = option.MonthlyRemain1;
reportTemp.lblMonthlyRemain2.Text = option.MonthlyRemain2;
reportTemp.lblMonthlyRemain3.Text = option.MonthlyRemain3;
reportTemp.SetData(items, option.Precision, appCon.UseForeignCurrency);
report = reportTemp;
}
else
{
var reportTemp = new BillingAgingListSectionReport();
reportTemp.ConsiderCustomerGroup = option.ConsiderCustomerGroup;
reportTemp.DisplayCutsomerCode = displayCode; // typo
reportTemp.RequireStaffSubtotal = option.RequireStaffSubtotal;
reportTemp.RequireDepartmentSubtotal = option.RequireDepartmentSubtotal;
reportTemp.SetBasicPageSetting(company.Code, company.Name);
reportTemp.lblMonthlyRemain0.Text = option.MonthlyRemain0;
reportTemp.lblMonthlyRemain1.Text = option.MonthlyRemain1;
reportTemp.lblMonthlyRemain2.Text = option.MonthlyRemain2;
reportTemp.lblMonthlyRemain3.Text = option.MonthlyRemain3;
reportTemp.SetData(items, option.Precision, appCon.UseForeignCurrency);
report = reportTemp;
}
report.Name = outputName;
report.Document.Name = outputName;
report.Run();
return(report.Convert());
}
/// <summary>請求残高年齢表データ取得 オプションによって合計行計算も実施</summary>
/// <param name="SearchOption"></param>
/// <returns></returns>
/// <remarks>
/// 合計行の集計
/// TODO: next → 合計を端数処理、端数処理した値を合計 変えられるようにする
/// </remarks>
public async Task <IEnumerable <BillingAgingList> > GetAsync(BillingAgingListSearch searchOption,
IProgressNotifier notifier = null,
CancellationToken token = default(CancellationToken))
{
const int staffSubtotalType = 1;
const int departmentSubtotalType = 2;
const int grandTotalType = 3;
var opt = searchOption;
opt.InitializeYearMonthConditions();
var details = (await billingAgingListQueryProcessor.GetAsync(opt, notifier, token)).ToArray();
var useReceipt = opt.BillingRemainType == 1;
var unit = opt.UnitValue;
var list = new List <BillingAgingList>();
var pcusIndexBuf = (int?)null;
var stafSub = GetSubtotalList();
var deptSub = GetSubtotalList();
var pcusSub = GetSubtotalList();
var grndSub = GetSubtotalList();
BillingAgingList detailBuf = null;
foreach (var detail in details)
{
detail.CurrentMonthSales = detail.BillingAmount0;
detail.CurrentMonthReceipt = detail.ReceiptAmount0;
detail.CurrentMonthMatching = detail.MatchingAmount0;
detail.LastMonthRemain = detail.Balance + detail.BillingAmountK
- (useReceipt ? detail.ReceiptAmountK : detail.MatchingAmountK);
detail.CurrentMonthRemain = detail.LastMonthRemain + detail.CurrentMonthSales
- (useReceipt ? (detail.CurrentMonthReceipt ?? 0M) : detail.CurrentMonthMatching);
detail.MonthlyRemain0 = detail.BillingAmount0 - detail.BillingMatchingAmount0 + (useReceipt ? detail.MatchingAmount0 - detail.ReceiptAmount0 : 0M);
detail.MonthlyRemain1 = detail.BillingAmount1 - detail.BillingMatchingAmount1 + (useReceipt ? detail.MatchingAmount1 - detail.ReceiptAmount1 : 0M);
detail.MonthlyRemain2 = detail.BillingAmount2 - detail.BillingMatchingAmount2 + (useReceipt ? detail.MatchingAmount2 - detail.ReceiptAmount2 : 0M);
detail.MonthlyRemain3 = detail.BillingAmount3 - detail.BillingMatchingAmount3 + (useReceipt ? detail.MatchingAmount3 - detail.ReceiptAmount3 : 0M)
+ detail.BillingAmount4 - detail.BillingMatchingAmount4 + (useReceipt ? detail.MatchingAmount4 - detail.ReceiptAmount4 : 0M);
var skip = searchOption.ConsiderCustomerGroup &&
searchOption.BillingRemainType > 0 &&
detail.ParentCustomerId == detail.CustomerId &&
detail.CurrentMonthSales == 0M &&
detail.CurrentMonthMatching == 0M;
if (detail.LastMonthRemain == 0M &&
detail.CurrentMonthReceipt == 0M &&
detail.CurrentMonthRemain == 0M)
{
continue;
}
if (pcusIndexBuf.HasValue &&
RequireCustomerGroupSubtotal(opt, detailBuf, detail))
{
SetSubtotal(list[pcusIndexBuf.Value], pcusSub, unit);
ResetSubtotal(pcusSub);
pcusIndexBuf = null;
}
if (RequireStaffSubtotal(opt, detailBuf, detail))
{
list.Add(GetSubtotal(detailBuf, stafSub, staffSubtotalType, unit));
ResetSubtotal(stafSub);
}
if (RequireDepartmentSubtotal(opt, detailBuf, detail))
{
list.Add(GetSubtotal(detailBuf, deptSub, departmentSubtotalType, unit));
ResetSubtotal(deptSub);
}
if (opt.ConsiderCustomerGroup &&
detail.ParentCustomerId.HasValue &&
(detailBuf?.ParentCustomerId != detail.ParentCustomerId ||
detailBuf?.StaffId != detail.StaffId ||
detailBuf?.DepartmentId != detail.DepartmentId))
{
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;
list.Add(parent);
pcusIndexBuf = list.IndexOf(parent);
}
if (opt.RequireStaffSubtotal)
{
AddSubtotal(stafSub, detail);
}
if (opt.RequireDepartmentSubtotal)
{
AddSubtotal(deptSub, detail);
}
if (opt.ConsiderCustomerGroup && pcusIndexBuf.HasValue)
{
AddSubtotal(pcusSub, detail);
}
AddSubtotal(grndSub, detail);
if (opt.ConsiderCustomerGroup &&
opt.BillingRemainType > 0 &&
detail.ParentCustomerId.HasValue)
{
detail.LastMonthRemain = null;
detail.CurrentMonthReceipt = null;
detail.CurrentMonthRemain = null;
detail.MonthlyRemain0 = null;
detail.MonthlyRemain1 = null;
detail.MonthlyRemain2 = null;
detail.MonthlyRemain3 = null;
}
if (!skip)
{
list.Add(detail);
TruncateValue(detail, unit);
}
detailBuf = detail;
}
if (pcusIndexBuf.HasValue &&
RequireCustomerGroupSubtotal(opt, detailBuf, null))
{
SetSubtotal(list[pcusIndexBuf.Value], pcusSub, unit);
}
if (RequireStaffSubtotal(opt, detailBuf, null))
{
list.Add(GetSubtotal(detailBuf, stafSub, staffSubtotalType, unit));
}
if (RequireDepartmentSubtotal(opt, detailBuf, null))
{
list.Add(GetSubtotal(detailBuf, deptSub, departmentSubtotalType, unit));
}
if (detailBuf != null)
{
list.Add(GetSubtotal(detailBuf, grndSub, grandTotalType, unit));
}
notifier?.UpdateState();
return(list);
}
public async Task <byte[]> GetAsync(BillingAgingListSearch option, IProgressNotifier notifier = null, CancellationToken token = default(CancellationToken))
{
var companyTask = companyQueryProcessor.GetAsync(new CompanySearch {
Id = option.CompanyId,
}, token);
var appConTask = applicationControlGetByCompanyQueryProcessor.GetAsync(option.CompanyId, token);
var settingTask = reportSettingQueryProcessor.GetAsync(option.CompanyId, ReportId, token);
var loadTask = billingAgingListProcessor.GetAsync(option, notifier, token);
var tasks = new List <Task> {
companyTask, appConTask, settingTask, loadTask
};
Task <IEnumerable <Currency> > currenciesTask = null;
if (option.CurrencyId.HasValue)
{
currenciesTask = currencyGetByIdsQueryProcessor.GetByIdsAsync(new[] { option.CurrencyId.Value }, token);
tasks.Add(currenciesTask);
}
await Task.WhenAll(tasks);
var company = companyTask.Result.First();
var appCon = appConTask.Result;
var settings = settingTask.Result.ToList();
var items = loadTask.Result.ToList();
var precition = currenciesTask?.Result.FirstOrDefault().Precision ?? 0;
if (!items.Any())
{
return(null);
}
var remianType = settings.GetReportSetting <ReportAdvanceReceivedType>(BillingRemainType);
var displayCode = settings.GetReportSetting <ReportDoOrNot>(DisplayCustomerCode) == ReportDoOrNot.Do;
var processor = remianType == ReportAdvanceReceivedType.UseMatchingAmount ?
(IProcessor) new BillingAgingListDocumentProcessor {
Company = company,
Items = items,
Option = option,
Precision = precition,
DisplayCustomerCode = displayCode,
UseForeignCurrency = appCon.UseForeignCurrency == 1,
} : new BillingAgingListReceiptDocumentProcessor {
Company = company,
Items = items,
Option = option,
Precision = precition,
DisplayCustomerCode = displayCode,
UseForeignCurrency = appCon.UseForeignCurrency == 1,
};
using (var stream = new MemoryStream())
{
using (var document = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook))
{
processor.Process(document);
}
return(stream.ToArray());
}
}
private string GetQueryInitializeTempTable(BillingAgingListSearch option)
{
var builder = new StringBuilder();
builder.Append(@"
IF EXISTS (SELECT * FROM tempdb..sysobjects
WHERE id = object_id(N'tempdb..#BillingAging'))
DROP TABLE #BillingAging;
CREATE TABLE #BillingAging
( [CompanyId] INT NOT NULL
, [CurrencyId] INT NOT NULL
, [ParentCustomerId] INT NOT NULL
, [CustomerId] INT NOT NULL
, [StaffId] INT NOT NULL
, [DepartmentId] INT NOT NULL
, [YearMonth] DATE NOT NULL
, [Balance] NUMERIC(18, 5) NULL DEFAULT(0)
, [BillingAmount] NUMERIC(18, 5) NULL DEFAULT(0)
, [BillingMatchingAmount] NUMERIC(18, 5) NULL DEFAULT(0)
, [ReceiptAmount] NUMERIC(18, 5) NULL DEFAULT(0)
, [MatchingAmount] NUMERIC(18, 5) NULL DEFAULT(0));
CREATE INDEX IdxBillingAging ON #BillingAging
( [CompanyId]
, [CurrencyId]
, [DepartmentId]
, [StaffId]
, [ParentCustomerId]
, [CustomerId]
, [YearMonth])
");
if (option.BillingRemainType > 0)
{
builder.Append(@"
IF EXISTS (SELECT * FROM tempdb..sysobjects
WHERE id = object_id(N'tempdb..#WorkReceipt'))
DROP TABLE #WorkReceipt;
CREATE TABLE #WorkReceipt
( [ReceiptId] BIGINT NULL
, [CompanyId] INT NULL
, [CurrencyId] INT NULL
, [CustomerId] INT NULL
, [StaffId] INT NULL
, [DepartmentId] INT NULL
, [YearMonth] DATETIME2(3) NULL
, [RecordedAt] DATETIME2(3) NULL
, [ReceiptAmount] NUMERIC(18, 5) NULL
, [MatchingAmount] NUMERIC(18, 5) NULL
, [HasMany] INT NULL
);
IF EXISTS (SELECT * FROM tempdb..sysobjects
WHERE id = object_id(N'tempdb..#CustomerGroupIds'))
DROP TABLE #CustomerGroupIds;
CREATE TABLE #CustomerGroupIds
( [CustomerId] INT NOT NULL);
CREATE INDEX IdxCustomerGroupIds ON #CustomerGroupIds
( [CustomerId] );
IF EXISTS (SELECT * FROM tempdb..sysobjects
WHERE id = object_id(N'tempdb..#HeaderIds'))
DROP TABLE #HeaderIds;
CREATE TABLE #HeaderIds
( [Id] BIGINT NOT NULL);
CREATE INDEX IdxHeaderIds ON #HeaderIds
( [Id] );
");
}
return(builder.ToString());
}
public async Task <HttpResponseMessage> GetSpreadsheet(BillingAgingListSearch option, CancellationToken token)
{
var content = await billingAgingListSpreadsheetProcessor.GetAsync(option, null, token);
return(Request.GetSpreadsheetResponseMessage(content, "billing-aging-list.xlsx"));
}
public async Task <IEnumerable <BillingAgingListDetail> > GetDetailsAsync(BillingAgingListSearch option, CancellationToken token) => (await billingAgingListProcessor.GetDetailsAsync(option, token)).ToArray();
public async Task <BillingAgingListDetailsResult> GetDetailsAsync(string SessionKey, BillingAgingListSearch searchOption)
{
return(await authorizationProcessor.DoAuthorizeAsync(SessionKey, async token =>
{
var result = await billingAgingListProcessor.GetDetailsAsync(searchOption, token);
return new BillingAgingListDetailsResult
{
ProcessResult = new ProcessResult {
Result = true,
},
BillingAgingListDetails = new List <BillingAgingListDetail>(result),
};
}, logger));
}
public async Task <IEnumerable <BillingAgingList> > GetR(BillingAgingListSearch option)
=> await hubContext.DoAsync(option.ConnectionId, async (notifier, token)
=> (await billingAgingListProcessor.GetAsync(option, notifier, token)).ToArray());
private string GetQueryInsertReceiptRemain(BillingAgingListSearch option)
{
var deptId = option.RequireDepartmentSubtotal ? "dp.[Id]" : "0";
var stafId = option.RequireStaffSubtotal ? "st.[Id]" : "0";
var deptAlias = option.IsMasterStaff ? "st" : "b";
var stafAlias = option.IsMasterStaff ? "cs" : "b";
var targetDate = option.UseBilledAt ? "b.[BilledAt]" : "b.[SalesAt]";
var builder = new StringBuilder();
builder.Append($@"
INSERT INTO #CustomerGroupIds
SELECT DISTINCT csg.[ParentCustomerId]
FROM [dbo].[CustomerGroup] csg
INNER JOIN [dbo].[Customer] cs ON cs.[Id] = csg.[ParentCustomerId]
AND cs.[CompanyId] = @CompanyId
UNION ALL
SELECT DISTINCT csg.[ChildCustomerId]
FROM [dbo].[CustomerGroup] csg
INNER JOIN [dbo].[Customer] cs ON cs.[Id] = csg.[ChildCustomerId]
AND cs.[CompanyId] = @CompanyId
INSERT INTO #HeaderIds
SELECT DISTINCT m.MatchingHeaderId
FROM [dbo].[Matching] m
INNER JOIN [dbo].[Billing] b ON b.[Id] = m.[BillingId]
AND b.[CompanyId] = @CompanyId");
if (option.CurrencyId.HasValue)
{
builder.Append(@"
AND b.[CurrencyId] = @CurrencyId");
}
builder.Append($@"
AND b.[Approved] = 1
AND b.[InputType] <> 3
INNER JOIN [dbo].[Receipt] r ON r.[Id] = m.[ReceiptId]
AND r.[RecordedAt] BETWEEN @ym4f AND @ym0t
AND r.[OriginalReceiptId] IS NULL
INNER JOIN [dbo].[Customer] cs ON cs.[Id] = b.[CustomerId]
AND cs.[CompanyId] = @CompanyId
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.[CompanyId] = @CompanyId
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.[CompanyId] = @CompanyId
AND dp.[Code] >= COALESCE(@DepartmentCodeFrom , dp.[Code])
AND dp.[Code] <= COALESCE(@DepartmentCodeTo , dp.[Code])
OPTION ( FORCE ORDER )
INSERT INTO #WorkReceipt
( [ReceiptId]
, [CompanyId]
, [CurrencyId]
, [CustomerId]
, [StaffId]
, [DepartmentId]
, [YearMonth]
, [RecordedAt]
, [ReceiptAmount]
, [MatchingAmount]
, [HasMany] )
SELECT r.[ReceiptId]
, r.[CompanyId]
, r.[CurrencyId]
, r.[CustomerId]
, r.[StaffId]
, r.[DepartmentId]
, r.[YearMonth]
, r.[RecordedAt]
, CASE r.[ReceiptSeq] WHEN 1 THEN r.[ReceiptAmount] ELSE 0 END [ReceiptAmount]
, r.[MatchingAmount]
, r.[HasMany]
FROM (
SELECT r.*
, ROW_NUMBER() OVER (
PARTITION BY r.[ReceiptId]
ORDER BY r.[CustomerId] ) [ReceiptSeq]
, COUNT(1) OVER ( PARTITION BY r.[ReceiptId] ) [HasMany]
FROM (
SELECT r.[Id] [ReceiptId]
, MIN( r.[CompanyId] ) [CompanyId]
, MIN( r.[CurrencyId] ) [CurrencyId]
, b.[CustomerId]
, {stafId} [StaffId]
, {deptId} [DepartmentId]
, MIN(
CASE WHEN r.[RecordedAt] BETWEEN @ym4f AND @ym4t THEN @ym4t
WHEN r.[RecordedAt] BETWEEN @ym3f AND @ym3t THEN @ym3t
WHEN r.[RecordedAt] BETWEEN @ym2f AND @ym2t THEN @ym2t
WHEN r.[RecordedAt] BETWEEN @ym1f AND @ym1t THEN @ym1t
WHEN r.[RecordedAt] BETWEEN @ym0f AND @ym0t THEN @ym0t
END ) [YearMonth]
, MIN( r.[RecordedAt] ) [RecordedAt]
, MIN( r.[ReceiptAmount] )
+ SUM ( m.[BankTransferFee]
- CASE WHEN m.[TaxDifference] < 0 THEN m.[TaxDifference] ELSE 0 END
+ COALESCE( mbd.[DiscountAmount], 0) ) [ReceiptAmount]
, 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");
if (option.CurrencyId.HasValue)
{
builder.Append(@"
AND r.[CurrencyId] = @CurrencyId");
}
builder.Append($@"
AND m.[MatchingHeaderId] IN (SELECT Id FROM #HeaderIds)
AND r.[RecordedAt] BETWEEN @ym4f AND @ym0t
AND r.[OriginalReceiptId] IS NULL
INNER JOIN [dbo].[Billing] b ON b.[Id] = m.[BillingId]
AND b.[Approved] = 1
AND b.[InputType] <> 3
AND b.[CustomerId] NOT IN ( /* not exists customer group */
SELECT CustomerId FROM #CustomerGroupIds )
INNER JOIN [dbo].[Customer] cs ON cs.[Id] = b.[CustomerId]
INNER JOIN [dbo].[Staff] st ON st.[Id] = {stafAlias}.[StaffId]
INNER JOIN [dbo].[Department] dp ON dp.[Id] = {deptAlias}.[DepartmentId]
LEFT JOIN (
SELECT mbd.[MatchingId]
, SUM( mbd.[DiscountAmount] ) [DiscountAmount]
FROM [dbo].[MatchingBillingDiscount] mbd
GROUP BY
mbd.[MatchingId]
) mbd
ON mbd.[MatchingId] = m.[Id]
LEFT JOIN [dbo].[Matching] m2 /* limitation first matching */
ON m2.[ReceiptId] = m.[ReceiptId]
AND m2.[MatchingHeaderId] < m.[MatchingHeaderId]
WHERE m2.[Id] IS NULL
GROUP BY
r.[Id]
, b.[CustomerId]");
if (option.RequireStaffSubtotal)
{
builder.Append(@"
, st.[Id]");
}
if (option.RequireDepartmentSubtotal)
{
builder.Append(@"
, dp.[Id]");
}
builder.Append($@"
UNION ALL
SELECT r.[Id] [ReceiptId]
, MIN( r.[CompanyId] ) [CompanyId]
, MIN( r.[CurrencyId] ) [CurrencyId]
, b.[CustomerId]
, {stafId} [StaffId]
, {deptId} [DepartmentId]
, MIN(
CASE WHEN r.[RecordedAt] BETWEEN @ym4f AND @ym4t THEN @ym4t
WHEN r.[RecordedAt] BETWEEN @ym3f AND @ym3t THEN @ym3t
WHEN r.[RecordedAt] BETWEEN @ym2f AND @ym2t THEN @ym2t
WHEN r.[RecordedAt] BETWEEN @ym1f AND @ym1t THEN @ym1t
WHEN r.[RecordedAt] BETWEEN @ym0f AND @ym0t THEN @ym0t
END ) [YearMonth]
, MIN( r.[RecordedAt] ) [RecordedAt]
, MIN( r.[ReceiptAmount] )
+ SUM ( m.[BankTransferFee]
- CASE WHEN m.[TaxDifference] < 0 THEN m.[TaxDifference] ELSE 0 END
+ COALESCE( mbd.[DiscountAmount], 0) ) [ReceiptAmount]
, 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");
if (option.CurrencyId.HasValue)
{
builder.Append(@"
AND r.[CurrencyId] = @CurrencyId");
}
builder.Append($@"
AND m.[MatchingHeaderId] IN (SELECT Id FROM #HeaderIds)
AND r.[RecordedAt] BETWEEN @ym4f AND @ym0t
AND r.[OriginalReceiptId] IS NULL
INNER JOIN [dbo].[Billing] b ON b.[Id] = m.[BillingId]
AND b.[Approved] = 1
AND b.[InputType] <> 3
AND b.[CustomerId] IN ( /* exist customer group */
SELECT CustomerId FROM #CustomerGroupIds )
INNER JOIN [dbo].[Customer] cs ON cs.[Id] = b.[CustomerId]
INNER JOIN [dbo].[Staff] st ON st.[Id] = {stafAlias}.[StaffId]
INNER JOIN [dbo].[Department] dp ON dp.[Id] = {deptAlias}.[DepartmentId]
LEFT JOIN (
SELECT mbd.[MatchingId]
, SUM( mbd.[DiscountAmount] ) [DiscountAmount]
FROM [dbo].[MatchingBillingDiscount] mbd
GROUP BY
mbd.[MatchingId]
) mbd
ON mbd.[MatchingId] = m.[Id]
LEFT JOIN [dbo].[Matching] m2
ON m2.[ReceiptId] = m.[ReceiptId]
AND m2.[MatchingHeaderId] < m.[MatchingHeaderId]
WHERE m2.[Id] IS NULL
GROUP BY
r.[Id]
, b.[CustomerId]");
if (option.RequireStaffSubtotal)
{
builder.Append(@"
, st.[Id]");
}
if (option.RequireDepartmentSubtotal)
{
builder.Append(@"
, dp.[Id]");
}
builder.Append($@"
) r
) r
OPTION ( FORCE ORDER )
INSERT INTO #BillingAging
( [CompanyId]
, [CurrencyId]
, [YearMonth]
, [ParentCustomerId]
, [CustomerId]
, [StaffId]
, [DepartmentId]
, [ReceiptAmount] )
SELECT wr.[CompanyId]
, wr.[CurrencyId]
, wr.[YearMonth]
, COALESCE( pcs.[Id], CASE cs.[IsParent] WHEN 1 THEN cs.[Id] ElSE 0 END ) [ParentCustomerId]
, wr.[CustomerId]
, wr.[StaffId]
, wr.[DepartmentId]
, wr.[ReceiptAmount]
FROM (
SELECT wr.[CompanyId]
, wr.[CurrencyId]
, wr.[CustomerId]
, wr.[StaffId]
, wr.[DepartmentId]
, wr.[YearMonth]
, CASE WHEN wr.[HasMany] > 1 THEN wr.[MatchingAmount] ELSE wr.[ReceiptAmount] END [ReceiptAmount]
FROM #WorkReceipt wr
UNION ALL
SELECT wr.[CompanyId]
, wr.[CurrencyId]
, wr.[CustomerId]
, wr.[StaffId]
, wr.[DepartmentId]
, wr.[YearMonth]
, wr.[ReceiptAmount]
FROM (
SELECT wr.[CompanyId]
, wr.[CurrencyId]
, COALESCE(ccsg.[ParentCustomerId], pcsg.[ParentCustomerId], wr.[CustomerId] ) [CustomerId]
, wr.[StaffId]
, wr.[DepartmentId]
, wr.[RecordedAt]
, wr.[YearMonth]
, SUM( wr.[ReceiptAmount] - wr.[MatchingAmount] ) [ReceiptAmount]
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.[HasMany] > 1
GROUP BY
wr.[CompanyId]
, wr.[CurrencyId]
, COALESCE(ccsg.[ParentCustomerId], pcsg.[ParentCustomerId], wr.[CustomerId] )
, wr.[StaffId]
, wr.[DepartmentId]
, wr.[RecordedAt]
, wr.[YearMonth]
) wr
) wr
LEFT JOIN [dbo].[Customer] cs ON cs.[Id] = wr.[CustomerId]
LEFT JOIN [dbo].[CustomerGroup] csg ON cs.[Id] = csg.[ChildCustomerId]
LEFT JOIN [dbo].[Customer] pcs ON pcs.[Id] = csg.[ParentCustomerId]
OPTION ( FORCE ORDER )
");
return(builder.ToString());
}
private string GetQuerySelectBillingAgingList(BillingAgingListSearch option)
{
var builder = new StringBuilder();
builder.Append(@"
SELECT ba.*
, cr.[Code] [CurrencyCode]
, 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]
FROM (
SELECT ba.[CompanyId]
, ba.[CurrencyId]
, CASE WHEN ba2.[HasAnyChildren] = 0
OR ba.[ParentCustomerId] = 0
OR @ConsiderCustomerGroup = 0 THEN NULL ELSE ba.[ParentCustomerId] END [ParentCustomerId]
, ba.[CustomerId]
, ba.[StaffId]
, ba.[DepartmentId]
, ba.[Balance]
, ba.[BillingAmountK]
, ba.[BillingAmount0]
, ba.[BillingAmount1]
, ba.[BillingAmount2]
, ba.[BillingAmount3]
, ba.[BillingAmount4]
, ba.[ReceiptAmountK]
, ba.[ReceiptAmount0]
, ba.[ReceiptAmount1]
, ba.[ReceiptAmount2]
, ba.[ReceiptAmount3]
, ba.[ReceiptAmount4]
, ba.[BillingMatchingAmountK]
, ba.[BillingMatchingAmount0]
, ba.[BillingMatchingAmount1]
, ba.[BillingMatchingAmount2]
, ba.[BillingMatchingAmount3]
, ba.[BillingMatchingAmount4]
, ba.[MatchingAmountK]
, ba.[MatchingAmount0]
, ba.[MatchingAmount1]
, ba.[MatchingAmount2]
, ba.[MatchingAmount3]
, ba.[MatchingAmount4]
FROM (
SELECT ba.[CompanyId]
, ba.[CurrencyId]
, ba.[ParentCustomerId]
, ba.[CustomerId]
, ba.[StaffId]
, ba.[DepartmentId]
, SUM( ba.[Balance] ) [Balance]
, SUM( CASE WHEN ba.[YearMonth] IN (@ym4t, @ym3t, @ym2t, @ym1t) THEN ba.[BillingAmount] ELSE 0 END ) [BillingAmountK]
, SUM( CASE WHEN ba.[YearMonth] = @ym0t THEN ba.[BillingAmount] ELSE 0 END ) [BillingAmount0]
, SUM( CASE WHEN ba.[YearMonth] = @ym1t THEN ba.[BillingAmount] ELSE 0 END ) [BillingAmount1]
, SUM( CASE WHEN ba.[YearMonth] = @ym2t THEN ba.[BillingAmount] ELSE 0 END ) [BillingAmount2]
, SUM( CASE WHEN ba.[YearMonth] = @ym3t THEN ba.[BillingAmount] ELSE 0 END ) [BillingAmount3]
, SUM( CASE WHEN ba.[YearMonth] = @ym4t THEN ba.[BillingAmount] ELSE 0 END ) [BillingAmount4]
, SUM( CASE WHEN ba.[YearMonth] IN (@ym4t, @ym3t, @ym2t, @ym1t) THEN ba.[ReceiptAmount] ELSE 0 END ) [ReceiptAmountK]
, SUM( CASE WHEN ba.[YearMonth] = @ym0t THEN ba.[ReceiptAmount] ELSE 0 END ) [ReceiptAmount0]
, SUM( CASE WHEN ba.[YearMonth] = @ym1t THEN ba.[ReceiptAmount] ELSE 0 END ) [ReceiptAmount1]
, SUM( CASE WHEN ba.[YearMonth] = @ym2t THEN ba.[ReceiptAmount] ELSE 0 END ) [ReceiptAmount2]
, SUM( CASE WHEN ba.[YearMonth] = @ym3t THEN ba.[ReceiptAmount] ELSE 0 END ) [ReceiptAmount3]
, SUM( CASE WHEN ba.[YearMonth] = @ym4t THEN ba.[ReceiptAmount] ELSE 0 END ) [ReceiptAmount4]
, SUM( CASE WHEN ba.[YearMonth] IN (@ym4t, @ym3t, @ym2t, @ym1t) THEN ba.[BillingMatchingAmount] ELSE 0 END ) [BillingMatchingAmountK]
, SUM( CASE WHEN ba.[YearMonth] = @ym0t THEN ba.[BillingMatchingAmount] ELSE 0 END ) [BillingMatchingAmount0]
, SUM( CASE WHEN ba.[YearMonth] = @ym1t THEN ba.[BillingMatchingAmount] ELSE 0 END ) [BillingMatchingAmount1]
, SUM( CASE WHEN ba.[YearMonth] = @ym2t THEN ba.[BillingMatchingAmount] ELSE 0 END ) [BillingMatchingAmount2]
, SUM( CASE WHEN ba.[YearMonth] = @ym3t THEN ba.[BillingMatchingAmount] ELSE 0 END ) [BillingMatchingAmount3]
, SUM( CASE WHEN ba.[YearMonth] = @ym4t THEN ba.[BillingMatchingAmount] ELSE 0 END ) [BillingMatchingAmount4]
, SUM( CASE WHEN ba.[YearMonth] IN (@ym4t, @ym3t, @ym2t, @ym1t) THEN ba.[MatchingAmount] ELSE 0 END ) [MatchingAmountK]
, SUM( CASE WHEN ba.[YearMonth] = @ym0t THEN ba.[MatchingAmount] ELSE 0 END ) [MatchingAmount0]
, SUM( CASE WHEN ba.[YearMonth] = @ym1t THEN ba.[MatchingAmount] ELSE 0 END ) [MatchingAmount1]
, SUM( CASE WHEN ba.[YearMonth] = @ym2t THEN ba.[MatchingAmount] ELSE 0 END ) [MatchingAmount2]
, SUM( CASE WHEN ba.[YearMonth] = @ym3t THEN ba.[MatchingAmount] ELSE 0 END ) [MatchingAmount3]
, SUM( CASE WHEN ba.[YearMonth] = @ym4t THEN ba.[MatchingAmount] ELSE 0 END ) [MatchingAmount4]
FROM #BillingAging ba
WHERE ba.[CompanyId] = @CompanyId
GROUP BY
ba.[CompanyId]
, ba.[CurrencyId]
, ba.[DepartmentId]
, ba.[StaffId]
, ba.[ParentCustomerId]
, ba.[CustomerId]
) ba
INNER JOIN (
SELECT ba.[CompanyId]
, ba.[CurrencyId]
, ba.[StaffId]
, ba.[DepartmentId]
, ba.[ParentCustomerId]
, SUM( CASE WHEN ba.[ParentCustomerId] = 0 OR ba.[ParentCustomerId] = ba.[CustomerId] THEN 0 ELSE 1 END ) [HasAnyChildren]
FROM #BillingAging ba
GROUP BY
ba.[CompanyId]
, ba.[CurrencyId]
, ba.[DepartmentId]
, ba.[StaffId]
, ba.[ParentCustomerId]
) ba2
ON ba.[CompanyId] = ba2.[CompanyId]
AND ba.[CurrencyId] = ba2.[CurrencyId]
AND ba.[StaffId] = ba2.[StaffId]
AND ba.[DepartmentId] = ba2.[DepartmentId]
AND ba.[ParentCustomerId] = ba2.[ParentCustomerId]
) ba
LEFT JOIN [dbo].[Customer] cs ON cs.[Id] = ba.[CustomerId]
LEFT JOIN [dbo].[Department] dp ON dp.[Id] = ba.[DepartmentId]
LEFT JOIN [dbo].[Staff] st ON st.[Id] = ba.[StaffId]
LEFT JOIN [dbo].[Currency] cr ON cr.[Id] = ba.[CurrencyId]
LEFT JOIN [dbo].[Customer] pcs ON pcs.[Id] = ba.[ParentCustomerId]
ORDER BY cr.[Code]");
if (option.RequireDepartmentSubtotal)
{
builder.Append(@"
, CASE WHEN dp.[Code] IS NULL THEN 1 ELSE 0 END, dp.[Code]");
}
if (option.RequireStaffSubtotal)
{
builder.Append(@"
, CASE WHEN st.[Code] IS NULL THEN 1 ELSE 0 END, st.[Code]");
}
builder.Append(@"
, COALESCE(pcs.[Code], cs.[Code])
OPTION ( FORCE ORDER )
");
return(builder.ToString());
}
public async Task <ActionResult <IEnumerable <BillingAgingList> > > Get(BillingAgingListSearch option, CancellationToken token) => (await billingAgingListProcessor.GetAsync(option, null, token)).ToArray();
private string GetQueryInsertBillingReamin(BillingAgingListSearch option)
{
var deptId = option.RequireDepartmentSubtotal ? "dp.[Id]" : "0";
var stafId = option.RequireStaffSubtotal ? "st.[Id]" : "0";
var deptAlias = option.IsMasterStaff ? "st" : "b";
var stafAlias = option.IsMasterStaff ? "cs" : "b";
var targetDate = option.UseBilledAt ? "b.[BilledAt]" : "b.[SalesAt]";
var builder = new StringBuilder();
builder.Append($@"
INSERT INTO #BillingAging
( [CompanyId]
, [CurrencyId]
, [YearMonth]
, [ParentCustomerId]
, [CustomerId]
, [StaffId]
, [DepartmentId]
, [BillingAmount]
, [BillingMatchingAmount]
)
SELECT b.[CompanyId]
, b.[CurrencyId]
, b.[YearMonth]
, COALESCE(pcs.[Id], CASE cs.[IsParent] WHEN 1 THEN cs.[Id] ELSE 0 END) [ParentCustomerId]
, b.[CustomerId]
, b.[StaffId]
, b.[DepartmentId]
, b.[BillingAmount]
, b.[BillingMatchingAmount]
FROM (
SELECT b.[CompanyId]
, b.[CurrencyId]
, b.[CustomerId]
, CASE WHEN {targetDate} BETWEEN @ym4f AND @ym4t THEN @ym4t
WHEN {targetDate} BETWEEN @ym3f AND @ym3t THEN @ym3t
WHEN {targetDate} BETWEEN @ym2f AND @ym2t THEN @ym2t
WHEN {targetDate} BETWEEN @ym1f AND @ym1t THEN @ym1t
WHEN {targetDate} BETWEEN @ym0f AND @ym0t THEN @ym0t
END [YearMonth]
, {stafId} [StaffId]
, {deptId} [DepartmentId]
, SUM( b.[BillingAmount]
- CASE WHEN b.[DeleteAt] = b.[DeleteAt] THEN b.[RemainAmount] ELSE 0 END ) [BillingAmount]
, SUM(COALESCE(m.[BillingMatchingAmount], 0)) [BillingMatchingAmount]
FROM [dbo].[Billing] b
LEFT JOIN (
SELECT m.[BillingId]
, SUM( m.[Amount]
+ m.[BankTransferFee]
- CASE WHEN m.[TaxDifference] < 0 THEN m.[TaxDifference] ELSE 0 END
+ COALESCE(mbd.[DiscountAmount], 0)
) [BillingMatchingAmount]
FROM [dbo].[Matching] m
INNER JOIN [dbo].[Billing] b ON b.[Id] = m.[BillingId]
AND b.[CompanyId] = @CompanyId
AND b.[Approved] = 1
AND b.[InputType] <> 3
AND m.[RecordedAt] <= @ym0t
LEFT JOIN (
SELECT mbd.[MatchingId]
, SUM( mbd.[DiscountAmount] ) [DiscountAmount]
FROM [dbo].[MatchingBillingDiscount] mbd
GROUP BY mbd.[MatchingId]
) mbd
ON mbd.[MatchingId] = m.[Id]
GROUP BY m.[BillingId]
) m
ON m.[BillingId] = b.[Id]
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])
WHERE b.CompanyId = @CompanyId");
if (option.CurrencyId.HasValue)
{
builder.Append(@"
AND b.CurrencyId = @CurrencyId");
}
builder.Append($@"
AND {targetDate} BETWEEN @ym4f AND @ym0t /* target date */
AND b.[Approved] = 1
AND b.[InputType] <> 3
GROUP BY
b.[CompanyId]
, b.[CurrencyId]
, b.[CustomerId]
, CASE WHEN {targetDate} BETWEEN @ym4f AND @ym4t THEN @ym4t
WHEN {targetDate} BETWEEN @ym3f AND @ym3t THEN @ym3t
WHEN {targetDate} BETWEEN @ym2f AND @ym2t THEN @ym2t
WHEN {targetDate} BETWEEN @ym1f AND @ym1t THEN @ym1t
WHEN {targetDate} BETWEEN @ym0f AND @ym0t THEN @ym0t
END");
if (option.RequireStaffSubtotal)
{
builder.Append(@"
, st.[Id]");
}
if (option.RequireDepartmentSubtotal)
{
builder.Append(@"
, dp.[Id]");
}
builder.Append(@"
) b
INNER JOIN [dbo].[Customer] cs ON cs.[Id] = b.[CustomerId]
LEFT JOIN [dbo].[CustomerGroup] csg ON cs.[Id] = csg.[ChildCustomerId]
LEFT JOIN [dbo].[Customer] pcs ON pcs.[Id] = csg.[ParentCustomerId]
OPTION ( FORCE ORDER )
");
return(builder.ToString());
}
public Task <IEnumerable <BillingAgingListDetail> > GetDetailsAsync(BillingAgingListSearch SearchOption, CancellationToken token)
{
var staffJoin = SearchOption.IsMasterStaff ? "cs.[StaffId]" : "b.[StaffId]";
var departmentJoin = SearchOption.IsMasterStaff ? "st.[DepartmentId]" : "b.[DepartmentId]";
var targetDate = SearchOption.TargetDate == 0 ? "b.[BilledAt]" : "b.[SalesAt]";
var builder = new StringBuilder();
builder.Append($@"
IF EXISTS (SELECT * FROM tempdb..sysobjects
WHERE id = object_id(N'tempdb..#BillingAgingDetail'))
DROP TABLE #BillingAgingDetail;
CREATE TABLE #BillingAgingDetail
([Id] BIGINT NOT NULL
,[BillingAmount] NUMERIC(18, 5) NOT NULL);
CREATE INDEX IdxBillingAgingDetail ON #BillingAgingDetail
( [Id] );
INSERT INTO #BillingAgingDetail
SELECT b.[Id]
, b.[BillingAmount]
- CASE WHEN b.[DeleteAt] = b.[DeleteAt] THEN b.[RemainAmount] ELSE 0 END [BillingAmount]
FROM [dbo].[Billing] b
INNER JOIN [dbo].[Customer] cs
ON cs.[Id] = b.[CustomerId]
AND {targetDate} BETWEEN @ymf AND @ymt
INNER JOIN [dbo].[Staff] st
ON st.[Id] = {staffJoin}
INNER JOIN [dbo].[Department] dp
ON dp.[Id] = {departmentJoin}
WHERE b.[CompanyId] = @CompanyId");
if (SearchOption.CurrencyId.HasValue)
{
builder.Append(@"
AND b.[CurrencyId] = @CurrencyId");
}
builder.Append($@"
AND ( b.[CustomerId] = @CustomerId
OR b.[CustomerId] IN (
SELECT csg.[ChildCustomerId]
FROM [dbo].[CustomerGroup] csg
WHERE csg.[ParentCustomerId] = @CustomerId )
)
AND b.[BillingAmount] <> CASE WHEN b.[DeleteAt] = b.[DeleteAt] THEN b.[RemainAmount] ELSE 0 END");
if (!string.IsNullOrEmpty(SearchOption.CustomerCodeFrom))
{
builder.Append(@"
AND cs.[Code] >= @CustomerCodeFrom");
}
if (!string.IsNullOrEmpty(SearchOption.CustomerCodeTo))
{
builder.Append(@"
AND cs.[Code] <= @CustomerCodeTo");
}
if (SearchOption.ClosingDay.HasValue)
{
builder.Append(@"
AND cs.[ClosingDay] = @ClosingDay");
}
if (!string.IsNullOrEmpty(SearchOption.StaffCodeFrom))
{
builder.Append(@"
AND st.[Code] >= @StaffCodeFrom");
}
if (!string.IsNullOrEmpty(SearchOption.StaffCodeTo))
{
builder.Append(@"
AND st.[Code] <= @StaffCodeTo");
}
if (!string.IsNullOrEmpty(SearchOption.DepartmentCodeFrom))
{
builder.Append(@"
AND dp.[Code] >= @DepartmentCodeFrom");
}
if (!string.IsNullOrEmpty(SearchOption.DepartmentCodeTo))
{
builder.Append(@"
AND dp.[Code] <= @DepartmentCodeTo");
}
builder.Append($@"
SELECT b.[Id]
, b.[CompanyId]
, b.[CurrencyId]
, b.[BilledAt]
, b.[DueAt]
, b.[SalesAt]
, bad.[BillingAmount]
, bad.[BillingAmount]
- COALESCE(m.[MatchingAmount], 0) [RemainAmount]
, b.[InvoiceCode]
, b.[Note1] [Note]
, cs.[Code] [CustomerCode]
, cs.[Name] [CustomerName]
, st.[Code] [StaffCode]
, st.[Name] [StaffName]
, dp.[Code] [DepartmentCode]
, dp.[Name] [DepartmentName]
, c.[Code] [CurrencyCode]
FROM #BillingAgingDetail bad
INNER JOIN [dbo].[Billing] b ON b.[Id] = bad.[Id]
INNER JOIN [dbo].[Customer] cs ON cs.[Id] = b.[CustomerId]
INNER JOIN [dbo].[Staff] st ON st.[Id] = {staffJoin}
INNER JOIN [dbo].[Department] dp ON dp.[Id] = {departmentJoin}
INNER JOIN [dbo].[Currency] c ON c.[Id] = b.[CurrencyId]
LEFT JOIN (
SELECT m.[BillingId]
, SUM( m.[Amount]
+ m.[BankTransferFee]
- CASE WHEN m.[TaxDifference] < 0 THEN m.[TaxDifference] ELSE 0 END
+ COALESCE(mbd.[DiscountAmount], 0)
) [MatchingAmount]
FROM #BillingAgingDetail bad
INNER JOIN [dbo].[Matching] m
ON bad.[Id] = m.[BillingId]
AND m.[RecordedAt] <= @ym0t
LEFT JOIN (
SELECT mbd.[MatchingId]
, SUM( mbd.[DiscountAmount] ) [DiscountAmount]
FROM [dbo].[MatchingBillingDiscount] mbd
GROUP BY mbd.[MatchingId]
) mbd
ON mbd.[MatchingId] = m.[Id]
GROUP BY m.[BillingId]
) m
ON m.[BillingId] = b.[Id]
WHERE bad.[BillingAmount] - COALESCE(m.[MatchingAmount], 0) <> 0");
return(dbHelper.GetItemsAsync <BillingAgingListDetail>(builder.ToString(), SearchOption, token));
}
