From 434b9264e1e14756c81987282c7914d2f086b6f6 Mon Sep 17 00:00:00 2001 From: dbs Date: Tue, 21 Apr 2009 02:59:48 +0000 Subject: [PATCH] Update a few libraries for our holdings Spaces in SQL scripts are much nicer than tabs git-svn-id: svn://svn.open-ils.org/ILS-Contrib/conifer/trunk@370 6d9bc8c9-1ec2-4278-b937-99fde70a366f --- tools/migration-scripts/generate_copies.sql | 6 +- tools/migration-scripts/windsor_patrons.sql | 166 ++++++++++++++++++---------- 2 files changed, 115 insertions(+), 57 deletions(-) diff --git a/tools/migration-scripts/generate_copies.sql b/tools/migration-scripts/generate_copies.sql index 39a5b43286..af4faca158 100644 --- a/tools/migration-scripts/generate_copies.sql +++ b/tools/migration-scripts/generate_copies.sql @@ -3,7 +3,11 @@ BEGIN; -- Map libraries UPDATE staging_items SET owning_lib = 'OSUL' - WHERE location = 'DESMARAIS'; + WHERE owning_lib = 'DESMARAIS'; + +UPDATE staging_items + SET owning_lib = 'OSTMA' + WHERE owning_lib = 'DESMARAIS'; UPDATE staging_items SET owning_lib = 'OSM', location = 'WWW' diff --git a/tools/migration-scripts/windsor_patrons.sql b/tools/migration-scripts/windsor_patrons.sql index 7a93dbe0d6..bf26fb4ed0 100644 --- a/tools/migration-scripts/windsor_patrons.sql +++ b/tools/migration-scripts/windsor_patrons.sql @@ -14,63 +14,117 @@ COPY staging_patron (username, profile, identity_type, password, standing, ident 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'; +COMMIT; + +-- Let's find our duplicate usernames +SELECT trim(both from username), identity_value, COUNT(username) as ucount +FROM staging_patron +GROUP BY username, identity_value +HAVING COUNT(username) > 1 +ORDER BY ucount DESC; + +-- Now let's find our duplicate barcodes +SELECT trim(both from username), identity_value, COUNT(identity_value) as ucount +FROM staging_patron +GROUP BY username, identity_value +HAVING COUNT(identity_value) > 1 +ORDER BY ucount DESC; + +-- Get the distinct set of values for dupe usernames +-- including active/barred/deleted status, just in case +SELECT DISTINCT trim(both from username), identity_value, active, barred, deleted +FROM staging_patrons +WHERE username IN ( + SELECT username + FROM staging_patrons + GROUP BY username + HAVING count(username) > 1 +); + +-- Do the barcodes for dupe usernames exist over in the staging_barcode table? +SELECT DISTINCT TRIM(BOTH FROM p.username) AS uname, p.identity_value, + CASE + WHEN p.identity_value IN (SELECT barcode FROM staging_barcode WHERE barcode = p.identity_value AND active = 't') THEN 'active new' + WHEN p.identity_value IN (SELECT barcode FROM staging_barcode WHERE old_barcode = p.identity_value AND active = 't') THEN 'active old' + WHEN p.identity_value IN (SELECT barcode FROM staging_barcode WHERE barcode = p.identity_value AND active = 'f') THEN 'inactive new' + WHEN p.identity_value IN (SELECT barcode FROM staging_barcode WHERE old_barcode = p.identity_value AND active = 'f') THEN 'inactive old' + ELSE 'not found' + END AS barcode_state + FROM staging_patron p + WHERE p.identity_value IN ( + SELECT DISTINCT identity_value + FROM staging_patrons + WHERE username IN ( + SELECT username + FROM staging_patrons + GROUP BY username + HAVING COUNT(username) > 1 + ) + ) + ORDER BY uname +; + +BEGIN; 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; + SELECT DISTINCT + CASE + -- Faculty + WHEN trim(both from patron.profile) IN ('AFAC', 'FAC', 'LAW FACLTY') THEN 11 + -- Graduate student + WHEN trim(both from patron.profile) IN ('AGRAD', 'GRAD') THEN 12 + -- Undergraduate student + WHEN trim(both from patron.profile) IN ('AUND', 'UND') THEN 13 + -- Readers (obviously need to map these to something better + WHEN trim(both from patron.profile) = 'DIRB' THEN 14 + WHEN trim(both from patron.profile) = 'EXAL' THEN 14 + WHEN trim(both from patron.profile) = 'EXEC' THEN 14 + WHEN trim(both from patron.profile) = 'EXOT' THEN 14 + WHEN trim(both from patron.profile) = 'ILL' THEN 14 + WHEN trim(both from patron.profile) = 'LAW1' THEN 14 + WHEN trim(both from patron.profile) = 'LAW2' THEN 14 + WHEN trim(both from patron.profile) = 'LAW3' THEN 14 + WHEN trim(both from patron.profile) = 'LAW COUR' THEN 14 + WHEN trim(both from patron.profile) = 'LAW DAY365' THEN 14 + WHEN trim(both from patron.profile) = 'LAW KEY2' THEN 14 + WHEN trim(both from patron.profile) = 'STAF' THEN 14 + WHEN trim(both from patron.profile) IS NULL THEN 14 + ELSE 14 + END AS profile, + trim(both from patron.username) AS usrname, + trim(both from patron.password) AS passwd, + patron.standing AS standing, + CASE + WHEN patron.identity_type = 'Other' THEN 3 + END AS ident_type, + trim(both from patron.identity_value) AS ident_value, + trim(both from 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, + trim(both from patron.family_name) AS family_name, + CASE + WHEN trim(both from patron.day_phone) != '' THEN patron.day_phone + ELSE NULL + END AS day_phone, + CASE + WHEN trim(both from patron.home_library) = 'Leddy' THEN 109 + WHEN trim(both from 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; +ROLLBACK; + +-- vim: et:ts=4:sw=4: -- 2.11.0