· 8 years ago · Apr 17, 2018, 06:52 AM
1def self.irs
2 raise "Couldn't find IRS data in /tmp" unless (1..4).all?{|i| File.exist?("/tmp/EO#{i}.LST")}
3
4 puts "Creating IRS table"
5 sql = <<-EOF
6 CREATE TABLE `organizations`
7 (
8 id VARCHAR(9),
9 primary_name VARCHAR(70),
10 care_of_name VARCHAR(35),
11 street VARCHAR(35),
12 city VARCHAR(22),
13 state VARCHAR(2),
14 zip VARCHAR(10),
15 group_exemption VARCHAR(4),
16 subsection_code VARCHAR(2),
17 affiliation_code VARCHAR(1),
18 classification_codes VARCHAR(4),
19 ruling_date VARCHAR(6),
20 deductibility_code VARCHAR(1),
21 foundation_code VARCHAR(2),
22 activity_codes VARCHAR(9),
23 organization_code VARCHAR(1),
24 universal_location_code VARCHAR(2),
25 advance_ruling_expiration_date VARCHAR(6),
26 tax_period VARCHAR(6),
27 asset_code VARCHAR(1),
28 income_code VARCHAR(1),
29 filing_requirement_code VARCHAR(3),
30 reserved_empty VARCHAR(3),
31 accounting_period VARCHAR(2),
32 asset_amount VARCHAR(13),
33 income_amount VARCHAR(13),
34 income_amount_neg VARCHAR(1),
35 form_990_rev_amount VARCHAR(13),
36 form_990_rev_amount_neg VARCHAR(1),
37 ntee_code VARCHAR(4),
38 sort_name VARCHAR(35)
39 ) ENGINE=InnoDB CHARSET=latin1;
40 EOF
41 ActiveRecord::Base.connection.execute("DROP TABLE IF EXISTS `organizations`;")
42 ActiveRecord::Base.connection.execute(sql)
43
44 db = ActiveRecord::Base.connection.current_database
45 puts "Importing IRS data from /tmp into #{db}"
46 ActiveRecord::Base.connection.execute("LOAD DATA INFILE '/tmp/EO1.LST' INTO TABLE #{db}.organizations FIELDS TERMINATED BY '' ENCLOSED BY '';")
47 ActiveRecord::Base.connection.execute("LOAD DATA INFILE '/tmp/EO2.LST' INTO TABLE #{db}.organizations FIELDS TERMINATED BY '' ENCLOSED BY '';")
48 ActiveRecord::Base.connection.execute("LOAD DATA INFILE '/tmp/EO3.LST' INTO TABLE #{db}.organizations FIELDS TERMINATED BY '' ENCLOSED BY '';")
49 ActiveRecord::Base.connection.execute("LOAD DATA INFILE '/tmp/EO4.LST' INTO TABLE #{db}.organizations FIELDS TERMINATED BY '' ENCLOSED BY '';")
50
51
52 puts "Altering IRS table"
53
54 sql = <<-EOF
55 ALTER TABLE `organizations`
56 MODIFY id INT(11) NOT NULL,
57 MODIFY group_exemption SMALLINT,
58 MODIFY subsection_code SMALLINT,
59 MODIFY deductibility_code SMALLINT,
60 MODIFY foundation_code SMALLINT,
61 MODIFY organization_code SMALLINT,
62 MODIFY affiliation_code SMALLINT
63 EOF
64
65 ActiveRecord::Base.connection.execute(sql)
66
67 puts "Trimming ntee_code"
68 ActiveRecord::Base.connection.execute("UPDATE `organizations` SET ntee_code=LEFT(ntee_code, 3)")
69
70 Organization.columns.select{|c| c.type == :string}.collect(&:name).each do |col|
71 puts "Trimming spaces from #{col}"
72 ActiveRecord::Base.connection.execute("UPDATE `organizations` SET #{col}=TRIM(#{col})")
73 ActiveRecord::Base.connection.execute("UPDATE `organizations` SET #{col} = NULL WHERE #{col} = ''")
74 end
75
76 puts "Adding primary key"
77 ActiveRecord::Base.connection.execute("ALTER TABLE `organizations` ADD PRIMARY KEY (id)")
78 puts "IRS Done"
79
80 end