From 9088f292cbdc4184ef87431271a75e8554a03d59 Mon Sep 17 00:00:00 2001 From: miker Date: Sat, 29 Jul 2006 18:44:07 +0000 Subject: [PATCH] import bits git-svn-id: svn://svn.open-ils.org/ILS/trunk@5167 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- .../extras/import/barcode_lookup_loader.pl | 46 +++++++++ Evergreen/src/extras/import/legacy_circ.sql | 95 +++++++++++++++++++ Evergreen/src/extras/import/load_legacy.sql | 3 +- .../src/extras/import/non-real_user_loader.pl | 18 ++++ .../src/extras/import/parse_patron_xml.pl | 2 +- .../src/extras/import/recirc_user_loader.pl | 18 ++++ 6 files changed, 179 insertions(+), 3 deletions(-) create mode 100644 Evergreen/src/extras/import/barcode_lookup_loader.pl create mode 100644 Evergreen/src/extras/import/legacy_circ.sql create mode 100755 Evergreen/src/extras/import/non-real_user_loader.pl create mode 100755 Evergreen/src/extras/import/recirc_user_loader.pl diff --git a/Evergreen/src/extras/import/barcode_lookup_loader.pl b/Evergreen/src/extras/import/barcode_lookup_loader.pl new file mode 100644 index 0000000000..9e13009170 --- /dev/null +++ b/Evergreen/src/extras/import/barcode_lookup_loader.pl @@ -0,0 +1,46 @@ +#!/usr/bin/perl + +use Getopt::Long; + +my ($usermap,$nonusers,$recirc) = (); + +GetOptions( + 'usermap=s' => \$usermap, + 'nonusers=s' => \$nonusers, + 'recirc=s' => \$recirc, +); + +my %u_map; +open F, $usermap; +while (my $line = ) { + chomp($line); + my ($b,$i) = split(/\|/, $line); + $b =~ s/^\s*(\S+)\s*$/$1/o; + $i =~ s/^\s*(\S+)\s*$/$1/o; + $u_map{$b} = $i; +} +close F; + +print "CREATE TABLE legacy_baduser_map ( barcode text, id int, type text);\n"; +print "COPY legacy_baduser_map FROM STDIN;\n"; + +open F, $nonusers; +while () { + chomp; + my ($p,$l,$b) = split '\|'; + next unless ($u_map{$b}); + print "$b\t$u_map{$b}\tN\n"; +} +close F; + +open F, $recirc; +while () { + chomp; + my ($b) = split '\|'; + next unless ($u_map{$b}); + print "$b\t$u_map{$b}\tR\n"; +} +close F; + +print "\\.\n"; + diff --git a/Evergreen/src/extras/import/legacy_circ.sql b/Evergreen/src/extras/import/legacy_circ.sql new file mode 100644 index 0000000000..d38e6874f8 --- /dev/null +++ b/Evergreen/src/extras/import/legacy_circ.sql @@ -0,0 +1,95 @@ +BEGIN; + +-- First, we extract the real circs for users that we know about +CREATE TABLE legacy_real_circ (usr int, item int, start_date date, due_date date, item_type text, circ_lib int, claim_return_date date) AS + SELECT DISTINCT ON (lc.charge_key1, lc.charge_key2, lc.charge_key3) + au.id AS usr, + cp.id AS item, + CASE WHEN lc.renewal_date = 'NEVER' + THEN lc.charge_date::DATE + ELSE lc.renewal_date::DATE + END AS start_date, + CASE WHEN lc.due_date = 'NEVER' + THEN (now() + '20 years')::DATE + ELSE lc.due_date::DATE + END AS due_date, + li.item_type, + ou.id AS circ_lib, + CASE WHEN lc.claim_return_date = '0' + THEN NULL + ELSE lc.claim_return_date::DATE + END AS claim_return_date + FROM legacy_charge lc + JOIN joined_legacy li + ON ( lc.charge_key1 = li.cat_key + AND lc.charge_key2 = li.call_key + AND lc.charge_key3 = li.item_key ) + JOIN asset.copy cp ON (cp.barcode = li.item_id) + JOIN actor.org_unit ou ON (lc.library = ou.shortname) + JOIN actor.usr au ON (lc.user_key = au.id) + LEFT JOIN legacy_baduser_map bu ON (bu.id = lc.user_key) + WHERE bu.id IS NULL + ORDER BY + lc.charge_key1, + lc.charge_key2, + lc.charge_key3, + lc.charge_key4 DESC; + + +-- Now build a table containing the status change info ... +CREATE TABLE legacy_status_change_circ AS + SELECT DISTINCT ON (lc.charge_key1, lc.charge_key2, lc.charge_key3) + pol.profile AS profile, + cp.id AS item + FROM legacy_charge lc + JOIN joined_legacy li + ON ( lc.charge_key1 = li.cat_key + AND lc.charge_key2 = li.call_key + AND lc.charge_key3 = li.item_key ) + JOIN asset.copy cp ON (cp.barcode = li.item_id) + JOIN legacy_baduser_map bu ON (bu.id = lc.user_key) + JOIN legacy_non_real_user pol ON (bu.barcode = pol.barcode) + WHERE bu.type = 'N' + ORDER BY + lc.charge_key1, + lc.charge_key2, + lc.charge_key3, + lc.charge_key4 DESC; + +-- ... and update the copies with it +UPDATE asset.copy + SET status = legacy_copy_status_map.id + FROM legacy_status_change_circ + JOIN legacy_copy_status_map ON (legacy_copy_status_map.name = legacy_status_change_circ.profile) + WHERE asset.copy.id = legacy_status_change_circ.item; + + +-- Next up, circ_lib changes based on recirc users ... +CREATE TABLE legacy_lib_change_circ AS + SELECT DISTINCT ON (lc.charge_key1, lc.charge_key2, lc.charge_key3) + ou.id AS lib, + cp.id AS item + FROM legacy_charge lc + JOIN joined_legacy li + ON ( lc.charge_key1 = li.cat_key + AND lc.charge_key2 = li.call_key + AND lc.charge_key3 = li.item_key ) + JOIN asset.copy cp ON (cp.barcode = li.item_id) + JOIN legacy_baduser_map bu ON (bu.id = lc.user_key) + JOIN legacy_recirc_lib pol ON (bu.barcode = pol.barcode) + JOIN actor.org_unit ou ON (ou.shortname = pol.lib) + WHERE bu.type = 'R' + ORDER BY + lc.charge_key1, + lc.charge_key2, + lc.charge_key3, + lc.charge_key4 DESC; + +-- ... and apply that too. +UPDATE asset.copy + SET circ_lib = legacy_lib_change_circ.lib + FROM legacy_lib_change_circ + WHERE asset.copy.id = legacy_lib_change_circ.item; + +COMMIT; + diff --git a/Evergreen/src/extras/import/load_legacy.sql b/Evergreen/src/extras/import/load_legacy.sql index 61875f1e4e..9adcbec4ab 100644 --- a/Evergreen/src/extras/import/load_legacy.sql +++ b/Evergreen/src/extras/import/load_legacy.sql @@ -113,7 +113,7 @@ INSERT INTO asset.stat_cat_entry (stat_cat, owner, value) -- Create a temp table to speed up CN and copy inserts -CREATE TEMP TABLE joined_legacy AS +CREATE TABLE joined_legacy AS SELECT i.*, c.call_num FROM legacy_item i JOIN legacy_callnum c USING (cat_key,call_key); @@ -129,7 +129,6 @@ INSERT INTO asset.call_number (creator,editor,record,label,owning_lib) -- Import base copy data --- CREATE TEMP TABLE legacy_copy_list AS INSERT INTO asset.copy (circ_lib,creator,editor,create_date,barcode,status,location,loan_duration,fine_level,opac_visible,price,circ_modifier,call_number) SELECT DISTINCT ou.id AS circ_lib, 1 AS creator, diff --git a/Evergreen/src/extras/import/non-real_user_loader.pl b/Evergreen/src/extras/import/non-real_user_loader.pl new file mode 100755 index 0000000000..67d3737238 --- /dev/null +++ b/Evergreen/src/extras/import/non-real_user_loader.pl @@ -0,0 +1,18 @@ +#!/usr/bin/perl + +use strict; +use warnings; + +print <) { + chomp; + my ($p,$l,$b) = split '\|'; + print "$p\t$l\t$b\n"; +} + +print '\.'."\n"; + diff --git a/Evergreen/src/extras/import/parse_patron_xml.pl b/Evergreen/src/extras/import/parse_patron_xml.pl index 54f589ec05..36424f42cb 100755 --- a/Evergreen/src/extras/import/parse_patron_xml.pl +++ b/Evergreen/src/extras/import/parse_patron_xml.pl @@ -96,7 +96,7 @@ for my $patron ( $doc->documentElement->childNodes ) { next; } } else { - $uid = $count; + next; } unless ($uid > 1) { diff --git a/Evergreen/src/extras/import/recirc_user_loader.pl b/Evergreen/src/extras/import/recirc_user_loader.pl new file mode 100755 index 0000000000..7a2ae9217d --- /dev/null +++ b/Evergreen/src/extras/import/recirc_user_loader.pl @@ -0,0 +1,18 @@ +#!/usr/bin/perl + +use strict; +use warnings; + +print <) { + chomp; + my ($b,$l) = split '\|'; + print "$b\t$l\n"; +} + +print '\.'."\n"; + -- 2.43.2