How to migrate from Aleph to Koha: ALEPH 21 to Koha 3.22 migration scripts in English   

This document includes all ALEPH 21 to Koha 3.22 migration scripts.

The scripts will take between 1 and 2 workdays to execute.

Order of migration:

0. Turn off access to reserve items in Primo/OPAC.

0.1 Make ALEPH read-only: http://support.exlibrisgroup.com/articles/Q_A/Putting-Aleph-in-Read-Only-Mode-during-cutover-to-new-instance/?q=make+aleph+readonly&l=en_US&c=Product%3AAleph&fs=Search&pn=1

0.2 Turn of the print daemon in Aleph.

0.3 Reset the Koha database

DELETE FROM issues;
DELETE FROM old_issues;
DELETE FROM items;
DELETE FROM reserves;
DELETE FROM old_reserves;
DELETE FROM accountlines;
DELETE FROM statistics;
DELETE FROM borrowers;
DELETE FROM biblio;
DELETE FROM biblioitems;
DELETE FROM auth_header;
DELETE FROM sessions;
DELETE FROM zebraqueue;

ALTER TABLE borrowers AUTO_INCREMENT=1;
ALTER TABLE statistics AUTO_INCREMENT=1;
ALTER TABLE biblio AUTO_INCREMENT=1;
ALTER TABLE biblioitems AUTO_INCREMENT=1;
ALTER TABLE issues AUTO_INCREMENT=1;
ALTER TABLE old_issues AUTO_INCREMENT=1;
ALTER TABLE reserves AUTO_INCREMENT=1;
ALTER TABLE old_reserves AUTO_INCREMENT=1;
ALTER TABLE accountlines AUTO_INCREMENT=1;
ALTER TABLE items AUTO_INCREMENT=1;
ALTER TABLE auth_header  AUTO_INCREMENT=1;
ALTER TABLE  sessions AUTO_INCREMENT=1;
ALTER TABLE zebraqueue AUTO_INCREMENT=1;

Reset the Zebra Index: If you are running a package install then you'll want to run the following commands to reset the authorities
and biblios Zebra indices instead:

$ sudo zebraidx -c /etc/koha/sites/zzzlibraryzebra-authorities-dom.cfg -g iso2709 -d authorities init
$ sudo zebraidx -c /etc/koha/sites/zzzlibraryzebra-biblios.cfg -g iso2709 -d biblios init

1. Migrate the catalogue data from MARCXML file to Koha. (approx. 1h)
1.5 Migrate the items data from Aleph to Koha using SQL. (approx. 1h)

2.0 Add anonymous Patron user (borrowernumber = 1) in Koha

2.2 Migrate the Patron/user database from Aleph to Koha (approx. 8h)

2.2.1 Migrate the issue history from Aleph to Koha using SQL. (approx. 1h)

2.2.2 Add access rights for librarian users to Koha.

flags = 1 ADMIN => Superlibrarian

UPDATE `borrowers` SET flags='1' WHERE `cardnumber` in ('0987654321')

flags = 69334 NORMAL => Circulate, Catalogue, Borrowers, Reserveforothers, Borrow, Editcatalogue, Updatecharges, Acquisition, Reports,

UPDATE `borrowers` SET flags='69334' WHERE `cardnumber` in ('1234567890')

2.5 Create generic desk users in Koha.

flags = 1238 CIRKULATION => Circulate, Catalogue, Borrowers, Reserveforothers, Borrow, Updatecharges

UPDATE `borrowers` SET flags='1238'  WHERE `cardnumber` in ('DESKUSER')

3.1 Migrate active loans from Aleph to Koha using SQL.
3.2 Migrate active reservations from Aleph to Koha using SQL.
3.3 Migrate patron debts from Aleph to Koha using SQL.

7. Make sure that the Koha server can send an enough amount of outgoing mails.

11. Update so that there is only one e-mail address conected with the e-mail field in the Koha borrowers table:

UPDATE borrowers SET email=trim(substr(email, 1, instr(email, ';')-1)), B_email=trim(substr(email, instr(email, ';')+1)) WHERE email like '%;%'

12. Extract a list of items that were not imported into Koha from Aleph:

select substr(z308_rec_key, 3, 10) as Personnummer, z36_id, z30_rec_key, case when z36_rec_key is not null then 'ONLOAN' else NULL end as ONLOAN, z30_barcode, z30_sub_library, z30_material, z30_call_no,
A.z13_author || ' (' || A.z13_year || ') ' || ' ' || A.z13_title AS TITLE, 
B.z13_author || ' (' || B.z13_year || ') ' || ' ' || B.z13_title AS B_TITLE
from LTU01.z13 A
left outer join LTU01.z103 on substr(z103_rec_key_1,6,9) = substr(z13_rec_key, 1, 9)
left outer join LTU50.z30 on substr(z103_rec_key,6,9) = substr(z30_rec_key, 1, 9)
left outer join LTU50.z36 on z30_rec_key = z36_rec_key
Left outer join LTU50.z13 B on substr(z30_rec_key, 1, 9)=substr(B.z13_rec_key, 1, 9)
left outer join USR01.z308 on z36.z36_id=z308_id and z308_rec_key like '03%'
where Z103_LKR_LIBRARY='LTU50' and Z103_LKR_TYPE='ADM' and z30_rec_key not in (select z30_rec_key from TMP_BIBID_USED)

After the migration it is possible to execute queries to see what was never migrated:

12.1.

CREATE TABLE TMP_BIBID_USED AS (select z30_rec_key, z00r_text as BIBID from LTU50.TMP_KOHA_BIBID 
left outer join LTU01.z00r on z00r_text = BIBID
left outer join LTU01.z103 on substr(z103_rec_key_1,6,9) = z00r_doc_number
left outer join LTU50.z30 on substr(z103_rec_key,6,9) = substr(z30_rec_key, 1, 9)
where 
z00r_field_code='001' and
Z103_LKR_LIBRARY='LTU50' and Z103_LKR_TYPE='ADM' and
trim(z30_barcode) is not NULL)

12.2.

 select substr(z308_rec_key, 3, 10) as Personnummer, z36_id, z30_rec_key, case when z36_rec_key is not null then 'ONLOAN' else NULL end as ONLOAN, z30_barcode, z30_sub_library, z30_material, z30_call_no,
A.z13_author || ' (' || A.z13_year || ') ' || ' ' || A.z13_title AS TITLE, 
B.z13_author || ' (' || B.z13_year || ') ' || ' ' || B.z13_title AS B_TITLE
from LTU01.z13 A
left outer join LTU01.z103 on substr(z103_rec_key_1,6,9) = substr(z13_rec_key, 1, 9)
left outer join LTU50.z30 on substr(z103_rec_key,6,9) = substr(z30_rec_key, 1, 9)
left outer join LTU50.z36 on z30_rec_key = z36_rec_key
Left outer join LTU50.z13 B on substr(z30_rec_key, 1, 9)=substr(B.z13_rec_key, 1, 9)
left outer join USR01.z308 on z36.z36_id=z308_id and z308_rec_key like '03%'
where
Z103_LKR_LIBRARY='LTU50' and
Z103_LKR_TYPE='ADM' and
z30_rec_key not in (select z30_rec_key from TMP_BIBID_USED)

Export the result from 12.2 into an Excelfile, it is containing all the records that where never exported into Koha.

13. After all changes rebuild Zebra INDEX:

sudo /usr/sbin/koha-rebuild-zebra -v -f zzzlibrary


 Comments   
Comment by LTU [ 2016-01-19 ]

STEP 1 + STEP 1.5

ALEPH: Here are the items

LIBRIS: Here are the MARCXML records

Import the MARCXML file into Koha using this command:

sudo koha-shell -c "perl /usr/share/koha/bin/migration_tools/bulkmarcimport.pl -b -v -m=MARCXML -file /tmp/LAUB.total.20150708.marc.marcxml" zzzlibrary

To import 220 000 records takes about 40 minutes.

  • This SQL generates a cross-index list between KOHA's biblioitemnumber and biblionumber and ALEPH'S/LIBRIS' bibid number
SELECT biblionumber, biblioitemnumber, ExtractValue(marcxml, '//controlfield[@tag=\"001\"]') as BIBID FROM biblioitems
  • Import the result from the SQL query into a table in the ALEPH zzz50 table called TMP_KOHA_BIBID
  • Make sure that the BIBID in TMP_KOHA_BIBID does not contain any NULL rows - if you find any remove them.

Run the following command on the ALEPH server ZZZ50 database

select '"' || trim(biblionumber) || '","' || trim(biblioitemnumber) || '",' || trim(z30_barcode) || ',NULL,NULL,' || trim(z30_sub_library) || ',NULL,NULL,NULL,NULL,NULL,NULL,' || case trim(z30_item_status) when '90' then '1' else '0' end || ',' || case trim(z30_item_process_status) when 'DA' then '1' else '0' end || ',' || case trim(z30_item_process_status) when 'MI' then '1' else '0' end || ',NULL,' || case trim(z30_item_status) when '70' then '1' else '0' end || ',NULL,"' || replace(trim(trim(z30_call_no) || ' ' || trim(z30_call_no_2) || ' ' || trim(z30_description)),'"','""') || '",NULL,' || trim(z30_no_loans) || ',NULL,NULL,NULL,' || case trim(replace(trim(z30_note_opac) || ' ' || trim(z30_note_circulation),'"','""')) when '.' then 'NULL' else '"' || trim(replace(trim(z30_note_opac) || ' ' || trim(z30_note_circulation),'"','""')) || '"' end || ',' || case trim(replace(z30_note_internal,'"','""')) when '' then 'NULL' else '"' || trim(replace(z30_note_internal,'"','""')) || '"' end || ',' || trim(z30_sub_library) || ',NULL,NULL,NULL,' || case when z36_due_date is null then 'NULL' else '"' || substr(z36_due_date,1,4) || '-' || substr(z36_due_date, 5,2) || '-' || substr(z36_due_date,7,2) || '"' end || ',ddc,NULL,' || trim(z30_collection) || ',NULL,NULL,' || trim(z30_item_status) || ',NULL,NULL,NULL,NULL' as insert_data
from LTU50.TMP_KOHA_BIBID 
left outer join LTU01.z00r on z00r_text = BIBID
left outer join LTU01.z103 on substr(z103_rec_key_1,6,9) = z00r_doc_number
left outer join LTU50.z30 on substr(z103_rec_key,6,9) = substr(z30_rec_key, 1, 9)
left outer join LTU50.z36 on z30_rec_key = z36_rec_key
where 
z00r_field_code='001' and
Z103_LKR_LIBRARY='LTU50' and Z103_LKR_TYPE='ADM' and
trim(z30_barcode) is not NULL
  • Export the result from above into a CSV file: (Select CSV + no headers + no left or right enclosures + UTF-8)
  • Upload the file on /var/tmp on the Koha server

