1 of 74

What’s for Sale?

Discover the Power of Koha’s Point of Sale System

Barbara Glassford Johnson, Library Manager Mary Woodward, Circulation Supervisor

Bedford Public Library, TX

2024 koha-US Conference

Round Rock, TX – August 13, 2024

2 of 74

2

What is Point of Sale (POS)?

Point of Sale (aka POS)

For selling items not connected to a patron account: used books, tote bags, flash drives, event tickets, etc.

If desired, integrates with patron-connected payments for lost items, damage, etc.

3 of 74

Point of Sale overview

3

Bedford Public Library

Our Challenge

Our Solution

Setup & Configuration

Sales & Refunds

Reconciling & Reports

Customizations

Opportunities

4 of 74

4

Our Library

Single location

40,516 sq. ft.

Open 7 days/week for 57 hours

Cardholders

35,400

Collection

113,425 items

Annual circulation

422,876

Visits per year

200,000+

730 programs

29,344 attendees

Library staff

20.85 FTE

5 of 74

5

Our Information Desk

Central Information Desk

staffed with two employees

24 staff members

assigned to work the desk

Staff changes hourly

Payments taken for three entities – Library, Friends and Foundation

6 of 74

6

Ancient register from 2009

7 of 74

7

Our Challenge

Old

technology

No support

No manual

One shared register on back counter

Missing receipts wasted tons of register tape

Credit card swipe stopped working

Different devices for cash vs credit cards

On its death bed - needed a quick solution

I became Circulation Supervisor in 2018.

One of the first tasks was to learn how to use this old register

There was was no manual and no support
Nobody knew how to program it
Tried to get IT and Finance to help us
Called vendor we purchased it from and they ‘literally’ laughed at us
They couldn’t believe we were still using it in 2023

We had one shared register on the back counter

Certain credit cards would ask for a PIN and required the patron to come behind the desk which was not secure

One of my favorite features was whenever there was a mistake it would print out the entire transactions since the last printing – could equate to 200 yards of receipt tape

At some point during COVID the credit card machine stopped working with our register.

That’s when it really got interesting –

we had to use the register for cash
a Square for credit cards for library transactions
a Clover for credit card transaction for the Friends
Staff was confused
It was very inefficient
Lots of mistakes were being made

We were nearing the end of a fiscal year and entering pumpkin patch season (a very busy time for us)

So we needed to find a new solution and quick!

8 of 74

8

The Integrated Solution

Investigated a 3^rd party vendor
Pro – integrated solution - transactions would have synced with Koha
Pro – easy to use
Con – significant cost
Con – needed a Verifone account – Verifone was unresponsive
Con – concerned about vendor’s customer support
Con – staff would need to learn a new software

9 of 74

9

The Finance Dept. Solution

Use the Finance’s ERP software – commonly used for accounting, purchase orders, check requests, revenue collection, vendor inquiries, etc.
Con - not designed for a busy, customer-focused service desk
Con - each transaction would take 12 steps – yikes!
Con – no automatic calculation of change
Con – could only be used on one shared computer
Con – staff would have to log in and out every hour
Con – still not integrated with Koha

10 of 74

10

The Koha Solution

POS module integrated into Koha
It’s free
It’s supported – by ByWater and the Koha community
Staff already familiar with Koha
Customizable
Efficient
Not integrated with credit card terminal
Lacks detailed reporting

11 of 74

Setup and Configuration

11

12 of 74

12

System preferences

13 of 74

13

POS enabled

14 of 74

14

Configure cash registers

15 of 74

15

Set a default register (optional)

16 of 74

16

Configure items for purchase

These are known as Debit Types – you may already have some configured in your system.

This is a screenshot of a few of our debit types

We sell a lot of stuff – for the library, the Friends of the Library and the Library Foundation.
Our Finance Dept. uses specific codes for the things we sell – we used their codes as our debit type codes and also incorporated those codes into our descriptions
Our debit type codes in Koha start with the Finance codes
The descriptions use prefixes of BPL, LIB, FR and FD so we will know which entity the account is for and for sorting.

We have 30 different debit types

If you have a lot of debit types, then I would work out a naming scheme before creating them.

Archive and restore

If you create a debit type but no longer need it you can’t delete it but you can archive it

If you need it later you can restore it

That can be handy because you might want a debit type to only be available during a certain time of year – such as the one we use for pumpkins

17 of 74

17

Debit types

18 of 74

18

Payment types

19 of 74

Making Sales

19

20 of 74

