X-Git-Url: http://git.equinoxoli.org/?p=migration-tools.git;a=blobdiff_plain;f=mig-xml%2Fexcel_mapping_reports.xml;h=2c101b735f9946dc178e0c60aba1a67e2e518788;hp=397f2143424201c9fb57705d33e98373ee07cde8;hb=49c84ca86a91a97007c975eff40d50890cc46fbb;hpb=e092916cfb7e0dcabe3de25c965c4b23f8409130

diff --git a/mig-xml/excel_mapping_reports.xml b/mig-xml/excel_mapping_reports.xml
index 397f214..2c101b7 100644
--- a/mig-xml/excel_mapping_reports.xml
+++ b/mig-xml/excel_mapping_reports.xml
@@ -67,23 +67,16 @@
         <tag>tlc</tag>
         <iteration>0</iteration>
         <heading>Count.Note Type.Notes</heading>
-        <query>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
+        <query>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) 
          </query>
     </report>
 
     <report>
-        <name>tlc_load_asset_in_house_use</name>
-        <report_title>Copies With In House Uses</report_title>
-        <tag>tlc</tag>
-        <iteration>0</iteration>
-        <heading>Copy Count.Sum of In House Uses.Do Not Migrate</heading>
-        <query>SELECT COUNT(l_totalinhouseuses), SUM(l_totalinhouseuses::INTEGER) FROM ititeminformation_tsv_clean WHERE l_totalinhouseuses != '0'</query>
-        <note>TLC doesn't have dates recorded for in house use so these can only be migrated as date of migration or we can not migrate them.</note>
-    </report>
-
-    <report>
         <name>tlc_load_asset_holdings_codes</name>
         <report_title>Holdings Codes</report_title>
         <tag>tlc</tag>
@@ -113,15 +106,6 @@
     </report>
 -->
     <report>
-        <name>tlc_load_asset_barcodes_with_spaces</name>
-        <report_title>Count of Barcodes with Spaces</report_title>
-        <tag>tlc</tag>
-        <iteration>0</iteration>
-        <heading>Count of Barcodes With Spaces.Transformation</heading>
-        <query>SELECT COUNT(l_itembarcode) FROM ititeminformation_tsv_clean WHERE l_itembarcode ~* ' '</query>
-    </report>
-
-    <report>
         <name>tlc_load_money_migrating_bills</name>
         <report_title>Migrating Bills By Bill Type</report_title>
         <tag>tlc</tag>
@@ -147,7 +131,7 @@
         <heading>Count.Year of Expiration.Do Not Migrate?</heading>
         <query>SELECT COUNT(l_agencynumber), LEFT(l_expirationdate,4) FROM itagency_tsv_clean GROUP BY 2 ORDER BY 2</query>
     </report>
-
+<!--
     <report>
         <name>tlc_load_usrs_bynever</name>
         <report_title>Patrons With Claims</report_title>
@@ -156,7 +140,7 @@
         <heading>Count.Claim Type.Do Not Migrate</heading>
         <query>SELECT COUNT(l_agencynumber) FROM itagency_tsv_clean, 'Never Checked Out' WHERE l_itemsclaimednevercheckedout != '0' GROUP BY 1 UNION ALL SELECT COUNT(l_agencynumber), 'Claims Returned' FROM itagency_tsv_clean WHERE l_itemsclaimedreturned != '0' GROUP BY 1</query>
     </report>
-
+-->
     <report>
         <name>tlc_load_usrs_byactive</name>
         <report_title>Patrons by Last Active Date</report_title>
@@ -187,15 +171,6 @@
     </report>
 
     <report>
-        <name>tlc_load_usrs_alternate_ids</name>
-        <report_title>Count of Patrons w Alt Id</report_title>
-        <tag>tlc</tag>
-        <iteration>0</iteration>
-        <heading>Count.Migration Need</heading>
-        <query>SELECT COUNT(l_agencynumber) FROM itagency_tsv_clean WHERE l_alternateid IS NOT NULL</query>
-    </report>
-
-    <report>
         <name>tlc_load_active_usr_passwords</name>
         <report_title>Count of Patrons w Passwords</report_title>
         <tag>tlc</tag>
@@ -205,24 +180,6 @@
         <note>If any PINS are blank we will have to choose a method to supply them as Evergreen requires passwords.</note>
     </report>
 
-    <report>
-        <name>tlc_load_usr_barcodes_wspacess</name>
-        <report_title>Barcodes with Spaces</report_title>
-        <tag>tlc</tag>
-        <iteration>0</iteration>
-        <heading>Count</heading>
-        <query>SELECT COUNT(l_agencynumber) FROM itagency_tsv_clean WHERE l_barcode ~* ' '</query>
-    </report>
-
-    <report>
-        <name>tlc_load_usr_barcode_lengths</name>
-        <report_title>Barcode Lengths by Library</report_title>
-        <tag>tlc</tag>
-        <iteration>0</iteration>
-        <heading>Count of Barcode.Barcode Length.Branch.Transformation Note</heading>
-        <query>SELECT COUNT(l_agencynumber), LENGTH(l_barcode), l_issuingbranch FROM itagency_tsv_clean GROUP BY 2, 3 ORDER BY 3, 2</query>
-    </report>
-
    <report>
         <name>tlc_load_usr_notes</name>
         <report_title>Count of Patron Notes</report_title>