If neccessary run this command:

sed 's/\"\"/\"/g' export.alephdata_loaddata_1.csv > export.alephdata_loaddata_1.parsed.csv

to remove double quotations

  • Import the file "export.alephdata_loaddata_1.parsed.csv" to Koha database using the SQL command below:
LOAD DATA INFILE '/tmp/export.alephdata_loaddata_1.parsed.csv'
INTO TABLE items
CHARACTER SET UTF8
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
(biblionumber, biblioitemnumber, barcode, dateaccessioned, booksellerid, homebranch, price, replacementprice, replacementpricedate, datelastborrowed, datelastseen, stack, notforloan, damaged, itemlost, itemlost_on, withdrawn, withdrawn_on, itemcallnumber, coded_location_qualifier, issues, renewals, reserves, restricted, itemnotes, itemnotes_nonpublic, holdingbranch, paidfor, location, permanent_location, onloan, cn_source, cn_sort, ccode, materials, uri, itype, more_subfields_xml, enumchron, copynumber, stocknumber)
SET itemnumber = NULL;

Make sure that all fields were imported correctly. E.g. holdingbrancg should be set for all the records:

If the import fails - check for duplicates in the barcode field or other fields. Remove these duplicates. Barcodes cannot have the comma character, remove the comma if found.

If you see a problem with Swedish characters å + ä + ö then check below:

update items 
set itemnotes = replace(itemnotes, '}', 'å') 
where itemnotes like '%}%'

update items 
set itemnotes = replace(itemnotes, '{', 'ä') 
where itemnotes like '%{%'

update items 
set itemnotes = replace(itemnotes, '|', 'ö') 
where itemnotes like '%|%'

Comment by LTU [ 2016-01-19 ]

STEP 2.2 Migrate the Patron/user database from Aleph to Koha (approx. 8h)

1) Log in to your aleph-account with ssh.

$> csh -f $aleph_proc/p_file_03 usr01,z308,go,,FIX 

Creates a textfile /exlibris/aleph/u21_1/usr01/files/z308.seqaa

$> mv /exlibris/aleph/u21_1/usr01/files/z308.seqaa . 
$> sed s/^/"insert into rawstage values \(\'"/g z308.seqaa > left.s 
$> awk '{print $0"\);"}' left.s > l.s 
$> sed s/')'/"\')"/g l.s > l1.s 
$> mv l1.s rawstage.sql 
$> \rm -r l*.s z308* 

2) Execute rawstage.sql in the USR01-schema.

(a21_1):~>s+ usr01 
usr01@ALEPH21>drop table rawstage; 
usr01@ALEPH21>create table rawstage(in_row varchar2(200)); 
usr01@ALEPH21>@rawstage.sql 
usr01@ALEPH21>commit 
usr01@ALEPH21>exit 

2b) Creates a PL/SQL Stored Procedure.

CREATE OR REPLACE PROCEDURE duplicateRemoverTable( 
    table_in IN VARCHAR2, 
    column_in IN VARCHAR2) 
IS 
  sql_str VARCHAR2(1000); 
BEGIN 
  sql_str := 'DROP TABLE DUPLICATE_T; CREATE TABLE DUPLICATE_T AS ' || 'select ' || column_in || ',rownum rownumber, rowid radid from ' || table_in || ' where ' || column_in || ' in(select ' || column_in || ' from ' || table_in ||' group by ' || column_in || ' having count (' || column_in || ') > 1) order by ' || column_in || ';'; 
  sql_str := sql_str || ' delete from ' || table_in || ' where rowid in (select radid from DUPLICATE_T where mod(ROWNUMBER,2) = 1); commit;'; 
  sql_str := upper(sql_str); 
  dbms_output.put_line(sql_str); 
  --execute immediate sql_str; 
EXCEPTION 
WHEN OTHERS THEN 
  raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM); 
END; 

3) Creates support tables needen for step-to-step work to get the final table to_kova_t

Run in schema USR01.

DROP TABLE passwd_raw_t; 
CREATE TABLE passwd_raw_t parallel 4 AS 
SELECT trim(SUBSTR(in_row, 3,20)) FIRST_KEY, 
  trim(SUBSTR(in_row, 3,LENGTH(in_row)-20)) rest 
FROM rawstage 
WHERE SUBSTR(in_row, 0,LENGTH(in_row)-16) LIKE '%AC'; 
COMMIT; 

DROP TABLE DUPLICATE_T; 
CREATE TABLE DUPLICATE_T AS SELECT FIRST_KEY,ROWNUM ROWNUMBER, ROWID RADID FROM PASSWD_RAW_T WHERE FIRST_KEY IN(SELECT FIRST_KEY FROM PASSWD_RAW_T GROUP BY FIRST_KEY HAVING COUNT (FIRST_KEY) > 1) ORDER BY FIRST_KEY; 
DELETE FROM PASSWD_RAW_T WHERE ROWID IN (SELECT RADID FROM DUPLICATE_T WHERE MOD(ROWNUMBER,2) = 1); COMMIT; 


DROP TABLE passwd_t; 
CREATE TABLE passwd_t parallel 4 AS 
SELECT first_key, 
  trim(SUBSTR(SUBSTR(rest,instr(rest,'LTU50') +5),0,instr(SUBSTR(rest,instr(rest,'LTU50')+5),' '))) passwd, 
  LENGTH(trim(SUBSTR(SUBSTR(rest,instr(rest,'LTU50') +5),0,instr(SUBSTR(rest,instr(rest,'LTU50')+5),' ')))) langd, 
  is_number(trim(SUBSTR(SUBSTR(rest,instr(rest,'LTU50')+5),0,instr(SUBSTR(rest,instr(rest,'LTU50')+5),' ')))) isnumber 
FROM passwd_raw_t 
WHERE SUBSTR(rest,instr(rest,'LTU50')+5) LIKE '% %'; 
COMMIT; 

DROP TABLE DUPLICATE_T; 
CREATE TABLE DUPLICATE_T AS SELECT FIRST_KEY,ROWNUM ROWNUMBER, ROWID RADID FROM PASSWD_T WHERE FIRST_KEY IN(SELECT FIRST_KEY FROM PASSWD_T GROUP BY FIRST_KEY HAVING COUNT (FIRST_KEY) > 1) ORDER BY FIRST_KEY; 
DELETE FROM PASSWD_T WHERE ROWID IN (SELECT RADID FROM DUPLICATE_T WHERE MOD(ROWNUMBER,2) = 1); 
COMMIT; 

DROP TABLE keypasswd_t; 
CREATE TABLE keypasswd_t parallel 4 AS 
SELECT first_key, passwd FROM passwd_t WHERE langd<5 AND isnumber='1'; 
COMMIT; 

DROP TABLE DUPLICATE_T; 
CREATE TABLE DUPLICATE_T AS SELECT FIRST_KEY,ROWNUM ROWNUMBER, ROWID RADID FROM KEYPASSWD_T WHERE FIRST_KEY IN(SELECT FIRST_KEY FROM KEYPASSWD_T GROUP BY FIRST_KEY HAVING COUNT (FIRST_KEY) > 1) ORDER BY FIRST_KEY; 
DELETE FROM KEYPASSWD_T WHERE ROWID IN (SELECT RADID FROM DUPLICATE_T WHERE MOD(ROWNUMBER,2) = 1); 
COMMIT; 

4) Create and run a PL/SQL Stored Procedure cleanAlephName which trim whitespaces before and after some fields.

CREATE OR REPLACE PROCEDURE cleanAlephName 
IS 
  CURSOR z304_cur 
  IS 
    SELECT rowid, 
      z304_address 
    FROM z304 
    WHERE z304_address LIKE ' %' 
    OR z304_address LIKE '% '; 
BEGIN 
  FOR z304_rec IN z304_cur 
  LOOP 
    UPDATE z304 
    SET z304_address =trim(z304_address), 
      z304_zip =trim(z304_zip), 
      z304_email_address=trim(z304_email_address), 
      z304_telephone =trim(z304_telephone), 
      z304_cat_name =trim(z304_cat_name) 
    WHERE rowid =z304_rec.rowid; 
    COMMIT; 
  END LOOP; 
EXCEPTION 
WHEN OTHERS THEN 
  raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM); 
END cleanAlephName; 

 exec cleanAlephName; 

5) Create and run a PL/SQL Stored Procedure cleanAlephPhone which remove some unwanted characters.

CREATE OR REPLACE PROCEDURE cleanAlephPhone 
IS 
  CURSOR z304_cur 
  IS 
    SELECT rowid FROM z304; 
BEGIN 
  FOR z304_rec IN z304_cur 
  LOOP 
    UPDATE z304 
    SET z304_telephone=trim(REPLACE(REPLACE(REPLACE(z304_telephone,'-',''),' ',''),'/','')) 
    WHERE rowid =z304_rec.rowid; 
    COMMIT; 
  END LOOP; 
EXCEPTION 
WHEN OTHERS THEN 
  raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM); 
END; 

 exec cleanAlephPhone; 

6) Creates a support-table och creates a Stored Procedure cleanDmp_t which trims strings.

DROP TABLE dmp_t; 
CREATE TABLE dmp_t parallel 4 AS 
SELECT trim(SUBSTR(z304_rec_key,0,LENGTH(z304_rec_key)-2)) KEY, 
  z304_address ADDRESS, 
  z304_email_address MAIL, 
  Z304_TELEPHONE PHONE, 
  z304_cat_name 
FROM z304; 
COMMIT; 

DROP TABLE DUPLICATE_T; 
CREATE TABLE DUPLICATE_T AS SELECT KEY,ROWNUM ROWNUMBER, ROWID RADID FROM DMP_T WHERE KEY IN(SELECT KEY FROM DMP_T GROUP BY KEY HAVING COUNT (KEY) > 1) ORDER BY KEY; 
DELETE FROM DMP_T WHERE ROWID IN (SELECT RADID FROM DUPLICATE_T WHERE MOD(ROWNUMBER,2) = 1); 
COMMIT; 

CREATE OR REPLACE PROCEDURE cleanDmp_t 
IS 
  CURSOR dmp_t_cur 
  IS 
    SELECT rowid FROM dmp_t; 
BEGIN 
  FOR dmp_t_rec IN dmp_t_cur 
  LOOP 
    UPDATE dmp_t 
    SET address =trim(address), 
      mail =trim(mail), 
      phone =trim(phone), 
      z304_cat_name=trim(z304_cat_name) 
    WHERE rowid =dmp_t_rec.rowid; 
    COMMIT; 
  END LOOP; 
EXCEPTION 
WHEN OTHERS THEN 
  raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM); 
END; 

 exec cleanDmp_t; 

