Reporting on Point of Sale Transactions

by Andrew Fuerste-Henry on Feb 26, 2021

Koha version 20.05 adds a new point of sale feature to track the sale of items without linking them to a specific patron. Kelly wrote a blog post about its configuration and use, which you can see here. In this post, I'd like to talk about how one's point of sale transactions are recorded in the database and can be accessed via reports.

An old friend

Point of sale data gets recorded in the accountlines table, along with all the data about fines, fees, and the payments associated with them. That's great, because it means we don't need to learn a new data structure! We just need to figure out what those point of sale transactions look like and how to tell them apart from all the fine and fee transactions.

To get started, I set up some basic point of sale options on my test site. We're selling kittens and espresso shots for $1 each. My library is a lot of fun. Again, see Kelly's blog post linked above for details of this setup.

I've made one sale so far (one kitten). Since I'm on a brand new test site, I can find my sale with a super easy report:

SELECT * FROM accountlines

That's just going to give me all the accountlines data in my whole system.

We can see our one transaction created two entries in accountlines, mirroring the structure we'd expect for a charge on a patrons account. The first accountline records the amount owed for the sale (we'll call this the debit), the second accountline records how it was paid for (we'll call this the credit).

We can also see that none of these entries contain a borrowernumber, since point of sale doesn't record who you sold to. That can help us differentiate point of sale transactions from others. If it has a borrowernumber, it's not a point of sale transaction. However, we cannot assume that every accountline without a borrowernumber is a point of sale transaction. We'll also see empty borrowernumbers on old fines and fees where the patron has been deleted.

Next I performed a second sale, this time for two kittens and one espresso. To sell two kittens at once, I clicked the big Add button under Items for Purchase on the left and then edited the quantity under This Sale on the right.

This one made three accountlines entries: one for the espresso, one for the kittens, and one for the payment. The note column shows the quantity (1 for espresso, 2 for kittens).

Alternatively, I could have gotten two kittens by just clicking the Add button twice, rather than editing the quantity. That would look like this in the sale screen:

And like this in the accountlines data:

So, for each sale recorded through point of sale, the accountlines table will record one debit line for each entry in the This Sale box and one line credit line for the whole sale. They all have the same date and timestamp values, which gives an easy way to see that they go together. They'll also be connected through the account_offsets table, which we'll talk about in a bit.

Finding the credits

I want to talk first about how to write a report to find the credits associated with point of sale transactions, because that's the simplest part of this. In the screenshots above, you'll see that all the credits have a credit_type_code of "PURCHASE." Point of sale is the only thing in Koha that uses this credit_type_code, so you can use it to limit your report. For example, you could find all point of sale credits in a date range with:

SELECT date, amount, payment_type, register_id
FROM accountlines
WHERE credit_type_code='purchase' AND date(date) BETWEEN <<Start date|date>> and <<End date|date>>

Or you could get the sales totals per register per day in a date range with:

SELECT date(date), register_id, sum(amount)
FROM accountlines
WHERE credit_type_code='purchase'
GROUP BY date(date), register_id 

Finding the debits

The debits associated with a point of sale transaction are a bit more complicated, as they're not guaranteed to have codes that are unique to point of sale, like the credit does. When we set up our debit types for sale, we also had the option to mark them as available for manual invoice applied to a patron's account.

If your debit type is only used for point of sale, then these reports are a lot like what we did above for credits. For example, in my test system we only use the espresso debit type for point of sale. That means I can get a list of all my espresso sales in a date range with (remember the note field shows the quantity sold):

SELECT date, note, amount
FROM accountlines
WHERE debit_type_code='esp' AND date(date) BETWEEN <<Start date|date>> and <<End date|date>>

Or I can get a count of espressos sold per day in a date range with:

SELECT date(date), sum(note), sum(amount)
FROM accountlines
WHERE debit_type_code='esp'
GROUP BY date(date)

However, my kitten debit type is also used for manual invoices, so if I used the above reports for kittens I'd get all those manual invoices mixed in there, messing up my totals. Because only the credits are definitively marked as relating to point of sale, I'll need to make my report link up each debit with its associated credit.

Linking debits and credits

There are lots of situations in which one might want a definitive link between a payment and what it was for, including my example of mixed sales and invoices. To link these up, we need to pull in the account_offsets table. This can get a little tricky.

In my last sale example screenshot above, we can see a $3 payment with accountlines_id 15. The schema page for account_offsets tells us that account_offsets.credit_id is "the id of the accountline that increased the patron's balance." So if we say

SELECT *
FROM account_offsets
WHERE credit_id=15

we get:

Our $3 credit was split across three different debits, applying $1 to each. The debit_id column lists the accontlines_id values for each of the debits to which this credit was applied. Note that account_offsets is also valuable as the only spot that shows us exactly how that credit was divided up between the debits.

With all this in mind, here's the FROM statement we need to make our report link credits and debits:

FROM account_offsets ao
LEFT JOIN accountlines d ON (ao.debit_id=d.accountlines_id AND d.debit_type_code is not null)
LEFT JOIN accountlines c ON (ao.credit_id=c.accountlines_id AND c.credit_type_code is not null)

This is a bit tricky, as it requires us to join in the accountlines table twice, once for the debits and once for the credits. In order to do that, I needed to give the two instances of accountlines new names, so I called one "d" and the other "c." While I was at it, I renamed account_offsets to "ao" just to save myself some typing.

The next sticky bit is that I need to make sure d is only debits and c is only credits. That's why the ON parts of each join include a limit to not-null debit and credit codes. Remember a given accountline will only have a debit_type_code OR a credit_type_code, never both.

With those joins figured out, we can run a query for to find our kitten sales without including any manual invoices for kittens. More specifically, I can look for only those kitten debits that link up to a point of sale credit:

SELECT d.accountlines_id, d.date, d.amount
FROM account_offsets ao
LEFT JOIN accountlines d ON (ao.debit_id=d.accountlines_id AND d.debit_type_code is not null)
LEFT JOIN accountlines c ON (ao.credit_id=c.accountlines_id AND c.credit_type_code is not null)
WHERE d.debit_type_code='kit' AND c.credit_type_code='purchase'

Linking debits to credits also lets us do more complicated things. For example, we could get totals per sale item and per payment type, if we wanted to see how patrons tend to pay for our different items:

SELECT d.debit_type_code, c.payment_type, sum(ao.amount)
FROM account_offsets ao
LEFT JOIN accountlines d ON (ao.debit_id=d.accountlines_id AND d.debit_type_code is not null)
LEFT JOIN accountlines c ON (ao.credit_id=c.accountlines_id AND c.credit_type_code is not null)
WHERE c.credit_type_code='purchase'
GROUP BY d.debit_type_code, c.payment_type

Ask for help

Anything involving money gets pretty high-stakes, and these reports can get tricky. Don't forget we're here to help. Whether you've written your own report that isn't quite perfect or you've only gotten as far an an idea of what you'd like, just drop us a line!