From f4e2580f0d1671136bc2166f97b5ad36b0ed31a0 Mon Sep 17 00:00:00 2001
From: dbs <dbs@6d9bc8c9-1ec2-4278-b937-99fde70a366f>
Date: Fri, 24 Apr 2009 03:07:45 +0000
Subject: [PATCH] Add mobile and other phones to patron records Use a less dumb
 UPDATE syntax

git-svn-id: svn://svn.open-ils.org/ILS-Contrib/conifer/trunk@383 6d9bc8c9-1ec2-4278-b937-99fde70a366f
---
 tools/migration-scripts/windsor_patrons.sql | 35 ++++++++++++++++++++++++-----
 1 file changed, 30 insertions(+), 5 deletions(-)

diff --git a/tools/migration-scripts/windsor_patrons.sql b/tools/migration-scripts/windsor_patrons.sql
index 3b5c455d7b..1a461aed9e 100644
--- a/tools/migration-scripts/windsor_patrons.sql
+++ b/tools/migration-scripts/windsor_patrons.sql
@@ -2,6 +2,8 @@ DROP TABLE staging_patron;
 DROP TABLE staging_patron_address;
 DROP TABLE staging_barcode;
 DROP TABLE staging_note;
+DROP TABLE staging_mobile_phone;
+DROP TABLE staging_other_phone;
 
 BEGIN;
 
@@ -9,11 +11,16 @@ CREATE TABLE staging_patron (idfield SERIAL NOT NULL, username TEXT, profile TEX
 CREATE TABLE staging_patron_address (idfield SERIAL NOT NULL, 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 (idfield SERIAL NOT NULL, barcode TEXT, old_barcode TEXT, active BOOLEAN);
 CREATE TABLE staging_note (idfield SERIAL NOT NULL, barcode TEXT, create_date TEXT, publicly_visible BOOLEAN, title TEXT, note TEXT, create_date2 TEXT);
+CREATE TABLE staging_mobile_phone (idfield SERIAL NOT NULL, barcode TEXT, phone TEXT);
+CREATE TABLE staging_other_phone (idfield SERIAL NOT NULL, barcode TEXT, phone 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';
+COPY staging_mobile_phone (barcode, phone) FROM '/home/dbs/conifer/windsor_patron_load_phones_mobile.csv';
+COPY staging_other_phone (barcode, phone) FROM '/home/dbs/conifer/windsor_patron_load_phones_other.csv';
+
 COMMIT;
 
 ---- Let's find our duplicate usernames
@@ -153,24 +160,41 @@ INSERT INTO actor.usr (profile, usrname, passwd, standing, ident_type, ident_val
         END AS alert_message,
         patron.create_date::DATE as create_date,
         patron.expire_date::DATE as expire_date
-    FROM staging_patron patron;
+    FROM staging_patron patron
+;
+
+-- And add our mobile and other phones to the patron record
+-- Mapping Windsor's "other" to evening_phone in Evergreen
+UPDATE actor.usr au 
+    SET evening_phone = TRIM(sop.phone)
+    FROM staging_other_phone sop
+    WHERE sop.barcode = au.ident_value
+;
+
+-- Mapping Windsor's "mobile" to other_phone in Evergreen
+UPDATE actor.usr au 
+    SET day_phone = TRIM(smp.phone)
+    FROM staging_mobile_phone smp
+    WHERE smp.barcode = au.ident_value
+;
 
 -- Now we need to generate actor.card entries
+-- And be careful to distinguish Windsor vs. existing entries
 INSERT INTO actor.card (usr, barcode, active) 
     SELECT au.id, au.ident_value, 't'
     FROM actor.usr au
     WHERE au.ident_value IS NOT NULL
+    AND au.home_ou IN (109, 122)
     AND au.ident_value NOT IN (SELECT barcode FROM actor.card)
     AND au.id > 1
 ;
 
-UPDATE actor.usr au SET card = (
-    SELECT ac.id
+UPDATE actor.usr au SET card = ac.id
     FROM actor.card ac
     WHERE ac.barcode = au.ident_value
     AND au.card IS NULL
     AND au.id > 1
-);
+;
 
 -- Get rid of the "old" barcodes that we inserted into actor.usr
 DELETE FROM staging_barcode
@@ -191,7 +215,8 @@ UPDATE staging_patron_address
 
 UPDATE staging_patron_address
     SET address_type = NULL
-    WHERE TRIM(address_type) = '';
+    WHERE TRIM(address_type) = ''
+;
 
 INSERT INTO actor.usr_address (usr, address_type, street1, street2, city, state, county, country, post_code) 
     SELECT DISTINCT
-- 
2.11.0