7) Create a support-table.

DROP TABLE dmp2_t; 
CREATE TABLE dmp2_t parallel 4 AS 
SELECT dmp_t.key, 
  dmp_t.mail, 
  passwd_raw_t.rest 
FROM dmp_t, 
  passwd_raw_t 
WHERE passwd_raw_t.first_key=dmp_t.key; 
COMMIT; 

DROP TABLE DUPLICATE_T; 
CREATE TABLE DUPLICATE_T AS SELECT KEY,ROWNUM ROWNUMBER, ROWID RADID FROM DMP2_T WHERE KEY IN(SELECT KEY FROM DMP2_T GROUP BY KEY HAVING COUNT (KEY) > 1) ORDER BY KEY; 
DELETE FROM DMP2_T WHERE ROWID IN (SELECT RADID FROM DUPLICATE_T WHERE MOD(ROWNUMBER,2) = 1); 
COMMIT; 

8) Creates a Stored Procedure that reurns the decrypted password.

CREATE OR REPLACE FUNCTION getDecryptedPIN( 
    in_key IN VARCHAR2) 
  RETURN VARCHAR2 
IS 
  v_pin VARCHAR2(20) :='1111'; 
BEGIN 
  SELECT DISTINCT(passwd) INTO v_pin FROM keypasswd_t WHERE first_key=in_key; 
  RETURN v_pin; 
EXCEPTION 
WHEN OTHERS THEN 
  RETURN '0000'; 
END; 

9) Creates support-tables.

DROP TABLE ssnpid_t; 
CREATE TABLE ssnpid_t parallel 4 AS 
SELECT trim(SUBSTR(Z353_REC_KEY,15)) SSN, 
  getDecryptedPIN(trim(SUBSTR(Z353_REC_KEY,15))) PIN, 
  Z353_ID 
FROM z353 
WHERE Z353_REC_KEY LIKE '% LTU50ID %'; 
COMMIT; 

DROP TABLE DUPLICATE_T; 
CREATE TABLE DUPLICATE_T AS SELECT SSN,ROWNUM ROWNUMBER, ROWID RADID FROM SSNPID_T WHERE SSN IN(SELECT SSN FROM SSNPID_T GROUP BY SSN HAVING COUNT (SSN) > 1) ORDER BY SSN; 
DELETE FROM SSNPID_T WHERE ROWID IN (SELECT RADID FROM DUPLICATE_T WHERE MOD(ROWNUMBER,2) = 1); 
COMMIT; 

DROP TABLE DUPLICATE_T; 
CREATE TABLE DUPLICATE_T AS SELECT Z353_ID,ROWNUM ROWNUMBER, ROWID RADID FROM SSNPID_T WHERE Z353_ID IN(SELECT Z353_ID FROM SSNPID_T GROUP BY Z353_ID HAVING COUNT (Z353_ID) > 1) ORDER BY Z353_ID; 
DELETE FROM SSNPID_T WHERE ROWID IN (SELECT RADID FROM DUPLICATE_T WHERE MOD(ROWNUMBER,2) = 1); COMMIT; 

DROP TABLE testdmp_t; 
CREATE TABLE testdmp_t parallel 4 AS 
SELECT ssnpid_t.ssn SSN, 
  ssnpid_t.pin PIN, 
  ssnpid_t.z353_id IDKEY, 
  trim(dmp_t.address) ADDRESS, 
  dmp_t.mail MAIL, 
  dmp_t.phone PHONE 
FROM ssnpid_t, 
  dmp_t 
WHERE ssnpid_t.z353_id=dmp_t.key; 
COMMIT; 

DROP TABLE DUPLICATE_T; CREATE TABLE DUPLICATE_T AS SELECT SSN,ROWNUM ROWNUMBER, ROWID RADID FROM TESTDMP_T WHERE SSN IN(SELECT SSN FROM TESTDMP_T GROUP BY SSN HAVING COUNT (SSN) > 1) ORDER BY SSN; DELETE FROM TESTDMP_T WHERE ROWID IN (SELECT RADID FROM DUPLICATE_T WHERE MOD(ROWNUMBER,2) = 1); COMMIT; 
DROP TABLE DUPLICATE_T; CREATE TABLE DUPLICATE_T AS SELECT IDKEY,ROWNUM ROWNUMBER, ROWID RADID FROM TESTDMP_T WHERE IDKEY IN(SELECT IDKEY FROM TESTDMP_T GROUP BY IDKEY HAVING COUNT (IDKEY) > 1) ORDER BY IDKEY; DELETE FROM TESTDMP_T WHERE ROWID IN (SELECT RADID FROM DUPLICATE_T WHERE MOD(ROWNUMBER,2) = 1); COMMIT; 

10) Creates the main table to_kova_t which contains extracted data from eralier created tables och Stored Procedures.

DROP TABLE to_kova_t; 
CREATE TABLE to_kova_t parallel 4 AS 
SELECT SSN, 
  PIN, 
  MAIL, 
  REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(PHONE,' ',''),'-',''),'/',''),'\',''),',','') PHONE, 
  trim(SUBSTR(trim(SUBSTR(address,0,49)), 1, INSTR(trim(SUBSTR(address,0,49)), ',')-1)) SURNAME, 
  trim(SUBSTR(trim(SUBSTR(address,0,49)), INSTR(trim(SUBSTR(address,0,49)), ',') +1)) FIRSTNAME, 
  trim(SUBSTR(address,50,47)) Street, 
  trim(SUBSTR(trim(SUBSTR(address,97,50)),0,LENGTH(trim(REPLACE(REPLACE(REPLACE(lower(SUBSTR(address,97,50)),'å','a'),'ä','a'),'ö','o')))-instr(reverse(trim(REPLACE(REPLACE(REPLACE(lower(SUBSTR(address,97,50)),'å','a'),'ä','a'),'ö','o'))),' '))) ZIP, 
  trim(initcap(SUBSTR(trim(SUBSTR(address,97,50)),1 +LENGTH(trim(REPLACE(REPLACE(REPLACE(lower(SUBSTR(address,97,50)),'å','a'),'ä','a'),'ö','o')))-instr(reverse(trim(REPLACE(REPLACE(REPLACE(lower(SUBSTR(address,97,50)),'å','a'),'ä','a'),'ö','o'))),' ')))) CITY , 
  trim(SUBSTR(address,145)) ADDRESS2 
FROM testdmp_t 
ORDER BY LENGTH(SSN); 
COMMIT; 

DROP TABLE DUPLICATE_T; 
CREATE TABLE DUPLICATE_T AS SELECT SSN,ROWNUM ROWNUMBER, ROWID RADID FROM TO_KOVA_T WHERE SSN IN(SELECT SSN FROM TO_KOVA_T GROUP BY SSN HAVING COUNT (SSN) > 1) ORDER BY SSN; 
DELETE FROM TO_KOVA_T WHERE ROWID IN (SELECT RADID FROM DUPLICATE_T WHERE MOD(ROWNUMBER,2) = 1); 
COMMIT; 

11) Creates some Stored Procedures that modify som present data.

CREATE OR REPLACE PROCEDURE cleanTo_kova_t 
IS 
  CURSOR to_kova_t_cur 
  IS 
    SELECT rowid FROM to_kova_t; 
BEGIN 
  FOR to_kova_t_rec IN to_kova_t_cur 
  LOOP 
    UPDATE to_kova_t 
    SET mail =trim(mail), 
      phone =trim(phone), 
      surname =trim(surname), 
      firstname=trim(firstname), 
      street =trim(street), 
      zip =trim(zip), 
      city =trim(city), 
      address2 =INITCAP(trim(address2)) 
    WHERE rowid=to_kova_t_rec.rowid; 
    COMMIT; 
  END LOOP; 
EXCEPTION 
WHEN OTHERS THEN 
  raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM); 
END; 

 exec cleanTo_kova_t; 


CREATE OR REPLACE FUNCTION shallShift( 
    surname_in IN VARCHAR2) 
  RETURN VARCHAR2 
IS 
  shift VARCHAR2(1); 
BEGIN 
  shift := 0; 
  IF NVL(LENGTH(surname_in),0) = 0 THEN 
    shift := 1; 
  END IF; 
RETURN shift; 
EXCEPTION 
WHEN OTHERS THEN 
  raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM); 
END; 

CREATE OR REPLACE PROCEDURE cleanName 
IS 
  shift VARCHAR2(1); 
  CURSOR borrowers_cur 
  IS 
    SELECT rowid, surname, firstname,shallShift(surname) FROM to_kova_t; 
BEGIN 
  FOR borrowers_rec IN borrowers_cur 
  LOOP 
    shift := shallShift(borrowers_rec.surname); 
    IF shift = '1' THEN 
      UPDATE to_kova_t SET surname=firstname WHERE rowid=borrowers_rec.rowid; 
      UPDATE to_kova_t SET firstname=NULL WHERE rowid=borrowers_rec.rowid; 
      COMMIT; 
    END IF; 
  END LOOP; 
EXCEPTION 
WHEN OTHERS THEN 
  raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM); 
END; 

 exec cleanName; 

12) Encrypt the PIN-code och save/run it in/from file named run.sh

select './passwd.pl ' || SSN || ' ' || PIN || ' >> runner.sql' from to_kova_t; 

Save this resultset in a file run.sh and transfer it to the Koha-machine.

Clean the data SSN from unwanted signs.

$> sed s/'*'/'X'/g run.sh > run2.sh 
$> mv run2.sh run.sh 
$> sed s/"'"/X/g run.sh > r.sh 
$> mv r.sh run.sh 
$> chmod 700 run.sh 

Run (it takes a long time)

./run.sh 

the results is in a file named runner.sql

13) Update all users with new password character.
Run s+ USR01

Run runner.sql,takes some time to update to_kova_t.

14) Remove duplicates in to_kova_t

  
DROP TABLE DUPLICATE_T; 
CREATE TABLE DUPLICATE_T AS SELECT SSN,ROWNUM ROWNUMBER, ROWID RADID FROM TO_KOVA_T WHERE SSN IN(SELECT SSN FROM TO_KOVA_T GROUP BY SSN HAVING COUNT (SSN) > 1) ORDER BY SSN; 
DELETE FROM TO_KOVA_T WHERE ROWID IN (SELECT RADID FROM DUPLICATE_T WHERE MOD(ROWNUMBER,2) = 1); 
COMMIT; 

15) Decide how many months back in time users shall be migrated.
1 year = 12 months
5 years = 4x12=60 months

 
DELETE 
FROM to_kova_t 
WHERE ssn NOT IN 
  (SELECT ssn 
  FROM ssnpid_t 
  WHERE getLastActivity(z353_id)>TO_CHAR(ADD_MONTHS (SYSDATE, -60),'YYYYMMDD') 
  ); 
