public async Task <byte[]> GetAsync(BillingSearch option, CancellationToken token = default(CancellationToken))
{
var companyTask = companyQueryProcessor.GetAsync(new CompanySearch {
Id = option.CompanyId,
}, token);
var loadTask = billingSearchQueryProcessor.GetAsync(option, token);
await Task.WhenAll(companyTask, loadTask);
var precision = 0; // currency related precision
var company = companyTask.Result.First();
var items = loadTask.Result.ToList();
if (!items.Any())
{
return(null);
}
var report = new PaymentScheduleInputSectionReport();
report.SetBasicPageSetting(company.Code, company.Name);
report.Name = "入金予定リスト" + DateTime.Today.ToString("yyyyMMdd");
report.SetData(items, precision);
report.Run();
return(report.Convert());
}
private async Task <List <Billing> > GetBillingAsync(BillingSearch option)
=> await ServiceProxyFactory.DoAsync(async (BillingServiceClient client) => {
var result = await client.GetDueAtModifyItemsAsync(SessionKey, option);
if (result.ProcessResult.Result)
{
return(result.Billings);
}
return(new List <Billing>());
});
private async Task <BillingsResult> LoadBillingAsync(BillingSearch searchOption)
{
var useDepartmentFilter = Departments.Count != DepartmentIdsInner.Count;
if (useDepartmentFilter)
{
await Util.SaveWorkDepartmentTargetAsync(Login, ClientKey, DepartmentIdsInner.ToArray());
}
return(await ServiceProxyFactory.DoAsync(async (BillingServiceClient client)
=> await client.GetItemsAsync(SessionKey, searchOption)));
}
private async Task <List <Billing> > GetBillingByInputIdAsync(long inputId) =>
await ServiceProxyFactory.DoAsync(async (BillingService.BillingServiceClient client) =>
{
var option = new BillingSearch {
CompanyId = CompanyId,
BillingInputId = inputId,
};
var result = await client.GetItemsAsync(SessionKey, option);
if (result.ProcessResult.Result)
{
return(result.Billings);
}
return(new List <Billing>());
});
public async Task <BillingsResult> GetItemsAsync(string SessionKey, BillingSearch BillingSearch)
{
return(await authorizationProcessor.DoAuthorizeAsync(SessionKey, async token =>
{
var result = (await billingSearchProcessor.GetAsync(BillingSearch, token)).ToList();
return new BillingsResult
{
ProcessResult = new ProcessResult {
Result = true
},
Billings = result,
};
}, logger));
}
public Task <IEnumerable <BillingDueAtModify> > GetAsync(BillingSearch option, CancellationToken token = default(CancellationToken))
{
var query = new StringBuilder(@"
SELECT b.*
, cs.Code [CustomerCode]
, cs.Name [CustomerName]
, ccy.Code [CurrencyCode]
, mct.Code [CollectCategoryCode]
, mct.Name [CollectCategoryName]
, oct.Code [OriginalCollectCategoryCode]
, oct.Name [OriginalCollectCategoryName]
FROM (
SELECT CASE COUNT(b.Id) WHEN 1 THEN MIN(b.Id) ELSE NULL END [Id]
, CASE COALESCE(b.BillingInputId, -1) WHEN -1 THEN NULL
ELSE COALESCE(b.BillingInputId, -1)
END [BillingInputId]
, MIN(b.CompanyId) [CompanyId]
, MIN(b.CurrencyId) [CurrencyId]
, MIN(b.CustomerId) [CustomerId]
, MIN(b.DepartmentId) [DepartmentId]
, MIN(b.StaffId) [StaffId]
, MAX(b.InvoiceCode) [InvoiceCode]
, MAX(b.BilledAt) [BilledAt]
, MAX(b.ClosingAt) [ClosingAt]
, MAX(b.DueAt) [DueAt]
, COALESCE(MAX(b.OriginalDueAt)
, MAX(b.DueAt)) [OriginalDueAt]
, CASE WHEN MAX(b.OriginalDueAt) IS NULL THEN NULL
ELSE MAX(b.DueAt) END [ModifiedDueAt]
, SUM(b.RemainAmount - b.OffsetAmount) [TargetAmount]
, MAX(b.DeleteAt) [DeleteAt]
, MAX(b.CollectCategoryId) [CollectCategoryId]
, COALESCE(MAX(b.OriginalCollectCategoryId)
, MAX(b.CollectCategoryId)) [OriginalCollectCategoryId]
, CASE WHEN MAX(b.OriginalCollectCategoryId) IS NULL THEN NULL
ELSE MAX(b.CollectCategoryId) END [ModifiedCollectCategoryId]
, MAX(b.UpdateBy) [UpdateBy]
, MAX(b.UpdateAt) [UpdateAt]
, MIN(b.Id) [MinId]
, COUNT(b.Id) [RecordCount]
FROM Billing b
LEFT JOIN BillingInput bi
ON bi.Id = b.BillingInputId
AND bi.PublishAt IS NOT NULL
WHERE b.CompanyId = @CompanyId
AND b.Id = b.Id
AND bi.Id IS NULL
GROUP BY
COALESCE(b.BillingInputId, b.Id)
, COALESCE(b.BillingInputId, -1)
HAVING SUM(b.RemainAmount - b.OffsetAmount) <> 0
AND MAX(b.DeleteAt) IS NULL
) b
INNER JOIN Customer cs ON cs.Id = b.CustomerId
INNER JOIN Currency ccy ON ccy.Id = b.CurrencyId
INNER JOIN Department dp ON dp.Id = b.DepartmentId
INNER JOIN Staff st ON st.Id = b.StaffId
INNER JOIN Category oct ON oct.Id = b.OriginalCollectCategoryId
LEFT JOIN Category mct ON mct.Id = b.ModifiedCollectCategoryId
WHERE b.MinId = b.MinId");
if (option.BsBilledAtFrom.HasValue)
{
query.Append(@"
AND b.BilledAt >= @BsBilledAtFrom");
}
if (option.BsBilledAtTo.HasValue)
{
query.Append(@"
AND b.BilledAt <= @BsBilledAtTo");
}
if (option.BsClosingAtFrom.HasValue)
{
query.Append(@"
AND b.ClosingAt >= @BsClosingAtFrom");
}
if (option.BsClosingAtTo.HasValue)
{
query.Append(@"
AND b.ClosingAt <= @BsClosingAtTo");
}
if (option.BsDueAtFrom.HasValue)
{
query.Append(@"
AND b.DueAt >= @BsDueAtFrom");
}
if (option.BsDueAtTo.HasValue)
{
query.Append(@"
AND b.DueAt <= @BsDueAtTo");
}
if (!string.IsNullOrEmpty(option.BsInvoiceCodeFrom))
{
query.Append(@"
AND b.InvoiceCode >= @BsInvoiceCodeFrom");
}
if (!string.IsNullOrEmpty(option.BsInvoiceCodeTo))
{
query.Append(@"
AND b.InvoiceCode <= @BsInvoiceCodeTo");
}
if (!string.IsNullOrEmpty(option.BsInvoiceCode))
{
option.BsInvoiceCode = Sql.GetWrappedValue(option.BsInvoiceCode);
query.Append(@"
AND b.InvoiceCode LIKE @BsInvoiceCode");
}
if (!string.IsNullOrEmpty(option.BsCustomerCodeFrom))
{
query.Append(@"
AND cs.Code >= @BsCustomerCodeFrom");
}
if (!string.IsNullOrEmpty(option.BsCustomerCodeTo))
{
query.Append(@"
AND cs.Code <= @BsCustomerCodeTo");
}
if (!string.IsNullOrEmpty(option.BsDepartmentCodeFrom))
{
query.Append(@"
AND dp.Code >= @BsDepartmentCodeFrom");
}
if (!string.IsNullOrEmpty(option.BsDepartmentCodeTo))
{
query.Append(@"
AND dp.Code <= @BsDepartmentCodeTo");
}
if (!string.IsNullOrEmpty(option.BsStaffCodeFrom))
{
query.Append(@"
AND st.Code >= @BsStaffCodeFrom");
}
if (!string.IsNullOrEmpty(option.BsStaffCodeTo))
{
query.Append(@"
AND st.Code <= @BsStaffCodeTo");
}
if (!string.IsNullOrEmpty(option.BsCurrencyCode))
{
query.Append(@"
AND ccy.Code = @BsCurrencyCode");
}
query.Append(@"
ORDER BY b.MinId ASC");
return(dbHelper.GetItemsAsync <BillingDueAtModify>(query.ToString(), option, token));
}
private BillingSearch GetSearchCondition()
{
var options = new BillingSearch();
options.CompanyId = CompanyId;
options.BsBilledAtFrom = datBilledAtFrom.Value;
options.BsBilledAtTo = datBilledAtTo.Value;
options.BsSalesAtFrom = datSalesAtFrom.Value;
options.BsSalesAtTo = datSalesAtTo.Value;
options.BsDueAtFrom = datDueAtFrom.Value;
options.BsDueAtTo = datDueAtTo.Value;
options.BsCustomerName = txtCusName.Text;
options.BsCustomerNameKana = txtCusNameKana.Text;
options.BsPayerName = txtPayerName.Text;
options.BsCustomerCodeFrom = txtCusCodeFrom.Text;
options.BsCustomerCodeTo = txtCusCodeTo.Text;
options.BsStaffCodeFrom = txtStaffCodeFrom.Text;
options.BsStaffCodeTo = txtStaffCodeTo.Text;
options.BsDepartmentCodeFrom = txtDepCodeFrom.Text;
options.BsDepartmentCodeTo = txtDepCodeTo.Text;
options.BsInvoiceCodeFrom = txtInvoceCodeFrom.Text;
options.BsInvoiceCodeTo = txtInvoceCodeTo.Text;
options.BsInvoiceCode = txtInvoiceCode.Text;
if (cmbAmountType.SelectedIndex == 0)
{
options.BsRemaingAmountFrom = nmbAmountFrom.Value;
options.BsRemaingAmountTo = nmbAmountTo.Value;
}
else
{
options.BsBillingAmountFrom = nmbAmountFrom.Value;
options.BsBillingAmountTo = nmbAmountTo.Value;
}
options.ExistsMemo = cbxMemo.Checked;
options.BsMemo = txtMemo.Text;
if (cmbInputType.ValueMember != "")
{
options.BsInputType = Convert.ToInt32(cmbInputType.SelectedItem.SubItems[1].Value);
}
options.BsUpdateAtFrom = datUpdateAtFrom.Value;
if (datUpdateAtTo.Value.HasValue)
{
var updatedTo = datUpdateAtTo.Value.Value;
options.BsUpdateAtTo = updatedTo.Date.AddDays(1).AddMilliseconds(-1);
}
options.BsUpdateBy = UpdateBy;
options.BsNote1 = txtNote1.Text;
options.BsNote2 = txtNote2.Text;
options.BsNote3 = txtNote3.Text;
options.BsNote4 = txtNote4.Text;
options.BsNote5 = txtNote5.Text;
options.BsNote6 = txtNote6.Text;
options.BsNote7 = txtNote7.Text;
options.BsNote8 = txtNote8.Text;
options.AssignmentFlg
= (int)AssignmentFlagChecked.NoAssignment
| (int)AssignmentFlagChecked.PartAssignment;
if (ParentCustomerId != null && ParentCustomerId != 0)
{
options.ParentCustomerId = ParentCustomerId ?? 0;
}
options.UseDepartmentWork = Departments.Count != DepartmentIdsInner.Count;
options.ClientKey = ClientKey;
options.LoginUserId = Login.UserId;
if (!string.IsNullOrEmpty(CurrencyCode))
{
options.BsCurrencyCode = CurrencyCode;
}
if (cmbSeikyuKubun.SelectedItem != null)
{
options.BsBillingCategoryId = Convert.ToInt32(cmbSeikyuKubun.SelectedItem.SubItems[1].Value);
}
options.KobetsuType = "Matching";
return(options);
}
private BillingSearch PrepareDataForOverWrite(BillingImport billingImport, int CompanyId,
List <ImporterSettingDetail> detailList)
{
if (!detailList.Any(x => x.DoOverwrite == 1))
{
return(null);
}
var option = new BillingSearch();
option.CompanyId = CompanyId;
foreach (var detail in detailList.Where(x => x.DoOverwrite == 1))
{
if (detail.Sequence == (int)Fields.CustomerCode)
{
option.CustomerId = billingImport.CustomerId;
}
if (detail.Sequence == (int)Fields.BilledAt)
{
option.BilledAt = billingImport.BilledAt;
}
if (detail.Sequence == (int)Fields.InvoiceCode)
{
option.InvoiceCode = billingImport.InvoiceCode;
}
if (detail.Sequence == (int)Fields.Note1)
{
option.Note1 = billingImport.Note1;
}
if (detail.Sequence == (int)Fields.Note2)
{
option.Note2 = billingImport.Note2;
}
if (detail.Sequence == (int)Fields.Note3)
{
option.Note3 = billingImport.Note3;
}
if (detail.Sequence == (int)Fields.Note4)
{
option.Note4 = billingImport.Note4;
}
if (detail.Sequence == (int)Fields.Note5)
{
option.Note5 = billingImport.Note5;
}
if (detail.Sequence == (int)Fields.Note6)
{
option.Note6 = billingImport.Note6;
}
if (detail.Sequence == (int)Fields.Note7)
{
option.Note7 = billingImport.Note7;
}
if (detail.Sequence == (int)Fields.Note8)
{
option.Note8 = billingImport.Note8;
}
if (detail.Sequence == (int)Fields.CurrencyCode)
{
option.CurrencyCode = billingImport.CurrencyCode;
}
}
return(option);
}
public async Task <IEnumerable <BillingDueAtModify> > GetDueAtModifyItems(BillingSearch option, CancellationToken token) => (await billingDueAtModifyProcessor.GetAsync(option, token)).ToArray();
public async Task <IEnumerable <Billing> > GetAsync(BillingSearch option, CancellationToken token = default(CancellationToken)) => await billingSearchQueryProcessor.GetAsync(option, token);
public async Task <BillingDueAtModifyResults> GetDueAtModifyItemsAsync(string sessionKey, BillingSearch option)
=> await authorizationProcessor.DoAuthorizeAsync(sessionKey, async token => {
var billings = (await billingDueAtModifyProcessor.GetAsync(option, token)).ToList();
return(new BillingDueAtModifyResults
{
ProcessResult = new ProcessResult {
Result = true
},
Billings = billings,
});
}, logger);
public async Task <byte[]> GetAsync(BillingSearch option, CancellationToken token = default(CancellationToken))
{
var companyTask = companyQueryProcessor.GetAsync(new CompanySearch {
Id = option.CompanyId,
}, token);
var loadTask = billingSearchQueryProcessor.GetAsync(option, token);
var reportTask = reportSettingQueryProcessor.GetAsync(option.CompanyId, ReportId, token);
var gridTask = gridSettingQueryProcessor.GetAsync(new GridSettingSearch {
CompanyId = option.CompanyId, GridId = GridId.BillingSearch,
}, token);
await Task.WhenAll(companyTask, gridTask, reportTask, loadTask);
var company = companyTask.Result.First();
var items = loadTask.Result.ToList();
if (!items.Any())
{
return(null);
}
var settings = reportTask.Result.ToList();
var gridDic = gridTask.Result.ToDictionary(x => x.ColumnName);
var orders = items.AsQueryable().OrderBy(x => 0);
var departmentSubtotal = settings.GetReportSetting <ReportDoOrNot>(DepartmentSubtotal) == ReportDoOrNot.Do;
var staffSubtotal = settings.GetReportSetting <ReportDoOrNot>(StaffSubtotal) == ReportDoOrNot.Do;
var customerSubtotal = settings.GetReportSetting <ReportDoOrNot>(CustomerSubtotal) == ReportDoOrNot.Do;
var unitPrice = settings.GetReportSetting <ReportUnitPrice>(UnitPrice);
var outputOrder = settings.GetReportSetting <ReportOutputOrder>(OutputOrder);
var orderDateType = settings.GetReportSetting <ReportBaseDate>(OrderDateType);
var billReport = new BillingServiceSearchSectionReport();
billReport.SetBasicPageSetting(company.Code, company.Name);
billReport.Name = $"請求データ一覧{DateTime.Today:yyyyMMdd}";
billReport.lblNote1.Text = gridDic["Note1"].ColumnNameJp;
switch (unitPrice)
{
case ReportUnitPrice.Per1000: billReport.UnitPrice = 1000M; break;
case ReportUnitPrice.Per10000: billReport.UnitPrice = 10000M; break;
case ReportUnitPrice.Per1000000: billReport.UnitPrice = 1000000M; break;
default: billReport.UnitPrice = 1M; break;
}
if (departmentSubtotal)
{
orders = orders.ThenBy(x => x.DepartmentCode);
}
else
{
billReport.gfDepartmentTotal.Visible = false;
}
if (staffSubtotal)
{
orders = orders.ThenBy(x => x.StaffCode);
}
else
{
billReport.gfStaffTotal.Visible = false;
}
if (customerSubtotal)
{
orders = orders.ThenBy(x => x.CustomerCode);
}
else
{
billReport.gfCustomerTotal.Visible = false;
}
switch (outputOrder)
{
case ReportOutputOrder.ByCustomerCode: orders = orders.ThenBy(x => x.CustomerCode); break;
case ReportOutputOrder.ByDate:
switch (orderDateType)
{
case ReportBaseDate.BilledAt: orders = orders.ThenBy(x => x.BilledAt); break;
case ReportBaseDate.SalesAt: orders = orders.ThenBy(x => x.SalesAt); break;
case ReportBaseDate.ClosingAt: orders = orders.ThenBy(x => x.ClosingAt); break;
case ReportBaseDate.DueAt: orders = orders.ThenBy(x => x.DueAt); break;
}
break;
case ReportOutputOrder.ById: orders = orders.ThenBy(x => x.Id); break;
}
billReport.DataSource = orders.ToList();
billReport.Run();
return(billReport.Convert());
}
/// <summary>Get Condition For Search Data </summary>
/// <returns>Search Condition For Billing</returns>
private BillingSearch GetSearchCondition()
{
var bls = new BillingSearch();
bls.CompanyId = CompanyId;
bls.LoginUserId = Login.UserId;
if (datBilledAtFrom.Value.HasValue)
{
bls.BsBilledAtFrom = datBilledAtFrom.Value.Value;
}
if (datBilledAtTo.Value.HasValue)
{
bls.BsBilledAtTo = datBilledAtTo.Value.Value;
}
if (datClosingAtFrom.Value.HasValue)
{
bls.BsClosingAtFrom = datClosingAtFrom.Value.Value;
}
if (datClosingAtTo.Value.HasValue)
{
bls.BsClosingAtTo = datClosingAtTo.Value.Value;
}
if (datDueAtFrom.Value.HasValue)
{
bls.BsDueAtFrom = datDueAtFrom.Value.Value;
}
if (datDueAtTo.Value.HasValue)
{
bls.BsDueAtTo = datDueAtTo.Value.Value;
}
if (!string.IsNullOrWhiteSpace(txtInvoiceCode.Text))
{
bls.BsInvoiceCode = txtInvoiceCode.Text;
}
if (!string.IsNullOrWhiteSpace(txtInvoiceCodeFrom.Text))
{
bls.BsInvoiceCodeFrom = txtInvoiceCodeFrom.Text;
}
if (!string.IsNullOrWhiteSpace(txtInvoiceCodeTo.Text))
{
bls.BsInvoiceCodeTo = txtInvoiceCodeTo.Text;
}
if (!string.IsNullOrWhiteSpace(txtCurrencyCode.Text))
{
bls.BsCurrencyCode = txtCurrencyCode.Text;
}
if (!string.IsNullOrWhiteSpace(txtDepartmentCodeFrom.Text))
{
bls.BsDepartmentCodeFrom = txtDepartmentCodeFrom.Text;
}
if (!string.IsNullOrWhiteSpace(txtDepartmentCodeTo.Text))
{
bls.BsDepartmentCodeTo = txtDepartmentCodeTo.Text;
}
if (!string.IsNullOrWhiteSpace(txtStaffCodeFrom.Text))
{
bls.BsStaffCodeFrom = txtStaffCodeFrom.Text;
}
if (!string.IsNullOrWhiteSpace(txtStaffCodeTo.Text))
{
bls.BsStaffCodeTo = txtStaffCodeTo.Text;
}
if (!string.IsNullOrWhiteSpace(txtCustomerCodeFrom.Text))
{
bls.BsCustomerCodeFrom = txtCustomerCodeFrom.Text;
}
if (!string.IsNullOrWhiteSpace(txtCustomerCodeTo.Text))
{
bls.BsCustomerCodeTo = txtCustomerCodeTo.Text;
}
var assingmentFlag
= (int)Rac.VOne.Common.Constants.AssignmentFlagChecked.NoAssignment
| (int)Rac.VOne.Common.Constants.AssignmentFlagChecked.PartAssignment;
bls.AssignmentFlg = assingmentFlag;
if (ApplicationControl.UseReceiptSection == 1 && cbxUseSectionFilter.Checked)
{
bls.UseSectionMaster = true;
}
else
{
bls.UseSectionMaster = false;
}
return(bls);
}
public async Task <ActionResult <IEnumerable <Billing> > > GetItems(BillingSearch option, CancellationToken token) => (await billingSearchProcessor.GetAsync(option, token)).ToArray();
public Task <IEnumerable <long> > GetItemsForImportAsync(BillingSearch option, CancellationToken token = default(CancellationToken))
{
var query = @"
SELECT b.Id
FROM Billing b
WHERE b.CompanyId = @CompanyId
AND b.AssignmentFlag IN (0,1)
AND b.DeleteAt IS NULL";
var whereCondition = new StringBuilder();
if (option.BilledAt.HasValue)
{
query += @"
AND b.BilledAt = @BilledAt";
}
if (option.BillingAmount != 0)
{
query += @"
AND b.BillingAmount = @BillingAmount";
}
if (option.TaxAmount != 0)
{
query += @"
AND b.TaxAmount = @TaxAmount";
}
if (!string.IsNullOrEmpty(option.InvoiceCode))
{
query += @"
AND b.InvoiceCode = @InvoiceCode";
}
if (option.Price != 0)
{
query += @"
AND b.Price = @Price";
}
if (option.TaxClassId.HasValue)
{
query += @"
AND b.TaxClassId = @TaxClassId";
}
if (!string.IsNullOrEmpty(option.Note1))
{
query += @"
AND b.Note1 = @Note1";
}
if (!string.IsNullOrEmpty(option.Note2))
{
query += @"
AND b.Note2 = @Note2";
}
if (!string.IsNullOrEmpty(option.Note3))
{
query += @"
AND b.Note3 = @Note3";
}
if (!string.IsNullOrEmpty(option.Note4))
{
query += @"
AND b.Note4 = @Note4";
}
if (!string.IsNullOrEmpty(option.Note5))
{
query += @"
AND b.Note5 = @Note5";
}
if (!string.IsNullOrEmpty(option.Note6))
{
query += @"
AND b.Note6 = @Note6";
}
if (!string.IsNullOrEmpty(option.Note7))
{
query += @"
AND b.Note7 = @Note7";
}
if (!string.IsNullOrEmpty(option.Note8))
{
query += @"
AND b.Note8 = @Note8";
}
if (option.CustomerId != 0)
{
query += @"
AND b.CustomerId = @CustomerId";
}
return(dbHelper.GetItemsAsync <long>(query, option, token));
}
public Task <IEnumerable <Billing> > GetAsync(BillingSearch option, CancellationToken token = default(CancellationToken))
{
// Matchingを介したReceipt集計クエリをJOINするか否か(検索速度を考慮)
var requireRecordedAt = option.RequireRecordedAt;
var query = new StringBuilder(@"
SELECT
b.Id
, b.CompanyId
, b.CurrencyId
, b.CustomerId
, b.DepartmentId
, b.StaffId
, b.BillingCategoryId
, b.InputType
, b.BillingInputId
, b.BilledAt
, b.ClosingAt
, b.SalesAt
, b.DueAt
, b.BillingAmount
, b.TaxAmount
, b.AssignmentAmount
, b.RemainAmount
, b.OffsetAmount
, b.AssignmentFlag
, b.Approved
, b.CollectCategoryId
, b.OriginalCollectCategoryId
, b.DebitAccountTitleId
, b.CreditAccountTitleId
, b.OutputAt
, b.PublishAt
, b.InvoiceCode
, b.TaxClassId
, b.Note1
, b.Note2
, b.Note3
, b.Note4
, b.DeleteAt
, b.RequestDate
, b.ResultCode
, b.TransferOriginalDueAt
, b.ScheduledPaymentKey
, b.Quantity
, b.UnitPrice
, b.UnitSymbol
, b.Price
, b.CreateBy
, b.CreateAt
, b.UpdateBy
, b.UpdateAt
, b.AccountTransferLogId
, b.DestinationId
, b.Note5
, b.Note6
, b.Note7
, b.Note8
, b.ReminderId
, b.DueAt BillingDueAt
, CASE WHEN b.OriginalDueAt IS NULL THEN b.DueAt ELSE b.OriginalDueAt END OriginalDueAt
, CASE WHEN b.OriginalDueAt IS NULL THEN NULL ELSE b.DueAt END ModifiedDueAt
, b.RemainAmount - b.OffsetAmount as PaymentAmount
, (b.RemainAmount - b.OffsetAmount) as TargetAmount
, bmm.Memo
, d.BillingId AS BillingDiscountId
, COALESCE(d.DiscountAmount , 0) DiscountAmount
, COALESCE(d.DiscountAmount1, 0) DiscountAmount1
, COALESCE(d.DiscountAmount2, 0) DiscountAmount2
, COALESCE(d.DiscountAmount3, 0) DiscountAmount3
, COALESCE(d.DiscountAmount4, 0) DiscountAmount4
, COALESCE(d.DiscountAmount5, 0) DiscountAmount5
, c.Code CustomerCode
, c.Name CustomerName
, c.Kana CustomerKana
, c.IsParent
, dp.Code DepartmentCode
, dp.Name DepartmentName
, cur.Code CurrencyCode
, cct.Code CollectCategoryCode
, cct.Name CollectCategoryName
, bct.Code BillingCategoryCode
, bct.Name BillingCategoryName
, st.Code StaffCode
, st.Name StaffName
, us.Code LoginUserCode
, us.Name LoginUserName
, CASE WHEN b.OriginalCollectCategoryId IS NULL THEN cct.Code +' : '+ cct.Name ELSE cate.Code +' : '+ cate.Name END CategoryCodeAndName
, CASE WHEN b.OriginalCollectCategoryId IS NULL THEN NULL ELSE cct.Code +' : '+ cct.Name END OrgCategoryCodeAndName
, bdc.ContractNumber
, bdc.Comfirm
, COALESCE(pc.Id , c.Id ) [ParentCustomerId]
, COALESCE(pc.Code, c.Code) [ParentCustomerCode]
, COALESCE(pc.Name, c.Name) [ParentCustomerName]
, COALESCE(pc.Kana, c.Kana) [ParentCustomerKana]
, binput.PublishAt AS BillingInputPublishAt
");
if (requireRecordedAt)
{
query.Append(@"
, mrg.FirstRecordedAt
, mrg.LastRecordedAt");
}
query.Append(@"
FROM Billing as b
LEFT JOIN Customer c ON c.Id = b.CustomerId
LEFT JOIN CustomerGroup cg ON c.Id = cg.ChildCustomerId
LEFT JOIN Customer pc ON pc.Id = cg.ParentCustomerId
LEFT JOIN Currency cur ON cur.Id = b.CurrencyId
LEFT JOIN Category cate ON cate.Id = b.OriginalCollectCategoryId
LEFT JOIN Category cct ON cct.Id = b.CollectCategoryId
LEFT JOIN Category bct ON bct.Id = b.BillingCategoryId
LEFT JOIN Department dp ON dp.Id = b.DepartmentId
LEFT JOIN Staff st ON st.Id = b.StaffId
LEFT JOIN BillingMemo bmm ON b.Id = bmm.BillingId
LEFT JOIN LoginUser us ON us.Id = b.UpdateBy
LEFT JOIN BillingDivisionContract bdc ON b.Id = bdc.BillingId
LEFT JOIN BillingInput binput ON b.BillingInputId = binput.Id
LEFT JOIN (
SELECT BillingId
, SUM(DiscountAmount) AS DiscountAmount
, SUM(CASE WHEN DiscountType = 1 THEN DiscountAmount ELSE 0 END) DiscountAmount1
, SUM(CASE WHEN DiscountType = 2 THEN DiscountAmount ELSE 0 END) DiscountAmount2
, SUM(CASE WHEN DiscountType = 3 THEN DiscountAmount ELSE 0 END) DiscountAmount3
, SUM(CASE WHEN DiscountType = 4 THEN DiscountAmount ELSE 0 END) DiscountAmount4
, SUM(CASE WHEN DiscountType = 5 THEN DiscountAmount ELSE 0 END) DiscountAmount5
FROM BillingDiscount
GROUP BY BillingId
) d
ON b.Id = d.BillingId
");
if (requireRecordedAt)
{
query.Append(@"
LEFT JOIN (
SELECT
m.BillingId,
MIN(r.RecordedAt) as FirstRecordedAt,
MAX(r.RecordedAt) as LastRecordedAt
FROM
Matching m
LEFT JOIN Receipt r ON m.ReceiptId = r.Id
GROUP BY
m.BillingId
) mrg
ON b.Id = mrg.BillingId
");
}
query.Append(@"
WHERE b.CompanyId = @CompanyId");
if (option.BsBilledAtFrom.HasValue)
{
query.AppendLine(" AND b.BilledAt >= @BsBilledAtFrom");
}
if (option.BsBilledAtTo.HasValue)
{
query.AppendLine(" AND b.BilledAt <= @BsBilledAtTo");
}
if (option.BsSalesAtFrom.HasValue)
{
query.AppendLine(" AND b.SalesAt >= @BsSalesAtFrom");
}
if (option.BsSalesAtTo.HasValue)
{
query.AppendLine(" AND b.SalesAt <= @BsSalesAtTo");
}
if (!string.IsNullOrEmpty(option.BsCustomerName))
{
option.BsCustomerName = Sql.GetWrappedValue(option.BsCustomerName);
query.AppendLine(" AND c.Name LIKE @BsCustomerName");
}
if (!string.IsNullOrEmpty(option.BsCustomerNameKana))
{
option.BsCustomerNameKana = Sql.GetWrappedValue(option.BsCustomerNameKana);
query.AppendLine(" AND c.Kana LIKE @BsCustomerNameKana");
}
if (!string.IsNullOrEmpty(option.BsPayerName))
{
option.BsPayerName = Sql.GetWrappedValue(option.BsPayerName);
query.AppendLine(@"
AND EXISTS (
SELECT 1 FROM KanaHistoryCustomer khc
WHERE khc.CustomerId = b.CustomerId AND khc.PayerName like @BsPayerName)");
}
if (option.BsClosingAtFrom.HasValue)
{
query.AppendLine(" AND b.ClosingAt >= @BsClosingAtFrom");
}
if (option.BsClosingAtTo.HasValue)
{
query.AppendLine(" AND b.ClosingAt <= @BsClosingAtTo");
}
if (!string.IsNullOrEmpty(option.BsScheduledPaymentKey))
{
option.BsScheduledPaymentKey = Sql.GetWrappedValue(option.BsScheduledPaymentKey);
query.AppendLine(" AND b.ScheduledPaymentKey LIKE @BsScheduledPaymentKey");
}
if (option.BsDueAtFrom.HasValue)
{
query.AppendLine(" AND b.DueAt >= @BsDueAtFrom");
}
if (option.BsDueAtTo.HasValue)
{
query.AppendLine(" AND b.DueAt <= @BsDueAtTo");
}
if (!string.IsNullOrEmpty(option.BsInvoiceCodeFrom))
{
query.AppendLine(" AND b.InvoiceCode >= @BsInvoiceCodeFrom");
}
if (!string.IsNullOrEmpty(option.BsInvoiceCodeTo))
{
query.AppendLine(" AND b.InvoiceCode <= @BsInvoiceCodeTo");
}
if (!string.IsNullOrEmpty(option.BsInvoiceCode))
{
option.BsInvoiceCode = Sql.GetWrappedValue(option.BsInvoiceCode);
query.AppendLine(" AND b.InvoiceCode LIKE @BsInvoiceCode");
}
if (!string.IsNullOrEmpty(option.BsCurrencyCode))
{
query.AppendLine(" AND cur.Code = @BsCurrencyCode");
}
if (!string.IsNullOrEmpty(option.BsDepartmentCodeFrom))
{
query.AppendLine(" AND dp.Code >= @BsDepartmentCodeFrom");
}
if (!string.IsNullOrEmpty(option.BsDepartmentCodeTo))
{
query.AppendLine(" AND dp.Code <= @BsDepartmentCodeTo");
}
if (!string.IsNullOrEmpty(option.BsStaffCodeFrom))
{
query.AppendLine(" AND st.Code >= @BsStaffCodeFrom");
}
if (!string.IsNullOrEmpty(option.BsStaffCodeTo))
{
query.AppendLine(" AND st.Code <= @BsStaffCodeTo");
}
if (option.IsParentFlg != 0)
{
query.AppendLine(@"
AND (
b.CustomerId IN (SELECT ChildCustomerId FROM CustomerGroup WHERE ParentCustomerId = @CustomerId )
OR b.CustomerId = @CustomerId
)");
}
else if (!string.IsNullOrEmpty(option.BsCustomerCode))
{
query.AppendLine(" AND b.CustomerId = @CustomerId");
}
if (!string.IsNullOrEmpty(option.BsCustomerCodeFrom))
{
query.AppendLine(" AND c.Code >= @BsCustomerCodeFrom");
}
if (!string.IsNullOrEmpty(option.BsCustomerCodeTo))
{
query.AppendLine(" AND c.Code <= @BsCustomerCodeTo");
}
if (option.BsRemaingAmountFrom.HasValue)
{
query.AppendLine(" AND b.RemainAmount >= @BsRemaingAmountFrom");
}
if (option.BsRemaingAmountTo.HasValue)
{
query.AppendLine(" AND b.RemainAmount <= @BsRemaingAmountTo");
}
if (option.BsBillingAmountFrom.HasValue)
{
query.AppendLine(" AND b.BillingAmount >= @BsBillingAmountFrom");
}
if (option.BsBillingAmountTo.HasValue)
{
query.AppendLine(" AND b.BillingAmount <= @BsBillingAmountTo");
}
if (option.BsBillingCategoryId != 0)
{
query.AppendLine(" AND b.BillingCategoryId = @BsBillingCategoryId");
}
if (option.CollectCategoryId != 0)
{
query.AppendLine(" AND b.CollectCategoryId = @CollectCategoryId");
}
if (!string.IsNullOrEmpty(option.BsInputType.ToString()) && option.BsInputType != 0)
{
query.AppendLine(" AND b.InputType = @BsInputType");
}
if (option.ExistsMemo)
{
query.AppendLine(" AND bmm.Memo IS NOT NULL");
}
if (!string.IsNullOrEmpty(option.BsMemo))
{
option.BsMemo = Sql.GetWrappedValue(option.BsMemo);
query.AppendLine(" AND bmm.Memo LIKE @BsMemo");
}
if (option.BsUpdateAtFrom.HasValue)
{
query.AppendLine(" AND b.UpdateAt >= @BsUpdateAtFrom");
}
if (option.BsUpdateAtTo.HasValue)
{
query.AppendLine(" AND b.UpdateAt <= @BsUpdateAtTo");
}
if (option.BsUpdateBy.HasValue)
{
query.AppendLine(" AND b.UpdateBy = @BsUpdateBy");
}
if (!string.IsNullOrEmpty(option.BsNote1))
{
option.BsNote1 = Sql.GetWrappedValue(option.BsNote1);
query.AppendLine(" AND b.Note1 LIKE @BsNote1");
}
if (!string.IsNullOrEmpty(option.BsNote2))
{
option.BsNote2 = Sql.GetWrappedValue(option.BsNote2);
query.AppendLine(" AND b.Note2 LIKE @BsNote2");
}
if (!string.IsNullOrEmpty(option.BsNote3))
{
option.BsNote3 = Sql.GetWrappedValue(option.BsNote3);
query.AppendLine(" AND b.Note3 LIKE @BsNote3");
}
if (!string.IsNullOrEmpty(option.BsNote4))
{
option.BsNote4 = Sql.GetWrappedValue(option.BsNote4);
query.AppendLine(" AND b.Note4 LIKE @BsNote4");
}
if (!string.IsNullOrEmpty(option.BsNote5))
{
option.BsNote5 = Sql.GetWrappedValue(option.BsNote5);
query.AppendLine(" AND b.Note5 LIKE @BsNote5");
}
if (!string.IsNullOrEmpty(option.BsNote6))
{
option.BsNote6 = Sql.GetWrappedValue(option.BsNote6);
query.AppendLine(" AND b.Note6 LIKE @BsNote6");
}
if (!string.IsNullOrEmpty(option.BsNote7))
{
option.BsNote7 = Sql.GetWrappedValue(option.BsNote7);
query.AppendLine(" AND b.Note7 LIKE @BsNote7");
}
if (!string.IsNullOrEmpty(option.BsNote8))
{
option.BsNote8 = Sql.GetWrappedValue(option.BsNote8);
query.AppendLine(" AND b.Note8 LIKE @BsNote8");
}
var flags = (AssignmentFlagChecked)option.AssignmentFlg;
var selectedFlags = new List <int>();
if (flags.HasFlag(AssignmentFlagChecked.NoAssignment))
{
selectedFlags.Add(0);
}
if (flags.HasFlag(AssignmentFlagChecked.PartAssignment))
{
selectedFlags.Add(1);
}
if (flags.HasFlag(AssignmentFlagChecked.FullAssignment))
{
selectedFlags.Add(2);
}
if (selectedFlags.Any() && !flags.HasFlag(AssignmentFlagChecked.All))
{
query.AppendLine($" AND b.AssignmentFlag IN ({(string.Join(", ", selectedFlags))})");
}
if (!string.IsNullOrEmpty(option.LoginUserCode))
{
query.AppendLine(" AND b.UpdateBy = @UserId");
}
if (option.UseSectionMaster &&
option.KobetsuType != "Matching")
{
query.AppendLine(@"
AND EXISTS (SELECT 1
FROM SectionWithLoginUser swl
INNER JOIN SectionWithDepartment swd
ON swl.LoginUserId = @LoginUserId
AND swl.SectionId = swd.SectionId
AND swd.DepartmentId = b.DepartmentId )");
}
if (option.UseDepartmentWork)
{
query.AppendLine(@"
AND b.DepartmentId IN (
SELECT DISTINCT wdt.DepartmentId
FROM WorkDepartmentTarget wdt
WHERE wdt.ClientKey = @ClientKey
AND wdt.UseCollation = 1 )");
}
if (option.RequestDate == 1)
{
query.AppendLine(" AND b.RequestDate IS NOT NULL");
}
if (option.CurrencyId != 0)
{
query.AppendLine(" AND cur.Id = @CurrencyId");
}
if (option.ParentCustomerId != 0)
{
query.AppendLine(@"
AND b.CustomerId NOT IN (SELECT ChildCustomerId
FROM CustomerGroup
UNION
SELECT ParentCustomerId
FROM CustomerGroup)
AND c.IsParent <> 1
");
}
if (option.IsDeleted)
{
query.AppendLine(" AND b.DeleteAt IS NOT NULL");
}
else
{
query.AppendLine(" AND b.DeleteAt IS NULL");
}
if (option.BsDeleteAtFrom.HasValue)
{
query.AppendLine(" AND b.DeleteAt >= @BsDeleteAtFrom");
}
if (option.BsDeleteAtTo.HasValue)
{
query.AppendLine(" AND b.DeleteAt <= @BsDeleteAtTo");
}
if (option.KobetsuType == "Matching")
{
query.AppendLine(" AND cct.UseAccountTransfer = 0");
}
if (option.BillingId.HasValue)
{
query.AppendLine(" AND b.Id = @BillingId");
}
if (option.BillingInputId.HasValue)
{
query.AppendLine(" AND b.BillingInputId = @BillingInputId");
}
query.Append(@"
ORDER BY b.Id
OPTION ( FORCE ORDER )
");
return(dbHelper.GetItemsAsync <Billing>(query.ToString(), option, token));
}