20

New setup

Physical cash drawer

Press to open or use key

Separate credit card swipes

Receipt printers

Pin pad for debit cards

21 of 74

21

POS enabled

22 of 74

22

Select items to add to sale

Click the +Add button make a sale

1. Click button to make a sale

23 of 74

23

2. Double-click in cells to edit cost and quantity

Totals are auto-calculated

3. Enter amount patron gives you

4. Change is auto-calculated

5. Select payment type

6. Click confirm

24 of 74

24

Confirmation & receipts

25 of 74

25

POS connected to patron accounts

26 of 74

26

Manual invoice on patron account

27 of 74

27

Lost book payment on patron account

Click the +Add button make a sale

28 of 74

28

Lost book payment

29 of 74

29

POS transaction history

30 of 74

Giving Refunds

30

31 of 74

Refunds�

Go to Transaction History

Find transaction that needs to be refunded

Click on ‘Issue Refund’ button

Enter refund amount

Select original transaction type

Create new transaction for correct amount

31

32 of 74

Daily Reconciliation

32

33 of 74

33

Daily Cash Register Reconciliation

Go to POS > Cash Summary

Print daily cash register reconciliation report

Count cash, checks and credit/debit in the drawer

Verify amounts in drawer match daily report and Cash Summary page

Fix mistakes

Click on the 'Reconcile drawer' link

Confirm that you are removing the amount of cash listed

Click the 'Confirm' button to zero out the register

34 of 74

34

35 of 74

35

36 of 74

36

Types of errors

Wrong fund – copies vs. tote bags
Wrong entity – Library vs. Friends vs. Foundation
Wrong amount
Wrong payment type – cash vs. credit card
Failure to enter credit card transactions in Koha
Reconciling in Koha before counting the drawer

37 of 74

37

Date/Time problems

Missed credit card transaction entered into POS the next morning

Our biggest problem comes from trying to correct the previous day’s transactions.

We wish we could change debit type codes and transaction types after the fact without messing up our reports.

We’d like to be able to edit a transaction in order to fix the fund, entity, amount or payment type. And we would like to be able to backdate the corrected transaction so that it will be on the correct day.

What we really need is a Void button in the POS module which would allow us to correct these errors – there is a bug for that - #28599

Or bug #36029 – Add the ability to backdate a POS transaction

Answer for why we reconcile the next day:

If we reconciled at close we wouldn’t encounter the date/time problem. But we have a rotating staff, many who are part-timers, in the evenings.

They aren’t authorized to reconcile the cash drawer.

And staff always wants to leave instead of stay after so we’ve do the reconciliation in the mornings.

38 of 74

Last reconciliation

Cash summary page has a link to the last reconciliation

Gives totals by debit type and payment type

Only viewable after cash register has been reconciled

38

39 of 74

Enhanced reconciliation modal

Bug 37530

Ability to view detailed information prior to reconciliation

Ability to detect and fix mistakes prior to reconciling

Totals by debit type

Could use in place of a custom report

39

40 of 74

Edit POS transactions - button

Bug 36029

Add edit button to transaction history

Controlled by a permission

Launches a modal

40

41 of 74

Edit POS transactions - modal

Bug 36029

Create a modal for editing POS transactions

Add edit button for each transaction

Change Debit types to a dropdown

Allow editing of debit type, date, amount, payment type, cash register

41

42 of 74

Reports

42

43 of 74

Built-in cash register report�

Go to Reports module > Statistics wizards > Cash register

Gives dates, amounts, debit types, etc.

Doesn’t include what was sold, what entity it was for, how it was paid, etc.

43

44 of 74

44

Built-in cash register report

45 of 74

45

Reports process

What information do you need?
How detailed does it need to be?
How many reports do you need?
How do you know you are getting accurate results?
Who will you be sharing the report(s) with?
Test, test, test!!
Refine. And test again.

What does your report need to include?
What level of detail do you need? Just a total of sales? Or broken down by fund, entity or payment type?
Do you need different reports for specific purposes?
How do you know you are getting accurate results?

We kept an Excel spreadsheet of every transaction with totals so we could compare to the custom reports and make sure the totals were correct.
This allowed us to fine-tune our reports – our goal is to only have to use the reports in Koha but we’re not quite there yet
I also relied heavily on the entries in the account_offsets table

Will you be sharing this report with others?

Will it be understood by others who view it?
Does it need to look professional?

Test and test again! We are still finding things that we need to tweak.