COMMIT; 

16) It might be problems with firstname and surname.
If a user has all names in firstname fields they are moved to the surname field.

 
CREATE OR REPLACE FUNCTION getTail( 
    name_in IN VARCHAR2) 
  RETURN VARCHAR2 
IS 
  tail VARCHAR2(200) :=' '; 
  stripped_tail VARCHAR2(200); 
BEGIN 
  tail := SUBSTR(name_in, LENGTH(name_in)-3); 
  stripped_tail := trim(tail); 
  IF LENGTH(tail)>LENGTH(stripped_tail) THEN 
    tail := stripped_tail; 
  END IF; 
RETURN tail; 
EXCEPTION 
WHEN OTHERS THEN 
  raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM); 
END; 


create or replace PROCEDURE fixStreet 
IS 
  tail VARCHAR2(5); 
  firstname_tmp VARCHAR2(100); 
  surname_tmp VARCHAR2(100); 
   
    CURSOR borrowers_first_cur 
  IS 
    SELECT rowid, 
      firstname, 
      getTail(firstname) gTail, 
      street 
    FROM to_kova_t 
    WHERE LENGTH(firstname)>25; 
   
  CURSOR borrowers_cur 
  IS 
    SELECT rowid, 
      surname, 
      getTail(surname) gTail, 
      street 
    FROM to_kova_t 
    WHERE LENGTH(surname)=49; 
BEGIN 
  FOR borrowers_rec IN borrowers_cur 
  LOOP 
    tail := borrowers_rec.gTail; 
    IF (LENGTH(tail) >= 1) THEN 
      UPDATE to_kova_t SET street=tail || street WHERE rowid=borrowers_rec.rowid; 
      surname_tmp := SUBSTR(borrowers_rec.surname, 1, (LENGTH(borrowers_rec.surname)-LENGTH(tail))); 
      UPDATE to_kova_t SET surname=surname_tmp WHERE rowid=borrowers_rec.rowid; 
      COMMIT; 
    END IF; 
  END LOOP; 
   
  FOR borrowers_first_rec IN borrowers_first_cur 
  LOOP 
    tail := borrowers_first_rec.gTail; 
    IF (LENGTH(tail) >= 1) THEN 
      UPDATE to_kova_t SET street=tail || street WHERE rowid=borrowers_first_rec.rowid; 
      firstname_tmp := trim(SUBSTR(borrowers_first_rec.firstname, 1, (LENGTH(borrowers_first_rec.firstname)-LENGTH(tail)))); 
      UPDATE to_kova_t SET firstname=firstname_tmp WHERE rowid=borrowers_first_rec.rowid; 
      COMMIT; 
    END IF; 
  END LOOP; 
   
EXCEPTION 
WHEN OTHERS THEN 
  raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM); 
END; 

 exec fixStreet; 

17) Creates Stored Procedure(s) that gives a user a category and home-library.

create or replace FUNCTION getHomeLibrary( 
    ssn_in VARCHAR2) 
  RETURN VARCHAR2 
IS 
  v_homelibrary VARCHAR2(20); 
  v_id VARCHAR2(20); 
BEGIN 
  SELECT b.z303_home_library 
  INTO v_homelibrary 
  FROM ssnpid_t a, 
    z303 b 
  WHERE a.z353_id =b.z303_rec_key 
  AND a.ssn =ssn_in; 
  v_homelibrary := trim(v_homelibrary); 
   
  IF ((upper(v_homelibrary)='HEM') OR (upper(v_homelibrary)='HOME')) THEN 
    SELECT z353_id INTO v_id FROM ssnpid_t WHERE ssn=ssn_in; 
    SELECT trim(SUBSTR(z305_rec_key,LENGTH(v_id)+1)) 
    INTO v_homelibrary 
    FROM LTU50.z305 
    WHERE z305_rec_key LIKE ( 
      (SELECT z353_id FROM ssnpid_t WHERE ssn=ssn_in 
      ) 
      || '%') 
    AND z305_bor_type IS NOT NULL; 
  END IF; 
   
  if ((v_homelibrary is null) or (length(v_homelibrary) = 0)) then 
  v_homelibrary := 'LA'; 
  end if; 


    if (v_homelibrary='LTU50') then 
  v_homelibrary := 'LA'; 
  end if; 

RETURN v_homelibrary; 
EXCEPTION 
WHEN OTHERS THEN 
RETURN 'LA'; 
--insert into offer_t values(ssn_in); 
 -- raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM); 
END; 

CREATE OR REPLACE FUNCTION getMainCategory( 
    ssn_in VARCHAR2) 
  RETURN VARCHAR2 
IS 
  v_maincategory VARCHAR2(20); 
BEGIN 
  SELECT z305_bor_status 
  INTO v_maincategory 
  FROM LTU50.z305 
  WHERE trim(z305_rec_key)= 
    (SELECT trim(z353_id 
      || trim(getHomeLibrary(ssn))) 
    FROM ssnpid_t 
    WHERE ssn=ssn_in 
    ); 
  RETURN v_maincategory; 
EXCEPTION 
WHEN OTHERS THEN 
  RETURN '01'; 
  -- raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM || ' ssn=' || ssn_in); 
END; 

CREATE OR REPLACE FUNCTION getCategoryLife( 
    ssn_in VARCHAR2) 
  RETURN VARCHAR2 
IS 
  v_maincategory VARCHAR2(2); 
  v_life VARCHAR2(100); 
BEGIN 
  v_maincategory:=getmainCategory(ssn_in); 
  CASE v_maincategory 
  WHEN '01' THEN 
    v_life := 'DATE_ADD(CURDATE(),INTERVAL 3 YEAR)'; 
  WHEN '02' THEN 
    v_life := 'DATE_ADD(CURDATE(),INTERVAL 3 YEAR)'; 
  WHEN '03' THEN 
    v_life := 'DATE_ADD(CURDATE(),INTERVAL 5 YEAR)'; 
  WHEN '04' THEN 
    v_life := 'DATE_ADD(CURDATE(),INTERVAL 5 YEAR)'; 
  WHEN '05' THEN 
    v_life := 'DATE_ADD(CURDATE(),INTERVAL 3 YEAR)'; 
  WHEN '06' THEN 
    v_life := 'DATE_ADD(CURDATE(),INTERVAL 3 YEAR)'; 
  WHEN '12' THEN 
    v_life := 'DATE_ADD(CURDATE(),INTERVAL 5 YEAR)'; 
  WHEN '13' THEN 
    v_life := 'DATE_ADD(CURDATE(),INTERVAL 1 YEAR)'; 
  WHEN '18' THEN 
    v_life := 'DATE_ADD(CURDATE(),INTERVAL 5 YEAR)'; 
  WHEN '19' THEN 
    v_life := 'DATE_ADD(CURDATE(),INTERVAL 3 YEAR)'; 
  WHEN '20' THEN 
    v_life := 'DATE_ADD(CURDATE(),INTERVAL 10 YEAR)'; 
  WHEN '32' THEN 
    v_life := 'DATE_ADD(CURDATE(),INTERVAL 3 YEAR)'; 
  WHEN '33' THEN 
    v_life := 'DATE_ADD(CURDATE(),INTERVAL 3 YEAR)'; 
  WHEN '34' THEN 
    v_life := 'DATE_ADD(CURDATE(),INTERVAL 3 YEAR)'; 
  WHEN '35' THEN 
    v_life := 'DATE_ADD(CURDATE(),INTERVAL 3 YEAR)'; 
  WHEN '36' THEN 
    v_life := 'DATE_ADD(CURDATE(),INTERVAL 3 YEAR)'; 
  WHEN '84' THEN 
    v_life := 'DATE_ADD(CURDATE(),INTERVAL 1 MONTH)'; 
  WHEN '99' THEN 
    v_life := 'DATE_ADD(CURDATE(),INTERVAL 1 YEAR)'; 
  ELSE 
    v_life := 'DATE_ADD(CURDATE(),INTERVAL 5 YEAR)'; 
  END CASE; 
RETURN v_life; 
EXCEPTION 
WHEN OTHERS THEN 
  raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM); 
END; 

18) Creates dynamic sql.
The result shall be runned in Koha.

drop table to_kova_t_bak; 
create table to_kova_t_bak as select * FROM to_kova_t; 

create or replace function getProblemSSN(in_key IN varchar2) 
   RETURN varchar2 
IS 
   v_out_ssn varchar2(30); 
BEGIN 
select substr(z308_rec_key,3,10) INTO v_out_ssn from z308 where z308_id=in_key and z308_rec_key like '03%'; 
v_out_ssn := trim(v_out_ssn); 
RETURN v_out_ssn; 
EXCEPTION 
WHEN OTHERS THEN 
raise_application_error(20001,'An error was encountered - '||SQLCODE||' -ERROR '||SQLERRM); 
END; 

update to_kova_t set ssn=getProblemSSN(ssn) where ssn like 'PID%'; 
commit; 

Check duplicates:

select ssn, count(ssn) 
from to_kova_t 
group by ssn 
having count (ssn) > 1; 
create or replace FUNCTION isOnlyTwoName( 
    in_name IN VARCHAR2) 
  RETURN VARCHAR2 
IS 
BEGIN 
if regexp_count(in_name,' ') = 1 then 
return '1'; 
else 
return '0'; 
end if; 
EXCEPTION 
WHEN OTHERS THEN 
raise_application_error(20001,'An error was encountered - '||SQLCODE||' -ERROR '||SQLERRM); 
END; 

create or replace FUNCTION getLastname( 
    in_name IN VARCHAR2) 
  RETURN VARCHAR2 
IS 
BEGIN 
return trim(substr(in_name,0,instr(in_name,' '))); 
EXCEPTION 
WHEN OTHERS THEN 
raise_application_error(20001,'An error was encountered - '||SQLCODE||' -ERROR '||SQLERRM); 
END; 

create or replace FUNCTION getFirstname( 
    in_name IN VARCHAR2) 
  RETURN VARCHAR2 
IS 
BEGIN 
return trim(substr(in_name,instr(in_name,' '))); 
EXCEPTION 
WHEN OTHERS THEN 
raise_application_error(20001,'An error was encountered - '||SQLCODE||' -ERROR '||SQLERRM); 
END; 
  
drop table to_kova_t_backup; 
create table to_kova_t_backup as select * from to_kova_t; 

update to_kova_t set firstname=getFirstname(surname), surname=getLastname(surname) where firstname is null and isOnlyTwoName(surname)='1'; 
commit; 

 delete from to_kova_t where SSN='SKETJÖR'; 
  delete from to_kova_t where SSN='FÄRG'; 

