From 485bced7bdcfd9eb081bc64367bae0a58be93ea5 Mon Sep 17 00:00:00 2001
From: Nick Clemens <nick@bywatersolutions.com>
Date: Fri, 24 May 2024 12:30:55 +0000
Subject: [PATCH] Bug 28633: POC - Add sort_name for patron

This is just a possible solution, add a new "sort" name for patrons - we could expand the calculations
here if needed, but essentially we just store the effective name, making it available for SQL sorting

Signed-off-by: Emily Lamancusa <emily.lamancusa@montgomerycountymd.gov>
---
 Koha/Patron.pm                                |  3 ++
 api/v1/swagger/definitions/patron.yaml        |  5 +++
 api/v1/swagger/paths/patrons.yaml             |  5 +++
 .../data/mysql/atomicupdate/bug_28633.pl      | 41 +++++++++++++++++++
 installer/data/mysql/kohastructure.sql        |  3 ++
 .../prog/en/includes/patron-search.inc        |  4 +-
 6 files changed, 59 insertions(+), 2 deletions(-)

diff --git a/Koha/Patron.pm b/Koha/Patron.pm
index 802fda725f..ce29d60635 100644
--- a/Koha/Patron.pm
+++ b/Koha/Patron.pm
@@ -251,6 +251,9 @@ sub store {
                 }
             }
 
+            # Set the sorting name for the patron
+            $self->sort_name( $self->effective_name );
+
             unless ( $self->in_storage ) {    #AddMember
 
                 # Generate a valid userid/login if needed
diff --git a/api/v1/swagger/definitions/patron.yaml b/api/v1/swagger/definitions/patron.yaml
index 56e7e21ef0..a92ab686a2 100644
--- a/api/v1/swagger/definitions/patron.yaml
+++ b/api/v1/swagger/definitions/patron.yaml
@@ -24,6 +24,11 @@ properties:
       - string
       - "null"
     description: patron's preferred name
+  sort_name:
+    type:
+      - string
+      - "null"
+    description: patron's name for sorting
   middle_name:
     type:
       - string
diff --git a/api/v1/swagger/paths/patrons.yaml b/api/v1/swagger/paths/patrons.yaml
index 129b2bae53..a95016a900 100644
--- a/api/v1/swagger/paths/patrons.yaml
+++ b/api/v1/swagger/paths/patrons.yaml
@@ -34,6 +34,11 @@
         description: Case insensitive search on preferred name
         required: false
         type: string
+      - name: sort_name
+        in: query
+        description: Case insensitive search on name used for sorting
+        required: false
+        type: string
       - name: title
         in: query
         description: Case insensitive search on title
diff --git a/installer/data/mysql/atomicupdate/bug_28633.pl b/installer/data/mysql/atomicupdate/bug_28633.pl
index 9bef59047d..407f2680a1 100755
--- a/installer/data/mysql/atomicupdate/bug_28633.pl
+++ b/installer/data/mysql/atomicupdate/bug_28633.pl
@@ -39,6 +39,47 @@ return {
             );
             say $out "Added column 'borrower_modifications.preferred_name'";
         }