A lot of this was done by trial and error.
We had staff doing all kinds of transactions in the test server and then we would run reports to compare the two.

Things we learned along the way:

needed to remove writeoffs or it would mess with the totals
needed to remove transactions with zero totals
how to subtotal and grand total the money
how to subtract refunds from sales
how to account for refunds
how to account for old debit types

We created all new debit types for POS – the reports included some mystery payments – we realized that old charges from our previous debit type structure (ex. Overdue Fines which we don’t charge anymore) were being included because that’s what was recorded with the transaction at that time it was created – so when it was paid a long time later it still used a debit type we were no longer using. So that meant additional adjustments to our reports in order to account for everything.

how to incorporate complicated concats, joins and unions

46 of 74

46

Account offsets report

SELECT *

FROM account_offsets o

LEFT JOIN accountlines d ON (o.debit_id = d.accountlines_id and d.debit_type_code is not null)

LEFT JOIN accountlines c ON (o.credit_id = c.accountlines_id and c.credit_type_code is not null)

LEFT JOIN cash_registers ON (c.register_id = cash_registers.id)

WHERE DATE(c.date) BETWEEN <<Collected BETWEEN (yyyy-mm-dd)|date>> AND <<and (yyyyy-mm-dd)|date>>

47 of 74

47

Reports

Tables needed:

account_offsets
accountlines
cash_registers

FROM account_offsets o

LEFT JOIN accountlines d ON (o.debit_id = d.accountlines_id and d.debit_type_code is not null)

LEFT JOIN accountlines c ON (o.credit_id = c.accountlines_id and c.credit_type_code is not null)

LEFT JOIN cash_registers ON (c.register_id = cash_registers.id)

48 of 74

48

Daily cash reconciliation report

Daily cash register reconciliation report

Provides total for each fund

Broken down by payment type (cash, credit/debt, check)

49 of 74

Daily cash register reconciliation�

Lists totals for each debit type

Breaks payments down by payment type

Gives subtotals by payment type

Includes refunds

Calculates net totals by payment type

Calculates the grand total

49

50 of 74

50

SQL for daily report

SELECT to_char(c.timestamp, 'yyyy-mm-dd') AS 'Date', cash_registers.name AS 'Register', d.debit_type_code AS 'Fund',

CONCAT('',FORMAT(abs(sum(o.amount)),2),'') AS 'Amount',

c.payment_type AS 'Payment Type'

FROM account_offsets o

LEFT JOIN accountlines d ON (o.debit_id = d.accountlines_id and d.debit_type_code is not null)

LEFT JOIN accountlines c ON (o.credit_id = c.accountlines_id and c.credit_type_code is not null)

LEFT JOIN cash_registers ON (c.register_id = cash_registers.id)

WHERE DATE(c.date) BETWEEN <<Collected BETWEEN (yyyy-mm-dd)|date>> AND <<and (yyyyy-mm-dd)|date>>

AND d.debit_type_code != ''

AND c.payment_type = 'CASH'

GROUP BY d.debit_type_code ASC

UNION ALL

51 of 74

51

Daily report – cash sales

CONCAT('',FORMAT(ABS(sum(o.amount)),2),'') AS 'Amount',

'<h5 style="color: black !important; text-align: left; padding: 6px; margin-top: 0px !important; margin-bottom: 0px !important; line-height: .65 !important; padding-left: 0px; font-size: 15px !important; font-weight: bold;">Total Cash Sales</h5>' AS 'Payment Type'

FROM account_offsets o

LEFT JOIN accountlines d ON (o.debit_id = d.accountlines_id and d.debit_type_code is not null)

LEFT JOIN accountlines c ON (o.credit_id = c.accountlines_id and c.credit_type_code is not null)

LEFT JOIN cash_registers ON (c.register_id = cash_registers.id)

WHERE DATE(c.date) BETWEEN <<Collected BETWEEN (yyyy-mm-dd)|date>> AND <<and (yyyyy-mm-dd)|date>>

AND d.debit_type_code != ''

AND c.payment_type = 'CASH'

UNION ALL

52 of 74

52

Daily report – cash refunds

SELECT '', '', '', CONCAT('',FORMAT(-ABS(sum(c.amount)),2),'') AS 'Amount', CONCAT('','Total Cash Refunds','') AS 'Payment Type'

FROM account_offsets o

LEFT JOIN accountlines d ON (o.debit_id = d.accountlines_id and d.debit_type_code is not null)

LEFT JOIN accountlines c ON (o.credit_id = c.accountlines_id and c.credit_type_code is not null)

