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); }