330 lines
17 KiB
C#
330 lines
17 KiB
C#
using General_Ledger_Service.DTOs.Accounts;
|
|
using General_Ledger_Service.DTOs.Reports;
|
|
using General_Ledger_Service.Infra;
|
|
using General_Ledger_Service.Interfaces.Repositories;
|
|
using General_Ledger_Service.Models;
|
|
using Microsoft.EntityFrameworkCore;
|
|
|
|
namespace General_Ledger_Service.Repositories
|
|
{
|
|
public class AccountRepository(AppDBContext context) : IAccountRepository
|
|
{
|
|
private readonly AppDBContext _context = context;
|
|
|
|
public Task<Account?> GetByIdAsync(long accountId) =>
|
|
RepositoryHelpers.GetByIdAsync(_context.Accounts, accountId);
|
|
|
|
public async Task<Account?> GetByCodeAsync(string accountCode) =>
|
|
await _context.Accounts.FirstOrDefaultAsync(a => a.AccountCode == accountCode);
|
|
|
|
// Used by BankService's auto-created "Bank"/"Cash" GL hierarchy (2026-07-31) to find an
|
|
// existing system header/type node by its fixed name before creating a new one -
|
|
// accountName is always a fixed literal here ("Bank", "Cash", a CashAccountType.Name),
|
|
// never end-user free text, so a name collision would only ever come from the same
|
|
// auto-creation logic itself, not an unrelated manually-created account.
|
|
public async Task<Account?> GetByNameAndParentAsync(string accountName, long? parentAccountId) =>
|
|
await _context.Accounts.FirstOrDefaultAsync(a => a.AccountName == accountName && a.ParentAccountId == parentAccountId);
|
|
|
|
public async Task<int> GetAncestorDepthAsync(long parentAccountId) =>
|
|
RecursiveTreeQueries.GetAncestorDepth(await LoadTreeRowsAsync(), parentAccountId);
|
|
|
|
public async Task<string?> GetAncestorBreadcrumbAsync(long accountId) =>
|
|
RecursiveTreeQueries.GetAncestorBreadcrumb(await LoadTreeRowsAsync(), accountId);
|
|
|
|
public async Task<List<AccountTreeNode>> GetDescendantTreeAsync(long accountId)
|
|
{
|
|
var rows = RecursiveTreeQueries.GetDescendantTree(await LoadTreeRowsAsync(), accountId);
|
|
return [.. rows.Select(r => new AccountTreeNode
|
|
{
|
|
AccountId = r.Id,
|
|
AccountCode = r.Code,
|
|
AccountName = r.Name,
|
|
ParentAccountId = r.ParentId,
|
|
Depth = r.Depth
|
|
})];
|
|
}
|
|
|
|
public async Task<bool> WouldCreateCycleAsync(long accountId, long newParentId) =>
|
|
RecursiveTreeQueries.WouldCreateCycle(await LoadTreeRowsAsync(), accountId, newParentId);
|
|
|
|
public async Task<(List<Account> Items, int TotalCount)> SearchAsync(short? accountTypeId, bool? isActive, bool? isPostable, int? page, int? pageSize)
|
|
{
|
|
var query = _context.Accounts.AsQueryable();
|
|
|
|
if (accountTypeId.HasValue)
|
|
{
|
|
query = query.Where(a => a.AccountTypeId == accountTypeId.Value);
|
|
}
|
|
|
|
if (isActive.HasValue)
|
|
{
|
|
query = query.Where(a => a.IsActive == isActive.Value);
|
|
}
|
|
|
|
if (isPostable.HasValue)
|
|
{
|
|
query = query.Where(a => a.IsPostable == isPostable.Value);
|
|
}
|
|
|
|
var totalCount = await query.CountAsync();
|
|
|
|
var items = await RepositoryHelpers.ApplyPaging(query.OrderBy(a => a.AccountCode), page, pageSize)
|
|
.ToListAsync();
|
|
|
|
return (items, totalCount);
|
|
}
|
|
|
|
public Task<Account> AddAsync(Account account) =>
|
|
RepositoryHelpers.AddAsync(_context.Accounts, account);
|
|
|
|
|
|
// Retrofit 2026-07-22 - flat list, no depth/indentation (05_LKAS_Report_PDF_Templates.md
|
|
// Section 2): a plain postable-leaf-account listing needs no recursive tree at all.
|
|
// Fix 2026-10-20: each row shows the account's NET balance in exactly one column (debit
|
|
// balance -> Debit, credit balance -> Credit, the other stays 0 and renders blank), and an
|
|
// account whose debits and credits cancel out (net zero) is left out entirely - per
|
|
// 05_LKAS_Report_PDF_Templates.md Section 2. Previously this returned the gross debit and
|
|
// credit totals side by side, so any account with movement on both sides (or an entry and
|
|
// its reversal) showed equal/duplicated figures in both columns.
|
|
public async Task<List<TrialBalanceRowDto>> GetTrialBalanceAsync(DateOnly asOfDate)
|
|
{
|
|
// Post-closing trial balance: includes year-end closing journals (JournalType CLOSING).
|
|
var totals = await GetPostedTotalsAsync(null, asOfDate, includeClosing: true);
|
|
var postableAccounts = await _context.Accounts.AsNoTracking().Where(a => a.IsPostable).ToListAsync();
|
|
|
|
return [.. postableAccounts
|
|
.Where(a => totals.TryGetValue(a.AccountId, out var t) && t.Debit != t.Credit)
|
|
.OrderBy(a => a.AccountCode, StringComparer.Ordinal)
|
|
.Select(a =>
|
|
{
|
|
var net = totals[a.AccountId].Debit - totals[a.AccountId].Credit;
|
|
return new TrialBalanceRowDto
|
|
{
|
|
AccountCode = a.AccountCode,
|
|
AccountName = a.AccountName,
|
|
Debit = net > 0 ? net : 0,
|
|
Credit = net < 0 ? -net : 0
|
|
};
|
|
})];
|
|
}
|
|
|
|
// Retrofit 2026-07-31 - Accounting_System_Design_LKAS.md Section 11.3. Leaf/postable rows
|
|
// only, tagged with balance_sheet_classification inherited down the tree (same
|
|
// recursive-tag pattern as cash_flow_category/income_statement_section); ReportService
|
|
// groups these into the nested BalanceSheetReportDto (Non-Current/Current Assets,
|
|
// Equity, Non-Current/Current Liabilities) and computes section/grand totals. Balance is
|
|
// already sign-adjusted per account type: Asset is debit-normal (debit - credit);
|
|
// Liability/Equity are credit-normal, so they use the opposite sign (credit - debit) to
|
|
// show as conventional positive balances too - the same flip the P&L applies.
|
|
public async Task<List<BalanceSheetLeafRowDto>> GetBalanceSheetLeafRowsAsync(DateOnly asOfDate)
|
|
{
|
|
var accounts = await _context.Accounts.AsNoTracking().ToListAsync();
|
|
var roots = accounts.Where(a => a.ParentAccountId == null && (a.AccountTypeId == 1 || a.AccountTypeId == 2 || a.AccountTypeId == 3));
|
|
var classification = InheritTag(accounts, roots, a => a.BalanceSheetClassification);
|
|
// Includes closing journals so Retained Earnings carries every closed year's profit.
|
|
var totals = await GetPostedTotalsAsync(null, asOfDate, includeClosing: true);
|
|
|
|
return [.. accounts
|
|
.Where(a => a.IsPostable && classification.ContainsKey(a.AccountId) && totals.ContainsKey(a.AccountId))
|
|
.Select(a => new BalanceSheetLeafRowDto
|
|
{
|
|
AccountCode = a.AccountCode,
|
|
AccountName = a.AccountName,
|
|
AccountTypeId = a.AccountTypeId,
|
|
BalanceSheetClassification = classification[a.AccountId],
|
|
Balance = SignedBalance(a.AccountTypeId, totals[a.AccountId])
|
|
})
|
|
.Where(r => r.Balance != 0)
|
|
.OrderBy(r => r.AccountCode, StringComparer.Ordinal)];
|
|
}
|
|
|
|
// Retrofit 2026-07-22 - Accounting_System_Design_LKAS.md Section 11.4. Leaf/postable rows
|
|
// only, tagged with income_statement_section inherited down the tree (same recursive-tag
|
|
// pattern as cash_flow_category); ReportService groups these into the nested
|
|
// ProfitAndLossReportDto and applies the Expense-side sign flip (Amount here is the raw,
|
|
// unflipped credit - debit - negative for a normal debit-balance Expense account).
|
|
public async Task<List<ProfitAndLossLeafRowDto>> GetProfitAndLossLeafRowsAsync(DateOnly periodStart, DateOnly periodEnd, bool includeClosing = false)
|
|
{
|
|
var accounts = await _context.Accounts.AsNoTracking().ToListAsync();
|
|
var roots = accounts.Where(a => a.ParentAccountId == null && (a.AccountTypeId == 4 || a.AccountTypeId == 5));
|
|
var section = InheritTag(accounts, roots, a => a.IncomeStatementSection);
|
|
var totals = await GetPostedTotalsAsync(periodStart, periodEnd, includeClosing);
|
|
|
|
return [.. accounts
|
|
.Where(a => a.IsPostable && section.ContainsKey(a.AccountId) && totals.ContainsKey(a.AccountId))
|
|
.Select(a => new ProfitAndLossLeafRowDto
|
|
{
|
|
AccountCode = a.AccountCode,
|
|
AccountName = a.AccountName,
|
|
AccountTypeId = a.AccountTypeId,
|
|
IncomeStatementSection = section[a.AccountId],
|
|
Amount = totals[a.AccountId].Credit - totals[a.AccountId].Debit
|
|
})
|
|
.Where(r => r.Amount != 0)
|
|
.OrderBy(r => r.AccountCode, StringComparer.Ordinal)];
|
|
}
|
|
|
|
// Accounting_System_Design_LKAS.md Section 11.5 - "cash-like" accounts are the recursive
|
|
// descendants of the configured CASH_ACCOUNT_ROOT_ID system_config value (resolved and
|
|
// validated by ReportService before this is ever called - a plain long here, so an
|
|
// unset/invalid config fails with a clear error before reaching any query).
|
|
public async Task<List<CashFlowWorkingCapitalChangeDto>> GetCashFlowWorkingCapitalChangesAsync(DateOnly periodStart, DateOnly periodEnd, long cashAccountRootId)
|
|
{
|
|
var accounts = await _context.Accounts.AsNoTracking().ToListAsync();
|
|
var cashAccountIds = RecursiveTreeQueries.GetDescendantIds(ToTreeRows(accounts), cashAccountRootId);
|
|
var category = InheritTag(accounts, accounts.Where(a => a.ParentAccountId == null), a => a.CashFlowCategory);
|
|
var opening = await GetPostedTotalsAsync(null, periodStart.AddDays(-1));
|
|
var closing = await GetPostedTotalsAsync(null, periodEnd);
|
|
|
|
var rows = new List<CashFlowWorkingCapitalChangeDto>();
|
|
foreach (var a in accounts.Where(a => a.IsPostable
|
|
&& !cashAccountIds.Contains(a.AccountId)
|
|
&& category.GetValueOrDefault(a.AccountId) == "OPERATING"))
|
|
{
|
|
var openingBalance = opening.TryGetValue(a.AccountId, out var o) ? SignedBalance(a.AccountTypeId, o) : 0m;
|
|
var closingBalance = closing.TryGetValue(a.AccountId, out var c) ? SignedBalance(a.AccountTypeId, c) : 0m;
|
|
var change = closingBalance - openingBalance;
|
|
if (change == 0)
|
|
{
|
|
continue;
|
|
}
|
|
|
|
rows.Add(new CashFlowWorkingCapitalChangeDto
|
|
{
|
|
AccountCode = a.AccountCode,
|
|
AccountName = a.AccountName,
|
|
Direction = change >= 0 ? "Increase" : "Decrease",
|
|
ChangeAmount = a.AccountTypeId == 1 ? -change : change
|
|
});
|
|
}
|
|
|
|
return [.. rows.OrderBy(r => r.AccountCode, StringComparer.Ordinal)];
|
|
}
|
|
|
|
public async Task<List<CashFlowLineDto>> GetCashFlowCategoryLinesAsync(string category, DateOnly periodStart, DateOnly periodEnd, long cashAccountRootId)
|
|
{
|
|
var accounts = await _context.Accounts.AsNoTracking().ToListAsync();
|
|
var cashAccountIds = RecursiveTreeQueries.GetDescendantIds(ToTreeRows(accounts), cashAccountRootId);
|
|
var inherited = InheritTag(accounts, accounts.Where(a => a.ParentAccountId == null), a => a.CashFlowCategory);
|
|
var totals = await GetPostedTotalsAsync(periodStart, periodEnd);
|
|
|
|
return [.. accounts
|
|
.Where(a => a.IsPostable
|
|
&& !cashAccountIds.Contains(a.AccountId)
|
|
&& inherited.GetValueOrDefault(a.AccountId) == category
|
|
&& totals.ContainsKey(a.AccountId))
|
|
.Select(a => new CashFlowLineDto
|
|
{
|
|
AccountCode = a.AccountCode,
|
|
AccountName = a.AccountName,
|
|
Amount = totals[a.AccountId].Credit - totals[a.AccountId].Debit
|
|
})
|
|
.OrderBy(r => r.AccountCode, StringComparer.Ordinal)];
|
|
}
|
|
|
|
public async Task<decimal> GetCashBalanceAsync(DateOnly asOfDate, long cashAccountRootId)
|
|
{
|
|
var accounts = await _context.Accounts.AsNoTracking().ToListAsync();
|
|
var cashAccountIds = RecursiveTreeQueries.GetDescendantIds(ToTreeRows(accounts), cashAccountRootId);
|
|
var totals = await GetPostedTotalsAsync(null, asOfDate);
|
|
|
|
return totals
|
|
.Where(t => cashAccountIds.Contains(t.Key))
|
|
.Sum(t => t.Value.Debit - t.Value.Credit);
|
|
}
|
|
|
|
private async Task<List<RecursiveTreeQueries.TreeRow>> LoadTreeRowsAsync() =>
|
|
await _context.Accounts.AsNoTracking()
|
|
.Select(a => new RecursiveTreeQueries.TreeRow
|
|
{
|
|
Id = a.AccountId,
|
|
Code = a.AccountCode,
|
|
Name = a.AccountName,
|
|
ParentId = a.ParentAccountId
|
|
})
|
|
.ToListAsync();
|
|
|
|
private static List<RecursiveTreeQueries.TreeRow> ToTreeRows(IEnumerable<Account> accounts) =>
|
|
[.. accounts.Select(a => new RecursiveTreeQueries.TreeRow
|
|
{
|
|
Id = a.AccountId,
|
|
Code = a.AccountCode,
|
|
Name = a.AccountName,
|
|
ParentId = a.ParentAccountId
|
|
})];
|
|
|
|
// Per-account base debit/credit totals over posted journal entries, optionally bounded
|
|
// by entry date (inclusive on both ends). The sums run in the database; accounts with no
|
|
// matching lines are simply absent from the result.
|
|
// Year-end closing journals (JournalType == ClosingJournalType) move a year's income/expense into
|
|
// Retained Earnings. They are excluded unless includeClosing is true, so the P&L, cash flow and
|
|
// similar period reports still show a closed year's own activity; the balance sheet and trial
|
|
// balance include them so every account's balance is complete.
|
|
public const string ClosingJournalType = "CLOSING";
|
|
|
|
private async Task<Dictionary<long, (decimal Debit, decimal Credit)>> GetPostedTotalsAsync(DateOnly? from, DateOnly? to, bool includeClosing = false)
|
|
{
|
|
var lines = _context.JournalEntryLines.Where(l => l.Journal.IsPosted);
|
|
|
|
if (!includeClosing)
|
|
{
|
|
lines = lines.Where(l => l.Journal.JournalType != ClosingJournalType);
|
|
}
|
|
|
|
if (from.HasValue)
|
|
{
|
|
lines = lines.Where(l => l.Journal.EntryDate >= from.Value);
|
|
}
|
|
|
|
if (to.HasValue)
|
|
{
|
|
lines = lines.Where(l => l.Journal.EntryDate <= to.Value);
|
|
}
|
|
|
|
var totals = await lines
|
|
.GroupBy(l => l.AccountId)
|
|
.Select(g => new { AccountId = g.Key, Debit = g.Sum(l => l.BaseDebit), Credit = g.Sum(l => l.BaseCredit) })
|
|
.ToListAsync();
|
|
|
|
return totals.ToDictionary(t => t.AccountId, t => (t.Debit, t.Credit));
|
|
}
|
|
|
|
// Asset (type 1) is debit-normal; every other type is credit-normal.
|
|
private static decimal SignedBalance(short accountTypeId, (decimal Debit, decimal Credit) totals) =>
|
|
accountTypeId == 1 ? totals.Debit - totals.Credit : totals.Credit - totals.Debit;
|
|
|
|
// Walks the tree from the given roots, giving each node its own tag or, if it has none,
|
|
// its parent's inherited tag. Only nodes reachable from the roots appear in the result,
|
|
// so a root filter (e.g. types 1-3 only) also scopes which accounts are included.
|
|
private static Dictionary<long, string?> InheritTag(List<Account> accounts, IEnumerable<Account> roots, Func<Account, string?> tag)
|
|
{
|
|
var childrenByParent = accounts
|
|
.Where(a => a.ParentAccountId.HasValue)
|
|
.ToLookup(a => a.ParentAccountId!.Value);
|
|
|
|
var result = new Dictionary<long, string?>();
|
|
var stack = new Stack<(Account Node, string? Inherited)>();
|
|
foreach (var root in roots)
|
|
{
|
|
stack.Push((root, tag(root)));
|
|
}
|
|
|
|
while (stack.Count > 0)
|
|
{
|
|
var (node, inherited) = stack.Pop();
|
|
if (!result.TryAdd(node.AccountId, inherited))
|
|
{
|
|
continue;
|
|
}
|
|
|
|
foreach (var child in childrenByParent[node.AccountId])
|
|
{
|
|
stack.Push((child, tag(child) ?? inherited));
|
|
}
|
|
}
|
|
|
|
return result;
|
|
}
|
|
}
|
|
}
|