Bug 39015 - Date sorting not working in statistics wizards
Summary: Date sorting not working in statistics wizards
Status: Signed Off
Alias: None
Product: Koha
Classification: Unclassified
Component: Reports (show other bugs)
Version: Main
Hardware: All All
: P5 - low normal
Assignee: Owen Leonard
QA Contact: Testopia
URL:
Keywords:
Depends on: 38984
Blocks:
  Show dependency treegraph
 
Reported: 2025-02-03 11:28 UTC by David Roberts
Modified: 2025-02-08 21:41 UTC (History)
1 user (show)

See Also:
Change sponsored?: ---
Patch complexity: Trivial patch
Documentation contact:
Documentation submission:
Text to go in the release notes:
This fixes date sorting for the cash register statistics wizard so that columns with dates sort correctly (it adds a "data-sort" attribute to the columns in the results table which contain dates - this allows DataTables to sort using the unformatted date). It also adds the "anti-the" class to the title column, so that it sorts excluding articles such as a, the, and an.
Version(s) released in:
Circulation function:


Attachments
Bug 39015: Fix sorting by date on cash register statistics page (3.77 KB, patch)
2025-02-03 14:52 UTC, Owen Leonard
Details | Diff | Splinter Review
Bug 39015: Fix sorting by date on cash register statistics page (3.30 KB, patch)
2025-02-03 15:23 UTC, Owen Leonard
Details | Diff | Splinter Review
Bug 39015: Fix sorting by date on cash register statistics page (3.34 KB, patch)
2025-02-08 21:32 UTC, David Nind
Details | Diff | Splinter Review

Note You need to log in before you can comment on or make changes to this bug.
Description David Roberts 2025-02-03 11:28:16 UTC
If a site has the dateformat system preference set to dd/mm/yyyy, date sorting doesn't work in the statistics wizards - e.g. /cgi-bin/koha/reports/cash_register_stats.pl. It does seem to sort correctly if the dateformat is set to mm/dd/yyyy.

This might be connected to https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=37875
Comment 1 Owen Leonard 2025-02-03 14:52:55 UTC Comment hidden (obsolete)
Comment 2 Owen Leonard 2025-02-03 15:23:51 UTC
Created attachment 177470 [details] [review]
Bug 39015: Fix sorting by date on cash register statistics page

This patch adds a "data-sort" attribute to the columns in the results
table which contain dates. This allows DataTables to sort using the
unformatted date.

The patch also adds the "anti-the" class to the column containing
titles, ensuring that the sort excludes articles.

To test, apply the patch and go to Reports -> Statistics wizards -> Cash
register. You must have multiple entries in the results, which could be
generated by using the "Create manual invoice" form under a patron's
account tab. Unfortunately that won't create entries with multiple dates
to sort on. I assume a proper test will require some manual SQL updates.

To test title sorting you should have some transactions which are tied
to an item. To generate this data:

- Set the 'WhenLostChargeReplacementFee' system preference to "Charge"
- Add a replacement cost to some items in the catalog.
- Check those items out ot a patron.
- From the "Item details" tab for those records, mark each item lost.
  - The charges should now be on the patron's account.

- On the Cash register statistics page, submit the form using parameters
  which will return multiple results.
- Test that the "Transaction date" and "Updated" columns work correctly
  with various settings of the "dateformat" system preference.
- Test that the "Bibliographic record title" column sorts correctly,
  excluding initial articles (a, an, the) from the sort.

Sponsored-by: Athens County Public Libraries
Comment 3 David Nind 2025-02-08 21:32:20 UTC
Created attachment 177662 [details] [review]
Bug 39015: Fix sorting by date on cash register statistics page

This patch adds a "data-sort" attribute to the columns in the results
table which contain dates. This allows DataTables to sort using the
unformatted date.

The patch also adds the "anti-the" class to the column containing
titles, ensuring that the sort excludes articles.

To test, apply the patch and go to Reports -> Statistics wizards -> Cash
register. You must have multiple entries in the results, which could be
generated by using the "Create manual invoice" form under a patron's
account tab. Unfortunately that won't create entries with multiple dates
to sort on. I assume a proper test will require some manual SQL updates.

To test title sorting you should have some transactions which are tied
to an item. To generate this data:

- Set the 'WhenLostChargeReplacementFee' system preference to "Charge"
- Add a replacement cost to some items in the catalog.
- Check those items out ot a patron.
- From the "Item details" tab for those records, mark each item lost.
  - The charges should now be on the patron's account.

- On the Cash register statistics page, submit the form using parameters
  which will return multiple results.
- Test that the "Transaction date" and "Updated" columns work correctly
  with various settings of the "dateformat" system preference.
- Test that the "Bibliographic record title" column sorts correctly,
  excluding initial articles (a, an, the) from the sort.

Sponsored-by: Athens County Public Libraries
Signed-off-by: David Nind <david@davidnind.com>
Comment 4 David Nind 2025-02-08 21:41:42 UTC
Testing notes using KTD:

1. I could replicate the date sorting issue with dates in the dd/mm/yyyy format, other date formats worked as expected.

2. Example query to update the date field:
   - koha-mysql kohadev
   - update accountlines set date="2025-01-15 12:00:00" where accountlines_id=3;
   - amend to reflect the data in your databases
   - list all transactions: select * from accountlines;
   - I amended the dates so that they were in different months and years

3. I amended the record titles to include the, a, and an at the start of the title - they now correctly sort by ignoring the article.

4. For the "Transaction type" field selector, I changed to "All transactions".