SQL help - Bulk Card Number modification

classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|

SQL help - Bulk Card Number modification

satishamv
Hi,

With Koha 16.11.11 / Package Installation/ Ubuntu 14.04 Desktop with
MySQL -  mysql Ver 14.14 Distrib 5.5.57

We use, students' register number as card number for circulation. And
regarding bulk 'card number' modifications :

I have a situation, where, first semester borrowers will only get
'permanent register number' during the time of examinations.
Till that, I should manage borrowers' circulation with 'temporary card
number' (assigned by me). After receiving 'permanent register number from
the college office, I have to replace 'temporary number' by 'permanent card
number'.

Going editing/modifying each patron one by one is very tedious and time
consuming, therefore, Is there any way I could use sql and some kind of
script to replace card number? (at the terminal)

Your suggesstions or best practices would be very helpful.

Thank You.

Satish MV
Librarian
Govt. Engineering College, Hassan
www.gechassan.ac.in


--
_______________________________________________
Koha mailing list  http://koha-community.org
[hidden email]
https://lists.katipo.co.nz/mailman/listinfo/koha
Reply | Threaded
Open this post in threaded view
|

Re: SQL help - Bulk Card Number modification

Pedro Amorim
Hello Satish,

I'm not sure I fully understand your situation but I'll try to provide help
based on what I could figure out:


*We use, students' register number as card number for circulation.
Andregarding bulk 'card number' modifications :*
Does this mean you use the patrons' borrowernumber as cardnumber? So
borrowernumber = cardnumber?



*After receiving 'permanent register number fromthe college office, I have
to replace 'temporary number' by 'permanent cardnumber'.*
How exactly is the temporary card number going to be assigned? What's the
pattern? Are you using the already present cardnumber and concatenating
something like "temp" to it?

If what I stated above is true, then this should help:

UPDATE borrowers
   SET cardnumber = CONCAT("TMP", borrowernumber)
   WHERE categorycode = 'STU';

UPDATE borrowers
   SET cardnumber = borrowernumber
   WHERE cardnumber LIKE "TMP*";

If what I stated above is false and/or makes no sense, then ignore
everything I said :)

GL,

Pedro Amorim


On 18 January 2018 at 04:57, SATISH <[hidden email]> wrote:

> Hi,
>
> With Koha 16.11.11 / Package Installation/ Ubuntu 14.04 Desktop with
> MySQL -  mysql Ver 14.14 Distrib 5.5.57
>
> We use, students' register number as card number for circulation. And
> regarding bulk 'card number' modifications :
>
> I have a situation, where, first semester borrowers will only get
> 'permanent register number' during the time of examinations.
> Till that, I should manage borrowers' circulation with 'temporary card
> number' (assigned by me). After receiving 'permanent register number from
> the college office, I have to replace 'temporary number' by 'permanent card
> number'.
>
> Going editing/modifying each patron one by one is very tedious and time
> consuming, therefore, Is there any way I could use sql and some kind of
> script to replace card number? (at the terminal)
>
> Your suggesstions or best practices would be very helpful.
>
> Thank You.
>
> Satish MV
> Librarian
> Govt. Engineering College, Hassan
> www.gechassan.ac.in
>
>
> --
> _______________________________________________
> Koha mailing list  http://koha-community.org
> [hidden email]
> https://lists.katipo.co.nz/mailman/listinfo/koha
>
_______________________________________________
Koha mailing list  http://koha-community.org
[hidden email]
https://lists.katipo.co.nz/mailman/listinfo/koha
Reply | Threaded
Open this post in threaded view
|

Re: SQL help - Bulk Card Number modification

satishamv
Hi Pedro,

Thank you for the reply.

I try to make my question little clear now,

I am not referring to 'borrowernumber' (assigned by Koha) instead referring
to 'cardumber'

Temporary cardnumber does not prefix with TMP ( now, I understand it's
importance)

Current patron "cardnumber "  designed by the library,  is in following
format: 17ME01, 17ME02, so on (upto 250+)
17= 2017, ME =Mechanical Engineering, 01,02 = Serial Number of the student
as per the List provided by the College Office.

Because, every year, to the 'first semester' admission, it takes few months
to get the register number assigned by the University.
Till that, I need to manage with some 'cardnumber' to take care of
circulation.

I use, University Registration Number as 'cardnumber' in Koha as it is
easily  trace-able across the various sections/departments when no-dues
verification needed by each department. If I use my own format, it may
conflict.