@@ -233,15 +190,6 @@
     </report>
     
     <report>
-        <name>tlc_load_misc</name>
-        <report_title>Miscellaneous Options</report_title>
-        <tag>tlc</tag>
-        <iteration>0</iteration>
-        <heading>Count.Option.Value.Note</heading>
-        <query>SELECT COUNT(l_agencynumber), 'Create Evergreen Grouped/Family Accounts?' FROM itagency_tsv_clean WHERE l_responsibleagencynumber IS NOT NULL GROUP BY 2 UNION ALL SELECT COUNT(l_itembarcode), 'Lost Items' FROM ititemlocation_tsv_clean WHERE l_outdate IS NOT NULL AND l_status = 'L' GROUP BY 2</query>
-    </report>
-
-    <report>
         <name>tlc_load_usr_balance</name>
         <report_title>Count and Sum of Balances</report_title>
         <tag>tlc</tag>
@@ -250,7 +198,7 @@
         <query>SELECT COUNT(l_agencynumber), SUM(l_accountbalance::INTEGER) FROM itagency_tsv_clean WHERE l_accountbalance != '0'</query>
         <note>If this is being migrated there are a few options of how to do it and each will have different workflows.</note>
     </report>
-
+<!--
     <report>
         <name>tlc_load_usr_addresses_phones</name>
         <report_title>Patron Phones</report_title>
@@ -260,7 +208,7 @@
         <query>SELECT COUNT(l_phone1), 'Phone 1' FROM itaddress_tsv_clean WHERE l_phone1 IS NOT NULL GROUP BY 2 UNION ALL SELECT COUNT(l_phone1), 'Phone 2' FROM itaddress_tsv_clean WHERE l_phone2 IS NOT NULL GROUP BY 2</query>
         <note>These need to be mounted to Evergreen phone fields.</note>
     </report>
-
+-->
     <report>
          <name>tlc_load_usr_stat_cats</name>
         <report_title>Patron Stat Cats and Counts</report_title>
@@ -752,5 +700,51 @@
         </query>
     </report>
 
+<!-- tlc reports 
+
+    <report>
+        <name>tlc_reports_load_asset_holdings_codes</name>
+        <report_title>Holdings Codes</report_title>
+        <tag>tlc_reports</tag>
+        <iteration>0</iteration>
+        <heading>Count.Library.Holdings Codes.Evergreen Circulation Modifier.Evergreen Shelving Locatione</heading>
+        <query>SELECT COUNT(id), x_source, l_holdingscode FROM asset_copy_legacy GROUP BY 2, 3 ORDER BY 2, 3</query>
+    </report>
+
+    <report>
+        <name>tlc_reports_load_usrs_bygroup</name>
+        <report_title>Patrons by Agency Type</report_title>
+        <tag>tlc_reports</tag>
+        <iteration>0</iteration>
+        <heading>Count.Library.Permission Group.Evergreen Permission Group</heading>
+        <query>SELECT COUNT(id), l_location, l_borrower_type FROM actor_usr_legacy GROUP BY 2, 3 ORDER BY 2, 3</query>
+    </report>
+
+    <report>
+        <name>tlc_reports_load_usr_balance</name>
+        <report_title>Count and Sum of Balances</report_title>
+        <tag>tlc_reports</tag>
+        <iteration>0</iteration>
+        <heading>Count.SUM.Migrate?</heading>
+        <query>SELECT COUNT(id), SUM(l_current_fine_balance::NUMERIC(6,2)) FROM actor_usr_address_legacy WHERE l_current_fine_balance != '0' AND l_current_fine_balance IS NOT NULL</query>
+    </report>
+
+    <report>
+        <name>tlc_reports_users_blocked</name>
+        <report_title>Count of Blocked Patrons</report_title>
+        <tag>tlc_reports</tag>
+        <iteration>0</iteration>
+        <heading>Count.Blocked.Migrate?</heading>
+        <query>SELECT COUNT(id), l_blocked FROM actor_usr_address_legacy GROUP BY 2</query>
+    </report>
 
+    <report>
+        <name>tlc_reports_load_usr_addresses_phones</name>
+        <report_title>Patron Phones</report_title>
+        <tag>tlc_reports</tag>
+        <iteration>0</iteration>
+        <heading>Count.Phone Type.Phone Type</heading>
+        <query>SELECT COUNT(id), 'Phone 1' FROM actor_usr_address_legacy WHERE l_phone_1 IS NOT NULL GROUP BY 2 UNION ALL SELECT COUNT(id), 'Phone 2' FROM actor_usr_address_legacy WHERE l_phone_2 IS NOT NULL GROUP BY 2</query>
+    </report>
+-->
 </reports_file>