From d16140e5fc72567618cbba27877f33a46b6617c2 Mon Sep 17 00:00:00 2001 From: Nick Clemens 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 --- 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 c54e4f8d7d1..75f2757814c 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 56e7e21ef08..a92ab686a2f 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 e8e3bebc394..9b395641f26 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 9bef59047db..407f2680a13 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 404f6702aea..7ff0e040311 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -1410,6 +1410,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, @@ -1532,6 +1533,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', @@ -2668,6 +2670,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 99cf512a3de..b1aac3f5342 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/includes/patron-search.inc +++ b/koha-tmpl/intranet-tmpl/prog/en/includes/patron-search.inc @@ -493,7 +493,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 ) { @@ -518,7 +518,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.39.2