X-Git-Url: http://git.equinoxoli.org/?p=migration-tools.git;a=blobdiff_plain;f=mig-xml%2Fexcel_mapping_reports.xml;h=6c8dc7336638d45e90c4e6775d3f1fdc199fad4a;hp=e6ebac0a953b006d352e300c5b6fcb156375d8e2;hb=371837d00ef07cb77f7e8ff44d1ec7fbc69beff0;hpb=f074de65f0c5cbfece63d9d5bb32369888930a1c
diff --git a/mig-xml/excel_mapping_reports.xml b/mig-xml/excel_mapping_reports.xml
index e6ebac0..6c8dc73 100644
--- a/mig-xml/excel_mapping_reports.xml
+++ b/mig-xml/excel_mapping_reports.xml
@@ -67,9 +67,12 @@
tlc
0
Count.Note Type.Notes
- SELECT COUNT(l_itemcomment), 'Item Comments' FROM ititeminformation_tsv_clean WHERE l_itemcomment IS NOT NULL GROUP BY 2
- UNION ALL SELECT COUNT(l_physicalcondition), 'Condition Notes' FROM ctlocitem_tsv_clean WHERE l_physicalcondition IS NOT NULL GROUP BY 2
- UNION ALL SELECT COUNT(l_checkinoutnote), 'Circ Notes' FROM ctlocitem_tsv_clean WHERE l_checkinoutnote IS NOT NULL GROUP BY 2
+ SELECT COUNT(l_itemcomment)::TEXT, 'Item Comments' FROM ititeminformation_tsv_clean WHERE l_itemcomment IS NOT NULL GROUP BY 2
+ UNION ALL SELECT COUNT(l_physicalcondition)::TEXT, 'Condition Notes' FROM ctlocitem_tsv_clean WHERE l_physicalcondition IS NOT NULL GROUP BY 2
+ UNION ALL SELECT COUNT(l_checkinoutnote)::TEXT, 'Circ Notes' FROM ctlocitem_tsv_clean WHERE l_checkinoutnote IS NOT NULL GROUP BY 2
+ UNION ALL (SELECT DISTINCT 'Sample Item Comment', l_itemcomment FROM ititeminformation_tsv_clean WHERE l_itemcomment IS NOT NULL LIMIT 20)
+ UNION ALL (SELECT DISTINCT 'Sample Physical Condition', l_physicalcondition FROM ctlocitem_tsv_clean WHERE l_physicalcondition IS NOT NULL LIMIT 20)
+ UNION ALL (SELECT DISTINCT 'Sample Circ Note', l_checkinoutnote FROM ctlocitem_tsv_clean WHERE l_checkinoutnote IS NOT NULL LIMIT 20)
@@ -195,7 +198,7 @@
SELECT COUNT(l_agencynumber), SUM(l_accountbalance::INTEGER) FROM itagency_tsv_clean WHERE l_accountbalance != '0'
If this is being migrated there are a few options of how to do it and each will have different workflows.
-
+
tlc_load_usr_stat_cats
Patron Stat Cats and Counts
@@ -472,6 +475,8 @@
These need to be mapped to Evergreen phone types.
+
+
hz_borrowersbybtypes
Borrowers by Borrower Types
@@ -482,13 +487,30 @@
- hz_borrowerpinsamples
- Borrower PINs Samples
+ hz_borrowersbybtypes
+ Borrowers by Borrower Types
+ horizon
+ 1
+ Count.Borrower Type.Description
+ SELECT COUNT(*), a.btype, b.descr FROM borrower_csv_clean a JOIN btype_csv_clean b ON b.btype = a.btype GROUP BY 2, 3 ORDER BY 2;
+
+
+
+ hz_borrowerpincount
+ Borrower PINs Count
horizon
0
- Sample PINs
- SELECT l_borrower_pin FROM actor_usr_legacy WHERE LENGTH(l_borrower_pin) > 1 LIMIT 20;
- Only 16 PINS defined.
+ Count of Migratable Passwords / PINs
+ SELECT COUNT(l_borrower_pin) FROM actor_usr_legacy WHERE LENGTH(l_borrower_pin) > 1;
+
+
+
+ hz_borrowerpincount
+ Borrower PINs Count
+ horizon
+ 1
+ Count of Migratable Passwords / PINs
+ SELECT COUNT(pin) FROM borrower_csv_clean WHERE LENGTH(pin) > 1;
@@ -496,9 +518,35 @@
Borrower Note Field Samples
horizon
0
- Count
+ Sample of Migratable Notes
SELECT l_borrower_borrower_note FROM actor_usr_legacy WHERE LENGTH(l_borrower_borrower_note) > 1 LIMIT 20;
- 19,473
+
+
+
+ hz_borrowernotesample
+ Borrower Note Field Samples
+ horizon
+ 1
+ Sample of Migratable Notes
+ SELECT borrower_note FROM borrower_csv_clean WHERE LENGTH(borrower_note) > 1 LIMIT 20;
+
+
+
+ hz_borrowernotescount
+ Count of Migratable Borrower Notes
+ horizon
+ 0
+ Count
+ SELECT COUNT(l_borrower_borrower_note) FROM actor_usr_legacy WHERE LENGTH(l_borrower_borrower_note) > 1;
+
+
+
+ hz_borrowernotescount
+ Counnt of Migratable Borrower Notes
+ horizon
+ 1
+ Count
+ SELECT COUNT(borrower_note) FROM borrower_csv_clean WHERE LENGTH(borrower_note) > 1;
@@ -508,7 +556,6 @@
0
Count
SELECT l_borrower_note2 FROM actor_usr_legacy WHERE LENGTH(l_borrower_note2) > 1 LIMIT 20;
- 78
@@ -518,7 +565,6 @@
0
Count
SELECT l_borrower_note3 FROM actor_usr_legacy WHERE LENGTH(l_borrower_note3) > 1 LIMIT 20;
- Only 6.
@@ -535,7 +581,21 @@
UNION ALL SELECT l_borrower_phone_4_phone_type AS b FROM actor_usr_legacy) x
GROUP BY 2 ORDER BY 2
- Ordinal phones will have to be combined.
+
+
+
+ hz_phones
+ Borrower Phones
+ horizon
+ 1
+ Count.Borrower Phone Position
+
+ SELECT COUNT(*), '0' FROM borrower_phone_csv_clean WHERE ord = '0'
+ UNION ALL SELECT COUNT(*), '1' FROM borrower_phone_csv_clean WHERE ord = '1'
+ UNION ALL SELECT COUNT(*), '2' FROM borrower_phone_csv_clean WHERE ord = '2'
+ UNION ALL SELECT COUNT(*), '3' FROM borrower_phone_csv_clean WHERE ord = '3'
+ UNION ALL SELECT COUNT(*), '4' FROM borrower_phone_csv_clean WHERE ord = '4'
+
@@ -555,6 +615,16 @@
+ hz_bstats
+ Borrower B-Stats
+ horizon
+ 1
+ Count.B-Stat.Description
+ SELECT COUNT(*), a.bstat, b.descr FROM borrower_bstat_csv_clean a JOIN bstat_csv_clean b ON b.bstat = a.bstat GROUP BY 2, 3;
+
+
+
+
hz_copybycollection
Copies by Collection
horizon
@@ -562,14 +632,14 @@
Count.Collection
SELECT COUNT(id), l_collection FROM asset_copy_legacy GROUP BY 2 ORDER BY 2;
-
-
- hz_copyfieldexamples
- Sample Copy Field Values
+
+
+ hz_copybycollection
+ Copies by Collection
horizon
- 0
- Field
- SELECT l_copy FROM asset_copy_legacy WHERE LENGTH(l_copy) > 1 LIMIT 30;
+ 1
+ Count.Collection.Description.PAC Description
+ SELECT COUNT(*), a.collection, c.descr, c.pac_descr FROM item_csv_clean a JOIN collection_csv_clean c ON c.collection = a.collection GROUP BY 2, 3, 4 ORDER BY 2, 3, 4;
@@ -580,35 +650,52 @@
Count.Item Type (itype)
SELECT COUNT(id), l_itype FROM asset_copy_legacy GROUP BY 2 ORDER BY 2;
-
+
- hz_volfieldexamples
- Sample Volume Field Values
+ hz_itemsbyitype
+ Items by IType
horizon
- 0
- Field
- SELECT l_volume FROM asset_copy_legacy WHERE LENGTH(l_volume) > 1 LIMIT 30;
-
+ 1
+ Count.Item Type (itype).Description
+ SELECT COUNT(*), a.itype, b.descr FROM item_csv_clean a JOIN itype_csv_clean b ON b.itype = a.itype GROUP BY 2, 3 ORDER BY 2;
-
-
- hz_iteminhouseuses
- Item In House Uses
+
+
+ hz_internalnotescount
+ Internal/Check In Item Notes
horizon
0
- Count.Number of Uses
- SELECT COUNT(id), l_n_inhouse_uses FROM asset_copy_legacy GROUP BY 2 ORDER BY 2;
+ Count
+ SELECT COUNT(l_internal_note) FROM asset_copy_legacy WHERE LENGTH(l_internal_note) > 1;
+
+
+
+ hz_internalnotescount
+ Internal/Check In Item Notes
+ horizon
+ 1
+ Count
+ SELECT COUNT(cki_notes) FROM item_csv_clean WHERE LENGTH(cki_notes) > 1;
hz_internalnotesample
- Internal Item Notes
+ Internal/Check In Item Notes Sample
horizon
0
- Sample Note
- SELECT l_internal_note FROM asset_copy_legacy WHERE LENGTH(l_internal_note) > 1 LIMIT 30;
+ Count
+ SELECT l_internal_note FROM asset_copy_legacy WHERE LENGTH(l_internal_note) > 1 LIMIT 20;
-
+
+
+ hz_internalnotesample
+ Internal/Check In Item Notes Sample
+ horizon
+ 1
+ Count
+ SELECT cki_notes FROM item_csv_clean WHERE LENGTH(cki_notes) > 1 LIMIT 20;
+
+
@@ -675,6 +762,7 @@
SELECT COUNT(id), l_access_if_applicable FROM actor_usr_legacy GROUP BY 2 ORDER BY 2;
+
rm_actor_comments
Patron Comments