+        if ( !column_exists( 'borrowers', 'sort_name' ) ) {
+            $dbh->do(
+                q{
+                ALTER TABLE borrowers
+                ADD COLUMN sort_name longtext NULL DEFAULT NULL
+                COMMENT "patron/borrower's name for sorting purposes"
+                AFTER preferred_name
+            }
+            );
+            say $out "Added column 'borrowers.sort_name'";
+            $dbh->do(
+            q{
+                UPDATE borrowers
+                SET sort_name = firstname
+            }
+            );
+            say $out "Populated sort_name with borrowers firstname"
+
+        }
+        if ( !column_exists( 'deletedborrowers', 'sort_name' ) ) {
+            $dbh->do(
+                q{
+                ALTER TABLE deletedborrowers
+                ADD COLUMN sort_name longtext NULL DEFAULT NULL
+                COMMENT "patron/borrower's name for sorting purposes"
+                AFTER preferred_name
+            }
+            );
+            say $out "Added column 'deletedborrowers.sort_name'";
+        }
+        if ( !column_exists( 'borrower_modifications', 'sort_name' ) ) {
+            $dbh->do(
+                q{
+                ALTER TABLE borrower_modifications
+                ADD COLUMN sort_name longtext NULL DEFAULT NULL
+                COMMENT "patron/borrower's name for sorting purposes"
+                AFTER preferred_name
+            }
+            );
+            say $out "Added column 'borrower_modifications.sort_name'";
+        }
         my @default_patron_search_fields = split( '\|', C4::Context->preference('DefaultPatronSearchFields') );
         unless ( grep /preferred_name/, @default_patron_search_fields ) {
             if ( grep /firstname/, @default_patron_search_fields ) {
diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql
index 2ba68277a3..672c384ea3 100644
--- a/installer/data/mysql/kohastructure.sql
+++ b/installer/data/mysql/kohastructure.sql
@@ -1402,6 +1402,7 @@ CREATE TABLE `borrower_modifications` (
   `surname` longtext DEFAULT NULL,
   `firstname` mediumtext DEFAULT NULL,
   `preferred_name` longtext DEFAULT NULL COMMENT 'patron/borrower''s preferred name',
+  `sort_name` longtext DEFAULT NULL COMMENT 'patron/borrower''s name to be used for sorting',
   `middle_name` longtext DEFAULT NULL COMMENT 'patron/borrower''s middle name',
   `title` longtext DEFAULT NULL,
   `othernames` longtext DEFAULT NULL,
@@ -1524,6 +1525,7 @@ CREATE TABLE `borrowers` (
   `surname` longtext DEFAULT NULL COMMENT 'patron/borrower''s last name (surname)',
   `firstname` mediumtext DEFAULT NULL COMMENT 'patron/borrower''s first name',
   `preferred_name` longtext DEFAULT NULL COMMENT 'patron/borrower''s preferred name',
+  `sort_name` longtext DEFAULT NULL COMMENT 'patron/borrower''s name to be used for sorting',
   `middle_name` longtext DEFAULT NULL COMMENT 'patron/borrower''s middle name',
   `title` longtext DEFAULT NULL COMMENT 'patron/borrower''s title, for example: Mr. or Mrs.',
   `othernames` longtext DEFAULT NULL COMMENT 'any other names associated with the patron/borrower',
@@ -2661,6 +2663,7 @@ CREATE TABLE `deletedborrowers` (
   `surname` longtext DEFAULT NULL COMMENT 'patron/borrower''s last name (surname)',
   `firstname` mediumtext DEFAULT NULL COMMENT 'patron/borrower''s first name',
   `preferred_name` longtext DEFAULT NULL COMMENT 'patron/borrower''s preferred name',
+  `sort_name` longtext DEFAULT NULL COMMENT 'patron/borrower''s name to be used for sorting',
   `middle_name` longtext DEFAULT NULL COMMENT 'patron/borrower''s middle name',
   `title` longtext DEFAULT NULL COMMENT 'patron/borrower''s title, for example: Mr. or Mrs.',
   `othernames` longtext DEFAULT NULL COMMENT 'any other names associated with the patron/borrower',
diff --git a/koha-tmpl/intranet-tmpl/prog/en/includes/patron-search.inc b/koha-tmpl/intranet-tmpl/prog/en/includes/patron-search.inc
index dd53cc2fa4..670c520e7b 100644
--- a/koha-tmpl/intranet-tmpl/prog/en/includes/patron-search.inc
+++ b/koha-tmpl/intranet-tmpl/prog/en/includes/patron-search.inc
@@ -536,7 +536,7 @@
                             }
                             [% CASE 'name-address' %]
                             {
-                                "data": "me.surname:me.preferred_name:me.firstname:me.middle_name:me.othernames:me.street_number:me.address:me.address2:me.city:me.state:me.postal_code:me.country",
+                                "data": "me.surname:me.sort_name:me.middle_name:me.othernames:me.street_number:me.address:me.address2:me.city:me.state:me.postal_code:me.country",
                                 "searchable": true,
                                 "orderable": true,
                                 "render": function( data, type, row, meta ) {
@@ -561,7 +561,7 @@
                             }
                             [% CASE 'name' %]
                             {
-                                "data": "me.surname:me.preferred_name:me.firstname:me.middle_name:me.othernames",
+                                "data": "me.surname:me.sort_name:me.middle_name:me.othernames",
                                 "searchable": true,
                                 "orderable": true,
                                 "render": function( data, type, row, meta ) {
-- 
2.34.1