Bug 40445

Summary: Point of Sale reconciliation input during daily summaries
Product: Koha Reporter: Hannah Dunne-Howrie <hdunne-howrie>
Component: Point of SaleAssignee: Martin Renvoize (ashimema) <martin.renvoize>
Status: Needs Signoff --- QA Contact: Testopia <testopia>
Severity: enhancement    
Priority: P5 - low CC: david, lucy.vaux-harvey, martin.renvoize, sally.healey
Version: unspecified   
Hardware: All   
OS: All   
GIT URL: Change sponsored?: ---
Patch complexity: --- Documentation contact:
Documentation submission: Text to go in the release notes:
Version(s) released in:
Circulation function:
Bug Depends on: 40625    
Bug Blocks:    
Attachments: Bug 40445: Add CASHUP_SURPLUS and CASHUP_DEFICIT account types
Bug 40445: Add unit tests for cashup reconciliation functionality
Bug 40445: Add cashup reconciliation functionality to point of sale
Bug 40445: (follow-up) Exclude reconciliation accountlines from outstanding_accountlines

Description Hannah Dunne-Howrie 2025-07-18 10:00:07 UTC
We use a separate spreadsheet for our daily reconciliation of income taken which uses the cash register summaries. It occurs to me that this process can be streamlined by allowing an input stage to the summaries process so staff interaction with summaries is not the cashing up to package the transactions into a summary (we want the daily summaries to happen automatically) we want the staff input to be to add in (against totals for payment type and cash register) the amount of what was actually taken using the z reports and physical cash, i.e. a reconciliation stage so it will be easy for staff to see if the money is 'out' and then a process to reconciliate with a way to add in a note for the summary they are signing off with an perhaps a way for them to then 'balance the books' to account for the discrepancy.

We would then need this staff input on the summaries (the physical amount taken and any notes) to be stored in a table, somewhere logical, so we can then report on this and create a way in which library managers can check in on this if needed and audit.
Comment 1 Martin Renvoize (ashimema) 2025-08-08 17:29:08 UTC
Created attachment 185289 [details] [review]
Bug 40445: Add CASHUP_SURPLUS and CASHUP_DEFICIT account types

This patch adds two new system account types to support cashup reconciliation:

- CASHUP_SURPLUS (credit type): Used when actual cash found exceeds expected amount
- CASHUP_DEFICIT (debit type): Used when actual cash found is less than expected amount

Both types are system-managed and cannot be manually added by staff. They will be
automatically created during cashup processes when discrepancies are detected.

Changes include:
- Atomicupdate script for existing installations
- Mandatory YAML files for fresh installations
Comment 2 Martin Renvoize (ashimema) 2025-08-08 17:29:10 UTC
Created attachment 185290 [details] [review]
Bug 40445: Add unit tests for cashup reconciliation functionality

This patch adds comprehensive unit tests for the cashup reconciliation system:

- Tests for balanced cashups (no surplus/deficit accountlines created)
- Tests for surplus scenarios with CASHUP_SURPLUS credit creation
- Tests for deficit scenarios with CASHUP_DEFICIT debit creation
- Tests for user note handling and storage in reconciliation accountlines
- Tests for transaction integrity ensuring atomicity
- Tests for note sanitization (whitespace trimming, empty note handling)

The tests ensure that:
- Reconciliation accountlines are only created when there's a discrepancy
- Surplus/deficit amounts are calculated and stored correctly
- User notes are properly combined with system reconciliation details
- Database transactions maintain consistency
- Account types are linked correctly to cash registers
Comment 3 Martin Renvoize (ashimema) 2025-08-08 17:29:12 UTC
Created attachment 185291 [details] [review]
Bug 40445: Add cashup reconciliation functionality to point of sale

This patch implements comprehensive cashup reconciliation capabilities for the Koha
point-of-sale system, allowing staff to record actual cash amounts and automatically
track surplus or deficit discrepancies.

