<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40"><head><meta http-equiv=Content-Type content="text/html; charset=utf-8"><meta name=Generator content="Microsoft Word 15 (filtered medium)"><style><!--
/* Font Definitions */
@font-face
{font-family:Wingdings;
panose-1:5 0 0 0 0 0 0 0 0 0;}
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:DengXian;
panose-1:2 1 6 0 3 1 1 1 1 1;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:"\@DengXian";
panose-1:2 1 6 0 3 1 1 1 1 1;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
font-size:11.0pt;
font-family:"Calibri",sans-serif;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
p.MsoListParagraph, li.MsoListParagraph, div.MsoListParagraph
{mso-style-priority:34;
margin-top:0cm;
margin-right:0cm;
margin-bottom:0cm;
margin-left:36.0pt;
font-size:11.0pt;
font-family:"Calibri",sans-serif;}
span.EmailStyle18
{mso-style-type:personal-reply;
font-family:"Calibri",sans-serif;
color:windowtext;}
.MsoChpDefault
{mso-style-type:export-only;
font-family:"Calibri",sans-serif;}
@page WordSection1
{size:612.0pt 792.0pt;
margin:72.0pt 72.0pt 72.0pt 72.0pt;}
div.WordSection1
{page:WordSection1;}
/* List Definitions */
@list l0
{mso-list-id:950471831;
mso-list-type:hybrid;
mso-list-template-ids:-1341067694 2017210406 201916419 201916421 201916417 201916419 201916421 201916417 201916419 201916421;}
@list l0:level1
{mso-level-number-format:bullet;
mso-level-text:-;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;
font-family:"Calibri",sans-serif;
mso-fareast-font-family:DengXian;}
@list l0:level2
{mso-level-number-format:bullet;
mso-level-text:o;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;
font-family:"Courier New";}
@list l0:level3
{mso-level-number-format:bullet;
mso-level-text:;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;
font-family:Wingdings;}
@list l0:level4
{mso-level-number-format:bullet;
mso-level-text:;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;
font-family:Symbol;}
@list l0:level5
{mso-level-number-format:bullet;
mso-level-text:o;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;
font-family:"Courier New";}
@list l0:level6
{mso-level-number-format:bullet;
mso-level-text:;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;
font-family:Wingdings;}
@list l0:level7
{mso-level-number-format:bullet;
mso-level-text:;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;
font-family:Symbol;}
@list l0:level8
{mso-level-number-format:bullet;
mso-level-text:o;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;
font-family:"Courier New";}
@list l0:level9
{mso-level-number-format:bullet;
mso-level-text:;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;
font-family:Wingdings;}
ol
{margin-bottom:0cm;}
ul
{margin-bottom:0cm;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]--></head><body lang=EN-AU link=blue vlink=purple style='word-wrap:break-word'><div class=WordSection1><p class=MsoNormal>Glad to hear other folk are interested to store numbers in E.164 too! Like you say, we’re already doing it for SMS numbers, so I think it makes sense to do it for all phone numbers. Kyle, what do you think about my Koha::Patron->phone() method for doing that?<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>I was thinking how it would be nice to format them for display, but it’s probably easier said than done.<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal> For instance, in Australia, we have several different formats (https://www.stylemanual.gov.au/grammar-punctuation-and-conventions/numbers-and-measurements/telephone-numbers):<o:p></o:p></p><ul style='margin-top:0cm' type=disc><li class=MsoListParagraph style='margin-left:0cm;mso-list:l0 level1 lfo1'>0412 345 678 (mobile)<o:p></o:p></li><li class=MsoListParagraph style='margin-left:0cm;mso-list:l0 level1 lfo1'>02 1234 5678 (landline)<o:p></o:p></li><li class=MsoListParagraph style='margin-left:0cm;mso-list:l0 level1 lfo1'>1300 123 456 (nation-wide local rate number)<o:p></o:p></li><li class=MsoListParagraph style='margin-left:0cm;mso-list:l0 level1 lfo1'>13 00 00 (alternative nation-wide local rate number)<o:p></o:p></li></ul><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>It looks like Number::Phone doesn’t support Australian numbers and Number::Phone::Normalize looks old/simple. <o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>David Cook<o:p></o:p></p><p class=MsoNormal>Senior Software Engineer<o:p></o:p></p><p class=MsoNormal>Prosentient Systems<o:p></o:p></p><p class=MsoNormal>Suite 7.03<o:p></o:p></p><p class=MsoNormal>6a Glen St<o:p></o:p></p><p class=MsoNormal>Milsons Point NSW 2061<o:p></o:p></p><p class=MsoNormal>Australia<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>Office: 02 9212 0899<o:p></o:p></p><p class=MsoNormal>Online: 02 8005 0595<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><div style='border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0cm 0cm 0cm'><p class=MsoNormal><b><span lang=EN-US>From:</span></b><span lang=EN-US> Kyle Hall <kyle@bywatersolutions.com> <br><b>Sent:</b> Wednesday, 14 September 2022 8:26 PM<br><b>To:</b> dcook@prosentient.com.au<br><b>Cc:</b> koha-devel <koha-devel@lists.koha-community.org>; Tomas Cohen Arazi <tomascohen@theke.io>; Martin Renvoize <martin.renvoize@ptfs-europe.com><br><b>Subject:</b> Re: Handling normalized phone number data<o:p></o:p></span></p></div><p class=MsoNormal><o:p> </o:p></p><div><div><p class=MsoNormal>Storing numbers in E.164 makes sense to me! We already enforce it for SMS numbers, so it would make sense to extend this to all other phone numbers. It would then be trivial to have a phone number format syspref in the same way we have a date format syspref. For searching it would be as simple as stripping all non-numerics from the search term and performing a substring search.<o:p></o:p></p></div><div><p class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal>Perhaps Number::Phone or Number::Phone::Normalize would be of use.<o:p></o:p></p></div><div><p class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal>Kyle<o:p></o:p></p></div></div><p class=MsoNormal><o:p> </o:p></p><div><div><p class=MsoNormal>On Wed, Sep 14, 2022 at 2:43 AM <<a href="mailto:dcook@prosentient.com.au">dcook@prosentient.com.au</a>> wrote:<o:p></o:p></p></div><blockquote style='border:none;border-left:solid #CCCCCC 1.0pt;padding:0cm 0cm 0cm 6.0pt;margin-left:4.8pt;margin-right:0cm'><div><div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Hi all,<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>I’ve been working lately on <a href="https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=23817" target="_blank">https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=23817</a> in an effort to let staff users easily search phone numbers in the Patrons module.<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Currently, phone number searching doesn’t work if you have punctuation or other formatting inconsistent with your search query. <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>So I’m proposing normalizing phone numbers. Normalizing the search query is easy, but normalizing the data in the database is harder. In older versions of Koha, I used SQL to normalize the phone column in the WHERE clause, but that’s seemingly impossible now that we’re using DataTables and the REST API.<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>So it seems to me the only way forward is to normalize the data in the database. I’ve attached a patch which provides a Koha::Patron->phone() set method which normalizes the phone number before it’s saved in the database, but I don’t know what other Koha folk think about that. <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Thoughts?<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>David Cook<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Senior Software Engineer<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Prosentient Systems<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Suite 7.03<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>6a Glen St<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Milsons Point NSW 2061<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Australia<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Office: 02 9212 0899<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Online: 02 8005 0595<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p></div></div></div></blockquote></div><p class=MsoNormal><br clear=all><br>-- <o:p></o:p></p><div><div><div><div><div><div><div><div><div><p class=MsoNormal style='background:white'><span style='font-size:10.0pt;font-family:"Arial",sans-serif;color:#222222'>Kyle M. Hall</span><span style='font-size:12.0pt;font-family:"Arial",sans-serif;color:#222222'><o:p></o:p></span></p></div></div><div><p class=MsoNormal style='background:white'><span style='font-family:"Arial",sans-serif;color:black'>Loose Cannon, ByWater Solutions</span><o:p></o:p></p></div><div><p class=MsoNormal style='background:white'><span style='font-size:10.0pt;font-family:"Arial",sans-serif;color:#222222'><a href="mailto:kyle@bywatersolutions.com" target="_blank"><span style='color:#1155CC'>kyle@bywatersolutions.com</span></a><br><a href="http://bywatersolutions.com/" target="_blank"><span style='color:#1155CC'>https://bywatersolutions.com</span></a></span><o:p></o:p></p></div><p class=MsoNormal><b><span style='font-size:10.0pt;font-family:"Arial",sans-serif;color:#222222;background:white'><a href="http://bywatersolutions.com/what-is-koha/" target="_blank"><span style='color:#1155CC'>What is Koha?</span></a></span></b><o:p></o:p></p></div></div></div></div></div></div></div></div></body></html>