From 1be6dcaa50907811e85226e4ef29cdb08adff5b4 Mon Sep 17 00:00:00 2001 From: dbs Date: Mon, 20 Apr 2009 04:47:18 +0000 Subject: [PATCH] First cut of Windsor patron import script; not yet complete. git-svn-id: svn://svn.open-ils.org/ILS-Contrib/conifer/trunk@363 6d9bc8c9-1ec2-4278-b937-99fde70a366f --- tools/migration-scripts/windsor_patrons.sql | 76 +++++++++++++++++++++++++++++ 1 file changed, 76 insertions(+) create mode 100644 tools/migration-scripts/windsor_patrons.sql diff --git a/tools/migration-scripts/windsor_patrons.sql b/tools/migration-scripts/windsor_patrons.sql new file mode 100644 index 0000000000..7a93dbe0d6 --- /dev/null +++ b/tools/migration-scripts/windsor_patrons.sql @@ -0,0 +1,76 @@ +DROP TABLE staging_patron; +DROP TABLE staging_patron_address; +DROP TABLE staging_barcode; +DROP TABLE staging_note; + +BEGIN; + +CREATE TABLE staging_patron (username TEXT, profile TEXT, identity_type TEXT, password TEXT, standing INTEGER, identity_type2 TEXT, identity_value TEXT, name_prefix TEXT, first_given_name TEXT, second_given_name TEXT, family_name TEXT, name_suffix TEXT, day_phone TEXT, evening_phone TEXT, other_phone TEXT, alert_message TEXT, home_library TEXT, active BOOLEAN, barred BOOLEAN, deleted BOOLEAN, create_date DATE, expire_date DATE); +CREATE TABLE staging_patron_address (barcode TEXT, address_type TEXT, street_1 TEXT, street_2 TEXT, city TEXT, county TEXT, province TEXT, country TEXT, postal_code TEXT); +CREATE TABLE staging_barcode (barcode TEXT, old_barcode TEXT, active BOOLEAN); +CREATE TABLE staging_note (barcode TEXT, create_date TEXT, publicly_visible BOOLEAN, title TEXT, note TEXT, create_date2 TEXT); + +COPY staging_patron (username, profile, identity_type, password, standing, identity_type2, identity_value, name_prefix, first_given_name, second_given_name, family_name, name_suffix, day_phone, evening_phone, other_phone, alert_message, home_library, active, barred, deleted, create_date, expire_date) FROM '/home/dbs/conifer/windsor_patron_load_base.csv'; +COPY staging_patron_address (barcode, address_type, street_1, street_2, city, county, province, country, postal_code) FROM '/home/dbs/conifer/windsor_patron_load_addresses.csv'; +COPY staging_barcode (barcode, old_barcode, active) FROM '/home/dbs/conifer/windsor_patron_load_barcodes.csv'; +COPY staging_note (barcode, create_date, publicly_visible, title, note, create_date2) FROM '/home/dbs/conifer/windsor_patron_load_notes.csv'; + +INSERT INTO actor.usr (profile, usrname, passwd, standing, ident_type, ident_value, first_given_name, second_given_name, family_name, day_phone, home_ou, active, barred, deleted, alert_message, create_date, expire_date) + SELECT + CASE + -- Faculty + WHEN patron.profile IN ('AFAC', 'FAC', 'LAW FACLTY') THEN 11 + -- Graduate student + WHEN patron.profile IN ('AGRAD', 'GRAD') THEN 12 + -- Undergraduate student + WHEN patron.profile IN ('AUND', 'UND') THEN 13 + -- Readers (obviously need to map these to something better + WHEN patron.profile = 'DIRB' THEN 14 + WHEN patron.profile = 'EXAL' THEN 14 + WHEN patron.profile = 'EXEC' THEN 14 + WHEN patron.profile = 'EXOT' THEN 14 + WHEN patron.profile = 'ILL' THEN 14 + WHEN patron.profile = 'LAW1' THEN 14 + WHEN patron.profile = 'LAW2' THEN 14 + WHEN patron.profile = 'LAW3' THEN 14 + WHEN patron.profile = 'LAW COUR' THEN 14 + WHEN patron.profile = 'LAW DAY365' THEN 14 + WHEN patron.profile = 'LAW KEY2' THEN 14 + WHEN patron.profile = 'STAF' THEN 14 + WHEN patron.profile IS NULL THEN 14 + ELSE 14 + END AS profile, + patron.username AS usrname, + patron.password AS passwd, + patron.standing AS standing, + CASE + WHEN patron.identity_type = 'Other' THEN 3 + END AS ident_type, + patron.identity_value AS ident_value, + patron.first_given_name AS first_given_name, + CASE + WHEN trim(both from patron.second_given_name) != '' THEN patron.second_given_name + ELSE NULL + END AS second_given_name, + patron.family_name AS family_name, + CASE + WHEN patron.day_phone != '' THEN patron.day_phone + ELSE NULL + END AS day_phone, + CASE + WHEN patron.home_library = 'Leddy' THEN 109 + WHEN patron.home_library = 'Law' THEN 122 + ELSE 109 + END AS home_ou, + patron.active as active, + patron.barred as barred, + patron.deleted as deleted, + CASE + WHEN trim(both from patron.alert_message) != '' THEN patron.alert_message + ELSE NULL + END AS alert_message, + patron.create_date::DATE as create_date, + patron.expire_date::DATE as expire_date + FROM staging_patron patron; + +--COMMIT; -- 2.11.0