Backend Changes:
- Enhanced Koha::Cash::Register->add_cashup() method to accept actual_amount and optional notes
- Automatic creation of CASHUP_SURPLUS or CASHUP_DEFICIT accountlines when discrepancies detected
- Database transaction handling ensures atomicity of cashup actions and reconciliation records
- Input validation and sanitization for amounts and notes

Frontend Changes:
- Interactive cashup modal requiring staff to enter actual amount removed from register
- Real-time reconciliation calculation showing surplus/deficit as user types
- Conditional note field (1000 char limit) appears only when discrepancies are detected
- Enhanced cashup summary modal displays reconciliation details prominently
- Empty amount field forces conscious entry (no pre-population)

Features:
- Balanced cashups: Only cashup action created (no reconciliation accountlines)
- Surplus cashups: Creates CASHUP_SURPLUS credit with audit details
- Deficit cashups: Creates CASHUP_DEFICIT debit with audit details
- Staff notes: Optional explanations for discrepancies stored with system calculations
- Full audit trail: All reconciliation data preserved with timestamps and manager links

The implementation ensures mathematical balance is maintained while providing complete
audit capabilities for cash register discrepancies.
Comment 4 Martin Renvoize (ashimema) 2025-08-08 17:29:14 UTC
Created attachment 185292 [details] [review]
Bug 40445: (follow-up) Exclude reconciliation accountlines from outstanding_accountlines

The outstanding_accountlines method should exclude CASHUP_SURPLUS and
CASHUP_DEFICIT accountlines that are created during cashup reconciliation.
These reconciliation entries represent discrepancies found during cashup
and should not be counted as outstanding amounts for subsequent cashups.

This prevents surplus/deficit accountlines from being included in the
total when calculating expected amounts for future cashups.

Test plan:
1. Perform a cashup with surplus or deficit
2. Verify that reconciliation accountlines are created
3. Check that subsequent calls to outstanding_accountlines()
   exclude these reconciliation entries
4. Run prove t/db_dependent/Koha/Cash/Register.t
Comment 5 Martin Renvoize (ashimema) 2025-08-08 17:29:51 UTC
# Test plan

## Prerequisites

1. **System Requirements:**
    - `UseCashRegisters` system preference set to 'Use' to enable cash management
    - `EnablePointOfSale` set to enabled to expose 'Point of sale' functionality
    - Staff user with `cash_management` permissions (specifically 'cashup' permission)
    - At least one cash register configured for the current library

## Test Scenarios

### 1. Basic Cashup Modal Interface

**Objective:** Verify the enhanced cashup modal interface and user experience

**Steps:**

1. Navigate to Point of Sale → Register (select a specific register)
2. Perform some transactions to create outstanding amounts (e.g., process a payment)
3. Click "Record cashup" button
4. **Verify:** Cashup modal opens with the following elements:
    - Expected amount clearly displayed (should match outstanding transactions)
    - Float amount shown
    - "Actual amount removed from register" field is **empty** (not pre-populated)
    - Field is marked as required with proper validation
    - Reconciliation section is hidden initially
    - Note field is hidden initially
5. **Verify:** Cannot submit form with empty amount field
6. Close modal and repeat to ensure consistency

### 2. Real-time Reconciliation Calculation

**Objective:** Test the dynamic reconciliation calculation as user types

**Steps:**

1. Open cashup modal (as above)
2. Note the expected amount (e.g., £15.00)
3. **Test Case 2a - Balanced Amount:**
    - Enter the exact expected amount (e.g., 15.00)
    - **Verify:** Reconciliation text shows "Balanced - no surplus or deficit"
    - **Verify:** Text appears in green/success styling
    - **Verify:** Note field remains hidden (no discrepancy)
4. **Test Case 2b - Surplus Amount:**
    - Enter amount higher than expected (e.g., 18.50 when expecting 15.00)
    - **Verify:** Reconciliation text shows "Surplus: £3.50"
    - **Verify:** Text appears in green/success styling
    - **Verify:** Note field appears for explanation
5. **Test Case 2c - Deficit Amount:**
    - Enter amount lower than expected (e.g., 12.00 when expecting 15.00)
    - **Verify:** Reconciliation text shows "Deficit: £3.00"
    - **Verify:** Text appears in warning styling (orange/yellow)
    - **Verify:** Note field appears for explanation