LEFT JOIN cash_registers ON (c.register_id = cash_registers.name)

WHERE DATE(c.date) BETWEEN <<Refunded BETWEEN (yyyy-mm-dd)|date>> AND <<and (yyyyy-mm-dd)|date>>

AND c.credit_type_code != 'WRITEOFF' AND c.credit_type_code != 'CANCELLATION’ AND c.credit_type_code != 'PURCHASE'

AND c.credit_type_code != 'PAYMENT’ AND c.credit_type_code != 'CREDIT'

AND d.payment_type = 'CASH’ AND o.type = 'APPLY’ AND o.amount != '0.00'

UNION ALL

53 of 74

53

Daily report – net sales

SELECT '', '', '', CONCAT('',(

(SELECT FORMAT(ABS(sum(o.amount)),2) as sales

FROM account_offsets o

LEFT JOIN accountlines d ON (o.debit_id = d.accountlines_id and d.debit_type_code is not null)

LEFT JOIN accountlines c ON (o.credit_id = c.accountlines_id and c.credit_type_code is not null)

LEFT JOIN cash_registers ON (c.register_id = cash_registers.id)

WHERE DATE(c.date) BETWEEN <<Collected BETWEEN (yyyy-mm-dd)|date>> AND <<and (yyyyy-mm-dd)|date>>

AND d.debit_type_code != ''AND c.payment_type = 'CASH')

+

(SELECT FORMAT(-ABS(sum(c.amount)),2) as refunds

FROM account_offsets o

LEFT JOIN accountlines d ON (o.debit_id = d.accountlines_id and d.debit_type_code is not null)

LEFT JOIN accountlines c ON (o.credit_id = c.accountlines_id and c.credit_type_code is not null)

LEFT JOIN cash_registers ON (c.register_id = cash_registers.name)

WHERE DATE(c.date) BETWEEN <<Refunded BETWEEN (yyyy-mm-dd)|date>> AND <<and (yyyyy-mm-dd)|date>>

AND c.credit_type_code != 'WRITEOFF' AND c.credit_type_code != 'CANCELLATION’ AND c.credit_type_code != 'PURCHASE’ AND c.credit_type_code != 'PAYMENT'

AND c.credit_type_code != 'CREDIT’ AND d.payment_type = 'CASH’ AND o.type = 'APPLY'

AND o.amount != '0.00')) ,'') AS 'Amount', CONCAT('','Net Cash (cash in drawer)','') AS 'Payment Type'

54 of 74

54

Monthly cash reconciliation report

Monthly cash register reconciliation report

Provides total for each fund

Broken down by entity (Library, Friends, Foundation)

55 of 74

Monthly cash register reconciliation�

Totals for each fund broken down by entity

Totals are for all payment types

Grand totals by entity

Allows for proper disbursement of funds to City, Friends and Foundation

55

56 of 74

56

SQL for monthly report

SELECT to_char(c.timestamp, 'yyyy-mm') AS 'Month', 'Library' AS 'Entity',

d.debit_type_code AS 'Fund', CONCAT('',FORMAT(abs(sum(o.amount)),2),'') AS 'Total amount'

FROM account_offsets o

LEFT JOIN accountlines d ON (o.debit_id = d.accountlines_id and d.debit_type_code is not null)

LEFT JOIN accountlines c ON (o.credit_id = c.accountlines_id and c.credit_type_code is not null)

LEFT JOIN cash_registers ON (c.register_id = cash_registers.id)

WHERE c.date BETWEEN CAST(CONCAT(<<Collected between|date>>, ' ', '09:00:00') AS DATETIME) AND CAST(CONCAT(<<and|date>>, ' ', '08:59:59') AS DATETIME)

AND d.debit_type_code LIKE 'LI30%’ AND c.credit_type_code != 'WRITEOFF’ AND d.status IS NULL

OR c.date BETWEEN CAST(CONCAT(<<Collected between|date>>, ' ', '09:00:00') AS DATETIME) AND CAST(CONCAT(<<and|date>>, ' ', '08:59:59') AS DATETIME)

AND d.debit_type_code LIKE 'Printing/Copier Fees’ AND c.credit_type_code != 'WRITEOFF’ AND d.status IS NULL

GROUP BY d.debit_type_code asc

57 of 74

57

Want the SQL?

Daily report

https://github.com/bedford-public-library-texas/reports/blob/main/daily_cash_918

Monthly report

