/*

David Schlangen
david.schlangen@lexisnexis.com
Financial Crimes Enforcement Network - List of Money Services businesses (names, address and services provided) across the US

Link to the website to pull the data from: http://www.fincen.gov/financial_institutions/msb/msbstateselector.html

1. Select All States, click OK.
2. Save the spreadsheet on your desktop.
3. Open the spreadsheet and scroll to the bottom.  Delete the Legend rows so they are not included as rows in the DB:
  * NEW KEY-Form 107 (effective on 1/2005)
  A. issuer of traveler's checks
  B. seller of traveler's checks
  C. redeemer of traveler's checks
  D. issuer of money orders
  E. seller of money orders
  F. redeemer of money orders
  G. currency dealer or exchanger
  H. check casher
  I. money transmitter

4. Convert that Excel spreadsheet into CSV format
5. Spray the csv file onto thor, giving it the following logical file name:
    thor400::in::fincen_money_services_businesses
Format:  ASCII
Max Record Length: 8192
Separator:  \,
Line Terminator:  \n,\r,\r\n
Quote:  "


5. After spraying the file, read it in using the following code
*/

import std;

record_layout := RECORD
  string LEGAL_NAME; 
  string DBA_NAME; 
  string STREET_ADDRESS;
  string CITY; 
  string STATE; 
  string ZIP; 
  string Services_Provided;
  string STATE_CODES_WHERE_SERVICES_OFFERED; 
  string NUMBER_OF_BRANCHES; 
  string Auth_Sign_Date; 
  string Received_Date;
END;

d := DATASET('~thor400::in::fincen_money_services_businesses', record_layout, csv(quote('"'), heading(single)) )(legal_name<>'' or dba_name<>'');

OUTPUT(d);

record_layout2 := record
  string LEGAL_NAME; 
  string DBA_NAME; 
  string STREET_ADDRESS;
  string CITY; 
  string STATE; 
  string ZIP; 
  string Services_Provided;
  string STATE_CODES_WHERE_SERVICES_OFFERED; 
  string NUMBER_OF_BRANCHES; 
  string Received_Date;
  boolean issuer_of_travelers_checks;
  boolean seller_of_travelers_checks;
  boolean redeemer_of_travelers_checks;
  boolean issuer_of_money_orders;
  boolean seller_of_money_orders;
  boolean redeemer_of_money_orders;
  boolean currency_dealer_or_exchanger;
  boolean check_casher;
  boolean money_transmitter;
end;

p := project(d, transform(record_layout2,
                  self.issuer_of_travelers_checks := std.str.find(left.Services_Provided, 'A', 1) > 0;
                  self.seller_of_travelers_checks := std.str.find(left.Services_Provided, 'B', 1) > 0;
                  self.redeemer_of_travelers_checks := std.str.find(left.Services_Provided, 'C', 1) > 0;
                  self.issuer_of_money_orders := std.str.find(left.Services_Provided, 'D', 1) > 0;
                  self.seller_of_money_orders := std.str.find(left.Services_Provided, 'E', 1) > 0;
                  self.redeemer_of_money_orders := std.str.find(left.Services_Provided, 'F', 1) > 0;
                  self.currency_dealer_or_exchanger := std.str.find(left.Services_Provided, 'G', 1) > 0;
                  self.check_casher := std.str.find(left.Services_Provided, 'H', 1) > 0;
                  self.money_transmitter := std.str.find(left.Services_Provided, 'I', 1) > 0;
                  self := left));
                  
output(enth(p, 100), named('sample_FINCEN_MSB_listings')); // sample of 100 random records

minnesota_listings := p(state='MN' or std.str.find(STATE_CODES_WHERE_SERVICES_OFFERED, 'MN', 1)>0);
output(minnesota_listings, named('sample_Minnesota_listings'));
output(count(minnesota_listings), named('count_Minnesota_listings'));


// create some summary stats about the data in the file
t := table(p, {total_records := count(group),
               check_cashing_agencies := count(group, check_casher),
               currency_dealers_in_minnesota := count(group, currency_dealer_or_exchanger and  
                    (state='MN' or std.str.find(STATE_CODES_WHERE_SERVICES_OFFERED, 'MN', 1)>0 ) );
               }, few);
output(t, named('summary_stats'));