6. **Test Case 2d - Invalid Input:**
    - Clear the field
    - **Verify:** Reconciliation section hides
    - **Verify:** Note field hides
    - Enter invalid values (letters, special characters)
    - **Verify:** No reconciliation calculation occurs

### 3. Balanced Cashup Processing

**Objective:** Test cashup when actual amount equals expected amount

**Steps:**

1. Create transactions totaling £10.00 (or known amount)
2. Open cashup modal
3. Enter exact expected amount (£10.00)
4. **Optional:** Add a note (should be ignored for balanced cashups)
5. Click "Confirm cashup"
6. **Verify:** Page redirects successfully (POST/REDIRECT/GET pattern)
7. **Verify:** Cashup appears in cashup history
8. **Database Verification:**

    ```sql
    -- Check cashup action created
    SELECT * FROM cash_register_actions WHERE code = 'CASHUP' ORDER BY timestamp DESC LIMIT 1;

    -- Check NO surplus/deficit accountlines created
    SELECT * FROM accountlines WHERE register_id = [REGISTER_ID]
    AND (credit_type_code = 'CASHUP_SURPLUS' OR debit_type_code = 'CASHUP_DEFICIT');
    ```

9. **Verify:** No surplus/deficit entries in account lines

### 4. Surplus Cashup Processing

**Objective:** Test cashup when actual amount exceeds expected amount

**Steps:**

1. Create transactions totaling £15.00
2. Open cashup modal
3. Enter higher amount (£20.00 = £5.00 surplus)
4. **Verify:** Shows "Surplus: £5.00" in reconciliation
5. **Optional:** Add explanatory note: "Found extra £5 note in drawer"
6. Click "Confirm cashup"
7. **Verify:** Successful redirect
8. **Database Verification:**

    ```sql
    -- Check cashup action
    SELECT amount FROM cash_register_actions WHERE code = 'CASHUP' ORDER BY timestamp DESC LIMIT 1;
    -- Should show 20.00

    -- Check surplus accountline
    SELECT amount, description, note FROM accountlines
    WHERE register_id = [REGISTER_ID] AND credit_type_code = 'CASHUP_SURPLUS'
    ORDER BY date DESC LIMIT 1;
    -- Amount should be -5.00 (negative for credit)
    -- Note should contain user's explanation
    ```

9. **Verify:** Surplus accountline created with correct negative amount
10. **Verify:** User note stored correctly (if provided)

### 5. Deficit Cashup Processing

**Objective:** Test cashup when actual amount is less than expected

**Steps:**

1. Create transactions totaling £25.00
2. Open cashup modal
3. Enter lower amount (£22.00 = £3.00 deficit)
4. **Verify:** Shows "Deficit: £3.00" with warning styling
5. Add explanatory note: "£3 missing - possible incorrect change given"
6. Click "Confirm cashup"
7. **Verify:** Successful redirect
8. **Database Verification:**

    ```sql
    -- Check deficit accountline
    SELECT amount, description, note FROM accountlines
    WHERE register_id = [REGISTER_ID] AND debit_type_code = 'CASHUP_DEFICIT'
    ORDER BY date DESC LIMIT 1;
    -- Amount should be 3.00 (positive for debit)
    -- Note should contain explanation
    ```

9. **Verify:** Deficit accountline created with correct positive amount
10. **Verify:** User note stored correctly

### 6. Note Field Validation

**Objective:** Test note field behavior and validation

**Steps:**

1. Create discrepancy situation (surplus or deficit)
2. **Test Case 6a - Valid Note:**
    - Enter meaningful note (under 1000 characters)
    - **Verify:** Note saves correctly with cashup
3. **Test Case 6b - Empty/Whitespace Note:**
    - Leave note field empty OR enter only spaces/tabs
    - Submit cashup
    - **Verify:** No note stored in database (should be NULL/undef)
