Created
January 30, 2017 16:33
-
-
Save taylor01/5810a4c04d4fcf4c66a42edcabaa50e8 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| class SalesHistoryImporter | |
| require 'csv' | |
| def import(file) | |
| @filename = File.basename(file) | |
| @file = open(file) | |
| if !check_file_name(file) | |
| return false | |
| end | |
| # check and see if we have a compressed file. | |
| begin | |
| process(Zlib::GzipReader.new(@file)) | |
| rescue Zlib::GzipFile::Error | |
| process(@file) | |
| end | |
| return true | |
| end | |
| private | |
| def process(data) | |
| quote_chars = %w(") | |
| a = data.to_a | |
| @record_count = get_trailer_record(a) | |
| @import_count = 0 | |
| # drop the first (header) and last (trailer) records | |
| data = a.drop_first_and_last.join | |
| # cleanup any stray quotes | |
| data = data.gsub("\"", "") | |
| begin | |
| csv = CSV.parse(data, {col_sep: "|", quote_char: quote_chars.shift}) | |
| rescue CSV::MalformedCSVError | |
| quote_chars.empty? ? raise : retry | |
| end | |
| csv.each do |row| | |
| create_record(row) | |
| @import_count += 1 | |
| end | |
| if @import_count == @record_count | |
| log(@filename, 'success') | |
| else | |
| log(@filename, 'failure') | |
| end | |
| end | |
| def log(filename, status) | |
| SalesHistoryImportLog.create(file_name: filename, import_status: status ) | |
| end | |
| def clear_quote_characters(data) | |
| end | |
| def get_trailer_record(d) | |
| d.last.split('|')[1].to_i | |
| end | |
| def create_record(row) | |
| SalesHistory.create( | |
| :number_of_fields => row[0], | |
| :created_by => row[1], | |
| :rr_system_number => row[2], | |
| :rr_store_number => row[3], | |
| :rr_branch_number => row[4], | |
| :new_used_other => row[5], | |
| :stock_number => row[6], | |
| :model_year => row[7], | |
| :model_make => row[8], | |
| :carline => row[9], | |
| :model => row[10], | |
| :model_description => row[11], | |
| :vin => row[12], | |
| :motive_power => row[13], | |
| :invoice_number => row[14], | |
| :vehicle_color => row[15], | |
| :vehicle_color_code => row[16], | |
| :vehicle_trim_color => row[17], | |
| :engine_cylinders => row[18], | |
| :license_plate => row[19], | |
| :vehicle_title_number => row[20], | |
| :vehicle_classification => row[21], | |
| :vehicle_title_state => row[22], | |
| :transmission_type => row[23], | |
| :advanced_technology => row[24], | |
| :diesel_engine => row[25], | |
| :four_wheel_drive => row[26], | |
| :turbo_charger => row[27], | |
| :automatic_transmission => row[28], | |
| :air_conditioning => row[29], | |
| :front_wheel_drive => row[30], | |
| :personal_business_agriculture => row[31], | |
| :number_of_passengers => row[32], | |
| :number_of_doors => row[33], | |
| :manufacturer_certificate_of_origin_date => row[34], | |
| :state_inspection_date => row[35], | |
| :vehicle_registration_state => row[36], | |
| :vehicle_make_number => row[37], | |
| :days_in_stock => row[38], | |
| :in_service_date => row[39], | |
| :gm_certified_used_vehicle => row[40], | |
| :odometer => row[41], | |
| :reconditioning_amount => row[42], | |
| :irregular_payment_amount => row[43], | |
| :prepaid_finance_charge_amount => row[44], | |
| :irregular_last_payment_amount => row[45], | |
| :number_of_irregular_payments => row[46], | |
| :contract_rate_type => row[47], | |
| :disposition_fee => row[48], | |
| :total_number_of_payments => row[49], | |
| :days_until_first_payment => row[50], | |
| :equivalent_add_on_rate => row[51], | |
| :effective_add_on_rate => row[52], | |
| :annual_percentage_rate => row[53], | |
| :equivalent_simple_rate => row[54], | |
| :buy_rate => row[55], | |
| :price_of_vehicle => row[56], | |
| :cash_down => row[57], | |
| :cash_deposit => row[58], | |
| :total_of_deferred_payments => row[59], | |
| :ammount_financed => row[60], | |
| :finance_charge => row[61], | |
| :total_of_payments => row[62], | |
| :deferred_payment_price => row[63], | |
| :monthly_payment => row[64], | |
| :one_pay_option_amount => row[65], | |
| :payments_per_year => row[66], | |
| :one_pay_option_days_til_due => row[67], | |
| :one_pay_option_due_date => row[68], | |
| :outside_loan_date => row[69], | |
| :outside_loan_amount => row[70], | |
| :outside_loan_payment => row[71], | |
| :outside_loan_to_down => row[72], | |
| :outside_loan_to_pick_up_1 => row[73], | |
| :outside_loan_finance_charge => row[74], | |
| :outside_loan_total_of_payments => row[75], | |
| :outside_loan_number_of_payments => row[76], | |
| :acquisition_fee => row[77], | |
| :vehicle_residual_value => row[78], | |
| :dealer_markup => row[79], | |
| :other_deprecable_amount => row[80], | |
| :security_deposit => row[81], | |
| :gross_cap_cost_of_vehicle => row[82], | |
| :net_cap_cost_of_vehicle => row[83], | |
| :monthly_depreciation => row[84], | |
| :adjusted_residual => row[85], | |
| :monthly_lease_charge => row[86], | |
| :base_payment => row[87], | |
| :total_initial_payment => row[88], | |
| :total_term_depreciation => row[89], | |
| :msrp => row[90], | |
| :residual_basis => row[91], | |
| :pro_rata_amount => row[92], | |
| :tax_on_pro_rata_amount => row[93], | |
| :one_years_fees => row[94], | |
| :money_factor => row[95], | |
| :advance_monthly_payment_amount => row[96], | |
| :balloon_payment_amount => row[97], | |
| :total_monthly_lease_payment => row[98], | |
| :estimated_miles_per_year => row[99], | |
| :estimated_miles_per_month => row[100], | |
| :estimated_excess_mileage_charge => row[101], | |
| :charge_per_mile_for_excess => row[102], | |
| :excess_miles_per_term => row[103], | |
| :total_pro_rata_payment => row[104], | |
| :adjusted_percentage_for_residual => row[105], | |
| :depreciated_value => row[106], | |
| :miscellaneous_upfront_total => row[107], | |
| :adjustment_to_residual => row[108], | |
| :purchase_option_fee => row[109], | |
| :other_equity => row[110], | |
| :total_driveoff => row[111], | |
| :profit_driveoff => row[112], | |
| :total_driveoff_amounts => row[113], | |
| :total_cash_back_amounts => row[114], | |
| :number_of_advance_payments => row[115], | |
| :miles_per_year => row[116], | |
| :miles_over_term_of_lease => row[117], | |
| :total_cap_reduction => row[118], | |
| :total_warranty_financed => row[119], | |
| :total_warranty_down_payment => row[120], | |
| :number_of_monthly_payments => row[121], | |
| :aftermarket_total_prices => row[122], | |
| :aftermarket_total_of_prices_financed => row[123], | |
| :amount_for_service_options => row[124], | |
| :first_payment_date => row[125], | |
| :loan_termination_date => row[126], | |
| :last_installment_date => row[127], | |
| :other_premium => row[128], | |
| :vendors_single_interest_insurance => row[129], | |
| :dealer_prep => row[130], | |
| :vendor_fee_1 => row[131], | |
| :vendor_fee_2 => row[132], | |
| :vendor_fee_3 => row[133], | |
| :manufacturer_rebate => row[134], | |
| :rebate_amount_1 => row[135], | |
| :rebate_amount_2 => row[136], | |
| :rebate_amount_3 => row[137], | |
| :rebate_amount_4 => row[138], | |
| :rebate_amount_5 => row[139], | |
| :rebate_description_1 => row[140], | |
| :rebate_description_2 => row[141], | |
| :rebate_description_3 => row[142], | |
| :rebate_description_4 => row[143], | |
| :rebate_description_5 => row[144], | |
| :total_cash_down => row[145], | |
| :total_down => row[146], | |
| :documentation_fee => row[147], | |
| :documentation_handling_fee => row[148], | |
| :discount_on_vehicle => row[149], | |
| :total_accessories_fee => row[150], | |
| :gap_term => row[151], | |
| :license_fee => row[152], | |
| :cvr_fee => row[153], | |
| :total_fees => row[154], | |
| :total_financed_taxes => row[155], | |
| :total_memo_taxes => row[156], | |
| :total_upfront_taxes => row[157], | |
| :total_monthly_taxes => row[158], | |
| :total_taxes => row[159], | |
| :total_state_taxes => row[160], | |
| :luxury_tax_handling_flag => row[161], | |
| :luxury_tax_rate => row[162], | |
| :luxury_tax_threshold => row[163], | |
| :luxury_tax_basis => row[164], | |
| :luxury_tax_amount => row[165], | |
| :miscellaneous_vehicle_cost => row[166], | |
| :cost_of_vehicle => row[167], | |
| :pdi_cost => row[168], | |
| :service_contract_cost => row[169], | |
| :pack_subtracted_from_gross_payable => row[170], | |
| :service_contract_reserve => row[171], | |
| :physical_damage_reserve => row[172], | |
| :total_insurance_reserve => row[173], | |
| :finance_reserve => row[174], | |
| :base_finance_reserve => row[175], | |
| :net_fni_reserve => row[176], | |
| :gross_payable_for_commission => row[177], | |
| :net_profit_on_vehicle => row[178], | |
| :gross_profit_on_vehicle => row[179], | |
| :mbi_cost => row[180], | |
| :mbi_reserve => row[181], | |
| :total_fni_reserve => row[182], | |
| :incentive_amount_1 => row[183], | |
| :incentive_amount_2 => row[184], | |
| :incentive_amount_3 => row[185], | |
| :incentive_amount_4 => row[186], | |
| :incentive_amount_5 => row[187], | |
| :incentive_applied_to_gross_payable => row[188], | |
| :incentive_applied_to_vehicle_gross => row[189], | |
| :incentive_description_1 => row[190], | |
| :incentive_description_2 => row[191], | |
| :incentive_description_3 => row[192], | |
| :incentive_description_4 => row[193], | |
| :incentive_description_5 => row[194], | |
| :incentives => row[195], | |
| :incentives_excluded_from_gross => row[196], | |
| :other_reserve => row[197], | |
| :other_cost => row[198], | |
| :gap_reserve => row[199], | |
| :total_of_aftermarket_costs => row[200], | |
| :total_of_aftermarket_reserves => row[201], | |
| :holdback_for_recap_reserve => row[202], | |
| :freight_charge => row[203], | |
| :flooring_cost => row[204], | |
| :adjustment_to_gross_1 => row[205], | |
| :adjustment_to_gross_2 => row[206], | |
| :adjustment_to_gross_3 => row[207], | |
| :adjustment_to_gross_4 => row[208], | |
| :adjustment_to_gross_5 => row[209], | |
| :pack_new_vehicle => row[210], | |
| :pack_used_vehicle => row[211], | |
| :acquisition_fee_cost => row[212], | |
| :acquisition_fee_reserve => row[213], | |
| :value_option_package_discount_cost => row[214], | |
| :value_option_package_discount_ret => row[215], | |
| :total_front_end_commission => row[216], | |
| :total_back_end_commission => row[217], | |
| :adjustements_excluded_from_gross => row[218], | |
| :adjustment_to_gross => row[219], | |
| :holdback_applied_to_gross => row[220], | |
| :percentage_of_holdback_applied_to_gross_payable => row[221], | |
| :holdback_applied_to_vehicle_gross => row[222], | |
| :percentage_of_incentive_applied_to_gross_payable => row[223], | |
| :vehicle_gross => row[224], | |
| :reconditioning_cost => row[225], | |
| :total_fni_commission => row[226], | |
| :total_salesman_commission => row[227], | |
| :decreasing_life_product_code => row[228], | |
| :life_insurance_term => row[229], | |
| :level_initial_insured_amount => row[230], | |
| :level_life_term => row[231], | |
| :net_pay_insurance => row[232], | |
| :dismemberment_insurance_sold => row[233], | |
| :pre_existing_condition => row[234], | |
| :permanent_total_disability => row[235], | |
| :ah_insurance_term => row[236], | |
| :ah_monthly_benefit => row[237], | |
| :iui_monthly_benefit => row[238], | |
| :total_life_premium => row[239], | |
| :total_life_and_ah_premiums => row[240], | |
| :accelerated_death_benefit => row[241], | |
| :pdi_premium => row[242], | |
| :deal_decreasing_life_premium => row[243], | |
| :deal_level_life_premium => row[244], | |
| :gap_premium => row[245], | |
| :ah_premium => row[246], | |
| :initial_insured_life_amount => row[247], | |
| :ah_insured_amount => row[248], | |
| :life_reserve => row[249], | |
| :total_lah_reserve => row[250], | |
| :level_life_expiration_date => row[251], | |
| :ah_expiration_date => row[252], | |
| :iui_expiration_date => row[253], | |
| :iui_reserve => row[254], | |
| :iui_premium => row[255], | |
| :iui_coverage_term => row[256], | |
| :iui_insured_amount => row[257], | |
| :stock_number_of_trade_1 => row[258], | |
| :stock_number_of_trade_2 => row[259], | |
| :stock_number_of_trade_3 => row[260], | |
| :id_number_of_trade_1 => row[261], | |
| :id_number_of_trade_2 => row[262], | |
| :id_number_of_trade_3 => row[263], | |
| :make_of_trade_1 => row[264], | |
| :make_of_trade_2 => row[265], | |
| :make_of_trade_3 => row[266], | |
| :model_of_trade_1 => row[267], | |
| :model_of_trade_2 => row[268], | |
| :model_of_trade_3 => row[269], | |
| :trade_allowance_1 => row[270], | |
| :trade_allowance_2 => row[271], | |
| :trade_allowance_3 => row[272], | |
| :payoff_on_trade_1 => row[273], | |
| :payoff_on_trade_2 => row[274], | |
| :payoff_on_trade_3 => row[275], | |
| :model_description_of_trade_1 => row[276], | |
| :model_description_of_trade_2 => row[277], | |
| :model_description_of_trade_3 => row[278], | |
| :color_of_trade_1 => row[279], | |
| :color_of_trade_2 => row[280], | |
| :color_of_trade_3 => row[281], | |
| :year_of_trade_1 => row[282], | |
| :year_of_trade_2 => row[283], | |
| :year_of_trade_3 => row[284], | |
| :actual_cash_value_of_trade_1 => row[285], | |
| :actual_cash_value_of_trade_2 => row[286], | |
| :actual_cash_value_of_trade_3 => row[287], | |
| :total_of_all_payoffs => row[288], | |
| :total_of_all_trades => row[289], | |
| :total_net_of_all_trades => row[290], | |
| :total_actual_cash_value_of_all_trades => row[291], | |
| :total_of_overallowance_of_all_trades => row[292], | |
| :total_of_underallowance_of_all_trades => row[293], | |
| :total_positive_trade_equity => row[294], | |
| :total_negative_trade_equity => row[295], | |
| :service_contract_type => row[296], | |
| :service_contract => row[297], | |
| :service_contract_handling_flag => row[298], | |
| :service_contract_company_name => row[299], | |
| :service_contract_company_address => row[300], | |
| :service_contract_company_city => row[301], | |
| :warranty_vendor => row[302], | |
| :warranty_policy_number => row[303], | |
| :total_warranty_premium => row[304], | |
| :total_warranty_premium_financed => row[305], | |
| :total_warranty_down_payment_paid_to_vendor => row[306], | |
| :total_warranty_amount_financed_by_vendor => row[307], | |
| :warranty_number_of_payments => row[308], | |
| :warranty_total_amount_financed_by_other => row[309], | |
| :warranty_class => row[310], | |
| :warranty_vehicle_code => row[311], | |
| :mechanical_breakdown_insurance_premium => row[312], | |
| :mechanical_breakdown_insurance_contract_miles => row[313], | |
| :mechanical_breakdown_insurance_deductible => row[314], | |
| :gm_product => row[315], | |
| :gm_production_card_type => row[316], | |
| :deal_type => row[317], | |
| :deal_status => row[318], | |
| :system_date => row[319], | |
| :deal_number => row[320], | |
| :customer_number => row[321], | |
| :deal_date_on_contracts => row[322], | |
| :deal_category => row[323], | |
| :co_owner => row[324], | |
| :fleet_deal => row[325], | |
| :out_of_state_sale => row[326], | |
| :commercial_deal => row[327], | |
| :referral_source => row[328], | |
| :gm_division => row[329], | |
| :vehicle_sold_as_is => row[330], | |
| :close_date_of_deal => row[331], | |
| :delivery_date_of_deal => row[332], | |
| :towing_deductible => row[333], | |
| :bank_name => row[334], | |
| :bank_address => row[335], | |
| :bank_city => row[336], | |
| :bank_state => row[337], | |
| :bank_zip => row[338], | |
| :bank_id_number => row[339], | |
| :accounting_bank_code => row[340], | |
| :dealer_tax_id_number => row[341], | |
| :company_name_of_dealer => row[342], | |
| :dealer_name => row[343], | |
| :dealer_number_for_titling => row[344], | |
| :fni_manager_name => row[345], | |
| :general_motors_dealer_code => row[346], | |
| :registered_owner => row[347], | |
| :registered_owner_address => row[348], | |
| :registered_owner_city => row[349], | |
| :registered_owner_state => row[350], | |
| :registered_owner_zip => row[351], | |
| :registered_owner_date_of_birth => row[352], | |
| :salesperson_1_number => row[353], | |
| :salesperson_1_last_name => row[354], | |
| :salesperson_1_first_name => row[355], | |
| :salesperson_1_middle_name => row[356], | |
| :salesperson_2_number => row[357], | |
| :salesperson_2_last_name => row[358], | |
| :salesperson_2_first_name => row[359], | |
| :salesperson_2_middle_name => row[360], | |
| :trade_odometer_1 => row[361], | |
| :trade_odometer_2 => row[362], | |
| :trade_odometer_3 => row[363], | |
| :buyer_name_type_1 => row[364], | |
| :buyer_last_name => row[365], | |
| :buyer_first_name => row[366], | |
| :buyer_individual_business_flag => row[367], | |
| :buyer_middle_name => row[368], | |
| :buyer_address_1 => row[369], | |
| :buyer_address_2 => row[370], | |
| :buyer_city => row[371], | |
| :buyer_state => row[372], | |
| :buyer_zip => row[373], | |
| :buyer_county => row[374], | |
| :buyer_phone => row[375], | |
| :buyer_ssn => row[376], | |
| :buyer_birth_date => row[377], | |
| :buyer_salutation => row[378], | |
| :buyer_work_phone => row[379], | |
| :buyer_cell_phone_extension => row[380], | |
| :buyer_cell_phone => row[381], | |
| :buyer_business_extension => row[382], | |
| :buyer_fax_number => row[383], | |
| :buyer_fax_extension => row[384], | |
| :buyer_pager => row[385], | |
| :buyer_pager_extension => row[386], | |
| :buyer_home_phone => row[387], | |
| :buyer_gender => row[388], | |
| :buyer_suffix => row[389], | |
| :buyer_salutation_name => row[390], | |
| :buyer_employer => row[391], | |
| :buyer_children_names => row[392], | |
| :buyer_follow_up_phone => row[393], | |
| :buyer_follow_up_mail => row[394], | |
| :buyer_mail_code => row[395], | |
| :buyer_dealer_code => row[396], | |
| :buyer_email_address => row[397], | |
| :buyer_unlisted_phone_number => row[398], | |
| :buyer_billing_address => row[399], | |
| :buyer_billing_city => row[400], | |
| :buyer_billing_state => row[401], | |
| :buyer_billing_zip => row[402], | |
| :buyer_employer_phone => row[403], | |
| :sales_manager_name => row[404], | |
| :co_buyer_name_type_1 => row[405], | |
| :co_buyer_last_name => row[406], | |
| :co_buyer_first_name => row[407], | |
| :co_buyer_individual_business_flag => row[408], | |
| :co_buyer_middle_name => row[409], | |
| :co_buyer_address_1 => row[410], | |
| :co_buyer_address_2 => row[411], | |
| :co_buyer_city => row[412], | |
| :co_buyer_state => row[413], | |
| :co_buyer_zip => row[414], | |
| :co_buyer_county => row[415], | |
| :co_buyer_phone => row[416], | |
| :co_buyer_ssn => row[417], | |
| :co_buyer_birth_date => row[418], | |
| :co_buyer_salutation => row[419], | |
| :co_buyer_work_phone => row[420], | |
| :co_buyer_cell_phone_extension => row[421], | |
| :co_buyer_cell_phone => row[422], | |
| :co_buyer_business_extension => row[423], | |
| :co_buyer_fax_number => row[424], | |
| :co_buyer_fax_extension => row[425], | |
| :co_buyer_pager => row[426], | |
| :co_buyer_pager_extension => row[427], | |
| :co_buyer_home_phone => row[428], | |
| :co_buyer_gender => row[429], | |
| :co_buyer_suffix => row[430], | |
| :co_buyer_salutation_name => row[431], | |
| :co_buyer_employer => row[432], | |
| :co_buyer_children_names => row[433], | |
| :co_buyer_follow_up_phone => row[434], | |
| :co_buyer_follow_up_mail => row[435], | |
| :co_buyer_mail_code => row[436], | |
| :co_buyer_dealer_code => row[437], | |
| :co_buyer_email_address => row[438], | |
| :co_buyer_unlisted_phone_number => row[439], | |
| :co_buyer_billing_address => row[440], | |
| :co_buyer_billing_city => row[441], | |
| :co_buyer_billing_state => row[442], | |
| :co_buyer_billing_zip => row[443], | |
| :co_buyer_employer_phone => row[444] | |
| ) | |
| end | |
| # check the filename to make sure it is the | |
| # proper name for a sales history file | |
| def check_file_name(file) | |
| filename = File.basename(file) | |
| filename.starts_with?('RRO_D_DR_RCI') | |
| end | |
| end |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment