1- namespace :import do
2- desc "Import trade permits from csv file"
3- task :trade_permits => [ :environment ] do
4- #TMP_TABLE = "permits_import"
5- #file = "lib/files/permit_details.csv"
6- #drop_table(TMP_TABLE)
7- #create_table_from_csv_headers(file, TMP_TABLE)
8- #copy_data(file, TMP_TABLE)
1+ namespace :import do
2+ desc "Import trade permits from csv file"
3+ task :trade_permits => [ :environment ] do
4+ TMP_TABLE = "permits_import"
5+ file = "lib/files/permit_details.csv"
6+ permits_import_to_index = { "permits_import" => [ "permit_number" , "shipment_number" , "permit_reporter_type" ] }
7+ trade_permits_to_index = { "trade_permits" => [ "shipment_number" , "legacy_reporter_type" ] }
98
10- sql = <<-SQL
11- ALTER TABlE trade_permits ADD COLUMN shipment_number int;
12- INSERT INTO trade_permits (number, shipment_number, legacy_reporter_type, created_At, updated_at)
13- SELECT permit_number,
14- shipment_number,
15- legacy_reporter_type,
16- now()::date AS created_at,
17- now()::date AS updated_at
18- FROM permits_import;
19- SQL
20- ActiveRecord ::Base . connection . execute ( sql )
9+ delete_shipment_number_tmp_column
10+ drop_indices ( trade_permits_to_index )
11+ drop_indices ( permits_import_to_index )
12+ drop_table ( TMP_TABLE )
13+ create_table_from_csv_headers ( file , TMP_TABLE )
14+ copy_data ( file , TMP_TABLE )
15+ drop_indices ( trade_permits_to_index )
16+ add_shipment_number_tmp_column
17+ create_indices ( trade_permits_to_index )
18+ populate_trade_permits
19+ create_indices ( trade_permits_to_index )
20+ insert_into_trade_shipments
21+ drop_indices ( permits_import_to_index )
22+ drop_indices ( trade_permits_to_index )
23+ delete_shipment_number_tmp_column
24+ end
25+ end
2126
22- sql = <<-SQL
23- CREATE INDEX index_permits_import_on_permit_number
24- ON permits_import
25- USING btree
26- (permit_number);
27- CREATE INDEX index_permits_import_on_shipment_number
28- ON permits_import
29- USING btree
30- (shipment_number);
31- CREATE INDEX index_permits_import_on_legacy_reporter_type
32- ON permits_import
33- USING btree
34- (legacy_reporter_type);
35- SQL
27+ def populate_trade_permits
28+ sql = <<-SQL
29+ INSERT INTO trade_permits (number, shipment_number, legacy_reporter_type, created_At, updated_at)
30+ SELECT permit_number,
31+ shipment_number,
32+ permit_reporter_type,
33+ now()::date AS created_at,
34+ now()::date AS updated_at
35+ FROM permits_import;
36+ SQL
37+ puts "Inserting into trade_permits"
38+ execute_query ( sql )
39+ end
3640
37- permits_entity = { "import" => "I" , "export" => 'E' , "origin" => 'O' }
38- permits_entity . each do |k , v |
3941
40- sql = <<-SQL
41- UPDATE trade_shipments
42- SET #{ k }
43- FROM (SELECT array_agg(number) array,
44- shipment_number
45- from trade_permits
46- where type = #{ v }
47- group by shipment_number) a
48- where legacy_shipment_number = a.shipment_number
49- SQL
50- ActiveRecord ::Base . connection . execute ( sql )
51-
52- end
42+ def insert_into_trade_shipments
43+ permits_entity = { "import" => "I" , "export" => 'E' , "origin" => 'O' }
44+ permits_entity . each do |k , v |
45+ sql = <<-SQL
46+ UPDATE trade_shipments
47+ SET #{ k }
48+ FROM (SELECT array_agg(id) id,
49+ shipment_number
50+ from trade_permits
51+ where type = '#{ v }
52+ group by shipment_number) a
53+ where legacy_shipment_number = a.shipment_number
54+ SQL
55+ puts "Inserting into trade_shipments"
56+ execute_query ( sql )
57+ end
58+ end
5359
54- sql = <<-SQL
55- ALTER TABlE trade_permits DROP COLUMN shipment_number;
56- SQL
57- ActiveRecord ::Base . connection . execute ( sql )
60+
5861
62+ def execute_query ( sql )
63+ ActiveRecord ::Base . connection . execute ( sql )
64+ end
5965
60-
66+ def drop_indices ( index )
67+ index . each do |table , columns |
68+ columns . each do |column |
69+ sql = <<-SQL
70+ DROP INDEX IF EXISTS index_#{ table } #{ column }
71+ SQL
72+ puts "Dropping index #{ index }
73+ execute_query ( sql )
74+ end
75+ end
76+ end
6177
62- #Trade::Permi.select(:legacy_shipment_number).joins("INNER JOIN permits_import ON legacy_shipment_number = shipment_number limit 10").each do |number|
63- #sql = <<-SQL
64- # UPDATE trade_shipments SET export_permits_ids = s.array FROM
65- # (SELECT #{number.legacy_shipment_number} as number, ARRAY(SELECT permit_number
66- # FROM trade_permits inner join permits_import WHERE shipment_number = #{number.legacy_shipment_number} and permits_import.permit_type = 'E')) s
67- # WHERE legacy_shipment_number = s.number
68- #SQL
69- #puts ActiveRecord::Base.connection.execute(sql).first
70-
71- end
78+ def create_indices ( table_columns )
79+ table_columns . each do |table , columns |
80+ columns . each do |column |
81+ sql = <<-SQL
82+ CREATE INDEX index_#{ table } #{ column }
83+ ON #{ table }
84+ USING btree
85+ (#{ column }
86+ SQL
87+ puts "Creating index for #{ column }
88+ execute_query ( sql )
89+ end
7290 end
73- end
91+ end
92+
93+ def add_shipment_number_tmp_column
94+ sql = <<-SQL
95+ ALTER TABlE trade_permits ADD COLUMN shipment_number int;
96+ SQL
97+ execute_query ( sql )
98+ end
99+ def delete_shipment_number_tmp_column
100+ sql = <<-SQL
101+ ALTER TABlE trade_permits DROP COLUMN shipment_number;
102+ SQL
103+ execute_query ( sql )
104+ end
0 commit comments