https://github.com/bedford-public-library-texas/reports/blob/main/monthly_cash_952

58 of 74

CSS and jQuery

58

59 of 74

59

Confusing terminology

Cashup = Reconcile/Reconciliation

Bankable = Cash in drawer

Income (cash) = Payment/Sales (cash)

Outgoing (cash) = Refunds (cash)

60 of 74

60

Improved terminology

61 of 74

61

jQuery terminology changes

//BEGIN Rename Cashup buttons in POS

$('button.cashup_individual.btn.btn-xs.btn-default:contains("Record cashup")').text("Reconcile drawer");

$('button.cashup_all.btn.btn-xs.btn-default:contains("Cashup all")').text("Reconcile all");

$('#registers > thead > tr > th:nth-child(3):contains("Last cashup")').text("Last reconciliation");

$('#pos_cashup.btn.btn-default:contains("Record cashup")').text("Reconcile drawer");

$('h2:contains("Cashup history")').text("Drawer reconciliation history");

$('button.cashup_all.btn.btn-default:contains("Cashup all")').text("Reconcile all");

//BEGIN Rename Cash Summary column headers on registers.pl

$('#registers > thead > tr > th:nth-child(5):contains("Bankable")').text("Cash in drawer");

$('#registers > thead > tr > th:nth-child(6):contains("Income (cash)")').text("Payments/Sales (cash)");

$('#registers > thead > tr > th:nth-child(7):contains("Outgoing (cash)")').text("Refunds (cash)*");

62 of 74

62

Make ‘Issue refund’ easier to locate

button.btn.btn-default.btn-xs.pos_refund {

background-color: green;

color: white;

}

63 of 74

Debit type descriptions�

Used a naming scheme to sort debit types by description

By default, the table sorts by debit type code

Table can’t be configured

Used jQuery to sort by the description

Places most used items at the top and reduces scrolling

63

64 of 74

64

Sort by description as a default

/*BEGIN Sort POS items for purchases by description asc as default */

if (window.location.href.indexOf("pay.pl") > -1) {

$('#invoices').DataTable({

"order": [[ 2, "asc" ]],

"columnDefs" : [{"targets":2}],

});

}

65 of 74

65

Sales - money left-aligned

66 of 74

66

CSS to right-align money for sales

/*POS - right-align all money and quantities in 'items for purchase' and 'this sale' area on pos/pay.pl*/

#sale > tbody > tr.odd > td.editable, #sale > tbody > tr.even > td.editable,

#sale > tbody > tr.odd > td:nth-child(4), #sale > tbody > tr.even > td:nth-child(4),

#sale > tfoot > tr > td:nth-child(2),

#sale > tbody > tr.odd > td.editable_int, #sale > tbody > tr.even > td.editable_int,

#invoices > tbody > tr > td:nth-child(3) {

text-align: right;

}

67 of 74

67

Sales - right-aligned money & quantities

68 of 74

Transaction history - money left-aligned

68

69 of 74

69

Right-align money – transaction history

/*POS - right-align all money on transaction history page at register.pl*/

#sales > tbody > tr > td:nth-child(3),

#sales > tfoot > tr > td:nth-child(2),

#past_sales > tbody > tr > td:nth-child(3),

#past_sales > tfoot > tr > td:nth-child(2),

#table_cashups > tbody > tr > td:nth-child(3) {

text-align: right;

}

70 of 74

Transaction history - money right-aligned

70

71 of 74

Opportunities

71

72 of 74

72

Wishlist

Bug 36029 – Add ability to backdate/edit a transaction in POS
Bug 28599 – Add ability to void purchases in Point of Sale
Bug 27785 – Point of Sale needs a note field
Bug 37530 – Ability to view and print previous day’s transactions prior to reconciling
Bug 27800 – Make the ability to enter quantity more clear in Point of Sale
Bug 28164 – Sorting of transaction history doesn’t work
Bug 37566 – Limiting transaction history works but does not recalculate grand total

73 of 74

73

More POS bugs

Bug 37563 – Refund modal in Point of Sale should include ending zeros
Bug 28539 – Allow for negative payments in Point of Sale
Bug 36403 – Add ability to set default sort order for items that display in the Point of Sale system
Bug 28265 – Add option to include and calculate sales tax in Point of Sale
Bug 28406 – Point of Sale doesn’t register a sale when total amount is 0

74 of 74

Thank You!

Contact Us:

Barbara – barbara.johnson@bedfordtx.gov

Mary – mary.woodward@bedfordtx.gov