New number assigned by the University is: 4GH17ME18, 4GH17ME19, so
on....and it is referred as permanent for the patron for future use
and Library should use this number.

So, my current requirement is:

"cardnumber" = 17ME01, 17ME02, ....to be changed as =  4GH17ME18, 4GH17ME19
in bulk.

And I request, any scripts or sql to modify in bulk, would be useful.

Thank you

Satish MV
Librarian
Govt. Engineering College, Hassan.
Karnataka.
_______________________________________________
Koha mailing list  http://koha-community.org
[hidden email]
https://lists.katipo.co.nz/mailman/listinfo/koha
Reply | Threaded
Open this post in threaded view
|

Re: SQL help - Bulk Card Number modification

Hugo Agud-2
Hi

the easiest way is to create a temporary table in Koha where you store the
old and the new cardnumber and with a sql statement update the
borrowers.cardnumber with the new value

Kindest Regards

2018-01-18 12:57 GMT+01:00 SATISH <[hidden email]>:

> Hi Pedro,
>
> Thank you for the reply.
>
> I try to make my question little clear now,
>
> I am not referring to 'borrowernumber' (assigned by Koha) instead referring
> to 'cardumber'
>
> Temporary cardnumber does not prefix with TMP ( now, I understand it's
> importance)
>
> Current patron "cardnumber "  designed by the library,  is in following
> format: 17ME01, 17ME02, so on (upto 250+)
> 17= 2017, ME =Mechanical Engineering, 01,02 = Serial Number of the student
> as per the List provided by the College Office.
>
> Because, every year, to the 'first semester' admission, it takes few months
> to get the register number assigned by the University.
> Till that, I need to manage with some 'cardnumber' to take care of
> circulation.
>
> I use, University Registration Number as 'cardnumber' in Koha as it is
> easily  trace-able across the various sections/departments when no-dues
> verification needed by each department. If I use my own format, it may
> conflict.
>
> New number assigned by the University is: 4GH17ME18, 4GH17ME19, so
> on....and it is referred as permanent for the patron for future use
> and Library should use this number.
>
> So, my current requirement is:
>
> "cardnumber" = 17ME01, 17ME02, ....to be changed as =  4GH17ME18, 4GH17ME19
> in bulk.
>
> And I request, any scripts or sql to modify in bulk, would be useful.
>
> Thank you
>
> Satish MV
> Librarian
> Govt. Engineering College, Hassan.
> Karnataka.
> _______________________________________________
> Koha mailing list  http://koha-community.org
> [hidden email]
> https://lists.katipo.co.nz/mailman/listinfo/koha
>



--

*Hugo Agud - Orex Digital *

*www.orex.es <http://www.orex.es>*


<http://www.orex.es/>    [image: www.orex.es/koha] <http://www.orex.es/koha>
   [image: www.orex.es/vufind] <http://www.orex.es/vufind>


Directo

Calle Sant Joaquin,117, 2º-3ª · 08922 Santa Coloma de Gramanet - Tel: 933
856 138   [hidden email] · http://www.orex.es/



No imprima este mensaje a no ser que sea necesario. Una tonelada de papel
implica la tala de 15 árboles y el consumo de 250.000 litros de agua.



Aviso de confidencialidad
Este mensaje contiene información que puede ser CONFIDENCIAL y/o de USO
RESTRINGIDO. Si usted no es el receptor deseado del mensaje (ni
está autorizado a recibirlo por el remitente), no está autorizado a copiar,
reenviar o divulgar el mensaje o su contenido. Si ha recibido este mensaje
por error, por favor, notifíquenoslo inmediatamente y bórrelo de su sistema.
_______________________________________________
Koha mailing list  http://koha-community.org
[hidden email]
https://lists.katipo.co.nz/mailman/listinfo/koha
Reply | Threaded
Open this post in threaded view
|

Re: SQL help - Bulk Card Number modification

satishamv
Hello Hugo,

Can you please do little elaborate it, how to go step by step.

-satish
_______________________________________________
Koha mailing list  http://koha-community.org
[hidden email]
https://lists.katipo.co.nz/mailman/listinfo/koha
Reply | Threaded
Open this post in threaded view
|

Re: SQL help - Bulk Card Number modification

Jens Weber (PHSH)
In reply to this post by satishamv

Hello Satish

