ACCOUNTING LIBRARY
For this project, we will be building the foundation for a double-entry
accounting system called almighty-kaikei. Double-entry accounting is a system
that is designed to prevent or detect accounting errors by tracking two sides of
a ledger: the debit and credit sides.
There are five basic types of accounts in a double-entry accounting system: Asset, Expense, Income, Liability, and Equity. All of the accounts are recorded in what's called a Chart of Accounts.
The types are divided into two groups: left hand side (LHS) and right hand side (RHS) accounts. Assets and Expenses are LHS account, the rest are RHS accounts.
The balance of an account's ledger is calculated based on all of its debits and credits. For example, for a Sales Revenue account:
| Date | Description | Debit | Credit | Balance |
|---|---|---|---|---|
| 2025-11-4 | Opening Balance | 0 | ||
| 2025-11-5 | Cash Sale | 100 | 100 | |
| 2025-11-5 | Cash Sale | 50 | 150 | |
| 2025-11-6 | Return | 20 | 130 | |
| 2025-11-7 | Cash Sale | 200 | 330 | |
| --------- | --------------- | ----- | ------ | ------- |
| Total | 20 | 350 | 330 |
On the other hand, the Cost of Goods Sold (COGS) account for these transactions will look like this:
| Date | Description | Debit | Credit | Balance |
|---|---|---|---|---|
| 2025-11-4 | Opening Balance | 0 | ||
| 2025-11-5 | Cash Sale | 60 | 60 | |
| 2025-11-5 | Cash Sale | 20 | 80 | |
| 2025-11-6 | Return | 10 | 70 | |
| 2025-11-7 | Cash Sale | 150 | 220 | |
| --------- | --------------- | ----- | ------ | ------- |
| Total | 230 | 10 | 220 |
Notice that the value of the balance for the Sales (income/revenue) account increases with credits, but for the COGS (an expense account) the balance increases with debits.
For RHS accounts, credits increase the balance, debits decrease the balance. For LHS accounts, debits increase the balance, credits decrease the balance.
Thus,
- For LHS accounts,
(= balance (- debits credits)). - For RHS accounts,
(= balance (- credits debits)).
The balance of a transaction is calculated from the debits and credits on two or more accounts in the transaction. A transaction consists of a least one debit and one credit to two separate accounts.
Let's say we are a business that wants to record a cash sale of physical merchandise. A sale will affect that following accounts:
- Inventory (asset)
- Cash (asset)
- Sales Revenue (income)
- Cost of Goods Sold (COGS, expense)
Typically, transactions are visualized as a table with a debit and credit column:
| Account | Debit | Credit |
|---|---|---|
| Cash | 100 | |
| Sales Revenue | 100 | |
| COGS | 60 | |
| Inventory | 60 | |
| ------------- | ----- | ------ |
| Totals | 160 | 160 |
So in the table above, cash is debited (increasing in value), sales revenue (an income account) is credited (increasing in value). You now have recorded an increase in your cash-on-hand and your sales.
The Cost of Goods Sold account is debited (increasing in value), and your inventory is credited (decreasing in value). You have paid down the expense you had when purchasing an inventory good and the size or value of your inventory has decreased. By subtracting COGS from Sales Revenue, you can also know how much money you made from the transaction.
And the debit and credit totals match on both sides.
Ledgers are ways of viewing the data your system records. For example, a general ledger will show raw data: what transactions occurred. A sales ledger will show sales. An account ledger will show the transactions for an account and its current balance. An accounts payable aging report will show how much money you owe, to whom, and how overdue you might be on a certain account. All of these different ledgers provide insight into your accounting: cash flow, sales/returns, etc.
Requirements
Given the above, we need to provide the following functionality in our library:
- Account Creation
- Balanced Transaction Creation
- Account Balance Retrieval
- Chart of Accounts Balance Retrieval
The library should also provide the ability to easily create ledgers from the data it provides.
Testing & Examples
This library's functionality is going to be trickier than the almighty-money
library's was, so we're going to need to create tests. For that, we'll be using
lisp-unit2. We will do some basic unit tests, and we'll make some example
ledgers to demonstrate the library's use in an accounting system.
Dependencies
We will be using the almighty-money library we created along with some other
dependencies.
Since we are now adding dependencies to our project, I will introduce you to
vend, a library that replaces the defacto-standard quicklisp library for
dependency management. Let me explain why.
Vendor Your Dependencies
The guiding principle of the Almighty Lisp developer is to, above all else, prioritize simplicity. Simplicity is the key to writing code that you and others can maintain without fear or frustration.
When it comes to dependencies, simplicity looks like this:
- Generally avoid adding dependencies to a project unless necessary. If you can write a simple thing yourself that gets the job done, you probably should.
- When adding dependencies to your project, adopt them as your code. You are now responsible for their bugs, features, efficiency, etc.
- When possible, remove dependencies. Usually this means slowly extracting functionality from a dependency and rewriting it customized for your needs.
Some people feel that the trend to make dependencies easier to add to a
project via tools like npm or uv are detrimental to codebase quality and are
a security liability. If adding and maintaining dependencies with such tools is
easy, you will be tempted to add and keep large amounts of third-party code in
your codebase. Adding a dependency is and should feel like a serious, risky
decision.
While I can see the benefits of that mindset, the Almighty Lisp philosophy is one that is designed to maximize the effectiveness of the individual programmer. If you have a team of developers, you can expect to be able to work together to write the exact code that you need, both your library code and application code. Individual programmers need to prioritize writing application code over library code, which means individuals need to rely more heavily on dependencies.
If your brain is so big you can crank out the whole web stack by yourself while producing high-quality code, you can ignore the above. For the rest of us, we need a simple way of introducing dependencies into our projects that provide us the means to easily transition dependencies into code fit exactly to our purpose, free from the potential of introduced security vulnerabilities.
vend is a library that does precisely that by making it simple to vendor our
dependencies. Vend works by looking at our defsystem definitions, looks at
what other systems our system :depends-on, and cloning the requisite systems
from github into a directory in our project folder–defaulting to vendored.
If for some reason you don't want to use vend, you can feel free to use
quicklisp instead.
Installing vend
Installing vend is simple, but it does have one unusual requirement: it
requires ECL, a different implementation of Common Lisp (we installed SBCL at
the very beginning, before even installing Emacs). So first, we need to install
ECL.
If you're on Linux, you can try looking for a package for your distribution.
On MacOS, ECL is available on Homebrew.
Otherwise, you can follow the instructions below to compile ECL from the source. These come directly from the manual.
Download ECL
Download the latest release of ECL from their server.
Extract the source code and enter its directory
$ tar -xf ecl-xx.x.x.tgz
$ cd ecl-xx.x.x
Run the configuration file, build the program and install it
$ ./configure --prefix=/usr/local
$ make
$ make install
Make sure the program is installed and ready to run
$ /usr/local/bin/ecl
ECL (Embeddable Common-Lisp) 16.0.0
Copyright (C) 1984 Taiichi Yuasa and Masami Hagiya
Copyright (C) 1993 Giuseppe Attardi
Copyright (C) 2000 Juan J. Garcia-Ripoll
Copyright (C) 2015 Daniel Kochmanski
ECL is free software, and you are welcome to redistribute it
under certain conditions; see file 'Copyright' for details.
Type :h for Help.
Top level in: #<process TOP-LEVEL>.
>
Clone vend git repo
Now that you have ECL installed, you can install vend.
Clone the vend git repository from Github.
Run make
After cloning the repo, navigate into the vend directory and run the following commands:
make
make install
After that, enter vend in the command line (you may need to restart the
terminal) and you should see this:
vend - Vendor your Common Lisp dependencies
Commands:
check [focus] - Check your dependencies for issues
get - Download all project dependencies into 'vendored/'
graph [focus] - Visualise a graph of transitive project dependencies
init [name] - Create a minimal project skeleton
repl [args] - Start a Lisp session with only your vendored ASDF systems
search [term] - Search known systems
test [args] - Run all detected test systems
Flags:
--help - Display this help message
--version - Display the current version of vend
Emacs configuration
You need to configure Emacs to open the REPL via vend. To open your
config.el file, type C-h d c or SPC f P (doom/open-private-config) and
open config.el from the minibuffer. In your config.el file, add:
(setq sly-default-lisp 'sbcl
sly-lisp-implementations '((sbcl ("vend" "repl" "sbcl") :coding-system
utf-8-unix)
(ecl ("vend" "repl" "ecl") :coding-system
utf-8-unix)
(abcl ("vend" "repl" "abcl") :coding-system
utf-8-unix)
(clasp ("vend" "repl" "clasp") :coding-system
utf-8-unix)))
Can't find vend?
I had this error while testing on a fresh install of Ubuntu:
File local-variables error: (doom-hook-error lisp-mode-local-vars-hook
sly-editing-mode (file-missing Searching for program No such file or directory vend))
That means that Emacs can't find the vend executable. To resolve the error, I
installed the package exec-path-from-shell.
To install it, type SPC f P and open config.el. Add this to your config.el
file:
(use-package! exec-path-from-shell
:when (or (memq window-system '(mac ns x))
(daemonp))
:config
(exec-path-from-shell-initialize))
And add this to your packages.el file (in the same folder as the config.el):
(package! exec-path-from-shell)
Run doom sync in the terminal and restart doom with SPC q r
(doom/restart-and-restore).
Strangely, after I did this and then undid the changes, vend continued to
work. We all love software that just works, don't we folks?
Project Setup
Our project is called almighty-kaikei, so find-file (C-x C-f or SPC f f)
almighty-kaikei/almighty-kaikei.asd and save it. We're going to need tests
later, so just make a t directory, too.
Now that we have vend installed, we can get the rest of our dependencies.
First, we create a directory called almighty-kaikei and define our system in
almighty-kaikei.asd.
(defsystem "almighty-kaikei"
:author "Micah Killian"
:version "0.0.1"
:description "Almighty Double-Entry Accounting Program"
:depends-on (#:almighty-money #:local-time #:mito #:sxql)
:components ((:file "src/main")))
Make sure to save the file.
Now it's time to run vend. In the terminal, run this command:
% vend get
You will get an error saying that almighty-money isn't a known system. Not
surprising–it's on our computer!
To get around this, first we make the vendored directory ourselves and make
another directory inside it called almighty-money.
% mkdir vendored
% cd vendored
% mkdir almighty-money
Now we can make a symbolic link from where we saved almighty-money and the
directory we just made.
% ln -s ~/path/to/the/original/almighty-money almighty-money
If you did everything right, you can run vend get in the almighty-kaikei
directory. It will find the almighty-money.asd file and won't attempt to
search for or download it. Then it will download the rest of the dependencies as
expected.
Now that we have our system setup, let's create the main.lisp file in the
project root and define the package.
(defpackage #:almighty-kaikei
(:use #:cl)
(:nicknames #:almighty-kaikei/main #:ak)
(:local-nicknames (#:lt #:local-time)
(#:m #:mito)
(#:s #:sxql)
(#:am #:almighty-money)))
Now's a good time to learn a bit about our dependencies.
Introducing Our Dependencies
local-time is the defacto standard library for working with time, similar to
Python's datetime library.
mito is a SQL object-relational mapper. It's not such an opinionated ORM–we can
rely on simple SQL queries for the most part when using it. It does the work of
creating database tables and serializing between Lisp and SQL data.
sxql is a domain specific language for writing SQL in Lisp. I happen to really
enjoy doing everything in Lisp (in a later project we will write HTML with the
hsx library, another DSL). Putting everything I'm doing right in one spot,
rather than spreading it across several files, just feels right to me.
If you're not such an enthusiast of this style, you can check out the cl-yesql
library. It is a library similar to Clojure's Yesql library. It allows you to
use SQL from Lisp, rather than write it as with sxql.
Accounts
The first thing we need to do is provide a way of designating and knowing whether an account is a left-hand-side account or a right-hand-side account.
Recall that there are five basic categories of account: asset, expense, income, liability, and equity. Recall also that debits and credits influence the value of LHS and RHS differently and use a different formula for calculating their balance. So when we make accounts, we are going to assign it a certain category and we'll need to know which side it is in order to calculate its balance.
Open a buffer (C-x C-f or SPC f f) in almighty-kaikei/src/main.lisp and add this code:
(defparameter *left-hand-side* '(("asset" -1) ("expense" -1)))
(defparameter *right-hand-side* '(("income" 1) ("liability" 1) ("equity" 1)))
(defparameter *account-types* (append *left-hand-side* *right-hand-side*))
(defun valid-account-category-p (account-category)
"A predicate that takes an account category string. Returns a list of the
category and its side, or nil."
(assoc account-category *account-types* :test #'equal))
Just a reminder: the :test #'equal parameter in the call to assoc is necessary
because strings are arrays of characters, and two separate strings are two
vectors in living in different parts of your computer's memory. Since the
default for :test is #'eql, assoc usually tests if two things are in identical
places in memory. equal, on the other hand, will compare the structure of its
inputs.
(eql "hello" "hello") ; two separately instantiated arrays
; => NIL
(let ((str "hello"))
(eql str str)) ; same variable, same place in memory
; => T
(equal "hello" "hello")
; => T
Next, we define the account table with Mito:
(m:deftable account ()
((name :col-type :text)
(code :col-type :text)
(category :col-type :text)
(currency :col-type :text))
(:table-name "almighty_account")
(:documentation "A mito macro that defines a class and SQL table for
double-entry accounting accounts."))
(defmethod print-object ((this account) stream)
(print-unreadable-object (this stream :type t :identity t)
(with-slots (name code) this
(format stream "~a ~a" name code))))
(defun side (account)
"A function for getting the multiplier used for determining whether a
debit/credit increases or decreases the value of an account."
(second (assoc (account-category account) *account-types* :test #'equal)))
Mito's deftable macro expand into a defclass with a special :meta-class.
The slots of the class will all have accessors prefixed with the name of the
table. In this case, the accessor for the name slot is account-name. This is
similar to how structures work.
The :col-type slot option is for setting the SQL column type. In our case, we
are going to use SQLite, which only really has text and integer types. If we
were using PostgreSQL, we'd need to be more specific with our :col-types.
By default Mito will set the SQL table name to the name of the Lisp class
(account here), but we can manually set it with the :table-name class
option. Here we are namespacing it with the almighty_ prefix.
We also define a print-object method for the account class to make it easier
to understand when we print account objects to the REPL.
side will be important for when we enter and retrieve data from the database:
it will determine whether the amount entered or returned is negative or
positive.
We can manually construct account objects with make-instance, but we'll make
a custom constructor for convenience.
(defun make-account (name code category &optional (currency am:*default-currency*))
"A constructor for ACCOUNT objects. CURRENCY defaults to
ALMIGHTY-MONEY:*DEFAULT-CURRENCY*."
(unless (valid-account-category-p category)
(error "~a is not a valid account category." category))
(make-instance 'account :name name :code code :category category :currency
currency))
That also allows us to validate the category.
Because we need to store and retrieve accounts to and from SQL tables, we need some functions for doing that for accounts.
(defun get-account (account-code)
"A function that takes an ACCOUNT CODE and returns an ACCOUNT object. Returns an
error if an ACCOUNT with that CODE doesn't exist."
(let ((account (m:find-dao 'account :code account-code)))
(when account
account)))
(defun save-account (account)
"A function for saving ACCOUNT rows to a database. Returns an error if the
account already exists."
(let* ((code (account-code account))
(account-exists (get-account code)))
(when account-exists
(error "An account with code ~a already exists." code))
(m:insert-dao account)))
(defun get-or-save-account (account)
"A function that takes an ACCOUNT object and searches your database for an
ACCOUNT with the same CODE. If one exists, it's returned. Otherwise, it's
created and returned."
(let ((account-exists (get-account (account-code account))))
(if account-exists
account-exists
(m:insert-dao account))))
get-account makes it easy to get an account from a simple string containing an
account code.
save-account is self-explanatory: We don't want duplicate accounts, so we
prevent that with save-account by first doing a lookup for the account-code
of the account we're trying to create.
get-or-save-account is useful for times where you want to ensure an account
is saved in the database without causing an error if it already exists.
For all of these, we used mito and sxql to query our database. Mito provides
select-dao, a macro creating a list of Lisp objects containing the queried
data. The first argument is a symbol–the name of a table defined with
mito:deftable. It acts as the "SELECT * FROM [table-name]" part of a SQL
query. You then provide the rest of the query using sxql.
Now let's define the transaction table.
Transactions
When a transaction is made, such as a sale, a return, a payment, etc. we need to create different parts (what we'll call Legs)–debits and credits–that we need a way to know which parts go together. Transactions timestamps and ids are doing to be how we refer to groups of Legs.
(m:deftable transaction ()
((date :col-type :timestamp
:inflate #'lt:universal-to-timestamp
:deflate #'lt:timestamp-to-universal))
(:table-name "almighty_transaction"))
(defmethod print-object ((this transaction) stream)
(print-unreadable-object (this stream :type t :identity t)
(with-slots (date) this
(format stream "~a" date))))
(defun make-transaction (&optional (date (lt:now)))
(make-instance 'transaction :date date))
Mito provides the ability to set how a slot "inflates" and
"deflates"–serializes into Lisp or SQL values. We are using local-time to
convert between universal times and local-time timestamps.
According to the HyperSpec about universale time (https://cl-community-spec.github.io/pages/Universal-Time.html),
Universal time is an absolute time represented as a single non-negative integer—the number of seconds since midnight, January 1, 1900 GMT (ignoring leap seconds).
While it's not strictly necessary to serialize to universal times before saving to the database, it will make future queries easier.
make-transaction sets the default value of the date slot to local-time:now.
Legs
The real fun begins with legs. Legs are like entries in the accounting books: This account was debited this amount at this time. A Transaction has to have at least two Legs–a debit and a credit–to be balanced. The whole purpose of a double-entry accounting system is to record at least two sides of a transaction to reduce errors in money tracking. If a transaction doesn't balance, that's a sign that there is an error in money tracking.
So first, let's setup our leg table.
(defparameter *debit* -1)
(defparameter *credit* 1)
(m:deftable leg ()
((account :col-type account)
(side :col-type :integer)
;;; Use LEG-ENTRY to access ENTRY-AMOUNT and ENTRY-CURRENCY as an
;;; ALMIGHTY-MONEY:MONEY object.
(entry-amount :col-type :integer) ; ALMIGHTY-MONEY:MONEY object AMOUNT.
(entry-currency :col-type :text) ; ALMIGHTY-MONEY:MONEY object CURRENCY-CODE.
(transaction :col-type (or transaction :null)))
(:table-name "almighty_leg"))
(defmethod print-object ((this leg) stream)
(print-unreadable-object (this stream :type t :identity t)
(format stream "~a ~:[DEBIT~;CREDIT~] ~a ~a"
(account-code (leg-account this))
(plusp (leg-side this))
(leg-entry this)
(leg-transaction this))))
We will represent debits as -1 and credits as 1. That will be useful both for
printing and balancing transactions and the Chart of Accounts.
Mito allows us to assign :col-type to other tables, creating a relationship
between different tables. The SQL table will actually have account_id and
transaction_id columns corresponding to the ids of the other table rows.
side records whether a leg is a debit or a credit. Debits will be represented
with -1, and credits with 1.
entry-amount and entry-currency will record the amount of a certain currency
of the leg. Ideally, we would be able to define some kind of custom :col-type
for an almighty-money:money object and name it entry, but mito doesn't
provide the ability to create custom column types on the Lisp side, so we will
simulate it by creating an "accessor" for entries:
(defgeneric leg-entry (leg)
(:documentation "A function for getting the ENTRY-AMOUNT and ENTRY-CURRENCY of
a LEG object and returning a MONEY object.")
(:method ((this leg))
(am:make-money (leg-entry-amount this) (leg-entry-currency this))))
Since all class slot accessors are generic functions, I am implementing
leg-entry as a generic, too.
Now we need a constructor.
(defun make-leg (account side entry transaction)
"A constructor for making `leg' objects. `account' can either be an `account' object or
an `account-code'. `entry' must receive an `almighty-money:money' object.
Intended for use in `transact!' where `legs' are created with the `debit' and
`credit' functions, and the `transact!' adds a `transaction' to the legs once it
determines if the sides of the transaction are balanced."
(let ((account (etypecase account
(account account)
(string (get-account account)))))
(unless (am:moneyp entry)
(error 'type-error :expected-type 'am:money :datum entry))
(make-instance 'leg :account account
:side side
:entry-amount (am:settled-amount entry)
:entry-currency (am:currency-code entry)
:transaction transaction)))
We use etypecase to convert from one a string account-code to an account
object if necessary. We want almighty-money:money objects passed as the
entry, so we signal a type-error condition if we don't get a money object
in the entry position.
Usually we don't want to use make-leg directly. Instead, we'll provide some
helper functions for making debits and credits.
(defun debit (account amount)
"A function for constructing a `leg' object with a side of `*debit*'. The `transaction'
slot will be modified later before saving the row to the database.
Usage examples:
(debit \"CASH000\" (usd 5000))
(debit \"COGS000\" 10000)
"
(let ((account (etypecase account
(account account)
(string (get-account account))))
(amount (etypecase amount
(integer (am:make-money amount))
(am:money amount))))
(make-leg account *debit* (am:money* amount (side account) *debit*) nil)))
(defun credit (account amount)
"A function for constructing a `leg' object with a side of `*credit'. The `transaction'
slot will be modified later before saving the row to the database.
Usage example:
(credit \"REV000\" (usd 5000))
(credit \"SLS000\" 10000)
"
(let ((account (etypecase account
(account account)
(string (get-account account))))
(amount (etypecase amount
(integer (am:make-money amount))
(am:money amount))))
(make-leg account *credit* (am:money* amount (side account) *credit*) nil)))
The amount can be either a almighty-money:money object, or a simple integer. If
it's an integer, then we convert it to a money object using the
almighty-money:*default-currency*. Unless you're dealing with multiple
currencies, you can simply set the *default-currency* for your application and
save yourself some typing.
Remember that almighty-money only supports USD and JPY at the moment. If you
want to use some other currency in almighty-kaikei, you need to add support to
almighty-money.
The important bit is the fact that we take the amount and multiply it by the
side of the account and by *debit* or *credit*. An asset account that is
credited should save a negative amount into the database–crediting a
left-hand-side account should decrease its value. On the other hand, crediting a
right-hand-side account should increase its value.
It might be clarifying to look at the math this way:
(let ((lhs -1)
(rhs 1)
(debit -1)
(credit 1)
(amount 7))
(list (* amount lhs debit)
(* amount lhs credit)
(* amount rhs debit)
(* amount rhs credit)))
; => (7 -7 -7 7)
We're getting close to being able to make a transaction. Before we get there, we need to be able to tell debit legs from credit legs so we can see if the two sides of the transaction are balanced.
(defun debit-p (leg)
(= (leg-side leg) *debit*))
(defun credit-p (leg)
(= (leg-side leg) *credit*))
(defun sum-all-if (fun legs)
(let ((filtered (remove-if-not fun legs)))
(if filtered
(reduce #'am:money+ (mapcar #'(lambda (leg)
(am:make-money (abs (leg-entry-amount leg))
(leg-entry-currency leg)))
filtered)))))
(defun sides-balanced-p (legs)
(let ((debits (sum-all-if #'debit-p legs))
(credits (sum-all-if #'credit-p legs)))
(= 0 (- (am:amount debits) (am:amount credits)))))
As mentioned before, crediting a left-hand-side account will result in a
negative value being passed to make-leg, the same as if you debited a
right-hand-side account like an income account. Keeping every transaction zero'd should
help keep the Chart of Accounts zero'd, too.
Finally, we make the transact! function.
(define-condition unbalanced-transaction (error)
((debit-amount :initarg :debit-amount :reader debit-amount)
(credit-amount :initarg :credit-amount :reader credit-amount)
(message :initarg :message :reader message
:initform "Left and right entries of the transaction do not balance."))
(:report (lambda (condition stream)
(with-slots (debit-amount credit-amount message) condition
(format stream "~a LEFT: ~a | RIGHT: ~a" message debit-amount credit-amount)))))
(defun record-transaction (transaction legs)
"A function for saving the data rows of a `transaction' to a SQL database: `legs' for
the `transaction', and the `transaction' itself."
(dbi:with-transaction m:*connection*
(let ((transaction (m:insert-dao transaction)))
(loop :for leg :in legs
:do (m:insert-dao leg))
transaction)))
(defun transact! (&rest legs)
"A function for making a double-entry accounting transaction having at least
one debit and one credit leg.
If there is a LOCAL-TIME:TIMESTAMP in LEGS, the first one is used for the
transaction. If no LOCAL-TIME:TIMESTAMP is in LEGS, uses LOCAL-TIME:NOW for the
transaction.
Returns an UNBALANCED-TRANSACTION error if debits /= credits.
Usage Examples:
(transact! (debit \"CASH000\" 5000)
(credit \"REV000\" 5000))
(transact! (local-time:parse-timestring \"2025-12-25\")
(debit \"CASH000\" 5000)
(credit \"REV000\" 5000))
"
(let ((date (or (find-if (lambda (x) (typep x 'lt:timestamp)) legs) (lt:now)))
(legs (remove-if-not (lambda (x) (typep x 'leg)) legs)))
(unless (> (length legs) 1)
(error "transactions must have at least two legs."))
(unless (and (find-if #'debit-p legs)
(find-if #'credit-p legs))
(error "transactions must have at least one debit and one credit leg."))
(let ((transaction (make-transaction date))
(debits (sum-all-if #'debit-p legs))
(credits (sum-all-if #'credit-p legs)))
(unless (sides-balanced-p legs)
(error 'unbalanced-transaction
:debit-amount (am:amount debits)
:credit-amount (am:amount credits)))
(loop :for leg :in legs
:do (setf (leg-transaction leg) transaction))
(record-transaction transaction legs))))
If we look at transact! first, it can take an arbitrary number of legs. Since
legs is a &rest parameter, legs will be accessible in a list. First, we
check to see if any of the arguments are a local-time:timestamp. If we find
one, we will use that for the transaction.
After that, we're going to ensure first that we have at least two legs. We also
ensure that there is at least one debit and one credit in there. If the sides
are all balanced, we add a transaction to the transaction slot of all the
legs and save the transaction to the database. record-transaction makes use
of dbi:with-transaction to make the entire creation of the transaction row
and all of the leg rows. If adding one record fails, they all fail together to
prevent data corruption.
If the sides are unbalanced, we signal an unbalanced-transaction error.
Creating and signaling a condition will make it easy to take specific actions
for that kind of error (in a web application, we'd like to send the error to the
user in the browser, for example).
Playing around
Before we continue, how about we try playing with the code. In order to actually create any data, first we need to connect to our database and create tables. For right now, let's just keep things simple by establishing a long-lasting connection at toplevel.
(mito:connect-toplevel :sqlite3 :database-name "t/test.db")
#<DBD.SQLITE3:DBD-SQLITE3-CONNECTION {7009C903B3}>
If you forgot to make a t directory for tests during project setup, you should
do that before running the above code.
Later, you can disconnect using disconnect-toplevel
(mito:disconnect-toplevel)
After establishing a connection, we need to create the tables. Mito provides a useful function for this purpose:
(m:ensure-table-exists 'account)
(m:ensure-table-exists 'transaction)
(m:ensure-table-exists 'leg)
[abbreviated]...
CREATE TABLE "almighty_leg" (
"id" INTEGER PRIMARY KEY AUTOINCREMENT,
"account_id" INTEGER NOT NULL,
"side" INTEGER NOT NULL,
"entry_amount" INTEGER NOT NULL,
"entry_currency" TEXT NOT NULL,
"transaction_id" INTEGER,
"created_at" TIMESTAMP,
"updated_at" TIMESTAMP
) () [0 rows] (809ms) | SLYNK::CALL-WITH-RETRY-RESTART
=> (#<SXQL-STATEMENT: CREATE TABLE almighty_leg (
id INTEGER PRIMARY KEY AUTOINCREMENT,
account_id INTEGER NOT NULL,
side INTEGER NOT NULL,
entry_amount INTEGER NOT NULL,
entry_currency TEXT NOT NULL,
transaction_id INTEGER,
created_at TIMESTAMP,
updated_at TIMESTAMP
)>)
ensure-table-exists will look to see if a table exists. If it doesn't, it
creates it. Otherwise, it returns nil.
Once you have your tables, you need some accounts to mess with.
(save-account (make-account "Cash" "CSH000" "asset"))
(save-account (make-account "Sales Revenue" "REV000" "income"))
#<ACCOUNT Sales Revenue REV000 {70056C6183}>
We'll just make these for now.
Let's make a sale: customer pays cash for something.
(transact! (debit "CSH000" (am:usd 5000))
(credit "REV000" (am:usd 5000)))
#<TRANSACTION 2026-06-19T11:18:03.315517Z {700926C563}>
We can look at the contents of the database using Emacs' built-in sqlite-mode
for looking at SQLite databases. Type M-x sqlite-mode-open-file and open
t/test.db. You should see the names of the tables we made and the number of rows
in each table.
| Table Name | Number of Rows |
|---|---|
| almightyaccount | 2 |
| almightyleg | 2 |
| almightytransaction | 1 |
If you switch from Evil's Normal-mode to Emacs-mode then you can even press Enter on a table
name and see the rows and their data.
Account Balance
So we've confirmed that we're saving data to the database, but now we need to know something about the accounts: What are their current balances? For that, we need write another function.
(defun universal-now ()
(lt:timestamp-to-universal (lt:now)))
(defun account-legs (account &optional (as-of (universal-now)))
"A function for getting all the legs of an account as of a certain time,
defaulting to now.
Usage Examples:
(account-legs \"CSH000\")
(account-legs sales-account-object transaction-object)
(account-legs cogs-account-object (local-time:parse-timestring \"2025-11-27\"))"
(let* ((account (etypecase account
(account account)
(string (get-account account))))
(as-of (etypecase as-of
(lt:timestamp (lt:timestamp-to-universal as-of))
(transaction (lt:timestamp-to-universal (transaction-date as-of)))
(string (lt:timestamp-to-universal (lt:parse-timestring as-of)))
(integer as-of))))
(m:select-by-sql 'leg
(s:select :*
(s:from (:as :almighty_leg :l))
(s:inner-join (:as :almighty_transaction :t) :on (:= :l.transaction_id :t.id))
(s:where (:and (:= :l.account_id (m:object-id account))
(:<= :t.date as-of)))))))
(defun account-balance (account &optional (as-of (universal-now)))
"A function for getting the balance of an account at a certain point in time,
defaulting to now.
Usage Example:
(account-balance \"CSH000\")
(account-balance sales-account-object)"
(let ((account (if (stringp account)
(get-account account)
account))
(as-of (etypecase as-of
(lt:timestamp (lt:timestamp-to-universal as-of))
(transaction (lt:timestamp-to-universal (transaction-date as-of)))
(string (lt:timestamp-to-universal (lt:parse-timestring as-of)))
(integer as-of))))
(sql-account-balance account as-of)))
(defun sql-account-balance (account as-of)
(let ((result (first (m:retrieve-by-sql
(s:select ((:as (:coalesce (:sum :leg.entry_amount) 0) :amount)
(:as :leg.entry_currency :currency))
(s:from (:as :almighty_account :account))
(s:inner-join (:as :almighty_leg :leg) :on (:= :leg.account_id :account.id))
(s:inner-join (:as :almighty_transaction :tran) :on (:= :leg.transaction_id :tran.id))
(s:where (:and (:= :account.id (m:object-id account))
(:<= :tran.date as-of)))
(s:group-by :leg.entry_currency))))))
(if result
(am:make-money (second result) (fourth result))
(am:make-money 0 (account-currency account)))))
Both functions make use of sxql to write SQL queries, and mito for running the
queries on the database. account-legs does a simple join, while account-balance
returns the sum of all of the legs of the account. To avoid long-term performance
issues stemming from the creation of many leg objects and then running
calculations on them on the Lisp side, we're going to do the math on the SQL
side and simply return the result as an almighty-money object.
mito:retrieve-by-sql will return a plist like this:
(:AMOUNT 5000 :CURRENCY "USD")
Hence the calls to second and fourth for building the money object.
Final Package Definition
Now that we're all done writing the almighty-kaikei library, make sure to export
all the necessary symbols in the defpackage.
(defpackage #:almighty-kaikei
(:use #:cl)
(:local-nicknames (#:lt #:local-time)
(#:m #:mito)
(#:s #:sxql)
(#:am #:almighty-money))
(:import-from #:almighty-money
#:*default-currency*)
(:export
#:*default-currency*
#:with-db-connection
#:account
#:account-name
#:account-code
#:account-category
#:account-currency
#:make-account
#:get-account
#:account-transactions
#:transaction
#:transaction-date
#:make-transaction
#:leg
#:leg-account
#:leg-account-id
#:leg-side
#:leg-entry-amount
#:entry-amount
#:leg-entry-currency
#:leg-transaction
#:leg-transaction-id
#:leg-entry
#:make-leg
#:debit
#:credit
#:debit-p
#:credit-p
#:sum-all-if
#:sides-balanced-p
#:unbalanced-transaction
#:transact!
#:transaction-legs
#:account-legs
#:account-balance
#:account-legs-before-transaction
#:universal-now
#:account-balance-before-transaction
#:get-or-save-account
#:predicate
#:test-db-path
#:entry-currency
#:save-account
#:sql-account-balance
#:account-balance-fast
#:account-balance-slow
#:*debit*
#:*credit*
#:side
#:chart-of-accounts-balanced-p
#:lhs-account-p))
Writing an Example Accounting Application
Now that we have the basic functionality of our library finished, it's time to use it in an application.
Our theoretical application is an accounting application for a hotel that rents rooms and sells "goods" like foods, drinks, etc.
In the next chapter, we are going to deploy this application as a CLI application that runs transactions and returns reports. For the remainder of this chapter, we'll build what we'll later deploy. We'll approach this application like this:
- Setup, which will include preparing to make a binary executable.
- Writing database queries.
- Writing "views". These will be
formatstrings, so hold on to your butt. - Writing some tests just to make sure our queries and views work as we want them to.
- Writing code that produces CLI commands via
clingon.
Setup
For this, we will make a separate project called almighty-accounting
We need a system for this example, so let's set that up.
;;; almighty-accounting/almighty-accounting.asd
(defsystem "almighty-accounting"
:author "Micah Killian"
:version "0.0.1"
:description "A rudimentary accounting system controlled via CLI."
:depends-on (#:almighty-money #:almighty-kaikei #:local-time #:mito #:sxql #:dbi #:dbd-sqlite3 #:clingon)
:serial t
:components ((:file "db")
(:file "transactions")
(:file "views")
(:file "commands")
(:file "main"))
:build-operation "program-op"
:build-pathname "almighty-accounting"
:entry-point "almighty-accounting:main")
Make sure to make a vendored directory and symlink almighty-money and
almighty-kaikei in the directory before you run vend get to grab the other dependencies.
The :build-operation, :build-pathname, and :entry-point are new asdf
functionality we haven't seen yet. They are for building an executable file
later in the next chapter on deploying.
The dbd-sqlite3 is a cl-dbi library driver for SQLite. If we don't include this,
we'll have trouble later when building the binary.
The clingon library is used for making command-line commands. We'll see how it's
used later.
Queries
Open a buffer to almighty-accounting/src/db.lisp and define the package:
(defpackage #:almighty-accounting/db
(:use #:cl)
(:local-nicknames (#:lt #:local-time)
(#:m #:mito)
(#:s #:sxql)
(#:ak #:almighty-kaikei)
(#:am #:almighty-money)))
(in-package #:almighty-accounting/db)
This package will be in charge of helping us work with the database: establishing a connection, making select queries, and doing. First, we need some common utilities for all of our query functions.
(defmacro with-universals (bindings &body body)
`(let ,(loop :for (var val) :in bindings
:collect (list var
`(lt:timestamp-to-universal
(if (stringp ,val)
(lt:parse-timestring ,val)
,val))))
,@body))
(defmacro with-db-connection (db-name &body body)
`(let ((mito:*connection* (dbi:connect :sqlite3 :database-name db-name)))
(unwind-protect (progn ,@body)
(dbi:disconnect mito:*connection*))))
(defun system-db-path (path)
(let ((db-path (merge-pathnames path)))
(ensure-directories-exist db-path)
db-path))
(defmacro with-chart-of-accounts (path &body body)
`(let ((db-name (if (string-equal ":memory:" (pathname-name ,path))
,path
(system-db-path ,path))))
(with-db-connection db-name
; Asset Accounts
(let ((cash-account (ak:make-account "Cash" "CSH000" "asset"))
(inventory-account (ak:make-account "Inventory" "INV000" "asset"))
(accounts-receivable-account (ak:make-account "Accounts Receivable" "REC000" "asset"))
; Expense Accounts
(cost-of-goods-sold-account (ak:make-account "Cost of Goods Sold" "CGS000" "expense"))
(general-expenses-account (ak:make-account "General Expenses" "GEN000" "expense"))
; Income Accounts
(sales-revenue-account (ak:make-account "Sales Revenue" "SLS000" "income"))
(room-revenue-account (ak:make-account "Room Revenue" "RMR000" "equity"))
; Tax Liability Account
(tax-account (ak:make-account "Tax Payable" "TAX000" "liability")))
(let ((mito:*mito-logger-stream* nil)
(mito:*mito-migration-logger-stream* nil))
(mito:ensure-table-exists 'ak:account)
(mito:ensure-table-exists 'ak:transaction)
(mito:ensure-table-exists 'ak:leg))
;; Asset
(ak:get-or-save-account cash-account)
(ak:get-or-save-account inventory-account)
(ak:get-or-save-account accounts-receivable-account)
;; Expense
(ak:get-or-save-account cost-of-goods-sold-account)
(ak:get-or-save-account general-expenses-account)
;; Income
(ak:get-or-save-account sales-revenue-account)
(ak:get-or-save-account room-revenue-account)
;; Tax Liability Account
(ak:get-or-save-account tax-account)
,@body))))
To begin with, we have with-universals. This converts local-time:timestamp
objects into Common Lisp universal times.
Earlier, we wrote the transaction deftable in order to automatically
serialize timestamps to universal times as well as the reverse. For our queries,
if we ever want to filter rows by timestamps, we might be tempted to use
local-time:timestamp objects directly, but they don't work. We could try
converting timestamps into a format that SQLite understands that still looks
like a timestamp, but for our purposes, a simple integer will do.
with-db-connection is for connecting to the database. unwind-protect will disconnect from
the database once the body form returns.
system-db-path ensures that we have a place to put our database file.
merge-pathnames will take the current working directory in the REPL (which can
be modified in the REPL using ,set-directory or by syncing the REPL to a
package with SPC m r s or M-x sly-mrepl-sync) and merge the arguments into it.
ensure-directories-exist will take whatever pathname merge-pathnames
returns and create it if it doesn't already exist.
with-chart-of-accounts will open a db connection and create a chart of accounts
and save it to a SQLite file if one doesn't already exist. We'll wrap our
entry-point to our CLI application with this macro.
Since our application is for a hotel, we include "Room Revenue" separately from the rest of our revenue.
Next, we need to set up our database queries. We'll need to get some information
like the number of transactions, number of sales, all of the legs and sales for
a period of time, and a trial-balance. A trial-balance shows the total balance
of all accounts, summing each account's debits and credits (which will be either
positive or negative depending on the type of account).
(defun transaction-count-for-period (from to)
(with-universals ((from from)
(to to))
(second (first (m:retrieve-by-sql
(s:select ((:count :*))
(s:from :almighty_transaction)
(s:where (:and
(:>= :almighty_transaction.date from)
(:<= :almighty_transaction.date to)))))))))
(defun legs-for-period (from to)
(with-universals ((from from)
(to to))
(m:select-dao 'ak:leg
(m:joins 'ak:transaction)
(s:where (:and
(:>= :almighty_transaction.date from)
(:<= :almighty_transaction.date to)))
(s:order-by :almighty_transaction.id))))
(defun sales-count-for-period (from to)
(with-universals ((from from)
(to to))
(second (first (m:retrieve-by-sql
(s:select ((:count :*))
(s:from :almighty_transaction)
(s:inner-join :almighty_leg :on (:= :almighty_leg.transaction_id :almighty_transaction.id))
(s:where (:and
(:or (:= :almighty_leg.account_id (m:object-id (ak:get-account "RMR000")))
(:= :almighty_leg.account_id (m:object-id (ak:get-account "SLS000"))))
(:= :almighty_leg.side ak:*credit*)
(:>= :almighty_transaction.date from)
(:<= :almighty_transaction.date to)))))))))
(defun sales-for-period (from to)
(with-universals ((from from)
(to to))
(m:select-dao 'ak:leg
(m:joins 'ak:transaction)
(s:where (:and
(:or (:= :almighty_leg.account_id (m:object-id (ak:get-account "RMR000")))
(:= :almighty_leg.account_id (m:object-id (ak:get-account "SLS000"))))
(:= :almighty_leg.side ak:*credit*)
(:>= :almighty_transaction.date from)
(:<= :almighty_transaction.date to)))
(s:order-by :almighty_transaction.id))))
(defun trial-balance ()
;; There is currently a bug in mito that causes a syntax error when running
;; this query through RETRIEVE-BY-SQL.
;; Instead we are using the dbi interface directly.
(multiple-value-bind (query binds)
(s:yield (s:select ((:as :account.name :account)
(:as :leg.entry_currency :currency)
(:as (:sum (:case
:WHEN :leg.side := ak:*debit*
:THEN :leg.entry_amount
:else 0))
:debit_amount)
(:as (:sum (:case
:WHEN :leg.side := ak:*credit*
:THEN :leg.entry_amount
:else 0))
:credit_amount)
(:as (:sum :leg.entry_amount) :total_amount))
(s:from (:as :almighty_account :account))
(s:inner-join (:as :almighty_leg :leg)
:on (:= :leg.account_id :account.id))
(s:group-by :account.name :leg.entry_currency)
(s:order-by :account.name :leg.entry_currency)))
(dbi:fetch-all (dbi:execute (dbi:prepare mito:*connection* query)
binds))))
Note that we hardcode the "RMR000" and "SLS000" accounts in the sales queries.
A more robust accounting system would likely have a "concrete" account categories system
built on top of the base almighty-kaikei one that only has "abstract" categories
like "asset" or "income", and we would have concrete accounts that inherit from
an abstract accounts defclass/deftable.
mito:retrieve-by-sql will return values like this:
((:|COUNT(*)| 0))
Using assoc to get the value 0 would be more trouble than it's worth, so we just
use first and second instead. If we had a lot of these kinds of queries, we
would make a utility function, but we don't, so we won't.
On the other hand, mito:select-dao will return a list of objects, like leg or
transaction.
The trial-balance function uses the cl-dbi API to query the database because of
a bug in mito. sxql:yield returns two values: a SQL query string with
place-holders for values to be inserted, and a list of values to insert in those
place-holders. From the docs (https://github.com/fukamachi/sxql#usage-v1-api):
(select (:id :name :sex)
(from (:as :person :p))
(where (:and (:>= :age 18)
(:< :age 65)))
(order-by (:desc :age)))
#<SXQL-STATEMENT: SELECT id, name, sex FROM person AS p WHERE ((age >= 18) AND (age < 65)) ORDER BY age DESC>
(yield *)
"SELECT id, name, sex FROM person AS p WHERE ((age >= ?) AND (age < ?)) ORDER BY age DESC" (18 65)
We bind the result of our query to query and binds and with multiple-value-bind
and then pass them as arguments to dbi:prepare and dbi:execute. Finally,
dbi:fetch-all will return the result of executing the query in a list of plists.
With that, we're done with writing our query functions. Make sure to export the relevant symbols.
(defpackage #:almighty-accounting/db
(:use #:cl)
(:local-nicknames (#:lt #:local-time)
(#:m #:mito)
(#:s #:sxql)
(#:ak #:almighty-kaikei)
(#:am #:almighty-money))
(:export
#:with-db-connection
#:with-chart-of-accounts
#:transaction-count-for-period
#:sales-count-for-period
#:sales-for-period
#:legs-for-period
#:trial-balance))
So now we have queries for getting leg data and data specific to the Room
Revenue (sales) account. Next, let's make the functions for actually making
transactions.
Transactions
Open a buffer to almighty-accounting/src/transactions.lisp and add the following
code to the top:
(defpackage #:almighty-accounting/transactions
(:use #:cl)
(:local-nicknames (#:ak #:almighty-kaikei)
(#:am #:almighty-money)))
(in-package #:almighty-accounting/transactions)
Here we're going to write our "business logic" for our little accounting application.
(defparameter *sales-tax-ratio* 10) ; 10%
(defparameter *card-fee-ratio* 5) ; 5%
(defun calculate-sales-tax (money)
(am:money* money (/ *sales-tax-ratio* 100)))
(defun calculate-card-fee (money)
(am:money* money (/ *card-fee-ratio* 100)))
We're going to simulate dealing with sales tax and credit card processing fees, so these helper functions will come in handy.
Now, let's make our first function, starting with a cash-reserve-room transaction:
(defun cash-reserve-room (room)
"Reserve `room' with a cash payment and post the journal entry.
`room' is a list whose second element is the gross amount paid in
cash (tax-inclusive). Returns the posted transaction.
The accounting:
0. Gross includes sales tax and is the cash you receive.
1. Net revenue is whatever you get to keep after paying sales tax.
You probably would want to include expenses like electricity, amenities, clean
up, etc. somewhere in the calculation."
(let* ((gross-revenue (second room))
(sales-tax (calculate-sales-tax gross-revenue))
(net-revenue (am:money- gross-revenue sales-tax)))
(ak:transact! (ak:debit "CSH000" gross-revenue)
(ak:credit "TAX000" sales-tax)
(ak:credit "RMR000" net-revenue))))
This is the simplest transaction: get some cash, calculate your sales tax burden and record the net revenue.
Next, we want to do the same thing, but for a credit-card transaction:
(defun card-reserve-room (room)
"Reserve `room' with a card payment and post the journal entry.
`room' is a list whose second element is the gross amount charged to the
customer's card (tax-inclusive). Returns the posted transaction.
The accounting:
0. Gross includes sales tax.
1. Taxes are a portion of the gross so the tax liability increases (credited
to TAX000).
2. The card fee is a portion of the gross taken immediately by the processor.
It is our expense, so we increase expenses (debit GEN000).
3. Because the processor keeps the fee, it never lands in our bank. So
Accounts Receivable is booked NET of the fee (debit REC000 for gross minus
the fee).
4. Revenue is the ex-tax room price (gross minus taxes) and increases on a
sale (credited to SLS000).
5. This sale's net profit, returned as the second value, is revenue minus the
card fee (the only expense on this entry)."
(let* ((gross-revenue (second room))
(sales-tax (calculate-sales-tax gross-revenue))
(card-fee (calculate-card-fee gross-revenue))
(receivable (am:money- gross-revenue card-fee))
(net-revenue (am:money- gross-revenue sales-tax)))
(ak:transact! (ak:debit "REC000" receivable)
(ak:debit "GEN000" card-fee)
(ak:credit "TAX000" sales-tax)
(ak:credit "SLS000" net-revenue))))
The code is hardly more complicated, but the accounting feels like it's taken a step up. The explanation in the documentation string gives you the details if you want. The basic difference is that the money we actually get comes minus the cost of the card processing fee.
We have cash and card functions for making transactions for rooms, but now we need transactions for "goods". The difference between rooms and goods is that usually we only make a reservation one room at a time, whereas goods are often bought in groups (as in a cart) and quantities, so our data model will be slightly modified from the one for rooms.
We'll want to sum up the price for all the goods, so first we'll make the necessary helper functions.
(defun good-name (good)
(first good))
(defun good-quantity (good)
(second good))
(defun good-procurement-price (good)
(third good))
(defun good-sale-price (good)
(fourth good))
(defun total-sale-price-of-good (good)
(let ((quantity (good-quantity good))
(sale-price (good-sale-price good)))
(am:money* sale-price quantity)))
(defun total-procurement-cost-of-good (good)
(let ((quantity (good-quantity good))
;; Pretend we have a database with procurement prices and we grab that
;; here.
(procurement-price (good-procurement-price good)))
(am:money* procurement-price quantity)))
A good is a list of lists. Inner lists are four elements in length, with the
good's name, quantity, procurement-price, and sale-price. A "procurement" price is the price we pay to
some vendor to buy a good to add to inventory, whereas the "sale" price is the
one we sell to "our" customers.
For our first goods-related function, we'll work with cash.
(defun cash-sale-of-goods (goods)
"Make a cash sale of a list of `goods'.
`goods' is a list of goods. Each `good' is a list whose second element is
quantity and the third element is the per-unit price of the good. Total price
of `goods' is quantity times unit-price. Returns posted transaction.
The accounting:
0. Gross includes sales tax (`good' prices include tax).
1. Taxes are a portion of the gross, owed to the government later, so the
tax liability increases (credited to TAX000).
2. Net revenue is gross - sales tax.
3. We have less inventory, so we credit (reducing in value) inventory the
total cost of the goods sold.
4. Our inventory assets have been consumed, so we debit (increasing in value)
the Cost of Goods Sold account, recognizing the expense."
(let* ((gross-revenue (reduce #'am:money+ (mapcar #'total-sale-price-of-good goods)))
(sales-tax (calculate-sales-tax gross-revenue))
(net-revenue (am:money- gross-revenue sales-tax))
(inventory (reduce #'am:money+ (mapcar #'total-procurement-cost-of-good goods)))
(cogs inventory))
(ak:transact! (ak:debit "CSH000" gross-revenue)
(ak:credit "TAX000" sales-tax)
(ak:credit "SLS000" net-revenue)
(ak:credit "INV000" inventory)
(ak:debit "CGS000" cogs))))
Again, the accounting is going to be the tougher part to follow. The major
additions here are how we mapcar and reduce to get the total price of the
inventory "goods" sold and then deduct that price from our inventory account,
and then "recognize" the expense-cost of those goods by debiting a "Cost of
Goods Sold" account.
Finally, we come to our most complicated transaction: a card sale of goods.
(defun card-sale-of-goods (goods)
"Make a card sale of a list of `goods'.
`goods' is a list of goods. Each `good' is a list whose second element is
quantity and the third element is the per-unit price of the good. Total price of
`goods' is quantity times unit-price. Returns posted transaction.
The accounting:
0. Gross includes sales tax (`good' prices include tax).
1. Taxes are a portion of the gross, owed to the government later, so the tax
liability increases (credited to TAX000).
2. Net revenue is gross - sales tax.
3. We have less inventory, so we credit (reducing in value) inventory the total
cost of the goods sold.
4. We also paid down the cost of those goods, so we debit (reducing in value)
the Cost of Goods Sold account.
5. Our net revenue is gross minus sales taxes.
6. Receivable (what you'll actually get) is gross minus fees.
7. Inventory decreases via credit, COGS increases via debit, recognizing that
inventory has been consumed and that the inventory had a cost associated with
it."
(let* ((gross-revenue (reduce #'am:money+ (mapcar #'total-sale-price-of-good goods)))
(sales-tax (calculate-sales-tax gross-revenue))
(card-fee (calculate-card-fee gross-revenue))
(net-revenue (am:money- gross-revenue sales-tax))
(receivable (am:money- gross-revenue card-fee))
(inventory (reduce #'am:money+ (mapcar #'total-procurement-cost-of-good goods)))
(cogs inventory))
(ak:transact! (ak:debit "REC000" receivable)
(ak:debit "GEN000" card-fee)
(ak:credit "TAX000" sales-tax)
(ak:credit "SLS000" net-revenue)
(ak:credit "INV000" inventory)
(ak:debit "CGS000" cogs))))
Again, if you need a detailed explanation of the accounting, take a look at the documentation string. The important difference again is the introduction of a card fee and its influence on the receivable value.
With that, we have all of our transaction-making functions complete. Make sure to export all of the relevant symbols.
(defpackage #:almighty-accounting/transactions
(:use #:cl)
(:local-nicknames (#:ak #:almighty-kaikei)
(#:am #:almighty-money))
(:export
#:cash-reserve-room
#:card-reserve-room
#:cash-sale-of-goods
#:card-sale-of-goods))
Our application is going to need ways for both viewing our transaction records as well as commands for making those transactions. To begin, we'll make the views.
Views
Open a buffer to almighty-accounting/src/views.lisp. Input the following:
(defpackage #:almighty-accounting/views
(:use #:cl)
(:local-nicknames (#:db #:almighty-accounting/db)
(#:ak #:almighty-kaikei)
(#:am #:almighty-money)
(#:lt #:local-time)))
(in-package #:almighty-accounting/views)
In here, we're going to create three simple reports. The first one is a general ledger. The most basic kind of accounting report is the general ledger. It simply contains all of the transactions made, detailing the two sides and accounts affected.
;;; CLI VIEWS
(defun format-general-ledger (from &optional (to (lt:now)))
(loop
:with transaction-count := (db:transaction-count-for-period from to)
:with legs := (db:legs-for-period from to)
:with leg-count := (length legs)
:initially
(format t "~&GENERAL LEDGER~%")
(format t "~&Showing ~a transactions.~%" transaction-count)
(format t "~a~30t~a~60t~a~%" "Transaction #" "Debits" "Credits")
:for leg :in legs
:if (ak:debit-p leg)
:do (format t "~a~30t~30a~%"
(ak:leg-transaction-id leg)
(list (am:format-money nil (ak:leg-entry leg))
(ak:account-name (ak:leg-account leg))))
:else
:do (format t "~a~60t~30a~%" (ak:leg-transaction-id leg) (list (am:format-money nil (ak:leg-entry leg)) (ak:account-name (ak:leg-account leg))))
:end
:finally (terpri)))
I hope you read the chapter on the loop macro.
:with binds a variable once at the beginning of the iteration. It essentially
allows you to avoid wrapping the loop in a let form.
:initially does something (in this case, two formats) once at the beginning of
the iteration.
:for does what you imagine it will do.
We decide what to :do with the :if conditional directive.
:finally, at the end of the iteration, we do something. In this case, terpri
will output a newline.
The format string is fairly simple: the TILDE T is used for tabulation–adding
space (when necessary) to format text in a table.
Imagine that there is a cursor that moves through the string, printing out as it
goes along. The numbers 30 and 60 are the
colnum to place the cursor before continuing the format.
The format-trial-balance function will look similar:
(defun format-trial-balance ()
(loop
:with result := (db:trial-balance)
:initially
(format t "~&TRIAL BALANCE~%")
(format t "~a~30t~a~60t~a~%" "Account" "Debit" "Credit")
:for row :in result
:for account := (getf row :|account|)
:for currency := (getf row :|currency|)
:for debit-amount := (abs (getf row :|debit_amount|))
:for credit-amount := (abs (getf row :|credit_amount|))
:for total-amount := (abs (getf row :|total_amount|))
:if (> debit-amount credit-amount)
:do (format t "~a~30t~a~60t~a~%"
account
(am:format-money nil (am:make-money total-amount currency))
"")
:else
:do (format t "~a~30t~a~60t~a~%"
account
""
(am:format-money nil (am:make-money total-amount currency)))
:end
:finally (terpri)))
:for + := will rerun the form following := and bind the result to the
variable with every iteration. The trial-balance query will return a plist. In
the select SQL statement in trial-balance, there are number of :as expressions
where some return :sums as :debit_amount, :credit_amount, etc. These lower-case
keys will be returned in the plist as is. By default Common Lisp
up cases all symbols. To keep them lower-case, you must wrap them in | (pipes).
:debit_amount
:DEBIT_AMOUNT
:|debit_amount|
:|debit_amount|
I don't know why, but cl-dbi (the library that actually executes the query)
keeps the keywords lowercase, resulting in the less ergonomic keywords needed to
successfully getf the values for each key. My guess is that cl-dbi is trying to
maintain compatibility between Lisp and naming conventions in SQL or other
languages (camelCase, perhaps).
The *-amount values are returned as absolute values because our system
internally represents every increasing value as positive, and every decreasing
value as negative, but in accounting, there are no negative values–just debits
and credits which mean something different depending on the type of account
those transactions run on. Since the purpose here is to represent the debit
value or credit value, rather than our own internal representation of those
values, we return the absolute values.
Finally, we will have a view to see sales.
(defun format-sales (from &optional (to (lt:now)))
(loop
:with transaction-count := (db:sales-count-for-period from to)
:with legs := (db:sales-for-period from to)
:with leg-count := (length legs)
:initially
(format t "~&SALES~%")
(format t "~&Showing ~a transactions.~%" transaction-count)
(format t "~a~15t~a~45t~a~%" "Transaction #" "Debits" "Credits")
:for leg :in legs
:if (ak:debit-p leg)
:do (format t "~a~15t~a~%"
(ak:leg-transaction-id leg)
(list (am:format-money nil (ak:leg-entry leg))
(ak:account-name (ak:leg-account leg))))
:else
:do (format t "~a~45t~a~%"
(ak:leg-transaction-id leg)
(list (am:format-money nil (ak:leg-entry leg))
(ak:account-name (ak:leg-account leg))))
:end
:finally (terpri)))
By now it should be clear what's going on.
We now have our views. The last thing we need to provide some interface to use our application. In our case, we will make this as a simple text-based application, so we will provide CLI commands.
Test Driving
Now that we have the tools to add data, make queries, and view the data in a coherent way, you and I both would probably feel good if we could give our code a spin. Let's write some tests.
Open a buffer at almighty-accounting/almighty-accounting-test.asd and add this:
(defsystem "almighty-accounting-test"
:author "Micah Killian"
:version "0.0.1"
:description "Tests for almighty-accounting."
:depends-on (#:almighty-accounting #:lisp-unit2)
:components ((:module "t/"
:components ((:file "tests")))))
For testing, we're going to use lisp-unit2. There are many many testing
frameworks for Common Lisp. Another popular one is Fiveam, but I like lisp-unit2.
Now that you have a system, go ahead and run vend get again to get lisp-unit2
and its dependencies downloaded into the vendored directory. After that, we need
to load the system file and then the system itself.
First, in the REPL, type ,set-directory and make sure the directory is set to
the almighty-accounting project root. After that, run this code:
(asdf:load-asd (merge-pathnames "almighty-accounting-test.asd" *default-pathname-defaults*))
That will load the asd file. After that, in the REPL, type ,load-system and then
choose almighty-accounting-test to load it.
Open a buffer at t/tests.lisp and add this:
(defpackage #:almighty-accounting/tests
(:use #:cl)
(:local-nicknames (#:lu #:lisp-unit2)
(#:lt #:local-time)
(#:ak #:almighty-kaikei)
(#:am #:almighty-money)
(#:db #:almighty-accounting/db)
(#:transactions #:almighty-accounting/transactions)
(#:views #:almighty-accounting/views)))
(in-package #:almighty-accounting/tests)
Let's make a simple test to learn how to use lisp-unit2.
(lu:define-test testing-testing-123 ()
(lu:assert-eql 123 123))
This test will pass if (eql 123 123) or fail otherwise. To run the test, we can
use lu:run-tests.
(lu:run-tests)
It should return something like this:
#<LU:TEST-RESULTS-DB Tests:(1) Passed:(1) Failed:(0) Errors:(0) Warnings:(0) {700C0D01D3}>
To delete a test, use undefine-test.
(lu:undefine-test testing-testing-123 ()
(lu:assert-eql 123 123))
You can group sets of tests using "tags". You can specify one or more tags.
(lu:define-test test-my-test (:tags '(math)))
(lu:define-test test-my-test2 (:tags '(accounting reporting)))
To run tests with a certain tag or tags:
(lu:run-tests :tags '(accounting))
In addition to tags, you can specify contexts. Contexts are lexical environments
for tests. You can specify them on a per-test basis or for all tests in the call
to lu:run-tests.
(lu:define-test test-my-context-text (:contexts (list #'with-my-context
#'with-another-context)))
(lu:run-tests :run-contexts (list #'lu:with-failure-debugging-context))
lu:with-failure-debugging-context is a built-in context that will open the
debugger if an assertion fails, giving you an opportunity to look at the data.
Contexts are functions that return a funcall to a body function.
(defun with-my-context (body-fn)
(let ((...))
(do-thing)
(do-another-thing)
(funcall body-fn)))
Every test that uses with-my-custom-context will include this lexical
environment.
We'll need to use the with-chart-of-accounts macro for our context, but we need
to use it in a function.
(defun with-chart-of-accounts-context (body-fn)
(let ((mito:*mito-logger-stream* nil)
(mito:*mito-migration-logger-stream* nil))
(db:with-chart-of-accounts #p":memory:"
(funcall body-fn))))
We will want to look at the information lisp-unit2 prints out when tests
complete, so we set *mito-logger-stream* and *mito-migration-logger-stream* to
nil to suppress the output of the internal mito:ensure-table-exists calls in with-chart-of-accounts.
Because we're going to be using with-chart-of-accounts-context for every single
test, we'll make a couple of convenience macros to keep things short.
(defmacro define-accounting-test (name tags &body body)
`(lu:define-test ,name (:tags ,tags
:contexts (list #'with-chart-of-accounts-context))
,@body))
(defmacro undefine-accounting-test (name tags &body body)
`(lu:undefine-test ,name (:tags ,tags
:contexts (list #'with-chart-of-accounts-context))
,@body))
The undefine is for removing a test from lisp-unit2's internal list of tests to run.
Now we can write some tests. We'll keep it brief: do our transactions in
almighty-accounting/transactions signal the almighty-kaikei:unbalanced-transaction condition?
(define-accounting-test test-cash-reserve-room '(cash logic)
(lu:assert-no-error
'ak:unbalanced-transaction
(transactions:cash-reserve-room `("nice room" ,(am:usd 2000)))))
(define-accounting-test test-card-reserve-room '(card logic)
(lu:assert-no-error
'ak:unbalanced-transaction
(transactions:card-reserve-room `("nice room" ,(am:usd 2000)))))
(define-accounting-test test-cash-sale-of-goods '(cash logic)
(lu:assert-no-error
'ak:unbalanced-transaction
(transactions:cash-sale-of-goods *cart*)))
(define-accounting-test test-card-sale-of-goods '(card logic)
(lu:assert-no-error
'ak:unbalanced-transaction
(transactions:card-sale-of-goods *cart*)))
*cart* is just a simple list of goods to send to the *-sale-of-goods
transactions.
;;; item-name quantity sale-price procurement-price
(defparameter *cart* `(("item1" 1 ,(am:usd 7999) ,(am:usd 5999))
("item2" 3 ,(am:usd 500) ,(am:usd 400))))
Let's run the tests.
(lu:run-tests :run-contexts (list #'lu:with-summary-context))
#<LU:TEST-RESULTS-DB Tests:(4) Passed:(4) Failed:(0) Errors:(0) Warnings:(0) {700B80B213}>
Now we want to test our views. We could use the lisp-unit2:assert-print test,
but I think it's easier to just do the prints and check them against the
transactions for our purposes.
(define-accounting-test test-cash-reserve-room '(cash logic)
(lu:assert-no-error
'ak:unbalanced-transaction
(transactions:cash-reserve-room `("nice room" ,(am:usd 2000))))
(views:format-trial-balance)
(views:format-general-ledger (lt:today)
(lt:adjust-timestamp (lt:today) (:offset :day 1)))
(views:format-sales (lt:today)
(lt:adjust-timestamp (lt:today) (:offset :day 1))))
(define-accounting-test test-card-reserve-room '(card logic)
(lu:assert-no-error
'ak:unbalanced-transaction
(transactions:card-reserve-room `("nice room" ,(am:usd 2000))))
(views:format-trial-balance)
(views:format-general-ledger (lt:today)
(lt:adjust-timestamp (lt:today) (:offset :day 1)))
(views:format-sales (lt:today)
(lt:adjust-timestamp (lt:today) (:offset :day 1))))
(define-accounting-test test-cash-sale-of-goods '(cash logic)
(lu:assert-no-error
'ak:unbalanced-transaction
(transactions:cash-sale-of-goods *cart*))
(views:format-trial-balance)
(views:format-general-ledger (lt:today)
(lt:adjust-timestamp (lt:today) (:offset :day 1)))
(views:format-sales (lt:today)
(lt:adjust-timestamp (lt:today) (:offset :day 1))))
(define-accounting-test test-card-sale-of-goods '(card logic)
(lu:assert-no-error
'ak:unbalanced-transaction
(transactions:card-sale-of-goods *cart*))
(views:format-trial-balance)
(views:format-general-ledger (lt:today)
(lt:adjust-timestamp (lt:today) (:offset :day 1)))
(views:format-sales (lt:today)
(lt:adjust-timestamp (lt:today) (:offset :day 1))))
local-time doesn't have a tomorrow function, but we can just use
local-time:adjust-timestamp and offset today by a day in the future. If we were
doing more tests that needed both a "from" and "to", we could make a convenience
macro, but it's fine to just copy/paste here.
Now run the tests again:
(lu:run-tests :run-contexts (list #'lu:with-summary-context))
------- STARTING Testing: ALMIGHTY-ACCOUNTING/TESTS Starting: ALMIGHTY-ACCOUNTING/TESTS::TEST-CASH-RESERVE-ROOM TRIAL BALANCE Account Debit Credit Cash $20.00 Room Revenue $18.00 Tax Payable $2.00 GENERAL LEDGER Showing 1 transactions. Transaction # Debits Credits 1 ($20.00 Cash) 1 ($2.00 Tax Payable) 1 ($18.00 Room Revenue) SALES Showing 1 transactions. Transaction # Debits Credits 1 ($18.00 Room Revenue) ALMIGHTY-ACCOUNTING/TESTS::TEST-CASH-RESERVE-ROOM - PASSED (0.02s) : 1 assertions passed ... and so on
Finally, we can see both the effects of our transactions and our nicely tabulated views.
But it's one thing to run these transactions in a test; it's another to actually run them through a command-line interface. Let's build our UI.
Commands
Open a buffer to almighty-accounting/src/commands.lisp and add this at the top:
(defpackage #:almighty-accounting/commands
(:use #:cl)
(:local-nicknames (#:db #:almighty-accounting/db)
(#:transactions #:almighty-accounting/transactions)
(#:views #:almighty-accounting/views)
(#:ak #:almighty-kaikei)
(#:am #:almighty-money)
(#:cli #:clingon)
(#:lt #:local-time)))
(in-package #:almighty-accounting/commands)
The clingon library provides a formal way for creating CLI commands with options
and even a free --help display that's updated when we update our command
definitions.
In clingon, there are three parts that can be connected and coordinated: a
command, its options, and its handler.
To begin with, let's make the top-level command.
(defun top-level/command ()
"Creates and returns the top-level command"
(cli:make-command
;; The name of this command won't actually matter. The executable will be
;; named `almighty-accounting'and it will call this command directly through the `main'
;; function.
:name "almighty-accounting"
:description "Almighty-Accounting CLI Application"
:version "0.1.0"
:license "BSD 2-Clause"
:authors '("Micah Killian <[email protected]>")
:handler #'top-level/handler))
A command is a thing you can type into the terminal to do something. Using
clingon:make-command, our command comes with a free --help menu
automatically generated.
The clingon:make-command function takes several keyword arguments. :name is
required, and is how you actually use the command in the terminal. For example,
in the terminal, you would type almighty-accounting to use this command…
…if we weren't already making an executable with asdf named almighty-accounting.
Look back at the system definition in the Setup section of this chapter. The
build-pathname is going to be the name of the executable that asdf produces.
That executable calls a main function that we will write later. The main
function will run this top-level/command as we'll see in a little bit.
The :handler is the function name for the command handler. A handler is the
part that actually does something. If a command is a route, then a handler is
a controller in the MVC model. We will be making handles that call two kinds
of functions: our transactions (like cash-reserve-room) and our views (like
format-general-ledger). The transaction handlers are going to require a bit of
work to build the interface, but the views will be pretty simple.
One thing to keep in mind: the .../command, .../handler, etc. naming is just
a convention for helping understand the code. You can name the functions
anything you want.
The top-level/handler has one job: print the usage menu for the user.
(defun top-level/handler (cmd)
(cli:print-usage-and-exit cmd t))
To test the command and handler, we do this:
(let ((app (top-level/command)))
(cli:run app))
NAME:
almighty-accounting - Almighty-Accounting CLI Application
USAGE:
almighty-accounting [global-options] [<command>] [command-options] [arguments ...]
OPTIONS:
--help display usage information and exit
--version display version and exit
AUTHORS:
Micah Killian <[email protected]>
LICENSE:
BSD 2-Clause
=> NIL
Neat.
What we need now are some real commands that do something. Because those
commands are going to have their entry point behind top-level/command, they
are called sub-commands. Let's make our sub-commands function.
(defun top-level/sub-commands ()
(list
;; Reports
;; (general-ledger/command)
;; (trial-balance/command)
;; (sales/command)
;; Actions
;; (cash-reserve-room/command)
;; (card-reserve-room/command)
;; (cash-sale-of-goods/command)
;; (card-sale-of-goods/command)
))
These are the commands we are going to define. Since we don't have them defined yet, we have them commented out.
Then we add top-level/sub-commands to the top-level/command definition like
so:
(defun top-level/command ()
"Creates and returns the top-level command"
(cli:make-command
;; The name of this command won't actually matter. The executable will be
;; named `almighty-accounting'and it will call this command directly through the `main'
;; function.
:name "almighty-accounting"
:description "Almighty-Accounting CLI Application"
:version "0.1.0"
:license "BSD 2-Clause"
:authors '("Micah Killian <[email protected]>")
:handler #'top-level/handler
:sub-commands (top-level/sub-commands))) ; <-- Add this
Let's make our first sub-command: cash-reserve-room/command
(defun cash-reserve-room/command ()
(cli:make-command
:name "cash-reserve-room"
:description "Record a room reservation paid in cash."
:handler #'cash-reserve-room/handler))
And the handler:
(defun cash-reserve-room/handler (cmd)
(declare (ignore cmd))
(%reserve-room/handler #'transactions:cash-reserve-room))
The %reserve-room/handler helper:
(defun %reserve-room/handler (transaction-fun)
"Handles cash or card room reservation transactions. Prompts for
number of nights, restarts if the value isn't a positive integer.
Passes the `room' and `quantity' to the right transaction function.
Returns the total formatted `am:money' value of the transaction."
(let ((price (am:usd 10000)) ; hardcoding for simplicity
(number-of-nights (ask-number-of-nights)))
(funcall transaction-fun `(,price ,number-of-nights))
(format t "Transaction completed. Total: ~a~%"
(am:format-money nil (am:money* price number-of-nights)))))
We hardcode the price of the room to simplify our code, but in a real application this would almost certainly be substantially more involved.
This helper is designed to work for both cash-reserve-room and
card-reserve-room transactions, so it takes the transaction function to run as
an argument and runs funcall on that function. ask-number-of-nights is
simple.
(defun ask-number-of-nights ()
(format t "~&Number of nights: ")
(let* ((user-input (read-line))
(number-of-nights (parse-integer user-input :junk-allowed t)))
(cond ((or (null number-of-nights)
(<= number-of-nights 0))
(format t "~&Number of nights must be a positive integer. You passed: ~a~%" user-input)
(ask-number-of-nights))
(t number-of-nights))))
We ask for user input with read-line. read-line returns a string, but we
request a number from the user, so we use parse-integer to take a string like
"67" and turn it into 67. :junk-allowed t means that if parse-integer
can't parse an integer from the user-input, it will return nil instead of
signalling an error. If our command signals an error, it will stop us
mid-command. Instead, we just want to give the user an error message with
format and restart the process from the beginning.
If we do parse an integer from the user-input without any drama, then we just return the number.
The cond could easily be rewritten as an if:
(if (or (null number-of-nights)
(<= number-of-nights 0))
(progn
(format t "~&Number of nights must be a positive integer. You passed: ~a~%" user-input)
(ask-number-of-nights))
number-of-nights)
But generally you should prefer cond over using a progn inside if.
The card-reserve-room/command and card-reserve-room/handler look nearly
identical to the cash version.
(defun card-reserve-room/command ()
(cli:make-command
:name "card-reserve-room"
:description "Record a room reservation paid by card."
:handler #'card-reserve-room/handler))
(defun card-reserve-room/handler (cmd)
(declare (ignore cmd))
(%reserve-room/handler #'transactions:card-reserve-room))
We can test both of these out in the REPL.
(let ((app (top-level/command)))
(db:with-chart-of-accounts #p"data/data.db"
(cli:run app '("cash-reserve-room"))))
Number of nights: dude Number of nights must be a positive integer. You passed: dude Number of nights: 6 Transaction completed. Total: $600.00 NIL
You'll only know the difference between them if you look at the SQL data. You
can use M-x sqlite-mode-open-file in Doom to take a quick look at the raw
data, or you can wait a little longer and run some reports on your database.
Now that we have room reservations taken care of, we need to work on cash and card sale of goods. Let's start with our command and handler.
(defun cash-sale-of-goods/command ()
"Creates and returns the cash sale of goods command."
(cli:make-command
:name "cash-sale"
:description "Record a sale of goods paid in cash."
:handler #'cash-sale-of-goods/handler))
(defun cash-sale-of-goods/handler (cmd)
"Records a cash sale of goods."
(declare (ignore cmd))
(%sale-of-goods/handler #'transactions:cash-sale-of-goods nil))
The real meaty part of this operation is in the %sale-of-goods/handler helper
function.
(defun %sale-of-goods/handler (transaction-fun goods)
"Handles cash or card sale of goods transactions. Prompts users for a valid
`inventory-good-name', restarting if they input the wrong name. Prompts for
`quantity', restarts if the value isn't a positive integer. After a `good' and
`quantity' are selected, prompts to repeat the process for a different `good'.
Appends all `cart-goods' into a list of lists, and then passes them to the
correct transaction function to complete the transaction.
Returns the total formatted `am:money' value of the transaction."
(let ((good (ask-good-name)))
(cond ((and (equalp "finish" good)
(not (null goods)))
(funcall transaction-fun goods)
(format t "Transaction completed. Total: ~a~%"
(am:format-money nil (reduce #'am:money+
(mapcar #'transactions::total-sale-price-of-good
goods)))))
((and (equalp "finish" good)
(null goods))
(return-from %sale-of-goods/handler
(format t "~&Command Aborted.")))
(t (let ((quantity (ask-good-quantity)))
(let ((cart-goods (append goods `((,(inventory-good-name good)
,quantity
,(inventory-good-cog good)
,(inventory-good-price good))))))
(cond ((y-or-n-p "More?")
(%sale-of-goods/handler transaction-fun cart-goods))
(t (funcall transaction-fun cart-goods)
(format t "Transaction completed. Total: ~a~%"
(am:format-money nil (reduce #'am:money+
(mapcar #'transactions::total-sale-price-of-good
cart-goods))))))))))))
The basic idea of this function is to ask what good the user wants, and how
much they want, and ask if they want to repeat the process for a different good.
ask-good-name and ask-good-quantity have some simple error recovery just
like ask-number-of-nights:
(defun ask-good-name ()
(format t "~&Name of good: ")
(let* ((user-input (read-line))
(good (assoc user-input *inventory-goods-for-sale* :test #'equalp)))
(cond ((equalp "finish" user-input)
user-input)
((null good)
(format t "Unknown good: ~a. Must be one of: ~:{~&~a~%~}"
user-input
*inventory-goods-for-sale*)
(ask-good-name))
(t good))))
(defun ask-good-quantity ()
(format t "~&Quantity: ")
(let* ((user-input (read-line))
(quantity (parse-integer user-input :junk-allowed t)))
(cond ((or (null quantity)
(<= quantity 0))
(format t "Invalid quantity: ~a. Quantity must be a positive integer." user-input)
(ask-good-quantity))
(t quantity))))
ask-good-name gives the user two choices:
- finish, an escape hatch for if they accidentally chose to continue adding more goods to the transaction.
- recover from bad input. The good needs to actually exist in the
inventory, so if they input something that doesn't exist, we ask them to try
again (recursively calling
ask-good-nameto start over) andformata list of valid goods in the inventory.TILDE BRACESwill iterate over the items of a list. It will take each item in the list in order and consume them with~a. TheCOLONmodifier makes it iterate over nested lists, as in this situation where we use analist.
Once we get the good, we use ask-good-quantity to get the quantity, with a
similar error recovery mechanism.
%sale-of-goods/handler asks the user a y or n question, and if the
response is y, then we recursively call %sale-of-goods/handler with the
cart-goods appended with the good we asked about.
As I said, the good that ask-good-name asks for needs to be in an inventory,
specifically *inventory-goods-for-sale*, and then creates a cart-good (which
includes a quantity) to append to our goods.
;;; Pretend we have a DB with all this information.
(defparameter *inventory-goods-for-sale* `(("beef" ,(am:usd 5000) ,(am:usd 7000))
("chicken" ,(am:usd 1000) ,(am:usd 2000))
("rice" ,(am:usd 2000) ,(am:usd 2500))
("cheese" ,(am:usd 500) ,(am:usd 700))
("bread" ,(am:usd 700) ,(am:usd 1000))))
(defun inventory-good-name (good)
(first good))
(defun inventory-good-price (good)
(second good))
(defun inventory-good-cog (good)
(third good))
Our "database" holds both the cost of the good as well as the sale price of the good to help us keep track of our revenue.
Adding a card-sale-of-goods/command and handler is easy:
(defun card-sale-of-goods/command ()
"Creates and returns the card sale of goods command."
(cli:make-command
:name "card-sale"
:description "Record a sale of goods paid by card."
:handler #'card-sale-of-goods/handler))
(defun card-sale-of-goods/handler (cmd)
"Records a card sale of goods."
(declare (ignore cmd))
(%sale-of-goods/handler #'transactions:card-sale-of-goods nil))
We use the same helper function, but tell it to run
transactions:card-sale-of-goods instead of cash-sale-of-goods.
You can test the commands out like so:
(let ((app (top-level/command)))
(db:with-chart-of-accounts #p"data/data.db"
(cli:run app '("cash-sale")))) ; Or `card-sale'.
Name of good: dude Unknown good: dude. Must be one of: beef chicken rice cheese bread Name of good: beef Quantity: dude Invalid quantity: dude. Quantity must be a positive integer. Quantity: 9 More? (y or n) y Name of good: finish Transaction completed. Total: $450.00 NIL
With that, we now how all of our actions set up, but now we want some reports. The good news is that this code will be relatively short.
(defun from-to/options ()
"Creates and returns the general-ledger command options."
(list
(cli:make-option
:string
:short-name #\f
:long-name "from"
:key :from
:description "Beginning of period of sales.")
(cli:make-option
:string
:short-name #\t
:long-name "to"
:key :to
:description "End of period of sales.")))
(defun general-ledger/command ()
"Creates and returns the general-ledger command."
(cli:make-command
:name "general-ledger"
:description "Return the general ledger of our accounting system example."
:options (general-ledger/options)
:handler #'general-ledger/handler))
(defun general-ledger/options ()
"Creates and returns the general-ledger command options."
(from-to/options))
(defun general-ledger/handler (cmd)
"Returns the general ledger of the almighty-kaikei accounting system example."
(let ((from (cli:getopt cmd :from))
(to (cli:getopt cmd :to (lt:now))))
(views:format-general-ledger from to)))
(defun trial-balance/command ()
"Creates and returns the trial balance command."
(cli:make-command
:name "trial-balance"
:description "Return the trial balance of our accounting system example."
:handler #'trial-balance/handler))
(defun trial-balance/handler (cmd)
"Returns the trial balance of the almighty-kaikei account system example."
(declare (ignore cmd))
(views:format-trial-balance))
(defun sales/command ()
"Creates and returns the sales report command."
(cli:make-command
:name "sales"
:description "Return the sales of our accounting system example."
:options (sales/options)
:handler #'sales/handler))
(defun sales/options ()
"Creates and returns the sales command options."
(from-to/options))
(defun sales/handler (cmd)
"Returns the sales report for a given period of the almighty-kaikei accounting
system example. Requires -f option."
(let ((from (cli:getopt cmd :from))
(to (cli:getopt cmd :to (lt:now))))
(views:format-sales from to)))
The main different between these commands and the previous ones is the use of
the :options argument. With the previous commands, we interactively asked
users for their input. In this case, we just want to receive the from/to
information from the user when they type out the command, as in
almighty-accounting general-ledger --from "2026-7-2".
We'll take a look at one of the options:
(cli:make-option
:string
:short-name #\f
:long-name "from"
:key :from
:description "Beginning of period of sales.")
The data type of the option is :string, meaning that cli:getopt will return
a string like "2026-7-2".
:short-name and :long-name specify how to use the option when calling the
command. This option allows us to say either almighty-accounting general-ledger
--from 2026-7-2 or almighty-accounting general-ledger -f 2026-7-2.
:key is the keyword used to access the value in our handler using
cli:getopt. If we set the :key to :foo then we can access the value in the
handler using (cli:getopt cmd :foo).
Finally, the :description is what will be displayed in the help or use menu.
Once you compile all of the functions in this section, run this:
(let ((app (top-level/command)))
(db:with-chart-of-accounts #p"data/data.db"
(cli:run app nil)))
NAME:
almighty-accounting - Almighty-Accounting CLI Application
USAGE:
almighty-accounting [global-options] [<command>] [command-options] [arguments ...]
OPTIONS:
--help display usage information and exit
--version display version and exit
COMMANDS:
general-ledger Return the general ledger of our accounting system example.
trial-balance Return the trial balance of our accounting system example.
sales Return the sales of our accounting system example.
cash-sale Record a sale of goods paid in cash.
cash-reserve-room Record a room reservation paid in cash.
card-sale Record a sale of goods paid by card.
card-reserve-room Record a room reservation paid by card.
AUTHORS:
Micah Killian <[email protected]>
LICENSE:
BSD 2-Clause
=> NIL
Now, as for the view handlers, you pass them as additional string values in the
call to cli:run.
(let ((app (top-level/command)))
(db:with-chart-of-accounts #p"data/data.db"
(cli:run app '("sales" "-f" "2026-6-30"))))
SALES Showing 32 transactions. Transaction # Debits Credits 1 ($69.99 Sales Revenue) 2 ($69.99 Sales Revenue) ... 32 ($405.00 Sales Revenue) => NIL
Now we have all of the commands we need to provide our interface. The final step is deploying–building an executable. We'll cover that in the next chapter.