sourceAccount, err = txStore.Accounts.Get(cmd.Context(), flags.sourceAccount)
if err != nil {
if errors.Is(err, sql.ErrNoRows) {
- sourceAccount, err = txStore.Accounts.Get(cmd.Context(), flags.sourceAccount, []store.AccountsOptFunc{store.WithAccountTree(true)}...)
+ sourceAccount, err = txStore.Accounts.Get(cmd.Context(), flags.sourceAccount, []store.AccountsOptFunc{store.WithAccountFullName(true)}...)
if err != nil {
return accountError(err)
}
destinationAccount, err = txStore.Accounts.Get(cmd.Context(), flags.destinationAccount)
if err != nil {
if errors.Is(err, sql.ErrNoRows) {
- destinationAccount, err = txStore.Accounts.Get(cmd.Context(), flags.destinationAccount, []store.AccountsOptFunc{store.WithAccountTree(true)}...)
+ destinationAccount, err = txStore.Accounts.Get(cmd.Context(), flags.destinationAccount, []store.AccountsOptFunc{store.WithAccountFullName(true)}...)
if err != nil {
return accountError(err)
}
sourceAccount, err = txStore.Accounts.Get(cmd.Context(), flags.sourceAccount)
if err != nil {
if errors.Is(err, sql.ErrNoRows) {
- sourceAccount, err = txStore.Accounts.Get(cmd.Context(), flags.sourceAccount, []store.AccountsOptFunc{store.WithAccountTree(true)}...)
+ sourceAccount, err = txStore.Accounts.Get(cmd.Context(), flags.sourceAccount, []store.AccountsOptFunc{store.WithAccountFullName(true)}...)
if err != nil {
return ErrAccountDoesNotExist
}
destinationAccount, err = txStore.Accounts.Get(cmd.Context(), flags.destinationAccount)
if err != nil {
if errors.Is(err, sql.ErrNoRows) {
- destinationAccount, err = txStore.Accounts.Get(cmd.Context(), flags.destinationAccount, []store.AccountsOptFunc{store.WithAccountTree(true)}...)
+ destinationAccount, err = txStore.Accounts.Get(cmd.Context(), flags.destinationAccount, []store.AccountsOptFunc{store.WithAccountFullName(true)}...)
if err != nil {
return ErrAccountDoesNotExist
}
func listTransactionCmd(cli *cli) *cobra.Command {
var flags struct {
- account string
+ accounts []string
+ accountDepth int
limit int
startPostDate string
endPostDate string
orderByPostDate bool
orderDescending bool
includeTotals bool
+ shortName bool
}
var cmd = &cobra.Command{
- Use: "list",
+ Use: "list [flags]",
+ Short: "List transactions",
+ Example: `* List transactions:
+
+ gt transaction list
+
+* List all transactions by setting limit to 0:
+
+ gt transactions list --limit=0
+
+* List transactions and output in json:
+
+ gt transaction list --output json
+
+* List transactions for account guid:
+
+ gt transaction list --account 9b1d2bc513da4076b236aee6114b21a7
+
+* List transactions for account name:
+
+ gt transaction list --account expenses:dining:pizza
+
+* List transactions for multiple accounts:
+
+ gt transaction list --account expenses:dining --account expenses:takeaway
+
+* List transactions for account tree and exclude totals:
+
+ gt transaction list --account expenses:petrol --include-totals=false
+
+* List transactions with a date range:
+
+ gt transaction list --start-post-date 2025-01-01 --end-post-date 2025-03-31
+
+* List transactions within a date range and with a description that contains %Pizza:
+
+ gt transaction list --start-post-date 2024-01-01 --end-post-date 2024-12-31 --description-like "%Pizza"
+`,
RunE: func(cmd *cobra.Command, args []string) error {
var err error
s := store.NewStore(cli.db)
transactionQuery := store.NewTransactionQuery()
- var account *store.Account
- if flags.account != "" {
- account, err = s.Accounts.Get(cmd.Context(), flags.account)
- if err != nil {
- if errors.Is(err, sql.ErrNoRows) {
- account, err = s.Accounts.Get(cmd.Context(), flags.account, []store.AccountsOptFunc{store.WithAccountTree(true)}...)
- if err != nil {
+ var accounts []*store.Account
+ if len(flags.accounts) != 0 {
+ seen := make(map[string]struct{}, len(flags.accounts))
+ for _, i := range flags.accounts {
+ account, err := s.Accounts.Get(cmd.Context(), i)
+ if err != nil {
+ if errors.Is(err, sql.ErrNoRows) {
+ account, err = s.Accounts.Get(cmd.Context(), i, []store.AccountsOptFunc{store.WithAccountFullName(true)}...)
+ if err != nil {
+ return ErrAccountDoesNotExist
+ }
+ } else {
return ErrAccountDoesNotExist
}
- } else {
- return ErrAccountDoesNotExist
}
+ if _, exists := seen[account.GUID]; exists {
+ return ErrAccountSpecifiedMultiplyTimes
+ }
+ seen[account.GUID] = struct{}{}
+ accounts = append(accounts, account)
}
}
}
var transactions []*store.Transaction
- if account != nil {
+ if len(accounts) != 0 {
// NOTE(rene): If account is not nil, user is wanting to list
// transactions by account. To do this, we must find all splits with
// account_guid == account then return all transactions for found
// splits.
- splits, err := s.Splits.All(cmd.Context(), store.NewSplitQuery().Where("account_guid=?", account.GUID))
+ var allAccounts []*store.Account
+
+ for _, account := range accounts {
+ allAccounts = append(allAccounts, account)
+ allAccounts = append(allAccounts, account.GetDescendants()...)
+ }
+
+ accountGUIDs := make([]string, len(allAccounts))
+ for i, account := range allAccounts {
+ accountGUIDs[i] = account.GUID
+ }
+
+ placeholders := make([]string, len(accountGUIDs))
+ args := make([]any, len(accountGUIDs))
+ for i, guid := range accountGUIDs {
+ placeholders[i] = "?"
+ args[i] = guid
+ }
+
+ splits, err := s.Splits.All(cmd.Context(), store.NewSplitQuery().Where(fmt.Sprintf("account_guid IN (%s)", strings.Join(placeholders, ",")), args...))
if err != nil {
return err
}
}
}
- placeholders := make([]string, len(txGUIDs))
- args := make([]any, len(txGUIDs))
- for i, guid := range txGUIDs {
- placeholders[i] = "?"
- args[i] = guid
- }
+ if len(txGUIDs) == 0 {
+ transactions = []*store.Transaction{}
+ } else {
+ placeholders = make([]string, len(txGUIDs))
+ args = make([]any, len(txGUIDs))
+ for i, guid := range txGUIDs {
+ placeholders[i] = "?"
+ args[i] = guid
+ }
- transactions, err = s.Transactions.All(cmd.Context(), transactionQuery.Copy().Where(fmt.Sprintf("guid IN (%s)", strings.Join(placeholders, ",")), args...))
- if err != nil {
- return err
+ transactions, err = s.Transactions.All(cmd.Context(), transactionQuery.Copy().Where(fmt.Sprintf("guid IN (%s)", strings.Join(placeholders, ",")), args...))
+ if err != nil {
+ return err
+ }
}
} else {
transactions, err = s.Transactions.All(cmd.Context(), transactionQuery)
return err
}
- renderOpts := []render.RendererOptsFunc{render.WithIncludeTotals(flags.includeTotals)}
+ renderOpts := []render.RendererOptsFunc{render.WithIncludeTotals(flags.includeTotals), render.WithAccountShortName(flags.shortName)}
return r.Render(cmd.OutOrStdout(), transactions, renderOpts...)
},
}
cmd.Flags().IntVar(&flags.limit, "limit", 50, "Limit")
- cmd.Flags().StringVar(&flags.account, "account", "", "Account GUID")
cmd.Flags().StringVar(&flags.startPostDate, "start-post-date", "", "Start Post Date")
cmd.Flags().StringVar(&flags.endPostDate, "end-post-date", "", "Start Post Date")
cmd.Flags().BoolVar(&flags.orderByPostDate, "order-by-post-date", true, "Order by Post Date")
cmd.Flags().StringVar(&flags.descriptionLike, "description-like", "", "Description like")
cmd.Flags().StringVar(&flags.output, "output", "table", FlagsUsageOutput)
cmd.Flags().BoolVar(&flags.includeTotals, "include-totals", true, FlagsUsageIncludeTotals)
+ cmd.Flags().BoolVar(&flags.shortName, "short-name", false, FlagsUsageAccountShortName)
+ cmd.Flags().StringArrayVar(&flags.accounts, "account", []string{}, "Filter by account GUID or account full name (can specify multiple times)")
return cmd
}
func getTransactionCmd(cli *cli) *cobra.Command {
var flags struct {
- output string
+ output string
+ shortName bool
+ includeTotals bool
}
var cmd = &cobra.Command{
Use: "get",
return err
}
- return r.Render(cmd.OutOrStdout(), transaction)
+ renderOpts := []render.RendererOptsFunc{render.WithIncludeTotals(false), render.WithAccountShortName(flags.shortName)}
+ return r.Render(cmd.OutOrStdout(), transaction, renderOpts...)
},
}
cmd.Flags().StringVar(&flags.output, "output", "table", "Output format")
+ cmd.Flags().BoolVar(&flags.shortName, "short-name", false, FlagsUsageAccountShortName)
return cmd
}
for _, split := range transaction.Splits {
debit, credit := formatAmount(split.ValueNum, split.ValueDenom)
+ accountName := split.Account.FullName
+ if opts.accountShortName {
+ accountName = split.Account.Name
+ }
+
table.Append([]string{
"",
"",
- split.Account.Name,
+ accountName,
debit,
credit,
})
accountGUID := split.AccountGUID
if _, exists := accountTotals[accountGUID]; !exists {
accountTotals[accountGUID] = &AccountTotal{
- Name: split.Account.Name,
+ Name: accountName,
TotalNum: 0,
TotalDenom: split.ValueDenom,
}
Description *string
Hidden *int64
Placeholder *int64
+
+ Parent *Account
+ Children []*Account
+ Level int
+}
+
+func (a *Account) GetDescendants() []*Account {
+ var descendants []*Account
+ var traverse func(*Account)
+ traverse = func(account *Account) {
+ for _, child := range account.Children {
+ descendants = append(descendants, child)
+ traverse(child)
+ }
+ }
+ traverse(a)
+ return descendants
}
type AccountQuery struct {
Opts AccountsOpts
}
+// AccountsOpts configures account query and retrieval behavior.
type AccountsOpts struct {
- withAccountTree bool
+ // withAccountFullName indicates that the lookup string should be interpreted as
+ // a colon-separated full account name (e.g., "expenses:dining:pizza") rather than
+ // a GUID. When true, Get() traverses the account tree from the root to locate
+ // the account by its hierarchy path.
+ withAccountFullName bool
}
func defaultAccountsOpts() *AccountsOpts {
return &AccountsOpts{
- withAccountTree: false,
+ withAccountFullName: false,
}
}
type AccountsOptFunc func(*AccountsOpts)
-func WithAccountTree(b bool) AccountsOptFunc {
+func WithAccountFullName(b bool) AccountsOptFunc {
return func(o *AccountsOpts) {
- o.withAccountTree = b
+ o.withAccountFullName = b
}
}
-// getFullAccountName takes a store.Account and attempts to return its full account name (e.g. expenses:dining:pizza)
+func getAccountSubtree(ctx context.Context, db DBTX, guid string) ([]*Account, error) {
+ query := `
+WITH RECURSIVE account_tree AS (
+ SELECT
+ accounts.guid,
+ accounts.name,
+ accounts.account_type,
+ accounts.commodity_guid,
+ accounts.commodity_scu,
+ accounts.non_std_scu,
+ accounts.parent_guid,
+ accounts.code,
+ accounts.description,
+ accounts.hidden,
+ accounts.placeholder
+ FROM accounts
+ WHERE accounts.guid = ?
+ UNION ALL
+ SELECT
+ a.guid,
+ a.name,
+ a.account_type,
+ a.commodity_guid,
+ a.commodity_scu,
+ a.non_std_scu,
+ a.parent_guid,
+ a.code,
+ a.description,
+ a.hidden,
+ a.placeholder
+ FROM accounts a
+ INNER JOIN account_tree at ON a.parent_guid = at.guid
+)
+SELECT
+ guid,
+ name,
+ account_type,
+ commodity_guid,
+ commodity_scu,
+ non_std_scu,
+ parent_guid,
+ code,
+ description,
+ hidden,
+ placeholder
+FROM account_tree
+`
+ rows, err := db.QueryContext(ctx, query, guid)
+ if err != nil {
+ return nil, err
+ }
+ defer rows.Close()
+
+ var accounts []*Account
+ for rows.Next() {
+ account, err := scanAccount(rows)
+ if err != nil {
+ return nil, err
+ }
+ fullName, err := getFullAccountName(ctx, db, account)
+ if err != nil {
+ return nil, err
+ }
+ account.FullName = fullName
+ accounts = append(accounts, account)
+ }
+
+ return accounts, nil
+}
+
+func buildTreeFromAccounts(accounts []*Account, root *Account) {
+ accountMap := make(map[string]*Account)
+ for _, account := range accounts {
+ account.Children = make([]*Account, 0)
+ accountMap[account.GUID] = account
+ }
+
+ for _, account := range accounts {
+ if account.ParentGUID != nil {
+ if parent, exists := accountMap[*account.ParentGUID]; exists {
+ parent.Children = append(parent.Children, account)
+ account.Parent = parent
+ account.Level = parent.Level + 1
+ }
+ }
+ }
+
+ if rootFromAccountMap := accountMap[root.GUID]; rootFromAccountMap != nil {
+ root.Children = rootFromAccountMap.Children
+ root.Parent = rootFromAccountMap.Parent
+ root.Level = rootFromAccountMap.Level
+
+ for _, child := range root.Children {
+ if child.Parent != root {
+ child.Parent = root
+ }
+ }
+ }
+}
+
+// getFullAccountName takes a store.Account and attempts to return its full
+// account name (e.g. expenses:dining:pizza)
func getFullAccountName(ctx context.Context, db DBTX, account *Account) (string, error) {
s := []string{account.Name}
for account.ParentGUID != nil {
return parentAccounts[len(parentAccounts)-1], nil
}
+func getSubtreeWithTree(ctx context.Context, db DBTX, root *Account) error {
+ accounts, err := getAccountSubtree(ctx, db, root.GUID)
+ if err != nil {
+ return err
+ }
+
+ if len(accounts) == 0 {
+ return sql.ErrNoRows
+ }
+
+ buildTreeFromAccounts(accounts, root)
+ return nil
+}
+
func (s AccountsStore) Get(ctx context.Context, guidOrName string, opts ...AccountsOptFunc) (*Account, error) {
var account *Account
o := defaultAccountsOpts()
fn(o)
}
- if o.withAccountTree {
+ if o.withAccountFullName {
account, err := getAccountFromAccountTree(ctx, s.db, guidOrName)
if err != nil {
return nil, err
return nil, err
}
account.FullName = fullName
+ if err := getSubtreeWithTree(ctx, s.db, account); err != nil {
+ return nil, err
+ }
return account, nil
}
}
account.FullName = fullName
+ if err := getSubtreeWithTree(ctx, s.db, account); err != nil {
+ return nil, err
+ }
+
return account, nil
}
return nil, sql.ErrNoRows
}
+ for _, transaction := range transactions {
+ for _, split := range transaction.Splits {
+ account := split.Account
+ accountFullName, err := getFullAccountName(ctx, t.db, account)
+ if err != nil {
+ return nil, err
+ }
+ account.FullName = accountFullName
+ }
+ }
+
return transactions[0], nil
}
}
defer rows.Close()
- return scanTransactions(rows)
+ transactions, err = scanTransactions(rows)
+ if err != nil {
+ return nil, err
+ }
+
+ for _, transaction := range transactions {
+ for _, split := range transaction.Splits {
+ account := split.Account
+ accountFullName, err := getFullAccountName(ctx, t.db, account)
+ if err != nil {
+ return nil, err
+ }
+ account.FullName = accountFullName
+ }
+ }
+
+ return transactions, nil
}
func scanTransactions(rows *sql.Rows) ([]*Transaction, error) {
NonSTDSCU: accountNonStdSCU.Int64,
}
+ if accountCommodityGUID.Valid {
+ account.CommodityGUID = &accountCommodityGUID.String
+ }
+
+ if accountParentGUID.Valid {
+ account.ParentGUID = &accountParentGUID.String
+ }
+
+ if accountCode.Valid {
+ account.Code = &accountCode.String
+ }
+
+ if accountDescription.Valid {
+ account.Description = &accountDescription.String
+ }
+
if accountHidden.Valid {
account.Hidden = &accountHidden.Int64
}