Point of Sale Overview
Visit the Point of Sale section in the Koha manual for details on setting up and using this feature.
In addition to the information there, note that your library will need to decide how it wants the system preference FinePaymentAutoPopup set. If it is set to "[Don't] automatically display a print dialog for a payment receipt when making a payment", you will see these options once you have completed a sale:
If 'Print receipt' is selected, the browser will open the receipt for printing. If 'Email receipt' is selected, you will be prompted to enter an email address to which the receipt will be sent. The 'Payment received' box will remain until you either leave the page or complete another transaction, so you can choose one and then the other if the patron would like both. If you enter an email address that matches a patron in your system, the receipt will not show up in their Notices tab since the transaction is not actually linked to their account.
If the system preference is set to "[Do] automatically display a print dialog for a payment receipt when making a payment", there will be no email option - instead, there will be a popup to print the receipt. If it is accidentally closed without printing, it can't be prompted to print again as with the first option.
When deciding how to set FinePaymentAutoPopup, keep in mind that the system preference also applies to payments made in a patron's account. If it is set to [Do], receipts for account payments will automatically pop up for printing. If it's set to [Don't], staff will need to click the 'Print' button to print a receipt.
Reports
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, set up some basic point of sale options. This library is selling kittens and espresso shots for $1 each.
One sale has been made so far (one kitten). On a brand new site, the sale can be found with a super-easy report:
SELECT * FROM accountlines
That's just going to give all the accountlines data in the library system.
This one transaction created two entries in accountlines, mirroring the structure for a charge on a patrons account. The first accountline records the amount owed for the sale (also known as a debit), the second accountline records how it was paid for (also known as a credit).
None of these entries contain a borrowernumber, since point of sale doesn't record the buyer. That can help differentiate point of sale transactions from others. If it has a borrowernumber, it's not a point of sale transaction. However, do not assume that every accountline without a borrowernumber is a point of sale transaction. It is possible to see empty borrowernumbers on old fines and fees where the patron has been deleted.
A second sale is transacted, this time for two kittens and one espresso. To sell two kittens at once, click the big Add button under Items for Purchase on the left and then edited the quantity under This Sale on the right.
This added 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, two kittens could be added 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, discussed below.
Finding the Credits
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 similar to the reports for credits. In this example, the espresso debit type for point of sale is used. The report will return a list of all 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 the report can be modify to return 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, the kitten debit type is also used for manual invoices, so the reports for kittens would also return manual invoices mixed in and confuse the totals. Because only the credits are definitively marked as relating to point of sale, the report will need to link 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 the 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 the 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, give the two instances of accountlines new names, so name one "d" and the other "c." At the same time rename account_offsets to "ao" to save some typing.
The next step 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, 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
Related Articles
Koha Item Status Reports
In another article, there was a discussion of a variety of item statuses and how their behavior can be customized in Koha. That article covered everything that is a “status” in Koha: Withdrawn, Lost, Damaged, Restricted, and Not For Loan. However, ...Creating and Running Reports
Koha uses SQL (Structured Query Language) for reports. Basically, you are asking, via SQL, to select certain information from specific fields, where the information matches specified criteria. Of course, libraries who are comfortable with SQL can ...Using Mana to Import Reports into Koha
The Koha community shares reports through Mana-kb. The setup is the same as for Serials and adds a Mana link in the Reports Module. Using Mana in the Reports Module When creating a SQL report, there is a an option in the dropdown - “New SQL from ...Reporting on Deleted Records
One common question is "What happens to my circulation data when an item gets deleted?" The short answer is, thankfully, that data is still there. The same is true for deleted borrower and biblio records. Koha holds onto this data, we just need to ...Notices in Koha
Notices are a powerful way to communicate with patrons about their checked-out materials, holds, renewals, etc. Koha has many options for customizing the notices and customizing how they are delivered, both through settings configuration and through ...