DROP TABLE DUPLICATE_T; 
CREATE TABLE DUPLICATE_T AS SELECT SSN,ROWNUM ROWNUMBER, ROWID RADID FROM TO_KOVA_T WHERE SSN IN(SELECT SSN FROM TO_KOVA_T GROUP BY SSN HAVING COUNT (SSN) > 1) ORDER BY SSN; 
DELETE FROM TO_KOVA_T WHERE ROWID IN (SELECT RADID FROM DUPLICATE_T WHERE MOD(ROWNUMBER,2) = 1); 
COMMIT; 

drop table to_koha_insert; 
create table to_koha_insert PARALLEL NOLOGGING as 
SELECT 'insert into koha_ltulibrary.borrowers( cardnumber, surname, firstname, address,address2, city, zipcode, email, phone, branchcode, categorycode, dateenrolled, dateexpiry, password, userid, privacy) values (' 
  || 'substring(' 
  || chr(34) 
  || SSN 
  || chr(34) 
  || ',1,16)' 
  ||',' 
  || chr(34) 
  || SURNAME 
  || chr(34) 
  || ',' 
  || chr(34) 
  || FIRSTNAME 
  || chr(34) 
  ||',' 
  || chr(34) 
  || STREET 
  || chr(34) 
  || ',' 
  || chr(34) 
  || ADDRESS2 
  || chr(34) 
  ||',' 
  || chr(34) 
  || CITY 
  || chr(34) 
  || ',' 
  || 'substring(' 
  || chr(34) 
  || ZIP 
  || chr(34) 
  || ',1,25)' 
  || ',' 
  || chr(34) 
  || MAIL 
  || chr(34) 
  || ',' 
  || chr(34) 
  || PHONE 
  || chr(34) 
  ||',' 
  || chr(34) 
  || getHomeLibrary(SSN) 
  || chr(34) 
  || ',' 
  || chr(34) 
  || getMainCategory(SSN) 
  || chr(34) 
  ||',' 
  || 'CURDATE()' 
  || ',' 
  || getCategoryLife(SSN) 
  || ',' 
  || chr(34) 
  || PIN 
  || chr(34) 
  ||',' 
  || 'substring(' 
  || chr(34) 
  || SSN 
  || chr(34) 
  || ',1,75)' 
  || ',' 
  || chr(34) 
  || '1' 
  || chr(34) 
  || '); commit;' 
 sql_insert FROM to_kova_t; 

Save the result and run it in your Koha mySql.

In Koha run:

delete from koha_ltulibrary.borrower_attributes; 
delete from koha_ltulibrary.issues; 
delete from koha_ltulibrary.borrowers where borrowernumber>1; 

Then the results from above.

19) This steps is necsecary to migrate all extra id fields.

Create a table that shall contain all extra id values.
DROP TABLE extraid_t; 
CREATE TABLE extraid_t 
  ( 
    Z308_ID VARCHAR2(27) NOT NULL, 
    k00 VARCHAR2(40), 
    k01 VARCHAR2(40), 
    k02 VARCHAR2(40), 
    k03 VARCHAR2(40), 
    k04 VARCHAR2(40), 
    k05 VARCHAR2(40), 
    k06 VARCHAR2(40), 
    k77 VARCHAR2(40), 
    kZZ VARCHAR2(40) 
  ); 

Create a support-view => 
CREATE OR REPLACE VIEW extraid_raw_v 
AS 
  SELECT z308_ID, 
    trim(SUBSTR(z308_rec_key,0,2)) id, 
    trim(SUBSTR(z308_rec_key,3,LENGTH(z308_rec_key)-7)) idkey 
  FROM z308 
  ORDER BY z308_ID, 
    id; 

Create a Stored Procedure that extract extra id from table z308 and put them in to table extraid_t

CREATE OR REPLACE PROCEDURE putExtraid( 
    pid_in IN VARCHAR2) 
IS 
  c00_idkey VARCHAR2(40); 
  c01_idkey VARCHAR2(40); 
  c02_idkey VARCHAR2(40); 
  c03_idkey VARCHAR2(40); 
  c04_idkey VARCHAR2(40); 
  c05_idkey VARCHAR2(40); 
  c06_idkey VARCHAR2(40); 
  c77_idkey VARCHAR2(40); 
  cZZ_idkey VARCHAR2(40); 
  CURSOR c00 
  IS 
    SELECT idkey FROM extraid_raw_v WHERE z308_ID=pid_in AND id='00'; 
  CURSOR c01 
  IS 
    SELECT idkey FROM extraid_raw_v WHERE z308_ID=pid_in AND id='01'; 
  CURSOR c02 
  IS 
    SELECT idkey FROM extraid_raw_v WHERE z308_ID=pid_in AND id='02'; 
  CURSOR c03 
  IS 
    SELECT idkey FROM extraid_raw_v WHERE z308_ID=pid_in AND id='03'; 
  CURSOR c04 
  IS 
    SELECT idkey FROM extraid_raw_v WHERE z308_ID=pid_in AND id='04'; 
  CURSOR c05 
  IS 
    SELECT idkey FROM extraid_raw_v WHERE z308_ID=pid_in AND id='05'; 
  CURSOR c06 
  IS 
    SELECT idkey FROM extraid_raw_v WHERE z308_ID=pid_in AND id='06'; 
  CURSOR c77 
  IS 
    SELECT idkey FROM extraid_raw_v WHERE z308_ID=pid_in AND id='77'; 
  CURSOR cZZ 
  IS 
    SELECT idkey FROM extraid_raw_v WHERE z308_ID=pid_in AND id='ZZ'; 
BEGIN 
  OPEN c00; 
  FETCH c00 INTO c00_idkey; 
  IF c00%notfound THEN 
    c00_idkey := NULL; 
  END IF; 
  CLOSE c00; 
  OPEN c01; 
  FETCH c01 INTO c01_idkey; 
  IF c01%notfound THEN 
    c01_idkey := NULL; 
  END IF; 
  CLOSE c01; 
  OPEN c02; 
  FETCH c02 INTO c02_idkey; 
  IF c02%notfound THEN 
    c02_idkey := NULL; 
  END IF; 
  CLOSE c02; 
  OPEN c03; 
  FETCH c03 INTO c03_idkey; 
  IF c03%notfound THEN 
    c03_idkey := NULL; 
  END IF; 
  CLOSE c03; 
  OPEN c04; 
  FETCH c04 INTO c04_idkey; 
  IF c04%notfound THEN 
    c04_idkey := NULL; 
  END IF; 
  CLOSE c04; 
  OPEN c05; 
  FETCH c05 INTO c05_idkey; 
  IF c05%notfound THEN 
    c05_idkey := NULL; 
  END IF; 
  CLOSE c05; 
  OPEN c06; 
  FETCH c06 INTO c06_idkey; 
  IF c06%notfound THEN 
    c06_idkey := NULL; 
  END IF; 
  CLOSE c06; 
  OPEN c77; 
  FETCH c77 INTO c77_idkey; 
  IF c77%notfound THEN 
    c77_idkey := NULL; 
  END IF; 
  CLOSE c77; 
  OPEN cZZ; 
  FETCH cZZ INTO cZZ_idkey; 
  IF cZZ%notfound THEN 
    cZZ_idkey := NULL; 
  END IF; 
  CLOSE cZZ; 
  INSERT 
  INTO extraid_t VALUES 
    ( 
      pid_in, 
      c00_idkey, 
      c01_idkey, 
      c02_idkey, 
      c03_idkey, 
      c04_idkey, 
      c05_idkey, 
      c06_idkey, 
      c77_idkey, 
      cZZ_idkey 
    ); 
  COMMIT; 
EXCEPTION 
WHEN OTHERS THEN 
  raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM); 
END; 

Create a support-view with z308_id =>

CREATE OR REPLACE VIEW z308id_v 
AS 
  SELECT DISTINCT(z308_id) FROM extraid_raw_v; 

Create SQL from SQL =>

SELECT 'exec putExtraid(' 
  || chr(39) 
  || trim(z308_id) 
  || chr(39) 
  || '); commit;' 
FROM z308id_v; 

Run the results from above.
Thoose statemnets are population the table extraid_t

20) In Koha MySql run

select borrowernumber, cardnumber from koha_ltulibrary.borrowers; 

Save values from above in a textfile table_export_DATA.ldr

Create support-table borrow_card_t

DROP TABLE borrow_card_t; 
CREATE TABLE borrow_card_t 
  ( 
    borrowernumber VARCHAR2(20), 
    cardnumber VARCHAR2(20) 
  ); 

Run SQL*Loader for fast load of table BORROW_CARD_T

table_export_DATA.ctl:

OPTIONS (ERRORS=2886) 
LOAD DATA 
INFILE 'table_export_DATA.ldr' "str '{EOL}'" 
APPEND 
CONTINUEIF NEXT(1:1) = '#' 
INTO TABLE "BORROW_CARD_T" 
FIELDS TERMINATED BY'|' 
OPTIONALLY ENCLOSED BY '"' AND '"' 
TRAILING NULLCOLS ( 
borrowernumber CHAR (16), 
cardnumber CHAR (16)) 

Run

