public Task <InvoiceCommonSetting> SaveAsync(InvoiceCommonSetting setting, CancellationToken token = default(CancellationToken))
{
#region merge query
var query = @"
MERGE INTO InvoiceCommonSetting target
USING (
SELECT @CompanyId [CompanyId]
) source
ON (
target.CompanyId = source.CompanyId
)
WHEN MATCHED THEN
UPDATE SET
ExcludeAmountZero = @ExcludeAmountZero
,ExcludeMinusAmount = @ExcludeMinusAmount
,ExcludeMatchedData = @ExcludeMatchedData
,ControlInputCharacter = @ControlInputCharacter
,UpdateBy = @UpdateBy
,UpdateAt = GETDATE()
WHEN NOT MATCHED THEN
INSERT (CompanyId, ExcludeAmountZero, ExcludeMinusAmount, ExcludeMatchedData, CreateBy, CreateAt, UpdateBy, UpdateAt, ControlInputCharacter)
VALUES (@CompanyId, @ExcludeAmountZero, @ExcludeMinusAmount, @ExcludeMatchedData, @UpdateBy, GETDATE(), @UpdateBy, GETDATE(), @ControlInputCharacter)
OUTPUT inserted.*; ";
#endregion
return(dbHelper.ExecuteAsync <InvoiceCommonSetting>(query, setting, token));
}
public Task <IEnumerable <BillingInvoiceDetailForExport> > GetDetailsForExportAsync(
IEnumerable <long> BillingInputIds,
InvoiceCommonSetting invoiceCommonSetting,
CancellationToken token = default(CancellationToken))
{
return(dbHelper.GetItemsAsync <BillingInvoiceDetailForExport>(
GetQuerySelectBillingInvoiceDetailsForExport(invoiceCommonSetting),
new { BillingInputIds = BillingInputIds.GetTableParameter() },
token));
}
private async Task InitializeInvoiceCommonAsync(CompanySource source, LoginUser loginUser, CancellationToken token)
{
var invoiceCommonSetting = new InvoiceCommonSetting
{
CompanyId = source.Company.Id,
ExcludeAmountZero = 1,
ExcludeMinusAmount = 1,
ExcludeMatchedData = 1,
CreateBy = loginUser.Id,
UpdateBy = loginUser.Id,
};
await invoiceCommonSettingProcessor.SaveAsync(invoiceCommonSetting, token);
}
public Task <IEnumerable <BillingInvoiceDetailForPrint> > GetDetailsForPrintAsync(
BillingInvoiceDetailSearch option,
InvoiceCommonSetting invoiceCommonSetting, CancellationToken token = default(CancellationToken))
{
return(dbHelper.GetItemsAsync <BillingInvoiceDetailForPrint>(
GetQuerySelectBillingInvoiceDetailsForPrint(option, invoiceCommonSetting),
new
{
option.ClientKey,
BillingInputIds = option.BillingInputIds.GetTableParameter(),
TemporaryBillingInputIds = option.TemporaryBillingInputIds.GetTableParameter(),
option.InvoiceTemplateId
}, token));
}
public Task <IEnumerable <BillingInvoice> > GetAsync(BillingInvoiceSearch searchOption,
InvoiceCommonSetting invoiceCommonSetting,
CancellationToken token = default(CancellationToken))
{
return(dbHelper.ExecuteQueriesAsync(async connection =>
{
await dbHelper.ExecuteAsync(connection,
GetQueryInsertWorkBillingInvoices(searchOption),
searchOption, token);
return await dbHelper.QueryAsync <BillingInvoice>(connection,
GetQuerySelectBillingInvoices(searchOption, invoiceCommonSetting),
searchOption,
token);
}));
}
public async Task <ActionResult <InvoiceCommonSetting> > SaveInvoiceCommonSetting(InvoiceCommonSetting setting, CancellationToken token) => await invoiceCommonSettingProcessor.SaveAsync(setting);
public async Task <InvoiceCommonSettingResult> SaveInvoiceCommonSettingAsync(string SessionKey, InvoiceCommonSetting InvoiceCommonSetting)
{
return(await authorizationProcessor.DoAuthorizeAsync(SessionKey, async token =>
{
var result = await invoiceCommonSettingProcessor.SaveAsync(InvoiceCommonSetting, token);
return new InvoiceCommonSettingResult
{
ProcessResult = new ProcessResult {
Result = true
},
InvoiceCommonSetting = result,
};
}, logger));
}
private string GetQuerySelectBillingInvoiceDetailsForExport(InvoiceCommonSetting invoiceCommonSetting)
{
var builder = new StringBuilder();
builder.Append(@"
SELECT com.Code AS CompanyCode
, b.BillingInputId
, b.Id AS BillingId
, b.BilledAt
, b.ClosingAt
, b.SalesAt
, b.DueAt
, b.BillingAmount
, b.TaxAmount
, b.Price
, b.RemainAmount
, b.InvoiceCode
, b.Note1
, b.Note2
, b.Note3
, b.Note4
, b.Note5
, b.Note6
, b.Note7
, b.Note8
, bm.Memo
, bdc.ContractNumber
, b.Quantity
, b.UnitSymbol
, b.UnitPrice
, bi.PublishAt
, bi.PublishAt1st
, b.AssignmentFlag
, dp.Code AS DepartmentCode
, dp.Name AS DepartmentName
, bc.Code AS BillingCategoryCode
, bc.Name AS BillingCategoryName
, bc.ExternalCode AS BillingCategoryExternalCode
, b.TaxClassId
, tc.Name AS TaxClassName
, cc.Code AS CollectCategoryCode
, cc.Name AS CollectCategoryName
, cc.ExternalCode AS CollectCategoryExternalCode
, s.Code AS StaffCode
, s.Name AS StaffName
, cus.Code AS CustomerCode
, cus.Name AS CustomerName
, cus.ShareTransferFee
, cus.PostalCode AS CustomerPostalCode
, COALESCE(de.Address1, cus.Address1) AS CustomerAddress1
, COALESCE(de.Address2, cus.Address2) AS CustomerAddress2
, COALESCE(de.DepartmentName, cus.DestinationDepartmentName) AS CustomerDepartmentName
, COALESCE(de.Addressee, cus.CustomerStaffName) AS CustomerAddressee
, COALESCE(de.Honorific, cus.Honorific) AS CustomerHonorific
, cus.Note AS CustomerNote
, cus.ExclusiveBankCode
, cus.ExclusiveBankName
, cus.ExclusiveBranchCode
, LEFT(cus.ExclusiveAccountNumber, 3) AS VirtualBranchCode
, cus.ExclusiveBranchName
, RIGHT(cus.ExclusiveAccountNumber, 7) AS VirtualAccountNumber
, cus.ExclusiveAccountTypeId
, com.BankName1 AS CompanyBankName1
, com.BranchName1 AS CompanyBranchName1
, com.AccountType1 AS CompanyAccountType1
, com.AccountNumber1 AS CompanyAccountNumber1
, com.BankName2 AS CompanyBankName2
, com.BranchName2 AS CompanyBranchName2
, com.AccountType2 AS CompanyAccountType2
, com.AccountNumber2 AS CompanyAccountNumber2
, com.BankName3 AS CompanyBankName3
, com.BranchName3 AS CompanyBranchName3
, com.AccountType3 AS CompanyAccountType3
, com.AccountNumber3 AS CompanyAccountNumber3
, com.BankAccountName AS CompanyBankAccountName
, com.Name AS CompanyName
, com.PostalCode AS CompanyPostalCode
, com.Address1 AS CompanyAddress1
, com.Address2 AS CompanyAddress2
, com.Tel AS CompanyTel
, com.Fax AS CompanyFax
FROM Billing b
LEFT JOIN Company com
ON com.Id = b.CompanyId
LEFT JOIN BillingMemo bm
ON bm.BillingId = b.Id
LEFT JOIN BillingDivisionContract bdc
ON bdc.BillingId = b.Id
LEFT JOIN BillingInput bi
ON bi.Id = b.BillingInputId
LEFT JOIN Department dp
ON dp.Id = b.DepartmentId
LEFT JOIN Category bc
ON bc.Id = b.BillingCategoryId
LEFT JOIN TaxClass tc
ON tc.Id = b.TaxClassId
LEFT JOIN Category cc
ON cc.Id = b.CollectCategoryId
LEFT JOIN Staff s
ON s.Id = b.StaffId
LEFT JOiN Customer cus
ON cus.Id = b.CustomerId
LEFT JOIN Destination de
ON de.Id = b.DestinationId
WHERE b.BillingInputId IN (SELECT Id FROM @BillingInputIds)
");
if (invoiceCommonSetting.ExcludeMatchedData != 0)
{
builder.AppendLine(" AND b.AssignmentFlag <> 2");
}
builder.AppendLine(" ORDER BY b.BillingInputId, b.SalesAt, b.Id");
return(builder.ToString());
}
private string GetQuerySelectBillingInvoiceDetailsForPrint(
BillingInvoiceDetailSearch billingInvoiceDetailSearch,
InvoiceCommonSetting invoiceCommonSetting)
{
var templateId = billingInvoiceDetailSearch.TemporaryBillingInputIds != null
? "@InvoiceTemplateId"
: "bi.InvoiceTemplateId";
var builder = new StringBuilder(@"
SELECT b.BillingInputId
, b.InvoiceCode
, b.BilledAt
, b.ClosingAt
, b.SalesAt
, b.DueAt
, b.BillingAmount
, CASE b.TaxClassId WHEN 0 THEN b.TaxAmount
ELSE 0
END AS TaxAmount
, CASE b.TaxClassId WHEN 0 THEN (b.BillingAmount - b.TaxAmount)
ELSE b.BillingAmount
END AS TaxExcludedPrice
, b.RemainAmount
, CASE b.TaxClassId WHEN 1 THEN '税込'
WHEN 0 THEN '税抜'
ELSE tc.Name
END AS TaxClassName
, b.Note1
, b.Note2
, CASE b.Quantity WHEN 0 THEN NULL
ELSE b.Quantity
END AS Quantity
, CASE b.UnitPrice WHEN 0 THEN NULL
ELSE b.UnitPrice
END AS UnitPrice
, b.UnitSymbol
, s.Name AS StaffName
, it.DisplayStaff
, '' AS Tel
, '' AS Fax
, c.PostalCode AS CustomerPostalCode
, c.Address1 AS CustomerAddress1
, c.Address2 AS CustomerAddress2
, c.Code AS CustomerCode
, c.Name AS CustomerName
, c.DestinationDepartmentName AS CustomerDestinationDepartmentName
, c.CustomerStaffName AS CustomerStaffName
, c.Honorific AS CustomerHonorific
, c.ShareTransferFee
");
if (billingInvoiceDetailSearch.DestinationId.HasValue)
{
builder.AppendFormat(@" , {0} AS DestinationId
", billingInvoiceDetailSearch.DestinationId.Value);
}
else
{
builder.AppendLine(" , b.DestinationId");
}
builder.AppendLine(@"
, des.Name AS DestinationName
, des.PostalCode AS DestinationPostalCode
, des.Address1 AS DestinationAddress1
, des.Address2 AS DestinationAddress2
, des.DepartmentName AS DestinationDepartmentName
, des.Addressee AS DestinationAddressee
, des.Honorific AS DestinationHonorific
, it.Title
, it.Greeting
, it.DueDateComment
, it.DueDateFormat
, it.TransferFeeComment
, c.ReceiveAccountId1
, c.ReceiveAccountId2
, c.ReceiveAccountId3
, ('振込銀行：'
+ c.ExclusiveBankName
+ ' '
+ c.ExclusiveBranchName
+ ' '
+ at.Name
+ ' '
+ RIGHT(c.ExclusiveAccountNumber, 7)
) AS ExclusiveAccount
");
if (billingInvoiceDetailSearch.TemporaryBillingInputIds != null)
{
builder.Append(@"
, (SELECT COUNT(*)
FROM WorkBillingInvoice wbi2
WHERE wbi2.ClientKey = @ClientKey
AND wbi2.TemporaryBillingInputId = wbi.TemporaryBillingInputId
) AS DetailCount
FROM Billing b
");
}
else
{
builder.Append(@"
, (SELECT COUNT(*)
FROM Billing b2
WHERE b2.BillingInputId = b.BillingInputId
) AS DetailCount
FROM Billing b
");
}
if (billingInvoiceDetailSearch.TemporaryBillingInputIds != null)
{
builder.Append(@"
INNER JOIN WorkBillingInvoice wbi
ON wbi.ClientKey = @ClientKey
AND wbi.BillingId = b.Id
");
}
builder.Append($@"
LEFT join Customer c
ON c.Id = b.CustomerId
LEFT JOIN TaxClass tc
ON tc.Id = b.TaxClassId
LEFT JOIN Staff s
ON s.Id = b.StaffId
LEFT JOIN BillingInput bi
ON bi.Id = b.BillingInputId
LEFT JOIN InvoiceTemplateSetting it
ON it.Id = { templateId }
LEFT JOIN BankAccountType at
ON at.Id = c.ExclusiveAccountTypeId
LEFT JOIN Destination des
");
if (billingInvoiceDetailSearch.DestinationId.HasValue)
{
builder.AppendFormat(@" ON des.Id = {0}
", billingInvoiceDetailSearch.DestinationId.Value);
}
else
{
builder.AppendLine(" ON des.Id = b.DestinationId");
}
if (billingInvoiceDetailSearch.TemporaryBillingInputIds != null)
{
builder.AppendLine("WHERE wbi.TemporaryBillingInputId IN (SELECT Id FROM @TemporaryBillingInputIds)");
}
else
{
builder.AppendLine("WHERE b.BillingInputId IN (SELECT Id FROM @BillingInputIds)");
}
if (invoiceCommonSetting.ExcludeMatchedData != 0)
{
builder.AppendLine(" AND b.AssignmentFlag <> 2");
}
builder.AppendLine(" ORDER BY b.BilledAt asc, c.Code asc, des.Code asc, b.BillingInputId asc");
return(builder.ToString());
}
private string GetQuerySelectBillingInvoices(BillingInvoiceSearch option,
InvoiceCommonSetting invoiceCommonSetting)
{
var select1 = option.IsPublished ?
"0"
: "1";
var select2 = option.IsPublished ?
"bi.InvoiceTemplateId AS InvoiceTemplateId"
: "COALESCE(its.Id, 0) AS InvoiceTemplateId";
var builder = new StringBuilder();
builder.Append($@"
declare @CompanyCode nvarchar(20) = (SELECT Code From Company WHERE Id = @CompanyId)
SELECT inv.TemporaryBillingInputId
, @CompanyCode AS CompanyCode
, inv.BillingInputId
, {select1} AS Checked
, its.Code AS InvoiceTemplateCode
, {select2}
, inv.InvoiceCode
, inv.DetailsCount
, inv.CustomerId
, cus.Code AS CustomerCode
, cus.Name AS CustomerName
, cus.PostalCode AS CustomerPostalCode
, cus.Address1 AS CustomerAddress1
, cus.Address2 AS CustomerAddress2
, cus.DestinationDepartmentName AS CustomerDestinationDepartmentName
, cus.CustomerStaffName AS CustomerStaffName
, cus.Honorific AS CustomerHonorific
, inv.AmountSum
, inv.RemainAmountSum
, (cat.code + ':' + cat.name) AS CollectCategoryCodeAndNeme
, inv.ClosingAt
, inv.BilledAt
, dep.Code AS DepartmentCode
, dep.Name AS DepartmentName
, sta.code AS StaffCode
, sta.name AS StaffName
, inv.DestinationId
, dest.code AS DestnationCode
, dest.Name AS DestinationName
, dest.PostalCode AS DestinationPostalCode
, dest.Address1 AS DestinationAddress1
, dest.Address2 AS DestinationAddress2
, dest.DepartmentName AS DestinationDepartmentName
, dest.Addressee AS DestinationAddressee
, dest.Honorific AS DestinationHonorific
, bi.PublishAt
, bi.PublishAt1st
, inv.UpdateAt
FROM (
SELECT wbi.TemporaryBillingInputId
, wbi.BillingInputId
, b.CompanyId
, b.CurrencyId
, b.CustomerId
, b.DepartmentId
, b.StaffId
, b.DestinationId
, b.BilledAt
, b.ClosingAt
, b.DueAt
, b.InvoiceCode
, SUM(b.BillingAmount) AS AmountSum
, SUM(b.RemainAmount) AS RemainAmountSum
, b.CollectCategoryId
, COUNT(*) AS DetailsCount
, CASE WHEN (MAX(b.AssignmentFlag) = MIN(b.AssignmentFlag) AND MAX(b.AssignmentFlag) = 0) THEN 0
WHEN (MAX(b.AssignmentFlag) = MIN(b.AssignmentFlag) AND MAX(b.AssignmentFlag) = 2) THEN 2
ELSE 1
END AS AssignmentFlagForInvoice
, MAX(b.UpdateAt) AS UpdateAt
FROM WorkBillingInvoice wbi
INNER JOIN Billing b
ON b.Id = wbi.BillingId
WHERE wbi.ClientKey = @ClientKey
GROUP BY wbi.TemporaryBillingInputId
, wbi.BillingInputId
, b.CompanyId
, b.CurrencyId
, b.CustomerId
, b.DepartmentId
, b.StaffId
, b.DestinationId
, b.BilledAt
, b.ClosingAt
, b.DueAt
, b.InvoiceCode
, b.CollectCategoryId
) AS inv
LEFT JOIN BillingInput bi
ON bi.Id = inv.BillingInputId
LEFT JOIN Customer cus
ON cus.Id = inv.CustomerId
LEFT JOIN Category cat
ON cat.id = inv.CollectCategoryId
LEFT JOIN Department dep
ON dep.id = inv.departmentId
LEFT JOIN Staff sta
ON sta.Id = inv.StaffId
LEFT JOIN Destination dest
ON dest.id = inv.DestinationId
LEFT JOIN InvoiceTemplateSetting its
ON its.CollectCategoryId = inv.CollectCategoryId
WHERE 1 = 1
");
//検索条件
if (option.IsPublished)
{
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))
{
builder.AppendLine($" AND inv.AssignmentFlagForInvoice IN ({(string.Join(", ", selectedFlags))})");
}
if (option.PublishAtFrom.HasValue)
{
builder.AppendLine(" AND bi.PublishAt >= @PublishAtFrom");
}
if (option.PublishAtTo.HasValue)
{
builder.AppendLine(" AND bi.PublishAt <= @PublishAtTo");
}
if (option.PublishAtFirstFrom.HasValue)
{
builder.AppendLine(" AND bi.PublishAt1st >= @PublishAtFirstFrom");
}
if (option.PublishAtFirstTo.HasValue)
{
builder.AppendLine(" AND bi.PublishAt1st <= @PublishAtFirstTo");
}
}
//除外設定
if (invoiceCommonSetting.ExcludeAmountZero == 1)
{
if (option.ReportInvoiceAmount == 0)
{
builder.AppendLine(" AND inv.AmountSum <> 0");
}
else
{
builder.AppendLine(" AND inv.RemainAmountSum <> 0");
}
}
if (invoiceCommonSetting.ExcludeMinusAmount == 1)
{
if (option.ReportInvoiceAmount == 0)
{
builder.AppendLine(" AND inv.AmountSum >= 0");
}
else
{
builder.AppendLine(" AND inv.RemainAmountSum >= 0");
}
}
if (invoiceCommonSetting.ExcludeMatchedData == 1)
{
builder.AppendLine(" AND inv.AssignmentFlagForInvoice <> 2");
}
builder.AppendLine(" ORDER BY inv.BilledAt asc, cus.Code asc, dest.code asc, inv.BillingInputId asc, inv.TemporaryBillingInputId asc");
return(builder.ToString());
}
public async Task <InvoiceCommonSetting> SaveAsync(InvoiceCommonSetting seting, CancellationToken token = default(CancellationToken)) => await addInvoiceCommonSettingQueryProcessor.SaveAsync(seting, token);
