public Task <int> UpdateAsync(JournalizingOption option, CancellationToken token = default(CancellationToken))
{
var query = @"
UPDATE ar
SET ar.TransferOutputAt = @UpdateAt
FROM AdvanceReceivedBackup ar
WHERE ar.CompanyId = @CompanyId
AND ar.CurrencyId = @CurrencyId
AND ar.TransferOutputAt IS NULL
AND EXISTS (
SELECT 1
FROM Receipt r
WHERE r.OriginalReceiptId = ar.OriginalReceiptId
AND r.CompanyId = @CompanyId
AND r.CurrencyId = @CurrencyId
AND r.OutputAt = @UpdateAt";
if (option.RecordedAtFrom.HasValue)
{
query += @"
AND r.RecordedAt >= @RecordedAtFrom";
}
if (option.RecordedAtTo.HasValue)
{
query += @"
AND r.RecordedAt <= @RecordedAtTo";
}
query += @" )
";
return(dbHelper.ExecuteAsync(query, option, token));
}
public Task <int> UpdateAsync(JournalizingOption option, CancellationToken token = default(CancellationToken))
{
var query = $@"
UPDATE re
SET re.OutputAt = @UpdateAt
, re.UpdateAt = @UpdateAt
, re.UpdateBy = @LoginUserId
FROM ReceiptExclude re
INNER JOIN Receipt r
ON r.Id = re.ReceiptId
AND r.CompanyId = @CompanyId
AND r.CurrencyId = @CurrencyId
AND r.Apportioned = 1
AND r.Approved = 1
AND re.OutputAt IS NULL
";
if (option.RecordedAtFrom.HasValue)
{
query += @"
AND r.RecordedAt >= @RecordedAtFrom";
}
if (option.RecordedAtTo.HasValue)
{
query += @"
AND r.RecordedAt <= @RecordedAtTo";
}
return(dbHelper.ExecuteAsync(query, option, token));
}
public Task <int> UpdateAsync(JournalizingOption option, CancellationToken token = default(CancellationToken))
{
var query = @"
UPDATE r
SET r.OutputAt = @UpdateAt
, r.UpdateAt = @UpdateAt
, r.UpdateBy = @LoginUserId
FROM Receipt r
INNER JOIN Category ct
ON ct.Id = r.ReceiptCategoryId
AND ct.UseAdvanceReceived = 1
AND r.CompanyId = @CompanyId
AND r.CurrencyId = @CurrencyId
AND r.Apportioned = 1
AND r.Approved = 1
AND r.OutputAt IS NULL
AND r.DeleteAt IS NULL
";
if (option.RecordedAtFrom.HasValue)
{
query += @"
AND r.RecordedAt >= @RecordedAtFrom";
}
if (option.RecordedAtTo.HasValue)
{
query += @"
AND r.RecordedAt <= @RecordedAtTo";
}
return(dbHelper.ExecuteAsync(query, option, token));
}
public async Task <CountResult> CancelAsync(string SessionKey, JournalizingOption option)
=> await authorizationProcessor.DoAuthorizeAsync(SessionKey, async token => {
var result = await hatarakuDBJournalizingProcessor.CancelAsync(option, token);
return(new CountResult {
ProcessResult = new ProcessResult {
Result = true
},
Count = result,
});
}, logger);
public async Task <HatarakuDBDataResult> ExtractAsync(string SessionKey, JournalizingOption option)
=> await authorizationProcessor.DoAuthorizeAsync(SessionKey, async token => {
var result = await hatarakuDBJournalizingProcessor.ExtractAsync(option, token);
return(new HatarakuDBDataResult
{
ProcessResult = new ProcessResult {
Result = true
},
HatarakuDBData = result.ToList(),
});
}, logger);
public async Task <CountResult> UpdateOutputAtAsync(string SessionKey, JournalizingOption option)
=> await authorizationProcessor.DoAuthorizeAsync(SessionKey, async token =>
{
var count = await receiptJournalizingProcessor.UpdateOutputAtAsync(option, token);
return(new CountResult
{
ProcessResult = new ProcessResult {
Result = true
},
Count = count,
});
}, logger);
public async Task <int> CancelJournalizingAsync(JournalizingOption option,
CancellationToken token = default(CancellationToken))
{
using (var scope = transactionScopeBuilder.Create())
{
var count = await updateReceiptJournalizingQueryProcessor.CancelAsync(option, token);
if (count > 0)
{
scope.Complete();
}
return(count);
}
}
public Task <IEnumerable <JournalizingSummary> > GetSummaryAsync(JournalizingOption option, CancellationToken token = default(CancellationToken))
{
var query = @"
SELECT r.OutputAt
, Count(*) [Count]
, ccy.Code [CurrencyCode]
, SUM( r.ReceiptAmount ) [Amount]
, MAX( r.UpdateAt ) [UpdateAt]
FROM Receipt r
INNER JOIN Currency ccy
ON ccy.Id = r.CurrencyId
AND r.CompanyId = @CompanyId
AND r.CurrencyId = @CurrencyId
AND r.Apportioned = 1
AND r.Approved = 1
AND r.DeleteAt IS NULL";
if (!option.IsOutputted)
{
query += @"
AND r.OutputAt IS NULL ";
}
else
{
query += @"
AND r.OutputAt IS NOT NULL ";
}
if (option.RecordedAtFrom.HasValue)
{
query += @"
AND r.RecordedAt >= @RecordedAtFrom ";
}
if (option.RecordedAtTo.HasValue)
{
query += @"
AND r.RecordedAt <= @RecordedAtTo ";
}
query += @"
INNER JOIN Category ct
ON ct.Id = r.ReceiptCategoryId
AND ct.UseAdvanceReceived = 0
GROUP BY r.OutputAt, ccy.Code
ORDER BY r.OutputAt DESC
";
return(dbHelper.GetItemsAsync <JournalizingSummary>(query, option, token));
}
public Task <IEnumerable <JournalizingSummary> > GetSummaryAsync(JournalizingOption option, CancellationToken token = default(CancellationToken))
{
var matchingCondition = (option.IsOutputted)
? @" AND m.OutputAt IS NOT NULL"
: @" AND m.OutputAt IS NULL";
if (option.RecordedAtFrom.HasValue)
{
matchingCondition += @"
AND m.RecordedAt >= @RecordedAtFrom";
}
if (option.RecordedAtTo.HasValue)
{
matchingCondition += @"
AND m.RecordedAt <= @RecordedAtTo";
}
var query = $@"
SELECT t.OutputAt
, COUNT(*) [Count]
, SUM( t.Amount ) [Amount]
, MAX( t.UpdateAt ) [UpdateAt]
FROM (
SELECT m.OutputAt
, m.BillingId
, SUM( m.Amount ) [Amount]
, MAX( m.UpdateAt ) [UpdateAt]
FROM MatchingHeader mh
INNER JOIN Matching m
ON mh.Id = m.MatchingHeaderId
AND mh.CompanyId = @CompanyId
AND mh.CurrencyId = @CurrencyId
AND mh.Approved = 1
{matchingCondition}
GROUP BY
m.OutputAt
, m.BillingId
) t
GROUP BY OutputAt
ORDER BY OutputAt DESC";
return(dbHelper.GetItemsAsync <JournalizingSummary>(query, option, token));
}
public async Task <int> UpdateAsync(JournalizingOption option, CancellationToken token = default(CancellationToken))
{
var count = 0;
using (var scope = transactionScopeBuilder.Create())
{
option.UpdateAt = await dbSystemDateTimeQueryProcessor.GetAsync(token);
count += await updateMatchingJournalizingQueryProcessor.UpdateAsync(option, token);
count += await updateReceiptMatchingJournalizingQueryProcessor.UpdateAsync(option, token);
count += await updateAdvanceReceivedBackupJournaliginzQueryProcessor.UpdateAsync(option, token);
count += await updateReceiptExcludeJournalizingQueryProcessor.UpdateAsync(option, token);
scope.Complete();
}
return(count);
}
public Task <IEnumerable <HatarakuDBData> > ExtractAsync(JournalizingOption option, CancellationToken token = default(CancellationToken))
{
var conditionMatching = (option.OutputAt == null || !option.OutputAt.Any())
? " AND m.OUtputAt IS NULL"
: " AND m.OutputAt IN @OutputAt";
if (option.RecordedAtFrom.HasValue)
{
conditionMatching += @"
AND m.RecordedAt >= @RecordedAtFrom";
}
if (option.RecordedAtTo.HasValue)
{
conditionMatching += @"
AND m.RecordedAt <= @RecordedAtTo";
}
var query = $@"
SELECT b.[InvoiceCode]
, t.[RecordedAt]
, b.[AssignmentAmount]
, b.[BillingAmount]
FROM (
SELECT m.BillingId
, MAX( m.RecordedAt ) [RecordedAt]
FROM MatchingHeader mh
INNER JOIN Matching m
ON mh.Id = m.MatchingHeaderId
AND mh.CompanyId = @CompanyId
AND mh.CurrencyId = @CurrencyId
AND mh.Approved = 1
{conditionMatching}
GROUP BY m.BillingId
) t
INNER JOIN Billing b ON b.Id = t.BillingId
ORDER BY b.Id";
return(dbHelper.GetItemsAsync <HatarakuDBData>(query, option, token));
}
public async Task <int> CancelAsync(JournalizingOption option, CancellationToken token = default(CancellationToken))
{
if (!(option.OutputAt?.Any() ?? false))
{
return(0);
}
var query = @"
UPDATE re
SET re.OutputAt = NULL
, re.UpdateAt = @UpdateAt
, re.UpdateBy = @LoginUserId
FROM ReceiptExclude re
INNER JOIN Receipt r
ON r.Id = re.ReceiptId
AND r.CompanyId = @CompanyId
AND r.CurrencyId = @CurrencyId
AND r.Apportioned = 1
AND r.Approved = 1
AND re.OutputAt IN @OutputAt
";
return(await dbHelper.ExecuteAsync(query, option, token));
}
public async Task <byte[]> GetAsync(JournalizingOption option, CancellationToken token = default(CancellationToken))
{
var companyTask = companyQueryProcessor.GetAsync(new CompanySearch {
Id = option.CompanyId,
}, token);
var appConTask = applicationControlGetByCompanyQueryProcessor.GetAsync(option.CompanyId, token);
var nameTask = columnNameSettingQueryProcessor.GetAsync(new ColumnNameSetting {
CompanyId = option.CompanyId, TableName = nameof(Receipt),
}, token);
var loadTask = receiptJournalizingQueryProcessor.ExtractAsync(option, token);
await Task.WhenAll(companyTask, appConTask, nameTask, loadTask);
var company = companyTask.Result.First();
var appCon = appConTask.Result;
var naming = nameTask.Result.ToList();
var items = loadTask.Result.ToList();
if (!items.Any())
{
return(null);
}
var report = new ReceiptJournalizingSectionReport();
var reoutput = option.OutputAt?.Any() ?? false;
var fileName = $"{(reoutput ? "再出力" : "")}入金仕訳{DateTime.Now:yyyyMMdd_HHmmss}";
report.SetBasicPageSetting(company.Code, company.Name);
report.Name = fileName;
report.SetData(items, option.Precision, appCon.UseForeignCurrency == 1, naming);
report.Run();
return(report.Convert());
}
public Task <int> CancelAsync(JournalizingOption option, CancellationToken token = default(CancellationToken))
{
var query = @"
UPDATE r
SET r.OutputAt = NULL
, r.UpdateAt = GETDATE()
, r.UpdateBy = @LoginUserId
FROM Receipt r
INNER JOIN Category ct
ON ct.Id = r.ReceiptCategoryId
AND r.CompanyId = @CompanyId
AND r.OutputAt IN @OutputAt
AND r.Apportioned = 1
AND r.Approved = 1
AND r.DeleteAt IS NULL
AND ct.UseAdvanceReceived = 0 ";
if (option.CurrencyId != 0)
{
query += @"
AND r.CurrencyId = @CurrencyId";
}
return(dbHelper.ExecuteAsync(query, option, token));
}
public async Task <int> CancelAsync(JournalizingOption option, CancellationToken token = default(CancellationToken))
{
if (option.OutputAt == null)
{
option.OutputAt = new List <DateTime>();
}
var query = @"
UPDATE ar
SET ar.TransferOutputAt = NULL
FROM AdvanceReceivedBackup ar
WHERE ar.CompanyId = @CompanyId
AND ar.CurrencyId = @CurrencyId
AND ar.TransferOutputAt IN @OutputAt
AND EXISTS (
SELECT 1
FROM Receipt r
WHERE CompanyId = @CompanyId
AND r.CurrencyId = @CurrencyId
AND r.OriginalReceiptId = ar.OriginalReceiptId
AND r.OutputAt IS NULL )
";
return(await dbHelper.ExecuteAsync(query, option, token));
}
public async Task <int> CancelAsync(JournalizingOption option, CancellationToken token = default(CancellationToken))
{
if (option.OutputAt == null)
{
option.OutputAt = new List <DateTime>();
}
var query = @"
UPDATE r
SET r.OutputAt = NULL
, r.UpdateAt = @UpdateAt
, r.UpdateBy = @LoginUserId
FROM Receipt r
INNER JOIN Category ct
ON ct.Id = r.ReceiptCategoryId
AND r.CompanyId = @CompanyId
AND r.CurrencyId = @CurrencyId
AND ct.UseAdvanceReceived = 1
AND r.Apportioned = 1
AND r.Approved = 1
AND r.OutputAt IN @OutputAt
";
return(await dbHelper.ExecuteAsync(query, option, token));
}
public async Task <ActionResult <int> > CancelMatchingJournalizing(JournalizingOption option, CancellationToken token) => await matchingJournalizingProcessor.CancelAsync(option, token);
public async Task <ActionResult <int> > CancelMatchingJournalizingR(JournalizingOption option) => await hubContext.DoAsync(async token => await matchingJournalizingProcessor.CancelAsync(option, token));
public async Task <ActionResult <int> > UpdateOutputAt(JournalizingOption option, CancellationToken token) => await matchingJournalizingProcessor.UpdateAsync(option, token);
public async Task <ActionResult <int> > UpdateOutputAtR(JournalizingOption option) => await hubContext.DoAsync(async token => await matchingJournalizingProcessor.UpdateAsync(option, token));
public async Task<MatchingJournalizingProcessResult> CancelMatchingJournalizingAsync(string SessionKey, JournalizingOption option)
=> await authorizationProcessor.DoAuthorizeAsync(SessionKey, async token =>
{
await matchingJournalizingProcessor.CancelAsync(option, token);
return new MatchingJournalizingProcessResult { ProcessResult = new ProcessResult { Result = true } };
}, logger);
public async Task <ActionResult <int> > CancelAsync(JournalizingOption option) => await hatarakuDBJournalizingProcessor.CancelAsync(option);
public async Task <ActionResult <IEnumerable <HatarakuDBData> > > ExtractAsync(JournalizingOption option) => (await hatarakuDBJournalizingProcessor.ExtractAsync(option)).ToArray();
public async Task <ActionResult <IEnumerable <MatchingJournalizing> > > MFExtractMatchingJournalizingR(JournalizingOption option)
=> await hubContext.DoAsync(async token
=> (await matchingJournalizingProcessor.MFExtractAsync(option, token)).ToArray());
public async Task <ActionResult <IEnumerable <MatchingJournalizing> > > MFExtractMatchingJournalizing(JournalizingOption option, CancellationToken token) => (await matchingJournalizingProcessor.MFExtractAsync(option, token)).ToArray();
public async Task <ActionResult <IEnumerable <JournalizingSummary> > > GetMatchingJournalizingSummary(JournalizingOption option, CancellationToken token) => (await matchingJournalizingProcessor.GetSummaryAsync(option, token)).ToArray();
public async Task<JournalizingSummariesResult> GetMatchingJournalizingSummaryAsync(string SessionKey, JournalizingOption option)
=> await authorizationProcessor.DoAuthorizeAsync(SessionKey, async token =>
{
var result = await matchingJournalizingProcessor.GetSummaryAsync(option, token);
return new JournalizingSummariesResult
{
ProcessResult = new ProcessResult { Result = true },
JournalizingsSummaries = result.ToList(),
};
}, logger);
public async Task <ActionResult <IEnumerable <MatchedReceipt> > > GetMatchedReceiptR(JournalizingOption option)
=> await hubContext.DoAsync(async token
=> (await matchingJournalizingProcessor.GetMatchedReceiptAsync(option, token)).ToArray());
public async Task<MatchingJournalizingsResult> MFExtractMatchingJournalizingAsync(string SessionKey, JournalizingOption option)
=> await authorizationProcessor.DoAuthorizeAsync(SessionKey, async token =>
{
var result = await matchingJournalizingProcessor.MFExtractAsync(option, token);
return new MatchingJournalizingsResult
{
ProcessResult = new ProcessResult { Result = true },
MatchingJournalizings = result.ToList(),
};
}, logger);
public async Task <ActionResult <IEnumerable <MatchedReceipt> > > GetMatchedReceipt(JournalizingOption option, CancellationToken token) => (await matchingJournalizingProcessor.GetMatchedReceiptAsync(option, token)).ToArray();