sqlldr userid=USR01/*** control=table_export_DATA.ctl log=sqlldr.log 

21) In Aleph USR01 create a help-view bnumber_eid_v

DROP 
  TABLE bnumber_eid_v; 
DROP 
  VIEW bnumber_eid_v; 
CREATE OR REPLACE VIEW bnumber_eid_v 
AS 
  SELECT 
    a.borrowernumber, 
    b.z308_id, 
    b.K00, 
    b.K01, 
    b.K02, 
    b.K03, 
    b.K04, 
    b.k05, 
    b.k06, 
    b.k77, 
    b.kzz 
  FROM 
    borrow_card_t a, 
    extraid_t b 
  WHERE 
    a.CARDNUMBER=b.K03; 
CREATE OR REPLACE VIEW bnumber_eid_v_2 
AS 
  SELECT 
    a.borrowernumber, 
    b.z308_id, 
    b.K00, 
    b.K01, 
    b.K02, 
    b.K03, 
    b.K04, 
    b.k05, 
    b.k06, 
    b.k77, 
    b.kzz 
  FROM 
    borrow_card_t a, 
    extraid_t b 
  WHERE 
    a.CARDNUMBER=b.K00; 
     
  DROP 
    TABLE bnumber_eid_t; 
     
  CREATE 
    TABLE bnumber_eid_t AS 
  SELECT 
    * 
  FROM 
    bnumber_eid_v; 
     
  INSERT 
  INTO 
    bnumber_eid_t 
  SELECT 
    * 
  FROM 
    bnumber_eid_v_2; 
     
  DROP 
    VIEW bnumber_eid_v; 
     
  CREATE 
    TABLE bnumber_eid_v AS 
  SELECT 
    * 
  FROM 
    bnumber_eid_t; 
   
    drop table support_borrower_pid_t; 
    create table support_borrower_pid_t as select borrowernumber, z308_id pid from bnumber_eid_v; 

22) Create a support-table

DROP TABLE extraid_komp_t; 
CREATE TABLE extraid_komp_t 
  ( 
    borrowernumber VARCHAR2(30), 
    kod CHAR(2), 
    kodnyckel VARCHAR2(30) 
  ); 
  

23) Create a Stored Procedure that extract data from extraid_t and populate table extraid_komp_t

CREATE OR REPLACE PROCEDURE fillExtraIdKomp( 
    borrowernumber_in IN VARCHAR2) 
IS 
  k00_p bnumber_eid_v.k00%TYPE; 
  k01_p bnumber_eid_v.k01%TYPE; 
  k02_p bnumber_eid_v.k02%TYPE; 
  k03_p bnumber_eid_v.k03%TYPE; 
  k04_p bnumber_eid_v.k04%TYPE; 
  k05_p bnumber_eid_v.k05%TYPE; 
  k06_p bnumber_eid_v.k06%TYPE; 
  k77_p bnumber_eid_v.k77%TYPE; 
  kZZ_p bnumber_eid_v.kZZ%TYPE; 
BEGIN 
  SELECT k00 
  INTO k00_p 
  FROM bnumber_eid_v 
  WHERE borrowernumber=borrowernumber_in; 
  SELECT k01 
  INTO k01_p 
  FROM bnumber_eid_v 
  WHERE borrowernumber=borrowernumber_in; 
  SELECT k02 
  INTO k02_p 
  FROM bnumber_eid_v 
  WHERE borrowernumber=borrowernumber_in; 
  SELECT k03 
  INTO k03_p 
  FROM bnumber_eid_v 
  WHERE borrowernumber=borrowernumber_in; 
  SELECT k04 
  INTO k04_p 
  FROM bnumber_eid_v 
  WHERE borrowernumber=borrowernumber_in; 
  SELECT k05 
  INTO k05_p 
  FROM bnumber_eid_v 
  WHERE borrowernumber=borrowernumber_in; 
  SELECT k06 
  INTO k06_p 
  FROM bnumber_eid_v 
  WHERE borrowernumber=borrowernumber_in; 
  SELECT k77 
  INTO k77_p 
  FROM bnumber_eid_v 
  WHERE borrowernumber=borrowernumber_in; 
  SELECT kZZ 
  INTO kZZ_p 
  FROM bnumber_eid_v 
  WHERE borrowernumber=borrowernumber_in; 
  INSERT INTO extraid_komp_t VALUES 
    (borrowernumber_in,'00',k00_p 
    ); 
  COMMIT; 
  INSERT INTO extraid_komp_t VALUES 
    (borrowernumber_in,'01',k01_p 
    ); 
  COMMIT; 
  INSERT INTO extraid_komp_t VALUES 
    (borrowernumber_in,'02',k02_p 
    ); 
  COMMIT; 
  INSERT INTO extraid_komp_t VALUES 
    (borrowernumber_in,'03',k03_p 
    ); 
  COMMIT; 
  INSERT INTO extraid_komp_t VALUES 
    (borrowernumber_in,'04',k04_p 
    ); 
  COMMIT; 
  INSERT INTO extraid_komp_t VALUES 
    (borrowernumber_in,'05',k05_p 
    ); 
  COMMIT; 
  INSERT INTO extraid_komp_t VALUES 
    (borrowernumber_in,'06',k06_p 
    ); 
  COMMIT; 
  INSERT INTO extraid_komp_t VALUES 
    (borrowernumber_in,'77',k77_p 
    ); 
  COMMIT; 
  INSERT INTO extraid_komp_t VALUES 
    (borrowernumber_in,'ZZ',kZZ_p 
    ); 
  COMMIT; 
EXCEPTION 
WHEN OTHERS THEN 
  raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM); 
END; 
  

24) Creates dynamic SQL

SELECT 'exec fillExtraIdKomp(' 
  || chr(39) 
  || borrowernumber 
  || chr(39) 
  || '); commit;' 
FROM bnumber_eid_v; 

25) Run the select above and also run the result from 24)

26) Creates mySQL SQL from Oracle SQL

SELECT 'insert into koha_ltulibrary.borrower_attributes values(' 
  || chr(39) 
  || borrowernumber 
  || chr(39) 
  || ',' 
  || chr(39) 
  || kod 
  || chr(39) 
  || ',' 
  || chr(39) 
  || kodnyckel 
  || chr(39) 
  || ',' 
  || chr(39) 
  || chr(39) 
  ||');' 
FROM extraid_komp_t 
WHERE kod NOT IN ('77','ZZ') 
AND kodnyckel IS NOT NULL; 

27) Take the result from 26) and run it in MySQL Koha.

29) D O N E

Comment by LTU [ 2016-01-19 ]

STEP 2.2.1 Migrate the issue history from Aleph to Koha using SQL. (approx. 1h)

1. Export the fields itemnumber, biblionumber, biblioitemnumber, barcode from items to ALEPH ZZZ50 table TMP_BARCODE_ITEMNUMBER

2. Run SQL

NB. The second column having value "1" in this SQL command indicates the anonymous patron record in Koha. If you have another anonymous patron record change this value accordingly.

select row_number() over (order by itemnumber) +900000000 as issue_id, '1' as BORROWERNUMBER, ITEMNUMBER, 
case when z36h_due_date=0 then '' else substr(z36h_due_date,0,4) || '-' || substr(z36h_due_date,5,2) || '-' || substr(z36h_due_date,7,2) end as DATE_DUE, 
trim(z36h_sub_library) AS BRANCHCODE,  
case when z36h_returned_date=0 then '' else substr(z36h_returned_date,0,4) || '-' || substr(z36h_returned_date,5,2) || '-' || substr(z36h_returned_date,7,2) end as returndate, 
case when z36h_last_renew_date=0 then '' else substr(z36h_last_renew_date,0,4) || '-' || substr(z36h_last_renew_date,5,2) || '-' || substr(z36h_last_renew_date,7,2) end  as lastrenewdate, '' as return, z36h_no_renewal as renewals, '' as auto_renew, 
case when z36h_time=0 then '' else substr(z36h_time,0,4) || '-' || substr(z36h_time,5,2) || '-' || substr(z36h_time,7,2) || ' ' || substr(z36h_time,9,2) || ':' || substr(z36h_time,11,2) || ':' || substr(z36h_time,13,2) end as timestamp, 
case when z36h_loan_date=0 then '' else substr(z36h_loan_date,0,4) || '-' || substr(z36h_loan_date,5,2) || '-' || substr(z36h_loan_date,7,2)  end as issuedate, '0' as onsite_checkout
from z36h
left outer join z30 on z36h_rec_key=z30_rec_key
left outer join TMP_BARCODE_ITEMNUMBER on trim(z30_barcode)=trim(BARCODE)
where 
z30_barcode is not null and
ITEMNUMBER is not null
order by itemnumber

3. Export to a CSV file and upload on the server on path /var/tmp/

4. Import the CSV file into Koha table "old_issues" with the following command

LOAD DATA INFILE '/var/tmp/export.lonehistory2.20151212.csv'
INTO TABLE old_issues
CHARACTER SET UTF8
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
(issue_id, borrowernumber, itemnumber, date_due, branchcode, returndate, lastreneweddate, `return`, renewals, auto_renew, timestamp, issuedate, onsite_checkout)

Comment by LTU [ 2016-01-19 ]

STEP 2.2.2 Add access rights for librarian users to Koha.

Comment by LTU [ 2016-01-19 ]

STEP 3.1 Migrate active loans from Aleph to Koha using SQL.

1) In Koha create a support-view showing relations between barcode and itemnumber.

drop view koha_ltulibrary.item_barcode_v;
CREATE VIEW koha_ltulibrary.item_barcode_v AS SELECT itemnumber, barcode FROM koha_ltulibrary.items;

2) Export the result from Koha

select * from koha_ltulibrary.item_barcode_v;

in to a support-table in Aleph/LTU50.

3) In Aleph/LTU50 make:

drop table item_barcode_t;
create table item_barcode_t(itemnumber int, barcode varchar2(20));

4) Importing data from 2) from koha_ltulibrary.item_barcode_v.

Fastest way to load is with Oracle SQL*Loader.

Create a ctl-fil, and a data-fil.
item_barcode_t.ctl =>

OPTIONS (ERRORS=29717)
LOAD DATA 
INFILE 'table_export_DATA.ldr' "str '{EOL}'"
APPEND
CONTINUEIF NEXT(1:1) = '#'
INTO TABLE "ITEM_BARCODE_T"
FIELDS TERMINATED BY'|'
OPTIONALLY ENCLOSED BY '"' AND '"'
TRAILING NULLCOLS ( 
itemnumber ,
barcode CHAR (20))

table_export_DATA.ldr =>

7733|"§707039168857"|{EOL}
3249553|"§70704490551D"|
{EOL} 3343546|"+B7072001405DF"|{EOL}
... ... ...

5)
At Aleph machine run:

sqlldr userid=LTU50/*** control=table_export_DATA.ctl log=sqlldr.log

6) Create a Stored Function that handle date dateformat and null-date.

CREATE OR REPLACE FUNCTION getNullDate(
    in_date IN VARCHAR2)
  RETURN VARCHAR2
IS
  v_date VARCHAR2(50);
BEGIN
  IF in_date IS NULL THEN
    v_date   :='null';
  ELSE
    v_date:=chr(39) || trim(in_date) || chr(39);
  END IF;
RETURN v_date;
EXCEPTION
WHEN OTHERS THEN
  RETURN NULL;
END;

create or replace FUNCTION getMySQLDate(
    in_date IN VARCHAR2)
  RETURN VARCHAR2
IS
  v_date VARCHAR2(50);
BEGIN
  IF in_date='0' THEN
    v_date :=NULL;
  ELSE
    IF (LENGTH(in_date)=15) THEN
 --     v_date          := trim(SUBSTR(in_date,1,4) || '-' || SUBSTR(in_date,5,2) || '-' || SUBSTR(in_date,7,2) || ' ' || SUBSTR(in_date,9,2) || ':' || SUBSTR(in_date,11,2) || ':' || SUBSTR(in_date,13,2) || '.' || SUBSTR(in_date,15,1));
   v_date          := trim(SUBSTR(in_date,1,4) || '-' || SUBSTR(in_date,5,2) || '-' || SUBSTR(in_date,7,2) || ' ' || SUBSTR(in_date,9,2) || ':' || SUBSTR(in_date,11,2));

    ELSE
  --    v_date:=trim(TO_CHAR(to_date(in_date),'YYYY-MM-DD HH24:MI:SS')) || '.0';
  v_date:=trim(TO_CHAR(to_date(in_date),'YYYY-MM-DD HH24:MI'));
    END IF;
  END IF;
RETURN v_date;
EXCEPTION
WHEN OTHERS THEN
  RETURN NULL;
END;

7) Create a helper-view:

CREATE OR REPLACE VIEW item_reckey_v
AS
  SELECT a.itemnumber,
    a.barcode,
    b.z30_rec_key
  FROM item_barcode_t a,
    z30 b
  WHERE trim(a.barcode)=trim(b.z30_barcode);

8) In USR01 run:

GRANT SELECT ON ssnpid_t TO LTU50;
GRANT SELECT ON extraid_komp_t TO LTU50;

In LTU50:
Create a helper-view that is a mapping to issues in Koha:

CREATE OR REPLACE VIEW kova_loans_v
AS
  SELECT trim(b.z36_id) pid,
    trim(usr01.extraid_komp_t.borrowernumber) borrowernumber,
    trim(a.itemnumber) itemnumber,
    getMySQLDate(b.z36_due_date) date_due,
    trim(b.z36_sub_library) branchcode,
    getMySQLDate(b.z36_last_renew_date) lastreneweddate,
    trim(b.z36_no_renewal) renewals,
    getMySQLDate(b.z36_upd_time_stamp) issuedate
  FROM item_reckey_v a,
    z36 b,
    usr01.ssnpid_t,
    usr01.extraid_komp_t
  WHERE a.z30_rec_key=b.z36_rec_key
  AND b.z36_id       =usr01.ssnpid_t.z353_id
  AND b.z36_id       =usr01.extraid_komp_t.kodnyckel
  AND b.z36_item_status <> 11;

9) Create a main-view:

CREATE OR REPLACE VIEW aleph_to_koha_loan_v
AS
  SELECT 'insert into koha_ltulibrary.issues (borrowernumber,itemnumber,date_due,branchcode,returndate,lastreneweddate,' || chr(96) || 'return'|| chr(96) || ',renewals,'|| chr(96) || 'timestamp'|| chr(96) || ',issuedate,onsite_checkout) values ('
    || borrowernumber
    || ','
    || itemnumber
    || ','
    || chr(39)
    || date_due
    || chr(39)
    || ','
    || chr(39)
    || branchcode
    || chr(39)
    ||',null,'
    || getNullDate(lastreneweddate)
    || ',null,'
    || renewals
    || ',now(),'
    || chr(39)
    || issuedate
    || chr(39)
    || ',0);' sql_ins
  FROM kova_loans_v;
  

10) Create SQL from SQL:

select * from aleph_to_koha_loan_v;
  

11) Take the result from 10) above and execute it in Koha.

Comment by LTU [ 2016-01-19 ]

STEP 3.2 Migrate active reservations from Aleph to Koha using SQL.

1) In Aleph/LTU50 create a support-table containing borrowernumber and PID

drop table borrower_pid_t;
create table borrower_pid_t(borrowernumber varchar2(20), z37_id varchar2(20));

2) In Koha run

SELECT borrowernumber, attribute FROM koha_ltulibrary.borrower_attributes where code='00';

Export the result and fill the table borrower_pid_t

Quickest way to load is with Oralce SQL*Loader.

Create a ctl-file and a data-file.

item_barcode_t.ctl =>

OPTIONS (ERRORS=5773)
LOAD DATA 
INFILE 'table_export_DATA.ldr' "str '{EOL}'"
APPEND
CONTINUEIF NEXT(1:1) = '#'
INTO TABLE "BORROWER_PID_T"
FIELDS TERMINATED BY'|'
OPTIONALLY ENCLOSED BY '"' AND '"'
TRAILING NULLCOLS ( 
borrowernumber  CHAR (20),
z37_id CHAR (20))

In the Aleph machine run:

sqlldr userid=LTU50/*** control=table_export_DATA.ctl log=sqlldr.log

3) Create some support-tables:

DROP TABLE reserv_b_t;
CREATE TABLE reserv_b_t AS
SELECT b.bibid,
  a.z37_rec_key,
  a.z37_id
FROM z37 a,
  TMP_BIBID_USED b
WHERE trim(a.z37_rec_key) LIKE (trim(b.z30_rec_key)
  || '%');
  
DROP TABLE bibnum_reckey_t;
CREATE TABLE bibnum_reckey_t AS
SELECT a.bibid,
  a.z37_rec_key,
  a.z37_id,
  b.biblionumber
FROM reserv_b_t a,
  tmp_koha_bibid b
WHERE a.bibid=b.bibid;

4) Create a Stored Procedure that handle hem, home problems:

create or replace function getbastardLibrary(
    pid_in VARCHAR2)
      RETURN VARCHAR2
IS
  v_homelibrary VARCHAR2(20);
  v_id          VARCHAR2(20);
  v_fail         VARCHAR2(20);
  v_howmany     NUMBER;
  v_twins       NUMBER;
BEGIN
  SELECT COUNT(*)
  INTO v_howmany
  FROM LTU50.z305
  WHERE trim(z305_rec_key) LIKE trim(pid_in
    || '%')
  AND z305_bor_status <>'05';
  
  SELECT COUNT(*)
  INTO v_twins
  FROM LTU50.z305
  WHERE trim(z305_rec_key) LIKE trim(pid_in
    || '%');

IF v_howmany >1 THEN
    SELECT z305_rec_key 
    INTO v_homelibrary
    FROM LTU50.z305
    WHERE trim(z305_rec_key) LIKE trim(pid_in
      || '%')
    AND z305_bor_status <>'05'
    AND z305_rec_key NOT LIKE '%LTU50%'
    AND z305_bor_status =
      (SELECT MAX(z305_bor_status)
      FROM LTU50.z305
      WHERE trim(z305_rec_key) LIKE trim(pid_in
        || '%')
      AND z305_bor_status <>'05'
      AND z305_rec_key NOT LIKE '%LTU50%'
      )
    AND LENGTH(trim(z305_rec_key)) =
      (SELECT MAX(LENGTH(trim(z305_rec_key)))
      FROM LTU50.z305
      WHERE trim(z305_rec_key) LIKE trim(pid_in
        || '%')
      AND z305_bor_status <>'05'
      AND z305_rec_key NOT LIKE '%LTU50%'
      );

  ELSE
  
    IF v_twins>0 THEN
      SELECT z305_rec_key
      INTO v_homelibrary
      FROM LTU50.z305
      WHERE trim(z305_rec_key) LIKE trim(pid_in
        || '%')
      AND z305_rec_key NOT LIKE '%LTU50%';
    ELSE
      SELECT z305_rec_key
      INTO v_homelibrary
      FROM LTU50.z305
      WHERE trim(z305_rec_key) LIKE trim(pid_in
        || '%')
      AND z305_bor_status <>'05';
    END IF;
    
  END IF;
  

v_homelibrary               := trim(v_homelibrary);

IF (instr(v_homelibrary,'LA')>1) THEN
  v_id                      := 'LA';
END IF;
IF (instr(v_homelibrary,'SUND')>1) THEN
  v_id                        := 'SUND';
END IF;
IF (instr(v_homelibrary,'LAM')>1) THEN
  v_id                       := 'LAM';
END IF;
IF (instr(v_homelibrary,'LAT')>1) THEN
  v_id                       := 'LAT';
END IF;

return trim(v_id);
EXCEPTION
 WHEN NO_DATA_FOUND THEN
     SELECT trim(substr(trim(z305_rec_key),length(trim(z305_rec_key))-2))
     into v_fail
    FROM LTU50.z305
    WHERE trim(z305_rec_key) LIKE trim(pid_in
      || '%')
    AND z305_bor_status <>'05'
    AND z305_rec_key NOT LIKE '%LTU50%'
    and z305_bor_status=(select min(z305_bor_status) from LTU50.z305 WHERE trim(z305_rec_key) LIKE trim(pid_in || '%'));
    return trim(v_fail);
WHEN OTHERS THEN
  raise_application_error(-20001,pid_in || ' :An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END;

4) In Aleph/LTU50 create a support-table containing itemnumber, biblionumber, homebranch and holdingbranch

drop table item_biblio_t;
create table item_biblio_t(itemnumber varchar2(11), biblionumber varchar2(11), homebranch varchar2(10), holdingbranch varchar2(10));

4) In Koha run:

select itemnumber, biblionumber, homebranch, holdingbranch from items; 

Export the resul.

5) Fastest way is to use Oracle SQL*Loader.
Create a ctl-file:

OPTIONS (ERRORS=29992)
LOAD DATA
INFILE 'table_export_DATA.ldr' "str '{EOL}'"
APPEND
CONTINUEIF NEXT(1:1) = '#'
INTO TABLE "ITEM_BIBLIO_T"
FIELDS TERMINATED BY'|'
OPTIONALLY ENCLOSED BY '"' AND '"'
TRAILING NULLCOLS (
itemnumber CHAR(11),
biblionumber CHAR(11),
homebranch CHAR (10),
holdingbranch CHAR (13))

DATA-filen:

 3364197|52|"LA"|"LA"|{EOL} 3209961|53|"LA"|"LA"|{EOL} 3394937|53|"LA"|"LA"|{EOL} 
 ... ... ...
 

6) Move/copy/ftp the files table_export_DATA.ctl and table_export_DATA.ldr to the Aleph-server

7) Run at Aleph-machine:

sqlldr userid=LTU50/*** control=table_export_DATA.ctl log=sqlldr.log

8) Create support Stored Functions:

CREATE OR REPLACE FUNCTION getItemNumber(
    in_biblionumber IN VARCHAR2,
    in_biblo        IN VARCHAR2)
  RETURN VARCHAR2
IS
  v_itemnumber VARCHAR2(11);
BEGIN
  SELECT itemnumber
  INTO v_itemnumber
  FROM item_biblio_t
  WHERE biblionumber=in_biblionumber
  AND homebranch    =in_biblo;
  RETURN v_itemnumber;
EXCEPTION
WHEN OTHERS THEN
  RETURN '0';
END;

9) Create a support-view and a support-table:

CREATE OR REPLACE VIEW item_v
AS
  SELECT c.z37_id,
    trim(c.borrowernumber) borrowernumber,
    trim(TO_CHAR(to_date(a.z37_open_date),'YYYY-MM-DD')) reservedate,
    trim(b.biblionumber) biblionumber ,
    trim(lower(a.z37_status)) constrainttype,
    trim(a.z37_pickup_location) branchcode,
    '1' priority,
    trim(TO_CHAR(sysdate,'YYYY-MM-DD')) TIMESTAMP,
    1 lowestpriority
  FROM z37 a,
    bibnum_reckey_t b,
    borrower_pid_t c
  WHERE trim(a.z37_rec_key)=trim(b.z37_rec_key)
  AND b.Z37_ID             = c.z37_id;
  
DROP TABLE reserves_t;
CREATE TABLE reserves_t AS
SELECT (a.borrowernumber ||a.biblionumber) ids,  
a.z37_id,
a.borrowernumber,
a.reservedate,
a.biblionumber,
a.constrainttype,
a.branchcode,
a.priority,
a.TIMESTAMP,
b.itemnumber,
a.lowestpriority
FROM item_v a,
item_biblio_t b
WHERE a.biblionumber= b.biblionumber
AND a.branchcode =b.HOLDINGBRANCH;

update reserves_t set itemnumber='';
commit;

Run this UNTIL no duplicates are found IMPORTANT.

DROP TABLE DUPLICATE_T; 
CREATE TABLE DUPLICATE_T AS SELECT ids,ROWNUM ROWNUMBER, ROWID RADID FROM reserves_t WHERE ids IN(SELECT ids FROM reserves_t GROUP BY ids HAVING COUNT (ids) > 1) ORDER BY ids; 
DELETE FROM reserves_t WHERE ROWID IN (SELECT RADID FROM DUPLICATE_T WHERE MOD(ROWNUMBER,2) = 1); COMMIT;
 
 
  UPDATE reserves_t
  SET branchcode    =getbastardLibrary(z37_id)
  WHERE branchcode IN('HEM','HOME');
commit;

10) At Aleph/USR01 run:

GRANT EXECUTE ON  USR01.getHomeLibrary TO LTU50; 

10) Create the main-view which has MySQL insert-rows:

CREATE OR REPLACE VIEW insert_reserves_v
AS
  SELECT 'insert into koha_ltulibrary.reserves(borrowernumber,reservedate,biblionumber,branchcode,reservenotes,priority,'
    || chr(96)
    || 'timestamp'
    || chr(96)
    || ',lowestpriority) values ('
    || borrowernumber
    || ','
    || chr(39)
    || reservedate
    || chr(39)
    || ','
    || biblionumber
    || ','
    || chr(39)
    || branchcode
    || chr(39)
    || ','
    || chr(39)
    || chr(39)
    || ','
    || chr(39)
    || priority
    || chr(39)
    || ','
    || chr(39)
    || TIMESTAMP
    || chr(39)
    || ','
    || lowestpriority
    || ');' ins_sql
  FROM reserves_t;

11) Run in Aleph:

select * from insert_reserves_v;

Copy the result and paste it into Koha MySQl.

12) Run the result from 11) above in Koha.

13) D O N E

Comment by LTU [ 2016-01-19 ]

STEP 3.3 Migrate patron debts from Aleph to Koha using SQL.

1) In Koha MySQL run:

select itemnumber, barcode from koha_ltulibrary.items; 

In Aleph/LTU50 create a helper-table: item_barcode_t

DROP TABLE item_barcode_t;
CREATE TABLE item_barcode_t
  (
    itemnumber VARCHAR2(7),
    barcode     VARCHAR2(21)
  );

Import the values from the select-statement with Oracle SQL*Loader:

table_export_DATA.ctl:

OPTIONS (ERRORS=2886)
LOAD DATA 
INFILE 'table_export_DATA.ldr' "str '{EOL}'"
APPEND
CONTINUEIF NEXT(1:1) = '#'
INTO TABLE "ITEM_BARCODE_T"
FIELDS TERMINATED BY'|'
OPTIONALLY ENCLOSED BY '"' AND '"'
TRAILING NULLCOLS ( 
itemnumber  CHAR (7),
barcode CHAR (21))

At the Aleph machine run:

sqlldr userid=LTU50/*** control=table_export_DATA.ctl log=sqlldr.log

2) In Koha run:

select borrowernumber, cardnumber from  koha_ltulibrary.borrowers;

Import the values from the select-statement with Oracle SQL*Loader.

Create a suport-table in Aleph bnum_cnum_t

drop  table bnum_cnum_t;
create table bnum_cnum_t(borrowernumber varchar2(7), cardnumber varchar2(16));

Put the values from the select-statement into the table bnum_cnum_t with oracle SQL*Loader:

table_export_DATA.ctl:

OPTIONS (ERRORS=2886)
LOAD DATA 
INFILE 'table_export_DATA.ldr' "str '{EOL}'"
APPEND
CONTINUEIF NEXT(1:1) = '#'
INTO TABLE "BNUM_CNUM_T"
FIELDS TERMINATED BY'|'
OPTIONALLY ENCLOSED BY '"' AND '"'
TRAILING NULLCOLS ( 
borrowernumber  CHAR (7),
cardnumber CHAR (16))

At the Aleph machine run:

sqlldr userid=LTU50/LTU50 control=table_export_DATA.ctl log=sqlldr.log

3) In Aleph LTU50 run:

drop table pnr_pid_t;
create table pnr_pid_t as 
select trim(substr(z308_rec_key,3,10)) pnr, z308_id from  usr01.z308 where substr(z308_rec_key,1,2)='03';

4) In Aleph LTU50 run:

drop table support_borrower_pid_t;
create table support_borrower_pid_t as
select a.borrowernumber, b.z308_id
from bnum_cnum_t a, pnr_pid_t b
where
a.cardnumber=b.pnr;

5) In Aleph LTU50 run:

drop table exemplar_t;
create table exemplar_t as
select z30_rec_key, z30_barcode, z13_author || ' (' || z13_year || ') ' || ' ' || z13_title AS TITLE
from LTU01.z13
left outer join LTU01.z103 on substr(z103_rec_key_1,6,9) = substr(z13_rec_key, 1, 9)
left outer join LTU50.z30 on substr(z103_rec_key,6,9) = substr(z30_rec_key, 1, 9)
left outer join LTU50.z31 on z30_rec_key = z31_rec_key
where
Z103_LKR_LIBRARY='LTU50' and
Z103_LKR_TYPE='ADM';
create or replace view alpeh_skuld_v as
select substr(z31_rec_key,1,12) PID, z31_type,z31_sum, z31_description, substr(z31_key,1,15) barcode, z31_date_x datum from z31 where z31_status='O' and z31_credit_debit='D';
create or replace view aleph2kova_skuld_v as
select a.borrowernumber, b.pid, b.z31_type, b.z31_sum, b.z31_description, b.barcode, b.datum
from alpeh_skuld_v b, support_borrower_pid_t a
where b.PID = a.z308_id;
create or replace view skuld_v as
select a.borrowernumber, a.z31_type, a.z31_sum, a.z31_description, a.barcode, b.title, a.datum
from aleph2kova_skuld_v a, exemplar_t b
where b.Z30_rec_key=a.BARCODE;

6) In Aleph LTU50 run:

create or replace FUNCTION getAccountType(
    in_z31type IN VARCHAR2)
  RETURN VARCHAR2
IS
  v_type char(1);
BEGIN
v_type := 'M';
  IF (in_z31type = '40') THEN
    v_type :='A';
  END IF;
    IF (in_z31type = '41') THEN
    v_type := 'L';
  END IF;
      IF (in_z31type = '80') THEN
    v_type := 'M';
  END IF;
      IF (in_z31type = '81') THEN
    v_type := 'M';
  END IF;
  return v_type;
END getAccountType;
CREATE OR REPLACE FUNCTION getAmount(
  in_z31sum IN VARCHAR2)
RETURN VARCHAR2
IS
  p_sum VARCHAR2(20);
BEGIN
  p_sum := nvl(trim(SUBSTR(ltrim(in_z31sum,'0'),1,LENGTH(ltrim(in_z31sum,'0'))-2)),'0');
  RETURN p_sum;
END getAmount;
CREATE OR REPLACE FUNCTION getBarcode(in_z30_rec_key IN VARCHAR2)
RETURN VARCHAR2
IS
  v_barcode VARCHAR2(50);
BEGIN
  SELECT z30_barcode
  INTO v_barcode
  FROM exemplar_t
  where Z30_rec_key=in_z30_rec_key;
  
  v_barcode               := trim(v_barcode);
  return v_barcode;
END getBarcode;
CREATE OR REPLACE FUNCTION getItemnumber(in_barcode IN VARCHAR2)
RETURN VARCHAR2
IS
  v_itemnumber VARCHAR2(50);
BEGIN
  SELECT itemnumber
  INTO v_itemnumber
  FROM ITEM_BARCODE_T
  where barcode=in_barcode;
  
  v_itemnumber               := trim(v_itemnumber);
  return v_itemnumber;
END getItemnumber;

7) In Aleph LTU50 run:

create or replace view skuld_v2 as
select borrowernumber, '2' accountno, getBarcode(barcode) itemnumber, datum, getAmount(z31_sum) amount, title description, null dispute, getAccountType(z31_type) accounttype, getAmount(z31_sum) amountoutstanding,
null lastincrement, 1 notify_id, 0 notify_level, z31_description note, borrowernumber manager_id from skuld_v;

– WITH itemnumber

create or replace view  skuld_insert_v as
select 'insert into koha_ltulibrary.accountlines (borrowernumber,accountno,itemnumber,date,amount,description,dispute,accounttype,amountoutstanding,lastincrement,notify_id,notify_level,note,manager_id) values(' || borrowernumber || ',' || chr(39) || accountno || chr(39) || ','|| nvl(getItemnumber(itemnumber),'null') ||',' || chr(39) || datum || chr(39) || ',' || amount || ',' || chr(39) || replace(trim(description),'''','''''')|| chr(39) || ',null,' || chr(39) || accounttype || chr(39) || ',' || amountoutstanding || ',null,' || notify_id || ',' || notify_level || ',' || chr(39) ||note || chr(39) || ',' || manager_id || ');' ins_sql from skuld_v2

– WITHOUT itemnumber

create or replace view skuld_insert_prim_v as
select 'insert into koha_ltulibrary.accountlines (borrowernumber,accountno,itemnumber,date,amount,description,dispute,accounttype,amountoutstanding,lastincrement,notify_id,notify_level,note,manager_id) values(' || borrowernumber || ',' || chr(39) || accountno || chr(39) || ',null,' || chr(39) || datum || chr(39) || ',' || amount || ',' || chr(39) || replace(trim(description),'''','''''')|| chr(39) || ',null,' || chr(39) || accounttype || chr(39) || ',' || amountoutstanding || ',null,' || notify_id || ',' || notify_level || ',' || chr(39) ||note || chr(39) || ',' || manager_id || ');' ins_sql from skuld_v2;

8) In Koha run:

delete from `koha_ltulibrary`.`accountlines` where accountlines_id>1

9) In Aleph LTU50 run:

select * from skuld_insert_v;

Save the result from above and run it in Koha database.

10) D O N E

Generated at Thu Jan 21 14:24:52 CET 2016 using JIRA 6.4.11#64026-sha1:78f6ec473a3f058bd5d6c30e9319c7ab376bdb9c.