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