4. **Test Case 6c - Long Note:**
    - Enter note approaching 1000 character limit
    - **Verify:** Submission works correctly
    - Enter note exceeding 1000 characters
    - **Verify:** Note is truncated to 1000 characters
5. **Test Case 6d - Special Characters:**
    - Enter note with quotes, apostrophes, newlines
    - **Verify:** Special characters handled properly

### 7. Cashup Summary Modal Enhancement

**Objective:** Test the enhanced cashup summary display

**Steps:**

1. Perform various cashups (balanced, surplus, deficit)
2. Navigate to cashup history or click "Summary" link for completed cashups
3. **Verify:** Summary modal displays:
    - All regular transactions grouped properly
    - Reconciliation information displayed separately in footer
    - Surplus/deficit amounts highlighted distinctly
    - Clean separation between regular transactions and reconciliation data
4. **Test different scenarios:**
    - Summary of balanced cashup (no reconciliation footer)
    - Summary of surplus cashup (highlighted surplus in footer)
    - Summary of deficit cashup (highlighted deficit in footer)

### 8. Outstanding Accountlines Filtering

**Objective:** Verify surplus/deficit entries don't affect subsequent cashups

**Steps:**

1. Perform cashup with surplus/deficit (creates reconciliation accountlines)
2. Add new regular transactions
3. **Verify:** Outstanding accountlines total excludes previous reconciliation entries
4. Perform another cashup
5. **Verify:** Expected amount calculation is correct (not inflated by reconciliation entries)

### 9. Transaction Integrity and Error Handling

**Objective:** Test system behavior under error conditions

**Steps:**

1. **Database Transaction Test:**
    - Monitor database during cashup process
    - **Verify:** All changes (cashup action + reconciliation accountline) are atomic
2. **Permission Test:**
    - Test with user lacking 'cashup' permission
    - **Verify:** Appropriate error message displayed
3. **Invalid Amount Test:**
    - Try submitting with invalid amounts (negative, non-numeric)
    - **Verify:** Client-side validation prevents submission
    - **Verify:** Server-side validation as backup
4. **Network Interruption:**
    - Submit cashup and simulate network issue
    - **Verify:** No partial/duplicate cashup records created

### 10. Multi-Register Environment

**Objective:** Test functionality with multiple cash registers

**Steps:**

1. Configure multiple cash registers for same library
2. Perform cashups on different registers
3. **Verify:** Reconciliation entries are correctly associated with specific registers
4. **Verify:** Outstanding amounts are calculated per register
5. Test "Cashup All" functionality if available
6. **Verify:** Individual register reconciliation works correctly

## Expected Results Summary

**After successful testing, the system should demonstrate:**

1. **User Experience:**

    - Intuitive modal interface requiring conscious amount entry
    - Real-time feedback on discrepancies
    - Conditional note fields appearing only when needed
    - Clear visual distinction between different reconciliation states

2. **Data Integrity:**

    - Accurate cashup amounts recorded
    - Proper creation of surplus/deficit accountlines when needed
    - Correct mathematical relationships (credits negative, debits positive)
    - User notes preserved exactly as entered (or NULL if empty)

3. **System Behavior:**

    - Atomic database transactions ensuring consistency
    - Proper filtering of reconciliation entries from outstanding calculations
    - Successful POST/REDIRECT/GET pattern preventing duplicate submissions
    - Enhanced summary displays showing reconciliation data appropriately

4. **Audit Trail:**
    - Complete timestamp records for all actions
    - Manager ID linkage for accountability
    - Detailed descriptions for reconciliation entries
    - Preserved user explanations for discrepancies

## Automated Test Verification

**Run the test suite:**

```bash
docker exec --user kohadev-koha --workdir /kohadevbox/koha -i kohadev-koha-1 bash -c 'prove t/db_dependent/Koha/Cash/Register.t'
```

**Expected:** All tests pass, covering balanced cashup, surplus handling, deficit handling, note management, and transaction integrity.

---

**Note:** This test plan assumes the CASHUP_SURPLUS and CASHUP_DEFICIT account types are properly installed via the mandatory data files or database updates. If these are missing, the functionality will not work correctly.