sales_history_importer.rb

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