private string CreateSelectCollectionScheduleData(CollectionScheduleSearch option)
{
var builder = new StringBuilder();
builder.Append(@"
SELECT ls.*
, cs.[Code] [CustomerCode]
, cs.[Name] [CustomerName]
, cs.[ClosingDay]
, cs.[CollectCategoryId] [CustomerCollectCategoryId]
, cs.[SightOfBill] [CustomerSightOfBill]
, dp.[Code] [DepartmentCode]
, dp.[Name] [DepartmentName]
, st.[Code] [StaffCode]
, st.[Name] [StaffName]
, ct.[Code] [CollectCategoryCode]
, ct.[Name] [CollectCategoryName]
FROM (
SELECT ls.[CustomerId]
, ls.[StaffId]
, ls.[CollectCategoryId]
, SUM( CASE ls.[YearMonth] WHEN @dtprt THEN ls.[BillingAmount] ELSE 0 END )
- SUM( CASE ls.[YearMonth] WHEN @dtprt THEN ls.[MatchingAmount] ELSE 0 END ) [UncollectedAmountLast]
, SUM( CASE ls.[YearMonth] WHEN @dt0t THEN ls.[BillingAmount] ELSE 0 END )
- SUM( CASE ls.[YearMonth] WHEN @dt0t THEN ls.[MatchingAmount] ELSE 0 END ) [UncollectedAmount0]
, SUM( CASE ls.[YearMonth] WHEN @dt1t THEN ls.[BillingAmount] ELSE 0 END )
- SUM( CASE ls.[YearMonth] WHEN @dt1t THEN ls.[MatchingAmount] ELSE 0 END ) [UncollectedAmount1]
, SUM( CASE ls.[YearMonth] WHEN @dt2t THEN ls.[BillingAmount] ELSE 0 END )
- SUM( CASE ls.[YearMonth] WHEN @dt2t THEN ls.[MatchingAmount] ELSE 0 END ) [UncollectedAmount2]
, SUM( CASE ls.[YearMonth] WHEN @dt3t THEN ls.[BillingAmount] ELSE 0 END )
- SUM( CASE ls.[YearMonth] WHEN @dt3t THEN ls.[MatchingAmount] ELSE 0 END ) [UncollectedAmount3]
FROM #List ls
GROUP BY
ls.[CustomerId]
, ls.[StaffId]
, ls.[CollectCategoryId]
) ls
INNER JOIN [dbo].[Customer] cs
ON cs.[Id] = ls.[CustomerId]
INNER JOIN [dbo].[Category] ct
ON ct.[Id] = ls.[CollectCategoryId]
INNER JOIN [dbo].[Staff] st
ON st.[Id] = ls.[StaffId]
INNER JOIN [dbo].[Department] dp
ON dp.[Id] = st.[DepartmentId]
WHERE NOT (ls.[UncollectedAmountLast] = 0
AND ls.[UncollectedAmount0] = 0
AND ls.[UncollectedAmount1] = 0
AND ls.[UncollectedAmount2] = 0
AND ls.[UncollectedAmount3] = 0 )
ORDER BY
dp.[Code] ASC
, st.[Code] ASC
, cs.[Code] ASC
, ct.[Code] ASC");
return(builder.ToString());
}
public async Task <CollectionSchedulesResult> GetAsync(string SessionKey, CollectionScheduleSearch SearchOption, string connectionId)
{
return(await authorizationProcessor.DoAuthorizeAsync(SessionKey, async token =>
{
var notifier = hubContext.CreateNotifier(connectionId);
var result = await collectionScheduleProcessor.GetAsync(SearchOption, token, notifier);
return new CollectionSchedulesResult
{
ProcessResult = new ProcessResult {
Result = true,
},
CollectionSchedules = new List <CollectionSchedule>(result),
};
}, logger, connectionId));
}
public Task <IEnumerable <CollectionSchedule> > GetAsync(CollectionScheduleSearch searchOption, CancellationToken token, IProgressNotifier notifier)
{
return(dbHelper.ExecuteQueriesAsync(async connection =>
{
await dbHelper.ExecuteAsync(connection, CreateInitializeTempTable(searchOption), searchOption, token);
notifier?.UpdateState();
await dbHelper.ExecuteAsync(connection, CreateInsertBillingData(searchOption), searchOption, token);
notifier?.UpdateState();
await dbHelper.ExecuteAsync(connection, CreateInsertMatchingData(searchOption), searchOption, token);
notifier?.UpdateState();
var items = await dbHelper.QueryAsync <CollectionSchedule>(connection, CreateSelectCollectionScheduleData(searchOption), searchOption, token);
notifier?.UpdateState();
return items;
}));
}
private string CreateInitializeTempTable(CollectionScheduleSearch option)
{
var builder = new StringBuilder();
builder.Append(@"
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'tempdb..#List'))
DROP TABLE #List;
CREATE TABLE #List
([CustomerId] INT NOT NULL
,[CollectCategoryId] INT NOT NULL
,[StaffId] INT NOT NULL
,[YearMonth] DATETIME2(3) NOT NULL
,[BillingAmount] NUMERIC(18, 5) NOT NULL DEFAULT(0)
,[MatchingAmount] NUMERIC(18, 5) NOT NULL DEFAULT(0)
);
CREATE INDEX IdxList ON #List
([CustomerId] ASC
,[CollectCategoryId] ASC
,[StaffId] ASC
,[YearMonth] ASC);
");
return(builder.ToString());
}
public async Task <byte[]> GetAsync(CollectionScheduleSearch option, IProgressNotifier notifier, CancellationToken token = default(CancellationToken))
{
var companyTask = companyQueryProcessor.GetAsync(new CompanySearch {
Id = option.CompanyId,
}, token);
var loadTask = collectionScheduleProcessor.GetAsync(option, token, notifier);
await Task.WhenAll(companyTask, loadTask);
var company = companyTask.Result.First();
var items = loadTask.Result.ToList();
if (!items.Any())
{
return(null);
}
var report = new CollectionScheduleSectionReport();
var title = "回収予定表";
var reportName = title + DateTime.Today.ToString("yyyyMMdd");
report.GroupByDepartment = option.DisplayDepartment;
report.NewPagePerDepartment = option.NewPagePerDepartment;
report.NewPagePerStaff = option.NewPagePerStaff;
report.lblUncollectedAmountLast.Text = option.UncollectedAmountLast;
report.lblUncollectAmount0.Text = option.UncollectedAmount0;
report.lblUncollectAmount1.Text = option.UncollectedAmount1;
report.lblUncollectAmount2.Text = option.UncollectedAmount2;
report.lblUncollectAmount3.Text = option.UncollectedAmount3;
report.SetBasicPageSetting(company.Code, company.Name);
report.Name = reportName;
report.SetData(items);
report.Run();
return(report.Convert());
}
private string CreateInsertBillingData(CollectionScheduleSearch option)
{
var staff = !option.UseMasterStaff ? "b.[StaffId]" :
option.DisplayParent ? "COALESCE(pcs.[StaffId], cs.[StaffId])" : "cs.[StaffId]";
var department = option.UseMasterStaff ? "st.[DepartmentId]" : "b.[DepartmentId]";
var customerId = option.DisplayParent ? "COALESCE(pcs.[Id], cs.[Id])" : "cs.[Id]";
var customerCode = option.DisplayParent ? "COALESCE(pcs.[Code], cs.[Code])" : "cs.[Code]";
var builder = new StringBuilder();
builder.Append($@"
INSERT INTO #List
([CustomerId]
,[CollectCategoryId]
,[StaffId]
,[YearMonth]
,[BillingAmount]
)
SELECT {customerId} [CustomerId]
, b.[CollectCategoryId]
, {staff}
, CASE
WHEN b.[DueAt] <= @dtprt THEN @dtprt
WHEN b.[DueAt] BETWEEN @dt0f AND @dt0t THEN @dt0t
WHEN b.[DueAt] BETWEEN @dt1f AND @dt1t THEN @dt1t
WHEN b.[DueAt] BETWEEN @dt2f AND @dt2t THEN @dt2t
WHEN b.[DueAt] >= @dt3f THEN @dt3t
END [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 b.[Approved] = 1
AND b.[InputType] <> 3
AND b.[BilledAt] <= @dt0t");
if (option.DisplayParent)
{
builder.Append(@"
LEFT JOIN [dbo].[CustomerGroup] csg
ON csg.[ChildCustomerId] = b.[CustomerId]
LEFT JOIN [dbo].[Customer] pcs
ON pcs.[Id] = csg.[ParentCustomerId]");
}
builder.Append($@"
INNER JOIN [dbo].[Staff] st
ON st.[Id] = {staff}");
if (!string.IsNullOrEmpty(option.StaffCodeFrom))
{
builder.Append(@"
AND st.[Code] >= @StaffCodeFrom");
}
if (!string.IsNullOrEmpty(option.StaffCodeTo))
{
builder.Append(@"
AND st.[Code] <= @StaffCodeTo");
}
builder.Append($@"
INNER JOIN [dbo].[Department] dp
ON dp.[Id] = {department}");
if (!string.IsNullOrEmpty(option.DepartmentCodeFrom))
{
builder.Append(@"
AND dp.[Code] >= @DepartmentCodeFrom");
}
if (!string.IsNullOrEmpty(option.DepartmentCodeTo))
{
builder.Append(@"
AND dp.[Code] <= @DepartmentCodeTo");
}
builder.Append(@"
WHERE b.[Id] = b.[Id]");
if (!string.IsNullOrEmpty(option.CustomerCodeFrom))
{
builder.Append($@"
AND {customerCode} >= @CustomerCodeFrom");
}
if (!string.IsNullOrEmpty(option.CustomerCodeTo))
{
builder.Append($@"
AND {customerCode} <= @CustomerCodeTo");
}
builder.Append($@"
GROUP BY
{customerId}
, b.[CollectCategoryId]
, {staff}
, CASE
WHEN b.[DueAt] <= @dtprt THEN @dtprt
WHEN b.[DueAt] BETWEEN @dt0f AND @dt0t THEN @dt0t
WHEN b.[DueAt] BETWEEN @dt1f AND @dt1t THEN @dt1t
WHEN b.[DueAt] BETWEEN @dt2f AND @dt2t THEN @dt2t
WHEN b.[DueAt] >= @dt3f THEN @dt3t
END");
return(builder.ToString());
}
private string CreateInsertMatchingData(CollectionScheduleSearch option)
{
var staff = !option.UseMasterStaff ? "b.[StaffId]" :
option.DisplayParent ? "COALESCE(pcs.[StaffId], cs.[StaffId])" : "cs.[StaffId]";
var department = option.UseMasterStaff ? "st.[DepartmentId]" : "b.[DepartmentId]";
var customerId = option.DisplayParent ? "COALESCE(pcs.[Id], cs.[Id])" : "cs.[Id]";
var customerCode = option.DisplayParent ? "COALESCE(pcs.[Code], cs.[Code])" : "cs.[Code]";
var builder = new StringBuilder();
builder.Append($@"
INSERT INTO #List
([CustomerId]
,[CollectCategoryId]
,[StaffId]
,[YearMonth]
,[MatchingAmount]
)
SELECT {customerId} [CustomerId]
, b.[CollectCategoryId]
, {staff}
, CASE
WHEN b.[DueAt] <= @dtprt THEN @dtprt
WHEN b.[DueAt] BETWEEN @dt0f AND @dt0t THEN @dt0t
WHEN b.[DueAt] BETWEEN @dt1f AND @dt1t THEN @dt1t
WHEN b.[DueAt] BETWEEN @dt2f AND @dt2t THEN @dt2t
WHEN b.[DueAt] >= @dt3f THEN @dt3t
END [YearMonth]
, SUM( m.[Amount]
+ m.[BankTransferFee]
- CASE WHEN m.[TaxDifference] < 0 THEN m.[TaxDifference] ELSE 0 END
+ COALESCE( mbd.[DiscountAmount], 0 ) ) [MatchingAmount]
FROM [dbo].[Billing] b
INNER JOIN [dbo].[Matching] m
ON b.[Id] = m.[BillingId]
AND b.[CompanyId] = @CompanyId
AND b.[Approved] = 1
AND b.[InputType] <> 3
AND b.[BilledAt] <= @dt0t
AND m.[RecordedAt] <= @dt0t
INNER JOIN [dbo].[Customer] cs
ON cs.[Id] = b.[CustomerId]");
if (option.DisplayParent)
{
builder.Append(@"
LEFT JOIN [dbo].[CustomerGroup] csg
ON csg.[ChildCustomerId] = b.[CustomerId]
LEFT JOIN [dbo].[Customer] pcs
ON pcs.[Id] = csg.[ParentCustomerId]");
}
builder.Append($@"
INNER JOIN [dbo].[Staff] st
ON st.[Id] = {staff}");
if (!string.IsNullOrEmpty(option.StaffCodeFrom))
{
builder.Append(@"
AND st.[Code] >= @StaffCodeFrom");
}
if (!string.IsNullOrEmpty(option.StaffCodeTo))
{
builder.Append(@"
AND st.[Code] <= @StaffCodeTo");
}
builder.Append($@"
INNER JOIN [dbo].[Department] dp
ON dp.[Id] = {department}");
if (!string.IsNullOrEmpty(option.DepartmentCodeFrom))
{
builder.Append(@"
AND dp.[Code] >= @DepartmentCodeFrom");
}
if (!string.IsNullOrEmpty(option.DepartmentCodeTo))
{
builder.Append(@"
AND dp.[Code] <= @DepartmentCodeTo");
}
builder.Append($@"
LEFT JOIN (
SELECT mbd.[MatchingId]
, SUM( mbd.[DiscountAmount] ) [DiscountAmount]
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 b.[BilledAt] <= @dt0t
INNER JOIN [dbo].[MatchingBillingDiscount] mbd
ON m.[Id] = mbd.[MatchingId]
GROUP BY
mbd.[MatchingId]
) mbd
ON m.[Id] = mbd.[MatchingId]
WHERE b.[Id] = b.[Id]");
if (!string.IsNullOrEmpty(option.CustomerCodeFrom))
{
builder.Append($@"
AND {customerCode} >= @CustomerCodeFrom");
}
if (!string.IsNullOrEmpty(option.CustomerCodeTo))
{
builder.Append($@"
AND {customerCode} <= @CustomerCodeTo");
}
builder.Append($@"
GROUP BY
{customerId}
, b.[CollectCategoryId]
, {staff}
, CASE
WHEN b.[DueAt] <= @dtprt THEN @dtprt
WHEN b.[DueAt] BETWEEN @dt0f AND @dt0t THEN @dt0t
WHEN b.[DueAt] BETWEEN @dt1f AND @dt1t THEN @dt1t
WHEN b.[DueAt] BETWEEN @dt2f AND @dt2t THEN @dt2t
WHEN b.[DueAt] >= @dt3f THEN @dt3t
END");
return(builder.ToString());
}
public async Task <ActionResult <IEnumerable <CollectionSchedule> > > Get(CollectionScheduleSearch option, CancellationToken token) => (await collectionScheduleProcessor.GetAsync(option, token, null)).ToArray();
public async Task <ActionResult <IEnumerable <CollectionSchedule> > > GetR(CollectionScheduleSearch option)
=> await hubContext.DoAsync(option.ConnectionId, async (notifier, token)
=> (await collectionScheduleProcessor.GetAsync(option, token, notifier)).ToArray());
/// <summary>回収予定表 データ抽出/整形</summary>
/// <param name="searchOption"></param>
/// <returns></returns>
/// <remarks>
/// 得意先 回収区分の情報をどのように取り扱うか ビジネスロジックはここに寄せる
/// </remarks>
public async Task <IEnumerable <CollectionSchedule> > GetAsync(CollectionScheduleSearch searchOption, CancellationToken token, IProgressNotifier notifier)
{
var initializeResult = searchOption.InitializeYearMonth();
var schedules = (await collectionScheduleQueryProcessor.GetAsync(searchOption, token, notifier))
.Where(x => x.HasAnyValue).ToList();
if (!(schedules?.Any() ?? false))
{
notifier?.UpdateState();
return(Enumerable.Empty <CollectionSchedule>());
}
const int collectCategoryType = 3;
const string ContractCode = "00";
var categories = (await categoryByCodeQueryProcessor.GetAsync(new CategorySearch {
CompanyId = searchOption.CompanyId, CategoryType = collectCategoryType
}, token)).ToList();
var contractId = categories.FirstOrDefault(x => x.Code == ContractCode)?.Id ?? 0;
var customerIds = schedules.Where(x => x.CustomerCollectCategoryId == contractId)
.Select(x => x.CustomerId).Distinct().ToArray();
var contracts = await customerPaymentContractQueryProcessor.GetAsync(customerIds, token);
var result = new List <CollectionSchedule>();
var groupCount = 0;
var subtotal = new Dictionary <string, Dictionary <int, decimal> >();
var subDpt = new Dictionary <string, Dictionary <int, decimal> >();
var subStf = new Dictionary <string, Dictionary <int, decimal> >();
var deptBuf = string.Empty;
var stafBuf = string.Empty;
var requireDepartmentSubtotal = searchOption.IsPrint && searchOption.NewPagePerDepartment;
var requireStaffSubtotal = searchOption.IsPrint && searchOption.NewPagePerStaff;
foreach (var group in schedules.GroupBy(x => x.CustomerId))
{
var detail = group.First();
var category = categories.FirstOrDefault(x => x.Id != contractId && x.Id == detail.CustomerCollectCategoryId);
var contract = contracts.FirstOrDefault(x => x.CustomerId == detail.CustomerId);
var customerInfo = GetCustomerInfo(detail, category, contract);
if (requireStaffSubtotal &&
!string.IsNullOrEmpty(stafBuf) && stafBuf != detail.StaffCode)
{
result.AddRange(GetSubtotalRecords(subStf, categories, deptBuf, stafBuf, 1, "担当者計", ref groupCount));
}
if (requireDepartmentSubtotal &&
!string.IsNullOrEmpty(deptBuf) && deptBuf != detail.DepartmentCode)
{
result.AddRange(GetSubtotalRecords(subDpt, categories, deptBuf, stafBuf, 2, "部門計", ref groupCount));
}
var list = new List <CollectionSchedule>();
CollectionSchedule contractPayment = null;
foreach (var item in group)
{
if (item.CollectCategoryCode == ContractCode)
{
contractPayment = item;
continue;
}
list.Add(item);
}
if (contractPayment != null && contract != null)
{
foreach (var divided in Divide(contractPayment, contract))
{
var index = list.FindIndex(x => x.CollectCategoryCode == divided.CollectCategoryCode);
if (index < 0)
{
list.Add(divided);
continue;
}
list[index].UncollectedAmountLast += divided.UncollectedAmountLast;
list[index].UncollectedAmount0 += divided.UncollectedAmount0;
list[index].UncollectedAmount1 += divided.UncollectedAmount1;
list[index].UncollectedAmount2 += divided.UncollectedAmount2;
list[index].UncollectedAmount3 += divided.UncollectedAmount3;
}
list.Sort((x, y) => string.Compare(x.CollectCategoryCode, y.CollectCategoryCode));
}
for (var i = 0; i < list.Count; i++)
{
if (i == 0)
{
list[i].RowId = ++groupCount;
}
else
{
list[i].DepartmentName = string.Empty;
list[i].StaffName = string.Empty;
list[i].ClosingDay = null;
}
var code = list[i].CollectCategoryCode;
if (!subtotal.ContainsKey(code))
{
subtotal.Add(code, Pivot(null));
}
if (requireDepartmentSubtotal && !subDpt.ContainsKey(code))
{
subDpt.Add(code, Pivot(null));
}
if (requireStaffSubtotal && !subStf.ContainsKey(code))
{
subStf.Add(code, Pivot(null));
}
if (!list[i].HasAnyValue)
{
continue;
}
AddValue(subtotal[code], list[i]);
if (requireDepartmentSubtotal)
{
AddValue(subDpt[code], list[i]);
}
if (requireStaffSubtotal)
{
AddValue(subStf[code], list[i]);
}
}
for (var i = 0; i < customerInfo.Count; i++)
{
if (list.Count <= i)
{
var item = new CollectionSchedule();
item.CustomerId = detail.CustomerId;
item.StaffCode = detail.StaffCode;
item.DepartmentCode = detail.DepartmentCode;
list.Add(item);
}
list[i].CustomerInfo = customerInfo[i];
}
result.AddRange(list);
deptBuf = detail.DepartmentCode;
stafBuf = detail.StaffCode;
}
if (requireStaffSubtotal)
{
result.AddRange(GetSubtotalRecords(subStf, categories, deptBuf, stafBuf, 1, "担当者計", ref groupCount));
}
if (requireDepartmentSubtotal)
{
result.AddRange(GetSubtotalRecords(subDpt, categories, deptBuf, stafBuf, 2, "部門計", ref groupCount));
}
// grand total
{
// category total
result.AddRange(GetSubtotalRecords(new Dictionary <string, Dictionary <int, decimal> >(subtotal), categories, "", "", 3, "合計", ref groupCount));
var item = GetNewItem(string.Empty, string.Empty);
item.CustomerInfo = "総合計";
item.RowId = ++groupCount;
item.RecordType = 4;
foreach (var key in subtotal.Keys)
{
foreach (var field in subtotal[key].Keys)
{
SetValue(item, field, subtotal[key][field]);
}
}
result.Add(item);
}
if (searchOption.UnitPrice != 1M)
{
foreach (var item in result)
{
item.Truncate(searchOption.UnitPrice);
}
}
notifier?.UpdateState();
return(result);
}