To me it seems the easiest way to proceed might be to use the "Import patrons" feature (in the 'Tools' section). This can also be used to *update* patron information -- and this can be single fields, including cardnumber.

The only caveat here is that the records to update are either selected by cardnumber or by username (field "userid"). Because you want to change cardnumbers you will have to select by usernames -- and in case you haven't set these you will need to do so in a first step where you select by cardnumber.

Hope that is of help --
Best regards

Jens



_________________________________________
Jens Weber
Pädagogische Hochschule Schaffhausen
Didaktisches Zentrum
Ebnatstrasse 80
8200 Schaffhausen
www.phsh.ch





> -----Ursprüngliche Nachricht-----
> Von: SATISH [mailto:[hidden email]]
> Gesendet: Donnerstag, 18. Januar 2018 06:58
> An: Koha
> Betreff: [Koha] SQL help - Bulk Card Number modification
>
> Hi,
>
> With Koha 16.11.11 / Package Installation/ Ubuntu 14.04 Desktop with
> MySQL -  mysql Ver 14.14 Distrib 5.5.57
>
> We use, students' register number as card number for circulation. And
> regarding bulk 'card number' modifications :
>
> I have a situation, where, first semester borrowers will only get
> 'permanent register number' during the time of examinations.
> Till that, I should manage borrowers' circulation with 'temporary card
> number' (assigned by me). After receiving 'permanent register number from
> the college office, I have to replace 'temporary number' by 'permanent card
> number'.
>
> Going editing/modifying each patron one by one is very tedious and time
> consuming, therefore, Is there any way I could use sql and some kind of
> script to replace card number? (at the terminal)
>
> Your suggesstions or best practices would be very helpful.
>
> Thank You.
>
> Satish MV
> Librarian
> Govt. Engineering College, Hassan
> www.gechassan.ac.in
>
>
> --

_______________________________________________
Koha mailing list  http://koha-community.org
[hidden email]
https://lists.katipo.co.nz/mailman/listinfo/koha
Reply | Threaded
Open this post in threaded view
|

Re: SQL help - Bulk Card Number modification

ramakant
In reply to this post by satishamv
Dear Satish,

 

With the help of sql query you will resolve your problem.

Make your data in excel sheet  then use below query for the same and resolve
your query in bulk

e.g. update borrowers set cardnumber = '4GH17ME18' where cardnumber =
'17ME01';

 

 

Regards,

 

============================================================================
===========

Ramakant

Senior Library Information Assistant

P K Kelkar Library

Indian Institute of Technology Kanpur - 208 016

Email - [hidden email], [hidden email]

Phone: 0512 6797707 (O), +91 8090049089 (Mo.)

 

_______________________________________________
Koha mailing list  http://koha-community.org
[hidden email]
https://lists.katipo.co.nz/mailman/listinfo/koha
Reply | Threaded
Open this post in threaded view
|

Re: SQL help - Bulk Card Number modification

satishamv
Dear Ramakant,

Yes, I did the same way for modification of a single cardnumber.
but how to do it for bulk? and also data is in excel.

with thanks
satish
_______________________________________________
Koha mailing list  http://koha-community.org
[hidden email]
https://lists.katipo.co.nz/mailman/listinfo/koha
Reply | Threaded
Open this post in threaded view
|

Re: SQL help - Bulk Card Number modification

Radek Šiman
Hi Satish,

maybe this piece of SQL code could help you... Please adjust the
parameters @prefix and @offset. In the case you're satisfied with
results, use the formulas in your UPDATE command.

Regards,
Radek

select * FROM (
     SELECT
         @prefix:="4GH" as cardnumber,
         @offset:=17 as university_id
     ) as vars
where 0 = 1

union

select
     cardnumber,
     CONCAT(
         @prefix,
         left(cardnumber, 2),
         MID(cardnumber, 3, 2),
         @offset + CAST(right(cardnumber, 2) as UNSIGNED INT)
     ) as university_id
from borrowers

Dne 19.01.18 v 7:21 SATISH napsal(a):

> Dear Ramakant,
>
> Yes, I did the same way for modification of a single cardnumber.
> but how to do it for bulk? and also data is in excel.
>
> with thanks
> satish
> _______________________________________________
> Koha mailing list  http://koha-community.org
> [hidden email]
> https://lists.katipo.co.nz/mailman/listinfo/koha


_______________________________________________
Koha mailing list  http://koha-community.org
[hidden email]
https://lists.katipo.co.nz/mailman/listinfo/koha